Mục đích của chúng ta khi học Excel đó là có thể tổ chức dữ liệu tốt, từ đó lập được các báo cáo theo yêu cầu công việc. Thế nhưng chúng ta cũng gặp phải vô vàn những thắc mắc như:
- Tổ chức dữ liệu như thế nào là tốt?
- Làm thế nào để kiểm tra dữ liệu trên Excel để đảm bảo tính chính xác của dữ liệu?
- Làm thế nào để lập được các báo cáo trên Excel?
- Báo cáo thế nào là đẹp, khoa học?
- …
Nhằm giúp các bạn hình thành tư duy tốt trong việc tổ chức dữ liệu và lập báo cáo trên Excel, Học Excel Online xin giới thiệu với các bạn seri bài viết về “Kỹ năng tổ chức, quản lý dữ liệu và lập báo cáo trên Excel”. Bài viết của tác giả Dương Mạnh Quân – Giảng viên của Học Excel Online, dựa trên những kinh nghiệm giảng dạy trong thực tế của thầy liên quan tới nội dung này. Mời các bạn cùng tìm hiểu cách xử lý lỗi định dạng số trong excel nhé.
Bài 4: Hướng dẫn cách xử lý lỗi dữ liệu dạng số [Number] trong Excel
Việc sai dữ liệu dạng số có ảnh hưởng rất lớn trong quá trình tính toán và lập báo cáo. Bởi kết quả của báo cáo phụ thuộc rất lớn vào nội dung này. Lỗi về dữ liệu dạng số có thể kể tới như:
- Sai về định dạng dữ liệu: có phần thập phân hay không, dữ liệu là % hay dạng số thông thường, có lẫn lộn giữa dữ liệu dạng ngày tháng [date] vào trong dữ liệu dạng số không [vì dữ liệu dạng ngày tháng về bản chất cũng là dạng số]
- Sai về thao tác nhập dữ liệu: Trong quá trình nhập có lẫn 1 ký tự text vào chung với ký tự số, dẫn tới Excel sẽ coi đó là dữ liệu Text thay vì dạng Number
Vậy làm thế nào để phát hiện những lỗi dữ liệu dạng số đã có trong 1 bảng dữ liệu? Chúng ta cùng tìm hiểu nhé.
1. Phát hiện lỗi dữ liệu dạng số với bộ lọc tự động trong Excel [công cụ AutoFilter]
Về cách mở công cụ AutoFilter thì mình đã hướng dẫn ở trong bài học trước, mời các bạn xem lại bài:
Hướng dẫn cách sắp xếp ngày tháng trong excel
2. Nguyên tắc sắp xếp dữ liệu dạng số trong AutoFilter
Với dữ liệu dạng số sẽ được tự động sắp xếp trong cửa sổ dữ liệu lọc của AutoFilter theo quy tắc:
- Tăng dần từ nhỏ tới lớn [nếu có số âm thì sẽ tính cả từ phần số âm trở đi chứ không phải bắt đầu từ số 0]
- Nếu có chứa ký tự không phải dạng số thì sẽ nằm ở cuối danh sách lọc tự động này.
Trong hình trên chúng ta có thể thấy số 124,235 là số nhỏ hơn 12,489,621 nhưng lại được xếp phía sau => Số đó đang bị sai loại dữ liệu bởi vì có chứa dấu cách
3. Nguyên tắc dữ liệu dạng Number có thể tính toán được
Ngoài cách sử dụng AutoFilter để phát hiện dữ liệu lỗi, chúng ta có thể dựa trên 1 nguyên tắc nữa để phát hiện dữ liệu lỗi:
Dữ liệu dạng Number có thể tính toán [cộng, trừ, nhân, chia] được; còn dữ liệu dạng Text thì không.
Theo nguyên tắc này chúng ta có thể kiểm tra xem dữ liệu nghi ngờ là sai có thể thực hiện các phép toán cơ bản không. Nếu ra lỗi #VALUE! thì không phải dữ liệu dạng Number.
4. Cách xử lý, sửa lỗi dữ liệu
Bước 1: Thống nhất về định dạng dữ liệu
Chọn toàn bộ cột dữ liệu có chứa dữ liệu dạng số[ví dụ ở đây là cột E]
Vào mục Format Cells > Chọn mục Number theo hình sau
- Decimal places: số sau dấu phẩy là mấy chữ số
- Use 1000 separator : Sử dụng dấu ngăn cách hàng nghìn
Tương tự dạng Number có thể sử dụng các dạng Accounting hoặc Currency [nếu không sử dụng ký hiệu loại tiền tệ thì chọn None]
Bước này giúp chúng ta thống nhất về cách hiển thị dữ liệu, định dạng dữ liệu khi nhập vào để tránh việc quy ước hiển thị dữ liệu không đồng nhất trong cùng 1 cột.
Bước 2: Lọc những lỗi ký tự để sửa [cách thứ 1]
Căn cứ vào giá trị lớn nhất trong dãy số đã được sắp xếp tự động trong Auto Filter:
- Phần phía trước số đó: Là những số đã được định dạng đúng
- Phần phía sau số đó: Là những số có lỗi
Sau khi lọc những số có lỗi thì thực hiện nhập lại các số đó
Bước 3: Sử dụng hàm Value để sửa [cách thứ 2]
Ngoài việc sửa trực tiếp vào bảng, có thể tạo cột phụ và sử dụng hàm VALUE trong cột phụ đó để chuyển giá trị trong bảng về đúng dữ liệu dạng số.
Trình tự thực hiện:
- Trước khi sửa lỗi cần copy dữ liệu gốc ra 1 Sheet mới và xử lý trên nội dung copy ở Sheet mới để đảm bảo không bị mất dữ liệu.
- Thừa ký tự: các dấu cách thừa, dấu chấm, dấu phẩy, dấu chấm phẩy, dấu nháy… lẫn vào ký tự dạng số => Sử dụng chức năng Replace để xóa bỏ các ký tự thừa
- Ở các định dạng khác [không phải dạng Number nhưng đã đảm bảo chỉ toàn ký tự dạng số] thì sử dụng hàm Value trực tiếp cho các giá trị đó. Sau khi có kết quả hàm Value thì sử dụng chức năng Copy/Paste special>Value để trả về kết quả vào bảng dữ liệu.
Bước 5: Kiểm tra lại nội dung xem những dữ liệu lỗi đã được sửa thành công chưa
Để cho chắc chắn chúng ta nên có thao tác kiểm tra lại dữ liệu để tránh việc sửa bị sót, thiếu.
Nên lưu trữ 1 bản copy dữ liệu gốc để tránh việc làm mất dữ liệu trong quá trình xử lý.
Bài tiếp theo: Hướng dẫn cách xử lý lỗi dữ liệu dạng Text trong Excel
Ngoài ra Học Excel Online xin giới thiệu với các bạn “Khóa học Excel từ cơ bản tới nâng cao dành cho người đi làm“. Đây là 1 khóa học rất đầy đủ kiến thức và bổ trợ rất tuyệt vời cho bạn trong việc làm quen với Excel, sử dụng các công cụ, các hàm trong Excel để đáp ứng yêu cầu công việc tổ chức, quản lý dữ liệu và lập báo cáo trên Excel, đăng ký bằng cách bấm vào ảnh phía dưới:
Khám phá ngay 101 cách sắp xếp trong Excel không phải ai cũng biết!
Khi làm việc trên bảng tính, dữ liệu quá nhiều và lộn xộn, buộc bạn phải sắp xếp trong Excel để tiện quan sát và làm việc. Tuy nhiên, bạn lại không biết làm cách nào để xắp xếp, phân loại chúng. Vậy, hãy cùng ThuthuatOffice đi khám phá những cách sắp xếp trong Excel tại bài viết ngày hôm nay nhé! Chắc chắn sẽ giúp bạn rất nhiều trong công việc đấy! Khám phá ngay thôi nào.
Cách sắp xếp trong Excel
Hãy cùng khám phá những cách sắp xếp trong Excel vô cùng đơn giản dưới đây nhé.
Hàm sắp xếp thứ tự trong Excel
Đối với các trang tính trong Excel, bạn có thể dùng hàm Rank để sắp xếp, phân hạng các dữ liệu.
Công thức của hàm Rank: =RANK[number;ref;[order]]
Trong đó:
- Number: các giá trị cần xếp hạng.
- Ref:Danh sách, khối dữ liệu cần sắp xếp.
- Order: Thứ tự cần sắp xếp, kiểu sắp xếp [tăng dần hoặc giảm dần]. Khi order = 0 [hoặc không có tham số này] thì sẽ theo thứ tự giảm dần. Còn order = 1 sẽ tính theo thứ tự tăng dần.
Các trường hợp sử dụng hàm Rank:
- Khi bạn muốn sắp xếp trong Excel theo thứ tự tăng dần.
- Khi bạn muốn sắp xếp trong Excel theo thứ tự giảm dần.
Cách sắp xếp trong Excel theo thứ tự giảm dần
Bước 1: Tại ô đầu tiên, bạn nhập công thức: =RANK[B2,$B$2:$B$5,1]
Bước 2:NhấnEnterđể áp dụng công thức.
Bước 3: Để áp dụng công thức cho các ô còn lại, bạn chỉ cần đặt con trỏ chuột vào góc dưới ô chứa kết quả trên cho xuất hiện dấu cộng rồi kéo xuống dưới là xong. Và cuối cùng bạn sẽ nhận được kết quả như thế này.
Cách sắp xếp trong Excel theo thứ tự tăng dần
Tương tự đối với cách làm trên, khi muốn sắp xếp trong Excel theo thứ tự giảm dần bạn cũng chỉ cần áp dụng những bước cơ bản sau:
Bước 1: Nhập công thức=RANK[B2,$B$2:$B$5,0]vào ô đầu tiên trong cột xếp hạng.
Bước 2: Bấm Enter để áp dụng công thức.
Bước 3: Để áp dụng công thức cho các ô còn lại, bạn chỉ cần đặt con trỏ chuột vào góc dưới ô chứa kết quả trên cho xuất hiện dấu cộng rồi kéo xuống dưới là xong. Và cuối cùng bạn sẽ nhận được kết quả như thế này.
Cách sắp xếp trong Excel theo thứ tự abc
Để sắp xếp trong Excel theo thứ tự bảng chữ cái. Bạn hãy theo dõi những bước làm dưới đây nhé:
Bước 1: Mở bảng Excel bạn muốn sắp xếp, bôi đen cột tên.
Bước 2: Chọn tab Datavà chọnSort AZ.
Bước 3: Lúc này cửa sổ Sort hiện lên. Tại phần ColumnchọnTên, tại phần OrderchọnA to Z > click OK.
Bước 4: Sau khi hoàn thành các bước trên, bảng tên của bạn đã được sắp xếp theo thứ tự abc
Sắp xếp có điều kiện trong Excel
Sắp xếp có điều kiện trong Excel là một cách giúp bạn tối ưu hóa cách trình bày trang tính của mình. Từ đó, giúp việc sắp xếp công việc trên Excel được nhanh chóng, trực quan, có cái nhìn cụ thể, chi tiết hơn. Cùng theo dõi ví dụ bên dưới nhé.
Bước 1: Bôi đen toàn bộ trang tính. Chọn tab Data trên thanh công cụ rồi chọn Sort.
Bước 2: Khi hộp thoại Sort hiện lên.
- Add Level: bạn có thể sắp xếp và phân chia theo nhiều cấp độ
- Delete Level: Chọn các dữ liệu và xóa
- Sort by: Sắp xếp các dữ liệu theo chiều giảm dần
- Sort On: là các tiêu chí lựa chọn: màu sắc, font chữ,…
- Order: thứ tự sắp xếp tăng dần hay giảm dần
- My data has headers: sắp xếp dữ liệu ở cả phần tiêu đề.
Bước 3: Thêm Tên hàng và Màu ở mục Sort by. Sort sẽ sắp xếp: Nhà cung cấp → Tên hàng → Màu.
Bước 4: Sau khi thực hiện các bước trên bạn sẽ nhận được bảng có kết quả như sau:
Cách sắp xếp tên giống nhau trong Excel
Để sắp xếp tên giống nhau trong Excel bạn có thể tham khảo những bước sắp xếp tên theo bảng chữ cái abc.
Đầu tiên bạn bôi đen cột tên > Chọn tab Data> ChọnSort A→Z.
Thế là các bạn đã hoàn thành các bước sắp xếp.
Cách sắp xếp trong Excel theo ngày tháng
Bên cạnh việc sắp xếp tên trong Excel. Việc sắp xếp trong Excel theo ngày thánh cũng rất được quan tâm. Hãy theo dõi bảng tính ví dụ phía dưới để hiểu thêm về các bước làm nhé.
Bước 1: Mở file Excel và bôi đen toàn bộ bảng tính, rồi chọn thẻ Data.
Bước 2: Chọn mụcSort & Filter > Click vào Sort.
Bước 3: Khi hộp thoại Sort hiện lên. Chọn Ngày lĩnh lương ở phần Sort by.
Bước 4: Tại mục Sort On > chọn Values.TạiOrder > Chọn Oldest to Newest. Rồi bấm OK.
Sau khi hoàn thành các bước, bạn sẽ nhận được kết quả như thế này.
Lỗi sắp xếp trong Excel
Khi bạn thao tác sắp xếp trong Excel. Một lỗi mà Excel thường xuyên hay báo là: “Excel không thể hoàn thành nhiệm vụ này với các tài nguyên sẵn có. Chọn ít dữ liệu hoặc đóng các ứng dụng khác”.
Để giải quyết lỗi này, bạn có thể làm như sau:
Bước 1: Bấm vào Bắt đầu, click vàoChạy, nhập “regedit” vào hộp mở, rồi bấmOK.
Bước 2: Xác định vị trí, rồi chọn: HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options.
Bước 3: Trên menu chỉnh sửa, click vào Mới, rồi bấm vào giá trị DWORD [32 bit].
Bước 4: Nhập “EnableFullRowSort”, nhấn Enter.
Bước 5: Trong mục chi tiết, bấm chuột phải vàoEnableFullRowSort, rồi bấm Sửa đổi.
Bước 6: Trong hộp dữ liệu giá trị , nhập 1 > OK.
Bước 7: Thoát Registry Editor và thao tác sắp xếp trong Excel lại từ đầu.
Xem thêm:
- Cách tách tên trong Excel
- Xếp hạng trong Excel
- Khóa giá trị trong Excel
Như vậy, bài viết hôm nay đã giúp bạn tìm hiểu về những cách sắp xếp trong Excel. Nếu yêu thích bài viết hãy Like và Share bài viết để tiếp thêm động lực cho ThuthuatOffice cho ra thêm nhiều bài viết mới nhé.
Excel -Cách sử dụng các phép chia trong Excel
Cách cố định tiêu đề trong Excel dạng hàng, dạng cột và khi in bảng dữ liệu
2 cách chuyển số thành chữ trong Excel phù hợp với mọi phiên bản
3 bước cố định dòng trong Excel cực nhanh chóng
Cách copy công thức trong Excel đơn giản nhất
4 cách tạo Drop List trong Excel không hề khó khăn
Cách chọn vùng in trong Excel cực nhanh