• Author / Uploaded
• Jose123457777

Citation preview

FUNCIONES EN EXCEL 2003 Y EXCEL 2007 INTRODUCCION Esta página está dedicada a definicion funciones de EXCEL, EXCEL 2007 y EXCEL 2003, con tutoriales paso a paso, ya que las FUNCIONES DE EXCEL son uno de los temas mas importantes de este valioso programa y cuando se combinan entre si, efectuando anidamiento de funciones tienen una potencia enorme, tan es así que podríamos pasarnos escribiendo libros enteros de funciones sin que el tema quede agotado.. Empezaré diciendo que Excel tiene más de 327 funciones divididas por categorías (sin contar claro está con las que el usuario puede definir), estas son:

1.- Funciones de complemento y automatización 2.- Funciones de cubo 3.- Funciones de base de datos 4.- Funciones de fecha y hora 5.- Funciones de ingeniería 6.- Funciones financieras 7.- Funciones de información 8.- Funciones lógicas 9.- Funciones de búsqueda y referencia 10.- Funciones matemáticas y trigonométricas 11.- Funciones estadísticas 12.- Funciones de texto Aquí les dejo un archivo con la mayoría de ellas, con traducción ingles/castellano

DEFINICION DE FUNCIONES Empezaré con las mas comunes para luego ir agregando. La clasificación que hago es un tanto personal, como podrán ver, hay algunas funciones puestas en varias categorías en forma simultanea.

LAS MAS COMUNES A estas funciones se puede acceder en forma rápida desde el icono ya que haciendo clic en el triangulito se despliega un menú emergente con botones a todas ellas( Excel 2007 y 2003 ). SUMA PROMEDIO MAX MIN CONTAR FUNCIONES QUE SUMAN y CUENTAN( algunas de acuerdo a una condición)

SUMA CONTAR

SUMAR.SI SUMAR.SI.CONJUNTO CONTAR.SI CONTAR.SI.CONJUNTO CONTAR.BLANCO CONTARA FUNCIONES CONDICIONALES

SI CONTAR.SI CONTAR.SI.CONJUNTO SUMAR.SI SUMAR.SI.CONJUNTO

FUNCIONES LOGICAS Una proposición lógica es una afirmación que puede se VERDADERA o FALSA pero no ambas a la vez y justamente los argumentos de las funciones lógicas son proposiciones lógicas. También las funciones lógicas devuelven resultados VERDADERO o FALSO. Esta es una definición que se cumple en forma estricta solamente para las siguientes funciones:

Y O NO

FUNCIONES DE BÚSQUEDA Y REFERENCIA

BUSCARV DESREF INDICE COINCIDIR

INDIRECTO

HIPERVINCULO ELEGIR

FUNCIONES DE INFORMACION

ESERROR FUNCIONES DE TEXTO CONCATENAR

FUNCIONES MATEMATICAS SUMAPRODUCTO

FUNCIONES DE FECHA Y HORA

HOY

FUNCION SUMA La función suma es una de las mas usadas, prueba de esto es que Excel tiene un icono especial para efectuar sumas rápidas, con esto quiero decir que no hace falta poner =SUMA() para efectuar la suma de un cierto rango, lo que por cierto ahorra tiempo y evita errores, este icono se llama autosuma

y para usarlo basta con

seleccionar el rango que queremos sumar hacer clic en y el resultado aparece en la celda inmediatamente inferior al rango, aunque si queremos el resultado en otro lado basta con seleccionar la celda, luego el rango que queremos sumar, Enter y listo( Excel 2003 y 2007)

fijemonos en la barra inferior de Excel y veremos siempre la suma ,el promedio y cuantas celdas se seleccionaron para efectuar la suma(Excel 2007).

Si tenemos varias columnas, seleccionamos esas columnasdejando espacio para poner los totales

y luego aplicamos autosuma

Si no seleccionamos nada Excel interpreta que lo que queremos sumar es la secuencia de números puesta verticalmente u horizontalmente( tiene que estar activa una cela en la columna o fila respectivamente), en estos casos inserta automáticamente la función suma con los rangos que interpreta querenos sumar, para obtener la suma solo nos falta apretar ENTER, para aclarar las cosas veamos los siguientes gráficos.

apretamos ENTER y obtenemos la suma

y si la secuencia es horizontal

apretamos ENTER y obtenemos la suma

También podemos utilizar la autosuma para obtener subtotales por ejemplo si tenemos la siguiente tabla

seleccionando los rangos de cada subtotal apretando la tecla control

y obtenemos los subtotales apretando e icono de autosuma

Si hubiéramos una gran cantidad de subtotales, hacerlo así sería muy engorroso entonces lo que hacemos es seleccionar el rango adecuado y usar "ir a" apretando F5( O) con lo que aparece

luego oprimimos Especial y vamos al siguiente pantalla en la que tildamos solo constantes y números

esto hace que se seleccionen solamente los números de los subtotales

luego apretamos en autosuma y obtenemos los subtotales

En Excel 2007

aparece en la solapa Inicio y en Excel 2003 en la barra iconos.

Pero la función suma es mucho mas que una suma rápida, esta entrega la suma de los valores que estan en las celdas a que hacen referencia los argumentos y estos pueden ser: un valor numérico, la referencia o el nombre de una celda, la referencia o el nombre de un rango, una fórmula matemática o una función, en este último caso estariamos ante un Anidamiento de funciónes , por otro lado los parámetros pueden ser todos lo que nos hagan falta, o sea =SUMA(parametro-1, parametro-2,parametro-3,.............,parametro-n..) Veamos el siguiente ejemplo

donde se ve que la expresión =SUMA(A1:C4;E1;230;E4*F4) ubicada en la celda A7 devuelve la suma de las celdas 1- A1,B1,C1,A2,B2,C2,A3,B3,C3.A4,B4,C4 2- La celda E1 3- El número 230 4- El producto de las celdas E4 y F4

Por último veanos un ejemplo mas práctico Se tiene una tabla con las ganancias semestrales por cereales

Se quieren los totales anuales por granos, los totales por semestre y el total final descontando los impuestos de un 36%

Los totales se hacen con autosuma y el total final con =SUMA(d4;-E4). Los impuestos se calcularon con una fórmula (=D4*$A$11) pero esto es otro tema

FORMULAS INTRODUCCION: Una FORMULA es una expresión que sirve para realizar cálculos produciendo un valor que se asigna a la celda en la que se introduce dicha expresión. Las fórmulas estan formadas por operadores( símbolos matemáticos ) y operandos, que pueden ser números, referencias a celdas y funciónes, estas funciónes deben entregar un valor numérico o lógico, esto último es debido a que para Excel VERDADERO es equivalente al numero uno y FALSO al cero. Para evitar las confusiones que pueden surgir en el orden en que se realizan las operaciónes, existe una jerarquía que determina que operaciónes se realizan primero y cuales despues, tal jerarquía se denomina GERARQUIA DE PRECEDENCIA DE OPERADORES y esta en la siguiente tabla TABLA DE PRECEDENCIAS

por ejemplo: -5+7*8 da como resultado 51, pues se realiza primero el producto(56) y luego se resta el 5. Este orden se puede alterar colocando paréntesis, ya que estos tienen el orden de precedencia mas alto, veamos como se puede transformar una expresión según donde se coloque el paréntesis, veamos

en la primera fórmula la prioridad es del producto que tiene un orden de precedencia mayor y el resultado es 61.Esto se altera en la segunda fórmula poniendo paréntesis, se efectúa primero la suma y luego el producto y el resultado es 96

FORMULAS INTRODUCCION: Una FORMULA es una expresión que sirve para realizar cálculos produciendo un valor que se asigna a la celda en la que se introduce dicha expresión. Las fórmulas estan formadas por operadores( símbolos matemáticos ) y operandos, que pueden ser números, referencias a celdas y funciónes, estas funciónes deben entregar un valor numérico o lógico, esto último es debido a que para Excel VERDADERO es equivalente al numero uno y FALSO al cero. Para evitar las confusiones que pueden surgir en el orden en que se realizan las operaciónes, existe una jerarquía que determina que operaciónes se realizan primero y cuales despues, tal jerarquía se denomina GERARQUIA DE PRECEDENCIA DE OPERADORES y esta en la siguiente tabla TABLA DE PRECEDENCIAS

por ejemplo: -5+7*8 da como resultado 51, pues se realiza primero el producto(56) y luego se resta el 5. Este orden se puede alterar colocando paréntesis, ya que estos tienen el orden de precedencia mas alto, veamos como se puede transformar una expresión según donde se coloque el paréntesis, veamos

en la primera fórmula la prioridad es del producto que tiene un orden de precedencia mayor y el resultado es 61.Esto se altera en la segunda fórmula poniendo paréntesis, se efectúa primero la suma y luego el producto y el resultado es 96

FUNCION MAX() La función MAX() da el valor máximo de un rango o rangos y/o de referecias a celdas, como se ve en la tabla siguiente

En el apartado Anudamiento de funciones hay un ejemplo de Y() anidada con MIN() y MAX().

ANIDAMIENTO DE FUNCIONES INTRODUCCION

IR A TUTORIALES

El anidamiento de funciónes junto con la programación VBA es lo que mas potencia da al programa EXCEL, aquí todo depende de a donde nos pueda llevar nuestra habilidad e imaginación y es donde se convierte en un programa muy versátil pudiendo abarcar diversas disciplinas como son la Ingeniería, Estadística, Matemática, Finanzas, Contabilidad por decir algunas que se me ocurren. El anidamiento de funciónes no es otra cosa que ubicar una función en el argumento de otra de forma adecuada, dicho así parece muy simple pero veremos que la cosa puede complicarse mucho dado que la anidación pude hacerse en muchos niveles e involucrar a muchas funciónes dando expresiones muy largas y difíciles de manejar, esto dista mucho de querer desalentar, mas bien insta a la curiosidad y a la práctica. Empezaremos por lo mas simple para ir a lo mas complejo en forma progresiva pero antes voy a aclarar esto de los niveles y el límite que hay y la forma adecuada de hacerlo, para esto ,como siempre nada mejor que un ejemplo

Se sabe que el promedio de las temperaturas del año en curso de la provincia de Misiónes es de 27º y se tiene una tabla con los promedios de las temperaturas de los meses del año anterior, se quiere saber si es verdadero que los 27º entran en el rango de los promedios de los meses del año anterior

FUNCION MIN() La función MIN() da el valor mínimo de un rango o rangos y/o de referecias a celdas, como se cave en la tabla siguiente

En el apartado Anudamiento de funciones hay un ejemplo de Y() anidada con MIN() y MAX().

FUNCION CONTAR Para contar valores numéricos Y fechas en un rango o lista de argumentos, se utiliza la función CONTAR, veamos la siguiente tabla

como se ve la función está en la celda C9 de valor 8 que es el resultado de contar los números y fechas del rango C1:D5 y de las referencias C7 Y D7 , se puede apreciar que se ignoran en la cuenta los blancos , los valores lógicos , lo errores y las cadenas de caracteres. Para contar celdas en blanco disponemos de función CONTAR .BLANCO. Para contar celdas que no están vacías CONTARA

FUNCION CONTAR.BLANCO Cuenta los espacios en blanco que hay en un rango

como se ve la función CONTAR.BLANCO que está en la celda C9 cuenta las celdas en blanco que están en el rango C1:D7 que son 3.

FUNCION CONTARA Cuenta todas las celdas que no están vacías de un rango, veamos este ejemplo

en este caso el rango C1:D7 tiene 12 celdas pero como CONTARA no cuenta la vacía, el resultado de la función, que está en la celda C9 es 11. FUNCIONES QUE SUMAN y CUENTAN( algunas de acuerdo a una condición)

FUNCION SUMA La función suma es una de las mas usadas, prueba de esto es que Excel tiene un icono especial para efectuar sumas rápidas, con esto quiero decir que no hace falta poner =SUMA() para efectuar la suma de un cierto rango, lo que por cierto ahorra tiempo y evita errores, este icono se llama autosuma

y para usarlo basta con

seleccionar el rango que queremos sumar hacer clic en y el resultado aparece en la celda inmediatamente inferior al rango, aunque si queremos el resultado en otro lado basta con seleccionar la celda, luego el rango que queremos sumar, Enter y listo( Excel 2003 y 2007)

fijemonos en la barra inferior de Excel y veremos siempre la suma ,el promedio y cuantas celdas se seleccionaron para efectuar la suma(Excel 2007).

Si tenemos varias columnas, seleccionamos esas columnasdejando espacio para poner los totales

y luego aplicamos autosuma

Si no seleccionamos nada Excel interpreta que lo que queremos sumar es la secuencia de números puesta verticalmente u horizontalmente( tiene que estar activa una cela en la columna o fila respectivamente), en estos casos inserta automáticamente la función suma con los rangos que interpreta querenos sumar, para obtener la suma solo nos falta apretar ENTER, para aclarar las cosas veamos los siguientes gráficos.

apretamos ENTER y obtenemos la suma

y si la secuencia es horizontal

apretamos ENTER y obtenemos la suma

También podemos utilizar la autosuma para obtener subtotales por ejemplo si tenemos la siguiente tabla

seleccionando los rangos de cada subtotal apretando la tecla control

y obtenemos los subtotales apretando e icono de autosuma

Si hubiéramos una gran cantidad de subtotales, hacerlo así sería muy engorroso entonces lo que hacemos es seleccionar el rango adecuado y usar "ir a" apretando F5( O) con lo que aparece

luego oprimimos Especial y vamos al siguiente pantalla en la que tildamos solo constantes y números

esto hace que se seleccionen solamente los números de los subtotales

luego apretamos en autosuma y obtenemos los subtotales

En Excel 2007

aparece en la solapa Inicio y en Excel 2003 en la barra iconos.

Pero la función suma es mucho mas que una suma rápida, esta entrega la suma de los valores que estan en las celdas a que hacen referencia los argumentos y estos pueden ser: un valor numérico, la referencia o el nombre de una celda, la referencia o el nombre de un rango, una fórmula matemática o una función, en este último caso estariamos ante un Anidamiento de funciónes , por otro lado los parámetros pueden ser todos lo que nos hagan falta, o sea =SUMA(parametro-1, parametro-2,parametro-3,.............,parametro-n..) Veamos el siguiente ejemplo

donde se ve que la expresión =SUMA(A1:C4;E1;230;E4*F4) ubicada en la celda A7 devuelve la suma de las celdas 1- A1,B1,C1,A2,B2,C2,A3,B3,C3.A4,B4,C4 2- La celda E1 3- El número 230 4- El producto de las celdas E4 y F4

Por último veanos un ejemplo mas práctico Se tiene una tabla con las ganancias semestrales por cereales

Se quieren los totales anuales por granos, los totales por semestre y el total final descontando los impuestos de un 36%

Los totales se hacen con autosuma y el total final con =SUMA(d4;-E4). Los impuestos se calcularon con una fórmula (=D4*$A$11) pero esto es otro tema

FUNCION CONTAR Para contar valores numéricos Y fechas en un rango o lista de argumentos, se utiliza la función CONTAR, veamos la siguiente tabla

como se ve la función está en la celda C9 de valor 8 que es el resultado de contar los números y fechas del rango C1:D5 y de las referencias C7 Y D7 , se puede apreciar que se ignoran en la cuenta los blancos , los valores lógicos , lo errores y las cadenas de caracteres. Para contar celdas en blanco disponemos de función CONTAR .BLANCO. Para contar celdas que no están vacías CONTARA

FUNCION SUMAR.SI INTRODUCCION La función SUMAR.SI permite sumar valores de un rango de acuerdo a un criterio o condición.

La función SUMAR.SI tiene 3 parámetros: El primero es la referencia o el rango que contiene los valore sobre los que se evaluará la condición. El segundo es el que contiene el criterio a aplicar con el objeto de determinar que se suma y que no El tercero es opcional, esto quiere decir que si la condición esta en el mismo rango donde se efectúa la suma, no hace falta el tercer parámetro, pero si el criterio esta en un rango y donde se hace la suma en otro (u otros )rangos, entonces tiene que colocarse el tercer parametro. Para aclarar las cosas que mejor que un ejemplo: Supongamos que una inmobiliaria tiene un listado con el valor de las propiedades que se vendieron en Enero y quiere saber la suma de aquellas que superaron los $160.000, para obtener la respuesta se emplea la función SUMAR.SI como se muestra en el gráfico

En este caso con dos parámetros alcanza puesto que el criterio esta en la rango E2:E5, que el mismo rango donde se efectúa la suma con la condición dada y no hace falta poner =SUMA(E2:E5;">160000";E2:E5)..Si en cambio tenemos esta otra tabla

aquí si hace falta el tercer parámetro ya que el rango donde se efectúa el criterio (D2:D5) no es el mismo que el rango donde se efectúa la suma (E2:E5). Dejo como ejercicio averiguar las comisiónes que se cobran al vendedor por propiedades cuyo costo es inferior a $ 400.000.

FUNCION SUMAR.SI.CONJUNTO Excel 2007 incorpora una función nueva, SUMAR.SI.CONJUNTO, que es parecida a SUMAR.SI, que suma celdas teniendo en cuenta un solo criterio, en tanto que SUMAR.SI.CONJUNTO, suma celdas teniendo en cuenta múltiples criterios. SINTAXIS: SUMAR.SI.CONJUNTO(rango de sumas; criterio_rango1; criterio1; criterio rango2; criterio2..) donde: rango de sumas: es un argumento obligatorio, en el que se suman una o mas celdas. criterio_rango1: es un argumento requerido que es el primer rango en el que se evalúa el criterio asociado ( criterio1). criterio1: argumento obligatorio que es un criterio asociado a criterio_rango1 en la forma de número, expresión, referencia de celda o texto, que define en cual celda, en el rango de sumas, se adicionará debido al criterio_rango1. Los otros argumentos son opcionales. Veremos un ejemplo donde se consideran 3 criterios: Un negocio de artículos de computación y electrodomésticos cuenta con la siguiente tabla

y se quiere saber cual fue la suma de las ventas de Juan López, en el sector Electrodomésticos con montos que superaron los 200 $. Como se ve los criterios son:

  

"Juan López" "Electrodomésticos" ">200

entonces para responder a lo que el negocio quiere saber , se introduce la tabla en una Hoja de Excel y se usa la fórmula ( en la tabla se resaltan las filas que cumplen los 3 criterios)

que insertamos en la celda D24, dando el resultado 4200$, como se puede ver

FUNCION CONTAR.SI Esta función es una combinación de las funciónes CONTAR y SI , tiene dos argumentos, el primero es el rango cuyas celdas se desean contar y el segundo es el criterio que determina que celda sera contada o no

con esta misma tabla podríamos preguntar cuántos hombres hay

FUNCION CONTAR Para contar valores numéricos Y fechas en un rango o lista de argumentos, se utiliza la función CONTAR, veamos la siguiente tabla

como se ve la función está en la celda C9 de valor 8 que es el resultado de contar los números y fechas del rango C1:D5 y de las referencias C7 Y D7 , se puede apreciar que se ignoran en la cuenta los blancos , los valores lógicos , lo errores y las cadenas de caracteres. Para contar celdas en blanco disponemos de función CONTAR .BLANCO. Para contar celdas que no están vacías CONTARA

FUNCION SI La función SI sirve para tomar decisiones de acuerdo a una condición, por eso podríamos decir que es una función condicional,siendo la condición el resultado de la evaluación de una proposición lógica ( VERDADERO o FALSO), es decir; si el resultado es VERDADERO se hace una cosa, y si es FALSO se hace otra.. Esta función tiene 3 argumentos

Ejemplo: De acuerdo a un informe volcado a una tabla una empresa quiere saber en que meses tuvo pérdidas o ganancias

para lo cual estos datos se ponen en una Hoja de Excel y se usa la función SI de la siguiente manera

arrastrando la función hasta completar, el resultado final es

a esta empresa no le fue demasiado bien-

FUNCION CONTAR.SI.CONJUNTO Excel 2007 incorpora una función nueva, CONTAR.SI.CONJUNTO, que es parecida a CONTAR.SI, que cuenta celdas teniendo en cuenta un solo criterio, en tanto que CONTAR.SI.CONJUNTO, cuenta celdas teniendo en cuenta múltiples criterios. SINTAXIS: CONTAR.SI.CONJUNTO(rango1; criterio1; rango2; criterio2…) pudiendo especificarse mas de 127 rangos/criterios. donde : rango1: es el rango donde se cuentan las celdas, debido al criterio1, criterio2, etc. Los primeros dos argumentos son obligatorios. Veremos un ejemplo donde se consideran 3 criterios: Un negocio de artículos de computación y electrodomésticos cuenta con la siguiente tabla

y quiere saber cuantas veces el vendedor Juan Lopez aparece en la misma, en el sector Electrodomésticos con ventas que superan los 200 $. Como se ve los criterios son:

  

"Juan López" "Electrodomésticos" ">200

entonces para responder a lo que el negocio quiere saber , se introduce la tabla en una Hoja de Excel y se usa la fórmula ( en la tabla se resaltan las filas que cumplen los 3 criterios)

que insertamos en la celda D23, dando el resultado 3, como se puede ver

FUNCION CONTAR.SI Esta función es una combinación de las funciónes CONTAR y SI , tiene dos argumentos, el primero es el rango cuyas celdas se desean contar y el segundo es el criterio que determina que celda sera contada o no

con esta misma tabla podríamos preguntar cuántos hombres hay

FUNCION CONTAR.BLANCO Cuenta los espacios en blanco que hay en un rango

como se ve la función CONTAR.BLANCO que está en la celda C9 cuenta las celdas en blanco que están en el rango C1:D7 que son 3.

FUNCION CONTARA Cuenta todas las celdas que no están vacías de un rango, veamos este ejemplo

en este caso el rango C1:D7 tiene 12 celdas pero como CONTARA no cuenta la vacía, el resultado de la función, que está en la celda C9 es 11. FUNCIONES CONDICIONALES

FUNCION SI La función SI sirve para tomar decisiones de acuerdo a una condición, por eso podríamos decir que es una función condicional,siendo la condición el resultado de la evaluación de una proposición lógica ( VERDADERO o FALSO), es decir; si el resultado es VERDADERO se hace una cosa, y si es FALSO se hace otra.. Esta función tiene 3 argumentos

Ejemplo: De acuerdo a un informe volcado a una tabla una empresa quiere saber en que meses tuvo pérdidas o ganancias

para lo cual estos datos se ponen en una Hoja de Excel y se usa la función SI de la siguiente manera

arrastrando la función hasta completar, el resultado final es

a esta empresa no le fue demasiado bien-

FUNCION CONTAR.SI Esta función es una combinación de las funciónes CONTAR y SI , tiene dos argumentos, el primero es el rango cuyas celdas se desean contar y el segundo es el criterio que determina que celda sera contada o no

con esta misma tabla podríamos preguntar cuántos hombres hay

FUNCION CONTAR.SI.CONJUNTO Excel 2007 incorpora una función nueva, CONTAR.SI.CONJUNTO, que es parecida a CONTAR.SI, que cuenta celdas teniendo en cuenta un solo criterio, en tanto que CONTAR.SI.CONJUNTO, cuenta celdas teniendo en cuenta múltiples criterios. SINTAXIS: CONTAR.SI.CONJUNTO(rango1; criterio1; rango2; criterio2…) pudiendo especificarse mas de 127 rangos/criterios. donde : rango1: es el rango donde se cuentan las celdas, debido al criterio1, criterio2, etc. Los primeros dos argumentos son obligatorios. Veremos un ejemplo donde se consideran 3 criterios: Un negocio de artículos de computación y electrodomésticos cuenta con la siguiente tabla

y quiere saber cuantas veces el vendedor Juan Lopez aparece en la misma, en el sector Electrodomésticos con ventas que superan los 200 $. Como se ve los criterios son:

  

"Juan López" "Electrodomésticos" ">200

entonces para responder a lo que el negocio quiere saber , se introduce la tabla en una Hoja de Excel y se usa la fórmula ( en la tabla se resaltan las filas que cumplen los 3 criterios)

que insertamos en la celda D23, dando el resultado 3, como se puede ver

FUNCION SUMAR.SI INTRODUCCION La función SUMAR.SI permite sumar valores de un rango de acuerdo a un criterio o condición.

La función SUMAR.SI tiene 3 parámetros: El primero es la referencia o el rango que contiene los valore sobre los que se evaluará la condición. El segundo es el que contiene el criterio a aplicar con el objeto de determinar que se suma y que no El tercero es opcional, esto quiere decir que si la condición esta en el mismo rango donde se efectúa la suma, no hace falta el tercer parámetro, pero si el criterio esta en un rango y donde se hace la suma en otro (u otros )rangos, entonces tiene que colocarse el tercer parametro. Para aclarar las cosas que mejor que un ejemplo: Supongamos que una inmobiliaria tiene un listado con el valor de las propiedades que se vendieron en Enero y quiere saber la suma de aquellas que superaron los $160.000, para obtener la respuesta se emplea la función SUMAR.SI como se muestra en el gráfico

En este caso con dos parámetros alcanza puesto que el criterio esta en la rango E2:E5, que el mismo rango donde se efectúa la suma con la condición dada y no hace falta poner =SUMA(E2:E5;">160000";E2:E5)..Si en cambio tenemos esta otra tabla

aquí si hace falta el tercer parámetro ya que el rango donde se efectúa el criterio (D2:D5) no es el mismo que el rango donde se efectúa la suma (E2:E5). Dejo como ejercicio averiguar las comisiónes que se cobran al vendedor por propiedades cuyo costo es inferior a $ 400.000.

FUNCION SUMAR.SI.CONJUNTO Excel 2007 incorpora una función nueva, SUMAR.SI.CONJUNTO, que es parecida a SUMAR.SI, que suma celdas teniendo en cuenta un solo criterio, en tanto que SUMAR.SI.CONJUNTO, suma celdas teniendo en cuenta múltiples criterios. SINTAXIS: SUMAR.SI.CONJUNTO(rango de sumas; criterio_rango1; criterio1; criterio rango2; criterio2..) donde: rango de sumas: es un argumento obligatorio, en el que se suman una o mas celdas.

criterio_rango1: es un argumento requerido que es el primer rango en el que se evalúa el criterio asociado ( criterio1). criterio1: argumento obligatorio que es un criterio asociado a criterio_rango1 en la forma de número, expresión, referencia de celda o texto, que define en cual celda, en el rango de sumas, se adicionará debido al criterio_rango1. Los otros argumentos son opcionales. Veremos un ejemplo donde se consideran 3 criterios: Un negocio de artículos de computación y electrodomésticos cuenta con la siguiente tabla

y se quiere saber cual fue la suma de las ventas de Juan López, en el sector Electrodomésticos con montos que superaron los 200 $. Como se ve los criterios son:

  

"Juan López" "Electrodomésticos" ">200

entonces para responder a lo que el negocio quiere saber , se introduce la tabla en una Hoja de Excel y se usa la fórmula ( en la tabla se resaltan las filas que cumplen los 3 criterios)

que insertamos en la celda D24, dando el resultado 4200$, como se puede ver

FUNCIONES LOGICAS Una proposición lógica es una afirmación que puede se VERDADERA o FALSA pero no ambas a la vez y justamente los argumentos de las funciones lógicas son proposiciones lógicas. También las funciones lógicas devuelven resultados VERDADERO o FALSO. Esta es una definición que se cumple en forma estricta solamente para las siguientes funciones:

FUNCION Y() La función Y() ,como O() es una función lógica ya que sus argumentos son proposiciones lógicas, la función evalúa los argumentos y devuelve un resultado VERDADERO o FALSO ( aclaro que esta función puede tener un solo parametro sin dar error, aunque no tiene mucho sentido práctico) Su sintaxis es: Y(parámetro1;parámetro2;parámetro3;.....) La función devuelve VERDADERO si la evaluación de todos los parámetros es VERDADERA y dara FALSO si la evaluación al menos uno de sus parámetros es FALSA o si todos son FALSOS.

Veamos un ejemplo

vemos que si cambiamos una desigualdad, o las dos el resultado es FALSO

FUNCION O() Como Y() la función O() es una función lógica, porque sus argumentos son proposiciones lógicas o pruebas lógicas la función evalúa los argumentos y devuelve un resultado VERDADERO o FALSO., su sintaxis es O(parámetro1;parámetro2;parámetro3;.....) La función devuelve FALSO si la evaluación de todos los parámetros es FALSO y dara VERDADERO si la evaluación almenos uno de sus parámetros es VERDADERO o si todos son VERDADEROS. Veamos un ejemplo

FUNCION O() Como Y() la función O() es una función lógica, porque sus argumentos son proposiciones lógicas o pruebas lógicas la función evalúa los argumentos y devuelve un resultado VERDADERO o FALSO., su sintaxis es O(parámetro1;parámetro2;parámetro3;.....) La función devuelve FALSO si la evaluación de todos los parámetros es FALSO y dara VERDADERO si la evaluación almenos uno de sus parámetros es VERDADERO o si todos son VERDADEROS. Veamos un ejemplo

FUNCION NO() La función NO() invierte el valor lógico de los argumentos de las funciónes Y() y O(), por consiguiente se utiliza en combinación con ellas, su sintaxis es: NO(Y(argumento1; argumento2; argumento3;..........)) como puede verse en siguiente ejemplo

como se ve invierte el valor de verdad de los argumentos de Y() FUNCIONES DE BÚSQUEDA Y REFERENCIA

FUNCION BUSCARV La función BUSCARV busca datos que están en primera columna de una tabla(a esta tabla se la denomina matriz de búsqueda o de datos), si el valor es encontrado devuelve el dato asociado (valor que esta en la misma fila que el dato a buscar) de una columna especificada, la sintaxis es;

los primeros tres argumentos son obligatorios y el cuarto es opcional Veamos el siguiente ejemplo: Un profesor tiene una tabla con las notas de un alumno puestas en números y quiere completarla poniendo las notas en palabras

para hacer esto cuenta con otra tabla de equivalencias

vuelca estos datos en un libro de Excel poniendo en la Hoja1 la tabla a completar y en la Hoja2 la tabla con las equivalencias pero sin los rótulos para tener directamente la matriz de datos

despues coloca la siguiente fórmula en la celda E3 de la Hoja 1:

en la que D3 es una referencia donde está el contenido , que en este caso es el valoor 2, aunque hay casos en que por la naturaleza del problema, por ejemplo una consulta, la referencia puede al principio estar vacia, dando el error #N/A (no aplicable), en el tutorial ELIMINAR MESAJE DE ERROR EN BV, daremos una solución a este antiestético mensaje.

A continuación se arrastra la función hasta completar la tabla

en este caso la matriz de búsqueda está en otra hoja, pero puede estar en cualquier lado, incluso dentro de otra tabla.

ELIMINAR MENSAJES DE ERROR EN BUSCARV

En la función BUSCARV muchas veces ocurre que el primer parámetro, que es la referencia a una celda, esta vacío en forma momentánea, por ejemplo cuando se hace una consulta a una base de datos, dando el error #N/A (no aplicable), también este error puede aparecer cuando un valor buscado no está en la matriz de datos. Para eliminar este antiestético mensaje recurrimos a un anidamiento de las funciones SI, ESERROR Y BUSCARV. Por ejemplo: supongamos que una línea aérea dispone de un momnitor, donde los pasajeros pueden consultar el descuento que tiene determinado destino, teniendo el formulario de consulta el siguiente aspecto

Para resolver el problema con Excel introducimos la fórmula de búsqueda en la celda que esta debajo de DESCUENTO y elaboramos de una matriz de búsqueda en la Hoja2 (para que quede oculta a los usuarios) con los destinos y sus descuentos respectivos.

Damos el nombre "descuento" a la matriz de búsqueda e introducimos la función BUACARV con la con la siguiente sintaxis: BUSCARV(C2;descuento;2;FALSO) como se muestra en la figura.

Se ve que BUSCARV da error aunque se introdujo la función correctamente, esto se debe a que la referencia C2 esta vacía porque aun no se introdujo ningún destino. Otro error se presentaría si se introdujera un destino que no esta en "descuento"

ambos mensajes de error desaparecen si se reemplaza a BUSCARV por la fórmula

donde se ha puesto una leyenda de advertencia si el destino no se encuentra

y se ve que funciona para la celda C3 vacía

o para un destino que sí está en la tabla "descuento".

Se preguntarán como ESERROR se da cuenta de cual de los 2 errores se está cometiendo; la respuesta es que no tiene manera de darse cuenta, evitamos que tenga que decidir con C2="" , que es la proposición lógica que junto con SI detectan si hay un blanco y si lo hay colocan otro blanco enD3 , si este error no está, entra a jugar el tercer argumento de SI, donde ya hay solo 2 posibilidades; que este el segundo error o no, si está es detectado por SI, ESERROR Y BUSCARV, descartado el segundo error se hace la búsqueda normal del principio. Las funciones anidadas son muy poderosas, aunque al principio suelen ser un verdadero dolor de cabeza, es por eso que hay algunos métodos para trabajar con ellas, lo que nos dará pie para un tutorial mas adelante.

FUNCION DESREF La función DESREF es tan útil como difícil de entender al principio. DESREF devuelve una referencia a partir de otra que podemos llamar referencia de partida, vamos a tratar de aclarar esto. Recordemos que una referencia es el código de una celda( A1;F3;H124, etc) o el código de un rango de celdas(A3:G6;H5:K7;etc) y aquí pasan dos cosas distintas según se trate de una celda o un rango de celdas; veamos:

Aquí se ve que si se trata de la referencia a una celda Excel devuelve el contenido de esa celda( la fórmula está puesta en el recuadro negro) y en este caso DESREF funciona así

La referencia que devuelve( y por tanto su contenido) es el que resulta de ubicarse en la celda B2 y desplazace x filas y luego x columnas. Concretamente una posibilidad podría ser

y esta expresión puesta en una hoja de Excel ( en la celda de partida B2) resulta en lo siguiente

y obtengo la referencia a una celda, que en este caso es la D5 y por lo tanto su contenido. Hablando en forma simple: parto de B3 me desplazo 3 celdas hacia abajo, luego 2 celdas hacia la derecha devuelve la referencia a la celda D5 y muestra su contenido. Una aclaración: si me desplazo hacia arriba o a la izquierda tengo que anteponer el signo menos y cuidar siempre de no salirme de los límites de la hoja porque sino da error, como podemos ver

la referencia está fuera de la hoja. Cuando nuestra referencia de partida es un rango, la sintaxis de DESREF() cambia un poco

si dejamos los argumentos para celda en cero, partimos de C2:E7 y ponemos 9 para alto y 4 para ancho

colocando la función con sus argumentos en una hoja de Excel

vemos que la referencia de partida, que está resaltada en rojo, se transforma en la referencia C2:F10, resaltada en verde, es como si la referencia de partida se dilatara de C2:E7 a C2:F10, que finalmente es lo que devuelve DESREF, pero como se ve nos da un error y es lógico que así sea pues en este caso Excel no sabe que hacer ¿qué número va a devolver si tiene 3 opciónes?, lo mas coherente es que haga algo con ellos, como sumarlos por ejemplo, pero DESREF por si sola no puede y tiene que anidarse con otras funciónes como SUMA ( que sumaria 42+100+450=592), pero también puede anidarse con PROMEDIO, MAX, MIN, etc y así se elimina el error, como vemos en la siguiente tabla

Se preguntaran; tienen alguna función los argumentos de fila y columna?, la respueste es si, ellos actúan como si el rango de partida fuese una celda( despues de todo una celda es in rango de 1x1) y lo mueven de acuerdo alos valores que adopta: hacia abajo, arriba derecha e izquierda con las mismas reglas de las celdas, luego los parámetros de ancho y alto se encargan de teminar el trabajo contrayendolos, dilatándolos o dejándolos como estan. Veamos un ejemplo en que participan todos los argumentos

Se ve que la referencia al rango final es F8:H12, partiendo del rango inicial C2:D5, y su suma es 140( también está el máximo y mínimo.) En estos momentos no se alcanza a ver el potencial que tiene la función DESREF, una muestra se puede ver en el tutorial RANGOS DINAMICOS

FUNCION INDICE La función INDICE tiene la particularidad de tener dos sintaxis:

 

SINTAXIS MATRICIAL : devuelve un valor o matriz de valores SINTAXIS REFERNCIAL: devuelve un rango o referencia.

SINTAXIS MATRICIAL : En matemática una matriz es un arreglo de números, una tabla de valores o dicho de otra manera una forma de ordenar números identificándolos por su ubicación en filas y columnas o mas precisamente por la intersección de una fila con una columna. En Excel, un rango, es lo que para la matemática una matriz, vayamos a una Hoja de Excel

aquí podemos identificar el rango B1:E5 ( recuadrado en rojo) con una matriz de 4 filas por 4 columnas donde estas se numeran, desde arriba y a la izquierda empezando por 1, en forma creciente, con lo que por ejemplo el numero 567 correspondería a la intersección de la fila 3 con la columna 2, el numero 23 con la intersección de la fila 1 con la columna 4 etc. Esto es lo que hace la función INDICE, devolver el numero que esta en la celda que es la intersección de una fila con una columna, aclaro que en este caso en la celda puede haber un numero, una cadena de caracteres, un mensaje de error, una formula etc. Dicho esto se entenderá mejor la sintaxis de la función INDICE

en este caso INDICE nos devuelve el valor 567

CASOS PARTICULARES

  

Si el primer argumento es una matriz columna ( 1columna por n filas) se omite el argumento columna. Si el primer argumento es una matriz fila ( 1 fila por n columnase) se omite el argumento fila Si el primer argumento es una matriz de n columnas por m filas y se pone cero como segundo argumento INDICE puede devolver una columna o una fila de la matriz n X m,para hacer esto INDICE se introduce como una FORMULA MATRICIAL

SINTAXIS REFERENCIAL: Devuelve la referencia de la celda ubicada en la intersección de una fila y de una columna determinadas de un rango. Si hay mas de un rango se podrá elegir, mediante un tercer argumento llamado área, en cual de ellos se buscará la intersección de filas y columnas, el primer rango se relaciona con el área 1, el segundo rango con el área 2 y así sucesivamente. Para el siguiente ejemplo

la sintaxis es

que da como resultado "autos"

FUNCION COINCIDIR La función COINCIDIR es una función de búsqueda como BUSCARV pero a diferencia de esta, COINCIDIR no devuelve un valor sino una posición dentro de un rango, este rango puede ser una columna o una fila y contener números, palabras o una combinación de ambos . La sintaxis tiene 3 parámetros; el 1º es el valor

referencia cuya posición se quiere encontrar, el 2º el rango y el tercero pude ser -1, 0 y 1, que tomen esos valores va a depender de:   

Si la lista está desordenada el tercer parámetro es 0, dando error si el numero no está en dicha lista. Si la lista está ordenada en forma ascendente el valor es 1 o no se pone ninguno, si el valor no está pero se encuentra entre otros dos , o sea ab se da la posición del valor b)

Vamos a dar ejemplos para aclarar los conceptos: Caso 1: lista desordenada

La lista está desordenada y el valor 325 se encuentra en la lista siendo su posición 2 Caso2: Lista ordenada en forma ascendente

como se ve el valor no está en la lista pero 50,6 o < que el valor actual de la celda "E1" CODIGO PARA COPIAR Y PEGAR Sub SelectCase() A1 = Range("E1").Value Select Case Range("D1").Value Case Is = A1 ActiveSheet.Range("D4").Value = "Los Valores de D1 y E1 son iguales" Case Is > A1 ActiveSheet.Range("D4").Value = "D1 es mayor que E1" Case Is < A1

ActiveSheet.Range("D4").Value = "E1 es mayor que D1" End Select End Sub

La utilidad esencial de esta estructura se manifiesta cuando los casos que se evalúan son mas numerosos como veremos en el siguiente ejemplo. Introducir en una celda la nota de un alumno y en otra una leyenda que diga si esta aplazado, aprobado y en caso de estar aprobado si su nota fue buena, muy buena, distinguida o sobresaliente, teniendo en cuenta que: Aplazado= 1,2,3 Aprobado= 4,5 Bueno= 6,7 Muy bueno= 8 Distinguido= 9 Sobresaliente= 10 se puede responder a estas preguntas aplicando este código

CODIGO PARA COPIAR Y PEGAR Sub SelectCase() a = Range("A2").Value Select Case a

Case 1 To 3 ActiveSheet.Range("B2").Value = "APLAZADO" Range("E1").Select Case 4 To 5 ActiveSheet.Range("B2").Value = "APROBADO" Range("E1").Select Case 6 To 7 ActiveSheet.Range("B2").Value = "BUENO" Range("E1").Select Case 8 ActiveSheet.Range("B2").Value = "MUY BUENO" Range("E1").Select Case 9 ActiveSheet.Range("B2").Value = "DISTINGUIDO" Range("E1").Select Case 10 ActiveSheet.Range("B2").Value = "SOBRESALIENTE" Range("E1").Select End Select End Sub El resultado final queda como se ve en la figura, en este caso hemos agregado un botón para disparar la macro, el cual esta señalado por la flecha roja

Tipos de datos, funciones y subrutinas:

Variables simples y Tipos de datos INTRODUCCION: Una variable simple es una porción de memoria donde se puede almacenar un valor y se les debe dar un nombre para identificarlas entre sí, también están asociados a un tipo de dato. Un tipo de dato es el rango de valores que las variables pueden aceptar o, dicho de otra manera, la cantidad de memoria que se reserva para albergar dicho rango. Por ejemplo la variable de tipo Integer, puede guardar valores en un entorno de -32.768 a +32.787 ocupando 2 bytes. Todos los tipos de datos se resumen en la tabla de abajo.

Funciones INTRODUCCION: Las funciones son un trozo de código inserto en el programa principal que recibe uno, muchos o ningún valor (parámetros o argumentos) y que a diferencia de las subrutinas devuelven un único valor, por esta razón se debe especificar( aunque no es obligatorio)de qué tipo de dato es dicho valor. Las funciones deben tener un nombre que las identifique y así poder ser llamadas por el programa principal. También nos permiten ampliar el listado de las funciones que ya vienen por defecto en Excel, estas funciones están asociadas a un libro o una hoja de Excel y las denominamos Funciones personalizadas, son muy útiles,entre otras cosas, cuando la fórmula que se requiere para resolver un problema usando las funciones propias de las hojas Excel resulta muy larga y complicada, haciendo casi imposible su comprensión. Funciones personalizadas: Empezaremos dando un ejemplo trivial de una función personalizada que podríamos resolver sin recurrir a ellas, este ejemplo es solo para mostrar como funcionan. Supongamos que queremos multiplicar 2 números enteros (en la figura el entero 12 y el 2)que están en las celdas A1 y C1 y que el resultado se devuelva en la celda B3, que obviamente se resuelve con la fórmula =A1*C1 ubicada en la celda B3, mentalmente podemos decir que el resultado es 24

pero lo haremos con una función definida por nosotros programada con VBA que llamaremos MULTIPLICA()

Esta función ya esta disponible junto con las demás,(solamente en la hoja donde la definimos) como se puede ver en el recuadro rojo, también puede verse que su definición está entre Function y End Function

la usamos poniendo dos números como argumento, tal como puede verse

Ahora daremos un ejemplo de una función definida por el usuario que tiene más utilidad. La ferretería EL BULON hace el 20% de descuento si las ventas superan las 100 unidades. Esto se puede resolver con la función SI, como se puede ver en la siguiente figura

Resolveremos el mismo problema definiendo una función, que llamaremos DESCUENTO, con el siguiente código

ya definida la función DESCUENTO() la podemos aplicar

Se ve que el resultado es el mismo, pero si definimos la función DESCUENTO el proceso es mas rápido. Como se habrá notado en el ejemplo anterior, está permitido omitir el tipo de dato, pero es una buena costumbre ponerlos, ya que nos puede ahorrar muchos problemas principalmente en la depuración del código en programas largos (En otros idiomas, como en C, esto es obligatorio) por surte VBA tiene la posibilidad de forzar el hecho de tener que poner obligatoriamente los tipos de datos con la instrucción Opción Éxplicit al principio de cada módulo y mejor aún, se puede configurar el editor de VBA para que se ponga automáticamente esta instrucción en todos los nuevos módulos, esto se hace yendo a Herramientas->Opciones->Solapa “Editor“ y en ella tildando la casilla “Requerir declaración de variables”, como se puede ver en la siguiente imagen

Ahora daré un ejemplo, que si bien puede resolverse con funciones (mas precisamente funciones matriciales) les puedo asegurar que la solución es muy complicada y es aquí donde se ve la verdadera utilidad de las funciones personalizadas. Se trata de la obtención de las iniciarles de un nombre completo.

Su código es el siguiente:

usaremos la funcion recien definida en la siguiente tabla

Llegando a este punto debemos hacer algunas aclaraciones del código En primer lugar podemos ver que se usan las funciones de librería de VBA (funciones que ya están definidas), más precisamente las funciones de cadena Len(), Asc() y Mid(). Una cadena es una serie de caracteres tratados como una misma unidad, estos caracteres pueden ser letras, números o caracteres especiales, como pueden ser /, *, &, %, @ y otros muchos incluido el espacio. A todos estos caracteres les corresponde un código numérico llamado ASCII . Estas funciones sirven para el manejo de cadenas, hay otras que iremos analizando cuando sea necesario. Función Len Esta función nos devuelve el número de caracteres de una cadena, por lo tanto retorna un número entero y recibe un parámetro que es un tipo de dato string. Su sintaxis es: Len([cadena de caracteres])

Ejemplo: Si Texto="hola como estas" Len(Texto) devuelve el valor 15. Función Asc Con la función Asc podemos obtener el código ASCII de un caracter

Función Mid Extrae partes de una cadena y recibe 3 parámetros. Sintaxis: Mid(cadena, inicio, longitud) El parámetro cadena es la cadena a extraer caracteres. inicio es el carácter desde donde se comienza la extracción. longitud es la cantidad de caracteres devueltos a partir del carácter de inicio. Dicho esto el código se puede entender mas claramente 1 Function INICIALES(Texto As String) As String 2 Dim strLong As Long, i As Long 3 Dim textTemp As String 5 strLong = Len(Texto) 7 For i = 1 To strLong 8 If Asc(Mid(Texto, i, 1)) >= 65 And Asc(Mid(Texto, i, 1)) =65 o Asc= 65 And Asc(Mid(Texto, i, 1))