Thế nào là bảng tính đồng nhất và không đồng nhất?
Khi thao tác với hàm hoặc công thức Excel, bạn phải chọn ít nhất một cột để làm cơ sở cho việc xử lý. Dữ liệu đồng nhất là dữ liệu trên cột được chọn có một cấu trúc như nhau, bạn có thể ứng dụng ngay các hàm về tra cứu, lọc, sắp xếp một cách dễ dàng chỉ bằng một công thức và sao chép cho toàn bộ các dòng còn lại.
Bảng tính không đồng nhất là bảng tính mà dữ liệu trên một cột chọn làm cơ sở để xử lý cũng không đồng nhất, dẫn tới việc dù có đặt hàm, lọc, sử dụng hàm tra cứu cũng không thể xử lý chung cho toàn bộ bảng tính.
Dưới đây là một tình huống thực tế về xử lý dữ liệu không đồng nhất:
Bạn nhận được một danh sách hàng nghìn mặt hàng và chưa có mã hàng do chỉ mới lấy dữ liệu thô ngoài thị trường. Các mặt hàng đều không có mã hàng và mã nhóm, bạn được yêu cầu phải căn cứ trên tên hàng để phân chia thành từng nhóm hàng để quản lý dễ hơn, và muốn phần ký tự kích thước không gắn với tên hàng mà phải tách thành cột riêng. Bạn không muốn có tên hàng trùng lắp, và cũng muốn kiểm tra xem bảng tính của mình có trùng tên với danh sách đã có hay không, v.v...
Với những yêu cầu như trên đối với một cột tên hàng không đồng nhất về cấu trúc (tên hàng được viết tự do, không quy định về số lượng ký tự, mã hiệu...), bạn chỉ còn cách căn cứ vào từ khóa của tên hàng kết hợp các chức năng nâng cao của Excel để xử lý dữ liệu theo ý muốn một cách nhanh chóng.
Tóm tắt công việc
Các bước dưới đây nhằm tạo ra một đặc điểm nhận dạng của từng nhóm dữ liệu có điểm chung nào đó, kết hợp chức năng lọc, sắp xếp để lấy ra hoặc xử lý hàng loạt dữ liệu, biến dữ liệu không đồng nhất trở thành dữ liệu đồng nhất về cấu trúc, rút ngắn thời gian xử lý:
- Sử dụng chức năng đánh dấu màu sắc theo từ khóa.
- Lọc dữ liệu bằng chức năng Filter by Color (lọc bằng màu sắc).
- Tách cột bằng chức năng Convert to column.
- Loại bỏ ký tự không mong muốn bằng chức năng Replace.
- Gỡ bỏ các tên trùng lắp.
- Phát hiện các ký tự trùng nhau.
- Ứng dụng hàm Vlookup.
Thực hiện
1. Chức năng tô màu theo từ khóa:
Công việc bạn sẽ thực hiện là phân nhóm hàng theo nguyện liệu, và ghi chú mã nhóm vào cột bên cạnh.
Bạn thao tác lần lượt đối với mỗi chất liệu bằng cách: chọn hết cột tên hàng, vào menu Home, chọn Conditional Formating, chọn High light, chọn Text that contains và gõ từ “nhựa” vào, ngay lập tức, các tên hàng nào có chứa từ “nhựa” sẽ được tô màu.
2. Lọc dữ liệu bằng chức năng Filter (lọc bằng màu sắc):
Sau khi tô màu cho hàng nhựa, bạn bấm vào ô vừa mới được tô màu, chọn Filter > Filter by cell’s color, lúc này màn hình chỉ hiển thị các ô có màu. Bạn ghi mã nhóm mà bạn đặt cho nhóm nhựa vào cột bên cạnh (ghi cho ô đầu tiên, rồi kéo kiểu copy xuống ô cuối cùng. Sau đó, bấm chuột phải vào ô có màu, chọn Clear filter.
Bảng tính sẽ hiển thị đầy đủ mã hàng, nhưng quan sát sẽ thấy những mặt hàng nhựa đã có mã nhóm ghi chú bên cạnh.
Bạn tiếp tục thao tác lại đối với các chất liệu khác, cho đến khi đã thiết lập xong mã nhóm cho tên hàng. Lưu ý, trước khi chuyển qua chất liệu khác, bạn phải vào Conditions formatings, chọn Clear rules from entire sheet để bỏ màu của nhóm đã làm trước đó.
3. Tách cột bằng chức năng Convert to column:
Bạn muốn tách phần kích thước ghi trong ngoặc của tên hàng sang một cột riêng, là cột kích thước. Excel cho phép người dùng tách dữ liệu trong một cột ra làm nhiều cột dựa trên một ký tự do người dùng chỉ định làm căn cứ để tách. Ở đây, phần tên hàng và kích thước đều được ngăn cách bởi dấu mở ngoặc, nói cách khác, nếu cắt tất cả các tên hàng tại điểm cắt là dấu đóng ngoặc, thì hầu hết các kích thước sẽ tách khỏi tên hàng.
Bạn copy cột tên hàng qua cột mã nhóm kế tiếp (để tránh việc dữ liệu sau khi tách sẽ đè lên cột mã nhóm, nếu cột tên hàng đứng trước cột mã nhóm), vào menu Data, chọn Text to Column, đánh dấu chọn vào Delimited bấm Next, đánh dấu vào ô Others và đánh dấu mở ngoặc vào ô kế bên để ấn định dấu đóng ngoặc là điểm chia cắt, chọn Next và Ok. Dữ liệu sẽ tách thành hai cột, và dấu ngoặc sẽ biến mất, chỉ còn lại dấu đóng ngoặc. Bạn làm thao tác tương tự với cột có dấu đóng ngoặc, và tiêu chuẩn chia cắt là dấu đóng ngoặc, bạn sẽ có cột kích thước như ý.
4. Loại bỏ ký tự không mong muốn bằng chức năng Replace:
Để loại bỏ ký tự đóng ngoặc trong phần tách cột, bạn có thể áp dụng chức năng thay thế ký tự, nghĩa là thay thế ký tự đóng ngoặc bằng ký tự trống, bạn cũng được kết quả tương tự. Chức năng Replace cho phép người dùng thay thế ký tự bằng một ký tự khác, có thể làm hàng loạt đối với dữ liệu có quá nhiều ký tự cần thay thế.
Chọn vùng dữ liệu có chứa các ký tự cần thay thế, bấm tổ hợp phím Control+F, ở vùng Find what nhập từ cần thay thế, vùng Replace with, nhập chữ sẽ thế vào, bấm Replace all để thay thế toàn bộ kết quả tìm được.
5. Gỡ bỏ các tên trùng lắp:
Bạn không muốn trên một cột có những dữ liệu trùng nhau, và mỗi tên có duy nhất một dòng. Để thực hiện, bạn chọn vùng dữ liệu cần thao tác, vào menu Data, chọn Replace Duplicate, chọn cột dữ liệu, và bấm Ok. Các dữ liệu trùng sẽ loại bỏ, chỉ giữ lại một mã trong số các mã bị trùng.
Chức năng này đặc biệt hữu ích khi bạn dùng hàm sumif để thống kê tổng của mỗi nhóm hàng. Thao tác này giúp bạn có ngay một cột mã nhóm để thống kê, mà không có mã nhóm nào xuất hiện nhiều hơn một lần trong bảng thống kê tổng.
6. Phát hiện các dữ liệu trùng nhau:
Nếu như việc gỡ bỏ dữ liệu trùng chỉ cho phép loại bỏ, không thể biết được dữ liệu nào là dữ liệu trùng, bạn cần sử dụng lại chức năng tô màu của bước 1, nhưng lần này, sẽ tô màu theo điều kiện là những dữ liệu giống nhau.
Quét chọn vùng dữ liệu cần thao tác, chọn Conditions Formating, chọn Hight light, chọn Duplicate Value. Các dữ liệu trùng nhau sẽ được tô màu.
7. Ứng dụng hàm Vlookup
Vlookup là hàm dùng để tra cứu dựa trên giá trị tham chiếu và bảng tham chiếu để điền nội dung vào dòng giá trị tương ứng. Có thể vận dụng hàm này để nhận biết một số vấn đề hữu ích khi kiểm tra dữ liệu.
Khi chạy hàm, nếu không tìm thấy giá trị cần tra trong bảng tra mà kết quả trả về là #N/A, có nghĩa tại dòng có giá trị #N/A là những giá trị không có trong bảng cần kiểm tra. Áp dụng đặc điểm này để nhận dạng mặt hàng nào ở trong bảng tính trùng với danh sách hoặc không có trong danh sách cần đối chiếu của bạn.
Việc kết hợp nhuần nhuyễn 7 thao tác như trên, đảm bảo bạn có thể làm chủ hoàn toàn dữ liệu, có thể “xáo trộn”, biến đổi dữ liệu theo ý muốn một cách nhanh nhất, đặc biệt là các dữ liệu thô, số lượng lớn. Tuy nhiên, việc xử lý có hiệu quả hay không cũng tùy thuộc vào khả năng đánh giá sơ bộ ban đầu của người dùng và đưa ra các kết hợp thao tác hợp lý.
