До сих пор мы узнали, как связать: / Mathematical-functions-how-to-sum-column-in-a-excel-by-matching-heading [суммировать весь соответствующий столбец] `таблицы в Excel. Но как мы суммируем значения, когда нам нужно сопоставить столбец и строку. В этой статье мы узнаем, как суммировать совпадающие строки и столбцы.

Для этого есть две формулы, но сначала давайте рассмотрим сценарий.

Здесь у меня есть таблица, в которой записаны продажи сотрудников в разные месяцы. Имя сотрудников может повторяться. См. Рисунок ниже:

image

Нам нужно получить сумму за майский месяц, где продавец — Дональд.

Метод 1. Суммирование соответствующего заголовка столбца и заголовка строки с помощью функции СУММПРОИЗВ. Link: / summing-excel-sumproduct-function [SUMPRODUCT Function] — это наиболее универсальная функция, когда дело доходит до суммирования и подсчета значений с использованием сложных критериев. Общая функция для суммирования путем сопоставления столбца и строки:

=SUMPRODUCT((columns)(column_headers=column_heading)(row_headers=row_heading)

Столбцы: * Это двухмерный диапазон столбцов, которые вы хотите суммировать. Он не должен содержать заголовков. В таблице выше это C3: N7.

column_headers: это диапазон заголовков столбцов *, который вы хотите суммировать. В приведенных выше данных это C2: N2.

column_heading: * Это заголовок, который вы хотите сопоставить. В приведенном выше примере он находится в B13.

Без промедления воспользуемся формулой.

row_headers: это диапазон заголовков * строк, который вы хотите суммировать.

В приведенных выше данных это B3: B10.

row_heading: * Это заголовок, который вы хотите сопоставить в строках. В приведенном выше примере это F13.

Без промедления воспользуемся формулой.

Запишите эту формулу в ячейку D13 и позвольте Excel творить чудеса (волшебства не бывает) …​

=SUMPRODUCTC3:N10)(C2:N2=B13)(B3:B10=E13

Это возвращает значение:

image

Теперь при изменении месяца или продавца сумма изменится в соответствии с заголовком строки и заголовком столбца.

Как это работает?

Это простая логическая логика.

(C2: N2 = B13): этот оператор вернет массив ИСТИНА и ЛОЖЬ.

Все совпадающие значения в столбце имеют значение true, а другие — false. В этом случае у нас будет только одно значение True, поскольку диапазон C2: N2 содержит только один экземпляр May в 5 ^ th ^ Location.

(B3: B10 = E13): это будет работать так же, как указано выше, и вернет массив ИСТИНА и ЛОЖЬ. Все совпадающие значения будут иметь значение ИСТИНА, а другие — ЛОЖЬ. В этом случае у нас будет 2 ИСТИНА, так как диапазон B3: B10 имеет два экземпляра «Donald».

(C2: N2 = B13) (B3: B10 = E13): * Теперь мы умножаем массивы, возвращаемые операторами. Это реализует и логику, и мы получим массив из единиц и нулей. Теперь у нас будет 2D-массив, который будет содержать 2 единицы и остальные нули.

(C3: N10) (C2: N2 = B13) (B3: B10 = E13) = Наконец, мы умножаем 2D-массив на 2D-таблицу. Он снова вернет массив нулей и чисел, соответствующих критериям.

Наконец, функция СУММПРОИЗВ суммирует массив, что приведет к желаемому результату.

Метод 2: Суммирование заголовка совпадающего столбца и заголовка строки с помощью функции СУММ и ЕСЛИ Общая формула для суммирования совпадающих строк и столбцов с использованием функции Excel СУММ и ЕСЛИ:

=SUM(IF(column_headers=column_heading,IF(row_headers=row_heading,columns)))

Все переменные такие же, как в описанном выше методе. Здесь их просто нужно использовать в другом порядке.

Запишите эту формулу в ячейку D13:

=SUM(IF(C2:N2=B13,IF(B3:B10=E13,C3:N10)))

Это вернет правильный ответ. Смотрите скриншот ниже:

image

Как это работает?

Логика такая же, как и у первого метода SUMPRODCUT, только механизм другой. Если я объясню это вкратце, внутренняя ссылка link: / tips-if-condition-in-excel [IF Function] возвращает 2D-массив того же размера, что и таблица. Этот массив содержит количество двух совпадающих строк. Затем внутренняя ссылка : / tips-if-condition-in-excel [IF Function]

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

Наконец, ссылка: / суммирование-суммирование-значений-на-пересечении-двух или более-диапазонов [функция СУММ] суммирует этот массив, и мы получаем нашу сумму.

Так что да, ребята, вот как вы можете суммировать совпадающие строки и столбцы из таблицы в Excel. Надеюсь, это было для вас объяснительным и полезным. Если у вас есть какие-либо сомнения по этой теме или другие сомнения, связанные с Excel / VBA, задайте их в разделе комментариев ниже.

Статьи по теме:

link: / summing-how-to-sum-column-in-a-excel-by-matching-heading [Как суммировать столбец в Excel по совпадающему заголовку] | Если вы хотите получить сумму столбца, просто используя имя столбца, вы можете сделать это в Excel тремя простыми способами. Синтаксис метода SUMPRODUCT для суммирования соответствующего столбца:

link: / excel-generals-sumif-with-3d-reference-in-excel [СУММЕСЛИ с трехмерной ссылкой в ​​Excel] | * Интересный факт заключается в том, что обычные трехмерные ссылки Excel не работают с условными функциями, такими как функция СУММЕСЛИ . В этой статье мы узнаем, как заставить 3D-реферирование работать с функцией СУММЕСЛИ.

link: / excel-generals-relative-and-absolute-reference-in-excel [Относительная и абсолютная ссылка в Excel] | Ссылки в Excel — важная тема для каждого новичка. Даже опытные пользователи Excel делают ошибки при ссылках.

ссылка: / справочник-формул-динамического-рабочего листа [Справочник динамического рабочего листа] | Предоставляйте справочные листы динамически, используя функцию ДВССЫЛ в excel. Это просто …​

link: / excel-range-name-expanding-links-in-excel [Расширение ссылок в Excel] | Расширяющаяся ссылка расширяется при копировании вниз или вправо. Для этого мы используем знак $ перед номером столбца и строки. Вот один пример …​

link: / excel-range-name-absolute-reference-in-excel [Все об абсолютной ссылке] | Тип ссылки по умолчанию в Excel является относительным, но если вы хотите, чтобы ссылка на ячейки и диапазоны была абсолютной, используйте знак $. Вот все аспекты абсолютной ссылки в Excel.

Популярные статьи:

link: / keyboard-formula-shortcuts-50-excel-shortcuts-to-повышения-продуктивности [50 сочетаний клавиш Excel для повышения производительности] | Выполняйте свою задачу быстрее. Эти 50 ярлыков сделают вашу работу в Excel еще быстрее.

link: / формулы-и-функции-введение-функции-vlookup [Функция ВПР в Excel] | Это одна из наиболее часто используемых и популярных функций Excel, которая используется для поиска значений из разных диапазонов и листов.

link: / tips-countif-in-microsoft-excel [COUNTIF в Excel 2016] | Подсчитайте значения с условиями, используя эту удивительную функцию. Вам не нужно фильтровать данные для подсчета определенного значения.

Функция Countif важна для подготовки вашей приборной панели.

link: / excel-formula-and-function-excel-sumif-function [Как использовать функцию СУММЕСЛИ в Excel] | Это еще одна важная функция приборной панели. Это поможет вам суммировать значения для конкретных условий.