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