Как в excel посчитать сумму определенных ячеек

Использование функции «Автосумма» для суммирования чисел

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

Когда вы нажимаете кнопку Автосумма, Excel автоматически вводит формулу для суммирования чисел (в которой используется функция СУММ).

Приведем пример. Чтобы добавить в этот бюджет для развлечения номера в январе, выберите ячейку B7, а не ячейку, расположенную под столбцом чисел. Затем нажмите кнопку Автосумма. В ячейке B7 появится формула, и Excel выделит ячейки, для которых выполняется суммирование.

Чтобы отобразить результат (95,94) в ячейке В7, нажмите клавишу ВВОД. Формула также отображается в строке формул вверху окна Excel.

Чтобы сложить числа в столбце, выберите ячейку под последним числом в столбце. Чтобы сложить числа в строке, выберите первую ячейку справа.

Функция автосуммирования находится в двух расположениях: » Главная » > «Автосумма», а формулы > Автосумма.

Создав формулу один раз, ее можно копировать в другие ячейки, а не вводить снова и снова. Например, при копировании формулы из ячейки B7 в ячейку C7 формула в ячейке C7 автоматически настроится под новое расположение и подсчитает числа в ячейках C3:C6.

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

Также вы можете суммировать числа путем создания простых формул.

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

Когда вы нажимаете кнопку Автосумма, Excel автоматически вводит формулу для суммирования чисел (в которой используется функция СУММ).

Приведем пример. Чтобы добавить в этот бюджет для развлечения номера в январе, выберите ячейку B7, а не ячейку, расположенную под столбцом чисел. Затем нажмите кнопку Автосумма. В ячейке B7 появится формула, и Excel выделит ячейки, для которых выполняется суммирование.

Чтобы отобразить результат (95,94) в ячейке В7, нажмите клавишу ВВОД. Формула также отображается в строке формул вверху окна Excel.

Чтобы сложить числа в столбце, выберите ячейку под последним числом в столбце. Чтобы сложить числа в строке, выберите первую ячейку справа.

Функция автосуммирования находится в двух расположениях: » Главная » > «Автосумма», а формулы > Автосумма.

Создав формулу один раз, ее можно копировать в другие ячейки, а не вводить снова и снова. Например, при копировании формулы из ячейки B7 в ячейку C7 формула в ячейке C7 автоматически настроится под новое расположение и подсчитает числа в ячейках C3:C6.

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

Также вы можете суммировать числа путем создания простых формул.

Если требуется суммировать столбец или строку чисел, позвольте Excel Online выполнить математические действия. Выберите ячейку рядом с числами, которые необходимо сложить, нажмите кнопку «Автосумма» на вкладке Главная и нажмите клавишу ВВОД.

При нажатии кнопки автосумма Excel Online автоматически вводит формулу (в которой используется функция сумм) для суммирования чисел.

Приведем пример. Чтобы добавить в этот бюджет для развлечения номера в январе, выберите ячейку B7, а не ячейку, расположенную под столбцом чисел. Затем нажмите кнопку Автосумма. В ячейке B7 появится формула.

Нажмите клавишу Ввод , чтобы отобразить результат (95,94) в ячейке B7. Вы также просмотреть формулу в строке формул.

На планшете или телефоне с Android

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

Коснитесь элемента Автосумма.

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

На вкладке Формулы выберите Автосумма.

Нажмите Сумма и нажмите клавишу ВВОД.

Выберите ячейку под последним числом в столбце.

Нажмите кнопку ленты .

Коснитесь элемента Автосумма.

На планшете или телефоне с Windows

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

Коснитесь элемента Автосумма.

СЧЕТЕСЛИ с несколькими условиями.

На самом деле функция Эксель СЧЕТЕСЛИ не предназначена для расчета количества ячеек по нескольким условиям. В большинстве случаев я рекомендую использовать его множественный аналог — функцию СЧЕТЕСЛИМН. Она как раз и предназначена для вычисления количества ячеек, которые соответствуют двум или более условиям (логика И). Однако, некоторые задачи могут быть решены путем объединения двух или более функций СЧЕТЕСЛИ в одно выражение.

Количество чисел в диапазоне

Одним из наиболее распространенных применений функции СЧЕТЕСЛИ с двумя критериями является определение количества чисел в определенном интервале, т.е. меньше X, но больше Y.

Например, вы можете использовать для вычисления ячеек в диапазоне B2: B9, где значение больше 5 и меньше или равно 15:

Количество ячеек с несколькими условиями ИЛИ.

Когда вы хотите найти количество нескольких различных элементов в диапазоне, добавьте 2 или более функций СЧЕТЕСЛИ в выражение. Предположим, у вас есть список покупок, и вы хотите узнать, сколько в нем безалкогольных напитков.

Сделаем это:

Обратите внимание, что мы включили подстановочный знак (*) во второй критерий. Он используется для вычисления количества всех видов сока в списке

Как вы понимаете, сюда можно добавить и больше условий.

Сумма каждых 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. И так далее.

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

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

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

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

Как посчитать сумму с разных листов в Excel?

Изучим возможности для суммирования данных из ячеек с разных листов книги с помощью использования трехмерных ссылок в Excel.

Практически каждый из нас при работе в Excel хотя бы раз наверняка применял операцию сложения с помощью оператора плюс «+» и различных функций (СУММ, СУММЕСЛИ, СУММЕСЛИМН и т.п.).

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

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

Вариант 1. Ручной ввод

Начнем с одной стороны с медленного варианта, но с другой стороны с более понятного и наглядного способа.

Для примера возьмем простую задачу и сложим данные ячейки A1 с трех листов: Лист1, Лист2 и Лист3. Для этого поочередно в формулу записываем ссылки на ячейку с разных листов и знаки сложения «+»:

=Лист1!A1+Лист2!A1+Лист3!A1

Это совсем простой вариант. Еще один вариант записи можно аналогично реализовать с помощью функции СУММ:

=СУММ(Лист1!A1;Лист2!A1;Лист3!A1)

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

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

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

Вариант 2. Полуавтоматический ввод

Повторно воспользуемся функцией СУММ, но в этот раз запишем ссылку на листы через двоеточие:

=СУММ(Лист1:Лист3!A1)

В этом случае общее количество суммируемых листов для нас уже не так важно, главное знать только начальный и конечный. Поэтому запись для сложения 10 или 20 листов будет примерно такой же — ИмяПервогоЛиста:ИмяПоследнегоЛиста!Диапазон

Поэтому запись для сложения 10 или 20 листов будет примерно такой же — ИмяПервогоЛиста:ИмяПоследнегоЛиста!Диапазон.

Чуть более подробно остановимся на логике формирования формулы.

Напомню, что оператор двоеточие в Excel служит для объединения диапазона, который образует ссылку на все ячейки находящиеся между первой и последней (включая сами ячейки).

Например, диапазон A1:A10 обозначает двумерную ссылку, в которую попадают все ячейки между первой (A1) и последней (A10).

При добавлении еще одной размерности в виде листов мы получаем так называемую трехмерную ссылку. К примеру, диапазон Лист1:Лист3!A1:A10 содержит все ячейки от Лист1!A1:A10 до Лист3!A1:A10.

Таким образом в нашем примере суммируются все ячейки A1 от Лист1 до Лист3. Но при применении такого способа суммирования есть некоторые тонкости, которые желательно нужно помнить, чтобы случайно не допустить ошибку, давайте подробнее про них поговорим.

Особенности трехмерных ссылок

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

• Добавление/копирование листа. Если добавить новый лист или скопировать уже существующий и вставить его между первым и последним (используемых в формуле), то он автоматически будет участвовать в расчете.
  Т.е. сама формула не поменяется, а значение вполне может измениться, если на новом листе в тех же ячейках содержатся данные;
• Удаление листа. Аналогичный случай примеру выше. При удалении листа формула не изменится, а расчет поменяется в зависимости от удаленных данных (если удалить начальный или конечный листы, то формула изменится, промежуточный — формула останется неизменной);
• Перемещение листа. При перемещении листа в другое место книги формула не изменится, но поменяется набор листов между ними (либо добавятся новые, либо удалятся текущие).

Если описание покажется несколько сложным, в этом случае попробуйте поэкспериментировать с формулой и перестановкой листов на конкретном примере и поймите по какому принципу она работает.

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

Сумма с разных листов

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

1. Выберите пустую ячейку на первом листе, затем перейдите в мастер функций так, как описывалось выше.
2. В окне «Аргументы функции» поставьте курсор в строку Число2, затем перейдите на другой лист программы.
3. Выделите нужные для подсчета ячейки и кликните по кнопке подтверждения.

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

Программа Microsoft Excel: подсчет суммы

Автосумма

​ и ставим в​ в отдельности.​Во время работы в​ «Р*В»). Найдет фамилии​ или используете автофильтр,​Есть несколько способов​вычисления​

​ при вставке строк​число1​ были заполнены данными.​ адрес данного диапазона​ такой вид:​ временные затраты увеличатся​ при помощи арифметической​ и ставим в​

​ ячейку, которая находится​ дней, если строчками​

​ ней знак «=».​Для того, чтобы просмотреть​ программе Microsoft Excel​ — Рублев, Рылеев,​

​ чтобы отображались только​ добавить или вычесть​имеет значение​ или столбцов​    (обязательный)​ Это и есть​ тут же будет​=СУММ(число1;число2;…)​ ещё больше.​ формулы можно подсчитать​

​ ней знак «=».​ под ним. Затем,​

​ таблицы являются дни,​ После этого, поочередно​ сумму целого массива,​ часто требуется подбить​ т.д.​ определенные данные, может​ значения времени. Например,​Авто​Если вставить строку или​Первое число для сложения.​ сумма значений отдельно​ отображен в поле​Как видим, аргументами этого​Гораздо более быстрым способом​ сумму в строчке.​ Затем, поочередно кликаем​ жмем на всю​

​ или общую стоимость​ кликаем по каждой​ или нескольких массивов​

​ сумму в столбцах​? (знак вопроса)​ понадобиться вычислить сумму​ чтобы получить разницу​.​ столбец, формула не​

Функция «СУММ»

​ Это может быть​ по строкам. Такой​ окна аргументов. Затем​ оператора являются числа​ сложить данные в​ Давайте посмотрим, как​

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

​ обозначает один символ.​ только видимых ячеек.​ между 8:00 и​Вкладка «формулы»​ будут обновляться для​ число 4, ссылка​

​ результат удалось получить​ щелкаем по кнопке​ или адреса ячеек,​ строчке является применение​ действует данный способ​ столбца, которые желаете​ «Автосумма».​ Давайте выясним различные​ сумму значений которых​

​ Microsoft Excel существует​ а также просто​ Например, критерий «ст?л»​ Для этого можно​ 12:00 для вычисления​нажмите​ включения добавленная строка,​ на ячейку, например​ потому, что по​«OK»​ в которых они​ автосуммы.​ на конкретном примере.​

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

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

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

​ воспользоваться формулой​. Можно также использовать​ автоматически обновляется (при​ ячеек, например B2:B8.​ в Excel относительны,​После того, как мы​ может быть до​ числовыми значениями первой​ указана выручка пяти​ нужно нажимать на​ которая расположена под​ данные столбца программы​ адрес ячейки добавлен​ хотим, чтобы выводилась​ несколько инструментов для​ Можно написать несколько​ строку итогов в​=(«12:00»-«8:00»)*24​F9​ условии, что вы​число2–255​ а не абсолютны,​

​ произвели указанное действие,​ 255.​ строчки. Выделение проводим,​ магазинов по датам.​ клавишу «+». Формула​ столбцом.​ Microsoft Excel.​

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

Просмотр суммы в приложении Microsoft Excel

​ не вне диапазона,​    (необязательный)​ и при копировании​ сумма значений строки​Посмотрим, как можно просуммировать​ зажав левую кнопку​ Наименования магазинов являются​ ввода отображается в​Сумму для нескольких столбцов​Скачать последнюю версию​ вводим знак «+»​ кнопке «Вставить функцию»,​

​ Давайте разберемся, как​ «Иван?????» (Иванов, Иванченко,​ функция, выбранная из​ время начала от​ на листе.​ указанных в формуле).​

​Это второе число для​ изменяют свои координаты.​ тут же отобразится​ элементы в строке​ мыши. Перейдя во​ названиями строк, а​ выбранной вами ячейке,​ одновременно можно подсчитать,​ Excel​ с клавиатуры, и​ расположенной слева от​ суммировать ячейки в​ т.д.)​ раскрывающегося списка «Итог»,​ времени окончания

Обратите​Некоторые значения не добавляются.​ Это важно, особенно​ сложения. Можно указать​Урок: Как сделать автозаполнение​

​ в той ячейке,​

lumpics.ru>

В Эксель есть автосумма, а нужен автоминус как сделать !!!

​ секунд и сообщить,​ с помощью автосуммы,​ условие состоит в​ с клавиатуры, и​ установили вывод результатов,​ кнопку справа от​ Выделяем её, и​ ячеек, и кликаем​​ диапазон. Затем кликаем​ Excel – это​Eugen n​ наоборот​Должно получиться типа:​ в нижний правый​Вениамин шац​​ СУММЕСЛИМН . Например​ также можно работать​​ помогла ли она​ можно оперировать только​ том, что все​​ так после ввода​ отобразиться общая сумма​ этого поля.​ жмем на кнопку​ по кнопке «Автосумма».​ по уже знакомой​ австосумма.​: postawit minus pered​​Делаю через автосумму​ =сумм (C3:C14;E3:E14;G3:G14;I3:I14)​ край выделения, чтобы​: Красиво ответили оба​ нужно добавить вверх​ по горизонтали при​ вам, с помощью​ данными выстроенными в​

​ ячейки, сумму которых​​ координат каждой ячейки.​

​ данных всех указанных​​Мы опять возвращаемся в​ «OK».​Но на экран выводится​ нам кнопке «Автосумма».​

​Для того, чтобы подсчитать​​ formuloj awtosummi​ просто минус перед​

​Можете набрать в​

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

Критерии

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

СУММЕСЛИМН и умные таблицы

Посмотрите на пример 1 нашего учебного файла, формула в L6

Обратите внимание, как удобно использовать структурные ссылки умных таблиц в формулах вообще и в СУММЕСЛИМН в частности

L4 и L5 содержат значения критериев.

Критерий для периода дат

А вот как задать период дат (с. по . ) при суммировании:

Обратите внимание на очень важную конструкцию:

Это не что иное, как выражение в виде текстовой строки. Причём это не статическое выражение, которое обычно приводят в справке по СУММЕСЛИМН , типа » >= 200000 «. Это выражение динамическое, то есть — гораздо более ценное и интересное. Всё что вам нужно — это вставить между знаком операции » >= » и ссылкой на ячейку с параметром — знак операции сложения строк » & «.

Динамическая операция сравнения

А почему бы не дать на откуп пользователю право определять операцию для критерия? В предыдущем примере пользователь мог выбирать порог N в критерии » >=N «. А в этом примере пользователь определяет и порог и саму операцию! А почему нет?

В L12 у нас находится выпадающий список, ссылающийся на 4 возможные операции: >, >=, ? » — заменяет любой символ, » * » — заменяет любое количество символов (в том числе и его отсутствие). Например критерий » *т* » сработает и на слово «Центр», и на слово «Восток». А, если бы существовала такая альтернатива, то сработал бы и на слово «опт» (это, как раз случай, когда вторая звёздочка в «*т*» заменила ноль символов справа от «т»).

Ответы на сложные вопросы

При помщи *ЕСЛИМН формул можно отвечать на довольно заковыристые вопросы. Например, какая доля холодильников в Центре была продана по ценам, превышающим средние цены холодильников по всем магазинам? А вот формула, которое это расчитывает:

Как видите мы использовали 2 формулы СУММЕСЛИМН и 1 СРЗНАЧЕСЛИМН . СРЗНАЧЕСЛИМН вернула нам средние цены по холодильникам. Первая СУММЕСЛИМН вернула количество проданных телевизоров с ценами выше средних, а вторая формула вернула общее количество холодильников, проданных в Центре. Найдя частное между результатами двух СУММЕСЛИМН , мы получили долю от единицы и просто выразили её в процентах.

Набор магазинов

А что делать, если нам нужны продажи двух магазинов? В рамках одной формулы это решить в общем случае нельзя (метод через символы подстановки не универсален, так как магазины могут иметь полностью уникальные имена). Ответ очевиден — надо использовать 2 формулы и просто сложить их результаты. Хотя понятно, что такой метод подойдёт далеко не во всех ситуациях.

Но есть и ограничения.

Надеюсь вы убедились, что *ЕСЛИМН достаточно гибки, но есть один узкий момент, который надо хорошо понимать. Например, я хочу знать, сколько раз я торговал в Центре, предоставляя покупателям скидку более или равную 5%? Как я могу это узнать? Только вычисляя по каждой строке отношение предоставленной скидки к базовой цене. Понимаете — по каждой строке надо делать вычисление и сравнивать с 5%! Вот такое формулы *ЕСЛИМН сами, без вашей помощи сделать не смогут, так как формула один раз вычисляет критерий, а потом сравнивает его со всеми строками критериального диапазона, а нам надо это делать динамически. Но никто нам не мешает организовать дополнительный столбец, который будет предварительно считать отношение скидки к базовой цене, а после этого можно уже остальную работу поручить формуле СЧЁТЕСЛИМН (см. пример 7).

Пустые ячейки

Если вы хотите, чтобы формула *ЕСЛИМН отреагировала на пустые ячейки, то следует использовать критерии «» (пустая строка) или «=». Например, посмотрите лист Blank нашего учебного файла:

= СУММЕСЛИМН ( E3:E10 ; B3:B10 ; «=» )

однако, если пустота ячейки является следствием работы формулы (например, формулы ЕСЛИ ), то такая ячейка отреагирует только на критерий «», то есть:

Не пустые ячейки

А вот, если критерием является то, что ячейка хоть что-то содержит, то можно использовать такую форму:

ПРОСМОТР и СУММ – поиск в массиве и сумма связанных значений

В случае, когда Ваше искомое значение — это массив, функция ВПР становится бесполезной, поскольку она не умеет работать с массивами данных. В такой ситуации Вы можете использовать функцию ПРОСМОТР (LOOKUP) в Excel, которая похожа на ВПР, к тому же работает с массивами так же, как и с одиночными значениями.

Давайте разберем пример, чтобы Вам стало понятнее, о чём идет разговор. Предположим, у нас есть таблица, в которой перечислены имена клиентов, купленные товары и их количество (таблица Main table). Кроме этого, есть вторая таблица, содержащая цены товаров (таблица Lookup table). Наша задача – написать формулу, которая найдёт сумму всех заказов заданного клиента.

Как Вы помните, нельзя использовать функцию ВПР, если искомое значение встречается несколько раз (это массив данных). Используйте вместо этого комбинацию функций СУММ и ПРОСМОТР:

