Preparado por Profesor: Rodrigo Araya A

INDICE CAPITULO 1 : GENERALIDADES Introduccin Generalidades de Excel o Conceptos de Rango (Fila, Columna , Celda) o Operadores Aritmticos o Referencias Bsicas CAPITULO 2 : FORMULAS Y FUNCIONES ELEMENTALES Manejo de Formulas Simples Manejo de Funciones Bsicas Manejo de Funciones de Cadena Manejo de Funciones de Fecha CAPITULO 3 : FUNCION CONDICIONAL Concepto y Estructura de una Condicin Operadores de Comparacin Funcin Condicional SI o Condicionalidad Simple o Condicionalidad Anidada o Conectivos Lgicos o Condicionalidad Compuesta Uso y Aplicacin de la Funcin Condicional con las funciones elementales CAPITULO 4 : FUNCIONES DE BUSQUEDA Y REFERENCIA VALIDACIN DE DATOS Concepto de tabla (Base de Datos) Funcin BUSCARV , BUSCARH o Bsqueda Directa o Bsqueda Indirecta Uso y Aplicacin de las funciones de bsqueda CAPITULO 5 : GRAFICOS Uso del Asistente para Grficos en Excel Tipos de Grficos Formatos reas de Grficos Modificacin de Grficos

CAPITULO 6 : BASES DE DATOS Y TABLAS DINAMICAS Bases de datos generalidades Filtros y Filtros Avanzados Uso del Asistente para Tablas Dinmicas Funcionalidad de las Tablas Dinmicas Propiedades de las Tablas Dinmicas Consolidaciones, Subtotales y Agrupamiento APENDICE 1 Jerarqua de Errores en Excel

Preparado por Profesor: Rodrigo Araya A

GENERALIDADES CAPITULO 1 Microsoft Excel es un software que permite desarrollar aplicaciones computacionales, llamadas planillas electrnicas, que involucran clculo, anlisis y gestin de datos.
ACCESO Y PRESENTACIN DE LA PLANILLA

Para ingresar a La planilla electrnica Excel debe: Haga un clic en INICIO (se desplegar un men ) Haga un clic en la opcin PROGRAMAS (se desplegar otro men) Elija con el mouse la opcin MICROSOFT EXCEL Se desplegar la ventana de la aplicacin

Men principal

Botones de opciones

Barra de frmula

Selector de columna

rea de trabajo

Selector de fila

Celda

hojas de trabajo

columna

Una celda est conformada por la interseccin de una columna y una fila La referencia a una celda es indicando primero la columna y luego la fila (en la figura anterior esta marcada la celda E12). En la barra de frmula Ud. Ver en que celda est situado y el contenido de ella. (en la figura el lado derecho del signo igual est en blanco, esto indica que la celda E12 est vaca). Cada celda tiene una capacidad de 256 caracteres (aunque visualmente sea de 10 caracteres). Ud. Deber recordar que si imprime una planilla lo que obtendr es lo que se ve en pantalla. Para desplazarse por las celdas debe utilizar el mouse (con un clic sobre la celda) o puede utilizar las teclas de flechas direccionales.

Preparado por Profesor: Rodrigo Araya A

SELECCIN DE CELDAS Y RANGO DE CELDAS

Para seleccionar una celda basta hacer un clic sobre ella. Para seleccionar un rango de celdas haga un clic sobre una de ellas y sin soltar el botn del mouse arrastre hasta la ltima celda que requiera (Excel mostrar en negro las celdas marcadas a excepcin de la primera celda).

INGRESO DE DATOS A LA PLANILLA

Excel reconoce los siguientes tipos de datos: o Numricos. (se alinean por defecto de izquierda a derecha). o Alfanumricos o textos (Se alinean de derecha a izquierda). o Frmulas y funciones. Para ingresar un dato a una celda: o Seleccionar una celda o Digitar el dato o Presionar la tecla ENTER

MODIFICACIN Y ELIMINACIN DE DATOS

Para modificar un dato tiene las siguientes posibilidades: o Seleccionar la celda y ubicar, con un clic del mouse, el cursor en la barra de herramientas. Realizar los cambios, luego presionar tecla ENTER o Seleccionar la celda, presionar la tecla F4, hacer los cambios y presionar la tecla ENTER. Para eliminar el contenido de una celda o rango de celdas : o Seleccione la o las celdas y presione la tecla SUPR

FORMATOS DE CELDAS Y DE DATOS

Se entiende por formato al aspecto visual de las celdas y datos contenidos en ella. Para dar un formato debe: Seleccionar la celda o rango de celdas. Aplicar el formato deseado FORMATOS GENERALES
Tipo de fuente tamao de fuente negrita cursiva subrayado alineacin

Bordes

Color de relleno color de fuente

Preparado por Profesor: Rodrigo Araya A

FORMATOS DE DATOS Seleccionar del men principal la opcin FORMATO (se desplegar un submen). Seleccionar la opcin CELDAS, se desplegar la siguiente ventana:

En la hoja Nmero Ud. Tiene las distintas categoras de formatos para datos Ud. Selecciona la categora y el sistema desplegar las opciones para esa categora. Cuando Ud. No encuentre el estilo deseado elija en categoras la opcin PERSONALIZADA. La hoja alineacin permite modificar la alineacin de los datos La hoja Fuente permite modificar el tipo, tamao, color y estilo de la fuente La hoja Bordes permite personalizar los contornos de las celdas. La hoja proteger se analizar mas adelante.

OBSERVACION: Para eliminar una o mas celdas adems de sus formatos debe seleccionarlas. Del men principal debe elegir la opcin EDICION, luego, del submen desplegado elegir la opcin BORRAR y luego del submen desplegado la opcin TODO.

Preparado por Profesor: Rodrigo Araya A

EJERCICIO #1 Ingresar la siguiente planilla:
Ttulo de la planilla Encabezados de columnas

Lneas de datos

Aplicar los 1. 2. 3. 4. 5.

siguientes formatos a la planilla: El ttulo debe quedar con letra tipo Arial, tamao 14, color rojo Desde la celda A3 a la celda E8 deben quedar con bordes color azul Centre el rango desde A3 a A9 Aplique formato monetario a los precios (signo $) Aplique formato de fecha dd- nombre del mes - ao con 2 cifras

GUARDAR, RECUPERAR Y CERRAR UNA PLANILLA

Para guardar una planilla debe seleccionar del men principal la opcin ARCHIVO, se desplegar un submen. Seleccione la opcin GUARDAR. Se desplegar una ventana en la cual Ud. Debe indicar: 1. Donde la guardar (Guardar en). Seleccione la ubicacin. 2. Con que nombre la guardar (Nombre de archivo:). Digite el nombre por Ud. Elegido. OBSERVACION: Si realiza cambios a su planilla, para guardarlos slo debe hacer un clic en el botn GUARDAR (el icono es un diskette).

Para recuperar una planilla debe seleccionar del men principal la opcin ARCHIVO y luego la opcin ABRIR. Se desplegar una ventana. 1. Seleccione la ubicacin del archivo (Buscar en) 2. En el rea central aparecern los nombres de todas las planillas que estn guardadas. Seleccione con doble clic la deseada por Ud. Para cerrar la planilla Elija del men la opcin ARCHIVO y luego la opcin CERRAR OBSERVACION: Practique estas ltimas operaciones para mecanizarlas, sobre todo para guardar en su disco de trabajo (disco 31/2 A:). Recuerde que las pruebas debern quedar en su disco. Si no se preocupa de guardarlas en el tendr calificacin 1,0

Preparado por Profesor: Rodrigo Araya A

COPIAR Y DESPLAZAR CELDAS

Para copiar una celda o un rango de ellas debe: o Seleccionar la celda o el rango o Hacer un clic en el botn COPIAR o Seleccionar la celda destino. o Hacer un clic en el botn PEGAR Para desplazar una celda o un rango de ellas debe: o Seleccionar la celda o el rango o Hacer un clic en el botn CORTAR o Seleccionar la celda destino o Hacer un clic en el botn PEGAR

Cortar Copiar

Pegar

INSERTAR Y ELIMINAR FILAS Y/O COLUMNAS

Para insertar una fila y o columna en la planilla debe: o En el selector de fila o columna seleccione la fila(s) o columna(s) o En el men principal elija la opcin FILA o COLUMNA.

Observacin: o Se inserta(n) fila(s) sobre la(s) seleccionadas. o Se inserta(n) columna(s) a la derecha de la(s) seleccionadas. EJERCICIO #2 En la planilla anterior realice las siguientes actividades: 1. 2. 3. 4. Gurdela en su disco de trabajo con el nombre MIPRIMER EJERCICIO DE EXCEL. Inserte dos filas entre los encabezados de columna y la primera lnea de datos. Inserte una columna entre la columna UNIDADES y la columna PRECIO. Guarde los cambios y cierre la planilla.

Preparado por Profesor: Rodrigo Araya A

FRMULAS EN EXCEL CAPITULO 2 Una frmula es una expresin aritmtica que combina valores, celdas numricas y operadores de relacin. OPERADORES DE RELACIN: + (suma) - (resta) ^ (Elevado a ) TIPOS DE FRMULAS a) Frmulas Relativas : Son aquellas frmulas que toman el valor en relacin a la posicin donde se encuentra el curso.b) Frmulas Absolutas : Son aquellas frmulas en las cuales existen un valor constante, que no cambia, para ello excel identifica la constante anteponiendo el signo $ como prefijo a la columna, a la fila o a ambas, ya que los valores constantes se pueden encontrar a nivel de slo la fila, de la columnas o tanto de la fila como la columna. Ejemplo : fila SINTAXIS =frmula (Argumento sin espacios ) INGRESO DE UNA FORMULA Seleccionar una celda Digitar la frmula y presionar tecla ENTER B2*$C$250, donde en C250 hay un valor que no cambia tanto a nivel de columna como de * (multiplicacin) / (divisin) % (porcentaje)

EJEMPLOS DE FORMULAS =X8*4 =(F3+G9+P6)/3 =H10*27% Multiplicar el contenido de la celda X8 por 4 Calcular el promedio entre los valores almacenados en las celdas F3,G9 y P6 Calcular el 27% del valor almacenado en la celda H10 Multiplica el valor UF que se encuentra en F400 por el precio y se lo suma al precio =(($F$400)*B2)+C2 final

Preparado por Profesor: Rodrigo Araya A

EJERCICIO #1 Crear la siguiente planilla:

a) Completar las columnas MONTO, IVA y UTILIDAD, considerando que: MONTO = Unidades multiplicado por precio IVA = 18% del monto UTILIDAD = monto -iva - 13% del monto b) Complete los totales, considerando que: MONTO TOTAL DE VENTA = suma de los montos TOTAL IVA = suma de los IVAS UTILIDAD TOTAL = suma de las utilidades OBSERVACION: Si observa se dar cuenta que al ingresar una frmula, en la planilla Ud. Visualiza el resultado pero en la barra de frmula aparece la frmula ingresada. Esto permite que se produzca el reclculo automtico.
COPIAR UNA FORMULA

Ingresar la frmula Ubicarse en la celda que tiene la frmula y ubicar se con el mouse sobre el vrtice inferior derecho de la celda (aparecer una cruz de color negro). Haga un clic y sin soltar el botn arrastre hasta cubrir las celdas donde desee que quede copiada la frmula. Lo anterior es vlido si las celdas son en un rango continuo. De otra forma debe realizar la copia con los botones de COPIAR y PEGAR.

POSICIONES RELATIVAS DE CELDAS

Cuando Ud. Copie una frmula se dar cuenta que la estructura de la frmula se mantiene, pero las celdas involucradas son de acuerdo a la ubicacin en la planilla. Esto se denomina posicin relativa.
RECALCULO AUTOMATICO

