Condividi:        

Excel, NUOVE PRESTAZIONI

Vuoi potenziare i tuoi documenti Word? Non sai come si fa una macro in Excel? Devi creare una presentazione in PowerPoint?
Oppure sei passato a OpenOffice e non sei sicuro di come lavorare al meglio?

Moderatori: Anthony47, Flash30005

Excel, NUOVE PRESTAZIONI

Postdi Anthony47 » 27/01/20 02:06

Da alcuni anni non acquisto piu' licenze una-tantum (versione fissa, durata illimitata) ma la licenza Office 365 (versione sempre aggiornata, durata annuale). Questa opzione ha il vantaggio di mettere a disposizione degli utenti nuove prestazioni, che intanto vengono inserite nelle versioni piu' nuove.
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)
Inserita in H3 e confermata col solo Enter i risultati verranno pero' scritti in H3:H26 perche' questa e' la matrice di dati prodotta.
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:
Immagine
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:
Immagine

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
Immagine

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
Immagineshare image

Data 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:
Immagine

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
Immagine

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
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Sponsor
 

Re: Excel, NUOVE PRESTAZIONI

Postdi zsadist » 29/01/20 10:03

Grazie :)
Adattati! L'incapacità di cambiare direzione porta alla sconfitta
zsadist
Utente Junior
 
Post: 96
Iscritto il: 04/04/19 13:48

Re: Excel, NUOVE PRESTAZIONI

Postdi Anthony47 » 25/08/21 14:26

In quest'altra discussione presento la Funzione LET: viewtopic.php?f=26&t=112111
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea


Torna a Applicazioni Office Windows


Topic correlati a "Excel, NUOVE PRESTAZIONI":


Chi c’è in linea

Visitano il forum: Nessuno e 129 ospiti