Используйте FILTER, чтобы вернуть все количества, связанные с меткой графического ресурса, в Excel для Mac. Полученный массив можно развернуть в ячейки листа, использовать в другом вычислении или просуммировать, не теряя повторяющиеся совпадения.
В этом примере используются условные метки и количества графических ресурсов. Нужно выбрать одну метку, получить все соответствующие ей количества и использовать эти отдельные числа в вычислении. В документации Microsoft указано, что FILTER возвращает массив на основе заданных критериев. Функция FILTER.
Какие версии Excel для Mac поддерживают этот способ?
В актуальной документации Microsoft прямо указано, что FILTER поддерживается в Excel для Microsoft 365 для Mac, Excel 2024 для Mac и Excel 2021 для Mac. Используйте одну из этих версий для приведённых ниже формул. Функция FILTER.
Чтобы узнать, какая версия установлена, откройте Excel и выберите Excel → О программе Excel. Microsoft указывает этот путь меню для Mac; в руководстве по определению версии сказано, что в окне «О программе» приложения Mac отображаются номер версии и тип лицензии. Проверьте как выпуск, так и номер сборки. Совместное использование документов с другими версиями Office для Mac; Сведения об Office: какая версия Office у меня установлена?.
Excel 2019 для Mac и Excel 2016 для Mac не входят в список версий с документированной поддержкой FILTER. Если вы используете одну из них, для этого рабочего процесса с динамическими массивами откройте книгу в одной из перечисленных версий для Mac. Приведённые ниже инструкции предполагают выполнение этого условия; это не обходной способ с формулами массива для устаревших версий.
Подготовьте небольшой пример с количествами ресурсов
На пустом листе введите следующие данные в диапазон A1:B7. В первой строке находятся заголовки. Вводите количества как числа, включая ноль в B6.
| Строка листа | Столбец A: ресурс | Столбец B: количество |
|---|---|---|
| 1 | Ресурс | Количество |
| 2 | Плитка A | 4 |
| 3 | Дуга B | 7 |
| 4 | Плитка A | 2 |
| 5 | Точка C | 5 |
| 6 | Плитка A | 0 |
| 7 | Плитка A | 4 |
Введите Выбранный ресурс в D1, а Плитка A — в D2. Введите Подходящие количества в F1. Оставьте пустыми ячейки под F2, чтобы массиву было куда развернуться.
В этом примере для одной метки намеренно указаны два количества, равных четырём, и одно количество, равное нулю. Правильный результат должен включать все четыре подходящие строки: 4, 2, 0 и 4. Удаление повторяющихся значений или исключение нуля изменит результат.
Верните все подходящие количества с помощью FILTER
Выберите F2, введите следующую формулу и нажмите Return:
=FILTER(B2:B7,A2:A7=D2)
В формуле B2:B7 — это массив количеств, а A2:A7=D2 — условие включения. Эти диапазоны охватывают одни и те же шесть исходных строк, как того требует Microsoft: высота или ширина массива условия включения должна быть совместима с диапазоном. Функция FILTER.
Для приведённых данных результат занимает диапазон F2:F5:
| Ячейка | Результат |
|---|---|
| F2 | 4 |
| F3 | 2 |
| F4 | 0 |
| F5 | 4 |
Введите формулу только в F2; Excel автоматически разместит остальные результаты в соседних ячейках. Microsoft называет это развертыванием массива. Чтобы позднее изменить формулу, отредактируйте верхнюю левую ячейку результата — в данном случае F2. Формулы динамических массивов и поведение развернутых массивов.
Два значения четыре соответствуют отдельным подходящим строкам источника. Сохраните оба, если в следующем вычислении нужно учесть каждое вхождение выбранного ресурса.
Почему XLOOKUP не возвращает все четыре строки
В документации Microsoft сказано, что XLOOKUP возвращает элемент, соответствующий первому найденному совпадению. Функция может вернуть несколько столбцов из совпавшей строки, но это не означает, что она перечислит все строки с одинаковой меткой. Функция XLOOKUP.
Например, следующая формула вернёт из примера только 4:
=XLOOKUP(D2,A2:A7,B2:B7)
Этот результат подходит, если ответ определяется одной соответствующей строкой. В этой задаче количества находятся в четырёх отдельных строках, поэтому используйте результат FILTER.
Используйте массив в дальнейших вычислениях
Ссылайтесь на весь результат с помощью F2#. В документации Microsoft знак решётки описан как оператор развернутого диапазона: он обозначает весь массив, полученный из начальной ячейки. Так формула следующего вычисления не будет привязана к текущему результату из четырёх ячеек. Рекомендации и примеры формул массива.
Размещайте формулы следующих вычислений рядом с результатом, а не сразу под ним, чтобы оставить место для дополнительных подходящих количеств. В приведённых ниже примерах используются столбцы H–J.
Сложите все подходящие количества
Введите Общее количество в H1, затем введите эту формулу в H2:
=SUM(F2#)
Пример результата — 10: четыре плюс два плюс ноль плюс четыре. Функция SUM принимает диапазоны и складывает их числовые значения. Функция SUM.
Если вам нужна только сумма, можно вычислить её непосредственно в другой пустой ячейке:
=SUM(FILTER(B2:B7,A2:A7=D2))
Выберите наглядный массив, если нужно просматривать или повторно использовать отдельные количества. Вложенную формулу используйте, если на листе нужна только итоговая сумма.
Умножьте каждое количество на общий коэффициент
Предположим, для примера нужно сделать три копии каждого подходящего количества. Введите Количество копий в I1, а число 3 — в I2. Введите Скорректированные количества в J1, затем введите эту формулу в J2:
=F2#*$I$2
Ожидаемые результаты в J2:J5 — 12, 6, 0 и 12. В документации Microsoft приводятся вычисления с массивами, в которых значения перемножаются и возвращается несколько результатов; в руководстве по калькулятору для Mac звёздочка указана как оператор умножения. Рекомендации и примеры формул массива; Использование Excel в качестве калькулятора в Excel для Mac.
Чтобы получить только скорректированную сумму, введите следующую формулу в H4:
=SUM(F2#*$I$2)
Ожидаемая сумма — 30. Это примеры вычислений на предоставленных условных данных. Арифметика проверена отдельно; формулы книги сверены с документацией Microsoft, но не выполнялись в Excel для Mac.
Выберите, что делать, если совпадений нет
Замените значение в D2 на Волна D — метку, отсутствующую в примере. Исходная формула FILTER с двумя аргументами вернёт #CALC!, поскольку Excel не может вернуть пустой массив. В документации Microsoft указано, что необязательный третий аргумент позволяет задать значение на случай отсутствия результата. Как исправить ошибку #CALC!.
Выберите это значение с учётом требований следующего вычисления. Для данного листа можно использовать следующие варианты:
| Требование | Формула или способ | Последствие |
|---|---|---|
| Отсутствующие совпадения должны оставаться заметными при вычислениях | Оставьте =FILTER(B2:B7,A2:A7=D2) |
При отсутствии совпадений появляется #CALC!, что позволяет проверить причину. |
| Результат в первую очередь нужен для чтения | Используйте =FILTER(B2:B7,A2:A7=D2,"No matches") |
При отсутствии совпадений возвращается текстовое сообщение. |
| Отсутствующая метка должна давать ноль при суммировании | Используйте =SUM(FILTER(B2:B7,A2:A7=D2,0)) |
При отсутствии совпадений сумма равна нулю. |
Значение по умолчанию ноль — это намеренно выбранное правило вычисления. Оно не означает, что в исходных данных есть подходящая строка с нулевым количеством. В этом примере у Плитки A действительно указано нулевое количество, а для Волны D исходных строк нет. Сохраняйте это различие, если важно наличие ресурса.
С текстовым сообщением тоже следует быть внимательным: согласно документации Microsoft, SUM игнорирует текст в ссылочных ячейках. Поэтому суммирование результата, содержащего только текст Совпадений нет, может дать ноль и скрыть отсутствие подходящих строк. Если при отсутствии метки дальнейшие вычисления должны остановиться, используйте подход с отображением ошибки. Функция SUM.
Перед продолжением исходных числовых примеров верните значение D2 к Плитка A.
Добавляйте второе условие только при необходимости
Если в следующей задаче требуется учитывать только положительные количества, задайте и проверку метки, и условие положительного количества:
=FILTER(B2:B7,(A2:A7=D2)*(B2:B7>0))
Для примера формула вернёт 4, 2 и 4. В документации Microsoft указано, что умножение условий в FILTER задаёт логическое И. Этот вариант намеренно исключает подходящее нулевое значение, поэтому оставьте исходную формулу, если нужны все подходящие количества. Функция FILTER.
Устраните неполадки и включите новые строки
Если формула возвращает не те четыре количества, проверьте лист в следующем порядке:
- Проверьте место для результата. Если диапазон заблокирован, появится ошибка
#SPILL!. Перед повторной попыткой освободите предполагаемую область развертывания. - Проверьте расположение формулы. Формулы развернутых массивов нужно размещать в сетке листа за пределами таблицы Excel.
- Проверьте зависимости книги. Microsoft поддерживает связанные динамические массивы между книгами, только пока обе книги открыты; после закрытия исходной книги обновление может привести к ошибке
#REF!.
Эти ограничения описаны в разделе Формулы динамических массивов и поведение развернутых массивов.
Затем проверьте метки и количества в источнике. Сравните D2 с нужными ячейками ресурсов и убедитесь, что количества введены как числа. Одной лишь правдоподобной суммы недостаточно для проверки, поскольку SUM может игнорировать текстовые значения. Функция SUM.
Формулы примера ссылаются на исходные строки с 2-й по 7-ю. Если вы добавите данные в строку 8, измените оба исходных диапазона, чтобы включить её. Например, после ввода Плитка A в A8 и числового количества 3 в B8 замените формулу в F2 на следующую:
=FILTER(B2:B8,A2:A8=D2)
Ожидаемый массив будет содержать значения 4, 2, 0, 4, 3, а сумма станет равна 13. Ссылка F2# учитывает расширенный результат, но сама по себе не включает строку, пропущенную в исходных диапазонах.
Для часто растущего списка Microsoft рекомендует использовать таблицу Excel со структурированными ссылками, которые корректируются при добавлении или удалении строк. Размещайте развернутый результат за пределами этой таблицы. Формулы динамических массивов и поведение развернутых массивов.
Перед тем как полагаться на сумму, проверьте отдельные результаты: для исходных шести строк при выборе Плитка A должны вернуться четыре количества, включая оба значения четыре и ноль. Убедившись в этом, используйте F2# в дальнейших вычислениях и выберите подходящее для назначения листа правило на случай отсутствия совпадений.
Источники
- Функция FILTER — Microsoft Support.
- Совместное использование документов с другими версиями Office для Mac — Microsoft Support.
- Сведения об Office: какая версия Office у меня установлена? — Microsoft Support.
- Формулы динамических массивов и поведение развернутых массивов — Microsoft Support.
- Функция XLOOKUP — Microsoft Support.
- Рекомендации и примеры формул массива — Microsoft Support.
- Функция SUM — Microsoft Support.
- Использование Excel в качестве калькулятора в Excel для Mac — Microsoft Support.
- Как исправить ошибку #CALC! — Microsoft Support.