Da oltre un anno erano state annunciate alcune nuove prestazioni e funzioni di una certa rilevanza, disponibili inizialmente a chi partecipa a programmi di test prima delle diffusione generalizzata, e aspettavo con ansia che venissero rese disponibili sulle mie installazioni standard.
Oggi finalmente le ho trovate (devo dire che nell'ultimo mese potrei non aver mai controllate, insomma potrebbe anche essere un regalo di Natale di cui mi accorgo solo oggi) e ne voglio parlare insieme a voi
Quanto segue e' applicato nel file dimostrativo scaricabile qui: https://www.dropbox.com/s/1v2cp1e0m5aao ... .xlsx?dl=0
Tra le prestazioni che cambieranno il nostro modo di usare le formule, la piu' importante e' certamente il concetto di "matrice dinamica"
Esempio
-in Excel tradizionale, se vogliamo moltiplicare il contenuto di una colonna per il contenuto di un'altra useremo una formula del tipo:
- Codice: Seleziona tutto
=B3:B26*(C3:C26/60)
Da inserire nell'intera area di output (ad es F3:F26) e confermare con Contr-Maiusc-Enter (formula a matrice)
Se la formula fosse scritta "a matrice" ma in una sola cella avremmo come output solo la prima cella; se venisse scritta solo in F3:F13 allora solo queste in queste celle avremmo il risultato
-con le Matrici dinamiche, una formula spalmera' il suo risultato su tutta l'area che il risultato richiede. Quindi la formula puo' essere scritta analogamente:
- Codice: Seleziona tutto
=B3:B26*(C3:C26/60)
Qualora l'area non fosse tutta libera allora verra' visualizzato l'errore #ESPANSIONE; bastera' rimuovere (o spostare) il dato che occupa (abusivamente?) l'area per visualizzare tutti i risultati
La propagazione dei risultati avviene sia nelle righe sottostanti la cella contenente la formula che nelle colonne alla sua destra, a seconda di come si sviluppa la matrice del risultato.
Selezionando una delle celle contenenti il risultato si potra' vedere, nella barra della formula, la formula che l'ha prodotta, ma solo nella cella "madre" la formula puo' essere editata o cancellata; inoltre tutte le celle appartenenti allo stesso risultato appariranno contornate da una sottile linea continua.
Le novita' includono anche numerose nuove funzioni
1) CERCA.X
https://support.office.com/it-it/articl ... eae8bf5929
La funzione che mandera' in soffitta CERCA.VERT (e la sorella meno usata CERCA.ORIZZ)
Consente di indicare in quale intervallo cercare un valore e quale in intervallo prelevare il valore da restituire, con varie opzioni
Esempio:
Data la tabella in colonne A:C con punteggi e classifica di una gara, si vuole costruire il "podio"
Normalmente, non potendo usare Cerca.Vert (che vuole la colonna di ricerca sempre a sinistra del risultato da estrarre) si userebbe INDICE piu' CONFRONTA; es, in E29 e poi da copiare verso destra
- Codice: Seleziona tutto
=INDICE($A$30:$A$38;CONFRONTA(E30;$C$30:$C$38;0))
La nuova Cerca.X consente di scrivere (in I29 e poi da copiare verso destra)
- Codice: Seleziona tutto
=CERCA.X(I30;$C$30:$C$38;$A$30:$A$38)
Ho fatto un caso in cui Cerca.Vert non e' direttamente utilizzabile, ma anche usandola al posto di Cerca.Vert ha qualche vantaggio
Ad esempio, se voglio la posizione in classifica di Antonio posso usare la nota formula
- Codice: Seleziona tutto
=CERCA.VERT(A31;A30:C38;3;0)
Oppure potro usare
- Codice: Seleziona tutto
=CERCA.X(A31;A30:A38;C30:C38)
Poiche' CERCA.X richiede di conoscere solo la colonna di ricerca e solo la colonna del risultato, e non l'intera tabella, Excel dovra' utilizzare meno risorse per il calcolo del risultato. Ovviamente il vantaggio su una tabella 10*3 e' minimo, ma su una tabella 1000 righe * 25 colonne comincera' a essere tangibile.
Inoltre, se aggiungessi una colonna alla tabella, Cerca.Vert potrebbe richiedere la correzione del parametro "Indice", mentre Cerca.X fara' l'aggiustamento automatico.
Parametri opzionali.
La sintassi di Cerca.X:
- Codice: Seleziona tutto
=CERCA.X(valore;matrice_ricerca;matrice_restituita;[se_non_trovato];[modalità_confronto];[modalità_ricerca])
- Codice: Seleziona tutto
[se_non_trovato] consente di specificare un valore da visualizzare se la ricerca non produce risultato, invece del semplice #ND
[modalità_confronto] consente di specificare la modalita' di confronto; il valore di default e' 0=cerca valore esatto
[modalità_ricerca] consente di specificare se la ricerca avviene dall'alto (cerca il primo valore; impostazione di default) o dal basso (quindi cerca l'ultimo valore)
Se gli intervalli sono Orizzontali allora il comportamento sara' analogo a Cerca.Orizz
Una sottigliezza che apre ad usi "strani" di Cerca.X: questa funzione restituisce intrinsecamente un intervallo, non il suo valore.
2) DATI.ORDINA
https://support.office.com/it-it/articl ... 0e8e44b86c
Finalmente si possono Ordinare dei dati tramite una sola funzione
Partendo dalla stessa tabella dati usata per CERCA.X, costruire la tabella con la classifica ordinata e' questione di una sola formula:
Basta in N29:
- Codice: Seleziona tutto
=DATI.ORDINA(A29:C38;2;-1)
La sintassi della funzione:
- Codice: Seleziona tutto
=DATI.ORDINA (matrice,[sort_index],[sort_order],[by_col])
- Codice: Seleziona tutto
[sort_index] consente di indicare quale colonna sara' usata per determinare l'ordine. Se omesso si usera' la prima
[sort_order] consente di indicare se Crescente (1, valore di dafault) o Decrescente (-1)
[by_col] consente di indicare se si vuole orinare per righe (Falso, valore di default) o per colonne (Vero)
3) DATI.ORDINA.PER
https://support.office.com/it-it/articl ... a35134f28f
E' appena piu' sofisticata di DATI.ORDINA, perche' consente di indicare piu' colonne su cui basare l'ordinamento e per ognuna il tipo di ordinamento
La sintassi:
- Codice: Seleziona tutto
=DATI.ORDINA.PER (Array, by_array1, [sort_order1], [by_array2, sort_order2],...)
Esempio, data una tabella A1:F100:
- Codice: Seleziona tutto
=DATI.ORDINA.PER (A1:F100, C1:C100, 1, E1:E100, -1)
La tabella sara' ordinata per valori crescenti di colonna C e valori decrescenti di colonna E
Un vantaggio ulteriore di DATI.ORDINA.PER (rispetto a DATI.ORDINA) e' che in caso di eventuale inserimento o rimozione di colonne la formula si autoregola (DATI.ORDINA usa invece un Indice per indicare la colonna, quindi potrebbe risultare necessario correggere in caso di aggiunta /eliminazione di colonne)
4) MATR.CASUALE
https://support.office.com/it-it/articl ... 0a47fd4d33
Consente di creare una matrice di valori casuali, con varie opzioni
La sintassi:
- Codice: Seleziona tutto
=MATR.CASUALE([Righe],[Colonne],[min],[max],[numero_intero])
I parametri:
- Codice: Seleziona tutto
[Righe] Quante righe
[Colonne] Quante colonne
[min] Valore Minimo; default a 0
[max] Valore Massimo; default a 1
[numero_intero] Vero per ottenere valori Interi, Falso per ottenere valori decimali (valore di Default)
4a) Una bella combinazione: DATI.ORDINA.PER e MATR.CASUALE
Supponiamo di voler mixare in ordine casuale un elenco, allora di puo' usare DATI.ORDINA.PER e come by_array il risultato di MATR.CASUALE
Ad esempio, partendo dalla tabella usata per CERCA.X posso creare un elenco casuale di nominativi
Bastera' in V29
- Codice: Seleziona tutto
=DATI.ORDINA.PER(A30:A38;MATR.CASUALE(9))
5) UNICI
https://support.office.com/it-it/articl ... 204fb85e1e
Finalmente i valori unici estratti con una sola funzione
share imageData la tabella di colonne A:B, la nuova funzione UNICI consente di creare l'elenco delle voci uniche.
Bastera' in F43 la formula
- Codice: Seleziona tutto
=UNICI(A43:A56)
6) SEQUENZA
https://support.office.com/it-it/articl ... b78519ca90
Consente di creare strutture di dati tabellari
Forse e' di utilita' poco immediata, ma e' di grande utilita' per creare matrici di costanti, invece che scriverle nella forma {1.2.3.4.5}
Ad esempio puo' essere usata per creare rapidamente le intestazioni di riga e di colonna di una tabelle di riepilogo.
Es:
Le celle colorate sono le uniche con formula; in particolare, per la cella Gialla e Arancione, rispettivamente:
- Codice: Seleziona tutto
=TESTO(DATA(2020;SEQUENZA(1;12);1);"mmm")
- Codice: Seleziona tutto
=SEQUENZA(10;1;2015)
7) FILTRO
https://support.office.com/it-it/articl ... 77ad80c759
Finalmente si filtra un elenco con una funzione
Esempio, usando la stessa tabella utilizzata per la funzione UNICI
Accanto ai valori unici (colonna F, calcolata con la funzione UNICI) sono state calcolate le Qt complessive per ognuna delle voci (colonna G, calcolata con la funzione "tradizionale" SOMMA.SE). In colonna I a adiacenti vengono visualizzate, in orizzontale, quali qt unitarie sono state incluse nel calcolo del "complessivo", usando la funzione FILTRO. Poiche' la tabella filtrata e' verticale, per trasporre i dati in orizzontale ho usato la funzione tradizionale MATR.TRASPOSTA
La formula in I43 e poi copiata verso il basso:
- Codice: Seleziona tutto
=MATR.TRASPOSTA(FILTRO($B$43:$B$56;$A$43:$A$56=F43))
In colonne O-P-Q invece la stessa funzione FILTRO viene usata per restituire le sole voci della tabella filtrate per il contenuto di O43
La formula in P43:
- Codice: Seleziona tutto
=FILTRO(A43:B56;A43:A56=O43)
La sintassi
- Codice: Seleziona tutto
=FILTRO(Matrice,Criterio,[Se_Vuoto])
Volendo filtrare secondo piu' criteri bisogna moltiplicare le varie formule di Criterio. Esempio:
- Codice: Seleziona tutto
=FILTRO(A43:B56;(A43:A56=O43)*(B43:B56>1))
Filtrera' secondo la descrizione inserita in O43 e per Qt>1
Oppure e' possibile sommare le varie formule di Criterio, se si vuole filtrare per un Criterio o per l'altro; oppure creare criteri di filtro personalizzati usando una formula complessa che restituisca una matrice di criteri.
Per controllare se avete a disposizione queste nuove funzionalita', provate a scrivere una formula basata su CERCA.X: se la formula non compare nell'elenco che si crea man mano che digitate (o se vi restituisce #NOME) allora dovete aspettare o il prossimo aggiornamento (se avete Office 365) o il prossimo acquisto (se avete una licenza specifica di durata illimitata).
Da parte mia mi immagino la confusione che puo' nascere dall'uso di queste prestazioni in ambienti variegati, sul forum cerchero' di non menzionarli se non quando offrono soluzioni evidentemente piu' semplici.
Ciao
