Autor: David Ringstrom, CPA

Jakiś czas temu wyjaśniłem, jak korzystać z kreatora Tekst na kolumny w programie Excel do rozdzielania tekstu w arkuszu kalkulacyjnym na kolumny. Chociaż takie podejście jest pomocne w przypadku danych znajdujących się w arkuszu kalkulacyjnym, w innych przypadkach możesz chcieć połączyć arkusze kalkulacyjne z plikami tekstowymi, które zmieniają się okresowo. W tym artykule przedstawię etapy automatyzacji tego procesu.

Powszechnie stosowaną metodą dostępu do plików tekstowych jest okno dialogowe Otwórz. Jak pokazano na rysunku 1, można wybierać spośród różnych typów plików, w tym plików tekstowych. W zależności od typu pliku program Excel może automatycznie rozdzielić dane na kolumny, jak pokazano na rysunku 2, lub pozostawić wszystkie dane w jednej kolumnie, jak pokazano na rysunku 3. Dane, takie jak te pokazane na rysunku 3, można następnie ręcznie rozdzielić na kolumny za pomocą Kreatora zamiany tekstu na kolumny.

Rysunek 1: W oknie dialogowym Otwórz można wybrać jeden z różnych typów plików.

Rysunek2: Excel automatycznie rozdzielił dane na kolumny.

Rysunek 3 : Excel pozostawił wszystkie dane w jednej kolumnie.

Wielu użytkowników nie wie jednak, że istnieje alternatywny sposób dostępu do plików tekstowych, który może zminimalizować liczbę czynności, a nawet zautomatyzować integrację danych z plików tekstowych w arkuszach kalkulacyjnych. Aby to zrobić:

  • Excel 2007 i nowsze: Wybierz opcję Z tekstu w sekcji Uzyskaj dane zewnętrzne na karcie Dane.
  • Excel 2003 i wcześniejsze: Wybierz polecenie Dane, Importuj dane zewnętrzne, a następnie Importuj dane.

W tym momencie w każdej wersji programu Excel zostanie wyświetlona odmiana okna dialogowego Otwórz:

  • Excel 2007 i nowsze: Okno dialogowe jest opatrzone etykietą Importuj pliki tekstowe.
  • W programie Excel 2003 i wcześniejszych: Okno dialogowe jest opatrzone etykietą Wybierz źródło danych.

Niezależnie od wersji programu Excel należy przejść do pliku tekstowego, a następnie kliknąć przycisk Importuj. Pojawi się nowe okno dialogowe o nazwie Kreator importu tekstu. Kreator ten jest identyczny z Kreatorem importu tekstu do kolumn, ale dla osób nieobeznanych z jego działaniem przedstawię kolejne kroki:

Na pierwszym ekranie kreatora określasz, czy Twój plik tekstowy ma być delimitowany czy o stałej szerokości:

  • Delimitowany: Oznacza, że między poszczególnymi polami znajduje się separator, taki jak tabulator, przecinek, spacja, średnik lub znak |, który jest określany jako symbol rury.
  • Stała szerokość: oznacza, że każdemu polu przypisana jest określona liczba znaków, co oznacza, że dane są równomiernie rozmieszczone w kolumnach.

Jak pokazano na rysunku 4, w oknie podglądu danych wyświetlanych jest kilka pierwszych wierszy pliku tekstowego, co ułatwia określenie typu pliku. Po wybraniu typu delimitowanego lub o stałej szerokości kliknij przycisk Dalej, aby przejść do następnego kroku kreatora.

Rysunek 4: Excel udostępnia okno podglądu danych, w którym można zobaczyć kilka pierwszych wierszy pliku tekstowego.

Jeżeli wybierzesz opcję ograniczania, drugi krok kreatora będzie wyglądał tak, jak na rysunku 5. Możesz w nim wybrać jeden ze standardowych ograniczników lub wprowadzić dowolny ogranicznik, na przykład symbol rury w polu Inne. Pomimo mylącej szerokości pola Inny, można w nim umieścić tylko jeden znak.

Rysunek 5: Jeżeli w kroku 1 Kreatora importu tekstu wybrano opcję Delimitowany, w kroku 2 wybieramy ogranicznik.

Jeśli natomiast wybierzesz opcję stałej szerokości, drugi ekran kreatora będzie wyglądał tak, jak na rysunku 6. Excel będzie próbował automatycznie rozmieścić podziały między każdą kolumną. Możesz zmienić położenie tych podziałów kolumn, przeciągając je w lewo lub w prawo. Kliknij dwukrotnie na przerwę, aby ją usunąć, lub kliknij raz między dwa pola, aby wstawić przerwę między kolumnami.

Rysunek 6: Jeśli wybrałeś stałą szerokość w kroku 1 Kreatora importu tekstu, możesz ustawić szerokość pól w kroku 2.

