Uurige seda arvu:
Oletame, et soovite sellest aruande koostada nii, nagu oleksite filtreerinud piirkonna. See tähendab, et kui filtreerite põhja, näete:
Aga mis siis, kui soovite sama asja valemipõhist versiooni?
Siin on tulemus, mida otsite veergudest I: K:
On selge, et see on sama aruanne, kuid siin pole filtreeritud üksusi. Kui soovite uut idaaruannet, oleks tore lihtsalt muuta G1 väärtus ida suunas:
Siin on, kuidas seda tehakse. Esiteks ei tehta seda VLOOKUPi abil. Nii et ma valetasin selle tehnika pealkirja kohta!
Veergu F varem ei näidatud ja seda saab peita (või teisaldada kuhugi mujale, et see aruannet ei segaks).
Veerus F on näidatud rea numbrid, kus veerus A leidub G1; see tähendab, millised read sisaldavad väärtust „Põhja”? See meetod hõlmab kasutades raku eespool, nii et see peab algama vähemalt 2. rida See sobib väärtus "Põhja" vastu veerus A, kuid mitte kogu veerus kasutage funktsiooni Nihe: OFFSET($A$1,F1,0,1000,1).
Kuna F1 on 0, siis see on see, OFFSET(A1,0,0,1000,1)mis on A1: A1000. (1000 on meelevaldne, kuid töö tegemiseks piisavalt suur - saate sellest teha mis tahes muu numbri).
F2 väärtus 2 on see, kus asub esimene põhjaosa. Samuti soovite lõpus lisada F1 väärtuse, kuid see on seni null.
“Maagia” ärkab ellu lahtris F3. Te juba teate, et esimene põhi on leitud 2. real. Nii et soovite hakata otsima kahte rida allpool A1. Seda saate teha, määrates funktsiooni OFFSET teise argumendina 2.
Valem F3 automaatselt osutavad 2, mis oli arvutatud raku F2: kopeerimisel valemiga allapoole, näed =OFFSET($A$1,F2,0,1000,1)mis on OFFSET($A$1,2,0,1000,1)mis on A3: A1000. Nii et sobitate põhja selle uue vahemiku vastu ja see leiab põhja selle uue vahemiku kolmandast lahtrist, nii et MATCH annab 3.
Kui lisate ülaltoodud lahtrist F2 tagasi väärtuse, näete 3 pluss 2 või 5, mis on rida, mis sisaldab teist põhja.
Kõigi väärtuste saamiseks on see valem täidetud piisavalt kaugele.
Nii saate rea numbrid, kust kõik põhjapoolsed kirjed on leitud.
Kuidas tõlkida need ridade numbrid I – K veergu? Seda kõike tehakse ühe valemiga. Sisestage see valem I2: =IFERROR(INDEX(A:A,$F2),””). Kopeerige paremale ja seejärel alla.
Miks kasutada IFERROR-i? Kus viga on? Pange tähele lahtrit F6 - see sisaldab # N / A (sellepärast soovite veeru F peita), kuna pärast 15. rida pole enam põhjapoolseid. Nii et kui veerg F on viga, tagastage tühi. Vastasel juhul võtke väärtus veerust A (ja kui see on täidetud, siis B & C).
$ F2 on absoluutne viide veergule F, nii et täitmisõigus viitab endiselt veerule F
See külalisartikkel pärineb Exceli MVP Bob Umlaselt. See on üks tema lemmiktehnikaid tema raamatust Excel väljaspool kasti.
Excel väljaspool kasti »
