Jak korzystać z LICZ., SUMA.WARUNKÓW, ŚREDNICH.JEŻELI w programie Excel

Trzy najczęściej używane formuły w programie Excel, które wykonują proste obliczenia matematyczne, to LICZBA , SUMA i ŚREDNIA . Niezależnie od tego, czy zarządzasz budżetem finansowym w Excelu, czy po prostu śledzisz najbliższe wakacje, prawdopodobnie korzystałeś już z jednej z tych funkcji.

W tym artykule omówimy podstawy tych trzech funkcji oraz ich odpowiednich i użytecznych odpowiedników: LICZ.WARUNKI, SUMA.WARUNKÓW i ŚREDNIE.JEŻELI.

Załóżmy, że otwieramy nowy biznes internetowy zajmujący się sprzedażą telefonów komórkowych i mamy arkusz z listą sprzedaży zrealizowanej w ciągu pierwszych dwóch miesięcy. Pobierz przykładowy arkusz kalkulacyjny Excel tutaj .

Excel LICZENIE, SUMA i ŚREDNIA

Aby dowiedzieć się, ile sprzedaliśmy telefonów komórkowych, możemy szybko skorzystać ze wzoru na LICZĘ , jak pokazano poniżej:

=LICZ.(E2:E16)

Z drugiej strony, aby uzyskać całkowitą kwotę sprzedaży, którą zrealizowaliśmy, możemy użyć formuły SUMA , jak pokazano poniżej:

=SUMA(E2:E16)

Na koniec, aby poznać średnią sprzedaż wszystkich telefonów, możemy skorzystać ze wzoru ŚREDNIA , jak poniżej:

=ŚREDNIA(E2:E16)

Wynik powinien być jak poniżej:

Formuły LICZBA, SUMA i ŚREDNIA działają tylko w przypadku rekordów, w których wartość komórki jest w formacie liczbowym. Każdy rekord w zakresie formuły (tj. E2:E16 w tym przykładzie), który nie jest w formacie liczbowym, zostanie zignorowany.

Dlatego upewnij się, że wszystkie komórki w formule LICZBA, SUMA i ŚREDNIA są sformatowane jako Liczba , a nie Tekst . Spróbuj użyć tej samej formuły, ale z zakresem E:E zamiast E2:E16 . Zwróci ten sam wynik co poprzednio, ponieważ ignoruje nagłówek (tj. Sale Price ), który jest w formacie tekstowym.

A co, jeśli chcemy poznać liczbę sprzedaży, całkowitą kwotę sprzedaży i średnią kwotę sprzedaży na telefon, tylko dla tych sprzedawanych w USA? Tutaj ważną rolę odgrywają LICZ., SUMA.WARUNKÓW i ŚREDNIE.JEŻELI. Przestrzegaj poniższego wzoru:

LICZNIKI

Podział formuły:

1. =LICZ.WARUNKI( – Znak „=” wskazuje początek formuły w komórce, a LICZ.WARUNKI to pierwsza część używanej przez nas funkcji programu Excel.
2. D2:D16 — Odwołuje się do zakresu danych, aby sprawdzić, czy spełnia on kryteria uwzględnienia w formule zliczania.
3. „USA” – Kryteria do wyszukania w podanym zakresie danych ( D2:D16 )
4. ) – Nawias zamykający oznaczający koniec formuły.

Formuła zwraca 6, czyli liczbę sprzedaży produktów wysyłanych z magazynu w USA.

SUMY

Podział formuły:

1. =SUMA.WARUNKÓW( – Znak „=” ponownie wskazuje początek formuły.
2. E2:E16 – Odnosi się do zakresu danych, które chcielibyśmy zsumować, czyli ceny sprzedaży w naszym przykładzie.
3. D2:D16 – Odnosi się do zakresu danych do sprawdzenia, czy spełnia kryteria uwzględnienia go w łącznej kwocie.
4. „USA” – Kryteria do wyszukania w podanym zakresie danych ( D2:D16 )
5. ) – Nawias zamykający oznaczający koniec formuły.

Formuła pokazuje łączną sprzedaż w wysokości 6050 USD dla produktów wysłanych z magazynu w USA.

ŚREDNIE

Podział formuły:

1. =ŚREDNIEJEŻELI( – Znak „=” oznacza początek formuły.
2. E2:E16 – Odnosi się do zakresu danych, które chcielibyśmy uśrednić. W tym przykładzie chcemy uzyskać średnią wielkość sprzedaży dla wszystkich telefonów sprzedawanych w USA.
3. D2:D16 – Odwołuje się do zakresu danych, aby sprawdzić, czy spełnia on kryteria uwzględnienia w formule średniej.
4. „USA” – Kryteria do wyszukania w podanym zakresie danych
5. ) – Nawias zamykający wskazujący końce formuły.

Formuła pokazuje, że sprzedaliśmy produkt za około 1008 USD za telefon w USA.

Wszystkie trzy formuły mogą przyjmować więcej niż jedno kryterium. Na przykład, jeśli chcemy poznać te same liczby (tj. LICZBA , SUMA i ŚREDNIA ) dla produktów sprzedawanych w USA , ale konkretnie tylko dla  marki Samsung , wystarczy dodać zakres danych do sprawdzenia, a następnie jego kryteria.

Proszę zapoznać się z poniższym przykładem, w którym do wstępnej kontroli kryteriów dodano drugie kryterium. (Niebieski tekst oznacza pierwsze kryterium, a czerwony oznacza drugie kryterium)

=LICZ.WARUNKI(D2:D16;„USA”, B2:B16;„Samsung”) =SUMA.WARUNKÓW(E2:E16;D2:D16;„USA”, B2:B16;„Samsung”) =ŚREDNIA.WARUNKÓW(E2:E16;D2 :D16,"USA", B2:B16,"Samsung")

Zauważysz, że Excel ma również  formuły LICZ.JEŻELI , SUMA.JEŻELI i ŚREDNIA.JEŻELI bez sufiksu „S” . Są one używane podobnie jak LICZ.WARUNKI , SUMA.WARUNKÓW i ŚREDNIE.WARUNKI . Jednak te bez sufiksu „S” we wzorze mają ograniczenie polegające na dopuszczaniu tylko jednego kryterium na wzór.

Ponieważ składnia jest nieco inna, zalecałbym używanie  funkcji COUNTIFS , SUMIFS i AVERAGEIFS tylko dlatego, że można jej użyć dla jednego lub więcej kryteriów, jeśli to konieczne. Cieszyć się!