Лабораторная работа 3 Тема- ИСПОЛЬЗОВАНИЕ ФУНКЦИЙ В РАСЧЕТАХ MS EXCEL Цель занятия
Работа добавлена на сайт samzan.net: 2016-03-05
Поможем написать учебную работу
Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
от 25%
Подписываем
договор
Лабораторная работа №3
Тема: ИСПОЛЬЗОВАНИЕ ФУНКЦИЙ В РАСЧЕТАХ MS EXCEL
Цель занятия. Изучение информационной технологии организации расчетов с использованием встроенных функций в таблицах MS Excel.
Задание 1. Создать таблицу динамики розничных цен и произвести расчет средних значений.
Порядок работы
- Запустите редактор электронных таблиц Microsoft Excel (Пуск Программы Microsoft office Microsoft Excel).
- Переименуйте ярлык Лист 1, присвоив ему имя «Динамика цен».
- На листе «Динамика цен» создайте таблицу по образцу.
- Произведите расчет изменения цены в колонке «Е» по формуле:
Изменение цены =
Цена на 01. 06.2003/Цена на 01. 04.2003
Не забудьте задать процентный формат чисел в колонке «Е».
- Рассчитайте средние значения по колонкам, пользуясь мастером функций Функция СРЗНАЧ находится в разделе «Статистические». Для расчета функции среднего значения установите курсор в соответствующей ячейке для расчета среднего значения (В14), запустите мастер функций (кнопкой Вставка функции в строке формул или на вкладке Формулы группа Библиотека функций кнопка Вставить функцию или вызвать меню к кнопке категория Статистические функция СРЗНАЧ).
- После нажатия на кнопку ОК откроется окно для выбора диапазона данных для вычисления заданной функции. В качестве первого числа выделите группу ячеек с данными для расчета среднего значения В6:В13 и нажмите кнопку ОК. В ячейке В14 появится среднее значение данных колонки «В».
- Аналогично рассчитайте средние значения в других колонках.
- В ячейке А2 задайте функцию СЕГОДНЯ, отображающую текущую дату, установленную в компьютере (вкладка Формулы группа Библиотека функций вызвать меню к кнопке функция СЕГОДНЯ).
- Выполните текущее сохранение файла «Лабораторная работа 3».
Задание 2. Создать таблицу изменения количества рабочих дней наемных работников и произвести расчет средних значений. Построить график по данным таблицы.
Исходные данные:
- На листе 2 создайте таблицу по заданию. Объединение выделенных ячеек произведите используя кнопку Объединить и поместить в центре на вкладке Главная группа Выравнивание или вызовите диалоговое окно Формат ячеек вкладка Выравнивание установите флажок .
Краткая справка. Изменение направления текста производится путем поворота текста на 90°, вызываемого на вкладке Главная группа Выравнивание вызовите диалоговое окно Формат ячеек вкладка Выравнивание установите Ориентацию — поворот надписи на 90°.
- Произведите расчет средних значений по строкам и столбцам с использованием функции СРЗНАЧ.
- Постройте график изменения количества рабочих дней по годам и странам. Подписи оси «X» задайте с помощью вкладки Работа с диаграммой вкладка Конструктор группа Данные щелкнуть по кнопке Выбрать данные в появившемся окне в области Элементы легенды (ряды) выделить пункт Ряд1, а в области Подписи горизонтальной оси выбрать кнопку Изменить. В открывшемся окне Подписи оси указать интервал ячеек B3:M3. Нажать ОК.
- После построения графика произведите форматирование вертикальной оси, задав минимальное значение 1500, максимальное значение 2500, цену деления 100. Для форматирования оси выполните щелчок правой кнопкой мыши по ней, выбрать в контекстном меню пункт Формат оси и в разделе Параметры оси и задать соответствующие значения.
- Выполните текущее сохранение файла «Лабораторная работа 3».
Задание 3. Применение функции ЕСЛИ при проверке условий. Создать таблицу расчета премии за экономию горючесмазочных материалов (ГСМ). Исходные данные:
- На очередном свободном листе электронной книги создайте таблицу по заданию.
- Произвести расчет Премии (25 % от базовой ставки) по формуле
Премия =
Базовая ставка * 0,25 при условии, что
План расходования ГСМ > Фактически израсходовано ГСМ.
Для расчета Премии установите курсор в ячейке F4, запустите мастер функций и выберите функцию ЕСЛИ (категория - Логические ЕСЛИ).
- Задайте условие и параметры функции ЕСЛИ.
В первой строке «Логическое выражение» задайте условие С4 > D4.
Во второй строке задайте формулу расчета премии, если условие выполняется Е4*0,25.
В третьей строке задайте значение 0, поскольку в этом случае премия не начисляется.
- Произведите сортировку по столбцу фактического расходования ГСМ по возрастанию. Для этого установите курсор в ячейку D4 и выберите на вкладке Данные группа Сортировка и фильтр щелкните по кнопке .
Задание 4. Скопировать таблицу котировки курса доллара (лист «Курс доллара») и произвести под таблицей расчет средних значений, максимального и минимального значений курсов покупки и продажи доллара. Расчет произвести с использованием «Мастера функций».
- Откройте одновременно файлы MS Excel «Лабораторная работа 1» и «Лабораторная работа 3»
- Скопируйте содержимое листа «Курс доллара» (Лабораторная работа 1, лист 1) на новый лист . Для этого воспользоваться командой Переместить /Скопировать контекстного меню ярлыка.
- В разделе Переместить выбранные листы в книгу: выберите «Лабораторная работа 3». Не забудьте для копирования поставить галочку в окошке Создавать копию.
- Перемещать и копировать листы можно перетаскивая их ярлычки, а для копирования удерживайте нажатой клавишу [Ctrl] (это осуществляется в пределах одной книги).
- Рассчитайте максимальное, минимальное и среднее значения по колонкам «Курс покупки» и «Курс продажи», пользуясь мастером функций. Функция СРЗНАЧ, МАКС, МИН находятся в разделе «Статистические».
- Сохраните изменения и распечатайте результаты выполнения лабораторной работы.
ОТНОСИТЕЛЬНАЯ И АБСОЛЮТНАЯ АДРЕСАЦИЯ MS EXCEL
Задание 5. Создать таблицу расчета рентабельности продукции. Константы вводить в расчетные формулы в виде абсолютной адресации. Исходные данные представлены ниже:
- На новом листе электронной книги создайте таблицу констант (отпускная цена одного изделия) и основную расчетную таблицу по заданию.
- Введите исходные данные. При вводе номеров в колонку «А» (числа 1, 2, 3 и т.д.) используйте прием автозаполнения ряда чисел. Для этого наберите два первых числа ряда (числа 1 и 2), выделите их мышкой и подведите курсор к правому нижнему углу выделенных ячеек до изменения вида курсора на черный крестик. Прихватите мышью маркер автозаполнения и протяните его вниз до нужного значения — произойдет создание ряда натуральных чисел (арифметическая прогрессия).
- Выделите цветом ячейку со значением константы — отпускной цены 57,00 р.
Рекомендации. Для удобства работы и формирования навыков работы с абсолютным видом адресации, рекомендуется при оформлении констант окрашивать ячейку цветом, отличным от цвета расчетной таблицы. Тогда при вводе формул окрашенная ячейка (т.е. ячейка с константой) будет вам напоминанием, что следует установить абсолютную адресацию (набором символа $ с клавиатуры или нажатием клавиши [F4]).
- Произведите расчеты во всех строках таблицы. Формулы для расчета:
Выпуск продукции = Количество выпущенных изделий * Отпускная цена одного изделия,
в ячейку С7 введите формулу = С5*$Е$2 (ячейка Е2 задана в виде абсолютной адресации);
Себестоимость выпускаемой продукции = Количество выпущенных изделий * Себестоимость одного изделия,
в ячейку С8 введите формулу = С5*С6;
Прибыль от реализации продукции = Выпуск продукции — Себестоимость выпускаемой продукции,
в ячейку С9 введите формулу = С7-С8;
Рентабельность продукции = Прибыль от реализации продукции/Себестоимость выпускаемой продукции,
в ячейку С10 введите формулу = С9/С8.
- На строку расчета рентабельности продукции наложите Процентный формат чисел. Остальные расчеты производите в Денежном формате.
- Выполните текущее сохранение файла.
Задание 6. Создать таблицу расчета дохода сотрудников организации. Константы вводить в расчетные формулы в виде абсолютной адресации. Исходные данные представлены ниже:
- На очередном свободном листе электронной книги создайте таблицу по заданию.
- Введите значения констант и исходные данные. Форматы данных (денежный или процентный) задайте по образцу задания.
- Произведите расчеты, применяя к константам абсолютную адресацию. Формулы для расчетов:
Подоходный налог = (Оклад - Необлагаемый налогом доход) * % подоходного налога,
в ячейку D10 введите формулу = (С10-$С$3)*$С$4;
Отчисления в благотворительный фонд = Оклад * % отчисления в благотворительный фонд,
в ячейку Е10 введите формулу = С10*$С$5;
Всего удержано = Подоходный налог + Отчисления в благотворительный фонд,
в ячейку F10 введите формулу = D10 + E10;
К выдаче = Оклад - Всего удержано,
в ячейку G10 введите формулу = C10-F10.
4. Постройте объемную гистограмму по данным столбца «К выдаче», проведите форматирование диаграммы.
- Переименуйте лист электронной книги, присвоив ему имя «Доход сотрудников».
- Выполните текущее сохранение файла.
Задание 7. Создать таблицу расчета квартальной и годовой прибыли. Константы вводить в расчетные формулы в виде абсолютной адресации. Исходные данные:
Доход = Розничная цена * Кол-во проданных изделий
Себестоимость = Розничная цена * Процент себестоимости
Прибыль = Доход - Себестоимость - Расходы