Dalam Microsoft 365 untuk Mac atau Excel 2024 untuk Mac, gabungkan CHOOSECOLS, TOCOL, HSTACK dan FILTER untuk menukar label aset seni dan kuantiti yang berulang menjadi baris inventori. Formula contoh di bawah mengekalkan padanan pasangan, kuantiti sifar dan entri yang tidak lengkap.

Panduan ini ditujukan kepada pengguna desktop Mac yang lembaran kerjanya mengulang lajur atribut diikuti lajur kuantitinya. Di sini, setiap atribut ialah label aset seni, seperti Circle Tile. Tujuannya adalah untuk menghasilkan inventori dua lajur, kemudian mencari kuantiti tanpa memisahkannya daripada label.

Versi Excel untuk Mac yang manakah menyokong formula ini?

Microsoft menyenaraikan Excel untuk Microsoft 365 untuk Mac dan Excel 2024 untuk Mac secara jelas bagi ketiga-tiga fungsi penyusunan semula: CHOOSECOLS, TOCOL dan HSTACK. Gunakan salah satu edisi tersebut untuk formula utama.

Excel 2021 untuk Mac menyertakan tatasusunan dinamik, FILTER, LET dan XLOOKUP, tetapi itu tidak membuktikan sokongan untuk fungsi penyusunan semula yang lebih baharu. Dokumentasi ciri Excel 2021 untuk Mac Microsoft menerangkan keupayaan tatasusunan dan carian yang tersedia; halaman ketiga-tiga fungsi di atas tidak menyenaraikan Excel 2021.

Semak edisi dan versi anda dalam dialog About aplikasi. Panduan versi Office Microsoft menerangkan bahawa dialog pada Mac memaparkan versi dan jenis lesen. Jika edisi anda tidak mempunyai fungsi penyusunan semula itu, gunakan Microsoft 365 untuk Mac atau Excel 2024 untuk Mac untuk kaedah automatik, atau gunakan kaedah penyusunan manual yang diterangkan di bawah.

Sediakan contoh sintetik yang lengkap

Pada lembaran kerja kosong, masukkan data ini dalam A1:F4. Biarkan dua sel kosong di tengah baris 3 benar-benar kosong. Semua label dan kuantiti hanyalah ilustrasi.

Atribut 1 Kuantiti 1 Atribut 2 Kuantiti 2 Atribut 3 Kuantiti 3
Circle Tile 4 Line Texture 2 Dot Pattern 0
Arc Motif 3 Grid Texture 5
Circle Tile 7 Wave Pattern 6 Square Tile 1

Setiap pasangan bersebelahan mewakili satu entri inventori. Output yang dimaksudkan dibaca merentasi setiap baris sumber sebelum diteruskan ke bawah. Circle Tile muncul dua kali kerana ini ialah entri yang berasingan; menggabungkan label berulang ialah tugas tambahan.

Kaedah ini mengandaikan setiap label diikuti terus oleh kuantitinya dan semua lajur yang dipilih tergolong dalam corak berulang itu. Jika helaian anda turut mengandungi nama koleksi atau medan lain yang perlu disertakan pada setiap entri, output dua lajur ini tidak mengekalkannya. Tentukan medan yang diperlukan oleh inventori sebelum menggunakan formula.

Tukarkan pasangan berulang menjadi baris inventori

Masukkan Asset dalam H1 dan Quantity dalam I1. Pastikan kawasan output di bawah pengepala itu kosong. Tampal formula lengkap ini ke dalam H2, kemudian tekan Return. Formula tatasusunan dinamik dimasukkan sekali dan berkembang ke sel bersebelahan, seperti yang diterangkan dalam panduan formula tatasusunan Microsoft.

=LET(
    source,A2:F4,
    clean,IF(source="","",source),
    labels,TOCOL(CHOOSECOLS(clean,1,3,5),0,FALSE),
    quantities,TOCOL(CHOOSECOLS(clean,2,4,6),0,FALSE),
    keep,((labels<>"")+(quantities<>""))>0,
    FILTER(HSTACK(labels,quantities),keep,"No inventory entries")
)

Hasil yang dijangka memenuhi H2:I9:

Aset Kuantiti
Circle Tile 4
Line Texture 2
Dot Pattern 0
Arc Motif 3
Grid Texture 5
Circle Tile 7
Wave Pattern 6
Square Tile 1

Susunan pasangan, lapan entri output, pengekalan sifar dan jumlah kuantiti 28 dalam contoh ini disemak menggunakan pengiraan Python kecil yang deterministik. Formula Excel disemak berdasarkan sintaks fungsi yang didokumenkan oleh Microsoft; formula itu tidak dijalankan dalam Excel.

