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

Sut i ddod o hyd i'r holl gyfuniadau sy'n hafal i swm penodol yn Excel?

Er enghraifft, mae gen i'r rhestr ganlynol o rifau, ac yn awr, rydw i eisiau gwybod pa gyfuniad o rifau yn y rhestr sy'n cyfrif hyd at 480, yn y screenshot canlynol a ddangosir, gallwch chi weld bod yna bum grŵp o gyfuniadau posib sy'n adio yn gyfartal i 480, fel 300 + 60 + 120, 300 + 60 + 40 + 80, ac ati. Yr erthygl hon, byddaf yn siarad am rai dulliau i ddarganfod pa gelloedd sy'n dod i werth penodol yn Excel.


Dewch o hyd i gyfuniad celloedd sy'n hafal i swm penodol â fformwlâu

Yn gyntaf, mae angen i chi greu rhai enwau amrediad, ac yna defnyddio fformiwla arae i ddod o hyd i'r celloedd sy'n cyfateb i'r gwerth targed, gwnewch hynny gyda'r cam wrth gam canlynol:

1. Dewiswch y rhestr rifau a diffiniwch y rhestr hon enw amrediad - Ystod1 i mewn i'r Blwch Enw, a'r wasg Rhowch allwedd i orffen enw'r amrediad a ddiffiniwyd, gweler y screenshot:

2. Ar ôl diffinio enw amrediad ar gyfer y rhestr rifau, yna mae angen i chi greu dau enw amrediad pellach yn y Rheolwr Enw blwch, cliciwch Fformiwlâu > Rheolwr Enw, Yn y Rheolwr Enw blwch deialog, cliciwch Newydd botwm, gweler sgrinluniau:

3. Yn y popped allan Enw Newydd deialog, nodwch enw List1 i mewn i'r Enw maes, a theipiwch y fformiwla hon = ROW (INDIRECT ("1:" & ROWS (Range1))) (Ystod1 yw'r enw amrediad rydych chi wedi'i greu yng ngham 1) i mewn i'r Yn cyfeirio at maes, gweler y screenshot:

4. Cliciwch OK i ddychwelyd i'r Rheolwr Enw deialog, yna parhewch i glicio Newydd botwm i greu enw amrediad arall, yn y Enw Newydd deialog, nodwch enw List2 i mewn i'r Enw maes, a theipiwch y fformiwla hon = ROW (INDIRECT ("1:" & 2 ^ ROWS (Range1))) (Ystod1 yw'r enw amrediad rydych chi wedi'i greu yng ngham 1) i mewn i'r Yn cyfeirio at maes, gweler y screenshot:

5. Ar ôl creu'r enwau amrediad, cymhwyswch y fformiwla arae ganlynol i gell B2:

=IF(ISNUMBER(MATCH(ROWS($1:1),IF(INDEX(MOD(INT((List2-1)/2^(TRANSPOSE(List1)-1)),2),MATCH(TRUE,MMULT(MOD(INT((List2-1)/2^(TRANSPOSE(List1)-1)),2),Range1)=$C$2,0),),TRANSPOSE(List1)),0)),"X",""), a'r wasg Shift + Ctrl + Enter allweddi gyda'i gilydd, yna llusgwch y ddolen llenwi i lawr i gell B8, rhif olaf y rhestr, a gallwch weld bod y rhifau sy'n gyfanswm o 480 wedi'u marcio fel X yng ngholofn B, gweler y screenshot:

  • Nodiadau:
  • Yn y fformiwla hir uchod: List1, List2 ac Ystod1 yw'r enwau amrediad rydych chi wedi'u creu mewn camau blaenorol, C2 yw'r gwerth penodol yr ydych am i rifau gael ei ychwanegu ato.
  • Os oes gan fwy nag un cyfuniad o werthoedd swm sy'n hafal i'r gwerth penodol, dim ond un cyfuniad sydd wedi'i restru.

Darganfyddwch a rhestrwch yr holl gyfuniadau sy'n hafal i swm penodol yn gyflym ac yn hawdd yn Excel

