«Вычисления в электронных таблицах»
Цель. Приобрести и закрепить практические навыки по применению функций категорий Статистические, Дата и время, Логические и Математические с использованием Мастера функций.
Задание 1. Создать таблицу, показанную на рисунке.
A | B | C | D | E | F | G | H | |
1 | Продажа комплектующих к персональным компьютерам | |||||||
2 | Месяц | Центр ЭВМ | ЭВМ-сервис | Дом бизнеса | Техноцентр | Среднее | Максимум | Минимум |
3 | Январь | 18420 | 10305 | 25420 | 15940 | |||
4 | Февраль | 18300 | 10370 | 25400 | 15880 | |||
5 | Март | |||||||
6 | Апрель | |||||||
7 | Май | |||||||
8 | Июнь | |||||||
9 | Июль | |||||||
10 | Август | |||||||
11 | Сентябрь | |||||||
12 | Октябрь | |||||||
13 | Ноябрь | |||||||
14 | Декабрь | |||||||
15 | Итого: | |||||||
16 | Максимум | |||||||
17 | Минимум |
Алгоритм выполнения задания.
1. Записать заголовок и шапочки таблицы (ячейкиА1:Н2).
2. Заполнить боковик таблицы, используя функцию Список.
2.1. В ячейку А3 записать Январь.
2.2. Выделить ячейку А3, подвести указатель мыши к правому нижнему углу ячейки, указатель примет вид тонкого чёрного креста ┼, протащить с нажатой левой кнопкой до ячейки А14 (операция Автозаполнения).
3. Заполнить четыре столбца цифровыми данными:
3.1. Заполнить две строки указанными на рисунке цифрами.
3.2. Выделить диапазон ячеек В3:Е4, выполнить операцию Автозаполнение до строки Итого.
4. Заполнить графу Итого, используя операции Автосумма и Автозаполнение.
5. Рассчитать Среднее в ячейке F3, используя команду Вставка функции на вкладке Формулы.
5.1. Выделить ячейку F3, щёлкнуть значок fx на вкладке Формулы.
5.2. В диалоговом окне Вставка функции в поле Категория выбрать Статистические, в поле Функция найти и выбрать СРЗНАЧ, нажать ОК.
5.3. Появится диалоговое окно функции СРЗНАЧ с автоматически подставленным диапазоном В3:F3 в поле Число1 и подсказками, нажать ОК.
6. Заполнить столбец Среднее по Декабрь, используя операцию Автозаполнение.
7. Рассчитать Максимум в ячейке G3, используя команду Вставка функции.
7.1. Выделить ячейку G3, щёлкнуть значок fx на вкладке Формулы.
7.2. В диалоговом окне Вставка функции в поле Категория выбрать Статистические, в поле Функция найти и выбрать МАКС, нажать ОК.
7.3. Появится диалоговое окно функции МАКС с автоматически подставленным диапазоном B3:F3в поле Число1, этот диапазон неверен, для его исправления:
7.3.1. Отодвинуть диалоговое окно, захватив его левой кнопкой мыши за любое место на сером поле так, чтобы была видна строка Январь.
7.3.2. Обвести диапазон В3:Е3 с нажатой левой кнопкой мыши, при этом в поле Число1 появятся нужные адреса (можно также ввести нужные адреса с клавиатуры), нажать ОК.
8. Заполнить столбец Максимум по Декабрь, используя операцию Автозаполнение.
9. Рассчитать Минимум в ячейкеН3, используя команду Вставка функции.
9.1. Выделить ячейку Н3, щёлкнуть значок fx на вкладке Формулы.
9.2. В диалоговом окне Вставка функции в поле Категория выбрать Статистические, в поле Функция найти и выбрать МИН, нажать ОК.
9.3. Появится диалоговое окно функции МИН с автоматически подставленным диапазоном B3:G3 в поле Число1, этот диапазон неверен, для его исправления:
9.3.1. Отодвинуть диалоговое окно, захватив его левой кнопкой мыши за любое место на сером поле так, чтобы была видна строка Январь.
9.3.2. Обвести диапазон В3:Е3 с нажатой левой кнопкой мыши, при этом в поле Число1 появятся нужные адреса (можно также ввести нужные адреса с клавиатуры), нажать ОК.
10. Заполнить столбец Минимум по Декабрь, используя операцию Автозаполнение.
11. Рассчитать строку 16 Максимум с помощью мастера функций, исправляя диапазон адресов на В3:В14 и применяя операцию Автозаполнение.
12. Рассчитать строку 17 Минимум с помощью мастера функций, исправляя диапазон адресов на В3:В14 и применяя операцию Автозаполнение.
Задание 2. Создать таблицу, показанную на рисунке.
А | В | С | |
1 | Функции Дата и время | ||
2 | Начало работы | ||
3 | Системная (текущая) дата и время | ||
4 | Сегодня | ||
5 | Конец месяца | ||
6 | Конец года | ||
7 | Осталось до конца месяца | ||
8 | Осталось до конца года | ||
9 | |||
10 | День рождения | ||
11 | Прожил дней | ||
12 | Сегодня + 100 дней | ||
13 | Сегодня + 365 дней |
Алгоритм выполнения задания.
1. Записать указанный текст обозначений в столбец А.
2. В ячейку В2 записать дату и время своей работы строго соблюдая формат, например, 15.01.07 10:15 (т.е. 15 января 2007 года 10 часов 15 минут)
3. В ячейку В3 вставить текущую дату с помощью Вставки функций:
3.1. Выделить ячейку В3, щёлкнуть значок fx на вкладке Формулы.
3.2.В диалоговом окне Вставка функции в поле Категория выбрать Дата и время, в поле Функция найти и выбрать ТДАТА, нажать Ок и ОК.
4. В ячейку В4 вставить текущую дату с помощью Вставки функции, выбрав функцию СЕГОДНЯ.
5. В ячейки В5 и В6 записать даты конца месяца и конца года, например, 31.01.07 и 31.12.07.
6. В ячейку В7 записать формулу =В5-В4 (получим разность в формате ДД.ММ.ГГ).
7. В ячейку В8 записать формулу =В6-В4 (получим разность в формате ДД.ММ.ГГ).
Примечание. Программа некорректно обрабатывает количество месяцев, завышая его на единицу.
8. В ячейку В10 записать дату своего дня рождения, например, 29.12.01.
9. Вычислить число прожитого времени по формуле =В4-В10 (в формате ДД.ММ.ГГ и учётом примечания).
10. Вычислить даты в ячейках В12 и В13, самостоятельно записав нужные формулы.
11. Преобразовать дату в ячейке В13 в текстовый формат, для этого:
11.1. Выделить ячейку В13, выполнить команду Формат/Ячейки/Число.
11.2. В диалоговом окне в поле Числовые форматы выбрать Дата, в поле Тип выбрать формат вида «14 март, 2001», нажать ОК.
12. Скопировать диапазон ячеек В4:В6 в диапазон С4:С6, для этого:
12.1. Выделить диапазон В4:В6.
12.2. Щелкнуть кнопку Копировать на вкладке Главная.
12.3. Выделить ячейку С4, щёлкнуть кнопку Вставить на вкладке Главная.
13. Преобразовать формат даты в ячейке С6 в текстовый, выполнив команду Формат/Ячейки/Число и выбрав Тип «Март 2001».
14. Преобразовать формат даты в ячейке С5 в текстовый, выполнив команду Формат/Ячейки/Число и выбрав Тип «14 мар».
15. Преобразовать формат даты в ячейке С4 в текстовый, выполнив команду Формат/Ячейки/Число и выбрав Тип «14 мар 01».
16. Установить в ячейке С3 отображение секундомера системных часов, для этого:
16.1. Выделить ячейку С3, щёлкнуть значок fx на вкладке Формулы.
16.2.В диалоговом окне Вставка функции в поле Категория выбрать Дата и время, в поле Функция найти и СЕКУНДЫ, нажать ОК.
16.3.В диалоговом окне СЕКУНДЫ ввести в поле Дата_как_число адрес В3, ОК.
16.4. Значения секунд в ячейке С3 будут изменяться при нажатии клавиши F9.
17. Вычислить длительность выполнения работы, для этого:
17.1.Выделить ячейку С2, записать формулу =В3-В2, нажать Enter, результат будет записан в формате ДД.ММ.ГГ ЧЧ:ММ.
17.2.Преобразовать значение в ячейке С2 в формат ЧЧ:ММ:СС, для этого:
17.2.1. Выделить ячейку С2, выполнить команду Формат/Ячейки/Число.
17.2.2. В поле Числовые форматы выбрать (все форматы).
17.2.3. В поле Тип выбрать [ч]:мм:сс, нажать ОК.
17.2.4. Значения секунд в ячейке С2 будут изменяться при нажатии клавишиF9.
18. Сравнить вычисленные значения с показанием системных часов на Панели задач.
Задание 3. Создать таблицу, показанную на рисунке.
А | В | 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% оклада.
Задание 4. Создать и заполнить таблицу алгебраических функций, показанную на рисунке.
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, выбрав функцию ФАКТР.
Примечание. Любую функцию можно записать с клавиатуры, точно соблюдая текст названия функции и её синтаксис, применяемый в Мастере функций.
Задание 5. Создать и заполнить таблицу тригонометрических функций, показанную на рисунке.
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, проверить результат для всех углов.