Method for constructing pharmacy auxiliary inventory module based on Excel VBA technology

Through the pharmacy auxiliary inventory module based on Excel VBA technology, the problem of low efficiency in pharmacy inventory is solved, the automation and accuracy of drug management is achieved, the optimization of drug procurement and management strategies is improved, and it is applicable to different pharmacy needs and reduced cost.

CN119003603APending Publication Date: 2024-11-22THE FIRST AFFILIATED HOSPITAL OF GUANGXI MEDICAL UNIVERSITY
View PDF 0 Cites 0 Cited by

Patent Information

Application Number
CN202410922552.1
Authority / Receiving Office
CN · China
Patent Type
Applications(China)
Current Assignee / Owner
Filing Date
2024-07-10
Publication Date
2024-11-22

AI Technical Summary

Technical Problem

The existing technology has failed to effectively improve the automation level of pharmacy inventory work, resulting in a wide variety of drugs and the same drug occupies multiple cargo spaces, and the inventory work efficiency is inefficient and requires a lot of human resources.

Method used

The pharmacy assisted inventory module is built based on Excel VBA technology, including inventory table production, data processing and secondary inventory table generation. Through automated acquisition and cleaning of data, inventory tables are generated and drug profits and losses are analyzed, manual operations are reduced, and work efficiency is improved.

Benefits of technology

It realizes automation of pharmacy inventory, quickly process data, reduces time costs, improves the accuracy and efficiency of drug management, is suitable for the specific needs of different pharmacies, and reduces costs.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN119003603A_ABST
    Figure CN119003603A_ABST
Patent Text Reader

Abstract

The invention relates to the technical field of pharmacy auxiliary inventory, in particular to a pharmacy auxiliary inventory module construction method based on the Excel VBA technology, comprising: constructing an inventory table making module based on the Excel VBA technology, the inventory table making module being used for obtaining inventory demand data and generating an inventory table based on the inventory demand data; a second-disk table manufacturing module is constructed, and the second-disk table manufacturing module is used for performing data screening on the first-disk table according to a preset threshold value and generating a corresponding second-disk table based on the screened data; and a checking data processing module is constructed, and the checking data processing module is used for sending out a last month checking summary situation notice, confirming a contrastive analysis field, automatically comparing the checking data of the current month with the checking data of the last month, and deeply analyzing the profit and loss situations of the medicines. According to the invention, data can be quickly processed, manual operation is reduced, the inventory accuracy is ensured, the inventory working efficiency of a pharmacy is remarkably improved, the inventory time cost is reduced, and data support is provided to optimize drug purchase and management.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present invention relates to the technical field of pharmacy auxiliary inventory counting, and in particular to a method for constructing a pharmacy auxiliary inventory counting module based on Excel VBA technology. Background Art

[0002] In the context of the information age, the medical industry is actively introducing advanced technologies to improve service quality and the level of modernization of management. Hospital pharmacy automation has become a global trend, which not only improves the quality of pharmaceutical services, but also provides technical support for more effective use of pharmacy data.

[0003] With the continuous advancement of new technologies, the safety of patients' medication has been significantly enhanced. At the same time, higher requirements have been placed on the professional development of pharmacists. As one of the core departments of medical institutions, the efficiency and accuracy of drug inventory in pharmacies are crucial to drug management. Therefore, how to use information technology to improve the efficiency and accuracy of pharmacy inventory has become a common focus of research in the field at home and abroad.

[0004] In foreign countries, many pharmacies and medical institutions have adopted advanced pharmacy management systems, and introduced barcode scanning, radio frequency identification technology, biometrics technology and pharmacy automation technology to improve the accuracy of drug use. In China, with the popularization and upgrading of hospital information systems, more and more medical institutions have begun to use electronic data exchange and information systems to assist inventory work.

[0005] However, although existing technologies have provided support for pharmacy assisted inventory work, the level of automation of pharmacy inventory counting processes is still low. Faced with the situation where hospital pharmacies have a wide variety of drugs and the same drug occupies multiple shelves, the current inventory work involves multiple tedious steps such as making inventory sheets, generating secondary inventory sheets, and analyzing inventory data. Existing technologies have failed to effectively address the challenges of automated inventory data processing in pharmacies. These tasks are not only time-consuming, but also require a lot of human resources.

[0006] To sum up, exploring how to significantly improve pharmacy inventory efficiency and simplify manual operations has become an urgent problem that needs to be solved. Summary of the invention

[0007] To solve the above problems, the present invention provides a method for constructing a pharmacy auxiliary inventory module based on Excel VBA technology, which is used to improve the pharmacy inventory efficiency and simplify manual operations.

[0008] In order to achieve the above object, the technical solution of the present invention is as follows: a method for constructing a pharmacy auxiliary inventory module based on Excel VBA technology, comprising: constructing an inventory table making module based on Excel VBA technology, the inventory table making module is used to obtain inventory demand data, and generate a corresponding inventory table based on the inventory demand data;

[0009] Construct a second disk table production module, which is used to filter data from the first disk table according to a preset threshold and generate a corresponding second disk table based on the filtered data;

[0010] Build an inventory data processing module, which is used to issue a notification of the inventory summary of the previous month, confirm the comparative analysis fields, automatically compare the inventory data of the current month with that of the previous month, and conduct in-depth analysis of the profit and loss of drugs.

[0011] Furthermore, the inventory list making module is used to obtain HIS system data and inventory requirements, and to filter out inventory requirement data matching the inventory requirements from the HIS system data, and to generate a corresponding inventory list based on the inventory requirement data.

[0012] Further, obtain HIS system data and inventory requirements, including:

[0013] Data preparation, receiving inventory requirements, and extracting the data required for a table from the HIS system according to the inventory requirements;

[0014] Data cleaning: verifying the integrity and accuracy of the extracted data required for inventory; cleaning and repairing the data, and marking the data that does not meet the threshold for integrity and accuracy;

[0015] Export data, export the data required for inventory after cleaning, repair and marking, and temporarily store it as the basic data for generating the corresponding inventory table.

[0016] Furthermore, the inventory requirements include the inventory date and time range and the scope of the drugs to be counted; the data required for the inventory include the drug list, inventory quantity, inventory location, drug price and supplier information.

[0017] Furthermore, data is filtered for a table according to a preset threshold, including:

[0018] According to the corresponding data of a plate table exported from the HIS system, construct an array containing required fields and check the field integrity; retain the required field columns, delete other columns and the "Total" row, and cut the large infusion information to a new workbook; according to the user's preset thresholds, automatically filter the drugs with profit and loss amounts and profit and loss quantities, and exclude drugs that do not require secondary inventory.

[0019] Furthermore, required fields include code, name, product name, specification, frozen quantity, firm quantity, profit and loss amount, and profit and loss quantity.

[0020] Furthermore, the inventory data processing module is used to extract data from the inventory table of last month and the new inventory table of the current month, and output the inventory data of last month and the inventory data of the current month. The inventory data of last month and the inventory data of the current month at least include the name, specification and actual quantity of the medicine, compare the inventory data of the current month and last month, and calculate the profit and loss data of the medicine.

[0021] Furthermore, the inventory data processing module is also used to analyze the reasons for profit and loss, including changes in sales volume, inventory loss, and mis-delivery and loss of drugs.

[0022] Furthermore, the inventory data processing module is also used to monitor historical inventory data and the latest inventory data, analyze the historical inventory data and the latest inventory data to output drug procurement strategies and management strategies.

[0023] The above scheme has the following beneficial effects:

[0024] 1. This solution builds a pharmacy auxiliary inventory module based on Excel VBA technology, including automatic generation of inventory sheets, intelligent production of secondary inventory sheets, and analysis of inventory data. Compared with the existing inventory methods, it can quickly process data, reduce manual operations, ensure inventory accuracy, and provide data support to optimize drug procurement and management.

[0025] 2. Compared with the existing inventory counting method, this solution greatly improves work efficiency, reduces time costs, and brings significant improvements and enhancements to the management of pharmacies.

[0026] 3. In this solution, VBA technology can be customized according to the actual needs of pharmacies to meet the specific management needs of different pharmacies. Compared with purchasing professional drug management software, the inventory module built based on Excel VBA technology is less expensive and can meet the inventory needs of pharmacies.

[0027] Additional aspects and advantages of the present invention will be given in part in the following description and in part will be obvious from the following description, or will be learned through practice of the present invention. BRIEF DESCRIPTION OF THE DRAWINGS

[0028] Figure 1 It is a flow chart of an embodiment of a method for constructing a pharmacy auxiliary inventory module based on Excel VBA technology of the present invention;

[0029] Figure 2 It is a module structure diagram of an embodiment of a method for constructing a pharmacy auxiliary inventory module based on Excel VBA technology of the present invention;

[0030] Figure 3The figure is a schematic diagram of a specific technical route of an embodiment of a method for constructing a pharmacy auxiliary inventory module based on Excel VBA technology of the present invention. DETAILED DESCRIPTION

[0031] The technical solution of the present invention will be described clearly and completely below in conjunction with the accompanying drawings. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0032] The following is further described in detail through specific implementation methods:

[0033] Example 1 is as shown in the attached Figure 1-Figure 3 As shown: A method for constructing a pharmacy auxiliary inventory module based on Excel VBA technology mainly includes the following steps:

[0034] S1. Construct an inventory table making module based on Excel VBA technology. The inventory table making module is used to obtain inventory demand data and generate a corresponding inventory table based on the inventory demand data. Specifically, this embodiment obtains the data required to construct the inventory table based on the hospital information system (HIS). As a core component of hospital informatization construction, the HIS system (Hospital Information System) integrates the data and information of various departments and business processes of the hospital, and can meet the needs of generating inventory tables. In addition, the inventory table content is obtained according to different requirements, which can be input by the staff or preset in advance.

[0035] The acquisition of HIS system data mainly includes the following steps:

[0036] Data preparation, receiving inventory requirements, extracting the data required for inventory from the HIS system according to the inventory requirements; usually, inventory requirements include the inventory date and time range and the scope of the inventory drugs, and the data required for inventory include drug list, inventory quantity, inventory location, drug price and supplier information, etc. Among them, the drug list includes information such as drug name, specification, unit, etc.; the inventory quantity records the actual inventory quantity of each drug; the inventory location indicates the location or area where each drug is stored; the drug price records the purchase price or retail price of each drug; the supplier information records the supplier's name, contact information and other information for further supply chain management.

[0037] Data cleaning: verify the integrity and accuracy of the extracted data required for inventory; clean and repair the data, and mark the data whose integrity and accuracy do not meet the threshold; among them, integrity and accuracy mainly include missing value check: check whether there are missing fields in the data, such as drug name, specification, inventory quantity, etc.; use data cleaning tools to identify and process missing fields, such as filling missing values, deleting missing values, etc. Outlier detection: based on whether there are outliers in the data based on the user's preset threshold, such as: inventory quantities exceeding the threshold range, unreasonable prices, etc. are judged as outliers, and prompt the user together with the missing fields. Data consistency verification: ensure the consistency between different data fields, such as whether the drug name and specification match, whether the inventory quantity is reasonable, etc. Duplicate data check: check whether there are duplicate records in the data. Mark the data whose integrity and accuracy do not meet the threshold in the above process.

[0038] Export data, export the data required for inventory after cleaning, repair and marking, and temporarily store it as the basic data for generating the corresponding inventory forms, and generate the corresponding inventory forms based on these basic data. This process effectively optimizes the pharmacy inventory process, reduces time costs, and can quickly output inventory forms in a short time.

[0039] Specifically, use VBA code to process the original inventory data exported from the HIS system to ensure that all required fields (code, name, product name, specification, packaging unit, storage location) are intact, otherwise a pop-up window will appear. Keep the columns where the required fields are located, delete other columns and the "Total" row, and then re-sort the columns according to the order of the fields specified above. Automatically identify the maximum number of positions in the "Storage Location" field, split the "Storage Location" column, and create a number of worksheets equal to the maximum number of "Storage Location" positions. Each worksheet only retains one column of storage location data to achieve data separation. Merge the split worksheets to generate the final inventory sheet.

[0040] S2. Construct a second disk table production module, which is used to filter data from the first disk table according to a preset threshold, and generate a corresponding second disk table based on the filtered data; this step can effectively ensure the accuracy of the inventory.

[0041] Specifically, it includes: constructing an array containing required fields based on the corresponding data of the first plate table exported from the HIS system, and checking the integrity of the fields; retaining the required field columns, deleting other columns and the "Total" row, and cutting the large infusion information to a new workbook; automatically filtering the profit and loss amount and profit and loss quantity of drugs according to the user's preset threshold, excluding drugs that do not require secondary inventory, and improving the screening efficiency and accuracy. Among them, the required fields include code, name, product name, specification, frozen quantity, actual quantity, profit and loss amount, and profit and loss quantity; finally, generating the corresponding second plate table based on the filtered data.

[0042] S3. Construct an inventory data processing module. The inventory data processing module is used to issue a notification on the inventory summary of the previous month, ask the user whether the inventory summary of the previous month has been placed, and confirm the fields for comparison and analysis (code, name, profit and loss quantity, identification). Confirm the comparative analysis fields; and automatically identify the profit and loss of the drugs. Specifically, automatically identify the profit and loss of the drugs, mark the fully hedged drug rows as green, and the incompletely hedged drug rows as yellow, and sort them. Mark the fully hedged drugs as "A" and the incompletely hedged drugs as "B", quickly identify the reasons for the drug's profit and loss, and facilitate analysis.

[0043] Specifically, the inventory data processing module is used to extract the data in the inventory table of the previous month and the new inventory table of the current month, and output the inventory data of the previous month and the current month. The inventory data of the previous month and the current month at least include the drug name, specification, initial inventory and actual inventory. The completeness and accuracy of the inventory data of the previous month and the current month also need to be analyzed. Then the inventory data of the current month and the previous month are compared to calculate the profit and loss data of the drug. The reasons for profit and loss are analyzed, including sales volume changes, inventory loss, missing and expiration.

