Сумма в месяц
Разберем обычный пример, как посчитать сумму в месяц. В принципе это достаточно нередко возникающая задачка, и решить ее можно обычный формулой.
Что желаем получить
В данном примере, у нас есть определенный спектр дат (B3:B11) и надлежащие сиим датам значения (С3:С11). В ячейках F3:F5 мы желаем получить суммы в месяц из спектра.
Для этого используем функцию «СУММЕСЛИМН», данная функция суммирует значения из обозначенного спектра, для которых производятся несколько критерий. Для нашей задачки таковым условием будет определение спектра от начала до конца месяца. Для упрощения создадим так что задать в спектре нужно лишь исходную дату, другими словами 1-ое число месяца, а дату окончания месяца получим из функции «КОНЕЦМЕСЯЦА».
Формула
Введем в ячейку F3, такую формулу:
Разберемся, что мы ввели
С3:С11 – спектр, из которого будут браться значения для суммирования.
B3:B11;»>=»&E3 – 1-ое условие, определяющее, что значение из спектра В3:В11 обязано быть больше либо равно значению в ячейке Е3. Потому что в ячейке Е3 указана дата начала месяца, то сиим условием определяем нижнюю границу спектра дат.
«<=»&КОНМЕСЯЦА(E3;0) – 2-ое условие указывающее верхнюю границу спектра дат. Значение обязано быть меньше либо равно чем итог выполнения функции «КОНЕЦМЕСЯЦА», датой для которой является 1-ый денек месяца из ячейки Е3.
Таковым образом, мы обусловили функцию просматривающую спектр дат и проверяющую заходит ли текущее значение ячейки в спектр дат определенных условием, если значение заходит, то оно суммируется в итог.
Делаем прекрасно
Наша формула работает верно, но не совершенно эстетично смотрятся значения месяцев, которые фактический представлены числом начала месяца. Наиболее приятно смотрелось бы, если заместо числа было просто заглавие месяца. Это просто создать, определив свой формат представления значений.
Откроем окно «Формат ячеек» и на вкладке «Число» выберем «(все форматы)». Посреди предопределенных форматов есть один формат наиболее – наименее нам пригодный «МММ.ГГ». В этом формате будет представлено сокращенное заглавие месяца и две крайние числа года.
Что бы не определять собственный формат, можно применять и этот предопределенный формат. Но если в таблице данных содержатся значения лишь за один год, выводить номер года нет ни какого смысла. Введем в поле «Тип» свой формат «ММММ» — четыре огромные буковкы М. Опосля что наши ячейки воспримут вид:
Exceltip
Блог о программке Microsoft Excel: приемы, хитрости, секреты, трюки
Функция СУММПРОИЗВ — как применять формулу СУММПРОИЗВ в Excel
В нынешней статье мы разглядим одну из самых фаворитных и массивных функций Excel – СУММПРОИЗВ. Вначале функция может показаться для вас не таковой полезной, как почти все обрисовывают. Но стоит начать работать со перечнями либо массивами данных, полезность СУММПРОИЗВ становится тривиальной.
СУММПРОИЗВ – синтаксис формулы
Синтаксис формулы Excel суммы произведений достаточно прост. Функция берет в качестве аргументов один либо наиболее массивов данных и возвращает сумму произведений соответственных значений.
Синтаксис функции смотрится последующим образом: =СУММПРОИЗВ(список1; список2 …)
Другими словами, если у вас есть массив данных <2;3;4>в одной колонке и <5;10;20>– в иной, и вы пользовались функцией СУММПРОИЗВ, вы получите итог 120 (поэтому что 2*5+3*10+4*20=120).
СУММПРОИЗВ и массивы
Давайте представим, что у вас имеется таблица продаж с колонками Имя торговца, Регион и Сумма продаж. И для вас нужно выяснить, на какую сумму сделал продаж тот либо другой агент. Это просто, можно пользоваться функцией СУММЕСЛИ и указать в качестве аспекта суммирования, нужное нам имя агента.
Вопросец достаточно просто решаем с одним условием, но что если количество критерий растет и нам нужно выяснить сумму продаж определенного агента в определенном регионе. У нас есть три пути:
- Пользоваться формулами массивов
- Пользоваться сводными таблицами
- Пользоваться формулой СУММЕСЛИМН
По сути у нас еще есть один, сокрытый путь, пользоваться СУММПРОИЗВ.
Внедрение СУММПРОИЗВ в формулах массива
Если представить, что данные находятся в спектре A2:C21, с именами агентов в колонке A, регионами – в колонке B и продажами – в колонке C, то формула СУММПРОИЗВ будет смотреться последующим образом:
Давайте разберемся, как работает формула:
- Часть формулы (- -(A2:A21="Агент Смит" отыскивает Агента Смита в спектре A2:A21 и возвращает массив с единицами и нулями (единица, если ячейка содержит Агента Смита, и ноль – если нет).
- Часть — -(B2:B21="Запад") делает тоже самое, лишь возвращает единицу, если ячейка содержит Запад.
- C2:C21 – просто возвращает массив с продажами
Если вы перемножите все три массива и потом просуммируете произведения, получиться разыскиваемый итог.
Послесловие
Функция СУММПРОИЗВ сумеет еще больше, когда вы поймете, как она работает. Данная статья лишь приоткрывает заавесь потаенны к данной формуле.
Для вас также могут быть увлекательны последующие статьи
11 объяснений
А вот вопросик таковой, 2-ой вариант который вы обрисовали повторяет формулу СУММЕСЛИМН, итак вот не сталкивались ли вы какая будет работать резвее? и где можно подробнее почитать про внедрение «- -» никогда ранее с сиим не сталкивался?
Сергей, совершенно точно, формула СУММЕСЛИМН будет работать резвее, потому что интегрированные формулы шустрее, чем пользовательские. Данный пример будет полезен читателям, которые употребляют старенькые версии Excel (до 2007), в которых данная функция не реализована.
По поводу второго вопросца на английских ресурсах о этом много написано, в любом поисковике введите «double dash excel», думаю, вы отыщите ответ на собственный вопросец.
Сергей, совершенно точно, формула СУММЕСЛИМН будет работать резвее, потому что интегрированные формулы шустрее, чем пользовательские
СУММПРОИЗВ — это пользовательская функция?
Тут я имею в виду, что функцию СУММПРОИЗ мы затачиваем под функционал СУММЕСЛИМН
Хороший денек.
Одно из преимуществ функции СУММПРОИЗ в отличии от СУММЕСЛИ — это работа с закрытым файлом, т.е. СУММПРОИЗ рассчитывает итог, используя данные из иной рабочей книжки, даже если она закрыта.
Все было отлично, пока длина спектра данных (список1; список2 …) не стала переменной. Решила обычное написание спектра поменять на заглавие колонки таблицы, к примеру, (- -(Табл1[Агент]=»Агент Смит»)….), где Табл1 — таблица в другом файле. Пока этот иной файл открыт, все работает. Но при закрытом файле хоть какой Update приводит СУММПРОИЗ к ошибке (Ref). Либо я что-то делаю некорректно?
Хороший денек, Лилия
К огорчению, это слабенькое пространство Excel. Он не способен растягивать динамические спектры с закрытых книжек. Есть предложение создавать все вычисления на стороне закрытой книжки, а в книжке, где это будет употребляться, помещать лишь ссылку на ячейку закрытой книжки
Спасибо Для вас большущее. Побольше для вас времени и положительных чувств и далее вести собственный веб-сайт!
Допустим у нас в компании посиживают «умники» и пишут слова с ошибками: Агент Смит, Агент Смид, Запад, Запат и т.д. Вопросец:
Как употребляются подстановочные знаки «*», «?» в функции «СУММПРОИЗВ»?
Когда пишу =СУММПРОИЗВ(- -(A2:A21=»Агент*»);- -(B2:B21=»Запа?»);C2:C21), итог=0
Заблаговременно спасибо!
Хорошего времени суток.
С помощью функции суммпроизводства пробую посчитать, столбце с датами. Пишу в условии последующее. =Сейчас()-4
Ошубку не выдает но не считает итог.
А если пишу <=Сейчас()-4 то считает верно по условию проводит расчет. Как верно записать условие подскажите пожалуйста?
Здрасти! Подскажите, как задать условие — если значение в ячейке $H$2=1, то ссылаться на столбец MATYEAR и возвращать 1, если MAT 17, если $H$2 равно не один, тогда ссылаться на примыкающий столбец. Писала условие через ЕСЛИ, но почему-либо не работает.
Вопросец по второму примеру. Можно ли выяснить через формулу, какие значения были применены в расчете? Допустим ответ вышел 200 через данные 1*1*198+1*1*2=200
Вопросец как можно по формуле выяснить значения какие значения были просуммированы?
12 нужных для работы с данными математических функций в Excel
Научитесь применять все прикладные инструменты из функционала MS Excel.
Microsoft Excel – одна из самых фаворитных и вседоступных программ для представителей различный специальностей. Сейчас мы разглядим, пожалуй, одну из самых применяемых групп формул – математические формулы.
Начнем с того, как отыскать их посреди остального функционала. Есть несколько путей того, как открыть перечень математических формул.
Самый обычный метод – надавить на клавишу «Формулы» на панели управления. Потом избрать из списка тип функций: «Математические».
Перед вами покажется выпадающий длиннющий перечень всех имеющихся операторов:
Рис.1 Перечень математических функций в Excel
Всего в Excel около 80 математических и тригонометрических функций. Мы разглядим не все, лишь самые всераспространенные из их, также обратим внимание на некие аспекты, о которых вы, может быть, не знали. Если в статье вы не отыскали подходящую для вас функцию, то скачивайте наш бесплатный гайд «Математические функции в Excel».
Для разминки вспомним самые обыкновенные формулы.
1. Формулы СУММ(), ПРОИЗВЕД()
Эти операции имеют родственную структуру и однообразный тип аргументов, потому мы их соединили в один блок. СУММ() служит для сложения данных в нескольких ячейках, ПРОИЗВЕД() – разумеется, для нахождения произведения.
Аргументами этих функций могут быть числа, спектры, ссылки на ячейку, в которой содержится числовое значение. Количество частей не быть может больше 30.
СУММ() и ПРОИЗВЕД() пропускают пустые ячейки, ячейки текстового формата и логические значения. Операторы заносят итог вычислений в отдельную, ранее выделенную курсором ячейку:
Рис.2 Применение функции СУММ()
Аналогично для формулы ПРОИЗВЕД():
Рис.3 Применение функции ПРОИЗВЕД()
2. Формула ЧАСТНОЕ()
Тоже одна из обычных операций в арифметике. В Excel производится тоже нетрудно: у функции ЧАСТНОЕ() есть два аргумента: делимое и делитель.
В выделенной ячейке выводится личное:
3. Формула СУММЕСЛИ()
Оператор СУММЕСЛИ() находит сумму чисел. Основное отличие данной для нас функции от СУММ() в том, что тут в качестве аргумента можно задавать условие (лишь одно), которое будет демонстрировать, какие значения будут применены в расчетах, а какие — нет.
В качестве критерий могут выступать неравенства со знаками больше, меньше либо не равно («>», «<», «< >»). Число, которое не соответствует введенному условию, не будет включен в суммирование.
На рисунке 5 изображено суммирование всех чисел, которые больше 0.
Оранжевым выделены те числа, которые будут включены в расчет функцией СУММЕСЛИ().
Другие числа просто будут игнорироваться:
Рис 5. Применение функции СУММЕСЛИ()
Не считая неизменных аргументов, существует к тому же доп – «Спектр суммирования». Он добавляется тогда, когда нужно просуммировать один спектр, а условия выбирать по другому спектру.
К примеру, необходимо посчитать общую стоимость всех проданных фруктов.
Для этого воспользуемся последующей формулой:
Рис. 6 Пример с функцией СУММЕСЛИ() с необязательным аргументом «Спектр суммирования
Другими словами поначалу пишем спектр, по которому проверяем условие, потом само ограничение и в конце спектр чисел, которые нужно суммировать. В примере на рисунке 6 выше, соответственно, все строчки из группы «Овощи» в расчет включены не будут.
4. Формулы ОКРУГЛ(), ОКРУГЛВВЕРХ(), ОКРУГЛВНИЗ()
Функция ОКРУГЛ() создана для округления значения до данного количества символов опосля запятой. В качестве первого аргумента выступают, как обычно, числа либо спектр ячеек, второго – разряд, до которого необходимо округлить число.
К примеру, округление значения до второго знака опосля запятой:
Рис.7 Применение функции ОКРУГЛ()
Если в качестве второго аргумента выступает 0, то число будет округляться до наиблежайшего целого:
Рис. 8 Применение функции ОКРУГЛ() до целого значения
2-ой аргумент быть может и отрицательным, тогда округление будет происходить до требуемого знака перед запятой:
9. Рис. Применение функции ОКРУГЛ(), когда 2-ой аргумент меньше 0
Если нужно округлить в сторону наименьшего либо большего по модулю числа употребляют функции ОКРУГЛВНИЗ(), ОКРУГЛВВЕРХ(), соответственно:
Рис.10 Применение функции ОКРУГЛВНИЗ()
Рис.11 Применение функции ОКРУГЛВВЕРХ()
Замечание: почти все могут решить, что функции округления никчемны, потому что можно просто убрать/добавить доп символ опосля запятой при помощи клавиш прирастить/уменьшить разрядность.
По сути, это не так.
Дело в том, что повышение либо уменьшение разрядности влияет лишь на «наружный вид» ячейки, другими словами на то, как мы число лицезреем.
Само число, при всем этом, не изменяется. Функции округления же стопроцентно меняют вид числа, убирая излишние разряды.
5. Формулы ОТБР(), ЦЕЛОЕ()
Эти функции весьма похожи на прошлые, но работают незначительно по-другому.
ОТБР() убирает все числа справа от запятой и у положительных, и у отрицательных чисел. На первом месте в скобках опосля оператора пишется значение, а на втором – разряд, опосля которого удалятся все знаки.
Если 2-ой аргумент пропущен, то по дефлоту ставится 0:
Рис.12 Применение функции ОТБР()
ЦЕЛОЕ() – функция, которая выдает в качестве результата меньшее целое число, стоящее перед аргументом:
Рис.13 Применение функции ЦЕЛОЕ()
На положительные числа операторы влияют практически идиентично, а вот на отрицательные – нет.
Функция ЦЕЛОЕ(-5,6) выдаст итог (-6), а ОТБР(-5,6;0) выдаст (-5), хотя в то же время для числа 5,3 итог обеих функций будет однообразный – число (5).
6. Формула ABS()
Математическая формула ABS() дозволяет получить число по модулю. Как обычно, аргументами оператора является число либо ссылка на ячейку.
Рис.14 Применение функции ABS()
Эту функцию комфортно применять, к примеру, когда нужно отыскать количество дней меж датами. Из школьной программки почти все знают, что необходимо из большего вычитать наименьшее.
Но что созодать если дана большая таблица, где тяжело найти, где какое значение? Тут нам помогает оператор ABS(), который переводит отрицательное число в положительное.
Рис.15 Применение функции ABS() в работе с датами
7. Формула КОРЕНЬ()
КОРЕНЬ() — достаточно легкая функция с одним аргументом (числом либо ссылкой на ячейку), которая находит квадратный корень числа:
Рис.16 Применение функции КОРЕНЬ()
Замечание. Для извлечения корня иной степени (не квадратного) можно воспользоваться функцией СТЕПЕНЬ().
8. Формула СТЕПЕНЬ()
Функция СТЕПЕНЬ() дозволяет возвести число в всякую степень, в том числе извлечь корень (другими словами возвести число в дробную степень).
К примеру, чтоб извлечь кубический корень из числа 8, нужно пользоваться формулой, как на рисунке 17.1.
Рис.17 Применение функции СТЕПЕНЬ()
Рис.17.1 Применение функции СТЕПЕНЬ() для извлечения кубического корня
Кроме математической функции СТЕПЕНЬ(), можно воспользоваться оператором «^», но он смотрится наименее чистоплотно в формулах.
Если для вас любопытно выяснить больше о остальных математических функциях, напишите о этом ниже в комментах. Записывайтесь на открытый онлайн-курс «Аналитика в Excel», если желаете научиться делать рутинную работу в программке резвее.
Научитесь применять все прикладные инструменты из функционала MS Excel.
