Assalamu'alaikum wr. wb.
Mantabss Ms. Jan Raisin, terimakasih atas pencerahan & pembelajarannya. Cuma di
milis ini saya bisa dptkan ilmu lebih dari guru2 yg sangat sangat royal berbagi
ilmu ... insyaallah jadi amal jariyah ...
Sekali lagi terimakasih Ms. raisin
Wassalam,
mulki
----- Original Message -----
From: Jan Raisin
To: belajar-excel@yahoogroups.com
Sent: Monday, December 17, 2012 1:34 PM
Subject: Re: [belajar-excel] Menggabungkan 2 Data absensi ke dalam 1 File
gabung absensi
Wa'alaikumsalam wr. wb.,
berhubung pak Ghozi belum menjawab, mungkin terhalang oleh kesibukan ybs,
izinkan Jan mencoba memberikan penjelasan mengenai alur formula dari pak Ghozi
=IF(ROW(1:1)<=COUNTA('File-01'!$A$7:$A$40);OFFSET('File-01'!$A$6;MOD(ROW(1:1)-1;COUNTA('File-01'!$A$7:$A$40))+1;COLUMN()-1);OFFSET('File-02'!$A$6;ROW(1:1)-COUNTA('File-01'!$A$7:$A$40)+1;COLUMN()-1))
untuk mudah membacanya maka formula dibagi menjadi beberapa bagian:
========================================================================================================================================================
Bagian pertama adalah formula =If (kondisi , nilai_jika_kondisi_terpenuhi ,
nilai_jika_kondisi_tidak_terpenuhi)
=IF(ROW(1:1)<=COUNTA('File-01'!$A$7:$A$40);OFFSET('File-01'!$A$6;MOD(ROW(1:1)-1;COUNTA('File-01'!$A$7:$A$40))+1;COLUMN()-1);OFFSET('File-02'!$A$6;ROW(1:1)-COUNTA('File-01'!$A$7:$A$40)+1;COLUMN()-1))
jika kondisi yang disorot dengan warna biru terpenuhi, maka ambil nilai dari
yang disorot dengan warna hijau, jika tidak terpenuhi maka ambil nilai yang
disorot dengan warna merah.
semoga yang bagian pertama dapat dimengerti dengan mudah, karena hal itu
dibutuhkan untuk memahami bagian berikutnya.
===================================================================================================================================================================
Bagian Kedua, beralih ke kondisi di dalam fungsi If tersebut: ROW(1:1) <=
COUNTA('File-01'!$A$7:$A$40)
ada 3 bagian di dalam kondisi ini, yaitu ROW(1:1) , <= , dan
COUNTA('File-01'!$A$7:$A$40)
Bagian yang berbunyi Row(1:1) akan menghasilkan nilai 1, jika formula
tersebut dicopy ke arah bawah akan menghasilkan formula Row(2:2), Row(3:3) dst,
dan hasilnya adalah berupa nilai mulai dari 1, 2, 3, 4, dst sesuai dengan
jumlah baris yang dipaste. Untuk pembuktian, coba tulis di suatu cell formula
berikut, =Row(1:1) lalu copy ke arah bawah dan lihat hasilnya.
Bagian yang berbunyi <= adalah operator matematika yang berfungsi untuk
membandingkan besarnya suatu nilai,
Bagian yang berbunyi Counta('File-01'!$A$7:$A$40) adalah berfungsi untuk
menghitung jumlah cell yang tidak kosong di sheet File-01, mulai dari cell A7
sampai cell A40.
Khusus pada kasus ini: formula Counta('File-01'!$A$7:$A$40) akan menghasilkan
nilai 11, untuk pembuktian coba hitung manual pada sheet File-01 mulai cell A7
sampai A40, ada berapa cell yang tidak kosong?
===================================================================================================================================================================
bagian KeTiga, adalah nilai yang diambil jika kondisi terpenuhi,
OFFSET('File-01'!$A$6;MOD(ROW(1:1)-1;COUNTA('File-01'!$A$7:$A$40))+1;COLUMN()-1)
berhubung pengambilan nilai untuk kondisi terpenuhi dan tidak terpenuhi
logikanya sama, maka untuk yang diberi warna merah tidak Jan jelaskan lagi.
pada bagian ini pak Ghozi menggunakan fungsi OffSet, mari kita bahas maksud
dan tujuan penggunaan fungsi ini.
syntax OffSet adalah sebagai berikut:
=OffSet(cell_atau_range_yang_menjadi_rujukan_atau_titik_nol_atau_titik_awal_untuk_bergerak
, nomor_indeks_baris_dari_titik_awal , nomor_indeks_kolom_dari_titik_awal ,
tinggi_baris , lebar_kolom)
pada penggunaan fungsi OffSet kali ini oleh Pak Ghozi hanya memanfaatkan
Titik awall , nomer indeks baris , dan nomer indeks kolom saja, sedangkan
tinggi baris dan kolom yang bersifat opsional tidak digunakan karena hanya
nilai 1 cell yang diambil.
lihat yang diberi warna biru ('File-01'!$A$6), ini adalah titik start awal
atau titik nol, letaknya di sheet File-01 pada cell A6
untuk posisi barisnya diperoleh dari perhitungan antara sisa hasil pembagian
MOD , nilai baris setelah dikurangi dengan 1 yaitu Row(1:1)-1, dengan jumlah
data yang tidak kosong di sheet File-01 mulai cell A7 sampai cell A40
COUNTA('File-01'!$A$7:$A$40))+1
untuk posisi kolomnya diperoleh dari pengambilan nilai kolom setelah
dikurangi 1 COLUMN()-1
Untuk lebih mudah mengerti bagian keTiga dan keEmpat, coba pelajari cara
penggunaan fungsi OffSet di dalam lembar kerja
===================================================================================================================================================================
Bagian keEmpat adalah pengambilan nilai jika kondisi If tidak terpenuhi,
logika dan penjelasannya sama dengan bagian keTiga.
Semoga penjelasan ini dapat dimengerti dan dipahami, jika ada yang kurang
jelas silakan ditanyakan kembali.
cmiiw,
-Ms. Jan Raisin-
Pada 15 Desember 2012 11:43, Mulki <mu...@aop.component.astra.co.id> menulis:
Dear Pak Ghozi ...
Bisa bantu cara baca/penjelasan dari formula bapak dibawah ?
Terimakasih atas pencerahannya
Wassalam,
mulki
----- Original Message -----
From: ghozi alkatiri
To: belajar-excel@yahoogroups.com
Sent: Friday, December 14, 2012 5:17 PM
Subject: Bls: [belajar-excel] Menggabungkan 2 Data absensi ke dalam 1
File gabung absensi
tulis formula pertama di A7
=IF(ROW(1:1)<=COUNTA('File-01'!$A$7:$A$40);OFFSET('File-01'!$A$6;MOD(ROW(1:1)-1;COUNTA('File-01'!$A$7:$A$40))+1;COLUMN()-1);OFFSET('File-02'!$A$6;ROW(1:1)-COUNTA('File-01'!$A$7:$A$40)+1;COLUMN()-1))
copy ke kanan dan ke bawah
pada cell berisi 0 (nol) dari tabel yang kosong dihilangkan dengan
conditional formating opsi font no colour.
selanjutnya lihat langsung di attachment sheet solusi
wassalam
Ghozi Alkatiri
--------------------------------------------------------------------------
Dari: Rachmad HIDAYAT <rachmad.hida...@external.total.com>
Kepada: "belajar-excel@yahoogroups.com" <belajar-excel@yahoogroups.com>
Dikirim: Jumat, 14 Desember 2012 15:17
Judul: [belajar-excel] Menggabungkan 2 Data absensi ke dalam 1 File
gabung absensi
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 bantuan nya ya
Terima Kasih,
Rachmad Hidayat
Inspection Method
3487
