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

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

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

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

3. В тетради описать назначение каждой из функций, которые были использованы ранее в п. 2.

4. Скопировать таблицу на Лист 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 ввести формулу для вычисления отметки студента, используя функцию ЕСЛИ. Для этого выполнить следующие действия:

3.1. В ячейку F10 ввести функцию ЕСЛИ.

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

3.3. В поле Значение_если_истина ввести «отметка 2».

3.4. Установить курсор в поле Значение_если_ложь.

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

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

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

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

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

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

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

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

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

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

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

6. Скопировать созданную таблицу на Лист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, используя функцию СЧЕТЕСЛИ.

Лабораторная работа № 2.3
Вычисление значения функции в MS Excel

Цель: научиться использовать встроенные математические функции для вычисления значения функции в MS Excel.