Funkcje statystyczne Excela: Samouczek z przykładami do badań, część czwarta

Excel udostępnia szeroką gamę funkcji statystycznych, które wykonują obliczenia, od podstawowej średniej, mediany i trybu po funkcje wyszukiwania.

W tym artykule zagłębimy się w funkcje wyszukiwania.

Należy pamiętać, że w najnowszych wersjach programu Excel wprowadzono pewne funkcje statystyczne i dlatego nie są one dostępne w starszych wersjach.

Szacowany czas czytania: 18 minuti

Funkcje wyszukiwania

MAX

Funkcja MAX programu Excel znajduje się w kategorii Funkcje statystyczne programu Microsoft Excel. Zwraca największą wartość z listy wartości. MAX oznacza maksimum i kiedy określisz listę wartości, szuka w niej najwyższej wartości i zwraca tę wartość w wyniku.

składnia

= MAX(number1, [number2], …)

Tematy

• number1:  liczba, komórka zawierająca liczbę lub zakres komórek zawierających liczby, z których chcesz uzyskać największą liczbę.
• [number2]:  liczba to komórka zawierająca liczbę lub zakres komórek zawierających liczby, z których chcesz uzyskać największą liczbę.

przykład

Aby opanować funkcję MAX, musimy wypróbować ją na przykładzie, poniżej znajdziesz taki, który możesz wypróbować:

W poniższym przykładzie liczby wprowadziliśmy bezpośrednio do funkcji, oddzielając je przecinkiem.

Uwaga: Możesz także wprowadzić liczbę, używając podwójnych cudzysłowów.

W poniższym przykładzie odwołaliśmy się do zakresu i jako największą wartość zwrócono 1861. Można także odwoływać się do tablicy.

W poniższym przykładzie napotkaliśmy wartość błędu i funkcja zwróciła w wyniku wartość błędu.

MAXA

Funkcja Excela Maxa jest bardzo podobny do Funkcja Excela Max.

Jedyna różnica między tymi dwiema funkcjami występuje, gdy argument jest dostarczany do funkcji jako odwołanie do komórki lub tablicy komórek.

Funkcja Max funkcja ignoruje wartości logiczne i tekstowe Maxa liczy się wartość logiczna TRUE jako 1, wartość logiczna FALSE jako 0, a wartości tekstowe jako 0.

Funkcja MAXA Excel zwraca największą wartość z podanego zestawu wartości liczbowych, zliczając tekst i wartość logiczną FALSE jako wartość 0 i zliczanie wartości logicznej TRUE jako wartość 1.

składnia

= MAXA(number1, [number2], …)

Tematy

• number1:  liczba (lub tablice wartości liczbowych), komórka zawierająca liczbę lub zakres komórek zawierających liczby, z których chcesz uzyskać największą liczbę.
• [number2]:  liczba to komórka zawierająca liczbę (lub tablice wartości liczbowych) lub zakres komórek zawierających liczby, z których chcesz uzyskać największą liczbę.

W bieżących wersjach programu Excel (Excel 2007 i nowsze) można podać do 255 argumentów numerycznych do funkcji Maxa, ale w Excelu 2003 funkcja może przyjąć tylko do 30 argumentów numerycznych.

Przykłady

Przykład 1

Komórka B1 poniższego arkusza kalkulacyjnego przedstawia tę funkcję Excel Maxa, używany do pobrania największej wartości ze zbioru wartości w komórkach A1-A5.

Przykład 2

Komórka B1 poniższego arkusza kalkulacyjnego przedstawia tę funkcję Excel Maxa, używany do pobrania największej wartości ze zbioru wartości w komórkach A1-A3.

Zwróć uwagę, że wartość TRUE w komórce A1 arkusza kalkulacyjnego jest traktowana przez funkcję jako wartość liczbowa 1 Maxa. Jest to zatem największa wartość w zakresie A1-A3.

Dalsze przykłady funkcji Excel Maxa są zapewnione Witryna internetowa pakietu Microsoft Office .

Błąd funkcji MAXA

Jeśli pojawi się błąd z funkcji Maxa programu Excel, prawdopodobnie jest to błąd #VALORE!: Występuje, jeśli wartości zostaną podane bezpośrednio do funkcji Maxa nie są numeryczne.

MAXIFS

Funkcja Excela Maxifs to funkcja wyszukiwania, która zwraca maksymalną wartość z podzbioru wartości określonych na podstawie jednego lub większej liczby kryteriów.

składnia

= MAXIFS( max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ... )

Tematy

• max_range:  Tablica wartości numerycznych (lub zakres komórek zawierających wartości liczbowe), z której chcesz zwrócić maksymalną wartość, jeśli zostaną spełnione kryteria.
• criteria_range1:  tablica wartości (lub zakres komórek zawierających wartości) do przeprowadzenia testu criteria1 .(Ta tablica musi mieć tę samą długość co max_range ).
• criteria1: Warunek do sprawdzenia w odniesieniu do wartości w criteria_range1.
• [criteria_range2, criteria2], [criteria_range3, criteria3], ...: Dodatkowe opcjonalne tablice wartości do przetestowania i odpowiednich warunków do przetestowania.

Funkcja Maxifs może obsłużyć do 126 par tematów criteria_range e criteria.

Każde z podanych kryteriów może być:

• wartość liczbową (która może być liczbą całkowitą, dziesiętną, datą, godziną lub wartością logiczną) (np. 10, 01, TRUE)

lub

• ciąg tekstowy (np. „Imię”, „MercoleZ")

lub

• wyrażenie (na przykład „>1”, „<>0”).

Nei criteria w odniesieniu do tekstu możesz używać symboli wieloznacznych:

• ? aby dopasować dowolny pojedynczy znak
• * aby dopasować dowolną sekwencję znaków.

Jeśli criteria jest ciągiem tekstowym lub wyrażeniem, należy to podać funkcji Maxifs w cudzysłowie.

Funkcja Maxifs Wielkość liter nie jest uwzględniana. Dlatego na przykład porównując wartości w criteria_range z criteria, ciągi tekstowe „TEXT"A"text” będą uznawane za równe.

Funkcja Maxifs został po raz pierwszy wprowadzony w programie Excel 2019 i dlatego nie jest dostępny we wcześniejszych wersjach programu Excel.

Przykłady

Poniższy arkusz kalkulacyjny przedstawia kwartalne dane sprzedażowe dla 3 przedstawicieli handlowych.

Funkcja Maxifs można wykorzystać do znalezienia maksymalnej wartości sprzedaży dla dowolnego kwartału, terytorium lub przedstawiciela handlowego (lub dowolnej kombinacji kwartału, terytorium i przedstawiciela handlowego).

Spójrzmy na następujące przykłady.

Przykład 1

Aby znaleźć maksymalną wielkość sprzedaży w pierwszym kwartale:

=MAXIFS( D2:D13, A2:A13, 1 )

co daje wynik $ 456.000.

W tym przykładzie plik Excel Maxifs identyfikuje wiersze, w których wartość w kolumnie A jest równa 1 i zwraca maksymalną wartość z odpowiednich wartości w kolumnie D.

Oznacza to, że funkcja znajduje maksimum z wartości 223.000 125.000 USD, 456.000 2 USD i 3 4 USD (z komórek DXNUMX, DXNUMX i DXNUMX).

Przykład 2

Ponownie, korzystając z powyższego arkusza danych, możemy również użyć funkcji Maxifs, aby znaleźć maksymalną wielkość sprzedaży „Jeffa” w kwartałach 3 i 4:

=MAXIFS( D2:D13, A2:A13, ">2", C2:C13, "Jeff" )

Ta formuła zwraca wynik $ 310.000 .

W tym przykładzie plik Excel Maxifs identyfikuje linie, w których:

• Wartość w kolumnie A jest większa niż 2

E

• Wpis w kolumnie C jest równy „Jeff”

i zwraca maksimum odpowiednich wartości w kolumnie D.

Oznacza to, że ta formuła znajduje maksimum wartości 310.000 261.000 USD i 8 11 USD (z komórek DXNUMX i DXNUMX).

Konsultacje il Witryna internetowa pakietu Microsoft Office aby uzyskać więcej informacji na temat przykładów funkcji programu Excel Maxifs.

Błąd funkcji MAXIFS

Jeśli pojawi się błąd funkcji Excel Maxifs, prawdopodobnie będzie to jeden z poniższych:

#VALUE!: Sprawdza, czy tablice max_range e criteria_range dostarczone nie wszystkie mają tę samą długość.

@NAME?: Występuje, jeśli używasz starszej wersji programu Excel (sprzed 2019 r.), która nie obsługuje tej funkcji Maxifs.

MIN

Funkcja MIN to funkcja wyszukiwania, która zwraca najniższą wartość z listy wartości. MIN oznacza minimum, a kiedy określisz listę wartości, wyszukuje w niej najniższą wartość i zwraca tę wartość w wyniku.

składnia

= MIN(number1, [number2], …)

Tematy

• number1:  liczba, komórka zawierająca liczbę lub zakres komórek zawierających liczby, z których chcesz uzyskać najmniejszą liczbę.
• [number2]:  liczba, komórka zawierająca liczbę lub zakres komórek zawierających liczby, z których chcesz uzyskać najmniejszą liczbę.

przykład

W poniższym przykładzie liczby wprowadziliśmy bezpośrednio do funkcji, oddzielając je przecinkiem.

Możesz także wprowadzić liczbę, używając podwójnych cudzysłowów. W poniższym przykładzie odwołaliśmy się do zakresu, a zwrócony wynik to 1070.

W poniższym przykładzie napotkaliśmy wartość błędu i funkcja zwróciła w wyniku wartość błędu.

MINA

Funkcja Excela MINA jest bardzo podobny do Funkcja Excela MIN.

Jedyna różnica między tymi dwiema funkcjami występuje, gdy argument jest dostarczany do funkcji jako odwołanie do komórki lub tablicy komórek.

W tym przypadku funkcja MIN funkcja ignoruje wartości logiczne i tekstowe MINA liczy się wartość logiczna TRUE jako 1, wartość logiczna FALSE jako 0, a wartości tekstowe jako 0.

Funkcja MINA Excel zwraca najmniejszą wartość z podanego zestawu wartości liczbowych, zliczając tekst i wartość logiczną FALSE jako wartość 0 i zliczanie wartości logicznej TRUE jako wartość 1.

składnia

= MINA( number1, [number2], ... )

Tematy

• number1:  liczba, komórka zawierająca liczbę lub zakres komórek (lub tablice wartości liczbowych) zawierający liczby, z których chcesz uzyskać najmniejszą liczbę.
• [number2]:  liczba, komórka zawierająca liczbę lub zakres komórek (lub tablice wartości liczbowych) zawierający liczby, z których chcesz uzyskać najmniejszą liczbę.

W aktualnych wersjach programu Excel (Excel 2007 i nowsze) można podać do funkcji do 255 argumentów numerycznych MINA, ale w programie Excel 2003 funkcja może przyjąć maksymalnie 30 argumentów numerycznych.

Przykłady

Przykład 1

Komórka B1 poniższego arkusza kalkulacyjnego przedstawia funkcję Excel MINA, służącą do pobrania najmniejszej wartości ze zbioru wartości w komórkach A1-A5.

Przykład 2

Komórka B1 poniższego arkusza kalkulacyjnego przedstawia funkcję Excel MINA, używany do pobrania najmniejszej wartości ze zbioru wartości w komórkach A1-A3.

Pamiętaj, że wartość TRUE w komórce A1 arkusza kalkulacyjnego jest traktowana przez funkcję jako wartość liczbowa 1 MINA. Jest to zatem najmniejsza wartość w zakresie A1-A3.

Więcej przykładów funkcji Excela MINA są zapewnione Witryna internetowa pakietu Microsoft Office .

Błąd funkcji MINA

Jeśli pojawi się błąd z funkcji MINA programu Excel, prawdopodobnie jest to błąd #VALORE!. Występuje jeśli wartości podawane do funkcji MINA nie są numeryczne.

MINIFS

Funkcja Excela MINIFS to funkcja wyszukiwania, która zwraca minimalną wartość z podzbioru wartości określonych na podstawie jednego lub większej liczby kryteriów.

składnia

= MINIFS( min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ... )

Tematy

• min_range:  Tablica wartości numerycznych (lub zakres komórek zawierających wartości liczbowe), z której chcesz zwrócić maksymalną wartość, jeśli zostaną spełnione kryteria.
• criteria_range1:  tablica wartości (lub zakres komórek zawierających wartości) do przeprowadzenia testu criteria1 .(Ta tablica musi mieć tę samą długość co min_range ).
• criteria1: Warunek do sprawdzenia w odniesieniu do wartości w criteria_range1.
• [criteria_range2, criteria2], [criteria_range3, criteria3], ...: Dodatkowe opcjonalne tablice wartości do przetestowania i odpowiednich warunków do przetestowania.

Funkcja Minifs może obsłużyć do 126 par tematów criteria_range e criteria.

Każde z podanych kryteriów może być:

• wartość liczbową (która może być liczbą całkowitą, dziesiętną, datą, godziną lub wartością logiczną) (np. 10, 01, TRUE)

lub

• ciąg tekstowy (np. „Imię”, „MercoleZ")

lub

• wyrażenie (na przykład „>1”, „<>0”).

Nei criteria w odniesieniu do tekstu możesz używać symboli wieloznacznych:

• ? aby dopasować dowolny pojedynczy znak
• * aby dopasować dowolną sekwencję znaków.

Jeśli criteria jest ciągiem tekstowym lub wyrażeniem, należy to podać funkcji Minifs w cudzysłowie.

Funkcja Minifs Wielkość liter nie jest uwzględniana. Dlatego na przykład porównując wartości w criteria_range z criteria, ciągi tekstowe „TEXT” i „tekst” będą traktowane jako to samo.

Funkcja Minifs został po raz pierwszy wprowadzony w programie Excel 2019 i dlatego nie jest dostępny we wcześniejszych wersjach programu Excel.

Przykłady

Poniższy arkusz kalkulacyjny przedstawia kwartalne dane sprzedaży dla 3 sprzedawców.

Funkcja Minifs można wykorzystać do znalezienia minimalnej wartości sprzedaży dla dowolnego kwartału, regionu lub przedstawiciela handlowego.

Pokazano to w poniższych przykładach.

Przykład 1

Aby znaleźć minimalną wielkość sprzedaży w pierwszym kwartale:

=MINIFS( D2:D13, A2:A13, 1 )

co daje wynik $ 125.000 .

W tym przykładzie plik Excel Minifs identyfikuje wiersze, w których wartość w kolumnie A jest równa 1 i zwraca minimalną wartość z odpowiednich wartości w kolumnie D.

Oznacza to, że funkcja znajduje minimum z wartości 223.000 125.000 USD, 456.000 2 USD i 3 4 USD (z komórek DXNUMX, DXNUMX i DXNUMX).

Przykład 2

Ponownie, korzystając z powyższego arkusza danych, możemy również skorzystać z tej funkcji Minifs aby znaleźć minimalną wielkość sprzedaży „Jeffa” w kwartałach 3 i 4:

=MINIFS( D2:D13, A2:A13, ">2", C2:C13, "Jeff" )

Ta formuła zwraca wynik $261.000 .

W tym przykładzie plik Excel Minifs identyfikuje linie, w których:

• Wartość w kolumnie A jest większa niż 2

E

• Wpis w kolumnie C jest równy „Jeff”

i zwraca minimum odpowiednich wartości w kolumnie D.

Oznacza to, że ta formuła znajduje minimum wartości 310.000 261.000 USD i 8 11 USD (z komórek DXNUMX i DXNUMX).

Więcej przykładów funkcji Excela Minifs, skonsultuj się z Witryna internetowa pakietu Microsoft Office .

Błąd funkcji MINIFS

Jeśli pojawi się błąd funkcji Excel Minifs, prawdopodobnie jest to jedna z następujących przyczyn:

• #VALORE! -Sprawdza, czy tablice min_range e criteria_range dostarczone nie wszystkie mają tę samą długość.
• #NOME? – Występuje, jeśli używasz starszej wersji programu Excel (sprzed 2019 r.), która nie obsługuje tej funkcji Minifs.

LARGE

Funkcja Excela Large to funkcja wyszukiwania, która zwraca k-tą największą wartość z tablicy wartości numerycznych.

składnia

= LARGE( array, k )

Tematy

• array – Tablica wartości numerycznych, w których należy szukać k-tej największej wartości.
• K – Indeks, czyli funkcja zwraca k-tą największą wartość zarray pod warunkiem, że.

Argument tablicowy można podać do funkcji bezpośrednio lub jako odwołanie do zakresu komórek zawierających wartości liczbowe. Jeżeli wartości w podanym zakresie komórek są wartościami tekstowymi, wartości te zostaną zignorowane.

przykład

Poniższy arkusz kalkulacyjny przedstawia funkcję programu Excel Large, używany do pobierania pierwszej, drugiej, trzeciej, czwartej i piątej największej wartości ze zbioru wartości w komórkach A1-A5.

Kilka przemyśleń na temat powyższego przykładowego arkusza kalkulacyjnego:

• W komórce B1, gdzie k jest ustawione na 1, funkcja Large wykonuje tę samą czynność co Funkcja Excela Maks ;
• W komórce B5, gdy k jest ustawione na 5 (liczba wartości w podanej tablicy), funkcja Large wykonuje tę samą akcję co Funkcja Excel Min .

Dalsze szczegóły i przykłady funkcji Excel Large można znaleźć na stronie Witryna internetowa pakietu Microsoft Office .

Błąd funkcji LARGE

Jeśli Excel Large zwraca błąd, prawdopodobnie jest to jeden z poniższych:

• #NUM! – Występuje, jeśli:
  • Podana wartość k jest mniejsza niż 1 lub większa niż liczba wartości w dostarczonej tablicy
    lub
    L 'array pod warunkiem, że jest pusty.
• #VALUE! – Występuje, jeśli podane k nie jest liczbą.

Jednak w obliczeniach funkcji DUŻY mogą wystąpić błędy, nawet jeśli podana wartość k będzie wynosić od 1 do liczby wartości w dostarczonej tablicy. Możliwą przyczyną może być to, że wartości tekstowe, w tym tekstowe reprezentacje liczb w podanej tablicy, są ignorowane przez funkcję Large. Dlatego ten problem może wystąpić, jeśli wartości w podanej tablicy są tekstowymi reprezentacjami liczb, a nie rzeczywistymi wartościami liczbowymi.

Rozwiązanie można osiągnąć poprzez konwersję wszystkich wartości tablicy na wartości liczbowe. 

SMALL

Funkcja Excel Small to funkcja wyszukiwania, która zwraca k-tą najmniejszą wartość z tablicy wartości numerycznych.

składnia

= SMALL( array, k )

Tematy

• array – Tablica wartości numerycznych do wyszukiwania k-tej największej wartości.
• K – Indeks, czyli funkcja zwraca k-tą największą wartość zarray pod warunkiem, że.

Argument tablicowy można podać do funkcji bezpośrednio lub jako odwołanie do zakresu komórek zawierających wartości liczbowe. Jeżeli wartości w podanym zakresie komórek są wartościami tekstowymi, wartości te zostaną zignorowane.

przykład

Poniższy arkusz kalkulacyjny przedstawia funkcję programu Excel Small, używany do pobierania pierwszej, drugiej, trzeciej, czwartej i piątej najmniejszej wartości ze zbioru wartości w komórkach A1-A5.

W przykładzie należy wziąć pod uwagę, że:

• W komórce B1, gdzie k jest ustawione na 1, funkcja Small wykonuje tę samą czynność co Funkcja Excel Min ;
• W komórce B5, gdy k jest ustawione na 5 (liczba wartości warray pod warunkiem ), funkcja Small wykonuje tę samą czynność co Funkcja Max w programie Excel .

Więcej szczegółów i przykładów funkcji Excel Small są zapewnione Witryna internetowa pakietu Microsoft Office .

Błąd funkcji SMALL

Jeśli Excel SMALL zwraca błąd, prawdopodobnie jest to jeden z poniższych:

• #NUM! – Występuje, jeśli:
  • Podana wartość k jest mniejsza niż 1 lub większa niż liczba wartości w dostarczonej tablicy
    lub
    Podana tablica jest pusta.
• #VALUE! – Występuje, jeśli podane k nie jest liczbą.

Jednakże w obliczeniach funkcji mogą wystąpić błędy LARGE nawet jeśli podana wartość k mieści się w przedziale od 1 do liczby wartości warray pod warunkiem, że. Możliwą przyczyną może być to, że wartości tekstowe, w tym tekstowe reprezentacje liczb w plikuarray pod warunkiem, że są one ignorowane przez funkcję Large. Dlatego ten problem może wystąpić, jeśli wartości w plikuarray podane są tekstowe reprezentacje liczb, a nie rzeczywiste wartości liczbowe.

Rozwiązanie można osiągnąć poprzez konwersję wszystkich wartościarray w wartościach liczbowych. 

Powiązane lektury

• Funkcje statystyczne Excela: Samouczek z przykładami, część pierwsza
• Wykresy w Excelu, czym są, jak stworzyć wykres i jak wybrać optymalny wykres
• JSP Request, wszystkie metody niejawnego obiektu Request
• Zarządzanie projektami w szkoleniu empirycznym
• Tabele przestawne: czym są, jak tworzyć w Excelu i Google. Poradnik z przykładami

Ercole Palmeri