☰

Сократить формулу Excel «ЕСЛИ»?

tag-icon • tag-icon microsoft-excel worksheet-function
Сократить формулу Excel «ЕСЛИ»?

Эта формула работает, но она огромна:

=IF(X3=B2,K2,IF(X3=B3,K3,IF(X3=B4,K4,IF(X3=B5,K5,IF(X3=B6,K6,IF(X3=B7,K7,IF(X3=B8,K8,IF(X3=B9,K9,IF(X3=B10,K10,IF(X3=B11,K11,IF(X3=B12,K12,IF(X3=B13,K13,IF(X3=B14,K14,IF(X3=B15,K15,IF(X3=B16,K16,IF(X3=B17,K17,IF(X3=B18,K18,IF(X3=B19,K19,IF(X3=B20,K20,IF(X3=B21,K21))))))))))))))))))))

Вот что он делает:

If X3 is the same as B2, show the contents of cell K2.
If X3 is the same as B3, show the contents of cell K3.
If X3 is the same as B4, show the contents of cell K4.
...etc etc etc all the way to...
If X3 is the same as B21, show the contents of cell K21.

Поскольку B2:B21 — это просто столбец ячеек, а K2:K21 — это тоже просто столбец ячеек, есть ли способ сократить приведенную выше формулу, чтобы она не была огромной?

Я не знаю, как превратить это в два диапазона: В-клетки и К-клетки.

Попытка сделать что-то подобное не сработает: =IF(X3=B2:B21,K2:K21)

Потому что указание Excel использовать :означает указание ему сложить все от B2 до B21 и от K2 до K21. Мне было интересно, есть ли какой-то другой разделитель (не :), который указывает Excel обрабатывать каждую ячейку по отдельности, а не складывать их?

Это не работает: =IF(X3=B2-B21,K2-K21)

В результате получается:#VALUE!

Проблема в том, что какой бы номер ни был в ячейке B, он также должен совпадать с соответствующим номером (по горизонтали) в ячейке K.

Заранее спасибо всем, кто знает ответ. Я уверен, что это действительно просто, если такая функция существует в Excel.

решение1

В зависимости от вашей версии Excel вы можете использовать XLOOKUP, VLOOKUPилиINDEX/MATCH

введите описание изображения здесь

=XLOOKUP($X$3,$B$2:$B$22,$K$2:$K$22,"")
=VLOOKUP($X$3,$B$2:$K$22,10,FALSE)
=INDEX($K$2:$K$22,MATCH($X$3,$B$2:$B$22))

решение2

=VLOOKUP(X3;B2:K21;columns(B2:K2))

  • Найдите значение X3̈́ среди ячеек B2:B21 (первый столбец диапазона)
  • при нахождении выберите и отобразите значение B2:K2-столбцы справа от него.

... и Да, VLOOKUP принимает еще один аргумент, который обычно отображается, когда вы вводите имя функции или даже когда вы нажимаете F1 (Справка).
Значение этого аргумента по умолчанию — True, поэтому в этом случае нет необходимости вводить его, но если вам нужно точное совпадение в первом аргументе, то вместо этого необходимо указать здесь «Ложь».

Добавьте IFERROR(...;"Not found")вокруг него окошко, чтобы отобразить указание «ничего не найдено».

--- файл: example.csv --- M4 использован вместо X3 выше

,,,,,,,,,,,,
,1,,,,,,,,,А,,
,2,,,,,,,,,B,,"=ВПР(M4;B2:K21;10;Ложь)"
,3,,,,,,,,,С,,5
,4,,,,,,,,,D,,
,5,,,,,,,,,E,,
,6,,,,,,,,,Ф,,
,7,,,,,,,,,Г,,
,8,,,,,,,,,H,,
,9,,,,,,,,,Я,,
,10,,,,,,,,,J,,
,11,,,,,,,,,К,,
,12,,,,,,,,,Л,,
,13,,,,,,,,,М,,
,14,,,,,,,,,Н,,
,15,,,,,,,,,О,,
,16,,,,,,,,,П,,
,17,,,,,,,,,Q,,
,18,,,,,,,,,Р,,
,19,,,,,,,,,С,,
,20,,,,,,,,,Т,,
,21,,,,,,,,,У,,

решение3

По крайней мере, мы можем избавиться от лишних скобок, используяIFS:

=IFS(X3=B2,K2,X3=B3,K3,X3=B4,K4,X3=B5,K5,X3=B6,K6,X3=B7,K7,X3=B8,K8,X3=B9,K9,X3=B10,K10,X3=B11,K11,X3=B12,K12,X3=B13,K13,X3=B14,K14,X3=B15,K15,X3=B16,K16,X3=B17,K17,X3=B18,K18,X3=B19,K19,X3=B20,K20,X3=B21,K21)

Это общее упрощение, которое работает всякий раз, когда у вас есть IFподобные вложенные функции, даже если различные условия и результаты не имеют ничего общего.

Однако в вашем случае естьявляетсяпростой шаблон для условий, и мы можем упростить ваше выражение еще больше, например, используя usingXLOOKUP:

=XLOOKUP(X3, B2:B21, K2:K21)

Обратите внимание, что XLOOKUPэто новая функция в Excel 2021, и она может не работать в более старых версиях Excel. Для этих версий вы можете достичь того же результата, используяINDEXиMATCH, как в:

=INDEX(K2:K21, MATCH(X3, B2:B21, 0))

или используяVLOOKUP:

=VLOOKUP(X3, B2:K21, COLUMNS(B2:K2), FALSE)

Однако в данном случае поддержка, XLOOKUPвероятно, является наиболее удобным решением, поскольку она также поддерживает несколько дополнительных параметров, которые позволяют указать, как выполняется поиск и что делать, если точное совпадение не найдено.

(Также обратите внимание, что решения INDEX/ MATCHи VLOOKUPпотребуют настройки или могут вообще не работать, если вы хотите, например, выполнить поиск по строке, а не по столбцу, или вернуть значение из столбца, который находится слева от столбца поиска. XLOOKUPследуетпросто работай(во всех случаях, что, по моему мнению, является веской причиной отдать предпочтение именно ему, где это возможно.)

Связанный контент