Cómo mostrar datos ocultos en un gráfico de Excel

Imagine que ha creado un hermoso gráfico para su informe de Excel. Decide ocultar los datos de origen porque los usuarios del informe no necesitan verlos. De repente la información del gráfico desaparece. Entonces, esto plantea la pregunta: ¿ cómo mostrar datos ocultos en un gráfico de Excel ?

Respondemos a esta pregunta en este post. Pero vamos más allá. También analizamos cómo podemos utilizar esta técnica para una interactividad del usuario más avanzada.

Tabla de contenido

• Cómo mostrar datos ocultos en un gráfico de Excel
• Utilice datos ocultos para crear gráficos dinámicos
• Código VBA para alternar entre mostrar datos ocultos/visibles
• Alternar celdas ocultas para el gráfico activo
• Alternar celdas ocultas para todos los gráficos en la hoja de trabajo
• Aplicar la configuración oculta del gráfico activo a los gráficos de la misma hoja de trabajo
• Scripts de Office para alternar entre mostrar datos ocultos/visibles
• Alternar celdas ocultas para el gráfico activo
• Alternar celdas ocultas para todos los gráficos en la hoja de trabajo
• Aplicar la configuración oculta del gráfico activo a los gráficos de la misma hoja de trabajo
• 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 del archivo: 0113 Mostrar datos ocultos en Excel chart.zip

Tener acceso

Cómo mostrar datos ocultos en un gráfico de Excel

La opción para mostrar u ocultar datos del gráfico se establece gráfico por gráfico. Es una de esas configuraciones que probablemente hayas visto muchas veces pero nunca te hayas dado cuenta.

Para mostrar datos ocultos en un gráfico de Excel:

1. Haga clic derecho en el gráfico. Haga clic en Seleccionar datos… en el menú.
   
2. En el cuadro de diálogo Seleccionar fuente de datos , haga clic en el botón Celdas ocultas y vacías .
   
3. Se abre el cuadro de diálogo Configuración de celdas ocultas y vacías . Habilite Mostrar datos en filas y columnas ocultas y luego haga clic en Aceptar .
   
4. Haga clic en Aceptar nuevamente para cerrar el cuadro de diálogo de configuración de la fuente de datos.

Eso es todo, eso es todo lo que se necesita. La información del gráfico ahora vuelve a ser visible. Tome nota mental de las configuraciones adicionales en el cuadro de diálogo Configuración de celdas vacías y ocultas ; nunca se sabe cuándo podrían resultar útiles.

Utilice datos ocultos para crear gráficos dinámicos

Inicialmente, la función para mostrar datos ocultos puede parecer molesta. Sin embargo, en realidad crea un nuevo nivel de flexibilidad para mostrar gráficos. Al combinar esta configuración con Autofiltro o una tabla de Excel, podemos especificar qué filas mostrar dentro de un gráfico.

Ejemplo

Los siguientes son los datos de origen de un gráfico:

Esos datos de origen como un gráfico de barras se muestran de la siguiente manera:

Como el gráfico está conectado a la tabla, al filtrar la tabla se muestran solo los elementos seleccionados en el gráfico.

Como ejemplo, he seleccionado Norte en la columna Región de la tabla. Por lo tanto, el gráfico también se actualiza y muestra solo la región Norte.

Esto crea una interactividad flexible estilo tablero al aprovechar el poder de las filas ocultas.

Código VBA para alternar entre mostrar datos ocultos/visibles

Si tenemos muchos gráficos, puede llevar mucho tiempo aplicar esto a cada gráfico individualmente. Los siguientes códigos VBA alternan la configuración de celdas ocultas en varios escenarios.

Para ver otros ejemplos de gráficos de VBA, consulte esta publicación: Guía definitiva: VBA para gráficos de gráficos en Excel (más de 100 ejemplos)

Alternar celdas ocultas para el gráfico activo

El siguiente código alterna la configuración de celdas ocultas solo en el gráfico activo.

Sub ToggleChartDisplayHiddenRows()'Declare and assign variableDim cht As ChartSet cht = ActiveChart'Ignore errors if no chart activeOn Error Resume Next'Toggle hidden data visibilitycht.PlotVisibleOnly = Not cht.PlotVisibleOnlyOn Error GoTo 0End Sub

Alternar celdas ocultas para todos los gráficos en la hoja de trabajo

El siguiente código alterna las celdas ocultas para cada gráfico.

Sub ToggleChartDisplayHiddenRowsAllOnSheet()'Declare and assign variableDim chtObj As ChartObject'Loop through all charts on the worksheetFor Each chtObj In ActiveSheet.ChartObjects    'Toggle hidden data visibility    chtObj.Chart.PlotVisibleOnly = Not chtObj.Chart.PlotVisibleOnlyNextOn Error GoTo 0End Sub

Aplicar la configuración oculta del gráfico activo a los gráficos de la misma hoja de trabajo

El siguiente código cambia la configuración de cada gráfico de la hoja de trabajo para que sea idéntico a la hoja activa.

Sub ToggleChartDisplayHiddenRowsSameAsActive()'Declare and assign variableDim chtObj As ChartObjectDim hiddenRowsSetting As Boolean'Capture the PlotVisibility of active charthiddenRowsSetting = ActiveChart.PlotVisibleOnly'Ignore errors if no chart activeOn Error Resume Next'Loop through all charts on the worksheetFor Each chtObj In ActiveSheet.ChartObjects    'Toggle hidden data visibility    chtObj.Chart.PlotVisibleOnly = hiddenRowsSettingNextOn Error GoTo 0End Sub

Scripts de Office para alternar entre mostrar datos ocultos/visibles

A continuación se muestran 3 scripts de Office que realizan las mismas tareas que los códigos VBA anteriores.

Alternar celdas ocultas para el gráfico activo

El siguiente script alterna la configuración de celdas ocultas en el gráfico activo.

function main(workbook: ExcelScript.Workbook) {  //Declare and assign variablelet cht = workbook.getActiveChart()//Ignore errorstry {  //Toggle hidden data visibility  cht.setPlotVisibleOnly(!cht.getPlotVisibleOnly())} catch (err) {}}

Alternar celdas ocultas para todos los gráficos en la hoja de trabajo

La siguiente secuencia de comandos alterna las celdas ocultas para cada gráfico en la hoja de trabajo activa.

function main(workbook: ExcelScript.Workbook) {//Declare and assign variablelet ws = workbook.getActiveWorksheet()let chtArr = ws.getCharts();//Loop through all charts on the worksheetfor (let i = 0; i  chtArr.length; i++) {    //Ignore errors  try {    //Toggle hidden data visibility    chtArr[i].setPlotVisibleOnly(!chtArr[i].getPlotVisibleOnly())  } catch (err) {  }}}

Aplicar la configuración oculta del gráfico activo a los gráficos de la misma hoja de trabajo

El siguiente script cambia cada gráfico de la hoja de trabajo para que tenga la misma configuración que la hoja activa.

function main(workbook: ExcelScript.Workbook) {//Declare and assign variablelet ws = workbook.getActiveWorksheet()let chtArr = ws.getCharts();//Loop through all charts on the worksheetfor (let i = 0; i  chtArr.length; i++) {    //Ignore errors  try {    //Toggle hidden data visibility    chtArr[i].setPlotVisibleOnly(workbook.getActiveChart().getPlotVisibleOnly())  } catch (err) {  }}}

Conclusión

En esta publicación, hemos visto que es fácil mostrar datos ocultos en un gráfico de Excel. Luego vimos cómo aprovechar el poder de esta función para crear gráficos dinámicos que se actualizan según la selección de filtros. Finalmente, analizamos los métodos de VBA y Office Scripts para automatizar el cambio de la configuración Mostrar datos en filas y columnas ocultas .

---