Ինչպե՞ս գտնել Excel- ում տրված գումարին հավասար բոլոր զուգորդումները:

Օրինակ, ես ունեմ թվերի հետևյալ ցուցակը, և այժմ ուզում եմ իմանալ, թե ցուցակում թվերի որ համադրություն է կազմում 480-ը, ցույց տրված հետևյալ նկարում, տեսնում եք, որ հնարավոր համակցությունների հինգ խումբ կա, որոնք հավասար են մինչև 480, օրինակ `300 + 60 + 120, 300 + 60 + 40 + 80 և այլն: Այս հոդվածում ես կխոսեմ որոշ մեթոդների մասին` պարզելու, թե որ բջիջներն են Excel- ում որոշակի արժեք կազմում:

• Գտեք բջիջների համադրություն, որը տրված գումարին հավասար է բանաձևերի հետ
• Գտեք բջիջների համադրություն, որը հավասար է տրված գումարին `Solver հավելվածին
• Գտեք բջիջների համադրությունը, որը հավասար է տրված գումարին ՝ օգտագործողի կողմից սահմանված գործառույթի հետ
• Գտեք բոլոր զուգորդումները, որոնք հավասար են տրված գումարին, զարմանալի հատկանիշով

Գտեք բջիջների համադրություն, որը տրված գումարին հավասար է բանաձևերի հետ

Նախ, անհրաժեշտ է ստեղծել մի շարք տիրույթների անուններ, այնուհետև կիրառել զանգվածի բանաձև ՝ գտնելու այն բջիջները, որոնք գումարվում են նպատակային արժեքի համար, կատարեք հետևյալ քայլ առ քայլ.

1, Ընտրեք համարների ցուցակը և այս ցուցակը սահմանեք տիրույթի անունով-- Լեռնաշղթա 1 մեջ Անունը Boxեւ սեղմեք Մտնել ստեղնը սահմանված տիրույթի անունն ավարտելու համար տե՛ս նկարը.

2, Թվերի ցուցակի համար միջակայքի անուն սահմանելուց հետո, ապա ձեզ հարկավոր է ստեղծել հետագա երկու տիրույթի անուններ Անունը կառավարիչ տուփը, սեղմեք Բանաձեւեր > Անունը կառավարիչԷ, ապա Անունը կառավարիչ երկխոսության վանդակում, սեղմեք նոր կոճակը, տես սքրինշոթերը.

3, Դուրս ցցված Նոր անուն երկխոսություն, մուտքագրեք անուն List1- ը մեջ անուն, ազգանուն դաշտը և մուտքագրել այս բանաձևը = ROW (INDIRECT ("1:" & ROWS (Range1))) (Լեռնաշղթա 1 միջակայքի անունն է, որը դուք ստեղծել եք քայլ 1-ում) մեջ Վերաբերում է դաշտ, տես նկարի նկարը.

4: Սեղմեք OK վերադառնալու համար Անունը կառավարիչ երկխոսություն, ապա շարունակեք կտտացնել նոր կոճակը ՝ մեկ այլ տիրույթի անուն ստեղծելու համար Նոր անուն երկխոսություն, մուտքագրեք անուն List2- ը մեջ անուն, ազգանուն դաշտը և մուտքագրել այս բանաձևը = ROW (ԱՆՀԱՄԱՏ ("1:" & 2 ^ ROWS (Range1))) (Լեռնաշղթա 1 միջակայքի անունն է, որը դուք ստեղծել եք քայլ 1-ում) մեջ Վերաբերում է դաշտ, տես նկարի նկարը.

5, Լեռնաշղթայի անունները ստեղծելուց հետո խնդրում ենք կիրառել զանգվածի հետևյալ բանաձևը 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","")եւ սեղմեք Shift + Ctrl + Enter ստեղները միասին, այնուհետև լրացնելու բռնակը ներքև քաշեք դեպի B8 բջիջը, որը ցուցակի վերջին համարն է, և B սյունակում կարող եք տեսնել այն թվերը, որոնց ընդհանուր գումարը 480 է, տես նկարի նկարը.

Գտեք բջիջների համադրություն, որը հավասար է տրված գումարին `Solver հավելվածին

Եթե ​​շփոթված եք վերը նշված մեթոդի հետ, Excel- ը պարունակում է ա Լուծիչի հավելում առանձնահատկությունը, օգտագործելով այս հավելումը, կարող եք նաև որոշել այն թվերը, որոնց ընդհանուր գումարը հավասար է տվյալ արժեքի:

1. Նախ, անհրաժեշտ է ակտիվացնել սա solver ավելացրուխնդրում եմ գնա Ֆայլ > ԸնտրանքներԷ, ապա Excel ընտրանքներ երկխոսության վանդակում, սեղմեք Add-Ins- ը ձախ պատուհանից, ապա կտտացրեք Լուծիչի հավելում - ից Պասիվ կիրառման հավելումներ բաժին, տես նկարի նկարը.

2. Այնուհետեւ կտտացրեք Go կոճակը մուտք գործելու համար Add-Ins- ը երկխոսություն, ստուգում Լուծիչի հավելում տարբերակը և սեղմեք OK այս հավելումը հաջողությամբ տեղադրելու համար:

3, Solver հավելումն ակտիվացնելուց հետո անհրաժեշտ է մուտքագրել այս բանաձևը B9 բջիջում. = SUMPRODUCT (B2: B9, A2: A9)(B2: B9 ձեր համարների ցուցակին կից դատարկ սյունի բջիջներ է, և A2: A9 ձեր համարների ցուցակն է: ), և սեղմեք Մտնել ստեղնը, տես նկարի նկարը.

4. Այնուհետեւ կտտացրեք Ամսաթիվ > solver գնալու համար Լուծիչի պարամետր երկխոսության տուփ, երկխոսության մեջ, խնդրում ենք կատարել հետևյալ գործողությունները.

(1.) Կտտացրեք  բջիջը ընտրելու կոճակը B10 որտեղից է ձեր բանաձևը Նպատակ դնել Բաժին;

(2.) Հետո Դեպի բաժին ընտրեք Արժեքըև մուտքագրեք ձեր նպատակային արժեքը 480 ինչպես պետք է;

(3.) Համաձայն Փոփոխական բջիջները փոխելով բաժինը, սեղմեք կոճակը ՝ բջիջների տիրույթը ընտրելու համար B2: B9 որտեղ նշվեն ձեր համապատասխան համարները:

5. Եվ հետո կտտացրեք Ավելացնել կոճակը, գնալու համար Ավելացնել սահմանափակում երկխոսության վանդակում, սեղմեք կոճակը ՝ բջիջների տիրույթը ընտրելու համար B2: B9եւ ընտրեք ամբար բացվող ցուցակից տե՛ս նկարը.

6: Սեղմեք OK վերադառնալ Լուծիչի պարամետր երկխոսություն, ապա կտտացրեք Լուծել կոճակը, մի քանի րոպե անց, ա Լուծողի արդյունքներ երկխոսության տուփը դուրս է գալիս, և դուք կարող եք տեսնել բջիջների համադրությունը, որոնք հավասար են տրված 480 գումարի, նշվում են որպես 1. Լուծողի արդյունքներ ընտրեք Պահեք Solver Solution տարբերակը և սեղմեք OK երկխոսությունից դուրս գալու համար: Տեսեք,

Գտեք բջիջների համադրությունը, որը հավասար է տրված գումարին ՝ օգտագործողի կողմից սահմանված գործառույթի հետ

Առաջին երկու մեթոդները բոլորը բարդ են մեր Excel- ի օգտվողների մեծ մասի համար, այստեղ ես կարող եմ ստեղծել VBA կոդ այս աշխատանքը արագ և հեշտ լուծելու համար:

Resultիշտ արդյունք ստանալու համար նախ պետք է թվերի ցուցակը տեսակավորել ըստ նվազման կարգի: Եվ ապա արեք հետևյալ քայլերը.

1, Պահեք պահեք ALT + F11 բացել ստեղները Microsoft Visual Basic հավելվածների համար պատուհան.

2: Սեղմեք Տեղադրել > Մոդուլներ, և տեղադրեք հետևյալ կոդը Մոդուլներ Պատուհանը:

VBA կոդ. Գտեք բջիջների համադրությունը, որը հավասար է տրված գումարին.

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, Դրանից հետո պահեք և փակեք այս կոդի պատուհանը, ապա վերադառնաք աշխատանքային թերթ և մուտքագրեք այս բանաձևը = getcombination (A2: A9, C2) դատարկ խցիկի մեջ և սեղմել Մտնել բանալին, դուք կստանաք հետևյալ արդյունքը, որը ցույց է տալիս տրված գումարին հավասար թվով համակցված թվերը, տես նկարի նկարը.

Գտեք բոլոր զուգորդումները, որոնք հավասար են տրված գումարին, զարմանալի հատկանիշով

Միգուցե վերոհիշյալ բոլոր մեթոդները որոշ չափով դժվար են ձեզ համար, այստեղ ես կներկայացնեմ մի հզոր գործիք, Excel- ի համար նախատեսված գործիքներԻր Կազմիր համար հատկություն, դուք կարող եք արագորեն ստանալ բոլոր համադրությունները, որոնք հավասար են տվյալ գումարին:

Տեղադրելուց հետո Excel- ի համար նախատեսված գործիքներԽնդրում եմ արեք հետևյալ կերպ

1: Սեղմեք Կուտոլս > Պարունակություն > Կազմիր համար, տես նկարի նկարը.

