Tách cột là chia một ô chứa nhiều thông tin, như "Áo thun trắng - M - 2", thành nhiều cột, mỗi cột một thuộc tính; ghép cột là việc ngược lại, nối nhiều cột thành một chuỗi để in, dán hay làm khoá tra cứu. Cả hai việc đều có công cụ sẵn trong Excel và Google Sheets, nhưng mỗi công cụ có một bẫy riêng: ghi đè cột bên cạnh, mất số 0 đầu, ngày biến thành số năm chữ số, hay đoán sai mẫu ở những dòng không đều.
Bài này so sánh các cách tách và ghép, chỉ ra khi nào dùng cách nào, cách nhờ AI viết công thức khi mẫu dữ liệu không đều, và cách kiểm để chắc không có dòng nào bị lệch cột. Ví dụ trong bài dùng bảng đơn hàng của một cửa hàng quần áo nhỏ, số liệu giả định.
Vì sao một ô không nên chứa nhiều thông tin
Một bảng dữ liệu dễ dùng tuân theo nguyên tắc mỗi cột một thuộc tính, mỗi dòng một bản ghi. Ô "Áo thun trắng - M - 2" vi phạm nguyên tắc đó: nó gộp tên hàng, cỡ và số lượng. Bạn không cộng được tổng số lượng, không lọc được những đơn cỡ M, không đếm được áo trắng bán bao nhiêu chiếc.
Những ô gộp như vậy sinh ra tự nhiên khi nhập nhanh: nhân viên gõ theo thói quen ghi sổ, phần mềm xuất ra một cột mô tả gộp, hoặc khách điền biểu mẫu một ô tự do. Đôi khi ngược lại, thông tin bị chia quá vụn: số nhà một cột, tên đường một cột, mà lúc in phiếu giao hàng lại cần một dòng địa chỉ liền.
Vì vậy tách và ghép thường đi cùng nhau: tách để lưu và tính toán, ghép để hiển thị và trao đổi. Nguyên tắc chung là lưu ở dạng tách nhỏ nhất bạn cần, rồi ghép bằng công thức khi cần hiển thị, đừng làm ngược lại.
Trước khi tách, lưu bản gốc và thêm vài cột trống bên phải cột cần tách. Đây là bước nhỏ nhưng tránh được lỗi hay gặp nhất, như phần dưới sẽ nói.
Chọn công cụ tách theo tình huống
Excel và Google Sheets có ít nhất bốn cách tách. Bảng dưới so sánh để bạn chọn đúng cách cho từng việc.
| Cách | Hợp với | Bẫy cần tránh |
|---|---|---|
| Text to Columns (Excel), Split text to columns (Google Sheets) | Tách một lần, dữ liệu đều đặn theo một ký tự phân cách | Ghi đè cột bên phải; tự đổi mã có số 0 đầu thành số nếu không đặt kiểu Text |
| Flash Fill, phím tắt Ctrl+E (Excel 2013 trở đi) | Mẫu dễ thấy bằng mắt nhưng khó diễn tả bằng ký tự phân cách | Đoán mẫu từ vài ví dụ, có thể sai ở dòng không đều; kết quả là giá trị tĩnh, không tự cập nhật |
| Hàm TEXTSPLIT, TEXTBEFORE, TEXTAFTER (Excel 365 và bản mới), SPLIT (Google Sheets) | Dữ liệu được bổ sung thường xuyên, cần kết quả tự cập nhật | Không có ở Excel cũ; kết quả tràn sang ô bên cạnh, báo lỗi nếu ô đó đã có dữ liệu |
| Power Query, Split Column | Việc lặp lại hằng tuần, tách thành cột hoặc thành dòng | Cần làm quen giao diện; nhớ làm mới truy vấn khi có dữ liệu mới |
Nếu chỉ làm một lần với vài trăm dòng đều đặn, Text to Columns là nhanh nhất. Nếu bảng nhận thêm dữ liệu mỗi ngày, dùng hàm hoặc Power Query để không phải tách lại bằng tay.
Tách bằng Text to Columns mà không làm hỏng bảng
Đây là cách phổ biến nhất và cũng là nơi nhiều người mất dữ liệu. Làm theo thứ tự:
- Chèn đủ số cột trống bên phải cột cần tách. Ô "Áo thun trắng - M - 2" tách ra ba phần, cần ít nhất hai cột trống, vì phần đầu ở lại cột gốc. Không chèn thì Excel ghi đè lên cột kế bên mà chỉ hỏi một câu xác nhận dễ bấm nhầm.
- Chọn cột, vào Data, Text to Columns, chọn Delimited, bấm Next.
- Chọn ký tự phân cách. Với chuỗi ngăn bằng " - ", chọn Other và gõ dấu gạch ngang; khoảng trắng hai bên sẽ được bỏ ở bước sau bằng TRIM. Nếu phân cách là dấu phẩy mà tên hàng cũng có dấu phẩy, cách này sẽ tách sai; khi đó dùng hàm hoặc đổi ký tự phân cách trước.
- Ở bước 3, chọn từng cột trong khung xem trước và đặt kiểu: cột mã hàng, mã khách đặt Text để giữ số 0 đầu; cột ngày đặt Date với thứ tự DMY; cột không cần thì chọn Do not import column.
- Bấm Finish, rồi dùng TRIM cho các cột mới và kiểm như phần cuối bài.
Google Sheets có lệnh tương tự trong menu Data, Split text to columns, tự nhận ký tự phân cách và cũng ghi đè các cột bên phải. Chèn cột trống trước là thói quen nên giữ ở cả hai công cụ.
Dùng hàm và Flash Fill khi dữ liệu không đều
TEXTSPLIT cho kết quả tự cập nhật. Ở Excel 365, =TRIM(TEXTSPLIT(A2,"-")) tách ô theo dấu gạch ngang và bỏ khoảng trắng thừa, kết quả tràn sang các ô bên phải. Khi ô gốc đổi, kết quả đổi theo. Với Google Sheets, =SPLIT(A2,"-") làm việc tương tự.
TEXTBEFORE và TEXTAFTER cho phần đầu, phần cuối. =TEXTBEFORE(A2," - ") lấy tên hàng trước lần phân cách đầu tiên; =TEXTAFTER(A2," - ",-1) lấy phần sau lần phân cách cuối cùng, hợp để lấy số lượng dù tên hàng dài ngắn khác nhau. Đặt số lượng vào hàm VALUE để thành số tính được.
Flash Fill khi mẫu dễ thấy. Gõ kết quả mong muốn cho hai ba dòng đầu, chọn ô tiếp theo và bấm Ctrl+E. Excel đoán mẫu và điền phần còn lại. Hữu ích khi tách kiểu "lấy chữ đầu của tên đệm" mà hàm khó diễn tả. Nhưng Flash Fill chỉ đoán; hãy lọc và soát những dòng có cấu trúc khác thường.
Nhờ AI khi mẫu lộn xộn. Mô tả cho AI, ví dụ: "Cột A chứa mô tả đơn hàng dạng tên hàng, màu, cỡ, số lượng, nhưng có dòng thiếu màu, có dòng ghi số lượng kèm chữ cái, có dòng dùng dấu gạch chéo thay gạch ngang. Dưới đây là mười dòng mẫu giả định. Viết công thức Excel 365 tách thành bốn cột, cột nào thiếu để trống, dòng không nhận ra thì ghi KIỂM TRA." Thử trên bộ mẫu trước khi áp dụng.
Tách thành dòng thay vì thành cột. Ô "áo, quần, mũ" trong một đơn có ba món nên được tách thành ba dòng, mỗi dòng một món, chứ không phải ba cột. Power Query làm được việc này bằng Split Column, chọn tách thành Rows. Excel 365 có thể kết hợp TEXTSPLIT với TOCOL; nhờ AI viết công thức cụ thể cho bảng của bạn.
Tách theo độ dài cố định: mã hàng, số chứng từ
Không phải chuỗi nào cũng có ký tự phân cách. Nhiều mã hàng, mã chứng từ được dựng theo vị trí: ví dụ giả định mã gồm hai chữ cái đầu là nhóm hàng, bốn chữ số tiếp là số thứ tự, hai ký tự cuối là màu. Với kiểu này, tách theo vị trí ký tự chứ không theo dấu.
Hàm LEFT, MID, RIGHT làm việc này gọn: =LEFT(A2,2) lấy nhóm hàng, =MID(A2,3,4) lấy số thứ tự, =RIGHT(A2,2) lấy mã màu. Text to Columns cũng có lựa chọn Fixed width, cho bạn kéo vạch chia trong khung xem trước. Nhớ đặt kiểu Text cho phần số thứ tự để giữ số 0 đầu.
Cách này chỉ đúng khi mọi mã cùng độ dài và cùng cấu trúc. Trước khi tách, dùng LEN đếm độ dài từng mã và lọc những mã dài ngắn khác thường; đó thường là mã nhập sai, mã cũ theo quy tắc khác, hoặc mã dính khoảng trắng. Xử lý riêng nhóm đó thay vì để công thức cắt lệch.
Nếu không chắc quy tắc dựng mã, đừng đoán từ vài mã mẫu. Hỏi người lập danh mục hàng hoặc xem tài liệu của phần mềm. AI có thể gợi ý những quy luật nó thấy trong một danh sách mã mẫu, nhưng chỉ người trong cuộc mới xác nhận được quy luật đó có đúng cho mọi mã hay không.
Ghép cột không mất định dạng
Cách ghép đơn giản nhất là toán tử &: =B2&", "&C2&", "&D2. Excel 2019 trở đi và Google Sheets có thêm TEXTJOIN, tiện hơn khi có ô trống: =TEXTJOIN(", ",TRUE,B2:D2) nối các ô bằng dấu phẩy và bỏ qua ô trống, không để lại hai dấu phẩy liền nhau. Hàm CONCAT nối liền không có ký tự phân cách.
Bẫy lớn nhất khi ghép là ngày và số. Ghép một ô ngày vào chuỗi sẽ ra con số năm chữ số thay vì ngày, vì Excel lấy giá trị thật bên dưới chứ không lấy thứ đang hiển thị. Cách sửa là bọc bằng TEXT: =B2&" giao ngày "&TEXT(C2,"dd/mm/yyyy"). Tương tự, số tiền cần TEXT với định dạng có dấu phân cách hàng nghìn nếu muốn hiển thị đẹp, và mã hàng có số 0 đầu phải được lưu dạng văn bản từ trước thì khi ghép mới còn số 0.
Lưu ý chuỗi định dạng trong hàm TEXT phụ thuộc ngôn ngữ của Excel ở một số bản; nếu "dd/mm/yyyy" không chạy, thử mô tả cho AI phiên bản và ngôn ngữ giao diện của bạn để nhận chuỗi định dạng phù hợp.
Khi ghép để làm khoá tra cứu, như nối mã khách với ngày đơn, chèn một ký tự ngăn cách hiếm gặp ở giữa, chẳng hạn dấu gạch đứng. Không có ký tự ngăn cách, mã "12" nối "34" và mã "1" nối "234" cho ra cùng một chuỗi.
Kiểm sau khi tách và ghép
Phép kiểm quan trọng nhất sau khi tách là xem có dòng nào bị lệch cột không. Lấy một cột lẽ ra luôn là số, như số lượng, dùng ISNUMBER sau VALUE để đếm ô không phải số; những ô đó thường là dòng có thêm hoặc thiếu một phần, làm mọi phần phía sau trượt sang cột khác. Đếm thêm số ô trống ở từng cột mới; một cột lẽ ra luôn có giá trị mà có ô trống là dấu hiệu tương tự.
Phép kiểm thứ hai là ghép ngược. Nối các cột vừa tách lại bằng đúng ký tự phân cách ban đầu, rồi so với cột gốc đã TRIM bằng phép so sánh bằng. Dòng nào ra FALSE là dòng tách không trọn vẹn. Cách này phát hiện được cả những chỗ mất ký tự mà mắt thường bỏ qua.
Phép kiểm thứ ba dành cho số và mã: tổng số lượng trước và sau khi tách phải khớp nếu trước đó bạn có tổng ở nơi khác, như sổ kho hay báo cáo từ phần mềm; mã hàng sau khi tách phải tra được trong danh mục hàng. Mã không tra được thường là mã bị mất số 0 đầu hoặc dính khoảng trắng.
Nếu bảng chứa họ tên, số điện thoại khách cạnh mô tả đơn hàng, chỉ đưa cho AI cột mô tả đã thay bằng ví dụ giả định khi xin công thức. Khi đã có bảng tách gọn, bạn sẵn sàng cho bước tổng hợp và đọc số liệu, phần mà trang AI Phân Tích đi sâu hơn.
Câu hỏi thường gặp
Text to Columns làm mất dữ liệu cột bên cạnh, có lấy lại được không?
Bấm hoàn tác ngay nếu chưa lưu tệp. Nếu đã lưu, phải lấy lại từ bản sao hoặc lịch sử phiên bản của tệp. Để tránh lặp lại, luôn chèn đủ cột trống bên phải trước khi tách.
Vì sao ghép ô ngày vào chuỗi lại ra một con số lạ?
Vì Excel lưu ngày dưới dạng số đếm từ mốc năm 1900 và khi ghép nó lấy con số đó chứ không lấy phần hiển thị. Bọc ô ngày bằng hàm TEXT với định dạng dd/mm/yyyy để giữ dạng ngày.
Excel không có hàm TEXTSPLIT thì tách cột bằng gì?
TEXTSPLIT chỉ có ở Excel 365 và các bản mới. Với bản cũ, dùng Text to Columns, Flash Fill, hoặc kết hợp FIND, LEFT, MID, RIGHT. Power Query có sẵn từ Excel 2016 cũng tách được và lặp lại được.
Flash Fill có đáng tin để tách dữ liệu lớn không?
Flash Fill chỉ đoán mẫu từ vài ví dụ bạn gõ, nên có thể sai ở những dòng có cấu trúc khác thường. Dùng được cho dữ liệu đều, nhưng luôn kiểm bằng cách ghép ngược hoặc lọc các dòng bất thường.
Một ô chứa nhiều món hàng thì nên tách thành cột hay thành dòng?
Thường nên tách thành dòng, mỗi dòng một món, để cộng số lượng và lọc theo món dễ dàng. Power Query có tuỳ chọn tách cột thành nhiều dòng; bảng tách thành nhiều cột món sẽ khó tính toán khi số món mỗi đơn khác nhau.
TEXTJOIN khác gì toán tử nối chuỗi thông thường?
TEXTJOIN cho phép đặt một ký tự ngăn cách chung và bỏ qua ô trống, nên chuỗi kết quả không có dấu phẩy thừa. Toán tử nối thông thường ghép đúng những gì bạn viết, ô trống vẫn để lại ký tự ngăn cách.