Bill przesłał w tym tygodniu pytanie dotyczące programu Excel.
Mam bazę danych wydarzeń w Excelu i mój szef chce, żebym sporządzał wykresy częstotliwości według miesięcy. Czytałem twoją sztuczkę zmiany dat dziennych na daty miesięczne i formuł Excel CSE. Wypróbowałem wszystkie kryteria, które przychodzą mi do głowy w poniższej formule Excel CountIf, aby spojrzeć na 2 kryteria.
Sytuację można prawdopodobnie łatwo rozwiązać za pomocą tabeli przestawnej (XL95-XL2000) lub wykresu przestawnego (tylko XL2000). Na razie zajmijmy się pytaniem, które zadałeś. Po lewej stronie znajduje się arkusz roboczy. Wygląda na to, że będziesz chciał wprowadzić formuły w komórkach B4406: D4415, aby obliczyć liczbę określonych wydarzeń w każdym miesiącu.
Funkcja CountIf jest wyspecjalizowaną formą formuły tablicowej, która świetnie sprawdza się w przypadku pojedynczych kryteriów. Nie działa dobrze, gdy masz wiele kryteriów. Poniższe przykładowe formuły policzyłyby liczbę wierszy z opcją Rain i liczbą wydarzeń w styczniu 97:
=COUNTIF(B2:B4403,"=Rain")
=COUNTIF(A2:A4403,"="&A4406)
Nie ma możliwości użycia CountIf, aby uzyskać przecięcie dwóch warunków.
Każdemu czytelnikowi, który nie jest zaznajomiony z wprowadzaniem formuł tablicowych, bardzo polecam zapoznanie się z tematem Użyj formuł CSE, aby usprawnić program Excel.
Bill nie podał tego w swoim pytaniu, ale chcę zbudować formułę, którą może wprowadzić tylko raz w komórce B4406, którą można łatwo skopiować do innych komórek w jego zakresie. Używając odwołań bezwzględnych i mieszanych w formule, można uniknąć kłopotów związanych z wprowadzaniem nowej formuły dla każdego przecięcia.
Oto krótki przegląd formuł bezwzględnych, względnych i mieszanych. Zwykle, jeśli wprowadzisz formułę taką jak =SUM(A2:A4403)w D1, a następnie skopiujesz ją do E2, twoja formuła w E2 zmieni się na =SUM(B3:C4403). Jest to fajna funkcja arkuszy nazywana „adresowaniem względnym”, ale czasami nie chcemy, aby tak się stało. W tym przypadku chcemy, aby każda formuła odnosiła się do zakresu A2: B4403. Kiedy kopiujemy formułę z komórki do komórki, powinna zawsze wskazywać na A2: B4403. Podczas wprowadzania formuły naciśnij raz F4 po wejściu w zakres, a twoja formuła zmieni się na=SUM($A$2:$A$4403). Znak dolara wskazuje, że ta część odwołania nie zmieni się podczas kopiowania formuły. Nazywa się to adresowaniem bezwzględnym. Możliwe jest zablokowanie tylko kolumny za pomocą znaku $ i zezwolenie na to, aby wiersz był względny. Nazywa się to odniesieniem mieszanym i zostanie wprowadzone jako =$A4406. Aby zablokować wiersz, ale pozostawić kolumnę względną, użyj =B$4405. Podczas wpisywania formuły użyj klawisza F4, aby przełączać się między czterema smakami odwołań względnych, bezwzględnych i mieszanych.
Oto wzór na komórkę B4406:
=SUM(IF($C$2:$C$4403=$A4406,IF($B$2:$B$4403=B$4405,1,0),0))
Wpisz formułę. Po zakończeniu formuły przytrzymaj klawisze Ctrl, Shift, a następnie wprowadź. Możesz teraz skopiować formułę do C4406: D4406, a następnie skopiować te trzy komórki do każdego wiersza w tabeli wynikowej.
Formuła wykorzystuje wszystkie trzy formy odniesień mieszanych i bezwzględnych. Zagnieżdża 2 instrukcje if, ponieważ funkcja AND () wydawała się nie działać w formule tablicowej. Aby lepiej wyjaśnić, co dzieje się z funkcją tablic, przeczytaj ponownie artykuł Użyj formuł CSE, aby usprawnić program Excel wspomniany powyżej.
