Lista członkostw to typowe małe zadanie firmy: policz osoby spełniające warunek, uśrednij grupę, zmierz czas od daty początkowej i oznacz tych, którzy wymagają uwagi. Umiejętności to COUNTIFS, AVERAGEIF, arytmetyka dat i IF z AND albo OR.
Zaplanuj przed pisaniem
Zapisz każde pytanie jako „ile”, „jaka średnia” albo „które”. „Ile” to liczenie, „jaka średnia” to ŚREDNIA.JEŻELI (Angielski: AVERAGEIF), a „które” to kolumna znaczników zbudowana za pomocą JEŻELI (Angielski: IF). Zdecyduj, czy wszystkie warunki muszą być prawdziwe (AND), czy wystarczy jeden z nich (OR). Pomylenie tych dwóch to najczęstszy błąd.
Liczenie z warunkami
LICZ.WARUNKI (Angielski: COUNTIFS) przyjmuje pary: zakres i test. Każda para musi być prawdziwa, aby wiersz został policzony. Test z porównaniem zapisuje się w cudzysłowie, na przykład ">=4". Gdy używasz komórki, połącz ją znakiem &: =LICZ.WARUNKI(B2:B30;">="&F1).
ŚREDNIA.JEŻELI (Angielski: AVERAGEIF) działa jak SUMA.JEŻELI (Angielski: SUMIF), ale zwraca średnią pasujących wierszy. Jeśli żaden wiersz nie pasuje, zwraca #DZIEL/0!. To wskazówka, że tekst kryteriów różni się od danych, na przykład dodatkową spacją.
Daty to liczby
Excel zapisuje datę jako liczbę dni, więc odjęcie dwóch dat daje liczbę dni między nimi. Jeśli data początkowa jest w D2, to =DZIŚ()-D2 to liczba dni od niej. Jeśli wynik wygląda jak data, zmień format komórki na Liczba albo Ogólny.
Ponieważ TODAY zmienia się codziennie, zmieniają się też twoje odpowiedzi. W stałej odpowiedzi do studium przypadku użyj daty podanej w pytaniu, w osobnej komórce.
Mały wymyślony przykład
Do ćwiczeń wyobraź sobie zamiast tego listę dziesięciu kart bibliotecznych. Kolumna A zawiera imię i nazwisko członka, kolumna B liczbę wypożyczeń w tym roku, kolumna C typ karty (Child albo Adult), a kolumna D datę wydania. Możesz zapytać, ilu dorosłych wypożyczyło co najmniej pięć książek, jaka jest średnia liczba wypożyczeń dla kart typu Child i które karty są starsze niż dwa lata i nieużywane.
Zwróć uwagę, jak każde pytanie odpowiada narzędziu: liczenie z dwoma warunkami, średnia z jednym warunkiem i znacznik, który wymaga porównania dat i porównania wypożyczeń. Zapisz to odwzorowanie na papierze, zanim zaczniesz, a potem sprawdź każdy wynik szybkim ręcznym liczeniem kilku pierwszych wierszy.
Znaczniki i sprawdzenia
Formuła znacznika powinna czytać się jak reguła: IF (pierwszy test ORAZ drugi test), wtedy jedna etykieta, w przeciwnym razie inna. Wypełnij ją w dół całej listy, a potem policz etykiety za pomocą LICZ.JEŻELI (Angielski: COUNTIF) i porównaj z liczbą uzyskaną w inny sposób. Etykiety powinny dać w sumie liczbę członków.
- Kopiuj formuły w dół dopiero wtedy, gdy zakresy używają $ tam, gdzie mają pozostać stałe.
- Sprawdź granice: czy "co najmniej 4" obejmuje członka z dokładnie 4?
- Przeskanuj kolumny liczbowe pod kątem pustych pól i tekstu, zanim zaufasz liczeniu.