manual de excel avanzado 2012

Unidad de Servicios de Cmputo Acadmico

UNICA

[MANUAL DE EXCEL AVANZADO] Realiz Ing. Gabriela Camacho Villaseor

Prohibida la reproduccin y distribucin de este material sin la debida autorizacin de UNICA

Excel Avanzado Excel

Avanzado

AUTOFORMATO

Excel proporciona formatos prediseados listos para aplicarse, estos formatos

ofrecen la ventaja de que permiten cambiar la apariencia de un documento en

forma rpida y detallada.

A esta caracterstica de Excel se le conoce con el nombre de AUTOFORMATO.

Para utilizarla realiza los siguientes pasos.

1.- Selecciona una tabla generada en Excel como se muestra en la figura:

2.- Para dar autoformato a una tabla da Clic en Estilo de Tabla y aparecer la

siguiente ventana donde podrs seleccionar el autoformato o disear su propio

formato.



Avanzado

3.- Selecciona el estilo que te mas le agrade y hacer clic en el botn aceptar, Excel

aplicar el formato seleccionado.

4.- Se puede modificar los formatos y preestablecidos dando clic en la opcin

Nuevo estilo de la tabla En la parte inferior aparecern varias casillas de

verificacin que permitir definir las caractersticas que se desean aplicar de los

formatos propuestos.

5.- Los cambios realizados se aplicaran solo en el rango seleccionado, sin dejar

alteradas las opciones de autoformato para su uso posterior.



Avanzado

Formato Condicional

El formato condicional es una caracterstica si se desea dar un formato diferente a

la informacin y cuando sta cumple con ciertos criterios definidos por el usuario.

Dentro de la cinta de opciones en la pestaa de INICIO le damos clic en la opcin

formato condicional.

Con formato condicional puede tener diferentes formatos para la celda cuando su

valor es suficientemente alto o bajo para hacer una distincin entre ellos y que

sobresalgan y se distingan entre los valores de la tabla. Se seleccionar la celda o

celdas que se le deseen aplicar el formato condicional. El cuadro de dilogo se

abre con la forma de la Condicin

1.- Lista para que usted la llene.

2.- Establecer la Condicin: Llene los cuadros de texto eligiendo: El Valor de la

Celda Es, y mayor que o igual a, desde la lista que baja. Hacer un clic en el tercer

cuadro de texto y escribir adentro = $C$14 o solo haga un clic en la celda C14.



Avanzado

3.- Fijar formato: Clic en la esquina izquierda de fuente y elegir Tramas |

Sombreado de Celda = Verde y Fuente | Color = Blanco y estilo de Fuente =

Negrita. No obtendr completamente todas las opciones de tipografa que est

acostumbrado a ver en el dilogo Fuentes.

Haga un clic en Aceptar para cerrar el dilogo de Formato de Celdas. El dilogo

de Formato Condicional ensea una muestra utilizando sus opciones. Puede

agregar hasta dos juegos ms de condiciones - cada una de ellas con un formato

diferente. Esta vez solo necesita la Condicin uno.



Avanzado

Hacer nuevamente un clic en Aceptar para cerrar el dilogo. Cuando la condicin

no se cumple, ver el formato original.

Pegado Especial

Esta opcin nos permite pegar una celda, grafico, tabla, etc. Como lo hacamos

con la opcin Pegar convencional, pero ste nos permite seleccionar que es lo que

queremos pegar exactamente, adems de tener la opcin de pegar el vnculo lo

cual significa que cuando cambia el objeto de original en la copia se reflejan los

cambios pertinentes. As como crear vnculos entre los documentos originales.

Pegado especial de Excel a Excel

Primero que todo debemos seleccionar las celdas, luego seleccionar copiar y

despus dar clic en la cinta de opciones, en la pestaa de Inicio y en pegar,

desplegar la lista desplegable y seleccionar la opcin Pegado especial nos

aparecer la siguiente ventana.



Avanzado

En el apartado de "pegar" podemos indicar que es lo que queremos copiar de las

celdas, puede ser todo, o solo las formulas, formatos, comentarios, etc.

En el apartado de "operacin" podemos decir que operacin se va aplicar a las

celdas entre si (si el contenido de las celdas es numrico). Por Ejmplo si en la

celda A1 tenemos el nmero 5 y en B2 hay un 3, podemos copiar la celda A1 y

hacerle un pegado especial a la celda B2 y en operacin seleccionamos suma

entonces el resultado que obtendremos ser B2 = 8.



Avanzado

Tambin podemos copiar el vnculo presionando en la misma ventana sobre el

botn Pegar vnculos, esto significa que siempre mostrar lo mismo que hay en la

celda original, es una especie de espejo o copia que se actualiza a cada cambio

que ocurre en la celda original.

Pegado especial de Excel a otra aplicacin Office

Tiene los mismos objetivos que en el Excel pero la ventana que nos aparecer es

la siguiente:

En esta ventana hay dos opciones principales, Pegar y Pegar vnculo. En "Pegar"

lograramos pegar el contenido con el formato que seleccionemos en el cuadro de

la derecha. Con "Pegar vnculo" conseguiremos pegue el contenido pero con la

ventaja que se actualizar si el origen cambia, tambin podremos seleccionar el

tipo de formato con que se visualizar.



Avanzado

INSERTAR COMENTARIOS

En ocasiones se necesita realizar comentarios sobre alguna cifra o alguna

frmula.

Es posible insertar comentarios asociados a una celda, para hacerlo, seleccione

de la cinta de opciones el men Revisar, el comando Comentario que presentar

una caja de dilogo para capturar el comentario correspondiente.

Para ver el comentario basta con acercar el cursor a la celda identificada con un

tringulo en la parte superior derecha.

Imprimir comentarios

En la ficha Hoja del men Archivo Imprimir----Configurar pgina ----en la

pestaa Hoja seleccionar en comentarios alguna de las tres opciones que tiene

disponibles.

Hipervnculos

Comentarios



Avanzado

Un hipervnculo en Microsoft Excel tiene el mismo concepto que en las pginas

web, nos sirve para ir a otra parte del libro o incluso, enviar email, abrir una pgina

web o abrir cualquier otro tipo de archivo. De la misma forma tambin podemos

hacer un hipervnculo en una celda, una imagen, grfico o cualquier otro objeto

que tengamos en el libro.

Crear

La manera de hacer un hipervnculo a una celda, imagen o cualquier otro objeto es

la misma, lo primero que se debe de hacer es pulsar clic derecho en el objeto que

se le aplicar el hipervnculo, luego seleccionamos Hipervnculo y nos aparecer

la ventana donde podemos especificar la opciones de nuestro nuevo hipervnculo.

Tipos de Hipervnculos:



Avanzado

Archivo o pgina web existente: Sirve para hacer el vnculo a una pgina web de

las que hayamos visitado previamente o tambin podemos agregar la direccin de

otra web.

Lugar de este documento: Aqu podemos hacer un link a otra hoja de libro, incluso

especificar la celda, la sintaxis se puede poner en cuadro que se llama texto:

nombre de hoja! celda, ejemplo para que se desplace a la celda G6 de la hoja 2,

tendramos que poner: hoja2!G6.

Crear nuevo documento: Sirve para que crear un nuevo de Office incluso pginas

web, debemos pulsar en el botn Cambiar y indicar la ruta y nombre que tendr el

nuevo archivo.

Direccin de correo electrnico: Por ltimo tenemos la opcin de hacer un

hipervnculo a un correo electrnico, lo que debemos incluir es la direccin de

email del destinatario. Pero hay que tener en cuenta que esto solo funciona

cuando tenemos instalado algn programa de correo electrnico, como por

ejemplo el Outlook.

CONSOLIDAR DATOS

La consolidacin de datos consiste en realizar un resumen de informacin que se

encuentre distribuida ya sea en una hoja o en varias hojas. Es muy comn la

consolidacin cuando se tiene informacin detallada en hojas y se desea realizar

un informe anual.

La consolidacin requiere de un rea destino, donde se pondr la informacin

consolidada y las reas fuente donde se encuentra la informacin.



Avanzado

Existen dos mtodos para consolidar datos:

POR POSICIN: Cuando varias reas fuente incluyen categoras de datos

similares, dispuestos de manera idntica, podrs consolidar los datos segn la

posicin utilizando referencias de celdas y de archivos para guiar la presentacin

de la tabla de consolidacin. La consolidacin por posicin se utiliza cuando se

trata de categoras similares de datos en las diferentes hojas. Un ejemplo se

puede observar en la siguiente imagen donde se desea realizar un resumen del

promedio trimestral de cada grupo.

POR CATEGORA: Cuando tus reas fuente tienen una distribucin diferente. La

consolidacin se realiza por categora utilizando rtulos. La consolidacin por

categora se utiliza cuando los datos no estn en ubicaciones similares o se tiene

un nmero diferente de filas o columnas de datos en cada categora.



Avanzado

Una vez que consolides los datos, podrs darle nombre a la referencia de un

rango de celdas de un rea fuente, para poder continuar consolidando los mismos

datos an si los datos fuente se mueven a otra ubicacin. Tambin podrs

expandir la consolidacin para incluir reas fuente adicional o para reducir el

campo de consolidacin eliminando reas fuente.

COMANDO CONSOLIDAR (MEN DATOS).

Recopila los datos localizados en una o varias reas fuente y los coloca en una

tabla en un rea de destino. Si se consolida por categoras utilizando rtulos,

debers incluir los rtulos que deseas que aparezcan en el rea de destino, en la

seleccin de las reas fuente. Si consolidas por posicin utilizando referencias de

celdas, no se incluyen los rtulos de categora en las reas fuente que selecciona.

Para activarlo, se elige el men Datos Consolidar este comando puede

consolidar datos con vnculos a hojas de clculo con fuentes externas. Al activarlo

se abrir el cuadro de dilogo Consolidar similar al de la siguiente imagen.

h)

e)

f)

g)

c)

a)

b)

d)



Avanzado

a) Funcin: Especifica la funcin que se desea utilizar para realizar la

consolidacin de los datos.

b) Referencia: Especifica un rea fuente para ser agregada a la consolidacin.

Una referencia puede aceptar cualquiera de las siguientes formas:

Referencia Utilizacin:

Referencias de celdas Cuando el rea fuente y destino estn

en la misma hoja de clculo.

Referencias de hojas y celdas Fuente y destino estn en hojas de

clculo diferentes.

Referencias de libros, hojas y celdas Fuente y destino estn en libros de

trabajo diferentes.

Referencias de ruta de acceso, libros,

hojas y celdas.

Fuente y destino estn en libros de

trabajo diferentes y ubicaciones

diferentes en el disco.

Nombre de un rea con nombre El rea fuente es un rea con nombre.

Es posible especificar hasta 255 reas fuente para consolidar. Las hojas de

clculo que contienen las reas fuente no necesitan estar abiertas durante la

consolidacin. Aunque puedes usar hojas de clculo que no fueron guardadas

como fuentes de datos, se recomienda guardar las hojas de clculo fuente antes

de consolidar.

c) Todas las referencias: Muestra todas las referencias de las reas fuente que ha

elegido para la consolidacin.



Avanzado

d) Usar rtulos en: Define si deben usarse los rtulos del rengln superior de las

reas fuente, de la columna izquierda, ambos o ninguno. Estos cuadros de

chequeo slo se utilizan cuando se consolidan datos por categora. Varias reas

fuente deben presentarse y estar rotuladas de manera idntica para que esta

opcin funcione adecuadamente. Al consolidar por categora es necesario incluir

los rtulos de la categora en las reas fuente que selecciona para la

consolidacin. Excel transfiere stos rtulos al rea de destino automticamente.

e) Crear vnculos con los datos de origen: Crea vnculos desde el rea destino a

las reas fuente cuando consolida datos para que el rea de destino se actualice

automticamente siempre que cambien los datos fuente. Excel crea una frmula

de vinculacin para cada celda e inserta filas o columnas en el rea de destino

para que contengan las frmulas de vinculacin para cada parte de los datos

fuente. Despus, el rea de destino se agrupa con las frmulas de vinculacin

ubicadas en las filas o columnas ocultas subordinadas a posiciones o a categoras

en el rea de destino.

f) Examinar: Abre un cuadro de dilogo en el que permite seleccionar un archivo

con reas fuente para agregarlo a la lista. Todas las referencias. El nombre del

archivo seleccionado se coloca en el cuadro Referencia, donde deber agregarle

la referencia de celda o de rango.

g) Agregar: Agrega la referencia especificada en Referencia a la consolidacin sin

cerrar el cuadro de dilogo.

h) Eliminar: Eliminar la referencia del rea fuente seleccionada de la lista.

Para consolidar datos por posicin:



Avanzado

Debers verificar que la organizacin de los datos de todas las reas sea idntica.

Los valores que deseas consolidar de cada rea fuente deben estar en la misma

posicin relativa dentro del rea fuente seleccionada.

Selecciona el rea de destino para los datos consolidados.

Elige el men Datos Consolidar.

En Funcin, selecciona la funcin de resumen que deseas utilizar para la

consolidacin.

En Referencia, escribe las reas fuente que deseas consolidar. Si es

necesario especifica la ruta.

Elige el botn Agregar si deseas adicionar ms referencias, para mantener

el cuadro abierto.

Al terminar de especificar todas las referencias, selecciona la caja de

chequeo Crear vnculos con los datos de origen si deseas que Excel

actualice la tabla de consolidacin automticamente cuando cambian los

datos fuente; de lo contrario, debers elegir el men Datos Consolidar

cada vez que actualices los datos fuente.

Pulsa en el botn Aceptar.

Para consolidar por categora:

Selecciona el rea de destino para los datos consolidados. Si escribiste

rtulos de categoras (como encabezados de filas y de columnas) en el

rea de destino, incluye dichas celdas al seleccionar esa rea.



Avanzado

Elige el men Datos Consolidar.

En Funcin, selecciona la funcin de resumen que deseas utilizar para la

consolidacin.

En Referencia, escribe las reas fuente que deseas consolidar. Si es

necesario especifica la ruta. Recuerda incluir los rtulos de categora que

se quieras utilizar. Si la hoja de clculo del rea fuente no est abierta, elige

el botn Examinar y seleccinalo de la lista.

Elige el botn Agregar si deseas adicionar ms referencias para mantener

el cuadro abierto.

Si el primer rengln de las reas fuente contienen rtulos, activa la caja Fila

superior. Si la columna izquierda de las reas fuente contiene rtulos de

categora, activa Columna izquierda. Las dos cajas pueden seleccionarse

en una sola consolidacin. Si solamente una de las dos opciones estn

seleccionadas y por posicin para la casilla de verificacin que no est

seleccionada.

Activa la caja Crear vnculos con los datos de origen si deseas que Excel

actualice la tabla de consolidacin automticamente cuando cambien los

datos fuente; de lo contrario, debers elegir el men Datos Consolidar

cada vez que se actualicen los datos fuente.

Pulsa en el botn Aceptar.



Avanzado

REGLAS DE VALIDACIN

Las herramientas de validacin de Excel te ayudarn a buscar errores en los libros

y hojas de clculo, a corregirlos y a evitar la introduccin de datos incorrectos.

Puedes aplicar las herramientas de validacin para localizar los errores de forma

que los puedas corregir inmediatamente.

Sin embargo, puedes evitar que se produzcan errores, especificando los valores

que sern aceptados al introducir los datos. Si alguien introduce un valor que est

fuera del rango permitido, Excel te comunicar un mensaje de error (que puedes

tambin personalizar).

S, a pesar de ello, el dato errneo es introducido, podrs comprobar ms tarde la

hoja de clculo o el libro, para marcar todos los datos invlidos y corregirlos

personalmente.

Con la ayuda de las reglas de validacin se define qu datos son vlidos para

celdas individuales o rangos de celdas; restringe la entrada de datos a un tipo

particular como nmeros enteros, nmeros decimales o texto y establece lmites

para las entradas vlidas.

Cabe mencionar que estas reglas restringen el valor escrito por un usuario

directamente en la celda, pero el usuario podr utilizar los comandos para copiar y

pegar para transferir informacin invlida a una celda restringida.

Para hacer uso de la Validacin de datos es necesario seleccionar la(s) celda(s) a

las que se desean aplicar las reglas de validacin y seguir con el siguiente

procedimiento.



Avanzado

Seleccionar el men Datos Validacin de Datos y aparecer un cuadro de

dilogo Validacin de Datos similar que se muestra en la siguiente imagen

En la pestaa de Configuracin se selecciona el recuadro de Permitir el tipo de

dato al que se le va a dar entrada a esa (s) celda(s). Dependiendo la seleccin

pregunta diferentes datos para tener la informacin completa.

Se puede tener la opcin de introducir en una celda un mensaje y que al momento

de estar en esa celda se despliegue un mensaje. Para utilizar esta opcin se

selecciona la pestaa Mensaje entrante y aparecer el cuadro de dilogo similar al

que se muestra en la siguiente imagen con las siguientes opciones.



Avanzado

Mostrar mensaje al seleccionar la celda: Esta opcin puede o no estar

activa.

Mostrar este mensaje al seleccionar la celda: En esta opcin se escribe

el mensaje que se desea que aparezca.

Ttulo: Se escribe el encabezado del mensaje en cuestin.

Mensaje: de entrada: Se escribe el texto que deseas que aparezca al

seleccionar la celda.

En el separador Mensaje de error el cul se muestra en la siguiente imagen se

puede introducir un mensaje de error en caso de que el contenido de la celda no

est correcto con respecto a lo que se indic en el separador de Configuracin.



Avanzado

La opcin Mostrar mensaje de error si se introducen datos no vlidos es opcional,

si se activa en la opcin Mostrar este mensaje de alerta si el usuario introduce

datos no vlidos, se proceder a escribir el Ttulo y el Mensaje de error de la

misma manera que en el separador Mensaje entrante en la caja de lista.

Estilo: Se puede definir si el mensaje es tipo lmite: El cual no deja introducir mal la

informacin. Advertencia: Que muestra un mensaje indicando que est mal la

informacin.



Avanzado

INTRODUCCIN

