Что такое функция в экселе: Функции Excel (по категориям)

Содержание

17 функций и 6 хитростей Excel, которые помогут упростить работу

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

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

Вы можете воспользоваться нашей памяткой, которая поможет не забыть, какой инструмент Excel для чего предназначен (таблица 1).

Таблица 1. 17 полезных функций Excel

Функция		Назначение	Пример
1	СУММ	Позволяет складывать отдельные значения, диапазоны ячеек, ссылки на ячейки или данные всех этих трёх видов.	=СУММ(Число 1;Число 2;…Число n) или =СУММ(А1;B1;C1) — сумма значений в ячейках
2	ПРОИЗВЕД	Перемножает все числа, переданные как аргументы, и возвращает произведение.	=ПРОИЗВЕД(Число 1; Число 2;…;Число n) или =ПРОИЗВЕД(А1;B1;C1) — произведение значений в ячейках
3	СРЗНАЧ	Считает среднее арифметическое числовых значений.	=СРЗНАЧ(Число 1; Число 2;. ..;Число n) или =СРЗНАЧ(А1;A2;A3)
4	ЕСЛИ	Позволяет выполнять логические сравнения значений и ожидаемых результатов (проверить, выполняются ли заданные условия в выбранном диапазоне таблицы). У функции возможны два результата (ИСТИНА и ЛОЖЬ). Функцию ЕСЛИ можно настроить сразу по нескольким критериям.	=ЕСЛИ(логическое выражение; [функция если истина]; [функция если ложь]) Например, если в таблице нужно определить значения меньше 100, то значение 96 будет отмечено функцией как истинное, а значение 125 — как ложное. =ЕСЛИ(A1>=100;»истина»;ЕСЛИ(A1<100;»ложь«))
5	СУММЕСЛИ	Суммирует значения, удовлетворяющие заданным условиям (например, все затраты из одной категории).	=СУММЕСЛИ(ячейки которые нужно проверить на условие; условие; какие ячейки складывать при удовлетворении условию)
6	СЧЁТЕСЛИ	Подсчитывает количество ячеек в диапазоне, удовлетворяющих заданному условию (например, сколько раз в списке повторяется то или иное название).	= СЧЕТЕСЛИ(ячейки которые надо подсчитывать, критерий по которым ячейку принимать в расчёт)
7	СРЗНАЧЕСЛИ	Рассчитывает условное среднее значение.	=СРЗНАЧЕСЛИ(ячейки которые нужно проверить на условие; условие; для каких ячеек рассчитывать среднее при удовлетворении условию)
8	МИН	Возвращает наименьшее значение в списке аргументов.	=МИН(Число 1; Число 2;…;Число n) или =МИН(A2:A6)
9	МАКС	Возвращает максимальное значение в списке аргументов (функция обратная МИН).	=МАКС(Число 1; Число 2;…;Число n) или =МАКС(A2:A6)
10	НАИМЕНЬШИЙ	Используется для получения минимального значения из заданного диапазона ячеек (возвращает k-ое наименьшее значение из массива данных).	В ячейках А1;A5 находятся числа 1;3;6;5;10. =НАИМЕНЬШИЙ (A1;A5) при разных k: k=1; результат =1 (первое наименьшее значение) k=2; результат=2 (второе наименьшее значение) k=3; результат=5 (третье наименьшее значение)
11	НАИБОЛЬШИЙ	Позволяет выбрать значение по его относительному местоположению (возвращает k-ое по величине значение из множества данных). Функцией можно воспользоваться для определения наилучшего, второго или третьего результатов.	В ячейках А1;A5 находятся числа 1;3;6;5;10. = НАИБОЛЬШИЙ (A1;A5) при разных k: k=1; результат = 10 (первое наибольшее значение) k=2; результат = 6 (второе наибольшее значение) k=3; результат = 5 (третье наибольшее значение)
12	СЖПРОБЕЛЫ	Позволяет избавиться от всех лишних пробелов в заданных ячейках (кроме уместных одинарных).	=СЖПРОБЕЛЫ(адрес ячейки)
13	ЛЕВСИМВ	Возвращает заданное количество знаков из указанной строки слева.	=ЛЕВСИМВ(адрес ячейки; количество знаков)
14	ПРАВСИМВ	Возвращает заданное количество знаков из указанной строки справа.	=ПРАВСИМВ(адрес ячейки; количество знаков)
15	ПСТР	Возвращает знаки из текстовой строки, начиная с указанной позиции.	=ПСТР(адрес ячейки; начальное число; число знаков)
16	ВПР	Позволяет находить данные по строкам в таблице или диапазоне (по фрагменту известных данных можно найти неизвестные) Например, по номеру товара можно найти его цену или по идентификатору найти имя сотрудника.	=ВПР(искомое значение; таблица; номер столбца; тип совпадения) Тип совпадения может быть приблизительным или точным. Для передачи точного значения в формуле нужно прописать 0 или ЛОЖЬ, для передачи приблизительных значений указывается 1 или ИСТИНА
17	Конкатенация	Позволяет объединить в одной ячейке данные из двух и более ячеек. Самый простой способ — вставить между адресами ячеек амперсанд (&).	=ячейка 1&ячейка 2…&ячейка n (=A1&A2&A3)

В Excel немало и других инструментов, и хитростей, зная которые можно существенно упростить себе работу. Вот шесть из них.

1. Чем полезно умное форматирование

Чтобы систематизировать данные, привести таблицы в понятный и презентабельный вид можно использовать условное форматирование (рисунок 1). Это целый массив способов обработки данных.

Методы основаны на цветном выделении ячеек в зависимости от различных критериев:

ранжирования по диапазону значений с помощью гистограмм\
сравнения с константой
различных значков

Благодаря динамике, данные корректируются при каждом изменении.

Рисунок 1. Условное форматирование

2. Как создать умную таблицу

Список данных в Excel можно преобразовать в умную таблицу (рисунок 2), у которой есть масса полезных функций:

шапка таблицы автоматически закрепляется при прокрутке, включаются кнопки фильтра для отбора и сортировки;
при дописывании новых столбцов и строк таблица автоматически растягивается;
появляется дополнительная вкладка «Конструктор» с дополнительными инструментами анализа и настроек;
введённые формулы автоматом копируются на весь столбец.

Рисунок 2. Умная таблица

3. Как визуализировать данные с помощью спарклайнов

Чтобы отобразить динамику ваших данных, можно использовать спарклайны. Это маленькие диаграммы, расположенные прямо в ячейках.

Чтобы создать спарклайн, нажмите «Вставка» → группа «Спарклайны» → кнопка «График» или «Гистограмма» (рисунок 3).

Рисунок 3. Спарклайны

4. Как перенести большую формулу

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

Замените знак «=» на «!». Формула превратится в символьную строку, которая перемещается без изменений. После того как формула полностью вписана в нужную ячейку, поменяйте знак обратно на «=».

5. Как ускорить и упростить работу с помощью Power Query

Представьте, что вам нужно составлять еженедельный отчёт. Вы готовите таблицы в Excel. А исходные вы получаете в виде CSV-файлов. Нужно каждый раз искать в них только необходимую вам информацию, вставлять данные в Excel, обновляя сводные данные и графики. Всё можно сделать намного проще, воспользовавшись Power Query.

Это технология подключения к данным. С помощью Power Query можно находить, загружать, объединять, преобразовывать, обновлять, и уточнять данные из различных источников.

Надстройка умеет собирать данные из фалов почти 40 различных форматов (например, TXT, XLSX, HTML, CSV, JSON, XML). Помогает менять регистр на правильный, приводить цифры к числовому формату, заполнять пробелы, исправлять заголовки таблиц, разделять текстовые фрагменты на столбцы и склеивать их снова в единый текст, удаляет пустые столбцы и строки, выполняет многие другие полезные операции.

Power Query представлена в двух вариантах:

отдельным модулем, доступным для скачивания с официального сайта Microsoft (для Excel 2010-13).
как сервисная функция в составе редактора (рисунок 4).

В большинстве последних версий Excel надстройка находится на вкладке «Данные» → Получить и преобразовать.

Рисунок 4. Power Query

6. Как восстановить несохранённые файлы

Даже если вы закрыли документ, забыв «согласиться» с сохранением, есть шанс восстановить данные. Вот алгоритм для разных версий Excel:

Excel 2010: «Файл» → «Последние» и найдите в правом нижнем углу экрана кнопку «Восстановить несохранённые книги».
Excel 2013: «Файл» → «Сведения» → «Управление версиями» → «Восстановить несохранённые книги».
Для последующих версий Excel: «Файл» → «Сведения» → «Управление книгой».

Здесь вы найдёте временные копии созданных, изменённых, но несохраненных книг.

Level 4 — Функции используемые редко — Справочник функций Excel

Level 3 Level 5

Level 4

Learn these words

44 words 0 ignored

Check the boxes below to ignore/unignore words, then click save at the bottom. Ignored words will never appear in any learning session.

All None

Ignore?

ДОЛЯГОДА

Возвращает долю года, которую составляет количество дней между начальной и конечной датами.

БДСУММ

Суммирует числа в поле для записей базы данных, удовлетворяющих условию.

Подсчитывает количество числовых ячеек в базе данных.

БСЧЁТА

Подсчитывает количество непустых ячеек в базе данных.

Находит максимальное значение среди выделенных записей базы данных.

Находит минимальное значение среди выделенных записей базы данных.

ДВ.В.ДЕС

Преобразует двоичное число в десятичное.

ДВ.В.ШЕСТН

Преобразует двоичное число в шестнадцатеричное.

ДЕС.В.ДВ

Преобразует десятичное число в двоичное.

ДЕС.В.ШЕСТН

Преобразует десятичное число в шестнадцатеричное.

ШЕСТН.В.ДВ

Преобразует шестнадцатеричное число в двоичное.

ШЕСТН.В.ДЕС

Преобразует шестнадцатеричное число в десятичное.

Вычисляет приведенную (к настоящему моменту) стоимость инвестиции.

РУБЛЬ.ДЕС

Преобразует цену в рублях, выраженную в виде дроби, в цену в рублях, выраженную десятичным числом.

РУБЛЬ.ДРОБЬ

Преобразует цену в рублях, выраженную десятичным числом, в цену в рублях, выраженную в виде дроби.

СТАВКА

Вычисляет процентную ставку по аннуитету за один период.

Вычисляет чистую приведенную стоимость инвестиции, основанной на серии периодических денежных потоков и ставке дисконтирования.

ИНФОРМ

Выдает информацию о текущей операционной среде.

Возвращает номер листа, на который имеется ссылка.

Возвращает количество листов в ссылке

ТИП.ОШИБКИ

Определяет номер, соответствующий одному из типов ошибок Microsoft Excel.

ЯЧЕЙКА

Определяет информацию о формате, местоположении или содержимом ячейки.

ИСКИЛИ

Возвращает логическое исключающее ИЛИ всех аргументов.

Вычисляет арккосинус числа.

Вычисляет косинус числа.

Вычисляет число e, возведенное в указанную степень.

Вычисляет натуральный логарифм числа.

Вычисляет логарифм числа по заданному основанию.

Вычисляет десятичный логарифм числа.

Вычисляет синус заданного угла.

Вычисляет тангенс числа.

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

Находит наибольший общий делитель.

Находит наименьшее общее кратное.

Вставляет число «пи».

СТЕПЕНЬ

Вычисляет результат возведения числа в степень.

НАКЛОН

Находит наклон линии линейной регрессии.

ОКРВНИЗ.ТОЧН

Округляет число в большую сторону (независимо от его знака) до ближайшего целого или кратного.

ТЕНДЕНЦИЯ

Находит значения в соответствии с линейным трендом.

ПЕЧСИМВ

Удаляет все непечатаемые знаки из текста.

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

СОВПАД

Проверяет идентичность двух текстов.

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

КОДИР.URL

Возвращает строку запроса, зашифрованную в виде URL-адреса.

Что такое функция в Excel

Главная / Что такое функция в Excel

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

Компоненты

Каждая функция состоит из двух основных компонентов. Короче говоря, каждая функция (за исключением нескольких) состоит из двух следующих элементов:

Имя функции
Аргументы

Позвольте мне показать вам пример. Давайте посмотрим на приведенную ниже функцию, которую мы вставили в ячейку A1.

Теперь, если вы посмотрите на строку формул, вы сможете понять структуру функции, разделив ее на две части: имя и аргументы.

Аргументы функции

Как я уже упоминал, в функции нужно указать входные значения, чтобы получить желаемый результат. Аргумент — это то значение, которое вам нужно указать. Если вы посмотрите на синтаксис функции, вы увидите, что в каждой функции есть набор аргументов для указания.

Ниже приведены типы аргументов:

Обязательный: Обязательный аргумент является обязательным для указания пользователем и без которого функция не может вычислить свой результат.
Необязательно: Если вы пропустите указание этих аргументов, это не остановит функцию для вычисления значения результата.
Нет аргументов: есть несколько функций (например, NOW), для которых не нужно указывать аргументы.

Как ВСТАВИТЬ функцию в Excel

Самый простой способ вставить функцию в ячейку Excel — ввести имя функции, которую вы хотите вставить, начиная со знака равенства.

Допустим, вы хотите вставить функцию СУММ:

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

Основные типы

Ниже приведены основные типы:

Текстовые функции : Если вы имеете дело с данными, содержащими текст, то ниже приведены некоторые функции, которые вам необходимо изучить для эффективной работы.
Функции даты : Даты являются одним из основных компонентов данных, которые вы используете каждый день, и помогают вам лучше анализировать ваши данные.
Функции времени : Как и даты, время также может быть в данных, и вы можете использовать функции времени для работы с данными, где у вас есть значения времени.
Логические функции : Логические функции могут помочь вам создать одну из самых полезных формул в электронной таблице.
Математические функции : Excel предназначен для расчетов и анализа, а также математических функций, и вы можете использовать эти функции, чтобы улучшить свои вычисления и анализ.
Статистические функции : Одна из лучших особенностей Excel — это множество статистических функций, которые можно использовать для легкого анализа данных.
Функции поиска : В Excel есть несколько специальных функций, которые могут помочь вам найти значение или конкретную информацию о ячейке или диапазоне ячеек.
Информационная функция : Это некоторые специальные функции, которые вы можете использовать для получения информации о введенных вами значениях.
Финансовые функции : Эти функции могут помочь вам в простых, но важных финансовых вычислениях.

100 лучших функций Excel

Об авторе

Пунит использует Excel со времен учебы в колледже. Он помог тысячам людей понять возможности электронных таблиц и изучить Microsoft Excel. Вы можете найти его в Интернете, в твиттере об Excel, на беговой дорожке, а иногда и в горах.

Изучите Excel (видео) Подключитесь к Puneet

Что такое функция в Excel?

Изучите 5 «основ» Excel, которые необходимо изучить

Начать работу с Excel очень просто. Подпишитесь на наш 5-дневный мини-курс, чтобы получить простых урока по использованию основных электронных таблиц.

Основы строк, столбцов и ячеек…
Как сортировать и фильтровать данные как про…
Плюс мы раскроем почему формулы и ссылки на ячейки так важны и как их использовать…

Имя

Адрес электронной почты

Отправляя эту информацию, вы соглашаетесь с политикой конфиденциальности и условиями обслуживания Deskbright.

Нет, спасибо! Верните меня к статье.

Структура таблиц Microsoft Excel — отличный способ поддерживать порядок. Но программа — это гораздо больше, чем просто ряд строк и столбцов, в которые вы можете вводить данные.

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

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

Давайте подробнее рассмотрим функции, чтобы понять, как они работают.

Структура функции

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

Во-первых, это имя функции . Это похоже на название рецепта, которое вы увидите вверху одной из страниц своей кулинарной книги. Это уникальный идентификатор, который сообщает Excel, что мы пытаемся приготовить.
Далее идут аргументы . Аргументы подобны ингредиентам рецепта. Это кусочки, которые вы вставляете, которые в конечном итоге объединятся, чтобы сделать что-то большее.
Наконец, вывод . Вывод функции такой же, как и вывод рецепта — это конечный продукт, который готов быть представленным пользователю (или, в случае рецепта, съеденным).

Написание функции в Excel

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

Основная структура функции следующая:

 =ИМЯ_ФУНКЦИИ(аргумент_1, аргумент_2, аргумент_3...) 
 Вывод: Вывод

После знака = мы пишем имя функции, чтобы сообщить Excel, какой рецепт мы хотите использовать. Затем мы используем знак открывающих скобок ((), чтобы сообщить программе, что мы собираемся передать ей список аргументов.

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

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

Практический пример

Давайте рассмотрим практический пример использования функции СУММ. Это один из наиболее часто используемых рецептов Excel — он принимает любое количество аргументов (все они должны быть числовыми) и выдает сумму этих аргументов. Формула для СУММ выглядит следующим образом:

 =СУММ(число_1, число_2...)

Напомним: название функции — СУММ. Аргументы: число_1, число_2 и столько дополнительных чисел, сколько вы хотите ввести (эта конкретная функция принимает неограниченное количество аргументов, точно так же, как рецепт, который становится все лучше и лучше, когда вы добавляете больше ингредиентов). Когда вы закончите писать функцию и нажмете Enter , Excel покажет вам результат.

Попробуйте ввести в ячейку пустой электронной таблицы следующее:

 =СУММ(3, 7) 
 Вывод: 10

Excel выводит 10, потому что СУММА 3 и 7 равна 10.

Вот еще один пример с еще большим количеством аргументов:

 =СУММ(1, 2, 3, 4, 5) 
 Результат: 15

Здесь Excel выводит 15, СУММУ 1, 2, 3, 4 и 5.

Бесконечные аргументы и необязательные аргументы

На этих страницах мы будем использовать несколько разных типы обозначений для обозначения особых случаев внутри функций:

Во-первых, некоторые функции, такие как SUM выше, теоретически имеют бесконечное число аргументов. Например, вы можете взять СУММУ бесконечного числа цифр. В подобных случаях мы используем три точки (…) для обозначения бесконечного числа дополнительных аргументов, например:

 =ИМЯ_ФУНКЦИИ(аргумент_1, аргумент_2, аргумент_3...)

Если у функции конечное число аргументов, вы не увидите это. .. в конце, например:

 =ИМЯ_ФУНКЦИИ(аргумент_1, аргумент_2 )

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

 =ИМЯ_ФУНКЦИИ(аргумент_1, аргумент_2 (необязательный))

В большинстве наших руководств по функциям мы объясним, почему что-то является необязательным и как вы можете его использовать.

Вот оно! Теперь, когда вы знаете, что такое функция, ознакомьтесь с нашими руководствами по некоторым логическим функциям Excel, чтобы освоить самый мощный инструмент Excel.

Что такое функция в экселе: Функции Excel (по категориям) — Служба поддержки Майкрософт