slide1 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Douglas M. Chatham, SEE Enrollee Air Toxics Assessment & Implementation Section U.S. EPA Region 4 PowerPoint Presentation
Download Presentation
Douglas M. Chatham, SEE Enrollee Air Toxics Assessment & Implementation Section U.S. EPA Region 4

Loading in 2 Seconds...

play fullscreen
1 / 14

Douglas M. Chatham, SEE Enrollee Air Toxics Assessment & Implementation Section U.S. EPA Region 4 - PowerPoint PPT Presentation


  • 122 Views
  • Uploaded on

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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

Douglas M. Chatham, SEE Enrollee Air Toxics Assessment & Implementation Section U.S. EPA Region 4


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - 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