Cómo calcular la media ponderada en tablas dinámicas en Excel

Problema

Queremos calcular la media ponderada dentro de una tabla dinámica en Excel.

En nuestro ejemplo, usando el conjunto de datos mtcars , queremos calcular la media de mpg ponderada por disp.

Solución

Vamos a ver dos alternativas:

Opción 1: Mediante una columna auxiliar.

1. Añadimos una columna auxiliar multiplicando la variable (mpg) por los pesos (disp)

2. Creamos un campo calculado dividiendo la columna auxiliar (mpg*disp) por los pesos (disp)

3. Creamos la tabla dinámica agrupando por el campo deseado

Opción 2: Con una fórmula DAX.

1. Agregamos la tabla original al modelo de datos. No es necesario crear una columna auxiliar.

2. Creamos una nueva medida


weighted_average:=SUMX( Table1,[mpg] * [disp]) / SUM([disp])

3. Creamos la tabla dinámica tomando como origen Desde Modelo de datos, usando la medida recién creada

Entradas relacionadas

• Media aritmética ponderada en R

Referencias

• Conjunto de datos mtcars

Cómo trabajar con ficheros que sobrepasan el límite de filas Excel

Problema

En Excel queremos importar y trabajar con ficheros que exceden el límite de 1.048.576 filas.

Solución

1. En la ficha Datos en el grupo Obtener datos externos hacemos clic en Desde un archivo de texto.

2. En el cuadro de diálogo Importar archivo de texto, buscamos la ubicación del fichero de texto y hacemos clic en Importar.
3. En el Asistente para importar texto, seleccionamos las opciones correspondientes: encabezados, delimitadores, calificadores de texto, y clic en Finalizar.
4. En Importar Datos, nos aseguramos de seleccionar primero Agregar estos datos al Modelo de datos y luego Informe de tabla dinámica.

5. Excel empezará a procesar el fichero mostrando en la parta inferios Cargando modelo de datos. Tardará varios minutos, dependiendo del tamaño del fichero y de la memoria RAM del ordenador.
6. Finalizada la importación podremos generar una tabla dinámica cuyo origen de datos será el fichero de texto importado.

Resultados

Para examinar los datos importados, en la ficha Power Pivot, seleccionamos Administrar en la sección Modelo de datos. Ahí podremos comprobar y examinar los millones de registros importados, crear Medidas (campos calculados) y acceder a otras opciones.

Entradas relacionadas

Importar datos desde la web a Excel

Importar ficheros CSV en Excel mediante VBA

Actualizar origen de datos de Excel: atajo y con VBA

No es posible editar o actualizar los vínculos o el origen de datos

Consulta SQL en Excel mediante Microsoft ActiveX Data Objects (ADO)

Conectar una consulta de unión (union query) de Access desde Excel

Referencias

Conectarse con datos externos

Propiedades de conexión

Anclar y desanclar lista de campos de una tabla dinámica en Excel

Problema

Queremos aprender cómo anclar o desanclar la lista de campos de una tabla dinámica.

Solución

• Desanclar campos

Situamos el cursor sobre Campos de la tabla dinámica, cuando apareza un aspa arrastramos hacia la parte de la hoja en la que deseamos situarlos.


• Resultado

• Anclar campos

Volvemos a situar el cursor sobre Campos de la tabla dinámica, cuando apareza un aspa hacemos doble clic y los campos se anclarán en la posición original.


• Resultado

Crear una hoja de Excel por cada valor de un filtro de una tabla dinámica

Problema

Queremos crear automáticamente una hoja por cada uno de los valores de un filtro de informe de una tabla dinámica. Por ejemplo, por cada año de la siguiente tabla dinámica.

Solución

1. Clic en cualquier celda de la tabla dinámica.
2. Dentro de la pestaña analizar, en el grupo Herramientas de tabla dinámica, clic en Opciones y seleccionamos Mostrar páginas de filtro de informes.

3. En el cuadro de diálogo seleccionamos el campo deseado

4. Se habrá generado una hoja por cada año.

Notas

Es necesario que el campo por el que deseamos crear las tablas esté como filtro de la tabla dinámica.

Referencias

• Tablas dinámicas

Contar elementos únicos con una tabla dinámica en Excel

Problema

Queremos contar los elementos únicos de un campo mediante una tabla dinámica en Excel.

Solución

Antes del Excel 2013 esto no era posible. Con Excel 2013, 2016 y Office 365 es posible emplear la función de resumen Recuento distinto, siempre que usemos el modelo de datos en Excel.

1. Al crear la tabla dinámica, seleccionamos: Agregar estos datos al modelo de datos y hacemos clic en Aceptar.

2. Añadimos el campo que queremos contar en el área valores

3. Clic en la flecha situada a la derecha del nombre del campo y luego selecciona la opción de Configuración de campo de valor.

4. En Resumir campo de valor por seleccionamos Recuento distinto y clic en Aceptar.

Resultado

Se puede apreciar como el campo Recuento distinto de Campo 2 cuenta los elementos únicos. Mientras que Recuento de Campo 2 el número de valores no vacíos, el equivalente a CONTARA.

Referencias

• Crea un modelo de datos en Excel

Entradas relacionadas

• Desactivar IMPORTARDATOSDINAMICOS como referencia a tablas dinámicas
• Desactivar autoajustar anchos de columnas al actualizar tablas dinámicas
• Opciones de cálculo de fórmulas, rendimiento y tratamiento de errores
• Reducir el tamaño de ficheros con tablas dinámicas en Excel
• Tablas dinámicas con origen de datos dinámico

Desactivar autoajustar anchos de columnas al actualizar tablas dinámicas con VBA

Problema

Por defecto al crear una tabla dinámica, Excel actualizará automáticamente el ancho las columnas de la tabla dinámica al tamaño del texto o valor de número más extenso. En lugar de desactivar esta opción manualmente como en la entrada anterior para cada una de las tablas dinámicas, queremos hacerlo automáticamente mediante VBA

Solución

Para desactivar esta opción en todas las tablas dinámicas de la hoja activa.

1. Abrimos el Editor de Microsoft Visual Basic: Alt+F11.
2. Copiamos una de las subrutinas de más abajo en un módulo.
• Tablas en la hoja activa

Sub Desactivar_Autoajustar()
For Each pt In ActiveSheet.PivotTables
    pt.HasAutoFormat = False
Next
End Sub

• Todas las tablas del libro de Excel

Sub Desactivar_Autoajustar2()
Dim n As Integer
Dim i As Integer
Dim pt As PivotTable
n = ActiveWorkbook.Worksheets.Count
    For i = 1 To n
        For Each pt In ActiveWorkbook.Worksheets(i).PivotTables
            pt.HasAutoFormat = False
        Next pt
    Next i    
End Sub

3. Ejecutamos la subrutina, F5.
4. Habremos deseactivado las casillas de Autoajustar anchos de columnas al actualizar en todas las tablas dinámicas de la hoja activa o del libro, dependiendo de la opción elegida.

Referencias

• Opciones de tabla dinámica

Desactivar autoajustar anchos de columnas al actualizar tablas dinámicas

Problema

Por defecto al crear una tabla dinámica Excel actualizará automáticamente el ancho las columnas de la tabla dinámica automáticamente al tamaño del texto o valor de número más extenso.

Solución

Para desactivar esta opción

1. Abrimos el cuadro de diálogo Opciones de tabla dinámica, botón secundario del ratón y clic en Opciones de tabla dinámica.

2. Desactivamos la casilla Autoajustar anchos de columnas al actualizar.

Referencias

• Opciones de tabla dinámica

Desactivar IMPORTARDATOSDINAMICOS como referencia a tablas dinámicas

Problema

Queremos desactivar la función IMPORTARDATOSDINAMICOS como referencia a celdas de tablas dinámicas. Es decir, que cada vez que referenciamos una celda de una tabla dinámica utilice el valor de la celda y no dicha fórmula.

En el ejemplo anterior en lugar de

=IMPORTARDATOSDINAMICOS("Número",$F$3,"Día",1)

debería simplemente aparecer

=G4

Solución

1. Clic en cualquier celda de la tabla dinámica para mostrar las herramientas de tabla dinámica en la cinta de opciones.

2. En la pestaña Analizar clic en Tabla dinámica, clic en opciones y desactivamos la opción Generar GetPivotTable.

Opciones de Excel

También es posible desactivar dicha opción en Opciones de Excel, dentro de la sección Trabajo con fórmulas.

Entradas relacionadas

• Opciones de cálculo de fórmulas, rendimiento y tratamiento de errores
• Reducir el tamaño de ficheros con tablas dinámicas en Excel
• Tablas dinámicas con origen de datos dinámico

Atajo para agrupar y desagrupar datos de tablas dinámicas

Problema

Deseamos agrupar elementos de una tabla dinámica sin necesidad de usar los botones de la cinta de opciones.

Solución

Agrupar

Seleccionamos los elementos deseados y presionamoss: Mayús + Alt + Flecha derecha.

Si solamente seleccionamos un elemento de la tabla dinámica y es número, moneda o fecha aparecerán los siguientes menús desplegables:

Desagrupar

Seleccionamos los elementos deseados y presionamoss: Mayús + Alt + Flecha izquierda.

Estos atajos son especialmente efectivos para elementos contiguos. Mantenemos presionado el botón Mayús avanzando con el cursor para seleccionar los elementos deseados, y después tecleamos Alt más la flecha del cursor correspondiente.

Entradas relacionadas

• Abrir ficheros y aplicaciones con cuadro de diálogo e hipervínculo en Excel
• Abrir ficheros y aplicaciones con un cuadro de diálogo en Access

Agrupar fechas en tablas dinámicas por semanas

Problema

Tenemos una tabla dinámica y deseamos agrupar las fechas por semanas.

Solución

1. Agrupamos las etiquetas de fila. Clic con el botón secundario del ratón en cualquiera de las etiquetas de fila (columna con fechas) y clic en Agrupar.

2. En el desplegable Agrupar, escribimos el inicio, el final y el número de días: 7. Excel detecta automáticamente las fechas inicial y final. En nuestro ejemplo, como deseamos que las semanas comiencen cada lunes, escribimos el primer lunes de 2016: 04/01/2016.

Resultado final

Entradas relacionadas

Agrupar datos en tablas dinámicas con intervalos personalizados