DOMINA LAS FRMULAS DE EXCEL
SERGIO PROPERGOL
WWW.AYUDAEXCEL.COM
AYUDA EXCEL www.ayudaexcel.com
Tal vez conozcas mi trayectoria o quizs esta es
la primera vez que lees sobre m. En cualquier
caso, presentarse nunca est de ms.
Mi nombre es Sergio Propergol y llevo al frente
de Ayuda Excel desde 2006. Antes de comenzar
con este proyecto, trabaje en varias empresas
como administrativo. En todos mis empleos
utilic Microsoft Excel para las ms diversas
tareas: manejo de bases de datos, importacin
y exportacin de archivos, clculos de nminas
y cotizaciones, creacin de paneles de mando,
etc.
Al comprobar que con Excel podra realizar casi todo tipo de tareas, decid
profundizar ms en esta herramienta, adquiriendo varios libros y algn que
otro curso donde aprend los trucos y secretos de la hoja de clculo.
En agosto de 2006 cre Ayuda Excel con la intencin de ayudar a otras
personas,
con
sus
casos
reales.
Desde 2008 ofrezco mis servicios de consultora para empresas y
particulares. Entre los servicios se encuentran el de optimizacin de
plantillas y creacin de aplicaciones basadas en VBA.
La mayor parte de estos aos he trabajado con casos reales, lo que me ha
proporcionado un grado de experiencia bastante bueno.
Con este libro quiero transmitirte los conocimientos que he ido adquiriendo
con Excel, de una forma fcil y rpida, que te permitir afrontar cualquier
reto que te propongas o propongan en tu empresa.
Comenzamos.
Qu es una frmula
En esta primera parte te ensear conceptos generales, para todo tipo de
funciones y frmulas, para aprovechar al mximo su utilidad.
Excel es la mejor herramienta informtica para almacenar y clasificar
informacin, sobre todo nmeros y grficos, pero la verdadera esencia de
Excel est en la posibilidad de utilizar frmulas y funciones para realizar
operaciones con esos datos. Con Excel puedes realizar una cantidad
inimaginable de operaciones de todo tipo.
Las frmulas son el alma de Excel. Sin ellas, no se podra estar hablando del
concepto de hoja de clculo.
Una frmula consiste en un cdigo que se introduce en una celda y que
permite realizar clculos de algn tipo. Este clculo siempre devuelve un
resultado que se muestra en la propia celda. Las frmulas utilizan
operadores y funciones de hoja, junto con valores y textos. Estos valores y
textos pueden introducirse a mano, encontrarse en otras celdas de la misma
hoja, de otras hojas o de otros libros en otros equipos.
Para que lo veas ms claro, una frmula puede contener:
Operadores matemticos, como suma (+), resta (-), multiplicacin (*)
o divisin (/)
Referencias de celdas (incluyendo tambin nombres de celdas y
rangos)
Valores o texto
Funciones de hoja, como SUMA ( ) o PROMEDIO ( )
Despus de haber introducido la frmula, la celda muestra el resultado del
clculo. La frmula introducida aparecer en la barra de frmulas al
seleccionar la celda.
Te muestro varios ejemplos de frmulas:
=150*,05 multiplica 150 por 0,05. Esta frmula utiliza solamente
valores y siempre devuelve el mismo resultado (7,5).
=A1+A2 suma los valores que contienen las celdas A1 y A2. El
resultado puede variar, dependiendo de los valores que contenga
cada celda.
Introducir una frmula
Cuando introduces en una celda el signo igual (=), Excel entiende que es una
frmula lo que vas a introducir, ya que todas las frmulas comienzan igual
(aunque tambin permite comenzarlas con los signos + y -).
=SUMA
+SUMA(A1:A250)
(A1:A250)
Una frmula se puede introducir de forma manual (con el teclado), o
sealando las celdas con el teclado o el ratn, que te ensear en un
momento.
Introducir una frmula manualmente
La verdad es que introducir una frmula con el teclado no tiene mucho
misterio. Simplemente debes situar la celda activa donde quieres
introducirla y escribir el signo igual (=) seguido de la frmula. Mientras
escribes, fjate en que los datos que introduces en la celda, van apareciendo
tambin en la barra de frmulas. Una vez introducida, pulsa Intro.
Atencin:
Como te ensear ms adelante, para introducir una frmula matricial
debes pulsar Ctrl + Mays + Intro.
Introducir una frmula sealando celdas
Otra forma de introducir una frmula es mediante el ratn. Si utilizas el ratn para seleccionar
las celdas o rangos de celdas que formarn parte de la frmula, te ahorrars bastante tiempo y
evitars cometer errores de introduccin. Por ejemplo, para introducir la frmula =A1+A2, sigue
los siguientes pasos:
1. Haz clic en la celda A3 para activarla.
2. Escribe el signo igual (=) para comenzar a introducir los datos.
3. Pulsa dos veces la flecha hacia arriba () hasta llegar a la celda A1.
Observa que cada vez que pulses esta tecla, la celda inmediatamente
superior se mostrar con un borde animado y la referencia a dicha
celda aparecer tanto en la barra de frmulas como en la celda donde
ests introduciendo la frmula.
4. Si en vez del teclado prefieres utilizar el ratn, puedes pulsar
directamente en la celda a introducir.
Escribe el signo ms (+). El borde en movimiento se convertir en un
borde azul.
5. Pulsa una sola vez la flecha hacia arriba () para agregar la celda A2 a
la frmula. Si lo prefieres, puedes sealar directamente la celda con el
ratn.
6. Pulsa Intro para finalizar la introduccin.
Como en el mtodo anterior, al pulsar Intro aparecer el resultado de la
frmula en la celda donde se introdujo, en este caso, en A3.
Este mtodo parece ms complicado que el anterior, pero es mucho ms
rpido y adems, previene los errores, ya que al seleccionar directamente
las celdas con el ratn, tendrs la seguridad de que las referencias son
introducidas correctamente.
Operadores matemticos
Excel utiliza ocho operadores matemticos bsicos:
Nombre
Potencia
Multiplicacin
Divisin
Suma
Resta
Equivalencia
Mayor que
Menor que
Smbolo
^
*
/
+
=
>
<
Ejemplo
10^2=100
10*2=20
10/2=5
10+2=12
10-2=8
10=10
10>2
2<10
Los operadores de la tabla anterior estn ordenados por precedencia, es
decir, que Excel no calcula las expresiones de izquierda a derecha, sino que
unas operaciones se calculan antes que otras, segn el nivel de precedencia.
Los operadores encerrados en la misma celda tienen el mismo nivel de
prioridad. Esto significa que la multiplicacin y la divisin, la suma y la resta,
mayor y menor que, tienen el mismo orden de precedencia.
Te pongo un ejemplo para que no te quede ninguna duda:
Ejemplo:
Cuando se calcula de izquierda a derecha 3+2*4 es igual a 20. 3+2 se calcula
primero y el resultado se multiplica por 4. Sin embargo, la forma correcta de
clculo que tiene esta expresin es: 2*4=8 y 8+3=11. Este es el resultado
correcto, ya que la multiplicacin se calcula antes que la suma.
Puedes alterar el orden de precedencia de los clculos encerrando las
expresiones entre parntesis ( ). Las expresiones que hay dentro de los
parntesis se calculan antes que las que hay fuera.
De esta manera, la expresin ((2+3)*4) ahora s es igual a 20 porque (2+3)=5
se evala en primer lugar, y el resultado se utiliza para calcular el parntesis
exterior (5*4)=20.
Recuerdo que me cost bastante tiempo asimilar el orden de precedencia
sin tener que consultarlo, pero lo consegu acordndome de la
expresin PEDMSR (Parntesis, Exponente, Divisin, Multiplicacin, Suma,
Resta).
La divisin y la multiplicacin tienen el mismo orden de precedencia y
se calculan de izquierda a derecha.
La suma y la resta tienen el mismo orden de precedencia y se calculan
de izquierda a derecha.
Prueba a calcular las siguientes expresiones con Excel para que comprendas
el orden de cada clculo:
Ejemplo:
(3+2)*2=10
3+2*2=7
(10+20)/2=15
10+20/2=20
((4+6)*2)^2=400
4+6*2^2=28
Recuerda que para introducir estas frmulas debes comenzar escribiendo el
signo igual (=).
Frmulas con varias referencias
Excel es capaz de hacer clculos complejos con relativa rapidez. En la
mayora de los casos se utilizan varias celdas con datos y debes saber cmo
hacer referencia a varias celdas al mismo tiempo.
Supn que te dan la siguiente tabla con datos:
Para poder calcular el beneficio por unidad, se han de calcular las ventas y
luego restar el coste de los artculos.
Para hacer esto, primero tienes que calcular las ventas introduciendo la
frmula =B2*C2 en la celda D2.
Ahora sita la celda activa en D3. Si introduces la misma frmula que en la
celda de arriba (unidades vendidas x precio), haciendo referencia a las celdas
correctas.
Fjate en el cdigo de colores de las celdas seleccionadas. Con este cdigo se
hace ms fcil revisar la frmula. Las celdas B3 y C3 son azul y verde
respectivamente.
Una vez que has calculado todos los valores de las ventas, debes calcular el
beneficio obtenido. Para esto debes restar los gastos de las ventas.
Utilizando lo que ya sabes sobre las operaciones y el orden de precedencia,
puedes suponer cul ser la frmula a escribir:
Importe-(Ud.
Vendidas*Coste
por
Ud.+Gastos)=Beneficio
En el ejemplo que te he propuesto, la frmula anterior se traduce en la
siguiente. Observa el cdigo de colores de las celdas:
Una vez introducida la frmula en la primera celda de la columna de
beneficios, contina hasta la ltima fila.
Auditora de frmulas
Como te he dicho anteriormente, las frmulas son una parte indispensable
de Excel. Para reforzar su utilidad, existen una serie de herramientas que
puedes utilizar para comprobar los datos se estn calculando
correctamente. A pesar de que Excel es capaz de manejar frmulas muy
complejas, estas herramientas de auditora son muy fciles de utilizar. Las
puedes encontrar en la ficha Frmulas:
Te indico los conceptos que debes tener en cuenta de estos botones:
Rastrear precedentes
El botn Rastrear precedentes se utiliza para retroceder por todas las
celdas que se utilizan para calcular la frmula actual. En el caso del ejemplo
que has visto antes, Excel calcula cada celda que utiliz en el clculo de la
frmula:
Cada punto azul corresponde a un valor utilizado en la frmula. La flecha
apunta al valor final. (He eliminado el formato de moneda para que veas
claramente la flecha azul).
Si haces clic en el botn Rastrear precedentes de nuevo, los valores que se
utilizaron para calcular todas las frmulas precedentes, tambin se
destacan. Esto significa que podemos seguir haciendo clic para ver ms
niveles de precedencia. En el ejemplo, los valores utilizados para calcular la
columna ventas, se muestran haciendo clic en Rastrear precedentes dos
veces. En este caso, tambin he eliminado el formato de moneda para
distinguir mejor la flecha:
Puedes ocultar estas flechas haciendo clic en Quitar flechas en el
grupo Auditora de frmulas:
En breves lneas te mostrar algo ms acerca de estos botones.
Rastrear dependientes
El comando Rastrear dependientes funciona al contrario que Rastrear
precedentes . Destaca todos los elementos que dependen del valor de la
celda activa. En nuestro ejemplo, esto no va a funcionar, ya que en la
columna de beneficios no hay dependientes de ese valor. Excel mostrar un
mensaje de advertencia si haces clic en este botn y no existen datos que
mostrar.
Sin embargo, la columna Importe s tiene celdas dependientes:
Al igual que con el botn Rastrear precedentes, puedes hacer clic varias
veces en l para mostrar los diferentes niveles de dependencia.
Quitar flechas
Los botones anteriores puedes utilizarlos de forma independiente o al
mismo tiempo en la misma celda, recargando la hoja con flechas azules. Para
eliminar las flechas existe el botn Quitar flechas.
Al hacer clic en el botn Quitar flechas directamente, se eliminarn todas las
flechas de la hoja. Si solo deseas eliminar las flechas de las celdas
precedentes o dependientes, puedes pulsar en el tringulo situado en la
parte derecha del botn para eliminar solo las flechas precedentes o las
dependientes:
Mostrar frmulas
El botn Mostrar frmulas mostrar todas las frmulas de la hoja en lugar de
su valor calculado. Funciona como un interruptor. Cuando se activa, muestra
todas las frmulas y se ensanchan las columnas para que sean ms fciles de
visualizar.
Normalmente suelo utilizar este botn cuando estoy en la fase de creacin
de frmulas en una plantilla grande, ya que de un simple vistazo, puedo ver
las referencias donde apuntan las frmulas. Esta vista tambin facilita a otros
usuarios, ver tu trabajo y comprender cmo se calculan los valores, en lugar
de hacer clic y mirar en cada una de las celdas.
Si haces clic de nuevo en el botn, se volvern a mostrar los valores
calculados y las columnas volvern a su ancho normal.
Comprobacin de errores
El funcionamiento de esta herramienta es similar a los comandos de revisin
ortogrfica de un procesador de textos. Mientras introduces una frmula,
Excel se mantiene alerta para ir comprobando algunos errores, aunque
existen algunos casos en que no es capaz de detectarlos.
Por ejemplo, en la siguiente imagen he escrito incorrectamente una
referencia de celda en la frmula de la celda D2. Esto afecta tanto a la propia
celda como a las que dependen de ella en la columna Beneficio:
Hago clic en el botn Comprobacin de errores para buscar los errores en
toda la hoja. Si Excel encuentra alguno, aparecer en el cuadro de dilogo
Comprobacin de errores:
En este caso, D2 contiene un error de nomenclatura. La referencia de la celda
B est incompleta. Puedes utilizar los botones de la parte derecha del
cuadro para:
Abrir el archivo de ayuda para obtener informacin especfica acerca
de este error.
Mostrar los pasos de clculo que se siguieron para llegar a este punto.
Ignorar el error por el momento.
Abrir la barra de frmulas para corregir el error a mano.
Hago clic en el botn Opciones de Excel en la ficha Archivo para abrir el
cuadro de dilogo. Selecciono la categora Frmulas . Aqu puedo activar o
desactivar la opcin de comprobacin de errores, elegir el color que se
utilizar para resaltar los errores y restablecer los errores que se marcaron
previamente e ignorarlos.
Evaluar frmulas
Como has visto antes, al mostrarte el resto de botones de este tema, hay
una serie de comandos que puedes utilizar para rastrear lo que hace una
frmula. El botn Evaluar frmula, realiza estas mismas acciones un poco
ms avanzadas, ya que muestra cada clculo que se utiliza para obtener un
valor. Este comando me resulta especialmente til, ya que permite
asegurarme de que una frmula compleja se est calculando correctamente
y muestra el resultado que espero. Tambin es til para identificar en qu
expresin de la frmula se encuentra un determinado error.
En la siguiente imagen puedes ver que he seleccionado un valor en la
columna Beneficio. He pulsado en el botn Evaluar frmula:
Se mostrar el cuadro de dilogo Evaluar frmula, que contiene la frmula
que va a ser examinada:
Hago clic en el botn Evaluar para entrar en la primera parte de la frmula.
En este caso, D2 es el primer valor:
Observa que el botn Paso a paso para entrar est activo. Si hago clic en l,
profundizar un nivel en la frmula, de la misma forma en que se poda
hacer con las celdas precedentes y dependientes. En este caso, El valor de
B2 se muestra en un rea de texto diferente:
Ahora hago clic en el botn Paso a paso para salir para cerrar el rea de texto
y aplicar el valor 400 en el mismo lugar donde apareca B2. Al pulsar el botn,
tambin se pasar a la siguiente parte de la frmula.
Ahora el siguiente valor se resalta y la opcin Paso a paso para entrar vuelve
a estar activa. Hago clic en Evaluar para continuar la comprobacin de la
frmula:
Al finalizar, el corrector habr pasado a travs de todos los clculos de uno
en uno antes de mostrar el paso final:
Al hacer clic en Evaluar una vez ms, se llevar a cabo el clculo final y se
mostrar el resultado. Si tras finalizar la evaluacin, hago clic de nuevo, se
volver a iniciar el proceso desde el principio.
Ventana Inspeccin
Imagina que ests recopilando una gran cantidad de resultados de una
encuesta. A medida que agregas ms y ms datos en la hoja, se hace ms
complicado vigilar ciertas celdas clave. La Ventana Inspeccin est diseada
para mostrar el valor de las celdas que especifiques y otra informacin que
podra cambiar dentro de la hoja. La Ventana Inspeccin es una ventana
flotante que permanecer abierta sin importar lo que ests haciendo con
Excel.
Imagina la hoja con la que he estado trabajando hasta ahora:
Si quiero hacer el seguimiento de un determinado valor en la columna
Beneficio, la Ventana Inspeccin es la ideal.
Para utilizar esta herramienta simplemente hago clic en la celda que quiera
controlar y luego pulso en el botn Ventana Inspeccin. Aparecer una nueva
ventana que mostrar todas las estadsticas esenciales referidas al valor de
la celda.
La Ventana Inspeccin es muy fcil de usar:
Para mover la celda activa a un valor en la Ventana Inspeccin, haz
doble clic en uno de los valores.
Para agregar ms valores a la ventana, haz clic en Agregar inspeccin y
despus especifica la celda.
Para eliminar valores, selecciona uno de ellos en la Ventana
Inspeccin y haz clic en Eliminar inspeccin.
Cuando hayas terminado con la Ventana Inspeccin, haz clic en el botn de
cierre.
Mostrar e imprimir frmulas
Si introduces una frmula y pulsas Intro, Excel calcular automticamente
esta frmula (y todas las dems frmulas que se encuentren en la hoja de
clculo) y mostrar los resultados. Sin embargo, para facilitar la creacin y
revisin de estos clculos, puedes hacer que aparezcan las frmulas (en
lugar de los resultados). Para ello puedes hacer clic en Frmulas Mostrar
frmulas:
Esto mostrar las frmulas de la hoja:
Para visualizar una vista previa de la pgina haz clic en Archivo - Imprimir:
Gracias
Gracias por descargar este archivo. Forma parte de una serie de mini-ebooks
sobre Excel que solo podrs encontrar en Ayuda Excel.
Como veo que ya conoces mi web y mi trabajo, sabrs dnde acudir cuando
tengas algn problema sobre Excel o cuando necesites formacin.
Contacta conmigo
Estar encantado de ayudarte.
Sergio
