Ведомость учета поставки материалов

Дата поставки	Код поставщика	Наименование поставщика	Код материала	Наименование материала	Сумма поставки
05.09.2010		Заря		Сталь	5,00
06.09.2010		Космос		Чугун	7,00
07.09.2010		Аврора		Медь	3,00
10.0902010		Восход		Никель	4,00
05.09.2010		Космос		Олово	2,00
11.09.2010		Заря		Сталь	5,00
12.09.2010		Заря		Медь	5,00
06.09.2010		Аврора		Свинец	1,00
07.09.2010		Аврора		Никель	2,00
14.09.2012		Восход		Свинец	5,00
15.09.2012		Космос		Олово	5,00

Рис. 7. Исходные данные для заполнения ддокумента «Ведомость учета поставки материалов»

11. Переименуйте очередной лист в «Ведомость учета поставки материалов» (аналогично действиям пункта 2).

12.Создайте «шапку» таблицы «Ведомость учета поставки материалов» (аналогично действиям пунктов 3 – 5), (см. рис.7).

 

13. Заполните графу «Дата поставки» вручную, указав в вкладке «Число» формат «Текстовый».

Заполните графы «Наименование поставщика» и «Код поставщика»:

· сделайте ячейку С3 активной;

· в меню «Данные» выберите команду «Проверка данных», в поле «Тип данных» выберите «Список»;

· введите значение в поле «Источник», выделив диапазон A3÷A7 в «Справочнике поставщика» (рис. 8);

Рис. 8 Настройка списка поставщиков

· нажмите кнопку «OK»;

· для того чтобы ввод наименования поставщика из списка осуществлялся в каждой ячейке столбца B («Наименование поставщика») сделайте ячейку С3 активной и, установив курсор на маркер в правом нижнем углу, щелкните левой клавишей мыши и протяните его до ячейки С13 (рис. 9);

Рис. 9 Вид листа «Ведомость учета поставки материалов»

при настройке списка

· сделайте активной ячейку В3;

· воспользуйтесь командой «Вставить функцию» меню «Формулы»;

· в поле «Категория:» выберите «Ссылки и массивы»;

· в поле «Выберите функцию» нажмите «ВПР» (рис. 10);

Рис. 10 Вид первого окна мастера функций

· нажмите кнопку «OK».

· введите в поле «Искомое_значение», щелкнув по ячейке С3;

· нажмите «Enter»;

· введите информацию в поле «Таблица»;

· воспользуйтесь командой «Использовать в формуле» меню «Формулы», выбрав «Вставить имена»(рис. 10а);

Рис. 10а. Ввод информации в поле «Таблица»

 

· выделите «Имя:» «Наименование_поставщика» (рис. 11)

Рис.11 Ввод имени массива в качестве аргумента формулы

· нажмите кнопку «OK»;

· нажмите «Enter»;

· введите информацию – цифру 2 в поле «Номер_столбца»;

· введите информацию – цифру 0 в поле «Интервальный_ просмотр» .

Заполненные поля представлены на рис. 12.

Рис.12 Вид второго окна мастера функций

· нажмите кнопку «OK».

· установите курсор на маркер в правом нижнем углу ячейки E3, щелкните левой клавишей мыши и протяните его до ячейки F13.

Функция ВПР работает только после ввода наименования поставщика в соответствующую ячейку столбца С. Поэтому появится знак #Н/Д (нет данных), который исчезнет после ввода наименования поставщика.

14. Введите наименования поставщиков в ячейки В3÷B13:

· сделайте ячейку С3 активной;

· щелкните на кнопку рядом с ячейкой С3 и из предложенного списка выберите наименование поставщика – Заря. Ячейка В3 – «Код поставщика» будет заполнена автоматически (рис.13)

Рис. 13 Автоматическое заполнение кода поставщика

по его наименованию

 

· аналогично заполните ячейки С4÷С13, ячейки В4÷В13 будут также заполнены автоматически.

Далее заполняются графы D, E, F. Для этого:

· сделайте активной ячейку Е3;

· воспользуйтесь командой «Вставить функцию» меню «Формулы»;

