teoria funciones especiales

8/19/2019 teoria Funciones especiales

http://slidepdf.com/reader/full/teoria-funciones-especiales 1/16

Curso Excel avanzado Funciones especiales I Teoría 1

FUNCIONES ESPECIALES I

Esta primera sesión presenta funciones especiales, que añaden todala complejidad que se desee a las ojas de c!lculo, permitiendooperaciones de tipo condicional " de #$squeda que dotan a las ojasde posi#ilidades de toma de decisiones " de operaciones típicas delas #ases de datos%

CONTENIDO

Funciones especiales I%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

Funciones especiales%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

Funciones de tipo condicional%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

Función (I%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

Condición%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

E(*+ -C., E(-/0E ., E(TE2T.%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

C.-T %(I 3 (/0 %(I%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

Funciones de #$squeda " referencia%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%*/(C 6, */(C 7%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

si8nar nom#re a un ran8o%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

.tras funciones de inter9s%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

I;/ +%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

0I- 0 2%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

1




F/-CI.-E( E(<ECI +E(

Excel dispone de m!s funciones de las que puedes necesitar, porqueest! diseñado para m$ltiples usos, desde administrativos astacientí=cos% +o normal es que uses sólo unas pocas%

<ara facilitarte la elección, las funciones est!n clasi=cadas encate8orías%

#re la =c a Fórmulas " ver!s todas las posi#ilidades que tienespara ele8ir una función> Lógicas, Texto, Fecha y hora ?% Como espro#a#le que uses sólo unas pocas, puedes acudir al primer #otón dela izquierda, fx I sertar !u ció , que te permite acceder a todas%

Tam#i9n tienes un #otón similar en la línea de entrada de fórmulas%

(i pulsas so#re 9l, " eli8es la opción To"as , o#tendr!s el cat!lo8ocompleto de funciones% /na vez encontrada la que #usca#as, puedespulsar el #otón Ace#tar " así se insertar! la función en tu fórmula%

&




Como fec a inicial marcamos la celda C&, " a su derec a Excel latraduce a los días transcurridos desde el 51@51@1 55, A5 :'% Esto o

os a!ecta, es sólo u a curiosi"a" %

Como fec a =nal marcamos C:, que equivale a A54&A días%

l lle8ar a la *ase no sa#emos qu9 si8ni=ca% cudimos a la a"udapulsando so#re el enlace )Ayu"a so$re esta !u ció * sí lle8aremos a la p!8ina de a"uda>

En ella leemos que si consideramos el año real de : ' o : díasde#eremos escri#ir un 1% +o acemos " pulsamos ceptar% .#serva#ien la si8uiente ima8en%

.#tendremos el resultado de 5,'1)454&&, es decir, un poco m!s demedio año% (i multiplicas esa cantidad por : ' días o#tienes un

resultado de 14 días%

Insistimos que este es sóloun ejemplo para que si8aslos pasos, pero que estafunción no es pro#a#le quela uses nunca%

Bescu#rir!s con el uso quelas funciones las escri#ir!sdirectamente si recuerdas

su sintaxis% <or ejemplo, para la función < .0EBI. no necesitar!stanta a"uda, pues si escri#es directamente +P&O EDIO-C./C0.12

A




te devolver! el promedio de todas las cantidades escritas entre C& "C1&5%

'




F/-CI.-E( BE TI<. C.-BICI.- +

Estas funciones alteran su resultado se8$n se cum#la o oalgu as co "icio es % Es mu" interesante li8ar lo que aparece enuna celda a una característica previa que nos interese% 6emosal8unos ejemplos de resultados que dependen de una condición>

• En una lista de cali=caciones del 5 al , deseamos rotular comoE2CE+E-TE las puntuaciones ' " " como 0 + las de 5,1 "&%

• Beseamos contar los espacios en #lanco que a" en unacolumna%

• (i un n$mero es ne8ativo queremos escri#irle a su derec a<G BIB " si es positivo, ; - -CI

• /na cantidad no puede pasar de 155% (ería interesante que losdatos ma"ores que esa cantidad se truncaran a 155%

Todos estos ejemplos suponen una toma de decisión se8$n elcontenido de una celda% Esto es lo que consi8uen las funciones queveremos a continuación%

F/-CIH- (I

Es la función condicional #!sica% ct$a so#re una condición " si es3er"a"era se calcula u a #rimera !órmula y si es !alsa otrasegu "a %

SI-Co "ició 4 5alor si es 3er"a"era la co "ició 45alor si es !alsa2

SI-D67894774.62 si8ni=caría que si la celda B:A es ma"or que 4, elresultado que se escri#iría sería el AA, " en caso contrario el &:%

Esta función la usar!s cuando act$es so#re unos datos " operes deforma distinta se8$n sean ellos% Ejemplos>

• compras 8randes se les aplica un descuento " a las pequeñasno%




0es visos

Enero :AE2CE(I6.

Fe#rero ::E2CE(I6.

0arzo 1&#ril 14

0a"o A5E2CE(I6.

Kunio 1& Kulio

8osto )(eptiem#re 1'

.ctu#re :1E2CE(I6.

-oviem#re & E2CE(I6.Biciem#re &&

E2CE(I6.

0edia&1,4::::

::

Si o logras a la #rimera, i siste, %ue esto es muy im#orta te %Inventa t$ ejemplos parecidos, preferi#lemente relacionados con tu

tra#ajo actual%

C.-BICIH-

+lamamos Condición a una fórmula de Excel que nos devuelve uno delos dos valores 6E B BE . o F +(.%

+as condiciones m!s sencillas son las que est!n formadas por unacomparación, que se puede escri#ir con los si8uientes si8nos>

O I8ual queP 0a"or queQ 0enor queQP Bistinto aPO 0a"or o i8ualQO 0enor o i8ual

Toda comparación escrita con ellos, como &:PA, CAQPC',0&APOA' es una posi#le condición%

4




E(*+ -C., E(-/0E ., E(TE2T.

Estas tres funciones informan so#re el contenido de una celda " sonmu" $tiles com#inadas con la función (I>

E(*+ -C.

Bevuelve el valor ló8ico 6E B BE . si la celda ar8umento est!vacía% (e puede com#inar con (I>

SI-ES@LANCO-U a cel"a245alor si est e $la co4 5alor si o loest 2

Ejemplos>

SI-ES@LANCO-DB24*&elle a la cel"a DB*4 )5ale*2 > (i la celda B)est! en #lanco te recuerda que de#es rellenarla, " si no te devuelveun M6aleN%

[email protected] ES @LANCO 4 TIENE CONTENIDO 2 Essimilar a la anterior% (ólo te informa sin su8erirte nada%

E(-/0E .

Bevuelve el valor ló8ico 6E B BE . si la celda ar8umento contieneun n$mero%

SI-ESN E&O- 24 G.4 2 (i en R a" un n$mero, lo divideentre & " en caso contrario escri#e un espacio en #lanco%

Esta función te puede ser $til para que no operes con datos que nosean n$meros%

E(TE2T.

Es complementaria de la anterior% Bevuelve el valor ló8ico6E B BE . si la celda ar8umento contiene un texto%

C.-T %(I 3 (/0 %(I




Estas dos funciones son mu" $tiles, pero su funcionamiento no esdemasiado #ueno% Tienen muc as limitaciones%

C.-T %(I

+a primera, CONTA&'SI , cuenta las celdas contenidas en un ran8oque cumplen un criterio determinado% (u formato es

CONTA&'SI-&a go4Criterio2

El criterio suele ser una condición escrita entre comillas> MP&N,M*ur8osN, MQ&1,AN, M&&N,? (i el criterio consiste sólo en un n$mero,se pueden suprimir las comillas%

<or ejemplo, C.-T %(I 1&>C:&SN<9rezND cuenta la veces que

aparece <9rez en el ran8o 1&>C:&(i necesitas dos criterios o dos ran8os distintos, de#er!s usar lafunción CONTA&'SI'CONHUNTO% <or ejemplo, en la ta#la si8uiente

+etra -$meroA

( :( A

:

B &( :A

( 'CB )

Total " ma"or que: &

podemos desear contar cu!ntos datos tienen letra " n$mero ma"orque :% /saríamos, por ejemplo,+CONTA&'SI'CONHUNTO-C7/C064 A 4D7/D064 86 2, con unresultado de & datos%

(/0 %(I

(/0 %(I an8o de #$squedaS CriterioS an8o de sumaD

15




(/0 %(I, act$a de forma similar aC.-T %(I, pero en ella se puede añadirotro ran8o en el que se especi=que qu9datos se suman%

<or ejemplo, en el ran8o de la ima8en, sideseamos sumar los datos de la columnaC que se corresponden con el valor dela columna *, plantearíamos la función>

SU A&'SI-@7/@004*A*4C7/C002 " nos daría el resultado de 1A,suma de los tres datos que se corresponden con > A ' 'O1A%

ecuerda los tres datos que a" que dar>

• &a go "e $ s%ue"a > En este caso *A>*11, donde se #usca la

• Criterio/ M N, porque esa es la letra que #uscamos%

• &a go "e suma/ MCA>C11N nos indica dónde est!n lossumandos%

.tras funciones similares son

CONTA&'@LANCO> Cuenta las celdas vacías de un ran8o

CONTA&A> Cuenta las celdas no vacías%

Inventa situaciones similares a las que emos explicado " repasatodas las funciones aprendidas antes de se8uir adelante%

F/-CI.-E( BE *U(V/EB 3 EFE E-CI

*/(C 6, */(C 7

(on dos funciones de #$squeda de un elemento en una lista% -o sonf!ciles de aprender, pero cuando lo consi8as o#tendr!s esquemas dec!lculo mu" potentes%

(u formato es, en el caso de */(C 7>

11




@USCA&J-Eleme to %ue se $usca4 atriK o ra go e el %uehay %ue $uscar4 mero "e colum a "es"e la %ue "e3uel3e lai !ormació e co tra"a2

*/(C 7(e le dan como datos un valor determinado, una matriz en cu"aprimera =la a de #uscar " el n$mero de orden de la =la en la quede#e extraer la información paralela a la #uscada% sí, en la matriz

Teresa <a#lo 0aría ;ema

1 ) 1 )' 1 45 1 ))

#ril 0a"o Enero 0arzo

la función */(C 7 0aríaS0atrizS:D daría como resultado Enero "*/(C 7 <a#loS0atrizS&D nos devolvería el año 1 )' +a pala#ra0atriz quiere si8ni=car el ran8o en el que est9n los datos, por ejemplo

:>B D%

-o es f!cil de entender al principio% epasa asta que lo consi8as%

1&




*/(C 6

(imilar a la anterior, pero realiza la #$squeda por columnas en lu8arde por =las%

Esta función es mu" $til para MtraducirN un dato con otro de otracolumna que ocupe su mismo n$mero de orden% +o veremos mejorcon un ejemplo>

Ta$la "ee%ui3ale cias5 0 +

1 0 +& 0 +

Cali=cación> ','

: E;/+Comentario> *IE-

A E;/+' *IE-

*IE-) *IE-4 0/3 *IE-

0/3 *IE-15 E2CE+E-TE

En la ima8en puedes o#servar que la ta#la de equivalencia traduceunas puntuaciones de 5 a 15 en comentarios, como *IE- o E;/+ %

su derec a podremos escri#ir un dato en la ima8en ','D " lafunción */(C 6 le asi8nar! un comentario *IE-D%

Esta función */(C 6 necesita cuatro datos, aunque el $ltimo sepuede suprimir% En el ejemplo emos escrito esta fórmula>+@USCA&5-F94C>/D0>4.2

En la que se an concretado estos datos>

• Eleme to a $uscar/ F4, que es donde est! escrita lapuntuación de ','%

• Ta$la "e $ s%ue"a > C >B1 % En este dato as de escri#ir lata$la com#leta , no sólo la columna de #$squeda% <or eso se

a usado el ran8o C >B1 " no C >C1 %

1:




• Colum a > &, porque lo que #uscamos es un comentario, " estosse encuentran en la se8unda columna%

• Or"e a"o o "esor"e a"o > (i detr!s de estos datos noescri#es nada o añades un 1 O*/(C 6 F4SC >B1 S&S1DD seconsidera que los datos est or"e a"os " que puedesescri#ir uno que no est9 en la ta#la, como ','%

(i escri#es un 5 O*/(C 6 F4SC >B1 S&S5DD sólo se admitir!ncoincidencias exactas, " la ta#la podr! estar desordenada%

Esto es un poco complicado, por lo que quiz!s necesites practicar unpoco so#re ello% <or ejemplo, en esta ta#la de 8astos por mesesinserta a su derec a un pequeño módulo en el que si escri#es un meste devuelva el 8asto correspondiente% Es como consultar una #ase dedatos>

/sa la función */(C 6% Comienza escri#iendo en ella la celda F',despu9s la ta#la " =nalmente la columna donde est!n los 8astos 1 ó&D% (uerte%

Otras !u cio es similares

COLU NA > Bevuelve el n$mero de columna de una celdaFILA> Bevuelve el n$mero de =la de una celda

COINCIDI&> *usca un elemento en un ran8o " devuelve su n$merode orden%

(I;- -.0* E /- -;.

1A




En la misma =c a de Fórmulas puedes ver el #otón de Asig arom$re a u ra go % Esta utilidad te facilita la edición de fórmulas,

porque en lu8ar de usar referencias tipo LBLA o C11&, puedes usarnom#res%

<or ejemplo, en la ima8en se est!asi8nando el nom#re #rue$a auna lista del 1 al )%

Bespu9s, para encontrar supromedio o su suma, podr!sescri#ir P&O EDIO-#rue$a2 oSU A-#rue$a2, con lo que ellen8uaje de tus fórmulas ser!

m!s natural%<ara insertar un nom#re a un ran8o de#es se8uir estos pasos>

• #rir la =c a Fórmulas

• (eleccionar un ran8o o una celda

• <ulsar el #otón de Asig ar om$re a u ra go %

• En la ventana de asi8nación escri#ir el nom#re deseado " pulsa

Ace#tar no es conveniente cam#iar nada m!sD%

.T ( F/-CI.-E( BE I-TE G(

I;/ +

Compara "os ca"e as "e texto " devuelve 6E B BE . si sonexactamente i8uales " F +(. si no lo son% I=UAL reconocema"$sculas " min$sculas, pero pasa por alto las diferencias deformato%

<or ejemplo OI;/ + M<erezNSN<9rezND devolvería falso%

<uedes usar esta función para detectar pala#ras mal escritas, si launes con la función (I> O(I I;/ + CASC'DSNNSN0al escritoND nodevolvería nada si son i8uales, " escri#iría M0al escritoN si no lo son%

1'




0I- 0 2

m#as funciones devuelven el mínimo " el m!ximo respectivamentede los valores de un ran8o% (u formato es el mismo O0I- ran8oD oO0 2 ran8oD%

1

teoria funciones especiales

Documents