Undersök denna siffra:
Antag att du vill producera en rapport från detta som om du har filtrerat på regionen. Det vill säga om du filtrerar på North, skulle du se:
Men tänk om du ville ha en formelbaserad version av samma sak?
Här är resultatet du letar efter i kolumner I: K:
Det är uppenbart att det är samma rapport, men det finns inga filtrerade objekt här. Om du ville ha en ny rapport om East, skulle det vara trevligt att helt enkelt ändra värdet i G1 till East:
Så här är det gjort. Först och främst är det inte gjort med VLOOKUP. Så jag ljög om titeln på denna teknik!
Kolumn F visades inte tidigare och den kan döljas (eller flyttas någon annanstans så att den inte stör rapporten).
Vad som visas i kolumn F är radnumren där G1 finns i kolumn A; det vill säga vilka rader innehåller värdet "Nord"? Denna teknik innebär att man använder cellen ovan, så det måste börja i åtminstone rad 2. Den matchar värdet ”North” mot kolumn A, men i stället för hela kolumnen, använda en OFFSET funktion: OFFSET($A$1,F1,0,1000,1).
Eftersom F1 är 0 är OFFSET(A1,0,0,1000,1)detta A1: A1000. (1000 är godtycklig, men tillräckligt stor för att göra jobbet - du kan göra det till något annat nummer).
Värdet 2 i F2 är där det första "norr" är. Du vill också lägga tillbaka värdet på F1 i slutet, men det är hittills noll.
”Magin” kommer till liv i cell F3. Du vet redan att det första norr finns i rad 2. Så du vill börja söka två rader under A1. Du kan göra det genom att ange 2 som det andra argumentet för OFFSET-funktionen.
Formeln i F3 pekar automatiskt på 2 som beräknades i cell F2: När du kopierar formeln ner ser du =OFFSET($A$1,F2,0,1000,1)vilken OFFSET($A$1,2,0,1000,1)som är A3: A1000. Så du matchar norr mot det nya området och det hittar norr i den tredje cellen i det nya området, så MATCH ger 3.
Genom att lägga tillbaka värdet från cellen ovan, F2, kommer du att se 3 plus 2 eller 5, som är raden som innehåller det andra norr.
Denna formel är tillräckligt fylld för att få alla värden.
Det ger dig radnumren där alla norra poster finns.
Hur översätter du dessa radnummer till resultaten i kolumnerna I till K? Allt görs med en enda formel. Ange denna formel i I2: =IFERROR(INDEX(A:A,$F2),””). Kopiera rätt och kopiera sedan ner.
Varför använda IFERROR? Var är felet? Observera cell F6 - den innehåller # N / A (det är därför du vill dölja kolumn F) eftersom det inte finns fler norr efter rad 15. Så om kolumn F är ett fel, returnera ett tomt. Annars hämtar du värdet från kolumn A (och när det fylls till höger, B & C).
$ F2 är en absolut referens till kolumn F så fyllningsrätten hänvisar fortfarande till kolumn F.
Den här gästartikeln kommer från Excel MVP Bob Umlas. Det är en av hans favorittekniker från hans bok, Excel Outside the Box.
Excel utanför lådan »
