Vispārēja formula
=A1+(COUNTIF(exp_rng,A1)-1)*adjustment
Kopsavilkums
Lai pārtrauktu saites, varat izmantot palīgkolonnu un funkciju COUNTIF, lai pielāgotu vērtības, lai tajās nebūtu dublikātu un tāpēc neizveidotos saites. Parādītajā piemērā formula D5 ir:
=C5+(COUNTIF($C$5:C5,C5)-1)*0.01
Konteksts
Dažreiz, kad jūs izmantojat tādas funkcijas kā MAZA, LIELA vai RANK, lai ranžētu augstākās vai zemākās vērtības, jums rodas saites, jo datos ir dublikāti. Viens no veidiem, kā pārtraukt šādas saites, ir pievienot palīgkolonnu ar pielāgotām vērtībām, pēc tam šīs vērtības sarindot oriģinālu vietā.
Šajā piemērā vērtību pielāgošanai izmantotā loģika ir nejauša - pirmā vērtības dublikāts "uzvarēs", bet jūs varat pielāgot formulu, lai izmantotu loģiku, kas atbilst jūsu konkrētajai situācijai un lietošanas gadījumam.
Paskaidrojums
Būtībā šī formula izmanto funkciju COUNTIF un paplašināšanas diapazonu, lai uzskaitītu vērtību gadījumus. Paplašināšanas atsauce tiek izmantota tā, lai COUNTIFS atgrieztu notiekošo gadījumu skaitu, nevis katras vērtības kopējo skaitu:
COUNTIF($C$5:C5,C5)
Pēc tam no rezultāta tiek atņemts 1 (kas padara visu nedublēto vērtību skaitu nulli), un rezultāts tiek reizināts ar 0,01. Šī vērtība ir "korekcija", un tā ir apzināti maza, lai būtiski neietekmētu sākotnējo vērtību.
Parādītajā piemērā Metrolux un Diamond abiem ir vienāds vērtējums 5000 USD. Tā kā Metrolux sarakstā tiek parādīts pirmais, skriešanas skaitlis 5000 ir 1 un tiek atcelts, atņemot 1, tāpēc aprēķins palīgā slejā paliek nemainīgs:
=C8+(COUNTIF($C$5:C8,C8)-1)*0.01 =C8+(1-1)*0.01 =C8+0 =C8
Tomēr Dimantam skriešanas skaitlis 5000 ir 2, tāpēc tāme tiek koriģēta:
=C11+(COUNTIF($C$5:C11,C11)-1)*0.01 =C11+(2-1)*0.01 =C11+1*0.01 =C11+0.01
Visbeidzot, sarindošanai tiek izmantotas koriģētās vērtības, nevis sākotnējās vērtības kolonnās G un H. G5 formula ir:
=SMALL($D$5:$D$12,F5)
H5 formula:
=INDEX($B$5:$B$12,MATCH(G5,$D$5:$D$12,0))
Šo formulu skaidrojumu skatiet šajā lapā.
Pagaidu palīga kolonna
Ja galīgajā risinājumā nevēlaties izmantot palīgkolonnu, varat īslaicīgi izmantot palīgkolonnu, lai iegūtu aprēķinātās vērtības, pēc tam izmantojiet Īpašā ielīmēšana, lai pārvērstu vērtības "vietā" un pēc tam izdzēstu palīgkolonnu. Šis video demonstrē tehniku.
