In Google Sheets (ma anche in Excel) ci sono formule che messe in una cella possono popolare una intera colonna o anche più colonne, come FILTER() o SORT().
Altre formule operano normalmente solo su una cella ma è possibile renderle "formule array" inglobandole nella funzione ARRAYFORMULA().
La funzione ARRAYFORMULA()
Per esempio, la funzione RIF.RIGA() (ROW() nella versione inglese) riporta il numero di riga corrente, o quello dell'indirizzo passato come parametro. Inserendo =ROW(A2) in una cella otteniamo "2". Se si specifica un intervallo, tipo =ROW(A2:C5) si ottiene sempre e solo un "2". Ma se si scrive =ARRAYFORMULA(ROW(A2:C5)), la cella e quelle sottostanti mostreranno "2", "3", "4", "5".
ARRAYFORMULA si occupa di "sviluppare" l'array dato in ingresso e chiamare ciclicamente la funzione data, organizzando i risultati in un array che viene poi mostrato nelle celle (o può essere usato per "alimentare" altre funzioni).
Occorre dire che ARRAYFORMULA non opera con tutte le funzioni.
Le funzioni logiche tipo E() (AND() in inglese) o O() (OR in inglese) e simili se usate in una ARRAYFORMULA() impediscono il processo. È però possibile eseguire un AND usando il simbolo "*" e un OR con il simbolo "+", per esempio, la seguente formula non si svilupperà su più colonne:
=ARRAYFORMULA(SE(E(A2:A5 <> ""; B2:B5 <> ""); "caso 1"; "Caso 2"))
mentre questa sì:
=ARRAYFORMULA(SE((A2:A5 <> "") * (B2:B5 <> ""); "caso 1"; "Caso 2"))
Anche INDEX() non permette il funzionamento di ARRAYFORMULA.
In questo post ho cercato una soluzione al problema di cercare l'ultima occorrenza di un elemento all'interno di una colonna utilizzando ARRAYFORMULA(), per evitare di trascinare e duplicare la stessa formula su tutte le righe.
Ho trovato una soluzione, che fa uso di CERCA.VERT() che, diversamente da INDEX(), può operare in una ARRAYFORMULA.
Espressioni array (fa parte della soluzione)
Può essere utile considerare anche questo tipo di "espressione array": :
= {1; 2}
che visualizza "1" nella cella dove sta l'espressione e "2" nella cella sottostante.
Oppure
= {A2:C4}
che riporta nelle celle circostanti il contenuto dell'intervallo indicato.
Questa caratteristica diventa utile ad esempio se vogliamo inserire la formula in riga 1 e avere i dati dalla riga 2 in poi, in questo modo:
= {"Titolo colonna"; ARRAYFORMULA(SE(A2:A8 > 4; "A"; "B"))}
Questo permette di mostrare "Titolo colonna" in riga 1 e di vedere i dati dalla riga 2 in giù.
Ma attenzione: funziona solo se la formula riporta dati su una sola colonna. Vale a dire che la parte che sta nella riga 1 deve occupare lo stesso numero di colonne della parte che sta sotto.
= {"Titolo colonna"; {A2:C4}}
darebbe un errore perché abbiamo una sola colonna nella prima parte e tre colonne nella seconda parte.
Alla cosa si può rimediare per esempio cosi:
= {MATR.TRASPOSTA({"Titolo colonna1"; "TC2"; "TC3"}); {A2:C4}}
Occorre usare MATR.TRASPOSTA() (o TRANSPOSE() nella versione inglese) per "orizzontalizzare" i tre elementi.
Con queste premesse cerchiamo di affrontare il problema
===================================
[AGGIORNAMENTO UTILE] - Quando ho scritto il post ho usato espressioni tipo
TRASPOSE({TRASPOSE({1; 2; 3}); TRASPOSE({7; 8; 9})})
per mettere in verticale i due array e affiancarli orizzontalmente.
Allora non sapevo che gli elementi di un array possono essere composti sia con ";" che con "\" (nella versione inglese si usano ";" e ",").
Sia nella versione inglese che in quella italiana ";" dispone gli elementi in verticale mentre "\" o "," li dispone in orizzontale.
Dato che nelle formule inglesi si usa "," invece di ";", questa cosa porta ulteriore confusione perché all'interno di un array non bisogna sostituire "," con ";", altrimenti si "ruotano" gli array.
Sostituire i ";" con "\" all'interno di un array equivale quindi ad applicare MATR. TRASPOSTA() all'array stesso.
Ora che lo so basta scrivere
{{1; 2; 3} \ {7; 8; 9}}
Per ottenere lo stesso risultato. Nei casi mostrati più avanti ci si può avvalere di questa nuova conoscenza per riscrivere le formule in modo più compatto.
===================================
Cercare l'ultimo prezzo di acquisto
Supponiamo di avere un foglio dove annotiamo degli acquisti di diversi prodotti.
Uno stesso tipo di prodotto può essere stato acquistato più volte e ogni nuovo acquisto viene accodato al fondo della lista.
Vogliamo vedere quali prodotti abbiamo acquistato e quale è l'ultimo prezzo di acquisto.
In riga 1, ci sono i titoli delle colonne, in colonna A, dalla cella A2 in poi (range A2:A) troviamo i nomi dei prodotti e a fianco (B2:B) il prezzo di acquisto.
In A1 possiamo scrivere "Prodotto"
In B1 "Costo di acquisto"
In A2 e al di sotto elenchiamo alcuni prodotti, anche ripetendo dei valori (ci servirà perché dovremo cercare l'ultima occorrenza)
In B2 e sottostanti, indichiamo i prezzi di acquisto
Se i nuovi acquisti fossero inseriti in cima basterebbe trovare la prima occorrenza con CERCA.VERT() o CONFRONTA() + INDICE() avremmo già risolto.
Con la funzione UNIQUE() possiamo ottenere una lista di tutti i prodotti acquistati, presi una sola volta. Possiamo mettere in colonna C2 la formula:
= UNIQUE(A2:A)
Oppure, come abbiamo visto, mettiamo in colonna C1 questa:={"Prodotti unici"; UNIQUE(A2:A)}
Ora, in C2:C abbiamo la lista di prodotti presi una sola volta, e per ciascuno di questi vogliamo determinare l'ultimo prezzo di acquisto, indicandolo a fianco del nome del prodotto, nella colonna D.
Ci sono soluzioni, che vedremo, che risolvono il problema usando formule "a strascico" ma in realtà speriamo di poter ottenere i risultati utilizzando una unica formula posta in D1.
Cercando soluzioni in internet non ho trovato nulla ma alla fine vedremo che la cosa è fattibile.
Una soluzione intermedia sarebbe quella di appoggiarsi a delle colonne o a un foglio di servizio dove elencare dei dati temporanei, ma cercheremo di evitare anche questo.
Se occorresse individuare la prima occorrenza del prodotto, CERCA.VERT() (VLOOKUP()) sarebbe quello che fa al caso nostro, almeno se la colonna dalla quale recuperare il risultato è alla destra della colonna dei prodotti, come nel nostro caso.
Una soluzione più generale sarebbe usare CONFRONTA() e INDICE(), dove CONFRONTA() cerca la parola, determina la riga e la passa a INDICE() che usa la riga per prelevare un dato da una qualunque colonna del foglio. Ma questo se ci interessasse la prima occorrenza. Noi vogliamo l'ultima...
Non esiste un "Reverse VLookup" (CERCA.VERT() inverso)
Stranamente non esiste un "Reverse VLookup", che trova le cose dal fondo e non esiste neppure una funzione che capovolga il foglio, o delle colonne, in modo da permettere di usare il normale CERCA.VERT(). Almeno, non sono stato capace di trovare nulla di idoneo.
Se avessimo il numero di riga...
Se ci limitiamo alle formule a strascico, una volta determinata la riga dove si trova l'ultima occorrenza diventa facile recuperare l'elemento desiderato, usando INDICE(). Immaginiamo che la riga, per ogni voce della nostra colonna C, sia indicata in colonna E
=INDICE($A$2:$B; E2; 2)
Data la riga, indicata in E2, INDICE prende la seconda colonna del range A2:B, estraendo quindi dalla colonna B il prezzo di acquisto.Dato che la colonna A non ci interessa, potrebbe anche essere:
=INDICE($B$2:$B; E2; 1)
Invece di "E2" si può sostituire una delle formule indicate sotto (rimuovendo il "=" iniziale)Cercare la riga dell'ultima occorrenza con FILTER() e CONTA.SE()
=INDICE(FILTER(RIF.RIGA($A$2:$A); $A$2:$A = C2); CONTA.SE($A$2:$A; C2))
In questo caso RIF.RIGA() genera un array dei numeri di riga corrispondenti all'intervallo dato. FILTER genera un array con la riga in cui si trova ciascuna occorrenza del prodotto indicato in C2. CONTA.SE() conta quante occorrenze sono state trovate e permette a INDICE() estrae dall'array l'ultimo elemento, che corrisponde al numero di riga desiderato.Cercare la riga dell'ultima occorrenza con MAX() e "*"
Mentre normalmente ARRAYFORMULA() si usa per popolare più celle, questa formula genera un unico risultato, dopo avere eseguito un ciclo di operazioni. È un altro modo per trovare la riga dell'ultima occorrenza. Usando ARRAYFORMULA() le operazioni indicate vengono eseguite su tutto l'intervallo e per ogni volta che in colonna A si trova il valore indicato in C2 la funzione MAX determina quale è il massimo numero di riga trovato. Alla fine abbiamo la riga. Meglio specificare che l'espressione ($A$2:$A = C2) vale 1 quando è vera e 0 quando è falsa, per cui, moltiplicando per il numero di riga si ottiene 0 o il numero di riga.
Ma le precedenti formule vanno trascinate lungo la colonna e non possono (ancora) essere ridotte a una sola.
Capovolgere l'origine (senza aggiungere colonne)
Se mettiamo l'espressione in una cella, (poniamo D2) riempiremo tre colonne con i dati. Ma si può "semplicemente" utilizzare questa espressione, senza il segno "=" iniziale, per "alimentare" una formula più ampia che ci riporti ciò che stiamo cercando, vale a dire l'ultima occorrenza di cosa c'è in C2, in questo caso.
Useremo la parte:
per generare tre colonne (virtuali, che non compaiono sul foglio, ma vengono passate alla funzione di ricerca). La prima colonna sarà a colonna A capovolta, la seconda colonna sarà la B capovolta e la terza colonna sarà la colonna con i numeri di riga (generati da ROW()), dal più alto al più basso.
La colonna con i numeri di riga (virtuali) è stata messa "a destra" per lasciare a sinistra i dati che serviranno a CERCA.VERT().
Trovare il costo
Ottenere il numero di riga della ultima occorrenza
Si può anche inserire la formula nella riga dei titoli, in D1, inglobando la formula precedente in un array, dove il primo elemento è il titolo della colonna, seguito dalla formula
In questa formula abbiamo una funzione ARRAYFORMULA() esterna, posta nel foglio corrente, che opera sull'intervallo A5:A del foglio corrente.
All'interno di questa ARRAYFORMULA c'è una funzione SORT() che deve ordinare i dati presenti sul foglio FoglioOrigine, e opera sull'intervallo di dati A2:F. Inoltre a questo intervallo viene affiancato un vettore di RIF.RIGA() che deve essere dello stesso numero di elementi (righe) di A2:F di FoglioOrigine.
In un primo tempo avevo dubitato che la formula fosse utile tra fogli diversi, che potevano avere numero di righe diverso ma il problema era che non avevo messo gli intervalli giusti.
La seguente formula contiene una funzione SORT(), la quale opera su un array di dati di un altro foglio dentro a un'altra. Quella esterna usa un intervallo A5:A, che è relativo al foglio dove la formula risiede mentre l'ARRAYFORMULA interna usa un intervallo A2:A, relativo al foglio dove risiedono i dati
Con INDICE() si devono trascinare le formule, Con CERCA.VERT() si può usare ARRAYFORMULA()
Con la precedente formula, che fa uso di CERCA.VERT() possiamo compilare una intera colonna. Il limite di CERCA.VERT() è che può riportare solo dati da colonne che si trovano alla destra della colonna di ricerca.Oppure la formula può essere usata con la funzione INDICE() per recuperare il dato da una qualsiasi colonna della riga indicata. Ma mentre CERCA.VERT() può essere usato in ARRAYFORMULA(), INDICE() può solo essere usato in una formula trascinabile.
Il "-1" finale serve per compensare il fatto che la ricerca parte da A2 e non da A1.
Nessun commento:
Posta un commento
NOTA: Attualmente i commenti sono moderati, vale a dire che non compariranno immediatamente. Questo mi consente sia eliminare eventuale spam sia di leggere i messaggi e eventualmente rispondere.