CN112331315A - Nurse automatic scheduling method and system based on VBA - Google Patents
Nurse automatic scheduling method and system based on VBA Download PDFInfo
- Publication number
- CN112331315A CN112331315A CN202011118953.XA CN202011118953A CN112331315A CN 112331315 A CN112331315 A CN 112331315A CN 202011118953 A CN202011118953 A CN 202011118953A CN 112331315 A CN112331315 A CN 112331315A
- Authority
- CN
- China
- Prior art keywords
- scheduling
- shift
- vba
- solution
- nurse
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Granted
Links
- 238000000034 method Methods 0.000 title claims abstract description 32
- 238000005457 optimization Methods 0.000 claims abstract description 15
- 238000009826 distribution Methods 0.000 claims abstract description 7
- 230000006870 function Effects 0.000 claims description 29
- 238000004364 calculation method Methods 0.000 claims description 8
- 230000001351 cycling effect Effects 0.000 claims description 4
- 230000008859 change Effects 0.000 claims description 3
- 238000004040 coloring Methods 0.000 claims description 2
- 230000003203 everyday effect Effects 0.000 claims description 2
- 238000002474 experimental method Methods 0.000 description 4
- 238000010845 search algorithm Methods 0.000 description 4
- 238000013461 design Methods 0.000 description 3
- 238000006243 chemical reaction Methods 0.000 description 2
- 238000010276 construction Methods 0.000 description 2
- 230000002354 daily effect Effects 0.000 description 2
- 238000001514 detection method Methods 0.000 description 2
- 238000011156 evaluation Methods 0.000 description 2
- 230000002452 interceptive effect Effects 0.000 description 2
- 238000012423 maintenance Methods 0.000 description 2
- 230000004048 modification Effects 0.000 description 2
- 238000012986 modification Methods 0.000 description 2
- 230000008569 process Effects 0.000 description 2
- 230000009471 action Effects 0.000 description 1
- 230000009286 beneficial effect Effects 0.000 description 1
- 230000009194 climbing Effects 0.000 description 1
- 230000007547 defect Effects 0.000 description 1
- 238000011161 development Methods 0.000 description 1
- 230000006872 improvement Effects 0.000 description 1
- 230000007774 longterm Effects 0.000 description 1
- 230000007246 mechanism Effects 0.000 description 1
- 230000000474 nursing effect Effects 0.000 description 1
- 238000010926 purge Methods 0.000 description 1
- 238000012360 testing method Methods 0.000 description 1
Images
Classifications
-
- G—PHYSICS
- G16—INFORMATION AND COMMUNICATION TECHNOLOGY [ICT] SPECIALLY ADAPTED FOR SPECIFIC APPLICATION FIELDS
- G16H—HEALTHCARE INFORMATICS, i.e. INFORMATION AND COMMUNICATION TECHNOLOGY [ICT] SPECIALLY ADAPTED FOR THE HANDLING OR PROCESSING OF MEDICAL OR HEALTHCARE DATA
- G16H40/00—ICT specially adapted for the management or administration of healthcare resources or facilities; ICT specially adapted for the management or operation of medical equipment or devices
- G16H40/20—ICT specially adapted for the management or administration of healthcare resources or facilities; ICT specially adapted for the management or operation of medical equipment or devices for the management or administration of healthcare resources or facilities, e.g. managing hospital staff or surgery rooms
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F8/00—Arrangements for software engineering
- G06F8/30—Creation or generation of source code
- G06F8/31—Programming languages or programming paradigms
- G06F8/315—Object-oriented languages
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y02—TECHNOLOGIES OR APPLICATIONS FOR MITIGATION OR ADAPTATION AGAINST CLIMATE CHANGE
- Y02P—CLIMATE CHANGE MITIGATION TECHNOLOGIES IN THE PRODUCTION OR PROCESSING OF GOODS
- Y02P90/00—Enabling technologies with a potential contribution to greenhouse gas [GHG] emissions mitigation
- Y02P90/30—Computing systems specially adapted for manufacturing
Landscapes
- Engineering & Computer Science (AREA)
- Software Systems (AREA)
- General Business, Economics & Management (AREA)
- Health & Medical Sciences (AREA)
- Business, Economics & Management (AREA)
- Theoretical Computer Science (AREA)
- General Engineering & Computer Science (AREA)
- Biomedical Technology (AREA)
- Primary Health Care (AREA)
- Public Health (AREA)
- Computing Systems (AREA)
- Medical Informatics (AREA)
- General Health & Medical Sciences (AREA)
- Physics & Mathematics (AREA)
- General Physics & Mathematics (AREA)
- Epidemiology (AREA)
- Management, Administration, Business Operations System, And Electronic Commerce (AREA)
- Medical Treatment And Welfare Office Work (AREA)
Abstract
The invention provides a method and a system for realizing automatic scheduling of nurses based on VBA, wherein the method comprises the following steps: acquiring the shift requirements and nurse personnel data of at least one day; obtaining an initial scheduling result by adopting a random distribution mode according to the acquired scheduling requirements and nurse personnel data; obtaining an optimal scheduling result according to a preset scheduling constraint, an initial scheduling result and a scheduling optimization algorithm combining variable neighborhood and local search; the VBA is used for storing a scheduling table, different characters of the array represent different shifts, and the VBA codes traverse the two-dimensional array of the scheduling table to convert the characters into the names of the shifts and fill the names of the shifts back to the scheduling interface; the method and the system can reduce the deployment complexity and cost of the information system to the maximum extent, the modularization of the system is high, the system is convenient to expand and maintain, and the system does not need the support of any other third-party plug-in.
Description
Technical Field
The disclosure relates to the technical field of nurse scheduling automation, in particular to a nurse automatic scheduling method and system based on VBA.
Background
The statements in this section merely provide background information related to the present disclosure and may not necessarily constitute prior art.
The problem of nurse scheduling in hospitals is a complex class of staff allocation problems. Although many efficient algorithms have been proposed in recent years, it is still a complicated and arduous task to integrate these algorithms into existing hospital information systems or to design and develop separate shift software systems.
The inventor of the present disclosure finds that not only the complex scheduling algorithm, the solver call and the interactive interface design need to be realized, but also the codes need to be continuously maintained and expanded, and the scheduling system needs to be integrated with the current information system of the hospital or deployed independently. Therefore, many hospital shift jobs still use manual or semi-manual methods. When the number of nurses is large, the scheduling is time-consuming, and the quality can not be guaranteed.
Disclosure of Invention
In order to solve the defects of the prior art, the disclosure provides a nurse automatic scheduling method and system based on VBA, which can reduce the deployment complexity and cost of an information system to the maximum extent, have higher modularization of the system, are convenient to expand and maintain, and do not need the support of any other third-party plug-in.
In order to achieve the purpose, the following technical scheme is adopted in the disclosure:
the first aspect of the disclosure provides a nurse automatic scheduling method based on VBA.
A nurse automatic scheduling method based on VBA comprises the following steps:
acquiring the shift requirements and nurse personnel data of at least one day;
obtaining an initial scheduling result by adopting a random distribution mode according to the acquired scheduling requirements and nurse personnel data;
obtaining an optimal scheduling result according to a preset scheduling constraint, an initial scheduling result and a scheduling optimization algorithm combining variable neighborhood and local search;
the VBA is used for storing a scheduling table, different characters of the array represent different shifts, and the VBA code traverses the two-dimensional array of the scheduling table to convert the characters into the names of the shifts and fill the names of the shifts back to the scheduling interface.
As some possible implementation manners, the shift scheduling optimization algorithm combining variable neighborhood and local search specifically includes:
taking the obtained initial shift arrangement result as a current solution and a global optimal solution;
setting a stagnation counter to be zero, if the upper limit of the calculation time is not reached, adding one to the stagnation counter, copying a copy of the current solution as a candidate solution, generating a random number, and performing group exchange on the candidate solution;
if the objective function value of the candidate solution is smaller than or equal to the function value of the current solution, local search is used for the candidate solution to obtain a local optimal solution as the candidate solution, if the objective function value of the candidate solution obtained through the local search is smaller than or equal to the global optimal solution, the global optimal solution is updated, the stagnation counter is reset to be zero, and at the moment, the scheduling result that the global optimal solution is the optimal solution is output.
As a further limitation, if the objective function value of the candidate solution is greater than the function value of the current solution and the stall counter is greater than the upper limit, setting the stall counter to zero;
and setting the global optimal solution as the current solution, disturbing the current solution, and if the upper limit of the calculation time is not reached, continuing to circulate until the global optimal solution is obtained.
As a further limitation, the group exchange specifically includes: randomly selecting a continuous time period, wherein the starting position and the ending position are generated in a random mode, randomly selecting two nurses in the time period, and then exchanging the shifts of the two nurses in the shift list.
By way of further limitation, the perturbation is a random change to a solution, specifically reassigning a nurse's shift.
As a further limitation, the local search specifically includes:
all days k cycles;
cycling from 1 to N-1 for nurse i, and cycling from i to N for nurse j;
saving the current solution using a temporary solution;
the shift of nurses i and j on day k is swapped for the temporary solution;
if the temporary solution is better, the temporary solution is changed into the current solution;
and returning to the current solution.
As some possible implementation manners, for different shifts, the macro codes formatted by the cell are used for background coloring of the different shifts, the keys are linked with functions in the VBA, and the functions of filing, clearing and locking are realized by using self-contained functions in the VBA.
As some possible implementations, the overall objective function of the shift table is the sum of the penalty values of all constraints multiplied by the weight of each constraint, the constraints including mandatory and non-mandatory constraints;
mandatory constraints include: each employee can only be scheduled to one shift every day, the shift required in each department room must be met, the rest needs to be scheduled after night shift, the work shift scheduled at the previous shift needs to be fixed, and the rest needs to be scheduled in the appointed holiday;
non-mandatory constraints include: the maximum working days per week of a single person cannot exceed the upper limit, the maximum night shift days per week of the single person cannot exceed the upper limit, independent working days do not appear, a night shift is arranged before the night shift, the maximum continuous working days cannot be exceeded, and the total number of shifts arranged by each nurse is equal within a period of time.
As some possible implementations, in all constraints related to consecutive days, an additional table page is used to store the previous shift, and the end of the current shift is considered in the constraint description as the beginning of the new shift.
As some possible implementation manners, the VBA code is used to traverse the two-dimensional data of the shift table, check the times of non-compliance with the constraint condition, and record and return the weighted sum of the relevant penalty values.
The second aspect of the disclosure provides a nurse automatic scheduling system based on VBA.
A nurse automatic scheduling system based on VBA realization includes:
a data acquisition module configured to: acquiring the shift requirements and nurse personnel data of at least one day;
an initial shift schedule module configured to: obtaining an initial scheduling result by adopting a random distribution mode according to the acquired scheduling requirements and nurse personnel data;
an optimal shift module configured to: obtaining an optimal scheduling result according to a preset scheduling constraint, an initial scheduling result and a scheduling optimization algorithm combining variable neighborhood and local search;
the VBA is used for storing a scheduling table, different characters of the array represent different shifts, and the VBA code traverses the two-dimensional array of the scheduling table to convert the characters into the names of the shifts and fill the names of the shifts back to the scheduling interface.
The third aspect of the present disclosure provides an electronic device, which includes the automatic nurse scheduling system implemented based on VBA according to the second aspect of the present disclosure.
Compared with the prior art, the beneficial effect of this disclosure is:
1. the method, the system or the electronic equipment are developed based on Excel software, the Excel software is ubiquitous in computers in China at present and is easy to obtain, therefore, the development of the software system by using the platform can reduce the deployment complexity and cost of the information system to the maximum extent, the modularization of the realization of the system is high, the expansion and the maintenance are convenient, and the system does not need the support of any other third-party plug-in.
2. The method, the system or the electronic equipment can solve the scheduling optimization problem of actual scale in real life, and simultaneously considers actual constraints and various actual requirements related to nursing scheduling in a hospital and constraints between two schedules, so that the overall scheduling balance is ensured.
3. According to the method, the system or the electronic equipment, the solving quality can reach the level of industrial-grade optimization software, a high-quality scheduling list can be obtained within acceptable time, the whole process is automatic scheduling, and a user only needs to provide parameters.
4. The method, the system or the electronic equipment disclosed by the disclosure provide a method for linking a theoretical nurse scheduling algorithm with practical application, other more effective optimization algorithms can be embedded into the system in subsequent expansion, and the system can be modified and used aiming at the nurse scheduling problem of different occasions.
Advantages of additional aspects of the disclosure will be set forth in part in the description which follows, and in part will be obvious from the description, or may be learned by practice of the disclosure.
Drawings
The accompanying drawings, which are included to provide a further understanding of the disclosure, illustrate embodiments of the disclosure and together with the description serve to explain the disclosure and are not to limit the disclosure.
Fig. 1 is a schematic flow chart of a method for automatically scheduling a nurse based on VBA according to embodiment 1 of the present disclosure.
Fig. 2 is a screenshot of a shift scheduling system interface provided in embodiment 1 of the present disclosure.
Detailed Description
The present disclosure is further described with reference to the following drawings and examples.
It should be noted that the following detailed description is exemplary and is intended to provide further explanation of the disclosure. Unless defined otherwise, all technical and scientific terms used herein have the same meaning as commonly understood by one of ordinary skill in the art to which this disclosure belongs.
It is noted that the terminology used herein is for the purpose of describing particular embodiments only and is not intended to be limiting of example embodiments according to the present disclosure. As used herein, the singular forms "a", "an" and "the" are intended to include the plural forms as well, and it should be understood that when the terms "comprises" and/or "comprising" are used in this specification, they specify the presence of stated features, steps, operations, devices, components, and/or combinations thereof, unless the context clearly indicates otherwise.
The embodiments and features of the embodiments in the present disclosure may be combined with each other without conflict.
Example 1:
as shown in fig. 1, an embodiment 1 of the present disclosure provides a method for automatically scheduling nurses based on VBA, including the following steps:
acquiring the shift requirements and nurse personnel data of at least one day;
obtaining an initial scheduling result by adopting a random distribution mode according to the acquired scheduling requirements and nurse personnel data;
obtaining an optimal scheduling result according to a preset scheduling constraint, an initial scheduling result and a scheduling optimization algorithm combining variable neighborhood and local search;
the VBA is used for storing a scheduling table, different characters of the array represent different shifts, and the VBA code traverses the two-dimensional array of the scheduling table to convert the characters into the names of the shifts and fill the names of the shifts back to the scheduling interface.
In detail, firstly, a shift scheduling system is constructed, and the method comprises the following steps:
1) establishing a system interactive interface; setting the position of the parameter cell; the relevant parameters are read using VBA code.
The main parameters include the number of people scheduled, the number of days, the type of shift, the daily shift demand, the vacation requirements of nurses, etc.
2) A two-dimensional array is used in the VBA to store a shift table. Implementing detection codes of evaluation functions related to mandatory constraints and non-mandatory constraints according to a required model; and obtaining an overall evaluation function according to the established constraint, and providing a basis for a subsequent search algorithm.
3) The search algorithm is written and called, and the embodiment uses a modified random immigration domain algorithm to search the shift.
4) And constructing an interface conversion function for scheduling solving, and constructing codes related to interface keys and other tool functions to perfect the whole system.
In detail, the scheduling method comprises the following steps:
(1) design of the interface and reading of parameters.
To facilitate use by most healthcare managers, the present embodiment presents a shift list in the basic representation of the most common "people-days" two-dimensional list. Therefore, the interface mainly includes a shift page with the horizontal axis as days and the vertical axis as nurse numbers. The interface also includes other related elements such as keys, parameters, and indicative labels, etc. The interface is shown in the screenshot in fig. 2.
The shift demand in the graph includes the shift required per day. The nurse list is the serial number of the nurse, and the nurse list can be modified into the name of the nurse in the specific using process. To the right are parameters related to constraints, such as maximum number of working days, maximum number of night shift days, etc. The lower right part is a command key. On the left hand side is a nurse selection area, where a numerical value of 1 is used to indicate which nurses are selected. The slash in the shift table indicates that the current nurse is on vacation. Note that all shift contents in the shift list, including the slash, are automatically given by the system.
Relevant variables are established in VBA codes, parameters are read, for the shift requirement and the leave requirement, a two-dimensional array in VBA is adopted in the embodiment, and other parameters are stored by adopting independent variables.
(2) And the code realizes the detection code of the scheduling table and the constraint condition.
This embodiment models nurse scheduling as an optimization problem, using constraints to calculate an objective function for a scheduling table. The model of nurse shift may take different forms depending on the needs of a particular hospital department. For the convenience of subsequent expansion, the embodiment adopts a relatively common model.
The model includes four shifts: day shift (8: 00 early to 4:00 afternoon), night shift (4: 00 afternoon to 10:00 night), night shift (10: 00 night to 8:00 next day), and rest. The model is scheduled in days and weeks as a whole period, for example: a one or two week shift schedule may be generated at a time.
The constraints mainly considered by the model are as follows.
Mandatory constraints include (prefixed with H as a label):
h1: each employee can only be ranked to one shift per day.
H2: daily shifts of demand in the department must be met.
H3: the rest is required to be arranged after night work. One day of rest must be scheduled after each nurse has gone through the night shift.
H4: the work shift of the previous shift needs to be fixed. This constraint is mainly to solve the junction between two shifts, assuming that a nurse in the previous shift is a night shift, and the first day of the next shift must be rest according to H3. Other constraints associated with multiple consecutive days are similar.
H5: a well agreed vacation requires a break to be scheduled. This constraint is used to simulate the situation where a shift cannot be scheduled on the day of annual leave or leave.
Non-mandatory constraints include (prefixed with S as a label):
s1: the single maximum number of work days per week cannot exceed the upper limit. Penalty values are generated if the number of days a nurse works within a week exceeds a set upper limit. The calculation method is the number of days exceeded multiplied by the weight.
S2: the maximum night shift days per week for a single person cannot exceed the upper limit. The constraint is used for limiting the number of night shifts, and the calculation method is the same as the previous constraint.
S3: independent working days do not appear. If a nurse schedules a three day shift as: rest-work (shift arbitrary) -rest, a penalty occurs. The calculation method is to add one if this occurs once. This constraint is used to prevent situations where a continuous rest cannot be generated.
S4: the night shift is arranged before the night shift as much as possible. The constraint is a unique mode of operation for the local reference hospital. That is, if a nurse is a night shift on a certain day, the nurse tries to schedule a night shift on the following day. If the night shift is not followed by a night shift, the penalty value is increased by one.
S5: the maximum continuous day of operation cannot be exceeded. The constraint calculation method is different from S1 and S2 in that any number of consecutive days of operation needs to be checked.
S6: the total number of shifts scheduled by each nurse is as equal as possible over a period of time. The constraint is to characterize fairness among nurses. Due to the use of the Excel platform, the shift information can be conveniently recorded. Here, the average over a period of time is used as a basis, and a penalty occurs if the shift of a nurse differs from the average. The penalty value is the total number of shifts of the phase difference.
The overall objective function of a shift table is the penalty value of all constraints multiplied by the weight of each constraint. In VBA code, a two-dimensional array is used to represent a shift table. Each element of the array represents a different shift with a different english letter. All of the above constraints are characterized by loops. The specific mode is that the VBA codes are used for traversing the two-dimensional data of the shift table, the times of non-conformity with the constraint condition are checked, and the sum of relevant penalty values is recorded and returned. Hard constraints use larger weights and soft constraints use smaller weights.
In all constraints related to consecutive days, in order to consider the situation of the boundary between two consecutive shifts, the present embodiment adopts other Excel pages to store the last shift, and considers the end part of the shift as the beginning part of the new shift in the constraint description. The present embodiment considers the number of days of the previous shift as a fixed mandatory constraint.
To implement S6, a long-term nurse shift balance constraint, the present embodiment records a different number of shifts per nurse for a period of time on other pages. The mechanism can be adjusted by the user and maintenance personnel as required.
(3) And designing a search algorithm.
In order to be independent of other optimization solvers, the present embodiment adopts a random search algorithm implemented based on VBA. The algorithm is a shift scheduling optimization method combining variable neighborhood and local search. The pseudo-code of the algorithm is described below. The "solution" in the code is a shift table.
The key steps are explained as follows:
the construction of the line 1 initial solution is realized by randomly distributing the shift demands for each day;
the group exchange in row 7 is implemented by randomly selecting a continuous period of time, for example, 5 consecutive days, and the start position and the end position are both generated in a random manner; randomly selecting two nurses in the time period, and exchanging the shift of the two nurses in the shift schedule;
action 8, finding a better solution, using local search to continue searching for local optimum and updating a global optimum solution;
behavior 12 is a case where no better solution is found, where the perturbation refers to a random change of a solution, and in this embodiment, the shift of a nurse is reassigned;
the local search in line 9 is a hill climbing algorithm, and the specific algorithm is as follows:
(4) and constructing an interface conversion function and other tool functions of the shift scheduling solution.
For the optimal solution obtained by the algorithm, a VBA code is used for traversing the two-dimensional array of the shift list to convert the letter into the name of the shift and fill the name back to the shift interface. For different shifts, the different shifts were background colored using Excel cell formatted macro code. The key is connected to a function in the VBA. The archive, purge, and lock functions are implemented using the general implementation of the native functions in the VBA.
(5) Numerical experiment
In order to test the effectiveness of the method of the present embodiment, the present embodiment uses parameters and rules in several departments of a local hospital. The number of nurses in these departments is about [10, 18 ]; the number of shift-scheduling weeks is 2 to 4 weeks; the single person working upper limit per week is 4 or 5; the upper limit of night shifts is 1. Since the demand varies greatly in the department over the year, the study consults parameters over time and generates 15 parameter combinations as data examples. The size of the parameter is used here to name the data instance. For example, I-2-10-5, means 2 weeks, 10 people, and 5 weeks for work.
For comparison, the study established an integer programming model, and solved all the examples for the same data using the commercial-grade optimization solver, cplex12.7, and based on a Java implementation. An Excel version 2010 adopted by the system in the embodiment is; the experimental machine is Intel i5-6500,16.0GB memory; the operating system is Microsoft Windows 7.0. The upper time limit in the experiment was 30 minutes. The solution for Cplex was set to 1 hour and 4 threads were used. Since the algorithm was random, each data instance was run 10 times and the information was counted.
The results of the experiment are shown in table 1. Wherein the first column and the second column are example serial numbers and serial numbers; the third column is the value of the optimal solution obtained by Cplex; the third column is the lower bound of the relaxation model. The fifth column to the eighth column respectively represent the minimum value, the maximum value, the average value and the standard deviation obtained by 10 operations of the system. The table is marked in bold if the minimum value can find the same integer solution as Cplex.
Table 1: results of numerical experiments
Comparing the third and fifth columns of the graph (i.e., the minimum value that can be found by Cplex and the system described in this example), in all 15 examples, the algorithm used by the system described in this example can find the same optimal solution for the 86.7% (13/15) example as the Cplex solver. The determined optimal solution can be found by 8 examples of Cplex, and the algorithms of the system described in the embodiment can also be obtained in all cases. Looking at the sixty-seven-eight columns in the table, it can be seen that for the more difficult cases ( cases 3, 5, and 9, i.e. the maximum value cannot reach the optimal value), the maximum value and the minimum value of 10 runs of the algorithm of the system described in this embodiment are only different by 1, and the standard deviation is not more than 0.50. This data can prove the stability of the algorithm is high. In conclusion, the system of the embodiment can obtain a solution with the same quality as that of the commercial solver within an acceptable time, so that the task of automatic scheduling can be completed.
Example 2:
the embodiment 2 of the present disclosure provides an automatic nurse scheduling system implemented based on VBA, including:
a data acquisition module configured to: acquiring the shift requirements and nurse personnel data of at least one day;
an initial shift schedule module configured to: obtaining an initial scheduling result by adopting a random distribution mode according to the acquired scheduling requirements and nurse personnel data;
an optimal shift module configured to: obtaining an optimal scheduling result according to a preset scheduling constraint, an initial scheduling result and a scheduling optimization algorithm combining variable neighborhood and local search;
the VBA is used for storing a scheduling table, different characters of the array represent different shifts, and the VBA code traverses the two-dimensional array of the scheduling table to convert the characters into the names of the shifts and fill the names of the shifts back to the scheduling interface.
The construction method and the working method of the system are the same as those in embodiment 1, and are not described again here.
Example 3:
an embodiment 3 of the present disclosure provides an electronic device, including the automatic nurse scheduling system implemented based on VBA described in embodiment 2 of the present disclosure.
The above description is only a preferred embodiment of the present disclosure and is not intended to limit the present disclosure, and various modifications and changes may be made to the present disclosure by those skilled in the art. Any modification, equivalent replacement, improvement and the like made within the spirit and principle of the present disclosure should be included in the protection scope of the present disclosure.
Claims (10)
1. A nurse automatic scheduling method based on VBA is characterized by comprising the following steps:
acquiring the shift requirements and nurse personnel data of at least one day;
obtaining an initial scheduling result by adopting a random distribution mode according to the acquired scheduling requirements and nurse personnel data;
obtaining an optimal scheduling result according to a preset scheduling constraint, an initial scheduling result and a scheduling optimization algorithm combining variable neighborhood and local search;
the VBA is used for storing a scheduling table, different characters of the array represent different shifts, and the VBA code traverses the two-dimensional array of the scheduling table to convert the characters into the names of the shifts and fill the names of the shifts back to the scheduling interface.
2. The automatic scheduling method for nurses based on VBA implementation as claimed in claim 1, wherein the scheduling optimization algorithm combining neighborhood variation and local search specifically comprises:
taking the obtained initial shift arrangement result as a current solution and a global optimal solution;
setting a stagnation counter to be zero, if the upper limit of the calculation time is not reached, adding one to the stagnation counter, copying a copy of the current solution as a candidate solution, generating a random number, and performing group exchange on the candidate solution;
if the objective function value of the candidate solution is smaller than or equal to the function value of the current solution, local search is used for the candidate solution to obtain a local optimal solution as the candidate solution, if the objective function value of the candidate solution obtained through the local search is smaller than or equal to the global optimal solution, the global optimal solution is updated, the stagnation counter is reset to be zero, and at the moment, the scheduling result that the global optimal solution is the optimal solution is output.
3. The VBA-based nurse automatic scheduling method of claim 2, wherein if the objective function value of the candidate solution is greater than the function value of the current solution and the stall counter is greater than the upper limit, the stall counter is set to zero;
setting the global optimal solution as a current solution, disturbing the current solution, and if the current solution does not reach the upper limit of the calculation time, continuing to circulate until the global optimal solution is obtained;
or,
the group switching specifically comprises: randomly selecting a period of continuous time, generating a starting position and an ending position in a random mode, randomly selecting two nurses in the period of time, and exchanging the shift of the two nurses in a shift schedule;
or,
the perturbation is a random change to a solution, specifically, a reassignment of a nurse's shift.
4. The automatic nurse scheduling method implemented based on VBA as claimed in claim 2, wherein the local search specifically includes:
all days k cycles;
cycling from 1 to N-1 for nurse i, and cycling from i to N for nurse j;
saving the current solution using a temporary solution;
the shift of nurses i and j on day k is swapped for the temporary solution;
if the temporary solution is better, the temporary solution is changed into the current solution;
and returning to the current solution.
5. The method of claim 1, wherein for different shifts, background coloring is performed on the different shifts using cell formatted macro codes, linking keys with functions in the VBA, and archiving, clearing, and locking functions are performed using self-contained functions in the VBA.
6. The VBA-based implemented nurse automatic scheduling method of claim 1, wherein an overall objective function of the scheduling table is a sum of penalty values of all constraints multiplied by weights of each constraint, the constraints comprising mandatory and non-mandatory constraints;
mandatory constraints include: each employee can only be scheduled to one shift every day, the shift required in each department room must be met, the rest needs to be scheduled after night shift, the work shift scheduled at the previous shift needs to be fixed, and the rest needs to be scheduled in the appointed holiday;
non-mandatory constraints include: the maximum working days per week of a single person cannot exceed the upper limit, the maximum night shift days per week of the single person cannot exceed the upper limit, independent working days do not appear, a night shift is arranged before the night shift, the maximum continuous working days cannot be exceeded, and the total number of shifts arranged by each nurse is equal within a period of time.
7. The VBA-based nurse automatic scheduling method as claimed in claim 1, wherein, among all constraints related to consecutive days, an additional form page is used to store the previous scheduling, and the end part of the current scheduling is considered as the beginning part of the new scheduling in the constraint description.
8. The automatic nurse scheduling method implemented based on VBA of claim 1 wherein the VBA code is used to traverse the two-dimensional data of the scheduling table, check the number of times of non-compliance with the constraint condition, and record and return the weighted sum of the associated penalty values.
9. The utility model provides a nurse automatic scheduling system based on VBA realizes which characterized in that includes:
a data acquisition module configured to: acquiring the shift requirements and nurse personnel data of at least one day;
an initial shift schedule module configured to: obtaining an initial scheduling result by adopting a random distribution mode according to the acquired scheduling requirements and nurse personnel data;
an optimal shift module configured to: obtaining an optimal scheduling result according to a preset scheduling constraint, an initial scheduling result and a scheduling optimization algorithm combining variable neighborhood and local search;
the VBA is used for storing a scheduling table, different characters of the array represent different shifts, and the VBA code traverses the two-dimensional array of the scheduling table to convert the characters into the names of the shifts and fill the names of the shifts back to the scheduling interface.
10. An electronic device characterized by comprising the VBA-based nurse automatic scheduling system according to claim 9.
Priority Applications (1)
Application Number | Priority Date | Filing Date | Title |
---|---|---|---|
CN202011118953.XA CN112331315B (en) | 2020-10-19 | 2020-10-19 | Automatic nurse scheduling method and system based on VBA |
Applications Claiming Priority (1)
Application Number | Priority Date | Filing Date | Title |
---|---|---|---|
CN202011118953.XA CN112331315B (en) | 2020-10-19 | 2020-10-19 | Automatic nurse scheduling method and system based on VBA |
Publications (2)
Publication Number | Publication Date |
---|---|
CN112331315A true CN112331315A (en) | 2021-02-05 |
CN112331315B CN112331315B (en) | 2023-05-05 |
Family
ID=74314144
Family Applications (1)
Application Number | Title | Priority Date | Filing Date |
---|---|---|---|
CN202011118953.XA Active CN112331315B (en) | 2020-10-19 | 2020-10-19 | Automatic nurse scheduling method and system based on VBA |
Country Status (1)
Country | Link |
---|---|
CN (1) | CN112331315B (en) |
Cited By (2)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
CN114816172A (en) * | 2022-04-02 | 2022-07-29 | 阿里巴巴(中国)有限公司 | Personnel scheduling interaction processing method and device and electronic equipment |
CN115456566A (en) * | 2022-08-29 | 2022-12-09 | 上海数禾信息科技有限公司 | Scheduling list processing method, system, electronic equipment and storage medium |
Citations (3)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
CN107451393A (en) * | 2017-06-29 | 2017-12-08 | 山东师范大学 | Nurse Scheduling method and apparatus based on random variable neighborhood search algorithm |
CN108901045A (en) * | 2018-04-27 | 2018-11-27 | 长沙学院 | A kind of the adaptive flow dispatching method and system of non-cooperative game |
CN111062462A (en) * | 2019-11-12 | 2020-04-24 | 中山大学 | Local search and global search fusion method and system based on differential evolution algorithm |
-
2020
- 2020-10-19 CN CN202011118953.XA patent/CN112331315B/en active Active
Patent Citations (3)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
CN107451393A (en) * | 2017-06-29 | 2017-12-08 | 山东师范大学 | Nurse Scheduling method and apparatus based on random variable neighborhood search algorithm |
CN108901045A (en) * | 2018-04-27 | 2018-11-27 | 长沙学院 | A kind of the adaptive flow dispatching method and system of non-cooperative game |
CN111062462A (en) * | 2019-11-12 | 2020-04-24 | 中山大学 | Local search and global search fusion method and system based on differential evolution algorithm |
Non-Patent Citations (1)
Title |
---|
汪泰利: "护士排班问题的优化算法研究", 《中国优秀硕士学位论文全文数据库 医药卫生科技辑》 * |
Cited By (3)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
CN114816172A (en) * | 2022-04-02 | 2022-07-29 | 阿里巴巴(中国)有限公司 | Personnel scheduling interaction processing method and device and electronic equipment |
CN114816172B (en) * | 2022-04-02 | 2023-11-17 | 阿里巴巴(中国)有限公司 | Personnel scheduling interaction processing method and device and electronic equipment |
CN115456566A (en) * | 2022-08-29 | 2022-12-09 | 上海数禾信息科技有限公司 | Scheduling list processing method, system, electronic equipment and storage medium |
Also Published As
Publication number | Publication date |
---|---|
CN112331315B (en) | 2023-05-05 |
Similar Documents
Publication | Publication Date | Title |
---|---|---|
CN100375971C (en) | System and method for hierarchical layout specialization | |
US8874456B2 (en) | Resource scheduling and monitoring | |
Axelsson | The organizational pendulum: Healthcare management in Sweden 1865-1998 | |
Smith et al. | A computer-based nurse scheduling system | |
CN112331315A (en) | Nurse automatic scheduling method and system based on VBA | |
CN103559323B (en) | Database implementation method | |
Diaz et al. | RMC: A tool to design WWW applications | |
CN103631596A (en) | Configuration device and configuration method of business object data entry and updating rule | |
Dotoli et al. | Modeling and management of a hospital department via Petri nets | |
CN104714999A (en) | System and method of integrating time-aware data from multiple sources | |
CN111126809A (en) | Guard duty scheduling method and system | |
Sun et al. | Response surface optimisation of surgery start times in a single operating room using designed simulation experiments | |
JPH09251491A (en) | Working information management system | |
Kelly | A matrix editor for a metaCASE environment | |
Young | A conceptual framework for hospital administrative decision systems. | |
Mujica Mota et al. | An improved time line search algorithm for manufacturing decision-making | |
Gnoli et al. | Transforming the Integrative Levels Classification to SKOS: representation of numbers, dates and people via parallel facets | |
DuFour | Three case studies in planning: I. Predicting hospital bed needs. | |
Jordaan et al. | Reflection on mongodb database logical and physical modeling | |
Gorman et al. | A tutorial of integrating duality and branch and bound in earliness–tardiness scheduling with idle insertion time problems | |
KR100700219B1 (en) | Method For Managing A Consultation Schedule Of A Dialysis Patient | |
US20120066248A1 (en) | Dynamic Creation of Materialized Database Views | |
Mchardy et al. | Modelling of intensive care units and operating theatre in public hospitals | |
Chernyshev et al. | THE USE OF DATA WAREHOUSES ON THE EXAMPLE OF THE APPLICATION PROCESSING SYSTEM IN GOVERNMENT AGENCIES | |
Pease et al. | The Hospital Game: Optimizing Surgery Schedules to Save Resources, and to Save Lives |
Legal Events
Date | Code | Title | Description |
---|---|---|---|
PB01 | Publication | ||
PB01 | Publication | ||
SE01 | Entry into force of request for substantive examination | ||
SE01 | Entry into force of request for substantive examination | ||
GR01 | Patent grant | ||
GR01 | Patent grant |