Excel NẾU không làm việc với văn bản
|
Nếu bạn làm việc với các công thức trong Excel, sớm muộn gì bạn cũng sẽ gặp phải vấn đề công thức Excel hoàn toàn không hoạt động (hoặc cho kết quả sai) Show
Mặc dù sẽ thật tuyệt nếu chỉ có một vài lý do có thể khiến các công thức bị trục trặc. Thật không may, có quá nhiều thứ có thể sai (và thường là như vậy) Nhưng vì chúng ta đang sống trong một thế giới tuân theo nguyên tắc Pareto, nên nếu bạn kiểm tra một số vấn đề phổ biến, thì có khả năng giải quyết được 80% (hoặc thậm chí có thể là 90% hoặc 95% các vấn đề khiến công thức không hoạt động trong Excel) Và trong bài viết này, tôi sẽ nêu bật những vấn đề phổ biến có khả năng là nguyên nhân khiến công thức Excel của bạn không hoạt động Vậy hãy bắt đầu Hướng dẫn này bao gồm Cú pháp sai của hàmHãy để tôi bắt đầu bằng cách chỉ ra điều hiển nhiên Mọi hàm trong Excel đều có một cú pháp cụ thể – chẳng hạn như số lượng đối số mà hàm có thể nhận hoặc loại đối số mà hàm có thể chấp nhận Và trong nhiều trường hợp, lý do khiến các công thức Excel của bạn không hoạt động hoặc cho kết quả sai có thể là do đối số không chính xác (hoặc thiếu đối số) Ví dụ: hàm VLOOKUP nhận ba đối số bắt buộc và một đối số tùy chọn Nếu bạn cung cấp một đối số sai hoặc không chỉ định đối số tùy chọn (nơi cần thiết để công thức hoạt động), nó sẽ cho bạn một kết quả sai Ví dụ: giả sử bạn có một tập dữ liệu như hình bên dưới, nơi bạn cần biết điểm Mark trong Bài kiểm tra 2 (tại ô F2) Nếu tôi sử dụng công thức dưới đây, tôi sẽ nhận được kết quả sai, vì tôi đang sử dụng sai giá trị trong đối số thứ ba (đối số yêu cầu số Chỉ mục cột) =VLOOKUP(A2,A2:C6,2,FALSE) Trong trường hợp này, công thức tính toán (vì nó trả về một giá trị), nhưng kết quả không chính xác (thay vì điểm trong Bài kiểm tra 2, nó cho điểm trong Bài kiểm tra 1) Một ví dụ khác mà bạn cần thận trọng với các đối số là khi sử dụng hàm VLOOKUP với đối sánh gần đúng Vì bạn cần sử dụng một đối số tùy chọn để chỉ định nơi bạn muốn VLOOKUP khớp chính xác hoặc khớp gần đúng, nên việc không chỉ định đối số này (hoặc sử dụng sai đối số) có thể gây ra sự cố Dưới đây là một ví dụ tôi có dữ liệu điểm của một số học sinh và tôi muốn trích xuất điểm trong kỳ thi 1 cho các học sinh trong bảng bên phải Khi tôi sử dụng công thức VLOOKUP bên dưới, nó báo lỗi cho một số tên =VLOOKUP(E2,$A$2:$C$6,2) Điều này xảy ra vì tôi chưa chỉ định đối số cuối cùng (được sử dụng để xác định xem thực hiện đối sánh chính xác hay đối sánh gần đúng). Khi bạn không chỉ định đối số cuối cùng, nó sẽ tự động khớp gần đúng theo mặc định Vì chúng tôi cần thực hiện khớp chính xác trong trường hợp này, nên công thức sẽ trả về lỗi cho một số tên Mặc dù tôi đã lấy ví dụ về hàm VLOOKUP, nhưng trong trường hợp này, đây là thứ có thể áp dụng cho nhiều công thức Excel có cả các đối số tùy chọn. Also read: Identify Errors Using Excel Formula Debugging Khoảng trắng thừa gây ra kết quả không mong muốnCác khoảng trắng ở đầu, ở cuối rất khó phát hiện và có thể gây ra sự cố khi bạn sử dụng một ô có các dấu cách này trong công thức Ví dụ: trong ví dụ dưới đây, nếu tôi cố gắng sử dụng VLOOKUP để lấy điểm cho Mark, nó sẽ báo lỗi #N/A (lỗi không khả dụng) Mặc dù tôi có thể thấy rằng công thức là chính xác và tên 'Đánh dấu' rõ ràng có trong danh sách, nhưng điều tôi không thể thấy là có một khoảng trắng ở cuối ô có tên (trong ô D2) Excel không coi nội dung của hai ô này là giống nhau và do đó, nó coi nội dung đó là không khớp khi tìm nạp giá trị bằng VLOOKUP (hoặc có thể là bất kỳ công thức tra cứu nào khác) Để khắc phục sự cố này, bạn cần xóa các ký tự khoảng trắng thừa này Bạn có thể làm điều này bằng cách sử dụng bất kỳ phương pháp nào sau đây
Trong ví dụ trên, bạn có thể sử dụng công thức bên dưới để đảm bảo nó hoạt động =VLOOKUP(TRIM(D2),$A$2:$B$6,2,0) Trong khi tôi đã lấy ví dụ về VLOOKUP, đây cũng là một vấn đề phổ biến khi làm việc với các hàm TEXT Ví dụ mình dùng hàm LEN để đếm tổng số ký tự trong một ô, nếu có khoảng trắng ở đầu hoặc cuối thì cũng sẽ đếm và cho kết quả sai Lấy đi / Cách khắc phục. Nếu có thể, hãy làm sạch dữ liệu của bạn bằng cách xóa mọi khoảng trắng ở đầu/dấu hoặc dấu cách kép trước khi sử dụng những khoảng trắng này trong công thức. Nếu bạn không thể thay đổi dữ liệu ban đầu, hãy sử dụng hàm TRIM trong công thức để xử lý việc này Sử dụng tính toán thủ công thay vì tự độngMột cài đặt này có thể khiến bạn phát điên (nếu bạn không biết đó là nguyên nhân gây ra tất cả các sự cố) Excel có hai chế độ tính toán – Tự động và Thủ công Theo mặc định, chế độ tự động được bật, có nghĩa là trong trường hợp tôi sử dụng một công thức hoặc thực hiện bất kỳ thay đổi nào trong các công thức hiện có, nó sẽ tự động (và ngay lập tức) thực hiện phép tính và cho tôi kết quả Đây là cài đặt mà tất cả chúng ta đều quen thuộc Trong cài đặt tự động, bất cứ khi nào bạn thực hiện bất kỳ thay đổi nào trong trang tính (chẳng hạn như nhập công thức mới cho ngay cả một số văn bản trong một ô), Excel sẽ tự động tính toán lại mọi thứ (vâng, mọi thứ) Nhưng trong một số trường hợp, người ta kích hoạt cài đặt tính toán thủ công Điều này chủ yếu được thực hiện khi bạn có một tệp Excel nặng với nhiều dữ liệu và công thức. Trong những trường hợp như vậy, bạn có thể không muốn Excel tính toán lại mọi thứ khi bạn thực hiện các thay đổi nhỏ (vì có thể mất vài giây hoặc thậm chí vài phút) để quá trình tính toán lại này hoàn tất Nếu bạn bật tính toán thủ công, Excel sẽ không tính toán trừ khi bạn buộc nó phải Và điều này có thể khiến bạn nghĩ rằng công thức của bạn không tính được Tất cả những gì bạn cần làm trong trường hợp này là đặt phép tính trở lại tự động hoặc buộc tính toán lại bằng cách nhấn phím F9 Dưới đây là các bước để thay đổi phép tính từ thủ công sang tự động
Quan trọng. Trong trường hợp bạn đang thay đổi phép tính từ thủ công sang tự động, bạn nên tạo một bản sao lưu sổ làm việc của mình (đề phòng trường hợp điều này khiến sổ làm việc của bạn bị treo hoặc khiến Excel gặp sự cố) Lấy đi / Cách khắc phục. Nếu bạn nhận thấy rằng công thức của mình không cho kết quả như mong đợi, hãy thử một thao tác đơn giản trong bất kỳ ô nào (chẳng hạn như thêm 1 vào công thức hiện có. Sau khi bạn xác định vấn đề là vấn đề cần thay đổi chế độ tính toán, hãy thực hiện phép tính lực bằng cách sử dụng F9 Xóa Hàng/Cột/Ô dẫn đến #REF. LỗiMột trong những điều có thể ảnh hưởng nghiêm trọng đến các công thức hiện có của bạn trong Excel là khi bạn xóa bất kỳ hàng/cột nào đã được sử dụng trong các phép tính. Đôi khi, khi điều này xảy ra, Excel sẽ tự điều chỉnh tham chiếu và đảm bảo rằng các công thức đang hoạt động tốt Và đôi khi… nó không thể Rất may, một dấu hiệu rõ ràng mà bạn nhận được khi các công thức bị hỏng khi xóa các ô/hàng/cột là lỗi #REF. lỗi trong các ô. Đây là một lỗi tham chiếu cho bạn biết rằng có một số vấn đề với các tham chiếu trong công thức Hãy để tôi chỉ cho bạn ý của tôi bằng cách sử dụng một ví dụ Dưới đây tôi đã sử dụng công thức SUM để cộng các ô A2. A6 Bây giờ, nếu tôi xóa bất kỳ ô/hàng nào trong số này, công thức SUM sẽ trả về lỗi #REF. lỗi. Điều này xảy ra bởi vì khi tôi xóa hàng, công thức không biết phải tham chiếu gì bây giờ Bạn có thể thấy rằng đối số thứ ba trong công thức đã trở thành #REF. (trước đó đề cập đến ô mà chúng tôi đã xóa) Lấy đi / Cách khắc phục. Trước khi bạn xóa bất kỳ dữ liệu nào đang được sử dụng trong công thức, hãy đảm bảo không có lỗi sau khi xóa. Bạn cũng nên tạo bản sao lưu công việc của mình thường xuyên để đảm bảo rằng bạn luôn có thứ gì đó để dự phòng Đặt dấu ngoặc đơn không chính xác (BODMAS)Khi các công thức của bạn bắt đầu lớn hơn và phức tạp hơn, bạn nên sử dụng dấu ngoặc đơn để hoàn toàn rõ ràng phần nào thuộc về nhau Trong một số trường hợp, bạn có thể đặt dấu ngoặc đơn sai vị trí, dẫn đến kết quả sai hoặc lỗi Và trong một số trường hợp, bạn nên sử dụng dấu ngoặc đơn để đảm bảo công thức hiểu những gì cần được nhóm và tính toán trước Ví dụ: giả sử bạn có công thức sau =5+10*50 Trong công thức trên, kết quả là 505 vì Excel thực hiện phép nhân trước rồi mới cộng (vì có thứ tự ưu tiên khi nói đến các toán tử) Nếu bạn muốn nó thực hiện phép cộng trước và sau đó là phép nhân, bạn cần sử dụng công thức dưới đây =(5+10)*50 Trong một số trường hợp, thứ tự ưu tiên có thể phù hợp với bạn nhưng bạn vẫn nên sử dụng dấu ngoặc đơn để tránh nhầm lẫn Ngoài ra, nếu bạn quan tâm, bên dưới là thứ tự ưu tiên cho các toán tử khác nhau thường được sử dụng trong công thức OperatorDescriptionOrder of Precedence: (colon)Range1(single space)Intersection2, (comma)union3–Negation (as in –1)4%Percentage5^Exponentiation6* and /Multiplication & division7+ and –Addition & subtraction8&Concatnenation9= < > <= >= <>Comparison10Lấy đi / Cách khắc phục. Luôn sử dụng dấu ngoặc đơn để tránh bất kỳ sự nhầm lẫn nào, ngay cả khi bạn biết thứ tự ưu tiên và đang sử dụng nó một cách chính xác. Có dấu ngoặc đơn giúp kiểm tra công thức dễ dàng hơn Sử dụng sai tham chiếu ô tuyệt đối/tương đốiKhi bạn sao chép và dán công thức trong Excel, nó sẽ tự động điều chỉnh tham chiếu. Đôi khi, đây chính xác là điều bạn muốn (hầu hết khi bạn sao chép công thức xuống cột) và đôi khi bạn không muốn điều này xảy ra Tham chiếu tuyệt đối là khi bạn sửa tham chiếu ô (hoặc tham chiếu phạm vi) để nó không thay đổi khi bạn sao chép và dán công thức, còn tham chiếu tương đối là tham chiếu thay đổi Bạn có thể đọc thêm về tham chiếu tuyệt đối, tương đối và hỗn hợp tại đây Bạn có thể nhận được kết quả không chính xác trong trường hợp bạn quên thay đổi tham chiếu thành tham chiếu tuyệt đối (hoặc ngược lại). Đây là điều xảy ra khá thường xuyên với tôi khi tôi đang sử dụng các công thức tra cứu Để tôi chỉ cho bạn một ví dụ Dưới đây, tôi có một tập dữ liệu mà tôi muốn lấy điểm trong Bài kiểm tra 1 cho các tên trong cột E (trường hợp sử dụng VLOOKUP đơn giản) Dưới đây là công thức mà tôi sử dụng trong ô F2 và sau đó sao chép vào tất cả các ô bên dưới nó =VLOOKUP(E2,A2:B6,2,0) Như bạn có thể thấy rằng công thức này gây ra lỗi trong một số trường hợp Điều này xảy ra vì tôi chưa khóa đối số mảng bảng – đó là A2. B6 trong ô F2, trong khi đáng lẽ nó phải là $A$2. $B$6 Bằng cách đặt các ký hiệu đô la này trước số hàng và bảng chữ cái cột trong tham chiếu ô, tôi buộc Excel phải giữ cố định các tham chiếu ô này. Vì vậy, ngay cả khi tôi sao chép công thức này xuống, mảng bảng sẽ tiếp tục tham chiếu đến A2. B6 Mẹo chuyên nghiệp. Để chuyển đổi một tham chiếu tương đối thành một tham chiếu tuyệt đối, hãy chọn tham chiếu ô đó trong ô và nhấn phím F4. Bạn sẽ nhận thấy rằng nó thay đổi bằng cách thêm các ký hiệu đô la. Bạn có thể tiếp tục nhấn F4 cho đến khi nhận được tham chiếu mong muốn Tham chiếu sai tới tên Sheet/WorkbookKhi bạn tham chiếu đến các trang tính hoặc sổ làm việc khác trong một công thức, bạn cần tuân theo một định dạng cụ thể. Và trong trường hợp định dạng không chính xác, bạn sẽ gặp lỗi Ví dụ: nếu tôi muốn tham chiếu đến ô A1 trong Trang tính 2, tham chiếu sẽ là = Trang tính 2. A1 (nơi có dấu chấm than sau tên trang tính) Và trong trường hợp có nhiều từ trong tên trang tính (giả sử đó là Dữ liệu mẫu), tham chiếu sẽ là ='Dữ liệu mẫu'. A1 (trong đó tên được đặt trong dấu nháy đơn theo sau là dấu chấm than) Trong trường hợp bạn đang tham chiếu đến một sổ làm việc bên ngoài (giả sử bạn đang tham chiếu đến ô A1 trong 'Trang tính mẫu' trong sổ làm việc có tên 'Sổ làm việc mẫu'), tham chiếu sẽ như hình bên dưới ='[Example Workbook.xlsx]Example Sheet'!$A$1 Và trong trường hợp bạn đóng sổ làm việc, tham chiếu sẽ thay đổi để bao gồm toàn bộ đường dẫn của sổ làm việc (như hình bên dưới) ________số 8_______Trong trường hợp cuối cùng bạn thay đổi tên của sổ làm việc hoặc trang tính mà công thức đề cập đến, nó sẽ cung cấp cho bạn lỗi #REF. lỗi Also read: How to Find External Links and References in Excel Tài liệu tham khảo thông tưTham chiếu vòng là khi bạn tham chiếu (trực tiếp hoặc gián tiếp) đến cùng một ô mà công thức đang được tính toán Dưới đây là một ví dụ đơn giản, trong đó tôi sử dụng công thức SUM trong ô A4 trong khi sử dụng nó trong chính phép tính =VLOOKUP(E2,$A$2:$C$6,2)0 Mặc dù Excel hiển thị cho bạn lời nhắc cho bạn biết về tham chiếu vòng tròn, nhưng nó sẽ không làm điều đó cho mọi trường hợp. Và điều này có thể cho bạn kết quả sai (không có bất kỳ cảnh báo nào) Trong trường hợp bạn nghi ngờ tham chiếu vòng tròn đang chơi, bạn có thể kiểm tra xem ô nào có nó Để thực hiện việc này, hãy nhấp vào tab Công thức và trong nhóm 'Kiểm tra Công thức', nhấp vào biểu tượng thả xuống Kiểm tra Lỗi (mũi tên nhỏ hướng xuống dưới) Di con trỏ qua tùy chọn Tham chiếu vòng tròn và nó sẽ hiển thị cho bạn ô có vấn đề về tham chiếu vòng tròn Các ô được định dạng dưới dạng văn bảnNếu bạn thấy mình ở trong tình huống ngay khi bạn nhập công thức khi nhấn enter, bạn sẽ thấy công thức thay vì giá trị, thì đó là trường hợp rõ ràng ô được định dạng dưới dạng văn bản Khi một ô được định dạng là văn bản, nó coi công thức là một chuỗi văn bản và hiển thị nó như là Nó không bắt nó phải tính toán và đưa ra kết quả Và nó có một sửa chữa dễ dàng
Trong trường hợp các bước trên không giải quyết được vấn đề, một điều khác cần kiểm tra là liệu ô có dấu nháy đơn ở đầu hay không. Rất nhiều người thêm dấu nháy đơn để chuyển đổi công thức và số thành văn bản Nếu có dấu nháy đơn, bạn chỉ cần loại bỏ nó Văn bản tự động được chuyển đổi thành ngàyExcel có thói quen xấu là chuyển đổi trông giống như một ngày thành một ngày thực tế. Ví dụ bạn nhập 1/1 thì Excel sẽ chuyển thành 01-Jan của năm hiện tại Trong một số trường hợp, đây có thể chính xác là điều bạn muốn và trong một số trường hợp, điều này có thể chống lại bạn Và vì Excel lưu trữ các giá trị ngày và giờ dưới dạng số, nên ngay khi bạn nhập 1/1, nó sẽ chuyển đổi nó thành một số đại diện cho ngày 1 tháng 1 của năm hiện tại. Trong trường hợp của tôi, khi tôi làm điều này, nó sẽ chuyển đổi nó thành số 43831 (cho ngày 01-01-2020) Điều này có thể gây rối với công thức của bạn nếu bạn đang sử dụng các ô này làm đối số trong công thức Làm thế nào để khắc phục điều này? Một lần nữa, chúng tôi không muốn Excel tự động chọn định dạng cho chúng tôi, vì vậy chúng tôi cần chỉ định rõ ràng định dạng của mình Dưới đây là các bước chuyển định dạng sang văn bản để không tự động chuyển văn bản sang ngày tháng
Bây giờ, bất cứ khi nào bạn nhập bất cứ thứ gì vào các ô đã chọn, nó sẽ được coi là văn bản và không tự động thay đổi Ghi chú. Các bước trên sẽ chỉ hoạt động đối với dữ liệu được nhập sau khi định dạng đã được thay đổi. Nó sẽ không thay đổi bất kỳ văn bản nào đã được chuyển đổi thành ngày trước khi bạn thực hiện thay đổi định dạng này Một ví dụ khác mà điều này có thể thực sự khó chịu là khi bạn nhập một văn bản/số có các số 0 ở đầu. Excel tự động loại bỏ các số 0 đứng đầu này vì nó cho rằng chúng vô dụng. Ví dụ: nếu bạn nhập 0001 vào một ô, Excel sẽ thay đổi thành 1. Trong trường hợp bạn muốn giữ nguyên các số 0 đứng đầu này, hãy làm theo các bước trên Hàng/Cột ẩn có thể cho kết quả không mong muốnĐây không phải là trường hợp công thức cho bạn kết quả sai mà là sử dụng sai công thức Ví dụ: giả sử bạn có tập dữ liệu như hình bên dưới và tôi muốn lấy tổng của tất cả các ô hiển thị trong cột C Trong ô C12, tôi đã sử dụng hàm SUM để lấy tổng giá trị bán hàng cho tất cả các bản ghi đã cho này Càng xa càng tốt Bây giờ, tôi áp dụng bộ lọc cho cột mặt hàng để chỉ hiển thị các bản ghi cho Bán máy in Và đây là vấn đề – công thức trong ô C12 vẫn hiển thị cùng một kết quả – tôi. e. , tổng của tất cả các bản ghi Như tôi đã nói, công thức không đưa ra kết quả sai. Trên thực tế, hàm SUM vẫn hoạt động bình thường Vấn đề là chúng ta đã sử dụng sai công thức ở đây Hàm SUM không thể tính dữ liệu đã lọc và cung cấp cho bạn kết quả cho tất cả các ô (ẩn hoặc hiển thị). Nếu bạn chỉ muốn lấy tổng/số lượng/trung bình của các ô hiển thị, hãy sử dụng các hàm SUBTOTAL hoặc AGGREGATE Bài học rút ra chính – Hiểu cách sử dụng đúng và các giới hạn của một hàm trong Excel Đây là một số nguyên nhân phổ biến mà tôi đã thấy khi các công thức Excel của bạn có thể không hoạt động hoặc cho kết quả không mong muốn hoặc sai. Tôi chắc chắn rằng còn nhiều lý do nữa khiến công thức Excel không hoạt động hoặc không cập nhật Trong trường hợp bạn biết bất kỳ lý do nào khác (có thể là điều gì đó khiến bạn thường xuyên khó chịu), hãy cho tôi biết trong phần nhận xét Hy vọng bạn tìm thấy hướng dẫn này hữu ích Bạn cũng có thể thích các hướng dẫn Excel sau đây
SÁCH EXCEL MIỄN PHÍ Nhận Ebook 51 mẹo Excel để tăng năng suất và hoàn thành công việc nhanh hơn
Tên CÓ - GỬI SÁCH ĐIỆN TỬ CHO TÔI 5 suy nghĩ về "Công thức Excel không hoạt động. Lý do có thể và cách KHẮC PHỤC NÓ. ”
|
