Excel: как упростить растущую вложенную формулу IF и?

Question

У меня есть ряд формул И И, и они достигнут предела. Какое лучшее решение для замены?

НАПРИМЕР.

IF(AND(AC9=1,AC4=(AB4+7)),1&"b",
IF(AND(AC9=2,AC4=(AB4+7)),2&"b",
IF(AND(AC9=3,AC4=(AB4+7)),3&"b",
IF(AND(AC9=4,AC4=(AB4+7)),4&"b",
IF(AND(AC9=5,AC4=(AB4+7)),5&"b",)))))

или проще

IF(AB18=1,(100%+AB17)*($D$271*AB16),
IF(AB18=2,(100%+AB17)*($E$271*AB16),
IF(AB18=3,(100%+AB17)*($F$271*AB16),
IF(AB18=4,(100%+AB17)*($G$271*AB16),
IF(AB18=5,(100%+AB17)*($H$271*AB16),)))))

Все переменные находятся в нескольких строках. Верхний ряд пронумерован 1,2,3,4 и т.д.

Вложенные функции работают нормально, но количество вариантов, которые я могу предложить, ограничено. Существуют ли конкретные функции, к которым мы можем обратиться в этих ситуациях?

Answer 1

fixer123, возможно, предоставил вам лучшее решение, используя vlookup против таблицы.

Другой возможный подход, если вы хотите использовать аналогичную версию своей формулы и не можете иметь справочную таблицу, возможно, будет использовать ту же формулу, что и у вас.

=IF(AND(AC9<=5,AC4=(AB4+7)),AC9&"b")

Ваш другой пример, который вы могли бы достичь с помощью чего-то подобного, что определяет адрес ячейки, на который нужно умножить

=IF(AB18<=5,(100%+AB17)*(INDIRECT(ADDRESS(271,AB18+3))*AB16))

Answer 2

Один из подходов - превратить это в поиск. Вот суть примера, подобного вашей первой проблеме.

Я сохранил все в начале таблицы для простоты. Твой АС9 мой С1. Ваш AC4 мой D1. Я просто вставил результат в D1, равный AB4+7 (я просто придумал, каким будет этот результат).

Вы хотите присвоить значения, если AC9 - это число в определенном диапазоне, а AC4 - это определенное значение. Я обрабатываю требование AND, объединяя их.

Столбцы F и G содержат справочную таблицу. Вы можете расширить таблицу по мере необходимости. В таком случае вы можете использовать формулу, которую вы копируете, для заполнения таблицы по мере необходимости. Например, ваша формула F1 может быть:

=ROW()&$AB$4+7

При копировании этого столбца будет использоваться номер строки, чтобы увеличить целевое значение AC9, и объединить его с целевым значением AB4+7 .

Столбец G содержит значение, которое вы хотите. Таким образом, вы можете использовать формулу как:

=ROW()&"b"

Копирование этого столбца заполняет вас значениями результата.

Чтобы сделать поиск, моя формула в A1:

=IFERROR(VLOOKUP($C$1&$D$1,$F$1:$G$5,2,0),"")

Это создает значение поиска путем объединения текущих значений C1 и D1 в моей электронной таблице и сравнения их со значениями в столбце F в поисках точного соответствия. Если он находит совпадение, он извлекает соответствующее значение из столбца G.

Если совпадений нет, поиск выдаст ошибку. Обтекание VLOOKUP с помощью IFERROR присваивает нулевой результат, если есть ошибка (нет совпадения).

Ваш второй пример даже не требует конкатенации, просто таблица поиска, содержащая числа для сравнения AB18, и связанный с ним расчет в качестве результата.