Formula generik
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete, partial_expanding,0)),0))
Ringkasan
Untuk membandingkan dua senarai dan menarik nilai yang hilang dari satu senarai ke senarai yang lain, anda boleh menggunakan formula array berdasarkan INDEX dan MATCH. Dalam contoh yang ditunjukkan, nilai terakhir dalam senarai B adalah dalam sel D11. Rumus dalam D12, disalin, adalah:
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete,$D$5:D11,0)),0))
di mana "lengkap" adalah julat bernama B5: B15.
Catatan: ini adalah formula larik dan mesti dimasukkan dengan kawalan + shift + enter.
Penjelasan
Berfungsi dari dalam ke luar, inti dari formula ini adalah ungkapan MATCH dalaman:
ISNA(MATCH(complete,$D$5:D11,0)
Di sini, fungsi MATCH digunakan untuk membandingkan semua nilai "lengkap" dengan senarai separa. Julat bernama "lengkap" digunakan untuk nilai pencarian, dan senarai separa digunakan sebagai tatasusunan pencarian. Namun, perhatikan bahawa senarai separa dimasukkan sebagai julat pengembangan yang mengakhiri "satu sel di atas" sel formula. Ini membolehkan senarai separa berkembang untuk memasukkan nilai-nilai baru seperti yang muncul di bawah senarai asal.
Hasil MATCH adalah susunan nombor dan kesalahan # N / A, di mana nombor mewakili nilai dalam senarai lengkap yang terdapat dalam senarai separa; dan kesalahan menunjukkan nilai yang hilang:
(1;#N/A;2;3;#N/A;4;5;6;#N/A;7;#N/A)
Fungsi ISNA digunakan untuk mengubah hasil ini menjadi susunan nilai BENAR dan SALAH. Dalam array ini, TRUE sesuai dengan nilai yang hilang dan FALSE sesuai dengan nilai yang ada:
(FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE)
Fungsi ISNA mengembalikan array ini ke MATCH luar sebagai tatasusunan. Fungsi MATCH selalu mengembalikan perlawanan pertama yang dijumpai, jadi perlawanan akan mengembalikan kedudukan (baris) dari nilai hilang pertama yang dijumpai. Hasil ini dikembalikan ke INDEX sebagai nombor baris, dengan julat bernama "lengkap" disediakan sebagai array.
Dalam sel D12, nilai pertama yang hilang adalah "kiwi" pada baris 2, jadi kami mempunyai:
=INDEX(complete,2) // returns "kiwi"
Pada D13, "kiwi" kini disertakan dalam rujukan yang berkembang, jadi nilai pertama yang hilang adalah "pir":
=INDEX(complete,5) // returns "pear"
Dan sebagainya. Setelah semua nilai yang hilang ditambahkan, formula akan mengembalikan kesalahan # N / A.
