Чтобы объединить соответствующие показатели инвентаря в Excel для Mac, сложите совпадающие числовые массивы с помощью +. Если строки с объектами расположены в разном порядке, сначала найдите каждый объект, а затем сложите возвращённые количества. Так отдельные показатели сохранятся в отдельных результатах.

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

Выберите формулу в зависимости от соответствия объектов

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

Условие для исходных данных Подходящий способ Результат
Одинаковые объекты, порядок строк и столбцы показателей Сложить числовые диапазоны одинакового размера По одному результату для каждого объекта и показателя
Уникальные подписи объектов, разный порядок строк Сложить результаты двух многоколоночных формул XLOOKUP Показатели одного объекта в отдельных ячейках
Повторяющиеся подписи объектов, записи которых нужно объединить Выполнить отдельный расчёт SUMIF для каждого показателя Результат для каждого нужного объекта и показателя

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

Проверьте выпуск Excel для Mac

Пример с поиском подходит для Excel для Microsoft 365 для Mac, Excel 2024 для Mac и Excel 2021 для Mac. Microsoft явно указывает, что функция XLOOKUP недоступна в Excel 2016 и Excel 2019, хотя эти выпуски указаны в общем списке применимости на странице. Документация Microsoft по XLOOKUP.

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

Для необязательной формулы HSTACK, приведённой далее в статье, требуется Excel для Microsoft 365 для Mac или Excel 2024 для Mac; Microsoft не указывает Excel 2021 для Mac для этой функции. Для основных вычислений она не нужна. Документация Microsoft по HSTACK.

Создайте два небольших списка художественных материалов

Используйте пустой лист с обычными диапазонами ячеек. Введите инвентарь первого места хранения в A1:C4:

Объект Экземпляры печатной продукции Карточки с образцами цветов
Tile-A 8 3
Tile-B 5 7
Tile-C 6 2

Введите инвентарь второго места хранения в E1:G4, оставив столбец D пустым:

Объект Экземпляры печатной продукции Карточки с образцами цветов
Tile-A 4 2
Tile-B 9 1
Tile-C 3 5

Оба числовых блока имеют высоту три строки и ширину два столбца. В первом числовом столбце указано количество экземпляров печатной продукции, во втором — количество карточек с образцами цветов. Подписи находятся за пределами диапазонов для вычислений.

Подготовьте область результатов в J1:L4, как показано ниже. Пока введите только заголовки и подписи объектов; ячейки с количеством оставьте пустыми.

Объект Всего экземпляров печатной продукции Всего карточек с образцами цветов
Tile-A
Tile-B
Tile-C

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

Складывайте выровненные массивы, чтобы сохранить каждый соответствующий результат

Если исходные строки расположены в показанном порядке, введите эту полную формулу листа в K2:

=B2:C4+F2:G4

Подтвердите ввод клавишей Return. В Excel с динамическими массивами результат занимает K2:L4: три строки объектов и два столбца показателей. Microsoft указывает, что формулы массива могут возвращать несколько результатов, а динамические результаты разливаются в соседние ячейки. Рекомендации и примеры формул массива.

Ожидаемый результат:

Объект Всего экземпляров печатной продукции Всего карточек с образцами цветов
Tile-A 12 5
Tile-B 14 8
Tile-C 9 7

Вычисление выполняется по позициям: B2+F2 даёт 12, а C2+G2 — 5. Для каждой оставшейся позиции действует то же правило. Два исходных блока превращаются в один блок результатов тех же размеров.

Для инвентаря с одним столбцом эквивалентная формула выглядела бы так: =B2:B4+F2:F4. Она возвращает три количества — по одному для каждой строки. Версия с двумя столбцами просто сохраняет второй показатель рядом с первым.

Почему внешняя функция SUM даёт неверную структуру результата

Эти формулы вычисляют общий итог:

=SUM(B2:C4,F2:G4)

=SUM(B2:C4+F2:G4)

Для этого примера каждая формула возвращает 55. Это число объединяет данные по всем трём объектам и двум показателям. Microsoft указывает, что SUM складывает переданные ей значения, ссылки или диапазоны; поэтому обёртывание массива результатов в SUM сводит его к одному значению. Документация Microsoft по SUM.

Используйте приведённый выше результат из шести ячеек для проверки. Одно значение 55 в одной ячейке отвечает на другую задачу, хотя арифметически оно рассчитано верно.

Расположение для выровненных строк, совместимое с разными версиями

Если вы предпочитаете формулы в отдельных ячейках, введите =B2+F2 в K2. Перетащите маркер заполнения вправо в L2, а затем заполните диапазон K2:L2 вниз до строки 4. Относительные ссылки изменятся и вычислят каждую соответствующую пару. Microsoft описывает такой способ заполнения с помощью маркера для Excel для Mac. Использование Excel в качестве калькулятора в Excel для Mac.

Так получатся те же шесть значений без разлива результата. При этом порядок строк в исходных данных всё равно должен совпадать.

Сопоставляйте объекты по подписям, если строки в исходных данных расположены в разном порядке

Если на порядок строк нельзя полагаться, сначала очистите предыдущий результат из K2. Оставьте список нужных объектов в J2:J4. Введите следующую формулу в K2:

=XLOOKUP($J2,$A$2:$A$4,$B$2:$C$4)+XLOOKUP($J2,$E$2:$E$4,$F$2:$G$4)

Каждая формула поиска находит объект в J2 и возвращает два его столбца с количествами. Сложение возвращённых строк даёт 12 в K2 и 5 в L2. Microsoft документирует использование точного совпадения по умолчанию и возврат нескольких элементов из многоколоночного диапазона возврата. Документация Microsoft по XLOOKUP.

Скопируйте формулу из K2 в K3:K4, оставив L2:L4 свободным для горизонтальных результатов. Знаки доллара фиксируют исходные диапазоны; нужный объект меняется с каждой строкой результата.

Для диагностики поменяйте порядок полных записей во втором исходном списке так, чтобы Tile-C стоял первым. Перемещайте каждую подпись вместе с обоими количествами. Результат поиска должен остаться прежним, тогда как сложение по позициям теперь сопоставляло бы разные объекты.

Для отсутствующей подписи возвращается #N/A, поскольку в формуле не указана необязательная замена для отсутствующего совпадения. Для повторяющейся подписи возвращается первое совпадение, а не сумма записей. Это задокументированные ограничения поиска. Документация Microsoft по XLOOKUP.

В результат попадут только объекты из списка J2:J4. Перед использованием собственных данных проверьте, что в этот список входят все объекты из обоих мест хранения, которые вы собираетесь отобразить.

Объединяйте повторяющиеся записи объектов, обрабатывая каждый показатель отдельно

Если несколько строк относятся к одному объекту намеренно, используйте условное суммирование. Для исходных диапазонов из примера введите эту полную формулу в K2:

=SUMIF($A$2:$A$4,$J2,B$2:B$4)+SUMIF($E$2:$E$4,$J2,F$2:F$4)

Скопируйте её вправо в L2, а затем заполните диапазон K2:L2 вниз до строки 4. При перемещении вправо диапазоны количеств изменятся на столбцы с карточками образцов, а диапазоны подписей останутся фиксированными. Каждая ячейка результата суммирует только данные своего объекта и показателя. Microsoft документирует использование одного диапазона для условий и другого — для соответствующих количеств. Документация Microsoft по SUMIF.

Чтобы показать повторяющиеся записи, добавьте Tile-A, 2 и 1 в A5:C5. Перед повторным вычислением расширьте диапазоны условий и количеств для первого исходного списка до строки 5:

=SUMIF($A$2:$A$5,$J2,B$2:B$5)+SUMIF($E$2:$E$4,$J2,F$2:F$4)

Скопируйте изменённую формулу вправо и вниз, как раньше. Для примера результат Tile-A составит 14 экземпляров печатной продукции и 6 карточек с образцами цветов. Фиксированный диапазон, заканчивающийся на строке 4, пропустил бы добавленную запись, поэтому при использовании этого метода нужно следить за диапазонами.

Диапазоны условий должны иметь тот же размер и форму, что и диапазоны количеств. Также учтите, что SUMIF воспринимает * и ? как подстановочные знаки; в используемых здесь условных подписях их нет. Документация Microsoft по SUMIF.

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

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

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

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

Для получения необязательного блока результатов с подписями в Microsoft 365 для Mac или Excel 2024 для Mac используйте исходный выровненный пример в новой пустой области:

=HSTACK(A2:A4,B2:C4+F2:G4)

Так подписи объектов размещаются рядом с вычисленными показателями. HSTACK добавляет столбцы, а содержащаяся в формуле функция + выполняет сложение. Эта формула не сопоставляет подписи и не исправляет разный порядок строк в исходных данных. Документация Microsoft по HSTACK.

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

Источники