Вітаю, друзі! Ви на каналі Корисний Ексел. Сьогодні я пропоную вам розглянути мій варіант складського обліку товарів, як кількісного, так і вартісного.
За допомогою готового файлу ви зможете вносити операції по поступленню товарів, по продажу товарів, опробіткуванню та списання товарів, внаслідок інвентаризації. А також в звіті на окремому аркуші ви зможете дивитися залишки товарів на будь-яку дату та за будь-який період, який ви оберете в розрізі складу та номенклатурної категорії товарів. Засікавило? Тоді продовжуйте дивитися і не забувайте підписатися на мій канал, поставте вподобайку, натисніть на дзвіночок, щоб не пропустити наступне відео. Я заздалегідь підготував пустий файл, який ми з вами будемо заповнювати.
Перш за все, потрібно створити 4 аркуші в нашому файлі. Це звіт по залишкам, поступлення, вибуття і довідник. В довідник ми заповнюємо ось так по стовцям нашу довідникову інформацію, тобто кількість складів. які в нас будуть використовуватися наші види операцій поступлення та операцій вибуття, які в нас будуть номенклатурні категорії товарів, і безпосередньо вже наші номенклатури в окремих колонках згідно наших номенклатурних груп. Тепер розглянемо аркуш поступлення.
На аркуші поступлення... ведеться облік надходження товарів на всі наші склади. Наприклад, внаслідок купівлі товарів, оприходування товарів або переміщення товарів з іншого складу. Тому в цій таблиці є такі колонки, як номер операції, дата, вид операції, склад, категорія, номенклатура, кількість, ціна поступлення і сума. Для того, щоб спростити заповнення цієї таблиці, ми повинні зробити випадаючий перелік із нашого довідника.
Для цього скористаємося одним із способів створення випадаючого списку через встановлення іменованого діапазону. Для цього переходимо в довідник, виділяємо наш діапазон клітинок. із нашими складами. І ось в цьому куті, там де А2, вводимо назву нашого діапазону. Це в нас склад.
Далі, для виду операції поступлення, ми аналогічним чином називаємо цей діапазон клітинок поступлення, нижнє підкреслення, операція. Аналогічним чином вид операції вибуття напишу вибуття. Це був підготовчий етап для того, щоб створити випадаючий перелік на основі іменованого діапазону.
Тому тепер там, де вид операції, ми виділяємо цю колонку, натискаємо на вкладці дані перевірка даних. В цьому випадаючому переліку обираємо список. Там, де джерело, натискаємо «Дорівнює» і потрібно тут внести ім'я нашого іменованого діапазону. Тобто «Дорівнює» «Поступлення».
Оп, натискаємо «Ок». І, як бачимо, тепер в нас з'явився іменований діапазон з нашими видами операцій. Далі аналогічним чином робимо випадаючий список по стовпцю «Склад». Виділяю дані в цьому стовпці, натискаю на перевірка даних, натискаю на список, в полі «Джерело» натискаю на «Дорівнює» і вводжу ім'я іменованого діапазону «Склад».
Аналогічним чином робимо випадаючий перелік для стовпця «Категорія». Переходжу на довідник. виділяю діапазон комірок для його іменування і в полі ім'я діапазону вношу категорію.
Переходжу на аркуш поступлення, виділяю наш діапазон із категоріями товарів, натискаю на перевірка даних. Обираю список і там, де джерело, ставлю «Дорівнює категорія». Натискаю на «На».
Як бачимо, тут в нас з'явилися наші категорії товарів. Тепер для того, щоб зробити випадаючий список для номенклатури, але щоб він був залежним від нашої категорії, тобто якщо ми тут обрали пилососи, то в номенклатурі товарів потрібно, щоб випадаючий список включав тільки і лише пилососи. Тому для цього нам потрібно зробити іменовані діапазони для кожної з категорій товарів і найголовніше назвати ці діапазони чітко так, як в нас називаються наші категорії товарів в стопці.
з категоріями товарів в довіднику. Тобто, якщо тут написано пилососи, то значить я ось скопіюю цю назву, виділю наші номенклатури, які позначені як пилососи, і іменую їх як пилососи. Аналогічно, якщо в нас тут вказані смарт-годинники з нижнім підкресленням між словами, то мені потрібно їх скопіювати.
Аналогічним чином я виділяю ці номенклатури із смарт-годинниками і іменую їх як смарт-годинники із нижнім підкресленням. Те ж саме роблю для телевізорів та телефонів. Тепер для того, щоб зробити випадаючий список, нам потрібно скористатися формулою indirect на російський сміш. Для цього я...
Введу цю формулу в клітинку поряд, щоб потім її скопіювати в перевірку даних. Відкривається дужка і посилаюся на клітинку пилососи. Але так як це в нас розумна таблиця, то посилання із назвою стовпця та номером рядка перетворюється на назву стовпця розумної таблиці.
Це нам не потрібно, тому мені потрібно ввести посилання на клітинку E2 вручну. Закриваю дужку. Як бачимо, згідно з категорією товарів пилососи, в нас з'явився перелік номенклатури пилососів.
Якщо я зміню тут смарт-водинники, то ми бачимо, що наші... Номенклатурні позиції змінилися на смарт-годинники. Це свідчить про те, що наша формула працює. Тому ми ось цю формулу копіюємо, виділяємо нашу номенклатуру, натискаємо на перевірку даних, обираємо список і вставляємо наше посилання.
І натискаємо на ОК. Як бачимо, тепер ми маємо випадаючий список. відповідно до категорії, яку ми встановили в стопці категорія.
Давайте пересвідчимося. Я зараз ось тут оберу телевізори. І що ми тут маємо?
Ми маємо перелік з телевізорів. Наступним кроком потрібно ввести формулу в колонку сума як добуток кількості та ціни. Нумерацію наших операцій.
Ми робимо простою формулою, як клітинка вища, тобто А2 плюс одиничка. І потім цю формулу копіюємо донизу. А в першу клітинку ставимо одиничку, якщо ми хочемо, щоб наше нумерування починалося з одиниці.
Якщо нам потрібен інший номер, то ми його так і вводимо, і наша нумерація наших. Рядків буде починатися з того номеру, який ми введемо. Далі наступний аркуш це вибуття. На цьому аркуші ми повинні будемо аналогічним чином зробити випадаючі списки по виду операції, по складу, по категорії та номенклатурі товарів.
Але ми цього зараз на відео не будемо робити, щоб… не затягувати наше відео. Тому перейдемо до найголовнішого. Це до звіту по залишкам товарів на складах. Переходжу на відповідний аркуш. Як бачимо, в мене тут вже заготовлена таблиця, яку ми будемо заповнювати.
Тому давайте почнемо її заповнювати. Дата з. Я введу 01.01.
2024 року, дата по, наприклад, 15.01.2024 року. Склад. Тут потрібно зробити випадаючий перелік, тому я натискаю на перевірка даних, обираю список і вводжу дорівнює склад. Натискаю на ок і обираю всі склади. Далі.
Там, де категорія аналогічна, мені потрібно зробити випадаючий перелік, список. Тому я тут пишу дорівнює категорія і натискаю на ОК. Так, і ось з'явилися наші категорії. Я обираю поки що всі категорії, щоб ось сюди нижче у звіт нам потрапляли всі склади і всі категорії товарів.
І далі ось в клітинку. А 10 нам потрібно ввести формулу, яка буде нам витягувати із аркушу поступлення наші склади, категорії і номенклатури товарів. Для цього існує функція «фільтр», яка доступна в Екселі починаючи з 2021 року.
Ми цю функцію будемо комбінувати разом з формулою унік'ю, яка буде нам виводити унікальні значення номенклатури, складів і категорій. А також все це огорнемо в функцію ifs, тобто, яка буде нам показувати варіанти номенклатур, складів та категорій, в залежності від… обрання складу та категорії в шапці налаштувань нашої таблиці. З готової таблиці я скопіюю вже готову формулу, щоб не затягувати відео, тільки надам коментарі щодо цієї формули.
Отже, спочатку тут необхідно прописати функцію ifs, тобто, є слівмин, потім за допомогою функції end, тобто ми зараз ставимо умову, якщо клітинка B4, це в нас склад, буде дорівнювати всі, ось зараз в нас обрано всі, та клітинка B5 буде дорівнювати також всі, це B5, це в нас категорія товарів, тобто склад і категорія товарів будуть дорівнювати значень всі, то це значить, що буде виконуватися ось така Формула. Формула Unique, яка посилається на стопці розумної таблиці з надходження, із стопця склад до стопця номенклатура. Тобто Unique витягує з цієї таблиці унікальні значення і сортує по першому стопцю, тобто по складу, формула Sort. Далі, якщо ми оберемо якийсь параметр по складу і категорії, який не буде дорівнювати всі, тобто і склад, і категорія не будуть дорівнювати всі, то тоді виконується формула разом із трьома формулами. Це сорт, уніклю і фільтр.
Тобто, спочатку, за допомогою формули фільтр, ми із діапазону стопців від складу до номенклатури Витягуємо склад, який буде введений в клітинку B4, ось тут замість всі, а також тут оператор помножити, тобто І, категорія буде дорівнювати клітинці B5, тобто щось буде обране інше. Далі наступний варіант розвитку подій в нас може бути, що склад буде дорівнювати всі, а категорія не буде дорівнювати назві всі. то в нас тут буде один критерій, тобто категорія товарів.
Тобто ось ця формула відрізняється від Попередньої тим, що ми прибрали один із критерій, а саме критерій відбору по складу. Тобто в нас параметр B5 категорія товарів повинна дорівнювати таблиці із стопця категорія. Наступний випадок це в нас коли склад буде не дорівнювати пункту всі, а B5 навпаки буде дорівнювати пункту всі. Тут ми аналогічним чином, як і в попередньому випадку, використовуємо функцію Filter Unique та Sort, але умову для формули Filter змінюємо на те, що він фільтрує поступ цю склад, значення яке буде дорівнювати значенню в клітинці B4, тобто значенню склада. І обов'язково потрібно закрити дужку.
щоб закрити функцію ifs і натискаємо на enter. І тепер давайте перевіримо, чи коректно працюють наші відбори. Ось, наприклад, я зараз відберу основний склад. Ось, як бачите, тепер в нас тут немає іншого складу, як магазин. Наприклад, я хочу подивитися окремо по магазину.
Ось ми бачимо наші магазини. І тепер, наприклад, мені потрібно подивитися... по категорії телефони наші товари. Як бачимо, нам наша формула відфільтрувала нашу номенклатуру. Давайте я зміню склад на основний.
Ось, бачите, склад основний категорії товарів ось такі. І далі поставлю тут всі. І бачимо, що телефони відображаються на обидвох складах. Якщо ми будемо вносити якісь нові номенклатури, в таблицю поступлення, то вони будуть автоматично додаватися до цієї номенклатури, тому що в нашій формулі йде посилання саме на стовбець розумної таблиці. Тому потрібно слідкувати, щоб наші рядки були в межах нашої розумної таблиці.
А про це свідчить в правому нижньому куті нашої розумної таблиці ось ця межа розумної таблиці. Тобто ми можемо цю межу підтягнути вниз трошки. І як бачимо, формули ось тут автоматично продовжилися.
Продовжилися наші випадаючі списки. Тепер нам потрібно заповнити наші стопці із кількістю та сумою даними із таблиць поступлення та вибуття. Залишок на початок періоду. Поки я залишаю незаповненим, ми до нього повернемося трохи згодом.
А одразу перейдемо до заповнення колонки поступлення, а саме кількості. Я вже з готової таблиці скопіюю формулу і лише її прокоментую. Колонку кількість ми будемо заповнювати моєю найулюбливішою функцією SUMIFS.
сум'ї слімен. Давайте розглянемо її детальніше. Першим параметром нам потрібно послатися на стовбець із кількістю, тобто це той діапазон, який буде сумуватися в залежності від наших критеріїв.
В даному випадку це в нас таблиця надходження, стовбець, кількість. Далі я вказую тут... що в нас буде діапазон критерій надходження склад, а саме наш критерій це клітинка А10. Це ось клітинка в даному випадку магазин.
Наступний критерій в нас буде номенклатура. Аналогічним чином мені потрібно виділити стовбець із нашою номенклатурою, тобто це таблиця надходження і стовбець номенклатура. Через крапку з кумою посилання на клітинку С10, тобто це клітинка з нашою номенклатурою безпосередньо ось в таблиці звіту.
І далі два критерії по даті, щоб наші всі поступлення вибиралися в залежності від обраної дати З і обраної дати По. Отже, Перший параметр це в мене критерій дата з. Зверніть увагу, будь ласка, як ввести критерій більше або дорівнює.
Тобто нам потрібно показати всю інформацію, яка буде з 1 січня. Тобто 1 січня і більше. Тобто більше або дорівнює. І ось тут в мене посилання на клітинку B2 із знаком долара.
Тут закріплюємо цю комірку. Наступним параметром це буде дата по. Зверніть увагу на синтаксис введення цього параметру.
Я тут лапки відкриваю, ставлю знак менше, потім дорівнює. Лапки закриваю подвійні. І ставлю знак end і посилаюся на нашу клітинку B3. І обов'язково її закріплюю, щоб при копіюванні цієї формули вниз, в нас ці посилання не зсунулися вниз.
Мені залишається натиснути Enter і скопіювати цю формулу донизу нашої таблиці. Далі, для того, щоб заповнити стопчик поступлення із сумою, мені потрібно скопіювати цю формулу, але трошки її змінити. Там, де діапазон суми надходження кількість, я її зміню на суму.
Як бачимо, як тільки я прибрав назву кількість, у мене тут з'явилася підказка. Згідно якої я можу обрати необхідний мені столбець. В даному випадку це сума. І натискаю на Enter.
Копіюю цю формулу на всю таблицю. І як бачимо, наші поступлення записалися. Аналогічним чином мені потрібно внести кількість по вибуттю товарів.
Для цього я скопіюю. формулу із колонки кількість поступлення, вставлю її, натисну на Enter. І замість назви таблиці надходження мені потрібно замінити на назву таблиці вибуття. Щоб дізнатися, як саме називається моя таблиця, мені потрібно перейти на цю таблицю, натиснути на конструктор таблиці і ось тут зліва.
Ми бачимо, що наша таблиця називається «Видаток». Для зручності редагування формули я видаляю знак «Дорівнює» перед формулою, натискаю на «Ентер», потім переходжу на пункт «Знайти» і обираю «Замінити». В поле «Знайти» вводжу надходження, а в поле «Замінити» на… «Воджу видаток» і натискаю на «Замінити».
Як бачимо, в нашій формулі назва таблиці «Надходження» змінилася на «Видаток». Тепер ставлю «Дорівнює» перед нашою формулою і натискаю на «Ентер». Як бачимо, тепер формула працює і вона саме сумує кількість товару, яку ми видали зі складу за той період, який ми обрали.
Попіюю цю формулу донізу. І тепер що стосується суми. Для того, щоб вирахувати вартість товарів, що вибули, ми скористаємося одним із методів обліку товарів, що вибули, як по середньозваженій собівартості. Для цього мені потрібно ввести формулу. Залишок на початок суми додаю.
Те, що поступилося цей період, беру в дужки, потім поділити дужкою, відкриваю кількість на початок, додаю кількість поступлення і потім ще все це помножую на кількість вибулих товарів. І таким чином середньозважена собівартість вибулих товарів В нас буде 401,351. Тепер я продовжую цю формулу донизу.
Як бачимо, там, де в нас немає значень, ми отримуємо помилки. Тому я нашу формулу угорну ще в формулу if error. Тобто, якщо помилка.
Якщо помилка, то будемо ставити 0. If error, потім сюди і крап. Коскомою 0. Душку закриваємо. І в мене тут не вистачає ще однієї дужки, яку я ставлю. Так, тепер все працює. Давайте я скопіюю донізу.
Як бачимо, тепер в мене помилок немає, є нулі. Нам тепер залишається заповнити залишок на кінець дня. І для цього я ставлю дорівнює. Кількість на початок. Додаю поступлення.
І віднімаю кількість. по вибулих. Копіюю цю формулу на суму, пересвідчуюся, що наша формула зсунулася в праву.
Як бачимо, що дійсно 37-25, в нас буде 12, і 594 тисячі мінус 401 тисяча, в нас буде 193 тисячі майже. Я копіюю ці дві формули донизу на всю таблицю. І, власне кажучи, на 95% наша таблиця готова.
В принципі, ми можемо її користуватися, але нам не вистачає залишку на початок. Для цього мені потрібно буде скопіювати цей аркуш, на якому саме і буде розраховуватися залишок на початок періода. Отже, копіюю цей аркуш, називаю його я...
як звіт дод, тобто додатковий, і мені тут потрібно змінити дату по, яка буде дорівнювати даті з на нашому першому звіті, мінус 1. Тобто ми будемо отримувати в цій таблиці дані, які будуть до дати з. Тобто ми... Всі наші дані розділяємо на дві частини.
Перша частина це період з дати початку обліку, тобто з дати початку введення нашої таблиці, і до дати, яка передує початкові даті цільового звіту. І друга частина від довільної початкової дати до довільної кінцевої дати. Так, а ось тут я оберу, наприклад, не 1 січня, а...
І тепер, як бачимо, в нас на цьому додатковому звіті дати наші з 1 по 2 січня, тобто з першого дня дати обліку, дата початку обліку, до дати, яка буде в нас датою на початок дня нашого головного звіту. Значення склад Та категорія я ставлю дорівнює нашому головному звіту, а залишок на початок в мене буде дорівнювати залишок на кінець нашого додаткового звіту. Кількість і аналогічна сума. Все, тепер ми отримали повністю робочий звіт по залишкам товарів, в якому розраховується залишок на початок. поступлення і вибуття.
Продемонструю. Наприклад, сформуємо звіт з 5 січня по 20 січня. Як бачимо, наші дані змінюються, а це значить, що наш звіт коректно працює.
Тепер для зручності введення дати нам потрібно завантажити доповнення, яке спростить нам цю задачу. Для цього нам потрібно перейти на вкладку «Вставлення». і натиснути «Отримати надбудову». Поле пошуку. Введемо слово на англійській мові «Календар» і натиснемо на кнопку «Пошук».
Ось є таке доповнення «Mini Calendar and Date Picker». Натискаємо на «Додати», натискаємо тут «Продовжити». І як бачите, в нас з'явився ось такий календар, за допомогою якого ми можемо зручно...
вводити дати. Доповнення перепитує, чи дійсно потрібно перезаписати дату. Натискаю на ОК.
І як бачимо, дата змінилася і наші дані також перерахувалися. Єдине, що трошки підлаштуємо його. Отже, як бачимо, ми тепер зробили досить функціональний звіт, який дозволяє нам дізнаватися залишки. товарів, а якщо ми знаємо залишки товарів на окрему дату, то ми зможемо цими товарами і управляти. Отже, друзі, дякую, що додивилися це відео до кінця.
Для тих, хто не хоче з нуля створювати таку таблицю із складським обліком, ви можете її придбати на платформі Etsy або звернувшись до мене в Instagram. Всі посилання будуть в описі до цього відео. Підписуйтесь на канал, ставте вподобайки, коментуйте це відео, задавайте питання і з задоволенням на них відповім.
Побачимося в наступному відеоролику. Слава Україні!