Stylizuj dokumenty Excel i dodawaj hiperłącza za pomocą tabel nawigacyjnych

Ten artykuł wyjaśnia, jak formatować komórki i dodawać hiperłącza do dokumentów Excel, które tworzysz za pomocą tabel nawigacyjnych Power Query. Zanim zaczniesz, zapoznaj się z dokumentami Create Excel z tabelami nawigacyjnymi dotyczącymi schematu tabeli nawigacyjnej, typów części i zachowania pozycjonowania.

Stosowanie stylów

Dodaj Styles listę do rekordu części Properties. Każdy element zawiera Target, który identyfikuje komórki arkusza, oraz Style, który określa ich formatowanie. Możesz stosować style do elementów SheetData, Table, Range i Text, a także do bazowej tabeli danych elementu Chart.

Użyj tego kształtu dla każdego elementu na liście:

[Target = [Column = "Revenue"], Style = [NumberFormat = "$#,##0.00"]]

Nazwy pól rekordów M uwzględniają wielkość liter, więc zapisz Target, Style, Font, Color oraz inne pola dokładnie tak, jak pokazano. Możesz użyć udokumentowanych wartości opcyjnych, takich jak Center, Double, i Solid bez względu na przypadek, ale użyj udokumentowanej obudowy. W źródłach Power Query nazwy kolumn schematu w miejscach docelowych rekordów oraz mapowaniach hiperłączy uwzględniają wielkość liter.

Poniższy przykład formatuje nagłówek, dwie kolumny numeryczne, szerokości kolumn oraz wysokość wiersza nagłówka.

let
    Sales = #table(
        type table [Product = text, Revenue = number, Margin = number],
        {
            {"Northwind", 1200.5, 0.24},
            {"Contoso", 2750, 0.31}
        }
    ),
    ExcelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            {
                "Sales", "SalesData", "SheetData",
                [
                    Styles =
                    {
                        [Target = "A1:C1", Style = [Font = [Name = "Aptos", Size = 12, Family = 2, Bold = true, Color = "White"], Fill = "#305496", Alignment = [Horizontal = "Center"]]],
                        [Target = [Column = "Revenue"], Style = [NumberFormat = "$#,##0.00"]],
                        [Target = [Column = "Margin"], Style = [NumberFormat = "0.0%"]],
                        [Target = "A:A", Style = [Width = 24]],
                        [Target = "B:C", Style = [Width = 14]],
                        [Target = "1:1", Style = [RowHeight = 24]]
                    }
                ],
                Sales
            }
        }
    )
in
    ExcelDocument

Wybierz docelowy styl

Cele używają współrzędnych bezwzględnych arkusza roboczego. Ich adresy nie przesuwają się, gdy część zaczyna się gdzieś indziej niż komórka A1.

Wartość Row w obiekcie docelowym rekordu jest bezwzględnym numerem wiersza arkusza, numerowanym od 1. Wyjątkiem są pola prostokątne: Top, Left, Bottom oraz Right są indeksowane od zera i liczone włącznie.

Stylizuj komórki poza danymi części

Cel może zawierać puste komórki poza danymi części, na przykład aby dodać wypełnienie lub ramkę wokół obszaru raportu. Używaj elementu Table lub Range, który jest jawnie pozycjonowany, gdy potrzebujesz takiego działania. SheetData obiekty docelowe są ograniczone do jego obszaru danych. W przypadku części automatycznie pozycjonowanych jedynie dekoracje poniżej wszystkich danych arkusza są niezawodnie emitowane.

Każda komórka utworzona wyłącznie na potrzeby stylizacji wlicza się do limitów materializacji stylów. Preferuj cele całokolumnowe i całowierszowe dla dużych obszarów.

Odniesienie do stylu

Każdy element stylu jest opcjonalny. A Style to częściowa poprawka, a nie zamiennik pełnego formatowania komórki. Określ tylko pola, które chcesz zmienić. Pominięte elementy pozostają bez zmian. Zagnieżdżone rekordy też są częściowe. Na przykład [Font = [Bold = true]] zmienia pogrubienie bez konieczności podawania ani resetowania nazwy, rozmiaru lub koloru czcionki.

Krój pisma

Poniższy przykład określa czcionkę Aptos o rozmiarze 12 punktów i klasyfikuje ją jako rodzinę czcionek bezszeryfowych:

[Font = [Name = "Aptos", Size = 12, Family = 2, Bold = true, Color = "#1F4E79"]]

Name jest przechowywany w zeszycie; nie instaluje ani nie osadza czcionki. Wybierz czcionkę dostępną dla osób otwierających zeszyt. Excel może zastąpić inną czcionką, gdy żądana czcionka nie jest zainstalowana. FamilyPomaga Excel wybrać zamiennik i zwykle jest pomijany, chyba że musisz sklasyfikować wymaganą czcionkę. Na przykład użyj Family = 1 z Georgia, Family = 2 z Arial lub Family = 3 z Consolas.

Wypełnij

W przypadku wypełnienia jednolitego użyj koloru jako całego stylu, jako wartości Fill lub jako Fill[Color]:

[Target = "A1:C1", Style = "#FFF2CC"]
[Target = "A1:C1", Style = [Fill = "#FFF2CC"]]
[Target = "A1:C1", Style = [Fill = [Color = "#FFF2CC"]]]

Rekord wypełnienia może zawierać PatternType, ForegroundColor, oraz BackgroundColor. PatternTypeakceptuje nazwy wzorców Excel, w tym None, Solid, Gray125, Gray0625, DarkGray, MediumGray, LightGray, , oraz ciemne lub jasne warianty poziome, pionowe, diagonalne, siatkowe i kratowe.

Dla wypełnienia stałego jest ForegroundColor widocznym kolorem. BackgroundColor jest używany wyłącznie przez wypełnienia wzorzyste.

Granica

Ustaw Outline = true tak, aby stosować jeden styl na wszystkich czterech zewnętrznych krawędziach każdej komórki docelowej. Domyślny obrys jest cienki i czarny.

[Target = "B2:D5", Style = [Border = [Outline = true, Style = "Medium", Color = "Red"]]]

Dla różnych krawędzi używamy Left, Right, Top, Bottom, lub Diagonal. Krawędź może być nazwą stylu lub rekordem zawierającym Style i Color. Ustaw DiagonalUp lub DiagonalDown, aby wybrać kierunek ukośny.

Style obramowania to Dashed, Dotted, Double, DashDot, MediumDashDotDot, DashDotDot, MediumDashed, Hair, MediumDashDot, SlantDashDot, Thick, Medium, Thin oraz None.

Wyrównanie

Ochrona

Protection obsługuje pola logiczne Locked i Hidden. Te ustawienia obowiązują dopiero po zabezpieczeniu arkusza w Excel. Domyślny format komórek Excel jest ustawiony Locked na true, więc komórki, dla których nie określiłeś ochrony, zostają zablokowane po włączeniu ochrony arkusza. Użyj ustawienia Locked ustawionego na false w przypadku komórek, które powinny pozostać edytowalne.

[Target = "B2:B20", Style = [Protection = [Locked = false]]]

Ustaw Hidden na true, aby ukryć formułę komórki, gdy arkusz jest chroniony. Miejsce docelowe programu Excel zapisuje ustawienie ochrony komórek, ale nie włącza ochrony arkusza; włącz ochronę w programie Excel, aby ustawienia Locked lub Hidden zostały zastosowane.

Format liczb

NumberFormat akceptuje kod formatu liczbowego programu Excel. Zastępuje automatyczne formatowanie miejsca docelowego oparte na typie danych dla wskazanych komórek. Ustawienia czcionki, wypełnienia, ramki i wyrównania nadal obowiązują.

Strona obsługuje wbudowane formaty Excel oraz bezpieczne, niestandardowe formaty. Nie ogranicza się to tylko do przykładów z tej tabeli.

[Target = [Column = "Margin"], Style = [NumberFormat = "0.0%"]]
[Target = [Column = "Revenue"], Style = [NumberFormat = "$#,##0.00"]]
[Target = [Column = "OrderDate"], Style = [NumberFormat = "yyyy-mm-dd"]]
[Target = [Column = "Code"], Style = [NumberFormat = "@"]]

Kody niestandardowego formatu mogą zawierać do 255 znaków. Kody w formacie Excel używają podwójnych cudzysłowów wokół dosłownego tekstu. Podwaj tę liczbę cudzysłowów wewnątrz wartości tekstowej M, jak w "#,##0 ""USD""". Możesz też uciec przed pojedynczą postacią za pomocą ukośnika odwrotnego. Znaki kontrolne, nawiasy kątne, niedopasowane cudzysłowy lub nawiasy, niebezpieczny tekst bez znaków ucieczki oraz nieobsługiwane tokeny formatu są odrzucane z błędami od 30078 do 30083.

Szerokość kolumny i wysokość wiersza

Width akceptuje wartości od 0 do 255. Zastosuj go do celu całego słupa, celu z nazwanymi kolumnami lub komórki bądź ograniczonego zakresu. Ograniczony zakres określa rozmiar każdej kolumny, którą obejmuje.

Dla efektywnego i przewidywalnego doboru rozmiarów:

• Użyj odwołania do całej kolumny, na przykład "A:A", gdy znasz pozycję w arkuszu.
• Używaj, [Column = "Description"] gdy pozycja może się zmienić, ale schemat tabeli źródłowej jest stabilny.
• Grupuj sąsiednie kolumny, które wymagają tej samej szerokości, na przykład [Target = "B:D", Style = [Width = 14]].
• Unikaj dużych obszarów docelowych dla poszczególnych komórek, gdy potrzebujesz tylko zmienić szerokość kolumn. Cele całokolumnowe nie materializują się w każdej komórce.

Gdy kilka części przypisuje automatycznie obliczane szerokości do tej samej kolumny arkusza kalkulacyjnego, używana jest największa automatycznie obliczona szerokość, aby zawartość jednej części nie zwężała kolumny innej części. Zamiast tego pierwszeństwo mają jawnie określone szerokości: w obrębie jednej części wygrywa późniejsza, równie specyficzna reguła; reguła dla konkretnej kolumny wygrywa z szerokością dla całego arkusza; a gdy nakładające się części jawnie określają szerokość tej samej kolumny arkusza, wygrywa część położona najwyżej. Definiuj jedną wyraźną szerokość na kolumnę arkusza, jeśli to możliwe.

RowHeight akceptuje wartości od 0 do 409 punktów. Działa tylko z celem tekstowym obejmującym cały wiersz, takim jak "2:2" lub "2:10". Inne formy docelowe zwracają błąd 30090.

Użyj jednego zakresu obejmującego cały wiersz dla sąsiednich wierszy mających tę samą wysokość, na przykład [Target = "2:20", Style = [RowHeight = 18]]. To jest bardziej wydajne niż oddzielne reguły dla każdego wiersza. Gdy zakresy wysokości wierszy nakładają się, w części wspólnej pierwszeństwo mają późniejsze reguły, dlatego najpierw należy umieścić szeroką regułę domyślną, a następnie wyjątki.

Określ kolory

Kolory czcionki, wypełnienia i ramek akceptują następujące formy:

Połącz reguły stylu

Gdy wiele reguł ma wpływ na komórkę, ich pola stylu są scalane zgodnie z kolejnością na liście. Późniejsza wartość zastępuje wcześniejszą wartość tego samego pola, podczas gdy pola niepowiązane pozostają w tym samym polu. Na przykład późniejsze wypełnienie nie usuwa wcześniejszej pogrubionej czcionki. Uporządkuj zasady od ogólnego do konkretnego.

Rozmiarowanie jest zgodne z zasadami pierwszeństwa opisanymi w Szerokość kolumny i wysokość wiersza, a nie ze zwykłym scalaniem pól.

