Obecný vzorec
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete, partial_expanding,0)),0))
souhrn
Chcete-li porovnat dva seznamy a stáhnout chybějící hodnoty z jednoho seznamu do druhého, můžete použít maticový vzorec založený na INDEXU a MATCH. V zobrazeném příkladu je poslední hodnota v seznamu B v buňce D11. Zkopírovaný vzorec v D12 je:
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete,$D$5:D11,0)),0))
kde „kompletní“ je pojmenovaný rozsah B5: B15.
Poznámka: toto je maticový vzorec a musí být zadán pomocí kláves Control + Shift + Enter.
Vysvětlení
Při práci zevnitř ven je jádrem tohoto vzorce vnitřní MATCH výraz:
ISNA(MATCH(complete,$D$5:D11,0)
Zde se funkce MATCH používá k porovnání všech „úplných“ hodnot s částečným seznamem. Pojmenovaný rozsah „kompletní“ se používá pro vyhledávací hodnoty a částečný seznam se používá jako vyhledávací pole. Všimněte si však, že částečný seznam je zadán jako rozšiřující se oblast, která končí „o jednu buňku výše“ buňkou vzorce. To umožňuje rozbalení částečného seznamu tak, aby zahrnovalo nové hodnoty, jak se objevují pod původním seznamem.
Výsledkem MATCH je pole čísel a chyb # N / A, kde čísla představují hodnoty v úplném seznamu, které existují v částečném seznamu; a chyby představují chybějící hodnoty:
(1;#N/A;2;3;#N/A;4;5;6;#N/A;7;#N/A)
Funkce ISNA se používá k převodu těchto výsledků do pole TRUE a FALSE hodnot. V tomto poli odpovídá TRUE chybějícím hodnotám a FALSE odpovídá stávajícím hodnotám:
(FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE)
Funkce ISNA vrací toto pole k vnějšímu MATCH jako vyhledávací pole. Funkce MATCH vždy vrátí první nalezenou shodu, takže shoda vrátí pozici (řádek) první nalezené chybějící hodnoty. Tento výsledek je vrácen do INDEXU jako číslo řádku, přičemž jako pole je uveden pojmenovaný rozsah „Complete“.
V buňce D12 je první nalezená chybějící hodnota „kiwi“ v řádku 2, takže máme:
=INDEX(complete,2) // returns "kiwi"
V D13 je „kiwi“ nyní zahrnuto do rozšiřující reference, takže první chybějící hodnota je „hruška“:
=INDEX(complete,5) // returns "pear"
A tak dále. Po přidání všech chybějících hodnot vrátí vzorec chybu # N / A.
