Utilizando formulas y funciones

¿ QUÉ ES UNA FÓRMULA EN EXCEL ?

Las fórmulas en Microsoft Excel son expresiones que se utilizan para realizar hojas de
cálculos o procesamiento de valores, produciendo un nuevo valor que será asignado a la
celda en la cual se introduce dicha fórmula. En una fórmula, por lo general, intervienen
valores que se encuentran en una o más celdas de un libro de trabajo.

Una fórmula: Podría decirse que la formula es el conjunto de números y signos que
agrupados realizan determinada operación ó tarea, o cuando tu introduces los valores y
para usarlas necesitamos de los conectores aritméticos.

Ejemplo

A1+B1=X
Una fórmula es una secuencia formada por valores constantes, referencias a otras celdas,
nombres, funciones, u operadores

Una fórmula se compone de operandos y de operadores.

OPERANDO: Son los datos que necesitamos calcular, y puede ser un valor constante
(por ejemplo =12*5). Puede ser también un rango de celdas, e incluso una función.
OPERADOR: No es más que la forma de indicar a Excel la operación que queremos
realizar sobre un operando (por ejemplo +, %, /, etc.)
Así pues, un ejemplo de fórmula podría ser: =B5*3+A5. En esta sencilla fórmula puedes
ver como existen operandos y operadores según lo explicado en el párrafo anterior. Una
fórmula llegará a ser muchísimo más complicada --según las necesidades del cálculo a
realizar--, pero siempre responderá a estas premisas

QUE ES UNA FUNCION EN EXCEL : Esta compuesta de formulas preestablecidas, que

ayudan a desarrollar los procedimientos matemáticos o cálculos u operaciones, desde las
más simples como sumar hasta las complejas que involucran condicionales y operaciones
lógicas. Ejemplo Raiz(c5+f6+b1+a3), Sumar(a+b+c), Promedio(E6:E8), f=xày (teoría de
conjuntos),

Existen muchos tipos de funciones dependiendo del tipo de operación o cálculo que
realicen. Así hay funciones matemáticas y trigonométricas, estadísticas, financieras, de
texto, de fecha y hora, lógicas, de base de datos, de búsqueda y referencia y de
información
primeras operaciones con formulas y funciones

Para iniciar la aplicación Microsoft Office Excel 2007 con el uso de formulas vamos a
realizar una pequeña tabla donde se determinen los valores por cada producto de nuestra
tienda escolar, con los siguientes datos.
 FUNCIÓN CONCATENAR

La función CONCATENAR permite unir dos o más elementos de texto que están
contenidos en celdas diferentes. También permite unir textos puestos entre comillas
directamente en los argumentos de la función. Su sintaxis es:

=CONCATENAR(Texto1;Texto2;...)

Esta función puede tener como máximo 30 argumentos.

Ejemplos:

Si tenemos en la celda A1 el texto YAHOO y en la celda B5 ARGENTINA, nos queda

YAHOO ARGENTINA en la celda donde se introduce la fórmula. Veamos

Notar que el segundo argumento es un espacio (" ") para separar ambas palabras y que
el formato color no se tiene en cuenta.

Uniendo textos directamente usando comillas

También se pueden concatenar textos, sin usar la función CONCATENAR y empleando

en su lugar el símbolo ampersand ( & ), como podemos ver:
 CONCATENAR MÚLTIPLE

En este tutorial veremos cómo programar una función en Excel que permita
concatenar fácilmente varios elementos al ingresar un rango de datos y no las
celdas uno a uno como nos pide la función CONCATENAR en Excel.

La función CONCATENAR es MUY limitada. Debemos incluir una a una las

celdas que se quieren combinar y no permite utilizar rangos.

Supongamos tenemos las siguientes listas:

Y queremos tener en una única celda, todas las ciudades de cada país
combinadas.

Ejemplo, que para Brasil nos quede: “San Pablo, Belo Horizonte,
Fortaleza, Salvador”.

La función que deberíamos aplicar sería:

• =CONCATENAR (B3;”, “;B4;”, “;B5;”, “;B6;”, “;B7;”.”)

Por ejemplo, para México solo listé tres. Si copiamos la función de arriba en la
columna E, el resultado sería: “México, Monterrey, Guadalajara, , .”

No es lo que estamos buscando, pero para que quede mejor, habría que
corregir la función a mano y escribir:

• =CONCATENAR(E3;”, “;E4;”, “;E5;”.”)

FUNCIÓN SUMA
La función suma es una de las más 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)
Fijémonos 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 columnas dejando 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 queremos 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 más que una suma rápida, esta entrega la suma de los
valores que están 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 estaríamos
ante un anidamiento de funciones, por otro lado los parámetros pueden ser todos lo que
nos hagan falta, o sea

=SUMA(parametro-1, parametro-2,parametro-3,.............,parámetro-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 veamos un ejemplo más 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

Función CONSULTAV explicada

La función CONSULTAV es la manera en que Excel te permite encontrar “una aguja en un

pajar”. Te explicaré mejor con un ejemplo. Supongamos que tenemos una lista de empleados
de nuestra compañía en donde la primer columna indica el número de empleado y la segunda
columna su nombre.

Ejemplo de la función CONSULTAV

Ahora necesitas encontrar el nombre de la persona que tiene el número de empleado 45362
¿Cómo lo haces? Lo adivinaste bien, utilizas la función CONSULTAV para encontrar el
nombre del empleado. Observa la siguiente imagen:
Mi lista de empleados (que es muy pequeña para este ejemplo) se encuentra en el rango
A2:B10 y en la celda D1 he colocado el número de empleado de quien deseo conocer el
nombre.

Parámetros de la función CONSULTAV

El primer parámetro de la función CONSULTAV es el valor que estoy buscando, que en este
caso es el valor de la celda D1 que contiene el número de empleado a encontrar. El segundo
parámetro de la función es el rango de datos que es A2:B10.

El tercer parámetro es muy importante, porque indica la columna que Excel regresará como
resultado. Para este ejemplo el rango de datos tiene dos columnas: la columna 1 es el número
de empleado y la columna 2 es el nombre. Lo que yo necesito es que una vez que Excel
encuentre el número de empleado me regrese el nombre, por lo que le pido que me regrese la
columna 2.

Finalmente el cuarto parámetro FALSO indica que quiero una coincidencia exacta al buscar el
número de empleado especificado. Para encontrar el nombre de otro empleado es suficiente
con cambiar el valor de la celda D1 con el nuevo número de empleado y Excel mostrará el
nombre:
 Nota importante: La función CONSULTAV regresará a su nombre
original (BUSCARV) al momento de instalar el Service Pack 1 de
Office 2010.

FUNCIÓN DE BÚSQUEDA
FUNCIÓN BUSCARV o CONSULTAV
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 está 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
Después coloca la siguiente fórmula en la celda E3 de la Hoja 1:

CONSULTAV

en la que D3 es una referencia donde está el contenido , que en este caso es el valor 2,
aunque hay casos en que por la naturaleza del problema, por ejemplo una consulta, la
referencia puede al principio estar vacía, dando el error #N/A (no aplicable), en el
tutorial ELIMINAR MENSAJE 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.
 Función “SI”
He recibido varias consultas sobre el uso de la función “SI” en Excel.

Es de extrema utilidad. Sirve cuando necesitamos que el resultado de una

celda dependa de alguna condición.

En este tutorial intentaré explicar fácilmente su uso.

La estructura de la función es:

=SI(Prueba_logica;Valor_si_verdadero;Valor_si_falso)

Veamos cómo funciona con un ejemplo:

Supongamos tenemos un cuadro como el de la derecha, con el detalle de

Ejecutivos de venta, las ventas realizadas así como el objetivo y queremos ver
si cumplió o no con él.

En esta instancia es donde realizamos la “función_lógica“.

Una función lógica es una comparación cuyo resultado es Verdadero o Falso.

En este caso necesitamos comparar si las ventas superan (o igualan) al

objetivo. O, alternativamente, si las resta de las ventas – el objetivo es mayor
que cero. Cuestión de gustos.

Se dieron cuenta como en la redacción escrita del problema a resolver

utilizamos la palabra “si”?

Por lo tanto, la función lógica será simplemente:

Función lógica:

Ventas >= Objetivo

 En este caso usamos la combinación de signos “>=” para indicar que el valor
de las ventas debe ser mayor o igual. Igualmente funciona a la inversa “<=”.
Para el caso de “diferente de”, debemos utilizar “<>”.

Si efectivamente las Ventas superan el Objetivo (Ventas>=Objetivo es

VERDADERO), queremos que la celda (de la columna D) nos muestre
“Cumplió”. Y, en caso que no se cumpla (es decir, Ventas>=Objetivo es
FALSO), nos muestre “No cumplió”.

Ya tenemos entonces las tres partes:

• La función lógica: Ventas>=Objetivos

• Valor si Verdadero: Mostrar “Cumplió”

• Valor si Falso: Mostrar “No cumplió”.

La fórmula, para la celda D2 será entonces:

=SI(B2>=C2;”Cumplió”;”No Cumplió”)

Es totalmente equivalente escribirla de la forma:

=SI(B2<C2;”No Cumplió”;”Si Cumplió”) => SI las Ventas son menores al

Objetivo entonces mostrar “No Cumplió”, en caso contrario (es decir, si es
FALSA la comparación Ventas<Objetivo), mostrar “Sí Cumplió”).

Función SI anidada

Se dice que una función está anidada cuando se utiliza una función dentro de
si misma.

Haciendo un poco más complicado el ejemplo anterior, queremos

saber si superó el objetivo y si la diferencia fue mayor de $5.000.

Esto es, tenemos que hacer DOS comparaciones: Si supera el objetivo y si lo

supera por más o menos de $5.000

Empezamos con la función:

=SI(Ventas<Objetivo) Entonces (caso VERDADERO) => “No Cumplió”

De lo contrario (caso FALSO)

=SI(Ventas-Objetivo<5000) Entonces (caso Verdadero) => “Pasó menos de

$5.000″

De lo contrario (caso FALSO) => “Pasó más de 5.000″.

La fórmula completa quedaría para el ejemplo anterior:

=SI(B2<C2;”No Cumplió”;SI(B2-C2<5000;”Pasó menos de $5.000″;”Pasó más

de $5.000″))
 Formato Condicional a partir de una fórmula

Autor: Excelman

Los números se marcan si la columna C no tiene letra

Tenemos dos celdas con números y una tercera con una letra.

Si la tercera columna tiene una letra, entonces los dos valores anteriores quedan
en negro.

Sin embargo, si la tercera celda NO tiene letra, los datos de las dos columnas
anteriores deben marcarse en rojo, tal como se muestra en la figura.

Tenemos entonces que los valores de las columnas A y B cambian de color, pero
están sujetos al valor de la columna C.

Para resolver esto utilizaremos el Formato Condicional y dos funciones que no

hemos visto hasta entonces: NO y ESTEXTO.

Ya los nombres de las funciones les irá dando idea de qué pienso hacer: Dar color
rojo a los datos de las columnas A y B cuando NO ES TEXTO la columna C.

Para hacer esto en Excel, debemos proceder de la siguiente manera:

Seleccionamos el rango de las celdas a las cuales queremos aplicar el formato

condicional. En nuestro caso, es el rango A1 a B5.

Vamos a Formato Condicional y seleccionamos Nueva regla …

En el nuevo Menú, elegimos “Utilice una fórmula que determine las celdas para
aplicar formato”.

En el recuadro inferior, debemos ingresar la fórmula que se debe cumplir para

aplicar la regla. Es donde utilizaremos las dos funciones mencionadas al inicio del
post: NO y ESTEXTO.

ESTEXTO: Es una función que hace simplemente eso: Verifica si el contenido de

las celdas es texto y, en caso de serlo, devuelve VERDADERO y en caso
contrario, FALSO.
NO: Convierte una expresión VERDADERO en FALSO y viceversa. La utilidad que
le vamos a dar es porque necesitamos identificar las celdas que NO sean TEXTO.
Ingresaremos la fórmula =NO(ESTEXTO($C1)). Es MUY importante incluir el
signo “=”.

Igualmente, es MUY importante incluir el símbolo de $ antes de la C, porque la

columna NO cambia (tanto para la columna A como para la columna B la
condición se determina por la columna C), pero NO debemos incluir el símbolo de
$ antes del número de fila (en nuestro caso el 1), porque la condición SÍ cambia a
medida que cambiamos de columna.

Estas definiciones son críticas para obtener los resultados que necesitamos.

A continuación seleccionamos “Formato … ” y determinamos el formato que

queremos darle a los números. En este caso, seleccioné simplemente “Negrita” y
color “Rojo“.
Ya veremos pintados de rojos los valores de las columnas A y B que no tienen
texto en la columna C.

FORMATO CONDICIONAL EN EXCEL 2010

CAMBIOS Y MEJORAS

En la nota sobre formato condicional personalizado con iconos pasé por alto las
diferencias y mejoras introducidas en la versión 2010 de Excel (gracias a Carola
por llamar mi atención sobre el error). Si bien los cambios y mejoras son menos
dramáticos de los introducidos en Excel 2007, vale la pena pasar revista a las
diferencias entre Excel 2007 y Excel 2010 en lo que a formato condicional se
refiere.

En esta nota veremos los cambios en el formato condicional con conjuntos de

iconos. Esta funcionalidad fue introducida en Excel 2007
La limitación en Excel 2007 es que no podemos combinar entre iconos que
pertenecen a distintos conjuntos. En Excel 2010 podemos cambiar el icono de
cada una de las condiciones

Esta funcionalidad incluye la posibilidad de determinar que bajo determinada

condición no aparezca ningún icono
Esto es útil cuando queremos que aparezca un icono sólo cuando se cumple una
determinada condición, por ejemplo los tres mejores meses de ventas

Otro cambio es la posibilidad de elegir el conjunto de iconos visualmente desde

una lista desplegable
Otra herramienta introducida en Excel 2007 y mejorada en Excel 2010 son las
barras de datos.

En este ejemplo aplicamos formato condicional con barra de datos en la versión

Excel 2007

Podemos ver que las proporciones entre las barras del mejor mes (Agosto) y el
peor (Setiembre) no concuerdan con la diferencia entre los números. Las barras
de datos en Excel 2007 no sirven para comparar valores. Este problema fue
solucionado en Excel 2010. Los mismos datos en Excel 2010 se ven así
 Otras mejoras en formato condicional con barras de datos son:

• Posibilidad de elegir entre relleno degradado y relleno sólido (en Excel 2007 sólo
degradado)
• Posibilidad de poner borde a la barra
• Posibilidad de representar valores negativos en forma efectiva

Todos los derechos de autor.

[Link]
Formato Condicional
Se utilizara el formato condicional, para no visualizar en la hoja, el error N/A al instante de imprimir.

1. Seleccionar el conjunto de celdas D8 hasta J31.

2. Hacer clic sobre la pestaña inicio.
3. Sobre la opción formato condicional. Dar clic.
4. Del menú visualizado hacer clic en nueva regla.

SENA Servicio Nacional de Aprendizaje

Virtual Conocimiento para todos los Colombianos
5. Seleccionar el tipo de regla “aplicar formato a las celdas que contengan”.
6. Seleccionar errores.
7. Hacer clic en formato.
8. En la ventana desplegada, en la opción color, seleccionar blanco.
9. Aceptar en la ventana formato de celdas.
10. Aceptar en la ventana nueva regla de formato.

NOTA: Se utiliza el formato condicional para que los errores N/A,

no se vean en la cotización, ya que al instante de imprimir, será
visible.

SENA Servicio Nacional de Aprendizaje

Virtual Conocimiento para todos los Colombianos
 SUMA

Se ingresaran, funciones para la ejecución de cálculos.

5. Ubicar la celda J32 e ingresar la formula.

=SUMA(J7:J31). Oprimir enter para aceptar.
6. Sobre la celda, se visualiza el error N/A (NO APLICA).

Nótese que el error aparece a que las celdas a J8 Hasta J31 no tienen un valor numérico, solo
el error N/A.

SENA Servicio Nacional de Aprendizaje

Virtual Conocimiento para todos los Colombianos
[Link]

Se utiliza la función [Link], para no ver el error N/A en la hoja, simplemente cero (0)

1. Seleccionar la celda J7.

2. Hacer clic en la barra de formulas.
3. Modificar la formula por. =[Link](C7*H7;0)
4. Copiar el valor a las celdas J8 hasta J31.

Recuerde: Para copiar los valores de celda, ubicar el cursor sobre la esquina inferior derecha de
la celda que contiene la formula, cuando cambie a flecha, arrastrar y soltar en la celda deseada.

SENA Servicio Nacional de Aprendizaje

Virtual Conocimiento para todos los Colombianos
 Función SUMAPRODUCTO y promedios
ponderados
En varias ocasiones, el calcular un promedio simple o aritmético (es decir,
dividir la suma de los números entre la cantidad de elementos), no es
representativa del comportamiento de la situación que estamos analizando.

Un ejemplo habitual de esta situación es, por ejemplo, cuando tenemos

cantidades vendidas y precios.

Veamos un caso extremo:

Tenemos 99 unidades de un producto vendidos a $1 y 1 unidad vendida a $9.

La tarifa promedio, según promedio simple, sería ($9 + $1) / 2 = $5.

Pero la realidad es muy distinta.

• 99 unidades a $1 nos da $99

• 1 unidad a $9 da $9

• En total se vendieron 100 unidades por $108.

• El precio promedio por unidad es $108/100 = $1,08

El promedio simple de tarifa da $5. El promedio calculado por las ventas da

$1,08. Vaya diferencia!!

¿Por qué esta diferencia? Porque el promedio simple, el tradicional que

conocemos, NO funciona cuando los valores a promediar (en nuestro caso,
tarifas), tienen diferentes frecuencias (la tarifa de $1 se repite 99 veces
mientras que la de $9 se repite una sola vez).

En este tutorial, veremos cómo utilizar la función SUMAPRODUCTO de Excel a

través de este ejemplo para comprender mejor su funcionamiento.
 Vamos a trabajar con el ejemplo de la derecha. En la columna B tenemos un
detalle de la cantidad vendida de cada producto y en la C el precio.

En la celda C9 vamos a calcular el promedio simple usando la función

PROMEDIO:

=PROMEDIO(C2:C6)

El promedio “ponderado”, es decir, aquel en el cual a cada observación se le da

el peso (“pondera”) por la cantidad de veces que se repite, se calcula:

Esto es, la suma de la multiplicación

(producto) de cada cantidad (frecuencia) por elprecio correspondiente,
dividido por la suma de las cantidades.

SUMAPRODUCTO en Excel
Finalmente llegamos al punto donde utilizamos la función.

La función SUMAPRODUCTO hace justamente las operaciones de la parte

superior de la función mencionada: suma el producto (multiplicación) de
elementos de distintos rangos.

La función tiene la siguiente estructura:

SUMAPRODUCTO(rango1;rango2;…)

Es decir, permite realizar el cálculo de varios rangos (no solo 2 como en

nuestro ejemplo).

También es muy útil para realizar cálculos aplicando filtros o restricciones, pero
esto lo dejaré para otro tutorial.
De esta forma, calcularemos nuestro promedio ponderado en la celda C10
utilizando la fórmula:

=SUMAPRODUCTO(B2:B6;C2:C6)/SUMA(B2:B6)

Donde la función SUMAPRODUCTO calculará:

B2*C2+C3*C3+B4*C4+B5*C5+B6*C6.

Nos ahorra tener que introducir todas estas multiplicaciones a mano, o contar
con una columna adicional (en nuestro caso la celda de ventas contiene la
multiplicación de cada par cantidad-precio).

La tarifa según promedio simple nos dio $5. Según promedio ponderado $2.

Hagamos una simple comprobación. Si queremos estimar cuánto dinero se

vendió, usaríamos la aproximación cantidad vendida x precio promedio.

Utilizando el promedio lineal, nos da 178 unidades s $5 = $890 (!!!!!)

Mientras si usamos el promedio ponderado, tendremos 178 unidades x $2 =

$356 (que corresponde exactamente a las ventas realizadas)