Excel-vita di hacking per chi si occupa di reporting e l'elaborazione dei dati

Renat non è la prima volta che un autore ospite parla Layfhakere. In precedenza, abbiamo pubblicato un ottimo materiale da lui su come creare un piano di formazione: libro principale e risorse online, così come algoritmo incrementale creare un piano di formazione.

Questo articolo contiene alcune semplici tecniche che aiutano semplificare il lavoro in Excel. essi sono particolarmente utili per chi si occupa di reporting direzionale, prepara una varietà di analitica report basati sugli sbarchi di rapporti 1C e altre forme di queste presentazioni e diagrammi per leadership. Non fingere di essere una novità assoluta - in un modo o nell'altro, questi metodi certamente discussi nei forum o di cui all'articolo.

Semplice alternativa al VLOOKUP e CERCA.ORIZZ, se i valori richiesti non sono nella prima colonna della tabella: VIEW, INDEX + PARTITA

VLOOKUP (VLOOKUP) E PGR (CERCA.ORIZZ) funzionano solo se i valori obiettivo sono nella prima colonna o riga della tabella da cui si desidera ottenere i dati.

In altri casi, ci sono due opzioni:

1. Utilizzare la funzione VIEW (ricerca).
   Lei ha la seguente sintassi: GUARDANDO (lookup_value Cerca vettore; vektor_rezultata). Ma per farlo funzionare correttamente è necessario valori di intervallo Ricerca vettore Essi sono stati in ordine crescente:
2. CORRISPONDENZA utilizzare una combinazione di funzioni (MATCH) e un indice (INDEX).
   funzione match restituisce il numero di serie del elemento dell'array (con il suo aiuto si possono trovare, in una riga della tabella l'elemento desiderato), e funzione INDICE restituisce un elemento di matrice con un numero predeterminato (che sappiamo funzione MATCH).opzioni di sintassi:
   • MATCH (lookup_value massiv_poiska; match_type) - nel nostro caso, abbiamo bisogno di un tipo di confronto "corrispondenza esatta", che corrisponde al numero 0.
   • INDEX (matrice; row_num; [Column_num]). In questo caso, non è necessario specificare il numero di colonna come array consiste di una singola linea.

In quanto tempo per riempire le celle vuote nella lista

Il compito - per riempire le celle con i valori nella colonna sopra (a piedi in ciascuna riga della tabella, non solo nella prima fila di tasselli di libri sull'argomento):

Selezionare la colonna "Oggetto", premere sul nastro nel gruppo di pulsante "Home" "Trovare e selezionare» → «Evidenziare il gruppo di cellule» → «vuoto cellula ", è possibile introdurre la formula (cioè, mettere un segno di uguale) e si riferisce alla cella dall'alto, semplicemente premendo la freccia su tastiera. Quindi premere Ctrl + Invio. Dopo di che, i dati possono essere salvati come un valore, perché le formule non sono più necessari:

Come trovare gli errori nella formula

Formula di calcolo parte separata

Per comprendere la complessa formula (in cui come argomenti della funzione utilizzanti la funzione, cioè, alcune funzioni sono integrate in un altro), o per trovare in essa una fonte di errori, spesso la necessità di calcolare parte di esso. Ci sono due semplici modi:

1. Per calcolare la parte destra della formula nella barra della formula, selezionare la parte, e premere F9:
   
   In questo esempio, c'era un problema con la funzione di ricerca (SEARCH) - ci sono stati argomenti fino misti. E 'importante ricordare che se non si annulla il calcolo della funzione e premere Invio, quindi la parte calcolata rimarrà numero.
2. Premere il tasto "Calcola la formula" nel gruppo "formula" sul nastro:
   
   
   Nella finestra che appare, è possibile calcolare il passo formula per passo e per determinare in quale fase e in quali errori di funzione (se del caso):
   

Come determinare ciò che determina che formula o riferimento

Per determinare quale delle cellule dipende dalla formula nel gruppo della "formula" nella barra multifunzione fare clic sul pulsante "Individua precedenti":

Ci sono frecce che indicano il risultato della cui dipende.

Se appare il simbolo sull'immagine selezionata in rosso, la formula dipende da cellule che si trovano in altre liste o in altri libri:

Cliccando su di esso, siamo in grado di vedere esattamente dove l'influenza della cella o intervallo:

Accanto alla "Individua precedenti" c'è un pulsante "carico", lavorano allo stesso modo: visualizza frecce dalla cella attiva con la formula alle celle che dipendono da essa.

Pulsante "Rimuovi frecce" che si trova nello stesso blocco, permette di rimuovere Precedents, carico, o entrambi i tipi contemporaneamente frecce:

Come trovare la quantità valori (numero, medi) delle cellule con più fogli

Diciamo che si dispone di più fogli simile con i dati che si desidera aggiungere, trovare o trattati in altro modo:

Per fare questo, la cella in cui si desidera visualizzare il risultato, immettere la formula standard, come ad esempio SUM (SUM), e digitare l'argomento seguito da due punti e il cognome del primo foglio dall'elenco dei fogli è necessario processo:

Riceverete la quantità di indirizzo di cella B3 con fogli "Data1", "Data2", "dati3":

Tali opere indirizzamento per lastre disposte successivamente. La sintassi è: = (FEATUREpervyy_list: posledniy_list riferito al campo!).

Come costruire automaticamente un frasi formulari

Utilizzando i principi fondamentali di lavorare con il testo in Excel e alcune funzioni semplici, può essere frasi formulari preparati per i rapporti. Diversi principi del lavoro con il testo:

• Combinare testo con il segno & (può sostituire la sua funzione CONCATENATE (CONCATENA), ma questo non è molto utile).
• Il testo è sempre scritto tra virgolette, i riferimenti di cella con il testo - non sempre.
• Per ottenere il carattere "virgolette" speciali, utilizzando la funzione di carattere (char) argomento 32.

ESEMPIO creazione di frasi utilizzando template formule:

risultato:

In questo caso, oltre alla funzione SIMBOLO (char) (per visualizzare le citazioni) utilizzando la funzione IF (IF), che permette di variare il testo A seconda se v'è un positivo andamento delle vendite e la funzione del testo (TEXT), che permette di visualizzare un numero in qualsiasi Formato. La sua sintassi è descritto di seguito:

TESTO (valore; formato)

Il formato è specificato tra virgolette nello stesso modo come se hai inserito un formato personalizzato nella sezione "Formato celle".

È possibile automatizzare testi più complessi. Nella mia pratica era di automatizzare lunga, ma i commenti di routine sui conti di gestione nel formato "cifra è scesa / rosa al XX sul piano principalmente per l'incremento / decremento del FAKTORA1 XX, aumento / diminuzione del FAKTORA2 YY... »con la lista che cambia fattori. Se si scrive tali commenti spesso e il processo di scrittura può algorithmization - vale la pena una volta perplesso creare una formula o macro che si salverà da almeno una parte del lavoro.

Come salvare i dati in ogni cella dopo la fusione

Quando si combinano le cellule mantenute solo valore. Excel si avverte quando si tenta di celle di unione:

Di conseguenza, se si ha una formula che dipende da ogni cellula, cesserà di lavoro dopo la loro unione (bug # N / A nelle linee 3-4 esempi):

Per celle di unione, pur conservando i dati in ciascuna di esse (forse si dispone di una formula, come in questo esempio, l'astratto; si potrebbe desiderare di unire le celle, ma mantenere tutti i dati in futuro, o per nascondere le loro intenzioni), combinare qualsiasi cella foglio, selezionarle e quindi utilizzare il formato di trasferimento comando "Copia formato" sulle cellule che avete bisogno e combinare:

Come costruire una sintesi delle diverse fonti di dati

Se avete bisogno di costruire un consolidato da più fonti di dati, è necessario aggiungere al nastro o la barra di accesso rapido "Creazione guidata tabella pivot e grafici" in cui v'è una tale opzione.

È possibile farlo nel modo seguente: "File» → «Impostazioni» → «barra di accesso rapido» → «Tutta la squadra» → «creazione guidata tabella pivot e diagrammi» → «Aggiungi»:

Dopo di che, il nastro sarà l'icona corrispondente, facendo clic sul quale fa sì che l'aggiunta del maestro:

Quando si fa clic su di esso, una finestra di dialogo:

In esso si dovrà selezionare "A poche band di consolidamento" e fare clic su "Avanti". Nella sezione successiva, è possibile selezionare l'opzione "Crea un singolo campo pagina" o "Creare un campo pagina." Se volete voi stessi a venire con un nome per ciascuna delle fonti di dati - per selezionare la seconda voce:

Nella casella successiva, aggiungere tutti gli intervalli in base ai quali costruire una sintesi, e chiedere loro i nomi:

Dopo quel punto, ospiterà un rapporto di tabella pivot nell'ultima finestra di dialogo - su un un nuovo foglio esistente o:

Rapporto di tabella pivot pronto. Il filtro "Page 1" è possibile scegliere solo una delle fonti di dati, se necessario:

Come calcolare il numero di occorrenze del testo nel testo di un B (SuperMTS «MTS tariffarie" - due occorrenze della MTS abbreviazione)

In questo esempio, colonna A, ci sono alcune righe di testo, e il nostro compito - per scoprire quante volte ciascuno di essi rispetti il ​​testo richiesto nella cella E1:

Per risolvere questo problema, è possibile utilizzare una formula complessa che consiste delle seguenti funzioni:

1. LEN (LEN) - calcola la lunghezza del testo, un singolo argomento - il testo. Esempio: LEN ( "macchina") = 6.
2. SOSTITUTO (supplente) - sostituisce la stringa di testo di un determinato testo con un altro. Sintassi: SOSTITUTO (il testo; star_tekst; nov_tekst). Esempio: SOSTITUTO ( "auto", "auto", "") = "mobile".
3. UPPER (superiore) - sostituisce tutti i caratteri di una stringa in lettere maiuscole. L'unico argomento - il testo. Esempio: UPPER ( "macchina") = "MACCHINA". Questa funzione sarà necessario per rendere la ricerca case-insensitive. Dopo tutto, UPPER ( "Macchina") = UPPER ( "Macchina")

Per trovare un certo ingresso di una stringa di testo ad un altro, è necessario rimuovere tutte le sue occorrenze in originale e confrontare la lunghezza della stringa dall'originale:

LEN ( "Tariffa Super MTS MTS") - LEN ( "Super Rate") = 6

E poi dividere la differenza per la lunghezza della linea, che stavamo cercando:

6 / LEN ( "MTS") = 2

Questo è due volte la linea "MTS" è nell'originale.

Resta da scritto nel linguaggio delle formule algoritmo (indicata "testo" il testo che stiamo cercando di ingresso e "desiderata" - il numero di occorrenze che siamo interessati a):

= (LEN (testo) -DLSTR (riserva (UPPER (testo); UPPER (ricercato per); ""))) / LEN (ricercato per)

Nel nostro esempio, la formula è la seguente:

= (LEN (A2) -DLSTR (SOSTITUTO (SUPERIORE (A2); SUPERIORE ($ E $ 1); ""))) / LEN ($ E $ 1)