Formula generik
=A1+(COUNTIF(exp_rng,A1)-1)*adjustment
Ringkasan
Untuk memutuskan hubungan, anda boleh menggunakan kolom pembantu dan fungsi COUNTIF untuk menyesuaikan nilai agar tidak mengandungi pendua, dan oleh itu tidak akan menghasilkan ikatan. Dalam contoh yang ditunjukkan, formula dalam D5 adalah:
=C5+(COUNTIF($C$5:C5,C5)-1)*0.01
Konteks
Kadang-kadang, apabila anda menggunakan fungsi seperti KECIL, BESAR, atau RANK untuk menentukan nilai tertinggi atau terendah, anda akan berakhir dengan ikatan, kerana data tersebut mengandungi pendua. Salah satu cara untuk memutuskan hubungan seperti ini adalah dengan menambahkan kolom pembantu dengan nilai yang telah disesuaikan, kemudian nilaikan nilai tersebut dan bukan asalnya.
Dalam contoh ini, logik yang digunakan untuk menyesuaikan nilai adalah rawak - nilai pendua pertama akan "menang", tetapi anda boleh menyesuaikan formula untuk menggunakan logik yang sesuai dengan situasi dan kes penggunaan anda.
Penjelasan
Pada intinya, formula ini menggunakan fungsi COUNTIF dan julat yang berkembang untuk menghitung kejadian nilai. Rujukan pengembangan digunakan sehingga COUNTIFS mengembalikan jumlah kejadian yang berjalan, bukannya jumlah keseluruhan untuk setiap nilai:
COUNTIF($C$5:C5,C5)
Seterusnya, 1 dikurangkan dari hasilnya (yang menjadikan jumlah semua nilai bukan pendua menjadi sifar) dan hasilnya dikalikan dengan 0.01. Nilai ini adalah "penyesuaian", dan sengaja kecil agar tidak mempengaruhi nilai asalnya secara material.
Dalam contoh yang ditunjukkan, Metrolux dan Diamond keduanya mempunyai anggaran yang sama sebanyak $ 5000. Oleh kerana Metrolux muncul pertama dalam senarai, kiraan berjalan 5000 adalah 1 dan dibatalkan dengan mengurangkan 1, jadi anggaran tetap tidak berubah di ruang penolong:
=C8+(COUNTIF($C$5:C8,C8)-1)*0.01 =C8+(1-1)*0.01 =C8+0 =C8
Walau bagaimanapun, untuk Diamond, jumlah berjalan 5000 adalah 2, jadi anggarannya 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 pemeringkatan dan bukannya nilai asal pada lajur G dan H. Rumus dalam G5 adalah:
=SMALL($D$5:$D$12,F5)
Formula dalam H5:
=INDEX($B$5:$B$12,MATCH(G5,$D$5:$D$12,0))
Lihat halaman ini untuk penjelasan mengenai formula ini.
Tiang penolong sementara
Sekiranya anda tidak mahu menggunakan lajur pembantu dalam penyelesaian terakhir, anda boleh menggunakan lajur penolong buat sementara waktu untuk mendapatkan nilai yang dihitung, kemudian gunakan Tampal Khas untuk menukar nilai "di tempat" dan hapus lajur penolong selepas itu. Video ini menunjukkan teknik.
