Splošna formula
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete, partial_expanding,0)),0))
Povzetek
Če želite primerjati dva seznama in potegniti manjkajoče vrednosti z enega seznama na drugega, lahko uporabite matrično formulo, ki temelji na INDEX in MATCH. V prikazanem primeru je zadnja vrednost na seznamu B v celici D11. Kopirana formula v D12 je:
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete,$D$5:D11,0)),0))
kjer je "popoln" imenovani obseg B5: B15.
Opomba: to je matrična formula, ki jo je treba vnesti s tipko control + shift + enter.
Pojasnilo
Jedro te formule, ki deluje od znotraj navzven, je notranji izraz MATCH:
ISNA(MATCH(complete,$D$5:D11,0)
Tu se funkcija MATCH uporablja za primerjavo vseh "popolnih" vrednosti z delnim seznamom. Imenovani obseg "Complete" se uporablja za iskalne vrednosti, delni seznam pa kot iskalno polje. Upoštevajte pa, da je delni seznam vnesen kot razširljiv obseg, ki se konča "eno celico nad" celico formule. To omogoča delni seznam, da se razširi in vključi nove vrednosti, kot so prikazane pod prvotnim seznamom.
Rezultat MATCH je niz številk in # N / A napak, kjer številke predstavljajo vrednosti na celotnem seznamu, ki obstajajo na delnem seznamu; in napake predstavljajo manjkajoče vrednosti:
(1;#N/A;2;3;#N/A;4;5;6;#N/A;7;#N/A)
Funkcija ISNA se uporablja za pretvorbo teh rezultatov v niz TRUE in FALSE vrednosti. V tem polju TRUE ustreza manjkajočim vrednostim, FALSE pa obstoječim vrednostim:
(FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE)
Funkcija ISNA vrne to polje zunanjemu MATCH kot iskalno polje. Funkcija MATCH vedno vrne prvo najdeno ujemanje, zato bo ujemanje vrnilo položaj (vrstico) prve najdene manjkajoče vrednosti. Ta rezultat se vrne v INDEX kot številka vrstice, z imenovanim obsegom "popolno" kot matriko.
V celici D12 je prva najdena manjkajoča vrednost "kivi" v vrstici 2, zato imamo:
=INDEX(complete,2) // returns "kiwi"
V D13 je "kivi" zdaj vključen v razširjajočo se referenco, zato je prva manjkajoča vrednost "hruška":
=INDEX(complete,5) // returns "pear"
In tako naprej. Ko so dodane vse manjkajoče vrednosti, bo formula vrnila napako # N / A.
