Содержание

• 1. Постановка задачи
• 2. Создание структуры таблицы
• 3. Проведение расчетов характеристик
• 3.1 Суммарный выпуск продукции за вычетом брака
• 3.2 Вычисление суммарных значений по графам Количество,
• Брак, Сумма
• 3.3 Вычисление суммарных значений выпуска продукции
• по производителям
• 3.4 Вычисление количества продукции по каждому производителю
• 4. Проведение сортировки в таблице
• 5. Использование фильтрации
• 5.1 Отбор производителей, выпускающих майонез
• 5.2 Отбор производителя, выпустившего меньшее количество брака
• 5.3 Отбор продукции, выпускаемой МЖК «Хабаровский»
• 5.4 Производитель, выпускающий майонез упаковкой в 250 г
• 6. Создание сводной таблицы
• 7. Построение накопительной гистограммы по сводной таблице
• Используемая литература
• 1. Постановка задачи
• 1. Создать таблицу и заполнить ее следующими данными:
• Выпуск продукции предприятиями масложиркомбината

Продукция	Производитель	Упаковка, г	Количество, шт	Брак, шт	Выпуск, кг
Маргарин	«Хабаровский»	400	25000	12
Майонез	«Асарти»	250	10000	120
Шоколадное масло	«Хабаровский»	1000	5000	13
Сыр	«Асарти»	500	18000	126
Молоко	«Асарти»	1000	15000	10
Сгущенка	«Хабаровский»	250	30000	21
Масло	«Асарти»	200	12500	36
Маргарин	«Асарти»	1000	11000	65
Плавленый сыр	«Хабаровский»	100	550	25
Сырок глазированный	«Хабаровский»	40	15000	36
ИТОГО:

• 2. Рассчитать следующие характеристики:
• a) суммарный выпуск продукции за вычетом брака;
• b) суммарное значение по графам Количество, Брак, Выпуск (функция СУММ), среднее значение в графе Упаковка (функция СРЗНАЧ);
• c) суммарное значение продукции по производителю (функция СУММЕСЛИ);
• d) количество продукции по каждому производителю (функция СЧЕТЕСЛИ).
• 3. Выполнить сортировку таблицы по производителю, продукции и упаковке.
• 4. С помощью фильтрации получить следующую информацию:
• a) названия производителей, выпускающих майонез;
• b) название производителя, выпускающего меньшее количество брака (предварительно вычислив промежуточные итоги по производителю);
• с) наименование продукции, выпускаемой МЖК «Хабаровский»;
• d) определить производителя, выпускающего майонез упаковкой в 250 грамм.
• 5. Создать сводную таблицу для расчета суммарного выпуска продукции, в строках которой выводятся названия производителя, а в строках - наименование продукции.
• 6. По данным сводной таблицы построить накопительную гистограмму выпуска продукции по производителям.

2. Создание структуры таблицы

После создания заданной структуры таблицы, окно Microsoft Excel будет иметь вид:

3. Проведение расчетов характеристик

3.1 Суммарный выпуск продукции за вычетом брака

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

Выполним такой расчет для первой строки данных таблицы. То есть нужно в ячейку F4 (Выпуск, кг) ввести произведение ячейки С4 (Упаковка, г) на разность D4 (Количество, шт) и Е4 (Брак, шт) и результат поделить на 1000. В итоге в ячейке F5 будет записана формула: «=C4*(D4-E4)/1000».

Для остальных строк таблицы воспользуемся Автозаполнением.

Для вычисления суммарного выпуска продукции перейдем в ячейку F15 и нажмем кнопку Автосумма на панели инструментов.

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

Продукция	Производитель	Упаковка, г	Количество, шт	Брак, шт	Выпуск, кг
Маргарин	Хабаровский	400	25000	12	9995,2
Майонез	Асарти	250	10000	120	2470
Шоколадное масло	Хабаровский	1000	5000	13	4987
Сыр	Асарти	500	18000	126	8937
Молоко	Асарти	1000	15000	10	14990
Сгущенка	Хабаровский	250	30000	21	7494,75
Масло	Асарти	200	12500	36	2492,8
Маргарин	Асарти	1000	11000	65	10935
Плавленый сыр	Хабаровский	100	550	25	52,5
Сырок глазированный	Хабаровский	40	15000	36	598,56
ИТОГО:					62952,81

3.2 Вычисление суммарных значений по графам Количество, Брак, Сумма

Для расчета суммарного значения по графе Количество перейдем в ячейку D15 и нажмем кнопку Автосумма на панели инструментов.

Для суммирования значений по графам Брак и Сумма поступим аналогично.

После выполнения вычислений таблица примет вид:

Продукция	Производитель	Упаковка, г	Количество, шт	Брак, шт	Выпуск, кг
Маргарин	Хабаровский	400	25000	12	9995,2
Майонез	Асарти	250	10000	120	2470
Шоколадное масло	Хабаровский	1000	5000	13	4987
Сыр	Асарти	500	18000	126	8937
Молоко	Асарти	1000	15000	10	14990
Сгущенка	Хабаровский	250	30000	21	7494,75
Масло	Асарти	200	12500	36	2492,8
Маргарин	Асарти	1000	11000	65	10935
Плавленый сыр	Хабаровский	100	550	25	52,5
Сырок глазированный	Хабаровский	40	15000	36	598,56
ИТОГО:			142050	464	62952,81

