CN112331315A

CN112331315A - Nurse automatic scheduling method and system based on VBA

Info

Publication number: CN112331315A
Application number: CN202011118953.XA
Authority: CN
Inventors: 郑自然
Original assignee: Shandong Normal University
Current assignee: Shandong Normal University
Priority date: 2020-10-19
Filing date: 2020-10-19
Publication date: 2021-02-05
Anticipated expiration: 2040-10-19
Also published as: CN112331315B

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

Nurse automatic scheduling method and system based on VBA

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 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;

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:

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

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;

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:

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;

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;

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:

10. An electronic device characterized by comprising the VBA-based nurse automatic scheduling system according to claim 9.