Minggu, 16 Desember 2012

[belajar-excel] Digest Number 1971

7 New Messages

Digest #1971
1a
Re: Menghitung rata2 sisa activity by "Mr. Kid" nmkid.family@ymail.com
3a
3b
Re: tombol quick print pada sheet aktif by "Mr. Kid" nmkid.family@ymail.com
4a
Re: Rumus untuk table range by "Risnaldi" risnaldi
5a
Cara mengarsir grafik by "Agus Ramiyanto"
5b
Re: Cara mengarsir grafik by "Mr. Kid" nmkid.family@ymail.com

Messages

Sat Dec 15, 2012 10:22 am (PST) . Posted by:

"Mr. Kid" nmkid.family@ymail.com

Belum ada yang reply ya...

Formula-formula berikut meminta syarat :
1. Pada baris ke-3 diisi dengan angka bulan [1 sampai 12] (bukan teks bulan
apapun)
2. sebuah kolom dikanan kolom data terkanan selalu kosong (untuk kasus ini,
berarti kolom *BB*) mulai dari baris 3 sampai baris terakhir data.
3. data selalu dimulai dari week 1

Bahasa manusia untuk semua formula dibawah sana :
"Reratakan nilai data yang angka bulan di baris 3 adalah sama dengan angka
bulan di-week pertama setelah week akhir proses"

Formula untuk M-1 : (misal baris 6)
>> pakai logat xl2007 ke atas
*=AVERAGEIF( $F$3:$BB$3 , INDEX($F$3:$BB$3,$B6+1)+0 , $F6:$BB6 )
*Formula ini akan menghasilkan error value ketika akhir proses adalah pada
bulan terakhir yang ada. Untuk itu perlu penambahan trap error dengan
IFError.
*=IFError( AVERAGEIF($F$3:$BB$3,INDEX($F$3:$BB$3,$B6+1)+0,$F6:$BB6) , 0 )
Bagian-bagian formula :
$F6:$BB6 *:: nilai data yang direrata
$F$3:$BB$3 :: angka-angka bulan
INDEX($F$3:$BB$3,*$B6+1*)*+0* :: angka bulan pada week pertama setelah week
akhir proses
*$B6*:: kolom bantu posisi week akhir proses yang berupa formula
=MID(E6,FIND("#",E6,9)+1,2)
*+0* :: penambah angka bulan setelah week akhir proses

>> pakai logat ilmiah (array formula) yang dimengerti semua versi (jangan
lupa untuk mengakhiri penulisan formula dengan CTRL SHIFT ENTER)
*=AVERAGE( IF( $F$3:$BB$3 = INDEX($F$3:$BB$3,$B6+1)+0 , $F6:$BB6 ) )
Bagian-bagian formula :
**$F6:$BB6 *:: nilai data yang direrata
$F$3:$BB$3 :: angka-angka bulan
INDEX($F$3:$BB$3,*$B6+1*)*+0* :: angka bulan pada week pertama setelah week
akhir proses
*$B6*:: kolom bantu posisi week akhir proses yang berupa formula
=MID(E6,FIND("#",E6,9)+1,2)
*+0* :: penambah angka bulan setelah week akhir proses

*Catatan :
*1. Jika keadaan nyata tidak sama dengan asumsi, maka sesuaikan formula.
2. Jika kriteria lebih dari 1, maka
>> pada logat xl2007 ganti AverageIF dengan AverageIF*s* dan sesuaikan
bagian kriteria agar diperoleh nilai-nilai data yang akan direrata.
>> pada logat ilmiah dibagian pengkondisian (IF) dijabarkan logika
perbandingan kriteria-kriteria yang digunakan.
3. Pada M-2 atau M-sekian, sesuaikan penambah angka bulan setelah week
akhir proses agar diperoleh nilai bulan yang pantas direrata nilai datanya.
4. Jika akan mencari M pada angka bulan week pertama sebelum week awal
proses, sesuaikan rujukan yang berkaitan dengan range di baris 3.

Wassalam,
Kid.

2012/12/14 Tony Bexcel <tonybexcel@yahoo.co.id>

> **
>
>
> Dear All Pakar yang budiman,
>
> Terlampir adalah file sebelum nya namun disini masih ada kasus yang butuh
> bantuan untuk menghitung rata2 di 3 bulan kedepan akhir dari activity.
> Dalam penyampaian kasus mungkin saya kurang baik, untuk itu saya lampirkan
> file terlampir.
> Atas bantuan dan waktunya saya ucapkan terimakasih.
>
>
> Salam,
> Tony
>
>
>

Sat Dec 15, 2012 12:49 pm (PST) . Posted by:

"Mr. Kid" nmkid.family@ymail.com

Dear BeExceller,

Kasus menggabung file sangat banyak dimilis. Proses seperti ini umumnya
dilakukan menggunakan VBA.
Pada tabel data source yang berkaidah database, fitur Get External Data
dapat menjadi opsi selain VBA.

File terlampir adalah contoh proses menyalin data dari suatu workbook ke
workbook kerja.
File-01.xls dan File-02.xls adalah contoh data. Pada contoh kali ini, kedua
file contoh tersebut harus *diletakkan pada folder yang sama* dengan file
bernama re-Gabung 2 File Updated per pekan.xls.

Salah satu isi dalam file re-Gabung 2 File Updated per pekan.xls terlampir
adalah sebuah prosedur copy paste values dan format number data dari file
lain ke suatu range tertentu. Prosedur tersebut bernama *AmbilData*.
Prosedur ini memanfaatkan properti currentregion dalam menentukan area data
yang ada di suatu file yang akan di-copy. Hal ini adalah salah satu batasan
dari prosedur AmbilData. Prosedur ini dapat digunakan secara sederhana
dengan memanggil nama prosedur dan menyediakan data-data input parameter
yang dibutuhkannya.

Berikut ini adalah salinan prosedur tersebut.
-> script terdapat dalam file mod473_Kid_WbkProc.bas yang dapat di-ekspor
ke VB Project yang membutuhkan melalui kegiatan Import file melalui menu
File di VBE <-
'-------------------------------------
'Prosedur AmbilData :
'-------------------------------------
'Input :
'sFileFullName :: string :: nama file lengkap beserta path dan
ekstensinya yang akan diambil datanya
'vSht :: variant :: nama sheet atau indeks sheet lokasi
data di sFileFullName
'sFirstFieldAddress :: string :: alamat cell baris pertama pada kolom
pertama data
'rngAnchorTargetTable :: range :: object range header pertama di
lokasi paste values
'-------------------------------------
'Kid, Jakarta, 2012-12-16, Excel VBA
'-------------------------------------
Public Sub AmbilData(sFileFullName As String, vSht As Variant,
sFirstFieldAddress As String, _
rngAnchorTargetTable As Range, Optional sRes As
String = vbNullString)
Dim wbkSource As Workbook
Dim shtSource As Worksheet
Dim rngSource As Range
Dim bAppAlert As Boolean

On Error GoTo Keluar 'arahkan trap error (sesuaikan baris ini jika
dikonversi ke .Net)

'simpan setting kecerewetan excel dan set turnoff
bAppAlert = Application.DisplayAlerts
Application.DisplayAlerts = False

Set wbkSource = Workbooks.Open(sFileFullName, 2, False)
'buka file dan simpan pointer workbook
Set shtSource = wbkSource.Worksheets(vSht)
'definisikan sheet kerja
Set rngSource = shtSource.Range(sFirstFieldAddress).Resize(1, 1)
'simpan pointer range anchor data

'perluas area data yang akan di-copy
Set rngSource = Range(rngSource, _

rngSource.CurrentRegion.Cells(rngSource.CurrentRegion.Count) _
)
rngSource.Copy 'copy datanya
'paste special values dan format ke lokasi tujuan di baris kosong
pertama
rngAnchorTargetTable _
.Offset(rngAnchorTargetTable.CurrentRegion.Rows.Count) _
.PasteSpecial xlPasteValuesAndNumberFormats
wbkSource.Close False 'tutup file source tanpa proses simpan

Keluar:
Application.CutCopyMode = False 'buang data di clipboard
Application.DisplayAlerts = bAppAlert 'kembalikan setting seperti
semula