Po określeniu separatora lub zatwierdzeniu podziału na kolumny kliknij przycisk Dalej, aby przejść do trzeciego kroku kreatora. Ten krok umożliwia zastosowanie różnych ustawień do kolumn w pliku tekstowym. Domyślnie każde pole jest oznaczone jako Ogólne, co oznacza, że zostanie zaimportowane do arkusza kalkulacyjnego bez określonego formatu. Oznacza to, że zera wiodące zostaną usunięte, a daty w formatach takich jak rrrr-mm-dd będą traktowane w arkuszu kalkulacyjnym jako tekst. Zamiast przyjmować domyślne ustawienie Ogólne, możesz wybrać jedną z trzech innych opcji:

  1. Tekst: Ta opcja nakazuje programowi Excel traktowanie kolumny jako tekstu, co oznacza, że w kolumnach zawierających wartości numeryczne, np. kody pocztowe, zostaną zachowane zera wiodące.
  2. Data: Ta opcja nakazuje programowi Excel przekonwertowanie kolumny na datę. Po wybraniu tej opcji należy określić format daty używany w pliku tekstowym, np. rrrr-mm-dd. Program Excel przekonwertuje te daty na standardowy format daty arkuszy kalkulacyjnych programu Excel.
  3. Nie importuj (pomiń): Ta opcja nakazuje programowi Excel usunięcie niepotrzebnych kolumn z pliku tekstowego. Oryginalny plik tekstowy pozostanie nienaruszony, ale kolumny, które zostaną oznaczone jako Nie importuj, nie pojawią się w arkuszu kalkulacyjnym.

Po zmianie wszystkich ustawień w kroku 3 kreatora i kliknięciu przycisku Zakończ pojawi się okno dialogowe pokazane na rysunku 7. Po potwierdzeniu lokalizacji, w której mają znajdować się dane z pliku tekstowego, kliknij przycisk Właściwości. Okno dialogowe pokazane na Rysunku 8 ujawnia ustawienia, których często używam do automatyzacji połączeń z plikami tekstowymi:

Rysunek 7: Okno dialogowe Importuj dane pojawia się po kliknięciu przycisku Zakończ w Kreatorze importu tekstu.

  • Prompt for File Name on Refresh: Wyłącz tę opcję, jeśli tworzysz stałe połączenie z konkretnym plikiem tekstowym, lub pozostaw ją włączoną, jeśli przy każdym odświeżaniu będziesz wprowadzał inny plik tekstowy.
  • Odświeżaj dane przy otwieraniu pliku: Ta opcja sprawia, że dane z pliku tekstowego są automatycznie importowane do arkusza kalkulacyjnego. Jest to istotna możliwość automatyzacji, ponieważ tworzy ona powiązanie z plikami tekstowymi typu "ustaw i zapomnij". Alternatywą jest pozostawienie tej opcji wyłączonej i ręczne odświeżanie danych, co opiszę za chwilę.
  • Wypełnianie formuł w kolumnach sąsiadujących z danymi: Jednym z moich ulubionych aspektów Excela jest możliwość, aby formuły, które umieszczam po prawej stronie danych z pliku tekstowego, były kopiowane do dodatkowych wierszy lub usuwane z niepotrzebnych wierszy przy każdym odświeżeniu danych z pliku tekstowego. Pozwala to na tworzenie połączeń typu "ustaw i zapomnij" z danymi pochodzącymi z pliku tekstowego.

Rysunek 8: Okno dialogowe Właściwości zewnętrznego zakresu danych pozwala zautomatyzować połączenia z plikami tekstowymi.

Kliknij dwukrotnie przycisk OK, aby zakończyć proces importowania. W przyszłości można ręcznie odświeżyć dane, klikając prawym przyciskiem myszy dowolną komórkę w obrębie bieżących danych i wybierając opcję Odśwież, jak pokazano na rysunku 9. Aby powrócić do okna dialogowego Właściwości pokazanego na Rysunku 8, kliknij prawym przyciskiem myszy na danych i wybierz polecenie Właściwości zakresu danych. Aby zastąpić bieżące dane danymi z innego pliku tekstowego, wybierz polecenie Edycja importu tekstu z menu po kliknięciu prawym przyciskiem myszy. Zostaniesz poproszony o ponowne przejście przez Kreator importu tekstu, jak opisano powyżej.

Rysunek 9: Kliknij prawym przyciskiem myszy komórkę i wybierz polecenie Odśwież, aby ręcznie odświeżyć dane.

Przeczytaj więcej artykułów Davida Ringstroma.

O autorze:

David H. Ringstrom, CPA kieruje firmą Accounting Advisors, Inc. z siedzibą w Atlancie, zajmującą się doradztwem w zakresie oprogramowania i baz danych, świadczącą usługi szkoleniowe i konsultingowe na terenie całego kraju. Skontaktuj się z Davidem pod adresem [email protected] lub śledź go na Twitterze. David jest prelegentem na konferencjach poświęconych Microsoft Excel, a także prowadzi webcasty dla kilku dostawców usług CPE, w tym dla partnera CPE Link w AccountingWEB.