fundamentos excel financiero

Fundamentos de Excel para Finanzas

Alfonso Rodrguez Sandis

Grupo Valoracin Financiera Aplicada

www.usc.es/valfinap

www.usc.es/modeleva

Universidad de Santiago de Compostela


2

TABLADECONTENIDOS1.HOJADECLCULO,CONTENIDOSYFORMATOS.......................................................................................6

1.1.Introduccin........................................................................................................................61.2.Textos,datosyfrmulas.....................................................................................................81.3.Formato.............................................................................................................................101.4.Validacin..........................................................................................................................121.5.Formatocondicional.........................................................................................................16

2.COPIADOYPEGADODEFRMULAS....................................................................................................202.1.Copiado/pegadolibre,sinbloqueos..................................................................................202.2.Copiado/pegadoconproteccinabsoluta,bloqueototal.................................................212.3.Copiado/pegadoconproteccinrelativa(bloqueandocolumna,peronofila).................222.4.Copiado/pegadoconproteccinrelativa(bloqueandofila,peronocolumna).................232.5.Unltimoejemploconunpocodetodo...........................................................................23

3.NOMBRES....................................................................................................................................243.1.Nombredecelda...............................................................................................................243.2.Nombrederango..............................................................................................................253.3.Crearnombresdesdelaseleccin.....................................................................................263.4.Nombresenactivo............................................................................................................27

4.FUNCIONESARITMTICAS.................................................................................................................284.1.SUMA................................................................................................................................284.2.SUMAconrangosemicerrado...........................................................................................294.3.CONTAR,CONTARAyCONTAR.BLANCO...........................................................................294.4.SUMAR.SIyCONTAR.SI.....................................................................................................304.5.SUMAPRODUCTO..............................................................................................................314.6.SUMAR.SI.CONJUNTO.......................................................................................................32

5.FUNCIONESESTADSTICASBSICAS.....................................................................................................325.1.MAX,MINyPROMEDIO....................................................................................................325.2.Funcionesdedesviacintpicayvarianza.........................................................................335.3.Funcionesdecovarianzaycorrelacin..............................................................................33


3

5.4.JERARQUIA........................................................................................................................345.5.K.ESIMO.MAYOR...............................................................................................................345.6.K.ESIMO.MENOR...............................................................................................................355.7.PENDIENTE........................................................................................................................355.8.DISTR.NORM.....................................................................................................................365.9.DISTR.NORM.INV...............................................................................................................365.10.PERCENTILYRANGO.PERCENTIL.....................................................................................37

6.MISCELNEA.................................................................................................................................386.1.TEXTO................................................................................................................................386.2.RAIZ...................................................................................................................................386.3.ABS,ENTERO,REDONDEAR...............................................................................................386.4.Funcionesdenmerosaleatorios......................................................................................396.5.Operacionesconfechas....................................................................................................406.6.HOYyFIN.MES..................................................................................................................406.7.AO,MES,DIA,DIASEM....................................................................................................41

7.FUNCIONESLGICAS.......................................................................................................................417.1.LafuncinSIparaunacondicinsimple...........................................................................427.2.LafuncinMAXcomoalternativa.....................................................................................437.3.UsodelafuncinSIanidada.............................................................................................437.4.AnidamientomltipledelafuncinSI..............................................................................447.5.FuncinY...........................................................................................................................467.6.FuncinO..........................................................................................................................467.7.FuncinNO........................................................................................................................477.8.FuncinSI.ERROR..............................................................................................................47

8.FUNCIONESDEBSQUEDAYREFERENCIA............................................................................................488.1.ELEGIR...............................................................................................................................488.2.LafuncinDESREF.............................................................................................................498.3.FuncinDESREFaldetalle.................................................................................................508.4.LafuncinCOINCIDIR........................................................................................................50


4

8.5.LafuncinBUSCAR............................................................................................................528.6.LafuncinINDICE..............................................................................................................53

9.FUNCIONESFINANCIERAS.................................................................................................................549.1.LafuncinINT.EFECTIVO...................................................................................................549.2.LafuncinTASA.NOMINAL................................................................................................549.3.Funcionesconanualidades:VA,PAGO,TASA,NPER.........................................................559.4.FuncinVAaadiendounpagofinal................................................................................569.5.FuncinVFaadiendounpagoinicial...............................................................................569.6.LasfuncionesPAGO,PAGOPRINyPAGOINT.....................................................................579.7.VNA...................................................................................................................................589.8.TIR.....................................................................................................................................589.9.LasfuncionesVNA.NO.PERyTIR.NO.PER..........................................................................58

10.EJEMPLODEUNAFUNCINARRAY..................................................................................................5911.USODELAHERRAMIENTATABLA...................................................................................................60

11.1.Tablasdeunsoloinput,columna....................................................................................6011.2.Tablasdeunsoloinput,fila............................................................................................6111.3.Tablasdedosinputs,filaycolumna...............................................................................62

12.HERRAMIENTABUSCAROBJETIVO....................................................................................................6413.ESCENARIOS................................................................................................................................66

13.1.UsodelAdministradordeEscenariosdeExcel................................................................6613.2.ElaboracindeEscenarios.Unmtodoalternativo........................................................69

14.SOLVER......................................................................................................................................7015.USODEREFERENCIASCIRCULARES...................................................................................................7316.ELABORACINDEGRFICOS...........................................................................................................75

16.1.Grficodelneasbsico..................................................................................................7516.2.Grficodecolumnasbsico............................................................................................7816.3.Grficodecolumnasylneas...........................................................................................7916.4.Grficodecolumnasylneasdedobleejevertical..........................................................8016.5.Grficodereas..............................................................................................................81


5

16.6.Grficodebarras.............................................................................................................8116.7.Grficodebarrastipotornado........................................................................................8216.8.Grficodedispersin.......................................................................................................8316.9.Grficocircular................................................................................................................8416.10.Grficoconrangodinmico..........................................................................................86

17.UNAMACROSENCILLA..................................................................................................................8818.FUNCIONESPERSONALIZADAS.........................................................................................................9019.USODELAAUDITORIADEFRMULAS...............................................................................................9320.AGRUPAR,DIVIDIR,INMOVILIZARPANELES,HIPERVNCULOS..................................................................95

20.1.Agrupar...........................................................................................................................9520.2.Dividir..............................................................................................................................9720.3.Inmovilizarpaneles.........................................................................................................9820.4.Hipervnculos...................................................................................................................99

21.IMPRIMIR...................................................................................................................................9921.1Seleccindedatosaimprimir..........................................................................................9921.2Encabezadosypie............................................................................................................9921.3Saltodepgina..............................................................................................................100

22.RECOMENDACIONESBSICASSOBRELAELABORACINDEMODELOSFINANCIEROSENEXCEL.....................10122.1.Nmerodehojasdeunmodelo....................................................................................10222.2.Alcancedelmodelo.......................................................................................................10222.3.Orientacinverticaluhorizontaldeunmodelo............................................................10222.4.Orientacindeizquierdaaderechaydearribaabajo..................................................10222.5.Acercadeinputsyoutputs............................................................................................10322.6.Lasceldasdeinputsdebencontenerdatos,nofrmulasylasceldasdeclculonodeben

contenerdatos,solofrmulas........................................................................................................10422.7.Introduzcalosdatosunasolavez.................................................................................10522.8.Introduzcanivelesensegundalnea..........................................................................10522.9.Creacindefrmulasychequeo...................................................................................10622.10.Optimiceeltiempoyaprovechesutrabajo................................................................107


6

El texto que desarrollamos a continuacin no pretende ser una

leccin exhaustiva de Excel, ni mucho menos. Slo nos hemos atrevido

a describir las cuestiones esenciales que consideramos de gran utilidad

para aquellas personas que deben recurrir al uso de hojas de clculo

con asiduidad. Hemos usado como programa de hoja de clculo de

referencia Excel, pero las cuestiones que desarrollamos son

perfectamente extrapolables a otros programas similares. Seguro que a

muchos usuarios algunas cuestiones les parecern muy elementales y

seguro que nos hemos dejado otras muchas en el tintero.

En las diferentes figuras hemos tratado de mostrar las frmulas

incorporadas para que el lector pueda seguir mejor el desarrollo de los

ejemplos. Para ello hemos usado dos funciones personalizadas,

PONFORMULA y PONFORMULACELDA que nos indican el contenido de

una celda. La primera nos indica simplemente el contenido de la celda,

la segunda nos indica adems la celda de que se trata.

1. Hoja de clculo, contenidos y formatos

1.1. Introduccin

Excel cuenta con una serie de elementos comunes en su interfaz de

usuario. Las opciones de los mens, as como los botones que existen

en las herramientas, tienen un comportamiento dinmico.

Cada vez que se crea un nuevo libro de Excel nos encontramos con

tres pginas distintas, tres hojas de clculo, cada una de las cuales

puede contener datos distintos o tambin pueden ser relacionados. Para

aadir ms hojas basta clicar en la solapa a la derecha de la ltima

hoja o bien usar el botn derecho del ratn estando situado encima de

la etiqueta de alguna de las hojas. Para eliminar una hoja basta clicar

en el botn derecho del ratn estando situado la etiqueta de la hoja que

se desea eliminar y clicar eliminar. Es pertinente otorgar un nombre

identificativo a cada hoja clicando en la etiqueta de la misma.


7

Una gran parte de la ventana de trabajo est ocupada por las

celdas, el rea de la hoja de clculo en la que podemos introducir

textos, datos o frmulas. Cada celda se identifica con una referencia

nica que indica en primer lugar la columna en la que se encuentra

(con una letra) y en segundo lugar la fila en la que se encuentra con un

nmero. Un grupo de celdas colindantes se denomina rango. El rango

se identifica con la celda superior izquierda y la inferior derecha,

separadas por :. Por ejemplo el rango B2:D6 contendr las celdas de la

fila 2, desde B hasta D, y hacia abajo hasta la fila 6.

Una vez creado o abierto un libro, comenzaremos a introducir

datos en las celdas. Aplicaremos formato a esos datos, copiaremos,

moveremos celdas, etc. Las flechas de desplazamiento del cursor nos

permiten desplazarnos por la hoja. Como luego veremos, si le resulta

ms sencillo puede asignar nombres a las celdas y rangos para hacer

referencia a ellas.

Los mens son bastante intuitivos. En el men Archivo podremos

abrir un archivo, guardarlo, imprimirlo, as como cambiar algunas

opciones bsicas. Muchos usuarios nunca precisan cambiar dichas

opciones.

En el men Inicio se encuentran las opciones vinculadas a

cuestiones de formato. Acudiremos al men Insertar cuando queramos

incluir un grfico o algn otro objeto. El men Frmulas nos permite

fundamentalmente la inclusin de funciones propias de Excel. El men

Datos nos da acceso a determinadas herramientas para el tratamiento

de datos. En nuestro caso haremos uso de las opciones de Validacin

de Datos y de Anlisis y Si. En el men revisar se nos permite realizar


8

cuestiones como la proteccin del libro o la revisin ortogrfica. En el

men Vista podremos configurar la forma en que se ven algunas

caractersticas de la hoja de clculo.

1.2. Textos, datos y frmulas

En una celda podremos incorporar bsicamente tres cuestiones.

Textos, datos y frmulas. Usaremos el siguiente ejemplo para su

descripcin:

Textos. Suele tratarse de informacin que se incorpora para

identificar el contenido de celdas adyacentes. Por ejemplo los textos que

hemos escrito en el rango B9:B15. Nos permite identificar el contenido

de las celdas que se encuentran a su derecha.


9

Datos. Son los inputs de nuestras operaciones. Habitualmente se

tratar de cifras. En nuestro ejemplo las cifras de 20, 30 y 40 del rango

C9:C11.

Frmulas. Comienzan con el signo =. Recogen las operaciones que

queremos realizar. En el ejemplo mostramos las tres posibilidades

bsicas. En C12 hemos indicado que se sumen las celdas C9, C10 y

C11, introduciendo la frmula de forma directa, tal como la planteamos

conceptualmente. En C13 realizamos la misma operacin pero usando

una funcin de Excel. Excel cuenta con numerosas funciones que

ayudan a realizar una serie de clculos ya preestablecidos. En nuestro

caso hemos recurrido a la funcin SUMA. Las funciones no tienen por

qu introducirse de forma aislada. En la celda D15 incluimos la funcin

PROMEDIO (que calcula la media de los datos que se le indiquen)

dentro de nuestro clculo del triple del promedio al cuadrado.

Una gran parte del potencial de Excel proviene del uso de sus

funciones. En este documento repasaremos algunas de ellas. Las funciones requieren ser invocadas por el nombre identificativo que tiene

cada una de ellas y luego, entre parntesis, es preciso indicar los

argumentos de dicha funcin. En el caso de la funcin SUMA y de la

funcin PROMEDIO slo requieren un argumento, el rango de los datos

que queremos sumar o promediar. Una vez introducido el nombre Excel

nos muestra una ayuda en pantalla que nos indica qu hace la funcin

y qu argumentos necesita. Si el argumento est entre corchetes es que

es opcional. Los argumentos se separan por ;. Si sabemos el nombre

de la funcin podemos invocarla tras poner el smbolo =. Excel nos ir

ayudando si no sabemos el nombre exacto indicndonos las que tienen

la raz del nombre igual que lo que vayamos escribiendo. Otra

alternativa es invocar las funciones desde el men, en el botn de

insertar funcin dentro del men frmulas lo que nos llevar a

pantallas de ayuda. En dichas pantallas podemos escoger la categora

de la funcin que queremos insertar ofreciendo despus mucha ayuda

sobre la funcin que hayamos escogido. Cuando usted se familiarice


10

con una funcin no usar los mens para insertarla y lo har, sin

duda, directamente desde la barra de frmulas.

1.3. Formato

El formato de las celdas es importante. Los botones del formato

permiten realizar las operaciones ms habituales: tipo de letra, tamao,

color, fondos, bordes, etc.

Lo que podemos denominar texto plano puede formatearse con

tipos de letra, color, justificacin a izquierda o derecha, en negrilla o

cursiva, etc.

En cuanto a los nmeros existen multitud de formatos. Los ms

recomendables son aquellos que facilitan la lectura y dificultan la

confusin. En ese sentido es adecuado usar separador de miles y no

incluir decimales en aquellos casos que no es necesario. Por ejemplo,

qu nos aportan los decimales en una cuenta de resultados? Sin


11

embargo, al introducir un precio de un producto es posible que la cifra

decimal sea necesaria. Con los porcentajes, tanto cuando se trata de

inputs o datos como de outputs o resultados, suele ser apropiado

incluir decimales para evitar confusiones. En el caso de las fechas

existen numerosos formatos alternativos.

Independientemente de que tengamos textos o cifras, en las celdas

podemos incluir cuestiones como los bordes o los rellenos.

Si lo deseamos podemos eliminar las finas lneas grises que

delimitan las celdas. Para ello en el men Vista desmarcamos la opcin

Lneas de cuadrcula. De esta forma slo se vern las divisiones de

celda que nosotros hayamos diseado explcitamente con la opcin de

bordes de celda.

Antes de acabar este pequeo apartado acerca de los formatos

vamos a ver dos cuestiones que nos parecen de mucha utilidad y que

usamos habitualmente.

Por un lado el formato personalizado de nmero. Se accede a dicha

opcin en la ficha nmero del men Inicio. Permite indicar un formato

concreto a una celda.


12

En el primer ejemplo le hemos indicado que la celda C28 ponga el

texto resultado, independientemente de la cifra o frmula que

incluyamos. En nuestro caso nos ayudar a veces para mejorar la

apariencia de alguna tabla. En el segundo ejemplo le hemos indicado

que aada el texto personas al contenido de la celda. As nos indica en

nuestro caso 5 personas. En cualquiera de los dos ejemplos vistos slo

se est afectando al formato de la celda, no a su contenido ni

operatividad.

Por ltimo, a veces puede ser de ayuda el uso del operador de

conexin de Excel (&) que permite mezclar textos y celdas mejorando la

informacin que se ofrece.

En nuestro ejemplo vinculamos el texto El precio es con el

contenido de una celda. En la segunda variante usamos la funcin

TEXTO para mejorar la forma en la que el contenido de la celda se

muestra. Tenga en cuenta que las celdas C33 y C34 ya no incluyen

cifras, ya no son operativas.

Las posibilidades de formato que tiene Excel son innumerables.

Nuestra recomendacin general es que la sencillez y sobriedad debe

primar para facilitar la lectura. No obstante puede haber ocasiones en

que deba usarse todo el potencial de variedades de formato.

1.4. Validacin

A la hora de introducir datos, informacin, en la hoja de clculo

una tcnica interesante consiste en restringir en las celdas destinadas a

ese propsito restricciones en cuanto a la informacin que pueden

albergar, reduciendo as las posibilidad de que un determinado clculo

se haga con un input inapropiado. Para realizar las tareas de validacin


13

debemos seleccionar la celda o celdas a las que afectar y acudir,

dentro del men Datos al submen Validacin de datos. Dicha ficha

tiene tres zonas. En la primera se indica la Configuracin, el tipo de

validacin. En la segunda el Mensaje de entrada, el mensaje que el

usuario ver cuando se site sobre la celda en cuestin. En la tercera el

Mensaje de error, el menaje que el usuario obtendr si trata de

introducir informacin que no est permitida en la celda.

Vemos un primer ejemplo en el que le indicamos que en la celda B4

slo pueden incluirse nmeros enteros entre 1 y 10.

En B6 realizamos el mismo tipo de validacin pero el mnimo y el

mximo no lo introducimos manualmente sino que lo vinculamos a las

celdas D6 y E6 respectivamente.

Otra validacin que podemos realizar es indicando que el usuario

slo puede escoger la entrada de una celda de una determinada lista

que se le presenta. En nuestro caso le hemos indicado en B8 que slo

puede incluir los valores 1, 2, 3 y 4.


14

En B10 realizamos el mismo tipo de validacin pero la lista de

datos posible se la indicamos referenciando el rango D10:G10.

Tambin podemos realizar la validacin indicando que slo puede

introducirse informacin decimal. En nuestro caso le hemos indicado

entre el 0% y el 20%. Observe que el 20% se introduce como 0,2.


15

De forma similar podemos indicar que un input haya de ser mayor

o menor que una determinada cifra, o incluso que una determinada

fecha, como en el ejemplo siguiente.

Por ltimo, tambin puede hacerse una validacin personalizada,

mediante una frmula. En nuestro ejemplo le indicamos que la entrada

de la celda no puede coincidir con el mnimo de un determinado rango.


16

En general las validaciones de celdas input tienen dos ventajas. Por

un lado evitan el que el usuario introduzca un valor inadecuado en una

celda. Por otro lado, usando adecuadamente la opcin de Informacin

de entrada se podr ofrecer una interesante informacin en lnea

acerca del uso del modelo en cuestin.

1.5. Formato condicional

En este repaso a algunas de las cuestiones de formato de Excel

vamos a ver brevemente una herramienta que es muy til para ayudar

a visualizar o analizar los resultados de un determinado trabajo, cual es

Formato condicional. Esta herramienta permite que una celda tenga un

formato diferente en funcin de su propio contenido incluso en funcin

del contenido de otra celda. Hay muchsimas posibilidades dentro de

esta opcionalidad. Para acceder al formato condicional debemos

dirigirnos dentro del men Inicio a la etiqueta Formato condicional

dentro del submen Estilos. Dentro de dicha etiqueta podremos crear

una nueva regla de formato condicional o editar una existente. Veamos

algunos ejemplos.


17

En nuestro primer ejemplo en el rango B7:K7 situamos diferente

cifras. En la celda E5 situamos otra cifra. Nuestra propuesta ser que a

las celdas del rango B7:K7 se les modifique el formato cuando su valor

coincida con el de la celda E5.

Le hemos indicado que ponga el nmero en rojo y negrilla y con un

fondo azulado. En primer lugar seleccionamos el rango B7:K7.

Acudimos a Nueva regla. Como se puede ver en la siguiente figura

hemos creado la regla dentro del grupo Aplicar formato nicamente a

las celdas que contengan. En dicha seccin le indicamos que la celda

de referencia es E5, con los smbolos $. En el botn formato escogemos

el tipo de formato que queremos aplicar.

De forma similar podemos indicarle que queremos que modifique el

formato a celdas que contengan valores por encima del de referencia:


18

En el siguiente ejemplo realizamos el formato condicional para tres

columnas siendo la cifra de referencia diferente para cada una de ellas.

Puede hacerse columna a columna, lo cual es laborioso. Lo mejor

es seleccionar toda la tabla y realizar el formato condicional de una sola

vez. Como podemos ver en la siguiente figura slo es necesario eliminar

uno de los smbolos dlar, el que antecede a la letra B (en este caso).

Ver el apartado sobre copiado y pegado y el papel de los smbolos $.

Una opcin ms compleja es la de establecer un formato

condicional en funcin de que se cumpla un determinada frmula. Por


19

ejemplo, queremos que en las siguientes filas se marque de forma

especial el valor mnimo de cada fila.

Para ello seleccionamos el rango B34:E37 y acudimos a la opcin

de Utilice una frmula que determine las celdas para aplicar formato

en el men de crear regla. Introducimos las frmula

=B34=MIN($B34:$E34).Cuando dicha frmula sea cierta, es decir,

cuando la celda de referencia sea el mnimo de su fila aplicar el

formato condicional. Observar que en el rango de la funcin MIN se ha

utilizado bloqueado parcial (ver el apartado de copiar y pegar), de forma

que la frmula se aplique fila a fila. Cambiando el bloqueo se podra

aplicar por columnas.

La ltima variante que vamos a ver es aplicar la opcin de que

cambie el formato a los valores en funcin de que estn por encima o


20

debajo del promedio. En nuestro caso lo hacemos para los que estn

por encima del promedio.

2. Copiado y pegado de frmulas

Antes de comenzar este apartado, recordar que para copiar y pegar

informacin de unas celdas a otras pueden utilizarse diferentes

sistemas: el men edicin, los iconos grficos de la barra de

herramientas, la combinacin de teclas de mtodo abreviado, el men

rpido del botn derecho del ratn y, cuando la zona de pegado est al

lado de la de copiado, el arrastre del ratn.

2.1. Copiado/pegado libre, sin bloqueos

Supongamos que tenemos la siguiente tabla:

Tenemos informacin de unidades vendidas y precios de venta para

tres meses diferentes. Deseamos calcular los ingresos de cada mes.

Para ello es suficiente con realizar la operacin en la celda C7 para el

primer mes y copiar la frmula hacia la derecha para aplicarla a los

meses 2 y 3. Qu frmula debemos introducir en este caso?

Simplemente debemos introducir en C7 la frmula =C5 * C6. Cuando


21

copiemos dicha frmula hacia la derecha automticamente en D7

aparecer la frmula =D5 * D6, y as sucesivamente. Dado que hemos

introducido inicialmente en C7 tanto C5 como C6 limpias, esto es, sin

proteccin, Excel asume que deseamos repetir la operacin pero con los

datos de la columna correspondiente. Lo mismo hubiera ocurrido si

deseamos copiar una frmula hacia abajo o arriba, esto es de unas filas

a otras (por supuesto tambin se mantiene el criterio si al copiar de

forma horizontal nos movemos hacia columnas de la izquierda y no de

la derecha). Este sistema de copiado/pegado es, quizs, el ms

utilizado. No se bloquea ni la columna ni la fila.

2.2. Copiado/pegado con proteccin absoluta, bloqueo total

En otras ocasiones puede que nos interese fijar una celda y que la

misma no se modifique en la operacin de copiado/pegado. Veamos un

ejemplo:

En este caso tenemos una serie de datos de ventas de diferentes

vendedores y para diferentes meses. Deseamos calcular la comisin a la

que tiene derecho cada uno de ellos y en cada uno de los meses

sabiendo que en todos los casos se les debe aplicar el 20%, de la celda

C11.

Para ello en la celda C18 introducimos la frmula = $C$10 * C13, y

copiamos hacia la derecha, hasta la columna E y luego hacia abajo

hasta la fila 20 (o primero hacia abajo y luego hacia la derecha). Al

ponerle los smbolos $ antes de la letra C y del nmero 11 ($C$11)


22

estamos protegiendo (bloqueando) la columna C y la fila 10; ello

significa que aunque nos movamos en la horizontal (a travs de

columnas) Excel va a respetar la columna C y no la variar, y aunque

nos movamos en la vertical (a travs de filas) Excel va a respetar la fila

11 y no la variar. Por tanto copiemos donde copiemos la frmula en la

hoja siempre respetar la posicin C11, que es la que contiene nuestro

dato de comisin universal a aplicar.

Por comodidad, para poner los smbolos $, una vez puesto =C11, y

antes de poner el operador de multiplicacin presionar la tecla de ayuda

F4 y Excel pondr automticamente los smbolos $.

2.3. Copiado/pegado con proteccin relativa (bloqueando columna, pero

no fila)

A veces no es necesario, o no deseamos, proteger o bloquear tanto

la columna como la fila. Observemos el siguiente ejemplo:

En este caso deseamos calcular los impuestos a pagar conociendo

los beneficios de tres empresas en dos aos diferentes y la tasa

impositiva aplicable (comn para las tres empresas, pero diferente para

cada ao). Para ello en la celda C32 introducimos la frmula = $C28 *

C25 y la copiamos/pegamos hacia la derecha hasta la columna E. La

columna C, que es la que contiene la tasa impositiva, queda bloqueada;

de esta forma al copiar hacia la derecha se respeta dicha columna. Sin

embargo, al no haber bloqueado la fila 28, al copiar hacia abajo, al ao


23

2, Excel coge la celda C29, que es la que contiene la tasa impositiva del

segundo ao. Para introducir los smbolos $, una vez introducido en

C32 =C28, y antes de introducir el operador de producto pulsar

repetidas veces la tecla de ayuda F4 hasta que aparezca la combinacin

deseada.

2.4. Copiado/pegado con proteccin relativa (bloqueando fila, pero no

columna)

En el siguiente ejemplo nos interesa proteger o bloquear la fila pero

dejar libre la columna:

En este caso el porcentaje de comisin para cada vendedor es el

mismo en todos los meses pero diferente para cada vendedor. En la

celda C46 introduciremos la frmula = C$38 * C40, de esta forma

protegeremos la fila 38 pero no la columna. Al copiar hacia la derecha

entrarn en la frmula las comisiones de los vendedores 2 y 3, pero al

copiar hacia abajo la fila 38, en la cual est el porcentaje de comisin,

permanecer inalterada a travs del rea de copiado/pegado.

2.5. Un ltimo ejemplo con un poco de todo

En este caso deseamos calcular a cunto ascendera una cuanta

de 1.000 euros colocada a diferentes tipos de inters durante una serie

de aos. Vemoslo:


24

En la celda C57, para un ao y el 10% introducimos la siguiente

frmula =$C$54*(1+C$56)^$B57. Protegemos tanto fila como columna

en C54, pues la cuanta es comn para todos los clculos. Protegemos

la fila 56 en C$56 y no la columna para facilitar el copiado/pegado a

columnas adyacentes asumiendo tipos de inters diferentes. Por ltimo,

en el caso del exponente del nmero de aos de la operacin,

protegemos la columna en $B57 pues la columna B es la que contiene

los aos, pero no la fila para que al copiar/pegar hacia abajo asuma los

diferentes aos para los cuales nos interesa realizar el clculo.

Un ltimo consejo. Si no necesita que el bloqueo sea parcial, como

en alguno de nuestros ejemplos anteriores, entonces realice bloqueos

absolutos. As reducir usted la posibilidad de error, reducir la

posibilidad de estar bloqueando justo al revs de lo deseado.

3. Nombres

En este apartado tratamos una cuestin que a veces puede

ayudarnos a clarificar y simplificar nuestro trabajo, abordamos la

definicin de nombres.

3.1. Nombre de celda

Es posible dar un nombre a una celda concreta. Para ello nos

situamos en dicha celda y en la barra de frmulas, a la izquierda de

todo, donde figura la ubicacin de la misma posicionamos el ratn, con

ello la referencia de dicha celda parpadear y se situar a la izquierda.


25

Escribimos entonces el nombre deseado y pulsamos Retorno. Tambin

podemos definir nombres (o borrarlos o reubicarlos) desde el men.

Debemos ir al men Frmulas, a la seccin de Nombres definidos y a la

etiqueta Asignar nombre.

En el siguiente ejemplo hemos definido a la celda C4 con el nombre

Precio. As, para invocar dicha celda ahora podremos utilizar su

ubicacin, C4, o su nombre, Precio.

Como podemos observar al realizar los clculos en C7:E7, aparece

en todos ellos Precio. No fue necesario introducirla en todas las celdas.

Bast introducir en C8 la siguiente frmula =Precio * C16, y

copiar/pegar hacia la derecha. Hemos conseguido lo mismo que si

hubiramos puesto =$C$4 * C6, esto es, con proteccin absoluta.

Entre las ventajas de este sistema estn el que para invocar ese

dato, el de la celda C4 desde cualquier hoja de clculo del mismo libro

ser suficiente utilizar el nombre Precio (esto es, los nombres son

vlidos para todo el libro salvo que se indique lo contrario en las

especificaciones en el men de Asignar nombre). Por otro lado, si los

nombres son claros y significativos facilita la comprensin de las

frmulas.

3.2. Nombre de rango

Tambin es posible definir un nombre para un rango de celdas y no

para una sola celda. La forma de definir un nombre para un rango es

igual que para una celda. Seleccionamos el rango y realizamos las

mismas operaciones que indicamos para la definicin de un nombre

para una celda individual. Veamos un ejemplo:


26

En este caso hemos nombrado al rango C11:E11 con el nombre

Unidades y la celda C4 sigue siendo la celda Precio.

En la fila 12, simplemente le indicamos que multiplique los

nombres y en la columna C coger por un lado el Precio (nico) y por el

otro el valor de la cuanta de unidades que le corresponda. En el caso

de las unidades es como si tuviramos un bloqueo de fila y no de

columna.

Podramos poner ms ejemplos, combinando nombres de celdas y

nombres de rango, pero seguro que usted ya ha cogido el hilo y es capaz

de explorar por s mismo las diferentes posibilidades y utilizar las que le

resulten ms cmodas y/o tiles. Por nuestra parte, pocas veces

recurrimos a estas alternativas, aunque son de gran utilidad cuando se

hacen modelos para terceros, para que estos puedan seguir con mayor

facilidad los clculos.

3.3. Crear nombres desde la seleccin

En el siguiente ejemplo calculamos la cuota de un prstamo a

partir de una determinada tasa de inters, una determinada duracin

de la operacin y una cuanta. Usamos la funcin PAGO, que veremos

en la seccin de funciones financieras.


27

En vez de ir nombrando una a una las celdas, en este caso lo que

hemos hecho es seleccionar el rango B18:C20 y acudir al men Crear

desde la seleccin en el submen de Nombres definidos dentro del

men frmulas. Le hemos indicado que cree los nombres a partir de los

valores de la columna izquierda, en nuestro caso la B, que es la que

contiene lo rtulos de nuestras variables.

3.4. Nombres en activo

A veces puede interesarnos conocer los nombres que tenemos

activos en nuestra hoja de clculo. Para ello nos situamos al fondo de la

hoja, en nuestro caso en B28 y acudimos al men Utilizar en la

frmula, dentro de Nombres definidos.


28

Al fondo se encuentra la opcin Pegar Nombres y dentro de la

misma la opcin Pegar lista. Clicamos en la misma y tendremos el

resultado que se puede observar en la figura adjunta, el listado de

nombres junto con la referencia de la celda a la que se refieren.

4. Funciones aritmticas

Comenzamos aqu un pequeo repaso a las funciones que

consideramos bsicas en diferentes apartados, en primer lugar con

algunas aritmticas.

4.1. SUMA

La funcin SUMA (rango) permite la adicin de todas las cuantas

contenidas en el rango de referencia:

Como vemos en el ejemplo, en la celda C11 le hemos indicado que

sume el contenido de C6 hasta C10 y luego hemos copiado/pegado

hacia la columna E. Podramos haber puesto en C11 la siguiente

frmula, que suma una a una las celdas = C6 +C7 +C8 +C9 +C10.

Adems de ser ms rpido con la funcin SUMA (aprovechando el icono

de la barra de herramientas) la principal ventaja de esta funcin sobre la suma individualizada es que si ahora aadimos nuevos conceptos de

gasto, la funcin se autoajustar, sin necesidad de retocarla. Con la

suma individualizada deberamos aadir el nuevo concepto a la

frmula.

Permtanos un par de consejos. En primer lugar, inserte las nuevas

filas en medio de las previas. Si realiza la insercin al final del rango, en


29

nuestro caso en la fila 11 original donde est el total gastos, la funcin

suma no coger la nueva fila, que ser la nueva fila 11 y quedar fuera

del rango (filas 6 a 10). En segundo lugar, cuando use el icono de compruebe siempre que el automatismo efectivamente est cogiendo el

rango que usted desea. Recuerde que puede corregir el rango de SUMA

que el automatismo de Excel le propone. Un error habitual es el que se

produce cuando en una fila tenemos los nmeros de ao (2006, 2007,

etc) y debajo una serie de cifras, y al sumarlas sumamos por error el

nmero identificativo del ao.

4.2. SUMA con rango semicerrado

En el siguiente ejemplo vamos a aprovechar lo ya visto en lo

referente a copiado/pegado con proteccin de celdas. Veamos el

siguiente ejemplo:

Como vemos, para calcular las ventas acumuladas de nuestros tres

vendedores, en la celda C20 introducimos la frmula =SUMA($C16:C16)

que luego copiamos hacia la derecha y hacia abajo. Al fijar la columna

C del inicio de rango de suma pero no fijar la del final de rango

conseguimos que a medida que nos desplazamos entre trimestres vayan

acumulndose las ventas. No fijamos la fila pues al copiar/pegar hacia

abajo querremos que coja los datos de los otros dos vendedores.

4.3. CONTAR, CONTARA y CONTAR.BLANCO

Una frmula que cuenta devuelve el nmero de celdas de un rango

especfico que rene ciertos criterios.


30

- CONTAR. Devuelve el nmero de celdas de un rango que contiene

valores numricos.

- CONTARA. Devuelve el nmero de celdas de un rango que no estn

en blanco (una celda puede contener texto).

- CONTAR.BLANCO. Devuelve el nmero de celdas en blanco de un

rango.

En el ejemplo anterior, se puede observar que son 8 las celdas del

rango. De ellas, 5 tienen nmero, 6 tienen alguna informacin y 2 estn

en blanco.

4.4. SUMAR.SI y CONTAR.SI

Estas funciones permiten realizar la suma o conteo de una serie de

datos cuando se cumple una determinada condicin. Veamos un

ejemplo:

Como podemos observar, tenemos una serie de cuantas y una

serie de vendedores responsables de las mismas. Para calcular la

cuanta total que corresponde a Luis introducimos en la celda F40 la


31

expresin =SUMAR.SI($C$40:$C$49;E40;$B$40:$B$49). Esta suma

condicional requiere en primer lugar el rango en el que se encuentra la

condicin a verificar, en este caso C40:C49, que es donde situamos los

nombres de los vendedores de cada operacin. Fijamos las celdas con

los smbolos $ pues al copiar/pegar hacia abajo para los totales de

David y Sara las celdas de referencia son las mismas. A continuacin

introducimos E40, la celda donde se encuentra la condicin a verificar,

en este caso que el vendedor sea Luis. No fijamos dicha celda para

facilitar que al copiar/pegar hacia abajo se aplique a los otros dos

vendedores. El ltimo elemento de la frmula es el rango donde se

encuentran los datos que hay que sumar si se cumple la condicin; en

este caso el rango es B40:B49, el cual fijamos para facilitar el

copiado/pegado hacia abajo.

Al igual que la funcin SUMA, con SUMAR.SI podemos insertar

nuevas filas (o columnas, segn se trate) y la frmula las engullir.

En el caso de CONTAR.SI, cuenta las celdas que cumplen la

condicin analizada. En nuestro caso queremos saber el nmero de

operaciones que ha hecho cada vendedor. Slo necesitamos el rango de

celdas a comprobar (el mismo que en la funcin SUMAR.SI) y el criterio

que se desea comprobar (en el caso de las operaciones de Luis, E45).

4.5. SUMAPRODUCTO

Esta funcin permite multiplicar los valores de dos rangos, dato a

dato y realizar la suma de los resultados.


32

Como vemos, slo es preciso indicar los dos rangos que queremos

multiplicar. Deben tener igual nmero de datos.

4.6. SUMAR.SI.CONJUNTO

Esta funcin permite realizar sumas chequeando ms condiciones

que la funcin SUMAR.SI, que slo permite analizar un criterio.

En nuestro caso queremos analizar dos criterios, el vendedor y si

las ventas son nacionales o exteriores. El primer argumento es el rango

a sumar, en nuestro caso el rango B66:B75. A continuacin se van

introduciendo los rangos de criterios y el criterio a cumplir, y as

sucesivamente. Hemos realizado bloqueos parciales para facilitar el

copiado de la frmula desde G66 al resto de celdas.

5. Funciones estadsticas bsicas

5.1. MAX, MIN y PROMEDIO

Estas tres funciones extraen el valor mximo, mnimo y la media

aritmtica de una determinada serie de datos de un rango.


33

Slo es preciso indicar el rango en el cual se encuentran los datos a

analizar.

5.2. Funciones de desviacin tpica y varianza

Con estas funciones se extrae la desviacin tpica, y la varianza,

tanto muestral como poblacional.

Para cada una de ellas tenemos dos nombres de funcin diferentes,

que dependen de la versin de Excel que se est utilizando. Los

nombres usados en las versiones ms antiguas se mantienen tambin

en las ms recientes por compatibilidad. Slo es preciso indicar el rango

en el cual se encuentra la serie de datos del que queremos conocer la

varianza o la desviacin tpica.

5.3. Funciones de covarianza y correlacin

Para conocer la covarianza poblacional o muestral es preciso

indicar los rangos de las dos series de datos implicadas. El oren es

indiferente. De igual forma el coeficiente de correlacin


34

(COEF.DE.CORREL) precisa que le indiquemos, de forma indistinta, los

rangos de las dos series de datos.

5.4. JERARQUIA

La funcin JERARQUIA nos indica el puesto que ocupa un dato

dentro de una serie en funcin de su valor, ya sea en orden ascendente

o descendente.

Debemos indicar el dato que queremos chequear, en segundo lugar

la serie a la que pertenece, y por ltimo indicaremos con un 0 si el

orden que buscamos es descendente o un 1 si es ascendente. En

nuestro ejemplo hemos pedido la jerarqua descendente.

5.5. K.ESIMO.MAYOR

La funcin K.ESIMO.MAYOR nos indica el valor que ocupa una

determinada posicin jerrquica dentro de una serie, de mayor a menor.


35

Debemos indicar en primer lugar la serie de datos y en segundo

lugar que orden jerrquico, de mayor a menor, buscamos. En nuestro

caso el valor de orden 1 es 650, el de orden 10 es 230.

5.6. K.ESIMO.MENOR

De forma anloga a la anterior, la funcin K.ESIMO.MENOR nos

indica el valor que ocupa una determinada posicin jerrquica dentro

de una serie, de menor a mayor.

Debemos indicar en primer lugar la serie de datos y en segundo

lugar que orden jerrquico, de menor a mayor, buscamos. En nuestro

caso el valor de orden 1 es 230, el de orden 10 es 650.

5.7. PENDIENTE

La funcin PENDIENTE nos da la pendiente de la recta que mejor

se ajusta a la nube de puntos generada entre dos variables, una

independiente (x) y otra dependiente (y), segn la ecuacin y = a + b x. y

que es el trmino b de la ecuacin de regresin lineal.


36

Debemos indicar en primer lugar el rango de la serie de la variable

dependiente y luego el rango de la serie de la variable independiente. En

este caso el orden de las series s es importante.

5.8. DISTR.NORM

Esta funcin, con dos versiones en funcin de la versin de Excel

utilizada, nos indica la probabilidad acumulada de un determinado

valor dentro de una funcin normal y dadas su media y su desviacin

tpica.

Debemos indicarle en primer lugar el valor buscado, a continuacin

la media, luego la deviacin tpica, y por ltimo, con la opcin

VERDADERO le indicamos que queremos la probabilidad acumulada.

5.9. DISTR.NORM.INV

Esta funcin, con dos versiones en funcin de la versin de Excel

utilizada, nos indica el valor que deja una determinada probabilidad

acumulada a su izquierda dentro de una funcin normal y dadas su

media y su desviacin tpica.


37

Debemos indicarle en primer lugar el valor de probabilidad

acumulada buscado, a continuacin la media y luego la deviacin

tpica.

5.10. PERCENTIL Y RANGO.PERCENTIL

Dados los valores de una serie la funcin PERCENTIL nos indica el

porcentaje de los mismos que son inferiores a un valor determinado que

se analice. (En la figura hemos ocultado las filas entre 16 y 102).

Como vemos, debemos indicar en primer lugar el rango de datos y

posteriormente el valor que queremos testar, en nuestro caso el de la

celda E7.

La funcin RANGO.PERCENTIL nos indica el valor que dentro de

una determinada serie deja por debajo el porcentaje que nosotros

queramos comprobar. Como vemos, debemos indicar en primer lugar el

rango de datos y posteriormente el porcentaje que queremos testar, en

nuestro caso el de la celda E12.


38

6. Miscelnea

En este apartado veremos algunas funciones de diferentes

categoras que nos pueden ser tiles, incluidas las operaciones con

fechas.

6.1. TEXTO

La funcin TEXTO permite convertir un determinado valor en texto,

con un formato especificado.

En los dos ejemplos que presentamos convertimos sendas cifras

con el formato que deseamos. En ambos casos debe indicarse la

referencia a la celda y posteriormente, entre indicar el formato

deseado.

6.2. RAIZ

Extrae la raz cuadrada de un nmero.

Slo precisa como argumento la referencia a la celda donde se

encuentra el nmero en cuestin.

6.3. ABS, ENTERO, REDONDEAR

Estas funciones nos permiten obtener el valor absoluto de un

nmero, su raz entera o bien redondearlo (ms all de cuestiones

meramente de formato).


39

En el caso de REDONDEAR es preciso indicarle a cuntos

decimales queremos que se realice el redondeo. El redondeo se hace

desde 0,5 (incluido) hacia arriba a la cifra siguiente y de 0,5 (no

incluido) hacia abajo a la cifra anterior. Existen variantes para

redondear siempre por arriba, por abajo, nmeros pares o nmeros

impares.

6.4. Funciones de nmeros aleatorios

La funcin ALEATORIO, que no requiere ningn argumento, nos da

un nmero aleatorio entre 0 y 1 cada vez que Excel refresca sus

clculos. Para forzar la actualizacin de Excel, pulse F9.

La funcin ALEATORIO.ENTRE nos da un nmero aleatorio entre

un valor inferior y otro superior que le hayamos indicado. Esos son los

dos argumentos que deben incorporarse a la funcin, y en ese orden.

Esta funcin no opera con datos decimales. En la figura vemos un


40

pequeo truco para salvar este inconveniente. Multiplicamos los valores

porcentuales por 100, dentro de la funcin, y dividimos posteriormente

el resultado por 100, fuera de la funcin. Esta funcin tambin cambia

el resultado cada vez que Excel se refresca.

6.5. Operaciones con fechas

Dada una fecha podemos saber cul ser la fecha un nmero de

das superior (sumar) o podemos saber la distancia en das entre dos

fechas (restar). El da 1 es el 1 de Enero de 1900, y partir de ah se

establecen todos los dems.

En nuestro ejemplo hemos hecho una suma y una resta.

6.6. HOY y FIN.MES

Existen numerosas funciones de fecha. Mostramos dos que suelen

ser muy tiles. La funcin HOY, que no necesita argumentos, nos

indica la fecha del da en el que nos encontramos. Por su parte, la

funcin FIN.MES nos indica el ltimo da del mes una serie de meses a

partir de una fecha concreta. Es preciso indicarle en primer lugar la

fecha de referencia y en segundo lugar el nmero de meses hacia

delante (nmero positivo) o hacia a atrs (nmero negativo) del que

queremos saber el ltimo da del mes.


41

6.7. AO, MES, DIA, DIASEM

Estas funciones extraen de una determinada fecha informacin

referida al ao, el mes, el da del mes y el da de la semana de que se

trate.

La nica que requiere un argumento adicional a la celda de

referencia es la funcin DIASEM. Nosotros le hemos indicado un 2, que

significa que el lunes es un 1, el martes es un 2 etc. Si se omite, por

defecto Excel interpretara semanas comenzando el domingo (1) y

acabando el sbado (7). En nuestro caso el 5-feb-2014 es el ao 2014,

mes 2, da 5 y fue un mircoles (3).

7. Funciones lgicas

Una de las cuestiones ms interesantes de los programas de hoja

de clculo es la posibilidad de introducir funciones lgicas en las

frmulas. De alguna forma ayudan a romper la linealidad de los

clculos. Existen diferentes funciones condicionales predefinidas en

Excel (NO, Y, O, SI).


42

7.1. La funcin SI para una condicin simple

Empecemos con un en ejemplo sencillo. Deseamos calcular las

comisiones en una serie de operaciones. La comisin alcanza el 5% de

la cuanta de la operacin, pero si dicha cuanta es inferior a 1.000 debe

aplicarse una comisin mnima de 50. Veamos una forma de resolverlo:

La funcin condicional opera con tres elementos o argumentos

dentro del parntesis: la condicin que se analiza, resultado si se

cumple y resultado si no se cumple. En nuestro caso hemos indicado

que compruebe si la cuanta de C8 es inferior a la mnima de C6. Si se

cumpliera dicha condicin el resultado habra de ser el contenido de C5

(la comisin mnima) y si no se cumple, esto es, si la cuanta supera la

cifra de 1.000 entonces debe multiplicarse la cifra en cuestin por la

comisin de C4. Por otro lado utilizamos los smbolos $ para permitir

copiar la frmula hacia la derecha y que opere para las dems cuantas.

La nica celda que no fijamos es precisamente la de la cuanta de la fila

8 sobre la que se desea calcular la comisin.

En general todas las frmulas que se desarrollen con la funcin SI

respondern a este sencillo esquema, si bien pueden hacerse ms

complejas en la medida en la que los argumentos se hagan ms

complejos e incorporen, por ejemplo, nuevas funciones condicionales,

que quedarn anidadas. Dentro de un momento le presentaremos un

ejemplo de esto ltimo, pero permtanos indicarle una forma sencilla de

evitar la condicional del ejemplo anterior usando otra funcin de Excel,

la funcin MAX.


43

7.2. La funcin MAX como alternativa

Dicha funcin devuelve el valor mximo de los contenidos en una

serie de argumentos. Vemoslo:

Introducimos dos argumentos en la funcin: por un lado la cuanta

de comisin mnima, $C$14, y por otro lado la comisin calculada como

porcentaje de la cuanta de la operacin, $C$13*C17; cuando la

comisin calculada sea superior al mnimo, esa ser la respuesta de la

frmula, pero cuando sea inferior, la frmula devolver la cuanta de

comisin mnima pues ser superior a la calculada.

Muchas operaciones de una sola condicional pueden simplificarse

de forma similar a la que acabamos de mostrarle, ya sea con la funcin

MAX o la funcin MIN, que opera de forma similar devolviendo el valor

mnimo de una serie de elementos.

7.3. Uso de la funcin SI anidada

En este caso queremos calcular los impuestos de varias empresas

en un sistema fiscal que aplica un 30% de tasa impositiva a los

beneficios que no alcancen los 90.000 euros y un 35% a los que

excedan dicha cifra. Por supuesto el impuesto ha de ser cero si los

beneficios son negativos. Hay multitud de formas de solucionar una

cuestin como esta. Vamos a hacerlo anidando dos condicionales:


44

Como se puede observar la primera condicional indica que si el

beneficio es menor a cero, los impuestos sern cero. A partir de ah, de

no cumplirse esta premisa, es decir, si los beneficios fueran positivos,

se abre la siguiente condicional. Esta segunda condicin pregunta

acerca de si los beneficios son inferiores o no al tramo de aplicacin de

la tasa alternativa; si son inferiores a dicha cifra, aplicaremos la tasa de

C23 a la cifra de beneficio. Si fueran superiores al tramo de aplicacin

de la tasa alternativa entonces el impuesto se compondra de dos

sumandos; en el primero de ellos aplicamos la tasa alternativa a ese

tramo de aplicacin, mientras que en el segundo sumando aplicamos la

tasa impositiva general al exceso de beneficio sobre el tramo de

aplicacin del mnimo. Los smbolos $, como siempre, nos ayudan a

fijar las posiciones que no deben variar al copiar/pegar la frmula hacia

la derecha, para aplicrsela al resto de empresas. Evidentemente la

primera condicin podra eliminarse aplicando la funcin MAX.

Sin duda usted podr disear, por s mismo, frmulas alternativas

que garanticen, en cualquier caso un resultado correcto.

En anidamientos complejos es aconsejable trazar sobre papel el

esquema de decisin para tratar de entenderlas cuando uno no ha sido

su creador, o los ha creado hace mucho tiempo.

7.4. Anidamiento mltiple de la funcin SI

En este caso vamos a anidar un total de cuatro sencillas

condicionales. La funcin SI nos habilita dos resultados. Al anidar

cuatro funciones SI tendremos 5 posibles resultados. Veamos el

ejemplo:


45

Establecemos en el rango C35:C45 las ventas de una serie de

meses. En la celda C32 indicamos con cuantos meses de demora se

producen los cobros de dichas ventas. En la columna D establecemos

los cobros. Para ello vamos abriendo condicionales. Si la demora es

cero, se cobran en cada mes las ventas del propio mes, si la demora es

1, se cobran las ventas del mes anterior, y as sucesivamente. En la

celda C32 hemos validado para que slo pueda introducirse 0, 1, 2 ,3 y

4. Observe que en la condicional el 4 no aparece. Aunque no

hubiramos validado, la condicional le aplicara una demora de cuatro

meses siempre que no se indicara 0, 1, 2, o 3. Es conveniente dejar la

funcin condicional de esta forma, para evitar que quede sin resultado

posible si no se cumple ninguna de las condiciones y no dejamos una

puerta de salida. Nuestra ruta de escape, en este caso, es el C35 del

final de la ltima condicin, el valor que adoptar la frmula si no se

cumple ninguna de las condiciones previas.

Nuestra recomendacin general es que siempre trate de disear la

funcin SI sobre el papel, con lenguaje ordinario, y una vez que la tenga

clara, que la traslade a la hoja de clculo. Tambin reconocemos aqu

que no somos entusiastas de la ayuda de Excel para crear este tipo de

funciones desde el men insertar funcin. Ayuda que es muy til, sin

embargo, en otros casos.

Como siempre hay formas alternativas de realizar lo que acabamos

de desarrollar. Puede ver alguna de ellas en la seccin dedicada a

funciones de bsqueda y referencia, ms adelante.


46

7.5. Funcin Y

La funcin Y devuelve VERDADERO o FALSO cuando se cumplen o

no todas las condiciones que se incluyen en sus argumentos.

En nuestro caso queremos comprobar en qu duplas de valores de

A y B ambos superan los mnimos establecidos. Esa circunstancia slo

se da en la primera dupla, la de la fila 54, la nica que muestra el

resultado VERDADERO.

7.6. Funcin O

La funcin O devuelve VERDADERO o FALSO cuando se cumplen o

no alguna de las condiciones que se incluyen en sus argumentos.

En nuestro caso queremos comprobar en qu duplas de valores, o

bien A o bien B (al menos uno de ellos) superan los mnimos

establecidos. Esa circunstancia se da en los tres primeros pares de

valores que muestran el resultado VERDADERO.


47

7.7. Funcin NO

La funcin NO devuelve VERDADERO o FALSO cuando no se

cumple o s se cumple una condicin. Nos da el resultado contrario de

lo que se pregunta.

En nuestro caso nos indica FALSO en la pregunta de si 50 supera

a 40 y sin embargo nos indica VERDADERO al chequear 10 frente a 40.

7.8. Funcin SI.ERROR

Esta funcin nos permite indicarle a Excel un posible resultado

alternativo cuando un determinado clculo genera un error. El primer

argumento es el clculo que queremos realizar y el segundo es el

resultado que deseamos establecer como salida alternativa en caso de

error.

En nuestro caso le hemos indicado que en caso de error Excel

muestre N.D. De esta forma evitamos que en las columnas D y E, al

dividir entre 0 los intereses nos indique un error (no es posible dividir

entre cero). El valor alternativo puedes ser otra frmula, otro clculo.


48

8. Funciones de bsqueda y referencia

Este tipo de funciones nos pueden ayudar a buscar informacin y

trasladarla de la forma ms conveniente. Veamos algunas de ellas.

8.1. ELEGIR

Esta funcin nos permite seleccionar un dato de entre una serie de

datos siguiendo una posicin, o ndice, en la serie que se haya indicado.

En el ejemplo contamos con una serie de compras, en las celdas

C7:C15 y deseamos establecer los pagos por las mismas segn la

demora que se establezca en C4.

Observe que la funcin elegir establece como primer argumento el

nmero ndice que se utilizar para extraer el dato de la serie. En este

caso $C$4 +1; el sumarle 1 es necesario para permitir que la frmula

funcione con una demora en pagos de cero, pues la funcin no admite

el cero como ndice pues ningn dato representa la posicin cero en la

serie. Lo fijamos con los $ para poder copiar hacia abajo. El resto de

argumentos son las celdas que deben situarse en el orden de extraccin

que indicaremos con el ndice: en primer lugar nuestra posicin 0, esto

es pago al contado, que ser la primera posicin de la serie, C11, y a

continuacin pago a un mes, C10, y as sucesivamente. No fijamos

posiciones en la serie para que se adapten al siguiente mes a medida

que copiemos/peguemos hacia abajo.


49

8.2. La funcin DESREF

La funcin DESREF es, a nuestro modo de ver, una de las

funciones menos conocidas y sin embargo ms tiles. Nos permite

invocar una celda a un nmero de filas de distancia y otro nmero de

columnas de distancia de una celda referencia que indiquemos.

Apliqumoslo a nuestro ejemplo de compras y pagos.

En este caso indicamos que la celda de referencia son las compras

del propio mes y que debemos coger una celda situada 2 filas arriba (el

smbolo menos antes de $C$19 indica que hay que desplazarse hacia

arriba en las filas; si fuese positivo nos desplazaramos hacia abajo) y

en la propia columna C, pues indicamos 0 columnas (si el nmero de

columnas fuese positivo nos desplazaramos hacia la derecha y si fuese

negativo hacia la izquierda). Al dejar sin fijar la celda de referencia

posibilitremos su copia/pegado hacia el resto de meses.

Veamos un ejemplo en el cual DESREF trabaja en columnas en vez

de en filas.

Ahora el cero se lo ponemos al indicador de filas.


50

8.3. Funcin DESREF al detalle

Veamos un ejemplo de uso de DESREF extrayendo de una matriz

una celda a partir de la celda central siguiendo las indicaciones de filas

y columnas que se le hagan.

Como vemos en la celda C45 le indicamos que extraiga desde la

posicin D49 (la celda referencia) 0 filas (C43) hacia abajo y dos

columnas (C44) a la izquierda (por el signo menos). El resultado es,

naturalmente, celda 0.-2.

8.4. La funcin COINCIDIR

La funcin coincidir sirve para encontrar la posicin (fsica) de un

valor dentro de una serie. En el siguiente, en C60, usamos COINCIDIR

para encontrar el lugar que ocupa en la serie C57:G57 el valor indicado

en C59.

En nuestro caso la cifra 220 est en la tercera posicin.


51

En el siguiente ejemplo combinamos COINCIDIR con ELEGIR para

determinar qu empresa tiene la mayor y menor rentabilidad de una

serie de empresas.

Con las funciones MAX y MIN encontramos la rentabilidad mxima

y la mnima. Con COINCIDIR extraemos su posicin en la serie. Por

ltimo, con ELEGIR extraemos el nombre de la empresa que ocupa esa

misma posicin en la serie B66:B73.

En ambos casos, dentro de la funcin COINCIDIR tras marcar el

rango de bsqueda, (C66:C73), aadimos el valor 0, para indicar que la

bsqueda tiene que ser exacta, esto es que no busque un valor que se

aproxime sino justo el valor indicado. Esta funcin puede crearnos

problemas si el valor buscado est repetido en la serie, pues nos dar la

referencia de la primera vez que lo encuentre.

En el siguiente ejemplo combinamos COINCIDIR con DESREF para

la misma tarea que antes. Con las funciones MAX y MIN encontramos

la rentabilidad mxima y la mnima. Con COINCIDIR extraemos su

posicin en la serie. Por ltimo, con DESREF extraemos el nombre de la

empresa ese nmero de filas hacia abajo desde la posicin B78.


52

8.5. La funcin BUSCAR

Veamos otra funcin que nos permite resolver la bsqueda que

acabamos de ver con la funcin COINCIDIR. Se trata de la funcin

BUSCAR. Esta funcin tiene una variante para bsqueda horizontal, en

filas (BUSCARH), y otra para bsqueda vertical, en columna.

Comencemos con un ejemplo de BUSCARV (para bsqueda vertical).

Como vemos, en F93, le indicamos que busque el valor mnimo de

la columna en la que tenemos las rentabilidades, esto es,

MIN(B91:B98). A continuacin le indicamos la matriz que contiene toda

la informacin (B91:C98). Excel realizar la bsqueda sobre la primera

columna de esta matriz. Seguidamente le indicamos en qu columna de

la matriz est el resultado que queremos mostrar, en este caso en la

columna 2. Esto es, busca la rentabilidad mnima pero no nos devuelve

ese valor sino el que se encuentre en la misma fila pero en la columna

2. Por ltimo, le indicamos FALSO para que realice la bsqueda en la

matriz tal como est, sin reordenar la primera columna de mayor a

menor.


53

Observe que respecto al ejemplo anterior de la funcin COINCIDIR

hemos cambiado de orden las columnas de rentabilidad y nombre de las

empresas. Ello es debido a que la bsqueda siempre se realiza en la

primera columna de la matriz, y por ello hemos tenido que poner como

primera columna de la izquierda el rango de rentabilidades.

Veamos ahora un ejemplo realizando la bsqueda en una fila

(BUSCARH). Tratamos de conocer los aos en los que se produce el

mayor y el menor endeudamiento en una serie.

Como podemos ver, con BUSCARH el procedimiento es igual al de

antes. Le indicamos que busque el valor mnimo (cosa que har en la

primera fila) y que nos devuelva el valor de la misma columna pero de la

fila 2 de la matriz. Aadimos FALSO por idntica razn que antes.

8.6. La funcin INDICE

La funcin INDICE permite extraer un dato de una tabla indicando

un nmero de fila y columna. A menudo, las funciones COINCIDIR e

NDICE se utilizan juntas para realizar bsquedas.

En el siguiente ejemplo se pretende conocer el nmero de mujeres

que hay en el grupo 3. En primer lugar se indica el rango de bsqueda,

C112:E116. Posteriormente, la primera funcin COINCIDIR indica el

nmero de fila en el que se encuentra el Grupo 3, y la segunda indica el

nmero de columna en la que se encuentran las mujeres. As la funcin

devuelve, dentro de la matriz especificada, el valor de la fila 3 columna

2.


54

9. Funciones financieras

En esta seccin vamos a repasar con ejemplos las principales

funciones financieras que EXCEL tiene predefinidas.

9.1. La funcin INT.EFECTIVO

Esta funcin permite calcular el tipo de inters efectivo una vez se

le indique el tipo de inters nominal y el nmero de periodos de clculo

de intereses por ao.

Como vemos, un 11,55% nominal pagadero por cuatrimestres (3

pagos al ao) se convierte en un 12,00% efectivo.

9.2. La funcin TASA.NOMINAL

Esta funcin realiza la operacin contraria a la funcin

INT.EFECTIVO; permite calcular el tipo de inters nominal una vez se le

indique el tipo de inters efectivo y el nmero de periodos de clculo de

intereses por ao.


55

Como vemos, un 12,00% efectivo equivale a un 11,55% nominal

pagadero por cuatrimestres.

9.3. Funciones con anualidades: VA, PAGO, TASA, NPER

A continuacin veremos cuatro funciones que nos permiten

conocer todos los elementos vinculados al valor actual de una renta

constante: cuanta de la renta (PAGO), valor actual de la serie de rentas

(VA), nmero de periodos (NPER) y tasa de inters (TASA). Conocidos

tres de los elementos podemos conocer el cuarto.

En C23 calculamos el Valor Actual (con VA) de la renta conociendo

la tasa, el nmero de operaciones y la cuanta de la renta. Dado que

Excel usa el concepto de partida y contrapartida, si queremos que el VA

sea positivo hemos de introducir el Pago, la renta, en negativo, o bien

poner antes de VA el signo menos. Debemos introducir la tasa, el

nmero de periodos y la renta, en ese orden.

En D22 calculamos la cuanta de la renta (con PAGO) conociendo

los otros tres datos. En este caso ponemos en signo negativo el valor

actual de la renta. Debemos introducir la tasa, el nmero de periodos y

el VA de la renta, en ese orden.

En E20 calculamos la tasa (con TASA). Debemos introducir el

nmero de periodos, la cuanta de la renta y el VA de la renta, en ese

orden. Como puede observarse, ponemos con signo negativo el VA, pues

dicho parmetro y las rentas deben tener signo opuesto dada su

naturaleza de entrada/salida de fondos. De no ser as la operacin no

tendra sentido (con cuotas y cuanta en la misma direccin de entrada

o salida).


56

En F21 calculamos el nmero de periodos (con NPER). Como

vemos, los argumentos de la funcin son la tasa de inters, la renta, y el

VA, en ese orden.

9.4. Funcin VA aadiendo un pago final

A veces a una serie de anualidades contantes se le aade un ltimo

pago. Es un argumento opcional que podemos incorporar a cualquiera

de las frmulas del apartado anterior. En nuestro caso vemoslo

aplicado a la funcin VA.

En este caso, por variar, hemos puesto el signo menos antes de la

funcin. Incorporamos el ltimo argumento, C30, el pago final.

Estamos calculando el Valor Actual de una serie de cuantas

constantes ms una ltima cuanta final. El PAGO (la renta constante)

y el VF (el pago final) tienen el mismo signo.

9.5. Funcin VF aadiendo un pago inicial

Aunque no la hemos visto antes, tambin existe la funcin VF, que

permite conocer el valor final de una renta constante. La veremos ahora

aadiendo la presencia de un pago inicial.

Tambin en este caso rigen las mismas convenciones en cuanto a

los dignos que ya hemos indicado antes. Estamos calculando el Valor


57

Final de una serie de cuantas constantes ms una primera cuanta

inicial. El PAGO (la renta constante) y el VA (el pago inicial) tienen el

mismo signo.

En todas las funciones que acabamos de ver existe un ltimo

argumento opcional. Nosotros lo hemos omitido, lo que implica que

Excel asume que nuestra operacin es pos pagable. Si queremos que

considere todas las anualidades como prepagables deberamos incluir

un 1 como argumento final.

9.6. Las funciones PAGO, PAGOPRIN y PAGOINT

Estas tres funciones nos permiten calcular la cuota, la parte

correspondiente a amortizacin y a intereses, respectivamente, de una

operacin de prstamo de pagos constantes, dada una cuanta, una

tasa de inters y un plazo. La primera de ellas, PAGO, es independiente

del periodo para el que se calcule (pues es constante), y ya la hemos

visto antes, mientras que tanto el principal como los intereses son

diferentes para cada periodo y por tanto debe indicarse el periodo de

que se trate.

Como podemos observar PAGOPRIN y PAGOINT incorporan la celda

B47 entre los argumentos, para indicar que deseamos conocer dichas

cuantas para el primer periodo del prstamo. En los tres casos

ponemos la cuanta, C43, con signo negativo para que el resultado

aparezca en positivo. El prstamo y los pagos asociados tienen sentidos

opuestos. En un caso el dinero va del prestamista al prestatario y en el

otro va en sentido contrario.


58

Estas funciones tambin tienen opciones que nos permiten

indicarle si los intereses se pagan por anticipado, o si una parte de la

operacin se deja para una amortizacin final, al acabar la vida de la

operacin.

9.7. VNA

La funcin VNA permite conocer el Valor Actual de una serie de

cuantas no necesariamente constantes.

En la funcin VNA el primer argumento es la tasa de descuento y el

segundo argumento es el rango de datos, que VNA asume son

pospagables.

9.8. TIR

La funcin TIR extrae la tasa interna de rentabilidad de una serie

de cuantas, de las cuales al menos una ha de ser negativa.

Al menos uno de dichos datos ha de tener signo negativo.

Habitualmente, en una inversin, el primero de ellos. Si los datos son

anuales, la tasa de rentabilidad obtenida ser anual, si son mensuales,

ser mensual, etc.

9.9. Las funciones VNA.NO.PER y TIR.NO.PER

Nos permiten el clculo del Valor Actual y de la Tasa de

rentabilidad cuando los periodos de tiempo entre cada par de datos no


59

son constantes, cuando las fechas en que se produce cada flujo

implican una distancia no homognea entre ellos.

Como podemos observar hemos de indicarle el rango en el cual se

encuentran las fechas correspondientes a cada flujo (el nmero de

valores en el rango de flujos debe coincidir con el del rango de fechas).

10. Ejemplo de una funcin Array

Las funciones array, o de tipo matricial, son una herramienta muy

potente. A veces pueden ayudarnos a resolver situaciones complejas

aunque hay que tener cuidado con su uso para evitar cometer errores

que pueden ser de bulto.

Veamos un ejemplo para resolver una cuestin que ya tenemos

resuelta con VNA, en este caso.

Tenemos una serie de valores en la fila 7 as como una serie de

factores de descuento en la fila 8. La suma actualizada de esos valores,

que podemos calcular con VNA, tambin podramos calcularla con

SUMAPRODUCTO.

En D10 diseamos nuestra propia funcin suma producto, tipo

array. Le indicamos que sume el rango D7.H7 multiplicado por el rango


60

D8:H8. Es preciso validar la operacin con Ctrl + Shift + Enter. Esta

validacin le pondr corchetes a la frmula.

En D11 omitimos el paso de los factores de descuento de la fila 8.

Le indicamos que sume el rango D7:H7 tras haberlo dividido entre 1

ms la tasa de descuento y elevado al rango de los periodos (D6.H6).

11. Uso de la herramienta TABLA

La Herramienta Tabla de Excel se encuentra integrada en el men

de Datos, dentro del grupo de Herramientas de datos y concretamente

dentro del desplegable de Anlisis y Si. Junto a ella se encuentran las

herramientas de administracin de escenarios y bsqueda de objetivo,

que veremos posteriormente. Permite recalcular una determinada

frmula con diferentes valores para un determinado input o para dos

inputs de forma simultnea. Empecemos con un ejemplo en el que slo

variamos un input.

11.1. Tablas de un solo input, columna

En este caso queremos calcular cmo se ve afectado el Pago anual

que permite devolver 10.000 Euros durante 5 aos al variar la tasa de

inters (inicialmente establecida en el 7%).

Comenzamos calculando en C6 el pago con los datos iniciales.


61

En el rango B12:B18 establecemos una serie de tipos de inters

que queremos chequear, nuestros inputs alternativos. En nuestro caso

desde el 1% al 7%. En la columna de la derecha y una fila arriba, en

C11, vinculamos la celda C6, donde hemos hecho el clculo original y

que ser nuestra cabecera de tabla, el output. A continuacin

seleccionamos el rango B11:C18 y acudimos a la herramienta tabla en

el men. Nos aparecer una caja de dilogo como la siguiente:

Como tenemos los datos de las tasas de inters alternativas en una

columna, indicamos que la Celda de entrada (columna) es C5 y dejamos

en blanco la Celda de entrada (Fila). Pulsamos Aceptar y la tabla estar

realizada. En cada una de las celdas C12:C18 aparecer escrito

=Tabla(;C5) entre corchetes. Dichas celdas no pueden eliminarse o

modificarse de forma individual. La Tabla se maneja como un todo.

Al lado de cada tasa de inters aparecer el correspondiente valor

de la cuota. Una forma de comprobar que est bien realizada es ver que

el dato, en este caso, de la celda C18 (el referido al 7%), coincide con el

de la celda C11, referido al 7% original.

En el rango E11:F18 hemos replicado la misma operacin slo que

en este caso la celda F11, la cabecera de la tabla, la hemos formateado

con formato de nmero personalizado para que ponga el texto Cuota y

as la tabla quede con una mejor presentacin.

11.2. Tablas de un solo input, fila

Las tablas pueden contener los inputs de la celda que vara en una

fila. Usemos nuestro ejemplo para ver el impacto de diferentes periodos

o vencimientos de la operacin. El proceso es similar. En C23

conectamos la celda C6, la que contiene el resultado que queremos


62

analizar. A su derecha, una fila encima, establecemos los diferentes

aos que queremos comprobar, desde 1 a 8. Marcamos el rango

C22:K23, y acudimos a la herramienta Tabla indicando que la Celda de

entrada (Fila) es C4, y dejamos en blanco la Celda de entrada

(columna). Pulsamos Aceptar y ya est. Podemos comprobar que est

bien observando que las celdas C23 y H23 contienen el mismo valor.

Tambin en este caso hemos replicado la operacin ms abajo

tuneando, en C28, la cabecera de la Tabla, con el texto Cuota.

11.3. Tablas de dos inputs, fila y columna

Tambin podemos realizar tablas de doble entrada, esto es, que

sean dos las variables que toman valores cambiantes de forma

simultnea. Juntemos los dos ejemplos anteriores en uno solo.


63

Como vemos la celda resultado se sita en el vrtice superior

izquierda de la tabla. A su derecha se introduce el rango de variacin de

la variable fila y por debajo el rango de variacin de la variable columna.

Luego seleccionamos C34:K41 y seguimos el proceso de antes, pero

indicando ahora tanto la celda de entrada de fila como la de la columna.

Podemos comprobar que la celda H41 contiene el mismo valor que la

original, C34, pues en H41 confluyen la tasa y el horizonte originales.

Hemos replicado la tabla ms abajo. En este caso la cabecera de la

Tabla, en C44, la hemos tuneado cambiando el color del nmero, de

forma que coincida con el color del fondo de la celda, y no se vea.

Las tablas suelen ocupar bastante capacidad de clculo. En el

men Frmulas, en el submen Clculo, puede indicarse que las tablas

no se actualicen de forma automtica, lo cual libera capacidad. Si

hacemos esto debemos acordarnos de actualizarlas manualmente

cuando queramos ver los valores correctos. Para refrescar la hoja de

clculo pulse F9.


64

12. Herramienta Buscar objetivo

Las hojas de clculo facilitan mucho los trabajos de simulacin,

pues al modificar un input podemos inmediatamente observar su efecto

sobre cualquier resultado que de una u otra forma dependa de dicho

input. Pero a veces lo que deseamos es hacer el trabajo al revs.

Deseamos conocer qu input es preciso para obtener un ouput

determinado. Para eso podemos apoyarnos en esta herramienta.

Supongamos que tenemos acceso a una lnea de financiacin al 5%

anual y a 10 aos y slo podemos pagar 6.000 euros al ao de cuota.

Cul es el endeudamiento mximo que podemos afrontar?

Comenzamos por establecer la estructura de la operacin:

Como vemos hemos puesto un valor de prueba en el apartado de

Capital, en nuestro caso 1.000 Euros.

A continuacin nos dirigimos a Buscar objetivo en el grupo Anlisis

y Si del Men Datos, y rellenamos la caja de dilogo de la siguiente

forma:

Observar que en la caja Definir la celda situamos la celda que

contiene la frmula para la cual tratamos de encontrar un objetivo, en

este caso C6, donde se calcula el Pago, en la caja Con el valor

introducimos el valor objetivo buscado, y en la caja Para cambiar la


65

celda introducimos la celda que Excel deber modificar tratando de

provocar que la celda objetivo alcance el valor buscado.

Una vez pulsado Aceptar, aparecer lo siguiente, en este caso:

Nos informa de que ha encontrado una solucin, en este caso

46.330, y que la celda objetivo ha alcanzado el valor objetivo.

La celda objetivo debe contener una frmula, y la celda que se

modifica debe contener un valor. Evidentemente es preciso que la celda

a modificar y la celda objetivo estn vinculadas de alguna forma, y es

preciso que el problema tenga solucin. De no ser as, Excel no podr

encontrarla.

Esta herramienta es de gran utilidad cuando se buscan los puntos

de ruptura en un anlisis de sensibilidad.

Como truco que evita introducir la cuanta exacta del objetivo a

travs del men, proponemos lo siguiente:

En la celda C8 hemos establecido el objetivo. En la celda C10

calculamos la diferencia entre el valor de la celda resultado (C6) y dicho

Objetivo. A continuacin acudimos a Buscar objetivo y le indicamos que


66

queremos que la celda C10 valga cero cambiando la celda C3, esto es,

que queremos que la celda C6 coincida con el objetivo.

Siempre es m

fundamentos excel financiero

Documents