Ошибка «Пустые массивы не поддерживаются»

Ошибка «Пустые массивы не поддерживаются»

У меня есть рабочая тетрадь с листами, по одному на каждый год, и мне нужно получить среднее значение всех месяцев, которые прошли в году этого листа. Листы названы по годам, и я назвал формулу: MONTHS.

=LET(year,VALUE(MID(CELL("filename"),SEARCH("]",CELL("filename"))+1,31)),IF(YEAR(TODAY())=year,MONTH(TODAY())-1,IF(year>YEAR(TODAY()),0,12)))

И используйте это, чтобы получить среднее значение:

В большинстве случаев это работает, но когда я делаю что-то в других ячейках или листах, возвращается#CALCошибка, что пустые массивы не поддерживаются. Когда я пересчитываю лист, это исправляется само собой, но очень раздражает делать это постоянно. Есть ли способ предотвратить это?

решение1

Мое личное мнение, основанное на годах использования APL, заключается в том, что поддержка пустых массивов необходима. Отсутствие поддержки пустых массивов — это ошибка со стороны MicroSoft.

решение2

У меня нет TAKE()доступных данных, поэтому я не могу определенно сказать: «Это ваша проблема». Я рассмотрю два аспекта: ваш MONTHSименованный диапазон, вероятно, ограничен КНИГОЙ, но его необходимо ограничить ЛИСТОМ, чтобы он мог указывать конкретную ячейку на каждом отдельном листе, и, возможно, TAKE()проблема связана с двумя потенциальными проблемами: указанием ,,параметра строк и первым делом, рассмотренным ниже, MONTHSиногда возвращающим «0», что означает TAKE()отсутствие столбцов, то есть пустой массив.

Тот факт, что вы упоминаете эту проблему в ситуации работы с другими листами, а затем возвращаетесь, склоняется к первой категории, области действия MONTHSи, таким образом, CELL()не указываете какую-либо конкретную ячейку. Но тот факт, что указание 0столбцов, по-видимому, может сгенерировать пустой массив, и вам говорят, что пустые массивы не поддерживаются, предполагает, что это и есть проблема. Однако в этом случае это не должно решаться так просто. «Не должно», конечно, не эквивалентно «не будет». Так что серая область.

Но ниже приведены три важные вещи, которые можно легко проверить, и чтобы сделать это проще, я протестировал материал области действия Named Range, и это так. (Я также протестировал стандартный способ заставить Excel никогда не возвращать описанную ошибку, и это не решает проблемы.) TAKE()Однако, как это может повлиять, я не могу сказать по результатам тестирования. Вы увидите, как легко проверить эти две вещи. Я делаю ставку на область действия, MONTHSнаилучшим образом соответствующую описанным проблемам. Поэтому это первое:

Ваша формула имеет три возможных результата, один из которых — 0.

Пока все хорошо. Однако в вашей TAKE()функции вы используете ее результаты, чтобы указать, сколько столбцов брать для отправки в AVERAGE()функцию.

Но при формировании массива, когда MONTHSформула возвращает 0, вы указываете TAKE()брать 0столбцы в той строке, в которой они находятся. 0columns означает пустой массив.

Чтобы это сработало для вас, вам нужен более сложный подход. Например:

Используйте IF()для проверки MONTHSрезультата . Если 0, то верните результат $0.00 в ячейку O5. Если это не результат, 0то используйте AVERAGE()формулу как есть.

Или

Возвращает a 1вместо a, 0когда yearвычисляемое значение находится в будущем. Можно было бы подумать, что все строки на этом листе будут пустыми, поэтому вы бы взяли среднее значение одной ячейки с нулевым значением и вернули ноль. Если нет, то можно было бы заметить похожую логику, которая бы применялась.

Другая мысль, не связанная напрямую и не вызывающая никаких проблем в данный момент, заключается в том, что при написании, CELL("filename")как вы это делаете, Excel каждый раз пересчитывает все случаи его использования на ВСЕХ листах, а не только на этом. И, исходя из работы на другом листе, возникает проблема, описанная частью того, что вы указали в вопросе: когда на другом листе Excel пересчитывает один из других листов, он использует лист, на котором вы находитесь, для возврата, CELL()а не тот лист, на котором он используется, за исключением того, на котором вы находитесь. Таким образом, все остальные получают неудачные результаты, и Excel, похоже, понимает это и возвращает ошибку на всех из них. Как я упоминаю ниже, это только раздражает вас или смущает, если начальник жалуется на это, если листы являются тупиками, их результаты не предшествуют ячейкам для других листов, например, листы суммирования по годам. Но если это так, они вносят ошибку в эти прямые использования, заставляя их возвращать ошибки...

Итак, об этом: Это вызывает проблему, не при редактировании текущего листа, а при переходе для внесения любых изменений на другой лист. Когда вы это делаете, другие рабочие листы выдают ошибку #VALUE!для своих ячеек, которые используют MONTHS. Возможно, это не такая уж и проблема, пока каждый лист является самодостаточным и не вкладывается в какие-либо другие листы. Вы бы перешли на лист, чтобы выполнить какую-то работу, и были бы раздражены, увидев эффект ошибки (конечно, ситуация «пустого массива», хотя у меня ее нет, TAKE()и поэтому я не могу проверить точный эффект... может быть, он вернет массив ошибок для усреднения и поэтому AVERAGE()не выдаст #CALC!ошибку, а скорее свою собственную #VALUE!ошибку. В любом случае, это просто раздражает вас и беспокоит начальство, но при первой же записи на лист это решится.

Эту проблему можно решить, создав MONTHSобласть действия SHEET вместо WORKBOOK. И указав ячейку (любую ячейку, но почему не ячейку A1?) для каждой из двух CELL()функций в ней. Как только вы это сделаете, эта проблема исчезнет.

Конечно, это означает создание именованного диапазона для каждого существующего листа, но если у вас есть шаблон или вы добавляете новые годы путем копирования текущего листа и удаления данных, именованный диапазон с областью действия ЛИСТ будет создаваться каждый раз при копировании и переименовании. Так что в будущем это не проблема. И удалите MONTHSименованный диапазон с областью действия WORKBOOK после этого.

Если TAKE()указание параметра ,,so no rowsи иногда указание 0параметра for columns не является источником затруднений (я не думаю, что это так, но, возможно, MS изменила весь свой философский подход: похоже, они INDEX()больше не делают то, что делали with ), то, скорее всего, CELLS()проблема в использовании.

Что касается этого, то все, что я могу сейчас найти об этой функции, это просто их рекламная пакость, выплюнутая или красиво выплюнутая через приятные примеры, текст и видео, но НИКТО не изучает реальные проблемы использования, я говорю не потому, что протестировал ее для вашего использования. Я отмечаю, что подход MS за последние несколько лет заключался в том, чтобы не разрешать использовать старое ,,) INDEX(), но, скорее, когда они говорят, что параметр rows является обязательным, они даже не имеют в виду свое старое, «должны быть указаны либо строки, либо столбцы», а скорее то, что независимо от того, предоставите ли вы параметр columns, вы ДОЛЖНЫ предоставить параметр rows. Кроме того, в надежде, что они интерпретируют ваше ,,MONTHS)как означающее «ЭТА строка, столько столбцов», можно надеяться, что они делают неявное пересечение, что на самом деле может быть не так, поскольку они КОНКРЕТНО уходят от этого, на практике и как концепция.

Итак, проблемы выглядят так:

  1. Необходимо использовать MONTHSименованные диапазоны с областью действия SHEET вместо (предположительно) версии с областью действия WORKBOOK.

  2. TAKE()проблема в том, что иногда не обрабатывается «0», возвращаемый MONTHS.

  3. TAKE()проблема заключается в том, что не обрабатывается отсутствие параметра rows.

  4. TAKE()будучи проблемой в ОБА 2 и 3 выше, являясь проблемами.

Редактировать:После всех этих исследований и не найдя ничего полезного, я опубликовал это, а затем нажал на еще одну страницу поиска... и нашел веб-сайт, на котором есть пример использования части, о которой ,,MONTHS)я часто упоминал выше, и который показывает TAKE()успех, поскольку он использует подход, при котором игнорируется все, что касается строк, и, таким образом, возвращаются все строки, как при простом использовании параметров в INDEX().

Так что ЭТОТ аспект не является проблемой. Извините, я слишком сильно отредактировал базовый ответ, чтобы удалить ту часть, где он находится, не переписав его полностью, и теперь уже слишком поздно, так как моя кровать зовет. Но это использование НЕ является проблемой, поскольку его пример показывает правильный возврат для этого использования. Он находится по адресу:

https://www.get-digital-help.com/how-to-use-the-take-function/

Часть 4. в макете, примерно 20–25 % от длины страницы.

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