Potrzebujemy odnaleźć komórki, które spełniają warunek daty (wybieramy odpowiedni wiersz) oraz wartości w tych komórkach mają być większe od zera.

Żeby odnaleźć je wszystkie potrzebujemy niestety skomplikowanej formuły. W pierwszej kolejności warto, żebyśmy obliczyli ilość komórek, które spełniają warunki, żeby ograniczyć później ilość wykonywanych obliczeń tablicowych.

Żeby zlokalizować wiersz dla daty będziemy potrzebowali przede wszystkim funkcji INDEKS, która będzie patrzeć na cały zakres danych. Następnie będziemy potrzebowali znaleźć wiersz, który nas interesuje, czyli datę. W tym pomoże nam funkcja PODAJ.POZYCJĘ, która odnajdzie pozycję wybranej daty na liście wszystkich dat. Będziemy jeszcze potrzebowali powiedzieć Excelowi, że interesuje nas cały wiersz, dlatego w trzecim argumencie funkcji INDEKS będziemy musieli wpisać wartość 0.

=INDEKS($B$2:$I$15;PODAJ.POZYCJĘ($K3;$A$2:$A$15;0);0)

Dzięki temu, że funkcja INDEKS zwraca odwołanie do zakresu możemy ją wstawić do funkcji LICZ.JEŻELI i policzyć ilość wartości w wierszu większych od zera.

=LICZ.JEŻELI(INDEKS($B$2:$I$15;PODAJ.POZYCJĘ($K3;$A$2:$A$15;0);0);">0")

Gdy mamy obliczoną ilość komórek w interesującym nas wierszu, które spełniają nasze kryterium, to możemy zacząć pisać formułę, która pozwoli nam wyciągnąć szukane wartości. W środku będzie nasza pierwsza funkcja INDEKS, dla której sprawdzimy, które wartości są większe od zera. Dla tych wartości chcemy, żeby Excel nam zwracał numery kolumn, w których te wartości się znajdują. Będziemy do tego potrzebowali funkcji JEŻELI, a jako jej drugiego argumentu funkcji NR.KOLUMNY, która będzie patrzeć na zakres $A$1:$H$1, żeby zwracała nam kolejne liczby (numery kolumn od 1 w górę). Trzeci argument funkcji JEŻELI pomijamy dzięki temu wynikiem naszej formuły będzie tablica numerów kolumn, gdzie była wartość, która spełniała nasz warunek i wartości FAŁSZ, tam gdzie wartość nie spełniała tego warunku.

=JEŻELI(INDEKS($B$2:$I$15;PODAJ.POZYCJĘ($K3;$A$2:$A$15;0);0)>0;NR.KOLUMNY($A$1:$H$1)) -> {1\2\3\FAŁSZ\FAŁSZ\6\7\FAŁSZ}

Dalszym krokiem będzie wyciąganie kolejnych wartości, czyli będziemy potrzebowali zatrudnić funkcję MIN.K, która wyciągnie nam numery kolumn od najmniejszego, dzięki temu, że w argument k wpiszemy funkcję LICZBA.KOLUMN z dynamicznym zakresem ($K$8:K8), który będzie się powiększał, gdy formułę będziemy przeciągać w bok, dzięki czemu funkcja MIN.K będzie nam zwracała kolejne numery kolumn, gdzie wartość spełniła warunek, a jeśli przeciągniemy formułę za daleko to zobaczymy błąd #LICZBA!, ponieważ nie mamy aż tylu wartości w komórkach.

=MIN.K(JEŻELI(INDEKS($B$2:$I$15;PODAJ.POZYCJĘ($K3;$A$2:$A$15;0);0)>0;NR.KOLUMNY($A$1:$H$1));LICZBA.KOLUMN($K$8:K8))

Ponieważ nam zależy na wartościach z tych kolumn, nie numerach kolumn, to musimy jeszcze raz napisać formułę wyciągającą wiersz po dacie, a wynik funkcji MIN.K wstawić w miejsce wcześniejszego zera (numeru kolumny)

=INDEKS($B$2:$I$15;PODAJ.POZYCJĘ($K3;$A$2:$A$15;0);MIN.K(JEŻELI(INDEKS($B$2:$I$15;PODAJ.POZYCJĘ($K3;$A$2:$A$15;0);0)>0;NR.KOLUMNY($A$1:$H$1));LICZBA.KOLUMN($K$8:K8)))

Ponieważ formuły tablicowe oprócz tego, że musimy je zatwierdzić kombinacją klawiszy Ctrl + Shift + Enter, mocno obciążają procesor, to dopiszemy jeszcze warunek w funkcji JEŻELI, który będzie sprawdzał, czy przekroczyliśmy już liczbę, które spełniają nasze warunki. Jeśli tak to będziemy chcieli wstawić pusty ciąg znaków (dwa podwójne cudzysłowy).

=JEŻELI(LICZBA.KOLUMN($K$8:K8)>$L3;"";INDEKS($B$2:$I$15;PODAJ.POZYCJĘ($K3;$A$2:$A$15;0);MIN.K(JEŻELI(INDEKS($B$2:$I$15;PODAJ.POZYCJĘ($K3;$A$2:$A$15;0);0)>0;NR.KOLUMNY($A$1:$H$1));LICZBA.KOLUMN($K$8:K8))))

Uff. Udało się to cała nasza formuła. Do pełni szczęścia 😀 potrzebujemy jeszcze, wyciągać nagłówki kolumn, w których zostały znalezione wartości, które nas interesują – będzie to ciut prostsza formuła, bo od początku znamy zakres komórek odpowiadający nagłówkowi.

=JEŻELI(LICZBA.KOLUMN($K$8:K8)>$L3;"";INDEKS($B$1:$I$1;1;MIN.K(JEŻELI(INDEKS($B$2:$I$15;PODAJ.POZYCJĘ($K3;$A$2:$A$15;0);0)>0;NR.KOLUMNY($A$1:$H$1));LICZBA.KOLUMN($K$8:K8))))

Od Excela 2010 możesz zastosować funkcję AGREGUJ, żebyś nie musiał formuły zatwierdzać jako formuły tablicowej (nie musisz korzystać z zatwierdzania kombinacją klawiszy Ctrl + Shift + Enter).

Dla nagłówków

=JEŻELI(LICZBA.KOLUMN($K$26:K26)>$L22;"";INDEKS($B$1:$I$1;AGREGUJ(15;6;NR.KOLUMNY($A$1:$H$1)/(INDEKS($B$2:$I$15;PODAJ.POZYCJĘ($K22;$A$2:$A$15;0);0)>0);LICZBA.KOLUMN($K$26:K26))))

Dla wartości:

=JEŻELI(LICZBA.KOLUMN($K$27:K27)>$L22;"";INDEKS($B$2:$I$15;PODAJ.POZYCJĘ($K22;$A$2:$A$15;0);AGREGUJ(15;6;NR.KOLUMNY($A$1:$H$1)/(INDEKS($B$2:$I$15;PODAJ.POZYCJĘ($K22;$A$2:$A$15;0);0)>0);LICZBA.KOLUMN($K$27:K27))))

Pozdrawiam
Adam Kopeć
Miłośnik Excela