В Microsoft 365 для Mac или Excel 2024 для Mac объедините CHOOSECOLS, TOCOL, HSTACK и FILTER, чтобы преобразовать повторяющиеся названия графических ресурсов и количества в строки инвентарного списка. Пример формулы ниже сохраняет соответствие пар, нулевые количества и неполные записи.

Это руководство предназначено для пользователя настольной версии Mac, на листе которого повторяются столбец атрибутов и следующий за ним столбец количества. Здесь каждый атрибут — это название графического ресурса, например «Круглая плитка». Задача — создать инвентарный список из двух столбцов, а затем искать количества, не отделяя их от названий.

Какие версии Excel для Mac поддерживают эту формулу?

Microsoft прямо указывает Excel для Microsoft 365 для Mac и Excel 2024 для Mac для всех трёх функций преобразования: CHOOSECOLS, TOCOL и HSTACK. Используйте одну из этих версий для основной формулы.

В Excel 2021 для Mac есть динамические массивы, FILTER, LET и XLOOKUP, но это не подтверждает поддержку более новых функций преобразования. В документации по функциям Excel 2021 для Mac Microsoft описывает доступные возможности работы с массивами и поиска; три страницы функций выше не содержат упоминания Excel 2021.

Проверьте выпуск и версию в диалоговом окне приложения О программе. В руководстве Microsoft по версиям Office объясняется, что в окне на Mac указаны и версия, и тип лицензии. Если в вашем выпуске нет функций преобразования, используйте Microsoft 365 для Mac или Excel 2024 для Mac для автоматического способа либо воспользуйтесь описанным ниже способом ручного объединения строк.

Подготовьте полный искусственный пример

На пустом листе введите эти данные в диапазон A1:F4. Две пустые ячейки в середине третьей строки должны действительно оставаться пустыми. Все названия и количества приведены для примера.

Атрибут 1 Количество 1 Атрибут 2 Количество 2 Атрибут 3 Количество 3
Круглая плитка 4 Текстура линий 2 Точечный узор 0
Дуговой мотив 3 Текстура сетки 5
Круглая плитка 7 Волнистый узор 6 Квадратная плитка 1

Каждая соседняя пара представляет одну запись инвентарного списка. В итоговом списке данные идут слева направо по каждой исходной строке, а затем переходят к следующей строке. Название «Круглая плитка» встречается дважды, поскольку это отдельные записи; объединение повторяющихся названий — дополнительная задача.

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

Преобразуйте повторяющиеся пары в строки инвентарного списка

Введите Ресурс в H1, а Количество — в I1. Оставьте область вывода под этими заголовками пустой. Вставьте эту полную формулу в H2, затем нажмите Return. Формулы динамических массивов вводятся один раз и распространяются на соседние ячейки, как описано в руководстве Microsoft по формулам массива.

=LET(
    source,A2:F4,
    clean,IF(source="","",source),
    labels,TOCOL(CHOOSECOLS(clean,1,3,5),0,FALSE),
    quantities,TOCOL(CHOOSECOLS(clean,2,4,6),0,FALSE),
    keep,((labels<>"")+(quantities<>""))>0,
    FILTER(HSTACK(labels,quantities),keep,"No inventory entries")
)

Ожидаемый результат занимает диапазон H2:I9:

Ресурс Количество
Круглая плитка 4
Текстура линий 2
Точечный узор 0
Дуговой мотив 3
Текстура сетки 5
Круглая плитка 7
Волнистый узор 6
Квадратная плитка 1

Порядок пар в примере, восемь выходных записей, сохранение нуля и общее количество 28 были проверены с помощью небольшого детерминированного вычисления на Python. Формула Excel сверена с документированным Microsoft синтаксисом функций; в Excel она не запускалась.

Как формула сохраняет соответствие пар

LET задаёт имена промежуточным результатам, чтобы формула могла использовать их повторно. Имена source, clean, labels, quantities и keep существуют только внутри этой формулы; создавать именованные диапазоны не нужно. Microsoft описывает это поведение в справке по функции LET.

CHOOSECOLS выбирает столбцы с названиями 1, 3 и 5, а также столбцы с количествами 2, 4 и 6. Эти числа обозначают позиции в выбранном исходном диапазоне. В документации Microsoft по функции CHOOSECOLS подтверждается, что она возвращает указанные столбцы из массива.

В обоих вызовах TOCOL используется 0 для сохранения каждой позиции и FALSE для обхода по строкам. Поскольку они проходят по соответствующим позициям в одном порядке, каждое название остаётся связанным со своим количеством. Microsoft описывает оба аргумента в справке по функции TOCOL.

HSTACK размещает два полученных столбца рядом. До фильтрации в каждом по девять позиций, поэтому их длина совпадает. Microsoft поясняет, что HSTACK присоединяет массивы по горизонтали и заполняет более короткие массивы ошибками, если число строк в них различается.

Наконец, keep выбирает позиции, где есть название, количество или оба значения. FILTER удаляет только полностью пустые пары. Последний аргумент задаёт сообщение, если записей не останется, в соответствии с документированным Microsoft синтаксисом функции FILTER.

Сохраняйте пустые значения и нули, не скрывая неполные записи

Пустое количество и числовой ноль несут разную информацию. Ноль — это введённое значение; пустую ячейку ещё нужно интерпретировать. В примерах Microsoft для TOCOL показано, что необработанные пустые ячейки могут возвращать ноль. Поэтому на этапе clean функция IF возвращает пустой текст для пустых исходных ячеек перед преобразованием. Microsoft описывает условные возвращаемые значения в справке по функции IF.

