Hà Vân
Hà Vân
Thảo luận 0 thảo luận
Vỗ tay 0 vỗ tay
Lượt xem 239 lượt xem

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

Jan 26 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.


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.

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é.

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 laajo đượ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.


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:




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. Để được học tất tần tật các thủ thuật Excel cho dân văn phòng thì các bạn hãy đăng ký ngay khóa học dưới đây:

Thủ thuật Excel từ cơ bản đến nâng cao cập nhật hàng tuần dân văn phòng cần thành thạo

Những thủ thuật nhanh gọn sẽ giúp bạn tiết kiệm thời gian tới 200% khi làm việc với dữ liệu Excel. Từ đó, các công việc văn phòng của bạn trở nên nhẹ nhàng và nhanh chóng hơn. Trong quá trình học, giảng viên cũng luôn hỗ trợ để giúp các bạn hiểu bài nhanh chóng bằng cách giải đáp mọi thắc mắc trong vòng 24h. Chúc các bạn luôn học tập hiệu quả!

Cùng tham gia cộng đồng hỏi đáp về chủ đề Excel Cơ Bản

Thảo luận 0 câu trả lời
Lượt xem 239 lượt xem
Vỗ tay vỗ tay

0 Bình luận

@ 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