Phần 2 giới thiệu tới các bạn các hàm xử lý số liệu
thường gặp
Hàm VLOOKUP: Dùng đển điền thông tin vào bảng dữ liệu lấy từ bảng phụ. Nó sẽ trả về giá trị của một ô năm trên một cột nào đó nếu thỏa mãn điều kiện dò tìm.
cấu trúc: VLOOKUP[lookup_value, table_array, row_index_num, range_lookup]
Lookup_value: giá trị dùng để tìm kiếm, có thể là một chuỗi ký tự, một tham chiếu, một giá trị nào đó của bảng chính.
Table_array: bảng dữ liệu[bảng
phụ] chứa dữ liệu cần tìn
Row_index_num: giá trị cần tìm nằm ở cột thứ mấy của bảng phụ
Range_lookup: Muốn tìm chính xác[số 0] hay tương đối[số 1]
Hãy điền tên vào cột Phương tiện dựa vào Mã hàng
Nhìn vào hình ta thấy tại ô D4 cú pháp như sau: =VLOOKUP[C4,$B$14:$C$18,2,0]
- C4: Giá trị dùng để tìm kiếm nằm ở ô C4
- $B$14:$C$18: Bảng chứa dữ liệu cần tìm kiếm[Bảng phụ], ở đây thêm dấu $ để cố định Bảng phụ, nếu không làm vậy thì khi Copy công thức vào các ô tiếp theo là D5, D6, ... Bảng phụ sẽ bị dịch chuyển.
- 2: Cột dữ liệu cần lấy thông tin ở Bảng phụ
- 0: Cần lấy giá trị tuyệt đối, Còn số 1 lấy giá trị tương đối.
Hàm
SUM: dùng để tính tổng các số trong vùng dữ liệu được chọn
Cấu trúc: Sum[Number1, Number2,....]
Trong đó Number1, Number2,...là các số cần tính tổng
Hàm SUMIF: dùng để tính tổng các số kèm theo điều kiện
Ví dụ: tính tổng tiền công cho công nhân có số giờ làm từ 5 giờ trở lên
Cấu trúc: SUMIF[range, criteria,[sum_range]]
-range: là số giờ làm của công nhân, vùng chọn từ B4:B10
-criteria: là điều kiện của số giờ làm lớn hơn và bằng 5
-sum_range:
là tổng tiền công của công nhân thỏa mãn có số giờ làm từ 5 giờ trở lên.
Nếu phần sum_range không có thì mặc định là tính tổng vùng range ở đây là B4:B10.
HÀM IF: là hàm trả về giá trị 1 nếu điều kiện đúng, giá trị 2 nếu điều kiện sai
Cấu trúc: IF[logical_test, [value_if_true], [value_if_false]]
Ví dụ: =IF[2 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 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: