Excel Periodical Report: 6 Kauj Ruam

2024 Tus sau: John Day | [email protected]. Kawg hloov kho: 2024-01-30 09:25

Nov yog kuv cov lus qhia rau kev siv sijhawm qhia nyob rau hauv Excel 2010. Hauv cov vis dis aus qhia hauv qab no, daim ntawv tshaj tawm no qhia peb txog kev siv hluav taws xob tshwj xeeb, dej, oxygen, nitrogen ib tuj ntawm cov khoom tiav, raws li txhua lub hlis, txhua hli, peb lub hlis twg, txhua xyoo. Qhov tseeb tag nrho cov ntaub ntawv tsim khoom yuav raug kaw thiab nkag mus txhua hnub los ntawm cov tswv lag luam. Txhawm rau nkag siab yooj yim, hauv kuv qhov kev qhia, nws tau simulated hauv 2 xyoos (2019 & 2020).

Kauj ruam 1: Tsim Excel Table

Tsim ib lub rooj Excel hu ua "CONSUMPTION", nrog nws kab ntawv raws li hauv qab no:

Hnub tawm tswv yim los ntawm 1/1/2019 txog 2020-31-12

Lub lis piam = WEEKNUM ([@Hnub])

Lub hli = TEXT ([@Hnub], "mmm")

Quarter = "Q." & ROUNDUP (HLI ([@Hnub Tim))/3, 0)

Xyoo = YEAR ([@Hnub])

Nkag mus siv cov ntaub ntawv ntawm kab:

Hluav taws xob (kW)

Dej (m3)

Cov pa (m3)

Nitrogen (m3)

Khoom (tuj)

Rau kev sim, Kuv siv RANDBETWEEN (min, max) ua haujlwm los tsim qhov sib piv ntawm cov lej (min, max). Qhov tseeb, peb yuav sau & nkag cov ntaub ntawv no txhua hnub thaum tsim khoom.

Kauj Ruam 2: Tsim Pivot Table Nrog Pivot Chart

Xaiv ib qho ntawm cov cell hauv koj cov ntaub ntawv tshiab thiab mus rau Insert Tab → Charts → Pivot Chart

Sau lub npe lub npe "CONSUMPTION" ntawm Table/Range Tab

Kauj Ruam 3: Tsim Ib Leeg Teb

Txhawm rau suav qhov kev siv tshwj xeeb, mus rau PivotTable Cov Cuab Yeej → Kev Xaiv → Cov Teb, Cov Khoom, & Poob → Xeem Teb: Nkag Mus Ntawm Lub Npe & Cov Qauv Tabs raws li hauv qab no:

Hluav taws xob (kW/ton) = 'Hluav taws xob (kW)'/'Khoom (tuj)'

Dej (m3/tuj) = 'Dej (m3)'/'Khoom (tuj)'

Oxygen (m3/ton) = 'Oxygen (m3)'/'Khoom (tuj)'

Nitrogen (m3/ton) = 'Nitrogen (m3)'/'Khoom (tuj)'

Kauj Ruam 4: Xaiv Cov Teb Ntxiv rau Qhia

Hauv PivotTable Field List, peb yuav xaiv cov teb ntxiv rau cov ntawv ceeb toom thiab muab lawv thaj chaw raug:

1. Axis Teb:

Xyoo

Peb Hlis

Hli

Lub lim tiam

2. Qhov muaj nqis:

Qhov nruab nrab ntawm hluav taws xob (kW/tuj)

Qhov nruab nrab Dej (m3/tuj)

Qhov nruab nrab ntawm cov pa (m3/tuj)

Qhov nruab nrab ntawm Nitrogen (m3/tuj)

Kauj Ruam 5: Ntxig Slicer

Cov txiaj ntsig muaj txiaj ntsig no zoo heev rau saib thiab sib piv sib txawv ua ke ntawm koj cov ntaub ntawv nrog nyem yooj yim.

Txhawm rau ntxiv Slicers hauv Excel 2010:

Mus rau PivotTable Cov Cuab Yeej → Xaiv → Ntxig Slicer

Hauv qhov Insert Slicers dialog box, nyem rau ntawm lub npov: Xyoo, Peb Hlis, Ib Hlis, Ib Hlis

Peb muaj tag nrho 4 lub tshuab txiav thiab hauv txhua lub qhov rai txiav, peb tuaj yeem nyem rau ntawm ib yam khoom uas koj xav lim

Kauj Ruam 6: Daim Ntawv Tshaj Tawm zaum kawg

Thaum kawg koj tuaj yeem xa cov ntawv tshaj tawm txhua lub hlis / hli / peb lub hlis / xyoo rau koj tus thawj tswj hwm, thiab koj tseem tuaj yeem sib piv kev noj haus ntawm lub lis piam ntawm lub hlis, hli lossis peb lub hlis ntawm xyoo lossis luam cov kab kos no rau Microsoft PowerPoint rau kev nthuav qhia.

Ib Hlis/ 2019 - Tshaj Qhia

Sib piv Peb Hlis 1 & 2/2019 vs Quarter 1 & 2/2020 Report (tuav CTRL qhov tseem ceeb thiab nyem ntau yam khoom)