È soltanto un Pokémon con le armi o è un qualcosa di più? Vieni a parlarne su Award & Oscar!

Excel Forum Per condividere esperienze su Microsoft Excel

Formattazione condizionale celle sulla base di testo

  • Messaggi
  • OFFLINE
    Simone
    Post: 1
    Registrato il: 25/10/2021
    Età: 38
    Utente Junior
    2016
    00 25/10/2021 12:38
    Buongiorno a tutti,
    mi trovo davanti ad un problema forse banale ma che non riesco a risolvere da solo.
    In pratica h la necessità di colorare delle celle sulla base del testo contenuto in altre celle; provo a fare un esempio pratico:
    voglio che la cella A1 si colori come segue:

    - blu se contiene una parola presente nelle celle b1:b10
    - giallo se contiene una parola presente nelle celle C1:C10
    - rosso se contiene una parola presente nelle celle d1:d10

    e così via....

    Inoltre ho bisogno che la corrispondenza tra le celle sia parziale, ovvero se la parola che fa cambiare colore fosse LATTE, deve far cambiare colore alle celle contenenti LATTE INTERO, LATTE SCREMATO o PANINO AL LATTE.

    Fino ad ora non sono riuscito a trovare una regola, o formula da inserire nella formattazione condizionale che permetta di soddisfare tutte le condizioni.

    Mi affido a voi.

    Buona Giornata.
    Simone.
  • OFFLINE
    dodo47
    Post: 3.233
    Registrato il: 06/04/2013
    Utente Master
    2010
    00 25/10/2021 13:36
    ciao
    ti faccio un esempio solo per la col. B

    nella FC:

    =CONTA.SE($B$1:$B$10;"*"&$A$1&"*")>0 e dai colore blu

    saluti

    edit: l'unico problema è se trova "latte" in più colonne....verrà applicata l'ultima FC


    [Modificato da dodo47 25/10/2021 13:41]
    Domenico
    Win 10 - Excel 2016
  • OFFLINE
    Simone
    Post: 1
    Registrato il: 25/10/2021
    Età: 38
    Utente Junior
    2016
    00 25/10/2021 14:03
    Ciao,
    innanzitutto ti ringrazio per la risposta celerissima.
    Ho provato la formula che mi hai dato adattandola alle celle reali, ma in pratica mi colora tutta la selezione cui applico la FC, come da screen di seguito:


    Sicuramente sbaglio qualcosa, o magari ho spiegato male prima.
    Comunque sia l'obiettivo è che siano colorate solo le celle contenenti latte e latte intero.
    Fammi sapere.
  • OFFLINE
    Marius44
    Post: 948
    Registrato il: 24/06/2015
    Città: CATANIA
    Età: 80
    Utente Senior
    Excel2019
    00 25/10/2021 15:27
    Salve a tutti
    Ma non mi sembra che la formula da te inserita sia quella suggerita da @Dodo47 (ciao Domenico)
    Fra l'altro ti veniva precisato che il suggerimento era solo per la colonna B ("ti faccio un esempio solo per la col. B")

    Vorrei sbagliarmi ma credo che ci voglia VBA (con la FC ci sono troppe condizioni)

    Ciao,
    Mario
  • OFFLINE
    federico460
    Post: 2.472
    Registrato il: 10/10/2013
    Città: VICENZA
    Età: 69
    Utente Veteran
    365
    00 25/10/2021 15:57
    ciao
    prova questa


    =E($A1<>"";SOMMA(SE.ERRORE(RICERCA($A1;$X$1:$AK$1);0))>0)

  • OFFLINE
    Simone
    Post: 2
    Registrato il: 25/10/2021
    Età: 38
    Utente Junior
    2016
    00 25/10/2021 16:15
    Grazie mille,

    questa seconda formula funziona, solo che necessità della corrispondenza totale del testo nella cella condizionata.

    Ciò significa che se scrivo LATTE si colora, ma se scrivo LATTE SCREMATO no. Lo stesso se scrivo FORMAGGIO funziona, ma con FORMAGGIO GRATTUGGIATO no.

    E' possibile risolvere anche questo problema oppure devo fare un elenco preciso con ogni possibile combinazione esistente?

    Il problema è che l'elenco diventerebbe molto complesso ed anche un singolare o plurale o una digitazione imperfetta farebbe si che la FC non vada più.

    Comunque sia è già un enorme passo avanti.

  • OFFLINE
    federico460
    Post: 2.473
    Registrato il: 10/10/2013
    Città: VICENZA
    Età: 69
    Utente Veteran
    365
    00 25/10/2021 16:21
    ciao
    =E($A1<>"";SOMMA(SE.ERRORE(RICERCA("*"&$A1&"*";$X$1:$AK$1);0))>0)

    bastava leggere le due formule eh!😏
  • OFFLINE
    Simone
    Post: 3
    Registrato il: 25/10/2021
    Età: 38
    Utente Junior
    2016
    00 25/10/2021 16:48
    scusa l'ignoranza,
    ma anche così la corrispondenza continua ad essere completa, e non parziale.
    Allego un file prova per capire cosa sbaglio.

    Grazie ancora.
  • OFFLINE
    federico460
    Post: 2.474
    Registrato il: 10/10/2013
    Città: VICENZA
    Età: 69
    Utente Veteran
    365
    00 25/10/2021 18:34
    ciao
    è chiaro che latte
    lo trova sia in
    LATTE
    che in
    LATTE SCREMATO

    ma se scrivi latte scremato
    excel cerca tutte le parole non latte.

    cosa vuoi scorporare le parole e cercarle tutte e due?

    prova questa
    utilizza la prima parola
    =E($A1<>"";SOMMA(SE.ERRORE(RICERCA("*"&SE.ERRORE(SINISTRA($A1;TROVA(" ";$A1));$A1)&"*";$X$1:$AK$1);0))>0)
    [Modificato da federico460 25/10/2021 19:05]
  • OFFLINE
    dodo47
    Post: 3.234
    Registrato il: 06/04/2013
    Utente Master
    2010
    20 25/10/2021 19:20
    Re:
    Simone (3ilV211025), 25/10/2021 12:38:

    voglio che la cella A1 si colori come segue:

    - blu se contiene una parola presente nelle celle b1:b10
    - giallo se contiene una parola presente nelle celle C1:C10
    - rosso se contiene una parola presente nelle celle d1:d10



    ciao
    io ho risposto alla tua domanda...se poi tu cambi le carte in tavola è un altro paio di maniche

    saluti



    Domenico
    Win 10 - Excel 2016
  • OFFLINE
    Simone
    Post: 4
    Registrato il: 25/10/2021
    Età: 38
    Utente Junior
    2016
    00 26/10/2021 09:05
    Scusate tutti,
    io non ho cambiato carte in tavola e non mi lamento certo di soluzioni che fin qui non sono andate bene, anzi ringrazio tutti per il tempo dedicato.
    Mi piace utilizzare excel ma purtroppo ne ho una conoscenza abbastanza limitata appena usciamo dalle classiche formule, e per questo mi rivolgo a voi, sperando di imparare anche qualcosa.

    Siccome l'argomento è un po complesso probabilmente avrò esposto male la mia necessità. Cerco di riassumerla in maniera più chiara possibile rimandando anche il file per chiarezza:

    - la parola LATTE (contenuta in un elenco di "parole chiave") deve far colorare le celle contenenti LATTE in ogni sua combinazione (es. LATTE, LATTE SCREMATO e CREMA di LATTE)

    Questo perchè le combinazioni che utilizzano la parole LATTE nei vari fogli di lavoro che andranno generati, sono innumerevoli ed al momento non completamente prevedibili.

    Allo stato dei fatti la situazione è la seguente:
    - la formula =E($A1<>"";SOMMA(SE.ERRORE(RICERCA($A1;$X$1:$AK$1);0))>0)
    funziona solo nel caso di corrispondenza completa tra parola chiave e cella con FC

    - la formula =E($A1<>"";SOMMA(SE.ERRORE(RICERCA("*"&SE.ERRORE(SINISTRA($A1;TROVA(" ";$A1));$A1)&"*";$X$1:$AK$1);0))>0)
    funziona anche con per le celle che iniziano per LATTE (es. LATTE in POLVERE) ma non che la contengono in altri punti (es. CREMA di LATTE)

    Questo sarebbe un problema marginale perchè i casi non coperti sarebbero pochi.

    Il problema più grosso è che non riesco a fare funzionare questa seconda formula nel foglio di lavoro reale perchè l'elenco delle "parole chiave" sta nel foglio2, mentre le celle con FC nel foglio1.

    Se qualcuno avesse voglia di fare quest'ultimo passaggio, in allegato trova il file che andrò ad utilizzare.

    Dopo di ciò prometto che non vi disturberò più😅. Promesso.
  • OFFLINE
    Marius44
    Post: 949
    Registrato il: 24/06/2015
    Città: CATANIA
    Età: 80
    Utente Senior
    Excel2019
    00 26/10/2021 10:15
    Ciao a tutti
    Ribadisco che, forse, bisogna ricorrere al VBA.
    Ecco una macro che (ne tuo "Foglio prova" fa quello che chiedi.
    Inserisci la macro in un Modulo standard e associala ad un pulsante sul Foglio.
    Elimina la FC altrimenti le parole vengono colorate in blu (io ho messo un verde) 
    Option Explicit
Option Compare Text

Sub TrovaParola()
Dim rng As Range, testo As String, parola() As String
Dim rCell As Range, n As Long, i As Long, j As Long, ur As Long
Columns(1).Interior.ColorIndex = xlNone
Columns(1).Font.ColorIndex = 1
Set rng = Range("X1:AE1")
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
  testo = Cells(i, 1).Value
  parola = Split(testo, " ")
  For j = 0 To UBound(parola)
    For Each rCell In rng
      With rCell
        n = InStr(.Text, parola(j))
        If n > 0 Then
          Cells(i, 1).Interior.ColorIndex = 4
        End If
      End With
    Next
  Next j
Next i
Set rng = Nothing
End Sub


    Fai sapere. Ciao,
    Mario
    [Modificato da Marius44 26/10/2021 10:16]
  • OFFLINE
    federico460
    Post: 2.475
    Registrato il: 10/10/2013
    Città: VICENZA
    Età: 69
    Utente Veteran
    365
    00 26/10/2021 11:50
    ciao
    ma tu vuoi colorare gli allergeni
    della colonna A della distinta?

    cosa devi colorare che non si capisce

    secondo me stiamo lavorando al contrario
    la ricerca va fatta diversamente

    a questo punto
    usa il vba
    [Modificato da federico460 26/10/2021 11:53]
  • OFFLINE
    Simone
    Post: 5
    Registrato il: 25/10/2021
    Età: 38
    Utente Junior
    2016
    00 26/10/2021 11:55
    Si esattamente,
    devo colorare gli ingredienti della colonna A del foglio distinta se contengono gli allergeni elencati nelle varie colonne del foglio "allergeni".
    Ciao e grazie
  • OFFLINE
    federico460
    Post: 2.476
    Registrato il: 10/10/2013
    Città: VICENZA
    Età: 69
    Utente Veteran
    365
    00 26/10/2021 15:11
    ciao
    se metti tutti gli elementi
    sugli allergeni
    puoi usare
    =MAX(SE(Allergeni!$B$2:$I$20=$A16;RIF.COLONNA(Allergeni!$B$2:$I$20)))>0

    ma così come è scritto come fai a sapere se il pecorino è latte?
    CREMA di LATTE perchè non è nella lista allergeni

    excel non può sapere che il pecorino è fatto con il latte di pecora
    [Modificato da federico460 26/10/2021 15:14]
  • OFFLINE
    Simone
    Post: 6
    Registrato il: 25/10/2021
    Età: 38
    Utente Junior
    2016
    00 26/10/2021 15:34
    La parola pecorino è inserita nella lista allergeni "LATTE", quindi lo intercetta facilmente. Il problema si presenterebbe se scrivessi Pecorino Romano o Pecorino siciliano o grattuggiato, o Dop, o tutta una serie di qualificazioni che mi viene complicato prevedere, il tutto moltiplicato per ogni ingrediente utilizzato. Era questo il problema principale evidentemente non risolvibile con formule ma solo con VBA.


    Infatti la macro di Marius sul foglio prova funziona, solo che andrebbe adattata al foglio reale ed io non ci sono riuscito.
    Ma non voglio più rompere quindi vedrò di crearmi un elenco ingredienti quanto più completo possibile.

    Vi ringrazio tutti e alla prossima!!!
  • OFFLINE
    Marius44
    Post: 950
    Registrato il: 24/06/2015
    Città: CATANIA
    Età: 80
    Utente Senior
    Excel2019
    00 26/10/2021 15:58
    Ciao
    La macro che ti ho suggerito è stata concepita per il "Foglio Prova" ma può benissimo essere adattata al Foglio con gli allergeni e "leggere" tutte le righe di ogni colonna. Devi solo perderci un po' di tempo per costruire i cicli necessari.
    Non arrenderti! Prova e se non riesci .... chiedi aiuto.

    Ciao,
    Mario
  • OFFLINE
    Simone
    Post: 7
    Registrato il: 25/10/2021
    Età: 38
    Utente Junior
    2016
    00 26/10/2021 17:14
    Grazie della fiducia Mario,
    ma mio malgrado non ho basi di linguaggio VBA. Quindi quello che riesco ad adattare lo devo solo all'intuizione😂.
    Vediamo se in questa occasione riesco a farmi un po di cultura, approfittando della tua disponibilità:

    PROBLEMA 1: la macro andrà applicata al Foglio1 denominato "Distinta" anzichè al foglio prova. Va specificato, o lui in automatico applica la regola a tutti i fogli attivi?

    PROBLEMA 2: rispetto alla prova, nel caso reale le colonne su cui applicare la macro e le "parole chiave" stanno su 2 fogli diversi (Distinta, Allergeni). Come si identificano nella macro? Ho visto che nella tua specifichi solo il range senza foglio di provenienza.

    Come vedi sono proprio cose base dovute alla mancata conoscenza dello strumento.
  • OFFLINE
    Marius44
    Post: 951
    Registrato il: 24/06/2015
    Città: CATANIA
    Età: 80
    Utente Senior
    Excel2019
    00 26/10/2021 17:30
    Ciao
    Ti allego il file con la macro inserita nel modulo del Foglio("Distinta")
    Ogni dato scritto nella Distinta fa scattare il colore (uguale a quello della colonna dove lo ha trovato)
    questo il codice 
    Option Explicit
Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh1 As Worksheet, sh2 As Worksheet
Dim ur As Long, uc As Long, j As Long, k As Integer, n As Integer
Dim rng As Range, rCell As Range
Dim parola() As String
Set sh1 = Worksheets("Distinta")
Set sh2 = Worksheets("Allergeni")
uc = sh2.Cells(1, Columns.Count).End(xlToLeft).Column
If Not Intersect(Target, Sheets("Distinta").Range("A:A")) Is Nothing Then
  If Target.Cells.Rows.Count > 1 Then
    Stop 'occorre gestire la selezione multipla
         'e anche il caso di cancellazione di dato
  End If
  For j = 2 To uc
    ur = sh2.Cells(Rows.Count, j).End(xlUp).Row
    Set rng = sh2.Range(sh2.Cells(2, j), sh2.Cells(ur, j))
    parola = Split(Target, " ")
    For k = 0 To UBound(parola)
      For Each rCell In rng
        With rCell
          n = InStr(.Text, parola(k))
          If n > 0 Then
            Target.Interior.ColorIndex = sh2.Cells(1, j).Interior.ColorIndex
            GoTo xit
          End If
        End With
      Next
    Next k
  Next j
End If
xit:
Set sh1 = Nothing
Set sh2 = Nothing
End Sub

    Ci sarà da gestire eventuali errori di digitazione ma anche la cattura di errori (per esempio se cancelli un dato va in debug, come pure se non trova il dato fra quanto scritto nel Foglio("Allergeni"). Ma questo può venire dopo, magari studiando un po' come hai detto.

    Fai sapere. Ciao,
    Mario
  • OFFLINE
    Simone
    Post: 8
    Registrato il: 25/10/2021
    Età: 38
    Utente Junior
    2016
    00 26/10/2021 18:01
    Sei un grande!!!
    Hai ragione i debug ci sono e andranno gestiti.
    Magari un giorno ci riuscirò.
    Grazie di tutto.
  • 15MediaObject5,00220 2
Soluzioni Excel Domande e Risposte
Passa alla versione desktop

  • Anteprima