Formuła ogólna
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete, partial_expanding,0)),0))
Podsumowanie
Aby porównać dwie listy i wyciągnąć brakujące wartości z jednej listy do drugiej, możesz użyć formuły tablicowej opartej na INDEKSIE i PODAJNIKU. W przedstawionym przykładzie ostatnia wartość na liście B znajduje się w komórce D11. Wzór w D12, skopiowany w dół, to:
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete,$D$5:D11,0)),0))
gdzie „kompletny” to nazwany zakres B5: B15.
Uwaga: to jest formuła tablicowa i należy ją wprowadzić za pomocą Ctrl + Shift + Enter.
Wyjaśnienie
Działając od wewnątrz, rdzeniem tej formuły jest wewnętrzne wyrażenie MATCH:
ISNA(MATCH(complete,$D$5:D11,0)
W tym przypadku funkcja PODAJ.POZYCJĘ służy do porównania wszystkich „kompletnych” wartości z częściową listą. Nazwany zakres „kompletny” jest używany do wyszukiwania wartości, a lista częściowa jest używana jako tablica wyszukiwania. Zwróć jednak uwagę, że lista częściowa jest wprowadzana jako rozszerzany zakres, który kończy się „jedną komórką nad” komórką formuły. Dzięki temu częściowa lista może się rozszerzyć, aby uwzględnić nowe wartości, które pojawiają się pod oryginalną listą.
Wynikiem PODAJNIKA jest tablica liczb i błędów # N / A, gdzie liczby reprezentują wartości z pełnej listy, które istnieją na liście częściowej; a błędy reprezentują brakujące wartości:
(1;#N/A;2;3;#N/A;4;5;6;#N/A;7;#N/A)
Funkcja ISNA służy do konwersji tych wyników na tablicę wartości PRAWDA i FAŁSZ. W tej tablicy TRUE odpowiada brakującym wartościom, a FALSE odpowiada istniejącym wartościom:
(FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE)
Funkcja ISNA zwraca tę tablicę do zewnętrznego MATCH jako tablicę wyszukiwania. Funkcja PODAJ.POZYCJĘ zawsze zwraca pierwsze znalezione dopasowanie, więc funkcja dopasowania zwróci pozycję (wiersz) pierwszej znalezionej brakującej wartości. Wynik ten jest zwracany do INDEKSU jako numer wiersza, a nazwany zakres jest „kompletny” jako tablica.
W komórce D12 pierwsza znaleziona brakująca wartość to „kiwi” w wierszu 2, więc mamy:
=INDEX(complete,2) // returns "kiwi"
W D13 „kiwi” jest teraz uwzględniane w rozwijanym odwołaniu, więc pierwsza brakująca wartość to „gruszka”:
=INDEX(complete,5) // returns "pear"
I tak dalej. Po dodaniu wszystkich brakujących wartości formuła zwróci błąd # N / A.