Często używane wzorce

Formatuj nagłówek raportu

[Target = "A1:E1", Style = [Font = [Name = "Aptos", Size = 12, Bold = true, Color = "White"], Fill = "#305496", Alignment = [Horizontal = "Center"]]]

Dodaj naprzemienne wypełnienia wierszy

[Target = "A2:E2", Style = [Fill = "#F2F2F2"]],
[Target = "A4:E4", Style = [Fill = "#F2F2F2"]]

Formatuj kolumny walut i wartości procentowych

[Target = [Column = "Revenue"], Style = [NumberFormat = "$#,##0.00"]],
[Target = [Column = "Margin"], Style = [NumberFormat = "0.0%"]]

Dodaj ramkę wokół bloku

[Target = "B2:D10", Style = [Border = [Outline = true, Style = "Medium"]]]

Grupy rozmiarów kolumn i wierszy

[Target = "A:A", Style = [Width = 28]],
[Target = "B:E", Style = [Width = 14]],
[Target = "1:1", Style = [RowHeight = 26]],
[Target = "2:20", Style = [RowHeight = 18]]

Udekoruj pusty obszar raportu

[Target = "G3:I5", Style = [Border = [Outline = true], Fill = "#FFF2CC"]]

Używaj tego schematu tylko z jawnie pozycjonowanym elementem Table lub Range. Więcej informacji można znaleźć w sekcji Stylizowanie komórek poza danymi części.

Style tekstu, fragmenty i scalone zakresy

Użyj elementu Text dla tytułu, notatki lub innej pojedynczej wartości. Zakres ograniczony StartCell tworzy scalony obszar. Style targets nadal używają adresów absolutnych arkuszy roboczych.

Poniższy przykład tworzy scalony tytuł i ustawia styl jego wypełnienia, czcionki, wyrównania, obramowania oraz wysokości wierszy.

let
    ExcelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            {
                "Summary", "ReportTitle", "Text",
                [
                    StartCell = "B2:E3",
                    Styles =
                    {
                        [
                            Target = "B2:E3",
                            Style =
                            [
                                Font = [Bold = true, Size = 16, Color = "White"],
                                Fill = "#305496",
                                Alignment = [Horizontal = "Center", Vertical = "Center"],
                                Border = [Outline = true, Style = "Medium", Color = "#1F1F1F"]
                            ]
                        ],
                        [Target = "2:3", Style = [RowHeight = 24]]
                    }
                ],
                "Quarterly sales summary"
            }
        }
    )
in
    ExcelDocument

Excel przechowuje wartość scaloną i większość formatowania w lewym górnym końcu komórki kotwicznej. Ramka jest inna: aby wyrenderować kontur wokół połączonego regionu, zeszyt musi również zawierać formatowanie ramek na zakrytych komórkach obwodu. Miejsce docelowe programu Excel tworzy te komórki brzegowe, gdy styl wnosi krawędzie Left, Right, Top lub Bottom do scalonego obszaru.

Reguły skierowane do objętych komórek mogą dodawać własne formatowanie. Unikaj stosowania dużych zakresów granic na komórkę do dużych scaleń: jeden region granicy scalonej może zmaterializować maksymalnie 1 048 576 komórek, a arkusz może wygenerować maksymalnie 4 194 304 komórek stylizowanych.

Dodawanie hiperlinków

Możesz dodać hiperłącza do wszystkich elementów zawierających dane: SheetData, Table, Range, Text oraz do bazowej tabeli danych elementu Chart.

Element docelowy musi być kompletnym, bezwzględnym adresem URL http lub https, na przykład https://www.microsoft.com. Względne adresy URL, odniesienia do arkuszy roboczej oraz inne schematy, takie jak file, ftp, i mailto nie są obsługiwane. To ograniczenie zapewnia, że każdy emitowany hiperlink jest zewnętrzną relacją sieciową z zweryfikowanym celem. Niepusty, nieobsługiwany adres URL powoduje, że zapis docelowy nie działa. Jeśli adres URL ma wartość null lub jest pusty, tworzona jest zwykła komórka bez hiperłącza.

Dodaj hiperłącza na dwa sposoby:

• Dodaj regułę do właściwości Hyperlinks części, gdy kolumna zawiera adresy URL.
• Dodaj metadane Hyperlink do poszczególnych wartości, gdy tekst wyświetlany, adresy URL lub opisy różnią się w zależności od wiersza.

Dodaj hiperłącza z kolumny URL

Każda reguła w Properties[Hyperlinks] wspiera następujące pola:

Zasada nie dodaje, nie usuwa ani nie zmienia nazw kolumn. Adres URL nulowy lub pusty pozostaje zwykłą komórką i nie tworzy linku hiperłącznego.

Poniższy przykład wyświetla Open dla każdego adresu URL i zastępuje standardowy wygląd hiperłącza jawnie zdefiniowanym stylem.

let
    Sites = #table(
        type table [Company = text, Website = nullable text],
        {
            {"Microsoft", "https://www.microsoft.com"},
            {"Power Query", "https://learn.microsoft.com/power-query/"},
            {"No website", null}
        }
    ),
    ExcelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            {
                "Links", "CompanyLinks", "Table",
                [
                    StartCell = "A1",
                    Hyperlinks =
                    {
                        [UrlColumn = "Website", DisplayText = "Open", Tooltip = "Open website"]
                    },
                    Styles =
                    {
                        [Target = [Column = "Website"], Style = [Font = [Color = "#008272", Underline = "Double", Bold = true]]]
                    }
                ],
                Sites
            }
        }
    )
in
    ExcelDocument

Dodaj hiperłącza do poszczególnych wartości

Dołącz metadane w formularzu value meta [Hyperlink = [Url = ..., Tooltip = ...]]. Jest wymagane Url w rekordzie metadanych, ale może być nullowe. Parametr Tooltip jest opcjonalny. Wartość bazowa staje się wartością wyświetlaną i zachowuje swój normalny typ danych oraz format liczbowy. Jeśli wartość bazowa jest null, a URL nie jest pusty, URL jest wyświetlany.

Poniższy przykład wykorzystuje funkcję pomocniczą do tworzenia tekstu wyświetlanego specyficznego dla wiersza, adresów URL i podpowiedzi narzędzi. Wartości powiązane i zwykłe mogą współistnieć w tej samej kolumnie.

let
    MakeLink = (displayValue as any, urlValue as nullable text, optional tooltipValue as nullable text) as any =>
        if tooltipValue = null then
            displayValue meta [Hyperlink = [Url = urlValue]]
        else
            displayValue meta [Hyperlink = [Url = urlValue, Tooltip = tooltipValue]],
    Links = #table(
        type table [Resource = any],
        {
            {MakeLink("Power Query documentation", "https://learn.microsoft.com/power-query/", "Read the documentation")},
            {"Not a hyperlink"},
            {MakeLink(null, "https://www.microsoft.com")}
        }
    ),
    ExcelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            {"Resources", "ResourceLinks", "SheetData", [], Links}
        }
    )
in
    ExcelDocument

Możesz przypisywać metadane hiperłączy do wartości w kolumnach o ściśle określonym typie, w tym do liczb i dat. Gdy komórka zawiera zarówno metadane hiperłącza, jak i pasującą regułę kolumny, adres URL i etykietka narzędziowa z metadanych mają pierwszeństwo dla tej komórki.

Domyślnie miejsce docelowe stosuje 11-punktową, pojedynczo podkreśloną czcionkę, która wykorzystuje kolor hiperłącza zeszytu ćwiczeń (kolor motywu 10), schemat czcionek minor oraz rodzinę czcionek 2. W nowym zeszycie ćwiczeń nazwa czcionki to Aptos Narrow; motyw zeszytu ćwiczeń określa dokładny widoczny krój pisma i kolor hiperłącza.

