Cómo crear hojas para cada elemento en el campo de tabla dinámica

Algunas características de Excel se esconden a simple vista. Están ahí, pero a nadie se le ocurre hacer clic en el botón. En esta publicación, cubrimos una de esas características. Estamos viendo cómo crear hojas separadas para cada elemento en un campo de tabla dinámica con la función Mostrar páginas de filtro de informe.

A los usuarios de Excel les encantan las tablas dinámicas porque son un método rápido y sencillo para resumir datos sin fórmulas. A menudo, los datos subyacentes incluyen diferentes centros de costos, gerentes o departamentos. Por lo tanto, es común duplicar la tabla dinámica y cambiar los filtros para que cada elemento tenga una pestaña separada con su propia vista.

¡DETENER! No vayas más lejos con este trabajo manual. Las tablas dinámicas tienen una función secreta oculta llamada Mostrar páginas de filtro de informes, que crea automáticamente esas vistas para nosotros.

Tabla de contenido

• Mostrar páginas de filtro de informes
• Configuración: agregue los campos a la sección Filtro
• Ejecutar: ejecutar las páginas de filtro de informes cortos
• Notas
• Caché dinámico compartido
• Los cambios no se muestran en todas las tablas dinámicas
• ¿Otros objetos no se copian?
• Nombres de hojas
• ¿Qué pasa con las tablas dinámicas del modelo de datos?
• ¿Utiliza varios campos de filtro?
• Copiar todos los objetos de la hoja de trabajo
• Código VBA
• Guión de oficina
• Conclusión

Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.

Nombre de archivo: 0142 Crear hojas a partir de elementos de tabla dinámica.zip

Tener acceso

Mostrar páginas de filtro de informes

La función Mostrar páginas de filtro de informes se encuentra en Opciones de análisis de tabla dinámica Mostrar páginas de filtro de informes...

Para cada elemento único en el campo Pivote:

• Se crea una nueva hoja de trabajo que contiene la tabla dinámica.
• La hoja de trabajo recibe un nombre según el valor del campo.
• La tabla dinámica se filtra según el valor del campo.

No importa cuántos artículos tengamos; creará un informe para cada uno de ellos. Si tenemos muchos, es posible que tarde bastante en ejecutarse y probablemente no brinde la mejor experiencia de usuario. Pero puede, si es necesario.

Profundicemos un poco más en esta función para comprender cómo funciona.

Configuración: agregue los campos a la sección Filtro

Supongo que ya ha creado una tabla dinámica. Identifique el campo para el que desea crear hojas de trabajo individuales. Agregue ese campo a la sección Filtros .

Como se muestra arriba, hemos agregado el campo Región a la sección Filtros.

Nota: Esta técnica solo funciona con elementos de la sección Filtros.

Ejecutar: ejecutar las páginas de filtro de informes cortos

Antes de hacer cualquier otra cosa... asegúrese de que la tabla dinámica esté exactamente como la desea. El estilo, el diseño y los filtros existentes actuales se replican para cada hoja de trabajo creada.

1. Seleccione una celda en la tabla dinámica para revelar la cinta Analizar de tabla dinámica.
2. Haga clic en Opciones de análisis de tabla dinámica Mostrar páginas de filtro de informes...
   
3. Aparece el cuadro de diálogo Mostrar páginas de filtro de informe . Seleccione el campo para el que crear los informes.
4. Haga clic en Aceptar .
   

¡AUGE! Las hojas de trabajo se crean con nombres únicos y las tablas dinámicas están prefiltradas para cada elemento.

Notas

Hay algunas cosas que debe tener en cuenta al utilizar esta función.

Caché dinámico compartido

Todas las tablas dinámicas comparten el mismo caché dinámico. Por lo tanto, al actualizar los datos de una tabla dinámica, se actualizan todas las demás tablas dinámicas.

Los cambios no se muestran en todas las tablas dinámicas

Si bien los datos se comparten entre tablas dinámicas, los demás cambios no. Cada tabla dinámica es un objeto independiente y no comparte formato ni diseños. Por eso es fundamental hacerlo bien desde el principio.

Si desea cambiar muchas tablas dinámicas, es más fácil eliminar todas las hojas adicionales y volver a generarlas mediante el mismo proceso.

¿Otros objetos no se copian?

La función crea una nueva hoja y le agrega una tabla dinámica. Por lo tanto, ningún otro objeto de la hoja de trabajo maestra no se copia.

Aunque pueda considerarlos parte de la tabla dinámica, las segmentaciones de datos y los gráficos dinámicos no se copian.

Nombres de hojas

Las hojas se crean en orden alfabético.

Si ya existe una hoja de trabajo con ese nombre, la nueva hoja de trabajo se creará con un número (por ejemplo, Hoja(1)).

¿Qué pasa con las tablas dinámicas del modelo de datos?

Mostrar páginas de filtro de informes funciona solo para tablas dinámicas estándar. Cualquier tabla dinámica creada con Power Pivot o un modelo de datos conectado no tiene esta característica.

¿Utiliza varios campos de filtro?

Antes de que preguntes… no, esto no funciona con múltiples campos de filtro. El cuadro de diálogo Mostrar páginas de filtro de informe solo permite la selección de un único campo.

Buen intento aunque

Copiar todos los objetos de la hoja de trabajo

Anteriormente notamos que Mostrar páginas de filtro de informe no copia todos los objetos de la hoja. Entonces, si queremos copiarlos, debemos recurrir a una macro VBA o un script de Office.

Código VBA

Copie lo siguiente en un módulo de código estándar. Seleccione la hoja con la tabla dinámica y luego ejecute el código.

Un cuadro de entrada nos pide que introduzcamos el nombre del campo a utilizar.

Los comentarios del código proporcionan detalles adicionales sobre cada sección.

Sub pivotTableWsCopy()Dim ws As WorksheetDim newWs As WorksheetDim pvt As PivotTableDim newPvt As PivotTableDim pvtField As PivotFieldDim newPvtField As PivotFieldDim pvtFieldName As StringDim pvtItem As PivotItem'On Error Exit Sub, not a PT pageOn Error Resume NextSet ws = ActiveSheetSet pvt = ws.PivotTables(1)If Err.Number  0 Then Exit Sub'Capture the name of the Pivot FieldpvtFieldName = Application.InputBox(Prompt:="Apply based on which field?", Title:="PivotTable", Type:=2)'Escape if user clicks CancelIf pvtFieldName = "False" Then Exit Sub'Set the Pivot FieldOn Error Resume NextSet pvtField = pvt.PivotFields(pvtFieldName)If Err.Number  0 Then    MsgBox pvtFieldName  "does not exist in the PivotTable"    Exit SubEnd If'Turn off message when deleting sheetApplication.DisplayAlerts = False'Delete any existing PT sheetsFor Each pvtItem In pvtField.PivotItems    On Error Resume Next    Sheets(pvtItem.Name).Delete    On Error GoTo 0        'Copy the worksheet    ws.Copy After:=ActiveWorkbook.Sheets(Sheets.Count)    Set newWs = ActiveWorkbook.Sheets(Sheets.Count)    newWs.Name = pvtItem.Name        'Change the PivotItem    Set newPvt = newWs.PivotTables(1)    Set newPvtField = newPvt.PivotFields(pvtFieldName)    newPvtField.ClearAllFilters    newPvtField.CurrentPage = pvtItem.Name    Next'Turn on alertsApplication.DisplayAlerts = True'Select the worksheetws.ActivateEnd Sub

Guión de oficina

También contamos con una solución Office Scripts.

Copie el siguiente código en el editor de scripts. Seleccione la hoja con la tabla dinámica y luego ejecute el script.

La ejecución es similar al método VBA, pero como los scripts de Office no tienen una manera fácil de mostrar un formulario para la entrada del usuario, utiliza el primer campo en la sección Filtros.

Los comentarios proporcionan detalles adicionales sobre cada sección.

function main(workbook: ExcelScript.Workbook) {  let ws = workbook.getActiveWorksheet();  let newWs: ExcelScript.Worksheet;  let pvt: ExcelScript.PivotTable;  let newPvt: ExcelScript.PivotTable;  let pvtItemName: string;  //On Error return, not a PT page  try {      pvt = ws.getPivotTables()[0];  } catch {      return;  }  //Get the Field Name  let pvtField = pvt.getFilterHierarchies()[0].getFields()[0];  let pvtFieldName = pvtField.getName();  //Loop through all Items in Field  for (let i = 0; i  pvtField.getItems().length; i++) {    //Get the name of the Item    pvtItemName = pvtField.getItems()[i].getName();    //Delete worksheet if exists    try {      newWs = workbook.getWorksheet(pvtItemName);        newWs.delete();    } catch {};    //Create the new worksheet    newWs = ws.copy(ExcelScript.WorksheetPositionType.after, ws);    newWs.setPosition(workbook.getWorksheets().length - 1);    newWs.setName(pvtItemName);    //Change PivotTable Item    newPvt = newWs.getPivotTables()[0];    newPvt.getFilterHierarchy(pvtFieldName).      getPivotField(pvtFieldName).applyFilter({        manualFilter: {          selectedItems: [pvtItemName]        }      });  }}

Conclusión

En esta publicación, hemos visto cómo crear hojas separadas para cada elemento en un campo de tabla dinámica. Todo lo que se necesita son unos pocos clics y la función Mostrar páginas de filtro de informes hace todo el trabajo por nosotros.

Sin embargo, si desea copiar otros objetos de la hoja de trabajo, VBA y Office Scripts pueden adaptarse mejor a sus necesidades.

Artículos Relacionados:

• Cómo crear una tabla dinámica a partir de varias tablas (manera fácil)
• Borrar elementos antiguos de una lista de filtros de tabla dinámica
• Excel VBA para tablas dinámicas

---