Cuadros de Doble Entrada desde Base Datos
Hola Amigos:
Tengo una extensa Base de datos sobre la que constantemente debo efectuar análisis en cuadros de doble entrada.
Digamos, Mi base esta formada por Campo1, Campo2, Campo3,..., Campo
Y un cuadro puede estar dado por:en las Filas Campo1, en las columnas Campo2, y en las intercepciones la suma de todos los Campos3 que cumplan con la fila y la columna.
La solución fueron las tablas dinámicas. Pero el problema surge (tal vez sea por mi poco conocimiento en el tema) cuando debo actualizarlas (algo que debo hacer frecuentemente). Y es que al actualizar pierdo todo el formato que le di a la tabla para la presentación e impresión, (en algunos casos debo insertar filas, sombrear algunas celdas, etc, etc), ademas aparecen subtotales no deseables en mi presentación.
La pregunta: Existe alguna fórmula (algo así como sumar.si) que leyendo la etiqueta de la fila y la etiqueta de la columna, ¿pueda efectuarme sumas o conteos filtrados?
Observación: Intente hacerlo con BDSuma, pero como empleo muchos cuadros me llenaría de matrices y matrices de condiciones, que me parece poco practico. Existe algún otro medio limpio, ¿conciso e ingenioso?
Muchísimas gracias por cualquier luz que pudieran darme
Tengo una extensa Base de datos sobre la que constantemente debo efectuar análisis en cuadros de doble entrada.
Digamos, Mi base esta formada por Campo1, Campo2, Campo3,..., Campo
Y un cuadro puede estar dado por:en las Filas Campo1, en las columnas Campo2, y en las intercepciones la suma de todos los Campos3 que cumplan con la fila y la columna.
La solución fueron las tablas dinámicas. Pero el problema surge (tal vez sea por mi poco conocimiento en el tema) cuando debo actualizarlas (algo que debo hacer frecuentemente). Y es que al actualizar pierdo todo el formato que le di a la tabla para la presentación e impresión, (en algunos casos debo insertar filas, sombrear algunas celdas, etc, etc), ademas aparecen subtotales no deseables en mi presentación.
La pregunta: Existe alguna fórmula (algo así como sumar.si) que leyendo la etiqueta de la fila y la etiqueta de la columna, ¿pueda efectuarme sumas o conteos filtrados?
Observación: Intente hacerlo con BDSuma, pero como empleo muchos cuadros me llenaría de matrices y matrices de condiciones, que me parece poco practico. Existe algún otro medio limpio, ¿conciso e ingenioso?
Muchísimas gracias por cualquier luz que pudieran darme
1 Respuesta
Respuesta de 230283
1
Comprendo tu caso y te voy adelantando que la solución esta con Sumar. Si(). Dentro de breves momentos por este mismo medio te doy la solución a tu caso. No te preocupes que dentro de poco regreso.
Aun no fnalices hasta que yo te lo diga
Christian
Aun no fnalices hasta que yo te lo diga
Christian
Bueno antes que nada te sugiero que tu base de datos en Excel debe ser de forma tabular, es decir que no haya en su composición celdas combinadas.
Tu base de datos puede tener varias columnas. El ejemplo que te daré es intuitivo. Hazlo y al hacerlo podrás fácilmente generalizarlo y acoplarlo a tu trabajo o proyecto.
1.- Asumamos que el ejemplo tiene 3 columnas. La organización de la base de datos es más o menos de esta manera:
[A1] Pais | [B1] Meses | [C1] PBI
[A2] Peru | [B2] Ene | [C2] 30750
[A3] Mexico | [B3] Ene | [C3] 60750
[A4] Argentina | [B4] Ene | [C4] 45320
[A5] Peru | [B5] Feb | [C5] 60000
[A6] Mexico | [B6] Feb | [C6] 8900
[A7] Argentina | [B7] Feb | [C7] 45700
****
Lo de arriba es la dispodicion de datos que están en "Hoja1". COmo veras comprende el rango A1:C7
2.- El truco es crear una columna auxiliar a tu base de datos. Formaríamos una cuarta columna.
3.- En la celda D2 de Hoja1 escribe la siguiente fórmula:
=A2&B2
[Fijate bien en el simbolo "&" que sirve para concatenar. La fórmula no tiene espacios en blanco]
4.- Arrastra esta fórmula desde D2 hasta D7
5.- Situate ahora en Hoja2 y coloquemos los títulos de filas y columnas que servirán como reporte:
[A2] Perú
[A3] Mexico
[A4] Argentina
****
[B1] Ene
[B2] Feb
***
6.- Ahora ingresemos esta formula en la celda B2 tal como esta:
=SUMAR.SI(Hoja1!$D:$D;Hoja2!$A2&Hoja2!B$1;Hoja1!$C:$C)
[Fijate en tu separador de argumentos que puede se punto y coma o coma. Yo he utilizado punto y coma ";"]
7.- El resultado en B2 debe dar 30750. LA fórmula esta probado y funciona.
Ahora esta misma fórmula arrastra hacia abajo y luego a la derecha para que se complete el reporte. Gracias a las referencias es posible arrastrar sin encesidad de reescribir la fórmula:
Tu base de datos puede tener varias columnas. El ejemplo que te daré es intuitivo. Hazlo y al hacerlo podrás fácilmente generalizarlo y acoplarlo a tu trabajo o proyecto.
1.- Asumamos que el ejemplo tiene 3 columnas. La organización de la base de datos es más o menos de esta manera:
[A1] Pais | [B1] Meses | [C1] PBI
[A2] Peru | [B2] Ene | [C2] 30750
[A3] Mexico | [B3] Ene | [C3] 60750
[A4] Argentina | [B4] Ene | [C4] 45320
[A5] Peru | [B5] Feb | [C5] 60000
[A6] Mexico | [B6] Feb | [C6] 8900
[A7] Argentina | [B7] Feb | [C7] 45700
****
Lo de arriba es la dispodicion de datos que están en "Hoja1". COmo veras comprende el rango A1:C7
2.- El truco es crear una columna auxiliar a tu base de datos. Formaríamos una cuarta columna.
3.- En la celda D2 de Hoja1 escribe la siguiente fórmula:
=A2&B2
[Fijate bien en el simbolo "&" que sirve para concatenar. La fórmula no tiene espacios en blanco]
4.- Arrastra esta fórmula desde D2 hasta D7
5.- Situate ahora en Hoja2 y coloquemos los títulos de filas y columnas que servirán como reporte:
[A2] Perú
[A3] Mexico
[A4] Argentina
****
[B1] Ene
[B2] Feb
***
6.- Ahora ingresemos esta formula en la celda B2 tal como esta:
=SUMAR.SI(Hoja1!$D:$D;Hoja2!$A2&Hoja2!B$1;Hoja1!$C:$C)
[Fijate en tu separador de argumentos que puede se punto y coma o coma. Yo he utilizado punto y coma ";"]
7.- El resultado en B2 debe dar 30750. LA fórmula esta probado y funciona.
Ahora esta misma fórmula arrastra hacia abajo y luego a la derecha para que se complete el reporte. Gracias a las referencias es posible arrastrar sin encesidad de reescribir la fórmula:
¡ Mi buen 230283 !
Antes que nada, gracias, muchas gracias por tu ayuda y por la claridad de tu explicación.
La salida que me ofreces es concisa, rápida y brillante. Mereces la mejor puntuación que pueda darte.
Sin embargo tengo una complicación:
1.- Mi BDatos es Tabular... eso esta OK
2.- La BD tiene + de 7,000 registros, y constantemente me llegan hojas excel (tabulares preestablecidos) de varias fuentes que consolido en una sola tabla.
3.-Algunos campos a modo de ejemplo son: fecha/mes/centroMedico/Tratamiento/Aplicacion/Sexo/Edad/Monto/TipoPac/ClasePac/Fidelidad/etc.
4.-Debo presentar + de 20 cuadros (semanalmente) en base a estos datos: por ejemplo, Tratamiento/Mes; TipoPac/Tratamiento; CentroMedico/Tratamiento; Fidelidad/ClasePac; Respuesta/Edad; etc. etc.
Mi preocupación:
1.- Según tu sugerencia, debo crear una columna llave(la concatenación) por cada cuadro a elaborar. Tratándose de tantos registros me preocupa la cantidad de fórmulas activas. Y el estar añadiendo columnas a la base.
Sin embargo, si no hay otra salida, es el camino que voy a seguir, pero tengo la esperanza que tengas alguna otra idea brillante.
Tu opinión es importante para mi.
Cordialmente,
Pepe
Antes que nada, gracias, muchas gracias por tu ayuda y por la claridad de tu explicación.
La salida que me ofreces es concisa, rápida y brillante. Mereces la mejor puntuación que pueda darte.
Sin embargo tengo una complicación:
1.- Mi BDatos es Tabular... eso esta OK
2.- La BD tiene + de 7,000 registros, y constantemente me llegan hojas excel (tabulares preestablecidos) de varias fuentes que consolido en una sola tabla.
3.-Algunos campos a modo de ejemplo son: fecha/mes/centroMedico/Tratamiento/Aplicacion/Sexo/Edad/Monto/TipoPac/ClasePac/Fidelidad/etc.
4.-Debo presentar + de 20 cuadros (semanalmente) en base a estos datos: por ejemplo, Tratamiento/Mes; TipoPac/Tratamiento; CentroMedico/Tratamiento; Fidelidad/ClasePac; Respuesta/Edad; etc. etc.
Mi preocupación:
1.- Según tu sugerencia, debo crear una columna llave(la concatenación) por cada cuadro a elaborar. Tratándose de tantos registros me preocupa la cantidad de fórmulas activas. Y el estar añadiendo columnas a la base.
Sin embargo, si no hay otra salida, es el camino que voy a seguir, pero tengo la esperanza que tengas alguna otra idea brillante.
Tu opinión es importante para mi.
Cordialmente,
Pepe
Lamentablemente en Excel no todo es color de rosa. Las tablas dinámicas debo admitir que es una maravilla, pero tiene sus piedras en el camino como tu manifiestas y existen otras más que crea en usuarios noveles como tu crea confusión y extrañeza.
Las funciones integradas de bases de datos que también haces mención tiene su limitación dado que necesariamente tenemos que crear manualmente una Hoja de criterios.
Las alternativas serian la Tabla dinámica que son excelentes para resumir grandes volúmenes de datos de múltiples maneras. Me comentas que aparecer filas que no deseas ver. TE comento que puedes ocultarlas si gustas.
Lo que necesitas es que tome valores tanto de filas como de columnas, pero teniendo en consideración los títulos de filas y comunes lo cual facilita. Con una sola fórmula haríamos una buena mezcla de referencia y solo queda arrastrarlas.
Si estuviera en tu lugar optaría por el consejo que te brindo. Claro esta que la fórmula que te ofrecí es para que te arroje un ratio o indicador especifico. Según tienes varios ratios a presentar tendrías que amoldar o editar la fórmula y arrastrar de nuevo. El empleo de columnas auxiliares en base de datos en Excel es común. Puedes ocultar o "Agrupar" las columnas de tal manera que no las veas. Al ocultarlas no afectaría el calculo que realice Excel.
Espero que la retroalimentación haya sido de tu alivio.
Te pediría ahora por favor que puedas finalizar y puntuar al final de esta página, dado que existen muchos usuarios que desean consultar.
Buena suerte!
Christian
Las funciones integradas de bases de datos que también haces mención tiene su limitación dado que necesariamente tenemos que crear manualmente una Hoja de criterios.
Las alternativas serian la Tabla dinámica que son excelentes para resumir grandes volúmenes de datos de múltiples maneras. Me comentas que aparecer filas que no deseas ver. TE comento que puedes ocultarlas si gustas.
Lo que necesitas es que tome valores tanto de filas como de columnas, pero teniendo en consideración los títulos de filas y comunes lo cual facilita. Con una sola fórmula haríamos una buena mezcla de referencia y solo queda arrastrarlas.
Si estuviera en tu lugar optaría por el consejo que te brindo. Claro esta que la fórmula que te ofrecí es para que te arroje un ratio o indicador especifico. Según tienes varios ratios a presentar tendrías que amoldar o editar la fórmula y arrastrar de nuevo. El empleo de columnas auxiliares en base de datos en Excel es común. Puedes ocultar o "Agrupar" las columnas de tal manera que no las veas. Al ocultarlas no afectaría el calculo que realice Excel.
Espero que la retroalimentación haya sido de tu alivio.
Te pediría ahora por favor que puedas finalizar y puntuar al final de esta página, dado que existen muchos usuarios que desean consultar.
Buena suerte!
Christian
Muchas gracias, has sido de gran ayuda.
Y valoro mucho la dedicación que das en tus respuestas.
Te comento que estuve indagando, y alguien sugirió el empleo de la función sumaproducto(), aun no lo he experimentado, pero parece prometer.
Nuevamente, muchas gracias
Y valoro mucho la dedicación que das en tus respuestas.
Te comento que estuve indagando, y alguien sugirió el empleo de la función sumaproducto(), aun no lo he experimentado, pero parece prometer.
Nuevamente, muchas gracias
