Елементи планування роботи виробництва та складів - Формули робочого листа - Excel - Каталог
Уявіть, що ви плануєте закупівлю витратних матеріалів для виробництва. До вас стікаються 2 потоки даних: виробничі плани та прогноз щодо наявності виробничих матеріалів на складах. У вас є кілька заводів, багато видів матеріалів. На виході ви зобов'язані надавати інформацію щодо того, які матеріали в яких кількостях і коли слід закуповувати і куди відправити.
Розглядаючи цей приклад, ми навчимося декільком досить оригінальним та ефектним прийомам, які ви потім зможете застосовувати для своїх завдань.
Ви дізнаєтеся універсальний метод суміщення даних із двох (і більше) таблиць, що мають різні формати
Ви дізнаєтесь, як використовувати зведені таблиці для отримання звіту з наростаючим підсумком
Ми будемо використовувати: розумні таблиці, іменовані діапазони, формули ІНДЕКС (INDEX), ЯКЩО (IF), ПОШУКПОЗ (MATCH), СТОЛБЕЦЬ (COLUMN), РЯДОК (ROW), ЧСТРОК (ROWS) та зведені таблиці
Ви побачите чудову ілюстрацію синтезу перерахованих вище інструментів Excel для досягнення вражаючих результатів
Дані на вході
Аркуш REQ містить плани використання матеріалів (компоненти) для кінцевої продукції.
Наприклад, компонент P49 буде потрібний на зводі L01 у кількості 58 235 штук до 26 травня 2015 року. Зауважте, що суми негативні, на відміну від наступної таблиці. Це нам знадобиться.
Аркуш STK відображає процес надходження матеріалів на склади заводів.
Зважаючи на те, що обидві таблиці складаються в основному зі схожих стовпців, компановка стовпців різна та й кількість стовпців не збігається. Як це зазвичай буває, ви ніяк не можете керувати даними, що надходять до вас із облікової системи підприємства.
Отже, у нас із вами є потікподій, які зменшують запаси матеріалів на складах (виробництво) та потік подій, що збільшують запаси (закупівлі). Все що нам треба – це побудувати ці події на одній тимчасовій шкалі та стежити, щоб рівень складських запасів не ставав негативним. Негативний рівень запасів свідчить, що з виробництва бракує матеріалів. Багато великих компаній мають штат людей, які займаються приблизно такою роботою, яку я зараз описав. В даному випадку моя задача, показати шляхи, як це можна робити в Excel з мінімальною кількістю зусиль і з відомою часткою витонченості.
Файл прикладу
Об'єднуємо таблиці
Об'єднуватимемо таблиці. формулами. Тобто в осередках нашої об'єднаної таблиці будуть такі формули, які спочатку виведуть усі рядки таблиці REQ, а потім усі рядки таблиці STK. І все це буде зроблено з урахуванням того, що у всіх таблиць різна структура. На цьому етапі ми зовсім не дбатимемо про сортування рядків – нехай йдуть, як йдуть.
Вихідні таблиці оформляємо як розумних таблиць, присвоюючи їм відповідні ідентифікатори: лист REQ - розумна таблиця tblREQ , лист STK - tblSTK .
Тепер перейдемо на лист Combine. Наша об'єднана таблиця повинна складатися з наступних стовпців: Компонент, Завод, Термін, Кількість, де Термін - це або дата виробництва, або дата надходження матеріалу на склад. Крім цього додаємо 2 допоміжні стовпці: Таблиця та Рядок. Якщо комірка стовпця Таблиця містить 1, то дані витягуються з таблиці tblREQ , якщо 2 - то tblSTK . Осередки стовпця Рядок підказуватимуть, з якого рядка відповідної таблиці брати дані.
Формула для колонки Таблиця виглядає так:
=ЯКІ( РЯДОК(1:1) 0" ) + 1 )
Це стандартний підхід,роздивлений тут.
Зведена таблиця
Ось зараз буде важливо, дуже багато хто цього не розуміє:
Все, що можна зробити з допомогою зведених таблиць, має бути зроблено з допомогою зведених таблиць.
Це питання ваших трудовитрат, ефективності вашої роботи. Зведені таблиці – ключовий інструмент Excel. Інструмент надзвичайно потужний і простий Одночасно. Розумієте, водночас!
Отже, зведену таблицю будуємо з урахуванням ИД rngCombined . Налаштування всі стандартні:
Поле Кількість я перейменував на Запаси. Операція по цьому полю підсумування плюс ось таке налаштування:
