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

Chuyển dữ liệu Excel từ chiều dọc sang chiều ngang sẽ khá mất thời gian nếu các bạn chưa biết cách sử dụng công thức này. Hãy cùng Gitiho khám phá ngay bây giờ nhé.

Khi làm việc với Excel, sẽ có những lúc chúng ta cần chuyển đổi cách trình bày bảng dữ liệu. Nếu dữ liệu Excel của bạn đang được bố trí theo bảng dọc thì bạn chuyển nó thành bảng ngang như thế nào?

Ví dụ chúng ta có bảng dữ liệu dọc thuộc vùng A1:C8 như bảng bên trái trong hình ảnh sau và cần chuyển thành bảng ngang như vùng E1:L3 ở bên phải.

Với số lượng dòng ít chắc hẳn nhiều bạn sẽ nghĩ đến việc Copy & Paste. Nhưng nếu đó là một bảng chứa hàng trăm, hàng ngàn dòng dữ liệu thì sao? Khi đó, các bạn hãy viết công thức chuyển dữ liệu Excel từ chiều dọc sang chiều ngang như chúng mình hướng dẫn ngay dưới đây.

Xem thêm: Cách dùng hàm đếm ô có dữ liệu trong Excel chi tiết nhất

Cách chuyển dữ liệu Excel từ chiều dọc sang chiều ngang

Chuyển dữ liệu từ chiều dọc sang chiều ngang giúp bạn thay đổi cách hiển thị để phân tích dữ liệu dễ dàng hơn. Ngoài ra, bạn có in thể in file Excel có nhiều cột trên một trang giấy khi chuyển từ chiều dọc sang ngang. Dưới đây là cách chuyển dữ liệu Excel sang chiều ngang:

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

Trong phạm vi của bài viết này, chúng ta sẽ sử dụng những hàm trong Excel khá cơ bản như sau:

• Hàm INDIRECT: Lấy giá trị của một ô dựa vào tham chiếu tới ô đó
• Hàm ADDRESS: Lấy địa chỉ của một ô dựa vào số dòng, số cột của nó
• Hàm ROW: Xác định số dòng
• Hàm COLUMN: Xác định số cột

Kỹ thuật chủ yếu mà chúng ta sử dụng ở đây là FillRight hay còn lại là sao chép công thức sang phía bên phải. Các bạn có thể chọn vùng muốn sao chép công thức sang rồi bấm tổ hợp phím tắt Ctrl + D nhé.

Xem thêm: Bộ đề thi, bài tập Excel có đáp án về hàm trong Excel quan trọng

Giả sử ban đầu chúng ta muốn chuyển nội dung cột Họ tên từ chiều dọc sang chiều ngàng thì vị trí đặt công thức sẽ là ô F1 còn vị trí tham chiếu bắt đầu tại ô A2. Khi ô F1 đã có công thức thì chúng ta Fillright sang phái bên phải. Với mỗi ô tiếp theo trên cùng dòng 1 thì sẽ lấy tương ứng giá trị của ô tiếp theo trên dòng A. Các bạn có thể hiểu một cách 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".

Các bước chuyển dữ liệu Excel từ chiều dọc sang chiều ngang

Bước 1: Dùng hàm INDIRECT kết hợp với hàm ADDRESS để lấy kết quả tại ô A2

Nếu các bạn nhập cộng thức =INDIRECT(ADDRESS(2,1)) vào ô F1 thì kết quả thu được chính là giá trị tại ô A2. Tuy nhiên, khi chúng ta sao chép công thức từ ô F1 sang bên phải thì lại có yêu cầu khác là chỉ thay đổi giá trị dòng, không thay đổi giá trị cột trong hàm ADDRESS,

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

Ví dụ: 

• 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)

Bước 2: Chúng ta sẽ dùng cách dưới đây để xác định các số 0,1,2... được cộng thêm khi fillright 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)

Bước 3: Ghép các thành thành phần vào công thức tại ô F1 thì chúng ta sẽ được:

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

Hình ảnh dưới đây là kết quả mà chúng ta có được khi thực hiện công thức này:

Tương tự như trên, muốn tham chiếu nội dung cho phần Bộ phận và Năm sinh thì chúng ta cũng chỉ cần thay đổi vị trí của cột cần tham chiếu. Công thức cụ thể như sau:

• 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

Các bạn thấy đó, về cơ bản thì chúng ta chỉ cần làm được một công thức là có thể dễ dạng tạo ra các công thức cho những tham chiếu khác. Như vậy, việc chuyển dữ liệu Excel từ chiều dọc sang chiều ngang bằng cách kết hợp hàm INDIRECT và hàm ADDRESS đã trở nên đơn giản hơn bao giờ hết.

Xem thêm: Hướng dẫn cách lấy địa chỉ của Cell hiện tại trong Excel

Cách chuyển dữ liệu Excel từ chiều dọc sang chiều ngang trong Excel 365

Nếu các bạn sử dụng Excel trong bộ Microsoft Office 365 thì thao tác chuyển dữ liệu chiều dọc sang chiều ngang lại vô cùng đơn giản. Bởi vì đây là phiên bản Excel có những tính năng hiện đại và mới nhất hiện nay nên bạn sẽ được sử dụng hàm mảng động để thực hiện thao tác này. Bạn chỉ cần nhập công thức vào một ô là Excel sẽ tự điền được kết quả đến nhiều ô lân cận khác như trong hình ảnh dưới đây:

Xem thêm: Hướng dẫn các ưu điểm của Microsoft Office 365 và cách cài đặt

Kết luận

Các bạn hãy mở Excel lên và thử làm ngay thao tác mà chúng mình đã hướng dẫn nhé. Hy vọng bài hướng dẫn này sẽ hữu ích cho quá trình làm việc với Excel của bạn. Chúc các bạn luôn học tập hiệu quả!