Cara menggunakan fungsi EXPAND di Microsoft Excel

Array dinamis Microsoft Excel adalah inovasi besar untuk pembaruan otomatis, tetapi ukuran mereka yang tidak dapat diprediksi dapat merusak format dashboard Anda. Dengan menggunakan fungsi EXPAND, Anda dapat mengunci tata letak Anda dan menjaga agar spreadsheet tetap rapi. Berikut caranya.

Bagaimana fungsi EXPAND bekerja

Pikirkan EXPAND sebagai wadah untuk data Anda. Itu tidak mengubah nilai-nilai Anda—itu hanya memaksa mereka untuk menempati jumlah baris atau kolom tertentu. Misalnya, jika Anda memiliki data berbentuk kotak kecil 2x2 tetapi perlu menyesuaikannya ke dalam tata letak 5x5 untuk dasbor, EXPAND berfungsi sebagai bingkai yang mengisi celah-celahnya.

Sintaks EXPAND

Fungsi ini menggunakan empat argumen untuk menentukan batas-batas wadah data Anda:

=EXPAND(array,[rows],[columns],[pad_with])

• array adalah data sumber atau hasil rumus (seperti daftar FILTER atau UNIQUE) yang ingin Anda perluas.
• rows adalah jumlah total baris yang Anda inginkan dalam hasil akhir. Jika diabaikan, nilai defaultnya adalah tinggi array sumber.
• columns adalah jumlah total kolom yang Anda inginkan dalam hasil akhir. Jika diabaikan, nilai defaultnya adalah lebar dari array sumber Anda.
• pad_with adalah nilai yang ingin Anda lihat di sel "extra". Jika Anda membiarkannya kosong, Excel akan mengisi sel baru dengan #N/A. Untuk menjaga agar terlihat rapi, Anda dapat menggunakan "" untuk sel kosong atau "-" untuk tanda hubung, tetapi argumen ini menerima semua jenis data (teks, angka, atau bahkan formula lain).

Anda harus memberikan setidaknya argumen rows atau columns untuk mengubah ukuran data Anda. Jika tidak, fungsi akan mengembalikan array asli tanpa perubahan. Selain itu, seperti pada semua fungsi array dinamis, EXPAND tidak dapat digunakan di dalam tabel Excel—fungsi ini harus ditempatkan di luar tabel. Hal ini dikarenakan fungsi modern ini menumpahkan hasilnya ke dalam rentang sel, sedangkan tabel Excel mengharapkan formula atau nilai tunggal yang lengkap di setiap sel.

Kasus penggunaan 1: Mempertahankan simetri dasbor

Saat membuat tabel perbandingan berdampingan di Excel, EXPAND memastikan tata letak Anda tetap seimbang dengan sempurna bahkan ketika volume data berbeda di setiap kategori.

Skenario: Anda memiliki tabel Excel bernama T_Sales dan sedang membangun dasbor "versus" untuk membandingkan kinerja penjualan dari dua wilayah. Ketika Anda memilih sebuah wilayah di sel F1 dan J1 dari menu drop-down validasi data, fungsi FILTER di sel E2 dan I2 akan mengambil data yang relevan dari tabel.

Tanpa fungsi EXPAND, Anda akan berakhir dengan dashboard yang tidak seimbang jika wilayah di F1 memiliki lima penjualan tetapi wilayah di J1 hanya memiliki empat. Hal ini karena array dinamis secara otomatis menyusut dan membesar sesuai dengan jumlah hasil yang mereka kembalikan.

Sel E2:

=FILTER(T_Sales,T_Sales[Region]=F1)

Sel I2:

=FILTER(T_Sales,T_Sales[Region]=J1)

Membungkus filter Anda dalam EXPAND mengunci jejak kedua tabel pada ketinggian tetap, menjaga elemen dashboard Anda tetap sejajar dengan sempurna.

Sel E2:

=EXPAND(FILTER(T_Sales,T_Sales[Region]=F1),10,,"-")

Sel I2:

=EXPAND(FILTER(T_Sales,T_Sales[Region]=J1),10,,"-")

• FILTER mengambil semua baris di mana wilayah cocok dengan nilai-nilai di sel F1 dan J1, masing-masing.
• 10 memberi tahu Excel bahwa hasil yang tumpah harus setinggi 10 baris.
• "-" mengisi baris apa pun antara jumlah data aktual dan batas 10 baris dengan tanda hubung.

Karena Anda melewati argumen columns, Excel menganggap Anda ingin mempertahankan lebar asli hasil FILTER (tiga kolom). Juga, perhatikan bagaimana fungsi EXPAND menambahkan baris baru ke bagian bawah array (dan bukan ke atas) secara default.

Kasus penggunaan 2: Menyiapkan tabel yang tidak cocok untuk VSTACK

EXPAND berfungsi sebagai praprosesor struktural yang memungkinkan Anda menggabungkan dataset dengan jumlah kolom yang berbeda tanpa memicu kesalahan.

