Тема. Мастер функций в MS Excel.
Цель. Приобрести и закрепить практические навыки по применению функций категории Логические с использованием Мастера функций.
Задание. Создать таблицу, показанную на рисунке.
|
А
|
В
|
C L С
|
D
|
E
|
1
|
Ведомость начисления заработной платы
|
2
|
№ п/п
|
Фамилия
|
Оклад
|
Материальная помощь
|
Сумма к выдаче
|
3
|
1
|
Сидоров
|
1850
|
|
|
4
|
2
|
Петров
|
1000
|
|
|
5
|
3
|
Глухов
|
2300
|
|
|
6
|
4
|
Смирнов
|
950
|
|
|
7
|
5
|
Галкин
|
1100
|
|
|
8
|
6
|
Иванов
|
4500
|
|
|
9
|
7
|
Авдеев
|
3400
|
|
|
10
|
8
|
Горшков
|
2800
|
|
|
11
|
|
Всего:
|
|
|
|
Алгоритм выполнения задания.
В ячейке А1 записать название таблицы.
В ячейках А2:Е2 записать шапочки таблицы с предварительным форматированием ячеек, для этого:
Выделить диапазон ячеек А2:Е2.
Выполнить команду Правой кнопкой мыши/Формат Ячеек/Выравнивание.
Установить переключатель «переносить по словам».
В поле «по горизонтали» выбрать «по центру».
В поле «по вертикали» выбрать «по центру».
Набрать тексты шапочек, подбирая по необходимости ширину столбцов вручную.
Заполнить графы с порядковыми номерами, фамилиями, окладами.
Рассчитать графу Материальная помощь, выдавая её тем сотрудникам, чей оклад меньше1500 руб., для этого:
Выделить ячейку D3, вызвать Мастер функций, в категории Логические выбрать функцию ЕСЛИ.
В диалоговом окне функции указать следующие значения:
Логическое выражение
|
С3<1500
|
Значение_если_истина
|
150
|
Значение_если_ложь
|
0
|
Скопировать формулу для остальных сотрудников с помощью операции Автозаполнение.
Вставить столбец Квалификационный разряд.
Выделить столбец Е, щёлкнув по его заголовку.
Выполнить команду Вставка/Столбцы.
Записать шапочку Квалификационный разряд.
Заполнить этот столбец разрядами от 7 до 14 произвольно так, чтобы были все промежуточные разряды.
Вставить и рассчитать столбец Премия, используя логическую функцию ЕСЛИ, выдавая премию в размере 20% оклада тем сотрудникам чей разряд выше 10.
Логическое выражение
|
Е3>10
|
Значение_если_истина
|
С3*0,2
|
Значение_если_ложь
|
0
|
Рассчитать графу Сумма к выдаче так, чтобы в сумму не вошёл Квалификационный разряд.
Рассчитать итоговые значения по всем столбцам, кроме столбца Квалификационный разряд.
Проверить автоматический перерасчёт таблицы при изменении значений:
Изменить оклады нескольким сотрудникам, проверить изменение таблицы.
Изменить квалификационные разряды нескольким сотрудникам.
Изменить условие начисления премии: если Квалификационный разряд выше 12, то выдать Премию в размере 50% оклада.
Контрольные вопросы
Для решения каких задач используется логическая функция ЕСЛИ?
Как реализуются функции копирования и перемещения в Excel?
Как можно вставить или удалить строку, столбец в Excel?
Лабораторная работа №7
Тема. Мастер функций в MS Excel.
Цель. Приобрести и закрепить практические навыки по применению функций категории Математические с использованием Мастера функций.
Задание 1. Создать и заполнить таблицу алгебраических функций, показанную на рисунке.
|
A
|
B
|
C
|
D
|
E
|
F
|
G
|
H
|
1
|
Число
|
Десятичный логарифм
|
Натуральный логарифм
|
Корень
|
Квадрат
|
Куб
|
Показательная функция
|
Факториал
|
2
|
0
|
|
|
|
|
|
|
|
3
|
1
|
|
|
|
|
|
|
|
Алгоритм выполнения задания.
В ячейках А1:Н1 записать шапочки таблицы с предварительным форматированием ячеек, для этого:
Выделить диапазон ячеек А1:Н1.
Выполнить команду Правой кнопкой мыши/Формат Ячеек/Выравнивание.
Установит переключатель «переносит по словам».
В поле «по горизонтали» выбрать «по центру».
В поле «по вертикали» выбрать «по центру».
Набрать тексты шапочек, подбирая по необходимости ширину столбцов вручную.
Записать в графу Число ряд чисел, начиная с 0:
В ячейки А2 и А3 записать 0 и 1.
Выполнить операцию Автозаполнение до числа 15.
Заполнить графу Десятичный логарифм следующим образом:
Выделить ячейку В2, вызвать Мастер функций, выбрать категорию математические, выбрать функцию LOG10.
В поле Число ввести адрес А2 с клавиатуры или, отодвинув диалоговое окно функции за любое место серого поля, щелкнуть ячейку А2.
Выполнить операцию Автозаполнение для всего столбца.
Примечание. В ячейке В2 должно быть #ЧИСЛО!, т. к. логарифм 0 не существует.
Заполнить графу Натуральный логарифм аналогично, выбрав функцию LN.
Заполнить графу Корень аналогично, выбрав функцию КОРЕНЬ.
Графы Квадрат и Куб заполнить следующим образом:
Выбрать функцию СТЕПЕНЬ.
В поле Число ввести адрес А2.
В поле Степень ввести 2 для квадратичной функции или 3 для кубической.
Заполнить графу Показательная функция следующим образом:
Выбрать функцию СТЕПЕНЬ.
В поле Число ввести 2.
В поле степень ввести адрес А2.
Заполнить графу Факториал аналогично пю3, выбрав функцию ФАКТР.
Примечание. Любую функцию можно записать с клавиатуры, точно соблюдая текст названия функции и её синтаксис, применяемый в Мастере функций.
Задание 2. Создать и заполнить таблицу тригонометрических функций, показанную на рисунке.
|
A
|
B
|
C
|
D
|
E
|
F
|
1
|
Угол, град.
|
Угол, радиан
|
Синус
|
Косинус
|
Тангенс
|
Сумма квадратов
|
2
|
0
|
|
|
|
|
|
3
|
15
|
|
|
|
|
|
Заполнить графу Угол, град. числами от 0 до 180, используя операцию Автозаполнение.
Заполнить графу Угол, радиан значениями, применив функцию РАДИАНЫ.
Заполнить графы Синус, Косинус, Тангенс, применяя функции SIN, COS, TAN. В качестве аргумента выбирать значения угла в радианах.
Примечание. В некоторых ячейках значения записываются в экспоненциальной форме, например, запись 1,23Е-16 означает, что число 1, 23 возводится в степень минус 16, что даёт число, очень близкое к нулю, а запись 1,23Е+16 означает возведение числа 1,23 в степень плюс 16.
Заполнить графу Сумма квадратов известной формулой SIN2()+ COS2()=1, проверить результат для всех углов.
Контрольные вопросы
Какие форматы записи числовых данных используются в Excel?
Как изменить формат числовых данных?
Как изменить разрядность числа в таблице?
Как вызвать справку Excel?
Какой символ обязательно набирается перед вводом формулы?
Лабораторная работа №8
Тема. Абсолютный адрес в MS Excel.
Цель. Приобрести и закрепить практические навыки по применению абсолютной адресации при расчёте электронной таблицы.
Задание 1. Создать и заполнить таблицу расчёта доходов, показанную на рисунке.
|
A
|
B
|
C
|
D
|
E
|
1
|
Распределение доходов в зависимости от КТУ
|
2
|
Общий доход
|
10000
|
|
|
|
3
|
Фамилия
|
Время, ч
|
Квалификационнй разряд
|
КТУ
|
Сумма к выдаче
|
4
|
Сотрудник 1
|
5
|
10
|
|
|
5
|
|
10
|
12
|
|
|
6
|
|
12
|
18
|
|
|
7
|
|
8
|
5
|
|
|
8
|
|
15
|
10
|
|
|
9
|
|
7
|
8
|
|
|
10
|
|
20
|
9
|
|
|
11
|
|
10
|
6
|
|
|
12
|
|
8
|
15
|
|
|
13
|
|
16
|
10
|
|
|
14
|
Итого
|
|
|
Алгоритм выполнения задания.
Записать исходные значения таблицы, указанные на рисунке.
Заполнить графу Фамилия значениями Сотрудник 1÷10, используя операцию Автозаполнение.
Рассчитать графу КТУ как произведение времени, затраченного сотрудником, на его квалификационный разряд (формула =В4*С4).
Подсчитать значение Итого с помощью операции Автосумма.
Графа Сумма к выдаче рассчитывается как произведение общего дохода на отношение КТУ данного сотрудника к итоговому КТУ (формула =В2*D4/D14).
При выполнении операции Автозаполнение в графе Сумма к выдаче появляются ошибки #ЗНАЧ! и #ДЕЛ/0!. Это происходит из-зи того, что при применении формулы происходит изменение адресов в ней, например, в ячейке Е5 формула содержит адреса = В3*D5/D15.
Для правильного расчёта необходимо зафиксировать адреса В2 и D14, для этого:
Выделить ячейку Е4.
В строке формул отображается формула из этой ячейки, щёлкнуть по адресу В2 в этой формуле, нажать клавишу F4, у обозначения адреса появятся значки $B$4, щёлкнуть по обозначению адреса D14, нажать клавишу F4, у обозначения адреса появятся значки $D$14.
Выполнить заново операцию Автозаполнение для графы Сумма к выдаче (вместе с ячейкой Итого).
В ячейке Итого должна получиться сумма, равная Общему доходу.
Присвоить денежным величинам обозначение в рублях, для этого выделить ячейку В2, щёлкнуть кнопку Денежный формат на панели инструментов Форматирование или выполнить команду Правой кнопкой мыши/Формат Ячеек/Выравнивание.
Денежный, установить в поле Обозначение тип р.
Для проверки возможности автоматического перерасчёта таблицы заменить значения Квалификацилннлгл разряда, Времени, затраченного некоторыми сотрудниками, а также величины Общего дохода, например на 25000 р.
Установить для графы Сумма к выдаче отображение с двумя десятичными разрядами, для этого выделить диапазон ячеек Е4:Е14, щёлкнуть на кнопке Увеличить разрядность на панели инструментов Форматирование или выполнить команду Правой кнопкой мыши/Формат Ячеек/Выравнивание.
/Денежный, установить в поле Число десятичных знаков число 2.
Задание 2. Создать и заполнить таблицу расчёта стоимости, показанную на рисунке.
|
A
|
B
|
C
|
D
|
E
|
1
|
Стоимость программного обеспечения
|
2
|
Наименование
|
Стоимость, $
|
Стоимость, р.
|
Стоимость, Евро
|
Доля в общей стоимости, %
|
3
|
OC Windows
|
18
|
|
|
|
4
|
Пакет MS Office
|
32
|
|
|
|
5
|
Редактор Corel Draw
|
15
|
|
|
|
6
|
Графический ускоритель 3D
|
22
|
|
|
|
7
|
Бухгалтерия 1С
|
50
|
|
|
|
8
|
Антивирус DR Web
|
20
|
|
|
|
9
|
Итого
|
157
|
|
|
|
10
|
Курс валюты (к рублю)
|
28
|
|
35
|
|
Алгоритм выполнения задания.
Записать исходные текстовые и числовые данные.
Рассчитать графу Стоимость, р., используя курс доллара как абсолютный адрес.
Рассчитать графу Стоимость, Евро, используя курс доллара и курс Евро как абсолютные адреса.
Рассчитать графу Доля в общей стоимости, используя итоговую Стоимость, р. как абсолютный адрес.
Преобразовать числовые значения в графе Доля в общей стоимости в процентные значения:
Выделить числовые значения этой графы.
Щёлкнуть по кнопке Процентный формат.
Установить отображение процентов с одним десятичным знаком, используя кнопки Увеличить или Уменьшить разрядность.
Контрольные вопросы
Для чего используются абсолютные и относительные адреса ячеек?
В чём смысл правил автоматической настройки формул при выполнении операций копирования и перемещения?
Лабораторная работа №9
|
Скачать 284 Kb.