Obecný vzorec
=A1+(COUNTIF(exp_rng,A1)-1)*adjustment
souhrn
Chcete-li přerušit vazby, můžete pomocí pomocného sloupce a funkce COUNTIF upravit hodnoty tak, aby neobsahovaly duplikáty, a proto nebudou mít za následek vazby. V zobrazeném příkladu je vzorec v D5:
=C5+(COUNTIF($C$5:C5,C5)-1)*0.01
Kontext
Někdy, když použijete funkce jako SMALL, LARGE nebo RANK k seřazení nejvyšších nebo nejnižších hodnot, skončíte s vazbami, protože data obsahují duplikáty. Jedním ze způsobů přerušení vazeb, jako je tento, je přidání pomocného sloupce s hodnotami, které byly upraveny, a potom pořadí těchto hodnot místo originálů.
V tomto příkladu je logika použitá k úpravě hodnot náhodná - první duplicitní hodnota „vyhraje“, ale můžete upravit vzorec tak, aby používal logiku, která odpovídá vaší konkrétní situaci a případu použití.
Vysvětlení
V základu používá tento vzorec funkci COUNTIF a rozšiřující se rozsah k počítání výskytů hodnot. Rozšiřující odkaz se používá tak, že COUNTIFS vrací průběžný počet výskytů, místo celkového počtu pro každou hodnotu:
COUNTIF($C$5:C5,C5)
Dále se od výsledku odečte 1 (což činí počet všech neduplicitních hodnot nulový) a výsledek se vynásobí 0,01. Tato hodnota je „úpravou“ a je záměrně malá, aby podstatně neovlivnila původní hodnotu.
V ukázaném příkladu mají Metrolux i Diamond stejný odhad 5 000 USD. Vzhledem k tomu, že Metrolux se v seznamu objevuje jako první, je počet spuštěných 5 000 1 a je zrušen odečtením 1, takže odhad zůstane ve sloupci pomocníka nezměněn:
=C8+(COUNTIF($C$5:C8,C8)-1)*0.01 =C8+(1-1)*0.01 =C8+0 =C8
Pro Diamond je však počet běhů 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
Nakonec se upravené hodnoty použijí pro hodnocení místo původních hodnot ve sloupcích 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 této stránce naleznete vysvětlení těchto vzorců.
Dočasný pomocný sloup
Pokud ve výsledném řešení nechcete použít sloupec pomocníka, můžete k výpočtu vypočítaných hodnot použít dočasně sloupec pomocníka, poté pomocí příkazu Vložit jinak převést hodnoty „na místě“ a sloupec pomocníka následně odstranit. Toto video ukazuje techniku.
