Microsoft Excel (Tablas)

 

PREPARACIÓN DE LOS DATOS

Antes de crear una tabla en Excel debemos tomar en cuenta algunas recomendaciones:

Los datos deben estar organizados en filas y columnas.

• La primera fila debe utilizarse para los encabezados de columna es decir, una descripción muy corta de los datos contenidos en la columna.
• Cada columna debe tener el mismo tipo de dato: texto, número, fecha, moneda, etc.
• Cada fila tendrá los detalles de un solo registro, por ejemplo: un empleado, un artículo, una orden de compra.
• Los datos no deben contener filas o columnas en blanco.

Una vez que hemos revisado que nuestros datos cumplen con estas condiciones, estaremos listos para crear una tabla en Excel. Por ejemplo, la siguiente imagen muestra los datos de ejemplo antes de iniciar con la creación de la tabla.

                   

• CREAR UNA TABLA EN EXCEL

Parar crear una tabla en Excel debes seleccionar cualquiera de las celdas del rango de datos y pulsar el comando Insertar > Tabla y de inmediato Excel identificará el rango que contiene los datos:

 

Si por alguna razón, el rango reconocido por Excel no concuerda con tu rango de datos, es el momento adecuado para hacer la modificación a dicha referencia.

 Si Excel detecta una fila de encabezados en los datos, se mostrará seleccionada la opción “La tabla tiene encabezados”. De igual manera, deberás confirmar que tus datos tienen una fila de encabezados. Al pulsar el botón Aceptar, habrás creado una tabla en Excel.

Excel aplicará un estilo de tabla predeterminado, que para este ejemplo es ese color azul de fondo  que se ha colocado de manera alterna en cada una de las filas. Además, los encabezados tienen un estilo diferente e incluyen los botones de flecha que nos permitirán ordenar los datos o aplicar un filtro.

 Las tablas en Excel siempre requieren que la primera fila de los datos sean los encabezados de columna y en caso de que no tengas encabezados, se insertarán nombres genéricos como Columna1, Columna2, Columna3, etc. Por ejemplo, la siguiente imagen muestra una tabla que ha sido creada con los mismos datos de ejemplo pero que no tenían la fila de encabezados.

 

Al detectar la falta de una fila de encabezados, Excel insertará una fila con nombres genéricos de manera que haya espacio para colocar las flechas para los filtros de la tabla.

• DAR FORMATO COMO TABLA:

Para dar formato a una tabla haz clic en cualquier celda dentro del rango de celdas que contienen los datos y posteriormente haz clic sobre el comando Dar formato como tabla que se encuentra dentro del grupo Estilos de la ficha Inicio y se mostrará la galería de formatos disponibles:

Haz clic sobre la imagen de miniatura del estilo que deseas y se mostrará el cuadro de diálogo Dar formato como tabla

 

                                                        

 

• LA HERRAMIENTA TABLA:

Las tablas en Excel son un objeto especial en las hojas de cálculo ya que nos ayudarán a organizar y visualizar nuestros datos de manera amigable. Además, una vez creada la tabla de Excel, podremos ordenar y filtrar los datos fácilmente.

Muchos usuarios de Excel consideran una tabla a cualquier rango que tenga datos estructurados y cuyas celdas tengan un estilo y vista diferente.

 

Sin embargo, una tabla de Excel no es solamente un rango de celdas con datos bien organizados. Una tabla en Excel es un objeto especial que ha sido creado con alguno de los comandos disponibles, como el comando Tabla que está ubicado en la ficha Insertar, dentro del grupo Tablas.

 

Este comando da un formato especial a un rango de celdas y además inserta automáticamente los filtros sobre cada una de las columnas. Este comando es el que convierte un rango normal de celdas en una tabla.

• MODIFICAR LOS DATOS DE LA TABLA:

Para modificar o introducir nuevos datos en la tabla, podemos teclear directamente los nuevos valores en ella o bien podemos utilizar un formulario de datos. Esta segunda opción viene muy bien sobre todo si la tabla es muy grande.

Cambiarle el nombre a una tabla: 

Fórmulas > Administrador de nombres,al hacer clic se mostrará un cuadro de diálogo con la lista de rangos nombrados de nuestro libro así como las tablas existentes.

