Manual Excel Avanzado

Manual de Excel Avanzado Avanzado

CONTENIDO 1.

Formato Formato Personaliza Personalizado do de Celdas__ Celdas_____ ______ ______ ______ ______ ______ ______ ______ ______ ______ ______ ______ ______ ___4 4 a. Cuando Cuando crear crear formatos formatos personali personalizados zados_____ __________ __________ __________ __________ __________ __________ ___________ ___________ ______4 _4 b. Como crear formatos formatos personalizados_______ personalizados_________________ __________________ ________________ __________________ ________________4 ______4 c. Códigos Códigos de formato formato de númer númeroo para para fecha fechass  horas_____ horas__________ __________ ___________ __________ _________ __________ _______! __! d. Códigos Códigos de formato formato de número número para para posicion posiciones es decimale decimales" s" espacios" espacios" color colores es  condicion condiciones_ es_______# e. Códigos Códigos de forma formato to de númer númeroo para para texto texto  espaci espaciado__ ado_____ _______ ________ _______ _______ ________ ________ _______ ______$% ___$% f. Eliminar Eliminar un formato formato de número número personali personalizado__ zado_______ __________ __________ __________ __________ __________ _________ ________$ ____$$$ g. &i las fecha fechass introducid introducidas as en la ho'a ho'a de c(lculo c(lculo no no tienen tienen el mismo mismo aspecto_ aspecto___ ____ ____ ____ ____ ____ ____ _____ ___$$ $$

2.

Usar Funciones Funciones avanzadas avanzadas para calcular calcular valores__ valores_____ ______ ______ ______ ______ ______ ______ ______ _____11 __11 )unción &*MA+.&,__________________________________________________________________$-

Ejercicio Nº 1: Cálculos condicionales en bienes inmuebles___________________________12 a )unción C/01A+.&,__________ C/01A+.&,________________________ __________________________ _________________________ __________________________$2 _____________$2

Fórmulas Matriciales:__________________________________________________________14 a. Cómo crear crear un un total total bas(ndose bas(ndose en varias varias condici condiciones_ ones_____ _______ ______ _______ _______ ______ _______ _______ _______ _______$ ___$44 as )unciónes 3.E&,M/.ME0/+"  3.E&,M/.MA/+____________________________________$5 as )unciones +E6/06EA+.ME0/&"  +E6/06EA+.MA&______________________________$5 a )unción AEA1/+, AEA1/+,/_______________________ /__________________________________________ ______________________________________$7 ___________________$7 as )unciones E&____________________________________________________________________$7 a )unción 8/__________________________ 8/________________________________________ _____________________________ ____________________________$9 _____________$9 a )unción A8/+A_______________ A8/+A____________________________________ ______________________________________________ _____________________________$9 ____$9

Funciones Funcion es de Bases de Datos BDunción__________________________________________ BDunci ón__________________________________________1! 1! Ejercicio Nº 2: Cálculos con unciones de base de datos BD___________________________21 Funciones de "e#to____________________________________________________________22 a )unción ,:;*,E+6A___________________________ ,:;*,E+6A_________________________________________________________ __________________________________-____-a )unción 6E+EC8A_______________________ 6E+EC8A___________________________________ ______________________ ______________________ __________________-______-a )unción E<1+AE_________________________ E<1+AE____________________________________________________________ _______________________________________-2 ____-2 a )unción 8AA+_________________ 8AA+____________________________________ ______________________________________ ____________________________-2 _________-2 a )unción E0C/01+A+_______________ E0C/01+A+______________________________ ______________________________ ______________________________-4 _______________-4 a )unción A+=/_______________________________ A+=/_____________________________________________________________ __________________________________-4 ____-4 as )unciónes MA*&C" M,0*&C  0/M>+/>,/_________________ 0/M>+/>,/__________________________ _________________ _____________-4 _____-4 a )unción &*&1,1*,+__________________________ &*&1,1*,+_________________________________________ ______________________________ _____________________-5 ______-5 a )unción 6EC,MA______________ 6EC,MA_________________________________ ____________________________________ ______________________________-5 _____________-5

Ejercicio Nº $: %eneración de códi&os con unciones de te#to____________ te#to__________________ ___________ _______2' __2' Ejercicio Nº 4: Con(ertidor de N)meros en *etras__________________________________2! a )unción E&>AC,/&____________ E&>AC,/&__________________________ ____________________________ _____________________________ _______________________2$ ________2$ 3.

Validación de de Ingreso Ingreso de datos_____ datos_________ ________ _______ _______ ________ ________ ________ ________ ________ _______32 ___32 a. ;ue es la la valida validación ción____ ________ ________ ________ ________ ________ ________ ________ ________ ________ ________ ________ ________ ________ _________ _____22 b. Cómo validar el ingreso de datos en un rango de celdas______ celdas______________ _______________ ________________ ____________2___2c. +ealizar +ealizar una una auditor auditoria ia en en un rango rango validado_ validado______ __________ __________ _________ _________ __________ _________ _________ ________24 ___24

4.

!neas de "endencia "endencia en los #r$%icos_______ #r$%icos____________ _________ _________ _________ _________ _________ ________3 ____3& & a. *tilidad *tilidad de de las l?nea l?neass de tendenc tendencia__ ia______ ________ ________ _______ _______ _______ _______ _______ _______ ________ ________ ________ _______2! ___2! b. 1ipos de gr(fico @ue admiten admiten l?neas de tendencias_________ tendencias_____________________________ ____________________________2! ________2! c. Agregar Agregar una l?nea l?nea de tendenc tendencia ia a una serie serie de de datos__ datos______ ________ ________ ________ ________ ________ ________ ________ ______2# __2#

'.

Uso del (otón C$mara Fotogr$%i Fotogr$%ica_ ca____ ______ ______ ______ ______ ______ ______ ______ ______ ______ ______ ______ _____41 __41 a. Cómo habili habilitar tar el botón botón C(mara C(mara )otogr( )otogr(fica_ fica____ ______ _______ _______ _______ _______ ______ _______ _______ ______ _______ ________ ____4$ 4$ b. Cómo utilizar el botón C(mara )otogr(fica__________ )otogr(fica_________________ ______________ ______________ ______________ ____________4$ _____4$

>reparado por 6aniel :egarra :avaleta

>(g. $

Manual de Excel Avanzado Avanzado ).

a Programación Programación en *+cel______ *+cel___________ _________ ________ ________ _______ _______ ________ _______ _______ ________ _____42 _42 a. *so de la grabadora grabadora de macros___ macros________ __________ ___________ ___________ _________ __________ ____________ ___________ __________ _______4__4 b. E'ecución de una macro______ macro__________________ ________________________ ______________________ ______________________ __________________42 ______42 c. *so del Editor Editor de isu isual al Basic___ Basic______ _______ ________ ________ ________ _______ _______ ________ _______ _______ _______ _______ _______ ______44 ___44 d. >ara ingresar ingresar al Editor Editor de isu isual al Basic_ Basic______ ___________ ___________ __________ __________ __________ __________ __________ _________45 ____45 e. Modifica Modificarr una macro macro en el Editor de isu isual al Basic___ Basic______ ______ _______ _______ ______ ______ ______ ______ ______ ______ ______ ____45 _45

&.

Creación de ,acros ,acros con con la gra(adora gra(adora de macros____ macros_______ ______ ______ ______ ______ ______ ______ ______4& ___4& a. ;ue es la graba grabadora dora de macros_ macros____ _______ ________ _______ _______ ________ ________ _______ _______ ________ _______ _______ ________ ________ ____4! 4! b. Cómo modificar las instrucciones instrucciones de las macros________ macros_______________ _______________ ________________ ________________4! ________4!

-.

Creación de Plantillas en *+cel______ *+cel__________ _________ _________ _________ _________ ________ _________ __________ _____4& 4& a. ;ue es una >lantilla >lantilla____ _________ _________ ________ _________ _________ _________ __________ _________ ________ _________ _________ ________ ________4! ____4! b. Como se crea una >lantilla____________ >lantilla_____________________ _________________ __________________ ___________________ _________________4! ________4!

Ejercicio Nº +: ,cciones re-etiti(as con macros_____________________________________4. Ejercicio Nº /: 0n control de Caja Cica___________________________ Cica_______________________________________ ______________+1 __+1 Ejercicio  3esultados de una Encuesta__________________ Encuesta_____________________________ ________________________+' _____________+' a )unción ),A_____________________ ),A_________________________________ _________________________ ________________________ ______________________75 ___________75 a )unción ),A&__________________ ),A&_____________________________ ______________________ _____________________ _____________________ _________________75 ______75 .

*dición de ,acros ,acros en en Visual Visual /asic para para 0plicaciones__ 0plicaciones_____ ______ ______ ______ ______ ______ ______ ____)) _)) a. ;ue es isual isual Basic Basic para para Aplicac Aplicaciones iones_____ __________ ___________ __________ _________ __________ __________ __________ __________ ______77 _77 b. El Editor de isual isual Basic___________________ Basic_______________________________ _________________________ __________________________77 _____________77

1.

as instruc instruccion ciones es de Visual Visual /asic___ /asic______ ______ ______ ______ ______ ______ ______ ______ ______ ______ ______ _____) __)& & a. Continuar Continuar instrucc instrucciones iones en múltiple múltipless l?neas___ l?neas______ ______ _______ _______ ______ _______ _______ ______ _______ _______ _______ _______7 ___7## b. Aadir comentarios__________________ comentarios___________________________________________ ___________________________________________79 __________________79 c. Comprobar Comprobar errores errores de sinta sintaxis__ xis______ ________ ________ ________ ________ _________ _________ ________ ________ ________ ________ ________ ______79 __79

11. 11.

*scri(ir *scri(ir instrucc instruccione ioness de declara declaració ción__ n_____ ______ ______ ______ ______ ______ ______ ______ ______ ______ ______ ___) )

12.

*scri(ir *scri(ir instrucc instruccione ioness de asign asignació ación__ n_____ ______ ______ _____ _____ ______ ______ _____ _____ ______ ______ ______ ___& &

13.

"ra(a "ra(ao o con celdas celdas en en Vis Visual ual /asic /asic___ ______ ______ ______ ______ ______ ______ ______ ______ ______ ______ ______ ____& _& a. +eferenci +eferenciaa a celdas  rangos rangos utilizando utilizando la notaci notación ón A$____ A$_______ ______ ______ ______ ______ ______ ______ ______ ______ ______! ___!$$ b. >ara seleccionar seleccionar celdas  rangos de celdas_______________________________________ celdas___________________________________________!$ ____!$

Ejercicio Nº ': rocedimientos 5ub -ara seleccionar ran&os_______________ ran&os_________________________'2 __________'2 *a ro-iedad End_____________________________________________________________'! Ejercicio Nº .: Nombrar ran&os6 5eleccionar 5elecci onar columnas 7 Borrar__________________ Borrar _____________________.8 ___.8 c.

>ara ingres ingresar ar datos datos en celdas celdas  rango rangoss de celdas__ celdas_____ _______ _______ _______ ________ _______ _______ ________ ________ ________ ____#2 #2

Ejercicio Nº !: ,si&nar (alores a celdas 7 ran&os___________________________________.4 d.

*sos del mDtodo mDtodo &elect &elect  la propiedad propiedad &electio &election_____ n__________ __________ __________ ___________ ___________ __________ _______#7 __#7

Ejercicio Nº 18: rocedimientos rocedimi entos 5ub -ara Cambio de moneda_____ moneda ___________ ___________ ___________ _______.' _.' Ejercicio Nº 11: 11: rocedimientos rocedimi entos 5ub -ara ,orro ,orro -ersonal__________________________!8 -ersonal_______________________ ___!8 Pr$ctica de a(oratorio a(oratorio 1______ 1__________ ________ ________ ________ ________ ________ ________ ________ ________ ________ _______4 ___4

1ra arte:

Control Control de Cuentas Cuentas -or a&ar____ a&ar________ ________ _______ _______ ________ _______ ______ ______ ______ ______ _____!4 __!4

2da art arte: e:

"abla Diná Dinámic micaa -ara -ara 3esume 3esumen n de Cuent Cuentas as -or -or a&ar_ a&ar___ ____ ____ ____ ____ ____ ____ ____ ___!. _!.

Pr$ctica de a(oratorio a(oratorio  2________ 2______________ ___________ ___________ ____________ ____________ ____________ ___________ _____

1ra arte: arte:

Creación Creación del cuadro cuadro Mo(imient Mo(imientos os de ,lmac9n_ ,lmac9n_____ ________ ________ ________ ________ ________ _____!! _!!


>(g. -

Manual de Excel Avanzado Avanzado ).

a Programación Programación en *+cel______ *+cel___________ _________ ________ ________ _______ _______ ________ _______ _______ ________ _____42 _42 a. *so de la grabadora grabadora de macros___ macros________ __________ ___________ ___________ _________ __________ ____________ ___________ __________ _______4__4 b. E'ecución de una macro______ macro__________________ ________________________ ______________________ ______________________ __________________42 ______42 c. *so del Editor Editor de isu isual al Basic___ Basic______ _______ ________ ________ ________ _______ _______ ________ _______ _______ _______ _______ _______ ______44 ___44 d. >ara ingresar ingresar al Editor Editor de isu isual al Basic_ Basic______ ___________ ___________ __________ __________ __________ __________ __________ _________45 ____45 e. Modifica Modificarr una macro macro en el Editor de isu isual al Basic___ Basic______ ______ _______ _______ ______ ______ ______ ______ ______ ______ ______ ____45 _45

&.

Creación de ,acros ,acros con con la gra(adora gra(adora de macros____ macros_______ ______ ______ ______ ______ ______ ______ ______4& ___4& a. ;ue es la graba grabadora dora de macros_ macros____ _______ ________ _______ _______ ________ ________ _______ _______ ________ _______ _______ ________ ________ ____4! 4! b. Cómo modificar las instrucciones instrucciones de las macros________ macros_______________ _______________ ________________ ________________4! ________4!

-.

Creación de Plantillas en *+cel______ *+cel__________ _________ _________ _________ _________ ________ _________ __________ _____4& 4& a. ;ue es una >lantilla >lantilla____ _________ _________ ________ _________ _________ _________ __________ _________ ________ _________ _________ ________ ________4! ____4! b. Como se crea una >lantilla____________ >lantilla_____________________ _________________ __________________ ___________________ _________________4! ________4!

Ejercicio Nº +: ,cciones re-etiti(as con macros_____________________________________4. Ejercicio Nº /: 0n control de Caja Cica___________________________ Cica_______________________________________ ______________+1 __+1 Ejercicio  3esultados de una Encuesta__________________ Encuesta_____________________________ ________________________+' _____________+' a )unción ),A_____________________ ),A_________________________________ _________________________ ________________________ ______________________75 ___________75 a )unción ),A&__________________ ),A&_____________________________ ______________________ _____________________ _____________________ _________________75 ______75 .

*dición de ,acros ,acros en en Visual Visual /asic para para 0plicaciones__ 0plicaciones_____ ______ ______ ______ ______ ______ ______ ____)) _)) a. ;ue es isual isual Basic Basic para para Aplicac Aplicaciones iones_____ __________ ___________ __________ _________ __________ __________ __________ __________ ______77 _77 b. El Editor de isual isual Basic___________________ Basic_______________________________ _________________________ __________________________77 _____________77

1.

as instruc instruccion ciones es de Visual Visual /asic___ /asic______ ______ ______ ______ ______ ______ ______ ______ ______ ______ ______ _____) __)& & a. Continuar Continuar instrucc instrucciones iones en múltiple múltipless l?neas___ l?neas______ ______ _______ _______ ______ _______ _______ ______ _______ _______ _______ _______7 ___7## b. Aadir comentarios__________________ comentarios___________________________________________ ___________________________________________79 __________________79 c. Comprobar Comprobar errores errores de sinta sintaxis__ xis______ ________ ________ ________ ________ _________ _________ ________ ________ ________ ________ ________ ______79 __79

11. 11.

*scri(ir *scri(ir instrucc instruccione ioness de declara declaració ción__ n_____ ______ ______ ______ ______ ______ ______ ______ ______ ______ ______ ___) )

12.

*scri(ir *scri(ir instrucc instruccione ioness de asign asignació ación__ n_____ ______ ______ _____ _____ ______ ______ _____ _____ ______ ______ ______ ___& &

13.

"ra(a "ra(ao o con celdas celdas en en Vis Visual ual /asic /asic___ ______ ______ ______ ______ ______ ______ ______ ______ ______ ______ ______ ____& _& a. +eferenci +eferenciaa a celdas  rangos rangos utilizando utilizando la notaci notación ón A$____ A$_______ ______ ______ ______ ______ ______ ______ ______ ______ ______! ___!$$ b. >ara seleccionar seleccionar celdas  rangos de celdas_______________________________________ celdas___________________________________________!$ ____!$

Ejercicio Nº ': rocedimientos 5ub -ara seleccionar ran&os_______________ ran&os_________________________'2 __________'2 *a ro-iedad End_____________________________________________________________'! Ejercicio Nº .: Nombrar ran&os6 5eleccionar 5elecci onar columnas 7 Borrar__________________ Borrar _____________________.8 ___.8 c.

>ara ingres ingresar ar datos datos en celdas celdas  rango rangoss de celdas__ celdas_____ _______ _______ _______ ________ _______ _______ ________ ________ ________ ____#2 #2

Ejercicio Nº !: ,si&nar (alores a celdas 7 ran&os___________________________________.4 d.

*sos del mDtodo mDtodo &elect &elect  la propiedad propiedad &electio &election_____ n__________ __________ __________ ___________ ___________ __________ _______#7 __#7

Ejercicio Nº 18: rocedimientos rocedimi entos 5ub -ara Cambio de moneda_____ moneda ___________ ___________ ___________ _______.' _.' Ejercicio Nº 11: 11: rocedimientos rocedimi entos 5ub -ara ,orro ,orro -ersonal__________________________!8 -ersonal_______________________ ___!8 Pr$ctica de a(oratorio a(oratorio 1______ 1__________ ________ ________ ________ ________ ________ ________ ________ ________ ________ _______4 ___4

1ra arte:

Control Control de Cuentas Cuentas -or a&ar____ a&ar________ ________ _______ _______ ________ _______ ______ ______ ______ ______ _____!4 __!4

2da art arte: e:

"abla Diná Dinámic micaa -ara -ara 3esume 3esumen n de Cuent Cuentas as -or -or a&ar_ a&ar___ ____ ____ ____ ____ ____ ____ ____ ___!. _!.

Pr$ctica de a(oratorio a(oratorio  2________ 2______________ ___________ ___________ ____________ ____________ ____________ ___________ _____


Creación Creación del cuadro cuadro Mo(imient Mo(imientos os de ,lmac9n_ ,lmac9n_____ ________ ________ ________ ________ ________ _____!! _!!


>(g. -

Manual de Excel Avanzado Avanzado 2da arte:

"abla "abla de de 5aldos 5aldos Finales Finales 7 %ráico %ráico de de Mo(imie Mo(imientos ntos -or e;culo_ e;culo__________188

$ra arte:

+ta arte: arte:

Creación Creación de una macro macro -ara obtener obtener el =arde# =arde# de cada (e;cu (e;culo_ lo___ ____ ____ ____ ___182 _182

Pr$ctica de a(oratorio a(oratorio  3________ 3______________ ___________ ___________ ___________ ___________ ____________ ___________1 _____14 4 Pr$ctica de a(oratorio a(oratorio  4________ 4______________ ___________ ___________ ___________ ___________ ____________ ___________1 _____1 


Creación Creación de la *ista de recios_ recios_____ _________ _________ ________ ________ ________ ________ ________ ________1 ____118 18

2da arte:

Creación Creación de la *ista *ista de Clientes_ Clientes____ ______ ______ _____ _____ ______ ______ ______ ______ _____ _____ ______ ____11 _1111

$ra arte: Creación de la Boleta de enta_____ enta__________ __________ __________ __________ _________ _________ __________1 _____111 11 4ta arte: arte:

"raer "raer la oja >ue con(ierte con(ierte n)meros n)meros en letras____ letras_______ ______ ______ ______ ______ ______ ______ ____11 _11$$

+ta arte: arte:

Creación del 3e&istro 3e&istro de entas_ entas_____ ________ _________ _________ _________ _________ ________ ________ _______11 ___11++

Crear la macro @ue +egistra una Boleta de enta__________________________________________$$5 Crear la macro @ue prepara una 0ueva Boleta de enta_____________________________________$$7

Códi&os ,5C?? -ara caracteres es-eciales:________________________________________11.


>(g. 2


1. Formato Formato Persona Personaliza lizado do de Celdas Celdas a. Cuando Cuando crear crear forma formatos tos person personaliz alizados ados a apariencia @ue pueden ad@uirir los números en las celdas" pueden variar según el formato @ue se les asigne  para esto existen varias categor?as de formatos. >ero si estas estas no satisfa satisfacen cen nuestra nuestrass necesid necesidade ades" s" uno mismo mismo pued puedee crear crear sus propios propios formatos de números personalizados.

E'emplo de formatos tradicionales existentes en Excel A 1

3,473.50

Estilo millares

#,##0.00

2

S/. 67 672.25

Estilo moneda

S/. #,##0.00

3

13.7%

Estilo porcentual

0.0%



(7.5!

"eati$os entre parntesis

(0.00!

!

4&'un

ec)as con d*a + mes

d&mmm

E'emplo de formatos personalizados creados por el usuario A "

64.5 

-ara pesos en iloramos

0.0 

#

37.4 º

rados cent*rados

0.0

-ara cdios

000000

C

$

000254

%

 1%

e8to + porcenta9e

 0%

1&

4 puntos

":mero + te8to

0 puntos


>(g. 4


1enga en cuenta @ue en todos los e'emplos anteriores las celdas tienen datos numDricos  aun@ue el formato ha cambiado su apariencia" Fel contenido de ellas si&ue siendo num9ricoG  puede operarse matem(ticamente en cual@uier formula. Nota.-

Cuando se crean formatos personalizados, estos formatos solo tendrán validez dentro del libro en el cual se crearon.

>ara poder crear formatos personalizados ha @ue seguir unas cuantas reglas" las cuales pasamos a indicar a continuación '. Como Como crear crear formatos formatos pers personal onalizado izados s En el menú Formato@Celdas" elegir la categor?a ersonaliAada

Escribir a>u; el ormato -ersonaliAado

En la casill casillaa de texto texto llamad llamadaa "i-o se pueden definir formatos personalizados o elegir los a existentes en la lista inferior. Crear un ormato de n)mero -ersonaliAado


>(g. 5

Manual de Excel Avanzado Avanzado 1. Selecci Seleccione one las las celdas celdas a las las que desea desea dar form formato ato.. 2. En el menú ormato, ormato, !a"a clic clic en Celdas # !a"a clic en en la fic!a fic!a Número Número.. $. En la lista Cate"or%a, Cate"or%a, !a"a clic clic en una una cate"or%a cate"or%a #, #, a continua continuaci&n, ci&n, !a"a !a"a clic en un formato inte"rado que se aseme'e al que se desee. (. En la lista lista Cate Cate"or% "or%a, a, !a"a !a"a clic clic en )erson )ersonaliz alizada ada.. *. En el cuadro cuadro +ip +ipo, o, modifique modifique los los c&di"os c&di"os de formato formato de número número para para crear crear el formato que que desee.

>uede especificar hasta cuatro secciones de códigos de formato. as secciones" separadas por caracteres de punto  coma" definen los formatos de los números positivos" números negativos" valores cero  texto" en ese orden. &i especifica sólo dos secciones" la primera se utiliza para los números positivos  ceros"  la segunda se utiliza para los números negativos. &i especifica sólo una sección" todos los números utilizan ese formato. &i omite una sección" inclua el punto  coma @ue separa esa sección. ormato de n:meros positi$os

ormato de ceros

()((&.&&*+ , -O/O0( )((&.&&+ , &.&& , Factura 4 ormato de n:meros neati$os Nota.-

ormato de te8tos

El s%mbolo de arroba  representa el contenido de la celda cuando este contenido es del tipo teto.

*tilice códigos de formato @ue describan cómo desea mostrar un número" una fecha u hora" una moneda" porcenta'es o notación cient?fica  un texto o espacio. Ejem-lo:

A un grup grupoo de celd celdas as con con dife difere rent ntes es dato datoss se le da el sigu siguie ient ntee form format atoo personalizado -A5670()((&.&&*+ , -O/O0( )((&.&&+ , 8in 9alor , 4 anulada A 1

:$#3.%

2

&

ero

3

Factura

e8to



"2!.2!$

!

;oleta


":mero neati$o

":mero positi$o e8to

>(g. 7

Manual de Excel Avanzado

El resultado final en el rango formateado es el siguiente A 1

$)#3.%&+

Entre parntesis + color ro9o

2

8in 9alor

ero cam;ia a Sin alor

3

Factura anulada



")2!.2"

!

;oleta anulada

Se le area la pala;ra anulada
Como se puede ver" cada celda ad@uiere el formato @ue le corresponde según el contenido @ue esta tenga. c. Cmero para fec?as @ ?oras 6?as" meses  aos >ara presentar d?as" meses  aos" inclua en una sección los siguientes códigos de formato. &i utiliza una HmH inmediatamente detr(s del código HhH o HhhH" o bien inmediatamente delante del código HssH" Microsoft Excel presentar( los minutos en lugar de presentar el mes. ara (er

0se este códi&o

os meses como $I$-

m

os meses como %$I$-

mm

os meses como ene.Idic.

mmm

os meses como eneroIdiciembre

mmmm

os meses como la inicial de cada mes

mmmmm

os d?as como $I2$

d

os d?as como %$I2$

dd

os d?as como lun.Is(b.

ddd

os d?as como lunesIs(bado

dddd

os aos como %%I99

aa

os aos como $9%%I9999

aaaa

8oras" minutos  segundos >ara presentar horas" minutos  segundos" inclua en una sección los siguientes códigos de formato.


>(g. !

Manual de Excel Avanzado ara (er

0se este códi&o

as horas como %I-2

h

as horas como %%I-2

hh

os minutos como %I59

m

os minutos como %%I59

mm

os segundos como %I59

s

os segundos como %%I59

ss

as horas como 4 a.m.

h a.m.Jp.m.

a hora como 427 p.m.

hmm a.m.Jp.m.

a hora como 427%2 p.

hmmss aJp

El tiempo transcurrido en horasK por e'emplo" -5%-

Lhmm

El tiempo transcurrido en minutosK por e'emplo" 7247

Lmmss

El tiempo transcurrido en segundos

Lss

)racciones de segundo

hmmss.%%

&i el formato contiene la indicación a.m. o p.m." la hora se basar( en el formato de $- horas" donde Ha.m.H o HaH indica las horas desde la medianoche hasta el mediod?a  Hp.m.H o HpH indica las horas desde el mediod?a hasta la medianoche. En caso contrario" el relo' se basar( en el formato de -4 horas. a letra HmH o las letras HmmH deben aparecer inmediatamente detr(s del código HhH o HhhH" o bien inmediatamente delante del código HssHK de lo contrario" Microsoft Excel presentar( el mes en lugar de presentar los minutos.

d. Cmero para posiciones decimales) espacios) colores @ condiciones

*tilice los códigos de formato de número para crear un formato de número personalizado. Decimales 7 d;&itos si&niicati(os

>ara dar formato a las fracciones o los números con decimales" inclua los siguientes d?gitos marcadores en una sección. &i un número tiene m(s d?gitos a la derecha del separador @ue marcadores en el formato" se redondear( para @ue tenga tantos decimales como marcadores. &i ha m(s d?gitos a la iz@uierda del separador @ue marcadores" se presentar(n los d?gitos adicionales. &i el formato contiene


>(g. #

Manual de Excel Avanzado solamente signos de número NOP a la iz@uierda del separador" los números menores @ue la unidad comenzar(n por el separador. 

muestra únicamente los d?gitos significativos  no muestra los ceros sin valor.

8

NceroP muestra los ceros sin valor si un número tiene menos d?gitos @ue la cantidad ceros en el formato.



agrega los espacios de los ceros sin valor a cada lado del separador decimal" para alinear los decimales con formato de fuente de ancho fi'o" como Courier 0eQ. 1ambiDn puede utilizarse R para las fracciones @ue tengan un número de d?gitos variable. ara (er

0se este códi&o

$-24.59 como $-24.7

OOOO.O

#.9 como #.9%%

O.%%%

.72$ como %.7

%.O

$- como $-.%  $-24.57# como $-24.5!

O.%O

44.29#. $%-.75  -.# con decimales alineados

RRR.RRR

5.-5 como 5 $J4  5.2 como 5 2J$%" con los s?mbolos de división alineados

O RRRJRRR

5e-arador de millares

>ara ver una coma como separador de los millares o para a'ustar la escala de un número mediante un múltiplo de mil" inclua una coma en el formato de número. ara (er

0se este códi&o

$-%%% como $-"%%%

O"OOO

$-%%% como $-

O"

$--%%%%% como $-"-

%"%""

Color

>ara definir el color de una sección del formato" escriba en la sección el nombre de uno de los siguientes ocho colores entre corchetes. El color debe ser el primer elemento de la sección.


>(g. 9


L0E=+/

LA:*

L)*C&,A

L+/S/

LA=*AMA+,0A LE+6E LBA0C/ LAMA+,/ Condiciones

>ara definir los formatos de número @ue se aplicar(n únicamente si coinciden con las condiciones @ue se haan especificado" encierre la condición entre corchetes. a condición consta de un operador de comparación  un valor. >or e'emplo" el siguiente formato muestra los números iguales o inferiores a $%% en color ro'o  los números superiores a $%% en color azul. 3ojoG188H,AulI188

En cambio" para aplicar formatos condicionales a las celdas Npor e'emplo" el sombreado de color @ue depende del valor de una celdaP utilice el comando )ormato condicional en el menú )ormato.

e. Cmero para teto @ espaciado ,&re&ar caracteres

>ara ver texto  números en una celda" encierre los caracteres de texto entre comillas NHHP o ponga delante de un solo car(cter una barra invertida NTP. ,nclua los caracteres en la sección correspondiente de los códigos de formato. >or e'emplo" introduzca el formato U %.%% HExcesoHKVU %.%% H6efectoH para ver un importe negativo como HVU$-5.!4 6efectoH. El espacio  los siguientes caracteres se muestran sin comillas U V W J N P  X Y Z [ Ncomilla simple iz@uierdaP [ Ncomilla simple derechaP \ ] ^  `  ?ncluir una sección de te#to

&i se inclue una sección de texto" siempre ser( la última sección en el formato de número. ,nclua el car(cter  en la sección en @ue desee presentar el texto escrito en la celda. &i se omite el car(cter  en la sección de texto" no se ver( el texto @ue se haa escrito. &i desea ver siempre caracteres de texto espec?ficos con el texto escrito" encierre el texto adicional entre comillas dobles NH HP" por e'emplo" Hfacturación bruta de H &i el formato no inclue una sección de texto" el texto @ue escriba no se ver( afectado por el formato. ,&re&ar es-acio


>(g.

%$ Manual de Excel Avanzado >ara crear un espacio con el ancho de un car(cter en un formato de número" inclua un subraado _ seguido del car(cter. >or e'emplo" si despuDs de un subraado se cierra un parDntesis _J entonces los números positivos se alinear(n correctamente con los números negativos @ue estDn entre parDntesis. 3e-etir caracteres

>ara repetir el car(cter siguiente en el formato para llenar el ancho de la columna" inclua un asterisco NP en el formato de número. >or e'emplo" introduzca B()((&.&& para incluir suficientes signos G despuDs del signo monetario para llenar una celda. El resultado ser?a >?????2,716.25

f. Eliminar un formato de n>mero personalizado 1.- En el menú ormato, !a"a clic en Celdas # !a"a clic en la fic!a Número. 2.- En la lista Cate"or%a, !a"a clic en )ersonalizada. $.- En la parte inferior del cuadro +ipo, !a"a clic en el formato personalizado que desee eliminar. (.- a"a clic en Eliminar.

&olamente pueden eliminarse los formatos personalizados. Microsoft Excel aplicar( el formato predeterminado N=eneralP a todas las celdas del libro a las @ue se haa dado formato mediante el formato personalizado @ue se ha eliminado.

=. 8i las fec?as introducidas en la ?oa de clculo no tienen el mismo aspecto

Cuando se introduce una fecha en una celda" dicha fecha aparece en un formato de fecha predeterminado o en un formato aplicado a la celda antes de introducir la fecha. El formato de fecha predeterminado se basa en la configuración de la ficha )echa del cuadro de di(logo Configuración regional" al @ue se tiene acceso desde el >anel de control de indoQs. &i estos valores de fecha han cambiado" cual@uier fecha existente en los libros no formateados con el comando )ormato de celdas tambiDn cambia.

2. 6sar Funciones aGanzadas para calcular Galores


>(g. $$

Manual de Excel Avanzado Conociendo a las funciones b(sicas de Excel" como son 5uma6 romedio6 Ma#6 Min6 Contar6 Contara6 Entero6 3edondear6 3a;A6 i6 5i6 K6 <6 Buscar(6 Buscar6 5ubtotales6 ?ndirectoL

as cuales se han visto a en el primer curso inicial de Excel" ahora veamos algunas otras funciones m(s avanzadas &umar.si" Contar.si" 3.esimo.maor" 3.esimo.menor" +edondear.mas" +edondear.menos" Aleatorio" Esblanco" Esnumero" Estexto" Eserror" BdMax" 8o" Ahora.

Funci
6onde 3an&o

Es el rango de celdas @ue se desea evaluar.

Criterio

Es el criterio en forma de número" expresión o texto" @ue determina @uD celdas se van a sumar. >or e'emplo" el argumento criterio puede expresarse como 2-K H2-HK H2-HK HmanzanasHK 64L

3an&o a sumar

&on las celdas @ue se van a sumar. as celdas contenidas en +ango a sumar se suman sólo si las celdas correspondientes del +ango coinciden con el Criterio. &i +ango a sumar se omite" se suman las celdas contenidas en el argumento +ango.

Ejercicio Nº 1: Cálculos condicionales en bienes inmuebles En una ho'a de c(lculo en las columnas A" B  C se tiene una relación de las valorizaciones de varios bienes inmuebles pertenecientes a tres personas. Al lado derecho se @uiere calcular a cuanto ascienden los bienes acumulados de cada propietario. A ; C 9alorizacion de Garias casas

1 2 3 Propietario 5ona 9alorizacireparado por 6aniel :egarra :avaleta 1& Sonia "orte S/. 0,000.00 11 TOTA7 8. %!#)&&&.&&

D

E F Totales por Propietario Propietario arlos 'or#e Sonia TOTA7

S/. S/. S/. 8.

9alorizaci
Total de casas inferiores a 100,000:

>(g. $-

8.

1"!)&&&.&&


>ara calcular la valorización acumulada de las casas de cada propietario haga usted lo siguiente 1.- En ( escribir la f&rmula/

56U,07.6I804901:*4:C49C1;

2.- 0ue"o copie esta f&rmula en * # . $.- En  use 3utosuma para sumar las tres celdas.

Si tambi4n se desea calcular la suma de las valorizaciones que son inferiores a 155,555 soles, entonces/ (.- 0a formula en 11 ser%a/

G50M,3L5?C4:C18H188888J Note que se !a omitido el 6an"o a sumar.

Como hemos podido ver" esta función puede seleccionar en un rango cuales valores se van a sumar" teniendo en cuenta una cierta condición. &in embargo" la función solo puede evaluar una sola condición de criterio 1odas las casas @ue sean de un determinado propietario G50M,3L5?,4:,18HOor&eHC4:C18J

oK todas las casas @ue se encuentren en una determinada zona G50M,3L5?B4:B18HNorteHC4:C18J 7a Funci
&intaxis C
Es el rango de celdas @ue se desea evaluar.

Criterio

Es el criterio en forma de número" expresión o texto" @ue determina @uD celdas se van a contar. >or e'emplo" el argumento criterio puede expresarse como 2-K H2-HK H2-HK HmanzanasHK64.

a función C/01A+&, cuenta el número de veces @ue aparece un valor en un rango de celdas" por e'emplo Contar el número de celdas en el rango B4B$% @ue contienen el texto H&urH.


>(g. $2

Manual de Excel Avanzado GC
>ero" cómo har?amos si se desea sumar todas las valorizaciones de casas ubicadas en una determinada zona  @ue adem(s pertenezcan a un determinado propietarioR >ara resolver c(lculos condicionales @ue re@uieren m(s de un criterio" entonces se puede hacer uso de la combinación de la función 50M,  la función 5?" o tambiDn la función C
Fórmulas Matriciales: a. C
En Ecel al"unas f&rmulas pueden escribirse como f&rmulas matriciales. )ara esto !a# que escribir la f&rmula # al finalizar !a# que presionar la combinaci&n de teclas Ctrl7S!ift7Enter, esto !ace que la formula se encierre entre llaves al momento de in"resar a la celda, quedando de esta forma/ <5%ormula=

rimer Caso -ara condiciones del ti-o KJ

Ahora veremos la siguiente fórmula matricial @ue permitir( calcular el valor total de las valorizaciones de las celdas C4C$%" donde el rango A4A$% contiene al propietario H&oniaH" 7 el rango B5B$% contiene la zona denominada H0orteH. PG50M,5?,4:,18G5oniaJQB4:B18GNorteJHC4:C18JJR

as dos condiciones est(n encerradas entre parDntesis  entre ellas se escribe el operador de multiplicación" lo cual indica @ue las dos condiciones deben cumplirse a la vez. 8Condici&n del tipo 9: 5e&undo Caso -ara condiciones del ti-o ara calcular el valor total de las valorizaciones de las celdas C4C$%" donde A4A$% contiene HCarlosH o HSorgeH" utilice la siguiente fórmula. PG50M,5?,4:,18 GCarlosJS,4:,18 GOor&eJHC4:C18JJR

En este caso las dos condiciones encerradas entre parDntesis se escribe con el operador de suma entre ellas" lo cual estar?a indicando @ue es suficiente @ue se cumpla una de las dos condiciones para @ue se realice el c(lculo. 8Condici&n del tipo ;: 0o olvide @ue ambas son fórmulas matriciales Nest(n encerradas entre llavesP  deben introducirse presionando al final C1+W&8,)1WE01E+.
Contar el número de veces @ue aparecen varias condiciones En la siguiente fórmula" cada vez @ue Excel encuentre H&oniaH en el rango A4A$%" comprobar( la presencia del texto H0orteH en la misma fila en la columna B Nel rango B4B$%P. A continuación" Excel calcular( de varios modos" el número de filas @ue contienen ambos textos. En los tres casos las formulas obtienen el mismo resultado. PGCreparado por 6aniel :egarra :avaleta

>(g. $4

Manual de Excel Avanzado o sino as? PG50M,5?,4:,18G5oniaJQB4:B18GNorteJH1H8JJR

o tambiDn as? PG50M,5?,4:,18G5onia H5?B4:B18GNorteH1H8JJJR En las primeras dos formulas el operador de multiplicación indica @ue las condiciones son del tipo K  en la tercera formula se est( utilizando dos funciones &, anidadas" o sea una función &, dentro de otra función &,. &e trata en estos casos de formulas matriciales  deben introducirse presionando al final C1+W&8,)1WE01E+.

7as Funci
&intaxis =LE5?M
es el rango de celdas @ue se desea analizar.

osición

es ubicación del número menor o maor @ue se desea encontrar. Esto es" para el menor de todos seria $" para el segundo menor ser?a -" etc..

&i se @uisiera averiguar cual es la valorización @ue ocupa el segundo lugar entre las maores"  cual es la tercera valorización mas pe@uea" las fórmulas ser?an G=LE5?M
1./6888L88

G=LE5?M
1146888L88

7as Funciones EDONDEA.HENO8) @ EDONDEA.HA8 +edondea un valor numDrico al inmediato inferior o al inmediato superior según la cantidad de decimales @ue se desee obtener.

&intaxis 3ED
Es la cantidad numDrica @ue se desea redondear.

osición decimal

Es la cantidad de decimales a los @ue se desea redondear la cantidad numDrica. &i en posición se escribe un número


>(g. $5

Manual de Excel Avanzado negativo se estar( redondeando no los d?gitos decimales sino los d?gitos enteros. &i en la celda E4 estuviera escrito el numero $2/'L2.$6 las funciones arro'ar?an los siguientes resultados G3ED
$2/'L2

G3ED
$2/'L$

G3ED
$2/'

G3ED
$2/.

G3ED
$2/8

G3ED
$2'8

G3ED
$888

G3ED
4888

7a Funci
&intaxis ,*E,"<3?<J

Esta función carece de argumentos. El siguiente e'emplo simula el lanzamiento de un dado E01E+/NAEA1/+,/NP7PW$ a función Aleatorio multiplicada por 7 generar( un numero entre %  5.9999. a función Entero truncar( la parte decimal de Dstos números @uedando as? enteros del % al 5"  al aumentarles $ Dstos finalmente serian números entre $  7. Escrita esta fórmula" bastara con presionar la tecla de función L)9 para recalcular la fórmula  entonces AEA1/+,/ generar( otro número al azar.

7as Funciones E8 En esta sección se describen 9 funciones para ho'as de c(lculo @ue se utilizan para comprobar el tipo de un valor o referencia.

Cada una de estas funciones" a las @ue se conoce como funciones E&" comprueba el tipo del argumento valor  devuelve E+6A6E+/ o )A&/ dependiendo del resultado. >or e'emplo" E&BA0C/ devuelve el valor lógico E+6A6E+/ si valor es una referencia a una celda vac?a" de lo contrario devuelve )A&/. &intaxis >reparado por 6aniel :egarra :avaleta

>(g. $7

Manual de Excel Avanzado E5B*,NC<(alorJ E5E33(alorJ E5E33<3(alorJ E5*<%?C<(alorJ E5N
Es el valor @ue se desea probar. >uede ser el valor de una celda vac?a" de error" lógico" de texto" numDrico" de referencia o un nombre de rango @ue haga referencia a alguno de los anteriores.

alor

Función

De(uel(e E3D,DE3< si

E&BA0C/

alor se refiere a una celda vac?a.

E&E++

alor se refiere a cual@uier valor de error con excepción de O0JA.

E&E++/+

alor se refiere a uno de los valores de error NO0JA" OA/+X" O+E)X" O6,J%X" O0*MX" O0/MB+ER o O0*/XP.

E&/=,C/

alor se refiere a un valor lógico.

E&0/6

alor se refiere al valor de error O0JA Nel valor no est( disponibleP.

E&0/1E<1/

alor se refiere a cual@uier elemento @ue no sea texto Nobserve @ue esta función devuelve E+6A6E+/ si el valor se refiere a una celda en blancoP.

E&0*ME+/

alor se refiere a un número.

E&+E)

alor se refiere a una referencia.

E&1E<1/

alor se refiere a texto.

Nota.-

0os ar"umentos valor de las funciones ES no se convierten. )or e'emplo, en la ma#or%a de las funciones en las que se requiere un número, el valor de teto <1=< se convierte en el número 1=. Sin embar"o, en la f&rmula ESN>?E6;8<1=<:, <1=< no se convierte # ESN>?E6; devuelve 30S;.


>(g. $!


Cuando usar las unciones E5

as funciones E& son útiles en fórmulas cuando se desea comprobar el resultado de un c(lculo. Al combinar esas funciones con la función &," proporcionan un mDtodo para localizar errores en fórmulas Nobserve los siguientes e'emplosP. Ejem-los

E&/=,C/NE+6A6E+/P es igual a E+6A6E+/ E&/=,C/NHE+6A6E+/HP es igual a )A&/ E&0*ME+/N4P es igual a E+6A6E+/ &upongamos @ue en una ho'a el rango A$A5 muestra los siguientes valores de texto" numDrico  de error H/roH" H+egión$H" O+E)X" U 22%.9-  O0JA respectivamente. 1 2 3  !

A SoB*a rimestre1 #C 330.2 #"/D

E&BA0C/NA$P es igual a )A&/ E&E++/+NA2P es igual a E+6A6E+/ E&0/6NA2P es igual a )A&/ E&0/6NA5P es igual a E+6A6E+/ E&E++NA5P es igual a )A&/ E&0*ME+/NA4P es igual a E+6A6E+/ Nsi se introdu'o U22%.9- como número  no como textoP E&+E)N+egión$P es igual a E+6A6E+/ Nsi +egión$ se define como un nombre de rangoP E&1E<1/NA-P es igual a E+6A6E+/ Nsi 1rimestre$ tiene formato de textoP &upongamos @ue en otra ho'a de c(lculo desea calcular el promedio del rango C$C4" pero no est( seguro de @ue las celdas contienen números. a fórmula >+/ME6,/NC$C4P devolver( el valor de error O6,J%X si C$C4 no contiene números. >uede utilizar la siguiente fórmula para localizar posibles errores  permitir la realización del c(lculo &,NE&E++/+N>+/ME6,/NC$C4PPKH0o ha 0úmerosHK>+/ME6,/NC$C4PP


>(g. $#

Manual de Excel Avanzado 7a Funci
&intaxis U
Esta función carece de argumentos. GU
dar?a como resultado 07/06/2004

7a Funci
&intaxis ,U<3,J

Esta función carece de argumentos. G,U<3,J dar?a como resultado

07/06/2004

0321

Funciones de Bases de Datos BDfunción En esta sección se describen las $- funciones para ho'as de c(lculo empleadas para los c(lculos de bases de datos No listasP de Microsoft Excel. Cada una de estas funciones" denominadas colectivamente funciones B6" usa tres argumentos base_de_datos" nombre_de_cam-o  criterios. Estos argumentos se refieren a los rangos de la ho'a de c(lculo empleados en la función para base de datos. &intaxis BDunciónBase de datosHCam-oHCriteriosJ Base_de_datos Nota.-

es el rango de celdas @ue compone la base de datos.

En ?icrosoft Ecel, una base de datos es una lista de datos relacionados en la que las filas de informaci&n son re"istros # las columnas de datos, campos. 0a primera fila de la lista contiene los r&tulos de cada columna. 0a referencia se puede introducir como ran"o de celdas o como nombre que representa el ran"o que contiene la lista. En todas las funciones para bases de datos, si la referencia de base de datos corresponde a una celda dentro de una tabla dinámica, el cálculo se realiza únicamente con los datos de esa tabla dinámica. Si desea calcular subtotales en la lista, use el comando Subtotales en el menú @atos para insertar los subtotales.

Cam-o

,ndica la columna en @ue se utiliza la función. as columnas de datos en la lista deben tener un rótulo identificativo en la primera fila. Campo puede ser una


>(g. $9

Manual de Excel Avanzado cadena de texto con el rótulo de columna encerrado entre dobles comillas" como por e'emplo HEdadH o H+endimientoH en el e'emplo siguiente" o como un número @ue representa la posición de la columna en la lista $ para la primera columna Nrbol en el e'emplo siguienteP" - para la segunda NAltoP  as? sucesivamente. Criterios

Es una referencia a un rango de celdas @ue especifican condiciones de una función. a función devuelve información de la lista @ue concuerda con las condiciones especificadas en el rango de criterios. Este rango inclue una copia del rótulo de la columna en la lista de la columna @ue desea @ue resuma la función. a referencia al argumento criterios puede introducirse como rango de celdas" como A9)$% en la tabla de base de datos @ue se muestra a continuación" o como nombre asignado a un rango" por e'emplo HCriterio.H

5u&erencias



Cual@uier rango se puede usar como argumento criterios" siempre @ue inclua por lo menos un nombre de campo  por lo menos una celda deba'o del nombre de campo para especificar un valor de comparación de criterios.

>or e'emplo" si el rango =$=- contiene el encabezado de campo ,ngresos en la celda =$  la cantidad $%.%%% en la celda =-" el rango podr?a definirse como CoincidirAn"resos  ese nombre podr?a usarse como argumento criterios en las funciones para bases de datos. 

Aun@ue el rango de criterios puede ubicarse en cual@uier parte de la ho'a de c(lculo" no colo@ue el rango de criterios deba'o de la lista. &i agrega m(s información a la lista utilizando el comando )ormulario en el menú 6atos" la nueva información se agrega a la primera fila deba'o de la lista. &i la fila de deba'o no est( vac?a" Microsoft Excel no podr( agregar la nueva información.



Asegúrese de @ue el rango de criterios no se superpone sobre la lista.



>ara realizar una operación en toda una columna de la base de datos" introduzca una l?nea en blanco deba'o de los nombres de campo en el rango de criterios


>(g. -%


Ejercicio Nº 2: Cálculos con funciones de base de datos BD En la siguiente ilustración se muestra una base de datos @ue contiene la descripción  comportamiento de varios tipos de (rboles. Cada registro contiene información acerca de un (rbol. El rango A5E$$ se denomina 8*E+1/  el rango A$)2 C+,1E+,/&. A 1 Ar'ol 2 Fan=ano 3 -eral  ! Ar'ol " Fan=ano # -eral $ ere=o % Fan=ano 1& -eral 11 Fan=ano

; Alto G10

C Edad

D .endimiento

E Manancia

Alto 1@ 12 13 14  @

Edad 20 12 14 15 @ 

.endimiento 14 10  10 @ 6

Manancia S/. 105.00 S/. 6.00 S/. 105.00 S/. 75.00 S/. 76.@0 S/. 45.00

F Alto H16

B6C/01A+N8*E+1/KHEdadHKA$)-P es igual a $. Esta función examina los registros de manzanos cuo alto var?a entre $%  $7 metros  determina cu(ntos campos Edad de esos registros contienen números. B6C/01A+AN8*E+1/KH=ananciaHKA$)-P es igual a $. Esta función examina los registros de manzanos cuo alto var?a entre $%  $7 metros"  determina el número de campos =anancia de esos registros @ue no est(n en blanco. B6MA+/6*C1/N8*E+1/KH+endimientoHKA$)-P es igual a $%" el producto del rendimiento de los manzanos con un alto entre $%  $7. B6>+/ME6,/N8*E+1/KH+endimientoHKA$B-P es igual a $-" el rendimiento promedio de manzanos con un alto de m(s de $% metros. B6>+/ME6,/N8*E+1/K2K8*E+1/P es igual a $2" la edad media de todos los (rboles en la base de datos. >reparado por 6aniel :egarra :avaleta

>(g. -$

Manual de Excel Avanzado B66E&E&1N8*E+1/KH+endimientoHKA$A2P es igual a -.9!K la desviación est(ndar estimada en el rendimiento de manzanos  perales si los datos de la base de datos son únicamente una muestra de la población total del huerto. B66E&E&1>N8*E+1/KH+endimientoHKA$A2P es igual a -.75K la desviación est(ndar verdadera en el rendimiento de manzanos  perales si los datos de la base de datos representan el con'unto de la población. B6A+N8*E+1/KH+endimientoHKA$A2P es igual a #.#K la varianza estimada en el rendimiento de manzanos  perales si los datos de la base de datos sólo representan una muestra de la población total del huerto. B6A+>N8*E+1/KH+endimientoHKA$A2P es igual a !.%4K la varianza real en el rendimiento de manzanos  perales si los datos de la base de datos representan el con'unto de la población del huerto. B6E<1+AE+N8*E+1/KH+endimientoGKC+,1E+,/&P devuelve el valor de error O0*MX por@ue m(s de un registro cumple con los criterios.

Funciones de Teto 7a Funci
&intaxis ?VW0?E3D,te#toHnum_de_caracteresJ "e#to

Es la cadena de texto @ue contiene los caracteres @ue se desea extraer

num_de_caracteres

Especifica el numero de caracteres @ue se desea extraer. &i se omite extrae solo un caracter

E'emplo ,:;*,E+6ANFEl >eruanoGK!P

dar?a como resultado

El -eru

.

NEl espacio en blanco tambiDn es un car(cter @ue se toma en cuentaP 7a Funci
&intaxis DE3ECU,te#toHnum_de_caracteresJ "e#to


num_de_caracteres

Especifica el numero de caracteres @ue se desea extraer. &i se omite extrae solo un caracter

6E+EC8ANFos >eruanosGK#P >reparado por 6aniel :egarra :avaleta


-eruanos

.

>(g. --

&intaxis ET"3,Ete#toH-osición_inicialHnum_de_caracteresJ "e#to


-osición_inicial

Es la posición del primer car(cter @ue se desea extraer del argumento 1exto. l primer car(cter en 1exto es $ Especifica el numero de caracteres @ue se desea extraer.

num_de_caracteres

E'emplo E<1+AENFos >eruanosGK5"4P


-eru

.

7a Funci
&intaxis U,**,3te#to_buscadoHdentro_del_te#tolH-osición_inicialJ "e#to_buscado

Es la cadena de texto o caracter @ue se desea ubicar. >uede usar los comodines  o R. / usar tambiDn

Dentro_del_te#to

Es la cadena de texto dentro de la cual se desea ubicar el texto buscado

-osición_inicial

Es la posición del primer car(cter a partir de la cual se iniciar( la bús@ueda

E'emplo 8AA+NFaGKFas >eruanasGK$P


2.

8AA+NFaGKFas >eruanasGK5P E'emplo


.

&i en la celda C4 estuviese el texto Ias -eruanas

. entonces

E<1+AENC4K8AA+NF GKC4K$PW$K4P

dar?a como resultado -eru . En este e'emplo" 8AA+ se encarga de buscar la posición donde se encuentra el espacio en blanco" le suma $ a esta posición  a partir de all? la función E<1+AE devuelve los siguientes 4 caracteres


>(g. -2

&intaxis ENC
Es la cadena de texto o caracter @ue se desea ubicar. >uede usar los comodines  o R. / usar tambiDn

Dentro_del_te#to

Es la cadena de texto dentro de la cual se desea ubicar el texto buscado

-osición_inicial

Es la posición del primer car(cter a partir de la cual se iniciar( la bús@ueda

E'emplo &i en la celda A2 estuviese el texto Juien FDI anda mal aca;a entonces E0C/01+A+NFmalG"A2K$P

.


16.


7.

en cambio" 8AA+NFmalG"A2K$P

N+ecuerde @ue 8AA+ no diferencia maúsculas ni minúsculasP 7a Funci
&intaxis *,3%<te#toJ "e#to

Es la cadena de texto" o celda @ue contiene un texto

E'emplo A+=/NF:ara? 1oledoGP


12.

7as Funci
&intaxis M,K05Cte#toJ M?N05Cte#toJ N


>(g. -4

Manual de Excel Avanzado E'emplo MA*&CNFmonto totalGP

dar?a como resultado FA"A ADI .

M,0*&CNF6/& M,GP

dar?a como resultado dos mil 0/M>+/>,/NFbill gatesGP dar?a como resultado Kill ates

. .

7a Funci
&intaxis 505"?"0?3te#toHte#to_ori&inalHte#to_nue(oHn)mero_de_ocurrenciaJ "e#to


"e#to_ori&inal

Es la cadena de texto" @ue se desea reemplazar

"e#to_nue(o

Es la cadena de texto @ue reemplazar( al texto_original

N)mero_de_ocurrencia

Es el numero de aparición del texto_original dentro de toda la cadena de texto. &i se omite se reemplazar( el texto_original en todos los sitios donde aparezca

E'emplo &i en la celda A2 estuviese el texto Juien mal anda mal aca;a

.

entonces &*&1,1*,+NA2"GmalGKGbienGP dar?a como resultado Juien ;ien anda ;ien aca;a en cambio"

.

&*&1,1*,+NA2"GmalGKGpeorGK-P dar?a como resultado Juien mal anda peor aca;a . NEn este caso solo sustitue la segunda palabra encontradaP

7a Funci
&intaxis DEC?M,*n)meroHdecimalesHno_se-arar_millaresJ N)mero

Es el número @ue desea redondear  convertir en texto.

Decimales

Es el número de d?gitos a la derecha del separador decimal.


>(g. -5

Manual de Excel Avanzado No_se-arar_millares Es un valor lógico @ue" si es E+6A6E+/" impide @ue 6EC,MA inclua un separador de millares en el texto devuelto.
•

• •

•

os números en Microsoft Excel nunca pueden tener m(s de $5 d?gitos significativos" pero el argumento decimales puede tener hasta $-! d?gitos. &i decimales es negativo" el argumento número se redondea hacia la iz@uierda del separador decimal. &i omite el argumento decimales" se calcular( con - decimales. &i el argumento no_separar_millares es )A&/ o se omite" el texto devuelto incluir( el separador de millares. a principal diferencia entre dar formato a una celda @ue contiene un número con el comando Celdas del menú Formato  dar formato a un número directamente con la función 6EC,MA es @ue 6EC,MA convierte el resultado en texto. *n número @ue recibe formato con el comando Celdas sigue siendo un número.

Ejem-los 1 2 3

Fórmula

A 1234.51@ &1234.567 7244.325

3esultado

6EC,MANA$K-P

$"-24.5-

6EC,MANA$KV$P

$"-2%

6EC,MANA-KV$KE+6A6E+/P

V$-2%

6EC,MANA2P


!"-44.22

Descri-ción

+edondea A$ - d?gitos a la derecha del punto decimal +edondea A$ $ d?gito a la iz@uierda del punto decimal +edondea A- $ d?gito a la iz@uierda del separador decimal"  sin coma de millar +edondea A2 - d?gitos a la derecha del separador decimal

>(g. -7


Ejercicio Nº !: "eneración de códi#os con funciones de teto En el siguiente cuadro se muestra la relación de socios @ue se han inscrito en un club social"  se desea completar las columnas de códigos  nombres completos de cada socio.

ara &enerar los códi&os:

Cada código se compone de 9 caracteres @ue son extra?dos de los datos de cada socio" teniendo en cuenta ciertas condiciones. >or e'emplo para el primer socio inscrito su códigoo ser?a el siguiente

.+OVU8+0M 6os últimos d?gitos del ao de nacimiento

>rimeras letras de sus apellidos  de su nombre

&exo &egunda letra del lugar de nacimiento 6os d?gitos del mes de su inscripción

as formulas para lograr este código serian entonces >reparado por 6aniel :egarra :avaleta

>(g. -!

Manual de Excel Avanzado •

6os últimos d?gitos del ao de nacimiento GDE3ECU,,X<U4J62J

•

>rimeras letras de sus apellidos  de su nombre G ?VW0?E3D,C461JY?VW0?E3D,D461JY?VW0?E3D,E461J

•

6os d?gitos del mes de su inscripción G DE3ECU,8YME5B4J62J

•

&egunda letra del lugar de nacimiento G M,K05CET"3,E?46261JJ

•

&exo G %4

)inalmente entonces" la formula @ue habr?a @ue escribir en la celda A4 para generar el código ser?a GDE3ECU,,X<U4J62JY?VW0?E3D,C461JY?VW0?E3D,D461J Y?VW0?E3D,E461JYDE3ECU,8YME5B4J62JYM,K05CET "3,E?46261JJY%4

Como se puede observar" esta formula une todas las formulas anteriores utilizando entre ellas el operador de concatenación YL ara escribir los nombres com-letos de cada socio en una sola celda:

En la columna ) se tendr( @ue escribir una formula @ue una el nombres del socio  luego de una coma los dos apellidos" pero en maúsculas. >or e'emplo para el primer socio deber( decir Uu&o6 O,3,M?**< V,,*, En la celda )4 ha @ue escribir la siguiente fórmula GE4Y6 YM,K05CC4Y YD4J )inalmente" tanto la formula de los códigos como la de los nombres completos" deber( copiarse hacia aba'o para todos los dem(s socios.


>(g. -#


Ejercicio Nº $: Con%ertidor de N&meros en 'etras 8a ocasiones en @ue se desea @ue en ciertos documentos una cantidad numDrica sea expresada en letras. 1al es el caso de los montos de una factura" o de una boleta de ventas" o el monto de un che@ue. >ara estos casos el siguiente e'ercicio nos podr?a ser de utilidad 1.- En un 0ibro nuevo escribir los si"uientes datos tal como se muestra en la ima"en si"uiente/

2.- @efinir los si"uientes nombres de ran"o/

B$

:N

B/

: *E"3,5

C!:U1.

: ",B*,

$.- 0ue"o escribir las si"uientes f&rmulas en las celdas/ ,!:

E01E+/NA/+N6E+EC8AN6EC,MANE01E+/N0J$%%%P"%"E+6A6E+/P"7PPJ$%%%%%P

,18: E01E+/NA/+N6E+EC8AN6EC,MANE01E+/N0J$%%%P"%"E+6A6E+/P"5PPJ$%%%%P ,11: E01E+/NA/+N6E+EC8AN6EC,MANE01E+/N0J$%%%P"%"E+6A6E+/P"4PPJ$%%%P ,12: E01E+/NA/+N6E+EC8AN6EC,MANE01E+/N0J$%%%P"%"E+6A6E+/P"2PPJ$%%P ,1$: E01E+/NA/+N6E+EC8AN6EC,MANE01E+/N0J$%%%P"%"E+6A6E+/P"-PPJ$%P ,14: E01E+/NA/+N6E+EC8AN6EC,MANE01E+/N0J$%%P"%"E+6A6E+/P"-PPJ$%P ,1+: E01E+/NA/+N6E+EC8AN6EC,MANE01E+/N0P"%"E+6A6E+/P"2PPJ$%%P


>(g. -9

Manual de Excel Avanzado ,1/: E01E+/NA/+N6E+EC8AN6EC,MANE01E+/N0P"%"E+6A6E+/P"-PPJ$%P ,1': A/+N6E+EC8AN6EC,MANE01E+/N0P"%"E+6A6E+/P"$PP ,1.: A/+N6E+EC8AN6EC,MAN0"-"E+6A6E+/P"-PP B!:

&,NNA9$"A$%%"A$$%P"HC,E0H"B*&CA+NA9"1ABA"7PP

B18: &,NA$$%"B*&CA+NA$%"1ABA"4P"&,NA$%$"HH"B*&CA+NA$%"1ABA"5PPP B11: &,NA$%$"&,NA$$%"HH"B*&CA+NA$$"1ABA"2PP"&,NA$$$"H*0H"B*&CA+NA$$"1ABA"-PPP B12: &,NNA$-$"A$2%"A$4%P"HC,E0H"B*&CA+NA$-"1ABA"7PP B1$: &,NA$4%"B*&CA+NA$2"1ABA"4P"&,NA$2$"HH"B*&CA+NA$2"1ABA"5PPP B14: &,NA$2$"&,NA$4%"HH"B*&CA+NA$4"1ABA"2PP"&,NA$4$"H*0H"B*&CA+NA$4"1ABA"-PPP B1+: &,NNA$5$"A$7%"A$!%P"HC,E0H"B*&CA+NA$5"1ABA"7PP B1/: &,NA$!%"B*&CA+NA$7"1ABA"4P"&,NA$7$"HH"B*&CA+NA$7"1ABA"5PPP B1': &,NA$7$"&,NA$!%"HH"B*&CA+NA$!"1ABA"2PP"B*&CA+NA$!"1ABA"-PP B1.: &,NA$#`$%"H%H"HHPZ6EC,MANA$#"%"E+6A6E+/P B+:

&,N0$%%%%%%%%%"6EC,MANE01E+/N0J$%%%%%%%%%P"%"E+6A6E+/PZH"H"HHPZ &,N0$%%%%%%"6E+EC8AN6EC,MANE01E+/N0J$%%%%%%P"%"E+6A6E+/P"2PZH[H"HHPZ&,N0 $%%%"6E+EC8AN6EC,MANE01E+/N0J$%%%P"%"E+6A6E+/P"2P"HHPZH"HZ&,N0%"6E+EC8AN 6EC,MAN0"-"E+6A6E+/P"7P"HHP

B/:

E&>AC,/&NB9ZH HZB$%ZB$$Z&,N&*MANA9A$%P%"H M,/0E&" H"&,NA$$$"H M,/0E&" H "&,NA$$$"H M,/0" H"HHPPPZB$-ZH HZB$2ZB$4Z&,N&*MANA$-A$4P%"H M,"H"&,N&*MA NA9A$$P%"H H"HHPPPZB$5ZH HZB$7ZB$!ZH  HZ&,NA$#%"B$#"H%%HPZHJ$%% HZH0*E/& &/E&H

a ho'a de c(lculo se deber( ver as? entonces


>(g. 2%

Manual de Excel Avanzado (.- Buarde este libro con el nombre etras

Ahora" cada vez @ue escriba un nuevo número entero o con decimales en la celda B-" inmediatamente en la celda B7 aparecer( su e@uivalente en letras  redondeado a dos decimales. En la celda B5 aparecer( el mismo número original redondeado a - decimales  con la siguiente puntuación un punto para separar decimales" una coma para separar millares  una comilla simple para separar los millones. En otros e'ercicios más adelante veremos como se pueden utilizar estos resultados para confeccionar acturas, oletas de Denta # Biro de C!eques.

7a FunciAC,/& en texto procedente de otras aplicaciones @ue pueda contener una separación irregular.

&intaxis E5,C?<5"e#toJ "e#to

Es el texto del @ue desea @uitar espacios innecesarios.

GE5,C?<5Z ,rriba er) ZJ

El resultado ser(

,rriba er)


>(g. 2$


3. 9alidaci
>or e'emplo ,mpedir ingresar edades de personas @ue sean maores a $%% aos. ,mpedir @ue se ingresen votos en una mesa de sufragio maores a -%% electores ,mpedir @ue se escriban códigos de art?culos @ue no existen en la lista de precios '. C
(. a"a clic en el operador que desee en el cuadro @atos # especifique el limite inferior o superior para los datos, o bien ambos l%mites, dependiendo del operador que se seleccione. )ueden introducirse valores, referencias de celda o f&rmulas para los l%mites.


>(g. 2-

Manual de Excel Avanzado Si desea permitir que la celda que se está restrin"iendo est4 en blanco o si desea definir l%mites que utilicen una referencia de celda, o una f&rmula que dependa de celdas que inicialmente están en blanco, compruebe que la casilla de verificaci&n ;mitir blancos está activada. )ara !acer obli"atorias las restricciones definidas en las celdas en blanco, tratándolas como si tuvieran ceros, desactive la casilla de verificaci&n ;mitir blancos. *. )ara que aparezcan mensa'es que pidan entradas # que epliquen o impidan las entradas incorrectas, especifique los tipos de mensa'es que desee en las fic!as ?ensa'e entrante # ?ensa'e de error. >Cómo ?acerlo@

)ara omitir la presentaci&n de mensa'es, desactive las casillas de verificaci&n ?ostrar mensa'e al seleccionar la celda en la fic!a ?ensa'e entrante # ?ostrar mensa'e de error si se introducen datos no válidos en la fic!a ?ensa'e de error.

Ejem-lo:

&e desea validar un cuadro para @ue en la columna Cantidad solo permita escribir números enteros  positivos. Entonces" cuando se trate de escribir en una celda una cantidad como !.2 @ue es un numero decimal" deber( aparecer un mensa'e de error como el @ue se ve a la derecha" impidiendo ingresar este número. 1 2 3  ! " # $ %

A ; Personal Cantidad Empleados 13@ i#ilantes 11 Secretarias 24 erentes @ %cnicos 7.3 Super$isores endedores

>ara esto seleccionar el rango B-B#" llamar al menú 6atosJalidación"  elegir las opciones como se muestran en la siguiente ventana de di(logo


>(g. 22


Nota.-

Cuando se especifique el tipo de datos que se permiten, esto no afectará al formato. )ara dar formato a las celdas, como números, fec!as u !oras, !a"a clic en el menú ormatoCeldas #, a continuaci&n, !a"a clic en la fic!a Número. Seleccione el formato que desee en el cuadro Cate"or%a # lue"o seleccione las opciones que desee. >na f&rmula para un l%mite puede evaluar los datos únicamente en la !o'a de cálculo en que se !a#an confi"urado las restricciones. )ara utilizar en una f&rmula los datos de otra !o'a de cálculo, o de otros libros, introduzca una referencia de celda para los datos eternos en una celda de la !o'a de cálculo activa o defina un nombre para los datos eternos en la !o'a de cálculo activa. 0a f&rmula puede !acer referencia a la celda o al nombre en la misma !o'a de cálculo. )or e'emplo, si los datos que desea utilizar en una f&rmula están en la celda 3 de la primera !o'a de cálculo de un libro denominado )resupuesto.ls, puede definir el nombre AatosV$lidos en la !o'a de cálculo activa para que sea FG)resupuesto.lsHo'a1IJ3J #, posteriormente, introducir una referencia F@atosDálidos cuando se especifiquen los l%mites para los datos.

c. ealizar una auditoria en un ran=o Galidado >uede buscar entradas de celdas @ue no se a'usten a sus criterios  rodearlas autom(ticamente con un c?rculo haciendo clic en el botón 3odear con un c;rculo datos no (álidos de la barra de herramientas ,uditoria. >ara hacer visibles los botones de Auditoria usar el menú Uerramientas@,uditoria@Mostrar barra de auditoria. Ejem-lo1 1.- En una !o'a de cálculo escriba una lista de varios nombres de niKos # sus respectivas edades, tal como se muestra en la fi"ura si"uiente.


>(g. 24

Manual de Excel Avanzado ;bserve que las edades de 0aura # ?i"uel fueron escritas err&neamente. En este cuadro se desea verificar que las edades #a in"resadas de los niKos, no sean ma#ores de 12 aKos. )ara esto, !a"a usted lo si"uiente/

2.- Seleccionar el ran"o 2/L # con el menú AatosBValidación , ele"ir )ermitir Números enteros entre un m%nimo de 5, # un máimo de 12.

$.- 0ue"o de validar, en la barra de !erramientas 3uditoria !a"a un clic en el bot&n 7odear con un c!rculo datos no


>(g. 25

Manual de Excel Avanzado v$lidos , entonces incorrectas.

aparecerán marcadas con c%rculos ro'os las cantidades

(.- Corri'a entonces las edades de ambos niKos. 3 medida que corri'a estas cantidades, observe que irán desapareciendo los c%rculos ro'os que encerraban a las edades equivocadas.

Ejem-lo2

En otro casoK suponga @ue se tiene un registro de ventas con la relación de las facturas emitidas en un mes  se desea verificar @ue los números de factura no aparezcan repetidos" en cuo caso habr?a @ue analizar el motivo del error  corregirlo" a sea cambiando el numero de la factura por el correcto" o eliminando el registro de la factura por estar duplicada. 1.- En una !o'a en blanco escriba los datos del 6e"istro de Dentas que se muestra en la ima"en si"uiente/ A 1 2 3  ! " # $ % 1& 11 12 13 1 1! 1"

; C .EMI8T.O DE 9ENTA8

Fec?a 02&Act 02&Act 03&Act 03&Act 03&Act 03&Act 03&Act 04&Act 04&Act 04&Act 05&Act 06&Act

N Factura 001&0124 001&0126 001&0126 001&0127 001&012@ 001&012 001&012 001&0130 001&0131 001&0132 001&0132 001&0134

Honto S/. 1,306.00 S/. 510.00 S/. 1,364.00 S/. 432.00 S/. 35.00 S/. 3.00 S/. 3.00 S/. 220.00 S/. 1,1.00 S/. [email protected] S/. 257.00 S/. [email protected]

Esta Bactura de;er*a ser la 001&0125

Esta Bactura se repite + de;erL eliminarse

Ia Bactura de a;a9o de;er*a ser la 001&0133

2.- En este cuadro seleccionar todas las celdas con los números de factura de la columna , # con el menú AatosBValidación se valida se"ún como se muestra en la ventana de dialo"o a continuaci&n/ 5/4D/' 0a f&rmula verificará cuando dos celdas consecutivas se repitan.


>(g. 27


$.- 0ue"o de validar, !a"a clic en el bot&n de auditoria 7odear con un c!rculo los datos no v$lidos .

3 medida que se va#an corri"iendo los errores en los números de las facturas, al i"ual que en el e'emplo anterior, los c%rculos ro'os tambi4n se irán desapareciendo.

. 7Qneas de Tendencia en los Mrficos a. 6tilidad de las lQneas de tendencia as l?neas de tendencia se usan para mostrar gr(ficamente las tendencias de los datos  analizar los problemas de predicción. Este an(lisis tambiDn se denomina an(lisis de regresión. Mediante el uso del an(lisis de regresión" puede representarse una l?nea de tendencia en un gr(fico m(s all( de los datos actuales para predecir los valores futuros. >or e'emplo" en el siguiente gr(fico se utiliza una l?nea de tendencia simple @ue muestra la previsión para cuatro trimestres indicando una clara tendencia de aumento en los ingresos. Media mó(il.V 1ambiDn se puede crear una media móvil" @ue suaviza las fluctuaciones en los datos  muestra la trama o tendencia con m(s claridad.

'. Tipos de =rfico Rue admiten lQneas de tendencias >ueden agregarse l?neas de tendencia a las series de datos en los siguientes gr(ficos

 

de (reas -6 no apiladas" de barras"



de columnas"



de l?neas"



de cotizaciones"



de tipo < N6ispersiónP" 


>(g. 2!

Manual de Excel Avanzado 

de burbu'as.

0o pueden agregarse l?neas de tendencia a las series de datos en los gr(ficos 26" radiales" circulares" de superficie o de anillos. &i se cambia un gr(fico o una serie de datos de modo @ue a no permita la l?nea de tendencia asociada Npor e'emplo" si se cambia el tipo de gr(fico por un gr(fico de (reas 26 o si se cambia la vista de un informe de gr(fico din(mico o de un informe de tabla din(mica asociadoP" se perder(n las l?neas de tendencia.

c. A=re=ar una lQnea de tendencia a una serie de datos 1.- En el "ráfico, !a"a clic en la serie de datos en la que desea a"re"ar la l%nea de tendencia o la media m&vil. 2.- En el menú Bráfico, !a"a clic en la opci&n 3"re"ar l%nea de tendencia. $.- En la fic!a +ipo, !a"a clic en el tipo de l%nea de tendencia de re"resi&n o en la media m&vil que desee.


>(g. 2#

Manual de Excel Avanzado Si se selecciona )olinomial, introduzca en el cuadro ;rden el valor potencial más alto de la variable independiente. Si se selecciona ?edia m&vil, introduzca en el cuadro )er%odo el número de per%odos que va a utilizarse para calcular la media m&vil./

Ejem-lo

>ara analizar las proecciones en un gr(fico de l?neasK una vez creado el grafico sealar con el puntero del mouse la l?nea  llamar al menú contextual con un clic derecho del mouse" luego elegir la opción ,&re&ar l;nea de tendencia.

En la ventana de di(logo elegir en 1ipo de tendencia o regresión la opción ineal  en la ficha /pciones marcar la casilla >resentar ecuación en el gr(fico" luego LAceptar. &e ver( entonces en el grafico el siguiente resultado


>(g. 29

Manual de Excel Avanzado N&i se reemplaza FxG en la ecuación por el numero del mes se obtienen los pronósticos de las ventas de esos meses.P &i luego de tener la l?nea de tendencia en el grafico" se ingresan los valores de venta de los meses faltantes" la l?nea de tendencia as? como la ecuación se a'ustaran en el gr(fico actualiz(ndose a estos nuevos valores. >osteriormente se puede agregar formato a los elementos del grafico" as? como a la l?nea de tendencia  a la ecuación en el grafico" tal como se ve en la figura. /tras formas en las @ue se puede mostrar el mismo an(lisis de ventas con la l?nea de tendencia se muestran a continuación en los gr(ficos siguientes En un gr(fico de reas

En un gr(fico de Barras

1enga en cuenta lo siguiente &i se agrega una media móvil a un gr(fico < N6ispersiónP" la media móvil se basar( en el orden de los valores < trazados en el gr(fico. >ara obtener el resultado deseado" puede ser necesario ordenar los valores < antes de agregar una media móvil. >reparado por 6aniel :egarra :avaleta

>(g. 4%

Manual de Excel Avanzado as l?neas de tendencia no se mantienen en los informes de gr(fico din(mico si se modifica la vista del gr(fico o de su informe de tabla din(mica asociado. Antes de agregar l?neas de tendencia u otros elementos de formato a un informe de gr(fico din(mico" asegúrese de @ue el diseo es satisfactorio.

!. 6so del 'otara habilitar este botón" ingrese al menú erJBarra de herramientasJ>ersonalizar  en la ficha Comandos" categor?a 8erramientas ubi@ue el botón C(mara  arr(strelo a las barras de herramientas de la ventana de Excel o ub?@uelo en la barra de menú. '. Cbicar el puntero en el lu"ar donde desee revelar la foto # !a"a nuevamente un clic.

Al aparecer la foto esta se comporta como cual@uier imagen"  entonces podr( cambiar su tamao  posición a voluntad. / sea @ue una foto podr( ubicarse sobre un cuadro de Excel" o sobre un gr(fico o formando parte de un dibu'o" incluso podr( agruparse con gr(ficos o dibu'os como cual@uier imagen sin perder su calidad de foto. Nota.-

Si el contenido del ran"o al que se le !a tomado una foto es modificado, entonces la foto tambi4n cambiará su ima"en, actualizándose automáticamente a esos cambios.

Ejem-lo

Esta es una oto


>(g. 4$

Manual de Excel Avanzado En este e'emplo se ha tomado una foto a las celdas @ue contienen el tipo de cambio del dólar  se la ha pegado encima del cuadro de existencias. a foto mantendr( actualizado el valor del dólar cada vez @ue este sea cambiado.

". 7a Pro=ramaciro @ue fueron dos ho'as de calculo mu populares en 6/&" as? como en las versiones iniciales de Excel" la programación de las Fmacro instruccionesG se escrib?an usando una serie de códigos o palabras reservadas @ue eran propias únicamente de Dstas aplicaciones. >ero" a partir de la versión 5.% de Excel" Microsoft recurre al lengua'e de isual Basic para escribir sus programas en ho'as de módulo @ue se anexan en forma transparente a los libros de Excel" recurriendo para ello al Editor de isual Basic para Aplicaciones a. 6so de la =ra'adora de macros A pesar @ue la programación se realiza internamente utilizando el lengua'e de isual Basic" no es necesario @ue los usuarios de Excel sepan necesariamente el uso de este lengua'e.  esto es as?" gracias a @ue existe la posibilidad de @ue se puedan grabar las acciones @ue necesitamos utilizando para ello una grabadora de macros" la cual almacena en memoria todas las acciones a grabar" e internamente convierte a estas en un programa en isual Basic.

>ara crear una macro haciendo uso de la grabadora de macros se siguen los siguientes pasos 1.- 0lamar al menú erramientas ?acroBrabar nueva macro 2.- Escribir un nombre para la macro a "rabar. Este nombre no deberá contener espacios en blanco. $.- 3l aparecer el mensa'e MBrabando en la barra de estado, realizar cuidadosamente # en forma secuencial, todas las acciones que se desean "rabar.


>(g. 4-

Manual de Excel Avanzado (.- )ara finalizar la "rabaci&n !acer un clic en el bot&n/

, o llamar al menú

erramientas?acroinalizar "rabaci&n, con lo cual deberá desaparecer el mensa'e MBrabando en la barra de estado. Nota.-

0as macros al ser creadas pueden "rabarse en el libro actual, como se puede apreciar en la ventana de dialo"o anterior en la opci&n/ *ste li(ro , con lo cual la macro solo funcionará en ese libroO o tambi4n se pudo ele"ir en la ventana de diálo"o la opci&n/ i(ro de macros personal , para cu#o caso la macro podr%a funcionar tambi4n en todos los libros de EcelO sin embar"o, !a# que tener cuidado con esto, pues si se "raba en el libro de macros personal esto !ará que dic!a macro # todas las que se encuentren all% sean car"adas en memoria 63? cada vez que se in"rese a Ecel, restando con ello memoria al computador.

*na vez creada" veamos a continuación como se hace para @ue la macro funcione '. Eecuci
&in embargo" un modo mas controlado  f(cil de e'ecutar una macro es asign(ndola a un elemento de imagen" como puede ser a un dibu'o de Autoformas" a un botón de herramientas personalizado o incluso hasta a un gr(fico. >ara asignar una macro a un botón de Autoformas realizar lo siguiente

Clic derecho VVVVVVVVVVVVVVVV VVVVVVVVVVVVVVVV VVVVVVVVVVVVVVVV ,si&nar macro... VVVVVVVVVVVVVVVV VVVVVVVVVVVVVVVV

1.- SeKalar con el mouse el borde del bot&n # !acer un clic derec!o para llamar al menú contetual 2.- En el menú ele"ir la opci&n 3si"nar macro. $.- Seleccionar el nombre de la macro de la lista # lue"o clic en 3ceptar.

Ejem-lo

&e va ha crear una macro @ue alinee verticalmente al centro" el contenido de una celda


>(g. 42

Manual de Excel Avanzado 1.- )ara esto primero escribir en una celda un dato. 1.- 0lame al menú erramientas?acroBrabar nueva macro # escriba como nombre de la macro/ CentradoDertical 8sin de'ar espacio en blanco entre las 2 palabras:, # lue"o 3ceptar. 2.- 3l aparecer el mensa'e MBrabando en la barra de estado, realizar la acci&n de centrado, esto es/ llamar al menú ormatoCeldas # en la fic!a 3lineaci&n ele"ir en la opci&n 3lineaci&n del teto Dertical, la alternativa Centrar, lue"o ele"ir el bot&n 3ceptar. $.- inalmente terminar la macro con el bot&n Aetener gra(ación o con el menú EerramientasB ,acroBAetener gra(ación . (.- 3 continuaci&n dibu'e en la !o'a un bot&n con 3utoformas # !aciendo un clic derec!o en 4l, as%"nele la macro CentradoVertical. *.- 3!ora, al escribir en una celda un dato podrá centrar el dato verticalmente !aciendo un clic en el bot&n de la macro.

c. 6so del Editor de 9isual ;asic >ara la macro del e'emplo anterior" el programa o subrutina en el lengua'e de isual Basic @ue se habr?a generado autom(ticamente" ser?a el siguiente Sub CentradoVertical() ' ' Macro creada por Daniel Zegarra - UNI ' With Selection .ori!ontal"lign#ent $ %l&eneral .VerticalAlignment = xlCenter .Wrape%t $ ale .*rientation $ + ."ddIndent $ ale .Shrin,oit $ ale .MergeCell $ ale nd With


>(g. 44

Manual de Excel Avanzado nd Sub

Este programa se encontrar?a escrito en una ho'a de Modulo insertada dentro del libro actual de Excel"  para ver esta ho'a de módulo habr?a @ue ingresar a la ventana del Editor de isual Basic. d. Para in=resar al Editor de 9isual ;asic &e puede hacer de dos formas

i. ii.

>resionando la combinación de teclas ,ltSF11" o lamando al menú 8erramientasJMacroJEditor de isual Basic.

&i al ingresar al Editor de isual Basic la ventana estuviese totalmente vac?a" llamar al menú er  elegir la opción E#-lorador de ro7ectos" o presionar CtrlS3 . &e ver( entonces la siguiente ventana en la pantalla

En la ventana de >roecto de la iz@uierda" haga doble clic en el elemento Modulo1 para abrir la ventana conteniendo el código en isual Basic de la macro. e. Hodificar una macro en el Editor de 9isual ;asic El Editor se comporta en forma seme'ante a un procesador de texto" en el se pueden escribir  modificar las instrucciones creadas por la grabadora de macros" as? como escribir a mano nuevas subrutinas en la misma ho'a de Modulo1 o insertando nuevas ho'as al proecto  escribiendo las macros en ellas.


>(g. 45

Manual Excel Avanzado

Recommend Documents