Kutools ar gyfer Excel's Colur Rhif gall cyfleustodau eich helpu i ddarganfod a rhestru'r holl gyfuniadau a chyfuniadau penodol sy'n hafal i rif swm penodol yn gyflym ac yn hawdd. Cliciwch i lawrlwytho Kutools ar gyfer Excel!

Kutools ar gyfer Excel: gyda mwy na 300 o ychwanegion Excel defnyddiol, am ddim i roi cynnig arnynt heb unrhyw gyfyngiad mewn 30 diwrnod. Dadlwythwch a threial am ddim Nawr!


Dewch o hyd i gyfuniad celloedd sy'n hafal i swm penodol ag Solver Add-in

Os ydych wedi drysu gyda'r dull uchod, mae Excel yn cynnwys a Ychwanegiad Datrysydd nodwedd, trwy ddefnyddio'r ychwanegiad hwn, gallwch hefyd nodi'r rhifau y mae cyfanswm yn hafal i werth penodol.

1. Yn gyntaf, mae angen i chi actifadu hyn Datryswr ychwanegu i fewn, ewch i ffeil > Dewisiadau, Yn y Dewisiadau Excel blwch deialog, cliciwch Add-Ins o'r cwarel chwith, ac yna cliciwch Ychwanegiad Datrysydd oddi wrth y Ychwanegiadau Cais Anweithredol adran, gweler y screenshot:

2. Yna cliciwch Go botwm i fynd i mewn i'r Add-Ins deialog, gwirio Ychwanegiad Datrysydd opsiwn, a chlicio OK i osod yr ychwanegiad hwn yn llwyddiannus.

3. Ar ôl actifadu'r ychwanegiad Datryswr, yna mae angen i chi nodi'r fformiwla hon yn y gell B9: = CYFLWYNIAD (B2: B9, A2: A9), (B2: B9 yn gell golofn wag wrth ymyl eich rhestr rifau, a A2: A9 yw'r rhestr rifau rydych chi'n eu defnyddio. ), a'r wasg Rhowch allwedd, gweler y screenshot:

4. Yna cliciwch Dyddiad > Datryswr i fynd i'r Paramedr Datryswr blwch deialog, yn y dialog, gwnewch y gweithrediadau canlynol:

(1.) Cliciwch  botwm i ddewis y gell B10 lle mae eich fformiwla i mewn o'r Gosod Amcan adran;

(2.) Yna yn y I adran, dewiswch Gwerth Of, a nodwch eich gwerth targed 480 yn ôl yr angen;

(3.) O dan y Trwy Newid Celloedd Amrywiol adran, cliciwch botwm i ddewis ystod celloedd B2: B9 ble fydd yn marcio'ch rhifau cyfatebol.

5. Ac yna cliciwch Ychwanegu botwm i fynd i'r Ychwanegu Cyfyngiad blwch deialog, cliciwch botwm i ddewis ystod celloedd B2: B9, a dethol bin o'r gwymplen, gweler y screenshot:

6. Cliciwch OK i fynd yn ôl y Paramedr Datryswr deialog, yna cliciwch Datrys botwm, rai munudau'n ddiweddarach, a Canlyniadau Datryswr mae blwch deialog wedi'i popio allan, a gallwch weld bod y cyfuniad o gelloedd sy'n hafal i swm penodol 480 wedi'u marcio fel 1. Yn y Canlyniadau Datryswr deialog, dewiswch Cadwch Datrysiad Datryswr opsiwn, a chlicio OK i adael y dialog. Gweler y screenshot:

Nodyn: Mae'r dull hwn hefyd yn gallu cael un celloedd cyfuniad yn unig os oes mwy nag un cyfuniad o werthoedd sydd â swm sy'n hafal i'r gwerth penodol.


Dewch o hyd i gyfuniad celloedd sy'n hafal i swm penodol â Swyddogaeth Diffiniedig Defnyddiwr

Mae'r ddau ddull cyntaf i gyd yn gymhleth i'r rhan fwyaf o'n defnyddwyr Excel, yma, gallaf greu cod VBA i ddatrys y swydd hon yn gyflym ac yn hawdd.

I gael y canlyniad cywir, rhaid i chi ddidoli'r rhestr rifau yn nhrefn ddisgynnol yn gyntaf. Ac yna gwnewch gyda'r camau canlynol:

1. Daliwch i lawr y ALT + F11 allweddi i agor y Microsoft Visual Basic ar gyfer Ceisiadau ffenestr.

2. Cliciwch Mewnosod > Modiwlau, a gludwch y cod canlynol yn y Modiwlau Ffenestr.

Cod VBA: Darganfyddwch gyfuniad celloedd sy'n hafal i swm penodol:

Function GetCombination(CoinsRange As Range, SumCellId As Double) As String
'updateby Extendoffice
    Dim xStr As String
    Dim xSum As Double
    Dim xCell As Range
    xSum = SumCellId
    For Each xCell In CoinsRange
        If Not (xSum / xCell < 1) Then
            xStr = xStr & Int(xSum / xCell) & " of " & xCell & "  "
            xSum = xSum - (Int(xSum / xCell)) * xCell
        End If
    Next
    GetCombination = xStr
End Function

3. Yna arbedwch a chau y ffenestr god hon, ac yna ewch yn ôl i'r daflen waith, a nodi'r fformiwla hon = cyfaddawdu (A2: A9, C2) i mewn i gell wag, a gwasgwch Rhowch yn allweddol, fe gewch y canlyniad canlynol sy'n dangos y rhifau cyfuniad sy'n hafal i swm penodol, gweler y screenshot:

  • Nodiadau:
  • Yn y fformiwla uchod, A2: A9 yw'r ystod rhif, a C2 yn cynnwys y gwerth targed rydych chi am fod yn hafal iddo.
  • Os oes gan fwy nag un cyfuniad o werthoedd swm sy'n hafal i'r gwerth penodol, dim ond un cyfuniad sydd wedi'i restru.

Dewch o hyd i'r holl gyfuniadau sy'n hafal i swm penodol gyda nodwedd anhygoel

Efallai bod yr holl ddulliau uchod ychydig yn anodd i chi, yma, byddaf yn cyflwyno teclyn pwerus, Kutools ar gyfer Excel, Gyda'i Colur Rhif nodwedd, gallwch gael yr holl gyfuniadau sy'n hafal i swm penodol yn gyflym.

Awgrym:I gymhwyso hyn Colur Rhif nodwedd, yn gyntaf, dylech lawrlwytho'r Kutools ar gyfer Excel, ac yna cymhwyswch y nodwedd yn gyflym ac yn hawdd.

Ar ôl gosod Kutools ar gyfer Excel, gwnewch fel hyn:

1. Cliciwch Kutools > Cynnwys > Colur Rhif, gweler y screenshot:

2. Yna, yn y Lluniwch rif blwch deialog, cliciwch botwm i ddewis y rhestr rifau rydych chi am eu defnyddio o'r Ffynhonnell Data, ac yna nodwch y cyfanswm yn y Swm blwch testun, gweler y screenshot:

3. Ac yna, cliciwch OK botwm, bydd blwch prydlon yn popio allan i'ch atgoffa i ddewis cell i ddod o hyd i'r canlyniad, gweler y screenshot:

4. Yna, cliciwch OK, ac yn awr, mae'r holl gyfuniadau sy'n hafal i'r rhif penodol hwnnw wedi'u harddangos fel isod sgrinlun a ddangosir:

Cliciwch i Lawrlwytho Kutools ar gyfer Excel a threial am ddim Nawr!


Demo: Dewch o hyd i gyfuniad celloedd sy'n hafal i swm penodol yn Excel


Yr Offer Cynhyrchedd Swyddfa Gorau

