Di Microsoft 365 untuk Mac atau Excel 2024 untuk Mac, gabungkan CHOOSECOLS, TOCOL, HSTACK, dan FILTER untuk mengubah label aset seni dan kuantitas yang berulang menjadi baris inventaris. Rumus contoh di bawah ini mempertahankan keselarasan pasangan, kuantitas nol, dan entri yang belum lengkap.

Panduan ini ditujukan bagi pengguna desktop Mac yang lembar kerjanya mengulang kolom atribut yang diikuti kolom kuantitasnya. Dalam contoh ini, setiap atribut adalah label aset seni, seperti Ubin Lingkaran. Tugasnya adalah membuat inventaris dua kolom, lalu mencari kuantitas tanpa memisahkannya dari labelnya.

Versi Excel untuk Mac mana yang mendukung rumus ini?

Microsoft secara eksplisit mencantumkan Excel untuk Microsoft 365 untuk Mac dan Excel 2024 untuk Mac untuk ketiga fungsi pengubahan bentuk: CHOOSECOLS, TOCOL, dan HSTACK. Gunakan salah satu edisi tersebut untuk rumus utama.

Excel 2021 untuk Mac menyertakan array dinamis, FILTER, LET, dan XLOOKUP, tetapi itu tidak membuktikan dukungan untuk fungsi pengubahan bentuk yang lebih baru. Dokumentasi fitur Excel 2021 untuk Mac dari Microsoft menjelaskan kemampuan array dan pencarian yang tersedia; tiga halaman fungsi di atas tidak mencantumkan Excel 2021.

Periksa edisi dan versi Anda di dialog About aplikasi. Panduan versi Office dari Microsoft menjelaskan bahwa dialog Mac menampilkan versi dan jenis lisensi. Jika edisi Anda tidak memiliki fungsi pengubahan bentuk tersebut, gunakan Microsoft 365 untuk Mac atau Excel 2024 untuk Mac untuk metode otomatis, atau gunakan cara penyusunan manual yang dijelaskan di bawah.

Siapkan contoh sintetis yang lengkap

Di lembar kerja kosong, masukkan data ini di A1:F4. Biarkan dua sel kosong di tengah baris 3 benar-benar kosong. Semua label dan kuantitas hanya ilustrasi.

Atribut 1 Kuantitas 1 Atribut 2 Kuantitas 2 Atribut 3 Kuantitas 3
Ubin Lingkaran 4 Tekstur Garis 2 Pola Titik 0
Motif Lengkung 3 Tekstur Kisi 5
Ubin Lingkaran 7 Pola Gelombang 6 Ubin Persegi 1

Setiap pasangan yang berdampingan mewakili satu entri inventaris. Hasil yang diinginkan dibaca melintasi setiap baris sumber sebelum berlanjut ke bawah. Ubin Lingkaran muncul dua kali karena ini adalah entri terpisah; menggabungkan label yang berulang merupakan tugas tambahan.

Metode ini mengasumsikan setiap label langsung diikuti kuantitasnya dan semua kolom yang dipilih mengikuti pola berulang tersebut. Jika lembar Anda juga berisi nama koleksi atau bidang lain yang harus menyertai setiap entri, keluaran dua kolom ini tidak menyimpannya. Tentukan bidang yang dibutuhkan inventaris sebelum menerapkan rumus.

Ubah pasangan berulang menjadi baris inventaris

Masukkan Aset di H1 dan Kuantitas di I1. Kosongkan area keluaran di bawah judul tersebut. Tempelkan rumus lengkap ini ke H2, lalu tekan Return. Rumus array dinamis dimasukkan sekali dan meluas ke sel-sel di sebelahnya, seperti dijelaskan dalam panduan rumus array dari 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 diharapkan mengisi H2:I9:

Aset Kuantitas
Ubin Lingkaran 4
Tekstur Garis 2
Pola Titik 0
Motif Lengkung 3
Tekstur Kisi 5
Ubin Lingkaran 7
Pola Gelombang 6
Ubin Persegi 1

Urutan pasangan pada contoh, delapan entri keluaran, dipertahankannya nilai nol, dan jumlah kuantitas 28 diperiksa dengan perhitungan Python deterministik sederhana. Rumus Excel dicocokkan dengan sintaks fungsi yang didokumentasikan Microsoft; rumus tersebut tidak dijalankan di Excel.

Cara rumus mempertahankan keselarasan pasangan

LET memberi nama pada hasil antara agar rumus dapat menggunakannya kembali. Nama source, clean, labels, quantities, dan keep hanya ada di dalam rumus ini; Anda tidak perlu membuat rentang bernama. Microsoft mendokumentasikan perilaku ini dalam referensi LET.

CHOOSECOLS memilih kolom label 1, 3, dan 5 serta kolom kuantitas 2, 4, dan 6. Angka-angka ini adalah posisi di dalam rentang sumber yang dipilih. Dokumentasi CHOOSECOLS dari Microsoft menegaskan bahwa fungsi ini mengembalikan kolom tertentu dari sebuah array.

Kedua pemanggilan TOCOL menggunakan 0 untuk mempertahankan setiap posisi dan FALSE untuk memindai melintasi baris. Karena keduanya menelusuri posisi yang sesuai dengan urutan yang sama, setiap label tetap selaras dengan kuantitasnya. Microsoft mendokumentasikan kedua argumen dalam referensi TOCOL.

HSTACK menempatkan dua kolom hasil berdampingan. Keduanya berisi sembilan posisi sebelum penyaringan, sehingga panjangnya sama. Microsoft menjelaskan bahwa HSTACK menambahkan array secara horizontal dan mengisi array yang lebih pendek dengan kesalahan jika jumlah barisnya berbeda.

Terakhir, keep memilih posisi yang berisi label, kuantitas, atau keduanya. FILTER hanya menghapus pasangan yang benar-benar kosong. Argumen terakhirnya menyediakan pesan jika tidak ada entri tersisa, mengikuti sintaks FILTER yang didokumentasikan Microsoft.

Pertahankan sel kosong dan nol tanpa menyembunyikan entri yang belum lengkap

Kuantitas kosong dan nilai nol memiliki makna berbeda. Nol adalah jumlah yang dimasukkan; sel kosong masih perlu ditafsirkan. Contoh TOCOL dari Microsoft menunjukkan bahwa sel kosong yang tidak ditangani dapat menghasilkan nol. Karena itu, langkah clean menggunakan IF untuk mengembalikan teks kosong bagi sel sumber yang kosong sebelum mengubah bentuk data. Microsoft mendokumentasikan nilai hasil bersyarat dalam referensi IF.

Rumus ini sengaja mempertahankan pasangan yang belum lengkap agar tetap terlihat. Lakukan pemeriksaan berikut sebelum mengandalkan hasilnya:

Pasangan sumber Perilaku keluaran Tindakan
Label dan kuantitas angka Mempertahankan keduanya Periksa apakah kuantitasnya memang dimaksudkan
Label dan nol Mempertahankan nol Pertahankan jika nol merupakan jumlah yang disengaja
Kedua sel kosong Menghapus pasangan Tidak perlu diperbaiki
Label dengan kuantitas kosong Mempertahankan label dan kuantitas kosong Lengkapi kuantitasnya
Label kosong dengan kuantitas Mempertahankan label kosong dan kuantitas Lengkapi labelnya

Jangan menghapus sel kosong secara terpisah dari array label dan kuantitas. Jika label hilang di satu posisi dan kuantitas hilang di posisi lain, menghapus sel kosong secara terpisah dapat menggeser array dan menghasilkan pasangan yang keliru. Mempertahankan posisi hingga setelah penggabungan adalah pilihan desain utama dalam rumus ini.

Sel yang tampak kosong tetapi berisi spasi tidak dianggap kosong oleh rumus ini. Bereskan entri seperti itu di sumber. Demikian pula, rumus ini mempertahankan kuantitas yang dimasukkan sebagai teks dan tidak otomatis mengonversinya. Periksa nilai tersebut sebelum menghitung total.

Cari satu entri atau tampilkan semua entri yang cocok

Masukkan Ubin Lingkaran di K2. Untuk mengambil kuantitas pertamanya, masukkan rumus ini di L2:

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

H2# merujuk ke seluruh hasil luapan yang dimulai di H2, sehingga pencarian mengikuti perubahan ukurannya. Microsoft menjelaskan notasi ini dalam dokumentasi operator rentang luapan.

Hasilnya adalah 4. Mode kecocokan eksplisit 0 meminta kecocokan tepat, dan pencarian bawaan mengembalikan entri cocok pertama. Referensi XLOOKUP dari Microsoft mendokumentasikan kedua perilaku tersebut. Referensi itu juga menegaskan bahwa XLOOKUP tidak tersedia di Excel 2016 dan Excel 2019.

