Vizsgálja meg ezt az ábrát:
Tegyük fel, hogy ebből olyan jelentést szeretne készíteni, mintha a régiót szűrte volna. Vagyis ha északra szűr, akkor a következőket látja:
De mi van, ha ugyanannak a képletalapú változatát szeretné?
Íme az eredmény, amelyet az I: K oszlopokban keres:
Nyilvánvaló, hogy ugyanaz a jelentés, de itt nincsenek szűrt elemek. Ha új keleti jelentést szeretne, akkor jó lenne egyszerűen megváltoztatni a G1 értékét Keletre:
Így történik. Először is, nem a VLOOKUP használatával történik. Tehát hazudtam ennek a technikának a címéről!
Az F oszlop korábban nem volt látható, és elrejthető (vagy máshová helyezhető, így nem zavarja a jelentést).
Az F oszlopban az látható, ahol G1 található az A oszlopban; vagyis milyen sorok tartalmazzák az „észak” értéket? Ez a módszer segítségével a cella felett, ezért meg kell kezdeni, legalább 2. sor illeszkedik az értéke „Észak” ellen oszlop, de ahelyett, hogy az egész oszlopot, használjon OFFSET funkció: OFFSET($A$1,F1,0,1000,1).
Mivel F1 értéke 0, ez OFFSET(A1,0,0,1000,1)az A1: A1000. (Az 1000 önkényes, de elég nagy a munka elvégzéséhez - tetszőleges számot megadhat).
Az F2 2. értéke az első „észak”. Vissza szeretné adni az F1 értékét is a végén, de ez eddig nulla.
A „varázslat” az F3 cellában elevenedik meg. Már tudja, hogy az első észak a 2. sorban található. Tehát két sort szeretne keresni az A1 alatt. Ezt úgy teheti meg, hogy 2-t megad az OFFSET függvény második argumentumaként.
Az F3 képlete automatikusan arra a 2-re mutat, amelyet az F2 cellában számítottak: Ha a képletet lefelé másolja, akkor meglátja =OFFSET($A$1,F2,0,1000,1), OFFSET($A$1,2,0,1000,1)melyik az A3: A1000. Tehát az északhoz illeszkedik az új tartományhoz képest, és északnak találja az új tartomány harmadik celláját, így a MATCH 3-at ad.
Ha összeadja az értéket a fenti cellából, az F2, akkor a 3-at plusz a 2-t vagy az 5-öt látja, amely a második északot tartalmazó sor.
Ez a képlet elég messze van kitöltve az összes érték megszerzéséhez.
Ezzel megkapja azokat a sorszámokat, ahol az összes északi rekord megtalálható.
Hogyan fordíthatja le ezeket a sorszámokat az I – K oszlop eredményére? Mindezt egyetlen képlettel végezzük. Írja be ezt a képletet I2: =IFERROR(INDEX(A:A,$F2),””). Másolja jobbra, majd másolja le.
Miért érdemes használni az IFERROR-t? Hol a hiba? Figyelje meg az F6 cellát - # N / A-t tartalmaz (ezért szeretné elrejteni az F oszlopot), mert a 15. sor után már nincsenek északiak. Tehát, ha az F oszlop hibás, adjon meg egy üres mezőt. Ellenkező esetben vegye fel az értéket az A oszlopból (és jobbra kitöltve a B & C-t).
Az $ F2 abszolút hivatkozás az F oszlopra, így a kitöltési jog továbbra is az F oszlopra vonatkozik.
Ez a vendég cikk az Excel MVP Bob Umlas-tól származik. Ez az egyik kedvenc technikája az Excel Outside the Box című könyvéből.
Excel a dobozon kívül »
