Obliczanie sumy kilku najmniejszych i największych wartości

Analiza danych wymaga szczególnego zwrócenia uwagi na wartości w sposób znaczący odbiegające od przeciętnych, ponieważ mogą one decydować o ostatecznym wyniku. O ile pojedyncze wartości ekstremalne (minimum i maksimum) mogą być przypadkowe, o tyle kilka kolejnych wartości skrajnych może być najistotniejszych dla całego zestawienia.

Konstrukcja formuły tablicowej, dzięki której wyliczysz sumę x kolejnych wartości maksymalnych, jest następująca:

=SUMA(MAX.K(zakres;WIERSZ(ADR.POŚR("1:"&x))))

W celu obliczenia sumy kilku kolejnych wartości najmniejszych zastosuj funkcję MIN.K() zamiast MAX.K(). Formuła wymaga podania jedynie zakresu z danymi oraz liczby (x) kolejnych wartości skrajnych, które chcesz uwzględnić w obliczeniach. Jeśli chcesz podać parametr x bezpośrednio, a nie jako odniesienie, musisz go umieścić w cudzysłowie.

Formuła zliczająca x wartości największych w zakresie B3:B10 wygląda następująco:

=SUMA(MAX.K(B3:B10;WIERSZ(ADR.POŚR("1:"&B12))))

Po jej wprowadzeniu uzyskasz pożądany wynik.

Pamiętaj, że do poprawnego funkcjonowania każdej formuły tablicowej niezbędne jest zatwierdzenie jej z użyciem kombinacji klawiszy [Ctrl]+[Shift]+[Enter].

W celu obliczenia sumy x wartości najmniejszych wykorzystana została formuła:

=SUMA(MIN.K(B3:B10;WIERSZ(ADR.POŚR("1:"&B12))))

Wyjaśnienie działania formuły:

Działanie funkcji MAX.K() sprowadza się do wyszukania k‑tej największej wartości. Określenie parametru k następuje w kombinacji funkcji WIERSZ() i ADR.POŚR(). Kombinacja ta, z uwzględnieniem parametru wprowadzanego w komórce B12, generuje zakres od: do, jaki przyjmować będzie parametr k.

Jako że jest to formuła tablicowa, następują w niej wielokrotne obliczenia: parametr k w funkcji MAX.K() będzie przyjmował wartości od 1 do x zdefiniowanego w komórce B12. Po każdorazowym przebiegu funkcja MAX.K() będzie przekazywać do funkcji SUMA() kolejną k‑tą największą wartość z zakresu danych. Ostateczny wynik wyświetlony będzie dzięki działaniu funkcji SUMA(). Dla funkcji MIN.K() zasada działania formuły nie ulega zmianie.

RvsTLGf6nBpMt

Nagranie filmowe dotyczące obliczania sumy kilku najmniejszych i największych wartości.

Nagranie filmowe dotyczące obliczania sumy kilku najmniejszych i największych wartości.

Film dostępny pod adresem /preview/resource/RvsTLGf6nBpMt

Nagranie filmowe dotyczące obliczania sumy kilku najmniejszych i największych wartości.

Pobierz plik z danymi:

Polecenie 1

Załóżmy, że wszystkie klasy w twojej szkole zbierały karmę dla zwierząt z pobliskiego schroniska. Przygotuj arkusz zawierający zestawienie wszystkich wyników, a następnie sprawdź, ile razem zebrały trzy najlepsze klasy. Oblicz, ile procent całości zebranej karmy stanowi ich wynik.

Pokaż rozwiązanie

Przykładowe rozwiązanie zadania:

Rp28WSqR95vk9

Na zrzucie ekranu widoczny jest fragment arkusza Excel. W arkuszu kolejno dodano opisy: w komórce A1 klasa, w komórce B1 ilość zebranej karmy. W kolumnie A, w komórkach od A2 do A13 wpisano kolejne klasy. W kolumnie B, w komórkach od B2 do B13 wpisano wartości liczbowe zebranej karmy. Obok danych kolumnę D3 zatytułowano Ile procent całości?. W komórce D4 wpisano 40,41 procent. Dodatkowo zaprezentowano sposób obliczania zbiórki trzech najlepszych klas. Formułę obliczania należy wpisać w odpowiedniej komórce. W tym przypadku w komórce D2. Brzmi ona następująco: =SUMA(MAX.K(B2:B13;WIERSZ(ADR.POŚR("1:3")))).

R1XyuwJX4yAhO

Na zrzucie ekranu widoczny jest fragment arkusza Excel. W arkuszu kolejno dodano opisy: w komórce A1 klasa, w komórce B1 ilość zebranej karmy w kilogramach. W kolumnie A, w komórkach od A2 do A13 wpisano kolejne klasy. W kolumnie B, w komórkach od B2 do B13 wpisano wartości liczbowe zebranej karmy. Obok danych kolumnę D1 zatytułowano Trzy najlepsze klasy zebrały. W komórce D2 wpisano liczbę 219. Dodatkowo zaprezentowano sposób obliczania procent całości. W komóce D3 wpisano tytuł Ile procent całości? W tym przypadku w komórce D4. Brzmi ona następująco: =D2/SUMA(B2:B13).

Polecenie 2

Rozbuduj skonstruowany arkusz, a następnie zbadaj, ile karmy zebrało razem 25% klas, które uzbierały jej najmniej.

Pokaż rozwiązanie

Przykładowe rozwiązanie zadania:

R15YAuFU0wJiA

Na zrzucie ekranu widoczny jest fragment arkusza Excel. W arkuszu kolejno dodano opisy: w komórce A1 klasa, w komórce B1 ilość zebranej karmy w kilogramach. W kolumnie A, w komórkach od A2 do A13 wpisano kolejne klasy. W kolumnie B, w komórkach od B2 do B13 wpisano wartości liczbowe zebranej karmy. Obok danych kolumnę D1 zatytułowano Trzy najlepsze klasy zebrały. W komórce D2 wpisano liczbę 219. Komórkę D3 zatytułowano: Ile procent całości?, w komórce D4 wpisano wartość 40,41 procent. Komórkę D6 zatytułowano 25 procent klas, w komórce D7 wpisano liczbę 3. Dodatkowo zaprezentowano sposób obliczania sumy 25 procent klas, które zebrało najmniej. Formułę obliczania należy wpisać w odpowiedniej komórce. W tym przypadku w komórce D9. Brzmi ona następująco: =SUMA(MIN.K(B2:B13;WIERSZ(ADR.POŚR("1:"$D7)))).