การค้นหาชุดตัวเลขที่เป็นไปได้ทั้งหมดภายในรายการที่รวมกันเป็นจำนวนเฉพาะถือเป็นความท้าทายที่ผู้ใช้ Excel จำนวนมากอาจเผชิญ ไม่ว่าจะเพื่อวัตถุประสงค์ด้านงบประมาณ การวางแผน หรือการวิเคราะห์ข้อมูล

ในตัวอย่างนี้ เรามีรายการตัวเลข และมีวัตถุประสงค์เพื่อระบุว่าชุดค่าผสมใดจากรายการนี้รวมกันได้ไม่เกิน 480 ภาพหน้าจอที่ให้ไว้แสดงให้เห็นว่ามีกลุ่มชุดค่าผสมที่เป็นไปได้ห้ากลุ่มที่จะบรรลุผลรวมนี้ รวมถึงชุดค่าผสมเช่น 300+120 +60, 250+120+60+50 และอื่นๆ ในบทความนี้ เราจะสำรวจวิธีการต่างๆ เพื่อระบุชุดค่าผสมเฉพาะของตัวเลขภายในรายการที่รวมค่าที่กำหนดใน Excel

ค้นหาการรวมกันของตัวเลขเท่ากับผลรวมที่กำหนดด้วยฟังก์ชัน Solver

รับการรวมกันของตัวเลขทั้งหมดเท่ากับผลรวมที่กำหนด

• โดยใช้ User Defined Function
• โดยคุณสมบัติอัจฉริยะ – Kutools สำหรับ Excel

รับชุดตัวเลขทั้งหมดที่มีผลรวมในช่วงด้วยโค้ด VBA

ค้นหาการรวมเซลล์ที่เท่ากับผลรวมที่กำหนดด้วยฟังก์ชัน Solver

การเจาะลึกเข้าไปใน Excel เพื่อค้นหาชุดค่าผสมของเซลล์ที่รวมกันเป็นจำนวนเฉพาะอาจดูยุ่งยาก แต่ Solver Add-in ทำให้มันเป็นเรื่องง่าย เราจะแนะนำคุณตลอดขั้นตอนง่ายๆ ในการตั้งค่า Solver และค้นหาชุดเซลล์ที่เหมาะสม ทำให้สิ่งที่ดูเหมือนเป็นงานที่ซับซ้อนตรงไปตรงมาและทำได้

ขั้นตอนที่ 1: เปิดใช้งาน Add-in ของ Solver

1. กรุณาไปที่ เนื้อไม่มีมัน > Optionsใน ตัวเลือก Excel คลิกตกลง Add-Ins จากบานหน้าต่างด้านซ้าย จากนั้นให้คลิก Go ปุ่ม. ดูภาพหน้าจอ:
   
2. จากนั้น Add-Ins กล่องโต้ตอบปรากฏขึ้น ให้ตรวจสอบ โปรแกรม Solver Add-in แล้วคลิก OK เพื่อติดตั้ง Add-in นี้ให้สำเร็จ
   

ขั้นตอนที่ 2: ใส่สูตร

หลังจากเปิดใช้งาน Add-in ของ Solver คุณจะต้องป้อนสูตรนี้ลงในเซลล์ B11:

=SUMPRODUCT(B2:B10,A2:A10)

ขั้นตอนที่ 3: กำหนดค่าและเรียกใช้ Solver เพื่อให้ได้ผลลัพธ์

1. คลิก ข้อมูล > ตัวแก้ ไป พารามิเตอร์ Solver ในกล่องโต้ตอบโปรดดำเนินการดังต่อไปนี้:
   • (1. ) คลิก เพื่อเลือกเซลล์ B11 โดยที่สูตรของคุณอยู่จาก กำหนดวัตถุประสงค์ มาตรา;
   • (2. ) จากนั้นในไฟล์ ไปยัง เลือก มูลค่าของและป้อนค่าเป้าหมายของคุณ 480 ตามที่คุณต้องการ
   • (3. ) ภายใต้ โดยการเปลี่ยนเซลล์แปรผัน โปรดคลิก ปุ่มเพื่อเลือกช่วงเซลล์ B2: B10 ที่จะทำเครื่องหมายหมายเลขที่เกี่ยวข้องของคุณ
   • (4. ) จากนั้นคลิก เพิ่ม ปุ่ม
   • 
2. จากนั้น เพิ่มข้อ จำกัด กล่องโต้ตอบจะปรากฏขึ้น คลิก ปุ่มเพื่อเลือกช่วงเซลล์ B2: B10และเลือก ถัง จากรายการแบบเลื่อนลง ในที่สุดคลิก OK ปุ่ม. ดูภาพหน้าจอ:
   
3. ตัว Vortex Indicator ได้ถูกนำเสนอลงในนิตยสาร พารามิเตอร์ Solver คลิกที่ แก้ ปุ่มบางนาทีต่อมา a ผลการแก้ปัญหา กล่องโต้ตอบจะปรากฏขึ้น และคุณจะเห็นการรวมกันของเซลล์ซึ่งเท่ากับผลรวมที่กำหนด 480 ซึ่งถูกทำเครื่องหมายเป็น 1 ในคอลัมน์ B ใน ผลการแก้ปัญหา โปรดเลือก ให้โซลูชัน Solver แล้วคลิก OK เพื่อออกจากกล่องโต้ตอบ ดูภาพหน้าจอ:
   

รับการรวมกันของตัวเลขทั้งหมดเท่ากับผลรวมที่กำหนด

การสำรวจความสามารถเชิงลึกของ Excel ช่วยให้คุณค้นหาชุดตัวเลขทุกชุดที่ตรงกับผลรวมที่ระบุ และง่ายกว่าที่คุณคิด ในส่วนนี้จะแสดงสองวิธีในการค้นหาชุดค่าผสมของตัวเลขทั้งหมดที่เท่ากับผลรวมที่ระบุ

รับการรวมกันของตัวเลขทั้งหมดเท่ากับผลรวมที่กำหนดด้วยฟังก์ชันที่กำหนดโดยผู้ใช้

เพื่อค้นหาการรวมกันของตัวเลขที่เป็นไปได้ทั้งหมดจากชุดเฉพาะที่รวมกันถึงค่าที่กำหนด ฟังก์ชันแบบกำหนดเองที่แสดงด้านล่างนี้ทำหน้าที่เป็นเครื่องมือที่มีประสิทธิภาพ

ขั้นตอนที่ 1: เปิดตัวแก้ไขโมดูล VBA และคัดลอกโค้ด

1. ค้างไว้ ALT + F11 ใน Excel และจะเปิดไฟล์ Microsoft Visual Basic สำหรับแอปพลิเคชัน หน้าต่าง
2. คลิก สิ่งที่ใส่เข้าไป > โมดูลและวางรหัสต่อไปนี้ในหน้าต่างโมดูล
   รหัส VBA: รับชุดตัวเลขทั้งหมดเท่ากับผลรวมที่กำหนด

   Public Function MakeupANumber(xNumbers As Range, xCount As Long)
   'updateby Extendoffice
       Dim arrNumbers() As Long
       Dim arrRes() As String
       Dim ArrTemp() As Long
       Dim xIndex As Long
       Dim rg As Range
   
       MakeupANumber = ""
       
       If xNumbers.CountLarge = 0 Then Exit Function
       ReDim arrNumbers(xNumbers.CountLarge - 1)
       
       xIndex = 0
       For Each rg In xNumbers
           If IsNumeric(rg.Value) Then
               arrNumbers(xIndex) = CLng(rg.Value)
               xIndex = xIndex + 1
           End If
       Next rg
       If xIndex = 0 Then Exit Function
       
       ReDim Preserve arrNumbers(0 To xIndex - 1)
       ReDim arrRes(0)
       
       Call Combinations(arrNumbers, xCount, ArrTemp(), arrRes())
       ReDim Preserve arrRes(0 To UBound(arrRes) - 1)
       MakeupANumber = arrRes
   End Function
   
   Private Sub Combinations(Numbers() As Long, Count As Long, ArrTemp() As Long, ByRef arrRes() As String)
   
       Dim currentSum As Long, i As Long, j As Long, k As Long, num As Long, indRes As Long
       Dim remainingNumbers() As Long, newCombination() As Long
       
       currentSum = 0
       If (Not Not ArrTemp) <> 0 Then
           For i = LBound(ArrTemp) To UBound(ArrTemp)
               currentSum = currentSum + ArrTemp(i)
           Next i
       End If
    
       If currentSum = Count Then
           indRes = UBound(arrRes)
           ReDim Preserve arrRes(0 To indRes + 1)
           
           arrRes(indRes) = ArrTemp(0)
           For i = LBound(ArrTemp) + 1 To UBound(ArrTemp)
               arrRes(indRes) = arrRes(indRes) & "," & ArrTemp(i)
           Next i
       End If
       
       If currentSum > Count Then Exit Sub
       If (Not Not Numbers) = 0 Then Exit Sub
       
       For i = 0 To UBound(Numbers)
           Erase remainingNumbers()
           num = Numbers(i)
           For j = i + 1 To UBound(Numbers)
               If (Not Not remainingNumbers) <> 0 Then
                   ReDim Preserve remainingNumbers(0 To UBound(remainingNumbers) + 1)
               Else
                   ReDim Preserve remainingNumbers(0 To 0)
               End If
               remainingNumbers(UBound(remainingNumbers)) = Numbers(j)
               
           Next j
           Erase newCombination()
   
           If (Not Not ArrTemp) <> 0 Then
               For k = 0 To UBound(ArrTemp)
                   If (Not Not newCombination) <> 0 Then
                       ReDim Preserve newCombination(0 To UBound(newCombination) + 1)
                   Else
                       ReDim Preserve newCombination(0 To 0)
                   End If
                   newCombination(UBound(newCombination)) = ArrTemp(k)
   
               Next k
           End If
           
           If (Not Not newCombination) <> 0 Then
               ReDim Preserve newCombination(0 To UBound(newCombination) + 1)
           Else
               ReDim Preserve newCombination(0 To 0)
           End If
           
           newCombination(UBound(newCombination)) = num
   
           Combinations remainingNumbers, Count, newCombination, arrRes
       Next i
   
   End Sub
   

ขั้นตอนที่ 2: ป้อนสูตรที่กำหนดเองเพื่อให้ได้ผลลัพธ์

หลังจากวางโค้ดแล้ว ให้ปิดหน้าต่างโค้ดเพื่อกลับไปที่เวิร์กชีต ป้อนสูตรต่อไปนี้ลงในเซลล์ว่างเพื่อแสดงผลลัพธ์ จากนั้นกด เข้าสู่ กุญแจสำคัญในการรับชุดค่าผสมทั้งหมด ดูภาพหน้าจอ:

=MakeupANumber(A2:A10,B2)

ปลาย: หากคุณต้องการแสดงรายการผลลัพธ์ชุดค่าผสมในแนวตั้งในคอลัมน์ โปรดใช้สูตรต่อไปนี้:

=TRANSPOSE(MakeupANumber(A2:A10,B2))

รับการผสมตัวเลขทั้งหมดเท่ากับผลรวมที่กำหนดพร้อมฟีเจอร์อันทรงพลัง

ด้วยข้อจำกัดของฟังก์ชันดังกล่าว เราขอแนะนำโซลูชันที่รวดเร็วและครอบคลุม: Kutools for Excel's Make up a Number คุณลักษณะซึ่งเข้ากันได้กับ Excel เวอร์ชันใดก็ได้ ทางเลือกนี้สามารถจัดการกับจำนวนบวก ทศนิยม และจำนวนลบได้อย่างมีประสิทธิภาพ ด้วยฟีเจอร์นี้ คุณจะได้รับชุดค่าผสมทั้งหมดที่เท่ากับผลรวมที่กำหนดได้อย่างรวดเร็ว

1. คลิก Kutools > คอนเทนต์ > สร้างตัวเลขดูภาพหน้าจอ:
   
2. จากนั้นใน สร้างตัวเลข โปรดคลิก เพื่อเลือกรายการหมายเลขที่คุณต้องการใช้จากปุ่ม แหล่งข้อมูลจากนั้นป้อนจำนวนทั้งหมดลงในไฟล์ รวม กล่องข้อความ. สุดท้ายคลิก OK ปุ่มดูภาพหน้าจอ:
   
3. จากนั้นกล่องพร้อมท์จะปรากฏขึ้นเพื่อเตือนให้คุณเลือกเซลล์เพื่อค้นหาผลลัพธ์ จากนั้นคลิก OKดูภาพหน้าจอ:
   
4. และตอนนี้ชุดค่าผสมทั้งหมดที่เท่ากับจำนวนที่ระบุนั้นได้แสดงไว้ตามภาพหน้าจอด้านล่างนี้:
   

รับชุดตัวเลขทั้งหมดที่มีผลรวมในช่วงด้วยโค้ด VBA

บางครั้ง คุณอาจพบว่าตัวเองอยู่ในสถานการณ์ที่คุณต้องระบุชุดตัวเลขที่เป็นไปได้ทั้งหมดที่รวมกันได้เป็นผลรวมภายในช่วงที่ระบุ ตัวอย่างเช่น คุณอาจต้องการค้นหาทุกกลุ่มที่เป็นไปได้ของตัวเลขโดยที่ผลรวมอยู่ระหว่าง 470 ถึง 480

การค้นพบชุดตัวเลขที่เป็นไปได้ทั้งหมดซึ่งรวมเป็นค่าภายในช่วงที่ระบุแสดงถึงความท้าทายที่น่าสนใจและใช้งานได้จริงใน Excel ส่วนนี้จะแนะนำโค้ด VBA สำหรับการแก้ปัญหานี้

ขั้นตอนที่ 1: เปิดตัวแก้ไขโมดูล VBA และคัดลอกโค้ด

