Excel-lifehacks para os envolvidos no processamento de relatórios e dados
Dicas / / December 19, 2019
Renat não é a primeira vez que um autor convidado fala sobre Layfhakere. Anteriormente, publicamos um excelente material com ele sobre como criar um plano de formação: principal livro e recursos on-line, bem como algoritmo incrementais criar um plano de formação.
Este artigo contém algumas técnicas simples que ajuda simplificar o trabalho em Excel. eles são particularmente úteis para aqueles que estão envolvidos na elaboração de relatórios de gestão, prepara uma variedade de analítica relatórios baseados nos desembarques de relatórios 1C e outras formas de estas apresentações e diagramas para liderança. Não fingir ser uma novidade absoluta - de uma forma ou de outra, esses métodos certamente discutido em fóruns ou referido no artigo.
alternativa simples para PROCV e PROCH, se os valores necessários não estão na primeira coluna da tabela: VIEW, INDEX + JOGO
PROCV (PROCV) E PGR (PROCH) só funcionam se os valores-alvo estão na primeira coluna ou linha da tabela a partir do qual você deseja obter dados.
Em outros casos, há duas opções:
- use a função VISTA (PROC).
Ela tem a seguinte sintaxe: observação (valor_procurado Pesquisar vector; vektor_rezultata). Mas para que ele funcione corretamente, você precisa para valores de faixa pesquisa vector Eles foram classificados em ordem crescente: - FÓSFORO usar uma combinação de funções (FÓSFORO) e um índice (INDEX).
função JOGO retorna o número de série do elemento na matriz (com a sua ajuda você pode encontrar, em uma linha da tabela o elemento desejado), e função índice retorna um elemento de matriz com um número predeterminado (que sabemos a função JOGO).opções de sintaxe:
• MATCH (valor_procurado massiv_poiska; tipo_correspondência) - no nosso caso, precisamos de um tipo de comparação "correspondência exata", que corresponde ao número 0.
• INDEX (matriz; núm_linha; [Column_num]). Neste caso, não é necessário especificar o número da coluna como a matriz é constituída por uma única linha.
Como rapidamente para preencher as células vazias na lista
A tarefa - para preencher as células com os valores na coluna acima (sujeito a pé em cada linha da tabela, não só na primeira linha bloco de livros sobre o assunto):
Selecione a coluna "Assunto", pressione na fita no grupo de botão "Home" "Localize e selecione» → «Realce o grupo de células» → «em branco célula "e começar a introduzir a fórmula (isto é, colocar um sinal de igual) e refere-se à célula a partir de cima, por simplesmente pressionando a seta para cima teclado. Em seguida, pressione Ctrl + Enter. Depois disso, os dados podem ser salvos como um valor, porque as fórmulas não são mais necessários:
Como encontrar erros na fórmula
Cálculo fórmula parte separada
Para compreender o complexo fórmula (em que como argumentos da função usando outra função, ou seja, algumas funções estão embutidos no outro), ou para encontrar nele uma fonte de erros, muitas vezes necessidade de calcular parte dela. Há duas maneiras fáceis:
- Para calcular a parte direita da fórmula na barra de fórmulas, selecionar a parte, e pressione F9:
Neste exemplo, houve um problema com a função SEARCH (PESQUISA) - havia argumentos para cima mistos. É importante lembrar que, se você não cancelar o cálculo da função e pressione a tecla Enter, em seguida, a parcela calculada permanecerá número. - Pressione o botão "calcular a fórmula" no grupo "fórmula" na fita:
Na janela que aparece, você pode calcular o passo fórmula a passo e para determinar em que fase e em que função de erro (se houver):
Como determinar o que determina que a fórmula ou referenciados
Para determinar qual das células depende da fórmula no grupo da "fórmula" na faixa de opções, clique no botão "Rastrear precedentes":
Há setas apontando a partir do qual o resultado depende.
Se o símbolo aparece na imagem selecionada em vermelho, a fórmula depende de células que estão em outras listas ou em outros livros:
Clicando sobre ele, podemos ver exatamente onde a influência da célula ou intervalo:
Ao lado do "Rastrear precedentes" há um botão "dependentes", trabalhando da mesma maneira: ele exibe setas da célula ativa com a fórmula para células que dependem dele.
Botão "Remover setas", localizado no mesmo bloco, permite remover precedentes, dependentes, ou os dois tipos ao mesmo tempo setas:
Como encontrar a quantidade (número, média) valores de células com várias folhas
Vamos dizer que você tem vários folha semelhante com os dados que você deseja adicionar, encontrar ou processados de alguma outra forma:
Para fazer isso, a célula na qual você deseja ver o resultado, introduza a fórmula padrão, como SUM (SOMA), e insira o argumento seguido por dois pontos e o último nome da primeira folha da lista de folhas necessárias processo:
Você receberá a quantidade de B3 endereço de célula com folhas "Data1", "Data2", "data3":
Tais obras de endereçamento para folhas dispostas sucessivamente. A sintaxe é: = (FEATUREpervyy_list: referência posledniy_list para o intervalo!).
Como construir automaticamente um frases estereotipadas
Usando os princípios básicos do trabalho com texto em Excel e algumas funções simples, pode ser frases estereotipadas preparados para relatórios. Vários princípios do trabalho com o texto:
- Combine texto com a marca e (pode substituir a sua CONCATENATE função (CONCATENATE), mas isso não é muito útil).
- O texto é sempre escrita entre aspas, as referências de célula com o texto - nem sempre.
- Para obter os caracteres "aspas" especiais, utilizando a função de caracteres (CHAR) Alegação 32.
EXEMPLO criar frases molde usando fórmulas:
resultado:
Neste caso, para além da função SÍMBOLO (CHAR) (para exibir as aspas) usando a função IF (IF), que permite alterar o texto em Dependendo se há uma tendência positiva nas vendas e a função do texto (texto), o que permite exibir um número em qualquer formato. Sua sintaxe é descrito abaixo:
TEXT (valor; formato)
O formato é especificado entre aspas, da mesma forma como se você entrou em um formato personalizado no "Format Cells."
Você pode automatizar textos mais complexos. Na minha prática era para automatizar muito tempo, mas os comentários de rotina sobre as contas dos administradores no formato "número caiu / rosa no XX sobre o plano, principalmente devido ao aumento / diminuição do FAKTORA1 XX, aumento / diminuição na FAKTORA2 YY... »com a lista mudando fatores. Se você escrever tais comentários muitas vezes eo processo de escrita pode algoritmização - vale a pena uma vez intrigado criar uma fórmula ou macro que irá salvá-lo de, pelo menos, parte do trabalho.
Como salvar os dados em cada célula após a fusão
Ao combinar as células reteve apenas um valor. Excel avisa quando você tenta células de mesclagem:
Assim, se você tivesse uma fórmula que depende de cada célula, ele deixará de trabalho após sua união (bug # N / A em linhas 3-4 exemplos):
Para células de mesclagem, preservando os dados em cada um deles (talvez você tem uma fórmula, como neste exemplo, o resumo; você pode querer mesclar células, mas manter todos os dados no futuro, ou para esconder as suas intenções), combinar qualquer célula em folha, selecioná-los e, em seguida, usar o formato de transferência "Pincel" de comando sobre as células que você precisa e combinar:
Como construir um resumo das várias fontes de dados
Se você precisa para construir um consolidou a partir de múltiplas fontes de dados, é necessário adicionar à fita ou a Barra de Acesso Rápido "Assistente de tabela dinâmica e gráficos" em que há uma tal opção.
Você pode fazer isso da seguinte forma: "File» → «Configurações» → «Quick Access Toolbar» → «Toda a equipa» → «Assistente de Tabelas Dinâmicas e diagramas» → «Adicionar»:
Depois disso, a fita vai ser um ícone correspondente, clique sobre o que faz a adição do mestre:
Quando você clicar nele, uma caixa de diálogo:
Nele você precisará selecionar "Algumas bandas de consolidação" e clique em "Next". Na próxima seção, você pode selecionar a opção "Criar um único campo de página" ou "Criar um campo de página." Se você quiser se chegar a um nome para cada uma das fontes de dados - para selecionar o segundo item:
Na próxima caixa, adicionar todas as faixas com base na qual construir um resumo, e pedir-lhes os nomes:
Após esse ponto, vai acolher um relatório de tabela dinâmica na última caixa de diálogo - em uma nova folha de cálculo existente ou:
Relatório de tabela dinâmica pronta. O filtro "Página 1" você pode escolher apenas uma das fontes de dados, se necessário:
Como calcular o número de ocorrências do texto no texto de A B (SuperMTS «MTS tarifárias" - duas ocorrências da MTS abreviatura)
Neste exemplo, a coluna A, há algumas linhas de texto, e nossa tarefa - para descobrir quantas vezes cada um deles se encontra com o texto desejado na célula E1:
Para resolver este problema, você pode usar uma fórmula complexa que consiste nas seguintes funções:
- LEN (LEN) - calcula o comprimento do texto, um único argumento - o texto. Exemplo: LEN ( "máquina") = 6.
- Suplente (Suplente) - substitui a cadeia de texto de um determinado texto com outro. Sintaxe: substituto (texto; star_tekst; nov_tekst). Exemplo: substituto ( "carro", "carro", "") = "mobile".
- UPPER (superior) - substitui todos os caracteres em uma string para maiúsculas. O único argumento - o texto. Exemplo: SUPERIOR ( "máquina") = "MÁQUINA". Este recurso será necessário para fazer case-insensitive de pesquisa. Afinal, UPPER ( "Máquina") = UPPER ( "Máquina")
Para localizar um determinado item de uma cadeia de texto para outro, você deve remover todas as suas ocorrências no original e comparar o comprimento da corda a partir do original:
LEN ( "Tarifa Super MTS MTS") - LEN ( "Super Taxa") = 6
E depois dividir a diferença pelo comprimento da linha, que estávamos procurando:
6 / LEN ( "STM") = 2
Que é duas vezes a linha "MTS" está no original.
Ele continua a ser escrito na linguagem das fórmulas algoritmo (denotado "texto" o texto que estamos buscando entrada e "desejado" - o número de ocorrências que estamos interessados em):
= (LEN (texto) -DLSTR (SUBSTITUTO (SUPERIOR (texto); SUPERIOR (procurada); ""))) / LEN (procurada)
No nosso exemplo, a fórmula é a seguinte:
= (LEN (A2) -DLSTR (SUBSTITUTO (SUPERIOR (A2); SUPERIOR ($ E $ 1); ""))) / LEN ($ E $ 1)