На отдельных рабочих листах выполнить задания

Показатели производства

	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;"Вы получаете бесплатный журнал";"Спасибо за покупку")