Tilføj betinget formatering til Excel-dokumenter med navigationstabeller

Denne artikel forklarer, hvordan man tilføjer betinget formatering (datadrevet fremhævelse såsom værdiregler, farveskalaer, databjælker og ikonsæt) til Excel-dokumenter bygget med navigationstabeller. Den bygger videre på to ledsagende artikler og gentager ikke deres indhold:

• Oprettelse af Excel-dokumenter med navigationstabeller dækker navigationstabeller, deltyper og positionering.
• Style Excel-dokumenter og tilføjelse af hyperlinks dækker Target grammatik-, postStyle- og farveformater, som alle genbruges af betinget formatering.

Oversigt

Betinget formatering er den samme funktion, som du opnår i Excel gennem Home > Conditional Formatting: i stedet for at formatere en celle direkte, giver du Excel en regel, og Excel anvender formateringen kun på de celler, der matcher. Fordi reglen følger med arbejdsbogen, opdateres markeringen af sig selv, hver gang dataene ændres.

Du tilknytter betingede formater til en , , , eller Text del, eller til en Chart parts bagbord, gennem egenskabenConditionalFormatting. RangeTableSheetData Det er en liste over regler, og hver regel er en post med en Target (cellerne til evaluering), a Type (hvilken slags regel) og nogle typespecifikke felter:

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

Target bruger samme grammatik som egenskaben Styles :

Se Vælg et stilmål for de komplette målregler. [Column = "A"] betyder kildekolonnen med navnet A, ikke arbejdsarkkolonne A; brug "A:A" for arbejdsarkskolonnen.

M postfeltnavne og kildeskema-kolonnenavne er små og små bogstaver. Skriv felter som Target, Type, Style, og Thresholds præcis som vist. Dokumenterede opregnede værdier som CellIs, GreaterThan, og percent accepteres uden hensyn til kasus, men bruger den dokumenterede forstørrelse.

Hver regel tilhører en af to familier, som afgør, om du leverer en Style:

De kanoniske regelformer er:

// 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"]

Et betinget format Style kan sætte en af disse komponenter (alle beskrevet i Stil-komponenter):

• Font (navn, størrelse, fed, kursiv, gennemstregning, understregning, farve og de andre skrifttypeegenskaber)
• Fill (baggrundsfarve eller mønster)
• Border (kanter og diagonaler)
• NumberFormat(en Excel-nummerkode)
• Alignment (horisontal og vertikal justering, wrap, rotation, indrykning, læserækkefølge)
• Protection (låst / skjult, som kun træder i kraft, når arket er beskyttet)

Width RowHeight og er ikke en del af betinget formatering: en regel ændrer, hvordan celler ser ud, men kan ikke ændre størrelsen på kolonner eller rækker. At inkludere dem i en regels Style er en fejl (30141).

Hurtig reference

Fælles felter

Tærskler

Farveskalaer, databarer og ikonsæt skal vide, hvor deres farver, bjælkelængder eller ikonbrudpunkter ligger på tværs af værdiintervallet. Du beskriver hver af disse positioner med en lille registrering af [Type, Value]. Disse valg matcher de muligheder, Excel tilbyder i dropdown-menuerne Minimum, Maksimum og Type i dets farveskala, databjælke og ikonsæt.

For Valuenum, percent, og percentile skal være numerisk. Inkluder Value ikke for min eller max.

Kun ikonsat tærskelposter accepterer det valgfrie GreaterThanOrEqual flag. Læg det ikke til ColorScale stop eller DataBarMin og Max thresholds. Se IconSet.

Minimalt eksempel

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

Stilregler

Stilregler, stil et ja/nej-spørgsmål om hver celle og anvender dig Style , hvor svaret er ja. De dækker Excel's Highlight Cells-regler og Top/Bottom Rules-menuer samt formelbaserede regler.

CellIs: sammenlign hver celle med en værdi

Denne regel svarer til Excel's Highlight Cells Rules-familie. Hver celle i målet sammenlignes med den eller de værdier, du angiver, og anvendes Style hvor sammenligningen er sand.

Operatorer: , , , , , , GreaterThan, Between, NotBetweenContainsText, , BeginsWithNotContains, . EndsWithGreaterThanOrEqualNotEqualEqualLessThanOrEqualLessThan

[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"]]]

Udtryk: din egen formel

Denne regel svarer til Excel's Brug en formel til at bestemme, hvilke celler der skal formateres. Formulaer en hvilken som helst Excel-formel, der returnerer sand eller falsk. Excel evaluerer det for øverste venstre celle i målet, fylder det derefter ud over og ned over resten af målet, og flytter uankrede referencer på samme måde, som man kopierer en formel i Excel. Lås en søjle eller række med $ for at holde det fast.

[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"]]

Da formlen ligger i en M-tekstværdi, skal alle dobbeltanførselstegn i den fordobles. For at sammenligne med teksten Active, skriv Formula = "$D2=""Active""".

Tilstedeværelsesregler: blanke felter, fejl og dubletter

Disse regler spejler Excel's blanke, fejl- og duplikationsværdier-muligheder. De kræver ikke ekstra felter, kun Target, Type, og Style. Excel bestemmer tilstanden for dig.

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

Top10: højeste eller laveste værdier

Denne regel bruger Excel's Top/Bund-regler (top eller bund N elementer, eller top/bund N %). På trods af navnet er det Rank ikke begrænset til 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"]]

Over gennemsnittet: over eller under gennemsnittet

Denne regel svarer til Excel's Top/Bund-regler > Over/Under gennemsnittet. Excel beregner gennemsnittet af målområdet og fremhæver cellerne på den side, du vælger.

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

TimePeriod: datoer i forhold til i dag

Denne regel svarer til Excel's Highlight Cells Rules > A Date Occuring. Den fremhæver datoceller, der falder i en periode målt i forhold til den dag, hvor arbejdsbogen åbnes, så markeringen forbliver aktuel over tid. Du skal inkludere TimePeriod, og det skal være en af: today, yesterday, tomorrow, , last7Days, thisWeek, lastWeeknextWeekthisMonthlastMonth. nextMonth

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

Visuelle regler

Visuelle regler maler deres egne grafik (en gradient, bjælker eller ikoner), så de tager aldrig et Style eller StopIfTrue. Du styrer deres udseende udelukkende gennem typens egne felter.

ColorScale: en 2- eller 3-farvegradient

Excel's farveskalaer skygger hver celle efter, hvor dens værdi ligger i området, og blander mellem stopfarverne. ColorScale er en liste af 2 eller 3 stop; hvert stop er en tærskel (Type, plus Value når nødvendigt) og en Color. Endepunktsstop bruger min normalt og max; et midterstop bruger percentile normalt eller num.

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

DataBar: in-cell-bjælker

Excel's databarer tegner en lille vandret bjælke i hver celle, længere for større værdier, som et mini-søjlediagram inde i kolonnen.

// 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: et ikon pr. celle

Excel's ikonsæt placerer et lille ikon (et trafiklys, pil, flag, vurdering osv.) i hver celle baseret på dens værdi.

Ikon-sæt ID'er: 3Arrows, 3ArrowsGray, 3Flags, 3TrafficLights1, 3TrafficLights2, 3Signs, 3Symbols, 3Symbols2, 5Rating4Arrows5ArrowsGray4ArrowsGray4TrafficLights5Arrows4RedToBlack4Rating. 5Quarters Tre yderligere sæt blev tilføjet i nyere versioner af Excel: 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]}]

Formater sammenflettet tekst

Betinget formatering virker også på Text dele. Når StartCell en samlet region defineres, skal man målrette området med en absolut rækkevidde eller med en række eller rektangelet mål, der opløses til området. Reglen gælder i hele det sammenlagte område.

#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"
        }
    }
)

For en auto-positioneret Text del løses rekordmål efter placering og kan spænde over det sammensmeltede område. Brug en eksplicit, StartCell når reglen skal henvise til faste arbejdsarkadresser.

Bemærkninger og begrænsninger

• Regelrækkefølgen er prioritet. Den første matchningsregel på listen har højeste prioritet. Flere regler kan gælde for de samme celler på én gang (for eksempel en databar under et værdibaseret highlight); de lægges i lag i prioriteret rækkefølge. Brug StopIfTrue den for at forhindre Excel i at anvende lavprioritetsregler på en celle, når en tidligere regel matcher.
• Style Hører kun til stilreglerne. At levere Style på en ColorScale, DataBar, eller IconSet regel er en fejl (30130); at Style udelade en stilregel er en fejl (30125).
• Width Og RowHeight ansøg ikke. Et betinget format kan ændre, hvordan en celle ser ud, men kan ikke ændre størrelsen på kolonner eller rækker. Inkludering Width af eller RowHeight i a Style afvises (30141) i stedet for at blive fjernet i stilhed.
• Nyere funktioner kræver en nyere Excel. Rige databjælker (et solidt fyld, negative farver, en akse, kanter eller en højre-til-venstre Direction) og 3Stars, 3Triangles, og 5Boxes ikonsættene blev introduceret i Excel 2010. De vises fuldt ud i Excel 2010 og senere, inklusive Microsoft 365 og Excel på internettet, og degraderes elegant i Excel 2007 til en almindelig gradientlinje eller et klassisk ikon med samme antal ikoner. MinLength / MaxLength og per-threshold GreaterThanOrEqual er klassiske muligheder, der virker i alle understøttede versioner.
• Et par fintgåede Excel-muligheder er ikke tilgængelige. Designet er at brugerdefinerede per-ikon-glyffer (der blander ikoner fra forskellige sæt) uden felt på denne overflade og bruger Excel's standardindstillinger. De understøttede felter for hver regeltype er præcis dem, der er listet i Quick reference - og per-rule-tabellerne ovenfor.
• Målene følger den fælles grammatik. [Column = "Name"] er navnebaseret, og koordinatkonventionerne matcher stilmålsreglerne. Absolutte intervaller, der ligger uden for enhver datadel, understøttes, men (ligesom stilarter) kun på ark bygget af eksplicit positionerede Table dele eller Range dele, ikke på SheetData dele eller auto-positionerede dele.
• Regler for tomme datafald, kolonner. En [Column = "Name"] regel på en del, der har et header, men ingen datarækker, har intet at evaluere, så reglen springes lydløst over i stedet for at producere et ugyldigt interval.
• Datoer bliver til Excel-serienumre. En CellIs sammenligning med en dato- eller dato-tidsværdi skrives som den matchende Excel-datoserie. En værdi, som Excel ikke kan gemme som dato eller tidspunkt, bliver afvist (30136).
• Værdier bruger skalartyper. A kan være et tal, tekst, logisk, nul, dato, dato, tid, dato, CellIsValue tidszone, tid eller varighed. Lister accepteres kun som to-værdi input for Between og NotBetween; lister, poster, tabeller, funktioner og binære værdier er ikke gyldige enkeltsammenligningsværdier (30143).
• Farver accepterer alle former beskrevet i Specificér farver.

Fejlfinding

Fejlreference

Almindelige problemer

En visuel regel afvises

Problem: A DataBar, ColorScale, eller IconSet regelkast 30130.

Årsag: Visuelle regler gengiver deres egne grafik og kan ikke bære en Style eller StopIfTrue.

Løsning: FjernStyle og StopIfTrue. Indstil farver gennem typens egne felter (Color, ColorScale stop osv.).

Ikonsæt kast 30134

Problem: EnIconSet regel afvises, selvom ID'et ser rigtigt ud.

Årsag: Antallet Thresholds matcher ikke ikonantallet i ID'et (for eksempel fire tærskler med 3TrafficLights1).

Løsning: Angiv præcis så mange tærskler som det foranliggende ciffer i ID'en (3, 4 eller 5).

En rig databar ser almindelig ud i ældre Excel

Problem: ADataBar med Direction, negative farver, en akse, kanter eller en solid fyldning gengives som en almindelig gradientbjælke.

Årsag: Disse funktioner introduceres i Excel 2010. Excel 2007 viser den indbyggede fallback-bjælke.

Løsning: Åbn arbejdsbogen i Excel 2010 eller senere (inklusive Microsoft 365 og Excel på internettet). Der er ikke behov for ændring af reglen.

En regel fremhæver den forkerte kolonne

Problem: [Column = "A"]målretter de forkerte celler.

Årsag: Som med stilarter matcher [Column = ...] den et kolonnenavn i kildens Power Query-tabelskema, ikke et Excel-kolonnebogstav.

Løsning: Send kildens kolonnenavn i Power Query, eller brug et strengområde som "A:A". Se Vælg et stilmål.