Индивидуальная работа № 3. 1 страница

Создание Сводных таблиц и построение графиков.

Вариант 1.

Анализ выполнения товарооборота предприятиями

народного потребления за 2007 год (в тыс. грн.)

Предприятие	Товарооборот
План года	квартал	квартал	квартал	квартал	Всего
ОАО «Заря»		13258,8		6015,6
Фабрика «Мишка»
АОЗТ «БУМ»
ЦУМ 1
Макаронная фабрика1
ТК «Маяк»
Добрыня
ЦУМ 2
ОАО «Заря»
Макаронная фабрика2
Геркулес

1. Определить выполненный товарооборот за год каждым предприятием;

2. Определить общий выполненный товарооборот предприятий, имеющих одинаковый план года.

3. Найти максимальный товарооборот за первые два квартала (1 и 2) и минимальный товарооборот за последние два квартала (3 и 4). Вычислить средний товарооборот за год

4. Построить круговую диаграмму годового товарооборота предприятий

5. В исходной таблице отобрать предприятия, годовой товарооборот которых составил более 2000000 грн., используя расширенный фильтр.

6. Создать сводную таблицу товарооборота предприятий.

7. Определить два предприятия, имеющих максимальный выполненный товарооборот за четыре квартала.

Вариант 2.

Анализ выполнения плана товарооборота

Наименование товара	Розница	Опт	Всего
План	Факт	План	Факт	План	Факт
Диван
Кресло
Стол
Кресло
Диван
Кресло
Стол
Всего

1. Определить:

- Плановую и фактическую продажу всех видов товара по рознице и опту (строка всего);

- Суммарное выполнение товарооборота (план и факт) по каждому наименованию товара (колонка всего);

2. Построить диаграмму, на которой показать суммарный товарооборот товара.

3. Определить суммарный оптовый товарооборот товара по факту по каждому наименованию товара;

4. Определить товары, оптовый товарооборот по плану которых не превышает 2000, используя расширенный фильтр

5. Создать сводную таблицу выполнения товарооборота;

6. Отобрать 2 товара с минимальной фактической оптовой ценой;

7. Провести анализ выполнения товарооборота (план, факт) по каждому виду товара с помощью диаграммы;

8. Сделать тень к шапке таблицы и представить таблицу в режиме формул.

Вариант 3.

Анализ продаж книжной лавки «Лонамэн»

Автор	Название	Изд-во	Цена	Кол-во	Стоимость
Берченко В.	Работа на ПК	Наука	9,0
Руденко С.	Сборник задач	Юность	13,69
Шварц И.	Программирование	Д/пользователя	26,80
Пасько С.	Самоучитель	Юность	17,78
Шварц И.	Изучаем ПК	Наука	34,65
Руденко С.	Практикум	Д\пользователя	11,56
Берченко В.	Текстовый редак.	Библиотека	28,78
Пасько С.	Самоучитель	Наука	27,95

1. Определить: Стоимость продажи каждой книги; Три книги с минимальной ценой; Суммарное количество проданных книг каждого автора.Расположить на лист 2, переименовав его в «общее количество»;

2. Определить суммарную стоимость книг, изданных разными издательствами;

3. Создать круговую диаграмму суммарной стоимости книг, изданных разными издательствами;

4. Создать сводную таблицу продаж книжной лавки;

5. Представить информацию о книгах, стоимость которых < 15000 грн.;

6. Определить общее количество проданных книг каждого издательства;

7. Найти двух авторов, книги которых имеют максимальную цену;

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

Вариант 4.

Анализ продаж книжной лавки «Лонамэн»

Автор	Название	Изд-во	Цена	Кол-во	Стоимость1	Стоимость2
Берченко В.	Работа на ПК	Наука	9,0
Руденко С.	Сборник задач	Юность	13,69
Шварц И.	Программирование	Д/пользователя	26,80
Пасько С.	Самоучитель	Юность	17,78
Шварц И.	Изучаем ПК	Наука	34,65
Руденко С.	Практикум	Д\пользователя	11,56
Берченко В.	Текстовый редак.	Библиотека	28,78
Пасько С.	Самоучитель	Наука	27,95

1. Определить: Стоимость1 проданных книг.

2. Найти Стоимость2 проданных книг, если они будут проданы по цене книги Шварца И. «Изучаем ПК».

3. Определить суммарное количество книг, проданных каждым издательством (Наука, Юность,…).

4. С помощью расширенного фильтра отобрать книги, цена которых не превышает 25 грн.

5. Показать реализацию книг с помощью диаграммы.

6. Определить общую Стоимость1 книг каждого автора.

7. Создать сводную таблицу реализации книг.

8. Представить таблицу в режиме формул.

Вариант 5.

Поставщик	Номер договора	Сумма поставок	Отклонения
По договору	Фактически	Стоимость	%
«Роял»
«Букмер»
«Фаст»
«Букмер»
«Роял»
«Букмер»
«Фаст»
«Роял»
«Букмер»

1. Рассчитать отклонения (ст5=ст.4-ст.3; ст6=ст.4/ст.3*100);

2. Определить фактическую сумму поставок по каждому поставщику. Расположить на Лист 2.

3. Создать график отклонений в % по каждому договору. Расположить на Лист 3.

4. Найти среднеарифметическое отклонение в % по каждому договору.

5. Определить общую сумму плановых поставок по каждому номеру договора и построить график. Расположить на Лист 4.

6. Определить количество договоров для каждого поставщика. Расположить на
Лист 5.

7. Создать сводную таблицу оперативного учета выполнения обязательств по условиям договоров.

8. Используя расширенный фильтр определить поставщиков, у которых фактическая сумма поставок меньше 3000.

Вариант 6.

Анализ оперативного учета выполнения обязательств по договорам за 2002 год

Наименование поставщиков	Номер договора	Тип товара	Сумма поставок
По договору	По факту
«Арис»		Кондитерские
«Центр»		Молочные
«Визон»		Хлебобулочные
«Либерти»		Мясные
«Центр»		Хлебобулочные
«Визон»		Кондитерские
«Либерти»		Молочные
«Арис»		Кондитерские
«Март»		Молочные

1. Определить общую сумму поставок по договорам каждым поставщиком.

2. Создать график суммарного выполнения обязательств по договору каждым поставщиком.

3. Определить общую сумму выполнения обязательств по факту каждым поставщиком.

4. Найти двух поставщиков с минимальной суммой поставок по факту.

5. Определить общую сумму поставок (по договору и по факту) по каждому типу товара.

6. Используя расширенный фильтр, отобрать поставщиков, сумма поставок по договору которых меньше 1000.

7. Создать сводную таблицу учета выполнения обязательств.

Вариант 7.

№ п/п	Туристическая фирма	Маршрут	Общие сведения	Общая стоимость поездки
Км	Кол-во дней	Стоимость одного дня, у.е.	Грн.	У.е.
	САМ	Рим - Париж
	Гамалия	Милан - Стамбул
	Трейс	Донецк - Афины
	Кий&авиа	Лондон - Берлин
	Гамалия	Рига - Москва
	Кий&авиа	Санкт-Петербург - Пекин
	САМ	Киев - Кельн
	Трейс	Амстердам - Венеция

1. Определить колонки 7, 8; среднюю продолжительность круиза; общий километраж круизов всего и по каждому агентству отдельно.

2. Учитывая курс доллара к гривне, равный 5,4, определить общую стоимость каждого круиза в гривнах. Результата представить в денежном формате. Заливку шапки таблицы сделать зеленым и сохранить полученную таблицу на Лист 2, переименовав его самостоятельно.

3. Построить диаграмму; количество дней каждого из предложенных круизов по каждому агентству. Сохранить на Лист 3, переименовав его на «Количество дней круизов».

4. Создать сводную таблицу общей стоимости круизов в долларах по каждому тур. Агентству, сохранить на Лист 4, переименовав его на «Стоимость круизов».

5. Определить круизы, километраж которых находится в пределах от 4000 до $000 км., с указанием общей стоимости круизов в гривнах. Результат сохранить на Листе 5, переименовав его на «Километраж круизов».

6. По данным исходной таблицы определить общую стоимость поездки в гривнах, получаемой каждым тур. агентством. Полученную таблицу сохранить на Лист 6, назвав его самостоятельно.

7. Поставить метку к ячейке с наибольшим километражем.

8. Определить две туристические фирмы с максимальной общей стоимостью поездки.

Вариант 8.

№ п/п	Компания	Ф.И.О.	Заказ на сумму	Курс	% скидки	Личный доход
грн.	у.е.			грн.	у.е.
	Oriflame	Арина О.Л.			5,5
	Avon	Ершова Р.М.			5,4
	Черный жемчуг	Репина О. А.			5,45
	Mary Kay	Орхова С.Ю.			5,45
	Avon	Маева П. Р.			5,4
	Oriflame	Карун В.Д.			5,5
	Mary Kay	Ларина Т.Н.			5,45
	Черный жемчуг	Пех Р.М.			5,45

1. Добавить записи в строке 9,10 (1 компания «Линда», 2 дистрибьютора:
Жукова Г.Н. и Волна С.Ф. с суммами заказа соответственно 182 грн. и 191 грн., курс -5,45, процент скидки - 10%).

2. Определить колонки 5,8,9 общую сумму заказа по каждой компании и в целом. Определить 3-х дистрибьюторов с максимальным личным доходом.

3. Построить диаграмму по общим суммам заказа каждой компании, сохранить на Листе 2, который переименовать на «Сумму заказа».

4. Отсортировать фамилии дистрибьюторов по алфавиту. Сохранить на Листе 3, переименовав его самостоятельно.

5. Колонки 4,5,8,9 представить в денежном формате, а колонку 7 - в процентном. Сохранить на Лист 4, переименовав его на «Формат».

6. По данным исходной таблицы определить общую сумму заказа по каждой компании. Сохранить на Листе 5, переименовав его на «Итоги суммы заказа».

7. Создать сводную таблицу сумм заказов в грн. и долларах отдельно по каждой компании. Сохранить на Лист 6, переименовав его на «Суммы заказов компаний».

8. Исходную таблицу представить в режиме формул и сохранить на Лист 7, назвать его самостоятельно.

Вариант 9.

Магазин	Вид ткани	Начальное сальдо, грн.	Обороты	Конечное сальдо, грн.	Курс	Конечное сальдо в нац. валюте
Дебет	Кредит
Цена	Кол-во	Цена	Кол-во
Иголочка	Шелк		6,95		6,95			8,36
Швея	Хлопок		3,69		3,69			8,36
Мастерица	Шифон		8,20		8,20			8,36
Ниточка	Хлопок		3,96		3,96			8,36
Марина	Шелк		6,95		6,95			8,36
Павлин	Шелк		6,95		6,95			8,36
Каштан	Шифон		8,20		8,20			8,36

1. Определить:

- сумму по дебету и кредиту каждого магазина, предварительно добавив столбцы после столбцов «Количество»;

- конечное сальдо;

- общие показатели по всем магазинам в строке «Итого»;

- средние значения всех показателей.

2. Заголовок таблицы оттенить, установить соответствующий формат ячеек, отсортировать магазины по алфавиту.

3. Построить диаграмму реализации и поступления продукции (по стоимости) по магазинам «Иголочка» и «Марина».

4. На отдельном листе представить всю информацию о магазинах, которые получили товаров на сумму > 146456 и <455072.

5. На разных листах представить информацию о разных видах тканей, назвать листы соответственно «Шелк», «Хлопок» и «Шифон».

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

7. Создать 3 сводные таблицы.

8. Определить 3 магазина с максимальным начальным сальдо.

Вариант 10.

№ п/п	Магазины	Вид товара	Цена	Количество товара	Единица измерения	Валовой доход
	Маг. №4	Бетон	157,52		М^3
	Маг. №2	Горелка			Шт
	Маг. №3	Гвозди	1,833		Кг
	Маг. №4	Гвозди	1,833		Кг
	Маг. №2	Бетон	159,52		М^3
	Маг. №3	Горелка			Шт
	Маг. №1	Гвозди	1,833		Кг
	Маг. №4	Бетон	159,52		М^3
	Маг. №2	Гвозди	1,833	НО	Кг
	Маг. №4	Горелка			Шт

1. Определить: валовой доход (произведение цены за единицу товара на количество реализованной продукции) по магазинам и по району; магазин, валовой доход которого максимальный; среднее количество проданного товара каждого вида по четырем магазинам.

2. Построить диаграмму, на которой показать реализацию каждого вида продукции по каждому магазину, используя все имеющиеся средства редактирования диаграмм. Сохранить ее на лист 2, переименовать лист на «Реализацию продукции».

3. Создать сводную таблицу реализации товара по видам, с указанием номера магазина. Сохранить на лист 3, переименовать его самостоятельно.

4. Определить магазины, продавшие > 50, или <= 90 единиц товара, с указанием вида товара. Сохранить полученную таблицу на лист 4, переименовать его (использовать расширенный фильтр).

По данным исходной таблицы определить суммарный валовой доход каждого магазина. Сохранить полученную таблицу на листе 5, переименовать лист на «Итоги по валовому доходу».

5. Выполнить консолидацию видов товара по их количеству. Консолидированную таблицу сохранить на листе 6, переименовать лист на «Консолидация видов товара».

6. Залить шапку таблицы синим цветом, оттенить, вставить строку заголовка, где указать название таблицы «Валовой доход магазинов района по определенным видам продукции на 01. 2003». - центрировать по ширине таблицы.

7. Представить таблицу, полученную в результате выполнения 1-го задания в режиме формул и скопировать на лист 7, назвав его «Формулы». Исходную таблицу сохранить на листе 1, в режиме вывода результатов. Лист переименовать на «Исходная таблица».

Вариант 11.

О производстве деталей разными цехами завода имеются следующие данные:

Цех	Произведено	Общее кол-во	Общая стоимость
Болты	Гайки	Шурупы
Цена ед.	Брак	Годные	Цена ед.	Брак	Годные	Цена ед.	Брак	Годные	Брак	Годные	Брак	Годные
	0,95			0,68			0,61
	0,95			0,68			0,61
	0,95			0,68			0,61
	0,95			0,68			0,61
	0,95			0,68			0,61
	0,95			0,68	И		0,61

1. Определить: все пустые столбцы; добавить строку «Итого» и определить ее; цех-эталон по максимальной стоимости годных деталей; цех-эталон по минимальному количеству бракованных деталей; каких деталей было произведено больше и каким цехом; общее количество и стоимость деталей в целом по заводу.

2. Построить диаграмму, на которой показать количество годных и бракованных деталей, произведенных 1 и 4 цехами.

3. Название диаграммы написать жирным шрифтом, а подписи данных - курсивом.

4. Определить два цеха, производящих наименьшее количество бракованных шурупов.

5. Создать 2 любые сводные таблицы на разных листах.

6. На отдельном листе представить данные только о производстве годных деталей.

7. Построить нестандартный график общей стоимости годных деталей, произведенных 1, 3 и 5 цехами.

8. Таблицу представить в режиме вывода результатов и в режиму вывода формул.

9. Общую стоимость бракованных деталей представить в денежном формате.

10. Определить два цеха, производящих наименьшее количество бракованных шурупов.

Вариант 12.

Фамилия	Должность	Начислено, грн.
Оклад	Премия	Сверхурочные
Иванов	Столяр	320,6	-	45,8
Прокопенко	Маляр	226,7	100,0	-
Сидорчук	Столяр	361,9	-	-
Мороз	Столяр	318,4	-	64,4
Лесенко	Маляр	243,5	92,0	-
Смирнов	Плотник	192,4	-	40,2
Косарев	Маляр	254,6	80,0	-
Воробьев	Столяр	314,7	54,2	34,4
Хлебников	Плотник	196,1	-	-
Светин	Столяр	325,6	67,4	-

1. Определить:

- в графе 6 - сумму начисления;

- в графе 7 - удержания в пенсионный фонд (2%);

- в графе 8 - удержания в фонд социального страхования (0,5%);

- в графе 9 - подоходный налог в сумме 19,55 + 20% от суммы начисления;

- в графе 10 - сумму удержания;

- в графе 11 - сумму к выдаче;

- в строке «Итого» - сумму по графам 7-10;

- минимальный оклад.

2. Построить диаграмму начисления зарплаты с указанием значения. Диаграмму разместить на отдельном листе с именем «Начисление».

3. Определить рабочих, оклад которых ≥ 230 грн. или ≤ 320 грн. определить рабочих, которые получают премию за выполненную работу. Таблицы разместить на одном листе с именем «Рабочие».

4. Определить суммарный размер премий, начисленных всем малярам и столярам.

5. Создать сводную таблицу начисления зарплаты по профессии. Данные представить на отдельном листе с именем «Зарплата».

6. Построить график удержания подоходного налога с работников. График разместить на отдельном листе.

Дата добавления: 2014-11-18