Nagrinėkite šį skaičių:
Tarkime, kad iš to norite sukurti ataskaitą, tarsi filtruotumėte regione. Tai yra, jei filtruosite šiaurę, pamatysite:
Bet ką daryti, jei norėtumėte formulėmis pagrįstos to paties dalyko versijos?
Štai rezultatas, kurio ieškote I stulpeliuose: K:
Aišku, tai ta pati ataskaita, bet čia nėra filtruotų elementų. Jei norėtumėte naujos ataskaitos apie Rytus, būtų malonu tiesiog pakeisti G1 reikšmę į Rytus:
Štai kaip tai daroma. Visų pirma, tai daroma ne naudojant VLOOKUP. Taigi melavau dėl šios technikos pavadinimo!
F stulpelis anksčiau nebuvo rodomas ir jį galima paslėpti (arba perkelti kur nors kitur, kad netrukdytų ataskaitai).
Tai, kas rodoma F stulpelyje, yra eilutės, kur G1 yra A stulpelyje; tai yra, kuriose eilutėse yra reikšmė „Šiaurė“? Šis metodas apima naudojant ląstelių anksčiau, todėl reikia pradėti bent 2. atitinka vertę "šiaurės" prieš stulpelio, bet vietoj visą stulpelį, naudokite poslinkis funkciją eilės: OFFSET($A$1,F1,0,1000,1).
Kadangi F1 yra 0, tai OFFSET(A1,0,0,1000,1)yra A1: A1000. (1000 yra savavališkas, bet pakankamai didelis, kad galėtų atlikti šį darbą - galite padaryti jį bet kokiu kitu numeriu).
2 reikšmė F2 yra ten, kur yra pirmasis „šiaurė“. Taip pat norite vėl pridėti F1 vertę, tačiau kol kas tai lygi nuliui.
„Magija“ atgyja F3 langelyje. Jūs jau žinote, kad pirmasis šiaurinis yra 2 eilutėje. Taigi norite pradėti ieškoti dviejų eilučių žemiau A1. Tai galite padaryti nurodydami 2 kaip antrąjį funkcijos OFFSET argumentą.
F3 formulė automatiškai nurodys 2, kurie buvo apskaičiuoti langelyje F2: Kai nukopijuosite formulę žemyn, pamatysite =OFFSET($A$1,F2,0,1000,1), OFFSET($A$1,2,0,1000,1)kuri yra A3: A1000. Taigi jūs suderinate šiaurę su šiuo nauju diapazonu ir jis randa šiaurę trečioje šio naujo diapazono ląstelėje, taigi MATCH suteikia 3.
Pridėję atgal aukščiau esančio langelio F2 vertę, pamatysite 3 plius 2 arba 5, tai yra eilutė, kurioje yra antroji šiaurė.
Ši formulė užpildyta pakankamai toli, kad gautų visas reikšmes.
Tai suteiks jums eilučių numerius, kuriuose yra visi Šiaurės įrašai.
Kaip išversti tuos eilučių numerius į rezultatus I – K stulpeliuose? Visa tai daroma pagal vieną formulę. Įveskite šį formulę I2: =IFERROR(INDEX(A:A,$F2),””). Nukopijuokite dešinėn, tada nukopijuokite žemyn.
Kodėl naudoti IFERROR? Kur klaida? Atkreipkite dėmesį į langelį F6 - jame yra # N / A (todėl norite paslėpti F stulpelį), nes po 15 eilutės nebėra Šiaurės. Taigi, jei F stulpelis yra klaida, grąžinkite tuščią. Kitu atveju pasiimkite vertę iš A stulpelio (o kai užpildysite dešinėje, B & C).
$ F2 yra absoliuti nuoroda į F stulpelį, todėl užpildymo teisė vis tiek nurodo F stulpelį.
Šis svečio straipsnis yra iš „Excel MVP Bob Umlas“. Tai viena mėgstamiausių jo knygos „Excel Outside the Box“ technikų.
„Excel“ dėžutės išorėje »
