Dodaj formatowanie warunkowe do dokumentów Excel za pomocą tabel nawigacyjnych

Ten artykuł wyjaśnia, jak dodać formatowanie warunkowe (podświetlanie oparte na danych, takie jak reguły wartości, skale kolorów, paski danych i zestawy ikon) do dokumentów Excel zbudowanych za pomocą tabel nawigacyjnych. Opiera się na dwóch artykułach towarzyszących i nie powtarza ich treści:

• Tworzenie dokumentów Excel z tabelami nawigacyjnymi obejmuje tabele nawigacyjne, typy części i pozycjonowanie.
• Stylizowanie dokumentów programu Excel i dodawanie hiperłączy obejmuje składnię Target, rekord Style oraz formaty kolorów, z których wszystkich ponownie używa formatowanie warunkowe.

Przegląd

Formatowanie warunkowe to ta sama funkcja, którą osiągasz w Excel poprzez > Home Conditional Formatting: zamiast formatować komórkę bezpośrednio, nadajesz Excelowi regułę, a Excel stosuje formatowanie tylko do pasujących komórek. Ponieważ reguła podróżuje wraz ze zeszytem, podświetlanie odświeża się samo za każdym razem, gdy dane się zmienią.

Formaty warunkowe przypisujesz do elementu SheetData, Table, Range lub Text albo do tabeli źródłowej elementu Chart za pomocą właściwości ConditionalFormatting. Jest to lista reguł, a każda reguła jest rekordem z polem Target (komórki do oceny), polem Type (określającym rodzaj reguły) oraz kilkoma polami specyficznymi dla danego typu:

ConditionalFormatting =
{
    [Target = [Column = "Revenue"], Type = "CellIs", Operator = "GreaterThan", Value = 2000, Style = [Fill = "#C6EFCE"]],
    [Target = [Column = "Margin"],  Type = "DataBar", Color = "#638EC6"]
}

Target używa tej samej gramatyki co właściwość Styles:

Zobacz Wybierz docelowy styl, aby uzyskać pełne reguły dotyczące celu. [Column = "A"] oznacza kolumnę źródłową o nazwie A, a nie kolumnę A arkusza; użyj "A:A" dla kolumny arkusza.

M nazw pól rekordów i kolumn schematów źródłowych są zależne od wielkości liter. Zapisz pola takie jak Target, Type, Style, oraz Thresholds dokładnie tak, jak pokazano. Udokumentowane wartości wyliczone, takie jak CellIs, GreaterThan, i percent są akceptowane bez względu na przypadek, ale używają udokumentowanego obudowania.

Każda reguła należy do jednej z dwóch rodzin, od których zależy, czy podajesz Style:

Kanoniczne kształty reguł to:

// Style rule: Style is required; StopIfTrue is optional.
[Target = [Column = "Revenue"], Type = "CellIs", Operator = "GreaterThan", Value = 2000, Style = [Fill = "#C6EFCE"], StopIfTrue = false]

// Visual rule: don't include Style or StopIfTrue.
[Target = [Column = "Margin"], Type = "DataBar", Color = "#638EC6"]

Format Style warunkowy może ustawić dowolny z tych komponentów (wszystkie opisane w Style components):

• Font (nazwa, rozmiar, pogrubienie, kursywa, przekreślenie, podkreślenie, kolor i inne właściwości czcionki)
• Fill (kolor tła lub wzór)
• Border (krawędzie i przekątne)
• NumberFormat(kod w formacie numerów Excel)
• Alignment (wyrównanie poziome i pionowe, owinięcie, rotacja, wcięcie, kolejność odczytu)
• Protection (zablokowane / ukryte, które mają zastosowanie tylko wtedy, gdy arkusz jest chroniony)

Width i RowHeightnie są częścią formatowania warunkowego: reguła zmienia wygląd komórek, ale nie może zmieniać rozmiaru kolumn czy wierszy. Włączenie ich do reguł Style to błąd (30141).

Szybki przewodnik

Typowe pola

Thresholds

Skale kolorów, paski danych i zestawy ikon muszą wiedzieć, gdzie ich kolory, długości pasków lub punkty przerwania ikon znajdują się w zakresie wartości. Opisujesz każdą z tych pozycji za pomocą małego rekordu [Type, Value]. Te opcje odpowiadają opcjom, które oferuje Excel w rozwijanych menu Minimum, Maximum i Typ w dialogach skali kolorów, paska danych i zestawu ikon.

Value dla num, percent i percentile musi być numeryczne. Nie włączaj Value dla min lub max.

Tylko rekordy progowe zestawu ikon akceptują opcjonalną flagę GreaterThanOrEqual. Nie dodawaj tego do ColorScale stopów ani DataBarMin i Max progów. Zobacz IconSet.

Minimalny przykład

let
    sales = #table(
        type table [Region = text, Revenue = number, Margin = number],
        {{"North", 1200, 0.18}, {"South", 2750, 0.32}, {"West", 3100, 0.41}}
    ),
    excelDocument = #table(
        type table [PartType = nullable text, Properties = nullable record, Data = any],
        {
            {
                "Table",
                [
                    ConditionalFormatting =
                    {
                        [Target = [Column = "Revenue"], Type = "CellIs", Operator = "GreaterThan", Value = 2000, Style = [Fill = "#C6EFCE"]],
                        [Target = [Column = "Margin"],  Type = "ColorScale", ColorScale = {[Type = "min", Color = "#F8696B"], [Type = "max", Color = "#63BE7B"]}]
                    }
                ],
                sales
            }
        }
    )
in
    excelDocument

Reguły stylów

Zasady stylu zadają w odniesieniu do każdej komórki pytanie, na które odpowiada się „tak” lub „nie”, i stosują Style wszędzie tam, gdzie odpowiedź brzmi „tak”. Obejmują one menu Reguły wyróżniania komórek i Reguły najwyższych/najniższych wartości w programie Excel, a także reguły oparte na formułach.

CellIs: porównaj każdą komórkę z wartością

Ta reguła odpowiada rodzinie reguł Highlight Cells w Excel. Każda komórka w celu jest porównywana z wartością lub wartościami, które podajesz, i stosuje się Style tam, gdzie porównanie jest prawdziwe.

Operatory: LessThan, Equal, NotEqual, GreaterThanOrEqual, GreaterThan, EndsWith, BeginsWith, ContainsText, Between, NotBetween, NotContains, LessThanOrEqual.

[Target = [Column = "Revenue"], Type = "CellIs", Operator = "Between", Value = {500, 3000}, Style = [Fill = "#FFEB9C"]],
[Target = [Column = "Region"],  Type = "CellIs", Operator = "BeginsWith", Value = "N", Style = [Font = [Bold = true, Color = "#9C0006"]]]

Wyrażenie: własna formuła

Ta reguła odpowiada regule programu Excel Użyj formuły, aby określić, które komórki sformatować. Formulato dowolna formuła Excel, która zwraca prawdę lub fałsz. Excel oblicza ją dla komórki w lewym górnym rogu obszaru docelowego, a następnie wypełnia nią pozostałą część obszaru docelowego w prawo i w dół, przesuwając niezakotwiczone odwołania tak samo, jak przy kopiowaniu formuły w Excelu. Zablokuj kolumnę lub wiersz za pomocą $, aby pozostawały nieruchome.

[Target = "C12:C20", Type = "Expression", Formula = "$C12>$D12", Style = [Font = [Bold = true]]],
[Target = "B23:B27", Type = "Expression", Formula = "WEEKDAY($B23,2)>5", Style = [Fill = "#FFC7CE"]]

Ponieważ wzór znajduje się w wartości tekstowej M, wszelkie podwójne cudzysłowy w niej muszą być podwojone. Aby porównać z tekstem Active, zapisz Formula = "$D2=""Active""".

Reguły obecności: puste miejsca, błędy i duplikaty

Te zasady odzwierciedlają opcje puste, błędne i zduplikowane wartości w Excel. Nie wymagają dodatkowych pól, tylko Target, Type, i .Style Excel określa warunek za ciebie.

