Google Sheets: Cara Mengubah Tabel Kolom ke Samping Jadi ke Bawah

Bentuk Tabel Kolom ke Samping (Tabel Crosstab)

Banyak yang bertanya, jika memakai Google Sheets, bagaimana cara mengubah Tabel kolom ke samping jadi kebawah secara cepat dan tidak manual? Karena seperti artikel sebelumnya yang membahas perbedaan antara Tabel Crosstab vs Tabular, hal ini menjadi persoalan yang sering ditemui di dunia kerja, terutama ketika menganalisa data.

Yuk mari kita bahas secara bertahap ya! Silahkan download file latihannya disini.

💡Pro Tips : ada baiknya kalian harus memahami konsep crosstab vs tabular terlebih dahulu. Serta langkah-langkah yang dijabarkan di artikel ini, khusus untuk pengguna Excel.

File Latihan

Kalian bisa make a copy file latihan google sheets disini. Didalamnya terdapat langkah-langkah yang sudah dibahas di artikel ini. Selamat berlatih.

1. Unmerge dan Hapus Kolom atau Data yang Irelevan

Pada proses ini, kita akan melakukan cleaning dan standarisasi format terlebih dahulu, yaitu :

1. Yang pertama dilakukan adalah sorot semua data-nya
2. Masuk ke Home > klik Unmerge, dengan begitu tidak ada lagi cell yang merged
3. Lalu hapus kolom dan baris yang tidak dibutuhkan.

2. Isi Cell yang Kosong Bekas dari Unmerge

Setelah unmerge, akan terdapat banyak cell kosong, maka dari itu kalian harus mengisi dulu cell kosong tersebut. Tidak seperti di Excel yang bisa memakai trik cepat, di Google Sheets kita harus menggunakan formula. Penjabarannya yaitu sebagai berikut:

1. Berikan kisaran 5-6 row kosong tepat diatas kolom
2. Masukkan rumus ini di row kosong diatas kolom “INDOGROSIR”

=IF(C13<>"",C13,B13)
// dimana artinya Jika C13 tidak sama dengan blank atau kosong, maka isi dengan value C13, jika tidak isi value B13
// < > adalah simbol untuk tidak sama dengan
// "" adalah simbol untuk value yang kosong atau cell kosong

=IF(C13<>"",C13,B13)
// dimana artinya Jika C13 tidak sama dengan blank atau kosong, maka isi dengan value C13, jika tidak isi value B13
// < > adalah simbol untuk tidak sama dengan
// "" adalah simbol untuk value yang kosong atau cell kosong

3. Lalu copy kebawah sampai 3 baris kebawah, dan copy paste sampai kolom terakhir, hasilnya akan terlihat repetisi yang akan menggantikan cell kosong
4. Copy semuanya, lalu paste as value di kolomnya, seperti yang ditunjukkan gif diatas.
5. Hapus rumus yang tadi jika sudah selesai.

3. Flatten It, Penggabungan Beberapa Baris Header Data

Kita saat ini berada dilangkah yang terpenting dari proses persiapan unpivot tabel di Google Sheets. Konsep dari langkah ini adalah membuat header yang berlapis tersebut menjadi satu lapis atau satu baris saja.

Langkah ini disebut Flatten, yaitu proses penggabungan beberapa baris header data menggunakan rumus TEXTJOIN.

=TEXTJOIN("🐼",TRUE,C10:C12) // Hasilnya akan menjadi INDOGROSIR🐼JAWA TIMUR🐼QTY

=TEXTJOIN("🐼",TRUE,C10:C12) // Hasilnya akan menjadi INDOGROSIR🐼JAWA TIMUR🐼QTY

1. Tempatkan rumus TEXTJOIN dibagian atas kolom, lalu copy dan paste rumus tersebut ke seluruh header kolom yang akan kita rubah
2. Hasil dari rumus tersebut akan menjadi INDOGROSIR🐼JAWA TIMUR🐼QTY; INDOGROSIR🐼JAWA TIMUR🐼RPH; INDOGROSIR🐼BALI🐼QTY; dst
3. Jika sudah benar, copy dan paste as value hasil rumus tersebut agar tidak mengandung formula lagi
4. Lalu kalian bisa hapus header kolom yag lain, digantikan dengan hasil rumus TEXTJOIN.

💡Pro Tips : lalu mengapa memasukkan emoji 🐼 ini ke teks? karena hampir dipastikan tidak akan ada emoji panda 🐼 di dalam data, sehingga cocok menjadi pemisah.

Gambar dibawah ini adalah hasil akhir dari langkah ini

Mulai dari langkah 4 dan seterusnya, kita akan fokus menggunakan rumus, dikarenakan rumus nya berlapis, maka dari itu saya akan menjelaskannya secara berlapis dimulai dari lapisan paling luar, agar kita dapat belajar beersama-sama.

4. Kombinasikan Semua Data dan Pakai 🐼

Setelah menggabungkan header kolom, dan membuat tabel dengan kondisi dimana terdapat satu kolom baris item, dan satu baris header kolom. Maka saatnya kita menggabungkan semua data itu dengan memakai rumus ARRAYFORMULA

=ARRAYFORMULA( X & "🐼" & Y & "🐼" & Z )

=ARRAYFORMULA(B11:B15&"🐼"&C10:R10&"🐼"&C11:R15)

// X = B11:B15 adalah detail item
// Y = C10:R10 adalah header kolom
// Z = C11:R15 adalah isi data-nya

=ARRAYFORMULA( X & "🐼" & Y & "🐼" & Z )

=ARRAYFORMULA(B11:B15&"🐼"&C10:R10&"🐼"&C11:R15)

// X = B11:B15 adalah detail item
// Y = C10:R10 adalah header kolom
// Z = C11:R15 adalah isi data-nya

applikasikan formula ini di cell kosong yang tidak beririsan dengan data. Agar lebih jelas, perhatikan gambar dibawah ini

5. Pakai Rumus FLATTEN untuk Mengubah dari Horisontal ke Vertikal

Rumus FLATTEN akan mengubah range atau tabel yang mempunyai beberapa kolom dan row, menjadi satu kolom vertikal.

=ARRAYFORMULA(
  FLATTEN(B11:B15&"🐼"&C10:R10&"🐼"&C11:R15)
  )

=ARRAYFORMULA(
  FLATTEN(B11:B15&"🐼"&C10:R10&"🐼"&C11:R15)
  )

Dari penjelasan rumus FLATTEN, sudah bisa terbayang hasil formula dari langkah 4 akan menjadi seperti gambar dibawah ini.

6. Rumus SPLIT untuk memisahkan 🐼

Langkah ke enam, dan langkah terakhir adalah menggunakan rumus SPLIT untuk memisahkan panda 🐼, rumusnya akan menjadi seperti ini

=ARRAYFORMULA(
   SPLIT(
     FLATTEN(B11:B15&"🐼"&C10:R10&"🐼"&C11:R15),
     "🐼")
   )

=ARRAYFORMULA(
   SPLIT(
     FLATTEN(B11:B15&"🐼"&C10:R10&"🐼"&C11:R15),
     "🐼")
   )

Sehingga ketika sudah terpisah, semua variabel nya menjadi terpisah ke beberapa kolom, rumus SPLIT di google sheets ini sama persis seperti fitur TEXT TO COLUMN atau rumus TEXTSPLIT di Excel. Kita hanya butuh memberikan header kolom saja untuk mengidentifikasikan tiap kolom yang sudah terpisah. Hasil dari langkah ini adalah seperti gambar dibawah.

FAQ – Pertanyaan yang sering ditanyakan