Vispārēja formula
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete, partial_expanding,0)),0))
Kopsavilkums
Lai salīdzinātu divus sarakstus un izvilktu trūkstošās vērtības no viena saraksta uz otru, varat izmantot masīva formulu, kuras pamatā ir INDEX un MATCH. Parādītajā piemērā B saraksta pēdējā vērtība ir šūnā D11. Formula D12, kas nokopēta uz leju, ir:
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete,$D$5:D11,0)),0))
kur "pabeigts" ir nosauktais diapazons B5: B15.
Piezīme: šī ir masīva formula, un tā jāievada ar vadības taustiņu + shift + enter.
Paskaidrojums
Strādājot no iekšpuses uz āru, šīs formulas kodols ir iekšējā MATCH izteiksme:
ISNA(MATCH(complete,$D$5:D11,0)
Šeit MATCH funkcija tiek izmantota, lai salīdzinātu visas "pilnīgas" vērtības ar daļējo sarakstu. Nosauktais diapazons "pabeigts" tiek izmantots uzmeklēšanas vērtībām, un daļējais saraksts tiek izmantots kā uzmeklēšanas masīvs. Tomēr ņemiet vērā, ka daļējais saraksts tiek ievadīts kā paplašināms diapazons, kas beidzas "viena šūna virs" formulas šūnas. Tas ļauj daļēju sarakstu paplašināt, iekļaujot jaunas vērtības, kādas tās parādās zem sākotnējā saraksta.
MATCH rezultāts ir skaitļu un # N / A kļūdu masīvs, kur skaitļi apzīmē vērtības pilnā sarakstā, kas pastāv daļējā sarakstā; un kļūdas norāda trūkstošās vērtības:
(1;#N/A;2;3;#N/A;4;5;6;#N/A;7;#N/A)
ISNA funkcija tiek izmantota, lai pārvērstu šos rezultātus TRUE un FALSE vērtību masīvā. Šajā masīvā TRUE atbilst trūkstošajām vērtībām un FALSE atbilst esošajām vērtībām:
(FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE)
ISNA funkcija atgriež šo masīvu uz ārējo MATCH kā uzmeklēšanas masīvu. Funkcija MATCH vienmēr atgriež pirmo atrasto atbilstību, tāpēc atbilstība atgriezīs pirmās atrastās vērtības pozīciju (rindu). Šis rezultāts tiek atgriezts INDEX kā rindas numurs, kā masīvu norādot nosaukto diapazonu "pabeigts".
Šūnā D12 pirmā trūkstošā vērtība ir "kivi" 2. rindā, tāpēc mums ir:
=INDEX(complete,2) // returns "kiwi"
D13 sadaļā "kivi" tagad ir iekļauta izvērstajā atsaucē, tāpēc pirmā trūkstošā vērtība ir "bumbieris":
=INDEX(complete,5) // returns "pear"
Un tā tālāk. Kad visas trūkstošās vērtības ir pievienotas, formula atgriezīs kļūdu # N / A.
