Cách Chuyển Đổi Bảng Excel Thành Cột Duy Nhất Với Hàm TOCOL 2024

Dữ liệu trải rộng trên nhiều cột là tình huống quen thuộc khi làm việc với Excel. Bạn có bảng sản phẩm với giá theo từng khu vực, danh sách nhân viên chia theo phòng ban, hoặc báo cáo doanh số theo tháng. Muốn gộp tất cả vào một cột duy nhất để phân tích hoặc tạo biểu đồ thường tốn hàng chục phút sao chép thủ công. Excel 2024 giới thiệu hàm TOCOL giải quyết vấn đề này trong một công thức duy nhất.

Mục Lục

Cú pháp cơ bản của TOCOL

Hàm TOCOL chuyển đổi một mảng hoặc vùng ô thành một cột dọc. Cú pháp đơn giản với ba tham số, trong đó chỉ tham số đầu tiên là bắt buộc.

Cú pháp đầy đủ:

=TOCOL(mảng, [bỏ_qua], [quét_theo_cột])

Các tham số:

mảng (bắt buộc): Vùng ô hoặc mảng cần chuyển đổi
bỏ_qua (tùy chọn): Xác định loại giá trị cần bỏ qua
quét_theo_cột (tùy chọn): Cách thức quét mảng

Ví dụ đơn giản nhất với vùng A1:C3 chứa 9 giá trị:

=TOCOL(A1:C3)

Công thức này đọc dữ liệu từ trái sang phải, từ hàng trên xuống hàng dưới, rồi trả về một cột duy nhất gồm 9 giá trị. Chỉ cần nhập công thức vào một ô, kết quả tự động tràn xuống các ô bên dưới.

Xử lý ô trống và lỗi với tham số bỏ_qua

Dữ liệu thực tế thường có ô trống hoặc lỗi công thức. TOCOL mặc định giữ tất cả giá trị, trong đó ô trống hiển thị số 0 và các lỗi giữ nguyên. Điều này gây khó hiểu khi mảng gốc đã có giá trị 0 thật.

XEM THÊM: Cách Sử Dụng Hàm XLOOKUP Trong Excel 2021 Với 8 Ví Dụ Thực Tế

Tham số bỏ_qua có 4 giá trị:

0 hoặc bỏ trống: Giữ tất cả giá trị (mặc định)
1: Bỏ qua ô trống
2: Bỏ qua các lỗi
3: Bỏ qua cả ô trống và lỗi

Ví dụ thực tế:

Bảng dữ liệu A1:D5 chứa doanh số bán hàng với một số ô trống (chưa cập nhật) và ô lỗi (công thức tính sai):

=TOCOL(A1:D5, 1)

Công thức này bỏ qua các ô trống, chỉ trả về các giá trị thực tế. Kết quả là danh sách gọn gàng không có số 0 xen kẽ.

Để loại bỏ cả ô trống và lỗi trong một bước:

=TOCOL(A1:D5, 3)

Cách này tiết kiệm thời gian hơn so với việc dùng công thức phụ để lọc sau.

Quét theo cột thay vì theo hàng

Mặc định TOCOL đọc dữ liệu theo hàng từ trái sang phải. Trong một số trường hợp, bạn cần đọc theo cột từ trên xuống dưới. Tham số thứ ba kiểm soát thứ tự quét này.

Tham số quét_theo_cột:

FALSE hoặc bỏ trống: Quét theo hàng (mặc định)
TRUE hoặc 1: Quét theo cột

So sánh kết quả:

Với mảng 3×3 chứa số từ 1 đến 9:

A1: 1  B1: 2  C1: 3
A2: 4  B2: 5  C2: 6
A3: 7  B3: 8  C3: 9

Quét theo hàng (mặc định):

=TOCOL(A1:C3)

Kết quả: 1, 2, 3, 4, 5, 6, 7, 8, 9

Quét theo cột:

=TOCOL(A1:C3, , TRUE)

Kết quả: 1, 4, 7, 2, 5, 8, 3, 6, 9

Lưu ý dấu phẩy kép giữa tham số thứ nhất và thứ ba. Đây là cách bỏ qua tham số thứ hai khi không cần dùng. Thứ tự khác nhau này quan trọng khi dữ liệu có ý nghĩa theo cột, chẳng hạn doanh số theo tháng trong từng cột.

Kết hợp TOCOL với các hàm khác

Sức mạnh thực sự của TOCOL nằm ở khả năng kết hợp với các hàm mảng động khác trong Excel 2024.

XEM THÊM: Cách Sử Dụng Hàm CHOOSE Trong Excel Để Chọn Giá Trị Tự Động

Loại bỏ giá trị trùng lặp:

Sau khi chuyển đổi thành cột, dùng UNIQUE để lọc các giá trị duy nhất:

=UNIQUE(TOCOL(A1:E10, 1))

Công thức này chuyển vùng 5 cột thành một cột, bỏ qua ô trống, rồi loại trùng. Kết quả là danh sách gọn gàng không có giá trị lặp.

Sắp xếp kết quả:

Thêm SORT để sắp xếp danh sách theo thứ tự tăng dần:

=SORT(TOCOL(A1:E10, 1))

Hoặc kết hợp cả hai:

=SORT(UNIQUE(TOCOL(A1:E10, 1)))

Đây là cách tạo danh sách duy nhất và có thứ tự từ nhiều cột chỉ với một công thức. Trước đây công việc này cần ít nhất 3 bước riêng biệt.

Gộp nhiều vùng không liền kề:

Khi dữ liệu nằm rải rác trong bảng tính, dùng VSTACK để gộp rồi mới dùng TOCOL:

=TOCOL(VSTACK(A1:C5, F1:H5, K1:M5), 1)

VSTACK xếp chồng các vùng theo chiều dọc, TOCOL chuyển tất cả thành một cột và bỏ qua ô trống.

Các tình huống thực tế

Tình huống 1: Tạo danh sách sản phẩm từ nhiều danh mục

Bảng tính có 4 cột sản phẩm theo danh mục. Cần tạo danh sách tổng hợp để làm dropdown list:

=SORT(UNIQUE(TOCOL(B2:E50, 1)))

Danh sách tự động cập nhật khi thêm sản phẩm mới vào bất kỳ cột nào.

Tình huống 2: Gộp dữ liệu từ nhiều tháng

Báo cáo doanh số có mỗi tháng một cột. Cần tổng hợp toàn bộ để tính trung bình:

=AVERAGE(TOCOL(C2:N50, 1))

Công thức này bỏ qua ô trống (tháng chưa có dữ liệu) và tính trung bình chính xác.

Tình huống 3: Đếm giá trị duy nhất trong bảng nhiều cột

Thay vì đếm từng cột rồi cộng lại:

=COUNTA(UNIQUE(TOCOL(A1:F100, 1)))

Một công thức trả về số lượng giá trị duy nhất trong toàn bộ vùng 6 cột.

Lưu ý về hiệu năng và giới hạn

Hàm TOCOL xử lý nhanh với hầu hết bảng tính thông thường. Tuy nhiên khi làm việc với vùng dữ liệu lớn, có một số điểm cần lưu ý.

XEM THÊM: Hàm DROP Trong Excel 2024: Cách Tôi Loại Bỏ Hàng Và Cột Trong Vài Giây

Lỗi thường gặp:

Lỗi #NUM! xảy ra khi mảng quá lớn không vừa trong một cột của Excel. Điều này hiếm khi xảy ra trừ khi bạn chọn toàn bộ cột làm đối số.

Lỗi #SPILL! xuất hiện khi không đủ ô trống bên dưới để hiển thị kết quả. Xóa hoặc di chuyển dữ liệu cản trở phía dưới ô chứa công thức.

Giới hạn vùng dữ liệu:

Tránh dùng tham chiếu toàn cột như A:C vì sẽ tạo mảng quá lớn. Thay vào đó xác định vùng cụ thể như A1:C1000. Bạn có thể dùng công thức động để mở rộng vùng khi cần:

=TOCOL(A1:INDEX(C:C, COUNTA(C:C)), 1)

Công thức này tự động mở rộng đến hàng cuối cùng có dữ liệu trong cột C.

Khả năng tương thích

Hàm TOCOL chỉ có sẵn trong Excel for Microsoft 365 và Excel 2024 trên Windows, Mac và phiên bản web. Người dùng Excel 2021, 2019 hoặc các phiên bản cũ hơn không thể sử dụng hàm này.

Nếu chia sẻ file cho người dùng Excel cũ, công thức TOCOL sẽ hiển thị lỗi #NAME? trên máy của họ. Trong trường hợp này, cân nhắc chuyển đổi công thức thành giá trị tĩnh trước khi chia sẻ: sao chép kết quả, dán dưới dạng giá trị vào vị trí khác, rồi xóa công thức gốc.

Đối với Excel 2021, có thể tạo công thức tương tự bằng cách kết hợp các hàm cũ hơn nhưng phức tạp hơn nhiều. TOCOL đơn giản hóa quy trình này đáng kể.

Kết quả sau khi áp dụng

Hàm TOCOL giúp chuyển đổi dữ liệu nhiều cột thành một cột chỉ trong một công thức duy nhất. Kết hợp với UNIQUE và SORT tạo ra danh sách gọn gàng, không trùng lặp và có thứ tự. Công việc từng tốn 10-15 phút sao chép và sắp xếp thủ công giờ hoàn thành ngay lập tức.

Hàm này hoạt động tốt nhất trong Excel 365 hoặc Excel 2024. Người dùng có thể kiểm tra phiên bản Excel của mình tại File > Account > About Excel để xác nhận khả năng sử dụng các hàm mảng động mới này.