'cek error dan kembalikan pesan error ke user melalui bariabel sres
If Err.Number <> 0 Then
sRes = "Error : " & Err.Number & " - " & Err.Description
End If

'kembalikan setting trap error
Err.Clear
On Error GoTo 0
End Sub

Cara pakai :
1. buat sebuah module dan copas prosedur AmbilData ke module tersebut.
2. pada prosedur yang membutuhkan proses copas dari file lain ke suatu
range tertentu, lakukan pemanggilan prosedur AmbilData disertai penyediaan
input parameter yang dibutuhkan.

Contoh pemakaian :
Prosedur bernama GabungFile membutuhkan proses mengambil data dari file
Sumber.xls yang ada di drive E folder Dataku.
Sheet yang akan diambil datanya bernama 'Mingguan' dengan record pertama
pada kolom pertama tabel ada di cell D9.
Tabel data sumber ini memiliki baris yang rapat dan kolom yang rapat
(kontinyu).
Data akan di-copy dari file tersebut dan di-paste values beserta format
number-nya ke workbook kerja (workbook aktif) pada sheet bernama myDBTable.
Tabel dalam sheet myDBTable memiliki header di baris 1 mulai cell A1.
Baris pemanggilan prosedur AmbilData dalam prosedur GabungFile adalah :
AmbilData "E:\Dataku\Sumber.xls" , "Mingguan" , "D9" ,
thisworkbook.sheets("myDBTable").range("a1")

Bila pesan error (jika terjadi) juga akan ditangkap untuk ditampilkan
ke-user, maka sediakan sebuah variabel string sebagai wadah pesan tersebut.
Selalu kirim variabel tersebut dalam keadaan kosong. Jika dikembalikan oleh
prosedur AmbilData dalam keadaan isi, maka ada error yang terjadi.
dim sPesanError as string
spesanerror=vbnullstring
AmbilData "E:\Dataku\Sumber.xls" , "Mingguan" , "D9" , _
thisworkbook.sheets("myDBTable").range("a1") ,spesanerror
if lenb(spesanerror)<>0 then
msgbox "Ada error sebagai berikut :" & vbcrlf & spesanerror
endif

Dimohon untuk mengabarkan error yang terjadi ke milis. Jika memungkinkan,
sekalian dengan hasil perbaikannya.
Selamat mencoba dan mengembangkannya.

Wassalamu'alaikum Wr. Wb.
Kid.

2012/12/14 Rachmad HIDAYAT <rachmad.hidayat@external.total.com>

> **
>
>
> Dear Pakar Bexcel,****
>
> Saya punya case begini nih, mohon bisa dibantu ya..,****
>
> ** **
>
> **- **Saya punya dua atau lebih file Absensi dengan format tabel
> yang sama, namun nama data nama,materi, absensi berbeda. (dalam case ini
> saya jadikan satu file saja, tapi faktanya ada 3 file xlsx, FILE-01.xlxs(di
> Sheet File-01),FILE-02(di sheet File-02).xlsx, GABUNG DATA.xlsx (Gabung
> data File-01dan File02)****
>
> **- **Setiap akhir pekan saya pingin mengetahui berapa total
> keseluruhan yang hadir(H), tidak hadir(X)dari orang-orang tersebut****
>
> **- **Ohya,Data orang bisa bertambah jadi nanti secara otomatis
> akan tergabung juga di file gabung****
>
> **- **Bagaimana cara menggabungkan 2 file ini menjadi satu file
> gabungan ya ?****
>
> ** **
>
> Lebih jelasnya bisa di lihat di lampiran Saya cob pake concolidate, namun
> masih bingung cara pake dan error****
>
> Terim kasih atas bantuannya ya****
>
> ** **
>
> Terima Kasih,****
>
> Rachmad Hidayat****
>
> Inspection Method****
>
> 3487****
>
> ** **
>
>
>

Sat Dec 15, 2012 9:11 pm (PST) . Posted by:

"Risnaldi" risnaldi

Sebelumnya saya ingin mengucapkan terima kasih kepada Mr. Kid yang telah memberikan solusi case sebelumnya.

Kali ini saya ada 1 pertanyaan lagi, apakah pada sheet aktif bisa dibuat suatu tombol atau sesuatu yang bisa melakukan quick print untuk sheet lainnya. Penjelasannya, pada workbook terdapat 6 worksheet dimana 1 sheet aktif terdapat 5 tombol yang bisa melakukan quick print untuk 5 sheet lainnya.

Mungkin master-master excel ada yang bisa membantu case diatas.

Terima kasih.

Sat Dec 15, 2012 11:02 pm (PST) . Posted by:

"Mr. Kid" nmkid.family@ymail.com

Hai Risnaldi,

Biasanya hal ini dilakukan dengan VBA.
Syntax dasar :
Sheets("nama sheet").printout

Coba langkah berikut :
1. buat sebuah shape dalam worksheet (bila perlu, ganti nama shape melalui
name box [area dikiri formula bar])
2. klik kanan shape -> assign macro -> tulis pada macro name nama
prosedurnya, misal PrintSheet1 -> tekan tombol New
3. tulis didalamnya :
Sheets("nama sheet yang akan diprint").printout 'ganti nama sheet
yang akan diprint dengan nama sheetnya, misal rekap jika akan print sheet
rekap
4. kembali ke worksheet dengan menekan ALT F11
5. ulangi langkah 1 sampai 4 untuk tombol lainnya
6. save as file (tekan ALT F A) -> pilih pada save as type salah satu
dari .xls atau .xlsm atau .xlsb -> ganti nama file (tanpa ekstensi) ->
tekan save
7. coba klik salah satu shape yang telah dibuat.

Wassalam,
Kid.

2012/12/16 Risnaldi <risnaldi@yahoo.com>

> **
>
>
> Sebelumnya saya ingin mengucapkan terima kasih kepada Mr. Kid yang telah
> memberikan solusi case sebelumnya.
>
> Kali ini saya ada 1 pertanyaan lagi, apakah pada sheet aktif bisa dibuat
> suatu tombol atau sesuatu yang bisa melakukan quick print untuk sheet
> lainnya. Penjelasannya, pada workbook terdapat 6 worksheet dimana 1 sheet
> aktif terdapat 5 tombol yang bisa melakukan quick print untuk 5 sheet
> lainnya.
>
> Mungkin master-master excel ada yang bisa membantu case diatas.
>
> Terima kasih.
>
> _
>

Sat Dec 15, 2012 9:12 pm (PST) . Posted by:

"Risnaldi" risnaldi

Terima kasih banyak atas bantuan Mr. Kid, case saya akhirnya dapat terpecahkan dengan solusi yang diberikan.

Wassalam,
Risnaldi

--- In belajar-excel@yahoogroups.com, "Mr. Kid" <mr.nmkid@...> wrote:
>
> Hai Ris,
>
> Setiap kriteria berupa interval sebaiknya dipisah menjadi 2 kolom yaitu
> titik start dan titik end. Misal Price Start dan Price End.
> Kriteria yang digunakan untuk kasus ini adalah :
> >> price start (dibuat baru di kolom B)
> >> price end (dibuat baru di kolom C)
> >> years start (dibuat baru di kolom D)
> >> years end (dibuat baru di kolom E)
> Sedangkan nilai yang akan diambil adalah persentase (jika 0%, maka tulis 0%
> daripada menggunakan dash) diletakkan pada kolom H
> Semua referensi di atas ada dalam sheet bernama rating.
>
> Data input ada dalam sheet data berupa :
> >> price input di cell E3
> >> tahun perakitan di cell E4
>
> Data tahun perakitan harus dihitung usianya terhadap tahun sekarang dengan
> formula :
> Year( Today() ) - E4
>
> Formula rate :
> =SUMPRODUCT((Rating!B3:B17<=E3)*(Rating!C3:C17>=E3)*(Rating!D3:D17<=YEAR(TODAY())-E4)*(Rating!E3:E17>=YEAR(TODAY())-E4)*Rating!H3:H17)
> *dengan penjelasan :*
> (Rating!B3:B17<=E3) Price Start <= Price Input (Rating!C3:C17>=E3) Price
> End >= Price Input (Rating!D3:D17<=YEAR(TODAY())-E4) Years Start <=
> selisih tahun input (Rating!E3:E17>=YEAR(TODAY())-E4) Years End >= selisih
> tahun input Rating!H3:H17 Nilai yang diambil * seluruh kriteria harus
> terpenuhi, maka menggunakan operator AND yang setara perkalian
> File terlampir.
>
> Wassalam,
> Kid.
>
>
> 2012/12/14 Risnaldi <risnaldi@...>
>
> > **
> >
> >
> > Dear Master Excel,
> > Mohon bantuannya rumus apa yang digunakan untuk table range. Dimana pada
> > saat mengisi harga dan tahun perakitan akan keluar hasil (RATE)
> > berdasarkan table harga dan tahun perakitan.
> > Sebelumnya sudah coba pakai vlookup tapi masih belum berhasil, mungkin
> > para master excel disini ada yang bisa bantu. file terlampir.
> > Atas waktu dan bantuannya saya ucapkan banyak terimakasih.
> >
> > Salam,
> > Ris naldi
> >
> >
> >
>

Sun Dec 16, 2012 12:34 am (PST) . Posted by:

"Agus Ramiyanto"

Assalamu alaikum wr wb

Para pakar excel, mohon bantuannya apakah bisa dan bgmn caranya agar grafik
excel terarsir.
Contoh terlampir, yg diarsir adalah area galian (grafik garis warna orange).
Terima kasih.

Salam

Agus R

--- Mods ---
Sepertinya si file lampiran ketinggalan.
fyi.
Posting kemilis melalui Compose email di akun email pribadi bisa menyertakan file lampiran. Jika posting melalui website milis secara langsung malah tidak bisa menyertakan file lampiran.
------------

Sun Dec 16, 2012 1:10 am (PST) . Posted by:

"Mr. Kid" nmkid.family@ymail.com

Wa'alaikumussalam Wr. Wb.

Pak Agus,

Mungkin dengan jenis chart stacked column bisa memenuhi keinginan.
Data yang akan diarsir dibuat dikolom tersendiri. Data yang tidak diarsir
juga dibuat dikolom tersendiri.
Nilai yang mestinya tidak ada dalam kolom masing-masing tersebut diganti
dengan data bertipe selain numerik.

File terlampir adalah sebuah contoh. Nilai yang mestinya tidak dikolom
tersebut diganti dengan data teks kosong (nullstring)

Wassalamu'alaikum Wr. Wb.
Kid.

2012/12/16 Agus Ramiyanto <aramiyanto@gmail.com>

> **
>
>
> Assalamu alaikum wr wb
>
> Para pakar excel, mohon bantuannya apakah bisa dan bgmn caranya agar grafik
> excel terarsir.
> Contoh terlampir, yg diarsir adalah area galian (grafik garis warna
> orange).
> Terima kasih.
>
> Salam
>
> Agus R
>
> --- Mods ---
> Sepertinya si file lampiran ketinggalan.
> fyi.
> Posting kemilis melalui Compose email di akun email pribadi bisa
> menyertakan file lampiran. Jika posting melalui website milis secara
> langsung malah tidak bisa menyertakan file lampiran.
> ------------
>
>
GROUP FOOTER MESSAGE
---------------------------------------------------------------------
bergabung ke milis (subscribe), kirim mail kosong ke:
belajar-excel-subscribe@yahoogroups.com

posting ke milis, kirimkan ke:
belajar-excel@yahoogroups.com

berkunjung ke web milis
http://tech.groups.yahoo.com/group/belajar-excel/messages

melihat file archive / mendownload lampiran
http://www.mail-archive.com/belajar-excel@yahoogroups.com/
atau (sejak 25-Apr-2011) bisa juga di :
http://milis-belajar-excel.1048464.n5.nabble.com/

menghubungi moderators & owners: belajar-excel-owner@yahoogroups.com

keluar dari membership milis (UnSubscribe):
kirim mail kosong ke  belajar-excel-unsubscribe@yahoogroups.com
---------------------------------------------------------------------

Tidak ada komentar:

Posting Komentar