Untuk memperluas matriks jumlah aset seni menjadi satu baris lembar kerja per item, pertama-tama cantumkan setiap kombinasi aset–format beserta jumlah kumulatifnya. Lalu gunakan SEQUENCE dan XLOOKUP untuk mengulang labelnya sebanyak yang diperlukan. Panduan ini menggunakan label karya seni sintetis.
Tugasnya sederhana: ubah kisi perencanaan yang ringkas menjadi daftar item. Jumlah dua menghasilkan dua baris dengan aset dan format yang sama; nol tidak menghasilkan baris. Contoh berikut menampilkan perhitungan perantara agar Anda dapat memeriksa hasilnya sebelum menyesuaikannya.
Versi Excel untuk Mac mana yang mendukung metode ini?
Gunakan Excel untuk Microsoft 365 untuk Mac, Excel 2024 untuk Mac, atau Excel 2021 untuk Mac. Dokumentasi Microsoft mencantumkan edisi-edisi ini untuk SEQUENCE maupun XLOOKUP, dan panduan fitur Excel 2021 untuk Mac secara eksplisit mendokumentasikan array dinamis. Fungsi SEQUENCE, Fungsi XLOOKUP, Yang baru di Excel 2021 untuk Mac.
Ketersediaan fungsi penting saat menyalin rumus dari tutorial lain. Misalnya, Microsoft mencantumkan VSTACK untuk Microsoft 365 dan Excel 2024 di Mac, tetapi tidak mencantumkan Excel 2021. Metode ini menghindari ketergantungan tersebut. Microsoft juga secara eksplisit menyatakan bahwa XLOOKUP tidak tersedia di Excel 2016 dan Excel 2019, sehingga edisi tersebut tidak dapat menjalankan rumus di bawah tanpa perubahan. Fungsi VSTACK, Fungsi XLOOKUP.
Siapkan matriks jumlah aset seni
Pada lembar kerja kosong, masukkan data sintetis berikut di A1:C4. Kolom pertama berisi label aset, baris judul berisi format, dan sel lainnya berisi jumlah.
| Aset | Kecil | Besar |
|---|---|---|
| Ubin Lengkung | 2 | 0 |
| Ubin Titik | 1 | 0 |
| Ubin Gelombang | 2 | 1 |
Untuk contoh ini, setiap jumlah harus berupa bilangan bulat numerik nol atau lebih. Masukkan nol secara eksplisit jika item tidak diperlukan. Bereskan sel kosong, jumlah berupa teks, nilai negatif, pecahan, dan kesalahan sel sebelum melanjutkan. Rumus ini mengasumsikan jumlah yang valid; rumus tidak memvalidasi atau membulatkannya.
Hasil yang diharapkan berisi enam baris item: dua Ubin Lengkung Kecil, satu Ubin Titik Kecil, dua Ubin Gelombang Kecil, dan satu Ubin Gelombang Besar. Label yang berulang memang disengaja karena setiap baris mewakili satu item. Rumus ini membuat catatan lembar kerja; rumus tidak membuat atau menggandakan berkas karya seni.
Uraikan matriks menjadi daftar bantu yang mudah diperiksa
Biarkan kolom D kosong. Di E1:I1, masukkan judul Posisi, Aset, Format, Jumlah, dan Item terakhir. Masukkan angka satu hingga enam di E2:E7, secara berurutan. Ada enam baris bantu karena tiga aset dikalikan dua format menghasilkan enam kombinasi.
Daftar bantu dibaca melintasi setiap baris sumber sebelum berpindah ke bawah: Ubin Lengkung Kecil, Ubin Lengkung Besar, lalu dua kombinasi Ubin Titik, diikuti dua kombinasi Ubin Gelombang. Menjaga urutan ini tetap konsisten sangat penting.
Ambil label dan jumlah setiap kombinasi
Masukkan rumus ini di F2:
=INDEX($A$2:$A$4,INT((E2-1)/2)+1)
Masukkan rumus ini di G2:
=INDEX($B$1:$C$1,1,MOD(E2-1,2)+1)
Masukkan rumus ini di H2:
=INDEX($B$2:$C$4,INT((E2-1)/2)+1,MOD(E2-1,2)+1)
Salin F2:H2 ke bawah hingga baris tujuh. Ini adalah rumus sel biasa, dengan satu hasil per sel. INDEX mengambil nilai pada baris dan kolom yang ditentukan. Di sini, INT membuat baris sumber bertambah setiap dua posisi, sedangkan MOD membuat kolom sumber bergantian di antara dua format. Fungsi INDEX, Fungsi INT, Fungsi MOD.
Hitung nomor item terakhir untuk setiap kombinasi
Di I2, masukkan rumus berikut dan salin ke bawah hingga I7:
=SUM($H$2:H2)
Referensi awal yang tetap dan referensi akhir yang bergerak menghasilkan total berjalan. SUM menjumlahkan jumlah dari baris bantu pertama hingga baris saat ini. Fungsi SUM.
Daftar bantu yang sudah selesai seharusnya terlihat seperti ini:
| Posisi | Aset | Format | Jumlah | Item terakhir |
|---|---|---|---|---|
| 1 | Ubin Lengkung | Kecil | 2 | 2 |
| 2 | Ubin Lengkung | Besar | 0 | 2 |
| 3 | Ubin Titik | Kecil | 1 | 3 |
| 4 | Ubin Titik | Besar | 0 | 3 |
| 5 | Ubin Gelombang | Kecil | 2 | 5 |
| 6 | Ubin Gelombang | Besar | 1 | 6 |
Anggap Item terakhir sebagai batas. Kombinasi pertama menempati nomor item satu dan dua. Kombinasi ketiga menempati nomor item tiga. Kombinasi kelima menempati nomor empat dan lima, sedangkan kombinasi keenam menempati nomor enam. Jumlah nol tidak menggeser batas, sehingga tidak mendapatkan nomor item.
Hasilkan satu baris keluaran per item
Di K1, masukkan Aset; di L1, masukkan Format. Biarkan sel di bawah kedua judul tersebut kosong. Tempatkan keluaran di sel lembar kerja biasa di luar tabel Excel: Microsoft mendokumentasikan bahwa rumus array yang tumpah tidak didukung di dalam tabel. Rumus array dinamis dan perilaku array yang tumpah.
Masukkan rumus lengkap ini di K2:
=IF(SUM($H$2:$H$7)=0,"",XLOOKUP(SEQUENCE(SUM($H$2:$H$7)),$I$2:$I$7,$F$2:$F$7,,1,1))
Masukkan rumus lengkap ini di L2:
=IF(SUM($H$2:$H$7)=0,"",XLOOKUP(SEQUENCE(SUM($H$2:$H$7)),$I$2:$I$7,$G$2:$G$7,,1,1))
Konfirmasikan setiap rumus dengan Return. Jangan salin kedua rumus ini ke bawah: hasil array-nya otomatis mengisi baris di sekitarnya. Microsoft menyebut ekspansi otomatis ini sebagai perilaku tumpah. Rumus array dinamis dan perilaku array yang tumpah.
SEQUENCE menghasilkan nomor item dari satu hingga jumlah total. Untuk setiap nomor, XLOOKUP mencari batas kumulatif. Dua argumen terakhirnya menentukan kecocokan persis atau nilai berikutnya yang lebih besar, dengan pencarian dari awal ke akhir. Arah pencarian tersebut mempertahankan batas pertama yang cocok saat nilai total berulang akibat jumlah nol. Fungsi SEQUENCE, Fungsi XLOOKUP.
IF terluar mengembalikan teks kosong jika jumlah totalnya nol. Dalam kasus itu, kedua sel jangkar tampak kosong dan tidak ada catatan item yang dapat digunakan. Ini adalah kondisi jumlah total nol yang disengaja, menggunakan IF untuk memilih di antara dua hasil. Fungsi IF.
Untuk matriks yang diberikan, K1:L7 seharusnya berisi:
| Aset | Format |
|---|---|
| Ubin Lengkung | Kecil |
| Ubin Lengkung | Kecil |
| Ubin Titik | Kecil |
| Ubin Gelombang | Kecil |
| Ubin Gelombang | Kecil |
| Ubin Gelombang | Besar |
Kedua kolom keluaran menggunakan nomor item dan rentang batas yang sama. Hanya rentang nilai yang berubah: kolom F menyediakan label aset, sedangkan kolom G menyediakan format. Pastikan masukan pencarian tersebut tetap selaras saat mengedit rumus.
Periksa hasil sebelum memperluas sumber
Lakukan tiga pemeriksaan dengan data contoh:
- Bandingkan total:
=SUM(B2:C4)dan=SUM(H2:H7)harus sama-sama menghasilkan enam. - Bandingkan kombinasi: keluaran harus berisi empat kombinasi nonnol dengan jumlah pengulangan yang disebutkan.
- Ubah
B2dari dua menjadi tiga: hasil yang diharapkan menjadi tujuh baris, dengan tiga entri Ubin Lengkung Kecil. Setelah itu, kembalikan nilainya menjadi dua.
Pemeriksaan pertama mendeteksi jumlah yang terlewat atau terduplikasi saat penguraian. Pemeriksaan kedua mendeteksi label yang tidak selaras, yang tidak dapat diketahui dari total saja. Pemeriksaan ketiga memeriksa apakah perubahan diteruskan melalui daftar bantu ke keluaran.
Tambahkan lebih banyak aset atau format secara terencana
Perubahan jumlah di dalam B2:C4 menggunakan penyiapan yang sudah ada. Menambah dimensi matriks mengharuskan Anda mengubah referensi dan panjang daftar bantu.
Sebagai contoh lengkap, perluas sumber menjadi empat aset dan tiga format di A1:D5. Pindahkan atau pertahankan area bantu di E:I, masukkan posisi satu hingga dua belas di E2:E13, lalu ganti rumus di F2:H2 dengan:
=INDEX($A$2:$A$5,INT((E2-1)/3)+1)
=INDEX($B$1:$D$1,1,MOD(E2-1,3)+1)
=INDEX($B$2:$D$5,INT((E2-1)/3)+1,MOD(E2-1,3)+1)
Salin rumus-rumus tersebut hingga baris tiga belas, dan perluas rumus total berjalan di kolom I hingga baris yang sama. Di kedua rumus keluaran, ganti setiap rentang bantu yang berakhir di baris tujuh agar berakhir di baris tiga belas. Pembagi dan basis sisa kini tiga karena setiap aset memiliki tiga format. Periksa kembali total sumber dan setiap kombinasi setelah melakukan perubahan ini.
Atasi keluaran yang tidak lengkap atau terhalang
Jika Excel menampilkan #SPILL!, periksa area keluaran yang dituju untuk mencari entri yang sudah ada atau sel gabungan, dan pastikan rumus berada di luar tabel Excel. Hasil yang melampaui tepi lembar kerja juga tidak dapat tumpah. Microsoft mendokumentasikan setiap kondisi ini. Cara memperbaiki kesalahan #SPILL!.
Jika jumlah baris keluaran terlalu sedikit, periksa jumlahnya sebelum mengubah logika pencarian. Microsoft menyatakan bahwa SUM mengabaikan teks dalam rentang yang dirujuk. Karena itu, jumlah yang disimpan sebagai teks dapat tidak disertakan dalam total. Perbaiki nilai sumber lalu ulangi pemeriksaan total. Fungsi SUM.
Jika label salah meskipun totalnya benar, bandingkan daftar bantu dengan matriks sel demi sel. Pastikan aset, format, jumlah, dan batas kumulatif setiap baris bantu tetap bersama. Jangan mengatur ulang satu kolom bantu secara terpisah.
Setelah total dan kombinasi sesuai, gunakan dua kolom keluaran sebagai daftar item. Simpan matriks dan perhitungan bantu agar perubahan jumlah di kemudian hari tetap dapat ditelusuri ke baris yang diulang.
Sumber
- Yang baru di Excel 2021 untuk Mac — Dukungan Microsoft.
- Fungsi SEQUENCE — Dukungan Microsoft.
- Fungsi XLOOKUP — Dukungan Microsoft.
- Fungsi VSTACK — Dukungan Microsoft.
- Fungsi INDEX — Dukungan Microsoft.
- Fungsi INT — Dukungan Microsoft.
- Fungsi MOD — Dukungan Microsoft.
- Fungsi SUM — Dukungan Microsoft.
- Fungsi IF — Dukungan Microsoft.
- Rumus array dinamis dan perilaku array yang tumpah — Dukungan Microsoft.
- Cara memperbaiki kesalahan #SPILL! — Dukungan Microsoft.