Scarica la cartella di lavoro di esempio
Abbiamo discusso in altri articoli su come esistono funzioni come OFFSET e INDIRETTO che sono volatili. Se inizi a utilizzare molti di questi in un foglio di calcolo o hai molte celle dipendenti dalla funzione volatile, puoi far sì che il tuo computer impieghi molto tempo a ricalcolare ogni volta che provi a cambiare una cella. Piuttosto che essere frustrato dal fatto che il tuo computer non sia abbastanza veloce, questo articolo esplorerà modi alternativi per risolvere le situazioni comuni che le persone usano OFFSET e INDIRETTO.
Sostituzione di OFFSET per creare un elenco dinamico
Dopo aver appreso della funzione OFFSET, è un malinteso comune che sia l'unico modo per restituire un risultato con dimensione dinamica utilizzando l'ultimo paio di argomenti. Diamo un'occhiata a un elenco nella colonna A in cui il nostro utente potrebbe decidere in seguito di aggiungere ulteriori elementi.
Per creare un menu a discesa nella cella C2, puoi definire un intervallo denominato con una formula volatile come
=OFFSET($A$2, 0, 0, CONTA.VALORI($A:$A)-1, 1)
Con l'attuale configurazione, questo restituirebbe sicuramente un riferimento all'intervallo A2:A5. Tuttavia, esiste un altro modo per utilizzare l'INDICE non volatile. Per fare questo, pensa di scrivere un riferimento all'intervallo che va da A2 a A5. Quando scrivi "A2:A5", non pensare a questo come un singolo pezzo di dati, ma piuttosto come a "StartingPoint" e "EndingPoint" separati da due punti (ad esempio, StartingPoint:EndingPoint). In una formula, sia StartingPoint che EndingPoint possono essere i risultati di altre funzioni.
Ecco la formula che useremo per creare l'intervallo dinamico utilizzando la funzione INDICE:
=$A$2:INDICE($A:$A, COUNTA($A:$A))
Nota che abbiamo affermato che il punto di partenza per questo intervallo sarà sempre A2. Dall'altra parte dei due punti, stiamo usando INDEX per determinare dove deve essere EndingPoint. Il COUNTA determinerà che ci sono 5 celle con dati nella colonna A, quindi il nostro INDICE creerà un riferimento ad A5. La formula viene quindi valutata in questo modo:
=$A$2:INDICE($A:$A, COUNTA($A:$A)) =$A$2:INDICE($A:$A, 5) =$A$2:$A5
Utilizzando questa tecnica, puoi creare dinamicamente un riferimento a qualsiasi elenco o anche una tabella bidimensionale utilizzando la funzione INDICE. In un foglio di calcolo con un'abbondanza di funzioni OFFSET, la sostituzione degli OFFSET con INDEX consentirà al tuo computer di iniziare a funzionare molto più velocemente.
Sostituzione di INDIRETTO per i nomi dei fogli
La funzione INDIRETTO viene spesso chiamata quando le cartelle di lavoro sono state progettate con dati sparsi su più fogli di lavoro. Se non riesci a ottenere tutti i dati su un singolo foglio, ma non desideri utilizzare una funzione volatile, potresti essere in grado di utilizzare SCEGLI.
Considera il seguente layout, in cui abbiamo i dati sulle vendite in 3 diversi fogli di lavoro. Nel nostro foglio Riepilogo, abbiamo selezionato da quale trimestre desideriamo visualizzare i dati.
La nostra formula in B3 è:
=SCEGLI(MATCH(B2, D2:D4, 0), Autunno!A2, Inverno!A2, Primavera!A2)
In questa formula, la funzione CONFRONTA determinerà quale area vogliamo restituire. Questo indica quindi alla funzione SCEGLI quale dei seguenti intervalli restituire come risultato.
Puoi anche usare la funzione SCEGLI per restituire un intervallo più ampio. In questo esempio, abbiamo una tabella di dati di vendita su ciascuno dei nostri tre fogli di lavoro.
Invece di scrivere una funzione INDIRETTA per creare il nome del foglio, puoi lasciare che SCEGLI determini su quale tabella eseguire la ricerca. Nel mio esempio, ho già chiamato le tre tabelle tbFall, tbWinter e tbSpring. La formula in B4 è:
=CERCA.VERT(B3, SCEGLI(MATCH(B2, D2:D4, 0), tbFall, tbWinter, tbSpring), 2, 0)
In questa formula, MATCH determinerà che vogliamo il 2ns articolo dalla nostra lista. CHOOSE prenderà quindi quel 2 e restituirà il riferimento a tbWinter. Infine, il nostro VLOOKUP sarà in grado di completare la ricerca nella tabella data e troverà che le vendite totali per Banana in inverno sono state di $ 6000.
=CERCA.VERT(B3, SCEGLI (MATCH(B2, D2:D4, 0), tbFall, tbWinter, tbSpring), 2, 0) =VLOOKUP(B3, CHOOSE(2, tbFall, tbWinter, tbSpring), 2, 0) = CERCA.VERT(B3, tbInverno, 2, 0) =6000
Questa tecnica è limitata dal fatto che devi compilare la funzione SCEGLI con tutte le aree da cui potresti voler recuperare un valore, ma ti dà il vantaggio di evitare una formula volatile. A seconda di quanti calcoli devi completare, questa capacità potrebbe rivelarsi piuttosto preziosa.
