ИНФ Лабораторные работы _Windows, Word,Excel

Лабораторная работа №9 Использование различных категорий функций 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, в поле Значение_если_истина – (B2D2)*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.В ячейку В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.Назовите группы функций и назначение каждой из них?

Лабораторная работа 10 Создание и работа со списками. Сортировка данных

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

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

1.Создатьтаблицувсоответствиисзаданнымобразцом(рис4.12.)

2.ПосчитатьзначениястолбцаF.

3.ЗадатьимярабочеголистаСписок.

4.СпомощьюФормыдобавитькспискуследующиеданные:

Дляэтого:

Курсорустановитьвконцесписка.

Выполнить команду Данные – Форма, в результате чего появится диалоговое окно, заголовоккоторогосовпадаетсименемрабочеголиста.

Рис. 4.12. Примерсписка

Для добавления новой информации необходимо нажать кнопку Добавить. В правом верхнем углу появится надпись Новая запись, после чего можно начать ввод новой информации. Информация вводится построчно, после заполнения всех полей первой записинеобходимонажатькнопкуДобавить, чтобыэтаинформациязанесласьвтаблицу, диалоговоеокноочиститсяиможнопереходитьквводувторойзаписиит.д.

ПослевводавсейинформацииследуетнажатькнопкуЗакрыть.

Сортировкаданныхпоодномустолбцу.

2.Скопировать Список на Лист 2. Расположить данные в списке в порядке возрастанияподате.

3.ПереименоватьЛист2, задавемуимяДата_повозрастанию.

4.Скопировать Список на Лист 3. Расположить данные в списке в порядке возрастанияценыкниг.

5.ПереименоватьЛист3, задавемуимяЦена_повозрастанию.

6.Скопировать СписокнаЛист4. Расположитьданные в списке в порядке убывания количествакниг.

Сортировкаданныхпонесколькимстолбцам

7. Расположить данные в списке в порядке возрастания даты покупки книг, а для одинаковыхдат– поалфавитустолбцаТематика. Дляэтого:

Сделатьтекущейлюбуюячейкувобластисписка.

Выполнить команду Данные – Сортировка, в результате чего появится диалоговое окнодлязаданияусловийсортировки.

ВполеСортироватьповыберитеизспискаимястолбцаДата, условиесортировки–

повозрастанию.

Вполе Затем по выберите из списка имя столбца Тематика, условие сортировки –

повозрастанию.

НажмитекнопкуОК.

Данные будут отсортированы в порядке возрастания дат, а для одинаковых дат – по алфавитутематики.

Сортировкачастисписка

8.Расположитьданныеза3-йкварталвпорядкевозрастанияценыкниг. Дляэтого: СкопироватьСписокнаЛист6 иотсортироватьстолбецсдатамиповозрастанию. Выделитедиапазонячеек, необходимыйдлясортировки(А14:F19).

ВыполнитекомандуДанные– Сортировка.

Вполе Сортировать по выберите из списка имя столбца D (имя столбца, содержащегоинформациюоцене), условиесортировки– повозрастанию.

НажмитекнопкуОК.

9.СохранитетаблицуподименемБазаданных.xls.

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

1.Дать определение базы данных.

2.При каких условиях MS Excel будет считать таблицу списком?

3.Дать определение понятию «Сортировка».

4.Виды сортировок.

5.Как отсортировать список по одному полю?

6.Как отсортировать список по нескольким полям?

Лабораторная работа 11 АНАЛИЗ СПИСКА С ПОМОЩЬЮ РЕЖИМА АВТОФИЛЬТР

Цель: научитьсяобрабатыватьданныеспомощьюАвтофильтра.

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

1.В таблице, созданной в работе 4.1, выведите для просмотра информацию, относящуюсякпокупкамтолькоза2-йквартал, потематикеЭкономика. Дляэтого:

Сделайтетекущейлюбуюячейкувобластисписка.

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

Раскройте список столбца Дата и выберите строчку 2 кв. В результате на экране останетсятолькоинформация, относящаясяко2-мукварталу.

Раскройте список столбца Тематика и выберите строчку Экономика. В результате останутся только строчки, отвечающие требованиям обоих критериев отбора одновременно.

2.Отберитеинформациюокнигах, ценакоторыхбольшеилиравна30, номеньше100 рублей. Дляэтого:

ВыделитевседанныевстолбцеЦена, включаязаголовокстолбца.

ВыполнитекомандуДанные– Фильтр– Автофильтр.

РаскройтесписокстолбцаЦенаивыберитеУсловие…

Вдиалоговом окне Пользовательский автофильтр в поле Цена выберите условие

Большеилиравно.

Вправомверхнемполевыберитезначениедляусловия( внашемслучае30).

Второеусловие(меньше100) вводитсявовторойнаборполей. ПослезаданиявсехусловийнажмитекнопкуОК.

3. Отберите информацию о книгах, тематика которых начинается с буквы К. Для этого:

Выделите все данные в столбце Тематика, включая заголовок столбца.

ВыполнитекомандуДанные– Фильтр– Автофильтр.

РаскройтесписокстолбцаТематикаивыберитеУсловие…

Вдиалоговом окне Пользовательский автофильтр в первом поле задайте

Начинаетсяс.

Вправомверхнемполезадайтезначениедляусловия(внашемслучаебукваК). НажмитекнопкуОК.

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

1.Что такое фильтрация списка?

2.Что такое Автофильтр?

3.Как установить фильтр на списке?

4.Как убрать фильтр со спика?

5.Что такое Пользовательский автофильтр?

6.Как вызвать Пользовательский автофильтр?

7.К каким типам данных применяется переключатель И в диалоговом окне Пользовательский фильтр?

8.К каким типам данных применяется переключатель ИЛИ в диалоговом окне Пользовательский фильтр?