| Referensi | : | |
| Platform | : | Excel 2007 dan Google Earth |
| Lokasi File | : |
Saya mendownload hasil GPS tracker pada sebuah kendaraan operasional untuk bulan periode February 2011. Dari data tersebut akan saya buat laporan posisi mobil tersebut di saat hari libur dan hari kerja tiap jamnya pada chart excel dengan background image dari google earth seperti gambar di bawah:
Pada chart yang akan dibuat terdiri dari 3 series yaitu:
1. Series Day Off, yaitu menunjukkan lokasi mobil saat hari libur
2. Series work Day, yaitu lokasi mobil saat hari kerja
3. Keterangan lokasi mobil, saat digerakkan atau diclick tombol scroll bar.
Semula data asli dari GPS tracker adalah merekam posisi tiap 5 detik. Dengan mempertimbangkan bahwa kalo semua data tersebut akan ditampilkan semua, maka chart akan menjadi penuh dengan titik2, maka data yang diambil hanya posisi tiap jam.
Tahapan pembuatan chartnya adalah :
1. Membuat data yang menampilkan posisi tiap jam dari data hasil download
2. Menentukan hari kerja dan hari libur dari tanggal hasil download GPS
3. Membuat Series untuk chart.
4. Membuat Chart
5. Memasukkan background image google earth
1. Menampilkan data posisi tiap jam.
Data awal hasil download setelah dibuka di excel:
Informasi yang berperan dalam pembuatan chart yang diinginkan adalah: informasi tanggal dan waktu di kolom [A], informasi koordinat X dan Y di kolom [E] dan [F]. Informasi di kolom [E] dan [F] sebenarnya adalah informasi Bujur dan Lintang dalam satuan derajat desimal.
Seperti terlihat di tabel di atas semula jumlah recordnya adalah 6483 record. Dengan menggunakan microsoft query, jumlah record tersebut akan diambil hanya satu record tiap jamnya sehingga akan didapat table baru yang lebih sedikit jumlah recordnya.
Cara menggunakan Microsoft query adalah sebagai berikut:
| 1.1. | Buat named range pada data file sumber: Block data sumber dari [A5:K6483], dari menu [Formula], pada group [Define Names], pilih [Define Name]. kemudian namai range tersebut misalnya [GpsLog]. Save file excel tersebut kemudian tutup. |
 |
| 1.2. | Buka File Baru, kemudian jalankan microsoft query: Dari menu [Data], pada group [Get External Data], pilih [From Other Sources], kemudian [From Microsoft Query] |
 |
| 1.3. | Pada pilihan [Choose Data Source], pilih [Excel Files*], kemudian click [OK] | |
| 1.4. | Kemudian pilih workbook (file excel) sumber yang akan diquery. Hasil dari langkah 1.1. click [OK] |
|
| 1.5 | Pada pilihan available tables and columns, pilih table [GpsLog], kemudian click [>] |  |
| 1.6 | Click tombol Next, sampai muncul kotak dialog [Query Wizard Finish]. Pilih [View data or edit query in Microsoft Query], kemudian click [Finish]. Data dari tabel [GpsLog] akan ditampilkan di [Microsoft Query] |
 |
| 1.7 | Pada Microsoft Query, click tombol [SQL]. Ganti SQL statement menjadi: SELECT G.*,T.ID keterangan dari SQL di atas: (SELECT min(GpsLog.Date_Time) as JAM, membuat table ‘T’ yang mempunyai field [JAM] dan [ID]. Isi dari [JAM] =min(GpsLog.date_Time) adalah Jam Minimum dari field [Date_Time] yang dikelompokkan berdasarkan [ID}=FORMAT(GpsLog.Date_Time,’dd-mm-yy hh’). INNER JOIN GpsLog AS G ON G.Date_Time=T.JAM Table ‘T’ digabung dengan table [GpsLog] alias ‘G’ yang mempunyai tanggal dan jam yang sama G.Date_Time=T.JAM. Hasil Query diurutkan berdasarkan [Date_Time] ORDER BY G.Date_Time SELECT G.*,T.ID : Semua field di Table ‘G’ dan filed [ID] dari table `T’ yang ditampilkan. |
|
| 1.8 | Click [OK], sampai muncul table baru hasil query. Terlihat dari tabel disamping, kolom Date_Time, tidak lagi menampilkan record tiap 5 detik, tetapi tiap 1 (satu) jam. Jumlah record semula 6483 dipilih menjadi hanya 681 record. |
 |
| 1.9 | Masih di Microsoft Query, pada menu [File], pilih [Return Data to Microsoft Excel]
Pilih view in [Table], put in [Existing Worksheet], kemudian click [OK] Table posisi mobil tiap jam sudah ditampilkan di worksheet |
 |
2. Menentukan hari kerja dan hari libur
| 2.1 | Mendefinisikan hari libur dan Hari Kerja Hari Libur adalah Hari Sabtu, Minggu dan Hari Libur Nasional. |
|
| 2.2 | Buat List Hari Libur Nasional di sheet tersendiri. Contoh disamping adalah contoh list libur nasional. Block Range [A1:A15], kemudian dari menu [Formula], pada group [Define Names], pilih [Create from Selection] Pilih atau check, Create Names values from values in the : Top Row. un-checked pilihan yang lain. Named Range baru bernama [LiburNasional] sudah ditambahkan di workbook |
 |
| 2.3 | Kembali ke sheet hasil query [GpsLog]. masukkan field baru di sebelah kanan field [ID] atau di cell [M1] dengan nama [DayOff]. Masukkan rumus atau formula di [M2] |
Kolom [M] atau kolom [DayOff] akan bernilai 0, jika hari kerja (Workday) dan bernilai > 0 jika hari libur (DayOff) |
3. Membuat Series Chart
| 3.1 | Series Day Off akan dibuat berdasarkan named range [DayOffBujur],[DayOffLintang] dan untuk Series Work Day berdasarkan [WorkDayBujur],[WorkDayLintang]
Ketik DayOffBujur, DayOffLintang di kolom [N] dan [O] Masukkan formula di [P2]=IF($M2>0,NA(),E2) |
Block Mulai [N1:Q682], kemudian dari menu [Formula], pada group [Define Names], pilih [Create from Selection] Pilih atau check, Create Names values from values in the : Top Row. un-checked pilihan yang lain. Block Juga [A1:A682], kemudian lakukan Create Name from selection seperti langkah di atas. Pada menu [Formula], pilih [Name Manager] untuk menampilkan named range yang telah dibuat. |
4. Membuat Chart
| 4.1 | Pindah atau buat sheet Baru | |
| 4.2 |  |
Dari Menu [Insert], pada group [Charts], pilih [Scatter] kemudian pilih type Scatter with only Markers.
Dari Menu [Design], pilih [Select Data] |
| 4.3 |  |
Click [Add], pada Series Name, ketik Work Day. Click icon panah di bawah Series X values, kemudian click worksheet yang berisi table, tekan F3. Dari List Name pilih WorkDayBujur. click icon panah di bawah Series Y values, kemudian click worksheet yang berisi table, tekan F3 pilih name WorkDayLintang dengan cara yang sama buat series name untuk Day Off |
| 4.4 | Chart menjadi:  |
chart disamping, harus dilakukan setting lebih lanjut yaitu menentukan batas minimum dan maximum untuk masing-masing bujur dan lintang. |
| 4.5 | Tabel minimum dan maksimum bujur dan lintang: 
Kolom F dan G adalah hasil konversi bujur, dan lintang dari derajat desimal menjadi derajat-menit-detik [F2]=ABS(D2)/24 : [G2]=ABS(E2)/24 Format cell untuk [F2:G3]
|
Rumus untuk table disamping: [B2]=MIN(OFFSET(Date_Time,0,4)) [C2]=MAX(OFFSET(Date_Time,0,4)) [B3]=MIN(OFFSET(Date_Time,0,5)) Harga Minimal dibulatkan ke bawah dan maximal dibulatkan ke atas ke ratusan terdekat: [D3]=FLOOR(B3,0.01) |
| 4.6 | Setting Batas Max dan Min Bujur:  |
Pilih X-Axis Chart (Bujur), click kanan kemudian pilih [Format Axis]
Sesuai hasil perhitungan di atas, Pilih Y-Axis (Lintang) |
| 4.7 | Hasil Chart setelah setting Axis:  |
5. Memasukkan Background Image Google Earth.
| 5.1. | Jalankan google earth | |
| 5.2. | Menambah place mark Minimum (Bujur, Lintang) dan Maximum (Bujur, Lintang) di google earth.
Dari Menu [Add], pilih [Placemark] |
 |
| 5.3 |  |
Pada kolom Name , ketik Min. Masukkan Nilai Latitute (Lintang) minimal dan Longitude (Bujur) Minimal. Note: Karena Hasil Tracking untuk nilai lintang adalah positif, maka lokasinya ada di lintang Utara (N) sedangkan untuk bujur selalu East €. Place mark untuk minimum adalah: |
| 5.4 | Capture image (Print Screen) google earth yang menampilkan kedua placemark, kemudian crop image tepat melalui kedua place mark. Simpan file hasil cropping tersebut |
 |
| 5.5 | Pada program excel, pilih chart yang akan diberi image google earth.
Dari menu [Lay Out], pada group [Current Selection], pilih [Chart Area] Kemudian Click [Format Selection] |
 |
| 5.6 |  |
Pada option Fill, pilih Picture or Texture fill. Insert from File. Pilih Pile hasil cropping google earth. kemudian close Format Area. Chart menjadi: |
| 5.7 | Setelah label axis dan grid dihapus dan setting marker style chart menjadi: |
==bersambung====
coretan tentang autocad dan excel 
kenapa ga bisa download filenya? selalu diarahkan ke website microsoft
kenapa saya tidak bisa download filenya? selalu saja diarahkan ke halaman microsoft 365