Đôi khi, bạn có thể cần phải lật 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à từ trái sang phải trong tập dữ liệu ngang.
Hướng dẫn này sẽ chỉ cho bạn cách lật dữ liệu theo hàng, cột và bảng trong Excel.
Lật dữ liệu sử dụng SORT và cột trợ giúp
Một trong những cách dễ nhất để đảo ngược thứ tự của dữ liệu trong Excel là sử dụng cột trợ giúp và sau đó sử dụng cột đó để sắp xếp dữ liệu.
Lật dữ liệu theo chiều dọc
Giả sử bạn có một tập dữ liệu tên trong một cột như được hiển thị bên dưới và 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:
- Trong cột liền kề, hãy nhập ‘Helper’ làm tiêu đề cho cột
- Trong cột Helper, hãy nhập một chuỗi số [1,2,3, v.v.]. Bạn có thể sử dụng các phương pháp được giới thiệu ở đây để thực hiện việc này một cách nhanh chóng
- Chọn toàn bộ tập dữ liệu bao gồm cả cột Helper
- Nhấp vào tab Data
- Nhấp vào icon Sort
- Trong hộp thoại Sort, chọn ‘Helper’ trong ‘Sort by’
- Trong phần Order, chọn ‘Largest to Smallest’
- Nhấp OK
Các bước trên sẽ sắp xếp dữ liệu dựa trên các giá trị của cột Helper, điều này cũng sẽ dẫn đến việc đảo ngược thứ tự của cột tên trong dữ liệu.
Sau khi hoàn tất, hãy xóa cột Helper.
Trong ví dụ này, tôi đã chỉ cho bạn cách lật dữ liệu khi bạn chỉ có một cột, nhưng bạn cũng có thể sử dụng kỹ thuật tương tự nếu bạn có một bảng. Chỉ cần đảm bảo rằng bạn chọn toàn bộ bảng và 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àm theo cùng một phương pháp để lật dữ liệu theo chiều ngang trong Excel.
Excel có một 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 Sort [tính năng ‘Sort left to right’].
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:
- Trong hàng phía dưới, hãy nhập ‘Helper’ làm tiêu đề cho hàng
- Trong hàng Helper, hãy nhập một chuỗi số [1,2,3, v.v.].
- Chọn toàn bộ tập dữ liệu bao gồm hàng Helper
- Nhấp vào tab Data
- Nhấp vào biểu tượng Sort
- Trong hộp thoại Sort, nhấp vào nút Options.
- Trong hộp thoại vừa mở ra, nhấp chọn ‘Sort left to right’
- Nhấp OK
- Trong phần Sort by, chọn Row 3 [hoặc bất kỳ hàng nào có cột Helper của bạn]
- Trong phần Order, chọn ‘Largest to Smallest’
- Nhấp OK
Các bước trên sẽ lật toàn bộ bảng theo chiều ngang.
Sau khi hoàn tất, bạn có thể xóa hàng Helper.
Lật dữ liệu bằng công thức
Microsoft 365 đã có một số công thức mới giúp đảo ngược thứ tự của cột hoặc bảng trong Excel thực sự dễ dàng.
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 đang 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 công thức SORTBY [Microsoft 365]
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 trong bảng này:
Để làm điều 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ờ, hãy sử dụng công thức sau bên dưới ô trong 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à bằng cách sử dụng kết quả của hàm ROW làm cơ sở để sắp xếp.
Hàm ROW trong trường hợp này sẽ trả về một mảng số đại diện cho các số hàng ở giữa phạm vi được chỉ định [trong ví dụ này sẽ là một chuỗi số như 2, 3, 4, v.v.].
Và vì đối số thứ ba của công thức này là -1, nó sẽ buộc công thức sắp xếp dữ liệu theo thứ tự giảm dần.
Dữ liệu có số hàng cao nhất sẽ ở trên cùng và dữ liệu có số hàng 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 các giá trị để có được một bảng tĩnh.
Sử dụng hàm INDEX
Trong trường hợp bạn không có quyền truy cập vào hàm SORTBY, đừng lo – bạn có thể sử dụng hàm INDEX.
Giả sử bạn có một tập dữ liệu tên như hình dưới đây và bạn muốn lật dữ liệu này.
Dưới đâ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 sẽ trả về giá trị từ ô dựa trên số được chỉ định trong đối số thứ hai.
Điều kỳ diệu thực sự xảy ra trong đối số thứ hai mà tôi đã sử dụng hàm ROWS.
Vì tôi đã khóa phần thứ hai của tham chiếu trong hàm ROWS, trong ô đầu tiên, nó sẽ trả về số hàng giữa A2 và A12, sẽ là 11.
Nhưng khi nó đi xuống các hàng, tham chiếu đầu tiên sẽ thay đổi thành A3, rồi đến A4, v.v., trong khi tham chiếu thứ hai sẽ vẫn như cũ vì tôi đã khóa nó và đặt nó là tuyệt đối.
Khi chúng ta đi xuống các hàng, kết quả của hàm ROWS sẽ giảm 1, từ 11 xuống 10 xuống 9, v.v.
Và vì hàm INDEX trả về cho chúng ta giá trị dựa trên số trong đối số thứ hai, điều này cuối cùng sẽ cung cấp 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 bạn có nhiều cột trong tập dữ liệu. tuy nhiên, bạn sẽ phải chỉ định đối số thứ hai 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 như được hiển thị bên dưới và bạn muốn đảo ngược thứ tự của toàn bộ bảng:
Dưới đây là công thức sẽ làm điều đó 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 đó đã thêm đối số thứ ba chỉ định số cột mà từ đó giá trị sẽ được tìm nạp.
Để làm cho công thức này động, tôi đã sử dụng hàm COLUMNS 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 giá trị để đảm bảo rằng bạn có 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 cho 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 cách sử dụng VBA
Nếu lật dữ liệu trong Excel là việc bạn phải làm khá thường xuyên, bạn cũng có thể thử phương pháp VBA.
Với code macro VBA, bạn có thể sao chép và dán nó một lần trong sổ làm việc trong VBA Editor, 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 code trong Personal Macro Workbook hoặc dưới dạng add-in Excel và có thể sử dụng code 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à code VBA sẽ lật dữ liệu đã chọn theo chiều dọc trong trang tính.
Sub FlipVerically[]
Dim TopRow As Variant
Dim LastRow As Variant
Dim StartNum As Integer
Dim EndNum As Integer
Application.ScreenUpdating = False
StartNum = 1
EndNum = Selection.Rows.Count
Do While StartNum < EndNum
TopRow = Selection.Rows[StartNum]
LastRow = Selection.Rows[EndNum]
Selection.Rows[EndNum] = TopRow
Selection.Rows[StartNum] = LastRow
StartNum = StartNum + 1
EndNum = EndNum – 1
Loop
Application.ScreenUpdating = True
End Sub
Để sử dụng code này, trước tiên bạn cần thực hiện lựa chọn tập dữ liệu mà bạn muốn đảo ngược [không bao gồm các tiêu đề], rồi chạy code này.
Code này hoạt động như thế nào?
Đoạn code trên trước tiên đếm tổng số hàng trong tập dữ liệu và gán giá trị đó 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ứ 2 trong hàng thứ hai cuối cùng và sau đó hoán đổi chúng. Sau đó, nó di chuyển đến hàng thứ ba trong hàng thứ ba 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 trong khi chạy code, sau đó chuyển nó trở lại TRUE khi code đã 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 trong thời gian thực trên màn hình của mình, điều này cũng giúp tăng tốc quá trình.
Làm thế nào để sử dụng Code?
Làm theo các bước dưới đây để sao chép và dán code này vào VB Editor:
- Mở tệp Excel nơi bạn muốn thêm code VBA
- Giữ phím ALT và nhấn phím F11 [bạn cũng có thể chuyển đến tab Developer và nhấp vào biểu tượng Visual Basic]
- Khi Visual Basic Editor mở ra, sẽ có phần Project Explorer ở phần bên trái của trình soạn thảo VBA. Nếu bạn không thấy nó, hãy nhấp vào tab “View” và sau đó nhấp vào “Project Explorer”
- Nhấp 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 code
- Chuyển đến tùy chọn Insert và sau đó nhấp vào Model. Điều này sẽ thêm một module mới vào sổ làm việc
- Đúp chuột vào biểu tượng module trong Project Explorer. Thao tác này sẽ mở cửa sổ code cho module đó
- Sao chép và dán code VBA ở trên vào cửa sổ code
Để chạy code macro VBA, trước tiên hãy chọn tập dữ liệu mà bạn muốn lật [ngoại trừ tiêu đề].
Với dữ liệu đã chọn, hãy chuyển đến VB Editor và nhấp vào nút play màu xanh lục trên thanh công cụ hoặc chọn bất kỳ dòng nào trong code rồi nhấn phím F5
Vì vậy, đây là một số phương pháp bạn có thể sử dụng để 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 mà tôi đã đề cập trong hướng dẫn này [công thức, tính năng 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 sẽ phải điều chỉnh công thức và code VBA cho phù hợp để lật dữ liệu theo chiều ngang].