Excel formülü: Eksik değerleri bulun ve alın -

İçindekiler

Genel formül

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

Özet

İki listeyi karşılaştırmak ve eksik değerleri bir listeden diğerine çekmek için, INDEX ve MATCH'a dayalı bir dizi formülü kullanabilirsiniz. Gösterilen örnekte, B listesindeki son değer D11 hücresindedir. D12'deki formül aşağıya kopyalanır:

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

burada "tamamlandı", adlandırılmış B5: B15 aralığıdır.

Not: Bu bir dizi formülüdür ve kontrol + shift + enter ile girilmelidir.

Açıklama

İçten dışa doğru çalışarak, bu formülün özü, iç MATCH ifadesidir:

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

Burada, MATCH işlevi, tüm "tam" değerleri kısmi listeyle karşılaştırmak için kullanılır. "Tam" adlı aralık, arama değerleri için kullanılır ve kısmi liste, arama dizisi olarak kullanılır. Bununla birlikte, kısmi listenin, formül hücresinin "bir hücre yukarısında" sona eren genişleyen bir aralık olarak girildiğine dikkat edin. Bu, kısmi listenin orijinal listenin altında görünen yeni değerleri içerecek şekilde genişlemesine izin verir.

KAÇINCI'nın sonucu, sayılar ve #YOK hataları dizisidir; burada sayılar, kısmi listede bulunan tam listedeki değerleri temsil eder; ve hatalar eksik değerleri temsil eder:

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

ISNA işlevi, bu sonuçları bir DOĞRU ve YANLIŞ değerler dizisine dönüştürmek için kullanılır. Bu dizide, DOĞRU eksik değerlere, YANLIŞ ise mevcut değerlere karşılık gelir:

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

ISNA işlevi, bu diziyi arama dizisi olarak dış MATCH'a döndürür. MATCH işlevi her zaman bulunan ilk eşleşmeyi döndürür, bu nedenle eşleşme, bulunan ilk eksik değerin konumunu (satırını) döndürür. Bu sonuç, dizi olarak sağlanan "tamamlandı" adlı aralıkla birlikte satır numarası olarak INDEX'e döndürülür.

D12 hücresinde, bulunan ilk eksik değer 2. satırdaki "kivi" dir, bu nedenle elimizde: