Bài hướng dẫn viết công thức để chuyển dữ liệu từ dạng dọc sang dạng ngang

Khi bạn muốn biến đổi dữ liệu từ dạng cột sang dạng hàng (hay ngược lại), bạn sẽ sử dụng phương pháp được biết đến là “TRANSPOSE”. Phương pháp này yêu cầu nhiều bước thực hiện tinh vi. Bạn có hứng thú không, nếu muốn học cách để viết công thức chuyển dữ liệu từ dọc sang ngang? Hãy cùng theo dõi bài viết của Siêu Marketing.

Cách viết công thức để chuyển dữ liệu từ dọc sang ngang

Xét bảng dữ liệu dưới đây làm ví dụ:

Giả sử ta có dữ liệu theo cột từ A1 đến C8 và muốn chuyển sang hàng theo cách hiển thị trong bảng E1 đến L3. Công thức nào sẽ đáp ứng yêu cầu này?

Hàm và kỹ thuật cần dùng

Chúng ta có thể thực hiện nhiệm vụ này thông qua nhiều cách khác nhau, nhưng bài viết này chỉ đề cập đến một số hàm cơ bản như:

  • Hàm Indirect: trả về giá trị ở một ô xác định qua việc tham chiếu đến ô đó
  • Hàm Address: tạo ra địa chỉ của một ô từ dữ liệu dòng, cột
  • Hàm Row: trả về số hiệu của hàng
  • Hàm Column: trả về số hiệu của cột

Kỹ thuật chính cần sử dụng là FillRight, còn được gọi là kéo công thức theo hướng bên phải (sử dụng phím tắt Ctrl + R)

Thứ tự thực hiện cần xác định

Ví dụ cần chuyển cột Họ tên thành dạng hàng ngang. Ta bắt đầu từ ô F1 và ô tham chiếu là A2.

Sau khi nhập công thức ở ô F1, ta áp dụng fillright để sao chép công thức sang phải. Mỗi ô mới trên hàng 1 sẽ lấy dữ liệu tương ứng trên các hàng của cột A theo kiểu “Fillright công thức tại hàng 1 thì tương đương với filldown ở cột A”.

Bước 1: Sử dụng Indirect và Address để lấy giá trị từ ô A2

Khi ta nhập =INDIRECT(ADDRESS(2,1)) vào ô F1, ta sẽ nhận được giá trị tại ô A2.

Nhưng chúng ta muốn sao chép công thức trong ô F1 sang bên phải và việc làm này đòi hỏi chỉ sửa dòng số trong hàm ADDRESS mà không thay đổi cột.

Bằng cách thêm 1 đơn vị vào số dòng mỗi lần fillright, ta có thể đổi giá trị dòng như sau:

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

G1: tham chiếu đến ô A3 =ADDRESS(2+1,1)

H1: tham chiếu đến ô A4 =ADDRESS(2+2,1)

Chúng ta có thể xác định các giá trị 0, 1, 2,… được cộng thêm khi fillright qua cách sau:

0 = số cột F trừ số cột F = COLUMN(F1)-COLUMN($F$1)

1 = số cột G trừ số cột F = COLUMN(G1)-COLUMN($F$1)

2 = số cột H trừ số cột F = COLUMN(H1)-COLUMN($F$1)

khi gộp lại ta có công thức tại ô F1 là:

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

Cùng xem xét kết quả:

Và đối với dữ liệu như Bộ phận hay Năm sinh, ta chỉ cần thay đổi giá trị cột trong hàm tham chiếu.

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

Như vậy, từ một công thức chính xác, ta có thể phái sinh ra nhiều công thức tham chiếu khác. Nắm vững cách sử dụng hàm INDIRECT và ADDRESS cho phép chúng ta dễ dàng chuyển đổi dữ liệu từ dạng dọc sang ngang và ngược lại.

Excel

365

Với việc sử dụng Excel 365 hoặc Office 365, bạn sẽ thấy công tác của mình trở nên thuận lợi hơn bởi phiên bản này của Excel hỗ trợ hàm mảng động, điều đó có nghĩa là có thể tự động mở rộng kết quả của một công thức ra các ô liền kề từ một ô đơn lẻ, minh họa bằng hình ảnh sau đây

ham-transpose-trong-excel-365

Nâng cao

Làm thế nào để bạn chỉnh sửa công thức để có thể chuyển dữ liệu từ hàng ngang sang hàng dọc?

Hãy thử thực hiện và để lại bình luận với kết quả bạn nhận được. Một gợi ý cho bạn là sử dụng thủ thuật FillDown, và vì thế mỗi lần bạn sử dụng FillDown thì tham chiếu sẽ được tăng thêm một cột.

Những bài viết khác về cùng chủ đề mà bạn có thể quan tâm bao gồm:

Hướng dẫn về những kỹ thuật copy công thức theo đúng nhu cầu trong Excel

Phương pháp để chuyển dữ liệu trong Excel, từ hàng sang cột và ngược lại

Cách dùng hàm INDIRECT trong Excel, bao gồm công thức và các ví dụ cụ thể

Trương Thành Tài

    [submission_id id-lien-he]

    0
      0
      Đơn hàng
      Đơn hàng trốngQuay lại Shop