Используйте поиск с точным совпадением, чтобы добавить метаданные ресурсов в каждую строку инвентаризации, включая строки с повторяющимися идентификаторами ресурсов. В Excel для Mac можно сформировать один разливающийся массив с помощью HSTACK и XLOOKUP или использовать отдельные столбцы поиска в Excel 2021.

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

Какая версия Excel для Mac поддерживает этот подход?

Проверьте установленную версию: выберите Excel → About Microsoft Excel. В инструкциях Microsoft для Mac указано, что в диалоговом окне «О программе» отображаются номер версии приложения и тип лицензии. Проверьте оба значения, чтобы выбрать подходящую формулу. Сведения об Office: какую версию Office я использую?

Выпуск для Mac Подход в этой статье
Excel для Microsoft 365 для Mac Один объединенный результат с помощью HSTACK и XLOOKUP
Excel 2024 для Mac Один объединенный результат с помощью HSTACK и XLOOKUP
Excel 2021 для Mac Три отдельных разливающихся столбца XLOOKUP
Excel 2019 или 2016 Для этих формул требуется более новая версия

Microsoft указывает Microsoft 365 для Mac и Excel 2024 для Mac в документации по HSTACK. В документации по функциям Excel 2021 для Mac подтверждается наличие XLOOKUP и динамических массивов. В документации по XLOOKUP прямо указано, что Excel 2016 и 2019 не поддерживаются.

Если у вас Excel 2021, используйте приведенный ниже метод с отдельными столбцами. Он позволяет получить тот же результат обогащения без необходимости в HSTACK.

Подготовьте инвентаризацию и метаданные с общим идентификатором ресурса

Создайте в одной книге два листа с именами Inventory и Metadata. Введите следующие данные инвентаризации в диапазон Inventory!A1:B6. Для этого примера оставьте эти ячейки обычным диапазоном листа.

AssetID Quantity
ARC-01 3
TILE-02 5
ARC-01 2
GRID-03 4
TILE-02 1

Введите метаданные в диапазон Metadata!A1:D4:

AssetID Title Format Palette
ARC-01 Arc Study Vector Teal
TILE-02 Tile Study Raster Amber
GRID-03 Grid Study Vector Slate

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

Используйте идентификатор ресурса, который одинаково обозначает ресурс на обоих листах. В этом примере заполнены все идентификаторы и поля метаданных. В своей книге явно укажите, как представлять неизвестные данные, например текстом Unknown, чтобы их можно было отличить от отсутствующего совпадения ресурса.

Проверьте, что для каждого идентификатора инвентаризации найдено ровно одно совпадение в метаданных

На листе Inventory введите MetadataMatches в ячейку C1. Введите эту формулу в ячейку C2, затем скопируйте ее до ячейки C6:

=COUNTIF(Metadata!$A$2:$A$4,A2)

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

  • 0: идентификатор ресурса отсутствует на листе метаданных.
  • 1: для ресурса есть единственная строка метаданных, которую предполагает этот процесс.
  • Любое число больше 1: в метаданных есть повторяющиеся идентификаторы, которые нужно устранить.

Функция COUNTIF подсчитывает ячейки, соответствующие условию. Для приведенных данных каждая проверка возвращает 1. Используйте простые идентификаторы, как в примере: COUNTIF считает звездочки и вопросительные знаки подстановочными знаками, поэтому идентификаторы с такими символами требуют дополнительной обработки. Использование функции СЧЁТЕСЛИ в Microsoft Excel

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

Создайте единый обогащенный массив в Microsoft 365 или Excel 2024

Создайте лист с именем Enriched. Введите эти пять заголовков в ячейки A1:E1: AssetID, Quantity, Title, Format, Palette. Оставьте диапазон A2:E6 пустым.

Выберите ячейку Enriched!A2, введите формулу целиком и нажмите Return:

=HSTACK(
  Inventory!A2:B6,
  XLOOKUP(Inventory!A2:A6,Metadata!A2:A4,Metadata!B2:B4,"MISSING METADATA",0),
  XLOOKUP(Inventory!A2:A6,Metadata!A2:A4,Metadata!C2:C4,"MISSING METADATA",0),
  XLOOKUP(Inventory!A2:A6,Metadata!A2:A4,Metadata!D2:D4,"MISSING METADATA",0)
)

Эта формула объединяет два исходных столбца инвентаризации с тремя столбцами результатов поиска. В каждом поиске используются те же пять идентификаторов инвентаризации, поэтому добавленные поля соответствуют исходным записям. Функция HSTACK объединяет массивы по горизонтали в указанном порядке. Функция HSTACK

