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

Làm thế nào để tìm tất cả các kết hợp bằng một tổng đã cho trong Excel?

Ví dụ: tôi có danh sách các số sau và bây giờ, tôi muốn biết tổ hợp số nào trong danh sách có tổng số là 480, trong ảnh chụp màn hình sau đây, bạn có thể thấy có năm nhóm kết hợp có thể cộng lại bằng nhau. đến 480, chẳng hạn như 300 + 60 + 120, 300 + 60 + 40 + 80, v.v. Bài viết này, tôi sẽ nói về một số phương pháp để tìm các ô tính tổng thành một giá trị cụ thể trong Excel.


Tìm tổ hợp ô bằng một tổng đã cho bằng các công thức

Trước tiên, bạn cần tạo một số tên dải ô, sau đó áp dụng công thức mảng để tìm các ô tính tổng thành giá trị đích, vui lòng thực hiện theo từng bước sau:

1. Chọn danh sách số và xác định danh sách này một tên phạm vi-- Phạm vi1 vào Tên Box, và hãy nhấn đăng ký hạng mục thi để kết thúc tên phạm vi đã xác định, xem ảnh chụp màn hình:

2. Sau khi xác định tên dải ô cho danh sách số, bạn cần tạo thêm hai tên dải ô trong Người quản lý tên hộp, vui lòng nhấp vào Công thức > Người quản lý tên, Trong Người quản lý tên hộp thoại, nhấn Mới , xem ảnh chụp màn hình:

3. Trong cửa sổ bật ra Tên mới hộp thoại, nhập tên List1 vào Họ tên trường và nhập công thức này = ROW (INDIRECT ("1:" & ROWS (Phạm vi 1))) (Phạm vi1 là tên dải ô bạn đã tạo ở bước 1) vào Đề cập đến trường, xem ảnh chụp màn hình:

4. Nhấp chuột OK để trở về Người quản lý tên hộp thoại, sau đó tiếp tục nhấp vào Mới để tạo một tên dải ô khác, trong Tên mới hộp thoại, nhập tên List2 vào Họ tên trường và nhập công thức này = ROW (INDIRECT ("1:" & 2 ^ ROWS (Range1))) (Phạm vi1 là tên dải ô bạn đã tạo ở bước 1) vào Đề cập đến trường, xem ảnh chụp màn hình:

5. Sau khi tạo tên dải ô, vui lòng áp dụng công thức mảng sau vào ô 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",""), và hãy nhấn Shift + Ctrl + Nhập với nhau, sau đó kéo chốt điền xuống ô B8, số cuối cùng của danh sách và bạn có thể thấy các số có tổng số tiền là 480 được đánh dấu là X trong cột B, xem ảnh chụp màn hình:

  • Ghi chú:
  • Trong công thức dài ở trên: List1, List2Phạm vi1 là tên dải ô bạn đã tạo trong các bước trước đây, C2 là giá trị cụ thể mà bạn muốn các số được thêm vào.
  • Nếu nhiều kết hợp giá trị có tổng bằng giá trị cụ thể thì chỉ một kết hợp được liệt kê.

Tìm và liệt kê tất cả các kết hợp bằng một tổng cho trước một cách nhanh chóng và dễ dàng trong Excel

Kutools cho Excel's Tạo thành một con số tiện ích có thể giúp bạn tìm và liệt kê tất cả các kết hợp và các kết hợp cụ thể bằng một số tổng đã cho một cách nhanh chóng và dễ dàng. Nhấp để tải xuống Kutools cho Excel!

Kutools cho Excel: với nhiều phần bổ trợ Excel tiện dụng, dùng thử miễn phí không giới hạn số ngày. Tải xuống và dùng thử miễn phí ngay!


Tìm kết hợp các ô bằng một tổng đã cho bằng Trình bổ trợ Bộ giải

Nếu bạn nhầm lẫn với phương pháp trên, Excel sẽ chứa một Phần bổ trợ Solver bằng cách sử dụng bổ trợ này, bạn cũng có thể xác định các số mà tổng số tiền bằng một giá trị nhất định.

1. Trước tiên, bạn cần kích hoạt Người giải quyết add-in, xin hãy đến Tập tin > Các lựa chọn, Trong Tùy chọn Excel hộp thoại, nhấn Add-Ins từ ngăn bên trái, rồi bấm Phần bổ trợ Solver từ Các ứng dụng bổ sung không hoạt động , xem ảnh chụp màn hình:

2. Sau đó nhấn vào Go để vào Add-Ins hộp thoại, kiểm tra Phần bổ trợ Solver và nhấp vào OK để cài đặt thành công bổ trợ này.

3. Sau khi kích hoạt phần bổ trợ Solver, bạn cần nhập công thức này vào ô B9: = SUMPRODUCT (B2: B9, A2: A9), (B2: B9 là một ô cột trống bên cạnh danh sách số của bạn và A2: A9 là danh sách số mà bạn sử dụng. ), và hãy nhấn đăng ký hạng mục thi chính, xem ảnh chụp màn hình:

4. Sau đó nhấn vào Ngày > Người giải quyết đi đến Tham số bộ giải , trong hộp thoại, vui lòng thực hiện các thao tác sau:

(1.) Nhấp vào  nút để chọn ô B10 nơi công thức của bạn từ Đặt mục tiêu phần;

(2.) Sau đó, trong Đến phần, chọn Giá trị củavà nhập giá trị mục tiêu của bạn 480 như bạn cần;

(3.) Dưới Bằng cách thay đổi các ô biến phần, vui lòng nhấp vào nút để chọn phạm vi ô B2: B9 nơi sẽ đánh dấu các số tương ứng của bạn.

5. Và sau đó nhấp vào Thêm nút để đi đến Thêm ràng buộc hộp thoại, nhấn nút để chọn phạm vi ô B2: B9, Và chọn bin từ danh sách thả xuống, xem ảnh chụp màn hình:

6. Nhấp chuột OK quay lại Tham số bộ giải hộp thoại, sau đó nhấp vào Giải quyết , vài phút sau, Giải kết quả hộp thoại hiện ra và bạn có thể thấy tổ hợp các ô bằng tổng 480 đã cho được đánh dấu là 1. Trong Giải kết quả hộp thoại, vui lòng chọn Giải pháp Keep Solver và nhấp vào OK để thoát khỏi hộp thoại. Xem ảnh chụp màn hình:

Chú thích: Phương thức này cũng chỉ có thể lấy một ô kết hợp nếu có nhiều hơn một tổ hợp giá trị có tổng bằng giá trị cụ thể.


Tìm tổ hợp ô bằng một tổng đã cho với Hàm do Người dùng Xác định

Hai phương pháp đầu tiên đều phức tạp đối với hầu hết người dùng Excel của chúng tôi, tại đây, tôi có thể tạo mã VBA để giải quyết công việc này một cách nhanh chóng và dễ dàng.

Để có kết quả chính xác, trước tiên bạn phải sắp xếp danh sách số theo thứ tự giảm dần. Và sau đó thực hiện theo các bước sau:

1. Giữ ALT + F11 phím để mở Microsoft Visual Basic cho các ứng dụng cửa sổ.

2. Nhấp chuột Chèn > Mô-đunvà dán mã sau vào Mô-đun Cửa sổ.

Mã VBA: Tìm tổ hợp ô bằng một tổng đã cho:

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. Sau đó, lưu và đóng cửa sổ mã này, sau đó quay lại trang tính và nhập công thức này = getcombination (A2: A9, C2) vào một ô trống và nhấn đăng ký hạng mục thi chính, bạn sẽ nhận được kết quả sau đây hiển thị các số kết hợp bằng một tổng đã cho, xem ảnh chụp màn hình:

  • Ghi chú:
  • Trong công thức trên, A2: A9 là phạm vi số, và C2 chứa giá trị mục tiêu mà bạn muốn bằng.
  • Nếu nhiều kết hợp giá trị có tổng bằng giá trị cụ thể thì chỉ một kết hợp được liệt kê.

Tìm tất cả các kết hợp bằng một tổng đã cho với một tính năng tuyệt vời

Có thể tất cả các phương pháp trên đều hơi khó đối với bạn, ở đây, tôi sẽ giới thiệu một công cụ mạnh mẽ, Kutools cho Excel, Với khả Tạo thành một con số , bạn có thể nhanh chóng nhận được tất cả các kết hợp bằng một tổng đã cho.

Lời khuyên:Để áp dụng điều này Tạo thành một con số , trước tiên, bạn nên tải xuống Kutools cho Excel, và sau đó áp dụng tính năng một cách nhanh chóng và dễ dàng.

Sau khi cài đặt Kutools cho Excel, hãy làm như sau:

1. Nhấp chuột Kutools > Nội dung > Tạo thành một con số, xem ảnh chụp màn hình:

2. Sau đó, trong Tạo một số hộp thoại, vui lòng nhấp vào để chọn danh sách số mà bạn muốn sử dụng từ Nguồn dữ liệu, rồi nhập tổng số vào Tổng hộp văn bản, xem ảnh chụp màn hình:

3. Và sau đó, nhấp vào OK , một hộp nhắc sẽ bật ra để nhắc bạn chọn một ô để định vị kết quả, xem ảnh chụp màn hình:

4. Sau đó, nhấp vào OKvà bây giờ, tất cả các kết hợp bằng với số đã cho đó đã được hiển thị như ảnh chụp màn hình bên dưới:

Nhấp để Tải xuống Kutools cho Excel và dùng thử miễn phí ngay!


Demo: Tìm tổ hợp ô bằng một tổng cho trước trong Excel


Các công cụ năng suất văn phòng tốt nhất

Kutools cho Excel giải quyết hầu hết các vấn đề của bạn và tăng 80% năng suất của bạn

  • Tái sử dụng: Chèn nhanh công thức phức tạp, biểu đồ và bất cứ thứ gì bạn đã sử dụng trước đây; Mã hóa ô với mật khẩu; Tạo danh sách gửi thư và gửi email ...
  • Thanh siêu công thức (dễ dàng chỉnh sửa nhiều dòng văn bản và công thức); Bố cục đọc (dễ dàng đọc và chỉnh sửa số lượng ô lớn); Dán vào Dải ô đã Lọchữu ích. Cảm ơn !
  • Hợp nhất các ô / hàng / cột mà không làm mất dữ liệu; Nội dung phân chia ô; Kết hợp các hàng / cột trùng lặp... Ngăn chặn các ô trùng lặp; So sánh các dãyhữu ích. Cảm ơn !
  • Chọn trùng lặp hoặc duy nhất Hàng; Chọn hàng trống (tất cả các ô đều trống); Tìm siêu và Tìm mờ trong Nhiều Sổ làm việc; Chọn ngẫu nhiên ...
  • Bản sao chính xác Nhiều ô mà không thay đổi tham chiếu công thức; Tự động tạo tài liệu tham khảo sang Nhiều Trang tính; Chèn Bullets, Hộp kiểm và hơn thế nữa ...
  • Trích xuất văn bản, Thêm Văn bản, Xóa theo Vị trí, Xóa không gian; Tạo và In Tổng số phân trang; Chuyển đổi giữa nội dung ô và nhận xéthữu ích. Cảm ơn !
  • Siêu lọc (lưu và áp dụng các lược đồ lọc cho các trang tính khác); Sắp xếp nâng cao theo tháng / tuần / ngày, tần suất và hơn thế nữa; Bộ lọc đặc biệt bằng cách in đậm, in nghiêng ...
  • Kết hợp Workbook và WorkSheets; Hợp nhất các bảng dựa trên các cột chính; Chia dữ liệu thành nhiều trang tính; Chuyển đổi hàng loạt xls, xlsx và PDFhữu ích. Cảm ơn !
  • Hơn 300 tính năng mạnh mẽ. Hỗ trợ Office / Excel 2007-2021 và 365. Hỗ trợ tất cả các ngôn ngữ. Dễ dàng triển khai trong doanh nghiệp hoặc tổ chức của bạn. Đầy đủ tính năng Dùng thử miễn phí 30 ngày. Bảo đảm hoàn lại tiền trong 60 ngày.
tab kte 201905

Tab Office mang lại giao diện Tab cho Office và giúp công việc của bạn trở nên dễ dàng hơn nhiều

  • Cho phép chỉnh sửa và đọc theo thẻ trong Word, Excel, PowerPoint, Publisher, Access, Visio và Project.
  • Mở và tạo nhiều tài liệu trong các tab mới của cùng một cửa sổ, thay vì trong các cửa sổ mới.
  • Tăng 50% năng suất của bạn và giảm hàng trăm cú nhấp chuột cho bạn mỗi ngày!
officetab dưới cùng
Nhận xét (47)
Chưa có xếp hạng. Hãy là người đầu tiên xếp hạng!
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Có cách nào để mở rộng phạm vi để nó bao gồm nhiều hơn 8 số? Ngoài ra, tôi không chắc chức năng này hoạt động như thế nào: "= ROW (INDIRECT (" 1: "& 2 ^ ROWS (Range1)))". Nếu tôi cố gắng mở rộng "Range1" vượt quá 15 hàng, tôi gặp lỗi #Ref. Nó hoạt động tuyệt vời chỉ với 8 con số, nhưng nếu bạn muốn bao gồm 50 con số hoặc thậm chí 100 con số thì sao.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Cùng một vấn đề
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Người đàn ông tuyệt vời !!! Người đàn ông tuyệt vời !!!
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Có cách nào để mở rộng phạm vi như Thom nói, lên đến 50 số, nhưng cũng chỉ có tổng sáu trong số các số trong phạm vi mà tổng đó thành tổng được chỉ định? Hiện tại, nó sẽ cung cấp tất cả các kết hợp có tổng số tổng được chỉ định. cảm ơn
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Tuyệt vời. Không thể làm cho công thức lớn hoạt động nhưng bổ trợ bộ giải hoạt động hoàn hảo. Đã tiết kiệm cho tôi rất nhiều công việc.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
nhưng nó mất quá nhiều thời gian
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Tôi tốt nhất là người mới bắt đầu học Excel. Tôi đã thử mọi thứ và nó không hoạt động. Tôi có thể làm gì sai?
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
làm thế nào nếu tôi cần nhiều hơn một kết hợp? Cảm ơn
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Chào. Phiên bản công thức cũng không phù hợp với tôi. Có cảm giác như bị hụt một bước. Tôi không thấy số được chỉ định trong ô C2 xuất hiện trong công thức ở đâu.

Cảm ơn
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Xin chào, Dori,


Không có công thức trong C2, nó chỉ là giá trị cụ thể mà bạn muốn các số được thêm vào.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Xin chào, tôi đã tải xuống Kutools nhưng không thể tìm thấy tất cả các kết hợp nhỏ hơn tổng số cụ thể.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Tôi có thể lấy ví dụ với Range1 để làm việc với phạm vi của tôi trong 12 hàng, nhưng khi tôi thay đổi phạm vi thành 42 hàng, nó không hoạt động. Tôi thậm chí đã khởi động lại toàn bộ quá trình với phiên bản 42 hàng và điều đó cũng không hoạt động. Có ý kiến ​​gì không?
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Xuất sắc!!!
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Bạn có thể tải lên excel?
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Macro không hoạt động nếu có nhiều hơn một giải pháp.
Ngoài ra, tôi đã không làm việc nếu tôi tìm thấy "0"
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Xin chào, Lorena,
Trước khi áp dụng mã VBA trên, trước tiên bạn phải sắp xếp danh sách số theo thứ tự giảm dần.
Thứ hai, mã không hoạt động chính xác để lấy tổng số 0.
Hy vọng nó có thể giúp bạn, cảm ơn bạn!
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Tôi đang cố gắng xác định sự pha trộn tốt nhất của sản phẩm và không chắc liệu đây có phải là cách tốt nhất để làm điều đó hay không. Tôi sử dụng nhiều nhất ba sản phẩm trong một sự pha trộn với 5 thông số kỹ thuật mỗi sản phẩm. Tất cả các thông số kỹ thuật là tuyến tính và có thể được tính trung bình khi kết hợp. Một hỗn hợp thường là 45,000lbs và mỗi mẻ là 30,000lbs. Hầu hết thời gian hỗn hợp của chúng tôi là 15 nghìn + 30 nghìn nhưng tôi muốn có thể tính toán cho các hỗn hợp bất thường bằng cách sử dụng gia số tất cả các cách xuống 2000 lbs.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Chào,

Vấn đề của tôi với công thức này là nó cung cấp cho tôi một giá trị đủ số lần để nhận được giá trị mục tiêu ..
Trong danh sách các giá trị khác nhau, có một số giá trị bằng nhau.

Ví dụ: tôi có 0,16 cho 3 lần (các giá trị đầu tiên trong danh sách) và công thức cho tôi câu trả lời rằng giá trị mục tiêu của tôi là 593 trên 0,16.

Tại sao nó không kết hợp các giá trị khác nhau để có được giá trị mục tiêu của tôi? Nó chỉ chọn một giá trị và cho biết giá trị đó sẽ là giá trị mục tiêu bao nhiêu lần.

Bất kỳ sự giúp đỡ hoặc ý tưởng?


Cảm ơn!
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
có ai biết cái này có hoạt động trên google sheet không
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Có, có một phần mở rộng tương tự như bộ giải của excel được gọi là "bộ giải"
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Có ai biết cách điều chỉnh chức năng VBA Getcombination để không được phép lặp lại không?

Ví dụ, đối với các số 1,2,3,4,5,13 nếu 14 phải đạt được hơn 1,13 là một nghiệm, không phải 14 của 1.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Hàm GetCombination (CoinsRange As Range, SumCellId As Double) As String
'cập nhật bởi Extendoffice 20160506
Làm mờ xStr dưới dạng chuỗi
Dim xSum As Double
Dim xCell As Range
xSum = SumCellId
Đối với mỗi xCell In CoinsRange
Nếu không (xSum / xCell <1) thì
xStr = xStr & "1 trong số" & xCell & ""
xSum = xSum - xCell
Cuối Nếu
Sau
GetCombination = xStr
Cuối Chức năng
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
xin chào, nó đang mang lại cho tôi lỗi tên lớn cho mã vba
bất kỳ trợ giúp nào vì tôi không biết gì trong VBA
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Xin chào Ram, điều này hoạt động tốt nhưng không cung cấp tổng số thực tế.
VÍ DỤ: nếu tôi có 23,34,25,28,10,17 & 12 và tôi có tổng là 80 (là tổng của 23,28,17 & 12), thì tôi cần mã vba có thể tìm thấy kết hợp này (tổng của 23,28,17 , 12 & XNUMX) Bạn có thể vui lòng giúp tôi điều này được không?
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Chào bạn,
Rất cám ơn thông tin;
Làm thế nào có thể tìm thấy các kết hợp gần đúng nhất nếu không có giá trị chính xác.
Rất cám ơn,
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Chào bạn,
Cảm ơn, điều này rất tốt,
Làm thế nào có thể tìm thấy các kết hợp gần đúng nhất nếu không có giá trị chính xác.
Rất cám ơn
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Phần bổ trợ của bộ giải sẽ không hoạt động nếu có số âm trong danh sách hoặc nếu giá trị của số là 0? Tôi đang cố gắng tìm tổng các số trong danh sách bằng 0 với một số số là số âm và số dương, nhưng trình giải không hoạt động. Tôi đã thay đổi một vài số trong danh sách của mình để kiểm tra để đảm bảo rằng tôi đã thực hiện đúng các bước và nó hoạt động cho bài kiểm tra. Xin cho biết cách giải với số âm và số dương để tìm giá trị XNUMX.
L     a
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
bạn đã bao giờ nhận được câu trả lời hay bạn đã tìm ra cách để làm điều này?
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Chào bạn,
Nếu có cả số dương và số âm trong cột, tôi khuyên bạn nên áp dụng Kutools cho Excel's Tạo thành một số, nó có thể giải quyết vấn đề của bạn một cách nhanh chóng và dễ dàng.

Bạn có thể tải xuống Kutools cho Excel và dùng thử miễn phí 60 ngày. Vui lòng thử!
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Tôi có 1162 ô để tìm số x. Excel cho tôi biết rằng có quá nhiều ô biến. Tập dữ liệu rất nhỏ! Bất kỳ đề xuất? Thanks!
Không có bình luận nào được đăng ở đây
Tải thêm
Để lại ý kiến ​​của bạn
Đăng với tư cách khách
×
Đánh giá bài viết này:
0   Nhân vật
Các vị trí được đề xuất

Kết nối với chúng tôi

Bản quyền © 2009 - www.extendoffice.com. | Đã đăng ký Bản quyền. cung cấp bởi ExtendOffice. | BẢN ĐỒ CHI NHÁNH
Microsoft và logo Office là các nhãn hiệu hoặc nhãn hiệu đã đăng ký của Microsoft Corporation tại Hoa Kỳ và / hoặc các quốc gia khác.
Được bảo vệ bởi Sectigo SSL