Funciones usuales de excel
FUNCIONES USUALES:

En las fórmulas de excel, podemos incorporar las llamadas funciones, que son fórmulas sencilla o complejas predefinidas para realizar cálculos con los datos que hay en las hojas. Todas las funciones se escriben con la siguiente sintaxis:



Por ejemplo, supongamos que tenemos una serie de números en el rango A3:B6 y queremos sumarlos. Podríamos hacerlo escribiendo =A3+A4+A5+A6+B3+B4+B5+B6, pero es evidente que sería poco funcional y poco práctico. Utilizamos la función Suma de la siguiente manera:

Veamos a continuación la sintaxis y aplicación de algunas funciones usuales y frecuentes con ejemplos básicos. El lector debe tener encuenta que estas funciones pueden anidarse para crear fórmulas y expresiones más complejas que permitan resolver datos reales...








=Año(fecha)

Devuelve el año de una fecha. Deberá escribirse una fecha entre los paréntesis o una referencia a una celda que contenga un valor de tipo fecha.

La utilizamos para saber la diferencia en años entre dos fechas, ya que si restamos directamente las dos fechas obtendremos otra fecha y no la diferencia en años. Hay que recordar que una fecha (datos de tipo fechas) es en realidad un número de serie que comienza en 1 correspondiente a la fecha 1/1/1900. La fórmula de la celda C2 de la imagen inferior, resta 40323 (25/05/2010) menos 38778 (02/03/2006) y obtiene 1545 que en formato fecha corresponde a 24/03/1904 lo cual no tiene sentido ni valor. Si quieres comprobarlo escribe 1545 en una celda y aplícale formato de fecha...



En realidad no es totalmente exacto decir que hay una diferencia de 4 años porque lo que hay es una diferencia de 4 años 2 meses y 23 dias. Igualmente ocurriría si la celda B2 contuviera la fecha 26/06/2006 ya que seguiría mostrando 4 años cuando en realidad la diferencia sería de 3 años, 11 meses y 1 dia. Si quieres saber la diferencia exacta de tiempo entre dos fecha puedes utilizar la función SIFECHA (2007). Puedes ver un ejemplo en este enlace trabajar con años, meses y dias








=Buscarv(valor;matriz;columna;ordenado)

Busca un valor determinado en la primera columna de una matriz y devuelve el valor que se encuentra en otra columna de la matriz pero en la misma fila que el valor determinado de la primera columna. Esta función utiliza cuatro argumentos separados por punto y coma. El primer argumento es "valor", es decir, el valor que buscamos. Puede ser un número, un texto (entre comillas), o una referencia a una celda donde se encuentra el valor. El segundo argumento es "matriz", es decir, el rango de celdas donde se encuentran los valores con los que trabajamos. El tercer parámetro es columna, es decir, la columna que contiene el valor que buscamos. El último parámetro es opcional, su valor será cero (desordenado) o uno (ordenado), es decir, es un valor lógico (verdadero o falso) que indica que la primera columna de valor está ordenado o no. Si no se encuentra el valor produce un mensaje de error.

Por ejemplo, supongamos que tenemos las siguientes columnas de datos: CONCEPTO, DOLARES, LIBRAS Y EUROS. Deseamos saber el precio en euros (columna 4) del producto "altavoz". El rango de datos (matriz) es F6:I9. La primera columna no está ordenada...


Existen otras funciones parecidas como BUSCARH, BUSCAR, INDICE...

Puedes ver un ejemplo mas completo en este enlace buscador de datos en una lista Excel...








=Coincidir(valor-buscado;matriz-busqueda;tipo)

Esta función devuelve un número entero correspondiente a la posición relativa de un elemento que se encuentra en una matriz. Sirve para saber que posición numérica ocupa un elemento en una matriz. El parámetro valor-buscado puede ser un texto, un número o una referencia a una celda. La matríz-búsqueda será el rango de celdas que contiene los valores. Tipo puede ser 0, 1, -1. Cero busca coincidencia exacta con la matriz ordenada o desordenada.

Por ejemplo, supongamos que tenemos una lista de nombres desordenados de paises del mundo en A2:A50. Queremos saber el lugar que ocupa España en esa lista...



Por si sola esta función no aporta gran información pero puede utilizarse en fórmulas con otras funciones para cálculos más avanzados. Puedes ver un ejemplo en este enlace Buscar en una matriz con INDICE








=Contar(rango)

Devuelve un número entero que corresponden con la cantidad de celdas numéricas que contiene un rango de celdas determinado. Las celdas que contengan texto no son contadas.

Por ejemplo, queremos saber cuantos pagos se han cobrado, es decir, cuantas celdas del rango tienen valores numéricos...



La función CONTAR suele utilizarse en combinación con otras para formar fórmulas avanzadas. CONTAR.SI y CONTAR.SI.CONJUNTO (2007) son variantes de esta función. Puedes ver un ejemplo con estas funciones en este enlace Contar elementos de un rango o matriz








=Contara(rango)

Devuelve un número entero que corresponden con la cantidad de celdas que no esten en blanco en un rango de celdas determinado, es decir, cuenta celdas ocupadas indistintamente de que contengan números, textos u otro tipo de dato.

Por ejemplo, si queremos saber cuantos pagos pendientes hay en el ejemplo anterior, escribimos: =CONTARA(L22:N24)-CONTAR(L22:N24)








=Contar.blanco(rango)

Devuelve un número entero que se corresponde con la cantidad de celdas vacías que contiene un rango de celdas determinado. Las celdas que contengan texto no son contadas.

Por ejemplo, queremos saber cuantos pagos se han cobrado...












=Contar.si(rango;criterio)

Devuelve un número entero que corresponde con la cantidad de celdas que cumplen una determinada condición especificada en un rango. En otras palbras, sirve para contar celdas que cumplen una determinada condición. Las condiciones se encuentran en el primer argumento "rango", la condición o criterio puede ser un literal entre comillas o una referencia a una celda

Por ejemplo, queremos saber cuantos empleados hay en el departamento de "GERENCIA"...



Puedes ver un ejemplo de CONTAR, CONTAR.SI, CONTAR.SI.CONJUNTO (2007) en este enlace Contar elementos de un rango o matriz








=Derecha(texto;nºcaracteres)

Esta función se utiliza con datos o celdas de tipo texto. Devuelve, de un texto o una celda que contenga un texto, uno o varios caracteres por la derecha.

Por ejemplo, la celda C19 contiene una dirección con un c.p. Queremos obtener, en otra celda, el c.p. Puesto que el c.p. tiene 5 caracteres más los 2 paréntesis, indicamos 7 caracteres...



Puedes ver un ejemplo más avanzado en este enlace Actualizar una columna de texto








=Dia(fecha)

Devuelve el dia de una fecha. Deberá escribirse una fecha entre los paréntesis o una referencia a una celda que contenga un valor de tipo fecha.

La utilizamos para saber la diferencia en dias entre dos fechas, ya que si restamos directamente las dos fechas obtendremos otra fecha (datos de tipo fechas) Hay que recordar que una fecha es en realidad un número de serie que comienza en 1 correspondiente a la fecha 1/1/1900. La fórmula de la celda C2 de la imagen inferior, resta 40323 (25/05/2010) menos 38778 (02/03/2006) y obtiene 1545 que en formato fecha corresponde a 24/03/1904 lo cual no tiene sentido ni valor. Si quieres comprobarlo escribe 1545 en una celda y aplícale formato de fecha...(un número de serie) y no la diferencia en dias. La función dia sólo tiene en cuenta el dia de una fecha.



La función dia(fecha) no tiene en cuenta el mes ni el año de las fechas. En realidad 23 no son los dias que hay entre esas dos fechas, sino la diferencia de los dos dígitos primeros de cada fecha, en otras palabras, entre las dos fechas del ejemplo hay realmente 4 años, 2 meses y 23 dias o lo que es lo mismo 1545 dias. La funcion SIFECHA(fec1;fec2,tipo) ofrece mejores prestaciones para trabajar con más exactitud con años, meses y diase. Puedes ver un ejemplo en este enlace trabajar con años, meses y dias








=Encontrar(Texto-Buscado;Texto;Numero-Inicial)

Esta función devuelve un número entero correspondiente a la posición en la que se ecuentra un texto dentro de otro texto. El primer argumento es "Texto-Buscado", es decir, un texto entre comillas o una referencia a una celda que contenga un texto. El segundo argumento es "Texto", es decir, el texto en el que buscamos "Texto-Buscado" o bien una referencia a una celda. "Número-Inicial" es un número entero que indica la posición a partir de la cual vamos a buscar.

Para ilustrar lo dicho, veamos este ejemplo. Supongamos que tenemos en la celda A11 la descripción de un producto, con varios caracteres con el nombre del producto, una referencia de producto de 14 caracteres y un código de estantería de 7 caracteres. Necesitamos obtener en otra celda sólo la refencia del producto sin el nombre ni la estantería. Utilizamos la función Extrae (ver función Extrae) con Encontrar anidada. Encontrar("REF";A11;1) devuelve el número 19, que corresponde a la posición en la que se encuentra el texto buscado "REF". El número 19 sirve para indicarle a Extrae la posición a partir de la cual debe extraer 14 caracteres correspondientes a la referencia completa del producto, que es precisamente lo que buscabamos...



La función ENCONTRAR y EXTRAE suelen utilizarse en una misma fórmula para procesar substring o subcadenas de un texto. Puedes ver ejemplos en los siguiente títulos:

Obtener el primer apellido de una celda con un nombre completo

Obtener el segundo apellido de un nombre completo

Obtener el nombre de una celda con un nombre completo








=Extrae(Texto;Numero-Inicial;NºCaracteres)

Devuelve una subcadena de texto de otra texto. "Texto" es el texto del que vamos a extraer o una referencia a una celda que contenga un texto. "Número-Inicial" es la posición a partir de la cual vamos a extraer. "NºCaracteres" es la cantidad de caracteres que queremos extraer.

Tenemos en la columna A códigos de productos, queremos extraer los caracteres centrales...



La función EXTRAE y ENCONTRAR suelen utilizarse en una misma fórmula para procesar substring o subcadenas de un texto. Puedes ver ejemplos en los siguiente títulos:

Obtener el primer apellido de una celda con un nombre completo

Obtener el segundo apellido de un nombre completo

Obtener el nombre de una celda con un nombre completo








=Esblanco(Referencia)

Esta es una función lógica, es decir, devuelve el valor lógico "verdadero" o "falso". Si una determinada celda se encuentra vacía (no se ha escrito nada), Esblanco devuelve "verdadero". Si en la celda hay cualquier dato devolvera "falso". Esta función, para que tenga utilidad, debe utilizarse anidada con otra.











=Fecha(Año;Mes;Dia)

Convierte números de serie en fechas reales. Por ejemplo: supongamos que disponemos de 25 dias para ejecutar un proyecto a partir de la fecha 6/2/2012. Deseamos saber la fecha de terminación de ejecución del proyecto. Como nuestro proyecto comienza el 6/2/2012 damos el año y el mes a la función FECHA. También proporcionamos a la función el día de comienzo, pero sumándole 25, el tiempo para realizar el proyecto...












=Hoy()

Esta es una función que no lleva argumentos. Devuelve un dato de tipo fecha (la fecha del sistema). Por ejemplo, si deseamos mantener actualizada la antigüedad en años de los empleados de una empresa...



Sin embargo, calcular la antigüedad de un empleado de esta manera es bastante inexacto. Supongamos que hoy es uno de Enero del 2006 (01/01/2006). Para el empleado Jose, la fórmula del ejemplo anterior, seguiría mostrando 1 año de antigüedad, lo cual es totalmente falso puesto que si se dio de alta en la empresa el 4/12/2005 y hoy es 1/1/2006 entonces su antigëdad no es ni siquiera de 1 mes puesto que sólo lleva en la empresa 27 dias. En resumen, para calcular la antigüedad exacta de un empleado, de una factura, tiempo entre dos fechas, etc. hay que utilziar la función SIFECHA. Puedes ver un ejemplo completo en este enlace trabajar con años, meses y dias








=Indice(Matriz;Fila;Columna)

Devuelve un valor o una matriz de valores en el argumento "Matriz" determinado por los valores de "Fila" y "Columna. También puede devolver una referencia. Si se obtiene una matriz de valores deberá introducirse como fórmula matricial.

Supongamos que tenemos una lista de nombres de productos en O6:O800 con precios en las columnas P, Q, y R. Deseamos saber el primer precio (columna 2) del producto "EH-200-A1" (fila 3). Para saber la fila en la que se encuentra el producto buscado, utilizamos la función Coincidir que devolverá 3 que será la fila que Indice utilizará para devolver el valor que se encuentra en la columna 2.



Puedes ver otro ejemplo en este enlace Buscar en una matriz con INDICE








=Izquierda(Texto;NºCaracteres)

Igual que Derecha, pero por la Izqierda...








=Largo(Texto)

Devuelve un número entero correspondiente a la cantidad de caracteres que contien un texto o una celda determinada.

Disponemos de una columna de evaluación en clase, en la que cada caracter "+" vale 0.5 puntos de calificación. Obtenemos la nota final...









=Max(Rango) y Min(Rango)

Devuelve el máximo valor numérico (o el mínimo) de un rango de celdas. El rango debe contener celdas numéricas. Si contien texto devolvera 0.



Suele utilizarse en fórmulas con otras funciones en cálculos más avanzados. Puedes ver un ejemplo en este enlace Buscar en una matriz con INDICE








=Mayúsc(Texto), Minúsc(Texto) y Nompropio(Texto)

Mayúsc y Minúsc convierten todos los caracteres de un texto en mayúsculas o minúsculas. Nompropio convierte en mayúscula la primera letra de cada palabra de un texto y en minúscula el resto de las letras de cada palabra...









=Mes(Fecha)

Devuelve un número (de 1 a 12) correspondiente a un mes del año de un valor de tipo fecha. Por ejemplo =Mes(25/06/2012), devuelve el valor 6. Cuando queremos calcular los meses entre dos fechas obtenemos meses incompletos.


Si queremos calcular con exactitud meses, dias y años entre fechas utiliza la función SIFECHA. Puedes ver un ejemplo de Mes(fecha) en este enlace trabajar con años, meses y dias








=Pago(Interes;Nper;Va;Vf;0)

Pago es una función financiera que devuelve el pago de un préstamo basándose en pagos constantes y en una tasa de interés constante. Interés es el tipo de interés del préstamo. Nper es el número total de pagos que hay que realizar. Va es el valor actual o el total del valor de los pagos. Vf es el valor futuro que para un prestamo sería 0. El último argumento indica si el vencimiento del pago es al principio del período o al final.











=Pagoint(Interés;Periodo;Nper;Va;Vf;0)

Pago es una función financiera que devuelve el "interés pagado" en un período específico de los pagos periódicos constantes con una tasa de interés constante (ver Pago).











=Producto(Rango)

Multiplica los números que hay en el rango.








=Promedio(Rango)

Calcula el promedio de un rango.











=Residuo(Dividendo;Divisor)

Devuelve el resto de la división entre Dividendo y Divisor. El resultado tiene el mismo signo que Divisor.

Para calcular la letra de DNI de una persona, se divide el número del DNI por la constante 23. El resto resultante determina la letra del DNI...











=Si(Condición;Sicierto;Sifalso)

Función condicional. Si una determinada "Condición" se cumple entonces se ejecuta la expresión "Sicierto". En caso contrario (si la condición no se cumple) se ejecuta "Sifalso.













=Sumaproducto(Rango1;Rango2;...)

Multiplica cada elemento del "Rango1" (o matriz1) por el correspondiente en "Rango2" (o matriz2) y suma todos los resultados de los productos...











=Sumar.si(Rango-Criterios;Criterio;Rango-Suma)

Suma condicional, es decir, suma las celdas numéricas de "Rango-Suma" pero sólo si se cumple una determinada condición o "Criterio" dada en un "Rango-Criterios".

El "Rango-Criterios" sólo puede contener una columna. El "Criterio" se puede escribir entre comillas directamente en la función o puede ser una referencia a una celda que contenga el criterio. "Rango-Suma" contendrá los valores numéricos que queremos sumar...






Hoja GOOGLE DOCS en la que puedes escribir números, datos, fórmulas y plantear comentarios y preguntas...:

















navegación

PowerPoint: Las presentaciones de PowerPoint se componen, normalmente de varias diapositivas o pantallas. La manera de exponer esas pantallas depende del modelo de navegación que utilicemos

=Año(fecha)

Devuelve el año de una fecha. Deberá escribirse una fecha entre los paréntesis o una referencia a una celda que contenga un valor de tipo fecha...






 eduardo@aulapc.es Granada (España)