Hướng dẫn cách viết công thức chuyển dữ liệu từ chiều dọc sang chiều ngang

Chuyển dữ liệu đang được tổ chức theo chiều dọc sang chiều ngang (hay ngược lại) được gọi với thuật ngữ chung là “TRANSPOSE”. Tuy nhiên việc transpose lại được thực hiện với khá nhiều thao tác mới hoàn thành được. Vậy bạn có muốn biết cách viết công thức để chuyển dữ liệu từ chiều dọc sang chiều ngang không? Hãy cùng Học Excel Online tìm hiểu ngay nào.

Xem nhanh

Công thức chuyển dữ liệu từ chiều dọc sang chiều ngang

Ví dụ chúng ta có bảng dữ liệu như sau:

Từ bảng dữ liệu được tổ chức theo chiều dọc A1:C8, chúng ta muốn chuyển sang chiều ngang như bảng E1:L3. Công thức nào giúp làm được việc này?

Các hàm và kỹ thuật cần sử dụng

Có nhiều cách để làm việc này, nhưng trong bài này chúng ta sẽ dùng những hàm khá cơ bản như sau:

Hàm Indirect: lấy giá trị trong 1 ô dựa vào tham chiếu tới ô đó
Hàm Address: lấy địa chỉ của 1 ô dựa vào số dòng, số cột
Hàm Row: xác định số dòng
Hàm Column: xác định số cột

Kỹ thuật cần sử dụng chủ yếu là FillRight, hay còn gọi là sao chép công thức về phía bên phải (phím tắt là Ctrl + D)

Xác định trình tự thực hiện

Hãy lấy ví dụ đầu tiên là chuyển nội dung cột Họ tên sang chiều ngang. Vị trí đặt công thức là ô F1, vị trí tham chiếu bắt đầu với ô A2.

Khi đã có công thức tại ô F1, chúng ta fillright công thức đó sang phía tay phải. Với mỗi ô tiếp theo trên cùng dòng 1 sẽ lấy tương ứng giá trị của tại ô tiếp theo trên dòng A. Hiểu ngắn gọn là “Công thức fillright tại dòng 1 thì kết quả sẽ filldown tại cột A“.

Bước 1: Lấy kết quả tại ô A2 với hàm Indirect+Address

Nếu tại ô F1 chúng ta nhập công thức =INDIRECT(ADDRESS(2,1)) thì kết quả thu được chính là giá trị tại ô A2

Tuy nhiên ở đây chúng ta cần sao chép công thức tại F1 sang bên phải. Khi sao chép thì lại yêu cầu: Chỉ thay đổi về giá trị Dòng trong hàm ADDRESS, còn cột thì không.

Để thay đổi giá trị dòng (tại F1 tham chiếu dòng 2, G1 tham chiếu dòng 3…) thì chúng ta sẽ bắt đầu từ dòng 2, khi fillright tới mỗi 1 ô sẽ cộng thêm 1 đơn vị vào số dòng.

Cụ thể:
F1: tham chiếu tới ô A2 =ADDRESS(2+0,1)

G1: tham chiếu tới ô A3 =ADDRESS(2+1,1)

H1: tham chiếu tới ô A4 =ADDRESS(2+2,1)

…

Như vậy để có thể xác định các số 0, 1, 2… được cộng thêm khi fillright chúng ta làm như sau:

0 = số cột của cột F – số cột của cột F = COLUMN(F1)-COLUMN($F$1)

1 = số cột của cột G – số cột của cột F = COLUMN(G1)-COLUMN($F$1)

2 = số cột của cột H – số cột của cột F = COLUMN(H1)-COLUMN($F$1)

…

Ghép lại vào công thức tại ô F1 ta có:

=INDIRECT(ADDRESS(2+COLUMN(F1)-COLUMN($F$1),1))

Hãy xem kết quả nào:

Tương tự như vậy, muốn tham chiếu các nội dung về Bộ phận, Năm sinh thì chúng ta chỉ cần thay đổi vị trí cột cần tham chiếu.

F2=INDIRECT(ADDRESS(2+COLUMN(F1)-COLUMN($F$1),2)) tham chiếu cột số 2 là cột B
F3=INDIRECT(ADDRESS(2+COLUMN(F1)-COLUMN($F$1),3)) tham chiếu cột số 3 là cột C

Như vậy chỉ cần làm tốt 1 công thức, chúng ta dễ dàng tùy biến sang các tham chiếu khác. Bằng cách sử dụng hàm INDIRECT kết hợp hàm ADDRESS, chúng ta đã có thể chuyển bất kỳ kiểu sắp xếp dữ liệu từ chiều dọc sang chiều ngang được rồi.

Excel 365

Nếu bạn sử dụng Office 365 hoặc Excel 365, thì công việc của bạn sẽ đơn giản đi rất nhiều bởi vì trong phiên bản Excel này, hàm mảng động được hỗ trợ, có nghĩa là, công thức bạn nhập vào 1 ô sẽ có khả năng điền kết quả sang nhiều ô lân cận khác như trong hình dưới đây