В Excel точно почему EOMONTH() (и другие) не работают в формулах массива?

Question

Может ли выражение Excel быть преобразовано в форму массива, иногда трудно предсказать. Например, на следующем снимке экрана показана таблица со строкой дат в верхней части, за которой следуют четыре строки, которые ссылаются на первую. Первые три (т.е. строки Excel 3, 4 и 5) содержат формулу массива. Первые два хороши, но третий, используя EOMONTH (), ломается. Затем в строке 6 Excel отображается та же формула EOMONTH (), но не в виде массива. Работает нормально. В B3:B5 и C7:I7 я использовал FORMULATEXT () для отображения функций в различных строках и ячейках.

Я не вижу ничего очевидного в документации для EOMONTH(), которая могла бы предсказать сбой.

Есть ли способ выяснить, какие функции имеют такие ограничения? (Это якобы общая форма моего вопроса, но это не очень помогает).

Answer 1

Microsoft просто решила не расширять все формулы для работы с массивами.

Кто-то может быть трудным, кто-то может быть нелогичным, или, может быть, у них закончилось время, кто знает. В результате есть список функций, которые могут обрабатывать массивы, а остальные - нет. У меня сейчас нет ссылки (я пойду посмотрю), но это факт, что есть определенный список.

Для многих функций вы можете использовать обходные пути Google, например, вместо EOMONTH(range,0) вы можете использовать DATE(YEAR(range),MONTH(range)+1,0)} (нулевой день следующего месяца просто за день до первого дня следующего месяца ...) вместо OR(cond1,cond2,...) вы можете использовать IF(cond1+cond2+...>0,.. , и для AND(cond1,cond2,...) вы можете заменить IF(cond1*cond2*...,... , потому что Excel рассматривает TRUE как 1 и FALSE как 0.

Некоторые формулы (те, которые были в пакете инструментов анализа в Excel 2003) также можно настроить для работы с массивами, но не с диапазонами - небольшая разница! Добавьте - - (минус минус, но без пробела) перед диапазоном, чтобы сделать его массивом: EOMONTH(- -range,0)

Answer 2

Не работает Но вы можете сделать:

{=DATE(YEAR(C$2:i$2),MONTH(C$2:I$2)+1,0)}

Answer 3

А также обходной путь, отмеченный другими здесь

{=DATE(YEAR(C$2:i$2),MONTH(C$2:I$2)+1,0)}

Вы можете создать массив на основе UDF. Другими словами, подделка!

Для работы UDF в книге Excel должна быть включена поддержка макросов (что может быть или не быть проблемой для вас). Некоторые ресурсы по UDF на основе массива:

• myonlinetraininghub (я нашел язык в этом легко понять).
• Здесь на SE
• Другой SE - это обсуждает входные данные как диапазон или массив. Пример в ответе использует два UDF, но при правильном использовании TypeName вы могли бы фактически объединить их в один UDF, который знает, был ли передан диапазон или массив.