Curso Excel Básico - Intermedio

Relator: Miguel Rivera Adonis

“Clase 4”

IntroducciónBase de Datos: !➢ Definición de Base de Datos ➢ Ordenar datos ➢ Formulario ➢ Filtros ➢ Trabajar con Sub-Totales ➢ Validación de Datos ➢ Funciones de Búsqueda

Base de DatosConcepto • Una base de datos es un conjunto o colección de

datos, la misma que se encuentra organizada por campos y registros. Microsoft Excel incluye funciones de  base de datos para  realizar un análisis  de los mismos, los  cuales se encuentran almacenados en forma de lista. !

Campos y Registro • Los campos representan por columnas. Ejemplo:

Nombre, Apellido, edad, estado civil, etc. Los registros son los datos específicos de cada

campo, se encuentra representado filas. Ejemplo: Andrés Carrasco, 20 años, soltero.

Ordenar Datos• Ordenar los datos es una parte esencial del análisis de datos. Puede que

desee poner una lista de nombres en orden alfabético, compilar una lista de

niveles de inventario de productos de mayor a menor u ordenar filas por

colores o por iconos. Ordenar los datos ayuda a verlos y a comprenderlos

mejor, así como a organizarlos y encontrarlos más fácilmente y a tomar

decisiones más eficaces.

Ordenar Datos Utilizando un solo criterio

1. Selecciona el rango D2:D26, incluyendo el encabezado de columna (Horas) 2. En la ficha Datos, haz clic en Ordenar de la A a Z.  . Antes de realizar el ordenamiento, aparece un

mensaje de advertencia que te pedirá ampliar la selección de datos. Debes tener presente que con sólo una columna seleccionada, los datos no se ordenarán de manera apropiada. Da clic en el botón Ordenar..., pero con la marca de verificación en "Ampliar la selección". Los datos se ordenan por el número de horas.

3. Selecciona cualquier celda de la columna A y haz clic en Ordenar de A a Z. Los datos se ordenaran por Apellido.

4. Ahora deberás ordenar la lista según el  Cargo, por lo tanto selecciona el rango  A2:E26. Haz clic en Ordenar para abrir la caja de diálogo Ordenar.

5. En la caja de diálogo Ordenar  y en el cuadro Ordenar por, selecciona Cargo  y luego en Criterio de ordenación selecciona Z a A y para terminar clic en el botón Aceptar.

Ordenar Datoscon Múltiples Criterios

1. Selecciona el rango A2:E26, puedes seleccionar el rango digitándolo en el Cuadro de nombres.

2. Haz clic en Ordenar para abrir el cuadro de diálogo 3. Selecciona Cargo en Ordenar por y como Criterio de ornenación de la A a Z. 4. Haz clic en Agregar nivel para indicar el segundo criterio de ordenación. 5. Selecciona Apellido como segundo criterio. El criterio de ornación debe quedar

la predeterminada 6. Haz clic en Aceptar.

Ordenar Datos usando Formato Condicional

1. Te ubicas en la ficha  Inicio, haz clic en  Buscar y seleccionar  y haz clic en Formato concional para ver si tus datos tienen formato condicional. En este caso aparece un mensaje para informar que ninguna celda de la hoja de cálculo tiene un formato condicional. Haz clic en Aceptar para cerrar la caja o cuadro de diálogo. !

2. Selecciona el rango D3:D26. Haz clic en Formato Condicional y luego haz clic en Conjunto de iconos y por último en 3 flechas (de color). Cada valor en la columna tiene ahora una fecha que indica si el valor está dentro del rango alto, medio o bajo. !

3. Selecciona el rango A2:E26. En la ficha  inicio, haz clic en Ordenar y filtrar del grupo Modificar y luego haz clic en Orden personalizado...

1.

2.

3.

4. Selecciona Horas en el cuadro Ordenar por. Selecciona Icono de celda debajo de Ordenar según. Acepta la flecha verde debajo de Criterio de ordenación. !

5. Haz clic en el botón Agregar nivel. !

6. Selecciona Horas, en el cuadro Luego por. Selecciona Icono de celda debajo de Ordenar según, selecciona la flecha amarilla y en la parte superior En el campo Criterio de ordenación. Haz clic en Aceptar. Los datos se ordenan con el conjunto de icono.

Ordenar Datos utilizando Atributos de Celdas

1. Selecciona el rango A3:E28 (incluye los encabezados de columnas). Haz clic en Ordenar.

2. En la caja de diálogo Ordenar, selecciona Apellido en el cuadro Ordenar por. Debajo de Ordenar según, selecciona Color de celda.

3. En Criterio de ordenación, selecciona el color rosado y En la parte superior.

4. Haz clic en Agregar nivel y selecciona Apellido en el cuadro Ordenar por. Debajo de Ordenar según, selecciona Color de celda. Selecciona el color Amarillo y En la parte superior.Agrega un nivel para el Verde y luego Agrega un nivel para el color Celeste. Debes tener un criterio para cada color como se ilustra a continuación.

Formulario• Para modificar o introducir nuevos datos en la tabla podemos teclear

directamente los nuevos valores sobre la ella, o bien podemos utilizar un formulario de datos. Esta segunda opción viene muy bien sobre todo si la lista es muy grande. !

• Un formulario de datos es un cuadro de diálogo que permite al usuario escribir o mostrar con facilidad una fila entera de datos (un registro).

Abrir Formulario• Para abrir el formulario de datos, tenemos que posicionarnos en la lista para que

esté activa, y pulsar en el icono Formulario .

• Como esta opción no está directamente disponible en la Cinta de opciones, podemos añadirla a la Barra de acceso rápido, de la forma que ya vimos. Pulsando el Botón Office > Opciones de Excel > Personalizar, y Agregar el icono Formulario..., en la sección de Comandos que no están en la cinta de opciones.

Cuadro FormularioAl crear el formulario, disponemos de siguientes

botones: !

• Nuevo: Sirve para introducir un nuevo registro. • Eliminar: Eliminar el registro que está activo. • Restaurar: Deshace los cambios efectuados. • Buscar anterior: Se desplaza al registro anterior. • Buscar siguiente: Se desplaza al siguiente registro. • Criterios: Sirve para aplicar un filtro de búsqueda. • Cerrar: Cierra el formulario.

• Para  cambiar los datos de un registro, primero nos posicionamos sobre el registro, luego rectificamos los datos que queramos (para desplazarnos por los campos podemos utilizar las teclas de tabulación), si nos hemos equivocado y no queremos guardar los cambios hacemos clic en el botón Restaurar, si queremos guardar los cambios pulsamos la tecla Intro. !

• Para crear un nuevo registro, hacemos clic en el botón Nuevo, Excel se posicionará en un registro vacío, sólo nos quedará rellenarlo y pulsar Intro o Restaurar para aceptar o cancelar respectivamente. !

• Después de aceptar Excel se posiciona en un nuevo registro en blanco por si queremos insertar varios registros, una vez agregados los registros, hacer clic en Cerrar. !

• Para  buscar un registro  y posicionarnos en él podemos utilizar los botones  Buscar anterior y Buscar siguiente o ir directamente a un registro concreto introduciendo un criterio de búsqueda. Pulsamos en el botón  Criterios  con lo cual pasamos al formulario para introducir el criterio de búsqueda, es similar al formulario de datos pero encima de la columna de botones aparece la palabra Criterios. !

• Por ejemplo, si buscamos un registro con el valor  Ana  en el campo  Nombre, escribimos Ana en Nombre y pulsamos el botón Buscar Siguiente, Excel vuelve al formulario de datos y nos posiciona en el registro de nombre Ana.

Filtro Automático

• Autofiltro permite filtrar una lista de acuerdo a una serie de criterios.

Autofiltro es una gran herramienta para filtrar datos, pero en ciertas ocasiones

resulta limitada. En muchos de esos casos podemos utilizar Filtro Avanzado.

Filtro Avanzado• En Excel puedes realizar un filtrado de datos totalmente personalizado

proporcionando los criterios que deseas aplicar a la información. Este tipo de filtrado es conocido como Filtro avanzado y en esta ocasión te mostraré cómo utilizarlo.

• Los criterios por los cuales se realizará el filtrado deben especificarse dentro de celdas de la misma hoja. Supongamos que deseo filtrar los registros del departamento de Finanzas.

• Para este ejemplo coloqué los criterios por arriba de la tabla de datos aunque realmente su ubicación no es de importancia.

• Antes de aplicar el filtro avanzado debo seleccionar la tabla de datos (A4:D13) y posteriormente ir a la ficha Datos y pulsar el botón Avanzadas que se encuentra en el grupo Ordenar y filtrar. Se mostrará el cuadro de diálogo Filtro avanzado.

• De la misma manera puedes especificar un criterio para cada columna. Hasta ahora solamente he especificado una sola condición por columna,   pero ahora deseo agregar a los resultados del filtro   la información del departamento de Informática. Para ello solamente agregaré una fila adicional al rango de criterios de la siguiente manera.

Observa cómo en el cuadro de texto para Rango de criterios he seleccionado el rango que contiene las condiciones del  filtro avanzado  necesarias. Solamente resta pulsar el botón Aceptar para aplicar el filtro.

• Al aceptar los cambios Excel filtrará la información de los empleados de Finanzas con apellido Hernández y además mostrará la información de los empleados del departamento de Informática sin importar el apellido que tengan.

• Como podrás observar, es factible especificar una condición por cada fila del rango de criterios. De esta manera puedes crear un filtro avanzado en Excel.

Subtotales en Excel• Cuando tienes una tabla de datos con una gran cantidad de información,

los  subtotales en Excel  nos pueden ayudar a comprender e interpretar mejor la información. Excel permite agregar subtotales de una manera muy sencilla.

• Supongamos la siguiente tabla de datos de donde quiero obtener los subtotales de ventas por cada mes:

• Lo primero que debo hacer es ordenar los datos por la columna sobre la cual se obtendrán los subtotales.  Para este ejemplo ordenaré los datos por la columna Mes. La columna se ordena haciendo clic en cualquier celda de la columna y posteriormente seleccionado el comando  Ordenar de más antiguos a más recientes que se encuentra dentro de la ficha Inicio. !

• La tabla quedará ordenada por mes.

El Botón Subtotal en Excel

• Para realizar la inserción de los  subtotales en Excel  debo pulsar el comando Subtotal que se encuentra en la ficha Datos dentro del grupo Esquema.

• Me debo asegurar de que la primera lista desplegable tenga seleccionada la opción Mes ya que indica que para cada cambio de mes se insertará un subtotal. Además utilizaré la función Suma y  la columna Ventas que aparecen seleccionadas. Al hacer clic en Aceptar se insertarán los subtotales.

• Observa cómo Excel ha insertado una fila nueva que contiene el subtotal para cada mes. Además a la izquierda de la hoja de cálculo Excel coloca controles adicionales que son útiles para ocultar o mostrar los grupos de datos de acuerdo a los subtotales.  Al hacer clic en alguno de ellos el grupo correspondiente se expande o se contrae.

Validación de Datos• Validar la información que introduces en Excel es de suma importancia para

prevenir cualquier interpretación equivocada al momento de analizar los datos. Afortunadamente Excel tiene una funcionalidad que nos permite validar los datos al momento de ingresarlos. !

Lista desplegable en Excel

• Excel nos permite crear listas desplegables que dan la posibilidad al usuario de elegir un valor dentro de una lista que ha sido previamente definida y validada:

• Para hacer uso de esta funcionalidad primero debes seleccionar la celda donde deseas que aparezca la lista desplegable e ir a la ficha Datos y hacer clic sobre el comando Validación de datos:

• En el recuadro Origen puedes colocar directamente los valores de tu lista desplegable separados por coma:

• Al hacer clic en el botón Aceptar se verán reflejados los cambios en la celda:

Lista desplegable con valores en un rango

• Si tu lista tiene muchos elementos puedes colocarlos dentro de una hoja de Excel y especificar el rango en el cuadro de diálogo Validación de datos y de esta manera tener una lista desplegable con los valores extraídos de dicho rango:

No es mala idea invertir un  poco de tiempo para agregar esta funcionalidad a tus hojas de Excel, recuerda que una  lista desplegable  te evitará muchos problemas al permitir el ingreso de valores previamente validados.

Funciones de Búsqueda y Referencia

• En la cinta de opciones, bajo la ficha Fórmulas - Grupo Biblioteca De Formulas podemos encontrar una serie de botones que nos permiten acceder y trabajar con las funciones que incorpora el programa.

• Vamos a comenzar el desarrollo por las funciones de búsqueda y referencia:

• Las funciones de búsqueda y referencia son aquellas funciones que a partir de unos argumentos nos ayudan a localizar valores o datos dentro de rangos Excel.

• En múltiples ocasiones disponemos de tablas con datos e informaciones (listas de precios, tablas de salarios, de impuestos, de rappels, grandes relaciones de elementos a modo de bases de datos, en las que buscar ciertos valores, mediante procedimientos que van desde los más sencillos y directos hasta otras más "rebuscados" e indirectos que nos pueden dar solución a necesidades concretas y de más difícil cálculo de no conocer estas posibilidades. Para ello, Excel dispone de una serie de herramientas y funciones que vamos a conocer y practicar mediante varios ejemplos prácticos.

! Las funciones que pertenecen a esta categoría son:

BUSCAR FILABUSCARV COLUMNABUSCARH TRANSPONERDESREF ELEGIRINDICE IMPORTARDATOSDINAMICOS

COINCIDIR HIPERVINCULODIRECCION INDIRECTO

Función Buscar• Definición: 

Devuelve un valor procedente de un rango dispuesto en fila, columna, o bien de una matriz que se corresponde con un valor buscado dentro de otro rango confrontado La función BUSCAR tiene dos formas de sintaxis:  - Forma vectorial.- Forma matricial.

Función BuscarV• Esta función es  muy importante en Excel. Es una forma avanzada de buscar valores

en un rango de celdas ya que sustituye en muchas ocasiones a las posibilidades de trabajo de la función BUSCAR.

• Busca un valor en la primera columna de una tabla (referida mediante su rango o nombre),  y devuelve el valor de la celda situada en la misma fila y en otra columna de la tabla cuyo número de columna dentro de la tabla se especifique. La tabla en la que buscar deberá tener un mínimo de 2 columnas y la primera -la columna "de ataque"-  deberá estar ordenada ascendentemente  por sus valores. Ejemplos de aplicación pueden ser que conocido un código de articulo deseemos saber su precio de compra (encontrándose este en la columna 12 de la tabla de artículos), dado un número de teléfono averiguar el nombre del contacto en una tabla de guía de teléfonos...En ocasiones tendremos que anidar una función BUSCARV dentro de otra función BUSCARV con lo que los planteamientos se endurecen a la vez que se hacen más potentes.

1. El  valor buscado: valor del dato o referencia a celda que contiene el dato con el que se desea extraer la información de la columna izquierda de la tabla en la que se quiere buscar.

!2. Matriz: rango de celdas que contienen todos los datos de la tabla (rango de la tabla). Si en la primera fila de la

tabla existen unos  títulos o rótulos, éstos,  quedarán excluidos  del rango de la tabla especificado en este segundo argumento. Si al rango le ha sido asignado previamente un nombre, se puede especificar como este argumento, dicho nombre.

!3. Indicador de columna: número de la columna, en relación a la tabla, en la que se encuentra el dato que se

quiere extraer (si la tabla tiene 3 columnas son 1, 2 o bien 3). Se especifica el número, no la letra de la columna A, B, C...

!4. Ordenado (parámetro opcional pero interesante): permite especificar 'falso' o 'verdadero' para saber si el valor

existe o no de forma exacta en la tabla.

!1. Falso: Si no localiza el dato buscado -por igual, es decir por el valor exacto-, muestra #N/A como error.

Este parámetro se utiliza para búsquedas por igual, es decir coincidentes plenamente (que el valor buscado exista en la columna "de ataque" de la tabla -generalmente la primera-) y no exige tener la tabla ordenada por los valores de la columna por la que se desea buscar.

2. Verdadero: Es la opción predeterminada, de no ser especificada. Si no localiza el dato buscado aporta el valor correspondiente al dato más parecido al mismo. El que corresponde al mayor valor menor.

Función BuscarH• La función BUSCARH en Excel busca un valor dentro de una fila y devuelve el valor

que ha sido encontrado o un error #N/A en caso de no haberlo encontrado. Esta función es similar, en cierto sentido, a la función BUSCARV.

Función Coincidir• Localiza un valor en un vector (rango de una sola columna o fila) y devuelve el

número de posición en la que se encuentra dicho valor. Si lo encuentra en la primera posición devolvería un 1, en la tercera posición devolvería un 3...

• Se utiliza para localizar, por ejemplo, en una tabla rectangular en donde existen títulos de fila y títulos de columna, en qué posición se encuentra en el rango de títulos de fila un determinado valor, y con otra función coincidir en qué posición se encuentra un valor dentro del rango que abarcan los títulos de columna. Con esos dos valores, posición en fila y posición en columna, la función COINCIDIR, trabaja en "tandem" con la función INDICE, la cual con esos dos datos, localiza un valor "al cruce" a modo de búsqueda en tabla de referencias cruzadas, a partir de los dos datos aportados previamente por la función coincidir. !

• Si se desea localizar la posición en vez del valor, esta es la función adecuada. No requiere que los datos del vector, se encuentren ordenados.

Función Índice• La función INDICE en Excel nos ayuda a obtener el valor de una celda dentro de

una matriz especificando el número de fila y columna. Esta función tiene dos formas de uso: de forma matricial y de forma de referencia.

Función Fila• La función FILA en Excel  nos devuelve el número de la fila que ocupa la

referencia que proporcionemos como argumento.  Si omitimos el argumento, la función FILA supondrá que nos referimos a la celda donde se encuentra actualmente.

Función Columna• Obtiene el número de columna de la celda especificada.

Función Dirección• Esta función obtiene la referencia válida en formato Excel -y en forma de texto-

correspondiente a la fila y columna especificadas como primer y segundo argumentos. En su sintaxis, aparece un tercer argumento que indicará si la referencia o dirección resultante se desea representar con forma de referencia absoluta o fija (con $) en las dos partes de la referencia, solo en la fila, solo en la columna, o en ninguna de las dos partes de la referencia.

•Fila (obligatorio): El número de fila de la celda. !•Columna (obligatorio): El número de columna de la celda. !•Abs (opcional): El tipo de referencia que se devolverá. La referencia absoluta es el valor predeterminado, 2 = Fila absoluta, columna relativa, 3 = Fila relativa, columna absoluta, 4 = Referencia relativa !•A1 (opcional): Indica si el estilo de referencia es A1, de lo contrario se utiliza el estilo de referencia F1C1. !•Hoja (opcional): El nombre de la hoja que se utilizará como referencia. Si se omite se usa la hoja actual

Función Elegir• La función ELEGIR en Excel  recibe una lista de valores y nos permite elegir algún

elemento de dicha lista con solo especificar la posición del elemento que necesitamos. Podemos especificar hasta 254 valores en la lista entregada a la función.

Función Desref• La función DESREF en Excel nos devuelve una referencia a un rango de celdas

que ha sido desplazado respecto a otra referencia que hemos especificado

Función Indirecto• La función INDIRECTO en Excel nos ayuda a obtener una referencia a una celda o

a un rango de celdas. Puedes utilizar esta función para crear una referencia que no cambiará aun cuando se inserten filas o columnas a la hoja de Excel.

• También puede ser utilizada para crear una referencia a partir de una letra y un número dando forma a la dirección de la celda de la cual deseamos obtener la referencia.

Función Transponer• En el artículo Cambiar de filas a columnas mostré como utilizar el comando Pegar

> Transponer para cambiar los datos de una fila a una columna de manera que se muestren como encabezados de columna.   Ahora veremos cómo ese mismo comando lo podemos utilizar para transponer rangos de celdas en Excel.

Función Importardatosdinamicos

• La función IMPORTARDATOSDINAMICOS en Excel  te permite copiar datos a partir de una tabla dinámica. Una de las ventajas es que al momento en que se actualicen los datos de la tabla, los datos copiados también se actualizan.