Cách biến các dải cột và hàng thành ma trận trong Excel

Lời mở đầu

Có những lúc bạn cần phải chuyển đổi dữ liệu dạng cột và hàng thành ma trận. Trong bài viết dưới đây, Học Excel Online sẽ chỉ cho bạn các công thức để giúp bạn thực hiện được điều đó. Để bắt đầu, ta sẽ tìm hiểu cách chuyển đổi các dải cột thành ma trận trước. Sau đó quá trình sẽ được lặp lại tương tự đối với các hàng.

Cách chuyển các dải cột thành ma trận

Giả sử bạn có 1 cột dữ liệu trong dải có tên TheCol2. Đây là một cột đơn nhưng lại được trải rộng qua nhiều hàng khác nhau. Để chuyển nó thành ma trận dạng 2xN gồm 2 hàng và N cột, trong đó N bằng số hàng của cột TheCol2 chia cho 2, bạn hãy sử dụng công thức dưới đây. Công thức sẽ được điền vào ô C9. Hãy thay phần tham chiếu $D$9 thành ô tính đầu tiên mà bạn điền công thức vào.

=OFFSET(TheCol2,COLUMN()-COLUMN($D$9)+((ROW()-ROW($D$9))*(ROWS(TheCol2)/2)),0,1,1)

Sau đó sao chép công thức toàn bộ các hàng cho đến ô thứ N, trong đó N bằng một nửa số cột của TheCol2. Tiếp tục sao chép các ô tính này xuống dưới 1 dòng. Chẳng hạn, nếu cột TheCol2 nằm ở vị trí B8:B16 và bạn lựa chọn ô D9 để ghi công thức, bạn cần sao chép công thức theo hàng ngang cho đến ô G9 và tiếp tục sao chép dải ô D9:G9 xuống dưới 1 dòng để chèn vào nội dung của dải ô D9:G10. Trong trường hợp bạn muốn có nhiều hàng hơn và ít cột đi, hãy đổi ký tự ROWS(TheCol2)/2 thành ROWS(TheCol2)/N trong đó N là số hàng mà bạn muốn điền. Dưới đây là ảnh ví dụ cột TheCol2 và kết quả của việc chuyển đổi thành ma trận:

Chuyển cột thành ma trận

Xem ngay: Các hàm excel thông dụng

Cách chuyển các dải hàng thành ma trận

Cách làm được thực hiện tương tự đối với dải ô theo hàng. Giả sử ta có hàng B22:I22, được đặt tên là TheRow. Để biến nó thành ma trận, bạn hãy điền công thức sau vào ô E25:

=OFFSET(TheRow,0,COLUMN()-COLUMN($E$25)+(ROW()-ROW($E$25))*(COLUMNS(TheRow)/2),1,1)

Lưu ý là bạn nên thay ô tham chiếu $E$25 bằng ô tính đầu tiên chứa công thức trên. Sao chép công thức này đến ô H25, hoặc đến ô ở giữa hàng TheRow. Tiếp đến bạn sao chép toàn bộ nội dung xuống phía dưới 1 dòng. Lúc này công thức sẽ nằm ở hàng E25:H26.  Trong trường hợp bạn muốn có nhiều hàng hơn và ít cột đi, hãy đổi ký tự COLUMNS(TheRow)/2 thành COLUMNS(TheRow)/N trong đó N là số hàng mà bạn muốn điền. Dưới đây là ảnh minh họa về kết quả của quá trình chuyển đổi trên:

Chuyển hàng thành ma trận