Da Riccardo Auriemma 
Excel si è evoluto notevolmente negli ultimi dieci anni e ogni versione contiene soluzioni diverse a una serie di problemi.
Recentemente, stavo risolvendo un problema di data in una tabella. È necessaria una formula per trovare in modo condizionale la data più vicina che corrisponde ad altri due criteri da una tabella. La soluzione dipende dalla versione di Excel che sto utilizzando.
Negli ultimi dieci anni, Excel è cambiato radicalmente. Ora più che mai, la versione del software determina cosa puoi fare e quanto è facile la soluzione.
Viene mostrato il programma con cui sto lavorando Figura 01.
Ho bisogno di conoscere la prossima data di revisione per ogni progetto. Ciò significa che ho bisogno della data più vicina nella colonna D, poiché c’è una cella vuota nella colonna E.
Il mio rapporto viene visualizzato in Figura 02. Le colonne hanno quattro diverse soluzioni. La riga 1 contiene le versioni con cui funziona. Il grado 2 descrive la soluzione.
Tutte le formule provengono dal grado 4 in Figura 02 appare in Figura 03.
Tutte le formule della riga 4 sono state copiate. Dovrai fare riferimento a Figura 03 Spiego anche queste formule. Le formule sono ragionevolmente complesse e fornisco maggiori dettagli nel video di accompagnamento per questo articolo.
La formula della cella K9 non è stata ancora visualizzata e verrà finalizzata.
Per definire o restituire una cella vuota, puoi utilizzare le virgolette doppie insieme in questo modo: “”.
Tutte le versioni – array – colonna H.
Le formule di matrice sono più complesse della maggior parte delle formule. Le formule di matrice possono funzionare con gli intervalli allo stesso modo delle formule standard per le singole celle. È necessario utilizzare un set di tastiere speciale per immettere gli array.
Tenere premuti i tasti Ctrl e Maiusc, quindi premere il tasto Invio. Questo inserisce le parentesi (parentesi graffe) attorno alla formula. Puoi vederlo nella prima versione elencata in Figura 03.
Questa formula di matrice calcola prima la prima funzione SE. Questo confronta ogni cella nell’intervallo A2: A13 con il codice del progetto nella cella G4. Se una cella corrisponde al codice del progetto, passa alla funzione IF successiva e cerca nella riga corrispondente nell’intervallo E2: 13 di una cella vuota.
Se trova una cella vuota, restituisce la riga corrispondente da D2: D13. Se uno dei test è FALSO, viene restituita una cella vuota.
Insieme, le funzioni IF forniscono un intervallo di date e celle vuote per la funzione MIN (che restituisce il valore minimo) per la tua revisione. La funzione MIN ignora le celle vuote.
Le date rimanenti vengono confrontate per determinare la prima data (la più bassa). Questo tipo di calcolo che utilizza formule normali richiede l’esecuzione di più celle. La formula di matrice può eseguire tutti i calcoli in una cella.
Excel 2010 e versioni successive – raggruppamento – prima colonna
La funzione AGGREGATE ha la capacità insolita di ignorare le celle di errore. Questa capacità ci consente di creare un’unica formula che funziona come una formula di matrice ma non è una matrice.
La funzione AGGREGA è la stessa della funzione SUBTOTALE. Si specifica il calcolo da eseguire. Il numero “15” all’inizio definisce la funzione SMALL. La funzione SMALL è una versione flessibile della funzione MIN.
La funzione MIN restituisce il valore minimo in un intervallo. La funzione SMALL può trovare la più bassa o la seconda più bassa, specificando il numero di posizione: 1 trova la più bassa, 2 secondi la più bassa.
Il secondo argomento di AGGREGATE ha 6 e questo indica di ignorare gli errori.
In Excel, le date sono numeri. Ogni data ha un numero di serie principale. Nella funzione AGGREGATE, dividiamo l’intervallo di date per il risultato del calcolo condizionale. Questo account esamina due condizioni separate. Ecco come funziona questo calcolo.
Ogni cella nell’intervallo può restituire VERO o FALSO. Quando moltiplichiamo le due condizioni insieme, convertono TRUE in 1 e FALSE in 0. E il risultato di A2 verrà moltiplicato per il risultato di E2 e così via al di sotto degli intervalli.
In due condizioni, ci sono quattro possibilità e due possibili risultati, come mostrato in Figura 04.
Quando moltiplichi qualcosa per zero (FALSO), restituisce zero, come puoi vedere in Figura 04. L’unica volta che 1 ritorna è quando entrambe le condizioni sono TRUE.
Abbiamo diviso le date sui risultati per questo caso. Qualsiasi condizione che non corrisponde sarà zero. La divisione per zero restituisce un errore.
Abbiamo ordinato alla funzione AGGREGATE di ignorare gli errori. Quando entrambe le condizioni corrispondono, la data verrà divisa per 1, lasciandola invariata. Le date rimanenti corrisponderanno a entrambi i criteri. Viene analizzato dalla funzione SMALL per determinare la data minima.
Il numero 1 alla fine della funzione AGGREGATE indica alla funzione SMALL di restituire il valore di date più basso (meno recente).
È stata aggiunta la funzione SE.ERRORE per gestire l’errore generato quando non vengono soddisfatte le condizioni.
Ciò accade quando non sono presenti celle vuote per un codice di progetto specifico.
Excel 2019 e versioni successive – MINIFS – Soluzione preferita – Colonna J.
Proprio come SUMIFS consente il raggruppamento condizionale, MINIFS consente il calcolo minimo condizionale.
Il primo intervallo, D2: D13, è l’intervallo in cui si trova il limite inferiore.
Quindi associ l’intervallo di condizioni, A2: A13, con la condizione G4. Puoi aggiungere condizioni aggiungendo un altro intervallo di condizioni, E2: E13 e un’altra condizione, “”. Questi sono tutti separati da virgole.
Poiché questa funzionalità è stata creata per gestire i nostri requisiti, questa è la soluzione consigliata. Sfortunatamente, è disponibile solo in Excel 2019 e nella versione in abbonamento. Nota che esiste anche una funzione MAXIFS.
Edizione solo in abbonamento – Matrici dinamiche – Colonna K.
La versione in abbonamento di Excel è disponibile tramite Microsoft 365 (in precedenza Office 365). Questa versione riceve aggiornamenti regolari. Nel 2020 ha ricevuto l’aggiornamento Dynamic Arrays, di cui ho parlato in tre articoli separati a maggio, giugno e luglio 2020.
Le matrici dinamiche cambiano il modo in cui vengono calcolate le formule. Per le formule di matrice, non è più necessario utilizzare Ctrl + Maiusc + Invio per inserirle. Le formule nelle celle H4 e K4 sono identiche ad eccezione delle parentesi su entrambe le estremità.
Il calcolo nella versione in abbonamento funziona allo stesso modo della formula di matrice che ho descritto in precedenza. Non consiglio questa soluzione a matrice dinamica, perché abbiamo la funzionalità MINIFS specializzata disponibile nella versione in abbonamento.
Questa formula mostra che il nuovo motore di calcolo di Excel può gestire una formula a cella singola che funziona con condizioni basate su intervalli, non solo celle singole. L’ho incluso, quindi puoi vedere come possiamo adattarlo per gestire requisiti più impegnativi.
Supponiamo invece di trovare la prima data in base al codice del progetto, di voler trovare la prima data in base alla prima lettera del codice account nella colonna C. È possibile associare prefissi diversi a diverse categorie di account. Figura 05 Ha il rapporto. La formula è inclusa nel K9 in formato Figura 03. MINIFS non può gestire questo tipo di situazione.
Gli array dinamici consentono di manipolare intervalli con altre funzioni. SINISTRA estrae i caratteri dalla sinistra della cella. Possiamo usarlo per estrarre la prima lettera dell’intervallo della colonna di calcolo.
Possiamo sostituire la condizione del codice del progetto con una condizione che estrae la prima lettera da ogni cella nell’intervallo C2: C13. Questo viene quindi confrontato con la lettera nella cella J9 per restituire VERO o FALSO per ogni cella dell’intervallo.
Excel è progredito nel corso degli anni. Siamo passati da formule di matrice complesse che richiedevano un input da tastiera speciale a una funzione che funziona come un array e quindi a una funzione personalizzata creata per gestire calcoli condizionali minimi.
Infine, Excel ha formule in grado di gestire condizioni flessibili in intervalli utilizzando matrici dinamiche.
Ricordati di guardare il video che accompagna questo articolo, che approfondirà il funzionamento di queste formule.
Il video di accompagnamento e il file Excel andranno più in dettaglio per illustrare queste tecniche.
Neale Blackwood CPA gestisce A4 Accounting, fornisce formazione su Excel, webinar e servizi di consulenza. Le domande possono essere inviate a [email protected]