· в поле «Категория:» выберите «Ссылки и массивы»;

· в поле «Выберите функцию» нажмите «ВПР» (рис. 10);

Рис. 10 Вид первого окна мастера функций

· нажмите кнопку «OK».

· введите в поле «Искомое значение», щелкнув по ячейке D3;

· нажмите «Enter»;

· введите информацию в поле «Таблица»;

· воспользуйтесь командой «Использовать в формуле» меню «Формулы», выбрав «Вставить имена»;

· выделите «Имя:» «Наименование материала (рис. 11)

· нажмите кнопку «OK»;

· нажмите «Enter»;

· введите информацию – цифру 2 в поле «Номер_столбца»;

· введите информацию – цифру 0 в поле «Интервальный_ просмотр» (рис. 12)

Рис.12 Вид второго окна мастера функций

· нажмите кнопку «OK».

· установите курсор на маркер в правом нижнем углу ячейки D3, щелкните левой клавишей мыши и протяните его до ячейки D13.

Функция ВПР работает только после ввода кода поставщика, записывая результат поиска в соответствующую ячейку столбца Е.

Далее вводится графа «Сума поставки» вручную.

Заполненная таблица выглядит следующим образом (рис. 14).

Рис. 14 Результат заполнения таблицы «Ведомость учета поставки материалов»

 

15. Создайте сводную таблицу «Фактическое выполнение поставок»:

· установите курсор в поле таблицы «Ведомость учета поставки материалов»;

· воспользуйтесь командой «Сводная таблица» из меню «Вставка»;

· в окне «Создание сводной таблицы» (MSOffice 2010) нажмите кнопку «OK» (рис 15).

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

Чтобы вставить поле в сводную таблицу, его необходимо перетащить в одну из четырех областей: «Фильтр отчета», «Названия столбцов», «Названия строк» и «Σ Значения»:

· перенесите в поле «Фильтр отчета» надпись «Код материала» (поставьте курсор на поле «Код материала», нажмите левую клавишу мыши и, не отпуская, перенесите в поле «Фильтр отчета»);

· перенесите в поле «Названия строк» надпись «Наименование поставщика»;

· перенесите в поле «Σ Значения» надпись «Сумма поставки фактическая»;

· в результате выполнения перечисленных действий получится сводная таблица (рис. 16);

Рис. 16 Фрагмент листа «Фактическое выполнение поставок»

 

· переименуйте лист со сводной таблицей в «Фактическое выполнение поставок».

Внимание! Если в графах сводной таблицы подсчитываются не суммы, а количество цифр, то необходимо изменить формат чисел. Для этого установить курсор в обрабатываемое поле сводной таблицы, вызвать правой кнопкой контекстное меню и в поле «Параметры полей значений» вызвать перечень операций, среди которых указать «Сумма».

 

Так как в приведенном примере необходимо проанализировать фактическое выполнение поставок всех видов материалов, отслеживая при этом сумму поставки каждым поставщиком, в качестве фильтра выбран показатель «Код материала». Таким образом, мы видим сумму задолженности перед каждым поставщиком за период и общую сумму задолженности перед всеми поставщиками за все виды материалов. При вводе иного значения в поле «Фильтр отчета» сводной таблицы можно получить иное представление данных для анализа, например по датам и поставщикам или по кодам материала. Рассмотрим далее порядок получения таких отчетов средствами сводных таблиц.

16. Создайте ведомость «Фактическое выполнение поставок по поставщикам и датам»

· установите курсор в поле таблицы «Ведомость учета поставки материалов»;

· воспользуйтесь командой «Сводная таблица» из меню «Вставка»;

· перенесите в поле «Названия строк» надпись «Наименование поставщика»;

· перенесите в поле «Названия строк» надпись «Дата поставки»;

· перенесите в поле «Фильтр отчета» надпись «Код материала»;

· перенесите в поле «Σ Значения» надпись «Сумма поставки фактическая»;

· переименуйте лист со сводной таблицей в «Фактическое выполнение поставок по поставщикам и датам» (рис. 17).

Рис. 17 Фактическое выполнение поставок по поставщикам и датам (сводная таблица)