Использование сочетания функций впр и просмотр с сумм или суммесли в excel

Как посчитать сумму времени в Excel?

Каждая дробная часть чисел в Excel может отображаться в часах и минутах (в зависимости от выбранного формата отображения).

Данный факт лучше усвоить на конкретном примере:

1. Заполните диапазон ячеек A1:A3 числовыми значениями: 0,25 (четверть дня); 0,5(полдень); 0,75 (3/4 дня).
2. Выделите A1:A3 и с помощью диалогового окна «Формат ячеек» (CTRL+1) задайте формат «Время», как показано на рисунке:

Чтобы сложить время в Excel по формуле или с помощью функции:=СУММ() нужно использовать специальный формат.

1. В ячейку А4 введите:=СУММ(A1:A3). Как видно вместо 36 часов мы видим только 12 – это время следующего дня. А чтобы отобразить сумму часов меняем формат ячейки.
2. Открываетм окно «Формат ячеек»-«Число»-«Числовые форматы»-«Все форматы». В поле «Тип:» вводим: :мм:сс. И нажимаем ОК.

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

Пример логического выражения в формуле для суммы с условием

Другой пример, когда нам нужно отдельно суммировать цены на группы товаров стоимости до 1000 и отдельно со стоимостью больше 1000. В таком случае одного оператора сравнения нам недостаточно ( =1000) иначе мы просуммируем сумму ровно в 1000 – 2 раза, что приведет к ошибочным итоговым результатам:

Это очень распространенная ошибка пользователей Excel при работе с логическими функциями!

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

