Tiene un informe que muestra las ventas de 16 representantes de ventas. Cada representante de ventas pertenece a un equipo. ¿Cómo se puede crear un informe que muestre las ventas totales de cada equipo?
Ver video
- Cree un informe de ventas por región y equipo
- Los datos originales tienen representante de ventas y región
- Una segunda tabla (mal formada) organiza a los representantes de ventas en equipos
- Método de facturación 1: reformar los datos de la jerarquía del equipo. Convierta ambos rangos en tablas Ctrl + T
- Cree una tabla dinámica, agregando los datos al modelo de datos. Saque al equipo de la segunda mesa.
- Crea una relación
- Mike Method2: Cree un SUMIFS donde el campo Criteria2 sea una matriz.
- Pase SUMIFS a la función SUMPRODUCT
- Método de facturación 3: reorganice la tabla de jerarquía de modo que el representante de ventas esté a la izquierda.
- Agregar una VLOOKUP a los datos originales
- Construye una tabla dinámica
- Método 4 de Mike: use el icono Relación en la pestaña Datos de la cinta
- Cuando cree la tabla dinámica, elija Usar el modelo de datos de este libro
- Método de facturación 5: Power Query. Agregue la tabla de búsqueda como solo conexión
- Agregue la tabla original solo como una búsqueda
- Fusionar esas dos tablas, agruparlas para producir el informe final
Transcripción del video
Dueling ExcelPodcast, Episodio 188: Informe del equipo de ventas por región.
Bill: Oye. Dar una buena acogida. Es hora de otro Podcast de Dueling Excel. Soy Bill Jelen de. Me acompañará Mike Girvin de ExcelIsFun. Este es nuestro episodio 188, Informe del equipo de ventas por región.
Muy bien, aquí está la pregunta que tenemos, un conjunto de datos aquí con varios representantes de ventas, cuánto fueron sus ventas por región, y algunas personas tienen ventas en ambas regiones, y luego la empresa ha organizado esos 16 representantes de ventas en estas cuatro ventas. equipos, y estamos tratando de averiguar, para cada equipo de ventas, cuántos ingresos obtuvieron.
Bien. Entonces, mi enfoque para esto es, ya sabes, no me gusta este formato aquí. Voy a reorganizar ese formato en una especie de tabla, una pequeña jerarquía aquí, que muestra para cada equipo quiénes son los representantes de ventas y luego, si se proporciona, estamos en Excel 2013 o Excel 2016 usando Windows y no una Mac. , entonces podemos hacer uso del modelo de datos y, para ello, tenemos que tomar cada una de estas tablas y FORMAT AS TABLE que es CONTROL + T. Entonces, está la primera tabla a la que llaman Tabla 8 y la segunda tabla a la que llamarán Tabla 9. Voy a cambiarles el nombre. Voy a tomar el primero y lo voy a llamar TABLA DE VENTAS y voy a tomar el segundo y lo voy a llamar JERARQUÍA DE EQUIPO, así. Bien.
Ahora, mira esto. A partir de Excel 2013, en la pestaña INSERTAR, creamos una TABLA PIVOT a partir del primer conjunto de datos, pero decimos AGREGAR ESTE DATOS AL MODELO DE DATOS, que es la forma más aburrida de hacerle saber que en realidad tiene el motor Power Pivot detrás de Excel. 2013. Incluso si no está pagando por Power Pivot, incluso si solo tiene el nivel básico de Excel Office 365 o Excel, lo tiene. Muy bien, aquí está nuestro nuevo informe y lo que voy a hacer es definitivamente quiero informar por REGIÓN, así que están las REGIONES, y quiero ver las VENTAS totales, pero quiero ver esto por equipo de ventas. Mira esto. Voy a elegir TODAS y eso me da las otras tablas de este grupo, incluida la JERARQUÍA DE EQUIPOS. Tomaré el EQUIPO y lo moveré por las COLUMNAS.
Ahora, lo primero que va a suceder aquí es que obtengamos las respuestas incorrectas. Es muy, muy normal obtener respuestas incorrectas. Entonces, lo que vamos a hacer es hacer clic en CREAR. Si estás en el '16, puedes AUTO-DETECTAR. Supongamos que están en Excel 2013 donde vamos a nuestra TABLA DE VENTAS. Hay un campo llamado REPRESENTANTE DE VENTAS y está relacionado con la JERARQUÍA, campo llamado REPRESENTANTE DE VENTAS, haga clic en Aceptar y tenemos las respuestas correctas. Mike, veamos qué tienes.
Mike: Gracias. Sí, el modelo de datos es una manera increíble de ir con dos tablas diferentes para construir una tabla dinámica y ese es realmente mi método preferido, pero si tuviera que hacerlo con una fórmula y necesitara tener EQUIPO DE VENTAS en la parte superior de cada columna así, eso significa que, con la fórmula, literalmente tenemos que revisar este conjunto de datos y, para cada registro, tengo que preguntar, ¿es el REPRESENTANTE DE VENTAS = para Gigi o Chin o Sandy o Sheila, y luego, si es un venta neta, tengo que decir, y es la región de América del Norte.
Bueno, podemos hacer eso. Podemos hacer una prueba lógica AND y una prueba lógica OR en la función SUMIFS. SUM_RANGE, esos son todos los números, así que voy a hacer clic en la celda superior, CONTROL + SHIFT + DOWNARROW + F4, CRITERIA_RANGE, voy a resaltar toda la columna SALESREP, CONTROL + SHIFT + DOWNARROW + F4,. Ahora, normalmente colocamos un solo elemento como REPRESENTANTE DE VENTAS JUNIO en los criterios. Eso le dice a SUMIFS que escupe una respuesta para JUNIO, pero, si resalto 4 celdas diferentes, 1 para cada representante de ventas, le indicamos a SUMSIFS que haga un SUMIF para cada representante de ventas individual.
Ahora, cuando copio esta fórmula, la necesito bloqueada, pero la copio a un lado, debe moverse. Entonces, tengo que presionar la tecla F4 1, 2 veces, bloquear la fila, pero no la columna. Ahora voy a). Esta es una operación de matriz de argumentos de función. Ese es el argumento de la función. El hecho de que tengamos varios elementos significa que es una operación de matriz. Entonces, cuando hago clic al final y presiono F9, SUMIFS nos obedeció. Escupió la cantidad total de junio, Sioux, Poppi y Tyrone. (= SUMIFS ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7))
Ahora, necesitamos limitar aún más esas cantidades agregando una condición AND. Realmente necesitamos que sea junio y Norteamérica o Sioux y Norteamérica o Poppi y Norteamérica, y así sucesivamente. CONTROL + Z. Simplemente ampliamos, RANGO DE CRITERIOS 2. Ahora tenemos que mirar a través de la columna REGIÓN. CONTROL + MAYÚS + FLECHA ABAJO + F4, y voy a hacer clic en la condición única, F4 1, 2, 3 veces para bloquear la columna pero no la fila. Si hago clic al final y F9, esos son los totales de cada uno de nuestros representantes de ventas en Norteamérica. Cuando lo copiemos, SUMIFS entregará el total para cada representante de ventas para América del Sur. (= SUMIFS ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, $ E8))
Tenga en cuenta que solo SUMIFS entrega varios números que debemos sumar. CONTROL + Z. Por lo tanto, podría ponerlo en esta función SUMA, pero el argumento NÚMERO 1 de la función SUMA no calculará esta operación de matriz correctamente sin usar CONTROL + MAYÚS + ENTRAR. Entonces, voy a hacer trampa y usar SUMPRODUCT. Ahora, normalmente, SUMPRODUCT toma múltiples matrices y las multiplica, esa es la parte de PRODUCTO, y luego las agrega, pero solo voy a usar ARRAY1 y solo usaré la parte SUM de SUMPRODUCT,), CONTROL + ENTER, cópielo hacia abajo y hacia un lado, y como tengo muchas referencias de celda locas, voy a llegar a la última en F2 y, efectivamente, tiene todas las celdas y rangos correctos. Bien. Voy a volver a. (= SUMPRODUCTO (SUMIFS ($ B $ 4: $ B $ 45, $ A $ 4: $ A $ 45, F $ 4: F $ 7, $ C $ 4: $ C $ 45, $ E8)))
Bill: ¿Qué? Eso es una locura. Miguel. Señale a Mike. Oh Dios mío. Poner un rango de valores en SUMIFS y luego enviarlo a SUMPRODUCTS y hacer que lo trate como un ARRAY. Oye, eso es salvaje. Deberíamos detenernos allí mismo. Señale a Mike.
Bien. Volvamos a mi método, pero finjamos que no tienes Excel 2013. Has vuelto a Excel 2010 o, peor aún, a Excel para Mac. Quiero decir, dice que es Excel. No lo sé. Me vuelve loco lo que la Mac puede o no puede hacer. Entonces, vamos a llevar mi TABLA DE JERARQUÍA aquí y, como BUSCARV no puede mirar a la izquierda, tomaré la información del REPRESENTANTE DE VENTAS, CONTROL + X, y pegaré. Sí, sé que puedo indexar y comparar. No estoy de humor para indexar y igualar hoy. Muy bien, es realmente simple. Aquí, = VLOOKUP, tome ese nombre de SALESREP allí, y F4, 2, EXACTMATCHFALSE así, haga doble clic para copiarlo. (= BUSCARV (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.
Bien. Este hola. Quiero agradecerles por pasar por este largo Podcast Dueling Excel. Nos vemos la próxima vez para otro episodio de ExcelIsFun.
Descargar archivo
Descarga el archivo de muestra aquí: Duel188.xlsm
