1. Для автоматизации подстановки данных необходимо создать справочники.
Переименуйте лист книги MS Excel в Справочники. Для каждого справочника:
§ в первой строке списка приведите полужирным шрифтом имена полей;
§ закрепите строку имен полей, чтобы она не исчезала при перемещении по экрану (Вид – Закрепить области);
§ внесите в справочники необходимую информацию.
2. Создайте на листе Ведомость таблицу:
§ Поле «Код поставщика» заполните с помощью функции ПРОСМОТР:
- Установите курсор в первую ячейку столбца «Код поставщика» и вставьте функцию ПРОСМОТР.
- Данная функция имеет два формата. Выберите первый: искомое значение; просматриваемый вектор; вектор результатов.
- Задайте аргументы:
Искомое значение – ячейка с указанием поставщика;
Просматриваемый вектор – столбец «поставщик» в справочнике (ссылка должна быть абсолютной);
Вектор результатов – столбец «код поставщика» в справочнике (ссылка должна быть абсолютной).
- После того, как функция будет скопирована на весь столбец, он будет заполнен кодами поставщиков. Проверьте правильность результатов.
Важно! Для правильной работы функции ПРОСМОТР необходимо, чтобы данные в справочнике, по которому производится поиск (в нашем примере это справочник поставщиков), были отсортированы по просматриваемому вектору (в нашем примере это столбец «поставщик»).
- Если обнаружены ошибки, выполните сортировку в справочнике поставщиков.
§ Поля «Цена» и «Ед.измерения» заполните с помощью функции ВПР:
- Установите курсор в первую ячейку столбца «Цена» и вставьте функцию ВПР.
- Задайте аргументы:
Искомое значение – ячейка в ведомости с наименованием товара;
Таблица – справочная таблица, содержащая данные о товарах и их ценах (ссылка должна быть абсолютной);
Номер столбца – номер столбца, содержащего цену на товар (в нашем случае это 2-ой столбец справочника);
Интервальный просмотр – проставить 0.
- Скопируйте функцию на весь столбец.
- Аналогично заполните столбец «Ед.измерения».
§ Для заполнения столбца «Стоимость» используйте формулу
Стоимость = Количество * Цена
§ С помощью Автосуммирования вычислите Итоговую сумму.
Пример 4. Сводные таблицы
Для получения отчетов разной формы удобно использовать Сводные таблицы. Предварительно нужно выделить таблицу с заголовками столбцов, вызвать Данные – Сводная таблица, выполнить шаги 1 –3. Затем перетаскивая различные элементы из списка полей сводной таблицы в ее макет, можно получать таблицы требуемой формы.
Перетаскивая поле Товар в Область строк, поле Дата в Область столбцов, поле Стоимость в Область значений, получите сводную таблицу, отражающую динамику получения различных товаров с подсчетом сумм по стоимости:
Постройте сводную таблицу, отражающую динамику получения товаров от поставщиков с подсчетом стоимости поставок (сумма по полю Стоимость):
