1 / 14

The Use of Excel Pivot Tables & Charts to Process TRI and RSEI Data in Support of Data Quality, Enforcement, and Pol

The Use of Excel Pivot Tables & Charts to Process TRI and RSEI Data in Support of Data Quality, Enforcement, and Pollution Prevention Activities. Douglas M. Chatham, SEE Enrollee Air Toxics Assessment & Implementation Section U.S. EPA Region 4 Atlanta, Georgia. CONTACT INFORMATION.

elsa
Download Presentation

The Use of Excel Pivot Tables & Charts to Process TRI and RSEI Data in Support of Data Quality, Enforcement, and Pol

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. The Use of Excel Pivot Tables & Charts to Process TRI and RSEI Data in Support of Data Quality, Enforcement, and Pollution Prevention Activities Douglas M. Chatham, SEE Enrollee Air Toxics Assessment & Implementation Section U.S. EPA Region 4 Atlanta, Georgia

  2. CONTACT INFORMATION Ezequiel Velez, EPA Region 4 Toxics Release Inventory (TRI) Coordinator Tel. (404) 562-9191 velez.ezequiel@epa.gov Douglas Chatham, SEE Enrollee EPA Region 4 TRI Program Assistant Tel. (404) 562-9113 chatham.douglas@epa.gov U.S. EPA Region 4 61 Forsyth Street, S.W. Atlanta, GA 30303

  3. Producing Charts from TRI-Explorer Data • Export Data from TRI-Explorer to Excel ALsc06-releases.xls • Export TRI-Explorer Data to CSV files. ST88-06-releases.csv, ST98-06-releases.csv, STch06-releases.csv, STsc06-releases.csv, and STfa06-releases.csv • Enter formulas for Total Air, Water, Land, and Underground Injection. • Copy the Results and Paste (to the right) the Values into a blank area. Copy the ID column and the Total Air and paste into another blank area. • Sort by Total TRI Releases or by Total Air in Descending order. • Insert bar charts from these sorted results.

  4. Producing Charts from TRI-Explorer Data • Export Data from TRI-Explorer to Excel • Copy Data from the State File to the Region 4 File. R4sc06Temp.xls • The Chemical and Industry Data must be aligned across the state entries to be able to sum them.

  5. Producing Charts from TRI-Explorer Data • Export Data from TRI-Explorer to Excel Let’s Do This a Little Faster. TRIMacro.xls

  6. Producing Charts from RSEI • Export Data from RSEI to Access • Run RSEI with No Elements Selected • Set up a Table with Chemical.CAS Number, Facility.Facility ID, Submission.Year, and Release.Media Code Fields selected. • Start a new Access database with an appropriate name (US-96-05.mdb). • Import the Paradox tables Chemical.db, Facility.db, Media.db and the table produced in step 2 into the new database. • Establish Relationship Links between the Tables (CAS Number, Facility ID, and Media).

  7. Producing Charts from RSEI • Set Up Access Queries and Export to Excel. • The Fields I include are: • CAS Number Chemical • Year • Facility ID Facility Name • FIPS County • State ZipCode • Region • SIC Code 2Digit SICName • Media MediaType • TRI Pounds Hazard Full Model

  8. Producing Charts from RSEI • Export Query to Excel • Close the Query in Access • Select Export from the File Menu • Specify How and Where the File Should be Saved (As Excel 97- 2003) • Close Access and Open the Excel File • The Next Slide shows a screen shot of the resulting Excel sheet.

  9. Producing Charts from RSEI • Set Up Excel Pivot Tables and Charts • Open the Excel Spread-Sheet. • qryAL98-05Start.xls • Select Cell A1 • Select “Data/Pivot Table and Pivot Chart Report” • Select “Pivot Chart Report” • Move Fields from the Field List to the Appropriate Areas of the Pivot Table.

  10. Producing Charts from RSEI • Save Pivot Table Data & Create Excel Charts • Insert Pounds and Risk Data Sheets • Copy Pivot Table Data and Paste to the Appropriate Data Sheet • Create a Bar Chart from the Data Sheet • Format the Chart • qryAL_98-05Pivot.xls

  11. Producing Charts from RSEI • Use Visual Basic Program to Generate Charts from the Data Sheets • Let’s Generate some charts Using Visual Basic • RSEIMacro.xls • What’s Next?? • I Plan to Program the Transfer of Data from Pivot Tables to the Data Tables • http://www.epa.gov/region4/air/airtoxic/RSEI_Charts.htm

More Related