Crear vínculos de formulario de Excel para informes dinámicos con longitud variable - Excel Learning Network
Excel puede usar fórmulas y formato condicional para expandir o contraer automáticamente su informe cuando cambia el valor condicional en una celda, sin necesidad de macros. He aquí cómo hacerlo.
Las tablas de Excel son una característica poderosa introducida en Excel 2007. No sólo puede generar informes directamente sobre él, sino que también puede utilizarlo como fuente de datos para informes dinámicos, incluidos informes tipo acordeón de longitud variable.
La primera tarea es rápida y sencilla, la segunda tarea es más interesante. Veamos estas dos tareas.
Reporte directamente desde una tabla de Excel
Supongamos que tiene una tabla como esta y desea generar un informe de distribución de llamadas para cada vendedor.
La forma más sencilla es informar directamente desde la tabla. Por ejemplo, para informar sobre Alyson, ¿haría clic en Vendedor? ¿Vendedor? Filtre y seleccione Alyson de la lista para obtener la siguiente información:
Puede imprimir su informe y luego hacer lo mismo con Peter.
Sin embargo, normalmente no querrás informar directamente desde el formulario. Por ejemplo...
Es posible que desee utilizar fórmulas para mostrar información agregada a los datos de la tabla. (Por supuesto, puede agregar columnas calculadas a la tabla, pero si esos cálculos requieren muchas búsquedas, las tablas más largas pueden tardar mucho en volver a calcularse).
Es posible que deba incluir varias filas de información en su informe para cada fila de la tabla.
En este caso necesitarás crear un informe dinámico que devuelva los datos de la tabla, lo cual es una tarea muy interesante.
Presentación de informes dinámicos ?Accordion?
La siguiente imagen muestra tres informes generados a partir de la misma hoja de trabajo. Como puede ver, estos informes varían en longitud... expandiéndose y contrayéndose como un acordeón. Por eso los llamo informes de acordeón.
Al seleccionar ?*? de la lista se mostrará automáticamente el primer informe. Al seleccionar ? se mostrará el segundo informe y al seleccionar ? Los tres informes toman datos de las dos hojas de Excel mencionadas anteriormente.
Las instrucciones a continuación le mostrarán cómo crear este informe desde cero.
Se crearon dinámicamente tres informes de acordeón a partir de una hoja de cálculo.
Estos informes también son dinámicos en el sentido de que cambian dinámicamente según los criterios que usted especifica en una o más celdas.
Aquí hay una descripción general de la hoja de trabajo del informe con el comodín seleccionado como condición de CurSeller:
Nota...
El registro de asignación de llamadas en la parte superior de esta página tiene seis asignaciones. Este informe también tiene seis asignaciones, pero utiliza dos filas para cada asignación.
Este informe muestra a los vendedores en la columna D. Al seleccionar un vendedor específico, este campo no se conservará... como se muestra a continuación.
Las líneas 18 y 19 contienen fórmulas que permiten agregar distribuciones de llamadas adicionales. Puede copiar estas dos filas en su hoja de cálculo si es necesario.
Este es el mismo informe con Alyson seleccionada:
Cuando se selecciona Alyson, la estructura del informe cambia automáticamente de dos maneras. Primero, el contenido de la columna D desaparece. En segundo lugar, muestre solo los trabajos de Alyson, dejando una fila en blanco en la parte inferior del informe.
Veamos cómo crear esta magia...
Crear la tabla de registro y la tabla de clientes
El primer paso es crear en un nuevo libro la tabla de registro . Para hacer esto, ingrese la fecha y el texto que se muestran aquí.
Luego, para convertir una hoja de cálculo simple en una hoja de cálculo de Excel en Excel 2007 o posterior, seleccione la hoja de cálculo y elija ¿Insertar? ,?¿hoja? ,?¿hoja? Cree una tabla en el cuadro de diálogo Crear tabla. En el cuadro de diálogo Crear tabla, asegúrese de que Mi tabla tenga un título y luego seleccione Aceptar. Seguro.
De forma predeterminada, la primera tabla se denomina Tabla1. Para darle a la tabla el nombre Registro, seleccione cualquier celda de la tabla y luego, en el cuadro de diálogo Herramientas de tabla, elija ¿Diseño? , ingrese Registrar como nombre de la tabla en el atributo?
Del mismo modo, necesitamos una tabla "Detalles del cliente":
Dale a la tabla el nombre "Cliente".
Configurar la mesa de control
El segundo paso es configurar la mesa de control. Primero, cree una nueva hoja de trabajo en el libro de informes y asígnele el nombre Hoja de control.
La imagen de la derecha muestra el contenido completo de la mesa de control.
La celda TopRow devuelve el número de la primera fila del "Registro de distribución de llamadas". El número de la primera línea del registro de distribución de llamadas. Aquí está la fórmula para la celda que se muestra:
B2: = FILA (Registro [#Todos])
Para crear esta fórmula, ingresé...
= fila(
...
= fila(
...
La celda NumRows usa la función FILAS para devolver el registro. número de filas de datos actuales en la tabla La fórmula para la celda que se muestra es la siguiente:
B3: = FILAS (Registro [#Todos]) - 1
Cambie los dos. valores en la columna A El nombre se especifica como el nombre del rango en la columna B.
Seleccione el rango A2:B3,
Seleccione ?nombre definido?, ?cree fórmula basada en la selección >
En el cuadro de diálogo, asegúrese de que solo queden columnas a la izquierda y seleccione Aceptar.
Para configurar la lista de vendedores, ingrese el texto como se muestra y formatéelo como desee. Inicie el cuadro de diálogo "Crear a partir de la selección" nuevamente, pero esta vez asegúrese de que solo la fila superior sea la lista de vendedores.
Cree el informe
Primero en el libro de trabajo de informes. hoja de cálculo en . Asigne el nombre a la hoja de cálculo ?Informes?
Ahora, configuremos el cuadro de lista en la celda B1 como se muestra en el informe aquí (y se repite varias veces a continuación):
Seleccione la celda. B1 y luego seleccione Datos, Herramientas de datos, Validación de datos y luego, en el cuadro de diálogo Validación de datos, en el cuadro de lista Permitir Ingrese ? En el cuadro de lista Permitir, ingrese =Vendedores como Fuente y seleccione Aceptar
Ahora, los únicos datos que puedes ingresar en la celda B1 son los que ingresaste en la tabla de control Contenido de la lista de vendedores.
Utiliza "Crear nombre" para especificar el nombre del rango CurSeller para la celda. B1.
Para generar el informe real, primero ingrese la etiqueta en el rango F3:F4 /p>
Como se muestra a continuación, para centrar las etiquetas en tres columnas, ¡no combine las celdas! En su lugar, seleccione el rango F3:H4 y seleccione el iniciador de diálogo Alinear (que se muestra con la flecha roja en la imagen a continuación). La pestaña Alineación comienza a mostrarse. La pestaña Alineación del cuadro de diálogo Formato de celdas.
¿Para alineación de texto horizontal? Centrar horizontalmente en el cuadro de diálogo, luego seleccione Aceptar.
Ingrese etiquetas en el área F5:H5, luego ingrese fórmulas y datos para cada columna
Columna F. contiene números de serie que se repetirán para cada conjunto de filas del informe. La información de asignación utiliza dos filas, por lo que el mismo número de serie aparece dos veces. La fórmula en la columna H depende de los valores que se muestran en las celdas F6 y F7. Luego ingresa la siguiente fórmula...
F8: = F6 1
...y cópiala en las columnas como se muestra.
La columna G cuenta cada fila en cada grupo de filas. Las fórmulas de formato condicional dependen de estos números.
Para ingresar estos números, ingrese los valores que se muestran en las celdas G6 y G7, luego copie el rango G6:G7 en las columnas como se muestra.
La columna H contiene las fórmulas más complejas del informe. El propósito de estos números es identificar filas en la tabla de registro que contienen información especificada por el filtro CurSeller.
Esta es la fórmula de la celda H6. Lo he dividido en siete líneas para que sea más fácil de leer y explicar, pero normalmente puedes escribirlo en una sola línea.
Categoría 1: H6: = IF ($F6gt; NumRows, ? ,
Categoría 2: IFERROR (
Categoría 3: IF (CurSeller =?* ?, $F6,
Categoría 4: AGREGADO (15, 6, (1 / (Registro [Vendedor] = CurSeller))*
Fila 5: FILA (Registro [Vendedor] ), $F6)
Fila 6: -TopRow),
Fila 7 (Fila 7: ?))
Fila 1: Si la fila actual F Si el número de secuencia en la columna es mayor que el número de filas en el registro, se devuelve una cadena vacía (?). De lo contrario...
Líneas 2 y 7: si hay un error en el. fórmula a continuación, luego devuelva la cadena vacía en la fila 7. De lo contrario, devuelva el contenido de la siguiente fórmula
Fila 3: Si el usuario selecciona el carácter comodín ?*? mostrar esta fila. Por lo tanto, el número de secuencia se muestra en la columna F.
Filas 4 y 5: use la función AGREGAR para devolver el número de fila más pequeño que contiene los datos de Alyson (suponiendo que su nombre esté seleccionado). p>Cuando copia esta fórmula en la celda H8, $F6 se convierte en $F8, que tiene un valor de 2. Por lo tanto, la función AGREGAR devuelve el segundo número de fila más pequeño que contiene los datos de Alyson copiados en H10. , y así sucesivamente.
En la línea 4, la sección (Log [SalesPerson] = CurSeller) genera una matriz temporal que contiene los valores VERDADERO y FALSO. Al dividir una matriz entre 1, devuelve una matriz con valores. igual a 1 y #DIV/0! Incorrecto, que es lo que queremos en este caso.
Luego, cuando multiplicamos por Cuando la fila 5 devuelve el número de línea, obtienes una matriz de números de línea y. #DIV/0!
Dado que el 6 en el segundo argumento de AGGREGATE le dice a la función que ignore el error, y dado que el 6 en su primer argumento, el 15 le dice a la función que use la función PEQUEÑA, entonces AGGREGATE regresará el número de fila más pequeño sin errores (especificado por el valor en la celda F6), que es la primera fila donde se encuentra el nombre de Alyson (mejor). Para comprender más acerca de cómo funciona la fórmula AGREGAR en esta situación, estudie los Ejemplos 8 a 11 en la página. Página de función AGREGAR.
Línea 6: El número de fila devuelto en la línea 4 es el número de fila de la hoja de trabajo; sin embargo, necesitamos el número de índice de fila en la tabla de registro; Para obtener este número, necesitamos usar el número de fila devuelto por la celda TopRow en la hoja de trabajo.
Copie la fórmula en la columna H como se muestra.
Creación del cuerpo del informe
Este es el informe, repetido nuevamente desde arriba:
Con estas tres columnas de control, ahora podemos devolver lo que aparece en el texto del informe.
Por lo tanto, ingrese la fórmula que se muestra...
A3: =? IF (CurSeller =?*?, ?Todos los vendedores?, PROPER (CurSeller)) Distribución de llamadas
Esta fórmula devuelve el texto del título del informe.
Los rangos A4:C5 contienen las etiquetas mostradas. Simplemente ingrésalo.
La celda D4 contiene la siguiente fórmula:
D4: =SI (CurSeller = ? *?, ? Vendedor?, ?)
Cuando la condición CurSeller es igual ? *Esta fórmula devolverá una cadena vacía.
En la línea 6, ingrese...
A6: = SI ($H6 =?, ?, INDEX (Registro [Cliente], $H6))
B6: = SI ($H6 =?, ?, INDEX (Registro [Fecha], $H6))
C6: = SI ($H6 =?, ?, INDEX (Registro [Tipo de llamada] ], $H6))
La columna H sirve como interruptor. Si la celda especificada en esta columna (determinada por la función ÍNDICE) contiene una cadena vacía (?), entonces queremos que la hoja de trabajo no muestre nada, ni números ni texto ni formato. Cuando la celda H6 no está vacía, estas fórmulas devuelven el nombre del cliente, la fecha y el tipo de llamada.
D6: = IF (OR (CurSeller lt; gt; ? *? , $H6 =?) , ? , INDEX (Registro [Vendedor], $H6))
Unidad La celda D6 agrega otra prueba. Si CurSeller es un comodín o la celda H6 está vacía, se devuelve una cadena vacía. En caso contrario será devuelto a ventas.
Las siguientes tres fórmulas en la tabla CustomerDetails devuelven datos:
A7: =IF ($H6 =?, ?, INDEX (Clientes [Empresa], MATCH ($A6, Clientes [Cliente], 0)))
B7: = SI ($H6 =?,?, INDEX (Clientes [Teléfono], MATCH ($ A6, Clientes [Cliente], 0)))
C7: = IF ($H6 =?, ?, INDEX (Clientes [Ventas Pasadas], MATCH ($A6, Clientes [Cliente], 0)))
Estas tres fórmulas se parecen. Obtienen el nombre del cliente en la celda A6, el nombre del cliente en la tabla Detalles del cliente y encuentran el Nombre del cliente en la tabla Detalles del cliente. El nombre del cliente en la tabla "CustomerDetails" busca el número de fila de ese cliente, luego devuelve cada fila en la tabla "Company" y luego devuelve ?Phone?PastSales?
en esa fila. Después de ingresar el. fórmula en las filas 19 y 7, copie la fórmula en la fila 19.
Formato del informe
El siguiente es el informe completo como referencia:
Líneas 3 a 5 especifique todos los colores de relleno y fuentes requeridos.
Ahora, especifiquemos el formato condicional que hará que el informe funcione como por arte de magia. Recuerde el informe al especificar las fórmulas en el trabajo con texto. Si se supone que la fila o columna está en blanco, la fórmula de la celda afectada ya aparecerá como una cadena vacía.
Este informe utiliza cuatro formatos condicionales...
Formato condicional 1
Este formato controla la segunda línea de cada conjunto de dos líneas en el cuerpo del informe. Se asigna de la siguiente manera:
Selecciona el rango A6:D19, asegurándote de que la celda A6 sea la celda activa.
¿Seleccionar?Página de inicio? ,?¿estilo? ,?Formato condicional? ,?Nueva regla
En el cuadro de diálogo Nueva regla de formato, seleccione ¿Usar una fórmula para determinar qué celdas formatear?
En el cuadro de edición Formato de valor cuando esta fórmula es verdadera, ingresa:
= AND ($G6 = 2, $H6 lt;gt;?)
(Nota: el número de fila en esta fórmula y las otras fórmulas de formato condicional a continuación deben ser el mismo que el número de fila de la celda activa).
En el cuadro de diálogo Nueva regla de formato, seleccione "Formato ". Comienza el cuadro de diálogo Formato de celdas.
En la pestaña "Bordes", especifique el borde inferior.
Seleccione Aceptar hasta que desaparezcan todos los cuadros de diálogo.
Formato condicional 2
Este formato controla la alternancia de colores de relleno azul y blanco en el cuerpo del informe. Los pasos son los siguientes:
Seleccione el rango A6:D19 y asegúrese de que la celda A6 sea la celda activa.
¿Seleccionar?Página de inicio? ,?¿estilo? ,?Formato condicional? ,?Nueva regla
En el cuadro de diálogo Nueva regla de formato, seleccione ¿Usar una fórmula para determinar qué celdas formatear?
En el cuadro de edición denominado "Formatear como valores para los cuales esta fórmula es verdadera:", ingrese:
= AND (ISODD ($F6), $H6lt;gt; ?)
En el cuadro de diálogo "Nueva regla de formato", seleccione "Formato". ¿Formatear celdas?
En la pestaña Relleno, especifique un relleno azul claro.
Seleccione Aceptar hasta que desaparezcan todos los cuadros de diálogo.
Formato condicional 3
Este formato es uno de los dos formatos que ocultan el contenido de la columna D:
Seleccione el rango D4:D5 y asegúrese de que la celda D4 es la celda activa.
¿Seleccionar?Página de inicio? ,?¿estilo? ,?Formato condicional? ,?Nueva regla
En el cuadro de diálogo Nueva regla de formato, seleccione ¿Usar una fórmula para determinar qué celdas formatear?
¿En el texto marcado con ?Valor de formato? (donde la fórmula es verdadera) en el cuadro de edición, escriba:
= CurSeller lt; gt;*?
En el cuadro de diálogo Nueva regla de formato, seleccione ?Formatear celdas?
En la pestaña Relleno, especifique Sin color.
En la pestaña "Borde", especifique un borde izquierdo negro.
Seleccione Aceptar hasta que desaparezcan todos los cuadros de diálogo.
Formato condicional 4
Este formato es otro formato que oculta el contenido de la columna D:
Seleccione el rango D6:D19 y asegúrese de que la celda D6 esté célula activa.
¿Seleccionar?Página de inicio? ,?¿estilo? ,?Formato condicional? ,?Nueva regla
En el cuadro de diálogo Nueva regla de formato, seleccione ¿Usar una fórmula para determinar qué celdas formatear?
¿En el texto marcado con ?Valor de formato? (donde la fórmula es verdadera) en el cuadro de edición, escriba:
= CurSeller lt; gt;*?
En el cuadro de diálogo Nueva regla de formato, seleccione ?Formatear celdas?
En la pestaña Relleno, especifique Sin color.
En la pestaña "Bordes", especifique "Sin bordes".
Seleccione Aceptar hasta que desaparezcan todos los cuadros de diálogo.
Ahora deberías tener un informe de tu trabajo con el acordeón.