Mae tablau colyn yn dda i bawb - maent yn cyfrifo'n gyflym, ac wedi'u ffurfweddu'n hyblyg, a gellir cyfuno'r dyluniad yn gain, os oes angen. Ond mae yna hefyd ychydig o hedfan yn yr eli, yn arbennig, yr anallu i greu crynodeb, lle na ddylai'r ardal werth gynnwys rhifau, ond testun.
Gadewch i ni geisio mynd o gwmpas y cyfyngiad hwn a chreu “cwpl o faglau” mewn sefyllfa debyg.
Tybiwch fod ein cwmni'n cludo ei gynhyrchion mewn cynwysyddion i sawl dinas yn Ein Gwlad a Kazakhstan. Anfonir cynwysyddion ddim mwy nag unwaith y mis. Mae gan bob cynhwysydd rif alffaniwmerig. Fel data cychwynnol, mae yna dabl safonol sy'n rhestru danfoniadau, y mae angen i chi wneud rhyw fath o grynodeb ohono er mwyn gweld yn glir nifer y cynwysyddion a anfonir i bob dinas a phob mis:
Er hwylustod, gadewch i ni wneud y tabl gyda'r data cychwynnol “smart” ymlaen llaw gan ddefnyddio'r gorchymyn Cartref - Fformat fel bwrdd (Cartref - Fformat fel Tabl) a rho enw iddi Dosbarthu tab Constructor (Dylunio). Yn y dyfodol, bydd hyn yn symleiddio bywyd, oherwydd. bydd modd defnyddio enw'r tabl a'i golofnau yn uniongyrchol yn y fformiwlâu.
Dull 1. Yr hawsaf – defnyddiwch Power Query
Mae Power Query yn offeryn hynod bwerus ar gyfer llwytho a thrawsnewid data yn Excel. Mae'r ychwanegiad hwn wedi'i gynnwys yn Excel yn ddiofyn ers 2016. Os oes gennych Excel 2010 neu 2013, gallwch ei lawrlwytho a'i osod ar wahân (yn hollol rhad ac am ddim).
Y broses gyfan, er eglurder, dadansoddais gam wrth gam yn y fideo canlynol:
Os nad yw'n bosibl defnyddio Power Query, gallwch fynd i ffyrdd eraill - trwy dabl colyn neu fformiwlâu.
Dull 2. Crynodeb ategol
Gadewch i ni ychwanegu un golofn arall at ein tabl gwreiddiol, lle gan ddefnyddio fformiwla syml rydym yn cyfrifo nifer pob rhes yn y tabl:
Yn amlwg, mae angen -1, oherwydd mae gennym bennawd un llinell yn ein tabl. Os nad yw eich tabl ar ddechrau'r ddalen, yna gallwch ddefnyddio fformiwla ychydig yn fwy cymhleth, ond cyffredinol, sy'n cyfrifo'r gwahaniaeth yn niferoedd y rhes gyfredol a phennawd y tabl:
Nawr, mewn ffordd safonol, byddwn yn adeiladu tabl colyn o'r math a ddymunir yn seiliedig ar ein data, ond yn y maes gwerth byddwn yn gollwng y maes Rhif llinell yn lle yr hyn a ddymunwn cynhwysydd:
Gan nad oes gennym nifer o gynwysyddion yn yr un ddinas yn yr un mis, bydd ein crynodeb, mewn gwirionedd, yn rhoi nid y swm, ond rhifau llinell y cynwysyddion sydd eu hangen arnom.
Yn ogystal, gallwch chi ddiffodd grand ac is-gyfansymiau ar y tab Adeiladwr - cyfansymiau cyffredinol и Is-gyfanswm (Dyluniad - Cyfanswm Mawr, Is-gyfansymiau) ac yn yr un lle newidiwch y crynodeb i gynllun bwrdd mwy cyfleus gyda'r botwm Adroddiad ffug (Cynllun yr Adroddiad).
Felly, rydym eisoes hanner ffordd i'r canlyniad: mae gennym fwrdd lle, ar groesffordd y ddinas a'r mis, mae rhif rhes yn y tabl ffynhonnell, lle mae'r cod cynhwysydd sydd ei angen arnom yn gorwedd.
Nawr, gadewch i ni gopïo'r crynodeb (i'r un ddalen neu'r llall) a'i gludo fel gwerthoedd, ac yna rhowch ein fformiwla i'r ardal werth, a fydd yn tynnu cod y cynhwysydd yn ôl y rhif llinell a geir yn y crynodeb:
swyddogaeth IF (OS), yn yr achos hwn, yn gwirio nad yw'r gell nesaf yn y crynodeb yn wag. Os yn wag, yna allbynnwch llinyn testun gwag “”, hy gadewch y gell yn wag. Os nad yw'n wag, yna echdynnwch o'r golofn Cynhwysydd tabl ffynhonnell Dosbarthu cynnwys cell yn ôl rhif rhes gan ddefnyddio swyddogaeth MYNEGAI (MYNEGAI).
Efallai mai'r unig bwynt nad yw'n amlwg iawn yma yw'r gair dwbl Cynhwysydd yn y fformiwla. Math rhyfedd o ysgrifennu:
Cyflenwadau[[Cynhwysydd]:[Cynhwysydd]]
… dim ond i gyfeirio at y golofn sydd ei angen Cynhwysydd yn absoliwt (fel cyfeiriad gyda $signs ar gyfer tablau “di-smart” cyffredin) ac nid oedd yn llithro i golofnau cyfagos wrth gopïo ein fformiwla i'r dde.
Yn y dyfodol, wrth newid y data yn y tabl ffynhonnell Dosbarthu, rhaid inni gofio diweddaru ein crynodeb ategol gyda rhifau llinell trwy dde-glicio arno a dewis y gorchymyn Diweddaru ac Arbed (Adnewyddu).
Dull 3. Fformiwlâu
Nid yw'r dull hwn yn gofyn am greu tabl colyn canolradd a diweddaru â llaw, ond mae'n defnyddio “arf trwm” Excel - y swyddogaeth CRYNODEB (SUMIFS). Yn lle edrych i fyny rhifau rhesi mewn crynodeb, gallwch eu cyfrifo gan ddefnyddio'r fformiwla hon:
Gyda rhywfaint o swmp allanol, mewn gwirionedd, mae hwn yn achos defnydd safonol ar gyfer y swyddogaeth crynhoi dethol CRYNODEBA sy'n crynhoi rhifau'r rhesi ar gyfer y ddinas a'r mis a roddwyd. Unwaith eto, gan nad oes gennym nifer o gynwysyddion yn yr un ddinas yn yr un mis, bydd ein swyddogaeth, mewn gwirionedd, yn rhoi allan nid y swm, ond rhif y llinell ei hun. Ac yna y swyddogaeth eisoes yn gyfarwydd o'r dull blaenorol MYNEGAI Gallwch hefyd dynnu codau cynhwysydd:
Wrth gwrs, yn yr achos hwn, nid oes angen i chi feddwl am ddiweddaru'r crynodeb mwyach, ond ar dablau mawr, y swyddogaeth SYMIAU gall fod yn amlwg yn araf. Yna bydd yn rhaid i chi ddiffodd diweddaru fformiwlâu yn awtomatig, neu ddefnyddio'r dull cyntaf - tabl colyn.
Os nad yw ymddangosiad y crynodeb yn addas iawn ar gyfer eich adroddiad, yna gallwch dynnu rhifau rhesi ohono i'r tabl terfynol nid yn uniongyrchol, fel y gwnaethom ni, ond gan ddefnyddio'r ffwythiant GET.PIVOT.TABLE.DATA (GET.PIVOT.DATA). Mae sut i wneud hyn i'w weld yma.
- Sut i greu adroddiad gan ddefnyddio tabl colyn
- Sut i osod cyfrifiadau mewn tablau colyn
- Cyfrif dethol gyda SUMIFS, COUNTIFS, ac ati.