Cách đảo ngược dữ liệu cột/hàng trên Excel siêu nhanh

Trong nhiều lúc khi làm việc ta sẽ muốn đảo ngược dữ liệu trên cột hoặc hàng để xử lý và tính toán các bước tiếp theo. Dưới đây mình sẽ hướng dẫn cách nhanh nhất để đảo ngược dữ liệu:

Tải ngay file mẫu thực hành đảo ngược dữ liệu

1. Đảo ngược dữ liệu trong cột (chiều dọc)

Để đảo ngược dữ liệu trong cột ta sẽ sử dụng hàm INDEX kết hợp với hàm ROWS + hàm SEQUENCE. Cụ thể như sau:

Cách 1: Sử dụng công thức động (áp dụng cho Excel 365+)

Với Excel 365, ta có thể sử dụng công thức động với hàm INDEX và SEQUENCE. Để làm ta tạo một cột bên cạnh cột cần đảo ngược dữ liệu, nhập công thức sau:

=INDEX(A2:A11, SEQUENCE(ROWS(A2:A11),, ROWS(A2:A11), -1))

Trong đó:

• ROWS(A2:A11) đếm số hàng.
• SEQUENCE(…) tạo dãy chỉ số từ lớn đến nhỏ (bước -1).
• INDEX lấy phần tử theo chỉ số đó → danh sách bị đảo ngược.

Cách 2: Công thức cổ điển (áp dụng mọi phiên bản Excel)

Công thức kinh điển để đảo dữ liệu cột áp dụng cho mọi phiên bản Excel thì chỉ cần kết hợp hàm INDEX và hàm ROW như sau:

=INDEX($A$2:$A$11, ROWS($A$2:$A$11) - ROWS(A$2:A2) + 1)

Trong công thức trên:

• INDEX(vùng, vị_trí_hàng) → trả về giá trị tại vị trí_hàng trong vùng (ở đây vùng là $A$2:$A$11)
• ROWS($A$2:$A$11) → đếm số hàng trong vùng. Với A2:A11 là 10
• ROWS(A$2:A2) trả về 1 ở dòng đầu, 2 ở dòng kế… (tự tăng đều đặn khi kéo xuống).

Ý tưởng trong công thức trên là biến bộ đểm tăng dần khi kéo xuống (2, 3, 4, …) thành chỉ số giảm dần (10, 9, 8, …) để INDEX lấy từ cuối lên đầu.

Cách 3: Dùng SORTBY (áp dụng cho Excel 365+)

Có một cách khác với Excel 365 trở lên, là sử dụng SORTBY để đảo ngược dữ liệu với công thức sau:

=SORTBY(A2:A11, ROW(A2:A11), -1)

Trong đó:

• Công thức chuc của hàm SORTBY là: SORTBY(range, by_array, sort_order)
• range = A2:A11: danh sách nguồn
• by_array = ROW(A2:A11): tạo mảng số hàng {2;3;…;11} tương ứng từng ô trong A2:A11
• sort_order = -1: sắp xếp giảm dần theo mảng số hàng.

ROW(A2:A11) sinh ra {2;3;4;5;6;7;8;9;10;11} → sắp giảm dần thành {11;10;…;2}. Sắp theo khóa này khiến dữ liệu ở hàng 11 lên trước, rồi 10, … ⇒ ngược thứ tự ban đầu.

Đây là công thức động nên chỉ cần đặt ở một ô thì kết quả tự “tràn” xuống các ô bên dưới. Khi thêm/bớt các mục trong vùng (nếu vùng tham chiếu bao trùm mục mới) thì kết quả cũng cập nhật theo.

2. Đảo ngược dữ liệu trong hàng (theo chiều ngang)

Cách 1: Công thức động (áp dụng cho Excel 365+)

Tương tự với công thức đảo ngược dữ liệu cột, nhưng ta sẽ sử dụng hàm INDEX kết hợp với hàm COLUMNS là chính và tạo công thức động với hàm SEQUENCE như sau:

=INDEX(B2:K2, SEQUENCE(1, COLUMNS(B2:K2), COLUMNS(B2:K2), -1))

Công thức trên sẽ trả về dãy trong B2:K2 theo thứ tự ngược lại (từ phải sang trái), và kết quả tràn sang phải.

Trong đó:

