Gestire le cartelle della tombola in Excel

Certo che per inserire questo articolo nella sezione dell’Office Automation ci vuole coraggio ma in fin dei conti si tratta sempre di Excel.
In queste vacanze ho giocato a tombola con la mia famiglia; ovviamente non ho vinto nulla ma mi sono divertito ugualmente. Un geek malato di informatica come me non poteva escludere il computer in una occasione come questa.
Ovviamente al posto di usare le lenticchie, le bucce d’arancia o altri segnaposto per tenere traccia dei numeri estratti ho decisao di usare un foglio di calcolo.
Per dirla tutta ho usato OpenOffice ma la sostanza non cambia dal momento che ho fatto uso delle formule standard.

In pratica sono bastati un paio di cerca.vert per tenere traccia dei numeri estratti e delle eventuali vincite.


Per chi fosse interessato, alla fine di questo articolo è possibile scaricare sia la versione xls che ods.

Il file si compone di tre fogli:

  • Estrazioni – E’ una sorta di deshboard in cui visualizzare lo stato delle cartelle ed i numeri che di volta in volta vengono estratti. Man mano che si procede con l’estrazione è possibile vedere cosa succede su ogni cartella: dall’ambo alla tombola;
  • Cartelle – Contiene i dati relativi a tutte le cartelle possedute. In particolare contiene i numeri così come sono riportate sulla cartella originale. E’ qui che l’utente deve inserire i numeri appartenenti alle cartelle in tre righe da cinque collonne ciascuna;
  • Vincita – Contiene un pannello che consente di calcolare facilmente le quote da assegnare alla tombola, alla cinquina e così via fino all’ambo. Il calcolo tiene conto del costo di ogni singola cartella e del numero di cartelle assegnate (comprensive delle sei cartelle che costituiscono il tabellone);

Si tratta di certo di un prodotto migliorabile ma sinceramente io non sono andato oltre dal momento che si tratta di un lavoro svolto in mezz’ora durante le vacanze di natale.

per scaricare il file cliccate qui.

alla prossima.

Excel – Totali in grassetto con il minimo sforzo

La scorsa settimana sono stato coinvolto in un progetto molto impegnativo legato al costo del prodotto di Fiat Group Automobiles (FGA), nell’ambito del quale era necessario simulare la chiusura di fine mese di alcuni stabilimenti.

Come sempre accade in questi casi, Excel viene impiegato sopratutto per lo scambio di informazioni e per la generazione di report.

Poiché gli stabilimenti del gruppo Fiat sono veramente tanti e per ognuno di essi è stato necessario produrre un numero consistente di documenti, mi sono reso conto subito che una cospicua parte del tempo era destinata alla formattazione dei fogli lasciando poco tempo alle attività di controllo.

In particolare un mio collega mi ha chiesto se fosse possibile mettere in grassetto le righe dei totali in modo facile e veloce.
Ho pensato subito alla formattazione condizionale ma c’era un limite da superare; le condizioni di formattazione per una cella vengono calcolate sul valore della cella stessa.

La formattazione condizionale
Per mezzo della formattazione condizionale è possibile personalizzare lo stile di una cella in base al valore che contiene. Ad esempio è possibile visualizzare un numero in rosso quando è negativo ovvero in verde se positivo.
Nella figura riportata in basso è possibile vedere un esempio:

Nell’esempio riportato in precedenza, il carattere della cella diventa verde se il valore contenuto è maggiore di 0 altrimenti resta del valore impostato nella formattazione originaria.
E’ possibile specificare più condizioni contemporaneamente è precisamente un massimo di tre.
Formattare le celle in questo modo è utile quando i dati sono calcolati, perchè non c’è bisogno di modificare a mano il formato. Purtroppo però, alla base della condizione è possibile specificare solo il valore ovvero la formula contenuta nella cella.

Il Conto Economico
Nel mio caso, il report del conto economico era simile a quello indicato nella figura in basso. Ovviamente i valori inseriti sono fittizi ma il layout si avvicina molto all’originale.

L’idea è quella di copiare in tutte le celle del foglio, una formattazione condizionale che imposti il formato grassetto in base alla seguente regola:

La cella è da riportare in grassetto se sulla stessa riga, in corrispondenza della colonna D, il valore inizia per ‘TOT’

Testare condizioni su celle diverse
Come dicevo prima però, la condizione può essere testata solo sul contenuto della cella stessa, di conseguenza è necessario implementare una formula che restituisca il valore della cella testata in caso di esito negativo. Se ad esempio la cella da testare è A1, allora la formula potrebbe essere:

=A1+SE(SINISTRA($D1;3)=”TOT”;0;1)



Se i primi tre caratteri della cella D1 contengono TOT, allora la formula restituirà A1 altrimenti restituirà A1+1. In questo modo posso utilizzare la formattazione condizionale impostando il parametro “uguale a“.
E’ possibile trascinare la formattazione della cella A1 in qualunque cella (notare il dollaro che precede la lettera D).
Esiste però un problema con le celle che non contengono un valore numerico poichè in questo caso non è possibile fare una somma.

In effetti per i valori testuali dovrei modificare la formula nel modo seguente:

=A1 & SE(SINISTRA($D1;3)=”TOT”;”";”1″)



Al posto di una somma verrebbe fatta una concatenazione ottenendo lo stesso risultato.
Non è però conveniente utilizzare due formattazioni condizionali differenti perchè bisognerebbe sempre controllare il valore contenuto nelle celle. Per questo motivo, la formula definitiva controlla il tipo di dati contenuti nella cella ed applica la somma o la concatenazione di conseguenza:

=SE(TIPO(A1)=2;A1 & SE(SINISTRA($D1;3)=”TOT”;”";”1″);A1 + SE(SINISTRA($D1;3)=”TOT”;0;1))




Ora è sufficiente selezionare l’intero foglio e copiare la fomattazione della cella A1.

Automatizzare il tutto
Per automatizzare il tutto creiamo una piccola macro in Visual Basic:

Sub TotaliGrassetto()
    Application.ScreenUpdating = False
    Cells.Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:=”= SE (TIPO(A1) = 2; A1 & SE(SINISTRA($D1;3) = “”TOT”"; “”"”; “”1″”);A1 + SE(SINISTRA($D1;3) = “”TOT”"; 0 ; 1 ))”
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = False
    End With
    Range(“A1″).Select
    Application.ScreenUpdating = True
End Sub




Creazione di una add-in MS-Excel
Per rendere le operazioni più veloci è possibile includere la procedure appena descritta in file di tipo xla pronta per essere importata mediante i componenti aggiuntivi di Excel.
Utilizzare una add-in è comodo altrimenti bisognerebbe incollare tutto nello stesso file.

Conclusioni
Al fondo di questo articolo ho inserito il link ai sorgenti.
Nel sorgente trovate anche una procedura che crea un menù per richiamare la macro (avevamo detto che bisogna essere veloci e così ho inserito anche una shortcut da tastiera [CTRL-ALT-T]).

Sorgenti
totali_grassetto.zip

Versioning di un file PPT

Ogni società di consulenza trasmette la propria visual identity tramite alcuni strumenti tra cui le presentazioni.
Nel mio ambiente di lavoro, l’imprinting  è così forte che riuscirei a riconoscere la presentazione di un mio collega fra mille anche se non presenta il logo della società.

Il Template utilizzato per la creazione delle slides in MS-Power Point deve presentare, in alto a destra, il nome del file che a sua volta contiene la versione del documento. Questo consente di controllare le revisioni della presentazione anche in forma cartacea.

E qui viene il bello. Ogni volta che cambia la versione oppure il nome del file bisogna cambiare l’apposita etichetta della diapositiva.

Detto questo, di seguito trovate la mia soluzione al problema.
Poche righe di visual basic che, tramite una macro, modificano una etichetta (nell’esempio è myText) sostituendone il contenuto con il nome del file PPT.

Sub naming()
  Dim myViewType As PpViewType

  myViewType = ActiveWindow.ViewType
  ActiveWindow.ViewType = ppViewSlideMaster
  ActivePresentation.SlideMaster.Shapes(“myText”).Select
  ActiveWindow.Selection.ShapeRange.TextFrame.TextRange.Select
  ActiveWindow.Selection.ShapeRange.TextFrame.TextRange.Characters(Start:=1, Length:=Len(ActiveWindow.Selection.TextRange.Text)).Select
  ActiveWindow.Selection.TextRange.Text = ActivePresentation.Name
  ActiveWindow.Selection.Unselect
  ActiveWindow.ViewType = myViewType
End Sub

Excel e collegamenti a fogli esterni

Quante volte vi è capitato di dover ripristinare i collegamenti a file esterni perchè avete spostato/modificato i vostri file xls?
A me capita spesso se considerate che implemento i fogli sul mio pc e poi li invio in posta elettronica al cliente.
Sono stufo di sentirmi dire sempre che i link non funzionano.
Ho deciso di risolvere il problema alla radice.
L’idea è quella di mettere tutti i files in una cartella e aggiornare i link all’avvio del workbook.

Option Explicit

Sub AggiornaLink()
‘ Per ogni link contenuto nel workbook
‘ Sostituiamo il path del file sorgente
‘ Con quello del workbook corrente

Dim aLinks() As Variant
Dim i As Integer
Dim aPath() As String

aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then
  For i = 1 To UBound(aLinks)
    aPath = Split(aLinks(i), “\”)
    ActiveWorkbook.ChangeLink Name:= _
    aLinks(i), NewName:= _
    ActiveWorkbook.Path & “\” & aPath(UBound(aPath)), Type:=xlExcelLinks
  Next i
End If
End Sub

Relink di tabelle pivot da sorgente MS-Access

La scorsa settimana mi è capitato di dover gestire l’ennesimo problema relativo ad i link di MS-Excel.
Un mio collega ha generato una tabella pivot contenente dati provenienti da una query di MS-Access.
Questa pivot viene successivamente utilizzata per alimentare alcuni grafici di Excel ed alcune slides in Power Point.
Basta aggiornare di volta in volta i dati nel file mdb per aggiornare a cascata anche i grafici e le slides.
C’è un solo vincolo a tutto ciò. E’ necessario che i files restino sempre nella stessa posizione pena la perdita del collegamento dei dati sorgenti nella tabella pivot.
Purtroppo, dopo aver fatto il rilascio dell’applicativo presso il cliente non è stato semplice mettere a posto il link al file mdb.

Come risolvere agevolmente il problema?

La prima idea che mi è venuta in mente è stata quella di utilizzare un percorso assoluto disponibile in tutti i sistemi Windows like (es: c:\).
Non è una soluzione elegante ma di sicuro è veloce.
Ho provato ad impiegare un DSN su file contenente tutte le informazioni necessarie per un collegamento alla tabella tramite ODBC.
Non ha funzionato perchè una volta creato il link, Excel prende tutte le informazioni e le conserva nel file. Di fatto modificare i parametri contenuti nel file DSN per aggiornare il link non ha senso dal momento che il file DSN non verrà più interrogato da Excel.
A questo punto ho deciso di mettere mano al codice e di risolvere una volta per tutte questo problema.

La soluzione proposta:
Di seguito una piccola sub che può essere applicata a qualunque pivot, per consentire l’aggiornamento dei link.
L’unico presupposto per il corretto funzionamento del codice è che tutti i files (xls ed mdb) si trovino nella stessa directory.
Sbirciando il codice noterete quanto sia grezzo il sistema con cui viene risolto il problema ma quando il tempo a disposizione è poco ci si accontenta anche di questo.

Option Explicit

‘ Questa funzione prende in input un worksheet e
‘ per ogni pivot in essa contenuta controlla che
‘ il link sia valido. In caso negativo sostituisce il
‘ link con il percorso del file corrente.

Public Sub PivotUpdate(ByRef ws As Worksheet)
  Dim pt As PivotTable
  Dim OldPath As String, NewPath As String
  Dim aPath() As String
  Dim intStart As Integer
  Dim IntStop As Integer
    
For Each pt In ws.PivotTables

  ’ Un modo bizzarro per controllare la correttezza del
  ’ link. In caso di errore, estriamo dalla descrizione
  ’ del problema il nome del file da linkare e sostiuiamo
  ’ il path con quello del file xls.
  
  On Error Resume Next
  pt.PivotCache.Refresh
  If Err <> 0 Then
    intStart = InStr(1, Err.Description, “”"”)
    IntStop = (InStr(intStart + 1, Err.Description, “”"”))
    aPath = Split(Mid(Err.Description, intStart + 1, IntStop – intStart – 1), “\”)
    OldPath = LCase(Replace(Mid(Err.Description, intStart + 1, IntStop – intStart – 1), “\” & aPath(UBound(aPath)), “”))
    NewPath = LCase(ActiveWorkbook.Path)
    Err.Clear
    On Error GoTo 0
    
    ’Da questo punto in avanti, se c’è un problema è meglio
    ’Segnalarlo all’utente.
    
    pt.PivotCache.Connection = Replace(LCase(pt.PivotCache.Connection), OldPath, NewPath)
    pt.PivotCache.CommandText = Replace(LCase(pt.PivotCache.CommandText), OldPath, NewPath)
    pt.PivotCache.Refresh
  End If
Next pt
End Sub

Per aggiornare una pivot all’avvio del workbook è sufficiente richiamare la subroutine:

Private Sub Workbook_Open()
  PivotUpdate Worksheets(“MySheet”)
End Sub