La nostra formula preferita è INDICE. Ecco come Microsoft lo ha cambiato nel 2020 con gli array dinamici

Questo post è disponibile anche in: Inglese

Esistono oltre 450 formule diverse in Microsoft Excel. E il nostro preferito di tutti i tempi è INDICE.

Nel gennaio 2020 e in Excel 365, Microsoft ha apportato una modifica significativa al modo in cui si comportano molte formule, incluso INDICE. Per le persone che utilizzano il nostro Fast Excel Development Method, questo è un aggiornamento importante che potrebbe avere un effetto sostanziale sulle prestazioni delle cartelle di lavoro Excel.

La sintesi è questa: se sei su Excel 365 usa @INDICE al posto di INDICE. Fallo e sarai a posto: le tue cartelle di lavoro Fast Excel funzioneranno velocemente e in modo pulito.

Vuoi sapere perché? Visto “#SPILL!” in una cella e ti sei chiesto cosa significa? Quindi continua a leggere perché questa è una storia di INDICE e array dinamici.

Cos’è INDICE e perché è così grande?

INDICE restituisce il valore di una cella che si trova in un intervallo, dato il numero di riga e / o colonna. Se inserisci nella formula un intervallo e un numero di riga (e colonna), la formula ti darà il valore che si trova nella cella.

Ecco la definizione della formula. INDICE (matrice; riga; [colonna])

In questo contesto, “matrice” è un intervallo di celle. “riga” è un numero che fa riferimento al numero di riga di quell’intervallo. “colonna” è il numero di colonna, non necessario se l’intervallo ha una colonna sola.

Ad esempio, ecco un elenco di frutta nell’intervallo di celle B2: B11. La formula = INDICE (B2: B11; D3) restituisce l’elemento in quella cella definita dal numero di riga fornito in D3. Quando il valore in D3 è 6, la formula restituisce “Watermelons”.

Nel nostro Fast Excel Development Method, utilizziamo ampiamente INDICE perché è un modo veloce e affidabile per unire due tabelle di dati.

Questo semplice esempio è su un unico foglio. Tuttavia, la potenza di INDICE è che fornisce un modo per estrarre dati da diverse tabelle su migliaia di record e costruire rapidamente un sistema potente.

Diciamo che usi molto la formula CERCA.VERT e poi un giorno impari INDICE (insieme a CONFRONTA). Non utilizzerai mai più CERCA.VERT. Ecco un articolo che ho scritto su INDICE come la funzione più utile che imparerai mai .

Di seguito è riportato un altro esempio che lo utilizza per unire Anagrafica articolo e Ordini di vendita per portare la Descrizione articolo insieme al record dell’ordine di vendita.

Cosa è cambiato in Excel 365 nel 2020 e che cos’è un array dinamico?

Nel gennaio 2020, Microsoft ha introdotto le formule Dynamic Array. Le formule di matrice restituiscono una matrice di valori anziché un singolo riferimento. In quanto tali, non sono nuovi, ma dovevi fare un ctrl-shift-Invio come una specie di trucco ninja e apparivano circondati da {parentesi graffe}.

Ora, le formule di matrice sono diventate più importanti e sono più facili da eseguire. E con Dynamic Array Formulas, puoi restituire un intervallo digitando semplicemente in una cella. Come questa nuova formula: SORT.

= SORT (B2: B11) nella cella E2 crea un elenco ordinato a partire da E2. Ottieni un intervallo per il prezzo di una cella!

Puoi trovare una buona, e aprofondita recensione della nuova formula su ExcelJet.com.

Le nuove formule non sono compatibili con le versioni precedenti di Excel. Anche se li crei in Excel 365, non funzioneranno in Excel 2016 e versioni precedenti.

Per questo motivo, non utilizzeremo le nuove formule nei download e strumenti standard forniti da noi o nel Fast Excel Development Template che offriamo.

Alcune di queste nuove formule sono piuttosto interessanti, ma non avrai bisogno di impararle per tenerti aggiornato sui nostri metodi.

Il problema è che INDICE è cambiata per diventare anche lei un Dynamic Array, se utilizzata con uno zero al posto del numero di riga.

Se usato con uno zero, INDICE restituirà un intero array di celle direttamente sotto. Quindi, = INDICE(B2: B11;0) restituisce un array che elenca tutti i valori in B2: B11.

Se quelle celle sono bloccate, ottieni un errore SPILL!. Guarda l’animazione per un confronto.

Questo è un problema con il metodo Fast Excel perché in grandi volumi, le formule di matrice funzioneranno molto più lentamente delle normali formule di riferimento.

Se c’è ambiguità sul fatto che si tratti di un array o di un riferimento, Excel entrerà in una sorta di groviglio cerebrale lento.

Il risultato sarà una cartella di lavoro inutilmente lenta. E se stai calcolando con 10.000 o 100.000 righe, le formule INDICE che si riferiscono a uno zero indicano che l’intera routine può richiedere 30 minuti quando normalmente lo farebbero in 8 secondi!

Ecco un esempio reale.

Questo è uno strumento di pianificazione e pianificazione delle capacità avanzate che stavamo creando come parte del nostro corso: Come costruire Strumenti di pianificazione in Excel .

Abbiamo creato uno strumento di pianificazione della capacità che ha fatto esplodere 5500 record della distinta base e ha calcolato la capacità richiesta per ogni articolo prodotto.

Nel foglio ScheduleCalcs, c’era un riferimento al Routing. E la riga di instradamento è apparsa in una tabella pivot, come puoi vedere in questa schermata.

Nel modello pivot, un riferimento a una tabella pivot restituirà una cella vuota nella riga 8.

Una cella vuota restituisce zero in un riferimento di cella. E usiamo la riga 8 per impostare la formula.

Excel 365 risolverà questo problema come una formula di matrice. La riga 10 ha le intestazioni della tabella, quindi questo bloccherà l’array, dando un errore #SPILL!. Quando viene incollato, viene bloccato in ogni cella e deve risolvere tutti quegli errori. E ancora più critico, altri fogli che hanno la formula INDIRETTO saranno molto rallentati.

Mi rendo conto che si tratta di aspetti profondi della conoscenza tecnica di Excel, quindi non voglio andare nella spiegazione del perché ciò accade.

È sufficiente sapere che se hai Excel 365 e usi INDICE, potresti avere un problema di lentezza nel ricalcolo delle cartelle di lavoro. E se usi INDIRETTO e altre funzioni volatili, questo problema si aggraverà ancora di più.

Ora, voglio darti la soluzione. E fortunatamente è molto semplice. Semplice come aggiungere un singolo carattere.

Come risolvere il problema della funzione INDICE

Per evitare questo problema, è sufficiente aggiungere “@” prima della formula INDICE.

Perché “@” o “chiocciola” è un “ Operatore di intersezione implicita “. Vedi? Ciò chiarisce tutto.

Oh. Non è affatto più chiaro? No, nemmeno per me fino a quando non ho letto di più.

Apparentemente “@” o “chiocciola” costringe Excel a leggere la formula INDICE come una formula destinata a restituire un riferimento. quindi non viene più confuso dall’array.

Fa in modo che INDICE si comporti come ha sempre fatto. Prima che Microsoft apportasse questa modifica.

Il fatto è che se usi molte formule INDICE, non puoi mai essere sicuro di come verrà risolto il riferimento nella riga 8. Almeno, non senza molti laboriosi controlli. Quindi, è più facile prendere l’abitudine di trasformare ogni INDICE in un @INDICE.

Fai solo attenzione a usare TROVA-SOSTITUISCI per questo. Perché potresti finire con un sacco di inutili @INDICEINDICE nelle tue formule! E fare un TROVA-SOSTITUISCI potrebbe ancora avere problemi di compatibilità quando poi dai la tua cartella di lavoro agli utenti con versioni precedenti di Excel.

Se utilizzi il segno”chiocciola” “@” (come operatore di intersezione implicito, come @INDICE (…) quando inserisci la formula la prima volta, le tue formule @INDICE funzioneranno in modo rapido e affidabile. Senza penalità o effetto collaterale negativo.

Versioni di Excel e controllo di compatibilità

Vale la pena dire alcune cose sulla compatibilità con le versioni precedenti e su altre versioni di Excel.

Se esegui la versione desktop installata di Excel 365, ti consiglio di usare @ ogni volta che usi INDICE. E soprattutto dove la formula nella riga 8 punta a una cella con che si risolve a zero oa una cella vuota. Se vedi @INDICE in una formula costruita da qualcun altro, sai perché.

Se esegui Excel 2019 o versioni precedenti, puoi utilizzare INDICE. Se qualcuno apre il tuo file su Excel 365, Excel lo convertirà automaticamente aggiungendo un simbolo @ “AT”.

Se esegui la versione online di Excel 365, questo principio si applica anche alla formula. MA, le macro non funzioneranno nella versione online. Quindi il nostro Fast Excel Development Method non ti sarà molto utile. In questo caso, installalo sulla tua macchina e poi usa Dropbox. Oppure esegui la versione installata e quindi sincronizza i tuoi file locali con Excel online.

Se esegui qualsiasi versione tra Excel 2010 e l’ultimo Excel 365, continueremo a supportarti. Tutto ciò che faremo verrà testato e controllato per la compatibilità con le versioni precedenti. Ignoriamo molte funzioni più recenti perché siamo consapevoli che molti dei nostri utenti hanno versioni precedenti e vogliamo essere inclusivi.

Se esegui Excel 2007 o versioni precedenti, possiamo ancora sviluppare sistemi personalizzati per te. Ma non possiamo garantire che i nostri download funzioneranno con la tua versione. Ci sono formule che sono così comuni ora che non erano supportate (SE.ERRORE per esempio) e le tabelle pivot sono state costruite in modo diverso nelle versioni precedenti al 2007. Penso che 10 anni sia un lasso di tempo ragionevole per supportare la retrocompatibilità. So che ci sono sviluppatori Excel vecchi e rinsecchiti che insistono nell’usare versioni vintage di Excel. Ma siamo passati a strumenti migliori 😉

Sarei interessato a un tuo contatto se hai 2007 o più anni e non hai intenzione di cambiare. E voglio continuare a usare i nostri metodi. Contattami su kien@production-scheduling.com o qui .

Se esegui Excel per Mac, in questo momento il Fast Excel Development Template non è completamente compatibile con Mac OS. Se disponi della versione 2016 o precedente, è disponibile il supporto per le macro. E stiamo esaminando cosa sarebbe necessario per rendere il Development Template completamente compatibile. Ma per ora questo è un lavoro in corso.

You can check the compatibility of your Excel 365 file with older workbooks, using the Verifica di compatibilità – File > Informazioni > Verifica Documento > Controlla Compatibilità Questa si sta dimostrando una funzione piuttosto utile. In particolare perchè a Windows non piacciono più versioni di Excel sullo stesso computer.

Continueremo a utilizzare la versione installata di Excel 365 e quindi testeremo la compatibilità con le versioni di Excel fino al 2010.

Lascia un commento

Torna su