Undersøg dette tal:
Antag at du vil producere en rapport ud fra dette, som om du har filtreret på regionen. Det vil sige, hvis du filtrerer på nord, vil du se:
Men hvad hvis du ville have en formelbaseret version af den samme ting?
Her er det resultat, du leder efter i kolonne I: K:
Det er klart, at det er den samme rapport, men der er ingen filtrerede varer her. Hvis du ville have en ny rapport om øst, ville det være rart at blot ændre værdien i G1 til øst:
Sådan gør du det. Først og fremmest er det ikke gjort ved hjælp af VLOOKUP. Så jeg løj om titlen på denne teknik!
Kolonne F blev ikke vist før, og den kan skjules (eller flyttes et andet sted, så den ikke forstyrrer rapporten).
Hvad der vises i kolonne F er rækkenumrene for hvor G1 findes i kolonne A; det vil sige, hvilke rækker indeholder værdien "Nord"? Denne teknik indebærer anvendelse af cellen ovenfor, så det skal begynde i mindst 2. række svarer til den værdi ”nord” mod kolonne A, men i stedet for hele kolonnen, bruge en OFFSET funktion: OFFSET($A$1,F1,0,1000,1).
Da F1 er 0, er dette OFFSET(A1,0,0,1000,1)A1: A1000. (1000 er vilkårlig, men stor nok til at udføre jobbet - du kan lave det til ethvert andet nummer).
Værdien 2 i F2 er, hvor den første "Nord" er. Du vil også tilføje værdien af F1 i slutningen, men det er hidtil nul.
”Magien” kommer til live i celle F3. Du ved allerede, at det første nord findes i række 2. Så du vil begynde at søge to rækker under A1. Du kan gøre det ved at angive 2 som det andet argument for OFFSET-funktionen.
Formlen i F3 peger automatisk på de 2, der blev beregnet i celle F2: Når du kopierer formlen ned, vil du se, =OFFSET($A$1,F2,0,1000,1)hvad OFFSET($A$1,2,0,1000,1)der er A3: A1000. Så du matcher Nord mod dette nye område, og det finder Nord i den tredje celle i dette nye område, så MATCH giver 3.
Ved at tilføje værdien fra cellen ovenfor, F2, vil du se 3 plus 2 eller 5, som er rækken, der indeholder det andet nord.
Denne formel er udfyldt langt nok til at få alle værdierne.
Det giver dig rækkenumrene, hvor alle nordregistreringer findes.
Hvordan oversætter du disse rækkenumre til resultaterne i kolonne I til K? Det hele gøres med en enkelt formel. Indtast denne formel i I2: =IFERROR(INDEX(A:A,$F2),””). Kopier til højre og kopier derefter ned.
Hvorfor bruge IFERROR? Hvor er fejlen? Bemærk celle F6 - den indeholder # N / A (det er derfor, du gerne vil skjule kolonne F), fordi der ikke er flere nord'er efter række 15. Så hvis kolonne F er en fejl, skal du returnere et tomt. Ellers samler du værdien fra kolonne A (og når den er udfyldt til højre, B & C).
$ F2 er en absolut reference til kolonne F, så udfyldningsretten henviser stadig til kolonne F.
Denne gæsteartikel er fra Excel MVP Bob Umlas. Det er en af hans yndlingsteknikker fra hans bog, Excel uden for boksen.
Excel uden for boksen »
