Đảm bảo các đơn vị tiêu chuẩn trong quá trình nhập dữ liệu (Microsoft Excel)
Sharon có một trang tính mà cô ấy sử dụng để ghi lại khoảng cách. Nhiều người nhập dữ liệu vào bảng tính và một số nhập khoảng cách bằng dặm (chẳng hạn như 15 m) và những người khác nhập khoảng cách bằng km (ví dụ như 28 Km).
Sharon tự hỏi liệu có công thức nào cô ấy có thể sử dụng để chuyển bất cứ thứ gì được đặt trong ô thành km để tất cả các câu trả lời đều giống nhau không?
Có những công thức bạn có thể sử dụng để thực hiện chuyển đổi, nhưng trước khi thực hiện chúng, bạn nên xem xét cách bạn sắp xếp trang tính của mình lại với nhau. Từ quan điểm quản lý dữ liệu, không phải là một ý kiến hay nếu cho phép mọi người chỉ định các đơn vị trong cùng một ô với mục nhập giá trị. Vấn đề là bạn sẽ không bao giờ có thể mắc phải tất cả những lỗi người dùng có thể mắc phải và lỗi chính tả. Ví dụ, ai đó có thể nhập 15 dặm là một trong những điều sau đây: 15 dặm, 15 dặm, 15miles, 15mi, 15mile, 15 mi, 15 ms, 15ms, vv Vấn đề tương tự xảy ra với entry km.
Một ý tưởng hay là tách giá trị khỏi chỉ định đơn vị. Ví dụ, bạn có thể cho phép một người nào đó để đặt một giá trị vào ô B4 và sau đó trong ô C4 họ có thể nhập một vấn thiết kế là liệu giá trị đại diện cho dặm hoặc km. Bạn có thể sử dụng xác thực dữ liệu trên ô để giới hạn các lựa chọn được chấp nhận cho mục nhập của chúng. Kết quả là một phương pháp nhập dữ liệu “chống đạn” hơn.
Điều đó đang được nói, hãy xem xét một vài ý tưởng để thực hiện chuyển đổi dữ liệu của bạn. Excel cung cấp một hàm CONVERT thú vị cho phép bạn thực hiện chuyển đổi dữ liệu từ hệ thống đo lường này sang hệ thống đo lường khác. Công thức để chuyển đổi từ dặm sang km sẽ như sau:
=CONVERT(B4,"mi","km")
Nếu bạn muốn biết tất cả các đơn vị đo lường mà bạn có thể chỉ định bằng hàm CONVERT, hệ thống trợ giúp trực tuyến của Excel sẽ đáp ứng được nhiệm vụ.
Nếu bạn sử dụng ô C4 để chỉ định một đơn vị đo lường (“mi” hoặc “km”), thì công thức sau có thể được sử dụng để luôn đảm bảo rằng bất kỳ thứ gì được nhập vào ô B4 đều được biểu thị bằng km:
IF(C4="mi",CONVERT(B4,"mi","km"),B4)
Nếu bạn không thể tách các đơn vị đo lường thành một ô khác, thì bạn phải cố gắng thực hiện một số chuyển đổi dữ liệu trên những gì được nhập. Điều quan trọng cần nhớ là bất kỳ thứ gì được nhập vào đều được Excel coi là giá trị văn bản vì nó chứa các ký tự không phải số.
Hãy giả sử trong một giây rằng ai đó nhập vào ô B4 giá trị “5 mi” mà không có dấu ngoặc kép. Bạn cần sử dụng công thức để loại bỏ các ký tự không phải số
=IF(RIGHT(SUBSTITUTE(B4," ",""),2)="mi",CONVERT(VALUE( LEFT(B4,LEN(B4)-2)),"mi","km"),VALUE(LEFT(B4,LEN(B4)-2)))
Công thức xem xét giá trị trong ô B4 và kiểm tra cụ thể hai ký tự ngoài cùng bên phải, sau khi bất kỳ khoảng trắng nào trong ô đã bị xóa. Nếu hai ký tự đó là “mi” (tất nhiên không có dấu ngoặc kép) thì hàm CONVERT được sử dụng để thực hiện chuyển đổi trên phần số của ô. Nếu hai ký tự ngoài cùng bên phải là bất kỳ ký tự nào khác ngoài “mi” thì công thức chỉ trả về phần số của ô mà không có bất kỳ thay đổi nào.
ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.
Mẹo này (3593) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365. Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện menu cũ hơn của Excel tại đây: