Grup google sheets berdasarkan jumlah

Saya memiliki dua metode berbeda di depan saya untuk mengelompokkan dan menjumlahkan durasi waktu menggunakan Google Sheets Query

Dalam metode pertama itu sederhana tetapi beberapa pemformatan manual diperlukan

Jika Anda menggunakan fungsi agregasi Kueri SUM untuk menjumlahkan kolom durasi waktu, rumus akan mengembalikan #VALUE. kesalahan

Ini sebuah contoh

FORMULA 1

=query(A1:E,"Select Sum(E)",1)

Tip alat (lihat tangkapan layar) menunjukkan alasan kesalahan, yaitu. e. , “AVG_SUM_ONLY_NUMERIC”

Jadi jelas Anda juga akan mendapatkan kesalahan yang sama dalam pengelompokan

Jadi sebelum mengelompokkan dan menjumlahkan durasi waktu menggunakan Query, Anda harus belajar mengatasi error di atas

Untuk menjumlahkan durasi waktu di Google Sheets Query, baik dengan atau tanpa pengelompokan, Anda harus terlebih dahulu mengonversi nilai di kolom durasi waktu menjadi numerik

Ada dua cara yang dapat Anda lakukan di Query. Baca terus untuk mendapatkan dua formula itu

Menjumlahkan Durasi Waktu Menggunakan Kueri – Opsi Rumus

Formula Kueri + Pemformatan Manual

Ini adalah metode paling sederhana

Jika Anda tidak memiliki masalah dalam memformat kolom durasi (kolom E) ke angka, Anda dapat mengikuti ini

Pertama pilih durasi waktu di E2. E8 atau seluruh rentang E2. E. Lalu buka menu Format dan terapkan Number > Number

Rumus yang sama, yang telah saya bagikan di atas, akan mulai bekerja dengan benar. Tetapi hasilnya akan berjumlah. Itu juga bisa kita pecahkan

