Você tem um relatório que mostra as vendas de 16 representantes de vendas. Cada representante de vendas pertence a uma equipe. Como você pode criar um relatório mostrando o total de vendas de cada equipe?
Assistir vídeo
- Elabore um relatório de vendas por região e equipe
- Os dados originais têm representante de vendas e região
- Uma segunda mesa (malformada) organiza os representantes de vendas em equipes
- Método 1 de Bill: reformule os dados da hierarquia da equipe. Transforme ambos os intervalos em tabelas Ctrl + T
- Crie uma tabela dinâmica, adicionando os dados ao modelo de dados. Puxe a equipe da segunda mesa.
- Crie um relacionamento
- Mike Method2: Construa um SUMIFS onde o campo Criteria2 é um array!
- Passe o SUMIFS para a função SUMPRODUCT
- Método 3 de Bill: reorganize a tabela de hierarquia para que o representante de vendas fique à esquerda.
- Adicione um VLOOKUP aos dados originais
- Construir uma tabela dinâmica
- Mike Método 4: Use o ícone de relacionamento na guia Dados da faixa de opções
- Ao criar a tabela dinâmica, escolha Usar o modelo de dados desta pasta de trabalho
- Método 5 de Bill: Power Query. Adicione a tabela de pesquisa como apenas uma conexão
- Adicione a tabela original apenas como uma pesquisa
- Junte essas duas tabelas, agrupe-as para produzir o relatório final
Transcrição de vídeo
Dueling ExcelPodcast, Episódio 188: Relatório da Equipe de Vendas por Região.
Bill: Ei. Bem vindo de volta. É hora de outro Podcast de duelo do Excel. Sou Bill Jelen de. Estarei com a presença de Mike Girvin da ExcelIsFun. Este é o nosso episódio 188, Relatório da equipe de vendas por região.
Tudo bem, então, aqui está a pergunta que temos, um conjunto de dados aqui com vários representantes de vendas, quanto suas vendas foram por região, e algumas pessoas têm vendas em ambas as regiões, e então a empresa organizou esses 16 representantes de vendas nessas quatro vendas equipes, e estamos tentando descobrir, para cada equipe de vendas, quanta receita eles tiveram.
Tudo bem. Então, minha abordagem é, você sabe, eu não gosto desse formato aqui. Vou reorganizar esse formato em algum tipo de tabela, uma pequena hierarquia aqui, que mostra para cada equipe quem são os representantes de vendas e, se estivermos no Excel 2013 ou Excel 2016 usando Windows e não um Mac , então podemos fazer uso do modelo de dados, e, para isso, temos que pegar cada uma dessas tabelas e FORMAT AS TABLE que é CONTROL + T. Então, há a primeira tabela que eles chamam de Tabela 8 e a segunda tabela que eles chamam de Tabela 9. Vou renomear essas. Vou pegar o primeiro e vou chamá-lo de TABELA DE VENDAS e vou pegar o segundo e vou chamá-lo de HIERARQUIA DE EQUIPE, assim. Tudo bem.
Agora, verifique isso. A partir do Excel 2013, na guia INSERIR, criamos uma TABELA PIVOT a partir do primeiro conjunto de dados, mas dizemos ADICIONE ESSES DADOS AO MODELO DE DADOS, que é a maneira mais enfadonha de informar que você realmente tem o motor Power Pivot atrás do Excel 2013. Mesmo que você não esteja pagando pelo Power Pivot, mesmo que tenha apenas o nível básico do Excel Office 365 ou Excel, você tem isso. Certo, então aqui está nosso novo relatório e o que vou fazer é definitivamente quero relatar por REGION, então há REGIONS e quero ver o total de VENDAS, mas quero olhar para isso pela equipe de vendas. Veja isso. Vou escolher TODAS e isso me dá as outras tabelas neste grupo, incluindo HIERARQUIA DE EQUIPE. Vou pegar a EQUIPE e movê-la pelas COLUNAS.
Agora, a primeira coisa que vai acontecer aqui é obtermos as respostas erradas. É muito, muito normal obter as respostas erradas. Então, o que vamos fazer é clicar em CRIAR. Se você estiver em 16, você pode AUTO-DETECTAR. Vamos fingir que eles estão no Excel 2013, onde acessamos nossa TABELA DE VENDAS. Lá existe um campo chamado REPRESENTANTE DE VENDAS e está relacionado à HIERARQUIA, campo chamado REPRESENTANTE DE VENDAS, clique em OK e teremos as respostas corretas. Mike, vamos ver o que você tem.
Mike: Obrigado. Sim, o modelo de dados é uma maneira incrível de usar duas tabelas diferentes para construir uma tabela dinâmica e esse é realmente meu método preferido, mas se você tivesse que fazer isso com uma fórmula e precisasse ter EQUIPE DE VENDAS no topo de cada coluna assim, isso significa que, com a fórmula, temos que literalmente olhar através desse conjunto de dados e, para cada registro, eu tenho que perguntar, é o SALES REP = para Gigi ou Chin ou Sandy ou Sheila, e então, se é um venda líquida, devo dizer, e é a região da América do Norte.
Bem, podemos fazer isso. Podemos fazer um teste lógico AND e um teste lógico OR na função SUMIFS. SUM_RANGE, esses são todos os números, então clicarei na célula superior, CONTROL + SHIFT + DOWNARROW + F4, CRITERIA_RANGE, vou destacar a coluna SALESREP inteira, CONTROL + SHIFT + DOWNARROW + F4,. Agora, normalmente colocamos um único item como REP. DE VENDAS DE JUNHO nos critérios. Isso diz ao SUMIFS para cuspir uma resposta para JUNHO, mas, se eu destacar 4 células diferentes - 1 para cada representante de vendas - estaremos instruindo o SUMSIFS a fazer um SUMIF para cada representante de vendas individual.
Agora, quando copio essa fórmula, preciso travá-la, mas copio ao lado, ela precisa se mover. Então, tenho que apertar a tecla F4 1, 2 vezes, travar a linha, mas não a coluna. Agora vou). Esta é uma operação de matriz de argumento de função. Esse é o argumento da função. O fato de termos vários itens significa que é uma operação de array. Então, quando eu clico no final e clico em F9, SUMIFS nos obedece. Cuspiu a quantia total de June, Sioux, Poppi e Tyrone. (= SUMIFS ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7))
Agora, precisamos limitar ainda mais esses valores adicionando uma condição AND. Nós realmente precisamos que seja junho e América do Norte ou Sioux e América do Norte ou Poppi e América do Norte e assim por diante. CONTROL + Z. Simplesmente estendemos CRITERIA RANGE 2. Agora precisamos examinar a coluna REGION. CONTROL + SHIFT + DOWNARROW + F4, e clicarei na única condição, F4 1, 2, 3 vezes para bloquear a coluna, mas não a linha. Se eu clicar em no final e em F9, esses são os totais para cada um de nossos representantes de vendas na América do Norte. Quando o copiarmos, SUMIFS entregará o total para cada representante de vendas para a América do Sul. (= SÚNIFES ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, $ E8))
Observe que são apenas SUMIFS entregando vários números que precisamos adicionar. CONTROL + Z. Então, eu poderia colocá-lo nesta função SUM, mas o argumento da função SUM NÚMERO 1 não calculará essa operação de matriz corretamente sem usar CONTROL + SHIFT + ENTER. Então, vou trapacear e usar SUMPRODUCT. Agora, normalmente, SUMPRODUCT pega vários arrays e os multiplica - essa é a parte PRODUCT - e então os adiciona, mas vou apenas usar ARRAY1 e apenas usar a parte SUM de SUMPRODUCT,), CONTROL + ENTER, copiá-lo para baixo e para o lado, e como eu tenho muitas referências de células malucas, vou chegar à última em F2 e, com certeza, tem todas as células e intervalos corretos. Tudo bem. Eu vou jogar de volta para. (= SUMPRODUTO (SUMIFS ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, $ E8)))
Bill: O quê? Isso é louco. Mike. Aponte para Mike. Oh meu Deus. Colocar um intervalo de valores em SUMIFS e, em seguida, enviá-lo para SUMPRODUCTS e fazê-lo tratá-lo como um ARRAY. Ei, isso é selvagem. Devíamos apenas parar por aí. Aponte para Mike.
Tudo bem. Vamos voltar ao meu método, mas fingir que você não tem o Excel 2013. Você está de volta ao Excel 2010 ou, pior, ao Excel para Mac. Quer dizer, diz que é o Excel. Eu não sei. Só me deixa louco o que o Mac pode ou não fazer. Então, vamos pegar minha TABELA DE HIERARQUIA aqui e, como PROCV não pode olhar para a esquerda, vou pegar as informações do REP DE VENDAS, CONTROL + X, e colar. Sim, eu sei que posso indexar e combinar. Não estou com vontade de indexar e comparar hoje. Tudo bem, então, é muito simples. Aqui, = VLOOKUP, pegue aquele nome SALESREP ali, e faremos F4, 2, EXACTMATCHFALSE assim, clique duas vezes para copiá-lo. (= PROCV (A4, $ F $ 4: $ G $ 19,2, FALSO))
Now that we have all this data back in one table, simple little INSERT, PIVOT TABLE. Even if you don't have the check box at this stage of the data model, we can build our report with SALES TEAM going across, REGION going down, and SALES like that. You can even, here, let's reverse these, REGION across and add the SALES REP in like that, in case you want to see who the sales reps were, and if, by default, if you don't want that, we could just collapse the whole group. So, from here, I go to the ANALYZE tab and collapse. Alright. So, there's our sales teams by region, and then, if someone wants to say, well, who was SALES TEAM 2, we can open that up individually, something like that. Mike, you got another one?
Mike: Still got to love VLOOKUP. It does so many amazing things and, yes, I agree with you. Excel for the Mac, that's not even Excel, is it? Alright. Okay. I have another method but I'm going to have to jump over to a different workbook. So, I just have the same two data sets and I've converted them to Excel tables and named them. There's the SALES table, there's the TEAM table, and I like your Power Pivot option so much I'm going to steal that but do it a slightly different way because, as you say, if you have Excel 2013 or later, you have the Power Pivot data model there, but it gets even better. On the DATA ribbon tab -- and I have Excel 2016 -- if you have the RELATIONSHIPS button, you can just build the relationship as if it was a VLOOKUP between these two tables and it will automatically send it to the data model.
So, here's the MANAGE RELATIONSHIPS. I'm going to click NEW. I'm going to select SALES table, SALES REP. This, in essence, is our lookup value, right, and then I'm going to select the lookup table dTEAM, and the SALES REP. This is the lookup table so it can look up SALES REP and return the SALES TEAM, but there's no VLOOKUP column. It simply is two tables in our pivot table field list. Yeah. Look at that, the relationships, when I click OK, it’s sending it to the data model.
Now I'm going to click in a cell off to the side ALT+N+V to open up CREATE PIVOTTABLE dialog box and -- look at that -- it already assumes I want the data model because there's stuff in the data model. Now I click OK and I have my two tables right there. I'm going to click the drop-down, SALES TEAM to ROW, SALES REP down below ROWS, and then SALES from the SALES TABLE down to VALUES. ROW LABELS. I don't like that so I'm going to go up to SHOW IN TABULAR, right click, NUMBER FORMATTING, something like CURRENCY, click OK.
Now, just as said, we can collapse this if we do not want to see the SALES REP, and then drag REGION down to COLUMNS, and, just like that, we have all of our sales teams’ totals for each region. I could even open this up. Whether you access the data model either through the check box in CREATE PIVOTTABLES dialog box or simply DATA, RELATIONSHIPS, that is the way to go. So fast and easy, and we can pull fields from two different tables. Alright, I'm going to throw it back to.
Bill: Whoa, Mike, the RELATIONSHIPS out here on the DATA tab, I'm sure I've never noticed that and I guess, in my defense, in the smaller version of Excel here, it doesn't have a word on it. Just looks like a tiny little icon and I realize it was new. That is super, super cool.
Alright. Let’s just do one more here. I'm going to use power query. So, on the DATA tab, GET & TRANSFORM DATA. FROM A TABLE, I select the first table, and I want to take this REGION field and I'm going to pivot it, so I'm going to create a pivot table right here in power query. I'd be careful here. The values are in the SALES area. Click OK. So, now, for each SALES REP, we have their sales to NORTH AMERICA and SOUTH AMERICA, and I'm going to call this ByRep. BYREP. I'll call it BYREP, and then HOME, CLOSE & LOAD, but I'm not going to CLOSE & LOAD to the workbook. I'm going to say ONLY CREATE CONNECTION, like that.
Alright. Then, I'll come to the second one and say that I'm going to create a query FROM A TABLE, alright, and this is just going to stay exactly the way it is. We'll call this TEAMS, and CLOSE & LOAD, CLOSE & LOAD to ONLY CREATE CONNECTION, like that.
Alright. So, now we have two different reports here and I'm going to say that I want to create a COMBINE QUERY, a MERGE QUERY, and my first query is going to be called BYREP, and then I'm going to look up into the TEAMS query. Now, this part is the part that is not intuitive at all. Click on SALES REP here, click on SALES REP here, and we want ALL FROM FIRST, MATCHING FROM SECOND. Click OK. Alright. So, now, here's all of our SALES REP information, what they sold in North America, what they sold in South America, and use the expand icon here, and all we want to get is the TEAM information. I just want to call it TEAM. I don't want to call it TEAMS.TEAM. That would be crazy.
Alright. At this point, we no longer need the SALES REP information. I'll remove that column. I'll take the TEAM and move it over to the left, and then -- check this out -- GROUP BY. We're going to GROUP BY the TEAM and the NEW COLUMN NAME is going to be called NORTH AMERICA, the OPERATION is going to be SUM, the NORTH AMERICA COLUMN, and then we'll add a second one called SOUTH AMERICA, SUM, the SOUTH AMERICA COLULMN. There we go. GROUP BY TEAM, two columns, and we have our information here.
Let's order this. So, on the HOME tab, we want to SORT A to Z. SALES TEAM 1, 2, 3, 4. There's our NORTH AMERICA. There's our SOUTH AMERICA. Now, finally, we'll CLOSE & LOAD and we have our results, and -- check this out -- that's even cooler than that. So, if I go back to BILLPQ and we take POPPI and we move POPPI to SALES TEAM 2 and then come back to our results out here, alright, so, SALES TEAM 2, we should see these numbers increase. Come here and click the refresh icon, and those numbers changed, right? How cool? How cool is that?
Alright, so, wrap up. The goal today, we're going to build a sales report by region and team. The original data has sales rep in region and then there's a lookup table -- in my opinion, badly shaped -- that organizes sales reps into teams. So, my method reshaped that data into a team hierarchy data, make both ranges into Control+T tables, create a pivot table, adding the data to the data model, and then create a relationship. Mike’s method: use SUMIFS where the Criteria2 to field is an array -- didn’t know you could do that -- and then the SUMPRODUCT function. My third method: rearrange the hierarchy table so sales rep is on the left and then do a VLOOKUP building a pivot table. Mike's method: use the RELATIONSHIP icon to build a relationship first, and then a pivot table from the workbook data model. And then, the fifth version -- the no VLOOKUP-no pivot table version in case you're afraid of both of those -- power query. Add the lookup table as a connection only, add the original table as a lookup only, doing the pivot right there to get North America and South America, merge those two tables, group by, and then group by within power query, and you can refresh.
Tudo bem. Bem, ei. Eu quero agradecer a você por passar por este tão longo Podcast Duelo do Excel. Nos vemos na próxima vez em outro episódio de e ExcelIsFun.
⇬ Fazer download do arquivo
Baixe o arquivo de amostra aqui: Duel188.xlsm
