Excel

Fórmulas y funciones de Excel

Excel Formulas Functions

Las fórmulas y funciones son el pan y la mantequilla de Excel. Conducen casi todo lo interesante y útil que harás en una hoja de cálculo. Este artículo presenta los conceptos básicos que necesita saber para dominar las fórmulas en Excel. Más ejemplos aquí .

¿Qué es una fórmula?

Una fórmula en Excel es una expresión que devuelve un resultado específico. Por ejemplo:



|_+_|

Ejemplo de fórmula básica - 1 + 3 = 3



|_+_|

Ejemplo de fórmula básica - 6/3 = 2

Nota: todas las fórmulas en Excel deben comenzar con un signo igual (=).



Referencias de celda

En los ejemplos anteriores, los valores están 'codificados'. Eso significa que los resultados no cambiarán a menos que vuelva a editar la fórmula y cambie un valor manualmente. Generalmente, esto se considera de mala forma, porque oculta información y dificulta el mantenimiento de una hoja de cálculo.

En su lugar, use referencias de celda para que los valores se puedan cambiar en cualquier momento. En la siguiente pantalla, C1 contiene la siguiente fórmula:

|_+_|

Fórmula con referencias de celda



Tenga en cuenta que, como estamos usando referencias de celda para A1, A2 y A3, estos valores se pueden cambiar en cualquier momento y C1 seguirá mostrando un resultado preciso.

Todas las fórmulas devuelven un resultado

Todas las fórmulas de Excel devuelven un resultado, incluso cuando el resultado es un error. A continuación, se utiliza una fórmula para calcular el cambio porcentual. La fórmula devuelve un resultado correcto en D2 y D3, ¡pero devuelve un # DIV / 0! error en D4, porque B4 está vacío:

El resultado de la fórmula puede ser un error

Hay diferentes formas de manejar los errores. En este caso, puede proporcionar el valor que falta en B4 o 'detectar' el error con el Función IFERROR y mostrar un mensaje más amigable (o nada en absoluto).

Copiar y pegar fórmulas

La belleza de las referencias de celda es que se actualizan automáticamente cuando se copia una fórmula en una nueva ubicación. Esto significa que no es necesario que ingrese la misma fórmula básica una y otra vez. En la siguiente pantalla, la fórmula en E1 se ha copiado al portapapeles con Control + C:

Fórmula en E1 copiada al portapapeles

Abajo: fórmula pegada en la celda E2 con Control + V. Observe que las referencias de celda han cambiado:

Fórmula en E1 pegada a E2

Misma fórmula pegada en E3. Las direcciones de celda se actualizan nuevamente:

Fórmula en E1 pegada a E3

Referencias relativas y absolutas

Las referencias de celda anteriores se llaman relativo referencias. Esto significa que la referencia es relativa a la celda en la que vive. La fórmula en E1 anterior es:

|_+_|

Literalmente, esto significa 'celda 3 columnas a la izquierda' + 'celda 2 columnas a la izquierda' + 'celda 1 columna a la izquierda'. Por eso, cuando la fórmula se copia en la celda E2, continúa funcionando de la misma manera.

Las referencias relativas son extremadamente útiles, pero hay ocasiones en las que no desea que cambie una referencia de celda. Una referencia de celda que no cambia cuando se copia se denomina referencia absoluta . Para hacer una referencia absoluta, use el símbolo de dólar ($):

|_+_|

Por ejemplo, en la siguiente pantalla, queremos multiplicar cada valor en la columna D por 10, que se ingresa en A1. Al usar una referencia absoluta para A1, 'bloqueamos' esa referencia para que no cambie cuando la fórmula se copia en E2 y E3:

Ejemplo de referencia absoluta

Aquí están las fórmulas finales en E1, E2 y E3:

|_+_|

Observe la referencia a las actualizaciones de D1 cuando se copia la fórmula, pero la referencia a A1 nunca cambia. Ahora podemos cambiar fácilmente el valor en A1 y volver a calcular las tres fórmulas. A continuación, el valor en A1 ha cambiado de 10 a 12:

Ejemplo de referencia absoluta después de que se cambia el valor en A1

Este sencillo ejemplo también muestra por qué no tiene sentido codificar valores en una fórmula. Al almacenar el valor en A1 en un lugar y hacer referencia a A1 con un referencia absoluta , el valor se puede cambiar en cualquier momento y todas las fórmulas asociadas se actualizarán instantáneamente.

Consejo: puede alternar entre la sintaxis relativa y absoluta con el Tecla F4 .

Cómo ingresar una fórmula

Para ingresar una fórmula:

  1. Seleccionar una celda
  2. Ingrese un signo igual (=)
  3. Escriba la fórmula y presione Intro.

En lugar de escribir referencias de celda, puede señalar y hacer clic, como se muestra a continuación. Las referencias de las notas están codificadas por colores:

Introducción de una fórmula con referencias de apuntar y hacer clic

Todas las fórmulas en Excel deben comenzar con un signo igual (=). Sin signo igual, sin fórmula:

Olvidé ingresar un signo igual significa que no hay fórmula, solo texto

Cómo cambiar una fórmula

Para editar una fórmula, tiene 3 opciones:

  1. Seleccione la celda, edite en el barra de formulas
  2. Haga doble clic en la celda, edite directamente
  3. Seleccione la celda, presione F2 , editar directamente

Independientemente de la opción que utilice, presione Entrar para confirmar los cambios cuando haya terminado. Si desea cancelar y dejar la fórmula sin cambios, haga clic en la tecla Escape.

Video: 20 consejos para ingresar fórmulas

¿Qué es una función?

Trabajando en Excel, escuchará las palabras 'fórmula' y 'función' que se usan con frecuencia, a veces de manera intercambiable. Están estrechamente relacionados, pero no son exactamente iguales. Técnicamente, una fórmula es alguna expresión que comienza con un signo igual (=).

Una función, por otro lado, es una fórmula con un nombre y un propósito especiales. En la mayoría de los casos, las funciones tienen nombres que reflejan su uso previsto. Por ejemplo, probablemente conozca Función SUM ya, que devuelve la suma de las referencias dadas:

|_+_|

los Función PROMEDIO , como era de esperar, devuelve el promedio de referencias dadas:

|_+_|

Y las funciones MIN y MAX devuelven valores mínimos y máximos, respectivamente:

|_+_|

Excel contiene cientos de funciones específicas . Para comenzar, vea 101 funciones clave de Excel .

Argumentos de función

La mayoría de las funciones requieren entradas para devolver un resultado. Estas entradas se denominan 'argumentos'. Los argumentos de una función aparecen después del nombre de la función, entre paréntesis, separados por comas. Todas las funciones requieren un paréntesis de apertura y cierre coincidente (). El patrón se ve así:

|_+_|

Por ejemplo, el Función COUNTIF cuenta las celdas que cumplen los criterios y toma dos argumentos, abarcar y criterios :

|_+_|

En la siguiente pantalla, el rango es A1: A5 y el criterio es 'rojo'. La fórmula en C1 es:

|_+_|

COUNTIF requiere dos argumentos, rango y criterio

Video: Cómo utilizar la función CONTAR.SI

No se requieren todos los argumentos. Los argumentos que se muestran entre corchetes son opcionales. Por ejemplo, el Función YEARFRAC devuelve una fracción de años entre una fecha de inicio y una fecha de finalización y toma 3 argumentos:

|_+_|

La fecha de inicio y la fecha de finalización son argumentos obligatorios, la base es un argumento opcional. Vea a continuación un ejemplo de cómo usar YEARFRAC para calcular la edad actual según la fecha de nacimiento.

Cómo ingresar a una función

Si conoce el nombre de la función, simplemente comience a escribir. Estos son los pasos:

1. Ingrese el signo igual (=) y comience a escribir. Excel mostrará una lista de funciones coincidentes según escribe:

A medida que escribe, Excel mostrará funciones coincidentes

Cuando vea la función que desea en la lista, use las teclas de flecha para seleccionar (o simplemente siga escribiendo).

2. Escriba la tecla Tab para aceptar una función. Excel completará la función:

Presione Tab para ingresar a la función seleccionada

3. Complete los argumentos requeridos:

Ingrese los argumentos requeridos

4. Presione Enter para confirmar la fórmula:

Presione Enter para confirmar e ingresar a la función

Combinar funciones (anidamiento)

Muchas fórmulas de Excel usan más de una función, y las funciones pueden ser ' anidado 'el uno dentro del otro. Por ejemplo, a continuación tenemos una fecha de nacimiento en B1 y queremos calcular la edad actual en B2:

Necesita una fórmula para calcular la edad actual en B2

los Función YEARFRAC calculará años con una fecha de inicio y una fecha de finalización:

YEARFRAC calculará años con una fecha de inicio y una fecha de finalización

Podemos usar B1 para la fecha de inicio, luego usar el Función HOY para proporcionar la fecha de finalización:

B1 para la fecha de inicio, la función HOY para proporcionar la fecha de finalización

Cuando presionamos Enter para confirmar, obtenemos la edad actual según la fecha de hoy:

|_+_|

Funciones YEARFRAC y TODAY para calcular la edad actual

Observe que estamos usando la función HOY para alimentar una fecha de finalización a la función YEARFRAC. En otras palabras, la función HOY se puede anidar dentro de la función YEARFRAC para proporcionar el argumento de fecha de finalización. Podemos llevar la fórmula un paso más allá y usar la Función INT para cortar el valor decimal:

|_+_|

YEARFRAC y HOY dentro de INT

Aquí, la fórmula YEARFRAC original devuelve 20,4 a la función INT, y la función INT devuelve un resultado final de 20.

Notas:

  1. La fecha actual en las imágenes de arriba es el 22 de febrero de 2019.
  2. Anidado SI funciones son un ejemplo clásico de funciones de anidamiento .
  3. los Función HOY es una función de Excel poco común que no requiere argumentos.

Conclusión clave: la salida de cualquier fórmula o función se puede introducir directamente en otra fórmula o función.

Operadores matemáticos

La siguiente tabla muestra los operadores matemáticos estándar disponibles en Excel:

Símbolo Operación Ejemplo
+ Adición =2+3=5
- Sustracción =9-2=7
* Multiplicación =6*7=42
/ División =9/3=3
^ Exponenciación =4^2=16
() Paréntesis =(2+4)/3=2

Operadores logicos

Los operadores lógicos brindan soporte para comparaciones como 'mayor que', 'menor que', etc. Los operadores lógicos disponibles en Excel se muestran en la siguiente tabla:

Operador Sentido Ejemplo
= Igual a = A1 = 10
No igual a = A110
> Mas grande que = A1> 100
< Menos que = A1<100
>= Mayor qué o igual a = A1> = 75
<= Menos que o igual a = A1<=0

Video: Cómo construir fórmulas lógicas

Orden de operaciones

Al resolver una fórmula, Excel sigue una secuencia llamada 'orden de operaciones'. Primero, se evalúan las expresiones entre paréntesis. A continuación, Excel resolverá los exponentes. Después de los exponentes, Excel realizará la multiplicación y la división, luego la suma y la resta. Si la fórmula involucra concatenación , esto sucederá después de las operaciones matemáticas estándar. Finalmente, Excel evaluará operadores logicos , si está presente.

  1. Paréntesis
  2. Exponentes
  3. Multiplicación y división
  4. Adición y sustracción
  5. Concatenación
  6. Operadores logicos

Consejo: puede utilizar el Evaluar característica para ver cómo Excel resuelve fórmulas paso a paso.

Convertir fórmulas en valores

A veces desea deshacerse de fórmulas y dejar solo valores en su lugar. La forma más fácil de hacer esto en Excel es copiar la fórmula, luego pegar, usando Pegado especial> Valores. Esto sobrescribe las fórmulas con los valores que devuelven. Puedes usar un atajo de teclado para pegar valores, o use el menú Pegar en la pestaña Inicio en la cinta.

Video: Pegar atajos especiales

Excel agregar todos los números en la columna

¿Que sigue?

A continuación, encontrará guías que le ayudarán a aprender más sobre las fórmulas y funciones de Excel. También ofrecemos capacitación en video en línea .

Autor Dave Bruns


^