ไปยังเนื้อหาหลัก

วิธีสร้างชุดค่าผสมทั้งหมดของ 3 หรือหลายคอลัมน์ใน Excel

สมมติว่าฉันมีข้อมูล 3 คอลัมน์ตอนนี้ฉันต้องการสร้างหรือแสดงรายการชุดข้อมูลทั้งหมดใน 3 คอลัมน์นี้ตามภาพด้านล่างที่แสดง คุณมีวิธีการที่ดีในการแก้ปัญหานี้ใน Excel หรือไม่?

สร้างชุดค่าผสมทั้งหมดตามข้อมูล 3 คอลัมน์โดยใช้สูตร

สร้างชุดค่าผสมทั้งหมดตามข้อมูล 3 คอลัมน์หรือหลายคอลัมน์โดยใช้รหัส VBA

สร้างชุดค่าผสมทั้งหมดโดยใช้ข้อมูล 3 คอลัมน์หรือหลายคอลัมน์โดยใช้คุณสมบัติที่ยอดเยี่ยม


สร้างชุดค่าผสมทั้งหมดตามข้อมูล 3 คอลัมน์โดยใช้สูตร

สูตรยาวต่อไปนี้สามารถช่วยในการแสดงรายการชุดค่าผสมทั้งหมดของ 3 คอลัมน์ได้โปรดทำดังนี้:

1. โปรดคลิกเซลล์ที่จะแสดงผลลัพธ์จากนั้นคัดลอกและวางสูตรด้านล่างลงในนั้น:

=IFERROR(INDEX($A$2:$A$4,INT((ROW(1:1)-1)/((COUNTA($B$2:$B$6)*(COUNTA($C$2:$C$5)))))+1)&"-"&INDEX($B$2:$B$6,MOD(INT((ROW(1:1)-1)/COUNTA($C$2:$C$5)),COUNTA($B$2:$B$6))+1)&"-"&INDEX($C$2:$C$5,MOD((ROW(1:1)-1),COUNTA($C$2:$C$5))+1),"")

หมายเหตุ: ในสูตรนี้: A2: A4, B2: B6, C2: C5 คือช่วงข้อมูลที่คุณต้องการใช้

2. จากนั้นลากที่จับเติมลงไปที่เซลล์จนกว่าเซลล์ว่างจะปรากฏขึ้นซึ่งหมายความว่าชุดค่าผสมทั้งหมดของ 3 คอลัมน์ได้รับการระบุไว้ดูภาพหน้าจอ:


สร้างชุดค่าผสมทั้งหมดตามข้อมูล 3 คอลัมน์หรือหลายคอลัมน์โดยใช้รหัส VBA

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

1. กด ALT + F11 คีย์เพื่อเปิด Microsoft Visual Basic สำหรับแอปพลิเคชัน หน้าต่าง

2. คลิก สิ่งที่ใส่เข้าไป > โมดูลและวางรหัสต่อไปนี้ในหน้าต่างโมดูล

รหัส VBA: สร้างชุดค่าผสมทั้งหมดของ 3 หรือหลายคอลัมน์

Sub ListAllCombinations()
'Updateby Extendoffice
Dim xDRg1, xDRg2, xDRg3 As Range
Dim xRg  As Range
Dim xStr As String
Dim xFN1, xFN2, xFN3 As Integer
Dim xSV1, xSV2, xSV3 As String
Set xDRg1 = Range("A2:A4")  'First column data
Set xDRg2 = Range("B2:B6")  'Second column data
Set xDRg3 = Range("C2:C5")  'Third column data
xStr = "-"   'Separator
Set xRg = Range("E2")  'Output cell
For xFN1 = 1 To xDRg1.Count
    xSV1 = xDRg1.Item(xFN1).Text
    For xFN2 = 1 To xDRg2.Count
        xSV2 = xDRg2.Item(xFN2).Text
      For xFN3 = 1 To xDRg3.Count
        xSV3 = xDRg3.Item(xFN3).Text
        xRg.Value = xSV1 & xStr & xSV2 & xStr & xSV3
        Set xRg = xRg.Offset(1, 0)
       Next
    Next
Next
End Sub

หมายเหตุ: ในโค้ดด้านบน A2: A4, B2: B6, C2: C5 คือช่วงข้อมูลที่คุณต้องการใช้ E2 คือเซลล์ผลลัพธ์ที่คุณต้องการค้นหาผลลัพธ์ หากคุณต้องการรับชุดค่าผสมทั้งหมดของคอลัมน์เพิ่มเติมโปรดเปลี่ยนและเพิ่มพารามิเตอร์อื่น ๆ ลงในโค้ดตามที่คุณต้องการ

3. จากนั้นกด F5 คีย์เพื่อเรียกใช้รหัสนี้และชุดค่าผสมทั้งหมดของ 3 หรือหลายคอลัมน์จะถูกสร้างขึ้นพร้อมกันดูภาพหน้าจอ:


สร้างชุดค่าผสมทั้งหมดโดยใช้ข้อมูล 3 คอลัมน์หรือหลายคอลัมน์โดยใช้คุณสมบัติที่ยอดเยี่ยม

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

เคล็ดลับ:ที่จะใช้สิ่งนี้ แสดงรายการชุดค่าผสมทั้งหมด ประการแรกคุณควรดาวน์โหลดไฟล์ Kutools สำหรับ Excelแล้วใช้คุณสมบัตินี้อย่างรวดเร็วและง่ายดาย

หลังจากการติดตั้ง Kutools สำหรับ Excelโปรดทำตามนี้:

1. คลิก Kutools > สิ่งที่ใส่เข้าไป > แสดงรายการชุดค่าผสมทั้งหมดดูภาพหน้าจอ:

2. ใน แสดงรายการชุดค่าผสมทั้งหมด กล่องโต้ตอบระบุข้อมูลคอลัมน์และตัวคั่นสำหรับแสดงรายการชุดค่าผสมดังภาพหน้าจอต่อไปนี้:

3. หลังจากตั้งค่าข้อมูลและตัวคั่นแล้วคลิก Ok ในกล่องพรอมต์ถัดไปเลือกเซลล์เพื่อค้นหาผลลัพธ์ดูภาพหน้าจอ:

4. จากนั้นคลิก OK ปุ่มชุดค่าผสมทั้งหมดถูกสร้างขึ้นทันทีตามภาพด้านล่างที่แสดง:

คลิกเพื่อดาวน์โหลด Kutools สำหรับ Excel และทดลองใช้ฟรีทันที!


เครื่องมือเพิ่มประสิทธิภาพการทำงานในสำนักงานที่ดีที่สุด

Kutools สำหรับ Excel แก้ปัญหาส่วนใหญ่ของคุณและเพิ่มผลผลิตของคุณได้ถึง 80%

  • ซุปเปอร์ฟอร์มูล่าบาร์ (แก้ไขข้อความและสูตรหลายบรรทัดได้อย่างง่ายดาย); การอ่านเค้าโครง (อ่านและแก้ไขเซลล์จำนวนมากได้อย่างง่ายดาย); วางลงในช่วงที่กรองแล้ว...
  • ผสานเซลล์ / แถว / คอลัมน์ และการเก็บรักษาข้อมูล แยกเนื้อหาของเซลล์ รวมแถวที่ซ้ำกันและผลรวม / ค่าเฉลี่ย... ป้องกันเซลล์ซ้ำ; เปรียบเทียบช่วง...
  • เลือกซ้ำหรือไม่ซ้ำ แถว; เลือกแถวว่าง (เซลล์ทั้งหมดว่างเปล่า); Super Find และ Fuzzy Find ในสมุดงานจำนวนมาก สุ่มเลือก ...
  • สำเนาถูกต้อง หลายเซลล์โดยไม่เปลี่ยนการอ้างอิงสูตร สร้างการอ้างอิงอัตโนมัติ ถึงหลายแผ่น ใส่สัญลักษณ์แสดงหัวข้อย่อย, กล่องกาเครื่องหมายและอื่น ๆ ...
  • แทรกสูตรที่ชื่นชอบและรวดเร็ว, ช่วงแผนภูมิและรูปภาพ; เข้ารหัสเซลล์ ด้วยรหัสผ่าน; สร้างรายชื่อผู้รับจดหมาย และส่งอีเมล ...
  • แยกข้อความ, เพิ่มข้อความ, ลบตามตำแหน่ง, ลบ Space; สร้างและพิมพ์ผลรวมย่อยของเพจ แปลงระหว่างเนื้อหาของเซลล์และความคิดเห็น...
  • ซุปเปอร์ฟิลเตอร์ (บันทึกและใช้โครงร่างตัวกรองกับแผ่นงานอื่น ๆ ); การเรียงลำดับขั้นสูง ตามเดือน / สัปดาห์ / วันความถี่และอื่น ๆ ตัวกรองพิเศษ โดยตัวหนาตัวเอียง ...
  • รวมสมุดงานและแผ่นงาน; ผสานตารางตามคอลัมน์สำคัญ แยกข้อมูลออกเป็นหลายแผ่น; Batch แปลง xls, xlsx และ PDF...
  • การจัดกลุ่มตาราง Pivot ตาม จำนวนสัปดาห์วันในสัปดาห์และอื่น ๆ ... แสดงปลดล็อกเซลล์ที่ถูกล็อก ด้วยสีที่ต่างกัน เน้นเซลล์ที่มีสูตร / ชื่อ...
kte แท็บ 201905
  • เปิดใช้งานการแก้ไขและอ่านแบบแท็บใน Word, Excel, PowerPoint, ผู้จัดพิมพ์, Access, Visio และโครงการ
  • เปิดและสร้างเอกสารหลายรายการในแท็บใหม่ของหน้าต่างเดียวกันแทนที่จะเป็นในหน้าต่างใหม่
  • เพิ่มประสิทธิภาพการทำงานของคุณ 50% และลดการคลิกเมาส์หลายร้อยครั้งให้คุณทุกวัน!
ด้านล่าง officetab
Comments (19)
Rated 4.5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
Isn't this permutations without repetition, not combinations (of either variety)?

P(7,3) = 210 and that's the number of "combinations" your formula churns out for 3 columns of 7 rows.

Permutation of 7 choose 3 is 343 (repetition allowed).

C(7,3) = 35

Combination of 7 choose 3 is 84 (repetition allowed).

None of these match the 210 value returned by your formula.

Regardless, I like your formula. It can be extremely useful and taught me a lot about how to get something unusual out of Excel (LibreCalc in my case :) ).
This comment was minimized by the moderator on the site
Thanks! The formula worked nicely
Rated 4.5 out of 5
This comment was minimized by the moderator on the site
Esse código me ajudou bastante e combinou um item de cada coluna entre elas. Mas também preciso combinar dois itens de cada coluna, sem repetição. Alguém poderia me ajudar nisso?
This comment was minimized by the moderator on the site
Esse código me ajudou bastante e combinou um item de cada coluna entre elas. Mas também preciso combinar dois itens de cada coluna, sem repetição. Alguém poderia me ajudar nisso?
This comment was minimized by the moderator on the site
Bonjour, comment faire pour que chaque valeurs soient placées dans une colonne distincte en non séparées par un tiret ?
This comment was minimized by the moderator on the site
조합의 나열을 "단어-단어-단어" 순으로 나열하는 법은 이해했습니다.
그런데 "단어"가 아니라 숫자일 경우,
즉 숫자의 조합을 단순 나열이 아닌 덧셈이나 곱셈으로 적용하려면 어떻게 해야하는지 알 수 있을까요?

'VBA 코드 : 3 개 또는 여러 열의 모든 조합 생성' 에서 말이죠.

"1-1-1" 로 엑셀에 결과 값이 표기되는 것이 아니고

-1 로 엑셀에 표기될 수 있게 말입니다.
This comment was minimized by the moderator on the site
Hello So this is a code for 9 columns :')
Sub ListAllCombinations()
'Updateby Extendoffice
Dim xDRg1, xDRg2, xDRg3, xDRg4, xDRg5, xDRg6, xDRg7, xDRg8, xDRg9 As Range
Dim xRg As Range
Dim xStr As String
Dim xFN1, xFN2, xFN3, xFN4, xFN5, xFN6, xFN7, xFN8, xFN9 As Integer
Dim xSV1, xSV2, xSV3, xSV4, xSV5, xSV6, xSV7, xSV8, xSV9 As String
Set xDRg1 = Range("A2:A3") 'First column data
Set xDRg2 = Range("B2:B3") 'Second column data
Set xDRg3 = Range("C2:C10") 'Third column data
Set xDRg4 = Range("D2:D2") 'Third column data
Set xDRg5 = Range("E2:E3") 'Third column data
Set xDRg6 = Range("F2:F3") 'Third column data
Set xDRg7 = Range("G2:G4") 'Third column data
Set xDRg8 = Range("H2:H3") 'Third column data
Set xDRg9 = Range("I2:I3") 'Third column data
xStr = "-" 'Separator
Set xRg = Range("K2") 'Output cell
For xFN1 = 1 To xDRg1.Count
xSV1 = xDRg1.Item(xFN1).Text
For xFN2 = 1 To xDRg2.Count
xSV2 = xDRg2.Item(xFN2).Text
For xFN3 = 1 To xDRg3.Count
xSV3 = xDRg3.Item(xFN3).Text
For xFN4 = 1 To xDRg4.Count
xSV4 = xDRg4.Item(xFN4).Text
For xFN5 = 1 To xDRg5.Count
xSV5 = xDRg5.Item(xFN5).Text
For xFN6 = 1 To xDRg6.Count
xSV6 = xDRg6.Item(xFN6).Text
For xFN7 = 1 To xDRg7.Count
xSV7 = xDRg7.Item(xFN7).Text
For xFN8 = 1 To xDRg8.Count
xSV8 = xDRg8.Item(xFN8).Text
For xFN9 = 1 To xDRg9.Count
xSV9 = xDRg9.Item(xFN9).Text
xRg.Value = xSV1 & xStr & xSV2 & xStr & xSV3 & xStr & xSV4 & xStr & xSV5 & xStr & xSV6 & xStr & xSV7 & xStr & xSV8 & xStr & xSV9
Set xRg = xRg.Offset(1, 0)
Next
Next
Next
Next
Next
Next
Next
Next
Next
End Sub
This comment was minimized by the moderator on the site
HiIn VBA code I used four column and range of the column are E2:E75, B2:B267, C2:C195 & D2:D267. Out put range is J2. In this case out put result was exceed row limit. Please help to solve the error
This comment was minimized by the moderator on the site
<p>Thank you so much for this code. I have modified the code for the amount of column I need (25).</p><p>Thanks,</p>
This comment was minimized by the moderator on the site
Thank you so much. Exactly what I need :-)))
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations