1. Hàm TOCOL trong Microsoft 365

Hàm TOCOL trong Excel 365 được sử dụng để chuyển đổi một mảng hoặc phạm vi ô thành một cột.

Hàm nhận ba đối số, nhưng chỉ đối số đầu tiên là bắt buộc.

=TOCOL(array, [ignore], [scan_by_column])

array (bắt buộc) - một mảng hoặc phạm vi để chuyển đổi thành một cột.

ignore (tùy chọn) - xác định có nên bỏ qua khoảng trống hoặc/và lỗi hay không. Có thể là một trong những giá trị sau:

- 0 hoặc bỏ qua (mặc định) - giữ tất cả các giá trị

- 1 - bỏ qua khoảng trống

- 2 - bỏ qua lỗi

- 3 - bỏ qua khoảng trống và lỗi

Scan_by_column (tùy chọn) - xác định xem quét mảng theo chiều ngang hay chiều dọc:

- FALSE hoặc bị bỏ qua (mặc định) - quét mảng theo hàng từ trái sang phải.

- TRUE - quét mảng theo cột từ trên xuống dưới.

Bạn có thể tham khảo 1 ví dụ đơn giản sử dụng hàm TOCOL như dưới đây.

Lưu ý:

- Để chuyển đổi một mảng thành một hàng, hãy sử dụng hàm TOROW.

- Để thực hiện chuyển đổi cột thành mảng ngược lại, hãy sử dụng hàm WRAPCOLS để ngắt dòng theo cột hoặc hàm WRAPROWS để ngắt dòng theo hàng.

- Để chuyển mảng từ ngang sang dọc hoặc ngược lại, tức là chuyển hàng thành cột, hãy sử dụng hàm TRANSPOSE.

2. Tính khả dụng của hàm TOCOL

TOCOL là một hàm mới, hiện chỉ được hỗ trợ trong Microsoft 365 (dành cho Windows và Mac) và Excel dành cho web.

3. Công thức TOCOL cơ bản để chuyển đổi phạm vi thành cột

Công thức TOCOL ở dạng đơn giản nhất chỉ yêu cầu một đối số - mảng. Ví dụ, để đặt một mảng hai chiều gồm 3 cột và 4 hàng vào một cột, công thức là:

Công thức chỉ được nhập vào một ô (E2 trong ví dụ này) và tự động tràn vào các ô bên dưới. Về mặt Excel, kết quả được gọi là phạm vi tràn (Spill Range)

Cách thức hoạt động của công thức này:

Về mặt kỹ thuật, phạm vi A2:C5 trước tiên được chuyển đổi thành mảng hai chiều. Xin lưu ý các hàng được phân tách bằng dấu chấm phẩy và các cột được phân cách bằng dấu phẩy:

{"Apple","Banana","Cherry";1,0,3;4,#N/A,6;7,8,9}

Hàm TOCOL quét mảng từ trái sang phải và biến nó thành mảng dọc một chiều:

{"Apple";"Banana";"Cherry";1;0;3;4;#N/A;6;7;8;9}

Kết quả được đặt trong ô E2, từ đó nó tràn vào các ô bên dưới.

Trong Excel có quy tắc là dấu “,” được sử dụng khi các giá trị nằm trên cùng 1 dòng. Còn dấu “;” được sử dụng để ngắt xuống dòng dưới.

4. Các ví dụ về sử dụng hàm TOCOL trong Excel

Để hiểu rõ hơn về các khả năng của hàm TOCOL và những tác vụ mà hàm này có thể thực hiện, chúng ta hãy xem một số ví dụ về công thức.

4.1. Chuyển đổi mảng thành cột bỏ qua khoảng trống và lỗi

Như bạn có thể nhận thấy trong ví dụ trước, công thức TOCOL mặc định giữ tất cả các giá trị từ mảng nguồn, bao gồm các ô trống và lỗi.

Trong mảng kết quả, các ô trống được biểu thị bằng số không, điều này có thể khá khó hiểu, đặc biệt nếu mảng ban đầu có giá trị 0. Giải pháp là bỏ qua khoảng trống. Đối với điều này, bạn đặt đối số thứ 2 thành 1:

=TOCOL(A2:C5, 1)

Để bỏ qua lỗi, hãy đặt đối số thứ 2 thành 2:

=TOCOL(A2:C5, 2)

Để loại trừ cả khoảng trống và lỗi, hãy sử dụng 3 cho đối số bỏ qua:

=TOCOL(A2:C5, 3)

Bạn có thể tham khảo kết quả của 3 tuỳ chọn trên ngay dưới đây.

4.2. Quét mảng theo chiều ngang hoặc chiều dọc

Với đối số scan_by_column mặc định (FALSE hoặc bị bỏ qua), hàm TOCOL quét mảng theo chiều ngang theo hàng. Để xử lý các giá trị theo cột, hãy đặt đối số này thành TRUE hoặc 1. Ví dụ:

=TOCOL(A2:C5, ,TRUE)

Lưu ý rằng, trong cả hai trường hợp, các mảng được trả về có cùng kích thước, nhưng các giá trị được sắp xếp theo một thứ tự khác.

Việc TOCOL mặc định quét theo hàng nếu bạn nào xài VBA thì sẽ thấy rất quen thuộc vì cách hoạt động của TOCOL giống hệt với vòng lặp For Each trong VBA.

Đây cũng là 1 cách giúp mình nhớ tham số mặc định của hàm TOCOL trở nên dễ dàng hơn rất nhiều.

Dưới đây là ví dụ về việc sử dụng thông số scan_by_column này.

4.3. Kết hợp nhiều phạm vi thành một cột

Nếu bạn đang xử lý một số phạm vi không liền kề, thì trước tiên bạn có thể kết hợp các phạm vi theo chiều dọc thành một mảng với sự trợ giúp của hàm VSTACK, sau đó sử dụng TOCOL để chuyển đổi mảng đã kết hợp thành một cột.

Giả sử phạm vi đầu tiên là A2:C4 và phạm vi thứ hai là A8:C9, công thức có dạng sau:

=TOCOL(VSTACK(A2:C4, A8:C9))

Công thức này thể hiện hành vi mặc định - đọc các mảng được kết hợp theo chiều ngang từ trái sang phải như được hiển thị trong cột E trong hình bên dưới.

Để đọc các giá trị theo chiều dọc từ trên xuống dưới, bạn đặt đối số thứ 3 của TOCOL thành TRUE:

=TOCOL(VSTACK(A2:C4, A8:C9), ,TRUE)

Xin lưu ý rằng, trong trường hợp này, trước tiên công thức trả về các giá trị từ cột A của cả hai mảng, sau đó từ cột B, v.v. Lý do là TOCOL quét một mảng được xếp chồng lên nhau, không phải các phạm vi riêng lẻ ban đầu.

Bạn có thể tham khảo ảnh mảng tạo thành do VSTACK để có thể dễ dàng tưởng tượng cách thức hoạt động của hàm TOCOL.

Nếu logic nghiệp vụ của bạn yêu cầu xếp chồng các phạm vi ban đầu theo chiều ngang thay vì theo chiều dọc, thì hãy sử dụng hàm HSTACK thay vì VSTACK.

Để nối từng mảng tiếp theo vào bên phải của mảng trước đó và đọc các mảng đã kết hợp theo chiều ngang, công thức là:

=TOCOL(HSTACK(A2:C4, A8:C10))

Để thêm từng mảng tiếp theo vào bên phải của mảng trước đó và quét các mảng đã kết hợp theo chiều dọc, công thức là:

=TOCOL(HSTACK(A2:C4, A8:C10), ,TRUE)

4.4. Trích xuất các giá trị duy nhất từ ​​một phạm vi nhiều cột

Hàm UNIQUE trong Excel có thể dễ dàng tìm thấy các giá trị duy nhất trong một cột hoặc một hàng cũng như trả về các hàng duy nhất, nhưng nó không thể trích xuất các giá trị duy nhất từ ​​một mảng nhiều cột. Giải pháp là sử dụng nó cùng với chức năng TOCOL.

Chẳng hạn, để trích xuất tất cả các giá trị khác nhau (riêng biệt) từ phạm vi A2:C7, công thức là:

=UNIQUE(TOCOL(A2:C7))

Ngoài ra, bạn có thể bọc công thức trên trong hàm SORT để sắp xếp mảng được trả về theo thứ tự bảng chữ cái:

=SORT(UNIQUE(TOCOL(A2:C7)))

5. Hàm TOCOL bị lỗi trong Excel trong các trường hợp nào?

Nếu hàm TOCOL bị lỗi, rất có thể đó là một trong những lý do sau:

5.1. Hàm TOCOL không được hỗ trợ trong Excel của bạn.

Khi bạn thấy lỗi #NAME? thì việc đầu tiên bạn cần làm đó là phải kiểm tra xem bạn đã gõ tên hàm đúng hay chưa. Nếu tên hàm bạn đã gõ chính xác nhưng lỗi vẫn tiếp diễn thì điều đó có nghĩa là trong Excel của bạn không có sẵn hàm TOCOL.

Vì hiện tại hàm TOCOL chỉ có sẵn trên Office 365 (Phiên bản Personal - Ở thời điểm của bài viết thì hàm TOCOL chưa được Update trên phiên bản dành cho Business và Enterprise) và phiên bản Office trên Web.

5.2. Mảng của bạn quá lớn

Lỗi #NUM cho biết mảng không thể vừa với một cột. Một trường hợp điển hình là khi bạn tham chiếu đến toàn bộ cột hoặc hàng.

Bạn nên nhớ là trong Excel thì số dòng và số cột là có hạn chế.

Điều đó có nghĩa là nếu mảng kết quả trả về vượt qua giới hạn này thì sẽ bị lỗi.

Giới hạn của Excel:

Số dòng: 1.048.576 dòng

Số cột: 16.384 cột

5.3. Không có đủ ô trống

Khi lỗi #SPILL xảy ra, hãy kiểm tra xem cột nơi công thức được nhập có đủ ô trống để điền kết quả hay không. Nếu các ô trống về mặt trực quan, hãy đảm bảo không có khoảng trắng và các ký tự không in được khác trong đó.

Bạn có thể download File đính kèm bằng cách click vào hình dưới đây.