Значения уровней формул Excel в наборе данных на основе значений страны находятся в

Я считаю, что ваши вручную рассчитанные уровни неверны. Однако вот начальный метод, который вам нужно будет настроить, чтобы он работал в соответствии с вашей собственной логикой.

Сначала вставьте вспомогательный столбец, который получает «граничную» точку для верхних 20% — формула

=IFERROR(PERCENTILE.INC(IF($A$2:$A$80=A2, $B$2:$B$80), 0.2), "")

Сделайте то же самое для точки 50%.

=IFERROR(PERCENTILE.INC(IF($A$2:$A$80=A2, $B$2:$B$80), 0.5), "")

Столбец для расчета уровня становится

=IF(B2 = "", 4, IF(B2>=C2, 1, IF(B2>=D2, 2, 3)))

Я предположил, что страна указана в столбце A, а ставка — в столбце B. Следите за диапазоном строк — я использовал только от 2 до 80.

Не уверен, правильно ли я понял логику:

=IF([@Rate] = "",4,XMATCH(PERCENTOF(XMATCH([@Rate],SORT(FILTER([Rate],[Country]=[@Country]))),ROWS(FILTER([Rate],[Country]=[@Country]))),{1;0.8;0.5},1))

При этом проверяется, находится ли значение в пределах 20% строк самых высоких значений, следующих 30% и т. д.

Не уверен, как вы ожидаете, что он будет вести себя в случае совпадения значений на уровнях.

Использование PERCENTRANK.INC (о существовании которого я не знал до прочтения ответов): =IF([@Rate] = "",4,XMATCH(PERCENTRANK.INC(FILTER([Rate],[Country]=[@Country]),[@Rate]),{1,0.8,0.5},1))

или: =IF([@Rate] = "",4,3-SUM(N(PERCENTRANK.INC(FILTER([Rate],[Country]=[@Country]),[@Rate])>{1,0.8,0.5})))

или используя диапазоны: =LET(d,A2:C26,a,TAKE(d,,1),b,INDEX(d,,2),MAP(a,b,LAMBDA(c,d,IF(d = "",4,XMATCH(PERCENTRANK.INC(FILTER(b,a=c),d),{1,0.8,0.5},1)))))

Другое решение с использованием таблицы:

=LET(countryData,FILTER([Rate],([Country]=[@Country])*([Rate]<>"")),
    rankedValue,IFNA(PERCENTRANK.INC(countryData,[@Rate]),0),
    XLOOKUP(rankedValue,{0.8;0.5;0},{1;2;3},"",-1))

На основе очень хорошего ответа Айка: использование REDUCE для получения результата за пределами таблицы; не уверен, улучшит ли это производительность.

=LET(
    percentages_for_tiers, {0.8, 0.5, 0},
    tiers, SEQUENCE(, COUNT(percentages_for_tiers)),
    has_value, LAMBDA(a, LEN(TRIM(a))),
    rate_tier, LAMBDA(country,
        LET(
            rates_for_country, FILTER(
                RatesByCounty[Rate],
                RatesByCounty[Country] = country
            ),
            non_empty_rates, FILTER(
                RatesByCounty[Rate],
                has_value(RatesByCounty[Rate]) *
                    (RatesByCounty[Country] = country)
            ),
            ranked_values, PERCENTRANK.INC(
                non_empty_rates,
                rates_for_country
            ),
            tier, XLOOKUP(
                ranked_values,
                percentages_for_tiers,
                tiers,
                ,
                -1
            ),
            comment_1, "Filter etc. return 0 for blank cells, so replace these zeros",
            non_empty_rate, FILTER(
                has_value(RatesByCounty[Rate]),
                RatesByCounty[Country] = country
            ),
            IF(non_empty_rate, tier, "")
        )
    ),
    result, REDUCE(
        "Tier",
        UNIQUE(RatesByCounty[Country]),
        LAMBDA(acc, cur, VSTACK(acc, rate_tier(cur)))
    ),
    result
)