Kokkuvõte
COUNTIFS (või COUNTIF) muutuva vahemikuga konfigureerimiseks võite kasutada funktsiooni OFFSET. Näidatud näites on valem B11-s järgmine:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
See valem loeb tühjad lahtrid vahemikus, mis algab punktist B5 ja lõpeb 2 rida lahtri kohal, kus valem elab. Sama valem kopeeritakse ja kleebitakse 2 rida andmete viimase kirje alla, nagu näidatud.
Selgitus
Näidatud näites on valem B11-s järgmine:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
Seestpoolt töötades teeb muutuva vahemiku seadistamise funktsioon siin funktsioon OFFSET:
OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1) // variable range
OFFSETil on viis argumenti ja see on konfigureeritud järgmiselt:
- viide = B $ 5, alustage lahtrist B5, rida lukus
- rida = 0, nihutatakse null rida algusrakust
- veerud = 0, nihutatakse veeru alglaht nullist
- kõrgus = RIDA () - RIDA (B $ 5) -1 = 5 rida kõrge
- laius = 1 veeru lai
Ridade vahemiku kõrguse arvutamiseks kasutame funktsiooni ROW järgmiselt:
ROW()-ROW(B$5)-1 // work out height
Kuna ROW () tagastab "praeguse" lahtri (st lahtri, milles valem elab) rea numbri, saame seda lihtsustada järgmiselt:
=ROW()-ROW(B$5)-1 =11-5-1 =5
Ülaltoodud konfiguratsiooni korral tagastab OFFSET vahemiku B5: B9 otse arvule COUNTIFS:
=COUNTIFS(B5:B9,"") // returns 4
Pange tähele, et ülaltoodud valemis on viide B $ 5-le segatud viide, kusjuures veeru suhteline ja rida on lukus. See võimaldab valemi kopeerida teise veergu ja ikkagi töötada. Näiteks kui see on kopeeritud C12-sse, on valem järgmine:
=COUNTIFS(OFFSET(C$5,0,0,ROW()-ROW(C$5)-1,1),"")
Märkus. OFFSET on kõikuv funktsioon ja võib põhjustada jõudlusprobleeme suurtes või keerukates töölehtedes.
Otsese ja aadressiga
Teine lähenemisviis on funktsioonide INDIRECT ja ADDRESS põhjal valemi kasutamine. Sel juhul paneme vahemiku tekstina kokku, seejärel kasutame teksti hindamiseks viitena INDIRECT-i. B11 valem oleks:
=COUNTIFS(INDIRECT(ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())),"")
Funktsiooni ADDRESS kasutatakse sellise vahemiku koostamiseks:
ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())
Esimesel ADDRESS-i korral pakume rea numbrit kõvakodeeritud väärtusena 5 ja esitame veeru numbri funktsiooniga COLUMN:
=ADDRESS(5,COLUMN()) // returns "$B$5"
Teisel juhul pakume reaarvu "praegune" miinus 2 ja praeguse veeru funktsiooniga VEERG:
=ADDRESS(ROW()-2,COLUMN()) // returns "$B$9"
Pärast nende kahe väärtuse ühendamist on meil:
"$B$5:$B$9" // as text
Pange tähele, et see on tekstistring. Kehtivaks viiteks teisendamiseks peame kasutama INDIRECT:
=INDIRECT("$B$5:$B$9") // returns $B$5:$B$9 as valid range
Lõpuks saab B11 valem:
=COUNTIFS($B$5:$B$9,"") // returns 4
Märkus: INDIRECT on kõikuv funktsioon ja võib põhjustada jõudlusprobleeme suurtes või keerukates töölehtedes.
