Trucchi di vita di Excel per coloro che sono coinvolti nella creazione di report e nell'elaborazione dei dati

2024 Autore: Malcolm Clapton | [email protected]. Ultima modifica: 2024-01-13 02:02

In questo post, Renat Shagabutdinov, assistente del direttore generale della casa editrice Mann, Ivanov e Ferber, condivide alcuni fantastici trucchi per la vita di Excel. Questi suggerimenti saranno utili per chiunque sia coinvolto in vari rapporti, elaborazione dei dati e creazione di presentazioni.

Questo articolo contiene semplici tecniche per semplificare il tuo lavoro in Excel. Sono particolarmente utili per coloro che sono impegnati nella reportistica gestionale, preparano vari report analitici basati su download da 1C e altri report, formano presentazioni e diagrammi da essi per la gestione. Non pretendo di essere una novità assoluta: in una forma o nell'altra, queste tecniche sono state probabilmente discusse nei forum o menzionate negli articoli.

Semplici alternative a CERCA. VERT e CERCA. ORIZZ, se i valori desiderati non sono nella prima colonna della tabella: CERCA, INDICE + RICERCA

Le funzioni CERCA. VERT e CERCA. ORIZZ funzionano solo se i valori desiderati si trovano nella prima colonna o riga della tabella da cui prevedi di ottenere i dati.

Altrimenti, ci sono due opzioni:

1. Usa la funzione CERCA.

   Ha la seguente sintassi: LOOKUP (lookup_value; lookup_vector; result_vector). Ma affinché funzioni correttamente, i valori dell'intervallo view_vector devono essere ordinati in ordine crescente:

   

2. Utilizzare una combinazione di funzioni CONFRONTA e INDICE.

   La funzione CONFRONTA restituisce il numero ordinale di un elemento nell'array (con il suo aiuto puoi trovare in quale riga della tabella si trova l'elemento cercato) e la funzione INDICE restituisce un elemento dell'array con un dato numero (che scopriremo utilizzando la funzione CONFRONTA).

   

   Sintassi della funzione:

   • SEARCH (search_value; search_array; match_type) - per il nostro caso, abbiamo bisogno di un tipo di corrispondenza "corrispondenza esatta", corrisponde al numero 0.

   • INDICE (array; numero_riga; [numero_colonna]). In questo caso, non è necessario specificare il numero di colonna, poiché l'array è costituito da una riga.

Come riempire rapidamente le celle vuote in un elenco

Il compito è riempire le celle nella colonna con i valori in alto (in modo che l'argomento sia in ogni riga della tabella e non solo nella prima riga del blocco di libri sull'argomento):

Seleziona la colonna "Oggetto", fai clic sulla barra multifunzione nel gruppo "Home", il pulsante "Trova e seleziona" → "Seleziona un gruppo di celle" → "Celle vuote" e inizia a inserire la formula (ovvero, metti un uguale segno) e fare riferimento alla cella in alto, semplicemente facendo clic sulla freccia in su sulla tastiera. Dopodiché premi Ctrl + Invio. Successivamente, puoi salvare i dati ricevuti come valori, poiché le formule non sono più necessarie:

Come trovare errori in una formula

Calcolo di una parte separata di una formula

Per comprendere una formula complessa (in cui altre funzioni vengono utilizzate come argomenti di funzione, ovvero alcune funzioni sono nidificate in altre) o per trovare la fonte degli errori in essa, spesso è necessario calcolarne una parte. Ci sono due semplici modi:

1. Per calcolare parte di una formula direttamente nella barra della formula, seleziona quella parte e premi F9:

   

   In questo esempio, si è verificato un problema con la funzione SEARCH: gli argomenti sono stati scambiati al suo interno. È importante ricordare che se non si annulla il calcolo della parte della funzione e si preme Invio, la parte calcolata rimarrà un numero.

2. Fare clic sul pulsante Calcola formula nel gruppo Formule sulla barra multifunzione:

   

   Nella finestra che appare, puoi calcolare la formula passo dopo passo e determinare in quale fase e in quale funzione si verifica un errore (se presente):

   

Come determinare da cosa dipende o da cosa si riferisce una formula

Per determinare da quali celle dipende una formula, nel gruppo Formule sulla barra multifunzione, fare clic sul pulsante Celle interessate:

Vengono visualizzate delle frecce per indicare da cosa dipende il risultato del calcolo.

Se viene visualizzato il simbolo evidenziato nell'immagine in rosso, la formula dipende dalle celle su altri fogli o in altri libri:

Facendo clic su di esso, possiamo vedere esattamente dove si trovano le celle o gli intervalli che influenzano:

Accanto al pulsante "Celle che influenzano" c'è il pulsante "Celle dipendenti", che funziona allo stesso modo: visualizza le frecce dalla cella attiva con una formula alle celle che dipendono da essa.

Il pulsante "Rimuovi frecce", situato nello stesso blocco, consente di rimuovere le frecce alle celle che influenzano, le frecce alle celle dipendenti o entrambi i tipi di frecce contemporaneamente:

Come trovare la somma (numero, media) dei valori delle celle da più fogli

Supponiamo che tu abbia diversi fogli dello stesso tipo con dati che desideri aggiungere, contare o elaborare in qualche altro modo:

Per fare ciò, nella cella in cui vuoi vedere il risultato, inserisci una formula standard, ad esempio SUM (SUM), e specifica il nome del primo e dell'ultimo foglio dall'elenco di quei fogli che devi elaborare in l'argomento, separato da due punti:

Riceverai la somma delle celle con l'indirizzo B3 dai fogli "Data1", "Data2", "Data3":

Questo indirizzamento funziona per i fogli localizzati costantemente … La sintassi è la seguente: = FUNCTION (first_list: last_list! Riferimento intervallo).

Come creare automaticamente frasi modello

Utilizzando i principi di base dell'utilizzo del testo in Excel e alcune semplici funzioni, è possibile preparare frasi modello per i report. Diversi principi per lavorare con il testo:

• Concateniamo il testo usando il segno & (puoi sostituirlo con la funzione CONCATENATE, ma non ha molto senso).
• Il testo è sempre scritto tra virgolette, i riferimenti alle celle con testo sono sempre senza.
• Per ottenere il carattere di servizio "virgolette", utilizzare la funzione CHAR con argomento 32.

Un esempio di creazione di una frase modello utilizzando le formule:

Risultato:

In questo caso, oltre alla funzione CAR (per visualizzare le quotazioni), viene utilizzata la funzione SE, che permette di modificare il testo a seconda che vi sia un andamento positivo delle vendite, e la funzione TESTO, che consente di visualizzare il numero in qualsiasi formato. La sua sintassi è descritta di seguito:

TESTO (valore; formato)

Il formato è specificato tra virgolette, proprio come se si immettesse un formato personalizzato nella finestra Formato celle.

Anche i testi più complessi possono essere automatizzati. Nella mia pratica, c'era l'automazione di commenti lunghi ma di routine al reporting direzionale nel formato "INDICATOR è diminuito/aumentato di XX rispetto al piano, principalmente a causa della crescita/decremento di FACTOR1 di XX, crescita/decremento di FACTOR2 di YY …" con un elenco variabile di fattori. Se scrivi spesso questi commenti e il processo di scrittura può essere algoritmizzato, vale la pena creare una formula o una macro che ti faccia risparmiare almeno parte del lavoro.

Come memorizzare i dati in ogni cella dopo la concatenazione

Quando unisci le celle, viene mantenuto un solo valore. Excel avverte di questo quando si tenta di unire le celle:

