Suma liczb oprócz kilku najmniejszych/największych
- Autor: Mirosław Zalewski
- 23 kwietnia 2013
- komentarzy 10
LibreOffice potrafi w mgnieniu oka policzyć sumę wszystkich liczb w podanym zakresie. Co jednak w sytuacji, gdy chcielibyśmy poznać sumę liczb za wyjątkiem kilku krańcowych? Artykuł opisuje, w jaki sposób to zrobić.
Podstawą jest funkcja SUMA.JEŻELI, która oblicza sumę wszystkich komórek w zakresie spełniających określony warunek. Nas interesują liczby większe niż (dajmy na to) trzecia najmniejsza.
Znajdziemy ją przy pomocy funkcji MIN.K, która zwraca k-tą najmniejszą liczbę w zakresie podanym jako argument. Gdybyśmy chcieli wykluczyć największe liczby ze zbioru, skorzystalibyśmy z analogicznej funkcji MAKS.K.
Gotowa formuła wygląda następująco:
=SUMA.JEŻELI(A1:A100;">"&MIN.K(A1:A100;3))
Drugi argument wykorzystanej funkcji musi być ciągniem znaków. Ponieważ pomiędzy cudzysłowami nie można wykorzystywać formuł, do dynamicznego stworzenia kryterium wykorzystaliśmy operator konkatenacji (&).
Sprawa się komplikuje
Jednak podana wyżej formuła nie sprawdzi się, jeżeli liczby w naszym zbiorze mogą się powtarzać. W takim wypadku musimy przede wszystkim określić, czy interesuje nas wykluczenie skrajnych liczb, czy skrajnych wartości.
Przyjrzyjmy się różnicom pomiędzy nimi na przykładzie poniższej bazy danych.
Suma wszystkich liczb w kolumnie A wynosi 42. Trzy najmniejsze liczby to 1, 1 i 2, a ich suma wynosi 4. Z kolei trzy najmniejsze wartości to 1, 2 i 3, a suma liczb o tych wartościach wynosi 12. Tak więc suma po wykluczeniu trzech najmniejszych liczb wynosi 38, zaś suma po wykluczeniu trzech najmniejszych wartości wynosi 30. Różnica jest raczej duża.
Wykluczanie najmniejszych/największych liczb
W tym celu najpierw obliczymy sumę wszystkich liczb w danym zakresie, a następnie odejmiemy od niej sumę kilku skrajnych liczb.
Najprostszym rozwiązaniem jest wykorzystanie tylu komórek roboczych, ile skrajnych liczb chcemy wykluczyć. Przy pomocy funkcji MIN.K (lub MAKS.K) w pierwszej umieścimy najmniejszą liczbę, w drugiej drugą najmniejszą, w trzeciej trzecią najmniejszą itd. Następnie zsumujemy je wszystkie i uzyskaną wartość odejmiemy od sumy wszystkich liczb w zbiorze.
Jednak ten sam efekt można uzyskać przy pomocy jednej tylko komórki. Otóż jeżeli funkcja MIN.K zostanie wprowadzona jako formuła macierzowa, jej drugim argumentem może być lista kolejnych liczb do wypisania. Normalnie wynik takiej funkcji zajmuje tyle komórek, ile liczb chcemy uzyskać; możemy go jednak od razu podać jako argument funkcji SUMA. Wtedy pozostaje nam już tylko policzyć sumę wszystkich komórek w zbiorze.
Gotowa formuła wygląda następująco:
=SUMA(A1:A12)-SUMA(MIN.K(A1:12;{1;2;3}))
Pamiętajmy, aby zatwierdzić ją za pomocą skrótu klawiaturowego Shift + Ctrl + Enter, w przeciwnym wypadku nie zadziała.
Jeżeli chcemy wykluczyć inną liczbę skrajnych liczb, powinniśmy zmodyfikować drugi argument funkcji MIN.K. Gdy chcemy wykluczyć największe liczby, oczywiście powinniśmy wykorzystać funkcję MAKS.K.
Wykluczanie najmniejszych/największych wartości
O ile mi wiadomo, tego problemu nie można rozwiązać za pomocą jednej tylko formuły. Ponadto opisany niżej sposób zadziała tylko wtedy, gdy nasz zbiór danych znajduje się w jednej kolumnie.
Ponieważ funkcje MIN.K i MAKS.K pracują na liczbach, a nie wartościach, pierwszym krokiem jest usunięcie duplikatów liczb ze zbioru. Dzięki temu usuniemy różnice między liczbą a wartością. Wykorzystamy do tego dodatkową kolumnę arkusza, najlepiej znajdującą się obok kolumny z naszymi danymi.
Istnieją dwa sposoby na wyodrębnienie unikatowych wartości ze zbioru.
Filtr
Jedną jest skorzystanie z filtra. Jest ona prostsza, ale nie sprawdza się, jeżeli nasz zbiór może się powiększać i zmieniać.
Przede wszystkim zaznaczamy kolumnę z danymi. Następnie wybieramy z menu Dane → Filtr → Filtr standardowy.... Pojawi się nowe okno. Z listy Wartość wybieramy Niepusty, a następnie klikamy Więcej opcji i zaznaczamy pola zatytułowane Bez duplikatów oraz Kopiuj wyniki do.... W polu w prawym dolnym rogu wpisujemy adres komórki, poniżej której chcemy mieć wyświetlone wartości pojawiające się w zbiorze.
Formuła
Drugą możliwością jest wykorzystanie formuły, która sprawdzi, czy dana wartość ze zbioru znajduje się już w kolumnie roboczej. Jeżeli nie, to ją do niej doda; jeżeli tak, nic nie pokaże. Formuła taka jest całkiem skomplikowana, ale automatycznie będzie uwzględniała wszelkie zmiany w zbiorze danych.
Do jej skonstruowania wykorzystamy funkcję WYSZUKAJ.PIONOWO, której działanie szerzej opisałem w artykule o tworzeniu dzienniczka ucznia (fragment „Obliczanie ocen końcowych”).
W pierwszej komórce kolumny roboczej po prostu wstawiamy odniesienie do pierwszej komórki kolumny z danymi (pierwsza wartość jest jedyna, więc również unikatowa). W drugiej zaś wprowadzamy formułę:
=JEŻELI(CZY.BŁĄD(WYSZUKAJ.PIONOWO(A2;$B$1:B1;1;0));A2;"")
Następnie „przeciągamy” ją w dół kolumny roboczej, aż do końca liczb w kolumnie z danymi.
Funkcja WYSZUKAJ.PIONOWO poszukuje wartości z sąsiedniej komórki wśród wartości unikatowych od pierwszego wiersza do wiersza tuż nad aktualną komórką. Jeżeli wartość ta nie zostanie znaleziona, funkcja zwróci błąd. Jeżeli tak, wtedy cała formuła zwróci poszukiwaną wartość (liczbę z sąsiedniej kolumny). Gdy błąd nie wystąpi, zwrócony zostanie pusty ciąg znaków.
Właściwe obliczenia
Gdy już w kolumnie roboczej znajdą się unikatowe liczby z kolumny z danymi, pozostaje nam wprowadzić formułę obliczającą sumę warunkową (taką samą jak opisaną na początku artykułu). Pamiętajmy tylko, że argumentem funkcji MIN.K (MAKS.K) musi być kolumna robocza, a nie kolumna z sumowanymi danymi.
=SUMA.JEŻELI(A1:A12;">"&MIN.K(B1:B12;3))
Mirosławie,
mój Libre Office 3.6.2 nie zna funkcji min.k.
Napisałem w arkuszu w komórkach A4,A5,A6 kolejno wartości 3,5,7 i w A7 napisałem:
=min.k(A4:A6;1).
Libre Office zamienił mi średnik na przecinek, a rezultat to #NAME?.
Co robię nie tak?
PS. Używam Libre Office w wersji angielskiej.
U mnie ta funkcja działa zarówno w LO 3.6.5, jak i starszym 3.5.6, trudno więc określić, co dokładnie jest przyczyną tego problemu. Niemniej mam kilka propozycji, w którym kierunku można iść.
1. Istnieje marginalna szansa, że w linii 3.6 został wprowadzony błąd, który został naprawiony dopiero w 3.6.3 lub późniejszej. W takim wypadku rozwiązaniem będzie aktualizacja do najnowszej wersji 3.6.6. Ogólnie rzecz ujmując, regresje w ramach jednej linii są sporadyczne, więc aktualizacja i tak jest dobrym pomysłem.
2. Przecinek jest separatorem argumentów funkcji w programie MS Excel. Czy plik, na którym pracujesz, jest w formacie XLS/XLSX? Jeżeli tak, to powinno pomóc zapisanie go w natywnym ODS. Ja nie mam zaufania do obsługi formatów Microsoftu przez LibreOffice i ogólnie zalecam pracowanie w formacie natywnym (OpenDocument), a dopiero na końcu wyeksportowanie pliku do formatu MS Office.
2a. W Narzędzia → Opcje → LibreOffice Calc → Formuła można określić, jaki symbol ma być separatorem argumentów funkcji. Może zmiana tego ustawienia wywołuje błąd?
3. Jeżeli cały LibreOffice (a nie tylko interfejs) jest w języku angielskim, to odpowiednia funkcja nazywa się SMALL, a nie MIN.K. Analogicznie zamiast MAKS.K trzeba użyć LARGE. Za ten brak intuicyjności winić należy raczej tłumaczy z Microsoftu, gdyż tłumacze LibreOffice (i wcześniej OpenOffice.org) starają się tłumaczyć nazwy funkcji w taki sposób, w jaki przetłumaczone one zostały w MS Excel.
Po pierwsze, dlaczego komentujący nie zainstaluje sobie najnowszej wersji LibreOffice. Przecież nie musi nic do tego dopłacać. To jest za darmo i może używać tego wszędzie, naprawdę wszędzie, w domu, w szkole, w pracy, na budowie i w wielu innych miejscach.
Po drugie, w wersji angielskiej nawet tak dziwnie nazywająca się funkcja może nazywać się inaczej.
Po trzecie, nie wiem po co MS zaczął lokalizować nazwy funkcji. Przez to wielki bajzel zrobił się.
O Panie dzięki Ci za twe rady cudne!
Nie wiem jak ja to robiłem czytając naście razy helpa że tych funkcji w spisie nie dostrzegłem
Wszak tylko człekiem jestem
Naprawdę szczerzę dziękuje za pomoc, dla mnie jest to masa pracy mniej. Wielki szacunek za to co robisz!
Pozdrawiam serdecznie
Jacek Rachwał
Świetny blog. Dzięki.
Mam jeden problem – o ile rewelacyjnie zlicza z wyłączeniem 3 liczb z 10 w zakresie A1:A10
formułą =SUMA.JEŻELI(A1:A10;”>”&MIN.K(A1:A10;3)) o tyle mam problem jak skonstruować formułę jeśli chcę zsumować z wyłączeniem 3 najniższych z rozproszonych komórek (np A1, B2, C5, A3, D8 itp)
Nie da się tak. Funkcja SUMA.JEŻELI potrafi pracować tylko na prostokątnym, „ciągłym” zakresie danych. Na rozproszonym nie potrafi rozróżnić argumentów (co jest elementem zakresu, a co kryterium) i się gubi.
Osobiście bym się przede wszystkim zastanowił, dlaczego te komórki są rozproszone. Może chodzi o zliczenie warunkowej sumy wśród komórek, które spełniają jakieś tam kryterium? W takim wypadku można skonstruować formułę która liczy sumę dla wartości spełniających kilka warunków.
Jeżeli rozproszenie nie następuje według żadnego wzorca, to pozostaje chyba tylko odwołanie się do tych komórek w prostokątnym zakresie nieużywanej części arkusza. Np. w komórce AA1 wstawiamy formułę =A1, w komórce AA2 — =B2 i tak dalej. Potem można przepuścić SUMA.JEŻELI na zakresie AA1:AA100. Oczywiście ten sposób ma mnóstwo wad i może się rozlecieć w każdym momencie, ale lepszego nie widzę.
W dokumentach google przechodzi to taką formułą
=SUM(large({C4;E4;G4;I4;K4;M4;O4;Q4;S4;U4};1);large({C4;E4;G4;I4;K4;M4;O4;Q4;S4;U4};2);large({C4;E4;G4;I4;K4;M4;O4;Q4;S4;U4};3);large({C4;E4;G4;I4;K4;M4;O4;Q4;S4;U4};4);large({C4;E4;G4;I4;K4;M4;O4;Q4;S4;U4};5);large({C4;E4;G4;I4;K4;M4;O4;Q4;S4;U4};6);large({C4;E4;G4;I4;K4;M4;O4;Q4;S4;U4};7))
Być może. Calc jednak w ręcznie tworzonych macierzach przyjmuje jako wartości jedynie liczby i tekst (a nie odwołania do innych komórek i funkcje), więc ta formuła zwróci błąd.
Ale z tego co widzę, chodzi tutaj o co drugą komórkę w zakresie C4:U4. Można więc powrócić do pomysłu liczenia sumy dla komórek spełniających kilka kryteriów.
Pierwszym warunkiem jest parzystość (podzielność przez 2). To akurat można załatwić dzięki funkcjom MOD i KOLUMNA. Drugim warunkiem jest „większa niż trzecia najmniejsza liczba w zakresie”. Tutaj sprawa komplikuje się o tyle, że nasza macierz liczb w parzystych kolumnach zawiera zera w miejscu liczb w nieparzystych kolumnach. W rezultacie proste:
Zwróci nam zero. Dlatego musimy odczytać wartość najmniejszą z uwzględnieniem tych X zer, które są w zbiorze. Można do tego wykorzystać LICZ.JEŻELI. Albo, jeżeli zbiór jest względnie niezmienny, wpisać na sztywno że potrzebna nam jest 10+3 najmniejsza liczba.
W każdym razie, gotowa formuła wygląda tak:
Dzieki! Bardzo mi dzisiaj pomogles! 🙂
A ja mam Pytanie. Jak zrobić aby z 4 liczb Np(4,5 i 3 i 2 i 3) odrzucił jedna największą i jedną najmniejszą a resztę zsumował ?
w tym przypadku odrzucamy 4,5 i 2 wynik to 6
Lub (3 3 3 3) 2 trójki są jednocześnie największą i najmniejszą liczba wiec je odrzucamy i wynik to 6
albo (5 3 4 5 ) odrzucamy 5 i 3 wynik to 9