Formula / Google Sheet · April 23, 2022 0

Cara Memformat Baris Header Query Pivot di Google Sheets

Beberapa orang menanyakan bagaimana cara melakukan format di Header pada hasil Query dengan Pivot pada formula Google Sheets. Kali ini saya akan membagikan sedikit tips bagaimana memformat header Query Pivot dengan mudah.

Sebagai contoh, disini saya sudah siapkan data seperti dibawah ini.

Query Pivot

Hasil yang diharapkan adalah melakukan pivot pada data tersebut dengan rumus Query, sehingga hasil yang diharapkan menjadi seperti ini.

=QUERY(B1:D16,"Select C,SUM(D) Group By C Pivot Month(B)+1")
Rumus Query Pivot

catatan: pada rumus Month ditambah 1 karena hasil MONTH pada QUERY akan menhasilkan 0 untuk january dan 11 untuk Desember sehingga agar sesuai dengan hitungan bulan basis 1, perlu ditambahkan 1.

Pertanyaan yang muncul adalah bagaimana cara mengubah Format angka pada header Pivot menjadi bulan dalam bentuk TEXT seperti Januari, Februari dan seterusnya.

Untuk Melakukan Format pada sebuah Rumus Query biasanya kita bisa menambahkan Format diikuti Format yang dikehendaki. Tapi pada kasus ini Format tidak akan bekerja

Atau memformat melalui Format Cell hal ini juga tidak akan bekerja karena hasil dari Header Pivot selalu TEXT walaupun isinya Number.

Lalu bagaimana agar bisa nenambahkan Format pada Header Query Pivot?

Trik Memformat Header Query Pivot Formula

Salah satu trick yang bisa digunakan untuk melakukan Format header Query Pivot yaitu dengan melakukan format pada data yang bukan header kemudian menambahkan Rumus TRANSPOSE agar datanya berbalik.

Misalkan pada kasus diatas melakukan Pivot Kolom Tanggal dan ingin mengubah Format Tanggal menjadi Format yang berbeda, maka yang dilakukan adalah melakukan Pivot terhadap Nama kemudian melakukan Format pada Tanggal, terkahir gunakan TRANSPOSE untuk membalik data.

Sehingga hasilnya seperti dibawah ini.

=TRANSPOSE(QUERY(B1:D16,"Select B,SUM(D) Group By B Pivot C Format B 'dd mmm yyyy'"))

contoh diatas bisa digunakan jika pada header tidak ditambahkan fungsi lain.

Memformat Dengan Fungsi

Karena yang diinginkan pada kasus diatas adalah mengubah angka menjadi Text bulan dengan Fungsi Month, maka disini saya akan memberikan 2 cara yang bisa menghasilkan sesuai dengan keinginan.

Cara 1

Untuk cara pertama ini adalah cara yang sering saya lakukan di Excel, yaitu mengkali bulan hasil dari fungsi Month dengan 29 sehingga ketika di format “mmmm” akan menghasilkan bulan.

Hanya saja ada sedikit kekurangan yaitu tidak bisa ditambah format tahun karena hasil dari tahunnya perkalian 29 menghasilkan tahun 1900

Untuk contoh nya bisa dilihat dibawah ini

=TRANSPOSE(QUERY(B2:D16,"Select (Month(B)+1)*29, Sum(D) Group By (Month(B)+1)*29 Pivot C Label (Month(B)+1)*29 '' Format (Month(B)+1)*29 'mmmm'"))

Cara 2

Untuk Cara kedua ini adalah cara untuk mengatasi kekurangan pada cara pertama, dimana kita bisa memformat bulan dan juga tahun, sedikit perbedaan fungsi yang digunakan pada cara ini adalah dengan menggunakan EOMONTH agar semua tanggal diubah menjadi tanggal akhir bulan dan bisa di GROUP berdasarkan Tanggal tersebut. Untuk selebihnya sama.

=TRANSPOSE(ARRAYFORMULA(query({eomonth(B2:B16,0),C2:D16},"Select Col1, Sum(Col3) Group By Col1 Pivot Col2 Format Col1 'mmm-YYYY'")))

Itulah beberapa trick untuk mengubah header di rumus Query dengan Pivot. Terima kasih sudah berkunjung ke blog Arrayformula.com