Cuando una celda cambia su contenido las frmulas que involucran dicha celda modifican y adecuan al nuevo valor (comprubelo cambiando en el ejercicio anterior las unidades de cualquiera de los productos. Para relaizar un reclculo dentro de toda la planilla debe utilizar la tecla F9

Preparado por Profesor: Rodrigo Araya A

FUNCIONES EN EXCEL

Corresponden a operaciones que estn previamente implementadas. Para utilizarlas se debe hacer referencia a la funcin e indicar los parmetros requeridos por ella ESTRUCTURA DE UNA FUNCION =Nombre de la funcin(Argumentos) FUNCIONES MAS UTILIZADAS CATEGORIA FUNCION =ABS(Referencia) DESCRIPCION Permite dejar en valor absoluto el contenido de una celda, formula o funcin.-

MATEMTICAS =REDONDEAR(Referencia;n de decimales) Aproxima el contenido de la celda al n de decimales indicado =REDONDEAR.MAS(Referencia;n de Aproxima el contenido de la celda al n de decimales) decimales hacia arribas a partir de cero Aproxima el contenido de la celda al n de =REDONDEAR.MENOS(Referencia;n de decimales hacia abajo. decimales) Quita los decimales al valor almacenado en la =TRUNCAR(Referencia;nde decimales) celda. No aproxima. Entrega un nmero entre 0 y 1. Cambia al volver a calcular.

=ALEATORIO( )

CATEGORIA

FUNCION =SUMA(rango de celdas) =PROMEDIO(rango de celdas)

DESCRIPCION Suma todos los contenidos de las celdas indicadas en el rango Calcula el promedio de todas las celdas comprendidas en el rango indicado Permite contar celdas ocupadas Entrega el menor valor del rango de celdas indicado Entrega el mayor valor del rango de celdas indicado. Permite calcular el producto entre celdas Permite calcular la suma de los productos de celdas, funcin muy til para realizar promedios ponderados Permite contar celdas que cumplen determinado

=CONTARA(rango de celdas) =MIN(rango de celdas)

=MAX(rango de celdas) ESTADISTICAS =PRODUCTO(Serie1, Serien)

=SUMAPRODUCTO(Rango1;Rangon) =CONTAR.SI(rango;Criterio)

Preparado por Profesor: Rodrigo Araya A

criterio o condicin =SUMAR.SI(R.Criterio ; criterio ; Rango suma) Suma las celdas que cumplen determinado criterio o condicin

=MAYUSC(Ref) =MINUSC(Ref) =NOMPROPIO(Ref) =IZQUIERDA(celda;nde caracteres)

Permite dejar en Mayscula una referencia de celda Permite dejar en minscula una referencia de celda Permite dejar cada carcter de una frase en mayscula y el resto en minscula. Entrega los primeros n caracteres de un texto

=DERECHA(celda;n de caracteres) Entrega los ltimos n caracteres de un texto =EXTRAE(celda;posicin;n de caracteres) DE TEXTO AVANZADAS =CONCATENAR(celda1;celda2;...) =ENCONTRAR(Texto;Referencia) Entrega un subconjunto de caracteres, los cuales pueden ser intermedios en el texto. Permite unir textos almacenados en celdas Entrega el nmero offset correspondiente a la posicin fsica del texto y/o carcter buscado dentro de una celda y/o referencia, desde la izquierda de la celda Entrega el largo de caracteres que tiene una cadena de texto Entrega el contenido de una direccin de celda Permite dejar una expresin de valor en el un valor numrico Elimina los espacios sobrantes de una cadena de texto. Permite convertir una expresin numrica en un valor de texto, de acuerdo a un determinado formato Devuelve la posicin relativa de un elemento en una matriz, que coincide con un valor dado en un orden especificado. Coincidencia 0 indica que sea igual al elemento buscado, 1, el mayor que se asemeje, -1 el menor que se asemeje Devuelve un valor o la referencia a un valor en una tabla o rango Permite generar carctares de acuerdoa ala tabla ASCII, siendo el N60 l letra A y el 90 la Z Permite accesar informacin de la celda de acuerdo al tipo de dato, el cual puede ser, Fomato, Contenido, Fila, Columna, Prefijo, Tipo

=LARGO(Referencia) =INDIRECTO(Referencia) =VALOR(Referencia) =ESPACIOS(Referencia) =TEXTO(Referencia;Formato)

=COINCIDIR(Valor buscado;Matriz Buscada ; Tipo coincidencia)

=INDICE(Ref;N Fila;N Columna)

=CARCTER(Argumento)

=CELDA(Tipo Dato;Referencia)

Preparado por Profesor: Rodrigo Araya A

=ESNUMERO(Referencia)

Permite verificar si una expresin es de tipo numrica o no, si lo es deja la palabra VERDADERO o el Nmero 1, en caso contrario deja la palabra FALSO o el nmero cero (0) Permite verificar si una expresin es de tipo texto o no, si lo es deja la palabra VERDADERO o el Nmero 1, en caso contrario deja la palabra FALSO o el nmero cero (0) Permite verificar si una expresin es de tipo error #N/A o no, si lo es deja la palabra VERDADERO o el Nmero 1, en caso contrario deja la palabra FALSO o el nmero cero (0) Permite verificar si una expresin es de tipo Error o no, si lo es deja la palabra VERDADERO o el Nmero 1, en caso contrario deja la palabra FALSO o el nmero cero (0) Permite generar cualquier fecha presente, futura o pasada, para ser utilizada dentro de las frmulas Entrega el da de la fecha Entrega el mes de la fecha Entrega el ao de la fecha Entrega la fecha actual Entrega Fecha y Hora actual del Sistema Entrega el nmero del da (entre 1 y 7) a que corresponde la fecha. El 1 corresponde al da Domingo. Calcula el n de das entre las fechas indicadas Permite generar cualquier hora para ser utilizada dentro de una frmula y/o funcin

=ESTEXTO(Referencia)

=ESNOD(Referencia)

=ESERROR(Referencia)

=FECHA(aaaa;mm;dd)

=DIA(fecha) =MES(fecha) =AO(fecha) DE FECHA =HOY() =AHORA() =DIASEM(fecha)

=DIAS360(fecha inicio;fecha trmino) =NSHORA(hh;mm;ss) EJEMPLO DE APLICACIN DE LAS FUNCIONES

Ingresar la siguiente informacin en una planilla nueva y luego realice las operaciones descritas:

Preparado por Profesor: Rodrigo Araya A

CELDA C4 D4 E4 B7 B8 B9 B10 C14 D14 E14 F14 C9 D19 E19 F19 G19 C22

INGRESAR =REDONDEAR(B4;1) =TRUNCAR(B4;1) =ALEATORIO()*B4 =SUMA(B4:B6) =PROMEDIO(B4:B6) =MIN(B4:B6) =MAX(B4:B6) =CONCATENAR(A14;B14) =IZQUIERDA(A14;3) =DERECHA(B14;3) =EXTRAE(B14;3;2) =DIA(A19) =MES(A19) =AO(A19) =DIASEM(A19) =DIAS360(A19;B19) =HOY()

Preparado por Profesor: Rodrigo Araya A

FUNCIN CONDICIONAL
CAPITULO 3 Permite definir procedimientos alternativos dependiendo del grado de verdad o falsedad de una condicin o expresin lgica dada. SINTAXIS: =SI(condicin lgica ; instruccin 1; instruccin 2 ) DESCRIPCION: Si la condicin lgica establecida es verdadera se ejecuta lo indicado como instruccin 1, por el contrario, si la condicin es falsa se ejecuta lo indicado como instruccin 2. OPERADORES DE RELACION: OPERADOR < > <= >= <> EJEMPLO 1: Crear la siguiente planilla en la hoja1: SIGNIFICADO Menor que Mayor que Menor o igual Mayor o igual distinto

Completar las columnas que falten segn el siguiente criterio: a) La columna CONTRATO debe ser llenada con la palabra INDEFINIDO (si el tipo de contrato es I) o PLAZO FIJO (si el tipo de contrato es P) b) La columna REMUNERACION MENSUAL debe ser llenada con el siguiente clculo: Remuneracin anual/12 : Si el tipo de contrato es a plazo fijo Remuneracin anual/11 : Si el tipo de contrato es indefinido c) La columna BONIFICACION debe ser calculada segn: 5% de la remuneracin mensual si el contrato es indefinido $10000 si el contrato es a plazo fijo. d) TOTAL es la suma de la remuneracin mensual mas la bonificacin (nmero entero).

Preparado por Profesor: Rodrigo Araya A

SOLUCION: a) b) c) d)

=SI(C5="I";"INDEFINIDO";"PLAZO FIJO") =SI(C5="P";E5/12;E5/11) =SI(C5="I";F5*5%;10000) =REDONDEAR(F5+G5;0)

FUNCIN CONDICIONAL ANIDADA

Se utiliza cuando se tienen tres o ms alternativas de proceso. SINTAXIS =SI(condicin ; instruccin 1 ; SI(condicin ; instruccin 2 ;SI( condicin ; instruccin 3 ; .....))) EJEMPLO 2: En la hoja2 de su planilla: - Copie la planilla del ejemplo anterior y elimine las funciones que desarroll. - Agregue los empleados que se indican:

a) Complete la columna CONTRATO con las palabras: INDEFINIDO : si el tipo de contrato es I P. FIJO : si el tipo de contrato es P HONORARIOS : Si el tipo de contrato es H b) Complete la columna REMUNERACIN MENSUAL con: Remuneracin anual / 11,5 : Si el contrato es indefinido Remuneracin anual / 12 : Si el contrato es a plazo fijo Remuneracin anual /10 : Si el contrato es a honorarios c) Complete la columna BONIFICACION con: 1,2% de la remuneracin mensual :Si el contrato es indefinido $12000 : Si el contrato es a plazo fijo Los contratos a honorarios no tienen bonificacin y los valores resultantes deben quedar como nmeros enteros. d) La columna TOTAL llnela de acuerdo a: Sueldo mensual mas la bonificacin (los resultados deben ser nmeros enteros) SOLUCION: a) =SI(C5="I";"INDEFINIDO";SI(C5="P";"P. FIJO";"HONORARIOS")) b) =SI(C5="P";E5/12;SI(C5="I";E5/11,5;E5/10)) c) =REDONDEAR(SI(C5="H";0;SI(C5="I";E5*1,2%;12000);0) d) =REDONDEAR(F5+G5;0)

Preparado por Profesor: Rodrigo Araya A

CONDICIONALIDAD COMPUESTA (Conectivos Lgicos)

lgicos Se utiliza cuando se requiere definir procesos alternativos bajo la verdad o falsedad de dos o mas condiciones. CONECTORES Y O Y lgico lgico SINTAXIS

=SI(Y(condicin 1;condicin 2); operacin 1;operacin 2) =SI(O(condicin 1;condicin 2);operacin 1;operacin 2)

EJEMPLO 3: En la hoja3 crear la siguiente planilla:

o o o

Complete la columna BONIFICACION considerando que: Si el tipo de contrato es I y la remuneracin anual es menor a $6000000 la bonificacin es de $15000 Si el tipo de contrato es I y la remuneracin anual es superior o igual a $6000000, la bonificacin es de $10000 Si el tipo de contrato es P la bonificacin es de $5000, los contratos H no tienen bonificacin

SOLUCION: =SI(Y(C5="I";D5<6000000);15000;SI(Y(C5="I";D5>=6000000);10000;SI(C5=P";5000;0)))

Preparado por Profesor: Rodrigo Araya A FUNCIONES DE BUSQUEDA Y REFERENCIA

CAPITULO 4 DAR NOMBRE A UN RANGO 1. quien se dar un nombre. 2. opcin DEFINIR. Se desplegar la siguiente tabla: Menu INSERTAR - opcin NOMBRE Marcar la celda o rango de celdas a

3. Donde est ubicado el cursor se digita el nombre que Ud. desea. En la parte inferior aparece el rango por Ud. seleccionado (se dar cuenta que las celdas estn fijadas) 4. Haga un clic en el botn ACEPTAR. OBSERVACION : La importancia que tiene lo anterior es que esto permite hacer referencia a las celdas (ya sea en una frmula o funcin) por el nombre dado.

ORDENAR UN RANGO DE CELDAS 1. Marcar el rango de celdas a ordenar 2. Men DATOS - opcin ORDENAR. Se desplegar la siguiente ventana:

Se indica la columna principal por la cual ordenar

Para indicar la ordenacin de subgrupos

Para indicar la ordenacin de subgrupos dentro del subgrupo anterior

Preparado por Profesor: Rodrigo Araya A

EJEMPLO: Crear la siguiente planilla, marcarla completamente y ordenarla de la siguiente forma:

OPERACIONES CON HOJAS DE CALCULO Un libro de excel contiene Hojas (Hoja1; hoja2, ......), las cuales aparecen en la parte inferior de la planilla. CAMBIAR EL NOMBRE A UNA HOJA

Hacer doble clic en la hoja y luego digitar el nombre INSERTAR UNA HOJA

Hacer un clic en una hoja, luego sin mover el mouse hacer un clic con el botn derecho (se despliega un men) elegir la opcin INSERTAR ... (se insertar a la derecha de la hoja seleccionada)

ELIMINAR UNA HOJA

Hacer un clic en una hoja, luego sin mover el mouse hacer un clic con el botn derecho (se despliega un men) elegir la opcin ELIMINAR ...

Preparado por Profesor: Rodrigo Araya A

CREACIN DE LISTAS Y VALIDACIN DE DATOS

1. Crear las siguientes planillas en un nuevo libro de Excel, respetando las referencias de las celdas indicadas:

2. Confeccin de Validacin:
o

Luego supondremos que Ud. debe realizar el pedido mensual de materiales para su departamento. Para ello a creado la primera planilla (suponiendo que el pedido no supera los 20 materiales). La planilla Lista de Materiales es una tabla de referencia, la cual contiene el detalle de los materiales que utiliza su departamento. El problema consiste en pensar que el usuario deber elegir en cada lnea de la primera planilla un material (columna A) e ingresar la cantidad (el resto deber completarse automticamente).

Proceso: 1. Ordenar los datos de la lista de materiales: - Marcar los datos (rango N4:P14). - Men DATOS opcin ORDENAR (en la ventana desplegada indicar como primer criterio la columna nombre). 2. Dar un nombre al rango de datos: - Marcar los datos (rango N4:P14). - Men INSERTAR opcin NOMBRE opcin DEFINIR ( en la ventana desplegada, en el cuadro superior, digitar el nombre. En nuestro caso digita MATERIAL).

Preparado por Profesor: Rodrigo Araya A 3. Crear la lista de validacin: - Ubica el cursor en la celda A6. - Men DATOS - opcin VALIDACIN. Se desplegar la siguiente ventana:
En el cuadro PERMITIR elija la opcin LISTA.

En el cuadro ORIGEN marcar el rango de la tabla MATERIAL correspondiente a la columna N4: N24

Al elegir la opcin LISTA se despliega el cuadro. Ubique el cursor en el cuadro (con un clic) y luego marque el rango de la lista de materiales correspondiente a los nombres de ellos (N4:N14). Haga un clic en botn ACEPTAR. Si Ud. hace un clic en la punta de flecha notar que se despliega la lista de materiales.

Ud. notar que la planilla de pedidos queda:

Preparado por Profesor : Rodrigo Araya A . Copie la celda A6 hasta la celda A24. 3. Obtencin del precio y el tipo: Ahora, si el usuario elige un material de la lista debe automticamente obtener el precio y el tipo de material. o Ubique el cursor en la celda B6 y digite la formula =buscarv(A6;MATERIAL;2;0) Esto permite obtener el precio de la tabla MATERIAL. Copie la celda hasta la celda B24.
o

Ubique el cursor en la celda C6 y digite la siguiente formula =buscarv(A6;MATERIAL;3;0) Esto permite obtener el tipo de la tabla MATERIAL. Copie la celda hasta la celda C24.

4. Generacin de montos y monto total del pedido: Ubique el cursor en la celda E6 y digitar =B6*D6 . Copie la celda hasta la celda E24.

Ubique el cursor en la celda E25 y digite =suma(E:6:E24). 5. Optimizacin de la planilla de pedidos: Si observas, al estar vaca la celda A6 en las restantes de la misma fila aparece #N/A , esto significa que no se encuentran esos datos.

Lo ideal sera: - que si el usuario no elige un material (celda A6 est en blanco) las otras celdas de la fila permanezcan en blanco. - El monto se calculara slo si el usuario eligi un material y adems ingres una cantidad. Proceso: Haga doble clic en la celda B6 y digite: =SI(A6=;;BUSCARV(A6;MATERIAL;2)) Haga doble clic en la celda C6 y digite: =SI(A6=;;BUSCARV(A6;MATERIAL;3)) Haga doble clic en la celda E6 y digite: =SI(o(B6=;D6=);;B6*D6) Copie hasta la fila 24 los anterior.

Preparado por Profesor : Rodrigo Araya A . PROBLEMA: Qu pasara si a Ud. le acotaran la cantidad a pedir de cada material? (supongamos que debe pedir al menos 1 unidad y un mximo de 10 unidades). Proceso: Esto se logra tambin con validacin. Ubique el cursor en la celdaD6. Men DATOS opcin VALIDACIN. Se despliega la ventana.

Elija en el cuadro PERMITIR la opcin NMERO ENTERO

Se activan las siguientes opciones:

Digite 1 en este cuadro

Digite 10 en este cuadro

Ahora haga un clic en la pestaa MENSAJE DE ERROR y en el cuadro Mensaje de error digite cantidad no permitida.

Preparado por Profesor : Rodrigo Araya A . Haga un clic en botn ACEPTAR. Ahora, borra las cantidades que hayas digitado en la planilla de pedidos y digita, por ejemplo, en la celda D6 el valor 12. Te dars cuenta que se despliega el cuadro:

Preparado por Profesor : Rodrigo Araya A .

FUNCIONES DE BSQUEDA
Es comn en una aplicacin de Excel tener una planilla principal y tablas de referencia de datos, en base a los cuales es necesario completar dicha planilla principal con datos obtenidos de las tablas.

Funcin BUSCARV
Busca un valor especfico en la columna ms a la izquierda de una tabla y devuelve el valor en la misma fila de una columna especificada en la tabla. SINTAXIS =BUSCARV(valor buscado;tabla de comparacin;indicador columna;0)

donde:
Valor buscado es el valor que se busca en la primera columna de la matriz. Valor_buscado puede ser un valor, una referencia o una cadena de texto. Tabla de comparacin es el conjunto de informacin donde se buscan los datos. Para hacer referencia a la tabla se sugiere; por comodidad, que le de un nombre al rango que ocupan las filas de datos (sin incluir los encabezados). Indicador columna es el nmero de columna donde est el dato a obtener (independiente donde se ubique la tabla, cada columna que la compone se enumeran 1, 2, 3, ... de izquierda a derecha. Falso o Cero, se utiliza cuando la tabla no se encuentra ordenada fsicamente.

La tabla de comparacin debe estar ordenada por la primera columna, ya sea OBSERVACION en forma fsica o en forma lgica, para ello en la funcin Buscar debe poner un cero despus de indicar la columna y/o fila de del ltimo componente de la funcin
EJEMPLO : 1. 2. 3. Crear una nueva planilla llamada EJEMPLO DE BUSCARV Cambiar el nombre de la hoja1 por DETALLE DE COMPRAS Cambiar el nombre de la hoja2 por TABLAS En la hoja DETALLE DE COMPRAS ingresar la siguiente planilla:

Preparado por Profesor : Rodrigo Araya A .

4. En la hoja TABLAS ingresar :

5. Ordenar los rangos A4:C7 (por CODIGO) y E4:F18 (por FECHA) 6. Dar los siguientes nombres a los rangos: A4:C7 PRODUC ; E4:F18 DOL

7. En la hoja DETALLE COMPRAS, en las celdas indicadas, ingresar: CELDA B5 INGRESAR =BUSCARV(A5;PRODUC;2;0)

C5

=BUSCARV(A5;PRODUC;3;0)

F5

=BUSCARV(E5;DOL;2;0)

G5

=C5*D5*F5

Preparado por Profesor : Rodrigo Araya A .

GRFICOS EN EXCEL CAPITULO 5 EJEMPLO : Ingrese la siguiente planilla:

Calcule TOTAL (es la suma de las unidades) CREACION DE UN GRAFICO 1. Haga un clic en el botn ASISTENTE PARA GRFICOS 2. Se desplegar la siguiente ventana:

Elija el estilo de grfico que desee y luego haga un clic en el botn

Preparado por Profesor : Rodrigo Araya A .

3. Se desplegar la siguiente ventana:

En esta ventana debe indicar el rango de datos que desea graficar. Para su comodidad haga un clic en (se minimizar la ventana y podr marcar el rango a graficar. En nuestro ejemplo marque el rango A3:D7). Para volver a la ventana vuelva a hacer un clic en el botn. Y presione el botn siguiente. 4. Se desplegar la siguiente ventana:

Esta ventana tiene varias hojas.

Preparado por Profesor : Rodrigo Araya A .

En la hoja TITULOS indicar: Ttulo del grfico Eje de categoras (X) Eje de valores(Y) En la hoja EJES En la hoja LINEAS DE DIVISION En la hoja LEYENDA En la hoja ROTULOS DE DATOS En la hoja TABLA DE DATOS Haga un clic en el botn siguiente y 5. Se desplegar la siguiente ventana:

: Para nuestro ejemplo : "VENTAS PRIMER TRIMESTRE" : Para nuestro ejemplo : "PRODUCTO" : Para nuestro ejemplo: "UNIDADES" : se activan o desactivan los rtulos para los ejes : se activan o desactivan las lneas de gua del grfico : se activa/desactiva y ubica la leyenda del grfico : se activa /desactiva la visualizacin de los valores o rtulos para los elementos del grfico : se puede asociar la tabla de datos al grfico

En esta ventana Ud. indica donde quedar el grfico. - En una hoja nueva : Queda el grfico en una hoja aparte de la hoja de datos. - Como objeto en : queda el grfico en la hoja de datos Haga un clic en el botn

Preparado por Profesor : Rodrigo Araya A .

EDICION DEL GRAFICO Al hacer un clic en el grfico este se activa (modo edicin). Al estar en este modo se puede aplicar formatos a los distintos elementos del grfico. Haga un clic en cualquier objeto del grfico (titulo, las barras, los ejes, etc.) y sin mover el mouse haga un clic con el botn derecho de el. Se desplegar el men contextual. Elija la opcin que diga FORMATO DE .... y especifique lo requerido por Ud. : Para marcar rangos de datos discontinuos mantenga presionada la tecla Ctrl marque los rangos deseados EJERCICIOS En base a la planilla anterior: 1. Realice un grfico de las ventas de ENERO A MAYO. El grfico debe ser de COLUMNAS 3D y deber quedar en una hoja aparte. El fondo del grfico debe ser con degradacin. La hoja deber llamarse GRAFICO VENTAS ENERO A MAYO. 2. Realice un grfico de las ventas de ENERO, MARZO Y JUNIO. 3. Realice un grfico con el total unidades vendidas. El grfico deber ser de estilo CONICO, con colores personalizados.

OBSERVACION y

Preparado por Profesor : Rodrigo Araya A .

BASES DE DATOS, FILTROS AVANZADOS Y TABLAS DINAMICAS

CAPITULO 6

Filtros y Bases de datos

El concepto de base de datos en excel, permite manipular los datos que hay en la planilla, como si fuera una base de datos y por lo tanto se pueden realizar operaciones bxicas de manejo de base de datos como son : o Ordenar los datos por columnas o Consultar datos que cumplan con caractersticas especiales,, utilizando los llamados AUTOFLITROS o FILTROS AVANZADOS o Extraer datos que cumplan caractersticas especiales o Realizar operaciones estadsticas sobre unos datos aplicando los llamados filtros. Conceptos Bsicos Registro : Corresponde a una fila de la planilla Campo: Corresponde a una columna de la planilla Consideraciones que se deben tener encuenta al tomar una planilla como base de datos. 1. Los encabezados de columnas (campos) deben ocupar una sola fila. 2. No deben existir en la planilla filas en blanco intermedias. Supongamos la sgte. Planilla en excel:

Ordenar la base de datos, selexcionar la planilla, el rango B3:G15

Preparado por Profesor : Rodrigo Araya A .

Ahora veremos la planilla ordenada por el nombre del producto y dentro de ese campo por la fecha del docto.

AUTOFILTROS Es un tipo de filtro que muestra los resultados en la misma planilla, ocultando los registros que no cumplen con los criterios. CREACION DE UN AUTOFILTRO 1. Marque la base de datos, incluyendo los encabezados de columna(campos), para el ejemplo que estamos viendo. Seleccionar el rango B3:G15 (esta ser su base de datos) 2. Men DATOS opcin FILTRO Opcin AUTOFILTRO. 3. Observar que a la derecha de cada campo aparece una punta de flecha (lista de seleccin). 4. Si hace un clic en cualquiera de estas puntas de flecha notar que se despliegan las opciones:

Preparado por Profesor : Rodrigo Araya A .

(Todas)

(las 10 mas...) (personalizar...)

los distintos valores para el campo

5. Al elegir una de las opciones Ud. Est creando un criterio.

Preparado por Profesor : Rodrigo Araya A .

EJEMPLO: Si Ud. desea visualizar solo los registros de adquisiciones de manzanas debe elegir para el campo MOVIMIENTO la opcin ADQ y para el campo PRODUCTO la opcin MANZANA.

LA OPCION PERSONALIZAR Permite crear criterios ms amplios (como rangos). EJEMPLO: supongamos que desea visualizar los movimientos que involucran una cantidad de palletes(unidades) superior a 60 pero inferior a 100

Preparado por Profesor : Rodrigo Araya A .

Preparado por Profesor : Rodrigo Araya A .

FILTROS AVANZADOS Se puede filtrar registros de la base de datos seleccionndolos de acuerdo a un determinado criterio La Opcin Filtro avanzado, permite obtener los registros que cumplan el criterio en otra rea de la hoja. CREACION DE UN FILTRO AVANZADO (Buscaremos todas las manzanas que tengan sobre o igual a 40 pallets) 1.Debe crear en otra parte de la hoja el rea de criterios (compuesta por los nombres de los campos que necesita y bajo ellos las condiciones que requiera). Se sugiere que los nombres de los campos para el criterio los copie de la base de datos para eliminar la probabilidad de error en la digitacin. 1.1.Crear el rango de criterios donde ud. quiera, yo lo har a partir de la celda I2. Deber quedar:

2.

Seleccionar el rango de la base de datos (B3:G15) 3. Elija la opcin DATOS del men. Luego la opcin FILTRO, y finalmente la opcin FILTRO 4. Se desplegar la siguiente ventana:

5. 6.

Indicado todo lo anterior haga un clic en ACEPTAR Obtendr todos los registros de la base de datos que cumplan con el criterio establecido.Ac los registros se muestran en la misma base de datos

Preparado por Profesor : Rodrigo Araya A .

Ahora buscaremos los mismos registros, pero los copiaremos a otra parte de la planilla 1. Marque el rango de la base de datos (B3:G15) 2. Exija la opcin DATOS FILTRO FILTRO AVANZADO. Se despliega la siguiente ventana:

3. 4. 5. 6. 7.

Elija la opcin COPIAR A OTRO LUGAR En RANGO DE CRITERIOS indique el rango $I$3:$J$4 (marcndolo) En COPIAR A indique la celda I$7 Haga un clic en ACEPTAR El resultado obtenido es:

Preparado por Profesor : Rodrigo Araya A .

Funciones Estadsticas para bases de datos

=BDSUMA(rango de la base de datos;n de columna a sumar;rango de criterio) =BDPROMEDIO(rango de la base de datos;N de columna a promediar;rango de criterio) =BDMIN(rango de la base de datos;N de columna de donde obtener el minimo;rango de criterio) =BDMAX(rango de la base de datos;N de columna de donde obtener el mayor;rango de criterio) =BDCONTARA(rango de la b.de datos;N de columna donde contar los registros;rango de criterio) OBSERVACION: El segundo parmetro es la celda donde se encuentra el encabezado necesario. EJEMPLOS: Considerando la base de datos anterior, obtener los siguientes totales:

DESARROLLO Antes de usar las funciones estadsticas de bases de datos, hay que establecer los criterios y lo escribiremos en la fila 14 y 15 desde la columna I en adelante.

Preparado por Profesor : Rodrigo Araya A .

TABLAS DINAMICAS Es una herramienta que permite, a partir de una planilla, obtener totales generales y/o de detalle, utilizando los cubos dimensionales de informacin. DESARROLLO A TRAVS DE UN EJEMPLO 1. Posicionese dentro de la base de datos tomada como ejemplo 2. Elija del men la opcin DATOS y del men desplegado la opcin ASISTENTE PARA TABLAS DINAMICAS. Se desplegar la siguiente ventana:

3. Haga un clic en SIGUIENTE y se desplegar la siguiente ventana (verifique que el rango indicado corresponde a la planilla completa)

Preparado por Profesor : Rodrigo Araya A .

4.- Al hacer siguiente el sistema le preguntar si quiere dejar la tabla dinmica en la misma hoja

existente o en una diferente:

Haga un clic en FINALIZAR y se desplegar la siguiente Hoja:

4. Arrastre (de la lista que est a la derecha): PLANTA (djelo sobre PAGINA) MERCADO DESTINO (djelo sobre FILA) FRUTA (djelo sobre FILA) PUERTO DE SALIDA (djelo sobre COLUMNA) MONTO VENTA (djelo dentro del recuadro DATOS)

Preparado por Profesor : Rodrigo Araya A .

5. haga doble clic en SUMA DE MONTO DE VENTA (dentro del recuadro DATOS). Se desplegar la siguiente ventana:

Elija Nmero y seleccione la opcin moneda para poner el smbolo monetario sin decimales..

Preparado por Profesor : Rodrigo Araya A .

6. Ud. obtendr el siguiente resultado (analcelo)

Preparado por Profesor : Rodrigo Araya A .

CONSOLIDACIONES , SUBTOTALES AGRUPAMIENTO Y ESQUEMAS Muchas veces uno se ve enfrentando a la necesidad de poder consolidar los datos, aplicando alguna funcin aritmtica como sumar los resultados o aplicar un promedio, EXCEL posee una herramienta muy til para ello que es la consolidacin de datos, para utilizarla se deben tener en consideracin que las planillas a utilizar deben poseer en lo posible la misma estructura a nivel de columnas mas que de filas. Para utilizarla de debe ir a Datos, Consolidar

Se debe estipular que funcin se va a utilizar, luego se debe seleccionar el rango de datos que se desean consolidar, uno a uno, aplicando la opcin agregar una vez que se ha seleccionado. Se debe aplicar rtulos a Fila y columnas para que quede con los ttulos correpondientes.Crear vnculos indica que los datos consolidados se encuentren conectados a los datos de origen por tanto si ello cambian el consolidado tambin, en caso que no solo la consolidacin queda como una copia de valores de los datos de origen.-

Subtotales Al trabajar en una bases de datos se pueden generar subtotales de acuerdo a uno o varios campos de ttulos, para ello ante todo se debe ordenar el campo de ttulo que se va a subtotalizar.

Preparado por Profesor : Rodrigo Araya A .

Se debe seleccionar el campo de ttulo asociado a Por cada cambio de en:, e decir que cada que cambie este campo se realizar el subtotal, luego se debe identificar que funcin se aplicar en este caso funcin Suma, luego se debe indicar en dnde se dejara el valor subtotalizado. Si se desea generar varios subtotales de sebe desmarcar la casilla de Verificacin que indica Reemplazar subtotales actuales.

Preparado por Profesor : Rodrigo Araya A .

Agrupamiento Mediante Agrupamiento se puedentener planillas de bases de datos con datos agrupados por ciertos rango, por ejemplo: Si se tiene una planilla coniformes de ventas mensuales y uno desea un resumen por trimestre, una forma rpida y ordena de hacerlo es a travs de agrupamiento.

Para ello de debe ir a Datos Agrupar y esquemas, uno puede seleccionar agrupar por columnas, por filas o agrupamiento autoesquema, se debe seleccionar las filas y/o columnas que se requiere agrupar, en este caso se marcan las columnas donde se encuentran los meses, sin seleccionar la columna de Trimestre, y se agrupa por columna:

Por cada agrupamiento queda el signo + o que indica se expanden los detalles o se contraen, de esta manera se tiene una visin general de slo los trimestre o con detalles incluidos de los meses respectivos.

Preparado por Profesor : Rodrigo Araya A . JERARQUIA DE ERRORES EN EXCEL

APENDICE 1

Los errores en Microsoft Excel, se producen generalmente por inconsistencias en la creacin e implementacin en las formulas y funciones creadas por los usuarios. Se debe mencionar que los significados de los errores se van adaptando al entorno en el cual se generan. QU PUEDE ESTAR EQUIVOCADO EN UNA HOJA DE CLCULO? Excel puede ayudar a detectar problemas comunes en las hojas de clculo como se detalla a continuacin: Problema Texto irreconocible frmula Error de frmula anidacin Ejemplo una Nombre de funcin mal escrito Cmo detectarlo Valor de error #NOMBRE?

en en

la Parntesis mal colocado en la Herramienta Evaluar frmula frmula

La operacin no incluye todos La frmula no incluye las filas Realice un seguimiento de las los datos agregadas posteriormente relaciones entre las frmulas y las celdas La frmula contiene uno de Frmula incoherente en el rea varios problemas frecuentes Comprobacin frmulas de errores en las

En cualquiera de los ejemplos anteriores lo que Ud. Debe hacer es: 1.- RESOLVER TODOS LOS VALORES DE ERROR Empiece con lo obvio y resuelva todos los valores de error en primer lugar. Cuando se escribe una frmula cuyo resultado Excel no puede evaluar, ste muestra un valor de error en la celda o marca por completo la formula. #####: => La columna no es lo suficientemente ancha, o se ha utilizado una fecha u hora negativa Si la columna no tiene el ancho necesario para mostrar el contenido, amplela o haga que quepan los datos. ENSANCHAR LA COLUMNA: Arrastre el lmite derecho del encabezado de la columna hasta que sta adopte el ancho deseado. O bien haga doble clic en el lmite derecho del encabezado de la columna.

CAMBIAR EL FORMATO: El contenido puede ajustarse con un formato distinto. Por ejemplo, use un formato sin el smbolo de moneda o con menos decimales. Seleccione la columna y, a continuacin, en el men Formato, haga clic en Categora. Seleccione un estilo de formato, cambie el nmero de decimales si lo desea y, a continuacin, haga clic en Aceptar. CONSIDERACIONES GENERALES PARA EL TRATAMIENTO DE FECHAS Si las fechas o las horas son negativas, compruebe los datos y las frmulas Las fechas y horas deben ser siempre valores positivos, una fecha no puede ser negativa. Si usa fechas y horas en las frmulas, asegrese de que las frmulas son correctas. Si los valores negativos son correctos, cambie el formato de la celda por otro que no sea un formato de fecha u hora. Seleccione las celdas, en el men Formato haga clic en Celdas, haga clic en la ficha Nmero y, a continuacin, seleccione un formato que no sea de fecha ni de hora.

Preparado por Profesor : Rodrigo Araya A .

#VALOR!: Se ha usado un argumento u operando errneo en la formula o funcin. Para ello, puede utilizar la herramienta Rastrear error para verificar los argumentos, como las referencias a celdas, los nmeros y los operandos (operando: referencias de celdas o nombres de rangos incorporadas en una frmula. #DIV/0!: Se est dividiendo un nmero, una celda por cero (0) o por una celda vaca o en blanco. Para ello : Busque una divisin por cero (0) explcita en la frmula, por ejemplo, =5/0. Si la frmula contiene una referencia a una celda en blanco o a una celda que contenga cero como divisor: Cambie la referencia de celda por la de otra celda. Escriba un valor distinto de cero en la celda usada como divisor. Para evitar que aparezca este valor de error, se puede usar la funcin de hoja de clculo SI de modo que se calcule el valor slo si el divisor es cero (0). Por ejemplo, si la frmula que crea el error es =A5/B5, use en su lugar =SI(B5=0,"",A5/B5). Las dos comillas representan una cadena de texto vaca. #NOMBRE?: Implica que a frmula contiene letras que Microsoft Excel no reconoce, para ello siempre compruebe las causas ms frecuentes pueden ser: Asegrese de que el nombre del conjunto de celdas existe, para ello en el men Insertar, elija Nombre y, a continuacin, haga clic en Definir, si el nombre no est en la lista, defnalo: En el cuadro Nombres en el libro, escriba el nombre para la frmula. En el cuadro Se refiere a, escriba = (signo igual), seguido de la frmula o del valor de la constante (constante: valor que no ha sido calculado y que, por tanto, no vara. Por ejemplo, el nmero 210 y el texto "Ingresos trimestrales" son constantes. Las expresiones, o los valores resultantes de ellas, no son constantes.) . Compruebe que el nombre est escrito correctamente. Verifique la ortografa. Seleccione el nombre en la , presione F3, haga clic en el nombre que desea usar y, a continuacin, barra de frmulas haga clic en Aceptar. Si la barra de frmulas no est visible, en el men Ver, haga clic en Barra de frmulas. Si la frmula usa un rtulo, asegrese de que se permiten. Para autorizar los rtulos, en el men Herramientas, haga clic en Opciones y, a continuacin, haga clic en la ficha Calcular. En Opciones del libro, active la casilla Aceptar rtulos en las frmulas. Cualquier texto que desee incluir en la frmula, escrbalo entre comillas dobles. #REF!: La frmula contiene una referencia de celda errnea Si elimin o peg las celdas a las que se hace referencia en la frmula, cambie las frmulas o inmediatamente despus de restablezca las celdas de la hoja de clculo haciendo clic en Deshacer borrar o pegar las celdas.