Oggi ho una piccola “chicca” da condividere con voi che mi è venuta in mente dopo aver aperto l’ennesimo foglio di calcolo Excel e aver visto che l’uso di questo potente strumento si trasforma nel 90% dei casi in un blocco appunti, ignorando praticamente tutta la potenza di calcolo che lo strumento mette a disposizione.
Se non sei interessato quindi ad Excel, puoi anche abbandonare la tua lettura ;-) … o magari dedicarti a qualche altro post più interessante, che sono sicuro su questo blog non mancano.
La potenza di calcolo ignorata
Vi stupireste se vi dico che solo il 10% di coloro che utilizzano Microsoft Excel lo usano per il 50% delle sue capacità elaborative? Io personalmente non ne sono rimasto affatto colpito quando ho letto questa informazione su di un quotidiano inglese qualche tempo fa. Li per li non gli ho nemmeno dato peso alla cosa, ma effettivamente non posso che confermare questa “scoperta” alla luce del”ennesimo foglio di calcolo che ho aperto recentemente. In questo caso specifico, vi erano una serie di date, in 8 celle diverse, per circa 50 mila righe. La persona che aveva creato il foglio, nell’ignoranza della potenza di calcolo di Excel, aveva perso tempo a trascrivere a mano la data più piccola dopo essersi scorso le 8 x 50000 = 400 mila celle. Quanto pensate ci abbia messo? A spanne, 8 ore al giorno, 12 caffè come minimo, se ne saranno andati 5gg pieni a dir poco.
Ora, questo caso specifico era una attività di volontariato, quindi poco male. Ma immaginatevi una cosa simile in una azienda, dove la produttività è la chiave essenziale. Si possono perdere 5gg per una cosa del genere? La risposta è no, a meno che non si voglia rendere la vostra attività non profittevole.
Trovare il valore più piccolo
Il titolo del post può essere leggermente fuorviante, perchè a pensarci bene il metodo che sto per proporre è valido sia per il caso che ho analizzato, quindi date, che per qualsiasi altra formula analoga dove un valore minimo o massimo devono essere trovati.
Vi starete chiedendo perché mettere in piedi un post del genere, se per ricavare il valore minimo o massimo si possono usare le funzioni MIN o MAX di Excel.
La risposta è semplice, e ve la spiego con un caso pratico che ho costruito e che ci porteranno all’introduzione della matrice di formule.
Prendiamo l’esempio sopra illustrato e poniamoci l’obiettivo di trovare l’anno più piccolo.In un caso normale, qualcuno potrebbe dire “che ci vuole?“. Utilizzi la funzione MIN, clicchi su tutte le celle e il gioco è fatto.
SBAGLIATO!!
Poichè le date in Excel sono trattate come numeri seriali, cioè a partire dal numero 1 dal 1/1/1900, inserendo qualcosa tipo =MIN(B10;C10;D10;E10;F10) produrrà come risultato il numero 39268, ovvero la data 05/07/07 convertita. Questo succede se la cella di destinazione non è stata adeguatamente formattata per rappresentare una data, utilizzando il pannello di controllo previsto da Excel.
Questo passaggio sopra si potrebbe evitare, modificando la formula in questo modo =YEAR(MIN(B10;C10;D10;E10;F10)). Ma anche così abbiamo altri due problemi. Cosa succede se ad un certo punto una cella tra quelle da analizzare è vuota?
Sebbene Excel sia in grado di ignorare la cosa, e di restituire il risultato corretto, sull’angolo sinistro della cella della vostra formula vedrete apparire un triangolino verde che indica che qualcosa è andato potenzialmente storto. Se non siete troppo pignoli, potreste anche cliccare sulla cella e quindi dire “ignora errore”.
Ma io sono pignolo e questo certo non vi aiuterà a risolvere il secondo problema. Quello della celerità nell’inserire le informazioni.
Se guardate la formula sopra proposta, vi renderete prima o poi conto di un potenziale problema che deriva nel momento in cui il vostro foglio di calcolo diventerà più grande e dovreste magari spostare le celle con i vostri dati, o peggio il numero di celle da considerare diventerà più grande. Vero, scritta la formula una volta, scritta per sempre (a patto di aggiornare le altre celle). Ma perchè non rendersi la vita più semplice?
Questo senza contare che fintanto che una formula è semplice come quella sopra proposta, raccapezzarcisi è semplice, ma cosa succede quando si hanno n variabili e condizioni tutte sulla stessa linea? Quanto tempo occorrerebbe prima di venirne a capo e scrivere una formula senza errori?
Utilizzare un intervallo di celle …
Nella stragrande maggioranza delle volte che utilizzerete Excel, vi renderete conto di come gli intervalli di celle possano diventare i vostri più “cari” amici. Nella fattispecie, la formula sopra proposta si può trasformare in =MIN(YEAR(B1:F1)). Però ecco che sorge un nuovo problema. Nel momento in cui una cella sarà vuota, questa volta Excel non sarà tanto intransigente e vi restituirà un nuovo e più chiaro errore.
Quel #VALUE! rappresenta ne più ne meno lo stesso errore che Excel aveva mostrato nella situazione precedente (triangolino verde), solo che nel caso sopra - dato un insieme omogeneo di date - Excel ha tirato ad indovinare. Ora con un intervallo (che nei meccanismi interni di calcolo richiede molta più elaborazione) il foglio di calcolo non si può più permettere questo “lusso”.
E allora, come risolviamo la cosa?
Qualcuno (con conoscenze più approfondite di Excel a questo punto) potrebbe pensare di utilizzare la funzione ISBLANK, che permette di restituire un altro valore nel caso in cui il contenuto della cella analizzata sia vuota. La funzione potrebbe essere quella giusta e si potrebbe trasformare la formula in qualcosa tipo =MIN(IF(ISBLANK(YEAR(B4:F4));0;YEAR(B4:F4))) ma c’è un problema (Ancora?! - ebbene si). Ammettendo che la formula sopra funzioni (ma non funziona) quello zero darebbe luogo ad un numero minimo non contenuto nel nostro intervallo di date. Questo senza contare che stiamo lavorando con un intervallo di celle e una formula come quella sopra proposta non funzionerebbe immediatamente perchè gli interrogativi che Excel si dovrebbe porre sono diversi.
Per quanto la logica umana è assai più complessa di quella di un elaboratore di calcolo, non scordiamoci che i nostri computer in fin dei conti capiscono solo istruzioni veramente basilari. E come tali queste informazioni devono essere proposte. Bisogna quindi dire ad Excel di utilizzare si un intervallo di celle, ma di considerare la cella individualmente rispetto alla fase di calcolo e questo lo possiamo ottenere solo utilizzando una matrice di formule.
Di seguito la formula funzionante (che tiene in considerazione quanto finora esposto): {=MIN(IF(((IF(ISBLANK(YEAR(B9:F9)); 1900))>1900);YEAR(B9:F9)))}
La formula proposta ha ancora dei limiti, quali:
- Non considerare le celle vuote;
- Considera il valore minimo tra quelli proposti;
- Utilizza un intervallo di celle per evitare di complicarsi troppo la vita;
- Se l’intervallo di celle cambia, cambiando in un paio di posti si ottiene la nuova formula;
Come dicevo poc’anzi, quella sopra proposta è una formula che contiene una matrice, rappresentate da quelle parentesi graffe che vedete all’inizio e alla fine della formula.
Quelle parentesi non si possono semplicemente digitare, ma si ottengono automaticamente nel momento in cui si inserisce la matrice. Per confermare normalmente una formula si deve premere invio; per inserire una matrice, sempre stando nella barra della formula, si deve premere la combinazione di tasti CTRL + SHIFT + INVIO (o CMD + SHIFT + INVIO se siete su Mac).
Detto questo, mi congedo perchè ritengo che l’articolo sia già sufficientemente troppo lungo (anche se non necessariamente complicato da capire). Spero che lo abbiate trovato utile, e vi invito a lasciare commenti se ne avete.