Итого в excel формула

Итого в excel формула

Полные сведения о формулах в Excel

​Смотрите также​​ по выборке);​Приступаем…​ отдельную ячейку. Единственное​ поля ввода данных.​ Excel – это​ кнопку «минус» в​ чтобы избежать двойного​ в диапазоне, в​ этой ячейке в​ с формулами. Перед​ строки выше в​Что происходит при перемещении,​ ​ функция СРЗНАЧ вычисляет​Ссылка указывает на ячейку​ доступна для скачивания.​Примечание:​– СТАНДОТКЛОНП (стандартное отклонение​Отсортируем диапазон по значению​ условие состоит в​

​После этого, окно аргументов​ австосумма.​ структуре.​ суммирования.​

​ котором имеются скрытые​​ значение, выполнив следующие​ заменой формулы на​ том же столбце​ копировании, вставке или​Смешанные ссылки​ среднее значение в​ или диапазон ячеек​ Если вы впервые​ Мы стараемся как можно​ по генеральной совокупности);​ первого столбца –​

Создание формулы, ссылающейся на значения в других ячейках

​ функции сворачивается, а​

​Для того, чтобы подсчитать​​Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() исключает все​Важно​

​ строки.​ действия.​ ее результат рекомендуется​

​R[2]C[2]​ удалении листов​ . Смешанная ссылка содержит либо​

​ диапазоне B1:B10 на​ листа и сообщает​ пользуетесь Excel или​

​ оперативнее обеспечивать вас​– СУММ;​ однотипные данные должны​

Просмотр формулы

​ ячейки, сумму которых​ мы можем выделить​ данным способом сумму,​​ неотображаемые строки структурой независимо от​​: Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() разработана​

​Особенность функции состоит в​Нажмите клавишу F2 для​ сделать копию книги.​Относительная ссылка на ячейку,​

Ввод формулы, содержащей встроенную функцию

​ . Нижеследующие примеры поясняют, какие​

​ абсолютный столбец и​ листе «Маркетинг» в​ Microsoft Excel, где​ даже имеете некоторый​ актуальными справочными материалами​

​– ДИСП (дисперсия по​ оказаться рядом.​

​ следует подсчитать, должны​ те ячейки, или​ кликаем по крайней​

​ используемого значения кода​ для столбцов данных​

Скачивание книги «Учебник по формулам»

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

Подробные сведения о формулах

​ предназначена для использования​Нажмите клавишу F9, а​ рассматриваются параметры и​ строки ниже и​

Части формулы Excel

​ трехмерных ссылках при​ абсолютную строку и​1. Ссылка на лист​​ значения или данные.​​ этой программой, данный​​ Эта страница переведена​​– ДИСПР (дисперсия по​​ таблице. Выбираем на​​ едином массиве.​​ значений которых хотим​​ или строки, и,​

​и, в нашем​​ данных. Она не​

​ совместно с другими​​ затем — клавишу​​ способы вычисления. Сведения​ на два столбца​ перемещении, копировании, вставке​

​ относительный столбец. Абсолютная​​ «Маркетинг».​​ С помощью ссылок​ учебник поможет вам​

​ автоматически, поэтому ее​​ генеральной совокупности).​​ ленте вкладку «Данные».​Просто выделяем диапазон ячеек,​ подсчитать. После того,​ находясь во вкладке​

​ случае, подсчитывает сумму​​ предназначена для строк​​ средствами EXCEL: Автофильтром​ ВВОД.​ о включении и​ правее​ и удалении листов,​

Использование констант в формулах Excel

​ ссылка столбцов приобретает​2. Ссылка на диапазон​ можно использовать в​ ознакомиться с самыми​ текст может содержать​Ссылка 1 – обязательный​ Группа «Структура» -​ сумму данных которых​ как массив выделен,​ «Главная», жмем на​ только товара Товар1. Этот​ данных или горизонтальных​ и Промежуточными итогами.​После преобразования формулы в​ выключении автоматического пересчета​R2C2​ на которые такие​ вид $A1, $B1​ ячеек от B1​ одной формуле данные,​ распространенными формулами. Благодаря​ неточности и грамматические​ аргумент, указывающий на​ команда «Промежуточные итоги».​ нужно узнать, и​ и его адрес​

Использование ссылок в формулах Excel

​ кнопку «Автосумма».​ результат аналогичен ситуации​ наборов данных (ее​ См. Файл примера.​ ячейке в значение​ листа см. в​Абсолютная ссылка на ячейку,​ ссылки указывают. В​ и т.д. Абсолютная​ до B10​ находящиеся в разных​ наглядным примерам вы​ ошибки. Для нас​ именованный диапазон для​Заполняем диалоговое окно «Промежуточные​ смотрим результат в​ появился в специальном​Программа выводит формулу в​ с автофильтром.​ использование в этом​ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер_функцииссылка1​ это значение (1932,322)​ статье Изменение пересчета,​

​ расположенную во второй​

​ примерах используется формула​ ссылка строки приобретает​3. Восклицательный знак (!)​ частях листа, а​ сможете вычислять сумму,​ важно, чтобы эта​ нахождения промежуточных итогов.​ итоги». В поле​ строке состояния программы​ поле, жмем на​ ячейку.​Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() может подсчитать​ случае может приводить​;ссылка2;. ))​ будет показано в​ итерации или точности​ строке второго столбца​ =СУММ(Лист2:Лист6!A2:A5) для суммирования​ вид A$1, B$1​ отделяет ссылку на​

​ также использовать значение​

​ количество, среднее значение​

​ статья была вам​Особенности «работы» функции:​ «При каждом изменении​

​ кнопку справа от​Для того, чтобы посмотреть​

​ сумму, количество и​

​ к непредсказуемым результатам).​Номер_функции​

​ строке формул. Обратите​

​ значений в ячейках​

​ и т.д. При​ лист от ссылки​ одной ячейки в​

​ и подставлять данные​

​ полезна. Просим вас​выдает результат по явным​

​ в» выбираем условие​

​Как видим, существует несколько​ этого поля.​ результат, нужно нажать​

​ среднее отобранных значений,​

​Пусть имеется исходная таблица.​ — это число от​

​ внимание, что 1932,322​

​Замена формул на вычисленные​Относительная ссылка на строку,​ с A2 по​ изменении позиции ячейки,​ на диапазон ячеек.​

​ нескольких формулах. Кроме​ не хуже профессионалов.​ уделить пару секунд​ и скрытым строкам;​ для отбора данных​ способов суммирования данных​

​Мы опять возвращаемся в​ на кнопку Enter​

​ а также включает​Применим Автофильтр и отберем​ 1 до 11, которое​

​ — это действительное​ значения​ расположенную выше текущей​ A5 на листах​

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

​ окно аргументов функции.​ на клавиатуре.​

​ еще 8 других​​ только строки с​ указывает какую функцию​ вычисленное значение, а​Замена части формулы на​ ячейки​ со второго по​ ссылка изменяется, а​ Если название упоминаемого листа​ ссылки на ячейки​ определенных элементах формулы,​ ли она вам,​ в фильтр;​ «Значение»). В поле​ Excel. Каждый из​ Если нужно добавить​Можно сделать и немного​ функций (см. синтаксис).​ товаром Товар1. Пусть​ использовать при вычислении​ 1932,32 — это​ вычисленное значение​R​ шестой.​

​ абсолютная ссылка не​ содержит пробелы или​​ разных листов одной​

​ просмотрите соответствующие разделы​​ с помощью кнопок​считает только в столбцах,​ «Операция» назначаем функцию​ этих способов имеет​ ещё один массив​ по-другому. Если мы​ Как правило, этик​ функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() подсчитает​ итогов внутри списка. ​ значение, показанное в​При замене формул на​Абсолютная ссылка на текущую​Вставка или копирование.​ изменяется. При копировании​ цифры, его нужно​ книги либо на​ ниже.​ внизу страницы. Для​ для строк не​ («Сумма»). В поле​ свой уровень сложности​ данных в общую​ хотим сложить ячейки​ функций вполне достаточно,​ сумму товаров Товар1,​

​Номер_функции​ ячейке в формате​​ вычисленные значения Microsoft​

​ строку​​ Если вставить листы между​ или заполнении формулы​ заключить в апострофы​ ячейки из других​Формула также может содержать​ удобства также приводим​ подходит.​ «Добавить по» следует​ и гибкости. Как​ сумму, то повторяем​ не всей строки​ но иногда требуется​ следовательно будем использовать​(включая скрытые значения)​ «Денежный».​ Office Excel удаляет​При записи макроса в​ листами 2 и​ вдоль строк и​ (‘), например так:​ книг. Ссылки на​ один или несколько​ ссылку на оригинал​Рассмотрим на примере использование​ пометить столбцы, к​ правило, чем проще​ те же действия,​ или столбца, а​

​ расширить возможности функции​ код функции 9 или​​Номер_функции​

​ эти формулы без​ Microsoft Excel для​​ 6, Microsoft Excel​ вдоль столбцов относительная​ ‘123’!A1 или =’Прибыль​ ячейки других книг​ таких элементов, как​ (на английском языке).​ функции:​ значениям которых применится​ вариант, тем он​ о которых говорилось​ только определенного диапазона,​ ПРОМЕЖУТОЧНЫЕ.ИТОГИ(). Рассмотрим пример​ 109.​(за исключением скрытых​ Если при редактировании ячейки​ возможности восстановления. При​ некоторых команд используется​ прибавит к сумме​ ссылка автоматически корректируется,​ за январь’!A1.​ называются связями или​функции​

​Начните создавать формулы и​Создаем дополнительную строку для​ функция.​ менее гибок. Например,​ выше, но только​ то выделяем этот​ вычисления среднего геометрического​Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() исключает все​ значений)​ с формулой нажать​ случайной замене формулы​ стиль ссылок R1C1.​ содержимое ячеек с​

​ а абсолютная ссылка​Различия между абсолютными, относительными​

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

​ в поле с​ диапазон. Затем кликаем​ для отобранных автофильтром​​ строки не включенные​Функция​ клавишу F9, формула​ на значение нажмите​ Например, если записывается​ A2 по A5​ не корректируется. Например,​ и смешанными ссылками​Стиль ссылок A1​ссылки​ чтобы выполнять расчеты​ Например, «сумма отобранных​ кнопку ОК. Исходная​ с помощью автосуммы,​

​ параметром «Число 2».​​ по уже знакомой​ значений. Функция СРГЕОМ()​ в результат фильтра​1​ будет заменена на​ кнопку​ команда щелчка элемента​

​ на новых листах.​​ при копировании или​Относительные ссылки​По умолчанию Excel использует​,​ и решать задачи.​ значений».​

​ таблица приобретает следующий​​ можно оперировать только​ При необходимости, подобным​ нам кнопке «Автосумма».​ отсутствует среди списка​ независимо от используемого​101​ вычисленное значение без​Отменить​Автосумма​Удаление​ заполнении смешанной ссылки​

​ . Относительная ссылка в формуле,​​ стиль ссылок A1,​операторы​Важно:​Включим фильтр. Оставим в​ вид:​ данными выстроенными в​

​ образом можно вводить​​Результат сразу же выводится​ функций доступных через​ значения константы​СРЗНАЧ​ возможности восстановления.​

​сразу после ввода​

​для вставки формулы,​ . Если удалить листы​ из ячейки A2​ например A1, основана​ в котором столбцы​и​ Вычисляемые результаты формул и​ таблице только данные​Если свернуть строки в​ ряд. Поэтому, в​ адреса практически неограниченного​ на экран.​ соответствующие коды, но​номер_функции​2​К началу страницы​

Источник:
http://my-excel.ru/formuly/itogo-v-excel-formula.html

Данные итогов в таблице Excel

В этом курсе:

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

Щелкните любое место таблицы.

Выберите Работа с таблицами > Конструктор и установите флажок Строка итогов.

Строка итогов будет вставлена в нижней части таблицы.

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

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

Вы увидите, что Excel создал следующую формулу: = подытог (109, [средне]). Это промежуточный итог для функции сумм, а также структурированная формула, которая является исключительно для таблиц Excel. Дополнительные сведения об использовании структурированных ссылок в таблицах Excel.

К итоговому значению можно применить и другие функции, щелкнув Другие функции или создав их самостоятельно.

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

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

Щелкните любое место таблицы.

Перейти к таблице > строка «итог«.

Читайте также  Как сделать протяжку в excel?

Строка итогов будет вставлена в нижней части таблицы.

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

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

Вы увидите, что Excel создал следующую формулу: = подытог (109, [средне]). Это промежуточный итог для функции сумм, а также структурированная формула, которая является исключительно для таблиц Excel. Дополнительные сведения об использовании структурированных ссылок в таблицах Excel.

К итоговому значению можно применить и другие функции, щелкнув Другие функции или создав их самостоятельно.

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

Вы можете быстро суммировать данные в таблице Excel, включив параметр Переключить строку итогов .

Щелкните любое место таблицы.

Откройте вкладку Конструктор таблиц > Параметры стиля > строка «Итого«.

Строка итогов будет вставлена в нижней части таблицы.

Установка агрегатной функции для ячейки строки итогов

Примечание: Это одна из нескольких бета-функций, и в настоящее время она доступна только для части инсайдеров Office. Мы будем оптимизировать такие функции в течение следующих нескольких месяцев. Когда они будут готовы, мы сделаем их доступными для всех участников программы предварительной оценки Office и подписчиков Microsoft 365.

Строка «итоги» позволяет выбрать агрегатную функцию, которая будет использоваться для каждого столбца.

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

Выберите агрегатную функцию, которая будет использоваться для столбца. Обратите внимание, что вы можете щелкнуть Дополнительные функции , чтобы просмотреть дополнительные параметры.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Источник:
http://support.microsoft.com/ru-ru/office/%D0%B4%D0%B0%D0%BD%D0%BD%D1%8B%D0%B5-%D0%B8%D1%82%D0%BE%D0%B3%D0%BE%D0%B2-%D0%B2-%D1%82%D0%B0%D0%B1%D0%BB%D0%B8%D1%86%D0%B5-excel-6944378f-a222-4449-93d8-474386b11f20

5 основ Excel (обучение): как написать формулу, как посчитать сумму, сложение с условием, счет строк и пр.

Здравствуйте!

Многие кто не пользуются Excel — даже не представляют, какие возможности дает эта программа! ☝

Подумать только: складывать в автоматическом режиме значения из одних формул в другие, искать нужные строки в тексте, создавать собственные условия и т.д. — в общем-то, по сути мини-язык программирования для решения «узких» задач (признаться честно, я сам долгое время Excel не рассматривал за программу, и почти его не использовал) .

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

То есть эта статья будет что-то мини гайда по обучению самому нужному для работы (точнее, чтобы начать пользоваться Excel и почувствовать всю мощь этого продукта!) .

Возможно, что прочти подобную статью лет 17-20 назад, я бы сам намного быстрее начал пользоваться Excel (и сэкономил бы кучу своего времени для решения «простых» задач. 👌

Обучение основам Excel: ячейки и числа

Примечание : все скриншоты ниже представлены из программы Excel 2016 (как одной из самой новой на сегодняшний день).

Многие начинающие пользователи, после запуска Excel — задают один странный вопрос: «ну и где тут таблица?». Между тем, все клеточки, что вы видите после запуска программы — это и есть одна большая таблица!

Теперь к главному : в любой клетке может быть текст, какое-нибудь число, или формула. Например, ниже на скриншоте показан один показательный пример:

  • слева : в ячейке (A1) написано простое число «6». Обратите внимание, когда вы выбираете эту ячейку, то в строке формулы (Fx) показывается просто число «6».
  • справа : в ячейке (C1) с виду тоже простое число «6», но если выбрать эту ячейку, то вы увидите формулу «=3+3» — это и есть важная фишка в Excel!

Просто число (слева) и посчитанная формула (справа)

👉 Суть в том, что Excel может считать как калькулятор, если выбрать какую нибудь ячейку, а потом написать формулу, например «=3+5+8» (без кавычек). Результат вам писать не нужно — Excel посчитает его сам и отобразит в ячейке (как в ячейке C1 в примере выше)!

Но писать в формулы и складывать можно не просто числа, но и числа, уже посчитанные в других ячейках. На скриншоте ниже в ячейке A1 и B1 числа 5 и 6 соответственно. В ячейке D1 я хочу получить их сумму — можно написать формулу двумя способами:

  • первый: «=5+6» (не совсем удобно, представьте, что в ячейке A1 — у нас число тоже считается по какой-нибудь другой формуле и оно меняется. Не будете же вы подставлять вместо 5 каждый раз заново число?!);
  • второй: «=A1+B1» — а вот это идеальный вариант, просто складываем значение ячеек A1 и B1 (несмотря даже какие числа в них!).

Сложение ячеек, в которых уже есть числа

Распространение формулы на другие ячейки

В примере выше мы сложили два числа в столбце A и B в первой строке. Но строк то у нас 6, и чаще всего в реальных задачах сложить числа нужно в каждой строке! Чтобы это сделать, можно:

  1. в строке 2 написать формулу «=A2+B2» , в строке 3 — «=A3+B3» и т.д. (это долго и утомительно, этот вариант никогда не используют) ;
  2. выбрать ячейку D1 (в которой уже есть формула) , затем подвести указатель мышки к правому уголку ячейки, чтобы появился черный крестик (см. скрин ниже) . Затем зажать левую кнопку и растянуть формулу на весь столбец. Удобно и быстро! ( Примечание : так же можно использовать для формул комбинации Ctrl+C и Ctrl+V (скопировать и вставить соответственно)) .

Кстати, обратите внимание на то, что Excel сам подставил формулы в каждую строку. То есть, если сейчас вы выберите ячейку, скажем, D2 — то увидите формулу «=A2+B2» (т.е. Excel автоматически подставляет формулы и сразу же выдает результат) .

Как задать константу (ячейку, которая не будет меняться при копировании формулы)

Довольно часто требуется в формулах (когда вы их копируете), чтобы какой-нибудь значение не менялось. Скажем простая задача: перевести цены в долларах в рубли. Стоимость рубля задается в одной ячейке, в моем примере ниже — это G2.

Далее в ячейке E2 пишется формула «=D2*G2» и получаем результат. Только вот если растянуть формулу, как мы это делали до этого, в других строках результата мы не увидим, т.к. Excel в строку 3 поставит формулу «D3*G3», в 4-ю строку: «D4*G4» и т.д. Надо же, чтобы G2 везде оставалась G2.

Чтобы это сделать — просто измените ячейку E2 — формула будет иметь вид «=D2*$G$2». Т.е. значок доллара $ — позволяет задавать ячейку, которая не будет меняться, когда вы будете копировать формулу (т.е. получаем константу, пример ниже) .

Константа / в формуле ячейка не изменяется

Как посчитать сумму (формулы СУММ и СУММЕСЛИМН)

Можно, конечно, составлять формулы в ручном режиме, печатая «=A1+B1+C1» и т.п. Но в Excel есть более быстрые и удобные инструменты.

Один из самых простых способов сложить все выделенные ячейки — это использовать опцию автосуммы (Excel сам напишет формулу и вставить ее в ячейку) .

Что нужно сделать, чтобы посчитать сумму определенных ячеек:

  1. сначала выделяем ячейки (см. скрин ниже 👇) ;
  2. далее открываем раздел «Формулы» ;
  3. следующий шаг жмем кнопку «Автосумма» . Под выделенными вами ячейками появиться результат из сложения;
  4. если выделить ячейку с результатом (в моем случае — это ячейка E8) — то вы увидите формулу «=СУММ(E2:E7)» .
  5. таким образом, написав формулу «=СУММ(xx)» , где вместо xx поставить (или выделить) любые ячейки, можно считать самые разнообразные диапазоны ячеек, столбцов, строк.

Автосумма выделенных ячеек

Как посчитать сумму с каким-нибудь условием

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

Я в своей таблицы буду использовать всего 7 строк (для наглядности) , реальная же таблица может быть намного больше. Предположим, нам нужно посчитать всю прибыль, которую сделал «Саша». Как будет выглядеть формула:

  1. » =СУММЕСЛИМН( F2:F7 ; A2:A7 ;»Саша») » — ( прим .: обратите внимание на кавычки для условия — они должны быть как на скрине ниже, а не как у меня сейчас написано на блоге) . Так же обратите внимание, что Excel при вбивании начала формулы (к примеру «СУММ. «), сам подсказывает и подставляет возможные варианты — а формул в Excel’e сотни!;
  2. F2:F7 — это диапазон, по которому будут складываться (суммироваться) числа из ячеек;
  3. A2:A7 — это столбик, по которому будет проверяться наше условие;
  4. «Саша» — это условие, те строки, в которых в столбце A будет «Саша» будут сложены (обратите внимание на показательный скриншот ниже) .

Сумма с условием

Примечание : условий может быть несколько и проверять их можно по разным столбцам.

Как посчитать количество строк (с одним, двумя и более условием)

Довольно типичная задача: посчитать не сумму в ячейках, а количество строк, удовлетворяющих какомe-либо условию.

Ну, например, сколько раз имя «Саша» встречается в таблице ниже (см. скриншот). Очевидно, что 2 раза (но это потому, что таблица слишком маленькая и взята в качестве наглядного примера). А как это посчитать формулой?

«=СЧЁТЕСЛИ( A2:A7 ; A2 )» — где:

  • A2:A7 — диапазон, в котором будут проверяться и считаться строки;
  • A2 — задается условие (обратите внимание, что можно было написать условие вида «Саша», а можно просто указать ячейку).

Результат показан в правой части на скрине ниже.

Количество строк с одним условием

Теперь представьте более расширенную задачу: нужно посчитать строки, где встречается имя «Саша», и где в столбце «B» будет стоять цифра «6». Забегая вперед, скажу, что такая строка всего лишь одна (скрин с примером ниже) .

Формула будет иметь вид:

=СЧЁТЕСЛИМН( A2:A7 ; A2 ; B2:B7 ;»6″) — (прим.: обратите внимание на кавычки — они должны быть как на скрине ниже, а не как у меня) , где:

A2:A7 ; A2 — первый диапазон и условие для поиска (аналогично примеру выше);

B2:B7 ;»6″ — второй диапазон и условие для поиска (обратите внимание, что условие можно задавать по разному: либо указывать ячейку, либо просто написано в кавычках текст/число).

Счет строк с двумя и более условиями

Как посчитать процент от суммы

Тоже довольно распространенный вопрос, с которым часто сталкиваюсь. Вообще, насколько я себе представляю, возникает он чаще всего — из-за того, что люди путаются и не знают, что от чего ищут процент (да и вообще, плохо понимают тему процентов (хотя я и сам не большой математик, и все таки. ☝) ).

👉 В помощь!

Как посчитать проценты: от числа, от суммы чисел и др. [в уме, на калькуляторе и с помощью Excel] — заметка для начинающих

Самый простой способ, в котором просто невозможно запутаться — это использовать правило «квадрата», или пропорции.

Читайте также  Как сделать разные таблицы в excel на одном листе?

Вся суть приведена на скрине ниже: если у вас есть общая сумма, допустим в моем примере это число 3060 — ячейка F8 (т.е. это 100% прибыль, и какую то ее часть сделал «Саша», нужно найти какую. ).

По пропорции формула будет выглядеть так: =F10*G8/F8 (т.е. крест на крест: сначала перемножаем два известных числа по диагонали, а затем делим на оставшееся третье число).

В принципе, используя это правило, запутаться в процентах практически невозможно 👌.

Пример решения задач с процентами

PS

Собственно, на этом я завершаю данную статью. Не побоюсь сказать, что освоив все, что написано выше (а приведено здесь всего лишь «пяток» формул) — Вы дальше сможете самостоятельно обучаться Excel, листать справку, смотреть, экспериментировать, и анализировать. 👌

Скажу даже больше, все что я описал выше, покроет многие задачи, и позволит решать всё самое распространенное, над которым часто ломаешь голову (если не знаешь возможности Excel) , и даже не догадывается как быстро это можно сделать. ✔

Источник:
http://ocomp.info/kak-napisat-formulu-v-excel.html

Промежуточные итоги в Excel (+ примеры функций)

Сделать окончательные итоги в рамках таблицы Excel не составляет труда. Поэтому многие пользователи интересуются, какими способами возможно подведение промежуточных в рамках одной таблицы. Конечно можно все делать вручную, но поскольку Excel – это программа для автоматизации обработки данных, давайте более подробно рассмотрим методы, с помощью которых можно поручить эту задачу компьютеру.

Требования к таблицам для подведения промежуточных итогов

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

  1. Внутри каждой ячейки должна содержаться информация. Не должно быть ни одной пустой.
  2. Запрещено создание шапки, состоящей из нескольких строк. Допустимо использование лишь одной. Также важно убедиться, что шапка располагается на первом ряду листа электронной таблицы.
  3. Таблица не должна быть отформатирована, как таковая. Простыми словами, должен быть обычный диапазон.

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

Процесс вычисления промежуточных итогов в Excel

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

1

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

Есть и другой способ выполнения этой задачи – специальная команда, которая так и называется – «Промежуточные итоги».

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

После этого непосредственно начинаем работу:

    Ориентируясь на содержимое ячеек, относящихся к первому столбцу, необходимо сделать так, чтобы данные одного типа находились вместе.

2
После этого осуществляем случайный выбор любой ячейки. Далее смотрим на ленту. Интересующая нас вкладка называется «Данные». Там же находится нужная нам кнопка, которую можно найти в группе «Структура».

3

  • После совершения этих нехитрых действий перед нами всплывет окошко, в каком задаются параметры итогов. Там есть следующие поля:
    • При каждом изменении в. В скриншоте это пункт «Название».
    • Операция. Здесь нужно выбрать непосредственно ту функцию, которая наиболее подходит под текущую задачу. В ситуации с нами такой является – «Сумма».
    • Добавить итоги по. Тут необходимо указать колонки, для которых требуется создание промежуточных итогов.
  • Нажимаем кнопку «ОК», чтобы подтвердить внесенные изменения и закрываем диалоговое окно.
  • После выполнения всех операций таблица обретет такой вид.

    4

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

    5

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

    6

    Есть множество дополнительных настроек итогов. Так, чтобы они отделялись от основной таблицы разрывом страницы, необходимо нажать на флажок «Конец страницы между группами».

    7

    Также возможно изменение расположения промежуточных итогов. Есть возможность разместить их над группой, а не под ней. Для этого необходимо убрать флажок рядом с пунктом «Итоги под данными».

    8

    Можно применять больше одной статистической функции в промежуточных итогах. Нами уже была назначена «Сумма», но можно добавить и средние продажи конкретных товаров.

    9

    Для этого необходимо снова зайти в меню «Промежуточные итоги». Далее необходимо найти пункт «Заменить текущие», а потом в поле «Операция» кликаем по функции «Среднее».

    Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ

    Как результат, возвращаемый функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ служит, как понятно из названия, промежуточный результат. С ее помощью возможно более гибкое создание промежуточных итогов.

    Синтаксис функции

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

    10

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

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

    Давайте на реальном примере разберем, как правильно применять эту функцию.

    1. Добавляем отдельный ряд. Даем ему имя. Пусть это будет, как вариант, «сумма отобранных значений».
    2. Активируем фильтрацию информации так, чтобы в диапазоне оставались лишь те числа, которые располагаются на одном ряду с текстом «Обеденная группа «Амадис».
    3. Далее вводим формулу в ячейку B2 (или какую-угодно, в которую есть возможность вывести промежуточный результат) =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;B3:B15).

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

    Если же нужно было рассчитать среднее значение, то формула имела бы такой вид.

    11

    В случае с поиском максимального значения, функция была бы такой.

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

    Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ () и Автофильтр

    Давайте представим, что у нас есть таблица с номерами товаров и их стоимостью.

    12

    Давайте используем функцию «Автофильтр», чтобы отображать лишь ряды, описывающие товар номер 1. И давайте тогда применим эту функцию, чтобы узнать сумму товаров, отмеченных номером 1. Соответственно, нам требуется функция 9 или 109.

    13

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

    Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ () и Скрытые строки

    Предположим, у нас есть такая же таблица, к которой не был применен автофильтр. Ряды, озаглавленные «Товар 2» мы попробуем просто скрыть стандартным способом. То есть, воспользоваться или контекстным меню, или перейти по пути Главная – Ячейки – Формат – Скрыть.

    14

    Здесь пользователю необходимо убедиться, что используется тот код, подходящий под скрытые строки. Если говорить проще, то для поиска промежуточной суммы необходимо ввести код 109, а не 9. Последний вариант не чувствителен к скрытым строкам. Поэтому при ручной фильтрации данных нужно использовать коды, начиная 101 и заканчивая 111. Обозначения соответствующие описанным выше, просто нужно к ним добавить 100.

    Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ () и средство Excel Промежуточные итоги

    Давайте теперь создадим другую таблицу и сделаем промежуточные итоги при использовании специальной опции Excel (не статистической функции, а специальное средство, описанное в начале этой статьи).

    15

    Теперь давайте отфильтруем таблицу, оставив только Товар1 в ней.

    16

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

    Другие функции

    В целом, количество функций, которые могут использоваться для подсчета промежуточного значения, достаточное. Но может оказаться так, что нужно включать другие. Предположим, перед нами стоит задача посчитать не среднее арифметическое, а среднее геометрическое. Эта функция выглядит, как СРГЕОМ() , и ее нет в описанном выше списке, но выкрутиться из этой ситуации получится. Для этого давайте откроем такую же таблицу.

    17

    Теперь с помощью автофильтра оставляем только одну категорию строк. Итак, у нас задача – заставить функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ() определить среднее геометрическое цен товаров с кодом функции 3. Сама формула должна быть формулой массива (то есть, взятой в фигурные скобки).

    Сама формула будет такой:

    18

    Как видим, мы используем сочетание функций СТРОКА и ДВССЫЛ в том месте, где расположен второй аргумент функции. Так можно добиться того, чтобы второму аргументу передавалось несколько диапазонов. Главное – убедиться в том, чтобы не было скрытой строки, потому что тогда помимо стоимости будет отображаться значение «Пустой текст», а оно функцией СРГЕОМ() не учитывается.

    Формула «Промежуточные итоги» в Эксель (примеры)

    Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ может быть использована в ситуации, если данных в таблице огромное количество. В этом случае после ручной настройки через описанный в начале статьи функционал будет возможность отображать только одну часть таблицы. При этом все функции будут работать так, как будто таблица и не фильтровалась вовсе.

    Первый пример такой. Предположим, нам нужно понять, какие промежуточные результаты продаж гитары марки lbanez.

    Наш диапазон выглядит следующим образом.

    19

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

    Если же использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ, то результат будет показан лишь для значений, оставшихся после фильтрации. Эта разница видна на этом скриншоте.

    20

    Теперь приведем пример выборочного суммирования ячеек. Для этого в окне настройки формулы ПРОМЕЖУТОЧНЫЕ.ИТОГИ (ее можно найти в окне ввода формул, что вызывается путем нажатия кнопки fx) нужно выставить соответствующие галочки в меню «Добавить итоги по», чтобы выбрать столбцы, для которых будет осуществляться суммирование. А чтобы формула пересчитывалась каждый раз при изменении, существует настройка «При каждом изменении в».

    Написание формулы промежуточных итогов вручную

    Написать формулу промежуточных итогов вручную не сложнее, чем любую другую формулу. Единственное, что надо знать – синтаксис.

    Любая формула начинается со знака =, потом пишется непосредственно название функции, открывается скобка, перечисляются все аргументы через точку с запятой), а потом закрывается скобка.

    Промежуточные итоги в сводной таблице Excel

    Добавление промежуточных итогов возможно и в сводной таблице. Этого нужно воспользоваться вкладкой «Итоги и фильтры» в ее параметрах. Чтобы осуществить вывод итогов на экран по отдельным значениям, необходимо воспользоваться кнопкой фильтра (в правой части названия колонки).

    Читайте также  Как в excel сделать разделитель разрядов пробелом?

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

    Источник:
    http://office-guru.ru/excel/podschet-summirovanie/promezhutochnye-itogi-v-excel-primery-funkcij.html

    Как сделать формулу в excel итого?

    Допустим у нас есть некая таблица. Мы «нарисовали» ее сами или получили в виде отчета из учетной системы. В ней все хорошо только нет итогов. Придется вводить формулы и потом их копировать. В зависимости от размеров таблицы этот процесс может занять длительное время. В этой статье я расскажу, как это можно сделать очень быстро.

    Итак, приступим. Есть у нас вот такая таблица:

    Мы выделяем все числовые данные плюс одну строку снизу и один столбец справа, чтобы получилось так:

    Переходим во вкладку «Главная» и в разделе «Редактирование» нажимаем кнопку «Автосумма«:

    И MS Excel все сделал за нас:

    Нам осталось только написать недостающие названия строки и столбца.

    А если наша таблица выглядит чуть сложнее и нам недостаточно посчитать общие итоги? Давайте разберем такую таблицу:

    Помимо общих итогов по столбцам и строкам, нам необходимо получить промежуточные итоги по менеджерам, чтобы было так:

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

    В открывшемся диалоговом окне в списке «Добавить итоги по:» устанавливаем галочки напротив пунктов «Январь«, «Февраль«, «Март» и нажимаем кнопку «ОК«:

    После чего наша таблица приобретет следующий вид:

    Если вас «напрягают» плюсики и минусики, образовавшиеся слева, во вкладке «Данные» в разделе «Структура» нажмите кнопку «Разгруппировать«, в выпавшем списке нажмите пункт «Удалить структуру«:

    Что делать дальше вы уже, наверное, догадались? Выделяем все числовые данные и один пустой столбец справа, во вкладке меню «Главная» в разделе «Редактирование» нажимаем «Автосумма«:

    Таблица приобретет такой вид:

    Форматируем необходимые строки и столбцы и получаем что хотели:

    Источник:
    http://e-xcel.ru/nachinayushchim/kak-bystro-poschitat-itogi-v-tablitse?_utl_t=vk

    8 простых способов как посчитать в Excel сумму столбца

    Как посчитать сумму в Excel быстро и просто? Чаще всего нас интересует итог по столбцу либо строке. Попробуйте различные способы найти сумму по столбцу, используйте функцию СУММ или же преобразуйте ваш диапазон в «умную» таблицу для простоты расчетов, складывайте данные из нескольких столбцов либо даже из разных таблиц. Все это мы увидим на примерах.

    Как суммировать весь столбец либо строку.

    Если мы вводим функцию вручную, то в вашей таблице Excel появляются различные возможности расчетов. В нашей таблице записана ежемесячная выручка по отделам.

    Если поставить формулу суммы в G2

    то получим общую выручку по первому отделу.

    Обратите внимание, что наличие текста, а не числа, в ячейке B1 никак не сказалось на подсчетах. Складываются только числовые значения, а символьные – игнорируются.

    Важное замечание! Если среди чисел случайно окажется дата, то это окажет серьезное влияние на правильность расчетов. Дело в том, что даты хранятся в Excel в виде чисел, и отсчет их начинается с 1900 года ежедневно. Поэтому будьте внимательны, рассчитывая сумму столбца в Excel и используя его в формуле целиком.

    Все сказанное выше в полной мере относится и к работе со строками.

    Но суммирование столбца целиком встречается достаточно редко. Гораздо чаще область, с которой мы будем работать, нужно указывать более тонко и точно.

    Суммируем диапазон ячеек.

    Важно научиться правильно указать диапазон данных. Вот как это сделать, если суммировать продажи за 1-й квартал:

    Формула расчета выглядит так:

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

    В формуле последовательно перечисляем несколько диапазонов:

    Естественно, их может быть не два, а гораздо больше: до 255 штук.

    Как вычислить сумму каждой N-ой строки.

    В таблице расположены повторяющиеся с определенной периодичностью показатели — продажи по отделам. Необходимо рассчитать общую выручку по каждому из них. Сложность в том, что интересующие нас показатели находятся не рядом, а чередуются. Предположим, мы анализируем сведения о продажах трех отделов помесячно. Необходимо определить продажи по каждому отделу.

    Иначе говоря, нужно, двигаясь вниз, взять каждую третью строчку.

    Это можно сделать двумя способами.

    Первый – самый простой, «в лоб». Складываем все цифры нужного отдела обычной математической операцией сложения. Выглядит просто, но представьте, если у вас статистика, предположим, за 3 года? Придется обработать 36 чисел…

    Второй способ – для более «продвинутых», но зато универсальный.

    И затем нажимаем комбинацию клавиш CTRL+SHIFT+ENTER, поскольку используется формула массива. Excel сам добавит к фигурные скобки слева и справа.

    Как это работает? Нам нужна 1-я, 3-я, 6-я и т.д. позиции. При помощи функции СТРОКА() мы вычисляем номер текущей позиции. И если остаток от деления на 3 будет равен нулю, то значение будет учтено в расчете. В противном случае – нет.

    Для такого счетчика мы будем использовать номера строк. Но наше первое число находится во второй строке рабочего листа Эксель. Поскольку надо начинать с первой позиции и потом брать каждую третью, а начинается диапазон со 2-й строчки, то к порядковому номеру её добавляем 1. Тогда у нас счетчик начнет считать с цифры 3. Для этого и служит выражение СТРОКА(C2:C16)+1. Получим 2+1=3, остаток от деления на 3 равен нулю. Так мы возьмем 1-ю, 3-ю, 6-ю и т.д. позиции.

    Формула массива означает, что Excel должен последовательно перебрать все ячейки диапазона – начиная с C2 до C16, и с каждой из них произвести описанные выше операции.

    Когда будем находить продажи по Отделу 2, то изменим выражение:

    Ничего не добавляем, поскольку первое подходящее значение как раз и находится в 3-й позиции.

    Аналогично для Отдела 3

    Вместо добавления 1 теперь вычитаем 1, чтобы отсчет вновь начался с 3. Теперь брать будем каждую третью позицию, начиная с 4-й.

    Ну и, конечно, не забываем нажимать CTRL+SHIFT+ENTER.

    Примечание. Точно таким же образом можно суммировать и каждый N-й столбец в таблице. Только вместо функции СТРОКА() нужно будет использовать СТОЛБЕЦ().

    Сумма каждых N строк.

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

    Используем то, что СУММ() может складывать значения не только в диапазоне данных, но и в массиве. Такой массив значений ей может предоставить функция СМЕЩ.

    Напомним, что здесь нужно указать несколько аргументов:

    1. Начальную точку. Обратите внимание, что С2 мы ввели как абсолютную ссылку.

    2. Сколько шагов вниз сделать

    3. Сколько шагов вправо сделать. После этого попадаем в начальную (левую верхнюю) точку массива.

    4. Сколько значений взять, вновь двигаясь вниз.

    5. Сколько колонок будет в массиве. Попадаем в конечную (правую нижнюю) точку массива значений.

    Итак, формула для 1-й недели:

    В данном случае СТРОКА() – это как бы наш счетчик недель. Отсчет нужно начинать с 0, чтобы действия начать прямо с ячейки C2, никуда вниз не перемещаясь. Для этого используем СТРОКА()-2. Поскольку сама формула находится в ячейке F2, получаем в результате 0. Началом отсчета будет С2, а конец его – на 5 значений ниже в той же колонке.

    СУММ просто сложит предложенные ей пять значений.

    Для 2-й недели в F3 формулу просто копируем. СТРОКА()-2 даст здесь результат 1, поэтому начало массива будет 1*5=5, то есть на 5 значений вниз в ячейке C7 и до С11. И так далее.

    Как найти сумму наибольших (наименьших) значений.

    Задача: Суммировать 3 максимальных или 3 минимальных значения.

    Функция НАИБОЛЬШИЙ возвращает самое большое значение из перечня данных. Хитрость в том, что второй ее аргумент показывает, какое именно значение нужно вернуть: 1- самое большое, 2 – второе по величине и т.д. А если указать <1;2;3>– значит, нужны три самых больших. Но при этом не забывайте применять формулу массива и завершать комбинацией клавиш CTRL+SHIFT+ENTER.

    Аналогично обстоит дело и с самыми маленькими значениями:

    3-D сумма, или работаем с несколькими листами рабочей книги Excel.

    Чтобы подсчитать цифры из одинаковой формы диапазона на нескольких листах, вы можете записывать координаты данных специальным синтаксисом, называемым «3d-ссылка».

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

    Посмотрите на этом небольшом видео, как применяются 3-D формулы.

    Как видите, у нас имеется 4 одинаковых таблицы. Стандартный метод нахождения выручки за месяц –

    В качестве аргументов перечисляем диапазоны данных.

    Мы указываем программе, что нужно произвести вычисления с ячейками B2:B8, находящимися на листах неделя1, неделя2, неделя3, неделя4. Здесь номер листа последовательно увеличивается на 1.

    Важное замечание. Вы можете пользоваться 3D-ссылками и в других случаях. К примеру, выражение

    найдет среднее значение однодневной выручки за месяц.

    Поиск нужного столбца и расчет его суммы.

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

    Для поиска и выбора конкретного товара используем весьма популярную комбинацию функций ИНДЕКС+ПОИСКПОЗ.

    Расчет суммы в G3 выполним так:

    Итак, комбинация ИНДЕКС+ПОИСКПОЗ должны возвратить для дальнейших расчетов набор чисел в виде вертикального массива, который и будет потом просуммирован.

    Опишем это подробнее.

    ПОИСКПОЗ находит в шапке наименований таблицы B1:D1 нужный продукт (бананы) и возвращает его порядковый номер (иначе говоря, 2).

    Затем ИНДЕКС выбирает из массива значений B2:D21 соответствующий номер столбца (второй). Будет возвращен весь столбик данных с соответствующим номером, поскольку номер строки (первый параметр функции) указан равным 0. На нашем рисунке это будет С2:С21. Остается только подсчитать все значения в этой колонке.

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

    Сумма столбцов из нескольких таблиц.

    Как в Экселе посчитать сумму столбца, если таких столбцов несколько, да и сами они находятся в нескольких разных таблицах?

    Для получения итогов сразу по нескольким таблицам также используем функцию СУММ и структурированные ссылки. Такие ссылки появляются при создании в Excel «умной» таблицы.

    При создании её Excel назначает имя самой таблице и каждому заголовку колонки в ней. Эти имена затем можно использовать в выражениях: они могут отображаться в виде подсказок в строке ввода.

    Источник:
    http://mister-office.ru/funktsii-excel/sum-function-excel.html