Hướng dẫn cách chuyển một cột thành nhiều cột bằng công thức trong Excel (Phần 1)

Chia sẻ bài viết này:
  •  
  •  
  •  
  •  
  •  
  •  
  •   
  •   

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:

Bằng việc thấu hiểu rằng hầu hết nhân viên văn phòng thường mất nhiều thời gian tra cứu cách sử dụng các hàm, các tính năng trong Excel, gặp khó khăn trong lồng ghép hàm, xử lý dữ liệu, công việc bận rộn không có thời gian học. Nên Học Excel Online đã thiết kế ra chương trình Excel từ cơ bản tới nâng cao dành cho người đi làm. Đây là chương trình học online, học viên có thể chủ động học mọi lúc mọi nơi, trao đổi trực tiếp với giảng viên tại bài giảng trong suốt quá trình học.

Với khoá học này học viên sẽ nắm được: :

  • Trọn bộ công cụ định dạng báo cáo chuyên nghiệp: Format, Cell, Number, Style,...không mất thời gian cả ngày ngồi sửa báo cáo
  • Toàn bộ kỹ năng lọc dữ liệu, lọc dữ liệu nâng cao trong Excel phục vụ cho mục đích trích xuất dữ liệu
  • Data Validation kiểm soát dữ liệu khi nhập vào một vùng trong bảng tính
  • TẤT TẦN TẬT hơn 100 hàm thông dụng, công thức mảng trong Excel, học tới đâu nhớ tới đó
  • Bộ 36 PHÍM TẮT giúp thao tác nhanh gấp đôi
  • BÁO CÁO SIÊU TỐC trong 1 phút với Pivot Table
  • Hỏi đáp trực tiếp tại bài giảng với giảng viên trong suốt quá trình học
  • CHUYÊN NGHIỆP trong báo cáo bằng Biểu đồ, đồ thị,....
  • 142+ bài giảng, 13+ giờ học, 200+ ví dụ thực tế cùng chuyên gia
  • 100+ Kỹ năng, thủ thuật Excel thông dụng với các hàm: SUMIF, HLOOKUP, VLOOKUP, DATEDIF…

Đố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

 

 


Chia sẻ bài viết này:
  •  
  •  
  •  
  •  
  •  
  •  
  •   
  •