Kasus Looping yang mengambil hanay data terakhir (100).

[hendrik karnadi]

Selamat siang rekan2 Be-Excel,

Mengungkit kisah lama jaman Siti Nurbaya, saya mencoba menggunakan Looping untuk konversi dari format database ke format table (matrix),

Dari :                                                       Ke:

 

Tapi kalau saya gunakan formula SumProduct hasilnya sdh benar seperti ini :

Data terlampir.

Mohon petunjuk para sesepuh Be-Excel.

Terima kasih.

Salam,

HK

[NangAgus]

Selamat sore pak Hendrik,

karena data tidak konsisten (tgl 1=11 data, tgl 2=10data, tgl3=9 data dst) dan data item tidak urut, maka perlu adanya pengecekan letak setiap item berada di kolom berapa dan baris berapa?

saya mengusulkan pengecekan sederhana: (Sheets("Data").Cells(k, 1) = Sheets("Hasil_Macro").Cells(i, 1)) (untuk mengecek item dan

(Sheets("Data").Cells(k, 2) = Sheets("Hasil_Macro").Cells(2, j)) untuk mengecek tanggal.

Jika tanggal dan itemnya sudah sesuai baru dituliskan dalam data: Sheets("Hasil_Macro").Cells(i, j) = Sheets("Data").Cells(k, 3).

Jadi loopnya saya ubah menjadi:

            For i = 3 To cItem.Count + 2
                For j = 2 To cTgl.Count + 1
                    For k = 1 To tblData.Rows.Count
                     If ((Sheets("Data").Cells(k, 1) = Sheets("Hasil_Macro").Cells(i, 1)) And (Sheets("Data").Cells(k, 2) = Sheets("Hasil_Macro").Cells(2, j))) Then Sheets("Hasil_Macro").Cells(i, j) = Sheets("Data").Cells(k, 3)
                    Next k
                Next j
            Next i

Pengecekan data dapat juga menggunakan with dan find untuk looping yang lebih bagus.

Regard

Kenang Agus

[Mr. Kid]

Target hasil membutuhkan urutan sumber data yang sorted berdasar Item lalu date, barulah si script yang ada bisa bekerja.

Jika sumber data tidak boleh di-sort berdasasr item lalu date, berarti butuh script untuk menyusun daftar unique item dulu, dipasang di area hasil.

Setelah itu baru loop sumber data dan loop tabel hasil untuk meletakkan nilai dari sumber data di tabel hasil.

Prosesnya akan jadi ruwet untuk yang masih terbata-bata dalam menulis script VBA.

Kalau sumber data diberi kolom bantu berisi formula, maka butuh 3 kolom bantu, yaitu :

1 kolom untuk formula bantu penentu Lokasi Item, ini bisa pakai fungsi Match (exact match) --> hasil berupa nomor baris data

1 kolom untuk formula bantu menyusun nomor urut lokasi item berdasar hasil formula kolom bantu ke-1 tadi -> hasilnya nomor urut nomor baris data tadi

1 kolom untuk formula penyusun nomor urut berdasar date per item (jadi item blabla akan dapat urutan nomor kolom 1,2,3,dst), fungsi countifs bisa dimanfaatkan dengan mengatur tanda $

Setelah itu baru susun script vba loop setiap baris data, peletakan di area hasil mengikuti angka hasil formula di kolom bantu ke-2 (baris hasil) dan kolom bantu ke-3 (kolom hasil)

Prosesnya pun tetap ruwet, apalagi yang masih terbata-bata dalam menyusun formula dan dalam menyusun script VBA

Excel punya fitur yang memudahkan semua itu mulai Excel 2007 ke atas. Tiga fitur yang digunakan untuk menghasilkan dataset hasil di kasus ini, yaitu :

1. fitur Excel Table -> untuk mengotomatiskan pemasangan formula ketika ada baris data baru yang ditambahkan, maka excel yang akan membuatkan formula di baris baru itu

2. fitur Excel Formula -> formula yang sudah sering dipakai oleh pengguna Excel

3. fitur Excel Pivot Table -> untuk mem-pivot-kan sumber data

Jika ada penambahan baris baru, hapus baris data, ubah isi data yang sudah ada di tabel sumber data, lakukan refresh melalui menu Data -> klik Refresh All

Tanpa pusing-pusing, sangat cepat untuk baris data yang banyak sekali, hanya formula bantu kolom D yang memberatkan Excel

Jika pakai fitur yang lebih bagus lagi yang ada di Excel, maka fitur yang dibutuhkan :

1. fitur Excel Table -> supaya sumber data selalu terikat menjadi sebuah blok data berbentuk table berkaidah database

2. fitur Power Query -> untuk transformasi data

*** Catatan : 

hasil transformasi data oleh fitur Power Query bisa ditampilkan kembali ke worksheet dalam bentuk Excel Table dan atau Excel Pivot Table (pilih salah satu atau mau dipakai semua)

Dari kiri ke kanan :

1. Excel Table sumber data

2. Excel Table hasil transformasi menggunakan fitur Power Query

3. Excel Pivot Table yang pivot data source-nya adalah query hasil fitur Power Query

fyi..

Fitur-fitur Excel yang powerful adalah kolaborasi dari 3 fitur berikut :

Excel Table - Power Query - Power Pivot

Support-nya adalah fitur Excel Formula (bila perlu).

Regards,

Kid

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to milis-belajar-e...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/1829840117.873580.1671432768459%40mail.yahoo.com.