Екцел формула: Пронађите и преузмите вредности које недостају -

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

=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete, partial_expanding,0)),0))

Резиме

Да бисте упоредили две листе и повукли вредности које недостају са једне листе на другу, можете користити формулу низа засновану на ИНДЕКС и МАТЦХ. У приказаном примеру, последња вредност на листи Б је у ћелији Д11. Копирана формула у Д12 је:

=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete,$D$5:D11,0)),0))

где је „комплетан“ именовани опсег Б5: Б15.

Напомена: ово је формула низа и мора се унети са цонтрол + схифт + ентер.

Објашњење

Радећи изнутра према ван, срж ове формуле је унутрашњи израз МАТЦХ:

ISNA(MATCH(complete,$D$5:D11,0)

Овде се функција МАТЦХ користи за упоређивање свих „комплетних“ вредности са делимичном листом. Именовани опсег „комплетан“ користи се за вредности претраживања, а делимична листа се користи као низ претраживања. Међутим, приметите да се делимична листа уноси као опсег који се шири и који завршава „једну ћелију изнад“ ћелије формуле. Ово омогућава проширивање делимичне листе тако да укључује нове вредности онако како се појављују испод оригиналне листе.

Резултат МАТЦХ-а је низ бројева и # Н / А грешака, где бројеви представљају вредности на целој листи које постоје на делимичној листи; а грешке представљају вредности које недостају:

(1;#N/A;2;3;#N/A;4;5;6;#N/A;7;#N/A)

Функција ИСНА користи се за претварање ових резултата у низ вредности ТРУЕ и ФАЛСЕ. У овом низу ТРУЕ одговара вредностима које недостају, а ФАЛСЕ одговара постојећим вредностима:

(FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE)

Функција ИСНА враћа овај низ спољашњем МАТЦХ-у као низ претраживања. Функција МАТЦХ увек враћа прво пронађено подударање, тако да ће матцх вратити положај (ред) прве пронађене вредности која недостаје. Овај резултат се враћа у ИНДЕКС као број реда, са именованим опсегом "комплетан" као низом.

У ћелији Д12, прва пронађена вредност која недостаје је "киви" у реду 2, тако да имамо: