Las funciones anidadas en Exceles utilizar una función en el argumento de otra de forma adecuada, parece muy simple pero veremos que se puede complicar dada que la anidación puede hacerse en muchos niveles e involucrar varias funciones. Construir fórmulas en Excel que combinan varias funciones es una forma de conseguir un resultado que nos puede sorprender.
En ocaciones necesitamos hacer varias modificaciones en un texto, que nos puede ayudar a optimizar nuestro trabajo, miremos el siguiente ejemplo.
Se requiere hacer los siguientes cambios en un texto:
* Eliminar los espacos que estan de mas
* La primera letra en mayuscula y el resto en minuscula
* Los espacios sustituirlos con "_"
* Agregar el texto "VBA"al final
Como sumar números Negativos y Positivos en Microsoft Excel, para sumar solo los valores negativos y
positivos de una lista de numeros, para lograrlo utilizaremos una función matricial (las
funciones matriciales, se caracterizan porque val dentro de llaves), el ejemplo
de hoy surge de la necesidad de saber el total de Notas de Credito(los numeros
positivos) y las Notas de Credito(los valores negativos), realizamos la suma
de la columna valiendos de la función SUNA el resultado seria -750, como vemos
en la grafica segun la selda B16.
Calcular el promedio excluyendo los ceros en Excel, El Promedio en Microsoft Excel devuelve el promedio (media aritmética) de los argumentos. Por ejemplo, si el rango (rango: dos o más celdas de una hoja. Las celdas de un rango pueden ser adyacentes o no adyacentes.) B9:B15 contiene números, la fórmula =PROMEDIO(B9:B15) devuelve el promedio de dichos números . el resultado es 4.29.
Como el titulo de esta publicación lo indica, Promedio Condicionado Sin Ceros, para este caso lo podemos lograr de dos formas:
Primer ejemplo:
En este caso utilizaremos 2 funciones, la SUMA y CONTAR.SI =SUMA(B9:B15)/CONTAR.SI(B9:B15,">0")
Segundo Ejemplo:
En ste caso utlizaremos la función PROMEDIO.SI =PROMEDIO.SI(B9:B15,">0")
Como pueden ver el resultado de los dos ejemplos dan el mismo resultado, claro que lo mas recomendable es utilizar el segundo ejemplo, la idea de presentar las dos opciones es ejemplificar formulas anidadas, que nos permiten lograr los mismos resultados.
Calcular la edad de una persona en Excel, se hace necesario cuando trabajamos con datos de personas y calcularlo en base a la fecha de nacimiento, en esta oportunidad veremos tres fórmulas para poder calcular la edad de las personas o la diferencia en años de dos fechas.
Con la funciones de Excel podemos calcular los años cumplidos de una persona, para este ejemplo utilizaremos las siguientes funciones SIFECHA, HOY, AÑO, ENTERO, DIAS360.
La Función Y
Microsoft Excel, comprueba si los valores son verdaderos, en este caso devuelve
VERDADERO de lo contrario FALSO, esta función es de gran utilidad para evaluar
si se cumplen varias condiciones a la vez. Un uso común de la función Y es
expandir la utilidad de otras funciones que realizan pruebas lógicas. Por
ejemplo, la función SI realiza una
prueba lógica y, luego, devuelve un valor si la prueba se evalúa como VERDADERO y otro valor si la prueba se
evalúa como FALSO. Con la función Y
como argumento prueba_lógica de la función SI, puede probar varias
condiciones diferentes en lugar de sólo una.
Imagen 1
En resumen
podemos decir que “devuelve VERDADERO si
todos los argumentos se evalúan como VERDADERO; devuelve FALSO si uno o más
argumentos se evalúan como FALSO”.
En el siguiente ejercicio utilizaremos la función SUMAR.SI para realizar una suma entre dos limites o intervalos. Segun el concepto de esta función nos dice que suma los valores en un rango que cumple criterios que se especifican. La sintaxis de la función SUMAR.SI cuenta con los argumentos (argumento: valor que proporciona información a una acción, un evento, un método, una propiedad, una función o un procedimiento).
#MicrosoftExcel, #SUMAR.SI, #Excel
SINTAXIS:
rango (Obligatorio)El rango de celdas que se desea evaluar según los criterios especificados. Las celdas de cada rango deben ser números, o bien nombres, matrices o referencias que contengan números. Los valores en blanco y los de texto no se tienen en cuenta.
criterios (Obligatorio)El criterio en forma de número, expresión o texto, que determina las celdas que se van a sumar.
Por ejemplo, los criterios pueden expresarse como 100, ">100", E3, 100, "100", "5.01.01.01" o funciones como HOY().
Importante: Cualquier criterio de texto o cualquier criterio que incluya los símbolos lógicos o matemáticos debe estar entre comillas dobles ("). Si el criterio es numérico, las comillas dobles no son obligatorias.
rango_suma(Opcional)Las celdas reales para agregar, si desea agregar celdas a las ya especificadas en el argumento rango. Si se omite el argumento rango_suma, Excel agrega las celdas que están especificadas en el argumento rango (las mismas celdas a las que se aplica los criterios).
Despues de conocer la sintaxis de la función y poder aplicarlo a un ejercicio real realizamos el siguiente ejemplo: en una columna que contiene el total de ventas según código y fecha, en este caso deseamos sumar las ventas mayores o iguales a 100 podemos usar la siguiente fórmula: en este caso omitiremos rango_suma =SUMAR.SI(C2:C6,">=100") el resultado es de 2,295.05 según nuestra tabla de prueba.
Otro ejemplo seria utilizando rango_suma =SUMAR.SI(B2:B6, "5.01.01.01", C2:C6) el resultado es de 8,28.15La fórmula suma sólo los valores del rango B2:B6, donde las celdas correspondientes al rango A2:A6 son iguales al código "5.01.01.01"
En Excel existe una forma de agregar un comentario o documentar las fórmulas que realizamos utilizando la función N, sin que altere el
resultado o la funcionalidad de la formula, el objetivo es comentar la fórmula
para que en el futuro nos ayude a entender porque se realizó dicho cálculo. Según
la ayuda de Excel nos indica que por lo general no es necesario usar esta
función y que existe solamente por cuestiones de compatibilidad con otros
programas.
La función BUSCARH (en inglés HLOOKUP) forma parte de las funciones de búsqueda y referencia, el significado de la "H" en el nombre de la función significa que hace una búsqueda Horizontal, BUSCARH en Microsoft Excel(es la abreviación de búsqueda horizontal) busca un valor dentro de una fila y devuelve el valor que ha sido encontrado es decir busca un valor en la fila superior de una tabla o una matriz (rango de datos) de valores y devuelve un valor en la misma columna de una fila especificada en la tabla o matriz.
La función BUSCARV (en inglés VLOOKUP) es una de las funciones de búsqueda y referencia mas utilizadas, el significado de la "V" en el nombre de la función significa que hace una búsqueda vertical, esto quiere decir que la hoja de cálculo debe estar organizada en forma de listas verticales, BUSCARV en Microsoft Excel(es la abreviación de búsqueda vertical) es la función perfecta que permite encontrar un valor dentro de un matriz(rango de datos), devolviendo el valor de la columna indicada en el argumento.
La función BUSCARVpermite buscar un valor en la primera columna de una matriz, una vez localizado nos muestra dentro de la misma fila el valor que contiene la columna que deseamos obtener, la función buscarv es una de las más utilizadas en la búsqueda
de registros en Excel a la hora de hacerlo por medio de formulas.
Asumiendo que la fórmula a utilizar es:
BUSCARV(D2,$A$2:$B$8,2,FALSO) Si en nuestra tabla o lista existe el valor
buscado todo está bien, pero si no existiera, el resultado sería #N/A
La función ALEATORIO en Excel devuelve un número real aleatorio que será mayor a cero pero menor a uno. "La aleatoriedad se asocia a todo proceso cuyo resultado no es previsible más que en razón de la intervención del azar. El resultado de todo suceso aleatorio no puede determinarse en ningún caso antes de que este se produzca" (Wikipedia)
Una lista aleatoria en Excel es donde el orden de los elementos nunca está establecido sino que el orden es totalmente al azar, para crear este tipo de lista debemos utilizar la función ALEATORIO. y REDONDEAR para aproximarlo y tener solo numeros enteros.
Lo primero que hacemos es ingresar la formula REDONDEAR(ALEATORIO()*30,0) en la columna "A" en la lista(Base de datos), para producir números aleatorios entre 0 y 30
Seguidamente crear una lista de 1 a 19 en otra hoja utilizando la funsion "BUSCARV"
La función ABS, devuelve el valor absoluto de un valor numérico. El valor absoluto de un número es su valor real,
es decir, el valor del número sin tomar en cuenta el signo positivo o negativo
que acompañe al número. El valor absoluto del número siempre será positivo.
Sintaxis de la función ABS de Excel
ABS(numero)
Ejemplo:
Si en la celda "A1"
tenemos el valor -535 al escribir en
la celda A2 la función =ABS(A1) nos devolverá el valor 535 sin el signo negativo. Y si el numero
fuera positivo obtendremos el valor 535.
Si en tus modelos de Excel tienes que realizar
cálculos que te dan valores negativos (números menores que cero) y necesitas
trabajar con números positivos, esta función puede ayudarte.
Función N
Devuelve un valor convertido en un número.
Por ejemplo vamos a comparar una cantidad con varias cantidades, y queremos saber si el valor que estamos comparando es mayor.
Ejemplo:
En la celda C9 escribimos 200, en la celda B13 escribimos 22, ahora bien en
la celda D13 escribimos la siguiente formula =B13>$C$9 y el resultado será
FALSO
Aquí es donde utilizaremos la función N, si el resultado es FALSO que
muestre un 0 y si el VERDADERO un 1.
En la celda E13 escribimos la formula =N(B13>$C$9) y el resultado será 0
Lafunción SUMAR.SI suma valores que cumplen con cierta condición. Si necesitamos sumar valores entre dos fechas. La función SUMAR.SI.CONJUNTO permite sumar un rango que cumplan varios criterios, lo que la hace diferente a la función SUMAR.SI que sólo considera un criterio. Si necesitamos sumar una columna según el código contable entre el
02/01/2011 y el 30/06/2011, podríamos utilizar un filtro, pero en esta
ocasión les comparto una formula para lograrlo.
Utilizaremos una suma condicionada a un rango de fechas, utilizamos la función SUMAR.SI.CONJUNTO
La formula esta compuesta por dos partes.
La primera parte suma los valores iguales o superiores a una fecha (Fecha Inicio). SUMAR.SI.CONJUNTO($C$11:$C$152,$A$11:$A$152,">="&E12,$B$11:$B$152,G12)
La segunda parte suma los valores superiores a otra fecha (Fecha Fin). SUMAR.SI.CONJUNTO($C$11:$C$152,$A$11:$A$152,">"&F12,$B$11:$B$152,G12)
Luego la sumatoria de la primera le restamos la sumatoria de la segunda formula, quedando de la siguiente forma:
=SUMAR.SI.CONJUNTO($C$11:$C$152,$A$11:$A$152,">="&E12,$B$11:$B$152,G12)-SUMAR.SI.CONJUNTO($C$11:$C$152,$A$11:$A$152,">"&F12,$B$11:$B$152,G12)
Nuestro rango es C11:C152 y queremos saber cuánto suma del 02/01/2011 al 30/06/2011 y que la Cta_Contable_ID es igual a1.01.03.01, por lo que hay que sumar lo que hay entre 02/01/2011 y 31/12/2011 y luego restar lo que hay entre 01/07/2011 y 31/12/2011. De esta forma quedaría únicamente lo que hay entre el 02/01/2011 y 30/06/2011 y el resultado es 568,112.73
Para poder comprobar si la formula esta dando el resultado correcto, podemos utilizar el filtro de Enero a Junio en la columna "Fecha_Operado" y en la columna Cta_Contable_ID filtramos por 1.01.03.01 y al sumar tendremos el mismo resultado.
La función BDCONTARA en Excel es de gran ayuda cuando trabajamos con nuestros listadoso Tablas en Excel, y no son suficiente mente potentes las “funciones normales” de Excel, por ejemplo necesitamos una suma condicionada a varios de los campos de nuestro origen de datos. Normalmente con la función SI, o con la suma condicionada SUMAR.SI o CONTAR.SI podíamos concluir nuestros análisis, de una forma elaborada.
Pero existen funciones de bases de datos, especialmente diseñadas para Bases de datos de Excel las funciones más básicas (SUMA, CONTAR, PROMEDIO, etc.); las diferenciamos por que estas funciones de bases de datos comienzan con “BD” (BDCONTAR, BDSUMA, etc).
En esta oportunidad les comparto una de las formas que podemos aprovechar estas funciones para poder obtener resultados efectivos cuando las variables se salen del alcance de las funciones comunes.
Veamos el siguiente ejemplo:
Necesitamos saber las respuestas de las siguientes interrogantes: 1) Cantidad de Libros del Autor WILSON 2) Libros de la Editorial PRENTICE HALL o MCGRAW-HILL 3) Libros de Autores cuyo nombre empieza Z 4) Libros cuyo precio esta entre 50 y 100 5) Libros de la Editorial ADDISON ó NORMA cuyo Precio sea Mayor a 20 6) Libros de la Editorial MCGRAW-HILL cuyo Nombre comience con F ó P
Funciones anidadas (Usar una función como uno de los argumentos en una fórmula de que usa una función se denomina anidamiento, y nos referiremos a esta función como una función anidada.) de Excel, a continuación se listan las funciones utilizadas en el archivo:
La plantilla esta compuesta por 4 hojas que se utilizan de la siguiente forma:
* En la hoja "PRESUPUESTO" es donde se ingresa el presupuesto anual, esta disponible para 2 presupuestos.
* La hoja "BD" es donde se ingresan los datos(ejecutados o gastados) y que alimenta la hoja "EJECUCION-MENSUAL" y "SALDO"
* La hoja "EJECUCION-MENSUAL" es para ver la ejecusion por mes y lo acumulado a la fecha, para poder ver otro convenio, solo hay que cambiar los datos en la celda D2 que corresponde al año y D3 al numero de convenio.
* La hoja "SALDO" es para ver lo presupuestado, ejecutado y el saldo actual por cuenta.
Depreciación:
Representan el deterioro que sufren los activos fijos debido al uso y al transcurrir del tiempo.
Su cuantificación se realiza con el objetivo de atribuir una cuota de gasto a los resultados económicos de un período determinado.
En esta portunidad vamos a utilizar la función SLN
Devuelve la depreciación por método directo de un bien en un período dado.
SLN(costo,valor_residual,vida)
Costo es el costo inicial del bien. Valor_residual es el valor al final de la depreciación (también conocido como valor residual del bien). Vida es el número de períodos durante los cuales se produce la depreciación del bien (también conocido como la vida útil del bien).
Ejemplo:Costo del bien = 50,000.00
Valor residual = 2,500.00
Años de vida útil = 10
Ayuda con la función "SI" en excel, resulta que tengo condicionada una celda, para que me indique cuando debo comprar y cuando no segun mi saldo a la fecha comparandolo con mi stock minimo,
Lo que quiero es darle las siguientes condiciones:
1ra. Si (mi saldo actual es igual o mayor a "0" (y mayor que mi stock minimo ) entonces "DEJA LA CELDA EN BLANCO")
2da. Si (mi stock actual es igual o mayor a "0", (y menor que mi stock minimo ); entonces señala "COMPRAR"),
SE LO QUE NECESITO HACER PERO NO LOGRO HACER LA SINTAXIS PARA QUE LA FORMULA NO ME DE ERROR.
En respuesta a lo anterior, propongo la siguiente solución como se ve en la imagen:
En al celda F13 escribimos la siguiente formula =SI(Y(E13>0,E13<$C$10),"COMPRAR","")
en donde le indicamos que si E13>0 y E13<C10, entonces que muestre el mensaje "COMPRAR", en caso contrario que la celda quede vacia.
En esta oportunidad les comparto una formula que me solicitaron en la oficina.
La pregunta es: Se puede hallar una palabra dentro de una cadena de texto no importando en que ubicacion se encuentre dentro del texto?
La respuesta es SI.
Si deseas buscar dentro de una cadena de texto, palabras o caracteres
específicos la posición inicial de la primera cadena de texto de una
celda en particular pues te doy la solución ya que a través de la
función de Excel ENCONTRAR y HALLAR en efecto puedes obtener el resultado
requerido.
La formula se ingresa en la celda B2 y en la A2 tendrenos el siguinete texto carcha1y2__embarazadas.xls
Opcion 1
Utilizando las funciones:
SI
SI.ERROR
EXTRAE
ENCONTRAR
LARGO
La formula queda de la siguiente forma:
=SI(SI.ERROR(EXTRAE(A2,ENCONTRAR($G$2,A2,1),$H$2),0)="FERTIL","fertil",SI(SI.ERROR(EXTRAE(A2,ENCONTRAR($G$3,A2,1),$H$3),0)="embarazadas","embarazadas",SI(SI.ERROR(EXTRAE(A2,ENCONTRAR($G$4,A2,1),$H$4),0)="r1","r1",SI(SI.ERROR(EXTRAE(A2,ENCONTRAR($G$5,A2,1),$H$5),0)="suplementaciones","suplementaciones",SI(SI.ERROR(EXTRAE(A2,ENCONTRAR($G$6,A2,1),$H$6),0)="vunas","vacunas","NO EXISTE")))))
Opcion 2
SI
ESERROR
HALLAR
La formula quedaria asi:
=SI(ESERROR(HALLAR("fertil",A2)),SI(ESERROR(HALLAR("embarazadas",A2)),SI(ESERROR(HALLAR("r1",A2)),SI(ESERROR(HALLAR("suplementaciones",A2)),SI(ESERROR(HALLAR("vunas",A2)),"NO EXISTE","vacunas"),"suplementaciones"),"r1"),"embarazadas"),"fertil")
Función HALLAR busca una cadena de texto dentro de una segunda cadena de texto y devuelven el número de la posición inicial de la primera cadena de texto desde el primer carácter de la segunda cadena de texto.
ESTRUCTURA:
HALLAR(Texto a buscar, Texto donde deseamos encontrar el texto, Posición inicial)
Los dos primeros argumentos son obligatorios, en cambio la posición inicial no, ya que si no la ponemos empezará a contar desde el número 1.
Ejemplo:
En la celda C9 escribimos el texto "#FreelancerExcel". Para saber que posición ocupa dentro de este texto la letra "F" escribir en la celda D9 la siguiente función HALLAR("F",C9), como resultado tendremos 2 que es la ubicación de la letra "F".
