Учет поставок товаров и услуг потребителям

Электронная таблица Excel удобна для учета различных периодических дей­ствий. При этом встроенные средства позволяют автоматизировать консолида­цию значений и отобразить результат в удобном и красивом графическом виде. Отметим, что наиболее удобны средства, встроенные в Excel 2000 и в последующие версии. Предыдущие версии программы обладают только частью возможностей послед­них версий.

Рис.9.1. Поставки за месяц

В предлагаемом примере ведется учет поставок товаров крупной оптовой фирмой, работающей с несколькими покупателями. Поставки в каждом месяце вводятся на отдельном листе рабочей книги, причем заносятся данные по по­ставкам различных групп товаров каждому покупателю. Создайте такую таблицу, чтобы описать поставки за какой-либо месяц (Рис.9.1).

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

Для создания сводной таблицы выберите команду меню Данные → Сводная таблица. Будет запущен мас­тер создания сводных таблиц и на эк­ране появится его первый диалог. С помощью верхнего переключателя на­до выбрать исходные данные для таб­лицы. Так как наша информация находится на нескольких листах, нужно выбрать вариант в нескольких диапазонах кон­солидации. Вкачестве создаваемого отчета выберите с помощью второго пере­ключателя сводная диаграмма (со сводной таблицей). Если вы работаете с Excel 97, сводную диаграмму построить нельзя. Установите переключатели в нужное по­ложение, чтобы перейти к следующему шагу мастера. В диалоге выберите вари­ант Создать одно поле страницы и нажмите кнопку Далее.

В появившемся диалоге нужно задать адреса ячеек с исходными данными. Вы можете вручную ввести нужные адреса, но удобнее обвести ячейки с помо­щью мыши. Щелкните мышью на поле Диапазон в диалоге, перейдите на первый лист с данными, и выделите с помощью мыши ячейки с заголовками и введен­ными данными. Адрес ячеек появится в поле. В нашем случае это адрес Октябрь!$А$4:$Е$10. Первый лист мы назвали Октябрь, а в ячейках с А4 по E10располо­жены введенные данные. Нажмите кнопку Добавить,и адрес будет добавлен в список диапазонов, расположенный в нижней части диалога. Далее перейдите на второй лист и выполните аналогичные действия, после чего добавьте адрес яче­ек с третьей страницы. В итоге в списке диапазонов должны присутствовать три строки со ссылками на нужные ячейки. Нажмите кнопку Далее, чтобы перейти к следующему шагу. При работе с Excel 97 появится диалог настройки таблицы. Ничего не меняя нажмите кнопку Далее. В более поздних версиях этот диалог не появится, и вы сразу перейдете к следующему шагу мастера.

В появившемся диалоге с помощью переключателя выберите построение диаграммы на новом листе. Дополнительные настройки можно не выполнять, приняв значения по умолчанию. Нажмите кнопку Готово, чтобы завершить работу с мастером. На новых листах будут построены сводная таблица и сводная диаграмма.

Перейдите на лист со сводной таблицей. Вначале изменим названия заго­ловков, после чего рассмотрим принципы работы со сводной таблицей. В ячей­ку А1 вместо Страница 1 введите название Месяц. В ячейку A3 вместо Сумма по полю Значение введите Сумма поставок. Вячейку А4 вместо Строка введите То­вар, а в поле ВЗвместо Столбец введите Клиент. В результате вы должны полу­чить примерно такую таблицу (Рис.9.2). В каждой ячейке сводной таблицы выводится сумма поставок за все месяцы определенного товара одному из клиентов.

Рис.9.2. Сводная таблица

 

Нажав кнопку ,расположенную в правой части ячейки В1, вы откроете диалог со списком месяцев, правда, называются они иначе, и их нужно переименовать. Выберите один из элементов списка и нажмите кнопку ОК. В таблице появятся данные о продажах за один из месяцев, то есть копия одного из заполненных ранее листов. Щелкните мышью на ячейке В1 и измените название на название нужного месяца, например, Объект3 на Октябрь. Аналогично введите названия остальных месяцев. Под ко­нец выберите в списке месяцев элемент (Все), чтобы снова вывести суммарную информацию.

9.3. Различный вид диаграмм

В сводной таблице кроме сумм за все месяцы выводятся итоги по отдель­ным товарам и по разным клиентам. Просмотрев эти цифры, вы можете сделать соответствующие выводы, но значительно удобнее работать с графическим представлением информации. Перейдите на лист с диаграммой, в которой ото­бражается та же информация, что и в сводной таблице (Рис.9.3, слева). Напо­минаем, что при работе с Excel 97 сводная диаграмма не создается, но вы може­те выделить сводную таблицу и построить обычную диаграмму. Мы же продол­жим описание работы со сводной диаграммой, которую можно построить в Excel 2000 и более поздних версиях.

Вы можете сильно изменить внешний вид диаграммы (Рис.9.3, справа), выбрав в меню команду Диаграммаи выбирая разные команды во вложенном меню, но главное достоинство сводной диаграммы не в этом. Как и в сводной таблице, вы можете выбрать конкретный месяц, информация о котором вас ин­тересует. Для этого нужно нажать кнопку , расположенную в левом верхнем углу таблицы, и выбрать в появившемся диалоге нужный ме­сяц. С помощью похожих списков, располо­женных в правой и нижней части диаграммы, можно выбрать вывод выборочной информа­ции по отдельным клиентам и по отдельным видам товаров (Рис9.4).

9.4. Вывод выборочной информации

Если вы измените исходные данные, нуж­но перейти на лист со сводной таблицей, щелк­нуть на ней мышью и выбрать команду меню Данные → Обновить данные. Сводная таблица будет пересчитана, а вместе с ней автоматически изменится сводная диаграмма. При больших объемах информа­ции сводные таблицы и сводные диаграммы являются чрезвычайно удобным средством обобщения разных данных.