3.3 Вычисление суммарных значений выпуска продукции по производителям

Выполним расчет суммарного выпуска продукции по производителю «Асарти».

В объединение ячеек А16 - С16 введем поясняющий текст «Суммарный выпуск продукции «Асарти».

Перейдем в ячейку F16. На панели инструментом нажмем стрелочку рядом с кнопкой Автосумма. Из выпадающего списка выберем пункт Другие функции и выберем функцию СУММЕСЛИ.

В качестве Диапазона зададим диапазон В3 - В12, в котором перечислены названия производителей. В качестве Критерия введем текст Асарти. Диапазон суммирования зададим F4:F13, в котором перечислены выпуски продукции в килограммах для каждого вида продукции.

В итоге полученная формула вычисления будет иметь вид: «=СУММЕСЛИ(B4:B13;"Асарти";F4:F13)».

Аналогично выполним суммирование продукции для производителя «Хабаровский», поместив значение в ячейку F17.

В результате таблица примет вид:

Продукция	Производитель	Упаковка, г	Количество, шт	Брак, шт	Выпуск, кг
Маргарин	Хабаровский	400	25000	12	9995,2
Майонез	Асарти	250	10000	120	2470
Шоколадное масло	Хабаровский	1000	5000	13	4987
Сыр	Асарти	500	18000	126	8937
Молоко	Асарти	1000	15000	10	14990
Сгущенка	Хабаровский	250	30000	21	7494,75
Масло	Асарти	200	12500	36	2492,8
Маргарин	Асарти	1000	11000	65	10935
Плавленый сыр	Хабаровский	100	550	25	52,5
Сырок глазированный	Хабаровский	40	15000	36	598,56
ИТОГО:			142050	464	62952,81
Суммарный выпуск продукции "Асарти"			39824,8
Суммарный выпуск продукции "Хабаровский"			23128,01

3.4 Вычисление количества продукции по каждому производителю

Вычислим количество продукции по производителю «Асарти».

Перейдем в ячейку D16. Из списка выбора функций выберем функцию СЧЕТЕСЛИ.

В качестве Диапазона зададим диапазон В14:В13. В качестве Критерия зададим Хабаровский.

В итоге в ячейке F16 будет записана формула =СУММЕСЛИ(B4:B13;"Хабаровский";F4:F13).

Аналогично рассчитаем количество выпускаемой продукции производителем Асарти, изменив только Критерий.

В итоге таблица будет иметь вид:

Продукция	Производитель	Упаковка, г	Количество, шт	Брак, шт	Выпуск, кг
Маргарин	Хабаровский	400	25000	12	9995,2
Майонез	Асарти	250	10000	120	2470
Шоколадное масло	Хабаровский	1000	5000	13	4987
Сыр	Асарти	500	18000	126	8937
Молоко	Асарти	1000	15000	10	14990
Сгущенка	Хабаровский	250	30000	21	7494,75
Масло	Асарти	200	12500	36	2492,8
Маргарин	Асарти	1000	11000	65	10935
Плавленый сыр	Хабаровский	100	550	25	52,5
Сырок глазированный	Хабаровский	40	15000	36	598,56
ИТОГО:			142050	464	62952,81
Суммарный выпуск продукции "Асарти"			39824,8
Суммарный выпуск продукции "Хабаровский"			23128,01
Количество продукции "Асарти"	5
Количество продукции "Хабаровский"	5

4. Проведение сортировки в таблице

Выполним сортировку таблицы по данным столбца Производитель.

Для этого выделим диапазон ячеек В4 - В13, в котором записаны названия производителей и нажмем кнопку Сортировка по возрастанию на панели инструментов.

После упорядочивания по производителю, таблица будет иметь вид:

Продукция	Производитель	Упаковка, г	Количество, шт	Брак, шт	Выпуск, кг
Маргарин	Асарти	400	25000	12	9995,2
Майонез	Асарти	250	10000	120	2470
Шоколадное масло	Асарти	1000	5000	13	4987
Сыр	Асарти	500	18000	126	8937
Молоко	Асарти	1000	15000	10	14990
Сгущенка	Хабаровский	250	30000	21	7494,75
Масло	Хабаровский	200	12500	36	2492,8
Маргарин	Хабаровский	1000	11000	65	10935
Плавленый сыр	Хабаровский	100	550	25	52,5
Сырок глазированный	Хабаровский	40	15000	36	598,56

Аналогично выполним упорядочивание по столбцу «Продукция». Получим:

Продукция	Производитель	Упаковка, г	Количество, шт	Брак, шт	Выпуск, кг
Майонез	Асарти	250	10000	120	2470
Маргарин	Асарти	400	25000	12	9995,2
Маргарин	Хабаровский	1000	11000	65	10935
Масло	Хабаровский	200	12500	36	2492,8
Молоко	Асарти	1000	15000	10	14990
Плавленый сыр	Хабаровский	100	550	25	52,5
Сгущенка	Хабаровский	250	30000	21	7494,75
Сыр	Асарти	500	18000	126	8937
Сырок глазированный	Хабаровский	40	15000	36	598,56
Шоколадное масло	Асарти	1000	5000	13	4987

Теперь отсортируем данные по упаковке. Получим:

Продукция	Производитель	Упаковка, г	Количество, шт	Брак, шт	Выпуск, кг
Сырок глазированный	Хабаровский	40	15000	36	598,56
Плавленый сыр	Хабаровский	100	550	25	52,5
Масло	Хабаровский	200	12500	36	2492,8
Майонез	Асарти	250	10000	120	2470
Сгущенка	Хабаровский	250	30000	21	7494,75
Маргарин	Асарти	400	25000	12	9995,2
Сыр	Асарти	500	18000	126	8937
Маргарин	Хабаровский	1000	11000	65	10935
Молоко	Асарти	1000	15000	10	14990
Шоколадное масло	Асарти	1000	5000	13	4987

5. Использование фильтрации

Для возможности использования фильтра необходимо выполнить пункт главного меню Microsoft Excel Данные>Фильтр>Автофильтр. После этого в заголовках каждого столбца появится выпадающий список автофильтра.

5.1 Отбор производителей, выпускающих майонез

Из выпадающего списка автофильтра для столбца Продукция выберем Майонез.

После применения фильтра будут отображены только данные о производителях майонеза:

Продукция	Производитель	Упаковка, г	Количество, шт	Брак, шт	Выпуск, кг
Майонез	Асарти	250	10000	120	2470

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

В выпадающем списке автофильтра данные столбца упорядочены по возрастанию. Поэтому для отбора производителя, выпустившего меньшее количество брака, выберем первое значение из списка автофильтра. Получим:

Продукция	Производитель	Упаковка, г	Количество, шт	Брак, шт	Выпуск, кг
Молоко	Асарти	1000	15000	10	14990

5.3 Отбор продукции, выпускаемой МЖК «Хабаровский»

Для поля Производитель из списка автофильтра выберем Хабаровский. Получим следующие данные о продукции производителя Хабаровский. Получим:

Продукция	Производитель	Упаковка, г	Количество, шт	Брак, шт	Выпуск, кг
Сырок глазированный	Хабаровский	40	15000	36	598,56
Плавленый сыр	Хабаровский	100	550	25	52,5
Масло	Хабаровский	200	12500	36	2492,8
Сгущенка	Хабаровский	250	30000	21	7494,75
Маргарин	Хабаровский	1000	11000	65	10935

5.4 Производитель, выпускающий майонез упаковкой в 250 г

Для столбца Продукция из списка автофильтра выберем Майонез, а для поля Упаковка, г выберем 250. Получим:

Продукция	Производитель	Упаковка, г	Количество, шт	Брак, шт	Выпуск, кг
Майонез	Асарти	250	10000	120	2470

6. Создание сводной таблицы

Для создания сводной таблицы выберем пункт главного меню Microsoft Excel Данные > Сводная таблица

На первом шаге Мастера сводных таблиц и диаграмм выберем в качестве размещения данных в списке или базе данных Microsoft Excel, а Вид создаваемого отчета - сводная таблица.

В качестве Диапазона зададим диапазон А3-F13.

Разместим полученную сводную таблицу на новом листе.

На следующем шаге зададим структуру сводной таблицы.

В качестве Заголовков строк перетащим поле Производитель. В качестве Заголовков столбцов зададим поле Продукция.

А в раздел Данные перенесем поле Выпуск, кг.

Получим следующую сводную таблицу:

Сумма по полю Выпуск, кг	Продукция
Производитель	Майонез	Маргарин	Масло	Молоко	Плавленый сыр	Сгущенка	Сыр	Сырок глазир.	Шокол. масло	Общий итог
Асарти	2470	9995,2		14990			8937		4987	41379,2
Хабаровский		10935	2492,8		52,5	7494,75		598,56		21573,61
Общий итог	2470	20930,2	2492,8	14990	52,5	7494,75	8937	598,56	4987	62952,81

7. Построение накопительной гистограммы по сводной таблице

На панели инструментов нажмем кнопку Мастер диаграмм.

Следующая сводная диаграмма будет создана мастером автоматически:

Используемая литература

1. Информатика. Под ред. В.А. Острейковского. - Москва. Издательство “Высшая школа”. 2002.

2. Информатика: Учебник. Под редакцией проф. Н.В. Макаровой. - Москва. Издательство “Финансы и статистика”. 2000.

3. Информационные технологии. Учебное пособие. Под ред. А. К. Волкова. - Москва. Издательство “Инфра-М”. 2001 г.

4. Козырев А.А. Самоучитель работы на персональном компьютере. Издание второе переработанное и дополненное. Издательство Михайлова В.А. Санкт-Петербург, 2000 г.

5. Microsoft Office XP. - Москва; Издательство “Диалектика”. 2002 г.