»11 czerwca 2011 r Dmitrij 243582 odsłon

Wyobraź sobie tabelę, w której nazwy departamentów (lub kont lub czegoś innego) są wymienione w wierszach w rzędzie.

Sumuj komórki według kryterium
Konieczne jest obliczenie całkowitej kwoty dla każdego działu. Wielu robi to za pomocą filtra i pisania długopisami w komórkach.
Chociaż można to zrobić łatwo i prosto za pomocą jednej funkcji - SUMMESLI .
SUMMESLES (SUMIF) - Zmniejsza liczbę komórek, które spełniają dany warunek (można określić tylko jeden warunek). Funkcji tej można również użyć, jeśli tabela jest podzielona na kolumny według okresów (miesięcznie, w każdym miesiącu, trzech kolumnach - Przychód | Wydatek | Różnica) i trzeba obliczyć łączną kwotę dla wszystkich okresów tylko według Dochodu, Wydatku i Różnicy.

Istnieją trzy argumenty w sumie dla SUMMESLI: Range , Criterion , Range_Summing .
= SUMMESLE (A1: A20000; A1; B1: B20000)
= SUMA (A1: A20000, A1, B1: B20000)

• Zakres (A1: A20000) - wskazuje zakres z kryteriami. To znaczy Kolumna, w której należy wyszukać wartość wskazaną przez argument Criterion .
• Kryterium (A1) jest wartością (tekstową lub liczbową, a także datą), którą należy znaleźć w zakresie . Może zawierać znaki wieloznaczne „*” i ��?”. To znaczy określając kryterium „* masa *”, aby podsumować wartości, w których występuje słowo „masa”. Jednocześnie słowo „masa” może pojawić się w dowolnym miejscu w tekście lub w komórce może znajdować się tylko jedno słowo. I określając „masa *”, wszystkie wartości zaczynające się od „masy” zostaną zsumowane. ��?” - zastępuje tylko jeden znak, tj. określając „mas? a” możesz zsumować linie o wartości „masa” i wartości „maska” itd.
  Jeśli kryterium jest zapisane w komórce i nadal musisz używać symboli wieloznacznych, możesz utworzyć łącze do tej komórki, dodając niezbędne. Załóżmy, że musisz zsumować wartości zawierające słowo „total”. Słowo „suma” jest zapisane w komórce A1, podczas gdy w kolumnie A mogą występować różne wartości pisowni zawierające słowo „ogółem”: „sumy za czerwiec”, „sumy za lipiec”, „sumy za marzec”. Formuła powinna wyglądać następująco:
  = LATO (A1: A20000; „*” i A1 i „*”; B1: B20000)
  „*” I A1 i „*” - znak & (ampersand) łączy kilka wartości w jedną. To znaczy wynikiem będzie „* wynik *”.
  Aby lepiej zrozumieć zasadę działania formuł, lepiej jest użyć narzędzia Obliczanie formuły : Jak wyświetlić kroki obliczania formuł
  Wszystkie kryteria i kryteria tekstowe z logicznymi i matematycznymi znakami muszą być ujęte w podwójne cudzysłowy (= SUMMESLI (A1: A20000; „total”; B1: B20000)). Jeśli kryterium jest liczbą, cytaty nie są wymagane. Jeśli chcesz bezpośrednio znaleźć znak zapytania lub gwiazdkę, musisz umieścić tyldę (~) przed nią.
  O tyldie i jej funkcjach można znaleźć w tym artykule: Jak zastąpić / usunąć / znaleźć gwiazdkę?
• Sum_Range (B1: B20000) (opcjonalny argument) - określa zakres sum lub wartości liczbowych do zsumowania.

Jak to działa: funkcja przeszukuje zakres dla wartości określonej przez argument Criterion , a gdy zostanie znalezione dopasowanie, sumuje dane wskazane przez argument Range_Amount. To znaczy jeśli mamy nazwę działu w kolumnie A i kwotę w kolumnie B, to określenie Departamentu Rozwoju jako kryterium spowoduje sumę wszystkich wartości kolumny B, naprzeciwko której znajduje się Departament Rozwoju w kolumnie A. W rzeczywistości, SumArrangement może nie być tego samego rozmiaru co argument Range i nie spowoduje to błędu samej funkcji. Jednak podczas definiowania komórek do sumowania, górna lewa komórka argumentu Range_Amount zostanie użyta jako komórka początkowa do sumowania, a następnie komórki odpowiadające wielkości i kształtu do argumentu Range zostaną zsumowane.

Niektóre funkcje
Ostatni argument funkcji (Sum_And_Band: B1: B20000) jest opcjonalny. Oznacza to, że nie można go określić. Jeśli go nie określisz, funkcja zsumuje wartości określone przez argument Range . Po co. Na przykład, musisz uzyskać sumę tylko tych liczb, które są większe od zera. W kolumnie A kwoty. Wtedy funkcja będzie wyglądać następująco:
= LATO (A1: A20000; "> 0")

Co należy wziąć pod uwagę: zakres_summing i zakres powinien być równy liczbie linii. W przeciwnym razie możesz uzyskać zły wynik. Optymalnie, jeśli będzie wyglądać jak w podanych przeze mnie formułach: zakres i zakres sumowania zaczynają się od jednej linii i mają taką samą liczbę linii: A1: A20000; B1: B20000

Podsumowanie dwóch lub więcej kryteriów
Ale co zrobić, gdy kryteria sumowania 2 i więcej? Załóżmy, że musisz zsumować tylko te kwoty, które należą do jednego działu i tylko na określoną datę. Szczęśliwi właściciele wersji Office 2007 i nowszych mogą korzystać z funkcji SUMMESLIMN:
= SUMMESLIMN ($ C $ 2: $ C $ 50; $ A $ 2: $ A $ 50; $ I $ 3; $ B $ 2: $ B $ 50; $ H8)
$ C $ 2: $ C $ 50 - zakres_summing. Pierwszy argument określa zakres komórek zawierających kwoty, które zostaną zebrane w jeden.
$ A $ 2: $ A $ 50, $ B $ 2: $ B $ 50 - Range_Criteria. Określa zakres komórek, w których chcesz wyszukać dopasowanie według kryterium.
$ I $ 3, $ H8 - kryterium. Tutaj, podobnie jak w SUMMESLI, dozwolone są znaki wieloznaczne * i ? i działają w ten sam sposób.

Specyfika określania argumentów: najpierw określany jest zakres kryterium (są one ponumerowane), następnie wartość (kryterium) jest wskazywana bezpośrednio średnikiem, który w tym zakresie musi być znaleziony - $ A $ 2: $ A $ 50; $ I $ 3. I nic więcej. Nie należy próbować najpierw określać wszystkich zakresów, a następnie kryteriów dla nich - funkcja będzie albo podawać błąd, albo nie będzie sumować tego, co jest konieczne.

Wszystkie warunki są porównywane zgodnie z zasadą I. Oznacza to, że jeśli wszystkie wymienione warunki są spełnione. Jeśli co najmniej jeden warunek nie jest spełniony, funkcja pomija linię i nic nie dodaje.
Jeśli chodzi o LATO, zakresy sumy i kryteriów powinny być równe liczbie wierszy.

Ponieważ SUMMESLIMN pojawił się tylko w wersjach programu Excel, począwszy od 2007 r., W jaki sposób nieszczęśliwi użytkownicy wcześniejszych wersji mogą być w takich przypadkach? Bardzo proste: użyj innej funkcji - SUMPRODUCT. Nie będę malować argumentów, ponieważ Jest ich wiele i wszystkie są tablicami wartości. Ta funkcja mnoży tablice wskazane przez argumenty. Spróbuję opisać ogólną zasadę używania tej funkcji do podsumowania danych w kilku warunkach.
Aby rozwiązać problem sumowania za pomocą kilku kryteriów, funkcja będzie wyglądać następująco:
= SUMPRODUCT (($ A $ 2: $ A $ 50 = $ I $ 3) * ($ B $ 2: $ B $ 50 = H5); $ C $ 2: $ C $ 50)
$ A $ 2: $ A $ 50 - zakres dat. $ I $ 3 to data kryterium, dla którego konieczne jest zsumowanie danych.
$ B $ 2: $ B 50 $ - nazwy działów. H5 - nazwa działu, na którym dane muszą zostać zsumowane.
2 C $: 50 C $ - zakres z kwotami.

Analizujemy logikę, ponieważ dla wielu będzie to całkowicie niejasne po prostu patrząc na tę funkcję. Jeśli tylko dlatego, że w pomocy ta aplikacja nie jest opisana. Aby zwiększyć czytelność, zmniejsz rozmiar zakresów:
= SUMPRODUCT (($ A $ 2: $ A 5 = $ I $ 3) * ($ B $ 2: $ B $ 5 = H5); $ C $ 2: $ C $ 5)
Tak więc wyrażenie ($ A $ 2: $ A $ 5 = $ I $ 3) i ($ B $ 2: $ B $ 5 = H5) są logicznymi i zwrotnymi tablicami logicznej FAŁSZ i PRAWDA. PRAWDA, jeśli komórka zakresu $ A $ 2: $ A 5 jest równa wartości komórki $ I $ 3, a komórka zakresu $ B $ 2: $ B 5 jest równa wartości komórki H5. To znaczy mamy następujące:
= SUMPRODUCT ({FALSE; TRUE; TRUE; FALSE} * {FALSE; FALSE; TRUE; FALSE}; 2 C $ $: 50 C $)
Jak widać, w pierwszej tablicy znajdują się dwa dopasowania warunku, aw drugim dwa. Ponadto te dwie tablice są mnożone (odpowiada za to znak mnożenia (*)). Gdy występuje mnożenie, następuje niejawna konwersja tablic FALSE i TRUE na stałe numeryczne 0 i 1 ({0; 1; 1; 0} * {0; 0; 1; 0}). Jak wiesz, po pomnożeniu przez zero otrzymujemy zero. Rezultatem jest pojedyncza tablica:
= SUMPRODUCT ({0; 0; 1; 0}; $ C $ 2: 50 C $)
Następnie tablica {0; 0; 1; 0} jest mnożona przez tablicę liczb w zakresie $ C $ 2: $ C $ 50:
= SUMPRODUCT ({0; 0; 1; 0}; {10; 20; 30; 40})
W rezultacie otrzymujemy 30. To, czego potrzebowaliśmy - dostajemy tylko kwotę, która spełnia kryterium. Jeśli jest więcej niż jedna suma spełniająca kryterium, zostaną one zsumowane.

Zaleta SUMMIR
Jeśli argumenty mają znak plus zamiast znaku mnożenia:
($ A $ 2: $ 5 $ = I $ 3 $) + ($ B 2: $ B 5 $ = H5)
wtedy warunki zostaną porównane zgodnie z zasadą OR: tj. łączne sumy zostaną zsumowane, jeśli spełniony zostanie co najmniej jeden warunek: albo $ A 2: $ A $ 5 jest równe wartości komórki I $ 3 lub komórce zakresu $ B 2: $ B 5 jest równe wartości komórki H5.
Jest to zaleta SUMMPRODUCT w porównaniu z SUMMESLIMN. SUMMESLIMN nie może sumować wartości zgodnie z zasadą OR, tylko zgodnie z zasadą AND (wszystkie warunki muszą być spełnione).

Wady
SUMPRODUCT nie może używać symboli wieloznacznych * i ?. Możliwe jest bardziej precyzyjne użycie, ale będą one postrzegane nie jako znaki specjalne, ale jako gwiazdka i znak zapytania. Myślę, że to poważna wada. I chociaż można to ominąć, używam innych funkcji wewnątrz SUMPRODUCT - nadal byłoby świetnie, gdyby funkcja mogła w jakiś sposób używać symboli wieloznacznych.

W przykładzie znajdziesz kilka przykładów funkcji dla lepszego zrozumienia tego, co zostało napisane powyżej.

Pobierz przykład

Kwota według kilku kryteriów (41,5 KiB, 10 477 pobrań)

Zobacz także:
Sumowanie komórek według koloru wypełnienia
Sumowanie komórek według koloru czcionki
Sumowanie komórek według formatu komórki
Oblicz ilość komórek według koloru wypełnienia
Oblicz ilość komórek według koloru czcionki
Jak zsumować dane z kilku arkuszy, w tym według warunków

Samouczki wideo

{"Dolny pasek": {"textstyle": "static", "textpositionstatic": "bottom", "textautohide": true, "textpositionmarginstatic": 0, "textpositiondynamic": "bottomleft", "textpositionmarginleft": 24, " textpositionmarginright „: 24,„ textpositionmargintop ”: 24,„ textpositionmarginbottom ”: 24,„ texteffect ”:„ slide ”,„ texteffecteasing ”:„ easeOutCubic ”,„ texteffectduration ”: 600,„ texteffectslidedirection ”:„ left ”,„ texteffectslidedirection ” : 30, „texteffectdelay”: 500, „texteffectseparate”: false, „texteffect1”: „slide”, „texteffectslidedirection1”: „right”, „texteffectslidedistance1”: 120, „texteffecteasing1”: „easeOutCubic”, „texteffectduration1”: 600 , „texteffectdelay1”: 1000, „texteffect2”: „slide”, „texteffectslidedirection2”: „right”, „texteffectslidedistance2”: 120, „texteffecteasing2”: „easeOutCubic”, „texteffectduration2”: 600, „texteffectdelay2”: 1500 ” textcss ":" display: block; padding: 12px; text-align: left; "," textbgcss ":" display: block; position: absolute; top: 0px; left: 0px; width: 100%; height: 100% ; kolor tła: # 333333; nieprzezroczystość: 0,6; filtr: a lpha (krycie = 60); „,” tytuł „”: display: blok; pozycja: względna; czcionka: pogrubiona 14px „Lucida Sans Unicode”, „Lucida Grande”, sans-serif, Arial; color: #fff; "," descriptioncss ":" display: block; pozycja: względna; czcionka: 12px „Lucida Sans Unicode”, „Lucida Grande”, sans-serif, Arial; kolor: #fff; margin-top: 8px; "," buttoncss ":" display: block; pozycja: względna; margin-top: 8px; "," texteffectresponsive ": true," texteffectresponsivesize ": 640," titlecssresponsive ":" font-size: 12px; "," descriptioncssresponsive ":" display: none: important; "," buttoncssresponsive ": „”, „addgooglefonts”: false, „googlefonts”: „”, „textleftrightpercentforstatic”: 40}}

Szukaj według tagów

Idź Dostęp zegarek na jabłko Multex Outlook Power Query and Power BI Praca VBA w edytorze Zarządzanie kodem VBA Darmowe dodatki Data i godzina Wykresy i wykresy Uwagi Ochrona danych Internet Obrazki i obiekty Arkusze i książki Makra i VBA Dodatki Dostosowywanie Drukuj Wyszukaj dane Polityka prywatności Poczta Programy Pracuj z aplikacjami Pracuj z plikami Tworzenie aplikacji Tabele podsumowujące Listy Szkolenia i webinaria Finansowe Formatowanie Wzory i funkcje Funkcje Excela Funkcje VBA Komórki i zakresy Akcje MulTEx analiza danych błędy i usterki w programie Excel linki