Используйте INDEX с двумя функциями MATCH для точного совпадения, чтобы получить количество на пересечении строки ресурса и столбца формата. В этом руководстве создаётся небольшая таблица графических ресурсов, объясняется формула и приводится альтернативный вариант с вложенной XLOOKUP для поддерживаемых версий Mac.

Какой способ поиска работает в Excel для Mac?

Для этой задачи у вас есть название ресурса, например Dot Grid, и формат, например PNG. Нужно получить одно значение количества на их пересечении. Это обычно называется двумерным поиском.

Microsoft указывает, что INDEX и MATCH доступны в Excel для Microsoft 365 для Mac, Excel 2024 для Mac и Excel 2021 для Mac. Оба метода в этой статье работают в рамках указанного списка версий Mac. См. документацию Microsoft по функции INDEX и функции MATCH.

XLOOKUP также документирована для этих выпусков Mac. Microsoft прямо указывает, что она недоступна в Excel 2016 и Excel 2019, несмотря на то что эти выпуски включены в более широкий список применимости на странице. Если в вашем выпуске нет XLOOKUP, используйте метод с INDEX и MATCH. Microsoft: функция XLOOKUP

Начните с INDEX и MATCH, если хотите по отдельности проверить позиции строки и столбца, пока изучаете формулу. Выберите вложенную XLOOKUP, если уже пользуетесь этой функцией и предпочитаете используемое по умолчанию точное совпадение.

Создайте небольшую таблицу количества графических ресурсов

На пустом листе введите следующие синтетические данные в диапазон A1:D5. Самый левый столбец таблицы ниже соответствует столбцу листа A; не добавляйте отдельный столбец с номерами строк. Эти количества приведены для примера.

Ресурс SVG PNG JPG
Tile Arc 12 18 6
Dot Grid 8 24 10
Loop Ribbon 15 9 0
Soft Square 7 14 5

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

Создайте отдельную область поиска на том же листе:

Ячейка Введите
F1 Ресурс для поиска
F2 Dot Grid
G1 Формат для поиска
G2 PNG
H1 Количество

Три исходных диапазона выполняют разные задачи: A2:A5 содержит названия ресурсов, B1:D1 — заголовки форматов, а B2:D5 содержит только количества. Если оставить заголовки за пределами диапазона количеств, относительные позиции будут выровнены правильно.

Получите количество с помощью INDEX и двух функций MATCH

Выберите H2, введите эту формулу целиком и нажмите Return:

=INDEX($B$2:$D$5,MATCH(F2,$A$2:$A$5,0),MATCH(G2,$B$1:$D$1,0))

Результат должен быть равен 24: количеству для Dot Grid в столбце PNG. В инструкциях Microsoft для Mac описано, как выбрать ячейку результата, ввести формулу, начинающуюся с =, и нажать Return. Microsoft: ввод формулы

Разберитесь в двух позициях

Первая MATCH находит позицию ресурса в диапазоне A2:A5; вторая находит позицию формата в диапазоне B1:D1. Последняя 0 требует точного совпадения. Без этого аргумента MATCH по умолчанию выполняет приблизительный поиск, для которого требуется упорядочивание данных. Microsoft: функция MATCH

Для указанных входных данных два внутренних вычисления выглядят так:

=MATCH(F2,$A$2:$A$5,0)

Результат: 2.

=MATCH(G2,$B$1:$D$1,0)

Результат: 2.

Таким образом, полная формула выбирает строку 2 и столбец 2 в блоке количеств:

=INDEX($B$2:$D$5,2,2)

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

Зафиксируйте исходные диапазоны при копировании

Знаки доллара фиксируют исходные диапазоны. Ссылки на входные данные F2 и G2 остаются относительными, поэтому при копировании H2 вниз в H3 следующая формула будет читать F3 и G3, используя ту же исходную таблицу. На Mac также можно выбрать ссылку в строке формул и нажимать Command+T, чтобы переключать типы ссылок. Microsoft: переключение между относительными и абсолютными ссылками

