Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

W tym samouczku omówiono sposób importowania i otwierania pliku programu Excel z wieloma arkuszami do jednej tabeli usługi LuckyTemplates.

Ten plik programu Excel z danymi zostanie użyty w tym samouczku.

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Plik zawiera 60 arkuszy danych o tym samym układzie.

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Celem tego samouczka jest załadowanie wszystkich arkuszy programu Excel do usługi LuckyTemplates i pobranie informacji o obligacjach w postaci jednej tabeli. Musisz zdobyć pasek bezpieczeństwa i dodać go w osobnej kolumnie. Następnie dołącz je wszystkie razem w jednej tabeli, która wykorzystuje interfejs użytkownika do generowania potrzebnego kodu.

Spis treści

Importowanie arkuszy Excela do LuckyTemplates

Pierwszą rzeczą do zrobienia jest otwarcie edytora Power Query i utworzenie parametru do przechowywania lokalizacji pliku za pomocą opcji Zarządzaj parametrami .

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

W polu Zarządzaj parametrami kliknij Nowy i wprowadź FileLocation jako nazwę parametru. Następnie ustaw Typ danych na Tekst , a Sugerowane wartości na Dowolna wartość . Skopiuj ścieżkę pliku programu Excel i wklej ją w polach Bieżąca wartość.

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Po naciśnięciu OK kliknij Nowe źródło i wybierz Skoroszyt programu Excel .

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Następnie wybierz i otwórz plik Excel. Po zakończeniu ekran Nawigatora otworzy się i wyświetli listę wszystkich arkuszy w pliku Excel.

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Wybierz pierwszy arkusz i naciśnij OK. Następnie zobaczysz tabelę w edytorze dodatku Power Query.

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Następną rzeczą do zrobienia jest zmiana zakodowanej ścieżki pliku na parametr pliku. Otwórz okno Edytora zaawansowanego i zmień ścieżkę pliku w kodzie źródłowym na FileLocation .

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Najlepiej przechowywać dane w tabelach programu Excel, ponieważ granice lub zakres danych są definiowane przed wprowadzeniem ich do dodatku Power Query . Ponieważ plik zawiera arkusze i nie ma tabel, istnieje ryzyko wprowadzenia pustych kolumn i pustych wierszy, więc musisz to uporządkować.

Wydobywanie paska zabezpieczeń z programu Excel do usługi LuckyTemplates

Świadomość, że układ arkuszy jest stały, pomaga w budowaniu rozwiązania, zwłaszcza jeśli chcesz zmaksymalizować i użyć interfejsu użytkownika do wygenerowania kodu. Na przykład jednym wymaganiem jest dodanie kolumny zawierającej znacznik bezpieczeństwa. Jeśli spojrzysz na dane, zobaczysz ticker.

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Interfejs użytkownika może pomóc w wyodrębnieniu wartości paska. Kliknij komórkę prawym przyciskiem myszy i wybierz opcję Dodaj jako nowe zapytanie .

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Na pasku formuły zobaczysz, że po nazwie tabeli występuje numer wiersza liczony od zera w nawiasach klamrowych. Nawiasy klamrowe nazywane są operatorami indeksu pozycyjnego . Zobaczysz również nazwę pola ujętą w nawiasy kwadratowe, które są nazywane operatorami dostępu do pól .

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Za pomocą tych kodów możesz teraz wyodrębnić wartość. Wróć do tabeli Bond i uzyskaj dane Bonda. Najpierw usuń osiem górnych ośmiu rzędów. Kliknij Usuń wiersze i wybierz Usuń górne wiersze .

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Następnie wprowadź 8 w polu Liczba wierszy i naciśnij OK.

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Następnie kliknij opcję Użyj pierwszego wiersza jako nagłówków, aby ustawić nagłówki.

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Po zakończeniu tabela obligacji będzie teraz wyglądać tak.

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Bezpośrednio pod nagłówkiem zobaczysz cienką linię reprezentującą jakość kolumny. Stamtąd widać, że w kolumnach jest sporo pustych miejsc. Oznacza to, że plik przyniósł wiele pustych wierszy.

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Usuwanie pustych wierszy

Aby usunąć puste wiersze, kliknij Usuń wiersze i wybierz Usuń puste wiersze .

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Ta transformacja generuje następującą składnię:

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Funkcja Record.FieldValues ​​pobiera wszystkie wartości z bieżącego wiersza w tabeli jako listę. List.RemoveMatchingItems usuwa wszystkie wartości z pierwszej listy, które mają dopasowanie na drugiej liście. Druga lista zawiera tylko pusty ciąg tekstowy lub wartość null. Są to wartości, które zostaną wykluczone z pierwszej listy.

Jeśli wszystkie puste ciągi tekstowe i wartość null zostały usunięte z listy z wartościami pola rekordu, lista powinna być pusta, a List.IsEmpty zostanie oceniona jako prawda. Następnie Table.SelectRows zachowa wartości Trues.

Nie powinieneś skończyć z tabelą zawierającą tylko puste wiersze. Dlatego słowo kluczowe not jest dodawane przed List.IsEmpty . To następnie zwraca tabelę zawierającą niepuste wiersze.

Oprócz pustych wierszy musisz także usunąć puste kolumny. Ale zanim to zrobisz, spójrz, co Power Query generuje po usunięciu kolumny. Wybierz czwartą kolumnę i kliknij Usuń kolumny .

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Po wykonaniu transformacji zobaczysz tę składnię na pasku formuły.

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Wywołuje funkcję Table.RemoveColumns , a następnie odwołuje się do poprzedniego kroku i przekazuje go w okienku Zastosowane kroki jako pierwszy argument. Transformacja dalej przekazuje listę zawierającą nazwy kolumn, które chcesz usunąć.

Duplikowanie zapytania

Teraz zduplikuj zapytanie i wybierz krok Promuj nagłówki w okienku Zastosowane kroki. Następnie kliknij ten krok prawym przyciskiem myszy i wybierz Usuń do końca .

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Pamiętaj, że możesz użyć operatora indeksu pozycyjnego, aby przekazać numer wiersza liczony od zera w zestawie nawiasów klamrowych. Dlatego zachowaj tylko pierwszy wiersz, wprowadzając 0 w dwóch nawiasach klamrowych na pasku formuły.

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Po zakończeniu tabela Bonda będzie wyglądać tak.

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Stamtąd ponownie użyj części logiki, która została utworzona w kroku Usunięto puste wiersze, aby przekształcić rekord w listę i usunąć wartość null. Wróć do zapytania Bond i wybierz krok Usuń puste wiersze. Następnie skopiuj ten kod M.

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Wróć do zduplikowanego zapytania i wklej kod na pasku formuły. Następnie ułóż kilka ciągów, aby formuła wyglądała tak.

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Utworzyłeś teraz listę z nazwami kolumn, które chcesz zachować. Zmień nazwę zapytania Nazwy kolumn .

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Następnie wróć do zapytania Bonda. Ponieważ utworzono kwerendę zawierającą wszystkie kolumny, które chcesz zachować, w funkcji Table.RemoveColumns musisz wyświetlić listę wszystkich kolumn, które chcesz wykluczyć .

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Zmień {Column4} z tą samą składnią, która została skopiowana z kroków Usunięto puste wiersze. Zmień także {“”, null} na ColumnNames .

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Następnie musisz przekazać listę z rzeczywistymi nazwami kolumn z tabeli Bond. Zmień Record.FieldValues(_) na Table.ColumnNames() . Wpisz # „Usunięto puste wiersze” w nawiasach, aby przekazać argument odwołania do tabeli.

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Przypisywanie typów danych do kolumn

Następną rzeczą do zrobienia jest przypisanie odpowiednich typów danych do kolumn. W kolumnie Data kliknij ikonę obok nagłówka i wybierz Data.

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Dla kolumn PX_LAST i YLD_YTM_MID wybierz typ danych Liczba dziesiętna.

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Za pomocą tych trzech zapytań utworzono wszystkie elementy składowe, których można użyć do zaprojektowania rozwiązania odnoszącego się do wszystkich arkuszy w pliku Excel . W tym celu należy zduplikować zapytanie Bonda i usunąć wszystkie kroki z wyjątkiem kroku Źródło w okienku Zastosowane kroki.

Dodawanie kolumny paska bezpieczeństwa z programu Excel do usługi LuckyTemplates

W kroku Źródło możesz zobaczyć wszystkie dane w pliku Excel. Zamiast tworzyć oddzielne zapytanie dla każdego arkusza, możesz użyć zapytania Bond i przekształcić zagnieżdżone tabele w kolumnie Dane.

Najpierw dodaj znacznik bezpieczeństwa. Jeśli klikniesz białe miejsce w komórce dowolnej tabeli, zobaczysz podgląd zawartości tej tabeli.

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Musisz utworzyć logikę za pomocą instrukcji , aby uzyskać znacznik bezpieczeństwa. Przejdź do zapytania Column2 i skopiuj adres paska zabezpieczeń z paska formuły.

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Następnie wróć do zapytania Bonda i dodaj niestandardową kolumnę.

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Nazwij kolumnę Security Ticker i wpisz następujący kod M.

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Formuła zawiera instrukcję JEŻELI mówiącą, że jeśli słowo Zabezpieczenia zostanie znalezione w Kolumnie 1, to poda wartość sąsiadującej z nią komórki z Kolumny 2. W przeciwnym razie zostanie podana wartość null.

Po naciśnięciu OK do tabeli zostanie dodana nowa kolumna ze znacznikami bezpieczeństwa.

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Kliknij rozwijany przycisk kolumny Security Ticker i odznacz wartość null, aby usunąć wszystkie wartości null w kolumnie.

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Następnie otrzymasz wszystkie informacje o Obligacjach ze wszystkich arkuszy. Wystarczy powtórzyć wykonane transformacje w zapytaniu Bond(2) i zastosować je do zagnieżdżonych tabel w kolumnie Dane.

Tworzenie niestandardowej kolumny dla zapytania Bond

Przejdź do zapytania Bond(2), otwórz okno Edytora zaawansowanego i skopiuj następujący kod:

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Następnie wróć do zapytania Bonda i utwórz kolejną niestandardową kolumnę. Ponieważ musisz zastosować wiele przekształceń w wielu krokach, musisz użyć instrukcji let . Więc wprowadź let i wklej kod z Edytora zaawansowanego.

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Następnie zmień Bond_Sheet na Dane , aby przekształcić tabelę w kolumnie Dane.

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Po naciśnięciu OK w tabeli zostanie dodana nowa kolumna.

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Jeśli klikniesz spację w dowolnej komórce tej kolumny, zobaczysz dane z zapytania Bond(2).

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Wszystko, czego potrzebujesz, to dwie ostatnie kolumny zapytania Bonda. Wybierz więc kolumny Security Ticker i Custom i kliknij Usuń inne kolumny na wstążce Usuń kolumny.

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Rozwiń kolumnę Niestandardowe i odznacz pole Użyj oryginalnej nazwy jako prefiksu.

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Po zakończeniu sprawdź poprawność tabeli. Pod tabelą wybierz opcję Profilowanie kolumn na podstawie całego zestawu danych .

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Po tej transformacji w kolumnie Dane wystąpi błąd.

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Sprawdź więc błąd, klikając kolumnę Dane i wybierając opcję Zachowaj błędy na wstążce Zachowaj wiersze.

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Jeśli klikniesz wartość błędu w kolumnie Dane, możesz przeczytać poniższy komunikat o błędzie:

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Aby rozwiązać problemy, najpierw usuń krok Zachowane błędy. Kliknij kolumnę Dane i wybierz Usuń błędy ze wstążki Usuń wiersze.

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Następnie ustaw opcję profilowania kolumn z powrotem na 1000 pierwszych wierszy. I to wszystko!

Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

