Všeobecný vzorec
=A1+(COUNTIF(exp_rng,A1)-1)*adjustment
Zhrnutie
Ak chcete prerušiť väzby, môžete pomocou pomocného stĺpca a funkcie COUNTIF upraviť hodnoty tak, aby neobsahovali duplikáty, a preto nebudú mať za následok väzby. V zobrazenom príklade je vzorec v D5:
=C5+(COUNTIF($C$5:C5,C5)-1)*0.01
Kontext
Niekedy, keď na zoradenie najvyšších alebo najnižších hodnôt použijete funkcie ako MALÉ, VEĽKÉ alebo HODNOTENIE, skončíte s väzbami, pretože údaje obsahujú duplikáty. Jedným zo spôsobov, ako takto prerušiť väzby, je pridať pomocný stĺpec s hodnotami, ktoré boli upravené, a potom tieto hodnoty zoradiť namiesto originálov.
V tomto príklade je logika použitá na úpravu hodnôt náhodná - prvá duplikovaná hodnota „vyhrá“, ale vzorec môžete upraviť tak, aby používal logiku, ktorá vyhovuje vašej konkrétnej situácii a prípadu použitia.
Vysvetlenie
Tento vzorec v jadre používa na počítanie výskytov hodnôt funkciu COUNTIF a rozširujúci sa rozsah. Rozširujúca referencia sa používa na to, aby COUNTIFS vrátil priebežný počet výskytov, namiesto celkového počtu pre každú hodnotu:
COUNTIF($C$5:C5,C5)
Ďalej sa od výsledku odpočíta 1 (čím sa počet všetkých neduplikovaných hodnôt rovná nule) a výsledok sa vynásobí 0,01. Táto hodnota je „úpravou“ a je zámerne malá, aby nemala podstatný vplyv na pôvodnú hodnotu.
V zobrazenom príklade majú spoločnosti Metrolux a Diamond rovnaký odhad 5 000 dolárov. Pretože Metrolux je na prvom mieste v zozname, priebežný počet 5 000 je 1 a je zrušený odpočítaním 1, takže odhad zostáva v stĺpci Pomocník nezmenený:
=C8+(COUNTIF($C$5:C8,C8)-1)*0.01 =C8+(1-1)*0.01 =C8+0 =C8
Pre Diamond je však priebežný počet 5 000 2, takže odhad je upravený:
=C11+(COUNTIF($C$5:C11,C11)-1)*0.01 =C11+(2-1)*0.01 =C11+1*0.01 =C11+0.01
Nakoniec sa upravené hodnoty použijú na zoradenie namiesto pôvodných hodnôt v stĺpcoch G a H. Vzorec v G5 je:
=SMALL($D$5:$D$12,F5)
Vzorec v H5:
=INDEX($B$5:$B$12,MATCH(G5,$D$5:$D$12,0))
Na tejto stránke nájdete vysvetlenie týchto vzorcov.
Dočasný pomocný stĺp
Ak vo výslednom riešení nechcete použiť stĺpec pomocníka, môžete na výpočet vypočítaných hodnôt použiť stĺpec pomocníka dočasne, potom pomocou príkazu Prilepiť špeciálne prevediete hodnoty „na miesto“ a stĺpec pomocníka potom odstránite. Toto video ukazuje túto techniku.
