Untuk menggabungkan hasil pencarian berukuran variabel di Excel untuk Mac, gunakan REDUCE dengan VSTACK untuk membuat satu larik persegi panjang. Panduan ini membuat daftar aset seni, mempertahankan urutan permintaan, dan menampilkan grup yang tidak cocok alih-alih menghilangkannya diam-diam.

Tugasnya sederhana: setiap grup aset yang diminta dapat cocok dengan beberapa baris sumber, satu baris, atau tidak ada baris. Anda ingin semua hasil tersebut berada dalam satu rentang lembar kerja yang mengembang, dengan label aset dan kuantitas tetap berpasangan.

Versi Excel untuk Mac mana yang mendukung pendekatan ini?

Gunakan Excel untuk Microsoft 365 untuk Mac untuk rumus utama di bawah ini. Microsoft secara eksplisit mencantumkan edisi tersebut di dokumentasi fungsi REDUCE. Halaman terkininya tidak mencantumkan Excel 2024 untuk Mac, jadi keberadaan fungsi larik modern lainnya saja tidak membuktikan bahwa seluruh rumus ini tersedia.

Fungsi Edisi Mac yang tercantum dalam dokumentasi Microsoft terkait Peran di sini
BYROW, MAP Microsoft 365 untuk Mac; Excel 2024 untuk Mac Menjelaskan pendekatan awal dan batasannya
REDUCE Microsoft 365 untuk Mac Menyimpan hasil yang terus bertambah di antara permintaan
VSTACK, HSTACK Microsoft 365 untuk Mac; Excel 2024 untuk Mac Menggabungkan blok hasil dan membuat baris pengganti
FILTER Microsoft 365 untuk Mac; Excel 2024 untuk Mac; Excel 2021 untuk Mac Mengembalikan setiap baris aset yang cocok

Daftar edisi ini berasal dari halaman referensi Microsoft untuk BYROW, MAP, VSTACK, HSTACK, dan FILTER.

Untuk memeriksa instalasi Anda, buka menu Excel lalu pilih About Excel. Petunjuk Microsoft untuk Mac menjelaskan bahwa dialog About pada aplikasi Office menampilkan versi dan jenis lisensinya. Periksa keduanya saat menentukan rumus yang akan digunakan. Lihat Tentang Office: Versi Office apa yang saya gunakan?.

Pratinjau Beta Mac yang lebih baru mengubah batas larik bertingkat

Pengumuman Microsoft tanggal 24 September 2026 memperkenalkan larik bertingkat dan FLATTEN sebagai fitur pratinjau untuk Beta Channel. Pengumuman tersebut menetapkan Mac Version 16.114 Build 26092111 atau yang lebih baru, dengan ketersediaan bergantung pada peluncuran bertahap. Sebagian besar perhitungan larik bertingkat memerlukan Compatibility Version buku kerja 3, yang dipilih melalui Formula > Calculation Options. Ketentuan ini penting: memiliki Microsoft 365 saja tidak membuktikan bahwa fitur pratinjau telah diaktifkan. Lihat Masukkan beberapa nilai dalam satu sel dengan daftar dan larik di Excel.

Solusi yang dijelaskan di bawah ini membuat hasil persegi panjang biasa tanpa mengandalkan fitur pratinjau tersebut. Dengan demikian, tugas dasar buku kerja untuk membuat daftar aset tidak bergantung pada pengaturan larik bertingkat.

Mengapa mengembalikan larik dari BYROW atau MAP menimbulkan masalah?

Microsoft mendokumentasikan BYROW sebagai fungsi yang menghasilkan satu hasil untuk setiap baris input. Referensinya secara eksplisit menyatakan bahwa jika fungsi tersebut mengembalikan apa pun selain satu nilai dari LAMBDA di dalamnya, akan muncul kesalahan #CALC!. Pencarian yang mengembalikan beberapa rekaman melampaui batas yang terdokumentasi tersebut. Lihat fungsi BYROW.

Sebagai contoh, setelah memasukkan data sampel di bagian berikutnya, ini adalah contoh diagnostik dari pendekatan bermasalah berdasarkan perilaku nilai tunggal yang terdokumentasi:

=BYROW(E2:E4,LAMBDA(key,FILTER(A2:C7,A2:A7=key,"No match")))

Permintaan pertama mengembalikan tiga baris dan tiga kolom. Meminta BYROW menempatkan blok tersebut pada satu posisi hasil akan menimbulkan masalah larik bertingkat. Mengubah fungsi luarnya menjadi MAP tidak menyediakan operasi penumpukan umum dalam perilaku perhitungan konvensional: hasil di dalamnya tetap terdiri atas beberapa sel. Dokumentasi Microsoft tentang MAP menjelaskan pemetaan nilai input ke nilai baru, sedangkan panduan #CALC! mendokumentasikan batasan larik bertingkat.

Membungkus perhitungan yang gagal dengan VSTACK tidak dapat memperbaiki hasil di dalamnya. Perubahan yang berguna adalah menambahkan setiap blok pencarian saat memproses permintaan, sehingga setiap hasil perantara sudah berupa satu larik persegi panjang.

