Для групп Тт1-43 и Тп1-06 по информатике ЛПР № 18-20
учебно-методический материал

Практическая работа 18

Тема: ИСПОЛЬЗОВАНИЕ ФУНКЦИЙ В РАСЧЕТАХ MS EXCEL

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

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

Исходные данные представлены на рис. 18.1.

Порядок работы

1. Запустите редактор электронных таблиц Microsoft Excel (при стандартной установке MS Office выполните Пуск/Программы/Microsoft Excel).

2. Откройте файл «Расчеты», созданный в Практических работах 16...17 (Файл/Открыть).

Рис. 18.1. Исходные данные для задания 18.1

3. Переименуйте ярлычок Лист 5, присвоив ему имя «Динамика цен».

4. На листе «Динамика цен» создайте таблицу по образцу, как на рис. 18.1.

5. Произведите расчет изменения цены в колонке «Е» по формуле

Изменение цены = Цена на 01.06.2003/Цена на 01.04.2003.

Не забудьте задать процентный формат чисел в колонке «Е» (Формат/ Ячейки/ Число/Процентный).

6. Рассчитайте средние значения по колонкам, пользуясь мастером функций fx. Функция СРЗНАЧ находится в разделе «Статистические». Для расчета функции среднего значения установите курсор в соответствующей ячейке для расчета среднего значения (В14), запустите мастер функций (кнопкой Вставка функции fx или командой Вставка/Функция) и на первом шаге мастера выберите функцию СРЗНАЧ (категория Статистические/СРЗНАЧ) (рис. 18.2).

После нажатия на кнопку ОК откроется окно для выбора диапазона данных для вычисления заданной функции. В качестве первого числа выделите группу ячеек с данными для расчета среднего значения В6:В13 и нажмите кнопку ОК (рис. 18.3). В ячейке В14 появится среднее значение данных колонки «В».

Аналогично рассчитайте средние значения в других колонках.

7. В ячейке А2 задайте функцию СЕГОДНЯ, отображающую текущую дату, установленную в компьютере (Вставка/Функция/ Дата и Время/Сегодня).

8. Выполните текущее сохранение файла (Файл/Сохранить).

Рис. 18.2. Выбор функции расчета среднего значения СРЗНАЧ

Рис. 18.3. Выбор диапазона данных для расчета функции среднего значения

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

Исходные данные представлены на рис. 18.4.

Порядок работы

1. На очередном свободном листе электронной книги «Расчеты» создайте таблицу по заданию. Объединение выделенных ячеек произведите кнопкой панели инструментов Объединить и поместить в центре или командой меню (Формат/Ячейки/вкладка Выравнивание/отображение – Объединение ячеек).

Рис. 18.4. Исходные данные для задания 18.2

Краткая справка. Изменение направления текста в ячейках производится путем поворота текста на 90° в зоне Ориентация окна Формат ячеек, вызываемого командой Формат/ Ячейки/вкладка Выравнивание/ Ориентация – поворот надписи на 90° (рис. 18.5).

Рис. 18.5. Поворот надписи на 90°

Рис. 18.6. Задание параметров шкалы оси графика

2. Произвести расчет средних значений по строкам и столбцам с использованием функции СРЗНАЧ.

3. Построить график изменения количества рабочих дней по годам и странам. Подписи оси «X» задайте при построении графика на втором экране мастера диаграмм (вкладка Ряд, область Подписи оси «X»).

4. После построения графика произведите форматирование вертикальной оси, задав минимальное значение 1500, максимальное значение 2500, цену деления 100 (рис. 18.6). Для форматирования оси выполните двойной щелчок мыши по ней и на вкладке Шкала диалогового окна Формат оси задайте соответствующие параметры оси.

5. Выполните текущее сохранение файла «Расчеты» {Файл/Сохранить).

Задание 18.3. Применение функции ЕСЛИ при проверке условий. Создать таблицу расчета премии за экономию горючесмазочных материалов (ГСМ).

Исходные данные представлены на рис. 18.7.

Порядок работы

1. На очередном свободном листе электронной книги «Расчеты» создайте таблицу по заданию.

2. Произвести расчет Премии (25 % от базовой ставки) по формуле

Премия = Базовая ставка х 0,25 при условии, что

План расходования ГСМ > Фактически израсходовано ГСМ.

Рис. 18.7. Исходные данные для задания 18.3

Для проверки условия используйте функцию ЕСЛИ.

Для расчета Премии установите курсор в ячейке F4, запустите мастер функций (кнопкой Вставка функции fx или командой Вставка/Функция) и выберите функцию ЕСЛИ (категория – Логические/ ЕСЛИ).

Задайте условие и параметры функции ЕСЛИ (рис. 18.8).

В первой строке «Логическое выражение» задайте условие С4 > D4.

Во второй строке задайте формулу расчета премии, если условие выполняется Е4 * 0,25.

В третьей строке задайте значение 0, поскольку в этом случае (невыполнение условия) премия не начисляется.

3. Произведите сортировку по столбцу фактического расходования ГСМ по возрастанию. Для сортировки установите курсор на любую ячейку таблицы, выберите в меню Данные команду

