Wow, penjelasan Master Kid RUAR BIASA. Baiklah saya coba membuat seperti yang Master jelaskan. Dan hasilnya nanti saya kirimkan ke group untuk di-review.
On Mon, Dec 10, 2018 at 12:39 AM 'Mr. Kid' [email protected] [belajar-excel] <[email protected]> wrote: > > > Begini, > Prinsip dasar mengolah data di Excel : > "Sebisa mungkin Excel mendapatkan data yang sudah siap pakai (data clean), > agar tidak perlu (setidaknya dapat mengurangi beban) proses penyiapan data > untuk bisa diolah lebih lanjut" > > 1. Terlepas data dari setiap guru seperti apa bentuknya, usahakan hasil > akhir dari pengumpulan semua data dari semua guru adalah seperti ini > (apapun proses yang dibutuhkan) : > > [image: rng1.gif] > > Disana ada kolom NIS. Kolom NIS yang menjadi andalan untuk proses > selanjutnya. > Kolom NAMA sangat buruk untuk diandalkan dalam proses selanjutnya, karena > pada umumnya, teks nama bisa sama. Lihat saja nama ANI di file asli Anda. > Kolom IDRecord berisi formula. > Proses pengumpulan data yang Anda miliki (entah itu dengan VBA baca setiap > file dari setiap guru setiap siswa atau apapun bentuknya) hanya mengisi > kolom selain IDRecord. > Nama tabel ini misalnya (Excel Table ya) dtNILAI > > 2. Siapkan tabel-tabel referensi > Untuk kasus ini, sepertinya tabel referensi daftar siswa dan tabel > referensi daftar bulan tahun data sudah mencukupi. > Misalnya seperti ini : > > [image: rng2.gif] > > Misal tabel referensi siswa (Excel Table) diberi nama refSISWA. > Tabel referensi bulan tahun berisi nilai tanggal 1 setiap bulan dalam > setahun (bisa setahun ajaran) diberi nama refPERIODE (Excel Table) dan bisa > disusun dengan formula bila perlu. > Tabel ini akan dimanfaatkan untuk membuat daftar pilihan di tabel output. > Misal pilihan dengan fitur Data Validation. > > 3. Menyiapkan sheet output > > susun items di sheet output. > > Misal sheet output akan memberi pilihan kepada user untuk memilih nama > siswa, maka daftar nama siswa yang disajikan dalam daftar adalah dari kolom > NIS_NAMA tabel referensi refSISWA. > Bukan dari kolom NAMA karena akan berisi nama yang duplicate dan Excel > tidak bisa membedakan dengan mudah > ** artinya, jika Anda menggunakan kolom NAMA saja, maka Anda akan > bekerja keras menyusun formula rumit dan menguras kerja komputer untuk > sekadar memberi tahu Excel, > nama terpilih yang tepat itu yang dengan NIS berapa. > Jadi, dibuatlah sebuah nama range (misal bernama _lstSISWA_ ) yang > merujuk hanya di kolom NIS_NAMA di tabel referensi refSISWA. > Nama range _lstSISWA_ akan digunakan dalam data validation di sheet > output. > Misal begini : > > > [image: rng3.gif] > > Kelebihan dari pemanfaatan fitur Excel Table adalah sifatnya sebagai tabel > dinamis. Jadi, nama range yang merujuk ke sebuah kolom Excel Table juga > otomatis akan dinamis. > > > Lakukan juga pada referensi bulan tahun yang merujuk ke refPERIODE kolom > PERIODE, dan misal diberi nama _lstPERIODE_ > > 4. Menyusun sheet output. > > Pasang data validation untuk bulan tahun dan pilih sebuah item (misal > pilih Nov-2018) [atur format number-nya bila perlu] > > Pasang data validation untuk nama yang berisi daftar NIS dan NAMA > (merujuk ke _lstSISWA_) > > Susun items output di sisi baris > > Susun tanggal di sisi kolom berisi nilai tanggal > ** tanggal 1 merujuk ke cells pilihan bulan lewat data validation > tadi, > ** tanggal 2 berisi formula nilai tanggal 1 ditambah dengan angka 1, > ** dst untuk tanggal lain > > susun formula hasil dengan formula yang sesederhana mungkin > ** karena tabel data (dtNILAI) sudah memiliki kolom IDRecord di sisi > kiri tabel, maka fungsi vLookUp bisa digunakan. Bila perlu sertakan IFError. > > Misalnya begini : > > [image: rng4.gif] > > Kolom IDRecord di tabel dtNILAI disusun dengan format nis|tgl|indikator > Maka lookup value juga disusun dengan format seperti format IDRecord, > dengan nilai-nilai cells yang diambil dari sheet output yang bersesuaian > Atur absolute references dalam formula sesuai kondisi yang dibutuhkan > (mana yang harus absolute kolom, atau absolute baris, dsb) > > > Semakin sederhana formula, dengan proses komputasi di setiap formula yang > tidak melibatkan range yang banyak, akan membuat kerja Excel menjadi lebih > optimum. > > > Andai menggunakan fitur PowerPivot, bisa lebih bagus lagi... > Proses pengumpul data yang Anda miliki bisa ditata agar bisa menghasilkan > data seperti ini : > > [image: rng11.gif] > > Tabel di atas masih kurang bagus. Lebih bagus lagi jika kolom INDIKATOR > hanya berisi kode item. Tapi tak mengapa, sekadar untuk belajar supaya > tidak terlalu ruwet. > > > Lalu, referensi disiapkan seperti ini : > > [image: rng12.gif]Tabel tanggal setahun (tabel paling kanan) untuk > dimensi waktu di dalam Excel Pivot Table nanti. > > > Semua excel tabel di-Add to data model (semua yang ada di sheet data dan > di sheet referensi) > Kemudian disusun relasi antar tabelnya (dari nama kolomnya saja sudah bisa > tampak relasinya kan, seperti INDIKATOR di referensi bertemu INDIKATOR di > tabel data, dan sebagainya) > > Lalu dibuatlah sebuah Measure (field yang akan diletakkan di bagian VALUES > dalam Excel Pivot Table). Misal bernama NILAI_SISWA di tabel data yang ada > di model. > Seperti ini : > > [image: rng13.gif] > > Kemudian kirim data dari data model ke Excel Pivot Table, dan susun pivot > table. Misalnya begini : > > [image: rng14.gif]Yup, bagian VALUES berisi data bertipe text hasil dari > measure bernama NAMA_SISWA > > Slicer bisa dimanfaatkan untuk memudahkan pemilihan nama siswa oleh user > (lihat kotak latar putih berisi daftar NIS dan NAMA) > > Susunan layout Excel Pivot Table-nya adalah seperti ini : > > [image: rng15.gif] > > > btw, > terkadang, ada aja yang bilang : > "mendapatkan data seperti tabel data seperti itu prosesnya rumit." > > plis deh... > > manfaatkan PowerQuery daripada VBA, maka akan lebih gampang. Siapa saja > bisa belajar dengan cepat. Jauh lebih cepat dibanding belajar VBA supaya > bisa menyusun data seperti itu. > > Proses penyiapan data dengan PowerQuery hanya dilakukan 1 kali seumur > hidup data. VBA cuma berisi perintah refresh doang... > > Pakai contoh saja deh... > Misal ada file data seperti ini : > > [image: rng22.gif]Kolom A sampai Kolom H di set sebagai header yang > di-merged cells 2 baris. > > Baris 1, pada kolom i dan seterusnya berisi NIS > Baris 2, pada kolom i dan seterusnya berisi NAMA > Kalau baris 1 di-hide, user akan melihat tabel dengan header 1 baris di > baris 2. > Anggap saja nama filenya Kelas_A.xlsx > Data di sheet yang tetap, yang misalnya di sheet bernama HARIAN > > > Di sebuah workbook baru bisa dibuat proses powerquery (di xl2013 ada menu > PowerQuery, di xl2016 ke atas, ada di menu DATA) -> get data -> from file > -> pilih Excel > -> pilih file Kelas_A.xlsx (atau apalah file Anda) -> OK > -> muncul pilihan nama sheet -> pilih sheet bernama HARIAN -> tekan EDIT > -> muncul PowerQuery Editor > hasil load pertama seperti ini misalnya : > > [image: rng23.gif] > > Proses berikutnya adalah proses mengubah data tersebut, yang tadinya > horisontal, jadi vertikal. > Seakan di-transpose sebagian kolomnya, yaitu bagian kolom berisi kode NIS. > Ini namanya proses unpivot. > Urutannya begini : > 1. lihat sisi kanan, ada bagian Query Settings -> ada kotak Applied Steps > -> klik proses bernama 'Changed Type' -> klik tanda x yang muncul atau klik > kanan pilih Delete > ** menentukan tipe data dilakukan nanti saja di proses akhir (lihat di > langkah 6) > 2. set first row as header (ada di menu Home dalam PowerQuery Editor) > 3. filter kolom INDIKATOR -> remove empty (baris yang blank dibuang saja) > 4. buang kolom yang tidak penting, misal kolom NO, PROGRAM bla, KOMPETENSI > bla, KELOMPOK, MINGGU KE, BULAN > >caranya : pilih kolom-kolom tersebut -> menu HOME -> remove columns > 5. lakukan unpivot : blok kolom INDIKATOR dan TGL -> menu TRANSFORM -> > klik panah kecil Unpivot Columns -> pilih Unpivot Other Columns > 6. ganti nama kolom Attribute menjadi NIS : klik kanan kolom Attribute -> > rename -> tulis NIS -> OK > * lakukan juga untuk kolom VALUE menjadi kolom bernama NILAI > ** bila perlu, ubah tipe data* setiap kolom agar sesuai, seperti > kolom NIS menjadi number : klik kolom NIS -> menu HOME -> Data Type -> > pilih Whole Number > 99. dah jadi, tinggal ditampilkan ke worksheet atau langsung dikirim ke > PowerPivot Data Model : menu HOME -> klik panah kecil Close & Load -> pilih > Close & Load To > muncul window peletakan hasil -> atur sesuai kebutuhan > Seperti begini jadinya : > > [image: rng24.gif] > > > > sudah begini, masih ada yang bilang : > "Filenya ada banyak loh..., mosok atu-atu..." > > Ya, kalo mau atu-atu juga gpp kok.... > > Kalo mau langsung sefolder juga bisa... > > Tinggal bikin sebuah folder yang isinya semua files yang dibutuhkan, > misal Kelas_A.xlsx, dsb yang pasti ada sheet bernama HARIAN dengan susunan > yang tetap. > > Trus bikin deh di workbook baru, proses untuk etl (extract, transform, > load) semua file dalam folder itu. > > Kan udah bisa bikin untuk 1 file. Tinggal belajar dikit lagi, n beres > deh... (no offense, ini beneran mudah) > > Pakai contoh lagi deh > Misal ada folder bernama DATA di drive D berisi files yang mau diproses. > Kelas_A.xlsx dan sebagainya, yang pasti ada sheet bernama HARIAN > > 0. Ambil 1 file dulu, misal Kelas_A.xlsx pakai cara di atas tadi dari > langkah 1 sampai 6 (langkah 99 nya nanti saja kalau sudah selesai semua > proses setiap file dalam folder) > 7. di dalam PowerQuery Editor, edit query HARIAN : klik kanan nama query > HARIAN -> pilih Advanced Editor -> muncul banyak tulisan tuh > -> di baris pertama, di-Enter supaya ada baris kosong -> lalu tambahin > bunyi set variabel berbunyi [ini case sensitive ya, jadi harus persis] : > (isiFILE as binary) => > * nama variabelnya adalah isiFILE > -> lalu lihat baris Source = blabla, ganti bagian berbunyi > *File.Contents("D:\DATA\Kelas_A.xlsx")* dengan nama variabelnya, yaitu > isiFILE (case sensitive ya) > -> tekan DONE > *** ini namanya membuat function di dalam powerquery, supaya bisa > dipakai dalam formula. > seperti ini jadinya isi si query HARIAN : > > [image: rng25.gif] > > > 8. Sekarang mau membuat daftar file dan mengangkut semua sheet dari > masing-masing file ke dalam PowerQuery, caranya adalah dengan membuat query > baru : > di PowerQuery Editor -> di sisi kiri ada bagian Queries -> klik kanan > bagian Queries -> pilih New Query -> File -> pilih Folder -> tunjuk folder > DATA yang berisi banyak file itu -> OK > -> muncul daftar file -> EDIT > Seperti ini : (nama query barunya adalah DATA [isinya daftar file > beserta isinya]) > > [image: rng26.gif] > > nah loh... mau bikin daftar file lengkap dengan berbagai info tentang file > juga bisa dilakukan dengan langkah 8 ini kan... > > tinggal pilih kolom mana yang harus dihapus > > Kolom [Content] itu isinya semua data di setiap sheet dalam file tersebut > > 9. ok, misal hasil akhir pengen ada nama filenya di kolom pertama ya... > jadi kolom yang dipakai cuma kolom [Content] dan kolom [Name]. Kolom > lainnya dibuang. > gini : klik kolom Name -> tekan CTRL jangan dilepasin -> klik kolom > Content [ini namanya cara blok banyak kolom sesuai selera] > -> klik kanan kolom-kolom yang terblok -> pilih remove other columns > > 10. kalau mau supaya ekstension file nya hilang, bisa di-replace. Atau > kalau ada file lainnya, bisa difilter. Atur saja seperti pakai autofilter. > 11. Mengeluarkan tabel sheet harian dari setiap file > -> menu Add Column -> add custom column -> muncul tempat nulis > formula > *** masih ingat ya, tadi ada function baca sebuah file sheet harian > bernama HARIAN > -> tulis di tempat nulis formula tersebut bunyi function HARIAN dan > isi variabel dengan kolom Content (kolom Content isinya semua data di dalam > file kan ya) > -> tekan OK > Begini : > > [image: rng27..gif] > > dan hasil query jadi begini : > > [image: rng28.gif] > > kolom baru (dalam contoh ini bernama [Custom]) berisi table hasil > proses function HARIAN. Isinya ya seperti data HARIAN yang sudah diproses > jadi vertikal itu. > > 12. Membuka isi kolom berisi Table (dalam contoh ini di kolom Custom) > > di kolom Custom ada panah berlok kiri dan kanan -> klik panah > belok kiri kanan itu untuk Expand -> muncul daftar kolom -> centang semua > kolom > -> hilangkan centang dari item 'Use original blabla' -> tekan OK > 13. buang kolom Content (karena tidak dipakai lagi), ubah nama kolom Name > menjadi bernama KELAS, atur tipe data setiap kolom, bila perlu filter kolom > NILAI remove empty > 14. Close & Load To -> muncul window lokasi peletakan hasil > > Kalau data hasil gabungan files dan transformasi itu bisa lebih > dari 1 juta (misal, sampai 2 juta atau 40 juta lebih gitu) atau ingin > disimpan dalam data model, maka : > pilih Connection Only -> centang Add to data model -> OK > [ini supaya bisa langsung digunakan oleh PowerPivot dan jadi lebih cepat] > > Kalau ingin disimpan dalam sheet (khusus hasil tidak lebih dari > jumlah baris Excel), maka : > pilih Sheet -> HILANGKAN CENTANG dari Add to data model -> OK > (supaya file tidak besar banget karena nyimpan di 2 tempat sekaligus) > *** Kalau sudah sampai langkah 14, proses dalam data model bisa dilakukan > seperti dongeng jauh di atas sana tentang power pivot tadi. > > > sudah begini pun, masih ada yang bilang : > "weeee... data saya itu ada di banyak sub folder tauk... gak sesimpel > itu..." > > walah, jadi orang kok gak simpel.. kacian deh lu... > > ya sudah lah... yang waras ngalah > > mau ditaruh di sub sub sub folder berbagai macam dan banyaknya > terserah... pokok e cuman sampai langkah 14 gitu doang > > tinggal refresh sesukanya, kalo sekali refresh gak ter-update, lakukan > sampai 3 kali... > > kalo 3 kali refresh masih belum update, lakukan 1 kali lagi sambil sebut > namanya Kid... wkwkwkwk > > kalo tetep gak bisa, berarti Anda kurang beruntung... (gak ngepren sama > Kid kali lu) > > > ah udah ah.. > bisa tambah error kalo dilanjutin > segitu aja deh.. > > selamat mencoba.. > jangan ngimpi dapet file jadi ya... > wkwkwkwk.. > > Regards, > Kid > > > > > > > On Sun, Dec 9, 2018 at 4:30 PM Cari Kebenaran [email protected] > [belajar-excel] <[email protected]> wrote: > >> >> >> Terima Bapak NangAgus. Rumusnya berjalan.. Mohon maaf kalau Bapak pusing >> melihat tabel penilaian di PAUD. Itu tuntutan kurikulum PAUD 2013. >> >> Untuk Master Kid yang menanyakan apakah tabel datanya diubah jadi ke >> bawah bagaimana ? >> Jawaban saya: Silakan diubah. Kami dengan senang hati akan menerima tiap >> masukan. >> Oke Master, saya jadi sangat pingin lihat perubahan tersebut. >> >> Terima kasih >> >> >> >> On Sun, Dec 9, 2018 at 12:06 AM 'Mr. Kid' [email protected] >> [belajar-excel] <[email protected]> wrote: >> >>> >>> >>> Kalau tabel datanya diubah jadi ke bawah bagaimana ? >>> >>> Cuma iseng nanya kok >>> Biasanya dijawab gak bisa dengan berbagai alasan. >>> >>> Tapi, sapa tahu yang ini gak biasa, jadi tabelnya akan diubah jadi ke >>> bawah. >>> >>> Sent from my smart enough phone >>> >>> On Dec 8, 2018, at 21:32, Agus [email protected] [belajar-excel] < >>> [email protected]> wrote: >>> >>> >>> >>> Aduh,...... >>> Format nilainya membuat saya pusing. >>> untuk satu siswa saja harus mengisi sangat banyak dan demikian rumitnya. >>> saya sedang membayangkan jika ada seorang guru membuat nilai untuk 1.000 >>> siswa seperti saya berapa banyak yang harus diisikan! maklumlah saya tidak >>> pernah belajar untuk menjadi guru anak kecil, >>> Mohon maaf atas segala kesalahan. >>> Coba di D6 diketik pormula: >>> >>> *=IFERROR(INDEX(HARIAN!$I$2:$AV$8,MATCH(D$5&$C6,INDEX(DAY(HARIAN!$F$2:$F$8)&HARIAN!$D$2:$D$8,,),0),MATCH($B$2,HARIAN!$I$1:$AV$1,0)),"")* >>> >>> Kopi pormula tersebut ke semua area. >>> Perlu diingat formula tersebut hanya membaca tanggal saja sesuai dengan >>> permintaan, namun belum dapat membedakan tanggal untuk bulan berbeda >>> apalagi tahun yang berbeda pasti tidak dapat dibedakan. >>> Jika menghendaki sesuai tanggal yang benar lakukan hal berikut: >>> 1. ubah saja value di D5 menjadi 1-11-18 dan ubah pula formatnya agar >>> menampilkan tanggal saja. >>> 2. ubah formula di atas dengan menghapus DAY >>> Salam >>> NangAgus >>> >>> On 12/8/2018 08:59, Cari Kebenaran [email protected] >>> [belajar-excel] wrote: >>> >>> >>> Kepada Yth. Para Master dan segenap anggota milis. Tolong dibantu. >>> >>> Dalam kasus ini saya punya dua sheet, yaitu HARIAN dan BULANAN. >>> Persoalannya adalah: >>> >>> Saya ingin mengisi nilai siswa pada range D6:AH167 (pada sheet BULANAN) >>> secara otomatis >>> berdasarkan nilai yang dicapai oleh siswa pada sheet HARIAN sesuai >>> dengan nama NAMA, INDIKATOR, DAN TANGGAL. Jadi ada 3 kriteria untuk mengisi >>> range D6:AH167 sheet HARIAN, yaitu NAMA, INDIKATOR, DAN *TANGGAL* >>> >>> Nama siswa sebagai filter-nya ada di cell B2 pada (sheet BULANAN). >>> Ketika saya mengubah nama siswa pada cell B2 (sebagai filter) di sheet >>> BULANAN maka hanya nilai dari siswa tersebut yang muncul di range nilai >>> (D6:AH167, sheet BULANAN). >>> >>> Range D5:AH5 (sheet BULANAN) merupakan tanggal (1 sampai dengan tanggal >>> akhir bulan). >>> Dalam file terlampir saya berikan contoh nilai atas nama siswa HASAN >>> (nama hanya contoh saja). >>> >>> Pakai formula atau VBA tidak masalah. Pakai keduanya pun saya lebih >>> senang. >>> >>> Atas perhatian dan bantuannya saya mengucapkan terima kasih. >>> >>> >>> <Copy of ISI RANGE NILAI BERDASARKAN NAMA DAN TANGGAL.xlsm> >>> >>> >
