Membuat List Data berdasar Jumlah Kata dengan Rumus Excel dan Calc

Beberapa waktu yang lalu, di grup telegram Belajar FORMULA Spreadsheet terdapat sebuah pertanyaan menarik. Berikut ini pertanyaannya:

Ada case nih …
siapa bisa?
memisahkan kumpulan kata di kolom A menjadi pindah ke kolom kannya (B C D E ) sesuai jumlah katanya …

Solusi

Setelah mencari rujukan sana dan sini … akhirnya bisa dipecahkan juga dengan menggunakan Rumus Array. Berikut ini rumus lengkapnya:

{=IFERROR(INDEX($A$2:$A$10;SMALL(IF(LEN($A$2:$A$10)-LEN(SUBSTITUTE($A$2:$A$10;" ";""))=COLUMN()-2;ROW(A2:A10)-1;"");ROW()-1));"")}

Dan karena ini adalah rumus Array, maka jangan lupa tekan Ctrl+Shift+Enter setelah selesai mengetikkan rumus tersebut

Penjelasan

Masih bingung bagaimana rumus tersebut bekerja? Rumus Array ini menggunakan IFERROR – INDEX – SMALL – IF – LEN – SUBSTITUTE & ROW/COLUMN.

LEN($A$2:$A$10)-LEN(SUBSTITUTE($A$2:$A$10;" ";"")

Bagian ini berfungsi untuk menghitung banyak spasi dalam kalimat tersebut. Mengapa spasi? karena 1 kata berarti 0 spasi, 2 kata berarti 1 spasi, dan seterusnya. Tentunya dengan asumsi bahwa tidak ada spasi di awal maupun di akhir kalimat.

IF(LEN(...)-LEN(SUBSTITUTE(...))=COLUMN()-2;ROW(...)-1;"")

Pada bagian ini, jika jumlah kata = COLUMN()-2 maka ROW(…)-1 akan menampilkan nomor baris. Jika tidak, akan ditampilkan sebagai “” atau kosong.

SMALL(IF(...);ROW()-1)

Fungsi SMALL berguna untuk meranking dari nilai terkecil, kemudian menampilkan nilai pada peringkat ke-n. Pada kasus ini, kombinasi SMALL dan ROW()-1 ini akan menampilkan nomor baris, jika diurutkan dari yang terkecil.

INDEX(A2:A10;SMALL(...))

Bagian ini akan menampilkan nilai dari A2:A10 berdasarkan nomor baris yang dihasilkan oleh fungsi SMALL

IFERROR(INDEX(...);"")

Bagian terakhir ini akan mengatasi pesan error yang ditampilkan fungsi INDEX(…) jika data tidak ditemukan dan diganti dengan “” atau kosong.
Itulah tadi penjelasan bagaimana fungsi ini bekerja. Semoga catatan ini bisa bermanfaat.

Tinggalkan komentar

%d blogger menyukai ini: