top of page
Foto del escritorJaime Franco Jimenez

Tablas y rangos

Vamos a ver la diferencia entre rango y una tabla, vamos a ver sus ventajas y desventajas.


Podemos usar tablas para facilitar la administración y el análisis de un grupo de datos.


En Excel, cuando tenemos un conjunto de datos, recibe dos nombres, o, puede ser simplemente un rango, que sería el formato más básico, o, puede ser una tabla, vamos a ver cómo es que se convierte a tabla.


Si tenemos un rango, ¿Cuál es la ventaja se seleccionar un rango?, pues que su manipulación es libre, y, podríamos, por ejemplo, cambiar el formato sin ningún problema, pero una desventaja, seria, por ejemplo, cuando trabajamos con una tabla dinámica, veamos un ejemplo, tenemos el siguiente modelo en formato de rango.









Nos colocamos en cualquier parte de los datos, vamos a la pestaña de Insertar, y, hacemos clic en tabla dinámica.







Se abre la ventana tabla dinamica desde la tabla o el rango, dejamos marcado nueva hoja de cálculo.











Aceptamos.


Una tabla dinamica, la veremos un poco más adelante, pero es una tabla resumen, en el modelo en el cual estamos trabajando, tiene los siguientes encabezados:




Pues, podemos resumir por fecha, provincia, centro, o, producto, y, estos encabezados lo podemos resumir por cantidad, precio, o, total.


Cuando hemos aceptado, vemos en la derecha una ventana llamada campos de tabla dinamica, donde vemos los encabezados de nuestro modelo.















En la parte inferior, vemos:















Que es donde podemos arrastrar los encabezados, pues, a filas arrastramos con el ratón el encabezado centro hasta filas, y, arrastramos total hasta valores.















Vemos un resumen de las ventas de cada centro.











Volvemos al modelo, vamos a la última fila, y, añadimos algunas filas más.













Volvemos a la tabla dinamica, en una celda dentro de la tabla dinamica, hacemos clic con botón alternativo de ratón, y, seleccionamos actualizar.












Vemos que no ha pasado nada, ¿Por qué?, bien, esta tabla sale de un rango, al salir de un rango debemos de especificar una celda de inicio, y, una celda de fin, quiere decir, que, aunque hemos añadido datos nuevos, no se han vistos reflejados.


Ahora, vamos a convertir el modelo en tabla, para ello, con una celda dentro del modelo, vamos a la pestaña insertar, y, hacemos clic en tabla, también, podemos usar el atajo de teclado CTRL más T.








Se abre la ventana crear tabla, donde ha quedado seleccionado todo el rango, y, dejamos marcado la tabla tiene encabezados.








Aceptamos, vemos que se le aplica un formato al modelo, y, se activan los filtros.








Cuando tenemos una tabla, estamos en una celda dentro de la tabla, se habilitan la pestaña diseño de tabla.


Hacemos clic en la pestaña diseño de tabla, dentro del grupo propiedades, podemos ver el nombre de la tabla, el cual podemos cambiar.







Una tabla trabaja por columnas, el encabezado fecha es desde la celda A2 hasta la última celda del modelo, haya o no haya datos, igual para el resto de los encabezados, por eso, cuando introducimos datos nuevos, se actualizan de forma automática.


Volvemos a insertar una tabla dinamica, e, igual que antes, resumimos por centro.












Volvemos al modelo, vamos a la última fila, y, añadimos filas nuevas.















Volvemos a la tabla dinamica, y, actualizamos.


Vemos como ha sido actualizada.












Podemos crear una tabla desde la pestaña de inicio, dentro del grupo estilos, desplegamos dar formato como tabla, y, solo tenemos que elegir el formato que más nos guste.














Pues es una de las ventajas de trabajar con una tabla.


Conozcamos más sobre tablas, vamos a la pestaña diseño de tabla, y, vamos al grupo opciones de estilo de tabla.







Una tabla puede incluir los elementos siguientes:


· Fila de encabezado: De manera predeterminada, una tabla tiene una fila de encabezado. Cada columna de la tabla tiene habilitado el filtrado en la fila de encabezado para que pueda filtrar u ordenar los datos de la tabla rápidamente, si desmarcamos la casilla fila de encabezado, vemos como los encabezados se ocultan, no se elimina.







· Filas con bandas: que es el sombreado alternativo o la agrupación en bandas que nos ayudan a distinguir mejor los datos, si desmarcamos la casilla filas con bandas, vemos como el sombreado de las filas desaparecen.









· Columnas con bandas: que es el sombreado alternativo o la agrupación en bandas de las columnas, por defecto, esta desactivada, si marcamos la casilla columnas con bandas, vemos como el sombreado de las columnas aparecen.









· Columnas calculadas: Al escribir una fórmula en una celda de una columna de una tabla, podemos crear una columna calculada en la que esa fórmula se aplica de forma instantánea a todas las demás celdas de la columna de la tabla.


· Fila de total: Una vez que agregamos una fila de totales a una tabla, Excel nos proporcionará una lista desplegable para seleccionar funciones como suma, promedio, etc. Al seleccionar una de estas opciones, la tabla las convertirá automáticamente a una función de subtotales, que omitirá las filas que se han ocultado de forma predeterminada con un filtro, de forma predeterminada, esta desactivada, pues, vamos a marcarla.


Si vamos al final del modelo, vemos que se ha añadido una nueva fila llamada Total, donde vemos el total de la columna total.








Vamos a colocarnos en la celda donde tenemos el total, vemos que aparece una punta de flecha mirando hacia abajo, la desplegamos y vemos las opciones que tenemos, como promedio, recuento, suma, entre otros…










Vemos que esta seleccionada suma, si hacemos doble clic en la celda de total, vemos que, en vez de aparecer suma, aparece subtotales, quiere decir, que solo suma las celdas visibles.








Este desplegable está habilitada para cada columna.


También, tenemos primera columna, y, última columna, de forma predeterminada, están desactivadas, si la marcamos, pone la primera fila en negrita, y, la primera columna en negrita.







Lo siguiente que vamos a ver es el controlador de tamaño, vamos a la última fila de la tabla, en la esquina inferior derecha vemos el controlador de tamaño, que nos permite arrastrar la tabla, y, podemos agregar o quitar fácilmente filas y columnas de tabla.











Otra forma de hacerlo es desde diseño de tabla, dentro del grupo propiedades, tenemos cambiar el tamaño de la tabla.










Vamos a hacer clic en cambiar tamaño de la tabla.


Se abre la ventana cambiar tamaño de la tabla, donde debemos de seleccionar el nuevo modelo.










Para mí, personalmente, en más fácil el uso del controlador de tamaño.


Después de crear una tabla de Excel, es posible que solo deseemos el formato de la tabla sin la funcionalidad de la tabla, para dejar de trabajar con los datos en una tabla sin perder ningún formato de estilo de tabla que hayamos aplicado, podemos convertir la tabla en un rango normal de datos en la hoja de cálculo, para ello, hacemos clic en cualquier lugar de la tabla y, a continuación, vamos a la pestaña diseño de tabla, dentro grupo herramientas, hacemos clic en convertir en rango.







Después de volver a convertir la tabla en un rango, las funciones de tabla dejan de estar disponibles. Por ejemplo, los encabezados de fila ya no incluyen las flechas de ordenar y filtrar y las referencias estructuradas.


Pues, vamos a hablar de las referencias estructuradas.


Cuando creamos una tabla de Excel, Excel asigna un nombre a la tabla y a cada encabezado de columna de la tabla. Cuando se agregamos fórmulas, estos nombres pueden aparecer automáticamente a medida que se escribe la fórmula y se seleccionan las referencias de cada celda en la tabla en lugar de especificarlas manualmente.


Una referencia estructurada está formada por:


· Nombre de tabla: =Tabla2 es un nombre de tabla predeterminado. Hace referencia a los datos de la tabla, sin encabezado ni filas de totales.


· Especificador de columna: =Tabla2[@Marca] es un especificador de columna que usa el nombre de la columna que representan. Hacen referencia a los datos de la columna, sin ningún encabezado de columna ni fila de total. Siempre se encierra el o los especificadores entre corchetes.


Reglas para los nombres de tabla:


Usar caracteres válidos. Siempre empieza un nombre con una letra, un carácter de subrayado (_) o una barra diagonal inversa (\). Podemos usar letras, números, puntos y caracteres de subrayado para el resto del nombre. No puede usar "C", "c", "R" o "r" para el nombre porque ya están designados como un método abreviado para seleccionar la columna o fila de la celda activa cuando las introduce en el cuadro nombre o ir a .


No use referencias de celda. Los nombres no pueden coincidir con una referencia de celda, como Z 100 o F1C1.


No usar un espacio para separar palabras. No se pueden usar espacios en el nombre. Puede usar el carácter de subrayado (_) y el punto (.) como separadores de palabras.

No utilizar más de 255 caracteres un nombre de tabla puede tener hasta 255 caracteres.

Usar nombres de tabla única. Nombres duplicados no están permitidos. Excel no distingue entre mayúsculas y minúsculas en los nombres si introduces "Ventas" pero si ya tiene otro nombre llamado "VENTAS" en el mismo libro, se le pedirá que elija un nombre único.


Usar un identificador de objeto. Si piensa tener una combinación de tablas, tablas dinámicas y gráficos, es una buena idea prefijar sus nombres con el tipo de objeto. Por ejemplo: tbl_Ventas hace referencia a una tabla de ventas, pt_Ventas para una tabla dinámica de ventas y chrt_Ventas para un gráfico de ventas.


Por ejemplo, en la celda H2, vamos a calcular de nuevo el total, debemos de multiplicar cantidad por precio, en la barra de fórmulas, hacemos clic en la celda E2, que es cantidad, vemos que aparece [@Cantidad], quiere decir que hace referencia a cada valor de la columna cantidad.






Ponemos el símbolo de asterisco, y, hacemos clic en la celda F2.






Cuando hagamos clic en Enter, este cálculo se aplicará a cada celda.


Si en vez de hacer clic en la celda, podemos escribir directamente el nombre de la celda, y, ya no aparecerá la sintaxis anterior, pero el resultado es el mismo.









Si queremos seleccionar una columna, nos colocamos con el ratón encima del encabezado hasta que el icono del ratón se convierta en una flecha con punta hacia debajo de color negro.


Si queremos seleccionar la tabla completa, vamos al primer encabezado, nos movemos hacia la parte izquierda superior, hasta que el icono del ratón se convierta en una flecha con punta hacia abajo inclinada.


Pues, dicha combinación de nombres de tabla y columna se denomina referencia estructurada, y Excel las usa porque los nombres se ajustan siempre que agregue o quite datos de la tabla.


Las referencias estructuradas también aparecen cuando crea una fórmula fuera de una tabla de Excel que hace referencia a los datos de la tabla.


También, podemos trabajar desde una celda usando el nombre de tabla y de columna, esta tabla se llama Tabla2, vamos a calcular, de nuevo, el total, en la celda J2, escribimos Ta, vemos una ventana con todas las funciones que contienen Ta, y, las tablas que comienzan por Ta.










Nos movemos con el ratón, seleccionamos nuestra tabla, y, tabulamos.


Abrimos un corchete, y, aparecen los encabezados disponibles.













Nos movemos con el ratón hasta cantidad, tabulamos y cerramos paréntesis.







Ponemos el símbolo de asterisco, abrimos otro corchete, en este caso, seleccionamos precio, cerramos corchete.







Pulsamos Enter, y, tenemos el mismo resultado.


Si queremos realizar una copia de la tabla, nos colocamos en la celda donde vamos a realizar la copia, ponemos el signo igual, en nombre de la tabla, abrimos un corchete, y, en la ventana que se abre, seleccionamos #Todo.














Tenemos una copia de la tabla incluido los encabezados.


Si queremos una copia de la tabla, sin incluir los encabezados, seleccionamos #Datos.














Si queremos hacer una copia de columna cantidad, incluyendo el encabezado, ponemos la siguiente sintaxis:


=Tabla2[[#Todo];[Centro]]


Si queremos traernos solo los encabezados, seleccionamos #Encabezados.















Si tenemos activada la casilla de totales, y, queremos traernos los totales, ponemos #Totales.















Cuando usamos la siguiente sintaxis, donde usamos la arroba (@).


=Tabla2[@Cantidad]


Hace referencia al primer valor de la columna cantidad, pero va a trabajar con cada valor de la columna.


Cuando usamos la siguiente sintaxis:


=Tabla2[Cantidad]


Hace referencia a cada valor de la columna cantidad.


En resumen, Tabla2[@Cantidad], devuelve la cantidad fila a fila de la columna Cantidad, el signo de arroba (@) hace referencia a valores individuales de una columna.


Si tenemos activada la casilla de totales, y, tenemos el total de la columna total, si usamos la siguiente expresión:


=Tabla2[[#Totales];[Total]]


Nos devuelve el total de la columna total.


De manera predeterminada, cuando se crea una fórmula, al hacer clic en un rango de celdas dentro de una tabla, se seleccionan las celdas y se escribe automáticamente una referencia estructurada en lugar del rango de celdas en la fórmula. Este comportamiento de semi elección hace que sea mucho más fácil introducir una referencia estructurada.


Podemos activar o desactivar este comportamiento activando o desactivando la casilla de verificación usar nombres de tabla en fórmulas, en la pestaña archivo, seleccionado Opciones, abre la ventana de opciones de Excel, seleccionamos fórmulas en la ventana de la izquierda, y, entramos en el grupo trabajo con fórmulas.









Vamos a ver una opción, que solo estaba disponible para tablas dinámicas, que es segmentación de datos, la cual encontramos en la pestaña diseño de tabla, dentro del grupo herramientas, tenemos segmentación de datos, esta opción nos permite, a través de botones, realizar filtros de una forma fácil.







Pues vamos a hacer clic en segmentación de datos.


Se abre la ventana insertar segmentación de datos, con los encabezados del modelo.












Vamos a seleccionar, por ejemplo, producto, y, aceptamos, vemos que aparece una ventana con los productos.













Si seleccionamos un producto, vemos como el modelo queda resumido por dicho producto, además, solo el producto queda con el formato, el resto de los productos aparecen con sin relleno.













Si queremos eliminar el filtro, hacemos clic en el siguiente icono:











Para seleccionar más de un producto, seleccionamos el primer producto, dejamos pulsada la tecla CTRL, y, seleccionamos el siguiente producto.


Si queremos seleccionar de forma secuencial una serie de productos, seleccionamos el primer producto, dejamos pulsada la tecla MAYUSC, seleccionamos el ultimo producto, y, queda seleccionado el primer producto, el ultimo producto, y, los productos que haya entre medio.


Por supuesto, podemos redimensionar la ventana.


Podemos agregar más de una segmentación, con una celda dentro de la tabla, volvemos a hacer clic en segmentación de datos, y, vamos a elegir centro.











Ahora, podemos resumir por centro, y, producto.


Cuando tenemos una segmentación seleccionada, se habilita la pestaña de segmentación, donde podemos, por ejemplo, cambiar el color.










Podemos decidir el número de columnas que debe de tener la segmentación, he puesto tres columnas.








Este es el resultado:














Los siguientes datos afectan a los botones.







Y los siguientes a la segmentación.







Si hacemos clic en el siguiente icono:







Se abre a la derecha la ventana segmentación de formato, donde podemos realizar diferentes cambios.

















Si hacemos clic en una de las segmentaciones, y, seleccionamos configuración de segmentación de datos, podemos cambia el nombre, el nombre es el que se usa en las fórmulas, o, el título.














A veces, cuando tenemos más de una segmentación, y, filtramos en una de ellas, puede ser que en la segunda segmentación, en algunos datos no haya coincidencia, y, dichos datos aparecen en un color más claro, pero si no queremos que aparezcan marcamos la casilla ocultar los elementos que no contienen datos.














Para seleccionar más de un elemento, podemos hacer uso de selección múltiple, y, seleccionar los elementos a filtrar.

















Para eliminarla, la seleccionamos y hacemos clic en suprimir.


Si queremos cambiar el formato, vamos a la pestaña diseño de tabla, y, cambiamos el formato en el grupo estilos de tabla.







Si queremos aplicar nuestro propio formato, desplegamos estilos de tabla, y, seleccionamos nuevo estilo de tabla.












Se abre la ventana nuevo estilo de tabla, donde decidimos a que elemento le vamos a dar formato.












Podemos dar formato a toda la tabla, pero le voy a dar formato a la fila de encabezados.












Hacemos clic en formato, le voy a dar un relleno verde oscuro, y, un color de letra blanco, en la parte de la derecha, podemos ver una vista previa.











Aceptamos, este formato que hemos creado no se aplica de forma automática, debemos de aplicarlo, para ello, desplegamos estilos de tabla, los estilos que creemos aparecen los primeros, en un grupo llamado personalizado, pues, simplemente lo seleccionamos.


















Si queremos modificarlo, duplicarlo, o, eliminarlo, volvemos a estilos de tabla, y, hacemos clic con botón alternativo de ratón sobre el estilo creado.












Celda combinada en una tabla


Ahora, tenemos el siguiente modelo, donde tenemos nombre y apellido, pero, vemos que tenemos Yolanda Amador, en una celda combinada.












Queremos convertir el modelo en tabla, pero, cuando creamos una tabla, la celda combinada se separará, y, queremos que siga combinada.


Vamos a convertirla en tabla, con una celda dentro del modelo, pulsamos CTRL más T.


Se abre la ventana de crear tabla, marcamos, la tabla tiene encabezados, y, aceptamos.








Vemos que donde esta Yolanda Amador, ya no está combinada.












Como he dicho anteriormente, necesitamos combinarla, y, centrarla, pues para ello, seleccionamos las dos celdas donde se encuentra Yolanda Amador.












Pulsamos CTRL más 1, para ir a formato de celdas.


Vamos a la pestaña de alineación.






Desplegamos horizontal, y, seleccionamos centrar en la selección.









Aceptamos, y, vemos como el nombre y el apellido han quedado centrado.












Aunque, siguen siendo celdas independientes, función igual que combinar y centrar, la diferencia que combinar y centrar, todas las celdas las convierte en una, y, con centrar en la selección, las celdas siguen siendo independientes.


¿sabían que los encabezados de una tabla, es una lista personalizada?


Tenemos los siguientes datos, que son nombres de productos, y, como encabezado, tenemos producto1.












Con una celda, dentro del modelo, pulso CTRL más T.


Se abre la ventana de crear tabla, marcamos, la tabla tiene encabezados, y, aceptamos.














En la celda D2, voy a añadir un producto, y, vemos como de forma automática, ha puesto el encabezado producto2.







Funcionaria, igual, para meses, y, días de la semana.



Miguel Angel Franco

138 visualizaciones0 comentarios

Entradas recientes

Ver todo

Comments


bottom of page