coretan tentang autocad dan excel

July 18, 2010

[XLS-PMG-01]: Schedule Proyek dengan Excel

Filed under: Excel — cadex @ 11:09
Referensi :  
Platfom : Microsoft Excel 2003
Lokasi File : di sini

Tujuan dari tulisan ini adalah membuat schedule project dengan menggunakan microsoft excel lengkap dengan manpower dan equipment schedule. Di program project planner seperti primavera atau microsoft project biasanya disebut gantt chart. Kali ini akan dicoba dibuat dengan excel, tentunya fasilitas dan kemampuannya jauh berbeda dengan software yang dikhususkan untuk project planner seperti primavera atau microsoft project.

Mohon maaf untuk pemakai excel 2007, karena tutorial yang akan disajikan di tulisan ini adalah menggunakan excel versi 2003 kadang ada tahapan yang tidak sesuai terutama untuk menu dan toolbars-nya. Tetapi dengan memahami tahapan-tahapannya pasti bisa diaplikasikan di excel 2007.

Snap shot project schedule yang akan dibuat kira-kira seperti gambar di bawah:

sch_proj

Keterangan:

[A3:A9] Nomer urut aktivitas
[B3:B9] Urutan aktivitas
[C3:C9] Nomor aktivitas sebelumnya
[D3:D9] Lag atau jeda waktu, memulai pekerjaan. Misal untuk pekerjaan waterpass dilaksanakan 2 (dua) hari setelah pekerjaan traverse, maka di kolom C dimasukkan id = 1 (id traverse), kemudian di kolom D dimasukkan angka 2.
[E3:E9] Tanggal dimulainya aktivitas. Untuk aktivitas pertama, dimasukkan tanggal secara manual, sedangkan untuk aktivitas berikutnya menggunakan rumus.
E2=OFFSET($G$2,MATCH(C4,$A$3:$A$9,0),0)+D4+1

keterangan rumus tersebut;
MATCH(C4,$A$3:$A$9,0) = 1 –> mencari lokasi id aktivitas sebelumnya ([C4]) di kolom  $A$3:$A$9. hasilnya adalah 1, yaitu terletak di baris pertama di range $A$3:$A$9

sehingga perhitungan rumus OFFSET($G$2,MATCH(C4,$A$3:$A$9,0),0), menjadi
OFFSET($G$2,1,0)= 19-Jul-10 –> Mengambil tanggal di kolom G, dimulai dari cell $G$2 ke bawah sebanyak 1 baris, kolomnya tetap (kolom=0).

Perhitungan E2 menjadi
=(19-Jul-10)+[D4]+1
=(19-Jul-10)+0+1
=(20-Jul-10)

Copy rumus E2 sampai ke E9

[F3:F9] Berisi durasi pekerjaan, dimasukkan manual
[G3:G9] Perhitungan akhir aktivitas berdasarkan tanggal mulai + durasi.
G3=E3+F3-1
[H2:Y2] Berisi tanggal dimulai dari awal aktivitas.
H2=E3
I2=H2+1
Copy I2 sampai Y2
[H3:Y9] Baris yang akan diarsir dimulai dari tanggal awal aktivitas dan berakhir di tanggal akhir aktivitas. Jika awal dan akhir pekerjaan berubah, otomatis pengarsirannya juga akan menyesuaikan.
Proses peng-arsir-an menggunakan fasilitas di excel yang disebut conditional formatting.

Jika angka di [H3:Y9]>0, maka akan tidak diarsir, jika sebaliknya, maka akan diarsir.
Tahapan untuk memunculkan angka di range tersebut adalah sebagai berikut

H3=(H$2>=$E3)*(H$2<=$G3)

keterangan rumus tersebut:
(H$2>=$E3)=TRUE –> Jika tanggal di H2 (18-Jul-10) >= tanggal mulai pekerjaan (18-Jul-10), maka bernilai TRUE, sebaliknya akan bernilai FALSE

(H$2<=$G3)=TRUE –> Jika tanggal di H2 (18-Jul-10) <= tanggal akhir pekerjaan (19-Jul-10), maka bernilai TRUE, sebaliknya akan bernilai FALSE

H3=TRUE*TRUE=1

copy rumus tersebut ke range [H3:Y9], maka akan didapat hasil berupa angka 1 dan 0, angak 1 sesuai dengan durasi tiap aktivitas.

Agar angka 0 (nol) tidak tampil, di excel 2003, pada menu ‘tools>>options’ pilih ‘View’ kemudian unchecked ‘Zero Values’. Untuk excel 2007, silahkan dicari di help atau di http://office.microsoft.com/en-us/excel-help/display-or-hide-zero-values-HP010070461.aspx

image 

Saatnya menggunakan conditional format
Block atau pilih range [H3:Y9], kemudian dari menu ‘Format>>Contional Formating’
Pada ‘Condition 1’, pilih ‘Formula is’ kemudian masukkan formula =H3>0

Click tombol ‘Format’, kemudian pilih format arsiran yang diinginkan dengan memilih tab ‘Patterns’.
Jika sudah dipilih format pattern yang diinginkan, click OK.

Silahkan dicoba, merubah-rubah durasi aktivitas, maka arsiran akan mengikuti secara otomatis.

untuk pengguna excel 2007, silahkan googling atau membaca di help. Maaf sekali lagi, karena aku tidak punya excel 2007.

[AA3:AE9] Kode manpower yang terlibat di tiap aktivitas
[AG3:AK9] kode equipment yang dipakai tiap aktivitas
[A13:B17] Kode manpower dan keterangannya
[E13] Jumlah mandays untuk kode manpower GD. E13=SUM(H13:Y13)
Copy rumus di [E13], sehingga didapat jumlah mandays dari seluruh man power.
[H13] Menunjukkan kehadiran manpower tiap harinya. Jika angkanya lebih > 1 dalam satu hari, maka pada hari tersebut manpower tersebut melakukan aktivitas lebih dari satu.

Rumus di H13, menggunakan formula array. Jika memasukkan rumus sebagai formual array, maka setelah menulis rumus harus diakhiri dengan Ctrl+Shift+Enter

H13=SUM(H$3:H$9*($AA$3:$AE$9=$A13))

akhiri penulisan rumus dengan Ctrl+Shift+Enter  sehingga rumus akan ditampilkan sebagai formula array yaitu ada tambahan { diawal dan } diakhir rumus.

H13={SUM(H$3:H$9*($AA$3:$AE$9=$A13))}

Keterangan rumus:
($AA$3:$AE$9=$A13) –> mencari manpower id di range $AA$3:$AE$9 dengan manpower id di [A13]. Jika sama, maka akan bernilai 1, jika tidak akan bernilai 0

H$3:H$9*($AA$3:$AE$9=$A13)—> mengalikan hasil penacarian manpower id $AA$3:$AE$9, dengan aktivitas kegiatan di range  H$3:H$9. Jika di tanggal tersebut ada assigment untuk manpower yang dimaksud, maka bernilai 1, jika tidak akan bernilai 0.

={SUM(H$3:H$9*($AA$3:$AE$9=$A13))} –> menjumlahkan array 1 dan 0 = jumlah kegiatan untuk manpower yang dicari.

copy rumus [H13] ke [H13:Y17]

Gunakan conditional format seperti yang sudah dijelaskan di atas…

[H22:Y26] Equipment schedule dihitung seperti cara menghitung, manpower schedule.

Selamat mencoba….

(00=[][]=00)

About these ads

6 Comments »

  1. [...] Lanjutan dari post sebelumnya [...]

    Pingback by [XLS-PMG-04]: Manpower dan Equipment Schedule dengan Excel (Lanjutan) « coretan tentang autocad dan excel — October 24, 2010 @ 13:56

  2. matur nuwun mas…tak cobane…gak ada macrone to…soale rep tak jajal neng open office…

    Comment by hery.hariyanto — November 1, 2010 @ 09:50

    • nggak jamin akan bisa di open office. kadang fungsi di excel tidak ada di open office atau sebaliknya

      Comment by cadex — November 1, 2010 @ 10:05

  3. tararengkyu mas zain

    Comment by siti — March 2, 2011 @ 14:02

    • sama2 bu guru siti.
      kalo ada rumus atau coding yang ampuh boleh kok di sharing di sini

      Comment by cadex — March 2, 2011 @ 14:49

  4. terma kasih boss

    Comment by dani — December 17, 2012 @ 08:26


RSS feed for comments on this post. TrackBack URI

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

The Shocking Blue Green Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 787 other followers

%d bloggers like this: