Hai Odong,
hehehe... ternyata pertanyaan iseng diimil lalu itu dijawab to...
maaf ya... baru hari ini sempet screening imil milis yang belum tuntas
kasusnya atau yang perlu tambahan catatan..
Begini, tentang :
> masalah nya saya tidak dapat membayangkan
>
> tidak usah dibayangkan
> lebih baik ditulis saja secara detil setiap langkahnya
> biasanya, akan tampak alur pokok prosesnya (biasanya loh ya, jadi kalo
kasus om odong itu luar biasa, ee...)
saya ambil, copy
>
> hmmm... dihari hujan kaya hari ini, emang top banget kalo sambil ngopy
karakter yang saya perlu pada suatu baris di sheet A, setelah itu saya
> masuk sheet B, saya cari karakter yang saya butuhkan dengan CTRL+F,
>
setelah dapat baris yang terdapat karakter yang saya cari tadi
>
> oh, maksudnya 'copy' isi sebuah cells di sheet A...
> ini, garis besarnya adalah :
"mendapatkan records di sheet B, yang sesuai dengan kriteria di kolom
tertentu di sheet A"
> proses ini bisa dengan formula (yaitu : CountIFs) atau dengan Advanced
Filter (syarat : nama kolom kriteria di sheet A harus sama atau sudah
dibuat sama dengan kolom tertentu di sheet B)
setelah dapat baris yang terdapat karakter yang saya cari tadi ( *bila
> lebih dari 1 baris maka dilihat nilai yang paling mendekati* )
>
> nilai paling mendekati perlu dijabarkan kriterianya, seperti absolut
selisih antara nilai sheet B kolom Nilai dengan sheet A kolom Nilai adalah
yang terendah atau ada kondisi lainnya.
> jika ada kriteria ini, maka sheet B sebaiknya menggunakan formula vLookUp
agar bisa menghitung nilai kriteria selisih tadi
> lalu, di kolom lainnya di sheet B, berdasar kolom formula selisih, dibuat
formula pemilih atau penanda record pertama dengan selisih terendah per
kriteria pencocokan
> Excel Table object akan sangat bermanfaat untuk hal ini, apalagi jika
diotomasi dengan VBA
maka 1 baris tersebut saya cut saya pindah ke sheet C saya pastekan pada
> baris yang kosong
>
> tuh kan, ternyata cuma (no offense) mau nyari records sheet B yang sesuai
dengan sheet A
> karena ada kriteria 'nilai paling mendekati' tadi, maka daripada pakai
Advanced Filter, pakai AutoFilter saja.
> Filter sheet B berdasar kolom penanda record (pemilih record)
> Hasil filter di-copy (visible only) dan di paste values (atau values n
format) ke sheet C baris kosong
saya kembali ke sheet A, baris yang saya ambil karakternya tadi saya cut
> lagi untuk di masukan lagi di sheet C tepat berada di baris yang berisi
> baris yang berasal dari sheet B.
>
> yang ini, berarti akan disertakan record acuan dari sheet A menempel
(disandingkan, Join atau lookup) pada sheet B
> kalau begitu, di sheet B, sekalian dibuat formula pengambil record sheet
A di record terpilih
> jadi, AutoFilter sudah akan berisi data sheet B yang telah disandingi
data sheet A yang saling bersesuaian dan sesuai kriteria untuk sheet C
> akhirnya, untuk dapat sheet C, tidak perlu loop. Cukup sekali proses
saja. Capek kalau pakai loop-loop-an dengan Find.
begitu seterusnya sampai baris pada sheet A habis, bila sheet A menyisakan
> baris data, itu akan saya konfirmasi lagi ke bagian terkait karena yang
> seharusnya punya saldo hanya sheet B.
>
> ini berarti di sheet A butuh kolom formula untuk cek ada tau tidak record
sheet A (berdasar kolom tertentu) di sheet C (kolom tertentu).
> di sheet C sudah ada kolom-kolom dari sheet A bukan ? Berarti formula
dengan CountIFs bisa dimanfaatkan
> Nah... proses mendapatkan sisa record di sheet A adalah dengan AutoFilter
> Filter tabel di sheet A berdasar kolom berformula CountIFs tadi. Filter
yang isi kolom tersebut mencerminkan adanya record A di sheet C
> hasil autofilter di buang (visible only, di-delete entire row atau delete
record table sesuai keadaan tabel)
> selesai deh...
so,
jadi tidak ribet ngatur shortcut yang butuh VBA, karena VBA-nya dipakai
langsung untuk mengotomasi proses
oh iya,
Sebaiknya tidak pakai record macro untuk menjalankan SOP (copy, find, cut,
paste, dst itu kan seperti SOP) yang sudah rutin dijalankan, karena record
macro akan terasa manfaatnya untuk yang sudah paham VBA. Kalau belum paham
VBA, maka belajar VBA dengan record macro akan membuatnya fokus untuk
memperbaiki hasil record macro, bukan untuk memahami kemampuan si VBA.
Bukankah lebih baik memahami kemampuan calon karyawan yang akan di-hire
daripada sibuk memperbaiki hasil kerja karyawan yang tidak dipahami tapi
telah telanjur dipekerjakan dan diberi task.
Sekadar catatan : (moga-moga bermanfaat bagi yang mau belajar)
1. Langkah pertama dalam menyusun otomasi adalah mendefinisikan ulang hal
yang akan diotomasi
> jadi, bukan SOP yang ada yang langsung dibuatkan baris kode bejibunnya
> mulailah dengan mendefinisikan bunyi tugas dari atasan, sumber-sumber
data, data akhir yang dibutuhkan, berbagai opsi cara mengerjakannya secara
manual, dsb
2. Lalu, coba berbagai opsi cara mengerjakannya (bila sempat), atau
bertanya tentang opsi-opsi yang memungkinkan untuk digunakan.
3. Kemudian, pilih opsi cara mengerjakan yang dinilai cukup efisien dari
sekian banyak opsi yang ada
4. Barulah memulai proses otomasi, diawali dengan menyusun perangkat data,
seperti :
- workbook dan sheet yang dibutuhkan,
- tabel yang dibutuhkan,
- kolom-kolom yang dibutuhkan,
- kolom bantu atau baris bantu atau cells bantu yang dapat mempermudah
proses
5. dilanjutkan dengan menyusun baris kode otomasi
Contoh untuk kasus ini :
Diketahui :
Atasan berkata, "tolong dicarikan data yang tidak semestinya, lalu
konfirmasikan ke pihak terkait ya..."
Ditanyakan :
Manakah data yang perlu dikonfirmasi ?
Penyelesaian :
> Langkah 1
Tema kasus : membandingkan 2 data, diambil yang tidak sesuai (bila
perlu, simpan yang sesuai)
Data : sheet A, sheet B, sheet C (berisi record yang sesuai)
Daya yang dibandingkan : Sheet A vs Sheet B
Kriteria perbandingan :
1. kolom X di sheet A terhadap kolom Y di sheet B
2. nilai terendah dari selisih (absolute) antara kolom NilaiA di
sheet A dengan NilaiB di sheet B dalam formula selisih = Absolutenya (
nilaiB - nilai A )
> Langkah 2
Opsi yang terpikirkan :
1. pakai Find :
setiap isi sheet A di-Find pada sheet B, bandingkan nilaiA
dengan nilaiB, cut record yang selisih terendah, paste ke sheet C baris
baru.
cut record sheet A yang sedang diproses ke sheet C di lokasi
paste terakhir dari sheet B tadi
ulangi sampai seluruh record sheet A selesai diperiksa, maka
didapatlah records sheet A yang perlu dikonfirmasi + record yang sesuai di
sheet C
2. pakai formula untuk memilih record yang yang sesuai
di sheet A :
- dibuat kolom formula berisi cek ada atau tidaknya
record tersebut di sheet B. Kalau ada, hasilnya > 0. Kalau tidak ada,
hasilnya 0. Dugaan : bisa pakai CountIFs
di sheet B :
- dibuat kolom formula hitung selisih (absolute)
terhadap nilaiA di sheet A. Karena absolute, nilai selisih minimal adalah
0. Dugaan : bisa pakai vLookUp
- dibuat kolom formula pemilih record dengan selisih
terendah per kolom kunci. Record dengan selisih terendah akan bernilai 0.
Dugaan : bisa pakai CountIFs kriteria "<" selisih baris tersebut.
- dibuat kolom formula pengambil kolom-kolom sheet A
untuk hasil di sheet C
di sheet C :
- disiapkan kolom-kolom hasil tanpa record
3. pakai excel data connection (fitur get external data)
di sheet C :
- koneksi ke dirinya sendiri, query join sheet B dengan
sheet A (left join), hitung selisih, group by ambil min. Ambil kolom sheet
B dan sheet A dengan selisih terendah, filter record sheet A tidak NULL
buat sheet D (record yang perlu konfirmasi) :
- koneksi ke dirinya sendiri, query join sheet A dengan
sheet B (left join). Ambil sheet A. Filter record sheet B adalah NULL
> Langkah 3
Dari 3 opsi di atas, urutan yang dinilai optimum adalah (dari optimum
tertinggi ke optimum terendah) : cara 2, cara 1, cara 3
> cara 2 : perlu belajar menyusun formula, tetapi sekali kerja
> cara 1 : kerja berulang-ulang
> cara 3 : perlu belajar Get External Data dan query
Jadi, pilih cara 2 dulu saja deh... (walaupun cara 3 tidak perlu VBA
dan formula, tetapi saat ini lebih optimum pakai cara 2 lalu nanti
diotomasi dengan VBA. Kalau sudah selesai belajar query, baru coba cara 3)
> Langkah 4
mulai deh buat sheet A beserta kolom berformulanya, bila perlu Excel
Table object-nya. Begitu juga di sheet B. Sheet C disipakan header-nya.
> Langkah 5
scripting deh
1. set kalkulasi jadi automatic
2. urutan proses salin record dengan selisih terendah dari sheet B ke
sheet C baris baru
- di sheet B, turn off autofilter, autofilter kolom pemilih
record bernilai 0, pilih record yang visible only, copy, ke baris kosong di
sheet C, paste values, turnoff autofilter di sheet B
3. urutan proses buang record sheet A yang ada di sheet B
- di sheet A, turn off autofilter, autofilter kolom penanda
record ada di sheet B bernilai ">0", pilih record yang visible only, delete
row atau delete table row, turnoff autofilter sheet A
4. pesan selesai ke user
Selesai deh scripting-nya, tinggal buat ornamen untuk interface user,
seperti tombol atau malah sheet interface khusus.
Moga-moga bisa menjadi awal berkembangnya manfaat.
Regards,
Kid
2017-11-29 14:17 GMT+07:00 odong nando odongna...@yahoo.com [belajar-excel]
<belajar-excel@yahoogroups.com>:
>
>
> hai boz kid,
>
> terimaksih solusinya, saya coba dulu klo mentok ga berhasil saya tanya
> lagi.. :)
>
> oiya,"kenapa CTRL+F nya yang berulang kali itu tidak diotomasi dengan VBA
> ?".
>
> masalah nya saya tidak dapat membayangkan, saya ambil, copy, karakter yang
> saya perlu pada suatu baris di sheet A, setelah itu saya masuk sheet B,
> saya cari karakter yang saya butuhkan dengan CTRL+F, setelah dapat baris
> yang terdapat karakter yang saya cari tadi ( bila lebih dari 1 baris maka
> dilihat nilai yang paling mendekati ), maka 1 baris tersebut saya cut saya
> pindah ke sheet C saya pastekan pada baris yang kosong, saya kembali ke
> sheet A, baris yang saya ambil karakternya tadi saya cut lagi untuk di
> masukan lagi di sheet C tepat berada di baris yang berisi baris yang
> berasal dari sheet B.
>
> begitu seterusnya sampai baris pada sheet A habis, bila sheet A menyisakan
> baris data, itu akan saya konfirmasi lagi ke bagian terkait karena yang
> seharusnya punya saldo hanya sheet B.
>
> br,
>
> odong nando
>
>
> ------------------------------
> *Dari:* "'Mr. Kid' mr.nm...@gmail.com [belajar-excel]" <
> belajar-excel@yahoogroups.com>
> *Kepada:* BeExcel <belajar-excel@yahoogroups.com>
> *Dikirim:* Selasa, 28 November 2017 19:15
>
> *Judul:* Re: [belajar-excel] mengganti shortcut
>
>
> Hai Odong,
>
> Tidak ada pengaturan shortcut di Excel Options (MS Word malah ada).
>
> Di Excel, mengubah isi shortcut perlu menggunakan VBA. Berarti security
> macro harus di-Enable.
> Misal, file kerja sehari-hari bernama Kerja.xlsx dan akan diatur
> penggunaan shortcutnya. Sebaiknya buat file baru (kosong), misal bernama
> mySetting.xlsm
> Jadi, file mySetting.xlsm dijalankan lebih dulu. Setelah itu baru file
> open si Kerja.xlsx.
> Kalau selesai pakai setting-nya dan ingin normal, tutup si mySetting.xlsm
> dan lanjutkan kerja dengan Kerja.xlsx dalam keadaan setting normal lagi.
>
> Isi mySetting.xlsm begini :
> 1. Buat event Workbook Open
> > waktu buka workbook, si CTRL+D dibuat supaya menjalankan prosedur
> pemanggil CTRL+F.
> > Prosedur pemanggil CTRL+F itu nanti dinamakan CariNGanti
> > bunyi baris kode supaya kalau ada yang pencet CTRL+D maka si Excel
> akan jalankan prosedur CariNGanti :
> Application.OnKey "^d" , "CariNGanti"
>
> 2. Buat event workbook before close
> > waktu workbook ditutup, si CTRL+D dikembalikan jadi default lagi
> Application.OnKey "^d"
>
> 3. disebuah general module, buat prosedur CariNGanti
> > prosedur ini isinya adalah memanggil dialog window Find atau Find n
> Replace.
> > Bisa salah satu dari yang ini :
> 1. dialog Find (agak beda dengan dialog Find n Replace hasil
> menekan CTRL+F)
> Application.Dialogs(xldalogformulafind).Show
>
> 2. seperti menekan tombol-tombol menu Excel ALT+H+FD+F (ribbon
> [ALT] + Home [H] + grup Editing -> find n select [FD] + Find... [F])
> Application.SendKeys "%HFDF"
> 'kalau mau diapit application.screenupdating =false diawal lalu
> =true diakhir juga boleh (kayanya gak perlu sih)
>
> 3. (direkomendasikan) command bar, seperti nomor 2, tapi langsung
> ke pokok menu berbunyi Find...
> Application.CommandBars("Edit").Controls("Find...").Execute
> 'perhatikan, di ribbon Home -> grup Editing -> Find n Select -> tulisannya
> Find... (diakhiri 3 bijik titik)
>
> 4. save workbook
> > save as dengan type .xlsm
>
> 5. tutup workbook, lalu buka lagi, n coba deh
> > buka juga file lainnya, seperti si kerja.xlsx
>
> Met mencoba ya...
>
> btw,
> kenapa CTRL+F nya yang berulang kali itu tidak diotomasi dengan VBA ?
> (jawabannya optional kok, bila perlu ndak usah dijawab)
>
> Regards,
> Kid
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> On Tue, Nov 28, 2017 at 5:35 PM, odong nando odongna...@yahoo.com
> [belajar-excel] <belajar-excel@yahoogroups.com> wrote:
>
>
> hai pak HK,
>
> justru karena hanya saya yang memakai komputer ini. jadi bagi saya tidak
> masalah mau di ganti shortcutnya seperti apa.
> mohon utk pak HK memberitahukan dimana saya bisa merubah shortcut excel
> ini. karena bagi saya cukup mengganggu.
>
> memang apa yang pak HK bilang ada benarnya juga, " Kalau salah pencet
> kenapa default shortcutnya yang musti diganti ??". kalau saya menanggapinya
> seperti ini pak, biasaya ada ratusan baris dalam 1 sheet excel yang harus
> saya hapus berdasarkan kriteria dari sheet lain, saya sudah coba
> menyocok2kannya menggunakan formula tp hasinya nihil, mungkin karena
> pengetahuan saya yang kurang. dari sebab itulah saya mencoba membiasakan
> diri utk mengambil bagian dari data cell sheet A, saya cari yang sama pada
> sheet B saya cut data yang saya anggap sesuai, saya taruh pada sheet C, dan
> saya kembali ke sheet A utk ambil data yang saya cari tari ke sheet C.
>
> kira2 kejadiannya seperti itu, dan saya sudah terbiasa melakukannya,
> semuanya dilakukan tanpa bantuan mouse, dan lagi klo saya lagi melakukan
> itu saya merasa senang aja pak, hehehe...
>
> jadi saya tidak mencaritahu lagi bagaimana formula yang harusnya saya
> terapkan, saya malah menginginkan shortcut ctrl+D itu hilang saja, atau ya
> sudah disamakan saja fungsinya dengan ctrl+F....
>
> br,
>
> odong nando
>
>
> ------------------------------
> *Dari:* "hendrik karnadi hendrikkarn...@yahoo.com [belajar-excel]" <
> belajar-excel@yahoogroups.com >
> *Kepada:* "odong nando odongna...@yahoo.com [belajar-excel]" <
> belajar-excel@yahoogroups.com >
> *Dikirim:* Selasa, 28 November 2017 15:07
> *Judul:* Re: [belajar-excel] mengganti shortcut
>
>
> Hai Odong,
> Kalau salah pencet kenapa default shortcutnya yang musti diganti ??
> Bukankah akan lebih repot kalau shortcut computer kita sudah dibuat custom
> kemudian dipakai orang lain yang masih menggunakan default shorcut ?
>
> Salam,
> HK
>
> On Tuesday, 28 November 2017, 2:37:56 PM GMT+7, odong nando
> odongna...@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com >
> wrote:
>
>
>
> hai BeExceler
>
> saya terbiasa bekerja dengan shortcut excel, tp kadang-kadang salah
> pencet. lagi enak enaknya pake shortcut ctrl+F eh kepencet ctrl+D,
> gaketauan tuh setelah nya saya pake lagi tu ctrl+F, memang jarang tp klo lg
> asik ga ketahuan hasilnya sangat merepotkan, dan yang paling fatal saya
> pernah mengulang pekerjaan saya yang seharusnya hampir kelar, karena
> ternyata bukan hanya 1x saya tekan ctrl+D tp beberapa kali, jadi sudah
> tidak ketahuan lagi mana angka yang tidak ada...
>
> dari kasus saya tersebut, mohon informasi di mana saya bisa menemuakan
> pengaturan untuk mengganti format shortcut excel 2010...
>
> terimakasih
>
> br,
>
> odong nando
>
>
>
>
>
>
>