El cuadro de diálogo Administrador de nombres nos dará mucha más información sobre las tablas de nuestro libro ya que podremos ver su nombre así como el rango de datos al cual hace referencia cada tabla. Si seleccionamos alguna de ellas y pulsamos el botón Editar podremos cambiar el nombre de la tabla para colocar alguno de nuestra preferencia.

• MODIFICAR LA ESTRUCTURA DE LA TABLA:

Una de las tablas más utilizadas en Excel son las tablas dinámicas. Las tablas dinámicas en Excel son un tipo de tabla que nos permiten decidir con facilidad los campos que aparecerán como columnas, como filas y como valores de la tabla y podemos hacer modificaciones a dicha definición en el momento que lo deseemos.

 Las conocemos como tablas dinámicas porque tú decides “dinámicamente” la estructura de la tabla, es decir sus columnas, filas y valores.

 Las tablas dinámicas en Excel también son conocidas como tablas pivote debido a su nombre en inglés: Pivot tables. Son una gran herramienta que nos ayuda a realizar un análisis profundo de nuestros datos ya que podemos filtrar, ordenar y agrupar la información de la tabla dinámica de acuerdo a nuestras necesidades.

¿Para que sirven las tablas dinámicas?

Para comparar grandes cantidades de datos e intercambiar fácilmente columnas por filas dentro de la misma tabla y realizar filtros que resulten en reportes que de otra manera necesitaríamos un tiempo considerable para construirlos. Considera el siguiente ejemplo.

-MODIFICAR CAMPOS DE UNA TABLA DINÁMICA:

Las tablas dinámicas son muy fáciles de manipular y de modificar. Excel permite cambiar los campos de la tabla de una manera muy sencilla y reestructurar la información con tan solo arrastrar los nuevos campos.

 Lista de campos de una tabla dinámica:

Las modificaciones las haremos en la Lista de campos de la tabla dinámica que se encuentra en la parte derecha de la pantalla. Esta lista se mostrará con tan solo hacer clic en cualquier celda de la tabla dinámica.

Si por alguna razón esta lista de campos no aparece en tu libro de Excel, debes activarla con el comando Lista de campo que se encuentra dentro del grupo Mostrar de la ficha Opciones.

-QUITAR UN CAMPO DE UNA TABLA DINÁMICA:

Para remover un campo arrástralo fuera del área donde se encuentra y cuando el puntero del ratón se convierta en una “X” suelta el botón del ratón para eliminar el campo. Otra manera de eliminar el campo es haciendo clic sobre él y seleccionar la opción Quitar campo.

-MOVER UN CAMPO DE UNA TABLA DINÁMICA:

Para mover un campo ya existente hacia una nueva ubicación solamente arrástralo de un área a otra o de igual manera selecciona cualquiera de las opciones de menú que permiten mover el campo a cualquiera de las áreas disponibles: Mover al filtro de informe, Mover a rótulos de fila, Mover a rótulos de columna o Mover a valores. Con este método puedes cambiar fácilmente las columnas por filas y viceversa.

-NUEVO CAMPO EN UNA TABLA DINÁMICA:

Finalmente para agregar un nuevo campo puedes arrastrarlo desde la lista de campos hacia el área deseada. También puedes marcar la caja de selección del campo lo cual hará que sea agregado a alguna de las áreas predeterminadas.

Las tablas dinámicas son un elemento de análisis de información de Excel muy poderoso y esa fortaleza proviene de la facilidad que provee para manipular la información de acuerdo a tus necesidades.

• ESTILOS DE TABLA:

Un estilo de tabla es una colección de atributos de formato de tabla, como los bordes de tabla y los contornos de fila y de columna, que se pueden aplicar en solo un paso. Un estilo de celda incluye otros formatos como los márgenes de celda, los estilos de párrafo, los contornos y los rellenos. Cuando edita un estilo, éste se actualizará automáticamente en todas las tablas o celdas en las que se haya aplicado.

-Estilos [Tabla básica] y [Ninguno]

De manera predeterminada, cada documento nuevo contiene un estilo [Tabla básica] que podrá aplicar a las tablas que cree y un estilo [Ninguno] que podrá utilizar para eliminar los estilos de celda de éstas. Puede editar el estilo [Tabla básica] pero no podrá cambiarle el nombre ni eliminar los estilos [Tabla básica] o [Ninguno].

-Uso de estilos de celda en estilos de tabla:

Al crear un estilo de tabla puede especificar qué estilos de celda desea aplicarles a las distintas áreas de la tabla: a las filas de encabezado y pie de página, a las columnas izquierda y derecha y a las filas de texto. Por ejemplo, para la fila de encabezado, puede asignar un estilo de celda que aplique un estilo de párrafo y para las columnas izquierda y derecha, puede asignar estilos de celda
diferentes que apliquen fondos con sombras.

-Información general sobre los paneles Estilos de tabla y Estilos de celda:

Utilice el panel Estilos de tabla (Ventana > Estilos > Estilos de tabla) para crear y asignar nombres a los estilos de las tablas y para aplicar los estilos a las tablas ya existentes o a las tablas que cree o importe. Utilice el panel Estilos de celda (Ventana > Estilos> Estilos de celda) para crear y asignar nombres a los estilos de celda y para aplicar los estilos a las celdas de la tabla. Los estilos se guardan con un documento y se muestran en el panel cada vez que se abre el documento. Para que la administración resulte más sencilla, puede guardar los estilos de tabla y de celda en grupos.

-Aplicación de estilos de tabla y celda:

A diferencia de los estilos de párrafo y carácter, los estilos de tabla y celda no comparten atributos. Esto quiere decir que, al aplicar un estilo de tabla, no se anula el formato de celda y que, al aplicar un estilo de celda, no se anula el formato de tabla. De manera predeterminada, la aplicación de un estilo de celda elimina el formato que haya aplicado cualquier estilo de celda anterior, pero no elimina el formato de celda local. Asimismo, la aplicación de un estilo de tabla elimina el formato que haya aplicado cualquier estilo de tabla anterior, pero no elimina las modificaciones realizadas a través de la casilla de verificación Opciones de tabla

-Modificación de los estilos de tabla y celda:

Una vez aplicado un estilo de tabla o celda, puede anular cualquier configuración que tenga. Para anular un estilo de tabla, puede cambiar las opciones del cuadro de diálogo Opciones de tabla. Para anular una celda, puede cambiar las opciones del cuadro de diálogo Opciones de celda o bien utilizar otros paneles para cambiar el trazo o relleno. Si selecciona una tabla o celda que tiene una modificación, aparecerá un signo más (+) junto al estilo en el panel Estilos.

Puede borrar las modificaciones de tabla y celda cuando aplique un estilo. También puede borrar las modificaciones de una tabla o celda en la que ya se ha aplicado un estilo.

 

• FÓRMULA PARA ORDENAR DATOS EN EXCEL

 

Excel nos permite ordenar la información rápidamente utilizando el comando Ordenar y filtrar, pero en ocasiones necesitamos una fórmula para ordenar datos en Excel y en esta ocasión te mostraré una alternativa para hacerlo.Los datos que utilizaremos en nuestro ejemplo tienen una columna de texto y otra columna de números. En el primer ejemplo ordenaremos los datos por los datos numéricos y en el segundo ejemplo ordenaremos por los valores de texto.

-Fórmula para ordenar datos numéricos:

Para nuestro primer ejemplo haremos un ordenamiento descendente de la columna Calificación. La función que nos permitirá hacer esto de una manera sencilla será la función JERARQUIA.EQV la cual obtiene la posición de un número dentro de un rango. Es importante mencionar que esta función está disponible a partir de Excel 2010, para versiones anteriores debes utilizar la función JERARQUIA. Ahora considera la siguiente fórmula:

=JERARQUIA.EQV(B2,$B$2:$B$9)

 Esta fórmula obtiene la posición del valor en B2 dentro del rango B2:B9 que para nuestro ejemplo será la posición 3.

-Fórmula para ordenar números en Excel:

La columna Posición asigna el número 1 al valor más alto dentro del rango y a partir de ahí se enumeran todos los registros. Puedes ver que la posición 8 corresponde a la calificación 61 la cual es la más baja de todas las calificaciones. Ahora que ya tenemos las posiciones podemos ordenar los datos utilizando como referencia la columna posición y utilizando la función INDICE y la función COINCIDIR de la siguiente manera:

=INDICE($A$2:$A$9,COINCIDIR(E2,$C$2:$C$9,0))

La función INDICE obtiene un valor dentro de un rango con solo especificar su fila. Con esta fórmula estamos buscando dentro del rango A2:A9 y la fila la obtenemos al hacer coincidir el número 1 de la columna E con el número 1 de la columna C (Posición) que para nuestro ejemplo es Roberto:

-Ordenar números con fórmula de Excel:

Al copiar la fórmula hacia abajo obtenemos automáticamente el nombre de cada alumno de acuerdo a su posición. Para obtener el dato de la calificación usamos una fórmula idéntica a la anterior con la diferencia de que la función INDICE hará la búsqueda sobre el rango B2:B9:

 Fórmula para ordenar números automáticamente en Excel

Con esto hemos ordenado los datos de manera descendente utilizando una columna numérica. Si queremos hacer un ordenamiento ascendente debemos hacer un solo cambio en la función JERARQUIA.EQV de la columna Posición colocando un número 1 como el tercer argumento de la función lo cual hará que los datos se ordenen de manera ascendente. Observa la fórmula de la celda C2 y el resultado del ordenamiento en los datos:

-Fórmula para orden ascendente de números:

En este caso los datos se ordenan comenzando por la calificación de menor valor hasta la de mayor valor. Así que con solo modificar el tercer argumento de la función JERARQUIA.EQV podrás controlar el tipo de ordenamiento de los datos.

-Desempatar posiciones en Excel:

Al tener dos datos numéricos del mismo valor, la función JERARQUIA.EQV determina un empate entre ambos y les otorga la misma posición, que en nuestro ejemplo es la posición 5, pero la siguiente posición otorgada será la posición 7 saltándose la posición 6 y por lo tanto generando un error en nuestra tabla ordenada.

Fórmula para ordenar texto en excel:

Para ordenar texto en Excel utilizando fórmulas es importante recordar la manera en que la herramienta trabaja con texto especialmente al utilizar operadores de comparación. Observa la siguiente tabla de datos:

Al utilizar el operador Mayor que (>) para comparar dos textos, Excel nos responderá si el primero de ellos tiene una posición mayor dentro del alfabeto. Para la celda C2 el resultado es falso porque la letra A no está después de la letra B. Sin embargo, para la celda C3 el resultado es verdadero porque la letra Z está efectivamente después de la letra A.

• ORDENAR Y FILTRAR DATOS EN EXCEL 

 

-Ordenar una tabla en excel:

Al pulsar el botón de filtro en el encabezado de una columna veremos algunas opciones de ordenación de datos. Al aplicar un criterio de ordenación a una tabla de Excel las filas se reorganizarán de acuerdo al contenido de la columna por la cual estamos ordenando. Si la columna contiene datos de tipo texto, entonces podremos ordenar los valores de A a Z (alfabéticamente) o de Z a A.

-Filtrar datos en una tabla de excel:

Cuando hablamos de filtrar datos en una tabla de Excel nos referimos a desplegar solamente aquellas filas que cumplen con los criterios especificados. Los datos de la tabla no son eliminados ni alterados sino que solamente se ocultan a la vista. Las opciones de filtrado se muestran también al pulsar el botón de flecha que se encuentra al lado de los encabezados de columna.

• FORMATO CONDICIONAL:

-Resaltar reglas de celdas:

Celdas vacías con formato condicional:

Nuestro primer objetivo será resaltar de color rojo aquellas celdas del rango B2:B10 que están vacías, así que comenzamos por seleccionar dicho rango de celdas y en seguida creamos una nueva regla de formato condicional desde Inicio > Formato condicional > Nueva regla.

Celdas contiguas a las celdas vacías:

Si en lugar de resaltar las celdas que están vacías te interesa resaltar las celdas que están a un lado de las celdas vacías, entonces el procedimiento cambiará apenas un poco ya que la única diferencia será el rango a donde aplicamos la regla de formato condicional creada.Por ejemplo, si en lugar de resaltar las celdas de la columna B queremos resaltar las celdas de la columna A que están a un lado de una celda vacía, entonces la regla de formato condicional debe ser creada sobre el rango A2:A10, así que comenzamos por seleccionar dichas celdas y creamos una nueva regla de formato condicional de la misma manera que en la sección anterior.

La fila que contiene una celda vacía:

Hasta ahora solo hemos resaltado las celdas de la columna B o las celdas de la columna A, pero es posible resaltar la fila completa de nuestro rango en caso de que alguna celda de la columna B esté vacía. En ese caso necesitaremos ampliar el rango a donde se aplicará la regla de formato condicional así que debemos seleccionar el rango A2:B10 y crear la nueva regla de formato condicional de la siguiente manera:

Observa que sigo utilizando la función ESBLANCO pero su argumento ha cambiado un poco ya que he agregado el símbolo $ antes de la letra B de la siguiente manera:

=ESBLANCO($B2)

Resaltar celdas no vacías:

Como último ejemplo te mostraré que no es tan complicado invertir la lógica de los ejemplos anteriores para resaltar las celdas que no están vacías. Supongamos que quiero resaltar las celdas de la columna B que no están vacías, así que comenzaré por seleccionar el rango B2:B10 y crearé la regla de formato condicional de la siguiente manera:

En este caso he utilizado la función NO cuyo objetivo es invertir el resultado de la función ESBLANCO y por tal motivo obtendré un valor VERDADERO cuando la celda no esté vacía. La fórmula que he utilizado es la siguiente:

=NO(ESBLANCO(B2))

-Escalas de color:

El objetivo del formato condicional con escalas de color es permitirnos identificar visualmente los valores numéricos que se aproximan tanto al valor máximo como al valor mínimo dentro de un rango. La tonalidad de cada celda dependerá de su cercanía con cualquiera de estos dos extremos. Por ejemplo, en el siguiente rango hay números aleatorios entre 1 y 97:

Los colores de las celdas varían entre el blanco y el verde, siendo el color verde el que está asociado al valor máximo y el color blanco al valor mínimo. Entre más pequeño sea un número, el color de la celda se tornará cada vez más claro hasta llegar a ser completamente blanco para el valor mínimo.

 Escalas de dos y tres colores:

Muchas veces necesitamos identificar el valor máximo, mínimo y el valor medio de un rango y conocer las celdas que se aproximan a cada uno de estos valores. Es por eso que Excel también nos provee de escalas de tres colores. De manera predeterminada podemos elegir entre 12 escalas de color, seis de ellas son escalas de tres colores y las otras seis son escalas de dos colores. Podemos acceder a estas opciones desde Inicio > Estilos > Formato condicional > Escalas de color:

-Reglas superiores e inferiores:

La regla 10 superiores te permitirá resaltar de manera inmediata las celdas que contengan los 10 valores con mayor valor dentro del rango de celdas. El cuadro de diálogo de esta regla es el siguiente:

Observa que puedes cambiar el número de elementos a seleccionar, por lo que en lugar de los 10 superiores podrías seleccionar los 20 superiores si así lo deseas. La regla 10 inferiores funciona de manera similar, solo que Excel obtendrá las celdas con menor valor dentro del rango.

La regla 10% de valores superiores es similar a las reglas anteriores, pero en lugar de indicar un número específico de celdas se introduce un porcentaje de celdas a seleccionar. Por ejemplo, dentro de un rango que contiene 20 celdas, si se especifica un 10% entonces el formato condicional se aplicará a las 2 celdas con mayor valor porque el 10% de 20 celdas son 2 celdas. El cuadro de diálogo es el siguiente:

-Conjuntos de Iconos:

Otra alternativa que tenemos para aplicar formato condicional es utilizar conjuntos de iconos los cuales son aplicados de acuerdo a la cantidad de rangos definidos y que pueden ser tres, cuatro y hasta cinco rangos.

 Ejemplo de formato condicional con iconos:

Tengo una lista de números aleatorios y voy a aplicar formato condiciona utilizando un conjunto de iconos desde Inicio > Estilos > Formato condicional > Conjunto de iconos y selecciono la opción 3 flechas (de color). El resultado es el siguiente:

Los valores más grandes tienen asociada una flecha de color verde mientras que los valores medios una de color amarillo. Solamente el valor 8 es el que tiene una flecha de color rojo porque es el valor más pequeño de todo el rango. ¿Cómo podemos conocer los valores exactos que determinan la asignación de una flecha verde, amarilla o roja? Ve a Inicio > Estilos > Formato condicional > Administrar reglas y se mostrará la regla que acabamos de crear. Pulsa el botón Editar regla y podremos ver los valores numéricos que determinan cada uno de los rangos:

-Creacion de formatos personlizados:

https://www.tusitioexcel.com/basico/formatos-personalizados-excel/