5.6 Категории функций MS Excel
В MS Excel используется более 100 функций, объединенных по категориям:
• Функции работы с базами данных можно использовать, если необходимо убедиться в том, что значения списка удовлетворяют условию. С их помощью, например, можно определить количество записей в таблице о продажах или извлечь те записи, в которых значение поля «Сумма» больше 1000, но меньше 2500.
• Функции работы с датой и временем позволяют анализировать и работать со значениями даты и времени в формулах. Например, если требуется использовать в формуле текущую дату, воспользуйтесь функцией СЕГОДНЯ , возвращающей текущую дату по системным часам.
• Инженерные функции служат для выполнения инженерного анализа. Это функции для работы с комплексными переменными, функции для преобразования чисел из одной системы счисления в другую (десятичную, шестнадцатиричную, восьмеричную, двоичную) и функции для преобразования величин из одной системы мер и весов в другую.
• Финансовые функции осуществляют такие типичные финансовые расчеты, как вычисление суммы платежа по ссуде, объем периодической выплаты по вложению или ссуде, стоимость вложения или ссуды по завершении всех отложенных платежей и т.д.
• Информационные функции предназначены для определения типа данных, хранимых в ячейке. Они проверяют выполнение какого-то условия и возвращают в зависимости от результата значение ИСТИНА или ЛОЖЬ . Так, если ячейка содержит четное значение, функция ЕЧЁТН возвращает значение ИСТИНА . Если в диапазоне функций имеется пустая ячейка, можно воспользоваться функцией
• Логические функции предназначены для проверки выполнения условия или для проверки нескольких условий. Так, функция ЕСЛИ позволяет определить, выполняется ли указанное условие, и возвращает одно значение, если условие истинно, и другое, — если оно ложно.
• Функции ссылки и автоподстановки осуществляют поиск в списках или таблицах. Например, для поиска значения в таблице используйте функцию ВПР , а для поиска положения значения в списке — функцию ПОИСКПОЗ .
• Арифметические и тригонометрические функции позволяют производить простые и сложные математические вычисления, например вычисление суммы диапазона ячеек, вычисление суммы ячеек диапазона, удовлетворяющих указанному условию, округление чисел и прочее.
• Статистические функции позволяют выполнять статистический анализ диапазонов данных. Например, можно провести прямую по группе значений, вычислить угол наклона и точку пересечения с осью Y и прочее.
• Функции обработки текста позволяют производить действия над строками текста, например изменить регистр или определить длину строки. Можно также объединить несколько строк в одну. Например, с помощью функций СЕГОДНЯ и ТЕКСТ можно создать сообщение, содержащее текущую дату и привести его к виду « дд-
ммм-гг» : = «Балансовый отчет от» &ТЕКСТ(СЕГОДНЯ(), «дд-мм-гг»)
Если необходимо выполнить сложные вычисления, воспользуйтесь возможностью создания своих собственных нестандартных функций. Их создать с помощью языка VBA .
5.7 Ошибки в формулах
При появлении сообщения Ошибка в формуле :
• Проверьте, одинаково ли количество открывающих и закрывающих скобок.
• Проверьте правильность использования оператора диапазона при ссылке на
• Проверьте, все ли необходимые аргументы введены для функций.
• Если первый символ в имени книги или листа не является буквой, необходимо заключить имя в одинарные кавычки.
• Проверьте, в каждой ли внешней ссылке указано имя книги и полный путь к
• Не изменяйте формат чисел, введенных в формулы. Например, даже если в формулу необходимо ввести 1000 р., то введите число 1000.
Ошибка #####. Ошибка появляется, когда вводимое числовое значение или результат выполнения формулы не умещается в ячейке. В этом случае можно увеличить ширину столбца путем перемещения границы, расположенной между заголовками столбцов. Кроме того, можно изменить формат числа ячейки.
Ошибка #ДЕЛ/0!. Ошибка появляется, когда в формуле делается попытка деления на ноль. Например, в качестве делителя используется ссылка на ячейку, содержащую нулевое или пустое значение (если операнд является пустой ячейкой, то ее содержимое интерпретируется как ноль), или в формуле содержится явное деление на ноль.
Ошибка #Н/Д. Значение ошибки #Н/Д является сокращением термина “Неопределенные Данные ”. Это значение помогает предотвратить использование ссылки на пустую ячейку. Введите в ячейки листа значение #Н/Д , если они должны содержать данные, но в настоящий момент эти данные отсутствуют. Формулы, ссылающиеся на эти ячейки, тоже будут возвращать значение #Н/Д вместо того, чтобы пытаться производить вычисления. Ошибка может возникнуть, если не заданы один или несколько аргументов стандартной или пользовательской функции, а также задан недопустимый аргумент.
Ошибка #ИМЯ?. Ошибка #ИМЯ? появляется, когда Excel не может распознать имя, используемое в формуле. Возможная причина:
• Используемое имя было удалено или не было определено.
• Имеется ошибка в написании имени.
• Имеется ошибка в написании имени функции.
• В формулу введен текст, не заключенный в двойные кавычки.
• В ссылке на диапазон ячеек пропущен знак двоеточия (:).
Ошибка #ПУСТО!. Ошибка #ПУСТО! появляется, когда задано пересечение двух областей, которые в действительности не имеют общих ячеек.
Ошибка #ССЫЛКА!. Ошибка #ССЫЛКА! появляется, когда используется недопустимая ссылка на ячейку. Возможно, ячейки, на которые ссылаются формулы, были удалены или в эти ячейки было помещено содержимое других скопированных ячеек.
Ошибка #ЗНАЧ!. Ошибка #ЗНАЧ! появляется, когда используется недопустимый тип аргумента или операнда. Например, вместо числового или логического значения введен текст, и MS Excel не может преобразовать его к нужному типу данных.
5.8 Использование имен
Рабочий лист может содержать заголовки, размещенные в начале столбца и слева от строки, с помощью которых описываются данные внутри листа. При ссылке на необходимые данные можно использовать эти заголовки в формуле, что делает ее более наглядной. Например, если таблица содержит количество проданной продукции в столбце «Продажа», а отдел, выполнивший торговую операцию, находится в строке «Отдел», то, создав формулу =Отдел Продажа, всегда можно определить количество проданной продукции заданным отделом. (Пробел между заголовками является оператором пересечения диапазонов , который предписывает формуле вернуть значения из ячейки, находящейся в пересечении строки «Отдел» и столбца «Продажа». )
Если данные не имеют заголовков или размещены на другом листе, можно создать имя, описывающее ячейку или диапазон ячеек. Использование имен может упростить понимание формулы. Например, =СУММ(Продано_в_первом_квартале) проще для понимания, чем формула =СУММ(Продажа!C20:C30). В этом примере имя Продано_в_первом_квартале представляет диапазон ячеек C20:C30 на листе «Продажа».
Имена можно использовать в любом листе книги. Например, если имя «Контракты» ссылается на группу ячеек A20:A30 в первом листе рабочей книги, то это имя можно применить в любом другом листе той же рабочей книги для ссылки на эту группу. По умолчанию имена являются абсолютными ссылками.
Для того чтобы присвоить имя ячейке или группе ячеек:
1. Выберите ячейку, группу ячеек или несмежный диапазон, которому необходимо присвоить имя.
2. Выберите поле имени, которое расположено слева в строке формул и введите
Присвоить ячейкам имена можно при помощи существующих заголовков строк и столбцов. Для этого:
1. Выделите область, в которой следует присвоить имена строкам или столбцам. Выделенная область должна содержать строку или столбец заголовков.
К какой категории относится функция сумм
Функция СУММ добавляет значения. Вы можете складывать отдельные значения, диапазоны ячеек, ссылки на ячейки или данные всех этих трех видов.
=СУММ(A2:A10) Добавляет значения в ячейки A2:10.
=СУММ(A2:A10;C2:C10) Добавляет значения в ячейки A2:10, а также ячейки C2:C10.
СУММ(число1;[число2];…)
Имя аргумента
Первое число для сложения. Это может быть число 4, ссылка на ячейку, например B6, или диапазон ячеек, например B2:B8.
Это второе число для сложения. Можно указать до 255 чисел.
Этот раздел содержит некоторые рекомендации по работе с функцией СУММ. Многие из этих рекомендаций можно применить и к другим функциям.
Метод =1+2 или =A+B. Вы можете ввести =1+2+3 или =A1+B1+C2 и получить абсолютно точные результаты, однако этот метод ненадежен по ряду причин.
Опечатки. Допустим, вы пытаетесь ввести много больших значений такого вида:
=14598,93+65437,90+78496,23
А теперь попробуйте проверить правильность записей. Гораздо проще поместить эти значения в отдельные ячейки и использовать их в формуле СУММ. Кроме того, значения в ячейках можно отформатировать, чтобы привести их к более наглядному виду, чем если бы они были в формуле.
Ошибки #ЗНАЧ!, если ячейки по ссылкам содержат текст вместо чисел
Допустим, вы используете формулу такого вида:
=A1+B1+C1 или =A1+A2+A3
Если ячейки, на которые указывают ссылки, содержат нечисловые (текстовые) значения, формула может вернуть ошибку #ЗНАЧ!. Функция СУММ пропускает текстовые значения и выдает сумму только числовых значений.
Ошибка #ССЫЛКА! при удалении строк или столбцов
При удалении строки или столбца формулы не обновляются: из них не исключаются удаленные значения, поэтому возвращается ошибка #ССЫЛКА!. Функция СУММ, в свою очередь, обновляется автоматически.
Формулы не обновляют ссылки при вставке строк или столбцов
При вставке строки или столбца формула не обновляется — в нее не включается добавленная строка, тогда как функция СУММ будет автоматически обновляться (пока вы не вышли за пределы диапазона, на который ссылается формула). Это особенно важно, когда вы рассчитываете, что формула обновится, но этого не происходит. В итоге ваши результаты остаются неполными, и этого можно не заметить.
Функция СУММ — отдельные ячейки или диапазоны.
Используя формулу такого вида:
=СУММ(A1;A2;A3;B1;B2;B3)
вы изначально закладываете в нее вероятность появления ошибок при вставке или удалении строк в указанном диапазоне по тем же причинам. Гораздо лучше использовать отдельные диапазоны, например:
=СУММ(A1:A3;B1:B3)
Такая формула будет обновляться при добавлении и удалении строк.
Мне нужно добавить, вычесть, умножить или поделить числа. Просмотрите серию учебных видео: Основные математические операции в Excel или Использование Microsoft Excel в качестве калькулятора.
Как уменьшить или увеличить число отображаемых десятичных знаков? Можно изменить числовой формат. Выберите соответствующую ячейку или соответствующий диапазон и нажмите клавиши CTRL+1, чтобы открыть диалоговое окно Формат ячеек, затем щелкните вкладку Число и выберите нужный формат, указав при этом нужное количество десятичных знаков.
Как добавить или вычесть значения времени? Есть несколько способов добавить или вычесть значения времени. Например, чтобы получить разницу между 8:00 и 12:00 для вычисления заработной платы, можно воспользоваться формулой =(«12:00»-«8:00»)*24, т. е. отнять время начала от времени окончания. Обратите внимание, что Excel вычисляет значения времени как часть дня, поэтому чтобы получить суммарное количество часов, необходимо умножить результат на 24. В первом примере используется формула =((B2-A2)+(D2-C2))*24 для вычисления количества часов от начала до окончания работы с учетом обеденного перерыва (всего 8,5 часов).
Если вам нужно просто добавить часы и минуты, вы можете просто вычислить сумму, не умножая ее на 24. Во втором примере используется формула =СУММ(A6:C6), так как здесь нужно просто посчитать общее количество часов и минут, затраченных на задания (5:36, т. е. 5 часов 36 минут).
Как получить разницу между датами? Как и значения времени, значения дат можно добавить или вычесть. Вот распространенный пример вычисления количества дней между датами. Для этого используется простая формула =B2-A2. При работе со значениями дат и времени важно помнить, что дата или время начала вычитается из даты или времени окончания.
Другие способы работы с датами описаны в статье Вычисление разности двух дат.
Как вычислить сумму только видимых ячеек? Иногда когда вы вручную скрываете строки или используете автофильтр, чтобы отображались только определенные данные, может понадобиться вычислить сумму только видимых ячеек. Для этого можно воспользоваться функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Если вы используете строку итогов в таблице Excel, любая функция, выбранная из раскрывающегося списка «Итог», автоматически вводится как промежуточный итог. Дополнительные сведения см. в статье Данные итогов в таблице Excel.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
10 популярных математических функций Microsoft Excel
Чаще всего среди доступных групп функций пользователи Экселя обращаются к математическим. С помощью них можно производить различные арифметические и алгебраические действия. Их часто используют при планировании и научных вычислениях. Узнаем, что представляет собой данная группа операторов в целом, и более подробно остановимся на самых популярных из них.
Применение математических функций
С помощью математических функций можно проводить различные расчеты. Они будут полезны студентам и школьникам, инженерам, ученым, бухгалтерам, планировщикам. В эту группу входят около 80 операторов. Мы же подробно остановимся на десяти самых популярных из них.
Открыть список математических формул можно несколькими путями. Проще всего запустить Мастер функций, нажав на кнопку «Вставить функцию», которая размещена слева от строки формул. При этом нужно предварительно выделить ячейку, куда будет выводиться результат обработки данных. Этот метод хорош тем, что его можно реализовать, находясь в любой вкладке.
Также можно запустить Мастер функций, перейдя во вкладку «Формулы». Там нужно нажать на кнопку «Вставить функцию», расположенную на самом левом краю ленты в блоке инструментов «Библиотека функций».
Существует и третий способ активации Мастера функций. Он осуществляется с помощью нажатия комбинации клавиш на клавиатуре Shift+F3.
После того, как пользователь произвел любое из вышеуказанных действий, открывается Мастер функций. Кликаем по окну в поле «Категория».
Открывается выпадающий список. Выбираем в нем позицию «Математические».
После этого в окне появляется список всех математических функций в Excel. Чтобы перейти к введению аргументов, выделяем конкретную из них и жмем на кнопку «OK».
Существует также способ выбора конкретного математического оператора без открытия главного окна Мастера функций. Для этого переходим в уже знакомую для нас вкладку «Формулы» и жмем на кнопку «Математические», расположенную на ленте в группе инструментов «Библиотека функций». Открывается список, из которого нужно выбрать требуемую формулу для решения конкретной задачи, после чего откроется окно её аргументов.
Правда, нужно заметить, что в этом списке представлены не все формулы математической группы, хотя и большинство из них. Если вы не найдете нужного оператора, то следует кликнуть по пункту «Вставить функцию…» в самом низу списка, после чего откроется уже знакомый нам Мастер функций.
Наиболее часто используется функция СУММ. Этот оператор предназначен для сложения данных в нескольких ячейках. Хотя его можно использовать и для обычного суммирования чисел. Синтаксис, который можно применять при ручном вводе, выглядит следующим образом:
В окне аргументов в поля следует вводить ссылки на ячейки с данными или на диапазоны. Оператор складывает содержимое и выводит общую сумму в отдельную ячейку.
СУММЕСЛИ
Оператор СУММЕСЛИ также подсчитывает общую сумму чисел в ячейках. Но, в отличие от предыдущей функции, в данном операторе можно задать условие, которое будет определять, какие именно значения участвуют в расчете, а какие нет. При указании условия можно использовать знаки «>» («больше»), «<» («меньше»), «< >» («не равно»). То есть, число, которое не соответствует заданному условию, во втором аргументе при подсчете суммы в расчет не берется. Кроме того, существует дополнительный аргумент «Диапазон суммирования», но он не является обязательным. Данная операция имеет следующий синтаксис:
ОКРУГЛ
Как можно понять из названия функции ОКРУГЛ, служит она для округления чисел. Первым аргументом данного оператора является число или ссылка на ячейку, в которой содержится числовой элемент. В отличие от большинства других функций, у этой диапазон значением выступать не может. Вторым аргументом является количество десятичных знаков, до которых нужно произвести округление. Округления проводится по общематематическим правилам, то есть, к ближайшему по модулю числу. Синтаксис у этой формулы такой:
Кроме того, в Экселе существуют такие функции, как ОКРУГЛВВЕРХ и ОКРУГЛВНИЗ, которые соответственно округляют числа до ближайшего большего и меньшего по модулю.
ПРОИЗВЕД
Задачей оператора ПРИЗВЕД является умножение отдельных чисел или тех, которые расположены в ячейках листа. Аргументами этой функции являются ссылки на ячейки, в которых содержатся данные для перемножения. Всего может быть использовано до 255 таких ссылок. Результат умножения выводится в отдельную ячейку. Синтаксис данного оператора выглядит так:
С помощью математической формулы ABS производится расчет числа по модулю. У этого оператора один аргумент – «Число», то есть, ссылка на ячейку, содержащую числовые данные. Диапазон в роли аргумента выступать не может. Синтаксис имеет следующий вид:
СТЕПЕНЬ
Из названия понятно, что задачей оператора СТЕПЕНЬ является возведение числа в заданную степень. У данной функции два аргумента: «Число» и «Степень». Первый из них может быть указан в виде ссылки на ячейку, содержащую числовую величину. Второй аргумент указывается степень возведения. Из всего вышесказанного следует, что синтаксис этого оператора имеет следующий вид:
КОРЕНЬ
Задачей функции КОРЕНЬ является извлечение квадратного корня. Данный оператор имеет только один аргумент – «Число». В его роли может выступать ссылка на ячейку, содержащую данные. Синтаксис принимает такую форму:
СЛУЧМЕЖДУ
Довольно специфическая задача у формулы СЛУЧМЕЖДУ. Она состоит в том, чтобы выводить в указанную ячейку любое случайное число, находящееся между двумя заданными числами. Из описания функционала данного оператора понятно, что его аргументами является верхняя и нижняя границы интервала. Синтаксис у него такой:
ЧАСТНОЕ
Оператор ЧАСТНОЕ применяется для деления чисел. Но в результатах деления он выводит только четное число, округленное к меньшему по модулю. Аргументами этой формулы являются ссылки на ячейки, содержащие делимое и делитель. Синтаксис следующий:
РИМСКОЕ
Данная функция позволяет преобразовать арабские числа, которыми по умолчанию оперирует Excel, в римские. У этого оператора два аргумента: ссылка на ячейку с преобразуемым числом и форма. Второй аргумент не является обязательным. Синтаксис имеет следующий вид:
Выше были описаны только наиболее популярные математические функции Эксель. Они помогают в значительной мере упростить различные вычисления в данной программе. При помощи этих формул можно выполнять как простейшие арифметические действия, так и более сложные вычисления. Особенно они помогают в тех случаях, когда нужно производить массовые расчеты.
Sample 35 Topic
Функция СУММ относится к категории: «Математические». Нажмите комбинацию горячих клавиш SHIFT+F3 для вызова мастера функций, и вы быстро ее там найдете.
Использование этой функции существенно расширяет возможности процесса суммирования значений ячеек в программе Excel. На практике ее рассмотрим возможности и настройки при суммировании нескольких диапазонов.
КАК В ТАБЛИЦЕ EXCEL ПОСЧИТАТЬ СУММУ СТОЛБЦА?
Просуммируем значение ячеек A1, A2 и A3 с помощью функции суммирования. За одно и узнаем, для чего используется функция сумм.
После ввода чисел перейдите на ячейку A4. На закладке инструментов «Главная» выберите инструмент «Сумма» в разделе «Редактирование» (или нажмите комбинацию горячих клавиш ALT+=). 
Диапазон ячеек распознан автоматически. Адреса ссылок уже введенные в параметры (A1:A3). Пользователю остается только нажать Enter.
В результате в ячейке A4 отображается результат вычисления. Саму функцию и ее параметры можно увидеть в строке формул.
Примечание. Вместо использования инструмента «Сумма» на главной панели, можно в ячейку A4 сразу ввести функцию с параметрами вручную. Результат будет одинаковый.
ПОПРАВКА АВТОМАТИЧЕСКОГО РАСПОЗНАНИЯ ДИАПАЗОНОВ
При введении функции с помощью кнопки на панели инструментов или при использовании мастера функций (SHIFT+F3). Функция СУММ() относится к группе формул «Математические». Автоматически распознанные диапазоны не всегда являются подходящими для пользователя. Их можно при необходимости быстро и легко поправить.
Допустим, нам нужно просуммировать несколько диапазонов ячеек, как показано на рисунке:
Перейдите в ячейку D1 и выберите инструмент «Сумма».
Удерживая клавишу CTRL дополнительно выделите мышкой диапазон A2:B2 и ячейку A3.
После выделения диапазонов нажмите Enter и в ячейке D4 сразу отобразиться результат суммирования значений ячеек всех диапазонов.
Обратите внимание на синтаксис в параметрах функции при выделении нескольких диапазонов. Они разделены между собой (;).
В параметрах функции СУММ могут содержаться:
ссылки на отдельные ячейки;
ссылки на диапазоны ячеек как смежные, так и несмежные;
целые и дробные числа.
В параметрах функций все аргументы должны быть разделены точкой с запятой.
Для наглядного примера рассмотрим разные варианты суммирования значений ячеек, которые дают один и тот же результат. Для этого заполните ячейки A1, A2 и A3 числами 1, 2 и 3 соответственно. А диапазон ячеек B1:B5 заполните следующими формулами и функциями:
При любом варианте мы получаем один и тот же результат вычисления – число 11. Последовательно пройдите курсором от B1 и до B5. В каждой ячейке нажмите F2, чтобы увидеть цветную подсветку ссылок для более понятного анализа синтаксиса записи параметров функций.
ОДНОВРЕМЕННОЕ СУММИРОВАНИЕ СТОЛБЦОВ
В Excel можно одновременно просуммировать несколько смежных и несмежных столбцов.
Заполняете столбцы, так как показано на рисунке:
Выделите диапазон A1:B3 и удерживая клавишу CTRL так же выделите столбец D1:D3.
На панели «Главная» щелкните по инструменту «Сумма» (или нажмите ALT+=).
Под каждым столбцом автоматически добавилась функция СУММ(). Теперь в ячейках A4; B4 и D4 отображается результат суммирования каждого столбца. Это самый быстрый и удобный метод.
Примечание. Данная функция автоматически подставляет формат ячеек которые она суммирует.