Excel Basico y Avanzado
Docente: Mauricio Muñoz Univalle
¿Qué es Excel?
Excel es un programa de Hoja de Cálculo, lo cual significa que está diseñado para que puedas realizar un sinfín de operaciones con data organizada en una cuadrícula por medio de fórmulas y funciones avanzadas de Excel.
La cuadrícula de Excel está compuesta por múltiples celdas en las que puedes introducir números, textos, gráficos, tablas y fórmulas de Excel; estas últimas, ayudarán a hacer más eficaz tu trabajo. Por ello, Excel es un programa muy completo que se puede usar en muchos ámbitos, especialmente en finanzas, economía y contabilidad.
Fuente: Microsoft
Antes de revisar las fórmulas de Excel avanzado, debes saber que Excel se caracteriza por 10 elementos básicos:
1. Celdas: un rectángulo en donde se unen una fila y una columna. Aquí se escriben las fórmulas de Excel.
2. Hojas de trabajo: es la cuadrícula de trabajo.
3. Libros de trabajo: es un archivo que puede contener muchas hojas de trabajo.
4. Cintas de opciones: es el menú superior de herramientas.
5. Fórmulas de Excel: son operaciones matemáticas que puedes generar en una celda para presentar resultados.
6. Funciones de Excel: son operaciones predefinidas por Excel.
7. Gráficos estadísticos de Excel: son representaciones visuales para datos estadísticos. Conoce la importancia de los gráficos estadísticos y el poder que puede darte su uso para tu empresa ⚡.
8. Tablas de Excel: es una herramienta para almacenar datos tabulares en una sección de columnas y filas que el programa mantiene independiente de la hoja de trabajo, para el posterior manejo que el usuario puede hacer con las funciones básicas y avanzadas de Excel.
9. Tablas dinámicas: es una herramienta que permite el análisis de grandes cantidades de datos dentro de las hojas de trabajo para luego presentarlos en reportes complejos o tablas sin necesidad de escribir fórmulas avanzadas de Excel.
10. Macros: aplicaciones que ayudan a la automatización de procesos y operaciones en las hojas de trabajo, de forma que se puede programar conectividades en las bases de datos, hacer analíticos, formularios, algoritmos, filtros, búsquedas web, entre otros. Hacen parte de las funciones avanzadas de la hoja de cálculo.
Fuente: Microsoft
La importancia de Excel
¿Por qué es importante Excel? Excel se distingue de muchos programas porque nos permite trabajar con datos informáticos. Esencialmente, es un programa que facilita el trabajo con números, además permite analizarlos con facilidad para generar reportes con herramientas y funciones avanzadas de una hoja de cálculo como los gráficos y las tablas dinámicas.
Por ello, Excel para las empresas llega a ser esencial, ya que permite la organización y el control de datos de una forma óptima. Para una empresa es importante ver procesos y organizar tareas, para lo cual los gráficos, las tablas y las fórmulas de Excel son de gran utilidad.
¿No es suficiente? ¿Quieres saber qué más cosas puedes hacer con Excel? ¡Ahora te decimos las funciones avanzadas de Excel!
¿Para qué sirve Excel?
Las hojas de cálculo de Excel te permiten observar y analizar información. Con esta herramienta podrás:
- Realizar facturas
- Llevar la contabilidad de negocios
- Crear gráficos estadísticos
- Analizar datos estadísticos
- Generar reportes
- Administrar información
- Tabular data (como puede ser esta tabla de ahorros mensual
- Hacer operaciones matemáticas y financieras
- Crear listas
Con Excel podrás almacenar una gran cantidad de datos, manejarlos y actualizarlos. Por lo que saber cómo usar las fórmulas y funciones avanzadas de Excel ayudará al correcto funcionamiento y control de tu empresa.
¿Qué pasaría si las empresas explotaran sus datos y los utilizaran para tomar mejores decisiones? Seguramente entenderían qué están haciendo mal y, enseguida, diseñarían estrategias para tomar mejores decisiones que favorezcan su crecimiento. Esta es la importancia de dominar las fórmulas avanzadas en Excel.
Ahora que ya sabes lo básico, pasaremos a las fórmulas de Excel; razón por la que Excel es el programa favorito de muchas personas.
Fuente: Microsoft.
¿Qué es y para qué sirve una fórmula de Excel?
Una fórmula de Excel es una expresión que se efectúa en una celda con la finalidad de generar una operación, un cálculo o un procesamiento de valores, donde el nuevo valor o resultado será visualizado en dicha celda.
Ejemplo: =MAX (A1:A14) | =(A1 + C2)
Fuente: Microsoft.
Diferencia entre Fórmula y Función
Las funciones son fórmulas matemáticas predefinidas por el mismo programa. Las fórmulas de Excel por lo general son combinadas con las funciones; esto significa que puedes tener fórmulas que contengan más de una función para poder llevar a cabo su objetivo.
Ambas son recursos que facilitan y agilizan nuestro trabajo; asimismo, ambas inician con el símbolo (=). Sin embargo, la diferencia está en la estructura: las fórmulas de Excel se crean añadiendo valores y colocando una operación matemática y las funciones se crean a partir de argumentos utilizando paréntesis.
Fórmula: =A1+B1
Función: =SUMA (A1:B1)
Clase_1: Conociendo la Interfaz de Excel
ACTIVIDAD 1: Estudie el siguiente video "Conociendo la Interfaz de Excel".
Actividad 1: Estudie detenidamente los videos de abajo, tome apuntes en su cuaderno, abra Excel y trate de realizar los ejercicios sugeridos en los videos.
Clase 3. Tablas y Gráficos 1
Actividad_1: Estudie el video de abajo donde muestra la forma de realizar diferentes tipos de gráficas en excel.
Actividad_2:
a. En una hoja nueva de excel digite los datos que aparcen abajo sobre las empresas que más exportaron agricola.
Nota: tenga en cuenta que a la hora de digitar los números de TOTAL EXPORTACIONES escribalos sin: signos de pesos, puntos ni comas.
b. Después de haber digitado los datos realice 3 gráficos con la misma información con los siguientes pasos:
1. De clic en la ficha de menú INSERTAR y luego de clic en GRÁFICOS en el icono COLUMNA y de clic en el primer gráfico de barras que aparece.
2. Realice el mismo procedimiento del paso 1 para insertar un gráfico de LINEA y un gráfico CIRCULAR.
Clase 4: Filtros
Filtrar es una manera conveniente y confiable de localizar y administrar información en una hoja de cálculo. Puedes filtrar información utilizando la característica de auto filtro en Excel 2007 para mostrar únicamente la información que cumple con ciertos criterios. La información filtrada puede ser copiada, manipulada e impresa sin tener que moverla a una nueva hoja de cálculo. Usando el filtro automático, puedes filtrar información seleccionando el criterio deseado de una lista, como por ejemplo, condiciones numéricas o por color. Aquí te explicamos cómo utilizar la característica de filtrado de Excel 2007.
Introducir Fórmulas y Funciones
Introducir fórmulas y Funciones
Una función es una fórmula predefinida por Excel 2007 (o por el usuario) que opera con uno o más valores y devuelve un resultado que aparecerá directamente en la celda o será utilizado para calcular la fórmula que la contiene.
La sintaxis de cualquier función es:
nombre_función(argumento1;argumento2;...;argumentoN)
Siguen las siguientes reglas:
- Si la función va al comienzo de una fórmula debe empezar por el signo =.
- Los argumentos o valores de entrada van siempre entre paréntesis. No dejes espacios antes o después de cada paréntesis.
- Los argumentos pueden ser valores constantes (número o texto), fórmulas o funciones.
- Los argumentos deben de separarse por un punto y coma ;.
Ejemplo: =SUMA(A1:C8)
Tenemos la función SUMA() que devuelve como resultado la suma de sus argumentos. El operador ":" nos identifica un rango de celdas, así A1:C8 indica todas las celdas incluidas entre la celda A1 y
=A1+A2+A3+A4+A5+A6+A7+A8+B1+B2+B3+B4+B5+B6+B7+B8+C1+C2+C3+C4+C5+C6+C7+C8
En este ejemplo se puede apreciar la ventaja de utilizar la función.
Las fórmulas pueden contener más de una función, y pueden aparecer funciones anidadas dentro de la fórmula.
Ejemplo: =SUMA(A1:B4)/SUMA(C1:D4)
Existen muchos tipos de funciones dependiendo del tipo de operación o cálculo que realizan. Así hay funciones matemáticas y trigonométricas, estadísticas, financieras, de texto, de fecha y hora, lógicas, de base de datos, de búsqueda y referencia y de información.
Para introducir una fórmula debe escribirse en una celda cualquiera tal cual introducimos cualquier texto, precedida siempre del signo.
Insertar función con el asistente
Una función como cualquier dato se puede escribir directamente en la celda si conocemos su sintaxis, pero Excel 2007 dispone de una ayuda o asistente para utilizarlas, así nos resultará más fácil trabajar con ellas.
Si queremos introducir una función en una celda:
Situarse en la celda donde queremos introducir la función.
Hacer clic en la pestaña Fórmulas.
Elegir la opción Insertar función.
O bien, hacer clic sobre el botón 
Aparecerá el siguiente cuadro de diálogo Insertar función:
Excel 2007 nos permite buscar la función que necesitamos escribiendo una breve descripción de la función necesitada en el recuadro Buscar una función: y a continuación hacer clic sobre el botón 
Seleccionar una función: las funciones que tienen que ver con la descripción escrita.
Para que la lista de funciones no sea tan extensa podemos seleccionar previamente una categoría del cuadro combinado O seleccionar una categoría:, esto hará que en el cuadro de lista sólo aparezcan las funciones de la categoría elegida y reduzca por lo tanto la lista. Si no estamos muy seguros de la categoría podemos elegir Todas.
En el cuadro de lista Seleccionar una función: hay que elegir la función que deseamos haciendo clic sobre ésta.
Observa como conforme seleccionamos una función, en la parte inferior nos aparecen los distintos argumentos y una breve descripción de ésta. También disponemos de un enlace Ayuda sobre esta función para obtener una descripción más completa de dicha función.
A final, hacer clic sobre el botón Aceptar.
Justo por debajo de la barra de fórmulas aparecerá el cuadro de diálogo Argumentos de función, donde nos pide introducir los argumentos de la función: Este cuadro variará según la función que hayamos elegido, en nuestro caso se eligió la función SUMA ().
En el recuadro Número1 hay que indicar el primer argumento que generalmente será una celda o rango de celdas tipo A1:B4 . Para ello, hacer clic sobre le botón 
En el recuadro Número2 habrá que indicar cuál será el segundo argumento. Sólo en caso de que existiera.
Si introducimos segundo argumento, aparecerá otro recuadro para el tercero, y así sucesivamente.
Cuando tengamos introducidos todos los argumentos, hacer clic sobre el botón Aceptar.
Si por algún motivo insertáramos una fila en medio del rango de una función, Excel expande automáticamente el rango incluyendo así el valor de la celda en el rango. Por ejemplo: Si tenemos en la celda A5 la función =SUMA(A1:A4) e insertamos un fila en la posición 3 la fórmula se expandirá automáticamente cambiando a =SUMA(A1:A5).
En la pestaña Inicio o en la de Fórmulas encontrarás el botón Autosuma
Con este botón tenemos acceso también a otras funciones utilizando la flecha de la derecha del botón.
Al hacer clic sobre ésta aparecerá la lista desplegable de la derecha:
Y podremos utilizar otra función que no sea
Ademas de poder accesar al diálogo de funciones a través de Más Funciones...
Utilizar Expresiones como argumentos de las Funciones
Excel permite que en una función tengamos como argumentos expresiones, por ejemplo la suma de dos celdas (A1+A3). El orden de ejecución de la función será primero resolver las expresiones y después ejecutar la función sobre el resultado de las expresiones.
Por ejemplo, si tenemos la siguiente función=Suma((A1+A3);(A2-A4)) donde:
A1 vale 1
A2 vale 5
A3 vale 2
A4 vale 3
Excel resolverá primero las expresiones (A1+A3) y (A2-A4)por lo que obtendremos los valores3 y 2 respectivamente, después realizará la suma obteniendo así5 como resultado.
Utilizar Funciones como argumentos de las Funciones
Excel también permite que una función se convierta en argumento de otra función, de esta forma podemos realizar operaciones realmente complejas en una simple celda. Por ejemplo =MAX(SUMA(A1:A4);B3) , esta fórmula consta de la combinación de dos funciones, la suma y el valor máximo. Excel realizará primero la suma SUMA(A1:A4) y después calculará el valor máximo entre el resultado de la suma y la celda B3.
Pregunta: El botón sirve para:
1. Descargue el archivo de excel "Introducción a formulas_1".
2. Observe y estudie el video de abajo y realice las actividades de las hojas 1 y 2 y responda en su cuaderno las preguntas de la hoja 3.
3. Envie el archivo de excel con su nombre, grado y los ejercicios al docente: fabiand80@yahoo.com
10 Fórmulas básicas de Excel
1. Fórmula SUMA
Sintaxis: =B4+D6
Esta fórmula de Excel es elemental, nos permite sumar los valores de un grupo de celdas o, incluso, filas y columnas enteras. La suma puede ser efectuada tanto por celdas separadas como por intervalos.
2. Fórmula RESTA
Sintaxis: =A2 - A3
Esta fórmula de Excel es tan esencial como la anterior, su función está en restar valores con las mismas posibilidades que la anterior fórmula.
3. Fórmula MULTIPLICACIÓN
Sintaxis: =A1 * A3 * A5
Esta fórmula de Excel sirve para multiplicar los valores de dos o más celdas. Se marcan las celdas cuyos valores queremos usar y entre ellas se coloca el símbolo de multiplicar (*).
4. Fórmula DIVISIÓN
Sintaxis: =A2 / C2
Esta fórmula de Excel es la complementaria de la anterior, su función es dividir valores. Para ello usaremos el símbolo "/" entre las casillas cuyos valores queremos emplear.
5. Fórmula PROMEDIO
Sintaxis: =PROMEDIO(A1:A365)
Esta fórmula de Excel es para obtener el promedio de un conjunto de datos registrados en la hoja de cálculo.
6. Fórmula MAX | Fórmula MIN
Sintaxis: =MIN(A5,B6,C9)
Sintaxis: =MAX(A5,B6,C9)
Estas fórmulas de Excel se emplean para conocer los valores máximos y mínimos que están en un conjunto de celdas. Para calcularlos usaremos los términos MAX y MIN junto con las celdas requeridas para el análisis.
7. Fórmula CONTAR
Sintaxis: =CONTAR(A1:A10)
Esta fórmula de Excel te permite contar valores (las celdas vacías o con texto son ignoradas). Sirve bastante cuando necesitas contar solo los datos y no el total de celdas en una cuadrícula con datos incompletos.
8. Fórmula ESPACIOS
Sintaxis: =ESPACIOS("texto")
Esta fórmula de Excel sirve como herramienta para establecer espaciados que faciliten la búsqueda y procesamiento de datos en las hojas de cálculo, ya que se encarga de quitar los espacios sobrantes y regularizar los espaciados.
9. Fórmula ALEATORIO.ENTRE
Sintaxis: =ALEATORIO.ENTRE(inferior, superior)
Esta fórmula de Excel sirve para devolver un número entero aleatorio entre los números que especifique. Esta es ideal para llenar una tabla con números distintos de manera rápida en un rango específico.
Si sólo deseas mostrar una previsulización de tu tabla, esta herramienta te quita el trabajo de rellenarla de forma manual.
10. Fórmula NOMPROPIO
Sintaxis: =NOMPROPIO("texto")
Fuente: Microsoft.
Esta fórmula de Excel es muy útil para limpiar tablas de datos, si este se encontraba con nombres en minúsculas o para poner la regla previamente para evitar el proceso anterior cuando rellenen las personas el contenido.
Ejercicio
Descargue el archivo "Operaciones de cálculo en Excel" y realice los ejercicios de las hojas 1 a 5. En cada hoja deberá transcribir los datos de las imágenes y realizar los cálculos respectivos.
Al finalizar guarde el archivo con el nombre "Operaciones_de_cálculo_en_Excel_nombre de estudiante". y enviarlo con el asunto. "Operaciones_nombre_de_estudiante" al docente
Descargar archivo Excel
1. Fórmula SI
Sintaxis: =SI(C2=5, entonces devolver un VERDADERO, en caso contrario devolver un FALSO)
Fuente: Support Microsoft.
Esta fórmula de Excel avanzado te permite realizar comparaciones lógicas entre un valor y un resultado que espera. Por esta razón, la fórmula SI puede tener dos resultados: si la comparación es Verdadera y si la comparación es Falsa. Esta fórmula de Excel permite evaluar texto o valores, asimismo, errores.
También, puedes usar operadores matemáticos y realizar cálculos adicionales según criterios. Además, esta fórmula de Excel se puede combinar con otras fórmulas avanzadas.
No debes olvidar que, si usas texto en las fórmulas, este debe ir entre comillas ("Texto"). Sólo no se aplica a VERDADERO o FALSO, porque Excel entiende de forma automática.
EJEMPLO DE LA FUNCIÓN SI
Probaremos la función SI con el siguiente ejemplo. Tengo una lista de alumnos con sus calificaciones correspondientes en la columna B. Utilizando la función SI desplegaré un mensaje de APROBADO si la calificación del alumno es superior o igual a 60 y un mensaje de REPROBADO si la calificación es menor a 60. La función que utilizaré será la siguiente:
=SI(B2>=60,"APROBADO","REPROBADO")
Observa el resultado al aplicar esta fórmula en todas las celdas de la columna C.
2. Fórmula SI.CONJUNTO
Sintaxis: =SI.CONJUNTO(Prueba_lógica1; Valor_si_verdadero1; Prueba_lógica2; Valor_si_verdadero2)
Función: Support Microsoft.
Esta fórmula de Excel avanzado sirve para comprobar si se cumplen una o varias condiciones y devolver un valor que corresponda a la primera condición VERDADERO. Esta fórmula de Excel te permite añadir variables más complejas a otras fórmulas para, por ejemplo, realizar una operación siempre que un conjunto de pruebas se cumplan.
Ejercicio Contar , sumar Si
3. Fórmula SUMAR.SI
Sintaxis: =SUMAR.SI(rango, criterio, [rango_suma])
Fuente: Excel Total.
Estas fórmulas de Excel avanzado nos permiten realizar una suma o un promedio solamente para los valores que cumplen una condición. Las condiciones que se pueden ingresar siguen el mismo formato que las condiciones utilizadas en CONTAR.SI y el SI.
Ejemplo
Copie los datos de ejemplo en la tabla siguiente y péguelos en la celda A1 de una hoja de cálculo nueva de Excel. Para que las fórmulas muestren los resultados, selecciónelas, presione F2 y luego ENTRAR. Si lo necesita, puede ajustar los anchos de la columna para ver todos los datos.
Categoría | Alimento | Ventas |
|---|---|---|
Verdura | Tomates | 2.300 € |
Verdura | Apio | 5.500 € |
Fruta | Naranjas | 800 € |
Mantequilla | 400 € | |
Verdura | Zanahorias | 4.200 € |
Fruta | Manzanas | 1.200 € |
Fórmula | Descripción | Resultado |
=SUMAR.SI(A2:A7;"Frutas";C2:C7) | Suma de las ventas de todos los alimentos en la categoría "Frutas". | 2000 € |
=SUMAR.SI(A2:A7;"Verduras";C2:C7) | Suma de las ventas de todos los alimentos en la categoría "Verduras". | 12.000 € |
=SUMAR.SI(B2:B7;"*s";C2:C7) | Suma de las ventas de todos los alimentos que terminan en "s" (Tomates, Naranjas y Manzanas). | 4.300 € |
=SUMAR.SI(A2:A7;"";C2:C7) | Suma de las ventas de todos los alimentos que no tienen una categoría especificada. | 400 € |
4. Fórmula CONTAR.SI
Sintaxis: =CONTAR.SI(rango;"criterio")
Función: Excel Total.
Esta fórmula de Excel avanzado se usa para sumar los valores de un intervalo que cumplen los criterios especificados. También, puedes aplicar los criterios de un rango y sumar los valores correspondientes de otro rango. Usando esta fórmula, Excel contará las celdas que ofrecen la condición que hayamos marcado.
Un ejemplo de función avanzada de Excel sería el nº de casos en un tiempo determinado, el nº de depósitos de una persona concreta o el número de veces que un nombre aparece en una lista, etc.
La función CONTAR.SI devuelve resultados incorrectos cuando se usa para comparar cadenas de más de 255 caracteres o con la cadena #VALUE!.
CONTAR.SI
La función CONTAR.SI en Excel nos da la oportunidad de contar el número de celdas de un rango que cumplan con un criterio establecido. Solamente cuando el valor de dichas celdas cumple la condición es considerada dentro de la cuenta.
Sintaxis de la función CONTAR.SI
Solamente existen dos argumentos para la función CONTAR.SI que son lo siguientes:
El criterio puede ser un número o una expresión que indicará la condición a cumplir, por ejemplo: 55, “>26”, “VERDE”. El criterio no hará diferencia entre mayúsculas y minúsculas. La función CONTAR.SI solamente permite especificar una sola condición, en caso de necesitar aplicar varias condiciones se puede utilizar la función
CONTAR.SI.CONJUNTO.
Ejemplo de la función CONTAR.SI
Tengo una lista de artículos y cada uno tiene asociado su color. Ahora necesito contar el total de artículos que son de color azul y para ello utilizaré la siguiente fórmula:
=CONTAR.SI(B2:B11, "azul")
Al aplicar esta fórmula a los datos de la columna B obtengo la cuenta correcta:
Puedes observar que el segundo argumento de la función CONTAR.SI está todo en minúsculas mientras que las celdas de la columna B tienen la primera letra en mayúsculas, sin embargo el valor “Azul” es considerado dentro de la cuenta por lo que comprobamos que la condición no es sensible a mayúsculas y minúsculas.
Recuerda que la función CONTAR.SI nos permitirá contar el número de celdas dentro de un rango que cumplan con el criterio que se haya especificado en el segundo argumento de la función.
5. Fórmula BUSCARV
Sintaxis: =BUSCARV (valor de búsqueda, rango que contiene el valor de búsqueda, el número de columna del rango que contiene el valor devuelto, la coincidencia aproximada (TRUE) o la coincidencia exacta (FALSO)).
Fuente: Support Microsoft.
Esta fórmula de Excel avanzado permite buscar elementos en una tabla o en un rango por fila, y retornar el valor en la misma fila para otra columna diferente (el valor de búsqueda debe estar siempre en la primera columna del rango para que la fórmula de Excel funcione correctamente).
Puedes especificar VERDADERO si deseas una coincidencia aproximada o FALSO si deseas una coincidencia exacta del valor devuelto. Si no especificas nada, el valor predeterminado de esta fórmula avanzada de Excel siempre será VERDADERO o la coincidencia aproximada.
Sintaxis: =CONCATENAR(“A1”, “espacio“, “B1”)
Fuente: Excel Total.
Esta fórmula de Excel avanzado nos permite unir dos o más cadenas de texto en una sola celda. Por ejemplo, si tenemos una columna “nombres” y otra “apellidos”, y queremos crear una tercera columna llamada “nombre completo”, esta fórmula de Excel te ahorra el trabajo manual, asimismo, te permite colocar un espacio entremedio.
7. Fórmula IZQUIERDA | Fórmula DERECHA
Sintaxis: =IZQUIERDA("texto",[núm_de_caracteres])
Sintaxis: =DERECHA(texto,[núm_de_caracteres])
IZQUIERDA devuelve el primer carácter o caracteres de una cadena de texto, según el número de caracteres que especifique el usuario.
DERECHA devuelve el último carácter o caracteres de una cadena de texto, según el número de caracteres especificado.
Fuente: Capacitación virtual.
Estas fórmulas de Excel avanzado son muy útiles para crear códigos en las bases de datos. Puedes usarlo para cortar la parte derecha o izquierda de una celda. Si combinas esto con Concatenar, puedes crear códigos para usuarios de manera rápida.
8. Fórmula ELEGIR
Sintaxis: =ELEGIR(núm_índice; valor1; [valor2]; ...)
Fuente: Excel Total.
Esta fórmula de Excel avanzado sirve para el análisis de escenarios financieros. Usa el argumento núm_índice para devolver un valor de una lista de argumentos de valores. Debes seleccionar uno de los 254 valores posibles a partir del rango del argumento índice. Por ejemplo, si valor1 a valor7 son los días de la semana, ELEGIR devuelve uno de los días cuando se usa un número entre 1 y 7 como argumento núm_índice.
Fuente: Microsoft.
9. Fórmula PARTIDO DE ÍNDICE
Sintaxis: =ÍNDICE(C3: E9, PARTIDO (B13, C3: C9,0), PARTIDO (B14, C3: E3,0))
Fuente: Excel Total.
Esta fórmula de Excel avanzado es una excelente función para finanzas más especializada que las fórmulas VLOOKUP o HLOOKUP. ÍNDICE devuelve el valor de una celda en una tabla basada en la columna y el número de fila. PARTIDO devuelve la posición de una celda en una fila o columna.
10. Fórmula SI.ERROR
Sintaxis: =SI.ERROR(valor;valor_si_error)
Fuente: Aglaia.
Esta fórmula de Excel avanzado sirve para interceptar y controlar errores en una fórmula. Esta fórmula de Excel devuelve un valor que se especifica si una fórmula lo evalúa como un error; en caso contrario, devuelve el resultado de la fórmula.
Esta fórmula de Excel nos puede ser muy útil para limpiar resultados que dan error, ya que podemos sustituir estos valores por un texto o número de nuestra elección.
Se evalúan los siguientes tipos de error: #N/A, #¡VALOR!, #¡REF!, #¡DIV/0!, #¡NUM!, #¿NOMBRE? o #¡NULO!
11. Fórmula EXTRAE
Sintaxis: =EXTRAE("texto", posición_inicial, núm_de_caracteres)
Fuente: Excel Total.
Esta fórmula de Excel avanzado sirve para devolver un número específico de caracteres de una cadena de texto, comenzando en la posición y en función del número de caracteres que especifique. Esta fórmula de Excel es muy útil cuando la extracción de datos que tenemos está «sucia» y necesitamos extraer una parte del texto de las celdas.
EXTRAE siempre cuenta cada carácter como 1, ya sea de un byte o de doble byte e independientemente de la configuración predeterminada de idioma.
12. Fórmula COINCIDIR
Sintaxis: =COINCIDIR (valor_buscado,matriz_buscada, [tipo_de_coincidencia])
Fuente: Excel Total.
Esta es una de las fórmulas de Excel avanzado que sirve de búsqueda y referencia. Con esta puedes buscar un elemento determinado en un intervalo de celdas y después devolver la posición relativa de dicho elemento en el rango.
13. Fórmula ESERROR
Sintaxis: =ESERROR (valor que se desea evaluar)
Fuente: Excel Total.
La función ESERROR sirve para comprobar si un valor —una celda o una fórmula— es un error y nos devuelve el valor VERDADERO si existe un error, o FALSO si la fórmula o celda tiene un valor correcto.
14. Fórmula PROMEDIO.SI
Sintaxis: =PROMEDIO.SI (rango; criterios; [rango_promedio])
Fuente: Capacitación virtual.
Esta es una fórmula avanzada de Excel para fines estadísticos. La fórmula PROMEDIO SI en Excel sirve para devolver el promedio de todas las celdas de un rango que cumplen unos criterios determinados.
La fórmula PROMEDIO SI nos puede ser muy útil para sacar diferentes promedios de una base de datos en función de variables que establezcamos. Por ejemplo, un promedio por cliente, por ticket medio de venta, por producto, etc.
15. Fórmula Y
Sintaxis: =Y(valor_lógico1, [valor_lógico2], ...)
Fuente: Support Microsoft.
Esta fórmula de Excel permite unir dos o más pruebas lógicas. Si las pruebas lógicas tienen como resultado el valor VERDADERO entonces la función Y devolverá VERDADERO. En caso de que una sola de las pruebas lógicas resulte FALSO entonces la función devolverá FALSO.
Ahora que ya sabes varias de las fórmulas de Excel y Excel avanzado podrás tener más tiempo para dedicarte a otras cosas, recuperarás tus horas de sueño y tus trabajos serán todavía impecables ¡Suerte!
Todos sabemos que los intereses son el índice que nos permite medir la rentabilidad de los ahorros e inversiones o el coste de un crédito, haciendo referencia a un porcentaje del total del crédito. Pero el problema llega cuando tenemos que diferenciar entre interés simple e interés compuesto, términos cuya frontera no siempre está clara. Hablamos de tasa de interés simple cuando el interés obtenido al vencimiento no se suma al capital para generar nuevos intereses, algo que afortunadamente podemos calcular a través de una hoja de Excel. Los diferentes tipos de intereses con los que nos encontramos son índices que nos ayudan a medir la rentabilidad de un proyecto, préstamo o inversión.
En particular, el interés simple es aquel calculado en base al capital inicial, lo que quiere decir que a lo largo del tiempo específico que dure la inversión el interés no variará, siempre será el mismo. De este modo, el cálculo del interés simple debe valorar la cuantía del capital inicial, el tiempo y el tipo de interés que cada entidad establezca, aunque normalmente siempre hablaremos de operaciones a corto plazo, por ejemplo medio año.Como observamos, el interés simple es empleado mediante la capitalización simple con la intención de volver a calcular el capital en un momento determinado del futuro, especialmente a corto plazo.
Cómo calcular el interés simple en Excel
Para poder calcular el interés simple (representado como I), tenemos que conocer una serie de datos sobre la inversión o el crédito con el que trabajamos. Concretamente, son:
- el capital (P)
- la tasa de interés (i)
- el plazo que se maneja (n).
Si tenemos estos tres datos, podremos aplicar la fórmula I = P * i * n que nos permite calcular el interés simple. Así, lo primero que haremos será plasmar estos tres valores en una pequeña tabla de Excel, y a partir de ahí podremos introducir la fórmula adecuada para calcular el interés simple.
Ejemplo de cómo calcular el interés simple con Excel
De este modo, si tenemos el valor correspondiente al capital en la celda A2, la tasa de interés en B2 y el plazo en C2, la fórmula que introduciremos en una celda en blanco sería =A2*B2*C2. Imagina que hablamos de un préstamo de 50.000€, el valor que tenemos en A2, con una tasa de interés del 18%, que sería el valor a introducir en la celda B2. Tenemos seis meses para devolverlo, introduciendo el valor 3 en la celda D2. Así, al utilizar esa fórmula tendríamos I=50.000*0,18*(6/12), y el interés simple serían 4.500€
Así, deberíamos devolver 54.400€ a la entidad que nos ha ofrecido el préstamo. Conviene recalcar que el interés simple se utiliza en préstamos a corto plazo o inversiones con plazos inferiores a un año, y nos permite conocer el interés a pagar o cobrar al final de un periodo concreto, que en el caso del ejemplo son 6 meses. Pero este no es el único caso en el que nos puede interesar calcular el interés simple.
¿Y el monto del interés simple? Así se calcula en Excel
Otro posible escenario sería determinar cuánto hay que pagar o cuánto vamos a cobrar al finalizar un periodo de tiempo determinado, el monto. En este caso, para determinar el monto, que se define con el símbolo S, hace falta conocer el capital (P) y el plazo que tenemos (n), pero no utilizamos la tasa de interés. Así, la fórmula para calcular el monto en Excel es S = P (1+n). De nuevo, hacemos una tabla en Excel donde se recojan estos datos relacionados con la operación y aplicamos la fórmula adecuada para completar el cálculo del monto.
Ejemplo 1
Como en el caso anterior, tenemos el capital total en A2, un valor de 50.000€; mientras que en B2 aparece el plazo, que en esta ocasión es de 3 mesas (0,25 años). Aplicamos la fórmula =A2*(1+B2/12), que traducida al lenguaje numérico sería Monto = 50.000(1+0,25) y obtenemos un total que en esta ocasión sería de 62.500€. Si a eso le restamos la cantidad inicial, vemos que en este caso el interés a pagar es de 12.500€.
Ejemplo 2
Cuál es la tasa de interés mensual que se invirtió en un capital de $2000 colocado a interés simple el 20/4/11 cuyo monto al 20/06/11 fue de $2500
4. Y nos da el valor de la tasa de interés mensual.
- Utilizo la función de tasa.int porque nos esta pidiendo la tasa de interés
- Reemplazo la formula de la función porque me da todos los datos para sacar la tasa.
- Obtengo el resultado
- Aplico la función int,acum porque esta es la función devuelve el interés acumulado de los pagos de interés periódico.
- Porque nos dan fecha de la inversión y del vencimiento
- Reemplazo la función y obtengo el interés simple.
DESCARGA DE LOS EJERCICIOS COMPLETOS
17. EL INTERÉS COMPUESTO El interés compuesto se refiere a aquellos intereses que son depositados en una cuenta bancaria donde se encuentra el capital y que a partir de ese momento también generarán intereses es decir,los intereses se reinvierten.
EJEMPLO DE INTERÉS COMPUESTO
Entenderemos mejor cómo calcular el interés compuesto haciendo un ejemplo. Supongamos que tengo $1,000 en una cuenta de inversión que paga 10% de interés anual. ¿Cuánto dinero tendré después de 3 años?
Algunos podrían pensar que obtendrán $300 de interés ya que el 10% de $1,000 son $100 y después de 3 años obtendrán dicha cantidad, pero eso no es correcto especialmente si los intereses se reinvierten. Observa la siguiente tabla de cálculo donde se muestran los intereses generados cada año después de sumar los intereses del año anterior:
Al cabo de 3 años nuestro capital ha generado $331 de interés compuesto, es decir, hemos cobrado interés de los intereses que se han reinvertido. Ya que hemos comprendido el concepto de interés compuesto debes saber que existe una fórmula para realizar el cálculo la cual revisaremos a continuación.
FÓRMULA PARA CALCULAR EL INTERÉS COMPUESTO
La fórmula para calcular el interés compuesto es la siguiente:
- Cf = Capital final
- Ci = Capital inicial
- i = Tasa de interés
- n = Número de períodos
Para comprobar esta fórmula sustituiré los valores de nuestro ejemplo anterior y obtendré el capital final de la siguiente manera:
Como puedes observar, hemos obtenido el mismo resultado que cuando hicimos el cálculo año por año. Para realizar el cálculo en Excel podríamos implementar esta misma fórmula de la siguiente manera:
La fórmula de Excel utiliza la función POTENCIA y hace operaciones aritméticas simples como la suma y la multiplicación. El resultado es correcto, pero existe otra alternativa para calcular el interés compuesto en Excel.
EL INTERÉS COMPUESTO EN EXCEL
Además de implementar la fórmula descrita anteriormente, podemos utilizar la función VF que nos ayudará a calcular el valor futuro de una inversión. A continuación podrás observar el cálculo de interés compuesto utilizando la función VF y los datos del ejemplo anterior:
Los argumentos de la función VF son los siguientes:
- tasa: Tasa de interés por período que en este ejemplo se encuentra en la celda B2.
- núm_per: Número períodos a calcular que están indicados en la celda B3.
- pago: Pago realizado en cada período, que no aplica para nuestro ejemplo ya que no haremos pagos adicionales al finalizar cada período y por eso he colocado el valor cero.
- va: Valor actual (inicial) de la inversión que en el ejemplo se encuentra en la celda B1 y que debe indicarse como un valor negativo.
- tipo: Indica el vencimiento de los pagos y debe ser 0 (u omitido) si el pago se realiza al final del período o 1 si se hace al inicio del período. En la fórmula del ejemplo he omitido este argumento.
De esta manera la función VF realiza el cálculo de interés compuesto en Excel con solo especificar los argumentos correctos. Es así como tenemos dos alternativas para realizar este tipo de cálculo en Excel, ya sea implementando la fórmula mostrada en la sección anterior o utilizando la función VF.
Descarga IS & IC18. VALOR ACTUAL Y VALOR FUTURO
Una de las funciones financieras más utilizadas en Excel son Valor actual (VA) y Valor futuro (VF). Ambas funciones utilizan argumentos similares por lo que solamente es necesario entender la terminología para poder utilizarlas adecuadamente.
LAS FUNCIONES VA Y VF EN EXCEL
Los argumentos de las funciones son los siguientes:
- Períodos. El número total de períodos. Por ejemplo, si se hace un pago mensual durante un año el total de períodos será de 12. Si se hace un pago mensual durante 3 años tendremos un total de 36 períodos.
- Pago. El pago hecho en cada período.
- Tasa. La tasa de interés constante para cada período.
Cuando utilizamos funciones financieras debemos recordar que los valores pueden ser positivos o negativos dependiendo de si se está recibiendo dinero o si se está pagando. Otra cosa importante a resaltar es que generalmente encontramos las tasas de interés expresadas de manera anual, por lo que si estamos haciendo un cálculo mensual deberemos dividir la tasa de interés entre 12.
La sintaxis básica para ambas funciones es la siguiente:
VA(Tasa, Períodos, Pago)
VF(Tasa, Períodos, Pago)
Observa los siguientes cálculos de funciones:
En este ejemplo he utilizado los mismos parámetros tanto para la función
VA como para la función VF. El valor futuro nos ayuda a saber la cantidad de dinero que tendremos en una fecha futura si se comienza a invertir desde hoy la cantidad especificada con una tasa de interés fija. El valor actual es el valor que tiene el día de hoy una inversión que iremos haciendo en pagos futuros a una tasa de interés fija.
18. MACROS
Introducción a las MACROS EN EXCEL
Aprende como funcionan, escribe y programa tu primera macro.
ACTIVIDAD_1:
Investigue las siguientes preguntas y respondalas en el cuaderno:
1. ¿Qué es una macro en EXCEL?
2. ¿Qué es y para qué sirve Visual Basic?
3. De 4 ejemplos de Macros en Excel
4. De 4 ventajas de las Macros en Excel
ACTIVIDAD_2:
Realice el siguiente taller en parejas: Vea el vídeo y trate de realizarlo en Excel, al finalizar compruebe el funcionamiento de la Macro realizada.
a. Descargue el archivo de abajo "Formato ejercicio MACROS excel.xlsx
b. Realice el ejercicio con base al video de abajo
Comentarios
Publicar un comentario