Загальна формула
=A1+(COUNTIF(exp_rng,A1)-1)*adjustment
Резюме
Щоб розірвати зв’язки, ви можете скористатися допоміжним стовпчиком та функцією COUNTIF для налаштування значень таким чином, щоб вони не містили дублікатів, а отже, не призвели до зв’язків. У наведеному прикладі формула в D5 має вигляд:
=C5+(COUNTIF($C$5:C5,C5)-1)*0.01
Контекст
Іноді, коли ви використовуєте такі функції, як SMALL, LARGE або RANK для ранжирування найвищих чи найнижчих значень, у вас з’являються зв’язки, оскільки дані містять дублікати. Одним із способів розірвати подібні зв'язки є додавання допоміжного стовпця зі скоригованими значеннями, а потім ранжування цих значень замість оригіналів.
У цьому прикладі логіка, яка використовується для коригування значень, є випадковою - перше повторюване значення "виграє", але ви можете налаштувати формулу, використовуючи логіку, яка відповідає вашій конкретній ситуації та випадку використання.
Пояснення
По суті, ця формула використовує функцію COUNTIF та розширюваний діапазон для підрахунку випадків значень. Посилання, що розширюється, використовується таким чином, що COUNTIFS повертає поточний підрахунок випадків, а не загальний підрахунок для кожного значення:
COUNTIF($C$5:C5,C5)
Далі з результату віднімається 1 (що робить підрахунок усіх неповторених значень нульовим) і результат множиться на 0,01. Ця величина є "коригуванням" і навмисно невеликою, щоб істотно не вплинути на початкову вартість.
У наведеному прикладі Metrolux і Diamond мають однакову оцінку в 5000 доларів. Оскільки Metrolux з’являється першим у списку, кількість запущених 5000 дорівнює 1 і скасовується шляхом віднімання 1, тому оцінка залишається незмінною у допоміжному стовпці:
=C8+(COUNTIF($C$5:C8,C8)-1)*0.01 =C8+(1-1)*0.01 =C8+0 =C8
Однак для Diamond кількість роботи 5000 становить 2, тому оцінка коригується:
=C11+(COUNTIF($C$5:C11,C11)-1)*0.01 =C11+(2-1)*0.01 =C11+1*0.01 =C11+0.01
Нарешті, скориговані значення використовуються для ранжирування замість вихідних значень у стовпцях G та H. Формула в G5 має вигляд:
=SMALL($D$5:$D$12,F5)
Формула в H5:
=INDEX($B$5:$B$12,MATCH(G5,$D$5:$D$12,0))
Пояснення цих формул див. На цій сторінці.
Тимчасова допоміжна колонка
Якщо ви не хочете використовувати допоміжний стовпець у остаточному рішенні, ви можете тимчасово використовувати допоміжний стовпець, щоб отримати обчислювані значення, а потім скористатися спеціальною вставкою, щоб перетворити значення "на місці" та видалити допоміжний стовпець потім. Це відео демонструє техніку.
