เนื้อหา
คู่มือนี้จะอธิบายสิ่งที่ต้องทำเพื่อแปลงแถวเป็นคอลัมน์ใน Excel เมื่อใช้ Excel คุณมักจะต้องสลับแถวและคอลัมน์ในตาราง ตัวอย่างเช่น มันเกิดขึ้นที่คนคนหนึ่งทำโต๊ะขนาดใหญ่ แล้วเขาก็รู้ว่ามันง่ายกว่ามากที่จะอ่านมันถ้าคุณพลิกมัน
Эта детальная инструкция расскажет о нескольких способах транспонирования Excel-таблицы, а также о часто встречаемых ошибках, с которыми может столкнуться пользователь. Все они могут использоваться на любой версии Excel, как очень старой, так และ самой новой.
การใช้คุณสมบัติพิเศษวาง
สมมติว่าคุณมีชุดข้อมูลสองชุด ประการที่สองคือสิ่งที่คุณต้องการที่จะจบลงด้วย อย่างแรกคือตารางที่ต้องการย้าย ในเวอร์ชันแรก ชื่อของประเทศต่างๆ จะถูกนำเสนอในคอลัมน์ต่างๆ และไม่สะดวกในการอ่าน และยิ่งไปกว่านั้น การเปรียบเทียบคุณลักษณะของรัฐต่างๆ ซึ่งกันและกัน ดังนั้นจึงเป็นการดีกว่ามากที่จะจัดระเบียบตารางเพื่อให้ชื่อประเทศปรากฏในคอลัมน์ต่างๆ
ในการสลับแถวและคอลัมน์ ให้ทำดังนี้:
- เลือกตารางเดิม หากคุณต้องการดูทั้งตารางในคราวเดียว (หากตารางมีขนาดใหญ่มาก) คุณต้องกดคีย์ผสม Ctrl + Home และหลังจากนั้น – Ctrl + Shift + End
- จากนั้นเซลล์จะถูกคัดลอก ซึ่งสามารถทำได้ผ่านเมนูบริบทหรือโดยการกดคีย์ผสม Ctrl+C ขอแนะนำให้ทำความคุ้นเคยกับตัวเลือกสุดท้ายทันที เพราะหากคุณเรียนรู้ปุ่มลัด คุณสามารถทำงานหลายอย่างได้อย่างแท้จริงในหนึ่งวินาที
- เลือกเซลล์แรกในช่วงเป้าหมาย При этом он должен находиться за пределами таблицы. Также необходимо подобрать ячейку таким образом, чтобы таблица после транспонирования не пересен. ตัวอย่างเช่น หากตารางเริ่มต้นมี 4 คอลัมน์และ 10 แถว หลังจากดำเนินการทั้งหมดแล้ว ตารางจะกระจายออกไป 10 เซลล์และอีก 4 เซลล์ไปด้านข้าง ดังนั้น ในช่วงนี้ (นับจากเซลล์เป้าหมาย) ไม่น่าจะมีอะไร
- ในเซลล์เป้าหมาย คุณต้องคลิกขวาและเลือก "วางแบบพิเศษ" จากนั้นทำเครื่องหมายที่ช่องถัดจากคำจารึก "ย้าย"
สำคัญ: หากมีสูตรในตารางต้นทาง สิ่งสำคัญคือต้องแน่ใจว่ามีการใช้การอ้างอิงแบบสัมบูรณ์ในแต่ละเซลล์ ต้องทำเพื่อให้ลิงก์ทั้งหมดอัปเดตโดยอัตโนมัติ
Огромное преимущество опции «Специальная вставка» и заклечается в возможности транспонировать танспонировать танспонировать такомное преимущество опции «Специальная вставка» и заклечается в возможности транспонировать танспонировать таномное преимущество опции «Специальная вставка» И при этом полностью сохраняется форматирование, что также позволяет сэкономить кучу времени.
Несмотря на эти явные плюсы, есть и ряд серьезных недостатков, которые мешают этому методу назы:
- เป็นการไม่ดีที่จะใช้มันสำหรับการย้ายตารางที่เต็มเปี่ยมซึ่งไม่ลดเหลือช่วงของค่าซ้ำซากจำเจ ในกรณีนี้ ฟังก์ชัน "Transpose" จะถูกปิดใช้งาน เมื่อต้องการแก้ไขปัญหานี้ คุณต้องแปลงตารางเป็นช่วง
- วิธีนี้เหมาะสำหรับการเปลี่ยนแบบครั้งเดียวเนื่องจากไม่เชื่อมโยงตารางใหม่กับข้อมูลต้นฉบับ กล่าวง่ายๆ เมื่อคุณเปลี่ยนตารางใดตารางหนึ่ง ข้อมูลจะไม่ได้รับการอัปเดตโดยอัตโนมัติในตารางที่สอง ดังนั้นการขนย้ายจะต้องทำซ้ำ
วิธีการสลับแถวและคอลัมน์และเชื่อมโยงไปยังตารางหลัก?
ดังนั้น สิ่งที่สามารถทำได้เพื่อให้ตัวเลือก "วางแบบพิเศษ" สามารถเชื่อมโยงตารางกับข้อมูลหลักและข้อมูลที่เป็นผลมาจากการย้ายตำแหน่ง ท้ายที่สุดแล้ว ทุกคนต้องการให้ข้อมูลอัปเดตโดยอัตโนมัติ
- Скопировать таблицу, для которой требуется транспонирование.
- เลือกเซลล์ที่ไม่มีข้อมูลในพื้นที่ว่างของตาราง
- เปิดเมนูวางแบบพิเศษ คล้ายกับตัวอย่างก่อนหน้า หลังจากนั้นคุณต้องคลิกที่ปุ่ม "แทรกลิงก์" ซึ่งอยู่ที่ด้านล่างซ้าย
- ผลลัพธ์จะเป็นดังนี้
- Выбрать новую таблицу и запустить окно «Найти и заменить» путем нажатия комбинации клавиш Ctrl + เอช
- Заменить все знаки вода формулы (=) на ххх (или любую другую комбинацию знаков, которой нет в норинор).
- ผลที่ตามมาคือสิ่งเลวร้ายจะเกิดขึ้น แต่นี่เป็นเงื่อนไขขั้นกลางที่จำเป็นเพื่อให้บรรลุผลตามที่ต้องการ แล้วทุกอย่างจะดูสวยงาม
- คัดลอกตารางผลลัพธ์แล้วใช้ Paste Special เพื่อย้ายข้อมูล
หลังจากนั้น คุณเพียงแค่เปิดกล่องโต้ตอบ "ค้นหาและแทนที่" อีกครั้ง และใช้เพื่อเปลี่ยน "xxx" ในเซลล์เป็น "=" เพื่อให้เซลล์ทั้งหมดเชื่อมโยงกับข้อมูลเดิม
แน่นอนว่าวิธีนี้ซับซ้อนและยาวกว่า แต่วิธีนี้ช่วยให้คุณไม่ต้องมีลิงก์ไปยังตารางต้นฉบับ แต่วิธีนี้ก็มีข้อเสียเช่นกัน มันแสดงให้เห็นความจำเป็นในการจัดรูปแบบต่ออย่างอิสระ
การประยุกต์ใช้สูตร
มีสองฟังก์ชันที่ช่วยให้คุณสามารถเปลี่ยนแถวและคอลัมน์ได้อย่างยืดหยุ่น: ТРАНСП และ ДВССЫЛ. Здесь также есть возможность сохранить связь с первоначальной таблицей, но механика работы несной таблицей, но механика работы несной таблицей.
ฟังก์ชันTRANSP
อันที่จริง สูตรนี้เปลี่ยนสเปรดชีตโดยตรง ไวยากรณ์มีดังต่อไปนี้:
=TRANSP(อาร์เรย์)
ตอนนี้เราจะพยายามนำไปใช้กับตารางที่มีข้อมูลเกี่ยวกับประชากรของแต่ละรัฐ
- นับจำนวนคอลัมน์และแถวในตารางและค้นหาพื้นที่ว่างในแผ่นงานที่มีขนาดเท่ากัน
- เริ่มโหมดแก้ไขโดยกดปุ่ม F2
- เขียนฟังก์ชัน ทรานส์ โดยมีช่วงข้อมูลอยู่ในวงเล็บ สิ่งสำคัญคือต้องใช้การอ้างอิงแบบสัมบูรณ์เสมอเมื่อใช้ฟังก์ชันนี้
- กดคีย์ผสม Ctrl+Shift+Enter สิ่งสำคัญคือต้องกดคีย์ผสมทั้งหมด มิฉะนั้น สูตรจะไม่ทำงาน
เพียงเท่านี้ ผลลัพธ์ก็จะประมาณนี้!
ข้อดีของสูตรนี้เหมือนกับวิธีก่อนหน้า (โดยใช้ฟังก์ชัน "ค้นหาและแทนที่") คือเมื่อข้อมูลพื้นฐานเปลี่ยนแปลง ตารางที่ย้ายข้อมูลจะอัปเดตโดยอัตโนมัติ
Но недостатки у нее тоже имеются:
- การจัดรูปแบบ เช่นเดียวกับในกรณีของวิธีค้นหาและแทนที่ จะไม่ถูกบันทึก
- ตารางต้นฉบับต้องมีข้อมูลบางส่วน มิฉะนั้น บางเซลล์จะมีเลขศูนย์
- Излишняя зависимость от источника данных. То есть, этот метод имеет противоположный по недостаток – невозможность изменять транспонированнннннок. Если попытаться это сделать, программа скажет, что невозможно редактировать часть массива.
นอกจากนี้ ฟังก์ชันนี้ไม่สามารถใช้งานได้อย่างยืดหยุ่นในสถานการณ์ต่างๆ เสมอไป ดังนั้นคุณจำเป็นต้องรู้เกี่ยวกับฟังก์ชันนี้ แต่ใช้เทคนิคที่มีประสิทธิภาพมากขึ้น
การใช้สูตรทางอ้อม
กลศาสตร์ของวิธีนี้คล้ายกับการใช้สูตรมาก ทรานส์, но при этом его использование решает проблему невозможности редактировать транспонированнуй танунуй танурбать транспонированунуй танурбать но при этом его использование решает проблему невозможности редактировать транспонированунуй танунуй танурбать
โน одной формулы ทางอ้อม ไม่เพียงพอ: คุณยังต้องใช้ฟังก์ชันนี้อยู่ ที่อยู่. ในตัวอย่างนี้จะไม่มีตารางขนาดใหญ่ เพื่อไม่ให้มีข้อมูลที่ไม่จำเป็นมากเกินไป
สมมติว่าคุณมีตารางดังกล่าว ซึ่งประกอบด้วย 4 คอลัมน์และ 5 แถว
ต้องดำเนินการดังต่อไปนี้:
- ใส่สูตรนี้: =ทางอ้อม(ที่อยู่(คอลัมน์(A1),แถว(A1))) ลงในเซลล์ด้านซ้ายบนของตารางใหม่ (ในตัวอย่างของเราคือ A7) แล้วกดปุ่ม Enter หากข้อมูลไม่เริ่มต้นในแถวแรกหรือคอลัมน์แรก คุณจะต้องใช้สูตรที่ซับซ้อนกว่านี้: =ДВССЫЛ(АДРЕС(СТОЛБЕЦ(A1)-СТОЛБЕЦ($A$1)+СТРОКА($A$1);СТРОКА(A1)-СТРОКА($A$1)+СТОЛБЕЦ($A$1))). ในสูตรนี้ A1 หมายถึงเซลล์บนสุดของตาราง โดยพิจารณาจากการสร้างเซลล์ที่สลับสับเปลี่ยน
- ขยายสูตรไปยังพื้นที่ทั้งหมดที่จะวางเวอร์ชันใหม่ของตาราง ในการดำเนินการนี้ ให้ลากเครื่องหมายที่มุมล่างขวาของเซลล์แรกไปยังปลายอีกด้านของตารางในอนาคต
- ทั้งหมด! ตารางปรากฏว่าย้ายข้อมูลสำเร็จแล้ว และคุณยังสามารถแก้ไขตารางได้ แน่นอนว่ารูปร่างหน้าตาของเธอไม่เป็นที่ต้องการมากนัก แต่การแก้ไขก็ไม่ยาก ในการคืนค่าการจัดรูปแบบที่ถูกต้อง คุณต้องคัดลอกตารางที่เราย้าย (นั่นคือ ตารางต้นฉบับ) จากนั้นเลือกตารางที่สร้างขึ้นใหม่ ถัดไป ให้คลิกขวาที่ช่วงที่เลือก จากนั้นคลิก "การจัดรูปแบบ" ในตัวเลือกการวาง
ดังนั้นการใช้ฟังก์ชัน ทางอ้อม ช่วยให้คุณสามารถแก้ไขค่าใดๆ ก็ได้ในตารางสุดท้าย และข้อมูลจะได้รับการอัปเดตเสมอทันทีที่มีการเปลี่ยนแปลงใดๆ ในตารางต้นทาง
แน่นอนว่าวิธีนี้ยากกว่า แต่ถ้าคุณฝึกฝนจริง ๆ ทุกอย่างก็ไม่น่ากลัวนัก
этотметодоинизсамыхих, потомучтонесмотрянатовоиันทาล
การรวมกันของสูตรทางอ้อมและที่อยู่ทำงานอย่างไร
После того, как вы разобрались в том, как использовать совокупность этих формул для транспонирования таблицы, вам, возмомя боченицо, вам, возмоза боченико, возможно, как вы разобрались в том, как использовать разобрались в том, как использовать совокупность этих формул для транспонирования глубоко изучить принцип работы этого метода.
ฟังก์ชัน ทางอ้อม ในสูตรของเราใช้เพื่อสร้างการอ้างอิงเซลล์ทางอ้อม ตัวอย่างเช่น หากคุณต้องการระบุค่าเดียวกันในเซลล์ A8 เช่นเดียวกับใน B1 คุณสามารถเขียนสูตรได้
=ทางอ้อม(“B1”)
Казалось бы, зачем это делать? Ведь можно просто написать ссылку на ячейку в другой ячейке. Но преимущество этой функции в том, что в ссылку можно превратить абсолютно любую строку, и даже ту, которая содь сатя салина ругих формул. Собственно, это мы и делаем в формуле.
นอกเหนือจากฟังก์ชั่น ที่อยู่ ยังใช้ในสูตร คอลัมน์ и สาย. รายการแรกส่งกลับที่อยู่ของเซลล์ตามหมายเลขแถวและคอลัมน์ที่ทราบอยู่แล้ว การทำตามลำดับที่ถูกต้องเป็นสิ่งสำคัญที่นี่ ขั้นแรกให้ระบุแถวแล้วจึงระบุเฉพาะคอลัมน์เท่านั้น ตัวอย่างเช่น ฟังก์ชัน ที่อยู่(4;2) จะส่งคืนที่อยู่ $B$2
Следующая используемая выше функция – это คอลัมน์. จำเป็นที่นี่เพื่อให้สูตรได้รับหมายเลขคอลัมน์จากการอ้างอิงเฉพาะ ตัวอย่างเช่น หากคุณใช้พารามิเตอร์ B2 ในวงเล็บของฟังก์ชันนี้ ฟังก์ชันนี้จะคืนค่าตัวเลข 2 เนื่องจากคอลัมน์ที่สองคือคอลัมน์ B
แน่นอน ฟังก์ชัน ROW ทำงานในลักษณะเดียวกัน เพียงส่งคืนหมายเลขแถว
และตอนนี้เราจะไม่ใช้ตัวอย่างที่เป็นนามธรรม แต่เป็นสูตรเฉพาะที่ใช้ด้านบน:
=ทางอ้อม(ที่อยู่(คอลัมน์(A1),แถว(A1)))
ที่นี่คุณจะเห็นสิ่งนั้นเป็นอย่างแรกในฟังก์ชั่น ที่อยู่ มีการระบุคอลัมน์แล้วจึงระบุเฉพาะบรรทัด และนี่คือความลับของความสามารถในการทำงานของสูตรนี้ที่ซ่อนอยู่ เราจำได้ว่าฟังก์ชันนี้ทำงานในลักษณะมิเรอร์ และอาร์กิวเมนต์แรกในนั้นคือหมายเลขบรรทัด และปรากฎว่าเมื่อเราเขียนที่อยู่ของคอลัมน์ตรงนั้น มันจะกลายเป็นหมายเลขบรรทัดและในทางกลับกัน
Тоесть, если подытожить:
- เราได้รับหมายเลขคอลัมน์และแถวโดยใช้ฟังก์ชันที่เกี่ยวข้อง
- การใช้ฟังก์ชัน ที่อยู่ แถวจะกลายเป็นคอลัมน์และในทางกลับกัน
- ฟังก์ชัน ทางอ้อม ช่วยในการแสดงข้อมูลที่มิเรอร์ในเซลล์
นั่นเป็นวิธีที่ง่ายทั้งหมด!
การใช้มาโครเพื่อย้าย
แมโครเป็นโปรแกรมขนาดเล็ก สามารถใช้เพื่อทำให้กระบวนการเป็นไปโดยอัตโนมัติ สิ่งสำคัญคือต้องพิจารณาว่ามาโครมีข้อจำกัดบางประการ วิธี Transpose สูงสุดช่วยให้คุณทำงานกับองค์ประกอบ 65536 หากเกินขีดจำกัดนี้จะส่งผลให้ข้อมูลสูญหาย
Во остальном, это эффективный метод автоматизации, который сможет значительно облегчить жином.
ตัวอย่าง
ย่อย TransposeColumnsRows()
Dim SourceRange เป็นช่วง
Dim DestRange เป็นช่วง
ตั้งค่า SourceRange = Application.InputBox(Prompt:=»Please select the range to transpose», Title:=»Transpose Rows to Columns», Type:=8)
ตั้งค่า DestRange = Application.InputBox(Prompt:=»เลือกเซลล์ด้านซ้ายบนของช่วงปลายทาง», หัวข้อ:=»เปลี่ยนแถวเป็นคอลัมน์», ประเภท:=8)
SourceRange.คัดลอก
ระยะปลายทาง เลือก
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = เท็จ
ย่อยสิ้นสุด
Но если знаний в программировании особо нет, ничего страшного. Можно воспользоваться описанными выше способами. А потом учиться новому по мере освоения старого.