Bạn đang xem bài viết Các Lỗi Thông Dụng Thường Gặp Trong Excel được cập nhật mới nhất tháng 12 năm 2023 trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.
Trong quá trình làm việc có khá nhiều đồng nghiệp hỏi mình về các lỗi của Excel. Ví dụ: #### trong excel là lỗi gì, value trong excel là lỗi gì, excel bị lỗi công thức, lỗi excel không chạy công thức, lỗi #n/a trong excel, enter không xuống dòng mà nhảy lên trên hoặc sang trái/ phải, hoặc nhập vào số nguyên nhưng kết quả lại ra số thập phân… Bài viết này mình sẽ chia sẻ tất cả các lỗi thường gặp trong Excel, phổ biến nhất mà ai cũng gặp phải ít nhất một lần. Ngoài ra còn có khoảng 10 lỗi công thức, hàm khi thực hiện tính toán nữa: #####, #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME?, #NULL!…
OK, LET’S GO!
A – Lỗi cài đặt Excel (12 lỗi thông dụng)Hầu hết các lỗi này đều xử lý bằng cách chỉnh sửa lại cài đặt trong Options của Excel: Vào Menu File Options:
1. Không tự động thực hiện phép tính khi ấn EnterLỗi này rất hay gặp phải, vì một lý do nào đó mà Excel tự động chuyển sang chế độ tính tay Manual. Tức là sẽ không tự động Automatic thực hiện phép tính khi bấm Enter nữa.
Không tự động thực hiện phép tính khi Enter
2. Enter không xuống dòng mà nhảy lên trên hoặc sang trái/ phảiVào Menu File Options Formulas Calculation options Workbook Calculation Tích chọn vào Automatic để cài đặt lại chế độ Automatic
Thông thường khi Enter kết thúc lệnh thì con trỏ được chuyển xuống ô ngay bên dưới, tuy nhiên nếu gặp phải trường hợp con trỏ nhảy lên trên thậm chí là sang trái sang phải thì bạn cũng đừng lo lắng, đây chỉ là do Excel đã bị cài đặt khác mặc định xuống dưới thôi.
Khắc phục: Vào Options Advanced Editing options trong phần Direction chọn lại Down:
3. Enter không di chuyển con trỏTương tự lỗi Enter con trỏ không xuống dòng, lần này thì con trỏ không chịu di chuyển, vẫn đứng im ô đang thao tác.
4. Mất chức năng Auto Fill (kéo copy công thức tự động)Khắc phục: Vào Options Advanced Editing options Tích chọn vào Enable fill handle and cell drag-and-drop:
Tích chọn để di chuyển con trỏ khi bấm Enter
5. Không sửa trực tiếp ô hiện hành bằng cách bấm đúp chuột đượcBình thường khi bấm đúp chuột hoặc bấm phím F2 thì chúng ta sẽ truy cập trực triếp vào ô hiện hành để chỉnh sửa được. Nhưng khi bị lỗi nó cứ trơ trơ ra không truy cập trực tiếp vào ô hiện hành được.
Khắc phục: Vào Options Advanced Editing options Tích chọn vào Alow editing direcly in cells:
Tích chọn Enable fill handle để thực hiện Auto Fill
6. Tự động chèn dấu thập phân khi nhập liệu (ví dụ nhập 150 nhưng kết quả là 1,5)Trường hợp này khá nhiều bạn không biết lý do tại sao và tra Google cũng ít thấy bài viết nên rất hoang mang nghi là Excel bị lỗi (crack mà ) rồi hì hục cài lại Excel 😆
Nhưng đây chỉ là cài đặt của Excel, lý do là Microsoft muốn có một công cụ nhập liệu số phần thập phân nhanh mà thôi.
Khắc phục: Vào Options Advanced Editing options Bỏ tích chọn Automatically insert a decimal point:
Tích chọn vào Alow editing direcly in cells
7. Không thấy địa chỉ ô (ví dụ B8/ C15) và tiêu đề cột ABC… mà chỉ thấy hiển thị RxCy trên thanh công thức, cột và hàng đều là tiêu đề sốĐây là cách hiện thị, truy vấn tham chiếu ô tính khác trong Excel. Cách hiện thị, tham chiếu này ít được sử dụng. Chúng ta hay dùng và quen thuộc với hệ cột ABC và hàng 123.
Khắc phục: Vào Options Formulas Working with formulas Bỏ tích chọn tại R1C1 reference style
8. Mất sheet tab (tên các sheet biến mất)Bình thường chúng ta có các bảng tính sheet1, sheet2, sheet3… (hoặc tên được thay đổi) và thao tác tính toán trên các sheet tab này. Excel có chức năng cho phép ẩn các sheet tab này đi đối với từng file (các file khác không ẩn thì vẫn hiển thijh bình thường).
Bỏ tích chọn Automatically insert a decimal point
Khắc phục: Vào Options Advanced Display options for this workbook Chọn File bị ẩn Tích chọn vào Show sheet tabs
9. Mất thanh cuộn ngang dọcThanh cuộn ngang dọc ở bên dưới cùng và bên phải bảng tính bị mất.
Khắc phục: Vào Options Advanced Display options for this workbook Tích chọn vào Show horizontal scroll bar và Show vertical scroll bar
Bỏ tích chọn tại R1C1 reference style
10, 11, 12. Mất thanh tiêu đề cột hàng, mất các đường Gridlines mờ, mất thanh công thức (thanh Function)Khắc phục: Vào menu View tích chọn vào Headings, Formula Bar và Gridlines
Chọn File bị ẩn Tích chọn vào Show sheet tabs
B – Lỗi công thức, hàm khi tính toán (10 lỗi) 13. Lỗi phím mũi tên – lỗi thường gặp trong Excel– Khi nhấn các phím mũi tên trên bàn phím thì lại cuộn màn hình chứ không nhảy sang ô bên trên, dưới, trái, phải. Nguyên nhân là do bạn đã bật phím Scroll Lock. – Khắc phục: Bạn hãy xem đèn của phím Scroll Lock có sáng không, nếu có thì bạn nhấn phím Scroll Lock để tắt nó đi. Lưu ý một số bàn phím thế hệ mới hoặc bàn phím Laptop tắt mở Scroll Lock phải bấm tổ hợp phím: Fn + Scroll Lock
14. #NULL! Lỗi dữ liệu rỗng 15. #### trong excel là lỗi gì? ##### Là lỗi thiếu độ rộng cột– Khi cột thiếu độ rộng. – Khi bạn nhập giá trị ngày tháng hoặc thời gian là số âm.Khắc phục: Dùng chuột kéo độ rộng cột ra cho phù hợp hoặc bấm đúp vào cột để chọn Autofix.
Vào menu View tích chọn vào Headings, Formula Bar và Gridlines
Lỗi thiếu độ rộng cột 16. Value trong excel là lỗi gì? #VALUE! Lỗi giá trịLỗi phím mũi tên do bật Scroll Lock
17. #DIV/0! Lỗi chia cho 0– Nhập vào công thức số chia là 0 hoặc chia cho ô trống. Ví dụ = 15/0
18. #NAME? Sai tên hàm – lỗi thường gặp trong Excel– Khi chúng ta dùng những hàm không thường trực trong Excel như EDATE, EOMONTH, NETWORKDAYS, WORKDAYS,… Khi đó cần phải vào menu Tools – Add-in. Đánh dấu vào tiện ích Analysis ToolPak. – Nhập sai tên hàm số. Trường hợp này xảy tra khi bạn dùng bộ gõ tiếng Việt ở chế độ Telex vô tình làm sai tên hàm như IF thành Ì, VLOOKUP thành VLÔKUP, hàm SUM thành SUMM hoặc bạn gõ bất kỳ một hàm nào không có trong Excel. – Dùng những ký tự không được phép trong công thức. – Nhập một chuỗi trong công thức mà không có đóng và mở dấu nháy kép ” “ – Không có dấu 2 chấm: trong dãy địa chỉ ô trong công thức.
#NULL! Lỗi dữ liệu rỗng
19. Lỗi #n/a trong excel: Lỗi tham chiếu dữ liệu– Giá trị trả về không tương thích từ các hàm dò tìm như VLOOKUP, HLOOKUP, LOOKUP hoặc MATCH. – Dùng hàm HLOOKUP, VLOOKUP, MATCH để trả về một giá trị trong bảng chưa được sắp xếp. – Không đồng nhất dữ liệu khi bạn sử dụng địa chỉ mảng trong Excel. – Quên một hoặc nhiều đối số trong các hàm tự tạo. – Dùng một hàm tự tạo không hợp lý. Hay xảy ra khi công thức tham chiếu đến một ô trong mảng nhưng ô đó lại không có giá trị trong mảng. Lỗi này thường xảy ra với các hàm tìm kiếm như VLOOKUP, HLOOKUP… Bạn chỉ cần xem kỹ lại công thức và chỉnh sửa ô mà hàm tham chiếu tới.
20. #REF! Sai vùng tham chiếu– Đây là lỗi tham chiếu ô, chẳng hạn bạn xóa dòng hay cột mà dòng cột đó được dùng trong công thức, khi đó sẽ bị báo lỗi #REF! – Hàm Vlookup tham chiếu ra ngoài vùng.
21. #NUM! Lỗi dữ liệu kiểu số#VALUE! Lỗi giá trị
– Dùng một đối số không phù hợp trong công thức sử dụng đối số là dữ liệu kiểu số. Ví dụ bạn đưa vào công thức số âm trong khi nó chỉ tính số dương. – Dùng hàm lặp đi lặp lại dẫn đến hàm không tìm được kết quả trả về. – Dùng một hàm trả về một số quá lớn hoặc quá nhỏ so với khả năng tính toán của Excel. – Chẳng hạn như phép tính =SQRT(-2) sẽ bị báo lỗi #NUM!, vì không có căn bậc 2 của số âm.
22. Lỗi công thức vòng trònSai tên hàm – lỗi thường gặp trong Excel
Tuy nhiên, trong Excel cũng có lựa chọn cài đặt cho phép Excel thực hiện phép tính vòng lặp, khi đó sẽ không báo lỗi nữa. Nếu muốn Excel thực hiện phép tính vòng lặp bạn vào File Options và tích vào nút, và chọn số lần thực hiện phép tính vòng lặp mong muốn, mặc định là 100. Kết quả sẽ cho chúng ta số liệu CỰC LỚN, vì nó đã thực hiện vòng lặp đến tận 100 lần!
C – Cách tìm và xử lý tất tần tật các lỗi trên chỉ trong một nốt nhạc: 1. Xử lý tất cả các lỗi cài đặt Excel về mặc định chỉ trong một nốt nhạc (12 lỗi):Chỉ cần cài đặt Add-ins này, chọn tab Utilities và bấm vào nút Settings Excel là toàn bộ 12 lỗi cài đặt trên được trả về mặc định. Việc cài đặt Add-ins này cực kỳ đơn giản, chỉ cần Download Add-ins về sau đó load vào Excel là xong (khoảng 15s).
#REF! Sai vùng tham chiếu
2. Tìm các lỗi công thức, hàm khi tính toán (10 lỗi)Trong bảng tính có nhiều số liệu, nhiều trang, file chúng ta lại có nhiều sheet như vậy thì việc phát hiện ra một lỗi tính toán khá mất thời gian. Hiện nay có 2 cách làm phổ biến sau:
Cách 1: Sử các hàm tìm lỗi có sẵn trong Excel kết hợp định dạng có điều kiện để tìm và tô màu ô lỗi. Thông dụng nhất là hàm ISERROR(). Hàm này sẽ trả về giá trị True nếu tìm thấy lỗi.
Thực hiện: Chọn vùng cần tìm lỗi, sau đó vào tab Home Conditonal Formatting New Rule:
Kết bài:Vậy là mình đã trình bày xong các lỗi Excel thông dụng và các cách để xử lý khắc phục lỗi. Bằng cách sử dụng XDTH Add-ins Excel chỉ trong một nốt nhạc chúng ta sẽ triệt tiêu các lỗi cài đặt Excel đưa về mặc định cũng như tìm thấy tất cả các lỗi công thức, hàm khi tính toán.
Bấm Settings Excel trong XDTH Add-ins Excel để cài đặt Excel về mặc định
Download XDTH Add-ins Excel 1.1 (Bổ sung thêm Format Shapes, bổ sung thêm một số Settings Excel)
Download XDTH Add-ins Excel 1.2 (Bổ sung thêm Trim Text và Group Qulity Control trong tab Utilities và một số Settings Excel về mặc định)
Còn nếu bạn quan tâm đến các khóa học thực hành chuyên môn xây dựng tại trung tâm, có thể xem thông tin các khóa học tại link bên dưới, liên hệ với trung tâm theo các cách TẠI ĐÂY.
Các hàm tìm lỗi trong Excel (IFEROR, IFNA, ISERR, ISERROR) Chọn vùng dữ liệu cần tìm lỗi Tab Home Conditonal Formatting New Rule Gõ lệnh = iserror(a14) sau đó chọn Format để chọn màu tô. Với a14 là ô bất kỳ trong vùng chọn, thường lấy ô đầu tiên cho dễ nhớ Sử dụng hàm ISERROR kết hợp định dạng có điều kiện để tìm các ô lỗi Tìm lỗi bằng XDTH Add-ins Excel chỉ trong một nốt nhạc! Download XDTH Add-ins Excel 1.0 Victor Vuong,
Các Lỗi Thường Gặp Ở Word
1. Khi gõ chữ tiếng Việt có làn sóng răng cưa màu đỏ dưới chữ:
– Tool/ Options/ Spelling & Grammar
– Hủy dấu trong ô Check Spelling as you type, chọn OK
2. Khi gõ chữ tiếng Việt có làn sóng răng cưa màu xanh dưới chữ:
– Tool/ Options/ Spelling & Grammar
– Hủy dấu trong ô Check Grammar as you type, chọn OK
3. Khi gõ thêm chữ vào thì chữ bên phải của từ đó bị mất
F Hủy bỏ chế độ ghi đè như sau:
– Tool/ Options/ Edit
– Hủy dấu trong ô Overtype mode, chọn OK
Có thể nháy vào OVR trên thah trạng thái
4. jkhkykj,uuipGõ chỉ số trên M 3
– Dùng tổ hợp phím Ctrl +Shift +: để nâng con trỏ lên trên và gõ số mũ
– Ấn tổ hợp phím Ctrl +Shift =: để đưa con trỏ về vị trí mặc định
5. Gõ chỉ số dưới H 2 O:
– Dùng tổ hợp phím Ctrl +: để nâng con trỏ lên trên và gõ số mũ
– Ấn tổ hợp phím Ctrl =: để đưa con trỏ về vị trí mặc định
6. Chữ văn bản màu trắng, nền màu xanh: lỗi do đâu
– Tool/ Options/ General
– Bỏ dấu ô Blue background, white text
– Ok
7. Tạo Header/ Footer trang chẵn khác trang lẻ
– Tạo tiêu đề trang lẻ
o View/ Header/Footer
o Gõ nội dung Header, ấn Enter
o Tương tự, tạo Footer
o Nháy vào nút công cụ Page Setup
o
o Chọn nhãn Layout
o Trong vùng Header/Footer, đánh dấu Different odd and even
o Chọn ok
– Tạo tiêu đề trang chẵn
o Tạo Header
o Tạo Footer
8. Tạo tiêu đề trong bảng cho các trang
– Bôi đen dòng tiêu đề
– Table/ Heading Row Repeat
9. Xóa bỏ tiêu đề
– Bôi đen dòng tiêu đề
– Table/ Bỏ dấu nháy ở Heading Row Repeat
10. Tạo đường nét gạch
– Gạch đậm: gõ 3 dấu ### và ấn Enter
11. Tạo địa chỉ trang web để truy cập từ word
– Tool/ Auto Correct/ Auto format as you type
– Đánh dấu vào thư mục Interner and Network paths with hyperlinks
– Ok
12. Theo dõi việc sửa văn bản
– Để theo dõi việc sửa văn bản làm như sau:
– Mở văn bản cần theo dõi
– Nháy vào thực đơn Tool/ Track changes/ Highlight Changes
– Đánh dấu mục Track changes while editing
– Ok
13. Cho copy nhưng không cho sửa
– Mở văn bản cần bảo vệ
– Chọn Tool/ Protect Document/ Comments
– Nhập mật khẩu vào ô Password (options)/ Ok
– Nhập lại Password 1 lần nữa/ ok
– Nhấn File/ Save để ghi lại văn bản đã được bảo vệ chỉ cho phép copy nhưng không cho phép sửa dữ liệu
14. Không cho copy và không cho sửa
– Mở văn bản cần bảo vệ
– Chọn Tool/ Protect Document/ Form
– Nhập mật khẩu vào ô Password (options)/ Ok
– Nhập lại Password 1 lần nữa/ ok
– Nhấn File/ Save để ghi lại văn bản đã được bảo vệ chỉ cho phép copy nhưng không cho phép sửa dữ liệu
15. Không cho xem văn bản
Nếu không muốn cho người khác xem văn bản của bạn hãy thiết lập chế độ bảo mật chống xem. Cách làm như sau:
– Mở văn bản cần bảo vệ
– Tool/ Options/ Security
– Gõ mật khẩu vào ô Password to Open/ Ok
– Gõ lại password 1 lẫn nữa/ Ok
– File/ Save để ghi các thiết lập
16. Tìm từ cần tìm trong văn bản
Nếu cần tìm từ nào đó trong văn bản nhiều trang bạn làm như sau:
– Mở văn bản
– Edit/ Find (Ctrl + F)
– Trong mục Find what: gõ từ cần tìm
– Muốn tìm tiếp, nháy vào Find Next vệt đen sẽ nhảy đến từ cần tìm
17. Tìm từ sai hàng loạt và thay bằng từ đúng
Giả sử bạn là người hay đánh sai từ Hà Nội (Hà Lội), những từ này nằm rải rác trong văn bản. Vậy muốn thay tất cả từ sai này thành từ đúng một cách nhanh nhất, thực hiện như sau:
– Mở văn bản cần sửa
– Edit/ Replace (Ctrl +H)
Nháy vào Replace All/ ok
18. Cách thiết lập để máy tính tự viết hoa sau dấu chấm
Tool/ Auto Correct
Chọn mục Capitalize first letter of sentences/ ok
Các Lỗi Trong Excel Thường Gặp Và Cách Sửa Lỗi Bạn Nên Biết
Bạn có gặp những lỗi này khi sử dụng Excel: #VALUE!, #NUM!, #NULL!… Xem nguyên nhân và cách sửa lỗi nhanh nhất, dễ nhất ở đây.
Cách sửa lỗi cực kỳ đơn giản, chỉ cần xem là làm được.
Các lỗi trong Excel – Nguyên nhân và cách sửa lỗi #VALUE! Lỗi giá trịBạn nhập vào công thức một chuỗi trong khi hàm yêu cầu một số hoặc một giá trị logic.
Đang nhập một hoặc chỉnh sửa các thành phần của công thức mà nhấn Enter.
Nhập quá nhiều tham số cho một toán tử hoặc một hàm trong khi chúng chỉ dùng một tham số. Ví dụ =LEFT(A2:A5)
#NAME! Sai tênBạn dùng những hàm không thường trực trong Excel như EDATE, EOMONTH, NETWORKDAYS, WORKDAYS,… Khi đó cần phải vào menu Tools – Add-in. Đánh dấu vào tiện ích Analysis ToolPak.
Nhập sai tên một hàm số. Trường hợp này xảy tra khi bạn dùng bộ gõ tiếng Việt ở chế độ Telex vô tình làm sai tên hàm như IF thành Ì, VLOOKUP thành VLOOKUP.
Dùng những ký tự không được phép trong công thức.
Nhập một chuỗi trong công thức mà không có đóng và mở dấu nháy đôi.””
Không có dấu 2 chấm : trong dãy địa chỉ ô trong công thức (Excel không nhận diện được tên trong công thức. Ví dụ như hàm =SM(BBB), ở hàm này sai cả tên công thức và tên mảng tham chiếu tới. Muốn khắc phục bạn phải sửa lại tên hàm cho đúng hoặc nhập đúng mảng cần tham chiếu.)
#N/A Lỗi dữ liệuGiá trị trả về không tương thích từ các hàm dò tìm như VLOOKUP, HLOOKUP, LOOKUP hoặc MATCH.
Dùng hàm HLOOKUP, VLOOKUP, MATCH để trả về một giá trị trong bảng chưa được sắp xếp.
Không đồng nhất dữ liệu khi bạn sử dụng địa chỉ mảng trong Excel.
Quên một hoặc nhiều đối số trong các hàm tự tạo.
Dùng một hàm tự tạo không hợp lý (Xảy ra khi công thức tham chiếu đến một ô trong mảng nhưng ô đó lại không có giá trị. Lỗi này thường xảy ra với các hàm tìm kiếm như VLOOKUP, HLOOKUP… Bạn chỉ cần xem kỹ lại công thức và chỉnh sửa ô mà hàm tham chiếu tới.)
#NUM! Lỗi dữ liệu kiểu sốDùng một đối số không phù hợp trong công thức sử dụng đối số là dữ liệu kiểu số. Ví dụ bạn đưa vào công thức số âm trong khi nó chỉ tính số dương.
Dùng hàm lặp đi lặp lại dẫn đến hàm không tìm được kết quả trả về.
Dùng một hàm trả về một số quá lớn hoặc quá nhỏ so với khả năng tính toán của Excel (Giá trị số trong công thức không hợp lệ, số quá lớn hoặc quá bé. Chẳng hạn như phép tính =99^999 sẽ bị báo lỗi #NUM!, bạn chỉ cần giảm bớt giá trị tính toán lại.)
Nguồn Sưu tầm
Các Lỗi Thường Gặp Khi Sử Dụng Hàm (Công Thức) Trong Excel Và Cách Khắc Phục
Khi sử dụng các hàm trong Excel việc phân tích bài toán và nhập đúng công thức cho ra kết quả chính xác là điều mà tất cả chúng ta hướng tới, tuy nhiên trong nhiều trường hợp chúng ta nhập đúng công thức nhưng lại không cho ra kết quả như mong muốn thì chúng ta làm thế nào?
Đặc biệt đối với các hàm dò tìm hay tham chiếu như hàm Hookup, hàm Vlookup, Index và Match,… việc xảy ra lỗi là rất thường xuyên do nhiều nguyên nhân, vậy làm sao để tìm ra nguyên nhân gây lỗi trong các công thức excel được nhập, trong bài viết này chúng ta cùng tìm hiểu một số lỗi thường gặp khi sử dụng các hàm trong Excel và cách khắc phục.
1. Lỗi độ rộng cột: #####* Trường hợp xảy ra : Khi bạn nhập giá trị mà khoảng rộng của cột không đủ để hiển thị.
2. Lỗi giá trị: #VALUE!* Trường hợp xảy ra:
Bạn nhập vào công thức một chuỗi trong khi hàm yêu cầu một số hoặc một giá trị logic.
Đang nhập một hoặc chỉnh sửa các thành phần của công thức mà nhấn Enter.
Nhập quá nhiều tham số cho một toán tử hoặc một hàm trong khi chúng chỉ dùng một tham số. Ví dụ =LEFT(A2:A5)
Xuất hiện khi trong công thức có các phép toán không cùng một kiểu dữ liệu. Chẳng hạn công thức =A1+A2, trong đó A1 có giá trị là 1, A2 là Tây Ninh.Vì 1 là một ký tự số học, còn Tây Ninh lại là một chuỗi mẫu tự nên không thực hiện được phép tính.
* Khắc phục: Để khắc phục, bạn phải chỉnh sửa cho giá trị của các phép toán có cùng một kiểu dữ liệu là ký tự số học hoặc chuỗi mẫu tự.
3. Lỗi dữ liệu: #N/A* Trường hợp xảy ra:
Giá trị trả về không tương thích từ các hàm dò tìm như VLOOKUP, HLOOKUP, LOOKUP hoặc MATCH.
Dùng hàm HLOOKUP, VLOOKUP, MATCH để trả về một giá trị trong bảng chưa được sắp xếp.
* Nguyên nhân:
Không đồng nhất dữ liệu khi bạn sử dụng địa chỉ mảng trong Excel.
Quên một hoặc nhiều đối số trong các hàm tự tạo.
Dùng một hàm tự tạo không hợp lý.
Xảy ra khi công thức tham chiếu đến một ô trong mảng nhưng ô đó lại không có giá trị. Lỗi này thường xảy ra với các hàm tìm kiếm như VLOOKUP, HLOOKUP…
* Khắc phục: Bạn chỉ cần xem kỹ lại công thức và chỉnh sửa ô mà hàm tham chiếu tới.
4. Sai vùng tham chiếu: #REF!* Trường hợp xảy ra: Xóa những ô đang được tham chiếu bởi công thức. Hoặc dán những giá trị được tạo ra từ công thức lên chính vùng tham chiếu của công thức đó.
* Nguyên nhân:
Đây là lỗi tham chiếu ô, chẳng hạn bạn xóa dòng hay cột mà dòng cột đó được dùng trong công thức, khi đó sẽ bị báo lỗi #REF!
Liên kết hoặc tham chiếu đến một ứng dụng không thể chạy được.
5. Lỗi chia cho 0: #DIV/0!* Trường hợp xảy ra: Nhập vào công thức số chia là 0. Ví dụ = MOD(10,0).
* Nguyên nhân: Số chia trong công thức là một tham chiếu đến ô trống.
* Khắc phục: không để số bị chia là số 0
6. Lỗi Sai tên: #NAME!* Trường hợp xảy ra:
Bạn dùng những hàm không thường trực trong Excel như EDATE, EOMONTH, NETWORKDAYS, WORKDAYS,… Khi đó cần phải vào menu Tools – Add-in. Đánh dấu vào tiện ích Analysis ToolPak.
Nhập sai tên một hàm. Trường hợp này xảy tra khi bạn dùng bộ gõ tiếng Việt ở chế độ Telex vô tình làm sai tên hàm như IF thành Ì, VLOOKUP thành VLÔKUP, hoặc bạn nhớ chưa chính xác và nhập sai tên hàm,…
Dùng những ký tự không được phép trong công thức.
Nhập một chuỗi trong công thức mà không có đóng và mở dấu nháy đôi.””
Không có dấu 2 chấm : trong dãy địa chỉ ô trong công thức.
Excel không nhận diện được tên trong công thức. Ví dụ như hàm =SUMI(UU), ở hàm này sai cả tên công thức và tên mảng tham chiếu tới.
* Khắc phục: Bạn phải sửa lại tên hàm cho đúng hoặc nhập đúng mảng cần tham chiếu.
7. Lỗi dữ liệu kiểu số: #NUM!* Trường hợp xảy ra:
Dùng một đối số không phù hợp trong công thức sử dụng đối số là dữ liệu kiểu số. Ví dụ bạn đưa vào công thức số âm trong khi nó chỉ tính số dương.
Dùng hàm lặp đi lặp lại dẫn đến hàm không tìm được kết quả trả về.
Dùng một hàm trả về một số quá lớn hoặc quá nhỏ so với khả năng tính toán của Excel.
Giá trị số trong công thức không hợp lệ, số quá lớn hoặc quá bé. Chẳng hạn như phép tính =99^999 sẽ bị báo lỗi #NUM!, bạn chỉ cần giảm bớt giá trị tính toán lại.
* Khắc phục: Sử dụng đối số, hàm, giá trị tính toán chuẩn xác.
8. Lỗi dữ liệu rỗng: #NULL!* Trường hợp xảy ra
Dùng một dãy toán tử không phù hợp
Dùng một mảng không có phân cách.
Lỗi này xảy ra khi sử dụng các phép toán tử như =SUM(A1:A5 B1:B5), giữa hai hàm này không có điểm giao nhau,
* Khắc phục: Sử dụng dãy toán tử phù hợp, dùng mảng có phân cách, bạn phải xác định chính xác điểm giao nhau giữa hai hàm.
Hàm Vlookup Trong Excel Và Các Ứng Dụng Nâng Cao Thường Gặp
Trong bài hướng dẫn về hàm VLOOKUP này, Blog Học Excel Online sẽ cố gắng dùng từ ngữ đơn giản để giải thích những điều cơ bản nhằm giúp quá trình học cho người mới bắt đầu trở nên dễ dàng nhất có thể. Chúng ta cũng sẽ khám phá vài công thức mẫu cho thấy cách dùng thông dụng nhất của hàm VLOOKUP trong Excel.
HÀM VLOOKUP TRONG EXCEL – BẢN MIÊU TẢ ĐƠN GIẢN VÀ CẤU TRÚCVậy hàm VLOOKUP là gì? Để bắt đầu, nó là một hàm trong Excel. 🙂 Nó dùng để làm gì? Nó tìm kiếm giá trị mà bạn định rõ và trả về giá trị tương ứng ở một cột khác. Nói một cách kỹ thuật hơn, hàm VLOOKUP tìm kiếm giá trị từ cột đầu tiên trong trong dải ô xác định, rồi trả về giá trị tương ứng trong cùng một hàng từ một ô khác.
Ở cách dùng thông dụng nhất, hàm VLOOKUP trong Excel tìm kiếm thông qua danh sách dữ liệu bạn tạo ra dựa trên ký hiệu nhận dạng đặc biệt rồi trả về cho bạn một mẩu thông tin tương ứng với ký hiệu nhận dạng đặc biệt đó.
Ký tự “V” trong hàm VLOOKUP là viết tắt của từ “vertical” (“thẳng đứng”). Ký tự đó được dùng để phân biệt hàm VLOOKUP và hàm HLOOKUP – đây là hàm tìm kiếm giá trị từ hàng trên cùng của mảng (H là viết tắt của từ “horizontal” (“nằm ngang”)).
Hàm VLOOKUP có sẵn trong tất cả phiên bản, gồm Excel 2013, Excel 2010, Excel 2007, Excel 2007, Excel XP và Excel 2000.
CÚ PHÁP HÀM VLOOKUP TRONG EXCELCú pháp hàm VLOOKUP trong Excel có dạng như sau:
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Như bạn thấy, hàm VLOOKUP trong Microsoft Excel có 4 thông số, hay câu lệnh. Ba thông số đầu tiên sẽ được trả về, thông số cuối cùng là tùy chọn.
lookup_value: giá trị tìm kiếm
Đây có thể hoặc là một giá trị (chữ số, ngày hay chuỗi ký tự) hoặc là một tham chiếu ô (tham chiếu ô chứa giá trị cần tìm), hoặc là một giá trị được trả về bởi một hàm Excel khác. Ví dụ:
Tìm kiếm chữ số: =VLOOKUP(40, A2:B15, 2) – công thức sẽ tìm kiếm số 40.
Tìm kiếm chuỗi ký tự: =VLLOKUP(“apples”, A2:B15, 2) – công thức sẽ tìm kiếm chữ “apples” (“táo”).
Tìm kiếm giá trị ở một ô khác: =VLOOKUP(C2, A2:B15, 2) – công thức sẽ tìm kiếm giá trị trong ô C2.
Lưu ý. Nếu giá trị trong hàm VLOOKUP là một chữ số và nó nhỏ hơn giá trị nhỏ nhất trong cột đầu tiên của mảng mà bạn đang tìm kiếm, thì hàm VLOOKUP sẽ trả về lỗi #N/A.
table_array: hai cột dữ liệu trở lên.
Hãy nhớ rằng, hàm VLOOKUP luôn luôn tìm kiếm giá trị từ cột đầu tiên của table_array. Table_array của bạn có thể chứa các giá trị khác nhau như chuỗi ký tự, ngày, chữ số, hay giá trị lôgic. Các giá trị không phân biệt dạng chữ, có nghĩa là ký tự viết hoa hay viết thường đều được xử lý như nhau.
Vì thế, công thức =VLOOKUP(40,A2:B15, 2) sẽ tìm kiếm số “40” từ ô A2 đến ô B15 bởi vì A là cột đầu tiên của table_array A2:B15. Hy vọng là mọi thứ đã trở nên dễ hiểu hơn. 🙂
col_index_num: số thứ tự cột trong table_array mà hàm VLOOKUP lấy giá trị ở hàng tương ứng để trả về.
Cột ở bên trái trong table_array xác định chính là 1, cột thứ hai là 2, cột thứ ba là 3, và vâng vâng.
Lưu ý: Nếu câu lệnh col_index_num của bạn nhỏ hơn 1, thì hàm VLOOKUP sẽ trả về lỗi #VALUE!. Trong trường hợp nó lớn hơn số cột trong table_array, thì hàm VLOOKUP sẽ trả về lỗi #REF!.
range_lookup: xác định liệu bạn đang tìm kiếm sự phù hợp tuyệt đối (FALSE) hay sự phù hợp tương đối (TRUE hay được bỏ qua). Thông số cuối cùng tuy là tùy chọn nhưng rất quan trọng. Ở dưới bài hướng dẫn về hàm VLOOKUP này, tôi sẽ cung cấp vài ví dụ nhằm giải thích cách làm công thức trở nên phù hợp tuyệt đối hay tương đối một cách chính xác.
VÍ DỤ HÀM VLOOKUP TRONG EXCELTôi hy vọng rằng bạn đã quen dần với hàm VLOOKUP. Bây giờ, hãy khám phá vài ví dụ sử dụng công thức VLOOKUP cho dữ liệu thực.
CÁCH DÙNG HÀM VLOOKUP TRONG EXCEL TỪ MỘT BẢNG TÍNH KHÁCTrong thực tiễn, công thức VLOOKUP rất hiếm khi được dùng để tìm kiếm dữ liệu trong cùng một bảng tính. Thông thường, bạn sẽ phải tìm kiếm và kéo dữ liệu tương ứng về từ bảng tính khác.
Để dùng hàm VLOOKUP từ bảng tính Excel khác, bạn nên nhập tên bảng tính và một dấu cảm thán trong câu lệnh table_array trước dải ô, ví dụ =VLOOKUP(40, Sheet2!A2:B15, 2). Công thức này chỉ ra rằng dải ô tìm kiếm A2:B15 nằm ở Sheet2.
Tất nhiên, bạn không cần phải nhập tên bảng tính một cách thủ công. Chỉ cần bắt đầu gõ công thức và khi gõ đến câu lệnh table_array, hãy đổi sang bảng tính cần tìm và dùng chuột để chọn dải ô.
Mẹo. Luôn luôn dùng tham chiếu ô tuyệt đối (có $) trong thông số table_array của công thức VLOOKUP là một ý tưởng tuyệt vời. Trong trường hợp này, dải ô cần tìm sẽ không thay đổi khi bạn sao chép công thức sang ô khác.
CÁCH DÙNG HÀM VLOOKUP TỪ MỘT SỔ LÀM VIỆC KHÁCĐể dùng hàm VLOOKUP giữa hai sổ làm việc Excel khác nhau, bạn nên đặt tên sổ làm việc trong dấu ngoặc vuông trước tên bảng tính.
Mở cả hai sổ làm việc. Bước này không bắt buộc, nhưng nó sẽ giúp việc lập công thức dễ dàng hơn bởi vì bạn sẽ không phải gõ tên sổ làm việc một cách thủ công. Bên cạnh đó, điều đó cũng sẽ bảo vệ công thức của bạn khỏi lỗi vô tình in sai.
Bắt đầu nhập công thức VLOOKUP, rồi đối với câu lệnh table_array, thì hãy đổi sang sổ làm việc khác rồi chọn dải ô cần tìm kiếm ở đó.
Đừng bỏ lỡ: Tài liệu tự học Excel 2003
CÁCH SỬ DỤNG TÊN CHO DẢI Ô HAY BẢNG TRONG CÔNG THỨC VLOOKUPGiả sử, nếu phải sử dụng cùng một dải ô cần tìm trong vài công thức VLOOKUP, thì bạn có thể tạo tên cho một dải ô rồi nhập trực tiếp tên của dải ô vào công thức thay vì phải nhập vùng cần tìm (câu lệnh table_array).
Để tạo tên cho dải ô, bạn chỉ cần chọn dải ô đó và gõ bất kỳ tên nào vào Name box (Hộp tên) – ở bên trái thanh Công thức.
=VLOOKUP(“Product 1”, Products, 2)
Hầu hết tên dải ô trong Excel đều được sử dụng cho toàn bộ sổ làm việc, nên bạn không cần định rõ tên của bảng tính trong câu lệnh table_array, dù cho dải ô bạn cần tìm nằm ở bảng tính khác. Nếu nó nằm ở sổ làm việc khác, thì bạn phải đặt tên sổ làm việc phía trước tên dải ô, ví dụ:
=VLOOKUP(“Product 1”, PriceList.xlsx!Products,2)
Những công thức như thế này này dễ hiểu hơn rất nhiều, đúng không? Bên cạnh đó, việc sử dụng tên cho dải ô có thể là một sự thay thế tuyệt vời cho tham chiếu ô tuyệt đối. Bởi vì tên dải ô không đổi khi sao chép công thức sang các ô khác, nên bạn có thể chắc rằng bạn sẽ luôn tìm đúng dải ô.
=VLOOKUP(“Product 1”, Table46[[Product]:[Price]],2)
hay thậm chí là có dạng như thế này: =VLOOKUP(“Product 1”, Table46,2)
VIỆC SỬ DỤNG KÝ TỰ ĐẠI DIỆN TRONG CÔNG THỨC VLOOKUPCũng như các công thức khác, bạn có thể sử dụng ký tự đại diện cho hàm VLOOKUP trong Excel:
Dấu chấm hỏi (?) để khớp với bất cứ ký tự riêng lẻ nào, và
Dấu sao (*) để khớp với bất kỳ dãy ký tự nào.
Việc sử dụng ký tự đại diện trong công thức VLOOKUP có thể rất hữu ích trong nhiều trường hợp:
Khi bạn không nhớ chính xác chuỗi ký tự bạn đang tìm kiếm.
Khi bạn muốn tìm một từ nào đó. Từ đó là một phần của nội dung ô. Hãy chú ý rằng hàm VLOOKUP tìm kiếm toàn bộ nội dung ô, như thể bạn đã chọn “Match the entire content” (“Khớp toàn bộ nội dung ô”) trong tương tác Tìm kiếm chuẩn của Excel.
Khi cột cần tìm có khoảng trống thụt đầu dòng hay cuối dòng. Nếu gặp trường hợp này, thì bạn có thể vắt óc cố tìm ra lý do tại sao công thức thông thường của bạn lại không được thực hiện.
VÍ DỤ 1. TÌM KIẾM CHUỖI KÝ TỰ BẮT ĐẦU HAY KẾT THÚC BẰNG CÁC KÝ TỰ NHẤT ĐỊNH=VLOOKUP(“*man”,$A$2:$C$11,1,FALSE) – tìm tên kết thúc bằng “man”.
=VLOOKUP(“ad*son”,$A$2:$C$11,1,FALSE) – tìm tên bắt đầu bằng “ad” và kết thúc bằng “son”.
=VLOOKUP(“?????”,$A$2:$C$11,1,FALSE) – tìm họ có năm ký tự.
Lưu ý. Để công thức VLOOKUP có ký tự đại diện chạy chính xác, bạn phải luôn thêm FALSE như một thông số cuối cùng. Nếu dải ô cần tìm của bạn có hơn một mục nhập khớp với tiêu chuẩn đại diện, thì giá trị tìm thấy đầu tiên sẽ được trả về.
VÍ DỤ 2. CÔNG THỨC VLOOKUP CÓ KÝ TỰ ĐẠI DIỆN DỰA TRÊN GIÁ TRỊ ÔĐiều này có thể được giải quyết bằng cách dùng hàm VLOOKUP có dạng như sau:
=VLOOKUP(“*”&C1&”*”, $A$2:$B$12, 2, FALSE)
Công thức này tìm kiếm giá trị ở ô C1 thông qua một dải ô xác định và trả về giá trị tương ứng ở cột B. Xin hãy chú ý rằng chúng ta sử dụng dấu và (&) trước và sau một tham chiếu ô trong thông số đầu tiên nhằm nối chuỗi ký tự.
Như bạn có thể thấy trong ảnh chụp màn hình bên dưới, hàm VLOOKUP của tôi trả về “Jeremy Hill” bởi vì mã bản quyền của anh ấy có một nhóm ký tự nằm trong ô C1:
VIỆC SỬ DỤNG CÔNG THỨC VLOOKUP CÓ GIÁ TRỊ TÌM KIẾM CHÍNH XÁC VÀ GIÁ TRỊ TÌM KIẾM TƯƠNG ĐỐIVà cuối cùng, hãy xem xét kỹ câu lệnh cuối cùng mà bạn nhập vào hàm VLOOKUP trong Excel – range_lookup. Vì đã được nói đến ở đầu bài hướng dẫn, câu lệnh này cực kỳ quan trọng bởi vì bạn có thể nhận được kết quả khác nhau tùy vào bạn nhập TRUE hay FALSE.
Đầu tiên, hãy xem “giá trị tìm kiếm chính xác” và “giá trị tìm kiếm tương đối” trong Microsoft Excel thật sự có nghĩa gì.
Nếu range_lookup được cài đặt là FALSE, thì công thức sẽ tìm chính xác giá trị, ví dụ, công thức sẽ tìm chính xác giá trị cần tìm khi bạn nhập giá trị đó làm thông số thứ nhất (lookup_value).
Nếu có hai hay nhiều hơn hai giá trị từ cột đầu tiên của table_array khớp với giá trị cần tìm, thì kết quả tìm thấy đầu tiên sẽ được trả về. Nếu không thể tìm thấy giá trị tìm kiếm chính xác, thì lỗi #N/A sẽ được trả về.
Ví dụ, nếu bạn sử dụng công thức =VLOOKUP(4, A2:B15,2,FALSE), nhưng dữ liệu của bạn không chứa giá trị 4 từ ô A2 đến cột A15, thì công thức sẽ trả về lỗi #N/A.
Nếu range_lookup được cài đặt thành TRUE hay được loại bỏ, thì công thức sẽ tìm sự phù hợp tương đối. Nói chính xác hơn, công thức VLOOKUP của bạn sẽ tìm kiếm sự phù hợp tuyệt đối đầu tiên và nếu sự phù hợp tuyệt đối không được tìm thấy, thì nó sẽ trả về sự phù hợp tương đối. Gía trị tìm kiếm tương đối là giá trị lớn nhất tiếp theo, chỉ nhỏ hơn lookup_value.
Lưu ý quan trọng! Nếu bạn nhập TRUE hay loại bỏ câu lệnh range_lookup, thì các giá trị ở cột đầu tiên của dải ô cần tìm phải được sắp xếp theo thứ tự tăng dần, đó là từ nhỏ nhất đến lớn nhất. Nếu không thì, hàm VLOOKUP trong Excel có thể sẽ không tìm được giá trị chính xác.
VÍ DỤ 1. CÁCH SỬ DỤNG HÀM VLOOKUP CÓ GIÁ TRỊ TÌM KIẾM CHÍNH XÁC TRONG EXCELCó thể bạn còn nhớ, để tìm kiếm sự phù hợp tuyệt đối, bạn phải đặt FALSE làm câu lệnh cuối cùng trong công thức VLOOKUP trong Excel.
Hãy chọn bảng “Animal speed” để làm ví dụ đầu tiên và tìm ra loài vật nào có thể chạy 80,5 km một giờ. Tôi tin chắc bạn sẽ chẳng gặp khó khăn gì với công thức này:
=VLOOKUP(50, $A$2:$B$15, 2, FALSE).
VÍ DỤ 2. SỬ DỤNG HÀM VLOOKUP CÓ GIÁ TRỊ CẦN TÌM TƯƠNG ĐỐI TRONG EXCELKhi sử dụng công thức VLOOKUP có giá trị tìm kiếm tương đối, đó là có range_lookup được cài đặt thành TRUE hay được bỏ đi, thì điều đầu tiên bạn cần làm đó là lọc cột đầu tiên trong dải ô cần tìm theo thứ tự tăng dần.
Điều này rất quan trọng bởi vì công thức VLOOKUP sẽ trả về giá trị lớn nhất tiếp theo cho giá trị cần tìm mà bạn đã định rõ rồi sẽ ngừng tìm kiếm. Nếu bạn không lọc dữ liệu một cách chính xác, thì cuối cùng bạn sẽ nhận được một kết quả lạ hay lỗi #N/A.
=VLOOKUP(69, $A$2:$B$15, 2, TRUE)
=VLOOKUP(69,$A$2:$B$15,2)
Như bạn có thể thấy, tôi đang cố tìm loài vật có tốc độ chạy gần 111 km một giờ nhất. Và đây là kết quả mà công thức VLOOKUP trả về:
Hy vọng rằng, những ví dụ này đã giúp việc sử dụng Excel dễ dàng hơn và hàm này không còn xa lạ với bạn nữa 🙂 Bây giờ, sẽ rất tuyệt để tổng kết những điều quan trọng mà bạn đã học nhằm giúp bạn nhớ các điểm mấu chốt tốt hơn.
HÀM VLOOKUP TRONG EXCEL – NHỮNG ĐIỀU CẦN NHỚ!Hàm VLOOKUP trong Excel không thể tìm ở bên trái. Nó luôn luôn tìm giá trị nằm trong cột cận trái của dải ô cần tìm (table_array).
Trong công thức VLOOKUP, tất cả giá trị đều không phân biệt dạng chữ, có nghĩa là ký tự viết hoa và viết thường đều được xử lý như nhau.
Nếu giá trị cần tìm nhỏ hơn giá trị nhỏ nhất trong cột đầu tiên của dải ô cần tìm, thì hàm VLOOKUP sẽ trả về lỗi #N/A.
Nếu câu lệnh thứ ba (col_index_num) nhỏ hơn 1, thì công thức VLOOKUP sẽ trả về lỗi #VALUE!. Trong trường hợp, nó lớn hơn số cột trong dải ô cần tìm (table_array), thì công thức sẽ trả về lỗi #REF!.
Hãy sử dụng tham chiếu ô tuyệt đối trong câu lệnh table_array của công thức VLOOKUP để có đúng dải ô cần tìm khi xử lý công thức. Hãy cân nhắc việc sử dụng tên cho dải ô hay cho bảng trong Excel như một phương án thay thế.
Khi tìm kiếm sự phù hợp tương đối (range_lookup được cải đặt thành TRUE hay loại bỏ), hãy luôn lọc dữ liệu ở cột đầu tiên trong dải ô cần tìm theo thứ tự tăng dần.
Và cuối cùng, hãy nhớ tầm quan trọng của thông số cuối cùng. Sử dụng TRUE hay FALSE khi thích hợp và bạn sẽ không phải đau đầu nữa.
Để có thể ứng dụng tốt Excel vào trong công việc, chúng ta không chỉ nắm vững được các hàm mà còn phải sử dụng tốt cả các công cụ của Excel. Những hàm nâng cao giúp áp dụng tốt vào công việc như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những công cụ thường sử dụng là Data validation, Conditional formatting, Pivot table…
Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học
Lỗi Hàm Sumif Trong Excel Thường Gặp Khi Sử Dụng Và Cách Sửa
Ngoài lỗi hàm SUMIF không hoạt động, trả về kết quả không chính xác là một trong những lỗi mà người dùng hàm Excel thường gặp phải. Trong quá trình sử dụng hàm để tính tổng giá trị, người dùng có thể sẽ gặp phải một số lỗi khác.
Hàm SUMIF trong Excel được sử dụng để tính tổng các giá trị dựa trên một số điều kiện cụ thể. Trong quá trình sử dụng hàm Excel này bạn có thể sẽ phải đối mặt với một số lỗi xảy ra.
Sửa lỗi hàm Sumif trong Excel thường gặp
Lỗi thường gặp khi dùng hàm SUMIF trong Excel1. Lỗi cú pháp hàm SUMIF Excel không hoạt động
Lỗi cú pháp hàm SUMIF Excel là lỗi phổ biến mà người dùng thường gặp phải, thậm chí là cả người dùng đã có nhiều kinh nghiệm cũng hay gặp phải lỗi này.
Về cơ bản, cấu trúc hàm SUMIF có dạng:
=SUMIF(condition_range,condition,sum range)
Trong đó:
– Tham số đầu tiên condition_range: Là vùng được chọn có chứa các ô điều kiện. Điều kiện của bạn sẽ chỉ được kiểm tra trong vùng này.– Tham số thứ 2, condition: Là điều kiện kiểm tra cho tham số condition_range.– sum range: Là phạm vi, vùng cần tính tổng.
=SUMIF(A2:A20,1-mar-13,C2:C20)
Tuy nhiên trong trường hợp này cú pháp hàm SUMIF Excel trên sẽ trả về giá trị là 0. Vậy nguyên nhân là do đâu?
Dữ liệu chúng ta đang làm việc được định dạng dưới dạng dữ liệu ngày tháng. Trong Excel, dữ liệu ngày tháng phải được biểu diễn dưới dạng chữ số.
=SUMIF(A2:A20,”1-mar-13″,C2:C20)
Hoặc:
=SUMIF(A2:A20,41334,C2:C20)
Lưu ý: trong trường hợp này tiêu chí là số nên chúng ta không cần sử dụng dấu ngoặc kép.
Một lưu ý quan trọng là nếu hàm Excel bao gồm dữ liệu ngày tháng, chúng ta cần kiểm tra định dạng dữ liệu đã chính xác chưa. Đôi khi nguyên nhân gây lỗi hàm SUMIF không hoạt động, không trả về giá trị chính xác là do lđịnh dạng dữ liệu không chính xác.
1.2. Lỗi khi sử dụng toán tử so sánh trong hàm SUMIF Excel
Cho ví dụ, ở đây yêu cầu đưa ra là tính tổng số lượng các ngày sau ngày 1-Mar-13, và cú pháp hàm chuẩn phải có dạng:
Lưu ý: Khi cần tính tổng giá trị, nếu một giá trị khớp trong phạm vi tiêu chí, chúng ta không cần sử dụng dấu “=”, chỉ cần viết giá trị hoặc cung cấp tham chiếu của giá trị đó làm tiêu chí.
1.3. Lỗi hàm SUMIF không hoạt động do định dạng dữ liệu sai
Hàm Excel SUMIF sử dụng để tính tổng các giá trị là số. Do đó nếu hàm không hoạt động đúng cách, trước hết chúng ta cần kiểm tra phạm vi tổng và định dạng phù hợp hay chưa.
Đôi khi chúng ta nhập dữ liệu từ các nguồn khác nhau có thể sử dụng các định dạng dữ liệu khác nhau, thậm chí dữ liệu số có thể được định dạng dưới dạng văn bản, và gây ra lỗi hàm SUMIF.
Trong trường hợp nếu giải pháp trên không khả dụng, một cách khác là sử dụng hàm VALUE để chuyển đổi chuỗi định dạng văn bản thành chuỗi định dạng số.
Sau khi hoàn tất, dán giá trị vào công thức hàm SUMIF.
Cú pháp hàm SUMIF tính tổng thời gian
Trong một số trường hợp sử dụng hàm SUMIF để tính tổng thời gian bạn có thể sẽ gặp phải một số lỗi.
=SUMIF(A2:A20,F3,C2:C20)
Tuy nhiên hàm trên sẽ trả về kết quả không chính xác. Điều này là bởi vì giá trị ngày và giờ, thời gian trong Excel được xử lý khác nhau.
Trong Excel, 1 giờ tương đương 1/24 đơn vị (unit). Vì vậy 12 giờ tương đương 0.5.
Để tính tổng thời gian, chúng ta sẽ phải thao tác thêm bước chuyển đổi định dạng ô G3 sang định dạng thời gian.
Kích chuột phải vào ô có giá trị cần định dạng, và chọn định dạng thời gian. Lúc này hàm SUMIF sẽ trả về kết quả chính xác.
1.4. Sử dụng hàm SUMPRODUCT nếu hàm SUMIF vẫn không hoạt động
Trong trường hợp nếu hàm SUMIF vẫn không hoạt động, chúng ta sẽ sử dụng hàm SUMPRODUCT để thay thế.
=SUMPRODUCT(–(A2:A20=F3),D2:D20)
Hoặc:
=SUMPRODUCT(–(F3=A2:A20),D2:D20) 2. Lỗi VALUE khi sử dụng hàm SUMIF Excel
Lỗi VALUE xảy ra là do công thức có chứa hàm SUMIF, COUNTIF hoặc COUNTBLANK tham chiếu đến các ô trong Workbook làm việc đã đóng. Để khắc phục lỗi VALUE này, chúng ta sẽ sử dụng kết hợp các hàm SUM và IF trong công thức mảng.
Công thức mảng là công thức có thể thực hiện nhiều phép tính trên một hoặc nhiều mục trong một mảng. Công thức mảng hoạt động trên 2 hoặc nhiều bộ dữ liệu được gọi là tham số mảng.
Ví dụ: trong ví dụ này yêu cầu là tính tổng doanh thu sản phẩm thủy sản. Kết quả sẽ được thêm trong Workbook báo cáo và dữ liệu nguồn nằm trong Workbook dữ liệu.
Logical_test : Data.xlsx!$A$23:$A$30=”Seafood”.Value_If _true: SUM(Data.xlsx!$D$23:$D$30).Value_if_false: 0.
Bước 8: Nhấn Ctrl + Shift + Enter để hoàn tất công thức mảng.Bước 9: Nếu trên màn hình hiển thị thông báo yêu cầu sửa công thức, clicl chọn Yes để tiếp tục.
Bằng cách sử dụng phương pháp này để tránh gặp phải các lỗi giá trị khi làm việc với Workbook không mở.
3. Lỗi chuỗi ký tự trong tiêu chí nhiều hơn 255 ký tự
Các hàm SUMIF và SUMIFS có thể trả về kết quả không chính xác nếu chuỗi ký tự dài hơn 255 ký tự.
Giải pháp để sửa lỗi trong trường hợp này là rút ngắn chuỗi ký tự. Cách đơn giản nhất để rút ngắn chuỗi ký tự là sử dụng hàm CONCATENATE hoặc toán tử (&) để chia nhỏ giá trị thành nhiều chuỗi.
Ví dụ:
Cập nhật thông tin chi tiết về Các Lỗi Thông Dụng Thường Gặp Trong Excel trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!