1.    Постановка задачи

1. Создать таблицу и заполнить ее следующими данными:

Выпуск продукции предприятиями масложиркомбината

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 и нажмем кнопку Автосумма на панели инструментов.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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