Екцел формула: Вишекритеријумско тражење и транспоновање -

Преглед садржаја

Генеричка формула

(=INDEX(rng1,MATCH(1,($A1=rng2)*(B$1=rng3),0)))

Резиме

Да бисте извршили претрагу са више критеријума и транспоновали резултате у табелу, можете користити формулу низа засновану на ИНДЕКС и МАТЦХ. У приказаном примеру, формула у Г5 је:

(=INDEX(amount,MATCH(1,($F5=location)*(G$4=date),0)))

Имајте на уму да је ова формула формула низа и мора се унети са цонтрол + схифт + ентер.

Ова формула такође користи три именована опсега: локација = Б5: Б13, количина = Д5: Д13, датум = Ц5: Ц13

Објашњење

Језгро ове формуле је ИНДЕКС, који преузима вредност из именованог опсега "износ" (Б5: Б13):

=INDEX(amount,row_num)

где је ров_нум разрађен помоћу функције МАТЦХ и неке логичке логике:

MATCH(1,($F5=location)*(G$4=date),0)

У овом исечку локација у Ф5 се упоређује са свим локацијама, а датум у Г4 са свим датумима. Резултат је у сваком случају низ ТРУЕ и ФАЛСЕ вредности. Када се ти низови помноже, математичка операција присиљава вредности ТРУЕ и ФАЛСЕ на оне и нуле, тако да претраживачки низ који иде у МАТЦХ изгледа овако:

(1;0;0;0;0;0;0;0;0)

МАТЦХ је постављен тако да се подудара са 1 као тачно подударање и враћа положај у ИНДЕКС као број реда. Број 1 ради за вредност претраживања јер низ сада садржи само јединице 1 и 0, као што је приказано горе.

Ф5 и Г4 уносе се као мешовите референце, тако да се формула може копирати кроз табелу без измена.