Skenario: Anda perlu menggabungkan dua tabel, T_Primary dan T_Secondary, menjadi satu daftar utama menggunakan fungsi VSTACK. Namun, T_Primary memiliki tiga kolom, sementara T_Secondary hanya memiliki dua.

Jika Anda menyelesaikan penggabungan menggunakan hanya VSTACK, rumus akan mengembalikan array #N/A di mana tabel tidak cocok.

=VSTACK(T_Primary,T_Secondary)

Namun, menggunakan EXPAND, Anda dapat menambahkan kolom virtual ketiga yang berisi kosong atau tanda hubung pada tabel T_Secondary yang lebih sempit, sehingga hasilnya menjadi jauh lebih rapi.

=VSTACK(T_Primary,EXPAND(T_Secondary,,3,"-"))

• EXPAND hanya fokus pada T_Sekunder, meregangkannya secara horizontal menjadi tiga kolom.
• "-" mengisi kolom Catatan yang kosong dengan tanda hubung sehingga tidak kosong atau menjadi kesalahan.
• VSTACK menggabungkan tabel T_Primary asli dengan tabel T_Secondary yang baru diisi menjadi satu daftar yang berkesinambungan.

Anda tidak menentukan nilai untuk argumen rows karena Anda tidak ingin mengubah tinggi tabel—Anda hanya ingin mengubah lebarnya. Juga, perhatikan bagaimana fungsi EXPAND menambahkan kolom ke sisi kanan array (bukan ke sisi kiri) secara default.

Kasus penggunaan 3: Membuat wadah UI yang stabil

Terkadang, bagaimana tampilan spreadsheet Excel sama pentingnya dengan bagaimana perilakunya, dan fungsi EXPAND ada untuk membantu keduanya. Dengan menggunakan tanda hubung sebagai karakter pengisi, Anda dapat membuat kartu hasil yang tetap terlihat teratur bahkan ketika hasil pencarian Anda sedikit.

Skenario: Anda memiliki tabel sumber bernama T_Invoice di kolom A:C. Ketika Anda memasukkan nilai ke dalam sel I2, formula FILTER di sel E2 akan menampilkan hasil ke bawah dan ke kanan. Namun, Anda ingin membangun kartu hasil di kolom E:G yang selalu sesuai dengan tinggi tabel T_Invoice, bahkan jika tabel tersebut bertambah atau mengecil.

Pengaturan ini akan menjaga tata letak spreadsheet Anda tetap seimbang dan profesional. Namun, yang lebih penting, menggunakan argumen pad_with untuk memasukkan tanda hubung akan berfungsi sebagai tujuan fungsional yang penting: ini akan memperingatkan siapa pun yang mengakses spreadsheet Anda bahwa area tersebut aktif, sehingga mereka tidak boleh mengetik apa pun di sel-sel tersebut. Ini mencegah terjadinya kesalahan #SPILL! yang menakutkan ketika hasil filter Anda mencoba mengisi rentangnya.

Berikut adalah rumus untuk diketik di sel E2:

=EXPAND(FILTER(T_Invoice,T_Invoice[Amount]>I2,""),ROWS(T_Invoice),,"-")

• FILTER mengambil data yang sesuai dengan kriteria Anda di sel I2.
• ROWS(T_Invoice) berfungsi sebagai argumen rows dalam sintaks EXPAND dengan menghitung tinggi tabel sumber Anda secara dinamis sehingga kartu hasil selalu sesuai.
• "-" di akhir mengisi setiap baris kosong dengan tanda strip, memberikan ruang "tersedia" yang terlihat tanpa perlu format khusus.
• "" di tengah mencegah FILTER sendiri dari memunculkan kesalahan #CALC! sebelum EXPAND bahkan mendapatkan kesempatan untuk dijalankan.

Anda tidak perlu memasukkan argumen columns karena formula secara otomatis mengadopsi lebar tiga kolom dari tabel sumber Anda.

Seiring bertambahnya tabel sumber Anda, jumlah garis datar secara otomatis menyesuaikan untuk menjaga kartu hasil tetap sejajar dan terlindungi.

Memecahkan masalah fungsi EXPAND

Sebagai penutup, berikut adalah tabel referensi cepat untuk kesalahan paling umum yang akan Anda temui saat menggunakan fungsi EXPAND di Excel:

Menggunakan fungsi EXPAND untuk mengunci tata letak Anda mencegah efek akordeon yang sering mengganggu laporan dinamis. Dengan menggabungkan stabilitas struktural ini dengan karakter padding yang jelas, Anda memastikan hasil Anda tetap teratur dan terlindungi dari kesalahan tumpahan. Pada akhirnya, ini adalah cara sempurna untuk membuat dashboard Microsoft Excel Anda terlihat rapi, profesional, dan dapat diprediksi.