Zar ha bisogno di applicare un formato condizionale ai valori nella colonna A di un foglio di lavoro, ma non riesce a capire quali potrebbero essere le regole. Oltre alla colonna A, ha anche dati che iniziano nella colonna B e aggiunge periodicamente nuove colonne di dati. Se sono presenti dati in tutte le colonne dispari che iniziano con B, Zar vuole che venga applicato un formato nella colonna A. (B è la colonna di dati 1 per il suo foglio di lavoro, quindi lo considera dispari). Se ci sono dati in tutte le colonne pari che iniziano con C, Zar vuole che venga applicato un formato diverso nella colonna A. Se ci sono dati in tutte le colonne di dati che iniziano con B, per quante possano essere, allora vuole che venga applicato un terzo formato.

Come Zar ha senza dubbio capito, puoi facilmente creare una formula per determinare se ci sono informazioni nelle colonne B e C e applicare la formattazione di conseguenza. In effetti, una semplice formula come questa farà il trucco:

=COUNTA(B1:C1)=2 =COUNTA(B1)=1 =COUNTA(C1)=1

La prima formula restituisce True se ci sono informazioni sia in B che in C, la seconda se ci sono informazioni in B e la terza se ci sono informazioni in C. Finché selezioni “Stop If True” per ogni regola / formula, allora la tua formattazione funzionerà bene.

La creazione di una formula per più colonne oltre B e C è solo marginalmente più difficile. Gli stessi tre tipi di formule, in ordine, sarebbero i seguenti:

=COUNTA(B1:G1)=6)

=COUNTA(B1,D1,F1)=3 =COUNTA(C1,E1,G1)=3

È possibile aggiungere facilmente ulteriori riferimenti di cella alle formule, se necessario. Un tale approccio restituisce True solo in tre condizioni: se TUTTE le celle nell’intervallo B1: G1 contengono qualcosa, se TUTTE le celle dispari (B1, D1, F1) hanno qualcosa in esse e se TUTTE le celle pari (C1, E1, G1) hanno qualcosa in loro. Non restituirà True se solo alcune delle celle nell’intervallo contengono valori. Ad esempio, ci sono valori nelle celle B1, C1 ed E1, quindi non restituirà True e nessuno dei criteri per la formattazione sarà soddisfatto.

Sebbene funzionino tutti bene con la nota limitazione, non sono esattamente ciò che Zar sta cercando: vuole una formula che rilevi quante colonne vengono utilizzate settimana dopo settimana, mentre continua ad aggiungere dati alle colonne e ad aggiustare il formula di conseguenza senza la necessità di modificare manualmente la formula per tenere conto dei dati aggiunti. In altre parole, se aggiunge dati alla colonna H, vorrebbe che le formule venissero adattate automaticamente per tenere conto della colonna aggiunta:

=COUNTA(B1:H1)=7)

=COUNTA(B1,D1,F1,H1)=4 =COUNTA(C1,E1,G1)=3

Questa è ovviamente un’esigenza più complessa. Forse il modo migliore per affrontare il problema è creare una funzione definita dall’utente (una macro) che possa esaminare un intervallo di celle e determinare se uno dei tre criteri è soddisfatto.

Considera la seguente macro:

Function CellChk(crng As Range) As String     Dim iNumOdds As Integer     Dim iNumEvens As Integer     Dim iOdds As Integer     Dim iEvens As Integer     Dim iTots As Integer     Dim iTotCells As Integer     Dim rWork As Range     Dim rCell As Range     Dim iLastCol As Integer     Dim sTemp As String

iOdds = 0     iEvens = 0     iTots = 0

' Figure out the real last column in the worksheet and set range     iLastCol = ActiveSheet.Cells.Find(What:="*", _         SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, _         LookIn:=xlFormulas).Column     Set rWork = Range(Cells(crng.Row, 2), Cells(crng.Row, iLastCol))



iTotCells = rWork.Count     iNumOdds = (iTotCells + 1) \ 2    ' Number of odd columns     iNumEvens = iTotCells - iNumOdds  ' Number of even columns

For Each rCell In rWork         If rCell <> "" Then             If ((rCell.Column - 1) Mod 2) = 1 Then                 iOdds = iOdds + 1             Else                 iEvens = iEvens + 1             End If             iTots = iTots + 1         End If     Next rCell

sTemp = ""

If iTots = iTotCells Then         sTemp = "t"

ElseIf iOdds = iNumOdds Then         sTemp = "o"

ElseIf iEvens = iNumEvens Then         sTemp = "e"

End If     CellChk = sTemp End Function

Si utilizza la macro passandogli un indirizzo nella riga che si desidera controllare.

Quindi, ad esempio, se applicassi la regola di formattazione condizionale alla cella A3, passeresti alla macro un indirizzo B3 o C3, qualsiasi cosa tranne A3, poiché ciò causerà un riferimento circolare. La macro cerca l’ultima cella utilizzata in quella riga e quindi determina quante celle pari e dispari contengono qualcosa. La macro restituisce uno dei quattro valori; se il primo criterio è soddisfatto (tutte le celle nella riga che inizia con la colonna B contengono qualcosa), viene restituita una “t”. Se tutte le colonne dispari (dove B è la prima colonna dispari) contengono qualcosa, viene restituito “o”. Se tutte le colonne pari (dove C è la prima colonna pari)

hanno qualcosa in loro, quindi viene restituito “e”. Se nessuno dei tre criteri viene soddisfatto, la funzione non restituisce nulla.

Dovrai comunque impostare tre regole di formattazione condizionale che si basano sulla valutazione di una formula. Eccone tre che puoi usare con questa macro:

=CellChk(B1)="t")

=CellChk(B1)="o")

=CellChk(B1)="e")

Questi esempi servono per applicare un formato condizionale alla cella A1; regolare i riferimenti di cella alla riga corretta che si desidera venga analizzata dalla macro. Ricorda che anche se specifichi una singola cella (B1 in questi esempi), la macro calcola il numero di celle da guardare effettivamente nella riga.

_Nota: _

Se desideri sapere come utilizzare le macro descritte in questa pagina (o in qualsiasi altra pagina dei siti ExcelTips), ho preparato una pagina speciale che include informazioni utili.

Fare clic qui per aprire quella pagina speciale in una nuova scheda del browser.

ExcelTips è la tua fonte di formazione economica su Microsoft Excel.

Questo suggerimento (5945) si applica a Microsoft Excel 2007, 2010, 2013, 2016, 2019 e Excel in Office 365.