excel avanzado curso entero

EXCEL AVANZADOVersion imprimible

Este documento deber servir exclusivamente como material de apoyo al contenidodel curso interactivo accesible a travs de internet.

IndiceMdulo I. Trabajo con Excel..........................................................

U.D. 1. Manejo de varios documentos. El men Ventana.......... 3U.D. 2. Manejo de varias hojas de clculo............................ 6U.D. 3. Herramientas avanzadas de edicin......................... 8U.D. 4. Personalizacin de las barras de herramientas........... 12U.D. 5. Macros........................................................... 18Prctica. Aplicar validacin y formato condicional................. 25

Mdulo II. Frmulas y funciones.....................................................U.D. 1. Frmulas......................................................... 27U.D. 2. Referencias...................................................... 29U.D. 3. Funciones........................................................ 31U.D. 4. Tablas de datos de una y dos variables.................... 39Prctica. Insercin y copiado de frmulas y funciones............ 44Prctica. Creacin de una tabla de doble entrada................. 45

Mdulo III. Filtro y orden..............................................................U.D. 1. Ordenar datos................................................... 47U.D. 2. Autofiltro..........................................................52U.D. 3. Filtro avanzado................................................. 58Prctica. Uso del orden y filtros....................................... 63

Mdulo IV. Subtotales y tablas dinmicas.........................................U.D. 1. Subtotales....................................................... 64U.D. 2. Tablas dinmicas............................................... 70U.D. 3. Grficos dinmicos............................................. 82Prctica. Totalizar por grupos..........................................92

Plataforma de Teleformacin de IFES Pgina 1 de 120

Prctica. Tabla y grfico dinmico.................................... 93Mdulo V. Herramientas avanzadas................................................

U.D. 1. Auditora......................................................... 95U.D. 2. Buscar objetivo................................................. 98U.D. 3. Escenarios..................................................... 101U.D. 4. Solver........................................................... 110Prctica. Usar escenarios para tomar decisiones................ 118Prctica. Usar solver para tomar decisiones...................... 119Links de inters......................................................... 120

EXCEL AVANZADO

EXCEL AVANZADO

Pgina 2 de 120 EXCEL AVANZADO

Mdulo I. Trabajo con Excel.U.D. 1. Manejo de varios documentos. El men Ventana.

U.D. 1. Manejo de varios documentos. El menVentana.

Si bien MS Excel es una aplicacin para crear hojas de clculo, a cada uno de losdocumentos (o archivos con extensin XLS) que se generan con este programa se lesllama "libros" ya que engloban varias hojas en el mismo, accesibles desde la zonainferior izquierda de la pantalla, como ya se profundizar en la siguiente unidaddidctica.

Cuando tengamos abiertos ms de dos libros o documentos a la vez, podremos pasarde uno a otro mediante el men Ventana.

EXCEL AVANZADO


Segn qu sistema operativo estemos usando tambinpodremos cambiar de documento haciendo uso de labarra de tareas para cambiar de ventana o de lacombinacin de teclas Alt+Tabulador.

Control de distintas zonas de la hojaA menudo se hace necesario ver dos partes de la misma hoja a la vez aunque no estnjuntas, para ello el men Ventana nos ofrece tres posibilidades:

Inmovilizar paneles. Muy til cuando queremos fijar las primeras filas y/ocolumnas, de forma que no desaparezcan de nuestra vista cuando nosdesplazamos hacia abajo y/o hacia la derecha. Por ejemplo para dejar fijoslos encabezados de una tabla de datos.

EXCEL AVANZADO


Si queremos que dejen de estar fijas ests zonas elegiremos Ventana/Movilizarpaneles.

Dividir. Con esta opcin podemos ver hasta cuatro zonas distintas de lahoja, independientemente de su situacin, por ejemplo cuando queremos vercomo cambia el total de una factura abajo del todo mientras cambiamosvalores mucho ms arriba.

Si queremos volver a ver una sola zona elegiremos Ventana/Quitar divisin.

Resulta ms rpido arrastrar los

manipuladores que tenemos encima y a la derecha delas barras de desplazamiento vertical y horizontal,respectivamente.

EXCEL AVANZADO


Nueva ventana. Abre otra ventana del mismo documento de forma quese puede visualizar otra zona distinta a la de la ventana original, a la quepodremos volver por el men Ventana tal y como se ha explicado al principiode esta unidad. Si guardamos un documento visualizado en varias ventanas,seguir presentndose de esta forma la prxima vez que lo abramos.

Abrir una nueva ventana es la nica forma de ver a lavez dos hojas del mismo libro. Una vez se ejecuta elcomando habra que ir a Ventana/Organizar... yelegir Mosaco, Horizontal o Vertical.

U.D. 2. Manejo de varias hojas de clculo.U.D. 2. Manejo de varias hojas de clculo.Hemos visto que al lado de la barra de desplazamiento horizontal tenemos unassolapas selectoras de hojas. Pulsando sobre ellas con el botn izquierdo del ratn,cambiamos entre las distintas hojas que puede contener un mismo documento XLS.

El nmero de hojas que tendr un documento nuevo pordefecto es el establecido enHerramientas/Opciones/Pestaa General/Nmero dehojas en nuevo libro.

EXCEL AVANZADO


Pulsando en una de esas solapas sobre el botn derecho del ratn nos aparecen lassiguientes opciones:

Insertar una nueva hoja inmediatamente a la izquierda de la actual Eliminar la hoja actual (nos pedir confirmacin) Cambiarle el nombre a uno ms descriptivo que Hoja1... Moverla o copiarla a otro sitio en la ordenacin que llevan las hojas Seleccionar todas las hojas de vez (para imprimirlas o copiarlas, por

ejemplo)Los botones , que estn al lado de las solapas, sirven para buscar alguna queen un momento dado no nos sea visible. Nos llevan, respectivamente, a ver la primerasolapa, la anterior, la siguiente y la ltima. Si pulsamos sobre ellos con el botnderecho, nos aparece un men conceptual con el que nos podemos dirigir directamentea una hoja concreta ya que se ven los nombres de todas.

EXCEL AVANZADO


Para hacer referencia a una celda de otra hoja dentro delmismo libro o documento de Excel, se antepone a lareferencia normal el nombre de la hoja seguido de unaexclamacin !. Por ejemplo: Hoja2!H8 se refiere a lacelda H8 de la hoja Hoja2.

U.D. 3. Herramientas avanzadas de edicin.

U.D. 3. Herramientas avanzadas de edicin.

VALIDACINMediante la herramienta de validacin se puede controlar que el valor que se introduceen una celda cumple determinados criterios. Para usarla hay que seleccionar la celda orango en cuestin e ir a la opcinDatos/Validacin..., donde tendremos queespecificar:

EXCEL AVANZADO


El tipo de dato a permitir: cualquiera, nmeros enteros, valores de una listasacada de otras celdas, decimales, fechas, horas, textos.

El tipo de restriccin a usar, dentro de las comparacioneslgicas: comprendido entre dos valores, distinto, menor, mayor y/o igual queotro valor.

Los valores o lmites a los que se refiere la restriccin anterior.

Tambin se puede exigir que el valor de la celda cumpla lo expresado por una frmula,donde pueden ser utiles las funciones de informacin, ESNUMERO(), ESTEXTO(),ESNOTEXTO(), etc.Existe la posibilidad de configurar que salga automticamente un mensaje en elmomento en que se entre en la celda con validacin.

EXCEL AVANZADO


Y tambin puede configurarse un mensaje de error que saldr cuando el valorintroducido no cumpla con las reglas de validacin, en cuyo caso podr tener tresestilos:

Lmite, avisar del error sin permitirnos que el valor no vlido siga en lacelda.

Advertencia, avisar y nos dejar elegir si cambiamos el valor no vlido oseguimos trabajando con l.

Informacin, slo informar de que no se cumplen las reglas de validacinsin pedirnos que lo cambiemos.

EXCEL AVANZADO


FORMATO CONDICIONAL

Se trata de formatos que se aplican automticamente siempre y cuando una celda o elresultado de una formula calculada cumpla una o varias condicines (hasta tres). Parausarlo hay que seleccionar la celda o rango en cuestin e ir a laopcinFormato/Formato condicional...,nos saldr un cuadro como el del siguienteejemplo, en el que se ha configurado que el color de relleno y del texto de las celdasseleccionadas dependa del valor de otra celda llamada "NOTA".

EXCEL AVANZADO


U.D. 4. Personalizacin de las barras de herramientas.

U.D. 4. Personalizacin de las barras deherramientas.

Las barras de herramientas constan de distintos botones y listas despegables con lasopciones ms frecuentemente utilizadas en el uso del programa, evitando al usuario elrodeo de ejercitarlas mediante opciones de men. Los botones estn decorados coniconos ms o menos representativos de su labor, adems si situamos el puntero delratn sin pulsarlo sobre cualquier botn al momento nos aparecer una pequeadescripcin de su funcin.

Algunas de uso muy comn son:

Barra Estndar

EXCEL AVANZADO


La mayora de sus botones son comunes a otras aplicaciones de Office:

-con opciones generales del documento como crear uno nuevo, abrir uno

existente, guardarlo, imprimirlo, ver como quedara antes de imprimirlo y

buscar errores ortogrficos.

-operaciones de edicin como las del portapapeles ( cortar, copiar y pegar),copiar formato y deshacer/rehacer acciones.

-botones que nos activan otras barras como Web y Dibujo.

-opciones de visualizacin como el zoom.

Si bien hay otros ms propios de una hoja de clculo como insertar una sumaautomtica (autosuma), insertar una funcin, ordenar ascendentemente unaseleccin, ordenarla descendentemente y ejecutar el asistente para grficos.

Barra de Formato

Las celdas que conforman una hoja de clculo van a tener un formato al que sesujetar su contenido. Los botones de esta barra se destinan a ese formato, y elfuncionamiento de algunos seguramente ya lo conozcamos. Los relativos al tipo deletra no cambian respecto a otros programas, la alineacin se refiere a los lmites de lasceldas.

Propios de la hoja de clculo son los siguientes botones:Funde las celdas seleccionadas en una sola, centrando en la misma el contenido

resultante.

Aplica a las celdas seleccionadas el formato numrico de porcentaje.

y Aplican el formato numrico de contabilidad, el segundo slo pone los puntos

EXCEL AVANZADO


de millar mientras que el primero aade el smbolo monetario que se establezcaenFormato/Celdas.../Nmeroo, en su defecto, enInicio/ Configuracin/ Panel deControl/ Configuracin Regional/ Moneda.

y aaden o quitan, respectivamente, un decimal redondeando la cantidad hacia

