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:
Linia 1. znak równości SUMA otwórz nawias okrągły MAX kropka K otwórz nawias okrągły zakres średnik WIERSZ otwórz nawias okrągły ADR kropka POŚR otwórz nawias okrągły cudzysłów 1 dwukropek cudzysłów ampersant x zamknij nawias okrągły zamknij nawias okrągły zamknij nawias okrągły zamknij nawias okrągły.
=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:
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:
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.
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
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.
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")))).
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
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).
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
Polecenie 2
Rozbuduj skonstruowany arkusz, a następnie zbadaj, ile karmy zebrało razem 25% klas, które uzbierały jej najmniej.
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)))).
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.