Nếu bạn đang có một vùng dữ liệu trên Excel và bạn muốn hoán vị – chuyển đổi dữ liệu hàng thành cột hoặc ngược lại thì bạn sẽ làm gì? Xóa đi nhập lại là cách mất nhiều thời gian của bạn.
Bạn đang xem: Đảo vị trí các dòng trong excel
Bài viết dưới đây sẽ hướng dẫn cho bạn thủ thuật hoán vị (Transpose) dữ liệu bằng các công cụ Paste Specials, hàm TRANSPOSE và công cụ Power Query cùng với ưu điểm và hạn chế của mỗi công cụ.
1. Hoán vị dữ liệu bằng Paste Special
Giả sử có cơ sở dữ liệu như dưới đây:
Dữ liệu này có thông tin về các Khu vực ở phần cột và các Quý trong năm ở phần hàng.
Các bược hoán đổi vị trí dòng và cột như sau:
Bước 1: Chọn vùng dữ liệu (A1:E5).
Bước 2: Bấm tổ hợp phím Ctrl + C hoặc chuột phải và chọn Copy
Bước 3: Dán dữ liệu cần được hoán vị tại vị trí một mới, sau đó ấn chuột phải và chọn Paste Special.
Bước 4: Trong hộp thoại Paste Special, chọn Transpose dưới góc phải và ấn OK.
Kết quả:
Lưu ý: Hoán vị bằng Paste Special nhanh và đơn giản, tuy nhiên dữ liệu được hoán vị sẽ được Paste Value và không thay đổi nếu dữ liệu gốc thay đổi.
2. Hoán vị dữ liệu trong Excel bằng hàm TRANSPOSE
Hàm TRANSPOSE là một công thức mảng dùng để hoán đổi vị trí dòng và cột
Bước 1: Chọn những ô mà chứa dữ liệu được hoán vị. Lưu ý rằng cần chọn chính xác số ô như dữ liệu gốc. Như ví dụ trên, ta có 5 cột và 5 dòng, vì vậy hãy chọn một vùng chứa 5 cột và 5 dòng.
Bước 2: Nhập hàm =TRANSPOSE(A1:E5) và ấn tổ hợp phím Control + Shift + Enter. Sau đó dữ liệu sẽ được hoán vị.
Lưu ý: Hàm TRANSPOSE sao chép giá trị của ô vì vậy dữ liệu được hoán vị sẽ thay đổi nếu dữ liệu gốc thay đổi. Một số hạn chế của hàm TRANSPOSE là không thể xóa đi một phần kết quả mà phải xóa toàn bộ kết quả trả về. Và khi có thêm dữ liệu (VD: thêm khu vực Africa thì hàm TRANSPOSE không tự động cập nhật dữ liệu mới được thêm vào).
3. Hoán vị dữ liệu trong Excel bằng công cụ Power Query
Được đánh giá là công cụ tốt nhất của Microsoft Excel trong 5 năm gần đây, Power Query giúp quá trình xử lý dữ liệu trở nên dễ dàng hơn nhờ ứng dụng Business Intelligence và tạo ra một số câu lệnh query cực kỳ tối ưu cho người dùng.
Đối với Excel 2016, Power Query có sẵn trong Excel dưới tên là Get & Transform trong tab Data
Các bước thực hiện Transpose bằng Power query như sau:
Bước 1: Chuyển dữ liệu gốc thành dạng Table
Chọn vùng dữ liệu, tab Home, Format as Table, hoặc nhấn phím tắt Ctrl + T và OK
Bước 2: Chọn 1 ô bất kỳ trong Table đã tạo, tab Data, From Table
Bước 3:
Trong giao diện Power Query Editor, tab Transform, Transpose
Chọn biểu tượng Table ở góc trên bên trái, Use First Row as Headers
Chọn Tab Home, Close & Load
Kết quả:
Nếu có dữ liệu mới được thêm vào dữ liệu gốc như hình dưới
Cập nhật dữ liệu mới bằng cách nhấn chuột phải chọn Refresh, các thao tác Transpose trong Power Query sẽ được thực hiện.
Lưu ý: thao tác Transpose trong Power Query chỉ cần thực hiện 1 lần và khi dữ liệu thay đổi hoặc thêm dòng/cột mới, chỉ cần Refresh để cập nhật dữ liệu mới nhất.
Trong bài viết này, shthcm.edu.vn Học Excel sẽ hướng dẫn các bạn các phương pháp hoán đổi vị trí các cột trong bảng dữ liệu – đổi vị trí cột trong excel: di chuyển cột bằng cách kéo thả chuột, di chuyển nhiều cột không liền kề cùng một lúc.
Cách di chuyển một cột trong bảng tính Excel:
Cut & Paste:
Nếu bạn không muốn thao tác trên con trỏ chuột, bạn có thể thay đổi thứ tự các cột bằng cách cắt dán chúng. Hãy nhớ rằng có một vài đặc điểm phụ thuộc vào việc bạn muốn di chuyển một cột hay nhiều cột cùng lúc.Chọn toàn bộ cột bằng cách nhấp vào tiêu đề của cột.Cắt cột được chọn bằng cách nhấn Ctrl + X, hoặc nhấp phải vào cột và chọn Cut từ danh sách tùy chọn. Bạn có thể bỏ qua bước 1 và đơn giản nhấp phải vào tiêu đề cột để chọn Cut.Chọn cột nằm trước nơi bạn muốn chèn cột được cắt, nhấp phải vào nó và chọn Insert Cut Cells từ danh sách tùy chọn.
Nếu bạn muốn sử dụng phím tắt, bạn có thể thực hiện theo những cách sau:
Chọn bất kỳ ô nào trong cột và nhấn Ctrl + Space để chọn toàn bộ cột
Nhấn Ctrl + X để cắt cột
Chọn cột nằm trước nơi bạn muốn dán cột vừa cắt
Nhấn Ctrl + + trên phím số để chèn cột
Cách di chuyển nhiều cột trong Excel:
Phương pháp cắt/dán sẽ hiệu quả đối với một cột, nhưng không cho phép bạn thao tác với nhiều cột cùng lúc. Nếu bạn cố thực hiện điều này, thông báo lỗi sẽ hiện ra yêu cầu bạn chọn dãy dữ liệu đơn lẻ: The command you chose cannot be performed with multiple selections. (Lệnh bạn yêu cầu không thể thực hiện với nhiều cột được chọn.)
Nếu bạn cần đặt lại thứ tự một vài cột trong trang tính, bạn có các lựa chọn sau đây:
Sử dụng chuột để kéo thả nhiều cột (đây là cách nhanh nhất)Cắt/dán từng cột (không thể áp dụng di chuyển nhiều cột cùng lúc)Sao chép, dán và xóa (cho phép di chuyển nhiều cột liền kề cùng một lúc)Dùng VBA MACROCách kéo thả cột trong Excel:
Giả sử bạn có một trang tính với các thông tin về sản phẩm công ty bạn và bạn muốn thay đổi vị trí các cột. Tôi sẽ sử dụng bảng giá Able
Bits cho ví dụ này. Những gì tôi muốn là hoán vị cột “License type” và “Product ID” để ID sản phẩm xuất hiện ngay sau tên sản phẩm.
Bạn có thể thao tác tương tự để kéo thả nhiều cột trong bảng tính Excel. Để chọn nhiều cột, nhấp vào tiêu đề cột đầu tiên bạn cần di chuyển, nhấn giữ phím Shift và nhấn tiêu đề cột cuối cùng. Sau đó làm theo bước 2 đến bước 4 đã hướng dẫn ở trên để di chuyển cột.
Chú ý. Bạn không thể kéo thả các cột hoặc các hàng không liền kề trong trang tính Excel, thậm chí trong Excel 2013.
Phương pháp kéo thả chỉ có tác dụng trong Excel 2013, 2010, 2007 và có thể sử dụng cho di chuyển các hàng.
Copy => Paste => Delete
Nếu kéo thả các cột bằng chuột không hiệu quả đối với bạn, hãy thử sắp xếp nhiều cột trong bảng tính Excel theo cách sau:
Chọn các cột bạn muốn di chuyển (nhấn vào tiêu đề cột đầu tiên, nhấn Shift sau đó nhấp vào tiêu đề cột cuối cùng)Một cách khác là chỉ chọn tiêu đề các cột được di chuyển, sau đó nhấn Ctrl + Space. Cách này chỉ chọn những ô tính chứa dữ liệu hơn là chọn toàn bộ cột, như trong hình dưới đây:
Chú ý. Nếu bạn sắp xếp lại các cột trong một vùng dữ liệu, bạn có thể thực hiện theo cách nào cũng được. Nếu bạn muốn hoán đổi vị trí một vài cột trong toàn bộ bảng tính Excel, hãy chọn cột bằng cách thứ 2 (chỉ những ô tính chứa dữ liệu). Nếu thao tác theo cách 1, bạn có thể mắc lỗi “The operation is not allowed. The operation is attempting to shift cells in a table of your worksheet” (Thao tác không được phép. Hoạt động này đang cố gắng thay đổi các ô trong bảng tính của bạn.)
Sao chép các cột được chọn bằng cách nhấn Ctrl + C, nhấp phải vào các cột và chọn Copy.Chọn cột nằm trước vị trí bạn muốn chèn cột vừa sao chép, nhấp phải vào nó và chọn Insert Copied Cells hoặc nhấn Ctrl + + trên phím số.
Đương nhiên, quá trình này dài hơn việc kéo thả các cột, nhưng nó hữu ích cho những ai thích dùng phím tắt hơn thao tác với chuột. Đáng tiếc là, nó cũng không thể áp dụng cho các cột không liền kề.
Xem thêm: Cách tính số điện trên công tơ, cách tính tiền điện theo công tơ
Dùng VBA Macro:
Nếu bạn có một số kiến thức về VBA, bạn có thể thử viết Macro để tự động di chuyển các cột trong bảng Excel. Đây là lý thuyết. Trong thực tế, việc xác định chính xác cột cần hoán đổi và vị trí mới của chúng sẽ tốn nhiều thời gian hơn kéo thả chúng một cách thủ công. Bên cạnh đó, không có đảm bảo rằng Macro sẽ luôn hoạt động theo kết quả bạn mong muốn và với mỗi lần thực hiện, bạn đều phải xác minh lại kết quả. Nhìn chung, Macro VBA dường như không phải một công cụ phù hợp cho nhiệm vụ này.
Như vậy với bài viết này, mình đã giới thiệu đến các bạn cách hoán đổi vị trí các cột trong Excel – đổi vị trí cột trong excel một cách đơn giản nhất. Để có thể ứng dụng tốt Excel vào trong công việc, chúng ta không chỉ nắm vững được các hàm mà còn phải sử dụng tốt cả các công cụ của Excel. Những hàm nâng cao giúp áp dụng tốt vào công việc như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những công cụ thường sử dụng là Data validation, Conditional formatting, Pivot table…