arriba o hacia abajo, segn el caso.Al aadir o quitar decimales, el valor que contiene lacelda no cambia, el resultado es slo a efectos devisualizacin. Si se quiere que los clculos se limiten alos decimales que se ven habr que ir aHerramientas/Opciones/Clculo/Precisin dePantalla.

Las paletas de bordes y rellenado permiten cambiar estas caractersticas

de las celdas seleccionadas.

Barra de Dibujo

Para trazar dibujos y cambiar sus formatos.

Barra de Imagen

Para insertar imgenes y cambiar sus matices y ajustes respecto al texto que las rodea.

Barra Web

Para explorar cualquier documento que pueda abrir Excel tanto de nuestro ordenador,

EXCEL AVANZADO


como de otro de una red local e incluso navegar por Internet.

Barra WordArt

WordArt es la denominacin de unos llamativos rtulos con ms posibilidades estticasque el texto normal y que podremos manejar con los botones de esta barra.

Barra de Grfico

Para modificar elementos y opciones de los grficos.

Manipulacin de las barras

Para activar una barra de herramientas podemos ir aVer/Barras de Herramientasoms rpido pulsando con el botn derecho sobre cualquier barra de menus o deherramientas. De ambas formas nos aparecer un men con las posibles barrasactivadas (marcadas con a) o desactivadas, pudiendo respectivamente quitar oponerlas con el botn izquierdo.

Para cambiar la ubicacin en la pantalla de las barras de herramientas

debemos considerar estas como meras ventanas de Windows, pudiendo asirlas por subarra azul de ttulo o en su defecto por una zona con dos rugosidades paralelas quetienen en su extremo, para luego arrastrar.

Para aadir y quitar botones a las barras de herramientas iremos aVer/Barras deHerramientas/Personalizary arrastrarlos desde/hasta supestaaComandos.Esto nos vendr muy bien para ubicar botones de las macros quecrearemos en un futuro. En la siguiente ilustracin se est emplazando en la barraestndar un botn para activar el autofiltro sin necesidad de ir a su correspondientemen.

EXCEL AVANZADO


Si, por el contrario, lo que queremos es eliminar botones, bastar con arrastrarlosdesde su sitio en la barra de herramientas hasta cualquier punto fuera de esa u otrasbarras. Estaremos eliminando botones si vemos que al puntero le acompaa un signoen forma de aspa "x".

EXCEL AVANZADO


Si nos encontraramos una barra con muchos botones cambiados y quisisemos quevolviera a estar como cuando se instal el programa, iremos aVer/Barras deHerramientas/Personalizar/Pestaa "Barras de herramientas"y pulsaremos elbotn"Restablecer...".

EXCEL AVANZADO


En este mismo cuadro tenemos un botn para crear una nueva barra de herramientasen blanco para personalizar aadiendo los botones que queramos.

U.D. 5. Macros.

U.D. 5. Macros.

Por macro (abreviatura de macroinstruccin) podemos entender un pequeo programacompuesto por una serie de acciones que se han grabado para su futura ejecucin enserie. Dicho programa queda elaborado en lenguaje VBA (Visual Basic paraAplicaciones). Aunque para elaborar la macro no hay necesidad de conocer dicholenguaje, un mnimo conocimiento del mismo viene muy bien para conseguirpersonalizarla y adaptarla mejor a nuestras necesidades.

EXCEL AVANZADO


Para grabar una macro, hay que ir aHerramientas/Macro/Grabar macro...,darle unnombre (sin espacios) a la futura macro y cuando aceptemos, la macro empezar agrabar nuestros movimientos hasta que accionemos el botn Detener grabacin de labarra del mismo nombre.

Para editar una macro, hay que abrir el editor Microsoft Visual BasicdesdeHerramientas/Macro/Editor de Visual Basico pulsar el botn Modificar delcuadro Macro deHerramientas/Macro/Macro....

EXCEL AVANZADO


Lo mejor para entender las macros, sin duda alguna, es ir creando algunas,empezando por procedimientos fciles e introduciendo maniobras ms complicadaspaulatinamente. Aunque el lenguaje en el que quedan almacenadas las macros esMicrosoft VBA (Visual Basic for Applications), se pueden hacer macros sencillas comola siguiente sin necesidad de conocer dicho lenguaje de programacin. Pero a pocoque persigamos unos resultados ms sofisticados necesitaremos conocimientos deVBA, sean superficiales o en profundidad, para retocar el cdigo que construyeautomticamente Excel al grabar la macro.

Grabar una macro se podra comparar con grabar un mensaje en un contestadorautomtico. Lo hacemos para automatizar algo que puede hacer una mquina ennuestro lugar un nmero indefinido de veces. Esa comodidad supone un esfuerzoinicial de indicarle minuciosamentea y punto por punto lo que la mquina deber repetiren el futuro. De hecho, todo lo que grabemos, sea necesario o no (porejemplo, moverse por la hoja con las barras de desplazamiento) ser repetido cada vezque ejecutemos la macro, al igual que si estornudamos al grabar el mensaje, se oir elestornudo cada vez que salte el contestador. Por ello es muy importante planificartodos los pasos antes de empezar, as como asegurarse de que ninguno de los pasosest ya hecho, para que haya que hacerlo de nuevo y as no se quede fuera de lagrabacin.

Ejemplo: Macro que borre slo ciertas celdasEn este sencillo ejemplo, vamos a hacer que la macro borre las celdas que contienenlos datos variables de la plantilla de una factura, para facilitar la tarea a usuariosposteriores y evitar posibles despistes y destrozos involuntarios. Vayamos paso por

EXCEL AVANZADO


paso:

1. Vamos aHerramientas/Macro/Grabar macro...,para especificar el nombre de lamacro (sin espacios), si queremos disponer de ella en futuros libros o slo en este yreflejar algn comentario que nos ayude a recordarla en futuras revisiones.

2. Realizamos los pasos a grabar, en este caso seleccionar las celdas que queremosque se borren, aunque realmente estn en blanco...

...y despus borrarlas con la tecla "Supr" o con la opcinEdicin/Borrar/Contenido.

EXCEL AVANZADO


Ntese que no hemos seleccionado las celdas con frmulas, como las que calculan lossubtotales, pues valdrn cero cuando borremos los valores de las columnas "Cantidad"y "Precio" y adems necesitamos que la frmula permanezca intacta para futuros usosde esta hoja (como si de una plantilla se tratara).

Si olvidamos detener la grabacin con el botn de la ilustracin anterior se seguirngrabando todos nuestros movimientos posteriores y que no tienen sentido en nuestramacro. Aunque pueda parecer raro, es un despiste muy habitual cuando se hacen lasprimeras macros.

Un fallo relativamente habitual que debemos evitar al nodetener la grabacin es grabar incluso el momentoposterior de comprobarla con lo que se produce un errorde "memoria insuficiente", debido a que Excel nuncaparara de ejecutar la macro, pues una de susinstrucciones es ponerse en marcha de nuevo a ellamisma, entrando en un bucle infinito.

EXCEL AVANZADO


Con la accin anterior ya tenemos el botn preparado aunque de momento no seproduce accin alguna al interactuar con l, por lo que hay que asignarle una macrocomo pasamos a ilustrar. Empezamos haciendo un clic derecho, eligiendo laopcin"Asignar macro..."para seleccionar la macro por su nombre.

EXCEL AVANZADO


Obsrvese en el men contextual de la ilustracin quehay ciertas opciones para darle un aspecto mspersonalizado al botn como elegir que su aparienciasea la de un icono (estilo predeterminado), un textodescriptivo o ambas cosas. Adems se puede cambiar elicono por otros preestablecidos, modificarlo (con unrudimentario editor de mapa de bits a tal efecto) oincluso pegar cualquier imagen de pocos pixeles quetengamos en el portapapeles.

A partir del momento en que cerramos el cuadro de personalizar ya tendremos el botnpreparado para desencadenar mediante una pulsacin todas las acciones que tenga lamacro para la que est signado. En este caso: seleccionar determinadas celdas, borrarsu contenido y desplazarse a otra celda para anular la seleccin.

Dada la dificultad que entraa confeccionar macros, no nos extrae tener fallos en loscomienzos. Si vemos que el resultado no es el deseado o queremos perfeccionarlatenemos dos vas:

Si no tenemos conocimientos de VBA (Visual Basic for Applications, lenguaje

EXCEL AVANZADO


de programacin que no nos hace falta para hacer los pasos anteriores peroque es en el que quedan almacenadas las macros para futurasmodificaciones) no nos queda ms remedio que volver a grabarla desdecero, reemplazando a la existente.

Con unas mnimas nociones de VBA, podremos adentrarnos en el editor de VBA yhacer los cambios pertinentes en su cdigo, ahorrndonos la molestia de repetir lagrabacin. Este es un motivo de peso por el que se propone al alumno interesado enampliar conocimientos al respecto que prosiga con el aprendizaje de VBA. Se accedemediante la opcinHerramientas/Macros/Editor de Visual Basico mediante elbotn"Modificar"de la opcinHerramientas/Macros/Macros...y presenta un aspectocomo el de la siguiente ilustracin.

Prctica. Aplicar validacin y formato condicional.

Prctica. Aplicar validacin y formatocondicional.

Esta prctica consiste en dotar a una hoja como la de la izquierda de las caractersticasque tiene la de la derecha:

EXCEL AVANZADO


Validacin: que en las notas del rango C1:D16 slo se puedan introducirvalores de 0 a 10 sin decimales, saltando un mensaje de error en caso deque se intente teclear un valor no vlido.

Formato condicional: que cada fila adopte un color en funcin del valor quetenga la calificacin correspondiente de la columna E.

Para ello descarga el archivo de Excel que se te facilita (para que no lo abra tunavegador, haz un clic derecho en el enlace anterior y elige "Guardar enlace como..." o"Guardar destino como..."). Una vez est descargado en tu disco, desarrolla laspropuestas anteriores en la hoja llamada "Hazlo aqu". Cuando termines, manda elarchivo resultante al tutor mediante el correo de la plataforma.

EXCEL AVANZADO


Mdulo II. Frmulas y funciones.U.D. 1. Frmulas.

U.D. 1. Frmulas.

Bsicamente, una celda tiene contenido y formato.

El contenido es la informacin que alberga, puede ser de dos tipos:

dato, depende directamente de lo que introduzca el usuario. frmula, es un clculo cuyo resultado depende de sus operadores, sean

valores constantes o variables (los de otras celdas que, a su vez, pueden serdatos o frmulas).

El formato se refiere a la presentacin del contenido en la cuadrcula (tipo de letra,alineacin, bordes, tramas y formatos numricos ms all de lo meramente estticocomo el nmero de decimales, el smbolo monetario, el porcentaje etc).

