Jak mogę odwoływać się do dwóch różnych kolumn funkcji COUNTIF?

11

Postaram się wyjaśnić. Powiedzmy, że mam arkusz, który wygląda tak (w tym wymyślonym przykładzie powiedzmy, że wysyłam e-maile marketingowe do różnych osób, znam ich wiek i kody pocztowe i chcę śledzić współczynniki klikalności w tych e-mailach):

        A   |    B        |    C  
  1     Age |    zip code |  click?  
  2     26  |    11111    |  true  
  3     27  |    11112    |  true  
  4     28  |    11111    |  false  
  5     27  |    22222    |  false  
  6     28  |    11112    |  false  
  7     26  |    22222    |  true

Teraz chcę śledzić różne statystyki. W takim przypadku chcę śledzić współczynnik kliknięć według wieku i kodu pocztowego. Mam więc taki stół:

      E         F          G 
 1   Age  |  # sent  | # clicked
 2   26   |
 3   26   |
 4   27   |
 5   28   |

Zasadniczo w kolumnie „# wysłane” mogę umieścić (na przykład w F2):

=COUNTIF(A:A, E2)

A to spowodowałoby 2 w komórce F2, ponieważ były dwa wystąpienia „26” w kolumnie A.

Ale co teraz, jeśli chcę wiedzieć, ile kliknięto? Chcę powiedzieć coś takiego:

=COUNTIF(A:A == E2  AND  C:C == True, for the same row where A:A matched E2)

Wiem, że mogę dodać kolejną kolumnę D z formułą podobną do

=IF(C2, A2, "")

Który będzie tylko wiek w kolumnie D, jeśli kolumna C jest prawdziwa. Ale mogę mieć potencjalnie wiele kryteriów i nie wydaje mi się, żebym musiał dodawać kolejną „fałszywą” kolumnę dla każdej kolumny kryteriów, którą mam.

Czy jest na to sposób? Jeśli nie w Dokumentach Google, w programie Excel?

google-sheets

— Jer
źródło

Uwaga: odpowiedź na nowe Arkusze Google znajduje się tam .

11

Użyj kombinacji operacji ARRAYFORMULA, SUM i arytmetycznych

Oto funkcja zliczania wszystkich kliknięć 26-latka:

=ARRAYFORMULA(sum((A:A=26) * (C:C="true")))

Ta operacja składa się z 3 części.

ARRAYFORMULA dba o zapętlenie w określonym zakresie
SUM zarządza liczenie wszystkich prawdziwych wyników

Zasadniczo prawda jest konwertowana na 1, a fałsz jest przekształcana na 0. Logika logiczna jest wykonywana przy użyciu operacji arytmetycznych.

Operacja AND używa mnożenia:

(1 * 1) = 1 - (True && True) = True
(1 * 0) = 0 - (True && False) = False
(0 * 0) = 0 - (False && False) = False

Operacja LUB używa kombinacji funkcji * SIGN i dodania:

znak (1 + 1) = 1 - (Prawda || Prawda) = Prawda
znak (1 + 0) = 1 - (Prawda || Fałsz) = Prawda
znak (0 + 0) = 0 - (Fałsz || Fałsz) = Fałsz

Uwaga: Funkcja znaku jest konieczna, ponieważ sposób dodawania wartości logicznej działa inaczej niż dodawanie arytmetyczne. Zasadniczo w dodawaniu boolowskim 1 + 1 = 1, w dodawaniu arytmetycznym 1 + 1 = 2. Oczywiście dodawanie arytmetyczne zepsuje licznik, więc musisz uruchomić wyniki operacji dodawania za pomocą funkcji znaku. Funkcja znak zwraca 1, jeśli wartość jest dodatnia, 0, jeśli wartość wynosi 0, a -1, jeśli wartość jest ujemna.

Powiedzmy, że chcesz policzyć kliknięcia dla wszystkich użytkowników w wieku 20–25 lat:

=ARRAYFORMULA(sum(sign((A:A=20) + (A:A=21) + (A:A=22) + (A:A=23) + (A:A=24) + (A:A=25)) * (C:C="true")))

— Evan Plaice
źródło

ZNAK nie jest tutaj wymagany, prawda? żadna pojedyncza komórka nie może mieć bothna przykład 20 i 23, więc dodanie nigdy nie dałoby ci wartości> 1 w tym przypadku. ZNAK jest wymagany tylko wtedy, gdy warunki nie wykluczają się wzajemnie. Czy w tej wersji nie byłoby łatwiej zrobić od 20 do 25 lat? =arrayformula(sum((A:A>=20)*(A:A<=25)*(C:C=TRUE)))

— Barry Houdini,

Próbowałem wyjaśnić ogólne zastosowanie logiki OR. Gdyby testy nie były wyłączne (zawarte w tej samej kolumnie), konieczna byłaby funkcja SIGN. W tym przypadku tak nie jest. I tak, twój przykład byłby lepszym podejściem, ale nie pokazuje użycia OR. Próbowałem wymyślić uproszczone podejście, aby zademonstrować użycie OR, które pasuje do pytania OP, ale oczywiście nie jestem zbyt dobry w wymyślaniu przykładów poza mankietem. Jeśli masz jakieś lepsze przykłady, zachęcam do edycji / poprawy odpowiedzi.

— Evan Plaice,

Przepraszam, Evan, źle zrozumiałem - to doskonały przykład.

— Barry Houdini,

można uniknąć = prawda, więc: = ARRAYFORMULA (suma ((A: A = 26) * C: C))

— tic

1

@tic Nie używałem logicznej wartości true (która jest PRAWDA w Arkuszach kalkulacyjnych Google). Odniosłem się do „prawdziwej” ciągu, którego operacja użyła w swoim pytaniu. Spróbuj, jestem prawie pewien, że jeśli przetestujesz wartości dla C: C, zawsze zwróci PRAWDA na niepustym ciągu. W komórkach arkusza kalkulacyjnego Google trzyma się reguł Excela nad JavaScript. Witamy w zabawnym świecie nieszczelnych abstrakcji.

— Evan Plaice

10

Na razie nowe Arkusze Google obsługują COUNTIFS, które mogą bezpośrednio obsłużyć wymagane zadanie.

=COUNTIFS(A:A, E2, C:C, "true")

Wymień wszystkie zakresy i porównanie, aby oddzielić je przecinkami.

— swarajk1
źródło

4

Dzięki za wynagrodzenie za przewijanie w dół. To jest odpowiedź podczas pracy z nowymi arkuszami google.

— Christiaan Westerbeek

3

W programie Excel użyłbym SUMPRODUCT w programie Excel 2003 lub COUNTIFS (z literą „S”) w programie Excel 2007 lub nowszym ....... ale w Dokumentach Google spróbuj tego

=arrayformula(sum((A:A=E2)*(C:C=TRUE)))

— Barry Houdini
źródło

3

=COUNTA( IFERROR( FILTER('Guest List'!$G:$G ; 'Guest List'!$G:$G = "Yes" ; 'Guest List'!$L:$L = "USA" ) ) )

To było dla dwóch kolumn - używam tego do mojej listy gości weselnych - w tym przypadku zestawiam „Tak” po wysłaniu zapisanej daty i „USA” dla lokalizacji, dzięki czemu mogę później obliczyć koszty wysyłki. Może być krótsza formuła, ale wydaje się, że to działa, więc nie mam nic przeciwko!

Szczęśliwego liczenia.

— Shef
źródło

1

W programie Excel zapomniałbym formuł i używałbym tabel przestawnych.

Konieczne może być dodanie jednej kolumny „licznika”, która może być używana wielokrotnie jako zmienna sumowana w celu uzyskania wyników.

— MaryC.fromNZ
źródło

0

Możesz dodać tablicę, w której chcesz przetestować {}.

W poniższym przykładzie:

=countif({F2,H2,J2,L2,N2,P2,R2,T2,V2,W2},"TRUE")

— Stuart Murless
źródło