Рис. 18.8. Задание параметров функции ЕСЛИ

Рис. 18.9. Задание параметров сортировки данных

Рис. 18.10. Конечный вид задания 18.3

Сортировка, задайте сортировку по столбцу «Фактически израсходовано ГСМ» (рис. 18.9).

4. Конечный вид расчетной таблицы начисления премии приведен на рис. 18.10.

5. Выполните текущее сохранение файла «Расчеты» (Файл/Сохранить).

Дополнительное задание

Задание 18.4. Скопировать таблицу котировки курса доллара (задание 16.1, лист «Курс доллара») и произвести под таблицей расчет средних значений, максимального и минимального значений курсов покупки и продажи доллара. Расчет произвести с использованием «Мастера функций».

Скопируйте содержимое листа «Курс доллара» на новый лист {Правка/'Переместить/'Скопировать лист). Можно воспользоваться командой Переместить/Скопировать контекстного меню ярлычка. Не забудьте для копирования поставить галочку в окошке Создавать копию (рис. 18.11).

Перемещать и копировать листы можно перетаскивая их ярлычки (для копирования удерживайте нажатой клавишу [Ctrl]).

Краткая справка. Для выделения максимального/минимального значений установите курсор в ячейке расчета, выберите встроенную функцию Excel МАКС {МИН) из категории

Рис. 18.11. Копирование листа электронной книги

«Статистические», в качестве первого числа выделите диапазон ячеек значений столбца В4: В23 (для второго расчета выделите диапазон С4: С23).

Практическая работа 19

Тема: ОТНОСИТЕЛЬНАЯ И АБСОЛЮТНАЯ АДРЕСАЦИЯ MS EXCEL

Цель занятия. Изучение информационной технологии организации расчетов с абсолютной адресацией данных (при работе с константами) в таблицах MS Excel.

Задание 19.1. Создать таблицу расчета рентабельности продукции. Константы вводить в расчетные формулы в виде абсолютной адресации.

Исходные данные представлены на рис. 19.1.

Рис. 19.1. Исходные данные для задания 19.1

Порядок работы

1. Запустите редактор электронных таблиц Microsoft Excel (при стандартной установке MS Office выполните Пуск/Программы/ Microsoft Excel).

2. Откройте файл «Расчеты», созданный в Практических работах № 16... 18 (Файл/Открыть).

3. На новом листе электронной книги «Расчеты» создайте таблицу констант (отпускная цена одного изделия) и основную расчетную таблицу по заданию.

4. Введите исходные данные. При вводе номеров в колонку «А» (числа 1, 2, 3 и т.д.) используйте прием автозаполнения ряда чисел. Для этого наберите два первых числа ряда (числа 1 и 2), выделите их мышкой и подведите курсор к правому нижнему углу выделенных ячеек до изменения вида курсора на черный крестик. Прихватите мышью маркер автозаполнения и протяните его вниз до нужного значения – произойдет создание ряда натуральных чисел (арифметическая прогрессия).

5. Выделите цветом ячейку со значением константы – отпускной цены 57,00 р.

Рекомендации. Для удобства работы и формирования навыков работы с абсолютным видом адресации, рекомендуется при оформлении констант окрашивать ячейку цветом, отличным от цвета расчетной таблицы. Тогда при вводе формул окрашенная ячейка (т.е. ячейка с константой) будет вам напоминанием, что следует установить абсолютную адресацию (набором символа $ с клавиатуры или нажатием клавиши [F4]).

6. Произведите расчеты во всех строках таблицы. Формулы для расчета:

Выпуск продукции = Количество выпущенных изделий х Отпускная цена одного изделия, в ячейку С7 введите формулу = С5*$Е$2 (ячейка Е2 задана в виде абсолютной адресации); Себестоимость выпускаемой продукции = Количество выпущенных изделий х Себестоимость одного изделия, в ячейку С8 введите формулу = С5*С6;

Прибыль от реализации продукции = Выпуск продукции – Себестоимость выпускаемой продукции, в ячейку С9 введите формулу = С7-С8;

Рентабельность продукции = Прибыль от реализации продукции/Себестоимость выпускаемой продукции, в ячейку СЮ введите формулу = С9/С8.

На строку расчета рентабельности продукции наложите Процентный формат чисел. Остальные расчеты производите в Денежном формате.

Формулы из колонки «С» скопируйте автокопированием (за маркер автозаполнения) вправо по строке в колонки «D» и «Е».

7. Выполните текущее сохранение файла (Файл/Сохранить).

Задание 19.2. Создать таблицу расчета дохода сотрудников организации. Константы вводить в расчетные формулы в виде абсолютной адресации.

Исходные данные представлены на рис. 19.2.

Порядок работы

1. На очередном свободном листе электронной книги «Расчеты» создайте таблицу по заданию.

2. Введите значения констант и исходные данные. Форматы данных (денежный или процентный) задайте по образцу задания.

3. Произведите расчеты по формулам, применяя к константам абсолютную адресацию.

Формулы для расчетов:

Подоходный налог = (Оклад - Необлагаемый налогом доход) х х % подоходного налога, в ячейку D10 введите формулу = (С10-$С$3)*$С$4;

Отчисления в благотворительный фонд = Оклад х % отчисления в благотворительный фонд, в ячейку ЕЮ введите формулу = СЮ*$С$5;

Всего удержано = Подоходный налог - Отчисления в благотворительный фонд, в ячейку F10 введите формулу = D10 + E10; К выдаче = Оклад - Всего удержано, в ячейку G10 введите формулу = C10-F10.

4. Постройте объемную гистограмму по данным столбца «К выдаче», проведите форматирование диаграммы.

Рис. 19.2. Исходные данные для задания 19.2

5. Переименуйте лист электронной книги, присвоив ему имя «Доход сотрудников».

6. Выполните текущее сохранение файла (Файл/Сохранить).

Дополнительное задание

Задание 19.3. Создать таблицу расчета квартальной и годовой прибыли. Константы вводить в расчетные формулы в виде абсолютной адресации.

Исходные данные представлены на рис. 19.3.

Рис. 19.3. Исходные данные для задания 19.3

Практическая работа 20

Тема: ФИЛЬТРАЦИЯ ДАННЫХ И УСЛОВНОЕ ФОРМАТИРОВАНИЕ В MS EXCEL

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

Задание 20.1. В таблице «Доход сотрудников» выполнить сортировку и фильтрацию данных.

Порядок работы

1. Запустите редактор электронных таблиц Microsoft Excel. Откройте файл «Расчеты», созданный в Практических работах 16... 19 (Файл/ Открыть).

Рис. 20.1. Задание условия сортировки данных

2. Скопируйте таблицы задания 19.2 (лист «Доход сотрудников»).

3. Произведите сортировку по фамилиям сотрудников в алфавитном порядке по возрастанию (выделите блок ячеек B10:G17 без итогов, выберите в меню Данные команду Сортировка, сортировать по Ф.И.О.) (рис. 20.1).

4. Постройте диаграмму по итогам расчета (данные столбца «К выдаче»). В качестве подписей оси «X» укажите фамилии сотрудников.

5. Произведите фильтрацию значений дохода, превышающих 1600 р.

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

Для установления режима фильтра установите курсор внутри таблицы и воспользуйтесь командой Данные/Фильтр/Автофильтр. В заголовках полей появятся стрелки выпадающих списков. Щелкните по стрелке в заголовке поля, на которое будет наложено условие (в столбце «К выдаче»), и вы увидите список всех неповторяющихся значений этого поля. Выберите команду для фильтрации Условие. В открывшемся окне Пользовательский автофильтр задайте условие – больше 1600 (рис. 20.2).

Рис. 20.2. Выбор условия фильтрации

Рис. 20.3. Конечный вид таблицы и диаграммы после сортировки и фильтрации

Произойдет отбор данных по заданному условию.

Проследите, как изменился вид таблицы и построенная диаграмма.

Конечный вид таблицы и диаграммы после сортировки и фильтрации представлен на рис. 20.3.

6. Выполните текущее сохранение файла (Файл/Сохранить).

Задание 20.2. В таблице «Средняя годовая температура воздуха» выполнить условное форматирование и ввод данных.

Порядок работы

1. На очередном свободном листе электронной книги «Расчеты» создайте таблицу по заданию (рис. 20.4).

2. При наборе месяцев используйте автокопирование, не забудьте повернуть данные на 90°.

3. Используйте автоподбор ширины ячеек, предварительно выделив ячейки (Формат/Столбец/Автоподбор ширины).

4. Проведите условное форматирование значений температур в ячейках В4:М9 (Формат/Условное форматирование) (рис. 20.5).

Установите формат данных:

меньше 0 – синим цветом шрифта (полужирный), равное 0 – зеленый фон, цвет шрифта – белый; больше 0 – красным цветом шрифта (полужирный).

Рис. 20.4. Исходные данные для задания 20.2

Рис. 20.5. Условное форматирование данных

Примечание. Условное форматирование можно задавать как до набора данных, так и после.

5. Выполните текущее сохранение файла (Файл/Сохранить).

Дополнительные задания

Задание 20.3. Скопировать таблицу расчета суммарной выручки (задание 16.2, лист «Выручка») и определить фильтрацией, в какие дни выручка по первому подразделению не превысила 3000 р.

Скопируйте содержимое листа «Выручка» на новый лист (Правка/Переместить–Скопировать лист). Не забудьте для копирования поставить галочку в окошке Создавать копию. Перемещать и копировать листы можно, перетаскивая их корешки (для копирования удерживайте нажатой клавишу [Ctrl]).

Задайте фильтрацию командой Данные/Фильтр/Автофильтр и условие выбора для первого подразделения – меньше или равно 3000 р.

Задание 20.4. Скопировать таблицу котировки курса доллара (задание 16.1, лист «Курс доллара») и провести условное форматирование значений курсов покупки и продажи доллара.

Установите формат данных:

меньше 31,5 – зеленым цветом шрифта (полужирный), больше или равно 31,5 – оранжевым цветом шрифта (полужирный курсив).