Note: The other languages of the website are Google-translated. Back to English

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

Yma dylai'r gystrawen generig i grynhoi gwerthoedd yn seiliedig ar grŵp yn Excel fod fel hyn:

=IF(group_name=cell_above_group_name, “” ,SUMIF(group_range,group_name,sum_range))
  • 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:

=IF(A2=A1,"",SUMIF($A$2:$A$13,A2,$B$2:$B$13))
  • 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.

=INDEX($A$2:$A$13,MATCH(0,COUNTIF($D$1:D1,$A$2:$A$13),0))
  • 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.

=SUMIF($A$2:$A$13,D2,$B$2:$B$13)

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 Dyrfa

Hoffech chi gwblhau eich gwaith dyddiol yn gyflym ac yn berffaith? Mae Kutools ar gyfer Excel yn dod â 300 o nodweddion uwch pwerus (Cyfuno llyfrau gwaith, swm yn ôl lliw, cynnwys cell hollti, dyddiad trosi, ac yn y blaen ...) ac arbed amser 80% i chi.

  • Wedi'i gynllunio ar gyfer 1500 o senarios gwaith, yn eich helpu i ddatrys problemau Excel 80%.
  • Gostyngwch filoedd o gliciau bysellfwrdd a llygoden bob dydd, lleddfu'ch llygaid a'ch dwylo blinedig.
  • Dewch yn arbenigwr Excel mewn 3 munud. Nid oes angen cofio unrhyw fformiwlâu poenus a chodau VBA mwyach.
  • Treial am ddim diderfyn 30 diwrnod. Gwarant arian yn ôl 60 diwrnod. Uwchraddio a chefnogi am ddim am 2 flynedd.
Rhuban o Excel (gyda Kutools ar gyfer Excel wedi'i osod)

Tab Swyddfa - Galluogi Darllen a Golygu Tabbed yn Microsoft Office (cynnwys 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), Yn union fel Chrome, Firefox, a New Internet Explorer.
Ergyd Sgrin o Excel (gyda Office Tab wedi'i osod)
sylwadau (0)
Dim sgôr eto. Byddwch y cyntaf i sgorio!
Nid oes unrhyw sylwadau wedi'u postio yma eto
Gadewch eich sylwadau
Postio fel Gwestai
×
Graddiwch y swydd hon:
0   Cymeriadau
Lleoliadau a Awgrymir