Dull 1. Fformiwlâu
Gadewch i ni ddechrau, ar gyfer cynhesu, gyda'r opsiwn symlaf - fformiwlâu. Os oes gennym dabl bach wedi'i ddidoli yn ôl dyddiad fel mewnbwn, yna i gyfrifo'r cyfanswm rhedeg mewn colofn ar wahân, mae angen fformiwla elfennol arnom:
Y brif nodwedd yma yw gosod yr amrediad y tu mewn i swyddogaeth SUM yn anodd - mae'r cyfeiriad at ddechrau'r ystod yn cael ei wneud yn absoliwt (gydag arwyddion doler), ac i'r diwedd - cymharol (heb ddoleri). Yn unol â hynny, wrth gopïo'r fformiwla i lawr i'r golofn gyfan, rydym yn cael ystod ehangu, y byddwn yn cyfrifo'r swm ohono.
Mae anfanteision y dull hwn yn amlwg:
- Rhaid trefnu'r tabl yn ôl dyddiad.
- Wrth ychwanegu rhesi newydd gyda data, bydd yn rhaid ymestyn y fformiwla â llaw.
Dull 2. Tabl colyn
Mae'r dull hwn ychydig yn fwy cymhleth, ond yn llawer mwy dymunol. Ac i waethygu, gadewch i ni ystyried problem fwy difrifol - tabl o 2000 o resi o ddata, lle nad oes didoli yn ôl y golofn dyddiad, ond mae yna ailadroddiadau (hy gallwn werthu sawl gwaith ar yr un diwrnod):
Rydym yn trosi ein tabl gwreiddiol yn llwybr byr bysellfwrdd “smart” (deinamig). Ctrl+T neu dîm Cartref - Fformat fel bwrdd (Cartref - Fformat fel Tabl), ac yna rydym yn adeiladu bwrdd colyn arno gyda'r gorchymyn Mewnosod – PivotTable (Mewnosod — Tabl Colyn). Rydyn ni'n rhoi'r dyddiad yn yr ardal rhesi yn y crynodeb, a nifer y nwyddau a werthwyd yn yr ardal werthoedd:
Sylwch, os nad oes gennych fersiwn hen iawn o Excel, yna mae'r dyddiadau'n cael eu grwpio'n awtomatig yn ôl blynyddoedd, chwarteri a misoedd. Os oes angen grŵp gwahanol arnoch (neu os nad oes ei angen arnoch o gwbl), gallwch ei drwsio trwy dde-glicio ar unrhyw ddyddiad a dewis gorchmynion Grŵp / Ungroup (Grŵp / Ungroup).
Os ydych am weld y ddau gyfanswm canlyniadol yn ôl cyfnodau a'r cyfanswm rhedegol mewn colofn ar wahân, yna mae'n gwneud synnwyr i daflu'r maes i'r ardal gwerth Sold eto i gael copi dyblyg o'r cae - ynddo byddwn yn troi arddangosiad y cyfansymiau rhedeg ymlaen. I wneud hyn, de-gliciwch ar y maes a dewiswch y gorchymyn Cyfrifiadau Ychwanegol – Cyfanswm Cronnus (Dangos Gwerthoedd fel — Cyfansymiau Cronnus):
Yno gallwch hefyd ddewis yr opsiwn o dyfu cyfansymiau fel canran, ac yn y ffenestr nesaf mae angen i chi ddewis y maes y bydd y croniad yn mynd iddo - yn ein hachos ni, dyma'r maes dyddiad:
Manteision y dull hwn:
- Mae llawer iawn o ddata yn cael ei ddarllen yn gyflym.
- Nid oes angen nodi unrhyw fformiwlâu â llaw.
- Wrth newid y data ffynhonnell, mae'n ddigon i ddiweddaru'r crynodeb gyda botwm dde'r llygoden neu gyda'r gorchymyn Data - Refresh All.
Mae'r anfanteision yn dilyn o'r ffaith mai crynodeb yw hwn, sy'n golygu na allwch chi wneud beth bynnag rydych chi ei eisiau ynddo (mewnosod llinellau, ysgrifennu fformiwlâu, adeiladu unrhyw ddiagramau, ac ati) na fydd yn gweithio mwyach.
Dull 3: Ymholiad Pŵer
Gadewch i ni lwytho ein tabl “clyfar” gyda data ffynhonnell i mewn i'r golygydd ymholiad Power Query gan ddefnyddio'r gorchymyn Data – O Dabl/Amrediad (Data — O Dabl/Ystod). Yn y fersiynau diweddaraf o Excel, gyda llaw, fe'i hailenwyd - nawr fe'i gelwir Gyda dail (O'r Daflen):
Yna byddwn yn cyflawni'r camau canlynol:
1. Trefnwch y tabl mewn trefn esgynnol yn ôl y golofn dyddiad gyda'r gorchymyn Trefnu esgynnol yn y gwymplen hidlydd ym mhennyn y tabl.
2. Ychydig yn ddiweddarach, i gyfrifo'r cyfanswm rhedeg, mae angen colofn ategol gyda'r rhif rhes trefnol. Gadewch i ni ei ychwanegu gyda'r gorchymyn Ychwanegu Colofn - Colofn Fynegai - O 1 (Ychwanegu colofn - colofn fynegai - O 1).
3. Hefyd, i gyfrifo'r cyfanswm rhedeg, mae angen cyfeiriad at y golofn Sold, lle mae ein data cryno. Yn Power Query, gelwir colofnau hefyd yn rhestrau (rhestr) ac i gael dolen iddo, de-gliciwch ar bennawd y golofn a dewiswch y gorchymyn Manylion (Dangos manylion). Bydd y mynegiant sydd ei angen arnom yn ymddangos yn y bar fformiwla, sy'n cynnwys enw'r cam blaenorol #"mynegai wedi'i ychwanegu", o ble rydym yn cymryd y tabl ac enw'r golofn [Gwerthiant] o'r tabl hwn mewn cromfachau sgwâr:
Copïwch yr ymadrodd hwn i'r clipfwrdd i'w ddefnyddio ymhellach.
4. Dileu cam olaf mwy diangen Sold ac ychwanegwch yn lle hynny golofn wedi'i chyfrifo ar gyfer cyfrifo'r cyfanswm rhedeg gyda'r gorchymyn Ychwanegu Colofn - Colofn Custom (Ychwanegu colofn - colofn personol). Bydd y fformiwla sydd ei hangen arnom yn edrych fel hyn:
Yma y swyddogaeth Rhestr.Amrediad yn cymryd y rhestr wreiddiol (colofn [Gwerthiant]) ac yn tynnu elfennau ohono, gan ddechrau o'r cyntaf (yn y fformiwla, dyma 0, gan fod rhifo yn Power Query yn dechrau o sero). Nifer yr elfennau i'w hadalw yw'r rhif rhes a gymerwn o'r golofn [Mynegai]. Felly dim ond un gell gyntaf o'r golofn y mae'r swyddogaeth hon ar gyfer y rhes gyntaf yn ei dychwelyd Sold. Ar gyfer yr ail linell - y ddwy gell gyntaf eisoes, ar gyfer y drydedd - y tair gyntaf, ac ati.
Wel, yna y swyddogaeth Rhestr.Sum yn crynhoi'r gwerthoedd a echdynnwyd ac rydym yn cael ym mhob rhes swm yr holl elfennau blaenorol, hy cyfanswm cronnus:
Mae'n aros i ddileu'r golofn Mynegai nad oes ei hangen arnom mwyach a llwytho'r canlyniadau yn ôl i Excel gyda'r Cartref - Close & Load to command.
Mae'r broblem yn cael ei datrys.
Cyflym a Furious
Mewn egwyddor, gallai hyn fod wedi cael ei atal, ond mae pry bach yn yr eli - mae'r cais rydyn ni'n ei greu yn gweithio ar gyflymder crwban. Er enghraifft, ar fy PC nid y PC gwannaf, mae tabl o ddim ond 2000 o resi yn cael ei brosesu mewn 17 eiliad. Beth os oes mwy o ddata?
I gyflymu, gallwch ddefnyddio byffro gan ddefnyddio'r swyddogaeth List.Buffer arbennig, sy'n llwytho'r rhestr (rhestr) a roddir iddo fel dadl i mewn i RAM, sy'n cyflymu mynediad iddo yn y dyfodol yn fawr. Yn ein hachos ni, mae'n gwneud synnwyr i glustogi'r rhestr #”Mynegai Ychwanegol”[Sold], y mae'n rhaid i Power Query ei chyrchu wrth gyfrifo'r cyfanswm rhedeg ym mhob rhes o'n tabl 2000 rhes.
I wneud hyn, yn y golygydd Power Query ar y Prif dab, cliciwch ar y botwm Golygydd Uwch (Cartref - Golygydd Uwch) i agor cod ffynhonnell ein hymholiad yn yr iaith M sydd wedi'i chynnwys yn Power Query:
Ac yna ychwanegu llinell gyda newidyn yno FyRhestr, y dychwelir ei werth gan y swyddogaeth byffro, ac yn y cam nesaf rydym yn disodli'r alwad i'r rhestr gyda'r newidyn hwn:
Ar ôl gwneud y newidiadau hyn, bydd ein hymholiad yn dod yn llawer cyflymach a bydd yn ymdopi â thabl 2000 rhes mewn dim ond 0.3 eiliad!
Peth arall, iawn? 🙂
- Siart Pareto (80/20) a sut i'w adeiladu yn Excel
- Chwiliad allweddair mewn testun a byffro ymholiad yn Power Query