Mae Kutools for Excel yn Datrys y rhan fwyaf o'ch Problemau, ac yn Cynyddu Eich Cynhyrchedd 80%

  • Ailddefnyddio: Mewnosod yn gyflym fformwlâu cymhleth, siartiau ac unrhyw beth rydych chi wedi'i ddefnyddio o'r blaen; Amgryptio Celloedd gyda chyfrinair; Creu Rhestr Bostio ac anfon e-byst ...
  • Bar Fformiwla Gwych (golygu llinellau lluosog o destun a fformiwla yn hawdd); Cynllun Darllen (darllen a golygu nifer fawr o gelloedd yn hawdd); Gludo i'r Ystod Hidlo...
  • Uno Celloedd / Rhesi / Colofnau heb golli Data; Cynnwys Celloedd Hollt; Cyfuno Rhesi / Colofnau Dyblyg... Atal Celloedd Dyblyg; Cymharwch y Meysydd...
  • Dewiswch Dyblyg neu Unigryw Rhesi; Dewiswch Blank Rows (mae pob cell yn wag); Darganfyddiad Gwych a Darganfyddiad Niwlog mewn Llawer o Lyfrau Gwaith; Dewis ar Hap ...
  • Copi Union Celloedd Lluosog heb newid cyfeirnod fformiwla; Auto Creu Cyfeiriadau i Daflenni Lluosog; Mewnosod Bwledi, Blychau Gwirio a mwy ...
  • Testun Detholiad, Ychwanegu Testun, Tynnu yn ôl Swydd, Tynnwch y Gofod; Creu ac Argraffu Subtotals Paging; Trosi rhwng Cynnwys a Sylwadau Celloedd...
  • Hidlo Super (arbed a chymhwyso cynlluniau hidlo i ddalenni eraill); Trefnu Uwch yn ôl mis / wythnos / dydd, amlder a mwy; Hidlo Arbennig gan feiddgar, italig ...
  • Cyfuno Llyfrau Gwaith a Thaflenni Gwaith; Uno Tablau yn seiliedig ar golofnau allweddol; Rhannwch Ddata yn Daflenni Lluosog; Trosi Swp xls, xlsx a PDF...
  • Mwy na 300 o nodweddion pwerus. Yn cefnogi Swyddfa / Excel 2007-2019 a 365. Yn cefnogi pob iaith. Defnydd hawdd yn eich menter neu sefydliad. Nodweddion llawn treial am ddim 30 diwrnod. Gwarant arian yn ôl 60 diwrnod.
tab kte 201905

Mae Tab Office yn Dod â rhyngwyneb Tabbed i'r Swyddfa, a Gwneud Eich Gwaith yn Haws o lawer

  • Galluogi golygu a darllen tabbed yn Word, Excel, PowerPoint, Cyhoeddwr, Mynediad, Visio a Phrosiect.
  • Agor a chreu dogfennau lluosog mewn tabiau newydd o'r un ffenestr, yn hytrach nag mewn ffenestri newydd.
  • Yn cynyddu eich cynhyrchiant 50%, ac yn lleihau cannoedd o gliciau llygoden i chi bob dydd!
gwaelod officetab
sylwadau (46)
Dim sgôr eto. Byddwch y cyntaf i sgorio!
Lleihawyd y sylw hwn gan y safonwr ar y wefan
A oes modd ehangu'r amrediad fel ei fod yn cynnwys mwy nag 8 rhif? Hefyd, nid wyf yn siŵr sut mae'r swyddogaeth hon yn gweithio: "=ROW(INDIRECT("1:"&2^ROWS(Range1)))". Os ceisiaf ehangu "Range1" y tu hwnt i 15 rhes, rwy'n cael gwall #Ref. Mae'n gweithio'n wych gyda dim ond yr 8 rhif, ond beth os oeddech chi am gynnwys, dyweder, 50 rhif neu hyd yn oed 100.
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Yr un mater
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Dyn Gwych!!! Dyn Gwych!!!
Lleihawyd y sylw hwn gan y safonwr ar y wefan
A oes modd ehangu'r amrediad fel y dywed Thom, i ddweud hyd at 50 o rifau, ond hefyd dim ond cyfanswm o chwech o'r niferoedd allan o'r ystod sy'n dod i'r cyfanswm penodedig? Ar hyn o bryd bydd yn darparu'r holl gyfuniadau sy'n cyfateb i'r cyfanswm penodedig. diolch
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Anhygoel. Methu â chael y fformiwla fawr i weithio ond fe weithiodd yr ategyn datryswr yn berffaith. Wedi arbed cymaint o waith i mi.
Lleihawyd y sylw hwn gan y safonwr ar y wefan
ond mae'n cymryd gormod o amser
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Ar y gorau, rydw i'n ddechreuwr datblygedig yn Excel. Rhoddais gynnig ar bopeth ac ni weithiodd. Beth allwn i fod yn ei wneud o'i le?
Lleihawyd y sylw hwn gan y safonwr ar y wefan
sut os oes angen mwy nag un cyfuniad arnaf? Diolch
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Helo. Wnaeth y fersiwn fformiwla ddim gweithio i mi chwaith. Mae'n teimlo fel ei fod yn colli cam. Ni welaf o ble mae'r rhif a nodir yng nghell C2 yn dod i mewn i'r fformiwla.

