Formula umum
=A1+(COUNTIF(exp_rng,A1)-1)*adjustment
Ringkasan
Untuk memutuskan hubungan, Anda bisa menggunakan kolom pembantu dan fungsi COUNTIF untuk menyesuaikan nilai sehingga tidak mengandung duplikat, dan oleh karena itu tidak akan menghasilkan hubungan. Dalam contoh yang ditampilkan, rumus di D5 adalah:
=C5+(COUNTIF($C$5:C5,C5)-1)*0.01
Konteks
Terkadang, saat Anda menggunakan fungsi seperti SMALL, LARGE, atau RANK untuk memberi peringkat nilai tertinggi atau terendah, Anda berakhir dengan ikatan, karena data berisi duplikat. Salah satu cara untuk memutuskan hubungan seperti ini adalah dengan menambahkan kolom pembantu dengan nilai yang telah disesuaikan, lalu memberi peringkat nilai tersebut, bukan aslinya.
Dalam contoh ini, logika yang digunakan untuk menyesuaikan nilai adalah acak - nilai duplikat pertama akan "menang", tetapi Anda dapat menyesuaikan rumus untuk menggunakan logika yang sesuai dengan situasi dan kasus penggunaan Anda.
Penjelasan
Intinya, rumus ini menggunakan fungsi COUNTIF dan rentang yang diperluas untuk menghitung kemunculan nilai. Referensi perluasan digunakan agar COUNTIFS mengembalikan jumlah kejadian yang berjalan, bukan jumlah total untuk setiap nilai:
COUNTIF($C$5:C5,C5)
Selanjutnya, 1 dikurangi dari hasil (yang membuat hitungan semua nilai bukan duplikat menjadi nol) dan hasilnya dikalikan dengan 0,01. Nilai ini adalah "penyesuaian", dan sengaja dibuat kecil agar tidak berdampak material pada nilai aslinya.
Dalam contoh yang ditunjukkan, Metrolux dan Diamond keduanya memiliki perkiraan yang sama yaitu $ 5.000. Karena Metrolux muncul pertama kali dalam daftar, hitungan lari 5000 adalah 1 dan dibatalkan dengan mengurangi 1, jadi perkiraannya tetap tidak berubah di kolom pembantu:
=C8+(COUNTIF($C$5:C8,C8)-1)*0.01 =C8+(1-1)*0.01 =C8+0 =C8
Namun, untuk Diamond, hitungan lari 5000 adalah 2, jadi perkiraannya disesuaikan:
=C11+(COUNTIF($C$5:C11,C11)-1)*0.01 =C11+(2-1)*0.01 =C11+1*0.01 =C11+0.01
Akhirnya, nilai yang disesuaikan digunakan untuk peringkat daripada nilai asli di kolom G dan H. Rumus di G5 adalah:
=SMALL($D$5:$D$12,F5)
Rumus di H5:
=INDEX($B$5:$B$12,MATCH(G5,$D$5:$D$12,0))
Lihat halaman ini untuk penjelasan tentang formula ini.
Kolom pembantu sementara
Jika Anda tidak ingin menggunakan kolom pembantu di solusi akhir, Anda dapat menggunakan kolom pembantu sementara untuk mendapatkan nilai terhitung, kemudian gunakan Tempel Khusus untuk mengonversi nilai "di tempat" dan menghapus kolom pembantu setelahnya. Video ini menunjukkan tekniknya.