Последний аргумент 0 в каждом поиске задает точное совпадение. Для идентификатора, которому не найдено соответствие, возвращается текст MISSING METADATA. Это видимое значение помогает обнаружить неполные совпадения, а не оставляет необъяснимую пустую ячейку. Функция XLOOKUP

Ожидаемые выходные данные занимают диапазон Enriched!A2:E6:

AssetID Quantity Title Format Palette
ARC-01 3 Arc Study Vector Teal
TILE-02 5 Tile Study Raster Amber
ARC-01 2 Arc Study Vector Teal
GRID-03 4 Grid Study Vector Slate
TILE-02 1 Tile Study Raster Amber

Обратите внимание: обе записи ARC-01 сохраняются, с количествами 3 и 2. Для обеих добавляются значения Arc Study, Vector и Teal. Метаданные обогатили эти записи, не объединив их.

Формула намеренно ищет по одному полю метаданных за раз, а затем объединяет результаты. Так явно задается структура выходных данных: пять строк инвентаризации, два исходных столбца и три добавленных столбца.

Используйте три столбца поиска в Excel 2021 для Mac

В Excel 2021 работайте непосредственно рядом с исходной инвентаризацией. Оставьте проверку количества совпадений в столбце C, затем введите Title, Format и Palette в ячейки D1:F1. Оставьте диапазон D2:F6 пустым.

Введите эту формулу в ячейку Inventory!D2:

=XLOOKUP(A2:A6,Metadata!A2:A4,Metadata!B2:B4,"MISSING METADATA",0)

Введите эту формулу в ячейку Inventory!E2:

=XLOOKUP(A2:A6,Metadata!A2:A4,Metadata!C2:C4,"MISSING METADATA",0)

Введите эту формулу в ячейку Inventory!F2:

=XLOOKUP(A2:A6,Metadata!A2:A4,Metadata!D2:D4,"MISSING METADATA",0)

После ввода каждой формулы нажимайте Return. Каждый результат заполнит пять строк под начальной ячейкой. Идентификаторы ресурсов и количества останутся в столбцах A:B, диагностическая проверка — в C, а добавленные метаданные появятся в столбцах D:F. Excel 2021 для Mac поддерживает динамические массивы, необходимые для этого подхода. Новое в Excel 2021 для Mac

Адаптируйте книгу для большего числа строк инвентаризации

В примерах используются фиксированные диапазоны, чтобы все ссылки были видны. По мере роста инвентаризации расширяйте все ссылки на данные инвентаризации до одной и той же последней строки. Например, если инвентаризация заканчивается строкой 501, в объединенной формуле нужны диапазон Inventory!A2:B501 и три ссылки на диапазон Inventory!A2:A501. Расширьте проверку количества совпадений до той же строки.

По мере роста объема метаданных одновременно расширяйте все ссылки на них. Если последней станет строка 101, используйте Metadata!A2:A101 для ключей и соответствующие диапазоны B2:B101, C2:C101 и D2:D101 для возвращаемых значений. Включайте реальные записи, а не добавляйте большой блок неиспользуемых строк.

Если книга регулярно пополняется, рассмотрите возможность преобразования исходных диапазонов в таблицы Excel и использования структурированных ссылок. В документации Microsoft сказано, что такие ссылки обновляются при добавлении и удалении строк источника. Разливающийся результат размещайте в сетке листа за пределами любой таблицы. Формулы динамических массивов и поведение разливающихся массивов

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

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

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

Если в строке отображается MISSING METADATA, проверьте для нее количество совпадений. Нулевое значение означает, что нужно добавить недостающую запись метаданных или исправить идентификатор ресурса. Если визуально одинаковые обозначения дают неожиданные результаты, проверьте, нет ли случайных пробелов в начале или конце: Microsoft указывает, что они могут быть причиной неожиданных результатов COUNTIF. Использование функции СЧЁТЕСЛИ в Microsoft Excel

Для этой настройки оставьте оба исходных листа в одной книге. Если позднее переместите источник в другую книгу, учтите документированное Microsoft ограничение: связанные динамические массивы требуют, чтобы обе книги были открыты, а обновление при закрытой исходной книге может привести к ошибке #REF!. Формулы динамических массивов и поведение разливающихся массивов

Сверьте синтетический пример по трем конкретным проверкам:

  1. В исходном порядке инвентаризации выведено пять строк данных.
  2. Количества сохранены: 3, 5, 2, 4, 1; их сумма — 15.
  3. Повторяющиеся идентификаторы получили одинаковые метаданные, а каждая проверка количества совпадений возвращает 1.

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

Источники