coretan tentang autocad dan excel

May 29, 2013

[XLS-SVY-21]: Menampilkan Cross Section 3D dengan Chart Excel

Filed under: Excel, Tukang Ukur — Tags: , — cadex @ 09:06

 

Referensi :  
Platform : Excel 2007
Lokasi file : download

Sebenarnya di Excel sudah menyediakan type "Surface Chart" yang bisa menampilkan grafik dengan data input 3 dimensi (XYZ) termasuk kemampuan untuk merubah properties sudut putarnya. Surface Chart bisa menampilkan grafik atau surface 3D jika nilai data X dan Y mempunyai nilai increment yang tetap atau dengan sistem grid karena dalam Surface Chart X dan Y tidak ditampilkan sebagai data numeric sehingga bukan nilai actual X dan Y itu sendiri.

Berikut contoh input data untuk Surface Chart dan tampilan hasilnya dalam chart:

image

Data di atas mempunyai nilai X dan Y yang tetap.

Untuk menampilkan data cross section dimana data X dan Y nya tidak teratur, seharusnya ada type chart tambahan yaitu 3D XYZ (scattered) tetapi sampai saat ini saya belum menemukan type chart yang dimaksud. Agar tampilan cross section 2D dapat ditampilkan 3D, maka data XYZ cross section dimanipulasi dengan matrik rotasi kemudian diplotkan di grafik XY (Scattered).

Format data cross section yang dipakai untuk simulasi ini adalah sama dengan data yang pernah dibahas di posting [XLS-SVY-007]: Plotting Cross Section dari Excel ke AutoCAD dan [XLS-SVY-13]: Plotting Cross Section dari Excel ke AutoCAD Versi 1.1. Dalam contoh cross section terlampir terdapat 3 buah cross section 2D yang akan diplot menjadi cross 3D. Nilai koordinat X diambil dari nama station dalam bentuk numeric. Misal untuk station 0+000 dan 100+000, maka koordinat X nya berturut-turut adalah 0 dan 100. Sedangkan koordinat Y mengacu jarak dari centerline atau offset dan nilai Z mengacu ke elevasi.

Berikut adalah cuplikan data yang dimaksud berikut dengan Chart 2D :

Station/Sheet Data & Chart 2D
0 image
50 image
100 image

Tampilan data dan 3D cross :

image

Semua proses pembentukan tabel dan perhitungan menggunakan fungsi formula excel tanpa menggunakan macro.

Uratan perhitungan adalah sebagai berikut:

1. Menghitung jumlah record dari sheet yang akan ditamplikan di chart 3D   image
 

[A3:A5]

List nama sheet dari masing-masing data 2D cross section. Diisi dengan numeric

  [B3] =COUNT(INDIRECT("’"&A3&"’!B1:B300"))
menghitung jumlah record di sheet A3. Maksimal record 300
Copy rumus [B3] ke bawah untuk menghitung record di sheet lainnya
  [C3:C5] cell bantu untuk menunjukkan lokasi perpindahan cross di table baru. Tabel baru adalah tabel gabungan dari beberapa data cross
  [B1] =SUM(B3:B5)
jumlah record di tabel baru
  [C1] =COUNT(A3:A50)
jumlah sheet yang akan ditampilkan
2. Menggabungkan record dalam satu tabel baru   image[E3]=IF(ROWS($E$2:E2)<=$B$1,ROWS($E$2:E2))
[F3]=LOOKUP(E3,$C$3:$C$5,$A$3:$A$5)
[G3]=OFFSET(INDIRECT("’"&$F3&"’!$B$1"),COUNTIF($F$3:$F3,$F3),)
[H3]=OFFSET(INDIRECT("’"&$F3&"’!$c$1"),COUNTIF($F$3:$F3,$F3),)

[E3] membuat nomer urut record di tabel baru. Bernilai FALSE jika nomer urut melebihi dari hitungan jumlah record di $B$1
[F3] menampilkan angka STA (koordinat X), berdasarkan cell bantu di cell [$C$3:$C$5]
[G3] menambil nilai offset (Y) dari berdasarkan nama sheet di cell [F3]
[H3] menambil nilai offset (Z) dari berdasarkan nama sheet di cell [F3]
copy rumus [E3:H3] ke bawah sampai nilai di [E3]=FALSE

3. Setting Matriks Rotasi   image
  [K3],[K4] dan [K5] Masukkan sudut rotasi dalam satuan derajat
  [L3] =RADIANS(K3)
  [L4] =RADIANS(K4)
  [L5] =RADIANS(K5)
    Buat "Named Range" rx, ry dan rz yang mengacu ke range [L3], [L4] dan [L5]
  [J9] =COS(ry)*COS(rz)
  [J10] =-COS(ry)*SIN(rz)
  [J11] =SIN(ry)
     
  [K9] =COS(rx)*SIN(rz)+SIN(rx)*SIN(ry)*COS(rz)
  [K10] =COS(rx)*COS(rz)-SIN(rx)*SIN(ry)*SIN(rz)
  [K11] =-SIN(rx)*COS(ry)
     
  [L9] =SIN(rx)*SIN(rz)-COS(rx)*SIN(ry)*COS(rz)
  [L10] =SIN(rx)*COS(rz)+COS(rx)*SIN(ry)*SIN(rz)
  [L11] =COS(rx)*COS(ry)
4. Perkalian Matrix Asal dengan Matrix Rotasi   Matrix Asal adalah matrix yang dibentuk dari tabel baru dengan mangambil nilai X,Y dan Z. Jumlah baris di matrix adalah=banyaknya record=[B1], sedangkan jumlah kolomnya=3. Dari contoh di atas, Matrix asalah adalah dimulai dari F3, dengan jumlah baris sebanyak B1 ke bawah dan lebar ke kanan 3 kolom atau kalo dinyatakan dalam rumus excel adalah =OFFSET($F$3,,,$B$1,3).
Formula bawah ini named range dan formula yang dipakai untuk perhitungan perkalian matrix:
  _3dCross =OFFSET($F$3,,,$B$1,3)
matrix asal
  _2DPlot =MMULT(_3dCross,TRANSPOSE($J$9:$L$10))
perkalian matrix asal ke matrix rotasi. Hasil dari perkalian ini adalah matrix dengan dimensi (n x 2), n= jumlah record dan 2 adalah absis x dan y yang akan diplot di chart 2D xy scattered
5. Setting series untuk chart XY Scattered _x =OFFSET($F$3,,,$B$1)
  _sta =OFFSET($A$3,,,$C$1)
  _xPlot =INDEX(_2DPlot,,1)
  _yPlot =INDEX(_2DPlot,,2)
  __x1 =IF(_x=INDEX(_sta,1),_xPlot,NA())
  __x2 =IF(_x=INDEX(_sta,2),_xPlot,NA())
  __x3 =IF(_x=INDEX(_sta,3),_xPlot,NA())
  __y1 =IF(_x=INDEX(_sta,1),_yPlot,NA())
  __y2 =IF(_x=INDEX(_sta,2),_yPlot,NA())
  __y3 =IF(_x=INDEX(_sta,3),_yPlot,NA())

Rangkuman named range yang dipakai dalam perhitungan dan chart adalah

image

Untuk setting chart dan bentuk box, silahkan dipelajari lebih lanjut dari file terlampir.

==selamat mencoba==

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: