excel manual avanzado

Upload: raul-zarate

Post on 03-Apr-2018

299 views

Category:

Documents


2 download

TRANSCRIPT

  • 7/28/2019 EXCEL Manual Avanzado

    1/149

    Manual avanzado de excel

    79

    Ing. Carmen L. Infante S.

    Ing. Fracisco J.Cruz V.

  • 7/28/2019 EXCEL Manual Avanzado

    2/149

    Manual avanzado de excel

    80

    NDICE

    PAG.

    INTRODUCCION 03

    CAPITULO I. CONCEPTOS BSICOS 05

    CAPITULO II. FORMULAS Y FUNCIONES AVANZADAS 17

    CAPITULO III. ESCENARIOS , ESQUEMAS Y VISTAS 37

    CAPITULO IV. LISTAS 60

    CAPITULO V. FUNCIONES DE BSQUEDA 70

    CAPITULO VI. MACROS 79

    CAPITULO VII. TABLAS Y GRAFICOS DINAMICOS 97

    CAPITULO VIII.FORMULARIOS 120

    BIBLIOGRAFA Y DIRECCIONES ELECTRNICAS 140

  • 7/28/2019 EXCEL Manual Avanzado

    3/149

  • 7/28/2019 EXCEL Manual Avanzado

    4/149

  • 7/28/2019 EXCEL Manual Avanzado

    5/149

    Manual avanzado de excel

    83

    Capitulo I

    CONCEPTOS BASICOSINTRODUCCIN.-

    Excel es una hoja de calculo que puede ser usado por cualquier profesional en surespectiva rea temtica pero depende del conocimiento de este para que aprovecheal mximo todas las utilidades que de la herramienta provee por lo cual se hacenecesario conocer algunas cosas adicionales que no se toman con frecuencia en uncurso de Excel, esto permitir brindar al usuario una visin mucho ms amplia de laimportancia de saber usar esta herramienta.

    El propsito de este manual es dar a conocer al usuario algunas herramientas que nose logran dar a conocer en un curso normal ( computacin I). Se esta partiendo que elusuario tiene un conjunto de conocimientos previos y con la gua de un profesor lepermitir su entendimiento.

    En esta parte brindaremos un conjunto de conocimientos necesario y bsicos que elusuario debe saber para familiarizarse con una hoja de calculo.

    Conceptos Bsicos.-

    La barra de ttuloEn ella se muestra el nombre del libro sobre el que se est trabajando en esemomento. Inicialmente es un nombre provisional hasta que se guarde, dondepodremos dar al libro otro nombre.

    Barra de mens.La barra de mens, al igual que el Word, contiene todas las operaciones que sepueden realizar en Excel, agrupadas en mens desplegables.

    Barra de herramientas estndar.

  • 7/28/2019 EXCEL Manual Avanzado

    6/149

    Manual avanzado de excel

    84

    Contiene los botones para ejecutar de forma inmediata algunas de las operacionesms habituales, como Abrir nuevo libro, Abrir nuevo desde archivo, Guardar ,Cortar, Copiar, Pegar, Imprimir, Ordenar etc.

    Barra de formato.Contiene los botones para aplicar de forma rpida un formato a las celdas filas ycolumnas de la tabla, como Elegir una fuente, tamao, poner negrita, cursiva,subrayado, etc.

    Barra de frmulas.Utilizaremos esta barra para aadir los clculos y las frmulas que se necesiten.

    Cuando colocamos el cursor en la caja de texto se activan los botones de estabarra, y escribiremos el clculo. Esto se estudiar con ms detalle en otro punto deltema.

    Hoja de datos.Si observamos la ventana de Excel vemos que la hoja de datos est dividida encolumnas alfabetizadas y filas numeradas. Todo el conjunto es lo que llamamos lahoja de datos, es donde vamos a escribir los datos.

    SELECCIONAR CELDAS, FILAS, COLUMNAS

    Antes de trabajar con celdas, debe seleccionar una celda o un grupo de celdas.Cuando seleccione una nica celda, esta se vuelve activa y su referencia aparecer enel cuadro de nombres, en el extremo izquierdo de la barra de frmulas. Aunque encada momento puede estar activa una nica celda, a menudo puede acelerar lasoperaciones seleccionando un grupo de celdas denominados rangos.

    La celda activa es la celda seleccionada en la que se introduce los datos cuando seempiezan a escribir. Slo puede haber una celda activa a la vez. La celda activa estrodeada por un borde ms grueso.

  • 7/28/2019 EXCEL Manual Avanzado

    7/149

    Manual avanzado de excel

    85

    Rango o bloque de celdas: es un conjunto de celdas adyacentes, que forman un arearectangular. La referencia a un rango es indicando la Celda inicial: Celda final .Ejemplo A1:B5, C8:D20.

    Formas de

    SeleccinProcedimiento

    EL RAT NVarias de celdas Al interior de la celda debe salir el puntero en forma de cruz gruesa,

    luego con el ratn realizar un clic de arrastre.

    Filas o Columnas Dar clic en los encabezados ya sea horizontal o vertical Toda la Hoja Dar clic en el vrtice formado por la interseccin de los

    encabezadosTECLADO

    Varias Celdas Sin dejar de presionar o tecla , pulse

    sucesivamente las flechas de direccin .

    TECLADO Y RATN Celdas

    discontinuas

    Sin dejar de presionar la haga clic de arrastre en un rango o

    bloque determinado.Varias celdas Sin dejar de presionar o tecla , haga clic en

    diagonal desde A1:F10.

    USO DE TECLAS EN MODO ABREVIADO.

    q Teclas para moverse y desplazarse por una hoja de clculo o un lib ro

    Teclas para moverse y desplazarse por una hoja de clculo o un lib ro

    Presione Para

    Teclas de direccin Moverse una celda hacia arriba, hacia abajo, haciala izquierda o hacia la derecha

    CTRL+tecla de direccin Ir hasta el extremo de la regin de datos actual

  • 7/28/2019 EXCEL Manual Avanzado

    8/149

    Manual avanzado de excel

    86

    INICIO Ir hasta el comienzo de una fila

    CTRL+INICIO Ir hasta el comienzo de una hoja de clculo

    CTRL+FIN Ir a la ltima celda de la hoja de clculo, que es lacelda ubicada en la interseccin de la columnasituada ms a la derecha y la fila ubicada ms abajo(en la esquina inferior derecha) o la celda opuesta ala celda inicial, que es normalmente la celda A1

    AV PG Desplazarse una pantalla hacia abajo

    RE PG Desplazarse una pantalla hacia arriba

    ALT+AV PG Desplazarse una pantalla hacia la derecha

    ALT+RE PG Desplazarse una pantalla hacia la izquierda

    CTRL+AV P G Ir a la siguiente hoja del libro

    CTRL+RE PG Ir a la hoja anterior del libro

    CTRL+F6 o CTRL+TAB Ir al siguiente libro o a la siguiente ventana

    CTRL+MAY S+F6 oCTRL+MAYS+TAB

    Ir al libro o a la ventana anterior

    F6 Mover al siguiente panel de un libro que se hadividido

    MAYS+F6 Mover al anterior panel de un libro que se ha dividido

    CTRL+RETROCESO Desplazarse para ver la celda activa

    F5 Mostrar el cuadro de dilogo Ir a

    MAYS+F5 Mostrar el cuadro de dilogo Buscar

    MAYS+F4 Repetir la ltima accin de Buscar (igual a Buscar siguiente)

    TAB Desplazarse entre celdas desbloqueadas en unahoja de clculo protegida

    Teclas para moverse por una hoja de clculo con el modo Fin acti vado

    Presione Para

    FIN Activar o desactivar el modo Fin

    FIN, tecla de direccin Desplazarse un bloque de datos dentro de una fila ocolumna

  • 7/28/2019 EXCEL Manual Avanzado

    9/149

  • 7/28/2019 EXCEL Manual Avanzado

    10/149

    Manual avanzado de excel

    88

    Presione Para

    CTRL+MAYS+% Aplicar el formato Porcentaje sin decimales

    CTRL+MAY S+ Aplicar el formato numrico Exponencial con dosdecimales

    CTRL+MAY S+ Aplicar el formato Fecha con el da, mes y ao

    CTRL+MAYS+@ Aplicar el formato Hora con la hora y minutos eindicar a.m. o p.m.

    CTRL+MAYS+! Aplicar el formato Nmero con dos decimales,separador de millares y signo menos ( ) para losvalores negativos

    CTRL+MAYS+& Aplicar un borde

    CTRL+MAY S+_ Quitar los contornos

    CTRL+N Aplicar o quitar el formato de negrita

    CTRL+K Aplicar o quitar el formato de cursiva

    CTRL+S Aplicar o quitar el formato de subrayado

    CTRL+5 Aplicar o quitar el formato de tachado

    CTRL+9 Ocultar filas

    CTRL+MAYS+( (parntesisde apertura) Mostrar filas

    CTRL+0 (cero) Ocultar columnasCTRL+MAYS+) (parntesisde cierre) Mostrar columnas

    Teclas para trabajar con los cuadros de dilogo Abri r y Guardar como

    Presione Para

    CTRL+F12 o CTRL+A Mostrar el cuadro de dilogo Abr ir

    ALT+F2, F12 o CTRL+G Guardar el libro activo

    ALT+MAY S+F2 oMAYS+F12

    Mostrar el cuadro de dilogo Guardar c omo

    ALT+1 Ir a la carpeta anterior

    ALT+2 Abrir la carpeta que se encuentra un nivel por encimade la carpeta abierta

  • 7/28/2019 EXCEL Manual Avanzado

    11/149

    Manual avanzado de excel

    89

    ALT+3 Cerrar el cuadro de dilogo y abrir la pgina debsqueda del World Wide Web

    ALT+4 Eliminar la carpeta o archivo seleccionado

    ALT+5 Crear una nueva subcarpeta en la carpeta abiertaALT+6 Alternar entre las vistas Lista, Detalles, Propiedades y

    Vista previa

    ALT+7 Mostrar el men Herramientas (botn Herramientas)

  • 7/28/2019 EXCEL Manual Avanzado

    12/149

    Manual avanzado de excel

    90

    Teclas para insertar, eliminar y co piar una seleccin

    Presione Para

    CTRL+C Copiar la seleccin

    CTRL+X Cortar la seleccin

    CTRL+V Pegar la seleccin

    SUPR Borrar el contenido de la seleccin

    CTRL+GUI N Eliminar celdas

    CTRL+Z Deshacer la ltima accin

    CTRL+MAYS+

    SIGNO MS

    Insertar celdas vacas

    La combinacin de teclas anteriormente expuestas son las ms conocidas por unusuario de Excel.

    Pegado Especial

    Ms Excel 2000 permite pegar no solo el contenido de las celdas, sino las frmulas, losformatos, los comentarios, todo excepto bordes o reglas de validacin. Adems, si lasceldas origen y las destino contienen nmeros se pueden realizar una operacin y

    automticamente se tiene nuevos valores. Saltar blancos no pega las celdas en blancode rea pegada.

    Excel permite trasponer el contenido de la(s) fila(s) por una columna(s) en otraspalabras cambia la orientacin de los datos cuando se pegan, los datos de la filasuperior se colocan en la columna y los de la columna izquierda, aparecen en la filasuperior.

    Se muestra el siguiente ejemplo, de transponer el B1:C4

    Bloque B1:C4 antes de Trasponer Bloque B1:C4, despus de transponer

  • 7/28/2019 EXCEL Manual Avanzado

    13/149

    Manual avanzado de excel

    91

    Para lograr Transponer un bloque realice los siguientes pasos

    1. Seleccione las celdas por ejemplo de B1:C42.Haga clic derecho y luego clic en copiar 3.Coloque el cursor en la celda destino por ejemplo A54. Haga Clic derecho y luego clic en Pegado Especial5.Haga clic en la casilla de verificacin Transponer y6..Finalmente clic en Aceptar .

    Pegar sin Formato.

    Al momento de realizar el pegado hacia una celda o rango, puede pegar sin formato,siempre cuando ingrese a pegado especial seleccione la opcin valores y luego clicen aceptar. De esta manera solamente se traslada su contenido ms no el formato.

    Formato condicional.

    Si una celda contiene los resultados de una frmula u otros valores que desee evaluar,puede identificarse las celdas aplicando formatos condicionales. Por ejemplo,puede aplicar negrita y color azul a la celda(s) si las notas sobrepasan de 15; pero sonmenores de 20.

    Para ubicar los formatos condicionales realice los siguientes pasos1. Seleccione las celdas que desee resaltar. Por ejemplo de B2:D32. Haga clic en el comando Formato condicional, del men Formato. A

    continuacin se presenta la siguiente ventana de dilogo:3. Siga el siguiente procedimiento: Para Utilizar los valores de las celdas

    seleccionadas como el criterio de formato, haga clic en valor de la celda,seleccione la frase de comparacin e introduzca un valor entre 15 y 20 en elcuadro correspondiente.

    4. Haga clic en Formato. Seleccione el estilo de fuente, negrita, el color, losbordes o la trama que desee aplicar. Microsoft Excel solamente aplicar losformatos seleccionados si el valor de la celda cumple la condicin o si lafrmula devuelve un valor VERDADERO.

    5. Para agregar otra condicin, haga clic en el botn Agregar y repita los pasosdel 3 al 5. Puede especificarse hasta tres condiciones. Si ninguna de las

  • 7/28/2019 EXCEL Manual Avanzado

    14/149

    Manual avanzado de excel

    92

    condiciones que se han especificado es verdadera, las celdas conservan losformatos existentes para identificar una cuarta condicin.

    Cuando cambian las condiciones. Si el valor de la celda cambia y ya no cumple lacondicin especificada, Microsoft Excel suprimir temporalmente los formatos queresalten esa condicin. Los formatos condicionales continan aplicados a las celdashasta que se quiten, aunque no se cumplan ninguna de las condiciones y no semuestren los formatos de celda especificados.

    Formatos a celdas.

    Cuando usamos Excel podemos aplicar un formato a las celdas que deseamos .Los tipos de formatos que se pueden aplicar son: ( Nmero , Al ineacin, Fuente,Bordes, Tramas, Proteger ). Como se muestra en la figura 1.x.

    q Para acceder a la opcin formato podemos realizarlo con la combinacin deceldas

    q Cuando tengamos una tabla ha esta le podemos aplicar un autoformatoaccediendo al men formato.

    Formato personalizadosPermite mostrar datos de acuerdo a los requerimientos del usuario. Se usan doscaracteres:

    # : Para mostrar dgitos del 0 al ), presenta un espacio el encontrar el valor de cero en alguna celda, 0 : Para mostrar dgitos del 0 al 9, presenta el valor cero, si el valor de la celda es cero.

  • 7/28/2019 EXCEL Manual Avanzado

    15/149

    Manual avanzado de excel

    93

    1. Seleccione las celdas a las que desea dar formato.2. En el men Formato, haga clic en Celdas y haga clic en la ficha Nmero. 3. En la lista Categora, Haga clic en una categora y, a continuacin, haga clic en

    un formato integrado que se asemeje al que se desee.4. En la lista Categora, Haga clic en Personalizada. 5. En el cuadro tipo, modifique los cdigos de formato de nmero para crear el

    formato que desee.6. Cuando se modifica un formato, ste no se quita.7. Puede especificar hasta cuatro secciones de cdigos de formato. Las

    secciones, separadas por caracteres de punto y coma, definen los formatos delos nmeros positivos, nmeros negativos, valores cero y texto, es ese orden.Si especifica slo dos secciones, la primera se utiliza para los nmeros

    positivos y ceros, y la segunda se utiliza para los nmeros negativos. Siespecifica slo una seccin, todos los nmeros utilizan ese formato. Si omiteuna seccin, incluya el punto y la coma de esa seccin.

    #.###,00_);[Rojo](#.###,000);0,00; Ventas @

    Formato de nmeros positivos

    Formato de nmeros negativos Formato de ceros

    Formato de texto

    Detalle de formato Personalizado

  • 7/28/2019 EXCEL Manual Avanzado

    16/149

    Manual avanzado de excel

    94

    PREGUNTAS DE REPASO

    Se desea darle color azul aquellas celdas cuyo valor es mayor que 300 y el color rojoaquellas celdas que son menores que 300 .

    Utilizando formato, celdas; resolver los siguientes ejercicios .

    Pregunta 1.

    Pregunta 2

  • 7/28/2019 EXCEL Manual Avanzado

    17/149

    Manual avanzado de excel

    95

    Pregunta 3

    Pregunta 4

    Pregunta 5

  • 7/28/2019 EXCEL Manual Avanzado

    18/149

    Manual avanzado de excel

    96

    Capitulo II

    FORMULAS Y FUNCIONESFORMULAS

    Crear frmulas

    La estructura o el orden de los elementos de una frmula determinan el resultado finaldel clculo. Las frmulas en Microsoft Excel siguen una sintaxis especfica, u orden,que incluye un signo igual (=) seguido de los elementos que van a calcularse (losoperandos), que estn separados por operadores de clculo. Cada operando puede

    ser un valor que no cambie (un valor constante), una referencia de celda o de rango,un rtulo, un nombre o una funcin de la hoja de clculo.

    Excel realiza las operaciones de de izquierda a derecha, siguiendo el orden deprecedencia de los operadores , comenzando por el signo igual (=). Puede controlar elorden en que se ejecutar el clculo utilizando parntesis para agrupar lasoperaciones que deben realizarse en primer lugar. Por ejemplo, la siguiente frmula daun resultado de 11 porque Excel calcula la multiplicacin antes que la suma. Lafrmula multiplica 2 por 3 y, a continuacin, suma 5 al resultado.=5+2*3Por el contrario, si se utilizan parntesis para cambiar la sintaxis, Excel sumar 5 y 2 y,a continuacin, multiplica el resultado por 3, obtenindose 21.=(5+2)*3En el siguiente ejemplo, los parntesis que rodean la primera parte de la frmulaindican a Excel que calcule B4+25 primero y despus divida el resultado de la sumade los valores de las celdas D5, E5 y F5.

    =(B4+25)/SUMA(D5:F5)

    Operadores de clculo de las frmulas

    Los operadores especifican el tipo de clculo que se desea realizar con los elementosde una frmula. Microsoft Excel incluye cuatro tipos diferentes de operadores declculo: aritmtico, comparacin, texto y referencia.

  • 7/28/2019 EXCEL Manual Avanzado

    19/149

    Manual avanzado de excel

    97

    Operadores aritmticos Para ejecutar las operaciones matemticas bsicas comosuma, resta o multiplicacin; combinan nmeros y generan resultados numricos,utilice los siguientes operadores aritmticos.

    Operador aritmtico Significado Ejemplo

    +(signo ms) Suma 3+3

    - (signo menos) RestaNegacin

    3-1-1

    * (asterisco) Multiplicacin 3*3

    / (barra oblicua) Divisin 3/3

    % (signo deporcentaje)

    Porcentaje 20%

    (acento circunflejo) Exponente 32 (el mismo que 3*3)

    Operadores de comparacin Se pueden comparar dos valores con los siguientesoperadores. Al comparar dos valores con estos operadores, el resultado es un valorlgico, bien VERDADERO bien FALSO.

    Operador decomparacin Significado Ejemplo

    =(igual) Igual a A1=B1

    >(mayor que) Mayor que A1>B1

    =B1

  • 7/28/2019 EXCEL Manual Avanzado

    20/149

    Manual avanzado de excel

    98

    Operadores de referencia Combinan rangos de celdas para los clculos con lossiguientes operadores.

    Operador dereferencia Significado Ejemplo

    :(dos puntos) Operador de rango quegenera una referencia a todaslas celdas entre dosreferencias, stas incluidas.

    B5:B15

    , (coma) Operador de unin quecombina varias referencias enuna sola.

    SUMA(B5:B15,D5:D15)

    Referencias a celdas y rangosUna referencia identifica una celda o un rango de celdas en una hoja de clculo eindica a Microsoft Excel en qu celdas debe buscar los valores o los datos que deseautilizar en una frmula. En las referencias se pueden utilizar datos de distintas partesde una hoja de clculo en una frmula, o bien utilizar el valor de una celda en variasfrmulas. Tambin puede hacerse referencia a las celdas de otras hojas en el mismolibro, a otros libros y a los datos de otros programas. Las referencias a celdas de otroslibros se denominan referencias externas. Las referencias a datos de otros programasse denominan referencias remotas.

    Diferenci a entre el estil o de referencia A1 y el estilo de referencia F1C1

    El estilo de referencia A1 De forma predeterminada, Microsoft Excel utiliza el estilode referencia A1, que se refiere a columnas con letras (de A a IV, para un total de 256columnas) y a las filas con nmeros (del 1 al 65536). Estas letras y nmeros sedenominan encabezados de fila y de columna. Para hacer referencia a una celda,

    escriba la letra de la columna seguida del nmero de fila. Por ejemplo, D50 hacereferencia a la celda en la interseccin de la columna D y la fila 50. Para hacerreferencia a un rango de celdas, especifique la referencia de la celda en la esquinasuperior izquierda del rango, dos puntos (:) y, a continuacin, la referencia a la celdaen la esquina inferior derecha del rango. A continuacin, se muestran algunosejemplos de referencias.

  • 7/28/2019 EXCEL Manual Avanzado

    21/149

    Manual avanzado de excel

    99

    El estilo de referencia F1C1 Tambin puede utilizarse un estilo de referencia en elque se numeren tanto las filas como las columnas de la hoja de clculo. El estilo dereferencia F1C1 es til para calcular las posiciones de fila y columna en macros . En el

    estilo F1C1, Excel indica la ubicacin de una celda con una "F" seguida de un nmerode fila y una "C" seguida de un nmero de columna. Obtener informacin sobrereferencias F1C1 .

  • 7/28/2019 EXCEL Manual Avanzado

    22/149

    Manual avanzado de excel

    100

    EJERCICIOS

    Ejemplo de Uso de celdas absolutas

    Usando Celdas absolutas calcular el Promedio Final, deber utilizar las ponderacionesbrindadas

    Utilizando Frmulas calcular los siguientes Casos:q Supngase que una persona decidi ahorrar S/. 10000 durante cinco aos en

    un banco donde ofreca una tasa efectiva anual de 8% Qu monto obtuvo alfinal de ese perodo?Y cuanto habra logrado si ahorraba a la misma tasa,pero en trminos nominales?

    F= Capital FinalP=es el capital inicialI= es la tasa de inters para el perodoN=es el nmero de perodos.

    F=P(1+i) n .. Formula para la tasa efectiva

    F=P(1+i*n) ... Formula para la tasa nominal

    q Supngase que se deposita S/50000 en un banco, a una tasa de inters anual

    de 5% durante cinco aos. Cunto se obtendr al final del plazo?(Recuerdrdese que todas las tasas bancarias estn expresadas en trminosefectivos anuales).Usar la siguiente frmula

    F=P(1+i) n .. Formula para la tasa efectiva

  • 7/28/2019 EXCEL Manual Avanzado

    23/149

    Manual avanzado de excel

    101

    q Una persona obtiene de un amigo un crdito por S/. 1500, reembolsable en 24cuotas mensuales, a una tasa de inters mensual de 5.5% Cunto deberdevolver mensualmente?

    q Un bono a 8 aos con un valor nominal de $10,000 paga a su tenedor una tasaanual de 8.5% en cupones trimestrales Cunto le paga trimestralmente?

    Usar la siguiente frmula para los dos casos anteriores:

    C=P[ (i*(1+i)n) / ((1+i) n- 1)]

    q Un empresario desea calcular el valor actual de los $20000 de ingresosanuales que su empresa espera obtener durante los siguientes ochoaos, dada una tasa de inters anual de 9%.

    q Se espera que un proyecto tenga ingresos anuales del orden de S/.500,000 durante los siguientes cinco aos. Se desea saber si a una tasade inters anual de 10%, ste permitir obtener un valor presente deingresos superior al valor presente de sus egresos, ascendente aS/.2 000,000 pues de no ser as no ser viable.

    Usar la siguiente frmula para los dos casos anteriores:

    P=C[ ((1+i) n-1) / (i*(1+i)n)]

  • 7/28/2019 EXCEL Manual Avanzado

    24/149

    Manual avanzado de excel

    102

    FUNCIONES

    Una funcin es una frmula especial que ya esta escrita y que acepta uno o ms

    valores llamados argumentos y realiza una operacin devolviendo un resultado.

    FUNCIONES MATEMTICAS

    CONTAR.BLANCO:Contabiliza el nmero de celdas en blanco dentro de un rango.Sintaxis.CONTAR.BLANCO(Rango)Rango: Es el bloque de celdas cuyas celdas en blanco se desea contabilizar.

    CONTAR.SI.Contabiliza las celdas del rango que cumplan la condicin del criterio.Sintaxis.CONATAR.SI(Rango;Criterio)Rango : Es el bloque de celdas cuyos datos se desa contabilizar.Criterio: Especifica el criterio(texto, expresin o nmero) que determinar las celdasdel rango sern contabilizadas.

  • 7/28/2019 EXCEL Manual Avanzado

    25/149

    Manual avanzado de excel

    103

    SUMAR.SI:Suma las celdas en el rango que coinciden con el argumento criterio.SintaxisSUMAR.SI(Rango1;Criterio;Rango2)

    Rango1 :Es el rango de celdas cuyos datos se desea evaluar.Criterio :Especifica el criterio (texto, expresin o nmro) que determinar las celdasdel rango que sern sumadas.Rango2: Son las celdas que se van a sumar. Las celdas del rango2 se suman slo silas celdas correspondientes del rango1 coinciden con el criterio. Si el rango2 se omite ,se sumarn las celdas contenidas en rango1.

    FUNCIONES ESTADSTICAS.

    MAX :Devuelve el mximo valor numrico de un rangoMAX(Rango)Rango : Es el bloque de celdas que contiene valores numricos, cuyo valor mximose desea determinar

  • 7/28/2019 EXCEL Manual Avanzado

    26/149

    Manual avanzado de excel

    104

    MIN :Devuelve el mnimo valor numrico de un rango.MIN(Rango)Rango : Es el bloque de celdas que contiene valores numricos, cuyo valor mximo

    se desea determinar

    MODA:Devuelve el valor que se repite con ms frecuencia en un rangoMODA(Rango)

    Rango . Es el bloque de celdas cuya moda se desea determinar.

    PROMEDIO:Devuelve el promedio (media aritmtica) de los valores numricos de unrango.PROMEDIO(Rango)Rango : Es el bloque de celdas cuyo promedio aritmticos se desea calcular

  • 7/28/2019 EXCEL Manual Avanzado

    27/149

    Manual avanzado de excel

    105

    CONTAR.Contabiliza slo los datos numricos que hay en un rangoCONTAR(Rango)Rango : Es el bloque de celdas, cuyas celdas con datos numricos se desean

    contabilizar.

    CONTARA:Contabiliza todos los datos que hay en un rango.CONATARA(Rango).Rango: Es el bloque de celdas, cuyas celdas con datos se desea contabilizar.

    FUNCIONES LGICASSI: Devuelve un valor si la exprsin es VERDADERO y otro valor si dicha expresin esFalso.SI(Expr.; Accin_V; Accin_F)

  • 7/28/2019 EXCEL Manual Avanzado

    28/149

    Manual avanzado de excel

    106

    Expr: Es una expresin que puede evaluarse como VERDADERO o FALSO. Accin_V :Es el valor que se devolver si la expresin es VERDADERO Accin_F : Es el valor que se devolver si la expresin es FALSO.

    Observacin:Es posible anidar hasta siete funciones SI para evaluaciones ms complejas.

    Ejemplo:Supongamos que desea calificar con letras los nmeros de referencia con el nombrePROM.Si PROM es la funcin devuelve.

    Mayor que 18 Excelente

    De 15 a 17 BuenoDe 11 a 14 RegularMenor que 11 Malo.

    Se podra utilizar la siguiente funcin anidada SI.=SI(prom>18; Excelente ;SI(prom>14; Bueno ;SI(prom>10; Regular; Malo )))

    Otro caso de usar SI anidados.

  • 7/28/2019 EXCEL Manual Avanzado

    29/149

  • 7/28/2019 EXCEL Manual Avanzado

    30/149

    Manual avanzado de excel

    108

    NO: Invierte el valor lgico del argumento.NO(Expr)Expr. Es una expresin lgica VERDADERO o FALSO. Si Expr es falso, no devuelve

    VERDADERO; si Expr es VERDADERO, No devuelve FALSO.

    FUNCIONES DE FECHAFecha: Devuelve la fecha especificada en valor de formato fecha

    FECHA(ao:mes:dia)

    HOY :Devuelve la fecha del sistema.HOY()

  • 7/28/2019 EXCEL Manual Avanzado

    31/149

    Manual avanzado de excel

    109

    AO: Devuelve el nmero del ao para una fecha dada.AO(Fecha)Fecha Es una fecha o direccin de celda.

    MES: Devuelve el nmeo del mes para una fecha dada.MES(fecha)

    Fecha Es una fecha o direccin de celda.

    DIA :Devuelve el nmero del da en el mes para una fecha dada.DIA(fecha)Fecha Es una fecha o direccin de celda.

    DIASEM:Devuelve el nmero del da de la semana para una fecha dada.DIASEM(Fecha:N)

  • 7/28/2019 EXCEL Manual Avanzado

    32/149

    Manual avanzado de excel

    110

    Fecha: Es una fecha o direccin de celdaN : Pueden ser 1,2. :

    FUNCIONES DE TEXTO.DERECHA:Devuelve N caracteres situados en el extremo derecho de una cadena detexto.DERECHA(TEXTO,N)

    TEXTO : Es la cadena de Caracteres.N : Especifica el nmero de caracteres que desea extraer.

    IZQUIERDA:Extrae N caracteres situados en el extremo izquierdo de una cadena detextoIZQUIERDA(Texto, N)

  • 7/28/2019 EXCEL Manual Avanzado

    33/149

    Manual avanzado de excel

    111

    Texto: Es la cadena de caracteresN : Especifica el nmero de caracteres que se desea extraer.

    EXTRAE :Extrae N Caracteres de una cadena de texto, comenzando en la posicinque se expecifique.EXTRAE(Texto,P,N)

    Texto: Es una cadena de CaracteresP : Es la Posicin a partir del cual se van a extraer N caracteres.N : Especifica el nmero de caracteres que se desea extraer.

    LARGO :Devuelve la longitud de una celda de textoLARGO(Texto)

  • 7/28/2019 EXCEL Manual Avanzado

    34/149

    Manual avanzado de excel

    112

    Texto: Es la cadena de caracteres cuya longitud se desea determinar. Los espaciostambin se cuentan como caracteres.

    TEXTO :Da formato a un nmero y lo convierte en texto. TEXTO convierte un valornumrico en texto con un formato numrico especifico.

    TEXTO(Valor; Formato)Valor : Es un nmero, celda o frmula que contenga un valor numricoFormato: Es un formato de nmero, en forma de texto, indicando en la ficha Nmerodel cuadro de dialogo Formato celdas.

  • 7/28/2019 EXCEL Manual Avanzado

    35/149

    Manual avanzado de excel

    113

    PREGUNTAS DE REPASO

    1.- En el programa de extensin profesional las secciones estn codificadas con 6

    caracteres. Por ejemplo 2345TC.

    Donde:1er Carcter representa el Turno

    2do Carcter representa el ciclo.

    Turno Horario ProgramaM1 08-10 TC TCNICO EN COMPUTACIN

    M2 10-12 TD TCNICO EN DISEO GRFICOM3 12-14 RN REDES NOVELL

    T1 15-17 T2 17-19 TN 19-21

    COMPLETAR EL SIGUIENTE CUADRO DE NOTAS:

  • 7/28/2019 EXCEL Manual Avanzado

    36/149

    Manual avanzado de excel

    114

    2.- Debido a un accidente automovilstico de un compaero de trabajo, se lleva a cabouna colecta voluntaria en el centro de trabajo donde labora para solventar losgastos de hospitalizacin. Obtener el total de aportes y completar el cuadro

    resumen de la siguiente tabla..

    3.- Se tiene en una tabla el nombre y la fecha de nacimiento de un grupo de personas,obtener.q El da y mes de nacimientoq El da de la semana de su cumpleaos en presente ao.q Su signo zodical.

  • 7/28/2019 EXCEL Manual Avanzado

    37/149

    Manual avanzado de excel

    115

    4.- Completar la siguiente plantilla de pagos.Especificaciones:

    TURNO SECCION BSICOM: MAANA 1 CAJ A 300

    T: TARDE 2 VENTAS 450N: NOCHE 3 ADMINISTRACIN 600

    4 VIGILANCIA 250

    BONIFICACIN:Si turno es Noche y Seccin es Vigilancia, 15% del Bsico; en casocontrario ser cero.DESCUENTO :Es el 18% del (Bsico+Bonificacin)NETO : Es el Bsico +Bonificacin Descuento.

    5.- Se tiene un Monto en soles y se desea desglosarlo de la siguiente manera.Billetes de S/. 100Billetes de S/. 50

    Billetes de S/. 20Billetes de S/. 10Monedas de S/. 5Monedas de S/. 2Monedas de S/. 1

  • 7/28/2019 EXCEL Manual Avanzado

    38/149

    Manual avanzado de excel

    116

    Capitulo III

    Escenarios, esquemas y vistas

    ESCENARIOS

    Administrador de Escenarios

    Se denomina escenario a un grupo de variables llamadas celdas cambiantes, que

    producen unos resultados diferentes y se guardan con el nombre deseado.

    Cada conjunto de celdas cambiantes representa un grupo de supuestos que se

    aplica a la hoja de clculo, con objeto de obtener unos resultados concretos. Se pueden

    definir hasta un mximo de 32 series de celdas cambiantes para cada escenario creado

    en un hoja de clculo.

    Los resultados obtenidos de todas las variables sirven para crear un informe de

    resumen en que aparezca el mejor caso, el peor caso y el caso previsto del problema

    planteado en la hoja. Tambin podr combinar escenarios de un grupo en un solo estilo

    y protegerlos u ocultarlos de posibles usuarios no deseados.

    Crear un Escenario

    Para crear un escenario se debe seguir el proceso siguiente :

    Activar el comando Escenarios del men Herramientas,

  • 7/28/2019 EXCEL Manual Avanzado

    39/149

    Manual avanzado de excel

    117

    Aparecer el cuadro de dilogo Administrador de Escenarios que semuestra a continuacin :

    El primer paso es crear un nuevo Escenario, para esto se hace un clic en el botn

    Agregar y aparecer el siguiente cuadro de dilogo :

  • 7/28/2019 EXCEL Manual Avanzado

    40/149

    Manual avanzado de excel

    118

    En este cuadro se debe escribir un nombre para el Escenario que se vaa crear, en este caso se escogi VENTAS.

    En el cuadro Celdas Cambiantes, introducir las referencias o los

    nombres definidos de las celdas cambiantes (que se desean modificar). Si se escribe ms de una referencia estas deben separarse con unpunto y coma (;).

    Tambin se pueden seleccionar las celdas directamente con el punterodel mouse.

    NOTA :Si se va a seleccionar celdas o rangos no adyacentes, se debe oprimir la teclaControl y mantenerse oprimida mientras se las marca con el puntero del mouse.

    En el cuadro Comentarios, se puede introducir un breve comentariodescriptivo por cada Escenario creado.

    Dar un clic en el botn Aceptar :

    Automticamente aparecer el cuadro de dilogo: Valores delEscenario , en que se visualizarn las variables actuales quecorresponden a las celdas cambiantes seleccionadas. Si este contienems de cinco celdas cambiantes aparecer una barra dedesplazamiento situada a la derecha de los cuadros de edicin, comose muestra a continuacin. En nuestro ejemplo hemos escogido comorango de las celdas cambiantes los valores de las ventas realizadas.

    A continuacin se debe introducir los valores deseados y dar un clicken el botn Aceptar para regresar al cuadro de dilogo Administradorde Escenarios , donde se aadir el escenario recin creado a la lista

    de escenarios.

    Para terminar y cerrar el cuadro, pulse el botn Cerrar, o bien, si desea visualizar losresultados en la hoja, pulse el botn mostrar o haga doble clic sobre el nombre delescenario creado en el cuadro Escenarios.

  • 7/28/2019 EXCEL Manual Avanzado

    41/149

    Manual avanzado de excel

    119

  • 7/28/2019 EXCEL Manual Avanzado

    42/149

    Manual avanzado de excel

    120

    Eliminar un escenario

    Cuando se elimine un escenario debe recordar que no puede deshacer esta eliminacin.

    Proceso de eliminar un escenario

    1. Activar el comando Escenarios del men Herramientas

    2. En el cuadro de dilogo Administrador de Escenarios , elegir elescenario que se desea eliminar de la lista del cuadro Escenarios ypulsar el botn Eliminar .

    Automticamente se borrar el escenario de la l ista y ser irrecuperable, a no ser que se vuelva a

    crearlo.

    Si se quiere proteger el escenario, se deber activar la casilla de

    proteccin Evitar cambios (evitar editar la hoja del escenario) y Ocultar (evita la presentacin del escenario). A continuacin deber activar laproteccin de la hoja activando el comando Proteger del menHerramientas y, a continuacin, Proteger hoja (comprobar que lacasilla Escenarios est activada).

    Editar un escenario

    El comando Modificar del Cuadro de dilogo Administrador de escenarios permite modificar el nombre del escenario y las referencias cambiantes del mismo.

    Proceso de editar un escenario :

  • 7/28/2019 EXCEL Manual Avanzado

    43/149

    Manual avanzado de excel

    121

    1. Activar el comando Escenarios del men Herramientas.2. Aparecer el cuadro de dilogo Administrador de escenarios 3. Pulsar el botn Modificar .

    4. Se mostrar un cuadro de dilogoModificar escenario , que muestra acontinuacin :

    A continuacin se debe modificar las opciones deseadas del escenario. Si conserva el

    nombre original del escenario, los nuevos valores de las celdas cambiantes introducidos

    sustituirn a los valores del escenario original.

    Para terminar y validar las opciones, pulsar el botn Aceptar . Tambin podr modificarlos valores del cuadro de dilogo Valores del escenario para las celdas cambiantes.Si desea volver al Administrador de escenarios sin modificar el escenario actual, pulseel botn Cancelar .

  • 7/28/2019 EXCEL Manual Avanzado

    44/149

    Manual avanzado de excel

    122

  • 7/28/2019 EXCEL Manual Avanzado

    45/149

    Manual avanzado de excel

    123

    Combinar escenarios

    Se puede combinar un escenario creado en la hoja activa con otro que estsituado en un libro de trabajo que previamente est abierto. Es muy posible que alcombinar ambos escenarios existan nombres duplicados, se debe evitarlo, pues habraconflicto entre los distintos escenarios creados.

    Proceso de combinar un escenario

    1. Activar el comando Escenarios del men Herramientas.2. En el cuadro de dilogo Administrador de escenarios que aparecer

    pulsar el botn Combinar .3. Aparecer el cuadro de dilogo Combinar escenarios, que se muestra

    a continuacin :

    En el cuadro Libro, escoger el libro de trabajo deseado con el cualvamos a combinar el escenario.

    En el cuadro Hoja, seleccionar el nombre de las hojas que contienenlos escenarios para combinar. En la parte inferior del cuadro se indicael nmero de escenarios que existen en las hojas seleccionadas. Todaslas celdas cambiantes en le hoja de clculo de origen debern hacerreferencia a las celdas cambiantes en la hoja de clculo activa.

  • 7/28/2019 EXCEL Manual Avanzado

    46/149

    Manual avanzado de excel

    124

    4. Para terminar, pulsar el botn Aceptar. Se cerrar el cuadro de dilogoCombinar escenarios a la vez que combinar los escenarios,volviendo al cuadro de dilogo Administrador de escenarios.

    5. Pulsar el botn cerrar para salir del cuadro de dilogo. De esta forma Excelcopiar todos los escenarios en las hojas de clculo origen en la hoja de clculo

    activa.

    Crear un informe de resumen de escenarios

    Mediante el Administrador de escenarios usted podr crear informes de resumen de

    escenarios o tablas dinmicas con los valores de las celdas cambiantes de hoja de clculo.

    Proceso para crear un escenario

    1. Activar el comando Escenarios del men Herramientas.2. Aparecer el cuadro de dilogo Administrador de escenarios .3. Elegir el escenario creado de la lista del cuadro Escenario. Por ejemplo, el

    escenario VENTAS.

    4. Pulsar el botn Resumen (estar disponible si existen escenarioscreados).

    5. Aparecer el cuadro de dilogo Resumen del escenario , que semuestra a continuacin :

  • 7/28/2019 EXCEL Manual Avanzado

    47/149

    Manual avanzado de excel

    125

    6. Elegir las celdas resultantes en dicho cuadro (por ejemplo, $C$4:$c$9 que son

    los precios en nuestra hoja) y, a continuacin pulsar el botn Aceptar .

    Automticamente se crear un informe de resumen o una tabla dinmica, segn laopcin elegida del cuadro. Este se crear en una hoja de clculo diferente del mismolibro de trabajo y se le asignar el nombre en la etiqueta Resumen escenario o Tabladinmica del escenario. ElResumen del escenario se muestra a continuacin :

    NOTA : Las celdas cambiantes resultantes son opcionales en los informes deresumen, pero obligatorias en los informes de tablas dinmicas.

  • 7/28/2019 EXCEL Manual Avanzado

    48/149

    Manual avanzado de excel

    126

    EJERCICIOS

    Se piden 3 presupuestos para la confeccin de una biblioteca a medida. El precioque se va a pagar tiene tres componentes :

    Los materiales. Principalmente la madera. La pintura, barniz o acabado. La mano de obra.

    El presupuesto que resulta de estas variables para un proponentes aparece en la

    planilla siguiente:

    Mueblera Corvaln.

    La frmula en D6 calcula el total de la biblioteca.

    Los valores de B2 y B3 son una caracterstica de la biblioteca y no dependen delos materiales, el acabado o la mano de obra. Los valores de C2, C3 y D4, en cambiovaran con cada presupuesto.

    Los valores de D2 y D3 se calculan multiplicando el rubro Cantidad por susrespectivo Precio Unitario, mientras que en D6 hay una sumatoria que da el preciofinal. No es una planilla compleja.

  • 7/28/2019 EXCEL Manual Avanzado

    49/149

    Manual avanzado de excel

    127

    Lo que complica el manejo del problema es que, en principio, tenemos unaplanilla como la de arriba por cada presupuesto que obtengamos, segn el tipo demadera, el acabado y la mano de obra. Por ejemplo, la planilla siguiente es igual a la

    anterior, pero para un trabajo de mayor calidad.

    Muebles Providencia

    La planilla siguiente es una firma llamada Muebles de Lujo, que son de una calidadsuperior a los anteriores.

    Muebles de lujo.

  • 7/28/2019 EXCEL Manual Avanzado

    50/149

    Manual avanzado de excel

    128

    Lo que se est buscando es una forma sencilla de comparar todos lospresupuestos recibidos para decidir con cual nos quedamos.

    Para esto podemos usar los escenarios .

    Una planilla como cualquiera de las que se vieron ms arriba, brinda distintosresultados segn los valores de sus datos. Para cada juego de datos hay un resultado(o juego de resultados) diferente. La planilla que resulta para cada juego de datos esun escenario. En otras palabras las planillas de ms arriba muestran distintosescenarios de la misma planilla.

    Las celdas que contienen los datos variables con cada escenario (en el ejemplo

    C2, C3 y D4) se denominan celdas cambiantes . La celda D6, que contiene elresultado final, se llama celda resultante . Hay que conocer previamente estosnombres, porque son los que usa la opcin al trabajar.

    Crear escenarios

    Podemos empezar con los valores que aparecen en la Planilla N 1. Para esteejemplo este presupuesto fue presentado por Mueblera Corvaln, y corresponde amadera de pino con acabado en barniz nacional importado, entonces:

    Se abren las opciones Herramientas/Escenarios . Aparece entonces el cuadro Figurasiguiente, que dice que no hay ningn escenario definido, de modo que hay quecrearlos.

  • 7/28/2019 EXCEL Manual Avanzado

    51/149

    Manual avanzado de excel

    129

    El cuadro principal para el manejo de escenarios. Todava no hay ningn escenariodefinido. Por ello, hay dar un clic en Agregar .

    1. Se hace un clic en Agregar. Aparece el cuadro de la Figura siguiente, donde seindican las caractersticas del escenario que se est creando.

    Aqu se define el escenario: su nombre, sus celdas cambiantes y algn comentarioadecuado .

  • 7/28/2019 EXCEL Manual Avanzado

    52/149

    Manual avanzado de excel

    130

    1) Donde dice nombre del escenario se escribe un nombre adecuado. Por ejemplo :Mueblera Corvaln.

    2) Donde dice celdas cambiantes , indicamos $C$2;$C$3;$D$4. Se separan conpunto y coma. Tambin las podemos seleccionar con el mouse manteniendoapretada la tecla Control al seleccionar celdas no contiguas.

    3) Donde dice Comentario, se escribe alguna aclaracin apropiada. Por ejemplo:Madera de pino c on barniz nacional.

    4) Se da un clic en Aceptar . Entonces aparece el cuadro de dilogo de la Figura dems arriba, donde se indican los valores de las celdas cambiantes para esteescenario. En principio, Excel adopta los valores actuales, lo cual es correcto.

    Lo mismo se hace para los escenarios restantes y a continuacin se muestran loscuadros respectivos con los escenarios creados.

    Resumen de los escenarios creados.

    Estos escenarios corresponden a los siguientes cuadros:

  • 7/28/2019 EXCEL Manual Avanzado

    53/149

    Manual avanzado de excel

    131

  • 7/28/2019 EXCEL Manual Avanzado

    54/149

    Manual avanzado de excel

    132

    Escenario de Muebles Providencia.

    Escenario de Muebles de Lujo.

    Estos tres escenarios estn resumidos en el cuadro que est ms arriba y que serepite a continuacin:

    Cuadro resumen de los tresescenarios.

  • 7/28/2019 EXCEL Manual Avanzado

    55/149

    Manual avanzado de excel

    133

    Paso de un escenario a o tro

    Como aparece en borde superior de la que est ms arriba. El cuadro dedilogo es el Administ rador de escenarios . Marcando el escenario que se desea very dando un clic en el botn Modificar , aparece el escenario que se desea ver.

    Resmenes

    La opcin de escenarios permite algo ms: armar una tabla que resuma lainformacin de todos los escenarios disponibles. Esto se hace de la siguiente forma :

    1. Se abre Herramientas / Escenarios para obtener el cuadro de laFigura de m arriba.

    2. Se hace un clic en Resumen . Aparece el cuadro de la Figura , que no da dosopciones

    3. Se marca la opcin Resumen .4. Donde dice Celdas resultantes , se indica D6.5. Se da un clic en Aceptar .

    En este cuadro de dilogo se especifican las caractersticas del resumen quequeremos obtener.

    En una hoja aparte del mismo libro aparece el resumen indicado como el quese muestra en la Figura que va a continuacin.

  • 7/28/2019 EXCEL Manual Avanzado

    56/149

    Manual avanzado de excel

    134

    El resumen con los datos de todos lo s escenarios disponibles .

    Esta tabla es fcil de comprender:

    Hay una columna (vertical) por cada escenario disponible, adems hayuna columna adicional para el escenario actual.

    Horizontalmente hay tres grupos de datos : una fila para lasdescripciones de los escenarios, una fila para cada celda cambiante y

    otra fila por cada celda resultante.

    Para hacer ms clara esta tabla resumen, es posible modificar los rtulos enalgunas celdas. Por ejemplo : la tabla de la Figura siguiente tiene los mismos valoresque la tabla de la Figura anterior , pero es un poco ms fcil de entender.

  • 7/28/2019 EXCEL Manual Avanzado

    57/149

    Manual avanzado de excel

    135

    El mismo resumen de la Figura de ms arriba , pero modificando algunos ttulos parahacerlo ms claro.

    Contraccin o expansin del resumen

    El resumen muestra tambin unos botones de comando sobre el borde izquierdode la planilla. Estos botones permiten expandir o contraer los grupos que semencionaban. Estos botones muestran un signo menos cuando el grupo estexpandido y visible, y un signo ms cuando el grupo est contrado y oculto.

    Estos resmenes no son dinmicos : no se actualizan al modificar la planilla ni alagregar, eliminar o modificar escenarios.

    Eliminar el resumen

    El Resumen del Escenario se borra eliminando la hoja que lo contiene. Estaoperacin no se puede revertir con la opcin Deshacer . Por eso al eliminarla aparece

    un cuadro de dilogo con la advertencia indicada en este prrafo.

    Resumen tipo tabla dinmica

    El cuadro de la Figura pequea de ms arriba muestra una segunda opcinpara el resumen : tabla dinmica. Marcando esta opcin obtenemos un resumen comoel que se muestra en la Figura siguiente.:Un resumen tipo tabla dinmica.

  • 7/28/2019 EXCEL Manual Avanzado

    58/149

    Manual avanzado de excel

    136

  • 7/28/2019 EXCEL Manual Avanzado

    59/149

    Manual avanzado de excel

    137

    ESQUEMAS Y VISTAS

    Definicin de Esquemas y Vistas En Excel , el uso de esquemas permite expandir o contraer la apariencia de una hojade clculo, de forma que la informacin se pueda ver con ms o menos detalle. En lafigura se muestra un ejemplo de tabla con totales absolutos y por meses. En la figurase muestra la misma tabla, con dos niveles de esquema por columnas y uno por filas.En la figura se ha contrado el nivel de esquema correspondiente a los trimestres.

    Figura . Ejemplo de tabla sin esquemas.

    Excel puede crear un esquema de modo automtico. Para ello busca celdas confrmulas que sean un resumen de las filas por encima o bien de las columnas a laizquierda.

    El esquema de la figura ha sido obtenido de este modo por medio del comando Datos/ Agrupar y Esquema / Autoesquema

    Un esquema en Excel puede contener hasta ocho niveles de filas y columnas y sepuede colocar en cualquier parte de la hoja de clculo.

    Cuando se muestra un esquema, los smbolos necesarios para contraer o expandir

    (pequeos botones con nmeros y con signos ms (+) y menos(-)) se presentan en unas

    barras especiales situadas en la parte superior e izquierda de la hoja de clculo que

  • 7/28/2019 EXCEL Manual Avanzado

    60/149

    Manual avanzado de excel

    138

    contiene dicho esquema . Estos smbolos permiten ocultar o mostrar los diferentes

    niveles del esquema, para poder mostrar ms o menos informacin.

    Con estos botones se contrae o expande la informacin del esquema. Paracomprender bien como funcionan estos esquemas lo mejor es practicar con ejemplossencillos.

    Figura . Contraccin de un nivel de columnas en el esquema de la figura.

    Figura . Esquemas en la tabla de la figura .

  • 7/28/2019 EXCEL Manual Avanzado

    61/149

    Manual avanzado de excel

    139

    Creacin y borrado de un esquema

    Hay dos formas de crear esquemas: una -ya citada- es la creacin automtica porparte de Excel y otra la creacin manual por parte del usuario.

    La creacin automtica de esquemas funciona bien en la mayora de los casos y es laforma ms simple de crear esquemas.

    La creacin manual es necesaria en el caso de que los datos estn organizados enuna forma tal que Excel no sea capaz de entenderlos correctamente. Si ya se tieneexperiencia anterior en la creacin de esquemas, la creacin manual permite tambinuna mayor flexibilidad a la hora de definir el esquema.

    Antes de usar la capacidad de Excel para crear esquemas automticamente, hay quecomprobar cmo se definen las celdas que contienen el resumen con respecto al resto

    de celdas que contienen los detalles. Todo ello debe ser coherente: por defecto lasceldas resumen en filas se deben referir a celdas con detalles situadas a su izquierda,mientras que las celdas resumen de columnas deben referirse a celdas con detallessituadas por encima.

    Esta condicin puede cambiarse con el comando Datos / Agrupar y Esquema /Configurar , que abre el cuadro de dilogo de la figura.

  • 7/28/2019 EXCEL Manual Avanzado

    62/149

    Manual avanzado de excel

    140

    Figura . Comando Datos / Agrupar y Esquema / Autoesquema.

    Para crear de modo automtico un esquema en una hoja de clculo, se pueden seguirlos siguientes pasos:

    1. Seleccionar el rango de celdas sobre el que quiere generar el esquema. Si se tratade la hoja de clculo al completo, basta seleccionar nicamente una celda.

    2. Seleccionar el comando Datos / Agrupar y Esquema / Autoesquema.

    Para eliminar un esquema de modo automtico basta seleccionar el comando Datos / Agrupar y Esquema / Borrar Esquema.

    Si se desea crear el esquema de forma manual, se puede proceder como se indica a continuacin.

    Para agrupar un conjunto de filas o de columnas en un nuevo nivel de esquema, hayque dar los pasos siguientes:

    1. Seleccionar las filas o columnas que desea agrupar bajo o a la izquierda de la fila ocolumna resumen.

    No se deben incluir en la seleccin las filas o columnas que contienen las frmulas deresumen.

    2. Elegir el comando Datos / Agrupar y Esquema / Agrupar . Esto mismo se puedeconseguirclicando en el botn Agrupar ( ).

    Para eliminar un nivel de esquema debe procederse en sentido opuesto: seseleccionan las filas o columnas con la informacin detallada y se elige el comandoDatos / Agrupar y Esquema / Desagrupar o se clica sobre el botn Desagrupar ( ). Es

  • 7/28/2019 EXCEL Manual Avanzado

    63/149

    Manual avanzado de excel

    141

    posible que los botones Agrupar y Desagrupar no se encuentren en la barra deherramientas Estndar y que haya que aadirlos; esto se hace por medio del comandoInsertar del men contextual de barras de herramientas. Los botones Agrupar y

    Desagrupar estn en la categora de botones Dato.

    Figura . Formas de orientar la creacin de lneas-resumen en Esquema. ..

    Visualizacin de un esquema

    La verdadera utilidad de los esquemas reside en la posibilidad de expandir y contraerla informacin mostrada en la hoja de clculo, para trabajar en cada momento con elnivel de detalle que sea necesario.

    Para manejar los diferentes niveles de detalle, se pueden seguir los pasos que acontinuacin se indican:

    1. Seleccionar una celda en la fila o columna resumen que se quiera mostrar uocultar.

    2. Ejecutar el comando Datos / Agrupar y Esquema / Ocultar o Mostrar Detalles.Estomismo puede hacerse por medio de los pequeos botones con nmeros o consignos (+) o (-) que aparecen en las barras situadas encima y a la izquierda de la

    hoja de clculo.

    Por supuesto cuando un esquema tiene detalles ocultos, la hoja de clculo sigueconteniendo la misma informacin, aunque a diferentes niveles. Se pueden creargrficos de slo los datos visibles de un esquema o con todos los datos de la hoja. Sepuede indicar a Excel que use slo los datos visibles o bien todos los datos -incluidoslos no visibles- a la hora de confeccionar un grfico.

  • 7/28/2019 EXCEL Manual Avanzado

    64/149

    Manual avanzado de excel

    142

    Para que por defecto se emplee una u otra de estas dos opciones, hay que seguir lospasos siguientes:

    1. Crear un grfico en la propia hoja y clicar dos veces sobre l, de forma que losmens de grficos correspondientes estn accesibles.

    2. Elegir el comando Formato/ Opciones.3. Seleccionar o deseleccionar la opcin Solo celdas visibles. En la mayora de los

    casos, esta opcin estar ya seleccionada4. Hacer clic en OK.

    Para determinar manualmente que slo se quiere trabajar con las celdas visibles, se

    debe mostrar primeramente el esquema de manera que contenga los niveles dedetalle y resumen que sean necesarios.

    Posteriormente hay que seleccionar las celdas con las que quiere trabajar y elegir elcomandoEdicin / Ir a... / Especial; en el cuadro de dilogo resultante seleccionar la opcin Soloceldasvisibles y hacer clic en OKpara concluir.

    Creacin y Gestin de Vistas

    Las Vistas son distintas formas que tiene Excel de ver o presentar una nicainformacin contenida en una hoja de clculo. Por ejemplo, distintas vistas pueden

    tener un outlinecon distintas filas y/o columnas expandidas u ocultas.

    Las vistas de Excel se crean y se gestionan con el comando Vistas personalizadas ovistaprevia, en el men Ver . Este generador de vistas ( View Manager ) es un aadido deExcel, lo cual quiere decir que no se instala por defecto, sino que hay que instalarlocuando se desee utilizar. Para ms informacin sobre las Vistas, consultar el Ayuda.

  • 7/28/2019 EXCEL Manual Avanzado

    65/149

    Manual avanzado de excel

    143

  • 7/28/2019 EXCEL Manual Avanzado

    66/149

    Manual avanzado de excel

    144

    Capitulo IV

    LISTASIntroduccin.-

    En Microsoft Excel, puede utilizarse fcilmente una lista como una base de datos.Cuando se ejecutan tareas en la base de datos, como bsquedas, clasificaciones odatos subtotales, Microsoft Excel reconoce automticamente la lista como una base dedatos y utiliza los siguientes elementos de la lista para organizar los datos.

    Las columnas de la lista son los campos en la base de datos. Por ejemplo losdatos que se muestran en la columna (id de pedido) es un campo

    Los rtulos de las columnas de la lista son los nombres de los campos en labase de datos. . Son los valores que estan desde la celda (A1:G1)

    Cada fila de la lista es un registro en la base de datos.. La lista de pedidos estacompuesta por 20 registro para nuestro caso desde la fil a 2 hasta la fil a 21.

    FORMULARIOS DE DATOS.

    Un formulario de datos es un cuadro de dilogo que permite al usuario introducir omostrar con facilidad una fila entera de informacin ( registro ) en una lista de una solavez. Tambin se pueden usar formularios de datos para ubicar y eliminar registros.

    Fi ura 4.1 Ho a de Pedidos

  • 7/28/2019 EXCEL Manual Avanzado

    67/149

    Manual avanzado de excel

    145

    Antes de utilizar un formulario de datos para agregar un registro a una lista nueva, stadeber tener rtulos en la parte superior de cada columna que contenga. MicrosoftExcel utiliza estos rtulos para crear campos en el formulario.

    La tarea previa debera ingresar algunos registros como se muestra en la figura 4.1.

    Para mostrar el formulario de datos, proceda de esta forma:

    1. Seleccione alguna de las celdas de los datos ingresados .2. Seleccione la opcin Datos y haga clic en Formulario. 3. Se muestra el siguiente formulario.

    En el formulario presentado en la figura 4.2 nosotros podemos realizar tareas como

    adicionar registros, eliminar o realizar una bsqueda.

    Para realizar una bsqueda en especial tendr que auxiliarse con el botn criterios,por ejemplo si usted desea ver los precios por unidad superiores a 60.

    Figura 4.2 Formulario de la Hoja de Pedidos

  • 7/28/2019 EXCEL Manual Avanzado

    68/149

    Manual avanzado de excel

    146

    Haga clic en el botn criterios, luego escriba en el campo en blanco correspondiente elcriterio a aplicar para nuestro caso nos ubicamos en el campo Precio por Unidad yescribimos >60, luego utilizar el botn buscar anterior o siguiente.

    Para salir del formulario de clic en el botn cerrar.

    ORDENAR LISTAS.

    Para ordenar una lista se hace en excel se hace en funcin de una columna o campode la lista y se puede hacer de manera ascendente o descendente.

    Para ordenar una lista primeramente usted puede ubicarse en alguna de las celdas de

    la columna que desea ordenar y luego elija en el menu datos la opcin de ordenar como se muestra en la figura 4.3

    Los encabezados de las columnas son utilizados para realizar la ordenacin.

    Se puede elegir ms de un criterio de ordenacin por ejemplo por Nombre (Es elnombre del vendedor) y por Precio por Unidad.

    Figura 4.3 Cuadro de Dilogo de Ordenar

  • 7/28/2019 EXCEL Manual Avanzado

    69/149

    Manual avanzado de excel

    147

    El resultado lo podemos observar en la figura

    SUBTOTALESNosotros con Excel podemos resumir datos calculando valores de subtotales y detotales de una lista.Se devuelve un subtotal en una lista o base de datos. Generalmente es ms fcil crear

    una lista con subtotales utilizando el comando SubTotales del men Datos.

    CONDICIONES PARA APLICAR SUBTOTALES

    Para usar Subtotales automticamente, la lista debe contener columnas rotuladas ydebe estar ordenada por las columnas que desea calcular los subtotales.

    APLICAR SUBTOTALES

    Cuando se inserta subtotales automticos, excel esquematiza la lista agrupando lasfilas con detalle con la fila subtotal asociada y agrupando las filas de subtotales con lafila del total general.

    CALCULADO EL SUBTOTAL POR CATEGORA

    1. Como primer paso ordenamos nuestra lista por el campo categoras . Como seindica en la figura. 4.5

    Figura 4.4 Lista ordenada por Nombre y Precio por Unidad

  • 7/28/2019 EXCEL Manual Avanzado

    70/149

  • 7/28/2019 EXCEL Manual Avanzado

    71/149

  • 7/28/2019 EXCEL Manual Avanzado

    72/149

    Manual avanzado de excel

    150

    FILTROS

    Aplicar filtros es una forma rpida y fcil de buscar y trabajar con un subconjunto dedatos de una lista. Una lista filtrada muestra slo las filas que cumplen el criterio quese especifique para una columna. Microsoft Excel proporciona dos comandos paraaplicar filtros a las listas:

    AUTOFILTROusado para criterios simples.

    FILTRO AVANZADO, para criterios ms complejos.

    A diferencia de ordenar, el filtrado no organiza las listas. El filtrado ocultatemporalmente las filas que no desee mostrar.Solo puede aplicar filtros a una lista de una hoja de clculo a la vez.

    Aplicaremos un autofiltro en la hoja pedidos.

    1. Haga clic en la celda de la lista que desee filtrar2. En el men Datos, seleccione Filtro y haga clic en Autofiltro. Debe obtener lo

    siguientes.

    Figura 4.8 Autofiltro Aplicado a la Lista

  • 7/28/2019 EXCEL Manual Avanzado

    73/149

    Manual avanzado de excel

    151

    3. Para presentar slo las filas que contienen un valor especifico, haga clic en laflecha de la columna que contiene los datos que desee presentar.

    4. Haga clic en el valor. Laura del campo Nombre.

    5. Debe obtener lo siguiente:

    Figura 4.9 Autofiltro Aplicado al Nombre Laura

    Figura 4.10 Resultado del Autofiltro al Nombre Laura

  • 7/28/2019 EXCEL Manual Avanzado

    74/149

    Manual avanzado de excel

    152

    RETIRAR FILTROS.q Para quitar un filtro de una columna de lista, haga clic en la flecha situada junto

    a la columna y despus en todos. q Para quitar filtros aplicados a todas las columnas de la lista, seleccione Filtro

    en el men Datos y haga clic en Mostrar todo.q Para quitar las flechas de filtro de una lista, seleccione Filtro en el men

    Datos y haga clic en Autofi lt ro .

    Retire el autofiltroantes aplicado.

    AUTOFILTRO PERSONALIZADO (O, Y) .

    Puede utilizar autofiltro personalizado para mostrar filas que contengan un valor u otro. Tambin puede utilizar un autofiltro personalizado para mostrar las filas que cumplan

    ms de una condicin en una columna, por ejemplo, las filas que contengas productospedidos mayores que $30 y menores iguales de $65.

    1. Seleccione Personalizar, tal como se muestra:

    Figura 4.11 Autofiltro Personalizado

  • 7/28/2019 EXCEL Manual Avanzado

    75/149

    Manual avanzado de excel

    153

    2. Se presenta el siguiente cuadro de dilogo, complete las condiciones tal como seindica. Haga clic en los botones de los cuadros combinados y seleccione el valor oescriba el precio.

    3.- Haga clic en el botn Aceptar y debe obtener los siguientes datos:

    Siguiendo el mismo procedimiento podemos mostrar los pedidos correspondientes aLaura o Nancy.

    Figura 4.12 Configuracin de filtro personalizado

    Figura 4.13

  • 7/28/2019 EXCEL Manual Avanzado

    76/149

    Manual avanzado de excel

    154

    Capitulo V

    Funciones de bsquedaIntroduccin.-

    Las funciones de bsqueda nos permiten realizar bsquedas en una matriz dereferencia en funcin de un parmetro de bsqueda.

    Imagine el siguiente caso donde usted tiene que extraer en funcin de la categora deun trabajador su sueldo bsico.

    .

    Entonces nuestro objetivo en un primer momento es escribir una frmula que hagan

    posible ver en la celda E9 el bsico del trabajador de acuerdo a la tabla propuesta en la

    parte superior.

    Recomendamos antes de todo asignarle un nombre al rango de celdas donde se

    encuentran los datos.1. Seleccione el rango de celdas (B3:E6) 2. Haga clic en el cuadro de nombres y escriba CLASIFICACIN.3. Pulse .

  • 7/28/2019 EXCEL Manual Avanzado

    77/149

    Manual avanzado de excel

    155

    Cuando usamos celdas con nombres hacemos referencias absolutas para la tabla, lo cual

    implica que no tendr inconveniente cuando copie la frmula si se aumentas filas o

    columnas.

    Para escribir la frmula debe ubicarse en la celda E9.

    Sintaxis de la Funcin BUSCARV()

    BUSCARV(valor_buscado ;matriz_de_comparacin;indicador_columnas;ordenado)

    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.

    Matriz_de_comparacin es el conjunto de informacin donde se buscan los datos.Utilice una referencia a un rango o un nombre de rango, como por ejemploBase_de_datos o Lista.

    Si el argumento ordenado es VERDADERO, los valores de la primera columnadel argumento matriz_de_comparacin deben colocarse en orden ascendente:...; -2; -1; 0; 1; 2; ... ; A-Z; FALSO; VERDADERO. De lo contrario, BUSCARVpodra devolver un valor incorrecto.

    Para colocar los valores en orden ascendente, elija el comando Ordenar delmen Datos y seleccione la opcin Ascendente .

    Los valores de la primera columna de matriz_de_comparacin pueden sertexto, nmeros o valores lgicos.

    El texto escrito en maysculas y minsculas es equivalente.

    Indicador_columnas es el nmero de columna de matriz_de_comparacin desde lacual debe devolverse el valor coincidente. Si el argumento indicador_columnas esigual a 1, la funcin devuelve el valor de la primera columna del argumentomatriz_de_comparacin; si el argumento indicador_columnas es igual a 2, devuelve elvalor de la segunda columna de matriz_de_comparacin y as sucesivamente. Siindicador_columnas es menor que 1, BUSCARV devuelve el valor de error #VALOR!;si indicador_columnas es mayor que el nmero de columnas dematriz_de_comparacin, BUSCARV devuelve el valor de error #REF!

  • 7/28/2019 EXCEL Manual Avanzado

    78/149

    Manual avanzado de excel

    156

    Ordenado Es un valor lgico que indica si desea que la funcin BUSCARV busqueun valor igual o aproximado al valor especificado. Si el argumento ordenado es

    VERDADERO o se omite, la funcin devuelve un valor aproximado, es decir, si noencuentra un valor exacto, devolver el valor inmediatamente menor quevalor_buscado. Si ordenado es FALSO, BUSCARV devuelve el valor buscado. Si noencuentra ningn valor, devuelve el valor de error #N/A.

    Regresando a nuestro ejemplo:

    q Valor Buscado. Indica que valor se buscar en la tabla. La bsquedasolamente se realiza en la primera columna de la matriz de bsqueda, raznpor la cual la funcin su denominacin BUSCARV. El argumento de bsqueda

    se recomienda hacerlo en lo posible referenciando la celda donde se encuentrael valor que se desea buscar. En nuestro ejemplo sera la celda (D9).

    q Matriz de Comparacin. Indica el rango donde se encuentran los datos. Paranuestro ejemplo hemos definido ese rango con el nombre de CLASIFICACIN.

    q Indicador Columnas. Indica el nmero de la columna de la matriz quecontiene el valor que desea mostrar. Por ejemplo en la Matriz CLASIFICACINse desea mostrar el BASICO entonces se escribe la columna 2 en esteargumento y si desea mostrar porcentaje de incentivo mostrara la columna 3.

    q Ordenado . Se utiliza para indicar si usted desea que se considere valoresaproximados al devolver el resultado. Los nicos valores que se puedenescribir en este argumento VERDADEROy FALSO .

  • 7/28/2019 EXCEL Manual Avanzado

    79/149

  • 7/28/2019 EXCEL Manual Avanzado

    80/149

    Manual avanzado de excel

    158

    finalmente despus de haber copiado las frmulas debemos obtener unosresultados similares a los de la Figura 5.3 como la

    BUSCARH (Buscar Horizontal)

    Busca un valor en la fila superior de una tabla o una matriz de valores y, acontinuacin, devuelve un valor en la misma columna de una fila especificada en latabla o en la matriz. Use BUSCARH cuando los valores de comparacin se encuentrenen una fila en la parte superior de una tabla de datos y desee encontrar informacinque se encuentre dentro de un nmero especificado de filas. Use BUSCARV cuandolos valores de comparacin se encuentren en una columna a la izquierda o de losdatos que desee encontrar.

    Sintaxis BUSCARH(valor_buscado;matriz_buscar_en ;indicador_filas; ordenado)

    Valor_buscado : es el valor que se busca en la primera fila de matriz_buscar_en.Valor_buscado puede ser un valor, una referencia o una cadena de texto.

  • 7/28/2019 EXCEL Manual Avanzado

    81/149

    Manual avanzado de excel

    159

    Matriz_buscar_en : es una tabla de informacin en la que se buscan los datos.Utilice una referencia a un rango o el nombre de un rango.

    Los valores de la primera fila del argumento matriz_buscar_en pueden sertexto, nmeros o valores lgicos.

    Si el argumento ordenado es VERDADERO, los valores de la primera fila delargumento matriz_buscar_en debern colocarse en orden ascendente: ...-2; -1;0; 1; 2;..., A-Z, FALSO, VERDADERO; de lo contrario, es posible queBUSCARH no devuelva el valor correcto.

    El texto en maysculas y minsculas es equivalente.

    Se pueden poner los datos en orden ascendente de izquierda a derechaseleccionando los valores y eligiendo el comando Ordenar del men Datos. Acontinuacin haga clic en Opciones y despus en Ordenar de izquierda aderecha y Aceptar . Bajo Ordenar por haga clic en la fila deseada y despusen Ascendente .

    Indicador_filas: es el nmero de fila en matriz_buscar_en desde el cual se deberdevolver el valor coincidente. Si indicador_filas es 1, devuelve el valor de la primera filaen matriz_buscar_en; si indicador_filas es 2, devuelve el valor de la segunda fila en

    matriz_buscar_en y as sucesivamente. Si indicador_filas es menor que 1, BUSCARHdevuelve el valor de error #VALOR!; si indicador_filas es mayor que el nmero de filasen matriz_buscar_en, BUSCARH devuelve el valor de error #REF!

    Ordenado : es un valor lgico que especifica si desea que el elemento buscado por lafuncin BUSCARH coincida exacta o aproximadamente. Si ordenado es VERDADEROo se omite, la funcin devuelve un valor aproximado, es decir, si no se encuentra unvalor exacto, se devuelve el mayor valor que sea menor que el argumentovalor_buscado. Si ordenado es FALSO, la funcin BUSCARH encontrar el valorexacto. Si no se encuentra dicho valor, devuelve el valor de error #N/A.Observaciones

    Si BUSCARH no logra encontrar valor_buscado, utiliza el mayor valor que seamenor que valor_buscado.

  • 7/28/2019 EXCEL Manual Avanzado

    82/149

    Manual avanzado de excel

    160

    Si valor_buscado es menor que el menor valor de la primera fila dematriz_buscar_en, BUSCARH devuelve el valor de error #N/A.

    Ejemplos Supongamos que en una hoja se guarda un inventario de repuestos. A1:A4 contiene"Ejes"; 4; 5; 6. B1:B4 contiene "Cojinetes"; 4; 7; 8. C1:C4 contiene "Engranajes"; 9; 10;11.

    Escribir la funcin BUSCARH en la Celda: B7: =BUSCARH("Ejes" ; A1:C4;2;VERDADERO)es igual a 4B8: =BUSCARH("Cojinetes",A1:C4,3,FALSO)es igual a 7B9: =BUSCARH("Cojinetes";A1:C4;3;VERDADERO)es igual a 7B10: =BUSCARH("Engranajes";A1:C4;4;)es igual a 11

    Matriz_buscar_en tambin puede ser una c onstante matrici al:

    BUSCARH(3;{1;2;3/"a";"b";"c"/"d";"e";"f"};2;VERDADERO)es igual a "c"

  • 7/28/2019 EXCEL Manual Avanzado

    83/149

    Manual avanzado de excel

    161

    En el ejemplo de la figura 5.5 usamos la funcin BUSCARH

    BSQUEDA DE REFERENCIA CRUZADAComo ltima forma de bsqueda se presenta el caso en el que usted requieradevolver un valor que se encuentre en una determinada fila y columna de unatabla. Suponga, por ejemplo, que usted dispone de una tabla en la que semuestra el monto de las ventas de tres empleados durante tres meses

    consecutivos. Los nombres de los vendedores estn dispuestos en la primeracolumna y los meses en la primera fila tal como se muestra en la Figura 5.6

    Empleando esta tabla, usted podra necesitar determinar el monto vendido por

    determinado vendedor en un mes en particular. Se dice que esta bsqueda es una

    referencia cruzada pues usted buscar la interseccin de la fila en la que se encuentra el

    nombre del vendedor con la columna correspondiente al mes.

  • 7/28/2019 EXCEL Manual Avanzado

    84/149

  • 7/28/2019 EXCEL Manual Avanzado

    85/149

  • 7/28/2019 EXCEL Manual Avanzado

    86/149

  • 7/28/2019 EXCEL Manual Avanzado

    87/149

    Manual avanzado de excel

    165

    6. Haga clic en el botn Detener Grabacin de la barra de herramientas delmismo nombre.

    UTILIZAR LAS MACRO.

    1. Haga clic en el men herramientas ; luego coloque el puntero del ratn sobreel submen Macro y, finalmente, sobre el comando Macro. Usted ver elsiguiente Cuadro de dilogo:

    2. Haga clic en el nombre de la macro que desee que se ejecute.3. Haga clic en el botn Ejecutar.

    ASIGNAR MACROS A BOTONES DE FORMULARIO.

    Si bien es cierto la macro funciona del modo adecuado, la forma de activarla no es lams rpida ni la ms cmoda para el usuario. Para lo cual Excel provee una forma deasignar un macro a un botn. Que se activa en la barra de Formulario.

    CONOCIMIENTOS PREVIOS DE PARA CREAR MACROS.

    Propiedades de Acti veCell .

    Devuelve un objeto Range que representa la celda activa de la ventana activa (laventana superior) o de la ventana especificada. Si la ventana no contiene una hoja declculo, esta propiedad fallar. Es de slo lectura.

    Comentarios

  • 7/28/2019 EXCEL Manual Avanzado

    88/149

    Manual avanzado de excel

    166

    Si no especifica un calificador de objeto, esta propiedad devolver la celda activa de laventana activa.

    Celda activa no es lo mismo que seleccin. La celda activa es una sola celda de laseleccin actual. La seleccin puede contener ms de una celda, pero slo una es lacelda activa.Todas las expresiones siguientes devuelven la celda activa y sonequivalentes:

    ActiveCellApplication.ActiveCellActiveWindow.ActiveCellApplication.ActiveWindow.ActiveCell

    Ejemplo de la propiedad ActiveCell

    Este ejemplo usa un cuadro de mensaje para mostrar el valor de la celda activa.Puesto que la propiedad ActiveCellfalla si la hoja activa no es una hoja de clculo.

    El siguiente ejemplo activar Sheet1 antes de utilizar la propiedad Ac tiveCell .

    Worksheets("Sheet1").Activate

    MsgBox ActiveCell.Value

    En este ejemplo se cambia el formato de fuente de la celda activa.Worksheets("Sheet1").Activate

    With ActiveCell.Font

    .Bold = True

    .Italic = True

    End With

    Trabajar con la celda activaLa propiedad ActiveCelldevuelve un objeto Range que representa la celda que estactiva. Puede aplicar cualquiera de las propiedades o los mtodos de un objeto Range a la celda activa, como en el ejemplo siguiente.

    Sub SetValue()

    Worksheets("Sheet1").Activate

    ActiveCell .Value = 35

  • 7/28/2019 EXCEL Manual Avanzado

    89/149

    Manual avanzado de excel

    167

    End SubNota Slo se puede trabajar con la celda activa cuando la hoja de clculo en la quese encuentra sea la hoja activa.

    Mover la celda activaPuede utilizar el mtodo Ac tivate para designar cul es la celda activa. Por ejemplo, elsiguiente procedimiento convierte B5 en la celda activa y, a continuacin, le da formatode negrita.

    Sub SetActive()

    Worksheets("Sheet1").Activate

    Worksheets("Sheet1").Range("B5").Activate

    ActiveCell.Font.Bold = True

    End Sub

    Nota Para seleccionar un rango de celdas, utilice el mtodo Select . Paraconvertir una sola celda en activa, utilice el mtodo Activate.Puede utilizar la propiedad Offset para pasar a la celda activa. El siguienteprocedimiento inserta texto en la celda activa del rango seleccionado y, acontinuacin, mueve la celda activa una celda a la derecha, sin cambiar laseleccin.Sub MoveActive()

    Worksheets("Sheet1").Activate

    Range("A1:D10").Select ActiveCell.Value = "Monthly Totals"

    ActiveCell.Offset(0, 1).Activate

    End Sub

    Seleccionar las celdas que rodean la celda activaLa propiedad CurrentRegion devuelve un rango de celdas limitadas por filas ycolumnas en blanco. En el siguiente ejemplo, la seleccin se ampla para incluir lasceldas contiguas a la celda activa que contiene datos. A continuacin, se asigna elestilo Moneda a este rango.

    Sub Region() Worksheets("Sheet1").Activate

    ActiveCell.CurrentRegion.Select

    Selection.Style = "Currency"

    End Sub

  • 7/28/2019 EXCEL Manual Avanzado

    90/149

  • 7/28/2019 EXCEL Manual Avanzado

    91/149

    Manual avanzado de excel

    169

    Use Range( arg ), donde arg asigna un nombre al rango, para devolver un objeto

    Range que represente una sola celda o un rango de celdas. El ejemplo

    siguiente coloca el valor de la celda A1 en la celda A5.

    Worksheets("Hoja1").Range("A5").Value = _

    Worksheets("Hoja1").Range("A1").Value

    El ejemplo siguiente rellena el rango A1:H8 con nmeros aleatorios

    estableciendo la frmula de cada celda del rango. La propiedad Range, si se

    emplea sin un calificador de objeto (un objeto colocado a la izquierda del

    punto), devuelve un rango de la hoja activa. Si la hoja activa no es una hoja de

    clculo, este mtodo no se llevar a cabo con xito. Use el mtodo Activate

    para activar una hoja de clculo antes de usar la propiedad Range sin uncalificador de objeto explcito.

    Worksheets("Hoja1").Activate

    Range("A1:H8").Formula = "=rand()" 'Range is on the active sheet

    El ejemplo siguiente borra el contenido del rango denominado "Criterios".

    Worksheets(1).Range("criteria").ClearContents

    Si usa un argumento de texto para la direccin del rango, deber especificar la

    direccin en notacin de estilo A1 (no podr usar la notacin F1C1).

    Propiedad CellsUse Cells(fila; columna), donde filaes el ndice de fila y columna es el ndice decolumna, para devolver una sola celda. El ejemplo siguiente establece en 24 el valorde la celda A1.

    Worksheets(1).Cells(1, 1).Value = 24

    El ejemplo siguiente establece la frmula de la celda A2.

    ActiveSheet.Cells(2, 1).Formula = "=sum(B1:B5)"

    Aunque tambin puede usar Range("A1") para devolver la celda A1, en algunasocasiones la propiedad Cells puede ser ms conveniente, ya que permite usar unavariable para la fila o la columna. El ejemplo siguiente crea encabezados de fila y

    columna en la Hoja1. Tenga en cuenta que, despus de activar la hoja de

  • 7/28/2019 EXCEL Manual Avanzado

    92/149

  • 7/28/2019 EXCEL Manual Avanzado

    93/149

    Manual avanzado de excel

    171

    Propiedad Offset

    Use Offset( fila; columna ), donde fila y columna son los desplazamientos de fila y

    columna, para devolver un rango con un desplazamiento especfico con respecto a

    otro. El ejemplo siguiente selecciona la celda situada tres filas debajo y una columna ala derecha de la celda de la esquina superior izquierda de la seleccin actual. No se

    puede seleccionar una celda que no est en la hoja activa, por lo que primero deber

    activar la hoja.

    Worksheets("sheet1").Activate

    'can't select unless the sheet is active

    Selection.Offset(3, 1).Range("A1").Select

    Mtodo UnionUse Union(rango1, rango2, ...) para devolver rangos de varias reas, es decir, rangoscompuestos por dos o ms bloques contiguos de celdas. El ejemplo siguiente crea unobjeto definido como la unin de los rangos A1:B2 y C3:D4 y, a continuacin,selecciona el rango definido.

    Dim r1 As Range, r2 As Range, myMultiAreaRange As Range

    Worksheets("sheet1").Activate

    Set r1 = Range("A1:B2")

    Set r2 = Range("C3:D4")

    Set myMultiAreaRange = Union(r1, r2)

    myMultiAreaRange.Select

    La propiedad Areas es muy til para trabajar con selecciones que contienenvarias reas. Divide una seleccin de varias reas en objetos Range individuales y despus devuelve los objetos en forma de conjunto. Puede usarla propiedad Count del conjunto devuelto para comprobar una seleccin quecontiene varias reas, como se muestra en el siguiente ejemplo.Sub NoMultiAreaSelection()

    numberOfSelectedAreas = Selection.Areas.CountIf numberOfSelectedAreas > 1 ThenMsgBox "You cannot carry out this command " & _

    "on multi-area selections"End If

    End Sub

  • 7/28/2019 EXCEL Manual Avanzado

    94/149

    Manual avanzado de excel

    172

    EJEMPLOS DE MACROS QUE SE PUEDEN A SIGNAR A UN BOTN

    Sub MostrarNombre()'Muestra el nombre de la hoja activa.

    MsgBox "El nombre de la hoja es " & UCase(ActiveSheet.Name)End Sub

    Sub NombrarHoja()'Asigna el nombre "Gastos_Enero" a la hoja activa del libro activo.

    ActiveWorkbook.ActiveSheet.Name ="Gastos_Enero"End Sub

    Sub NombrarHojas()

    'Asigna los nombres Ventas1, Ventas2,... a las hojas del libro activo.Dim x As Integer, Hoja As Worksheetx =1

    For Each Hoja In ActiveWorkbook.WorksheetsHoja.Name ="Ventas" & xMsgBox Hoja.Namex =x +1Next Hoja

    End Sub

    Sub MostrarNombres()'Visualiza los nombres de las hojas del libro activo.

    Dim Hojas As WorksheetFor Each Hojas In Worksheets

    MsgBox Hojas.NameNext Hojas

    End Sub

    Sub RangoUsado()'Selecciona el rango usado en la Hoja2.

    Worksheets("Hoja2").ActivateActiveSheet.UsedRange.SelectEnd Sub

    Sub OcultarHoja()'Oculta la Hoja2 del libro activo.

    ActiveWorkbook.Worksheets("Hoja2").Visible =FalseEnd Sub

    Sub MostrarHoja()'Hace visible la Hoja2 del libro activo.

  • 7/28/2019 EXCEL Manual Avanzado

    95/149

    Manual avanzado de excel

    173

    ActiveWorkbook.Worksheets("Hoja2").Visible =TrueEnd Sub

    Sub OcultarTodas()'Oculta todas las hojas del libro activo menos la Hoja1.

    Dim Hoja As WorksheetFor Each Hoja In Sheets

    If Hoja.Name Worksheets(1).Name Then Hoja.Visible =FalseNext HojaEnd Sub

    Sub OcultarHojas2()'Oculta todas las hojas menos la hoja activa.

    For Each Hoja In SheetsIf ActiveSheet.Name Hoja.Name Then Hoja.Visible =False

    Next HojaEnd Sub

    Sub HacerVisibleHojasOcultas()'Hace visible todas las hojas ocultas del libro activo.

    Dim Hoja As WorksheetFor Each Hoja In SheetsIf Hoja.Visible =False Then Hoja.Visible =TrueNext HojaEnd Sub

    Sub Condici onal1()'Solicita el precio de un artculo con la instruccin InputBox y lo'coloca en la celda A1 de la hoja activa. Si el valor ingresado'es superior a 1500, calcula el 15% de descuento y lo coloca en la'celda A2 de la hoja activa. En la celda A3 se guarda el precio del'artculo menos el descuento.

    Dim Precio As IntegerDim Descuento As IntegerPrecio = 0Descuento =0Precio = Val(InputBox("Ingresar el precio", "Ingreso de datos"))

    'Si la variable precio es mayor que 1500 calcula el descuento.If Precio >1500 Then

    Descuento =Precio * 0.15End If

  • 7/28/2019 EXCEL Manual Avanzado

    96/149

    Manual avanzado de excel

    174

    ActiveSheet.Range("A1").Value =PrecioActiveSheet.Range("A2").Value =DescuentoActiveSheet.Range("A3").Value =Precio - Descuento

    End Sub

    Sub Condici onal2()'Compara los valores de las celdas A1 y A2 de la hoja activa. Si son'iguales, asigna el color azul a la fuente de ambas celdas.

    If ActiveSheet.Range("A1").Value = ActiveSheet.Range("A2").Value ThenActiveSheet.Range("A1").Font.Color =RGB(0, 0, 255)ActiveSheet.Range("A2").Font.Color =RGB(0, 0, 255)

    End If End Sub

    Sub Descuento()Dim Precio As SingleDim Descuento As SinglePrecio = 0Precio = Val(InputBox("Ingresar el precio", "Ingresar datos"))

    'Si el valor de la variable Precio es mayor que 1500, entonces, aplicar'descuento del 10%; sino aplicar descuento del 5%.

    If Precio >1500 ThenDescuento =Precio * 0.1

    ElseDescuento =Precio * 0.05

    End If ActiveSheet.Range("A2").Value =DescuentoActiveSheet.Range("A1").Value =PrecioActiveSheet.Range("A3").Value =Precio - Descuento

    End Sub

    Sub AsignarColor()'Coloca en la celda A3 la diferencia de los valores de las celdas'A1 y A2. Si la diferencia es mayor o igual que 0, asigna el color'azul a la fuente de la celda A3, sino asigna el color rojo.

    Dim Valor1 As SingleDim Valor2 As SingleValor1 =ActiveSheet.Range("A1").ValueValor2 =ActiveSheet.Range("A2").ValueActiveSheet.Range("A3").Value = Valor1 - Valor2If ActiveSheet.Range("A3").Value < 0 Then

    'Asigna el color rojo a la fuenteActiveSheet.Range("A3").Font.Color =RGB(255, 0, 0)

    Else'Asigna el azul rojo a la fuenteActiveSheet.Range("A3").Font.Color =RGB(0, 0, 255)

    End If End Sub

  • 7/28/2019 EXCEL Manual Avanzado

    97/149

    Manual avanzado de excel

    175

    Sub Comparacin()'Compara los valores de las celdas A1 y A2 de la hoja activa.'Si son iguales, escribe en la celda A3 "A1 es igual que A2",'si el valor de A1 es mayor que A2, escribe "A1 mayor que A2";'sino, escribe "A2 es mayor que A1".

    Dim Valor1 As SingleDim Valor2 As SingleValor1 =ActiveSheet.Range("A1").ValueValor2 =ActiveSheet.Range("A2").ValueIf Valor1 =Valor2 Then

    ActiveSheet.Range("A3").Value = "A1 es igual que A2"Else

    If Valor1 >Valor2 ThenActiveSheet.Range("A3").Value ="A1 es mayor que A2"

    ElseActiveSheet.Range("A3").Value ="A2 es mayor que A1"

    End If

    End If End Sub

    Sub Promedio()'Solicita tres notas de un alumno mediante la funcin InputBox. Las notas son'colocadas en las celdas A1, A2 y A3 de la hoja activa. Luego, la macro calcula'el promedio de las notas y lo coloca en la celda A4. Si el promedio est entre'0 y 6, coloca en la celda A5 el mensaje "Muy deficiente"; si el promedio est'entre 7 y 10, coloca en A5 el mensaje "Deficiente"; si el promedio est entre'11 y 12, coloca el mensaje "Suficiente"; si est entre 13 y 15, "Bien"; si est 'entre 16 y 18, coloca "Notable"; si es mayor o igual que 19, "Sobresaliente".

    Dim Nota1 As Integer, Nota2 As Integer, Nota3 As IntegerDim Promedio As SingleNota1 =Val(InputBox("Ingresar la primera nota", "Promedio"))Nota2 =Val(InputBox("Ingresar la segunda nota", "Promedio"))Nota3 =Val(InputBox("Ingresar la tercera nota", "Promedio"))Promedio =(Nota1 +Nota2 + Nota3) / 3ActiveSheet.Range("A1").Value = Nota1ActiveSheet.Range("A2").Value = Nota2ActiveSheet.Range("A3").Value = Nota3ActiveSheet.Range("A4").Value = PromedioSelect Case Promedio

    Case 0 To 6ActiveSheet.Range("A5").Value ="Muy deficiente"

    Case 7 To 10ActiveSheet.Range("A5").Value ="Deficiente"

    Case 11 To 12ActiveSheet.Range("A5").Value ="Suficiente"

    Case 13 To 15ActiveSheet.Range("A5").Value ="Bien"

    Case 16 To 18ActiveSheet.Range("A5").Value ="Notable"

    Case Is >=19

  • 7/28/2019 EXCEL Manual Avanzado

    98/149

    Manual avanzado de excel

    176

    ActiveSheet.Range("A5").Value ="Sobresaliente"End SelectEnd Sub

    Sub IGV()'Calcula el IGV (18 por ciento) y el precio de venta de'una serie de artculos. El programa recorre las celdas'y se detiene cuando encuentra una celda vaca.

    Dim Precio As DoubleActiveWorkbook.Worksheets("Hoja3").ActivateSelection.Resize(1, 1).SelectWhile Not (IsEmpty(ActiveCell.Value))

    If IsNumeric(ActiveCell.Value) ThenPrecio = ActiveCell.ValueActiveCell.Offset(0, 1).SelectActiveCell.Value =Precio * 0.18ActiveCell.Offset(0, 1).SelectActiveCell.Value =Precio +Precio * 0.18ActiveCell.Offset(0, -2).SelectEnd If ActiveCell.Offset(1, 0).Select

    WendActiveCell.Offset(-1, 0).SelectActiveCell.CurrentRegion.SelectSelection.Style ="Currency [0]"End Sub

    Sub AmortizacinCuotasFijas()'Calcula los pagos que debe realizar un prestatario al final de'cada periodo de tiempo para amortizar un prstamo a inters compuesto.

    Dim c, i, t, Amortizaciones, Inters, Cuotas As DoubleDim SumaInters, SumaAmortizaciones, SumaCuotas As DoubleDim fila As IntegerWorksheets("Hoja1").ActivatelimpiarCells(3, 4).Value =Application.InputBox _

    ("Introducir la deuda a amortizar", Type:=1)c =Cells(3, 4).ValueCells(4, 4).Value =Application.InputBox _

    ("Introducir el tipo de inters anual", Type:=1)i =Cells(4, 4).Value / 100Cells(5, 4).Value =Application.InputBox _

    ("Introducir el tiempo en aos", Type:=1)t = Cells(5, 4).ValueCuotas =c * (i / (1 - (1 / ((1 + i) t))))SumaInters = 0: SumaAmortizar = 0: SumaCapitalPagado =0fila = 1While fila

  • 7/28/2019 EXCEL Manual Avanzado

    99/149

  • 7/28/2019 EXCEL Manual Avanzado

    100/149

  • 7/28/2019 EXCEL Manual Avanzado

    101/149

    CREAR FUNCIONES DEL USUARIO

    Para crear una funcin definida por el usuario debemos realizar1.- Ir al men Herramientas-Macros- Editor de Visual Basic

    2.- En el editor de Visual Basic damos clic derecho sobre la opcin mdulo en elexplorador agregamos a un modulo.

    3.- Ir al men Insertar Procedimiento, y se muestra una interfaz similar a la figura 6.5y le asignamos un nombre a nuestra funcin ( IGV), deber haber seleccionadopreviamente la opcin funcin y como Public.

  • 7/28/2019 EXCEL Manual Avanzado

    102/149

  • 7/28/2019 EXCEL Manual Avanzado

    103/149

  • 7/28/2019 EXCEL Manual Avanzado

    104/149

    Manual avanzado de excel

    182

    PREGUNTAS DE REPASO

    1. Qu es una macro?

    2. Cul es la Utilidad de las macros?

    3. Crear una macro que se asocia a un botn para calcular lo siguiente expresin.P=C[ ((1+i) n-1) / (i*(1+i)n)].

  • 7/28/2019 EXCEL Manual Avanzado

    105/149

    Manual avanzado de excel

    183

  • 7/28/2019 EXCEL Manual Avanzado

    106/149

    Manual avanzado de excel

    184

    Capitulo VII

    Tablas DinmicasINTRODUCCIN

    Un informe de tabla dinmica es una tabla interactiva que se puede utilizar pararesumir rpidamente grandes volmenes de datos. Podr girar sus filas y columnaspara ver diferentes resmenes de los datos de origen, filtrar los datos mostrandodiferentes pginas, o mostrar los detalles de determinadas reas de inters.

    Ejemplo de un informe de tabla dinmica sim