Задания и порядок выполнения

Лабораторная работа №6 Использование различных категорий функций MS Excel

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

Задания и порядок выполнения

Упражнение 1. Создать таблицу с использованием математических и статических функций.

1. Создать следующую таблицу:

2. Ввести в столбец В функции, указанные в столбце А, используя команду Вставка – Функция. В диалоговом окне Мастер функций использовать категории Математическая и Статическая.

3. Скопировать таблицу на Лист 2 и отобразить формулы.

Упражнение 2. Создать таблицу с использованием логических функций.

1. На третьем листе книги ввести следующую таблицу:

2. В ячейку С2 ввести формулу, по которой будет вычислена скидка на товар: если стоимость товара < 2000, то скидка составляет 5% от стоимости товара, иначе – 10%. Для этого необходимо выполнить команду Вставка – Функция. В диалоговом окне Мастер функций выбрать категорию Логические, в поле Выберите функцию выбрать функцию ЕСЛИ и нажать кнопку ОК. В появившемся диалоговом окне Аргументы функции в поле Лог_выражение ввести В2< 2000, в поле Значение_если_истина – 5%, в поле Значение_если_ложь – 10%. Нажать кнопку ОК.

3. Скопировать формулу в диапазон С3: С11.

4. В ячейку D2 ввести формулу, по которой вычисляется стоимость товара с учетом скидки и скопировать ее в диапазон D3: D11.

5. В ячейку Е2 ввести формулу, определяющую налог: если разность между стоимостью товара и стоимостью со скидкой > 5000, то налог составит 5% от этой разности, иначе – 2%. Для этого необходимо выполнить команду Вставка – Функция. В диалоговом окне Мастер функций выбрать категорию Логические, в поле Выберите функцию выбрать функцию ЕСЛИ и нажать кнопку ОК.

6. В появившемся диалоговом окне Аргументы функции в поле Лог_выражение ввести (B2-D2)> 5000, в поле Значение_если_истина – (B2-D2)*5%, в поле Значение_если_ложь – (B2-D2)*2%. Нажать кнопку ОК.

7. Скопировать формулу в диапазон Е3: Е11.

8. Скопировать созданную таблицу на Лист4 и отобразить формулы.

Упражнение 3. Создать таблицу с использованием вложенных функций.

1. На Листе5 создать следующую таблицу.

2. В ячейке Е10 вычислить сумму баллов каждого студента за выполнение трех заданий.

3. В ячейку F10 ввести формулу для вычисления отметки студента, используя функцию ЕСЛИ. Для этого выполнить следующие действия:

Ø в ячейку F10 ввести функцию ЕСЛИ;

Ø в поле Лог_выражение ввести условие Е10< 16;

Ø в поле Значение_если_истина ввести «отметка 2»;

Ø установить курсор в поле Значение_если_ложь;

Ø Вызвать вторую функцию ЕСЛИ. Вложенные функции выбираются из списка, обведенного рамкой на рис.2.6.

Рис.2.6. Пример выбора вложенной функции

4. Появится чистое диалоговое окно вложенной функции ЕСЛИ, в котором нужно ввести:

Ø в поле Лог_выражение ввести условие Е10< 22;

Ø в поле Значение_если_истина ввести «отметка 3»;

Ø поле Значение_если_ложь вызвать третью функцию ЕСЛИ;

Ø в поле Лог_выражение – Е10< 29;

Ø в поле Значение_если_истина – «отметка 4»;

Ø поле Значение_если_ложь – «отметка 5»;

Ø нажать кнопку ОК.

4. Скопировать формулу в ячейки F11: F14.

5. Скопировать созданную таблицу на Лист6 и отобразить формулы.

Упражнение 4. Создать таблицу с использованием функций Дата и время.

1. На Листе7 в ячейку А1 ввести «Текущая дата».

2. В ячейку А2 ввести «Размер премии».

3. В ячейку В1 ввести текущую дату с помощью функции СЕГОДНЯ. Для этого вызвать Мастер функций командой Вставка – Функция. В диалоговом окне Мастер функций выбрать категорию Дата и время, в поле Выберите функцию выбрать функцию СЕГОДНЯ и нажать кнопку ОК.

4. В ячейку А2 ввести размер премии 1500, 00р., при этом задать формат ячейки Денежный.

5. В ячейки с А4 по F4, ввести следующие данные:

6. Столбец №п/п заполнить числами с 1 по 15, используя автозаполнение.

7. Столбец Фамилия заполнить фамилиями.

8. Столбец Дата рождения заполнить датами, предварительно установив формат ячеек Дата и время.

9. В ячейках D5: D19 создать формулу для вычисления возраста. Для этого необходимо в ячейку D5 ввести следующую формулу:

= ГОД($С$1) – ГОД(С5)

Функция ГОД(дата_в_числовом формате) возвращает год от 1900 до 9999.

10. Скопировать формулу из ячейки D6: D19.

11. В ячейках столбца Юбилей должен содержаться текст «юбилей», либо символ «–» в зависимости от того, какое число содержится в соседней ячейке слева.

Только если в ячейках D5: D19 содержится число кратное 5, будем считать возраст юбилейным.

Чтобы создать формулу, определяющую юбилейный возраст, нужно воспользоваться логической функцией ЕСЛИ. Правильно созданная формула должна иметь вид:

=ЕСЛИ(ОСТАТ(D5; 5)=0; «юбилей»; «–»)

В логическом выражении приведенной формулы проверяется условие кратности 5 числа в ячейке D5. Для этого используется математическая функция ОСТАТ.

12. Скопировать формулу из ячейки Е5 в ячейки Е6: Е19.

13. С помощью Условного форматирования ячейки с текстом юбилей оформить шрифтом курсив полужирный, красного цвета с помощью команды Формат – Условное форматирование…

14. Заполнить ячейки F5: F19 в соответствии со следующим: премия начисляется только тем лицам, у которых юбилей.

Для решения этой задачи необходимо использовать функцию ЕСЛИ. Правильно созданная формула в ячейке F5 должна иметь вид:

=ЕСЛИ(Е5=”юбилей”; $C$2; ”–“)

15. Вычислить, сколько людей-юбиляров в таблице, используя функцию СЧЕТЕСЛИ.

16. Вычислить, сколько людей в таблице старше 25 лет, используя функцию СЧЕТЕСЛИ.

17. Вычислить, сколько людей в таблице моложе 24, используя функцию СЧЕТЕСЛИ.

Контрольные вопросы

1. Дать определение оператора в формуле.

2. Виды операторов. Назначение каждого из них

3. Что такое функция?

4. Что может быть указано в качестве аргумента функции?

5. Правило ввода формулы.

6. Что такое вложенная функция?

7. Какие могут возникать ошибки при вычислениях?

8. Что такое мастер функций?

9. Группы функций. Назначение каждой из них.