แมสเตอร์ Excel: 4 วิธีพิสูจน์แล้วแปลงแถวซ้ำเป็นคอลัมน์

หากคุณกำลังมองหาวิธีที่ง่ายที่สุดและมีประสิทธิภาพที่สุดในการ transpose (สลับ) แถวซ้ำเป็นคอลัมน์ใน Excel คุณมาถูกที่แล้ว มาดูรายละเอียดขั้นตอนการ transpose แถวซ้ำอย่างรวดเร็วกัน

ดาวน์โหลดสมุดงาน

4 วิธี Transpose แถวซ้ำเป็นคอลัมน์ใน Excel

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

เราใช้ Microsoft Excel 365 ในที่นี้ คุณสามารถใช้รุ่นอื่นได้ตามสะดวก

วิธีที่ 1: ใช้ Conditional Formatting Transpose แถวซ้ำเป็นคอลัมน์

ที่นี่จะใช้ Conditional Formatting ไฮไลท์แถวซ้ำ เพื่อให้ transpose แถวซ้ำได้ง่ายขึ้นในคอลัมน์ Column1 และ Column2

ขั้นตอน:
➤ เลือกช่วงข้อมูล ไปที่แท็บ Home >> กลุ่ม Styles >> ดรอปดาวน์ Conditional Formatting >> ตัวเลือก Highlight Cells Rules >> ตัวเลือก Duplicate Values

จะมีกล่องโต้ตอบ Duplicate Values ปรากฏ
➤ เลือก Duplicate และ Light Red Fill with Dark Red Text ในกล่อง Format cells that contain
➤ กด OK

แถวซ้ำของสินค้า Walnut และ Apple จะถูกไฮไลท์ สามารถแปลงแถวแรกของแต่ละคู่ซ้ำเป็นคอลัมน์ได้ง่าย

➤ เลือกแถวของสินค้า Walnut และ Apple โดยกด CTRL ค้างขณะเลือก และกด CTRL+C เพื่อคัดลอก

➤ เพื่อวางแถวเป็นคอลัมน์ Right-Click ที่เซลล์ปลายทาง แล้วเลือก Transpose จาก Paste Options ต่างๆ

สุดท้าย จะ transpose แถวซ้ำเป็นคอลัมน์ได้แล้ว

อ่านเพิ่มเติม: Conditional Transpose ใน Excel (2 ตัวอย่าง)

วิธีที่ 2: ใช้ฟังก์ชัน COUNTIF

ในส่วนนี้ จะรวมชื่อพนักงานขายและยอดขายด้วยตัวดำเนินการ Ampersand ในคอลัมน์ Extra1 จากนั้นนับจำนวนการซ้ำของแถวด้วยฟังก์ชัน COUNTIF ในคอลัมน์ Extra2 สุดท้ายแปลงแถวซ้ำเป็นคอลัมน์ด้วยตัวเลือก Transpose

ขั้นตอน:
➤ พิมพ์สูตรต่อไปนี้ในเซลล์ D4 เพื่อรวมเซลล์ทั้งหมดของแต่ละแถว

โดย B4 คือชื่อ SalesPerson, C4 คือค่า Sales และ & จะรวมพวกเขาเข้าด้วยกัน

➤ กด ENTER และลาก Fill Handle ลง

จะได้การรวมเซลล์แต่ละแถวในคอลัมน์ Extra1

➤ เพื่อนับการซ้ำของแถวในคอลัมน์ Extra1 เขียนสูตรต่อไปนี้ในเซลล์ E4

โดย D4 เป็นเกณฑ์ และ $D$4:D4 เป็นช่วง ช่วงนี้จะเริ่มจากแถวแรกถึงแถวที่สูตรถูกใช้ เช่น สำหรับ แถว 8 ช่วงจะเป็น $D$4:D8

➤ กด ENTER และลาก Fill Handle ลง

จะได้จำนวนการซ้ำในคอลัมน์ Extra2 ที่นี่ 2 สำหรับ Lara และ Howard หมายความว่าพวกเขาปรากฏสองครั้งพร้อมยอดขายในชุดข้อมูลนี้ จึงจะแปลงแถวเหล่านี้เป็นคอลัมน์

➤ เลือกแถวของ Lara และ Howard โดยกด CTRL ค้างขณะเลือก และกด CTRL+C คัดลอก

➤ Right-Click ที่เซลล์ปลายทาง เลือก Transpose จาก Paste Options

สุดท้าย จะ transpose แถวซ้ำของ Lara และ Howard เป็นคอลัมน์ได้

อ่านเพิ่มเติม: วิธี Transpose แถวเป็นคอลัมน์ใน Excel (5 วิธีที่มีประโยชน์)

บทความคล้ายคลึง

• วิธี Transpose ทุก n แถวเป็นคอลัมน์ใน Excel (2 วิธีง่าย)
• Excel Power Query: Transpose แถวเป็นคอลัมน์ (คู่มือทีละขั้นตอน)
• Excel VBA: Transpose หลายแถวในกลุ่มเป็นคอลัมน์
• Transpose หลายคอลัมน์เป็นคอลัมน์เดียวใน Excel (3 วิธีที่สะดวก)

วิธีที่ 3: ใช้การผสมผสานฟังก์ชัน IF และ COUNTIFS

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

ขั้นตอน:
➤ พิมพ์สูตรต่อไปนี้ในเซลล์ E4

โดย $B$4:$B$13, $C$4:$C$13, และ $D$4:$D$13 เป็นช่วงที่ 1, 2, 3 $B4, $C4, และ $D4 เป็นเกณฑ์ที่ 1, 2, 3

• COUNTIFS($B$4:$B$13,$B4,$C$4:$C$13,$C4,$D$4:$D$13,$D4) → ส่งคืนตัวเลขแทนจำนวนการซ้ำของแถว
  Output → 2

• COUNTIFS($B$4:$B$13,$B4,$C$4:$C$13,$C4,$D$4:$D$13,$D4)>1 กลายเป็น
  2>1 → ส่งคืน TRUE หากเงื่อนไขเป็นจริง มิฉะนั้น FALSE
  Output → TRUE

• IF(COUNTIFS($B$4:$B$13,$B4,$C$4:$C$13,$C4,$D$4:$D$13,$D4)>1, “Repeated Row”, “”) กลายเป็น
  IF(TRUE, “Repeated Row”, “”) → ส่งคืนสตริง “Repeated Row” สำหรับ TRUE มิฉะนั้นเว้นว่าง
  Output → Repeated Row

➤ กด ENTER และลาก Fill Handle ลง

ผลลัพธ์: ได้ข้อความ Repeated Row สำหรับแถวซ้ำ

➤ เลือกแถวของสินค้า Walnut และ Apple โดยกด CTRL ค้างขณะเลือก และกด CTRL+C คัดลอก

➤ Right-Click ที่เซลล์ปลายทาง เลือก Transpose จาก Paste Options

สุดท้าย ได้แถวซ้ำที่ transpose แล้ว ตามภาพ

อ่านเพิ่มเติม: วิธี Transpose แถวเป็นคอลัมน์ตามเกณฑ์ใน Excel (2 วิธี)

วิธีที่ 4: ใช้โค้ด VBA Transpose แถวซ้ำเป็นคอลัมน์ใน Excel

ในส่วนนี้ จะใช้โค้ด VBA transpose แถวซ้ำของสินค้า Walnut และ Apple เป็นคอลัมน์

ขั้นตอน:
➤ ไปที่แท็บ Developer >> ตัวเลือก Visual Basic

จะเปิด Visual Basic Editor
➤ ไปที่แท็บ Insert >> ตัวเลือก Module

จะสร้าง Module ขึ้นมา

➤ เขียนโค้ดต่อไปนี้

Sub transform_same_rows() Dim sht As Worksheet Dim item, extra_column As Double Dim storage_object As Object Set storage_object = CreateObject("scripting.dictionary") Set sht = Worksheets("VBA") Total_column = 3 For increment = 4 To 13 item = Join(Application.Transpose(Application.Transpose( _ sht.Cells(increment, 2).Resize(1, Total_column))), "*") If Not storage_object.exists(item) Then storage_object.Add item, New Collection storage_object(item).Add sht.Cells(increment, Total_column + 1).Value sht.Rows(increment).ClearContents Next increment increment = 4 For Each item In storage_object sht.Cells(increment, 2).Resize(1, Total_column).Value = Split(item, "*") extra_column = Total_column + 1 For Each str_value In storage_object(item) sht.Cells(increment, extra_column) = str_value extra_column = extra_column + 1 Next str_value increment = increment + 1 Next item For increment = 4 To 13 If sht.Cells(increment, 5).Value = sht.Cells(increment, 4).Value Then sht.Range(Cells(increment, 2), Cells(increment, 4)).Copy sht.Cells(increment, 6).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _ SkipBlanks:=True, Transpose:=True End If Next increment End Sub

ที่นี่ ประกาศ sht เป็น Worksheet, item, extra_column เป็น Double, storage_object เป็น Object ตั้ง sht เป็นชีท VBA กำหนด Total_column เป็น 3
storage_object ตั้งเป็นอ็อบเจกต์ Dictionary ช่วยเก็บรายการ
ใช้ FOR loop สำหรับช่วง increment จากแถว 4 ถึง 13 ใช้ TRANSPOSE function สองครั้ง และ JOIN function รวมเซลล์ทั้งหมดของแถวด้วยตัวคั่น * ไปยังตัวแปร item เพิ่มรายการใน storage_object แล้วล้างแถวทั้งหมด
แถวที่ไม่ซ้ำจะถูกใส่ใน 3 คอลัมน์ โดยใช้ SPLIT function และค่ายอดขายของแถวซ้ำจะถูกเพิ่มในคอลัมน์ติดกับคอลัมน์ Sales
ใช้ IF statement ตรวจสอบค่ายอดขายกับค่าของเซลล์ติดกัน และหากเงื่อนไขเป็นจริง จะคัดลอกแถวที่ตรงกันและ transpose แถวนั้น

➤ กด F5
จะสามารถล้างแถวซ้ำจากชุดข้อมูล พร้อม transpose แถวซ้ำได้

อ่านเพิ่มเติม: วิธี Transpose แถวเป็นคอลัมน์ด้วย Excel VBA (4 ตัวอย่างที่เหมาะสม)

ส่วนฝึกปฏิบัติ

สำหรับการฝึกปฏิบัติด้วยตนเอง มีส่วน Practice ดังภาพในชีทชื่อ Practice โปรดลองทำเอง

สรุป

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

อ่านเพิ่มเติม

• วิธี Transpose ใน Excel (5 วิธีง่าย)
• Transpose หลายแถวในกลุ่มเป็นคอลัมน์ใน Excel
• วิธี Transpose คอลัมน์เป็นแถวใน Excel (6 วิธี)
• แปลงคอลัมน์เป็นแถวใน Excel ด้วย Power Query
• วิธีแปลงคอลัมน์เป็นแถวใน Excel ตามค่าเซลล์
• VBA Transpose หลายคอลัมน์เป็นแถวใน Excel (2 วิธี)
• วิธีแปลงคอลัมน์เป็นรายการคั่นจุลภาคพร้อมเครื่องหมายคำพูดเดี่ยว
• วิธี Transpose ใน Excel VBA (3 วิธี)