Dotar de contenido a las celdas

Introducir datos y modificarlos

Para ello basta con activar la celda en la que queramos actuar y teclear su contenido, sibien es necesario un ltimo paso: el dato no se considerar introducido hasta que seactive otra celda (con Intro, Tabulador o Flechas de desplazamiento).Excel distingue automticamente el tipo de dato (texto, numrico o fecha), alinendolopor defecto a la derecha de la celda, excepto los textos, que los alinea a la izquierda.

Ntese que mientras se est introduciendo un dato aparecen dos botones en la barrade frmulas:

EXCEL AVANZADO


Para cambiar un dato ya introducido, podemos:

sustituirlo en su totalidad, activando la celda en cuestin e introduciendo elnuevo dato. Ntese lo voltil que es el contenido de una celda, puescualquier cosa que se teclee dentro de ella sustituir a lo existente. Por elloser til proteger nuestra hoja para evitar errores ajenos e incluso propios.

modificar slo una parte, bien haciendo clic en el dato de la barra defrmulas o bien haciendo doble clic en la propia celda, de ambas manerasaparecer un cursor que nos facilitar esta labor.

Proteger una hoja consta de dos pasos:1) Especificar qu celdas se quiere que no estnprotegidas, es decir, en las que queremos dejar abiertala posibilidad de que sean cambiadas por futurosusuarios. Para ellos las seleccionamos y luego nosdirigimos a la pestaa Proteger de la opcinFormato/Celdas...2) Elegir la opcin Herramientas/Proteger/Protegerhoja... (este ser el mismo camino que tomaremos paravolver a desproteger la hoja)

Introducir y modificar frmulas

Se hace igual que los datos, con la diferencia de que tienen que ir necesariamenteprecedidas por el signo igual =, el cual lo podemos conseguir va teclado o pulsandosobre el botn "=" de la barra de frmulas (este ltimo mtodo tiene la ventaja de quenos da una vista previa del resultado conforme se va construyendo la frmula).De hecho, la barra de frmulas toma su nombre de que en ella aparece la expresinalgebraica de la misma, mientras que en la cuadrcula del rea de trabajo slo se ve elvalor que toma en ese momento.

EXCEL AVANZADO


A efectos de deteccin de errores, si se desea se puedehacer que en la cuadrcula aparezcan tambin frmulasen vez de valores activando la casilla o Frmulas deHerramientas/Opciones.../Ver (ah tambin se puedeespecificar si se quiere dejar de ver los encabezados ylas lneas de divisin de celdas), con el teclado es msrpido pulsando Alt+ (ordinal).

Para modificar frmulas, oprese de la misma forma que con los datos, si bien sefinalizar o con las teclas ENTER o con tabulador o con el botn de la barra defrmulas, ya que activar otra celda podra ser considerado como que se est indicandouna variable de la frmula.

IMPORTANTE:cuando se formulen expresiones algebraicas debe tenerse en cuenta la prioridad declculo que tienen unas operaciones respecto a otras y que slo se puede rompermediante el uso de parntesis, recordemos:

1) la potencia, que en Excel se expresa con el acento circunflejo ^. Ejemplos: 23 2^3 5^(1+1/2) (50*2)^(1/2)

2) el producto y la divisin, caracteres * y /, respectivamente. Ejemplos: 2x3 2*3 (5+3*2/6)/(2+7/2)

3) la suma + y la diferencia -.

U.D. 2. Referencias.

U.D. 2. Referencias.

EXCEL AVANZADO


Cuando se copia una frmula de una celda, Excel no la copia exactamente, sino quecopia la posicin relativa que ocupan las celdas referenciadas respecto a la celdaorigen que las contiene. De esta forma si la celda E5 contiene la frmula =F6+F7, sucopia en B2 resultar ser =C3+C4.

Si se quiere que alguna de las coordenadas de una referencia no vare cuandoposteriormente se copie, previamente debemos poner un signo $ delante de esacoordenada que significa que esa referencia ser absoluta y no variar cuando seacopiada. As, si E5 contiene =$F6+F$7, su copia en B2 ser =$F3+C$7. Explicado deotra forma:

referencias absolutas que no cambian: las indicadas anteponiendo elsigno "$".

referencia relativa a la fila 6: lo que en la fila 5 es 6, en la 2 ser 3. referencia relativa a la columna C: lo que en la columna E es F, en la B ser

C.

En la figura, las celdas sombreadas contienen las cuatro formas posibles de hacer unareferencia a otra celda. Cada una de ellas ha sido copiada a sus celdas contiguas pordebajo y por la derecha, obtenindose los distintos resultados que seguramentecontribuirn a una mejor comprensin de este concepto.

EXCEL AVANZADO


Una forma rpida de cambiar el tipo de una referencia espulsar la tecla F4 cuando se est con el cursor en dichareferencia, con cada pulsacin cambiar en este orden:FC $F$C F$C $CF FC $F$C F$C $CF...

U.D. 3. Funciones.

U.D. 3. Funciones.

Las funciones son expresiones cuyo verdadero valor depende de unos argumentos.Por ejemplo: los argumentos de la funcin suma son sus sumandos, de tal manera quesi esos argumentos son 2 y 3, se dice que el valor que toma (o devuelve) esa funcines 5.

Sintaxis de una funcin

Una funcin requiere que se escriba con una sintaxis concreta.

FUNCIN(argumento1;argumento2;...;...;...;argumento n)El nmero (n) de argumentos (que siempre irn separados por punto y coma ;)depende de la funcin utilizada:

la mayora tienen un nmero fijo.Por ejemplo, REDONDEAR(nmero;nm_decimales) tiene 2argumentos: el nmero que se quiere redondear y los decimales que sedesea conservar del mismo.

REDONDEAR(4,5278;2) devolver el valor 4,53.

las hay que no tienen un nmero fijo. Por ejemplo: SUMA puede tener desde1 hasta 30 sumandos.

SUMA(5;8;2;4;9;3;2;7;8) devolvera el valor 48.PROMEDIO(5;8;2;4;9;3;2;7;8) devuelve la media aritmtica de esos

EXCEL AVANZADO


argumentos, cuyo valor es 5,333.

otras no necesitan parmetro alguno, si bien conservan los parntesis:

HOY() devuelve la fecha del momento en que se calcula.ALEATORIO() devuelve un nmero al azar comprendido entre el 0 y el1.

Argumentos posibles

Los argumentos pueden ser

Constantes, es decir, nmeros, textos, fechas o valores lgicos cuyo valor nocambia en funcin del valor de otras celdas.

Los dos nicos valores lgicos son VERDADERO yFALSO. Ambos son los dos nicos resultados posiblesde evaluar una proposicin o prueba lgica, de tal formaque la prueba 35 es igual a FALSO.

Variables, valores tomados por otras celdas. Se escribir la referencia de lacelda o bien su nombre.

Como en las frmulas puede resultar incmodo el uso dereferencias como B5, se pueden definir nombres msclaros para ciertas celdas en la opcin Insertar/Nombre/ Definir...,que tiene el siguiente cuadro dedilogo:

EXCEL AVANZADO


Tambin se puede dar nombre directamente a la celda activaescribindolo a la izquierda en la barra de frmulas.

Para usar un nombre en una frmula bastar con escribirlo, si no lorecordamos exactamente podemos seleccionarlo de la lista queaparece en opcinInsertar/Nombre/Pegar....

Ejemplo: SUMA(100000;CAPITAL_PRESTADO;J5) devolver la sumadel valor constante 100.000 y los valores variables contenidos en lasceldas CAPITAL_PRESTADO (referencia nombre) y J5 (referencianormal).

Otras funciones. Una funcin secundaria queda englobada en los parntesisde otra funcin principal de la cual es argumento. Por ejemplo:SUMA(2;8;REDONDEAR(5,32187;3)) devolver el valor 15,321.

Introduccin de funciones

Una forma arriesgada de hacerlo es teclearla directamente, como cualquier otro valor.Para ello tendremos en cuenta que la frmula que contiene una celda puedecomponerse de una operacin aritmtica, una funcin o de una combinacin de ambascosas. Por lo tanto, al introducir funciones no habr que olvidarse de poner el signoigual = al principio de la frmula a la que pertenezca, o mejor an: pulsando el botn

EXCEL AVANZADO


= de la barra de frmulas.

Si la forma anterior ha sido tachada de arriesgada es porque se puede cometer erroressintacticos al mnimo despiste. Estos errores son ms prevenibles usando la opcinInsertar/Funcin... o el botn .

Lo primero que veremos ser este cuadro de dilogo:

Una vez hayamos elegido la funcin nos aparece el cuadro de introduccin deargumentos reproducido bajo esta lneas. Es fcilmente reconocible porque no tienebarra de ttulo con la que moverlo, para ello basta con pinchar en cualquier parte grisdel mismo y arrastrarlo.

EXCEL AVANZADO


En dicho cuadro nos aparecern unos campos en blanco con los distintos argumentosque se requieren dispuestos unos debajo de otros. Nosotros los rellenaremosteclendolos o bien pinchando directamente en la hoja de clculo tras minimizar estecuadro mediante el botn . El cuadro queda reducido a la zona en blanco del

campo, de esta forma:

Cuando se pulse sobre Aceptar quedar introducida la frmula. Para modificarla bastacon activar su celda y volver a Insertar/Funcin..., acto seguido nos aparecern

los argumentos de la misma.

Si hay unas funciones dentro de otras, los argumentos que aparecern sern loscorrespondientes a la funcin donde se encuentre situado el cursor, bastar moverlopara ver los parmetros de otra funcin (dentro de la misma frmula). Tal y como dicela figura de encima, para poner como argumento cualquier otra funcin hay que acudiral desplegable de la izquierda de la barra de frmulas.

EXCEL AVANZADO


FUNCIONES AVANZADASMatemticas y Trigonomtricas

SUMA(nmero1;nmero2; ...) devuelve la suma de los argumentos (puedehaber hasta 30 de ellos). Una forma muy rpida de insertar esta funcin esactivar la celda donde se quiere tener el resultado de la suma y presionar elbotn Autosuma de la barra Estndar. Si bien Excel nos propondr unrango, nosotros podremos seleccionar otro distinto, incluso de celdas noadyacentes (valindonos de la tecla Ctrl).

REDONDEAR, REDONDEAR.MAS..., hay varias formas de redondear. SUMAR.SI, suma las celdas de un rango si en otro rango encuentra cierto

valor.

Bsqueda

BUSCAR, BUSCARV, BUSCARH, buscan un valor en un rango (o matriz) ydevuelve un dato relacionado en dicho rango. Por ejemplo, busca un cdigode cliente en la primera columna y devuelve sus apellidos en otra ms a laderecha.

INDICE, devuelve de un rango el valor especificado por unas coordenadas.

Texto

CONCATENAR(texto1;texto2; ...) concatena argumentos de texto, que serno bien textos entrecomillados o bien referencias a celdas que contengantexto. Por ejemplo: CONCATENAR(Fernando; ;Garca) devolver eltexto Fernando Garca.

MAYUSC(texto) convierte el texto en maysculas, NOMPROPIO slo poneen mayscula la primera letra.

TEXTO y MONEDA, convierten cifras en textos. VALOR hace locontrario, algo muy util si involuntariamente alguna cifra de la hoja slo esconsiderada como texto pero no se tiene en cuenta su valor a la hora dehacer clculos, como ilustra la imagen bajo estas lneas. Suele pasar convalores sacados de archivos con formatos distintos al de Excel (por ejemplo,de una pgina web) o cuando a la celda se le asin en el pasado lacategora de texto en la solapa"Nmero"de la opcin"Formato/Celdas..."

EXCEL AVANZADO


Financieras

PAGO(tasa;nper;va;vf;tipo) devuelve el pago constante de un periodo en unprstamo con sistema francs de amortizacin, donde tasa es la tasa deinters, nper el nmero de periodos del prstamo, va el valor actual de losfuturos pagos (lo que se presta), vf el valor residual o saldo final (en unprstamo suele ser cero) y tipo es la modalidad del prstamo(0=postpagable, 1=prepagable).

PAGOPRIN y PAGOINT calculan, respectivamente, la parte de devolucinde principal (amortizacin del prstamo) la parte de inters de una cuotaconcreta.

Fecha y hora

AHORA() devuelve la hora y fecha del momento en que se calcula. HOY() devuelve la fecha del momento en que se calcula.

Estadsticas

PROMEDIO(nmero1;nmero2; ...) devuelve la media aritmtica de losargumentos.

CONTAR.SI(rango;criterio) devuelve el nmero de veces que se cumplecierto criterio en el rango especificado. Por ejemplo:CONTAR.SI(F4:F8;Gil).

MAX(nmero1;nmero2; ...) devuelve el valor mximo de un conjunto devalores.

EXCEL AVANZADO


MIN(nmero1;nmero2; ...) devuelve el valor mnimo de un conjunto devalores.

Lgicas

SI(prueba_lgica;valor_si_verdadero;valor_si_falso) devuelve un valor si lacondicin especificada (prueba lgica) es VERDADERO (se cumple) y otrovalor si dicho argumento es FALSO (no se cumple).

Ejemplo (siendo NOTA el nombre de una celda que contiene un nmero):SI(NOTA

siguiente texto en cualquiera de los mdulos pertenecientes al libro de clculo encuestin:

Function LETRANIF(dni As Long) As Stringla siguiente lnea extrae (Mid) de esa cadena deletrasuna (1) posicin correspondiente al resto (Mod) dedividir dni por 23, +1

LETRANIF = Mid("TRWAGMYFPDXBNJZSQVHLCKE", (dniMod 23) + 1, 1)End Function

U.D. 4. Tablas de datos de una y dos variables.

U.D. 4. Tablas de datos de una y dos variables.

Tablas de una variable

Trabajando con la hoja de clculo es habitual tener que comparar el abanico deresultados que pueden producirse al alterar el valor de una variable, almacenada enuna celda, claro est. Para ello podramos ir haciendo cambios sucesivos en esa celda,observando los efectos en los clculos e imprimiendo o anotando de alguna manera losque nos interesaran.

Creando una tabla de una variable nos podemos ahorra esa tediosa labor, facilitando elanlisis conjunto de los posibles efectos en una o ms frmulas causados por elcambio de una celda (para el caso de dos celdas, ms adelante se estudian las tablasde dos variables). Un ejemplo lo tenemos bajo estas lneas:

EXCEL AVANZADO


En el ejemplo se analiza el efecto del cambio de la variable "unidades fabricadas" (D6)en los ingresos, los gastos y los beneficios, teniendo en cuenta las frmulas de losmismos:

Ingresos = Precio Vta. * Unidades = Gastos = Coste variable * Unidades + Coste fijo = Beneficio = Ingresos - Gastos = (Precio Vta. * Unidades) - (Coste variable *

Unidades + Coste fijo) = Unidades * (Precio Vta. - Coste variable) - Coste fijo= D6 * (D5 - D4) - D3

Para ello, primero preparamos la hoja con los datos de la variable que queremoscambiar y las frmulas que hay que calcular dispuestas como en la figura que sigue:

EXCEL AVANZADO


Una vez est la hoja preparada, seleccionamos un rango rectangular con las frmulasen la primera fila (o columna) y los valores de la variable en la primera columna (o fila)y despus elegimos la opcinDatos/Tabla...

Seguidamente se nos pedir que indiquemos la celda de entrada o variable (que debeestar fuera de la tabla) en la casilla correspondiente segn los datos los hayamosdispuesto en la primera fila o en la primera columna, respectivamente. En nuestroejemplo, la celda toma los valores de la columna.

EXCEL AVANZADO


Tablas de dos variables

Al hilo de lo anterior, se suele dar el caso de que nos preguntemos por los efectos node una, sino de dos variables sobre el valor de una frmula (no se puede de ms,debido a que trabajamos slo con dos dimensiones: filas y columnas). En este casopodremos hacer tablas de doble entrada de forma similar a como hemos hecho las deuna sola variable.

En este caso el ejemplo va a analizar el efecto de dos variables (el precio en D5 y lasunidades en D6) sobre el resultado, cuya frmula (D6*(D5-D4)-D3) est en la esquinasuperior izquierda de la tabla (H3).

Para ello disponemos la tabla de forma que la frmula cuyos resultados queremosanalizar est en la esquina superior izquierda y los valores de una variable en fila a suderecha y los de la otra variable debajo en columna.

EXCEL AVANZADO


Una vez est la hoja preparada, seleccionamos un rango rectangular que englobe lafrmula de la esquina y los distintos valores a analizar y despus elegimos laopcinDatos/Tabla...

Aqu tambin se nos pedir que indiquemos las celdas de entrada o variables (que

EXCEL AVANZADO


deben estar fuera de la tabla) en las casillas correspondientes segn los datos loshayamos dispuesto en la primera fila o en la primera columna, respectivamente. Ennuestro ejemplo, la fila da los valores del precio de venta y la columna los de lasunidades.

Al igual que con las tablas de una variable, podemos comprobar la enorme catidad declculos que nos hemos ahorrado.

Prctica. Insercin y copiado de frmulas y funciones.

Prctica. Insercin y copiado de frmulas yfunciones.

Esta prctica consiste en disear frmulas de todo tipo en una hoja con datos variados.Para demostrar que se sabe copiar frmulas y definir bien las referencias que debanser absolutas, las frmulas deben disearse slo en las celdas verdes, las amarillas secompletarn copiando las anteriores.

Las frmulas a desarrollar, por orden de dificultad son las siguientes:

1. Media de la fecha de nacimiento (PROMEDIO)2. Ao de la media de encima (AO)3. Redondear a cero decimales los ingresos multiplicados por 166,386

(REDONDEAR)4. Contar cuantos son de cada situacin laboral (CONTAR.SI)5. Sumar cuanto gana cada sexo (SUMAR.SI)6. Comparar sus ingresos con el Salario Mnimo Interprofesional (SI)7. Buscar el DNI que corresponde al cdigo de encima (BUSCARV)8. Concatenar los apellidos con el nombre que corresponde al cdigo de

encima (BUSCARV anidada dentro de CONCATENAR)

EXCEL AVANZADO


Aunque en esta imagen orientativa no se vean ntidamente los enunciados, en la hojaque tienes que descargar se visualizan sin problemas.

Para empezar descarga el archivo de Excel que se te facilita (para que no lo abra tunavegador, haz un clic derecho en el enlace anterior y elige "Guardar enlace como..." o"Guardar destino como..."). Una vez est descargado en tu disco, desarrolla laspropuestas anteriores sobre la misma hoja. Cuando termines, manda el archivoresultante al tutor mediante el correo de la plataforma.

Prctica. Creacin de una tabla de doble entrada.

Prctica. Creacin de una tabla de dobleentrada.

Esta prctica consiste en crear una tabla de doble entrada que calcule el ndice deMasa Corporal (IMC) a partir de el peso (Kg) y la talla (m) segn la siguiente frmula:IMC = Peso/Talla2

La tabla resultante tiene que parecerse a esta:

EXCEL AVANZADO


Desarrolla el ejercicio desde cero en un libro nuevo. Cuando termines, manda elarchivo resultante al tutor mediante el correo de la plataforma.

EXCEL AVANZADO


Mdulo III. Filtro y orden.U.D. 1. Ordenar datos.

U.D. 1. Ordenar datos.

El cometido principal de Excel es realizar clculos numricos de una manera potente yflexible, pero tambin es un buen aliado a la hora de manejar una sencilla base dedatos. Si bien no es la herramienta adecuada cuando lo que se necesita es una basede datos relacional (la formada por varias tablas relacionadas entre s), puede sernosmuy util para crear y manejar bases de una sola tabla, tambin llamadas planas. Unatabla puede definirse como una cuadrcula en la que cada fila es un registro y cada filaes un campo, de forma que cada celda contiene el valor cierto campo de un registroconcreto. En el ejemplo siguiente, una tabla de clientes, cada fila o registro es unindividuo (Aarn Ramrez, Adrin Gonzlez, Ainhoa Garca...) y cada columna o datoes un dato personal (el cdigo de cliente, el nombre, los apellidos...)Cuando se manejan bases de datos hay tres necesidades bsicas para gestionar losdatos que Excel resuelve de manera sencilla:

Ordenar registros respecto a uno o ms campos, la forma ms clsica defacilitar una bsqueda, por ejemplo: ordenar alfabticamente los clientes porsu apellido. En Excel se consigue con la opcinDatos/Ordenar...o conbotones a tal efecto, y .

Buscar valores aunque los registros no est ordenados, por ejemplo, buscarcierto NIF. Como en la mayora de los programas que manejan texto laopcin necesaria esEdicin/Buscar...que cuenta con una variante muy utilpara realizar reemplazos rpidos:Edicin/Reemplazar...Por ejemplo,podramos ordenar que en la columna del campo "Sexo" se reemplazasenautomticamente todos los valores "H" por la palabra "Hombre" o la "M" por"Mujer", ahorrndonos el trabajo de hacerlo manualmente.

Filtrar los registros que cumplen cierta condicin dejando temporalmenteinvisibles los que no la cumplen, por ejemplo, ver slo los clientes que sonde Barcelona. Para ello, Excel, reserva las opciones del menDatos/Filtro

Ms adelante profundizaremos en estas y otras necesidades ms avanzadas (o menosbsicas pero verdaderamente tiles que veremos en el siguiente mdulo) como

EXCEL AVANZADO


conseguir subtotales e informes dinmicos. De momento nos centramos en la primeraque hemos mencionado, ordenar, algo muy fcil pero que precisa tener unas cuantasconsideraciones previas.

En la siguiente ilustracin se demuestra lo fcil que es ordenar una tabla por un slocampo, tras seleccionar basta con pulsar el botn "Orden ascendente" (u "Ordendescendente" , en su caso). Slo hay que tener cuidado de no seleccionar ms deuna celda si no queremos que se disgreguen los datos seleccionados respecto de losno seleccionados. Tambin hay que tener en cuenta que si hubiera alguna fila contodas las celdas en blanco, la ordenacin no trascendera ms abajo de la misma, enese caso habra que seleccionar todas las celdas de la tabla (incluidas las celdas enblanco) antes de ordenar.

El siguiente es un ejemplo de lo advertido anteriormente, si antes de ordenar seselecciona ms de una celda slo se ordenarn los datos seleccionados,disgregndose de los no seleccionados.

EXCEL AVANZADO


Si no se tiene en cuenta lo anterior, ordenar puede convertirse en una maniobrapeligrosa. Podramos preguntarnos, por qu Excel acta de esta forma?Sencillamente porque puede haber muchas ocasiones en que s que nos interes ladisgregacin de datos, es decir que slo se ordene lo seleccionado,independientemente de los valores que lo rodean.

El siguiente ejemplo ilustra uno de esos momentos, queremos ordenardescendentemente los participantes en un campeonato segun los puntos que hanobtenido pero no nos interesa para nada que se desordene la columna "Puesto" quetiene que quedar inmutable. Para ello se han seleccionado previamente slo los datosde las columnas "Participante" y "Puntos" y Excel se limita a ordenar dicho rango.

EXCEL AVANZADO


Como hemos visto, por criterio de ordenacin toma lacelda activa, es decir, la nica que no se oscurece alseleccionarla (en este caso la que tiene el valor "3").Para cambiar de celda activa sin perder la seleccinbasta con pulsar la tecla "Tabulador" (para pasar a lasiguiente, o "Maysculas" + "Tabulador" para pasar a laanterior).

Las posibilidades no se quedan en el orden por un solo criterio, sino que se puedeordenar hasta por un mximo de tres campos, para ello es necesario recurrir al menDatos/Ordenar... y rellenar el siguiente cuadro en el que se especifican los criterios deordenacin (ascendente o descendente) por orden de prioridad. En el ejemplo seordenan los datos prioritariamente por provincia, a su vez los de la misma provincia seordenan por localidad y, por ltimo, los de la misma localidad se ordenan por apellidos.

EXCEL AVANZADO


Ntese en la parte inferior del cuadro que podemos especificar si la tabla tiene unaprimera fila de encabezamiento, es decir, que la primera fila (en este caso la 1)contiene los nombres de los campos y no datos los registros (en este caso clientes),para que no la incluya en la ordenacin. Es habitual que as sea y Excel lo sueledetectar cuando la fila con los encabezados tiene formato distinto al resto (en este casotiene fondo gris y letra en negrita) pero no viene mal saber dnde se especifica esteaspecto cuando dicha fila no es detectada.

Si se quiere ordenar por ms de tres criterios deberarecurrirse a una aplicacin de bases de datos quepermita realizar consultas como Access, Oracle, FoxPro, MySQL... Aunque sin salir de Excel podra crearseuna columna que concatenase los valores de loscampos que se quieran tener como criterios y ordenarslo por esa columna. Dicha columna contendra unafuncin de esta forma:

=CONCATENAR(columna 1, columna 2,columna 3, columna 4, etc...)

EXCEL AVANZADO


U.D. 2. Autofiltro.

U.D. 2. Autofiltro.

En Excel hacer un filtro, como su propio nombre puede llevar a adivinar, consiste envisualizar slo los registros (filas) que cumplen uno y/o varios criterios, por ejemplo:slo los clientes que son de Teruel.

Puntualicemos que un filtro no elimina registros, sino que los deja de visualizar. Enotras palabras, filtra slo los que cumplen con determinados criterios, no en vano sesuele representar con un icono que recuerda al filtro de una cafetera . La variante

ms rpida de los filtros es el autofiltro, que al activarse permite establecer de unaforma rpida e intuitiva cualquier columna de la tabla como criterio de filtrado.

Como muestra la siguiente imagen, el autofiltro se activa y desactiva alternativamentedesde la opcinDatos/Filtro/Autofiltro. Una vez activado, podremos establecer filtrosdesde los botones desplegables que acompaan a los ttulos de las columnas (los queestn filtrando filas quedarn evidenciados en color azul) y fcilmente anularemos esosfiltrados eligiendo en sus correspondientes desplegables la primera opcin (Todas).

EXCEL AVANZADO


Bajo estas lneas se muestra el efecto de haber elegido filtrar los registros cuyaprovincia sea "Sevilla". Ntese como el correspondiente botn desplegable ha tomadocolor azul para advertir al usuario de que no est viendo todas las provincias.

Si se activan filtros en ms de un campo, se hace un filtrado cada vez ms excluyenteya que slo se visualizan los registros que cumplen con todos y cada uno de loscriterios. Esto en teora de conjuntos nos buscara la interseccin del conjunto deregistros que cumplen un criterio con el de los que cumplen con el otro.

En el siguiente ejemplo slo se filtrarn los registros que adems de ser de la provincia"Sevilla" su sexo sea "M" (mujer). Ntese como a la izquierda los nmeros que

EXCEL AVANZADO


referencian las filas toman color azul (dando a antender que no estamos viendo todoslos que hay) a la vez que en la barra de estado aparece un recuento rpido de losregistros filtrados.

Si se quieren volver a ver todos los registros se puede acudir a laopcinDatos/Filtro/Mostrar todo. Si lo que queremos slo es retirar un criterio delfiltrado manteniendo el resto, nos dirigiremos al desplegable de la columna en cuestiny elegiremos "Todas". En esta imagen vemos como podramos volver a visualizarregistros de cualquier sexo, pero seguira vigente el criterio de que slo se vean los dela provincia "Sevilla".

Tambin es muy probable que en vez de elegir los que cumplen cierto criterio deigualdad, nos interesen sencillamente los que son distintos, mayores, menores,comiencen, terminen o contengan cierta expresin. Para ello, elegiremos "Personalizar"y rellenaremos el cuadro emergente conforme al criterio deseado.

EXCEL AVANZADO


Cuando los datos no son numricos, las comparaciones"mayor que" (>) o "menor que" () "Blanco".

Siguiendo con el ejemplo, ahora nos interesamos por los registros cuya localidad no es"Sevilla". Teniendo en cuenta que sigue activo el criterio de que la provincia sea"Sevilla" (le delata su desplegable de color azul), el resultado ser que veremosregistros de la provincia de Sevilla pero no de la capital.

Personalizar el filtro de un campo nos permite adems establecer un segundo criteriode comparacin, unido al primero mediante dos posibilidades:

la conjuncin "Y" (es decir, queremos que se cumplan ambos criterios,queremos registros que estn en la interseccin de ambos conjuntos)

la conjuncin "O" (es decir, nos conformamos con que se cumpla alguno delos dos criterios o ambos, queremos registros que estn en la unin deambos conjuntos)

El siguiente ejemplo devolvera registros cuyo valor en la columna "Ingresos Brutos"sea mayor o igual que 5.000 y a la vez no pase de 10.000 . Como lo que queremoses una interseccin de conjuntos, unimos ambos criterios con "Y". Unirlos con "O" notendra sentido, pues todos los registros superaran el filtrado, ya que todo el mundogana o ms de 5.000 o menos de 10.000 o ambas cosas a la vez.

EXCEL AVANZADO


Por su parte, este otro ejemplo devolvera registros cuyo valor en la columna "SituacinLaboral" sea "Desempleado" o "Jubilado". Como lo que queremos es una unin deconjuntos, unimos ambos criterios con "O". Unirlos con "Y" no tendra sentido, puesningn registro superara el filtrado, ya que en esta tabla o se figura como"Desempleado" o se figura como "Jubilado" pero no ambas cosas a la vez.

EXCEL AVANZADO


Ntese en las dos imgenes anteriores como se nospropone el uso de los llamados "caracteres comodn" (?y *), de los que trata al final de esta unidad didctica.

Otra posibilidad es filtrar un nmero fijo de elementos que sean los superiores o losinferiores en cierto criterio. Se consigue eligiendo en el desplegable del criterio encuestin la opcin "Las 10 ms...", aunque esta denominacin no hay que tomarla ensentido estricto, no tienen por qu ser 10 y pueden ser "las menos". Tambin, en vezde un nmero fijo se puede extraer un porcentaje del recuento total de registros, lo queen Estadstica se denomina un percentil (para ello se elige "por ciento" en el cuadroque emerge).En el siguiente ejemplo queremos filtrar los 20 clientes ms ancianos, por lo que nosdirigimos al desplegable del campo "Fecha de nacimiento" y solicitamos los "inferiores20 elementos", es decir los 20 que tienen una fecha de nacimiento menor, por lo tantolos de mayor edad.

Caracteres comodn

Como adelantbamos en la nota anterior, es reseable que para este mbito de losfiltros se aprovecha un clsico de la informtica: los caracteres comodn. Se usancuando se desconoce la secuencia exacta de caracteres a filtrar. La bsqueda tendrxito cuando encuentre en su lugar las equivalencias citadas en esta tabla:

Carctercomodn

Equivale a... Ejemplo

EXCEL AVANZADO


* Cualquier nmero decaracteres (incluso ninguno)

co*ta encuentra costa,corbata, cota, etc...

? Cualquier carcter individual co?ta encuentra costa,corta pero no corbata nicota

Aunque su utilizacin se suele poder sustituir por el uso de criterios tipo "contiene","comienza por" o "termina por", los usuarios que estn acostumbrados a usarlos nodudarn en hacerlo.

Primer ejemplo: si buscamos clientes que viven en cualquier portal de la "Calle delDesengao" pero sospechamos que tambin pueda venir escrita como "CalleDesengao" podramos acudir al filtro personalizado y solicitar aquellos registros cuyodomicilio sea igual a "calle*desengao*" evitndonos tener que filtrar aquellos cuyodomicilio comience por "calle" y contenga "desengao".

Segundo ejemplo: si buscamos clientes que su primer apellido sea Jimnez perodesconocemos si va con "G" o con "J" ni si se habr introducido con su correspondientetilde en la "" podramos solicitar aquellos registros cuyo campo "Apellidos" sea igual a"?im?nez*". As encontraramos registros tuvieran su primer apellido Jimnez,Gimnez, Jimenez, Gimenez, etc. sea cual sea el segundo. Es triste pero tanto en lasbsquedas como los filtros no es lo mismo pedir una palabra con tilde que sin ella, algoque no pasa, por ejemplo, en la mayora de los buscadores de Internet.

U.D. 3. Filtro avanzado.

U.D. 3. Filtro avanzado.

Aunque se usa en casos ms excepcionales, el filtro avanzado es una herramientadiseada para llegar a los objetivos que no se pueden alcanzar con el autofiltro, comoplantear criterios de comparacin respecto a otra celda o a una frmula, nonecesariamente un valor constante. Tambin permiten la unin no excluyente(mediante "O") de criterios en distintas columnas, algo imposible con el autofiltro. Otraventaja considerable es que permiten indicar que las celdas filtradas resultantes se

EXCEL AVANZADO


copien a otro punto de la hoja de clculo.En el uso del filtro avanzado ya no especificaremos los criterios a golpe de desplegablesino introducindolos en celdas en la parte superior de la hoja, de hecho es preciso quela tabla deje libres para los criterios al menos tres filas libres por encima.Para utilizarlo hay que acudir a la opcinDatos/Filtro/Filtro avanzado.Al ser denaturaleza ms complicada, se hace necesario recurrir a ejemplos para facilitar sucomprensin.

Primer ejemplo: queremos que se filtren aquellos registros cuya columna "Ingresosbrutos" tenga valores que no sobrepasen los del valor variable de una celda (Q2, quecontiene el Salario Mnimo Interprofesional, cambiante de un ao para otro). Lo primeroque se ha hecho es introducir en la celda P2 la frmula "=O6

Segundo ejemplo: nos interesa ver los desempleados que ingresan menos de 3.000 .Se necesita filtrar registros que cumplan a la vez dos criterios en columnas diferentes(interseccin de conjuntos, pues se tienen que cumplir el criterio 1 "Y" el criterio 2).Para ello, ambos criterios deben convivir en la misma fila.

EXCEL AVANZADO


Tercer ejemplo: nos interesa ver los autnomos que ingresan ms de 30.000 . Denuevo, se necesita filtrar registros que cumplan a la vez dos criterios en columnasdiferentes (interseccin de conjuntos, pues se tienen que cumplir el criterio 1 "Y" elcriterio 2). Otra vez, ambos criterios deben convivir en la misma fila.

Cuarto ejemplo: nos interesa ver los clientes que, o bien son autnomos obien ingresan ms de 30.000 , o ambas cosas. Esta vez, se necesita filtrar registrosque cumplan al menos uno de los dos criterios en columnas diferentes (unin deconjuntos, pues se tienen que cumplir el criterio 1 "O" el criterio 2). Para ello, amboscriterios deben estar en distintas filas.

EXCEL AVANZADO


Quinto ejemplo: nos interesa ver los clientes que sean de una localidad extremea perono de las capitales de provincia. Este caso es una combinacin de lo dicho ltimamentey junta la unin de dos intersecciones, es decir, basta con que se cumpla cualquiera delos siguientes enunciados: o bien ser de la provincia de Cceres pero de localidaddistinta () a Cceres, o bien ser de la provincia de Badajoz pero de localidad distinta() a Badajoz. Expresado de otra forma, buscamos el siguiente conjunto:((Provincia="Cceres") Y (Localidad"Cceres")) OO ((Provincia="Badajoz") Y (Localidad"Badajoz"))Por ello los criterios unidos por "Y" deben ir en la misma fila y a su vez, la unin con "O"de los criterios resultantes debe ponerlos en distintas filas.

EXCEL AVANZADO


Prctica. Uso del orden y filtros.

Prctica. Uso del orden y filtros.

Esta prctica consiste en ordenar y filtrar la tabla del libro a descargar:

Ordena los clientes ascendentemente por provincia, luego ascendentementepor localidad y luego descendentemente por fecha de nacimiento.

Filtra de una sola vez tanto los jubilados como los desempleados de laprovincia Madrid nacidos entre 1/1/1970 y 31/12/1989

Para ello descarga el archivo de Excel que se te facilita (para que no lo abra tunavegador, haz un clic derecho en el enlace anterior y elige "Guardar enlace como..." o"Guardar destino como..."). Una vez est descargado en tu disco, desarrolla laspropuestas anteriores sobre las hojas 1, 2 y 3. Cuando termines, manda el archivoresultante al tutor mediante el correo de la plataforma.

EXCEL AVANZADO


Mdulo IV. Subtotales y tablasdinmicas.

U.D. 1. Subtotales.

U.D. 1. Subtotales.

El uso de subtotales permite la realizacin rpida de clculos estadsticos en una tablade datos. En este proceso, Excel realiza agrupaciones de registros que tienen el mismovalor en un campo y les aade una fila debajo en la que ubica el clculo solicitado: lasuma, un recuento u otras estadsticas. Para ello slo es preciso que la tabla estordenada por dicho campo pues, en caso contrario, registros pertenecientes a unmismo grupo se encontraran desperdigados por la hoja y en vez de una fila nica conel subtotal, habra varios parciales que no serviran de mucho.

Los clculos que puede hacer Excel con los subtotales son los siguientes:

Suma: de los valores numricos. Contar: recuento de las celdas que no estn vacas, tal como hara la funcin

CONTARA(). Promedio: La media aritmtica de los valores numricos. Mx: extrae el mayor valor Mn: extrae el menor valor. Producto: multiplicacin de los valores numricos. Contar nmeros: recuento de las celdas con valores numricos, tal como

hara la funcin CONTAR(). Desvest y Desvestp: la desviacin estndar de una poblacin, donde la lista

es la muestra o toda la poblacin, respectivamente. Var y Varp: la varianza de una poblacin, donde la lista es la muestra o toda

la poblacin, respectivamente.

En el sencillo ejemplo que sigue vamos a pedir un recuento de clientes de cadacomunidad autnoma. Como ya se ha comentado, para conseguir una buenaagrupacin por dicho criterio previamente ordenaremos por el mismo para que losvalores idnticos queden juntos. Para ello seleccionamos slo una celda de la columna"Comunidad Autnoma" y pulsamos el botn "Orden Ascendente" .

EXCEL AVANZADO


Hay que tener especial cuidado con que los valores deun mismo grupo estn escritos de forma idntica (inclusoen lo que se refiere a acentos y ees, dando igual lasmaysculas), pues la mnima diferencia hara que Excellos separara en distintos grupos.

Una vez estn ordenados los datos, vamos a la opcinDatos/Subtotales...y elegimosla columna por la que agrupar (por la que se orden), la funcin estadstica a realizar, yel campo sobre el que dicha funcin va a realizar los clculos.

EXCEL AVANZADO


Ntese las tres casillas de verificacin en la parte inferior del cuadro:

Reemplazar subtotales actuales, est marcada por defecto para que alvolver a pedir un subtotal borre el anterior, la desactivaremos cuandoqueramos hacer subtotales anidados, como en el siguiente ejemplo.

Salto de pgina entre grupos: para que, a la hora de imprimir, no salgan dosgrupos en la misma pgina.

Resumen debajo de los datos: si se desmarca esta casilla, las filas con lossubtotales se aadirn encima, no por debajo de los grupos que es la opcinpor defecto.

Adems, el botn "Quitar todos" permite dejar la tabla de nuevo sin subtotales, como alprincipio.

El resultado es un esquema automtico al estilo de los que se pueden conseguirmanualmente desdeDatos/Agrupar y esquema/Agrupar...Este esquema encierra tresniveles a los que se puede acceder desde los tres botones numricos que aparecen ala izquierda de los encabezados de columna:

El 1 es el ms resumido, que muestra el clculo total de todos los registros(en este caso, el recuento de todos los clientes). Para desglosarlo al nivel 2de subtotales tambin podemos pulsar sobre el botn "+" que aparece a laizquierda de los encabezados de fila (botn que se tornar en un menos siqueremos recoger de nuevo las filas de ese subtotal).

El 2, desglosa propiamente los subtotales de grupos que se han pedido,ofreciendo el clculo parcial solicitado (en este caso, el recuento de clientesde cada provincia). Para desglosar al nivel 3 algn subtotal y ver losregistros concretos que lo han propiciado tambin basta con pulsar sobre elbotn "+" que aparece a la izquierda de los encabezados de fila (botn quese tornar en un menos si queremos recoger de nuevo las filas de esesubtotal).

EXCEL AVANZADO


Por ltimo, el nivel 3 desglosa al mximo detalle todos los registros de latabla, con sus correspondientes filas de subtotales intercalada. Lgicamenteno pueden desglosarse ms, pero siguiendo con la misma pauta, puedenresumirse de nuevo a nivel 2 de subtotal pulsando sobre los botones "-" de laizquierda.

EXCEL AVANZADO


Tambin se pueden anidar unos subtotales dentro de otros, creando ms niveles dedesglose. Para ello, previamente habr que ordenar por los criterios por los que sequiere agrupar, comenzando por el ms amplio y terminando por el ms concreto.Despus se irn solicitando subtotales, teniendo cuidado de desactivar la casilla"Reemplazar subtotales actuales".

EXCEL AVANZADO


Como ejemplo, supongamos que queremos un recuento de clientes de cadacomunidad autnoma como antes, pero que cuente tambin los de cada provincia y,finalmente, los de cada localidad. Para ello, el paso previo es ordenar por los trescriterios, dando preferencia a los ms amplios. Iremos aDatos/Ordenar...yordenaremos ascendentemente por comunidad autnoma, luego por provincia (los quesean de la misma comunidad) y finalmente por localidad (los que sean de la mismacomunidad y provincia).

Una vez ah iremos tres veces a la opcinDatos/Subtotales...,donde pediremos unrecuento del DNI tanto en cada cambio de comunidad autnma, despus por provinciay finalmente por localidad (evitando borra los subtotales existentes en las dos ltimasveces). La siguiente figura describe el proceso y el resultado final.

EXCEL AVANZADO


Ntese como esta ves tenemos 5 niveles, el del recuento general, los 3 de lossubtotales solicitados y el nivel detallado con todas las filas originales.

U.D. 2. Tablas dinmicas.

U.D. 2. Tablas dinmicas.

Las tablas dinmicas dan un paso ms all de los subtotales, pues tambin sirven parasacar toda clase de informes resumidos de grupos de registros, la diferencia es que las

EXCEL AVANZADO


conclusiones las sacan en una hoja aparte y, en principio, no permiten ver los registrosconcretos a los que se refieren. El apelativo de dinmicas les viene de la facilidad conla que un mismo informe se puede transformar en otro cambiando o aadiendo criteriosde agrupamiento y funciones de clculo. Una vez se comprenden las tablas, cualquierapuede hacer el informe que se proponga con unos pocos clics y arrastres de ratn.

Para ponerlas en marcha no hace falta ni hacer una ordenacin previa, basta con teneruna tabla con los encabezados de los campos en la primera fila e ir a laopcinDatos/Informe de tablas y grficos dinmicos...y al momento se iniciar unasistente (una sucesin de cuadros de dilogo) para orientarnos en las opciones aelegir.

En el primer paso se nos pregunta la ubicacin de los datos, que puede ser:

en una tabla del libro Excel (la opcin por defecto y ms usada). en cualquier base de datos externa compatible con los orgenes de datos

ODBC (como Access, dBase, etc...) que tengamos configurados en el panelde control de nuestro sistema operativo (vase "HerramientasAdministrativas").

dispersos en varios rangos de varias hojas del libro (consolidacin). en una tabla dinmica anterior, as ocupar menos memoria el archivo

resultante (esta opcin, lgicamente, slo est disponible si ya existe otratabla en el libro Excel.

Tambin se nos da a elegir entre crear una tabla (informe numrico) o un informegrfico (opcin que se estudia ms a fondo en la siguiente unidad didctica).En nuestro ejemplo no cambiamos ninguna opcin por defecto y pulsamos sobre"Siguiente >".

EXCEL AVANZADO


En el segundo paso podemos especificar el rango de celdas completo con los datos aanalizar, trabajo que nos ahorraremos si previamente ya estaba seleccionada cualquiercelda de la tabla y la misma no tiene filas en blanco. Nos aseguramos de que el rangoes el correcto y pulsamos de nuevo sobre "Siguiente >".

EXCEL AVANZADO


Por ltimo (tal y como nos da a entender la bandera a cuadros indicadora de metaautomovilstica), el tercer paso nos permite decidir la ubicacin de la tabla. La opcinpor defecto es en una hoja nueva, tambin es la ms recomendable en los comienzospara verlo todo ms claro y porque no sabemos las dimensiones finales de la tabla, porlo que situar la tabla en una de las hojas existentes podra hacer peligrar los valores deotras celdas.

En este momento, podramos acceder al diseo a priori y a las opciones avanzadas dela tabla con los respectivos botones de la parte inferior, aunque seguramente seentienda todo ms claro si se cambia a posteriori, por lo que pulsaremos sobre"Finalizar".

Al momento tendremos la tabla en blanco junto a una nueva barra de herramientasllamada "Tabla dinmica" que contiene los nombres de los campos de la tabla. Paradarle contenido a la tabla basta con arrastrar los nombres de los campos a alguna deestas zonas:

"Coloque campos de fila aqu", ah arrastraremos opcionalmente los campos quequeramos que sean criterio de agrupacin para formar categoras de registros

dispuestas en filas, una debajo de otra. Nos confirmar que estamos arrastrando elcampo a esta zona un puntero como el de la izquierda (ntense las sutiles diferenciasen el sombreado entre este y los tres siguientes)."Coloque campos de columna aqu", ah arrastraremos opcionalmente los campos que

EXCEL AVANZADO


sean criterio de agrupacin para formar categoras de registros dispuestas en

columnas, una al lado de otra. Nos confirmar que estamos arrastrando el campo aesta zona un puntero como el de la izquierda.

"Coloque datos aqu", ah arrastraremos necesariamente los campos sobre los quequeramos que se hagan clculos (el repertorio de funciones es el mismo que

para los subtotales: suma, recuento, promedio, etc...). Cuando arrastremos camposnumricos, automticamente har la suma, cuando se trate de valores de texto,sumarlos no tendr sentido y Excel se decantar por contarlos. Nos confirmar queestamos arrastrando el campo a esta zona un puntero como el de la izquierda.

"Coloque campos de pgina aqu", ah arrastraremos opcionalmente los campos quequeramos que sean criterio de filtrado de registros, de forma que podamos

optar por ver slo uno de los grupos que forman dichos campos (o todos si no se activael filtro). Nos confirmar que estamos arrastrando el campo a esta zona un punterocomo el de la izquierda.

Vista la teora, se entender mejor unos ejemplos. Para ello hemos preparado unatabla cuyos registros son las facturas de gastos de una pequea empresa, como puedeverse en la primera ilustracin de esta unidad didctica. Vamos a ir proponiendoenunciados de consultas estadsticas y comprobaremos como la tabla dinmica puededar respuesta a todas ellas:

Primer enunciado: cunto le hemos comprado a cada proveedor?

Solucin: Elegimos "Proveedor" como campo de fila y "BI" (base imponible) como dato.Al tratarse el dato de un nmero, automticamente ser sumado. Cada fila resumen esencabezada por un proveedor y una ltima fila clcula el total general.

EXCEL AVANZADO


Segundo enunciado: de lo que le hemos comprado a cada proveedor, cunto era decada tipo de IVA?

Solucin: elegimos "% IVA" como campo de columna. Se separan los clculos en trescolumnas, una por tipo de IVA y una columna a la derecha calcula su respectivo totalgeneral.

Tercer enunciado: bajo qu concepto contable realizamos esas compras de cadaproveedor y de cada tipo de IVA?

EXCEL AVANZADO


Solucin: elegimos "Concepto" como campo de fila por el que crear grupos, a laizquierda del existente ("Proveedor"). De esta forma, primero crea categoras porconceptos y despus las subdivide por proveedores. Si se arrastra a la derecha, elefecto de agrupamiento sera el inverso.

Otra forma de subdividir una categora en otra es hacerun doble clic en cualquiera de los rtulos de los gruposexistentes (cuidado, no en el campo de fondo gris, puesnos llevar a la configuracin del campo) y eligiendo lasubcategora con el siguiente cuadro de dilogo:

EXCEL AVANZADO


Cuarto enunciado: cunto nos hemos gastado por concepto, sin subdividir porproveedor?

Solucin: arrastramos el campo "Proveedor" fuera de la tabla hasta que al puntero leacompae el smbolo de un tachn rojo, como el que se observa abajo en la ilustracin.La subcategora "Proveedor" deja de actuar.

EXCEL AVANZADO


Quinto enunciado: cunto hemos comprado cada da?

Solucin: arrastramos el campo "Concepto" fuera de la tabla y elegimos "Fecha" comocampo de fila. Ahora, cada fila la forma la suma de las compras que se hicieron en unmismo da.

Sexto enunciado: estoy haciendo la declaracin trimestral de IVA cunto he compradoen cada trimestre de cada tipo?

Solucin: hacemos un clic derecho en el campo de fila recin instaurado Fecha" yelegimos la opcin "Agrupar".

EXCEL AVANZADO


En el cuadro "Agrupar" que aparece seguidamente, elegimos "Trimestres" y al pulsarsobre "Aceptar" ya tendremos lo que buscbamos.

EXCEL AVANZADO


Sptimo enunciado: No me basta con saber la BI de cada trimestre, necesito tambin elIVA y el total.

Solucin: elegimos "IVA" y "TOTAL" como campos de datos. Cada categora de filadesglosar los tres clculos, BI, IVA y total.

Octavo enunciado: quiero que se vuelva a quedar slo la suma del IVA.

Solucin: no se pueden arrastrar los campos fuera de la tabla pero se pueden excluirdesde el desplegable del encabezado gris "Datos". La tabla vuelve a calcular slo lasuma del IVA.

Noveno enunciado: quiero que los distintos tipos de IVA se vean de forma separadaconforme los vaya eligiendo, no todos a la vez en columnas.

Solucin: arrastramos el campo "% IVA" para que deje de ser campo de columna y seconvierta en campo de pgina. Ahora, veremos o el total general de todos los tipos (loque antes estaba en la columna de ms a la derecha) o el parcial de cada tipo, sinmezclarse con los otros, tan slo accionando el desplegable de arriba del todo.

EXCEL AVANZADO


Decimo enunciado: qu importe tuvo la compra ms importante que hice en cadatrimestre?

Solucin: accionamos el botn "configuracin de campo" de la barra "Tabla dinmica",tambin sale esta opcin haciendo un clic derecho en el campo de la esquina superiorizquierda de la tabla o, ms rpido todava, un doble clic (como se mencionaba en eltruco anterior). Ah cambiamos la configuracin del campo de datos y elegimos lafuncin "Mximo" en vez de "Suma".

EXCEL AVANZADO


Como se puede observar en el cuadro de la derecha de la imagen, al configurar elcampo, adems de cambiar de funcin se puede cambiar el formato de nmero comose hace ordinariamente con las celdas.

U.D. 3. Grficos dinmicos.

U.D. 3. Grficos dinmicos.

Los grficos dinmicos son una herramienta de anlisis visual derivada de las tablasdinmicas. Permiten la representacin grfica de los datos que resume una tabladinmica al igual que un grfico ordinario hace con series de valores en celdas. Dehecho cuando creemos un grfico dinmico, automticamente se crear una tabla quele acompaar para proporcionarle los datos que necesita.

Para crear un grfico dinmico hay que accionar la misma opcin que con las tablasdinmicas,Datos/Informe de tablas y grficos dinmicos...,pero eligiendo la opcin"Grfico dinmico", como es lgico.

EXCEL AVANZADO


El siguiente paso nos volver a preguntar por el rea de celdas que contiene los datosa analizar, igual que con las tablas_

Si despus del paso anterior, Excel detecta que ya hay una tabla referida a esos datos,

EXCEL AVANZADO


podremos basarnos en ella para ahorrar memoria, aunque sern interdependientesentre ellas. La eleccin es nuestra y nos ser consultada con el siguiente cuadro:

En el caso de responder afirmativamente o si en el paso 1 hemos indicado que nosbasaremos en otra tabla existente, deberemos indicarle cul en el siguiente cuadro:

Finalmente, el paso 3 nos consultar dde queremos ubicar la tabla quenecesariamente tiene que acompaar a todo grfico dinmico. Como ya hicimos en sumomento, recomendamos desviarla a una hoja nueva para que no entre en conflictocon valores existentes en nuestras celdas.

EXCEL AVANZADO


Una vez pulsemos "Finalizar", tendremos el grfico preparado en blanco y lacorrespondiente barra de herramientas de tabla dinmica, que ya nos es familiar,adems de la barra de grficos para editar el grfico resultante conforme a nuestraconveniencia.

Antes de seguir convendra recapitular qu es un grfico y qu partes lo componen.Podra definirse grfico como la representacin visual de los datos o valoresalcanzados por unas series a lo largo de ciertas categoras. Un ejemplo adaptado alfichero que usamos en la unidad didctica anterior: si realizamos un grfico querepresente las compras (campo "BI") realizadas para cada concepto contable duranteel ao (campo "Fecha"), los datos sern las bases imponibles, las series los conceptoscontables y las categoras las fechas.

La pauta a seguir ahora es arrastrar los campos dnde les correponda, teniendo encuenta que las 4 zonas que conocemos de las tablas dinmicas se ven un pocoalteradas:

"Coloque campos de categora aqu" (el eje de abscisas, X), ah arrastraremosopcionalmente los campos que queramos que sean criterio de agrupacin para

formar categoras de registros dispuestas el eje de abscisas, una junto a la otra. Nosconfirmar que estamos arrastrando el campo a esta zona un puntero como el de laizquierda.

"Coloque campos de serie aqu" (en la leyenda), ah arrastraremos opcionalmente los

EXCEL AVANZADO


sean criterio de agrupacin para formar series de registros, cada una de un

color. Nos confirmar que estamos arrastrando el campo a esta zona un puntero comoel de la izquierda.

"Coloque datos aqu" (en el rea de trazado del grfico), aharrastraremos necesariamente los campos sobre los que queramos que se

hagan clculos y se representen los valores alcanzados (el repertorio de funciones esel mismo que para los subtotales: suma, recuento, promedio, etc...). Cuandoarrastremos campos numricos, automticamente har la suma, cuando se trate devalores de texto, sumarlos no tendr sentido y Excel se decantar por contarlos. Nosconfirmar que estamos arrastrando el campo a esta zona un puntero como el de laizquierda.

"Coloque campos de pgina aqu" (arriba del todo), ah arrastraremos opcionalmentelos campos que queramos que sean criterio de filtrado de registros, de forma

que podamos optar por ver slo uno de los grupos que forman dichos campos (o todossi no se activa el filtro). Nos confirmar que estamos arrastrando el campo a esta zonaun puntero como el de la izquierda.

Seguramente la siguiente imagen ayudar a comprender mejor este tema, teniendo encuenta que ilustra el ejemplo enunciado anteriormente, la configuracin de un grficoque represente:

las compras... (el campo "BI" es el dato por lo que ir al rea de trazado) ...realizadas para cada concepto contable... (el campo "Concepto" define las

series, por lo que ir a la leyenda) ...durante el ao (el campo "Fecha" define las categoras del eje X, por lo

que ah hay que arrastrarlo).

EXCEL AVANZADO


El resultado de la maniobra anterior nos devuelve un grfico de columnas, cada colores una serie y las fechas se suceden en el eje X. Seguramente la informacin quesuministra el grfico ser ms util si agrupamos las fechas por trimestres, al igual quese hizo con las tablas dinamicas, para que en el eje X solo haya 4 categoras: los 4trimestres del ao. Para ello nos valemos del clic derecho en el campo de lascategoras ("Fecha").

EXCEL AVANZADO


Bajo estas lneas se puede ver el grfico resultante, con los trimestres comocategoras. Si queremos cambiar cualquier opcin podemos activar el asistente degrficos desde el botn o desde el menGrficoy acceder a cualquiera de sus 4

pasos:

Paso 1: tipo (columnas, barras, lneas, circular, etc...) y subtipo de grfico(normal, apilado, porcentual, en 3 dimensiones, etc...).

Paso 2: datos de origen, es decir, la tabla que alberga los valores arepresentar.

Paso 3: opciones de grfico, relativas al diseo (ttulos, ejes, lineas dedivisin, leyenda y tabla de datos).

Paso 4: ubicacin, o en una hoja nueva para l solo o flotando como unobjeto en una hoja existente, conviviendo con los valores de sus celdas.

Cmo no, tambin podremos hacer uso de la barra de grficos para cambiar el formatode cualquier elemento concreto del mismo (a elegir del desplegable):

EXCEL AVANZADO


De esta forma se ha conseguido el siguiente ejemplo: es el mismo grfico de antespero de tipo "reas" y subtipo "Apiladas con efecto 3D" (adems de algn que otroretoque de elementos sueltos: ttulo, colores...).

EXCEL AVANZADO


Otro ejemplo: de nuevo el mismo grfico pero de tipo "Circular" y subtipo "efecto 3D"(adems de algn que otro retoque de elementos sueltos: ttulo, colores, alto de labase, giro en 3D...).

EXCEL AVANZADO


Por ltimo, la siguiente figura ilustra el grfico anterior con otra ubicacin: flotandocomo un objeto en la misma hoja que la tabla dinmica en la que se basa.

EXCEL AVANZADO


Prctica. Totalizar por grupos.

Prctica. Totalizar por grupos.

EXCEL AVANZADO


Esta prctica consiste en realizar subtotales en las siguientes hojas del libro adescargar:

En la Hoja 1: el promedio de Ingresos Brutos de los clientes de cada sexo. En la Hoja 2: la suma de Ingresos Brutos de los clientes de cada localidad. En la Hoja 3: el cliente ms viejo y el ms joven de cada provincia.

Para ello descarga el archivo de Excel que se te facilita (para que no lo abra tunavegador, haz un clic derecho en el enlace anterior y elige "Guardar enlace como..." o"Guardar destino como..."). Una vez est descargado en tu disco, desarrolla laspropuestas anteriores sobre las hojas 1, 2 y 3. Cuando termines, manda el archivoresultante al tutor mediante el correo de la plataforma.

Prctica. Tabla y grfico dinmico.

Prctica. Tabla y grfico dinmico.

Esta prctica consiste en realizar una tabla y un grfico dinmico sobre los datos dellibro a descargar:

La tabla debe contar cuntos hombres y cuntas mujeres tienen unosingresos brutos comprendidos en estos tramos: de 0 a 9.999, de 10.000 a19.999, de 20.000 a 29.999 y de 30.000 a 40.000.

Pista: hay que usar la opcin de agrupar categoras.

El grfico debe representar mediante lneas los ingresos medios de cadasexo seg su situacin laboral.

EXCEL AVANZADO


Para ello descarga el archivo de Excel que se te facilita (para que no lo abra tunavegador, haz un clic derecho en el enlace anterior y elige "Guardar enlace como..." o"Guardar destino como..."). Una vez est descargado en tu disco, desarrolla laspropuestas anteriores. Cuando termines, manda el archivo resultante al tutor medianteel correo de la plataforma.

EXCEL AVANZADO


Mdulo V. Herramientas avanzadas.U.D. 1. Auditora.

U.D. 1. Auditora.

La barra de frmulas (representada bajo estas lneas) debe su nombre a que en lamisma aparece la expresin algebraica de la celda que est seleccionada en esemomento, mientras que en la cuadrcula del rea de trabajo slo se ve el valor quetoma.

Es inecitable que se produzcan errores en la elaboracin de las hojas de datos. Dealgunos nos informan las propias celdas con los siguientes cdigos de error:

##### #VALOR! #DIV/0! #NOMBRE? #N/A #REF! #NUM! #NULO!

Pero a menudo, los errores ms peligrosos son los que no detecta Excel porque dan unvalor correcto a efectos tcnicos pero que no concuerda con el proposito para el quehabamos formulado la celda. Son errores que pueden pasar fcilmente desapercibidosentre la marea de cifras en la que se suele convertir cualquier hoja de clculo.Para ello contamos con las herramientas de auditora, que sirven principalmente como

EXCEL AVANZADO


ayuda visual a la deteccin de ambas clases de errores, tanto tcnicos como deformulacin.

A efectos de deteccin de errores, si se desea se puedehacer que en la cuadrcula aparezcan tambin frmulasen vez de valores activando la casilla o Frmulas deHerramientas/Opciones.../Ver(ah tambin se puedeespecificar si se quiere dejar de ver los encabezadosy las lneas de divisin de celdas), con el teclado esms rpido pulsando Alt+ (ordinal).

Dichas herramientas se activan desde los botones de su propia barra, la de auditora,accesible desdeHerramientas/Auditoriay son los siguientes:

Rastrear y quitar precedentes (las que influyen en la celda activa)

Rastrear y quitar dependientes (en las que influye la celda activa)

Borrar todas las flechas

Insertar comentarios que nos ayuden a explicar lo que vemos en las celdas.

Rastrear precedentes es pedir que unas flechas azules indiquen las celdas queintervienen en el resultado de la celda activa. En este ejemplo (sencillo para una mejorcomprensin) nos preguntamos por las celdas que influyen en el total de la factura.

Para eliminar las flechas resultantes pulsaremos sobre "Quitar precedentes" (slode esa celda) o sobre "Quitar todas las flechas" .

EXCEL AVANZADO


Rastrear dependientes es pedir que unas flechas azules indiquen lasceldas en cuyo resultado interviene la celda activa. Ahora nos preguntamos por lasceldas en las que influye la base imponible del 4%.

Para eliminar las flechas resultantes pulsaremos sobre "Quitar dependientes" (slode esa celda) o sobre "Quitar todas las flechas" .

Durante el proceso de deteccin de errores puede sernos de gran ayuda comentar lasceldas, para un momento posterior en el que se necesite ms informacin acerca delcontenido de la celda, tanto para nosotros mismo como para futuros usuarios de lahoja. Para insertarlos tenemos tres opciones:

El botn de la barra de auditora. Clic derecho/Insertar comentario. MenInsertar/Comentario.

Si necesitamos modificar el comentario tenemos dos opciones:

El botn "Modificar comentario" de la barra de auditora. Clic derecho/Modificar comentario.

EXCEL AVANZADO


Por ltimo, si queremos borrar el comentario tenemos dos opciones:

Clic derecho/Modificar comentario. MenEdicin/Borrar/Comentario.

U.D. 2. Buscar objetivo.U.D. 2. Buscar objetivo.

EXCEL AVANZADO


Lo normal en las tareas diarias de Excel es disear frmulas que nos lleven a averiguarun resultado que necesitamos conocer. Sin embargo, suelen darse casos en los queconocemos el resultado al que queremos llegar, sabemos las frmulas que lo propicianpero nos preguntamos por el valor que deben tener las celdas precedentes o variablespara conseguir dicho resultado.

Una forma manual y tradicional de solucionar el problema sera plantear en un papelaparte la expresin algebraca del mismo, resolver la ecuacin y trasladar la formularesultante a Excel sustituyendo el resultado deseado en la misma para que nosdevuelva el valor de la variable. Un incoveniente puede rad

excel avanzado curso entero

Documents