Podsumowanie
Aby skonfigurować LICZ.WARUNKI (lub LICZ.JEŻELI) ze zmiennym zakresem, możesz użyć funkcji PRZESUNIĘCIE. W przedstawionym przykładzie formuła w B11 to:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
Ta formuła zlicza niepuste komórki w zakresie rozpoczynającym się od B5 i kończącym się 2 wiersze nad komórką, w której znajduje się formuła. Ta sama formuła jest kopiowana i wklejana 2 wiersze poniżej ostatniego wpisu w danych, jak pokazano.
Wyjaśnienie
W przedstawionym przykładzie formuła w B11 to:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
Pracując od wewnątrz, praca polegająca na ustawianiu zakresu zmiennej jest wykonywana tutaj przez funkcję OFFSET:
OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1) // variable range
OFFSET ma pięć argumentów i jest skonfigurowany w następujący sposób:
- odwołanie = B 5 $, zacznij od komórki B5, wiersz zablokowany
- wiersze = 0, przesunięcie zerowych wierszy od komórki początkowej
- cols = 0, przesunięcie zerowej kolumny początkowej komórki
- wysokość = WIERSZ () - RZĘD (B 5 $) -1 = 5 rzędów wysokości
- szerokość = szerokość 1 kolumny
Aby obliczyć wysokość zakresu w wierszach, używamy funkcji WIERSZ w następujący sposób:
ROW()-ROW(B$5)-1 // work out height
Ponieważ ROW () zwraca numer wiersza „bieżącej” komórki (tj. Komórki, w której znajduje się formuła), możemy uprościć w ten sposób:
=ROW()-ROW(B$5)-1 =11-5-1 =5
W powyższej konfiguracji OFFSET zwraca zakres B5: B9 bezpośrednio do COUNTIFS:
=COUNTIFS(B5:B9,"") // returns 4
Zauważ, że odniesienie do B $ 5 w powyższym wzorze jest odniesieniem mieszanym, z kolumną względną i wierszem zablokowanym. Pozwala to na skopiowanie formuły do innej kolumny i dalsze działanie. Na przykład po skopiowaniu do C12 formuła wygląda następująco:
=COUNTIFS(OFFSET(C$5,0,0,ROW()-ROW(C$5)-1,1),"")
Uwaga: PRZESUNIĘCIE jest funkcją ulotną i może powodować problemy z wydajnością w dużych lub złożonych arkuszach.
Z POŚREDNIM i ADRESEM
Innym podejściem jest użycie formuły opartej na funkcjach POŚREDNI i ADRES. W tym przypadku tworzymy zakres jako tekst, a następnie używamy opcji POŚREDNI, aby ocenić tekst jako odniesienie. Wzór w B11 wyglądałby tak:
=COUNTIFS(INDIRECT(ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())),"")
Funkcja ADRES służy do konstruowania takiego zakresu:
ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())
W pierwszym przypadku ADDRESS podajemy numer wiersza jako zakodowaną na stałe wartość 5, a numer kolumny podajemy funkcją COLUMN:
=ADDRESS(5,COLUMN()) // returns "$B$5"
W drugim przypadku podajemy „bieżący” numer wiersza minus 2, a bieżącą kolumnę funkcją COLUMN:
=ADDRESS(ROW()-2,COLUMN()) // returns "$B$9"
Po połączeniu tych dwóch wartości razem otrzymujemy:
"$B$5:$B$9" // as text
Zauważ, że jest to ciąg tekstowy. Aby przekonwertować na prawidłowe odniesienie, musimy użyć POŚREDNIE:
=INDIRECT("$B$5:$B$9") // returns $B$5:$B$9 as valid range
Ostatecznie wzór w B11 staje się:
=COUNTIFS($B$5:$B$9,"") // returns 4
Uwaga: POŚREDNIE to funkcja niestabilna i może powodować problemy z wydajnością w dużych lub złożonych arkuszach.
