ข้ามข้อจำกัดการ Paste Link + Transpose

ผู้ใช้ Excel ทุกคนน่าจะได้มีโอกาสต้องใช้การ Transpose ข้อมูลกันมาบ้างแล้วไม่มากก็น้อย เช่น การเปลี่ยน Layout ระหว่างตารางข้อมูลแนวตั้ง กับแนวนอน

ผู้ใช้อีกจำนวนหนึ่งก็อาจจะเคยทำการ Paste Link … คือการเอา Copy & Paste โดยไม่ได้เอาข้อมูลไปตรงๆ แต่ใช้วิธี Link หรือ Refer กลับมาที่ต้นฉบับแทน ดังเช่นในภาพตัวอย่างด้านล่างนี้

 

แต่ถ้าเราอยากจะทำการ Paste Link และ Transpose Data ไปพร้อมๆ กันล่ะก็ จะพบว่าเราใช้วิธีการ Paste Special ที่ Excel ให้มาไม่ได้ เพราะเมื่อเราเลือกติ๊ก Transpose ปุ๊บ ปุ่ม Paste Link ก็จะใช้ไม่ได้ปั๊บ

 

…บางที Microsoft ก็ใจร้ายเกินไป…

 

แต่ไม่เป็นไร ชีวิตเรายังพอมีหนทางให้เดินต่อ

ถ้าอยากลองทำตาม ให้โหลด Sample File นี้มาเปิดก่อนครับ

SampleFile

…

วิธีแก้ปัญหาวิธีที่ 1 : ใช้ Find and Replace ช่วย

1. ขั้นแรกให้เราใช้วิธี Copy และมา Paste Special ตามปรกติก่อน
   
   .
2. ณ จุดนี้ จะพบว่าเราได้ตารางข้อมูลอีกอันหนึ่งทีข้อมูลเหมือนกัน แต่จริงๆ แล้วเป็นสูตรที่อ้างอิงไปที่ตารางเดิม โดยใช้เครื่องหมายเท่ากับ (‘=’)
   
   .
3. จากนั้นให้เราเลือกคลุมตารางใหม่ทั้งตาราง แล้วกด Ctrl+H เพื่อเรียกหน้าต่าง Find and Replace ขึ้นมา
   
   .
4. แล้วใส่เครื่องหมาย = ตรงช่อง Find what
   ใส่ x= ตรงช่อง Replace with
   แสร็จแล้วกดปุ่ม Replace All จากนั้นก็ปิดหน้าต่าง Find and Replace นี้ไป
   
   .
5. ตารางใหม่ของเราก็จะหน้าตาเป็นแบบนี้ คือมีตัว x เพิ่มขึ้นมาข้างหน้าสูตร
   .
6. จากนั้นเราก็ Copy ตารางที่ 2 นี้ มาวางแบบ Transpose อีกทีหนึ่ง
   
   .
7. จากตรงนี้ เราก็แค่ทำสวนทางกับขั้นตอนที่ 4 คือ เลือกตารางล่าสุด กด Ctrl+H และ ทำการ Replace x= ด้วย = เพื่อเอาสูตรของเรากลับมาดังเดิม 
   
   .

เพียงแค่เท่านี้ เราก็ได้ตารางที่ Link กลับไปตารางเดิม พร้อมทั้ง Transpose ไปพร้อมๆ กันอีกด้วย

วิธีแก้ปัญหาวิธีที่ 2 : ใช้สูตร Transpose

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

1. ให้เรานับขนาดของตารางก่อน ว่ากี่ Rows และ กี่ Columns เช่นในตัวอย่างนี้ ตารางของผมมีขนาด 8 Rows X 4 Columns ก็จดไว้ในใจก่อน
   .
2. ลากคลุม Cells อีกที่หนึ่งที่เราอยากจะทำการวางสูตรที่จะลิ้งค์กลับมา่ที่ตารางข้อมูลต้นฉบับ โดยคราวนี้ให้ขนาดกลับด้านกันกับตารางต้นฉบับ กล่าวคือ ของเดิม 8×4 ข้องใหม่นี้ก็ให้เลือกเป็น 4×8
   
   .
3. ใส่สูตร =Transpose( 
   จากนั้นทำการเลือกตารางต้นฉบับที่เราต้องการ
   แล้วกด Ctrl+Shift+Enter เพื่อวางสูตรลงไป
   

 

อ้างอิง: ผมไปเจอ Trick แรกจาก Chandoo.org และอันที่สองจาก Excel.Tips.Net