AS
Microsoft Excel
Advanced Tips
"Forse non tutti sanno che..."
Cerca vert senza #N/D
Quanto sono amate le formule CERCA.VERT e CERCA.ORIZZ ?? ...e quanto odiato il simbolo di errore quando la stringa ricercata non viene trovata?!
Una soluzione è nidificare la formula SE.ERRORE
La seguente formula, se non trova il valore cercato, restituisce più elegantemente una stringa vuota.
=SE.ERRORE(CERCA.VERT(cella;matrice;indice;FALSO);"")
Le formule matriciali
In alcuni casi sono l'unica soluzione valida ad effettuare operazioni dipendenti da condizioni complesse che diversamente richiederebbero l'utilizzo di un DBMS.
Disponibili fin dalle versioni storiche di Excel di mia memoria, ancora in molti non le conoscono. Unica precauzione: usate in modo massiccio possono risultare onerose per il sistema, quindi consiglio di non estendere inutilmente le aree di ricerca dei valori.
Nel seguente esempio immagino di voler sommare, per ogni venditore, il fatturato da novembre 2018 a febbraio 2019, suddiviso per categoria prodotto.
Cosa c'è da sapere:
  • Le formule matriciali si inseriscono con CTRL+MAIUSC+INVIO
    Per verificare che l'inserimento sia andato a buon fine, verifica nella barra che la formula sia contenuta fra parentesi graffe.
  • Condizioni multiple vanno racchiuse fra parentesi tonde, dove l'operatore logico "*" significa AND e "+" significa OR
=SOMMA(SE(($G4=$C$2:$C$20)*($E$2:$E$20=H$3)*(($A$2:$A$20=2018)*($B$2:$B$20>=11)+($A$2:$A$20=2019)*($B$2:$B$20<=2));$D$2:$D$20;0))
La formula qui sopra va letta così:
Somma
se
{(cella G4 "Rossi" è uguale a C2:C20 colonna "Venditore")
e
(cella H3 "Cat.A" è uguale a E2:E20 colonna "Categoria")
e
[(A2:A20 colonna "Anno" è uguale a 2018 e B2:B20 colonna "Mese" è maggiore o uguale a 11)
oppure
(A2:A20 colonna "Anno" è uguale a 2019 e B2:B20 colonna "Mese" è minore o uguale a 2)]}
D2:D20 colonna "Importo"
altrimenti
0
Un po' di VBA..
La seguente routine visualizza e nasconde una serie di fogli.
Public Sub SheetsVisibleNotVisible()
Dim Matrice_fogli As Variant
Dim DimensioneArray As Integer
Dim Indice As Integer
Matrice_fogli = Array("Sheet1", "Sheet2", "Sheet3")
DimensioneArray = UBound(Matrice_fogli, 1)
For Indice = 0 To DimensioneArray Step 1
Sheets(Matrice_fogli(Indice)).Visible = Not Sheets(Matrice_fogli(Indice)).Visible
Next
End Sub
Quanto sarebbe comoda una scorciatoia per fare l'incolla valori o altre attività ripetitive?
Ecco alcuni codici. Non rimane che scegliere lo shortcut dal menù "macro" oppure scaricare la mia cartella di macro personale.
Public Sub IncollaSpeciale()
On Error GoTo Err
Selection.PasteSpecial Paste:=xlPasteValues
Exit Sub
Err:
MsgBox ("si è verificato un errore")
Exit Sub
End Sub
Crea un nuovo foglio. Elimina il foglio attivo, se non è l'unico. Rinomina il foglio attivo.
Public Sub NewSheet()
Sheets.Add After:=Sheets(Sheets.Count)
End Sub
Public Sub DelSheet()
If Sheets.Count > 1 Then
ActiveSheet.Delete
Else
MsgBox ("La cartella contiene un solo foglio!")
End If
End Sub
Public Sub RenameSheet()
Dim i As Integer
Dim n As String
i = 1
n = InputBox("Rinomina foglio", "Nuovo nome:", ActiveSheet.Name)
While i < Sheets.Count
If n = Sheets(i).Name Then
MsgBox ("Esiste già un foglio con quel nome")
Exit Sub
End If
i = i + 1
Wend
If IsNull(n) = False And n <> "" Then
ActiveSheet.Name = n
End If
End Sub
Formato numero, formato valuta, formato percentuale.
Public Sub FormatoNumero()
Selection.NumberFormat = "#,##0"
End Sub
Public Sub FormatoValuta()
Selection.NumberFormat = "$ #,##0"
End Sub
Public Sub FormatoPercentuale()
Selection.Style = "Percent"
End Sub
Adatta colonne e blocca celle orizzontale.
Public Sub AdattaColonne()
Cells.EntireColumn.AutoFit
End Sub
Public Sub BloccaOrizz()
ActiveWindow.FreezePanes = False
'reset evtl blocchi precedenti
ActiveWindow.FreezePanes = True
End Sub
Questa funzione fa una sorta di pulizia della formattazione, eliminando eventuali bordi, allineamenti e colori.
Public Sub ResetBordiAllineamentoSfondo()
Cells.Borders.LineStyle = xlNone
Cells.HorizontalAlignment = xlGeneral
With Cells.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub
Un esempio di formattazione condizionale.
La macro colora l'intera riga della tabella in base al valore della colonna E:E.
Il file di esempio è scaricabile dal link qui sotto.
Scaricando il seguente file accettate che viene rilasciato così com'è senza alcun tipo di garanzia.
Public Sub EsempioFormattazione()
Dim V_row As Integer
Dim V_cut As Integer
Dim Color1, Color2 As Integer
V_row = 2
V_cut = Range("H2").Value
Color1 = Range("H3").Interior.ColorIndex
Color2 = Range("H4").Interior.ColorIndex
While Cells(V_row, 5).Value <> ""
If Cells(V_row, 5) > V_cut Then
Range(Cells(V_row, 1), Cells(V_row, 5)).Interior.ColorIndex = Color1
Else
Range(Cells(V_row, 1), Cells(V_row, 5)).Interior.ColorIndex = Color2
End If
V_row = V_row + 1
Wend
Exit Sub
End Sub
Public Sub ResetFormattazione()
Dim V_row As Integer
V_row = 2
While Cells(V_row, 5).Value <> ""
Range(Cells(V_row, 1), Cells(V_row, 5)).Interior.ColorIndex = 0
V_row = V_row + 1
Wend
Exit Sub
End Sub
Metto liberamente a disposizione per il download la mia macro personale XLSB (per Excel versione 2007 e successive).
Per visualizzare o nascondere il foglio usate il tasto funzione F5
Troverete una tabellina per gestire in modo comodo e veloce le scorciatoie.
Il file va salvato nella cartella XLSTART, posizionata a seconda delle versioni di Office.
Scaricando il seguente file accettate che viene rilasciato così com'è senza alcun tipo di garanzia.
Le funzioni Open, Print e Input: un esempio di lettura da e scrittura su un file di testo.
In questo caso poniamo che esista il file "prova.txt" nella stessa cartella del file Excel. I dati vengono scritti in una tabella a dimensione variabile che inizi dalla cella E1. Alla cella B1 viene affidata la variabile relativa al tipo di scrittura: accodamento o sovrascrittura. Associo a due pulsanti le subroutine SCRIVI_FILE_Click e LEGGI_FILE_Click. Per chiarezza, l'esempio è volutamente limitato ad un caso di semplice lettura e scrittura, tuttavia la funzionalità può risultare estremamente utile per rielaborazioni complesse di file numerosi.
Qui di seguito il print-screen ed il link del file usato come esempio.
Scaricando il seguente file accettate che viene rilasciato così com'è senza alcun tipo di garanzia.
Sub SCRIVI_FILE_Click()
On Error GoTo Err
Dim V_TipoScrittura, V_NomeFile, V_Output As String
V_TipoScrittura = Cells(2, 2).Value
V_NomeFile = ActiveWorkbook.Path & "\prova.txt"
V_Output = ""
ApriFile V_TipoScrittura, V_NomeFile
Dim V_r, V_c As Long
If V_TipoScrittura = 1 Then
V_r = 1
Else
V_r = 2
End If
V_c = 5
While Cells(V_r, V_c).Value <> ""
While Cells(V_r, V_c).Value <> ""
If V_Output = "" Then
V_Output = Cells(V_r, V_c).Value
Else
V_Output = V_Output & Chr(9) & Cells(V_r, V_c).Value
End If
V_c = V_c + 1
Wend
ScriviFile V_Output
V_Output = ""
V_c = 5
V_r = V_r + 1
Wend
ChiudiFile
MsgBox ("Scritte " & V_r - 1 & " righe!")
uscita:
Exit Sub
Err:
MsgBox Err.Description
Resume uscita
End Sub
 
Sub LEGGI_FILE_Click()
On Error GoTo Err
Dim V_NomeFile As String
V_NomeFile = ActiveWorkbook.Path & "\prova.txt"
ApriFile 3, V_NomeFile
LeggiFile 1, 5
ChiudiFile
uscita:
Exit Sub
Err:
MsgBox Err.Description
Resume uscita
End Sub
 
Function ApriFile(Tipo, NomeFile)
If Tipo = 1 Then
Open NomeFile For Output As #1 'apre per sovrascrivere
ElseIf Tipo = 2 Then
Open NomeFile For Append As #1 'apre per accodare
ElseIf Tipo = 3 Then
Open NomeFile For Input As #1 'apre per leggere
Else
MsgBox ("Scegliere sovrascrivi/accoda/leggi")
Exit Function
End If
End Function
 
Function ScriviFile(Output)
Print #1, Output
End Function
 
Function LeggiFile(initR, initC)
Dim V_r, V_c As Long
V_r = initR
V_c = initC
Dim temp1, temp2 As String
temp1 = ""
temp2 = ""
Do While Not EOF(1)
temp1 = Input(1, #1)
If temp1 = Chr(9) Then
V_c = V_c + 1
Cells(V_r, V_c).Value = temp2
temp2 = ""
ElseIf temp1 = Chr(13) Then
V_r = V_r + 1
V_c = initC
temp2 = ""
ElseIf temp1 <> Chr(10) Then
temp2 = temp2 & temp1
Cells(V_r, V_c).Value = temp2
End If
Loop
End Function
 
Function ChiudiFile()
Close #1
End Function