Встроенные функции excel: Все функции Excel | MS Office

Встроенные функции — презентация онлайн

Похожие презентации:

3D печать и 3D принтер

Системы менеджмента качества требования. Развитие стандарта ISO 9001

Операционная система. Назначение и основные функции

Adobe Photoshop

AutoCAD история и возможности

Microsoft Excel

Облачные технологии

Корпорация Microsoft и ее особенности

Веб-дизайн

Тема 2. Пакеты прикладных программ

ВСТРОЕННЫЕ
ФУНКЦИИ
Укажите адреса выделенных
диапазонов ячеек:
А3 : D3
В3 : В8
А2 : С5
10
131
9
Дан фрагмент ЭТ:
A
B
C
1
1
4 = A1* B2
2
2
5
3
3
6
D
= $В$3 – B1
4 =СУММ(А1:А3)+1 =СРЗНАЧ(В1:В3)
Урок
5
Перемена
Перечислите ячейки, в которых содержатся
• числа
А1, А2, А3, В1, В2, В3
• текст
В5, D5
• формулы
A4, B4, C1, D1
Дан фрагмент ЭТ:
A
B
C
1
1
4 = A1*
5 B2
2
2
5
3
3
6
D
= $В$3 –2B1
4 =СУММ(А1:А3)+1
=СРЗНАЧ(В1:В3)
7
5
5
Урок
Перемена
Чему будет равен результат
вычислений формул, содержащихся
в ячейках ЭТ?
Дан фрагмент ЭТ:
A
B
C
1
1
4 = A1* B2
2
2
5 = $В$3 – А2
3
3
6
4 =СУММ(А1:А3)+1 =СРЗНАЧ(В1:В3)
5
Урок
D
= $В$3 – B1
= A4* B5
Перемена
Какой вид примет формула из ячейки С1 при
копировании ее в ячейку С4?
Какой вид примет формула из ячейки D1 при
копировании ее в ячейку С2?
5
22
48
(5+22+48)/3=25
7+2+5,5+4=18,5
4+8/4=6
(7+22)/2=14,5

8.

Встроенные функции

Встроенные функции
Встроенные функции — заранее определённые формулы.
В электронных таблицах реализовано несколько сотен
встроенных функций, подразделяющихся на: математические,
статистические, логические, текстовые, финансовые и др.
Имя функции
СУММ / SUM
Действие функции
Суммирование аргументов
МИН / MIN
Определение наименьшего значения
из списка аргументов
МАКС / MAX
Определение наибольшего значения
из списка аргументов
СЧЁТ / COUNT
Подсчитывает количество чисел в
аргументе

10. Функции обработки диапазона (статистические функции)

ФУНКЦИЯ (АРГУМЕНТЫ)
СУММ(A1 : F7)
СРЗНАЧ(A1 : F7)
МАКС(A1 : F7)
МИН(A1 : F7)
Правила ввода функций
1. Выделить ячейку, где будет введена функция
2.
3.
4.
5.
Вставка — Функция ( или fx на панели инструментов)
Выбрать функцию из списка
В окне Число ввести диапазон исходных данных
Ок
Встроенные функции
Диалоговое окно позволяет упростить создание формул и
свести к минимуму количество опечаток и синтаксических
ошибок. При вводе функции в формулу диалоговое окно
отображает имя функции, все её аргументы, описание
функции и каждого из аргументов, текущий результат функции
и всей формулы.
Как найти сумму расходов?
A
1
2
3
4
5
B
Дом 38
краска
наличники
C
Дом 45
25 000р. цемент
30 000р.
17 000р. гравий
35 000р.
доски
Всего
D
15 000р.
122 000 р.
=СУММ(B2:B3)+СУММ(D2:D4)
=СУММ(B2:B3;D2:D4)
диапазоны
можно выделять
мышью
A
B
1
7
5
2 =СУММ(A1:B1) =СУММ(A1:A2)
=СУММ(A1:B2)
3
A
1
2
3
7
12
Чему будет равен результат
вычислений формул, содержащихся
в ячейках ЭТ?
B
5
19
43

English    
Русский
Правила

Встроенные функции и их использование

Похожие презентации:

3D печать и 3D принтер

Системы менеджмента качества требования. Развитие стандарта ISO 9001

Операционная система. Назначение и основные функции

Adobe Photoshop

AutoCAD история и возможности

Microsoft Excel

Облачные технологии

Корпорация Microsoft и ее особенности

Веб-дизайн

Тема 2. Пакеты прикладных программ

1. ВСТРОЕННЫЕ ФУНКЦИИ И ИХ ИСПОЛЬЗОВАНИЕ

ТАБЛИЧНЫЙ ПРОЦЕССОР. ОСНОВНЫЕ СВЕДЕНИЯ

2. Ключевые слова

МК
Ключевые слова
встроенные функции
аргумент функции
мастер функций
математические функции
статистические функции
логические функции
финансовые функции
текстовые функции

3. Общие сведения о функциях

МК
Общие сведения о функциях
!
Встроенная функция – это заранее написанная процедура преобразования данных.
G
32
H
25,371 =ОКРУГЛ(G32; 1)
32
H
25,4
Встроенная функция
Уменьшить количество знаков после запятой можно с
помощью кнопки уменьшения разрядности числа.
имеет
вызывается
,00
возвращает
,0
Microsoft
Excel
единственное
имя
с аргументами
значение
В чем отличие результата применения
функции
ОКРУГЛ и действия, назначенного кнопке уменьшения
разрядности?
Можно
ли считать
этинадва
Как правило,
имя
— сокращённое
название
Аргументы
функции
– число, текст,
выражение,
ссылка
дублирующими
друг друга
действиями?
производимого
действия.
ячейкуварианта
или
диапазон
ячеек,
результат
другой
функции.
?

4. Общие сведения о функциях

МК
Общие сведения о функциях
!
Встроенная функция – это заранее написанная процедура преобразования данных.
Мастер
функций
математические функции
статистические функции
логические функции
текстовые функции
инженерные функции
Microsoft
Excel
функции даты и времени
Имена
функций
отражают
выполняемое
действие.
Для каждой
функции есть
справочная
информация.

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

МК
Математические функции
Функция
Результат
ABS (число)
Модуль (абсолютная величина) числа.
SIN (число)
Синус числа (угол в радианах).
РАДИАНЫ (число)
Перевод из градусной меры угла в радианную.
ГРАДУСЫ (число)
Перевод из радианной меры угла в градусную.
LOG (число; основание)
Логарифм числа по заданному основанию.
ДЕС (число; основание)
Преобразует текстовое представление числа в
системе счисления с заданным основанием в
десятичное значение.
ОКРУГЛВВЕРХ
(число; количество
разрядов)
Округляет в большую сторону, для округления
до целого количество разрядов – 0.
Использование математических
функций на примере заданий ЕГЭ
ДЕС
LOG
ОКРУГЛВВЕРХ

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

МК
Статистические функции
Функция
Функция
Результат
Результат
Подсчитывает количество ячеек из указанного
СЧЕТЕСЛИ
(диапазон;
МАКС
(число1;
число2)
Максимальное значение.
диапазона, содержимое которых удовлетвокритерий)
ряет заданному
условию. значение.
МИН (число1; число2)
Минимальное
СУММЕСЛИ (диапазон;
Вычисляет сумму тех чисел из указанного
НАИБОЛЬШИЙ
(диапазон; число-k)
k-ое наибольшеезадан(наиусловие;
диапазона,Возвращает
которые удовлетворяют
НАИМЕНЬШИЙ
(диапазон; число-k)
меньшее) значение диапазона.
диапазон_суммирования)
ному условию.
Количество перестановок заданного числа
Возвращает порядковый номер
РАНГ. РВ
(число; диапазон;
ПЕРЕСТ (число1;
число2) порядок)
объектов, которые выбираются из общего
числа в диапазоне. (РВ — высший,
РАНГ.СР (число; диапазон; порядок)
числа объектов.
СР-средний).
Количество перестановок для заданного числа
ПЕРЕСТА
(число1; значение2)
число2) объектов (сСчет
повторами),
СЧЕТ
(значение1;
числовых которые
значений.выбираются
из общего числа объектов.
СУММ (число1; число2)
Сумма.
Использование статистических
ПЕРЕСТ
СРЗНАЧ (число1;
число2)
Среднее
значение. ПЕРЕСТА
функций
на примере
заданий ЕГЭ

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

МК
Логические функции
!
Функция, результатом которой является ИСТИНА или ЛОЖЬ,
называется логической.
Функция
Результат
ИСТИНА
Истина.
ЛОЖЬ
Ложь.
НЕ (лог. выражение)
Меняет значение логического выражения на противоположное.
И (лог.выр.1; лог.выр.2)
Операция конъюнкции.
ИЛИ (лог.выр.1; лог.выр.2)
Операция дизъюнкции.
ЕСЛИ (лог. выражение;
зн_истина; зн_ложь)
Возвращаемое значение определяется
логическим выражением.
Использование логических
функций на примере заданий ЕГЭ
Пример

8. Статистические функции

МК
Текстовые функции
Функция
Результат
ДЛСТР (текст)
Возвращает количество знаков в
текстовой строке.
ЗАМЕНИТЬ (старый_текст; нач_поз;
число_знаков; новый_текст)
Заменяет часть строки текста на
другую строку.
НАЙТИ (иск._текст; просм._текст;
нач_позиция)
Возвращает позицию
искомой строки.
ПОВТОР (текст; число_повторений)
Повторяет текст заданное число
раз.
ПСТР (текст; нач_позиция; число)
Возвращает заданное
знаков из строки текста.
Использование текстовых функций
начала
число
Пример

9. Статистические функции

МК
Инженерные функции
Функция
Результат
БИТ.И (число1; число2)
Возвращает
двух чисел.
побитовую
конъюнкцию
БИТ.ИЛИ (число1; число2)
Возвращает
двух чисел.
побитовую
дизъюнкцию
ВОСЬМ.В.ДВ (число;
разрядность)
Преобразует
двоичное.
восьмеричное
ДЕС.В.ВОСЬМ (число;
разрядность)
Преобразует десятичное
восьмеричное.
число
в
ШЕСТН.В.ДЕС (число)
Преобразует десятичное
восьмеричное.
число
в
Использование инженерных
функций на примере заданий ЕГЭ
Перевод в СС
число
БИТ.И
в

10. Статистические функции

МК
Функции даты и времени
Функция
Результат
СЕГОДНЯ ()
Возвращает текущую дату в формате даты
и времени.
ГОД (дата)
Возвращает год – целое число от 1900 до
9999.
МЕСЯЦ (дата)
Возвращает месяц – целое число от 1 до 12.
ДЕНЬ (дата)
Возвращает день – целое число от 1 до 12.
ДНИ (кон_дата; нач_дата)
Возвращает количество дней между двумя
датами.
?
Настроить формулы электронной таблицы для обратного отсчета
«До ЕГЭ осталось: _____ мес. _____ дней»

11. Логические функции

МК
Самое главное
Встроенные функции – заранее написанные процедуры преобразования
данных. Каждая функция имеет имя и результат. Аргументами функции
могут быть числа, текст, выражения, ссылки на ячейку или диапазон
ячеек, результат другой функции. Принято делить функции на категории
по их назначению.
Категория
Круг решаемых задач
математические
решение уравнений, построение графиков функций
статистические
наибольшее, наименьшее или среднее значение, количество и
сумму ячеек, содержащих заданную информацию, и т. д.
логические
вычисление логических операций
текстовые
определение длины текста, номера позиции первого
вхождения символа в текст и др.
финансовые
вычисление размеров выплат при кредитовании, банковских
процентов на вклады, для определения %-ой ставки и др.

12. Логические функции

МК
Информационные источники
• http://rorganise.esy.es/wp-content/uploads/2015/02/microsoft-excel-logo. jpg

English    
Русский
Правила

встроенных функций Excel | Реальная статистика с использованием Excel

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

Основные математические функции

0003

Рисунок 2 — Свойства Log и Exp

Комбинаторные функции

Рис. , Для значений выше 170 возвращается значение ошибки. COMBIN(2* n, n ) дает значение для n  до 514. PERMUT(2* n, n ) дает значение для n  до 134,

Функции округления

Рисунок 4 — Функции округления

Int ( x ) = Тунк ( x , 0), когда x ≥ 0 или x — это integer и int ( x или x — это x ≥ 0 или x . ) = TRUNC( x , 0) – 1 иначе.

Интеллектуальные функции

Рисунок 5-Функции с целым числом

Обратите внимание, что int ( x ) = Тунк ( x , 0), когда x ≥ 0 и int ( x ) = Trunc (0) x , 0) – 1, если x < 0.

Функции случайных чисел

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

Суммарные и родственные функции

Для примеров на рисунке 7 пусть R1 = {4, 6, 7, -3}, R2 = {6, 8, -5, 7}, R3 = {5, «», «A», 6 , -1}

Рисунок 7 – Сумма и связанные функции

Вы также можете подсчитать уникальные элементы в диапазоне данных в Excel. Следующая формула массива подсчитывает количество уникальных значений в диапазоне R1 (информацию о СЧЁТЕСЛИ см. ниже).

      =СУММ(1/СЧЁТЕСЛИ(R1,R1))

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

      =СУММ((R1<>«»)/СЧЁТЕСЛИ(R1,R1&»»))

Если заменить СУММ на СУММПРОИЗВ, результатом будет обычная формула, а не формула массива, поэтому вам нужно только нажмите Введите вместо Ctrl-Shft-Введите .

Чтобы подсчитать количество уникальных числовых ячеек в диапазоне R1, вы можете использовать следующую формулу массива:

      =СУММ(ЕСЛИ(ЧАСТОТА(R1,R1)>0,1))

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

Real Statistics Resource Pack предоставляет функцию COUNTU (R1), которая подсчитывает количество уникальных числовых ячеек в диапазоне R1, и COUNTAU (R1), которая подсчитывает количество уникальных ячеек в диапазоне R1 (см. Дополнительные функции Excel) .

Рисунок 8 содержит несколько примеров использования этих формул.

Рисунок 8 – Количество уникальных элементов в диапазоне

Матричные функции

Excel поддерживает следующие матричные функции. Эти функции более подробно описаны в разделе Матрицы и итерационные процедуры. 9Рисунок 9. Функции матрицы Пользователи предыдущих версий Excel могут вместо этого использовать функцию массива реальной статистики IDENTITY ( n ). Real Statistic Resource Pack также предоставляет функцию массива MPROD , где MPROD(R1, R2, R3) = MMULT(R1, MMULT(R2, R3)) и MPROD(R1, R2, R3, R4) = MMULT(MMULT( R1, R2), МУМНОЖ(R3, R4)).

Функции динамического массива

Функции на рис. 10 доступны только пользователям Excel 2021 и Excel 365.

Рисунок 10. Функции динамического массива

Кроме того, функции, показанные на рисунке 11, были добавлены в Excel 365 в 2022 году. В настоящее время эти функции проходят бета-тестирование. Дополнительные сведения см. в разделе Функции переформатирования Excel.

Рисунок 11 – Функции переформатирования

Функции на основе LAMBDA

Функции на рис. 12 доступны только пользователям Excel 365.

Рисунок 12 – Функции на основе LAMBDA -b3944572-255d-4efb-bb96-c6d

e188#bm19

Список функций Excel с примерами формул

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

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

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

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

• Текстовые / строковые функции
• Логические функции
• Математические функции
• Статистические функции
• Функции поиска и ссылок
• Финансовые функции
• Функции даты
• Функции времени
• Функции UDF для подсчета и суммирования ячеек по цвету

Текстовые функции Excel (строковые функции)

Существует множество функций Microsoft Excel для управления текстовыми строками. Вот наиболее важные из них:

Функция ТЕКСТ

ТЕКСТ(значение, формат_текст) используется для преобразования числа или даты в тестовую строку в указанном формате, где:

• Значение — числовое значение, которое необходимо преобразовать в текст.
• Format_text — желаемый формат.

Следующие формулы демонстрируют функцию ТЕКСТ в Excel в действии:

=ТЕКСТ(A1,"мм/дд/гггг") — преобразование даты в ячейке A1 в текстовую строку в традиционном для США формате даты, например «01. 01.2015» (месяц/день/год).

=ТЕКСТ(A1,"€#,##0.00") — преобразует число в A1 в текстовую строку валюты, такую ​​как «3,00€».

Примеры формул ТЕКСТ:

• Функция ТЕКСТ для преобразования даты в текстовый формат
• ТЕКСТОВЫЕ формулы для преобразования числа в текст

Функция СЦЕПИТЬ

СЦЕПИТЬ(текст1, [текст2], …) предназначена для соединения нескольких фрагментов текста или объединения значений из нескольких ячеек в одну ячейку. Аналогичного результата можно добиться с помощью оператора Excel и , как показано на следующем снимке экрана.

Вы можете найти множество других примеров формул в учебнике СЦЕПИТЬ в Excel: объединяйте текстовые строки, ячейки и столбцы.

Функция TRIM

TRIM(текст) удаляет начальные и конечные пробелы, а также лишние пробелы между словами. Где текст — это либо текстовая строка, либо ссылка на ячейку, содержащую текст, из которого вы хотите удалить пробелы. На следующем снимке экрана показан пример использования:

Дополнительные примеры формул см. в разделе 3 способа удаления пробелов между словами и числами в Excel.

Функция ПОДСТАВИТЬ

ПОДСТАВИТЬ(текст, старый_текст, новый_текст, [номер_экземпляра]) заменяет один набор символов другим в указанной ячейке или текстовой строке. Синтаксис функции ПОДСТАВИТЬ следующий:

• Текст — исходная текстовая строка или ссылка на ячейку, в которую нужно подставить определенные символы.
• Old_text — символы, которые вы хотите заменить.
• New_text — символы, которыми вы хотите заменить старый текст.
• Nth_appearance — необязательный параметр, указывающий, какое вхождение old_text вы хотите заменить новым_текстом. Если опущено, то каждое вхождение старого текста будет заменено новым текстом.

Например, следующая формула ПОДСТАВИТЬ заменяет все запятые в ячейке A1 точками с запятой:

=ПОДСТАВИТЬ(A2, ",", ";")

Примеры формул ЗАМЕНЫ:

4 в клетке

ЗНАЧ функция

ЗНАЧ(текст) — преобразует текстовую строку в число.

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

Примеры формул ЗНАЧЕНИЕ:

• Преобразование цифр в текстовом формате в числа
• Функция ЗНАЧЕНИЕ для преобразования текста в дату

Функция EXACT

EXACT(text1, text2) сравнивает две текстовые строки и возвращает TRUE, если оба значения совпадают, включая регистр, в противном случае FALSE.

Например, если A2 — это «яблоки», а B2 — это «яблоки», формула =ТОЧНО(A2, B2) вернет ЛОЖЬ, поскольку они не являются точным совпадением.

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

Функции для изменения регистра текста (ПРОПИСНОЙ, МНИЖНИЙ, ПРОПИСНОЙ)

Microsoft Excel предоставляет 3 текстовые функции для преобразования между ПРОПИСНЫМ, нижним и правильным регистром.

ПРОПИСНЫЕ(текст) — преобразует все символы указанной текстовой строки в верхний регистр.

LOWER(текст) — меняет все прописные буквы в текстовой строке на строчные.

Proper(text) — делает заглавной первую букву каждого слова и переводит все остальные буквы в строчные (точнее, делает заглавными буквы, которые следуют за любым символом, кроме буквы).

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

Дополнительные примеры формул для преобразования регистра текста можно найти в разделе Изменение регистра текста в Excel на ПРОПИСНОЙ, нижний или правильный.

Извлечение текстовых символов (LEFT, RIGHT, MID)

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

LEFT(текст, [число_символов]) — возвращает указанное количество символов с начала текстовой строки.

ПРАВО(текст,[число_символов]) — возвращает указанное количество символов с конца текстовой строки.

MID(text, start_num, num_chars) — возвращает определенное количество символов из текстовой строки, начиная с любой указанной вами позиции.

В этих функциях вы указываете следующие аргументы:

• Текст — текстовая строка или ссылка на ячейку, содержащую символы, которые вы хотите извлечь.
• Start_num — указывает, с чего начать (т. е. позицию первого символа, который вы хотите извлечь).
• Num_chars — количество символов, которые вы хотите извлечь.

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

Логические функции в Excel

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

Функции И, ИЛИ, XOR

И(логический1, [логический2], …) — возвращает ИСТИНА, если все аргументы оцениваются как ИСТИНА, в противном случае — ЛОЖЬ.

ИЛИ(лог1, [лог2], …) — возвращает ИСТИНА, если хотя бы один из аргументов ИСТИНА.

Исключающее ИЛИ(лог1, [лог2],…) — возвращает логических исключения или всех аргументов. Эта функция появилась в Excel 2013 и недоступна в более ранних версиях.

Примеры формул логических функций:

• И примеры формул
• Примеры формулы ИЛИ
• Примеры формул XOR

Функция НЕ

НЕ(логическое) — инвертирует значение своего аргумента, т.е. если логическое оценивается как ЛОЖЬ, функция НЕ возвращает ИСТИНА и наоборот.

Например, обе следующие формулы вернут ЛОЖЬ:

=НЕ(ИСТИНА)

=НЕ(2*2=4)

Дополнительные примеры функции НЕ см. в разделе Использование функции НЕ в Эксель.

Функция ЕСЛИ

Функцию ЕСЛИ в Excel иногда называют «условной функцией», поскольку она возвращает значение на основе заданного вами условия. Синтаксис IF следующий:

IF(логическая_проверка, [значение_если_истина], [значение_если_ложь])

Формула ЕСЛИ проверяет условия, выраженные в аргументе логическая_проверка , и возвращает одно значение ( значение_если_истина ), если условие выполняется, и другое значение ( значение_если_ложь ), если условие не выполняется.

Например, формула =ЕСЛИ(A1<>"", "хорошо", "плохо") возвращает «хорошо», если в ячейке A1 есть какое-либо значение, и «плохо» в противном случае.

А вот пример вложенной формулы ЕСЛИ, которая «расшифровывает» экзаменационный балл в ячейке А2:

=ЕСЛИ(A2>80, "Великолепно", ЕСЛИ(A2>50, "Хорошо", ЕСЛИ(A2>30, "Удовлетворительно", "Плохо")))

Примеры формул ЕСЛИ:

• Формулы ЕСЛИ для чисел, текста, дат, пустых ячеек
• Оператор IF с несколькими условиями И/ИЛИ
• Вложенные функции ЕСЛИ в Excel
• Функция ЕСЛИ в формулах массива
• Использование ЕСЛИ с другими функциями Excel

Функции ЕСЛИОШИБКА и ЕСЛИНА

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

ЕСЛИОШИБКА(значение, значение_если_ошибка) — проверяет, дает ли формула или выражение ошибку. Если это так, формула возвращает значение, указанное в аргументе value_if_error , в противном случае возвращается результат формулы. Эта функция обрабатывает все возможные ошибки Excel, включая ЗНАЧЕНИЕ, Н/Д, ИМЯ, ССЫЛКА, ЧИСЛО и другие. Он доступен в Excel 2007 и выше.

IFNA(value, value_if_na) — введен в Excel 2013, работает аналогично ЕСЛИОШИБКА, но обрабатывает только ошибки #N/A.

В следующих примерах показана простейшая формула ЕСЛИОШИБКА:

Примеры формул ЕСЛИОШИБКА / ЕСЛИ:

• Использование функций ЕСЛИОШИБКА и ЕСЛИНА в Excel
• Excel VLOOKUP с ЕСЛИОШИБКА / ЕОШИБКА

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

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

Нахождение суммы ячеек

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

Функция СУММ

СУММ(число1,[число2],…) возвращает сумму своих аргументов. Аргументы могут быть числами, ссылками на ячейки или числовыми значениями на основе формул.

Например, простейшая математическая формула =СУММ(A1:A3, 1) суммирует значения в ячейках A1, A2 и A3, а затем добавляет к результату 1.

Примеры формулы СУММ:

• Функция СУММ для суммирования столбца В Excel
• Excel СУММ в формулах массива

Функции СУММЕСЛИ и СУММЕСЛИМН (условная сумма)

Обе функции суммируют ячейки в заданном диапазоне, удовлетворяющие определенному условию. Разница в том, что СУММЕСЛИМН может оценивать только один критерий, а СУММЕСЛИМН, представленный в Excel 2007, позволяет использовать несколько критериев. Обратите внимание, что порядок аргументов в каждой функции разный:

СУММЕСЛИ(диапазон, критерии, [диапазон_суммы])

СУММЕСЛИМН(диапазон_суммы, диапазон_критериев1, диапазон_критериев1, [диапазон_критериев2, критерии2], …)

• диапазон / диапазон_критериев соответствующие критерии.
• критерии — условие, которое должно быть выполнено.
• sum_range — ячейки для суммирования при выполнении условия.

На следующем снимке экрана показано, как функции СУММЕСЛИ и СУММЕСЛИМН можно использовать с реальными данными:

Примеры формул СУММЕСЛИ и СУММЕСЛИМН:

• СУММЕСЛИ в Excel — формулы для условного суммирования ячеек
• Суммарные ячейки с несколькими критериями

Функция СУММПРОИЗВ

СУММПРОИЗВ(массив1,массив2, …) — одна из немногих функций Microsoft Excel, обрабатывающих массивы. Он умножает предоставленные компоненты массива и возвращает сумму произведений.

Суть функции СУММПРОИЗВ может быть трудной для понимания, и мы надеемся, что следующие примеры прольют свет на ее основные области применения.

Примеры формулы СУММПРОИЗВ:

• формула СУММПРОИЗВ для Vlookup с учетом регистра
• Подсчет дубликатов между двумя столбцами
• Суммарные ячейки с несколькими критериями

Генерация случайных чисел (СЛУЧАЙ и СЛУЧМЕЖДУ)

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

RAND() — возвращает случайное действительное (десятичное) число от 0 до 1.

RANDBETWEEN(нижний, верхний) — возвращает случайное целое число между нижними и верхними числами, которые вы укажете.

В следующем учебном пособии довольно хорошо объясняется суть каждой функции с примерами формул: Как использовать функции СЛЧИС и СЛУЧМЕЖДУ в Excel.

Функции округления

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

• ОКРУГЛ — округлить число до указанного количества цифр.
• ОКРУГЛВВЕРХ — округление числа в большую сторону до указанного количества цифр.
• ROUNDDOWN — округлить число в меньшую сторону до указанного количества цифр.
• ОКРУГЛ — округляет число в большую или меньшую сторону до указанного кратного.
• ЭТАЖ — округлить число в меньшую сторону до указанного кратного.
• ПОТОЛОК — округлить число до указанного кратного.
• INT — округлить число до ближайшего целого числа в меньшую сторону.
• TRUNC — обрезать число до указанного количества знаков после запятой.
• ЧЕТНЫЙ — округлить число до ближайшего четного целого числа.
• ODD — округлить число до ближайшего нечетного целого числа.

Получение остатка после деления (функция ОСТАТ)

ОСТАТ(число, делитель) возвращает остаток после деления аргумента число на делитель .

Функция очень удобна во многих случаях, например:

• Сумма значений в каждой второй или N ^-й -й строке
• Чередование цветов строк в Excel

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

Нахождение наибольшего, наименьшего и среднего значения

MIN(число1, [число2], …) — возвращает минимальное значение из списка аргументов.

МАКС(число1, [число2], …) — возвращает максимальное значение из списка аргументов

СРЗНАЧ(число1, [число2], …) — возвращает среднее значение аргументов.

МАЛЕНЬКИЙ(массив, k) — возвращает k-е наименьшее значение в массиве.

НАИБОЛЬШИЙ(массив, k) — возвращает k-е наибольшее значение в массиве.

На следующем снимке экрана показаны основные статистические функции в действии.

Примеры формул:

• Использование ЕСЛИ с функциями СРЗНАЧ, МИН и МАКС
• Вычисление среднего значения первых N элементов списка — Пример 1. Вычисление среднего значения первых N элементов списка
• ВПР со СРЗНАЧ, МАКС., МИН. (формулы массива)
• Сумма N наибольших/наименьших чисел в диапазоне (формула массива)

Подсчет ячеек

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

COUNT(значение1, [значение2], …) — возвращает количество числовых значений (числа и даты) в списке аргументов.

СЧЁТ(значение1, [значение2], …) — возвращает количество непустых ячеек в списке аргументов. Он подсчитывает ячейки, содержащие любую информацию, включая значения ошибок и пустые текстовые строки («»), возвращаемые другими формулами.

СЧИТАТЬПУСТОТЫ(диапазон) — подсчитывает количество пустых ячеек в указанном диапазоне. Ячейки с пустыми текстовыми строками («») также считаются пустыми ячейками.

СЧЁТЕСЛИ(диапазон, критерии) — подсчитывает количество ячеек в диапазоне, соответствующих заданным критериям.

СЧЕТЧИСЛО(диапазон_критериев1, диапазон_критериев1, [диапазон_критериев2, критерии2]…) — подсчитывает количество ячеек, соответствующих всем указанным критериям.

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

Примечание. Поскольку даты хранятся в Excel как числа, они также учитываются статистическими функциями Excel. Например, формула =СЧЁТЕСЛИ(A2:A9, ">5") подсчитывает дату в ячейке A8 на снимке экрана выше, поскольку во внутренней системе Excel она хранится как 42005.

Примеры формул для подсчета ячеек:

• Подсчет ячеек с текстом (функции COUNTA и COUNT)
• Подсчет пустых ячеек в Excel (COUNTBLANK)
• Подсчет пустых или не пустых ячеек (COUNTIF)
• Использование СЧЁТЕСЛИ в Excel
• Подсчет ячеек с несколькими критериями (COUNTIFS)

Функции поиска и ссылок Excel

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

Функция ВПР

Функция ВПР ищет указанное значение в первом столбце и извлекает соответствующие данные из той же строки в другом столбце. Требуются следующие аргументы:

ВПР (искомое_значение, массив_таблиц, индексный_номер_столбца, [диапазон_просмотра])

• lookup_value — значение для поиска.
• table_array — два или более столбца данных.
• col_index_num — номер столбца, из которого извлекаются данные.
• range_lookup — определяет, следует ли искать с точным совпадением (FALSE) или с приблизительным соответствием (TRUE или опущено).

Например, формула =ВПР("яблоки", A2:C10, 3) ищет «яблоки» в ячейках с A2 по A10 и возвращает соответствующее значение из столбца C:

Примеры формулы ВПР:

• Учебник Excel по ВПР для начинающих
• Как использовать функции ВПР и СУММ или СУММЕСЛИ в Excel
• Двусторонний поиск, вложенный ВПР с несколькими критериями
• 4 способа выполнения Vlookup с учетом регистра в Excel
• ВПР Excel не работает — решения для ошибок N/A, NAME и VALUE

Функция ИНДЕКС

ИНДЕКС(массив, номер_строки, [номер_столбца]) — возвращает ссылку на ячейку в массиве на основе указанных вами номеров строки и столбца.

Вот простая формула ИНДЕКС: =ИНДЕКС(A1:C10, 3 ,4) , которая выполняет поиск в ячейках от A1 до C10 и возвращает значение на пересечении строки 3 ^rd и столбца 4 ^th , это ячейка D3.

В следующем учебном пособии подробно объясняется функция ИНДЕКС: 6 наиболее эффективных способов использования функции ИНДЕКС в Excel.

Функция ПОИСКПОЗ

ПОИСКПОЗ(искомое_значение, искомый_массив, [тип_сопоставления]) — ищет искомое_значение в искомом_массиве, а затем возвращает относительное положение этого элемента в диапазоне.

Комбинацию функций ПОИСКПОЗ и ИНДЕКС можно использовать как более мощную и универсальную альтернативу ВПР в Excel, как показано в следующем руководстве: ИНДЕКС и ПОИСКПОЗ — лучшая альтернатива ВПР в Excel.

Функция ДВССЫЛ

ДВССЫЛ(ref_text, [a1]) — возвращает ссылку на ячейку или диапазон, заданную текстовой строкой.

Вот пример простейшей НЕПРЯМОЙ формулы, чтобы получить общее представление:

В реальных рабочих листах НЕПРЯМЫЕ формулы часто используются для динамической ссылки на другой лист или книгу, для блокировки ссылки на ячейку или для создания зависимых выпадающих списков.

Примеры формул ДВССЫЛ:

• Как использовать функцию ДВССЫЛ в Excel
• Как сделать зависимый выпадающий список в Excel

Функция OFFSET

OFFSET(ссылка, строки, столбцы, [высота], [ширина]) — возвращает ссылку на диапазон ячеек, который смещен от начальной ячейки или диапазона ячеек на указанное количество строки и столбцы.

Например, =СМЕЩ(A1, 1, 2) возвращает значение в ячейке C2, поскольку оно находится на 1 строку ниже и на 2 столбца левее от A1.

Дополнительные примеры формул СМЕЩ см. в разделе Использование функции СМЕЩ в Excel.

функция ТРАНСП

ТРАНСП(массив) — преобразует горизонтальный диапазон ячеек в вертикальный диапазон и наоборот, т.е. преобразует строки в столбцы и столбцы в строки.

В следующем учебном пособии приводятся примеры формул и объясняются сильные и слабые стороны функции: Функция Excel ТРАНСП для преобразования строки в столбец.

Функция ГИПЕРССЫЛКА

HYPERLINK(link_location, [friendly_name]) — Создает гиперссылку на документ, хранящийся в локальной сети или Интернете.

Пошаговые инструкции по использованию функции ГИПЕРССЫЛКА см. в разделе Как добавить гиперссылку на другой лист Excel.

Финансовые функции Excel

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

Функция FV

FV(rate, nper ,pmt ,[pv], [type]) — вычисляет будущую стоимость инвестиции на основе постоянной процентной ставки.

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

Функции даты Excel

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

Создание дат

• ДАТА — возвращает порядковый номер указанной даты.
• ДАТАЗНАЧ — преобразует текстовую строку, представляющую формат даты в дату.

Текущая дата и время

• СЕГОДНЯ — возвращает текущую дату.
• СЕЙЧАС — возвращает текущую дату и время.

Извлечение дат и компонентов даты

• ДЕНЬ — возвращает день месяца.
• МЕСЯЦ — возвращает месяц указанной даты.
• ГОД — возвращает год указанной даты.
• КОНМЕСЯЦ — возвращает последний день месяца.
• ДЕНЬ НЕД — возвращает день недели.
• WEEKNUM — возвращает номер недели даты.

Вычисление разницы дат

• РАЗНДАТ — возвращает разницу между двумя датами.
• ЭДАТА — возвращает дату, которая является указанным числом месяцев до или после даты начала.
• YEARFRAC — вычисляет долю года между двумя датами.

Расчет рабочих дней

• РАБДЕНЬ — вычисляет дату, которая является заданным количеством рабочих дней до или после даты начала.
• РАБДЕНЬ.INTL — вычисляет дату, которая представляет собой указанное количество дней недели до или после даты начала, с пользовательскими параметрами выходных.
• ЧИСТРАБДНИ — возвращает количество рабочих дней между двумя датами.
• NETWORKDAYS.INTL — возвращает количество рабочих дней между двумя датами с настраиваемыми выходными.

Функции времени Excel

Ниже приводится список основных функций Excel для работы со временем.

ВРЕМЯ(час,минута,секунда) — возвращает порядковый номер, представляющий время.

TIMEVALUE(time_text) — преобразует время, введенное в виде текстовой строки, в порядковый номер, представляющий время.

NOW() — возвращает порядковый номер, соответствующий текущей дате и времени.

ЧАС(серийный_номер) — преобразует указанный порядковый номер в час.

МИНУТЫ(серийный_номер) — преобразует указанный серийный номер в минуты.

SECOND(serial_number) — преобразует указанный серийный номер в секунды.

Примеры формулы времени:

• Преобразование времени в десятичное число, часы, минуты или секунды
• Расчет времени в Excel — разница во времени, сложение/вычитание времени
• Вставка времени в Excel с помощью функций СЕЙЧАС и ВРЕМЯ

Подсчет и суммирование ячеек по цвету (определяемые пользователем функции)

Microsoft Excel не имеет встроенной функции для подсчета и суммирования ячеек по цвету, поэтому мы сделали шаг вперед и создали несколько пользовательских функций. Эти функции написаны на языке VBA, и вы можете добавлять их на листы через среду программирования Excel — редактор Visual Basic for Applications. Вот наш список пользовательских функций Excel:

• GetCellColor — возвращает цветовой код цвета фона указанной ячейки.
• GetCellFontColor( — возвращает цветовой код цвета шрифта указанной ячейки.
• CountCellsByColor) — считает ячейки с заданным цветом фона.
• CountCellsByFontColor — подсчитывает ячейки с указанным цветом шрифта.
• SumCellsByColor( — вычисляет сумму ячеек с определенным цветом фона.
• SumCellsByFontColor( — возвращает сумму ячеек с определенным цветом шрифта.
• WbkCountCellsByColor — подсчитывает ячейки с указанным цветом фона во всей книге.
• WbkSumCellsByColor — суммирует ячейки с указанным цветом фона во всей книге.

Вы можете скачать код VBA вышеуказанных функций вместе с подробными инструкциями по их использованию здесь: Суммирование и подсчет ячеек по заливке и цвету шрифта.

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