Entonces, ya hemos aprendido qué es la referencia 3D en Excel. El hecho divertido es que la referencia normal de Excel 3D no funciona con funciones condicionales, como la función SUMIF. En este artículo, aprenderemos cómo hacer que las referencias 3D funcionen con la función SUMIF.
La fórmula genérica para SUMIF con referencia 3D en Excel
Parece complicado pero no lo es (tanto).
| = SUMPRODUCT (SUMIF (INDIRECTO ("'" & rango_nombre_de_nombres_hoja & "'!" & "Rango_criterio"), criterios, INDIRECTO ("'" & rango_nombre_de_nombres_hoja & "'!" & "Rango_suma"))) |
"'" name_range_of_sheet_names "'":Es un rango con nombre que contiene los nombres de las hojas. Esto es muy importante.
"Rango de criterio":Es la referencia de texto de los criterios que contienen rango. (Debería ser el mismo en todas las hojas para el trabajo de referencia 3-D).
criterios:Es simplemente la condición que desea poner para la suma. Puede ser un texto o una referencia de celda.
"rango suma":Es la referencia de texto del rango de suma. (Debería ser el mismo en todas las hojas para el trabajo de referencia 3-D).
Suficiente de teoría, vamos a hacer referencias en 3D con la función SUMIF funcionando.
Ejemplo: Suma por región de varias hojas usando referencias 3D de Excel:
Estamos tomando los mismos datos que tomamos en el ejemplo simple de referencia 3D. En este ejemplo, tengo cinco hojas diferentes que contienen datos similares. Cada hoja contiene los datos de un mes. En la hoja maestra, quiero la suma de unidades y la colección por región de todas las hojas. Hagámoslo primero con las Unidades. Las unidades están en el rango D2: D14 en todas las hojas.
Ahora, si usa la referencia 3D normal con la función SUMIF,
= SUMIF (Ene: Abr! A2: A14, Maestro! B4, Ene: Abr! D2: D14)
Devolverá # ¡VALOR! error. Entonces no podemos usarlo. Usaremos la fórmula genérica mencionada anteriormente.
Usando la fórmula SUMIF genérica de referencia 3D anterior de Excel, escriba esta fórmula en la celda C3:
| = SUMPRODUCTO (SUMIF (INDIRECTO ("'" & Meses & "'!" & "A2: A14"), Maestro! B3, INDIRECTO ("'" & Meses & "'!" & "D2: D14"))) |
Aquí meses es un rango con nombre que contiene los nombres de las hojas. Esto es crucial.
Cuando presionas Enter, obtienes tu salida exacta.
¿Como funciona?
El núcleo de la fórmula es la función INDIRECTA y la rango con nombre. Aquí la cuerda"'"&Meses&"'!" & "A2: A14"se traduce en una matriz de referencias de rango de cada hoja en el rango con nombre.
| {"'Jan'! D2: D14"; "'Feb'! D2: D14"; "'Mar'! D2: D14"; "'Abr'! D2: D14"} |
Esta matriz contiene el referencia de textode rangos, no los rangos reales. Ahora, dado que es una referencia de texto, la función INDIRECTO puede usarla para convertirlos en rangos reales. Esto sucede para ambas funciones INDIRECTAS. Después de resolver los textos dentro de las funciones INDIRECTAS (mantén presionado), la fórmula se ve así:
| = SUMPRODUCT (SUMIF (INDIRECT (({"'Jan'! A2: A14"; "'Feb'! A2: A14"; "'Mar'! A2: A14"; "'Apr'! A2: A14"}) , Maestro! B3, INDIRECTO ({"'Jan'! D2: D14"; "'Feb'! D2: D14"; "'Mar'! D2: D14"; "'Abr'! D2: D14"}))) |
Ahora entra en acción la función SUMIF (no la INDIRECTA, como habrás adivinado). La condición se corresponde con el primer rango"'Jan'! A2: A14". Aquí la función INDIRECTO trabaja dinámicamente y convierte este texto en el rango real (es por eso que si intenta resolver INDIRECTO primero usando la tecla F9, no obtendrá el resultado). A continuación, suma los valores coincidentes en el rango."'Jan'! D2: D14".Esto sucede para cada rango de la matriz. Finalmente, tendremos una matriz devuelta por la función SUMIF.
| = SUMPRODUCTO ({97; 82; 63; 73}) |
Ahora SUMPRODUCT hace lo que mejor sabe hacer. Suma estos valores y conseguimos que funcione nuestra función 3D SUMIF.
Así que sí, chicos, así es como pueden lograr una función SUMIF 3D. Esto es un poco complejo, estoy de acuerdo en eso. Hay mucho margen para errores en esta fórmula 3D. Le sugiero que use la función SUMIF en cada hoja en una celda definida y luego use la referencia 3D normal para resumir esos valores.
Espero haber sido lo suficientemente explicativo. Si tiene alguna duda con respecto a la referencia de Excel a cualquier otra consulta relacionada con Excel / VBA, pregunte en la sección de comentarios a continuación.
Referencia relativa y absoluta en Excel | Hacer referencias en Excel es un tema importante para todos los principiantes. Incluso los usuarios experimentados de Excel cometen errores al hacer referencia.
Referencia de hoja de trabajo dinámica | Proporcione hojas de referencia dinámicamente utilizando la función INDIRECTA de excel. Esto es simple…
Ampliación de referencias en Excel | La referencia en expansión se expande cuando se copia hacia abajo o hacia la derecha. Usamos el signo $ antes de la columna y el número de fila para hacerlo. Aquí hay un ejemplo …
Todo sobre la referencia absoluta | El tipo de referencia predeterminado en Excel es relativo, pero si desea que la referencia de celdas y rangos sea absoluta, use el signo $. Aquí están todos los aspectos de la referencia absoluta en Excel.
Articulos populares:
50 accesos directos de Excel para aumentar su productividad | Acelera tu tarea. Estos 50 accesos directos le permitirán trabajar aún más rápido en Excel.
La función BUSCARV en Excel | Esta es una de las funciones más utilizadas y populares de Excel que se utiliza para buscar valores de diferentes rangos y hojas.
CONTAR.SI en Excel 2016 | Cuente valores con condiciones usando esta asombrosa función. No necesita filtrar sus datos para contar un valor específico. La función Countif es esencial para preparar su tablero.
Cómo usar la función SUMIF en Excel | Esta es otra función esencial del tablero. Esto le ayuda a resumir valores en condiciones específicas.