diolch
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Helo, Dori,


Nid oes fformiwla yn C2, dim ond y gwerth penodol yr ydych am i rifau ei ychwanegu ato.
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Helo, lawrlwythais Kutools ond ni allaf ei gael i ddod o hyd i'r holl combos yn llai na chyfanswm penodedig.
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Llwyddais i gael yr enghraifft gyda Range1 i weithio gyda fy ystod mewn 12 rhes, ond pan newidiais yr ystod i 42 rhes nid oedd yn gweithio. Fe wnes i hyd yn oed ailgychwyn y broses gyfan gyda'r fersiwn 42 rhes ac ni weithiodd hynny ychwaith. Unrhyw syniadau?
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Gwych !!!
Lleihawyd y sylw hwn gan y safonwr ar y wefan
A allech chi uwchlwytho'r Excel?
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Ni weithiodd y macro os oes mwy nag un ateb.
Hefyd, wnes i ddim gweithio os dof o hyd i "0"
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Helo, Lorena,
Cyn cymhwyso'r cod VBA uchod, rhaid i chi ddidoli'r rhestr rifau mewn trefn ddisgynnol yn gyntaf.
Yn ail, nid yw'r cod yn gweithio'n gywir i gael cyfanswm y rhif 0.
Gobeithio y gall eich helpu, diolch!
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Rwy'n ceisio pennu'r cyfuniad gorau o gynnyrch ac rwy'n ansicr ai dyma'r ffordd orau i'w wneud. Ar y mwyaf rwy'n defnyddio tri chynnyrch mewn cyfuniad â 5 manyleb yr un. Mae'r holl fanylebau yn llinol a gellir eu cyfartaleddu wrth eu cymysgu. Mae un cyfuniad fel arfer yn 45,000 pwys ac mae pob swp yn 30,000 pwys. Y rhan fwyaf o'r amser mae ein cyfuniadau yn 15k + 30k ond hoffwn allu cyfrifo ar gyfer y cyfuniadau anarferol gan ddefnyddio'r cynyddrannau yr holl ffordd i lawr i 2000 pwys.
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Heia,

Fy helynt gyda'r fformiwla hon yw ei fod yn rhoi un gwerth i mi am ddigon o weithiau i gael y gwerth targed.
Yn y rhestr o wahanol werthoedd mae rhai gwerthoedd sy'n hafal i'w gilydd.

Ee mae gen i 0,16 am 3 gwaith (y gwerthoedd cyntaf yn y rhestr) ac mae'r fformiwla yn rhoi'r ateb i mi mai fy ngwerth targed yw 593 o 0,16.

Pam nad yw'n cyfuno gwerthoedd gwahanol i gael fy ngwerth targed? Dim ond un gwerth y mae'n ei ddewis ac mae'n rhoi sawl gwaith y bydd yn werth targed.

Unrhyw help neu syniad?


Diolch!
Lleihawyd y sylw hwn gan y safonwr ar y wefan
oes unrhyw un yn gwybod os yw hyn yn gweithio ar google sheets
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Oes mae yna estyniad tebyg i ddatryswr excel o'r enw "datryswr"
Lleihawyd y sylw hwn gan y safonwr ar y wefan
A fyddai rhywun yn gwybod sut i addasu swyddogaeth Getcombination VBA fel na ddylid caniatáu unrhyw ailadrodd?

Er enghraifft, ar gyfer rhifau 1,2,3,4,5,13 os yw 14 i'w gyflawni mae 1,13 yn ddatrysiad, ac nid 14 o 1.
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Swyddogaeth GetCombination (Cronfeydd Amrediad Fel Ystod, SumCellId Fel Dwbl) Fel Llinynnol
'diweddaru erbyn Extendoffice 20160506
Dim xStr Fel Llinyn
Dim xSum Fel Dwbl
Dim xCell Fel Ystod
xSum = SumCellId
Ar gyfer Pob xCell Mewn Ystod Darnau Arian
Os Na (xSum / xCell < 1) Yna
xStr = xStr & " 1 o " &xCell & " "
xSum = xSum - xCell
Gorffennwch Os
Digwyddiadau
GetCombination = xStr
Swyddogaeth End
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Helo mae'n rhoi gwall enw amwys i mi ar gyfer y cod vba
unrhyw help achos dwi'n gwybod dim byd yn VBA
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Helo Ram, mae hyn yn gweithio'n iawn ond nid yw'n rhoi'r swm gwirioneddol.
EX: os oes gen i 23,34,25,28,10,17&12 a bod gen i swm o 80 (sef y swm o 23,28,17 a 12), mae angen cod vba arnaf a all ddod o hyd i'r cyfuniad hwn (swm o 23,28,17 , 12, XNUMX & XNUMX) A allwch chi fy helpu gyda hyn?
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Helo,
Diolch yn fawr am wybodaeth;
Sut i ddod o hyd i'r cyfuniadau mwyaf bras os nad oes union werth.
Diolch yn fawr,
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Helo,
Diolch mae hyn yn dda iawn,
Sut i ddod o hyd i'r cyfuniadau mwyaf bras os nad oes union werth.
Diolch yn fawr
Lleihawyd y sylw hwn gan y safonwr ar y wefan
A fydd y datryswr adio ddim yn gweithio os oes rhifau negatif yn y rhestr neu os yw gwerth y rhif yn 0? Rwy'n ceisio dod o hyd i swm o rifau mewn rhestr sy'n cyfateb i sero gyda rhai rhifau yn negyddol ac yn gadarnhaol, ond nid yw'r datryswr yn gweithio. Newidiais rifau cwpl ar fy rhestr i brofi i wneud yn siŵr fy mod yn dilyn y camau yn gywir ac fe weithiodd ar gyfer y prawf. Rhowch wybod os oes ffordd i ddatrys gyda rhifau negyddol a chadarnhaol i ddod o hyd i werth 0.
L     a
Lleihawyd y sylw hwn gan y safonwr ar y wefan
a gawsoch chi ateb erioed neu a wnaethoch chi ddod o hyd i ffordd o wneud hyn?
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Helo,
Os oes rhifau positif a negyddol yn y golofn, rwy'n argymell eich bod chi'n cymhwyso'r Kutools ar gyfer Excel's Gwneud i fyny nodwedd rhif, gall ddatrys eich problem yn gyflym ac yn hawdd.

Gallwch chi lawrlwytho Kutools ar gyfer Excel a threial am ddim 60 diwrnod. Ceisiwch!
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Mae gen i 1162 o gelloedd i ddarganfod rhif x. Mae Excel yn dweud wrthyf fod gormod o gelloedd newidiol. Set ddata fach iawn! Unrhyw awgrymiadau? Diolch!
Nid oes unrhyw sylwadau wedi'u postio yma eto
Llwytho mwy o
Gadewch eich sylwadau
Postio fel Gwestai
×
Graddiwch y swydd hon:
0   Cymeriadau
Lleoliadau a Awgrymir