kirimpertanyaan

 

Belajar dan konsultasi masalah Excel      

Salam Excel,

Jika pada tutorial sebelumnya mendeteksi value ganda menggunakan formula FREQUENCY maka pada tutorial ini menggunakan formula umum yaitu COUNTIF. Namun menjadi tidak umum jika ditulis menjadi rumus panjang atau yang dikenal mega formula seperti ini:

=IF(LEN(REPLACE(IF(COUNTIF(D11:T11,D11)>1,", "&D11,"")&IF(COUNTIF(F11:T11,F11)>1,", "&F11,"")&IF(COUNTIF(H11:T11,H11)>1,", "&H11,"")&IF(COUNTIF(J11:T11,J11)>1,", "&J11,"")&IF(COUNTIF(L11:T11,L11)>1,", "&L11,"")&IF(COUNTIF(N11:T11,N11)>1,", "&N11,"")&IF(COUNTIF(P11:T11,P11)>1,", "&P11,"")&IF(COUNTIF(R11:T11,R11)>1,", "&R11,""),1,2,""))=0,"nihil",REPLACE(IF(COUNTIF(D11:T11,D11)>1,", "&D11,"")&IF(COUNTIF(F11:T11,F11)>1,", "&F11,"")&IF(COUNTIF(H11:T11,H11)>1,", "&H11,"")&IF(COUNTIF(J11:T11,J11)>1,", "&J11,"")&IF(COUNTIF(L11:T11,L11)>1,", "&L11,"")&IF(COUNTIF(N11:T11,N11)>1,", "&N11,"")&IF(COUNTIF(P11:T11,P11)>1,", "&P11,"")&IF(COUNTIF(R11:T11,R11)>1,", "&R11,""),1,2,""))

Tutorial ini adalah solusi alternative dari solusi tutorial xxx dimana formula bertujuan untuk mendeteksi kode ganda pada sebuah penjadwalan. Formula pada tutorial Menandai Kode tersebut ditulis seperti dibawah:

=IF(FREQUENCY(D11:T11,D11:T11)>1,D11,IF(FREQUENCY(F11:T11,F11:T11)>1,F11,IF(FREQUENCY(H11:T11,H11:T11)>1,H11,IF(FREQUENCY(J11:T11,J11:T11)>1,J11,IF(FREQUENCY(L11:T11,L11:T11)>1,L11,IF(FREQUENCY(N11:T11,N11:T11)>1,N11,  IF(FREQUENCY(P11:T11,P11:T11)>1,P11,IF(FREQUENCY(R11:T11,R11:T11)>1,R11,"nihil"))))))))

Formula ini hanya menulis satu kode pertama ganda yang terdeteksi lebih awal dan jika tidak ada kode ganda maka akan ditulis kata “nihil”. Kode yang ganda yang lebih dari dua tidak akan ter-display sebelum kode ganda yang ditampilkan di-edit menjadi data single atau tunggal atau unik. Pada gambar dibawah pada baris 15 kode hanya ditulis 34 sementara 45 belum muncul.

 

duplicate countif 1Gambar 1.1

 

Pada gambar berikutnya ketika 34 diedit menjadi kode yang unik di baris tersebut maka pada kolom resume menuliskan kode ganda berikutnya. Dan seterusnya berlaku untuk kode ganda berikutnya.

duplicate countif 1.2Gambar 1.2

 

Kekuatan mega formula alternative pada tutorial kali ini ada pada point 2 dibawah yaitu eksekusi data ganda yang telah dideteksi oleh rumus utama. Eksekusi disini berupa penulisan semua kode ganda. Formula ini bertujuan untuk memenuhi requirement sbb:

  1. Mendeteksi dan menulis kode ganda (kode utama)
  2. Menulis kode ganda lebih dari satu dan tanda koma sebagai pemisah
  3. Menulis “nihil” jika tidak ada kode ganda

duplicate countif 1.3Gambar 1.3

 

Cobalah ikuti tahapan-tahapan hasil dari meditasi saya dibawah:

Pada file terlampir, pilih sheet meditasi dan mulai tulis

U11: =COUNTIF(D11:T11,D11) … tekan enter

Hasilnya U11: 1

Ulangi kembali

U11: =IF(COUNTIF(D11:T11,D11)>1,D11,"")

Hasilnya U11: 34

Berikutnya untuk deteksi kode ganda untuk 28 pada cell F11, tuliskan

U11: =IF(COUNTIF(D11:T11,D11)>1,D11,"")&IF(COUNTIF(D11:T11,F11)>1,F11,"")

Hasilnya U11: 3428

Fungsi tanda “&” adalah penggabung hasil dua formula.

Sukses untuk meditasi tahap pertama sudah anda lakukan sekaligus menjawab requirement pertama diatas.

Selanjutnya meditasi kedua menambahkan tanda koma untuk pemisah. Modifikasi formula diatas menjadi

U11: =IF(COUNTIF(D11:T11,D11)>1,", "&D11,"")& IF(COUNTIF(D11:T11,F11)>1,", "&F11,"")

Hasilnya U11:  , 34, 28

Abaikan koma didepan 34, tuliskan lebih panjang lagi dan copas ke cell dibawahnya

U11:

=IF(COUNTIF(D11:T11,D11)>1,", "&D11,"")&IF(COUNTIF(F11:T11,F11)>1,", "&F11,"")&IF(COUNTIF(H11:T11,H11)>1,", "&H11,"")&IF(COUNTIF(J11:T11,J11)>1,", "&J11,"")&IF(COUNTIF(L11:T11,L11)>1,", "&L11,"")&IF(COUNTIF(N11:T11,N11)>1,", "&N11,"")&IF(COUNTIF(P11:T11,P11)>1,", "&P11,"")&IF(COUNTIF(R11:T11,R11)>1,", "&R11,"")

Hasil pada beberapa cell sbb:

U11:  , 34, 28

U12:  , 50

U13:  kosong

U14:  , 14, 21, 14, 21, 21

U15:  , 34, 45

Pada menulis sekali kode yang sama lebih dari 2 kali maka tulis ulang rumus dengan mengganti “>1” menjadi “=2”

U11:

=IF(COUNTIF(D11:T11,D11)=2,", "&D11,"")&IF(COUNTIF(F11:T11,F11)=2,", "&F11,"")&IF(COUNTIF(H11:T11,H11)=2,", "&H11,"")&IF(COUNTIF(J11:T11,J11)=2,", "&J11,"")&IF(COUNTIF(L11:T11,L11)=2,", "&L11,"")&IF(COUNTIF(N11:T11,N11)=2,", "&N11,"")&IF(COUNTIF(P11:T11,P11)=2,", "&P11,"")&IF(COUNTIF(R11:T11,R11)=2,", "&R11,"")

Kemudian copas kebawah. Hasilnya sbb:

U14:  , 14, 21

Meditasi kedua selesai

Selanjutnya adalah menghapus satu koma pada awal hasil, lengkapi formula dengan REPLACE

Bagaimana REPLACE bekerja? Cek fungsi pada menu Help Ms. Excel anda … disana dibeberkan fungsi dan contoh soal penggunaan formula tersebut. Tulis kembali

U11:

=REPLACE(IF(COUNTIF(D11:T11,D11)=2,", "&D11,"")&IF(COUNTIF(F11:T11,F11)=2,", "&F11,"")&IF(COUNTIF(H11:T11,H11)=2,", "&H11,"")&IF(COUNTIF(J11:T11,J11)=2,", "&J11,"")&IF(COUNTIF(L11:T11,L11)=2,", "&L11,"")&IF(COUNTIF(N11:T11,N11)=2,", "&N11,"")&IF(COUNTIF(P11:T11,P11)=2,", "&P11,"")&IF(COUNTIF(R11:T11,R11)=2,", "&R11,""),1,2,"")

Jika disederhanakan:

=REPLACE(formula meditasi kedua,1,2,"")

Pada gambar dibawah diterangkan

duplicate countif 2.1

Yaa, menghapus oleh formula REPLACE teknisnya adalah mengganti koma dan spasi dengan tanpa value atau blank yang diwakili penulisannya dengan tanda petik dua diatas sebanyak dua buah tanpa spasi. Satu spasi dihitung satu karakter oleh excel maka jumlah 2 karakter yang digantikan blank diatas adalah menghapus koma dan satu spasi.

Meditasi ketiga DONE

 

Requirement terakhir adalah info nihil untuk baris tanpa ada kode ganda. Kembali tulis formula

U11:

=IF(LEN(REPLACE(IF(COUNTIF(D11:T11,D11)=2,", "&D11,"")&IF(COUNTIF(F11:T11,F11)=2,", "&F11,"")&IF(COUNTIF(H11:T11,H11)=2,", "&H11,"")&IF(COUNTIF(J11:T11,J11)=2,", "&J11,"")&IF(COUNTIF(L11:T11,L11)=2,", "&L11,"")&IF(COUNTIF(N11:T11,N11)=2,", "&N11,"")&IF(COUNTIF(P11:T11,P11)=2,", "&P11,"")&IF(COUNTIF(R11:T11,R11)=2,", "&R11,""),1,2,""))=0,"nihil",REPLACE(IF(COUNTIF(D11:T11,D11)=2,", "&D11,"")&IF(COUNTIF(F11:T11,F11)=2,", "&F11,"")&IF(COUNTIF(H11:T11,H11)=2,", "&H11,"")&IF(COUNTIF(J11:T11,J11)=2,", "&J11,"")&IF(COUNTIF(L11:T11,L11)=2,", "&L11,"")&IF(COUNTIF(N11:T11,N11)=2,", "&N11,"")&IF(COUNTIF(P11:T11,P11)=2,", "&P11,"")&IF(COUNTIF(R11:T11,R11)=2,", "&R11,""),1,2,""))

Jangan panik! Formula diatas jika disederhanakan menjadi

=IF(LEN(formula meditasi ketiga)=0,"nihil", formula meditasi ketiga))

Hasil pada beberapa cell sbb:

U11:  34, 28

U12:  50

U13:  nihil

U14:  14, 21

U15:  34, 45

duplicate countif 2.2

Meditasi berakhir. Formula yang buat anda tak bisa tidur telah hadir dari jari anda.

Pada tahap akhir ini anda telah membuktikan pada dunia … bahwa anda telah berhasil menulis mega formula. BRAVOOO

Mudah kan?

 

Semoga bermanfaat

Salam COUNTIF

Klinikexcel

  



Kitab VBA Excel Level Satu

Buat pemula yang serius pengen belajar MACRO VBA Excel, silahkan baca buku berikut:

Kitab VBA Excel Level Satu edisi 2 sEDISI 2 : Rp. 60.000

BELI VERSI CETAK KLIK DI SINI

BELI VERSI DIGITAL KLIK DI SINI

LIHAT PREVIEW KLIK DI SINI

 

Partners

logo baru local1news

Newsletter

Daftarkan email anda untuk mendapatkan update dari Klinik Excel