Формула намеренно оставляет неполные пары видимыми. Перед использованием результата выполните эти проверки:

Исходная пара Поведение вывода Действие
Название и числовое количество Сохраняет оба значения Проверьте, что количество указано намеренно
Название и ноль Сохраняет ноль Оставьте, если ноль — намеренное значение
Обе ячейки пусты Удаляет пару Исправления не требуются
Название без количества Сохраняет название и пустое количество Укажите количество
Пустое название с количеством Сохраняет пустое название и количество Укажите название

Не удаляйте пустые значения независимо из массивов названий и количеств. Если в одной позиции отсутствует название, а в другой — количество, отдельное удаление пустых ячеек может сдвинуть массивы и привести к неверным парам. Ключевой принцип этой формулы — сохранять позиции до объединения.

Ячейка, которая выглядит пустой, но содержит пробелы, этой формулой пустой не считается. Исправьте такие записи в исходных данных. Аналогично, формула сохраняет количества, введённые как текст, и не преобразует их автоматически. Проверьте такие значения перед вычислением итогов.

Найдите одну запись или верните все совпадения

Введите Круглая плитка в K2. Чтобы получить первое количество, введите эту формулу в L2:

=XLOOKUP(K2,CHOOSECOLS(H2#,1),CHOOSECOLS(H2#,2),"No matching asset",0)

H2# ссылается на весь разлившийся результат, начинающийся в H2, поэтому поиск учитывает изменения его размера. Microsoft объясняет эту нотацию в документации по оператору диапазона разлива.

Результат — 4. Явно заданный режим совпадения 0 требует точного совпадения, а поиск по умолчанию возвращает первое совпадение. В справке Microsoft по функции XLOOKUP описаны оба поведения. Там также подтверждается, что XLOOKUP недоступна в Excel 2016 и Excel 2019.

Если повторяющиеся названия имеют значение, поиска первого совпадения недостаточно. Чтобы вернуть все записи «Круглая плитка», поместите эту полную альтернативную формулу в N2, оставив свободное место в столбцах N:O:

=FILTER(H2#,CHOOSECOLS(H2#,1)=K2,"No matching asset")

В результате появятся две строки: «Круглая плитка» с количеством 4 и «Круглая плитка» с количеством 7. FILTER возвращает строки, удовлетворяющие заданному условию, как описано в справке Microsoft по функции FILTER. Используйте формулу поиска первого совпадения, если вам достаточно одной записи; используйте результат фильтрации, если нужно просмотреть все вхождения.

Проверьте результат и настройте исходный диапазон

В примере проверьте, что получилось восемь выходных строк, а у «Точечного узора» по-прежнему указано нулевое количество. Затем сравните сумму исходных количеств с суммой преобразованных. Введите эти формулы в две свободные ячейки:

=SUM(B2:B4,D2:D4,F2:F4)

=SUM(CHOOSECOLS(H2#,2))

Обе должны вернуть 28. В документации Microsoft по функции SUM подтверждается возможность складывать несколько диапазонов и поясняется, что текстовые значения игнорируются. Поэтому совпадение итогов не доказывает, что каждое количество является числом, и не выявляет переставленные между названиями количества. Также проверьте соответствие пар «название — количество».

Чтобы добавить больше исходных строк, замените в основной формуле A2:F4 фактическим диапазоном, например A2:F100. Строки за пределами этой ссылки не включаются. Для четырёх повторяющихся пар укажите в качестве источника восемь столбцов, например A2:H100, и обновите позиции столбцов с названиями на 1,3,5,7, а с количествами — на 2,4,6,8.

При расширении до восьми исходных столбцов переместите результат в свободную область, например J2, и обновите ссылки на разлившиеся результаты ниже по листу, указав J2#. Держите источник и результат отдельно. Недопустимая позиция столбца приводит к ошибке, как описано в разделе об ошибках функции CHOOSECOLS.

Устраните блокировку вывода или используйте ручное объединение строк

Если основная формула возвращает #SPILL!, выделите ячейку с формулой и проверьте предполагаемую область вывода. Переместите из неё существующие данные и проверьте, нет ли объединённых ячеек. Разместите формулу вне таблицы Excel: Microsoft указывает в руководстве по устранению ошибки #SPILL!, что формулы разливающихся массивов не могут распространяться внутри таблиц или в объединённые ячейки.

Если в источнике есть ошибка, исправьте соответствующую запись перед использованием инвентарного списка. Формула не скрывает ошибки; Microsoft отмечает, что ошибка в массиве включения функции FILTER приводит к ошибке всей функции. Подавление ошибок во всём преобразовании может скрыть запись, требующую исправления.

Для небольшого листа в Excel 2021 для Mac можно вручную получить такую же структуру из двух столбцов: скопируйте A2:B4 в H2:I4, C2:D4 — в H5:I7, а E2:F4 — в H8:I10. Удаляйте только строки, в которых пусты обе ячейки. При таком способе записи располагаются блоками пар, а не в порядке исходных строк, как в основной формуле; при изменениях источника работу придётся повторить.

После такой ручной подготовки в Excel 2021 для Mac можно использовать документированную возможность XLOOKUP с фиксированными диапазонами:

=XLOOKUP(K2,H2:H10,I2:I10,"No matching asset",0)

Хорошо подготовленный инвентарный список сохраняет все значимые пары, показывает неполные записи и использует поиск, подходящий для повторяющихся названий. Сначала проверьте эти свойства на примере, затем расширьте исходный диапазон в соответствии с листом.

Источники