Задачи по логистики. Решение задач с помощью ms excel 46
 Скачать 7.4 Mb.
|
ПЕРЕНОС ДАННЫХ ИЗ НЕСКОЛЬКИХ ТАБЛИЦ-ИСТОЧНИКОВ В ТАБЛИЦУ-ЦЕЛЬПри анализе товарных запасов часто возникает необходимость консолидации данных, содержащихся в нескольких таблицах-источниках. Например, ваша информационная база данных может формировать два отчета: «складская справка» и «оборачиваемость». Вам же необходима таблица с данными и по оборачиваемости и по текущим остаткам. Простая сортировка таблиц по алфавиту с последующим копированием данных из одной таблицы в другую пригодна далеко не всегда. Часто в складской справке не отображаются товары с нулевым остатком, при этом в таблице оборачиваемостей эти товары могут присутствовать. Соответственно возникает задача объединения таблиц с разным количеством товарных позиций. Перенос данных из таблицы-источника в таблицу-цель. Для переноса данных из таблицы-источника в таблицу-цель удобно воспользоваться функцией ВПР. Рассмотрим применение функции на примере. Пусть необходимо добавить из таблицы со складскими остатками (рис. 3.1) данные об оборачиваемостях товаров в таблицу на рис. 2.1. Таблица 3.1 в этом случае будет источником, а таблица 2.1 целью.  Рис. 3.1. Таблица со складскими остатками Последовательность действий следующая. Установите табличный курсор в ячейку и щелкните на инструменте «Вставить функцию». Откроется окно «Мастер функций шаг 1 из 2». В разделе «Категория:» щелкните на категории «Ссылки и массивы», а в разделе «Функция» дважды щелкните на функции ВПР. Откроется окно функции ВПР (рис. 3.2).  Рис. 3.2. Окно функции «ВПР» Первый параметр функции, который необходимо заполнить, — это «Искомое_значение». Чтобы перенести данные из таблицы-источника в таблицу-цель, необходимо задать признак, по которому функция ВПР найдет величину складского остатка в источнике и поместит в цель. В нашем случае таким признаком являются наименования товаров, указанные в столбце «Товар». Щелкните на ячейке «А1» в таблице 2.1. Тем самым вы укажете, что в источнике необходимо искать ячейку, содержимое которой совпадает с содержимом ячейки «А1». Следующий параметр функции — «Таблица». Здесь задается область поиска в таблице-источнике. Переместите курсор в область указанного параметра (первоначально она пустая), затем перейдите в файл или на лист с таблицей-источником и выделите область поиска. Чтобы выделить область поиска, щелкните на левой верхней ячейке области поиска и, удерживая левую кнопку мыши, тяните курсор до правой нижней ячейки области поиска. Обратите внимание, выделять область поиска следует, начиная со столбца, содержащего «Искомое_ значение». В нашем случае — это столбец А. Если перед столбцом с «Исходным значением» имеются столбцы с данными (например с № п/п), в область поиска они не должны включаться. Третий параметр «Номер_столбца» — это номер столбца в «Таблица», из которого будут переноситься данные в таблицу-цель. Первый столбец массива всегда имеет номер 1. В нашем примере «Номер_столбца» равен 2. Обратите внимание, что речь идет не о номерах столбцов таблицы-источника, а о номерах столбцов выделенного в таблице массива. То есть номера столбцов таблицы и массива совпадут только в частном случае, когда совпадают первый столбец таблицы и массива. Последний параметр «Интервальный_просмотр» — установите «ЛОЖЬ» и щелкните на «ОК». Теперь необходимо скопировать формулу в расположенные ниже ячейки. Прежде чем это сделать, «заморозьте» ячейки диапазона «Таблица», чтобы границы диапазона не сместились при копировании. Сейчас формула в ячейке «D1» имеет примерно такой вид: =ВПР(А1;'рис.3.1.'!А1:В27;2;ЛОЖЬ) исправьте ее на: =ВПР(А1;'рис.3.1.' !А$1:В$27;2;ЛОЖЬ) Внимание! Чтобы скопировать содержимое ячейки в смежные ячейки, выделите ячейку, затем подведите курсор мыши к маркеру, расположенному в правом нижнем углу выделенной ячейки. Курсор примет вид «». Нажмите левую кнопку мыши и тяните рамку в нужную сторону. Отпустите кнопку мыши. Во все захваченные рамкой ячейки скопируется содержимое исходной ячейки. Результирующая таблица представлена на рис. 3.3.  Рис. 3.3. Объединенная таблица оборачиваемости и остатков товаров |