[0044] The inventory data processing module is also used to monitor historical inventory data and the latest inventory data, analyze historical inventory data and the latest inventory data to output drug procurement strategies and management strategies. Based on the results of the profit and loss analysis, make decisions on drug procurement and management. For surplus drugs, consider adjusting inventory levels, optimizing sales strategies, etc.; for loss-making drugs, consider strengthening inventory management, optimizing procurement processes, or strengthening safety measures to prevent losses, etc. In addition, drug inventory data can be continuously monitored and continuously optimized and improved. For example, an early warning system can be established to detect abnormal situations in a timely manner and take corresponding measures; based on historical data and trend analysis, a long-term drug management strategy can be formulated.

[0045] The specific implementation code is as follows:

[0046] 1. Inventory sheet making module

[0047]

[0048]

[0049]

[0050]

[0051] 2. Second disk table production code

[0052]

[0053]

[0054]

[0055] 3. Data processing module code

[0056]

[0057]

[0058] Obviously, the above embodiments are merely examples for the purpose of clear explanation, and are not intended to limit the implementation methods. For those skilled in the art, other different forms of changes or modifications can be made based on the above description. It is not necessary and impossible to list all the implementation methods here. The obvious changes or modifications derived therefrom are still within the scope of protection of the invention.

Claims

1. A method for constructing a pharmacy auxiliary inventory module based on Excel VBA technology, characterized in that: include: Based on Excel VBA technology, an inventory sheet making module is constructed. The inventory sheet making module is used to obtain inventory demand data and generate a corresponding inventory sheet based on the inventory demand data; Construct a second disk table production module, which is used to filter data from the first disk table according to a preset threshold and generate a corresponding second disk table based on the filtered data; Build an inventory data processing module, which is used to issue a notification of the inventory summary of the previous month, confirm the comparative analysis fields, automatically compare the inventory data of the current month with that of the previous month, and conduct in-depth analysis of the profit and loss of drugs.

2. The method for constructing a pharmacy auxiliary inventory module based on Excel VBA technology according to claim 1, characterized in that: The inventory list making module is used to obtain HIS system data and inventory requirements, filter out inventory requirement data matching the inventory requirements from the HIS system data, and generate a corresponding inventory list based on the inventory requirement data.

3. The method for constructing a pharmacy auxiliary inventory module based on Excel VBA technology according to claim 2 is characterized in that: Obtain HIS system data and inventory requirements, including: Data preparation, receiving inventory requirements, and extracting the data required for a table from the HIS system according to the inventory requirements; Data cleaning: verifying the integrity and accuracy of the extracted data required for inventory; cleaning and repairing the data, and marking the data that does not meet the threshold for integrity and accuracy; Export data, export the data required for inventory after cleaning, repair and marking, and temporarily store it as the basic data for generating the corresponding inventory table.

4. The method for constructing a pharmacy auxiliary inventory module based on Excel VBA technology according to claim 3 is characterized in that: Inventory requirements include the inventory date and time range and the scope of the drugs to be counted; the data required for the inventory includes the drug list, inventory quantity, inventory location, drug price and supplier information.

5. The method for constructing a pharmacy auxiliary inventory module based on Excel VBA technology according to claim 2, characterized in that: Filter data in a table based on preset thresholds, including: According to the corresponding data of a plate table exported from the HIS system, an array is constructed to include the required fields and the field integrity is checked; the required field columns are retained, other columns and the "Total" row are deleted, and the large infusion information is cut to a new workbook; according to the user's preset threshold, the drugs with profit and loss amounts and profit and loss quantities are automatically filtered, and drugs that do not require secondary inventory are excluded.

6. The method for constructing a pharmacy auxiliary inventory module based on Excel VBA technology according to claim 5 is characterized in that: Required fields include code, name, product name, specification, frozen quantity, firm quantity, profit and loss amount, and profit and loss quantity.

7. The method for constructing a pharmacy auxiliary inventory module based on Excel VBA technology according to claim 1, characterized in that: The inventory data processing module is used to extract data from the inventory table of the previous month and the new inventory table of the current month, and output the inventory data of the previous month and the inventory data of the current month. The inventory data of the previous month and the inventory data of the current month at least include the name, specifications and actual quantity of the medicine, compare the inventory data of the current month and the previous month, and calculate the profit and loss data of the medicine.

8. The method for constructing a pharmacy auxiliary inventory module based on Excel VBA technology according to claim 7, characterized in that: The inventory data processing module is also used to analyze the reasons for profit and loss, including sales volume changes, inventory losses, and mis-delivery and missing drugs.

9. The method for constructing a pharmacy auxiliary inventory module based on Excel VBA technology according to claim 1, characterized in that: The inventory data processing module is also used to monitor historical inventory data and the latest inventory data, analyze historical inventory data and the latest inventory data to output drug procurement strategies and management strategies.