Jika label berulang memiliki arti, pencarian kecocokan pertama tidak memadai. Untuk menampilkan setiap entri Ubin Lingkaran, letakkan rumus alternatif lengkap ini di N2 dan sisakan ruang di kolom N:O:

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

Rumus ini mengembalikan dua baris: Ubin Lingkaran dengan 4 dan Ubin Lingkaran dengan 7. FILTER mengembalikan baris yang memenuhi kondisi yang diberikan, seperti didokumentasikan dalam referensi FILTER dari Microsoft. Pilih rumus kecocokan pertama jika satu entri yang cocok menjawab pertanyaan Anda; pilih hasil yang difilter jika Anda perlu memeriksa setiap kemunculan.

Verifikasi hasil dan sesuaikan rentang sumber

Untuk contoh ini, pastikan ada delapan baris keluaran dan periksa bahwa Pola Titik tetap memiliki kuantitas nol. Lalu bandingkan jumlah kuantitas sumber dengan jumlah kuantitas setelah pengubahan bentuk. Masukkan rumus berikut ke dua sel kosong:

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

=SUM(CHOOSECOLS(H2#,2))

Keduanya seharusnya menghasilkan 28. Dokumentasi SUM dari Microsoft mendukung penjumlahan beberapa rentang dan menjelaskan bahwa nilai teks diabaikan. Karena itu, jumlah yang cocok tidak membuktikan bahwa setiap kuantitas berupa angka dan tidak dapat mendeteksi kuantitas yang tertukar antarlabel. Periksa juga setiap pasangan label dan kuantitas.

Untuk menambah baris sumber, ubah A2:F4 di rumus utama menjadi rentang sebenarnya, misalnya A2:F100. Baris di luar referensi tersebut tidak disertakan. Untuk empat pasangan berulang, ubah sumber menjadi delapan kolom, misalnya A2:H100, dan perbarui posisi label menjadi 1,3,5,7 serta posisi kuantitas menjadi 2,4,6,8.

Saat memperluas sumber menjadi delapan kolom, pindahkan keluaran ke area kosong, seperti J2, dan perbarui referensi luapan berikutnya menjadi J2#. Pisahkan sumber dan keluaran. Posisi kolom yang tidak valid akan menghasilkan kesalahan, sebagaimana didokumentasikan dalam kesalahan CHOOSECOLS.

Atasi keluaran yang terhalang atau gunakan cara penyusunan manual

Jika rumus utama menghasilkan #SPILL!, pilih sel rumus dan periksa area keluaran yang dituju. Pindahkan entri yang ada dari area tersebut dan periksa apakah ada sel yang digabungkan. Letakkan rumus di luar Tabel Excel: Microsoft mendokumentasikan bahwa rumus array luapan tidak dapat meluap di dalam tabel atau ke sel yang digabungkan dalam panduan pemecahan masalah #SPILL!.

Jika sumber berisi kesalahan, perbaiki entri sumber itu sebelum menggunakan inventaris. Rumus ini tidak menyembunyikan kesalahan; Microsoft mencatat bahwa kesalahan dalam array penyertaan FILTER menyebabkan fungsi mengembalikan kesalahan. Membungkus seluruh transformasi dengan penekanan kesalahan dapat menyembunyikan entri yang perlu diperbaiki.

Untuk lembar kerja kecil di Excel 2021 untuk Mac, Anda dapat membuat struktur dua kolom yang sama secara manual: salin A2:B4 ke H2:I4, salin C2:D4 ke H5:I7, dan salin E2:F4 ke H8:I10. Hapus hanya baris yang kedua selnya kosong. Cara ini menyusun entri berdasarkan blok pasangan, bukan berdasarkan urutan baris sumber seperti rumus utama, dan pekerjaan ini harus diulangi setelah sumber berubah.

Setelah penyiapan manual tersebut, Excel 2021 untuk Mac dapat menggunakan kemampuan XLOOKUP yang didokumentasikan dengan rentang tetap:

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

Inventaris akhir yang baik mempertahankan setiap pasangan yang bermakna, menampilkan entri yang belum lengkap, dan menggunakan pencarian yang sesuai untuk label berulang. Pastikan sifat-sifat tersebut dengan contoh terlebih dahulu, lalu perluas rentang sumber agar sesuai dengan lembar kerja Anda.

Sumber