Jawne reguły Styles są nakładane na domyślny styl hiperłącza. Na przykład próbka kolumna-reguła zmienia kolor, podkreślenie i wagę, zachowując jednocześnie relację hiperłącza. Pola, których nie nadpisujesz, zachowują domyślne ustawienia hiperłącza.

Każdy arkusz obsługuje do 65 530 linków hiperaktywnych.

Limity celowania

• Cele muszą pozostać w siatce Excel liczącej 1 048 576 wierszy i 16 384 kolumn. Cele poza tymi granicami zwracają błąd 30096 lub 30097.
• Reguła materializująca pojedyncze komórki może obejmować najwyżej 1 048 576 komórek. Arkusz może zawierać maksymalnie 4 194 304 komórki zmaterializowane według reguł stylu.
• Zakresy obejmujące całe kolumny i całe wiersze są reprezentowane w wydajny sposób i nie zużywają limitu materializacji na komórkę tylko dlatego, że rozciągają się na cały arkusz. Wolę je ze względu na szerokie formatowanie i rozmiarowanie.
• RowHeight wymaga tekstu docelowego obejmującego cały wiersz. Width nie może używać całego wiersza ani rekordu wierszowego jako obiektu docelowego.
• Stylizacja poza danymi części podlega ograniczeniom pozycjonowania opisanym w sekcji Komórki stylu poza danymi części.

Limity i rozwiązywanie problemów

Informacje na temat błędów tabel nawigacyjnych i części znajdziesz w artykule Rozwiązywanie problemów z tabelami nawigacyjnymi programu Excel.

Typowe problemy

Reguła szerokości nie zmienia oczekiwanej kolumny

Przyczyna: Cel rekordu, taki jak ten, [Column = "A"] używa nazwy schematu tabeli źródłowej, a nie litery kolumny Excel. W arkuszu z nakładającymi się elementami inna jawnie określona szerokość również może mieć pierwszeństwo.

Rozdzielczość: Użyć "A:A" dla kolumny arkusza A. Użyć [Column = "Description"] dla kolumny źródłowej o nazwie Description. Dla każdej kolumny arkusza użyj jednego jawnie zdefiniowanego ustawienia szerokości.

Reguła RowHeight zwraca błąd 30090

Przyczyna:RowHeight została zastosowana do rekordowego celu lub ograniczonego zakresu komórek.

Rozwiązanie: Użyj docelowego tekstu obejmującego cały wiersz, na przykład [Target = "2:2", Style = [RowHeight = 30]].

Odrzucony jest niestandardowy format liczb

Przyczyna: Dosłowny tekst nie jest cytowany ani unikany, kod zawiera nieobsługiwany token, a cudzysłowy czy nawiasy są niezrównoważone.

Rozdzielczość: Umieść literalny tekst w podwójnych cudzysłowach wewnątrz kodu formatu Excel. W języku M podwój te znaki cudzysłowu, na przykład [NumberFormat = "#,##0 ""USD"""].

Żądana czcionka wygląda inaczej w Excelu

Przyczyna: Żądana czcionka nie jest zainstalowana na komputerze, który otwiera zeszyt, więc Excel zastępuje czcionką.

Rozwiązanie: Użyj powszechnie dostępnej czcionki i opcjonalnie podaj Family odpowiadającą klasyfikację, aby usprawnić zastępowanie.

Nie pojawia się styl lub kolor ramki

Przyczyna:Style i Color na poziomie obramowania mają zastosowanie tylko za pomocą skróconej właściwości Outline.

Rozdzielczość: Dodaj Outline = true, lub zdefiniuj krawędzie bezpośrednio, takie jak [Border = [Top = [Style = "Thin", Color = "Red"]]].

Hiperłącze powoduje niepowodzenie zapisu docelowego

Przyczyna: Niepusty adres URL jest względny lub wykorzystuje schemat inny niż http lub https.

Rozdzielczość: Podaj pełny adres URL, np. https://www.microsoft.com, lub użyj tekstu null lub pustego, gdy komórka nie powinna być linkiem.