Показатели производства
| 1 квартал | 2 квартал | 3 квартал | 4 квартал | Итого | |
| План | |||||
| Факт | |||||
| % выполн. плана |
1. Сумма по строке считается при помощи автосуммирования
2. % выполн. плана = Факт/План*100
3. Переименовать ярлычок листа в «Показатели»
4. Выполнить обрамление и заливку таблицы
5. Вычисленные значения показать с точностью до 2-х знаков после запятой
6. Построить диаграмму (Тип – Гистограмма), отражающую тенденцию выполнения плана.
Задание2:
Порядок выполнения:
· согласно варианту создать на рабочем листе таблицу
· отформатировать таблицу (выделить полужирным шрифтом заголовок таблицы, итоговые показатели и т.п.)
· провести расчет по формулам
· построить диаграммы:
- гистограмму по результатам расчетов
- круговую по данным одного столбца (или одной строки)
Расчет цен товаров с учетом курса валюты
| Курс $ (К) | 5,7 | |||
| НДС (Н) | ||||
| Наименование продукции | Цена $ (ЦД) | Цена гр. (ЦГ) | Сумма НДС (СН) | Цена реализации (ЦР) |
| Телефон | ||||
| Принтер | ||||
| Факс | ||||
| Модем | ||||
| Сканер | ||||
| Итого | Х | Х | Х | |
| Минимальная цена реализации | ||||
| Максимальная цена реализации |
ЦГ=ЦД*К СН=ЦГ*Н/100 ЦР=ЦГ+СН
Учет движения основных фондов цехов предприятия
| Наименование цеха | На начало года (НГ) | В течение года | На конец года (КГ) | Доля в общем объеме (Д) | В % к началу года (ПНГ) | |
| Поступило (П) | Выбыло (В) | |||||
| Ремонтный | ||||||
| Кузнечный | ||||||
| Литейный | ||||||
| Сборочный | ||||||
| Прокатный | ||||||
| Механический | ||||||
| Итого | ||||||
| Средний показатель на конец года | Х | |||||
| Максимальная доля в общем объеме |
КГ=НГ+П-В Д=КГ/(Итого КГ) * 100 ПНГ=КГ/НГ*100
Задание 3
Вычисление по формулам, копирование формул, вставка рисунков в таблицу
1. Создать таблицу расчёта строительных материалов для ремонта квартиры, подобную той, какая изображена на рис. 1.
2. Ввести в соответствующие ячейки рисунки (сканированные или стандартные из коллекции).
Лабораторная работа №4
Тема:
Работа со встроенными функциями Excel. Построение диаграмм
Цель:
Научиться использовать в формулах встроенные функции Excel, строить по рассчитанным таблицам диаграммы и графики.
К сведению
Для вызова встроенной функции необходимо установить курсор в ячейку, куда будет вводиться формула, а затем выбрать в меню Вставка/Функция
Задание 1
1. На рабочем листе MS Excel ввести и рассчитать следующую таблицу:
Счет покупки №
| A | B | С | D | E | |
| № п/п | Наименование товара | Цена единицы товара | Количество | Стоимость покупки | |
| Товар1 | |||||
| Товар2 | |||||
| Товар3 | |||||
| Итого | Х | Х | |||
| Стоимость со скидкой | Х | ||||
| Скидка | 5% |
Формулы для расчета:
1. Цену и Количество ввести произвольно
2. Стоимость покупки: Цена*Количество
3. Стоимость со скидкой: Итого-Итого*5%.
Если Итого меньше 200 грн, скидки нет; иначе скидка составляет 5%. Используется функция ЕСЛИ (Категория Логические): Если (E5<200;E5;E5-E5*$C$5)
Задание 2
1 На рабочем листе MS Excel ввести и рассчитать следующую таблицу:
| 2. Формулы для расчета: | |||||||
| Срок хранения = (Дата окончания - Дата прихода)/30, целое число | |||||||
| Прибыль вкладчика = Срок хранения*годовые % /12 * Внесённая сумма * Курс $ (абсолютный адрес) | |||||||
3. Ярлычок листа переименовать в «Банк»
4. Построить круговую диаграмму, демонстрирующую прибыль вкладчика.
Лабораторная работа № 5
Тема:
Работа со встроенными функциями Excel. Построение диаграмм
Цель:
Научиться использовать в формулах встроенные функции Excel, строить по рассчитанным таблицам диаграммы и графики.
К сведению
Для вызова встроенной функции необходимо установить курсор в ячейку, куда будет вводиться
формула, а затем выбрать в меню Вставка/Функция
Задание 1
Задание 2.
Сценарии являются частью блока задач, который иногда называют инструментами анализа "что-если". Сценарий — это набор значений, которые Microsoft Excel сохраняет и может автоматически подставлять на листе. Сценарии можно использовать для прогноза результатов моделей и систем расчетов. Существует возможность создать и сохранить на листе различные группы значений, а затем переключаться на любой из этих новых сценариев для просмотра различных результатов.
1.Требуется построить по предложенной форме и рассчитать таблицу следующего вида:
,
где Выручка, Стоимость закупок, Прибыль от реализации считается по формулам:
Выручка=Отпускная цена*Количество продаж
Стоимость закупок=Закупочная цена*Количество продаж
Прибыль от реализации=Выручка-Стоимость закупок
Сумма прибыли может изменяться в зависимости от отпускной цены или количества продаж.
Необходимо подобрать такие значения изменяемых ячеек (Отпускная цена, Количество продаж), при которых сумма прибыли будет максимальной.
Создаются еще два сценария, в которых ячейки В2 и В3 являются изменяемыми и равны соответственно 27 и 250, 32 и 185.
Ход выполнения задания
• скопировать таблицу на новый лист
• выбрать команду Сервис/Сценарии
• выбрать «Добавить»
• вставить имя нового сценария: «базовый»
• перечислить через точку с запятой все ячейки, которые будут изменяться во
всех сценариях: b2; b3.
• не изменять значения ни одной ячейки
• добавить еще один сценарий (кнопка «Добавить»).
• ввести имя: «первый»
• оставить координаты тех же изменяемых ячеек
• ввести соответствующие первому сценарию значения
• аналогично сформировать сценарий «второй».
Для просмотра сценариев можно использовать кнопку Вывести.
Затем, на основании этих сценариев создается отчет, в котором объединяются все результаты расчета и представлены данные для анализа.
Т.о., наиболее предпочтительный вариант 3, где повышается цена. Этот пример очень упрощен, в реальной экономической практике не совсем корректен, однако, хорошо иллюстрирует возможности сценариев и диспетчера сценариев.
Лабораторная работа № 6
Тема:
Работа со списками. Создание. Сортировка. Фильтрация. Итоги.
Цель:
Научиться работать с базами данных в Excel, выполнять отбор, сортировку, фильтрацию данных, подводить простейшие итоги.
К сведению. Работа со списками (базами данных) в MS Excel осуществляется при помощи пункта меню Данные.
Теоретические сведения.
Списки Microsoft Excel предоставляют средства, упрощающие управление и анализ групп связанных данных на листе Excel. Если объявить некоторый диапазон ячеек списком, то данными этого списка можно управлять и анализировать независимо от данных, не вошедших в список. Например, используя только данные из списка, можно фильтровать столбцы, добавлять строки итоговых значений и создавать отчет сводной таблицы.
На листе можно задать несколько списков, что позволяет гибко делить данные на отдельные хорошо управляемые наборы.
ЗАДАНИЕ.
Создать список.
1.1.Создайте таблицу, содержащую сведения о сотрудниках предприятия. Названия колонок: Фамилия, Должность, Отдел, Дата поступления на работу, Зарплата, Стаж работы, Надбавка, Премия, Всего начислено, Пенсионный фонд, Налогооблагаемая база, Налог, Выплатить.
1.2. Первые 5 колонок заполнить произвольными данными (не менее 10), при этом: в графе зарплата данные должны находиться в пределах от 700 до 2700 грн., должности и отделы должны повторяться.
1.3. Значения в остальных колонках рассчитать по формулам:
Стаж работы=(Сегодня()-Дата поступления на работу)/365.
Округлить до целого.
Примечание: функция Сегодня() возвращает текущую дату.
Премия=20%(Запрплата+Надбавка)
Всего начислено=Зарплата+Надбавка+Премия
Пенсионный фонд=1% от Всего начислено
 |
