Wiemy, że funkcja LICZ.WARUNKI w programie Excel może liczyć na wiele kryteriów. Przyjmuje argumenty jako kilka zakresów kryteriów i kryteriów. Możemy dynamicznie zmieniać kryteria, podając odwołanie do komórki, ale nie możemy dynamicznie zmieniać kolumny kryteriów. Cóż, nie bezpośrednio, ale możemy. Właśnie tego uczymy się z wyprzedzeniem formuł Excela. Robienie rzeczy w Excelu, których nie da się zrobić normalnie. Zobaczmy jak.
Uczmy się na przykładzie.
Kontekst
Tutaj przygotowałem dane sprzedaży wykonane w różnych miesiącach roku przez naszych doradców handlowych. Sprzedają różne modele naszego produktu, ogólnie nazwane model1, model2 i tak dalej. To, co musimy zrobić, to dynamicznie liczyć sprzedaż różnych modeli w różnych miesiącach.
W komórce I2 wybierzemy miesiąc. W komórce I2 wybierzemy model. Te wartości mogą ulec zmianie. Licznik też powinien się zmienić. Funkcja LICZ.WARUNKI powinna szukać kolumny miesiąca, która będzie zakresem kryteriów. Następnie wyszuka model w kolumnie tych miesięcy.
Tak więc tutaj kryteria i zakres_kryteriów są zmienne. Jak więc zrobić zmienną kolumnową w COUNTIFS? Oto jak?
Używanie nazwanego zakresu dla kolumny zmiennej lub zakresu kryteriów
Formuła ogólna
=LICZ.WARUNKI(POŚREDNIA(nazwa_zakresu);kryteria)
Najpierw nazwij każdą kolumnę zgodnie z ich nagłówkami. Aby to zrobić, wybierz tabelę i naciśnij CTRL+SHIFT+F3 i nazwij kolumny jak w górnym wierszu. Przeczytaj o tym tutaj.
Tak więc zakresy B3:B11, C3:C11, D3:D11 i E3:E11 są odpowiednio nazwane sty, luty, marzec i kwiecień.
Zapisz tę formułę w I4.
=LICZ.WARUNKI(POŚREDNIA(I2);I3)
Teraz, jeśli zmienisz miesiąc w I4, odpowiednia liczba miesięcy Modelu4 zostanie pokazana w I4.
Jak to działa?
Formuła jest prosta. Zacznijmy od środka.
ADR.POŚR(I2): Jak wiemy funkcja ADR.POŚR konwertuje tekst referencji na rzeczywistą referencję. Udostępniliśmy to I2. I2 zawiera Apr. Ponieważ mamy zakres E3:E11 nazwa Apr, ADR.POŚR(I2) tłumaczy się na E3:E11.
Formuła uproszczona do =LICZ.WARUNKI(E3:E11,I3). LICZ.WARUNKI zlicza wszystko, co znajduje się w I3 w zakresie E3:E11.
Gdy zmienisz miesiąc, kolumna będzie się zmieniać dynamicznie. Nazywa się to COUNTIFS z kolumnami Variable. W gifie użyłem formatowania warunkowego opartego na innej komórce.
Formuła może również działać z funkcją countif. Ale jeśli chcesz mieć wiele warunków, użyj funkcji LICZ.WARUNKI.
Kreatywny wykres kolumnowy zawierający sumy
Utwórz wykres nakładki w programie Excel 2016
Wykonaj wykres i analizę Pareto w programie Excel
Wykonaj wykres wodospadu w programie Excel
Wykresy Excela: małe wykresy w komórce
Wykres prędkościomierza (wskaźnik) w programie Excel 2016