Di conseguenza, se hai avuto una formula che dipende da ciascuna cella, smetterà di funzionare dopo averle combinate (# N / A errore nelle righe 3-4 dell'esempio):

Per unire le celle e conservare comunque i dati in ciascuna di esse (forse hai una formula come in questo esempio astratto; forse vuoi unire le celle, ma conservare tutti i dati per il futuro o nasconderli intenzionalmente), unisci tutte le celle sul foglio, selezionali, quindi usa il comando Copia formato per trasferire la formattazione nelle celle che devi combinare:

Come creare un pivot da più origini dati

Se è necessario creare un pivot da più origini dati contemporaneamente, sarà necessario aggiungere la "Creazione guidata tabella pivot e grafico" alla barra multifunzione o al pannello di accesso rapido, che dispone di tale opzione.

Puoi farlo come segue: "File" → "Opzioni" → "Barra degli strumenti di accesso rapido" → "Tutti i comandi" → "Procedura guidata tabella pivot e grafico" → "Aggiungi":

Successivamente, apparirà un'icona corrispondente sulla barra multifunzione, facendo clic su cui richiama la stessa procedura guidata:

Quando fai clic su di esso, viene visualizzata una finestra di dialogo:

In esso, è necessario selezionare la voce "In diversi intervalli di consolidamento" e fare clic su "Avanti". Nel passaggio successivo, puoi selezionare "Crea un campo pagina" o "Crea campi pagina". Se vuoi trovare indipendentemente un nome per ciascuna delle origini dati, seleziona il secondo elemento:

Nella finestra successiva, aggiungi tutti gli intervalli in base ai quali verrà costruito il pivot e assegna loro i nomi:

Successivamente, nell'ultima finestra di dialogo, specifica dove verrà posizionato il rapporto della tabella pivot, su un foglio esistente o nuovo:

Il report della tabella pivot è pronto. Nel filtro "Pagina 1" è possibile selezionare solo una delle origini dati, se necessario:

Come calcolare il numero di occorrenze del testo A nel testo B ("tariffa MTS SuperMTS" - due occorrenze dell'abbreviazione MTS)

In questo esempio, la colonna A contiene diverse righe di testo e il nostro compito è scoprire quante volte ciascuna di esse contiene il testo di ricerca situato nella cella E1:

Per risolvere questo problema, puoi utilizzare una formula complessa composta dalle seguenti funzioni:

1. DLSTR (LEN) - calcola la lunghezza del testo, l'unico argomento è il testo. Esempio: DLSTR ("macchina") = 6.
2. SOSTITUISCI - sostituisce un testo specifico in una stringa di testo con un altro. Sintassi: SOSTITUTO (testo; vecchio_testo; nuovo_testo). Esempio: SOSTITUTO (“auto”; “auto”; “”) = “mobile”.
3. UPPER - sostituisce tutti i caratteri in una stringa con lettere maiuscole. L'unico argomento è il testo. Esempio: SUPERIORE (“macchina”) = “AUTO”. Questa funzione è necessaria per eseguire ricerche senza distinzione tra maiuscole e minuscole. Dopotutto, SUPERIORE ("auto") = SUPERIORE ("Macchina")

Per trovare l'occorrenza di una determinata stringa di testo in un'altra, è necessario eliminare tutte le sue occorrenze in quella originale e confrontare la lunghezza della stringa risultante con quella originale:

DLSTR ("Tariffa MTS Super MTS") - DLSTR ("Tariffa Super") = 6

E poi dividi questa differenza per la lunghezza della stringa che stavamo cercando:

6 / DLSTR ("MTS") = 2

Sono esattamente due volte che la riga "MTS" è inclusa in quella originale.

Resta da scrivere questo algoritmo nel linguaggio delle formule (indichiamo con "testo" il testo in cui stiamo cercando le occorrenze e con "ricercato" - quello di cui siamo interessati al numero di occorrenze):

= (DLSTR (testo) -LSTR (SOSTITUTO (UPPER (testo); UPPER (ricerca), ""))) / DLSTR (ricerca)

Nel nostro esempio, la formula è simile a questa:

= (DLSTR (A2) -LSTR (SOSTITUTO (SUPPER (A2), UPPER ($ E $ 1), “”))) / DLSTR ($ E $ 1)