Lật ngược dữ liệu trong Excel (đảo ngược thứ tự dữ liệu theo cột/hàng)

Đôi khi, bạn có thể cần lật ngược dữ liệu trong Excel, tức là đảo ngược thứ tự của dữ liệu trong tập dữ liệu dọc và đảo ngược thứ tự dữ liệu từ trái sang phải trong tập dữ liệu ngang.

Bây giờ, nếu bạn nghĩ rằng phải có một hàm tích hợp sẵn trong Excel để làm điều này, tôi e rằng bạn sẽ thất vọng.

Mặc dù có một số cách để lật dữ liệu trong Excel, nhưng không có chức năng nào được tích hợp sẵn.Nhưng bạn có thể dễ dàng làm điều đó với các thủ thuật sắp xếp đơn giản, công thức hoặc VBA.

Trong hướng dẫn này, tôi sẽ chỉ cho bạn cách lật ngược dữ liệu trong hàng, cột và bảng trong Excel.

Vậy hãy bắt đầu!

Lật dữ liệu bằng cách sử dụng SORT và các cột phụ trợ

Một trong những cách dễ nhất để đảo ngược thứ tự dữ liệu trong Excel là sử dụng cột trợ giúp và sau đó sử dụng cột trợ giúp đó để sắp xếp dữ liệu.

Lật dữ liệu theo chiều dọc (thứ tự ngược lại)

Giả sử bạn có một cột như bên dướiTập dữ liệu tênvà bạn muốn lật dữ liệu này:

Dưới đây là các bước để lật dữ liệu theo chiều dọc:

1. Trong cột liền kề, hãy nhập "Người trợ giúp" làm tiêu đề cột
2. Trong cột trợ giúp, hãy nhập một chuỗi số (1, 2, 3, v.v.).Bạn có thể thực hiện việc này một cách nhanh chóng bằng cách sử dụng phương pháp được hiển thị ở đây
3. Chọn toàn bộ tập dữ liệu, bao gồm cả các cột phụ trợ
4. Nhấp vào tab Dữ liệu
5. Nhấp vào biểu tượng sắp xếp
6. Trong hộp thoại Sắp xếp, chọn Người trợ giúp từ trình đơn thả xuống Sắp xếp theo
7. Trong menu thả xuống theo thứ tự, hãy chọn "Lớn đến Nhỏ"
8. Bấm OK

Các bước trên sẽ sắp xếp dữ liệu dựa trên các giá trị cột trợ giúp, điều này cũng sẽ khiến các tên trong dữ liệu có thứ tự ngược lại.

Vui lòng thả cột trợ giúp khi bạn hoàn tất.

Trong ví dụ này, tôi chỉ cho bạn cách lật dữ liệu khi chỉ có một cột, nhưng bạn có thể sử dụng kỹ thuật tương tự nếu bạn có toàn bộ bảng.Chỉ cần đảm bảo chọn toàn bộ bảng, sau đó sử dụng cột trợ giúp để sắp xếp dữ liệu theo thứ tự giảm dần.

Lật dữ liệu theo chiều ngang

Bạn cũng có thể lật dữ liệu theo chiều ngang trong Excel theo cách tương tự.

Excel có tùy chọn sắp xếp dữ liệu theo chiều ngang bằng cách sử dụng hộp thoại Sắp xếp (tính năng Sắp xếp Trái sang Phải).

Giả sử bạn có một bảng như hình dưới đây và bạn muốn lật dữ liệu này theo chiều ngang.

Dưới đây là các bước để thực hiện việc này:

1. Trong hàng bên dưới, hãy nhập "Người trợ giúp" làm tiêu đề của hàng
2. Trong hàng trợ giúp, hãy nhập một chuỗi số (1, 2, 3, v.v.).
3. Chọn toàn bộ tập dữ liệu, bao gồm các hàng phụ trợ
4. Nhấp vào tab Dữ liệu
5. Nhấp vào biểu tượng sắp xếp
6. Trong hộp thoại Sắp xếp, bấm vào nút Tùy chọn.
7. Trong hộp thoại mở ra, nhấp vào Sắp xếp từ trái sang phải
8. Bấm OK
9. Trong danh sách thả xuống Sắp xếp Theo, hãy chọn Hàng 3 (hoặc bất kỳ hàng nào có cột trợ giúp của bạn)
10. Trong menu thả xuống theo thứ tự, hãy chọn "Lớn đến Nhỏ"
11. Bấm OK

Các bước trên sẽ lật toàn bộ bảng theo chiều ngang.

Khi hoàn tất, bạn có thể xóa dòng trợ giúp.

Sử dụng Excel để đảo ngược công thứclật dữ liệu

Microsoft 365 có một số công thức mới giúp bạn dễ dàng đảo ngược thứ tự của cột hoặc bảng trong Excel.

Trong phần này, tôi sẽ chỉ cho bạn cách thực hiện việc này bằng công thức SORTBY (nếu bạn sử dụng Microsoft 365) hoặc công thức INDEX (nếu bạn không sử dụng Microsoft 365)

Sử dụng chức năng SORTBY (khả dụng trong Microsoft 365)

Giả sử bạn có một bảng như sau và bạn muốn lật dữ liệu trong bảng đó:

Để thực hiện việc này, trước tiên, hãy sao chép các tiêu đề và đặt chúng ở nơi bạn muốn lật bảng

Bây giờ sử dụng công thức sau bên dưới ô tiêu đề ngoài cùng bên trái:

=SORTBY($A$2:$B$12,ROW(A2:A12),-1)

Công thức trên sắp xếp dữ liệu và sắp xếp dựa trên kết quả của hàm ROW.

Trong trường hợp này, hàm ROW sẽ trả về một mảng số đại diện cho số hàng giữa phạm vi được chỉ định (trong ví dụ này là một chuỗi số, chẳng hạn như 2, 3, 4, v.v.).

Vì tham số thứ ba của công thức này là -1, nên nó buộc công thức phải sắp xếp dữ liệu theo thứ tự giảm dần.

Bản ghi có số hàng cao nhất sẽ ở trên cùng và bản ghi có số quy tắc thấp nhất sẽ ở dưới cùng, về cơ bản đảo ngược thứ tự của dữ liệu.

Sau khi hoàn tất, bạn có thể chuyển đổi công thức thành một giá trị để có được một bảng tĩnh.

Sử dụng hàm chỉ mục

Nếu bạn không thể sử dụng hàm SORTBY, đừng lo lắng - bạn có thể sử dụng hàm INDEX tuyệt vời.

Giả sử bạn có một tập dữ liệu gồm các tên như hình bên dưới và bạn muốn lật dữ liệu này.

Đây là công thức để làm điều này:

= INDEX ($ A $ 2: $ A $ 12, ROWS (A2: $ A $ 12))

 Công thức này hoạt động như thế nào?

Công thức trên sử dụng hàm INDEX, hàm này sẽ trả về giá trị từ ô dựa trên số được chỉ định trong tham số thứ hai.

Điều kỳ diệu thực sự xảy ra khi tôi sử dụng tham số thứ hai của hàm ROWS.

Vì tôi đã khóa phần thứ hai của tham chiếu trong hàm ROWS, nên trong ô đầu tiên, nó sẽ trả về số hàng giữa A2 và A12, là 11.

Nhưng khi nó đi xuống hàng, tham chiếu đầu tiên sẽ thay đổi thành A3, sau đó là A4, v.v., trong khi tham chiếu thứ hai sẽ giữ nguyên vì tôi đã khóa nó và biến nó thành tuyệt đối.

Khi chúng ta đi xuống, kết quả của hàm ROWS sẽ giảm 1, từ 11 xuống 10 xuống 9, v.v.

Vì hàm INDEX trả về giá trị dựa trên số trong đối số thứ hai, điều này kết thúc cho chúng ta dữ liệu theo thứ tự ngược lại.

Bạn có thể sử dụng cùng một công thức ngay cả khi có nhiều cột trong tập dữ liệu.Tuy nhiên, bạn phải chỉ định tham số thứ hai, tham số này sẽ chỉ định số cột mà từ đó dữ liệu cần được tìm nạp.

Giả sử bạn có một tập dữ liệu giống như thế này và bạn muốn đảo ngược thứ tự của toàn bộ bảng:

Đây là công thức sẽ làm điều này cho bạn:

=INDEX($A$2:$B$12,ROWS(A2:$A$12),COLUMNS($A$2:A2))

Đây là một công thức tương tự, trong đó tôi cũng đã thêm một tham số thứ ba chỉ định số cột mà từ đó giá trị sẽ được lấy.

Để làm cho công thức này động, tôi đã sử dụng hàm COLUMNS, hàm này sẽ tiếp tục thay đổi giá trị cột từ 1 đến 2 thành 3 khi bạn sao chép nó sang bên phải.

Sau khi hoàn tất, bạn có thể chuyển đổi công thức thành một giá trị để đảm bảo kết quả tĩnh.

Lưu ý: Khi bạn sử dụng công thức để đảo ngược thứ tự của tập dữ liệu trong Excel, nó sẽ không giữ nguyên định dạng ban đầu.Nếu bạn cũng cần định dạng ban đầu của dữ liệu được sắp xếp, bạn có thể áp dụng nó theo cách thủ công hoặc sao chép và dán định dạng từ tập dữ liệu gốc vào tập dữ liệu được sắp xếp mới

Lật dữ liệu bằng VBA

Nếu lật dữ liệu trong Excel là việc bạn phải làm thường xuyên, bạn cũng có thể thử phương pháp VBA.

Sử dụng mã macro VBA, bạn có thể sao chép và dán nó một lần vào sổ làm việc trong trình chỉnh sửa VBA, sau đó sử dụng lại nhiều lần trong cùng một sổ làm việc.

Bạn cũng có thể lưu mã trong sổ làm việc macro cá nhân hoặc dưới dạng bổ trợ Excel và có thể sử dụng mã này trong bất kỳ sổ làm việc nào trên hệ thống của bạn.

Dưới đây là mã VBA để lật dữ liệu đã chọn theo chiều dọc trong trang tính.

Sub FlipVerically ()

Làm mờ hàng trên cùng dưới dạng biến thể
Làm mờ LastRow như một biến thể
Dim StartNum dưới dạng số nguyên
Dim EndNum dưới dạng số nguyên

Application.ScreenUpdating = Sai

Số bắt đầu = 1
EndNum = Lựa chọn.Hàng.Đếm

Do While StartNum <EndNum
TopRow = Selection.Rows (StartNum)
LastRow = Selection.Rows (EndNum)
Selection.Rows (EndNum) = TopRow
Selection.Rows (StartNum) = LastRow
Số bắt đầu = Số bắt đầu + 1
EndNum = EndNum - 1
Vòng lặp

Application.ScreenUpdating = Đúng
End Sub

Để sử dụng mã này, trước tiên bạn cần chọn tập dữ liệu bạn muốn đảo ngược (không bao gồm tiêu đề), sau đó chạy mã này.

Mã này hoạt động như thế nào?

Đoạn mã trên trước tiên đếm tổng số hàng trong tập dữ liệu và gán nó cho biến EndNum.

Sau đó, nó sử dụng vòng lặp Do While để sắp xếp dữ liệu.

Cách nó sắp xếp dữ liệu này là lấy hàng đầu tiên và hàng cuối cùng và hoán đổi chúng.Sau đó, nó di chuyển đến hàng thứ hai của hàng áp chót và sau đó hoán đổi chúng.Sau đó, nó di chuyển đến hàng thứ ba từ hàng thứ ba đến hàng cuối cùng, v.v.

Vòng lặp kết thúc khi tất cả việc sắp xếp hoàn tất.

Nó cũng sử dụng thuộc tính Application.ScreenUpdating và đặt nó thành FALSE khi mã đang chạy, sau đó chuyển nó trở lại TRUE khi mã chạy xong.

Điều này đảm bảo rằng bạn không thấy các thay đổi xảy ra trên màn hình trong thời gian thực và cũng đẩy nhanh quá trình.

Làm thế nào để sử dụng mã?

Sao chép và dán mã này vào trình soạn thảo VB như sau:

1. Mở tệp Excel nơi bạn muốn thêm mã VBA
2. Giữ ALT và nhấn F-11 (bạn cũng có thể chuyển đến tab nhà phát triển và nhấp vào biểu tượng Visual Basic)
3. Trong trình chỉnh sửa Visual Basic mở ra, sẽ có Trình khám phá dự án ở bên trái của trình chỉnh sửa VBA.Nếu bạn không thấy nó, hãy nhấp vào tab View, sau đó nhấp vào Project Explorer
4. Bấm chuột phải vào bất kỳ đối tượng nào của sổ làm việc mà bạn muốn thêm mã
5. Chuyển đến tùy chọn Chèn và nhấp vào Mô-đun.Điều này sẽ thêm một mô-đun mới vào sổ làm việc
6. Bấm đúp vào biểu tượng mô-đun trong Project Explorer.Thao tác này sẽ mở cửa sổ mã cho mô-đun đó
7. Sao chép và dán mã VBA ở trên vào cửa sổ mã

Để chạy mã macro VBA, trước tiên hãy chọn tập dữ liệu để lật (ngoại trừ tiêu đề).

Với dữ liệu đã chọn, hãy chuyển đến trình soạn thảo VB và nhấp vào nút phát màu xanh lục trên thanh công cụ hoặc chọn bất kỳ dòng nào trong mã và nhấn F5

Vì vậy, đây là một số cách bạn có thể lật dữ liệu trong Excel (tức là đảo ngược thứ tự của tập dữ liệu).

Tất cả các phương pháp tôi đã trình bày trong hướng dẫn này (công thức, hàm SORT và VBA) có thể được sử dụng để lật dữ liệu theo chiều dọc và chiều ngang (bạn phải điều chỉnh công thức và mã VBA cho phù hợp để lật dữ liệu theo chiều ngang).

Tôi hy vọng bạn thấy hướng dẫn này hữu ích.