應用Excel函數提升醫院倉庫庫存管理效率

Applying Excel Functions to Improve Inventory Management Efficiency in Hospital Warehouses

探討某地區醫院中央倉庫面臨的管理效率與準確性問題，並提出以Excel函數設計整合報表的方式來提升作業流程。過去該醫院使用自行開發的HIS系統管理庫存，但系統缺乏自動化與數據整合能力，導致倉管人員需耗費大量時間逐項人工查核庫存與訂單，工作效率低且容易出錯。為改善此情況，研究導入Excel函數技術，設計一套可整合多個資料表的庫存管理模板，以物料碼為主鍵，透過樞紐分析、VLOOKUP、篩選等功能自動整理出即時庫存、平均月耗用量與庫存百分比等關鍵資訊。研究重點包括三項應用：一、自動比對庫存資訊，可快速找出需補貨品項；二、缺料整理，以顏色標示庫存低於50%的品項，提高檢視效率；三、訂單建議產出，將需補品項依供應商彙整，減少手動比對的時間與錯誤。結果顯示，導入Excel模板後，原本每次需耗時152.9分鐘的訂單核對流程大幅縮短至10分鐘，效率提升超過九成。每月可節省19小時工作時間，換算薪資可年省約新台幣$43,320元，效益顯著。研究結論指出，Excel函數不僅降低人為錯誤，也提高了數據處理的即時性與準確性，特別適用於具備初步Excel技能的使用者。建議未來可進一步結合VBA、Power Query、Power BI等工具，發展成更高階的視覺化管理系統，並推廣至醫療後勤其他作業中，以強化整體醫療資源管理能力。

This study investigates the management efficiency and accuracy challenges faced by the central warehouse of a hospital. Previously, the hospital relied on a self-developed Hospital Information System (HIS) for inventory management. However, due to the system's lack of automation and data integration capabilities, warehouse staff were required to manually check inventory and orders item by item. This process was time-consuming, inefficient, and prone to errors. To address these issues, the study introduced Excel function techniques to design an integrated inventory management template. Using material codes as the primary key, the template leverages tools such as PivotTables, VLOOKUP, and filtering functions to automatically compile key information including real-time inventory, average monthly, consumption and inventory percentage. The study focused on three main applications: 1. Automatic Inventory Comparison - enabling quick identification of items that need restocking; 2. Shortage Highlighting - marking items with less than 50% stock in color to enhance visual inspection efficiency; 3. Order Recommendation Generation - consolidating items needing restocking by supplier, reducing manual comparison time and errors. The results showed that after implementing the Excel template, the time required for each order review process was significantly reduced from 152.9 minutes to just 10 minutes-an over 90% improvement in efficiency. This change translates to a monthly savings of 19 working hours, which equals approximately NT$43,320 in annual salary savings, demonstrating substantial benefits. In conclusion, the study found that Excel functions not only reduce human error but also improve the timeliness and accuracy of data processing. This approach is especially suitable for users with basic Excel skills. It is recommended that future developments incorporate tools such as VBA, Power Query, and Power BI to evolve into more advanced visual management systems. These could then be extended to other areas of medical logistics to strengthen overall healthcare resource management capabilities.