กลับด้านข้อมูลใน Excel (กลับลำดับข้อมูลในคอลัมน์/แถว)

บางครั้ง คุณอาจต้องพลิกข้อมูลกลับหัวใน Excel กล่าวคือ กลับลำดับของข้อมูลในชุดข้อมูลแนวตั้ง และกลับลำดับของข้อมูลจากซ้ายไปขวาในชุดข้อมูลแนวนอน

ตอนนี้ ถ้าคุณคิดว่าต้องมีฟังก์ชันในตัวใน Excel เพื่อดำเนินการนี้ ฉันเกรงว่าคุณจะผิดหวัง

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

ในบทช่วยสอนนี้ ฉันจะแสดงวิธีพลิกข้อมูลในแถว คอลัมน์ และตารางแบบกลับหัวใน Excel

มาเริ่มกันเลย!

พลิกข้อมูลโดยใช้ SORT และคอลัมน์เสริม

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

พลิกข้อมูลในแนวตั้ง (ลำดับย้อนกลับ)

สมมติว่าคุณมีคอลัมน์เหมือนด้านล่างชื่อชุดข้อมูลและคุณต้องการพลิกข้อมูลนี้:

นี่คือขั้นตอนในการพลิกข้อมูลในแนวตั้ง:

1. ในคอลัมน์ที่อยู่ติดกัน ให้ป้อน "Helper" เป็นส่วนหัวของคอลัมน์
2. ในคอลัมน์วิธีใช้ ให้ป้อนชุดตัวเลข (1, 2, 3 ฯลฯ)คุณสามารถทำได้อย่างรวดเร็วโดยใช้วิธีการที่แสดงไว้ที่นี่
3. เลือกชุดข้อมูลทั้งหมด รวมถึงคอลัมน์เสริม
4. คลิกแท็บข้อมูล
5. คลิกไอคอนจัดเรียง
6. ในไดอะล็อกบ็อกซ์ Sort ให้เลือก Helper จากเมนูแบบเลื่อนลง Sort By
7. ในเมนูคำสั่งแบบเลื่อนลง ให้เลือก "ใหญ่ไปเล็ก"
8. คลิกตกลง

ขั้นตอนข้างต้นจะจัดเรียงข้อมูลตามค่าคอลัมน์ตัวช่วย ซึ่งจะทำให้ชื่อในข้อมูลอยู่ในลำดับย้อนกลับด้วย

อย่าลังเลที่จะวางคอลัมน์ช่วยเหลือเมื่อคุณทำเสร็จแล้ว

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

พลิกข้อมูลในแนวนอน

คุณยังสามารถพลิกข้อมูลในแนวนอนใน Excel ได้ด้วยวิธีเดียวกัน

Excel มีตัวเลือกในการเรียงลำดับข้อมูลในแนวนอนโดยใช้กล่องโต้ตอบการเรียงลำดับ (คุณสมบัติเรียงลำดับจากซ้ายไปขวา)

สมมติว่าคุณมีตารางดังที่แสดงด้านล่าง และคุณต้องการพลิกข้อมูลนี้ในแนวนอน

นี่คือขั้นตอนในการทำเช่นนี้:

1. ในแถวด้านล่าง ให้ป้อน "Helper" เป็นชื่อแถว
2. ในแถวตัวช่วย ให้ป้อนชุดตัวเลข (1, 2, 3 ฯลฯ)
3. เลือกชุดข้อมูลทั้งหมด รวมถึงแถวเสริม
4. คลิกแท็บข้อมูล
5. คลิกไอคอนจัดเรียง
6. ในกล่องโต้ตอบการเรียงลำดับ ให้คลิกปุ่มตัวเลือก
7. ในกล่องโต้ตอบที่เปิดขึ้น ให้คลิก จัดเรียงจากซ้ายไปขวา
8. คลิกตกลง
9. ในรายการดรอปดาวน์เรียงตาม ให้เลือกแถวที่ 3 (หรืออะไรก็ได้ที่มีคอลัมน์ผู้ช่วยของคุณ)
10. ในเมนูคำสั่งแบบเลื่อนลง ให้เลือก "ใหญ่ไปเล็ก"
11. คลิกตกลง

ขั้นตอนข้างต้นจะพลิกทั้งตารางในแนวนอน

เมื่อเสร็จแล้วคุณสามารถลบบรรทัดตัวช่วยได้

ใช้ Excel เพื่อพลิกสูตรกลับหัวพลิกข้อมูล

Microsoft 365 มีสูตรใหม่ที่ช่วยให้ง่ายต่อการย้อนกลับลำดับของคอลัมน์หรือตารางใน Excel

ในส่วนนี้ ฉันจะแสดงวิธีทำโดยใช้สูตร SORTBY (ถ้าคุณใช้ Microsoft 365) หรือสูตร INDEX (หากคุณไม่ได้ใช้ Microsoft 365)

