Create new columns to integrate daily weather conditions into the raw accident dataset, and understand the importance of joining data to enable deeper analytics. Understand how to combine Excel’s VLOOKUP and MATCH functions to dynamically retrieve data based on column headers, and when to use IFERROR to categorize missing values.
- [Voiceover] VLOOKUP is one of the most commonly used…Excel functions for joining data,…but what most people don't know is that you can include…a nested MATCH function to automatically…pull in new fields.…I'll show you how to join multiple columns of data…using one single formula.…So I'll open my 04_02 file in Excel,…and what I wanna do here is combine the fields…in columns N through P of my Raw Weather Data tab…into the raw data in my Accident Map 2010 through 2013 tab.…
So what I'm gonna do to start is select my column headers…in N1 through P1, Control + C to copy,…and then back in my Accident Map tab,…I'm gonna paste these headers in columns AE through AG,…and I'll just drag AD over so that there's no overlap.…And now I'll use VLOOKUP functions here…where my key will be the Key value in column A.…So you can see that between the Accident Map tab…and my Raw Weather Data tab, column A is that consistent key…that I'll use to join the data together.…
So back in my Accident Map tab in cell AE2,…I'm gonna start writing my VLOOKUP function =VLOOKUP.…
Using conditional statements and text functions like LEFT, MID, RIGHT, and CONCATENATE, you'll learn to standardize data sets from the NCDC and create new dimensions. Then find out how to use VLOOKUP to join the data, and use COUNTIF statements to analyze trends. Once the historical data is in place, Chris shows how to use Excel's powerful WEBSERVICE and FILTERXML functions to tap directly into Weather Underground's API, which provides real-time weather information for any location and allows you to estimate accident rates based on current conditions.
Last but not least, you'll learn how to build custom charts to show the expected accident risk based on real-time inputs, and walk through some additional opportunities for deeper analysis.
- Standardizing data
- Creating new data dimensions fields
- Joining data
- Analyzing trends in the data with COUNTIF
- Creating an API feed with WEBSERVICE
- Extracting components with FILTERXML
- Visualizing the findings in a custom combo chart
- Applying your new skill set