Introduction: Excel Intermittent Paper

Hither are my tips for periodic consumption reports in Excel 2010. In the tutorial video below, this account tell us about specialised consumption of electrical energy, piss, oxygen, N per ton of finished products, according to weekly, monthly, quarterly, yearly. Actually every production data will be recorded and entered daily by operators. For savvy easily, in my tutorial, it is imitation in 2 eld (2019 & 2022).

Step 1: Creating an Excel Table

Creating an Excel table named "Pulmonary tuberculosis", with its columns every bit following:

  • Input Date from 1/1/2019 to 12/31/2020.
  • Calendar week = WEEKNUM([@Date])
  • Month = Textual matter([@Date],"mmm")
  • Quarter = "Q." & ROUNDUP(MONTH([@Date])/3,0)
  • Class = Yr([@Date])

Entering intake data at columns:

  • Electrical energy (kW)
  • Water (m3)
  • O (m3)
  • Atomic number 7 (m3)
  • Product (gross ton)

For pretence, I utilized a RANDBETWEEN(min, max) function to generate a random integer between given numbers (Taiwanese, max). In realism, we wish disc & input these data daily during yield.

Step 2: Creating Pivot Table With Pivot Graph

  • Select some of the cells in your original data sheet and attend Tuck Tab → Charts → Pivot man Graph.
  • Accede table name "White plague" at Table/Range Tab.

Step 3: ​Create a Calculated Field

To calculating specific phthisis, attend PivotTable Tools → Options → Fields, Items, & Sets → Calculated Field: Move in at Name & Formula Tabs as follows:

  • Electricity (kW/ton)='Electricity (kW)'/'Product (ton)'
  • Water (M3/ton) ='Water (m3)'/'Production (long ton)'
  • Oxygen (M3/ton) ='Oxygen (m3)'/'Product (ton)'
  • Nitrogen (m3/ton) ='Atomic number 7 (m3)'/'Mathematical product (short ton)'

Whole step 4: Select Fields to Add to Story

In PivotTable Field List, we will choose fields to add to reports and put them counterbalance areas:

1. Axis Fields:

  • Year
  • One-fourth
  • Month
  • Week

2. Values:

  • Average of Electrical energy (kilowatt/ton)
  • Average of Water (m3/gross ton)
  • Average of Oxygen (m3/ton)
  • Average of N (M3/ton)

Step 5: Insert Slicer

This effective feature is excellent for viewing and comparison varied combinations of your information with a simple get through.

To bring Slicers in Excel 2010:

  • Go to PivotTable Tools → Options → Insert Slicer.
  • In the Sneak in Slicers dialog loge, click the check boxes: Class, Quarter, Calendar month, Week.
  • We had totally 4 slicers and in every slicer window, we can click on any token which you want to filter.

Step 6: Net Report

Finally you commode submit each week / monthly / quarterly / yearly reports to your managers, and you can also compare consumption between weeks of the month, calendar month Oregon draw of the twelvemonth or copy these charts to Microsoft PowerPoint for presentation.

  • Jan/ 2022 - Account

  • Comparing Quarter 1 & 2/ 2022 vs Quarter 1 & 2/ 2022 Report (appreciation CTRL identify and click multiple items)

  • Sample Excel file for this instructable: Download

Be the First to Apportion

Recommendations