Untuk menggabungkan keputusan carian berukuran pemboleh ubah dalam Excel untuk Mac, gunakan REDUCE dengan VSTACK untuk membina satu larik segi empat tepat. Panduan ini mencipta senarai aset seni, mengekalkan urutan permintaan, dan memaparkan kumpulan tidak sepadan sebagai ganti menyingkirkannya secara senyap.

Tugas ini mudah: setiap kumpulan aset yang diminta boleh sepadan dengan beberapa baris sumber, satu baris, atau tiada. Anda mahu semua keputusan itu dalam satu julat lembar kerja yang berkembang, dengan label dan kuantiti aset kekal bersama.

Versi Excel untuk Mac yang mana menyokong pendekatan ini?

Gunakan Excel untuk Microsoft 365 untuk Mac untuk formula utama di bawah. Microsoft nyatakan edisi itu secara nyata pada dokumentasi fungsi REDUCE. Halaman semasa tidak menyenaraikan Excel 2024 untuk Mac, jadi kehadiran fungsi larik moden lain sendirian tidak membuktikan formula lengkap tersedia.

Fungsi Edisi Mac yang disenaraikan dalam dokumentasi Microsoft yang berkaitan Peranan di sini
BYROW, MAP Microsoft 365 untuk Mac; Excel 2024 untuk Mac Terangkan pendekatan asal dan sempadan
REDUCE Microsoft 365 untuk Mac Bawa keputusan yang berkembang di antara permintaan
VSTACK, HSTACK Microsoft 365 untuk Mac; Excel 2024 untuk Mac Gabungkan blok keputusan dan bina baris fallback
FILTER Microsoft 365 untuk Mac; Excel 2024 untuk Mac; Excel 2021 untuk Mac Kembalikan setiap baris aset yang sepadan

Penyenaraian edisi ini datang dari halaman rujukan Microsoft BYROW, MAP, VSTACK, HSTACK, dan FILTER.

Untuk memeriksa pemasangan anda, buka menu Excel dan pilih About Excel. Arahan Mac Microsoft menjelaskan kotak dialog About aplikasi Office memaparkan versi dan jenis lesen. Semak kedua-duanya semasa memutuskan formula mana untuk digunakan. Lihat About Office: What version of Office am I using?.

Pratinjau Beta Mac yang lebih baharu menukar sempadan larik bertingkat

Pengumuman Microsoft pada 24 September 2026 memperkenalkan larik bertingkat dan FLATTEN sebagai ciri pratonton untuk Saluran Beta. Ia menentukan Versi Mac 16.114 Build 26092111 atau kemudiaan, dengan ketersediaan tertakluk kepada pelancaran. Kebanyakan pengiraan larik bertingkat memerlukan Versi Keserasian Buku Kerja 3, dipilih melalui Formula > Calculation Options. Syarat-syarat ini penting: memiliki Microsoft 365 semata-mata tidak membuktikan pratonton diaktifkan. Lihat Put multiple values in one cell with lists and arrays in Excel.

Penyelesaian yang dikendalikan di bawah membina keputusan segi empat tepat biasa tanpa bergantung pada pratonton itu. Ini juga mengelakkan tugas senarai aset asas buku kerja bergantung pada tetapan larik bertingkat.

Mengapa mengembalikan larik dari BYROW atau MAP menyebabkan masalah?

Microsoft mendokumentasikan BYROW sebagai menghasilkan satu keputusan setiap baris input. Rujukannya menyatakan secara nyata bahawa mengembalikan apa-apa selain nilai tunggal dari LAMBDA dalaman menghasilkan ralat #CALC!. Carian yang mengembalikan beberapa rekod melampaui sempadan yang didokumentasikan. Lihat BYROW function.

Sebagai contoh, selepas memasukkan data contoh dalam bahagian seterusnya, ini adalah contoh diagnostik pendekatan bermasalah di bawah tingkah laku nilai tunggal yang didokumentasikan:

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

Permintaan pertama mengembalikan tiga baris dan tiga lajur. Meminta BYROW untuk meletakkan blok itu ke satu posisi keputusan mencipta masalah larik bertingkat. Menukar fungsi luar kepada MAP tidak menyediakan operasi penumpukan am di bawah tingkah laku pengiraan konvensional: keputusan dalaman masih mengandungi banyak sel. Dokumentasi MAP Microsoft menerangkan pemetaan nilai input ke nilai baru, manakala panduan #CALC! mendokumentasikan had larik bertingkat.

Melitup pengiraan gagal dalam VSTACK tidak dapat membaiki keputusan dalaman. Perubahan yang berguna adalah melampirkan setiap blok carian semasa memproses permintaan, supaya setiap keputusan pertengahan sudah satu larik segi empat tepat.

Bina carian aset lengkap dengan REDUCE dan VSTACK

1. Masukkan data sumber sintetik

Mula dengan lembar kerja kosong. Tempelkan blok berpisah tab ini ke dalam A1. Kuantiti adalah kiraan ilustratif aset seni.

Group	Asset	Quantity
Mosaic	Tile Amber	2
Orbit	Ring Silver	3
Mosaic	Tile Blue	4
Prism	Panel Clear	5
Mosaic	Tile Coral	1
Prism	Panel Frost	2

Tempelkan senarai permintaan berikut ke dalam E1. Kekalkan urutan yang dipaparkan supaya anda boleh membandingkan keputusan dijangka.

Requested group
Mosaic
Orbit
Pebble

Contoh ini sengaja mengandungi tiga hasil: Mosaic mempunyai tiga rekod sepadan, Orbit mempunyai satu, dan Pebble tidak ada. Rekod Prism harus kekal di luar output kerana kumpulan itu tidak diminta.

2. Semak satu carian secara bebas

Masukkan formula ini ke kawasan tidak digunakan, seperti L1:

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

Ia sepatutnya mengembalikan tiga rekod Mosaic. FILTER menggunakan perbandingan kumpulan untuk memilih baris lengkap dari sumber. Larik pemasukan mesti mempunyai ketinggian atau lebar yang serasi; di sini kedua-dua sumber dan syarat meliputi baris 2:7. Lihat FILTER function.

Semakan kecil ini memisahkan carian dari logik penumpukan. Jika baris ini salah, betulkan label atau rujukan sumber sebelum menambah pengiraan luar.

3. Masukkan formula penumpukan lengkap

Bersihkan ruang dalam lajur G:I, kemudian masukkan formula ini dalam 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 formula sekali di sel output kiri-atas; Excel mengukur julat tumpah secara automatik. Kekalkan di luar jadual Excel, kerana formula larik tumpah tidak disokong di dalam jadual. Microsoft menerangkan kedua-dua tingkah laku dalam Dynamic array formulas and spilled array behavior.

4. Bandingkan keputusan dijangka

Untuk data yang disediakan, output memenuhi G1:I6:

Kumpulan Aset Kuantiti
Mosaic Tile Amber 2
Mosaic Tile Blue 4
Mosaic Tile Coral 1
Orbit Ring Silver 3
Pebble Tiada sepadan

Dimensi dijangka mengikuti terus dari contoh: satu baris header, tiga baris Mosaic, satu baris Orbit, dan satu baris permintaan tidak sepadan. Empat rekod sepadan mengandungi kuantiti menjumlah 10. Kuantiti kosong di sebelah Pebble membezakan keputusan carian yang hilang dari kuantiti aset sifar.

Bagaimana formula merakam keputusan

REDUCE bermula dengan A1:C1 sebagai pengumpulnya. Untuk setiap nilai dalam E2:E4, key memegang kumpulan diminta semasa dan acc memegang keputusan yang dikumpul sehingga kini. Microsoft mendokumentasikan corak nilai awal, pengumpul, dan nilai semasa dalam REDUCE function.

Dalam formula ini, FILTER mencipta blok sepadan seterusnya dan VSTACK melampirkannya di bawah acc. Menggunakan header sebenar sebagai nilai awal memberikan keputusan tiga lajur serta-merta dan mengelakkan memperkenalkan baris permulaan kosong pelupusan. VSTACK melampirkan larik menegak secara berurutan, seperti yang digambarkan dalam VSTACK function.

Apabila tiada sepadan, argumen ketiga FILTER menyediakan HSTACK(key,"No match",""). Ini mencipta baris fallback tiga lajur: kumpulan diminta, label penjelasan, dan kuantiti kosong. Microsoft mendokumentasikan argumen keputusan kosong dalam FILTER function dan penyertaan mendatar dalam HSTACK function.

Mengekalkan fallback tiga lajur lebar itu penting. Microsoft mendokumentasikan VSTACK mengisi lajur yang hilang dengan #N/A apabila larik inputnya mempunyai lebar berbeza. Fallback sel tunggal akan meninggalkan padding tidak dikehendaki di sebelahnya. Lihat VSTACK function.