2, Հետո, Կազմիր թիվ երկխոսության տուփ, սեղմեք կոճակը ընտրելու համարների ցուցակը, որը ցանկանում եք օգտագործել Data Source, ապա մուտքագրեք ընդհանուր թիվը մեջ Գումար տեքստային տուփ, տես նկարի նկարը.

3, Եվ հետո, կտտացրեք OK կոճակը, հուշում է պատուհանը, որը կհիշեցնի ձեզ ընտրել բջիջ արդյունքը գտնելու համար, տես նկարի նկարը.

4. Դրանից հետո կտտացրեք OK, և այժմ, բոլոր համակցությունները, որոնք հավասար են տվյալ թվին, ցուցադրվել են, ինչպես ցույց է տրված ստորև նշված էկրանի նկարը.

Կտտացրեք Excel- ի և անվճար փորձաշրջանի համար Kutools- ը ներբեռնելու համար:

Դեմո. Excel- ում գտեք բջիջների համադրություն, որը հավասար է տվյալ գումարին

---

Գրասենյակի արտադրողականության լավագույն գործիքները

Excel-ի համար Kutools-ը լուծում է ձեր խնդիրների մեծ մասը և բարձրացնում ձեր արտադրողականությունը 80%-ով

• Վերաօգտագործել: Արագ տեղադրեք բարդ բանաձևեր, գծապատկերներ և այն ամենը, ինչ դուք նախկինում օգտագործել եք. Ryածկագրել բջիջները գաղտնաբառով; Ստեղծեք փոստային ցուցակ և նամակներ ուղարկել ...
• Super Formula Bar (հեշտությամբ խմբագրեք տեքստի և բանաձևի բազմաթիվ տողեր); Ընթերցանության դասավորությունը (հեշտությամբ կարդալ և խմբագրել մեծ թվով բջիջներ); Տեղադրել ֆիլտրացված տիրույթում...
• Միաձուլել բջիջները / տողերը / սյունակները առանց տվյալների կորստի. Պառակտված բջիջների պարունակությունը; Միավորել կրկնօրինակ տողերը / սյունակները... Կանխել կրկնօրինակ բջիջները; Համեմատեք միջակայքերը...
• Ընտրեք Կրկնօրինակ կամ Եզակի Շարքեր; Ընտրեք դատարկ շարքեր (բոլոր բջիջները դատարկ են); Super Find և Fuzzy Find շատ աշխատանքային գրքույկներում; Պատահական ընտրություն ...
• Actշգրիտ պատճեն Բազմաթիվ բջիջներ ՝ առանց բանաձևի հղումը փոխելու; Ավտոմատ ստեղծեք հղումներ դեպի մի քանի թերթեր; Տեղադրեք փամփուշտներ, Տուփեր և ավելին ...
• Քաղվածք տեքստ, Տեքստ ավելացնել, հեռացնել ըստ դիրքի, Հեռացնել տարածությունը; Ստեղծել և տպել էջային ենթագոտիներ; Փոխարկել բջիջների բովանդակության և մեկնաբանությունների միջև...
• Սուպեր զտիչ (պահպանել և կիրառել ֆիլտրի սխեմաները այլ թերթերի վրա); Ընդլայնված տեսակավորում ըստ ամիս / շաբաթ / օր, հաճախականություն և ավելին; Հատուկ զտիչ համարձակ, շեղատառով ...
• Միավորել աշխատանքային տետրերը և աշխատանքային թերթերը; Միավորել աղյուսակները ՝ հիմնված հիմնական սյունակների վրա; Տվյալները բաժանեք մի քանի թերթերի; Խմբաքանակի փոխակերպում xls, xlsx և PDF...
• Ավելի քան 300 հզոր հատկություններ. Աջակցում է Office / Excel 2007-2021 և 365: Աջակցում է բոլոր լեզուներին: Հեշտ տեղակայում ձեր ձեռնարկությունում կամ կազմակերպությունում: Ամբողջական հնարավորություններ 30-օրյա անվճար փորձարկում: 60-օրյա գումարի վերադարձի երաշխիք:

Կարդալ ավելին... Անվճար ներբեռնում... Գնեք ... 

---

Office Tab- ը Tabbed ինտերֆեյսը բերում է Office, և ձեր աշխատանքը շատ ավելի դյուրին դարձրեք

• Միացնել ներդիրներով խմբագրումը և ընթերցումը Word, Excel, PowerPoint- ով, Հրատարակիչ, Access, Visio և Project:
• Բացեք և ստեղծեք բազմաթիվ փաստաթղթեր նույն պատուհանի նոր ներդիրներում, այլ ոչ թե նոր պատուհաններում:
• Բարձրացնում է ձեր արտադրողականությունը 50%-ով և նվազեցնում մկնիկի հարյուրավոր սեղմումները ձեզ համար ամեն օր:

Կարդալ ավելին... Անվճար ներբեռնում... Գնեք ...