Crear columnas y filas calculadas con fórmulas de Excel

Las fórmulas de Excel le permiten agregar columnas calculadas y totales de fila que no existen en su documento QlikView o app de Qlik Sense original.

Lo que hará

Haga lo siguiente:

1. Añada totales de fila usando la función SUMA.
2. Añada columnas calculadas Puede hacer esto de dos maneras:

1. Tablas de Excel: Puede crear una tabla utilizando las funciones de tabla de Excel. Este método permite un mejor rendimiento en la creación de informes, pero requiere un mayor conocimiento de Excel. Recomendamos utilizar este enfoque.

2. Niveles: Inserte etiquetas de campo en un nivel y después añada fórmulas de Excel. Esto es sencillo de implementar, pero utilizar niveles puede afectar al rendimiento en la generación de informes.

Esta página muestra cómo crear columnas calculadas utilizando ambos enfoques. Para demostrarlo, calcularemos una tercera columna que reste los valores de una columna de los valores de otra columna. Los valores derivados de la tercera columna se dividirán por los correspondientes valores de una de las dos columnas originales.

Este tutorial utiliza datos de QlikView que se pueden encontrar en Archivos de muestra (solo en inglés). También puede usar sus propios datos de QlikView o Qlik Sense.

Acerca de los cálculos

Si desea minimizar el tiempo de creación de informes, puede agregar los cálculos deseados al script de carga de QlikView o Qlik Sense.

Este ejemplo utiliza fórmulas de Excel simples, pero puede insertar fórmulas más complejas si lo necesita. También puede insertar fórmulas de Qlik. Vea: Fórmulas de Qlik Sense y QlikView (solo en inglés).

Añadir una tabla de Excel con columnas calculadas y totales

Esta es la primera manera de crear columnas calculadas. Añadiremos el objeto fuente de QlikView al nodo Tables y después crearemos una tabla de Excel.

Añadir la tabla

Haga lo siguiente:

1. Cree un nuevo informe de Excel o abra uno existente.

2. Haga clic con el botón derecho en el nodo Tablas y seleccione Añadir objetos.

3. Seleccione un gráfico de la lista. Por ejemplo, añada Top 10 Products. Haga clic en Aceptar.

   

4. Haga clic en +, a la izquierda, para ampliar el nodo de la tabla CH184_Table - Top 10 Products.
5. De una en una, seleccione las columnas de CH184_Table - Top 10 Products y desmarque la casilla Keep Source Formats.

6. Mantenga pulsada la tecla CTRL y seleccione los campos de la tabla que desee agregar a la hoja.

   Por ejemplo: Product Name, Total Sales y Gross Profit. Arrástrelos a una fila vacía de celdas.

   

7. Seleccione las celdas que contengan el encabezado, las etiquetas de campo y una fila en blanco debajo.
8. En la cinta de opciones de Excel, haga clic en la pestaña Insertar y después clic en Tabla.

9. Asegúrese de que el indicador La tabla tiene encabezados esté habilitado y luego haga clic en Aceptar.

   

10. En el panel izquierdo, haga clic en Extras. Arrastre una etiqueta deleterow a una de las celdas de la fila vacía bajo la tabla.

    

    Esto eliminará la fila vacía del informe final.

Añadir la columna calculada

Haga lo siguiente:

1. Seleccione la primera celda vacía a la derecha de las etiquetas de campo.

2. Inserte la fórmula =[@[Total Sales]]-[@[Gross Profit]].

   Excel añadirá automáticamente esta nueva columna a la tabla.

   

3. Escriba un encabezado de columna. Por ejemplo: Costes.

4. A continuación, utilice la herramienta de diseño de tablas de Excel para aplicar el formato.

Añadir totales

Haga lo siguiente:

1. Seleccione una celda vacía debajo de la tabla. Por ejemplo, D8.

2. Inserte una fórmula SUMA en la celda. El rango de la función SUMA debe incluir:

   • La dirección de la celda en la fila que contiene la etiqueta de apertura de nivel.
   • La celda que contiene la etiqueta de campo.
   • La dirección de al menos una celda vacía inmediatamente debajo de ella.

   En este ejemplo, la función podría ser: =SUM(D4:7).

3. Haga clic y arrastre esta fórmula a E8 y F8.

4. Aplique las herramientas de formato de Excel a los elementos de la fila.

   Por ejemplo: los totales Total Sales y Gross Profit podrían tener el formato de Moneda.

   

Generar una vista previa del informe

Haga lo siguiente:

1. Haga clic en Vista previa.

   Excel inicia y muestra su informe.

2. Verá una tabla, con una columna que contiene los resultados calculados. La fila inferior contendrá los totales de columna.

   

3. Haga clic en Guardar y cerrar para guardar la plantilla y cierre el Editor de plantillas.

Usar niveles para construir una tabla con columnas calculadas y totales

Esta es la segunda manera de añadir columnas calculadas, usando niveles. Para informarse sobre los niveles, vea: Informes de Excel con niveles (solo en inglés).

Añadir el nivel

Haga lo siguiente:

1. Cree un nuevo informe o abra uno previo de Excel.
2. Haga clic con el botón derecho en el nodo Niveles y seleccione Añadir niveles.

3. Seleccione un gráfico en la lista. Por ejemplo, añada Top 10 Products. Haga clic en Aceptar.

   

4. Haga clic en el nodo CH184_Level - Top 10 Products y arrástrelo a tres celdas vacías alineadas verticalmente.

   

5. Haga clic en + a la izquierda para ampliar el nodo de nivel CH184_Level - Top 10 Products.
6. De una en una, seleccione las columnas de CH184_Table - Top 10 Products y desmarque la casilla Keep Source Formats.

7. Mantenga pulsada la tecla CTRL y seleccione los campos de nivel que desee agregar a la hoja.

   Por ejemplo: Product Name, Total Sales y Gross Profit. Arrástrelos a la fila vacía de celdas entre las etiquetas de nivel.

   

8. Dé formato a estos campos como prefiera. Por ejemplo, podría dar formato a Total Sales como moneda.

Añadir fórmulas y encabezados

1. Seleccione la primera celda vacía a la derecha de las etiquetas de campo. En este ejemplo, F4.
2. Haga clic en la celda, introduzca = y después haga clic en la celda Total Sales.

3. Escriba -, haga clic en la celda Gross Profit y pulse Intro. Esto calculará los costes.

   Cuando inserte una fórmula entre etiquetas de nivel, Qlik NPrinting la copiará en cada fila de la tabla final.

   Puede ignorar el error #VALUE! en la plantilla. Esto ocurre porque la fórmula de Excel hace referencia a celdas que contienen etiquetas de campo.

4. En la celda G4, escriba =F5/D5. Esto dividirá los costes por ventas.

5. Dé formato a su gusto a las celdas de fórmula utilizando las funciones de formato de Excel.

   Por ejemplo, costes como moneda y costes/ventas como porcentaje.

6. Escriba encabezados para cada columna en una fila ubicada una o dos filas por encima de la fila que contiene la etiqueta de nivel de apertura Top 10 Products.

   Dé formato a estos encabezados como desee.

   

7. Seleccione una celda por debajo de la etiqueta de cierre de nivel </Top 10 Products_Level>, de forma que haya al menos una celda vacía entre medias.

8. En la celda D8, inserte una fórmula SUMA de Excel para crear una fila de totales de columna. El rango de suma deberá incluir:

   1. La dirección de celda de la etiqueta de campo.
   2. La dirección de la celda vacía debajo de ella.

   En este ejemplo, la función podría ser: =SUM(D4:D6).

9. Haga clic y arrastre esta fórmula a E8 y F8.

10. Aplique las herramientas de formato de Excel a los elementos de la fila.

    Por ejemplo: los totales Total Sales y Gross Profit podrían tener el formato de moneda.

    

Generar una vista previa del informe

Haga lo siguiente:

1. Haga clic en Vista previa.

   Excel inicia y muestra su informe.

2. Verá una tabla, con las dos nuevas columnas que contienen sus resultados calculados. La fila inferior contendrá los totales de columna.

   

3. Haga clic en Guardar y cerrar para guardar la plantilla y cierre el Editor de plantillas.