Buat pencarian aset lengkap dengan REDUCE dan VSTACK

1. Masukkan data sumber sintetis

Mulailah dengan lembar kerja kosong. Tempel blok bertab ini ke A1. Kuantitasnya merupakan contoh jumlah aset seni.

Group\tAsset\tQuantity
Mosaic\tTile Amber\t2
Orbit\tRing Silver\t3
Mosaic\tTile Blue\t4
Prism\tPanel Clear\t5
Mosaic\tTile Coral\t1
Prism\tPanel Frost\t2

Tempel daftar permintaan berikut ke E1. Pertahankan urutan yang ditampilkan agar Anda dapat membandingkan hasil yang diharapkan.

Requested group
Mosaic
Orbit
Pebble

Sampel ini sengaja mencakup tiga hasil: Mosaic memiliki tiga rekaman yang cocok, Orbit memiliki satu, dan Pebble tidak memiliki yang cocok. Rekaman Prism tidak boleh muncul dalam hasil karena grup tersebut tidak diminta.

2. Periksa satu pencarian secara terpisah

Masukkan rumus ini di area yang tidak digunakan, seperti L1:

=FILTER($A$2:$C$7,$A$2:$A$7=$E$2,"No match")

Rumus tersebut seharusnya mengembalikan tiga rekaman Mosaic. FILTER menggunakan perbandingan grup untuk memilih baris lengkap dari sumber. Larik penyertaannya harus memiliki tinggi atau lebar yang kompatibel; di sini, sumber dan kondisinya sama-sama mencakup baris 2:7. Lihat fungsi FILTER.

Pemeriksaan sederhana ini mengisolasi pencarian dari logika penumpukan. Jika baris-baris tersebut salah, perbaiki label atau referensi sumber sebelum menambahkan perhitungan luar.

3. Masukkan rumus penumpukan lengkap

Sisakan ruang di kolom G:I, lalu masukkan rumus ini di G1:

=REDUCE(
  $A$1:$C$1,
  $E$2:$E$4,
  LAMBDA(acc,key,
    VSTACK(
      acc,
      FILTER(
        $A$2:$C$7,
        $A$2:$A$7=key,
        HSTACK(key,"No match","")
      )
    )
  )
)

Tekan Return. Masukkan rumus sekali saja di sel kiri atas area hasil; Excel akan mengatur ukuran rentang luapan secara otomatis. Letakkan rumus di luar tabel Excel karena rumus larik yang meluap tidak didukung di dalam tabel. Microsoft menjelaskan kedua perilaku tersebut dalam Rumus larik dinamis dan perilaku luapan larik.

4. Bandingkan dengan hasil yang diharapkan

Untuk data yang diberikan, hasil menempati G1:I6:

Grup Aset Kuantitas
Mosaic Tile Amber 2
Mosaic Tile Blue 4
Mosaic Tile Coral 1
Orbit Ring Silver 3
Pebble Tidak cocok

Dimensi hasil yang diharapkan langsung mengikuti sampel: satu baris tajuk, tiga baris Mosaic, satu baris Orbit, dan satu baris untuk permintaan yang tidak cocok. Empat rekaman yang cocok memiliki total kuantitas 10. Kuantitas kosong di samping Pebble membedakan hasil pencarian yang tidak ditemukan dari kuantitas aset bernilai nol.

Cara rumus menyusun hasil

REDUCE dimulai dengan A1:C1 sebagai akumulatornya. Untuk setiap nilai dalam E2:E4, key menyimpan grup yang sedang diminta dan acc menyimpan hasil yang sudah tersusun. Microsoft mendokumentasikan pola nilai awal, akumulator, dan nilai saat ini ini dalam fungsi REDUCE.

Dalam rumus ini, FILTER membuat blok berikutnya yang cocok dan VSTACK menambahkannya di bawah acc. Menggunakan tajuk sebenarnya sebagai nilai awal langsung menghasilkan tiga kolom dan menghindari penambahan baris awal kosong yang nantinya harus dibuang. VSTACK menambahkan larik secara vertikal berurutan, sebagaimana dijelaskan dalam fungsi VSTACK.

Jika tidak ada hasil yang cocok, argumen ketiga FILTER menyediakan HSTACK(key,"No match",""). Ini membuat baris pengganti tiga kolom: grup yang diminta, label penjelas, dan kuantitas kosong. Microsoft mendokumentasikan argumen untuk hasil kosong dalam fungsi FILTER dan penggabungan horizontal dalam fungsi HSTACK.

Penting untuk mempertahankan lebar tiga kolom pada pengganti tersebut. Microsoft mendokumentasikan bahwa VSTACK mengisi kolom yang kurang dengan #N/A jika lebar larik inputnya berbeda. Pengganti satu sel akan menyisakan pengisian yang tidak diinginkan di sebelahnya. Lihat fungsi VSTACK.

Tentukan perilaku hasil sebelum memperluas lembar kerja

Rumus ini menerapkan kebijakan tertentu: memproses setiap permintaan sesuai urutan, mempertahankan semua baris sumber yang cocok, dan menampilkan satu baris penanda untuk grup yang tidak cocok. Karena itu, grup yang sama jika diulang dalam daftar permintaan akan mengulangi blok yang cocok. Rekaman sumber yang berulang juga tetap dipertahankan; rumus ini tidak menghapus duplikat.

Gunakan panduan keputusan ini saat menyesuaikan contoh:

Hasil yang diinginkan Pendekatan yang sesuai Ketentuan
Satu hitungan untuk setiap grup MAP dengan COUNTIF Setiap permintaan menghasilkan satu nilai
Semua rekaman aset sesuai urutan permintaan Rumus lengkap REDUCE di atas Memerlukan fungsi Microsoft 365 untuk Mac yang tercantum dalam dokumentasi
Beberapa blok pencarian yang dicantumkan secara eksplisit VSTACK langsung dari beberapa pemanggilan FILTER Tambahkan argumen rumus untuk setiap permintaan
Larik bertingkat dipertahankan di dalam sel Pratinjau larik bertingkat Microsoft Memerlukan ketersediaan Beta dan pengaturan buku kerja yang tercantum dalam dokumentasi

Untuk pemeriksaan hitungan ringkas, masukkan rumus lengkap ini di K6 dan sisakan tiga sel kosong di bawahnya:

=MAP($E$2:$E$4,LAMBDA(key,COUNTIF($A$2:$A$7,key)))

Hitungan yang diharapkan adalah 3, 1, dan 0. Ini adalah penggunaan MAP yang tepat karena setiap permintaan menghasilkan satu angka. COUNTIF menghitung sel yang memenuhi kriteria; contoh ini menggunakan label sintetis sederhana. Lihat fungsi MAP dan Menggunakan fungsi COUNTIF di Microsoft Excel.

Alternatif permintaan tetap untuk Excel 2024 untuk Mac

Jika edisi Anda menyediakan FILTER, HSTACK, dan VSTACK, tetapi tidak menyediakan REDUCE, gunakan alternatif lengkap ini di G1 untuk menggantikan rumus sebelumnya. Alternatif ini menghasilkan keluaran yang sama seperti yang diharapkan untuk ketiga permintaan yang diberikan:

=VSTACK(
  $A$1:$C$1,
  FILTER($A$2:$C$7,$A$2:$A$7=$E$2,HSTACK($E$2,"No match","")),
  FILTER($A$2:$C$7,$A$2:$A$7=$E$3,HSTACK($E$3,"No match","")),
  FILTER($A$2:$C$7,$A$2:$A$7=$E$4,HSTACK($E$4,"No match",""))
)

Microsoft mencantumkan Excel 2024 untuk Mac dalam dokumentasi ketiga fungsi yang diperlukan: FILTER, HSTACK, dan VSTACK. Komprominya adalah pemeliharaan: grup permintaan tambahan memerlukan satu argumen FILTER lagi.

Diagnosis kesalahan dan pastikan hasil tetap lengkap

Untuk kesalahan #SPILL!, pilih sel rumus dan periksa area hasil yang dimaksud. Pindahkan entri yang ada dari area tersebut dan periksa apakah ada sel gabungan. Microsoft juga menyebutkan bahwa rumus yang diletakkan di dalam tabel dapat menyebabkan masalah; tempatkan hasil di kisi lembar kerja biasa. Lihat Cara memperbaiki kesalahan #SPILL!.

Untuk #CALC!, pertama-tama cari tahu perhitungan mana yang menghasilkannya. Rumus BYROW yang mengembalikan blok beberapa sel mencapai batas nilai tunggal yang terdokumentasi. Sebaliknya, FILTER yang berdiri sendiri tanpa hasil cocok dan tanpa pengganti dapat menghasilkan kesalahan larik kosong. Keduanya merupakan penyebab terpisah yang dijelaskan dalam panduan #CALC! dari Microsoft.

Saat menambahkan rekaman sumber, perluas $A$2:$C$7 dan $A$2:$A$7 hingga baris akhir yang sama. Saat menambahkan permintaan ke versi REDUCE, perluas $E$2:$E$4 hingga permintaan terakhir yang diinginkan. Hindari menyertakan sel permintaan yang tidak digunakan: rumus akan memproses setiap sel dalam rentang tersebut.

Untuk daftar sumber yang sering bertambah, pertimbangkan menyimpan sumber dalam tabel Excel dan mengganti referensi sumber tetap dengan referensi terstruktur. Microsoft mendokumentasikan bahwa referensi terstruktur akan menyesuaikan diri saat baris tabel ditambahkan atau dihapus. Letakkan hasil yang meluap di luar tabel. Lihat Rumus larik dinamis dan perilaku luapan larik.

Sebelum menggunakan daftar aset yang tersusun, bandingkan blok grupnya dengan daftar permintaan dan periksa penanda yang tidak cocok. Dalam contoh ini, pemeriksaan penyelesaian yang berguna cukup jelas: tiga rekaman Mosaic, satu rekaman Orbit, satu penanda Pebble yang terlihat, dan tidak ada rekaman Prism. Hal ini memastikan pemilihan dan urutan yang dimaksud untuk data yang diberikan.

Sumber