Налогооблагаемая база=Всего начислено –Пенсионный фонд
Выплатить=Налогооблагаемая база-Налог
1.4. Присвоить рабочему листу имя Сведения о сотрудниках.
1.5.Оформить таблицу как список (базу данных). Это означает, что заголовки столбцов должны занимать не более одной строки, не должно быть пустых строк и столбцов внутри таблицы, между таблицей и другими объектами на этом рабочем листе должны быть хотя бы одна пустая строка или столбец.
На отдельных рабочих листах выполнить задания
Варианты заданий
1.
Используя инструмент Итоги, определить промежуточные и общие итоги по полям Зарплата и Надбавка (операция сумма), предварительно отсортировав данные по отделам.
Используя автофильтр отобрать данные о сотрудниках, фамилии которых начинаются на заданную букву.
Используя Расширенный фильтр отобрать данные о сотрудниках с зарплатой менее 1550 грн.
На основании исходной, создать сводную таблицу суммарных выплат по отделам (заголовки столбцов) и фамилиям (заголовки строк). В качестве значений сводной таблицы должны использоваться данные столбца Выплатить, с операцией Сумма.
2.
Используя инструмент Итоги, определить промежуточные и общие итоги по полям Зарплата, Надбавка, Премия, Выплатить (операция сумма), предварительно отсортировав данные по отделам.
Используя автофильтр отобрать данные о сотрудниках, с заданной должностью и с зарплатой больше заданной
Используя Расширенный фильтр, отобрать данные о сотрудниках, конкретного отдела, со стажем не менее 5 лет
На основании исходной создать сводную таблицу, где Отдел – заголовки строк, Должность – заголовки столбцов, Фамилии – значения полей сводной таблицы (функция – Количество значений).
Лабораторная работа №7
Тема:
Работа со встроенными функциями Excel.
Цель:
Научиться использовать в формулах встроенные функции Excel.
Задание 2
| Аптека №15 | ||||||
| Заказ товара № 1 | ||||||
| № п/п | Наименование товара | Цена | Кол-во | Стоимость | ||
| Аскофен | 1,20 | 2,4 | ||||
| Нотта 50 мг | 36,00 | |||||
| Эспумизан | 12,00 | |||||
| Аven пенка д/умыв | 78,00 | |||||
| Аven термальная вода | 86,00 | |||||
| Уголь акт | 1,20 | 2,4 | ||||
| Сумма заказа | 228,8 | |||||
| Вы получаете бесплатный журнал | ||||||
| Задание | ||||||
| 1. Ввести данные в таблицу | ||||||
| 2. Диапазону ячеек Е5:Е10 присвоить имя "Заказ" (Вставка/Имя/Присвоить) | ||||||
| 3. В ячейку В13 ввести формулу: | ||||||
| =ЕСЛИ(СУММ(Заказ)>150;"Вы получаете бесплатный журнал";"Спасибо за покупку") |
