Обща формула
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete, partial_expanding,0)),0))
Обобщение
За да сравните два списъка и да изтеглите липсващи стойности от един списък в другия, можете да използвате формула на масив, базирана на INDEX и MATCH. В показания пример последната стойност в списък B е в клетка D11. Формулата в D12, копирана надолу, е:
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete,$D$5:D11,0)),0))
където "пълен" е наименованият диапазон B5: B15.
Забележка: това е формула на масив и трябва да се въведе с control + shift + enter.
Обяснение
Работейки отвътре навън, ядрото на тази формула е вътрешният израз на МАТЧ:
ISNA(MATCH(complete,$D$5:D11,0)
Тук функцията MATCH се използва за сравняване на всички "пълни" стойности спрямо частичния списък. Наименованият диапазон "завършен" се използва за справочни стойности, а частичният списък се използва като справочен масив. Забележете обаче, че частичният списък се въвежда като разширяващ се диапазон, който завършва "една клетка над" клетката с формула. Това позволява частичният списък да се разшири, за да включи нови стойности, както се появяват под първоначалния списък.
Резултатът от MATCH е масив от числа и # N / A грешки, където числата представляват стойности в пълния списък, които съществуват в частичния списък; и грешките представляват липсващи стойности:
(1;#N/A;2;3;#N/A;4;5;6;#N/A;7;#N/A)
Функцията ISNA се използва за преобразуване на тези резултати в масив от TRUE и FALSE стойности. В този масив TRUE съответства на липсващи стойности, а FALSE съответства на съществуващи стойности:
(FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE)
Функцията ISNA връща този масив към външния MATCH като справочен масив. Функцията MATCH винаги връща първото намерено съвпадение, така че съвпадението ще върне позицията (реда) на първата намерена липсваща стойност. Този резултат се връща в INDEX като номер на реда, като посоченият диапазон "завършен" се предоставя като масив.
В клетка D12 първата намерена липсваща стойност е "киви" на ред 2, така че имаме:
=INDEX(complete,2) // returns "kiwi"
В D13 "киви" вече е включен в разширяващата се справка, така че първата липсваща стойност е "круша":
=INDEX(complete,5) // returns "pear"
И така нататък. След като бъдат добавени всички липсващи стойности, формулата ще върне грешката # N / A.
