Dostałem zapytania jak zaznaczyć ciąg 5 (lub więcej) komórek, które spełniają warunek (np: są puste). Jeśli możesz sobie pozwolić, że nad danymi, które sprawdzasz jest odpowiednia ilość wierszy, gdzie nie będzie spełniony sprawdzany warunek, to zadanie sprowadza się do liczenia najdłuższego ciągu w zakresie, który się przesuwa — porada 263:
https://www.youtube.com/watch?v=as9mztMnTsw
Końcowa formuła tablicowa (musisz zatwierdzić Ctrl + Shift + Enter):
=MAX(CZĘSTOŚĆ(JEŻELI(CZY.PUSTA(A1:A9);WIERSZ(A1:A9);"");JEŻELI(CZY.PUSTA(A1:A9);"";WIERSZ(A1:A9))))>4
Jednak czasami zdarza się sytuacja, gdzie musisz martwić się o sufit, czyli górę arkusza, żeby Twój zakres nie wyszedł poza niego. W takiej sytuacji musisz najpierw zbudować zakres, który zatrzyma się na pierwszym wierszu arkusza i nie będzie szedł wyżej. Przyda się Ci do tego funkcja INDEKS.
Tablicą na którą będziemy patrzeć jest cała kolumna A (odwołanie A:A). Musimy tylko wybrać odpowiedni wiersz. W górę musimy przesunąć się 4 wiersze w górę (dla ciągu 5 komórek spełniających nasz warunek), ale nie dalej niż wiersz 1. Wystarczy, że do funkcji MAX wstawimy formułę WIERSZ()-4 oraz 1, funkcja WIERSZ bez podania argumentów zwraca wiersz komórki, w której znajduje się formuła. Czyli pierwsza komórka zakresu to:
=INDEKS(A:A;MAX(WIERSZ()-4;1))
Tu trzeba wspomnieć, że funkcja INDEKS zwraca faktycznie odwołanie do komórki, a nie jej wartość, dzięki czemu jeśli dołożymy dwukropek możemy za pomocą funkcji INDEKS budować zakresy. Druga komórka jest łatwiejsza do zbudowania, bo to tylko WIERSZ()+4, czyli:
=INDEKS(A:A;WIERSZ()+4)
łącząc dwie powyższe formuły mamy zbudowany dynamiczny zakres:
=INDEKS(A:A;MAX(WIERSZ()-4;1)):INDEKS(A:A;WIERSZ()+4)
Teraz musimy sprawdzić, czy komórki z zakresu spełniają warunek, tu jest on prosty — wystarczy wstawić naszą formułę do funkcji CZY.PUSTA. Ważniejszy jest kolejny krok, gdzie jeśli dana komórka spełnia nasz warunek, to chcemy, żeby został zwrócony numer wiersza tej komórki. Będziemy potrzebować funkcji JEŻELI i funkcji WIERSZ po naszym zbudowanym zakresie. Jeśli komórka nie spełnia warunku chcemy uzyskać pusty ciąg znaków (""), czyli całość sprowadza się do formuły:
=JEŻELI(CZY.PUSTA(INDEKS(A:A;MAX(WIERSZ()-4;1)):INDEKS(A:A;WIERSZ()+4));WIERSZ(INDEKS(A:A;MAX(WIERSZ()-4;1)):INDEKS(A:A;WIERSZ()+4));"")
Zbudowana przez nas formuła posłuży jako tablica danych do funkcji CZĘSTOŚĆ, natomiast jako tablicę przedziałów będziemy chcieli mieć odwrotność zbudowanej formuły, czyli pusty ciąg znaków ma być wtedy, gdy warunek jest spełniony, a numer wiersza wtedy, gdy warunek nie jest spełniony.
=CZĘSTOŚĆ(JEŻELI(CZY.PUSTA(INDEKS(A:A;MAX(WIERSZ()-4;1)):INDEKS(A:A;WIERSZ()+4));WIERSZ(INDEKS(A:A;MAX(WIERSZ()-4;1)):INDEKS(A:A;WIERSZ()+4));"");JEŻELI(CZY.PUSTA(INDEKS(A:A;MAX(WIERSZ()-4;1)):INDEKS(A:A;WIERSZ()+4));"";WIERSZ(INDEKS(A:A;MAX(WIERSZ()-4;1)):INDEKS(A:A;WIERSZ()+4))))
Pozostaje sprawdzić, jaki jest maksymalny ciąg (wstawić naszą formułę do funkcji MAX) i pamiętać żeby zatwierdzić kombinacją klawiszy Ctrl + Shift + Enter ponieważ jest to formuła tablicowa.
Niestety tej formuły nie przyjmie nam formatowanie warunkowe, bo dynamicznie budujemy zakres za pomocą funkcji INDEKS i musimy sobie zostawić formułę w kolumnie pomocniczej, by sprawdzać, czy dla danego wiersza maksymalny ciąg spełnia nasze kryteria.
Ponieważ te komplikacje występują przypomnę jeszcze raz, że łatwiejszym rozwiązaniem może być dostawienie wierszy na początku arkusza, żebyśmy od pierwszej sprawdzanej komórki mogli zaznaczyć cały sprawdzany zakres, który będzie się przesuwał z formułą w dół, czyli przykładowa formuła:
=MAX(CZĘSTOŚĆ(JEŻELI(CZY.PUSTA(A1:A9);WIERSZ(A1:A9);"");JEŻELI(CZY.PUSTA(A1:A9);"";WIERSZ(A1:A9))))>4
Pozdrawiam
Adam Kopeć
Miłośnik Excela
