Tại sao công thức excel không làm nổi bật các ô?

Tôi có một bảng tính [bảng tính 1] với tất cả các địa chỉ trong quận của chúng tôi [khoảng 140.000]. Những địa chỉ đó có vĩ độ và kinh độ được gán cho chúng

Tôi có một bảng tính khác [bảng tính 2] khoảng 300 người đã đăng ký và cung cấp địa chỉ của họ để tham gia chương trình nhận tài liệu miễn phí tại nhà của họ. Tôi muốn khớp các địa chỉ đó với các địa chỉ trong bảng tính 1 một cách có điều kiện để tôi có thể xác định vị trí những người đã đăng ký trên bản đồ bằng kinh độ và vĩ độ của họ [phương pháp này chính xác hơn các phương pháp hiện có khác]

Sử dụng công thức sau, tôi có khoảng 80 trong số 300 địa chỉ trong bảng tính 2 khớp với các địa chỉ trong bảng tính 1 =COUNTIFS[Address, $O2]

[Địa chỉ trong công thức đó là Phạm vi được đặt tên trong bảng tính 2, mà tôi đã đọc cho phép tôi so sánh các cột từ hai bảng tính khác nhau và nó đã hoạt động. ]

Các trận đấu không thành công là do những người đăng ký không phải lúc nào cũng viết tên đường phố của họ giống như cách mà quận ghi trong hồ sơ của mình. Nhưng họ hầu như luôn điền đúng số đường và từ đầu tiên của tên đường, vì vậy tôi đoán việc khớp khoảng 12 ký tự đầu tiên sẽ thu được nhiều kết quả khớp hơn

Vì vậy, tôi muốn định dạng có điều kiện khớp 12 ký tự đầu tiên trong cột địa chỉ trên bảng tính 1 với 12 ký tự đầu tiên trong cột địa chỉ trên bảng tính 2

Tôi đã thử =COUNTIFS[LEFT[Address,12],LEFT[$O2,12]] và một số công thức khác, nhưng tất cả đều bị Excel từ chối. Trợ giúp có được công thức chính xác sẽ được đánh giá rất cao

Tôi có ít nhất hai ô mà các công thức không hoạt động trong một. tập tin xlsm. Các công thức nằm trong các ô nhưng màn hình trống và nằm trên các trang tính khác nhau. Trong cả hai trường hợp, các công thức liền kề, sự khác biệt duy nhất là số cột/hàng, đều hoạt động như dự kiến

Công thức đơn giản nhất trong hai công thức là =IF[MAX[Y14. Y17]>NOW[]-$B46,MAX[Y14. Y17],0]

Tính toán tự động được đặt, định dạng cho công thức bên dưới được đặt thành ngày [và số ở bên dưới]

Tôi đã cố gắng tìm lý do tại sao công thức sau đây không hoạt động trong vài ngày, khi hôm nay tôi nhận ra rằng công thức ngày ở trên cũng không hoạt động

=INDEX[Quốc gia2. G$4. G$9999,MATCH[INDIRECT["A"&ROW[]],Nation2. B$4. B$9999,0]]

Tôi biết rằng công thức đầu tiên đã hoạt động ngày hôm qua. Trong ba tháng qua, tôi đã lưu tệp này để sao lưu, khi tôi mở hai trong số các bản sao lưu, các công thức tương ứng trong hai ô đó không còn hoạt động chính xác nữa

Ở công thức thứ hai, ngày giá trị ô 'A' là '31/12/22' [ngày của Vương quốc Anh] và có một ngày '31/12/22' trong phạm vi 'B' và 'G' tương ứng . Tuy nhiên, nếu tôi thay đổi ngày 'A' và 'B' thành hầu như bất kỳ ngày nào khác, giá trị 'G' chính xác sẽ được hiển thị. Điều này đúng nếu các ngày là bất kỳ thanh cuối tháng nào vào ngày 22 tháng 12 [bao gồm cả ngày 21 tháng 12 và ngày 23 tháng 12] và khi thấy nó hoạt động với '15/12/22', tôi chỉ thay đổi phần tử '15' nhưng không có kết quả

Trả lời

Ainsley Latimer nói.
8 tháng 6 năm 2022 lúc 3. 58 giờ chiều

Tôi có một sổ làm việc với nhiều trang tính khác nhau. Trên một sheet [Danh sách dự án tổng thể] có 17 cột và 365 dòng thông tin. Tôi có nhiều trang khác đi qua danh sách các dự án này và tách chúng thành các trang chính xác [tôi. e. , kế hoạch công tác, dự án thuộc ngân sách, dự án vượt ngân sách, v.v. ]

Vấn đề tôi gặp phải là nếu tôi xóa một hàng khỏi trang Danh sách Dự án Chính, thì dữ liệu trên các trang khác đó sẽ biến mất [vì nó không thể tham chiếu các ô trong các hàng mà tôi đã xóa] và tôi phải sao chép thủ công và . [làm mới các tính toán không hoạt động]

CÔNG THỨC SỬ DỤNG TRÊN BẢNG KẾ HOẠCH CÔNG VIỆC [ví dụ].
Cột A. =IF['Danh sách dự án chính'. C2="Kế hoạch làm việc",TRUE,FALSE]
Cột B. =IFERROR[INDEX['Danh sách dự án chính'. 2 đô la Úc. A$674,NHỎ[NẾU[$A$2. $A$1160,ROW[$A$2. $A$1160]-ROW[$A$2]+1],ROWS[$1. 1]]],""]

Có cách nào khắc phục để khi tôi xóa một hàng trên Danh sách dự án chính, các ô có TRUE/FALSE có thể thay đổi ô tham chiếu của chúng thành hàng bên dưới nó [hoặc chỉ xóa chính chúng?

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]

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àm
• Khoảng trắng thừa gây ra kết quả không mong muốn
• Sử dụng tính toán thủ công thay vì tự động
• Xóa Hàng/Cột/Ô dẫn đến #REF. Lỗi
• Đặt dấu ngoặc đơn không chính xác [BODMAS]
• Sử dụng sai tham chiếu ô tuyệt đối/tương đối
• Tham chiếu sai tới tên Sheet/Workbook
• Tài liệu tham khảo thông tư
• Các ô được định dạng dưới dạng văn bản
• Văn bản tự động được chuyển đổi thành ngày
• Hàng/Cột ẩn có thể cho kết quả không mong muốn

Cú pháp sai của hàm

Hã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ốn

Cá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ụ bên dưới, 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 hàm 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

1. Làm sạch ô và xóa mọi khoảng trắng ở đầu/cuối trước khi sử dụng nó trong công thức
2. Sử dụng hàm TRIM trong công thức để đảm bảo mọi dấu cách ở đầu/cuối/kép đều bị bỏ qua

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ác khoảng trắng này cũng sẽ được đế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ự động

Mộ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, 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

1. Nhấp vào tab Công thức
2. Nhấp vào Tùy chọn tính toán
3. Chọn 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 cho 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ỗi

Một trong những điều có thể có tác động tàn phá đối với 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… 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= < > = Comparison10

Lấ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 đối

Khi 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/Workbook

Khi 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 'Bảng 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ản

Nế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

1. Thay đổi định dạng thành 'Chung' từ 'Văn bản' [nó nằm trong tab Trang chủ trong nhóm Số]
2. Chuyển đến ô có công thức, vào chế độ chỉnh sửa [sử dụng F2 hoặc nhấp đúp vào ô] và nhấn Enter

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ày

Excel 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

1. Chọn các ô/phạm vi mà bạn muốn thay đổi định dạng
2. Nhấp vào tab Trang chủ
3. Trong nhóm Số, nhấp vào trình đơn thả xuống Định dạng
4. Nhấp vào Văn bản

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 đang hoạt động tốt

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

• Cách khóa công thức trong Excel
• Cách sao chép và dán công thức trong Excel mà không thay đổi tham chiếu ô
• Hiển thị Công thức trong Excel Thay vì Giá trị
• Chuyển đổi văn bản thành số trong Excel
• Chuyển đổi ngày thành văn bản trong Excel
• Microsoft Excel không mở;
• Phím mũi tên không hoạt động trong Excel. Di chuyển trang thay vì ô
• 20 Hàm và Công thức Excel Nâng cao

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

E-mail

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Ó. ”

1. Tháng một

   Tháng Tám 2020 lúc 1. 22 giờ sáng

   Xin chào Sumit,
   Tôi nhận ra những vấn đề này và phải giải quyết vấn đề này rất nhiều khi nhận được tệp hoặc câu hỏi từ người khác. Trong hầu hết các trường hợp, tôi có thể tìm ra giải pháp.
   Nhưng tôi vẫn còn một vấn đề rất khó chịu, có lẽ bạn có giải pháp cho vấn đề này. Tôi thường phải sử dụng các tệp có mã EAN trong đó.
   Với việc sử dụng phông chữ mã vạch chính xác, tôi có thể làm cho các mã này có thể đọc được bằng máy quét.
   Nhưng mã EAN được tự động chuyển sang giá trị Hàm mũ. Thí dụ. 8714632069985 được hiển thị là 8,71463E+12 hoặc 8,715E+12 trong một cột nhỏ hơn.
   Tôi có thể thay đổi thuộc tính của ô thành giá trị mà không cần số thập phân hoặc văn bản nhưng tôi hy vọng có một cách để tôi không phải thay đổi thuộc tính của ô vì nhiều tệp trong số này được nhập từ tệp csv.

2. sonia

   Tháng Tám 2020 lúc 10. 20 giờ sáng

   Rất hữu ích và thú vị.
   Cảm ơn Hội nghị cấp cao.

3. Willard Stevenson

   Tháng Tám 2020 lúc 10. 37 giờ sáng

   Xin thưa,
   Về #REF của bạn. phần, tôi có hàng trăm tệp Excel do người khác gửi cho tôi có thể có lỗi #REF. lỗi do thao tác cắt/dán [mà tôi đã khuyên không nên làm] và người dùng không nhất thiết phải biết điều đó. Tôi biết tôi có thể tìm thấy chúng trong tệp đã mở, nhưng bạn có biết làm thế nào tôi có thể xác định các tệp "xấu" mà không cần mở từng tệp một không? . văn bản trong tệp excel.
   Cảm ơn bạn về những hướng dẫn tuyệt vời,
   Willie

4. Willard Stevenson

   Tháng Tám 2020 lúc 10. 21 giờ sáng

   Khi tôi định dạng một ô có định dạng tùy chỉnh ;;; . Tôi có một cột hộp kiểm mà tôi muốn lọc TRUE hoặc FALSE mà không nhìn thấy văn bản, chỉ các hộp kiểm. Làm cho màu văn bản khớp với màu nền hoạt động nhưng có vấn đề với các hàng sọc. Bất kỳ đề xuất?