Sedikit lebih jauh tentang Excel pivot_table_report

Di awal awal saya kerja menggunakan Excel, dengan modal pengetahuan Excel seadanya saya selalu melakukan penyimpanan data secara sangat basic dan disaat mereview data2 tsb saya menjadi kerepotan dikarenakan tidak menggunakan fasilitas yang sangat membantu pada Excel, yaitu pivot-table report.

Dengan kutak-kutik, baca sana sini - akhirnya saya sedikit ngerti “tata cara” melakukan penyimpananan data yang lebih baik serta penggunaan pivot-table disaat nge-review data2 yg sudah saya simpan.

Disini saya mau membagi ulasan buat temen2 yang kerja menggunakan Excel dan kebetulan belum tau ttg hal ini atopun siapa saja yang tertarik “lebih dalem” ttg data, tabel dan pivot.

KASUS :
Menyimpan nota-nota penjualan pada transaksi yang terjadi.
Nota ini meliputi : No. Nota, Tanggal, Nama Barang (ITEM), Jumlah (QTY), Harga (PRICE) dan perhitungan perkalian QTY * PRICE.

PROBLEM :

  1. Mengetik ITEM yg sama dgn PRICE-nya berulang-ulang
  2. Menyimpan sekian banyak form-nota pada setiap WS (worksheet)
  3. File dan WS menjadi banyak, repot saat mau di-review.

form-nota

SOLUSI :
Ada beberapa kombinasi cara untuk menghindari pengetikan yang berulang-ulang : vlookup, data validation, auto-correction

vlookup
Membutuhkan Tabel.
Rumus : =vlookup(referensi cell, nama_tabel, nilai_kesekian, FALSE)

“VLOOKUP” adalah jenis formula yang membaca cell unik sebagai tautan utama, dimana hasil yang di display adalah nilai kesekian dari “kandungan” cell unik tsb.

Kalo kita lagi di supermarket, si kasir tinggal ketik kode - maka “kandungan” kode tsb akan muncul yaitu nama barang dan harganya.

Pertama kita buat dulu sebuah Tabel yg berisi header : KODE, ITEM, PRICE

Rule :

  • Kode HARUS selalu unik, tidak pernah boleh sama.
  • Tabel HARUS selalu mempunyai header
  • Tabel sebisanya semua kolom&baris terisi.

TABEL DATA ITEM

[tr]
[td]1. Buat WS baru dan namai WS ini : TABEL
Lalu buat tabel seperti pada gambar.
[/td]
[td]

[/td]
[/tr]

[tr]
[td]Tabel ini akan kita namakan : KODE
a. Blok keseluruhan tabel
[/td]
[td]

[/td]
[/tr]

[tr]
[td]
b. Taruh kursor di name_box, klik sekali, ketik KODE
[/td]
[td]

[/td]
[/tr]

FORM NOTA & FORMULA

[tr]
[td]
2. Buat WS baru
Namai WS ini : form_nota
Buat form seperti pada gambar
[/td]
[td]

[/td]
[/tr]

Formula vlookup akan kita taruh di setiap baris-cell yang di kolom ITEM —> cell B8 B9 dst s/d B12, demikian juga di setiap baris-cell yang di kolom PRICE —> cell D8 s/d D12. DAN kita akan memasukan formula perkalian pada setiap baris-cell dibawah kolom SUM serta formula grand-totalnya.

[tr]
[td]a. Letakan kursor pada cell B8, lalu ketik :
=vlookup(A8,KODE,2,FALSE)
lalu tekan ENTER pada keyboard. [/td]
[td]

[/td]
[/tr]

A8 adalah cell sebelah kiri B8 yang akan kita isi no_kode.

KODE adalah nama tabel yang kita sudah buat pada langkah 1b

2 adalah pointer kolom yang kedua dari nomor unik pada ws-TABEL kolom A (header KODE) dari tabel KODE yg sudah kita buat pada ws-TABEL —> pointer ini akan jatuh pada kolom B (header ITEM).

FALSE adalah saya nggak tau, pokok mesti ketik FALSE aja… :char11:

[tr]
[td]b. Letakan kursor pada cell D8, lalu ketik :
=vlookup(A8,KODE,3,FALSE)
lalu tekan ENTER pada keyboard.

3 adalah pointer kolom ketiga
dihitung dari header KODE pada ws-TABEL
—> pointer ini akan jatuh pada header PRICE.
[/td]
[td]

[/td]
[/tr]

[tr]
[td]c. Letakan kursor pada cell E8, lalu ketik :
=C8*D8
lalu tekan ENTER pada keyboard.
Ini adalah formula perkalian QTY x PRICE.[/td]
[td]

[/td]
[/tr]

[tr]
[td]d. Letakan kursor pada cell E13, lalu ketik
=SUM(E8:E12)
lalu tekan ENTER pada keyboard.
Ini adalah formula total jumlah.[/td]
[td]

[/td]
[/tr]

[tr]
[td]e. Blok 1 baris, mulai dari kolom B8 s/d E8,
lalu pada keyboard tekan ctrl + c
(pencet ctrl dulu, jangan dilepas lalu pencet c)
—> disini kita melakukan pengkopian
formula yg terdapat di B8, D8 dan E8.
[/td]
[td]

[/td]
[/tr]

[tr]
[td]f. Blok 5 baris kebawah mulai dari kolom B8,
lalu tekan ENTER pada keyboard.

untuk problem “#N/A” yang muncul,
akan kita tangani nanti…:slight_smile:
[/td]
[td]


[/td]
[/tr]

Coba ketik no_kode pada tiap2 baris dibawah header KODE dan jumlah-nya dibawah header QTY

Sekarang kita sudah mempunyai suatu sistim “supermarket” mini yang kita buat sendiri… :). Mudah2an bisa berguna dan kalo ada yang mao ditanya, silahkan.

Nah sampe sini dulu, soalnya kalo emang gak ada temen yang memerlukannya … ya saya males-lah untuk lebih jauh lagi pada langkah2 selanjutnya… ;D

salam.
PS : kalo males bikin2 lagi - download aja attachement filenya… :slight_smile:


Hallo bro Odading… Apa kabar…? Swr…gw rindu banget sama bro Oda… Udah lama gak ketemu… :char11:
Wew… keren bro… wajib juga dipelajari nich… :slight_smile:
Sayang gw lebih suka pake micro VB6 dengan data base menggunakan Access terutama mysql.
Klo ada yang mengulas Inventory System , Point Of Sale dgn source, coding vb6… keknya seru… :D?
Thx infonya…bro oda.

Halo Lomeru,

Ho-oh… lama nih kita gak jumpa disini …:slight_smile:

Sayang gw lebih suka pake micro VB6 dengan data base menggunakan Access terutama mysql.
waduh... saya malah baru denger dari kamu "micro VB6" ... :ashamed0002:

Apakah micro VB6 ini mirip2 dengan macro VBA pada excel ?
Kalo VBA pada excel, lumayan kadang saya juga gunakan…:slight_smile:

Dulu sempet belajarin Access, tapi puyeng… jadinya males.
BTW, sql itu yang dipake kalo kita bikin php file ya ?

Klo ada yang mengulas Inventory System , Point Of Sale dgn source, coding vb6... keknya seru.... :D?
Kamu donk yang mengulasnya... nanti saya ikutan belajar dari Lomeru deh... :)

Makasi Lomeru atas komen-nya.

salam.

DATA VALIDATION
Kadang digunakan apabila ITEM tidak menggunakan no. KODE.
Penggunaannya untuk menghindari kesalahan ketik pada ITEM, misal ITEM “Pisang” akan dibaca berbeda oleh pivot-report dgn ITEM "Pisang ".

Langkah2 :
Membuat Tabel dgn nama ITEM. (dengan asumsi no. KODE tidak digunakan)

pada ws TABEL
1a. Blok baris baris yang berisi nama ITEM (dibawah header ITEM) seperti pada gambar :

1b. Taruh kursor di name_box, klik sekali, ketik ITEM

Membuat Tabel dgn nama PRICE
Masih pada ws TABEL
2a. Blok seperti pada gambar

2b. Taruh kursor di name_box, klik sekali, ketik PRICE

pada ws form_nota
3a. Blok cell cell dibawah kolom ITEM

3b. Klik Data —> lalu pilih Validation…

—> muncul window DATA VALIDATION.
3c. Isi dan samakan seperti pada gambar, lalu klik OK.

Pada langkah 3 ini kita memvalidasikan cell cell yang kita blok pada langkah 3a, agar kita terhindar dari salah pengetikan.

Sedangkan pada langkah 3c, kita memilih “List” yang berarti adalah list dari nama2 ITEM yang bersumber (Source) dari Tabel yang bernama ITEM — jadi kita isi kolom “Source” dgn formula “=ITEM”.

3d. Baris baris yang kita blok pada langkah 3a, saat kita klik salah satunya akan muncul tanda-panah spt pada gambar :

Apabila kita klik panah tsb - akan ditampilkan list dari nama2 ITEM berdasarkan tabel ITEM yg kita buat. Coba ketik : [u]Pisang + spasi[/u] pada kolom B8 ("Pisang ") , lalu ENTER. Maka akan muncul window error, dikarenakan nama [u]Pisang (dgn spasi)[/u] tidak diketemukan pada tabel ITEM.

Jadi disini kita mempunyai 2 cara dalam pengetikan nama item :

  1. Bisa dengan drop-downlist (pasti benar)
  2. Bisa juga dengan mengetik manual (bisa salah)

Langkah selanjutnya adalah menaruh formula pada barisbaris yang berada dibawah header PRICE pada ws form_nota.

4a. Pada kolom D8, ketik formula =vlookup(B8, PRICE, 2, false)

Kali ini tautan referensi yang akan kita baca adalah nama barang yang terletak di kolom B8. (Sebelumnya kita gunakan A8 karena tautan referensinya adalah KODE barang).

Juga sekarang tabel yang kita gunakan adalah tabel yang kita namakan PRICE. Dimana kolom pertama pada tabel PRICE ini berisi list dari nama barang.

Sedangkan angka 2 adalah kolom kedua pada tabel PRICE yang berisi harga barang.

4b. Kopi formula pada langkah 4a tsb ke baris2 dibawahnya pada nota.

Kita sudah mem-validasi baris2 yang perlu validasi untuk menghindari kesalahan dalam pengetikan.

AUTO CORRECT
Apabila kita mempunyai puluhan tetapi masih dibawah 50 item, terkadang kita akan kesulitan untuk menggunakan drop-downlist hasil dari data-validasi… karena kita akan ditampilkan seluruh list nama barang yang ada.

Sedangkan untuk mengetik cukup memakan waktu lama apabila nama barang bukan sekedar pisang, mangga, nanas, dlsb —> (misal pisang kepok, mangga harum manis dll).

Dengan menggunakan AUTO CORRECT kita bisa menyingkat waktu dalam pengetikan nama barang yang panjang.

1a. Pertama kita ubah dulu tabel ITEM dgn mengganti nama2 barangnya menjadi lebih panjang.

1b. Klik Tools —> AutoCorrect …

—> akan muncul window baru AutoCorrect.
Centang “Replace text as you type” spt pada gambar dibawah :

1c. Kita akan mengisi satu per satu nama barang pada inputbox “With:” dgn masing masing singkatannya pada inputbox “Replace:”

RULE :
Nama barang HARUS PERCIS SAMA dgn nama barang yang ada di tabel ITEM.

pr - Pisang Raja —> klik Add
mh - Mangga Harum Manis —> klik Add
jba - Jeruk Bali (kwalitas A) —> klik Add
jbb - Jeruk Bali (kwalitas B) —> klik Add
th - Tomat Hijau —> klik Add

Klik OK.

Sekarang pada ws form_nota, coba ketik salah satu singkatan pada baris baris yang terdapat dibawah header ITEM.

Disini kita sudah bisa nyenengin Boss dengan bekerja lebih cepat pada pengetikan text yang sama dan berulang-ulang… :slight_smile:

Mohon jangan lupa, fungsi DATA VALIDATION dan AUTO CORRECT baru bisa kerasa manjurnya apabila kita mempunyai jenis barang puluhan serta kita tidak menggunakan/mempunyai nomor kode barang.

Berikutnya nanti kita akhirnya akan melangkah ke Pivot Table Report.

salam.

Lumayan sibuk bro Odading :slight_smile:
Cuma disingkat… Microsoft Visual Basic 6…Vr diatasnya Vb.net… Kyk gak mungkin klo gak tau.? :slight_smile:
Untuk listing,coding program kek sama…Mungkin disisi Interface yang berbeda…
Salah satu yang sering digunakan sebagai database oleh php adalah mysql. Vb6 juga bisa mengakses mysql dgn tambahan perangkat lunak pendukung ODBC konektor.keuntungan bisa multi user lebih banyak dari access.
He…he…he… maunya sih kasi tutorialnya… cuman sekarang kemampuanya cuman mentok di desig Form doang :ashamed0002: Coding programnya cepat bikin rambut rontok. :cheesy:

Tak bomark dulu ya bro…
Ditunggu pembuatan laporannya

Salam n Gbu…

Dari thread sini, nanti ada juga saya masukin sedikit VBA-nya.

Makanya saya merangkaknya pake Excel,
soalnya Access bikin puyeng tanpa VB - apalagi dgn VB :D.

Herannya kalo utk php file di website saya lumayan tau,
tapi kok di Access males banget ya untuk dipelajari ? :slight_smile:
Soalnya, kemampuan Excel itu sendiri menurut saya juga cukup bisa diandalkan si… kalo tibang untuk “pekerjaan pekerjaan kecil” … :slight_smile:

Gpp kok… siapa tau lomeru sempet, kan bisa bikin ulasan bikin form-nya…:slight_smile:

salam,

Ingat waktu kul dulu pas belajar Excel terjadi insiden tak terduga :smiley: hasil penjumlahan dikomputer dosen dengan komputer laptp gw berbeda aku tanya dosenya. pak ini kok hasil punya bapak dengan pny saya kok gak sama? dosennya trus dtangen ke gw setelah cek n ricek bbrp saat dosennya bilang ke gw .keknya komputernya error nich!. gw jawab…loh kok bisa error sic pak… pdhl lptpnya barusan di instal ulang loh…?dosen diem bentar…mungkin jarang dibersihin kalee…jadinya debunya numpuk didalem… Apa mungkin yah :mad0261: gawe pikir enak banget jadi dosen yah?jawabannya bisa klasik kek gitu ;D Sory out mlu… :smiley:

Bro Oda…misal:
KODE====>ITEM====>QTY====>PRINCE===>SUM
oda003—>jeruk-----harus diketik,apa bisa auto gak?.bila jumlahnya lebih dari 1 jumlahnya baru di ketik?
rata2 klo ada yang beli sic biasanya cuman satu item-----maklum bro saya bukan orang kantoran… :ashamed0004:
trus klo input data yang sama bs gak bro?.. keknya scan barcode… keceptan biasanya double bunyinya… itemnya juga double :smiley: maksudnya biar hemat waktu gtu… :slight_smile:
Thx… bro Oda…

dosen nya lagi suntuk kali, udah males mikir salah dimana. paling di rumus ato di makronya :).

kalu penggunaan barcode, sy blm ngerti caranya, bisanya cuma pake no. kode barang, hehehe.

utk qty yg lsg isi sndiri, kalu saya kayaknnya akan menggunakan makro yg tiap saat no. kode brg dimasukin lbh dr satu kali, qty brtambah 1.

nanti malem tak coba makronya.

:slight_smile:
salam.

Halo lomeru,

ini kodenya mungkin bisa kamu coba :

Sub MasukinKode()
Set NoKode = Range("a8")
Range("c8 : c12").Value = 0
Range("a8 : a12").ClearContents

Do
kode = InputBox("Masukan No. Kode")
If kode = "" Then Exit Sub

If kode = NoKode.Offset(-1, 0).Value Then
NoKode.Offset(-1, 2).Value = NoKode.Offset(-1, 2).Value + 1
Set NoKode = NoKode.Offset(-1, 0)
Else
NoKode.Value = kode
NoKode.Offset(0, 2).Value = 1
End If

Set NoKode = NoKode.Offset(1, 0)
Loop

End Sub

Karena nggak digunakan scan barcode, saya gunakan InputBox utk masukin no-kode yg jadi seolah-olah sedang scan barcode… :slight_smile:

salam.
PS : file-nya juga saya sertakan.


Sorry bro oda… baru bisa ol lagi…moga saja masih memantau tritnya… :smiley:
Nanti malam saya coba lagi bro…

Salam & Jbu…

halo lomeru… pa kabar ?
Sekarang giliran saya yang minta maap karena baru sekarang2 ini online lagi…hehehe :).

Gimana udah sempet coba kodenya ?

salam.

Udah di coba tapi belum sukses bro Odading…
Gak tau dimana salahnya… ntar saya otak-atik lagi…
Dari dulu excel memang ribet hehehe…

Salam & JBU