Gunakan IFNA di sekitar lookup yang tumpah untuk mengisi hasil #N/A-nya dari tabel referensi kedua. Contoh Excel untuk Mac ini mencocokkan label aset seni, mempertahankan kuantitas yang sudah ditemukan di tabel utama, dan menandai dengan jelas label yang tidak ditemukan di kedua tabel.

Versi Excel untuk Mac mana yang mendukung pendekatan ini?

Gunakan Excel untuk Microsoft 365 untuk Mac, Excel 2024 untuk Mac, atau Excel 2021 untuk Mac. Microsoft mencantumkan edisi-edisi ini untuk XLOOKUP maupun IFNA. Dokumentasi XLOOKUP secara eksplisit mengecualikan Excel 2016 dan Excel 2019, meskipun edisi tersebut tercantum dalam daftar penerapan yang lebih luas di halaman itu. Rumus di bawah ditujukan untuk edisi Mac yang didukung. Fungsi XLOOKUP, Fungsi IFNA

Tugasnya spesifik: Anda memiliki satu kolom label aset yang diminta dan menginginkan satu kuantitas untuk setiap label. Referensi utama diutamakan; referensi kedua menyediakan kuantitas hanya jika lookup utama menghasilkan #N/A. Semua label dan kuantitas di bawah ini bersifat sintetis.

Siapkan dua tabel referensi dan daftar aset yang diminta

Mulailah di lembar kerja kosong dalam satu buku kerja. Gunakan lokasi sel di bawah agar rumus berfungsi tanpa mengubah referensinya. Tabel referensi kecil ini merupakan rentang lembar kerja biasa; Anda tidak perlu mengonversinya menjadi objek Tabel Excel.

Masukkan referensi utama di D1:E4:

Sel di kolom D Label aset Sel di kolom E Kuantitas
D1 Aset E1 Kuantitas
D2 Garis tepi daun E2 8
D3 Tekstur titik E3 0
D4 Pola ubin E4 12

Masukkan referensi kedua di G1:H5:

Sel di kolom G Label aset Sel di kolom H Kuantitas
G1 Aset H1 Kuantitas
G2 Tekstur titik H2 9
G3 Sketsa lengkung H3 5
G4 Tekstur serat H4 3
G5 Garis tepi daun H5 10

Masukkan Aset yang diminta di J1, lalu masukkan lima label ini di J2:J6, masing-masing satu per sel dan dengan urutan berikut:

  1. Garis tepi daun
  2. Tekstur titik
  3. Sketsa lengkung
  4. Tekstur serat
  5. Motif busur

Pastikan kuantitas berupa angka dan isi setiap sel kuantitas di kedua referensi. Untuk contoh ini, nol berarti kuantitas yang tercatat adalah nol. Artinya bukan aset tersebut tidak ada.

Buat daftar label yang tumpah

Masukkan judul Aset, Kuantitas utama, dan Kuantitas lengkap di A1, B1, dan C1. Kosongkan A2:C20, lalu masukkan rumus ini di A2:

=FILTER(J2:J20,J2:J20<>"","")

Rumus ini menyaring sel kosong dari daftar yang diminta. Dengan data yang diberikan, rumus ini menumpahkan lima label aset ke A2:A6. Microsoft mendokumentasikan FILTER untuk tiga edisi Mac yang tercantum di atas dan menjelaskan bahwa argumen terakhirnya menyediakan hasil jika tidak ada baris yang memenuhi kondisi. Di sini, hasilnya adalah string kosong. Fungsi FILTER

Rumus berikutnya menggunakan A2# untuk merujuk ke seluruh rentang tumpahan ini. Operator # mengikuti ukuran rentang tumpahan, sehingga Anda tidak perlu mengodekan titik akhir saat ini sebagai A6. Microsoft menjelaskan perilaku ini dalam dokumentasi operator rentang tumpahan.

Isi kuantitas yang hilang dari referensi kedua

Pertama, buat lookup utama di B2:

=XLOOKUP(A2#,$D$2:$D$4,$E$2:$E$4)

Rumus ini mencari kolom aset utama dan mengembalikan kuantitas yang sesuai. XLOOKUP menggunakan pencocokan tepat secara default dan mengembalikan #N/A jika tidak ada kecocokan dan tidak ada hasil alternatif yang diberikan. Fungsi XLOOKUP

Untuk contoh ini, B2:B6 seharusnya berisi 8, 0, #N/A, #N/A, dan #N/A. Biarkan kesalahan kecocokan yang hilang tersebut tetap ada untuk langkah berikutnya.

Sekarang masukkan rumus pelengkap di C2:

=IFNA(B2#,XLOOKUP(A2#,$G$2:$G$5,$H$2:$H$5,"Not listed"))

IFNA memeriksa rentang tumpahan utama. Pada posisi yang berisi #N/A, rumus memberikan hasil yang sesuai dari lookup kedua. Hasil utama lainnya diteruskan tanpa perubahan. Microsoft secara khusus mendokumentasikan bahwa IFNA mengembalikan larik hasil ketika argumen pertamanya berupa rumus larik. Fungsi IFNA

Hasil lembar kerja yang diharapkan adalah:

Aset di kolom A Kuantitas utama di kolom B Kuantitas lengkap di kolom C
Garis tepi daun 8 8
Tekstur titik 0 0
Sketsa lengkung #N/A 5
Tekstur serat #N/A 3
Motif busur #N/A Tidak tercantum

Baca baris-baris ini sebagai pengujian prioritas. Garis tepi daun tetap 8 meskipun referensi kedua berisi 10. Tekstur titik tetap 0 meskipun referensi kedua berisi 9. Sketsa lengkung dan Tekstur serat mendapatkan kuantitas dari referensi kedua. Motif busur tetap jelas belum terselesaikan karena tidak ada di kedua referensi.

Kedua larik harus menggambarkan aset yang diminta dalam urutan yang sama. Dalam penyiapan ini, keduanya berasal dari A2#. Jika Anda menyesuaikan buku kerja yang sudah ada, jangan gabungkan hasil utama yang diurutkan dengan satu cara dengan lookup cadangan yang didasarkan pada daftar label berurutan berbeda.

Gunakan satu rumus jika Anda tidak memerlukan kolom perantara

Untuk menggabungkan lookup utama dan pelengkap, ganti rumus di C2 dengan alternatif lengkap ini:

=IFNA(
    XLOOKUP(A2#,$D$2:$D$4,$E$2:$E$4),
    XLOOKUP(A2#,$G$2:$G$5,$H$2:$H$5,"Not listed")
)

Rumus ini menggunakan data contoh yang sama dan seharusnya menghasilkan lima kuantitas lengkap yang sama. Kolom B tidak lagi diperlukan untuk perhitungan. Mempertahankan versi dua tahap saat menyiapkan buku kerja memudahkan Anda melihat referensi mana yang menyediakan setiap hasil.

Tentukan apakah suatu hasil memang tidak ada

Sebelum menyesuaikan rumus, periksa hasil yang dikembalikan lookup pertama. Label yang tidak ditemukan, hasil yang tampak kosong, dan kuantitas nol memerlukan keputusan yang berbeda.

Hasil utama Tindakan rumus pelengkap ini Hal yang perlu diperiksa
Kuantitas numerik, termasuk 0 Mempertahankannya Pastikan itu kuantitas utama yang dimaksud.
#N/A Menggunakan lookup kedua Pastikan kesalahan tersebut menandakan label tidak ada.
String kosong, \"\" Mempertahankannya Periksa apakah rumus utama menyembunyikan kecocokan yang tidak ditemukan.
Kesalahan lain Membiarkannya terlihat Perbaiki rumus atau referensi yang mendasarinya.

Misalnya, jika lookup utama yang sudah ada menggunakan \"\" sebagai argumen if_not_found, IFNA tidak dapat mengenali string kosong itu sebagai #N/A. Hapus argumen tersebut dari lookup utama, seperti yang ditunjukkan di B2, atau gunakan rumus gabungan di atas. Logika pelengkap bergantung pada dipertahankannya hasil kecocokan yang tidak ditemukan dalam bentuk yang dapat dikenali. Fungsi XLOOKUP, Fungsi IFNA

Periksa juga kuantitas referensi itu sendiri. IFNA menangani nilai kesalahan; fungsi ini tidak menguji apakah sel kuantitas sudah terisi. Jika catatan utama yang cocok memiliki kuantitas yang belum diisi, lengkapi catatan itu atau tentukan aturan terpisah untuk kuantitas yang belum diisi sebelum menerapkan alur kerja ini.

Ada satu perbedaan lagi: kesalahan #N/A yang sudah ada di dalam data kuantitas utama juga akan memicu pencarian cadangan ini. Untuk contoh ini, prasyaratnya adalah kolom kuantitas berisi angka yang valid, dengan #N/A hanya muncul akibat lookup yang tidak berhasil. Periksa kondisi tersebut sebelum menganggap setiap penggantian sebagai label aset yang tidak ditemukan.

Periksa prioritas dan pastikan rentang tumpahan tetap berfungsi

Gunakan lima baris contoh sebagai pemeriksaan penerimaan sederhana sebelum menggantinya dengan label aset Anda sendiri. Secara keseluruhan, baris-baris ini menguji kecocokan utama, nol yang valid, dua kecocokan sekunder, dan label yang tidak ada di kedua referensi.

Untuk pemeriksaan tambahan, ubah sementara kuantitas sekunder Garis tepi daun dari 10 menjadi 14. Kuantitas lengkap seharusnya tetap 8. Lalu ubah kuantitas sekunder Sketsa lengkung dari 5 menjadi 6; kuantitas lengkapnya seharusnya menjadi 6. Setelah itu, pulihkan nilai semula. Pemeriksaan ini hanya ilustrasi untuk buku kerja Anda, bukan hasil pengujian Excel yang dilaporkan.

Pertahankan satu catatan yang dimaksud untuk setiap label aset di masing-masing referensi. XLOOKUP biasanya mencari dari item pertama dan mengembalikan kecocokan pertama, jadi jangan berharap label berulang akan menggabungkan kuantitas. Selesaikan arti label duplikat sebelum mengandalkan desain lookup ini. Fungsi XLOOKUP

Perbaiki rentang keluaran yang terhalang

Masukkan setiap rumus yang menumpahkan hasil hanya di sel awalnya. Excel mengisi sel-sel di sebelahnya secara otomatis, dan hanya sel kiri atas dari rentang tumpahan yang dapat diedit. Letakkan rumus di luar objek Tabel Excel; Microsoft mendukung tabel sebagai data sumber, tetapi tidak mendukung rumus yang menumpahkan hasil di dalamnya. Rumus larik dinamis dan perilaku larik tumpahan

Jika Anda melihat #SPILL!, pilih sel rumus untuk menampilkan batas keluaran yang dimaksud. Gunakan opsi Select Obstructing Cells pada peringatan kesalahan, jika tersedia, lalu pindahkan entri yang menghalangi hasil. Sel yang digabungkan juga dapat mencegah hasil tumpahan; pindahkan keluaran atau pisahkan sel yang terdampak. Cara memperbaiki kesalahan #SPILL!

Perluas masukan dengan sengaja

Rumus daftar yang diminta memeriksa J2:J20. Label yang ditambahkan dalam rentang tersebut akan disertakan secara otomatis; label yang dimasukkan di bawahnya mengharuskan rentang rumus diperluas. Demikian juga, lookup referensi menggunakan rentang tetap yang berakhir di baris 4 dan 5. Perluas setiap rentang aset beserta rentang kuantitas yang sesuai saat menambahkan catatan.

Untuk lembar kerja yang lebih besar, Tabel Excel dengan referensi terstruktur dapat menyesuaikan rentang sumber saat baris ditambahkan atau dihapus. Tetap letakkan rumus keluaran di kisi lembar kerja di luar tabel tersebut. Rumus larik dinamis dan perilaku larik tumpahan

Simpan masukan contoh ini dalam buku kerja yang sama. Microsoft mendokumentasikan dukungan terbatas untuk larik dinamis antar buku kerja: larik dinamis tertaut mengharuskan kedua buku kerja tetap terbuka, dan penyegaran setelah buku kerja sumber ditutup dapat menghasilkan #REF!. Rumus larik dinamis dan perilaku larik tumpahan

Setelah baris yang diharapkan sesuai, gunakan kolom lengkap sebagai hasil kuantitas aset Anda. Pertahankan aturan prioritas utama, jaga agar nilai nol tetap bermakna, dan tandai label yang tidak ada di kedua referensi sebagai Tidak tercantum agar Anda dapat melengkapi data referensi dengan sengaja.

Sumber