Pilih tingkah laku output sebelum meluaskan lembar kerja

Formula ini melaksanakan polisi tertentu: memproses setiap permintaan mengikut urutan, mengekalkan semua baris sumber sepadan, dan memaparkan satu baris penanda untuk kumpulan tidak sepadan. Mengulang kumpulan dalam senarai permintaan dengan itu mengulang blok sepadan. Mengulang rekod sumber juga mengekalkan rekod itu; formula ini tidak mengandungi langkah penyingkiran gandaan.

Gunakan bantuan keputusan ini semasa menyesuaikan contoh:

Keputusan dikehendaki Pendekatan sesuai Syarat
Satu kiraan untuk setiap kumpulan MAP dengan COUNTIF Setiap permintaan menghasilkan satu nilai
Semua rekod aset dalam urutan permintaan Formula REDUCE lengkap di atas Memerlukan fungsi Microsoft 365 untuk Mac yang didokumentasikan
Beberapa blok carian yang dinyatakan dengan nyata VSTACK langsung panggilan FILTER berasingan Tambah argumen formula untuk setiap permintaan
Larik bertingkat disimpan di dalam sel Pratinjau larik bertingkat Microsoft Memerlukan ketersediaan Beta yang didokumentasikan dan tetapan buku kerja

Untuk semakan kiraan ringkas, masukkan formula lengkap ini dalam K6, membiarkan tiga sel di bawahnya tersedia:

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

Kiraan dijangka ialah 3, 1, dan 0. Ini adalah penggunaan yang sesuai untuk MAP kerana setiap permintaan menghasilkan satu nombor. COUNTIF mengira sel yang memenuhi kriteria; contoh menggunakan label sintetik mudah. Lihat MAP function dan Use the COUNTIF function in 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 dalam G1, menggantikan formula sebelum. Ia menghasilkan output dijangka yang sama untuk tiga permintaan yang disediakan:

=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 menyenaraikan Excel 2024 untuk Mac pada dokumentasi untuk ketiga-tiga fungsi yang diperlukan: FILTER, HSTACK, dan VSTACK. Pertukarannya adalah penyelenggaraan: kumpulan diminta yang lain memerlukan argumen FILTER yang lain.

Diagnosa ralat dan kekalkan keputusan lengkap

Untuk ralat #SPILL!, pilih sel formula dan periksa kawasan output yang dikehendaki. Pindahkan entri sedia ada keluar dari kawasan itu dan semak sel yang digabung. Microsoft juga mengenal pasti formula yang diletakkan di dalam jadual sebagai sebab; kekalkan output dalam grid lembar kerja biasa. Lihat How to correct a #SPILL! error.

Untuk #CALC!, kenal pasti terlebih dahulu pengiraan mana yang menghasilkannya. Formula BYROW yang mengembalikan blok multi-sel mencapai sempadan nilai tunggal yang didokumentasikan. FILTER berasingan tanpa sepadan dan tanpa fallback sebaliknya boleh menghasilkan ralat larik kosong. Ini adalah punca berasingan yang digambarkan dalam panduan #CALC! Microsoft.

Apabila menambah rekod sumber, estendkan kedua-dua $A$2:$C$7 dan $A$2:$A$7 ke baris tamat yang sama. Apabila menambah permintaan ke versi REDUCE, estendkan $E$2:$E$4 ke permintaan terakhir yang dikehendaki. Elakkan mengandungi sel permintaan tidak digunakan: formula memproses setiap sel dalam julat itu.

Untuk senarai sumber yang berkembang secara berkala, pertimbangkan untuk menyimpan sumber dalam jadual Excel dan menggantikan rujukan sumber tetap dengan rujukan berstruktur. Microsoft mendokumentasikan rujukan berstruktur menyesuaikan apabila baris jadual ditambah atau dibuang. Kekalkan keputusan tumpah di luar jadual. Lihat Dynamic array formulas and spilled array behavior.

Sebelum menggunakan senarai aset yang dirakam, bandingkan blok kumpulan dengan senarai permintaan dan semak penanda tidak sepadan. Dalam contoh ini, semakan penyelesaian yang berguna adalah konkret: tiga rekod Mosaic, satu rekod Orbit, satu penanda Pebble nampak, dan tiada rekod Prism. Itu mengesahkan pemilihan dan urutan yang dikehendaki untuk data yang disediakan.

Sumber