ฟังก์ชัน Transpose ใน Excel

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

ฟังก์ชัน TRANSPOSE – ย้ายช่วงเซลล์ใน Excel

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

ไวยากรณ์ฟังก์ชัน

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

การย้ายช่วงของเซลล์ในแนวตั้ง (คอลัมน์)

สมมติว่าเรามีคอลัมน์ที่มีช่วง B2:B6 พวกเขาสามารถมีทั้งค่าสำเร็จรูปและสูตรที่ส่งกลับผลลัพธ์ไปยังเซลล์เหล่านี้ ไม่ใช่เรื่องสำคัญสำหรับเรา การขนย้ายเป็นไปได้ในทั้งสองกรณี หลังจากใช้ฟังก์ชันนี้ ความยาวของแถวจะเท่ากับความยาวคอลัมน์ของช่วงเดิม

ลำดับขั้นตอนในการใช้สูตรนี้มีดังนี้

1. เลือกบรรทัด ในกรณีของเรา มีความยาวห้าเซลล์
2. หลังจากนั้น ให้เลื่อนเคอร์เซอร์ไปที่แถบสูตร แล้วป้อนสูตรที่นั่น =ทรานส์พี(B2:B6)
3. กดคีย์ผสม Ctrl + Shift + Enter

โดยปกติ ในกรณีของคุณ คุณต้องระบุช่วงที่เป็นปกติสำหรับตารางของคุณ

การย้ายช่วงเซลล์แนวนอน (แถว)

โดยหลักการแล้ว กลไกการดำเนินการเกือบจะเหมือนกับย่อหน้าที่แล้ว สมมติว่าเรามีสตริงที่มีพิกัดเริ่มต้นและจุดสิ้นสุด B10:F10 นอกจากนี้ยังสามารถมีทั้งค่าและสูตรโดยตรง มาสร้างคอลัมน์กัน ซึ่งจะมีขนาดเท่ากับแถวเดิมกัน ลำดับของการกระทำมีดังนี้:

1. เลือกคอลัมน์นี้ด้วยเมาส์ คุณยังสามารถใช้แป้นต่างๆ บนแป้นพิมพ์ Ctrl และลูกศรลง หลังจากที่คลิกที่เซลล์บนสุดของคอลัมน์นี้
2. หลังจากนั้นเราเขียนสูตร =ส่ง(B10:F10) ในแถบสูตร
3. เราเขียนเป็นสูตรอาร์เรย์โดยใช้คีย์ผสม Ctrl + Shift + Enter

ขนย้ายด้วย Paste Special

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

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

3 วิธีในการเปลี่ยนตารางใน Excel

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

วิธีที่ 1: วางแบบพิเศษ

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

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

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

1. เลือกช่วงของข้อมูลที่เราต้องหมุน หลังจากนั้นเราจะคัดลอกข้อมูลนี้
2. วางเคอร์เซอร์ที่ใดก็ได้บนแผ่นงาน จากนั้นคลิกที่ปุ่มเมาส์ขวาและเปิดเมนูบริบท
3. จากนั้นคลิกที่ปุ่ม "วางแบบพิเศษ"

หลังจากทำตามขั้นตอนเหล่านี้แล้ว คุณต้องคลิกที่ปุ่ม "ย้าย" ให้ทำเครื่องหมายที่ช่องถัดจากรายการนี้ เราไม่เปลี่ยนการตั้งค่าอื่น ๆ จากนั้นคลิกที่ปุ่ม "ตกลง"

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

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

วิธีที่ 2 ฟังก์ชัน TRANSP ใน Excel

อันที่จริง วิธีนี้ไม่ได้ใช้อย่างแข็งขันเหมือนตอนเริ่มต้นการปรากฏตัวของโปรแกรมสเปรดชีตนี้อีกต่อไป เนื่องจากวิธีนี้ซับซ้อนกว่าการใช้ Paste Special มาก อย่างไรก็ตาม พบการใช้งานในการขนย้ายตารางโดยอัตโนมัติ

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

1. อันดับแรก เราต้องเลือกช่วงข้อมูลที่จะใช้ในการย้ายตาราง คุณเพียงแค่ต้องเลือกพื้นที่ตรงกันข้าม ตัวอย่างเช่น ในตัวอย่างนี้ เรามี 4 คอลัมน์และ 6 แถว ดังนั้นจึงจำเป็นต้องเลือกพื้นที่ที่มีลักษณะตรงกันข้าม: 6 คอลัมน์และ 4 แถว ภาพแสดงให้เห็นได้เป็นอย่างดี
2. หลังจากนั้นเราก็เริ่มกรอกข้อมูลในเซลล์นี้ทันที เป็นสิ่งสำคัญที่จะไม่ลบส่วนที่เลือกออกโดยไม่ได้ตั้งใจ ดังนั้น คุณต้องระบุสูตรโดยตรงในแถบสูตร
3. จากนั้นกดคีย์ผสม Ctrl + Shift + Enter โปรดจำไว้ว่านี่คือสูตรอาร์เรย์ เนื่องจากเรากำลังทำงานกับชุดข้อมูลจำนวนมากในคราวเดียว ซึ่งจะถูกโอนไปยังชุดเซลล์ขนาดใหญ่อีกชุดหนึ่ง

หลังจากที่เราป้อนข้อมูลเรากดปุ่ม Enter หลังจากนั้นเราจะได้ผลลัพธ์ดังต่อไปนี้

เราจะเห็นว่าสูตรไม่โอนไปยังตารางใหม่ การจัดรูปแบบก็หายไป บทกวี

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

ดังนั้น วิธีนี้จึงเหมาะสมอย่างยิ่งในกรณีที่คุณต้องการให้แน่ใจว่าตารางที่ย้ายมาเชื่อมโยงกับตารางเดิม หากคุณใช้เม็ดมีดพิเศษ ความเป็นไปได้นี้จะไม่มีอีกต่อไป

ตารางสรุป

นี่เป็นวิธีการใหม่โดยพื้นฐาน ซึ่งทำให้ไม่เพียงแต่ย้ายตารางเท่านั้น แต่ยังดำเนินการหลายอย่างอีกด้วย จริงกลไกการขนย้ายจะค่อนข้างแตกต่างเมื่อเทียบกับวิธีการก่อนหน้านี้ ลำดับของการกระทำมีดังนี้:

1. มาทำตารางเดือยกัน ในการทำเช่นนี้คุณต้องเลือกตารางที่เราจำเป็นต้องเปลี่ยน หลังจากนั้น ไปที่รายการ "แทรก" และค้นหา "Pivot Table" ที่นั่น กล่องโต้ตอบเหมือนในภาพหน้าจอนี้จะปรากฏขึ้น
2. ที่นี่คุณสามารถกำหนดช่วงที่จะสร้างใหม่ได้ รวมทั้งทำการตั้งค่าอื่นๆ อีกจำนวนหนึ่ง ตอนนี้เราสนใจที่จะแทนที่ตารางเดือยเป็นหลัก - บนแผ่นงานใหม่
3. หลังจากนั้น เลย์เอาต์ของตารางเดือยจะถูกสร้างขึ้นโดยอัตโนมัติ จำเป็นต้องทำเครื่องหมายรายการเหล่านั้นที่เราจะใช้จากนั้นจะต้องย้ายไปยังตำแหน่งที่ถูกต้อง ในกรณีของเรา เราต้องย้ายรายการ "ผลิตภัณฑ์" ไปที่ "ชื่อคอลัมน์" และ "ราคาต่อชิ้น" เป็น "มูลค่า"
4. หลังจากนั้นตารางเดือยจะถูกสร้างขึ้นในที่สุด โบนัสเพิ่มเติมคือการคำนวณมูลค่าสุดท้ายโดยอัตโนมัติ
5. คุณสามารถเปลี่ยนการตั้งค่าอื่นๆ ได้เช่นกัน ตัวอย่างเช่น ยกเลิกการเลือกรายการ "ราคาต่อชิ้น" และเลือกรายการ "ต้นทุนทั้งหมด" เป็นผลให้เราจะมีตารางที่มีข้อมูลเกี่ยวกับราคาสินค้า วิธีการขนย้ายนี้มีประสิทธิภาพมากกว่าวิธีอื่นๆ มาอธิบายประโยชน์บางประการของตารางเดือยกัน:

