Một lỗi bảng tính phổ biến là thay đổi dữ liệu thô. Ai đó “chỉ sửa một thứ” trong bản sao duy nhất của dữ liệu hoặc vô tình ghi đè lên tập dữ liệu gốc và cuối cùng bạn phải mất hàng giờ để hoàn tác mớ hỗn độn đó. Tất cả chúng ta đều đã ở đó:việc khắc phục nhanh chóng sẽ biến thành cơn ác mộng về việc mất thông tin và kết quả đáng ngờ.
Để tránh điều này, hãy tuân theo quy tắc vàng:“Không chạm vào dữ liệu thô của bạn”. Bằng cách giữ nguyên tập dữ liệu gốc và làm việc trên các bản sao hoặc phiên bản phái sinh, bạn giảm thiểu lỗi, duy trì khả năng truy nguyên và làm cho quy trình làm việc của bạn dễ tái tạo hơn.
Trong hướng dẫn này, chúng ta sẽ tìm hiểu một cách điều chỉnh quy trình làm việc đơn giản có thể giúp bạn tránh khỏi nguy cơ mắc lỗi.
Tại sao quy trình công việc này lại quan trọng
- Chống lỗi: Dữ liệu thô là nguồn sự thật của bạn. Việc thay đổi trực tiếp (ví dụ:xóa hàng hoặc ghi đè ô) có thể gây ra những lỗi không thể khắc phục được, đặc biệt là trong các tập dữ liệu lớn hoặc phức tạp.
- Khả năng tái tạo: Nếu bạn cần xem lại bản phân tích của mình hoặc chia sẻ nó với người khác, thì việc có dữ liệu thô chưa được xử lý sẽ cho phép bạn truy lại các bước của mình mà không cần phỏng đoán.
- Kiểm soát phiên bản Lite: Xử lý dữ liệu thô ở dạng chỉ đọc bắt chước cách kiểm soát phiên bản cơ bản và bảo vệ bạn khỏi “thảm họa dữ liệu”.
- Hiệu quả: Việc dọn dẹp và chuyển đổi dữ liệu thành các trang tính riêng biệt giúp mọi thứ được ngăn nắp, giúp việc lặp lại hoặc tự động hóa sau này trở nên dễ dàng hơn.
Cách tiếp cận này đặc biệt hữu ích đối với các tập dữ liệu lộn xộn — ví dụ:khi bạn nhập tệp CSV có định dạng không nhất quán, trùng lặp hoặc thiếu giá trị. Thay vì chỉnh sửa tệp gốc, bạn sẽ sao chép và xóa nó trong một trang tính mới.
Hãy xây dựng quy trình làm việc từng bước này.
Bước 1:Nhập dữ liệu thô của bạn
- Mở tệp bảng tính mới
- Tạo trang tính mới có tên “Raw_Data”
- Nhập tập dữ liệu thô của bạn
- Đi tới Dữ liệu tab>> chọn Nhận dữ liệu>> chọn nguồn của bạn
Quy tắc chính:
Sau khi nhập, hãy khóa trang tính này để tránh vô tình chỉnh sửa.
- Nhấp chuột phải vào tab trang tính>> chọn Bảo vệ trang tính
- Đặt mật khẩu nếu cần
- Thêm ghi chú ở trên cùng:"Không chỉnh sửa. Chỉ dữ liệu nguồn."
Trang tính này bây giờ là kho lưu trữ không thể chạm tới của bạn. Không bao giờ chỉnh sửa ô ở đây.
Bước 2:Tạo bảng dọn dẹp
- Thêm trang tính mới có tên “Cleaned_Data”
- Tham khảo dữ liệu thô thay vì sao chép thủ công để tránh lỗi của con người
- Sử dụng công thức để lấy dữ liệu một cách linh hoạt
- Trong ô A1, chèn công thức sau:
- Kéo để điền vào phạm vi hoặc sử dụng công thức mảng để đạt hiệu quả:
Công thức này tạo ra một bản sao được liên kết. Bây giờ bạn có thể làm sạch trang tính này mà không ảnh hưởng đến bản gốc.
Bước 3:Làm sạch dữ liệu trong Sheet mới
Bây giờ hãy dọn sạch dữ liệu lộn xộn của bạn trong trang tính này. Làm việc theo từng cột hoặc sử dụng các công cụ tích hợp cho hoạt động hàng loạt.
Sửa các định dạng không nhất quán (ví dụ:Ngày):
- Giả sử ngày trong cột A không nhất quán.
- Trong cột mới, hãy sử dụng công thức để chuẩn hóa ngày:
Định dạng:
=IF(A2="","", IF(ISNUMBER(A2),A2, IFERROR( DATEVALUE(SUBSTITUTE(SUBSTITUTE(A2,"-","/"),".","/")), DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2)))))
Xử lý lỗi chính tả và sự không nhất quán:
- Sử dụng các công thức như PROPER() để chuẩn hóa cách viết hoa văn bản
Sửa lỗi chính tả:
- Nhấn Ctrl + H để mở Tìm và thay thế hộp thoại
- Thay thế các mục nhập sai bằng các giá trị đúng
- Nhấp vào Thay thế tất cả
Xóa nội dung trùng lặp:
- Sắp xếp cột nếu cần
- Đi tới Dữ liệu tab>> chọn Xóa nội dung trùng lặp
Xử lý các giá trị bị thiếu:
- Điền vào chỗ trống một cách hợp lý bằng cách sử dụng công thức như:
Thêm cột phái sinh nếu cần:
- Tổng doanh số bán hàng:
- Sau khi làm sạch, hãy sao chép cột mới và dán nó dưới dạng giá trị vào cột lộn xộn ban đầu
- Nhấp chuột phải>> chọn Dán Đặc biệt>> chọn Giá trị
Trong suốt quá trình, hãy ghi lại các thay đổi của bạn vào một tờ “Ghi chú” riêng biệt hoặc bằng cách sử dụng các nhận xét nội tuyến (Chèn>> Nhận xét).
Bước 4:Xây dựng bảng báo cáo (Phân tích dữ liệu của bạn)
Cách tốt nhất là tách phân tích thành một trang tính mới. Thêm một trang tính khác và đặt tên là “Phân tích”. Sử dụng dữ liệu từ trang tính “Cleaned_Data” cho các công thức, PivotTable hoặc truy vấn.
Tạo PivotTable:
- Đi tới Chèn tab>> chọn PivotTable
- Chọn phạm vi nguồn từ “Cleaned_Data”
- Chọn Vị trí>> nhấp vào OK
Xây dựng bản tóm tắt hàng tháng:
- Từ Trường PivotTable danh sách
- Kéo Vùng và Sản phẩm tới Hàng khu vực
- Kéo Ngày vào Cột khu vực
- Kéo Tổng doanh số tới Giá trị khu vực
Chèn bộ cắt:
- Đi tới Phân tích PivotTable tab>> chọn Chèn Bộ cắt
- Chọn Vùng
- Nhấp vào OK
Bây giờ báo cáo của bạn phụ thuộc vào dữ liệu sạch — không phải xuất dữ liệu lộn xộn. Điều này giúp việc dọn dẹp tách biệt khỏi thông tin chi tiết và cho phép làm mới dễ dàng nếu bạn cập nhật logic dọn dẹp của mình.
Bước 5:Tạo thói quen “Làm mới” (Thói quen tiết kiệm thời gian)
Mỗi lần xuất mới:
- Thay thế dữ liệu trong trang tính “Raw_Data” (giữ nguyên tiêu đề)
- Làm không chỉnh sửa bất kỳ giá trị nào trong trang tính “Raw_Data”
- Cập nhật bảng “Cleaned_Data”
- Đặt thời gian làm mới tự động hoặc làm mới theo cách thủ công
- Đi tới Dữ liệu tab>> nhấp vào Làm mới tất cả
Điều này biến việc báo cáo hàng tuần thành một quy trình có thể lặp lại thay vì một dự án dọn dẹp thủ công.
Bước 6:Lưu và lập phiên bản tệp của bạn
- Lưu dưới dạng: Sử dụng tên tệp như “Project_Data_v1.xlsx” và tăng số phiên bản theo thời gian
- Để cộng tác: Chia sẻ các phiên bản chỉ đọc để duy trì tính toàn vẹn của quy trình làm việc
- Tự động hóa: Tìm hiểu Power Query trong Excel để tải dữ liệu thô vào truy vấn tự động làm sạch và làm mới mà không cần chạm vào trang tính thô
Tóm tắt
Bằng cách làm theo các bước này, bạn có thể xây dựng quy trình làm việc để bảo vệ dữ liệu thô của mình trong khi vẫn giữ cho phân tích của bạn linh hoạt. Giữ nguyên các bản xuất thô trong một tờ và thực hiện làm sạch trong một tờ riêng. Toàn bộ quy trình làm việc trở nên an toàn hơn, dễ làm mới hơn và đáng tin cậy hơn nhiều — đặc biệt là khi làm việc với các tập dữ liệu lộn xộn.
Hãy bắt đầu từ quy mô nhỏ với tập dữ liệu tiếp theo và bạn sẽ nhanh chóng nhận thấy quy trình báo cáo của mình trở nên suôn sẻ hơn như thế nào.
Nhận MIỄN PHÍ Bài tập Excel nâng cao có Giải pháp!