Если вы хотите получить сумму столбца, просто используя имя столбца, вы можете сделать это в Excel тремя простыми способами. Давайте исследуем эти способы.

В отличие от других статей, давайте сначала посмотрим сценарий.

Здесь у меня есть таблица продаж, сделанных разными продавцами в разные месяцы.

image

Теперь задача — получить сумму продаж за данный месяц в ячейке C10. Если мы изменим месяц в B10, сумма должна измениться и вернуть сумму этого месяца, ничего не меняя в формуле.

Метод 1: Суммировать весь столбец в таблице с помощью функции СУММПРОИЗВ. Синтаксис метода SUMPRODUCT для суммирования совпадающего столбца:

=SUMPRODUCTcolumns)(headers=heading*

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

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

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

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

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

=SUMPRODUCTC3:N7)(C2:N2=B10*

и это вернет:

image

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

Это просто. В формуле оператор C2: N2 = B10 возвращает массив, содержащий все значения FALSE, кроме одного, соответствующего B10. Теперь формула

=SUMPRODUCT((C3:N7)\{FALSE,FALSE,FALSE,FALSE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE})

Теперь C3: N7 умножается на каждое значение этого массива. Каждый столбец становится нулевым, кроме столбца, умноженного на ИСТИНА. Теперь формула становится:

=SUMPRODUCT(\{0,0,0,0,6,0,0,0,0,0,0,0;0,0,0,0,12,0,0,0,0,0,0,0;0,0,0,0,15,0,0,0,0,0,0,0;0,0,0,0,15,0,0,0,0,0,0,0;0,0,0,0,8,0,0,0,0,0,0,0})

Теперь этот массив суммируется, и мы получаем сумму столбца, который соответствует столбцу в ячейке B10.

Метод 2: Суммировать весь столбец в таблице с помощью функции ИНДЕКС-ПОИСКПОЗ. Синтаксис метода суммирования совпадающих заголовков столбцов в Excel:

=SUM(INDEX(columns,,MATCH(heading,headers,0)))

Все переменные в этом методе такие же, как и в методе SUMPRODUCT.

Давайте просто реализуем это, чтобы решить проблему. Запишите эту формулу в C10.

=SUM(INDEX(C3:N7,,MATCH(B10,C2:N2,0)))

Это возвращает:

image

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

Формула решена наизнанку. Во-первых, link: / lookup-formulas-excel-match-function [MATCH function] возвращает индекс соответствующего месяца из диапазона C2: N2.

Поскольку у нас May в B1o, мы получаем 5. Теперь формула принимает вид

=SUM(INDEX(C3:N7,,5))

Затем link: / lookup-formulas-excel-index-function [INDEX function] возвращает значения из 5-го столбца C3: N7. Теперь формула становится:

=SUM(\{6;12;15;15;8})

И, наконец, получаем сумму этих значений.

Метод 3: Суммировать весь столбец в таблице с использованием именованного диапазона и функции КОСВЕННО Все становится просто, если вы называете свои диапазоны заголовками столбцов. В этом методе нам сначала нужно назвать столбцы их заголовками.

Выделите таблицу, включая заголовки, и нажмите CTRL + SHIFT + F3. Откроется диалоговое окно для создания имени из диапазонов. Проверьте верхний ряд и нажмите кнопку ОК.

image

Он будет называть все столбцы данных их заголовками.

Теперь общая формула для суммирования соответствующего столбца будет:

=SUM(INDIRECT(heading))

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

Чтобы реализовать эту универсальную формулу, запишите эту формулу в ячейку C10.

=SUM(INDIRECT(B10))

Это возвращает сумму месяца мая:

image

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

=SUM(INDIRECT(«Table1[«&B10&»]»))

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

В этой формуле функция ДВССЫЛ берет ссылку на имя и преобразует ее в действительную ссылку на имя. Далее процедура проста. Функция СУММ суммирует именованный диапазон.

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

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

link: / summing-how-to-sum-by-row-and-column [Как суммировать путем сопоставления строки и столбца в Excel] | * СУММПРОИЗВ — самая универсальная функция, когда дело доходит до суммирования и подсчета ценности с хитрыми критериями. Общая функция для суммирования путем сопоставления столбца и строки: …​

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

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

link: / lookup-formulas-dynamic-workheet-reference [Справочник по динамическому рабочему листу] | Предоставляйте справочные листы динамически с помощью функции КОСВЕННО в 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] | Это еще одна важная функция приборной панели. Это поможет вам суммировать значения для конкретных условий.