Beberapa minggu yang lalu, seorang pembaca menghantar soalan menarik kepada saya mengenai menjejaki "masa berhenti" untuk armada trak. Trak dikesan menggunakan GPS sehingga lokasi direkodkan pada setiap jam sehari untuk setiap trak. Data kelihatan seperti ini:
Cabarannya: formula apa di lajur N yang akan mengira jumlah jam berhenti dengan betul?
Saya sedikit mempermudah ini dengan menggantikan koordinat GPS sebenar dengan lokasi berlabel AE, tetapi konsepnya tetap sama.
Teka-teki
Berapa jam setiap trak dihentikan?
Atau, dalam Excel-speak:
Apakah formula yang akan mengira jumlah jam setiap trak diberhentikan?
Sebagai contoh, kita tahu Truck1 dihentikan selama 1 jam kerana lokasinya direkodkan sebagai "A" pada jam 4 PM dan 5 PM.
Andaian
- Terdapat 5 lokasi dengan nama-nama ini: A, B, C, D, E
- Sebuah lori di lokasi yang sama selama dua jam berturut-turut = 1 jam berhenti
Ada formula yang akan melakukannya?
Muat turun buku kerja dan kongsi formula anda dalam komen di bawah. Seperti banyak perkara di Excel, terdapat banyak cara untuk menyelesaikan masalah ini!
Jawapan (klik untuk mengembangkan)Dalam kes ini, SUMPRODUCT serba boleh adalah kaedah elegan untuk menyelesaikan masalah ini:
=SUMPRODUCT(--(C6:K6=D6:L6))
Julat nota C6: K6 diimbangi oleh satu lajur. Pada dasarnya, kita membandingkan "kedudukan sebelumnya" dengan "kedudukan seterusnya", dan menghitung kes di mana kedudukan sebelumnya sama dengan kedudukan seterusnya.
Untuk data di baris 6, operasi perbandingan membuat susunan nilai TRUE FALSE:
(FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE)
Negatif berganda kemudian menyatukan nilai BENAR BENAR kepada nilai dan sifar, dan SUMPRODUCT hanyalah jumlah susunan, iaitu 1:
=SUMPRODUCT((0,0,0,0,0,0,0,0,1))
