8 Обработка больших наборов данных в EXCEL

Постановка задачи: Создать базу данных в виде отчета по текущим покупкам товаров у различных фирм.

Исходные данные: Задайте произвольно название 5-6 фирм, у которых приобретается 5-6 наименований товаров.

Требование: Поскольку эффективность и наглядность методов обработки проявляется только для больших таблиц, число строк в создаваемой таблице должно быть достаточно большим (30-40).

Выполнить: Прогнозирование графическим способом. Построить: Диаграммы по различным фирмам, товарам, объемам покупок.

Образец работы приводится в конце, после описания порядка работы.

Порядок работы:

1. Создайте шапку таблицы;

2. Введите 30-40 строк в произвольном порядке, обязательно используя одинаковое написание названий фирм, товаров и дат;

3. Оформите границы таблицы, для чего выберите в строке меню Формат – Ячейки – Границы;

4. Выполните сортировку по возрастанию данных столбца Дата покупки, для чего выделить любую клетку столбца и нажать кнопку Сортировки на панели инструментов;

5. Выполнить сортировку данных по нескольким столбцам, войдите в меню Данные – Сортировка, затем в диалоговом окне задать до трех признаков сортировки;

6. Выполните отбор из таблицы некоторых строк - записей с помощью специального инструмента ФИЛЬТР: команда меню Данные – Фильтр – Автофильтр. В каждом столбце в шапки таблицы появляется кнопка для выбора условия отбора строк-записей: отбор ВСЕХ записей, отбор ПЕРВЫХ ДЕСЯТИ записей, отбор по заданному УСЛОВИЮ, отбор по конкретному ЗНАЧЕНИЮ для данного столбца.

НЕ нашли? Не то? Что вы ищете?

Наибольшие возможности предоставляются при задании УСЛОВИЯ отбора: надо ввести операцию (РАВНО, БОЛЬШЕ, МЕНЬШЕ и т. д.) и значение, имеющее смысл для данного столбца.

С помощью АВТОФИЛЬТРА можно задать одновременно условия для нескольких столбцов. (Например: Отобрать все покупки у данной фирмы, заключенные не позже заданной даты, имеющей объем не меньше заданной величины и стоимость не дороже заданной величины);

7. Выполнить подведение промежуточных итогов (например, суммарные итого отдельно по каждой фирме). Для этого выполнить сортировку по столбцу ФИРМА, выбрать команду Данные – Итоги и в диалоговом окне выбрать операцию (например - СУММА) и столбец для итоговых данных (например - ОБЪЕМ ПОКУПКИ).

8. Найти прогнозируемое значение объема покупки некоторого товара на основе имеющихся фактических данных. Для этого выполнить отбор нужного товара с помощью АВТОФИЛЬТРА, упорядочить отобранные данные по дате покупки. Выбрать пустую клетку в столбце ОБЪЕМ ПОКУПКИ для вывода результата прогноза, вызвать стандартную функцию ТЕНДЕНЦИЯ, задать мышью диапазон известных значений аргумента (у) — объемы покупки (остальные аргументы функции можно не задавать).

9. Выполнить прогнозирование графическим способом: выделить диапазон с исходными объемами покупок вместе с 1-2 пустыми клетками, построить по этим значениям столбчатую диаграмму (Вставка – Диаграмма), щелкнуть в первом (левом) столбце правой кнопкой мыши, выбрать команду Диаграмма – Добавить линию тренда, выбрать вид сглаживающей линии.

10. Построить диаграммы по различным фирмам, товарам, объемам покупок.

Образец работы

фирма

товар

кол-во

цена за единицу

сумма

дата

АО МММ

стулья

20

5

100

12.12.97

БУМ

рога

12

3

36

01.02.98

Копыта

кости

13

4

52

08.10.98

БУМ

дерево

13

3

39

10.10.98

Рога

рога

20

4

80

05.06.98

….

….

….

….

….

….

(Примерный вид таблицы отчета)

фирма

товар

кол-во

цена за единицу

сумма

дата

Копыта

рога

43

5

215

01.02.98

Копыта

рога

54

3

162

03.04.98

Рога

рога

20

4

80

05.06.98

Рога

рога

6

4

24

08.08.98

Рога

рога

20

7

140

13.08.98

148.4667

(Примерный вид отчета после отбора нужного товара (рога) с помощью АВТОФИЛЬТРА с результатом прогноза)

Прогнозирование графическим способом – диаграмма с линией тренда