Molte delle formule di Excel che utilizzo per gli elenchi richiedono supervisione. A volte, quando si aggiunge una nuova riga, si verifica un errore o è necessario modificarla. Questo problema non si verifica con TAKE e DROP. Queste due funzioni consentono di... La matrice dinamica, che fa espandere automaticamente le tabelleTrascinando o rimuovendo righe e colonne specifiche da un set di dati, questo viene aggiornato automaticamente man mano che i dati cambiano.
Combinando queste funzioni con quelle di ORDINA e FILTRA, otterrai elenchi che si aggiornano automaticamente senza alcuno sforzo aggiuntivo. Ecco come utilizzarli per creare elenchi che rimangano effettivamente aggiornati.
Link veloci
TAKE cattura esattamente le righe o le colonne di cui hai bisogno.
Invialo ai tuoi dati, comunicagli il numero di righe e lui te li consegnerà.
La funzione TAKE fa una cosa bene. Estrae un numero specificato di righe o colonne da un intervallo, partendo da qualsiasi estremità definita. Non necessita di colonne ausiliarie o L'interferenza INDEX-MATCH è complessaInvialo ai tuoi dati, indicagli quante righe desideri e lui te le fornirà.
Ecco la struttura della frase:
=TAKE(array, righe, [colonne])
- Vettore: L'intervallo o l'array di origine da cui viene estratto.
- righe: Numero di righe da restituire. Estrarre il numero positivo dall'alto; estrarre il numero negativo dal basso.
- colonne (facoltativo): Funziona allo stesso modo: estrae il positivo da sinistra e il negativo da destra.
Supponiamo di avere dati di vendita in A2:D20 e di voler visualizzare le prime cinque righe. Dovresti usare la seguente formula:
=PRENDI(A2:D20, 5)
Hai bisogno degli ultimi tre? Sostituisci con -3. Il risultato viene automaticamente distribuito alle celle adiacenti e ricalcolato ogni volta che i dati di origine cambiano.
DROP rimuove righe o colonne indesiderate.
Dille cosa vuoi che ignori e ti riporterà indietro tutto il resto.
La funzione DROP è l'opposto della funzione TAKE. Invece di specificare cosa si desidera conservare, si indica alla funzione cosa si desidera scartare e questa restituisce tutto il resto. Trovo questa funzione molto più semplice nelle situazioni in cui so esattamente quante righe indesiderate si trovano in cima o in fondo al dataset.
La sintassi riflette la funzione TAKE:
=DROP(array, righe, [colonne])
- Vettore: Il dominio o l'array sorgente.
- righe: Numero di righe da escludere. I valori positivi vengono eliminati dall'alto; i valori negativi vengono eliminati dal basso.
- colonne (facoltativo): I valori positivi vengono rimossi da sinistra e quelli negativi da destra.
Supponiamo che il set di dati nell'intervallo A2:D20 contenga due righe di riepilogo che assomigliano a un'intestazione e non sono necessarie. La seguente formula rimuove queste due righe e ripristina le restanti.
=ESCI(A2:D20; 2)
È anche possibile eliminare le colonne utilizzando:
=RILASCIA(A2:D20, 0, 1)
Questa funzione rimuove la prima colonna, utile quando un campo ID ostacola l'output. Come la funzione TAKE, il risultato si espande dinamicamente e si aggiorna man mano che i dati aumentano.
Combinazione delle funzioni TAKE e DROP per una segmentazione precisa dei dati
Posizionarli uno dentro l'altro per estrarre qualsiasi sezione dal centro
Separatamente, le funzioni TAKE e DROP estraggono i dati dai bordi di un set di dati. Tuttavia, inserendole una dentro l'altra, è possibile estrarre qualsiasi sezione dal centro, cosa che nessuna delle due funzioni può fare da sola.
L'idea è quella di utilizzare la funzione DROP per rimuovere le righe non necessarie in alto, quindi racchiudere il risultato nella funzione TAKE per ridurre il numero di righe da conservare. Utilizzando i dati di vendita come esempio, supponiamo di voler saltare le prime quattro voci e utilizzare le successive otto per coprire le transazioni dal 12 gennaio al 3 febbraio. La formula sarebbe:
=PRENDI(LASCIA(A2:D20; 4); 8)
La funzione DROP rimuove le prime quattro righe (le voci del 5 gennaio), mentre la funzione TAKE restituisce le otto righe successive di ciò che rimane. L'output include tutte e quattro le colonne, dalla data al venditore, in modo da ottenere una diapositiva completa senza dover aggiungere colonne ausiliarie o definire manualmente intervalli.
Ciò è particolarmente utile quando il set di dati cresce nel tempo, poiché entrambe le funzioni si adattano dinamicamente quando vengono aggiunte nuove righe.
Crea un elenco "Best N" autoaggiornante utilizzando le funzioni TAKE e SORT
L'elenco viene aggiornato automaticamente ogni volta che i dati cambiano.
È qui che la funzione TAKE inizia a dimostrare la sua importanza. Combinandola con la funzione SORT, è possibile creare un elenco che visualizza sempre i valori più alti (o più bassi) del set di dati, aggiornandosi automaticamente ogni volta che i dati cambiano.
Utilizzando ancora i dati di vendita, supponiamo di voler ottenere un elenco delle cinque transazioni più redditizie in base all'importo delle vendite. La formula è:
=PRENDI(ORDINA(A2:J20; 7; -1); 5)
La funzione ORDINA riordina tutte le 19 righe in base alla colonna 7 (importo delle vendite) in ordine decrescente, mentre la funzione PRENDI estrae le prime cinque voci da quel risultato ordinato. Si ottiene l'intera riga per ogni voce, inclusi data, regione, venditore e tutto il resto, in modo che il contesto rimanga intatto. Se non si ha familiarità con la funzione ORDINA, consultare la nostra guida su Ordinamento dei dati in Excel Copre le nozioni di base.
La parte migliore è ciò che accade quando arrivano nuovi dati di vendita. Quando aggiungi una riga con un valore di vendita più alto, questa si posiziona automaticamente tra le prime cinque. Se... Convertire l'intervallo di origine in un foglio di calcolo ExcelAnche la portata si espanderà autonomamente, rendendo l'intera configurazione completamente automatica.
Crea un elenco dinamico "Ultime voci" utilizzando le funzioni DROP e COUNTA
Sempre in mostra le ultime righe
L'elenco "Top N" è utile, ma a volte si desidera semplicemente visualizzare le aggiunte più recenti al set di dati. La funzione DROP, combinata con la funzione COUNTA, gestisce bene questo aspetto: conta il numero di righe ed elimina tutto tranne le ultime.
Ecco come visualizzare sempre le ultime cinque voci utilizzando i dati di vendita:
=DROP(A2:A20; COUNTA(A2:A20) - 5)
La funzione CONTA.VALORI conta tutte le celle riempite nella colonna A, in questo caso 19. Sottraendo 5, la funzione ELIMINA restituisce 14, quindi rimuove le prime 14 righe e restituisce le restanti cinque. Per ora, queste sarebbero le transazioni di marzo da rappresentanti di vendita come Mike Wilson a Tom Rodriguez.
Se aggiungi una nuova riga per aprile, CONTA.VALORI aumenterà a 20, ELIMINA verrà regolato a 15 e l'output visualizzerà le ultime cinque voci. Puoi anche utilizzare la seguente formula per ottenere lo stesso risultato, ma il metodo ELIMINA e CONTA.VALORI è più flessibile quando l'intervallo non è fisso.
=PRENDI(A2:A20, -5)
Le funzioni TAKE e DROP funzionano meglio se abbinate ad altre funzioni.
Prova queste combinazioni più tardi
Ho visto TAKE con SORT e DROP con COUNTA, ma c'è molto altro da esplorare. Includi FILTER in TAKE per creare elenchi che mostrano, ad esempio, le vendite di prodotti elettronici superiori a $ 3000, ordinate e specificate in base a un numero specifico. Oppure usa DROP con UNIQUE per rimuovere le intestazioni duplicate quando unisci dati da più fogli. CHOOSECOLS abbinato a TAKE può anche ridurre grandi set di dati alle sole colonne che contano. Una volta che inizi a annidare queste funzioni, le combinazioni diventano piuttosto pratiche.
