Aggiungi formattazione condizionata ai documenti Excel con tabelle di navigazione

Questo articolo spiega come aggiungere la formattazione condizionale (evidenziazione basata sui dati come regole di valore, scale di colore, barre dati e set di icone) ai documenti Excel costruiti con tabelle di navigazione. Si basa su due articoli complementari e non ne ripete i contenuti:

• Creare documenti Excel con tabelle di navigazione tratta le tabelle di navigazione, i tipi di parti e il posizionamento.
• Assegna uno stile ai documenti Excel e aggiungi collegamenti ipertestuali include la grammatica Target, il record Style e i formati di colore, tutti elementi riutilizzati dalla formattazione condizionale.

Overview

La formattazione condizionata è la stessa funzione che si raggiunge in Excel tramite Home > Conditional Formatting: invece di formattare direttamente una cella, si assegna una regola a Excel, e Excel applica la formattazione solo alle celle corrispondenti. Poiché la regola viaggia insieme al quaderno, l'evidenziazione si aggiorna da sola ogni volta che i dati cambiano.

Si applicano formattazioni condizionali a una parte SheetData, Table, Range o Text, oppure alla tabella sottostante di una parte Chart, tramite la proprietà ConditionalFormatting. È un elenco di regole, e ogni regola è un record con un Target (le celle da valutare), un Type (che tipo di regola) e alcuni campi specifici per tipo:

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

Target Usa la stessa grammatica della Styles proprietà:

Consulta Scegli una destinazione di stile per tutte le regole della destinazione. [Column = "A"] significa che la colonna sorgente è nominata A, non la colonna del foglio di calcolo A; usa "A:A" per la colonna del foglio di lavoro.

I nomi dei campi record M e i nomi delle colonne dello schema di origine fanno distinzione tra maiuscole e minuscole. Scrivi campi come Target, Type, Style, e Thresholds esattamente come mostrato. I valori elencati documentati come CellIs, GreaterThan, e percent sono accettati senza considerare il caso, ma si utilizza il casing documentato.

Ogni regola appartiene a una delle due famiglie, che determinano se fornire un Style:

Le forme canoniche delle regole sono:

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

Un formato Style condizionale può impostare uno qualsiasi di questi componenti (tutti descritti in componenti Stile):

• Font (nome, dimensione, grassetto, corsivo, barrato, sottolineato, colore e le altre proprietà del carattere)
• Fill (colore di sfondo o motivo)
• Border (bordi e diagonali)
• NumberFormat(un codice in formato numerico Excel)
• Alignment (allineamento orizzontale e verticale, avvolgimento, rotazione, rientrazione, ordine di lettura)
• Protection (bloccati / nascosti, che si attivano solo quando il foglio è protetto)

Width e RowHeightnon fanno parte della formattazione condizionale: una regola cambia l'aspetto delle celle ma non può ridimensionare colonne o righe. Includerli in una Style regola è un errore (30141).

Riferimento rapido

Campi comuni

Thresholds

Le scale di colore, le barre dati e i set di icone devono sapere dove si collocano i loro colori, lunghezze di barra o punti di interruzione delle icone nell'intervallo di valori. Descrivi ciascuna di queste posizioni con una breve traccia di [Type, Value]. Queste scelte corrispondono alle opzioni che Excel offre nei menu a tendina Minimo, Massimo e Tipo delle sue finestre di dialogo di scala colore, barra dati e icone.

Il Value per num, percent, e percentile devono essere numerici. Non includere Value per min o max.

Solo i record di soglia impostati da icone accettano il flag opzionale GreaterThanOrEqual . Non aggiungerlo a ColorScale stop o alle soglie DataBarMin e Max Vedi IconSet.

Esempio minimo

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

Regole di stile

Le regole di stile fanno una domanda sì/no su ogni cella e applicano la tua Style risposta ovunque sia sì. Coprono le Regole delle Celle di Evidenzia di Excel e i menu Regole Superiori/Inferiori, oltre a regole basate su formule.

CellIs: confronta ogni cella con un valore

Questa regola corrisponde al gruppo Regole evidenziazione celle di Excel. Ogni cella nel target viene confrontata con il valore o i valori forniti e il Style viene applicato ovunque il confronto risulti vero.

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

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

Espressione: la tua formula personale

Questa regola corrisponde alla regola di Excel Usa una formula per determinare quali celle formattare. Formulaè qualsiasi formula Excel che restituisce vero o falso. Excel lo valuta per la cella in alto a sinistra del bersaglio, poi lo riempie lungo il resto del bersaglio, spostando i riferimenti non ancorati nello stesso modo in cui farebbe una copia di una formula in Excel. Blocca una colonna o una riga con $ per mantenerla fissa.

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

Poiché la formula risiede all'interno di un valore di testo M, qualsiasi doppia virgoletta in essa deve essere raddoppiata. Per confrontare con il testo Active, scrivi Formula = "$D2=""Active""".

Regole di presenza: vuoti, errori e duplicati

Queste regole rispecchiano le opzioni vuote, errori e Valori Duplicati di Excel. Non richiedono campi aggiuntivi, solo Target, Type, e Style. Excel determina la condizione per te.

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

Top10: valori più alti o più bassi

Questa regola utilizza le Regole Top/Bottom di Excel (N elementi superiori o inferiori, o N %superiori/inferiori). Nonostante il nome, Rank non è limitato a 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"]]

Sopra la media: sopra o sotto la media

Questa regola corrisponde alle regole > Top/Bottom di Excel sopra/sotto la media. Excel calcola la media dell'intervallo target e evidenzia le celle sul lato che scegli.

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

Periodo: date relative a oggi

Questa regola corrisponde alle regole > Highlight Cell di Excel Una Data che Accide. Evidenzia le celle di data che cadono in un periodo misurato rispetto al giorno in cui il workbook viene aperto, così l'evidenziazione rimane aggiornata nel tempo. Devi includere TimePeriod, e deve essere uno di: today, yesterday, tomorrow, last7DaysthisWeek, lastMonthlastWeeknextWeekthisMonthnextMonth.

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

Regole visive

Le regole visive disegnano autonomamente i propri elementi grafici (una sfumatura, barre o icone), quindi non accettano mai un Style o StopIfTrue. Puoi controllarne interamente l'aspetto tramite i campi del tipo stesso.

ColorScale: un gradiente a 2 o 3 colori

Le Scale di colore di Excel sfumano ogni cella in base al punto in cui si colloca il suo valore nell'intervallo, fondendo tra i colori di stop. ColorScale è una lista di 2 o 3 stop; ogni stop è una soglia (Type, più Value quando necessario) e un Color. Le fermate ai capolinea di solito usano min e max; una fermata intermedia di solito usa percentile o num.

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

DataBar: barre nelle celle

Le Barre Dati di Excel disegnano una piccola barra orizzontale in ogni cella, più lunga per valori più grandi, come un mini grafico a barre all'interno della colonna.

// 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: un'icona per cella

Gli Icons Set di Excel mettono una piccola icona (un semaforo, una freccia, una bandiera, una classificazione e così via) in ogni cella in base al suo valore.

ID del set di icone: 3Symbols2, 4Arrows, 5Quarters, 5Arrows, 5Rating, 5ArrowsGray, 4TrafficLights, 4Rating, 4RedToBlack, 4ArrowsGray, 3Symbols, 3TrafficLights2, 3Signs, 3TrafficLights1, 3Flags, 3ArrowsGray, 3Arrows. Altri tre set sono stati aggiunti nelle versioni più recenti di 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]}]

Formatta il testo unito

La formattazione condizionale funziona anche su parti Text. Quando StartCell definisce un'area unita, specifica l'area con un intervallo assoluto o con un riferimento di riga o rettangolare che corrisponde all'area. La regola si applica all'intera area unita.

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

Per un elemento con posizionamento automatico Text, le destinazioni del record vengono risolte dopo il posizionamento e possono estendersi sull'intera regione unita. Usa un riferimento esplicito StartCell quando la regola deve fare riferimento a indirizzi fissi del foglio di lavoro.

Note e limitazioni

