coretan tentang autocad dan excel

March 14, 2011

[XLS-MAP-006]: Menampilkan Hasil Koordinat GPS di Chart Excel dengan image dari Google Earth

Filed under: Excel, Map — Tags: — cadex @ 02:02

 

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:

image

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:

image

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.

image
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]
image
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 [>] image
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]

image
1.7 Pada Microsoft Query, click tombol [SQL].
Ganti SQL statement menjadi:

SELECT G.*,T.ID 
FROM  ( 
SELECT min(GpsLog.Date_Time) as JAM,
FORMAT(GpsLog.Date_Time,’dd-mm-yy hh’)  AS ID
FROM GpsLog GpsLog 
GROUP BY FORMAT(GpsLog.Date_Time,’dd-mm-yy hh’)
) AS T  INNER JOIN GpsLog AS G  ON G.Date_Time=T.JAM 
ORDER BY G.Date_Time

keterangan dari SQL di atas:

(SELECT min(GpsLog.Date_Time) as JAM,
FORMAT(GpsLog.Date_Time,’dd-mm-yy hh’)  AS ID
FROM GpsLog GpsLog 
GROUP BY FORMAT(GpsLog.Date_Time,’dd-mm-yy hh’) ) AS T

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 
ORDER BY G.Date_Time

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.

image

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.

image
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

image

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

image
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]
=(WEEKDAY(A2,2)>5)+COUNTIF(LiburNasional,DATEVALUE(TEXT(A2,"m/d/yy")))

image

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]
Ketik WorkDayBujur, WorkDayLintang di kolom [P] dan [Q]

Masukkan formula di
[N2]=IF($M2>0,E2,NA())
[O2]=IF($M2>0,F2,NA())

[P2]=IF($M2>0,NA(),E2)
[Q2]=IF($M2>0,NA(),F2)

image 

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.
image

4. Membuat Chart

4.1 Pindah atau buat sheet Baru  
4.2 image Dari Menu [Insert], pada group [Charts], pilih [Scatter] kemudian pilih type Scatter with only Markers.

Dari Menu [Design], pilih [Select Data]

4.3 image 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:
image
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:
image

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
[F3]=ABS(D3)/24 : [G3]=ABS(E3)/24

Format cell untuk [F2:G3]

image

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))
[C3]=MAX(OFFSET(Date_Time,0,5))

Harga Minimal dibulatkan ke bawah dan maximal dibulatkan ke atas ke ratusan terdekat:
[D2]=FLOOR(B2,0.01)
[E2]=CEILING(C2,0.01)

[D3]=FLOOR(B3,0.01)
[E3]=CEILING(C3,0.01)

4.6 Setting Batas Max dan Min Bujur:
image
Pilih X-Axis Chart (Bujur), click kanan kemudian pilih [Format Axis]

Sesuai hasil perhitungan di atas,
Set Axis Options: 
Minimum Fix = 100.61
Maximum Fix = 101.46

Pilih Y-Axis (Lintang)
Set Axis Obtions:
Minimum Fix = 0.48
Maximum Fix = 1.71

4.7 Hasil Chart setelah setting Axis:
image
 

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]

image
5.3 image 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:
image
Lakukan langkah yang sama untuk Lintang dan bujur maksimal.

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
image
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]

image
5.6 image Pada option Fill, pilih Picture or Texture fill.
Insert from File.

Pilih Pile hasil cropping google earth. kemudian close Format Area.

Chart menjadi:
image

5.7 Setelah label axis dan grid dihapus dan setting marker style chart menjadi:  

image

 

==bersambung====

Leave a Comment »

No comments yet.

RSS feed for comments on this post.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: