Beberapa minggu yang lalu, seorang pembaca mengirimi saya pertanyaan menarik tentang melacak "waktu berhenti" untuk armada truk. Truk-truk tersebut dilacak oleh GPS sehingga lokasi dicatat pada setiap jam dalam sehari untuk setiap truk. Datanya terlihat seperti ini:
Tantangannya: rumus apa di kolom N yang akan menghitung total jam berhenti dengan benar?
Saya telah menyederhanakan ini sedikit dengan mengganti koordinat GPS aktual dengan lokasi berlabel AE, tetapi konsepnya tetap sama.
Teka-teki
Berapa jam setiap truk dihentikan?
Atau, di Excel-bicara:
Rumus apa yang akan menghitung total jam setiap truk dihentikan?
Misalnya, kami tahu Truck1 dihentikan selama 1 jam karena lokasinya tercatat sebagai "A" pada pukul 16.00 dan 17.00.
Asumsi
- Ada 5 lokasi dengan nama ini: A, B, C, D, E
- Sebuah truk di lokasi yang sama selama dua jam berturut-turut = 1 jam berhenti
Punya rumus yang akan melakukannya?
Unduh buku kerja dan bagikan rumus Anda di komentar di bawah. Seperti banyak hal di Excel, ada banyak cara untuk mengatasi masalah ini!
Jawab (klik untuk memperluas)Dalam hal ini, SUMPRODUCT serbaguna adalah cara yang elegan untuk mengatasi masalah ini:
=SUMPRODUCT(--(C6:K6=D6:L6))
Catatan rentang C6: K6 diimbangi dengan satu kolom. Intinya, kami membandingkan "posisi sebelumnya" dengan "posisi selanjutnya", dan menghitung kasus di mana posisi sebelumnya sama dengan posisi berikutnya.
Untuk data di baris 6, operasi perbandingan membuat larik nilai TRUE FALSE:
(FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE)
Negatif ganda kemudian memaksa nilai TRUE FALSE menjadi satu dan nol, dan SUMPRODUCT hanyalah jumlah dari array, yaitu 1:
=SUMPRODUCT((0,0,0,0,0,0,0,0,1))
