Untuk menggabungkan ukuran inventaris yang bersesuaian di Excel untuk Mac, tambahkan array numerik yang cocok dengan +. Jika urutan baris aset berbeda, cari setiap aset terlebih dahulu, lalu tambahkan kuantitas yang dikembalikan. Cara ini mempertahankan hasil terpisah untuk setiap ukuran.

Panduan ini menggunakan aset seni sintetis dengan dua ukuran: salinan cetak dan kartu contoh warna. Tugasnya adalah menggabungkan kuantitas dari dua lokasi penyimpanan dengan tetap mengidentifikasi setiap aset dan ukuran. Semua kuantitas hanya sebagai ilustrasi.

Pilih rumus berdasarkan kesesuaian aset

Sebelum menulis rumus, tentukan dasar kesesuaian dua nilai. Posisi yang cocok hanya berlaku jika kedua blok input memiliki urutan aset dan urutan ukuran yang sama. Label aset yang cocok memungkinkan baris sumber muncul dalam urutan berbeda.

Kondisi input Pendekatan yang didukung Hasil
Aset sama, urutan baris sama, kolom ukuran sama Tambahkan rentang numerik berukuran sama Satu hasil untuk setiap aset dan ukuran
Label aset unik, urutan baris berbeda Tambahkan dua hasil XLOOKUP multi-kolom Ukuran suatu aset di sel terpisah
Label aset berulang yang perlu digabungkan Gunakan perhitungan SUMIF terpisah untuk setiap ukuran Satu hasil untuk setiap aset dan ukuran yang diminta

Contoh di bawah ini menurunkan pendekatan tersebut dari perhitungan array, pengembalian beberapa item hasil pencarian, dan penjumlahan bersyarat yang didokumentasikan Microsoft. Pilih metode yang sesuai dengan struktur input sebelum menyalin rumus.

Pastikan edisi Mac Anda

Contoh pencarian berlaku untuk Excel untuk Microsoft 365 untuk Mac, Excel 2024 untuk Mac, dan Excel 2021 untuk Mac. Microsoft secara eksplisit menyatakan bahwa XLOOKUP tidak tersedia di Excel 2016 dan Excel 2019, meskipun edisi tersebut tercantum dalam daftar penerapan umum halaman tersebut. Dokumentasi XLOOKUP Microsoft.

Di Mac, menu aplikasi pada aplikasi Office memiliki dialog About yang menampilkan versi dan jenis lisensi. Di Excel, buka Excel → About Excel. Pastikan edisi produk serta nomor versinya sebelum memilih rumus. Tentang Office: Versi Office apa yang saya gunakan?.

Rumus HSTACK opsional yang dibahas nanti dalam artikel ini memerlukan Excel untuk Microsoft 365 untuk Mac atau Excel 2024 untuk Mac; Microsoft tidak mencantumkan Excel 2021 untuk Mac untuk fungsi tersebut. Perhitungan utama tidak memerlukannya. Dokumentasi HSTACK Microsoft.

Siapkan dua inventaris kecil aset seni

Gunakan lembar kerja kosong dengan rentang sel biasa. Masukkan inventaris lokasi pertama di A1:C4:

Aset Salinan cetak Kartu contoh warna
Tile-A 8 3
Tile-B 5 7
Tile-C 6 2

Masukkan inventaris lokasi kedua di E1:G4, dan biarkan kolom D kosong:

Aset Salinan cetak Kartu contoh warna
Tile-A 4 2
Tile-B 9 1
Tile-C 3 5

Kedua blok numerik memiliki tinggi tiga baris dan lebar dua kolom. Kolom numerik pertama menghitung salinan cetak; kolom kedua menghitung kartu contoh warna. Label berada di luar rentang aritmetika.

Di J1:L4, siapkan area keluaran seperti di bawah ini. Awalnya, masukkan hanya tajuk dan label aset; biarkan sel kuantitas kosong.

Aset Total salinan cetak Total kartu contoh warna
Tile-A
Tile-B
Tile-C

Untuk contoh ini, setiap kuantitas berupa angka dan setiap aset muncul sekali di masing-masing sumber. Catatan yang hilang dan nilai nol yang tercatat memiliki arti berbeda: gunakan nol jika kuantitas diketahui memang nol, dan selesaikan catatan yang tidak ada sebelum menganggapnya nol.

Tambahkan array yang selaras untuk mempertahankan setiap hasil yang bersesuaian

Dengan baris sumber dalam urutan yang ditampilkan, masukkan rumus lembar kerja lengkap ini di K2:

=B2:C4+F2:G4

Tekan Return untuk mengonfirmasi rumus. Di Excel dengan array dinamis, keluaran menempati K2:L4: tiga baris aset dan dua kolom ukuran. Microsoft mendokumentasikan bahwa rumus array dapat mengembalikan beberapa hasil dan hasil dinamis akan tercurah ke sel di sebelahnya. Panduan dan contoh rumus array.

Keluaran yang diharapkan:

Aset Total salinan cetak Total kartu contoh warna
Tile-A 12 5
Tile-B 14 8
Tile-C 9 7

Perhitungan mengikuti posisi: B2+F2 menghasilkan 12, sedangkan C2+G2 menghasilkan 5. Setiap posisi sisanya mengikuti aturan yang sama. Kedua blok sumber menjadi satu blok hasil dengan dimensi yang sama.

Untuk inventaris satu kolom, rumus setaranya adalah =B2:B4+F2:F4. Rumus tersebut mengembalikan tiga kuantitas, satu per baris. Versi dua kolom hanya mempertahankan ukuran kedua di samping yang pertama.

Mengapa SUM di bagian luar menghasilkan bentuk yang keliru

Rumus berikut menghitung total keseluruhan:

=SUM(B2:C4,F2:G4)

=SUM(B2:C4+F2:G4)

Untuk contoh ini, masing-masing mengembalikan 55. Angka itu mencampur ketiga aset dan kedua ukuran. Microsoft mendokumentasikan bahwa SUM menambahkan nilai yang diberikan sebagai angka, referensi, atau rentang; karena itu, membungkus array hasil dengan SUM mereduksinya menjadi satu nilai. Dokumentasi SUM Microsoft.

Gunakan hasil enam sel di atas sebagai pemeriksaan penerimaan. Satu sel berisi 55 menjawab tugas yang berbeda, meskipun aritmetikanya benar.

Tata letak untuk baris selaras yang tidak bergantung pada versi

Jika Anda lebih memilih rumus sel individual, masukkan =B2+F2 di K2. Seret gagang isian ke kanan hingga L2, lalu isi K2:L2 ke bawah sampai baris 4. Referensi relatif akan disesuaikan untuk menghitung setiap pasangan yang bersesuaian. Microsoft mendokumentasikan perilaku gagang isian ini untuk Excel untuk Mac. Gunakan Excel sebagai kalkulator di Excel untuk Mac.

Cara ini menghasilkan enam kuantitas yang sama tanpa hasil tercurah. Metode ini tetap bergantung pada kesamaan urutan baris sumber.

Cocokkan label aset saat urutan baris sumber berbeda

Jika posisi baris tidak dapat diandalkan, hapus hasil sebelumnya dari K2 terlebih dahulu. Pertahankan J2:J4 sebagai daftar aset yang diminta. Masukkan rumus berikut di K2:

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

Setiap pencarian menemukan aset di J2 dan mengembalikan dua kolom kuantitasnya. Penambahan baris yang dikembalikan menghasilkan 12 di K2 dan 5 di L2. Microsoft mendokumentasikan pencocokan tepat sebagai perilaku bawaan serta pengembalian beberapa item dari rentang hasil multi-kolom. Dokumentasi XLOOKUP Microsoft.

Salin rumus dari K2 ke K3:K4, dan biarkan L2:L4 kosong untuk hasil horizontal. Tanda dolar mengunci rentang sumber; aset yang diminta berubah mengikuti setiap baris keluaran.

Sebagai diagnosis, susun ulang catatan lengkap sumber kedua sehingga Tile-C berada di urutan pertama. Pindahkan setiap label bersama kedua kuantitasnya. Keluaran berbasis pencarian seharusnya tetap mempertahankan nilai yang diharapkan, sedangkan penambahan berbasis posisi akan memasangkan aset yang berbeda.

