Hoja de referencia de fórmulas de Excel: guía intermedia

Así que ha logrado dominar los conceptos básicos de Excel. ¡Felicitaciones para usted! Ahora, ha decidido mejorar las cosas y sumergirse en las cosas más avanzadas, ¿verdad? ¡Bien, está de suerte porque hemos creado una práctica hoja de trucos de Excel intermedio solo para usted!

Esta hoja de trucos intermedios de Microsoft Excel cubre una amplia gama de temas que mejorarán sus habilidades en análisis numérico, procesamiento de texto y lógica compleja de fecha y hora.

Al tener la Hoja de referencia intermedia de Excel como referencia de escritorio, puede profundizar en las fórmulas y funciones más poderosas de Microsoft Excel.

¡Ahora, asegúrese de seguir leyendo, pero también imprima y guarde la hoja de trucos a continuación!

¡Empecemos!

Excel-formulas-cheat-sheet-intermedio-1 Descargar

Tabla de contenido

• Fórmulas matemáticas intermedias
  • Lógica Condicional
• Fórmulas estadísticas intermedias
  • 1. MINA y MAXA
  • 2. CONTARA y CONTAR.SI
• Fórmulas de Excel para análisis financiero
  • 1. Función del PRODUCTO
  • 2. Funciones de COCIENTE y REGISTRO
  • Funciones para escenarios de inversión
• Fórmulas intermedias de fecha y hora de Excel
  • 1. Funciones del mes intermedio
  • 2. Funciones Semana Intermedia
  • 3. Funciones de tiempo intermedio
• Funciones de Excel para álgebra condicional y lógica
  • 1. Función SI
  • 2. Función Y
  • 3. Función O
  • 4. Función NO
• Células de referencia
  • 1. Función INDIRECTA
  • 2. Función ÍNDICE
  • 3. Función de compensación
• Fórmulas de texto intermedio
  • 1. Función UNIÓN DE TEXTO
  • 2. Función REEMPLAZAR
  • 3. Función SUSTITUIR
• Fórmulas de búsqueda intermedia
• Fórmulas para manejar errores de Microsoft Excel
  • 1. Función SI.ERROR
  • 2. Función IFNA
• Accesos directos de fórmulas y funciones
• Formato de celda intermedia
• Pensamientos finales

Fórmulas matemáticas intermedias

Nuestra hoja de trucos para principiantes cubrió las funciones matemáticas para principiantes más comunes, como la función SUMA. Los usuarios intermedios deben familiarizarse con estas funciones más complejas:

• ABS() devuelve el valor absoluto de un número.

• SQRT() devuelve la raíz cuadrada de un número

• RAND() proporciona un número aleatorio entre 0 y 1

Lógica Condicional

Los usuarios intermedios también deben saber cómo realizar cálculos matemáticos basados ​​en múltiples condiciones utilizando estas funciones:

• SUMAR.SI.CONJUNTO(suma_rango, criterio_rango1, criterio1, [….])

• CONTAR.SI.(rango_de_criterios1, criterio1, [rango_de_criterios2, criterio2], …)

• PROMEDIO.S.I.N.(criterios_rango1, criterios1, [criterios_rango2, criterios2], …)

Las funciones evalúan los criterios proporcionados y producen un valor lógico de VERDADERO o FALSO.

Supongamos que tiene los siguientes datos de ventas en las columnas A, B y C:

Para calcular la suma de las ventas de camisetas rojas, utilice la fórmula SUMAR.SI.CONJUNTO:

=SUMAR.SI.CONJUNTO(C2:C6, A2:A6, “Camiseta”, B2:B6, “Rojo”)

Para contar el número de filas con camisetas rojas, usa la fórmula CONTAR.SI:

=CONTAR.SI(C2:C6, A2:A6, “Camiseta”, B2:B6, “Rojo”)

Para calcular las ventas promedio de camisetas rojas, use la fórmula AVERAGEIFS:

=PROMEDIOSIFS(C2:C6, A2:A6, “Camiseta”, B2:B6, “Rojo”)

Fórmulas estadísticas intermedias

Además de funciones básicas como MIN y MAX, Excel tiene una gama más amplia de fórmulas estadísticas para usuarios intermedios. Estos son algunos de los más útiles:

• MINA

• MAXA

• CONTARA

• CONTAR.SI

1. MINA y MAXA

Las funciones MIN y MAX más utilizadas ignoran los valores de texto.

Las funciones alternativas MINA y MAXA consideran tanto texto como números al buscar los valores más altos o más bajos. Los valores de texto se evalúan como si fueran cero.

Las diferencias se muestran en la siguiente imagen. La primera fila evalúa un valor mínimo de 10, mientras que la segunda fila evalúa como 0 debido a la presencia de un valor de texto.

2. CONTARA y CONTAR.SI

La función COUNTA se usa para contar el número de celdas no vacías en un rango.

La función CONTAR.SI es menos específica porque se usa para contar el número de celdas dentro de un rango que cumplen una condición o criterio específico.

También puede usar estas funciones para contar el número de valores distintos en una columna. Este video muestra cómo hacerlo.

Fórmulas de Excel para análisis financiero

Hay varias fórmulas que querrá usar al realizar un análisis financiero, como la previsión de inversiones:

• PRODUCTO(número1, [número2…])

• COCIENTE(numerador, denominador)

• LOG(número, [base])

1. Función del PRODUCTO

Suponga que desea calcular el valor futuro de una inversión inicial de $1000 (celda B1) a una tasa de interés anual del 4 % (celda B2) después de cinco años (celda B3).

Utilice la función PRODUCTO para calcular el valor futuro con esta fórmula:

=B1 * PRODUCTO(1 + B2)^B3

Esta fórmula le devolverá el valor futuro de su inversión.

2. Funciones de COCIENTE y REGISTRO

El siguiente paso es calcular cuántos años tardará una inversión en duplicar su valor a la tasa de interés dada.

Use la función COCIENTE en combinación con la función REGISTRO para calcular esto de la siguiente manera:

=COCIENTE(LOG(2) / LOG(1 + B2), 1)

Esta fórmula devolverá 17 con los datos de la muestra, lo que indica que la inversión tardará 17 años en duplicar su valor a una tasa de interés anual del 4 %.

Esta imagen muestra las fórmulas en una hoja de cálculo de Excel :

Funciones para escenarios de inversión

Estas son algunas funciones que son adecuadas para escenarios financieros específicos. Uno de ellos puede ser exactamente lo que necesitas:

• NPV : la función NPV (Valor actual neto) calculará el valor actual neto de una inversión en función de una serie de flujos de efectivo futuros basados ​​en una tasa de descuento.

• ACCRINT : la función ACCRINT calcula el interés acumulado de un valor que paga intereses periódicos. Esto es útil para determinar el interés devengado por un valor desde la última fecha de pago hasta una fecha de liquidación determinada.

• INTRATE : la función INTRATE calcula la tasa de interés para un valor totalmente invertido.

• PMT : la función PMT calcula el pago total de un título de deuda.

• TIR : la función TIR proporciona la tasa interna de rendimiento.

• RENDIMIENTO : la función RENDIMIENTO proporciona el rendimiento de un valor en función de la tasa de interés, el valor nominal y el vencimiento.

Fórmulas intermedias de fecha y hora de Excel

Las funciones básicas de fecha y hora en Excel incluyen las funciones AHORA y HOY para la fecha actual. Los usuarios intermedios también deben saber cómo extraer componentes de una fecha determinada usando:

• DÍA (fecha)

• MES(fecha)

• AÑO (fecha)

La fórmula =MES(“23 de abril de 2023″) arrojará un resultado de 4 para el cuarto mes. De manera similar, las funciones DÍA y AÑO devolverán 23 y 2023 respectivamente.

1. Funciones del mes intermedio

Los usuarios intermedios a veces se ocupan de sumar o restar meses a una fecha y encontrar el final de un mes.

• EDATE(start_date, number_of_months)

• EOMES(fecha_inicio, numero_de_meses)

Por ejemplo, la fórmula =EDATE(“23 de abril de 2023”, 2) calculará la fecha dos meses después.

La fórmula =EOMES(23 de abril de 2023”, 2) calcula el final del mes dos meses después.

El resultado es "30 de junio de 2023", que tiene en cuenta que solo hay treinta días en junio. Si especifica 3 meses, el resultado será "31 de julio de 2023".

2. Funciones Semana Intermedia

Puede usar una combinación de las funciones SUM y WEEKDAY para contar el número de días hábiles completos dentro de un rango de fechas.

La función WEEKDAY devuelve el día de la semana para una fecha determinada. El segundo parámetro determina el sistema de numeración siendo el predeterminado de domingo a sábado.

Si tiene una serie de fechas en un rango de celdas de B1: B40, use esta fórmula para calcular los días laborables:

=SUMA(–(DÍA DE LA SEMANA(B1:B40,2)>5))

3. Funciones de tiempo intermedio

La función YEARFRAC calcula la fracción de un año entre dos fechas.

La función TIMEVALUE se utiliza para convertir una hora representada como texto en un número decimal que representa la proporción de un día de 24 horas.

El resultado es un número de serie de Excel donde 1 representa un día completo de 24 horas, 0,5 representa 12 horas, 0,25 representa 6 horas y así sucesivamente.

Suponga que tiene una hora en formato de texto "17:45" en la celda A1 y desea convertirla en un número decimal . Esta es la fórmula:

=VALORHORA(A1)

El valor 0,74 representa la proporción del día de 24 horas que ha transcurrido en el tiempo de quince minutos antes de las seis.

Tome para cambiar la celda de tiempo de un formato de tiempo a un formato de texto. Es posible que desee formatear esto como un porcentaje para que sea más claro.

Funciones de Excel para álgebra condicional y lógica

Las funciones condicionales y lógicas son esenciales para la toma de decisiones en Excel. Las siguientes funciones se pueden combinar con las fórmulas de Excel más básicas para una lógica poderosa:

1. SI (condición, valor_si_verdadero, valor_si_falso):

2. Y (condición1, condición2, …)

3. O(condición1, condición2, …)

4. NO(condición1, condición2, …)

La función SI evalúa una condición y devuelve diferentes valores dependiendo de si la condición es verdadera o falsa.

Las funciones AND, OR y NOT comprueban qué condiciones son verdaderas y le permiten tomar decisiones en consecuencia.

Aquí hay algunos ejemplos basados ​​en los datos de ventas que usamos antes.

1. Función SI

Suponga que desea mostrar el texto "Ventas bajas" si las ventas de los artículos son inferiores a 15. Si las ventas son más altas, desea mostrar "Ventas altas" en una nueva columna.

Use esta fórmula y cópiela en las otras filas:

=SI(C2<15, “Bajas Ventas”, “Altas Ventas”)

2. Función Y

Suponga que desea mostrar VERDADERO o FALSO para camisetas con al menos 15 ventas. Usa esta fórmula:

=Y(A3=”Camiseta”, C3>=15)

3. Función O

Suponga que desea probar varias condiciones y devolver VERDADERO si alguna de las condiciones se cumple y FALSO de lo contrario, entonces usaría la función O.

Por ejemplo, si tenemos puntajes de una prueba en las celdas A1 y B1, y queremos saber si alguno de los puntajes está por encima de 80, podríamos usar: =OR( A1>80, B1>80) . Esto devolverá VERDADERO si cualquiera de los puntajes (o ambos) es superior a 80, y FALSO si ambos son 80 o menos.

4. Función NO

Suponga que desea verificar si el artículo no es una sudadera con capucha. Usa esta fórmula:

=NO(A2=”Sudadera con capucha”)

Células de referencia

Un principiante se familiarizará rápidamente con el uso de referencias absolutas y relativas como $A$1 o B2. Los usuarios intermedios deben familiarizarse con el uso de referencias indirectas, índices y compensaciones:

1. INDIRECTO(ref_text)

2. ÍNDICE(rango, núm_fila, núm_columna)

3. DESPLAZAMIENTO(referencia_celda, filas, columnas):

1. Función INDIRECTA

La función INDIRECTA devuelve el valor de la referencia de celda especificada ingresada como texto. Para hacer referencia a la celda B3, use esta fórmula:

= INDIRECTO ("B3")

La ventaja sobre el uso de referencias de celdas simples es que esta función le brinda una referencia dinámica. Esto significa que Excel actualiza automáticamente la referencia cuando cambia la estructura de la hoja de cálculo, por ejemplo, se elimina una fila.

2. Función ÍNDICE

La función ÍNDICE se utiliza para hacer referencia a celdas dentro de un rango específico en función de los números de fila y columna.

Para hacer referencia a la celda B3 en una tabla de seis filas, use esta fórmula:

=ÍNDICE(B1:B6, 3)

3. Función de compensación

La función DESPLAZAMIENTO devuelve una celda o rango que está a un número específico de filas y columnas de distancia de una celda de referencia.

Si desea mostrar el valor de la celda que tiene dos celdas de ancho y una celda hacia abajo desde A1, use esta fórmula:

=DESPLAZAMIENTO(A1, 2, 1)

Aquí están las fórmulas en acción:

Fórmulas de texto intermedio

Los principiantes deben conocer funciones de texto como IZQUIERDA, que extrae uno o más caracteres del lado izquierdo de una cadena. Un usuario avanzado debe estar familiarizado con funciones como estas:

1. TEXTJOIN(delimitador, ignorar_vacío, texto1, [texto2, …])

2. REEMPLAZAR (texto_antiguo, núm_inicio, núm_caracteres, texto_nuevo)

3. SUSTITUIR (texto, texto_antiguo, texto_nuevo, [núm_instancia])

1. Función UNIÓN DE TEXTO

La función TEXTJOIN concatena celdas con el delimitador que proporcione. También puede especificar dónde ignorar las celdas en blanco.

Para producir una lista delimitada por comas de algunos elementos en la columna A, use esta fórmula:

= UNIÓN DE TEXTO (", ", VERDADERO, A2: A4)

2. Función REEMPLAZAR

La función de reemplazo le permite especificar la posición inicial y la longitud de la cadena que desea intercambiar en el destino.

Para reemplazar los primeros dos caracteres de una cadena con "XX", use esta fórmula:

= UNIÓN DE TEXTO (A2, 1, 2, "XX")

3. Función SUSTITUIR

La función de sustitución reemplaza las ocurrencias especificadas de una cadena de texto dentro de otra cadena de texto con una nueva cadena de texto.

Para reemplazar las ocurrencias de la palabra "Jeans" con "Leggings", use esta fórmula:

=SUSTITUIR(A3, “Jeans”, “Leggings”)

Aquí están las fórmulas en acción:

Fórmulas de búsqueda intermedia

Las funciones de búsqueda comunes como BUSCARV y COINCIDIR están cubiertas en la hoja de trucos para principiantes.

Los usuarios intermedios deben familiarizarse con la función de búsqueda ELEGIR:

• ELEGIR(núm_índice, valor1, [valor2, …])

La función ELEGIR devuelve un valor de una lista de valores en función de un número de índice especificado.

Suponga que tiene una columna de tallas de camisetas etiquetadas como 1, 2 o 3. Desea mostrar una categoría de pequeña, mediana y grande respectivamente en la misma fila. Usa esta fórmula:

=ELEGIR(A1, “Pequeño”, “Mediano”, “Grande”)

Fórmulas para manejar errores de Microsoft Excel

La hoja de trucos para principiantes enumeró los mensajes de error típicos que verá cuando trabaje con Excel. Los usuarios intermedios deberían poder usar fórmulas para manejar los errores con gracia.

1. SI.ERROR(valor, si_error)

2. IFNA(valor, valor_si_na)

1. Función SI.ERROR

Los errores de división por cero son comunes, pero la visualización predeterminada de Excel es #DIV/0. no será claro para todos los usuarios.

Use la función IFERROR para reemplazar el error estándar con "No se puede dividir por cero" con esta fórmula:

=SI.ERROR(A1/B1, “No se puede dividir por cero”)

Esta imagen muestra el error sin procesar en la tercera fila y la función IFERROR que maneja la cuarta fila.

2. Función IFNA

La función IFNA en Excel se usa para detectar y manejar errores #N/A.

Otra sección de esta hoja de trucos mostraba la función ELEGIR. Esta función devolverá un error N/A si el número de índice no es un número entero.

Supongamos que se ingresó un tamaño de camiseta como 1.5, y el usuario pensó erróneamente que esto mostraría una categoría entre pequeña y mediana. En su lugar, mostrará un error #N/A .

Para proporcionar un error más útil, use esta fórmula:

=IFNA(ELEGIR(A2, “Bajo”, “Medio”, “Alto”), “Índice no válido”)

Accesos directos de fórmulas y funciones

Existen varios métodos abreviados de teclado que pueden ayudarlo a trabajar de manera más eficiente con su hoja de trabajo.

• F2 : edite la celda activa y coloque el punto de inserción al final del contenido de la celda.

• F9 : Calcula y muestra el resultado de la parte seleccionada de una fórmula.

• Ctrl + Shift + Enter : Ingrese una fórmula de matriz.

• Mayús + F3 : abre el cuadro de diálogo Insertar función.

• Esc : cancela la entrada de una fórmula y vuelve al contenido original de la celda.

Es posible que algunos de estos atajos de Excel no estén disponibles en todos los idiomas o distribuciones de teclado.

Formato de celda intermedia

En el análisis financiero, el formato adecuado para las celdas que contienen números, fechas y monedas es crucial. Para formatear celdas, siga estos pasos:

1. Seleccione las celdas que desea formatear.

2. Haz clic derecho en las celdas seleccionadas y elige "Formatear celdas".

3. Seleccione la categoría apropiada (por ejemplo, Número, Moneda, Fecha) y aplique el formato deseado.

Algunos tipos de formato comunes a considerar en finanzas incluyen:

• Moneda : $1,234.56

• Porcentaje : 12,34%

• Contabilidad : ($1,234.00)

• Fecha : 10-may-2023

Pensamientos finales

A medida que se sienta más cómodo con las diversas fórmulas y funciones destacadas en esta hoja de trucos , se encontrará mejor equipado para manejar tareas y proyectos cada vez más complejos.

La práctica es clave para dominar cualquier habilidad, así que no tengas miedo de experimentar con estas fórmulas en tu trabajo diario. Aportarán su experiencia desde el nivel principiante hasta el nivel intermedio.

Esto es solo una muestra de lo que pueden hacer las funciones intermedias de Excel para simplificar su trabajo, agilizar sus análisis y ayudarlo a descubrir información de sus datos.

La verdadera magia ocurre cuando comienzas a combinar las funciones de nuestra hoja de trucos y las adaptas a tus necesidades únicas. Entonces, no te detengas aquí. Siga explorando, siga experimentando y descubrirá que Excel no es solo una herramienta, ¡es un cambio de juego!

¿Sientes que quieres llevar las cosas al siguiente nivel? Consulte nuestra hoja de referencia de fórmulas avanzadas de Excel.