Untuk menggabungkan ukuran inventori yang sepadan dalam Excel untuk Mac, tambahkan tatasusunan angka yang sepadan dengan +. Jika susunan baris aset berbeza, cari setiap aset dahulu, kemudian tambahkan kuantiti yang dikembalikan. Ini mengekalkan hasil berasingan bagi setiap ukuran.

Panduan ini menggunakan aset seni sintetik dengan dua ukuran: salinan cetakan dan kad swatch. Tugasnya ialah menggabungkan kuantiti daripada dua lokasi penyimpanan sambil memastikan setiap aset dan ukuran dapat dikenal pasti. Semua kuantiti adalah ilustrasi.

Pilih formula berdasarkan padanan aset

Sebelum menulis formula, tentukan perkara yang menjadikan dua nilai sepadan. Kedudukan yang sepadan hanya berfungsi apabila kedua-dua blok input mempunyai susunan aset dan susunan ukuran yang sama. Label aset yang sepadan membolehkan baris sumber berada dalam susunan yang berbeza.

Keadaan input Kaedah yang disokong Hasil
Aset sama, susunan baris sama, lajur ukuran sama Tambahkan julat angka yang sama saiz Satu hasil bagi setiap aset dan ukuran
Label aset unik, susunan baris berbeza Tambahkan dua hasil XLOOKUP berbilang lajur Ukuran satu aset merentasi sel berasingan
Label aset berulang yang perlu digabungkan Gunakan pengiraan SUMIF berasingan bagi setiap ukuran Satu hasil bagi setiap aset dan ukuran yang diminta

Contoh di bawah menerapkan pendekatan ini berdasarkan pengiraan tatasusunan, pemulangan berbilang item melalui carian dan penjumlahan bersyarat yang didokumenkan oleh Microsoft. Pilih kaedah yang sepadan dengan struktur input anda sebelum menyalin formula.

Sahkan edisi Mac anda

Contoh carian ini boleh digunakan dalam Excel untuk Microsoft 365 untuk Mac, Excel 2024 untuk Mac dan Excel 2021 untuk Mac. Microsoft menyatakan secara jelas bahawa XLOOKUP tidak tersedia dalam Excel 2016 dan Excel 2019, walaupun edisi tersebut disenaraikan dalam senarai kebolehgunaan umum halaman itu. Dokumentasi XLOOKUP Microsoft.

Pada Mac, menu aplikasi bagi aplikasi Office mengandungi dialog About, yang memaparkan versi dan jenis lesen. Dalam Excel, semak Excel → About Excel. Kenal pasti edisi produk serta nombor versi sebelum memilih formula. Perihal Office: Versi Office yang manakah saya gunakan?.

Formula pilihan HSTACK yang kemudian dalam artikel ini memerlukan Excel untuk Microsoft 365 untuk Mac atau Excel 2024 untuk Mac; Microsoft tidak menyenaraikan Excel 2021 untuk Mac bagi fungsi itu. Pengiraan utama tidak memerlukannya. Dokumentasi HSTACK Microsoft.

Sediakan dua inventori aset seni yang ringkas

Gunakan lembaran kerja kosong dengan julat sel biasa. Masukkan inventori lokasi pertama dalam A1:C4:

Aset Salinan cetakan Kad swatch
Tile-A 8 3
Tile-B 5 7
Tile-C 6 2

Masukkan inventori lokasi kedua dalam E1:G4, dengan membiarkan lajur D kosong:

Aset Salinan cetakan Kad swatch
Tile-A 4 2
Tile-B 9 1
Tile-C 3 5

Kedua-dua blok angka mempunyai tiga baris dan dua lajur. Lajur angka pertama mengira salinan cetakan; lajur kedua mengira kad swatch. Label berada di luar julat pengiraan.

Dalam J1:L4, sediakan ruang output di bawah. Masukkan pengepala dan label aset sahaja pada mulanya; biarkan sel kuantiti kosong.

Aset Jumlah salinan cetakan Jumlah kad swatch
Tile-A
Tile-B
Tile-C

Bagi contoh ini, setiap kuantiti ialah nombor dan setiap aset muncul sekali dalam setiap sumber. Rekod yang tiada dan nilai sifar yang direkodkan mempunyai maksud berbeza: gunakan sifar apabila kuantitinya diketahui sifar, dan selesaikan rekod yang tiada sebelum menganggap nilainya sifar.

Tambahkan tatasusunan sejajar untuk mengekalkan setiap hasil yang sepadan

Dengan baris sumber dalam susunan yang ditunjukkan, masukkan formula lembaran kerja lengkap ini dalam K2:

=B2:C4+F2:G4

Sahkan formula dengan Return. Dalam Excel yang menyokong tatasusunan dinamik, output memenuhi K2:L4: tiga baris aset dan dua lajur ukuran. Microsoft mendokumenkan bahawa formula tatasusunan boleh memulangkan berbilang hasil dan hasil dinamik melimpah ke sel bersebelahan. Panduan dan contoh formula tatasusunan.

Output yang dijangka ialah:

Aset Jumlah salinan cetakan Jumlah kad swatch
Tile-A 12 5
Tile-B 14 8
Tile-C 9 7

Pengiraan ini mengikut kedudukan: B2+F2 menghasilkan 12, manakala C2+G2 menghasilkan 5. Setiap kedudukan yang selebihnya mengikut peraturan yang sama. Kedua-dua blok sumber menjadi satu blok hasil yang mempunyai dimensi yang sama.

Untuk inventori satu lajur, formula setara ialah =B2:B4+F2:F4. Formula itu memulangkan tiga kuantiti, satu bagi setiap baris. Versi dua lajur hanya mengekalkan ukuran kedua di samping ukuran pertama.

Sebab SUM luaran menghasilkan bentuk yang salah

Formula ini mengira jumlah keseluruhan:

=SUM(B2:C4,F2:G4)

=SUM(B2:C4+F2:G4)

Bagi sampel ini, setiap formula memulangkan 55. Angka itu mencampurkan ketiga-tiga aset dan kedua-dua ukuran. Microsoft mendokumenkan bahawa SUM menambahkan nilai yang diberikan sebagai nombor, rujukan atau julat; oleh itu, membungkus tatasusunan hasil dalam SUM akan mengecilkannya menjadi satu nilai. Dokumentasi SUM Microsoft.

Gunakan hasil enam sel di atas sebagai semakan penerimaan. Satu sel yang mengandungi 55 menjawab tugas yang berbeza, walaupun pengiraannya betul.

Susun atur bebas versi untuk baris sejajar

Jika anda lebih suka formula sel individu, masukkan =B2+F2 dalam K2. Seret pemegang isian ke kanan sehingga L2, kemudian isi K2:L2 ke bawah hingga baris 4. Rujukan relatif akan dilaraskan untuk mengira setiap pasangan yang sepadan. Microsoft mendokumenkan cara pemegang isian ini berfungsi dalam Excel untuk Mac. Gunakan Excel sebagai kalkulator dalam Excel untuk Mac.

Kaedah ini menghasilkan enam kuantiti yang sama tanpa hasil yang melimpah. Namun, kaedah ini masih bergantung pada susunan baris sumber yang sepadan.

Padankan label aset apabila susunan baris sumber berbeza

Apabila kedudukan baris tidak boleh dipercayai, kosongkan hasil sebelumnya daripada K2 terlebih dahulu. Kekalkan senarai aset yang diminta dalam J2:J4. Masukkan formula berikut dalam K2:

=XLOOKUP($J2,$A$2:$A$4,$B$2:$C$4)+XLOOKUP($J2,$E$2:$E$4,$F$2:$G$4)

Setiap carian mencari aset dalam J2 dan memulangkan dua lajur kuantitinya. Penambahan baris yang dipulangkan menghasilkan 12 dalam K2 dan 5 dalam L2. Microsoft mendokumenkan bahawa padanan tepat digunakan secara lalai dan berbilang item boleh dipulangkan daripada julat pemulangan berbilang lajur. Dokumentasi XLOOKUP Microsoft.

Salin formula daripada K2 ke K3:K4, dan biarkan L2:L4 kosong untuk hasil mendatar. Tanda dolar mengekalkan julat sumber; aset yang diminta berubah mengikut setiap baris output.

Sebagai diagnostik, susun semula rekod lengkap sumber kedua supaya Tile-C berada di tempat pertama. Pindahkan setiap label bersama kedua-dua kuantitinya. Output berasaskan carian sepatutnya mengekalkan nilai yang dijangka, manakala penambahan berasaskan kedudukan kini akan memasangkan aset yang berbeza.

Label yang tiada memulangkan #N/A kerana formula tidak menyertakan nilai gantian pilihan untuk padanan tiada. Label yang berulang memulangkan padanan pertama dan bukannya menggabungkan rekodnya. Ini ialah had carian yang didokumenkan. Dokumentasi XLOOKUP Microsoft.

Output hanya merangkumi aset yang disenaraikan dalam J2:J4. Sebelum menggunakan data anda sendiri, pastikan senarai yang diminta ini merangkumi setiap aset yang ingin anda laporkan daripada mana-mana lokasi.

Gabungkan rekod aset berulang mengikut ukuran

Jika beberapa baris memang tergolong dalam aset yang sama, gunakan penjumlahan bersyarat. Dengan julat sampel asal, masukkan formula lengkap ini dalam K2:

=SUMIF($A$2:$A$4,$J2,B$2:B$4)+SUMIF($E$2:$E$4,$J2,F$2:F$4)

Salin formula itu ke kanan ke dalam L2, kemudian salin K2:L2 ke bawah hingga baris 4. Mengalih ke kanan menukar julat kuantiti kepada lajur swatch sambil mengekalkan julat label. Setiap sel output hanya menjumlahkan aset dan ukurannya sendiri. Microsoft mendokumenkan penggunaan satu julat untuk kriteria dan satu lagi untuk kuantiti yang sepadan. Dokumentasi SUMIF Microsoft.

Untuk menunjukkan rekod berulang, tambahkan Tile-A, 2 dan 1 dalam A5:C5. Sebelum mengira semula, panjangkan julat kriteria dan kuantiti sumber pertama hingga baris 5:

=SUMIF($A$2:$A$5,$J2,B$2:B$5)+SUMIF($E$2:$E$4,$J2,F$2:F$4)

Salin formula yang telah disemak itu ke seluruh lajur dan baris seperti sebelum ini. Output ilustrasi Tile-A menjadi 14 salinan cetakan dan 6 kad swatch. Julat tetap yang berakhir pada baris 4 akan mengecualikan rekod yang ditambahkan, jadi penyelenggaraan julat merupakan sebahagian daripada kaedah ini.

Pastikan setiap julat kriteria mempunyai saiz dan bentuk yang sama dengan julat kuantitinya. Perhatikan juga bahawa SUMIF menganggap * dan ? sebagai kad bebas; label sintetik di sini tidak mengandungi kedua-duanya. Dokumentasi SUMIF Microsoft.

Selesaikan ralat limpahan dan semak inventori akhir

Formula limpahan memerlukan ruang yang cukup untuk keseluruhan outputnya. Letakkannya di luar jadual Excel dan edit atau gantikannya melalui sel kiri atas. Microsoft mendokumenkan kedua-dua sekatan ini. Formula tatasusunan dinamik dan tingkah laku tatasusunan limpahan.

Jika #SPILL! muncul, semak sama ada terdapat entri sedia ada atau sel bercantum di destinasi yang dimaksudkan. Alihkan kandungan yang menghalang ke lokasi lain atau pilih ruang output yang kosong. Microsoft mengenal pasti destinasi yang terhalang dan sel bercantum sebagai punca ralat limpahan. Cara membetulkan ralat #SPILL!.

Semak kuantiti untuk entri teks jika penambahan memulangkan #VALUE!. Microsoft menyatakan bahawa penambahan langsung boleh gagal apabila berdepan teks bukan angka, manakala SUM mengabaikan teks. Oleh itu, menggantikan formula dengan SUM akan mengubah bentuk hasil dan cara teks dikendalikan. Dokumentasi SUM Microsoft.

Untuk blok hasil berlabel pilihan dalam Microsoft 365 untuk Mac atau Excel 2024 untuk Mac, gunakan sampel sejajar asal di ruang kosong yang baharu:

=HSTACK(A2:A4,B2:C4+F2:G4)

Kaedah ini meletakkan label aset di sebelah ukuran yang dikira. HSTACK menambahkan lajur; + di dalam formula menjalankan penambahan. Formula ini tidak memadankan label atau membetulkan susunan baris sumber yang berbeza. Dokumentasi HSTACK Microsoft.

Akhiri dengan menyemak tiga perkara: output merangkumi setiap aset yang dimaksudkan, setiap ukuran kekal dalam lajurnya sendiri, dan satu baris yang disemak secara manual sepadan dengan kuantiti sumber. Untuk input sejajar, gunakan penambahan julat. Untuk label unik dengan susunan baris yang berubah-ubah, tambahkan hasil carian. Untuk rekod berulang, jumlahkan ukuran setiap aset secara berasingan. Inventori akhir harus mengekalkan butiran yang diperlukan untuk mengenal pasti perkara yang dikira oleh setiap kuantiti.

Sumber