Dopo anni trascorsi a gestire fogli di calcolo complessi e disordinati, ho scoperto quattro funzioni di Excel che mi fanno risparmiare ore di lavoro ogni settimana, automatizzando attività di routine che la maggior parte delle persone esegue manualmente. Queste funzioni sono indispensabili per chiunque lavori regolarmente con i dati, che sia un analista di dati professionista o semplicemente un utente occasionale che cerca di semplificare il proprio lavoro.
Link veloci
4. XLOOKUP: Ricerca avanzata nei fogli di calcolo
XLOOKUP Si tratta di una funzione di ricerca avanzata nei programmi di fogli di calcolo come Microsoft Excel e Google Sheets, che va oltre le capacità delle funzioni di ricerca tradizionali come VLOOKUP و HLOOKUP. Disponibilità XLOOKUP Maggiore flessibilità, gestione più efficiente dei dati e riduzione degli errori comuni associati alle funzioni legacy. XLOOKUP Uno strumento essenziale per analisti finanziari, data scientist e chiunque lavori con grandi quantità di dati e abbia bisogno di estrarre informazioni specifiche in modo rapido e accurato. Utilizzando XLOOKUPÈ possibile cercare un valore in un intervallo specifico e restituire un valore corrispondente da un altro intervallo, indipendentemente dalla posizione delle colonne o delle righe. Supporta anche XLOOKUP Effettua ricerche da destra a sinistra e dal basso verso l'alto, il che la rende più versatile rispetto ad altre funzioni.
Addio VLOOKUP: XLOOKUP è la soluzione perfetta
Ho smesso di usare CERCA.VERT anni fa quando ho scoperto CERCA.X. Mentre CERCA.VERT cerca solo a destra e si blocca quando si spostano le colonne, CERCA.X funziona in qualsiasi direzione e rimane flessibile. CERCA.X è una delle Funzioni di Excel che possono farti risparmiare tempo Trova dati specifici nei tuoi fogli di calcolo.
Nei miei dati sui prezzi dei componenti del computer, devo trovare prezzi specifici per GPU in base ai modelli di prodotto. Con CERCA.VERT, dovrei ristrutturare l'intera tabella. Ma con CERCA.X, tutto ciò che devo fare è digitare:
=XLOOKUP("GIGABYTE GeForce RTX 3060 12GB Gaming OC", C:C, D:D)
XLOOKUP cerca nell'intera colonna del prodotto, trova la mia GPU e restituisce il prezzo corrispondente. Non importa dove si trovi la colonna del prezzo e non si blocca se aggiungo altre colonne in seguito. Lo uso sempre per fare riferimento alle informazioni sui prodotti in fogli diversi senza dover riformattare nulla.
La formula di base per XLOOKUP è:
=XLOOKUP(valore_cercato; matrice_cercata; matrice_restituita)
- valore di ricerca: Il valore che vuoi cercare.
- ricerca_array: Il posto dove cerchi valore.
- matrice_di_ritorno: La colonna o la riga che contiene il valore che si desidera restituire.
Quindi, nel mio caso, il valore che volevo trovare era "GIGABYTE GeForce RTX 3060 12GB Gaming OC". Volevo cercare questo valore nella colonna C:C e restituire il valore corrispondente da D:D nella stessa riga in cui era stata trovata la corrispondenza.
Un altro aspetto che apprezzo di XLOOKUP è che se aggiungo ",-1" alla fine della formula, la ricerca avviene dal basso verso l'alto, consentendomi di trovare automaticamente il prezzo più recente. Questo mi evita di dover ordinare manualmente i dati ogni volta che aggiorno i miei fogli di calcolo.
3. Utilizzo delle mie funzioni SOMMA.PIÙ.SE و CONTA.PIÙ.SE Nei fogli di calcolo
Gestire più standard in modo professionale
Le funzioni base SOMMA e CONTA.NUMERI sono sufficienti per compiti semplici, ma risultano inadeguate quando si tratta di analisi pratiche. Quando devo analizzare i miei dati sui prezzi in più condizioni, in genere utilizzo le funzioni SOMMA.PIÙ.SE e CONTA.PIÙ.SE. Queste mi permettono di segmentare facilmente centinaia di righe.
Supponiamo di voler contare il numero di processori AMD disponibili su Amazon US. Invece di filtrare manualmente, digito:
=CONTA.PIÙ.SE(F:F; "Amazon US"; K:K; "AMD")
Questo mi mostra immediatamente che nel mio dataset sono elencati 14 processori AMD su Amazon. Il bello è che posso compilare tutti i benchmark di cui ho bisogno.
Per l'analisi dei prezzi, la funzione SOMMA.PIÙ.SE funziona allo stesso modo. Per calcolare il valore totale di tutti i processori Intel attualmente in magazzino, utilizzo:
=SOMMA.PIÙ.SE(D:D; K:K; "Intel"; G:G; "Disponibile")
In questo modo vengono aggiunti tutti i prezzi nella colonna D, dove il marchio è "Intel" e lo stato delle scorte è "In magazzino".
La sintassi della funzione SOMMA.PIÙ.SE è:
=SOMMA.PIÙ.SE(intervallo_somma; intervallo_criteri1; criteri1; intervallo_criteri2; criteri2...)
- somma_intervallo: La colonna che vuoi sommare.
- criteri_intervallo1: La prima colonna per verificare le condizioni.
- criteri1: Prima condizione di intervallo.
- criteri_intervallo2, criteri2: Termini e condizioni aggiuntivi (facoltativi).
La funzione CONTA.PIÙ.SE funziona in modo simile, tranne per il fatto che conta le righe corrispondenti anziché sommare i valori:
=CONTA.PIÙ.SE(intervallo_criteri1; criteri1; intervallo_criteri2; criteri2...)
Preferisco usare SOMMA.PIÙ.SE e CONTA.PIÙ.SE per report rapidi perché aggiornano istantaneamente i nuovi dati, si integrano perfettamente nelle mie formule esistenti e mi permettono di mantenere tutto in linea senza creare una tabella pivot separata. Questi strumenti consentono un'analisi dei dati accurata ed efficiente, risparmiando tempo e fatica nello sviluppo di report complessi. L'utilizzo di funzioni come SOMMA.PIÙ.SE e CONTA.PIÙ.SE è una competenza essenziale per ogni analista di dati che desideri estrarre informazioni preziose dai dati in modo rapido e semplice.
2. Rifinitura e pulizia: passaggi essenziali per mantenere l'aspetto
Addio disordine di dati
Niente rovina un foglio di calcolo più velocemente di dati non strutturati pieni di spazi extra e caratteri nascosti. L'ho imparato a mie spese quando le mie ricerche fallivano sistematicamente a causa di spazi extra alla fine dei nomi dei moduli.
La funzione ANNULLA.SPAZI.rimuove gli spazi in eccesso all'inizio e alla fine del testo, nonché eventuali spazi tra le parole. Quando importo dati da fonti diverse, i nomi dei prodotti spesso presentano spazi incoerenti. Invece di ripulire manualmente ogni cella, creo una colonna di supporto e utilizzo:
=ANNULLA.SPAZI(C2)
Quindi sposto il puntatore del mouse sul bordo della cella finché non si trasforma in un segno più (+), quindi lo trascino verso il basso su tutte le righe su cui voglio che la funzione ANNULLA.
1. TESTOBEFORE e TESTAFTER: una spiegazione dettagliata e la loro importanza
Estrarre accuratamente i dati richiesti
Le funzioni TEXTBEFORE e TEXTAFTER sono tra le mie funzioni Excel preferite per riordinare fogli di calcolo disordinati. Le moderne funzioni di testo di Excel sono eccellenti nell'estrarre informazioni specifiche da stringhe di testo non strutturate. Ad esempio, la mia colonna dei prezzi conteneva voci come "$177.52", "178.33 USD", "₱9055" e "9645.50 PHP" mescolate insieme.
La funzione TEXTBEFORE estrae tutto ciò che precede un separatore specificato:
=TESTOPRIMA(D2, "USD")
In questo modo, la funzione ha estratto istantaneamente “178.33” da “178.33 USD”.
La funzione TEXTAFTER funziona al contrario, estraendo tutto ciò che si trova dopo il separatore:
=TESTODOPO(C2, "AMD")
In questo modo ho estratto la funzione "Processore Ryzen 5 5700X 8-Core AM4" da "Processore AMD Ryzen 5 5700X 8-Core AM4".
Per estrazioni complesse, combino entrambe le funzioni. Per ottenere il prezzo numerico di 177.52 USD:
=TESTOPRIMA(TESTODOPO(D8; "$"); "USD")
La sintassi generale delle funzioni TEXTBEFORE e TEXTAFTER è:
=TEXTBEFORE(testo, delimitatore) e =TEXTAFTER(testo, delimitatore)
L'enorme miglioramento apportato da queste due funzioni risiede nella loro precisione. Invece di utilizzare complesse combinazioni di funzioni STRINGA.ESTRAI, TROVA e LUNGHEZZA, posso ottenere estrazioni pulite utilizzando formule semplici e di facile lettura. Utilizzo spesso queste funzioni per separare i numeri di modello, estrarre le specifiche di prodotto ed estrarre dati puliti da testo importato, operazione che prima richiedeva ore di editing manuale.
Queste quattro funzioni risolvono alcuni dei maggiori sprechi di tempo in Excel, come la ricerca di dati tramite ricerche flessibili, l'analisi basata su più criteri, la pulizia di testo importato disordinato e l'estrazione di informazioni specifiche da stringhe di testo complesse. La maggior parte delle persone gestisce queste attività manualmente, dedicando ore a ciò che normalmente richiederebbe solo pochi minuti per implementare le formule corrette.
Hai utilizzato queste funzioni per qualsiasi cosa, dall'analisi dei prezzi dei componenti ai report di gestione dell'inventario. Funzionano indipendentemente dal tuo settore, perché dati disordinati e requisiti di ricerca complessi sono problemi universali. Una volta padroneggiate queste funzioni, ti chiederai come hai fatto a gestire i fogli di calcolo senza di esse.
