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

Linh Mai
Linh Mai
Nov 28 2022

 

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.

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

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 bằng công thức

Cách chuyển dữ liệu Excel từ chiều dọc 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:

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

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:

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

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ả!

0/5 - (0 bình chọn)

0/5 - (0 bình chọn)

Bài viết liên quan

Cách ứng dụng hàm INT và MOD cho lễ tân và các công việc khác

Cách ứng dụng hàm INT và MOD cho lễ tân và các công việc khác

Hướng dẫn cách tách địa chỉ ra khỏi chuỗi

Hướng dẫn cách tách địa chỉ ra khỏi chuỗi

CÁCH TÍNH ĐIỂM TRUNG BÌNH VÀ XẾP LOẠI TRONG EXCEL (KÈM BÀI TẬP)

CÁCH TÍNH ĐIỂM TRUNG BÌNH VÀ XẾP LOẠI TRONG EXCEL (KÈM BÀI TẬP)

Hướng dẫn kiểm tra giá trị trùng lặp cực đơn giản trên Excel 2010, 2013, 2016, 2019

Hướng dẫn kiểm tra giá trị trùng lặp cực đơn giản trên Excel 2010, 2013, 2016, 2019

Cách cộng, trừ ngày tháng năm trong Excel để tính số ngày siêu nhanh cho kế toán

Cách cộng, trừ ngày tháng năm trong Excel để tính số ngày siêu nhanh cho kế toán

Hướng dẫn 4 cách bôi đen trong Excel đơn giản và nhanh chóng

Hướng dẫn 4 cách bôi đen trong Excel đơn giản và nhanh chóng

@ 2020 - Bản quyền của Công ty cổ phần công nghệ giáo dục Gitiho Việt Nam
Giấy chứng nhận Đăng ký doanh nghiệp số: 0109077145, cấp bởi Sở kế hoạch và đầu tư TP. Hà Nội