[Target = [Column = "Sku"],  Type = "UniqueValues", Style = [Fill = "#C6EFCE"]],
[Target = [Column = "Note"], Type = "ContainsBlanks", Style = [Fill = "#FFEB9C"]]

Top10: najwyższe lub najniższe wartości

Ta zasada wykorzystuje Górne/Dolne Reguły Excel (górne lub dolne N przedmiotów, lub górne/dolne N %). Pomimo nazwy, Rank nie ogranicza się do 10.

[Target = [Column = "Stock"], Type = "Top10", Rank = 2, Style = [Fill = "#FFC7CE"]],
[Target = [Column = "Score"], Type = "Top10", Rank = 20, Percent = true, Bottom = true, Style = [Fill = "#FFC7CE"]]

PowyżejŚrednia: powyżej lub poniżej średniej

Ta reguła odpowiada regułom programu Excel Pierwsze/ostatnie reguły > Powyżej/poniżej średniej. Excel oblicza średnią dla zakresu docelowego i wyróżnia komórki po wybranej stronie.

[Target = [Column = "Score"], Type = "AboveAverage", Style = [Fill = "#C6EFCE"]],
[Target = [Column = "Score"], Type = "AboveAverage", AboveAverage = false, EqualAverage = true, StdDev = 1, Style = [Fill = "#FFEB9C"]]

Okres czasowy: daty względem współczesności

Ta zasada odpowiada opcji programu Excel Reguły wyróżniania komórek > Występująca data. Podświetla komórki zawierające daty, które mieszczą się w okresie wyznaczonym względem dnia otwarcia skoroszytu, dzięki czemu podświetlenie pozostaje aktualne z upływem czasu. Musisz uwzględnić TimePeriod, a musi to być jeden z: today, yesterday, , tomorrow, last7DaysthisWeek, lastWeek, . nextWeeknextMonththisMonthlastMonth

[Target = [Column = "Updated"], Type = "TimePeriod", TimePeriod = "last7Days", Style = [Fill = "#FFEB9C"]]

Reguły wizualizacji

Zasady wizualne rysują własne elementy graficzne (gradienty, paski lub ikony), więc nigdy nie przyjmują Style ani StopIfTrue. Kontrolujesz ich wygląd całkowicie za pomocą pól typu.

ColorScale: 2- lub 3-kolorowy gradient

Skale kolorów w programie Excel nadają każdej komórce odcień w zależności od położenia jej wartości w zakresie, płynnie przechodząc między kolorami granicznymi. ColorScale jest listą 2 lub 3 punktów; każdy punkt to próg (Type, oraz Value w razie potrzeby) oraz Color. Końcowe przystanki zwykle używają min i max; środkowy przystanek zwykle używa percentile lub num.

[Target = [Column = "Revenue"], Type = "ColorScale", ColorScale =
    {[Type = "min", Color = "#F8696B"], [Type = "percentile", Value = 50, Color = "#FFEB84"], [Type = "max", Color = "#63BE7B"]}]

DataBar: paski w komórkach

Paski danych programu Excel wyświetlają w każdej komórce mały poziomy pasek, dłuższy dla większych wartości, jak miniaturowy wykres słupkowy w kolumnie.

// Simple gradient bar spanning the data.
[Target = [Column = "Margin"], Type = "DataBar", Color = "#638EC6", Gradient = true],

// Solid bar, never shorter than 10% or longer than 90% of the cell width.
[Target = [Column = "Score"], Type = "DataBar", Color = "#638EC6", Gradient = false, MinLength = 10, MaxLength = 90],

// Right-to-left bars (they grow from the right edge of the cell).
[Target = [Column = "Usage"], Type = "DataBar", Color = "#638EC6", Direction = "rightToLeft"],

// Full data bar with negative colors and a center axis.
[Target = [Column = "Amount"], Type = "DataBar", Color = "#638EC6", Border = true, BorderColor = "#2F5496",
    NegativeFillColor = "#FF0000", NegativeBorderColor = "#C00000", AxisPosition = "middle", AxisColor = "#808080"]

IconSet: ikona na komórkę

Zestawy ikon Excel umieszczają w każdej komórce małą ikonę (sygnalizację świetlną, strzałkę, flagę, ocenę itd.) w zależności od jej wartości.

Identyfikatory zestawu ikon: 3Arrows, 3ArrowsGray, 3Flags, 3TrafficLights1, 3TrafficLights2, 3Signs, 3Symbols, 3Symbols2, 4Arrows, 4ArrowsGray, 5Quarters, 4RedToBlack, 4Rating, 4TrafficLights, 5Arrows, 5ArrowsGray, 5Rating. W nowszych wersjach Excela dodano trzy kolejne zestawy: 3Stars, 3Triangles, . 5Boxes

// Three traffic lights split at the 0 / 33 / 67 percentiles.
[Target = [Column = "Stock"], Type = "IconSet", IconSet = "3TrafficLights1",
    Thresholds = {[Type = "percent", Value = 0], [Type = "percent", Value = 33], [Type = "percent", Value = 67]}],

// Use a strict "greater than" at the top boundary instead of the default ">=".
[Target = [Column = "Rating"], Type = "IconSet", IconSet = "3Symbols",
    Thresholds = {[Type = "percent", Value = 0], [Type = "percent", Value = 33], [Type = "percent", Value = 67, GreaterThanOrEqual = false]}]

Format połączonego tekstu

Formatowanie warunkowe działa także na Text częściach. Gdy element StartCell definiuje scalony obszar, odwołuj się do niego za pomocą zakresu bezwzględnego albo odwołania do wiersza lub prostokąta, które wskazuje na ten obszar. Zasada obowiązuje na całym połączonym obszarze.

#table(
    type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
    {
        {
            "Summary", "Status", "Text",
            [
                StartCell = "B2:E2",
                ConditionalFormatting =
                {
                    [Target = [Row = 2], Type = "CellIs", Operator = "Equal", Value = "Review", Style = [Fill = "#FFEB9C", Font = [Bold = true]]]
                }
            ],
            "Review"
        }
    }
)

W przypadku automatycznie pozycjonowanego elementu Text elementy docelowe rekordu są określane po rozmieszczeniu i mogą obejmować scalony obszar. Używaj jawnego StartCell, gdy reguła musi odnosić się do stałych adresów arkusza.

Uwagi i ograniczenia