Например, введите Tile Arc в F3 и JPG в G3, затем скопируйте H2 в H3. Новый результат должен быть равен 6. Так вы получите небольшой список запросов, не меняя исходную таблицу ресурсов.

Используйте вложенную XLOOKUP в поддерживаемых версиях Mac

На том же листе с теми же входными данными для поиска вместо этого можно ввести такую формулу в H2:

=XLOOKUP(F2,$A$2:$A$5,XLOOKUP(G2,$B$1:$D$1,$B$2:$D$5))

Для Dot Grid и PNG она возвращает 24. В документации Microsoft приведён пример вложенной XLOOKUP, которая выполняет вертикальный и горизонтальный поиск, чтобы вернуть значение на пересечении. Точное совпадение используется по умолчанию. Microsoft: функция XLOOKUP

В этом примере внутренняя функция поиска выбирает столбец количеств PNG: 18, 24, 9 и 14. Внешняя функция поиска выбирает запись, соответствующую Dot Grid. Благодаря использованию тех же трёх исходных диапазонов этот метод легко сравнить с предыдущей формулой.

Проверьте результат перед использованием большой таблицы

Используйте следующие случаи для небольшой приёмочной проверки. Меняйте F2 и G2 для каждой строки, а затем сравнивайте H2 с ожидаемым результатом. Если хотите сохранить исходный пример, после проверки верните Dot Grid и PNG.

Ресурс Формат Ожидаемый результат Что проверяется
Dot Grid PNG 24 Пересечение в середине
Tile Arc JPG 6 Первый ресурс и последний формат
Soft Square SVG 7 Последний ресурс и первый формат
Loop Ribbon JPG 0 Явное нулевое количество
Tile Arcs PNG #N/A Отсутствующее название ресурса
Dot Grid TIFF #N/A Отсутствующий заголовок формата

Если число неверно, перед изменением метода поиска проверьте границы диапазонов. Для этой таблицы A2:A5 должен соответствовать четырём строкам диапазона B2:D5, а B1:D1 — его трём столбцам. Например, если включить A1 только в диапазон поиска ресурсов, вычисленная позиция сместится относительно нужной строки с количеством.

При расширении этой таблицы добавлением ещё одного ресурса в строку 6 измените диапазон ресурсов на A2:A6, а блок количеств — на B2:D6. Добавляя формат в столбец E, измените диапазон заголовков на B1:E1, а блок количеств — на B2:E5. Эти согласованные изменения сохраняют структуру, которую используют формулы.

Также учитывайте повторяющиеся названия. MATCH с точным совпадением возвращает позицию первого совпадения и не различает прописные и строчные буквы; в текстовом поиске она также трактует * и ? как подстановочные знаки. В этой таблице используйте уникальные названия без этих символов. Microsoft: функция MATCH

Выведите понятное сообщение, если название не найдено

После того как основной поиск заработает, можно заменить ошибку отсутствующего совпадения понятным сообщением:

=IFNA(INDEX($B$2:$D$5,MATCH(F2,$A$2:$A$5,0),MATCH(G2,$B$1:$D$1,0)),"Check asset or format")

IFNA возвращает указанное сообщение, если заключённая в неё формула выдаёт #N/A; в противном случае она сохраняет результат. Эта функция документирована для Microsoft 365 для Mac, Excel 2024 для Mac и Excel 2021 для Mac. Microsoft: функция IFNA

В этом примере для Tile Arcs выводится Проверьте ресурс или формат, а для Loop Ribbon с JPG по-прежнему возвращается 0. Это позволяет отличить отсутствующее название от записанного нулевого количества. Если в исходной ячейке количества указано значение #N/A, эта обёртка также заменит эту ошибку, поэтому проверьте исходную ячейку, если названия указаны верно.

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

Источники