• L'ordine delle regole è la priorità. La prima regola di abbinamento nella lista ha la priorità più alta. Diverse regole possono applicarsi contemporaneamente alle stesse celle (ad esempio, una barra dati sotto un evidenziato basato su valori); si stratificano in ordine di priorità. Usato StopIfTrue per impedire a Excel di applicare regole di priorità inferiore a una cella una volta che una regola precedente corrisponde.
• Style Appartiene solo alle regole di stile. Fornire Style su un ColorScale, DataBar, o IconSet regola è un errore (30130); omettere Style su una regola di stile è un errore (30125).
• Width e RowHeight non si applicano. Un formato condizionale può cambiare l'aspetto di una cella ma non può ridimensionare colonne o righe. Includere Width o RowHeight in un Style viene rifiutato (30141) invece di essere eliminato silenziosamente.
• Le funzionalità più recenti richiedono un Excel recente. Le barre dei dati avanzate, ovvero un riempimento a tinta unita, colori per i valori negativi, un asse, bordi o una direzione da destra a sinistra Direction, e i set di icone 3Stars, 3Triangles e 5Boxes sono stati introdotti in Excel 2010. Vengono visualizzati completamente in Excel 2010 e successivi, inclusi Microsoft 365 ed Excel per il web, e si degradano in modo elegante in Excel 2007 fino a diventare una semplice barra di gradiente o un classico set di icone con lo stesso numero di icone. MinLength / MaxLength e per soglia GreaterThanOrEqual sono opzioni classiche che funzionano in ogni versione supportata.
• Alcune opzioni Excel più dettagliate non sono esposte. Per design, i glifi personalizzati per ogni icona (mescolando icone da set diversi) non hanno campo su questa superficie e usano i valori predefiniti di Excel. I campi supportati per ogni tipo di regola sono esattamente quelli elencati nelle tabelle di riferimento rapido e per regola sopra.
• I bersagli seguono la grammatica condivisa. [Column = "Name"] è basata sul nome, e le convenzioni di coordinate corrispondono alle regole di stile target. Sono supportati intervalli assoluti che sono al di fuori di qualsiasi parte di dati, ma (come gli stili) solo nei fogli costruiti da parti Table o Range posizionate esplicitamente, non su parti SheetData o parti posizionate automaticamente.
• I dati vuoti eliminano le regole di colonna. Una [Column = "Name"] regola su una parte che ha un'intestazione ma nessuna riga di dati non ha nulla da valutare, quindi la regola viene saltata silenziosamente invece di produrre un intervallo invalido.
• Le date diventano numeri di serie Excel. Un CellIs confronto con un valore data o data-ora viene scritto come il seriale di data corrispondente in Excel. Un valore che Excel non può memorizzare come data o ora viene rifiutato (30136).
• I valori utilizzano tipi scalari. A CellIsValue può essere un numero, un testo, un valore logico, null, una data, una data/ora, una data/ora/fuso orario, un'ora o una durata. Le liste sono accettate solo come input a due valori per Between e NotBetween; liste, record, tabelle, funzioni e valori binari non sono validi per valori di confronto singoli (30143).
• I colori accettano ogni forma descritta in Specifica colori.

Risoluzione dei problemi

Informazioni di riferimento sugli errori

Problemi comuni

Una regola visiva viene rifiutata

Problema: Una regola DataBar, ColorScale o IconSet genera 30130.

Causa: Le regole visive generano la propria grafica e non possono contenere un Style o StopIfTrue.

Soluzione: rimuovere Style e StopIfTrue. Imposta i colori tramite i campi del tipo stesso (Color, ColorScale punti di arresto e così via).

Il set di icone genera 30134

Problema: Una regola viene respinta anche se l'ID sembra IconSet corretto.

Causa: Il Thresholds conteggio non corrisponde al conteggio delle icone nell'ID (ad esempio, quattro soglie con 3TrafficLights1).

Soluzione: Fornire esattamente tante soglie quante la cifra iniziale dell'ID (3, 4 o 5).

Una barra dati ricca appare semplice in Excel più vecchio

Problema: A DataBar con Direction, colori negativi, un asse, bordi o un riempimento solido viene reso come una barra di gradiente semplice.

Motivo: Queste funzionalità sono state introdotte in Excel 2010. Excel 2007 mostra la barra di riserva integrata.

Soluzione: apri il workbook in Excel 2010 o versioni successive (inclusi Microsoft 365 ed Excel per il web). Non è necessario modificare la regola.

Una regola evidenzia la colonna sbagliata

Problema: [Column = "A"] prende di mira le celle sbagliate.

Causa: Come per gli stili, [Column = ...] corrisponde a un nome di colonna nello schema della tabella sorgente di Power Query, non a una lettera di colonna Excel.

Soluzione: Passa il nome della colonna sorgente Power Query, oppure usa un intervallo di stringhe come "A:A". Vedi Scegli un obiettivo di stile.