Transcrições
1. Introdução: Oi, meu nome é George, e estou aqui para te ensinar as habilidades para se tornar muito mais proficiente em usar o Excel. Neste curso, mostrarei a você como criar seu próprio rastreador de negócios, planilha de
Lucros e Perdas para elevar seu negócio e acompanhar seu crescimento. C ou rendimentos, despesas e lucros e perdas. Facilmente. Acompanhe seu inventário de produtos e atualize-o automaticamente. Finalmente, personalizado para se adequar à sua marca e negócio. Mas o que é mais importante são as habilidades aprendidas cabelo pode ser transferido para tantas tarefas diferentes dentro do Excel. O curso é dividido em cinco seções e você terá uma pasta de trabalho fornecida para permitir que você acompanhe facilmente. Mostrarei a você como criar painéis dinâmicos e alterados automaticamente dependendo dos dias que você inserir. Você melhorará algumas habilidades fundamentais do Excel e aprenderá funções como o uso de intervalos nomeados, Formatação
Condicional e toneladas de fórmulas diferentes. O que queria mostrar cabelo era difícil de usar muitas das funções
do Excel que você tem disponível em um produto verdadeiramente útil. Após este curso, você não só terá uma ferramenta fácil de usar e simples para acompanhar o seu negócio? Mas você também terá aprendido habilidades que muitos usos avançados de XR desconhecem. E agora vamos começar.
2. Aula 1: categorias: Neste primeiro vídeo, criaremos nossas categorias para usar dentro do restante da pasta de trabalho. Vamos criá-lo para que você possa adicionar até 20 tipos de receita diferentes, tipos de
despesas, tipos de transação e produtos. A coisa realmente legal que você aprenderá hoje é como criar intervalos nomeados
dinâmicos para todas as suas categorias. A razão pela qual usaremos essas categorias como listas suspensas em outras guias. E ninguém quer ver muitos espaços em branco na lista suspensa. As habilidades aprendidas inferno, ser transferível para muitos cenários diferentes. Então, primeiro, abra sua pasta de trabalho do rastreador de negócios e vá para a guia Categorias. Então vamos começar. Primeiro. Vamos criar nossos tipos de receita. Para facilitar as coisas. Eu destacou células em laranja onde você deve inserir dados para tipos de receita vai iniciá-lo antes. Aumentará os tipos de receita e os chamaremos de vendas de produtos e marketing de afiliados. Em seguida, vamos adicionar alguns tipos de despesas. A partir do S4 entrará em taxas de transação, fase de
publicidade, custos de produtos e taxas de envio. Mover para tipos de transação irá inserir alguns tipos comuns, como renda, despesa, saldo e transferência. Agora, passando para a seção de produtos, vamos adicionar alguns nomes e categorias de produtos fictícios. Como uma inicialização entrará, não aplicável tanto no nome do produto quanto na categoria do produto. Não tem todas as transações serão relacionadas a um produto e queremos essa flexibilidade. Em seguida, vamos inserir sofá, mesa, cama, roupeiros e mesa como os nomes dos produtos na coluna F. E finalmente, vamos criar as seguintes categorias na coluna G. Agora, como mencionado antes, no final deste curso, você será capaz de inserir mais ou eu quis dizer as categorias que entramos para tornar isso útil para você. No entanto, para o curso seguro você manter para os mesmos que eu tenho entrado. Agora vem o bit interessante, como criar intervalos nomeados dinâmicos começará criando um intervalo nomeado normal. Vamos destacar assim antes de B203 e nomear esse intervalo de
renda de células indo para a caixa de endereço no canto superior esquerdo e inserindo renda. Agora, a razão pela qual estamos criando esses intervalos nomeados é muito fácil. Consulte elas em outras planilhas em listas suspensas. Vou mostrar-lhe um exemplo de uma lista suspensa e os benefícios de um intervalo nomeado dinâmico. Para aqueles de vocês que querem uma introdução em intervalos nomeados,
por favor, confira o meu Curso para Iniciantes. Eu tenho uma seção sobre ele, a célula K4, e eu vou destacá-lo em uma cor diferente rapidamente apenas para torná-lo mais visível. Em seguida, irá para dados e validação de dados em Permitir, selecione lista. E, em seguida, o tipo de origem. E dez é igual a renda. Você notará que a caixa suspensa tem muitas células em branco, o que não é ótimo e pode causar problemas. Então agora vamos criar um intervalo nomeado dinâmico para que apenas as células não-em branco serão nítidas usando a função offset. Deixe-me mostrar-lhe como esta função funciona primeiro, a função offset retorna uma célula ou intervalo de células que é um número especificado de linhas e colunas de uma célula ou intervalo de células. Comece a digitar deslocamento igual. Abre parênteses. O primeiro argumento é a célula inicial que queremos fazer referência let Center antes como um exemplo. Em seguida, o próximo argumento é o número de linhas para cima ou para baixo a partir da primeira célula. Vamos entrar 0 como queremos começar de muito, para. O próximo argumento é o número de colunas. Mais uma vez, vamos inserir 0 como queremos referência coluna b. Vamos então inserir a altura, que vamos fazer dinâmica usando a contagem de uma fórmula na coluna B. Contagem a contará todas as células que contêm dados. E, portanto, à medida que adicionamos novas categorias, isso mudará. Então, insira count a, abra parênteses e realce Coluna B e feche parênteses. E uma vez que temos um título que será contado, então entraremos menos1 depois disso. E finalmente, para a largura, você vai inserir um, pois queremos apenas uma coluna para ser retornada. Em seguida, feche o parêntese. Como você pode ver, as únicas categorias que entramos são exibidas. À medida que adicionamos ou removemos uma categoria, você verá que o que é exibido é atualizado automaticamente. Agora vamos atualizar nosso intervalo nomeado com esta fórmula. Primeiro, vá para fórmulas e depois gerenciador de nomes. Selecione a faixa nomeada de renda e vá para a caixa de fórmula na parte inferior. Aqui, você usará o comando Offset para criar um intervalo nomeado dinâmico. Então digite igual deslocamento, parênteses
abertos, categorias, ponto de exclamação. Antes, como queremos fazer referência à planilha. E vamos colocar uma referência absoluta em torno disso em 0 para os argumentos de linha e coluna. E para contar um, parênteses
abertos, categorias ponto de exclamação, coluna B com uma referência absoluta,
em seguida, feche parênteses e um menos um para o argumento height. E finalmente, um para o argumento de largura. Como atalho, você poderia ter copiado a fórmula que
criamos um m3 e apenas colá-lo isso também. E, finalmente, clique no carrapato verde. Agora olhando para trás para a lista, podemos ver que contém apenas as categorias que definimos. E se adicionarmos mais, a lista será atualizada automaticamente. Vamos agora criar os intervalos nomeados restantes começando com despesa. Volte para o gerente nomeado da Fórmula. E vamos copiar a fórmula que usamos para a faixa de renda para acelerar isso. Então me selecione. Digite despesa na fórmula de renda. Altere a referência para C4. E contam um alcance para ver e acertar OK. Faremos o mesmo para tipos de transação. Vai nomear este tipo T. E vamos mudar a referência para D4 e a contagem de um intervalo para D. O mesmo para o nome do produto, vai nomear este nome orgulhoso. E vamos mudar a referência para F4. E eles contam um intervalo para F. E, finalmente, a categoria de produto nomeará este gato orgulhoso e mudará a referência para G4. E eles contam um alcance para G. E aí está. Criamos nossas categorias para serem usadas em toda a pasta de trabalho usando intervalos nomeados dinâmicos e a função de deslocamento no Excel. Também apresentei outras funções, como listas de validação de dados. Estes são apenas alguns que você estará usando ao longo deste curso. Continuem avançando. E, no final, você terá uma ótima planilha de rastreamento de
lucros e perdas.
3. Aula 2: metas: Neste próximo vídeo, criaremos as metas que serão
vinculadas às nossas categorias que definimos
no vídeo anterior e nos permitirão definir uma meta de receita ou despesa específica para cada categoria. Nós estaremos usando se afirmações bastante extensivamente aqui. Assim, introduz aqueles que não o usaram para uma nova função. Primeiro, você pode abrir o negócio, rastrear uma pasta de trabalho e ir para a guia de destinos? Antes de começarmos, já preenchi algumas fórmulas como mostrado aqui. Estes irão simplesmente somar quaisquer dados que introduzirmos para resumir as metas de receita e despesa para cada período. Então vamos começar adicionando nossas categorias. Queremos que isso seja dinâmico e atualize a partir da guia Categorias. Mas já fizemos o trabalho árduo e podemos referir a um dos nossos intervalos nomeados que criamos. Então vamos começar indo para vender BY 13 e digite renda igual. Você pode ver que as categorias de renda que definimos agora estão sendo exibidas. Como nos vídeos anteriores. Destacei todas as células em laranja como uma célula onde o usuário insere texto livre ou números. Digamos que para vendas de produtos temos uma meta de 1000 por mês. Então vamos escrever um 1000 em d 13. E para marketing de afiliados, temos uma meta de 100 por mês. Então insira um 100 em d 14 usará isso para calcular os alvos semanais, trimestrais, anuais e completos do histórico. Primeiro, vamos usar uma instrução if para calcular o alvo semanal, que é o valor mensal, dividido por 4.3. Como há 4,3 semanas em um mês. Vá para c 13 e insira igual, se abrir parênteses. Se d 13 for igual a branco, definido por estas aspas, em
seguida, em branco para encontrar pelas aspas novamente. Caso contrário d 13 dividido por 4.3. E arrasta isso para baixo. A seguir, o trimestral. Vá para E 13 e digite igual se abrir parênteses, o 13 é igual a branco. Então em branco. Caso contrário d 13 vezes três. E o ano vai para F 13 e insira f igual, parênteses
abertas, d 13 é igual a branco, depois em branco. Caso contrário d 13 vezes 12. E finalmente, a história completa. Agora, esta será uma fórmula mais complicada. Como sua história completa não é uma constante. Pode ser de três meses ou três anos. Então precisamos ser um pouco espertos para calcular o alvo para o mesmo período de tempo. Precisaremos encontrar o número de dias que você está negociando. Divida isso por 365
e, em seguida, multiplique isso pela sua meta de renda anual. Vou explicar-te isso agora. Comece o mesmo que antes. Vá para g 13 e insira igual f, abra parênteses, d 13 é igual a branco, depois em branco. Agora, a fim de encontrar o número de dias que você está negociando, podemos usar a função min e max em seu histórico de transações. Digite, max, abra parênteses, e então clique nas transações que estamos na verdade e na coluna B.
E , em seguida, pressione F4 uma vez para colocar uma referência absoluta em torno disso, modo que quando copiarmos esta fórmula, ela ainda vai coluna de referência B. Em seguida, digite subtrair e, em seguida, MIN abrir parênteses. E novamente faça referência à coluna B e pressione F4 uma vez para colocar uma referência absoluta. Coloque essas fórmulas entre parênteses. Em seguida, divida por 365 e coloque essas fórmulas entre parênteses novamente. E, finalmente, multiplique por F 13
na planilha de alvos e feche parênteses. E apenas arraste até lá para baixo você tem. Depois de preencher os dados das transações, seus alvos de histórico completo serão atualizados automaticamente. Vamos agora fazer o mesmo para as categorias de despesas. Digite igual despesa no IEEE 13 para fazer referência à despesa chamada intervalo que criamos no vídeo anterior. Em seguida, insira as metas de despesa mensais. Vamos colocar alguns números falsos e garantir que eles são negativos. Então coloque menos 50 por mês para a fase de transações, menos 200 por mês para publicidade, fase, menos 500 por mês para o produto, fase menos 100 por mês para as taxas de envio. Agora, para inserir as fórmulas, podemos simplesmente copiar as fórmulas que fizemos para renda. Selecione C 13 TC 30 para copiar as células e colá-las em J 13. Agora faça o mesmo para as fórmulas de história trimestral, anual e completa. Você pode ver para o histórico completo, nós ainda estamos referindo a coluna B. Se nós não colocamos uma referência absoluta e copiamos isso entre as colunas teria mudado. Meu curso para iniciantes também abrange referências absolutas e relativas. Então, por favor, verifique se você precisa de uma apresentação. E, finalmente, vamos preencher a meta de receita anual e a meta de lucro anual. Para a meta de receita anual, vá para F4 e tipo igual a F 12. Para a meta de lucro anual, vá para L4, tipo igual F2 mais m2. E esses foram os alvos realmente concluídos. Neste vídeo, você viu como as declarações podem ser eficazes para completar várias tarefas. Também usaram algumas fórmulas relativamente complexas, especialmente ao tentar calcular os alvos do histórico completo. Na próxima sessão, vamos estar passando pelas transações foram realmente que nós brevemente abordados hoje.
4. Aula 3: transações: Entrar. Se o parêntese aberto E5 igual a branco com as aspas, então prancha. Caso contrário, alguns parênteses abertos. Selecione K4, I5
e, em seguida, menos J5. E entre parênteses. Copie isto para baixo para uma linha de 100. Isso irá resumir todas as suas transações de entrada e saída para fornecer um valor total do saldo. Agora, em vez de você ter que inserir transações manualmente, eu criei algumas transações fictícias que você pode copiar e colar valores nesta planilha. Para os arquivos de transações salvos no projeto e copie e cole valores. Todas as transações como psi. Basta lembrar, não cole sobre a categoria de produto ou fórmulas de saldo total. E aí está. Neste vídeo, abordamos alguns usos mais complexos da validação de dados. Se instruções e pesquisas V. Agora você também tem uma transação de trabalho C, que será o principal driver para as próximas planilhas. No próximo vídeo, criaremos nosso painel de inventário de produtos.
5. Aula 4: produtos: Neste vídeo, criaremos seu painel de inventário de produtos criaremos para que a planilha seja preenchida automaticamente à medida que você adiciona transações. Você não precisa se preocupar em manter o controle manual de sua lista de produtos. Ele será conduzido diretamente de suas transações. Primeiro abriu a pista de negócios uma pasta de trabalho e vá para a guia produto. Nesta worksheet estará usando intervalos nomeados, SUMIFS, e é funções de erro para garantir que as fórmulas atendem a qualquer cenário. E você deve ter notado que as células estão destacadas em cinza. Isso segue o mesmo formato que os vídeos anteriores. Células cinzentas indicam que irá introduzir uma fórmula aqui. Então vamos começar com o nome do produto. Como em vídeos anteriores, podemos extrair todos os nomes de produtos exclusivos fazendo referência ao intervalo nomeado. Vá para B5 e digite o nome orgulhoso igual. Para as categorias de produtos, vamos inserir se combinação de fórmula vLookup k2, C5, e inserir igual f, parêntese aberto. B5 está em branco. Então em branco. Caso contrário, VLookup, abra o parêntese B5 para o valor de pesquisa, que é o nome do produto, e as categorias, FTG para a matriz de tabela. E puxar três colunas dois, e então ir para uma correspondência exata. E, em seguida, para fechar parênteses. Agora, arraste esta fórmula para o fundo. Para as colunas a seguir, criaremos inicialmente uma versão mais simples que calculará números com base no histórico completo de transações. Mas no próximo vídeo, vamos nos certificar de que é ainda mais dinâmico e esses números sejam atualizados com base no período de tempo que selecionamos. Para. Agora, vamos primeiro calcular o total comprado. Vá para D5 e insira f igual, abra parênteses. B5 está em branco. Então em branco. Caso contrário, usamos alguma fórmula que nos permite resumir dados com base em critérios específicos. Então vamos inserir o intervalo de soma como a coluna Quantidade na planilha de transações. E o primeiro intervalo de critérios é a coluna de nome do produto. E os critérios serão o nome do produto no produto. Estamos realmente o próximo critério é a coluna tipo de transação na planilha de transações. E os critérios que vamos hard-code na fórmula como despesa. Lembre-se de colocar entre aspas duplas. Agora vamos colocar algumas referências absolutas e misturar nas células para garantir que elas estão bloqueadas no lugar correto quando rastreamos ou copiamos as fórmulas. Por favor, consulte a minha lição sobre referências celulares. Se isso estiver indo rápido demais. E vamos arrastar isso até o fundo. Para o total de produtos vendidos irá copiar a fórmula através Como só precisamos fazer uma pequena alteração. Vamos mudar a despesa para a renda como qualquer célula virá como renda. E vamos arrastar isso para baixo. Para inventário mantido é simplesmente a diferença entre sua tartaruga comprada e sua tartaruga vendida. Então CO2, F5, e entre igual f, parênteses
abertas. B5 está em branco. Então em branco. Caso contrário, D5, compras
totais menos E5, sua tartaruga vendida. E, finalmente, para a classificação de produtos vendidos, podemos usar a função de classificação no Excel aqui. Mas um problema com a função de classificação é que ela não atende a cenários com os números são os mesmos. Então, há duplicatas. Deixe-me primeiro mostrar-lhe como funciona a função de classificação. Vamos para a célula G5 e em posição igual, parênteses abertas. O primeiro argumento é o número que estamos tentando classificar, que é o total vendido para o primeiro produto. Então, selecione E5. O segundo argumento é organizado. Então, selecione E5 para E3, E4 e coloque em referência absoluta pressionando F4. O terceiro argumento é a ordem. Então, queremos que o maior número tenha classificação um. Então digite 0 aqui e arraste isso para G dez. Você pode ver que o sofá e o guarda-roupa tinham o mesmo número de células e o mesmo posto. E também estamos perdendo um posto cinco da lista. Isso destaca o problema em que estamos tentando classificar números duplicados. Podemos contornar isso fazendo uma pequena adição à fórmula. Insira a contagem de adição se abrir parênteses para o seu intervalo em E5 a E5, mas coloque em referência absoluta em torno do primeiro E5. Para os critérios, selecione E5 novamente, feche o parêntese. E, em seguida, menos um parêntese próximo. Arraste isso para baixo para g dez. E como você pode ver, o sofá e o guarda-roupa têm uma classificação diferente, embora eles tenham o mesmo número de vendas. Esta é uma maneira muito útil de classificar dados com duplicatas. Precisamos de alterar esta fórmula apenas um pouco mais para ter em conta quaisquer espaços em branco. Então vá para o início e crie uma instrução if
que indique quando o nome do produto está em branco, exiba um espaço em branco. Caso contrário, calcule a classificação como say, e arraste isso para baixo. E é assim que você cria um painel de inventário de produtos é orientado a partir de suas transações. Você pode ir mais longe e adicionar cálculos para o custo médio do produto, vendas, lucros, valores de estoque. Para tornar isso ainda mais útil. No próximo vídeo, criaremos o painel principal. Este é o vídeo final do curso e
amarrará todas as planilhas que você criou até agora. Este painel conterá gráficos para sua receita, despesas e produtos. Também será totalmente dinâmico, permitindo que você escolha seus períodos de tempo. E todos os gráficos e figuras serão atualizados de acordo.
6. Aula 5: Painel principal: Este é o vídeo final que vai trazer tudo o que você fez nos vídeos anteriores juntos. Todos os gráficos e dados serão dinâmicos. E você terá a opção de alterar os períodos de tempo e fazer com que todos os números e gráficos sejam atualizados automaticamente. Então vamos começar. Primeiro, você pode abrir a pasta de trabalho do rastreador de negócios e ir para a guia principal do painel? Esta folha é pré-preenchida com alguns dados simples para você começar. Mas faremos a maior parte do trabalho juntos para colocar esse painel em funcionamento. Primeiro, vamos começar com o canto superior esquerdo. Como sempre, as células laranja são células. Nós inserimos texto ou números. As células azuis contêm caixas suspensas onde você selecionará dados. E as células cinzentas serão onde você insere fórmulas. Vamos para a célula D7 e inserir a data do primeiro de janeiro de 2019. Em seguida, vamos criar a lista suspensa do período de tempo. Selecione D8, K2, Validação de Dados, selecione a lista. E na fonte, entraremos manualmente nossas opções de lista. Para a fonte, vamos inserir semanalmente, vírgula, mensal, vírgula, vírgula
anual e histórico completo, e selecionar OK. Como você pode ver, eles agora estão disponíveis na lista suspensa. Para a data de início, precisaremos inserir uma instrução if para atender a opção de histórico completo. Portanto, se o período selecionado for igual ao histórico completo, queremos que a data de início seja o primeiro dia da transação na página de transações. Caso contrário, queremos que a data de início seja a inserida em D7. Isso pode ser feito da seguinte forma. Digite igual, se parênteses abertos, D é igual ao histórico completo entre aspas. Então entre os homens. Abre parênteses. Vá para a planilha de transações e selecione a coluna B. Caso contrário, selecione D7, que é o dia inicial. Vamos também nomear esta célula como sublinhado de data começa, pois é muito mais fácil fazer referência a um intervalo nomeado. Para a data de término, precisaremos de várias declarações if para atender a cada período de tempo. Então vamos começar. Se o período de tempo for igual a semanal. E a data final é a data de início mais seis. período de tempo é igual a mensal. E vamos querer usar a função de data. E o ano será o mesmo que a data começar. O mês seria a data de início mais um. E o dia será a data de início menos1. Se o período de tempo. Podemos copiar a mesma fórmula que usamos mensalmente, mas basta mudar o mês para 12 meses. E, finalmente, para o histórico completo, vamos tomar o estado máximo na planilha de transação. E aí está. Foi uma fórmula bastante longa, mas permite que você atenda para todos os períodos de tempo. E como um passo final, vamos nomear esta célula como sublinhado de data. E eu gosto de fazer mais uma pequena atualização que irá apresentá-lo à formatação condicional. Como o intervalo de datas completo do histórico não exigirá que você insira um dia de início, seria melhor esvaziar o que é viável na célula D7 para evitar qualquer confusão, isso pode ser feito facilmente com a formatação condicional. Basta realçar a célula D7. Vá para a formatação condicional na guia Início. Selecione nova função. Vá para usar a fórmula para determinar quais células formatar. Onde ele declara valores de formato onde essa fórmula é verdadeira. Insira D8 igual para histórico entre aspas. Em seguida, selecione Formatar e digitou a cor de preenchimento e fonte tem a mesma cor. Vamos com azul escuro e depois acertamos OK. Agora você pode ver que, quando o histórico completo selecionado, a célula de data de início dos relatórios muda de cor e ela não fica mais visível. Em seguida, vamos preencher alguns dos dados na parte inferior, começando com a receita. Vá para a cela C42 T1. E vamos usar nossa renda chamada faixa para preencher os fluxos de renda. Para metas, vamos precisar puxar através das metas para
o tipo de receita correto com base nos períodos de tempo selecionados. Então, vamos usar uma combinação da função de índice e a função de correspondência para fazer isso. Selecione o índice igual do tipo 4.2.1, entre parênteses. Vá para a planilha de alvos. E a matriz será b 11 a G, 32 na folha de destino. E vamos colocar em referência absoluta em torno disso. Para o número da linha, precisaremos procurar o tipo de receita. Então, para fazer isso, digite, corresponda o parêntese aberto para o valor de pesquisa de volta ao painel principal e selecione o tipo de receita em C41. E estamos olhando esse valor para cima na coluna B no alvo xi. Então selecione b 11 para B32 como a matriz de pesquisa e coloque uma referência absoluta em torno disso. Para o tipo de correspondência, queremos uma correspondência exata. Então coloque 0 e feche parênteses. E para o número da coluna, fomos fazer referência à coluna que corresponde
ao período selecionado no painel principal. Então insira correspondência, abra parênteses. Como o Valor de Pesquisa, selecione,
desvie-se do painel principal e coloque em referência absoluta em torno disso. E para a matriz de pesquisa, selecione B11 para G11 e coloque uma referência absoluta em torno disso. Para a partida, queremos uma exata. Então coloque 0 e feche o parêntese duas vezes e arraste essa fórmula para baixo. Agora você vai notar que temos um monte de NAS que queremos nos livrar. Isso pode simplesmente ser feito com uma declaração if. Então vamos atualizar a fórmula. Selecione a célula D 41
e, no início, à direita, se abrir parênteses, selecione C 41, o tipo de receita é igual a branco, em
seguida, em branco e coloca outro parêntese de fechamento no final. Agora, se você arrastar esta fórmula para baixo, nós nos livramos dos NA. Para a receita real, precisaremos usar
a soma é fórmula para procurar o trabalho transações ela e somar todas as transações com base no tipo de receita para o período de tempo específico. Isso é um pouco mais complicado, mas eu vou te explicar. Selecione y4, z1 e tipo é igual a sumifs, parênteses abertos. Para o intervalo de verão, vá para a planilha de transações e selecione a coluna Receita, coluna i, e coloque uma referência absoluta em torno disso. Para o intervalo de critérios um, selecione o subtipo de transações na planilha de transações, coluna D e, em seguida, coloque uma referência absoluta. Quatro critérios. Um, volte para o painel principal e selecione o tipo de receita na faixa de critérios C
414 para ir para a planilha de transações e selecione a coluna de data, coluna B e F Para, para uma referência absoluta. Para os nossos critérios de data, queremos apenas somar as transações que estão entre as datas de início e término. Então precisamos dividir isso em duas criterias separadas. Para os critérios, queremos apenas alguns QUALQUER dias que são maiores ou iguais a uma data de início. Então, vamos precisar inserir aspas. O sinal de maior que. Igual a aspas, o símbolo n. E então queremos referenciar R estrela a. Então digite data sublinhado começar como este era o nome do dia de início que definimos no painel principal. Para o intervalo de critérios três, selecione a coluna de data, coluna b. Mais uma vez, queremos apenas alguns, QUALQUER dias que são menores ou iguais à data. Vamos precisar inserir aspas. O símbolo menor que é igual a aspas e ao símbolo n. E queremos fazer referência ao nosso fim do dia. Então digite data underscore end, como este era o nome da data final que definimos no painel principal. Sei que foi muito, mas estamos quase lá. Para atender a cenários em que você pode ter tipos de transação negativos para renda, IE, devoluções ou reembolsos. Queremos garantir que estes sejam também contabilizados. Então insira um sinal de menos e copie a fórmula inteira e cole-a para o sinal de menos. E tudo que você precisa mudar é o intervalo de soma da coluna de entrada, coluna i para a coluna de saída, coluna j. Assim como fizemos na fórmula de alvos, faremos uma atualização rápida para que nenhum dado seja calculado quando não temos um tipo de renda. Portanto, selecione a célula E4, t1, e na estrela, à direita, se abrir parênteses, selecione o tipo de receita em C41 é igual a
branco, em branco e coloque outro parêntese próximo no final. Agora, arraste esta fórmula para baixo. A fórmula de diferenças é muito mais simples. É apenas o seu rendimento real menos o seu rendimento alvo. Portanto, selecione F4, tipo t1 igual a, se abrir parênteses. Seu tipo de renda é igual a branco. Em branco. Caso contrário, o seu rendimento real menos o seu alvo. Então E4, t1 menos t0 41. E arrasta isso para baixo. Faremos o mesmo com as despesas agora. Vá para H 41 e digite igual despesa para preencher todos os fluxos de despesas. Para as metas de despesa, é semelhante à fórmula que fizemos para metas de renda. Vá para a célula I4, T1, e insira f igual, parêntese
aberto, que 41 está em branco e, em seguida, em branco. Caso contrário, vamos querer puxar as metas de despesas. Então insira índice, abra parênteses para a matriz. Vá para a planilha de destinos e selecione IL-1 para N32. Aperte F4 para uma referência absoluta. Para o número de linha em Match. Abre parênteses. Para o Valor de pesquisa, selecione 41 anos de idade no painel principal. O array de pesquisa é IL-1 para 32 na planilha de destinos. Aperte F4 novamente para uma referência absoluta e um 0 para o tipo de correspondência. Para a coluna NUM. Digite correspondência, abra parênteses. Para o Valor de Pesquisa, selecione D8 no painel principal. A matriz de pesquisa é 1111 na planilha de destinos. E insira zeros para o tipo de correspondência. Arraste esta fórmula para baixo. Para a despesa real. Podemos simplesmente copiar a fórmula da tabela de renda assim. E arrasta isso para baixo. E a diferença pode ser copiada e arrastar Dan também. Vamos agora passar para os gráficos. Começará no canto superior direito com um simples. Clique com o botão direito do mouse na caixa, clique em Selecionar dados e, em
seguida, selecione hey, H6 to j nine, e o gráfico será atualizado. E clique em OK. Em seguida, vamos criar o gráfico para o fluxo de renda. E eu vou mostrar-lhe como fazer esses gráficos dinâmicos que ele irá ignorar quaisquer células em branco. Então, primeiro vou mostrar o problema. Se não tornarmos o gráfico dinâmico, clique com o botão direito do mouse na caixa acima da tabela de renda e clique em Selecionar dados estava segurando Controle. Selecione células C4, T1 para E6
e, em seguida, selecione C 39, t 39. E mostrou as entradas de legenda estado Target e real. E o eixo horizontal estado produto, vendas, marketing de afiliados. Se eles estiverem invertidos, basta pressionar o botão de linha de comutação e colunas. Então acerte bem. Você notará que o gráfico não é tão útil por causa de todos os espaços em branco. E você não deseja atualizar os intervalos cada vez que adicionar categorias. Então eu vou mostrar a vocês como torná-lo dinâmico. Precisamos usar a função offset para definir a série ao lado de intervalos nomeados. Então deixe-me mostrar-lhe a fórmula para os valores-alvo de renda. Escolha qualquer cela livre. Então, vamos escolher D6, D2 e digite parênteses abertos de deslocamento igual. Para a referência, selecione D 41 e pressione F4 para uma referência absoluta. Para as linhas em 00 para as colunas, para a altura em conta a. E selecione t1 padrão, t2, d 60, e pressione F4 para uma referência absoluta. E entre parênteses. Em seguida, menos contagem em branco, abra parênteses e selecione 41 para D6 novamente e pressione F4 e feche parênteses. Então, estamos contando o número de células que têm dados nele, incluindo fórmulas menos o número de espaços em branco, que resultam no número de células que não estão em branco. Para a largura N21. Então, finalmente, feche parênteses. E como você pode ver, apenas os valores não em branco para metas de renda são mostrados. E esta é a fórmula que usaremos para o alvo de tinta chamado intervalo. Vamos fazer rapidamente a fórmula para a renda real intervalo nomeado. Copie a fórmula que fizemos para metas de renda e mude as referências de D para E psi. Agora que você tem as fórmulas, vamos definir um intervalo nomeado. Copie a fórmula de destino de renda, vá para fórmulas e, em seguida, gerenciador de nomes. Selecione novo para a tinta de tipo de nome. O alvo quatro refere-se a colar a fórmula. Calor. Ok. Agora copie a fórmula real de renda que fizemos e crie o intervalo de nomes reais de tinta, como digamos, os intervalos de nomes são criados, podemos atualizar nossos gráficos. Clique com o botão direito do mouse e
selecione os dados, selecione a série de destino e clique em Editar. E precisamos fazer é substituir as referências da célula no final com alvo de tinta logo após o ponto de exclamação. Faça o mesmo para a série real e clique em Editar, substituiu as referências de célula no final com tinta real logo após o ponto de exclamação. Uma vez que você acertar ok, a carga agora atualiza. Teremos de fazer o mesmo para a meta de despesa e despesa real. Copie a fórmula real de tinta e altere a referência de E para Eu gosto assim para calcular um intervalo dinâmico para o alvo de despesa e repita para a despesa real e altere a referência de i para j. Em seguida, vamos criar os intervalos de nomes para o destino de despesa e despesa real. Copie a fórmula para o destino da despesa. Em seguida, vá para Gerenciador de Nomes, selecione novo tipo de destino exp e cole a fórmula. E fazer o mesmo para real como psi. Vamos nomeá-lo. E finalmente, vamos fazer os gráficos. Primeiro, clique com o botão direito na caixa e selecione os dados. Vamos selecionar alguns dados para obter algumas informações preenchidas e torná-lo muito mais fácil enquanto mantém Controle. Selecione as células H 41 a j 60
e, em seguida, selecione H 39 a J 39 garantiu o estado de Entrada de Legenda Destino e real e o eixo horizontal indique os tipos de despesa. Se forem invertidos. Basta apertar o botão de alternar linha e colunas. Selecione a série de destino e clique em Editar. E tudo que você precisa fazer é substituir as referências de célula no final alvo XP logo após o ponto de exclamação. Faça o mesmo para a série real e clique em Editar. Substitua as referências de célula no final por x Real logo após o ponto de exclamação. E agora os gráficos de receita e despesas são dinâmicos. Então eu tenho menos a seção de produtos até o último como precisamos fazer uma atualização para nossas fórmulas sobre o produto C. Assim que atualizações com base no período de tempo definido. Vá para a planilha do produto e precisaremos atualizar as fórmulas para total de compra e total vendido. Vamos primeiro para a célula D5
na coluna de compra total e adicionar condições para o período de tempo. Para o terceiro intervalo de critérios. Vá para as transações que estamos realmente e selecione coluna B e, em seguida, pressione F4 para uma referência absoluta. O terceiro critério, digite aspas maiores que iguais a aspas. E, em seguida, adicione o símbolo n. E, em seguida, faça referência à data de início, que é a data de início. Para o quarto intervalo de critérios, selecione a coluna B novamente e pressione F4 para obter uma referência absoluta. E para o quarto critério, digite aspas, menor que igual, aspas. E então o símbolo n. E, em seguida, referenciar a data de término, que é chamada de data. E o fim. Isso irá então apenas algumas transações dentro do período de tempo selecionado na página principal do painel. Vamos arrastar essa fórmula para baixo. Agora, para acelerar, atualizando o total vendido, podemos copiar esta parte da fórmula e colar a fórmula no final, assim. E depois arraste para baixo. Ok, ótimo. Agora que o calor dos produtos é atualizado, podemos agora completar os dados do produto no painel principal. Vá para a planilha principal do painel e selecione a célula e 41. E aqui queremos extrair os produtos mais vendidos. Podemos usar as funções de índice e correspondência para fazer isso. Então digite igual índice, abra parênteses para o array vá para a folha de produto e a coluna B suspensa, que é a coluna de nome do produto, e pressione F4 para uma referência absoluta. Portanto, o número da linha, vamos procurar um no produto, então coluna de classificação usando a função de correspondência. Então insira correspondência, abra parênteses para o Valor de pesquisa, selecione m para T1 no painel principal. Também irá colocar uma referência mista onde queremos olhar as colunas para que seja fácil de copiar pressionando F4 três vezes. Para a matriz de pesquisa,
vá para a folha de produto e selecione a coluna G e pressione F4 uma vez para obter uma referência absoluta. Para o tipo de correspondência em 0 e, em seguida, feche parênteses. E para a coluna NUM, você pode inserir 0 e fechar parênteses. E então você pode arrastar esta fórmula para baixo. Você notará que obtemos NAS, pois temos menos de dez produtos. Então, podemos corrigir isso usando uma combinação da instrução if e é função de erro. Volte para n 41 e vá para o início da fórmula. E digite f, parênteses abertas é erro, parênteses abertas. E então vá para o final e coloque um parêntese próximo como um argumento lógico para o valor se verdadeiro, insira aspas como say e four, o valor é false, copie a fórmula de índice e cole-a aqui. O que isso significa é que se a fórmula de índice gerar um erro porque o valor de pesquisa não existe, então exiba um espaço em branco, caso contrário, exiba o resultado do índice. Menos. Arraste isso para baixo. Você pode ver os NAS desaparecerem. Agora, para a coluna total comprada, podemos copiar a fórmula para os nomes dos produtos. E nós só precisamos mudar o array da coluna B e a folha de produto para a coluna D. Lembre-se de mudá-lo duas vezes. Alteramos a matriz para a coluna D, pois esta era a coluna total comprada na folha de produto. Em seguida, arraste para baixo. E, em seguida, apenas rapidamente linha central. Para a coluna total do solo. Podemos copiar a fórmula e mudar a matriz para a coluna E. e depois arrastar para baixo. Para o inventário realizado. Podemos copiar a fórmula e alterar a matriz para a coluna F, e depois arrastar para baixo. E, finalmente, atualizaremos o gráfico para mostrar os melhores produtos vendidos tornarão esses dinâmicos da mesma forma que fizemos para a grama de renda e despesas. Para acelerar isso, copiaremos as fórmulas de deslocamento que fizemos anteriormente. E vamos alterar os intervalos como psi para referenciar os dados de produtos vendidos na coluna P. Vamos então copiar a fórmula, ir para fórmulas, depois gerente de nomes, e adicionar um novo intervalo de nomes para o nome, chamá-lo de produto vendido, e copie a fórmula na caixa de referência. Em seguida, feche os livros Gerenciador de nomes. Vamos então criar os gráficos. Portanto, clique com o botão direito na caixa, clique em Selecionar dados Vamos primeiro selecionar alguns dados, autocontrole. Selecione n 41 a 50
e, em seguida, selecione P 4150. Verifique se os nomes dos produtos estão na caixa horizontal do lado direito. Para a série, clique em Editar para o nome da série, à direita. Produtos vendidos. Para os valores da série. Exclua as referências de célula e substitua pelo produto vendido, que é o intervalo de nomes. Logo após o ponto de exclamação. Vamos apenas excluir todas as fórmulas offset que criamos, pois elas não são mais necessárias. E isto é, você já construiu sua pista de negócios. Uma planilha, os dados são totalmente dinâmicos. Você pode alterar os períodos de tempo e você pode alterar a data de início e os gráficos e dados serão atualizados. Como dizer. Eu realmente espero que você tenha achado este curso útil. E as habilidades aprendidas aqui permitirão que você crie todo tipo de planilhas diferentes. E assim vários problemas dentro do Excel. Se você tiver alguma dúvida que você quer que eu faça, quaisquer outros cursos, por favor me avise.
Learn To Excel - Josh, Excel Trainer, CFA, Analyst