Una BASE DE DATOS es un conjunto de datos organizados bajo ciertas

caractersticas, la cual permite hacer una bsqueda rpida de la informacin que

se desee as como un filtrado de la misma, adems de funcionar como una

herramienta utilizada para almacenar, organizar y recuperar informacin. Por

ejemplo, si se desea guardar los nombres y direcciones de toda la gente que se

encuentra en la lista de tarjetas de Navidad, se puede crear una base de datos y

luego guardar la siguiente informacin por cada persona: nombre, apellido,

direccin, etc. Cada parte de la informacin se traduce en un Campo (Celda)

separado de la lista. Todos los campos de una persona forman un registro.

En Excel, una celda es un campo y una fila de campos integran un registro.

En la Base de Datos a los rtulos de columna de la lista se les llama Nombres

de campo. Se deben observar las siguientes reglas al introducir informacin en la

base de datos.

Campos

Registros



Avanzado

Nombres de campo: Se debe introducir nombres de campo en la primera

fila de la base de datos. Por ejemplo, se podra escribir Nombre en la

columna que contendr los nombres y Apellido en la columna que

contendr los apellidos. No se debe saltar ninguna fila entre las filas de los

nombres de campo y el primer registro.

Registros: Cada registro debe encontrarse en una fila separada y no debe

haber filas vacas entre los registros.

Mismos elementos: Las celdas de una columna determinada deben

contener informacin del mismo tipo. Por ejemplo, si se tiene una columna

de cdigos postales, todas las celdas de esa columna deben contener un

cdigo postal y no algn otro tipo de datos. Es posible dejar una celda en

blanco, si una columna en particular no se aplica a ese registro.

Clculos: Se puede crear un campo calculado, que utilice informacin de

otro campo del mismo registro y produzca un resultado. Por ejemplo, si se

tiene una columna llamada totales de ventas se puede utilizar una frmula

para crear los valores de una columna llamada comisin (para esto se

debe introducir una frmula).

ORDENAR DATOS

En una base de datos Excel permite ordenar por filas o columnas, en orden

ascendente o descendente y considerando o ignorando las maysculas. Incluso se

pueden definir secuencias de ordenacin personalizadas para que, por ejemplo,

los nombres de divisiones de una compaa siempre aparezcan en el orden Norte,

Sur, Este y Oeste, en lugar de aparecer en orden alfabtico.

Para ordenar una Base de Datos se debe comenzar por seleccionar una celda en

cualquier lugar de la Base de Datos. Despus se elige la opcin de Ordenar del

Men Datos. Excel examina los datos para determinar la extensin de la lista,

determina si la lista contiene una fila de encabezado que no debera ordenarse y

entonces muestra el cuadro de dilogo Ordenar.



Avanzado



Avanzado

ORDENAMIENTO PERSONALIZADO

El primer paso para crear una lista personalizada es introducir los valores de la lista en un rango de celdas.

Posteriormente debemos ir a la ficha Archivo y hacer clic sobre Opciones e ir a la

seccin Avanzadas donde encontraremos el botn Modificar listas personalizadas.



Avanzado

Al pulsar el botn se mostrar el cuadro de dilogo Listas personalizadas donde

podremos especificar el rango de celdas que contiene nuestra lista.



Avanzado

Despus de haber especificado el rango de celdas con la lista solamente debemos

pulsar el botn Importar y se crear una nueva lista personalizada.

Para crear una secuencia nueva de ordenacin personalizada se deben seguir

estos pasos:

Ir a la pestaa de Datos dar clic en la opcin ordenar como se muestra en la

imagen



Avanzado

Nos enviara la siguiente imagen

Al principio tienes un solo criterio para ordenar, pero si das clic en el botn de

Agregar nivel podrs aadir los que desees. En la imagen se ha definido un

orden por sexo y despus, dentro de l, por nombre

Puedes cambiar tus criterios eliminando cualquier nivel, haciendo uso de las

Opciones y con las flechas de ordenacin ascendente y descendente. Prueba

esas tres operaciones.



Avanzado

FILTROS

Muchas veces se requiere trabajar con un subconjunto de la base de datos; un

grupo de registros de la base de datos. Por ejemplo, si se desea imprimir todos los

registros de ventas de un vendedor, todos los pedidos de un cliente o todos los

clientes que no han realizado ninguna compra este ao.

Se utilizar un filtro para seleccionar los registros que cumplen un criterio

denominado y ocultar temporalmente todos los registros y recuerda que debes

introducir las condiciones para definir el filtro.

USO DEL AUTOFILTRO

Para hacer que Excel establezca un Autofiltro. Selecciona cualquier celda de la

base de datos y ejecuta Datos Filtro Autofiltro. Excel lee cada registro de la

base de datos y crea una lista de condiciones de filtro para cada campo.

Haz clic sobre la fecha de seleccin que aparece junto al nombre de cada campo

para acceder a la lista de condiciones del campo.



Avanzado



Avanzado

La configuracin por defecto de las condiciones de cada campo es Todas, que

quiere decir que el contenido del campo no est siendo utilizado para filtrar los

registros.

Las 10 ms, se utiliza con campos numricos para presentar los 10, 5 o cualquier

otro nmero o porcentaje de valores ms altos o ms bajos.

Personalizar ofrece la opcin de crear un filtro personalizado en caso de que

desees otras opciones que no aparecen en la lista.

Filtro de texto

Otra de las opciones de la ventana, y que es muy til, es la de filtrar un texto de

forma avanzada, y no slo por su contenido completo.

Es igual, No es igual, Contiene, etc., que se explican por s solas. Por ejemplo,

seala los nombres, elige Filtro de Texto y despus Contiene. Si eliges la letra s

como en la imagen, nos seleccionar slo a Lourdes, Luisa y Carlos, pues la

palabra Pedro no contiene la s.

Filtros



Avanzado

Puedes combinar dos criterios, por ejemplo, que contenga a s pero no la c y slo aparecern Lourdes y Luisa.



Avanzado

Comodines En todas las opciones ofrecidas puedes usar los caracteres comodn ? y * El primero, ? sustituye a un solo carcter, por ejemplo, si escribes C?les te devolver palabras como coles, cales o celes. El segundo, * sustituye a todo un grupo de caracteres. Este es mucho ms til, pues si, por ejemplo, buscas G*, te filtrar todos los apellidos que comiencen por G, tengan la longitud que tengan. Los dos caracteres se pueden usar varias veces en una misma palabra: B?po?z, *j*, M*l*, etc.

Se pueden utilizar operadores de comparacin como se muestran en la siguiente

lista:

Operador Comparacin

Mayor que

Menor que

Mayor o igual que

Menor o igual que

Diferente

Igual que

Filtrado con el botn derecho

Si seleccionas una celda cualquiera y usas el botn derecho, se te ofrece un filtro

segn el valor de la celda actual, e incluso por su color o por el icono que

contenga.



Avanzado

En la imagen se ha seleccionado una celda que posee como icono una flecha

amarilla ascendente. Si usamos este filtrado por iconos, nos resultarn todas las

celdas que poseen la misma flecha.

FILTROS AVANZADOS

Los filtros avanzados permiten tener un mayor nmero de criterios de bsqueda,

su manejo es similar al de los filtros automticos. Para utilizarlos se selecciona el

men Datos Filtro Filtro avanzado.



Avanzado

A continuacin aparecer la siguiente pantalla de FILTROS AVANZADOS.

La opcin Filtrar sin moverla a otro lugar es similar a la operacin que se realiza

en filtros automticos; Copiar a otro lugar permite copiar el resultado de la

bsqueda en otro lugar que no sea dentro de la misma tabla, al activar esta

opcin, se activa tambin el cuadro Copiar a donde se tiene que especificar el

rango de celdas en donde se va a copiar la informacin filtrada.

Rango de lista: Escribe las referencias de las celdas en donde se encuentra

la lista o la base de datos en cuestin.

Slo registros nicos: Muestra un solo registro cuando se encuentren

registros duplicados.



Avanzado

Rango de criterio: Este rango debe ser otra rea de la hoja, diferente al

rea de copiado en donde se determinarn los criterios de la bsqueda.

Debe de ser el mismo nmero de campos de la lista y por lo menos una fila

en donde se escribirn los criterios para el filtrado de datos.

FORMULARIO (FICHA)

Los formularios de datos te proporcionan una forma fcil de introducir o buscar los

datos personalmente y ofrecen asimismo la posibilidad de que los datos sean

introducidos de forma eficiente por un usuario poco experto.

Para abrir el formulario de datos en la barra damos clic derecho y elegimos

Personalizar la barra de herramientas de acceso rpido. En los comandos

disponibles elegimos Formulario y presionamos Agregar >>



Avanzado

A continuacin se mencionan los pasos a seguir:

Seleccionar cualquier celda de la base de datos y ejecutar el comando Formulario

del Men Datos, para abrir un formulario de datos. El formulario mostrar el

primer registro de la base de datos.



Avanzado

Dado que los formularios estn orientados verticalmente, resultan particularmente

tiles si las columnas de tu base de datos superan el ancho de la pantalla. El uso

del formulario permite ver todos los campos de la base de datos a la vez sin tener

que desplazarse horizontalmente.

En el formulario, se utiliza la barra de desplazamiento vertical o las flechas hacia

arriba y hacia abajo para desplazarse por los registros. Utiliza la tecla Tab para

moverte por los campos del formulario; para pasar al siguiente registro,

pulse Intro.

Se puede modificar el contenido de un campo editando el cuadro de texto situado

junto al nombre del campo. Los contenidos de los campos calculados se muestran

sin cuadro de texto porque no pueden ser editados. No obstante, si modificas el

valor en que est basado el clculo, Excel volver a calcular ese campo. Para

desechar las modificaciones efectuadas en un registro, haz clic sobre el botn

Restaurar datos y se pasar a otro registro.



Avanzado

SUBTOTALES

La agrupacin de datos puede ser til en diversos casos, no obstante, cuando lo

que se quiere es usar los grupos para crear totales o subtotales, existe un mtodo

ms rpido para conseguir exactamente el mismo resultado.

Lo que tenemos que hacer es posicionarnos en cualquier celda de nuestra hoja de

clculo y seleccionamos la opcin de Datos Subtotales, aparecer una ventana

con varios apartados.

En el primero se indica la columna que tiene los datos por agruparse. La siguiente

lista desplegable sirve para seleccionar la operacin a realizar, siendo habitual

obtener la suma. Por ltimo, en la lista ubicada en la parte inferior, se marcarn las

columnas a totalizar.



Avanzado

En nuestro ejemplo elegiremos, totalizar el nmero de departamento y edad y su

valor agrupndolos por empleado. Dejamos activada la opcin Resumen debajo

de los datos, por lo que Excel, aparte de los subtotales por grupo, aadir tambin

un total general.

El resultado tras aceptar todos los parmetros, ser similar al que puede verse en

la pantalla.

Si se desea eliminar los subtotales creados con esta opcin, no tienes ms que

pulsar el botn Quitar todos que hay en la ventana.

LISTAS

Si bien Excel no tiene como principal objetivo el manejo de bases de datos,

incorpora caractersticas que le permiten operar con el equivalente a las tablas. El

concepto que utiliza Excel para referirse a una tabla es el de la Lista.



Avanzado

Por ello, una lista es una serie de filas de la hoja de clculo que contienen datos

similares. Adems, con Excel, se podr aprovechar su capacidad de anlisis y de

clculo para explotar la informacin contenida en la lista.

Para crear una lista existen dos posibilidades y se siguen los siguientes pasos:

Introduccin manual de los datos: Para que Excel reconozca un conjunto de datos

en la hoja de clculo como una lista, se debe:

Situar en una zona continua dentro de la hoja, aislada del resto de la

informacin contenida en la hoja por, al menos, una columna y una fila

vacas. Es aconsejable que la hoja de trabajo se reserve en exclusiva para

la lista.

Evitar cualquier discontinuidad dentro de la lista, como filas o columnas

vacas, lneas de divisin, etc. Si se desean delimitar secciones de la lista,

se pueden utilizar las opciones que proporcionan los bordes de las celdas,

pero nunca utilizar el contenido de las celdas como elementos

diferenciadores.

Los ttulos de los campos de la lista se correspondern con los datos

introducidos en la primera fila debajo de cada columna. Si se requiere

diferenciar el ttulo de la columna de su contenido, puede ser conveniente

utilizar un tipo de formato diferente para la primera fila, como un tipo y/o

tamao de letra diferente al resto, otro color, aplicar sombreado a la fila,

etc., con lo que se va a obtener mayor claridad.

Es conveniente que los ttulos de los campos no se repitan.

Puesto que las listas son el anlogo en Excel a una tabla de una base de datos, la

informacin que contiene cada columna debe ser homognea (debe referirse al

mismo nombre, mes, zona de ventas, importe, etc). Esta homogeneidad en la

columna se resalta utilizando el mismo formato en sus celdas.



Avanzado

FUNCIONES

Las funciones son programas estructurados que calculan un resultado

especfico: un total, un promedio, el valor de un plazo mensual o la

media geomtrica de un conjunto de nmeros.

IMPORTANCIA DE LAS FUNCIONES

Podramos decir que una funcin es una frmula compleja prefabricada, lista para

usar donde pueda ser til. En cierta forma, las funciones son como pequeos

programas que se ejecutan en el momento en que los llamamos por su nombre.

Adems, Excel permite ampliar el nmero de funciones disponibles. El usuario

puede escribir cualquier funcin que necesite usar y precisamente con un lenguaje

de programacin, conocido como Visual Basic para Aplicaciones.

La mayora de las funciones existentes necesitan argumentos sobre los que

operan. En el caso de la funcin SUMA por ejemplo, los argumentos son las

celdas a sumar. En caso de que la funcin precise varios argumentos o

parmetros, cada uno se separar del siguiente mediante un punto y coma (;).

Los argumentos que se pueden utilizar para una funcin pueden ser prcticamente

de cualquier tipo, desde valores numricos hasta referencias a celdas, pasando

por los ttulos o rangos. Incluso es posible utilizar como argumentos otras

funciones, dando lugar a lo que se conoce como anidacin. La frmula siguiente,

por ejemplo, llamara a la funcin SUMA entregando tres argumentos, dos de los

cuales son otras sumas y el tercero es un valor.

=SUMA (SUMA (B4:B12);SUMA (C4:C13);10)



Avanzado

Para acceder a la barra de Frmulas hay que dar clic en la pestaa de frmulas

como se muestra en la siguiente imagen.



Avanzado

CMO UTILIZAR FUNCIONES

Antes de introducir una funcin, asegrate de que la celda en la que

deseas obtener el resultado est activa.

Haz clic sobre el icono de la barra de frmulas para abrir la barra de

frmulas.



Avanzado

Haz clic sobre el combo de seleccionar una categora en usadas

recientemente de la lista del cuadro de funciones para abrir la lista de

las ltimas funciones utilizadas.

Si la funcin que quieres usar est en la lista, seleccinala y Excel la

llevar a la barra de frmulas. La Barra de Frmulas se expandir para

incluir una descripcin de la funcin, como se muestra en la siguiente

pantalla.

En las funciones corrientes que utilizan un rango de celdas como

argumento. Excel adivinar los nmeros que probablemente se querrn sumar o promediar y colocar el rango correspondiente en el

cuadro de texto del argumento. Los argumentos necesarios estn

rotulados en negrita, como Nmero 1 como se muestra en la siguiente

pantalla. Se deben rellenar estos cuadros de texto para utilizar

correctamente la funcin.



Avanzado

PRINCIPALES FUNCIONES MATEMTICAS

Las funciones son frmulas, de manera que todas las funciones

comienzan con el signo de igual (=). Despus viene el nombre de la

funcin, seguido de uno o ms argumentos separados por punto y coma

(;) y encerrados entre parntesis.

=SUMA (B6:B10)

Las funciones de Excel se agrupan en 10 categoras, que se indican en

las siguientes tablas.

Argumento opcional

Nombre de

la Funcin

Argumento necesario

Descripcin de la Funcin

Nombre de la Funcin

Argumento

Parntesis



Avanzado

Categora Ejemplos

Financiera Calcula la tasa de inters, pagos de

prstamos, amortizaciones, etc.

Fecha y Hora Proporciona la hora actual, el da

de la semana o del ao, la hora o

la fecha.

Matemticas y Trigonometra Calcula valores absolutos, cosenos,

logaritmos, etc.

Estadsticas Incluye funciones comunes para

calcular totales, promedios,

mximo y mnimo de un rango y

funciones avanzadas para

distribuciones t, distribuciones chi,

desviacin, etc.

Bsqueda y referencia Busca y proporciona valores de un

rango, crea hipervnculos a

documentos o de red.

Base de Datos Calcula valores de una tabla de

base de datos de Excel.

Texto Convierte texto a maysculas o

minsculas, recorta caracteres por la derecha o por la izquierda de

una cadena de texto, enlaza

cadenas de texto.

Lgica Analiza una expresin y

proporciona un valor VERDADERO

o FALSO; se usa para

desencadenar otras acciones o formatos.

Informacin Proporciona informacin de Excel o

de Windows sobre el estado actual

de una celda, de un objeto o del

entorno.

Ingeniera Se incluye con Office, pero se debe

instalar aparte desde el Paquete de

Herramientas de anlisis.



Avanzado

PRINCIPALES FUNCIONES MATEMTICAS

Funcin Descripcin

ABS (nmero) Obtiene el valor absoluto de

un nmero.

ALEATORIO () Obtiene un nmero al azar

entre 0 y 1.

COMBINAT(nmero, tamao) Da el nmero de

combinaciones de los

objetos.

ENTERO (nmero) Obtiene el entero del

nmero.

EXP (nmero) Obtiene e (es el exponente aplicado a la

base e o mejor conocido

como nmero de Euler)

elevado a la potencia del

nmero.

FACT (nmero) Obtiene el factorial del

nmero.

GRADOS (radianes) Convierte radianes a

grados.

LN (nmero) Obtiene el logaritmo

natural del nmero.

LOG (nmero,base) Obtiene el logaritmo del

nmero en la base

especificada.

LOG10(nmero) Obtiene el logaritmo del

