Испитајте ову цифру:
Претпоставимо да из овога желите да направите извештај као да сте филтрирали регион. Односно, ако филтрирате на северу, видећете:
Али шта ако желите исту верзију засновану на формули?
Ево резултата који тражите у колони И: К:
Јасно је да је то исти извештај, али овде нема филтрираних ставки. Ако желите нови извештај о Истоку, било би лепо да једноставно промените вредност у Г1 у Исток:
Ево како се то ради. Пре свега, то се не ради помоћу ВЛООКУП-а. Тако сам лагао о наслову ове технике!
Колона Ф раније није била приказана и може се сакрити (или преместити негде другде, тако да не омета извештај).
Оно што је приказано у колони Ф су бројеви редова где се Г1 налази у колони А; односно који редови садрже вредност „Север“? Ова техника подразумева коришћење ћелију горе, тако да морају да почну у најмање реду 2. То одговара вредности "Север" против колони А, али уместо целе колоне, користите ОФФСЕТ функцију: OFFSET($A$1,F1,0,1000,1).
Пошто је Ф1 0, ово OFFSET(A1,0,0,1000,1)је А1: А1000. (1000 је произвољно, али довољно велико за обављање посла - можете га направити било којим другим бројем).
Вредност 2 у Ф2 је место где је први „север“. Такође на крају желите да додате вредност Ф1, али ово је засад нула.
„Магија“ оживљава у ћелији Ф3. Већ знате да се први север налази у 2. реду. Дакле, желите да започнете претрагу два реда испод А1. То можете учинити тако што ћете као други аргумент функције ОФФСЕТ навести 2.
Формула у Ф3 ће се аутоматски указују на 2 који је обрачунат у ћелији Ф2: Када копирате формулу доле, видећете =OFFSET($A$1,F2,0,1000,1)који је OFFSET($A$1,2,0,1000,1)који је О3: А1000. Дакле, подударате Север са овим новим опсегом и он проналази Север у трећој ћелији овог новог опсега, тако да МАТЦХ даје 3.
Додавањем вредности из горње ћелије Ф2 видећете 3 плус 2 или 5, што је ред који садржи други север.
Ова формула је попуњена довољно далеко да добије све вредности.
Тако ћете добити бројеве редова у којима су пронађени сви записи са севера.
Како превести те бројеве редова у резултате у колонама од И до К? Све се то ради помоћу једне формуле. Унесите формулу у И2: =IFERROR(INDEX(A:A,$F2),””). Копирајте десно, а затим копирајте доле.
Зашто користити ИФЕРРОР? Где је грешка? Приметите ћелију Ф6 - она садржи # Н / А (због чега бисте желели да сакријете колону Ф) јер после реда 15 више нема Северних, па ако је колона Ф грешка, вратите празно. У супротном, преузмите вредност из колоне А (и када се попуни удесно, Б & Ц).
$ Ф2 је апсолутна референца на колону Ф, тако да се право попуњавања и даље односи на колону Ф.
Овај гостујући чланак је из програма Екцел МВП Боб Умлас. То је једна од његових омиљених техника из његове књиге, Екцел Оутсиде тхе Бок.
Екцел изван оквира »