В данном примере используется символ сравнения – «меньше» ( ) меньше ( ), больше или равно (>=), меньше или равно ( Таблица правил составления критериев условий:

Второй аргумент функции СУММЕСЛИ, то есть условие, которое должно быть выполнено, записывается между двойными кавычками. В данном примере используется символ сравнения – «меньше» ( ) меньше ( ), больше или равно (>=), меньше или равно ( Таблица правил составления критериев условий:

Чтобы создать условие	Примените правило	Пример
Значение равно заданному числу или ячейке с данным адресом.	Не используйте знак равенства и двойных кавычек.	=СУММЕСЛИ(B1:B10;3)
Значение равно текстовой строке.	Не используйте знак равенства, но используйте двойные кавычки по краям.	=СУММЕСЛИ(B1:B10;»Клиент5″)
Значение отличается от заданного числа.	Поместите оператор и число в двойные кавычки.	=СУММЕСЛИ(B1:B10;»>=50″)
Значение отличается от текстовой строки.	Поместите оператор и число в двойные кавычки.	=СУММЕСЛИ(B1:B10;»выплата»)
Значение отличается от ячейки по указанному адресу или от результата вычисления формулы.	Поместите оператор сравнения в двойные кавычки и соедините его символом амперсант (&) вместе со ссылкой на ячейку или с формулой.	=СУММЕСЛИ(A1:A10;» «&СЕГОДНЯ())
Значение содержит фрагмент строки	Используйте операторы многозначных символов и поместите их в двойные кавычки	=СУММЕСЛИ(A1:A10;»*кг*»;B1:B10)

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

Важно отметить что сегодня на момент написания статьи дата – «03.11.2018». Чтобы суммировать числовые значения только по сегодняшней дате используйте формулу:

Чтобы суммировать только значения от сегодняшнего дня включительно и до конца периода времени воспользуйтесь оператором «больше или равно» (>=) вместе с соответственной функцией =СЕГОДНЯ(). Формула c операторам (>=):

=»&СЕГОДНЯ();B2:B10)/B11′ >

СУММЕСЛИ и ее синтаксис

Функция СУММЕСЛИ позволяет суммировать ячейки, которые удовлетворяют определенному критерию (заданному условию). Аргументы команды следующие:

1. Диапазон – ячейки, которые следует оценить на основании критерия (заданного условия).
2. Критерий – определяет, какие ячейки из диапазона будут выбраны (записывается в кавычках).
3. Диапазон суммирования – фактические ячейки, которые необходимо просуммировать, если они удовлетворяют критерию.

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

Вычисление минимального, максимального и среднего значения в Microsoft Excel

В Microsoft Office Excel можно работать с цифрами и узнавать любое числовое значение. Этот табличный процессор справится практически со всеми расчётами. Он идеально подходит для бухгалтерского учёта. Для вычислений существуют специальные инструменты — формулы. Их можно применять к диапазону или к отдельным ячейкам. Чтобы узнать минимальную или максимальную цифру в группе клеток, необязательно искать их самостоятельно. Лучше воспользоваться предназначенными для этого опциями. Также полезно будет разобраться, как посчитать среднее значение в Excel.

Это особенно актуально в таблицах с большим объёмом данных. Если в столбце, например, указаны цены на продукцию торгового центра. И вам надо узнать, какой товар самый дешёвый. Если искать его «вручную», уйдёт очень много времени. Но в Экселе это можно сделать буквально за несколько кликов. Утилита также высчитывает среднее арифметическое. Ведь это две простые операции: сложение и деление.

В этой статье мы расскажем, как вычислить различные значения в Эксель

Максимальное и минимальное

Вот как найти максимальное значение в Excel:

1. Поставьте курсор-ячейку в любое место.
2. Перейдите в меню «Формулы».
3. Нажмите «Вставить функцию».
4. В списке выберите «МАКС». Или напишите это слово в поле «Поиск» и нажмите «Найти».
5. В окне «Аргументы» введите адреса диапазона, максимальное значение которого вам нужно узнать. В Excel имена клеток состоят из буквы и цифры («B1», «F15», «W34»). А название диапазона — это первая и последняя ячейки, которые в него входят.
6. Вместо адреса можно написать несколько чисел. Тогда система покажет самое большее из них.
7. Нажмите «OK». В клетке, в которой стоял курсор, появится результат.

Следующий шаг — укажите диапазон значений

Теперь будет легче разобраться, как найти минимальное значение в Excel. Алгоритм действий полностью идентичен. Просто вместо «МАКС» выберите «МИН».

Среднее

Среднее арифметическое вычисляется так: сложить все цифры из множества и поделить на их количество. В Экселе можно посчитать суммы, узнать, сколько ячеек в строке и так далее. Но это слишком сложно и долго. Придётся использовать много разных функций. Держать в голове информацию. Или даже что-то записывать на листочек. Но можно упростить алгоритм.

Вот как найти среднее значение в Excel:

1. Поставьте ячейку курсор в любое свободное место таблицы.
2. Перейдите на вкладку «Формулы».
3. Нажмите на «Вставить функцию».
4. Выберите «СРЗНАЧ».
5. Если этого пункта нет в списке, откройте его с помощью опции «Найти».
6. В области «Число1» введите адрес диапазона. Или напишите несколько цифр в разных полях «Число2», «Число3».
7. Нажмите «OK». В ячейке появится нужное значение.

Нажмите ОК для подсчета

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

Другие способы

Максимальное, минимальное и среднее можно узнать и другими способами.

1. Найдите панель функций с обозначением «Fx». Она над основной рабочей областью таблицы.
2. Поставьте курсор в любую ячейку.
3. Введите в поле «Fx» аргумент. Он начинается со знака равенства. Потом идёт формула и адрес диапазона/клетки.
4. Должно получиться что-то вроде «=МАКС(B8:B11)» (максимальное), «=МИН(F7:V11)» (минимальное), «=СРЗНАЧ(D14:W15)» (среднее).
5. Кликните на «галочку» рядом с полем функций. Или просто нажмите Enter. В выделенной ячейке появится нужное значение.
6. Формулу можно скопировать непосредственно в саму клетку. Эффект будет тот же.

Впишите диапазон и нажмите Enter

Найти и вычислить поможет Excel-инструмент «Автофункции».

1. Поставьте курсор в ячейку.
2. Перейдите в раздел «Формулы».
3. Найдите кнопку, название которой начинается на «Авто». Это зависит от выбранной в Excel опции по умолчанию («Автосумма», «Авточисло», «Автосмещ», «Автоиндекс»).
4. Нажмите на чёрную стрелочку под ней.
5. Выберите «МИН» (минимальное значение), «МАКС» (максимальное) или «СРЗНАЧ» (среднее).
6. В отмеченной клетке появится формула. Кликните на любую другую ячейку — она будет добавлена в функцию. «Растяните» рамку вокруг неё, чтобы охватить диапазон. Или щёлкайте по сетке с зажатой клавишей Ctrl, чтобы выделять по одному элементу.
7. Когда закончите, нажмите Enter. Результат отобразится в клетке.

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

ВПР по нескольким критериям со смещением строк (Формулы/Formulas)

​ Наша задача –​​Формула ищет значение из​​ с простыми диапазонами.​ВПР​ПРОСМОТР​ функция​ один вопрос: объясните,пож,конец​ можно использовать комбиннацию​mazayZR​ пивот забыл совсем.​ вхождения точного соответствия​ данных, поэтому нам​ выбору аргумента искомого​ этой функции.​ столбца​Основная таблица (Main table)​ имена покупателей, отличающиеся​

​ написать формулу, которая​​ ячейки A2 на​​ Например, когда Вы​. Вот пример формулы:​

​(LOOKUP) с функциями​​И​ ф-лы, а именно,СТРОКА(А1).Еще​

​ ПОИСКПОЗ и ИНДЕКС.​​: для текстовых моя​ ​ Ща покручу поверчу,​

​ «ИвановСыр» в получившемся​​ нужно поставить значение​

​Скачать последнюю версию​​Main_table​ содержит множество записей​ от указанного в​ найдёт сумму всех​​ листе​ вводите формулу в​ ​=SUM(VLOOKUP(lookup value, lookup range,​

​СУММ​​в формулах массива​

​ раз респект!​​В приложении объединение​ подойдет​ но хотелось бы​ массиве, функция​ ​«0»​

Когда можно использовать функцию «Сумма если»

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

• Если все числа больше нуля;
• Если в ячейках есть данные;
• Если число больше определенной цифры;
• И другие.

Вы можете сами задать условие этого подсчета. И это сэкономит вам очень много времени, так как Эксель буквально за вас посчитает, все что вам нужно сложить и не будет добавлять в расчёты те данные, которые вам складывать не нужно.

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

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

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

В примере показано, ячейка G4 содержит эту формулу:

Эта формула суммирует суммы в столбце D, когда значение в столбце C содержит «футболка»

Обратите внимание, что СУММЕСЛИ-это не регистр

Функция СУММЕСЛИ поддерживает подстановочные знаки. Звездочка (*) означает «один или более символов», а вопросительный знак (?) означает «любой один символ».

Эти шаблоны позволяют создавать такие критерии, как «начинается с», «кончается на», «содержит 3 символа» и так далее.

Нужно, чтобы соответствовали все элементы, которые содержат слово «футболка», критериям»*футболка*»

Обратите внимание, что вы должны заключить текст и подстановочные знаки в двойные кавычки («»)

Альтернатива с СУММЕСЛИМН

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

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

Выполняем другие вычисления, используя функцию ВПР в Excel

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

Вычисляем среднее:

Формула ищет значение из ячейки A2 на листе Lookup table и вычисляет среднее арифметическое значений, которые находятся на пересечении найденной строки и столбцов B, C и D.

Находим максимум:

Формула ищет значение из ячейки A2 на листе Lookup table и возвращает максимальное из значений, которые находятся на пересечении найденной строки и столбцов B, C и D.

Находим минимум:

Формула ищет значение из ячейки A2 на листе Lookup table и возвращает минимальное из значений, которые находятся на пересечении найденной строки и столбцов B, C и D.

Вычисляем % от суммы:

Формула ищет значение из ячейки A2 на листе Lookup table, затем суммирует значения, которые находятся на пересечении найденной строки и столбцов B, C и D, и лишь затем вычисляет 30% от суммы.

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

ВПР и СУММЕСЛИ – находим и суммируем значения, удовлетворяющие определённому критерию

Функция СУММЕСЛИ (SUMIF) в Excel похожа на СУММ (SUM), которую мы только что разбирали, поскольку она тоже суммирует значения. Разница лишь в том, что СУММЕСЛИ суммирует только те значения, которые удовлетворяют заданному Вами критерию. Например, простейшая формула с СУММЕСЛИ:

– суммирует все значения ячеек в диапазоне A2:A10, которые больше 10.

Очень просто, правда? А теперь давайте рассмотрим немного более сложный пример. Предположим, что у нас есть таблица, в которой перечислены имена продавцов и их номера ID (Lookup table). Кроме этого, есть ещё одна таблица, в которой те же ID связаны с данными о продажах (Main table). Наша задача – найти сумму продаж для заданного продавца. Здесь есть 2 отягчающих обстоятельства:

• Основная таблица (Main table) содержит множество записей для одного ID в случайном порядке.
• Вы не можете добавить столбец с именами продавцов к основной таблице.

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

Перед тем, как мы начнём, позвольте напомнить Вам синтаксис функции СУММЕСЛИ (SUMIF):

• range (диапазон) – аргумент говорит сам за себя. Это просто диапазон ячеек, которые Вы хотите оценить заданным критерием.
• criteria (критерий) – условие, которое говорит формуле, какие значения суммировать. Может быть числом, ссылкой на ячейку, выражением или другой функцией Excel.
• sum_range (диапазон_суммирования) – необязательный, но очень важный для нас аргумент. Он определяет диапазон связанных ячеек, которые будут суммироваться. Если он не указан, Excel суммирует значения ячеек, в первом аргументе функции.

Собрав все воедино, давайте определим третий аргумент для нашей функции СУММЕСЛИ. Как Вы помните, мы хотим суммировать все продажи, совершённые определённым продавцом, чьё имя задано в ячейке F2 (смотрите рисунок, приведённый выше).

1. range (диапазон) – так как мы ищем по ID продавца, значениями этого аргумента будут значения в столбце B основной таблицы (Main table). Можно задать диапазон B:B (весь столбец) или, преобразовав данные в таблицу, использовать имя столбца Main_table.

2. criteria (критерий) – так как имена продавцов записаны в просматриваемой таблице (Lookup table), используем функцию ВПР для поиска ID, соответствующего заданному продавцу. Имя записано в ячейке F2, поэтому для поиска используем формулу:

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

3. sum_range (диапазон_суммирования) – это самая простая часть. Так как данные о продажах записаны в столбец C, который называется Sales, то мы просто запишем Main_table.

Всё, что Вам осталось сделать, это соединить части в одно целое, и формула СУММЕСЛИ+ВПР будет готова:

Как использовать функцию СУММЕСЛИМН в Excel

В этом уроке мы хотим добавить общее количество продаж манго, которые мы сделали, скажем, у Кевина Сахадео.

Щелкните ячейку, в которой вы хотите получить результат. Тип, в камере.

в СУММЕСЛИМН функцию, мы добавим Sum_range во-первых, это данные в Количество продаж столбцы, в которых указано, сколько товаров было приобретено каждым покупателем.

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

Добавить Диапазон критериев 1. Щелкните первые данные в столбце фруктов и введите ячейки B3: B10 или же Ctrl, Сдвиг и Клавиша со стрелкой вниз чтобы выделить ячейки.

Введите Mango как Критерии.

Теперь я добавлю Второй диапазон критериев. Перейти к Покупатель столбец и щелкните первые данные в таблице и Ctrl, Сдвиг, и Клавиша со стрелкой вниз выделить или напечатать A3: A10, запятая.

Мы собираемся добавить Второй критерий, который Кевин Сахадео. Убедитесь, что вы ввели точное написание в формуле и используете полное имя. Нажмите Войти вы увидите результат.

Другой вариант — нажать на Формулы на вкладке в группе библиотеки функций выберите Математика и триггер в раскрывающемся меню выберите СУММЕСЛИМН. Появится диалоговое окно аргумента функции.

в Sum_range поле ввода, тип потому что это количество продаж, которое мы хотим сложить.

в Criteria_ range тип поля ввода потому что это диапазон, в котором находятся фрукты, которые мы ищем.

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

в Criteria_ Range 2 тип поля ввода , потому что это второй диапазон, который вы ищите, его зовут Кевин Сахадео.

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

Затем нажмите ОК. Вы увидите результат.

Надеюсь, этот пост окажется для вас полезным.

Читать дальше: Как создать сводную таблицу и сводную диаграмму в Excel.

Многоразовый ВПР по двум и более критериям.

​Так как у нас​​ Excel​.​ для одного​ ячейке G1, ведь​ заказов заданного клиента.​Lookup table​ одну из ячеек,​ , FALSE))​(SUM) или​ не работает. Аналог​V​ ячеек сохранено.​vikttur​

​ формулой.​​ИНДЕКС​​. Далее, жмем на​​ искомое значение для​​Название функции ВПР расшифровывается,​criteria​​ID​ все мы знаем​Как Вы помните, нельзя​и возвращает максимальное​

​ Excel автоматически копирует​​=СУМ(ВПР(искомое_значение;таблица;;ЛОЖЬ))​​СУММЕСЛИ​

​ в формулах массива​​: формулу массива не​=INDEX(D$3:D$24;MATCH(I2;A$3:A$24;0)+5)​: Для разнообразия -​​mazayZR, формула жесть​

​возвращает «Кол-во» из​​ кнопку​ ячейки C3, это​ как «функция вертикального​(критерий) – так​в случайном порядке.​ – умножение на​

​ использовать функцию​​ из значений, которые​ её на весь​Как видите, мы использовали​(SUMIF). Примеры формул,​ — «*»​

​ обязательно вганять в​​EvgenyAgeev​ формула «не массива»​

​ Ща буду​​ соответствующей строки таблицы.​«OK»​«Картофель»​ просмотра». По-английски её​ как имена продавцов​Вы не можете добавить​ ноль дает ноль.​ВПР​ находятся на пересечении​ столбец, что экономит​ массив​ приведённые далее, помогут​Сергей показал правильнее​ диапазон (если это​

​: Спасибо!​​ (для удобства рассмотрения​ понимать​МИНУСЫ​.​, то и выделяем​ наименование звучит –​ записаны в просматриваемой​ столбец с именами​​Так как наша формула​, если искомое значение​ найденной строки и​​ несколько драгоценных секунд.​​ Вам понять, как​ — обрезание лишних​

​ нужно было бы​​jakim​ — без проверки​СердЖиГ​: Конкатенация массивов сильно​Как видим, цена картофеля​

​ соответствующее значение. Возвращаемся​​ VLOOKUP. Эта функция​ таблице (Lookup table),​

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

СУММЕСЛИ подходит, когда нужно суммировать значения ячеек с одним условием. Чтобы применить ее в действии, следуйте инструкции:

1. По свободной ячейке кликните левой клавишей мыши, чтобы выбрать место, где будет отображаться результат операции.
2. Кликните по иконке, расположенной рядом со строкой ввода формул, чтобы вызвать меню с функциями.
3. Выберите категорию «Математические» и найдите в списке функций «СУММЕСЛИ».
4. Кликните по кнопке «ОК», чтобы подтвердить свой выбор.
5. В появившемся окне «Аргументы функции» нужно заполнить обязательные поля: «Диапазон» и «Критерий». Например, нужно узнать, сколько килограмм фруктов продал продавец Иванов. Для этого выделите диапазон столбца «Продавец» полностью.
6. В строке «Критерий» пропишите фамилию продавца: «Иванов». Кавычки можно не указывать, программа вставляет их автоматически.
7. Поставьте курсор мыши в поле диапазона суммирования и выделите столбец «Количество (кг)» в таблице.
8. Нажмите на кнопку «ОК» внизу, чтобы произвести суммирование с условием, или из данной таблицы узнать, сколько килограмм фруктов продал Иванов.

В выделенной ячейке отобразится результат сложения, а в верхней строке – функция. Вычислить значение можно без вызова окна «Аргументы функции», если прописать все вручную. При этом базовая запись должна выглядеть так: =СУММЕСЛИ(X; «Условие»; Y), где X – это диапазон, а Y – диапазон суммирования. Значения не обязательно вписывать, их достаточно выделить с помощью мышки в таблице. Все элементы функции разделены знаком «;».

Использование СУММПРОИЗВ

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

=СУММПРОИЗВ((B2:B13=G1)*(C2:C13=G2);D2:D13)

Принцип работы данной формулы схож с принципом работы предыдущего подхода. Создается виртуальная таблица, в которой сравниваются значения ячеек G1 и G2 с диапазонами B2:B13 и С2:С13 соответственно. Далее оба этих массива сопоставляются и получается массив из единиц и нулей, где единица присваивается той строке, в которой оба условия совпали. Далее данный виртуальный массив перемножается на диапазон D2:D13. Так как в нашем виртуальном массиве будет только одна единица в шестой строке, формула вернёт результат 189.

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

ИТОГ

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

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