15 Messages
Digest #3583
1a
Re: tanya rumus untuk detail perincian pada sheet rekap nota [1 Atta by "Mr. Kid" nmkid.family@ymail.com
1b
Bls: [belajar-excel] tanya rumus untuk detail perincian pada sheet r by "Ivan Sebastian" layonardo
3b
Re: Perintah SQL untuk menggabungkan data sekaligus menjumlahkan [1 by "Mr. Kid" nmkid.family@ymail.com
3c
Re: Perintah SQL untuk menggabungkan data sekaligus menjumlahkan by "hendrik karnadi" hendrikkarnadi
3d
Re: Perintah SQL untuk menggabungkan data sekaligus menjumlahkan by "Mr. Kid" nmkid.family@ymail.com
3e
Re: Perintah SQL untuk menggabungkan data sekaligus menjumlahkan by "hendrik karnadi" hendrikkarnadi
3f
Re: Perintah SQL untuk menggabungkan data sekaligus menjumlahkan by "Mr. Kid" nmkid.family@ymail.com
4b
Re: Bagaimana mengganti Name Manager pada file yang terkunci [1 Atta by "Mr. Kid" nmkid.family@ymail.com
4c
Bls: [belajar-excel] Bagaimana mengganti Name Manager pada file yang by "Tjahya Hartono" tjahyahartono
Messages
Wed Sep 30, 2015 2:13 am (PDT) . Posted by:
"Mr. Kid" nmkid.family@ymail.com
Cara yang saya sarankan juga pakai VBA (Macro) kok. Konsepnya malah lebih
terstruktur daripada yang Anda rancang.
Jadi, bukan masalah pakai VBA atau tidak. Tapi lebih cenderung ke
konsepnya, yaitu terstruktur atau tidak.
Kalau pertanyaannya ada cara lain atau tidak, tentu banyak, karena bisa
mengkombinasikan berbagai fitur Excel yang bisa diotomasi dengan VBA.
Tapi sebenarnya, pertanyaan Anda adalah ada konsep lainnya selain konsep
yang saya sarankan atau tidak...
Tentu jawabnya adalah banyak dan salah satunya adalah konsep yang Anda
rancang.
Pertanyaan berikutnya dari Anda adalah dengan konsep yang Anda rancang
tersebut bisa dibuatkan atau tidak...
Tentu jawabnya adalah tidak bisa, karena saya bisanya yang simpel-simpel
saja. Kalau yang rumit-rumit mah bikin kepala pening.
Tapi entah BeExceller yang lebih mampu berpening ria ya... ditunggu saja...
btw,
coba konsep Anda direnungkan kembali,
1. Kalau nanti berganti hari ada nota masuk lagi, dimana data nota yang
lama ? lalu seperti apa hasil rekapnya (menambahkan atau mengganti seluruh
nilai rekapan) ?
2. Bagaimana cara merekap nota per bulan, triwulan, jangka waktu tertentu
dsb... Tentu hal ini tidak dibutuhkan sekarang, tapi apa iya data akan
dibiarkan menguap begitu saja setelah di-input ? Apa wajar kalau setiap
inputan disimpan dalam 1 workbook yang berbeda (kalau sebulan ada 20 hari
kerja ada 20 file dong... gimana merekap gabungan per bulannya ? gabungkan
semua workbook ? cpd)
3. Apakah data ini berdiri sendiri dan tidak ada kaitannya dengan kegiatan
lain dalam perusahaan ? Bagaimana cara menghubungkannya dengan kegiatan
lain tersebut (jika ada kegiatan lainnya) ?
4. Kalau data nota ini sebenarnya hasil output dari suatu sistem, kenapa
ada proses input manual lagi yang lalu di pindah ke cetak ?
kira-kira segitu saja deh...
dari yang 4 itu saja kan sudah bikin pening kalau suatu saat nanti
dibutuhkan...
apa iya harus bangun dari nol lagi...
;)
met mencoba dan menunggu...
Wassalam,
Kid
On Wed, Sep 30, 2015 at 3:51 PM, Ivan Sebastian layonardo@yahoo.co.id
[belajar-excel] <belajar-excel@yahoogroups.com> wrote:
>
>
> terima kasih atas sarannya mr kidz... tapi apa ada cara lain selain itu?
> semisal pakai makro gt..
> ini file updatenya.. saya tambahkan rumus di bagian rekap nota...
> dan limit untuk sheet cetak nota saya bikin maks 24 nota untuk satu file...
>
>
>
>
> Pada Selasa, 29 September 2015 20:00, "'Mr. Kid' mr.nmkid@gmail.com
> [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:
>
>
>
> Hai Ivan,
>
> Sebaiknya Anda membuat sebuah sheet yang menampung seluruh data inputan
> berupa sebuah tabel yang baik.
>
> Misal Anda punya sheet inputan Nota berbentuk :
> No Nota 12345
> Nama a
> Alamat fffff
> Bla gjye
> tanggal 2015-09-29
>
> No pesanan kode barang kuantitas nilai
> 1 x 10 50
> 2 y 1 100
> 3 z 345 12345
>
> (nomor pesanan tentu ada maksimalnya per lembar nota)
>
>
> maka di sheet inputan sejajar dengan baris nomor pesanan di kolom yang tak
> terlihat dikanan sana dibuatkan formula untuk mengisi kolom berikut : susun
> kekanan dengan tampilan hasil formula yang Anda susun menjadi seperti ini
> (untuk contoh di atas)
> No Nota Nama Alamat Bla tanggal No Pesanan Kode
> Barang Kuantitas Nilai
> 12345 a fffff gjye 2015-09-29
> 1 x 10 50
> 12345 a fffff gjye 2015-09-29
> 2 y 1 100
> 12345 a fffff gjye 2015-09-29
> 3 z 345 12345
>
> Buat sebuah sheet baru bernama DATA berisi header :
> No Nota Nama Alamat Bla tanggal No Pesanan Kode
> Barang Kuantitas Nilai
>
>
> Nah, tombol yang ada di sheet input nota akan meng-copy area range
> berformula di sheet inputan nota (misal area yang dibiru) dan paste sepcial
> values ke baris kosong baru di sheet data.
>
> Setelah itu, output yang dibuat :
> 1. sheet Cetak Nota bisa mengambil dari sheet Data dengan formula
> 2. sheet Rekap Barang bisa mengmabil dari sheet Data dengan formula
>
> btw,
> Hal diatas sekedar saran agar pengolahan datanya lebih luwes, dan dapat
> terus dikembangkan secara baik, cepat, tepat, dan mudah
> Tidak ada paksaan untuk menerima saran diatas, karena hal pertama yang
> tampak adalah terasa ribet dan sulit.
> Sayangnya, sudah ditetapkan bahwa kemudahan itu adanya setelah kesulitan
>
> Wassalam,
> Kid
>
>
>
>
>
>
>
>
>
>
> 2015-09-29 19:35 GMT+07:00 Ivan Sebastian layonardo@yahoo.co.id
> [belajar-excel] <belajar-excel@yahoogroups.com>:
>
>
> data saya lampirkan... mohon bantuannya para master2 excel... thx..
>
>
>
>
>
>
terstruktur daripada yang Anda rancang.
Jadi, bukan masalah pakai VBA atau tidak. Tapi lebih cenderung ke
konsepnya, yaitu terstruktur atau tidak.
Kalau pertanyaannya ada cara lain atau tidak, tentu banyak, karena bisa
mengkombinasikan berbagai fitur Excel yang bisa diotomasi dengan VBA.
Tapi sebenarnya, pertanyaan Anda adalah ada konsep lainnya selain konsep
yang saya sarankan atau tidak...
Tentu jawabnya adalah banyak dan salah satunya adalah konsep yang Anda
rancang.
Pertanyaan berikutnya dari Anda adalah dengan konsep yang Anda rancang
tersebut bisa dibuatkan atau tidak...
Tentu jawabnya adalah tidak bisa, karena saya bisanya yang simpel-simpel
saja. Kalau yang rumit-rumit mah bikin kepala pening.
Tapi entah BeExceller yang lebih mampu berpening ria ya... ditunggu saja...
btw,
coba konsep Anda direnungkan kembali,
1. Kalau nanti berganti hari ada nota masuk lagi, dimana data nota yang
lama ? lalu seperti apa hasil rekapnya (menambahkan atau mengganti seluruh
nilai rekapan) ?
2. Bagaimana cara merekap nota per bulan, triwulan, jangka waktu tertentu
dsb... Tentu hal ini tidak dibutuhkan sekarang, tapi apa iya data akan
dibiarkan menguap begitu saja setelah di-input ? Apa wajar kalau setiap
inputan disimpan dalam 1 workbook yang berbeda (kalau sebulan ada 20 hari
kerja ada 20 file dong... gimana merekap gabungan per bulannya ? gabungkan
semua workbook ? cpd)
3. Apakah data ini berdiri sendiri dan tidak ada kaitannya dengan kegiatan
lain dalam perusahaan ? Bagaimana cara menghubungkannya dengan kegiatan
lain tersebut (jika ada kegiatan lainnya) ?
4. Kalau data nota ini sebenarnya hasil output dari suatu sistem, kenapa
ada proses input manual lagi yang lalu di pindah ke cetak ?
kira-kira segitu saja deh...
dari yang 4 itu saja kan sudah bikin pening kalau suatu saat nanti
dibutuhkan...
apa iya harus bangun dari nol lagi...
;)
met mencoba dan menunggu...
Wassalam,
Kid
On Wed, Sep 30, 2015 at 3:51 PM, Ivan Sebastian layonardo@yahoo.co.id
[belajar-excel] <belajar-excel@yahoogroups.com> wrote:
>
>
> terima kasih atas sarannya mr kidz... tapi apa ada cara lain selain itu?
> semisal pakai makro gt..
> ini file updatenya.. saya tambahkan rumus di bagian rekap nota...
> dan limit untuk sheet cetak nota saya bikin maks 24 nota untuk satu file...
>
>
>
>
> Pada Selasa, 29 September 2015 20:00, "'Mr. Kid' mr.nmkid@gmail.com
> [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:
>
>
>
> Hai Ivan,
>
> Sebaiknya Anda membuat sebuah sheet yang menampung seluruh data inputan
> berupa sebuah tabel yang baik.
>
> Misal Anda punya sheet inputan Nota berbentuk :
> No Nota 12345
> Nama a
> Alamat fffff
> Bla gjye
> tanggal 2015-09-29
>
> No pesanan kode barang kuantitas nilai
> 1 x 10 50
> 2 y 1 100
> 3 z 345 12345
>
> (nomor pesanan tentu ada maksimalnya per lembar nota)
>
>
> maka di sheet inputan sejajar dengan baris nomor pesanan di kolom yang tak
> terlihat dikanan sana dibuatkan formula untuk mengisi kolom berikut : susun
> kekanan dengan tampilan hasil formula yang Anda susun menjadi seperti ini
> (untuk contoh di atas)
> No Nota Nama Alamat Bla tanggal No Pesanan Kode
> Barang Kuantitas Nilai
> 12345 a fffff gjye 2015-09-29
> 1 x 10 50
> 12345 a fffff gjye 2015-09-29
> 2 y 1 100
> 12345 a fffff gjye 2015-09-29
> 3 z 345 12345
>
> Buat sebuah sheet baru bernama DATA berisi header :
> No Nota Nama Alamat Bla tanggal No Pesanan Kode
> Barang Kuantitas Nilai
>
>
> Nah, tombol yang ada di sheet input nota akan meng-copy area range
> berformula di sheet inputan nota (misal area yang dibiru) dan paste sepcial
> values ke baris kosong baru di sheet data.
>
> Setelah itu, output yang dibuat :
> 1. sheet Cetak Nota bisa mengambil dari sheet Data dengan formula
> 2. sheet Rekap Barang bisa mengmabil dari sheet Data dengan formula
>
> btw,
> Hal diatas sekedar saran agar pengolahan datanya lebih luwes, dan dapat
> terus dikembangkan secara baik, cepat, tepat, dan mudah
> Tidak ada paksaan untuk menerima saran diatas, karena hal pertama yang
> tampak adalah terasa ribet dan sulit.
> Sayangnya, sudah ditetapkan bahwa kemudahan itu adanya setelah kesulitan
>
> Wassalam,
> Kid
>
>
>
>
>
>
>
>
>
>
> 2015-09-29 19:35 GMT+07:00 Ivan Sebastian layonardo@yahoo.co.id
> [belajar-excel] <belajar-excel@yahoogroups.com>:
>
>
> data saya lampirkan... mohon bantuannya para master2 excel... thx..
>
>
>
>
>
>
Wed Sep 30, 2015 3:23 am (PDT) . Posted by:
"Ivan Sebastian" layonardo
roger that,kalo memang ga ada jalan lain, akan saya coba sesuai anjuran mr kidz.. thx
untuk jawaban no 1-3 diatasnota saya pastikan tidak akan bertambah.. karena memang sengaja saya buat seperti itu karena harga yg ada pada sheet pricelist tiap hari bisa berubah ubah setiap kali ada barang masuk pada hari itu... istilahnya nota harian lepas, begitu berganti hari buka file baru dan file data lama akan saya rename sesuai tanggal nota.. sehingga file baru akan selalu kosong isi awalnya.. benar seperti yg anda bilang.. 20 hari otomatis 20 nota.. rekap gabungan sudah ada bagiannya masing2 yg menanganinya....4. Kalau data nota ini sebenarnya hasil output dari suatu sistem, kenapa ada proses input manual lagi yang lalu di pindah ke cetak ?untuk jawaban no 4 ini..karena prosesnya seperti ini...orang beli barang kasih list item yg diminta apa... saya inputkan semua lalu saya tekan tombol next ke bagian sheet cetak nota dan nota saya cetak lalu saya kirimkan ke pegawai untuk menyiapkan barang2 sesuai dengan notanya.. semisal barangnya ternyata ada yg kurang maka nota yg ada di sheet cetak akan saya ubah sesuai dengan kondisi stock saat itu...jadi bila ada 2 org pembeli ... kalo saya tidak ada sheet input dan sheet cetak maka saya harus ketik ulank dari awal,dan customer ke2 harus menunggu customer 1 sampe bener2 selesai transaksinya...
Pada Rabu, 30 September 2015 16:14, "'Mr. Kid' mr.nmkid@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:
Cara yang saya sarankan juga pakai VBA (Macro) kok. Konsepnya malah lebih terstruktur daripada yang Anda rancang.
Jadi, bukan masalah pakai VBA atau tidak. Tapi lebih cenderung ke konsepnya, yaitu terstruktur atau tidak.
Kalau pertanyaannya ada cara lain atau tidak, tentu banyak, karena bisa mengkombinasikan berbagai fitur Excel yang bisa diotomasi dengan VBA.
Tapi sebenarnya, pertanyaan Anda adalah ada konsep lainnya selain konsep yang saya sarankan atau tidak...
Tentu jawabnya adalah banyak dan salah satunya adalah konsep yang Anda rancang.
Pertanyaan berikutnya dari Anda adalah dengan konsep yang Anda rancang tersebut bisa dibuatkan atau tidak...
Tentu jawabnya adalah tidak bisa, karena saya bisanya yang simpel-simpel saja. Kalau yang rumit-rumit mah bikin kepala pening.
Tapi entah BeExceller yang lebih mampu berpening ria ya... ditunggu saja...
btw,
coba konsep Anda direnungkan kembali,
1. Kalau nanti berganti hari ada nota masuk lagi, dimana data nota yang lama ? lalu seperti apa hasil rekapnya (menambahkan atau mengganti seluruh nilai rekapan) ?
2. Bagaimana cara merekap nota per bulan, triwulan, jangka waktu tertentu dsb... Tentu hal ini tidak dibutuhkan sekarang, tapi apa iya data akan dibiarkan menguap begitu saja setelah di-input ? Apa wajar kalau setiap inputan disimpan dalam 1 workbook yang berbeda (kalau sebulan ada 20 hari kerja ada 20 file dong... gimana merekap gabungan per bulannya ? gabungkan semua workbook ? cpd)
3. Apakah data ini berdiri sendiri dan tidak ada kaitannya dengan kegiatan lain dalam perusahaan ? Bagaimana cara menghubungkannya dengan kegiatan lain tersebut (jika ada kegiatan lainnya) ?
4. Kalau data nota ini sebenarnya hasil output dari suatu sistem, kenapa ada proses input manual lagi yang lalu di pindah ke cetak ?
kira-kira segitu saja deh...
dari yang 4 itu saja kan sudah bikin pening kalau suatu saat nanti dibutuhkan...
apa iya harus bangun dari nol lagi...
;)
met mencoba dan menunggu...
Wassalam,
Kid
On Wed, Sep 30, 2015 at 3:51 PM, Ivan Sebastian layonardo@yahoo.co.id [belajar-excel] <belajar-excel@yahoogroups.com> wrote:
terima kasih atas sarannya mr kidz... tapi apa ada cara lain selain itu? semisal pakai makro gt..ini file updatenya.. saya tambahkan rumus di bagian rekap nota...dan limit untuk sheet cetak nota saya bikin maks 24 nota untuk satu file...
Pada Selasa, 29 September 2015 20:00, "'Mr. Kid' mr.nmkid@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:
Hai Ivan,
Sebaiknya Anda membuat sebuah sheet yang menampung seluruh data inputan berupa sebuah tabel yang baik.
Misal Anda punya sheet inputan Nota berbentuk :
No Nota 12345
Nama a
Alamat fffff
Bla gjye
tanggal 2015-09-29
No pesanan kode barang kuantitas nilai
1 x 10 50
2 y 1 100
3 z 345 12345
(nomor pesanan tentu ada maksimalnya per lembar nota)
maka di sheet inputan sejajar dengan baris nomor pesanan di kolom yang tak terlihat dikanan sana dibuatkan formula untuk mengisi kolom berikut : susun kekanan dengan tampilan hasil formula yang Anda susun menjadi seperti ini (untuk contoh di atas)
No Nota Nama Alamat Bla tanggal No Pesanan Kode Barang Kuantitas Nilai
12345 a fffff gjye 2015-09-29 1 x 10 50
12345 a fffff gjye 2015-09-29 2 y 1 100
12345 a fffff gjye 2015-09-29 3 z 345 12345
Buat sebuah sheet baru bernama DATA berisi header :
No Nota Nama Alamat Bla tanggal No Pesanan Kode Barang Kuantitas Nilai
Nah, tombol yang ada di sheet input nota akan meng-copy area range berformula di sheet inputan nota (misal area yang dibiru) dan paste sepcial values ke baris kosong baru di sheet data.
Setelah itu, output yang dibuat :
1. sheet Cetak Nota bisa mengambil dari sheet Data dengan formula
2. sheet Rekap Barang bisa mengmabil dari sheet Data dengan formula
btw,
Hal diatas sekedar saran agar pengolahan datanya lebih luwes, dan dapat terus dikembangkan secara baik, cepat, tepat, dan mudah
Tidak ada paksaan untuk menerima saran diatas, karena hal pertama yang tampak adalah terasa ribet dan sulit.
Sayangnya, sudah ditetapkan bahwa kemudahan itu adanya setelah kesulitan
Wassalam,
Kid
2015-09-29 19:35 GMT+07:00 Ivan Sebastian layonardo@yahoo.co.id [belajar-excel] <belajar-excel@yahoogroups.com>:
data saya lampirkan... mohon bantuannya para master2 excel... thx..
#yiv4806722870 #yiv4806722870 -- #yiv4806722870ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv4806722870 #yiv4806722870ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv4806722870 #yiv4806722870ygrp-mkp #yiv4806722870hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv4806722870 #yiv4806722870ygrp-mkp #yiv4806722870ads {margin-bottom:10px;}#yiv4806722870 #yiv4806722870ygrp-mkp .yiv4806722870ad {padding:0 0;}#yiv4806722870 #yiv4806722870ygrp-mkp .yiv4806722870ad p {margin:0;}#yiv4806722870 #yiv4806722870ygrp-mkp .yiv4806722870ad a {color:#0000ff;text-decoration:none;}#yiv4806722870 #yiv4806722870ygrp-sponsor #yiv4806722870ygrp-lc {font-family:Arial;}#yiv4806722870 #yiv4806722870ygrp-sponsor #yiv4806722870ygrp-lc #yiv4806722870hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv4806722870 #yiv4806722870ygrp-sponsor #yiv4806722870ygrp-lc .yiv4806722870ad {margin-bottom:10px;padding:0 0;}#yiv4806722870 #yiv4806722870actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv4806722870 #yiv4806722870activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv4806722870 #yiv4806722870activity span {font-weight:700;}#yiv4806722870 #yiv4806722870activity span:first-child {text-transform:uppercase;}#yiv4806722870 #yiv4806722870activity span a {color:#5085b6;text-decoration:none;}#yiv4806722870 #yiv4806722870activity span span {color:#ff7900;}#yiv4806722870 #yiv4806722870activity span .yiv4806722870underline {text-decoration:underline;}#yiv4806722870 .yiv4806722870attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv4806722870 .yiv4806722870attach div a {text-decoration:none;}#yiv4806722870 .yiv4806722870attach img {border:none;padding-right:5px;}#yiv4806722870 .yiv4806722870attach label {display:block;margin-bottom:5px;}#yiv4806722870 .yiv4806722870attach label a {text-decoration:none;}#yiv4806722870 blockquote {margin:0 0 0 4px;}#yiv4806722870 .yiv4806722870bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv4806722870 .yiv4806722870bold a {text-decoration:none;}#yiv4806722870 dd.yiv4806722870last p a {font-family:Verdana;font-weight:700;}#yiv4806722870 dd.yiv4806722870last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv4806722870 dd.yiv4806722870last p span.yiv4806722870yshortcuts {margin-right:0;}#yiv4806722870 div.yiv4806722870attach-table div div a {text-decoration:none;}#yiv4806722870 div.yiv4806722870attach-table {width:400px;}#yiv4806722870 div.yiv4806722870file-title a, #yiv4806722870 div.yiv4806722870file-title a:active, #yiv4806722870 div.yiv4806722870file-title a:hover, #yiv4806722870 div.yiv4806722870file-title a:visited {text-decoration:none;}#yiv4806722870 div.yiv4806722870photo-title a, #yiv4806722870 div.yiv4806722870photo-title a:active, #yiv4806722870 div.yiv4806722870photo-title a:hover, #yiv4806722870 div.yiv4806722870photo-title a:visited {text-decoration:none;}#yiv4806722870 div#yiv4806722870ygrp-mlmsg #yiv4806722870ygrp-msg p a span.yiv4806722870yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv4806722870 .yiv4806722870green {color:#628c2a;}#yiv4806722870 .yiv4806722870MsoNormal {margin:0 0 0 0;}#yiv4806722870 o {font-size:0;}#yiv4806722870 #yiv4806722870photos div {float:left;width:72px;}#yiv4806722870 #yiv4806722870photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv4806722870 #yiv4806722870photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv4806722870 #yiv4806722870reco-category {font-size:77%;}#yiv4806722870 #yiv4806722870reco-desc {font-size:77%;}#yiv4806722870 .yiv4806722870replbq {margin:4px;}#yiv4806722870 #yiv4806722870ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv4806722870 #yiv4806722870ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv4806722870 #yiv4806722870ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv4806722870 #yiv4806722870ygrp-mlmsg select, #yiv4806722870 input, #yiv4806722870 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv4806722870 #yiv4806722870ygrp-mlmsg pre, #yiv4806722870 code {font:115% monospace;}#yiv4806722870 #yiv4806722870ygrp-mlmsg * {line-height:1.22em;}#yiv4806722870 #yiv4806722870ygrp-mlmsg #yiv4806722870logo {padding-bottom:10px;}#yiv4806722870 #yiv4806722870ygrp-msg p a {font-family:Verdana;}#yiv4806722870 #yiv4806722870ygrp-msg p#yiv4806722870attach-count span {color:#1E66AE;font-weight:700;}#yiv4806722870 #yiv4806722870ygrp-reco #yiv4806722870reco-head {color:#ff7900;font-weight:700;}#yiv4806722870 #yiv4806722870ygrp-reco {margin-bottom:20px;padding:0px;}#yiv4806722870 #yiv4806722870ygrp-sponsor #yiv4806722870ov li a {font-size:130%;text-decoration:none;}#yiv4806722870 #yiv4806722870ygrp-sponsor #yiv4806722870ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv4806722870 #yiv4806722870ygrp-sponsor #yiv4806722870ov ul {margin:0;padding:0 0 0 8px;}#yiv4806722870 #yiv4806722870ygrp-text {font-family:Georgia;}#yiv4806722870 #yiv4806722870ygrp-text p {margin:0 0 1em 0;}#yiv4806722870 #yiv4806722870ygrp-text tt {font-size:120%;}#yiv4806722870 #yiv4806722870ygrp-vital ul li:last-child {border-right:none !important;}#yiv4806722870
untuk jawaban no 1-3 diatasnota saya pastikan tidak akan bertambah.. karena memang sengaja saya buat seperti itu karena harga yg ada pada sheet pricelist tiap hari bisa berubah ubah setiap kali ada barang masuk pada hari itu... istilahnya nota harian lepas, begitu berganti hari buka file baru dan file data lama akan saya rename sesuai tanggal nota.. sehingga file baru akan selalu kosong isi awalnya.. benar seperti yg anda bilang.. 20 hari otomatis 20 nota.. rekap gabungan sudah ada bagiannya masing2 yg menanganinya....4. Kalau data nota ini sebenarnya hasil output dari suatu sistem, kenapa ada proses input manual lagi yang lalu di pindah ke cetak ?untuk jawaban no 4 ini..karena prosesnya seperti ini...orang beli barang kasih list item yg diminta apa... saya inputkan semua lalu saya tekan tombol next ke bagian sheet cetak nota dan nota saya cetak lalu saya kirimkan ke pegawai untuk menyiapkan barang2 sesuai dengan notanya.. semisal barangnya ternyata ada yg kurang maka nota yg ada di sheet cetak akan saya ubah sesuai dengan kondisi stock saat itu...jadi bila ada 2 org pembeli ... kalo saya tidak ada sheet input dan sheet cetak maka saya harus ketik ulank dari awal,dan customer ke2 harus menunggu customer 1 sampe bener2 selesai transaksinya...
Pada Rabu, 30 September 2015 16:14, "'Mr. Kid' mr.nmkid@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:
Cara yang saya sarankan juga pakai VBA (Macro) kok. Konsepnya malah lebih terstruktur daripada yang Anda rancang.
Jadi, bukan masalah pakai VBA atau tidak. Tapi lebih cenderung ke konsepnya, yaitu terstruktur atau tidak.
Kalau pertanyaannya ada cara lain atau tidak, tentu banyak, karena bisa mengkombinasikan berbagai fitur Excel yang bisa diotomasi dengan VBA.
Tapi sebenarnya, pertanyaan Anda adalah ada konsep lainnya selain konsep yang saya sarankan atau tidak...
Tentu jawabnya adalah banyak dan salah satunya adalah konsep yang Anda rancang.
Pertanyaan berikutnya dari Anda adalah dengan konsep yang Anda rancang tersebut bisa dibuatkan atau tidak...
Tentu jawabnya adalah tidak bisa, karena saya bisanya yang simpel-simpel saja. Kalau yang rumit-rumit mah bikin kepala pening.
Tapi entah BeExceller yang lebih mampu berpening ria ya... ditunggu saja...
btw,
coba konsep Anda direnungkan kembali,
1. Kalau nanti berganti hari ada nota masuk lagi, dimana data nota yang lama ? lalu seperti apa hasil rekapnya (menambahkan atau mengganti seluruh nilai rekapan) ?
2. Bagaimana cara merekap nota per bulan, triwulan, jangka waktu tertentu dsb... Tentu hal ini tidak dibutuhkan sekarang, tapi apa iya data akan dibiarkan menguap begitu saja setelah di-input ? Apa wajar kalau setiap inputan disimpan dalam 1 workbook yang berbeda (kalau sebulan ada 20 hari kerja ada 20 file dong... gimana merekap gabungan per bulannya ? gabungkan semua workbook ? cpd)
3. Apakah data ini berdiri sendiri dan tidak ada kaitannya dengan kegiatan lain dalam perusahaan ? Bagaimana cara menghubungkannya dengan kegiatan lain tersebut (jika ada kegiatan lainnya) ?
4. Kalau data nota ini sebenarnya hasil output dari suatu sistem, kenapa ada proses input manual lagi yang lalu di pindah ke cetak ?
kira-kira segitu saja deh...
dari yang 4 itu saja kan sudah bikin pening kalau suatu saat nanti dibutuhkan...
apa iya harus bangun dari nol lagi...
;)
met mencoba dan menunggu...
Wassalam,
Kid
On Wed, Sep 30, 2015 at 3:51 PM, Ivan Sebastian layonardo@yahoo.co.id [belajar-excel] <belajar-excel@yahoogroups.com> wrote:
terima kasih atas sarannya mr kidz... tapi apa ada cara lain selain itu? semisal pakai makro gt..ini file updatenya.. saya tambahkan rumus di bagian rekap nota...dan limit untuk sheet cetak nota saya bikin maks 24 nota untuk satu file...
Pada Selasa, 29 September 2015 20:00, "'Mr. Kid' mr.nmkid@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:
Hai Ivan,
Sebaiknya Anda membuat sebuah sheet yang menampung seluruh data inputan berupa sebuah tabel yang baik.
Misal Anda punya sheet inputan Nota berbentuk :
No Nota 12345
Nama a
Alamat fffff
Bla gjye
tanggal 2015-09-29
No pesanan kode barang kuantitas nilai
1 x 10 50
2 y 1 100
3 z 345 12345
(nomor pesanan tentu ada maksimalnya per lembar nota)
maka di sheet inputan sejajar dengan baris nomor pesanan di kolom yang tak terlihat dikanan sana dibuatkan formula untuk mengisi kolom berikut : susun kekanan dengan tampilan hasil formula yang Anda susun menjadi seperti ini (untuk contoh di atas)
No Nota Nama Alamat Bla tanggal No Pesanan Kode Barang Kuantitas Nilai
12345 a fffff gjye 2015-09-29 1 x 10 50
12345 a fffff gjye 2015-09-29 2 y 1 100
12345 a fffff gjye 2015-09-29 3 z 345 12345
Buat sebuah sheet baru bernama DATA berisi header :
No Nota Nama Alamat Bla tanggal No Pesanan Kode Barang Kuantitas Nilai
Nah, tombol yang ada di sheet input nota akan meng-copy area range berformula di sheet inputan nota (misal area yang dibiru) dan paste sepcial values ke baris kosong baru di sheet data.
Setelah itu, output yang dibuat :
1. sheet Cetak Nota bisa mengambil dari sheet Data dengan formula
2. sheet Rekap Barang bisa mengmabil dari sheet Data dengan formula
btw,
Hal diatas sekedar saran agar pengolahan datanya lebih luwes, dan dapat terus dikembangkan secara baik, cepat, tepat, dan mudah
Tidak ada paksaan untuk menerima saran diatas, karena hal pertama yang tampak adalah terasa ribet dan sulit.
Sayangnya, sudah ditetapkan bahwa kemudahan itu adanya setelah kesulitan
Wassalam,
Kid
2015-09-29 19:35 GMT+07:00 Ivan Sebastian layonardo@yahoo.co.id [belajar-excel] <belajar-excel@yahoogroups.com>:
data saya lampirkan... mohon bantuannya para master2 excel... thx..
#yiv4806722870 #yiv4806722870 -- #yiv4806722870ygrp-
Wed Sep 30, 2015 2:13 am (PDT) . Posted by:
"imam cahyo yuwono"
terima kasih Mr. Kid saya coba dulu. klo banayak tanya jangan marah ya.....
On Tuesday, September 29, 2015 7:32 PM, "'Mr. Kid' mr.nmkid@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com> wrote:
Wa'alaikumussalam wr wb
Hai Cahyo,
1. membuat combo box atau list (seperti search engine) yang saling berkaitan bagaimana pada sheet input kode dan nama penyakit kita bisa milih ( saya tidak bisa mengingat semua kode ICD maklum banyak, di cari dari nama penyakit)
> combobox memiliki properti bernama ColumnCount yang bisa menampilkan banyak kolom dalam daftar, untuk sumber daftar berisi banyak kolom. Properti BoundColumn adalah nomor indeks kolom yang akan menjadi isian properti Values. Properti TextColumn adalah indeks kolom dari daftar yang akan ditulis dalam combobox untuk item terpilih (isian properti Text).
> silakan diatur properti-ptoperti tersebut.
> Penggunaan ColumnCount lebih dari 1 terkadang membutuhkan pengaturan lebar kolom dalam daftar melalui properti ColumnWidth. Isiannya berurutan dari indeks kolom 1 sampai sejumlah isi ColumnCount. Contoh : 50pt;100pt berarti kolom ke-1 dalam daftar selebar 50pt dan kolom ke-2 selebar 100pt.
2. bagaimana sintax untuk macro perhitungan apa menggunakan sumproduct atau sumifs atau perhitungan lain karena yang menjadi patokan bulan, tempat, kriteria umur dan kode penyakit, mohon contoh (saya sudah coba dari beberappa referensi tapi gagal) referensi banyak tapi belum paham fungsi nya
> Fungsi SumIF sheet REKAP BULAN RI range E11 :
=SUMIFS(INDEX(Table1,0,COLUMNS('REKAP ALL'!$A$4:F$4)),INDEX(Table1,0,3),$C$4,INDEX(Table1,0,29),$C$5,INDEX(Table1,0,4),$C11)
> formula bisa di-copy ke baris dan kolom lain.
> untuk kolom tertentu yang tidak berurutan, lakukan penyesuaian rujukan nomor kolom (BUKAN NAMA KOLOM) dalam Table1yang akan dipakai untuk kalkulasi (yang akan di-sum), yaitu menggantikan bagian : COLUMNS('REKAP ALL'!$A$4:F$4)
> Jika akan diotomasi dengan VBA untuk mem-values-ken, formula di atas akan dipasang di E11:V20, baris kodenya :
application.calculation=xlcalculationautomatic
sheets("rekap bulan ini").range("e11:v20").formula="=SUMIFS(INDEX(Table1,0,COLUMNS('REKAP ALL'!$A$4:F$4)),INDEX(Table1,0,3),$C$4,INDEX(Table1,0,29),$C$5,INDEX(Table1,0,4),$C11)"
sheets("rekap bulan ini").range("e11:v20").value=sheets("rekap bulan ini").range("e11:v20").value> terapkan untuk area range lain dengan bunyi formula yang berbeda.
--------------
CATATAN :
1. susun formula universal yang bisa digunakan suatu area tertentu (misal area range SATU) pada sebuah cell dalam area tersebut
2. coba copy paste manual formula ke seluruh area range tertentu tersebut
3. jika sudah sesuai, maka usaha mem-values-ken dengan VBA bisa disusun dengan menyesuaikan rujukan sheet, range yang akan dipasangi formula dan bunyi formula yang akan dipasang.
4. sumif(s), CountIF(s), dsb bisa menerima wildcard character * dan ? yang bisa menjumlahkan dengan pattern tertentu.
5. jika kode memiliki pattern root dan child yang pasti, maka wildcard character bisa dimanfaatkan untuk mengkalkulasi total root.
Contoh : untuk menjumlah semua yang rootnya berkode A01 (bisa A01, A01.blablabla, A01.blublabli), maka bagian $C11 bisa diubah menjadi $C11&"*"
6. setiap 1 karakter " (petik dua) dalam formula disuatu cells harus diubah menjadi 2 buah karakter " (petik dua) ketika akan dimanfaatkan dalam VBA di atas.
--------------
3. pada sheet rekap dan rekap total, ingin buat auto copy code dan nama penyakit (baru bisa buat tombol filter aja he... he... )
> Lakukan usaha dengan cara manual lebih dulu. Jika sukses, barulah mencoba record macro. Kemudian ubah bagian tertentu hasil record macro agar mendapat suplai nilai dari cells yang menjadi area inputan user.
> Excel memiliki fitur Goto Special. Jadi, coba filter data sumber, blok area yang akan di-copy, CTRL G (Goto) -> tekan Special -> pilih visible cells -> OK, kemudian lakukan Copy (tekan CTRL C), lalu ke sheet tujuan, klik cell target paste, lakukan paste (tekan CTRL V).
3. untuk rekap triwulan, semester dan tahunan bagaiamana sudah cari di mbah google tapi belum nemu)
> Anda sudah bisa membuat data harian (sheet REKAP ALL) menjadi rekap bulanan (sheet REKAP BULAN INI) dengan bantuan kolom nama-nama bulan.
> Bukankah Anda bisa menerapkannya untuk triwulan dan segala macam pengelompokan yang dibutuhkan ? Dengan cara bagaimana hayo ?
4. pada sheet RL 4a dan 4b ada kelompok kode penyakit bagaimana cara menjumlahnya dengan macro contoh cell dengan isi kode A21.24-28. A31-32, 38-42-49 artinya kode A21 + A24 sampai dengan A28 + A31 sampai A32 + A38 + A42 sampai dengan A49 (itu kode urut ada di sheet ICD X ) kalo sulit ga apa apa paling di manual dulu yang penting untuk rekap saja untuk form RL bisa nyusul
> Masih ingat tentang rekap bulanan ? Kenapa kok bisa data harian yang bermacam-macam nilai tanggalnya menjadi terkumpul menyatu berdasar bulan tertentu ?
> Pada rekap bulanan, yang diproses untuk dibentuk pengelompokannya adalah berdasar kolom tanggal dan nama kelompoknya bisa disusun dengan formula.
> Pada kasus RL ini, Anda bisa menyusun kelompoknya juga, tetapi berdasar kolom kode. Jadi di REKAP ALL ada kolom yang menjelaskan kode tersebut akan masuk kelompok bernama apa (seperti tanggal tersebut masuk kelompok bernama bulan x misalnya)
> Pada sheet RL nya juga diperlukan nama kelompoknya disetiap baris data sheet RL.
> Fungsi SumIFs seperti di atas sana bisa Anda sesuaikan untuk kebutuhan penjumlahannya.
5. contoh contoh saja selanjut saya ingin belajar meneruskan
> coba deh Anda buat contohnya sendiri dengan mengikuti alur dongengan di atas...
Selamat mencoba.
Wassalamu'alaikum wr wb
Kid
On Mon, Sep 28, 2015 at 4:57 PM, imam cahyo yuwono chyywn@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com> wrote:
saya kirim ulang kembali data ada yang di delete hanya sebagian saja yang di munculkan
terima kasih
On Sunday, September 27, 2015 9:28 PM, "imam cahyo yuwono chyywn@yahoo.com [belajar-excel]" <belajar-excel@yahoogroups.com> wrote:
--- Mods ---
File lampiran yang melebihi 250KB dilepas dari email. Silakan penanya mengirim ulang file lampiran yang berisi data contoh dengan ukuran file yang diusahakan maksimal 250 KB dengan cara me-reply email ini.
----------------
Assalamualaikum Wr. Wb
Dear para master saya dapat tugas merekap data, sudah buat format untuk perhitungan saya menggunakan rumus sumproduct tapi file menjadi besar coba macro tapi tidak jalan yang saya tanyakan :
1. membuat combo box atau list (seperti search engine) yang saling berkaitan bagaimana pada sheet input kode dan nama penyakit kita bisa milih ( saya tidak bisa mengingat semua kode ICD maklum banyak, di cari dari nama penyakit)
2. bagaimana sintax untuk macro perhitungan apa menggunakan sumproduct atau sumifs atau perhitungan lain karena yang menjadi patokan bulan, tempat, kriteria umur dan kode penyakit, mohon contoh (saya sudah coba dari beberappa referensi tapi gagal) referensi banyak tapi belum paham fungsi nya
3. pada sheet rekap dan rekap total, ingin buat auto copy code dan nama penyakit (baru bisa buat tombol filter aja he... he... )
3. untuk rekap triwulan, semester dan tahunan bagaiamana sudah cari di mbah google tapi belum nemu)
4. pada sheet RL 4a dan 4b ada kelompok kode penyakit bagaimana cara menjumlahnya dengan macro contoh cell dengan isi kode A21.24-28. A31-32, 38-42-49 artinya kode A21 + A24 sampai dengan A28 + A31 sampai A32 + A38 + A42 sampai dengan A49 (itu kode urut ada di sheet ICD X ) kalo sulit ga apa apa paling di manual dulu yang penting untuk rekap saja untuk form RL bisa nyusul
5. contoh contoh saja selanjut saya ingin belajar meneruskan
mohon bantuannya maklum masih nubie
Wassalamualaikum Wr. Wb.
#yiv1258706507 #yiv1258706507 -- #yiv1258706507ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv1258706507 #yiv1258706507ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv1258706507 #yiv1258706507ygrp-mkp #yiv1258706507hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv1258706507 #yiv1258706507ygrp-mkp #yiv1258706507ads {margin-bottom:10px;}#yiv1258706507 #yiv1258706507ygrp-mkp .yiv1258706507ad {padding:0 0;}#yiv1258706507 #yiv1258706507ygrp-mkp .yiv1258706507ad p {margin:0;}#yiv1258706507 #yiv1258706507ygrp-mkp .yiv1258706507ad a {color:#0000ff;text-decoration:none;}#yiv1258706507 #yiv1258706507ygrp-sponsor #yiv1258706507ygrp-lc {font-family:Arial;}#yiv1258706507 #yiv1258706507ygrp-sponsor #yiv1258706507ygrp-lc #yiv1258706507hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv1258706507 #yiv1258706507ygrp-sponsor #yiv1258706507ygrp-lc .yiv1258706507ad {margin-bottom:10px;padding:0 0;}#yiv1258706507 #yiv1258706507actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv1258706507 #yiv1258706507activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv1258706507 #yiv1258706507activity span {font-weight:700;}#yiv1258706507 #yiv1258706507activity span:first-child {text-transform:uppercase;}#yiv1258706507 #yiv1258706507activity span a {color:#5085b6;text-decoration:none;}#yiv1258706507 #yiv1258706507activity span span {color:#ff7900;}#yiv1258706507 #yiv1258706507activity span .yiv1258706507underline {text-decoration:underline;}#yiv1258706507 .yiv1258706507attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv1258706507 .yiv1258706507attach div a {text-decoration:none;}#yiv1258706507 .yiv1258706507attach img {border:none;padding-right:5px;}#yiv1258706507 .yiv1258706507attach label {display:block;margin-bottom:5px;}#yiv1258706507 .yiv1258706507attach label a {text-decoration:none;}#yiv1258706507 blockquote {margin:0 0 0 4px;}#yiv1258706507 .yiv1258706507bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv1258706507 .yiv1258706507bold a {text-decoration:none;}#yiv1258706507 dd.yiv1258706507last p a {font-family:Verdana;font-weight:700;}#yiv1258706507 dd.yiv1258706507last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv1258706507 dd.yiv1258706507last p span.yiv1258706507yshortcuts {margin-right:0;}#yiv1258706507 div.yiv1258706507attach-table div div a {text-decoration:none;}#yiv1258706507 div.yiv1258706507attach-table {width:400px;}#yiv1258706507 div.yiv1258706507file-title a, #yiv1258706507 div.yiv1258706507file-title a:active, #yiv1258706507 div.yiv1258706507file-title a:hover, #yiv1258706507 div.yiv1258706507file-title a:visited {text-decoration:none;}#yiv1258706507 div.yiv1258706507photo-title a, #yiv1258706507 div.yiv1258706507photo-title a:active, #yiv1258706507 div.yiv1258706507photo-title a:hover, #yiv1258706507 div.yiv1258706507photo-title a:visited {text-decoration:none;}#yiv1258706507 div#yiv1258706507ygrp-mlmsg #yiv1258706507ygrp-msg p a span.yiv1258706507yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv1258706507 .yiv1258706507green {color:#628c2a;}#yiv1258706507 .yiv1258706507MsoNormal {margin:0 0 0 0;}#yiv1258706507 o {font-size:0;}#yiv1258706507 #yiv1258706507photos div {float:left;width:72px;}#yiv1258706507 #yiv1258706507photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv1258706507 #yiv1258706507photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv1258706507 #yiv1258706507reco-category {font-size:77%;}#yiv1258706507 #yiv1258706507reco-desc {font-size:77%;}#yiv1258706507 .yiv1258706507replbq {margin:4px;}#yiv1258706507 #yiv1258706507ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv1258706507 #yiv1258706507ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv1258706507 #yiv1258706507ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv1258706507 #yiv1258706507ygrp-mlmsg select, #yiv1258706507 input, #yiv1258706507 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv1258706507 #yiv1258706507ygrp-mlmsg pre, #yiv1258706507 code {font:115% monospace;}#yiv1258706507 #yiv1258706507ygrp-mlmsg * {line-height:1.22em;}#yiv1258706507 #yiv1258706507ygrp-mlmsg #yiv1258706507logo {padding-bottom:10px;}#yiv1258706507 #yiv1258706507ygrp-msg p a {font-family:Verdana;}#yiv1258706507 #yiv1258706507ygrp-msg p#yiv1258706507attach-count span {color:#1E66AE;font-weight:700;}#yiv1258706507 #yiv1258706507ygrp-reco #yiv1258706507reco-head {color:#ff7900;font-weight:700;}#yiv1258706507 #yiv1258706507ygrp-reco {margin-bottom:20px;padding:0px;}#yiv1258706507 #yiv1258706507ygrp-sponsor #yiv1258706507ov li a {font-size:130%;text-decoration:none;}#yiv1258706507 #yiv1258706507ygrp-sponsor #yiv1258706507ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv1258706507 #yiv1258706507ygrp-sponsor #yiv1258706507ov ul {margin:0;padding:0 0 0 8px;}#yiv1258706507 #yiv1258706507ygrp-text {font-family:Georgia;}#yiv1258706507 #yiv1258706507ygrp-text p {margin:0 0 1em 0;}#yiv1258706507 #yiv1258706507ygrp-text tt {font-size:120%;}#yiv1258706507 #yiv1258706507ygrp-vital ul li:last-child {border-right:none !important;}#yiv1258706507
On Tuesday, September 29, 2015 7:32 PM, "'Mr. Kid' mr.nmkid@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com> wrote:
Wa'alaikumussalam wr wb
Hai Cahyo,
1. membuat combo box atau list (seperti search engine) yang saling berkaitan bagaimana pada sheet input kode dan nama penyakit kita bisa milih ( saya tidak bisa mengingat semua kode ICD maklum banyak, di cari dari nama penyakit)
> combobox memiliki properti bernama ColumnCount yang bisa menampilkan banyak kolom dalam daftar, untuk sumber daftar berisi banyak kolom. Properti BoundColumn adalah nomor indeks kolom yang akan menjadi isian properti Values. Properti TextColumn adalah indeks kolom dari daftar yang akan ditulis dalam combobox untuk item terpilih (isian properti Text).
> silakan diatur properti-ptoperti tersebut.
> Penggunaan ColumnCount lebih dari 1 terkadang membutuhkan pengaturan lebar kolom dalam daftar melalui properti ColumnWidth. Isiannya berurutan dari indeks kolom 1 sampai sejumlah isi ColumnCount. Contoh : 50pt;100pt berarti kolom ke-1 dalam daftar selebar 50pt dan kolom ke-2 selebar 100pt.
2. bagaimana sintax untuk macro perhitungan apa menggunakan sumproduct atau sumifs atau perhitungan lain karena yang menjadi patokan bulan, tempat, kriteria umur dan kode penyakit, mohon contoh (saya sudah coba dari beberappa referensi tapi gagal) referensi banyak tapi belum paham fungsi nya
> Fungsi SumIF sheet REKAP BULAN RI range E11 :
=SUMIFS(INDEX(Table1,0,COLUMNS('REKAP ALL'!$A$4:F$4)),INDEX(Table1,0,3),$C$4,INDEX(Table1,0,29),$C$5,INDEX(Table1,0,4),$C11)
> formula bisa di-copy ke baris dan kolom lain.
> untuk kolom tertentu yang tidak berurutan, lakukan penyesuaian rujukan nomor kolom (BUKAN NAMA KOLOM) dalam Table1yang akan dipakai untuk kalkulasi (yang akan di-sum), yaitu menggantikan bagian : COLUMNS('REKAP ALL'!$A$4:F$4)
> Jika akan diotomasi dengan VBA untuk mem-values-ken, formula di atas akan dipasang di E11:V20, baris kodenya :
application.calculation=xlcalculationautomatic
sheets("rekap bulan ini").range("e11:v20").formula="=SUMIFS(INDEX(Table1,0,COLUMNS('REKAP ALL'!$A$4:F$4)),INDEX(Table1,0,3),$C$4,INDEX(Table1,0,29),$C$5,INDEX(Table1,0,4),$C11)"
sheets("rekap bulan ini").range("e11:v20").value=sheets("rekap bulan ini").range("e11:v20").value> terapkan untuk area range lain dengan bunyi formula yang berbeda.
--------------
CATATAN :
1. susun formula universal yang bisa digunakan suatu area tertentu (misal area range SATU) pada sebuah cell dalam area tersebut
2. coba copy paste manual formula ke seluruh area range tertentu tersebut
3. jika sudah sesuai, maka usaha mem-values-ken dengan VBA bisa disusun dengan menyesuaikan rujukan sheet, range yang akan dipasangi formula dan bunyi formula yang akan dipasang.
4. sumif(s), CountIF(s), dsb bisa menerima wildcard character * dan ? yang bisa menjumlahkan dengan pattern tertentu.
5. jika kode memiliki pattern root dan child yang pasti, maka wildcard character bisa dimanfaatkan untuk mengkalkulasi total root.
Contoh : untuk menjumlah semua yang rootnya berkode A01 (bisa A01, A01.blablabla, A01.blublabli), maka bagian $C11 bisa diubah menjadi $C11&"*"
6. setiap 1 karakter " (petik dua) dalam formula disuatu cells harus diubah menjadi 2 buah karakter " (petik dua) ketika akan dimanfaatkan dalam VBA di atas.
--------------
3. pada sheet rekap dan rekap total, ingin buat auto copy code dan nama penyakit (baru bisa buat tombol filter aja he... he... )
> Lakukan usaha dengan cara manual lebih dulu. Jika sukses, barulah mencoba record macro. Kemudian ubah bagian tertentu hasil record macro agar mendapat suplai nilai dari cells yang menjadi area inputan user.
> Excel memiliki fitur Goto Special. Jadi, coba filter data sumber, blok area yang akan di-copy, CTRL G (Goto) -> tekan Special -> pilih visible cells -> OK, kemudian lakukan Copy (tekan CTRL C), lalu ke sheet tujuan, klik cell target paste, lakukan paste (tekan CTRL V).
3. untuk rekap triwulan, semester dan tahunan bagaiamana sudah cari di mbah google tapi belum nemu)
> Anda sudah bisa membuat data harian (sheet REKAP ALL) menjadi rekap bulanan (sheet REKAP BULAN INI) dengan bantuan kolom nama-nama bulan.
> Bukankah Anda bisa menerapkannya untuk triwulan dan segala macam pengelompokan yang dibutuhkan ? Dengan cara bagaimana hayo ?
4. pada sheet RL 4a dan 4b ada kelompok kode penyakit bagaimana cara menjumlahnya dengan macro contoh cell dengan isi kode A21.24-28. A31-32, 38-42-49 artinya kode A21 + A24 sampai dengan A28 + A31 sampai A32 + A38 + A42 sampai dengan A49 (itu kode urut ada di sheet ICD X ) kalo sulit ga apa apa paling di manual dulu yang penting untuk rekap saja untuk form RL bisa nyusul
> Masih ingat tentang rekap bulanan ? Kenapa kok bisa data harian yang bermacam-macam nilai tanggalnya menjadi terkumpul menyatu berdasar bulan tertentu ?
> Pada rekap bulanan, yang diproses untuk dibentuk pengelompokannya adalah berdasar kolom tanggal dan nama kelompoknya bisa disusun dengan formula.
> Pada kasus RL ini, Anda bisa menyusun kelompoknya juga, tetapi berdasar kolom kode. Jadi di REKAP ALL ada kolom yang menjelaskan kode tersebut akan masuk kelompok bernama apa (seperti tanggal tersebut masuk kelompok bernama bulan x misalnya)
> Pada sheet RL nya juga diperlukan nama kelompoknya disetiap baris data sheet RL.
> Fungsi SumIFs seperti di atas sana bisa Anda sesuaikan untuk kebutuhan penjumlahannya.
5. contoh contoh saja selanjut saya ingin belajar meneruskan
> coba deh Anda buat contohnya sendiri dengan mengikuti alur dongengan di atas...
Selamat mencoba.
Wassalamu'alaikum wr wb
Kid
On Mon, Sep 28, 2015 at 4:57 PM, imam cahyo yuwono chyywn@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com> wrote:
saya kirim ulang kembali data ada yang di delete hanya sebagian saja yang di munculkan
terima kasih
On Sunday, September 27, 2015 9:28 PM, "imam cahyo yuwono chyywn@yahoo.com [belajar-excel]" <belajar-excel@yahoogroups.com> wrote:
--- Mods ---
File lampiran yang melebihi 250KB dilepas dari email. Silakan penanya mengirim ulang file lampiran yang berisi data contoh dengan ukuran file yang diusahakan maksimal 250 KB dengan cara me-reply email ini.
------------
Assalamualaikum Wr. Wb
Dear para master saya dapat tugas merekap data, sudah buat format untuk perhitungan saya menggunakan rumus sumproduct tapi file menjadi besar coba macro tapi tidak jalan yang saya tanyakan :
1. membuat combo box atau list (seperti search engine) yang saling berkaitan bagaimana pada sheet input kode dan nama penyakit kita bisa milih ( saya tidak bisa mengingat semua kode ICD maklum banyak, di cari dari nama penyakit)
2. bagaimana sintax untuk macro perhitungan apa menggunakan sumproduct atau sumifs atau perhitungan lain karena yang menjadi patokan bulan, tempat, kriteria umur dan kode penyakit, mohon contoh (saya sudah coba dari beberappa referensi tapi gagal) referensi banyak tapi belum paham fungsi nya
3. pada sheet rekap dan rekap total, ingin buat auto copy code dan nama penyakit (baru bisa buat tombol filter aja he... he... )
3. untuk rekap triwulan, semester dan tahunan bagaiamana sudah cari di mbah google tapi belum nemu)
4. pada sheet RL 4a dan 4b ada kelompok kode penyakit bagaimana cara menjumlahnya dengan macro contoh cell dengan isi kode A21.24-28. A31-32, 38-42-49 artinya kode A21 + A24 sampai dengan A28 + A31 sampai A32 + A38 + A42 sampai dengan A49 (itu kode urut ada di sheet ICD X ) kalo sulit ga apa apa paling di manual dulu yang penting untuk rekap saja untuk form RL bisa nyusul
5. contoh contoh saja selanjut saya ingin belajar meneruskan
mohon bantuannya maklum masih nubie
Wassalamualaikum Wr. Wb.
#yiv1258706507 #yiv1258706507 -- #yiv1258706507ygrp-
Wed Sep 30, 2015 3:36 am (PDT) . Posted by:
"hendrik karnadi" hendrikkarnadi
Dear Be-Excelers,
Berikut adalah perintah Macro yang saya dapat dari internet (file terlampir) untuk menggabungkan data (non unique) dari beberapa file yang ada dalam satu folder (Path),
Sub Consolidate()
Dim sSQL As String 'SQL String Dim oLr As ListRow 'Worksheets Row Dim cn As Object 'Connection Dim rs As Object 'Recordset
' Create SQL For Each oLr In Sheet1.ListObjects("Worksheets").ListRows If sSQL <> "" Then sSQL = sSQL & vbCr & "Union All" & vbCr 'tidak unik sSQL = sSQL & "Select * From " & oLr.Range(1) Next sSQL = Replace(sSQL, "<Path>", ThisWorkbook.Path) ' Create Connection Objects Set cn = CreateObject("ADODB.Connection") Set rs = CreateObject("ADODB.Recordset")
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & ThisWorkbook.FullName & ";" & _ "Extended Properties=""Excel 12.0 Xml;HDR=YES"";" rs.Open sSQL, cn Debug.Print sSQL
If Sheet1.ListObjects.Count > 1 Then Sheet1.ListObjects(2).Delete Sheet1.ListObjects.Add( _ SourceType:=xlSrcQuery, _ Source:=rs, _ Destination:=Sheet1.Range("C6")).QueryTable.Refresh
rs.Close cn.Close
Set rs = Nothing Set cn = Nothing
End Sub
Yang ingin saya tanyakan adalah bagaimana bunyi perintah SQL nya (warna biru) jika yang ingin ditampilkan adalah total per no rek (bukan detailnya).
Terima kasih.
Salam,HK
Berikut adalah perintah Macro yang saya dapat dari internet (file terlampir) untuk menggabungkan data (non unique) dari beberapa file yang ada dalam satu folder (Path),
Sub Consolidate(
Dim sSQL As String 'SQL String Dim oLr As ListRow 'Worksheets Row Dim cn As Object 'Connection Dim rs As Object 'Recordset
' Create SQL For Each oLr In Sheet1.ListObjects(
cn.Open "Provider=
If Sheet1.ListObjects.
rs.Close cn.Close
Set rs = Nothing Set cn = Nothing
End Sub
Yang ingin saya tanyakan adalah bagaimana bunyi perintah SQL nya (warna biru) jika yang ingin ditampilkan adalah total per no rek (bukan detailnya).
Terima kasih.
Salam,HK
Wed Sep 30, 2015 6:58 pm (PDT) . Posted by:
"Mr. Kid" nmkid.family@ymail.com
Hai Pak HK,
1. Query dasar agregat yang memiliki keys (agregat per anything) tanpa
filter terhadap hasil agregat
SELECT your_key_columns, your_aggregat_ecpression
FROM your_source
GROUP BY your_key_columns_without_columns_aliasses
contoh : total penjualan per tahun (berdasar tanggal invoice) per produk
SELECT produk_id, year(tgl_invoice) as tahun, sum(penjualan) as total_jual
FROM dataINV
GROUP BY produk_id, year(tgl_invoice) --> tanpa aliasses
2. Query dasar UNION [ALL]
Query1
UNION [ALL]
Query2
UNION [ALL]
.
.
UNION [ALL]
QueryN
dengan setiap Query# berupa select query yang memiliki susunan sesuai
kebutuhan.
Urutan kolom dan *tipe datanya *dari setiap Query# adalah tetap, dengan
definisi kolom di Query1 yang menjadi nama kolom output
3. Susunan select query dasar
SELECT your_columns [, your_aggregate_expressions]
FROM your_source
[
[JOIN your_join1 ON your_join1_relations]
.
.
[JOIN your_joinN ON your_joinN_relations]
]
[
WHERE your_filter_expressions
]
[
GROUP BY
your_columns_except_aggregate_expressions_without_columns_aliasses
[HAVING your_aggregate_filter_expressions] --> berisi filter
terhadap kolom-kolom agregat, dan bukan terhadap key columns
]
[
UNION [ALL]
your_another_query2
.
.
UNION [ALL]
your_another_queryN
]
[
ORDER BY your_sort_expressions
]
gunakan klausa ORDER BY secara bijak dan usahakan dihindari kecuali penting
sekali diseakhir mungkin suatu proses, karena klausa ORDER BY termasuk
computer resources eater
4. source dari query lain (subquery di bagian FROM)
SELECT your_dataset_alias#.your_columns
FROM
(
queryS
) as your_dataset_alias1
[another query clauses]
queryS bisa berisi select query dasar seperti di-nomor 3 tanpa klausa ORDER
BY
subquery suatu query seperti queryS juga bisa diletakkan pada bagian sumber
data yang di-JOIN
setiap Query# dalam UNION [ALL] bisa berisi subquery suatu query seperti
queryS
-----------------------
Prinsip dasar ber-query :
1. ketahui sumber data yang akan diproses
2. pahami alur komputasi yang dibutuhkan
3. susun query berdasar 1 dan 2
-----------------------
Jadi, *menggabungkan data hasil agregat* adalah hal yang berbeda
dengan *mengagregat
hasil gabungan data*
> *menggabungkan data hasil agregat *
SELECT your_key_columns, your_aggregate_expressions
FROM your_source1
GROUP BY your_key_columns_without_columns_aliasses
UNION [ALL]
SELECT your_key_columns, your_aggregate_expressions
FROM your_source2
GROUP BY your_key_columns_without_columns_aliasses
.
.
UNION [ALL]
SELECT your_key_columns, your_aggregate_expressions
FROM your_sourceN
GROUP BY your_key_columns_without_columns_aliasses
hasilnya bisa jadi ada your_columns yang sama dengan nilai your_aggregate
apa saja jika dengan [ALL] atau ada hasil aggregate yang hilang jika tanpa
[ALL]
> *mengagregat hasil gabungan data*
SELECT your_key_columns, your_aggregate_expressions
FROM
(
SELECT your_key_columns
FROM your_source1
UNION [ALL]
SELECT your_key_columns
FROM your_source2
.
.
UNION [ALL]
SELECT your_key_columns
FROM your_sourceN
) as your_dataset_alias
GROUP BY your_key_columns_without_columns_aliasses
Regard,
Kid
On Wed, Sep 30, 2015 at 5:36 PM, hendrik karnadi hendrikkarnadi@yahoo.com
[belajar-excel] <belajar-excel@yahoogroups.com> wrote:
>
>
> Dear Be-Excelers,
>
> Berikut adalah perintah Macro yang saya dapat dari internet (file
> terlampir) untuk menggabungkan data (non unique) dari beberapa file yang
> ada dalam satu folder (Path),
>
> Sub Consolidate()
>
> Dim sSQL As String 'SQL String
> Dim oLr As ListRow 'Worksheets Row
> Dim cn As Object 'Connection
> Dim rs As Object 'Recordset
>
> ' Create SQL
> For Each oLr In Sheet1.ListObjects("Worksheets").ListRows
> If sSQL <> "" Then sSQL = sSQL & vbCr & "Union All" & vbCr 'tidak
> unik
> * sSQL = sSQL & "Select * From " & oLr.Range(1)*
> Next
> sSQL = Replace(sSQL, "<Path>", ThisWorkbook.Path)
>
> ' Create Connection Objects
> Set cn = CreateObject("ADODB.Connection")
> Set rs = CreateObject("ADODB.Recordset")
>
> cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
> "Data Source=" & ThisWorkbook.FullName & ";" & _
> "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
> rs.Open sSQL, cn
> Debug.Print sSQL
>
> If Sheet1.ListObjects.Count > 1 Then Sheet1.ListObjects(2).Delete
> Sheet1.ListObjects.Add( _
> SourceType:=xlSrcQuery, _
> Source:=rs, _
> Destination:=Sheet1.Range("C6")).QueryTable.Refresh
>
> rs.Close
> cn.Close
>
> Set rs = Nothing
> Set cn = Nothing
>
> End Sub
>
> Yang ingin saya tanyakan adalah bagaimana bunyi perintah SQL nya (warna
> biru) jika yang ingin ditampilkan adalah *total* per no rek (*bukan
> detail*nya).
>
> Terima kasih.
>
> Salam,
> HK
>
>
>
1. Query dasar agregat yang memiliki keys (agregat per anything) tanpa
filter terhadap hasil agregat
SELECT your_key_columns, your_aggregat_ecpression
FROM your_source
GROUP BY your_key_columns_without_columns_aliasses
contoh : total penjualan per tahun (berdasar tanggal invoice) per produk
SELECT produk_id, year(tgl_invoice) as tahun, sum(penjualan) as total_jual
FROM dataINV
GROUP BY produk_id, year(tgl_invoice) --> tanpa aliasses
2. Query dasar UNION [ALL]
Query1
UNION [ALL]
Query2
UNION [ALL]
.
.
UNION [ALL]
QueryN
dengan setiap Query# berupa select query yang memiliki susunan sesuai
kebutuhan.
Urutan kolom dan *tipe datanya *dari setiap Query# adalah tetap, dengan
definisi kolom di Query1 yang menjadi nama kolom output
3. Susunan select query dasar
SELECT your_columns [, your_aggregate_expressions]
FROM your_source
[
[JOIN your_join1 ON your_join1_relations]
.
.
[JOIN your_joinN ON your_joinN_relations]
]
[
WHERE your_filter_expressions
]
[
GROUP BY
your_columns_except_aggregate_expressions_without_columns_aliasses
[HAVING your_aggregate_filter_expressions] --> berisi filter
terhadap kolom-kolom agregat, dan bukan terhadap key columns
]
[
UNION [ALL]
your_another_query2
.
.
UNION [ALL]
your_another_queryN
]
[
ORDER BY your_sort_expressions
]
gunakan klausa ORDER BY secara bijak dan usahakan dihindari kecuali penting
sekali diseakhir mungkin suatu proses, karena klausa ORDER BY termasuk
computer resources eater
4. source dari query lain (subquery di bagian FROM)
SELECT your_dataset_alias#.your_columns
FROM
(
queryS
) as your_dataset_alias1
[another query clauses]
queryS bisa berisi select query dasar seperti di-nomor 3 tanpa klausa ORDER
BY
subquery suatu query seperti queryS juga bisa diletakkan pada bagian sumber
data yang di-JOIN
setiap Query# dalam UNION [ALL] bisa berisi subquery suatu query seperti
queryS
-----------------------
Prinsip dasar ber-query :
1. ketahui sumber data yang akan diproses
2. pahami alur komputasi yang dibutuhkan
3. susun query berdasar 1 dan 2
-----------------------
Jadi, *menggabungkan data hasil agregat* adalah hal yang berbeda
dengan *mengagregat
hasil gabungan data*
> *menggabungkan data hasil agregat *
SELECT your_key_columns, your_aggregate_expressions
FROM your_source1
GROUP BY your_key_columns_without_columns_aliasses
UNION [ALL]
SELECT your_key_columns, your_aggregate_expressions
FROM your_source2
GROUP BY your_key_columns_without_columns_aliasses
.
.
UNION [ALL]
SELECT your_key_columns, your_aggregate_expressions
FROM your_sourceN
GROUP BY your_key_columns_without_columns_aliasses
hasilnya bisa jadi ada your_columns yang sama dengan nilai your_aggregate
apa saja jika dengan [ALL] atau ada hasil aggregate yang hilang jika tanpa
[ALL]
> *mengagregat hasil gabungan data*
SELECT your_key_columns, your_aggregate_expressions
FROM
(
SELECT your_key_columns
FROM your_source1
UNION [ALL]
SELECT your_key_columns
FROM your_source2
.
.
UNION [ALL]
SELECT your_key_columns
FROM your_sourceN
) as your_dataset_alias
GROUP BY your_key_columns_without_columns_aliasses
Regard,
Kid
On Wed, Sep 30, 2015 at 5:36 PM, hendrik karnadi hendrikkarnadi@yahoo.com
[belajar-excel] <belajar-excel@yahoogroups.com> wrote:
>
>
> Dear Be-Excelers,
>
> Berikut adalah perintah Macro yang saya dapat dari internet (file
> terlampir) untuk menggabungkan data (non unique) dari beberapa file yang
> ada dalam satu folder (Path),
>
> Sub Consolidate(
>
> Dim sSQL As String 'SQL String
> Dim oLr As ListRow 'Worksheets Row
> Dim cn As Object 'Connection
> Dim rs As Object 'Recordset
>
> ' Create SQL
> For Each oLr In Sheet1.ListObjects(
> If sSQL <> "" Then sSQL = sSQL & vbCr & "Union All" & vbCr 'tidak
> unik
> * sSQL = sSQL & "Select * From " & oLr.Range(1)
> Next
> sSQL = Replace(sSQL, "<Path>
>
> ' Create Connection Objects
> Set cn = CreateObject(
> Set rs = CreateObject(
>
> cn.Open "Provider=
> "Data Source=" & ThisWorkbook.
> "Extended Properties="
> rs.Open sSQL, cn
> Debug.Print sSQL
>
> If Sheet1.ListObjects.
> Sheet1.ListObjects.
> SourceType:=
> Source:=rs, _
> Destination:
>
> rs.Close
> cn.Close
>
> Set rs = Nothing
> Set cn = Nothing
>
> End Sub
>
> Yang ingin saya tanyakan adalah bagaimana bunyi perintah SQL nya (warna
> biru) jika yang ingin ditampilkan adalah *total* per no rek (*bukan
> detail*nya).
>
> Terima kasih.
>
> Salam,
> HK
>
>
>
Wed Sep 30, 2015 8:11 pm (PDT) . Posted by:
"hendrik karnadi" hendrikkarnadi
Mr. Kid,
Terima kasih atas penjelasannya.
Saya coba ganti perintah :sSQL = sSQL & "Select * From " & oLr.Range(1)
dengan
sSQL = sSQL & "Select Account, Description, Sum(Debits) as Total_Debit, Sum(Credits) as Total_Credit From " & oLr.Range(1) & vbCrGROUP BY Account
Ketika macro dijalankan keluar message "Syntax Error" pada huruf yang berwarna merah. Dimana salahnya ya ?
Terima kasih.
Salam,HK
From: "'Mr. Kid' mr.nmkid@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com>
To: BeExcel <belajar-excel@yahoogroups.com>
Sent: Thursday, 1 October 2015, 8:58
Subject: Re: [belajar-excel] Perintah SQL untuk menggabungkan data sekaligus menjumlahkan
Hai Pak HK,
1. Query dasar agregat yang memiliki keys (agregat per anything) tanpa filter terhadap hasil agregat
SELECT your_key_columns, your_aggregat_ecpression
FROM your_source
GROUP BY your_key_columns_without_columns_aliasses
contoh : total penjualan per tahun (berdasar tanggal invoice) per produk
SELECT produk_id, year(tgl_invoice) as tahun, sum(penjualan) as total_jual
FROM dataINV
GROUP BY produk_id, year(tgl_invoice) --> tanpa aliasses
2. Query dasar UNION [ALL]
Query1
UNION [ALL]
Query2
UNION [ALL]
.
.
UNION [ALL]
QueryN
dengan setiap Query# berupa select query yang memiliki susunan sesuai kebutuhan.
Urutan kolom dan tipe datanya dari setiap Query# adalah tetap, dengan definisi kolom di Query1 yang menjadi nama kolom output
3. Susunan select query dasar
SELECT your_columns [, your_aggregate_expressions]
FROM your_source
[
[JOIN your_join1 ON your_join1_relations]
.
.
[JOIN your_joinN ON your_joinN_relations]
]
[
WHERE your_filter_expressions
]
[
GROUP BY your_columns_except_aggregate_expressions_without_columns_aliasses
[HAVING your_aggregate_filter_expressions] --> berisi filter terhadap kolom-kolom agregat, dan bukan terhadap key columns
]
[
UNION [ALL]
your_another_query2
.
.
UNION [ALL]
your_another_queryN
]
[
ORDER BY your_sort_expressions
]
gunakan klausa ORDER BY secara bijak dan usahakan dihindari kecuali penting sekali diseakhir mungkin suatu proses, karena klausa ORDER BY termasuk computer resources eater
4. source dari query lain (subquery di bagian FROM)
SELECT your_dataset_alias#.your_columns
FROM
(
queryS
) as your_dataset_alias1
[another query clauses]
queryS bisa berisi select query dasar seperti di-nomor 3 tanpa klausa ORDER BY
subquery suatu query seperti queryS juga bisa diletakkan pada bagian sumber data yang di-JOIN
setiap Query# dalam UNION [ALL] bisa berisi subquery suatu query seperti queryS
-----------------------
Prinsip dasar ber-query :
1. ketahui sumber data yang akan diproses
2. pahami alur komputasi yang dibutuhkan
3. susun query berdasar 1 dan 2
-----------------------
Jadi, menggabungkan data hasil agregat adalah hal yang berbeda dengan mengagregat hasil gabungan data
> menggabungkan data hasil agregat
SELECT your_key_columns, your_aggregate_expressions
FROM your_source1
GROUP BY your_key_columns_without_columns_aliasses
UNION [ALL]
SELECT your_key_columns, your_aggregate_expressions
FROM your_source2
GROUP BY your_key_columns_without_columns_aliasses
.
.
UNION [ALL]
SELECT your_key_columns, your_aggregate_expressions
FROM your_sourceN
GROUP BY your_key_columns_without_columns_aliasses
hasilnya bisa jadi ada your_columns yang sama dengan nilai your_aggregate apa saja jika dengan [ALL] atau ada hasil aggregate yang hilang jika tanpa [ALL]
> mengagregat hasil gabungan dataSELECT your_key_columns, your_aggregate_expressionsFROM
(
SELECT your_key_columns
FROM your_source1
UNION [ALL]
SELECT your_key_columns
FROM your_source2
.
.
UNION [ALL]
SELECT your_key_columns
FROM your_sourceN
) as your_dataset_alias
GROUP BY your_key_columns_without_columns_aliasses
Regard,
Kid
On Wed, Sep 30, 2015 at 5:36 PM, hendrik karnadi hendrikkarnadi@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com> wrote:
Dear Be-Excelers,
Berikut adalah perintah Macro yang saya dapat dari internet (file terlampir) untuk menggabungkan data (non unique) dari beberapa file yang ada dalam satu folder (Path),
Sub Consolidate()
Dim sSQL As String 'SQL String Dim oLr As ListRow 'Worksheets Row Dim cn As Object 'Connection Dim rs As Object 'Recordset
' Create SQL For Each oLr In Sheet1.ListObjects("Worksheets").ListRows If sSQL <> "" Then sSQL = sSQL & vbCr & "Union All" & vbCr 'tidak unik sSQL = sSQL & "Select * From " & oLr.Range(1) Next sSQL = Replace(sSQL, "<Path>", ThisWorkbook.Path) ' Create Connection Objects Set cn = CreateObject("ADODB.Connection") Set rs = CreateObject("ADODB.Recordset")
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & ThisWorkbook.FullName & ";" & _ "Extended Properties=""Excel 12.0 Xml;HDR=YES"";" rs.Open sSQL, cn Debug.Print sSQL
If Sheet1.ListObjects.Count > 1 Then Sheet1.ListObjects(2).Delete Sheet1.ListObjects.Add( _ SourceType:=xlSrcQuery, _ Source:=rs, _ Destination:=Sheet1.Range("C6")).QueryTable.Refresh
rs.Close cn.Close
Set rs = Nothing Set cn = Nothing
End Sub
Yang ingin saya tanyakan adalah bagaimana bunyi perintah SQL nya (warna biru) jika yang ingin ditampilkan adalah total per no rek (bukan detailnya).
Terima kasih.
Salam,HK
#yiv0784936447 #yiv0784936447 -- #yiv0784936447ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv0784936447 #yiv0784936447ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv0784936447 #yiv0784936447ygrp-mkp #yiv0784936447hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv0784936447 #yiv0784936447ygrp-mkp #yiv0784936447ads {margin-bottom:10px;}#yiv0784936447 #yiv0784936447ygrp-mkp .yiv0784936447ad {padding:0 0;}#yiv0784936447 #yiv0784936447ygrp-mkp .yiv0784936447ad p {margin:0;}#yiv0784936447 #yiv0784936447ygrp-mkp .yiv0784936447ad a {color:#0000ff;text-decoration:none;}#yiv0784936447 #yiv0784936447ygrp-sponsor #yiv0784936447ygrp-lc {font-family:Arial;}#yiv0784936447 #yiv0784936447ygrp-sponsor #yiv0784936447ygrp-lc #yiv0784936447hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv0784936447 #yiv0784936447ygrp-sponsor #yiv0784936447ygrp-lc .yiv0784936447ad {margin-bottom:10px;padding:0 0;}#yiv0784936447 #yiv0784936447actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv0784936447 #yiv0784936447activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv0784936447 #yiv0784936447activity span {font-weight:700;}#yiv0784936447 #yiv0784936447activity span:first-child {text-transform:uppercase;}#yiv0784936447 #yiv0784936447activity span a {color:#5085b6;text-decoration:none;}#yiv0784936447 #yiv0784936447activity span span {color:#ff7900;}#yiv0784936447 #yiv0784936447activity span .yiv0784936447underline {text-decoration:underline;}#yiv0784936447 .yiv0784936447attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv0784936447 .yiv0784936447attach div a {text-decoration:none;}#yiv0784936447 .yiv0784936447attach img {border:none;padding-right:5px;}#yiv0784936447 .yiv0784936447attach label {display:block;margin-bottom:5px;}#yiv0784936447 .yiv0784936447attach label a {text-decoration:none;}#yiv0784936447 blockquote {margin:0 0 0 4px;}#yiv0784936447 .yiv0784936447bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv0784936447 .yiv0784936447bold a {text-decoration:none;}#yiv0784936447 dd.yiv0784936447last p a {font-family:Verdana;font-weight:700;}#yiv0784936447 dd.yiv0784936447last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv0784936447 dd.yiv0784936447last p span.yiv0784936447yshortcuts {margin-right:0;}#yiv0784936447 div.yiv0784936447attach-table div div a {text-decoration:none;}#yiv0784936447 div.yiv0784936447attach-table {width:400px;}#yiv0784936447 div.yiv0784936447file-title a, #yiv0784936447 div.yiv0784936447file-title a:active, #yiv0784936447 div.yiv0784936447file-title a:hover, #yiv0784936447 div.yiv0784936447file-title a:visited {text-decoration:none;}#yiv0784936447 div.yiv0784936447photo-title a, #yiv0784936447 div.yiv0784936447photo-title a:active, #yiv0784936447 div.yiv0784936447photo-title a:hover, #yiv0784936447 div.yiv0784936447photo-title a:visited {text-decoration:none;}#yiv0784936447 div#yiv0784936447ygrp-mlmsg #yiv0784936447ygrp-msg p a span.yiv0784936447yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv0784936447 .yiv0784936447green {color:#628c2a;}#yiv0784936447 .yiv0784936447MsoNormal {margin:0 0 0 0;}#yiv0784936447 o {font-size:0;}#yiv0784936447 #yiv0784936447photos div {float:left;width:72px;}#yiv0784936447 #yiv0784936447photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv0784936447 #yiv0784936447photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv0784936447 #yiv0784936447reco-category {font-size:77%;}#yiv0784936447 #yiv0784936447reco-desc {font-size:77%;}#yiv0784936447 .yiv0784936447replbq {margin:4px;}#yiv0784936447 #yiv0784936447ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv0784936447 #yiv0784936447ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv0784936447 #yiv0784936447ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv0784936447 #yiv0784936447ygrp-mlmsg select, #yiv0784936447 input, #yiv0784936447 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv0784936447 #yiv0784936447ygrp-mlmsg pre, #yiv0784936447 code {font:115% monospace;}#yiv0784936447 #yiv0784936447ygrp-mlmsg * {line-height:1.22em;}#yiv0784936447 #yiv0784936447ygrp-mlmsg #yiv0784936447logo {padding-bottom:10px;}#yiv0784936447 #yiv0784936447ygrp-msg p a {font-family:Verdana;}#yiv0784936447 #yiv0784936447ygrp-msg p#yiv0784936447attach-count span {color:#1E66AE;font-weight:700;}#yiv0784936447 #yiv0784936447ygrp-reco #yiv0784936447reco-head {color:#ff7900;font-weight:700;}#yiv0784936447 #yiv0784936447ygrp-reco {margin-bottom:20px;padding:0px;}#yiv0784936447 #yiv0784936447ygrp-sponsor #yiv0784936447ov li a {font-size:130%;text-decoration:none;}#yiv0784936447 #yiv0784936447ygrp-sponsor #yiv0784936447ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv0784936447 #yiv0784936447ygrp-sponsor #yiv0784936447ov ul {margin:0;padding:0 0 0 8px;}#yiv0784936447 #yiv0784936447ygrp-text {font-family:Georgia;}#yiv0784936447 #yiv0784936447ygrp-text p {margin:0 0 1em 0;}#yiv0784936447 #yiv0784936447ygrp-text tt {font-size:120%;}#yiv0784936447 #yiv0784936447ygrp-vital ul li:last-child {border-right:
Terima kasih atas penjelasannya.
Saya coba ganti perintah :sSQL = sSQL & "Select * From " & oLr.Range(1)
dengan
sSQL = sSQL & "Select Account, Description, Sum(Debits) as Total_Debit, Sum(Credits) as Total_Credit From " & oLr.Range(1) & vbCrGROUP BY Account
Ketika macro dijalankan keluar message "Syntax Error" pada huruf yang berwarna merah. Dimana salahnya ya ?
Terima kasih.
Salam,HK
From: "'Mr. Kid' mr.nmkid@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com>
To: BeExcel <belajar-excel@yahoogroups.com>
Sent: Thursday, 1 October 2015, 8:58
Subject: Re: [belajar-excel] Perintah SQL untuk menggabungkan data sekaligus menjumlahkan
Hai Pak HK,
1. Query dasar agregat yang memiliki keys (agregat per anything) tanpa filter terhadap hasil agregat
SELECT your_key_columns, your_aggregat_ecpression
FROM your_source
GROUP BY your_key_columns_without_columns_aliasses
contoh : total penjualan per tahun (berdasar tanggal invoice) per produk
SELECT produk_id, year(tgl_invoice) as tahun, sum(penjualan) as total_jual
FROM dataINV
GROUP BY produk_id, year(tgl_invoice) --> tanpa aliasses
2. Query dasar UNION [ALL]
Query1
UNION [ALL]
Query2
UNION [ALL]
.
.
UNION [ALL]
QueryN
dengan setiap Query# berupa select query yang memiliki susunan sesuai kebutuhan.
Urutan kolom dan tipe datanya dari setiap Query# adalah tetap, dengan definisi kolom di Query1 yang menjadi nama kolom output
3. Susunan select query dasar
SELECT your_columns [, your_aggregate_expressions]
FROM your_source
[
[JOIN your_join1 ON your_join1_relations]
.
.
[JOIN your_joinN ON your_joinN_relations]
]
[
WHERE your_filter_expressions
]
[
GROUP BY your_columns_except_aggregate_expressions_without_columns_aliasses
[HAVING your_aggregate_filter_expressions] --> berisi filter terhadap kolom-kolom agregat, dan bukan terhadap key columns
]
[
UNION [ALL]
your_another_query2
.
.
UNION [ALL]
your_another_queryN
]
[
ORDER BY your_sort_expressions
]
gunakan klausa ORDER BY secara bijak dan usahakan dihindari kecuali penting sekali diseakhir mungkin suatu proses, karena klausa ORDER BY termasuk computer resources eater
4. source dari query lain (subquery di bagian FROM)
SELECT your_dataset_alias#.your_columns
FROM
(
queryS
) as your_dataset_alias1
[another query clauses]
queryS bisa berisi select query dasar seperti di-nomor 3 tanpa klausa ORDER BY
subquery suatu query seperti queryS juga bisa diletakkan pada bagian sumber data yang di-JOIN
setiap Query# dalam UNION [ALL] bisa berisi subquery suatu query seperti queryS
-----------------------
Prinsip dasar ber-query :
1. ketahui sumber data yang akan diproses
2. pahami alur komputasi yang dibutuhkan
3. susun query berdasar 1 dan 2
-----------------------
Jadi, menggabungkan data hasil agregat adalah hal yang berbeda dengan mengagregat hasil gabungan data
> menggabungkan data hasil agregat
SELECT your_key_columns, your_aggregate_expressions
FROM your_source1
GROUP BY your_key_columns_without_columns_aliasses
UNION [ALL]
SELECT your_key_columns, your_aggregate_expressions
FROM your_source2
GROUP BY your_key_columns_without_columns_aliasses
.
.
UNION [ALL]
SELECT your_key_columns, your_aggregate_expressions
FROM your_sourceN
GROUP BY your_key_columns_without_columns_aliasses
hasilnya bisa jadi ada your_columns yang sama dengan nilai your_aggregate apa saja jika dengan [ALL] atau ada hasil aggregate yang hilang jika tanpa [ALL]
> mengagregat hasil gabungan dataSELECT your_key_columns, your_aggregate_expressionsFROM
(
SELECT your_key_columns
FROM your_source1
UNION [ALL]
SELECT your_key_columns
FROM your_source2
.
.
UNION [ALL]
SELECT your_key_columns
FROM your_sourceN
) as your_dataset_alias
GROUP BY your_key_columns_without_columns_aliasses
Regard,
Kid
On Wed, Sep 30, 2015 at 5:36 PM, hendrik karnadi hendrikkarnadi@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com> wrote:
Dear Be-Excelers,
Berikut adalah perintah Macro yang saya dapat dari internet (file terlampir) untuk menggabungkan data (non unique) dari beberapa file yang ada dalam satu folder (Path),
Sub Consolidate(
Dim sSQL As String 'SQL String Dim oLr As ListRow 'Worksheets Row Dim cn As Object 'Connection Dim rs As Object 'Recordset
' Create SQL For Each oLr In Sheet1.ListObjects(
cn.Open "Provider=
If Sheet1.ListObjects.
rs.Close cn.Close
Set rs = Nothing Set cn = Nothing
End Sub
Yang ingin saya tanyakan adalah bagaimana bunyi perintah SQL nya (warna biru) jika yang ingin ditampilkan adalah total per no rek (bukan detailnya).
Terima kasih.
Salam,HK
#yiv0784936447 #yiv0784936447 -- #yiv0784936447ygrp-