Formula generică
=A1+(COUNTIF(exp_rng,A1)-1)*adjustment
rezumat
Pentru a rupe legăturile, puteți utiliza o coloană de ajutor și funcția COUNTIF pentru a regla valorile, astfel încât acestea să nu conțină duplicate și, prin urmare, să nu conducă la legături. În exemplul prezentat, formula din D5 este:
=C5+(COUNTIF($C$5:C5,C5)-1)*0.01
Context
Uneori, când utilizați funcții precum SMALL, LARGE sau RANK pentru a clasa cele mai mari sau cele mai mici valori, ajungeți la legături, deoarece datele conțin duplicate. O modalitate de a rupe legături de acest fel este de a adăuga o coloană de ajutor cu valori care au fost ajustate, apoi clasifica valorile respective în locul originalelor.
În acest exemplu, logica utilizată pentru ajustarea valorilor este aleatorie - prima valoare duplicat va „câștiga”, dar puteți regla formula pentru a utiliza logica care se potrivește situației dvs. specifice și cazului de utilizare.
Explicaţie
La bază, această formulă folosește funcția COUNTIF și un interval extins pentru a număra aparițiile valorilor. Referința de extindere este utilizată astfel încât COUNTIFS să returneze un număr de apariții, în loc de un număr total pentru fiecare valoare:
COUNTIF($C$5:C5,C5)
Apoi, 1 este scăzut din rezultat (ceea ce face ca numărul tuturor valorilor non-duplicate să fie zero) și rezultatul este înmulțit cu 0,01. Această valoare este „ajustarea” și intenționat de mică pentru a nu avea un impact material asupra valorii inițiale.
În exemplul prezentat, Metrolux și Diamond au ambele aceeași estimare de 5000 USD. Deoarece Metrolux apare prima în listă, numărul de rulare de 5000 este 1 și este anulat scăzând 1, astfel încât estimarea rămâne neschimbată în coloana de ajutor:
=C8+(COUNTIF($C$5:C8,C8)-1)*0.01 =C8+(1-1)*0.01 =C8+0 =C8
Cu toate acestea, pentru Diamond, numărul de rulare de 5000 este 2, deci estimarea este ajustată:
=C11+(COUNTIF($C$5:C11,C11)-1)*0.01 =C11+(2-1)*0.01 =C11+1*0.01 =C11+0.01
În cele din urmă, valorile ajustate sunt utilizate pentru clasare în locul valorilor originale din coloanele G și H. Formula din G5 este:
=SMALL($D$5:$D$12,F5)
Formula din H5:
=INDEX($B$5:$B$12,MATCH(G5,$D$5:$D$12,0))
Consultați această pagină pentru o explicație a acestor formule.
Coloana de ajutor temporar
Dacă nu doriți să utilizați o coloană de ajutor în soluția finală, puteți utiliza temporar o coloană de ajutor pentru a obține valori calculate, apoi utilizați Lipire specială pentru a converti valorile „la locul” și ștergeți apoi coloana de ajutor. Acest videoclip demonstrează tehnica.
