Пример 3. Функции ПРОСМОТР и ВПР

1. Для автоматизации подстановки данных необходимо создать справочники.

Переименуйте лист книги MS Excel в Справочники. Для каждого справочника:

§ в первой строке списка приведите полужирным шрифтом имена полей;

§ закрепите строку имен полей, чтобы она не исчезала при перемещении по экрану (Вид – Закрепить области);

§ внесите в справочники необходимую информацию.

 

2. Создайте на листе Ведомость таблицу:

 

§ Поле «Код поставщика» заполните с помощью функции ПРОСМОТР:

- Установите курсор в первую ячейку столбца «Код поставщика» и вставьте функцию ПРОСМОТР.

- Данная функция имеет два формата. Выберите первый: искомое значение; просматриваемый вектор; вектор результатов.

- Задайте аргументы:

Искомое значение – ячейка с указанием поставщика;

Просматриваемый вектор – столбец «поставщик» в справочнике (ссылка должна быть абсолютной);

Вектор результатов – столбец «код поставщика» в справочнике (ссылка должна быть абсолютной).

- После того, как функция будет скопирована на весь столбец, он будет заполнен кодами поставщиков. Проверьте правильность результатов.

Важно! Для правильной работы функции ПРОСМОТР необходимо, чтобы данные в справочнике, по которому производится поиск (в нашем примере это справочник поставщиков), были отсортированы по просматриваемому вектору (в нашем примере это столбец «поставщик»).

- Если обнаружены ошибки, выполните сортировку в справочнике поставщиков.

 

§ Поля «Цена» и «Ед.измерения» заполните с помощью функции ВПР:

- Установите курсор в первую ячейку столбца «Цена» и вставьте функцию ВПР.

- Задайте аргументы:

Искомое значение – ячейка в ведомости с наименованием товара;

Таблица – справочная таблица, содержащая данные о товарах и их ценах (ссылка должна быть абсолютной);

Номер столбца – номер столбца, содержащего цену на товар (в нашем случае это 2-ой столбец справочника);

Интервальный просмотр – проставить 0.

- Скопируйте функцию на весь столбец.

- Аналогично заполните столбец «Ед.измерения».

 

§ Для заполнения столбца «Стоимость» используйте формулу

Стоимость = Количество * Цена

§ С помощью Автосуммирования вычислите Итоговую сумму.

 

Пример 4. Сводные таблицы

Для получения отчетов разной формы удобно использовать Сводные таблицы. Предварительно нужно выделить таблицу с заголовками столбцов, вызвать Данные – Сводная таблица, выполнить шаги 1 –3. Затем перетаскивая различные элементы из списка полей сводной таблицы в ее макет, можно получать таблицы требуемой формы.

 

Перетаскивая поле Товар в Область строк, поле Дата в Область столбцов, поле Стоимость в Область значений, получите сводную таблицу, отражающую динамику получения различных товаров с подсчетом сумм по стоимости:

 

Постройте сводную таблицу, отражающую динамику получения товаров от поставщиков с подсчетом стоимости поставок (сумма по полю Стоимость):