Yleinen kaava
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete, partial_expanding,0)),0))
Yhteenveto
Voit vertailla kahta luetteloa ja vetää puuttuvat arvot yhdestä luettelosta toiseen käyttämällä taulukkoa, joka perustuu hakemistoon ja vastaavuuteen. Esitetyssä esimerkissä luettelon B viimeinen arvo on solussa D11. D12: n kaava kopioituna alaspäin on:
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete,$D$5:D11,0)),0))
missä "täydellinen" on nimetty alue B5: B15.
Huomaa: tämä on matriisikaava ja se on syötettävä näppäimillä + + shift + enter.
Selitys
Työskentelemällä sisältä ulospäin, tämän kaavan ydin on sisäinen MATCH-lauseke:
ISNA(MATCH(complete,$D$5:D11,0)
Tässä MATCH-funktiota käytetään vertaamaan kaikkia "täydellisiä" arvoja osaluetteloon. Nimettyä aluetta "täydellinen" käytetään hakuarvoihin ja osaluetteloa käytetään hakutaulukkona. Huomaa kuitenkin, että osittainen luettelo syötetään laajenevana alueena, joka päättyy "yksi solu kaavan solun yläpuolelle". Tämä sallii osittaisen luettelon laajentamisen uusiin arvoihin sellaisina kuin ne näkyvät alkuperäisen luettelon alla.
MATCH-tulos on joukko numeroita ja # N / A-virheitä, joissa luvut edustavat arvoja täydellisessä luettelossa, jotka ovat osittaisessa luettelossa; ja virheet edustavat puuttuvia arvoja:
(1;#N/A;2;3;#N/A;4;5;6;#N/A;7;#N/A)
ISNA-funktiota käytetään näiden tulosten muuntamiseen TOSI- ja EPÄTOSI-arvoiksi. Tässä taulukossa TRUE vastaa puuttuvia arvoja ja FALSE vastaa olemassa olevia arvoja:
(FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE)
ISNA-funktio palauttaa tämän taulukon ulommalle MATCH: lle hakutaulukkona. MATCH-toiminto palauttaa aina ensimmäisen löydetyn vastaavuuden, joten ottelu palauttaa ensimmäisen löydetyn arvon sijainnin (rivin). Tämä tulos palautetaan hakemistoon INDEX rivinumerona, ja taulukoksi on annettu nimetty alue "täydellinen".
Solussa D12 ensimmäinen puuttuva arvo on "kiivi" rivillä 2, joten meillä on:
=INDEX(complete,2) // returns "kiwi"
Kohdassa D13 "kiivi" sisältyy nyt laajenevaan viitteeseen, joten ensimmäinen puuttuva arvo on "päärynä":
=INDEX(complete,5) // returns "pear"
Ja niin edelleen. Kun kaikki puuttuvat arvot on lisätty, kaava palauttaa # N / A -virheen.
