Общая формула
=A1+(COUNTIF(exp_rng,A1)-1)*adjustment
Резюме
Чтобы разорвать связи, вы можете использовать вспомогательный столбец и функцию СЧЁТЕСЛИ, чтобы настроить значения так, чтобы они не содержали дубликатов и, следовательно, не приводили к связям. В показанном примере формула в D5:
=C5+(COUNTIF($C$5:C5,C5)-1)*0.01
Контекст
Иногда, когда вы используете такие функции, как SMALL, LARGE или RANK для ранжирования самых высоких или самых низких значений, вы получаете связи, поскольку данные содержат дубликаты. Один из способов разорвать подобные связи - добавить вспомогательный столбец со значениями, которые были скорректированы, а затем ранжировать эти значения вместо оригиналов.
В этом примере логика, используемая для корректировки значений, является случайной - первое повторяющееся значение «выиграет», но вы можете настроить формулу для использования логики, которая соответствует вашей конкретной ситуации и варианту использования.
Объяснение
По сути, эта формула использует функцию СЧЁТЕСЛИ и расширяющийся диапазон для подсчета вхождений значений. Раскрывающаяся ссылка используется таким образом, что СЧЁТЕСЛИМН возвращает текущее количество вхождений вместо общего количества для каждого значения:
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))
См. Эту страницу для объяснения этих формул.
Временная вспомогательная колонка
Если вы не хотите использовать вспомогательный столбец в окончательном решении, вы можете временно использовать вспомогательный столбец для получения вычисленных значений, затем использовать Специальную вставку для преобразования значений «на месте» и впоследствии удалить вспомогательный столбец. Это видео демонстрирует технику.