Wniosek

To rozwiązanie oparte na interfejsie użytkownika może pomóc w dołączaniu wielu arkuszy z pliku programu Excel do usługi LuckyTemplates. Zamiast tworzyć 60 oddzielnych zapytań i wielokrotnie wykonywać wszystkie przekształcenia, to rozwiązanie może utworzyć pojedyncze zapytanie, które wykona wszystkie przekształcenia. Wykorzystaj i zmaksymalizuj to rozwiązanie, aby stworzyć doskonały raport z danymi .

Melisa

Leave a Comment

Dodawaj, usuwaj i zmieniaj nazwy kolumn w R za pomocą Dplyr

Dodawaj, usuwaj i zmieniaj nazwy kolumn w R za pomocą Dplyr

W tym samouczku omówimy pakiet dplyr, który umożliwia sortowanie, filtrowanie, dodawanie i zmianę nazw kolumn w języku R.

Funkcje zbierania w Microsoft Power Automate

Funkcje zbierania w Microsoft Power Automate

Odkryj różnorodne funkcje zbierania, które można wykorzystać w Power Automate. Zdobądź praktyczne informacje o funkcjach tablicowych i ich zastosowaniu.

Oceń wydajność kodu DAX w DAX Studio

Oceń wydajność kodu DAX w DAX Studio

Z tego samouczka dowiesz się, jak ocenić wydajność kodów DAX przy użyciu opcji Uruchom test porównawczy w DAX Studio.

Czym jest self w Pythonie: przykłady z życia wzięte

Czym jest self w Pythonie: przykłady z życia wzięte

Czym jest self w Pythonie: przykłady z życia wzięte

Jak zapisać i załadować plik RDS w R

Jak zapisać i załadować plik RDS w R

Dowiesz się, jak zapisywać i ładować obiekty z pliku .rds w R. Ten blog będzie również omawiał sposób importowania obiektów z R do LuckyTemplates.

Powrót do pierwszych N dni roboczych — rozwiązanie języka kodowania DAX

Powrót do pierwszych N dni roboczych — rozwiązanie języka kodowania DAX

Z tego samouczka języka kodowania DAX dowiesz się, jak używać funkcji GENERUJ i jak dynamicznie zmieniać tytuł miary.

Zaprezentuj spostrzeżenia przy użyciu wielowątkowej techniki dynamicznych wizualizacji w usłudze LuckyTemplates

Zaprezentuj spostrzeżenia przy użyciu wielowątkowej techniki dynamicznych wizualizacji w usłudze LuckyTemplates

W tym samouczku omówiono sposób korzystania z techniki wielowątkowych wizualizacji dynamicznych w celu tworzenia szczegółowych informacji na podstawie dynamicznych wizualizacji danych w raportach.

Wprowadzenie do filtrowania kontekstu w usłudze LuckyTemplates

Wprowadzenie do filtrowania kontekstu w usłudze LuckyTemplates

W tym artykule omówię kontekst filtra. Kontekst filtrowania to jeden z głównych tematów, z którym każdy użytkownik usługi LuckyTemplates powinien zapoznać się na początku.

Najlepsze wskazówki dotyczące korzystania z aplikacji w usłudze online LuckyTemplates

Najlepsze wskazówki dotyczące korzystania z aplikacji w usłudze online LuckyTemplates

Chcę pokazać, jak usługa online LuckyTemplates Apps może pomóc w zarządzaniu różnymi raportami i spostrzeżeniami generowanymi z różnych źródeł.

Analizuj zmiany marży zysku w godzinach nadliczbowych — Analizuj za pomocą LuckyTemplates i DAX

Analizuj zmiany marży zysku w godzinach nadliczbowych — Analizuj za pomocą LuckyTemplates i DAX

Dowiedz się, jak obliczyć zmiany marży zysku przy użyciu technik, takich jak rozgałęzianie miar i łączenie formuł języka DAX w usłudze LuckyTemplates.