Now have a VBA final examination, the various learned VB failed to achieve a great god, genuflect is begged on BBS for great god help rookie solutions, is to use the Excel VBA, make up a operating room scheduling problem, the main investigation is Greedy heuristic (Greedy heuristic), attach a topic, can help the great god of details please contact me, my contact way: QQ: 243676529 E-mail: [email protected] is really thank you very much!

With two surgeon in the hospital, they operate in separate operating room, surgeons hope to work 6 hours a day on average, if more than more than 7.5 hours a day they will be very not happy, this means that the occasional surgery more than 7.5 hours is acceptable, but the schedule should be designed to let this happen to minimize,
For each in the list of the patient, the managers should know which one of the two doctors will undergo surgery, and calculated based on the hospital records such the expected (average) of operation time and operation standard deviation
To find a feasible (but is likely to be sub-optimal) arranged to this problem is to use the greedy heuristic (greedy heuristic), in your homework, you can use the following greedy heuristic to develop code:
1. Select a surgeon, S, whose schedule has not had been planned.
Select a calendar has no plans to surgeons, S
2. Create an ordered list, L, of the patients who require treatment by that surgeon, in item first-just order of expected operation duration.
Make a order about need the doctor to treat patients table L, with the expected operating time of descending order
3. Consider the patient, P, at the top of the L, awaiting operation o. P to Add the earliest acceptable daily schedule for the surgeon s. A daily schedule is acceptable for patient P if adding operation will not O:
Considering the first patient list L P, O waiting for surgery, increase P the surgeon S earliest acceptable schedule every day, a schedule is acceptable, if P O would not happen if surgery for the patients of the following:
(a) take the total expected duration of the operations for that day over 6 hours, or estimated total operation time more than 6 hours in the day or
(b) take the total expected duration of the operations on day plus the standard deviation for the total duration of operations on that day over 7.5 hours. The total expected time surgery time and standard deviation of more than 7.5 hours
If no to acceptable daily schedule exists then the create a new daily schedule at the end of the current schedule for surgeon S to accommodate patient P (and implicitly operation O).
If there is no such an acceptable schedule, in order to adapt to the patients with P, create a new schedule at the end of the current surgeon S schedule, (O) and implicit surgery
4. Remove the patient from l. P
Remove the patient from L P
5. If L is not empty, then return to step 3.
If L is not empty, then back to the third step
6. If there are surgeons whose schedules have not had planned then return to step 1.
If any surgeon's calendar is not arrange, then return to step 1
In order to make the heuristic deterministic (i.e. to guarantee the same result each time it is run on the same Problem instance), we must specify how ties are broken in step 2. In this coursework, ties should be broken by selecting the patient with the highest standard deviation of operation duration first. If two patients have the same expected operation duration and standard deviation of operation duration, then the patient with the smallest index (first on the original list of patients) should be chosen first.
In order to determine the heuristic (i.e., running on the same problem instance to ensure each have the same result) we must specify how to break this ties in step 2, in this homework, ties should be first through the choice of patients operation time of the highest standard deviation is broken, if two patients have the same expected standard deviation were the same in operation time and operation time, the youngest of the index patient (the first patient in the original list) should be the first choice,

Example 1: if patients 5 and 7 have the same expected operation duration, with standard deviations of 1.5 and 1.7, respectively, then the patient 7 should be listed first.
Case 1: if the patients 5 and 7 of the surgery is expected the same time, their standard deviation was 1.5 and 1.7, respectively, 7 patients should be in the list of the first

Example 2: if 4 and 8 patients have the same expected operation durations and standard deviations then patient 4 should be listed first.
Example 2: if the patient 4 and 8 patients had the same expected operating time and standard deviation, so the patient should be among the list in the first 4

Refer to the technical articles (I'll give you another word) refer to the details, about how combining operation time and the combination of the standard deviation is expected to calculate


Needs in the EXCEL VBA for greedy heuristic, your program must have an independent users meet, so that users can
1. Read the data from the text of a CSV file
2. Identify any problem data
3. Run the greedy heuristic
4. The output