Thx mr kid arahannya…..saya coba pelajari
Salam irianto From: belajar-excel@yahoogroups.com [mailto:belajar-excel@yahoogroups.com] Sent: 22 April 2015 8:34 To: BeExcel Subject: Re: [belajar-excel] ubah bentuk data tabel Maaf baru sempat nulis yang agak panjangan dikit... Cara membuat query : silakan pelajari struktur bahasa SQL dari internet, misalnya disini <http://www.w3schools.com/sql/sql_intro.asp> . Jika bisa membuat query, maka akan bisa membacanya. Jika bisa membaca query, maka akan bisa membuatnya. Susunan umum query untuk mengambil data (menyusun dataset atau query SELECT) : SELECT koloms FROM dataset_sumber JOIN dataset_lookup ON kriterias_lookup WHERE kriterias_filter GROUP BY koloms_penyusun_grup HAVING kriterias_filter_berdasar_hasil_penge-grup-an > bisa dijadikan urutan belajar SQL dasar < Yang biru : harus bisa (basic) Yang merah : diusahakan segera bisa Yang hitam : untuk yang serius mengolah data berbasis database Yang hijau : untuk yang suka bermain dengan pengolahan data berbasis database Dua part (klausa) lagi yang sebaiknya bisa, yaitu : UNION [ALL] -> dibaca UNION atau UNION ALL : menggabungkan data (BUKAN menjejerkan data ya) ORDER BY -> mengurutkan dataset hasil Pembuatan query dilakukan seperti pembuatan formula, dengan langkah umum : -3 : mendefinisikan target -2 : mendefinisikan kriteria-kriteria proses -1 : menyusun alur proses 0 : memilih alat dan bahan proses 1 : menyusun bagian FROM 2 : menyusun bagian SELECT 3 : menyusun bagian lain dari query (bila proses memerlukannya) 4 : evaluasi query 5 : rilis kalo sudah ok Contoh untuk kasus ini : -3 : mendefinisikan target > membentuk dataset dengan kolom-kolom : nip; nama; time; state. -2 : mendefinisikan kriteria-kriteria proses > nip dari kolom nip, nama dari kolom nama, time dari kolom tgl dan (msk atau > klr), state berupa teks yang berbunyi C/In atau C/Out > state C/In ketika time dari tgl dan msk > state C/Out ketika time dari tgl dan klr > time akan berisi nilai jika kolom absen bernilai 'D', dan akan kosong atau > nol jika kolom absen bukan bernilai 'D' -1 : menyusun alur proses > a. menyusun dataset untuk state bernilai 'C/In' > b. menyusun dataset untuk state bernilai 'C/Out' > c. menggabungkan (BUKAN menjejerkan) hasil a dan b > d. mengurutkan hasil c berdasar kolom nip (kolom ke-1 hasil c) secara ASC dan > state (kolom ke-4 hasil c) secara ASC 0 : memilih alat dan bahan proses > menggunakan query > sumber data dari file Excel sheet bernama cara3_data > proses dilakukan di file Excel 1 : menyusun bagian FROM > untuk proses -1.a. maupun -1.b. (karena diambil dari sumber data yang sama) FROM [cara3_data$] AS a "Ambil data dari dataset bernama cara3_data yang berupa sheet Excel dan beri nama panggilan berbunyi 'a' " 2 : menyusun bagian SELECT (sumber data diberi nama panggilan a) > untuk proses -1.a. SELECT a.nip, a.nama ,cdate(format(clng(a.tgl),'0000-00-00')+' '+left(a.msk,2)+':'+right(a.msk,2)),0) as [time] ,'C/In' as [state] "susun dataset kolom dari kolom nip sumber data a, kolom nama dari sumber data a, ketika kolom absen dari sumber data a bernilai 'D', susun data datetime dari kolom tgl ditambah waktu berupa susunan 2 karakter terkiri kolom msk dihubungkan titik dua (:) diikuti 2 karakter terkanan kolom msk dan kolom ini diberi nama panggilan time, kolom dengan nama panggilan state diisi dengan teks tetap (constant text alias string) berbunyi C/In" > untuk proses -1.b. SELECT a.nip, a.nama ,cdate(format(clng(a.tgl),'0000-00-00')+' '+left(a.klr,2)+':'+right(a.klr,2)),0) as [time] ,'C/Out' as [state] "susun dataset kolom dari kolom nip sumber data a, kolom nama dari sumber data a, ketika kolom absen dari sumber data a bernilai 'D', susun data datetime dari kolom tgl ditambah waktu berupa susunan 2 karakter terkiri kolom klr dihubungkan titik dua (:) diikuti 2 karakter terkanan kolom klr dan kolom ini diberi nama panggilan time, kolom dengan nama panggilan state diisi dengan teks tetap (constant text alias string) berbunyi C/Out" 3 : menyusun bagian lain dari query (bila proses memerlukannya) > dengan susunan dasar query SELECT seperti yang diberi warna warni di atas, > maka susunan proses 1 dan 2 menjadi : - proses -1.a. SELECT a.nip, a.nama ,cdate(format(clng(a.tgl),'0000-00-00')+' '+left(a.msk,2)+':'+right(a.msk,2)),0) as [time] ,'C/In' as [state] FROM [cara3_data$] AS a - proses -1.b. SELECT a.nip, a.nama ,cdate(format(clng(a.tgl),'0000-00-00')+' '+left(a.klr,2)+':'+right(a.klr,2)),0) as [time] ,'C/Out' as [state] FROM [cara3_data$] AS a > proses -1.c., yaitu menggabungkan hasil a dan b dengan memanfaatkan klausa > UNION ALL SELECT a.nip, a.nama ,cdate(format(clng(a.tgl),'0000-00-00')+' '+left(a.msk,2)+':'+right(a.msk,2)),0) as [time] ,'C/In' as [state] FROM [cara3_data$] AS a UNION ALL SELECT a.nip, a.nama ,cdate(format(clng(a.tgl),'0000-00-00')+' '+left(a.klr,2)+':'+right(a.klr,2)),0) as [time] ,'C/Out' as [state] FROM [cara3_data$] AS a > proses -1.d., mengurutkan hasil dengan klausa ORDER BY berdasar kolom ke-1 > ASC dan kolom ke-4 ASC ORDER BY 1 ASC, 4 ASC --> karena default ORDER BY adalah ASC, maka bisa ditulis : ORDER BY 1, 4 > memasang baris pengurutan data ke query yang sudah disusun SELECT a.nip, a.nama ,cdate(format(clng(a.tgl),'0000-00-00')+' '+left(a.msk,2)+':'+right(a.msk,2)),0) as [time] ,'C/In' as [state] FROM [cara3_data$] AS a UNION ALL SELECT a.nip, a.nama ,cdate(format(clng(a.tgl),'0000-00-00')+' '+left(a.klr,2)+':'+right(a.klr,2)),0) as [time] ,'C/Out' as [state] FROM [cara3_data$] AS a ORDER BY 1, 4 4 : evaluasi query > copas query ke bagian edit query dihasil koneksi data di Excel > jika sukses, berarti bisa dirilis > jika gagal (hasil belum sesuai atau tidak bisa dijalankan), perlu perbaikan > query --> misal hasilnya sukses 5 : rilis kalo sudah ok > save workbook dan kirim sebagai file lampiran Kira-kira demikian... Wassalam, Kid 2015-04-21 14:37 GMT+10:00 'irianto' irianto.mand...@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com>: Nah klo query nya gini berhasil…….. Bisa dijelasin Mr mengenai cara membuat dan baca query nya?maklum baru tau banget dan pengen tau bwanget……..ora opo2 ya….. Salam irianto From: belajar-excel@yahoogroups.com [mailto:belajar-excel@yahoogroups.com] Sent: 21 April 2015 10:42 To: BeExcel Subject: Re: [belajar-excel] ubah bentuk data tabel hmmm... jangan-jangan ada sesuatu yang kurang saat meng-install ms office-nya. Tapi bisa pakai VBA (macro) gak ? btw, moga-moga tidak bermasalah dengan fungsi Left dan Right ya. Query ini menggantikan fungsi Replace dengan Left dan Right select a.nip,a.nama,iif(a.absen='D',cdate(format(clng(a.tgl),'0000-00-00')+' '+left(a.msk,2)+':'+right(a.msk,2)),0) as [time],'C/In' as [state] from [cara3_data$] as a union all select a.nip,a.nama,iif(a.absen='D',cdate(format(clng(a.tgl),'0000-00-00')+' '+left(a.klr,2)+':'+right(a.klr,2)),0) as [time],'C/Out' as [state] from [cara3_data$] as a order by 1 ,4 Wassalam, Kid 2015-04-21 13:18 GMT+10:00 'irianto' irianto.mand...@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com>: Sudah MR…sheet asli pun tidak bisa di refresh,muncul massage yang sama: “undefined function ‘replace’ in expression” salam From: belajar-excel@yahoogroups.com [mailto:belajar-excel@yahoogroups.com] Sent: 21 April 2015 9:51 To: BeExcel Subject: Re: [belajar-excel] ubah bentuk data tabel bunyi query-nya tampak ok. sudahkah dicoba untuk dihapus semua isi area penulisan querynya sebelum ditulisi ulang ? apakah sheet asli bawaan file lampiran bisa di-refresh (klik kanan cells dalam tabel hasil di sheet asli lampiran lalupilih refresh) ? Wassalam, Kid 2015-04-21 12:41 GMT+10:00 'irianto' irianto.mand...@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com>: Berikur query nya : select a.nip,a.nama,iif(a.absen='D',cdate(format(clng(a.tgl),'0000-00-00')+' '+replace(a.msk,'.',':')),0) as [time],'C/In' as [state] from [cara3_data$] as a union all select a.nip,a.nama,iif(a.absen='D',cdate(format(clng(a.tgl),'0000-00-00')+' '+replace(a.klr,'.',':')),0) as [time],'C/Out' as [state] from [cara3_data$] as a order by 1 ,4 thx informasinya From: belajar-excel@yahoogroups.com [mailto:belajar-excel@yahoogroups.com] Sent: 21 April 2015 9:22 To: BeExcel Subject: Re: [belajar-excel] ubah bentuk data tabel hmmm.... kalau dari pesannya, permasalahan ada di penggunaan fungsi replace dalam query-nya, dan bukan pada proses pembentukan koneksi data dengan fitur Get External Data-nya. Boleh ndak kalau dicoba tulis manual yang persis sama plek atau copas ke mari bunyi query yang menampilkan error tersebut ? Fitur Get External Data banyak dibahas di internet. Excel help memberi penjelasan yang lengkap. Contoh penggunaan fitur Get External Data antara lain bisa dilihat di coretan yang dimulai dari sini <http://excel-mr-kid.blogspot.com/2011/04/get-external-data-01-introduction.html> . Wassalam, Kid. 2015-04-21 12:03 GMT+10:00 'irianto' irianto.mand...@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com>: To mr.kid….. Tertarik dengan cara 3(get external data),saya coba meniru wejangan mr kid di sheet lain dengan attachment yang sama,namun ketika saya klik kanan table hasil àpilih table àpilih edit queryàpilih type=SQLàcomand text= paste copy query ,koq gk berhasil ya ?massage nya : Apanya yang salah mister? Ø Adakah referensi untuk lebih detailnya mengenai pemahaman get external data?thx pencerahannya. Salam irianto From: belajar-excel@yahoogroups.com [mailto:belajar-excel@yahoogroups.com] Sent: 19 April 2015 4:22 To: BeExcel Subject: Re: [belajar-excel] ubah bentuk data tabel [1 Attachment] Hai Pie, File terlampir berisi 3 cara, yaitu : 1. formula > silakan diubah rujukannya agar lebih dinamis sesuai data setempat > proses meng-copy formula hasil agar bisa mengikuti kedinamisan jumlah record > sumber data adalah satu hal yang harus dilakukan manual. Tapi proses > meng-copy formula ini bisa dikembangkan untuk di-otomasi dengan VBA. Silakan > memperlajari VBA dan menyusun proses otomasinya. 2. pivot table > meski susunan kolomnya tidak sama persis, tetapi dengan fitur Excel Table > disisi data dan fitur Excel Pivot Table disisi output, akan mempercepat > proses karena cukup melakukan Refresh All melalui ribbon Data -> group > Connections -> Refresh All > cocok untuk record data yang sangat banyak 3. get external data (buat yang mau saja) > cara ini adalah dengan mengkoneksi data ke sebuah file Excel. Pada kasus ini, > sumber data yang dikoneksi ada di file yang sama dengan lokasi output, > meskipun akan lebih baik jika file sumber data dan file output adalah file > yang berbeda. > bagi pemula dalam fitur Get External Data atau yang ingin mempelajari fitur > Get External Data, unduh file lampiran dan letakkan di drive D pada root > directory (jangan didalam folder apapun) > ketika file hasil unduhan dibuka, akan muncul pesan security tentang Data > Connection. Pastikan di-Enable. (jadi bukan hanya VBA [macro] yang butuh > diatur security-nya, file terlampir tidak berisi VBA apapun) > cara ini cocok untuk record yang banyak sekali, karena proses pembaruan data > dilakukan di sisi data lalu di save dan melakukan refresh all. Andaikan > output di file yang lain (bisa jadi di komputer lain), maka file output > tersebut yang melakukan refresh all. > pengembangan system pengolahan data untuk bermigrasi ke aplikasi database > atau bahkan system database akan menjadi lebih mudah. > satu buah file sumber data di sebuah komputer bisa dikoneksi oleh banyak file > output dari banyak komputer sekalipun tanpa VBA dan tanpa shared workbook. > user yang bertugas mengisi data baru di file sumber data dapat bekerja > seperti biasa. Setelah di-save, maka cukup beritahukan kepada pemilik file > output untuk melakukan refresh data dengan Refresh All di file output > masing-masing. Wassalam Kid. 2015-04-18 15:41 GMT+10:00 indria pie mas_p...@yahoo.co.id [belajar-excel] <belajar-excel@yahoogroups.com>: yth. master excel mohon bantuan solusinya saya mau ubah data absen karyawan dari bentuk original menjadi bentuk custom seperti yang kami inginkan saya lampirkan saja filenya terima kasih sebelumnya salam pie
