Novos cursos e novas aulas Ah o curso tá em 10 vezes sem juros você pode adquirir começar hoje mesmo já se preparando para esse novo ano pras oportunidades de emprego e também de promoção na sua empresa OK então é isso vamos ao nosso vídeo vamos falar sobre a nova função pivotar a que vai est já com essa tradução pro português e Pivot by então Pivot by emem inglês que é a versão que nós vamos usar aqui tá bom mas tudo que a gente fizer aqui eu vou traduzir também em português então aqui a versão ele tá no Excel insider Beta eh deve vir.
Logo pro Excel também então começa normalmente no Excel insider Beta se você não é insider Beta você pode também se cadastrar tá é de graça só ir lá na conta e mudar e aqui embaixo nós vamos falar então a respeito das características e também nos parâmetros dele e depois em seguida nós temos vários exemplos aqui eu coloquei exemplos diferentes dessa vez exemplos mais complexos mais completos que você pode aplicar direto a partir do que eu vou mostrar para vocês hoje é uma função bem interessante ela lembra um pouco o group byy que a gente falou a respeito.
Agrupar que foi anterior então aqui a função pivo byy permite criar um resumo dos seus dados através de uma fórmula ele suporta agrupamento ao longo de dois eixos e a agregação dos valores Associados Associados por exemplo se você tivesse uma tabela de dados de vendas Poderia gerar um resumo de vendas por estado e ano então basicamente ela funciona como uma tabela dinâmica tá então até o nome dele relembra bastante né pivô Então você tem uma coluna de linhas e umaa e outro é de colunas aqui né então você vai ter a dimensão de linhas e colunas.
Dentro desse resumo usando uma única fórmula aqui você tem a sintaxe ry então aqui a gente vai falar a respeito de cada um dos parâmetros não vou ler eles ali tá sem tradução ainda tá tá sem tradução os parâmetros ainda mas eu vou ler aqui a respeito deles assim mesmo como estão então H Fields required é eles são obrigatórios tá ess que tá Como required é porque são obrigatórios então primeiro aqui h Fields uma matriz ou intervalo orientado colunas que contém os valores para agrupar linhas e gerar cabeçalhos de linha a Matriz ou.
Matriz do intervalo pode conter várias colunas neste caso a saída terá vários níveis de grupos de colunas mesma coisa também como a gente tem tabela dinâmica só que para cima aqui em colunas né então a gente tem essa dimensão que era linhas essa aqui é de colunas vai ter uma ou mais linhas que você tiver as colunas que você tiver vai ser assim vales então o campo de Vales vai ser a Matriz ou intervalo orientado a colunas de dados a serem agregados a Matriz ou o intervalo pode conter várias colunas nesse caso a saída terá múltiplas agregações isso significa então que você.
Vai ter aqui então a coluna as linhas as colunas e aqui no meio você vai ter os valores por exemplo a soma por exemplo a média por exemplo qualquer outra coisa que você quiser o mínimo máximo que você vai ter então para esse essa dimensão de linhas e colunas como a gente tem na tabela dinâmica vai ficar mais fácil quando a gente trabalhar com os exemplos ali mas basicamente são esses três parâmetros o quarto parâmetro é function é a função então a função lambda ah lambda ou lambda Eta que já é uma função preparada reduzida já on tem su aage.
Count que define Como agregar os valores um vetor de lambdas pode ser fornecido Nesse caso a saída terá múltiplas agregações e a orientação do vetor determinará se eles est estarão dispostos em linhas ou colunas então ele vai determinar aqui com lambda ou com eta ele vai dizer o que que é para fazer com os valores então tem os valores eu vou dizer eu quero somar eu quero a média eu quero mínimo máximo Então ele vai fazer aqui para você tá é isso que vai fazer então esses parâmetros são obrigatórios tá assim como a gente tem.
Tabela dinâmica basicamente e aqui você vai ter então outros que são eh que são que não são exigidos como Field headers que vai ser então e o cabeçalho das o cabeçalho né fica se Hall Fields Call Fields e valores t cabeçário e se os cabeçadas devem ser retornados daí sim não automático sim eh não mas mas gera né então aqui você tem se ele vai gerar ou não o cabeçalho se ele vai exibir ou não o cabeçalho aqui você tem também o How.
Total total depth determina se os cabeços da linhas devem ter conter totais então isso aqui ele vai fazer o total o totalizador vai colocar paraas linhas então um sem totais totais Gerais totais Gerais e subtotais totais Gerais no topo e totais gerais e subtotais no topo ausente ele vai colocar totais Gerais e sempre que possível subtotais então ele já vai botar automático total e subtotal caso você não Especifique se você quer ou não e para Claro que para.
Veja Também:
- Planilha de Formação de Preços de Produtos para Revenda em Excel 6.0
- Como fazer um Gráfico de Candle no EXCEL (Vela)
- Como Criar carteirinha de membros na planilha Cadastro de membros
Subtotal ele tem que ter pelo menos duas eh colunas How sort ah S Order ele
Indica Então como as linhas devem ser classificados os números correspondem as colunas então aqui ele vai fazer a classificação das linhas e se for negativo Elas serão classificadas em ordem decrescente inversa aqui também pode passar com uma matriz então você pode determinar se eu quero por estado e vendedor por exemplo você pode determinar aqui passando ele em tric Chaves aqui então qual o total dept.Então ele mesma coisa ele vai fazer determinar se as colunas TM totais Então como tem as linhas Total Então vai ser por baixo e as colunas tot total de coluna e vai colocar uma coluna no final de Total tá ou antes né pode ser para cá ou para cá então é vantagem também com relação à a tabela dinâmica também que você pode fazer Onde você quer o total se quer na frente quer atrás mesma coisa com relação às linhas qual o sort Order o número indica que as linhas a como as.
Linhas devem ser classificadas números correspondem às colunas em Call Fields seguidas pelas colunas em Valois se o valor o número for negativo as linhas serão classicadas em ordem decrescente inversa um vetor de números pode ser fornecido para classificar mesma coisa que a gente viu então pode classificar pela linha ou pela coluna e o Filter Array permite que você faça um filtro então desses dados se ele vai filtrar essas informações se eu quero por exemplo somente da região norte eu quero somente desse vendedor eu quero somente de 2022 20223 Então você consegue fazer.
Isso é uma função grande como você pode notar e ela é muito fácil de utilizar também AP apesar de ser grande e tem muitas possibilidades Então essa pivô ela vai fazer a mesma coisa que a gente tem em tabela dinâmica só que com fórmula Resumindo pivotar Então a gente vai começar com esse exemplo aqui que eu falei que eu ia fazer um exemplo mais e mais complexo mais completo vamos dizer assim aplicando direto e mostrando várias coisas interessantes para você então a primeira coisa que eu vou fazer aqui essa aqui é uma tabela tá uma tabela e agora eu quero colocar imagem.
Aqui de acordo com o vendedor Então essa função se você não conhece é bem interessante é a função imagem Então já tem uma imagem aqui ó a partir desse link que eu coloquei aqui ó se você abrir ele no navegador ele vai abrir para você uma imagem essa imagem por exemplo aqui e essa imagem você consegue puxar ela utilizando a função imagem e é bem fácil passo fazer assim ó aqui eu vou fazer um Vap se você não vai não tá em inglês vai ser PR V para você tá então eu quero pesquisar esse vendedor ponto e vírgula onde eu quero pesquisar.
Quero pesquisar aqui tá eu quero retornar a terceira coluna que é a coluna do valor ali deixa eu travar pon3 p0 eu quero pegar aquela imagem direto fecha parênteses Dá um enter e tá ali ele já busca a imagem automaticamente para você para colocar imagem aqui eu simplesmente usei a função imagem image né Imagem em português e Peguei aquele link e ele já busca automaticamente online só quis mostrar que você pode fazer isso também com procv ele já pegou automaticamente para todos também.
Ok continuando então nós temos valor quantidade região e aqui eu tenho o filtro também que eu vou aplicar depois que eu quero por exemplo aplicar um filtro quando for ano 2023 for Maurício por aí a gente consegue aplicar também mas primeiro eu quero começar aqui fazendo botar um pouco para baixo aqui na linha 16 mais ou menos eu vou fazer um pivô padrão pra gente então eu quero pegar aqui o vendedor quero pegar o ano né e quero trazer essa informação para cá então vamos lá igual pivô bu deixa eu aumentar.
Um pouquinho aqui linha 16 igual Pivot by abre parênteses H Fields Então qual vai aparecer o campo que vai aparecer no lado então eu vou pegar essa coluna aqui vou clicar duas vezes sobre vendedor vai aparecer flechinha clica duas vezes assim vendedor ponto e vírgula e agora tá perguntando qual Fields Quais são as colunas que vão aparecer para cá então vou botar o ano cliquei duas vezes ponto e vírgula o próximo Campo que ele tá perguntando é o valor então o valor eu posso pegar quantidade valor tanto faz.
Vou pegar esse aqui mesmo valores vou dar um enter aqui para você ver então ponto vírgula Alt enter para ficar melhor aqui para você ver agora tá perguntando qual é a função a função é o que ele vai fazer com esses valores tá com esses valores então eu já disse para ele Qual é a dimensão de linha dimensão de linha dimensão de coluna e qual é o valor que eu tô querendo fazer alguma coisa e agora ele vai mostrar para você uma lista de funções que você pode aplicar Direto você pode usar lambda direto mas esse aqui são lambda pronto eu vou pegar o aage que vai ser a média.
Para você aí tá Se tiver em português aí vai ser a média ponto vírgula Field headers então eu vou dizer Yes and show
Porque eu peguei aqui a linha de cabeçalho olha ali ponto vírgula TR Então quero que mostre ponto e vírgula e agora eu vou passar para ele se eu quero totais sub totais eu vou botar o que eu quero vou forçar ele direto lá Grand totais direto ok fecha parênteses dou um enter e olha o que a gente tem automático icamente ele veio e montou como se fosse uma tabela dinâmica E é exatamente essa a funcionalidade da.Função piv Olha que incrível ele já fez automaticamente então uma lista com todos os vendedores aqui as vendas agregadas já a média de venda de cada um desses vendedores ao longo do ano e deu Total também automaticamente tanto de linhas quanto de colunas porque a gente não definiu então ele já fez automático pra gente e uma outra coisa bastante interessante que isso não dá para fazer em tabela dinâmica é o seguinte vou dar F2 aqui vou mudar o vendedor aqui e vou.
Pegar a imagem cliquei duas vezes deu enter e olha o que a gente tem agora a gente tem imagem ao invés do vendedor então você pode colocar imagens aqui imagem de produto imagem de vendedor imagem eh por exemplo marcas de produtos é muito interessante muito realmente muito interessante poderia estar aqui também tá poderia colocar para cá também esse eh mudar o pivô né se você quiser mudar o pivô de repente colocar imagem para lá e a o ano para cá basta você vir trocar aqui ó peguei o ano botei para cá ponto e vírgula a imagem e agora a gente.
Tem ano e imagem desse formato aqui ão mostrando a imagem em cima né o vendedor em cima e aqui o ano de cada um E daí você consegue analisar assim para baixo também Depende a forma como você tá querendo ver esta informação OK agora nós vamos fazer com aplicar com filtro essa informação então é bem fácil também não tem muita dificuldade nós vamos vamos aplicar basicamente a mesma coisa que a gente viu no anterior então vou botar aqui piv By De novo abre parênteses H Fields eu posso pegar aqui qualquer um tá então eu vou pegar aqui a.
Imagem Peg a imagem mesmo H Fields Call Fields eu vou pegar então as colunas que nós temos aqui de deixa eu pegar aqui região Ah vou pegar duas eu vou pegar duas colunas aqui só para ficar diferente né vou pegar aqui ó coluna imagem para aparecer duas né ó aí Imagem em região Então esse você o nosso H Field vai aparecer a imagem e também a região para cada um deles tá então todas as vendas daquele vendedor por região ponto e vírgula agora eu vou.
Pegar o ano então vou pegar o ano ali nas colunas ponto e vírgula campo de valor poderia ser quantidade também poderia ser outro Campo ali ponto e vírgula apando aqui embaixo agora vou botar então a ele tá perguntando qual é a função que eu vou aplicar vou pegar o ver Rage também ponto e vírgula vou passar de novo que eu quero so total né ah o cabeçalho três eu quero também Grand total e subtotal então vou botar dois Ok ponto e vírgula e agora el tá passando H sort Order vou passar direto.
Quando tem aqui ó entre colchetes ali colchetes Você pode passar direto porque eles não são obrigatórios não é obrigatório não é obrigatório e agora eu quero ir no Filter Array que é esse último aqui no Filter Array eu vou dizer para ele então quais são os filtros que eu gostaria de aplicar e ela funciona igual a função filtro abre parênteses e eu quero então que seja quando for o ano eu vou clicar nesse ano aqui igual a esse ano que eu digitei aqui em cima ok fecha parênteses vezes então da mesma.
Forma que a gente tem no filtro ele vai gerar aqui uma matriz com verdadeiro e falso se for do ano 2020 2023 tá selecionado ele vai retornar aqui e o vezes eu posso aplicar mais condições e quando o vendedor vou clicar aqui for igual a Maurício ok fecha o parênteses fecha de novo para fechar a última e pronto então isso aqui vai ser aplicado Ali vai dizer assim eu quero quando for 2023 daí ele vai colocar true falso true falso verdadeiro e falso verdadeiro e falso e aqui também ele vai aplicar vendedor for igual a Maurício então ele.
Vai colocar de novo verdadeiro falso verdadeiro falso e daí só vai retornar quando for vezes verdadeiro vezes verdadeiro ou seja 1 x 1 e não 1 xz 0 ou z0 ve 1 porque isso vai retornar zero tá quando eu ent agora nós temos Este resultado então só o Maurício e quanto que ele vendeu em cada uma das regiões com o subtotal né Maurício e também os total é claro né se a gente mudar aqui ele vai trocando então para cada um deles 2020 2022 2023 ele puxa.
Automaticamente para você e pode aplicar formatação condicional pode Qualquer mudança que você fizer que isso é bem interessante também qualquer mudança que eu fizer você pode colocar aqui tá botar uma nova região aqui só para você ver deixa eu pegar aqui ó nordest você vai ver que ele vai mudar automaticamente no joquim ah 2020 2020 ele vai pegar ali e já vai mudar automaticamente pra gente olha que interessante Então Qualquer mudança que eu fizer ele vai aplicar automaticamente.
Pra gente olha ele já vem já aplica se eu mudar o valor também ele já muda tudo automaticamente sem eu fazer nenhum tipo de mudança então uma tabela dinâmica muito interessante criada aqui só com fórmulas vamos continuar então ir pra segunda fase segunda parte que o segundo exemplo interessante que eu pensei pra gente falar hoje tem bastante exemplo porque como eu te disse ela ela simula né ela faz na verdade a mesma coisa que nós temos na tabela dinâmica só que com fórmulas Isso é realmente muito.
Interessante é incrível eu diria aqui a gente consegue fazer também uma coisa muito interessante uma situação que acontece com todas as empresas tá todas as empresas TM situações que você precisa comparar dados também chamado como conciliar dados Então temos aqui ó eu peguei uma lista um padrão tá então nós temos essas notas fiscais com esses valores e a origem é o contábil tá então ess é origem contábil Então essas são as notas da contabilidade Essas são as notas nota.
Valor e o financeiro são as notas do financeiro veja então qual a estrutura que nós temos pode usar em qualquer outra conciliação uma chave um valor único ou mesmo que ele repita não tem problema mas a chave é por qual ele nós queremos fazer a comparação o valor a origem a nota fiscal o valor de novo e a origem mesma coisa então essas colunas você vê que elas se repetem são iguais nos dois né e eu quero comparar para saber quais são os valores que são diferentes para as notas então simplesmente eu vim aqui criei uma tabela e coloquei os dados um.
Abaixo do outro olha ali a lista contábil e financeiro tudo junto num lugar lugar só e agora com a função Pivot by a gente consegue fazer isso aqui ó deixa eu abrir um pouquinho mais tem bastante espaço pra gente trabalhar vou aumentar o vou aumentar aqui vai dar um zoom bem legal então aqui a gente consegue fazer o seguinte eu quero fazer essa conciliação mostrando então quanto que eu tenho no contábil e financeiro por nota só isso igual piv by Pivot by abre parênteses aí how Fields primeiro o campo de o campo chave né campo de linha.
Pode pegar ali as linhas as notas fiscais ponto e vírgula qual é a origem então eu vou botar no cabeçário Call Fields cliquei duas vezes ponto e vírgula Qual é o campo de valor aqui eu vou fazer uma coisa bem interessante nós vamos aplicar uma função antes da gente aplicar o valor então eu vou verificar se for contábil eu quero que fique negativo o valor se for financeiro eu quero fique positivo para que isso para eu fazer um menos o outro e assim eu tenho então o total me mostrando apenas a diferença então eu posso fazer assim ó.
Igual aliás C A if né if eu vou pegar a coluna de origem for igual a contábil Opa com contábil então menos valor se não valor se true clica duas vezes nesse outro ali então se for contábil então aquele valor se não positivo fecho parênteses e pronto ponto e vírgula qual é a função que eu vou usar vai ser a Sam Obrigatoriamente Sam que é a soma Tá bom então vai somar por.
Nota e vai mostrar por origem quando for contábil ele vai colocar negativo e quando for outro valor qualquer positivo financeiro vai ser positivo fecho parentes ou enter e olha o resultado que a gente tem temos aqui a nota fiscal temos aqui o valor no contábil temos aqui o valor no financeiro e aqui no total ele tá mostrando zero por quê 8.000 Men 8000 tá dando zero então quando ele ele tá igual ele vai mostrar zero aqui certo porque é um menos o outro Zero no total agora olha só data Filter vou clicar aqui vou desmarcar.
Você pode desmarcar aqui tá ou pode colocar ali quando for diferente de tal faixa então eu botei aqui ó desmarquei e vou deixar só os que deram diferença e daí vai mostrar aqui essa nota fiscal não tá no contábil e tá lançada no financeiro deu isso essa nota fiscal tá lançada no contábil não tá no financeiro e deu essa diferença essa nota fiscal tá lançada com valor diferente entre contábil e financeiro e pela diferença já dá para notar que foi lançada duas vezes e daí tá todas as diferenças na nossa conciliação tudo feito de uma.
Forma muito fácil Se eu mudar qualquer informação aqui ele muda aqui automaticamente também não precisa dar refresh não precisa fazer nada Tá bom então vamos lá para mais um caso muito interessante um caso de orçamento então a gente consegue trabalhar também com o orçamento é uma coisa também que é feito bastante né com quando a gente trabalha aí com nas empresas quando você tem situações lançamento eu quero colocar aqui por exemplo eh qu por área né as despesas por área e eu quero colocar aqui ao longo dos meses tá E daí total.
Né totalizador embaixo e em cima para dizer o quanto que foi gasto em cada um neste caso aqui em específico a gente tá querendo fazer então os totais eu quero mostrar para ele quanto que foi gasto quanto que foi orçado quanto que foi o percentual que já foi gasto né que ainda falta que sobra ali e quanto que foi realizado então quanto que já foi realizado daquela informação também quanto que eu ainda tenho né quanto que eu tenho no total ali quanto que foi gasto Qual é o percentual que a gente tem ainda né foi gasto do percentual quanto foi orçado também tá bom então.
Como é que a gente faz isso bom primeira coisa que a gente vai fazer aqui esse já é um um cálculo um pouco maior né que a gente vai fazer eu vou primeiro colocar aqui um s direto vou fazer um pivô direto para pegar as despesas ali então eu vou pegar aqui então esse valor igual piv by vou abrir de novo aqui então a estrutura só para mostrar também né estrutura que a gente tem aqui ó despesa a data o forçado e o relizado nota que aqui nós temos orçado e temos a data mas poderia ser ali tá o primeiro dia primeiro né mas poderia ser qualquer.
Dia tá aqui eu botei dia primeiro mas poderia ser dia 15 dia 20 mas eu quero por mês então como é que a gente faz isso vamos começar aqui então aqui eu vou colocar então por exemplo eu quero apenas o que foi gasto por exemplo foi gasto eu vou botar assim então igual piv by piv by a parênteses e eu vou pegar então a nossa coluna de despesas Então vou pegar as colunas de despesas aqui vou clicar duas vezes sobre ela as.
Despesas que a gente tem ponto e vírgula e agora no Call Fields que são as colunas que eu quero que apareça aqui em cima eu vou usar uma fórmula também vou dar um Alt enter aqui então eu vou aplicar uma fórmula nele essa fórmula que eu vou aplicar vai fazer uma conversão desses valores das datas para mês e ano para isso eu vou fazer assim text abre parênteses Qual é o nosso value vou clicar duas vezes sobre ele aqui então ponto e vírgula abre asas duplas mmm barra aa então ele vai converter elas já vai fazer essa.
Conversão pro formato aquele formato de mês barra ano ele vai botar aqui diretamente pra gente já vai fazer a conversão direta dentro da função piv byy então ele permite isso também ponto e vírgula agora em values vamos pegar aqui em values eu vou pegar o seguinte valor eu vou pegar quanto que eu tenho orçado tá eu vou pegar só o orçado vou mudar aqui para orçado aqui apenas tá então vou pegar o orçado ponto e vírgula e vou dar um Sam aqui para ele somar.
Então a nossa informação Ok ponto e vírgula ah um ali B not show então vou mostrar o feld headers não vou dar um enter aqui e agora já tem toda a informação ali eu acho que ele tá na linha incorreta vou botar na linha CCO Aí sim por causa da formatação aqui embaixo que eu botei tá bom botei nas formatações então pelo que a gente pode notar esse aqui é o orçado tá só para ficar claro orçado então ter que est mostrando quanto que a gente tem.
Por mês separado orçado para cada uma das áreas Olha que interessante já most mostra os totais também já mostra os totais também tudo prontinho pra gente tá então esse aqui é só D orçado agora se eu quiser do realizado realizado é da coluna realizado correto então aqui vamos dizer que seja do realizado e esse aqui vai mandar tá esse essa célula aqui vai mandar no que vai mostrar embaixo então a gente pode aplicar isso também eu vou mudar aqui ó poderia também tá só para ficar claro poderia botar um.
Desloque aqui também poderia botar mas eu quero mostrar de outra forma meu mouse não tá muito bom hoje então você vai fazer assim eu vou apagar de novo vou colar aqui tá vou botar assim ó if if abre parênteses se aqui estiver como dar F4 dar um ponto e vírgula aqui F2 F4 aqui para travar se aqui esver igual a orçado Então faz isso aqui correto se tiver orçado Então faz isso aqui tudo ok vou até identar para ficar melhor para.
Todo mundo ver aqui ó então o orçado ele vai fazer assim ótimo ponto e vírgula vou dar Alt emper if if abre parênteses G2 F4 for igual a realizado realizado então e daí eu vou dar um pivô B de novo daí eu vou pegar essa mesma aqui paraa gente não ter que ficar repetindo tudo na mão né digitando tudo de novo e simplesmente vou mudar aqui então a nossa coluna que nós estamos eh falando ali então ele tava no orçado né eu vou pegar o realizado agora então tá.
O orçado eu vou mudar aqui vou clicar duas vezes em realizado então se for orçado faz isso se for realizado faz isso ok aqui fechar parênteses vou dar um enter vou fechar aqui então fecho parênteses de novo Dá um enter então ali realizado vamos botar aqui orçado realizado tá então ele tá fazendo aqui o que a gente mandou né quando ele tá orçado e quando tá realizado Ok e agora se eu quiser fazer a diferença entre os dois se eu quiser saber quanto que foi gasto então eu vou pegar aqui de novo.
Postar um comentário