Muchos economistas o financieros dedican una parte importante del tiempo a trabajar en una computadora con la aplicación de oficina MS Excel. Este programa tiene un número considerable de funciones destinadas a crear informes, análisis de datos, planes de información, cálculos matemáticos y mucho más. El conocimiento de las fórmulas de Excel más importantes y una combinación de varias funciones facilita enormemente la solución de problemas prácticos y reduce el tiempo y el esfuerzo dedicado a ello.
Funciones INDEX y MATCH
Función IF combinada con función AND
Combinación de funciones SUM y OFFSET
Funciones SUMIF y COUNTIF
Función MODO.SNGL
Funciones INDEX y MATCH
En los cálculos financieros, a menudo se usa una combinación de las funciones INDEX y MATCH. Su acción conjunta es similar al funcionamiento de la función VLOOKUP, pero tiene muchas ventajas en comparación con ella. Primero, consideraremos estas funciones por separado.
Función INDEX
La función INDEX encuentra el valor de un elemento en un bloque de datos por el número de fila y el número de columna especificados. En general, su estructura se verá así:
INDEX (array; row_number; [column_number]), donde
- array – este es un bloque de celdas donde se realizará la búsqueda.
- row_number – este es el número de secuencia de la línea en la que se encuentra el valor. Este parámetro es obligatorio si no se especifica el número de columna.
- column_number – este es el número de serie de la columna en la que se encuentra el valor deseado. Si no se especifica un número de línea en la fórmula, este parámetro es obligatorio.
Si se especifican un número de fila y un número de columna, la función devuelve el valor de la celda ubicada en la intersección de estos datos.
Considera un ejemplo. Hay una tabla con una lista de números de productos y sus dimensiones. Para encontrar la longitud del número de producto 2, debe escribir una fórmula del formulario =INDEX(B2:D6;3;2)
En este ejemplo, el resultado de la función será el contenido de la celda en la intersección de la tercera fila y la segunda columna de esta matriz.
Desafortunadamente, con mayor frecuencia en los cálculos, se desconoce el número de fila o columna. Entonces la función MATCH viene al rescate.
Función MATCH
La acción de la función MATCH es similar a la acción de la función INDEX, pero MATCH no devuelve el valor de la celda, sino la posición de la celda en el rango especificado. En general, la fórmula se verá así:
MATCH (search_value; array; [match_type]), donde
- search_value – Esto es lo que necesitas encontrar. Aquí puede haber no solo un texto o valor numérico, sino también uno lógico, así como un enlace a una celda.
- array – este es el rango de celdas que se está viendo.
- match_type – Este es un parámetro opcional que se puede establecer en “1”, “0” o “-1”. Le dice a la función qué valor encontrar: exacto o aproximado. Si los datos en la matriz están disminuyendo en orden ascendente, el parámetro “1” indicará que es necesario seleccionar el valor máximo menor o igual al deseado. El parámetro “-1” se especifica para una matriz decreciente. En este caso, la función seleccionará el valor mínimo mayor o igual al deseado. El parámetro “0” encuentra el primer valor igual al deseado. Es este valor del tipo de coincidencia el que se usa en la combinación de las funciones INDEX y MATCH.
Para ilustrar el funcionamiento de esta función, considere un ejemplo. En la tabla anterior, encontramos en la columna “Product Number” cuál será la cuenta para el número de producto 2. Para esto, escribimos la fórmula: =MATCH(2;B2:B6;0)
¿Por qué necesito saber la posición de un elemento en una tabla? Resulta que es muy conveniente usar este valor como argumento para la función INDEX.
Combinación de las funciones INDEX y MATCH
Si analizamos ambas funciones, queda claro que la función INDEX busca valores de celda por número de fila y número de columna. Al mismo tiempo, la función MATCH encuentra números de fila y de columna. Por lo tanto, si usa estas dos funciones en la misma fórmula, MATCH encontrará la posición relativa del valor deseado, y la función INDEX usará estos valores y devolverá el contenido de las celdas calculadas.
Considera un ejemplo. En la tabla anterior encontramos el ancho del producto en el número 3. Para esto usamos la siguiente fórmula: =INDEX($D$2:$D$6;MATCH(3;$B$2:$B$6;0))
Se recomienda que utilice enlaces absolutos para estas fórmulas para que los rangos de búsqueda no se confundan al copiar fórmulas.
¿Por qué es mejor usar una combinación de funciones INDEX y MATCH en lugar de usar la función VLOOKUP? En primer lugar, una fórmula basada en INDEX y MATCH le permite buscar los datos deseados en el rango especificado de izquierda a derecha y de derecha a izquierda, mientras que cuando usa VLOOKUP, el valor deseado siempre debe estar en la columna del extremo izquierdo del rango.
En segundo lugar, cuando usa la función VLOOKUP, no puede eliminar ni agregar columnas a la tabla. De lo contrario, el resultado de la fórmula será incorrecto. Esto se debe a que la sintaxis de esta función implica especificar el rango completo y el número de columna específico del que se tomarán los datos. Al usar las funciones INDEX y MATCH, puede eliminar o agregar tantas columnas como desee.
En tercer lugar, cuando se usa una combinación de las funciones INDEX y MATCH, no hay restricción en el tamaño del valor de búsqueda, mientras que la función VLOOKUP limita el número de caracteres del valor de búsqueda a 255 caracteres.
Cuarto, cuando se realizan cálculos en grandes conjuntos de datos, el uso de las funciones INDEX y MATCH reduce significativamente el tiempo necesario para buscar valores en comparación con la función VLOOKUP. Esto se debe a que se llama a la función VLOOKUP para cada valor del rango de datos especificado. En contraste, una fórmula basada en las funciones INDEX y MATCH simplemente realiza una búsqueda y devuelve un resultado.
Función IF combinada con función AND
La función booleana IF verifica si el contenido de las celdas cumple ciertas condiciones. Si coincide, la función devuelve uno de los valores definidos por el usuario. En caso de discrepancia, devuelve otro valor establecido. La sintaxis de la función es la siguiente:
=IF(expresión_lógica; valor_si_verdadero; valor_si_falso), donde
- expresión_lógica – Estos son los datos que deben verificarse y las condiciones para la verificación. Por ejemplo А2>10.
- valor_si_verdadero – este es el registro que aparecerá si el valor de la celda satisface la condición dada.
- valor_si_falso – Este es el registro que aparecerá si el valor de la celda no satisface la condición dada.
Muchos usuarios que han realizado cálculos financieros complejos saben lo difícil que es comprender las fórmulas que usan bucles anidados utilizando la instrucción IF. Resulta que estas fórmulas se pueden simplificar si usa la función IF en combinación con las funciones AND / OR. La combinación de las funciones AND e IF funciona de la siguiente manera. Si A = 1 y A = 2, la fórmula devuelve el valor B; de lo contrario, devuelve C. Para la función OR, la fórmula no funciona de esa manera. Si A = 1 o A = 2, entonces la fórmula devuelve el valor B, de lo contrario, el valor C.
Considera un ejemplo. Cree una fórmula que verifique el contenido de la celda C2, igual a 110. Si el número está en el rango de 90 a 300, el resultado será 1, de lo contrario 0. La fórmula será la siguiente: =IF(AND(C2>=C4;C2<=C5);C7;C8)
Como se puede ver en la figura, se colocará un valor igual a 1 en la celda con el resultado de la función. El número 110 está realmente en el rango de 90 a 300.
El valor total puede ser no solo un número, sino también un texto, por ejemplo, las palabras “SÍ” o “No” o cualquier otra frase.
Combinación de funciones SUM y OFFSET
La función OFFSET en sí misma rara vez se usa, pero combinarla con otras funciones puede producir muy buenos resultados. Por ejemplo, el uso combinado de las funciones SUM y OFFSET le permite crear fórmulas bastante complejas cuando crea una función dinámica que suma un número variable de celdas. Para resolver este problema, se utiliza la función SUM y, en lugar de la celda final, se especifica la función OFFSET, es decir, la fórmula se vuelve dinámica.
La fórmula resultante se verá así:
= SUM (start_range:OFFSET (referencia, número de filas, número de columnas)), donde
- start_range – este es el punto de partida del rango de celdas utilizado por la función SUM.
- referencia – esta es la referencia de celda que se utiliza para calcular el punto final del rango.
- número de filas – este es el número de filas utilizadas para calcular el desplazamiento de la celda. Este valor puede ser positivo, negativo e igual a cero.
- número de columnas – Este es el número de columnas a la derecha o izquierda de la referencia de celda dada. Se usa al calcular el desplazamiento. Cuando se desplaza hacia la izquierda, este valor es negativo. Cuando se desplaza hacia la derecha, el valor es positivo. Si los datos calculados están en la misma columna, entonces este parámetro es cero.
Considera un ejemplo. Hay una tabla con los números de serie de los días del mes y los ingresos recibidos en cada día. Todos los días, la información en la tabla se actualiza agregando una fila con los ingresos recibidos por día. Componemos la fórmula en la celda final después del cuarto día de ventas: =SUM(B2:OFFSET(B6;-1;0))
Para agregar información sobre el quinto día de ventas, debe agregar una línea vacía después del cuarto día e ingresar la información necesaria. En este caso, la fórmula tomará la forma: =SUM(B2:OFFSET(B7;-1;0)), y el ingreso total aumentará en la cantidad de ingresos recibidos en el quinto día.
Funciones SUMIF y COUNTIF
Estas dos funciones se usan con mucha frecuencia en los cálculos financieros. SUMIF encuentra la suma en un rango dado de celdas por una determinada condición. COUNTIF cuenta todas las celdas que coinciden con la condición dada.
La función SUMIF tiene la siguiente estructura:
SUMARIO (rango; criterio; rango_sum), donde
- rango – esta es una matriz de celdas en las que se verificará el cumplimiento de los criterios especificados.
- criterio – esta es una condición para seleccionar celdas. Puede ser un número, texto, expresión o referencia de celda.
- rango_sum – este es un parámetro opcional. Si no lo especifica, la suma se realizará teniendo en cuenta el argumento “Rango”.
La acción de esta función es fácil de entender con un ejemplo. Hay una tabla con una lista de productos y su cantidad. Necesita encontrar la cantidad de “Product 1”. La fórmula para el cálculo será la siguiente: =SUMIF(B2:B7;”Product 1″;C2:C7)
En el mismo ejemplo, puede calcular la cantidad de productos que excluyen “Product 1″ utilizando la siguiente fórmula: =SUMIF(B2:B7;”<>Product 1”;C2:C7)
La función COUNTIF tiene la siguiente estructura:
COUNTIF (rango; criterio), donde
rango y criterio son similares a SUMIF.
En el mismo ejemplo, contamos el número de filas “Product 1”. La fórmula para calcular se verá así : =COUNTIF(B2:B7;”Product 1″).
Con esta fórmula, puede calcular el número de filas que satisfacen ciertas condiciones. Por ejemplo, para calcular el número de filas en esta tabla con el número de productos mayor que 10, debe crear la siguiente fórmula: =COUNTIF(C2:C7;”>10″).
Función MODO.SNGL
La función estadística MODE.SNGL y su versión obsoleta MODE encuentra el valor más frecuente en el rango de datos (matriz) y devuelve este valor. Sintaxis de la función: =MODE ((número1; [número2]; …), donde
- número1 – este es un argumento obligatorio, que es un número, una referencia a una celda numérica, una matriz o un rango de celdas.
- número2 – argumento opcional Tales argumentos pueden ser de 1 a 255.
Los argumentos que no se pueden convertir a números provocan un error de fórmula. Si no hay números idénticos en el rango especificado, el resultado de la función será el valor de error #N/A.
¿Por qué puedo usar esta función en los cálculos financieros? Por ejemplo, para averiguar qué productos se compran con mayor frecuencia en base a datos de resumen. El criterio de selección puede ser el precio del producto, tamaño, volumen, dimensiones, etc. Como ilustración, consideramos una tabla que refleja las ventas de bienes con la fecha de venta, el tamaño del producto y su precio. Para saber qué tamaños de productos se venden con mayor frecuencia, utilizamos la fórmula: =MODE(C2:C6)
La figura muestra que los productos con un tamaño de 36 tienen mayor demanda. Por lo tanto, la función MODE determina el evento que ocurre con más frecuencia en el rango de eventos.
Así, el uso de las funciones financieras de la aplicación Office Excel facilita enormemente la realización de liquidaciones financieras y comerciales de créditos y préstamos, análisis financiero y de inversiones y trabajo con valores. Una ventaja significativa de la aplicación es la capacidad de establecer una contraseña para proteger documentos importantes. Esto evita que terceros no autorizados accedan a informes financieros y económicos. Desafortunadamente, las contraseñas a veces se pierden, pero la recuperación de la contraseña Excel es posible mediante programas especiales.