• COLUMNS(B2:K2) dùng để đếm số cột trong dải B2:K2. Ví dụ B2:K2 có 10 cột ⇒ trả về 10
• SEQUENCE(1, COLUMNS(B2:K2), COLUMNS(B2:K2), -1):
  • Cú pháp: SEQUENCE(rows, columns, start, step)
  • Ở công thức này taọ một hàng có số cột bằng COLUMNS(B2:K2), bắt đầu từ chính số đó và bước -1
  • Ví dụ: Với 10 cột ⇒ sinh mảng: {10, 9, 8, …, 1} (nằm ngang).
• INDEX(B2:K2, …)
  • Cú pháp: INDEX(array, row_num, [column_num])
  • Vì B2:K2 là mảng 1 hàng – nhiều cột, Excel hiểu tham số chỉ số bạn đưa vào là chỉ số cột (trong trường hợp 2 đối số).
  • Khi đưa cả dãy {10,9,…,1} vào, INDEX sẽ lấy lần lượt cột 10, cột 9, …, cột 1 của B2:K2 và trả về một mảng ngang tương ứng ⇒ chính là đảo ngược hàng.

Cách 2: Sử dụng công thức kinh điển (áp dụng mọi phiên bản Excel)

Với các phiên bản Excel cũ hơn, ta có thể đảo ngược dữ liệu hàng chỉ với hàm INDEX và COLUMNS với công thức sau:

=INDEX($B$2:$K$2, COLUMNS($B$2:$K$2) - COLUMNS($B$2:B$2) + 1)

Trong công thức trên:

• INDEX($B$2:$K$2, …) sẽ về phần tử trong dải 1 hàng, nhiều cột. Vì dải chỉ có 1 hàng, đối số số 2 của INDEX được hiểu là chỉ số cột cần lấy (1 = cột B, 2 = cột C, …, 10 = cột K).
• COLUMNS($B$2:$K$2) đếm tổng số cột của vùng nguồn.
• COLUMNS($B$2:B$2) đếm số cột từ ô cố định $B$2 đến ô kết thúc tương đối B$2. Khi kéo công thức sang phải, phần kết thúc sẽ dịch:

• • Ở cột đầu (B…): COLUMNS($B$2:B$2) = 1
  • Sang cột kế (C…): COLUMNS($B$2:C$2) = 2
  • Rồi 3, 4, … tăng dần.

3. Cách đảo ngược dữ liệu theo từng nhóm (Group A/B/C)

Trong nhiều trường hợp, ta sẽ gặp đề bài khi cần đảo ngược dữ liệu theo từng nhóm. Ví dụ như sau:

Dải A2:C21 có cột Group tại cột A. Muốn đảo thứ tự trong từng Group nhưng vẫn giữ thứ tự các Group (A→B→C).

Để giải quyết bài toán đảo ngược dữ liệu theo từng nhóm trên ta sẽ sử dụng hàm SORTBY với nhiều khóa như sau:

=SORTBY(A2:C21, A2:A21, 1, ROW(A2:A21), -1)

Mục tiêu của công thức trên là sắp sếp toàn bộ bảng A2:C21 theo 2 khóa:

• Khóa 1 (Group): A2:A21 tăng dần → gom nhóm A rồi B rồi C.

   

• Khóa 2 (Thứ tự trong nhóm): ROW(A2:A21) giảm dần → đảo ngược thứ tự các hàng bên trong từng nhóm (hàng dưới lên trước).

Trong đó:

• SORTBY(range, by_array1, order1, by_array2, order2, ...) → Sắp range dựa trên một hoặc nhiều mảng “khóa”.
• range = A2:C21: toàn bộ dữ liệu 3 cột sẽ di chuyển nguyên hàng.

   

• by_array1 = A2:A21, order1 = 1 → sắp theo Group tăng dần (A→B→C).

   

• by_array2 = ROW(A2:A21), order2 = -1 → trong mỗi Group, sắp theo số hàng giảm dần.

=> Vì số hàng lớn hơn nằm phía dưới trong bảng, sắp giảm dần sẽ đưa “hàng dưới” lên trên ⇒ đảo ngược thứ tự trong nhóm.

Như vậy, bài viết trên đã hướng dẫn các bạn cách đảo ngược dữ liệu trong cột trên Excel. Hy vọng bài viết sẽ hữu ích với các bạn trong quá trình làm việc. Chúc các bạn thành công!