Лабораторная работа №3 ИСПОЛЬЗОВАНИЕ РАЗЛИЧНЫХ КАТЕГОРИЙ ФУНКЦИЙ MS EXCEL

Выполнена Лабораторная работа №3 ИСПОЛЬЗОВАНИЕ РАЗЛИЧНЫХ КАТЕГОРИЙ ФУНКЦИЙ MS EXCEL

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

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

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

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

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

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

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

4. Появится чистое диалоговое окно вложенной функции

ЕСЛИ, в котором нужно ввести:

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

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

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

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

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

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

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

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

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

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

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

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

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

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