1. ระบบอัตโนมัติ ด้วยความช่วยเหลือของตารางเดือย คุณสามารถสรุปข้อมูลโดยอัตโนมัติ รวมทั้งเปลี่ยนตำแหน่งของคอลัมน์และคอลัมน์ตามอำเภอใจ ในการทำเช่นนี้ คุณไม่จำเป็นต้องดำเนินการขั้นตอนเพิ่มเติมใดๆ
2. การโต้ตอบ ผู้ใช้สามารถเปลี่ยนโครงสร้างของข้อมูลได้หลายครั้งตามต้องการเพื่อทำงานของเขา ตัวอย่างเช่น คุณสามารถเปลี่ยนลำดับของคอลัมน์ ตลอดจนจัดกลุ่มข้อมูลได้ตามต้องการ สามารถทำได้หลายครั้งตามที่ผู้ใช้ต้องการ และใช้เวลาน้อยกว่าหนึ่งนาทีอย่างแท้จริง
3. ง่ายต่อการจัดรูปแบบข้อมูล มันง่ายมากที่จะจัดตารางเดือยในแบบที่คนต้องการ ในการดำเนินการนี้ เพียงคลิกเมาส์ไม่กี่ครั้ง
4. รับค่า. สูตรจำนวนมากที่ใช้ในการสร้างรายงานนั้นอยู่ในการเข้าถึงโดยตรงของบุคคล และง่ายต่อการรวมเข้ากับตารางสรุปข้อมูล ข้อมูลเหล่านี้เป็นข้อมูลเช่น ผลรวม การหาค่าเฉลี่ยเลขคณิต การกำหนดจำนวนเซลล์ การคูณ การหาค่าที่มากที่สุดและน้อยที่สุดในตัวอย่างที่ระบุ
5. ความสามารถในการสร้างแผนภูมิสรุป ถ้ามีการคำนวณ PivotTable ใหม่ แผนภูมิที่เกี่ยวข้องจะได้รับการอัปเดตโดยอัตโนมัติ คุณสามารถสร้างแผนภูมิได้มากเท่าที่คุณต้องการ ทั้งหมดสามารถเปลี่ยนแปลงได้สำหรับงานเฉพาะและจะไม่เชื่อมต่อถึงกัน
6. ความสามารถในการกรองข้อมูล
7. เป็นไปได้ที่จะสร้างตารางสาระสำคัญโดยยึดตามแหล่งข้อมูลมากกว่าหนึ่งชุด ดังนั้นฟังก์ชั่นของพวกเขาจะดียิ่งขึ้นไปอีก

จริง เมื่อใช้ pivot table ต้องคำนึงถึงข้อจำกัดต่อไปนี้:

1. ไม่สามารถใช้ข้อมูลทั้งหมดเพื่อสร้างตารางสาระสำคัญได้ ก่อนที่พวกมันจะถูกนำมาใช้เพื่อการนี้ เซลล์จะต้องถูกทำให้เป็นมาตรฐาน พูดง่ายๆ ว่าทำให้ถูกต้อง ข้อกำหนดบังคับ: การมีอยู่ของบรรทัดส่วนหัว ความสมบูรณ์ของทุกบรรทัด ความเท่าเทียมกันของรูปแบบข้อมูล
2. ข้อมูลจะได้รับการอัปเดตแบบกึ่งอัตโนมัติ ในการรับข้อมูลใหม่ในตารางสาระสำคัญ คุณต้องคลิกที่ปุ่มพิเศษ
3. ตารางเดือยใช้พื้นที่มาก ซึ่งอาจนำไปสู่การหยุดชะงักของคอมพิวเตอร์ อีกทั้งไฟล์จะส่งทาง E-mail ได้ยากด้วยเหตุนี้

นอกจากนี้ หลังจากสร้างตารางสาระสำคัญแล้ว ผู้ใช้จะไม่สามารถเพิ่มข้อมูลใหม่ได้