Weekday
Sunday
Sunday
Monday
Monday
Tuesday
Tuesday
Wednesday
Wednesday
Thursday
Thursday
Friday
Friday
Saturday
Saturday
Sunday
Sunday
Monday
Monday
Tuesday
Tuesday
Wednesday
Wednesday
Thursday
Thursday
Friday
Friday
Saturday
Saturday
Sunday
Sunday
Monday
Monday
Tuesday
Tuesday
Wednesday
Wednesday
Thursday
Thursday
Friday
Friday
Saturday
Saturday
Sunday
Sunday
Day
4/1/2018
4/1/2018
4/2/2018
4/2/2018
4/3/2018
4/3/2018
4/4/2018
4/4/2018
4/5/2018
4/5/2018
4/6/2018
4/6/2018
4/7/2018
4/7/2018
4/8/2018
4/8/2018
4/9/2018
4/9/2018
4/10/2018
4/10/2018
4/11/2018
4/11/2018
4/12/2018
4/12/2018
4/13/2018
4/13/2018
4/14/2018
4/14/2018
4/15/2018
4/15/2018
4/16/2018
4/16/2018
4/17/2018
4/17/2018
4/18/2018
4/18/2018
4/19/2018
4/19/2018
4/20/2018
4/20/2018
4/21/2018
4/21/2018
4/22/2018
4/22/2018
Shift
Day
Grave
Day
Grave
Day
Grave
Day
Grave
Day
Grave
Day
Grave
Day
Grave
Day
Grave
Day
Grave
Day
Grave
Day
Grave
Day
Grave
Day
Grave
Day
Grave
Day
Grave
Day
Grave
Day
Grave
Day
Grave
Day
Grave
Day
Grave
Day
Grave
Day
Grave
PatientStart
24
22
25
31
32
29
32
34
33
28
33
26
27
27
27
29
30
29
30
31
31
30
31
31
31
33
34
33
32
34
34
37
38
30
29
28
29
31
32
31
30
33
33
35
PatientAdmit
2
3
11
2
5
3
7
0
10
5
7
1
3
0
3
1
9
1
4
0
3
1
4
0
6
2
2
0
2
0
10
2
4
0
8
1
11
2
10
1
4
0
2
1
PatientDischarge
4
0
5
1
8
0
5
1
15
0
14
0
3
0
1
0
10
0
3
0
4
0
4
0
4
1
3
1
0
0
7
1
12
1
9
0
9
1
11
2
1
0
0
1
PatientEnd
22
25
31
32
29
32
34
33
28
33
26
27
27
27
29
30
29
30
31
31
30
31
31
31
33
34
33
32
34
34
37
38
30
29
28
29
31
32
31
30
33
33
35
35
Nurses
6
6
8
7
8
7
8
7
7
6
8
6
7
6
6
6
8
7
8
7
8
7
7
6
8
6
7
6
6
6
8
8
6
6
7
7
8
8
8
7
8
8
7
6
Monday
Monday
Tuesday
Tuesday
Wednesday
Wednesday
Thursday
Thursday
Friday
Friday
Saturday
Saturday
Sunday
Sunday
Monday
Monday
4/23/2018
4/23/2018
4/24/2018
4/24/2018
4/25/2018
4/25/2018
4/26/2018
4/26/2018
4/27/2018
4/27/2018
4/28/2018
4/28/2018
4/29/2018
4/29/2018
4/30/2018
4/30/2018
Day
Grave
Day
Grave
Day
Grave
Day
Grave
Day
Grave
Day
Grave
Day
Grave
Day
Grave
35
31
34
32
33
31
35
34
35
32
33
34
34
35
35
29
14
3
4
2
9
4
5
1
13
2
3
0
1
0
12
3
18
0
6
1
11
0
6
0
16
1
2
0
0
0
18
1
31
34
32
33
31
35
34
35
32
33
34
34
35
35
29
31
6
7
8
7
7
8
8
8
6
6
6
8
8
8
6
7
Weekday
Sunday
Sunday
Monday
Monday
Tuesday
Tuesday
Wednesday
Wednesday
Thursday
Thursday
Friday
Friday
Saturday
Saturday
Sunday
Sunday
Monday
Monday
Tuesday
Tuesday
Wednesday
Wednesday
Thursday
Thursday
Friday
Friday
Saturday
Saturday
Sunday
Sunday
Monday
Monday
Tuesday
Tuesday
Wednesday
Wednesday
Thursday
Thursday
Friday
Friday
Saturday
Saturday
Sunday
Sunday
Monday
Monday
Day
4/1/2018
4/1/2018
4/2/2018
4/2/2018
4/3/2018
4/3/2018
4/4/2018
4/4/2018
4/5/2018
4/5/2018
4/6/2018
4/6/2018
4/7/2018
4/7/2018
4/8/2018
4/8/2018
4/9/2018
4/9/2018
4/10/2018
4/10/2018
4/11/2018
4/11/2018
4/12/2018
4/12/2018
4/13/2018
4/13/2018
4/14/2018
4/14/2018
4/15/2018
4/15/2018
4/16/2018
4/16/2018
4/17/2018
4/17/2018
4/18/2018
4/18/2018
4/19/2018
4/19/2018
4/20/2018
4/20/2018
4/21/2018
4/21/2018
4/22/2018
4/22/2018
4/23/2018
4/23/2018
Shift
Day
Grave
Day
Grave
Day
Grave
Day
Grave
Day
Grave
Day
Grave
Day
Grave
Day
Grave
Day
Grave
Day
Grave
Day
Grave
Day
Grave
Day
Grave
Day
Grave
Day
Grave
Day
Grave
Day
Grave
Day
Grave
Day
Grave
Day
Grave
Day
Grave
Day
Grave
Day
Grave
Patient-Start
Patient-Admit
24
22
25
31
32
29
32
34
33
28
33
26
27
27
27
29
30
29
30
31
31
30
31
31
31
33
34
33
32
34
34
37
38
30
29
28
29
31
32
31
30
33
33
35
35
31
Patient-Discharge
2
3
11
2
5
3
7
0
10
5
7
1
3
0
3
1
9
1
4
0
3
1
4
0
6
2
2
0
2
0
10
2
4
0
8
1
11
2
10
1
4
0
2
1
14
3
4
0
5
1
8
0
5
1
15
0
14
0
3
0
1
0
10
0
3
0
4
0
4
0
4
1
3
1
0
0
7
1
12
1
9
0
9
1
11
2
1
0
0
1
18
0
Tuesday
Tuesday
Wednesday
Wednesday
Thursday
Thursday
Friday
Friday
Saturday
Saturday
Sunday
Sunday
Monday
Monday
4/24/2018
4/24/2018
4/25/2018
4/25/2018
4/26/2018
4/26/2018
4/27/2018
4/27/2018
4/28/2018
4/28/2018
4/29/2018
4/29/2018
4/30/2018
4/30/2018
Day
Grave
Day
Grave
Day
Grave
Day
Grave
Day
Grave
Day
Grave
Day
Grave
34
32
33
31
35
34
35
32
33
34
34
35
35
29
4
2
9
4
5
1
13
2
3
0
1
0
12
3
6
1
11
0
6
0
16
1
2
0
0
0
18
1
Patient-End
Nurses
22
25
31
32
29
32
34
33
28
33
26
27
27
27
29
30
29
30
31
31
30
31
31
31
33
34
33
32
34
34
37
38
30
29
28
29
31
32
31
30
33
33
35
35
31
34
6
6
8
7
8
7
8
7
7
6
8
6
7
6
6
6
8
7
8
7
8
7
7
6
8
6
7
6
6
6
8
8
6
6
7
7
8
8
8
7
8
8
7
6
6
7
32
33
31
35
34
35
32
33
34
34
35
35
29
31
8
7
7
8
8
8
6
6
6
8
8
8
6
7
Alesha Rogers is the Nurse Manager for the Neuro Acute Care Division at Suburbia Regional
Hospital. The 12-hour nursing shifts are divided into day and graveyard. Alesha collects data
on the number of patients that start each shift, the number of patients admitted, the number of
patients discharged, and the number of patients at the end of each shift. She also notes how many
nurses worked each shift. Because of your expertise in using Excel to analyze data, she has pro-
vided you a basic spreadsheet and asked you to help consolidate the data for her to analyze. She
is interested in the number of nurses per daily shift (such as all Sunday graveyard shifts) for the
month of April. Refer to Figure 5.47 as you complete this exercise.
month of April. Refer to Fig
PivotTable Fields
B
Choose fields to add to report:
1
H
G
Search
Patient
Erd
Nurses
22
6
6
Average of Patient-Start Average of Patient-End
30.5
31.4
31.6
32.2
32
30.875
31
31.375
31.5
31.875
31.625
30.75
31.375
31.625
31.35
31.46666667
25
27
0 0 0 0 0
2
3 Row Labels
4 Sunday
5 Monday
6 Tuesday
7 Wednesday
8 Thursday
9 Friday
10 Saturday
11 Grand Total
12
13
Weekday
Day
Shift
Patient-Start
Patient-Admit
Patient-Discharge
Patient-End
Nurses
Start Patients per Nurse
28
30
30
32
6
7
32
6
33
8
Average Number of Patients by Weekday
4
4
MORE TABLES...
33
8
6.5
15
16
29.2
39
Saturday
Drag fields between areas below
8
7
40
7
Y FILTERS
I COLUMNS
18
Shift
1 Wack
2 Sunday 4/1/2018 Day
3 Sunday
4/1/2018 Grave
4 Sunday 4/8/2018 Day
5 Sunday
4/8/2018 Grave
6 Sunday 4/15/2018 Day
7 Sunday 4/15/2018 Grave
8 Sunday 4/22/2018 Day
9 Sunday 4/22/2018 Grave
10 Sunday 4/29/2018 Day
11 Sunday 4/29/2018 Grave
12 Sunday Average
13 Monday 4/2/2018 Day
14 Monday 4/2/2018 Grave
15 Monday 4/9/2018 Day
16 Monday 4/9/2018 Grave
17 Monday
4/16/2018 Day
18 Monday
4/16/2018 Grave
19 Monday 4/23/2018 Day
20 Monday 4/23/2018 Grave
21 Monday 4/30/2018 Day
22 Monday 4/30/2018 Grave
23 Monday Average
24 Tuesday 4/3/2018 Day
25 Tuesday 4/3/2018 Grave
26 Tuesday 4/10/2018 Day
April Data
PivotTable (2)
39
8
Thursday
2 Values
40
7
Average of Patient-End
8
20 Wednesday
21
Tuesday
22
43
44
40
Average of Patient-Start
8
2016. Windows 10. Microsoft Corporation
6
23
Monday
ROWS
VALUES
6
24
25
Sunday
Weekday
43
37
39
40.4
36
7
Average of Pa.
Average of P...
29.5
30
30.5
32
32.5
7.2
B
26
27
28
39
7
29
.
8
1
Pivo ...
Defer Layout Update
April Data
Pivot Table (2)
06
Ready
E
URE 5.47 Suburbia Regional Hospital
.
.
a. Open e05p2 Patients and save it as e05p2Patients LastFirst.
b. Ensure that the April Data worksheet is active. Complete the following steps to sort the data:
Click the Data tab and click Sort in the Sort & Filter group.
Click the Sort by arrow and select Weekday.
Click the Order arrow and select Custom List to open the Custom Lists dialog box.
Select
Sunday, Monday, Tuesday in the Custom lists section and click OK. Click OK in the Sort
dialog box.
c. Click Subtotal in the Outline group. Complete the following steps in the Subtotal dialog box:
Click the At each change in arrow and select Weekday.
Click the Use function arrow and select Average.
Click the Patient-End check box to select it, keep the Nurses check box selected, and then
click OK.
d. Click the Group arrow in the Outline group on the Data tab, select Auto Outline, and then
click OK. Click the collapse button above column G to collapse the outline.
e. Click the April Table sheet tab, click the Insert tab, click Recommended PivotTables in
the Tables group, click the Sum of Patient-Start by Weekday and Shift thumbnail, and
then click OK.
f. Complete the following steps to modify the Pivot Table:
Click the Day check box in the PivotTable Fields List to add that field to the ROWS area.
Move the Shift field from the COLUMNS area to the FILTERS area in the PivotTable Fields
.
.
.
.
.
List.
Click the Shift arrow in cell B1, select Day, and click OK to filter the records to show
totals for only the day shifts.
Click the PivotTable Name box in the PivotTable group on the Analyze tab, type Patients-
Nurses, and then press Enter.
Rename Sheetl as PivotTable.
g. Complete the following steps to create a calculated field:
Click the Analyze tab, click Fields, Items, & Sets in the Calculations group, and then
select Calculated field.
Type Start Patients per Nurse in the Name box in the Insert Calculated field dialog box.
Double-click Patient-Start in the Fields list, type /, and double-click Nurses in the Fields
list. Click OK.
Click cell C3, click Field Settings in the Active Field group, click Number Format, click
Number in the Category list, click the Decimal places setting to 0, click OK in the Format
Cells dialog box, and then click OK in the Value Field Settings dialog box.
h. Click Insert Slicer in the Filter group, click the Weekday check box in the Insert Slicers
dialog box, and then click OK.
i. Complete the following steps to customize the filter:
• Move the filter to start in cell F3.
Click More in the Slicer Styles group on the Options tab and click Slicer Style Dark 5.
Change the Width to 1.5 in the Buttons group on the Options tab.
Press Ctrl and click Sunday and Saturday in the Weekday slicer to filter out these
two days.
j. Copy the PivotTable sheet tab and place the PivotTable (2) sheet tab between the April Data
and Pivot Table sheet tabs. Ensure that the Pivot Table (2) sheet is active and complete the fol-
lowing steps:
• Click the Shift check box in the PivotTable Fields List to remove it from the FILTERS area.
• Click the Day check box in the PivotTable Fields List to remove it from the ROWS area.
Click Multi-Select in the Weekday slicer window, click Sunday, and then click Saturday
.
so that all seven days will display.
.
k. Click in the Pivot Table, click the Analyze tab, click PivotChart in the Tools group, and then
complete the following steps:
Click Bar in the Insert Chart dialog box and click OK.
Click the Sum of Start Patients per Nurse arrow in the VALUES area of the Pivot Table
Fields List and select Remove Field.
Click the Sum of Patients Start arrow in the VALUES area of the PivotTable Fields List,
click Value Field Settings, select Average, click Number Format, select Number in the
Category list, click OK in the Format Cells dialog box, and then click OK in the Value Field
Settings dialog box,
Click the Sum of Patients End arrow in the VALUES area of the Pivot Table Fields List,
click Value Field Settings, select Average, click Number Format, select Number in the
Category list, click OK in the Format Cells dialog box, and then click OK in the Value Field
Settings dialog box.
Click the Analyze tab, click the Field Buttons arrow in the Show/Hide group, and then
select Hide All.
Click the Chart Title placeholder, type Average Number of Patients by Weekday.
and then press Enter.
Move the chart so that the top-left corner starts in cell A13.
1. Create a footer with your name on the left side, the sheet name code in the center, and the file
name code on the right side on each worksheet.
m. Save and close the file. Based on your instructor's directions, submit e05p2Patients_LastFirst.
.
Purchase answer to see full
attachment