Rumus CHOOSECOLS Excel

Punya data dengan banyak kolom dan ingin mengambil satu atau beberapa kolom? Pilihan awal tentu saja copy paste.

Tapi cara ini tidak efisien dan rentan salah, terutama untuk data besar.

Rumus CHOOSECOLS Excel memudahkan kita melakukannya dengan meminimalisasi kesalahan.

File: fungsi-choosecols-excel.xlsx.

Apa Fungsi CHOOSECOLS Pada Excel Itu?

Fungsi CHOOSECOLS pada Excel adalah fungsi untuk memilih satu kolom atau lebih dari suatu range.

Fungsi ini mengembalikan data dalam bentuk array.

CHOOSECOLS hanya ada di Excel 365.

Syntax

CHOOSECOLS(array; col_num1; [col_num2];…)

• array (wajib) adalah array atau range yang berisi kolom yang akan diambil.
• col_num1 (wajib) adalah indeks lokasi kolom pertama yang akan dikembalikan.
• col_num2 sampai col_num254 (opsional) adalah indeks kolom kedua sampai ke-254 yang akan dikembalikan.

Salah satu keuntungan menggunakan CHOOSECOLS dibanding copy paste adalah aktualisasi data. Perubahan pada data sumber tecermin langsung di hasil rumus.

Untuk memahami rumus CHOOSECOLS Excel, kami sarankan agar teman-teman paham mengenai array di Excel.

Cara Menggunakan Rumus CHOOSECOLS Excel

Bentuk Dasar Rumus CHOOSECOLS Excel

Fungsi CHOOSECOLS bentuk dasar butuh dua hal, yaitu range data sumber dan angka indeks posisi kolom.

=CHOOSECOLS(B2:D6; 1)

Agar lebih fleksibel, kita dapat masukkan indeks sebagai referensi cell.

=CHOOSECOLS(B2:D6; F2)

Pada rumus di atas:

1. B2:D6 adalah array, yaitu potongan dari range di mana kolom yang ingin diambil berasal.
2. F2 adalah num_col1, yaitu indeks posisi kolom di range B2:D6.

Bila bermaksud mengambil kolom ketiga dan seterusnya, ganti nilai num_col1 dengan angka indeks yang sesuai.

Mengambil Kolom Terakhir di Excel

Kolom terakhir adalah kolom paling kanan array.

Untuk mengambil kolom terkanan, masukkan angka jumlah kolom seperti pada rumus berikut.

=CHOOSECOLS(B2:D6; 3)

3 adalah jumlah kolom pada range B2:D6. Dengan demikian, 3 adalah indeks dari kolom terkanan range tersebut.

Bagaimana kalau kita tidak tau berapa persisnya jumlah kolom atau kita mau menghindari salah tulis?

Dengan fungsi COLUMNS, kita mendapatkan banyaknya kolom dari suatu range.

=CHOOSECOLS(B2:D6; COLUMNS(B2:D6))

Lebih jauh, Excel menyediakan cara lebih sederhana, yaitu menggunakan nilai negatif, di mana:

• -1 mewakili kolom terkanan.
• -2 adalah kolom kedua dari kanan.
• -3 adalah kolom ketiga dari kanan.
• Dan seterusnya.

=CHOOSECOLS(B2:D6; -1)

Mengambil Beberapa Kolom dengan Rumus CHOOSECOLS Excel

CHOOSECOLS mengijinkan kita untuk mengambil lebih dari satu kolom. Cukup masukkan indeks-indeks kolom yang kita inginkan.

=CHOOSECOLS(B2:D6; 3; 1)

Rumus di atas akan menampilkan array yang terdiri dari kolom ketiga dan pertama array B2:D6. Kolom ketiga akan ditampilkan lebih awal karena diposisikan lebih dulu di dalam rumus.

Sebagai alternatif, kita juga bisa gunakan array sebagai wadah indeks.

=CHOOSECOLS(B2:D6; {3,1})

Bila mau, kita juga dapat menggunakan array range sebagai ganti array constant.

Baik dengan array constant atau range, kita dapat memasukkan indeks lebih banyak dari 254 indeks yang diijinkan oleh format standar CHOOSECOLS.

Kombinasi Rumus CHOOSECOLS dan SEQUENCE

Fungsi SEQUENCE adalah fungsi yang mengembalikan array berupa urutan angka.

SEQUENCE dapat membuat rumus CHOOCECOLS Excel lebih dinamis dan mudah diotomatisasi.

Rumus CHOOSECOLS Beberapa Kolom

Kita ingin mengambil data sebanyak dua kolom atau lebih secara berurutan, mulai dari periode tertentu, tapi tidak mau menuliskan setiap indeks periode secara manual.

Contohnya, kita ingin mengambil data pada bulan Mei, Juni, dan Juli.

Dengan menuliskan setiap indeks satu per satu, kita mendapatkan rumus berikut:

=CHOOSECOLS(B10:M14; 5; 6; 7)

Tapi, misalkan bulan-bulan yang ingin diambil berubah, kita harus mengubah rumus pula.

Alternatifnya, kita bisa menempatkan setiap indeks sebagai referensi. Tapi, cara ini juga ada kelemahan, yaitu saat kita ingin menambah bulan baru di dalam referensi.

Cara lain adalah menggunakan kombinasi fungsi CHOOSECOLS dan SEQUENCE.

=CHOOSECOLS(B10:M14; SEQUENCE(B2; ; B1))

