Formulas EXCEL

5/23/2018 Formulas EXCEL

1/14F. Javier Snchez San Romn http://web.usal.es/javisan/hidro/ 1

Frmulas en EXCEL

Las imgenes de pantalla que se incluyen aqu se captaron sobre Excel 2003. No obstante, lasexplicaciones son idnticas para Excel 2007. Cuando existe alguna diferencia, se indica cmohacerlo para ambas versiones. (Supongo que todo sea igualmente vlido paraExcel 2010)

Muchas veces la hoja de clculo resulta til simplemente para confeccionar tablas confacilidad; tambin se usa con frecuencia para realizar grficos. Y todo eso est muy bien. Pero elverdadero corazn de la hoja de clculo es el clculo (de ah su nombre!).

En una celda de EXCEL podemos escribir tres tipos de cosas: Palabras, textos (Jos Prez Lpez) Nmeros (51028) Frmulas (=7/2)

De este tercer tipo vamos a tratar aqu. Excel se entera de que es una frmula porque empiezapor el signo = 1.

Tambin se reconocen otros tipos de datos, como fechas y horas.

1. Uso como calculadora

No es habitual escribir frmulas en Excel solamentecon nmeros, pero creo que es muy til para tener unprimer contacto con el tema y aprender a escribirfrmulas en una sola lnea, manejo de parntesis, etc.

Picamos en una celda cualquiera (porejemplo en A3) y escribimos:

=927/(2,8-1,005) .

y, tras pulsar RETORNO o INTRO,aparecer en la misma celda el resultado de:927

2,8 1,005.

Observamos que en la propia celdaA3aparece el resultado(516,4354), pero ms arribaobservamos la frmulaque hemos escrito.

En cambio, si en una celda escribimos un numero o un texto, en la ventana de arriba se repite exactamente lomismo que en la propia celda.

Mediante el uso cuidadoso de los parntesis pueden escribirse en una lnea frmulas

complejas. Por ejemplo, la siguiente expresin:2

1726 1041

2,3 0,91,005

280,7

escrita en una lnea sera as:

=((1726-104)/(2,3*0,9)-1)/(28^2-1,005/0,7)

El asterisco (*) es el signo de multiplicar y el acento circunflejo (^) se usa para indicar laspotencias. Excel primero calcula las potencias (282), luego las operaciones de multiplicar ydividir, y finalmente sumar y restar; los parntesis nos permiten alterar este orden. Por tanto, en

1Si comenzamos con los signos + - , Excel tambin comprende que es una frmula y aade automticamenteel signo =.

Si por algn motivo necesitamos empezar en una celda con =, + pero queremos que Excel NO lo considerecomo frmula, lo precedemos con el apstrofo ( ), el cual queda invisible, no saldr escrito en la celda.



el denominador del ejemplo anterior nohan sido necesarios los parntesis indicados enrojo ( )en esta nueva versin:

=((1726-104)/(2,3*0,9)-1)/(28^2-(1,005/0,7))

aunque si los ponemos, se obtiene el mismo resultado. En cambio, los parntesis verdes( )sson necesarios, porque, como / y * tienen el mismo rango, si los hubiramos omitido yhubiramos escrito la frmula as:

(1726-104)/2,3*0,9 etc...primero efecta el parntesis, luego

divide por 2,3 y el resultado lo multiplicapor 0,9 (y en la frmula original 0,9 vadividiendo).

Frmulas con funciones. Todas lasfunciones de la calculadora cientfica mscompleja se encuentran tambin enEXCEL; veamos un ejemplo muy simple: Si

queremos calcular

log(2)

cos(30) 3 , escribimos:=LOG(2)/COS(30)/RAIZ(3)

Ejercicios propuestos

1) Reproduce la frmula de cuatro pisos de la pgina anterior (sin mirar la solucin!). Elresultado debe ser 1,000... Es muy fcil equivocarse. Comprueba que existe el mismo nmero de

parntesis de abrir que de cerrar. Cuando hay un error en una frmula (casi siempre al primerintento lo hay) la ayuda automtica de Excel no suele ser til, creo que lo mejor es rechazarla yrepasarla uno mismo.

2) Calcula la expresin siguiente (comprueba: el resultado debe ser 1,000...):2,1

log(2,48)

8 25,873

2. Operamos con referencias a celdas, no con nmeros

Aunque lo anterior a veces es til, no es habitual. En EXCEL normalmente no se realizanoperaciones con nmeros, sino con el contenido de otras celdas. As, si en una celda escribimos=A2+B7, all aparecer el resultado de sumar elcontenido de A2 ms el contenido de B7. Esto esmucho ms prctico. Veamos un ejemplo:

Clculo del volumen de un cono:Datos: Radio de la base= 12

Altura = 29

Introducimos esos datosen B4 y B5

En la celda B7 (podra ser otra cualquiera)escribimos la frmula del volumen de un cono:

2 . .

3

Radio alturaVolumen

=

En la figura vemos el resultado numrico, y en

la ventana superior la frmula introducida:=B4^2* PI( ) * B5/3



El nmero pi,, Excel lo calcula como una funcin, dejando vaco el hueco entre losparntesis , as: PI( ).Truco : En lugar de escribir a mano B4, B5 (vale tambin b4, b5), despus de escribir el signo = picamos en la

celda B4 y aparece escrito B4 en la frmula.

La frmula est viva: si cambiamos los datos, cambia el resultado. Si guardamos eldocumento, en el futuro nos servir para calcular el volumen de cualquier cono, basta escribir en

las celdas B4 y B5 los datos del cono en cuestin, y en la celda B7 aparecer el resultado.Lgicamente los rtulos que preceden a los nmeros (radio de la base: etc) son innecesarios

para el clculo, se ponen solamente para recordarnos el significado de cada valor.

3. Frmulas que incluyen un rango de celdas

Con frecuencia necesitamos efectuar algn clculo con un conjuntode celdas que forman una columna o una fila. Ejemplo: Deseamossumar 50 nmeros que ocupan las celdas C1 hasta C50, colocados puesen una columnavertical. Donde queramos que aparezca la suma

podramos escribir:=C1+C2+C3+... etc ... +C49+C50

El resultado sera correcto, pero trabajoso. El modo adecuado dehacerlo es el siguiente:

=SUMA(C1:C50)

Aqu el signo de dos puntos ( : ) no tiene nada que ver con ladivisin, indica el rango, el parntesis podemos leerlo as: desde C1hasta C50.

Lo anterior tambin puede utilizarse con varias celdas que estn en horizontal, por ejemplo:

=SUMA(A1:R1)sumara los contenidos de las celdas de la fila 1, desdeA1hasta R1.

Adems de la operacin de suma, hay muchas funciones que se refieren a un rango de celdas.Por ejemplo:

=PROMEDIO (A1:A50)calculara la media aritmtica de los contenidos de las celdas desdeA1hastaA50.

Truco: Si las celdas de inters caben en la pantalla o al menos no tienen una gran extensin, en lugar de escribir amano B2:B18, basta con picar en B2, y manteniendo pulsado, arrastrar hasta B18. La expresin B2:B18 apareceautomticamente en la frmula.

4. Funciones

Ya hemos hablado de las funcionesde Excel. Hemos visto que en las frmulas se incluyenpalabras especficas que ejecutan una operacin concreta: hemos visto: RAIZ, LOG, COS,SUMA, PROMEDIO, ... Todas estas son funciones de Excel. Todas tienen en comn que vanseguidas de parntesis, entre los que se hace referencia a la celda o celdas que intervienen en laaplicacin de esa funcin. As RAIZ (C2) efecta la raz cuadrada del contenido de la celda C2 oPROMEDIO (B1:B50) calcula la media aritmtica de los contenidos en el rango de celdas desdeB1 hasta B50. Algunas funciones requieren que los parntesis queden vacos, como PI(), quedevuelve el nmero .

Existen muchsimas funciones, pero es muy fcil encontrar la que necesitamos:

- Excel 2003: Men Insertar > Funcin...

- Excel 2007: Pestaa Frmulas> (a la izquierda:) Insertar funcin



Aparece un cuadro donde podemos elegir que nos muestre todas o solamente de alguna de lascategoras, mostrando tambin una breve descripcin de cada funcin. (Enaparecen explicaciones extensas con ejemplos).

Si en el cuadroInsertar funcinpicamos enAceptaraparece un asistente que desea ayundarnos a rellenarcorrectamente la funcin. Una vez que hemos visto la palabra y el formato de la funcin, me parece aconsejablepicar en Cancelary escribir la funcin manualmente.

Atencin: Si utilizamos Excel en otro idioma, las palabras asignadas a las funciones cambian.

5. Repetir una frmula en muchas celdas

Con frecuencia necesitamos escribir la misma frmula en varias celdas contiguas enhorizontal o en vertical, veamos dos ejemplos:

Aqu hemos escrito en E2el promedio delrango (B2:D2), que es la Nota Media del

primer alumno, y necesitamos escribir unafrmula anloga para el resto de los

alumnos, en la zona indicada con la flecharoja.Debemos clonar la frmula hacia abajo,siguiendo la flecha.

En este caso, en B6hemos calculado elpromedio del contenido en Na+de variospozos, y queremos escribir frmulasanlogas para mostrar las medias de loscontenidos en Ca++y en Mg++.Aqu debemos clonar la frmula hacia la

derecha, tambin siguiendo la flecha.

Veamos cmo se hace en el primer ejemplo: Despus de escribir la frmulaen la primera celda, situamos el cursor sobre ese cuadrito negro:

el cursor se convierte en una +, hacemosclic en el botn principal, y mantenindolo

presionado, extendemos hacia abajo, luegosoltamos (ver fig.):

Anlogamente podemos extender una frmula hacia la derecha (como en el segundo ejemplo),hacia arriba o hacia abajo.

Si queremos extenderla ms all de la pantalla, quiz varios cientos de casillas, en lugar de arrastrar el ratn, esms cmodo hacerlo de este otro modo:

Supongamos que quiero extender la frmula de E2hasta E894:

1.Escribo la frmula en E22. Terminada la frmula me sito de nuevo sobre E2 (al hacerIntro,habamos saltado aE3).3. Hago MAYSCULAS-clic sobre la celda E894.As hemos seleccionado desde E2hasta E894.



4. (Excel 2003: ) Men Edicin>>Rellenar>>Haciaabajo(o CTRL+J)(Excel 2007: ) Pestaa Inicio>>Rellenar>>Haciaabajo(o CTRL+J)

Ejercicio sugerido: Reproduce enExcel los datos de la figura, yutilizando la funcinPROMEDIO( ) obtn la nota media

por alumno y por materia. Debesobtener los resultados que aparecen enla figura. El 6,166... del recuadro azules la media de las medias y puedeobtenerse de dos modos: haciendo el

promedio de las medias de los alumnoso haciendo el promedio de las mediasde las materias.

Utilizacin del rellenado de celdas sinfrmulas:

Si utilizamosesta mismatcnica deextender elcontenido de unacelda, cuando stacontiene una

palabra o un nmero, slo conseguiremos repetir idnticamente esa palabrao ese nmero, lo que a veces resultatil (figura de la izquierda).

Si deseamos generar una serie de nmeros con un incremento determinado, por ejemplo: 105,110,115, etc. bastacon escribir los dos primeros, seleccionar ambos y despus arrastar hacia abajo:

Seleccionar dosceldas: arrastrar... y soltar:

6. Problema posible al extender una frmula

Veamos un ejemplo muy simple: en la columnaAaparecen una serie de valores brutos quedeben ser multiplicados por el coeficiente que aparece en C2 y los resultados deben aparecer enla columna B.

En B5debe aparecer el valor del producto:123*0,81. Para ello en B5debemos escribir: =A5*C2

El siguiente paso es extender la frmula haciaabajo, como hemos visto en el apartado anterior. Alhacerlo as, vemos que el resultado no es correcto,aparecen ceros (fig. siguiente), y necesitamos que a la

derecha del 214 aparezca el producto 214*0,81:La causa del error se aprecia picando en B6 y

observando la frmula que hemos generado: Al



extender la frmula hacia abajo hemos generado laserie de frmulas que aparecen en la tabla de abajoa la izquierda (MAL), y necesitamos conseguir lasfrmulas de la derecha (BIEN) de un modoautomtico:

Para que al extender la frmula hacia abajo, la referencia C2siga siendo C2, y no seconvierta en C3, C4, etc. precedemos el 2 por el signo $.

Escribimos en B5: =A5*C$2

Al extenderla hacia abajo generaremos unas frmulas equivalentes a la columna BIEN (el $dentro de la frmula no tiene ningn efecto en el resultado del clculo)

En otro caso anlogo, en que deseemos extender una frmulahacia la derecha, y que noqueremos que vara la letra; en ese caso se precede la letra del signo $. Por ejemplo: A5*$C2

Otro modo ms elegante de solucionar este problema es dndole nombre a las celdas. Lovemos en el apartado siguiente.

7. Darle nombre a celdas y a rangos de celdas

Vamos a rehacer el ejercicio del punto anterior. En primer lugar bautizamos la celda C2con el

nombre coef. Para ello picamos en la celda C2, y en la ventana arriba a la izquierda, escribimosel nombre elegido (coef), despus tecla Intro:

El resto es simple. En B5, en lugar de escribir lafrmula =A5*C$2, escribiremos: =A5*coef

Finalmente, extendemos la frmula hacia abajo,como aprendimos en el apartado 4, y el resultado esel mismo que utilizando C$2.

Tambin se le puede dar nombre a un grupode celdasque formen fila o columna.

Con el mismo ejemplo: Seleccionamos desde A5

hasta A8, y en la ventana superior le damosnombre: val_bruto(no se admiten espacios); ese esel nombre de todo el grupo de celdas.

Para obtener los mismos resultados, en la celdaB5, ahora escribimos la frmula as:=val_bruto*coef, luego la extendemos hacia abajo.

El uso de nombres de celdas o de grupos de celdas, adems de facilitar el clculo, muestra aprimera vista el significado de la frmula.

Construccin de una tabla de doble entrada

Vamos a confeccionar una tabla que presente el volumen de diversos conos (Ya hemos jugadocon la frmula del volumen de un cono en el apartado 2): en la cabecera de la tabla aparecenvalores posibles para el radiode la base; en la columna izquierda diversas alturas.

MAL BIEN

=A5*C2 =A5*C2=A6*C3 =A6*C2

=A7*C4 =A7*C2

=A8*C5 =A8*C2



1) Primero escribimos los datos:

2) Seleccionamos las celdas de color azulylas llamamos altura. Seleccionamos despus lasceldas en color naranjay las llamamos radio

3) En la celda C3escribimos la frmulainicial, utilizando los nombres de los rangos deceldas : =PI()*radio^2*altura/3 ; en la celda C3utilizar los datos: base 5 y altura 10 ..

4) Con el procedimiento habitual (que vimosen el apartado 3) extendemos la frmula primeroen una direccin, por ejemplo hacia la derecha:

5) Estando seleccionada toda la fila (C3:F3),extendemos la frmula hacia abajo, hasta llenartoda la tabla:

6) El resultado debe ser ste:

8. Frmulas condicionales

Esta es una de las herramientas ms tiles y verstiles, la posibilidad de bifurcarel clculo: sise cumple tal condicin haz esto, si no se cumple, haz esto otro.

Como siempre veamos un ejemplo: queremos hacer la raz cuadrada de una serie de nmeros(estn en la columna B).

Los nmeros negativos no tienen raz, as queslo debemos calcular la raz si el nmero es > 0,y si el nmero es negativo vamos a hacer queaparezca la palabra negativo.

Para ello escribimos la frmula que se ve en lafigura, y que explicamos a continuacin.

La estructura de una frmula condicionalsiempre es as:

=SI( pregunta ;qu debe hacer si la respuestaa la pregunta es VERDADERO

;qu debe hacer si la respuesta

a la pregunta es FALSO)

La estructura bsica es como la de cualquier funcin: =SI( ). Pero dentro de los parntesis haytres partes, separadas por punto y coma (; ).

En nuestro ejemplo la frmula sera as:



=SI(B2>=0;RAIZ(B2);"negativo")

Podemos leerla as: Si el contenido de B2es mayor o igual a 0, escribes aqu la raz de B2,en caso contrario, escribes la palabra negativo

Observamos que si en una frmula aparece texto, se indica entre comillas ( ) y que mayor oigual se anota >=

Otros ejemplos de preguntaspueden ser: A1= Aprobado, A1>B1 , A1=0;RAIZ(B2);)

esto es una variacin del ejemplo anterior: si en B2 hay un nmero negativo, en lugar deaparecer la palabra negativo la celda correspondiente quedar vaca.

Varias preguntas anidadas

Lo ms complejo de la frmula condicional (y muy til) es que en la parte donde hemos deescribir qu hacer si la respuesta es VERDADERO o qu hacer si FALSO, podemos introducirotra pregunta completa con la misma estructura que ya conocemos. Una pregunta dentro de otraquedara as:

=SI (P1;Qu_hacer_si_P1_es_VERDAD;SI (P2;Qu_hacer_si_P2_es_VERDAD;Qu_hacer_si_P2_es_FALSO))

En el esquema anterior P1 y P2 son preguntas. Lo que aparece subrayado ser ejecutado si larespuesta a la pregunta P1 es FALSO (el subrayado es slo para la explicacin, en la frmulano se subraya!).

Este anidamiento se puede repetir hasta 7 veces, con lo que la frmula se convierte en unhermoso rompecabezas.

Ejemplo con 2 condicionales anidadas:

Quiero aplicar una frmula que slo es vlida para el rango de temperaturas de 10 a 30C(Imaginemos que la supuesta frmula fuera 2,3*temp0,8)

El proceso de razonamiento puede ser as:

1. La temperatura es 20C?Si es VERDADERO, escribir el rtulo temp alta.

Si es FALSO, calcular la frmulaLa frmula sera as (previamente hemos bautizadocomo templas celdas donde estn las temperaturas):

=SI(temp20;"temp

alta";2,3*temp^0,8))

El diagrama de flujo2se muestra a la derecha:

2Estos diagramas se utilizan para mostrar el flujo de una programacin. Las bifucaciones (preguntas) se indicansiempre en un rombo; la ejecucin de operaciones en un rectngulo



Ejemplo con 3 condicionales anidadas:

Supongamos que tenemos una lista de lugares ydeseamos seleccionar los que tienen una pluviometramayor que 800 mm/ao, una temperatura media menorque 15C y una cota mayor que 450 m.

La primera columna la denominaremos P, lasegunda temy la tercera cota.

El diagrama de flujo se muestra a la derecha y lafrmula que debemos escribir en la columna Dsera la siguiente:

=SI(P>800;SI(tem450;"Correcto";"cota baja");"temp alta");"Poca prec")

9. Frmulas matriciales

Todas las frmulas que hemos visto hasta aqu tienen una cosa en comn: la frmula se alojaen una celda y el resultado de dicha frmula aparece en esa celda. En la propia frmula puedenintervenir una celda o varias, pero el resultado aparece en una sola.

Las frmulas matriciales son diferentes, van asociadas a un grupo (matriz) de celdas, es decir:

el resultado de la frmula aparecer en varias celdas. Vamos a ver dos ejemplos:

Transponer una matriz (convertir filas en columnas)

Transponer una matriz consiste enconvertir filas en columnas y viceversa,manteniendo el orden (la 1 fila ser la 1columna, etc.). En la figura adjunta vemosa la izquierda una pequea matriz de 2columnas y 6 filas. Deseamos que esosmismos datos aparezcan en 2 filas y 6columnas, tal y como se ven abajo a laderecha.

Esta operacin se efecta fcilmentecon la funcin =TRANSPONER, perocomo es una frmula matricial hay queintroducirla del siguiente modo:

1) Se selecciona el rectngulo dedestino con precisin. En este ejemplohemos tenido que seleccionar 2 filas x 6columnasexactamente.

2) Se teclea la frmula como se ve enla figura, escribiendo el rango origen, eneste ejemplo A1:B6 Debe escribirse arriba



en la barra de frmulas (marcado en rojo en la figura), y escribirlo a mano, nose puede pasar elcursor por el rango A1:B6.

Se aplica la formula matricial con CTRL-MAY-INTRO (manteniendo las teclas ControlyMaysculas, pulsar Introo Retorno).

Se muestra el resultado en las celdas, y arriba en la barra de frmulas aparece la frmula entrelos smbolos { }, indicando que se trata de una frmula matricial.

Generar una tabla de frecuencias

A partir de una larga serie de datos, a veces necesitamos contar el nmero de casos quequedan incluidos en intervalos sucesivos. Supongamos, por ejemplo, que tenemos un listado decalificaciones en las celdas A1:A18, y queremos contar los Suspensos (menor que 5), Aprobados(de 5 a 6,99), Notable (de 7 a 8,99) y Sobresalientes (9 o ms).

Hay que utilizar una frmula matricialpuesto que el resultado de la frmula aparecer envarias celdas (las celdas E11:E14 del ejemplo).

La funcin a utilizar es =FRECUENCIA, y los pasos a seguir son los siguientes:

1) Escribimos los lmites superiores de las categoras(excepto de la ltima, que no tiene

lmite superior). En el ejemplo, estos lmites se han escrito en C3:C52) Se seleccionan las celdas donde

queremos que aparezcan losresultados, en este caso las celdasE11:E14. Tiene que haber una msque lmites; en nuestro ejemplo,como queremos dividir en 4categoras, hemos escrito previamente3 valores lmite.

3) Arriba, en la barra de frmulas

escribimos (tecleando, nopasando elcursor por las celdas):=FRECUENCIA(A1:A18;C3:C5), osea, primero el rango en el que estnlos valores, despus el smbolo; yfinalmente el rango en el que seencuentran los lmites superiores.

4) Pulsamos Control,MaysculasyRetornosimultneamente. El resultadoaparece como en la figura, se generan

automticamente los smbolos { }.(En este ejemplo, los 18 valores se han colocado en orden para comprobar visualmente que el

resultado es correcto, pero no es necesario que estn ordenados)

Si los lmites los hubiramos escrito como 5, 7 y 9 en vez de como aparecen en la figura, losdatos que coinciden con los valores lmite se hubieran contabilizado en el intervalo inferior, yaque la funcin FRECUENCIA considera menor o igual que. Por tanto el valor 5,0 es < 5 por loque 5,0 se habra contabilizado como Suspenso. Anlogo error se hubiera producido con losdatos 7,0 y 9,0



Apndice. Breves nociones de VBA (Macros)Se requiere algn conocimiento de programacin BASIC

Con las frmulas de Excelse pueden realizar clculos ytablas infinitamente mscomplejos que los indicadosen estos ejemplos. Pero, a

pesar de sus enormesposibilidades, hay cosas queno pueden hacerse. Porejemplo: Tenemos la tablaadjunta y queremos que a laderecha de cada fila aparezcala suma de los tres valores mayores de esa fila.

Para casos que no pueden afrontarse con las frmulas, Excel incluye las Macros. Este es unnombre que seguramente se conserva por razones histricas (primeras versiones de Excel), en laactualidad se trata del lenguaje de programacin Visual Basic para Aplicaciones (VBA), que seinstala en nuestro ordenador junto con MsOffice (Word, Excel, etc.). Este lenguaje en lneasgenerales es parecido a todos los BASIC que hayan existido y muy similar al Visual BasicdeMicrosoft, que es una aplicacin diferente.

El ejemplo ms simple: Tomamos los datos de entrada de unas celdas,devolvemos los resultados en otras

En general, en un programa sencillo distinguimos tres partes:

Entrada de datos Proceso de clculo

Salida de los resultados

Ejemplo: Programa para multiplicar dos nmeros.

En un BASIC estndar sera as:

Entrada de datos(o bien: INPUT factor1, y el usuario teclea el

valor 25, etc.)

f act or1 = 25

f act or2 = 3

Proceso de clculopr oduct o =f act or 1 * f act or2

Salida de resultados(aparece el resultado 75 en la pantalla)

PRI NT pr oduct o

En VBA de Excel es igual, solamente hemos de decirle en qu celdas de Excel se encuentranlos datos de entrada, y en qu celdas debe colocar el resultado. Veremos con este ejemplo que

para referirnos a la celda B9 se utilizan las expresiones: Range( B9) o Cel l s(9, 2) :

Test 1 Test 2 Test 3 Test 4 Test 5 Test 6 Test 7 Test 8 Test 8 Suma tresmejores

Individuo 1 28 20 18 25 28 26 82

Individuo 2 29 25 23 28 29 16 86

Individuo 3 29 26 32 17 30 33 21 95

Individuo 4 9 13 26 34 15 75

Individuo 5 34 27 20 18 30 11 91

Individuo 6 33 12 24 18 19 76

Individuo 7 34 31 31 19 34 30 37 14 21 105



1) Abrir Visual Basic de Excel

1. Abrimos VBA: MenHerramientas>>Macro>>EditordeVisualBasic

2. En la nueva ventana queaparece: Men Insertar>>Mdulo

3. Aparece un panel blanco conun cursor. Escribimos: Subprueba1 (en lugar de prueba1,escribimos el nombre que queramosdar al programa3;), y tras pulsar latecla Retorno, automticamenteaparece lo siguiente:

2) Escribir el cdigo

Hay que comenzar a escribir elcdigo BASIC en el cursorparpadeante, entre Suby End Sub.

Para el programa indicado arriba,podra ser as:

La primera sentencia significa: Toma el valor de la celda A3 y asgnalo a la variablef actor 1

La ltima sentencia realiza la operacin inversa: El valor de la variable pr oduct oescrbeloen la celda C4

Otro modo de referirse a las celdases ste:

(con Cel l sen lugar de Range)

Este segundo cdigo produce exactamente el mismo resultado que el primero, pero ser mstil en el ejemplo siguiente. Cuidado: en la primera notacin (Range) se indica primero lacolumna y despus la fila, en la notacin Cel l sse indica primero la fila, despus la columna;

por ejemplo: Range( A3) equivale a Cel l s (3, 1) .

3) Guardar el programa escrito

Este cdigo que hemos escrito en VBA forma parte del Libro de Excel desde el que lohemos creado. Al guardar en Excel, se guarda tambin en el Editor de Visual Basic (Tambin

podemos Guardar...en el Editor de Visual Basic).

4) Ejecucin del Programa

Men Herramientas>>Macro>>Ejecutar. Aparece un panel en el que aparecen todos losSubque hayamos escrito, y elegimos el deseado.

La prxima vez que abramos el documento Excel, se abrir junto con l el programa VBA (o Macro) quehemos escrito. (Al abrirlo, aparece un cuadro que pide permiso para abrir Macros. Por supuesto: no hay ningnpeligro: el programa lo hemos hecho nosotros mismos). Para ejecutarlo ya no es necesario abrir Visual Basic,solamente si quisiramos modificar algo.

3El nombre pr oduct o no sera vlido porque en este programa lo usamos para una variable, ni el depalabras reservadas como FOR, NEXT, etc



Leer valores de un rango de celdas

Supongamos que necesitamos leer todos los valores de una tabla, fila por fila o columna porcolumna. Realizados los clculos pertinentes, quiz el resultado consista en un solo valor o enuna matriz de nmeros que necesitamos escribir automticamente en determinadas celdas denuestra hoja de Excel.

Leer los valores de una fila y escribir una

fila

El siguiente programa lee los valores de la 2fila, desde la casilla C2 hasta la K2, los almacenaen la matriz val or ( ) , desde val or ( 3) hastaval or ( 11) . Despus escribe esos mismos valoresen otro lugar de la hoja de clculo (en la fila 6).

Sub l ee_f i l a( )Di m val or ( 99)For col umna = 3 To 11

val or( col umna) = Cel l s( 2, col umna) . Val ue

Next col umna' Ahor a escr i bi r en l a f i l a 6. . .

For col umna = 3 To 11Cel l s( 6, col umna) . Val ue = val or ( col umna)

Next col umnaEnd Sub

Leer una col umna es si mi l ar. Por ej empl o, l eer en l a col umna 3, desde l af i l a 2 a l a 50 ser a:

For f i l a = 2 To 50val or ( f i l a) = Cel l s ( f i l a, 3) . Val ue

Next f i l a

Celda activa. Desplazarse por la hoja de clculo mediante el cdigo VBA

Si queremos escribir un cdigo que pueda leer una fila (por ej de 9 celdas) pero que puedaestar en cualquier lugar de la hoja de clculo, sera ste:

For col umna = 1 To 9val or ( col umna) = Act i veCel l . Val ueActi veCel l . Of f set ( 0, 1) . Sel ect

Next col umna

Pero atencin!: antes de ejecutar esta macro, en Excel debemos situarnos en el extremoizquierdo de la fila que queremos leer (puede ser cualquier celda). En la primera sentencia lee elvalor de la celda activa, en la siguiente sentencia, la celda activa se desplaza una celda a la

derecha: Acti veCel l . Of f set ( 0, 1) ; en la siguiente vuelta del ciclo For . . . Next de nuevolee la celda activa que ahora ya es la siguiente, etc...

Todo lo anterior en dos dimensiones

Supongamos que queremos leer una tabla que ocupa 9 filas y 5columnas :

Sub l eer Tabl a( )Di m val or ( 99, 99)For f i l a = 1 To 9

For col = 1 To 5val or( f i l a, col ) = Cel l s ( f i l a, col ) . Val ue

Next colNext f i l aEnd Sub

Para escribirla bastara el mismo cdigo, simplemente invirtiendo la



sentencia central, como hemos hecho anteriormente, as:

Cel l s( f i l a, col ) . Val ue = valor( f i l a , c o l )

Otra manera de hacer lo mismo: Vamos a escribir siempre en la "celda activa"; pero cada vezdesplazamos la celda activa a la siguiente. Supongamos que queremos escribir el bloque denmeros a partir de la celda E21:

Range( "E21") . Sel ectFor f i l a = 1 To 9For col = 1 To 5

Acti veCel l . Val ue = val or ( f i l a, col )Acti veCel l . Of f set ( 0, 1) . Sel ect

Next colAct i veCel l . Of f set ( 1, - 5) . Sel ect

Next f i l a

En la primera sentencia seleccionamos la celda E21. Despus vamos asignando datos siempreen la celda activa, que va saltando un paso a la derecha: Of f set ( 0, 1) . Al final de cada fila,salta una fila hacia abajo y cinco celdas hacia la izquierda: Of f set ( 1, - 5)

Solucin al ejemplo planteado al principioEl cdigo necesario para calcular la suma de los tres valores ms altos de cada fila (tabla de la

pgina 9) puede ser as:

Sub suma_3mej ores( )Di m val or ( 30)num_i ndi vi duos = 7num_t est s = 9Range( "C2") . Sel ect ( par a sel ecci onar C2, por que l a t abl a del

ej empl o comi enza en C2)' Ahor a l eo y guar do l os dat os del i ndi vi duo act ual en val or ( )For i ndi vi duo = 1 To num_i ndi vi duos

For t est = 1 To num_t est sval or ( t est ) = Acti veCel l . Val ueActi veCel l . Of f set ( 0, 1) . Sel ect

Next t est' Or deno de mayor a menor l as puntuaci ones de ese i ndi vi duoFor k = num_t est s - 1 To 2 Step - 1

For n = 1 To kI f val or ( n) < val or ( n + 1) Then

pr ov = val or ( n)val or ( n) = val or ( n + 1)val or ( n + 1) = prov

End I fNext n

Next k Una vez or denadas, sumo l as t r es mayor essuma_3_mej = val or( 1) + val or( 2) + val or( 3)Act i veCel l . Val ue = suma_3_mej Sal t o a l a f i l a si gui ent e, pr i mer a cel daActi veCel l . Of f set ( 1, - num_t est s) . Sel ectNext i ndi vi duoEnd Sub

Bibliografa (del Apndice)

Hansen (2004).-Mastering Excel 2003 programming with VBA, Sybex, 585 pp.

Walkenbach, J. (2004).-Excel 2003. Programacin con VBA. Anaya, 992 pp.

Walkenbach, J. (2004).-Excel VBA Programming forDummies. Wyley, 425 pp.

Formulas EXCEL

Documents

Transcript of Formulas EXCEL