Cómo crear una tabla de un sumario en Excel

Esta nota viene a colación de la consulta de un lector que tiene una tabla que totaliza los resultados de una encuesta y quiere convertirla en una tabla plana (lista) que le permita usarla como base de datos para analizarlos con una tabla dinámica.

Es decir, por lo general partimos de una tabla (o lista) de datos y los analizamos con una tabla dinámica. Supongamos ahora que queremos hacer exactamente el proceso inverso, partir de la tabla totalizada (no dinámica, estática) y convertirla en la base de datos de origen.

Por ejemplo, supongamos esta tabla que totaliza los resultados de una encuesta

En Excel Clásico (97-2003) hacemos los siguientes pasos:

1 - accionamos el menú Datos-Tablas Dinámicas-Rangos de consolidación múltiples

2 – en el siguiente paso del asistente de tablas dinámicas señalamos "campos de página personalizados"

3 – en el próximo paso señalamos el rango de la tabla totalizada en "rango" del asistente

4 – en el último paso de asistente elegimos la ubicación de la nueva tabla dinámica y apretamos Finalizar

Excel crea una tabla dinámica idéntica al origen a la que agrega los totales por columna y fila

EL truco ahora consiste en arrastrar los campos "Fila" y "Columna" fuera de la tabla dinámica. Ésta queda reducida a una sola celda con el gran total

El último paso consiste en hacer un doble clic sobre el total (53 en nuestro ejemplo).Esto hace que Excel cree una hoja con el detalle de todos los datos

A partir de esta tabla (o lista) podemos crear tablas dinámicas para analizar los datos que previamente recibimos totalizados.

Este video muestra todo el proceso.



En Excel 2007 tenemos que hacer una pequeña manipulación antes de comenzar con el proceso. El asistente de Tablas Dinámicas no está presente en la cinta de comandos. Pero podemos agregarlo a la barra de herramientas de acceso rápido. Abrimos las opciones de Excel en el botón de Office y en Personalizar elegimos la opción "Comandos que no están en la cinta"

Esto nos permite acceder al asistente, tal como en Excel Clásico.

Esta técnica aparece en el libro de John Walkenbach "Favorite Excel 2007 Tips and Tricks"

Evitar la creación automática de hipervínculos en Excel

Curiosamente esta semana una lectora y un compañero de trabajo me dirigen la misma consulta: como evitar que Excel cree hipervínculos automáticamente.

Cada vez que ingresamos datos en una celda de la hoja, Excel trata de interpretar de qué tipo de dato de se trata. Si ingresamos un valor que se como fecha (dos o tres grupos de cifras separados por "-" o "/"), Excel lo convierte automáticamente en fecha.

Lo mismo sucede con textos que parecen ser direcciones de sitios (URL) o de correos electrónicos.

Existen varias maneras de evitar que Excel cree hipervínculos automáticamente.

Si queremos eliminar este comportamiento totalmente tenemos que seguir estos pasos

Excel 2007

1. Apretar el botón de Office y luego el botón de Opciones de Excel
2. En las opciones elegir Revisión y apretar el botón Opciones de autocorrección
3. En la pestaña "Autoformato mientras escribe" quitar la marca de la opción "Rutas de red e Internet…"

En Excel Clásico (97-2003) el proceso es similar: Herramientas-Opciones-Ortografía-Opciones de Autocorrección-Autoformato mientras escribe…

Una vez realizados estos cambios no se crearán hipervínculos en forma automática (hasta que volvamos a cambiar las definiciones).

Existen también opciones manuales para cada celda en particular. Si inmediatamente después de ingresar el valor ha sido convertido en hipervínculo por Excel apretamos Ctrl+Z (deshacer), el hipervínculo desaparece quedando el valor en la celda. También podemos usar "Quitar Hipervínculo" del menú contextual que se abre al apretar el botón derecho del mouse sobre la celda.

Si queremos quitar todos los hipervínculos de una hoja de un golpe, no tendremos más remedio que usar una macro. En un módulo común del editor de Vb ponemos este código, preferentemente en el cuaderno Personal

Sub remove_hyper()
    Cells.Hyperlinks.Delete
End Sub

Ubicar celdas que contienen hipervínculos

Uno de mis lectores, Daniel, me pregunta como ubicar celdas que contienen hipervínculos. Ya hemos visto que Excel nos permite ubicar todo tipo de celdas de acuerdo a sus características usando Ir A-Especial (F5)

Como puede apreciarse, no existe la opción de seleccionar celdas que contengan hipervínculos.
Como ya habrán presentido, podemos hacerlo creando una macro que haga la tarea.
La idea es seleccionar el rango donde pueden encontrarse las celdas con hipervínculos y correr la macro. La macro examina cada una de las celdas del rango y si contiene un hipervínculo la selecciona.

El código es

Sub select_hyperlink()
    Dim rngCell As Range, strCells As String
  
    strCells = ""
  
    For Each rngCell In Selection
        If rngCell.Hyperlinks.Count = 1 Then
            strCells = strCells & rngCell.Address & ","
        End If
    Next rngCell
  
    If Len(strCells) < 2 Then Exit Sub
  
    strCells = Left(strCells, Len(strCells) - 1)
    Range(strCells).Select
  
End Sub

Lo más conveniente es copiar el código en un módulo del Personal.xls (para poder usarlo en todo cuaderno abierto de Excel) y agregarle un atajo de teclado, por ejemplo Ctrl+Mayús+H

Otros usos de Buscar y Reemplazar en Excel

Buscar y Reemplazar (Ctrl+L) es tal vez una de las funcionalidades menos apreciadas por los usuarios de Excel. Esta funcionalidad nos permite ahorrar mucho tiempo y no poco dolores de cabeza usándola con un poco de imaginación.

Reemplazar referencias en fórmulas

Supongamos que en una celda tenemos esta (monstruosa) fórmula

Esta fórmula extrae la palabra que aparece en la celda A2 en la instancia que señalada en la celda B2 (aparece en esta nota sobre como extraer palabras de una frase).

Queremos reemplazar la referencia a la celda A2 por una referencia, digamos, a la celda B1.


Una posibilidad es hacerlo manualmente, lo cual no sólo es tedioso sino que puede conducirnos a cometer errores. Con Buscar y Reemplazar es cuestión de segundos.

Como la fórmula está en una única celda es crítico seleccionar dos celdas, la que contiene la fórmula y, si es posible, una celda contigua en blanco

En la ventanilla Buscar ponemos A2 y en Reemplazar B1, al apretar Reemplazar todos obtenemos

Todas las instancias de A2 han sido reemplazadas y la fórmula da ahora el resultado correcto. Esta técnica también es útil cuando tenemos que modificar fórmulas que están dispersas en la hoja.

Reemplazar formatos

Cuando de formatos se trata, soy fanático del uso de estilos. Pero si no usamos estilos y queremos reemplazar el formato de varias celdas, podemos usar Buscar y Reemplazar.

En este ejemplo, parte de las celdas están formadas con la fuente Arial 11 negrita y fondo celeste y el resto con la fuente Calibri 16 sin fondo de color.

Queremos cambiar el formato todas las celdas con el formato Arial 11 negrita a Calibri 16 sin fondo.
 
Después de seleccionar el rango, abrimos Buscar y Reemplazar y apretamos el botón de opciones. Apretamos formato de celda en Buscar

Elegimos una de las celdas del rango que contiene el formato que queremos reemplazar

Ahora abrimos la opción formato en Reemplazar y también aquí usamos la opción Elegir formato de celda y seleccionamos una celda que contenga el formato elegido

Ahora apretamos Reemplazar todos, y ya está!

Tips Excel – Repetir la última acción

¿Alguien conoce una traducción apropiada para la palabra "tip" en inglés? "Tip" es ese consejo afortunado que nos ahorra tiempo, dinero o ambos al mismo tiempo (después de todo, "time is money").

La pregunta viene a colación de una serie de notas cortas que estaré publicando sobre caminos para incrementar la productividad en el trabajo con Excel.

El tip de hoy es F4 (o Ctrl+Y) – repetir la última acción. Al apretar F4 Excel repite la última acción ejecutada. Esto es muy útil cuando aplicamos formatos, ya sea a celdas o a objetos (como gráficos, por ejemplo).

Veamos algunos ejemplos:

# repetir formato en celda



Esta técnica funciona tanto en Excel Clásico como en Excel 2007

# repetir formato en eje de gráfico (funciona en Excel Clásico)




Esta técnica no funciona en Excel 2007