• Priorytetem jest kolejność zasad. Pierwsza pasująca reguła na liście ma najwyższy priorytet. Kilka reguł może być stosowanych jednocześnie do tych samych komórek (na przykład pasek danych pod podświetleniem opartym na wartości); warstwują się w kolejności priorytetów. UżywajStopIfTrue, aby zapobiec stosowaniu przez Excel reguł o niższym priorytecie do komórki, gdy wcześniejsza reguła się zgadza.
• Style dotyczy tylko reguł stylu. Podanie Style w regule DataBar, ColorScale lub IconSet jest błędem (30130); pominięcie Style w regule stylu jest błędem (30125).
• Width i RowHeight nie mają zastosowania. Format warunkowy może zmieniać wygląd komórki, ale nie może zmieniać rozmiaru kolumn czy wierszy. Dołączanie Width lub RowHeight do Style powoduje odrzucenie (30141), zamiast ich cichego pomijania.
• Nowsze funkcje wymagają nowego Excela. Rozbudowane paski danych (wypełnienie pełne, kolory wartości ujemnych, oś, obramowania lub kierunek od prawej do lewej Direction) oraz zestawy ikon 3Stars, 3Triangles i 5Boxes zostały wprowadzone w programie Excel 2010. Wyświetlają się w pełni w Excel 2010 i nowszych, w tym Microsoft 365 i Excel dla sieci web, a w Excel 2007 łatwo degradują się do zwykłego paska gradientu lub klasycznego zestawu ikon z tą samą liczbą ikon. MinLength / MaxLength A per-threshold GreaterThanOrEqual to klasyczne opcje, które działają we wszystkich obsługiwanych wersjach.
• Niektóre szczegółowe opcje programu Excel nie są dostępne. Z założenia niestandardowe glify dla każdej ikony (mieszanie ikon z różnych zestawów) nie mają pola na tej powierzchni i korzystają z domyślnych ustawień Excel. Obsługiwane pola dla każdego typu reguły to dokładnie te wymienione w tabelach szybkiego odniesienia i poszczególnych reguł powyżej.
• Cele są zgodne z tą samą gramatyką. [Column = "Name"] opiera się na nazwie, a konwencje współrzędnych odpowiadają regułom stylu docelowym. Obsługiwane są zakresy bezwzględne wykraczające poza dowolny obszar danych, ale (podobnie jak style) tylko w arkuszach zbudowanych z jawnie pozycjonowanych części Table lub Range, a nie z części SheetData ani części pozycjonowanych automatycznie.
• Puste dane usuwają reguły kolumn. Reguła [Column = "Name"] dla sekcji, która ma nagłówek, ale nie ma żadnych wierszy danych, nie ma czego oceniać, więc jest pomijana bez komunikatu zamiast powodować utworzenie nieprawidłowego zakresu.
• Daty stają się numerami seryjnymi Excel. Porównanie CellIs z wartością daty lub daty i czasu zapisuje się jako pasujący numer seryjny Excel. Wartość, której Excel nie może zapisać jako daty lub godziny, zostaje odrzucona (30136).
• Wartości wykorzystują typy skalarne. CellIs Value może być liczbą, tekstem, wartością logiczną, wartością null, datą, datą i godziną, datą i godziną ze strefą czasową, godziną lub czasem trwania. Listy są akceptowane tylko jako dwuwartościowe wejście dla Between i NotBetween; listy, rekordy, tabele, funkcje i wartości binarne nie są ważnymi pojedynczymi wartościami porównawczymi (30143).
• Kolory przyjmują każdą formę opisaną w Określ kolory.

Troubleshooting

Referencja błędu

Typowe problemy

Reguła wizualna zostaje odrzucona

Problem: reguła DataBar, ColorScale lub IconSet zgłasza 30130.

Przyczyna: Reguły wizualne wyświetlają własne elementy graficzne i nie mogą zawierać elementu Style ani StopIfTrue.

Rozwiązanie: Usuń Style i StopIfTrue. Ustawiaj kolory za pomocą własnych pól typu (Color, ColorScale punkty itd.).

Rzuty zestawu ikon 30134

Problem: Reguła zostaje odrzucona, mimo że identyfikacja wygląda poprawnie IconSet .

Przyczyna: Liczba Thresholds nie zgadza się z liczbą ikon w ID (na przykład cztery progi z 3TrafficLights1).

Rozwiązanie: Podaj dokładnie tyle progów, ile jest cyfra prowadząca ID (3, 4 lub 5).

Bogaty pasek danych wygląda zwyczajnie w starszym Excelu

Problem: Wykres DataBar z Direction, negatywnymi kolorami, osią, ramkami lub wypełnieniem stałym jest wyświetlany jako zwykły pasek gradientowy.

Powodu: Te funkcje zostały wprowadzone w Excel 2010. Excel 2007 pokazuje wbudowany zapasowy pasek.

Rozwiązanie: Otwórz zeszyt w Excel 2010 lub nowszym (w tym Microsoft 365 i Excel dla sieci web). Nie jest potrzebna zmiana tej zasady.

Reguła wskazuje niewłaściwą kolumnę

Problem: [Column = "A"] celuje w niewłaściwe komórki.

Przyczyna: Podobnie jak styles, pasuje [Column = ...] do nazwy kolumny w źródłowym schemacie tabeli Power Query, a nie do litery kolumny Excel.

Rozwiązanie: Przekaż nazwę kolumny źródłowej w Power Query lub użyj zakresu ciągów, takiego jak "A:A". Zobacz Wybierz docelowy styl.