Pobierz przykładowy skoroszyt
Ten samouczek pokaże, jak obliczyć „duże jeśli” lub „małe jeśli”, pobierając n-tą największą (lub najmniejszą) wartość na podstawie kryteriów.
DUŻE I MAŁE Funkcje
Funkcja LARGE służy do obliczania n-tej największej wartości (k) w tablicy, podczas gdy funkcja MAŁA zwraca najmniejszą n-tą wartość.
Aby utworzyć „Duży jeśli”, użyjemy funkcji LARGE wraz z funkcją IF w formule tablicowej.
DUŻE JEŚLI
Łącząc DUŻY (lub MAŁY) i JEŻELI w formule tablicowej, możemy zasadniczo utworzyć funkcję „DUŻA JEŻELI”, która działa podobnie do wbudowanej formuły SUMA.JEŻELI. Przejdźmy przez przykład.
Mamy listę ocen uzyskanych przez uczniów z dwóch różnych przedmiotów:
Załóżmy, że poproszono nas o znalezienie trzech najlepszych ocen uzyskanych z każdego przedmiotu w następujący sposób:
Aby to osiągnąć, możemy zagnieździć funkcję JEŻELI z Przedmiot jako nasze kryteria wewnątrz funkcji LARGE, takie jak:
=DUŻA(JEŻELI(=; ),)
=DUŻY(JEŻELI($C$2:$C$10=$F3;$D$2:$D$10);G$2)
W przypadku korzystania z programu Excel 2022 i starszego należy wprowadzić formułę, naciskając CTRL + SHIFT + ENTER aby uzyskać nawiasy klamrowe wokół wzoru.
Jak działa formuła?
Formuła działa, oceniając każdą komórkę w naszym zakresie kryteriów jako PRAWDA lub FAŁSZ.
Znalezienie najwyższej wartości oceny (k=1) w matematyce:
=DUŻY(JEŻELI($C$2:$C$10=$F3;$D$2:$D$10);G$2)
=DUŻA(JEŻELI({PRAWDA; FAŁSZ;FAŁSZ; PRAWDA; FAŁSZ; PRAWDA; FAŁSZ; PRAWDA; FAŁSZ}, {0,81; 0,8; 0,93; 0,42; 0,87; 0,63; 0,71; 0,58; 0,73}), 1)
Następnie funkcja IF zastępuje każdą wartość FALSE, jeśli jej warunek nie jest spełniony.
=DUŻY({0,81;FAŁSZ;FAŁSZ;0,42;FAŁSZ;0,63;FAŁSZ;0,58;FAŁSZ},1)
Teraz funkcja LARGE pomija wartości FALSE i oblicza największą (k=1) z pozostałych wartości (0,81 to największa wartość między 0,42 a 0,81).
MAŁY JEŚLI
Ta sama technika może być również zastosowana z funkcją SMALL.
=MAŁY(JEŻELI($C$2:$C$10=$F3;$D$2:$D$10);G$2)
DUŻY JEŚLI z wieloma kryteriami
Aby użyć funkcji DUŻA JEŻELI z wieloma kryteriami (podobnie jak działa wbudowana formuła SUMA.JEŻELI), po prostu zagnieżdż więcej funkcji JEŻELI w funkcji DUŻY, w następujący sposób:
=DUŻA(JEŻELI(=; JEŻELI(=;)))
=DUŻY(JEŻELI($D2:$D18=$H3;JEŻELI($B2:$B18=$G3;$E2:$E18)),I$2)
Innym sposobem na uwzględnienie wielu kryteriów jest pomnożenie kryteriów razem, jak pokazano w tym artykule
Porady i wskazówki:
- Jeśli to możliwe, zawsze odwołuj się do pozycji (k) z komórki pomocniczej i zablokuj odwołanie (F4), ponieważ ułatwi to automatyczne wypełnianie formuł.
- Jeśli używasz programu Excel 2022 lub nowszego, możesz wprowadzić formułę bez kombinacji klawiszy Ctrl + Shift + Enter.
- Aby wyszukać nazwiska uczniów, którzy osiągnęli najwyższe oceny, połącz to z funkcją DOPASUJ DO INDEKSU
