Loading in 2 Seconds...
Loading in 2 Seconds...
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.
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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.
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 Ezequiel Velez, EPA Region 4 Toxics Release Inventory (TRI) Coordinator Tel. (404) 562-9191 firstname.lastname@example.org Douglas Chatham, SEE Enrollee EPA Region 4 TRI Program Assistant Tel. (404) 562-9113 email@example.com U.S. EPA Region 4 61 Forsyth Street, S.W. Atlanta, GA 30303
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.
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.
Producing Charts from TRI-Explorer Data • Export Data from TRI-Explorer to Excel Let’s Do This a Little Faster. TRIMacro.xls
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).
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
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.
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.
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
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