Trong bài viết này, Học Excel Online sẽ hướng dẫn bạn cách chuyển một cột thành nhiều cột thông qua ví dụ cụ thể bên dưới.

Hãy giả sử ta có một cột gồm 15 hàng, bây giờ công việc cần làm sẽ là chuyển thành 5 cột, mỗi cột có 3 hàng. Nói cách khác, ta sẽ tạo ra một mảng 5×3.

Chuyển một cột thành nhiều cột với OFFSET

Công thức

Ở cách đầu tiên, ta sẽ sử dụng OFFSET như sau:

1. Tại ô C1, điền công thức: =OFFSET($A$1,COLUMNS($A1:A1)-1+(ROWS(A$1:A1)-1)*5,0)
2. Sử dụng thao tác fill để kéo thả thành mảng 5×3.

Giải thích

Tại đây, ta KHÔNG tạo một mảng 5×3, mà thay vào đó ta tạo lập một công thức mà mỗi khi fill xuống sẽ tự tham chiếu tới 1 ô trong cột mà ta kiểm soát được khoảng cách giữa các ô. VD trong mảng 5×3 này, khi fill xuống, khoảng cách là 5 đơn vị. Nếu là mảng 3×5? Là 3 đơn vị.

Đơn vị ở đâu, các bạn hãy chú ý đoạn *5, thử sửa thành *3 xem sao nhé!

Hàm OFFSET trong trường hợp này hoạt động như sau:

1. Chọn ô đầu tiên của cột (A1).

2. Tại dòng tham chiếu (rows), xác định ô để tham chiếu đầu tiên. Toàn bộ vùng rows được thể hiện bằng công thức: =COLUMNS($A1:A1)-1+(ROWS(A$1:A1)-1)*số cột. Logic ở đây là:

Khi đi trong 1 hàng từ trái sang phải, tăng 1 đơn vị. Khi đi trong 1 cột từ trên xuống dưới, tăng theo khoảng cách đơn vị.

3. Nếu các bạn vẫn chưa rõ về hàm OFFSET, hãy đọc thêm tại đây.

Hàm OFFSET(giá trị sử dụng để tham chiếu, lệch mấy hàng, lệch mấy cột) được sử dụng trong trường hợp này, bởi ta tra cứu trong cùng cột, giá trị cột để 0.

Đối với giá trị hàng:

Để đi từ trái sang phải chỉ tăng 1 đơn vị, ta thiết lập công thức sử dụng hàm COLUMNS – hàm trả về số cột của một vùng dữ liệu. Tại ô đầu tiên, lấy chính ô đấy, ta bắt đầu với COLUMNS($A1:A1)=0, nhưng bởi COLUMNS sẽ trả về 1 nên ta cần thêm vào -1. Một cách cơ bản ta hiểu như sau:

Đối với giá trị cột:

Sau khi đã thiết lập đi từ trái sang phải chỉ tăng một đơn vị, ta sẽ thiết lập đi theo cột. Nói cách khác, mỗi lần xuống dòng, ta sẽ nhân khoảng cách lên một số lần tương ứng.

Vậy công thức ta cần thiết lập sẽ tuân theo nguyên tắc: đi từ trên xuống tăng 1 đơn vị, sau đó nhân với số lần. Nói cách khác, công thức ở đây là: =ROWS(A$1:A1)-1)*số lần.

Cuối cùng, ta cộng hai giá trị COLUMNS và ROWS lại với nhau. Kết quả thể hiện như trên đầu bài.

Giới hạn cột

Tuy vậy, khi bạn kéo cột quá tay theo hàng, có thể thấy rằng các giá trị vẫn tiếp tục hiện ra tăng tiến 1 đơn vị

Theo đúng mục đích ta cần là tách từ một cột thành nhiều cột, để giới hạn cột (Ví dụ bảng 3×5) ta có thể làm theo các bước sau:
1. Chèn giới hạn bảng:

2. So sánh số cột của vùng tạo ra với số cột được giới hạn bằng công thức: COLUMNS(vùng) > VALUE(LEFT(Giá trị loại bảng,1)).

Tùy vào cách nhập loại bảng mà các bạn có thể so sánh bằng các công thức khác nhau. Ở đây Học Excel Online sử dụng hàm Left để lấy ra giá trị 5 từ 5×3.

3. Biện luận theo điều kiện bằng hàm: Nếu COLUMNS(vùng)> VALUE(LEFT(giá trị loại bảng,1)) thì trả về giá trị rỗng “”, nếu không tiến hành OFFSET bình thường.

Công thức cụ thể trong trường hợp này là:

=IF(COLUMNS($C$1:C1)>VALUE(LEFT($K$1,1)),””,(OFFSET($A$1,COLUMNS($A1:A1)-1+(ROWS(A$1:A1)-1)*5,0)))

Vậy là ta đã hoàn thành việc tách một cột thành nhiều cột mà sự nhảy đơn vị diễn ra ở các hàng. Trong phẩn tiếp theo, Học Excel Online sẽ tiếp tục công việc tách một cột thành nhiều cột với sự nhảy đơn vị tại đầu mỗi cột. Đừng bỏ lỡ nhé!

Đọc phần 2 tại đây

Ngoài ra, bạn có thể đọc thêm về các hàm trong bài:

Tìm hiểu hàm OFFSET thông qua video

Hàm OFFSET kết hợp MATCH

Tạo Data Validation với Name động sử dụng OFFSET

Phân biệt hàm COLUMN và COLUMNS