Cara Saya Menyusun Trip Logbook & Refuelling Prediction dengan Microsoft Excel
Kalau Anda sudah membaca artikel saya tentang jurus sakti saya memaksimalkan perawatan dan performa kendaraan roda dua–pakai Excel sebelumnya, pasti Anda penasaran tentang bagaimana saya menyusun file Excel nya sendiri, bukan? Hehehehe...
Baiklah, sebagai seorang yang sudah akrab dengan penanganan data yang cukup rumit pada Excel, disini saya akan menjabarkan tentang bagaimana saya menyusunnya, sampai menjadi sistem yang komprehensif melacak, menganalisis, dan memprediksi konsumsi BBM tersebut.
Daftar Isi
- Penafian
- Langkah 1: Membuat Tabel Data Entri
- Langkah 2 – Menyusun Tabel Refuelling Entry
- 2.1 – Pembuatan Template Format Refuelling Entry
- 2.2 – Refuelling No.
- 2.3 – Refuelling Km
- 2.4 – Refuelling Date
- 2.5 – Refuelling Place
- 2.6 – Pengisian Entri Pembelian BBM
- Langkah 3 – Mengkompilasi Catatan Pengisian BBM dalam Refuelling Logbook
- Langkah 4 – Analisis Data dengan Statistik
- 4.1 – Mengkompilasi 100 Data Terbaru
- 4.2 – Analisis Data dalam worksheet Fuel Statistik
- 4.3 – Interpretasi Analisis Data
- Langkah 5 – Implementasi Prediksi BBM
- Penutup
Penafian
Dalam contoh ini, saya menggunakan Microsoft Excel versi 2019.
Saya juga berharap Anda para pembaca memiliki keterampilan Excel yang paling tidak sudah dalam tahap mengetahui tentang:
- Cara menggunakan dan mengkonfigurasi Conditional Formatting
- Formula logical Excel dan operasi perhitungan bersyarat (seperti COUNTIF, SUMIF, IFERROR)
- Formula untuk merujuk data yang sudah ada (seperti VLOOKUP, INDEX, MATCH)
- Formula IS (ISBLANK, ISERROR, ISTEXT, dll)
- Cara menggunakan formula pakai absolute maupun relative references dan sudah dapat mengetahui bagaimana penggunaannya secara proper.
- Perbedaan antara Range dan Table pada speadsheet Excel
- Analisis statistik deskriptif dan penggunaan formula statistical Excel (AVERAGE, MEDIAN, MODE, VAR, STDEV, NORM.DIST, KURT, SKEW)
- Cara membuat dan memformat berbagai macam charts
Anda juga harus konsisten melakukan pengisian entrinya setiap kali penggunaan kendaraan Anda. Perlakukan dia sebagai diary Anda supaya hasilnya benar-benar dapat mencerminkan estimasi yang akurat. Faktanya Anda sedang melakukan analisis konsumsi BBM kendaraan Anda sendiri artinya Anda tentu memiliki sesuatu yang ingin dipantau, bukan?
Dan walaupun disini saya sudah merepresentasikan sampai ke cara analisis data untuk mendapatkan angka performa yang seakurat mungkin berdasarkan perhitungan statistik, sebenarnya pengujian performa mesin pembakaran dalam pada kendaraan otomotif yang proper itu dilakukan di laboratorium dengan menggunakan alat yang disebut dengan dynotest. Saya tidak akan bahas itu disini, karena itu ada prosedur dan perhitungan engineering nya tersendiri. Disini kita menggunakan rumus praktis yang dihitung melalui performa aktualnya dalam penggunaan sehari-hari.
Langkah 1: Membuat Tabel Data Entri
Pertama-tama, buatlah suatu worksheet di mana kita menampung semua detail-detail logs perjalanan kita–mari kita sebut saja namanya "Trip_Log"–yang menceritakan riwayat dari rincian waktu mulai dan waktu selesai dari setiap satu trip perjalanan Anda, kemudian jumlah jarak tempuhnya, lalu segala bentuk perhentian yang Anda lakukan, maupun pengisian BBM.
1.1 – Pembuatan Template Format
Mulailah dengan membuat label header pada beberapa kolom kita (saya memulainya dari cell A3 supaya baris 1 bisa saya gunakan sebagai judul) dengan susunan sebagai berikut: `Start Time`, `End Time`, `Start Km`, `End Km`, `Current Place`, `Stop Mode`, dan `Notes`. seperti terlihat pada Gambar 1 berikut:
|
| Gambar 1. Tangkapan Layar dari penyusunan Data Entry pada Trip Log |
Kemudian dari susunan seperti ini, kita konversikan header tersebut menjadi dalam bentuk Table (menggunakan Ctrl+T). Pada contoh disini sebenarnya ini cara yang nyaman bagi saya pribadi, Anda boleh saja tetap menggunakan Range jikalau itu nyaman bagi Anda. Tapi saya akan menyarankan Anda menggunakan Table supaya penyusunan formulanya akan jauh lebih nyaman. Lalu panjangkan jumlah baris pengisiannya supaya dapat memuat data yang akan diisi dengan lebih banyak, dalam contoh disini saya membuatnya menjadi 1000 baris pakai Resize Table. Tak lupa saya juga mengubah Table Name nya–sebut saja namanya "Data_Entry".
1.2 – Pengisian Entri
Dengan tabel entri data seperti ini lalu bagaimana kita mengisinya? Berikut saya contohkan bagaimana pengisiannya.
|
| Gambar 2. Contoh pengisian Data Entry pada Trip Log |
- Untuk pengisian data ‘Start Time’ dan ‘End Time’, pastikan agar format kolom datanya menampilkan date dan time nya secara bersamaan. Untuk melakukan hal ini, Anda perlu mengubah formatnya secara custom, disini saya mencontohkannya dalam format [$-en-ID,1]ddd, d mmm yy, hh:mm AM/PM;@, sehingga pengisian datanya akan terlihat seperti Gambar. Anda blok kolom 'Start Time' dan 'End Time' Anda, kemudian Anda ganti format Numbernya dalam bentuk custom dengan menggunakan More Number Formats..., dan silahkan Anda tuliskan formatnya seperti demikian pada bagian Type. Lalu Anda klik OK.
-
Apabila ini adalah kali pertama Anda mulai mengukur perjalanan
Anda, maka Anda perlu mengosongkan baris pertama setelah header, kecuali 'End Km' sebagai data awal pertamanya. Anda perlu mencatat 'End Km' yang tertera pada odometer kendaraan Anda secara manual, namun kolom 'Start Km' kita isi dengan formula:
=IF(OR(ISTEXT(D4);ISBLANK(D4));"";D4)
Dengan cara seperti ini, 'Start Km' akan mengisi secara otomatis akan Km terakhir yang tertera pada odometer dari perjalanan sebelumnya sebagai Km awal untuk perjalanan saat ini. Disini, kita menggunakan fungsi IF, ISBLANK, dan ISTEXT supaya tabelnya terlihat bersih dan enak dilihat ketika dilakukan pengisian manual tanpa membuat Anda merasa terganggu dengan teks selain angka atau angka 0 ketika 'End Km' memang belum terisi.
- 'Current Place' adalah kolom yang menceritakan tentang tempat terkini Anda memberhentikan perjalanan. Disini Anda mengisi data berupa tempat, sehingga ini sifatnya adalah data kalimat. Anda mungkin bisa saja suatu ketika merasa bahwa pengisian kolom ini dirasa tidak terlalu perlu dan mengosongkannya, dan itu memang tidak akan mempengaruhi perhitungan. Namun alangkah baiknya jika memang data ini sebaiknya diisi supaya riwayat trip log Anda paripurna dan tidak ada yang kosong. Selain itu, tentu Anda juga dapat melacak tempat terakhir di mana Anda mengisi BBM juga jika Anda mengisinya dengan lengkap. Jika pengisian data ini membuat Anda merasa kewalahan, Anda sebetulnya bisa saja melakukan copy paste akan suatu nama tempat/alamatnya: misalnya dari Google Maps.
- 'Stop Mode' adalah data yang kita isi sebagai suatu pengkategorian. Disini, kita mengisinya dengan suatu atribut kategori. Dalam contoh ini, saya menggunakan kata "Parking" dan "Refuelling". Anda dapat saja menambahkan kategori baru sendiri–misalnya "Idle", "Borrowed" ketika kendaraannya memang sedang dipinjam. Namun yang pasti, disini Anda harus memiliki atribut "Refuelling" sebagai tanda bahwa itu adalah kejadian ketika Anda sedang melakukan pengisian BBM dalam perhentiannya. Dan ini nantinya akan menjadi basis kalimat kita untuk mengumpulkan jumlah Km dan jumlah perhitungan konsumsi BBM kita sebagai atribut yang dirujuknya. Nanti saya akan tunjukkan caranya di Langkah 2. Untuk menyederhanakan cara pengisian kolom ini, Anda pakai fitur Data Validation di mana Anda mengganti Validation criteria nya sebagai List dengan source berisi Parking;Refuelling;Idle;Borrowed;etc untuk menyederhanakan pengisian berulang pada cell menjadi menu dropdown sederhana.
- 'Notes' adalah kolom tambahan yang sifatnya bebas dapat Anda gunakan atau Anda biarkan kosong untuk menuliskan segala catatan keterangan tertulis lainnya yang mungkin Anda perlukan.
Langkah 2 – Menyusun Tabel Refuelling Entry
2.1 – Pembuatan Template Format Refuelling Entry
Berikutnya pada sisi kanan dari Tabel Data_Entry, kita tambahkan suatu tabel baru. Ini adalah bagian di mana kita merekam segala bentuk entri ketika kita mengisi BBM dengan detail-detail kolom labelnya berupa ‘No. Refuelling’, ‘Refuelling at Km.’, ‘Date’, ‘Place’, ‘Fuel Type’, ‘Price per Litre’, ‘Fuel Purchased’, ‘Litre’, dan ‘Payment Method’ seperti ditunjukkan pada Gambar 3.
|
| Gambar 3. Refuelling Entry di samping Trip Log Entry |
Perhatikanlah Tabel Refuelling Entry tersebut. Seperti pada Data_Entry, ia juga disusun dalam bentuk Table (menggunakan Ctrl+T) dan diberi nama Refuelling_Entry. Ukurannya juga harus sama dengan ukuran tabel Data_Entry.
Bagaimana cara kita mengisi Refuelling_Entry tersebut? Berikut saya jelaskan satu per satu:
2.2 – Refuelling No.
|
| Gambar 4. Cara Pengisian Kolom 'Refuelling No.' dengan Formula IF dan COUNTIF. |
Oleh karena itu, dalam kolom ini kita isi dengan formula:
=IF(Data_Entry[@[Stop Mode]]="Refuelling";COUNTIF($F$4:F4;"Refuelling");"")
Jadi disinilah kegunaan atribut "Refuelling" yang sebelumnya kita buat di kolom 'Stop Mode' pada Tabel Data_Entry. 'Refuelling No' akan mengisikan penomoran berurutan secara otomatis ketika Anda mengisi kolom 'Stop Mode' dengan atribut Refuelling setiap kali Anda melakukan pengisian BBM berkat permainan absolute reference dan relative reference dalam perintah
COUNTIF($F$4:F4;"Refuelling")pada argumen [value if true]. Dan dia akan mengosongkan isian dengan sendirinya kalau Anda tidak sedang mengisi BBM berkat perintah "" pada argumen [value if false].
2.3 – Refuelling Km
|
| Gambar 5. Cara Pengisian Kolom 'Refuelling at Km' dengan Formula IF. |
Label kolom 'Refuelling at Km' adalah kilometer terakhir yang tertera pada odometer Anda ketika Anda berhenti di tempat pengisian BBM. Jadi angka 'Refuelling at Km' = 'End Km' ketika 'Refuelling'. Nah, tapi karena tentunya tidak semua riwayat perjalanan adalah "refuelling" dan label ini juga terletak di Tabel yang terpisah, maka untuk mengisi kolom ini dengan formula yang serupa dengan sebelumnya, yaitu:
=IF(Data_Entry[@[Stop Mode]]="Refuelling";Data_Entry[@[End Km]];"")
Lagi, berkat atribut "Refuelling" dia akan mengisikan angka Km terakhir sesuai 'End Km' di odometer dengan sendirinya ketika Anda mengisi kolom 'Stop Mode' dengan atribut Refuelling setiap kali pengisian BBM dan akan mengosongkan isiannya sendiri kalau tidak sedang mengisi BBM berkat perintah "" pada argumen [value if false].
2.4 – Refuelling Date
|
| Gambar 6. Cara Pengisian Kolom 'Date' dengan Formula IF. |
Label kolom 'Date' untuk di Tabel Refuelling_Entry ini sengaja saya tambahkan, walaupun sebenarnya pada Tabel Data_Entry sudah ada. Tujuannya sebagai penanda ketika kita kompilasikan seluruh riwayat historis Refuelling di dalam worksheet terpisah nanti. Nah, untuk tanggalnya sendiri ini saya ambil bukan dari 'End Time' Data_Entry, melainkan dari 'Start Time' Data_Entry setelah refuelling dilakukan. Sebab secara logika, tentu perhitungan BBM setelah refuelling dimulai ketika Anda telah mengisi BBM dan berangkat kembali, bukan? Oleh karena itu, disini formulanya adalah:
=IF(Data_Entry[@[Stop Mode]]="Refuelling";A5;"")
Ketika Anda merujuk cell yang tidak sebaris satu sama lain, maka mau tidak mau Anda akan menuliskannya dalam cell nya (A5, A6, A7, dst), bukan dalam bentuk Data_Entry[@[Start Time]].
2.5 – Refuelling Place
|
| Gambar 7. Cara Pengisian Kolom 'Place' dengan Formula IF. |
Label kolom 'Place' juga di Tabel Refuelling_Entry ini sengaja saya tambahkan, walaupun sebenarnya pada Tabel Data_Entry sudah ada. Tujuannya sebagai penanda ketika kita kompilasikan seluruh riwayat historis Refuelling di dalam worksheet terpisah nanti. Cukup Anda ambil dari kolom 'Current Place' pada Tabel Data_Entry dengan formula:
=IF(Data_Entry[@[Stop Mode]]="Refuelling";Data_Entry[@[Current Place]];"")
2.6 – Pengisian Entri Pembelian BBM
|
| Gambar 8. Contoh Pengisian Entri Pembelian BBM Ketika Refuelling |
- Kolom entri 'Fuel Type' adalah data kalimat yang menyatakan jenis Bahan Bakar yang Anda beli. Disini saya menggunakan Pertalite dan Shell Super hanya sebagai contoh saja. Ini berguna untuk Anda mencari tahu apakah dengan jenis BBM tersebut ini mempengaruhi performa Anda atau tidak, sebab Anda akan perlu mengetahui jenis BBM yang paling pas untuk digunakan pada kendaraan Anda, bukan?
- Kolom 'Price per Litre' menyatakan harga per liter dari BBM yang dijual oleh outlet BBM nya, sementara kolom 'Fuel Purchased' adalah harga BBM yang Anda beli. Ini adalah data entri yang wajib Anda isi secara manual ketika mengisi BBM. Ubahlah format cell nya menjadi Accounting supaya lebih mudah dilihat.
-
Bagilah 'Fuel Purchased' dengan 'Price per Litre', maka Anda mendapatkan nilai 'Litre'. Pada kolom 'Litre' masukkan formula:
=IFERROR([@[Fuel Purchased]]/[@[Price per Litre]];"")
Dengan cara ini, Anda tidak perlu merasa sakit kepala dengan munculnya error pembagian #DIV/0! pada cell yang memang sengaja dibuat kosong. Sebab memang dalam tabel ini trip yang tidak termasuk dalam aktivitas pengisian BBM tidak akan dihitung, bukan? - Kolom 'Payment Method' menyatakan metode pembayaran pembelian BBM yang Anda lakukan. Ini dapat membantu Anda untuk melakukan pencatatan keuangan Anda jika Anda perlukan.
Catatan: Anda sebenarnya bisa saja menggabungkan data-data Refuelling_Entry yang sifatnya harus diisi manual pada Tabel Data_Entry sekaligus disatukan saja tanpa harus memisahkannya dalam 2 tabel, jika Anda merasa nyaman dengan Tabel pengisian entri yang besar. Namun dalam contoh ini, saya sengaja memisahkan antara entri perjalanan dan entri dalam konteks pengisian BBM nya.
Langkah 3 – Mengkompilasi Catatan Pengisian BBM dalam Refuelling Logbook
Berikutnya, kita akan mengkompilasi seluruh data pengisian BBM yang telah Anda lakukan dalam worksheet terpisah, di mana data dari tabel Refuelling_Entry kita ambil dan dirapihkan berdasarkan riwayat historisnya. Disini jugalah kita akan membuat basis analisis penggunaan konsumsi BBM kendaraannya.
3.1 – Template Refuelling Logbook
Mulailah dengan membuat label header pada beberapa kolom kita (seperti biasa saya memulainya dari cell A3 supaya baris 1 bisa saya gunakan sebagai judul) dengan susunan sebagai berikut: ‘Refuelling No.’, ‘Date’, ‘Place’, ‘Fuel Type’, ‘Start Km’, ‘End Km’, ‘Total Km’, ‘Litre’, dan ‘Km/L’. seperti terlihat pada Gambar 9 berikut:
|
| Gambar 9. Template Refuelling Logbook |
Kalau Anda perhatikan, pada dasarnya disini kita hanya memanggil kembali data-data yang sudah ada pada Tabel Refuelling_Entry di worksheet Trip_Log saja. Kita menarik data-data mentah dari apa yang sudah kita catat tanpa mengganggu struktur data yang sudah ada, merapihkannya tanpa mengikutsertakan bagian-bagian yang kosong, dan dari sini juga kita lakukan analisis konsumsi BBM nya. Nama Tabel ini saya beri nama 'Refuelling_Logbook' untuk membedakannya dengan 'Refuelling_Entry' yang diisi secara manual.
3.2 – Pengisian Formula Refuelling Logbook
Disini kita hanya perlu menggunakan formula-formula LOOKUP, seperti VLOOKUP, INDEX, atau MATCH. Dalam contoh ini, saya akan memakai VLOOKUP untuk memanggil kembali data-data ‘Date’, ‘Place’, ‘Fuel Type’, ‘Start Km’, ‘End Km’, ‘Total Km’, dan ‘Litre’, seperti terlihat pada Gambar 10 berikut
|
| Gambar 10. Animasi GIF dari penggunaan VLOOKUP untuk memanggil data-data yang sudah ada |
'Refuelling No." digunakan sebagai acuan lookup value yang dibutuhkan oleh formula VLOOKUP. Angkanya sendiri dalam contoh ini saya buat berurutan dari 1-100 pakai teknik Autofill tarik sederhana. Kemudian angka ini diacu dalam formula VLOOKUP sehingga:
-
Kolom 'Date' yang terletak pada kolom ke-3 pada Tabel
Refuelling_Entry kita isi dengan
=VLOOKUP([@[Refuelling No.]];Refuelling_Entry;3;FALSE)
-
Kolom 'Place' yang terletak pada kolom ke-4 pada Tabel Refuelling_Entry kita isi dengan
=VLOOKUP([@[Refuelling No.]];Refuelling_Entry;4;FALSE)
-
Kolom 'Fuel Type' yang terletak pada kolom ke-5 pada Tabel Refuelling_Entry kita isi dengan
=VLOOKUP([@[Refuelling No.]];Refuelling_Entry;5;FALSE)
-
Kolom 'Litre' yang terletak pada kolom ke-8 pada Tabel Refuelling_Entry kita isi dengan
=VLOOKUP([@[Refuelling No.]];Refuelling_Entry;8;FALSE)
Lalu dari mana nilai 'Start Km' dan 'End Km' kita ambil? Bukankah tidak ada kolomnya pada Refuelling_Entry? Nah, nilai ini akan kita tarik dari 'Refuelling Km' pada kolom ke-2 Tabel Refuelling_Entry, masih tetap pakai VLOOKUP. Bedanya adalah:
-
Pada kolom 'Start Km' Anda tulis:
=VLOOKUP([@[Refuelling No.]];Refuelling_Entry;2;FALSE)
-
dan pada kolom 'End Km' Anda tulis:
=VLOOKUP([@[Refuelling No.]]+1;Refuelling_Entry;2;FALSE)
Dengan cara ini, 'Start Km' akan mencerminkan angka Km awal ketika refuelling ke-n, dan 'End Km' akan mencerminkan angka Km ketika refuelling ke-n+1 (alias refuelling berikutnya).
Lalu Anda hitung selisih antara 'End Km' dan 'Start Km',
maka ini akan mencerminkan jumlah jarak yang telah Anda tempuh dalam
sekali pengisian BBM, bukan? Inilah yang kita isikan dalam kolom 'Total Km', yang formulanya kita tulis dengan
=[@[End Km]]-[@[Start Km]]
Dengan demikian, maka tampilan file Excel kita saat ini akan terlihat sebagai berikut:
|
| Gambar 11. Pengisian 'Start Km', 'End Km', dan 'Total Km' pada Refuelling_Logbook |
Terakhir untuk menghitung konsumsi BBM, pada umumnya kita cukup menggunakan rumus classic:
\[ \text{Konsumsi BBM per liter} = \frac{\text{jumlah jarak tempuh}}{\text{jumlah BBM yang digunakan}} \]
Sehingga perhitungan kita untuk Km/L akan menjadi sebagai berikut:
|
| Gambar 12. Perhitungan Konsumsi BBM per Liter |
Tentu saja tampilan dengan adanya error #N/A ketika data ini belum banyak terisi kurang enak dilihat, bukan? Untuk mengatasi masalah tersebut, kita cukup menambahkan fungsi IFNA dan argumen "" pada rumus-rumus VLOOKUP kita, sehingga formulanya menjadi:
-
Pada kolom 'Date' :
=IFNA(VLOOKUP([@[Refuelling No.]];Refuelling_Entry;3;FALSE);"") -
Pada kolom 'Place' :
=IFNA(VLOOKUP([@[Refuelling No.]];Refuelling_Entry;4;FALSE);"") -
Pada kolom 'Fuel Type' :
=IFNA(VLOOKUP([@[Refuelling No.]];Refuelling_Entry;5;FALSE);"") -
Pada kolom 'Start Km' :
=IFNA(VLOOKUP([@[Refuelling No.]];Refuelling_Entry;2;FALSE);"") -
Pada kolom 'End Km' :
=IFNA(VLOOKUP([@[Refuelling No.]]+1;Refuelling_Entry;2;FALSE);"") -
Pada kolom 'Litre' :
=IFNA(VLOOKUP([@[Refuelling No.]];Refuelling_Entry;8;FALSE);"")
Kemudian untuk mencegah error #VALUE! pada perhitungan-perhitungan aritmatika, maka cukup kita tambahkan fungsi IFERROR dan argumen "":
-
Pada kolom 'Total Km', sehingga formulanya menjadi
=IFERROR([@[End Km]]-[@[Start Km]];"") -
Pada kolom 'Km/L', sehingga formulanya menjadi
=IFERROR([@[Total Km]]/[@Litre];"")
Dan inilah tampilan "bersih" yang Anda lihat sekarang.
|
| Gambar 13. Refuelling Logbook yang sudah "dibersihkan" dari potensi error perhitungan |
Refuelling Logbook ini akan menampilkan data-data yang sudah Anda isi di entri dan tergantung dari banyaknya jumlah data riwayat pengisian yang Anda lakukan. Dia akan ter-update mengikuti pengisian refuelling terakhir yang Anda bukukan tentunya.
Dari sini nanti, semakin Anda rajin mencatat entrinya, tentu semakin banyak datanya. Dan semakin banyak datanya, tentu Anda dapat memperhatikan sendiri karakteristik performa kendaraan Anda dan kemudian membuat estimasi konsumsi BBM Anda dalam Km/L. Bisa pakai rata-rata, bisa juga dengan nilai tengahnya, atau bisa juga dari nilai yang paling sering muncul.
Langkah 4 – Analisis Data dengan Statistik
Tadi sudah sempat saya sentil sedikit bahwa untuk menghitung konsumsi BBM, pada umumnya kita cukup menggunakan rumus classic:
\[ \text{Konsumsi BBM per liter} = \frac{\text{jumlah jarak tempuh}}{\text{jumlah BBM yang digunakan}} \]
Mengapa ini saya katakan classic? Rumus tersebut memang demikian dasar perhitungannya, tidak ada yang salah. Masalahnya, bisa saja besok Anda menemukan bahwa hasilnya berbeda, entah lebih besar atau lebih kecil, tergantung Anda membawa dan tergantung seberapa berani Anda mengambil risiko menghabiskan BBM sedapat mungkin, tentu angkanya akan berubah dan tidak ada nilai pasti dong? Makanya saya tidak setuju bila hasil perhitungan dari angka tersebut langsung semata-mata dijadikan sebagai acuan eksaknya hanya dengan sekali mengukur saja, karena performa mesin itu sifatnya dinamis sepanjang penggunaan.
Pada dasarnya, konsumsi BBM pada kendaraan yang Anda gunakan akan tergantung dari:
- Cara Anda mengemudi (dibawa kencang/pelan)
- Kondisi sehat tidaknya kendaraan
- Jenis BBM yang Anda gunakan
- Setelan sistem bahan bakar kendaraan Anda
- dan segala bentuk faktor-faktor lainnya.
Semakin sering Anda mengumpulkan datanya, pasti variasi nilainya akan semakin banyak.
Saya mengetahui hal ini, karena saya sudah konsisten mengisi entrinya sejak 2 tahun yang lalu. Terhitung sejak saat itu sampai hari ini, sudah terkumpul 452 kali saya melakukan refuelling. Kalau saya ambil hanya tahun 2024 saja, sampai saat ini sudah ada 195 kali saya melakukan refuelling.
.png "Steven's Refuelling Logbook 01")
|
| Gambar 14. Riwayat Refuelling Logbook pribadi saya selama tahun 2024 sampai saat artikel ini ditulis |
Kalau datanya sudah sebanyak itu, lalu itu mau diapakan?
Tentu saja data yang sudah usang tidak perlu sampai membuat kita gagal move on, dong. Hehehehe... Kita tidak perlu harus sampai menganalisis populasi data terkumpul itu di mana terkadang variasi datanya ada yang terlampau aneh.
Misal beberapa bulan lalu atau setahun yang lalu mungkin kebetulan performa kendaraan Anda sedang kurang sehat secara aktual, lalu kemudian setelah dilakukan maintenance, repair, dan overhaul, kemudian Anda jadi lebih merasa percaya diri untuk menguji limit performa kendaraan Anda, ternyata Anda menemukan bahwa nilai rata-ratanya kemudian malah semakin lebih tinggi daripada periode sebelumnya. Makanya saya katakan perhitungan ini seharusnya dibuat dinamis, bukan terpaku secara statis hanya dalam sekali analisis saja. Data yang sudah usang silahkan disimpan, tetapi analisisnya sebaiknya dihitung secara periodik.
Caranya: ambil 100 sampel dari yang paling terbaru. Lalu kemudian data tersebut saya olah menggunakan analisis statistik – pakai histogram, descriptive statistics, maupun diagram lonceng. Analisis seperti ini memerlukan paling tidak 50 data terkumpul. Disini saya membuatnya 100 karena memang datanya sudah cukup banyak terkumpul dan saya ingin perhitungannya lebih akurat.
4.1 – Mengkompilasi 100 Data Terbaru
Untuk melakukan ini, kita harus mengubah sedikit referensi 'Refuelling No.' dan menambahkan kolom baru pada Tabel Refuelling_Logbook.
Pastikanlah ukuran Tabel Refuelling_Logbook Anda memuat 100 data yang akan ditarik, dan kemudian pada column 'Refuelling No.' paling terakhirnya Anda tulis:
=MAX(Refuelling_Entry[No. Refuelling])-1
lalu kemudian pada cell di atasnya Anda tuliskan:
=A103-1
dan Anda lakukan penarikan fill handle formula itu ke atas sampai cell pertamanya, kira-kira begini:
.png "Steven's Refuelling Logbook 02")
|
| Gambar 15a. Penarikan 100 Data Refuelling Terakhir (tampilan versi Show Formulas) |
.png "Steven's Refuelling Logbook 03")
|
| Gambar 15b. Penarikan 100 Data Refuelling Terakhir (tampilan versi biasa) |
Kemudian, pada sisi kanan kolom 'Refuelling No.' tambahkan kolom baru, beri nama 'Sampling No.', dan isilah dengan nomor dari 1-100 secara berurutan dari atas ke bawah sebagai berikut:
.png "Steven's Refuelling Logbook 04")
|
| Gambar 16. Penambahan Kolom 'Sampling No.' Pada Tabel Refuelling_Logbook |
4.2 – Analisis Data dalam worksheet Fuel Statistik
Buatlah worksheet baru, kemudian beri nama worksheet tersebut sebagai Fuel_Statistics. Disini, kita akan menarik 100 sampel data Km/L yang hendak dianalisa tersebut – lagi, dengan menarik datanya pakai VLOOKUP berdasarkan 'Sampling No.' yang baru saja dibuat. Kemudian kita lakukan analisis menggunakan descriptive statistics, termasuk membuat histogram dan kurva lonceng.
Untuk label kolom yang akan kita susun, buatlah kolom 'Sampling No.', 'Km/L', 'Norm.Dist', 'Mean', 'Median', 'Mode', 'Lowest Record', dan 'Highest Record', buatlah Tabel untuk memuat 100 data (menggunakan Ctrl+T), dan namailah Tabel tersebut sebagai Fuel_Statistical_Analysis. Ilustrasinya seperti ditunjukkan dalam Gambar berikut.
.png "Fuel Statistical Analysis 01")
|
| Gambar 17. Tabel Fuel_Statistical_Analysis pada worksheet Fuel_Statistics |
Isilah setiap kolom dengan cara sebagai berikut:
- Kolom 'Sampling No.' diisi nomor dari 1-100
-
Kolom 'Km/L' diisi dengan formula
=VLOOKUP([@[Sampling No.]];Refuelling_Logbook[[Sampling No,]:[Km/L]];9;FALSE)untuk menarik data 'Km/L' dari Refuelling_Logbook. Kemudian, gunakan Filter Button untuk membuat data ini berurutan dari yang paling kecil sampai yang paling besar menggunakan Sort Smallest to Largest. -
Kolom 'Norm.Dist' diisi dengan formula
=NORM.DIST([@[Km/L]];AVERAGE([Km/L]);STDEV.S([Km/L]);FALSE)Formula ini digunakan untuk membuat grafik kurva lonceng. -
Kolom 'Mean' adalah nilai rata-rata dari data yang dianalisis.
Cukup gunakan formula
=AVERAGE([Km/L]) -
Kolom 'Median' adalah nilai tengah dari data yang dianalisis.
Anda dapat gunakan formula
=MEDIAN([Km/L])atau bisa juga Anda gunakan formula
=QUARTILE.INC([Km/L];2)
-
Kolom 'Mode' adalah nilai yang paling sering muncul dari data
yang dianalisis. Anda dapat gunakan formula
=MODE([Km/L]) -
Kolom 'Lowest Record' adalah nilai terendah dari sampel data
terekam yang dianalisis. Cukup gunakan formula
=MIN([Km/L]) -
Kolom 'Highest Record' adalah nilai tertinggi dari sampel data
terekam yang dianalisis. Cukup gunakan formula
=MAX([Km/L])
.png "Fuel Statistical Analysis 02")
|
| Gambar 18. Formula Dalam Tabel Fuel_Statistical_Analysis (tampilan versi Show Formulas) |
Agar tampilannya membentuk grafik kurva lonceng yang sempurna, syaratnya adalah data 'Km/L' harus berurutan secara rapih, dan itulah alasannya mengapa sebelumnya kita menggunakan Sort Smallest to Largest. Jika Anda ingin membayangkan mengapa bisa demikian, gunakanlah Conditional Formatting pada kolom 'Km/L' dan 'Norm.Dist' dalam bentuk Data Bars dan perhatikanlah ciri khas bentuk dari kedua kolom tersebut. Pasti Anda akan langsung terbayang bagaimana data 'Km/L' yang tersusun berurutan mempengaruhi nilai dari 'Norm.Dist' nya yang menyatakan distribusi data tersebut.
.png "Fuel Statistical Analysis 03")
|
| Gambar 19. Tampilan Tabel Fuel_Statistical_Analysis yang sudah diisi formula (tampilan versi biasa) |
Setelah itu, select lah kolom 'Km/L' dan 'Norm.Dist', kemudian buatlah Line Chart berdasarkan kedua kolom tersebut pada Tab Insert.
.png "Fuel Statistical Analysis 04")
|
| Gambar 20. Grafik kurva lonceng yang terbentuk dari 'Km/L' sebagai sumbu X dan 'Norm.Dist' sebagai sumbu Y |
Untuk melengkapi grafik tersebut, Anda harus menyunting grafik tersebut secara manual melalui Select Data... dengan mengklik kanan grafik tersebut. Pada Legend Entries (Series) nya, Anda Add setiap data-data pada kolom 'Mean', 'Median', 'Mode', 'Lowest Record', dan 'Highest Record' yang sesuai, dengan mengacukan Series Y values nya terhadap data 'Norm.Dist'. Dicontohkan disini, bagaimana data 'Highest Record' dibuatkan series nya terhadap 'Norm.Dist' sebagai sumbu Y nya, sehingga membentuk garis vertikal yang mencerminkan batas maksimum nilai 'Km/L' pada data yang terekam. Demikian juga untuk parameter lainnya seperti 'Mean', 'Median', 'Mode', dan 'Lowest Record', series mereka masing-masing akan tercermin dalam bentuk garis vertikal, sehingga terbentuk grafik kurva lonceng yang biasa Anda temukan ketika menganalisis data statistik, bukan?
.png "Fuel Statistical Analysis 05")
|
| Gambar 21. Grafik kurva lonceng yang dilengkapi dengan parameter-parameter statistik komprehensifnya |
Ketika melakukan analisis data statistik rasanya tentu kurang lengkap jika kurva lonceng tidak disertai dengan histogram, bukan. Sayangnya Excel tidak memiliki fitur untuk mengkombinasikan kurva lonceng dan histogram sekaligus – kurva lonceng pada dasarnya dibuat menggunakan Line/Scatter Charts dalam Excel, sementara Histogram sudah menjadi Chart khusus tersendiri (dan itu bukan termasuk Bar Chart). Jadi kalaupun Anda mau melihat bagaimana sebaran data normalnya dalam histogram, Anda hanya perlu membuat satu grafik histogram secara terpisah dan pilihlah data 'Km/L' sebagai data yang akan di-plot. Grafiknya akan otomatis membentuk histogram dengan sendirinya beserta dengan bagaimana sebaran datanya terbentuk berdasarkan jumlah bins secara default. Dalam contoh disini, saya mengubah default bins nya menjadi 9 mengikuti aturan Sturges dan nilainya dibuat ganjil.
.png "Fuel Statistical Analysis 06")
|
| Gambar 22. Grafik kurva lonceng dan Grafik Histogram tentang sebaran data Konsumsi BBM kendaraan saya |
Terakhir untuk "mempercantik" analisis dalam worksheet ini selain saya sunting beberapa formatting grafiknya, tak lupa saya tambahkan beberapa descriptive statistics berupa tulisan dan angka-angkanya sendiri sebagai berikut:
|
| Gambar 23. Tampilan Akhir Dasbor Fuel_Statistic |
Saya melengkapi dengan keterangan tentang 'Mean', 'Median', 'Mode', 'Range', 'Variance', 'Standard Deviation', 'Lowest Record', 'Highest Record', 'Kurtosis', hingga 'Skewness' selayaknya perhitungan analisis statistik sebagaimana mestinya, yang angkanya kita peroleh melalui formula berikut:
-
'Mean' sebagai nilai rata-rata dari data Km/L:
=AVERAGE(Fuel_Statistical_Analysis[Km/L]) -
'Median' sebagai nilai tengah dari data Km/L:
=MEDIAN(Fuel_Statistical_Analysis[Km/L]) -
'Mode' sebagai nilai yang paling sering muncul dari
data Km/L:
=MODE(Fuel_Statistical_Analysis[Km/L]) -
'Range' sebagai jangkauan dari sampel data Km/L:
=MAX(Fuel_Statistical_Analysis[Km/L])-MIN(Fuel_Statistical_Analysis[Km/L]) -
'Variance' untuk data sampel kita pakai
=VAR.S(Fuel_Statistical_Analysis[Km/L]) -
'Standard Deviation' untuk data sampel kita pakai
=STDEV.S(Fuel_Statistical_Analysis[Km/L]) -
'Highest Record' cukup pakai
=MAX(Fuel_Statistical_Analysis[Km/L]) -
'Lowest Record' cukup pakai
=MIN(Fuel_Statistical_Analysis[Km/L]) -
'Kurtosis' yang menyatakan "kekurusan" kurva lonceng:
=KURT(Fuel_Statistical_Analysis[Km/L]) -
'Skewness' yang menyatakan kemiringan kurva lonceng:
=SKEW(Fuel_Statistical_Analysis[Km/L])
Ini semua adalah parameter-parameter yang pada umumnya diperhatikan dalam analisis data statistik.
Sebagai info tambahan, Anda mungkin juga tertarik menambahkan garis series yang menjelaskan tentang batas-batas sebaran data dalam ±3σ, dan itu bisa-bisa saja Anda tambahkan kolom pada Tabel dan grafik kurva loncengnya, namun nanti datanya akan lebih banyak sebab Anda perlu memisah setiap -1σ, +1σ, -2σ, +2σ, -3σ, dan +3σ masing-masing. Disini tidak saya contohkan.
4.3 – Interpretasi Analisis Data
Apa yang secara praktis dapat saya pelajari dari analisis tersebut?
Dari sini saya bisa mengetahui bahwa dari 100 pengisian BBM terakhir:
- Ternyata baik Mean, Median, dan Modus berada pada harga yang tidak berbeda, yaitu di sekitar 58,8–59,2 Km/L.
- Ternyata saya pernah 1–3 kali melakukan refuelling BBM lebih cepat dari sewajarnya, dan pernah 1–3 kali mendapatkan record yang ekstrem pula setelahnya hingga melampaui record Km/L yang wajar. Dugaan saya sebab saya ingat pada waktu itu memang saya pernah mengisi BBM lebih awal untuk jaga-jaga berkendara jarak jauh, lalu kemudian setelahnya saya sengaja habiskan lagi sampai limitnya. Itulah sebabnya dalam perhitungan data ini menjadi outlier alias data yang beda sendiri. Dan ini mempengaruhi range yang cukup besar pula.
- Ternyata kurva lonceng yang menyatakan distribusi normalnya membentuk kurva lonceng yang normal sebagaimana wajarnya. Ini tercermin dari nilai kurtosisnya yang tidak tumpul (di bawah nilai -3), dan nilai kelancipannya pun juga berada dalam angka yang wajar (di antara 0–3). Kemudian nilai kemiringannya pun juga masih berada dalam angka yang wajar dan masih dianggap simetrik (antara -0,5 s/d 0,5), tidak condong miring ke kiri dan ke kanan pula.
Walaupun sebaran datanya menunjukkan range yang cukup besar, namun karena distribusi normal menunjukkan indikasi yang wajar (dari kurtosis maupun skewness) maupun frekuensi data yang muncul pada histogram menunjukkan nilai yang konsisten 36 kali berturut-turut menghasilkan nilai yang sama (58–63 Km/L), maka saya tidak perlu merasa khawatir berlebihan bahwa perhitungan saya tidak akan akurat ketika kemudian dijadikan bahan prediksi.
Kecenderungan tengah (mean, median, dan modus) juga terletak pada harga yang sama. Maka dengan demikian saya dapat merasa yakin bahwa konsumsi BBM per liter pada kendaraan saya adalah 59 Km/L (kalau angkanya mau saya bulatkan), dan ini bisa saya jadikan acuan untuk memprediksi perjalanan saya kedepannya saya akan memerlukan BBM sekian liter, dana yang diperlukan, maupun estimasi berapa jarak tempuh yang dapat saya peroleh.
Langkah 5 – Implementasi Prediksi BBM
Lalu implementasi Prediksi BBM nya sendiri bagaimana dilakukannya?
Oh, gampang. Hehehehe...
Cukup Anda tambahkan di worksheet Trip_Log yang akan sering Anda buka setiap hari. Disini kita akan menambahkan suatu sistem monitoring BBM untuk membantu kita memantau kondisi BBM terkini kita maupun prediksinya – seakan-akan ini menjadi suatu dasbor tambahan kendaraan kita sendiri. Tujuannya saya buat seperti ini supaya kita tidak perlu harus repot pindah-pindah worksheet untuk memantaunya – terutama ketika file ini sedang dipakai di perjalanan via ponsel.
5.1 – Penambahan Monitor Prediksi di Trip Log
Berikut saya contohkan pembuatan Fuel Monitoring sederhananya.
|
| Gambar 24. Trip Log yang dilengkapi dengan sistem Monitoring BBM sederhana |
- Terkait pengisian BBM terbaru: 'Latest Refuelling' (pengisian BBM terbaru), 'at Km.' (Km ketika mengisi BBM terbaru), 'at Gas Station' (tempat mengisi BBM terbaru), dan 'Litre' (kuantitas BBM pada pengisian terbaru).
- 'Average Km/L' (Rata-rata konsumsi BBM per Liter), yang angkanya telah kita peroleh melalui analisis statistik tadi
- 'Current Km' (Km terkini yang tertera pada odometer kendaraan), 'Next Refuelling Prediction at' (prediksi bahwa pada Km sekian maka saya sudah harus mengisi BBM lagi), dan 'Your Current Fuel Indicator' (untuk indikator visualnya, di mana saya tambahkan Conditional Formatting kemudian dinyatakan dalam sisa Km yang kira-kira masih sanggup dan persentase)
Bagaimana angka itu bisa muncul? Tentu saja pakai formula. Hehehehe... Oke, seperti ini saya buat:
- Parameter-parameter tentang pengisian BBM terbaru saya ambil dari Tabel Refuelling_Entry, seperti biasa kita memanfaatkan permainan VLOOKUP dan MAX:
-
'Latest Refuelling'
=VLOOKUP(MAX(Refuelling_Entry[No. Refuelling]);Refuelling_Entry;3;FALSE)
-
'at Km.'
=VLOOKUP(MAX(Refuelling_Entry[No. Refuelling]);Refuelling_Entry;2;FALSE) -
'at Gas Station'
=VLOOKUP(MAX(Refuelling_Entry[No. Refuelling]);Refuelling_Entry;4;FALSE) -
'Litre'
=VLOOKUP(MAX(Refuelling_Entry[No. Refuelling]);Refuelling_Entry;8;FALSE) -
Untuk 'Average Km' kita ambil dari perhitungan rata-rata data pada worksheet Fuel Statistics saja.
=AVERAGE(Fuel_Statistical_Analysis[Km/L]) -
Untuk 'Current Km' ambil dari kolom 'End Km' pada tabel Data_Entry
=MAX(Data_Entry[End Km])dengan cara ini, Km terakhir yang Anda catat pada entri juga akan ikut ditampilkan secara otomatis pada sistem monitoring ini. Seiring penggunaan kendaraan tentu saja angka Km nya bertambah, bukan? Itulah alasannya kita menggunakan MAX. - Untuk 'Your Current Fuel Indicator' pada dasarnya sederhana:
-
Anda cukup menyatakan selisih antara 'Next Refuelling Prediction at' dengan 'Current Km'
=$B$10-$B$9 -
dan untuk menyatakan persentasenya Anda cukup menyatakan persentase
antara selisih antara 'Next Refuelling Prediction at' dengan 'Current Km' dibagi dengan selisih antara 'Next Refuelling Prediction at'
dengan 'Latest Refuelling'
=($B$10-$B$9)/($B$10-$B$3)
5.2 – Perhitungan 'Next Refuelling Prediction'
Nah, permasalahannya adalah: bagaimana 'Next Refuelling Prediction at' dihitung?
Mungkin terlintas di benak Anda bahwa perhitungan 'Next Refuelling Prediction at' cukup sederhana hanya dengan menjumlahkan Km ketika mengisi BBM terbaru 'at Km.' (B3) dengan hasil perkalian 'Litre' (B5) dan 'Average Km' (B7) saja, sehingga dapat kita tulis:
=$B$3+$B$5*$B$7
seperti ini saja, bukan?
😊
Saya pernah sempat berpikir seperti itu juga, dan akhirnya saya menyadari bahwa ternyata cara seperti itu tidak cukup dan masih bisa diimprovisasi. Saat itu saya menyadari bahwa suatu ketika saya mengisi BBM sementara BBM saya masih ada dengan isinya yang cukup banyak, ternyata sisanya itu tidak ikut terhitung dengan perhitungan tersebut. Dan lucunya setelah saya mengisi BBM dan melakukan pencatatan kembali, ternyata perhitungannya minus jauh, padahal BBMnya jelas-jelas masih ada banyak berkat sisa BBM sebelumnya yang tidak terhitung itu. Hehehehe...
Disini, saya menyadari bahwa pada saat pengisian BBM kita lakukan, akan ada 2 kemungkinan yang akan terjadi:
- Dapat saja angka 'Current Km' melebihi angka prediksi kita ('Next Refuelling Prediction at') – ini biasanya terjadi bila data Anda masih belum terlalu banyak, dan Anda masih penasaran ingin coba-coba apakah limit kendaraannya dapat menembus batas prediksi atau tidak dengan mengambil risiko kemungkinan dapat mogok di tengah jalan. Dalam kasus ini, 'Your Current Fuel Indicator' menunjukkan indikator minus (-), sebab 'Current Km' melampaui angka 'Next Refuelling Prediction at' di mana Anda akan merasa takjub bahwa ternyata kendaraan Anda bisa melebihi limit prediksi.
- Dapat saja ternyata Anda malah melakukan refuelling di tengah perjalanan walaupun 'Current Km' masih belum mencapai batas yang diberitahukan oleh prediksi 'Next Refuelling Prediction at'. Jadi walaupun BBM belum habis baik secara perhitungan di monitor maupun secara aktual, Anda tetap melakukan pengisian – entah karena satu dan lain hal. Dalam kasus ini, 'Your Current Fuel Indicator' menunjukkan indikator plus (+), sebab 'Current Km' masih di bawah angka 'Next Refuelling Prediction at', di mana itu artinya masih ada sisa BBM yang belum terkonsumsi.
Coba kalau Anda masih menggunakan formula seperti itu dalam kondisi 2 tersebut. Pasti Anda akan menemukan bahwa angka yang terhitung tidak termasuk sisa BBM yang sebenarnya masih ada (dan jumlahnya boleh saja masih terbilang banyak). Formula seperti itu hanya menghitung dari Km sejak pengisian BBM terkini 'at Km.' Anda ditambah dengan hasil perkalian 'Average Km' dan jumlah 'Litre' BBM yang Anda beli saja, tapi tidak menghitung sisanya.
Oleh karena itu, formula untuk menggambarkan 'Next Refuelling Prediction at' tersebut masih dapat kita improvisasi lagi, supaya baik dalam kedua kasus tersebut ia tetap dapat menggambarkan secara akurat indikator BBM nya tanpa terlihat menyimpang terlalu jauh.
Karena ada 2 kondisi seperti itu, maka di dalam penyusunan perhitungan 'Next Refuelling Prediction at' ini kita sebaiknya menggunakan formula IF, di mana:
- Dalam kondisi bila Performa Aktual < Performa Prediksi, maka 'Next Refuelling Prediction at' harus menggambarkan hasil penjumlahan 'at Km.' (Km ketika pengisian terkini) dengan hasil perkalian 'Average Km' dan jumlah 'Litre', juga ditambah dengan selisih antara Performa Aktual dengan Performa Prediksi – alias sisa BBM yang masih tersisa padanya. dan
- Dalam kondisi bila Performa Aktual ≥ Performa Prediksi, maka 'Next Refuelling Prediction at' cukup hanya perlu menggambarkan penjumlahan 'at Km.' (Km ketika pengisian terkini) dengan hasil perkalian 'Average Km' dan jumlah 'Litre' nya saja.
Nah, bagaimana kita akan mengimplementasikan formula dalam kondisi tersebut?
Penjelasan ini akan sedikit matematis, dan seperti biasa saya tidak segera melompat kepada kesimpulan. Saya akan mencoba menjelaskan bertahap demi bertahap, sebab argumennya tidak terlihat sesederhana syaratnya.
5.2.1 – Penjelasan Matematis Perhitungan 'Next Refuelling Prediction'
- JIKA Performa Aktual yang diperoleh dari sejak pengisian BBM sebelumnya hingga ketika Anda mengisi BBM lagi LEBIH KECIL dari Prediksi Performa Anda sebelumnya (yang nilainya diperoleh dengan mengkalikan Konsumsi BBM per Liter Rata-Rata dengan jumlah Liter BBM yang Anda beli pada pengisian BBM sebelumnya), MAKA Km Prediksi Pengisian Bahan Bakar Berikutnya (NRP) dihitung dengan menjumlahkan Km ketika Anda mengisi BBM terakhir (Km Awal) ditambah Prediksi Performa Anda saat ini (yang nilainya diperoleh dengan mengalikan Konsumsi BBM per Liter Rata-Rata dengan jumlah Liter BBM yang Anda beli saat ini) ditambah Bahan Bakar Tersisa (selisih antara Prediksi Performa Sebelumnya dan Total Km Aktual yang diperoleh dari pengisian BBM sebelumnya hingga ketika Anda mengisi BBM lagi).
- Tapi JIKA Performa Aktual yang diperoleh dari sejak pengisian BBM sebelumnya hingga ketika Anda mengisi BBM lagi LEBIH BESAR dari Prediksi Performa Anda sebelumnya (yang nilainya diperoleh dengan mengkalikan Konsumsi BBM per Liter Rata-Rata dengan jumlah Liter BBM yang Anda beli pada pengisian BBM sebelumnya), MAKA Km Prediksi Pengisian Bahan Bakar Berikutnya (NRP) cukup dihitung dengan menjumlahkan Km ketika Anda mengisi BBM terakhir (Km Awal) ditambah Prediksi Performa Anda saat ini (yang nilainya diperoleh dengan mengalikan Konsumsi BBM per Liter Rata-Rata dengan jumlah Liter BBM yang Anda beli saat ini) saja.
Terlalu panjang, bukan? Hehe... Mari kita bedah variabel-variabel tersebut pelan-pelan:
- Pada penyusunan 'Next Refuelling Prediction' yang memerlukan variabel antara data variabel terkini dan data variabel sebelumnya untuk dimasukkan ke dalam perhitungan, maka Latest Refuelling ini secara matematis dapat kita anggap sebagai data ke-n. Dengan demikian, kita dapat mengekspresikan:
- variabel terkini secara matematis dalam notasi: \( \text{(n)} \)
- dan variabel sebelumnya dalam notasi: \( \text{(n-1)} \)
- Prediksi Performa adalah perkiraan dari performa yang dapat Anda capai berdasarkan jumlah 'Litre' BBM yang Anda beli ketika refuelling dikalikan dengan estimasi 'Average Km' hasil analisis Anda, yang dapat kita terjemahkan secara matematis (misal saya notasikan dengan PPREDICTION.) sebagai: \[ \text{P}_\text{PREDICTION.} = \text{Ltr} × \text{Km/Ltr}_\text{(AVG)} \]
- Performa Aktual adalah performa Km hasil rekaman pencatatan Anda sehari-hari yang dihitung sejak pengisian BBM sebelumnya hingga pengisian BBM terkini. Kalau di dalam worksheet Refuelling_Logbook dia tidak lain adalah 'Total Km' hasil perhitungan 'End Km' dikurang 'Start Km'. Ini dapat kita terjemahkan secara matematis (misal saya notasikan dengan PACTUAL.) sebagai: \[ \text{P}_\text{ACTUAL. (n-1)} = \text{'at Km'}_\text{(n)} - \text{'at Km'}_\text{(n-1)} \]
Sehingga argumen yang akan kita bangun pada perhitungan 'Next Refuelling Prediction' ini pada fungsi IF bisa kita terjemahkan bahwa:
- JIKA Performa Aktual yang diperoleh dari sejak pengisian BBM sebelumnya hingga ketika Anda mengisi BBM lagi LEBIH KECIL dari Prediksi Performa Anda sebelumnya: \[ \text{P}_\text{ACTUAL. (n-1)} < \text{P}_\text{PREDICTION. (n-1)} \]MAKA 'Next Refuelling Prediction' (NRP) dihitung dengan menjumlahkan Km ketika Anda mengisi BBM terakhir (Km Awal) ditambah Prediksi Performa Anda saat ini ditambah Bahan Bakar Tersisa: \[ \text{NRP} = \text{'at Km'}_\text{(n)} + ( \text{Ltr}_\text{(n)} × \text{Km/Ltr}_\text{(AVG)} ) + (\text{P}_\text{PREDICTION. (n-1)} - \text{P}_\text{ACTUAL. (n-1)}) \]
- Namun JIKA Performa Aktual yang diperoleh dari sejak pengisian BBM sebelumnya hingga ketika Anda mengisi BBM lagi LEBIH BESAR dari Prediksi Performa Anda sebelumnya: \[ \text{P}_\text{ACTUAL. (n-1)} ≥ \text{P}_\text{PREDICTION. (n-1)} \]MAKA 'Next Refuelling Prediction' (NRP) cukup dihitung dengan menjumlahkan Km ketika Anda mengisi BBM terakhir (Km Awal) ditambah Prediksi Performa Anda saat ini saja: \[ \text{NRP} = \text{'at Km'}_\text{(n)} + ( \text{Ltr}_\text{(n)} × \text{Km/Ltr}_\text{(AVG)} ) \]
Dalam bentuk ekspresi matematis yang lebih formal, ini ditulis:
\[ \text{NRP} = \begin{cases} \text{'at Km'}_\text{(n)} + ( \text{Ltr}_\text{(n)} × \text{Km/Ltr}_\text{(AVG)} ) + (\text{P}_\text{PREDICTION. (n-1)} - \text{P}_\text{ACTUAL. (n-1)}), & \text{if } \text{P}_\text{ACTUAL. (n-1)} < \text{P}_\text{PREDICTION. (n-1)} \\ \text{'at Km'}_\text{(n)} + ( \text{Ltr}_\text{(n)} × \text{Km/Ltr}_\text{(AVG)} ), & \text{if } \text{P}_\text{ACTUAL. (n-1)} ≥ \text{P}_\text{PREDICTION. (n-1)} \end{cases} \]
Dapat kita perhatikan pada rumus tersebut bahwa pada dasarnya Anda hanya memerlukan variabel-variabel berupa:
- \( \text{'at Km'}_\text{(n)} \) – Km ketika Refuelling terkini, atau 'at Km' pada Latest Refuelling
- \( \text{'at Km'}_\text{(n-1)} \) – Km ketika Refuelling sebelum terkini
- \( \text{Ltr}_\text{(n)} \) – jumlah Litre BBM yang Anda beli ketika Refuelling terkini
- \( \text{Ltr}_\text{(n-1)} \) – jumlah Litre BBM yang Anda beli ketika Refuelling sebelum terkini
- \( \text{Km/L}_\text{(AVG)} \) – rata-rata konsumsi BBM per Litre dari hasil analisis statistik kita.
untuk kita masukkan ke dalam argumen Excel kita, Sebab:
- \( \text{P}_\text{ACTUAL (n-1)} \) – Performa Aktual yang diperoleh dari sejak pengisian BBM sebelumnya hingga ketika Anda mengisi BBM lagipada dasarnya adalah \( \text{P}_\text{ACTUAL. (n-1)} = \text{'at Km'}_\text{(n)} - \text{'at Km'}_\text{(n-1)} \), dan
- \( \text{P}_\text{PREDICTION (n-1)} \) – Prediksi Performa Anda sebelumnya pada dasarnya adalah \( \text{P}_\text{PREDICTION (n-1)} = \text{Ltr}_\text{(n-1)} × \text{Km/Ltr}_\text{(AVG)} \)
5.2.2 – Implementasi Perhitungan 'Next Refuelling Prediction' di Excel
Sekarang mari kita terjemahkan argumen tersebut ke dalam bentuk formula Excel nya.
Latest Refuelling menyatakan pengisian BBM paling terkini yang Anda lakukan. Tentu saja ini sifatnya akan berubah seiring pengisian BBM yang Anda lakukan, bukan? Karena kita sudah membuat penomoran otomatis pada kolom 'No. Refuelling', maka kita cukup mendefinisikan kondisi Latest Refuelling \( \text{(n)} \) ini sebagai nilai maksimal pada kolom 'No. Refuelling' saja.
MAX(Refuelling_Entry[No. Refuelling])Dan untuk menyatakan kondisi sebelum terkini \( \text{(n-1)} \) nya, cukup kita tambahkan -1 pada argumen tersebut:
MAX(Refuelling_Entry[No. Refuelling])-1Sehingga ketika seperti biasa kita gunakan formula VLOOKUP:
- Untuk angka \( \text{'at Km'}_\text{(n)} \) – Km ketika Refuelling terkini, kita tulis
VLOOKUP(MAX(Refuelling_Entry[No. Refuelling]);Refuelling_Entry;2;FALSE) - Untuk angka \( \text{'at Km'}_\text{(n-1)} \) – Km ketika Refuelling sebelum terkini, kita tulis
VLOOKUP(MAX(Refuelling_Entry[No. Refuelling])-1;Refuelling_Entry;2;FALSE)agar [lookup_value] yang dibaca oleh fungsi VLOOKUP menerjemahkannya sebagai angka No. Refuelling sebelum yang terkini. - Untuk angka \( \text{Ltr}_\text{(n)} \) – jumlah Litre BBM yang Anda beli ketika Refuelling terkini, kita tulis
VLOOKUP(MAX(Refuelling_Entry[No. Refuelling]);Refuelling_Entry;8;FALSE) - Untuk angka \( \text{Ltr}_\text{(n-1)} \) – jumlah Litre BBM yang Anda beli ketika Refuelling sebelum terkini, kita tulis
VLOOKUP(MAX(Refuelling_Entry[No. Refuelling])-1;Refuelling_Entry;8;FALSE)agar [lookup_value] yang dibaca oleh fungsi VLOOKUP menerjemahkannya sebagai angka No. Refuelling sebelum yang terkini. - Untuk angka \( \text{Km/L}_\text{(AVG)} \) – rata-rata konsumsi BBM per Litre dari hasil analisis statistik kita, cukup ambil dari:
AVERAGE(Fuel_Statistical_Analysis[Km/L])
Fungsi IF di Excel memiliki sintaks:
=IF(logical_test; [value_if_true]; [value_if_false])
Untuk menerjemahkan rumus \[ \text{NRP} = \begin{cases} \text{'at Km'}_\text{(n)} + ( \text{Ltr}_\text{(n)} × \text{Km/Ltr}_\text{(AVG)} ) + (\text{P}_\text{PREDICTION. (n-1)} - \text{P}_\text{ACTUAL. (n-1)}), & \text{if } \text{P}_\text{ACTUAL. (n-1)} < \text{P}_\text{PREDICTION. (n-1)} \\ \text{'at Km'}_\text{(n)} + ( \text{Ltr}_\text{(n)} × \text{Km/Ltr}_\text{(AVG)} ), & \text{if } \text{P}_\text{ACTUAL. (n-1)} ≥ \text{P}_\text{PREDICTION. (n-1)} \end{cases} \] tersebut ke dalam sintaks Excel, maka kita dapat menggunakan kondisi \( \text{if } \text{P}_\text{ACTUAL. (n-1)} < \text{P}_\text{PREDICTION. (n-1)} \), di mana:
- pada argumen logical_test yang menyatakan kondisi \[ \begin{align*} \text{if } \text{P}_\text{ACTUAL. (n-1)} &< \text{P}_\text{PREDICTION. (n-1)} \\ \text{if } \text{'at Km'}_\text{(n)} - \text{'at Km'}_\text{(n-1)} &< \text{Ltr}_\text{(n-1)} × \text{Km/Ltr}_\text{(AVG)} \end{align*} \] dapat kita tulis sintaksnya sebagai:
VLOOKUP(MAX(Refuelling_Entry[No. Refuelling]);Refuelling_Entry;2;FALSE) - VLOOKUP(MAX(Refuelling_Entry[No. Refuelling])-1;Refuelling_Entry;2;FALSE) < VLOOKUP(MAX(Refuelling_Entry[No. Refuelling])-1;Refuelling_Entry;8;FALSE) * AVERAGE(Fuel_Statistical_Analysis[Km/L]) - pada argumen [value_if_true] yang menyatakan kondisi \[ \begin{align*} \text{NRP} &= \text{'at Km'}_\text{(n)} + ( \text{Ltr}_\text{(n)} × \text{Km/Ltr}_\text{(AVG)} ) + (\text{P}_\text{PREDICTION. (n-1)} - \text{P}_\text{ACTUAL. (n-1)}) \\ &= \text{'at Km'}_\text{(n)} + ( \text{Ltr}_\text{(n)} × \text{Km/Ltr}_\text{(AVG)} ) + ((\text{Ltr}_\text{(n-1)} × \text{Km/Ltr}_\text{(AVG)}) - (\text{'at Km'}_\text{(n)} - \text{'at Km'}_\text{(n-1)})) \end{align*} \] nya dapat kita tulis:
VLOOKUP(MAX(Refuelling_Entry[No. Refuelling]);Refuelling_Entry;2;FALSE) + (VLOOKUP(MAX(Refuelling_Entry[No. Refuelling]);Refuelling_Entry;8;FALSE) * AVERAGE(Fuel_Statistical_Analysis[Km/L])) + ((VLOOKUP(MAX(Refuelling_Entry[No. Refuelling])-1;Refuelling_Entry;8;FALSE) * AVERAGE(Fuel_Statistical_Analysis[Km/L])) - (VLOOKUP(MAX(Refuelling_Entry[No. Refuelling]);Refuelling_Entry;2;FALSE) - VLOOKUP(MAX(Refuelling_Entry[No. Refuelling])-1;Refuelling_Entry;2;FALSE))) - dan untuk [value_if_false] yang menyatakan kondisi \[ \begin{align*} \text{if } \text{P}_\text{ACTUAL. (n-1)} &≥ \text{P}_\text{PREDICTION. (n-1)} \\ \therefore \text{NRP} &= \text{'at Km'}_\text{(n)} + ( \text{Ltr}_\text{(n)} × \text{Km/Ltr}_\text{(AVG)} ) \end{align*} \] nya dapat kita tulis:
VLOOKUP(MAX(Refuelling_Entry[No. Refuelling]);Refuelling_Entry;2;FALSE) + (VLOOKUP(MAX(Refuelling_Entry[No. Refuelling]);Refuelling_Entry;8;FALSE) * AVERAGE(Fuel_Statistical_Analysis[Km/L]))
Gabungkan semua argumen tersebut ke dalam formula IF lengkapnya, maka 'Next Refuelling Prediction' kita akan menjadi terlihat seperti berikut ini:
 |
| Gambar 25. Tampilan Kombinasi formula yang menyusun perhitungan 'Next Refuelling Prediction' |
=IF(VLOOKUP(MAX(Refuelling_Entry[No. Refuelling]);Refuelling_Entry;2;FALSE) - VLOOKUP(MAX(Refuelling_Entry[No. Refuelling])-1;Refuelling_Entry;2;FALSE) < VLOOKUP(MAX(Refuelling_Entry[No. Refuelling])-1;Refuelling_Entry;8;FALSE) * AVERAGE(Fuel_Statistical_Analysis[Km/L]); VLOOKUP(MAX(Refuelling_Entry[No. Refuelling]);Refuelling_Entry;2;FALSE) + (VLOOKUP(MAX(Refuelling_Entry[No. Refuelling]);Refuelling_Entry;8;FALSE) * AVERAGE(Fuel_Statistical_Analysis[Km/L])) + ((VLOOKUP(MAX(Refuelling_Entry[No. Refuelling])-1;Refuelling_Entry;8;FALSE) * AVERAGE(Fuel_Statistical_Analysis[Km/L])) - (VLOOKUP(MAX(Refuelling_Entry[No. Refuelling]);Refuelling_Entry;2;FALSE) - VLOOKUP(MAX(Refuelling_Entry[No. Refuelling])-1;Refuelling_Entry;2;FALSE))); VLOOKUP(MAX(Refuelling_Entry[No. Refuelling]);Refuelling_Entry;2;FALSE) + (VLOOKUP(MAX(Refuelling_Entry[No. Refuelling]);Refuelling_Entry;8;FALSE) * AVERAGE(Fuel_Statistical_Analysis[Km/L])))
Dilihat dari sintaksnya seperti ini rasanya cukup bikin sakit kepala bukan? Hehe... Itulah mengapa saya sengaja memisahkan pembahasan ini jadi satu sub-heading sendiri.
5.2.3 – Contoh Kasus Perhitungan 'Next Refuelling Prediction'
Yuk kita tes bagaimana argumen ini betul dapat bekerja dengan baik.
Saya ambil contoh, kebetulan ada kejadian yang saya ingat ketika di pengisian ke-193 belakangan ini di mana saya melakukan pengisian BBM padahal belum habis (baik menurut kalkulasi Excel maupun nyatanya – masih ada sekitar 1 kotak di indikator belum kedap-kedip). Artinya pada pengisian sebelunya (yang ke-192) saya mengisi BBM, masih ada sisanya belum terkonsumsi. Datanya sendiri adalah sebagai berikut:
- \( \text{'at Km'}_\text{(193)} = 139418,8 \text{ Km} \) – Km ketika Refuelling ke-193, atau 'at Km' pada Latest Refuelling
- \( \text{'at Km'}_\text{(192)} = 139272,7 \text{ Km} \) – Km ketika Refuelling ke-192
- \( \text{Ltr}_\text{(193)} = 1,0 \text{ L} \) – jumlah Litre BBM yang dibeli ketika Refuelling ke-193
- \( \text{Ltr}_\text{(192)} = 3,0 \text{ L} \) – jumlah Litre BBM yang dibeli ketika Refuelling ke-192
- \( \text{Km/L}_\text{(AVG)} = 58,9 \text{ Km/L} \) – rata-rata konsumsi BBM per Litre dari hasil perhitungan di statistik.
Maka dalam persamaan 'Next Refuelling Prediction', perhitungan matematisnya kurang lebih akan terlihat sebagai berikut:
\[ \text{NRP} = \begin{cases} \text{'at Km'}_\text{(193)} + ( \text{Ltr}_\text{(193)} × \text{Km/Ltr}_\text{(AVG)} ) + (\text{P}_\text{PREDICTION. (192)} - \text{P}_\text{ACTUAL. (192)}), & \text{if } \text{P}_\text{ACTUAL. (192)} < \text{P}_\text{PREDICTION. (192)} \\ \text{'at Km'}_\text{(193)} + ( \text{Ltr}_\text{(193)} × \text{Km/Ltr}_\text{(AVG)} ), & \text{if } \text{P}_\text{ACTUAL. (192)} ≥ \text{P}_\text{PREDICTION. (192)} \end{cases} \]
Mari kita lihat seperti apa tampilannya di Excel
 |
| Gambar 26. Next Refuelling Prediction sejak pengisian BBM kendaraan pribadi saya yang ke-193 |
Penutup
Seru kan ternyata melakukan pencatatan dan analisis konsumsi BBM per Liter pakai Excel? Jadi bukan hanya sekedar mencatat saja. Setelah sistem analisis dan perhitungan prediksi ini dibangun, pasti Anda akan jadi lebih banyak menemukan insight baru pada setiap parameternya dan dapat memahami performa kendaraan Anda sepanjang waktu – bisa mengidentifikasi kecenderungannya, sampai bahkan mengoptimasinya.
Anda juga bisa saja sedikit melakukan calculative risks bereksperimen melakukan pengujian Anda secara mandiri pakai sistem pencatatan model seperti ini apakah performa kendaraan Anda bisa mencapai limitnya sampai seberapa jauh secara lebih jelas.
Sistem inilah yang sudah menemani saya sejak 2 tahun lalu saya mulai melakukan pencatatan dan melakukan fine-tuning pada file .xlsx saya ini. Saya memperlakukan ini sebagai diary saya, kadang file nya juga saya gabung dengan worksheet saya terkait dengan Maintenance Logbook, kadang juga saya gabung dengan sistem pencatatan Bookkeeping pribadi saya, andai saya mau mengetahui berapa banyak biaya BBM yang sudah saya habiskan selama ini. Hehe...
Paling tidak dengan sistem Trip Log ini saya jadi memiliki alat yang powerful untuk melacak dan mengoptimasi penggunaan kendaraan saya sehari-hari.
Saya mendorong Anda sekalian untuk mencoba sendiri pencatatan sistem seperti ini biar Anda dapat merasakannya langsung. Terima kasih sudah membaca 😊.
Steven William Soputra
14 September 2024
Komentar
Posting Komentar