Jak korzystać z SUMIF, SUMIFS w Arkuszach Google

Funkcje SUMIF i SUMIFS Arkuszy Google pomagają analizować, organizować i sumować dane na podstawie określonych kryteriów.

Te formuły sumują wartości komórek, gdy spełniają ustawione przez Ciebie wymagania.

Niedawno zrobiłem to dla firmy internetowej, która chciała zagłębić się w swoje raporty o ruchu.

Wyeksportowałem dzienny ruch w witrynie firmy według kanałów z oprogramowania analitycznego i zaimportowałem te dane do Arkusza Google, który zapisałem jako „Dzienny ruch”.

Następnie zagregowałem dane: jeden wiersz na kanał dziennie. Firma otrzymuje ruch z około dziewięciu źródeł. Tak więc od 1 stycznia do 28 marca (87 dni) arkusz kalkulacyjny zawierał 748 wierszy z sumą ruchu w kanałach. To było za dużo informacji, aby przejrzeć je ręcznie.

Import danych zaowocował 748 wierszami łącznych wartości ruchu w kanale.

Korzystając z przykładowych danych, przyjrzyjmy się, jak formuły SUMA.JEŻELI i SUMIFS mogą pomóc w analizie ruchu w witrynie, a w szczególności:

• Ile ruchu wygenerował każdy kanał w całym 87-dniowym okresie?
• Ile miesięcznie generował każdy kanał?

SUMA

Zaimportowane informacje o ruchu mają trzy kolumny: data, kanał i liczba unikalnych wizyt dla każdego kanału w danym dniu.

Dane źródłowe mają w arkuszu trzy kolumny: Data, Kanał i Ruch w witrynie.

Moim pierwszym zadaniem jest ustalenie, jaki ruch wygenerował każdy kanał przez całe 87 dni.

Na początek utworzyłem arkusz o nazwie „Całkowity ruch według kanału” z dwiema kolumnami: „Kanał” i „Całkowity ruch”.

Nowy arkusz Całkowity ruch według kanału zawiera sumy ruchu dla każdego kanału w całym okresie.

Użyłem formuły SUMA.JEŻELI, aby uzyskać całkowity ruch z każdego kanału dla całego zakresu dat. Formuła przyjmuje dwa lub trzy parametry.

Wersja dwuparametrowa zawiera „zakres” i „kryterium”.

 =SUMA.JEŻELI(zakres; kryterium)

W tym przypadku zakres to miejsce, w którym formuła będzie szukać kryterium i komórek do zsumowania.

W tym przykładzie potrzebujemy wersji trzyparametrowej, która dodaje „zakres sumy”. Ten zakres to miejsce, w którym Arkusze Google będą wyglądać, gdy spełni kryterium. Zakres sumy reprezentuje komórki, które mają zostać zsumowane.

 =SUMA.JEŻELI(zakres; kryterium; suma zakresu)

Użyję teraz wzoru, aby uzyskać ruch z kanału afiliacyjnego.

Gdy zaczynam wpisywać „=SUMA.JEŻELI” na pasku formuły, Arkusze Google udostępniają opcję wyboru formuły SUMA.JEŻELI.

Arkusze Google sugerują SUMIF jeszcze przed zakończeniem pisania.

Ponieważ wybrałem sugerowaną formułę SUMA.JEŻELI, Arkusze Google pokazały mi przewodnik. Mogę poruszać się po skoroszycie i w razie potrzeby wybierać kolumny z arkusza Dzienny ruch.

Wybranie sugerowanej funkcji SUMA.JEŻELI upraszcza proces tworzenia formuły.

Przechodzę do arkusza „Dzienny ruch” i wybieram kolumnę B.

Następnie muszę zdefiniować moje kryterium, którym jest słowo „Partner”. Ponieważ mam już to słowo w moim nowym arkuszu Całkowity ruch według kanału, mogę użyć odwołania do komórki A3.

Kliknięcie komórki A3 dodaje jej wartość „Stowarzyszony” do formuły SUMA.JEŻELI jako kryterium.

Na koniec wracam do arkusza Daily Traffic i wybieram kolumnę C, zakres sumy. Jeśli kolumna B pasuje do mojego kryterium „Podmiot stowarzyszony”, wartość w kolumnie C tego samego wiersza zostanie dodana do sumy.

Jeśli kolumna B pasuje do mojego kryterium „Stowarzyszony”, wartość w kolumnie C tego samego wiersza zostanie dodana do sumy.

Oto ukończona formuła.

 =SUMA.JEŻELI('Ruch dzienny'!B:B,A3;'Ruch dzienny'!C:C)

Tak po prostu wiemy, że kanał partnerski odnotował 53 875 wizyt w witrynie od 1 stycznia do 28 marca.

Formuła SUMIF zadziałała. Kanał partnerski odnotował 53 875 odwiedzin od 1 stycznia do 28 marca.

Mogę chwycić dolny róg tej komórki i przeciągnąć formułę w dół, aby uzyskać sumy dla każdego z pozostałych kanałów.

Przeciągnięcie formuły komórki w dół kolumny wypełnia sumy ruchu dla wszystkich pozostałych kanałów.

Kanał „Płatny” (tj. reklamowy) wygenerował lwią część ruchu (3 038 521 wizyt), co jest typowe dla witryn e-commerce.

SUMIKI

Funkcja SUMI.JEŻELI jest podobna do funkcji SUMA.JEŻELI, z wyjątkiem tego, że umożliwia stosowanie wielu kryteriów. Ta funkcja pomaga odpowiedzieć na drugie pytanie: „Jak duży ruch generował każdy kanał miesięcznie?”

Parametry SUMIFS są w nieco innej kolejności.

 =SUMA.WARUNKI(zakres sumy; zakres kryterium; kryterium)

Możliwe jest posiadanie prawie nieograniczonej liczby kryteriów, dodając pary zakres kryteriów i kryterium.

 =SUMA.WARUNKI(suma przedział; kryterium 1; kryterium 1; kryterium 2; kryterium 2)

Utworzyłem kolejny arkusz „Miesięczny ruch według kanału” z rzędami kanałów i kolumnami miesięcy.

W arkuszu Ruch miesięczny według kanałów funkcja SUMIFS pobiera miesięczne sumy ruchu.

Pracowałem z funkcją SUMA.JEŻELI w taki sam sposób, jak funkcja SUMA.JEŻELI. Ponieważ może zawierać wiele kryteriów, formuła SUMIF może być długa. W końcu mam ukończoną formułę dla kanału afiliacyjnego w styczniu 2022 roku.

 =SUMIFS('Ruch dzienny'!C:C,'Ruch dzienny'!B:B,A2,'Ruch dzienny'!A:A,">=2022-01-01",'Ruch dzienny'!A:A , „<=2022-01-31”) 

Formuła SUMIF może być długa ze względu na wiele kryteriów.

Rozłóżmy tę formułę.

Pierwszym parametrem jest zakres sumy, kolumna C w arkuszu Ruch dzienny. Zawiera rzeczywiste natężenie ruchu.

 =SUMIFS('Dzienny ruch'!C:C,

Kolejne dwa parametry oddzielone przecinkami to pierwszy zakres kryterium i pierwsze kryterium.

 „Codzienny ruch”!B:B,A2

Kolumna B w arkuszu Ruch dzienny ( „Ruch dzienny”!B:B w formule) to lista kanałów. Komórka A2 zawiera nazwę kanału „Partner”. Mogłem wpisać „partner”, ale użycie odwołania do komórki umożliwia przeciągnięcie formuły w dół i wypełnienie innych kanałów.

Następne dwie pary: zakres kryterium i kryterium tworzą zakres dat. Kolumna A ruchu dziennego zawiera daty. Kryterium „>=2022-01-01” określa, że ​​data jest większa lub równa 1 stycznia 2022 r.

 „Dzienny ruch”!A:A,">=2022-01-01"

Podałem datę jako tekst „>=2022-01-01” , ponieważ wiedziałem, że Arkusze Google rozpoznają ten format. Innym sposobem zapisywania dat jest: ">="&data(2022,1,1).

Oba będą działać.

Mogę skopiować i wkleić formułę w całym zakresie dat dla każdego z nich, ale poza tym zadanie jest ukończone. Funkcja SUMIFs uczyniła ten proces stosunkowo prostym. Teraz znam całkowity miesięczny ruch generowany przez każdy kanał.

Funkcja SUMIFs była prosta, aby uzyskać miesięczne sumy według kanału.