Všeobecný vzorec
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete, partial_expanding,0)),0))
Zhrnutie
Ak chcete porovnať dva zoznamy a vytiahnuť chýbajúce hodnoty z jedného zoznamu do druhého, môžete použiť maticový vzorec založený na indexoch INDEX a MATCH. V zobrazenom príklade je posledná hodnota v zozname B v bunke D11. Vzorec v D12, skopírovaný nadol, je:
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete,$D$5:D11,0)),0))
kde „úplné“ je pomenovaný rozsah B5: B15.
Poznámka: toto je vzorec poľa a musí sa zadávať pomocou klávesov Control + Shift + Enter.
Vysvetlenie
Pri práci zvnútra von je jadrom tohto vzorca vnútorný výraz MATCH:
ISNA(MATCH(complete,$D$5:D11,0)
Tu sa funkcia MATCH používa na porovnanie všetkých „úplných“ hodnôt s čiastkovým zoznamom. Pomenovaný rozsah „dokončený“ sa používa pre vyhľadávacie hodnoty a čiastočný zoznam sa používa ako vyhľadávacie pole. Všimnite si však, že čiastočný zoznam sa zadáva ako rozširujúci sa rozsah, ktorý končí „o jednu bunku vyššie“ bunkou vzorca. To umožňuje čiastočný zoznam rozbaliť a zahrnúť nové hodnoty, ktoré sa zobrazujú pod pôvodným zoznamom.
Výsledkom MATCH je pole čísel a chýb # N / A, kde čísla predstavujú hodnoty v úplnom zozname, ktoré existujú v čiastočnom zozname; a chyby predstavujú chýbajúce hodnoty:
(1;#N/A;2;3;#N/A;4;5;6;#N/A;7;#N/A)
Funkcia ISNA sa používa na prevod týchto výsledkov na pole hodnôt TRUE a FALSE. V tomto poli TRUE zodpovedá chýbajúcim hodnotám a FALSE zodpovedá existujúcim hodnotám:
(FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE)
Funkcia ISNA vráti toto pole do vonkajšej MATCH ako vyhľadávacie pole. Funkcia MATCH vždy vráti prvú nájdenú zhodu, takže match vráti pozíciu (riadok) prvej nájdenej chýbajúcej hodnoty. Tento výsledok sa vráti na INDEX ako číslo riadku a ako pole sa uvedie pomenovaný rozsah „dokončený“.
V bunke D12 je prvou zistenou chýbajúcou hodnotou „kiwi“ v riadku 2, takže máme:
=INDEX(complete,2) // returns "kiwi"
V D13 je „kiwi“ teraz zahrnuté do rozširujúcej referencie, takže prvá chýbajúca hodnota je „hruška“:
=INDEX(complete,5) // returns "pear"
A tak ďalej. Po pridaní všetkých chýbajúcich hodnôt vráti vzorec chybu # N / A.
