Excel посчитайте суммы по каждому городу из таблицы 1 и подставьте значения в таблицу 2
Давайте представим, что мы работаем в торговой компании. Руководитель поставил нам задачу посчитать сумму продаж за последние 15 дней. За конкретный промежуток времени.
Давайте рассмотрим как это сделать.
У нас есть таблица с данными по продажам за каждый день. Для выполнения задачи нам потребуется функция СУММЕСЛИМН.
Как работает функция СУММЕСЛИМН?
Функция СУММЕСЛИМН в Excel используется для суммирования значений по нескольким критериям.
Синтаксис функции выглядит так:
=СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2; условие2]; …)
- диапазон_суммирования – это диапазон данных, по которым будут вычисляться условия указанных вами критериев для суммирования данных;
- диапазон_условия1, условие1 – диапазон, в котором проверяется первое условие функции. Criteria_range1 (диапазон_условия1) и criteria1(условие1) составляют пару, определяющую, к какому диапазону применяется определенное условие при поиске. Соответствующие значения найденных в этом диапазоне ячеек суммируются в пределах аргумента sum_range (диапазон_суммирования).
- [диапазон_условия2], условие 2] – (опционально) – второй диапазон критериев, по которым будут вычисляться данные;
Формула для суммирования значений между двумя датами
Итак, как я уже писал выше, у нас есть таблица с данными продаж по каждому дню. Наша задача посчитать сумму продаж за период с 1 июня 2018 по 15 июня 2018 года.
Для суммирования продаж за указанный период нам потребуется в любой свободной ячейке использовать следующую формулу:
После ввода этой формулы, функция вернет значение 559 134₽. Это значение соответствует сумме продаж за период с 1 июня по 15 июня 2018 года.
Для проверки корректности расчета выделите диапазон ячеек с данными продаж, соответствущим этим датам и в правом нижнем углу вы увидите сумму значений.
Как работает эта формула
В нашей формуле мы использовали логические операторы в функции СУММЕСЛИМН , которые помогают нам суммировать данные в указанном диапазоне дат.
Давайте разложим формулу на составные части для понимания ее работы:
- Первым делом мы указываем диапазон с данными продаж (B2:B28), среди которого нам нужно выбрать какие значения мы будем суммировать
- Затем, мы указываем диапазон с данными, к которому будет применяться проверка на соответствие условию. В нашем случае это диапазон с датами (A2:A28)
- Следующим шагом мы задаем условие по отношению к диапазону с датами, по которому формула должна определить какие данные суммировать. Мы указали первое условие, что дата должна быть больше или равна 01.06.2018
- Заключительным шагом мы задаем второе условие к диапазону с датами (A2:A28), по которому формула должна суммировать данные за период меньший или равный 15.06.2018
Как результат, функция суммирует значения в диапазоне с 1 по 15 июня 2018 года.
Как суммировать значения между двумя динамическими датами
На примере выше мы рассмотрели как суммировать данные между двумя конкретными датами. Но что, если мы хотим суммировать данные, например, за последние 7 дней на ту дату, в которую мы открыли файл? Если мы не хотим каждый раз проставлять в формулу конкретные даты?
В этом случае нам поможет следующая формула:
Как работает эта формула
В формуле, указанной выше, мы используем функцию СЕГОДНЯ для автоматического вычисления текущей даты.
Здесь, в первом критерии для вычисления нужной даты мы указываем функции условие, при котором нужно суммировать данные за период равный или меньше текущей даты.
Во втором критерии мы указываем функции, что нужно суммировать данные больше или равные текущей дате минус 6 дней.
Если у вас остались вопросы по этому примеру оставляйте их в комментариях.
В данной статье, на простых примерах, описаны варианты использования функции ВПР MS Excel. Важные аспекты и возможные ошибки, которые возникают при использование данной функции. Функция ВПР в Excel.
Как вызвать функцию ВПР. Функция ВПР в Excel
В первую очередь разберемся, как вызвать данную функцию. Выбираем закладку Формулы. Находим кнопку Вставить функцию. И нажимаем ее. Так же, можно вызвать функцию ВПР, сочетанием клавиш Shift + F3.
Появляется диалоговое окно Вставка функции. В строке Поиск функции вводим ВПР. Нажимаем найти. По результатам поиска, в пункте Выберите функцию, появляется ВПР. Нажимаем на нее левой кнопкой мыши два раза или нажимаем ОК. Появляется непосредственно диалоговое окно функции ВПР – Аргументы функции.
Теперь перейдем непосредственно к вариантам применения функции ВПР.
Первый вариант использования функции ВПР.
Для примера возьмем две таблице. В одной Таблице №1 будет перечень с названиями конфет и будет указана их цена за кг. В другой, Таблица №2, тот же перечень, но с указанием их количества в кг. Наша задача добавить в Таблицу №2, в столбец Цена, цену конфет из Таблицы №1, чтобы в итоге получить стоимость. Названия конфет в разных таблицах находятся в разных местах, поэтому просто скопировать цену конфет с одной таблице в другую не получиться.
Перед тем, как вызвать функцию ВПР, выбираем нужную нам ячейку, в которой будет находиться наша формула функции и соответственно значение, которое мы хотим увидеть. В нашем случае это ячейка G3. Эта ячейка находиться в столбце Цена, Таблица №2. Функция ВПР позволит взять из Таблицы №1 цену Конфеты А и вставить эту цену в столбец Цена, Таблицы №2, напротив Конфеты А.
Вызываем функцию ВПР, как описано выше.
Аргументы функции. Функция ВПР в Excel.
Искомое_значение.
Значение поиска, которое должно быть найдена в указанном нами диапазоне, в строке Таблица. В нашем примере мы указываем Конфеты Ж (ячейка Е3, Таблица №2). Так как это значение идет первое в столбце Название конфет, Таблица №2. (Это не принципиально, но удобно). Это значение, которое будет искать наша функция в Таблице №1.
Что бы выбрать нужную нам ячейку с значением, достаточно просто стать курсором в строку Искомое_значение, а потом клацнуть левой кнопкой мыши, по нужной ячейке в таблице ( В нашем примере ячейка Е3).
Таблица.
Можно присвоить нашему диапазону имя, и прописать его в строке Таблица.
Как это сделать. Выбираем нужный нам диапазон. Таблица №1. Выбираем закладку Формулы, кнопка Задать имя. Нажимаем. Появляется диалоговое окно Создание имени. Пишем любое имя. Но нужно его запомнить. Например Конфеты. Нажимаем ОК.
В строке Таблица, вместо диапазона нужно будет ввести имя, которое мы присвоили – Конфеты
Номер_столбца.
Функция ВПР осуществляет поиск значение в самом левом столбце таблицы указанного диапазона поиска. Функция присваивает этому столбцу номер 1, по умолчанию. В нашем примере самый левый столбце, это Название конфет в Таблице №1. А в строке Номер_столбца, нам нужно указать, какой номер по порядку имеет столбец, из которого нужно перенести данные. В нашем примере это столбце Цена в Таблице №1. Он «второй» по порядку, если считать слева на право, от столбца Название конфет, Таблица №1. Поэтому в строку Номер_столбца мы пишем цифру 2. Если бы столбец Цена, Таблица №1 был бы расположен по порядку не на втором месте, а предположим на десятом, то мы соответственно указывали бы в строке Номер_столбца цифру 10.
Интервальный _просмотр.
В этой строке мы пишем цифру ноль «0». Это значит, что функция ВПР будет осуществлять поиск точных совпадений между значениями поиска (Искомое_значение) и значениями в крайнем левом столбце диапазона поиска (Таблица). В нашем примере поиск точных совпадений будет происходить между столбцом Название конфет, Таблица №1, и столбцом Название конфет в Таблице №2.
Если мы поставим цифру один «1», функция будет осуществлять поиск не точного совпадения, а приближенного к нашему критерию поиска.
Вот как это выглядит все вместе.
Протягиваем формулу по всему столбцу Цена в Таблице №2. Все цены перенесены с Таблице №1 в Таблицу №2.
Второй вариант использования функции ВПР.
У нас есть Таблица №1 и Таблица №2. Каждая таблица состоит из одного столбца. Для понимания алгоритма работы функции ВПР, в данном случае, таких простых таблиц достаточно. Столбцы содержат практически одинаковые данные. При этом, нам нужно сравнить их и узнать, какие данные есть в Таблице №2, но нет в Таблице №1.
Справа от Таблицы 2, в ячейку G3, вставляем функцию ВПР. Это расположение взято в качестве примера, можно использовать любой другой столбец и оформление.
В диалоговом окне, Аргументы функции прописываем следующие данные:
Искомое_значение. Это значение ячейки из Таблицы №2, наличие которой мы проверяем в Таблице №1. В нашем примере, это ячейка F3 (Значение 9).
Таблица. В данном случае мы указываем не диапазон всей таблицы, а только диапазон конкретного столбца, который мы сравниваем. Можно выделять столбец в таблице. А можно выделять весь столбец листа. В том случае, если в нем больше нет других данных. Вместо диапазона можно указать заданное имя столбца (Задаем имя).
Номер_столбца. Поскольку в таблице указан конкретный столбец, то здесь мы ставим цифру 1, так как искомое значение и данные, которые мы хотим перенести совпадают.
Интервальный _просмотр. Здесь ставим ноль «0», так как хотим, чтобы функция искала точные совпадения.
Можно проверить с точностью наоборот. И найти какие данные есть в Таблице №1 но нет в Таблице № 2.
Обратите внимание. Функция ВПР в Excel.
Функция ВПР осуществляет поиск значений (это значения, которые указаны в строке Искомое_значение) в первом (самом левом) столбец таблицы, диапазон которой указан в строке Таблица.
Это особенность, так же говорит нам о том, что значения, которые мы хотим перенести в нашу таблицу, должны быть справа, от столбца, в котором происходит поиск по заданному критерию.
Если в диапазоне Таблица указан один, конкретный столбец, то функция ВПР проверяет только его. И данное правило не обязательно.
Можно осуществлять поиск на разных Листах. Алгоритм работы такой же. Формула функции будет выгладить вот так: =ВПР(E6;Лист1!$B$1:$C$11;2;0). В нашем примере формулы функция ВПР расположена на Листе 2, а поиск значения и перенос данных с диапазона поиска происходит на Листе 1. Вместо диапазона можно использовать Заданное имя. Например Конфеты. Тогда формула функции будет выглядеть вот так: =ВПР(E6;Конфеты;2;0).
Возможные ошибки.
1. Цель работы: изучение информационной технологии использования встроенных вычислительных функций Excel для финансового анализа.
2.Приборы и оборудование: Персональный компьютер.
3 . Выполнение работы.
Задание 1. Создать таблицу финансовой сводки за неделю, произвести расчеты, построить диаграмму изменения финансового результата, произвести фильтрацию данных.
Порядок работы
Запустите редактор электронных таблиц Microsoft Excel и создайте новую электронную книгу
Рис 2. Создание стиля оформления шапки таблицы
2. Введите заголовок таблицы «Финансовая сводка за неделю (тыс. р.)», начиная с ячейки А1.
3. Для оформления шапки таблицы выделите ячейки на третьей строке A 3: D 3 и создайте стиль для оформления. Для этого выполните команду Формат/Стиль и в открывшемся окне Стиль (рис.2) наберите имя стиля «Шапка таблиц» и нажмите кнопку Изменить. В открывшемся окне на вкладке Выравнивание задайте Переносить по словам и выберите горизонтальное и вертикальное выравнивание — по центру, на вкладке Число укажите формат — Текстовый. После этого нажмите кнопку Добавить.
4. На третьей строке введите названия колонок таблицы — « Дни недели», «Доход», «Расход », « Финансовый результат», далее заполните таблицу исходными данными
. Для ввода дней недели наберите «Понедельник» и произведите автокопирование до «Воскресенья» (левой кнопкой мыши за маркер автозаполнения в правом нижнем углу ячейки).
5. Произведите расчеты в графе « Финансовый результат » последующей формуле: = Доход - Расход
для этого в ячейке D 4 наберите формулу = В4-С4.
Введите расчетную формулу только для расчета по строке «Понедельник», далее произведите автокопирование формулы (так как в графе «Расход» нет незаполненных данными ячеек, можно производить автокопирование двойным щелчком мыши по маркеру автозаполнения в правом нижнем углу ячейки).
6. Для ячеек с результатом расчетов задайте формат — «Денежный» с выделением отрицательных чисел красным цветом ( Формат/Ячейки/ вкладка Число/формат — Денежный / отрицательные числа — красные. Число десятичных знаков задайте равное 2).
Обратите внимание, как изменился цвет отрицательных значений финансового результата на красный
7. Рассчитайте средние значения Дохода и Расхода, пользуясь мастером функций (кнопка fx ). Функция «Среднее значение» (СРЗНАЧ) находится в разделе «Статистические». Для расчета функции СРЗНАЧ дохода установите курсор в соответствующей ячейке для расчета среднего значения ( В11 ), запустите мастер функций ( Вставка/Функция /категория — Статистические / СРЗНАЧ )
В качестве первого числа выделите группу ячеек с данными для расчета среднего значения — В4:В10.
Аналогично рассчитайте « Среднее значение » расхода.
8. В ячейке D 13 выполните расчет общего финансового результата (сумма по столбцу «Финансовый результат»). Для выполнения автосуммы удобно пользоваться кнопкой Автосуммирования на панели инструментов или функцией СУММ . В качестве первого числа выделите группу ячеек с данными для расчета суммы — D 4: D 10
9. Проведите форматирование заголовка таблицы. Для этого выделите интервал ячеек от А1 до D 1 , объедините их кнопкой панели инструментов Объединить и поместить в центре или командой меню Формат/Ячейки/ вкладка Выравнивание/отображение — Объединение ячеек . Задайте начертание шрифта — полужирное; цвет —по вашему усмотрению.
10. Произведите фильтрацию значений дохода, превышающих 4000 р.
В режиме фильтра в таблице видны только те данные, которые удовлетворяют некоторому критерию, при этом остальные строки скрыты
В этом режиме все операции форматирования, копирования, автозаполнения, автосуммирования
и т.д. применяются только к видимым ячейкам листа. Для установления режима фильтра установите курсор внутри таблицы и воспользуйтесь командой Данные/Фильтр/Автофильтр .
В заголовках полей появятся стрелки выпадающих списков. Щелкните по стрелке в заголовке поля, на которое будет наложено условие (в столбце «Доход»), и вы увидите список всех неповторяющихся значений этого поля. Выберите команду для фильтрации — Условие
В открывшемся окне Пользовательский автофильтр задайте условие « Больше 4000 » Произойдет отбор данных по заданному условию.
Проследите, как изменились вид таблицы и построенная диаграмма.
11. Сохраните созданную электронную книгу в своей папке.
Задание 2. Заполнить таблицу, произвести расчеты, выделить минимальную и максимальную суммы покупки, по результатам расчета построить круговую диаграмму суммы продаж.
Используйте созданный стиль (Формат/Стиль/Шапка таблиц).
Формулы для расчета: Сумма = Цена* Количество ; Всего = сумма значений колонки « Сумма».
. Для выделения максимального/минимального значений установите курсор в ячейке расчета, выберите встроенную функцию МАКС (МИН) из категории «Статистические», в качестве первого числа выделите диапазон ячеек значений столбца «Сумма» (ячейки ЕЗ:Е10 ).
Задание 3. Заполнить ведомость учета брака, произвести расчеты, выделить минимальную, максимальную и среднюю суммы брака, а также средний процент брака; произвести фильтрацию данных по условию процента брака < 8 %, построить график отфильтрованных значений изменения суммы брака по месяцам.
Формула для расчета: Сумма брака = Процент брака *Сумма зарплаты .
В колонке «Процент брака» установите процентный формат чисел (Формат/Ячейки/вкладка Число/формат — Процентный).
Задание 4. Заполнить таблицу анализа продаж, произвести расчеты, выделить минимальную и максимальную продажи (количество и сумму); произвести фильтрацию по цене, превышающей 9000 р., построить гистограмму отфильтрованных значений изменения выручки по видам продукции
Формулы для расчета:
Всего = Безналичные платежи + Наличные платежи; Выручка от продажи = Цена х Всего
Содержание отчета:
Ход и выполнение работы.
Представить результаты работы на компьютере.
Список литературы
1. Михеева Е.В. Информационные технологии в профессиональной деятельности: Учеб. пособие для сред. проф. образования – М.: Издательский центр «Академия», 2005.
2. Михеева Е.В. Практикум по информационным технологиям в профессиональной деятельности: Учеб. пособие для сред. проф.образования – М.: Издательский центр «Академия», 2005.
3. Шафрин Ю.А Информационные технологии: 2ч. Ч1: Офисная технология и информационные системы – М.: БИНОМ. Лаборатория знаний, 2003.
Изучим возможности для суммирования данных из ячеек с разных листов книги с помощью использования трехмерных ссылок в Excel.
В связи с этим часто можно столкнуться с задачей, когда нужно просуммировать данные из одних и тех же ячеек с нескольких листов. К примеру, подвести общий итог для всех филиалов или отделов компании (коих может быть очень много), посчитать сумму за год по всем месяцам, где каждая часть с данными расположена на отдельном листе книги.
К решению этой задачи можно подходить по-разному, рассмотрим несколько вариантов как мы это можно сделать — от медленного и ручного до быстрого и полуавтоматического.
Как сложить листы в Excel?
Вариант 1. Ручной ввод.
Начнем с одной стороны с медленного варианта, но с другой стороны с более понятного и наглядного способа.
Это совсем простой вариант. Еще один вариант записи можно аналогично реализовать с помощью функции СУММ:
В реализуемых нами вариантах в общем и целом все хорошо, за исключением большой ручной работы при прощелкивании всех нужных для суммирования ячеек.
Как это часто бывает если нужно посчитать сумму для двух или трех листов, то вышеописанный способ отлично подойдет. Но если усложнить задачу и представить подобный пример не для 3, а, например, для 30 листов, то процесс уже несколько затянется.
Поэтому перейдем к чуть более сложному на первый взгляд варианту, но гораздо более быстрому с точки зрения трудовых и временных затрат.
Вариант 2. Полуавтоматический ввод.
Повторно воспользуемся функцией СУММ, но в этот раз запишем ссылку на листы через двоеточие:
В этом случае общее количество суммируемых листов для нас уже не так важно, главное знать только начальный и конечный.
Поэтому запись для сложения 10 или 20 листов будет примерно такой же — ИмяПервогоЛиста:ИмяПоследнегоЛиста!Диапазон.
Чуть более подробно остановимся на логике формирования формулы. Напомню, что оператор двоеточие в Excel служит для объединения диапазона, который образует ссылку на все ячейки находящиеся между первой и последней (включая сами ячейки). Например, диапазон A1:A10 обозначает двумерную ссылку, в которую попадают все ячейки между первой (A1) и последней (A10).
При добавлении еще одной размерности в виде листов мы получаем так называемую трехмерную ссылку. К примеру, диапазон Лист1:Лист3!A1:A10 содержит все ячейки от Лист1!A1:A10 до Лист3!A1:A10.
Таким образом в нашем примере суммируются все ячейки A1 от Лист1 до Лист3. Но при применении такого способа суммирования есть некоторые тонкости, которые желательно нужно помнить, чтобы случайно не допустить ошибку, давайте подробнее про них поговорим.
Особенности трехмерных ссылок
Как мы уже говорили ранее, при применении трехмерных ссылок нужен только первый и последний лист, промежуточные листы между ними в записи никак не участвуют. Отсюда и возникают следующие тонкости:
- Добавление/копирование листа. Если добавить новый лист или скопировать уже существующий и вставить его между первым и последним (используемых в формуле), то он автоматически будет участвовать в расчете.
Т.е. сама формула не поменяется, а значение вполне может измениться, если на новом листе в тех же ячейках содержатся данные; - Удаление листа. Аналогичный случай примеру выше. При удалении листа формула не изменится, а расчет поменяется в зависимости от удаленных данных (если удалить начальный или конечный листы, то формула изменится, промежуточный — формула останется неизменной);
- Перемещение листа. При перемещении листа в другое место книги формула не изменится, но поменяется набор листов между ними (либо добавятся новые, либо удалятся текущие).
Если описание покажется несколько сложным, в этом случае попробуйте поэкспериментировать с формулой и перестановкой листов на конкретном примере и поймите по какому принципу она работает.
Использование трехмерных ссылок не ограничивается только функциями для сложения данных, а можно использовать большинство математических функций. Поэтому если стоит аналогичная задача для нахождения, к примеру, среднего арифметического или наибольшего с наименьшим, то без проблем применяйте.
Спасибо за внимание!
Если у вас есть мысли или вопросы по теме статьи — пишите и спрашивайте в комментариях.
Читайте также: