Powstrzymaj program Excel przed konwersją danych do notacji naukowej podczas importowania danych z pliku CSV lub TXT.
Obejrzyj wideo
- Masz spacje, których TRIM nie usunie
- Masz numer części, który kończy się na e i cyfrę
- Masz numer części zawierający więcej niż 15 cyfr
- Jeśli importujesz jako plik CSV, numery części zmieniają się na notację naukową
- Jak wyświetlać rozszerzenia w Eksploratorze Windows
- Jeśli importujesz, otwierając plik .txt, możesz spróbować określić, że te kolumny są tekstowe, ale
- po znalezieniu / zastąpieniu nierozdzielającej spacji (znak 160) numery części zmieniają się na notację naukową
- Rozwiązaniem jest użycie danych, pobieranie danych zewnętrznych, z tekstu.
- Jednak tego polecenia brakuje w usłudze Office 365, które zostało zastąpione przez Pobierz & przekształcanie.
- Jeśli nie masz opcji Od tekstu, kliknij prawym przyciskiem myszy pasek narzędzi Szybki dostęp i Dostosuj
- W menu rozwijanym w lewym górnym rogu zmień na Wszystkie polecenia. Znajdź z tekstu (starsze) i dodaj do QAT
- Możesz otworzyć plik CSV za pomocą From Text, co pozwoli ci przejść przez kreator importu tekstu
- W kroku 2 kreatora określ przecinek i alt = "" + 0160 jako niestandardowe. Traktuj kolejne separatory jako jeden.
- Podziękowania dla Jana Karela: tutaj
- Nie zapomnij zagłosować: tutaj
Transkrypcja wideo
Naucz się programu Excel z podcastu, odcinek 2087: Zapobiegaj notacji naukowej podczas importu
Łał! Dzisiaj zajmiemy się wieloma różnymi pytaniami. Tak więc kilka osób zgłasza podobne problemy. Albo mamy numer części - Zobacz ten numer części tutaj, gdzie znajduje się przedostatnia cyfra. To wszystko jest numeryczne, ale przedostatnia cyfra to litera: D, E, F. Te E będą problemem. Te E pojawią się jako notacja naukowa lub jakikolwiek numer części, który jest całkowicie liczbowy dłuższy niż 15 cyfr, będzie miał problem.
Ponadto wiele osób pytało mnie o dane z tego systemu internetowego lub o ich pobieranie, a istnieją spacje przed i po tym, co nie pasuje do TRIM. Więc jeśli masz którykolwiek z tych trzech problemów, potencjalnie pomogę ci.
W porządku, pierwszą rzeczą, którą tutaj zrobimy, jest przyjrzenie się temu plikowi CSV, w porządku? Zamierzam tylko dwukrotnie kliknąć, aby to otworzyć; a ponieważ jest to plik CSV, nie zawracają sobie głowy przeprowadzaniem nas przez Kreatora importu tekstu, który jest okropny, prawda? Widzisz więc, że mamy pewne problemy. Po pierwsze, ze względu na E, wszystko, co ma literę E, przeszło do notacji naukowej. A jeśli spróbujemy to naprawić, wróć do numeru lub czegoś podobnego. Przegraliśmy. Straciliśmy rzeczy. To samo dotyczy rzeczy powyżej 16 znaków, nawet jeśli zmienisz je z powrotem na liczby, będziesz miał kłopoty, ponieważ straciłeś kilka ostatnich cyfr, w porządku. To po prostu okropne.
A to - Och, to jest świetne! To jest niesamowite. Spójrz na to, tak, przyszło bez konwersji. Och, ale są spacje wiodące i końcowe, nasze VLOOKUPS nie będą działać. W porządku, pierwszą rzeczą, którą chcemy zrobić, jest ustalenie, czym są te spacje początkowe i końcowe, ponieważ jeśli spróbuję = przyciąć, = przyciąć to coś, to nie zniknie. Jak mogę stwierdzić, że to nie odejdzie? Ponieważ mogę połączyć gwiazdkę = „” przed i - I widzisz, że wciąż coś tam jest, dobrze? A kiedy to się dzieje, wciąż coś tam jest. Wiesz, dlaczego TRIM nie tworzy - TRIM ma pozbyć się spacji wiodących i końcowych. Więc oto co robię. Jeśli chodzi o KOD = LEWEGO LEWEGO tego, 1, aby zobaczyć, co to jest, i jest to znak 160. Och, to nie jest to, co powinniśmy otrzymać.Tym razem zwykła stara przestrzeń, tylko przestrzeń. Naciśnij spację, to znak 32. To prawdziwa przestrzeń, której TRIM się pozbywa. Co to do cholery jest 160? 160 to niezłamująca przestrzeń. Jest to bardzo popularne w witrynach internetowych, ponieważ budując stronę internetową, umieszczasz przestrzeń kosmiczną. Cóż, Internet Explorer i Chrome sprawią, że będzie to jedna przestrzeń. Ale jeśli umieścisz nierozdzielające spacje, 3 z nich, to faktycznie zachowa 3 spacje.Ale jeśli umieścisz nierozdzielające spacje, 3 z nich, to faktycznie zachowa 3 spacje.Ale jeśli umieścisz nierozdzielające spacje, 3 z nich, to faktycznie zachowa 3 spacje.
W porządku, więc teraz jest frustrująca rzecz, którą musimy zrobić. Aby pozbyć się tych 160 spacji, musisz być w stanie wpisać znak 160, co oznacza, że musisz mieć klawiaturę numeryczną. W porządku, uważaj, kiedy to robię. Mam zamiar przytrzymać klawisz alt = "" i teraz za pomocą klawiatury numerycznej 0160 puścić i gotowe. Widzisz, po prostu pojawił się, w porządku? Teraz, jeśli masz szczęście, że masz klawiaturę numeryczną, cóż, ten problem będzie tutaj to Ctrl + H, w Znajdź, co przytrzymaj Alt + 0160, puść i Zastąp niczym, Zamień wszystko. Wszystko gotowe, dokonaliśmy 34 wymiany. Ale będę synem pistoletu, zmienili te liczby na notację naukową, w porządku. Więc udało mi się je wprowadzić, ale nadal mam szansę na przejście do notacji naukowej.
Nawiasem mówiąc, jeśli nie masz klawiatury numerycznej, możesz wpisać Alt + 0160, użycie liczb u góry nie zadziała. Zapomnij o tym, nigdy nie idź do pracy. Więc jeśli desperacko potrzebowałeś wpisać znak 160 = ZNAK (160), nie naciskaj Enter, naciśnij F9, co to obliczy. W porządku, więc teraz w tej komórce mam pojedynczą spację, ale to nie jest znak 32, to znak 160 i zamierzam przytrzymać klawisz Shift i nacisnąć klawisz strzałki w lewo, aby to zaznaczyć. Ctrl + C, teraz to jest w moim schowku. Teraz przyjdziemy tutaj. Wybierz te dwie kolumny, Ctrl + H, Znajdź co: wkleję tam Ctrl + V. Zamień na: Nic. Zamień wszystko, kliknij OK, kliknij Zamknij. I znowu, wzbijam się pod nosem, ponieważ wszystko to zamienili na notację naukową.
W porządku, to, co zwykle mówię ludziom, to co zwykle mówię ludziom, aby wrócili do Eksploratora Windows i przekonwertowali to z pliku CSV na plik .txt. Teraz tutaj, nie mogę tego zobaczyć. Jeśli nie - jeśli nie widzisz rozszerzeń, naciśnij klawisz alt = "", a następnie Narzędzia, a następnie Opcje folderów i tutaj, w obszarze Widok, gdzie jest napisane Ukryj rozszerzenia znanych typów plików, odznacz to. To najgorsze ustawienie w historii. Cały czas to wyłączam. Chcę zobaczyć rozszerzenie w ten sposób, mogę kliknąć prawym przyciskiem myszy, zmienić nazwę i zmienić na .txt. W porządku, jakie są korzyści z przejścia do pliku .txt? Hej, to jest niesamowite. Kiedy robię plik .txt, ponieważ wtedy, gdy przejdę do Plik i Otwórz, przejdziemy do tego folderu. Otwieram wersję .txt i klikam OK. W porządku, hej,Mogę przejść przez to i powiedzieć na każdym kroku, jaki to jest typ, więc mogę powiedzieć - Ach, przerwijmy to przecinkiem. Tak, piękna. I dalej, i właśnie tutaj powiem, nie pieprzyć tego. Tekst to sposób na powiedzenie, że nie pieprzyć tego. To samo tutaj, nie pieprzyć tego. Te, nie pieprzyć z nimi, tekst, tekst, tekst. Zwykle nie lubimy używać tekstu, ale tutaj, gdy zmieniają moje liczby, użycie tekstu pozwoli im wejść i nie będą one notacją naukową. Łał! To cudownie. I w ten sposób zawsze sugerowałem rozwiązanie tego problemu, ale potem zobaczyłem ten wspaniały artykuł mojego przyjaciela, Jana Karela z JKP Application Development Services, który pokazał mi genialny nowy sposób. Genialny nowy sposób. Więc pozwól, że ci to pokażę. JA'Link do tego artykułu umieść w komentarzu na YouTube. Koniecznie zapoznaj się z artykułem.
W porządku, więc wrócimy tutaj i piękną rzeczą jest to, że nie musimy zmieniać nazwy tego z tekstu - z CSV na tekst, ponieważ poradzi sobie z CSV, co jest naprawdę dobre, ponieważ jeśli otrzymujemy ten plik każdego dnia chcemy mieć możliwość radzenia sobie z CSV. Oto szalona rzecz. Jeśli korzystasz z programu Excel 2013 lub starszego, chcemy przejść do karty Dane, Pobierz dane zewnętrzne i użyj opcji Z tekstu. Ale jeśli korzystasz z Office 365, najnowszej wersji Office 365, ta sekcja zniknęła. W porządku, więc w Office 365 po kliknięciu prawym przyciskiem myszy tutaj i powiedz Dostosuj pasek narzędzi szybkiego dostępu, a po lewej wybierz Wszystkie polecenia.
To naprawdę długa lista, zejdziemy do „F.”. F jak From Text - spójrz na wszystkie te From Text, muszę znaleźć ten, który mówi From Text (Legacy). To stara wersja. Teraz widzą, chcą, abyśmy używali dodatku Power Query, ale po prostu stwórzmy coś, co będzie działać dla wszystkich. Teraz, gdy mam, teraz, gdy mam From Text Legacy, przejdę tutaj do zupełnie nowego arkusza roboczego, Wstaw arkusz roboczy. Teraz mamy miejsce na przejście z tekstu i przejdziemy do naszego pliku CSV. Kliknij Importuj, a powiemy Rozdzielany. Tak! Ale w kroku 2 powiem, że chcę go oddzielić przecinkiem. Chcę też rozgraniczać go w przestrzeni i chciałem go rozgraniczać w Alt + 0160. Teraz znowu, jeśli nie masz klawiatury numerycznej,Będę musiał użyć sztuczki, którą pokazałem ci kilka minut temu, aby móc to skopiować i wkleić do tej komórki. I och! Nawiasem mówiąc, jeśli masz wiele rzeczy obok siebie, nawet przecinek i Alt + 0160, potraktuj te kolejne separatory jako jeden. W porządku, ten tekst, właściwie to wszystko, będzie tekstem. Nie chcemy, żeby się z nimi pieprzyli. Wszyscy tak pozostają.
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.
Ta niesamowita sztuczka od mojego przyjaciela, Jana Karela, i nie zapomnij zagłosować na excel.uservoice.com. Hej, chcę ci podziękować za zatrzymanie się. Do zobaczenia następnym razem z kolejnym netcastem od.
Pobieranie pliku
Pobierz przykładowy plik tutaj: Podcast2087.xlsm
