Gunakan FILTER untuk memulangkan setiap kuantiti yang berkaitan dengan label aset seni dalam Excel untuk Mac. Tatasusunan yang terhasil boleh melimpah ke dalam sel lembaran kerja, digunakan dalam pengiraan lain atau dijumlahkan tanpa kehilangan entri sepadan yang berulang.
Panduan ini menggunakan label dan kuantiti aset grafik sintetik. Tugasnya ialah memilih satu label, mendapatkan semua kuantitinya dan menggunakan semula nombor individu tersebut dalam pengiraan. Microsoft mendokumenkan FILTER sebagai fungsi yang memulangkan tatasusunan berdasarkan kriteria yang ditentukan. Fungsi FILTER.
Versi Excel untuk Mac yang manakah menyokong kaedah ini?
Dokumentasi fungsi Microsoft yang terkini menyenaraikan Excel untuk Microsoft 365 untuk Mac, Excel 2024 untuk Mac dan Excel 2021 untuk Mac secara jelas sebagai menyokong FILTER. Gunakan salah satu edisi tersebut untuk formula di bawah. Fungsi FILTER.
Untuk mengenal pasti pemasangan anda, buka Excel dan pilih Excel → Perihal Excel. Microsoft mendokumenkan laluan menu Mac ini, dan panduan pengenal pasti versinya menjelaskan bahawa dialog Perihal aplikasi Mac memaparkan nombor versi dan jenis lesen. Semak edisi serta nombor binaan. Berkongsi dokumen dengan versi Office untuk Mac yang lain; Perihal Office: Versi Office manakah yang saya gunakan?.
Excel 2019 untuk Mac dan Excel 2016 untuk Mac tidak termasuk dalam ketersediaan FILTER yang didokumenkan. Jika anda menggunakan salah satu edisi itu, cara yang disokong untuk aliran kerja tatasusunan dinamik khusus ini ialah membuka buku kerja dalam salah satu edisi Mac yang disenaraikan. Arahan di bawah bergantung pada prasyarat tersebut; ini bukan penyelesaian formula tatasusunan legasi.
Sediakan contoh kecil kuantiti aset
Pada lembaran kerja kosong, masukkan data berikut dalam A1:B7. Baris pertama mengandungi pengepala. Masukkan kuantiti sebagai nombor, termasuk sifar dalam B6.
| Baris lembaran kerja | Lajur A: Aset | Lajur B: Kuantiti |
|---|---|---|
| 1 | Aset | Kuantiti |
| 2 | Tile A | 4 |
| 3 | Arc B | 7 |
| 4 | Tile A | 2 |
| 5 | Dot C | 5 |
| 6 | Tile A | 0 |
| 7 | Tile A | 4 |
Masukkan Aset yang dipilih dalam D1 dan Tile A dalam D2. Masukkan Kuantiti yang sepadan dalam F1. Biarkan sel di bawah F2 kosong supaya hasilnya mempunyai ruang untuk berkembang.
Contoh ini sengaja menyertakan dua kuantiti bernilai empat dan satu kuantiti bernilai sifar bagi label yang sama. Hasil yang betul mesti mengekalkan keempat-empat baris sepadan: 4, 2, 0 dan 4. Membuang nilai berulang atau mengecualikan sifar akan mengubah hasil yang diminta.
Pulangkan semua kuantiti sepadan dengan FILTER
Pilih F2, masukkan formula berikut dan tekan Return:
=FILTER(B2:B7,A2:A7=D2)
Formula itu menggunakan B2:B7 sebagai tatasusunan kuantiti dan A2:A7=D2 sebagai ujian kemasukan. Julat ini meliputi enam baris sumber yang sama, memenuhi keperluan Microsoft bahawa tatasusunan kemasukan mempunyai tinggi atau lebar yang serasi. Fungsi FILTER.
Dengan data contoh, hasilnya memenuhi F2:F5:
| Sel | Hasil |
|---|---|
| F2 | 4 |
| F3 | 2 |
| F4 | 0 |
| F5 | 4 |
Masukkan formula hanya dalam F2; Excel meletakkan hasil tambahan dalam sel bersebelahan secara automatik. Microsoft menamakan tingkah laku ini sebagai limpahan. Untuk mengubah formula kemudian, edit sel di penjuru kiri atas hasil, iaitu F2 di sini. Formula tatasusunan dinamik dan tingkah laku limpahan tatasusunan.
Kedua-dua kemunculan nilai empat mewakili baris sumber sepadan yang berasingan. Kekalkan kedua-duanya apabila pengiraan seterusnya perlu mengambil kira setiap kemunculan aset yang dipilih.
Mengapa XLOOKUP tidak mendapatkan keempat-empat baris ini
Microsoft menerangkan XLOOKUP sebagai fungsi yang memulangkan item bagi padanan pertama yang ditemuinya. Fungsi itu boleh memulangkan berbilang lajur daripada baris yang sepadan, tetapi keupayaan tersebut tidak menyenaraikan setiap baris yang mengandungi label yang sama. Fungsi XLOOKUP.
Contohnya, formula berikut hanya memulangkan 4 daripada sampel:
=XLOOKUP(D2,A2:A7,B2:B7)
Hasil itu sesuai apabila satu baris sepadan memberikan jawapannya. Untuk tugas ini, empat baris sepadan yang berasingan memberikan kuantiti, jadi gunakan hasil FILTER.
Gunakan semula tatasusunan dalam pengiraan seterusnya
Rujuk hasil lengkap dengan F2#. Microsoft mendokumenkan tanda pagar sebagai operator julat limpahan: ia merujuk kepada keseluruhan tatasusunan yang dihasilkan daripada sel permulaan. Ini mengelakkan formula seterusnya daripada terikat pada hasil empat sel semasa. Garis panduan dan contoh formula tatasusunan.
Letakkan formula seterusnya di sebelah hasil, bukan betul-betul di bawahnya, supaya ada ruang untuk lebih banyak kuantiti sepadan. Contoh berikut menggunakan lajur H hingga J.
Tambahkan semua kuantiti sepadan
Masukkan Jumlah kuantiti dalam H1, kemudian masukkan formula ini dalam H2:
=SUM(F2#)
Hasil ilustrasi ialah 10: empat tambah dua tambah sifar tambah empat. SUM menerima julat dan menambahkan nilai berangkanya. Fungsi SUM.
Jika anda hanya memerlukan jumlah, anda boleh mengiranya terus dalam sel kosong lain:
=SUM(FILTER(B2:B7,A2:A7=D2))
Pilih tatasusunan yang kelihatan apabila anda mahu menyemak atau menggunakan semula kuantiti individu. Pilih formula bersarang apabila lembaran kerja hanya memerlukan jumlah akhir.
Darabkan setiap kuantiti dengan faktor yang sama
Katakan ilustrasi memerlukan tiga salinan bagi setiap kuantiti sepadan. Masukkan Salinan dalam I1 dan nombor 3 dalam I2. Masukkan Kuantiti terlaras dalam J1, kemudian masukkan formula ini dalam J2:
=F2#*$I$2
Hasil yang dijangka dalam J2:J5 ialah 12, 6, 0 dan 12. Microsoft mendokumenkan pengiraan tatasusunan yang mendarabkan nilai dan memulangkan berbilang hasil, dan panduan kalkulator Macnya mengenal pasti asterisk sebagai operator pendaraban. Garis panduan dan contoh formula tatasusunan; Gunakan Excel sebagai kalkulator anda dalam Excel untuk Mac.
Untuk jumlah terlaras tunggal, masukkan yang berikut dalam H4:
=SUM(F2#*$I$2)
Jumlah yang dijangka ialah 30. Ini ialah pengiraan ilustrasi menggunakan data sintetik yang dibekalkan. Aritmetik telah disemak secara berasingan; formula buku kerja diperiksa berbanding dokumentasi Microsoft, bukan dijalankan dalam Excel untuk Mac.
Tentukan tindakan apabila tiada padanan
Tukar D2 kepada Wave D, label yang tiada dalam sampel. Formula FILTER dua argumen yang asal memulangkan #CALC! kerana Excel tidak dapat memulangkan tatasusunan kosong. Microsoft mendokumenkan argumen ketiga pilihan sebagai cara untuk membekalkan nilai apabila tiada hasil. Cara membetulkan ralat #CALC!.
Pilih nilai itu berdasarkan keperluan pengiraan seterusnya. Panduan membuat keputusan berikut terpakai pada lembaran kerja ini:
| Keperluan | Formula atau pendekatan | Akibat |
|---|---|---|
| Padanan yang tiada perlu kekal kelihatan semasa pengiraan | Kekalkan =FILTER(B2:B7,A2:A7=D2) |
Tiada padanan menghasilkan #CALC!, lalu mendorong siasatan. |
| Hasilnya terutamanya senarai yang mudah dibaca | Gunakan =FILTER(B2:B7,A2:A7=D2,"No matches") |
Tiada padanan memulangkan mesej teks. |
| Label yang tiada perlu menyumbang sifar kepada jumlah | Gunakan =SUM(FILTER(B2:B7,A2:A7=D2,0)) |
Tiada padanan menghasilkan jumlah sifar. |
Nilai sandaran sifar ialah peraturan pengiraan yang disengajakan. Ini tidak bermakna baris sumber sepadan wujud dengan kuantiti sifar. Dalam sampel ini, Tile A mempunyai kuantiti sifar yang sebenar; Wave D tiada baris sumber. Kekalkan perbezaan itu apabila kewujudan aset penting.
Mesej teks juga memerlukan perhatian: Microsoft mendokumenkan bahawa SUM mengabaikan teks dalam sel yang dirujuk. Oleh itu, menjumlahkan hasil yang hanya mengandungi Tiada padanan boleh menghasilkan sifar dan menyembunyikan ketiadaan baris sepadan. Gunakan pendekatan ralat yang kelihatan apabila pengiraan seterusnya patut terhenti jika label tiada. Fungsi SUM.
Pulihkan D2 kepada Tile A sebelum meneruskan contoh angka asal.
Tambahkan syarat kedua hanya apabila tugas memerlukannya
Jika tugas seterusnya secara khusus meminta kuantiti positif, gunakan ujian label dan ujian kuantiti positif:
=FILTER(B2:B7,(A2:A7=D2)*(B2:B7>0))
Bagi sampel ini, hasilnya ialah 4, 2 dan 4. Microsoft mendokumenkan pendaraban antara syarat sebagai ujian DAN dalam FILTER. Variasi ini sengaja mengecualikan sifar sepadan, jadi kekalkan formula asal apabila anda memerlukan setiap kuantiti sepadan. Fungsi FILTER.
Selesaikan masalah hasil dan pastikan baris baharu disertakan
Jika formula tidak menghasilkan empat kuantiti yang dijangka, semak lembaran kerja mengikut urutan ini:
- Periksa ruang output. Julat hasil yang terhalang menyebabkan
#SPILL!. Alihkan kandungan yang diperlukan dari kawasan limpahan yang dimaksudkan sebelum mencuba semula. - Semak lokasi formula. Formula tatasusunan limpahan perlu diletakkan dalam grid lembaran kerja di luar jadual Excel.
- Semak kebergantungan buku kerja. Microsoft menyokong tatasusunan dinamik terpaut antara buku kerja hanya selagi kedua-dua buku kerja terbuka; menyegar semula selepas menutup sumber boleh menghasilkan
#REF!.
Batasan ini didokumenkan dalam Formula tatasusunan dinamik dan tingkah laku limpahan tatasusunan.
Seterusnya, periksa label dan kuantiti sumber. Bandingkan D2 dengan sel aset yang dimaksudkan, dan pastikan kuantiti dimasukkan sebagai nombor. Jumlah yang kelihatan munasabah sahaja tidak mencukupi sebagai pengesahan kerana SUM boleh mengabaikan nilai teks. Fungsi SUM.
Formula sampel merujuk baris sumber 2 hingga 7. Jika anda menambahkan data pada baris 8, edit kedua-dua julat sumber supaya baris itu disertakan. Contohnya, selepas memasukkan Tile A dalam A8 dan kuantiti angka 3 dalam B8, gantikan formula dalam F2 dengan:
=FILTER(B2:B8,A2:A8=D2)
Tatasusunan yang dijangka menjadi 4, 2, 0, 4, 3, dan jumlahnya menjadi 13. Rujukan F2# mengikuti hasil yang dikembangkan, tetapi tidak dapat menjadikan julat sumber asal menyertakan baris yang tertinggal.
Untuk senarai yang kerap bertambah, Microsoft mengesyorkan jadual Excel dengan rujukan berstruktur, yang dilaraskan apabila baris jadual ditambah atau dibuang. Pastikan output limpahan berada di luar jadual itu. Formula tatasusunan dinamik dan tingkah laku limpahan tatasusunan.
Akhiri dengan menyemak hasil individu sebelum bergantung pada jumlah: bagi enam baris sumber asal, memilih Tile A sepatutnya memulangkan empat kuantiti, termasuk kedua-dua nilai empat dan sifar. Setelah hasil itu sepadan, gunakan semula F2# untuk pengiraan dan pilih peraturan apabila tiada padanan yang jelas serta sesuai dengan tujuan lembaran kerja.
Sumber
- Fungsi FILTER — Sokongan Microsoft.
- Berkongsi dokumen dengan versi Office untuk Mac yang lain — Sokongan Microsoft.
- Perihal Office: Versi Office manakah yang saya gunakan? — Sokongan Microsoft.
- Formula tatasusunan dinamik dan tingkah laku limpahan tatasusunan — Sokongan Microsoft.
- Fungsi XLOOKUP — Sokongan Microsoft.
- Garis panduan dan contoh formula tatasusunan — Sokongan Microsoft.
- Fungsi SUM — Sokongan Microsoft.
- Gunakan Excel sebagai kalkulator anda dalam Excel untuk Mac — Sokongan Microsoft.
- Cara membetulkan ralat #CALC! — Sokongan Microsoft.