Làm cách nào để chuyển đổi excel thành cột?

meme nói.
31 tháng 1 năm 2018 lúc 4. 43 giờ chiều

Xin chào
Tôi có một tệp rất lớn lên tới 30000 hàng. Tôi cần trích xuất một cột dựa trên một cột khác và chuyển đổi vị trí. Tôi đã cài đặt công cụ của bạn nhưng nó không hiển thị trình hướng dẫn như được mô tả. Tôi cần chuyển đổi bên dưới nhưng các bảng tổng hợp không giúp được gì. công cụ của bạn sẽ giúp được gì?

DocN DocTitle
1006 ABCE Pathway fred
1006 ABCE Pathway mardy
1006 ABCE Pathway pod
1006 ABCE Pathway pony
1006 ABCE Pathway mouse
1007 HIJK Guidance veg
1007 HIJK Guidance meat
1007 HIJK Guidance biscuits
1007 HIJK Guidance honey
1029 Pod Form green
1029 Pod Form blue
1029 Pod Form red
1029 Pod Form yellow
1029 Pod Form blue box
1029 Pod Form hat
1019 Pod Fod green wall
1019 Pod Fod blue
1019 Pod Fod red ball
1019 Pod Fod yellow
1019 Pod Fod blue box
1019 Pod Fod hat
1019 Pod Fod bluey

DocN DocTitle
1006 ABCE Pathway fred mardy pod pony mouse
1007 Hướng dẫn HIJK Bánh quy thịt rau mật ong
1029
1019 Pod Fod green wall blue red ball yellow blue box hat bluey

Đáp lại

Tất cả chúng ta nhìn vào dữ liệu khác nhau. Ví dụ: một số người tạo bảng tính Microsoft Excel với các trường chính nằm ngang. Những người khác thích dữ liệu được lật theo chiều dọc trong các cột. Đôi khi những tùy chọn này dẫn đến tình huống bạn muốn chuyển đổi dữ liệu. Trong hướng dẫn này, tôi sẽ chỉ cho bạn cách chuyển đổi hàng và cột bằng tính năng Chuyển vị trí

Nội dung hướng dẫn

• Vấn đề Bố cục
• Hàm Transpose của Excel là gì
• Chuyển đổi cột thành hàng bằng Paste Special
• Sử dụng Hàm Chuyển vị trong Công thức
• Sự khác biệt trong các tùy chọn chuyển vị
• Chuyển đổi công thức & ô trống
• Chỉ cho tôi cách làm video
• Tài nguyên hướng dẫn

Vấn đề Bố cục

Gần đây tôi đã nhận được một bảng tính Microsoft Excel lớn chứa thông tin đánh giá nhà cung cấp. Thông tin hữu ích nhưng tôi không thể sử dụng các công cụ như Bộ lọc tự động do cách tổ chức dữ liệu. Tôi cũng sẽ gặp sự cố nếu cần nhập bảng nguồn ban đầu vào cơ sở dữ liệu. Một ví dụ đơn giản được hiển thị dưới đây

Bảng tính Excel cần hoán đổi cột và hàng

Thay vào đó, tôi muốn tên Công ty được hiển thị theo chiều dọc trong Cột A và Thuộc tính Dữ liệu được hiển thị theo chiều ngang trong Hàng 1. Điều này sẽ giúp tôi dễ dàng hơn trong việc phân tích. Ví dụ: tôi không thể dễ dàng lọc các nhà cung cấp ở California bằng bảng gốc

Hàm Transpose của Excel là gì

Nói một cách đơn giản, tính năng chuyển vị sẽ thay đổi hướng của cột [phạm vi dọc] và hàng [phạm vi ngang] của bạn. Các hàng dữ liệu ban đầu của tôi sẽ trở thành các cột và các cột của tôi sẽ trở thành các hàng. Vì vậy, điều này khắc phục sự cố bố cục của tôi ở trên mà không cần phải nhập lại dữ liệu gốc của mình. Và hệ thống sẽ điều chỉnh nhãn cột

Chức năng chuyển đổi khá linh hoạt. Ví dụ: bạn có thể sử dụng tính năng này trong công thức Excel hoặc với các tùy chọn dán. Tuy nhiên, có một số khác biệt dựa trên phương pháp bạn sử dụng

Chuyển đổi cột thành hàng bằng Paste Special

Các bước nêu dưới đây được thực hiện bằng Microsoft Office 365, nhưng các phiên bản Microsoft Excel gần đây sẽ hoạt động

1. Mở bảng tính bạn cần thay đổi. Bạn cũng có thể tải xuống bảng ví dụ ở cuối hướng dẫn này
2. Nhấp vào ô đầu tiên trong phạm vi ô của bạn, chẳng hạn như A1
3. Giữ Shift khi bấm vào ô cuối cùng của phạm vi. Tập dữ liệu của bạn sẽ làm nổi bật
4. Từ tab Trang chủ, chọn Sao chép hoặc nhập Ctrl + c

Sao chép dữ liệu Excel hiện tại

1. Chọn ô mới nơi bạn muốn sao chép dữ liệu đã chuyển đổi của mình
2. Nhấp chuột phải vào ô đó và chọn biểu tượng Chuyển vị từ Tùy chọn Dán

Chọn tùy chọn Chuyển đổi

✪ Khi di chuột qua các tùy chọn Dán, bạn có thể thấy bố cục dữ liệu thay đổi.

Bây giờ bạn sẽ thấy Excel đã chuyển đổi các cột và hàng. Bạn có thể thay đổi kích thước các cột cho phù hợp với nhu cầu của mình

Hai bộ dữ liệu này là độc lập. Bạn có thể xóa các ô khỏi tập hợp trên cùng và việc này sẽ không ảnh hưởng đến tập hợp đã chuyển đổi

Dữ liệu được chuyển đổi theo bản gốc

Khi sử dụng phương pháp này, định dạng ban đầu của bạn được duy trì. Ví dụ: nếu tôi đã thêm nền màu vàng vào các ô ban đầu của mình B1. G1, cùng một màu nền sẽ được áp dụng. Điều này cũng đúng nếu tôi sử dụng chữ màu đỏ trên ô E. 5

Sử dụng Hàm Chuyển vị trong Công thức

Như tôi đã đề cập, Excel có nhiều cách để bạn chuyển cột thành hàng hoặc ngược lại. Cách thứ hai này sử dụng một công thức và mảng. Kết quả giống nhau ngoại trừ dữ liệu gốc của bạn và dữ liệu chuyển đổi mới được liên kết. Do đó, bạn có thể mất một số định dạng ban đầu. Ví dụ: văn bản và kiểu có màu xuất hiện nhưng không phải màu tô của ô

1. Mở trang tính Excel của bạn
2. Nhấp vào một ô trống nơi bạn muốn dữ liệu được chuyển đổi của mình. Mình đang dùng A7
3. Loại = chuyển đổi

Gõ công thức chuyển vị

✪ Lưu ý cách Excel cung cấp chú giải công cụ hiển thị cho Chuyển vị – “Chuyển đổi phạm vi ô dọc thành phạm vi ô ngang và ngược lại“.

1. Kết thúc công thức bằng cách thêm một [ và đánh dấu phạm vi ô mà chúng tôi muốn hoán đổi
2. Nhập ] để đóng phạm vi

Chuyển đổi công thức và phạm vi

1. nhấn Enter

✪ Nếu không sử dụng Microsoft 365, bạn có thể cần phải nhấn CTRL + SHIFT + Enter

Sự khác biệt trong các tùy chọn chuyển vị

Trong khi hai phương pháp này tạo ra kết quả tương tự, có sự khác biệt. Trong phương pháp dán đầu tiên, bất kỳ hành động nào tôi thực hiện trên một ô đều độc lập với phiên bản được chuyển đổi. Tôi có thể xóa các giá trị ban đầu và sẽ không có gì xảy ra với các cột mà tôi đã hoán đổi

Ngược lại, phiên bản công thức Transpose được kết nối với dữ liệu gốc. Vì vậy, ví dụ: nếu tôi thay đổi giá trị trong B2 từ 1200 thành 1500, giá trị mới sẽ tự động cập nhật trong B8. Tuy nhiên, điều ngược lại không đúng. Nếu tôi thay đổi bất kỳ ô được chuyển đổi nào, tập hợp ban đầu sẽ không thay đổi. Thay vào đó, tôi sẽ gặp lỗi #SPILL và dữ liệu đã chuyển đổi của tôi sẽ biến mất

Lỗi khi thay đổi ô đã chuyển

Chuyển đổi công thức & ô trống

Một điểm khác biệt nữa khi sử dụng công thức Transpose là nó sẽ chuyển đổi các ô trống thành “0”

Chuyển đổi các khoảng trống đã chuyển đổi thành 0

Cách khắc phục cho vấn đề này là sử dụng câu lệnh IF cho công thức giữ các ô trống ở dạng trống

=TRANSPOSE[IF[A1:G5="","",A1:G5]]

Ngoài ra, bạn có thể thực hiện tìm kiếm và thay thế trên các số không

Bây giờ chúng tôi đã chỉ cho bạn cách chuyển đổi dữ liệu trong Excel, hãy thử thử với bảng tính thực hành bên dưới. Bạn sẽ nhanh chóng hoán đổi các hàng và cột của mình trong Excel. Và trong khi bạn có thể làm việc với nhiều hàng, bạn có thể chuyển đổi một cột Excel thành hàng