Assignment 3 Overview As with Microsoft Excel, knowledge and use of relational databases is an important skill for a data analyst. Often large amounts of data are stored in relational databases and a

Sub DataPreparation() ' ' DataPreparation Macro ' ' Keyboard Shortcut: Ctrl+Shift+D ' Sheets("Sheet2").Select Sheets("Sheet2").Name = "Data" Sheets("Sheet1").Select Sheets("Sheet1").Name = "Analysis" Range("A1").Select ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ "Data!R1C10:R1048576C11", Version:=6).CreatePivotTable TableDestination:= _ "Analysis!R1C1", TableName:="PivotTable1", DefaultVersion:=6 Sheets("Analysis").Select Cells(1, 1).Select With ActiveSheet.PivotTables("PivotTable1") .ColumnGrand = True .HasAutoFormat = True .DisplayErrorString = False .DisplayNullString = True .EnableDrilldown = True .ErrorString = "" .MergeLabels = False .NullString = "" .PageFieldOrder = 2 .PageFieldWrapCount = 0 .PreserveFormatting = True .RowGrand = True .SaveData = True .PrintTitles = False .RepeatItemsOnEachPrintedPage = True .TotalsAnnotation = False .CompactRowIndent = 1 .InGridDropZones = False .DisplayFieldCaptions = True .DisplayMemberPropertyTooltips = False .DisplayContextTooltips = True .ShowDrillIndicators = True .PrintDrillIndicators = False .AllowMultipleFilters = False .SortUsingCustomLists = True .FieldListSortAscending = False .ShowValuesRow = False .CalculatedMembersInFilters = False .RowAxisLayout xlCompactRow End With With ActiveSheet.PivotTables("PivotTable1").PivotCache .RefreshOnFileOpen = False .MissingItemsLimit = xlMissingItemsDefault End With ActiveSheet.PivotTables("PivotTable1").RepeatAllLabels xlRepeatLabels With ActiveSheet.PivotTables("PivotTable1").PivotFields("ownership_type") .Orientation = xlRowField .Position = 1 End With ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _ "PivotTable1").PivotFields("number_of_certified_beds"), _ "Sum of number_of_certified_beds", xlSum With ActiveSheet.PivotTables("PivotTable1").PivotFields( _ "Sum of number_of_certified_beds") .Caption = "Average of number_of_certified_beds" .Function = xlAverage End With Range("A1").Select ActiveSheet.PivotTables("PivotTable1").CompactLayoutRowHeader = _ "Ownership Type" Range("B1").Select ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _ "Average of number_of_certified_beds").Caption = "Average Beds" Columns("B:B").Select Selection.NumberFormat = "0.00" Columns("B:B").EntireColumn.AutoFit Range("A16").Select ActiveSheet.PivotTables("PivotTable1").GrandTotalName = "Overal Average" Range("A1:B14").Select ActiveSheet.Shapes.AddChart2(216, xlBarClustered).Select ActiveChart.SetSourceData Source:=Range("Analysis!$A$1:$B$16") ActiveSheet.Shapes("Chart 1").Height = 396 ActiveSheet.Shapes("Chart 1").Width = 576 ActiveChart.ClearToMatchStyle ActiveChart.ChartStyle = 341 ActiveChart.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis) ActiveChart.ChartTitle.Select Selection.Caption = "Average Beds by Ownership Type" ActiveChart.SetElement (msoElementLegendNone) End Sub