nmero en base 10.

MDETERM (matriz) Obtiene la matriz

determinante de una matriz

dada.

MINVERSA(matriz) Obtiene la inversa de la

matriz dada.

MMULT (matriz1,matriz2) Obtiene el producto de dos

matrices.

MULTIPLO.INFERIOR(nmero,cifrasign) Redondea un entero al

mltiplo ms prximo al

argumento en direccin a

cero.

NUMERO.ROMANO(nmero) Obtiene le nmero romano

con formato de texto.

PI() Obtiene el valor del nmero



Avanzado

Pi.

POTENCIA(nmero,potencia) Obtiene el nmero al elevar

el argumento al nmero de

potencia.

PRODUCTO(nmero1,nmero2) Obtiene el producto de los

nmeros.

RADIANES (grados) Convierte grados en

radianes.

RAZ(nmero) Obtiene la raz cuadrada del

nmero.

REDONDEAR.IMPAR(nmero) Redondea el nmero al

entero ms cercano

prximo impar.

REDONDEAR.PAR(nmero) Redondea el nmero al

entero ms cercano

prximo par.

REDONDEAR (nmero,nmero de dgitos) Redondea el nmero al

nmero de dgitos

especificado.

REDONDEAR.MAR(nm,nm decimales) Redondea un nmero hacia

arriba, en direccin opuesta

a cero.

REDONDEAR.MENOS(nm,nm decimales) Redondea un nmero hacia

arriba, en direccin a cero.

RESIDUO(nmero,divisor) Obtiene el residuo del

nmero dividido entre el

divisor.

SIGNO(nmero) Obtiene el signo del

nmero.

SUBTOTALES(num_funcin,ref1) Obtiene el subtotal dentro

una lista o base datos.

Num_funcin, se refiere al

tipo de clculo:

1) Promedio, 2) Contar, 3)

ContarA, 4) Mximo,

5) Mnimo, 6) Producto, 7)

Desv.Estndar, 8) Desv.

Estndar-pobl.,9) Suma,

10) Varianza y

11) Varianza-pobl.

SUMA(nmero1,nmero2) Suma los argumentos

dados.

SUMA.CUADRADO(nm1,nm2) Suma los cuadrados de los

argumentos.



Avanzado

SUMAPRODUCTO(matriz1,matriz2,matriz3) Multiplica los componentes

de las matrices

suministradas y devuelve a

suma de los productos.

SUMAR.SI(rango,criterio,rango_suma) Suma celdas que coincidan

con el argumento.

SUMAX2MASY2(Matriz1,matriz2) Obtiene la sumatoria de la

suma de cuadrados entre

los valores

correspondientes en dos

matrices.

SUMAX2MENOSY2(matriz1,matriz2) Devuelve la sumatoria de la

diferencia de cuadrados

entre los valores en dos

matrices.

SUMAXMENOSY2(matriz_x,matriz_2) Devuelve la sumatoria de la diferencia de cuadrados

entre la diferencia de

valores en dos matrices.

TRUNCAR(nmero,nm_decimales) Convierte un nmero

decimal a un entero.

PRINCIPALES FUNCIONES ESTADSTICAS

FUNCIN DESCRIPCIN

BINOM.CRIT(ensayos,prob. De xito, valor

del criterio)

Obtiene el valor menor

cuya distribucin

binomial acumulativa es

menor o igual que un valor de criterio.

COEF.DE.CORREL(matriz1,matriz2) Obtiene el coeficiente de

correlacin entre dos

conjuntos de datos.

CONTAR(valor1,valor2...) Cuenta el nmero de

valores.

CONTAR.BLANCO(rango) Cuenta el nmero de

celdas en blanco dentro

de un rango especificado.

CONTAR.SI(rango,criterio) Cuenta las celdas en el

rango que coinciden con

la condicin dada.

CONTARA(valor1,valor2) Cuenta los valores en

valores.

COVAR(matriz1,matriz2) Obtiene la covarianza de



Avanzado

los valores por pares.

CRECIMIENTO(conocidoy,conocidox,nuevas

x)

Valores de un

crecimiento exponencial.

CUARTIL(Matriz,cuartil) Devuelve el cuartil de un conjunto de datos.

DESVEST(nmero1,nmero2) Obtiene la desviacin

estndar basada en una

muestra.

DESVESTA(ref1,ref2...) Obtiene la desviacin

estndar basada en una

muestra, incluyendo

valores lgicos y texto.

DESVESTPA(ref1,ref2...) Obtiene la desviacin

estndar basada en toda

la poblacin, incluyendo


DESVIA2(nmero1,nmero2) Obtiene la suma de los

cuadrados de las

desviaciones.

DESVPROM(nmero1,nmero2....) Obtiene el promedio de

las desviaciones

absolutas de la media de los puntos de los datos.

DIST.WEIBULL(x,alfa,beta,acumulado) Obtiene la probabailidad

de una variable aleatoria

con base a una

distribucin de

probabilidad Weibull.

DISTR.BETA(valor dentro del intervalo,

parmetro de distr.1, parmetro de distr.2,

lmite inf. De intervalo, lm. Superior del

intervalo).

Obtiene la probabilidad

con base a una funcin

de densidad de

probabilidad beta

acumulativa.

DISTR.BINOM(nm,xito,ensayos,prob de

xito, acumulado)



discreta con base a la

distribucin binomial.

DISTR.CHI(x,grados_libertad) Obtiene la probabilidad


continua con base a la

distribucin chi cuadrado

de una sola cola.

DISTR.EXP(valor de la funcin, valor del

prametro.acum)





Avanzado

con base a una

distribucin exponencial.

DISTR.F(x,grados libertad1,gradoslibertad2) Obtiene la probabilidad


con base a una

distribucin de

probabilidad F.

DISTR.GAMMA(valor dentro del intervalo,

parmetro de distri.1, parmetro de distr.2,

valor lgico que determina la forma de la

funcin)



con base a una

distribucin de

probabilidad gamma.

DISTR.HIPERGEOM(muestra_xito,nm_de

_muestra,poblacin_xito....)


de para una variable

aleatoria discreta

siguiendo una

distribucin

hipergeomtrica.

DISTR.LOG.NORM(x,media,desv_estndar) Obtiene la distribucin

logartmico-normal

acumulable de x.

DISTR.NORM(z) Obtiene la probabilidad de una variable aleatoria

continua con base a una

distribucin estndar

acumulativa.

DISTR.NOMR.INV(prob,media,desv.Estndar) Obtiene la probabilidad


con base a una distribucin normal

estndar acumulativa.

DISTR.T(x,grados de libertad,colas) Obtiene la probabilidad


con base a una

distribucin t de Student.

ESTIMACIN. LINEAL(conocido_y,conocido_

X,constante,estadstica)

Obtiene una matriz de

valores que describen los

datos en forma

exponencial.

ESTIMACIN. LOGARTMICA (conocido_y,

conocido_x,contante,estadstica)

Obtiene una matriz de

valores que describen los

datos en forma

exponencial.

FRECUENCIA (datos,grupos) Obtiene una distribucin



Avanzado

de frecuencia como una

matriz vertical.

INTERSECCION.EJE (conocido_y,conocido_x) Calcula el punto en el

cual una lnea

intersectar el eje Y.

JERARQUA (nmero,referencia,orden) Devuelve la jerarqua de

un nmero dentro de una

lista.

K.ESIMO.MAYOR(matriz,k) Obtiene el k-simo

mayor valor de un conjunto de datos.

K.ESIMO.MENOR(matriz,k) Obtiene el k-simo

menor valor de un

conjunto de datos.

MAX(nmero1,nmero2.....) Obtiene el nmero

mayor.

MAXA (ref1,ref2...) Obtiene el valor mximo

de un conjunto de

valores, incluyendo


MEDIA.ACOTADA)matriz,porcentaje) Obtiene la media de la

proporcin interior de un conjunto de datos.

MEDIA.ARMO(nmero1,nmero2,.......) Obtiene la media de la

proporcin interior de un

conjunto de datos.

MEDIA.GEOM)nmero1,nmero2,.......) Obtiene la media

geomtrica de una matriz

o rango de datos

numricos positivos.

MEDIANA(nmero1,nmero2........) Obtiene la mediana de

los nmeros.

MIN(nmero1,nmero2,.......). Obtiene el nmero

menor.

MINA(ref 1,ref 2,.....) Obtiene el valor mnimo

de un conjunto de

valores, incluyendo


MODA(nmero1,nmero2,..... ) Obtiene el valor ms

repetido de un conjunto

de datos.

NORMALIZACION(x,media,desv.Estndar) Obtiene el valor

normalizado.

PENDIENTE)matriz1,matriz2,) Obtiene la pendiente de



Avanzado

una lnea de regresin

lineal.

PERCENTIL(matriz,k) Obtiene el k-simo

percentil de los valores

de un rango.

PERMUTACIONES (nmero,tamao) Obtiene el nmero de

permutaciones para un

nmero determinado de

objetos.

POISSON(x,media,acumulado) Obtiene la probabilidad de que los valores de un

rango se encuentren

entre dos lmites.

PROBABILIDAD (rangox,rango probabilidad,

lmiteinf, lmite sup)


de que los valores de un

rango se encuentren entre dos lmites.

PROMEDIO (nmero1,nmero2,......) Obtiene el promedio de

los nmeros.

PROMEDIOA(ref1,ref2,.....) Obtiene el promedio de

los argumentos.

PRONSTICO (matriz1,matriz2,......) Obtiene un nmero en base a una tendencia

lineal.

TENDENCIA (matriz2,nuevos x,constante) Obtiene los valores que

resultan de una

tendencia lineal.

VAR(nmero1,nmero2,...) Estima la varianza de la

poblacin basada en una

muestra.

VARA(nmero1,nmero2,......) Estima la varianza de

una muestra, incluyendo


VARP(nmero1,nmero2,.....) Estima la varianza de la

poblacin basada en toda la poblacin.

VARPA(ref1,ref2,......) Estima la varianza de la

poblacin total,

incluyendo valores

lgicos y texto.

FUNCIONES LGICAS

FUNCIN DESCRIPCIN



Avanzado

FALSO Valor lgico Falso.

NO (Lgica) Es verdadera si lgica es

falso y falso si lgica es

verdadera.

O(lgica1,lgica 2,.....) Es verdadera si cualquier

argumento se cumple.

SI(Prueba_lgica,valor_si_verdadero,valor_si

_falso)

Obtiene el

valor_si_verdadero si la

prueba lgica se cumple,

de otra forma obtiene el valor_si_ falso.

VERDADERO Valor lgico verdadero.

Y(Lgica1,Lgica2) Es verdadera si cada

argumento es verdadero.

FUNCIONES DE FECHA Y HORA

FUNCIN DESCRIPCIN

AHORA () Obtiene la fecha y la hora actual

AO () Obtiene el ao de la fecha actual.

DA () Da del mes actual.

DIA360(fecha inicial,fecha final) Nmero de das entre dos fechas.

DIASEM(nmero de serie) Obtiene el da de la semana.

FECHA(ao,mes,da) Regresa la fecha especificada.

FECHANMERO(texto de la fecha) Regresa la fecha especificada en el

texto.

HORA(nmero de serie) Obtiene la hora actual del nmero

dado.

HORANUMERO(texto de la hora) Convierte una hora con formato texto en nmero de serie.

HOY() Obtiene la fecha actual.

MES (nmero de serie) Obtiene el mes del nmero de

serie.

MINUTO(nmero de serie) Obtiene el minuto del nmero de

serie.

HORA(nmero de serie) Obtiene el nmero de serie para

una hora dada.

SEGUNDO(nmero de serie) Obtiene el segundo del nmero de

serie indicado.

PRINCIPALES FUNCIONES DE BSQUEDA Y REFERENCIA

FUNCIN DESCRIPCIN



Avanzado

AREAS (referencia) Nmero de reas en

una referencia.

BUSCAR(valor buscado, vector de

comparacin, vector resultado)

Busca valores en un

vector.

BUSCARH(valor buscado, matriz buscar en,

indicador renglones, ordenado)

Busca en el primer

rengln de un arreglo y

regresa el valor de la

celda indicada.

BUSCARV(valor buscado, matriz buscar

en,ind,columnas,ordenado)

Busca en la primera

columna de un arreglo y se mueve en los

renglones para regresar

el valor de una celda.

COINCIDIR (valor buscado,matriz,tipo) Obtiene valores en una

referencia o matriz. Si

el tipo es 0, busca una coincidencia exacta. Si

el tipo es 1, busca el

mayor valor que sea

menor que igual al

valor buscado.

COLUMNA(referencia) Regresa el nmero de columna de una

referencia.

COLUMNAS(matriz) Regresa el nmero de

columnas en un arreglo.

DESREF(referencia,renglones, columnas) Obtiene una referencia

desviada a partir de la

referencia dada.

DIRECCIN(rengln,columna) Una referencia como

texto de una celda

referenciada por su

nmero de rengln y

columna.

ELEGIR(ndice,valor1,valor2,...) Regresa el valor seleccionado de los

valores listados

utilizando un nmero de

ndice.

FILA(referencia) Regresa un arreglo de

un nmero de renglones.

FILAS(matriz) Regresa el nmero de

renglones en un



Avanzado

arreglo.

HIPERVINCULO(ubicacin_del_vnculo_nombre

_ descriptivo)

Crea un acceso directo

o salto que abre un

documento guardado en

el disco duro, en un

servidor de red o en

Internet.

INDICE(referencia, nmero de renglones,

nmero de columnas)

Utiliza un ndice para

escoger un valor de un

arreglo.

INDIRECTO(referencia) Regresa el contenido de

una referencia en forma

de texto.

TRANSPONER(matriz) Transpone un arreglo o

matriz.

PRINCIPALES FUNCIONES DE TEXTO

FUNCIN DESCRIPCIN

CARACTER)valor) Devuelve el CARACTER

especificado por el cdigo

nmero

CODIGO(valor) Devuelve el nmero de cdigo

del primer carcter del texto.

CONCATENAR(texto1,texto2,......) Concatena argumentos de texto.

DECIMAL(nmero,decimales,nmero

de separador de miles)

Da formato de texto a un

nmero con un nmero

determinado de decimales.

DERECHA(texto,nmeros de

caracteres)

Extrae los caracteres del extremo

derecho de un valor de texto.

ENCONTRAR(texto buscado, texto

donde busca,

Posicin inicial de bsqueda).

Busca un valor de texto dentro

de otro (reconoce maysculas y

minsculas).

ESPACIOS(texto) Elimina espacios del texto.

EXTRAE(texto,posicin inicial,

nmero de caracteres)

Devuelve el nmero especfico de

caracteres de una cadena de

texto, comenzando en la posicin

que especifique.

HALLAR(Texto a buscar, texto en

donde busca)

Busca un valor de texto dentro

de otro (no reconoce maysculas

ni minsculas)

IGUAL(texto1,texto2,......) Comprueba si dos valores de

texto son idnticos



Avanzado

IZQUIERDA(texto, nmero de

caracteres)

Extrae los caracteres del extremo

izquierdo de un valor de texto.

LARGO(texto) Devuelve la longitud de una

cadena de texto.

LIMPIAR(texto) Elimina caracteres de control de

texto.

MAYUSC(texto) Convierte el texto en

maysculas.

MINUSC(texto) Convierte el texto en minsculas.

MONEDA(nmero, nmero decimales)

Da formato a un nmero y lo convierte en texto.

NOMPROPIO(texto) Convierte en maysculas la

primera letra de cada palabra de

un valor de texto.

REEMPLAZAR(texto original,nmero

de caracteres dentro del texto.

Reemplaza caracteres dentro del

texto.

REPETIR(texto a repetir, nmero de

veces)

Repite el texto un nmero

determinado de veces.

SUSTITUIR(texto, texto original,

texto nuevo)

Reemplaza caracteres dentro del

texto.

T(valor) Convierte los argumentos en

texto.

TEXTO(valor,formato) Da formato a un nmero y lo

convierte en texto.

VALOR(texto) Convierte un argumento de texto

en un nmero.

PRINCIPALES FUNCIONES DE INFORMACIN

FUNCIN DESCRIPCIN

Celda(tipo_de_info_,ref) Obtiene informacin acerca del

formato, ubicacin o contenido de

la celda superior izquierda en una

referencia.

ESBLANCO(valor) Verifica si una celda est vaca.

ESERR(valor) Verifica si una celda contiene

algn valor de error excepto

#N/A.

ESERROR(valor) Verifica si una celda contiene

algn valor de error de los tipos

#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #?NAME?

#NULL!.



Avanzado

ESLOGICO(valor) Verifica si una celda contiene un

valor lgico.

ESNO(valor) Verifica si una celda contiene un

valor #N/A.

ESNOTEXTO(valor) Devuelve Verdadero si Valor no

es texto.

ESNUMERO(valor) Devuelve Verdadero si Valor es

un nmero.

ESREF(valor) Devuelve Verdadero si Valor es

una referencia.

ESTEXTO(valor) Devuelve Verdadero si Valor es

texto.

INFO(tipo) Devuelve informacin acerca del

entorno operativo en el que se

esta trabajando.

N(valor) Devuelve el valor convertido en

un nmero.

NOD() Devuelve el valor de error #N/A.

TIPO(valor) Devuelve el nmero que indica el

tipo de datos de un valor.

TIPO.DE.ERROR(valor_de_error) Devuelve un nmero que

corresponde a uno de los valores

de error en Excel.

EJEMPLO DEL USO DE FUNCIONES

=SUMAR.SI(PRODUCTO,CAMISAS,IMPORTE) Calcula la suma de importes del producto

camisas.

=SUMAR.SI(PRODUCTO,: CAMISAS,IMPORTE)

Calcula la suma de

importes de todos los

productos excepto

CAMISAS. =SUMAR.SI(CIUDAD,Filtro 1,Unidades) Suma las unidades de la

ciudad que se encuentra

dentro de la celda con el

nombre Filtro 1. =SI(ESNO(COINCIDIR(Filtro 1,CIUDAD,0)),

No existe ciudad) Calcula la cadena de

texto propuesta como

mensaje de error si el

elemento de la celda

con el nombre Filtro 1 no se encuentra en el

rango denominado



Avanzado

Ciudad. =DESREF(A2,3,1) Calcula CAMISAS, tres

renglones abajo y una

columna a la derecha de

la celda A2.

=SI(DIA)AH0RA())=15, Verificar unidades, )

Muestra el mensaje si el

da de hoy es 15.

Frmulas Matriciales

Vamos a explicar primero el concepto de Matriz. Una matriz es un

conjunto de datos organizados en filas y columnas, una hoja Excel

tiene forma de una gran matriz porque est divida en filas y columnas.

Excel trabaja con matrices unidimensionales, es decir de una fila o de

una columna, y matrices bidimensionales formadas por filas y

columnas. Tambin existen matrices tridimensionales que estn

formadas por filas, columnas y profundidad, pero que Excel no

entiende.

Una frmula matricial es una frmula que se aplica a todas las

celdas de una matriz. Las frmulas matriciales permiten un ahorro

de trabajo.

Veamos cmo crear y utilizar las frmulas matriciales. Y vemoslo

como mejor se ven estas cosas, con un ejemplo.

Ejemplo: Vamos a crear este tipo de compra



Avanzado

Vamos a crear una frmula matricial para calcular el precio total de cada producto.

Seleccionamos las celdas donde irn los resultados. Suponemos que la tabla est ubicada

desde la celda A1, as las celdas de los resultados corresponden con las celdas que van

desde D2 hasta D9. Seleccionamos estas celdas.

Y escribimos la frmula =Moneda(B2:B9*C2:C9)



Avanzado

Una vez escrita la frmula pulsamos Ctrl+Shift+Intro para Excel la reconozca como

frmula matricial.



Avanzado

Observa que aparecen los resultados en la columna de Precio Total.

Ahora podemos obtener el resultado de la suma de los totales sin necesidad de crear una

frmula que revise las celdas de los totales parciales.

Sitate en la celda D10 y escribe =Moneda(SUMA(B2:B8*C2:C8))



Avanzado

Pulsa Ctrl+Shift+Intro para que la interprete como frmula matricial. Con esta frmula

podemos observar que no hacemos uso de la columna de totales parciales.

Ahora crearemos una matriz y la guardaremos como constante.

Sitate en otra hoja, en la celda A10, despliega el men Insertar y selecciona Nombre -->

Definir..., dentro del cuadro de dilogo Definir nombre en el campo Nombres en el libro:

escribe Semana.



Avanzado

En el recuadro donde pone Se refiere a: escribe

={"Lunes";"Martes";"Miercoles";"Jueves";"Viernes";"Sabado";"Domingo"}

Es equivalente a una fila y 7 columnas.

Da clic en el botn Aceptar.

Y ya tenemos una constante con los das de la semana.

Cuando queramos hacer uso de esta constante, slo la tenemos que asignar como frmula

matricial al conjunto de celdas correspondientes:

Selecciona las celdas A1:G1, tantas celdas como campos hemos introducido en la matriz.



Avanzado

Escribe el nombre de la constante precedida del signo "=", =Semana y pulsa

Ctrl+Shift+Intro.

Observa como slo aparece el primer da (Lunes) repetido, esto es debido a que estamos

asignando una matriz de una fila y siete columnas a una seleccin que consta de 7 filas y

una columna. La solucin consiste en hacer que los valores que asignamos estn en una

matriz de una columna y 7 filas (utilizando la funcin TRANSPONER).

Selecciona de la A1:G1 y coloca como se muestra a continuacin =Transponer(Semana) +

ctrl+shift+intro y nos mostrar el siguiente resultado

BSQUEDA DE UN VALOR DESEADO

(BUSCAR OBJETIVO)

La manera de acceder a Buscar Objetivo es dar clic en la pestaa de

Datos Anlisis Buscar Objetivo como lo muestra la

siguiente imagen:



Avanzado

Buscar objetivo vara el valor de una celda especfica hasta que una

frmula dependiente de esta, alcance el valor deseado. Al seleccionar

este comando aparecer el cuadro de dilogo Buscar objetivo con las

opciones siguientes.

Definir la celda. Especifica la celda que contiene la frmula a la

cual se le quiere encontrar la solucin.

Con el valor. Especifica el nuevo valor que se desea alcanzar.

Para cambiar la celda: Especifica la celda con el valor que se

quiere ir cambiando.

Al pulsar en el botn Aceptar, se muestra otro cuadro de dilogo con el

estado de la bsqueda. Sus opciones son:

Aceptar: Reemplaza el valor inicial con el encontrado.



Avanzado

Cancelar: Detiene el clculo, conservando en la celda el valor

inicial.

Paso a paso: Especifica un paso a la vez.

Pausa: Detiene el proceso cada que se incremente el valor inicial.

Continuar: Utilizado para continuar el proceso despus de una

pausa.

Por defecto, la bsqueda de un objetivo se detiene cuando se han

realizado 100 iteraciones o encuentra la respuesta que se acerca con

una precisin de 0.001 al objetivo. Si se requiere una precisin ms

grande, cambiar este valor en la opcin No. Mximo de iteraciones del

men Opciones dentro del botn de Office y en las opciones de clculo.

ESCENARIOS

ADMINISTRADOR DE ESCENARIOS

A travs de esta opcin es posible crear y almacenar escenarios, los

cuales son un conjunto de datos de entrada que producen diferentes

resultados. Se pueden crear, nombrar y guardar mltiples escenarios y

ver los resultados en cada uno dentro de la hoja de trabajo. Tambin se puede crear un reporte que provee un resumen de todos los valores de

entrada y los resultados de los escenarios que se hayan creado.

El administrador de escenarios crea y guarda diferentes conjuntos de

datos como escenarios separados. Puedes usar estos escenarios para

ver varios resultados basndote en diferentes suposiciones. Tambin

puedes crear informes que muestran los valores de celdas cambiantes y

los valores de celdas resultantes para cada escenario.

Puedes usar cualquier celda que tenga datos como una celda cambiante

para un escenario. Si defines o creas nombres para las celdas

cambiantes, stos aparecern en el cuadro de dilogo Agregar escenario.

Para abrir se elige la pestaa de Datos Anlisis y si Administrador de Escenarios como lo muestra la siguiente imagen:



Avanzado

Al dar clic nos mostrara la pantalla siguiente

Escenarios: Muestra los escenarios existentes en la hoja activa.



Avanzado

Celdas cambiantes: Presenta las celdas que contienen los datos

que van a cambiar en cada escenario.

Comentario: Presenta la fecha en que se cre o se edit un

escenario, el nombre del usuario que cre o modific el escenario

y cualquier texto que haya escrito.

Mostrar: Presenta en la hoja de clculo los valores de las celdas

cambiantes que corresponden al escenario seleccionado. La hoja de clculo se vuelve a calcular para reflejar los valores nuevos.

Agregar: Presenta el cuadro de dilogo Agregar escenario donde

puede especificar los valores para cada celda cambiante.

Eliminar: Elimina el escenario seleccionado.

Modificar: Muestra el cuadro de dilogo Modificar escenario donde

se podrn modificar caractersticas del escenario seleccionado.

Combinar: Presenta el cuadro de dilogo Combinar escenarios

para combinar escenarios de cualquier hoja de cualquier libro de trabajo abierto.

Resumen: Presenta el cuadro de dilogo Resumen del escenario el

cual permite crear informes de resumen o de tablas dinmicas.

Cerrar: Cierra el cuadro de dilogo.

Para crear un escenario hay que seguir los siguientes pasos:

Teniendo la hoja de clculo activa, elige la pestaa de Datos

Anlisis y si Administrador de Escenarios. Se abrir el cuadro de dilogo Administrador de Escenarios como se observ

anteriormente.

Elija el botn Agregar. Se mostrar el cuadro de dilogo Agregar

Escenario.



Avanzado

Teclea el Nombre del escenario (Hasta 255 caracteres).

En la caja Celdas cambiantes, teclea separados por comas, los

nombres de rangos de las celdas que sufrirn cambios.

En Comentarios escribe un texto que explique el objetivo del escenario.

En Proteccin podrs activar para impedir que otras personas

modifiquen el escenario. Si eliges Evitar cambios evitars que se

realicen cambios. Si eliges Ocultar evitars que el nombre del

escenario aparezca en el cuadro de dilogo Administrador de

escenarios.

Pulsa en el botn Aceptar. Aparecer el cuadro de dilogo Valor de

Escenario donde debers introducir los valores para las celdas

cambiantes. Una vez que determines el nmero de valores para

las celdas cambiantes da clic en Aceptar.



Avanzado

Al regresar al cuadro de dilogo Administrador de Escenarios elige el

botn Cerrar. Podrs agregar hasta 32 escenarios por hoja.

Para mostrar el escenario:

Para abrir se elige la pestaa de Datos Anlisis y si Administrador de Escenarios.



Avanzado

Seleccione el Nombre del escenario de la lista.

Escoje el botn Mostrar, vers en pantalla los datos variables en

las celdas correspondientes.

COMBINAR ESCENARIOS

