Todo lo que debes saber de Excel:
Excel Avanzado
1 Atajos de teclado en hoja de Cálculo
Acción |
Combinación de teclas |
|---|---|
Selecciona las celdas de una columna |
CTRL+ MAYUSC+FLECHA ABAJO |
Función |
Acción |
|---|---|
=Hoy()
|
Escribe la fecha del sistema operativo
|
| La función HOY() guarda la fecha del sistema. De manera que cada vez que abro un documento se guarda el día actual, dado que és un número, podré restarlo a cualquier fecha. |
|---|
En este ejemplo calcularemos los días que faltan para el vencimiento de unas facturas, teniendo en cuenta la fecha
de vencimiento de las mismas y la función Hoy()
En este otro ejemplo calcularemos los días que tiene cada empleado teniendo en cuenta la función hoy(), a su vez con la funcion frac.año() calcularemos los años que hay en la columna días. Para poder redondear los años sin que los decimales superiores a 5 añadan un año más, utilizamos la función redondear.menos()
La función SI de Excel devuelve un valor si una condición es verdadera y otro valor si es falsa. Puede usar hasta 64 funciones SI adicionales dentro de una función SI.
Por esto, una instrucción SI puede tener dos resultados. El primer resultado es si la comparación es Verdadera y el segundo si la comparación es Falsa.=SI(CONDICIÓN;VERDADERO;FALSO) Utilizaremos la primera funció Si para escribir suspendido o aprobado en una celda según la nota escrita en otra celda. Ejemplo: en la celda A14 tenemos la nota obtenida por un alumno, dónde la más baja es 1 y la más alta será 10. Por lo que con un 5 estará aprobado. =SI(A14<5;"SUSPENDIDO";"APROBADO")
 |
OPERADORES CONDICIONALS = IGUAL > MAYOR QUE < MENOR QUE >= MAYOR o IGUAL QUE <=MENOR o IGUAL QUE <> DIFERENTE DE |
En este otro ejemplo vamos a utilizar el Si condicional para valorar que en función del sueldo si es superior a 20.000 se le va a anotar el texto de no revisar y de lo contrario se va a escribir revisar
Condición numérica y respuesta en texto
=SI(E2>20000;"NO REVISAR";"REVISAR")
Teniendo en cuenta el resultado anterior si contiene REVISAR se le va a aplicar un aumento al sueldo de la columna correspondiente al aumento, de lo contrario el sueldo se queda igual.
Condición de texto y respuesta con operación matemática
=SI(F2="REVISAR";(E2*1,5%)+E2;E2)
Si Anidado en Excel
Teniendo en cuenta que a cada una de estas notas escolares les pertenece el
texto de la derecha.
Miraremos de crear un Si anidado para dar respuesta a todas las
posibilidades.
La celda A1 contendrá un valor del 1 al 10 y en la celda B1 ha de mostrar
el texto según la Nota.
Con esta anidación resolvemos hasta el valor 5
=SI(A1>2;SI(A1>4;"suf";"Ins");"Md")
Con esta anidación resolvemos hasta el valor 6
=SI(A1>2;SI(A1>4;SI(A1>5;"Bien";"suf");"Ins");"Md")
Con esta anidación resolvemos hasta el valor 8
=SI(A1>2;SI(A1>4;SI(A1>5;SI(A1>6;"Not";"Bien");"suf
");"Ins");"Md")
Con esta anidación resolvemos hasta el valor 10
=SI(A1>2;SI(A1>4;SI(A1>5;SI(A1>6;SI(A1>8;"Sob";"N
ot");"Bien");"suf");"Ins");"Md")
4 Crear Nombres
Los nombres se usan en fórmulas y con ellos serán mucho más fáciles de entender y mantener. Puede definir un nombre para un rango de celdas, una función, una constante o una tabla.
Para abrir el cuadro de diálogo Administrador de nombres, en la pestaña Fórmulas, en el grupo Nombres definidos, haga clic en Administrador de nombres.
En este ejemplo se usa un Rango nombre al que se le llama Dias_semana que hace referéncia al rango =Hoja3!$C$5:$C$11.
Este nombre se puede utilizar en la misma hoja del libro o bién en cualquier otra hoja del mismo libro. También se pueden utilizar en otras fórmulas.
La función BUSCARV de Excel busca elementos en una tabla o un rango por su fila. El secreto está en organizar los datos de modo que el valor que se consulta esté a la izquierda del valor que se desea buscar. Así dispuesto todo, use BUSCARV para dar con el valor de la columna deseada según el valor de la columna de la izquierda.
En el ejemplo de Buscarv en primer lugar vamos a crear una base de datos dónde iremos a buscar la referéncia del producto.
La columna Referencia contiene el valor único que identifica el producto
La columna Nombre Producto contiene la función =BUSCARV(A8;productes;2)
La columna Unidadad debemos introducir el número de unidades
La columna Precio
contiene la función=BUSCARV(A8;productes;3;FALSO)
La columna Dto =BUSCARV(A8;productes;4;FALSO))
La columna Total pedido contiene la operación de Precio x unidad - dto
Si queremos dejar las fórmulas para utilizarla como plantilla debemos incluir la función Si condional que valora si tenemos referencia en la fila activa. =SI(A8="";"";BUSCARV(A8;productes;3;FALSO))
- Para calcular el pago mensual utilizaremos la función Pago
La función =PAGO() calcula los pagos periódicos que tendremos que pagar sobre un préstamo, a un interés determinado, y en un tiempo x.
=PAGO(Interés;Tiempo;Capital)
Esta fórmula nos calculará el pago anualmente. Si queremos saber los pagos mensuales tendremos que dividir el interés por 12 y multiplicar el tiempo por 12. Observa:
=PAGO(Interés/12;Tiempo*12;Capital)
La función ABS() devuelve el resultado en valor absoluto.
La función =PAGOINT()Devuelve el interés pagado en un período específico por una inversión basándose en pagos periódicos constantes y en una tasa de interés constante.
PAGOINT(tasa, período, nper, va, [vf], [tipo])
7 Fórmulas con hojas y libros vinculados
Referencia externa a otro libro: Las fórmulas con referencias externas a otros libros se muestran de dos maneras, en función de si el libro de origen, el que proporciona datos a la fórmula, se encuentra abierto o cerrado en Excel. Cuando el libro de origen está abierto en Excel, la referencia externa incluye el nombre del libro entre corchetes ([ ]), seguido del nombre de la hoja de cálculo, un signo de exclamación (!) y las celdas de las que depende la fórmula. Por ejemplo, la siguiente fórmula suma las celdas C10:C25 del libro Presupuesto.xls.Referencia externa =SUMA([Presupuesto.xlsx]Anual!C10:C25) |
Si el libro de origen está cerrado en Excel, la referencia externa incluye toda la ruta de acceso.
Referencia externa =SUMA('C:\Informes\[Presupuesto.xlsx]Anual'!C10:C25) |
Para crear un gráfico, se ha de seleccionar al menos una celda en un rango de datos (un conjunto de celdas).
Si sus datos de gráfico se encuentran en un rango continuo de celdas, seleccione cualquier celda de dicho rango.
Si los datos están en diferentes columnas, se ha de hacer selección de dos o más rangos utilizando la tecla CTRL
En este ejemplo el primer gráfico tiene un tipo de gráfico Barras 3D agrupadas para las dos series.
El segundo gráfico tiene una serie combinada de Barras 2d agrupadas con líneas.
El tercer gráfico tiene una serie de áreas y otra con líneas.
Para combinar un gráfico, primero se selecciona el primer gráfico y a continunación en la cinta de propiedades de gráfico se selecciona Cambiar tipo de gráfico. En este cuadro de diálogo es dónde se puede escoger el tipo de gráfico y en las pestaña inferior se escoge el tipo para cada serie.
Consolidar datos de varias hojas de cálculo en una sola hoja de cálculo
Para resumir los resultados de datos en diferentes hojas de cálculo e informar de ellos, puede consolidar los datos de cada hoja de cálculo independiente en una hoja de cálculo (u hoja de cálculo maestra). Las hojas de cálculo que se consolidan pueden estar en el mismo libro como una hoja de cálculo maestra o en otros libros. Al consolidar datos en una hoja de cálculo, puede actualizarlos y agregarlos más fácilmente.
Consolide por categorías Use este método cuando los datos de varias áreas de origen se organizan de forma diferente, pero se usan las mismas etiquetas de fila y columna. Por ejemplo, puede usar este método cuando tiene una serie de hojas de inventario de cada mes en las que se usa el mismo diseño, pero cada hoja de cálculo contiene distintos elementos o un número diferente de elementos.
Puede consolidar datos mediante el comando Consolidar (pestaña Datos, grupo Herramientas de datos). También puede consolidar los datos con una fórmula o un informe de tabla dinámica.
Consolidar datos por categoría
1. En cada hoja de cálculo que contiene los datos que desea consolidar, prepare los datos del
siguiente modo:
▪ Asegúrese de que cada rango de datos está en formato de lista: cada columna tiene una etiqueta en la primera fila, contiene hechos similares y no tiene filas o columnas en blanco.
▪ Coloque cada rango en una hoja de cálculo diferente, pero no ponga ningún rango en la hoja de cálculo donde vaya a colocar la consolidación.
▪ Asegúrese de que cada rango tiene el mismo diseño.
2. En la hoja de cálculo maestra, haga clic en la celda superior izquierda del área donde desea que aparezcan los datos consolidados.
NOTA Para evitar sobrescribir datos existentes en la hoja de cálculo de destino que contiene los datos que está consolidando, asegúrese de dejar celdas suficientes a la derecha y debajo de esta celda para los datos consolidados.
3. En el grupo Herramientas de datos de la ficha Datos, haga
clic en Consolidar.
4. En el cuadro Función, haga clic en la función de resumen que desea que use Excel para
consolidar los datos.
5. Si la hoja de cálculo se encuentra en otro libro, haga clic en Examinar para buscar el archivo y,
a continuación, haga clic en Aceptar para cerrar el cuadro de diálogo Examinar.
La ruta de acceso del archivo se escribe en el cuadro Referencia seguido de un signo de exclamación.
6. Si la hoja de cálculo está en el libro actual, haga lo siguiente:
- 1. Haga clic en el botón de referencia a la derecha del cuadro de Referencia para abrir el
- cuadro de diálogo Consolidar - Referencia.
- 2. Abra la hoja de cálculo que contiene los datos que desea consolidar, seleccione los datos
- y haga clic en el botón a la derecha del cuadro.
7. En el cuadro de diálogo Consolidar, haga clic en Agregar y repita los pasos 6 y 7 para agregar
todos los rangos que desea.
8. Decida cómo desea actualizar la consolidación. Siga uno de los procedimientos siguientes:
▪ Para configurar la consolidación de manera que se actualice automáticamente cuando cambien los datos de origen, active la casilla de verificación Crear vínculos con los datos de origen.
>IMPORTANTE Solamente puede activar esta casilla de verificación si la hoja de cálculo se encuentra en otro libro. Cuando active esta casilla de verificación ya no podrá cambiar las celdas y los rangos que se incluyen en la consolidación.
▪ Para configurar la consolidación de modo que puede actualizarla manualmente cambiando las celdas y los rangos incluidos, desactive la casilla de verificación Crear vínculos con los datos de origen.
9. Active las casillas de verificación de Usar rótulos en que indican donde se encuentran los
rótulos en los rangos de origen, que puede ser: la Fila superior, la Columna izquierda o ambas.
Práctica de Consolidación por Categorías
Crear un nuevo libro y llamarlo Consolidación de datos Introducir los siguientes datos en las diferentes hojas del libro.
Las tablas dinámicas te permiten resumir y analizar fácilmente grandes cantidades de información con datos que pueda agrupar.
En primer lugar se debe disponer de una lista de datos bién estructurada, dónde la primera fila contenga los títulos de cada calumna. Se entiede como registro cada fila de datos que compone la lista. Las columnas deberán contener el mismo tipo de datos(numérico,texto, fecha) de no ser así las entenderá como texto.
Las listas no deben tener ninguna fila completamente vacía, y tampoco ninguna columna en medio completamente vacía. De lo contrario los datos quedaran partidos.
En este ejemplo tenemos una lista con 4 columnas y 287 filas con datos de productos y las ventas que se han producido en cada trimestre. Ahora realizaremos diferentes informes dado que la columna de categoria tiene diferentes datos que se pueden agrupar, al igual que los productos y los trimestres.
Primer paso: Colocar el cursor en un lugar cualquiera de la base de datos.
Segundo paso: Menú Insertar/Tabla dinámica, mostrará un cuadro de diálogo como el siguiente y escogemos Nueva hoja de cálculo para la colocación del informe
| En este Informe se agrupan las categorías y se suman el precio de las ventas |
|---|
 |
| Para dar formato a la tabla, en primer lugar damos formato moneda a la columna valor y luego escogemos diseño de la cinta de opciones de Tabla dinámica. En las pestaña diseño se puede escoger entre modelos prediseñados o bién se pueden personalizar. |
 |
| En el siguiente informe se ha añadido tan sólo la columna de producto. Ahora muestra la suma del valor de cada producto agrupado por categoría. |
 |
| En este otro informe se ha añadido el trimestre. Ahora muestra la suma del valor de cada producto agrupado por categoría y trimestre. |
 |
Ejercios Excel avanzado con soluciones
Descarga un archivo con ejemplos