Post on 26-Sep-2019
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 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.