Si t libro de trabajo tiene varias hojas, podrs tener en cada hoja definidos sus escenarios. A travs de la combinacin de escenarios

pueden tomar escenarios definidos en una hoja y aplicarlo a otra del

libro o inclusive de otro libro. Todas las celdas declaradas en los

escenarios como cambiantes debern figurar en la hoja donde quieras

utilizar el escenario. Si existen despus de la combinacin de nombres

de escenarios duplicados, Excel los diferenciar agregndole la fecha a

alguno de ellos y si tienen la misma fecha, se agregar un nmero.

Para combinar escenarios:

Estando en la hoja donde incluirs los escenarios. Abre todos los

libros de trabajo que contengan los escenarios que desees combinar.

Se elige la pestaa de Datos Anlisis y si Administrador de

Escenarios.

Elige el botn Combinar. Se abrir el cuadro de dilogo Combinar

Escenarios.



Avanzado

En el cuadro Libro, selecciona el nombre del libro de trabajo.

En el cuadro Hoja, selecciona el nombre de la hoja que contiene

los escenarios que deseas combinar.

Pulsa en el botn Aceptar. Vers el cuadro Administrador de

escenarios mostrando todos los escenarios incluidos o

combinados.

RESUMEN DE ESCENARIOS

Una vez definidos los diferentes escenarios, ser posible presentarlos en

una hoja en forma de resumen, mostrando los valores que contendrn

las celdas con frmulas que hacen referencia a las celdas cambiantes en

los escenarios. El resumen se insertar en una hoja aparte del libro.

Conviene nombrar las celdas resultantes as como las cambiantes (Insertar Nombre Definir) para mejorar la presentacin del informe. Para crear un resumen de escenarios:

Escoge la pestaa de Datos Anlisis y si Administrador de

Escenarios.

Se abrir el cuadro de dilogo Administrador de Escenarios.



Avanzado

Elige el botn Resumen. Se ver el cuadro Resumen de escenario.

En Tipo de informe elige el botn Resumen.

En Celdas resultantes, sombrea las celdas que varan al aplicar los

diferentes escenarios.

Pulsa el botn Aceptar.



Avanzado

Tablas dinmicas.

Crear una tabla dinmica

Una tabla dinmica consiste en el resumen de un conjunto de datos, atendiendo a

varios criterios de agrupacin, representado como una tabla de doble entrada que

nos facilita la interpretacin de dichos datos. Es dinmica porque nos permite ir

obteniendo diferentes totales, filtrando datos, cambiando la presentacin de los

datos, visualizando o no los datos origen, etc.

Supongamos que tenemos una tabla de datos que contiene mucha informacin

sobre las ventas de la compaa entre las cuales se encuentra una columna con

los productos de la empresa, tambin la ciudad donde se vende y las ventas

correspondientes para cada ciudad.

Te han solicitado un reporte con el total de ventas por ciudad y el total de ventas

por producto. As que lo que necesitas hacer es sumar las ventas para cada

ciudad y sumar las ventas de cada producto para obtener el reporte. En lugar de

utilizar frmulas podemos utilizar una tabla dinmica para obtener el resultado

deseado. Una tabla dinmica nos permite hacer una comparacin entre diferentes

columnas de una tabla. Puedes imaginarte una tabla dinmica de la siguiente

manera:



Avanzado

Lo primero que debemos hacer es especificar los campos de nuestra tabla de

datos que vamos a comparar. Elegimos las ciudades como las filas de nuestra

tabla dinmica:

Excel tomar todos los valores de ciudades de nuestra tabla de datos y los

agrupar en la tabla dinmica, es decir, colocar los valores nicos de la columna

de datos eliminando las repeticiones. Ahora hacemos lo mismo para especificar

las columnas de la tabla dinmica.



Avanzado

Finalmente elegimos una columna de valores numricos que sern calculados y

resumidos en la tabla dinmica:

As tendremos un reporte que compara las ciudades y los productos y para cada

combinacin obtendremos el total de ventas.

Lo ms importante es que las tablas dinmicas te permiten elegir entre todas las

columnas de una tabla de datos y hacer comparaciones entre ellas para poder

realizar un buen anlisis de la informacin.



Avanzado



Avanzado

Ahora hagamos un ejemplo:

Supongamos que tenemos una coleccin de datos de los artculos del almacn

con el nmero de referencia y el mes de compra, adems sabemos la cantidad

comprada y el importe del mismo.

Mes Articulos Vendedor Ventas Sucrusal

Enero Lcteos Juan $ 123.00 Lindavista

Enero Salchichonera Jose $ 164.00

Divisin del

Norte

Enero Abarrotes Carlos $ 125.00 Centro

Enero Refresco Antonio $ 147.00 San Joaquin

Enero Dulcera Oscar $ 123.00 Azcapozalco

Febrero Lcteos Juan $ 154.00 Lindavista

Febrero Salchichonera Jose $ 148.00

Divisin del

Norte

Febrero Abarrotes Carlos $ 126.00 Centro

Febrero Refresco Antonio $ 125.00 San Joaquin

Febrero Dulcera Oscar $ 145.00 Azcapozalco

Febrero Lcteos Juan $ 145.00 Lindavista

Marzo Salchichonera Jose $ 147.00

Divisin del

Norte

Marzo Abarrotes Carlos $ 149.00 Centro

Marzo Refresco Antonio $ 159.00 San Joaquin

Marzo Dulcera Oscar $ 148.00 Azcapozalco

Abril Lcteos Juan $ 126.00 Lindavista

Abril Salchichonera Jose $ 148.00

Divisin del

Norte

Vamos a crear una tabla dinmica a partir de estos datos para poder examinar

mejor las ventas de cada artculo en cada mes.



Avanzado

Para ello vamos a dar clic a la pestaa Insertar y hacemos clic en Tabla

dinmica (tambin podemos desplegar el men haciendo clic en la flecha al pie

del botn para crear un grfico dinmico).

Aparece el cuadro de dilogo de creacin de tablas dinmicas. Desde aqu

podremos indicar el lugar donde se encuentran los datos que queremos analizar

y el lugar donde queremos ubicarla.

En nuestro caso indicamos que vamos a seleccionar los datos de un rango de

celdas y que queremos ubicarla en una hoja de clculo nueva.



Avanzado

Podramos crear una conexin con otra aplicacin para obtener los datos

desde otra fuente diferente a Excel.

En el caso de seleccionar la opcin Selecciona una tabla o rango debemos

seleccionar todas las celdas que vayan a participar en nuestro caso todas las

del ejemplo del almacn como lo muestra la imagen , incluyendo las

cabeceras.



Avanzado

Damos Aceptar para seguir.

Se abrir un nuevo panel en la derecha de la pantalla:



Avanzado

Desde este panel podemos personalizar la forma en que van a verse los datos

en la tabla dinmica.

Con esta herramienta podramos construir una tabla dinmica con la siguiente

estructura:

Partes de una tabla dinmica

Justo cuando se ha creado una tabla dinmica se muestra en la parte derecha de la hoja la

lista de campos disponibles y por debajo las reas donde podemos arrastrar dichos campos.

Estas reas denotan cada una de las partes de una tabla dinmica.

Filtro de informe. Los campos que coloques en esta rea crearn filtros para la

tabla dinmica a travs de los cuales podrs restringir la informacin que vez en

pantalla. Estos filtros son adicionales a los que se pueden hacer entre las columnas y

filas especificadas.

Etiquetas de columna. Esta rea contiene los campos que se mostrarn como

columnas de la tabla dinmica.

Etiquetas de fila. Contiene los campos que determinan las filas de la tabla

dinmica.

Valores. Son los campos que se colocarn como las celdas de la tabla dinmica y que sern totalizados para cada columna y fila.

Una vez especificados los campos para cada una de las reas la tabla dinmica cobra vida.

Puedes tener una tabla dinmica funcional con tan solo especificar las columnas, filas y

valores. Los filtros son una herramienta para mejorar el anlisis sobre los datos de la tabla

dinmica.



Avanzado

Para nuestro ejemplo:

- Una fila para cada una de las Referencias de la tabla en

nuestro caso los artculos del almacn.

- Una columna para cada uno de los Meses de la tabla en

nuestro caso el mes.

- En el resto de la tabla incluiremos el total del Importe para

cada Referencia en cada Mes en nuestro caso seria las ventas.

Para ello simplemente tendremos que arrastrar los elementos que vemos

listados a su lugar correspondiente al pie del panel.



Avanzado

En este ejemplo deberamos arrastrar el campo artculos a , el

campo MES a y finalmente el campo ventas a la

seccin como se muestra en la siguiente imagen.

Tras realizar la tabla dinmica este sera el resultado obtenido.



Avanzado

Podemos ver que la estructura es la que hemos definido anteriormente, en los

rtulos de fila tenemos las referencias, en los rtulos de columnas tenemos los

meses y en el centro de la tabla las sumas de los importes.

Con esta estructura es mucho ms fcil analizar los resultados.

Una vez creada la tabla dinmica nos aparece la pestaa Opciones:

El panel lateral seguir pudindose utilizar, as que en cualquier momento podremos

quitar un campo de un zona arrastr

manual de excel avanzado 2012

Documents