Вспомогательные таблицы

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

На практике вспомогательные таблицы чаще всего приходится создавать самостоятельно. Правила создания таких таблиц:

1) скопируйте зависимые столбцы на новый лист (дайте этому листу осмысленное название);

2) первый столбец должен соответствовать искомому значению в ВПР – если нужно, поменяйте порядок столбцов;

3) удалите повторяющиеся строки с помощью кнопки «Удалить дубликаты» на вкладке «Данные»;

4) задайте имя получившемуся диапазону ячеек;

5) заполните зависимые столбцы в основной таблице через ВПР.

Пример 4. Поставки товаров

Имеется таблица, в которой записаны поставки товаров от различных поставщиков.

В этой таблице нет никаких формул, все значения вводятся вручную. В первую очередь – очевидная формула, стоимость = цена * количество.

Теперь можно спокойно заняться поиском зависимостей. Рассуждая логически, можно предположить следующие:

1) поставщик зависит от товара (если каждый товар может поставляться только одним поставщиком);

2) цена зависит от товара (если цены постоянны) или от товара и поставщика одновременно (если у каждого поставщика своя цена);

3) город/страна зависит от поставщика.

Чтобы проверить свои предположения, отсортируем таблицу по товару, а потом по поставщику.

Просмотрев всю таблицу, можно сделать вывод, что первые два предположения неверны (каждый товар может приходить от разных поставщиков и каждый раз по разной цене), а вот третье справедливо – каждый поставщик связан с одним конкретным городом/страной.

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

Скопируем столбцы «Поставщик» и «Город/страна» на новый лист. Столбцы должны идти именно в таком порядке, т.к. город зависит от поставщика.

Оставим только неповторяющиеся строки. Нужно выделить все данные, нажать «Удалить дубликаты» на вкладке «Данные» и подтвердить удаление по обоим столбцам.