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 <[email protected]>menulis:
> **
>
>
> **
> Dear Pak Ghozi ...
>
> Bisa bantu cara baca/penjelasan dari formula bapak dibawah ?
>
> Terimakasih atas pencerahannya
>
> Wassalam,
> mulki
>
>
> ----- Original Message -----
> *From:* ghozi alkatiri <[email protected]>
> *To:* [email protected]
> *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 <[email protected]>
> *Kepada:* "[email protected]" <[email protected]>
> *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
>
>
>
>
>
