Exceli valem: puuduvate väärtuste leidmine ja toomine -

Lang L: none (table-of-contents)

Üldine valem

=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete, partial_expanding,0)),0))

Kokkuvõte

Kahe loendi võrdlemiseks ja puuduvate väärtuste ühest loendist teise tõmbamiseks võite kasutada massiivi valemit, mis põhineb INDEXil ja MATCHil. Näidatud näites on loendi B viimane väärtus lahtris D11. D12 valem, mis on kopeeritud, on järgmine:

=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete,$D$5:D11,0)),0))

kus "täielik" on nimega vahemik B5: B15.

Märkus: see on massiivi valem ja see tuleb sisestada juhtklahviga + shift + enter.

Selgitus

Seestpoolt töötades on selle valemi tuum sisemine MATCH-väljend:

ISNA(MATCH(complete,$D$5:D11,0)

Siin kasutatakse funktsiooni MATCH kõigi "täielike" väärtuste võrdlemiseks osalise loendiga. Nimetatud vahemikku "täielik" kasutatakse otsingu väärtuste jaoks ja osalist loendit kasutatakse otsingu massiivina. Pange tähele, et osaline loend sisestatakse laieneva vahemikuna, mis lõpeb valemirakuga "ühe lahtri kohal". See võimaldab osalist loendit laiendada, lisades uued väärtused, nagu need ilmuvad algse loendi all.

MATCHi tulemus on arvude ja # N / A vigade massiiv, kus numbrid tähistavad täielikus loendis olevaid väärtusi, mis eksisteerivad osalises loendis; ja vead tähistavad puuduvaid väärtusi:

(1;#N/A;2;3;#N/A;4;5;6;#N/A;7;#N/A)

ISNA funktsiooni kasutatakse nende tulemuste teisendamiseks TRUE ja FALSE väärtuste massiiviks. Selles massiivis vastab TRUE puuduvatele väärtustele ja FALSE vastab olemasolevatele väärtustele:

(FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE)

Funktsioon ISNA tagastab selle massiivi välimisele MATCH-le kui otsingumassiivile. Funktsioon MATCH tagastab alati esimese leitud vaste, nii et vaste tagastab esimese leitud puuduva väärtuse positsiooni (rea). See tulemus tagastatakse INDEX-ile rea numbrina, massiivina antakse nimega vahemik "täielik".

Lahtris D12 on esimene puuduv väärtus 2. real "kiivi", nii et meil on: