【自動化Excel】在庫管理表を作成して、安全在庫数を保つ方法

[Table of Contents] 0:00 How to manage inventory and maintain safety stock levels in Excel 0:05 What is safety stock? 0:23 Checking the completed form 1:53 Entering the table's base item names and temporary information 5:57 Calculating current inventory levels using the SUMIFS function 9:25 Subtracting safety stock levels from current inventory levels to calculate the number of items until an order is required 10:10 Benefits of creating a table in a table 12:09 Creating a table in a table 14:48 Calculating current inventory levels by referencing table and column names (structured references) using the SUMIFS function 18:26 When current inventory levels fall below safety stock levels, turn the cell background red to indicate an order is required (Conditional Formatting) 20:28 Restricting entry of items other than those listed in the product list (Data Validation/List) 22:57 Restricting entry of items other than those allowed in the action field (Data Validation/List) 25:55 Prevent input into the Inventory Quantity and Outventory Quantity fields by selecting an action (Data Validation Rules, User Settings) 31:07 Gray out cells that cannot be entered, making it clear at a glance that they cannot be entered (Conditional Formatting, Use a Formula to Determine Which Cells to Format) 35:59 How to view, edit, and delete currently configured conditional formatting 36:44 Prevent input of anything other than dates into date data fields (Data Validation Rules, Date) 38:07 Delete temporary data Subscribe to the channel here    / @consul-miya   Supporter membership for 90 yen here    / @consul-miya   Subscribe to the radio channel that will improve your quality of life here    / @lifehack-radio   Twitter   / lifehacker_miya   [Popular Playlists] ・Super-Efficient Shortcut Keys & Techniques (Windows Edition) (   • 効率を爆上げするショートカットキー&テクニック(Windows編)  ) [Materials Used] ● Illustration Irasutoya (https://www.irasutoya.com/) ● Sound Effects Sound Effect Lab (https://soundeffect-lab.info/) ● BGM Track: Weero & Mitte - Our Dive [NCS Release] Music provided by NoCopyrightSounds. Watch:    • Weero & Mitte - Our Dive | Future House | ...   Free Download / Stream: http://ncs.io/OurDiveYO ● Ending Theme Track: Cartoon - Your Stories (feat. Koit Toome) [NCS Release] Music provided by NoCopyrightSounds. Watch:    • Cartoon, Jéja - Your Stories (feat. Koit T...   Free Download / Stream: http://ncs.io/YourStoriesYO In this video, I'll show you how to manage inventory and maintain safety stock levels using Excel. Safety stock is the minimum amount of inventory needed to prevent stockouts. By managing inventory using the method I'll introduce now, you can maintain safety stock levels and prevent stockouts. You can also display current inventory levels in a list, which can be used for inventory counts. First, let's check the completed form. This inventory management sheet uses two tables. The first is a product list. Here, you'll list all product names and set their safety stock levels. The second is a management table for inventory history. From left to right, you can enter: The date the inventory history was entered, The product name of the item received or sent, The action taken, The quantity received or sent. By recording when and how inventory was received or sent, you can review it later and prevent problems. The product name field can be entered by selecting from the list of product names entered in the product list. As you enter inventory history, the product list table automatically calculates the current inventory and the number of items remaining until an order is required, making it useful for inventory counts. If the current inventory falls below the safety stock level, the cell will turn red, indicating that an order is required. You can also filter the display to show only items whose inventory is below the safety stock level, allowing you to view only items that require an order. In the second half of the video, we'll also introduce input restrictions to prevent unintended information from being entered and how to design an easy-to-read table. Let's get started! Video Select and enter product name from the product list Filter only items below the safety stock level Item names and temporary data (product list table) First, create a product list table. Enter the item name field, which forms the basis of the table. When entering information to the right, pressing the Tab key confirms the cell entry and moves the selected cell to the right, allowing for faster entry. If the column width is insufficient to display all the information, double-click the border to the right of the column name to automatically adjust the column width to fit the amount of information. Next, enter temporary information to confirm each behavior. The Inventory Quantity and Or...