Ngăn Excel chuyển đổi dữ liệu của bạn sang ký hiệu khoa học khi bạn nhập dữ liệu từ tệp CSV hoặc TXT.
Xem video
- Bạn có khoảng trắng mà TRIM sẽ không xóa
- Bạn có một phần số kết thúc bằng e và một chữ số
- Bạn có một phần số có hơn 15 chữ số
- Nếu bạn nhập dưới dạng tệp CSV, số bộ phận sẽ thay đổi thành Ký hiệu khoa học
- Cách hiển thị tiện ích mở rộng trong Windows Explorer
- Nếu bạn nhập bằng cách mở tệp .txt, bạn có thể cố gắng chỉ định các cột đó là văn bản, nhưng
- khi bạn tìm / thay thế khoảng trắng không ngắt (ký tự 160), số bộ phận sẽ thay đổi thành ký hiệu khoa học
- Giải pháp là sử dụng Dữ liệu, Lấy Dữ liệu Ngoài, Từ Văn bản.
- Tuy nhiên, lệnh này bị thiếu trong Office 365, đã được thay thế bằng Get & Transform.
- Nếu bạn không có Từ Văn bản, hãy bấm chuột phải vào Thanh công cụ Truy cập Nhanh và Tùy chỉnh
- Trong menu thả xuống trên cùng bên trái, thay đổi thành Tất cả lệnh. Tìm từ văn bản (cũ) và thêm vào QAT
- Bạn có thể mở tệp CSV bằng Từ Văn bản và nó sẽ cho phép bạn chuyển qua trình hướng dẫn nhập văn bản
- Trong bước 2 của trình hướng dẫn, chỉ định cả dấu phẩy và alt = "" + 0160 làm tùy chỉnh. Coi các dấu phân cách liên tiếp là một.
- Cảm ơn Jan Karel: tại đây
- Đừng quên bình chọn: tại đây
Bản ghi video
Học Excel từ Podcast, Tập 2087: Ngăn ký hiệu khoa học khi nhập
Chà! Hôm nay chúng ta sẽ xử lý rất nhiều câu hỏi khác nhau. Vì vậy, một số người gửi các vấn đề tương tự. Hoặc chúng ta có một số bộ phận - Xem số bộ phận này ngay tại đây, nơi chữ số thứ hai đến chữ số cuối cùng. Tất cả đều là số nhưng chữ số cuối cùng thứ hai là một chữ cái: D, E, F. Những chữ E đó sẽ là một vấn đề. Những chữ E đó sẽ xuất hiện dưới dạng ký hiệu khoa học hoặc bất kỳ bộ phận nào hoàn toàn là số dài hơn 15 chữ số sẽ có vấn đề.
Ngoài ra, rất nhiều người đã hỏi tôi về- hoặc lấy dữ liệu từ hệ thống dựa trên web này và có những khoảng trống trước và sau đó không tốt với TRIM. Vì vậy, nếu bạn gặp bất kỳ vấn đề nào trong ba vấn đề đó, rất có thể, tôi sẽ giúp bạn.
Được rồi, điều đầu tiên chúng ta sẽ làm ở đây là chúng ta sẽ xem xét cái này, tệp CSV, được chứ? Và tôi sẽ nhấp đúp để mở nó; và bởi vì đó là một tệp CSV, họ không bận tâm đưa chúng ta qua Trình hướng dẫn Nhập Văn bản, điều này thật kinh khủng, phải không? Và như vậy, bạn thấy rằng chúng tôi có một số vấn đề. Trước hết, vì chữ E, bất cứ thứ gì có chữ E, đều được ký hiệu khoa học. Và nếu chúng tôi muốn thử và sửa lỗi này, hãy quay lại một số hoặc một cái gì đó tương tự. Chúng tôi đã thua. Chúng tôi đã mất nhiều thứ. Điều tương tự với những thứ trên 16 ký tự, ngay cả khi bạn thay đổi những ký tự này trở lại số bạn sẽ gặp rắc rối vì bạn đã mất vài chữ số cuối cùng. Nó chỉ là kinh khủng.
Và điều này - Ồ, điều này thật tuyệt! Điều này thật tuyệt. Nhìn này, uh yeah, nó đến mà không cần chuyển đổi. Ồ, nhưng có khoảng trắng ở đầu và ở cuối, VLOOKUPS của chúng ta sẽ không hoạt động. Được rồi, điều đầu tiên chúng tôi muốn làm là tìm ra những khoảng trống ở đầu và cuối đó là gì vì nếu tôi cố gắng = trim, = xén thứ đó, nó sẽ không biến mất. Và làm thế nào tôi có thể biết nó không biến mất? Bởi vì tôi có thể nối dấu hoa thị = “” trước và - Và bạn thấy vẫn còn cái gì đó ở đó, được chứ? Và khi điều đó xảy ra, vẫn có một cái gì đó ở đó. Bạn biết tại sao TRIM không thành công - TRIM được cho là loại bỏ các khoảng trống ở đầu và cuối. Vì vậy, đây là những gì tôi làm. Đối với = CODE của LEFT của điều này, 1 để xem đó là gì, và nó là một ký tự 160. Ồ, đó không phải là những gì chúng ta nên nhận.Lần này là một không gian cũ thông thường ở đó, chỉ là một không gian. Nhấn phím cách, đó là một ký tự 32. Đó là không gian thực mà TRIM loại bỏ. 160 là cái quái gì? A 160 là một không gian không phá vỡ. Điều này thực sự phổ biến xung quanh các trang web bởi vì nếu bạn đang xây dựng một trang web, bạn sẽ đặt không gian không gian. Chà, Internet Explorer và Chrome sẽ biến đó thành một không gian duy nhất. Nhưng nếu bạn đặt các khoảng trắng không đứt quãng, 3 trong số đó thì thực sự sẽ giữ nguyên 3 khoảng trắng.Nhưng nếu bạn đặt các khoảng trắng không đứt quãng, 3 trong số đó thì thực sự sẽ giữ nguyên 3 khoảng trắng.Nhưng nếu bạn đặt các khoảng trắng không đứt quãng, 3 trong số đó thì thực sự sẽ giữ nguyên 3 khoảng trắng.
Được rồi, bây giờ, đây là điều khó chịu mà chúng tôi phải làm. Để loại bỏ 160 khoảng trắng đó, bạn phải có thể nhập ký tự 160, nghĩa là bạn phải có bàn phím số. Được rồi, hãy chú ý khi tôi làm việc này. Tôi sẽ nhấn giữ phím alt = "" và bây giờ với bàn phím số 0160, hãy để nó ra và ở đó. Thấy chưa, nó vừa xuất hiện, được chứ? Bây giờ, nếu bạn đủ may mắn rằng bạn có bàn phím số, thì sự cố ở đây sẽ là Ctrl + H, trong Find what nhấn giữ Alt + 0160, bỏ qua và Replace with nothing, Replace All. Tất cả đã xong, chúng tôi thực hiện 34 lần thay người. Nhưng tôi sẽ là con trai của một khẩu súng, họ đã đổi những con số đó thành ký hiệu khoa học, được rồi. Vì vậy, tôi đã có thể lấy chúng, nhưng tôi vẫn có cơ hội đổi sang ký hiệu khoa học.
Nhân tiện, nếu bạn không có bàn phím số để có thể nhập Alt + 0160, thì việc sử dụng các số trên cùng sẽ không hoạt động. Quên nó đi, không bao giờ đi làm. Vì vậy, nếu bạn thực sự cần nhập ký tự 160 = CHAR (160), đừng nhấn Enter, nhấn F9 sẽ đánh giá điều đó. Được rồi, bây giờ trong ô đó tôi có một khoảng trắng nhưng không phải là ký tự 32, mà là ký tự 160 và tôi sẽ giữ phím Shift và nhấn phím Mũi tên trái để chọn. Ctrl + C, hiện đã có trên khay nhớ tạm của tôi. Bây giờ, chúng ta sẽ đến đây. Chọn hai cột này, Ctrl + H, Tìm cái gì: Tôi sẽ dán vào đó Ctrl + V. Thay thế bằng: Không có gì. Thay thế Tất cả, bấm Ok, bấm Đóng. Và một lần nữa, tôi đang rất khó thở vì họ đã chuyển đổi tất cả những điều đó thành ký hiệu khoa học.
Được rồi, những gì tôi thường bảo mọi người làm, những gì tôi thường bảo mọi người làm là quay lại Windows Explorer và chuyển đổi từ tệp CSV sang tệp .txt. Bây giờ ở đây, tôi không thể tình cờ nhìn thấy nó. Nếu bạn không - nếu bạn không nhìn thấy các tiện ích mở rộng, hãy nhấn phím alt = "", sau đó nhấn vào Công cụ rồi đến Tùy chọn thư mục và ngay tại đây trong Chế độ xem, nơi có nội dung Ẩn tiện ích mở rộng cho các loại tệp đã biết, hãy bỏ chọn. Đó là thiết lập tồi tệ nhất từ trước đến nay. Tôi tắt nó mọi lúc. Tôi muốn xem tiện ích mở rộng theo cách đó, tôi có thể tự do nhấp chuột phải và Đổi tên và thay đổi nó thành .txt. Được rồi, lợi ích của việc truy cập .txt là gì? Ồ, thật tuyệt. Khi tôi tạo .txt, vì nếu tôi đi tới Tệp và Mở, và chúng tôi sẽ duyệt đến thư mục đó. Và tôi mở phiên bản .txt, nhấp vào Ok. Được rồi, này,Tôi phải xem qua và nói từng bước đó là kiểu gì và vì vậy tôi có thể nói - À, hãy chia nhỏ nó bằng Dấu phẩy. Đúng, đẹp. Và Tiếp theo, và ngay ở đây tôi sẽ nói, đừng quá lo lắng về điều này. Văn bản là cách để nói rằng đừng rắc rối với điều này. Điều tương tự ở đây, không rắc rối với điều này. Những thứ này, đừng làm phiền chúng, Văn bản, Văn bản, Văn bản. Và thông thường chúng ta không thích sử dụng Văn bản nhưng ở đây họ đang thay đổi số của tôi, sử dụng Văn bản sẽ cho phép những người đó đi vào và chúng sẽ không phải là ký hiệu khoa học. Chà! Thật tuyệt vời. Và đây là cách mà tôi luôn đề xuất để giải quyết vấn đề này nhưng sau đó tôi thấy bài viết tuyệt vời này của một người bạn của tôi, Jan Karel, Dịch vụ phát triển ứng dụng JKP, đã cho tôi thấy một cách mới tuyệt vời. Một cách mới tuyệt vời. Vì vậy, hãy để tôi cho bạn thấy điều này. TÔI'Sẽ đặt liên kết đến bài viết này ở dưới đó trong bình luận YouTube. Hãy chắc chắn để kiểm tra các bài báo.
Được rồi, vì vậy chúng tôi sẽ quay lại đây và điều tuyệt vời là chúng tôi không phải đổi tên tệp này từ văn bản - từ CSV thành văn bản vì nó sẽ xử lý CSV, điều này thực sự tốt vì nếu chúng tôi nhận được tệp này mỗi ngày chúng tôi muốn có thể đối phó với CSV. Bây giờ, đây là một điều điên rồ. Nếu bạn đang sử dụng Excel 2013 trở xuống, chúng tôi muốn chuyển đến tab Dữ liệu, Lấy Dữ liệu Ngoài và sử dụng Từ Văn bản. Nhưng nếu bạn đang sử dụng Office 365, phiên bản mới nhất của Office 365, thì phần đó sẽ biến mất. Được rồi, trên Office 365 sau khi nhấp chuột phải lên đây và nói Tùy chỉnh Thanh công cụ Truy nhập Nhanh và bên trái chọn Tất cả Lệnh.
Bây giờ, đây là một danh sách thực sự dài, chúng ta sẽ đi xuống chữ F. F là cho Từ Văn bản - hãy xem tất cả các Từ này, tôi cần tìm cái có nội dung Từ Văn bản (Kế thừa). Đó là phiên bản cũ. Bây giờ hãy xem, họ muốn chúng ta sử dụng Power Query nhưng chúng ta hãy tạo thứ gì đó phù hợp với mọi người. Bây giờ tôi đã có, bây giờ tôi đã có Từ Kế thừa Văn bản, tôi sẽ chỉ đến đây với Trang tính hoàn toàn mới, Chèn trang tính. Bây giờ chúng ta có một nơi để thực hiện điều này từ văn bản và chúng ta sẽ điều hướng đến tệp CSV của mình. Nhấp vào Nhập và chúng tôi sẽ nói Phân cách. Đúng! Nhưng ở bước 2, tôi sẽ nói rằng tôi muốn Phân tách nó ở Dấu phẩy. Tôi cũng muốn Delimit it ở Space và tôi muốn Delimit nó ở Alt + 0160. Bây giờ một lần nữa, nếu bạn không có bàn phím số, bạn 'bạn sẽ phải sử dụng thủ thuật mà tôi đã chỉ cho bạn vài phút trước để có thể sao chép và dán nó vào ô đó. Và ôi! Nhân tiện, nếu bạn nhận được nhiều thứ bên cạnh nhau, ngay cả dấu phẩy và tổ hợp phím Alt + 0160, thì hãy coi các dấu phân cách liên tiếp đó là một. Được rồi, Văn bản này, thực ra tất cả những thứ này, sẽ là Văn bản. Chúng tôi không muốn họ gặp rắc rối với bất kỳ thứ nào trong số đó. Tất cả đều giữ nguyên như vậy.
Now, here's the beautiful thing. First off, CSV files, will get the answer to these questions because we use From Text and we get to say where we're going to put it and Properties, that we want to Save the query definition. And then, every time we open this file maybe we can go out and refresh the data, so this workbook could be the holder that every time we open this, it's going to go back out to the CSV and remember all of our answers and do that- do all the steps. So click Close, click OK and nothing comes in and scientific notation it's all been changed to Text. And you know, we don’t have to worry about just double-clicking they’ll be CSV file because it's allowing us to specify what each of those fields are.
Alright, my book, Power Excel with, has a lot of different tips: 617 Excel mystery solved. Now this one, unfortunately, is not in the book but it'll be in the next edition of the book, I guarantee that.
A lot of different things we talked about today. If you have spaces that TRIM won’t remove, learn how to figure this out. If you have a part number that ends in E and then a single digit or you have a partner with more than 15 digits, when you import a CSV file all of those are going to change to scientific notation. And oh by the way, if you've been burned by this, here's another URL I'm going to paste down in YouTube videos. Come out here and vote to make the Excel team try and tell them Excel team that you want Excel to stop changing large numbers to scientific notation. 584 votes right now. Let's try and get that up to 600, 700, 800 or even a thousand.
Alright, so I showed you how to change the extensions in Windows Explorer. When you open a .txt file, yeah, sure the things coming as text but as soon as you try and get rid of those non-breaking spaces, the part numbers change back to scientific notation and you have to swear again. So we use Data, Get External Data From Text, the old legacy version. If missing from Office 365 replaced by Get & Transform, so you have to right-click the Quick Access Toolbar and Customize in order to find it. Now when we open a CSV file with From Text, it lets you go through the text import wizard which is better than just double-clicking the CSV file. And step 2, the wizard will specify a comma and a space and then Alt+0160 as custom. Treat consecutive delimiters as one.
Thủ thuật tuyệt vời này từ bạn của tôi, Jan Karel, và đừng quên bình chọn tại excel.uservoice.com. À này, tôi muốn cảm ơn bạn đã ghé qua. Chúng tôi sẽ gặp bạn lần sau cho một netcast khác từ.
Tải tập tin
Tải file mẫu tại đây: Podcast2087.xlsm
