Kopsavilkums
Lai konfigurētu COUNTIFS (vai COUNTIF) ar mainīgu diapazonu, varat izmantot funkciju OFFSET. Parādītajā piemērā formula B11 ir:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
Šī formula skaita tukšās šūnas diapazonā, kas sākas no B5 un beidzas 2 rindas virs šūnas, kurā dzīvo formula. Tā pati formula tiek kopēta un ielīmēta 2 rindas zem datu pēdējā ieraksta, kā parādīts.
Paskaidrojums
Parādītajā piemērā formula B11 ir:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
Strādājot no iekšpuses, mainīgā diapazona iestatīšanas darbu šeit veic funkcija OFFSET:
OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1) // variable range
OFFSET ir pieci argumenti, un tas ir konfigurēts šādi:
- atsauce = B $ 5, sākas šūnā B5, rinda ir bloķēta
- rindas = 0, nobīdīt nulles rindas no sākuma šūnas
- cols = 0, nobīdīt nulles kolonnu sākuma šūnu
- augstums = ROW () - ROW (B $ 5) -1 = 5 rindas augsts
- platums = 1 kolonna plata
Lai aprēķinātu diapazona augstumu rindās, mēs izmantojam funkciju ROW šādi:
ROW()-ROW(B$5)-1 // work out height
Tā kā ROW () atgriež "pašreizējās" šūnas (ti, šūnas, kurā dzīvo formula) rindas numuru, mēs varam vienkāršot šādi:
=ROW()-ROW(B$5)-1 =11-5-1 =5
Izmantojot iepriekš minēto konfigurāciju, OFFSET atgriež diapazonu B5: B9 tieši uz COUNTIFS:
=COUNTIFS(B5:B9,"") // returns 4
Ievērojiet, ka atsauce uz B $ 5 iepriekšminētajā formulā ir jaukta atsauce, kolonnas relatīvais un rinda ir bloķēta. Tas ļauj formulu nokopēt citā kolonnā un joprojām darbojas. Piemēram, pēc kopēšanas uz C12, formula ir šāda:
=COUNTIFS(OFFSET(C$5,0,0,ROW()-ROW(C$5)-1,1),"")
Piezīme: OFFSET ir nepastāvīga funkcija, un tā var izraisīt veiktspējas problēmas lielās vai sarežģītās darblapās.
Ar NETIEŠU un ADRESI
Vēl viena pieeja ir formulas izmantošana, pamatojoties uz funkcijām INDIRECT un ADDRESS. Šajā gadījumā mēs apkopojam diapazonu kā tekstu, pēc tam izmantojiet INDIRECT, lai novērtētu tekstu kā atsauci. Formula B11 būtu:
=COUNTIFS(INDIRECT(ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())),"")
Funkcija ADDRESS tiek izmantota šāda diapazona izveidošanai:
ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())
Pirmajā ADDRESS instancē mēs piegādājam rindas numuru kā cieto kodu 5 un kolonnas numuru ar funkciju COLUMN:
=ADDRESS(5,COLUMN()) // returns "$B$5"
Otrajā gadījumā mēs piegādājam "pašreizējais" rindas numuru mīnus 2 un pašreizējo kolonnu ar funkciju COLUMN:
=ADDRESS(ROW()-2,COLUMN()) // returns "$B$9"
Pēc šo divu vērtību apvienošanas mums ir:
"$B$5:$B$9" // as text
Ņemiet vērā, ka šī ir teksta virkne. Lai pārveidotu par derīgu atsauci, mums jāizmanto INDIRECT:
=INDIRECT("$B$5:$B$9") // returns $B$5:$B$9 as valid range
Visbeidzot, B11 formula kļūst:
=COUNTIFS($B$5:$B$9,"") // returns 4
Piezīme: INDIRECT ir nepastāvīga funkcija, kas var izraisīt veiktspējas problēmas lielās vai sarežģītās darblapās.
