Formula con array?

Question

Avrei un problema relativo aduna formula inserita in un evento Worksheet_SelectionChange che mi cerca vero/falso in una lista di dati inseriti in una colonna di altro foglio:

=SE(E($A168>0;$B168>0;H168<>"CSI";H168<>"ACSI";H168<>"AICS";H168<>"ASC";H168<>"ACS";H168<>"ARCI";H168<>"ASI";H168<>"UNLAC";H168<>"FRA");CERCA.VERT($E168;Categorie!$B:$G;4;0);"")

E' possibile evitare di mettere tutti questi dati da cercare magari con una possibile array?

Vi ringrazio

Answer 1

Per ottenere lo stesso risultato, a meno di altre soluzioni che non conosco, occorre effettuare un ciclo (la qual cosa potrebbe rallentare un po' la procedura). Eventualmente oltre a disabilitare lo screenupdating potresti impostare il Calculation in manuale e poi reimpostarlo in automatico alla fine.

Dopo Private Sub Worksheet_SelectionChange(ByVal Target As Range) inserisci queste due dichiarazioni:

  Const strCond As String = "{""CSI"",""ACSI"",""AICS"",""ASC""}" '<--- da implementare con le altre condizioni

  Dim rng As Range

al posto di Range("M2:M" & Colonna1).FormulaLocal = "=SE(E($A2>0;$B2>0;H2<>""CSI"";H2<>""ACSI"";H2<>""AICS"";H2<>""ASC"";H2<>""ACS"";H2<>""ARCI"";H2<>""ASI"";H2<>""UNLAC"";H2<>""FRA"");CERCA.VERT($e2;Categorie!$B:$G;4;0);"""")"

Sostituisci con:

  For Each rng In Range("M2:M" & Colonna1)

    rng.FormulaArray = _

    "=IF(AND(RC1>0,RC2>0,RC[-5]<>" & _

    strCond & "),VLOOKUP(RC5,Categorie!C2:C7,4,0),"""")"

  Next rng

queste sono le formule che ottengo:

M2--> {=SE(E($A2>0;$B2>0;H2<>{"CSI";"ACSI";"AICS";"ASC"});CERCA.VERT($E2;Categorie!$B:$G;4;0);"")}

M3--> {=SE(E($A3>0;$B3>0;H3<>{"CSI";"ACSI";"AICS";"ASC"});CERCA.VERT($E3;Categorie!$B:$G;4;0);"")}

M4--> {=SE(E($A4>0;$B4>0;H4<>{"CSI";"ACSI";"AICS";"ASC"});CERCA.VERT($E4;Categorie!$B:$G;4;0);"")}

....

Fai una prova.

ciao

Answer 2

Ciao Norman,

il problema (il fatto preminente) è che Mauro vuole inserire una formula, quale che essa sia, tramite VBA senza dover fare "copia/incolla" della stessa dalla cella o intervallo di celle.

Cioé anche la tua formula

=SE(E($A168>0; VAL.ERRORE(CONFRONTA(H168;TabellaVietati[#Data];0)));CERCA.VERT($E168;Categorie!$B1:$G1000;4;0);"")

o

=SE(E($A168>0; VAL.ERRORE(CONFRONTA(H168;TabellaVietati[#Data];0)));CERCA.VERT($E168;TabellaRicerca;4;0);"")

poi andrebbe, secondo quelle che credo di aver inteso essere l'intezione di Mauro, in "automatico" tramite ad es.

Activece.FormulaR1C1 (nel suo caso ha utilizzato FormulaLocal) ="testo della formula" che nel tuo caso sarebbe una delle due da te proposte.

In pratica lui non vuole creare una FDU ma inserire una formula, da modificare eventualmente in maniera semplice nelle sue condizioni inserite in VBA.

Poi quale sia la formula più appropriata è da decidere (e non metto in dubbio che le tue proposte siano più efficienti di quella matriciale proposta da me) :-)

Io avevo proposto una soluzione iniziale che, come fai tu in qualche modo, impostasse un elenco di nomi in un range nominato e poi utilizzasse una funzione matriciale. E poi da VBA inserire la formula matriciale con riferimento al nome dell'intervallo.

Poi visto che tanto quella funzione va inserita tramite VBA nel foglio gli ho proposto di scrivere nel VBA l'elenco dei nomi "vietati".

Non so se sono riuscito a spiegarmi.

ciao

Answer 3

Ciao Mauro, ciao Casanamer,

Chiedo scusa se mi ri-intrometto in questo thread ma, pur essendo io un grande proponente dell'uso giudizioso de VBA, ritengo sia ancora valida la mia asserzione iniziale:

Detto questo, se si tratti di una sola formula, mi pare poco probabile che questa sia meno efficiente che una soluzione VBA e, avendo già ottenuta una formula adatta, non vedo il motivo per cercare un'alternativa soluzione.

Una formula ha i meriti di essere molto semplice da implementare e molto veloce in termini di tempi di risposta. Inoltre, con l'uso di una formula, si evita la necessità di salvare il file in formato xlsm e non si perde la possibilità di annullare le azioni precedenti. Io amo VBA, ma io non lo userei in questo caso - a meno che io non abbia trascurato un fatto pertinente!. A mio avviso, il fatto che un approccio sia fattibile non lo rende automaticamente consigliabile e io vorrei approfondire le formule prima di fare ricorso a VBA.

Nel caso della tua formula, io creerei una tabella  con un intervallo dinamico (magari una tabella Excel, forse su un foglio nascosto) di una sola colonna e riempito con i valori vietati. Poi, modificherei la tua formula con qualcosa del genere:

=SE(E($A168>0; VAL.ERRORE(CONFRONTA(H168;TabellaVietati[#Data];0)));CERCA.VERT($E168;Categorie!$B1:$G1000;4;0);"")

Oppure, creando il nome definito TabellaRicerca per l'intervallo di ricerca sul foglio Categorie:

=SE(E($A168>0; VAL.ERRORE(CONFRONTA(H168;TabellaVietati[#Data];0)));CERCA.VERT($E168;TabellaRicerca;4;0);"")

A titolo di esempio banale, potresti scaricare il mio file di prova Mauro20160717.xlsx a:

https://www.dropbox.com/s/s0cy1y532myoqv7/Mauro20160717.xlsx?dl=0

===

Regards,

Norman

Answer 4

Supponevo  che ci sarebbero stati dei rallenamenti rispetto alla situazione precedente.

Come dicevo potresti all'inizio, dopo le due dichiarazioni e prima di iniziare la cancellazione dei dati inserire:

  With Application

    .ScreenUpdating = False

    .Calculation = xlCalculationManual

    '.EnableEvents = False

  End With

e prima di End Sub inserire

  With Application

    .ScreenUpdating = True

    .Calculation = xlCalculationAutomatic

    '.EnableEvents = True

  End With

Nota come l'azione su EnableEvents è disabilitata tramite '.

Potresti attivarla (sia quando viene impostata in False e di conseguenza dove riviene impostata in True) nel caso in cui ci siano altri eventi azionati nel foglio (es. Worksheet_Change) e mentre vengono effettuate le operazioni dell'evento Worksheet_SelectionChange non sia necessario che vengano eseguiti.

Answer 5

Eh si, funziona perfettamente. Magnifico !

Adesso provo anche a cercare di velocizzare la procedura che con il ciclo si è rallentata,

Grazie mille.

Ciao da mauro