Yhteenveto
Voit määrittää COUNTIFS (tai COUNTIF) muuttuvalla alueella käyttämällä OFFSET-toimintoa. Esitetyssä esimerkissä B11: n kaava on:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
Tämä kaava laskee ei-tyhjät solut alueelle, joka alkaa B5: stä ja päättyy 2 rivillä solun yläpuolelle, jossa kaava asuu. Sama kaava kopioidaan ja liitetään 2 riviä datan viimeisen merkinnän alle kuvan osoittamalla tavalla.
Selitys
Esitetyssä esimerkissä B11: n kaava on:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
Työskentely sisältä ulospäin, muuttuvan alueen asettaminen tapahtuu OFFSET-toiminnolla tässä:
OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1) // variable range
OFFSET: llä on viisi argumenttia ja se on konfiguroitu seuraavasti:
- viite = B $ 5, aloita solusta B5, rivi lukittu
- rivit = 0, nolla riviä siirtyy alkusolusta
- cols = 0, offset nollasaraketta aloitussolu
- korkeus = RIVI () - RIVI (B $ 5) -1 = 5 riviä korkea
- leveys = 1 sarake leveä
Alueen korkeuden määrittämiseksi riveissä käytämme ROW-toimintoa seuraavasti:
ROW()-ROW(B$5)-1 // work out height
Koska ROW () palauttaa "nykyisen" solun rivinumeron (ts. Solun, jossa kaava elää), voimme yksinkertaistaa näin:
=ROW()-ROW(B$5)-1 =11-5-1 =5
Edellä esitetyllä kokoonpanolla OFFSET palauttaa alueen B5: B9 suoraan COUNTIFS: lle:
=COUNTIFS(B5:B9,"") // returns 4
Huomaa, että viittaus B $ 5: een yllä olevassa kaavassa on sekaviite, sarakkeen suhteellinen ja rivi lukittu. Näin kaava voidaan kopioida toiseen sarakkeeseen ja silti toimia. Esimerkiksi, kun se on kopioitu C12: een, kaava on:
=COUNTIFS(OFFSET(C$5,0,0,ROW()-ROW(C$5)-1,1),"")
Huomautus: OFFSET on epävakaa toiminto, joka voi aiheuttaa suorituskykyongelmia suurissa tai monimutkaisissa laskentataulukoissa.
Epäsuoralla ja OSOITTEELLA
Toinen lähestymistapa on käyttää kaavaa, joka perustuu epäsuoraan ja osoitteeseen. Tässä tapauksessa kootaan alue tekstinä ja arvioidaan sitten teksti INDIRECT-arvona viitteeksi. Kohdassa B11 oleva kaava olisi:
=COUNTIFS(INDIRECT(ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())),"")
ADDRESS-funktiota käytetään rakentamaan tällainen alue:
ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())
Ensimmäisessä ADDRESS-esimerkissä toimitamme rivinumeron kovakoodatuksi arvoksi 5 ja sarakkeen numerolle COLUMN-funktio:
=ADDRESS(5,COLUMN()) // returns "$B$5"
Toisessa tapauksessa toimitamme "nykyisen" rivinumeron miinus 2 ja nykyisen sarakkeen COLUMN-toiminnolla:
=ADDRESS(ROW()-2,COLUMN()) // returns "$B$9"
Liittämällä nämä kaksi arvoa yhteen meillä on:
"$B$5:$B$9" // as text
Huomaa, että tämä on tekstimerkkijono. Jos haluat muuntaa kelvolliseksi viitteeksi, meidän on käytettävä INDIRECT:
=INDIRECT("$B$5:$B$9") // returns $B$5:$B$9 as valid range
Lopuksi kaavan kohdassa B11 tulee:
=COUNTIFS($B$5:$B$9,"") // returns 4
Huomautus: INDIRECT on epävakaa toiminto, joka voi aiheuttaa suorituskykyongelmia suurissa tai monimutkaisissa laskentataulukoissa.
