9. Присвойте имя группе ячеек: выделите ячейки A3¸D7;
выберите команду Присвоить имя в разделе Определенные имена меню Формулы (рис. 6);
Рис. 6. Вид окна «Создание имени»
нажмите OK. 10.ПереименуйтеЛист2вВедомостьучетапоставкиматериалов
(аналогично действиям п. 2).
11. Создайте таблицу Ведомость учета поставки материалов (аналогично действиям п. 3–5) и введите исходные данные (рис. 7).
Рис. 7. Вид таблицы «Ведомость учета поставки материалов»
12. Заполните графы Наименование поставщика и Код постав-щика:
сделайте ячейку B3 активной;
в меню Данные выберите команду Проверка данных; в поле Тип данных выберите Список;
введите значение в поле Источник, выделив диапазон ячеек A3¸A7 в Справочнике поставщика (рис. 8);
Рис. 8. Настройка списка поставщиков
нажмите OK;
для того чтобы ввод наименования поставщика из списка осу-ществлялся в каждой ячейке столбца B (Наименование поставщи-ка), сделайте ячейку B3 активной и, установив курсор на маркер вправомнижнемуглу,щелкнителевойклавишеймышии протяните его до ячейки B13 (рис. 9);
Рис. 9. Вид листа «Ведомость учета поставки материалов» при настройке списка
сделайте активной ячейку А3;
воспользуйтесь командой Вставить функцию меню Формулы; в поле Категория: выберите Ссылки и массивы;
в поле Выберите функцию нажмите ВПР (рис. 10); нажмите OK;
введите информцию в поле Искомое_значение, щелкнув по ячейке B3;
нажмите Enter;
введите информацию в поле Таблица;
воспользуйтесь командой Использовать в формуле меню Фор-мулы, выбрав Вставить имена;
выделите Имя: Наименование поставщика (рис. 11);
Рис. 10. Вид первого окна Мастера функций
Рис. 11. Ввод имени массива в качестве аргумента формулы
нажмите OK; нажмите Enter;
введите информацию — цифру 2 в поле Номер_столбца; введите информацию — цифру 0 в поле Интервальный_про-
смотр (рис. 12);
Рис. 12. Вид второго окна Мастера функций
нажмите OK;
установите курсор на маркер в правом нижнем углу ячейки A3, щелкните левой кнопкой мыши и протяните его до ячейки А13.
Функция ВПР работает только после ввода наименования пос-тавщика в соответствующую ячейку столбца B.
щелкните на кнопку рядом с ячейкой B3 и из предложенного спискавыберитенаименованиепоставщика —Заря.ЯчейкаA3(Код поставщика) будет заполнена автоматически (рис. 13);
аналогично заполните ячейки В4¸B13, ячейки А4¸А13 будут также заполнены автоматически.
Внесение наименования поставщика из списка аналогично алго-ритму работы профессиональных приложений, позволяющих об-рабатывать учетную, управленческую, юридическую информацию во избежание двойного ввода идентичных данных. При заполнении соответствующих полей происходит обращение к справочнику ус-
ловно-постоянной информации. В нашем примере наименованию поставщикасоответствуетопределенныйкод,которыйавтоматически проставляетсяпривыбореизспискаопределенногопоставщика.При изменении наименования поставщика в ячейке B автоматически изменится и его кодовое значение в ячейке A.
Заполненная таблица выглядит следующим образом (рис. 14).
Рис. 13. Автоматическое заполнение кода поставщика по его наименованию
Рис. 14. Результат заполнения таблицы «Ведомость учета поставки материалов»
14.СоздайтесводнуютаблицуФактическоевыполнениепоставок: установите курсор в поле таблицы Ведомость учета поставки
материалов;
воспользуйтесь командой Сводная таблица из меню Вставка; в окне Создание сводной таблицы (MS Office 2010) нажмите
OK (рис. 15).
Рис. 15. Создание сводной таблицы
Чтобы вставить поле в сводную таблицу, перетащите его в одну из четырех областей: Фильтр отчета, Названия столбцов, Названия строк и S Значения:
перенесите в поле Фильтр отчета надпись Код материала (по-ставьтекурсорнаполеКодматериала,нажмителевуюкнопкумыши и, удерживая ее, перенесите надпись в поле Фильтр отчета);
перенесите в поле Названия строк надпись Наименование пос-тавщика;
перенесите в поле S Значения надпись Сумма поставки фак-тическая;
в результате выполнения перечисленных действий получится сводная таблица (рис. 16);
Рис. 16. Фрагмент листа «Фактическое выполнение поставок»
переименуйте лист со сводной таблицей в Фактическое вы-полнение поставок.
Так как в приведенном примере необходимо проанализировать фактическое выполнение поставок всех видов материалов, отсле-живая при этом сумму поставки каждым поставщиком, в качестве фильтра выбран показатель Код материала. Таким образом, можно отслеживать сумму задолженности перед каждым поставщиком запериодиобщуюсуммузадолженностипередвсемипоставщиками за все виды материалов.
При вводе другого значения в поле Фильтр отчета сводной таб-лицы можно получить иное представление данных для анализа (например, по датам и поставщикам или по кодам материалов).
Рассмотрим порядок получения подобных отчетов средствами сводных таблиц.
15. Создайте ведомость Фактическое выполнение поставок по поставщикам и датам:
установите курсор в поле таблицы Ведомость учета поставки материалов;
воспользуйтесь командой Сводная таблица из меню Вставка; перенесите в поле Названия строк надпись Наименование по-
ставщика;
перенесите в поле Названия строк надпись Дата поставки; перенесите в поле Фильтр отчета надпись Код поставщика; перенесите в поле S Значения надпись Сумма поставки фак-
тическая;
переименуйте лист со сводной таблицей в Фактическое выпол-нение поставок по поставщикам и датам (рис. 17).
Рис. 17. Фактическое выполнение поставок по поставщикам и датам (сводная таблица)
Фильтрацияигруппировкаданных.Полесводнойтаблицыиграет роль фильтра данных. Каждое поле имеет команду Все, используе-мую для отображения всех возможных значений поля.
Используя сводную таблицу Фактическое выполнение поставок, получите данные для кода материала 1001:
сделайте активным лист Фактическое выполнение поставок; нажмите кнопку правее поля Код материала;
выберите код материала 1001 (рис. 18).
Рис. 18. Фильтрация данных по полю «Код материала»
Фактическое выполнение поставок по коду материала 1001 пред-ставлено в виде сводной таблицы на рис. 19.
Рис. 19. Фактическое выполнение поставок по коду материала «1001»
16. Представьте наглядно результаты расчетов, создав диаграмму по данным сводной таблицы:
сделайте активным лист Фактическое выполнение поставок; выделите диапазон ячеек A7¸B7;
выберите команду Гистограмма в разделе Диаграммы меню Вставка;
в разделе Объемная гистограмма выберите Объемная гисто-грамма с группировкой;
переименуйте гистограмму в Фактическое выполнение по-ставок (рис. 20).
Рис. 20. Гистограмма «Фактическое выполнение поставок»
3.3. Результаты компьютерного эксперимента и их анализ