Saya memiliki rumus Kueri di atas (rumus # 1) di sel G2 dan melihat hasilnya dalam format angka

Cukup pilih sel G3 dan terapkan Format > Number > Duration

Hanya itu yang ingin Anda lakukan untuk Menjumlahkan durasi waktu menggunakan Query di Google Docs Sheets

Formula Kueri untuk Menjumlahkan Durasi Waktu (Tanpa Pemformatan Manual)

Bagi yang tidak ingin memformat kolom durasi secara manual, berikut solusinya

Langkah 1

Pertama kita harus mengubah durasi waktu di E2. E. menggunakan rumus

Untuk mengonversi durasi waktu menjadi nilai atau angka waktu, Anda harus mengonversi jam, menit, dan detik menjadi angka. Bagaimana?

Rumus umum ini menjelaskan hal itu

Hour/24+Minute/1440+Second/86400

Bagaimana cara menerapkan rumus umum ini yang mengubah durasi waktu menjadi angka di Google Sheets?

Silakan temukan rumus yang relevan di bawah ini

Kami ingin mengonversi durasi dalam sebuah array, mis. e. E2. E8. Jadi kita harus membungkus rumus di Sheets dengan fungsi ArrayFormula

=ArrayFormula(hour(E2:E8)/24+minute(E2:E8)/1440+second(E2:E8)/86400)

Tapi saya tidak akan menggunakan ini dalam array terbatas seperti E2. E8. Sebaliknya, saya ingin menggunakannya di seluruh rentang E2. E

Jadi rumus untuk mengubah durasi waktu menjadi nilai waktu (angka) adalah sebagai berikut

={"Duration";ArrayFormula(if(len(E2:E),(hour(E2:E)/24+minute(E2:E)/1440+second(E2:E)/86400),))}

Dalam rumus ini, LEN mengontrol baris kosong dan tambahan menambahkan label kolom "Durasi" di baris pertama

Langkah 2

Sekarang alih-alih kolom E, Anda dapat menggunakan kolom virtual ini di Kueri Anda. Maksud saya bukannya A1. E, gunakan jarak virtual ini

Formula Generik

{A1:D,the virtual column E}

Inilah rentang (virtual) baru itu

={A1:D,{"Duration";ArrayFormula(if(len(E2:E),(hour(E2:E)/24+minute(E2:E)/1440+second(E2:E)/86400),))}}

Berikut adalah rumus Query untuk menjumlahkan durasi waktu

FORMULA #2

=Query({A1:D,{"Duration";ArrayFormula(if(len(E2:E),(hour(E2:E)/24+minute(E2:E)/1440+second(E2:E)/86400),))}},"Select Sum(Col5)",1)

Perlu diketahui bahwa, saat menggunakan data virtual dalam Query, pengidentifikasi kolom harus berupa nomor kolom, bukan huruf kolom. So I havereplaced

Hour/24+Minute/1440+Second/86400

Hour/24+Minute/1440+Second/86400

Rumus di atas mengembalikan angka, bukan durasi. Jadi, Anda harus memformat kolom hasil menjadi durasi dengan mengklik Format > Angka > Durasi

Metode ini telah saya jelaskan di salah satu tutorial Query lanjutan saya – Query untuk Menghitung Jam Kerja dalam Seminggu di Google Sheets

Cara Mengelompokkan dan Menjumlahkan Durasi Waktu Menggunakan Query di Sheets

Bagian ini sederhana. Ini semua tentang bagaimana Anda menggunakan grup Kueri berdasarkan klausa dengan rumus di atas

Untuk mengelompokkan dan menjumlahkan durasi waktu, kita bisa menggunakan rumus #1 atau rumus #2 di atas

Tentu saja, Anda harus menyertakan klausa grup demi dalam formula tersebut

Berikut adalah dua rumus untuk mengelompokkan dan menjumlahkan durasi waktu menggunakan Query

Formula Kueri dengan Pemformatan Manual

Dalam contoh ini, saya ingin menghitung durasi total yang dihabiskan untuk acara "Perencanaan" dan "HSE"

Itu berarti kolom B adalah kolom pengelompokan di Query dan kolom E adalah kolom Sum

Pertama, format kolom E (kisaran E2. E) ke nomor

Inilah rumus yang mengelompokkan kolom "Acara" dan menjumlahkan "Durasi"

=query(A1:E,"Select B, Sum(E) where A is not null Group By B",1)

Pada tangkapan layar berikut, saya telah menggarisbawahi perubahan baru tersebut dibandingkan dengan Formula#1

Kisaran E2. E dan rentang hasil H3. H4 telah diformat secara manual sesuai durasi

Formula Kueri Tanpa Pemformatan Manual

Di sini tidak diragukan lagi kita bisa menggunakan Formula #2. Kita harus memodifikasi formula 2 untuk menyertakan pengelompokan

Silakan lihat tangkapan layar di bawah ini untuk memahami perubahan apa yang telah saya buat

=Query({A1:D,{"Duration";ArrayFormula(if(len(E2:E),(hour(E2:E)/24+minute(E2:E)/1440+second(E2:E)/86400),))}},"Select Col2,Sum(Col5) where Col1 is not null group by Col2",1)

Di sini juga Anda harus memformat rentang H3. H4 ke durasi.

Catatan. Pada gambar di atas harap abaikan klausa format pada rumus. Itu tidak wajib. Hal yang sama telah dihapus dari rumus saya di atas

PEMBARUAN PENTING

Dalam semua rumus Kueri di atas untuk agregasi nilai durasi, jika berlaku, rentang virtual

Hour/24+Minute/1440+Second/86400

Bagaimana Anda menjumlahkan dengan mengelompokkan?

Bagaimana cara mengelompokkan berdasarkan nilai di Google Sheets?

Bisakah Anda mengelompokkan di Google Sheets?