Aulatecnic.es

La formación no cambia el mundo, cambia a las personas que van a cambiar el mundo.

A.
Mejora tus hojas de cálculo incluyendo listas desplegables con validación de datos
B.
La función buscarV te dará múltiples soluciones para hacer más dinámicas tus hojas de cálculo
C.
Conocer las tablas dinámicas te permitirá realizar tantos informes como datos se recogen en una lista de datos.

Todo lo que debes saber de Excel:

Excel Avanzado

 

1 Atajos de teclado en hoja de Cálculo
2 Funciones de fecha

3 Función Si condicional

4
Crear Nombres de rango
5
La función BuscarV

6 Funciones financieras
7 Fórmulas con hojas y libros vinculados
8 Gráficos combinados
9 Consolidación de datos
10 Tablas dinámicas

 

 

1 Atajos de teclado en hoja de Cálculo

Acción
Combinación de teclas
Selecciona las celdas de una columna
Selecciona las celdas de una fila
Añade comentario en la celda
Cuadro de diálogo "Buscar"
Cuadro de diálogo "Guardar"
Muestra la ayuda de Excel
En edición Inserta $ en 4 modos
CTRL+ MAYUSC+FLECHA ABAJO
CTRL+ MAYUSC+FLECHA DERECHA
MAYUSC + F2
Control + B
Control + G
F1
F4 - para referéncias mixtas y absolutas

 

2 Funciones de fecha

Función

Acción

=Hoy()
=Ahora()
=Dia(A1)
=Mes(A1)
=Año(A1)
=Diasem(A1)

 

Escribe la fecha del sistema operativo
Escribe la fecha y hora del sistema operativo
Devuelve el día de la fecha introducida en A1
Devuelve el mes de la fecha introducida en A1
Devuelve el año de la fecha introducida en A1
Devuelve el día de la semanafecha introducida en A1

 

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()


3 Función SI CONDICIONAL

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")

Comprobar formato condicional sobre fechas de vencimiento en Excel

Comprobar formato condicional sobre fechas de vencimiento en Excel

 

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)

Comprobar formato condicional sobre fechas de vencimiento en Excel 

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.

 

Comprobar formato condicional sobre fechas de vencimiento en Excel 

5 La función BuscarV

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.

Comprobar formato condicional sobre fechas de vencimiento en Excel

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))

Comprobar formato condicional sobre fechas de vencimiento en Excel

 

 

 

6 Funciones Financieras

  • 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)

PAGO(tasa,nper,va,vf,tipo)

  • Tasa Obligatorio. Es el tipo de interés del préstamo.
  • Núm_per Obligatorio. Es el número total de pagos del préstamo.
  • Va Obligatorio. El valor actual o la cantidad total de una serie de pagos futuros en este momento; también se conoce como el principal.
  • VfOpcional. Es el valor futuro o un saldo en efectivo que se desea lograr después de efectuar el último pago. Si omite el argumento vf, se supone que el valor es 0 (es decir, el valor futuro de un préstamo es 0).
  • TipoOpcional. Es el número 0 (cero) o 1 e indica cuándo vencen los pagos.
  • Comprobar formato condicional sobre fechas de vencimiento en Excel

     

    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])

  • Tasa Obligatorio. Es la tasa de interés por período.
  • Período Obligatorio. Es el período para el que desea calcular el interés; debe estar comprendido entre 1 y el argumento nper.
  • Núm_per Obligatorio. Es el número total de períodos de pago en una anualidad.
  • Va Obligatorio. Es el valor actual o la suma total de una serie de futuros pagos.
  • Vf Obligatorio. Es el valor futuro o saldo en efectivo que desea lograr después de efectuar el último pago. Si omite el argumento vf, se supone que el valor es 0 (por ejemplo, el valor futuro de un préstamo es 0).
  • Tipo    Opcional. Es el número 0 o 1; indica cuándo vencen los pagos. Si omite tipo, se considera que es 0.
  •  

    Comprobar formato condicional sobre fechas de vencimiento en Excel Comprobar formato condicional sobre fechas de vencimiento en Excel

     

     

    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)
    Comprobar formato condicional sobre fechas de vencimiento en Excel

     

     

     

    8 Gráficos combinados

    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.

     

    Comprobar formato condicional sobre fechas de vencimiento en Excel

    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.

    Comprobar formato condicional sobre fechas de vencimiento en Excel

     

    9 Consolidación de datos

     

    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.



    10 Tablas dinámicas

    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

     

    Comprobar formato condicional sobre fechas de vencimiento en Excel 

     

     

    En este Informe se agrupan las categorías y se suman el precio de las ventas
    Comprobar formato condicional sobre fechas de vencimiento en Excel
    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.
    Comprobar formato condicional sobre fechas de vencimiento en Excel
    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.
    Comprobar formato condicional sobre fechas de vencimiento en Excel
    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.
    Comprobar formato condicional sobre fechas de vencimiento en Excel
     
     
     

    Descarga un archivo con ejemplos