1. ค้างไว้ ALT + F11 ใน Excel และจะเปิดไฟล์ Microsoft Visual Basic สำหรับแอปพลิเคชัน หน้าต่าง
2. คลิก สิ่งที่ใส่เข้าไป > โมดูลและวางรหัสต่อไปนี้ในหน้าต่างโมดูล
   รหัส VBA: รับชุดตัวเลขทั้งหมดที่รวมเป็นช่วงที่ระบุ

   Sub Getall_combinations()
   'Updateby Extendoffice
       Dim xNumbers As Variant
       Dim Output As Collection
       Dim rngSelection As Range
       Dim OutputCell As Range
       Dim LowLimit As Long, HiLimit As Long
       Dim i As Long, j As Long
       Dim TotalCombinations As Long
       Dim CombTotal As Double
       Set Output = New Collection
       On Error Resume Next
       Set rngSelection = Application.InputBox("Select the range of numbers:", "Kutools for Excel", Type:=8)
       If rngSelection Is Nothing Then
           MsgBox "No range selected. Exiting macro.", vbInformation, "Kutools for Excel"
           Exit Sub
       End If
       On Error GoTo 0
       xNumbers = rngSelection.Value
       LowLimit = Application.InputBox("Select or enter the low limit number:", "Kutools for Excel", Type:=1)
       HiLimit = Application.InputBox("Select or enter the high limit number:", "Kutools for Excel", Type:=1)
       On Error Resume Next
       Set OutputCell = Application.InputBox("Select the first cell for output:", "Kutools for Excel", Type:=8)
       If OutputCell Is Nothing Then
           MsgBox "No output cell selected. Exiting macro.", vbInformation, "Kutools for Excel"
           Exit Sub
       End If
       On Error GoTo 0
       TotalCombinations = 2 ^ (UBound(xNumbers, 1) * UBound(xNumbers, 2))
       For i = 1 To TotalCombinations - 1
           Dim tempArr() As Double
           ReDim tempArr(1 To UBound(xNumbers, 1) * UBound(xNumbers, 2))
           CombTotal = 0
           Dim k As Long: k = 0
           
           For j = 1 To UBound(xNumbers, 1)
               If i And (2 ^ (j - 1)) Then
                   k = k + 1
                   tempArr(k) = xNumbers(j, 1)
                   CombTotal = CombTotal + xNumbers(j, 1)
               End If
           Next j
           If CombTotal >= LowLimit And CombTotal <= HiLimit Then
               ReDim Preserve tempArr(1 To k)
               Output.Add tempArr
           End If
       Next i
       Dim rowOffset As Long
       rowOffset = 0
       Dim item As Variant
       For Each item In Output
           For j = 1 To UBound(item)
               OutputCell.Offset(rowOffset, j - 1).Value = item(j)
           Next j
           rowOffset = rowOffset + 1
       Next item
   End Sub
   
   
   

ขั้นตอนที่ 2: ดำเนินการโค้ด

1. หลังจากวางรหัสแล้วให้กด F5 เพื่อเรียกใช้โค้ดนี้ ในกล่องโต้ตอบแรกที่โผล่ออกมา ให้เลือกช่วงตัวเลขที่คุณต้องการใช้ แล้วคลิก OK. ดูภาพหน้าจอ:
   
2. ในกล่องพร้อมท์ที่สอง ให้เลือกหรือพิมพ์ตัวเลขขีดจำกัดต่ำ แล้วคลิก OK. ดูภาพหน้าจอ:
   
3. ในกล่องพร้อมท์ที่สาม ให้เลือกหรือพิมพ์หมายเลขขีดจำกัดสูง แล้วคลิก OK. ดูภาพหน้าจอ:
   
4. ในกล่องพร้อมท์สุดท้าย ให้เลือกเซลล์เอาต์พุต ซึ่งเป็นตำแหน่งที่จะเริ่มแสดงผลลัพธ์ จากนั้นคลิก OK. ดูภาพหน้าจอ:
   

ผล

ตอนนี้ ชุดค่าผสมที่มีคุณสมบัติเหมาะสมแต่ละรายการจะแสดงรายการเป็นแถวต่อเนื่องกันในแผ่นงาน โดยเริ่มจากเซลล์ผลลัพธ์ที่คุณเลือก

Excel มีวิธีการค้นหากลุ่มตัวเลขที่รวมกันเป็นจำนวนรวมได้หลายวิธี โดยแต่ละวิธีทำงานแตกต่างกัน ดังนั้นคุณจึงสามารถเลือกได้หลายวิธีโดยพิจารณาจากความคุ้นเคยกับ Excel และสิ่งที่คุณต้องการสำหรับโปรเจ็กต์ของคุณ หากคุณสนใจที่จะสำรวจเคล็ดลับและคำแนะนำเพิ่มเติมเกี่ยวกับ Excel เว็บไซต์ของเรามีบทช่วยสอนหลายพันรายการ คลิกที่นี่เพื่อเข้าถึงพวกเขา. ขอขอบคุณที่อ่าน และเราหวังว่าจะให้ข้อมูลที่เป็นประโยชน์เพิ่มเติมแก่คุณในอนาคต!

บทความที่เกี่ยวข้อง: