Üldine valem
=A1+(COUNTIF(exp_rng,A1)-1)*adjustment
Kokkuvõte
Seoste katkestamiseks saate abiveeru ja funktsiooni COUNTIF abil kohandada väärtusi nii, et need ei sisaldaks duplikaate ja seetõttu ei tekiks sidemeid. Näidatud näites on valem D5-s:
=C5+(COUNTIF($C$5:C5,C5)-1)*0.01
Sisu
Mõnikord, kui kasutate suurimate või madalamate väärtuste järjestamiseks selliseid funktsioone nagu VÄIKE, SUUR või RANG, on teil lõpuks sidemed, kuna andmed sisaldavad duplikaate. Üks viis niimoodi sidemete katkestamiseks on lisada kohandatud väärtustega abiveerg ja seejärel järjestada need väärtused originaalide asemel.
Selles näites on väärtuste korrigeerimiseks kasutatav loogika juhuslik - esimene duplikaatväärtus "võidab", kuid saate valemi kohandada loogika kasutamiseks, mis sobib teie konkreetse olukorra ja kasutusjuhtumiga.
Selgitus
Põhimõtteliselt kasutab see valem väärtuste esinemiste loendamiseks funktsiooni COUNTIF ja laienevat vahemikku. Laiendavat viidet kasutatakse nii, et COUNTIFS tagastab jooksva esinemiste arvu iga väärtuse koguarvu asemel:
COUNTIF($C$5:C5,C5)
Järgmisena lahutatakse tulemusest 1 (mis muudab kõigi mittekorduvate väärtuste arvu nulliks) ja tulemus korrutatakse 0,01-ga. See väärtus on "korrigeerimine" ja on tahtlikult väike, et see ei mõjutaks oluliselt algset väärtust.
Näidatud näites on Metroluxil ja Diamondil mõlemal sama hinnang 5000 dollarit. Kuna Metrolux ilmub loendis esimesena, on jooksuarv 5000 ja see tühistatakse lahutades 1, nii et hinnang jääb abistaja veerus muutumatuks:
=C8+(COUNTIF($C$5:C8,C8)-1)*0.01 =C8+(1-1)*0.01 =C8+0 =C8
Kuid Diamondil on jooksuarv 5000, seega hinnangut korrigeeritakse:
=C11+(COUNTIF($C$5:C11,C11)-1)*0.01 =C11+(2-1)*0.01 =C11+1*0.01 =C11+0.01
Lõpuks kasutatakse reastamiseks järjestatud väärtusi veergude G ja H algväärtuste asemel.
=SMALL($D$5:$D$12,F5)
Valem H5-s:
=INDEX($B$5:$B$12,MATCH(G5,$D$5:$D$12,0))
Nende valemite selgitusi leiate sellelt lehelt.
Ajutine abistaja veerg
Kui te ei soovi lõpplahenduses kasutada abistaja veergu, võite arvutatud väärtuste saamiseks ajutiselt kasutada abistaja veergu, seejärel kasutada väärtust "paigas" teisendamiseks funktsiooni Kleebi spetsiaalne ja seejärel abistaja veerg kustutada. See video näitab tehnikat.
