Aluno são 114 horas nove cursos acesso vitalício certificado para todos os cursos e ainda te dou 30 dias de garantia não gostou é só entrar em contato que a gente cancela então não tem nada a perder só tem a ganhar eu tenho certeza que vai revolucionar a sua vida profissional Tá bom então é isso vamos ao nosso vídeo no nosso projeto que nós vamos aprender várias técnicas para criação de relatórios profissionais eu vou primeiro começar então pela nossa base a nossa base esse relatório vai ser um relatório de orçamento onde a gente.
Tem o orçado e realizado para cada uma das áreas e também as contas dele pros meses então tem várias dimensões esse relatório aqui você vai ver por data por conta por departamento e o tipo também se ele é orçado ou realizado tudo isso ele vai est disposto dessa forma aqui na nossa tabela começamos pela tabela então onde cada coluna tem que ter apenas um tipo de dado né Por exemplo a data você vai ter apenas a data a conta vai ter apenas a conta departamento somente o departamento valor a mesma coisa e o.
Tipo vai ter então orçado ou realizado isso é muito importante você deve aplicar sempre no formato no nas tabelas que você for utilizar nos seus relatórios Esse é o formato correto sempre com cabeçalho e de cima para baixo tá então esse é o primeiro ponto todas suas bases faça desse jeito eu procuro colocar sempre a base separada colocar na base numa planilha só os cálculos em outro relatório em outro OK agora a primeira coisa então eu vou selecionar essa base toda toda essa tabela aqui e vou transformar essa lista.
Na verdade em uma tabela ó então eu faço com atalho cont contrl shift seta para baixo cont contrl shift seta pra direita então ele seleciona todo o intervalo do primeiro ou último registro e agora nós vamos aqui inserir tabela ou você vai a TTA ou inserir tabela você vem aqui inserir tabela minha tabela tem tem em cabeçadas dou um ok aqui pode mudar o tipo dela Se você quiser botar esse aqui e para botar o nome dela você vai vir em design tabela T orçamento Esse é o nome.
Aonde ele vai então contar verificar se aquela célula ao lado ela tá ativa ou não se ela tá visível ou não então para isso eu vou fazer assim ó igual subtotal vou até aumentar aqui ó igual subtotal abre parênteses conte valores então quero contar ela ponto e vírgula Qual é a célula qualquer uma dessas uma só uma só você vai selecionar fecha parênteses o.
Que ele vai responder é um se essas colunas estiver ã não estiver aparecendo não estiver se aquela célula não estiver sendo exibida ele não vai somar vai somar zero vai parecer zero se eu passar aqui o mouse ó cliquei no mouse 456 soma se eu vim aqui e mudar agora eu quero apenas o realizado ou apenas o orçado você v quando eu clicar aqui ele vai dar metade 228 porém vou tirar agora o filtro novamente para você entender a diferença entre eles então aqui ó soma eu vou somar eles todos somar esses.
Itens todos 456 correto agora eu vou fazer aqui ó igual eh conte conte pon valores a abr parênteses então eu tô contando a quantidade de valores Independente se ele é um ou zero Então olha só o que acontece quando eu venho aqui agora eu vou pegar apenas o orçado ou somente o realizado você vai ver que aqui continua 456 mas aqui tá 228 porque ele tá mostrando apenas aqueles que estão sendo exibidos porque aqu eles estão ocultos ele apareceu como zero tá não tá sendo exibido perfeito.
Bom sobre subtotal era isso então o subtotal ele faz contas considerando apenas as células que estão visíveis e a gente vai utilizar isso para fazer os nossos cálculos depois somas condicionais tá bom tanto aqui feito inicialmente agora nós vamos passar pra parte do relatório Então vou vir para cá em relatórios em relatórios nós vamos comear a fazer a estrutura eu vou deixar essa parte de cima aqui onde a gente vai colocar filtros Vou botar aqui eh também modificadores então onde a gente vai poder exibir ocultar uma parte várias coisas interessantes fica até o.
Final do vídeo que eu vou mostrar muita coisa legal para você hoje Tá mas primeiro a gente tem que começar pela estrutura então aqui embaixo Aqui na coluna na célula c17 eu vou começar com conta ó cont CRL n para ficar ali cont CRL n vou pular aqui uma coluna vou colocar atual aqui o orçamento aqui ao lado vou escrever gasto percentual e ao lado daí depois a gente já vai trabalhar então nessa conta aqui que eu quero retornar todas as contas que eu tenho aqui na nossa base de.
Veja Também:
- Planilha de Conferência do ICMS-ST Fiscal em Excel 6.0
- Quando usar VERDADEIRO ou FALSO nas funções do Excel
- 14 CARTEIRINHA DE MEMBRO WORD
Relatório eu estou utilizando o Office 365 então eu posso usar matrizes dinâmicas com várias funções muito
Interessantes que podem retornar essas informações e dinamicamente sem a necessidade de eu ir lá manualmente e pegar esses itens tirar somente o duplicados remover duplicados pegar somente valores únicos para isso eu posso fazer uso dessas funções eu vou usar aqui a classificar abre parênteses único Então essas duas aqui a único ela vai retornar uma lista de dados única e a classificar vai classificar os dados dessa lista.Então eu vou vir aqui com o único vou selecionar Então a nossa conta somente os dados da conta selecionei tudo tá vendo ali Como já ficou organizado ó ter orçamento conta então ele já fica nomeado certinho fecha o primeiro parênteses pro único fecho o segundo parênteses como eu sei que fechou todas o último e o primeiro vão ser preto cor preta e as outras cores estando vai variar dei um enter aqui e agora ele retornou então todas as contas que nós temos certo então estamos todas as contas aqui ótimo continuando Então eu vou passar.
Aqui por enquanto o atual e o orçamento não é o que a gente precisa trabalhar no momento e nós vamos trabalhar agora com a nossa função aqui para montar então Eh as nossas datas nas datas tem várias formas de você fazer a mesma coisa eu poderia por exemplo vir aqui e digitar um do 1 2023 porque as nossas datas lá aqui na nossa base de relatório tá assim ó um do um sempre primeiro dia de cada mês até deixa eu fazer uma coisa aqui também ó eu já vou mudar ela aqui control 1 selecionei tudo né cont control shift seta para baixo control um.
E agora eu tô mudando aqui para ó formatação personalizado mês mês mês tá então essa formatação vai fazer com que exiva janeiro fevereiro somente abreviado o nome dele tá o nome do mês Ótimo então aqui eu tenho janeiro eu poderia vir aqui preencher manualmente ou fazer com outras fórmulas também mas eu quero fazer uso aqui das matrizes dinâmicas para aprender mais coisas hoje então a gente pode fazer da seguinte forma igual data abre parênteses quando a gente tem a função data ele vai montar.
Uma data então eu vou botar 2023 que é o ano ponto e vírgula o mês eu vou colocar um mês fixo só pra gente ver 1 ponto e vírgula 1 fechei aqui então ele vai retornar 1/1 2023 acontece que eu tenho uma função chamada sequência essa função chamada sequência retorna então uma sequência de de números poderia ser linhas colunas e com início e fim eu quero por exemplo 12 linhas quero que ele retorne 12 por padrão ele vai começar no um e vai até o número que eu.
Mandei 12 então então a gente vai pegar essa função sequência que eu tenho aqui que vai de 1 a 12 e eu vou colocar aqui no lugar do mês sequência no lugar do mês 12 e o que ele vai fazer isso aqui ele criou então uma sequência com os números das datas então a data primeiro do um primeiro do dois primeiro do três aqui a formatação tá formatado desse jeito porque aqui tá como número mas se tivesse como data ele já ia ficar certinho tá não quero mudar muito isso que ali tem outra formatação tá bom muito bem mas eu queria que ele viesse.
Pra coluna Então como é que eu faço para isso eu vou usar a função transpor abre parênteses e fecha o final então o quer a linha vira coluna o queer a coluna vira linha dou enter pronto tá resolvido certo então aqui no pincel de formatação eu vou pegar esses dados aqui vou puxar pro lado aqui então esses itens perfeito pegou aqui então o formato deles já tá ali as datas certo tá ali certinho então então como é que a gente faz aqui eu ainda não é bem assim que eu queria eu queria que aparecesse daquele outro jeito né cont control um queria que aparecesse só o mês mês mês.
Mês pronto tá bom então ficou desse jeito aqui ó por enquanto ele tá com esses dados aqui ó tá não tem nada muito formatado por enquanto tá só assim mas aqui a gente já tá trabalhando com transpor já montou automaticamente Deixa eu botar Centralizado tá então já ficou tudo assim perfeito muito bem esse aqui eu também vou centralizar agora a gente vai começar a colocar algumas fórmulas nessa aqui a gente vai utilizar uma fórmula então primeiro eu tenho que fazer uma uma conta aqui Deixa eu botar um total aqui que.
Faltou Total onde eu vou querer os totais aqui ao lado tá então depois a gente bota o total agora aqui abaixo eu vou fazer uma soma Então quem que eu quero somar quando for aluguel desse mês e quando for esse esse item da conta e quando for esse mês aqui Então como é que eu faço para isso você vai fazer da seguinte forma igual soma C abre parênteses deixa eu até aumentar um pouquinho né Igual soma C abre parênteses qual o intervalo de soma o nosso intervalo de.
Soma vou vir na base e vou selecionar então a coluna de valor tá aqui valor certo ponto e vírgula Qual é o intervalo
De critérios que ele tá perguntando agora bom o primeiro intervalo de critérios que a gente pode colocar é a data então eu vou pegar a data cont control espaço selecionar a coluna toda e agora o próximo ponto e vírgula tá perguntando qual é o critério então o critério da data vai ser exatamente esse de cima mas veja só eu selecionei só o primeiro certo mas se eu fizer pro lado cont control shift o que vai acontecer ele vai pegar todos esses critérios de uma vez ele pegou o relatório j17 tudo.Que a gente tinha ali ó tá então ele pegou tudo que a gente tinha aqui na coluna de relatório então ele vai pegar todos esses parâmetros janeiro fevereiro março e ele vai colocar pra direita tudo isso tá mesma coisa a gente vai fazer com o outro ponto e vírgula então passando vários critérios pra função de soma C tá aqui eu tenho que passar alguma coisa um pouco mais resumidamente Tá mas também tem muito conteúdo aqui no guia do Excel a respeito e no nosso curso de Matriz dinâmica você vai muito a fundo com relação a isso mas muito a fundo você.
Aprende muito mesmo a trabalhar com matrizes desde o zero tá então bem mais é passo a passo aqui é muita coisa que você vai aprender nesse vídeo então eu tenho que passar alguma coisa mais rápido um pouquinho tá bom então os conceitos acabam não ficando todos talvez bem Claros Mas aí você fazendo consegue também baixar e acompanhar por aqui e também tem muito conteúdo tem mais de 1000 artigos no guia do Excel pode procurar lá que você vai encontrar também então muito bem continuando voltando aqui então nós retomando estamos somando o valor quando for dessa data que tiver aqui em cima agora tem.
Mais um critério que é a conta então eu vou vir aqui intervalo de critérios de novo vou selecionar quando for dessa conta aqui que eu tenho dessa conta aqui ponto e vírgula vou voltar pro relatório vou selecionar esse primeiro aqui vou digitar aqui ó c19 cerquilha ou seleciona tudo tanto faz tá bom então ficou assim ó soma somar o valor soma CS soma o valor quando for na coluna data for igual a data que eu tenho aqui em cima e a conta for igual a essa conta que eu tenho aqui ao lado faltou uma.
Coisa ainda porque lembra que a gente não quer somar quando for quando a coluna não tiver aparecendo quando a linha não tiver aparecendo a gente já vai entender por disso então eu quero fazer isso também ponto e vírgula mais um último critério esse último critério já vou mostrar porque que ele tem tá então tá aqui ó aliás tem mais dois critérios um é o orçado e realizado que a gente precisa botar faltou isso também então aqui eu quero somar somente quando for realizado Então selecionei tudo aqui ponto e vírgula aqui no critério ó eu vou digitar aqui direto ó.
Realizado realizado entre aspas duplas então eu tô somando somente quando for realizado e por último ponto e vírgula quando oculto essa coluna oculto ponto e vírgula ó contra espaço para pegar tudo for igual ponto e vírgula para ele passar o critério então sempre é intervalo de critérios e critério você pode botar quanto você quiser ali muitos critérios tá até o limite da função Então somente quando for igual a um Ou seja quando estiver sendo exibido quando nem tiver filtrado nada fechei.
Parênteses dei um enter e veja que agora a gente tem todos os dados mostrando Aqui de acordo com a soma da conta né do mês ainda verificando se tá exibido ou não e somente quando ele for igual a realizado certo aqui esses valores são um pouco grandes então eu vou mudar essa configuração dele então você vai selecionar tudo control um e aqui em personalizado você vai colocar assim eu vou colocar cerquilha ponto cerquilha 00 ponto.
Aliás v0 k pon k Então o que ele vai fazer é o seguinte ele vai pegar aqui vai formatar com com aquele decimal com ponto né E aqui ao final vai ser 0,0 para aparecer a primeira casa decimal e esse ponto que nós temos aqui ó eu tô dividindo ele por 1000 para não ficar aparecendo 64510 eu quero que apareça 64,5 k por exemplo vamos dizer que eu quero mais resumido para não poluir demais aqui o nosso orçamento então eu coloquei ele desse jeito aqui.
Tá bom daqu ao lado dele eu ainda vou colocar assim ó ponto e vírgula ponto e vírgula e vou dar um OK tá tem mais uma coisa que eu quero fazer cont control 1 quando a gente tem um número zero por exemplo aqui tem o zero quando a gente tem o número zero eu quero que ele apareça só um tracinho para ele não poluir demais também control um Então você vai ver aqui que o formato de contábil ele traz tracinho né moeda também acho que traz tracinho não contábil mais se com nenhuma assim tá então eu quero que traa só o tracinho.
Então como é que vai ficar esse cont control 1 você pode vir personalizado e pegar essa formatação que nós temos aqui então você pode pegar essa última formatação pega essa última que nós temos aqui ó última que é a formatação eh que é a formatação que nós temos do do número zero Então essa última que a gente tem depois do último ponto e vírgula tá aliás nós temos aqui deixa eu botar aqui tá então nós temos aqui ó positivo negativo e valor zero aliás Esse aqui é do valor zero tá então esse.
Aqui do valor zero Esse aqui é do texto também se você quiser botar pode copiar daqui para trás não tem problema nenhum então eu vou pegar aqui de novo cont control 1 e quando for aqui então lembrando positivo negativo e zero então no terceiro aqui ó terceiro então segundo depois dois ponto e vírgula eu vou botar assim o que vai acontecer que quando for zero opa ele vai ficar assim com tracinho tá eu digitei aqui ele despejou errado Claro mas eu já só queria ver o tracinho então apaguei aqui ele volta como estava antes e agora nós já temos os nossos dados centraliza tudo.
Isso aqui acima dele eu vou botar um soma igual soma abre parênteses seleciona para baixo tá dou um enter aqui e pronto vou copiar aqui para lado também tá tá ok ah ficou com o formato errado sim porque ele pegou el de cima né pegou mese formato é só pegar um pincel aqui de qualquer um deles e passar aqui pronto ó tá ali então o nosso valor total vou botar um cont control n para ficar ao lado eu já posso também fazer uma soma.
Do do atual né então eu posso fazer assim igual soma abre parênteses vou selecionar Então essa coluna toda aqui essa linha toda mesma coisa para todos ó portanto aqui todas as nossas somas também tá mesma coisa da soma aqui pode somar aqui também direto ó já somou aqui ok e no orçamento a gente já vai fazer tá então por enquanto tá assim por enquanto o nosso orçamento ó tá feito até aqui tá certo se quiser para ficar.
Mais bonitinho por enquanto a gente pode também já passar aqui a a usar alguma formatação tá então primeira formatação selecionei A primeira coluna preenchimento aqui é importante bord eu gosto de usar essa linha aqui ó ou essa ou essa também tá tanto faz essa aqui ou aquela de cima seleciona essa ali Contorno interna dou um ok e vai ficar desse jeito aqui para você tá então vai ficar aquela cor ã muito Escura então vai ficar um pouco mais claro mesma coisa você pode fazer pros demais então esse também então só toma cuidado PR se.
Você for fazer isso não usar muito o pincel porque se você usar o pincel ele vai pegar a formatação deles também tá o que tiver adjacente que for igual a os dois daí tudo bem mas o que não for daí você não faz tá bom opa opa ess segundo aqui Contorno interno OK tá Faz assim então mesma coisa que cont control um de de novo Contorno interna e OK tá esses de cima como são cabeçalhos eu também vou botar em cont control n e ficou desse jeito Por enquanto acima dele aqui ó.
Logo aqui eu vou escrever realizado pra gente saber que esse aqui é a nossa base de realizado control um e eu posso botar aqui também uma linha com a mesma cor para ele combinar né somente na linha de baixo Ok ficou assim então perfeito Então já tá um pouquinho mais organizado vou dar um Control o n aqui também para ficar ali também muito bem continuando ao lado a gente pode colocar também uma coluna onde a gente vai colocar eh se ele é orçado ou realizado Aqui nós temos só o orçado né Aliás só o.
Realizado eu quero também colocar uma do do orçado porque eu preciso dessa informação pra gente poder trabalhar e fazer os as análises por exemplo saber quanto que eu tenho Dorado quanto é que eu tenho de gasto baser vários gráficos que a gente pode fazer mas às vezes eu não quero exibir isso tudo Eu vou mostrar também como ar isso depois por enquanto você vai vir aqui vai copiar isso vamos aproveitar o que a gente já tem você vai vir aqui ao lado na coluna x e vai colar ela ali tá Ah ficou tudo errado sim a gente já vai arrumar então.
Aqui você vai escrever orçado muito bem isso tá ok e aqui nos dados OK agora esse daqui a gente não tá vendo que ele pegou ali a coluna né ele tá pegando da da linha Q1 era para ele pegar lá da frente ó ele pegou errado podia travado né vou travar ele aqui vou copiar antes deixa eu pegar aqui ó você pode vir aqui no q19 que é aquele da esse aqui ó que ele foi o que pegou errado trava ele acho que agora vai dar ok Esse aqui também pode travar agora vai dar ok Copi ess aqui.
Então somente a primeira célula V aqui colei ela e pronto então ele pegou e mostrou tudo aqui ah mas ele não tá mostrando certo né tá mostrando igual realizado e orçado o que a gente vai fazer simplesmente aqui em cima ó ou V muda aqui ou muda aqui também tá mas aqui fica mais claro ó eu vou mudar aqui de realizado para orçado orçado e pronto a gente já tem aqui aliás orçamento acho que ele tá escrito lá embaixo deixa eu ver orçamento tá orçamento não orçado.
Orçamento pronto Opa entre aspas duplas orçamento Então tá ali tá mostrando todo o valor do nosso orçamento Agora sim ele trouxe certinho a aqui ao lado nós temos também o valor do orçamento certo então eu vou mandar somar o orçamento abre parênteses vou pegar então no valor do nosso orçamento copiei aqui então vim para baixo colei para cima centralize OK mesma coisa pro Total aqui então e agora.
A gente já tem o total do orçamento total do atual total do realizado e aqui o gasto a gente vai também configurar ele deixa eu botar aqui uma bordinha que faltou no lado ali Ok e agora a gente tem o percentual o quanto que ele ã já foi foi gasto né então vou botar um C erro porque pode dar zero pode estar filtrado qualquer coisa igual se erro deixa eu aumentar aqui igual se erro abre parênteses então eu vou vir aqui em e18 que é o valor atual op Pode.
Ser aqui pode ser e18 dividido por F18 que é o nosso orçado então quanto do atual já foi gasto com relação ao orçamento fecha parênteses ponto e vírgula zero se der um erro daí boto zero tá bom aliás conton vul z0 fecho o parênteses tá ali Então esse é o valor 96% do orçamento já foi gasto Vou botar aqui um percentual centraliza ele se você quiser botar duas casas decimais pode também uma casa tanto faz vou copiar Aqui para baixo e tá ok então ele.
Mostrou o total do orçamento que já foi gasto E também o quanto que já foi gasto com outros como por exemplo aqui estourou bastante no aluguel de escritório e por aí vai tá então cada item tem a sua a própria questão ali tá bom tanto aqui já tá pronta essa parte também muito bem agora a gente vai continuar então e vamos trabalhar com mais uma coluna que eu vou precisar também aqui para criar o minigráfico depois vamos usar o lado aqui tá Então logo aqui na coluna AK a gente vai pegar.
Aqui esse orçado pode ser pega a mesma estrutura tá mais fácil copia tudo aqui e cola aqui em cima cola aqui então o que que a gente vai ter aqui vai ser realizado e alizado botar aqui e alizado menos orçado maior pior por que que eu tô botando maior pior pra pessoa que for ler aqui entender então quanto mais e se for negativo é bom se for positivo é ruim for positivo significa que o realizado tá maior que o orçado E se for negativo significa que o realizado tá menor que o orçado ou seja gastou menos.
Tá bom panto aqui eu vou apagar esses daqui e agora a gente vai fazer aqui então uma diferença entre um e outro então eu vou ver quanto que foi gasto a mais do que o orçado Então vou botar igual j18 que é o primeiro que eu tenho aqui menos o primeiro Dorado aqui tá tem um enter ali 48.392 quer dizer que ele gastou 48.392 a mais do que seria né Vou botar aqui como zer zer vou tirar os zero zeros ali tanto aqui ó positivo e negativo botar para baixo ali e vou.
Mudar esse aqui vou botar como assim tá bom aí se vocês quiserem a gente não vai exibir esses dados aqui se você quiser personalizar ele também pode faz como você achar que deve ali tá quiser botar ele assim então tá OK Tá Ou se quiser botar nesse outro formato aqui também pode não tem importância nenhuma o que a gente vai usar esse gráfico aqui esses dados aqui vai ser pra gente mostrar no mini gráfico depois o quanto que ele tá acima gasto naquele momento ali tá bom tanto aqui realizado orado tá.
Bom muito bem agora vamos fazer agora tá pronto aqui a nossa parte e de cálculo a gente vai fazer mais alguns ajustes agora apenas aqui com relação a filtros a gente vai adicionar filtros vai adicionar formatação condonal tudo isso tá então o primeiro item que a gente vai fazer aqui primeiro valor que a gente vai fazer aqui é uma formatação condicional quando eu quero estourar quero saber se esse valor aqui se ele é maior do que esse valor aqui então eu quero mostrar somente aqu eles são maiores como é que eu faço isso então eu.
Posso fazer aqui diretamente só fazer uma função direto e verificar essa diferença tá então a gente pode fazer isso para fazer isso eu quero vir aqui em cálculos também eu quero adicionar nessa nessa coluna cálculos aqui eu quero adicionar um controle nesse controle eu vou AD eu vou verificar se eu quero ã mostrar destacar esses valores eu não quero então eu vou botar aqui ó destacar destacar valores tá então nessa célula logo abaixo eu vou ter um controle lá dentro do do Excel.
Onde eu vou destacar ele e vou retornar ele como verdadeiro ou falso Quando eu fizer uma marcação e daí dentro da formatação eu vou verificar se ele tá marcado ou desmarcado e daí eu vou aplicar ou não a formatação Então olha que interessante vou vir aqui já tô aqui no nosso orçamento logo acima dele na guia desenvolvedor se não tiver habilitada Você vai clicar aqui personalizar faixa de opções vai marcar a guia desenvolvedor aqui embaixo tá marca ele desenvolvedor inserir e vou marcar esse item aqui ó controle de formulário caixa de seleção vou vir aqui.
Postar um comentário