Similar presentations:
ТП MS EXCEL. Технологии использования функций рабочего листа
1. ТП MS EXCEL. ТЕХНОЛОГИИ ИСПОЛЬЗОВАНИЯ ФУНКЦИЙ РАБОЧЕГО ЛИСТА
План лекцииМатематические функции ТП MS Excel
Функции для выполнения арифметических операций
Функции округления
Функции для работы с векторами
Логические функции ТП MS Excel
Статистические функции ТП MS Excel
Функции ТП MS Excel категории «Ссылки и массивы»
1
2. Математические функции Excel
функции для выполнения арифметических операций:СУММ, СУММКВ, ПРОИЗВЕД, ЦЕЛОЕ, ОСТАТ, СТЕПЕНЬ,
КОРЕНЬ, и др.;
тригонометрические и обратные тригонометрические
функции:
SIN, COS, АSIN, ACOS, ATAN, и др. LN, LOG, EXP;
функции округления:
ОКРВВЕРХ, ОКРВНИЗ, ОКРУГЛВВЕРХ, ОКРУГЛВНИЗ,
ОКРУГЛ, НЕЧЕТ, ЧЕТН…
функции для работы с векторами и матрицами:
СУММПРОИЗВ, СУММКВРАЗН, СУММРАЗНКВ,
СУММСУММКВ, МОБР, МОПРЕД, МУМНОЖ…
2
3. Функции для выполнения арифметических операций
СУММ (число1; число2; . . . ; число n) - вычисляет сумму аргументов(до 30 ).
Например,
=СУММ(B5:В10), сложит содержимое ячеек с B5 до B10,
=СУММ(B5:В10; А11) сложит содержимое ячеек с B5 до B10 с
содержимым ячейки А11
СУММКВ (число1; число2; . . . ; число n) – вычисляет сумму
квадратов аргументов
ПРОИЗВЕД (число1; число2; . . . ; число n) – возвращает
произведение аргументов.
СТЕПЕНЬ (число; степень) – возвращает результат возведения
аргумента число в указанную степень
КОРЕНЬ (число) – возвращает значение квадратного корня из
аргумента число.
3
4. Автосумма
АвтосуммаПроцедура.
Выделение ячейки B5
Клавиша («бегущая дорожка»)
ENTER
5. Пример Найти общую стоимость пряжи разрывная нагрузка которой превышает 10 сН/ текс
СУММЕСЛИ(диапазон; критерий; диапазон_суммирования)– суммирует ячейки, заданные указанным условием.
C2:C6 {12; 8; 11; 9; 14} – разрывная нагрузка
D2:D6 {30000; 20000; 25200; 19800; 35000} – цена пряжи
D7 =СУММЕСЛИ(C2:C6; “>10”; D2:D6) 90200
5
6. Пример1 Найти суммарную заработную плату сотрудников, которые провели более 30 испытаний:
СУММЕСЛИ(диапазон; критерий; диапазон_суммирования) – суммируетячейки, заданные указанным условием.
В2:В7 {50; 12; 45; 4; 18; 20} – количество проведенных испытаний
С2:С7 {850;156;750;20;98;189} – заработная плата
С8 СУММЕСЛИ(В2:В7; “>30”; С2:С7) 1600
6
7. Мастер функций
Назначение: определение синтаксиса функциис целью упрощения ее записи.
Активизация. 2 варианта:
• Вызов списка у кнопки
- Среднее
- Максимум
- Минимум
- Другие функции
• Активизация кнопки fХ
8. Мастер функций
Мастер fМастер f:
- Среднее
- Максимум
- Минимум
- Другие f
9. Мастер функций
10. Тригонометрические и обратные тригонометрические функции
ABS(число) – возвращает модуль (абсолютную величину)числа
ACOS(число), ASIN(число), ATAN(число) –
возвращает арккосинус арксинус арктангенс числа в радианах
COS(число), SIN(число), TAN(число) – возвращает
косинус, синус, тангенс числа
EXP(число) – возвращает экспоненту заданного числа
LN(число) – возвращает натуральный логарифм числа
LOG(число;основание_логарифма) – возвращает
логарифм числа по заданному основанию
10
11. Функции округления
ОКРУГЛ (число; число разрядов)Например,
ОКРУГЛ (82,93; 0) 83 (округление до целых).
ОКРУГЛ (82,93; 1) 82,9 (округление до десятых).
ОКРУГЛ (82,93; -1) 80 (округление до десятков).
ОКРУГЛВЕРХ (число; число разрядов)
ОКРУГЛВНИЗ (число; число разрядов)
НЕЧЕТ (число) и ЧЕТН (число)
ОКРВВЕРХ (число; точность) и
ОКРВНИЗ (число; точность).
11
12.
Функции для работы с векторамиСУММПРОИЗВ (ДЯ1; ДЯ2)
Суммирование произведений ячеек заданных диапазонов
Пример. Вычислить стоимость товара
A
Товар
B
Кол-во
C
Цена, руб
Нотбук
1305
25000
Принтер
665
9400
Сканер
203
11200
......
Итого:
....
D
Стоимость, руб
......
=СУММПРОИЗВ
(B2:B100;C2:C100)
ИТОГО = B2*C2+B3*C3+B4*C4+…
13. Функции для работы с матрицами
МОБР (массив)Пример
B39:E42 = МОБР(В13:Е16) А-1
Функцию следует вводить как формулы массива:
нажав одновременно <CTRL>, <SHIFT> и <ENTER>.
МОПРЕД (массив)
Пример
F13 = МОПРЕД(В13:Е16) -53
13
14.
• Матрицей А размера m x n называетсяпрямоугольная таблица из m строк и n
столбцов, состоящая из чисел или иных
математических выражений (называемых
элементами матрицы).
14
15.
МУМНОЖ (массив 1; массив 2)Массив 1, массив 2 – это перемножаемые массивы, причем
количество столбцов массива 1 должно быть равно числу строк массива 2.
Пример
I39 = МУМНОЖ(В39:Е42;G39:G42) матрица
Х
Функцию следует вводить как формулы массива:
нажав одновременно <CTRL>, <SHIFT> и <ENTER>.
15
16. Функции категории «Статистические»
СРЗНАЧ(зн1, зн2, … , зн n) – возвращает среднее
арифметическое значение диапазона ячеек. Если в диапазоне
находятся пустые ячейки или ячейки, содержащие текст, то они
игнорируются.
СРЗНАЧА (зн1,
зн2, … , зн n) – вычисляет среднее
арифметическое значений аргументов, которые, помимо чисел,
могут быть текстом или логическими значениями.
СЧЕТЕСЛИ (интервал, критерий) – количество
удовлетворяющих заданному критерию ячеек внутри интервала.
16
17.
СЧЕТ (зн1, зн2, … , знN)СЧЕТЗ (зн1, зн2, … , знN)
СЧЕТЗ (A1:A8) 6
СЧЁТ(A1:A8) 3
СЧЕТЗ (A4:A8) 4
СЧЁТ(A4:A8) 2
СЧЕТЗ (A1:A8, 2) 7
СЧЁТ(A1:A8,3) 4
СЧЕТЗ (A1:A8,
«Май") 7
17
18. МАКС (зн1, зн2, … , знN) МИН (зн1, зн2, … , знN) НАИБОЛЬШИЙ (массив; k) НАИМЕНЬШИЙ (массив; k)
=МАКС (I2:I7) 8,5=МИН(I2:I7) 6,8
=НАИБОЛЬШИЙ(I2:I7; 2)
8,3
=НАИМЕНЬШИЙ(I2:I7 ;2) 7
18
19.
Статистические функцииСЧЕТЕСЛИ (ДЯ; условие) : подсчет количества ячеек в заданном
диапазоне, для которых заданное условие истинно
Пример. Определить количество оплат предприятием
«Консат»
A
B
Предприятие
Дата
ВЫМПЕЛ
ЗАО ДОНСТРОЙ
Консат
АГАТ
Консат
15/01/17
24/09/17
24/09/17
25/07/17
12/01/17
D
…
Оплачено, тыс.руб
785,56
1984,42
7642,38
8523,65
5890,66
СЧЕТЕСЛИ (A2:A150; ‘Консат’)→2
20. Логические функции
1. ЕСЛИ (логическое_выражение; знач_если_истина;знач_если_ложь)
Пример 3
В3:B7 {85, 100, 65, 110, 90}
C3 ЕСЛИ(В3<=90; 100%; 50%)
20
21. Логические функции EXCEL
2. И(логическое_значение1;логическое_значение2; ....; логич_значение N)
Пример 3
С4 =ЕСЛИ(И(B4>50;B4<100); «1 сорт»; «2 сорт»)
Если значение в ячейке А5 находится в интервале от 50 до
100, то в активной ячейке вернется значение «1 сорт», при
других значениях будет выведено сообщение «2 сорт».
21
22. Логические функции EXCEL
3. ИЛИ(логическое_значение1;логическое_значение2; ....; логич_ значениеN)
А5 =ЕСЛИ(ИЛИ(B4>50;B4<100); «1 сорт»;
«2 сорт»)
Если значение в ячейке А5 больше 50 или меньше 100, то в
активной ячейке вернется значение «1 сорт», при других
значениях будет выведено сообщение «2 сорт».
.
22
23. Логические функции EXCEL
4. НЕ (логическое_значение)НЕ (8>2) -- > ЛОЖЬ
НЕ (8<2) -- > ИСТИНА
5. ИСТИНА – возвращает логическое
значение ИСТИНА
6. ЛОЖЬ - возвращает логическое
значение ЛОЖЬ
7. ЕСЛИОШИБКА - (значение,
значение_при_ошибке)
23
24.
Логическая функция ЕСЛИЕСЛИ (логич. выражение; знач.1; знач.2)
A
B
C
D
E
Год рожд.
Пол
...
...
1
ФИО
2
Иванов
1965
м
3
Петрова
1978
ж
Пример 1. Женщинам необходимо выплатить к 8 марта
премию 5000 руб.
D2 = ЕСЛИ (С2=‘ж’; 5000; 0)
25.
Логическая функция ЕСЛИЕСЛИ (логич. выражение; знач.1; знач.2)
A
B
C
D
E
Год рожд.
Пол
...
...
1
ФИО
2
Иванов
1965
м
3
Петрова
1978
ж
Пример 1. Женщинам выплатить к 8 марта премию 5000 руб.
= ЕСЛИ (С2=‘Ж’; 5000; 0)
Столбец С – пол
Примечание 1. Формат столбца С – текстовый
Примечание 2. Текстовые константы выделяются символами‘ ’
26.
Логическая функция ЕСЛИЕСЛИ (логич. выражение; знач.1; знач.2)
A
B
C
D
E
Год рожд.
Пол
...
...
1
ФИО
2
Иванов
1965
м
3
Петрова
1978
ж
0
5000
Пример 1. Женщинам выплатить к 8 марта премию 5000 руб.
D2 = ЕСЛИ (С2=‘Ж’; 5000; 0)
Столбец С – пол
Примечание 1. Формат столбца С – текстовый
Примечание 2. Текстовые константы выделяются символами‘ ’
27. Логическая функция ЕСЛИ
ЕСЛИ (логич. выражение; знач.1; знач.2)Примечание 1. В качестве ЗНАЧ.1, ЗНАЧ.2 может быть,
в свою очередь, использована функция ЕСЛИ.
Примечание 2. Допускается вложение функции ЕСЛИ
до 7 уровней
28. Функции категории «Ссылки и массивы»
ПРОСМОТР (искомое_значение;просматриваемый_вектор;
вектор_результатов)
ВПР(искомое_значение; инфо_таблица;
номер_столбца;
интервальный_просмотр)
ВЫБОР(номер_индекса; знач_1; знач_2;…)
29. Пример
Определить владельца машины с номернымзнаком 5259в-2
=ПРОСМОТР("5259в-2";M15:M17;L15:L17)
Результат вычисления формулы:
Сидоров К. А.
30. Задача.
Автоматизировать перерасчет окладовФИО
Категория
Оклад, руб.
1
2
1500
3000
3
4650
4
5
5700
6750
7800
8900
10000
13200
Категория
Оклад, руб.
Абрамов А.А.
2
2000
Берлин Б.Б.
4
4000
Васин В.В.
1
1000
Гааг Г.Г.
5
5000
6
7
8
9
Дулин Д.Д.
12
12000
10
16400
Зуев З.З.
10
10000
11
17600
12
19800
13
24000
14
28300
15
34600
16
45000
.....
...
...
31.
= ВПР (В2; Е$1$:F$17$; 2)32.
33.
РЕЗУЛЬТАТ34. ВЫБОР(номер_индекса; знач_1; знач_2;…)
ВЫБОР (2;«1-ый»;«2-ой»;«3-ий»;«Последний») «2-ой»СУММ(A1:ВЫБОР(3;A10;A20;A30)) СУММ(A1:A30)
Если ячейка A10 содержит 3, то:
ВЫБОР (A10;«Платья»;«Брюки»;«Юбки»;«Свитера»)
«Юбки»
Если A10 равняется 3, то:
ВЫБОР (A10;«1-ый»;«2-ой»;«3-ий»;«Последний») «3ий»
Если ПрошлыеПродажи - это имя, ссылающееся на
значение 10 000, то:
ВЫБОР(2; НовыеПродажи; ПрошлыеПродажи;
БюджетПродаж) 10 000
34
35. Функции категории «Текстовые»
НазваниеОперация, выполняющаяся над значениями
функции
в удовлетворяющих критерию строках
СЦЕПИТЬ
ЗАМЕНИТЬ
СОВПАД
НАЙТИ
ЛЕВСИМВ
ДЛСТР
СТРОЧН
ПРОПНАЧ
ПОДСТАВИТЬ
СЖПРОБЕЛЫ
ПРОПИСН
Объединяет несколько текстовых строк в одну
Замещает указанную часть символов текстовой строки другой
строкой текста
Проверяет идентичность двух текстов
Ищет вхождение одного текста в другой (с учетом регистра)
Возвращает указанное количество знаков сначала строки
текста
Возвращает количество знаков в текстовой строке
Делает все буквы в тексте строчными
Делает прописной первую букву в каждом слове текста
Заменяет в текстовой строке старый текст новым
Удаляет из текста пробелы
Делает все буквы в тексте прописными
35
36. Пример использования функций
=ЗАМЕНИТЬ («2007»;3; 2;«15») равняется 2015 –заменит две последние цифры в тексте 2007 на 15.
Если ячейка A10 содержит «Иванова Ольга Игоревна», то:
=ЗАМЕНИТЬ (А10;1; 6;«Петрова»)
Петрова Ольга Игоревна – заменит шесть первых символов
в тексте.
Если ячейка A1 содержит «Смирнов» , В1 – «Иван»,
С1- «Иванович», то:
А10=СЦЕПИТЬ (A1;B1;C1) «Смирнов Иван Иванович»
=СЦЕПИТЬ (2;0;15) 2015
=ЛЕВСИМВ («Кожа, Мех»,4) «Кожа»
=ПРАВСИМВ («Кожа, Мех»,3) «Мех»
36
37. Функции категории «Дата и Время»
Названиефункции
ГОД
ДЕНЬ
ДЕНЬНЕД
ДНЕЙ360
МЕСЯЦ
РАБДЕН
СЕГОДНЯ
ТДАТА
ЧИСТРАБДНИ
Операция, выполняющаяся над значениями в удовлетворяющих критерию
строках
Возвращает год, соответствующий аргументу дата_в_числовом_формате. Год
определяется как целое в интервале 1900-9999. Дата_в_числовом_формате - это
дата, год которой необходимо найти.
Возвращает день, соответствующий аргументу дата_в_числовом_формате.
Дата_в_числовом_формате - это дата, день которой необходимо найти.
Преобразует дату в числовом формате в день недели.
Вычисляет количество дней между двумя датами на основе 360-дневного года.
Преобразует дату в числовом формате в месяцы.
Возвращает число, представляющее дату, отстоящую на заданное количество
рабочих дней вперед или назад от начальной даты. (рабочими днями не считаются
выходные дни и дни, определенные как праздничные. Функция РАБДЕНЬ
используется, чтобы исключить выходные дни или праздники при вычислении дат
платежей, ожидаемых дат доставки или количества фактически отработанных
дней).
Возвращает текущую дату в числовом формате.
Возвращает текущую дату и время в числовом формате.
Возвращает количество рабочих дней между нач_дата и кон_дата. Праздники и
выходные в это число не включаются. Функцию ЧИСТРАБДНИ можно
использовать для вычисления оплаты работника на основе количества дней,
отработанных в указанный период
37
38. Пример использования функций
=ГОД (03.05.2015) равняется 2015 – вернет номер года.Если ячейка A1 содержит дату 05.12.2014 , то
=ГОД (А1) 2014.
Если ячейка A1 содержит число 12 , А2 – 3, А3- 2014, то:
=ДАТА (А1;А2;А3) 12.03.2014
=ДАТА (13;7;1985) 13.07.1985
Если ячейка A4 содержит дату 08.12.2014 , то
=ДЕНЬ (А4) 8.
=ДЕНЬНЕД (А4;2) 1 день недели (понедельник).
=ДНЕЙ360 (12.03.2015; 06.04.2015) 24 дня между этими
датами.
38
=СЕГОДНЯ 29.10.2015 возвращает
текущую дату.
39. Функции обработки дат
В EXCEL не представлен тип данных «Дата».Даты преобразуются в числа.
Функции:
1.
2.
3.
4.
5.
ДАТА(год,месяц,день)
ДЕНЬ (Дата как Число)
МЕСЯЦ (Дата как Число)
ГОД (Дата как Число)
СЕГОДНЯ ()
число
элемент даты
Аргумент – дата,
представленная в виде числа
40. Функции обработки дат
Пример 1. Повысить с 1 апреля стипендию на 5000 руб.ЕСЛИ(СЕГОДНЯ()>ДАТА(2017;03;31); А3+5000; А3)
Ячейка А3 – значение стипендии
Пример 2. Определить количество выплат штрафа
предприятиями, происшедших с начала 2006 года.
41. Пример 2.
СЧЕТЕСЛИ (А2:А11; >ДАТА(2005;01;01))СЧЕТЕСЛИ (В3:В14; >ДАТА(2006;01;01))
42.
Значения ошибок в формулахЕсли Excel не может выполнить обработку формулы в ячейке и вывести
результат, то он генерирует сообщение об ошибке и выводит его в данной
ячейке. Сообщение об ошибке всегда начинается со знака «#».
Сообщение об
ошибке
######
#ЗНАЧ!
#ДЕЛ/0
#ИМЯ?
#Н/Д
#ССЫЛКА!
#ЧИСЛО!
#ПУСТО!
Типичная причина
Возможное исправление
Данное не умещается в ячейке
Увеличить ширину ячейки
Недопустимый тип операнда или
аргумента
Попытка деления на ноль
Изменить неправильный тип
данных
Исправить формулу, данные
При наборе имени произошла
опечатка, текст ошибочно не был
заключен в кавычки
Не заданы аргументы функции,
неправильная ссылка на данные
В ссылке на ячейку указывается
недопустимый адрес
Полученное число слишком велико по
абсолютной величине,
Неприемлемый аргумент в функции с
числовым аргументом.
Ошибочная ссылка на ячейку или
диапазон
Исправить аргументы в
функции
Исправить формулу
Исправить формулу