Label yang tidak ditemukan akan menghasilkan #N/A karena rumus tidak menyertakan pengganti opsional untuk kecocokan yang tidak ditemukan. Label berulang akan mengembalikan kecocokan pertama, bukan menggabungkan catatannya. Ini adalah batasan pencarian yang terdokumentasi. Dokumentasi XLOOKUP Microsoft.

Keluaran hanya mencakup aset yang tercantum di J2:J4. Sebelum menggunakan data Anda sendiri, pastikan daftar yang diminta ini mencakup setiap aset dari salah satu lokasi yang ingin Anda laporkan.

Gabungkan catatan aset yang berulang, satu ukuran pada satu waktu

Jika beberapa baris memang termasuk aset yang sama, gunakan penjumlahan bersyarat. Dengan rentang contoh asli, masukkan rumus lengkap ini di K2:

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

Salin ke kanan hingga L2, lalu salin K2:L2 ke bawah sampai baris 4. Berpindah ke kanan mengubah rentang kuantitas menjadi kolom contoh warna sekaligus mempertahankan rentang label. Setiap sel keluaran hanya menjumlahkan aset dan ukurannya sendiri. Microsoft mendokumentasikan penggunaan satu rentang untuk kriteria dan rentang lain untuk kuantitas yang bersesuaian. Dokumentasi SUMIF Microsoft.

Untuk menunjukkan catatan berulang, tambahkan Tile-A, 2, dan 1 di A5:C5. Sebelum menghitung ulang, perluas rentang kriteria dan kuantitas 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 rumus yang diperbarui ke samping dan ke bawah seperti sebelumnya. Keluaran ilustratif Tile-A menjadi 14 salinan cetak dan 6 kartu contoh warna. Rentang tetap yang berakhir di baris 4 akan mengabaikan catatan tambahan tersebut, jadi pemeliharaan rentang merupakan bagian dari metode ini.

Pastikan setiap rentang kriteria memiliki ukuran dan bentuk yang sama dengan rentang kuantitasnya. Perhatikan juga bahwa SUMIF menganggap * dan ? sebagai karakter pengganti; label sintetis di sini tidak mengandung keduanya. Dokumentasi SUMIF Microsoft.

Atasi kesalahan limpahan dan periksa inventaris akhir

Rumus yang tercurah memerlukan ruang untuk seluruh keluarannya. Letakkan rumus di luar tabel Excel, dan edit atau ganti melalui sel kiri atasnya. Microsoft mendokumentasikan kedua batasan tersebut. Rumus array dinamis dan perilaku array yang tercurah.

Jika #SPILL! muncul, periksa apakah ada entri atau sel gabungan di area tujuan. Pindahkan konten yang menghalangi ke lokasi lain atau pilih area keluaran kosong. Microsoft mengidentifikasi tujuan yang terhalang dan sel gabungan sebagai penyebab kesalahan limpahan. Cara memperbaiki kesalahan #SPILL!.

Periksa apakah ada entri teks di antara kuantitas jika penambahan menghasilkan #VALUE!. Microsoft menyebutkan bahwa penambahan langsung dapat gagal pada teks nonnumerik, sedangkan SUM mengabaikan teks. Karena itu, mengganti rumus dengan SUM akan mengubah bentuk hasil sekaligus cara teks ditangani. Dokumentasi SUM Microsoft.

Untuk blok hasil berlabel opsional di Microsoft 365 untuk Mac atau Excel 2024 untuk Mac, gunakan contoh selaras asli di area kosong yang baru:

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

Cara ini menempatkan label aset di samping ukuran yang dihitung. HSTACK menambahkan kolom; + di dalam rumus melakukan penambahan. Cara ini tidak mencocokkan label atau memperbaiki urutan baris sumber yang berbeda. Dokumentasi HSTACK Microsoft.

Akhiri dengan memeriksa tiga hal: keluaran mencakup setiap aset yang dimaksud, setiap ukuran tetap berada di kolomnya sendiri, dan satu baris yang diperiksa secara manual sesuai dengan kuantitas sumber. Untuk input yang selaras, gunakan penambahan rentang. Untuk label unik dalam urutan baris yang berubah-ubah, tambahkan hasil pencarian. Untuk catatan berulang, jumlahkan ukuran setiap aset secara terpisah. Inventaris akhir harus mempertahankan detail yang diperlukan untuk mengidentifikasi apa yang dihitung oleh setiap kuantitas.

Sumber