Bendroji formulė
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete, partial_expanding,0)),0))
Santrauka
Norėdami palyginti du sąrašus ir trūkstamas reikšmes traukti iš vieno sąrašo į kitą, galite naudoti masyvo formulę, pagrįstą INDEX ir MATCH. Parodytame pavyzdyje paskutinė B sąrašo reikšmė yra langelyje D11. D12 formulė, nukopijuota žemyn, yra:
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete,$D$5:D11,0)),0))
kur „pilnas“ yra įvardytas diapazonas B5: B15.
Pastaba: tai yra masyvo formulė, kurią reikia įvesti su „Control“ + „Shift“ + „Enter“.
Paaiškinimas
Dirbant iš vidaus, šios formulės esmė yra vidinė MATCH išraiška:
ISNA(MATCH(complete,$D$5:D11,0)
Čia MATCH funkcija naudojama palyginti visas „visas“ reikšmes su daliniu sąrašu. Pavadintas diapazonas „baigtas“ naudojamas paieškos reikšmėms, o dalinis sąrašas naudojamas kaip paieškos masyvas. Tačiau atkreipkite dėmesį, kad dalinis sąrašas įvedamas kaip besiplečiantis diapazonas, kuris baigiasi formulės langeliu „viena langeliu aukščiau“. Tai leidžia išplėsti dalinį sąrašą, įtraukiant naujas reikšmes, kaip jos rodomos po pradiniu sąrašu.
MATCH rezultatas yra skaičių ir # N / A klaidų masyvas, kur skaičiai reiškia viso sąrašo reikšmes, esančias daliniame sąraše; ir klaidos rodo trūkstamas vertes:
(1;#N/A;2;3;#N/A;4;5;6;#N/A;7;#N/A)
ISNA funkcija naudojama šiems rezultatams konvertuoti į TRUE ir FALSE reikšmių masyvą. Šiame masyve TRUE atitinka trūkstamas reikšmes, o FALSE - esamas vertes:
(FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE)
ISNA funkcija grąžina šį masyvą į išorinį MATCH kaip paieškos masyvą. Funkcija ATITIKTI visada pateikia pirmąją rastą atitiktį, taigi atitiktis grąžins pirmosios rastos vertės poziciją (eilutę). Šis rezultatas grąžinamas į INDEX kaip eilutės numeris, nurodant masyvą nurodytą diapazoną „pilnas“.
D12 langelyje pirmoji trūkstama vertė yra „kivi“ 2 eilutėje, taigi mes turime:
=INDEX(complete,2) // returns "kiwi"
D13 „kivi“ dabar įtrauktas į besiplečiančią nuorodą, todėl pirmoji trūksta reikšmės yra „kriaušė“:
=INDEX(complete,5) // returns "pear"
Ir taip toliau. Pridėjus visas trūkstamas reikšmes, formulė pateiks klaidą # N / A.
