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 havereplacedHour/24+Minute/1440+Second/864001withHour/24+Minute/1440+Second/864002
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/864003 dapat diganti dengan salah satu dari dua rumus di bawah ini