ใช้ฟังก์ชัน SORTBY (มีให้ใน Microsoft 365)

สมมติว่าคุณมีตารางดังต่อไปนี้ และคุณต้องการพลิกข้อมูลในตารางนั้น:

ในการทำเช่นนี้ ก่อนอื่น ให้คัดลอกส่วนหัวและวางไว้ในตำแหน่งที่คุณต้องการพลิกตาราง

ตอนนี้ใช้สูตรต่อไปนี้ด้านล่างเซลล์ส่วนหัวซ้ายสุด:

=SORTBY($A$2:$B$12,ROW(A2:A12),-1)

สูตรข้างต้นจะเรียงลำดับข้อมูลและเรียงลำดับตามผลลัพธ์ของฟังก์ชัน ROW

ในกรณีนี้ ฟังก์ชัน ROW จะคืนค่าอาร์เรย์ของตัวเลขที่แสดงหมายเลขแถวระหว่างช่วงที่ระบุ (ในตัวอย่างนี้ ชุดของตัวเลข เช่น 2, 3, 4 เป็นต้น)

เนื่องจากพารามิเตอร์ที่สามของสูตรนี้คือ -1 จึงบังคับให้สูตรเรียงลำดับข้อมูลตามลำดับจากมากไปน้อย

ระเบียนที่มีหมายเลขแถวสูงสุดจะอยู่ที่ด้านบนสุด และระเบียนที่มีหมายเลขกฎต่ำสุดจะอยู่ที่ด้านล่าง ซึ่งโดยทั่วไปจะย้อนกลับลำดับของข้อมูล

เมื่อเสร็จแล้ว คุณสามารถแปลงสูตรเป็นค่าเพื่อรับตารางสแตติก

ใช้ฟังก์ชันดัชนี

หากคุณไม่สามารถใช้ฟังก์ชัน SORTBY ได้ ไม่ต้องกังวล คุณสามารถใช้ฟังก์ชัน INDEX ที่น่าทึ่งได้

สมมติว่าคุณมีชุดข้อมูลชื่อตามที่แสดงด้านล่าง และคุณต้องการพลิกข้อมูลนี้

นี่คือสูตรในการทำสิ่งนี้:

= INDEX ($ A $ 2: $ A $ 12, ROWS (A2: $ A $ 12))

 สูตรนี้ทำงานอย่างไร?

สูตรข้างต้นใช้ฟังก์ชัน INDEX ซึ่งจะคืนค่าจากเซลล์ตามตัวเลขที่ระบุในพารามิเตอร์ที่สอง

ความมหัศจรรย์ที่แท้จริงเกิดขึ้นเมื่อฉันใช้พารามิเตอร์ที่สองของฟังก์ชัน ROWS

เนื่องจากฉันล็อกส่วนที่สองของการอ้างอิงในฟังก์ชัน ROWS ในเซลล์แรก จะคืนค่าจำนวนแถวระหว่าง A2 และ A12 ซึ่งเท่ากับ 11

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

เมื่อเราลงไป ผลลัพธ์ของฟังก์ชัน ROWS จะลดลง 1 จาก 11 เป็น 10 เป็น 9 เป็นต้น

เนื่องจากฟังก์ชัน INDEX ส่งคืนค่าตามตัวเลขในอาร์กิวเมนต์ที่สอง จึงส่งผลให้ข้อมูลในลำดับที่กลับกัน

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

สมมติว่าคุณมีชุดข้อมูลที่มีลักษณะดังนี้ และคุณต้องการกลับลำดับของทั้งตาราง:

นี่คือสูตรที่จะทำสิ่งนี้ให้คุณ:

=INDEX($A$2:$B$12,ROWS(A2:$A$12),COLUMNS($A$2:A2))

นี่คือสูตรที่คล้ายกันซึ่งฉันได้เพิ่มพารามิเตอร์ตัวที่สามซึ่งระบุหมายเลขคอลัมน์ซึ่งควรใช้ค่า

ในการทำให้สูตรนี้เป็นไดนามิก ฉันใช้ฟังก์ชัน COLUMNS ซึ่งจะคอยเปลี่ยนค่าคอลัมน์จาก 1 เป็น 2 เป็น 3 เมื่อคุณคัดลอกไปทางขวา

เมื่อเสร็จแล้ว คุณสามารถแปลงสูตรเป็นค่าเพื่อให้ได้ผลลัพธ์คงที่

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

พลิกข้อมูลโดยใช้ VBA

ถ้าการพลิกข้อมูลใน Excel เป็นสิ่งที่คุณต้องทำบ่อยๆ คุณสามารถลองใช้วิธี VBA ได้

เมื่อใช้โค้ดแมโคร VBA คุณสามารถคัดลอกและวางครั้งเดียวในเวิร์กบุ๊กในตัวแก้ไข VBA จากนั้นนำมาใช้ซ้ำในเวิร์กบุ๊กเดียวกัน

คุณยังสามารถบันทึกรหัสในสมุดงานแมโครส่วนบุคคลหรือเป็น Add-in ของ Excel และสามารถใช้ได้ในสมุดงานใดๆ ในระบบของคุณ

ด้านล่างนี้คือโค้ด VBA เพื่อพลิกข้อมูลที่เลือกในแนวตั้งในเวิร์กชีต

ย่อย FlipVerically()

Dim TopRow เป็น Variant
Dim LastRow เป็น Variant
Dim StartNum เป็นจำนวนเต็ม
Dim EndNum เป็นจำนวนเต็ม

Application.ScreenUpdating = เท็จ

หมายเลขเริ่มต้น = 1
EndNum = การเลือก แถว การนับ

ทำในขณะที่ StartNum < EndNum
TopRow = Selection.Rows (จำนวนเริ่มต้น)
LastRow = Selection.Rows (จำนวนสุดท้าย)
Selection.Rows(EndNum) = แถวบนสุด
Selection.Rows(StartNum) = แถวสุดท้าย
StartNum = สตาร์ทนัม + 1
EndNum = EndNum - 1
ห่วง

Application.ScreenUpdating = จริง
ย่อยสิ้นสุด

ในการใช้รหัสนี้ ก่อนอื่นคุณต้องเลือกชุดข้อมูลที่คุณต้องการย้อนกลับ (ไม่รวมส่วนหัว) จากนั้นเรียกใช้รหัสนี้

รหัสนี้ทำงานอย่างไร

โค้ดด้านบนนี้นับจำนวนแถวทั้งหมดในชุดข้อมูลก่อน และกำหนดให้กับตัวแปร EndNum

จากนั้นจะใช้ลูป Do while เพื่อจัดเรียงข้อมูล

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

การวนซ้ำจะสิ้นสุดลงเมื่อการเรียงลำดับทั้งหมดเสร็จสิ้น

นอกจากนี้ยังใช้คุณสมบัติ Application.ScreenUpdating และตั้งค่าเป็น FALSE เมื่อโค้ดทำงาน จากนั้นเปลี่ยนกลับเป็น TRUE เมื่อโค้ดทำงานเสร็จสิ้น

เพื่อให้แน่ใจว่าคุณจะไม่เห็นการเปลี่ยนแปลงที่เกิดขึ้นบนหน้าจอแบบเรียลไทม์ และยังช่วยให้กระบวนการเร็วขึ้นอีกด้วย

วิธีการใช้รหัส?

คัดลอกและวางโค้ดนี้ในโปรแกรมแก้ไข VB ดังนี้:

1. เปิดไฟล์ Excel ที่คุณต้องการเพิ่มโค้ด VBA
2. กด ALT ค้างไว้แล้วกด F-11 (คุณยังสามารถไปที่แท็บนักพัฒนาซอฟต์แวร์แล้วคลิกไอคอน Visual Basic)
3. ในตัวแก้ไข Visual Basic ที่เปิดขึ้น จะมี Project Explorer ทางด้านซ้ายของตัวแก้ไข VBAหากคุณไม่เห็น ให้คลิกแท็บมุมมอง จากนั้นคลิก Project Explorer
4. คลิกขวาที่วัตถุใด ๆ ของสมุดงานที่คุณต้องการเพิ่มรหัส
5. ไปที่ตัวเลือกแทรกและคลิกที่โมดูลสิ่งนี้จะเพิ่มโมดูลใหม่ให้กับสมุดงาน
6. ดับเบิลคลิกที่ไอคอนโมดูลใน Project Explorerนี่จะเป็นการเปิดหน้าต่างรหัสสำหรับโมดูลนั้น
7. คัดลอกและวางโค้ด VBA ด้านบนลงในหน้าต่างโค้ด

เมื่อต้องการเรียกใช้โค้ดแมโคร VBA ให้เลือกชุดข้อมูลที่จะพลิกก่อน (ไม่รวมส่วนหัว)

เมื่อเลือกข้อมูลแล้ว ให้ไปที่โปรแกรมแก้ไข VB แล้วคลิกปุ่มเล่นสีเขียวในแถบเครื่องมือ หรือเลือกบรรทัดใดก็ได้ในโค้ดแล้วกด F5

ดังนั้น นี่คือวิธีบางส่วนที่คุณสามารถพลิกข้อมูลใน Excel (เช่น กลับลำดับของชุดข้อมูล)

วิธีการทั้งหมดที่ฉันได้กล่าวถึงในบทช่วยสอนนี้ (สูตร ฟังก์ชัน SORT และ VBA) สามารถใช้เพื่อพลิกข้อมูลในแนวตั้งและแนวนอน (คุณต้องปรับสูตรและโค้ด VBA ตามลำดับสำหรับการพลิกข้อมูลในแนวนอน)

ฉันหวังว่าคุณจะพบว่าบทช่วยสอนนี้มีประโยชน์