Функции и excel описание и примеры: Функции и формулы в Excel с примерами

Содержание

Функция Excel СУММЕСЛИ (SUMIF) — примеры использования

Добрый день, уважаемые подписчики и посетители блога statanaliz.info. Совсем недавно мы разобрались с формулой ВПР, и сразу вдогонку я решил написать о другой очень полезной функции Excel – СУММЕСЛИ. Обе эти функции умеют «связывать» данные из разных источников (таблиц) по ключевому полю и при некоторых условиях являются взаимозаменяемыми. В то же время есть и существенные отличия в их назначении и использовании.

Если назначение ВПР в том, чтобы просто «подтянуть» данные из одного места Excel в другое, то СУММЕСЛИ используют, чтобы числовые данные просуммировать по заданному критерию.

Функцию СУММЕСЛИ можно успешно приспособить для решения самых различных задач. Поэтому мы в этой статье рассмотрим не 1 (один), а 2 (два) примера. Первый связан с суммированием по заданному критерию, второй – с «подтягиванием» данных, то есть в качестве альтернативы ВПР.

Пример суммирования с использованием функции СУММЕСЛИ

Этот пример можно считать классическим. Пусть есть таблица с данными о продажах некоторых товаров.

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

Первым делом выделяем ячейку, где будет подсчитана сумма. Далее вызываем Мастера функций. Это значок fx в строке формул. Далее ищем в списке функцию СУММЕСЛИ и нажимаем на нее. Открывается диалоговое окно, где для решения данной задачи нужно заполнить всего два (первые) поля из трех предложенных.

Поэтому я и назвал такой пример упрощенным. Почему 2 (два) из 3 (трех)? Потому что наш критерий находится в самом диапазоне суммирования.

В поле «Диапазон» указывается та область таблицы Excel, где находятся все исходные значения, из которых нужно что-то отобрать и затем сложить. Задается обычно с помощью мышки.

В поле «Критерий» указывается то условие, по которому формула будет проводить отбор. В нашем случае указываем «>70». Если не поставить кавычки, то они потом сами дорисуются.

Последнее поле «Дапазон_суммирования» не заполняем, так как он уже указан в первом поле.

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

Заполнив в Мастере функций необходимые поля, нажимаем на клавиатуре кнопку «Enter», либо в окошке Мастера «Ок». На месте вводимой функции должно появиться рассчитанное значение. В моем примере получилось 224шт. То есть суммарное значение проданных товаров в количестве более 70 штук составило 224шт. (это видно в нижнем левом углу окна Мастера еще до нажатия «ок»). Вот и все. Это был упрощенный пример, когда критерий и диапазон суммирования находятся в одном месте.

Теперь давайте рассмотрим, пример, когда критерий не совпадает с диапазоном суммирования. Такая ситуация встречается гораздо чаще. Рассмотрим те же условные данные. Пусть нам нужно узнать сумму не больше или меньше какого-то значения, а сумму конкретной группы товаров, допустим, группы Г.

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

Результатом будет сумма проданных товаров из группы Г – 153шт.

Итак, мы посмотрели, как рассчитать одну сумму по одному конкретному критерию. Однако чаще возникает задача, когда требуется рассчитать несколько сумм для нескольких критериев. Нет ничего проще! Например, нужно узнать суммы проданных товаров по каждой группе. То бишь интересует 4 (четыре) значения по 4-м (четырем) группам (А, Б, В и Г). Для этого обычно делается список групп в виде отдельной таблички. Понятное дело, что названия групп должны в точности совпадать с названиями групп в исходной таблице. Сразу добавим итоговую строчку, где сумма пока равна нулю.

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

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

Как видно, для первой группы А сумма проданных товаров составила 161шт (нижний левый угол рисунка). Теперь нажимаем энтер и протягиваем формулу вниз.

Все суммы рассчитались, а их общий итог равен 535, что совпадает с итогом в исходных данных. Значит, все значения просуммировались, ничего не пропустили.

Пример использования функции СУММЕСЛИ для сопоставления данных

Функцию СУММЕСЛИ можно использовать для связки данных. Действительно, если просуммировать одно значение, то получится само это значение. Короче, СУММЕСЛИ легко приспособить для связки данных как альтернативу функции ВПР. Зачем использовать СУММЕСЛИ, если существует ВПР? Поясняю. Во-первых, СУММЕСЛИ в отличие от ВПР нечувствительна к формату данных и не выдает ошибку там, где ее меньше всего ждешь; во-вторых, СУММЕСЛИ вместо ошибок из-за отсутствия значений по заданному критерию выдает 0 (нуль), что позволяет без лишних телодвижений подсчитывать итоги диапазона с формулой СУММЕСЛИ. Однако есть и один минус. Если в искомой таблице какой-либо критерий повторится, то соответствующие значения просуммируются, что не всегда есть «подтягивание». Лучше быть настороже. С другой стороны зачастую это и нужно – подтянуть значения в заданное место, а задублированные позиции при этом сложить. Нужно просто знать свойства функции СУММЕСЛИ и использовать согласно инструкции по эксплуатации.

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

Отчетливо видно, что одна позиция не подтянулась, и вместо числового значения выдается ошибка #Н/Д. Скорее всего, в феврале этот товар просто не продавался и поэтому он отсутствует в базе данных за февраль. Как следствие ошибка #Н/Д показывается и в сумме. Если позиций не много, то проблема не большая, достаточно вручную удалить ошибку и сумма будет корректно пересчитана. Однако количество строчек может измеряться сотнями, и рассчитывать на ручную корректировку не совсем верное решение. Теперь воспользуемся формулой СУММЕСЛИ вместо ВПР.

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

На сегодня все. Всех благ и до новых встреч на statanaliz.info.

Поделиться в социальных сетях:

1.Функции в Excel. Мастер функций

Федеральное
агентство по образованию РФ

Новосибирский
Государственный Университет
экономики
и управления

Кафедра
Экономической информатики

ПРИВАЛОВА
П.А.

Методические
указания по выполнению
лабораторной
работы

«Microsoft
Excel 2007. Использование функций.»

по
дисциплине «Информатика»

для
студентов 1 курса дневного
отделения
экономических специальностей

Новосибирск

2009

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

Любая
функция имеет вид:

ИМЯ
(СПИСОК АРГУМЕНТОВ)

ИМЯ-
это фиксированный набор символов,
выбираемый из списка функций;

СПИСОК
АРГУМЕНТОВ (или только один аргумент)-
это величины, над которыми функция
выполняет операции. Аргументами функции
могут быть адреса ячеек, константы,
формулы, а также другие функции. В случае,
когда аргументом является другая
функция, мы имеем дело со вложенной
функцией.

Например,
запись СУММ(С7:C10;D7:D10)
содержит функцию СУММ с двумя аргументами,
каждый из которых является диапазоном
ячеек, а запись КОРЕНЬ(ABS(А2))
содержит функцию КОРЕНЬ, аргументом
которой является функция ABC,
у которой в свою очередь аргументом
является адрес ячейки А2.

Пакет
Excel
предоставляет удобный инструмент ввода
функций-
Мастер функций. Инструмент
Мастер функций можно
вызвать:

командой
Вставить функцию во
вкладке Формулы
из группы
Библиотека функций
(Рис. 1)

Рис.1 Команда
Вставить функцию
во вкладке
Формулы

командой
Вставить функцию
в строке формул (Рис.2).

Рис.2 Команда
Вставить функцию
в строке формул

После
вызова
Мастера функций
появляется диалоговое окно (Рис.3):

Рис.3 Диалоговое
окно Мастера
функций

В
этом окне нужно выбрать категорию
функции и в списке ниже необходимую
функцию.

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

Рис. 4 Окно
математической функции КОРЕНЬ

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

Рис.5 Выбор
вложенной (внутренней) функции

Если
в появившемся списке отсутствует
требуемая функция, то следует активизировать
строку «Другие
функции…»
и работать далее с диалоговым окном
Мастер
функций,
как описано выше.

После
ввода аргументов вложенной функции не
следует щелкать на кнопке ОК, а нужно
активизировать (щелкнуть мышью) имя
соответствующей внешней функции
в
поле ввода строки формул. Т.е. нужно
перейти на окно Мастера
функций соответствующей
внешней функции. Так следует повторять
для всех вложенных функций. В формулах
может быть до 64 уровней вложения функций.

2.Математические функции.

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

2.1.Задание для самостоятельной работы 1.

Перейдите
на Лист 2 рабочей книги.

Переименуйте
Лист 2 рабочей книги в Примеры
функций.

Создайте
таблицу, представленную на рис. 6 ( !!!
Ячейки
С4:C7
не заполняйте — в них будут вводиться
расчетные формулы.).

Рис.6 Задание
для самостоятельной работы 1. Примеры
математических функций

В
ячейку C4
введите формулу расчета квадратного
корня из произведения содержимого
ячейки A4
на абсолютное значение (модуль) числа
из ячейки B4
(использовать функции КОРЕНЬ и АВS).
В
ячейку C5
введите формулу для возведения
содержимого ячейки A5
в степень числа, содержащегося в ячейке
B5
(использовать функцию СТЕПЕНЬ).
В
ячейку C6
введите формулу расчета абсолютного
значения целой части разности содержимого
ячеек A6
и B6
(использовать функции АВS
и ЦЕЛОЕ).
В
ячейку С7 введите формулу расчета
остатка от деления содержимого ячейки
A7
на содержимое ячейки B7
(использовать функцию ОСТАТ).
Сравните
результаты с данными, представленными
в графе Результат.

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

Excel для Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Дополнительно… Меньше

Функции — это предопределенные формулы, которые выполняют вычисления с использованием определенных значений, называемых аргументами, в определенном порядке или структуре. Функции могут использоваться для выполнения простых или сложных вычислений. Вы можете найти все функции Excel на вкладке «Формулы» на ленте:

org/ListItem»>
Синтаксис функции Excel
Следующий пример функции ОКРУГЛ, округляющей число в ячейке A10, иллюстрирует синтаксис функции.
1. Структура . Структура функции начинается со знака равенства (=), за которым следует имя функции, открывающая скобка, аргументы функции, разделенные запятыми, и закрывающая скобка.
2. Имя функции . Чтобы просмотреть список доступных функций, щелкните ячейку и нажмите SHIFT+F3 , чтобы открыть диалоговое окно Вставить функцию .
3. Аргументы . Аргументы могут быть числами, текстом, логическими значениями, такими как TRUE или FALSE , массивами, значениями ошибок, такими как #N/A, или ссылками на ячейки. Аргумент, который вы назначаете, должен давать допустимое значение для этого аргумента. Аргументы также могут быть константами, формулами или другими функциями.
4. Подсказка аргумента . При вводе функции появляется всплывающая подсказка с синтаксисом и аргументами. Например, введите =ROUND( , и появится всплывающая подсказка. Подсказки появляются только для встроенных функций.
Примечание. Не нужно вводить функции заглавными буквами, например =ОКРУГЛ, так как Excel автоматически сделает имя функции заглавным, как только вы нажмете клавишу ввода. Если вы неправильно напишете имя функции, например =СУММ(A1:A10) вместо =СУММ(A1:A10), Excel вернет ошибку #ИМЯ? ошибка.
Вход в функции Excel
Когда вы создаете формулу, содержащую функцию, вы можете использовать диалоговое окно Вставить функцию , чтобы помочь вам ввести функции рабочего листа. После выбора функции в диалоговом окне Вставить функцию Excel запустит мастер функций, который отображает имя функции, каждый из ее аргументов, описание функции и каждого аргумента, текущий результат функции и текущий результат всей формулы.
Чтобы упростить создание и редактирование формул и свести к минимуму опечатки и синтаксические ошибки, используйте Автозаполнение формул . После того как вы введете = (знак равенства) и начальные буквы функции, Excel отобразит динамический раскрывающийся список допустимых функций, аргументов и имен, соответствующих этим буквам. Затем вы можете выбрать один из раскрывающегося списка, и Excel введет его за вас.
Вложенные функции Excel
В некоторых случаях вам может понадобиться использовать функцию в качестве одного из аргументов другой функции. Например, следующая формула использует вложенную функцию СРЗНАЧ и сравнивает результат со значением 50.
1. Функции СРЗНАЧ и СУММ вложены в функцию ЕСЛИ.
Допустимые результаты Если вложенная функция используется в качестве аргумента, вложенная функция должна возвращать тот же тип значения, что и аргумент. Например, если аргумент возвращает значение ИСТИНА или ЛОЖЬ, вложенная функция должна вернуть значение ИСТИНА или ЛОЖЬ. Если функция не работает, Excel отображает ошибку #ЗНАЧ! значение ошибки.
Ограничения уровня вложенности Формула может содержать до семи уровней вложенности функций. Когда одна функция (назовем ее Функцией Б) используется в качестве аргумента в другой функции (назовем ее Функцией А), Функция Б действует как функция второго уровня. Например, функция СРЗНАЧ и функция СУММ являются функциями второго уровня, если они используются в качестве аргументов функции ЕСЛИ. Функция, вложенная во вложенную функцию AVERAGE, становится функцией третьего уровня и так далее.

Формулы и функции в Excel (в простых шагах)

Введите формулу | Редактировать формулу | Приоритет оператора | Копировать/вставить формулу | Функция вставки

Формула — это выражение, которое вычисляет значение ячейки. Функции являются предопределенными формулами и уже доступны в Excel .

Ячейка A3 ниже содержит формулу, которая суммирует значение ячейки A2 со значением ячейки A1.

Ячейка A3 ниже содержит функцию СУММ, которая вычисляет сумму диапазона A1:A2.

Введите формулу

Чтобы ввести формулу, выполните следующие шаги.

1. Выберите ячейку.

2. Чтобы Excel знал, что вы хотите ввести формулу, введите знак равенства (=).

3. Например, введите формулу A1+A2.

Совет: вместо ввода A1 и A2 просто выберите ячейку A1 и ячейку A2.

4. Измените значение ячейки A1 на 3.

Excel автоматически пересчитывает значение ячейки A3. Это одна из самых мощных функций Excel!

Редактирование формулы

При выборе ячейки Excel показывает значение или формулу ячейки в строке формул.

1. Чтобы изменить формулу, щелкните в строке формул и измените формулу.

2. Нажмите Enter.

Приоритет оператора

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

Сначала Excel выполняет умножение (A1 * A2). Затем Excel добавляет к этому результату значение ячейки A3.

Другой пример:

Сначала Excel вычисляет часть в скобках (A2+A3). Затем он умножает этот результат на значение ячейки A1.

Копирование/вставка формулы

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

1. Введите приведенную ниже формулу в ячейку A4.

2а. Выберите ячейку A4, щелкните правой кнопкой мыши и выберите «Копировать» (или нажмите CTRL + c). ..

… затем выберите ячейку B4, щелкните правой кнопкой мыши и выберите «Вставить» в разделе «Параметры вставки» (или нажмите CTRL + v).

2б. Вы также можете перетащить формулу в ячейку B4. Выберите ячейку A4, щелкните в правом нижнем углу ячейки A4 и перетащите ее в ячейку B4. Это намного проще и дает точно такой же результат!

Результат. Формула в ячейке B4 ссылается на значения в столбце B.

Вставить функцию

Все функции имеют одинаковую структуру. Например, СУММ(A1:A4). Имя этой функции SUM. Часть в скобках (аргументы) означает, что мы даем Excel диапазон A1: A4 в качестве входных данных. Эта функция складывает значения в ячейках A1, A2, A3 и A4. Нелегко запомнить, какую функцию и какие аргументы использовать для каждой задачи. К счастью, функция «Вставить функцию» в Excel поможет вам в этом.

Чтобы вставить функцию, выполните следующие шаги.

1. Выберите ячейку.

2. Нажмите кнопку «Вставить функцию».