Manual Excel Nivel Avanzado

Manual de Excel Excel NIVEL AVANZADO

CAPITULO 1

La Nueva Interfaz de Excel 2010 El objetivo de este capítulo es mostrar la visión y el propósito de Microsoft para crear una nueva versión que ofrezca a los usuarios más ventajas y sobre todo la agilidad y facilidad en el manejo de las aplicaciones de Office 200! "unque E#cel 200$ introdujo algunos cambios radicales vs! su versión anterior! E#cel 200 ofrece mejoras con tan sólo unos pocos cambios redicales! "sí los usuarios de E#cel 200$ tendrán una fácil transición a E#cel 200!

1. NOVEDA NOVEDADES DES DE EXCE EXCEL L 2010 2010



Gráfico de dato en celda!

%on la nueva característica de minigráficos de E#cel 200& se pueden crear peque'os gráficos en las mismas celdas!

Pág 1

Manual de Excel Excel NIVEL AVANZADO 

"e#ore re$orte!

E#cel 200 cuenta con nuevas y fascinantes opciones de desglose de datos! (osee visualización mejorada de la vista de tabla dinámica para poder segmentar y filtrar datos de forma dinámica y mostrar sólo la información necesaria! )implifica y mejor muc*o el

análisis de grandes conjuntos de datos en filtros y tablas dinámicas! 





%O&A' Excel dede la (e) o *art$+one!

%on +E +E#c #ceel ,eb "pp- pod odrá rá ampliar su e#periencia de Office a la ,eb! (e (erm rmititee pu publ blic icar ar&& ve verr y editar sus *ojas de cálculo en la .eb/ !%on +Microsoft E#cel Mobi Mo bile le 20 200 0-- titien enee la ve vers rsió iónn móvil de E#cel que est adaptada específicamente para su smartp*one! Nuevo icono $ara Pe,ado e$ecial en *en- de$le,a)le de cada celda!

1na nueva característica es que guarda de modo automático una copia de los documentos no guardados por un má#imo de  días! Esto para el proceso de "utorrecuperación! )i inadvertidamente se cierra sin guardar un documento en el que *as estado trabajando durante 0 minutos o más& puedes facilmente recuperar tu trabajo! 

Un to.ue de ofiticaci/n a u $reentacione de dato!

Pág 2

Manual de Excel Excel NIVEL AVANZADO El formato condicional en E#cel 200 ofrece más control sobre los iconos y estilos& las barras de datos mejoradas& así como resaltar aspectos aspectos específicos específicos con tan solo unos unos clics! 

Nuevo  *á interactivo ,ráfico diná*ico!

3ecopile rápidamente más información de sus gráficos dinámicos! Muestre diferentes vistas de datos directamente en un gráfico dinámico& independiente independiente de las vistas de gráficos dinámicos& para analizar y capturar la vista más atractiva para sus n4meros!



ealice tarea de for*a *á rá$ida  encilla!

E#cel 200 simplifica el modo de acceso a las características! 5a nueva vista Microsoft Office 6ac7stage8 reemplaza al men4 "rc*ivo tradicional para permitirle guardar& compartir& imprimir y publicar sus *ojas de cálculo en tan solo unos pasos!

Pág 3

Manual de Excel NIVEL AVANZADO 

"odele  analice $ráctica*ente cual.uier dato de *anera eficiente!

El complemento PowerPivot para Excel 2010 proporciona una tecnología innovadora como la integración simplificada de datos desde múltiples orígenes y la manipulación inmediata de grandes conjuntos de datos con hasta millones de filas !os usuarios profesionales pueden pu"licar y compartir an#lisis pr#cticamente sin esfuer$o mediante %icrosoft &harePoint &erver 2010



Ca$acidad au*entada $ara crear +o#a de cálculo *á ,rande  co*$le#a!

'uenas noticias para los usuarios avan$ados y analistas( con la nueva versión de Excel 2010 de )* "its+ el an#lisis de grandes cantidades de información es m#s f#cil ,ue nunca -nalice grandes y complejos conjuntos de datos+ m#s grandes ,ue los archivos de 2 .' de las versiones anteriores de Excel 

Pu)li.ue  co*$arta a trav de lo 'ervicio de Excel!

!a integración de &harePoint &erver 2010 y los &ervicios de Excel permite a los usuarios avan$ados compartir an#lisis y resultados con toda la organi$ación mediante la pu"licación de hojas de c#lculo en la /e" ree un panel de inteligencia empresarial y comparta información confidencial de la empresa con sus colegas+ clientes y socios de negocio en un entorno con seguridad mejorada

2! La Cinta de O$cione

Pág 4

Manual de Excel NIVEL AVANZADO La cinta de o$cione es uno de los elementos más importantes de Excel, ya que contiene todas las o$cione del $ro,ra*a or,anizada en $eta3a . Al pulsar sobre una pestaña, accedemos a la fic+a. Las fic+a $rinci$ale son Inicio, Inertar , 4ie3o de $á,ina, 5/r*ula , 4ato, eviar y

6ita. En ellas se encuentran los distintos botones con las opciones disponibles. Pero además, cuando trabajamos con determinados elementos, aparecen otras de forma puntual: la fic+a de +erra*ienta. Por ejemplo, mientras tengamos seleccionado un gráfico, dispondremos de la fica %erra*ienta de ,ráfico, que nos ofrecerá botones especiali!ados para reali!ar modificaciones en los gráficos. Es posible que en la "ersi#n que tengas instalada en tu equipo de Excel $%&% "isualices otra

fic+a con más opciones. 'sto sucede porque los programas que tenemos instalados en el ordenador son capaces de interactuar con Excel, si están programados para ello, añadiendo erramientas y funcionalidades. (upongamos que tienes instalada la "ersi#n profesional de Acrobat, para crear documentos P)*. Es muy probable que en tu programa Excel apare!ca una fica llamada Acro)at que incluya erramientas +tiles como crear un P)* a partir de la oja de cálculo o exportar como P)* y en"iar por ema

Fichas! -ay siete ficas básicas en la parte superior. ada una representa un área de acti"idad. /nicio, /nsertar, )iseño de página, *#rmulas, )atos, 0e"isar y 1ista.

Gru$o!

ada fica contiene "arios grupos que contienen elementos relacionados. Por ejemplo la *ica /nicio presenta 2 grupos: Portapapeles, *uente, Alineaci#n, 3umero, Estilos, eldas y 4odificar.

Pág 5

Manual de Excel NIVEL AVANZADO Co*ando!

5n comando puede ser un bot#n, un cuadro en el que se escribe informaci#n, o un men+!

a! Lo Co*ando del Gru$o 9odo lo que *ay en una fic*a *a sido cuidadosamente seleccionado en función de las actividades del usuario! (or ejemplo& la fic*a :nicio contiene todo lo que se utiliza con mayor frecuencia& como los comandos del grupo ;uente para cambiar la fuente del contenido de celda< ;uente& 9ama'o de fuente& =egrita& %ursiva& etc! 6rupo *uente

"lgunos grupos contienes otros nuevos elementos llamados galerías como por ejemplo la galería que aparece al *acer clic en el comando Estilos de celda!

"l *acer clic en el comando Estilos de celda& aparece la siguiente galería<

>alería

)! Cuadro de 4iálo,o en lo Gru$o " primera vista& es posible que no encuentre un comando determinado de una versión anterior de E#cel! 9ranquilícese! "lgunos grupos tienen una peque'a flec*a diagonal en la esquina inferior derec*a !

Pág 6

Manual de Excel NIVEL AVANZADO ?aga clic en el iniciador de cuadros de diálogo ese grupo en concreto!

para ver más opciones de

%lic aquí

"parecerá entonces la ventana de dialogo Formato/Celdas/Fuente de la anterior version de E#cel& tal como se aprecia en la siguiente figura<

c! La %erra*ienta Contextual "l seleccionar un dibujo& aparece la fic*a Herramientas de dibujo& en la que se muestran grupos de comandos para trabajar con dibujos!

Pág 

Manual de Excel NIVEL AVANZADO 3

2

1

En esta nueva versión de E#cel& algunas fic*as sólo aparecen cuando las necesita! (or ejemplo& supongamos que acaba de insertar un dibujo pero a*ora desea *acer más cosas con l! Es posible que desee cambiar el color del borde que la rodea& o quizá quiera recortarlo! )eleccione el dibujo! )e muestra la fic*a Herramientas de dibujo! ?aga clic en ella o en la fic*a Formato!

"parecen grupos y comandos adicionales para trabajar con dibujos& como el grupo Estilos de "orma! "l *acer clic fuera del dibujo& la fic*a Herramientas de dibujo desaparece y se muestran los otros grupos! Nota: Según se van necesitando, aparecen fichas correspondientes a otras áreas de actividad, como tablas, dibujos, diagramas y gráficos.

d! Ocultar la cinta ?aga doble clic en la ;ic*a activa& o presione C#$% & F1& para ocultar los grupos de la cinta de opciones& a fin de tener más espacio en la pantalla!

Pág '

Manual de Excel NIVEL AVANZADO

(ara volver a ver la %inta de Opciones vuelva a *acer doble clic en una ;ic*a o presione la combinación de teclas %935 @ ;! (tro caso en )ue se oculta la Cinta*

)i se cambia el tama'o de la ventana de E#cel y se la *ace más peque'a& entonces de forma automática se ocultarán íntegramente todas las ;ic*as! 5a %inta de opciones centraliza y facilita satisfactoriamente la b4squeda de todos los elementos de E#cel 200! ?ay ocasiones& sin embargo& en que no necesita buscar nada! )ólo desea trabajar en el documento y le gustaría tener más espacio para *acerlo! (or ello& la cinta de opciones resulta tan fácil de ocultarla temporalmente& como de mostrarla en el momento que así lo desee!

e! Galer7a 5as galerías simplifican muc*as operaciones al presentar un conjunto de elementos que los usuarios pueden simplemente Aelegir y *acer clicA para conseguir los resultados deseados de una manera más profesional! E#isten galerías para aplicar formatos a gráficos& para seleccionar el dise'o de una tabla o rango de celdas& o para cambiar la apariencia de un ,ord"rt . E#isten 2 tipos de galerías<  

+ncrustadas ,es-legables

(or ejemplo& *aga clic en la fic*a +nsertar& y luego *aga clic en el Formas! comando "parecerá entonces una galería del tipo des-legable como se muestra en la figura de la derec*a!

>alería Besplegable

)i elije una de estas figuras y la dibuja en la *oja de calculo& observe lo que ocurre cuando la figura que acaba de dibujar se encuentra seleccionada< )e activará automáticamente la *erramienta conte#tual de dibujo y aparecerá una nueva fic*a Formato cuya cinta de opciones contiene varias galerías del tipo :ncrustadas!

Pág .


%uadro ás

(ara desplegar todas las opciones de la galería incrustada& *aga clic en el cuadro ás! El resultado será que la galería incrustada se desplegará convirtindose en una galería desplegable!

8! La "ini)arra de %erra*ienta "lgunos comandos de formato son tan 4tiles que desearía tenerlos disponibles siempre& independientemente de lo que est *aciendo! )upongamos que desea dar formato a una celda rápidamente& pero está trabajando en la fic*a Bise'o de página! (odría *acer clic en la fic*a :nicio para ver las opciones de formato& pero *ay un mtodo más rápido< )eleccione una celda o un rango de celdas y *aga un clic derec*o! 5a minibarra de *erramienta aparecerá juntamente con el men4 conte#tual!

5a minibarra de *erramientas es estupenda para opciones de formato! (ero si desea que otros tipos de comandos tambin estn siempre disponibles en todo momento& entonces use la barra de *erramientas de acceso rápido

Pág 10


9! La :arra de Acceo á$ido Cmo agregar un comando a la barra de acceso rá-ido!

5a barra de *erramientas de acceso rápido es el área peque'a que se encuentra en la parte superior izquierda de la cinta de opciones! %uando recin se instala E#cel& la barra de acceso rápido solo mostrará unas pocas *erramientas< >uardar& Bes*acer y 3e*acer! En esta barra de acceso rápido puede agregar sus comandos favoritos para que estn disponibles& independientemente de la fic*a en la que se encuentre! (or ejemplo puede agregar el botón de "utosuma& como se muestra en la siguiente figura< 3

En E#cel 200$ tendremos el siguiente procedimiento

1 2

Procedimiento abreiado*

1 )eleccione el botón de comando y pulse el botón derec*o del mouse 2 ?aga clic en la opción "gregar a la barra de *erramientas de acceso rápido 3 El botón de comando se a'adirá a la barra de acceso rápido En forma similar& si desea eliminar un botón de la barra de acceso rápido& bastará con pulsar el botón derec*o y elegir la opción Eliminar de la barra!

Pág 11


)uponga que usted quisiera agregar varios botones de *erramientas como los que se muestran en la siguiente figura& ya que stos serían los botónes que más a menudo va *a utilizar en la *oja de calculo<

(ara esta ocasión no vamos a utilizar el procedimiento abreviado& sino el procedimiento general& que además de permitirnos agregar numerosos botones& tambin los podremos ordenar del modo en que los deseamos tener en la barra! Procedimiento general*

1 ?aga clic en el cuadro ás

situado a la derec*a de la barra de acceso rápido& y en el men4 desplegable elija la opción as comandos! %lic aquí

2 "parecerá una ventana de diálogo donde podrá seleccionar y agregar diferentes botones de *erramientas y luego acomodarlos en el orden que lo desee!

Pág 12


3 ;inalmente *acer clic en el botón "ceptar! ;! Para Utilizar el Teclado (ara aquellas personas que en vez del mouse les agrada utilizar los mtodos abreviados del teclado para llamar a las opciones de menu o botones de *erramientas& entonces pueden recurrir al siguiente procedimiento que nos brinda esta nueva versión de Microsoft Office 200< (resione "59 para que se muestren los identificadores de las sugerencias de teclas abreviadas correspondientes a las fic*as de la cinta de opciones& el botón de Microsoft Office y la barra de *erramientas de acceso rápido!

Pág 13


5os mtodos abreviados que comienzan con la tecla %935 Cpor ejemplo& %935@% para copiar& o %935@= para poner negritaD siguen siendo los mismos que en las versiones anteriores de E#cel! =o obstante& el dise'o de la cinta de opciones incluye nuevos mtodos abreviados! (or quF (orque este cambio presenta dos grandes ventajas frente a las versiones anteriores< Mtodos abreviados para cada botón de la cinta de opciones! Mtodos abreviados que requieren menor combinación de teclas!  

ota*

9ambin puede utilizar los antiguos mtodos abreviados de "59@ con los que se tenía acceso a men4s y comandos en las versiones anteriores de E#cel& pero como los antiguos men4s no están disponibles& ya no aparecen en la pantalla las teclas que se deben presionar& por lo que se debe recordar todos los mtodos abreviados antiguos para poder utilizarlos!

(ugerencias: 

=oo*

"rrastre el control deslizante *acia la derec*a para ampliar la imagen& o *acia la izquierda para reducirla! "rrastre este control para ampliar o reducir la imagen

%lic aquí para abrir la ventana de diálogo Goom Pág 14




"l *acer clic en el n4mero de porcentaje que está a la izquierda del control deslizante& se abre el cuadro de diálogo Goom& donde puede especificar el porcentaje de zoom que desea! >ire esta rueda presionando %trl





)i el mouse CratónD tiene una rueda& puede mantener presionada la tecla %935 y girar la rueda *acia adelante para acercarse& o *acia atrás para alejarse! 9ambin puede encontrar los comandos Goom en la fic*a Her!

>! Lo )otone de 6ita " lado derec*o de la barra de estado se encuentran los botones de vista de E#cel! Estos permiten cambiar el modo en que se observará la *oja de calculo<

:otone de 6ita

5os botones de vista en E#cel 200 son tres< Hista =ormal • Hista Bise'o de página 

1ista pre"ia de salto de página

• Hista (revia de salto de página

)iseño de página 3ormal

a! La vita Nor*al 4uestra la oja de cálculo de la manera tradicionalmente conocida.

Pág 15


)! La vita 4ie3o de Pá,ina Es otra de las novedades de esta nueva version de E#cel 200! 5a *oja de calculo se muestra dividida en páginas con los encabezados y pies de pagina& tal y como se van a imprimir& pudiendose ver incluso los bordes del papel! Es como tener una vista preliminar permanente en la pantalla mientras se trabaja con la *oja escribiendo datos en las celdas! :ncluso se aprecia una regla vertical y otra *orizontal& tal como lo *ace ,ord en sus documentos!

Pág 16


c! La vita Previa de 'alto de Pá,ina Es la misma vista e#istente en la version anterior de E#cel! 1nas líneas punteadas que permiten ser arrastradas& indican el salto entre una y otra página!

1. La Hoja de Cálculo

a! El a*)iente de tra)a#o en Excel )on *ojas de cálculo que *acen fácil la creación y manipulación de tablas de datos! Esta "plicación incluye muc*as *erramientas de cálculo& así como diferentes tipos de formato& representación gráfica de los resultados& análisis de datos y automatización de tareas mediante la programación en Hisual 6asic!

Pág 1

Manual de Excel NIVEL AVANZADO 7ot#n de 4icrosoft 8ffice

uadro de 3ombres

inta de 8pciones de la fica /nicio

*icas

7arra de Acceso rápido

7arra de f#rmulas

Encabe!ados de olumna

Puntero de celda

7arra de )espla!amiento "ertical

Encabe!ados de *ila

7arra de )espla!amiento ori!ontal 7arra de Estado

Etiquetas de oja 7ot#n 3ue"a oja

7otones de 1istas

7arra de 9oom

)! Ta*a3o de la %o#a de Cálculo %olumnas< desde la  *asta la F, CI&JK columnasD ;ilas< L0K&$I filas ?ojas en un 5ibro< :nicialmente tiene J *ojas! ?asta un má#imo de 2 *ojas! %ada 5ibro en E#cel es un arc*ivo en el que se pueden *aber utilizado una o varias *ojas! 5os nombres de arc*ivo estándar de E#cel tienen la e#tensión< ls! c! El Puntero de Celda :ndica la celda activa donde se puede ingresar o modificar un dato uadro de 0elleno o de Autollenado

Pág 1'

Manual de Excel NIVEL AVANZADO d! El Puntero del "oue Bependiendo donde se ubique el puntero del mouse& este cambiará de forma para realizar diferentes acciones en la *oja de cálculo<

En el interior de la celda! (ara seleccionar celdas En el borde del puntero de celda! (ara copiar o mover celdas En el cuadro de relleno! (ara copiar en celdas consecutivas& o para rellenar series de datos e! In,reo de dato (ara escribir un nuevo dato en una celda o para reemplazar un dato allí e#istente< 1.- Ubicar el puntero de celda en el lugar donde se desea escribir un dato .- !scribir el dato y luego presionar Enter o despla"ar el puntero a otra celda #ota:

$uando se está ingresando datos en una celda, observe %ue al lado i"%uierdo de la &arra de !stado aparece el mensaje +ntroducir , y una ve" escrito los datos en las celdas, al seleccionar una de ellas, la barra de f'rmulas mostrará el contenido de esta celda.

(ara modificar un dato e#istente en una celda< 1.- Ubicar el puntero de celda en el lugar donde se encuentra el dato a modificar .- (acer doble clic en la celda, o presionar la tecla de funci'n 7F28 , o hacer clic en la &arra de )'rmulas *.- +l aparecer el cursor parpadeante, modificar el dato escrito en esa celda y luego presionar Enter #ota:

bserve %ue en esta ocasi'n cuando se está modificando el contenido de la celda, en la barra de !stado aparece el mensaje odi"icar!

f! Ti$o de dato En las celdas se pueden escribir los siguientes tipos de datos< =4meros!N %ombinando dígitos del 0 al & el punto decimal& o la barra diagonal& y los dos puntos para las fec*as y *oras& y el espacio en blanco para n4meros fraccionarios! Ejemplos< 2J Enteros $2! Becimales

Pág 19


J $PK 2KP0$P200 0J< #ota:

;raccionarios Cquebrados mi#tosD ;ec*as ?oras

#o deben escribirse los números con comas de millar ni con el signo monetario, pues estos podrán ser agregados luego al dar formato a las celdas.

9e#tos!N Estos datos pueden combinar las letras del alfabeto y cualquier otro carácter del teclado! Ejemplos< %arlos 3obles Empresa Editora ;lorencia  a'os "lmacn Q2 2K de Rulio del 200K @2$@J0N %J@$N"2 er 9rimestre ;órmulas!N Beben empezar a escribirse con el signo igual +S-! Estos datos se encargan de realizar diversas operaciones con los demás datos de la *oja de cálculo& utilizando para ello los operadores y las funciones! Ejemplos< S@2$@J0N S%J@$N"2 S"TJ0U S)1M"C"<"D S(3OMEB:OC"<"D S6V?2 ,! 'elecci/n de ran,o de celda 1na vez que se *an escrito datos en las celdas& estas podrán seleccionarse para realizar diferentes acciones con las celdas seleccionadas< Mover& copiar& formatear& borrar& imprimir& graficar& ordenar& etc! 5a selección de rangos de celda puede *acerse con el mouse& con el teclado& o con una combinación de ambos! %on el Mouse!N "rrastrar el mouse con el puntero en forma de cruz blanca para seleccionar varias celdas agrupadas en forma rectangular! (ara seleccionar filas o columnas completas& *acer clic o arrastrar el mouse sobre los encabezados de fila o columna! %on el 9eclado!N Manteniendo presionada la tecla W)*iftX desplazar el puntero de  celda con las teclas de desplazamiento C & & & D! 9eclado y Mouse!N Manteniendo presionada la tecla W%trlX seleccionar con el puntero del mouse varios rangos de celda! ?acer clic al principio de un rango de celdas y luego W)*iftX @clic al final del rango de celdas!

Pág 20


" fin de poner en práctica lo que *asta aquí estamos aprendiendo& realizaremos a continuación un ejercicio en la *oja de cálculo! Ejercicio Nº 1: Confeccin de un Cuadro de !a"o#

En una *oja de cálculo en blanco escriba los siguientes datos<

Escrita la primera celda, arrastrar el cuadro de relleno para copiar cada serie de datos respecti"amente

1.- !scriba el titulo uadro de Pagos en la celda +1 .- !n la celda +* escriba la palabra 4eses *.- !scriba Abril en la celda + y luego arrastre el cuadro de relleno hasta la celda + para copiar los nombres de los demás meses .- !n &* escriba el te/to )euda , y luego digite debajo de ella los montos mensuales de cada deuda 0.- !n $* escribir el te/to &ra uota y luego arrastrar el cuadro de relleno hacia la derecha para copiar los ttulos de las  cuotas de pago.

Cálculo de las 4 cuotas de -ago -ara cada mes*

ra %uota!N Es un tercio de toda la deuda

Pág 21


1.- !n $ escribir la f'rmula 7;<= y presionar !nter .- 2uego hacer doble clic en el cuadro de relleno de la celda $ para copiar la formula hacia abajo.

2da %uota!N Es un tercio de lo que resta a4n por pagar 1.- !n 3 escribir la f'rmula >7;;?<= y presionar !nter

Jra %uota!N

9ambin es un tercio de lo que resta por pagar 1.- !n ! escribir la f'rmula >7;;);?<= y presionar !nter #ota:

bserve %ue algunas formulas utili"an par4ntesis para agrupar algunas operaciones. !sto se debe a %ue las operaciones se reali"an según su jerar%ua. !sto se verá más adelante cuando se e/pli%ue el uso de los operadores.

ta %uota!N

Es la resta de toda la deuda menos las tres cuotas anteriores!

1.- !n ) escribir la f'rmula 7;;);E; y presionar !nter .- 2uego seleccionar el rango de celdas 3:) y hacer doble clic en el cuadro de relleno de la selecci'n para copiar simultáneamente las tres formulas hacia abajo.

)i los cálculos son correctos& entonces la *oja deberá quedar como se aprecia en el siguiente cuadro! %omo podrá verse& los resultados son mostrados con tantos decimales como quepan en la celda! 5uego se le dará formato a estos resultados para reducir la cantidad de decimales que se desean ver!

Pág 22


Para guardar el libro de Ecel en un archio*

1na vez terminado& *acer clic en el 9otn de (""ice& elegir Guardar como y guardar el 5ibro en la carpeta is ,ocumentos con el nombre< Cuadro de Pagos El libro se guardará como< Cuadro de Pagos!ls 2. C$o $anejar lo# da%o# en la Hoja de Cálculo

1na vez que se *an escrito datos en el interior de las celdas& es muy frecuente que estos datos se deseen mover *acia otro lado de la *oja& o se los desee copiar! Entonces se pueden utilizar varios mtodos para lograr este propósito< a! Para "over ran,o de celda utilizando la o$cione del *eno lo )otone de +erra*ienta 1.- Seleccionar el rango %ue se desea mover. .- 2lamar al menú conte/tual 5clic derecho67$ortar, o hacer clic en el bot'n $ortar, o presionar las teclas $trl.89. bserve %ue alrededor del rango seleccionado aparece un borde m'vil punteado. !sto indica %ue ese rango va ha ser movido. *.- Seleccionar una celda destino donde se desea %ue el rango sea movido .- resionar !nter. !l rango seleccionado originalmente desaparece y el contenido de sus celdas aparecerá en el sitio destino. #ota:

!n ve" de presionar !nter en el to paso anterior tambi4n se puede llamar al menú !dici'n7egar, o hacer clic derecho y elegir egar, o hacer clic en el bot'n egar, o presionar las teclas $trl.8;. ero como verán, más fácil es presionar !nter.

)! Para Co$iar ran,o de celda utilizando la o$cione del *eno lo )otone de +erra*ienta 1.- Seleccionar el rango %ue se desea copiar

Pág 23

Manual de Excel NIVEL AVANZADO .- 2lamar al menú conte/tual 5clic derecho67$opiar, o hacer clic en el bot'n $opiar, o presionar las teclas $trl.8$. #uevamente alrededor del rango seleccionado aparece un borde m'vil punteado, indicando %ue ese rango va ha ser copiado. *.- Seleccionar una celda destino donde se desea %ue el rango sea copiado .- resionar !nter. !n el sitio destino aparecerá una copia del rango seleccionado, y el borde m'vil punteado en el rango original habrá desaparecido, indicando %ue la acci'n de copiar ha finali"ado. #ota:

!n esta ocasi'n el presionar Enter en el to paso hara %ue se copie el rango pero una sola vez . !n cambio, si usted %uisiera copiar dicho rango varias veces en diferentes lugares de la hoja, entonces deberá llamar al menú !dici'n7egar, o hacer clic derecho y elegir egar, o hacer clic en el bot'n egar, o presionar las teclas $trl.8;, y esto tendrá %ue repetirlo en cada celda destino donde %uiera una nueva copia, y cuando haya terminado de reali"ar todas las copias deberá presionar la tecla Esc para retirar el rango m'vil punteado, con lo cual se dará por terminada la acci'n de copiar .

c! Para "over o Co$iar ran,o de celda utilizando el Puntero del "oue 1.- Seleccionar el rango %ue se desea mover o copiar. .- Ubicar el puntero del
!ste m4todo de arrastrar con el
d! Para Co$iar erie de dato con el Cuadro de elleno En ocasiones se desea copiar una celda o un grupo de celdas pero en forma m4ltiple y en celdas adyacentes y consecutivas a la celda original! En ese caso lo más práctico y fácil es utilizar el puntero del Mouse y arrastrar el cuadro de relleno de la celda original! "l obtener copias con el cuadro de relleno no siempre las copias son idnticas al dato original& sino que a veces la copia que se obtiene es lo que se llama una serie de datos! Esto ocurre por ejemplo cuando se copian los nombres de los meses del a'o CEnero& ;ebrero& Marzo& Etc!D& o tambin cuando se copian los nombres de los días de la semana CBomingo& 5unes& Martes& etc!D! Y& aunque estas son las dos 4nicas series

Pág 24

Manual de Excel NIVEL AVANZADO de datos oficialmente definidas por E#cel& tambin se pueden copiar otras muc*as series de datos& como veremos en el ejercicio que viene a continuación! e! Para :orrar dato en la celda 1.- Seleccionar el rango de celdas %ue se desea borrar. 2.- resionar la tecla > Suprimir ?.

Ejercicio Nº 2: Co&iar Serie# de da%o#

(rimero veremos cómo se copian aquellos datos que son considerados como elementos de serie en E#cel! 1.- !n una hoja de cálculo en blanco, en la celda +1 escribir la palabra Abril y luego arrastrar hacia abajo el cuadro de relleno de esa celda. $omo resultado se obtiene la copia de los demás meses a partir de +bril. .- +hora, en la celda &1 escriba la palabra Lun 52unes en forma abreviada6 y tambi4n arrastre su cuadro de relleno hacia abajo. 2os meses del a=o como los das de semana serán reconocidos como elementos de serie, tanto si se los escribe en forma completa como en forma abreviada. #ota:

!n el caso del mes de setiembre, el nombre del mes debe escribirse como Septiembre o Sep , por%ue de otro modo no será reconocida como elemento de una serie.

*.- Seguidamente escriba los siguientes datos y arrástrelos uno por uno con su cuadro de relleno hacia abajo.

2os datos %ue combinan números con te/to, o los %ue combinan te/to con números son copiados como series. 2os números ordinales 1er, do, *er@ o tambi4n 1ra, da. *ra@

Pág 25

Manual de Excel NIVEL AVANZADO 5*:10 sera copia

2as fechas como 7A7BB 5en cual%uiera de sus formatos: -Cun ' -Cun-B6 y tambi4n

las horas, son consideradas como series de datos 0:10, A:10@etc6

"*ora veamos qu pasa cuando los datos que se copian no son considerados como elementos de serie en E#cel! .- !n la fila  de la misma hoja de cálculo anterior, escriba estos datos y luego arrastre uno por uno sus cuadros de relleno.

$omo se puede ver, los te/tos simplemente no son elementos de series, y se copian tan iguales como fueron escritos en la celda. 5!/cel no sabe %ue despu4s del verano sigue el oto=o y la primavera, etc.D ni reconoce los puntos cardinales norte, sur, este y oesteD ni tampoco está enterado de los nombres de los miembros de la sagrada familia Cesús,
" continuación veremos el efecto que produce la tecla de %ontrol W%trl!X al momento de arrastrar el cuadro de relleno para copiar una serie de datos! 0.- !n la celda +10 escriba la palabra Abril . A.- 2uego, manteniendo presionada la tecla de trl . +rrastre el cuadro de relleno de esa celda. E.- (aga lo mismo en la celda &10, escribiendo en ella el número !" y arrastrando el cuadro de relleno pero manteniendo tambi4n presionada la tecla de trl . !l resultado es el %ue puede verse en la figura adjunta. 2a palabra +bril %ue antes fue un elemento de serie, ahora con la tecla de ctrl. es copiada como si se tratase de un te/to cual%uiera. !n cambio, el número 1B con ayuda de la tecla de trl . Se ha convertido en una serie de datos num4ricos cuyo incremento es la unidad. + ra" de esto podemos afirmar lo siguiente:

Pág 26


5Si hubi4ramos escrito 1B

#La tecla de trl$ al momento de copiar con el cuadro de relleno in%ibe la copia de los elementos de serie& o sea 'ue todo los 'ue antes era una serie de(ara de serlo$) #Solo en el caso de copiar un n*mero con la tecla de trl$ %ar+ 'ue este n*mero se convierta en una serie 'ue aumenta de uno en uno$) "*ora veremos lo que sucede cuando se copia con el cuadro de relleno no una& sino varias celdas seleccionadas! .- !n las celdas + y +* escriba los números !" y !,. F.- 2uego seleccione ambas celdas y arrastre su cuadro de relleno.

$omo resultado se obtiene una serie de números consecutivos %ue aumentan de

0 en 0, por%ue la diferencia entre 1B y 10 son 0 unidades. y 1.0, el incremento de la serie hubiese sido de .06

1B.-

!n las celdas $ y $* escriba nuevamente los números !" y !,.

11.- 2uego seleccione tambi4n ambas celdas pero arrastre su cuadro de relleno manteniendo presionada la tecla de trl . !n esta ocasi'n el resultado obtenido es copia de los  números e/actamente como fueron escritos. Ga no son copiados como serie ascendente. 1.- !n las celdas !, !* y ! escriba Hong,
Pág 2

Manual de Excel NIVEL AVANZADO '. C$o u%ili(ar lo# da%o# en la Hoja de Cálculo

a! O$eradore )on los que permiten que las formulas puedan realizar las diferentes operaciones con los datos! E#isten cuatro tipos de operadores< (-eradores atemáticos*

)e encargan de realizar operaciones con datos que necesariamente deben ser del tipo numrico!

: ;

%ambio de signo (orcentaje

<

(otenciación

=

Multiplicación

/

Bivisión

&

)uma

:

3esta

Ejemplos< S0NT2

 

2

SC0NDT2 S20@ N JZ2 S20@ N CJZ2D

 

2 2 

SJ00@IT0U



J!I

(-eradores de $elacin*

5as fórmulas que usan operadores de relación arrojan respuestas lógicas del tipo Herdadero o ;also& seg4n como sea evaluada la comparación! 5os datos a comparar pueden ser numricos o de te#to

> ?

Mayor Menor

>@ Mayor igual ?@ Menor igual @

:gual

Ejemplos< )i "S20& B2SMartes& y ?JSP2P0K Entonces< S"TJ[00



SB2\ [-Hiernes-  S?J[-2KP$P0K- 

;also Herdadero ;also

?> Biferente #ota.- 3entro de las f'rmulas, como puede verse en los ejemplos anteriores, los te/tos, las fechas y las horas deben escribirse siempre entre comillas.

Pág 2'


(-erador de #eto*

Ejemplos<

A %oncatenación

)i "S(uerta y "2S"zul

S-5a +V"V- es +V"2



5a (uerta es "zul

Este operador se encarga de unir te#tos de diferentes celdas para formar frases! 5os datos que se concatenan pueden ser del tipo te#to o tambin numrico& pero el resultado de estas formulas son datos del tipo te#to! (-eradores de $e"erencia*

* B

:ndica un rango

Ejemplos< S)1M"C"<"&%J&E
:ndica unión

es-acioD :ndica intersección

S)1M"C"J


)1M"C6J<%D

5os operadores de referencia se utilizan en las formulas para indicar que se debe *acer con las celdas o los rangos de celdas! 5os dos puntos escritos entre dos coordenadas de celdas permiten *acer referencia a un grupo de celdas dispuestas en forma rectangular! El punto y coma Co la comaD permite separar las celdas o rangos de celdas que se desean unir para una operación determinada! El espacio en blanco escrito entre dos rangos de celdas indica que se desea realizar una operación solamente con la intersección de ambos rangos& y no con todas las celdas en conjunto! )! 5/r*ula )on datos que deben empezar a escribirse con el signo igual& y procesan los datos escritos en las celdas realizando operaciones entre ellas con ayuda de los operadores! (or ejemplo& para escribir tradicionalmente una formula algebraica& lo *aríamos así< ( $ + &J − 2 + $  2 $  + 3

C ) $ −0DJ +

J

2

(ero en E#cel& teniendo en cuenta el orden de la jerarquía de las operaciones& la misma fórmula anterior la tendríamos que escribir de la siguiente manera< @C4&H&93D/,.<2D:25&C4D/F:10D<3&1 3/4D

Pág 2.

Manual de Excel NIVEL AVANZADO Be este modo se podrán escribir entonces todos los cálculos que uno desee utilizando para ello las fórmulas de E#cel! )in embargo& si estas fórmulas son muy comunes en nuestros cálculos& o si stas llegan a ser muy e#tensas o muy complicadas] entonces se puede recurrir al uso de las +funciones-& que es un mtodo abreviado para escribir fórmulas! c! La 5ic+a 5/r*ula  la ecritura de funcione 1na manera práctica para escribir formulas que se usan muy a menudo& es *aciendo uso de la ;ic*a + Formulas-& la cual muestra en el grupo + 9iblioteca de "uncionesuna lista de las funciones más utilizadas en E#cel<

). *or$a%o de Celda#

a! La 5ic+a Inicio El solo obtener correctamente los resultados en nuestros cuadros de E#cel no es suficiente] tambin es necesario que estos cuadros tengan una apariencia que *aga fácil su lectura y entendimiento! (ara esto e#iste la posibilidad de cambiar la apariencia del contenido de las celdas con ayuda de la fic*a +nicio y los botones de *erramientas que se encuentran en los grupos Fuente& lineacin& mero& Estilos y Celdas< *ica /nicio

)i se tiene el puntero del mouse sobre las celdas y se *ace un clic derec*o con l& aparecerán la inibarra de Herramientas& y además el men Contetual& con

Pág 30 4inibarra de -erramientas

Manual de Excel NIVEL AVANZADO diversas opciones cada uno para *acer más fácil el dar formato a las celdas que se *ayan seleccionado! )! Para dar 5or*ato a un Cuadro "bra el arc*ivo %uadro de (agos!#ls# que se guardo en el ejercicio anterior! %omo se verá& en ese cuadro solo se ingresaron datos& pero no se le dio formato alguno! 1.- Seleccione el rango +1:)1 y utilice el bot'n ombinar - centrar , para poner el titulo Cuadro de Pagos al centro del cuadro. 2uego aumente el tama=o de fuente a 1 puntos, y subráyelo. .- Seleccione los ttulos de la fila * y p'ngalos Ne.rita , entrado , dele $olor de )uente y $ olor de /elleno , luego colo%ue borde a cada una de sus celdas con la opci'n 0odos los bordes . *.- )inalmente seleccione las celdas con números y dele el formato !stilo millares para separar con comas en cada millar y redondearlos a  decimales. .- ara guardar el archivo con los cambios %ue acaba de hacer, presione las teclas trl1G . Color de Fuente Centrar

egrita

#odos los bordes

Color de $elleno

Estilo millares

Pág 31


CAPITULO 2 1! "ane#o del Tie*$o en Excel 

La 5ec+a (ara E#cel& todo el tiempo se mide en días! El + da- es la unidad de medida oficial! (ara nosotros los seres *umanos una fec*a es necesaria especificarla con tres diferentes n4meros& como por ejemplo la fec*a< 2KP0$P200K& significa 2K días& $ meses y 200K a'os! 5o *acemos así porque las fec*as de nuestro calendario se empiezan a contar a partir del nacimiento de %risto& y desde entonces *asta el 2K de Rulio de este a'o ya *an transcurrido más de 2000 a'os! (ero esa misma fec*a para E#cel representa un solo n4mero CJI$D& y no tres! Y como *ace esto E#celF En E#cel& el tiempo no se empieza a contar a partir del nacimiento de %risto& sino] el tiempo lo empieza a contar a partir del ^ de Enero del a'o 00! Heamos la siguiente tabla para entender mejor esto< Fecha ormal

Fecha -ara Ecel

da/mes/aGoD

dasD

0P0P00



02P0P00

2

0JP0P00

J

JP0P00

J

0P02P00

J2

02P02P00

JJ

JP2P00

JII

0P0P0

JI$

02P0P0

JIK

2KP0$P200K

JI$

El primer día del a'o 00& representa el n4mero & y a partir de allí cada día que transcurre es un n4mero más! O sea que al 2KP0$P200K ya *an transcurrido J&I$ días!

Pág 32

Manual de Excel NIVEL AVANZADO Ejercicio Nº 1: +anejo de *ec,a#

En una *oja de cálculo escriba lo siguiente<

)eleccione el rango& luego *aga un clic derec*o sobre l y elija la opción Formato de celdas_ En la ventana que aparece seleccionar la categoría eneral y "ceptar!

5uego darle a EJI el formato eneral !

En sta fec*a terminaría el contrato!

Pág 33


5as funciones B:"& ME) y "`O e#traen de 60 los n4meros que representan el día& mes y a'o de la fec*a escrita allí! 5a función B:")EM da como resultado un n4mero del  al $& que indica el día de la semana que cae dic*a fec*a! )i la respuesta es  entonces usted nació un día domingo& si es 2 entonces nació un lunes& y así sucesivamente! )i desea que E#cel le diga en modo te#to que día de la semana nació usted& entonces escriba en la celda %J la siguiente formula< @E%E+$,53BI,omingoIBI%unesIBIartesIBIiJrcolesIBIKueesIBILiernesIBIMábadoID

O tambin podría escribir en 6 la fórmula< @#E#(950BNddddND 

La %ora %omo sabemos que para E#cel el tiempo se mide en días! Entonces las *oras& minutos y segundos serán una fracción del día! O sea que las 2<00 meridiano sería para E#cel medio día& es decir el n4mero 0! ó ! Heamos la siguiente tabla para entender mejor esto< Hora ormal

Hora -ara Ecel

hora*minutoD

"raccin de daD

2<00

0!

I<00

0!2

K<00

0!$

0
0!0$22

Pág 34

Manual de Excel NIVEL AVANZADO Ejercicio Nº 2: +anejo de Hora#

En una *oja de cálculo escriba lo siguiente<

(uede usar %trl@)*ift@Cdos puntosD

5a parte entera representa la fec*a actual& y la parte decimal representa las *oras transcurridas en este día!

5uego darle a B2J el formato personalizado hh*mm

)umar las dos columnas para calcular las *oras de llegada de los ve*iculos

5uego de sumar las tres columnas& a la celda ;0 darle el formato personalizado< 7h8*mm

El formato personalizado< 7h8*mm& muestra la suma absoluta de las *oras!

Pág 35


2! 5or*ato Peronalizado de Celda 

Cuando crear for*ato $eronalizado 5a apariencia que pueden adquirir los n4meros en las celdas& pueden variar seg4n el formato que se les asigne y para esto e#isten varias categorías de formatos! (ero si estas no satisfacen nuestras necesidades& uno mismo puede crear sus propios formatos de n4meros personalizados! Ejemplo de formatos tradicionales e#istentes en E#cel< A 1

=,;2=.@%

Estilo millares

,%.%%

2

(<. B2$.$@

Estilo moneda

(<. ,%.%%

8

&=.2C

Estilo porcentual

%.%C

9 ;

>2D.@?

3egati"os entre parntesis

>%.%%?

;Fun

*ecas con dGa y mes

dmmm

Ejemplo de formatos personalizados creados por el usuario< A < >

B;.@ Hg

Para pesos en Iilogramos

%.% JHgK

? @

=2.; º C %%%$@;

6rados centGgrados Para c#digos

%.%KK %%%%%%

/61 &DC

Mexto y porcentaje

J/61K %C

10

; puntos

3+mero y texto

% JpuntosK

9enga en cuenta que en todos los ejemplos anteriores las celdas tienen datos numricos y aunque el formato *a cambiado su apariencia& + el contenido de ellas sigue siendo numJrico - y puede operarse matemáticamente en cualquier fórmula! #ota.- $uando se crean formatos personali"ados, estos formatos solo tendrán valide" dentro del libro en el cual se crearon.

(ara poder crear formatos personalizados *ay que seguir unas cuantas reglas& las cuales pasamos a indicar a continuación< • Co*o crear for*ato $eronalizado

Pág 36

Manual de Excel NIVEL AVANZADO En la fic*a +nicio en el grupo mero *acer clic en el botón iniciador de cuadro de dialogo Formato/Celdas*mero& y elegir la categoría PersonaliOada<

Escribir a)u el "ormato -ersonaliOado

En la casilla de te#to llamada #i-o se pueden definir formatos personalizados o elegir los ya e#istentes en la lista inferior! Crear un "ormato de nmero -ersonaliOado 1. Seleccione las celdas a las %ue desea dar formato. . !n la ficha Inicio en el grupo #úmero hacer clic en el bot'n iniciador de cuadro de dialogo )ormato7$eldas:#úmero. *. !n la lista $ategora, haga clic en ersonali"ada. . !n el cuadro Jipo, modifi%ue los c'digos de formato de número para crear el formato %ue desee.

(uede especificar *asta cuatro secciones de códigos de formato! 5as secciones& separadas por caracteres de punto y coma& definen los formatos de los n4meros positivos& n4meros negativos& valores cero y te#to& en ese orden! )i especifica sólo dos secciones& la primera se utiliza para los n4meros positivos y ceros& y la segunda se utiliza para los n4meros negativos! )i especifica sólo una sección& todos los n4meros utilizan ese formato! )i omite una sección& incluya el punto y coma que separa esa sección! *ormato de n+meros positi"os

*ormato de ceros

B0!00D  FO&OH B0!00D  0!00  5acturaJ K *ormato de n+meros negati"os

*ormato de textos

Pág 3


#ota.- !l smbolo de arroba arroba K representa representa el contenido contenido de la celda cuando este contenido es es del tipo te/to.

1tilice códigos de formato que describan cómo desea mostrar un n4mero& una fec*a u *ora& una moneda& porcentajes o notación científica y un te#to o espacio! Ejem-lo*

" 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< FA=ULB0!00D  FO&OH B0!00D  'in 6alor 6alor  K anuladaJ A 1

?>89!@

2

0

ero

8

Mexto

9

5actura <2;9!2;?

;

:oleta

3+mero negati"o

3+mero positi"o Mexto

El resultado final en el rango formateado es el siguiente< A 1 2

H?B>89!@0D

Entre parntesis y color rojo

'in 6alor

El ero cambia a (in 1alor

8

5actura anulada

9

;

:oleta anulada

(e le agrega la palabra anulada 0edondea y le da color a!ul (e le agrega la palabra anulada

%omo se puede ver& cada celda adquiere el formato que le corresponde seg4n el contenido que esta tenga! • C/di,o de for*ato de n-*ero $ara fec+a  +ora

Bías& meses y a'os (ara presentar días& meses y a'os& incluya en una sección los siguientes códigos de formato! )i utiliza una AmA inmediatamente detrás del código A*A o A**A& o bien inmediatamente delante del código AssA& Microsoft E#cel presentará los minutos minutos en lugar de presentar presentar el mes!

Pág 3'


Para er

se este cdigo

5os meses como 2 5os meses como 02 5os meses como ene!dic! 5os meses como enerodiciembre 5os meses como la inicial de cada mes 5os días como J 5os días como 0J 5os días como lun!sáb! 5os días como lunessábado 5os a'os como 00 5os a'os como 00

m mm mmm mmmm mmmmm d dd ddd dddd aa aaaa

?oras& minutos y segundos (ara presentar *oras& minutos y segundos& incluya incluya en una sección los siguientes códigos de formato! Para er

se este cdigo

5as *oras como 02J 5as *oras como 002J 5os minutos como 0 5os minutos como 00 5os segundos como 0 5os segundos como 00 5as *oras como  a!m! 5a *ora como 
* ** m mm s ss * a!m!Pp!m! *
Pág 3.


)i el formato contiene la indicación a!m! o p!m!& la *ora se basará en el formato de 2 *oras& donde Aa!m!A o AaA indica las *oras desde la medianoc*e *asta el mediodía y Ap!m!A o ApA indica las *oras desde el mediodía *asta la medianoc*e! En caso contrario& el reloj se basará en el formato de 2 *oras! 5a letra AmA o las letras AmmA AmmA debe debenn apar aparec ecer er inme inmedi diat atam amen ente te detr detrás ás del del códi código go A*A A*A o A**A A**A&& o bien bien inmediatamente delante del código AssA] de lo contrario& Microsoft E#cel presentará el mes en lugar de presentar los minutos! 

C/di,o de for*ato de n-*ero $ara $oicione deci*aleB e$acioB colore  condicione

1tilice los códigos de formato de n4mero para crear un formato de n4mero personalizado! ,ecimales Q dgitos signi"icatios

(ara dar formato a las fracciones o los n4meros con decimales& incluya los siguientes dígitos marcadores en una sección! )i un n4mero tiene más dígitos a la derec*a del separador que marcadores en el formato& se redondeará para que tenga tantos decimales como marcadores! )i *ay más dígitos a la izquierda del separador que marcadores& se presentarán los dígitos adicionales! )i el formato contiene solamente signos de n4mero CQD a la izquierda del separador& los n4meros menores que la unidad comenzarán por el separador! separador! #

0 ?

Muestra 4nicamente los dígitos significativos y no muestra los ceros sin valor! CceroD Muestra los ceros sin valor si un n4mero tiene menos dígitos que la cantidad de ceros en el formato! "grega los espacios de los ceros sin valor a cada lado del separador decimal& para alinear los decimales con formato de fuente de anc*o fijo& como %ourier =e.! 9ambin puede utilizarse F para las fracciones que tengan un n4mero de dígitos variable!

Para er

se este cdigo

2J! como 2J!I

QQQQ!Q

K! como K!00

Q!000

!IJ como 0!I

0!Q

2 como 2!0 y 2J!IK como 2J!$

Q!0Q

!JK! 02!I y 2!K con decimales alineados

FFF!FFF

!2 como  P y !J como  JP0& con los símbolos de división alineados

Q FFFPFFF

Pág 40

Manual de Excel NIVEL AVANZADO Me-arador de millares

(ara ver una coma como separador de los millares o para ajustar la escala de un n4mero mediante un m4ltiplo de mil& incluya una coma en el formato de n4mero!

Para er

se este cdigo

2000 como 2&000

Q&QQQ

2000 como 2

Q&

2200000 como 2&2

0&0&&

Color

(ara definir el color de una sección del formato& escriba en la sección el nombre de uno de los siguientes oc*o colores entre corc*etes! El color debe ser el primer elemento de la sección! W=E>3OX W"G15X W;1%):"X W3OROX W">1"M"3:="X WHE3BEX W65"=%OX W"M"3:55OX Condiciones

(ara definir los formatos de n4mero que se aplicarán 4nicamente si coinciden con las condiciones que se *ayan especificado& encierre la condición entre corc*etes! 5a condición consta de un operador de comparación y un valor! (or ejemplo& el siguiente formato muestra los n4meros iguales o inferiores a 00 en color rojo y los n4meros superiores a 00 en color azul! 7$ojo87?@1008R7Oul87>1008

En cambio& para aplicar formatos condicionales a las celdas Cpor ejemplo& el sombreado de color que depende del valor de una celdaD utilice el comando ;ormato condicional en el men4 ;ormato! 

C/di,o de for*ato de n-*ero $ara texto  e$aciado gregar caracteres

(ara ver te#to y n4meros en una celda& encierre los caracteres de te#to entre comillas CAAD o ponga delante de un solo carácter una barra invertida CD! :ncluya los caracteres en la sección correspondiente de los códigos de formato! (or ejemplo& introduzca el formato  0!00 AE#cesoA]N 0!00 ABefectoA para ver un importe negativo como AN2!$ BefectoA! El espacio y los siguientes caracteres se muestran sin comillas<  N @ P C D < / Z V  Ccomilla simple izquierdaD  Ccomilla simple derec*aD   h S \ [

Pág 41


+ncluir una seccin de teto

)i se incluye una sección de te#to& siempre será la 4ltima sección en el formato de n4mero! :ncluya el carácter  en la sección en que desee presentar el te#to escrito en la celda! )i se omite el carácter  en la sección de te#to& no se verá el te#to que se *aya escrito! )i desea ver siempre caracteres de te#to específicos con el te#to escrito& encierre el te#to adicional entre comillas dobles CA AD& por ejemplo& Afacturación bruta de A )i el formato no incluye una sección de te#to& el te#to que escriba no se verá afectado por el formato! gregar es-acio

(ara crear un espacio con el anc*o de un carácter en un formato de n4mero& incluya un subrayado S seguido del carácter! (or ejemplo& si despus de un subrayado se cierra un parntesis SD entonces los n4meros positivos se alinearán correctamente con los n4meros negativos que estn entre parntesis! $e-etir caracteres

:ncluya un asterisco CTD en el formato de n4mero! El asterisco repetirá el siguiente carácter *asta llenar el anc*o de la columna! (or ejemplo& introduzca MB0!00 para incluir suficientes signos @ despus del signo monetario para llenar una celda! El resultado sería< N$,2&B.$@ 

Eli*inar un for*ato de n-*ero $eronalizado 1.- !n la ficha Inicio en el grupo #úmero hacer clic en el bot'n iniciador de cuadro de dialogo )ormato7$eldas: #úmero. .- !n la lista $ategora, haga clic en ersonali"ada. *.- !n la parte inferior del cuadro Jipo, haga clic en el formato personali"ado %ue desee eliminar. .- (aga clic en !liminar.

)olamente pueden eliminarse los formatos personalizados! Microsoft E#cel aplicará el formato predeterminado C>eneralD a todas las celdas del libro a las que se *aya dado formato mediante el formato personalizado que se *a eliminado! 

'i la fec+a introducida en la +o#a de cálculo no tienen el *i*o a$ecto

Pág 42

Manual de Excel Excel NIVEL AVANZADO %uando se introduce una fec*a en una celda& dic*a fec*a aparece en un formato de fec*a predeterminado o en un formato aplicado a la celda antes de introducir la fec*a! El formato de fec*a predeterminado se basa en la configuración de la fic*a ;ec*a del cuadro de diálogo %onfiguración regional& al que se tiene acceso desde el (anel de control de ,indo.s! )i estos valores de fec*a *an cambiado& cualquier fec*a e#istente en los libros no formateados con el comando ;ormato de celdas tambin cambiará!

Pág 43


CAPITULO 8 2. *uncione# en E-cel: fr$ula# #i$&lificada#

"lgunas funciones *an cambiado en E#cel 200 como las siguientes ;1=%:O=E) E= E%E5 Ej%E5 200$

Ej%E5 200

'uscar

onsulta

'uscar2

aracter Extrae

0onsulta2 0ar

3&2ora

%ed 4iempo

356

36

&iError

&iError

a! Cuando Cuando utiliza utilizarr la la  5uncio 5uncione ne %uando las formulas que necesitamos se vuelven algo e#tensas y de uso muy frecuente& los mas practico es recurrir al uso de las ;unciones de E#cel& que no son sino un mtodo abreviado para escribir esas fórmulas! (ero para escribir una función es necesario cumplir con ciertas reglas de sinta#is! 5a sinta#is& es la forma estricta en que debe escribirse una función! c! C/*o C/*o ecri) ecri)ir ir la la 5unci 5uncione one 9odas las funciones constan de dos partes< El nombre de la función y sus argumentos que deben escribirse entre parntesis y separados por comas<

S=ombre de función C"rgumentosD Be las J2 funciones que ya e#istían en E#cel& la versión 200$ " 200 *a agregado 0 funciones más *aciendo un total de JJ funciones& las cuales se clasifican a*ora en las siguientes categorías< ;unciones de complementos y automatización ;unciones de cubo ;unciones de base de datos ;unciones de fec*a y *ora ;unciones de ingeniería ;unciones financieras ;unciones de información ;unciones lógicas ;unciones de b4squeda y referencia ;unciones matemáticas y trigonomtricas ;unciones estadísticas ;unciones de te#to #(#%

5  12 20 3. 54 .  1 60 '2 2 33.

Pág 44


Nota: !n forma estándar !/cel tiene disponibles solo 234 funciones. ero si se cree conveniente se pueden agregar en forma complementaria 56 funciones más, para análisis de datos financieros, cientficos, cientficos, y otros. otros. ara ello hay %ue ingresar ingresar al bot'n de O77ice , hacer clic en el bot'n inferior Opciones de Excel y elegir omplementosD luego en la parte inferior aparece una lista desplegable en la cual debe elegir la opci'n omplementos de Excel y y luego hacer hacer un clic en el bot'n Ir D aparecerá entonces una ventana de dialogo en la cual deberá activar la casilla 8erramientas para an+lisis , y finalmente +ceptar. +ceptar.

?e aquí algunos ejemplos de funciones< S)1M"C"J<"20D S(3OMEB:OC?
8 9 ; < > ? @ 10 11

Ni3o Car odri,o &o

Ni3a Lorena Nao*i Claudia

Edad @

&% 7eb && D =

5ec+a &BAgo no definida &&(ep %=Ago $;Ful &D(ep

Pág 45

Manual de Excel NIVEL AVANZADO las personas que trabajan con E#cel! El siguiente cuadro muestra una relación de personas a quienes se les *a programado la fec*a de su cita en una %línica< 5as funciones como cualquier fórmula deben empezar con un signo igual& y el resultado de la sta aparecerá en la misma celda donde se escribe la función! La 5unci/n 'U"A )uma de las edades de todos los pacientes< S)1M"C6<6D

3pta< J

!sta funci'n considera solo las celdas con datos num4ricos 5ignora la celda &A6

La 5unci/n PO"E4IO (romedio de edades de todos los pacientes< S(3OMEB:OC6<6D

3pta< K!$

#o se considera en el cálculo la celda &A pues su contenido no es num4rico

La 5unci/n "A Edad má#ima de un paciente< SM"C6<6D

3pta< 

!ncuentra el má/imo valor num4rico del rango mencionado

La 5unci/n "IN Edad mínima de un paciente< SM:=C6<6D

3pta< J

!ncuentra el mnimo valor del rango mencionado 5ignorando la edad del beb4 por%ue no es un dato num4rico6

La 5unci/n CONTA %antidad de pacientes que tienen fec*a de cita programada S%O=9"3C%<%D 3pta<  !sta funci'n cuenta cuántas celdas tienen datos num4ricos 5las fechas son números6

La 5unci/n CONTAA %antidad 9otal de pacientes S%O=9"3"C%<%D

3pta< I

!sta funci'n en cambio cuenta cuántas celdas en total están ocupadas sin importar el tipo de datos

La 5unci/n ENTEO Edad promedio de los pacientes sin considerar los decimales< SE=9E3OC(3OMEB:OC6<6DD 3pta< K 2a funci'n !ntero trunca la parte decimal del resultado

Pág 46

Manual de Excel NIVEL AVANZADO La 5unci/n E4ON4EA Edad promedio de los pacientes redondeada a  decimal y a cero decimales< S3EBO=BE"3C(3OMEB:OC6<6D&D 3pta< K!K S3EBO=BE"3C(3OMEB:OC6<6D&0D 3pta<  2a funci'n Ledondear tiene dos argumentos separados por una coma: el valor calculado 5el romedio6, y la cantidad de decimales a la %ue se desea redondear el resultado.

e! Coordenada A)oluta  Coordenada elativa

S3EBO=BE"3CE4/FT2&2D S3EBO=BE"3CE5/FT2&2D S3EBO=BE"3CE6/FT2&2D S3EBO=BE"3CE/FT2&2D En la coordenada ;2 del ejercicio anterior& el signo de  situado antes del n4mero de fila *ace que este n4mero 2 permanezca constante al copiarse la formula *acia abajo! En cambio& si alguna fórmula en otra ocasión tuviese que ser copiada& no *acia abajo& sino *acia la derec*a] entonces *abría que escribir el signo  antes de la letra de la coordenada< ;2 para que al copiarse la fórmula& la letra ; permanezca constante& de otro modo la letra aumentaría alfabticamente a >& ?& :_ etc! " estas formulas así escritas con el signo de & se las conoce como fórmulas con coordenadas absolutas! En conclusión entonces& una coordenada puede escribirse de varias formas seg4n sea el caso< ;2 coordenada relativa ;2 coordenada con n4mero de fila absoluta ;2 coordenada con letra de columna absoluta ;2 coordenada con n4mero de fila y letra de columna absolutas #ota.- MSi una f'rmula no necesita ser copiada a ningún lado, entonces no es necesario %ue tenga coordenadas absolutas de ningún tipoN.

Ejemplo< "bra un libro nuevo en blanco y en la celda " escriba la siguiente fórmula< N;O*N$
%uando presione la tecla Enter la celda se llenará de numerales< QQQQQQQQQ Esto ocurre porque la formula en realidad esta realizando una división entre cerro& lo cual no se puede calcular! (ero el resultado de la formula a*ora no es lo importante& sino que es lo que ocurre con la formula cuando esta es copiada! (ara ver en la celda no el resultado& sino la formula que usted escribió& *aga lo siguiente<

Pág 4

Manual de Excel NIVEL AVANZADO En la fic*a Formulas& grupo uditora de "ormulas& *acer clic en el comando ostrar "ormulas! Entonces la formula en la *oja de cálculo se verá así< N;O*N$
%opie la formula con el cuadro de relleno *acia abajo! %omo verá& los n4meros de las coordenadas de celda que no tienen dólar& *an aumentado! Cel n4mero 2 no es una coordenada de celda& por esto permanecerá siempre constanteD N;O*N$
"*ora copie la formula de la celda " *acia la derec*a usando el cuadro de relleno! El resultado se verá como en la siguiente figura! N;O*N$
N;O6N$
N;O-N$
5a letra de la coordenada que no tenía dólar *a aumentado alfabticamente& pero las que tenían dólar *an permanecido constantes! "*ora veamos otro ejemplo donde será necesario utilizar funciones que se encarguen de tomar decisiones por cuenta propia para ayudarnos a realizar cálculos utilizando para ello ciertos criterios de razonamiento lógico! El siguiente es una 9abla de )ueldos de varios empleados de una empresa& donde se desea aumentar de diferentes formas los sueldos de estos empleados! 5a labor de modificar sueldos matemáticamente *ablando& es una tarea relativamente sencilla si se dispone de *erramientas de cálculo y de los criterios ya establecidos para saber a quienes y en qu forma se aumentaran los sueldos! El problema está en que los aumentos por lo general no son para todos por igual de manera uniforme& sino que unos tienen más aumento que otros en base a decisiones estudiadas de antemano& y que a*ora nosotros debemos llevar a cabo dic*os cálculos& y si son muc*os los empleados y además cada empleado tiene una manera diferente de calcular su aumento de sueldo& entonces la labor se volvería muy tediosa si no fuera que podemos contar con la ayuda de las funciones lógicas de E#cel!

Pág 4'

Manual de Excel NIVEL AVANZADO Ejercicio Nº ': eali(ar au$en%o# en una /ala de Sueldo#

Heamos pues! En la *oja de cálculo se tiene la 9abla de )ueldos de varios empleados Caquí a*ora vemos solo  empleados& pero suponga que se trata de varios cientosD! Esta tabla muestra los datos de cada empleado y el monto de sus sueldos actuales& y en el transcurso de  meses y por razones de un convenio laboral en la empresa& ellos van a recibir paulatinamente varios aumentos de sueldo<

" continuación presentamos las condiciones de aumento para los sueldos< "umento de Enero!N 9odos los empleados por igual tendrán en este mes un aumento equivalente al 0U de su sueldo actual! En la celda B escribir y copiar la siguiente fórmula< @C4=10;

%omo resultado se tendría entonces el siguiente cuadro<

Pág 4.

Manual de Excel NIVEL AVANZADO La 5unci/n 'I 5a sinta#is de la función es la siguiente< @M+Condicin lgicaB ccin1B ccin2D L F

Bonde< ondici9n l9.ica Es una e#presión que contiene un operador de relación C[& \& [S& \S& S& \[D!

Acci9n! - Acci9n2 )on cualquier tipo de dato válido para escribirse en una celda 1sar la función M+B es como tener la posibilidad de escribir 2 datos diferentes en una misma celda& pero solo uno de los datos será el resultado de la función! )i la e#presión lógica resulta ser Lerdadera& entonces la accin1 es la respuesta de la función& en caso contrario si es Falsa entonces la accin2 sería la respuesta de la función! "umento de ;ebrero!N 9odos los que tengan un sueldo menor a 00 tendrán un aumento del 0U& sino el aumento será solo del U!! En la celda E escribir y copiar la siguiente fórmula< @M+C4?1500BC4=10;BC4=5;D

5os aumentos de este caso entonces serían los que se aprecian en el siguiente cuadro<

"umento de Marzo!N 5os que tengan un sueldo menor a 00& o los que tengan el cargo de 9ecnico] tendrán un aumento del 0U! Y los que no cumplan ninguna de las condiciones anteriores& entonces para ellos el aumento será solo del U!! En la celda ; escribir y copiar la siguiente fórmula< @M+(C4?1500B94@N#ecnicoNDBC4=10;BC4=5;D !sta f'rmula tiene ahora %ue reali"ar previamente una doble comparaci'n l'gica. rimero verifica si el sueldo es menor %ue 10BB y

Pág 50

Manual de Excel NIVEL AVANZADO tambi4n verifica si el cargo es Jecnico. +mbas comparaciones están encerradas entre par4ntesis y se le antepone la letra M ON, lo %ue significa %ue bastará con %ue una de las  condiciones se cumpla :no es necesario 'ue ambas sean verdaderas; para %ue se efectúe el primer cálculo del 1BO de aumentoD y si ninguna de las  condiciones se cumple entonces se ejecutara el segundo cálculo del 0O de aumento. $omo puede verse, cuando la funci'n O se antepone a las dos condiciones l'gicas, entonces no es necesario %ue ambas sean verdaderas, es suficiente con %ue una de ellas sea cierta para %ue se ejecute la acci'n1.

#ota.- Si en una f'rmula hay %ue escribir un te/to, como es el caso de la palabra 0ecnico , esta palabra deberá estar entre comillas, y además deberá escribirse de igual modo como está escrito en la tabla de sueldos, es decir, si en la tabla se escribi' la palabra sin acento entonces tampoco hay %ue ponerlo, o %ui"ás en la tabla si tena acento o estaba en plural o en singular, entonces en la formula tambi4n deberá escribirse del mismo modo. 2as mayúsculas o minúsculas no se toman en cuenta.

El cuadro con los aumentos de marzo se verían entonces así<

"umento de "bril!N En esta ocasión y por ser el mes de la )ecretaria& solo se aumentará un KU a las secretarias siempre y cuando estas ganen menos de 00] a las demás secretarias y al resto de los empleados se les dará una bonificación de  soles! En la celda > escribir y copiar la siguiente fórmula< @M+UC4?1500B94@NMecretariaNDBC4=';B45D !sta nueva f'rmula es parecida a la anterior pues tambi4n hay %ue verificar dos condiciones l'gicas, con la diferencia %ue ahora si es necesario %ue ambas sean verdaderas para %ue haya el aumento del O, por esta ra"'n ahora se antepone la funci'n < . #ote además %ue la segunda acci'n ya no es un cálculo matemático de porcentaje, sino %ue es una cantidad fija de 0 soles.

Pág 51


El cuadro con los aumentos de "bril serían los siguientes<

"umento de Mayo!N Este 4ltimo mes se *arán J diferentes clases de aumento teniendo en cuenta el monto actual de su sueldo básico en la columna %! (ara esto observe la grafica siguiente<

5os que ganen menos de 00 soles tendrán 20U de aumento& de 00 *asta 2000 soles tendrán U y los que ganen más de 2000 soles tendrán 0U de aumento! En la celda ? escribir y copiar la siguiente fórmula< F L

@M+C4?1500BC4=20;BM+C4?2000BC4=15;BC4=10;DD L F

$omo e/isten tres cálculos diferentes de aumento, pero la funci'n SI solo tiene la posibilidad de escribir  acciones, entonces se escribe una funci'n SI dentro de otra funci'n SI 5a esto se conoce como funciones SI anidadas6 de este modo la primera funci'n SI calcula el primer aumento y la segunda funci'n SI se encarga de los otros dos aumentos restantes.

Pág 52

Manual de Excel NIVEL AVANZADO #ota.- !n todas las f'rmulas de !/cel cada ve" %ue se abre un par4ntesis debe haber otro %ue lo cierre. bserve %ue en la formula anterior, al haber  funciones SI anidadas, al final es necesario cerrar con  par4ntesis.

;inalmente el cuadro con los aumentos de Mayo quedaría así<

%omo *emos podido ver& los cálculos en la tabla de sueldos *an sido calculados en cada mes por una sola fórmula& a pesar que los aumentos individuales para cada empleado eran distintos! (ero la función ): se encargó por nosotros de tomar las decisiones de cómo obtener estos aumentos y sin importar cuantos empleados tenga la empresa! " continuación vamos a ver cómo se puede e#traer la información de una tabla como la que *emos obtenido! (or ejemplo& cómo *aríamos para averiguar en la tabla cual es el sueldo que gana 5orena& o cual fue el aumento que ella recibió en Marzo! 9enga en cuenta que 5orena puede ser una de los cientos de empleados que e#isten en la tabla& y se desea encontrar esta información en forma rápida! (ara casos de b4squeda de datos como los descritos en el párrafo anterior& e#isten varias funciones] y una de ellas es la que vamos a ver a continuación& la función 61)%"3H! (ero antes de e#plicar el manejo de la función 61)%"3H& es necesario aprender cómo se pueden dar nombres de rango a las celdas! '. No$rar an"o# de Celda

a! Cuándo no*)rar un an,o de Celda

%omo ya *emos visto& las fórmulas y las funciones en muc*as ocasiones *acen mención a celdas o tambin a rangos de celda! 6ueno] cuando una celda o un rango de celdas es mencionado muc*as y veces en nuestras formulas& entonces lo más práctico es darle un nombre a este rango& y de allí en adelante en vez de escribir las coordenadas de celda& mejor se escribe el nombre de rango que le *emos dado! (ara nosotros será más fácil recordar el nombre de rango que las coordenadas de celda de ese rango!

Pág 53


)! C/*o dar No*)re a un an,o de Celda (ara nombrar un rango de celdas e#isten dos maneras de *acerlo& y ambas se

pueden usar por igual! =rimera 7orma>

1.- Seleccionar el rango de celda %ue se desea nombrar. .- !n la ficha ?ormulas 7 Nombres de7inidos , elegir la opci'n Asi.nar nombre a un ran.o. *.- !n la ventana de diálogo %ue aparece escribir el nombre %ue se le %uiere dar al rango 5lo recomendable es %ue el nombre sea de una sola palabra, no importa en mayúsculas o minúsculas6 .- )inalmente hacer clic en el bot'n +ceptar.

Se.unda 7orma> 1.- Seleccionar el rango de celda %ue se desea nombrar. .- (acer un clic en el casillero de uadro de Nombres %ue se encuentra en la parte i"%uierda de la barra de f'rmulas sobre los encabe"ados de columna. *.- !scribir en el casillero el nombre %ue se le %uiere dar al rango. .- resionar !nter.

1n vez nombrado un rango& este podrá ser usado de allí en adelante en cualquier fórmula o en cualquier orden de men4 de E#cel cada vez que se tenga que *acer referencia a ese rango! Ejemplo< )uponga que al rango "J<"$0 se le *a dado el nombre de rango (">O)! Entonces_

Pág 54

Manual de Excel NIVEL AVANZADO )i se deseara calcular la suma de todos los pagos& podríamos utilizar la función autosuma para ello< S)1M"C"J<"$0D "ntes se tenía que escribir la formula< "*ora que "J<"$0 tiene el nombre (">O)< S)1M"C(">O)D Ejercicio Nº ): O%ener una ole%a de !a"o#

El libro del ejercicio anterior que calculaba aumentos de sueldo posee dos *ojas< 9abla de )ueldos!N %ontiene la relacion de los empleados de la enpresa con sus cargos& sueldos y aumentos! 6oleta de (ago!N%ontiene un modelo simple de boleta de pago! 



%abe la aclaración& que en el siguiente ejercicio no se va a confeccionar una 6oleta de (agos formal como las que en realidad se usaría para el pago de los empleados en una empresa& sino que solamente nos va a servir de ejemplo para entender cómo es que se obtienen los datos de una tabla! En los ejercicios más avanzados al final del libro ya se verán soluciones finales para casos más completos!

%omo se puede ver en la figura& la 9oleta de Pagos ya está formateada< el 9itulo centrado& las celdas con bordes y color de relleno!

En esta boleta se desea que al escribir el nombre de un empleado en la celda 6J& en el resto de las celdas de la boleta aparezcan inmediatamente los datos de este empleado& los cuales serían traídos de la 9abla de )ueldos! (ara lograr esto *ay que realizar lo siguiente< ,ar el nombre ME%,(M al rango a la #abla de sueldos*

Pág 55

Manual de Excel NIVEL AVANZADO 1.- !n la (oja1: Jabla de Sueldos, seleccionar el rango de la tabla de sueldos +*:(1. .- (acer un clic en la casilla de $uadro de nombres, escribir la palabra S@ELDOS y presionar !nter.

La 5unci/n :U'CA6 a+ora CON'ULTA6 5a sinta#is de la función es la siguiente< @9MC$L,atoB$ango de #ablaBV ColumnaB(rdenD

Bonde< Es una celda que contiene el dato a buscar $ango de #abla Es un cuadro de varias filas y columnas que contiene información de la cual se desea e#traer una en especial en base al dato que se está buscando! En esta tabla la primera columna debe contener la lista de datos a buscar! V de Columna :ndica de cuál de las columnas de la tabla será e#traída la información que al final sería la respuesta de la función! (rden Es un argumento opcional que puede valer 0 ó  C;also o HerdaderoD! )i es 0& la b4squeda del dato en la tabla se *ará en forma e#acta& y la tabla no tiene necesariamente que estar ordenada! )i es  Co se omite este argumentoD entonces la b4squeda será apro#imada pero la tabla deberá estar ordenada ascendentemente de acuerdo a la primera columna! Esta función se utiliza para e#traer información relacionada con un dato] pero primero tiene que encontrar el dato en la tabla y luego e#traer la información que se encuentra en una de las celdas a la derec*a del dato dentro de la tabla! ,ato

#ota.- 2a funci'n &US$+L; solo buscará verticalmente el dato en la primera columna de la tabla, y si no lo encuentra, ya no lo buscará en las demás columnas, y la respuesta de la funci'n sera el mensaje de error: P#+Q 5no encontrado6.

Para obtener el Cargo del em-leado en la 9oleta 1.- !n la (oja: &oleta de ago, en la celda &* escribir %orena. .- !n la celda &0 escribir la siguiente f'rmula:

@9MC$L93BME%,(MB2B0D !sto significa %ue la funci'n @SA/V leerá el nombre del empleado en la celda 4 , luego buscará en la primera columna del rango llamado S@ELDOS al nombre del empleado, y cuando lo encuentre traerá de la columna 2 el cargo del empleado, y esa será la respuesta de la funci'n. !l " al final de la funci'n significa %ue la bús%ueda del nombre del empleado se hará en forma e/acta, es decir si en dato es el nombre

Pág 56

Manual de Excel NIVEL AVANZADO Lorena , entonces en la tabla buscará e/actamente a 2orena, y no a 2orenita o a 2orelai o a 2ore.

Para obtener el Mueldo Q los di"erentes umentos 1.- !n la celda &A escribir la siguiente f'rmula:

@9MC$L9T3BME%,(MB3B0D #ote %ue la f'rmula para obtener el sueldo del empleado es id4ntica a la %ue se us' para el cargo, con la única diferencia %ue el P de columna es * en ve" de , ya %ue el sueldo se encuentra en la tercera columna de la tabla. !sto %uiere decir %ue para obtener los aumentos hay %ue copiar esta f'rmula y luego nada más cambiar el número de columna respectivamente según el aumento %ue se desee e/traer. +demás, si 4sta f'rmula se desea copiar hacia abajo entonces el primer argumento de la funci'n deberá escribirse &R*. .- !n las celdas &E hasta &11 copiar las siguientes f'rmulas:

@9MC$L9T3BME%,(MB4B0D @9MC$L9T3BME%,(MB5B0D @9MC$L9T3BME%,(MB6B0D @9MC$L9T3BME%,(MBB0D @9MC$L9T3BME%,(MB'B0D

(ara obtener el (ago 9otal 1.- !n la celda &1* escribir la funci'n:

@M96*911D

5a 6oleta de (ago quedaría entonces así como se ve en el cuadro adjunto! Y si se escribe el nombre de otro empleado en la celda 6J entonces las funciones 61)%"3H deberán traer al instante los datos de ese otro empleado! #ota.- Seguramente %ue a estas alturas usted ya se habrá preguntado M - 'ue pasa si %a- dos empleados con el mismo nombreN. ues, si as sucede, &US$+L; solo traera los datos del primer empleado con ese nombre e ignorara a los demás. or esta ra"'n, cuando se dise=e una tabla de bús%ueda como 4sta, a cada empleado habra %ue darle un c'digo, y estos c'digos deberan estar en la primera columna de la Jabla de sueldos y as buscar a los empleados por su c'digo y no por su nombre. 3e igual modo se obrara si la tabla fuese de $lientes, o de roductos, o de +lumnos, etc.

Pág 5

Manual de Excel NIVEL AVANZADO Para Lalidar el +ngreso de datos en la celda 93* 1.- !n la (oja1: Jabla de Sueldos, seleccionar el rango +:+1 y darle el nombre de rango !<2!+3S. .- !n la (oja: &oleta de ago, seleccionar la celda &* y llamar al menú 3atos7;alidaci'n. +parecerá la siguiente ventana de dialogo:

• !n la casilla =ermitir elegir la opci'n Lista. • !n la casilla Ori.en escribir  EM=LEADOS . • 2uego Aceptar .

5a 6oleta de (ago mostrará entonces en la celda 6J un botón de lista desplegable para elegir los nombres de los empleados y así no tener que escribirlos con el teclado!

%lic aquí

Pág 5'


CAPITULO 9 1. Li#%a# de Da%o#: ordena$ien%o  3#4ueda

a! Qu e una Lita de 4ato Es un grupo de varias columnas consecutivas de celdas ocupadas con datos en su interior& y donde cada columna tiene un tipo *omogneo de datos! (or ejemplo<

)! C/*o ordenar una lita de dato uando lo )otone de +erra*ienta 1na de las formas más fáciles de *acer que E#cel ordene una lista de datos como la del ejemplo anterior& es utilizando los botones de *erramientas orden ascendente< y orden descendente< ! ue se encuentran en la fic*a Batos!

(or ejemplo& para ordenar la lista ascendentemente por el =ombre de cada socio 1.- Seleccionar una celda de la columna &, como la celda &A por ejemplo.. .- (acer un clic en el bot'n de herramientas

Orden ascendente.

2a lista entonces se vera as:

Pág 5.

Manual de Excel NIVEL AVANZADO Ejercicio Nº 5: 6n%er#eccin de ran"o#  u#o de 6ND6EC/O

(ara entender mejor el manejo de listas de datos& vamos a llevar a cabo un nuevo ejercicio en el cual se tiene que llevar el control de los pedidos de varios productos lácteos! En una ?oja de cálculo se tiene una lista de (recios& y una lista de (edidos de varios productos lácteos! El cuadro de pedidos en realidad llega *asta la fila 00& o sea que e#isten 0 pedidos en total! (ero como se ve& aun falta calcular el monto de los pedidos& así como los precios de cada producto pedido] para lo cual primeramente vamos a escribir las fórmulas que nos ayuden a obtener estos datos faltantes!

Cmo obtener los Precios de cada Pedido*

(rimeramente vamos a dar nombres de rango a cada columna y a cada fila de la lista de precios! 3ealice usted entonces las siguientes acciones< 1.- Seleccionar el rango de la lista de precios &*:!A. .- !n la ficha ?ormulas , grupo Nombres de7inidos& elegir el comando rear desde la selecci9n y aparecerá la siguiente ventana de diálogo:

Pág 60

Manual de Excel NIVEL AVANZADO *.- ;erificar %ue est4n marcadas las casillas )ila superior y $olumna i"%uierda, y luego presionar !nter. .- 2uego en la celda (11 escribir la siguiente f'rmula:

BINDI/E0O:E!!; INDI/E0O:?!!; 5a función :=B:3E%9O se encarga de e#traer el nombre de rango que se encuentra escrito en la celda E para luego este se intersecta con el otro nombre de rango escrito en ;! El espacio en blanco escrito entre las dos funciones :=B:3E%9O es el que le ordena a E#cel que intersecte los rangos! El resultado es el precio que se encuentra entre la intersección de la fila ueso y la columna 5aive en la lista de precios de la partes superior cuyos rangos fueron previamente nombrados en el paso =^2! 0.- + continuaci'n copie esta f'rmula hacia abajo con un doble clic en el cuadro de relleno.

Para obtener el cálculo de los ontos* A.- !n la celda I11 escribir la formula %ue multipli%ue la cantidad por el precio:

BG!!C8!! E.- Seguidamente copie igualmente esta f'rmula con un doble clic en el cuadro de relleno.

La 5unci/n IN4IECTO 5a sinta#is de la función es la siguiente< @+,+$EC#($e"erenciaD

Bonde< /e7erencia

Es una coordenada de celda o un nombre de rango dentro del cual e#iste escrito otra referencia de celda o nombre de rango! Esta función le dice a E#cel que no debe utilizar la celda escrita en la referencia misma& sino el nombre de rango que está escrito dentro de la referencia!

Pág 61


c! "ane#o de Lita de 4ato (ara trabajar con listas de datos es muy 4til conocer algunas de las *erramientas más usadas de la fic*a ,atos y en la fic*a +nsertar] estas son< (rdenar Filtros Mubtotal #abla dinámica

Ejercicio Nº 7: +anejo de una Li#%a de !edido#

1na vez realizado el ejercicio anterior& la lista de pedidos estaría completa& pero si se desea analizar cuantos pedidos deben ser entregados mensualmente& o a que clientes& o que productos y cual vendedor atendió el pedido& entonces la labor es muy complicada debido a que la lista se encuentra totalmente desordenada! Heamos a*ora un mtodo más completo para ordenar listas de datos! d! El Co*ando 4atoROrdenar (rdenar la lista -ara )ue lo usen en lmacJn*

)uponga que el día de ma'ana *ay que realizar la entrega de todos los pedidos que corresponden al mes de "bril& así que podríamos imprimir una copia de esta lista de pedidos y entregarla al encargado del almacn para que vaya cargando el camión con toda la mercadería! (ero& como ya se *a visto& al estar desordenada la lista& sta *aría que el trabajo de e#traer las diferentes cajas de productos del almacn sea una labor algo difícil que llevaría quizás a errores de confusión de pedidos y se estaría entregando un pedido por otro! Entonces& lo que se va *a *acer es lo siguiente< (rimero ordenar la lista separando los pedidos por meses& es decir los de "br luego May y finalmente Run! Be este modo se tendría separada ya toda la relación de pedidos que corresponden al mes de "bril! 5uego en la misma lista tambin se ordenará para cada mes los productos por su Marca& o sea la 5ec*e& el ueso y el Yogurt por separado& y finalmente cada producto a su vez se ordenará seg4n las diferentes marcas& >loria& 5aive y =estle! (ara poder lograr este ordenamiento se procederá de la siguiente forma< 1.- rimero seleccionar una de las celdas de la lista de pedidos. 5&1 por ejemplo6. .- !n la ficha 3atos hacer clic en el comando rdenar, y aparecerá la siguiente ventana de diálogo:

Pág 62


*.- !legir las * columnas a ordenar y adicionalmente tambi4n el criterio de ordenaci'n. $lic en +ceptar, y entonces el cuadro de pedidos se vera as:

#ota:

Si el rango de la lista %ue se desea ordenar tiene los ttulos de las columnas en la primera fila:
(rdenar la lista -ara )ue lo use el )ue realiOa la entrega con el camin*

Pág 63

Manual de Excel NIVEL AVANZADO 1na vez cargados todas las cajas de productos en el camión& el conductor deberá realizar las entregas de los pedidos a cada uno de los clientes que se encuentran en diferentes distritos de la ciudad& los cuales están indicados en la lista& pero para esta ocasión& no están ordenados como se desearía! "*ora& vamos a realizar un nuevo ordenamiento& y para este caso se deberán ordenar los pedidos en base a los lugares de entrega y a los clientes& tal como se observa en la siguiente ventana de dialogo<

!ntonces el cuadro de pedidos será fácil de usar para ir de distrito en distrito y reali"ar ordenadamente la entrega según los clientes %ue haya en cada lugar. !l $uadro se vera como se muestra en la siguiente figura:

e! El Co*ando 4atoR5iltro Filtrar la lista -ara consultar ciertos -edidos*

)abemos que la lista en total era de 0 pedidos! (ero& sin importar cuantos e#istan en total& en cierta ocasión se desea saber cuántos de los pedidos que se entregaron en "bril& fueron de lec*e >loria! Y en otra oportunidad& se desearía saber cuántos pedidos en el distrito de )an Miguel fueron vendidos por el vendedor 3obles! Y así por el estilo& se pueden necesitar realizar diferentes consultas a la lista! Esto se puede lograr con la ayuda del comando BatosP;iltro] y se procede de la siguiente forma< Pág 64

Manual de Excel NIVEL AVANZADO 1.- Seleccionar una celda cual%uiera del $uadro de edidos. .- !n la ficha Datos *acer clic en el comando ?iltro , entonces el cuadro de pedidos se vera as:

bserve %ue al elegir el comando ?iltro , los ttulos en la fila 1B del cuadro ahora muestran unos botones de lista desplegable. !stos son los botones de filtro, %ue se van a utili"ar para reali"ar las consultas a la lista. + continuaci'n vamos a reali"ar diversas consultas sobre los pedidos de los clientes: %lic aquí

Cuántos -edidos del mes de bril son de %eche loriaW 1.- (acer un clic en el bot'n de filtro del Mes& desactivar la casilla C)eleccionar 9odoD y elegir la opci'n Abril . !sto hará %ue de la lista de FB pedidos solo %ueden visibles los %ue pertenecen al mes de +bril, los demás pedidos %uedarán ocultos. .- 2uego hacer un clic en el bot'n de filtro del =roducto y elegir la opci'n Lec%e. *.- )inalmente hacer un clic en el bot'n de filtro de la Marca y elegir la opci'n Gloria.

Pág 65


$omo se observa en la figura, e/isten entonces A pedidos %ue cumplen con la condici'n %ue se haba solicitado: 52eche Tloria para +bril6 #ota:

$uando se reali"a el filtrado de una lista, los encabe"ados de fila muestran los números de fila de color a"ul, as como tambi4n aparecen unos iconos de embudo en los botones de a%uellos ttulos en los cuales se ha reali"ado una elecci'n de filtro.

Para oler a mostrar la lista de -edidos com-leta 1.- (acer un clic en el comando 9orrar

%lic aquí

G la lista vuelve a mostrar las filas ocultas y los encabe"ados de fila ya no aparecen de color a"ul sino otra ve" de color negro.

Cuántos -edidos de $obles son de Uogurt -ara la tienda XongW 1.- !legir en los botones de filtro las opciones Lobles, Gogurt y Hong respectivamente.

Si además de obtener el filtro anterior se desea sumar la cantidad total de cajitas de yogurt de estos 0 pedidos, entonces se puede utili"ar el bot'n de herramientas Autosuma , pero lo %ue ocurre en realidad no es la escritura de la funci'n Suma, sino la funci'n Subtotales. bserve %ue en la figura está seleccionada la celda T1B1 en la cual aparece el número E1, %ue en realidad es el resultado de la f'rmula %ue está visible en la

Pág 66

Manual de Excel NIVEL AVANZADO barra de f'rmulas. !sta funci'n esta sumando el rango T11:T1BB, pero la funci'n solo considera las celdas %ue se encuentran visibles e ignora las celdas %ue se han ocultado por acci'n del filtro.

La 5unci/n 'U:TOTALE' Esta función posee dos argumentos y permite obtener no solo la suma de un rango de celdas& sino  diferentes cálculos< @M9#(#%EMV de o-eracinB $ango a calcularD

Bonde< V de o-eracin

es un n4mero del  al  que indica la operación a realizar<  (romedio 2 %ontar J %ontara  Má#imo  Mínimo I (roducto $ Besviación estándar K Besviación estándar de la población  )uma 0 Harianza  Harianza de la población

$ango a calcular Es un rango de celdas en el cual se *an ocultado ciertas filas por

acción de un filtro!

Para obtener los Mubtotales de la "ila 101 1.- Seleccionar la celda T1B1. .- (acer un clic en el bot'n de herramientas Autosuma. *.- resionar !nter. 2a f'rmula %ue se habrá escrito será la siguiente funci'n:

@M9#(#%EM.B11*100D

El numero . indica que se va a Mumar el rango indicado! .- 2uego seleccionar la celda I1B1. 0.- (acer un clic en el bot'n de herramientas Autosuma. A.- resionar !nter. 2a f'rmula en esta ocasi'n será:

@M9#(#%EM.B+11*+100D

Pág 6

Manual de Excel NIVEL AVANZADO E.- + continuaci'n copie con el cuadro de relleno el contenido de la celda T1B1 hacia la celda )1B1 %ue esta a su i"%uierda. .- 2uego seleccionando la celda )1B1, en la barra de f'rmulas modifi%ue la formula cambiando el número 5 por el número 4. $on esto, la funci'n contará la cantidad visible de celdas ocupadas en el rango, dando como resultado la cantidad de pedidos %ue e/isten en la lista filtrada. #ota:

Si estando escritas las funciones SU&JJ+2!S, se reali"ase un nuevo filtrado a la lista de pedidos, entonces estas funciones se recalcularán automáticamente.

Cuál de los tres clientes tiene la maQor cantidad de -edidos en brilW 1.- !legir el menú 3atos7rdenar y )iltrar7&orrar. .- )iltrar los pedidos de +bril. *.- )iltrar al cliente
Cuántos -edidos de Xong -ara bril tienen un monto entre 500 Q 1000 solesW 1.- (acer clic en el bot'n de filtro del titulo Monto y elegir ?iltros de n*mero?iltro =ersonalizado . .- !n la ventana de dialogo elegir las opciones %ue se ven en la figura y luego hacer clic en +ceptar.

2a lista deberá dar los siguientes resultados:

Pág 6'


?asta a*ora& *emos podido ver como los filtros nos permiten obtener reportes depurados en base a diferentes criterios! )in embargo& por ejemplo en el caso de querer averiguar cuanto fue el acumulado vendido por cada uno de los vendedores de un determinado producto] los filtros y la función )ubtotales si nos dan esa respuesta& pero] sta solución no nos permite comparar simultáneamente las ventas de todos los vendedores al mismo tiempo& sino que los va mostrando uno por uno a medida que cada uno de estos vendedores es filtrado! O sea que si queremos averiguar quin es nuestro mejor vendedor del mes& lo que *abría que *acer es filtrar las ventas del primer vendedor y anotar el monto de sus ventas obtenido por la función )ubtotales al final de la lista& luego filtrar al segundo vendedor y anotar tambin su resultado y así sucesivamente *asta tener los resultados de todos los vendedores calculados! 6ueno& esto no es muy difícil de *acer si solamente son tres vendedores como en nuestra lista de ejemplo& pero] imagínense que tenemos & o 00& o talvez muc*os más vendedores] en este caso& averiguar quien es el mejor vendedor se convertiría en una tarea muy ardua y tediosa! (ara poder comparar varios resultados de subtotales al mismo tiempo e#iste otra *erramienta en el manejo de listas] esta es la opción del men4 BatosP)ubtotales que veremos a continuación! (ero antes vamos a retirar de la lista los botones de filtro y las formulas de )ubtotales de la fila 0! 1.- Seleccionar todas las f'rmulas de Subtotales %ue se encuentran en la fila 1B1 y b'rrelas con la tecla Suprimir. .- !n la ficha Datos elegir el comando ?iltro. 3esaparecerán los botones de +utofiltro de nuestra lista de pedidos y la lista se verá entonces como en su forma original, y estarán nuevamente visibles los FB pedidos.

f! El *en- 4atoR'u)totale Para aeriguar )uiJn es el mejor de nuestros Lendedores 1.- Seleccione una de las celdas con el nombre de un vendedor , y haga clic en el bot'n de herramientas rden ascendente. !sto hará %ue los pedidos se ordenen alfab4ticamente por vendedor .- !n la ficha Datos elegir el comando Subtotal

Pág 6.

Manual de Excel NIVEL AVANZADO %lic aquí

+parecerá entonces la siguiente ventana de diálogo:

Elegir Hendedor Elegir )uma

Herificar que se encuentren marcadas con un c*ec7 las opciones %antidad y Monto!

.- !legir en la ventana las opciones se=aladas y luego clic en +ceptar. !n la lista de pedidos inmediatamente aparecerán subtotales al final de cada vendedor, y a la i"%uierda de la hoja de calculo aparecerán tambi4n los botones de +grupar y !s%uema. 6otones de "grupar y Esquema

*.- (acer un clic en el bot'n número  de +grupar y es%uema %ue se encuentra a la i"%uierda de los encabe"ados de columna. !sto hará %ue desapare"can la relaci'n de pedidos de la lista y solamente %ueden visibles los subtotales por cada vendedor.

Pág 0

Manual de Excel NIVEL AVANZADO .- )inalmente seleccionar el monto total de uno de los vendedores y hacer un clic en el bot'n de herramientas rden descendente. !ntonces los subtotales de los vendedores de ordenarán de mayor a menor, con lo cual tendremos una lista de vendedores ordenada según su record de ventas. $on esto sabremos no solamente %uien es el mejor vendedor sino tambi4n %uien es el %ue menos ha vendido.

Para aeriguar )uiJn es el mejor de nuestros Clientes 1.- !n la ficha DatosSubtotales hacer clic en el bot'n 7uitar todos8 !sto hará %ue desapare"can los subtotales de los vendedores. 2uego se repiten los mismos pasos %ue reali"amos para averiguar %uien era el mejor vendedor, pero ahora tomamos en cuenta la columna de $lientes. .- Seleccione una de las celdas con el nombre de un liente , y haga clic en el bot'n de herramientas rden ascendente. *.- !n la ficha DatosSubtotales& elegir las opciones %ue se muestran en la ventana de diálogo adjunta, y luego clic en el bot'n >+ceptar? .- + continuaci'n clic en el bot'n 2$

1 2 3 0.- )inalmente seleccionar el monto total de uno de los $lientes y hacer un clic en el bot'n de rden descendente.

Be este modo se podrá averiguar en forma similar& cuál es la marca más vendida& o cual es el mejor mes de venta& o tambin cual es el producto que más ingresos nos brinda!

,! El *en- 4atoRInfor*e de ta)la  ,ráfico diná*ico

%uando se tiene listas grandes de datos& la manera más practica y potente de analizar esta lista es *aciendo uso de las llamadas 9ablas Binámicas] y a pesar de lo potente que es esta *erramienta& su creación es e#tremadamente fácil! "ntes de crear una tabla dinámica& vamos a retirar todos los subtotales anteriormente calculados!

Pág 1

Manual de Excel NIVEL AVANZADO 1.- !n la ficha 3atos elegir el comando Subtotales y hacer clic en el bot'n 7uitar todos8

Como crear una #abla ,inámica 1.- Seleccionar una de las celdas de la lista de pedidos. . !n la ficha Insertar elegir el comando 0abla din+mica. !sto hará %ue apare"ca la siguiente ventana de diálogo para ayudarnos a crear la tabla dinámica:

*.- ;erificar la fuente de datos %ue se desea anali"ar y la ubicaci'n del informe %ue se desea obtener. .- 2uego hacer clic en el bot'n >+ceptar? 0.- Se creará una nueva (oja en el libro actual, y aparecerá entonces el panel de tareas para permitirnos dise=ar %ue elementos va ha tener la tabla dinámica.

(anel de 9areas para dise'ar la 9abla dinámica

Pág 2

Manual de Excel NIVEL AVANZADO + la i"%uierda de esta ventana se observará un es%uema de c'mo se verá la tabla dinámica y %ue elementos deberá tener en su interior. ara ello se deberá arrastrar los campos %ue se encuentran en el panel de tareas de la ventana y %ue tienen los nombres de las diferentes columnas de la lista de pedidos. !stos campos se pueden ubicar en las áreas de ?iltro de in7orme , de /9tulos de olumna , de /9tulos de ?ila o de Valores. +un%ue los nombres de campo se pueden ubicar en cual%uiera de estás áreas, e/isten ciertos criterios recomendables para %ue las tablas dinámicas muestren resultados coherentes. !stos criterios son los siguientes: !n las áreas de ?iltro de in7orme , /9tulos de ?ila o /9tulos de olumna , se deben arrastrar los campos cuyo contenido son del tipo te/to 5por ejemplo los campos $lientes,
numrico Cpor ejemplo los campos %antidad& (recio& MontoD ya que en esta área se realizarán cálculos matemáticos con los datos!

.- bserve la figura y arrastre los nombres de campo tal como se muestra.

F.- +l terminar de dise=ar la tabla dinámica haga un clic derecho en el menú conte/tual elegir Opciones de tabla din+micaF

Pág 3

Manual de Excel NIVEL AVANZADO 1B.-!n la ventana de dialogo %ue aparecerá, elegir la ficha Mostrar y activar la casilla: Diseo de tabla din+mica cl+sica :permitir arrastrar campos en la

cuadrHcula;& y luego clic en "ceptar!

%lic aquí

!sto hará %ue la tabla dinámica se muestre los nombres de campo en los r'tulos de )ila y columna, y además permitirá %ue se puedan arrastrar estos campos para cambiar el dise=o de la tabla tan igual como se poda hacer en las versiones anteriores de !/cel.

Como "iltrar la #abla ,inámica con los cam-os de -ágina $omo puede verse, la tabla muestra un resumen de los pedidos separados por ;endedor en cada fila y por
Pág 4

Manual de Excel Excel NIVEL AVANZADO +dicionalmente, en la parte superior de la tabla aparecen los campos roducto, 2ugar y
.- Seguidamente haga clic en el bot'n del filtro =roducto& elija Lec%e .y luego haga clic en +ceptar. +ceptar. 2a tabla entonces solo mostrará los resultados de los pedidos de 2eche en el mes de +bril, y como puede verse los pedidos más grandes fueron de la marca Tloria con 1BF* unidades:

Como "iltrar la #abla ,inámica con los cam-os de Fila o Columna Jambi4n Jambi4n se pueden reali"ar filtros en los campos de )ila o de columna.

Pág 5

Manual de Excel Excel NIVEL AVANZADO + diferencia de los filtros de página, en esta ocasi'n e/isten casilleros donde se marcan con un chec los elementos %ue permanecerán visibles vis ibles y hay %ue %uitar el chec en los elementos %ue se desean ocultar. !sta clase de filtros sera útil si es %ue deseamos %ue la tabla anterior oculte los pedidos de la marca 2aive y deje visible los pedidos de las marcas Tloria y #estle. ara lograrlo realice realice entonces lo siguiente: 1.- (aga clic en el bot'n del filtro Marca y %uite el chec en la marca 2aive.

.- 2uego haga clic en +ceptar. !l resultado es el siguiente: la tabla se reduce a solo dos columnas para las marcas de leche, y además la última columna de Jotales, ya no suma los pedidos de la marca marca 2aive.

ara %ue las columnas %ue se ocultan con este filtro vuelvan a ser s er visibles hay %ue hacer clic en el bot'n de filtro y elegir la opci'n orrar 7iltro de de #Marca) .

Pág 6


Para agregar un nueo cam-o a la tabla dinámica 2a tabla dinámica no tiene el campo 2ugar, para traerlo basta con arrastrar desde el panel de tareas el campo 2ugar hacia el área )iltro de informe, o directamente hacia la tabla dinámica %ue se encuentra en la hoja de cálculo. 5ver figura6

#ota:

#o se podrá arrastrar un nuevo campo de la barra de tareas directamente hacia la tabla dinámica si no se encuentra activada la opci'n Diseo de tabla din+mica cl+sica :permite arrastrar campos en la cuadrHcula; %ue se encuentra en la ficha Mostrar de la ventana de diálogo Opciones de tabla din+mica .

Como intercambiar los cam-os de la #abla ,inámica +hora vamos a ver como hay %ue hacer para averiguar Vcuál es e s el 2ugar 5distrito6 donde se han hecho los mayores pedidos de leche en +brilW bserve en la tabla anterior %ue si el campo
Pág 

Manual de Excel NIVEL AVANZADO 2a tabla entonces, mostrará los totales por 2ugar:

Los campos "arca y Lu,ar an intercambiado su ubicaci#n

!n el Jotal general observamos %ue 2ima tiene los mayores pedidos de leche en +bril, en cambio $horrillos es el distrito %ue menos leche ha pedido. #ota:

Si se va ha cambiar de sitio un campo %ue se encuentre en el área de $olumna o de )ila dentro de la tabla dinámica hacia el área de ágina, es importante %ue previamente ese campo no se encuentre filtrado, o sea %ue debe estar marcada la opci'n 5 Seleccionar todo 6. 3e no hacerlo as, los elementos %ue se haban ocultado %uitándoles el chec, ya no se encontrarán disponibles cuando se %uiera filtrar este campo en el área de ágina.

Como "ormar gru-os de "ilas o columnas en la #abla ,inámica Si 2ima y &re=a son distritos de la "ona $entro, y $horrillos y San
Xona $entro 

 &re=a

 2ima

Xona este 

 $horrillos

 San
ara esto se deben formar los dos grupos siguiendo los siguientes pasos: 1.- Jal y como se encuentra la tabla dinámica anteriormente, seleccione los nombres de los distritos de 2ima y &re=a 5clic en 2ima y $trl8clic en &re=a6 .- 2uego en la cinta de Opciones de 8erramientas de tabla dinámica haga clic en el comando A.rupar selecci9n! *.- + continuaci'n seleccionar tambi4n $horrillos y San
Pág '

Manual de Excel NIVEL AVANZADO %lic aquí

(eleccione los pares de distritos que desea agrupar

.- !n ve" de Trupo1 escriba la palabra entro 5celda &E6 y en Trupo escriba la palabra 8este 5celda 3E6. 0.- )inalmente haga doble clic sobre el titulo $entro y tambi4n sobre el titulo este, o un clic en los botones 87- para mostros u ocultar los elementos de cada grupo. 2a tabla dinámica entonces mostrará los resultados agrupados por "onas $entro y este:

Encabe!ados de campo

7otones O< de grupo

Pág .

Manual de Excel NIVEL AVANZADO $omo ven, agrupar es sencillo. G si en ve" de tener los distritos en columnas tuvi4ramos los 1 meses de pedidos, entonces podramos haber agrupado los meses por trimestres o por semestres, según como convenga. #ota:

Una ve" creados los grupos, en la cinta pciones puede usar los comandos &otones 87- y !ncabe"ados de campo para ocultar o mostrar los botones de grupo y los encabe"ados de los campos.

0.- ara deshacer un grupo haga clic sobre el titulo del grupo 5$entro u este6 y elija el comando Desa.rupar de la cinta de Opciones , o haga un clic derecho en el ttulo del grupo y elija la opci'n Desa.rupar$

Para armar mlti-les nieles en "ilas o columnas de la #abla ,inámica 1.- Una ve" %ue ha desagrupado los distritos intercambie la posici'n de los campos 2ugar y roducto.

.- +rrastre el campo
!n el área de )ila de la tabla dinámica se verán dos niveles de campo: los nombres de los vendedores y de cada vendedor sus pedidos mensuales.

Pág '0


3e este modo se pueden armar varios niveles de campo tanto en la )ila, como en la $olumna de la tabla dinámica

Para etraer los registros )ue acumulan un alor en la #abla ,inámica Si se desea averiguar por ejemplo, cuáles y cuántos son los pedidos de Yueso %ue fueron vendidos por +lvarado en el mes de
Pág '1

Manual de Excel NIVEL AVANZADO !sto hará aparecer una nueva hoja en el 2ibro actual, y en ella se e/traerán todos los pedidos %ue totali"an ese valor se=alado en la tabla dinámica. (eleccione la celda 62 y aga un clic en Autosuma

Etiqueta de *oja

$omo se aprecia en la imagen adjunta, la (oja del libro muestra la relaci'n de los 0 pedidos %ue se deseaban e/traer. G si en la celda TE se calcula la suma de la columna 5usar +utosuma6 se verifica %ue efectivamente EFA fue la cantidad total pedida según mostraba as tambi4n la tabla dinámica %ue se encuentra en la (oja1.

#ota:

or cada ve" %ue haga doble clic en una celda de resultados de la tabla dinámica se insertará una nueva hoja mostrando el detalle de donde proviene ese valor. Si de este modo el libro se va llenando de hojas adicionales, y si no le interesa conservar estas hojas, antes de guardar el archivo elimine las hojas adicionales de este libro, de otro modo el libro ocupar mucho espacio innecesariamente. (aga clic derecho en la eti%ueta de hoja y elija la opci'n !liminar.

Para ctualiOar resultados en la #abla ,inámica $omo en las celdas de la tabla dinámica no e/isten formulas, entoncesD los resultados de la tabla dinámica no se recalcularán automáticamente cuando los datos del cuadro de edidos sean modificados. !l recálculo en la tabla deberá hacerse entonces manualmente, y para esto hay %ue hacer clic en el bot'n de comandos +ctuali"ar, o en el menú conte/tual de la tabla elegir la opci'n +ctuali"ar. #ota:

Siempre %ue vaya a consultar los resultados de una tabla dinámica, primeramente actualice la tabla para cerciorarse de %ue los datos en ella son los correctos.

Para crear un ra"ico ,inámico !stando seleccionada una celda de la tabla dinámica, basta con hacer un clic en el bot'n de comando Gr+7ico din+mico , luego elegir el

Pág '2

Manual de Excel NIVEL AVANZADO modelo de grafico %ue desee y al +ceptar aparecerá la siguiente imagen en la pantalla:

%lic aquí

2a diferencia con un grafico convencional, es %ue este posee un anel de filtros del gráfico dinámico. G si por ejemplo filtramos a los vendedores para %ue solo se muestre la grafica de ventas de +lvarado y Lobles, entonces la grafica se vera as:

#ota:

$omo el gráfico dinámico depende de los datos e/istentes en la tabla dinámica, al filtrar el gráfico, tambi4n se estará filtrando la tabla dinámica.

Pág '3


CAPITULO ; 1. Con#olidado de cuadro#

a! Cuando conolidar cuadro %uando se tenga listas de datos con información semejante una de otra& pero escrita en cuadros diferentes ya sea en la misma *oja o en *ojas distintas& entonces se puede *acer uso del men4 BatosP%onsolidar! Esta opción se utiliza para obtener diversos tipos de cálculo estadístico Csuma& promedio& má#imo& mínimo& varianza& etc!D en base a la información guardada en todos estos cuadros! )! Co*o conolidar cuadro Ejercicio Nº 8: Con#olidar lo# "a#%o# de 9aria# &er#ona# 1.- !n una hoja de cálculo en blanco escriba la siguiente lista de gastos correspondientes al mes de !nero .- (aga doble clic en la eti%ueta de la (oja1, escriba Enero y presione Enter! *.- Tuarde el archivo con el nombre onsolidar Gastos .

.- btenga una copia de la hoja !nero y cambie el nombre de la eti%ueta de hoja escribiendo en ella ?ebrero . #ota:

ara copiar una hoja arrastre la eti%ueta de la hoja manteniendo presionada la tecla de $trl. +l copiar de este modo el contenido de la copia es id4ntica a la hoja original en datos, formato, ancho de columnas, formulas, etc.

0.- !n la hoja ?ebrero modifi%ue los datos del cuadro tal como se muestra a continuaci'n: :note 'ue el orden de los rubros - las personas es distinto

aun'ue los nombres son i.uales;

Pág '4


A.- Seguidamente cambie el color de fuente y relleno de las celdas en el cuadro para %ue sea diferente al anterior. E.- btenga ahora una copia de la hoja ?ebrero y cambie el nombre de la eti%ueta de hoja escribiendo en ella Marzo .- 2uego modifi%ue los datos del cuadro de la hoja Surco tal como se muestra a continuaci'n: :en esta ocasi9n se %a a.re.ado el rubro Arbitrios - se %a

incluido a Elena como una cuarta persona m+s en la lista;

F.- Sa%ue una última copia, de la hoja
Pág '5


5o que se quiere en este 4ltimo cuadro es calcular el acumulado total de todos los gastos de las  personas durante estos J meses! O sea& se quiere obtener un consolidado de varios cuadros! #ota:

$uando se van a consolidar varios cuadros, se debe tener especial cuidado de %ue los nombres de los r'tulos de fila y columna sean escritos en id4ntica forma en todos los cuadros. 3e otra forma !/cel no podrá identificar %ue rubros son los %ue debe consolidar ni de %u4 persona se trata, ya %ue se basa en estos nombres para llevar a cabo esta tarea.

1na vez que ya se tienen las listas de gastos redactadas& se deben seguir los siguientes pasos para consolidarlas< 1.- 3ar nombre de rango a cada uno de los cuadros %ue se desean consolidar: !n la hoja !nero



al rango A4>D!2 darle el nombre ENE/O



!n la hoja )ebrero al rango A4>D!2 darle el nombre ?E/E/O  !n la hoja E!4 darle el nombre MA/ZO .- !n la hoja Jotal, seleccionar el rango A4>E!4 y luego en la ficha Datos *acer clic en el comando onsolidar :

*.- !n la ventana de datos onsolidar escriba y elija las opciones %ue se detallan en la figura adjunta, luego presione Aceptar . Herifique que estn marcadas ambas casillas

Pág '6


Elija la función )uma

Escriba los nombres de rango y elija gregar

Marque estas 2 casillas

2a siguiente ventana muestra el resultado consolidado de los tres cuadros de compras. ero aun faltará %ue le agregue un total general y adicione una columna con las unidades utili"adas en cada artculoD para ello haga usted los siguiente:. .- !n la celda )* escriba el ttulo 0otales y luego utili"ando Autosuma obtenga la suma de cada fila del cuadro. 0.- !n la celda T* escriba el titulo @nid y escriba a continuaci'n las unidades %ue corresponden a cada tipo de articulo. A.- )inalmente dele formato a estas dos nuevas columnas tal como se muestra a continuaci'n:

Pág '


!n este cuadro de totales, las celdas de las columnas &, $, 3 y ! no contienen ninguna f'rmula %ue se encargue de sumar las compras de cada oficina. 2a orden 3atos7$onsolidar se encarga de sumar los tres cuadros y mostrar en el cuadro consolidado solamente los resultados. !sto %uiere decir %ue si luego de haber obtenido el consolidado, se modifican las cantidades o se aumentan o disminuyen artculos a estos, entonces, el cuadro de totales no se enterara de estos cambios, y para actuali"arlo habra %ue volver a consolidar los cuadros. ero en este caso es ya más sencillo ya %ue !/cel recuerda cuales son los cuadros a consolidar y solamente hay %ue llamar al menú datos7$onsolidar y de frente elegir +ceptar. ara el caso de %ue los cuadros de compras sufran a menudo muchos cambios y esto haga necesario volver a consolidarlos una y otra ve", entonces %ui"ás lo más recomendable es %ue al llamar al menú datos7$onsolidar, en la ventana de diálogo se mar%ue la casilla de opci'n M$rear vnculos con los datos de origenN, esto hará %ue el cuadro total de consolidado se actualice automáticamente apenas uno de los cuadros de compra de las oficinas sufra un cambio. ero hay %ue tener en cuenta %ue esto hara %ue la hoja Jotales en realidad tenga un cuadro consolidado mucho más grande, y en 4l se habran creado celdas ocultas y formulas con vnculos e/ternos hacia las demás hojas de compras.

. ;ráfico# en la Hoja de Cálculo

5os gráficos son el mtodo más 4til y practico para analizar de manera mas objetiva los resultados numricos que pueden obtenerse en nuestros cuadros! "demás que en E#cel la creación de los gráficos es una labor muy sencilla y sumamente rápida! Ejercicio Nº : ;raficar un Cuadro de !roduccin 1.- !n una hoja de cálculo en blanco escriba el siguiente $uadro de roducci'n:

a! Uo del Aitente $ara Gráfico .- onga el bot'n de Xoom al E0O.

Pág ''

Manual de Excel NIVEL AVANZADO *.- Seleccione el rango A4>D6 en el cuadro de producci'n. .- (aga un clic en el bot'n de herramientas

M+sistente para gráficosN

+parecerá en la pantalla la ventana de diálogo %ue contiene el asistente para gráficos, el cual nos ayudará a crear un gráfico siguiendo  pasos:

Primer Paso* 0.- !legir a%u el tipo de grafico %ue se desea crear A.- Seleccione el tipo de gráfico de columnas, y el subtipo $olumna agrupada con efecto *3. E.- (aga clic en el bot'n Siguiente para ir al do paso.

Megundo Paso* .- ;erificar el rango de celdas %ue contiene los datos %ue se desean graficar F.- Seleccione el modo en %ue se desean mostrar las series de datos 5por filas o por columnas, haga clic en ellos para ver la diferencia6 1B.-(aga clic en el bot'n Siguiente para ir al *er paso.

#ercer Paso* +%u aparece una ventana %ue contiene varias fichas para elegir las opciones del grafico.

Pág '.

Manual de Excel NIVEL AVANZADO 11.-!n la ficha 0Htulos , escribir el ttulo %ue se desea dar al grafico. !n nuestro caso el titulo es: L3U$$I# !# ;!L+#. 1.-!n la ficha Le-enda , %uite la marca a la casilla Mostrar le-enda . 1*.-!n la ficha 0abla de datos , mar%ue la casilla Mostrar tabla de datos y Mostrar claves de le-enda . 1.- 2uego haga clic en el bot'n Siguiente para ir al to paso.

Cuarto Paso* 10.-!legir ubicar el gráfico como un objeto dentro de la (oja1, y luego clic en el bot'n )inali"ar. $on esto el grafico aparecerá en la hoja actual de la siguiente forma:

PO4UCCION EN 6EANO &%%

Tonelada @% % A0089 4A/9 A*'

E3E

*E7

4A0

=@ D%

&%% @%

%$2%

;% P% D%

1A.-+comode el gráfico arrastrando sus bordes para ubicarlo debajo del cuadro de producci'n como se observa en la figura:

)! 5or*ato de Gráfico "unque el gráfico está ya creado quizás se desee aun mejorar su apariencia cambiando el formato de cada uno de sus elementos! Esto es

Pág .0

Manual de Excel NIVEL AVANZADO posible& y solo basta con seleccionar cualquier elemento del gráfico 5Jitulo, fondo, columnas, escalas, etc.6 y cambiar su aspecto con el men4 ?ormato o con los botones de *erramientas de formato! )iga usted las siguientes instrucciones< 1E.-Seleccione el fondo gris detrás de las columnas del gráfico haciendo un clic all en ese fondo. 1.-$on el bot'n de herramientas olor de relleno elija la opci'n E7ecto de relleno , luego en la ficha De.radado elija 3os colores y seleccione los colores amarillo y naranja, a continuaci'n elija ponerlos en diagonal y finalmente +ceptar. 1F.-+hora seleccione el fondo blanco del gráfico haciendo un clic all, y luego llame al menú ?ormatoJrea del .r+7ico seleccionado . B.-!n la ventana M ?ormato del +rea del .r+7icoN y dentro de la ficha 0ramas , marcar con un chec las opciones Sombreado , y Es'uinas redondeadas. 2uego hacer clic en el bot'n !fectos de relleno y elegir en la ficha Je/tura uno de los modelos %ue all se muestran, y +ceptar dos veces para salir de la ventana de diálogo. 1.-Seleccione el ttulo del grafico y p'ngalo #egrita, luego cambie el color de letra según sea el fondo antes elegido. .-Seleccione el subttulo Joneladas, y con el menú ?ormato0itulo del e(e y seleccionado , ingrese a la ficha Alineaci9n gire el titulo FB grados, luego +ceptar. $on esto el grafico deberá verse de la siguiente forma:

PO4UCCION EN 6EANO  a d a l e n o T

&%%

@%

%

E3E

*E7

4A0

A0089

=@

&%%

;%

4A/9

D%

@%

%

A*'

%$2%

D%

Pág .1

Manual de Excel NIVEL AVANZADO c! Co$iar  *odificar Gráfico )e puede copiar un gráfico tan igual como lo *aríamos con cualquier otro objeto& es decir arrastrándolo con el Mouse mientras se presiona %trl& o usando las órdenes de %opiar y luego (egar! %uando convendría sacar copia a un gráficoF! Heamos pues] el gráfico anterior muestra la producción de los tres primeros meses! )i a*ora se desea graficar los tres meses siguientes& no es necesario volver a crear un nuevo gráfico& sino que simplemente se saca una copia al gráfico anterior y luego en la copia se modifica la definición del rango de celdas a graficar!

1.- Seleccione el gráfico y presionando $trl arrástrelo hacia un lado para obtener una copia. bserve %ue al estar seleccionado el nuevo gráfico, tambi4n se selecciona con un borde de color, el rango de celdas del cual depende este gráfico 5+*:+A y &*:3A6. !ntoncesD para cambiar este rango a graficar hay %ue hacer lo siguiente:

Pág .2

Manual de Excel NIVEL AVANZADO .- 2lamar al menú Gr+7icoDatos de ori.en , y al aparecer la ventana de diálogo, seleccione con el A6 y E4>G6& tal como se aprecia en la figura adjuntaD luego elija +ceptar.

*.- Seleccionar el ttulo del gráfico y modificar el te/to para %ue diga L3U$$I# 3! JZ. !l gráfico entonces, mostrará la producci'n de los meses de +bril a Cunio. 3e este modo podra obtener rápidamente la grafica de los  trimestres del a=o a partir del primer grafico creado. $abe se=alar %ue aun%ue al obtener la copia, 4sta inicialmente está encima y tapando al primer gráfico, pero, al finali"ar las modificaciones %ue se re%uieran dar a la copia, posteriormente 4sta se podrá mover a su posici'n definitiva en la parte inferior o al costado del primer gráfico según como se desee. 2os cambios %ue se pueden reali"ar a los gráficos son muy variados. !n el menú Gr+7ico se encuentran las opciones e%uivalentes a los  pasos %ue se siguieron para la creaci'n del gráfico inicial, as %ue si se desea se pueden cambiar estas especificaciones en cual%uier momento. +demás, si se selecciona cual%uier elemento o parte del gráfico, se puede llamar al menú )ormato y a continuaci'n hay la posibilidad de abrir all una ventana de formato con una gran variedad de opciones para reali"ar diversos cambios sobre el elemento seleccionado del gráfico.

Pág .3

Manual de Excel NIVEL AVANZADO or ejemplo: 1.- Seleccionar el gráfico L3U$$I# !# JZ. .- 2lamar al menú Gr+7ico0ipo de .r+7ico y elegir el tipo 9nico , y subtipo olumna 4D en 7orma c9nica .

PO4UCCION EN OTOSO

&%% d a l e n o  T a

@%

A*'

A0089

%

E3E

*E7

4A0

A0089

=@

&%%

;%

4A/9

D%

@%

P%

A*'

%$2%

D%

*.- 2uego haga clic sobre uno de los conos de +rro" y llame al menú ?ormatoSerie de datos seleccionada , y en la ventana de diálogo seleccionando la ficha Opciones cambie el valor de las opciones =ro7undidad del ran.o y Anc%o de ran.o& de !," a ,". y +ceptar. .- Seguidamente cambie a voluntad, el color del fondo del grafico, el color y tama=o de las letras, color de lneas de la tabla de datosD efecto de relleno y trama para cambiar la apariencia de los conos, etc. )inalmente el grafico podra verse como se muestra a continuaci'n:

PO4UCCION EN OTOSO 100  a d a l e n o T

;0 0

CA5T "AI= AO=

A70

4AQ

F53

A0089

&D

;&

=2

4A/9

D%

2;

;&

A*'

D%

P;

D;

Pág .4

Manual de Excel NIVEL AVANZADO rá"ico de Mectores Circulares* tro tipo de gráfico muy utili"ado es el gráfico de sectores circulares, !ste gráfico se emplea cuando se %uiere mostrar los datos en forma porcentual. ;eamos el siguiente caso para graficar la producci'n en Cunio: 1.- Seleccionar los rangos A4>A6 y G4>G6 . 2uego haga clic en el bot'n +sistente para gráficos. .- Seleccionar el tipo de grafico ircular , y subtipo ircular con e7ecto 4D . *.- 2uego avance hasta el tercer paso con el bot'n Siguiente .- !n la ficha Jitulo escriba L3U$$I# !# CU#I 0.- !n la ficha 2eyenda, desactive
odi"icar estos dos alores

Pág .5


!l gráfico tendrá entonces la siguiente apariencia:

PO4UCCION EN &UNIO

A0089, =2, $$C

A*', D;, @;C 4A/9, ;&, $;C

.- 2uego, seleccione con el
AO=B 8>B 22

CA5B @9B ;9

"AI=B 91B 29

Pág .6


rá"ico de %neas* !ste tipo de gráfico tambi4n es muy común de usar. ara esta ocasi'n vamos a graficar como lneas la variaci'n %ue sufre la producci'n de arro" durante todo el a=o. 1.- Seleccionar los rangos A4>M3. 2uego haga clic en el bot'n +sistente para gráficos. .- Seleccionar el tipo de grafico LHneas& y subtipo LHneas con marcadores en cada valor de datos$ 2uego continúe con el resto de pasos como en los casos anteriores. Si luego de creado el grafico, además le agrega formato, entonces el grafico podra verse as:

120 100 ?0 <0 90 20 0 EE FE9 $ 9$ U K K% ( MEP ( C # ( L

,+C

rá"ica de Ecuaciones en grá"icos de ,is-ersin U* 1.- !n una hoja de cálculo en blanco escribir en la columna + números correlativos desde el -1B hasta el 1B. 5ver figura adjunta6 .- !n la celda &0 escribir la siguiente f'rmula:

B,K21,$ *.- 2uego copiar esta f'rmula hacia abajo con doble clic en el cuadro de relleno. .- Seleccione el rango de celdas +:&0 y cree un gráfico del tipo :<; Dispersi9n , y Subtipo Dispersi9n con puntos de datos conectados por lHneas$

Pág .


0.- Una ve" creado el gráfico d4le formato a fin de %ue se vea como en la figura siguiente:

d! Co*)inar Gráfico con 4i)u#o "l crear gráficos en E#cel& a veces uno desea que el gráfico muestre más elementos de los que ya tiene& pero con las opciones del men4 gráfico o con las de formato no es posible lograr lo que se desea! Es estos casos quizás la alternativa sea el combinar el gráfico con elementos de dibujo los cuales se pueden dise'ar con

Pág .'

Manual de Excel NIVEL AVANZADO ayuda de las *erramientas de dibujo que se encuentran en la parte inferior de la ventana de E#cel! Ejercicio Nº <: Calculo de la =%ilidad  !un%o de E4uilirio

1na peque'a empresa produce un cierto artículo] y sus costos& así como su plan de producción y ventas mensuales son los siguientes< >astos ;ijos< 00 soles >astos Hariables< JK solesPunidad Cmaterial y mano de obraD (roducción< $ unidades  solesPunidad (recio de venta< 1nidades vendidas< K0U de la producción )e desea averiguar cuál es la utilidad de la empresa& y tambin calcular y graficar el punto de equilibrio! (ara lograr esto realice los siguientes pasos< 1.- !n una hoja de cálculo nueva ingrese los siguientes datos:

Pág ..

Manual de Excel NIVEL AVANZADO .- 2uego ingrese las siguientes f'rmulas en las celdas:

En ,6* En C11* En ,13* En E1* En E23* En 91.* !n 22> !n D22:

@,3&,6 En E'* @911=C11 En ,11* En E15* @,11/1&C13D @C6 En 923*

@96=C6 @C6='0; @,11:E15 @E15:E' @E15

@$E,(,E$ !M,3=C6D/E15:,6DB0D

BD4

luego copiar esta formula en 24

B6C22122luego copiar esta formula en D24

+ continuaci'n darle formatos a las celdas según el tipo de datos %ue representan y entonces la hoja deberá verse as:

rá"ica del Punto de E)uilibrio* *.- Seleccione el rango de celdas &1:!*

Pág 100

Manual de Excel NIVEL AVANZADO .- (aga clic en el bot'n +sistente para gráficos y en el !er =aso elija el tipo :<; Dispersi9n , y Subtipo Dispersi9n con puntos de datos conectados por lHneas$ 0.- !n la ventana del 2do =aso elegir Series en: olumnas. A.- !n la ventana del 4er =aso escribir como titulo del gráfico U#J 3! !YUI2I&LI, y elegir ubicar las 2eyendas: Aba(o. E.- 2uego clic en el bot'n )inali"ar. .- Seguidamente acomode el tama=o del gráfico para %ue se ubi%ue sobre el rango de celdas )1:[*. F.- Seleccione el eje vertical las &B,%%% de PUNTO 4E EQUILI:IO monedas, d4le el formato de !stilo &;,%%% millares y disminuya &$,%%% los decimales a cero conelbot'nde &%,%%% herramientas: ,%%% 1B.-2uego

aumente

el

tama=o de la leyenda, y tama=o de letra a 1

puntos.

B,%%% ;,%%% $,%%%

11.-Seleccione el fondo gris

y elija la opci'n sin relleno



%

@%

Gato 5i#o

&%%

Gato 5i#o V 6aria)le

&@%

$%%

In,reo Neto

1.-)inalmente aumente el grosor de las lneas del grafico, y tambi4n el tama=o del marcador a 1B puntos

$ealiOar dibujos encima de un grá"ico* +hora solo falta %ue el gráfico nos indi%ue cuál es el punto de e%uilibrio, o sea cuál es la cantidad de los artculos %ue se deben producir para %ue se cubran e/actamente los gastos de tal manera %ue ni se gane ni se pierda. !ste punto se encuentra en la vertical %ue pasa por el punto de intersecci'n entre la lnea de Tastos )ijos 8 ;ariables y la lnea de los Ingresos #etos. ara ello se utili"arán los siguientes botones de herramientas de dibujo: )lecha +utoformas7)lechas de blo%ue7)lecha abajo +utoformas7)lechas de blo%ue7entágono: 1.- 3ibuje la flecha vertical, y cambie su apariencia a lneas punteadas con el bot'n de la barra de dibujo Estilo de .ui9n.

Pág 101

Manual de Excel NIVEL AVANZADO .- $on la herramienta )lecha abajo, dibuje la flecha sobre el punto de intersecci'n y escriba dentro las letras Pe. 2uego acomode su forma y pntela con color de relleno. *.- $on la herramienta entágono, dibuje esta figura en el e/tremo inferior de la flecha punteada y hágala girar con la herramienta 3ibujo7Tirar o voltear7Tirar FB\ a la i"%uierda. 2uego escriba en su interior el número 11E, y tambi4n pinte con color de relleno.

<. 6$&ri$ir docu$en%o# en E-cel

5a impresión en E#cel es de manera semejante a la impresión de cualquiera de los otros documentos de Office! a! Para i*$ri*ir la +o#a de cálculo )i ya se *a concluido el trabajo en E#cel y el documento ya se encuentra guardado en un arc*ivo entonces& para imprimir& primeramente *aga clic en el botón de *erramientas Hista preliminar y acomode allí el contenido a imprimir en las *ojas de papel& luego ya podrá dar la orden de imprimir! Ejercicio Nº 10: 6$&ri$ir cuadro# de da%o#  "ráfico# de E-cel 1.- +bra el archivo anterior donde se calcul' y grafic' el punto de e%uilibrio:

Pág 102

Manual de Excel NIVEL AVANZADO .- Seleccione una de las celdas y haga clic en el bot'n ;ista preliminar

Si no se ha especificado %ue se desea imprimir, por defecto !/cel intentará imprimir todo el contenido de la hoja de cálculo actual en una hoja de papel %ue Hindo]s tenga configurada para la impresora predeterminada. Si el contenido de la hoja actual no cabe en una hoja de papel, la vista preliminar mostrará una por una todas las hojas necesarias tal y como estas se van a imprimir. 5haga clic en el bot'n Si.uiente o Anterior para ver las demás hojas6 G si no nos satisface el modo en %ue aparecen las hojas, entonces deberá usted hacer lo siguiente: *.- (aga clic en el bot'n $onfigurar y aparecerá una ventana con  fichas. .- !n la ficha =+.ina elija Orientaci9n de papel: 8orizontal , y en A(uste de escala elegir la opci'n A(ustar a !

pa.ina de anc%o por ! de alto$

Pág 103

Manual de Excel NIVEL AVANZADO 0.- !n la ficha M+r.enes elegir en la opci'n entrar en la p+.ina las casillas 8orizontalmente y tambi4n Verticalmente$ A.- 2uego elija +ceptar. G si ahora ya está conforme puede hacer clic en el bot'n Imprimir para enviar el documento hacia la impresora.

)! Para i*$ri*ir un ran,o de celda 1.- rimeramente seleccione el rango de celdas %ue desee imprimir, por ejemplo el rango A!>E24 .- 2lamar al menú Arc%ivoJrea de impresi9nEstablecer +rea de impresi9n *.- 2uego haga clic en el bot'n Vista preliminar y acomode el documento a imprimir en la hoja de papel según como se desee, utili"ando para ello nuevamente el bot'n on7i.urar . .- 2uego elija +ceptar. G a continuaci'n haga clic en el bot'n Imprimir para enviar el documento hacia la impresora. #ota:

$uando desee desactivar el rango establecido para imprimir, para %ue se vuelva a imprimir toda la hoja actual, entonces llame al menú: Arc%ivoJrea de impresi9norrar

+rea de impresi9n$

%uando se *ace clic en el botón :mprimir& en realidad se abrirá la ventana de diálogo :mprimir& la cual tiene las siguientes opciones<

Pág 104


Elija en esta ventana las opciones que crea convenientes como el modelo de impresora a utilizar& el n4mero de copias a imprimir& la cantidad de páginas& etc! Y luego *aga clic en "ceptar!

Pág 105


CAPITULO < 5uncione Avanzada a! Cate,or7a de la 5uncione de Excel Be las J2 funciones que ya e#istían en E#cel& la versión 200$ *a agregado 0 funciones más& *aciendo un total de JJ funciones& las cuales se clasifican a*ora en las siguientes categorías< ;unciones de complementos y automatización ;unciones de cubo ;unciones de base de datos ;unciones de fec*a y *ora ;unciones de ingeniería ;unciones financieras ;unciones de información ;unciones lógicas ;unciones de b4squeda y referencia ;unciones matemáticas y trigonomtricas ;unciones estadísticas ;unciones de te#to #(#%

5  12 20 3. 54 .  1 60 '2 2 33.

Nota: !n forma estándar !/cel tiene disponibles solo 234 funciones. ero si se cree conveniente, se pueden habilitar en forma complementaria 56 funciones más: para análisis de datos financieros, cientficos, y otros. ara ello hay %ue ingresar al bot'n de O77ice , hacer clic en el bot'n inferior Opciones de Excel y elegir omplementosD luego en la parte inferior aparece una lista desplegable en la cual debe elegir la opci'n omplementos de Excel y luego hacer un clic en el bot'n Ir D aparecerá entonces una ventana de diálogo en la cual deberá activar la casilla 8erramientas para an+lisis , y finalmente +ceptar.

Be seguro usted ya conoce las funciones básicas de E#cel& como son< MumaB PromedioB aB inB ContarB ContaraB EnteroB $edondearB $aOB PiB MiB UB (B 9uscarB 9uscarhB MubtotalesB +ndirecto!

5as cuales se *an visto en el primer curso inicial de E#cel& a*ora veamos algunas otras funciones más avanzadas< Sumar$si& ontar$si& ontar$Si$on(unto& Sumar$Si$on(unto& $esimo$ma-or& $esimo$menor& /edondear$mas& /edondear$menos& Ele.ir& Aleatorio& Esblanco& Esnumero& Estexto& Eserror& EsNod& 8o-& A%ora& dMax& Indice& Des/e7& oincidir& Iz'uierda& Derec%a& Extrae$ 8allar& Encontrar& Lar.o& Ma-usc& Minusc& Sustituir& Decimal& Espacios& ?ila& ?ilas& olumna& olumnas$

Pág 106


5unci/n 'U"A!'I )uma selectivamente las celdas que coinciden con el argumento criterio! Mintais M$!M+rangoBcriterioBrango a sumarD

Bonde< $ango Criterio

$ango a sumar

Es el rango de celdas que se desea evaluar! Es el criterio en forma de n4mero& e#presión o te#to& que determina qu celdas se van a sumar! (or ejemplo& el argumento criterio puede e#presarse como J2] AJ2A] A[J2A] AmanzanasA] B! )on las celdas que se van a sumar! 5as celdas contenidas en 3ango a sumar se suman sólo si las celdas correspondientes del 3ango coinciden con el %riterio! )i 3ango a sumar se omite& se suman las celdas contenidas en el argumento 3ango!

Ejercicio Nº ': Cálculo# condicionale# en iene# in$uele#

En una *oja de cálculo en las columnas "& 6 y % se tiene una relación de las valorizaciones de varios bienes inmuebles pertenecientes a tres personas! "l lado derec*o se quiere calcular a cuánto ascienden los bienes acumulados de cada propietario! (ara calcular la valorización acumulada de las casas de cada propietario *aga usted lo siguiente< 1.- !n ) escribir la f'rmula: BS@MA/$SI:A3>A!"&E3&3>!"; .- 2uego copie esta f'rmula en )0 y )A. *.- !n )E use +utosuma para sumar las tres valori"aciones. A 1 2 8 9 ; < > ? @ 10 11

: C 6alorizacion de varia caa

Pro$ietario arlos (onia Forge (onia Forge arlos (onia TOTAL

=ona (ur 3orte (ur 8este 3orte 8este 3orte

(<. (<. (<. (<. (<. (<. (<. 'R!

6alorizaci/n &&;,%%%.%% &$%,%%%.%% &B,%%%.%% 2@,%%%.%% $$$,%%%.%% &@%,%%%.%% D%,%%%.%% @;>B000!00

4

E 5 Totale $or Pro$ietario Pro$ietario arlos Forge (onia TOTAL

(<. (<. (<. 'R!

6alorizaci/n $B;,%%%.%% ;%,%%%.%% $@,%%%.%% @;>B000!00

Total de casas inferiores a 100,000:

'R!

1<;B000!00

Si tambi4n se desea calcular la suma de las valori"aciones %ue son inferiores a 1BB,BBB soles, entonces:

Pág 10

Manual de Excel NIVEL AVANZADO .- 2a formula en )11 sera:

@M$!M+C4*C10BI?100000ID #ote %ue se ha omitido el Lango a sumar.

%omo *emos 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< 9odas las casas que sean de un determinado propietario< @M$!M+4*10BIKorgeIBC4*C10D

o] todas las casas que se encuentren en una determinada zona< @M$!M+94*910BIorteIBC4*C10D



La 5unci/n CONTA!'I %uenta las celdas en el rango que coinciden con el argumento criterio! )inta#is C(#$!M+rangoBcriterioD

Es el rango de celdas que se desea evaluar! Criterio Es el criterio en forma de n4mero& e#presión o te#to& que determina qu celdas se van a contar! (or ejemplo& el argumento criterio puede e#presarse como J2] AJ2A] A[J2A] AmanzanasA]B! 5a función %O=9"3): cuenta el n4mero de veces que aparece un valor en un rango de celdas& por ejemplo< %ontar el n4mero de celdas en el rango 6<60 que contienen el te#to A)urA! $ango

@C(#$!M+94*910RIMurID

(ero& cómo *aríamos si se desea sumar todas las valorizaciones de casas ubicadas en una determinada zona y que además pertenezcan a un determinado propietarioF (ara resolver cálculos condicionales que requieren más de un criterio& entonces se puede *acer uso de las funciones Mumar!Mi!Conjunto & Contar!Mi!Conjunto & y Promedio!Mi!Conjunto!



La 5unci/n CONTA!'I!CON&UNTO %uenta el n4mero de celdas que cumplen con un determinado conjunto de condiciones o criterios! )inta#is Pág 10'

Manual de Excel NIVEL AVANZADO C(#$!M+!C(K#(rango1Bcriterio1BYD $ango1 Criterio1

Es el rango de celdas que se desea evaluar! Es el criterio en forma de n4mero& e#presión o te#to& que determina qu celdas se van a contar!

Ejemplos< %ontar el n4mero casas que tiene +)onia- en la zona +=orteA! @C(#$!M+!C(K#(4*10BIMoniaIB94*910BIorteID

%ontar el n4mero casas que tiene +Rorge- valorizadas en más de +200000A soles! @C(#$!M+!C(K#(4*10BIKorgeIBC4*C10BI>200000ID



La 5unci/n 'U"A!'I!CON&UNTO )uma las celdas de un rango que cumplen con un determinado conjunto de condiciones o criterios! )inta#is M$!M+!C(K#(rango a sumarBrango1Bcriterio1BYD $ango a sumar

$ango1 Criterio1

)on las celdas que se van a sumar! 5as celdas contenidas en 3ango a sumar se suman sólo si las celdas correspondientes coinciden con los m4ltiples %riterios! Es el rango de celdas que se desea evaluar! Es el criterio en forma de n4mero& e#presión o te#to& que determina qu celdas se van a sumar!

Ejemplos< )umar las valorizaciones de las casas que tiene +)onia- en la zona +=orteA! @M$!M+!C(K#(C4*C10B4*10BIMoniaIB94*910BIorteID

)umar las valorizaciones de las casas mayores a +00000- en la zona +=orte@M$!M+!C(K#(C4*C10BI>100000IB94*910BNorteND

Otro modo de resolver estos cálculos condicionales que requieren más de un criterio& es *aciendo uso de la combinación de la función M y la función M+& o tambin la función C(#$ y la función M+] pero escritas dentro de una 79rmula matricial !

Pág 10.

Manual de Excel NIVEL AVANZADO 5a ventaja de estas formulas matriciales es que las condiciones de criterios pueden ser del tipo Y o del tipo O!

Pág 110


5$ondici'n del tipo G6

5$ondici'n del tipo 6

5/r*ula "atricialeW • C/*o crear un total )aándoe en varia condicione #ota.- !n !/cel algunas f'rmulas pueden escribirse como f'rmulas matriciales. ara esto hay %ue escribir la f'rmula y al finali"ar hay %ue presionar la combinaci'n de teclas trl1S%i7t1Enter , esto hace %ue la formula se encierre entre llaves al momento de ingresar a la celda, %uedando de esta forma: B7ormulaP

Primer Caso -ara condiciones del ti-o UD

"*ora veremos la siguiente fórmula matricial que permitirá calcular el valor total de las valorizaciones de las celdas %<%0& donde el rango "<"0 contiene al propietario A)oniaA& Q el rango 6<60 contiene la zona denominada A=orteA! Z@MM+4*10@IMoniaID=94*910@IorteIDBC4*C10DD[

Escriba la formula sin las llaves& y al finalizar presione Ctrl&Mhi"t&Enter! 5as dos condiciones están encerradas entre parntesis y entre ellas se escribe el operador de multiplicación& lo cual indica que las dos condiciones deben cumplirse a la vez! Megundo Caso -ara condiciones del ti-o (D

(ara calcular el valor total de las valorizaciones de las celdas %<%0& donde "<"0 contiene A%arlosA o ARorgeA& utilice la siguiente fórmula! Z@MM+4*10 @ICarlosID&4*10 @IKorgeIDBC4*C10DD[

Escriba la formula sin las llaves& y al finalizar presione Ctrl&Mhi"t&Enter! En este caso las dos condiciones encerradas entre parntesis se escriben con el operador de suma entre ellas& lo cual estaría indicando que solo basta que se cumpla una de las dos condiciones para que se realice el cálculo! (tros ejem-los*

%ontar el n4mero de veces que aparecen varias condiciones En la siguiente fórmula& cada vez que E#cel encuentre A)oniaA en el rango "<"0& comprobará la presencia del te#to A=orteA en la misma fila en la columna 6 Cel rango 6<60D! " continuación& E#cel calculará de varios modos& el n4mero de filas que contienen ambos te#tos! En los tres casos las formulas obtienen el mismo resultado! Z@C(#$M+4*10@IMoniaID=94*910@IorteIDB1B0DD[

o sino así< Z@MM+4*10@IMoniaID=94*910@IorteIDB1B0DD[

o tambin así< Z@MM+4*10@IMonia IRM+94*910@IorteIB1B0DDD[

Pág 111


En las primeras dos formulas el operador de multiplicación indica que las condiciones son del tipo Y] y en la tercera formula se está utilizando dos funciones ): anidadas& o sea una función ): dentro de otra función ):! 5as tres son formulas matriciales y deben introducirse presionando al final Ctrl&Mhi"t&Enter! 

La 5uncione X!E'I"O!"ENOB  X!E'I"O!"AYO Encuentra los primeros menores o primeros mayores valores de una lista! )inta#is \!EM+(!E($rangoB-osicinD \!EM+(!U($rangoB-osicinD

es el rango de celdas que se desea analizar! Posicin es ubicación del n4mero menor o mayor que se desea encontrar! Esto es& para el menor de todos seria & para el segundo menor sería 2& etc! )i se quisiera averiguar cual es la valorización que ocupa el segundo lugar entre las mayores& Y cual es la tercera valorización más peque'a& las fórmulas serían< $ango



@\!EM+(!U($C4*C10B2D

1'6B000!00

@\!EM+(!E($C4*C10B3D

114B000!00

La 5uncione E4ON4EA!"ENO'B  E4ON4EA!"A' 3edondea un valor numrico al inmediato inferior o al inmediato superior seg4n la cantidad de decimales que se desee obtener! )inta#is $E,(,E$!E(MalorB-osicin decimalD $E,(,E$!MalorB-osicin decimalD

Es la cantidad numrica que se desea redondear! Posicin decimal Es la cantidad de decimales a los que se desea redondear la cantidad numrica! )i en posición se escribe un n4mero negativo se estará redondeando no los dígitos decimales sino los dígitos enteros! )i en la celda E4 estuviera escrito el numero 326!2'3B las funciones arrojarían los siguientes resultados< Lalor

@$E,(,E$!E(ME4B1D @$E,(,E$!ME4B1D

326!2 326!3

@$E,(,E$!E(ME4B0D

326

@$E,(,E$!ME4B0D

326'

Pág 112

Manual de Excel NIVEL AVANZADO @$E,(,E$!E(ME4B:1D

3260

@$E,(,E$!ME4B:1D

320

@$E,(,E$!E(ME4B:3D

3000

@$E,(,E$!ME4B:3D

4000

• La 5unci/n ALEATOIO

>enera un n4mero completamente al azar entre  y 0! )inta#is %E#($+(D

Esta función carece de argumentos! El siguiente ejemplo simula el lanzamiento de un dado SE=9E3OC"5E"9O3:OCDTID@ 5a función "leatorio multiplicada por I generará un numero entre 0 y !! 5a función Entero truncará la parte decimal de stos n4meros quedando así enteros del 0 al & y al aumentarles  stos finalmente serian n4meros entre  y I! Escrita esta fórmula& bastara con presionar la tecla de función W;X para recalcular la fórmula y entonces "5E"9O3:O generará otro n4mero al azar! 

La 5uncione E' En esta sección se describen  funciones para *ojas de cálculo que se utilizan para comprobar el tipo de un valor o referencia! %ada una de estas funciones& a las que se conoce como funciones E)& comprueba el tipo del argumento valor y devuelve HE3B"BE3O o ;"5)O dependiendo del resultado! (or ejemplo& E)65"=%O devuelve el valor lógico HE3B"BE3O si valor es una referencia a una celda vacía& de lo contrario devuelve ;"5)O!

)inta#is EM9%C(alorD EME$$alorD EME$$($alorD EM%(+C(alorD EM(,alorD EM(#E#(alorD EME$(alorD EM$EFalorD EM#E#(alorD

Pág 113

Manual de Excel NIVEL AVANZADO Es el valor que se desea probar! (uede ser el valor de una celda vacía& de error& lógico& de te#to& numrico& de referencia o un nombre de rango que *aga referencia a alguno de los anteriores!

Lalor

Funcin

,euele LE$,,E$( si

E)65"=%O

Halor se refiere a una celda vacía!

E)E33

Halor se refiere a cualquier valor de error con e#cepción de Q=P"!

E)E33O3

Halor se refiere a uno de los valores de error CQ=P"& QkH"5O3/& Qk3E;/& QkB:HP0/& Qk=1M/& Q=OM63EF o Qk=15O/D!

E)5O>:%O

Halor se refiere a un valor lógico!

E)=OB

Halor se refiere al valor de error Q=P" Cel valor no está disponibleD!

E)=O9E9O

Halor se refiere a cualquier elemento que no sea te#to Cobserve que esta función devuelve HE3B"BE3O si el valor se refiere a una celda en blancoD!

E)=1ME3O

Halor se refiere a un n4mero!

E)3E;

Halor se refiere a una referencia!

E)9E9O

Halor se refiere a te#to!

#ota.- 2os argumentos valor de las funciones !S no se convierten. or ejemplo, en la mayora de las funciones en las %ue se re%uiere un número, el valor de te/to ^1F^ se convierte en el número 1F. Sin embargo, en la f'rmula !S#U
Cuando usar las "unciones EM

5as funciones E) son 4tiles en fórmulas cuando se desea comprobar el resultado de un cálculo! "l combinar esas funciones con la función ):& proporcionan un mtodo para localizar errores en fórmulas Cobserve los siguientes ejemplosD! Ejem-los

E)5O>:%OCHE3B"BE3OD es igual a HE3B"BE3O E)5O>:%OCAHE3B"BE3OAD es igual a ;"5)O E)=1ME3OCD es igual a HE3B"BE3O

Pág 114

Manual de Excel NIVEL AVANZADO )upongamos que en una *oja el rango "<" muestra los siguientes valores de te#to& numrico y de error< AOroA& A3egiónA& Qk3E;/&  JJ0!2 y Q=P" respectivamente!

1 2 8 9 ;

A (ofGa Mrimestre& R0E*R N ==%.D$ 3
E)65"=%OC"D es igual a ;"5)O E)E33O3C"JD es igual a HE3B"BE3O E)=OBC"JD es igual a ;"5)O E)=OBC"D es igual a HE3B"BE3O E)E33C"D es igual a ;"5)O E)=1ME3OC"D es igual a HE3B"BE3O Csi se introdujo JJ0!2 como n4mero y no como te#toD E)3E;C3egiónD es igual a HE3B"BE3O Csi 3egión se define como un nombre de rangoD E)9E9OC"2D es igual a HE3B"BE3O Csi 9rimestre tiene formato de te#toD )upongamos que en otra *oja de cálculo desea calcular el promedio del rango %<%& pero no está seguro de que las celdas contienen n4meros! 5a fórmula (3OMEB:OC%<%D devolverá el valor de error QkB:HP0/ si %<% no contiene n4meros! (uede utilizar la siguiente fórmula para localizar posibles errores y permitir la realización del cálculo< S):CE)E33O3C(3OMEB:OC%<%DD&A=o *ay =4merosA&(3OMEB:OC%<%DD 

La 5unci/n %OY Ba como resultado la fec*a del sistema! )inta#is H(UD

Esta función carece de argumentos! @H(UD daría como resultado< 

&@<%B<$%%

La 5unci/n A%OA Ba como resultado la fec*a y *ora del sistema! )inta#is H($D

Esta función carece de argumentos! @H($D daría como resultado<

&@<%B<$%%

%=:$&

Pág 115




5uncione de :ae de 4ato :4funci/n

En esta sección se describen las 2 funciones para *ojas de cálculo empleadas para los cálculos de bases de datos Co listasD de Microsoft E#cel! %ada una de estas funciones& denominadas colectivamente funciones 6B& usa tres argumentos< baseSdeSdatos& nombreSdeScam-o y criterios! Estos argumentos se refieren a los rangos de la *oja de cálculo empleados en la función para base de datos! )inta#is 9,"uncin9ase de datosBCam-oBCriteriosD 9aseSdeSdatos

es el rango de celdas que compone la base de datos!

#ota.- !n
Cam-o

Criterios

:ndica la columna en que se utiliza la función! 5as columnas de datos en la lista deben tener un rótulo identificativo en la primera fila! %ampo puede ser una cadena de te#to con el rótulo de columna encerrado entre dobles comillas& como por ejemplo AEdadA o A3endimientoA en el ejemplo siguiente& o como un n4mero que representa la posición de la columna en la lista<  para la primera columna Crbol en el ejemplo siguienteD& 2 para la segunda C"ltoD y así sucesivamente! Es una referencia a un rango de celdas que especifican condiciones de una función! 5a función devuelve información de la lista que concuerda con las condiciones especificadas en el rango de criterios! Este rango incluye una copia del rótulo de la columna en la lista de la columna que desea que resuma la función! 5a referencia al argumento criterios puede introducirse como rango de celdas& como "<;0 en la tabla de base de datos que se muestra a continuación& o como nombre asignado a un rango& por ejemplo A%riterio!A

Mugerencias

Pág 116

Manual de Excel NIVEL AVANZADO %ualquier rango se puede usar como argumento criterios& siempre que incluya por lo menos un nombre de campo y por lo menos una celda debajo del nombre de campo para especificar un valor de comparación de criterios! (or ejemplo& si el rango ><>2 contiene el encabezado de campo :ngresos en la celda > y la cantidad 0&000 en la celda >2& el rango podría definirse como $oincidirIngresos y ese nombre podría usarse como argumento criterios en las funciones para bases de datos! • "unque el rango de criterios puede ubicarse en cualquier parte de la *oja de cálculo& no coloque el rango de criterios debajo de la lista! )i agrega más información a la lista utilizando el comando ;ormulario en el men4 Batos& la nueva información se agrega a la primera fila debajo de la lista! )i la fila de debajo no está vacía& Microsoft E#cel no podrá agregar la nueva información! "seg4rese de que 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 debajo de los nombres de campo en el rango de criterios •

Ejercicio Nº ): Cálculo# con funcione# de a#e de da%o# D

En la siguiente ilustración se muestra una base de datos que contiene la descripción y comportamiento de varios tipos de árboles! %ada registro contiene información acerca de un árbol! El rango " Peral ? ere!o @ 4an!ano 10 Peral 11 4an!ano

: C Alto Edad S&% Alto &P &$ &= &; D 

Edad $% &$ &; &@  D

4 endi*iento

E Ganancia

5 Alto T&B

endi*iento Ganancia &; (<. &%@.%% &% (<. DB.%% (<. &%@.%% D &% (<. 2@.%% (<. 2B.% P (<. ;@.%% B

6B%O=9"3C?1E39O&AEdadA&"<;2D es igual a ! Esta función e#amina los registros de manzanos cuyo alto varía entre 0 y I metros y determina cuántos campos Edad de esos registros contienen n4meros!

Pág 11

Manual de Excel NIVEL AVANZADO 6B%O=9"3"C?1E39O&A>ananciaA&"<;2D es igual a ! Esta función e#amina los registros de manzanos cuyo alto varía entre 0 y I metros& y determina el n4mero de campos >anancia de esos registros que no están en blanco! 6BM"C?1E39O&A>ananciaA&"<"JD es igual a 0!00 & la ganancia má#ima de manzanos y perales! 6BM:=C?1E39O&A>ananciaA&"<62D es igual a $!00 & la ganancia mínima de manzanos con un alto superior a 0 metros! 6B)1M"C?1E39O&A>ananciaA&"<"2D es igual a 22!00 & la ganancia total de manzanos! 6B)1M"C?1E39O&A>ananciaA&"<;2D es igual a $!00 ] la ganancia total de manzanos con un alto entre 0 y I! 6B(3OB1%9OC?1E39O&A3endimientoA&"<;2D es igual a 0& el producto del rendimiento de los manzanos con un alto entre 0 y I! 6B(3OMEB:OC?1E39O&A3endimientoA&"<62D es igual a 2& el rendimiento promedio de manzanos con un alto de más de 0 metros! 6B(3OMEB:OC?1E39O&J&?1E39OD es igual a J& la edad media de todos los árboles en la base de datos! 6BBE)HE)9C?1E39O&A3endimientoA&"<"JD es igual a 2!$] la desviación estándar estimada en el rendimiento de manzanos y perales si los datos de la base de datos son 4nicamente una muestra de la población total del *uerto! 6BBE)HE)9(C?1E39O&A3endimientoA&"<"JD es igual a 2!I] la desviación estándar verdadera en el rendimiento de manzanos y perales si los datos de la base de datos representan el conjunto de la población! 6BH"3C?1E39O&A3endimientoA&"<"JD es igual a K!K] la varianza estimada en el rendimiento de manzanos y perales si los datos de la base de datos sólo representan una muestra de la población total del *uerto! 6BH"3(C?1E39O&A3endimientoA&"<"JD es igual a $!0] la varianza real en el rendimiento de manzanos y perales si los datos de la base de datos representan el conjunto de la población del *uerto! 6BE93"E3C?1E39O&A3endimiento-&%3:9E3:O)D devuelve el valor de error Qk=1M/ porque más de un registro cumple con los criterios!

Pág 11'

Manual de Excel NIVEL AVANZADO *uncione# de 3#4ueda: 6ND6CE> DESE*> CO6NC6D6 Cuando usar la "uncin +,+CE*

%uando se desea encontrar un dato dentro de una rango& y se conoce en que fila y columna dentro de ese rango está el dato& entonces& se puede usar la función :=B:%E para ello! 

La 5unci/n IN4ICE Bevuelve la referencia o coordenada& o contenido de la celda en la intersección de una fila y columna concretas! )i la referencia se compone de selecciones no adyacentes& es posible determinar en cual de las selecciones se va a buscar! Mintais +,+CEC$e"erencia&=4mfila&=4mcolumna&=4máreaD $e"erencia

Es una referencia a uno o más rangos de celdas de b4squeda!

)i escribe un rango no adyacente para la referencia& incl4yala entre parntesis! )i cada área de la referencia sólo contiene una fila o columna& el argumento n4mfila o n4mcolumna& respectivamente& es opcional! (or ejemplo& para una referencia de fila 4nica& utilice :=B:%ECreferencia&&n4mcolumnaD! mS"ila mScolumna mSárea

Es el n4mero de la fila en referencia al cual se devuelve

una referencia! Es el n4mero de la columna en referencia al cual se devuelve una referencia! )elecciona un rango en referencia al cual se devuelve la intersección de n4mfila y n4mcolumna! 5a primera área seleccionada o escrita lleva el n4mero & la segunda el 2& etc! )i se omite n4márea& :=B:%E utiliza el área !

(bseraciones 







1na vez que referencia y n4márea *an seleccionado un rango concreto& n4mfila y n4mcolumna seleccionan una celda concreta< n4mfila  es la primera fila del rango& n4mcolumna  es la primera columna& etc! 5a referencia devuelta por :=B:%E es la intersección de n4mfila y n4mcolumna! )i n4mfila o n4mcolumna se establecen en 0 CceroD& :=B:%E devuelve la referencia de toda la columna o fila& respectivamente! =4mfila& n4mcolumna y n4márea deben apuntar a una celda de la referencia] de lo contrario& :=B:%E devolverá el valor de error Q3E;/! )i n4mfila y n4mcolumna se omiten& :=B:%E devuelve el área de referencia especificada por n4márea! El resultado de la función :=B:%E es una referencia y es interpretado como tal por otras fórmulas! )eg4n la fórmula& el valor devuelto de :=B:%E se puede

Pág 11.

Manual de Excel NIVEL AVANZADO utilizar como una referencia o como un valor! (or ejemplo& la fórmula %E55CAanc*oA& :=B:%E C"<62&&2DD es equivalente a %E55CAanc*oA&6D! 5a función %E55 utiliza el valor devuelto de :=B:%E como una referencia de celda! (or otra parte& una fórmula como 2T :=B:%E C"<62&&2D traduce el valor devuelto de :=B:%E al n4mero escrito en la celda 6! Ejemplo<

@+,+CE2*C6B2B1D

:ntersección de la segunda fila y la primera columna del rango "2<%I& que es el contenido de la celda "J C(látanosD!

@+,+CE2*C6B2B3D

:ntersección de la segunda fila y la tercera columna del rango "2<%I& que es el contenido de la celda %J CJKD!

@+,+CE1*C6B'*C11DB2B2B2D

:ntersección de la segunda fila y la segunda columna de la segunda área "K<%& que es el contenido de la celda 6 CJ&D! @M+,+CE1*C11B0B3B1DD

)uma de la tercera columna de la primera área del rango "<%& que es la suma de %<% C2ID! @M92*+,+CE2*C6B5B2DD

)uma del rango que se inicia en 62 y finaliza en la intersección de la quinta fila y la segunda columna del rango "2<%I& que es la suma de 62<6I C2&2D!

Pág 120


Cuando usar la "uncin ,EM$EF*

%uando se desea encontrar un dato ubicado en una fila y columna a partir de una celda conocida& entonces& se puede usar para ello la función BE)3E;! 

La 5unci/n 4E'E5 Bevuelve una referencia a un rango que es un n4mero de filas y de columnas de una celda o rango de celdas! 5a referencia devuelta puede ser una celda o un rango de celdas! (uede especificar el n4mero de filas y el n4mero de columnas a devolver! Mintais ,EM$EF$e"erenciaBFilasBColumnasBltoBnchoD $e"erencia

Filas

Columnas

lto ncho

Es la referencia en la que desea basar la desviación! Bebe referirse a una celda o rango de celdas adyacentes] en caso contrario& BE)3E; devuelve el valor de error QkH"5O3/ Es el n4mero de filas& *acia arriba o *acia abajo& al que desea que *aga referencia la celda superior izquierda! )i el argumento filas es & la celda superior izquierda de la referencia pasa a estar cinco filas más abajo de la referencia! ;ilas puede ser positivo Clo que significa por debajo de la referencia de inicioD o negativo Cpor encimaD! Es el n4mero de columnas& *acia la derec*a o izquierda& al que desea que *aga referencia la celda superior izquierda del resultado! )i el argumento columnas es & la celda superior izquierda de la referencia pasa a estar cinco columnas *acia la derec*a de la referencia! %olumnas puede ser positivo Clo que significa a la derec*a de la referencia de inicioD o negativo Ca la izquierdaD! Es el alto& en n4mero de filas& que desea que tenga la referencia devuelta! El alto debe ser un n4mero positivo! Es el anc*o& en n4mero de columnas& que desea que tenga la referencia devuelta! El argumento anc*o debe ser un n4mero positivo!

(bseraciones 





)i los argumentos filas y columnas colocan la referencia más allá del borde de la *oja de cálculo& BE)3E; devuelve el valor de error Qk3E;/ )i los argumentos alto o anc*o se omiten& los valores predeterminados serán los del argumento 3eferencia! BE)3E; en realidad no desplaza celdas ni modifica la selección& simplemente devuelve una referencia! )e puede utilizar la función BE)3E; con cualquier función que necesite una referencia como argumento! (or ejemplo& la fórmula )1M"CBE)3E;C%2]]2]J]DD calcula el valor total de un rango de tres filas por

Pág 121

Manual de Excel NIVEL AVANZADO una columna que se encuentra por debajo una fila y dos columnas a la derec*a de la celda %2! Ejemplo<



@,EM$EF3R3R2R1R1D

uestra el alor en la celda C6 40D

@M,EM$EF3*C5R:1R0R3R3DD

Muma el rango 2*C4 .0!5'D

@,EM$EFC3*E5R0R:3R3R3D

,euele un error -or)ue la re"erencia no está dentro de la hoja de cálculo V]$EF^D

La 5unci/n COINCI4I Bevuelve la posición relativa de un elemento en una matriz Cmatriz< utilizada para crear fórmulas sencillas que producen varios resultados o que funcionan en un grupo de argumentos que se organizan en filas y columnas! 1n rango de matriz comparte una fórmula com4n] una constante de matriz es un grupo de constantes utilizadas como un argumento!D que coincida con un valor especificado en un orden especificado! 1tilice %O:=%:B:3 en lugar de las funciones 61)%"3 cuando necesite conocer la posición de un elemento en un rango en lugar del elemento en sí! Mintais C(+C+,+$alor buscadoRmatriO buscadaRti-o de coincidenciaDD

es el valor que se utiliza para buscar el valor deseado en una tabla Halorbuscado es el valor que desea *acer coincidir en la matrizbuscada! (or ejemplo& cuando busque alg4n n4mero en la guía telefónica& estará usando el nombre de la persona como valor de b4squeda& pero el valor que realmente desea es el n4mero de telfono!

Lalor buscado 

Pág 122


Halorbuscado puede ser un valor Cn4mero& te#to o valor lógicoD o una referencia de celda a un n4mero& a un te#to o a un valor lógico!

atriO buscada es un rango de celdas contiguas que contienen posibles valores de

b4squeda! Matriz buscada debe ser una matriz o una referencia matricial! #i-o de coincidencia es el n4mero N& 0 ó  y especifica cómo *ace coincidir Microsoft E#cel el valorbuscado con los valores de matrizbuscada! tipodecoincidencia es & %O:=%:B:3 encuentra el mayor valor que es inferior o igual al valorbuscado! 5os valores en el argumento matrizbuscada deben colocarse en orden ascendente< !!!N2] N] 0] ] 2]!!!"N G] ;"5)O] HE3B"BE3O! )i tipodecoincidencia es 0& %O:=%:B:3 encuentra el primer valor que es e#actamente igual al valorbuscado! 5os valores en matrizbuscada pueden estar en cualquier orden! )i tipodecoincidencia es N& %O:=%:B:3 encuentra el menor valor que es mayor o igual al valorbuscado! 5os valores de matrizbuscada deben colocarse en orden descendente< HE3B"BE3O] ;"5)O] GN"] !!!2] ] 0] N ] N2] !!!y así sucesivamente! )i se omite tipodecoincidencia& se supondrá que es ! 







(bseraciones 







%O:=%:B:3 devuelve la posición del valor coincidente dentro de la matrizbuscada y no el valor en sí! (or ejemplo< %O:=%:B:3CAbA] AaA]AbA]AcAh]0D devuelve 2& la posición relativa de AbA dentro de la matriz AaA]AbA]AcAh! %O:=%:B:3 no distingue entre may4sculas y min4sculas cuando *ace coincidir valores de te#to! )i %O:=%:B:3 no puede encontrar una coincidencia& devuelve el valor de error Q=P"! )i tipodecoincidencia es 0 y valorbuscado es te#to& puede usar los caracteres comodín de signo de interrogación CFD y asterisco CTD en el argumento valorbuscado! El signo de interrogación corresponde a un solo carácter cualquiera y el asterisco equivale a cualquier secuencia de caracteres! )i lo que desea buscar es un signo de interrogación o un asterisco& escriba una tilde CD antes del carácter!

Pág 123

Manual de Excel NIVEL AVANZADO Ejemplo<

@C(+C+,+$3.R92*95R1D

(uesto que no *ay ninguna coincidencia e#acta& se devuelve la posición del siguiente valor inferior CJKD dentro del rango 62<6 C2D!

@C(+C+,+$41R92*95R0D

(osición de  dentro del rango 62<6 CD!

@C(+C+,+$40R92*95R:1D Bevuelve un error porque el rango 62<6 no está en

orden descendente CQ=P"D

Ejercicio Nº 5: u#car da%o# de Clien%e# en una li#%a ?cuando =SCAV no funciona@

)e tiene una lista de clientes en tres columnas< B=:& %liente y %elular!

)i se tiene como dato el B=: del cliente& entonces la función 6uscarH podría perfectamente buscar el nombre del cliente y tambin su n4mero del celular! (ero& si se tiene como dato el nombre del cliente& o su n4mero de celular& entonces 6uscarH no podría buscar el B=: del cliente& ya que la función 6uscarH solo buscará el dato en la primera columna de la lista& y no en la segunda o tercera columna! En casos como este& la solución es utilizar una combinación de las funciones BE)3E; y %O:=%:B:3& o sino :=B:%E y %O:=%:B:3! Heamos ambas soluciones en el ejercicio que se muestra a continuación! Pág 124


! Escribir la lista de clientes en una *oja de cálculo y luego dar nombres a los rangos como se indican a continuación<

2! En las columnas E y ; escribir los J cuadros siguientes& para buscar en ellos información de los clientes! J! En el primer cuadro& en la celda ;0 se tiene el n4mero de B=: del cliente& entonces en ; y ;2 se pueden utilizar funciones 61)%"3H para traer el nombre y el celular del cliente!

Pág 125


! En el segundo cuadro& en la celda ; se tiene el nombre del cliente& y en este caso en ;I y ;$ se utilizan funciones :=B:%E y %O:=%:B:3 para traer el B=: y el celular del cliente! ! En el tercer cuadro& en la celda ; se tiene el n4mero de celular del cliente& y para este ultimo caso en ;20 y ;2 se utilizan funciones BE)3E; y %O:=%:B:3 para traer el B=: y el nombre del cliente! 6. "dicionalmente se *an validado con la fic*a ,atos/Lalidacin de datos & a las siguientes celdas< ;0 (ermitir< 5ista& Origen< S"<"I ; (ermitir< 5ista& Origen< S6<6I ; (ermitir< 5ista& Origen< S%<%I

*uncione# de /e-%o 

La 5unci/n I=QUIE4A E#trae los primeros caracteres del e#tremo izquierdo de una cadena de caracteres )inta#is +_`+E$,tetoBnumSdeScaracteresD

Es la cadena de te#to que contiene los caracteres que se desea e#traer numSdeScaracteres Especifica el numero de caracteres que se desea e#traer! )i se omite e#trae solo un caracter #eto

Pág 126

Manual de Excel NIVEL AVANZADO Ejemplo< El Peru S:G1:E3B"C+El (eruano-&$D daría como resultado< CEl espacio en blanco tambin es un carácter que se toma en cuentaD 

.

La 5unci/n 4EEC%A E#trae los 4ltimos caracteres del e#tremo derec*o de una cadena de caracteres )inta#is ,E$ECHtetoBnumSdeScaracteresD

Es la cadena de te#to que contiene los caracteres que se desea e#traer numSdeScaracteres Especifica el numero de caracteres que se desea e#traer! )i se omite e#trae solo un caracter SBE3E%?"C+5os (eruanos-]KD daría como resultado< Peruanos . #eto



La 5unci/n ETAE Bevuelve un n4mero especifico de caracteres de una cadena de te#to comenzando en la posición especificada )inta#is E#$EtetoB-osicinSinicialBnumSdeScaracteresD #eto -osicinSinicial numSdeScaracteres

Es la cadena de te#to que contiene los caracteres que se desea e#traer Es la posición del primer carácter que se desea e#traer del argumento 9e#to! l primer carácter en 9e#to es  Especifica el numero de caracteres que se desea e#traer!

Ejemplo< SE93"EC+5os (eruanos-&&D 

daría como resultado<

Peru

.

La 5unci/n %ALLA 6usca una cadena de te#to dentro de otra cadena de te#to y leyendo de izquierda a derec*a devuelve el numero de la posición inicial de la cadena *allada Cno diferencia entre may4sculas ni min4sculasD )inta#is H%%$tetoSbuscadoRdentroSdelStetolR-osicinSinicialD #etoSbuscado Es la cadena de te#to o caracter que se desea ubicar! (uede usar los

comodines T o F! O usar tambin ,entroSdelSteto Es la cadena de te#to dentro de la cual se desea ubicar el te#to buscado -osicinSinicial Es la posición del primer carácter a partir de la cual se iniciará la b4squeda Ejemplo<

Pág 127

Manual de Excel NIVEL AVANZADO $. S?"55"3C+a-&+5as (eruanas-&D daría como resultado< D. S?"55"3C+a-&+5as (eruanas-&D daría como resultado< Ejemplo< )i en la celda % estuviese el te#to Las Peruanas ! entonces< . SE93"EC%&?"55"3C+ -&%&D@&D daría como resultado< Peru En este ejemplo& ?"55"3 se encarga de buscar la posición donde se encuentra el espacio en blanco& le suma  a esta posición y a partir de allí la función E93"E devuelve los siguientes  caracteres 

La 5unci/n ENCONTA 6usca una cadena de te#to dentro de otra cadena de te#to y leyendo de izquierda a derec*a devuelve el numero de la posición inicial de la cadena *allada C" diferencia de la función ?"55"3& esta función si reconoce entre may4sculas y min4sculasD )inta#is EC(#$$tetoSbuscadoBdentroSdelStetoB-osicinSinicialD #etoSbuscado Es la cadena de te#to o caracter que se desea ubicar! (uede usar los

comodines T o F! O usar tambin ,entroSdelSteto Es la cadena de te#to dentro de la cual se desea ubicar el te#to buscado -osicinSinicial Es la posición del primer carácter a partir de la cual se iniciará la b4squeda Ejemplo< )i en la celda "J estuviese el te#to quien 4AL anda mal acaba entonces< &B. SE=%O=93"3C+mal-&"J&D daría como resultado< en cambio& S?"55"3C+mal-&"J&D daría como resultado< 2. C3ecuerde que ?"55"3 no diferencia may4sculas ni min4sculasD !



La 5unci/n LAGO Bevuelve el n4mero de caracteres de una cadena de te#to )inta#is %$(tetoD #eto

Es la cadena de te#to& o celda que contiene un te#to

Ejemplo< S5"3>OC+Garaí 9oledo-D

daría como resultado<

&$. Pág 12'




La 5unci/ne "AYU'CB "INU'C  NO"POPIO %onvierte una cadena de te#to en may4sculas& en min4sculas o solo las primera letra de cada palabra en may4scula )inta#is UMCtetoD +MCtetoD (P$(P+(tetoD #eto

Es la cadena de te#to& o celda que contiene un te#to

Ejemplo< SM"Y1)%C+monto total-D daría como resultado< 483M8 M8MAL . . SM:=1)%C+BO) M:5-D daría como resultado< dos mil . S=OM(3O(:OC+bill gates-D daría como resultado< 7ill 6ates 

La 5unci/n 'U'TITUI 3eemplaza el te#to e#istente con te#to nuevo en una cadena )inta#is MM#+#+$tetoBtetoSoriginalBtetoSnueoRnmeroSdeSocurrenciaD

Es la cadena de te#to& o celda que contiene un te#to #etoSoriginal Es la cadena de te#to& que se desea reemplazar Es la cadena de te#to que reemplazará al te#tooriginal #etoSnueo meroSdeSocurrencia Es el numero de aparición del te#tooriginal dentro de toda la cadena de te#to! )i se omite se reemplazará el te#tooriginal en todos los sitios donde aparezca Ejemplo< )i en la celda "J estuviese el te#to quien mal anda mal acaba ! entonces< S)1)9:91:3C"J&-mal-&-bien-D . daría como resultado< quien bien anda bien acaba en cambio& S)1)9:91:3C"J&-mal-&-peor-&2D . daría como resultado< quien mal anda peor acaba CEn este caso solo sustituye la segunda palabra encontradaD #eto

Pág 12.




La 5unci/n 4ECI"AL 3edondea un n4mero al n4mero de decimales especificado& da formato al n4mero con el formato decimal usando comas y puntos& y devuelve el resultado como te#to!

)inta#is ,EC+%nmeroBdecimalesBnoSse-ararSmillaresD

Es el n4mero que desea redondear y convertir en te#to! ,ecimales Es el n4mero de dígitos a la derec*a del separador decimal! oSse-ararSmillares Es un valor lógico que& si es HE3B"BE3O& impide que BE%:M"5 incluya un separador de millares en el te#to devuelto! mero

(bseraciones 



 



5os n4meros en Microsoft E#cel nunca pueden tener más de  dígitos significativos& pero el argumento decimales puede tener *asta 2$ dígitos! )i decimales es negativo& el argumento n4mero se redondea *acia la izquierda del separador decimal! )i omite el argumento decimales& se calculará con 2 decimales! )i el argumento nosepararmillares es ;"5)O o se omite& el te#to devuelto incluirá el separador de millares! 5a principal diferencia entre dar formato a una celda que contiene un n4mero con el comando Celdas del men4 Formato y dar formato a un n4mero directamente con la función BE%:M"5 es que BE%:M"5 convierte el resultado en te#to! 1n n4mero que recibe formato con el comando Celdas sigue siendo un n4mero!

Ejem-los

1 2 8

Frmula

A &$=;.@&P &$=;.@B2 2$;;.=$@

$esultado

SBE%:M"5C"&2D

&2J!2

SBE%:M"5C"&ND

&2J0

SBE%:M"5C"2&N&HE3B"BE3OD

N2J0

SBE%:M"5C"JD

$&2!JJ

,escri-cin

3edondea " 2 dígitos a la derec*a del punto decimal 3edondea "  dígito a la izquierda del punto decimal 3edondea "2  dígito a la izquierda del separador decimal& y sin coma de millar 3edondea "J 2 dígitos a la derec*a del separador decimal

Pág 130


Pág 131

Manual de Excel NIVEL AVANZADO Ejercicio Nº 7: ;eneracin de cdi"o# con funcione# de %e-%o

En el siguiente cuadro se muestra la relación de socios que se *an inscrito en un club social& y se desea completar las columnas de códigos y nombres completos de cada socio!

Para generar los cdigos*

%ada código se compone de  caracteres que son e#traídos de los datos de cada socio& teniendo en cuenta ciertas condiciones! (or ejemplo para el primer socio inscrito su códigoo sería el siguiente<

'5K_H05 )e#o

Bos 4ltimos dígitos del a'o de nacimiento

(rimeras letras de sus apellidos y de su nombre

)egunda letra del lugar de nacimiento Bos dígitos del mes de su inscripción

5as formulas para lograr este código serian entonces< Bos 4ltimos dígitos del a'o de nacimiento< 

@,E$ECH(H4DB2D 

(rimeras letras de sus apellidos y de su nombre< =



+_`+E$,C4B1DA+_`+E$,,4B1DA+_`+E$,E4B1D

Bos dígitos del mes de su inscripción< =

,E$ECHI0IAEM94DB2D

Pág 132




)egunda letra del lugar de nacimiento< =

UMCE#$E+4B2B1DD

=

4

)e#o<

;inalmente entonces& la formula que *abría que escribir en la celda " para generar el código sería< @,E$ECH(H4DB2DA+_`+E$,C4B1DA+_`+E$,,4B1D A+_`+E$,E4B1DA,E$ECHI0IAEM94DB2DAUMCE #$E+4B2B1DDA4

%omo se puede observar& esta formula une todas las formulas anteriores utilizando entre ellas el operador de concatenación A! Para escribir los nombres com-letos de cada socio en una sola celda*

En la columna ; se tendrá que escribir una formula que una el nombres del socio y luego de una coma los dos apellidos& pero en may4sculas! (or ejemplo para el primer socio deberá decir< HugoB K$+%%( _L%

En la celda ; *ay que escribir la siguiente fórmula< @E4AIB IAUMCC4AI IA,4D

;inalmente& tanto la formula de los códigos como la de los nombres completos& deberá copiarse *acia abajo para todos los demás socios!

Ejercicio Nº 8: Con9er%idor de N3$ero# en Le%ra#

?ay ocasiones en que se desea que en ciertos documentos una cantidad numrica sea e#presada en letras! 9al es el caso de los montos de una factura& o de una boleta de ventas& o el monto de un c*eque! (ara estos casos el siguiente ejercicio nos podría ser de utilidad< 1.- !n un 2ibro nuevo escribir los siguientes datos tal como se muestra en la imagen siguiente:

Pág 133


.- 3efinir los siguientes nombres de rango:

93

*

96

* %E#$4M

C.*H1'

* #9%

*.- 2uego escribir las siguientes f'rmulas en las celdas: .* SE=9E3OCH"5O3CBE3E%?"CBE%:M"5CE=9E3OC=P000D&0&HE3B"BE3OD&IDDP00000D 10* SE=9E3OCH"5O3CBE3E%?"CBE%:M"5CE=9E3OC=P000D&0&HE3B"BE3OD&DDP0000D 11* SE=9E3OCH"5O3CBE3E%?"CBE%:M"5CE=9E3OC=P000D&0&HE3B"BE3OD&DDP000D 12* SE=9E3OCH"5O3CBE3E%?"CBE%:M"5CE=9E3OC=P000D&0&HE3B"BE3OD&JDDP00D 13* SE=9E3OCH"5O3CBE3E%?"CBE%:M"5CE=9E3OC=P000D&0&HE3B"BE3OD&2DDP0D 14* SE=9E3OCH"5O3CBE3E%?"CBE%:M"5CE=9E3OC=P00D&0&HE3B"BE3OD&2DDP0D 15* SE=9E3OCH"5O3CBE3E%?"CBE%:M"5CE=9E3OC=D&0&HE3B"BE3OD&JDDP00D 16* SE=9E3OCH"5O3CBE3E%?"CBE%:M"5CE=9E3OC=D&0&HE3B"BE3OD&2DDP0D 1* SH"5O3CBE3E%?"CBE%:M"5CE=9E3OC=D&0&HE3B"BE3OD&DD 1'* SH"5O3CBE3E%?"CBE%:M"5C=&2&HE3B"BE3OD&2DD 9.* 910* 911* 912* 913* 914*

S):CYC"S&"0S0&"S0D&A%:E=A&61)%"3HC"&9"65"&IDD S):C"S0&61)%"3HC"0&9"65"&D&):C"0S&AA&61)%"3HC"0&9"65"&DDD S):C"0S&):C"S0&AA&61)%"3HC"&9"65"&JDD&):C"S&A1=A&61)%"3HC"&9"65"&2DDD S):CYC"2S&"JS0&"S0D&A%:E=A&61)%"3HC"2&9"65"&IDD S):C"S0&61)%"3HC"J&9"65"&D&):C"JS&AA&61)%"3HC"J&9"65"&DDD S):C"JS&):C"S0&AA&61)%"3HC"&9"65"&JDD&):C"S&A1=A&61)%"3HC"&9"65"&2DDD

Pág 134

Manual de Excel Excel NIVEL AVANZADO S):CYC"S&"IS0&"$S0D&A%:E=A&61)%"3HC"&9 HC"&9"65"&IDD "65"&IDD 915* S):CYC"S&"IS0&"$S0D&A%:E=A&61)%"3 S):C"$S0&61)%"3HC"I&9"65"&D&):C"IS&AA&61)%"3 65"&D&):C"IS&AA&61)%"3HC"I&9" HC"I&9"65"&DDD 65"&DDD 916* S):C"$S0&61)%"3HC"I&9" 91* S):C"IS&):C"$S0&AA&61)%"3 S):C"IS&):C"$S0&AA&61)%"3HC"$&9 HC"$&9"65"&JDD&61)%"3 "65"&JDD&61)%"3HC"$&9 HC"$&9"65"&2DD "65"&2DD 91'* S):C"K\0&A0A&AADVBE%:M"5C"K&0&HE3B"BE3OD

S):C=[S000000000&BE%:M"5CE=9E3OC=P000000000D&0&HE3B"BE3ODVA&A&AADV ):C=[S000000&BE3E%?"CBE%:M"5CE=9E3OC=P000000D&0&HE3B"BE3OD&JDVAA&AADV):C=[ S000&BE3E%?"CBE%:M"5CE=9E3OC=P000D&0&HE3B"BE3OD&JD&AADVA&AV):C=[0&BE3E%?" CBE%:M"5C=&2&HE3B"BE3OD&ID&AAD SE)("%:O)C6VA AV60V6V):C)1M"C"<"0D[0&A M:55O=E)& A&):C"[& 96* SE)("%:O)C6VA " M:55O=E)& A&):C"S&A M:55O=& A&AADDDV62VA AV6JV6V):C)1M"C"2<"D[0& " M:5& A&):C)1M" C"<"D[0&A A&AADDDV6VA AV6IV6$VA Y AV):C"K[0&6K&A00ADV AP00 AVA=1EHO) )O5E)A 95*

5a *oja de cálculo se deberá ver así entonces<

.- Tuarde este libro con el nombre Letras

"*ora& cada vez que escriba un nuevo n4mero entero o con decimales en la celda 62& inmediatamente en la celda 6I aparecerá su equivalente en letras y redondeado a dos decimales! En la celda 6 aparecerá el mismo n4mero original redondeado a 2 decimales y con la siguiente puntuación< un punto para separar decimales& una coma para separar millares y una comilla simple para separar los millones! !n otros ejercicios ejercicios más adelante veremos veremos como se pueden utili"ar estos resultados resultados para confeccionar )acturas, &oletas de ;enta ;enta y Tiro de $he%ues.

Pág 135




La 5unci/n E'PACIO' E'PACIO' Elimina los espacios del te#to& e#cepto el espacio normal que se deja entre palabras! 1se E)("%:O) E)("%:O) en te#to procedente de otras aplicaciones aplicaciones que pueda contener una separación irregular!

)inta#is EMPC+(M#etoD #eto

Es el te#to del cual desea quitar espacios innecesarios!

@EMPC+(M rribaPer D

El resultado será< 

rriba Per

La 5unci/n 5ILA  5ILA' 5a sinta#is de la función es la siguiente< @F+%$e"erenciaD



Esta función le dice a E#cel a partir de que n4mero de fila se encuentra ubicada la referencia! @F+%M$e"erenciaD Esta función da como respuesta la cantidad de filas que contiene la referencia!

Bonde< /e7erencia Ejem-los*

S;:5"C"ID 3pta< I S;:5"C6D 3pta<  S;:5")C"<"$D 3pta<  )i el rango r ango "J<6$ "J<6$ tiene el nombre %1"B3O< S;:5"C%1"B3OD 3pta< J S;:5")C%1"B3OD 3pta<  Nota.- !n forma análoga a las dos funciones anteriores, tambi4n se pueden usar las funciones $2U<#+ y $2U<#+S respectivamente. respectivamente.

E(emplos: $2U<#+5+A6

Lpta: 1

$2U<#+5+*:&E6

Lpta: 1

$2U<#+S5$U+3L6

Lpta: 

Pág 136


CAPITULO 6II 



6alidar el In,reo de dato

Que e la validaci/n Es la forma en que E#cel impide que ingresen los datos que escribimos en las celdas Cn4meros& te#tos& fec*as y *oras& etc!D verificando que estos de encuentren dentro de límites especificados previamente! Be este modo se impide que se cometan errores al momento de digitar la información! (or ejemplo< :mpedir ingresar edades de personas que sean mayores a 00 a'os! :mpedir que se ingresen votos en una mesa de sufragio mayores a 200 electores :mpedir que se escriban códigos de artículos que no e#isten en la lista de precios C/*o validar el in,reo de dato en un ran,o de celda 1. Seleccione las celdas %ue desee restringir. . !n la ficha 3atos, haga clic en ;alidaci'n de datos y elija la ficha $onfiguraci'n. *. !n el cuadro ermitir, haga clic en el tipo de datos. ara especificar s'lo números, haga clic en #úmero entero o 3ecimal. ara especificar fechas u horas, haga clic en )echa u (ora.

. (aga clic en el operador %ue desee en el cuadro 3atos y especifi%ue el limite inferior o superior para los datos, o bien ambos lmites, dependiendo del operador %ue se seleccione. ueden introducirse valores, referencias de celda o f'rmulas para los lmites. Si desea permitir %ue la celda %ue se está restringiendo est4 en blanco o si desea definir lmites %ue utilicen una referencia de celda, o una f'rmula %ue

Pág 13

Manual de Excel NIVEL AVANZADO dependa de celdas %ue inicialmente están en blanco, compruebe %ue la casilla de verificaci'n mitir blancos está activada. ara hacer obligatorias las restricciones definidas en las celdas en blanco, tratándolas como si tuvieran ceros, desactive la casilla de verificaci'n mitir blancos. 0. ara %ue apare"can mensajes %ue pidan entradas y %ue e/pli%uen o impidan las entradas incorrectas, especifi%ue los tipos de mensajes %ue desee en las fichas
Q9mo %acerloR ara omitir la presentaci'n de mensajes, desactive las casillas de verificaci'n
Ejem-lo*

)e desea validar un cuadro para que en la columna %antidad solo permita escribir n4meros enteros y positivos! Entonces& cuando se trate de escribir en una celda una cantidad como $!J que es un numero decimal& deberá aparecer un mensaje de error como el que se ve a la derec*a& impidiendo ingresar este n4mero! 1 2 8 9 ; < > ?

A Peronal Empleados 1igilantes (ecretarias 6erentes Mcnicos (uper"isores 1endedores

: Cantidad &= && $;  2.=

@

(ara esto seleccionar el rango 62<6K& llamar al men4 BatosPHalidación& y elegir las opciones como se muestran en la siguiente ventana de diálogo<

Pág 13'


#ota.- $uando se especifi%ue el tipo de datos %ue se permiten, esto no afectará al formato. ara dar formato a las celdas, como números, fechas u horas, haga clic en el menú )ormato7$eldas y, a continuaci'n, haga clic en la ficha #úmero. Seleccione el formato %ue desee en el cuadro $ategora y luego seleccione las opciones %ue desee. Una f'rmula para un lmite puede evaluar los datos únicamente en la hoja de cálculo en %ue se hayan configurado las restricciones. ara utili"ar en una f'rmula los datos de otra hoja de cálculo, o de otros libros, introdu"ca una referencia de celda para los datos e/ternos en una celda de la hoja de cálculo activa o defina un nombre para los datos e/ternos en la hoja de cálculo activa. 2a f'rmula puede hacer referencia a la celda o al nombre en la misma hoja de cálculo. or ejemplo, si los datos %ue desea utili"ar en una f'rmula están en la celda +A de la primera hoja de cálculo de un libro denominado resupuesto./ls, puede definir el nombre DatosV+lidos en la hoja de cálculo activa para %ue sea >resupuesto./ls?(oja1QR+RA y, posteriormente, introducir una referencia 3atos;álidos cuando se especifi%uen los lmites para los datos.



ealizar una auditoria en un ran,o validado (uede buscar entradas de celdas que no se ajusten a sus criterios y rodearlas automáticamente con un círculo *aciendo clic en la opción $odear con un crculo datos no álidosB de la fic*a ,atos/Lalidacin de datos ! Ejem-lo1 1.- !n una hoja de cálculo escriba una lista de varios nombres de ni=os y sus respectivas edades, tal como se muestra en la figura siguiente. bserve %ue las edades de 2aura y
Pág 13.

Manual de Excel NIVEL AVANZADO !n este cuadro se desea verificar %ue las edades ya ingresadas de los ni=os, no sean mayores de 1 a=os. ara esto, haga usted lo siguiente:

.- Seleccionar el rango &:& y con el menú DatosValidaci9n , elegir ermitir #úmeros enteros entre un mnimo de B, y un má/imo de 1.

*.- 2uego de validar, en la barra de herramientas +uditoria haga un clic en el bot'n /odear con un cHrculo datos no v+lidos , entonces aparecerán marcadas con crculos rojos las cantidades incorrectas.

.- $orrija entonces las edades de ambos ni=os. + medida %ue corrija estas cantidades, observe %ue irán desapareciendo los crculos rojos %ue encerraban a las edades e%uivocadas.

Pág 140

Manual de Excel NIVEL AVANZADO Ejem-lo2

En otro caso] suponga que se tiene un registro de ventas con la relación de las facturas emitidas en un mes y se desea verificar que los n4meros de factura no aparezcan repetidos& en cuyo caso *abría que analizar el motivo del error y corregirlo& ya sea cambiando el numero de la factura por el correcto& o eliminando el registro de la factura por estar duplicada! 1.- !n una hoja en blanco escriba los datos del Legistro de ;entas %ue se muestra en la imagen siguiente: A 1 2 8 9 ; < > ? @ 10 11 12 18 19 1; 1<

: C EGI'TO 4E 6ENTA'

5ec+a %$8ct %$8ct %=8ct %=8ct %=8ct %=8ct %=8ct %;8ct %;8ct %;8ct %@8ct %B8ct

NZ 5actura %%&%&$; %%&%&$B %%&%&$B %%&%&$2 %%&%&$P %%&%&$D %%&%&$D %%&%&=% %%&%&=& %%&%&=$ %%&%&=$ %%&%&=;

(<. (<. (<. (<. (<. (<. (<. (<. (<. (<. (<. (<.

"onto &,=%B.%% @&%.%% &,=B;.%% ;=$.%% D=@.%% =DD.%% =DD.%% $$%.%% &,&DD.%% 22.%% $@2.%% =;.%%

Esta factura deberGa ser la %%&%&$@

Esta factura se repite y deberá eliminarse

La factura de abajo deberGa ser la %%&%&==

.- !n este cuadro seleccionar todas las celdas con los números de factura de la columna &, y con la ficha

DatosValidaci9n de datos

se valida según como se muestra en la ventana de dialogo a continuaci'n: 2a f'rmula B3T, verificará cuando dos celdas consecutivas se repitan. *.- 2uego de validar, haga clic en el bot'n de auditoria /odear con un cHrculo los datos no v+lidos . + medida %ue se vayan corrigiendo los errores en los números de las facturas, al igual %ue en el ejemplo anterior, los crculos rojos tambi4n se irán desapareciendo.

Pág 141

Manual de Excel NIVEL AVANZADO Ejercicio Nº : Con#ul%a# de !roduccin  Cálculo# de Ven%a

)e tiene un cuadro de la producción mensual de maíz en varios departamentos del (er4& y otro cuadro de la venta de este maíz para varios clientes! "l lado inferior del primer cuadro se quiere escribir formulas que permitan realizar diversas consultas respecto al cuadro de produccion! 9emas involucrados en este ejercico< =ombrar rangos m4ltiples 1so del operador de intersección 5a función :=B:3E%9O Halidación de rangos :ngreso simultáneo de datos en las celdas 5a función )1M"3!): 5a función 3E(E9:3 para crear barras       

Para realiOar las consultas del cuadro de -roduccin* 1.- 3ar nombre de rango a cada fila y columna del cuadro de producci'n: Seleccionar +*:T1* y en la ficha ?ormulas usar el comando rear desde la selecci9n , y luego +ceptar .- 3ar nombre ;!L+# al rango &:31*, y JZ al rango !:T1*: *.- !scribir las siguientes f'rmulas:

En D!6> En D!U>

BMa- usco BS@MA:VE/ANO usco;

.- ;alidar la celda &1 para elegir all los meses de una lista desplegable

Pág 142

Manual de Excel NIVEL AVANZADO Seleccionar la celda &1 y en la ficha Datos usar el comando Validaci9n de

datos& luego en la ventana de dialogo< !n ermitir, elegir: 2ista

!n rigen, escribir: &*:T* y +ceptar 0.- ;alidar la celda $1 para elegir all los lugares de una lista desplegable Seleccionar la celda $1 y en la ficha Datos usar el comando Validaci9n de

datos& luego en la ventana de dialogo< !n ermitir, elegir: 2ista

!n rigen, escribir: +:+1* y +ceptar A.- ;alide de igual manera las celdas &* y $*, pero al validar la celda &*, en el rango origen escriba: ;erano,to=o E.- !scribir a continuaci'n las siguientes f'rmulas:

En D2!> En D24>

BINDI/E0O:2!; INDI/E0O:2!; BS@MA:INDI/E0O:24; INDI/E0O:24;;

Para realiOar los cálculos de enta de la -roduccin* .- )eleccionar el rango 5I<5 y escribir la formula>

B INDI/E0O:6; INDI/E0O:6;CL4 y luego presionar trl1Enter F.- !scribir la siguiente f'rmula:

En 2"> B$SI:I6>I!,&I2"&L6>L!,; 5uego copiar esta fórmula *acia abajo! 1B.-)inalmente escribir la f'rmula:

En 2"> B /E=E0I/:WXW&2"!"""""; CEl carácter  se escribe con las teclas "lt@2D 5uego copiar tambin esta fórmula *acia abajo!

Pág 143


CAPITULO 6III L7nea de Tendencia en lo Gráfico 



Utilidad de la l7nea de tendencia 5as líneas de tendencia se usan para mostrar gráficamente las tendencias de los datos y analizar los problemas de predicción! Este análisis tambin 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 ejemplo& en el siguiente gráfico se utiliza una línea de tendencia simple que muestra la previsión para cuatro trimestres indicando una clara tendencia de aumento en los ingresos! edia mil!N 9ambin se puede crear una media móvil& que suaviza las fluctuaciones en los datos y muestra la trama o tendencia con más claridad! Ti$o de ,ráfico .ue ad*iten l7nea de tendencia (ueden agregarse líneas de tendencia a las series de datos en los siguientes gráficos< • • • • • • •

de áreas 2B no apiladas& de barras& de columnas& de líneas& de cotizaciones& de tipo Y CBispersiónD& y de burbujas!

=o pueden agregarse líneas de tendencia a las series de datos en los gráficos JB& radiales& circulares& de superficie o de anillos!

Pág 144

Manual de Excel NIVEL AVANZADO )i se cambia un gráfico o una serie de datos de modo que ya no permita la línea de tendencia asociada Cpor ejemplo& si se cambia el tipo de gráfico por un gráfico de áreas JB o si se cambia la vista de un informe de gráfico dinámico o de un informe de tabla dinámica asociadoD& se perderán las líneas de tendencia! 

A,re,ar una l7nea de tendencia a una erie de dato 1.- !n el gráfico, haga clic en la serie de datos en la %ue desea agregar la lnea de tendencia o la media m'vil. .- !n el menú Tráfico, haga clic en la opci'n +gregar lnea de tendencia. *.- !n la ficha Jipo, haga clic en el tipo de lnea de tendencia de regresi'n o en la media m'vil %ue desee. Si se selecciona olinomial, introdu"ca en el cuadro rden el valor potencial más alto de la variable independiente. Si se selecciona
Ejem-lo

(ara analizar las proyecciones en un gráfico de líneas] una vez creado el grafico se'alar con el puntero del mouse la línea y llamar al men4 conte#tual con un clic derec*o del mouse& luego elegir la opción gregar lnea de tendencia!

Pág 145


En la ventana de diálogo elegir en 9ipo de tendencia o regresión la opción 5ineal y en la fic*a Opciones marcar la casilla (resentar ecuación en el gráfico& luego W"ceptarX! )e verá entonces en el grafico el siguiente resultado<

C)i se reemplaza +#- en la ecuación por el numero del mes se obtienen los pronósticos de las ventas de esos meses!D )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 ajustaran 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 y a la ecuación en el grafico& tal como se ve en la figura! Otras formas en las que 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 6arras< 9enga en cuenta lo siguiente< )i se agrega una media móvil a un gráfico Y CBispersiónD& la media móvil se basará en el orden de los valores  trazados en el gráfico! (ara obtener el resultado

Pág 146

Manual de Excel NIVEL AVANZADO deseado& puede ser necesario ordenar los valores  antes de agregar una media móvil! 5as 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! "ntes de agregar líneas de tendencia u otros elementos de formato a un informe de gráfico dinámico& aseg4rese de que el dise'o es satisfactorio!

Pág 14


8! Uo de lo co*ando Co$iar  Pe,ar co*o i*a,en 

Para Co$iar  Pe,ar una i*a,en co*o 67nculo

Este comando podrá capturar cualquier rango de celdas en el cual e#istan datos& gráficos o dibujos y convirtiendo dic*o rango en una imagen podrá pegarlo en cualquier lugar del documento como un vínculo de actualización automática! 1.- Seleccionar el rango de celdas conteniendo los datos u objetos %ue desea tomar copiar y pegar como imagen. .- resionar $trl8$ para copiar el rango. *.- Seleccionar la celda en donde desee ubicar la imagen. .- !n la ficha Inicio elija el comando

=e.aromo ima.en=e.ar vHnculos de ima.en

*.- )inalmente presionar >!sc?.

Este objeto no se comporta como cualquier otra imagen& pues ella siempre estará delante de todos los objetos en la pantalla! 9ampoco se podrá agrupar con otros objetos de dibujo o imágenes! "unque si podrá cambiarse su posición y tama'o! Ejem-lo

En este ejemplo se *a copiado las celdas que contienen el tipo de cambio del dólar y del Euro& y se lo *a pegado como imagen con vínculo encima del cuadro de embarques! 5a imagen mantendrá actualizado el valor del dólar cada vez que este sea cambiado en las celdas origen en donde están escritas originalmente!

#ota.- Si el contenido del rango al %ue se ha copiado es modificado, entonces la imagen tambi4n cambiará, actuali"ándose automáticamente a esos cambios.

Pág 14'




Para Co$iar  Pe,ar una i*a,en no actualiza)le

Este comando es parecido al anterior& pero la imagen que se logre pegar no será de actualización automática! 1.- Seleccionar el rango de celdas conteniendo los datos u objetos %ue desea tomar copiar y pegar como imagen. .- !n la ficha Inicio elija el comando =e.aromo ima.enopiar como ima.en

+parecerá la siguiente ventana de diálogo: !lija la opci'n omo en pantalla& si desea capturar del rango seleccionado todos los objetos visibles en 4l, o sinoD la opci'n omo en impresora& si %uiere capturar solo los objetos %ue seran impresos en la impresora. *.- Seleccionar la celda en donde desee ubicar la imagen. .- !n

la ficha Inicio elija el comando

=e.aromo ima.en=e.ar como ima.en 0.- )inalmente presionar >!sc?.

" diferencia del objeto pegado como vinculo& este objeto se comporta como cualquier imagen& y entonces podrá ubicarse delante o atrás de cualquier objeto& así como agruparse con ellos& además de cambiar su tama'o y posición a voluntad!

Pág 14.


CAPITULO 6III La Pro,ra*aci/n en Excel "ACO' EN ECEL

5a palabra M"%3O está muy ligada a las *ojas de cálculo desde *ace muc*os a'os atrás en la poca en que 5otus 2J reinaba en el mundo de las microcomputadoras de escritorio! 1na M"%3O& no es sino una secuencia de ordenes escritas en los arc*ivos de las *ojas de calculo para que se encarguen de realizar automáticamente aquellas acciones que son repetitivas en el manejo de nuestros datos! Esto es& si en un trabajo en la *oja& es necesarios realizar una secuencia de tareas y mas tarde *ay que repetir esta secuencia otra vez& y luego otra vez] convirtindose esto en una rutina] entonces es cuando se *ace necesario recurrir a una M"%3O! O sea& un programa que automatice las acciones para no tener que *acerlas cada vez nosotros en cada ocasión! En 5otus2J y en uattro (ro que fueron dos *ojas de calculo muy populares en BO)& así como en las versiones iniciales de E#cel& la programación de las +macro instrucciones- se escribían usando una serie de códigos o palabras reservadas que eran propias 4nicamente de stas aplicaciones! (ero& a partir de la versión !0 de E#cel& Microsoft recurre al lenguaje de Hisual 6asic para escribir sus programas en *ojas de módulo que se ane#an en forma transparente a los libros de E#cel& recurriendo para ello al Editor de Hisual 6asic para "plicaciones 

Para activar la fic+a Pro,ra*ador Esta es una fic*a adicional muy necesaria si es que se quiere trabajar con macros en E#cel! (ara activarla *aga lo siguientes< 1.- 2lamar al menú de ffice y hacer clic en el bot'n pciones de !/cel. .- +ctivar la casilla


Uo de la ,ra)adora de *acro " pesar que la programación se realiza internamente utilizando el lenguaje de Hisual 6asic& no es necesario que los usuarios de E#cel sepan necesariamente el uso de este lenguaje! Y esto es así& gracias a que e#iste la posibilidad de que se puedan grabar las acciones que 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 Hisual 6asic!

Pág 150


(ara crear una macro *aciendo uso de la grabadora de macros se siguen los siguientes pasos< 1.- !n la ficha rogramador hacer clic en el comando Trabar macro.

.- !n la ventana de diálogo %ue aparece escribir un nombre para la macro a grabar. !ste nombre no deberá contener espacios en blanco. G luego haga clic en +ceptar.

*.- +l aparecer el bot'n cuadrado a"ul en el lado i"%uierdo de la barra de estadoD reali"ar cuidadosamente y en forma secuencial, todas las acciones %ue se desean grabar. .- ara finali"ar la grabaci'n hacer un clic en el comando Detener .rabaci9n de la ficha =ro.ramador , o en el cuadrado a"ul de la barra de estado.

Pág 151


#ota.- 2as macros al ser creadas pueden grabarse en el libro actual, como se puede apreciar en la ventana de dialogo anterior en la opci'n: Este libro , con lo cual la macro solo funcionará en ese libroD o tambi4n se pudo elegir en la ventana de diálogo la opci'n: Libro de macros personal , para cuyo caso la macro podra funcionar tambi4n en todos los libros de !/celD sin embargo, hay %ue tener cuidado con esto, pues si se graba en el libro de macros personal esto hará %ue dicha macro y todas las %ue se encuentren all sean cargadas en memoria L+< cada ve" %ue se ingrese a !/cel, restando con ello memoria al computador.

1na vez creada& veamos a continuación como se *ace para que la macro funcione< 

E#ecuci/n de una *acro 1na macro puede ejecutarse con el men4 ?erramientasPMacroPMacros& y en la ventana de dialogo& seleccionar su nombre y luego elegir el botón Ejecutar! )in embargo& un modo mas controlado y fácil de ejecutar una macro es asignándola a un elemento de imagen& como puede ser a un dibujo de "utoformas& a un botón de *erramientas personalizado o incluso *asta a un gráfico! (ara asignar una macro a un botón de "utoformas realizar lo siguiente<

1.- Se=alar con el mouse el borde del bot'n y hacer un clic derecho para llamar al menú conte/tual .- !n el menú elegir la opci'n +signar macro. *.- Seleccionar el nombre de la macro de la lista y luego clic en +ceptar.

Pág 152

Manual de Excel NIVEL AVANZADO .- )inalmente haga clic fuera del bot'n para liberar la selecci'n.

Ejercicio Nº <: +acro &ara $o9er da%o#

)e va *a crear una macro que separe de una lista de socios a las mujeres& ponindolas en una columna a la derec*a de los *ombres<

1.- ara reali"ar esto, primero seleccione la celda & %ue contiene el nombre de
.- !n la ficha rogramador hacer clic en el comando Trabar macro.

.- !n la ventana de diálogo %ue aparece escribir Mu(eres como nombre para la macro a grabar. G en la casilla <4todo abreviado escribir la letra MmN. 2uego haga clic en +ceptar.

Pág 153

Manual de Excel NIVEL AVANZADO .- +l aparecer el bot'n de M3etener grabaci'nN en la barra de estado, reali"ar lo siguiente:

 

resionar $trl8$ Seleccionar la celda de la derecha 5$6



resionar !nter 5para pegar6



Legresar a la celda de la i"%uierda 5&6



resionar >Supr? 5para borrar la celda6



&ajar el puntero una celda 5a la celda &06

*.- )inalmente terminar la macro con el bot'n Detener .rabaci9n .



Para e#ecutar una *acro con la tecla de "todo a)reviado

El modo permite ejecutar la macro en cualquier momento apenas se presione %trl @ la tecla que se eligió como mtodo abreviado! (ara probar ejecutar la macro del ejemplo anterior& primero regrese manualmente el nombre de María a la celda 6! 5uego ubicar el puntero en la celda 6& y luego presione %trl@m para ejecutar la macro! %omo podrá ver& la macro funciona perfectamente desplazando el nombre de María a la columna de las mujeres! (ero no va *a funcionar así de bien cuando queramos mover los nombres de las demás mujeres! " continuación& ubique el puntero sobre el nombre de la siguiente mujer en 6I y vuelva a presionar %trl@m para que la macro mueva a %laudia *acia la derec*a! (ero en esta ocasión& como ve& no ocurre lo que esperábamos! En vez de mover a %laudia *acia la derec*a& la *a movido encima de María!

(or qu la macro *a *ec*o esto_F Veamos lo 'ue %a sucedido>

Pág 154

Manual de Excel NIVEL AVANZADO %uando una macro graba las acciones que le indicamos& repetirá estas mismas acciones e#actamente de la misma manera en que fueron grabadas! %uando prendimos la grabadora de macros& E#cel aprendió a copiar el contenido de la celda actual en la celda % y luego a borrar la celda 6! "l ejecutar la macro para %laudia& esta fue copiada Cseg4n *abía aprendido E#celD a la celda %& y luego borró la celda 6! 5o que debimos *acer entonces al grabar la macro fue decirle a E#cel que utilice re"erencias relatias y no referencias absolutas& al momento de desplazarse de una celda a otra! 

Para ,ra)ar una *acro Uando eferencia elativa

Hamos entonces a volver a grabar la macro& pero esta vez usaremos referencias relativas! ?aga entonces lo siguiente< 1.- Seleccione una celda %ue contenga el nombre de una mujer. .- !n la ficha rogramador hacer clic en el comando @sar re7erencias relativas y luego clic en el comando Grabar macro .

.- !n la ventana de diálogo %ue aparece escribir Mu(eres2 como nombre para la macro. G en la casilla <4todo abreviado escribir la letra MrN. 2uego haga clic en +ceptar.

Pág 155

Manual de Excel NIVEL AVANZADO .- +l aparecer el bot'n de M3etener grabaci'nN en la barra de estado, reali"ar lo siguiente:  

resionar $trl8$ Seleccionar la celda de la derecha



resionar !nter 5para pegar6



Legresar a la celda de la i"%uierda



resionar >Supr? 5para borrar la celda6



&ajar el puntero una celda

*.- )inalmente terminar la macro con el bot'n Detener .rabaci9n .

A%ora si> Ubi%ue el puntero sobre el nombre de cual%uier mujer y presione las teclas $trl8r, y verá %ue la macro ya funciona a la perfecci'n, y as lo hará para todas las demás mujeres.





Uo del Editor de 6iual :aic (ara la macro del ejemplo anterior& el programa o subrutina que *a grabado E#cel está escrito en una *oja de Modulo insertada dentro del libro actual de E#cel! (ero este programa no se puede ver estando en la *oja de cálculo& sino que es necesario ingresar a otra ventana de aplicación llamada Editor de Hisual 6asic para poder verlo allí! Para in,rear al Editor de 6iual :aic )e puede *acer de dos formas< Pág 156

Manual de Excel NIVEL AVANZADO i! En la fic*a del Programador usar el comando Lisual 9asic& ó_ ii! En la fic*a del Programador usar el comando acros& elegir el nombre de la macro ujeres y luego clic en el botón odi"icar! 5a ventana de Hisual 6asic aparecerá en la pantalla& y mostrará al lado izquierdo un árbol ramificado del proyecto 5ista de )ocios& el cual contiene varias *ojas de calculo y una *oja de Modulo! "l lado derec*o de la ventana se observan las dos macros grabadas< Mujeres y Mujeres2! Observe la diferencia entre la primera macro grabada con referencias absolutas y la segunda que se grabó usando referencias relativas! 5a primera macro menciona e#actamente las coordenadas de celda que se usaron en la grabación& en cambio la segunda macro utiliza la palabra Offset para referirse a las celdas donde se desplazará el puntero de celda al ejecutar la macro!

Macro con referencias absolutas!

Macro grabada usando referencias relativas!

Pág 15


(ara pasar de la ventana de Hisual a la ventana de E#cel y viceversa& puede presionar las teclas "lt@;! Ejercicio Nº 10: Accione# re&e%i%i9a# con $acro#

?ay ocasiones en que se necesita en la *oja de calculo repetir un grupo de acciones muc*as veces para diferentes rangos de celda! En estos casos es cuando las macros nos serán de gran ayuda! )uponga que se dispone de una lista diaria de clientes que asisten durante un mes a realizar sus compras a una zapatería& y se desea ordenar esta lista en grupos semanales& y cada semana ordenada descendentemente seg4n la cantidad de clientes asistentes! 1na vez ordenada cada semana en orden descendente poner en negrita y de color rojo la cantidad ma#ima de personas que asistieron en cada semana! Heamos como se soluciona este caso con ayuda de las macros< 1ra Parte* rear la tabla de clientes 'ue asisten a una zapaterHa ! 1.- !n una hoja en blanco escriba lo siguiente:

.- Seleccionando la celda +0, llame al menú )ormato7$eldas y en la ficha #úmero, categora personali"ada, dise=e el siguiente formato en la casilla Jipo:

ddd dd mmm 2a fecha aparecerá como: iJ 01 Kun *.- 2uego con el bot9n derec%o del mouse arrastre el cuadro de relleno de la celda +0 hasta llegar a la celda +A, y cuando al soltar el bot'n del mouse apare"ca un menú

Pág 15'

Manual de Excel NIVEL AVANZADO conte/tual, elegir la opci'n /ellenar dHas de la semana , para copiar la serie de das laborales del mes de Cunio. 5Solo aparecerán 0 das laborables en cada semana6

5uego en la columna 6 vamos a escribir la cantidad de personas que asistieron cada día a la zapatería! (ara simular la cantidad de personas vamos a *acer que E#cel escriba n4meros al azar entre 0 y 0< .- !scriba la siguiente formula en la celda &0:

@10&E#E$(%E#($+(D=140D

!N "*ora copie esta formula *aciendo doble clic en el cuadro de relleno! 5uego convierta el resultado de estas formulas en valores< A.- Seleccione el rango &0:&A y elija el comando opiar , luego en el menú =e.ado especial , mar%ue la opci'n Valores y +ceptar. )inalmente >!sc?.

2da Parte* Dividir las personas en .rupos semanales ordenados descendentemente!

%omo se e#plicó al principio& se desea una macro que *aga lo siguiente<

E.-

rimero seleccione la celda +.

.- 2lame al comando Grabar F.-

nueva macro de la ficha =ro.ramador $omo nombre para la macro escriba la palabra Grupos y como letra de m4todo abreviado escriba la letra ^ Y ^. 2uego +ceptar

Pág 15.

Manual de Excel NIVEL AVANZADO 1B.- ;erifi%ue %ue se encuentre activo el comando Usar referencias relativas. 11.- 2uego realice los siguientes pasos: 

!n la ficha Inicio usar el comando InsertarInsertar

7ilas de %o(a 

Seleccionar la celda +E y presionar las teclas $trl8_ para seleccionar la regi'n actual 5+0:&E6



2lamar al comando 3atos7rdenar y elegir ordenar por $olumna &, en orden 3escendente y verifi%ue %ue #o se considere tener fila de encabe"amiento. 2uego haga clic en el bot'n +ceptar.



Seleccionar la celda &0 y ponerla en #egrita, y de color de fuente Loja.

Seleccione la celda +1 y haga un clic en el bot'n Detener .rabaci9n. 1*.- Tuarde el libro con el nombre de archivo Asistencia. 

%on esto ya tendremos ordenado el primer grupo de los días correspondientes a la primera semana de Rulio! Y como el puntero de celda se encuentra en "& solo bastará con presionar las teclas %trl@7 para ejecutar la macro y que esta se encargue de ordenar el grupo de la segunda semana& y así sucesivamente *asta terminar con el resto del mes!

Pág 160


CAPITULO I E)%E="3:O) E= E%E5 Esta herramienta avan$ada nos permitir# anali$ar las variaciones de determinadas varia"les dentro de un informe so"re unos resultados finales o intermedios

7n escenario es un conjunto de valores ,ue Excel guarda y sustituye en nuestro informe 7tili$aremos los escenarios para o"servar la variación en el resultado de nuestro modelo 4enemos la posi"ilidad de crear y guardar diferentes grupos de valores+ en distintos escenarios y+ a continuación+ pasar a cual,uiera de estos nuevos escenarios para ver distintos resultados En definitiva es el an#lisis normal de un modelo+ financiero o no+ con varios escenarios posi"les -ctivaremos esta herramienta avan$ada escenarios desde el menú erramientas 8 Escenarios+ activando así el administrador de escenarios+ desde el cual crearemos todos nuestros escenarios !ógicamente+ el paso previo ser# disponer de un modelo en nuestra hoja de c#lculo so"re el ,ue anali$ar la variación de algunas celdas resultantes según la varia"ilidad de otras celdas vinculadas Propondremos el siguiente modelo presupuestario+ sencillo+ para explicar el uso de los escenarios

1. 2. 3. 4.

En el menú Herramientas, elija Escenarios. Haga clic en Agregar. En el cuadro Nombre de escenario , escriba un nombre para el escenario. En el cuadro Celdas cambiantes , inrodu!ca las re"erencias de las celdas #ue desee cambiar. $%&' (ara preser)ar los )alores originales de las celdas cambianes, cree un escenario #ue uilice los

)alores originales de las celdas anes de crear escenarios #ue cambien )alores. *. 6. 7. . 9.

En Proteger, seleccione las opciones #ue desee. Haga clic en Aceptar. En el cuadro de di+logo Valores del escenario, inrodu!ca los )alores #ue desee para las celdas cambianes. (ara crear el escenario, -aga clic en Aceptar. i desea crear escenarios adicionales, -aga clic de nue)o en Agregar / repia el procedimieno. Cuando ermine de crear los escenarios, elija Aceptar / -aga clic en Cerrar en el cuadro de di+logo Administrador de escenarios.

Este es un tema que generalmente se presta a interpretaciones erróneas y mitos sobre +lo difícil que es- trabajar con escenarios! =ada más lejos de la realidad! En este material e#plicaremos en trminos sencillos la base conceptual de manejo de escenarios& introduciendo al lector en la terminología básica imprescindible y conectándolo con la realidad cotidiana de casos que aquí mismo vamos a e#poner! (ara comenzar& refle#ionemos sobre lo siguiente< sabemos los que es un escenarioF! (robablemente la respuesta es no& sin embargo& de ser afirmativa& las nociones más conocidas de escenario son las siguientes 5ugar donde normalmente se representa un espectáculo! )ituación que se presenta a partir de *ec*os que ocurren! Becisión que se toma seg4n datos recibidos! 9odas tienen en com4n el *ec*o de ocurrir dependiendo de una o más variables que determinan su ocurrencia& por ejemplo& la obra se presenta si va el p4blico& se toma la decisión " si se tiene la información  y se toma la decisión 6 si se tiene la información G! (artiendo de estas premisas básicas podemos comprender que el implementar un escenario en E#cel contiene resultados que dependen de cambios en algunos n4meros& es decir& si se   

Pág 161

Manual de Excel NIVEL AVANZADO escribe un valor  obtendremos un resultado "& si se escribe un valor G& obtendremos un resultado 6& etc! (ara ilustrar mejor lo antes se'alado& colocaremos un ejemplo que será desarrollado a lo largo de la guía! Heamos! 5a empresa +:mportadora HG5"!- )e dedica a la compra de láminas de diferentes medidas para su posterior venta a sus clientes! Besea conocer los costos de su lote de importación la cual depende del valor del Bólar y del porcentaje de flete que cobra el transportista! )e sabe que el flete actualmente es del 2U del valor de la mercancía y que el transportista piensa aumentarlo a U! (or otro lado& como el valor de cambio del Bólar fluct4a diariamente debemos predecir el precio del mismo para la fec*a de la negociación& Csuponga que *oy es 5unes y el trato se *ará el Hiernes de la misma semanaD& por lo tanto& le *an encomendado a 1d! realizar una tabla con la información que le permita tomar una decisión tentatia antes de la pró#ima semana! )e *a resaltado la palabra tentativa porque el estudio de escenarios sirve para orientar las decisiones& pero la dinámica de los negocios puede arrojar otras! El resto de la información suministrada es la siguiente< El valor actual del Bólar es de 6s! !20 por cada Bólar! )iempre se compran 0 unidades de cada lámina y son cuatro tipos de lámina C-& 2-& 2!- y J-D! El costo unitario es respectivamente & I& K y 0 Bólares! 5a variación del Bólar se calcula para el Hiernes a 6s! !J00& !00 ó !00!  

 

(uede implementar los cuatro escenarios Cactual y las tres variacionesD y elaborar un informe resumenF! 5a solución está en detalle a partir de la siguiente página!

Pág 162

Manual de Excel NIVEL AVANZADO %omenzamos preparando la información básica que ya disponemos& es decir& creando un marco de trabajo donde se definen columnas y filas con los datos iniciales del trabajo! El ejemplo se ilustra en la siguiente gráfica!

%omenzamos agregando los datos que no necesitan ning4n tipo de formulas ni funciones& con una simple trascripción

5uego procedemos a nombrar las celdas que contienen los valores para el Bólar y el porcentaje de ;lete que cobra el proveedor! Esto nos será 4til para el paso siguiente! )eleccione las cuatro celdas tal y como se ven aquí!

?aga clic aquí y luego en

Estas serán nuestras ariables de datos& recuerde esta los comandos del men4

definición porque más adelante se usará muy seguido

+=ombre- y +%rear-! para referirse a ellas!

"l aparecer esta ventana solo *aga clic en aceptar& esto nombra las celdas E2 y EJ usando el te#to de la izquierda& es decir el de

Pág 163


Bos nuevos nombres se agregan y si *acemos clic en uno de ellos& nos envía a la

5o siguiente es agregar las columnas calculadas& veamos<

5a columna +%osto 1nit 6s!- es resultado de multiplicar el costo unitario en Bólares Ccolumna BD por los 6olívares por Bólar! %on simples clics en las celdas adecuadas podemos realizar la fórmula& las cual se ve en la 6arra de ;órmulas del ejemplo! 5as siguientes columnas e#presan sus valores así! Costo ateriales columna FD* %antidad C%olumna %D # %osto 1nitario 6s!C%olumna ED! 9s! Flete columna D* %osto Materiales # ;lete! C la celda E3D Costo #otal columna HD* %osto materiales Ccolumna ;D # 6s! ;lete Ccolumna >D! 5uego agregamos los totales para las columnas ;& > y ? y obtenemos<

Pág 164

Manual de Excel NIVEL AVANZADO 1na vez preparado toda el área de trabajo inicial& es cuando vamos a trabajar con la *erramienta +Escenarios-! (rimero *acemos clic en esta opción!

D 5a ventana del administrador de escenarios posee un panel que nos permite crear& modificar y eliminar entre otras funciones] para comenzar *aremos clic en el botón "gregar& pues es allí donde se crean los nuevos escenarios!

2D "l *acer clic en "gregar& aparece la ventana de modificación de escenarios& en el primer cuadro asignamos un nombre al nuevo escenario Cobserve que se llama dólar J00D& las celdas cambiantes son las que contienen los valores actuales de 6s! por Bólar y U de ;lete& si queremos colocamos un comentario y *acemos clic en "ceptar!

Pág 165

Manual de Excel NIVEL AVANZADO "ctualmente los valores escritos

son 20 6s #  y 2U C0!02D& vamos a cambiar estos valores

"):

(or supuesto& luego *acemos clic en "ceptar

"l volver a E#cel vemos que los valores viejos no cambiaron!!! Eso es porque debemos *acer clic en el botó Mostrar para que se vea el escenari seleccionado! %os alores cambian Q lo resultados de la hoja tambiJn!

)i queremos agregar nuevos escenarios& solo debemos repetir el proceso y mostrar el escenario que nos interese& los valores iniciales tambin se pueden guardar como

Eliminar Q odi"icar escenarios!

1na vez creados todos los escenarios& posiblemente deseamos eliminar uno& *agamos lo siguiente<

Pág 166


:nnecesariamente *abíamos creado este escenario& ya que el estudio solo se basó en 20& J00& 00 y 00 6s! # Bólar Cver pág! 2D& por lo tanto queremos eliminarlo! Cobserve que incluso el escenario está mostradoD )encillamente *acemos clic aquí& y se elimina el escenario! (ara ver cualquier otro& lo seleccionamos y *acemos clic en Mostrar! )i lo que quiere es modificar el escenario seleccionado& *aga clic aquí y verá las mismas ventanas e#plicadas cuando agregó el escenario! +F($E $EME ,E EMCE$+(M!

(ara el ejemplo desarrollado en la guía e#isten tres posibles res4menes& todo depende del destinatario del informe o del uso que se le dará al mismo y por lo tanto& e#plicaremos teóricamente cada uno de ellos antes de detallar la forma de crearlos! =o obvie esta e#plicación pues es el fundamento teórico de los escenarios que se crearán! Be los tres posibles crearemos aquí dos y el tercero aunque tambin será e#plicado se lo dejaremos como tarea! E#iste una relación directa entre las variables y las columnas +%osto Mater!- y +6s! ;lete-! En cambio& la relación de las variables con el costo total de cada material y la suma de ellos es del tipo indirecto& ya que estos valores son el resultado de la suma de las columnas! +n"orme 1

)e *ará utilizando las columnas +%osto Mater!- y +6s! ;lete-! El informe reflejará los cambios en estas columnas cada vez que cambien los valores de las variables! Este tipo de informe tiene como finalidad analizar por separado el costo de los materiales y el flete!

+n"orme 2

)e *ará utilizando la columna del +%osto 9otalde los cuatro ítem! El informe reflejará los cambios en esta columna cada vez que cambien los valores de las variables! Este tipo de informe tiene como finalidad analizar el importe de pago por cada tipo de ítem!

+n"orme 3

)e *ará utilizando solamente la celda que contiene el total general a pagar! Este tipo de informe se *ace cuando sólo interesa saber los que se debe pagar en global sin analizar ítem por ítem!

Pág 16

Manual de Excel NIVEL AVANZADO "*ora veamos como se *ace< En el administrador de escenarios *acemos clic en el botón siguiente ventana<

y aparece la Escogemos el tipo resumen! 5as celdas resultantes son los valores del costo de los materiales y de los 6s! (or

flete& las cuales se ven aquí 5uego *acemos clic en el botón "%E(9"3 y se crea una nueva *oja con los siguientes valores<

En lugar de los nombres de los materiales& aparecen las celdas que contienen los valores de resultado 5ea la siguiente nota< =otas< 5a columna de valores actuales representa En cambio& las celdas

en el momento encambiantesquesecreóestánel:nforme

5a columna valores actuales muestra los del escenario que eliminamos en el punto anterior! Esto se *izo intencionalmente para que se entienda la diferencia entre el valor inicial y el valor que se muestra como actual y que está +montado- justo cuando se *ace el informe! Mi )uiere )ue en los alores actuales se los valores de las celdas cambiantes

ean losresumen inicialesB debe -rimero mostrarlos Q luego de escenario! 5as celdas cambiantes de crear el in"orme!

cada escenario se muestran en gris! Ello significa que cada escenario que 1d! creó está e#presado en el informe entre las columnas B y ? Cambas inclusiveD! 5a primera oración e#plica el contenido de la columna B y la segunda e#plica el contenido de las filas I y $ con las columnas E& ;& > y ?! "*ora vamos a corregir el informe para que se vea mejor estticamente y para evitar que se vea el escenario eliminado Ccon el Bólar a !I00 6s!D! ! Holvemos a la *oja de escenarios *aciendo clic en la pesta'a con el mismo nombre! 2! =ombre cada celda que contiene los resultados para materiales y fletes! Pág 16'


"l escribir uno a uno el nombre de cada celda presione Enter!

1d! puede usar el nombre que le plazca para cada celda& pero recuerde ese es el nombre con el que se identificará cada celda de resultado en el informe& sin embargo& y si lo desea& tome esta sugerencia< Celda ombre

EI Mater50 E$ Mater502 EK Mater50J E Mater50 ;I ;lete50 ;$ ;lete502 ;K ;lete50J ; ;lete50 J! %argue el administrador de escenarios y muestre el escenario llamado :=:%:"5! ! "*ora vea que se *a creado una nueva *oja llamada +3esumen de escenario 2-!

"*ora sí& los nombres están en castellano!!!

!!!y la columna Halores actuales refleja el escenario inicial de 20 6s! # Bólar

Este escenario coincide con el actual!

Pág 16.

Manual de Excel NIVEL AVANZADO "*ora *aremos el segundo informe& obviamente& omitiremos algunos pasos para no ser repetitivos! En este informe usamos como celdas resultantes sólo las del costo total Csin incluir el costo total generalD! o

olide colocarle nombres a cada una! 5uego de *acer clic en "ceptar

veremos!!!

Observe lo siguiente< ! 5as celdas de resultado están nombradas en castellano& lo cual le da una mejor presentación al reporte! Csi usó otros nombres& no importa& estos nombres son sólo un ejemploD! 2! "cá no *ablamos de materiales ni de fletes& estamos *ablando de la suma de los dos& por tanto& los montos reflejados en los escenarios representan a los totales de cada ítem! J! ;íjese que los valores de las celdas cambiantes son iguales a los del ejemplo anterior& sólo cambió el resultado que se refleja en el reporte! ! 5a nueva *oja se llama +3esumen de escenario J-! %on estos ejemplos sabemos que es capaz de *acer el tercer informe! %omo sugerencia final dise'e sus propios escenarios sobre casos *ipotticos! (or ejemplo< ! "umentos salariales del 0U& U y 20U para un grupo de trabajadores! 2! %ambio en el porcentaje de comisiones por venta de U& IU y KU para un grupo de vendedores! J! %ostos de mano de obra de una construcción si se emplean 2& J0& J o 0 obreros pagándoles salario mínimo a cada uno!

Pág 10

Manual de Excel NIVEL AVANZADO BUSCAR OBJETIVO Esa -erramiena "unciona como una 0regla de res0 en Ecel. % sea, e permie calcular #ue )alor debe omar una celda para poder alcan!ar un )alor deerminado en ora #ue es+ )inculada por medio de una "rmula a la primera. Es decir, podr+s ajusar el )alor de una celda para obener un resulado espec5co en ora. Ej Cu+no deben ser las )enas del produco 8 para alcan!ar una uilidad del :.

Eemplo No! "#

;enir cu+no debe ser la asa de inere al menú -erramienas 2. Elige el comando B$scar Obeti%o. 'parece el siguiene cuadro

4. En el cuadro Defnir la celda debes colocar la re"erencia de la celda a la #ue deseas dar un )alor espec5co, en ese caso ser+ la celda de la cuoa ja ?*, #ue es la #ue deseamos lle)ar a 1=.===. Esa celda debe conener una "rmula /a #ue debe depender de oras para su c+lculo.

*. En el cuadro con el valor coloca el )alor #ue #uieres dar a la celda. En ese caso ser+ de @1=.=== recuerda #ue el )alor debe ener el mismo signo #ue iene la cuoa originalmene, o sea, negai)o.

6. En para cambiar la celda escribe la re"erencia de la celda #ue )a a cambiar -asa lograr el objei)o. En ese caso ser+ la asa de iner
Pág 11

Manual de Excel NIVEL AVANZADO 6. (resiona el bon acepar. 'parecer+ la siguiene in"ormacin

Ese cuadro e indicar+ si se -a podido o no alcan!ar el objei)o / -asa #ue )alor logr Ecel aproimar el )alor de la celda al )alor deseado. '#u5, por ejemplo e dice #ue alcan! el objei)o / #ue el )alor acual de la celda es igual al )alor deseado @1=.===, para lo cual se necesiar+ #ue la asa de iner
Ejemplo No. 2: 6efinir cu#ntos de"en ser los períodos si deseo ,ue la cuota sea de 190000 Para ello sigue este procedimiento( 1 e al menú herramientas 2 Elige el comando Buscar Objetivo : En el cuadro Definir la celda+ en este caso ser# la celda de la cuota fija ;'9<+ ,ue es la ,ue deseamos llevar a 190000 * En el cuadro con el valor coloca el valor ,ue ,uieres dar a la celda En este caso ser# de =190000 ;recuerda ,ue el valor de"e tener el mismo signo ,ue tiene la cuota originalmente+ o sea+ negativo< 9 En para cambiar la celda escri"e la referencia de la celda ,ue va a cam"iar hasta lograr el o"jetivo En este caso ser# el número de períodos ;'2< Esta celda de"e ser un valor constante ya ,ue Excel cam"iar# el valor actual de la celda por el valor ,ue de"e tener para alcan$ar el o"jetivo >inalmente el cuadro ,uedar# así(

) Presiona el "otón aceptar -parecer# la siguiente información(

Pág 12


Este cuadro te indicar# si se ha podido o no alcan$ar el o"jetivo y hasta ,ue valor logró Excel aproximar el valor de la celda al valor deseado -,uí+ por ejemplo te dice ,ue alcan$ó el o"jetivo y ,ue el valor actual de la celda es igual al valor deseado = 190000 y se necesitar#n )*0 períodos aproximadamente para alcan$ar esta cuota Ejemplo No. 3: 6efinir cu#ntos de"e ser el pr?stamo si deseo ,ue la cuota sea de 190000 Para ello sigue este procedimiento( 1 e al menú herramientas 2 Elige el comando Buscar Objetivo : En el cuadro Definir la celda+ en este caso ser# la celda de la cuota fija ;'9<+ ,ue es la ,ue deseamos llevar a 190000 * En el cuadro con el valor coloca el valor ,ue ,uieres dar a la celda En este caso ser# de =190000 ;recuerda ,ue el valor de"e tener el mismo signo ,ue tiene la cuota originalmente+ o sea+ negativo< 9 En para cambiar la celda escri"e la referencia de la celda ,ue va a cam"iar hasta lograr el o"jetivo En este caso ser# el valor del pr?stamo ;'1< Esta celda de"e ser un valor constante ya ,ue Excel cam"iar# el valor actual de la celda por el valor ,ue de"e tener para alcan$ar el o"jetivo >inalmente el cuadro ,uedar# así(

) Presiona el "otón aceptar -parecer# la siguiente información(

Pág 13

Manual de Excel NIVEL AVANZADO Este cuadro te indicar# si se ha podido o no alcan$ar el o"jetivo y hasta ,ue valor logró Excel aproximar el valor de la celda al valor deseado -,uí+ por ejemplo te dice ,ue alcan$ó el o"jetivo y ,ue el valor actual de la celda es igual al valor deseado = 190000 y el pr?stamo para alcan$ar esta cuota ser# de :2@*A:A SOLVER El &olver es la herramienta principal del an#lisis de sensi"ilidad+ esta herramienta optimiza los modelos sujetos a restricciones+ como los modelos de programación lineal y no lineales+ la cual permite o"tener las soluciones óptimas para un modelo determinado Esta herramienta tiene un gran nivel de complejidad+ pero en este curso veremos sólo la parte "#sica de resolución de algunos pro"lemas de programación lineal 3ormalmente encontrar#s el &olver en el menú herramientas En caso de ,ue no apare$ca de"er#s instalarlo BómoC 1 e al menú herramientas 2 a$ clic en complementos 4e aparecer# el siguiente cuadro(

: -ctiva la casilla de verificación Solver * a$ clic en -ceptar 6e"es tener el 6 de instalación de %icrosoft 5ffice+ pues es muy facti"le ,ue te lo solicite para poder instalar los complementos Para tra"ajar con el solver de"es tener muy claro cu#les son las celdas ,ue en el modelo est#n sujetas a cam"ios y cu#les son las restricciones ,ue tienen dicho modelo

Pág 14

Manual de Excel NIVEL AVANZADO uando activas la herramienta &olver+ se activa el siguiente cuadro de di#logo(

%iremos algunos de los componentes de este cuadro( 1 Celda objetivo: es la celda ,ue contiene el valor ,ue se desea optimi$ar ;maximi$ar+ minimi$ar o llevar a un valor determinado< 3ecesariamente+ esta celda de"e contener una fórmula ;c#lculo< 2 Valor de la celda objetivo: asta dónde deseas llevar la celda o"jetivo+ al m#ximo+ al mínimo o a un valor determinadoD en este último caso de"es ingresar el valor en el cuadro : Cambiando las celdas: Especifica las celdas ,ue pueden ajustarse hasta ,ue se satisfagan las restricciones y se alcance el o"jetivo del pro"lema Estas celdas de"en estar directa o indirectamente relacionadas con la celda o"jetivo * Estimar: oloca todas las celdas ,ue no contienen c#lculos como celdas ajusta"les del modelo en el cuadro cambiando las celdas. 9 Sujetas a las siuientes restricciones: Este cuadro muestra las restricciones ,ue tiene el modelo en el momento ) !rear: Permite colocar una nueva restricción al modelo -l presionar este "otón se despliega el siguiente cuadro(

= Re"erencia de la celda: en este cuadro se de"e colocar el rango de celdas sujetas a restricción

Pág 15

Manual de Excel NIVEL AVANZADO ( %uestra el sím"olo de comparación o de relación entre el cuadro de la i$,uierda y el de la derecha - Restricci#n: se refiere a un número+ una referencia de celda o de rango+ o una fórmula ,ue limitan los datos del cuadro de la i$,uierda - Bot#n aceptar: utili$a este "otón para finali$ar la entrada de restricciones y cerrar el cuadro de di#logo - Bot#n arear: utili$a este "otón para insertar una nueva restricción sin cerrar el cuadro de di#logo -

@ Cambiar...: Permite modificar las restricciones del pro"lema A Eliminar...: Puedes usar este "otón para eliminar una restricción  Opciones...: ontrola las características avan$adas del proceso de solución+ cargarse o guardarse modelos+ esta"lecer límites a la velocidad de la solución ;tiempo+ iteraciones+ precisión+ tolerancia<+ tipo de modelo ;lineal o no lineal< y definir otros par#metros ada opción tiene una configuración predeterminada adecuada a la mayoría de los pro"lemas En este ítem es adecuado ;para la mayoría de los pro"lemas< activar la opción asumir no negativos+ para evitar ,ue las celdas ajusta"les asuman valores mayores ,ue cero

10 Restablecer todo: 'orra todas las opciones del cuadro de di#logo para ,ue puedas configurar otro modelo

Ejemplo No. $: El o"jetivo del ejemplo( Encontrar el margen de "eneficio anual m#ximo ;maximi$ar la celda >29<

Pág 16

Manual de Excel NIVEL AVANZADO eldas ,ue variar#n o ,ue se ajustar#n para alcan$ar el o"jetivo( las unidades trimestrales y el costo trimestral por pu"licidad 4eniendo en cuenta ,ue( !as unidades trimestrales tienen ,ue ser mínimo :000 ada trimestre se de"e invertir algo en pu"licidad

El costo anual de pu"licidad de"e estar entre 100 y 200 mil El costo total en el aFo no puede exceder los )00000 Para resolver este ejemplo de"es seguir este procedimiento( 1 Entra al menú erramientas 2 Elige el comando &olver : !lena el cuadro de acuerdo con lo siguiente( Celda objetivo: es la referencia de la celda para la cual ,uieres alcan$ar un valor+ en el ejemplo es la celda ,ue contiene el margen de "eneficio ;>29< Valor de la celda objetivo: es el valor ,ue deseas ,ue alcance la celda o"jetivo En este caso elegiremos %#ximo Cambiando las celdas: u#les son las celdas cam"iantes para poder resolver el modelo+ para el caso las únicas celdas ,ue puedo variar son( las unidades trimestrales ;'12(E12< y el costo trimestral por pu"licidad ;'1(E1< * -grega las restricciones Restricciones: son cada uno de los límites ,ue tiene el modelo En este caso las restricciones son( !as unidades trimestrales tienen ,ue ser mínimo :000 ;'12(E12 8G :000< - ada trimestre se de"e invertir algo en pu"licidad ;'1(E1 8G 0< - El costo anual de pu"licidad de"e estar entre 100 y 200 mil ;se divide en dos restricciones( >1 8G 100000 y >1 HG200000< - El costo total en el aFo no puede exceder los )00000 ;>22 HG )00000< -

!as restricciones se de"en entrar haciendo clic en el "otón agregar Por ejemplo la primera restricción ,uedaría(

&i deseas insertar otras restricciones sólo ha$ clic en -gregar sino ha$ clic en -ceptar >inalmente el cuadro ,uedar# así(

Pág 1


9 a$ clic en resolver y Excel te mostrar# lo siguiente(

En este cuadro se te informa lo ,ue sucedió con tu modelo+ en este caso &olver pudo encontrar una solución y te da la posi"ilidad de verla ;utili$ar solución de &olver< o de regresar a los valores originales y+ adem#s te permite generar : tipos de informes ada tipo de informe ,ue se especifi,ue se coloca en una hoja independiente !os tres tipos de informes son( %n"orme de Respuestas: muestra una lista con la celda o"jetivo y las celdas ajusta"les con sus valores originales y sus valores finales+ las restricciones y la información acerca de las mismas - %n"orme de Sensibilidad: facilita información acerca de la sensibilidad de la solución a ,ue se realicen pe,ueFos cam"ios en la fórmula de la celda o"jetivo o de las restricciones 3o se genera este informe para los modelos ,ue tengan restricciones enteras - %n"orme de L&mites: muestra una lista con la celda o"jetivo y las celdas ajusta"les con sus valores correspondientes+ los límites inferior y superior así como los valores del o"jetivo 3o se genera este informe para los modelos ,ue tengan restricciones enteras El límite inferior es el valor mínimo ,ue puede tomar la celda ajusta"le mientras se mantienen todas las dem#s celdas ajusta"les fijas y se continúa satisfaciendo las restricciones El límite superior es el valor m#ximo 4am"i?n puedes .uardar el escenario+ o sea+ guardar el modelo despu?s de ejecutar el solver como un escenario En nuestro ejemplo+ el informe de respuestas se mostraría así(

Pág 1'


#eclas de Funcin en icroso"t Ecel Funcin

#ecla &"

Mhi"t

"brir ventana de ayuda

:r a

Modificar el comentario de la celda activa (egar una función en una formula 3epetir el ultimo 6uscar C6uscar siguienteD Men4 EdiciónP6uscar

:r al siguiente (anel

:r al (anel "nterior

"gregar selecciones m4ltiples de celdas %alcular la *oja activa

&"/

Men4 ?erramientasP Ortografía "mpliar una selección de celdas %alcular todas la *ojas de todos los libros abiertos "ctivar la barra de men4

&""

%rear un grafico

&' &( &) &* &+ &, &&.

&"'

#ecla

Modificar la celda activa (egar un nombre en una formula 3epetir la ultima acción

Men4 "rc*ivoP >uardar como

(resentar un men4 conte#tual :nsertar una nueva *oja de calculo Men4 "rc*ivoP >uardar C%trl@>D

Funcin

Mhi"t

Ctrl

lt

"brir o cerrar el (anel de 9areas

:nsertar una *oja de grafico Men4 "rc*ivoP>uardar como Men4 :nsertarP=ombreP%rear %errar la ventana de E#cel

Befinir un nombre de rango %errar la ventana del libro actual 3estaurar el tama'o de la ventana :r a la siguiente ventana del libro Mover la ventana del libro actual %ambiar el tama'o de la ventana Minimizar la ventana del libro activo Ma#imizar o restaurar la ventana del libro actual :nsertar una *oja de Macros CE#cel !0D Men4 "rc*ivoP "brir

:r a la primera ventana del libro anterior Mostrar la ventana de diálogo Macro

"brir el Editor de Hisual 6asic

Ctrl

lt

Cdi"o# ASC66 &ara carac%ere# e#&eciale#:

"lt @ J

V

"lt @ 2



Pág 1.

Manual Excel Nivel Avanzado

Recommend Documents