La funzione OFFSET di Excel rallenterà il calcolo della cartella di lavoro. C'è un'alternativa migliore: una sintassi insolita di INDICE.
Questo è un suggerimento di nicchia. C'è una funzione incredibilmente flessibile chiamata OFFSET. È flessibile perché può puntare a un intervallo di dimensioni diverse che viene calcolato al volo. Nell'immagine seguente, se qualcuno cambia il menu a discesa # Qtrs in H1 da 3 a 4, il quarto argomento di OFFSET farà in modo che l'intervallo si espanda per includere quattro colonne.
I guru del foglio di calcolo odiano OFFSET perché è una funzione volatile. Se vai a una cella completamente non correlata e inserisci un numero, verranno calcolate tutte le funzioni OFFSET. Anche se quella cella non ha nulla a che fare con H1 o B2. La maggior parte delle volte, Excel è molto attento a perdere tempo a calcolare le celle che devono essere calcolate. Ma una volta introdotto OFFSET, tutte le celle OFFSET, più tutto il downline dell'offset, iniziano a calcolare dopo ogni modifica nel foglio di lavoro.
Stavo giudicando le finali ModelOff 2013 a New York City quando alcuni dei miei amici australiani mi hanno indicato una bizzarra soluzione alternativa. Nella formula seguente, sono presenti i due punti prima della funzione INDICE. Normalmente, la funzione INDICE mostrata di seguito restituirebbe 1403 dalla cella D2. Ma quando metti i due punti su entrambi i lati della funzione INDICE, inizia a restituire l'indirizzo della cella D2 invece del contenuto di D2. Questo è selvaggio che funzioni.
Perché questo è importante? INDEX non è volatile. Ottieni tutta la bontà flessibile di OFFSET senza dover ricalcolare ripetutamente.
Ho imparato questo suggerimento per la prima volta da Dan Mayoh a Fintega. Grazie ad Access Analytic per aver suggerito questa funzione.
Guarda un video
Trascrizione del video
Impara Excel dal podcast, episodio 2048 -: INDEX sostituirà un OFFSET volatile!
Ehi, sto podcasting tutti i miei suggerimenti da questo libro, fai clic sulla "i" nell'angolo in alto a destra per accedere alla playlist!
Va bene, OFFSET è una funzione straordinaria! OFFSET ci consente di specificare una cella nell'angolo in alto a sinistra, quindi utilizzare le variabili per definire da lì quante righe in basso, quante righe sopra, e quindi definire una forma, va bene. Quindi qui, se voglio sommare, o fare una media di, diciamo 3/4, questa formula qui darà un'occhiata alla formula. L'offset dice che stiamo partendo da B2, partendo da Q1, scendiamo da 0, sopra 0, la forma sarà alta 1 riga e sarà H1, in altre parole larga 3 celle, va bene. Quindi in questo caso tutto ciò che stiamo facendo è cambiare quante celle stiamo sommando, iniziamo sempre da B2, o B3 o B4 mentre copio, e poi decide quante celle sono larghe. Va bene, OFFSET è questa cosa interessante, svolge tutti i tipi di funzioni straordinarie, ma ecco il problema, è volatile!Ciò significa che anche se qualcosa non è nella catena di calcolo, Excel, ci vorrà del tempo per ricalcolarlo, il che rallenterà le cose, va bene così.
Questa fantastica versione di INDICE, giusto, normalmente se chiedessi l'INDICE di queste 4 celle, 3, restituirà il numero 1403. Tuttavia, quando metto i due punti prima o dopo l'INDICE, succede qualcosa di molto diverso, daremo un'occhiata a questa formula qui. Quindi indice delle 4 celle, quale voglio, voglio la terza, ma vedi che c'è: proprio lì. Quindi andremo sempre da B2: E2 e ti mostrerò se andiamo a Formule, Valuta Formula, va bene, quindi proprio qui calcolerà il numero 3. E qui, l'INDICE con: successivo ad esso, invece di dirci 1403, che è il modo in cui normalmente calcola INDICE, restituirà $ D2, e quindi MEDIA farà la media di quei 3. Assolutamente sorprendente, il modo in cui funziona e il vantaggio aggiuntivo, non è volatile, va bene,e questo può anche essere usato per sostituire un OFFSET incredibilmente complesso.
Quindi qui con questo OFFSET ci basiamo su questi valori. Se scelgo Q2 e Central, va bene, queste formule utilizzano CONFRONTA e CONTA.SE per calcolare quante righe in basso, quante colonne sopra, quanto è alto, quanto largo. E poi l'offset qui utilizza tutti quei valori per calcolare la mediana. Faremo solo un test per assicurarci che funzioni, quindi = MEDIANA di quella fascia blu laggiù, meglio essere 71, va bene, e sceglieremo qualcos'altro qui, Q3 e Ovest, quindi. Premi F2, lo trascinerò e lo ridimensionerò per riscrivere quella formula, premere Invio e funziona con OFFSET.
Bene qui, usando un INDICE, in realtà ho due punti nel mezzo con un INDICE sul lato sinistro e un INDICE sul lato destro, guarda questa cosa viene calcolata in Valuta Formula. Quindi inizia, valuterà, valuterà e proprio qui che INDICE sta per trasformarlo in un indirizzo di cella. Quindi H16 è il 1 °, Ovest, Q3, e sopra a destra valuterà, ne accoppierà altri, e poi a destra cambierà in I20. Quindi il fresco, fresco non volatile per sostituire un OFFSET utilizzando la funzione INDICE. Bene, questo suggerimento e molti altri in questo libro, fai clic sulla "i" nell'angolo in alto a destra per acquistare il libro.
Bene, riepilogo: OFFSET, funzione fantastica e flessibile, una volta imparato, puoi fare ogni genere di cose. Punta a una cella in alto a sinistra variabile, punta a un intervallo che ha una forma variabile, ma è volatile, quindi calcolano ad ogni calcolo. Excel di solito esegue un calcolo intelligente o ricalcola le celle che devono essere calcolate, ma con OFFSET verrà sempre calcolato. Invece di OFFSET puoi usare INDICE: o: INDICE o anche INDICE: INDICE, ogni volta che INDICE ha i due punti accanto, restituirà un indirizzo di cella invece del valore di quella cella. E il vantaggio è che INDEX non è volatile!
OK, voglio ringraziarti per essere passato, ci vediamo la prossima volta per un altro netcast da!
Download file
Scarica il file di esempio qui: Podcast2048.xlsm