Cara formula mengekalkan padanan pasangan

LET memberikan nama kepada hasil perantaraan supaya formula boleh menggunakannya semula. Nama source, clean, labels, quantities dan keep wujud dalam formula ini; anda tidak perlu mencipta julat bernama. Microsoft mendokumenkan tingkah laku ini dalam rujukan LET.

CHOOSECOLS memilih lajur label 1, 3 dan 5 serta lajur kuantiti 2, 4 dan 6. Nombor ini ialah kedudukan dalam julat sumber yang dipilih. Dokumentasi CHOOSECOLS Microsoft mengesahkan bahawa fungsi itu memulangkan lajur yang ditentukan daripada tatasusunan.

Kedua-dua panggilan TOCOL menggunakan 0 untuk mengekalkan setiap kedudukan dan FALSE untuk mengimbas merentasi baris. Oleh sebab kedua-duanya melintasi kedudukan yang sepadan dalam susunan yang sama, setiap label kekal sepadan dengan kuantitinya. Microsoft mendokumenkan kedua-dua argumen dalam rujukan TOCOL.

HSTACK meletakkan dua lajur yang terhasil bersebelahan. Kedua-duanya mengandungi sembilan kedudukan sebelum penapisan, jadi panjangnya sepadan. Microsoft menerangkan bahawa HSTACK menambahkan tatasusunan secara mendatar dan mengisi tatasusunan yang lebih pendek dengan ralat jika bilangan barisnya berbeza.

Akhir sekali, keep memilih kedudukan yang mengandungi label, kuantiti atau kedua-duanya. FILTER membuang pasangan yang benar-benar kosong sahaja. Argumen terakhirnya memberikan mesej apabila tiada entri yang tinggal, mengikut sintaks FILTER yang didokumenkan oleh Microsoft.

Kekalkan sel kosong dan sifar tanpa menyembunyikan entri yang tidak lengkap

Kuantiti kosong dan sifar berangka membawa maklumat yang berbeza. Sifar ialah bilangan yang dimasukkan; sel kosong masih perlu ditafsirkan. Contoh TOCOL Microsoft menunjukkan bahawa sel kosong yang tidak dikendalikan boleh menghasilkan sifar. Oleh itu, langkah clean menggunakan IF untuk memulangkan teks kosong bagi sel sumber yang kosong sebelum penyusunan semula. Microsoft mendokumenkan nilai pulangan bersyarat dalam rujukan IF.

Formula ini sengaja mengekalkan pasangan yang tidak lengkap supaya kekal kelihatan. Gunakan semakan berikut sebelum bergantung pada hasilnya:

Pasangan sumber Tingkah laku output Tindakan
Label dan kuantiti berangka Mengekalkan kedua-duanya Semak sama ada kuantiti itu memang dimaksudkan
Label dan sifar Mengekalkan sifar Kekalkan jika sifar ialah bilangan yang disengajakan
Kedua-dua sel kosong Membuang pasangan Tiada pembetulan diperlukan
Label dengan kuantiti kosong Mengekalkan label dan kuantiti kosong Lengkapkan kuantiti
Label kosong dengan kuantiti Mengekalkan label kosong dan kuantiti Lengkapkan label

Jangan buang sel kosong secara berasingan daripada tatasusunan label dan kuantiti. Jika label tiada pada satu kedudukan dan kuantiti tiada pada kedudukan lain, membuang sel kosong secara berasingan boleh mengalihkan tatasusunan lalu menghasilkan padanan pasangan yang salah. Mengekalkan kedudukan sehingga selepas penggabungan ialah pilihan reka bentuk utama dalam formula ini.

Sel yang kelihatan kosong tetapi mengandungi ruang tidak dianggap kosong oleh formula ini. Betulkan entri sedemikian dalam sumber. Begitu juga, formula mengekalkan kuantiti yang dimasukkan sebagai teks dan tidak menukarkannya secara automatik. Semak nilai tersebut sebelum mengira jumlah.

Cari satu entri atau pulangkan semua entri yang sepadan

Masukkan Circle Tile dalam K2. Untuk mendapatkan kuantiti pertamanya, masukkan formula ini dalam L2:

=XLOOKUP(K2,CHOOSECOLS(H2#,1),CHOOSECOLS(H2#,2),"No matching asset",0)

H2# merujuk kepada keseluruhan hasil limpahan yang bermula pada H2, jadi carian itu mengikuti perubahan saiznya. Microsoft menerangkan notasi ini dalam dokumentasi operator julat limpahan.

Hasilnya ialah 4. Mod padanan eksplisit 0 meminta padanan tepat, dan carian lalai memulangkan entri padanan pertama. Rujukan XLOOKUP Microsoft mendokumenkan kedua-dua tingkah laku ini. Rujukan itu juga mengesahkan bahawa XLOOKUP tidak tersedia dalam Excel 2016 dan Excel 2019.

Jika label berulang membawa maksud tertentu, carian padanan pertama tidak mencukupi. Untuk memulangkan setiap entri Circle Tile, letakkan formula alternatif lengkap ini dalam N2 dan pastikan ruang dalam lajur N:O mencukupi:

=FILTER(H2#,CHOOSECOLS(H2#,1)=K2,"No matching asset")

Formula ini memulangkan dua baris: Circle Tile dengan 4 dan Circle Tile dengan 7. FILTER memulangkan baris yang memenuhi syarat yang diberikan, seperti yang didokumenkan dalam rujukan FILTER Microsoft. Pilih formula padanan pertama apabila satu entri yang sepadan sudah menjawab soalan anda; pilih hasil yang ditapis apabila anda perlu menyemak setiap kemunculan.

Sahkan hasil dan sesuaikan julat sumber

Bagi contoh ini, sahkan bahawa terdapat lapan baris output dan Dot Pattern masih mempunyai kuantiti sifar. Kemudian bandingkan jumlah kuantiti sumber dengan jumlah kuantiti yang telah disusun semula. Masukkan formula berikut dalam dua sel kosong:

=SUM(B2:B4,D2:D4,F2:F4)

=SUM(CHOOSECOLS(H2#,2))

Kedua-duanya sepatutnya memulangkan 28. Dokumentasi SUM Microsoft menyokong penambahan berbilang julat dan menerangkan bahawa nilai teks diabaikan. Oleh itu, jumlah yang sepadan tidak membuktikan bahawa setiap kuantiti ialah angka dan tidak dapat mengesan kuantiti yang tertukar antara label. Semak pasangan label–kuantiti juga.

Untuk lebih banyak baris sumber, tukar A2:F4 dalam formula utama kepada segi empat tepat sebenar, seperti A2:F100. Baris di luar rujukan itu tidak disertakan. Untuk empat pasangan berulang, tukar sumber kepada lapan lajur, seperti A2:H100, dan kemas kini kedudukan label kepada 1,3,5,7 serta kedudukan kuantiti kepada 2,4,6,8.

Apabila mengembangkan sumber kepada lapan lajur, alihkan output ke kawasan yang jelas, seperti J2, dan kemas kini rujukan limpahan seterusnya kepada J2#. Pastikan sumber dan output berasingan. Kedudukan lajur yang tidak sah menghasilkan ralat, seperti yang didokumenkan dalam ralat CHOOSECOLS.

Atasi output yang terhalang atau gunakan kaedah penyusunan manual

Jika formula utama memulangkan #SPILL!, pilih sel formula dan periksa kawasan output yang dimaksudkan. Alihkan entri sedia ada dari kawasan itu dan semak sama ada terdapat sel bercantum. Letakkan formula di luar mana-mana Jadual Excel: Microsoft mendokumenkan bahawa formula tatasusunan limpahan tidak boleh melimpah di dalam jadual atau ke dalam sel bercantum dalam panduan penyelesaian masalah #SPILL!.

Jika sumber mengandungi ralat, betulkan entri sumber itu sebelum menggunakan inventori. Formula ini tidak menyembunyikan ralat; Microsoft menyatakan bahawa ralat dalam tatasusunan kemasukan FILTER menyebabkan fungsi itu memulangkan ralat. Menyembunyikan ralat pada keseluruhan transformasi boleh menyamarkan entri yang perlu dibetulkan.

Untuk lembaran kerja kecil dalam Excel 2021 untuk Mac, anda boleh menghasilkan struktur dua lajur yang sama secara manual: salin A2:B4 ke H2:I4, salin C2:D4 ke H5:I7 dan salin E2:F4 ke H8:I10. Buang hanya baris yang kedua-dua selnya kosong. Kaedah ini menyusun entri mengikut blok pasangan dan bukannya mengikut susunan baris sumber seperti formula utama, serta memerlukan kerja berulang selepas sumber berubah.

Selepas persediaan manual itu, Excel 2021 untuk Mac boleh menggunakan keupayaan XLOOKUP yang didokumenkan dengan julat tetap:

=XLOOKUP(K2,H2:H10,I2:I10,"No matching asset",0)

Inventori akhir yang berguna mengekalkan setiap pasangan yang bermakna, menonjolkan entri yang tidak lengkap dan menggunakan carian yang sesuai untuk label berulang. Sahkan ciri-ciri itu dahulu dengan contoh, kemudian luaskan julat sumber agar sepadan dengan lembaran kerja anda.

Sumber