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.
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.
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
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.
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.
-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:
