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. Выделите область, в которой следует присвоить имена строкам или столбцам. Выделенная область должна содержать строку или столбец заголовков.
Функции Excel
Microsoft Excel содержит так много функций, что даже опытные пользователи не могут похвастаться знанием всех функций Excel.
В следующей статье представлен список наиболее важных и полезных функций Excel.
Для удобства, функции Excel разделены на категории. Ниже представлены самые основные категории функций.
Математические функции
SUM
PRODUCT
SQRT
POWER
ABS
ROUND
ROUNDUP
ROUNDDOWN
INT
MOD
SUMIF
Статистические функции
MAX
MIN
AVERAGE
COUNT
COUNTA
COUNTIF
Текстовые функции
CONCATENATE
LEFT
RIGHT
LEN
UPPER
PROPER
LOWER
SEARCH
FIND
MID
SUBSTITUTE
REPLACE
TRIM
CODE
Логические функции
IF
AND
OR
IFERROR
Функции даты и времени
TODAY
MONTH
YEAR
WEEKDAY
Функции категории ссылки и массивы
VLOOKUP
HLOOKUP
LOOKUP
ROW
COLUMN
INDEX
MATCH
Функции категории проверка свойств и значений
ISERROR
ISNA
Хотите узнать какие функции есть в Excel и научиться правильно применять их в своей работе? Приходите на Тренинг Excel для продвинутых пользователей.
Функции Excel (по категориям)
Функции упорядочены по категориям в зависимости от функциональной области. Щелкните категорию, чтобы просмотреть относящиеся к ней функции. Вы также можете найти функцию, нажав CTRL+F и введя первые несколько букв ее названия или слово из описания. Чтобы просмотреть более подробные сведения о функции, щелкните ее название в первом столбце.
Ниже перечислены десять функций, которыми больше всего интересуются пользователи.
Эта функция используется для суммирования значений в ячейках.
Эта функция возвращает разные значения в зависимости от того, соблюдается ли условие. Вот видео об использовании функции ЕСЛИ.
Используйте эту функцию, когда нужно взять определенную строку или столбец и найти значение, находящееся в той же позиции во второй строке или столбце.
Эта функция используется для поиска данных в таблице или диапазоне по строкам. Например, можно найти фамилию сотрудника по его номеру или его номер телефона по фамилии (как в телефонной книге). Посмотрите это видео об использовании функции ВПР.
Данная функция применяется для поиска элемента в диапазоне ячеек с последующим выводом относительной позиции этого элемента в диапазоне. Например, если диапазон A1:A3 содержит значения 5, 7 и 38, то формула =MATCH(7,A1:A3,0) возвращает значение 2, поскольку элемент 7 является вторым в диапазоне.
Эта функция позволяет выбрать одно значение из списка, в котором может быть до 254 значений. Например, если первые семь значений — это дни недели, то функция ВЫБОР возвращает один из дней при использовании числа от 1 до 7 в качестве аргумента «номер_индекса».
Эта функция возвращает порядковый номер определенной даты. Эта функция особенно полезна в ситуациях, когда значения года, месяца и дня возвращаются формулами или ссылками на ячейки. Предположим, у вас есть лист с датами в формате, который Excel не распознает, например ГГГГММДД.
Функция РАЗНДАТ вычисляет количество дней, месяцев или лет между двумя датами.
Эта функция возвращает число дней между двумя датами.
Функции НАЙТИ и НАЙТИБ находят вхождение одной текстовой строки в другую. Они возвращают начальную позицию первой текстовой строки относительно первого знака второй.
Эта функция возвращает значение или ссылку на него из таблицы или диапазона.
Эти функции в Excel 2010 и более поздних версиях были заменены новыми функциями с повышенной точностью и именами, которые лучше отражают их назначение. Их по-прежнему можно использовать для совместимости с более ранними версиями Excel, однако если обратная совместимость не является необходимым условием, рекомендуется перейти на новые разновидности этих функций. Дополнительные сведения о новых функциях см. в статьях Статистические функции (справочник) и Математические и тригонометрические функции (справочник).
Если вы используете Excel 2007, эти функции можно найти в категориях Статистические и Математические на вкладке Формулы.
Возвращает интегральную функцию бета-распределения.
Возвращает обратную интегральную функцию указанного бета-распределения.
Возвращает отдельное значение вероятности биномиального распределения.
Возвращает одностороннюю вероятность распределения хи-квадрат.
Возвращает обратное значение односторонней вероятности распределения хи-квадрат.
Возвращает тест на независимость.
Соединяет несколько текстовых строк в одну строку.
Возвращает доверительный интервал для среднего значения по генеральной совокупности.
Возвращает ковариацию, среднее произведений парных отклонений.
Возвращает наименьшее значение, для которого интегральное биномиальное распределение меньше заданного значения или равно ему.
Возвращает экспоненциальное распределение.
Возвращает F-распределение вероятности.
Возвращает обратное значение для F-распределения вероятности.
Округляет число до ближайшего меньшего по модулю значения.
Вычисляет, или прогнозирует, будущее значение по существующим значениям.
Возвращает результат F-теста.
Возвращает обратное значение интегрального гамма-распределения.
Возвращает гипергеометрическое распределение.
Возвращает обратное значение интегрального логарифмического нормального распределения.
Возвращает интегральное логарифмическое нормальное распределение.
Возвращает значение моды набора данных.
Возвращает отрицательное биномиальное распределение.
Возвращает нормальное интегральное распределение.
Возвращает обратное значение нормального интегрального распределения.
Возвращает стандартное нормальное интегральное распределение.
Возвращает обратное значение стандартного нормального интегрального распределения.
Возвращает k-ю процентиль для значений диапазона.
Возвращает процентную норму значения в наборе данных.
Возвращает распределение Пуассона.
Возвращает квартиль набора данных.
Возвращает ранг числа в списке чисел.
Оценивает стандартное отклонение по выборке.
Вычисляет стандартное отклонение по генеральной совокупности.
Возвращает t-распределение Стьюдента.
Возвращает обратное t-распределение Стьюдента.
Возвращает вероятность, соответствующую проверке по критерию Стьюдента.
Оценивает дисперсию по выборке.
Вычисляет дисперсию по генеральной совокупности.
Возвращает распределение Вейбулла.
Возвращает одностороннее P-значение z-теста.
Возвращает свойство ключевого показателя эффективности (КПЭ) и отображает его имя в ячейке. КПЭ представляет собой количественную величину, такую как ежемесячная валовая прибыль или ежеквартальная текучесть кадров, используемой для контроля эффективности работы организации.
Возвращает элемент или кортеж из куба. Используется для проверки существования элемента или кортежа в кубе.
Возвращает значение свойства элемента из куба. Используется для подтверждения того, что имя элемента внутри куба существует, и для возвращения определенного свойства для этого элемента.
Возвращает n-й, или ранжированный, элемент в множестве. Используется для возвращения одного или нескольких элементов в множестве, например лучшего продавца или 10 лучших студентов.
Определяет вычисляемое множество элементов или кортежей, отправляя выражение для множества в куб на сервере, который создает множество, а затем возвращает его в Microsoft Excel.
Категории функций
Далее будет представлен список категорий функций с кратким описанием каждой из них.
Финансовые функции
Финансовые функции позволяют выполнять экономические вычисления, связанные с расчетом денежных средств. Функция ПЛТ, например, используется для вычисления величины ежемесячной выплаты по займу. Для вычисления необходимо задать следующие аргументы: сумму займа, процентную ставку займа и срок, на который он предоставлен.
Функции даты и времени
Функции этой категории позволяют работать со значениями даты и времени в формулах. Например, функция СЕГОДНЯ возвращает текущую дату (которая определена в системных часах компьютера).
Математические и тригонометрические функции
В эту категорию входят разнообразные функции, выполняющие математические и тригонометрические вычисления. Во всех тригонометрических функциях углы измеряются в радианах (а не в градусах). Для того чтобы преобразовать градусы в радианы, используйте функцию РАДИАНЫ.
Статистические функции
Функции этой категории предназначены для проведения статистического анализа диапазонов значений. С помощью статистических функций можно вычислить наименьшее значение набора значений, значение моды множества данных, стандартное отклонение выборки, дисперсию и т.д.
Функции ссылок и массивов
Функции этой категории используются для нахождения значений в списках и таблицах. Самый распространенный пример – поиск значения в таблице налоговых данных. Функция ПОИСКПОЗ может использоваться для определения налоговой ставки для конкретного уровня дохода.
Функции работы с базами данных
Функции этой категории применяются для вычисления суммы значений списка (также известного как база данных рабочего листа), который удовлетворяет определенным условиям. Предположим, у вас есть список, содержащий информацию о месячном объеме продаж. Функцию БСЧЁТ можно использовать для подсчета количества записей об объеме продаж в северном регионе, значение которых превышает 10000.
Текстовые функции
Текстовые функции позволяют манипулировать текстовыми строками в формулах. Функция ПСТР, например, возвращает заданное количество символов из строки текста, начиная с указанной позиции. Другие функции позволяют изменить регистр символов текста (например, преобразовать символы нижнего регистра в символы верхнего регистра).
Логические функции
Данная категория состоит всего из семи функций. Эти функции позволяют проверить заданное условие и вернуть логическое значение ИСТИНА или ЛОЖЬ. Функция ЕСЛИ также может оказаться полезной, поскольку она предоставляет формулам возможность принятия решений.
Информационные функции
Функции этой категории помогают определить тип данных, хранящихся в ячейке. Например, функция ЕТЕКСТ возвращает ИСТИНА, если значение является текстом. С помощью функции ЕПУСТО можно узнать, пуста ли ячейка. Функция ЯЧЕЙКА возвращает полезную информацию о конкретной ячейке.
Пользовательские функции
К этой категории относятся функции рабочего листа, созданные пользователем с помощью VBA. Пользовательские функции выполняются так же, как и встроенные функции Excel. Единственное отличие состоит в том, что описание аргументов этих функций не отображается в диалоговом окне Мастер функций.
Инженерные функции
Функции этой категории помогут вам при решении инженерных прикладных задач. Эти функции позволяют работать с комплексными числами, а также выполнять преобразование единиц одной системы исчисления или измерения в единицы исчисления или измерения другой системы.
Аналитические функции
Предназначены для манипулирования значениями, размещенными в кубе данных OLAP.
Функции совместимости
Это устаревшие статистические функции, помещенные в отдельную категорию для обеспечения совместимости с предыдущими версиями Excel. Полезны в ситуациях, когда нужно совместно работать с пользователями, на компьютерах которых установлены предыдущие версии.
Прочие категории функций
Кроме описанных выше категорий, в Excel существует еще четыре категории функций, которые не отображаются в диалоговом окне Мастер функций. Это такие категории, как Команды, Настраиваемые функции, Управление макросами и DDE/Внешние данные. Эти категории остались в наследство от старых версий Excel. Создав пользовательскую функцию, можно определить ее в одну из этих категорий. Кроме того, несложно отыскать и другие категории, созданные макросами.
Непостоянные функции
Некоторые функции Excel принадлежат особому классу так называемых непостоянных функций. Программа автоматически пересчитывает непостоянную функцию при пересчете рабочей книги даже в том случае, если формула, в которой эта функция используется, не задействована в пересчете.
Одной из непостоянных функций является функция СЛЧИС, которая возвращает новое случайное число при каждом пересчете рабочего листа. Кроме того, в Excel присутствуют следующие непостоянные функции:
ДВССЫЛ
ИНДЕКС
СМЕЩ
ЯЧЕЙКА
ОБЛАСТИ
СТРОКА
СТОЛБЕЦ
СЕЙЧАС
СЕГОДНЯ
Использование непостоянных функций имеет один побочный эффект: Excel будет всегда напоминать вам о сохранении рабочей книги перед ее закрытием (даже в том случае, если вы не вносили никаких изменений). Например, вы открыли рабочую книгу, в которой используются непостоянные функции, просмотрели ее (ничего при этом не меняя) и хотите закрыть файл. При этом Excel обязательно спросит, хотите ли вы сохранить рабочую книгу.
Чтобы отменить появление окна с этим вопросом, используйте режим вычислений вручную. Для этого откройте вкладку Формулы диалогового окна Параметров программы и в разделе Параметры вычислений установите переключатель Вручную и снимите флажок Пересчитывать книгу перед сохранением.