Так как это формула массива, не забудьте нажать комбинацию Ctrl+Shift+Enter при завершении ввода.

Lookup table – это название листа, где находится просматриваемый диапазон.

Давайте проанализируем составные части формулы, чтобы Вы понимали, как она работает, и могли настроить её под свои нужды. Функцию СУММ пока оставим в стороне, так как её цель очевидна.

1. Функция ПРОСМОТР просматривает товары, перечисленные в столбце C основной таблицы (Main table), и возвращает соответствующую цену из столбца B просматриваемой таблицы (Lookup table).

2. $D$2:$D$10 – количество товаров, приобретенных каждым покупателем, чьё имя есть в столбце D основной таблицы. Умножая количество товара на цену, которую возвратила функция ПРОСМОТР, получаем стоимость каждого приобретенного продукта.
3. $B$2:$B$10=$G$1 – формула сравнивает имена клиентов в столбце B основной таблицы с именем в ячейке G1. Если есть совпадение, возвращается 1, в противном случае . Таким образом, отбрасываются имена покупателей, отличающиеся от указанного в ячейке G1, ведь все мы знаем – умножение на ноль дает ноль.

Так как наша формула – это формула массива, она повторяет описанные выше действия для каждого значения в массиве поиска. В завершение, функция СУММ вычисляет сумму значений, получившихся в результате умножения. Совсем не сложно, Вы согласны?

Замечание. Чтобы функция ПРОСМОТР работала правильно, просматриваемый столбец должен быть отсортирован в порядке возрастания.

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

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

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

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

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

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

А вот 3D-метод:

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

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

К примеру, выражение

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

Поправка автоматического распознания диапазонов

При введении функции с помощью кнопки на панели инструментов или при использовании мастера функций (SHIFT+F3). функция СУММ() относится к группе формул «Математические». Автоматически распознанные диапазоны не всегда являются подходящими для пользователя. Их можно при необходимости быстро и легко поправить.

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

1. Перейдите в ячейку D1 и выберите инструмент «Сумма».
2. Удерживая клавишу CTRL дополнительно выделите мышкой диапазон A2:B2 и ячейку A3.
3. После выделения диапазонов нажмите Enter и в ячейке D4 сразу отобразиться результат суммирования значений ячеек всех диапазонов.

Обратите внимание на синтаксис в параметрах функции при выделении нескольких диапазонов. Они разделены между собой (;)

В параметрах функции СУММ могут содержаться:

• ссылки на отдельные ячейки;
• ссылки на диапазоны ячеек как смежные, так и несмежные;
• целые и дробные числа.

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

Для наглядного примера рассмотрим разные варианты суммирования значений ячеек, которые дают один и тот же результат. Для этого заполните ячейки A1, A2 и A3 числами 1, 2 и 3 соответственно. А диапазон ячеек B1:B5 заполните следующими формулами и функциями:

При любом варианте мы получаем один и тот же результат вычисления – число 11. Последовательно пройдите курсором от B1 и до B5. В каждой ячейке нажмите F2, чтобы увидеть цветную подсветку ссылок для более понятного анализа синтаксиса записи параметров функций.

Подсчет суммы в Excel с помощью таблицы

Встроенные инструменты Excel позволяют создать отформатированную таблицу из данных и взять результат оттуда. Для этого:

1. Выделите необходимые данные и преобразуйте их в таблицу, нажав на кнопку «Форматировать как таблицу» на панели инструментов. Выберите нужный стиль.
2. В появившемся окне подтвердите выбранный диапазон ячеек нажатием на кнопку «ОК».
3. Перейдите на вкладку «Конструктор», нажмите на кнопку «Параметры стилей таблиц» и поставьте галочку напротив пункта «Строка итогов».
4. Под отформатированной таблицей появится новая строка, в которой отобразится сумма, автоматически рассчитанная программой.