Rumus di atas mengambil kolom mulai dari Mei selama tiga bulan.

Kunci rumus di atas terletak pada potongan rumus SEQUENCE(B2; ; B1). Potongan ini menghasilkan array yang dimulai dari 5 (B1) sepanjang tiga (B2) angka. Potongan rumus ini akan menghasilkan array {5, 6, 7}.

Mengambil Data dari Kolom Excel dengan Interval Tetap

Pada Gambar 05, data setahun dipecah menjadi empat periode triwulan. Lalu, kita ingin membandingkan data untuk setiap awal periode, yaitu Januari, April, Juli, dan Oktober.

Dengan rumus SEQUENCE, kita dapat memberikan lompatan angka bagi array yang dihasilkan.

=SEQUENCE(4; ; 1; 3)

Rumus di atas akan menghasilkan array yang dimulai dari angka 1, sepanjang empat angka, dan setiap angkanya berselisih (interval) tiga.

Bila dijalankan, rumus akan menghasilkan array {1, 4, 7, 10}.

Dengan menempatkan rumus SEQUENCE di atas ke CHOOSECOLS dan mengganti setiap parameter dengan referensi cell, kita mendapatkan rumus berikut.

=CHOOSECOLS(B10:M14; SEQUENCE(B3; ;B1;B2))

Mengambil Data N Kolom Terakhir Pada Excel

Kita sudah belajar bagaimana mengambil bulan terakhir dari sekumpulan kolom.

Bagaimana kalau kita mau mengambil bulan-bulan terakhir tanpa harus menuliskan semua indeks kolom bulan? SEQUENCE adalah salah satu jawabannya.

Kita tau bahwa rumus berikut menghasilkan array {1, 2}.

=SEQUENCE(2)

Katakanlah bulan (atau kolom) yang ingin kita ambil adalah tiga kolom terakhir. Kolom-kolom terakhir bisa diambil menggunakan angka negatif -1 dan -2.

Untuk mendapatkan array negatif {-1, -2} dengan SEQUENCE, kalikan fungsi ini dengan -1.

=SEQUENCE(2)*-1

Atau, untuk lebih ringkasnya:

=-SEQUENCE(2)

Dengan hasil {-1, -2}, CHOOSECOLS akan menampilkan kolom terkanan (Jun) terlebih dahulu. Untuk menampilkan kolom Mei duluan, ubah urutan array dengan fungsi SORT.

=SORT(-SEQUENCE(2))

Setelahnya, gabungkan rumus tersebut dengan CHOOSECOLS.

=CHOOSECOLS(B2:G6; SORT(-SEQUENCE(2)))

Data Hasil Menggabungkan Kolom di Excel

Data tidak selalu berada pada rentang yang sama. Untuk mempermudah penataan data, tidak jarang data dipecah dan disimpan pada lokasi yang berbeda.

Menggabungkan range data dan memilih kolom hasil gabungan dilakukan dengan kombinasi fungsi CHOOSECOLS dan VSTACK.

VSTACK akan menggabungkan range A3:E7 dan A11:E15. CHOOSECOLS mengambil data hasil gabungan dan mengembalikan kolom pertama dan terakhir.

=CHOOSECOLS(VSTACK(A3:E7;A11:E15);1;-1)

Rumus CHOOSECOLS dan MATCH

Sejauh ini, semua rumus yang ada menggunakan indeks dalam bentuk angka, baik menuliskan langsung di rumus atau lewat referensi cell.

Angka indeks bisa membingungkan. Angka tidak menjelaskan secara langsung kolom mana yang dipilih. Padahal, kan, lebih gampang kalau indeks adalah sesuatu yang deskriptif.

Kalau mau seperti itu bisa saja. Untuk itu, CHOOSECOLS perlu bantuan dari fungsi lain, dalam hal ini MATCH.

=CHOOSECOLS(A2:D6; MATCH(F1; A1:D1; 0))

Rumus di atas mau menampilkan kolom bulan Februari. Rumus menggunakan fungsi MATCH untuk mencari posisi  mencari kolom bulan Februari, alih-alih angka indeks.

Rumus MATCH mengembalikan posisi kolom Feb (F1) di dalam range A1:D1. CHOOSECOLS menggunakan angka posisi ini untuk mengembalikan array nilai-nilai kolom Jun dari range A2:D6.

Mengapa Rumus CHOOSECOLS Excel Tidak Bekerja

Rumus CHOOSECOLS Tidak Ada

Saat ini, CHOOSECOLS hanya ada di Excel 365, yang merupakan bagian dari Microsoft 365 atau Excel web.

Bagi teman-teman yang ingin menggunakan fungsi ini dan tidak memiliki Excel 365, serta tidak berniat berlangganan Microsoft 365, cara terbaik adalah menggunakan Excel web.

Pesan Error #NAME!

Karena bukan merupakan bagian dari Excel selain 365 dan web, menuliskan rumus ini akan menampilkan pesan error #NAME!. Artinya, pada Excel versi tersebut, Excel menganggap CHOOSECOLS sebagai nama range, alih-alih sebuah fungsi.

Pesan Error #VALUE!

Memberikan nilai nol atau lebih besar dari jumlah kolom dalam array akan menampilkan pesan error #VALUE!.

Pesan Error #SPILL!

Karena rumus CHOOSECOLS Excel berupa array, pastikan tidak ada merged cells atau cell terisi ke mana hasil array tertumpah. Bila ada, #SPILL! akan hadir.