# Dph second Edition Day 2 Exercises First Half

Yüklə 12,27 Kb.
 tarix 18.08.2018 ölçüsü 12,27 Kb.

## First Half (HTN Master List)

### IF ISNA Function for Master Visit List VLOOKUP

1. If Food Pharmacy VLOOKUP results in NA then output /No. If it results in a value, then output Yes.

### Conditional Formatting Master Visit List

1. Select Nurse/Pharmacist column

2. Apply Conditional Formatting → Text That Contains: “Nurse”

3. Apply Conditional Formatting → Text That Contains: “Pharmacist”

4. Select Food Pharmacy column

5. Apply Conditional Formatting → Text That Contains: “Yes”

### Review Benefits of PivotTables Based on Tables

1. Add/Update Records in Master List forNurse Visit Pivot Table

2. Refresh Data

### Building Out Nurse Visit Eligibility PivotTable

3. Move EncounterFacility to Rows Quadrant

4. Explore PivotTable Tools → Design tab

• Move, Show, Hide Subtotals/Grand Totals

• Banded Rows

• Table Styles

• Report Layouts

### PivotTable Slicers & Drill Down Reports

1. Insert Slicers for Provider and EncounterFacility

2. Filter for patients with Provider A encountered in Primary Care

3. Filter for patients with Provider A, J, and K encountered in Primary Care

4. Double Click on Patient Count for Provider J to generate drill down report.

5. Review Move & Copy to move report into new workbook.

### Grouping PivotTable Data

1. Clear Nurse Visit PivotTable

• Rows: Age

• Values: ID

1. Summarize Values for ID as Count

2. Click and drag to select ages between:

• 18-24

• 25-34

• 35-44

• 45-54

• 55-64

• 65+

1. Right click on each selection field and choose “Group”

2. Remove Age from Values Quadrant

3. Rename Age2 as Age Brackets

### Show Values as % in PivotTables

1. Right click on ID values in PivotTable

2. Hover Over “Show Values As”

3. Select “% of Grand Total”

————————

## Second Half (Briefly in HTN, remainder in Cancer Screening Master List)

### Building Bar Charts

1. Using Nurse Visit PivotTable with the following fields:

• Rows: Provider

• Values: ID (Summarize as Count)

1. Insert PivotChart → Bar Chart

3. Remove EncounterFacility from Columns Quadrant

4. Remove Provider from Rows Quadrant

### Formatting & Managing Charts

1. Change Chart Type to Pie Chart

2. Review Chart Elements

• Chart Title

• Data Labels

• Legend/Data Table

1. Review Quick Layout/Chart Colors

• Save Chart Template for Future Use

• Change Color Options w/ Theme Color palate

### Building Cancer Screening Master List

1. Create the following columns in Cancer Screening Master List:

• Breast Screening

• Colorectal Screening

• Cervical Screening

1. VLOOKUP (Using MRN as Lookup_Value) to extract Screening Status from the following worksheets:

• Breast Cancer Screening

• Cervical Cancer Screening

• Colorectal Cancer Screening

### Using IFERROR to Clean Up VLOOKUP

1. Build IFERROR function around VLOOKUPs in Master List

2. If there is an N/A error, output “No Data Available”

### IF & OR Function to Check IF Due for Any Cancer Screening

1. Create New Column in Cancer Screening Master List called Any Cancer Screening Due

2. Build IF & OR Nested Function to check if any of the following columns say Due:

• Cervical Screening

• Breast Screening

• Colorectal Screening

1. IF any say Due, say “Due”

2. IF none say Due say “Not Due”

### PivotTable Analysis of Cancer Screening Master List

1. Build PivotTable with the following fields:

• Rows: RaceDescription

• Values: MRN (Summarize Values as Count)

1. Review Drill Down Reports (Double Click on Values in PivotTable)

2. Insert Slicers for the following fields:

• Cervical Screening

• Breast Screening

• Colorectal Screening

### Review Text to Columns Date Conversion

1. Apply Text to Columns on NextScheduledApptDate

### CONCATENATE Full Name

1. Insert New Column after PCPFirstName

2. Build CONCATENATE Function with PCPFirstName, “ “, and PCPLastName

Yüklə 12,27 Kb.

Dostları ilə paylaş:

Verilənlər bazası müəlliflik hüququ ilə müdafiə olunur ©muhaz.org 2020
rəhbərliyinə müraciət