Разгледайте тази цифра:
Да предположим, че искате да изготвите отчет от това, сякаш сте филтрирали региона. Тоест, ако филтрирате на север, ще видите:
Но какво, ако искате версия, базирана на формула на същото нещо?
Ето резултата, който търсите в колони I: K:
Ясно е, че е същият доклад, но тук няма филтрирани елементи. Ако искате нов отчет за Изток, би било хубаво просто да промените стойността в G1 на Изток:
Ето как се прави това. На първо място, това не се прави с помощта на VLOOKUP. Така че излъгах за заглавието на тази техника!
Колона F не е била показана преди и може да бъде скрита (или преместена някъде другаде, за да не пречи на отчета).
Това, което е показано в колона F, е номерът на редовете, където G1 се намира в колона A; т.е. какви редове съдържат стойността „Север“? Тази техника включва използването на клетката по-горе, така че трябва да започне в най-малко ред 2. съответства на стойността "Север" срещу колона А, но вместо на цялата колона, използвайте функцията на отместване: OFFSET($A$1,F1,0,1000,1).
Тъй като F1 е 0, това е OFFSET(A1,0,0,1000,1)кое е A1: A1000. (1000 е произволно, но достатъчно голямо, за да свърши работата - можете да го направите с всяко друго число).
Стойността 2 във F2 е мястото, където е първото „Север“. Също така искате да добавите обратно стойността на F1 в края, но това засега е нула.
„Магията“ оживява в клетка F3. Вече знаете, че първият север се намира в ред 2. И така, искате да започнете да търсите два реда под А1. Можете да направите това, като посочите 2 като втори аргумент на функцията OFFSET.
Формулата във F3 автоматично ще сочи към 2, изчислена в клетка F2: Когато копирате формулата надолу, ще видите =OFFSET($A$1,F2,0,1000,1)кое е OFFSET($A$1,2,0,1000,1)кое е A3: A1000. Така че вие сравнявате Север с този нов диапазон и той намира Север в третата клетка от този нов диапазон, така че МАТЧЪТ дава 3.
Като добавите обратно стойността от клетката по-горе, F2, ще видите 3 плюс 2 или 5, което е редът, който съдържа втория север.
Тази формула се попълва достатъчно далеч, за да получи всички стойности.
Това ще ви даде номерата на редовете, където са намерени всички записи на Север.
Как превеждате тези номера на редове в резултатите в колони от I до K? Всичко се прави с една формула. Въведете тази формула в I2: =IFERROR(INDEX(A:A,$F2),””). Копирайте надясно и след това копирайте надолу.
Защо да използвам IFERROR? Къде е грешката? Забележете клетка F6 - тя съдържа # N / A (поради което бихте искали да скриете колона F), защото след ред 15 няма повече северни, така че ако колона F е грешка, върнете празно. В противен случай вземете стойността от колона A (и когато се попълни вдясно, B & C).
$ F2 е абсолютна препратка към колона F, така че правото на попълване все още се отнася до колона F.
Тази статия за гости е от Excel MVP Боб Умлас. Това е една от любимите му техники от книгата му „Excel извън кутията“.
Excel извън кутията »
