Swm gwerthoedd yn ôl grŵp yn Excel
Weithiau, efallai y bydd yn ofynnol i chi grynhoi gwerthoedd yn seiliedig ar grŵp mewn tabl. Er enghraifft, mae gen i restr o gynhyrchion gyda'u symiau cyfatebol mewn colofn arall, nawr, rydw i eisiau cael y swm is-gyfanswm ar gyfer pob cynnyrch fel y dangosir isod y screenshot. Bydd y tiwtorial hwn yn cyflwyno rhai fformiwlâu ar gyfer datrys y dasg hon yn Excel.
- Gwerthoedd swm fesul grŵp - is-gyfanswm yn y tabl data gwreiddiol
- Gwerthoedd swm fesul grŵp - subtotals mewn lleoliad arall
Gwerthoedd swm fesul grŵp - is-gyfanswm yn y tabl data gwreiddiol
Yma dylai'r gystrawen generig i grynhoi gwerthoedd yn seiliedig ar grŵp yn Excel fod fel hyn:
- group_name: Y gell ag enw'r grŵp rydych chi am ei chrynhoi;
- cell_above_group_name: Y gell uwchben enw'r grŵp;
- group_range: Mae'r ystod o gelloedd yn cynnwys enwau'r grwpiau;
- sum_range: Yr ystod o gelloedd sy'n cyfateb i'r enw grŵp penodedig i grynhoi gyda'i gilydd.
Os yw'r data'n cael ei ddidoli yn ôl y golofn grwpio, trefnir yr un cynnyrch gyda'i gilydd ag isod y llun a ddangosir. I is-gyfanswm data yn ôl grŵp, gallwch greu fformwlâu yn seiliedig ar swyddogaethau IF a SUMIF.
1. Copïwch a gludwch y fformiwla ganlynol i mewn i gell wag wrth ochr eich data:
- Nodyn: Yn y fformiwla:
- A1 yw'r gell pennawd, a A2 yw'r gell gyntaf sy'n cynnwys enw'r cynnyrch rydych chi am ei ddefnyddio;
- A2: A13 ydy'r rhestr yn cynnwys enwau'r cynnyrch rydych chi am eu crynhoi yn seiliedig ar;
- B2: B13 yw'r data colofn rydych chi am gael yr is-gyfanswm.
2. Ac yna, llusgwch y ddolen llenwi i lawr i'r celloedd rydych chi am ddefnyddio'r fformiwla hon, a chyfrifir yr is-gyfanswm yn seiliedig ar enw pob cynnyrch, gweler y screenshot:
Esboniad o'r fformiwla:
=IF(A2=A1,"",SUMIF($A$2:$A$13,A2,$B$2:$B$13))
- SUMIF($A$2:$A$13,A2,$B$2:$B$13): Bydd y swyddogaeth SUMIF hon yn crynhoi'r gwerthoedd yn ystod B2: B13 yn unig os yw eu gwerthoedd cyfatebol yn ystod A2: A13 yn hafal i'r meini prawf A2.
- IF(A2=A1,"",SUMIF($A$2:$A$13,A2,$B$2:$B$13)): Mae'r swyddogaeth IF hon yn gwirio pob gwerth yng ngholofn A os yw ei werth yr un peth â'r gwerth yn y gell uchod. Er enghraifft, os yw gwerth cell A2 yn hafal i gell A1, ni fydd unrhyw beth (“”) yn dychwelyd, os nad yw'n cyfateb, bydd canlyniad swyddogaeth SUMIF yn cael ei ddychwelyd.
Gwerthoedd swm fesul grŵp - subtotals mewn lleoliad arall
Os na chaiff gwerthoedd pob grŵp eu trefnu gyda'i gilydd a'u rhestru ar hap yn y golofn, i grynhoi'r gwerthoedd paru yn seiliedig ar grŵp neu gategori, dylech dynnu enw pob grŵp o'r enwau cynnyrch, ac yna cael yr is-gyfanswm yn seiliedig ar enw'r grŵp hwn fel isod screenshot a ddangosir.
1. Yn gyntaf, tynnwch enw'r grŵp unigryw trwy ddefnyddio'r fformiwla arae ganlynol, yna pwyswch Ctrl + Shift + Enter allweddi gyda'i gilydd i gael y canlyniad cyntaf.
- Nodyn: Yn y fformiwla:
- A2: A13 yw'r ystod o gelloedd lle i echdynnu'r holl werthoedd unigryw;
- D1 yw'r gell uwchben eich fformiwla a gofnodwyd.
2. Ac yna, dewiswch y gell fformiwla a llusgwch y ddolen llenwi i lawr i'r celloedd nes bod holl enwau'r cynnyrch yn cael eu harddangos, gweler y screenshot:
3. Nawr, gallwch chi grynhoi'r gwerthoedd yn ôl enw'r grŵp rydych chi wedi'i dynnu. Yn yr achos hwn, bydd swyddogaeth SUMIF yn cael ei defnyddio yma, rhowch y fformiwla isod i mewn i gell wag, yn yr enghraifft hon, byddaf yn ei rhoi yng nghell E2.
4. Yna, llusgwch y ddolen llenwi i lawr i gopïo'r fformiwla hon i ddychwelyd cyfanswm trefn grwpiau eraill, gweler y screenshot:
Swyddogaeth gymharol a ddefnyddir:
- SUMIF:
- Gall swyddogaeth SUMIF helpu i grynhoi celloedd yn seiliedig ar un maen prawf.
- IF:
- Mae'r swyddogaeth IF yn profi am gyflwr penodol ac yn dychwelyd y gwerth cyfatebol rydych chi'n ei gyflenwi ar gyfer GWIR neu GAU.
Mwy o erthyglau:
- Symiau Anfoneb Is-gyfanswm Yn ôl Oed
- I grynhoi symiau'r anfoneb yn seiliedig ar oedran fel y gall isod y llun a ddangosir fod yn dasg gyffredin yn Excel, bydd y tiwtorial hwn yn dangos sut i is-gyfanswm symiau anfoneb yn ôl oedran gyda swyddogaeth SUMIF arferol.
- Swm Gwerthoedd Lleiaf neu Waelod N.
- Yn Excel, mae'n hawdd i ni grynhoi ystod o gelloedd trwy ddefnyddio'r swyddogaeth SUM. Weithiau, efallai y bydd angen i chi grynhoi'r rhifau 3, 5 neu n lleiaf neu waelod mewn ystod ddata fel islaw'r screenshot a ddangosir. Yn yr achos hwn, gall y CYFLWYNIAD ynghyd â'r swyddogaeth BACH eich helpu chi i ddatrys y broblem hon yn Excel.
- Swm Gwerthoedd Lleiaf neu Waelod N Yn Seiliedig ar Feini Prawf
- Mewn tiwtorial blaenorol, rydym wedi trafod sut i grynhoi'r n gwerthoedd lleiaf mewn ystod ddata. Yn yr erthygl hon, byddwn yn perfformio gweithrediad datblygedig pellach - i grynhoi'r n gwerthoedd isaf yn seiliedig ar un neu fwy o feini prawf yn Excel.
Yr Offer Cynhyrchedd Swyddfa Gorau
Kutools for Excel - Yn Eich Helpu i Sefyll Allan O'r Tyrfa
Kutools for Excel Yn cynnwys dros 300 o nodweddion, Sicrhau mai dim ond clic i ffwrdd yw'r hyn sydd ei angen arnoch chi...
Office Tab - Galluogi Darllen a Golygu Tabiau yn Microsoft Office (gan gynnwys Excel)
- Un eiliad i newid rhwng dwsinau o ddogfennau agored!
- Gostyngwch gannoedd o gliciau llygoden i chi bob dydd, ffarweliwch â llaw llygoden.
- Yn cynyddu eich cynhyrchiant 50% wrth wylio a golygu sawl dogfen.
- Yn dod â Thabiau Effeithlon i'r Swyddfa (gan gynnwys Excel), Just Like Chrome, Edge a Firefox.
