MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 1 de 174
MANUAL DE EXCEL AVANZADO
ILMER CONDOR
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 2 de 174
CAPITULO I
CONCEPTOS DE BASE DE DATOS. ORDENAR.
FILTRO. CONSOLIDACION.
OBJETIVO
El objetivo de este Capítulo es proporcionar la información necesaria sobre Bases de Datos,
Ordenamiento, Filtros y Consolidación.
METAS
Al completar este Capítulo el participante estará en capacidad de
- Ordenar una base de datos,
- Extraer o visualizar una parte de ella mediante el uso de Filtro Automático
- Extraer o visualizar una parte de ella mediante el uso de Filtro Avanzado
- Obtener diversas formas de consolidación
TEMAS A TRATARSE
1. CONCEPTOS DE BASE DE DATOS
2. ORDENAR UNA BASE DE DATOS
3. FILTROS
3.1. FILTROS AUTOMÁTICOS (Auto filtros)
3.2. FILTROS AVANZADOS
4. CONSOLIDACIÓN
4.1. DE UNA MISMA HOJA
4.2. DE MÚLTIPLES HOJAS
4.3. DE OTROS LIBROS
5. EJEMPLO DE APLICACION
6. EJERCICIOS
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 3 de 174
1. CONCEPTO DE BASE DE DATOS
Una Base de Datos es una colección de datos debidamente organizada
de tal manera que su acceso, consulta, adición, modificación y
eliminación de parte de ella, resultan ser operaciones sencillas. Un
conjunto de datos constituye una base de datos, si tiene una determinada
estructura física y lógica.
Desde la perspectiva del usuario (usuario final o usuario programador),
la estructura lógica de una Base de Datos está compuesta por un
conjunto de registros. Un registro está compuesto por un conjunto de
campos. Y un campo está constituido por un item. Este item es el
componente elemental en un registro.
Por ejemplo en una Base de Datos de Empleados podemos tener datos
respecto al código o número de empleado, sus apellidos y nombres, su
fecha de ingreso a la empresa, cargo, departamento, básico, etc.
El conjunto de todos los campos constituyen el registro empleado.
Cada empleado constituye un registro.
El conjunto de todos los empleados constituye una Base de Datos que
puede tener un nombre: Empleados, por ejemplo.
El conjunto de los empleados de una empresa, la lista de productos de
una ferretería, la relación de los pacientes de un hospital, los registros de
vuelos de una aerolínea, etc. constituyen bases de datos.
Por su estructura, una base de datos puede ser: Jerárquica o
Relacional.
Una BD Jerárquica se fundamenta en una estructura de datos de tipo
árbol, en el cual los datos pertenecen a un determinado nivel y el acceso
a ellos se realiza a través de los niveles, recorriendo las “ramas del árbol”
a través de navegaciones ascendentes o descendentes.
En una BD Relacional, en cambio, los registros y los campos se
encuentran directamente relacionados bajo la concepción de filas y
columnas. Una fila representa un registro y una columna representa un
campo. De esta manera, si en una fila se dispone de 5 columnas de
datos, podemos hablar de un registro con 5 campos.
Definición Registro Campo Estructura de una BD Una fila, un registro Una columna, un campo
Una Base de datos es llamada también una lista
BD relacional
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 4 de 174
Una BD Relacional es la más sencilla de manejar. Muchos
administradores de bases de datos, usan este tipo de estructura.
Puesto que una hoja de cálculo está compuesta por filas y columnas, el
Excel es uno de los programas de aplicaciones que permiten el manejo
de una base de datos relacional.
Un Sistema de administración de una base de datos o DBMS (Data Base
Management System), permite al usuario el acceso a sus datos y le
proporciona asistencia y facilidades para transformarlos en información.
Entre los primeros administradores de datos en sistemas main frame fue
el DBMS el cual se podía manejar mediante el lenguaje de datos DL/1. A
nivel de los microcomputadores, desde los años 80 fueron apareciendo
diversos lenguajes o administradores de datos como el dBase, Clipper,
Paradox, FoxPro, IMS, Oracle, Access.
Una hoja de cálculo es un sencillo y a la vez muy efectivo administrador
de datos pero bastante restringido, pero que satisface muchos de
nuestros requerimientos y apremios.
Mediante una hoja de cálculo podemos realizar operaciones de
ordenamiento, de búsqueda, extracción, etc. Y podemos aprovechar la
potencia de la hoja de cálculo para realizar diversos cálculos y gráficos.
La siguiente figura es un claro ejemplo de una Base de Datos en Excel.
El primer empleado; es decir, la primera fila, constituye el primer registro.
Se trata del señor Gonzáles Miller, Joe; trabaja en el departamento de
Administración; su básico es de 4205 soles; etc.
Un poco de historia
Figura 1
Si tomamos
en cuenta
hasta la
columna G, la
base de datos
contendría 14
registros y 6
campos
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 5 de 174
Ejemplo 1
Abra el archivo Películas de cine.xls.
Este libro contiene una lista de películas de cine conteniendo información
respecto a código, nombre de película, tipo, fecha de ingreso, precio,
estado y cantidad existente. Tomando en cuenta estos datos responda
las siguientes preguntas:
- ¿Cuántos campos tiene esta lista? ……….
- ¿Cuántos registros tiene esta lista? ……….
- ¿Cuántos campos de tipo numérico tiene? ………..
- ¿Cuántos campos de tipo fecha tiene? ………..
- Usando la siguiente secuencia complete el siguiente cuadro:
o Clic en una celda de una columna
o <Formato> - <Columna> - <Ancho>
o Tome nota del ancho
o <Aceptar>
Estructura de la base de datos PELICULAS
Nombre de campo Tipo Longitud Decimales
Codigo
NombrePeli
Tipo
FechaIng
Precio
Estado
Cantidad
- Haga que la base de datos se llame PELICULAS
Nota 1:
Una base de datos tiene un nombre desde el momento en que le
damos nombre de rango al conjunto de celdas seleccionadas.
Nota 2:
Trate de usar nombres adecuados como nombre de campo (o
columna). Por lo general dicho nombre debe decir algo respecto a
su contenido.
Nota 3:
Si desea usar dos palabras como nombre de campo, debe unirlas
con el guión de mayúscula.
7 786 2 (Precio, Cantidad) 1 (FechaIng) Cómo se llama la Base de Datos? No tiene nombre. Una cosa es el nombre del Libro: Películas de Cine, otra es el nombre de la hoja: Listado de películas y otra cosa es el nombre que puede tener la base de datos Como los datos (CON SUS RESPECTIVOS CAMPOS) están contenidos en el rango A1:G787, podemos usar <Insertar> - <Nombre> - <Definir> para dar nombre a nuestra base de datos: PELICULAS
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 6 de 174
2. ORDENAR UNA BASE DE DATOS (BD)
Introducción
Una base de datos, por la facilidad de acceso que proporciona, puede
contener abundante cantidad de información, la que se fue almacenando
sucesivamente, según se fueron completando. Esto indica que
probablemente (y es lógico que esto ocurra) no se encuentre
debidamente ordenada. En una base de datos de 40 trabajadores de una
empresa puede ser muy fácil la búsqueda de un empleado. Es muy
rápido el disponer de todos los datos respecto de dicho empleado.
Pero en una base de datos de 50 mil clientes de una entidad bancaria, la
base de datos de postulantes a una universidad, las ventas diarias que
se registra en determinado supermercado, a través de sus 30 cajas
registradoras, durante 10 horas del día y por sus 10 tiendas,
seguramente debe ser muy difícil localizar un determinado cliente, saber
el número de postulantes a la facultad de Medicina o calcular el monto
total de ingresos diarios obtenidos por la venta de espárragos durante
una semana.
Y qué podemos decir del costo o el tiempo requerido para resolver una
consulta?
Por estas y muchas otras razones una base de datos debe estar
ordenada; es decir, clasificada.
Una base de datos se ordena (o clasifica) de acuerdo a un criterio de
ordenamiento y tomando como base algún campo de la base de datos.
Una base de datos de clientes de cuenta corriente de un banco puede
estar ordenada por número de cuenta corriente o por apellidos; puede
estar ordenada por la frecuencia de uso de la cuenta, puede estar
ordenada por el saldo, en forma creciente o decreciente.
Una base de datos de postulantes puede ordenarse por facultad y por
apellidos y nombres del postulante; o podría ordenarse por facultad y por
colegio de procedencia; tal vez puede ordenarse por Distrito, por colegio
y por apellidos y nombres.
Las ventas diarias de un supermercado pueden estar clasificadas por
Tienda, dentro de cada tienda, por código de cajero, dentro de cada
cajero por el nombre del producto.
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 7 de 174
La forma cómo debe ordenarse una base de datos depende de cómo lo
desea el usuario en un momento determinado.
El Excel dispone del comando <Datos> para ordenar una base de datos.
Para ello es suficiente usar la siguiente secuencia:
<Datos> - <Ordenar>.
Ejemplo 1
Vamos a ordenar la lista de películas.
Abra el archivo Películas de cine.xls
- Haga clic en la celda B5
- Al usar la secuencia <Datos> - <Ordenar>, obtendrá la siguiente
ventana
- Al desplegar la lista, seleccione el campo Tipo
- El botón de opción Sí debe quedar activado ya que nuestra lista
contiene nombre de campo en la primera fila
- Haga clic en <Aceptar>
Observe los resultados
Ejemplo 2
Veamos el siguiente ejemplo.
Abra ahora el archivo Relación de personal.xls.
Esta lista contiene tres campos por las cuales se puede establecer cierta
jerarquía: Departamento, Sección y Puesto. Hay diversas formas de
Despliegue la lista para elegir el campo a ordenar
Despliegue la lista si desea ordenar después por este campo
Figura 2
Para que el rango de los datos quede seleccionado automáticamente, primero debe hacer clic al interior de los datos. De otra manera primero debe seleccionar el rango y luego usar el comando <Datos>
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 8 de 174
ordenar:
Alfabético por departamento. Esto requiere dos campos de
ordenamiento: Departamento primero y Nombres, después.
Alfabético por departamento y sección: Esto requiere tres campos:
Departamento primero, Sección después y Nombres en tercer lugar.
Para el primer caso, después de usar:
<Datos> - <Ordenar>
Complete la ventana como se muestra en la Figura 3 y luego haga clic en
<Aceptar>.
Para el segundo caso, después de usar la secuencia
<Datos> - <Ordenar>
Complete los datos según la Figura 4.
Como puede observar, los datos han sido ordenados primero por
Departamento, dentro de cada departamento se ha ordenado por
Sección y luego y dentro del él, se ha ordenado por nombres.
EJERCICIO
Abra el archivo BD Tabla.xls. Defina a los datos contenidos en la primera
hoja como Pedidos y a los de la segunda hoja como Productos.
Ordene a la base de datos Pedidos por País y Ciudad de destinatario y
por Cliente. En el caso de la base de datos Productos, ordene por Id de
pedido y por el Precio. En ambos casos en form
Si no desea seleccionar un campo, elija la opción <Ninguna>
Cualquier tipo de campo, se puede ordenar en forma - Ascendente o - Descendente
Figura 4
Figura 3
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 9 de 174
Figura 5
a decreciente.
3. FILTROS
Según el tamaño de una base de datos, la búsqueda, consulta o
recuperación de los datos contenidos en ella, puede ser un trabajo muy
pesado, no tanto por la dificultad de acceso, sino por lo difícil de la
búsqueda en una gran cantidad de datos.
En una base de datos corporativa, el personal de un determinado
departamento está interesado en una parte de ella: Por ejemplo, en el
caso de los empleados de ventas seguramente están interesados en
disponer de la cantidad de cada producto, del precio, de la ubicación
dentro del almacén; etc. Pero no requieren datos relativos a la forma de
pago de los clientes, a la dirección del proveedor; sin embargo a los de
cobranzas sí les interesan estos datos, no así las estadísticas de ventas.
Esto implica que la información a ser consultada o extraída desde una
base de datos, debe ser “filtrada” según el usuario de la misma. Cabe
añadir que un determinado usuario puede estar interesado sólo en una
parte de los datos; pero en otro momento puede estar realizando una
consulta diferente.
Otro gran problema es la manipulación de los datos si ésta es bastante
grande. Si el Departamento Médico de la Universidad de San Marcos
necesita examinar a los que ingresaron, no tiene sentido que deba
realizar consultas sobre toda la base de datos de 45,000 postulantes.
Hay pues muchas razones para trabajar con una parte de la base de
datos. Sea una parte de los registros o algunos campos de la misma.
En Excel disponemos
de la opción <Filtro> del
comando <Datos> para
realizar este trabajo.
Al usar la secuencia
<Datos> - <Filtro>,
obtenemos la ventana
mostrada en la Figura 5
de la derecha.
Filtros
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 10 de 174
De acuerdo a las opciones de <Filtro>, podemos aplicar:
- Autofiltro
- Filtro avanzado
Después de haber usado una de estas dos formas de filtrado, los
registros que se muestran son parciales. Usaremos la opción <Mostrar
todo> para disponer de todos los datos. Se sugiere que se haga uso de
esta opción toda vez que se quiera aplicar otra modalidad de filtrado,
excepto si se desea aplicar nuevo filtro al resultado de un filtro anterior.
3.1. FILTRO AUTOMATICO (Autofiltro)
Ejemplo 01
- Abra el archivo Relacion de Personal.xls
- Al usar la secuencia <Datos> - <Filtro> - <Autofiltro>, obtendremos
la ventana, parte de la cual, se muestra en la Figura 6.
- ss
3.2. sdss
- Al hacer clic en de la columna
Departamento, obtenemos el recuadro de
la derecha.
Según esto, podemos mostrar la lista
completa (Todas); sólo los mejores (Diez
mejores); podemos filtrar de manera muy
particular (Personalizar…) o podemos
mostrar los empleados de un solo
departamento.
Para esto último es suficiente hacer clic en el departamento que se
desea visualizar.
- Al hacer clic en Diseño, obtenemos la lista que se muestra en la
siguiente figura.
Haga clic en estas flechas para visualizar opciones de autofiltro en este campo
Figura 6
Opciones de Autofiltro
Autofiltro
Use <Datos> - <Filtro> - <Autofiltro> para activar o desactivar esta opción Autofiltro.
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 11 de 174
Si observamos la columna Departamento, veremos que sólo se
visualizan los empleados de Diseño. Del mismo modo, observando
el número de registro (fila), podemos decir que los empleados de
Diseño corresponden a los registros 16, 17, 24, 28, 29, 66, 75 y 80.
Observe también que en la barra de estado se puede leer el número
de registros que cumplen con este tipo de filtrado
- Al hacer clic nuevamente en el mismo botón y seleccionar el
departamento de Ingeniería, sólo veremos a los empleados de este
departamento.
- Ahora mostremos a todos los registros seleccionando la opción
<Mostrar todos> del recuadro mencionado.
- ¿Podríamos saber cuándo ingresó y qué sueldo tiene Lisa Barber?
Para ello hacemos clic en el botón de Nombres y de la lista que se
muestra seleccionamos Barber, Lisa.
- ¿Cuántos empleados son Auxiliares Técnicos?
En este caso hacemos clic en el botón de Puesto y seleccionamos
lo pedido.
Usemos autofiltro combinado:
Un filtro combinado se logra filtrando primero por un campo y luego
por los otros campos o columnas (empezando por el más general).
- ¿Cuántos empleados del departamento de Ingeniería pertenecen
a la sección Copiadoras?
Procedimiento:
o Hacemos clic primero en el botón de Departamento
o Seleccionamos Ingeniería
o Luego hacemos clic en Sección
o Finalmente seleccionamos Copiadoras
- ¿Cuántos empleados del departamento de Ingeniería están en la
Ingresó el 11/27/86 Sueldo anual: $ 47,986 Hay 4 Auxiliares Téc.
También puede usar <Datos> - <Filtro> - <Mostrar todo> para restablecer todos los registros.
Figura 7
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 12 de 174
sección Impresoras y son Técnicos?
El orden de selección es: Ingeniería, Sección y Puesto.
Autofiltro personalizado
El filtrado automático (Autofiltro) se enriquece con la opción
<Personalizar>, que es el que vamos a usar ahora.
Al hacer clic en <Personalizar…> de cualquier botón, se obtiene una
ventana como se muestra en la figura 8.
En ella se muestran dos filas conteniendo cuadros de textos. En el
lado izquierdo se muestra una lista de formas de condicionar el
filtrado y en el lado derecho se colocarán los valores que se deben
satisfacer. Tenemos también los conectivos lógicos “Y” y “O” cuando
se usan las dos filas.
Veamos los siguientes ejemplos:
- Muestre la lista de los empleados cuyo Sueldo Anual es superior a
$ 50,000
o Hacemos clic en el botón de Sueldo Anual para desplegar sus
opciones.
o Seleccionamos (Personalizar…)
o En el primer cuadro de texto seleccionamos “es mayor que”
o En el lado derecho digitamos 50000 (puede digitarse 50,000)
o Haciendo clic en <Aceptar> y observando la barra de estado
obtendremos 33 empleados
- ¿Cuántos empleados tienen un Sueldo Anual mayor o igual a $
60,000 y menores que $ 85,000?
Completemos la ventana como se muestra en la figura 9.
Opción de autofiltro <Personalizado>
Figura 8
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 13 de 174
- Tomando en cuenta la información que se muestra en la figura 10,
complete los espacios en blanco de la siguiente pregunta:
- ¿Cuántos empleados tienen un Sueldo Anual …………… o
…………………?
- Obtenga una lista de empleados cuyos nombres empiezan con A
ó con D
En este caso usaremos la siguiente secuencia:
o Restablecer los registros(<Datos> – <Filtro> – <Mostrar todo>)
o Hacemos clic en el botón de Nombres
o Seleccionamos (Personalizar…)
o Completamos el cuadro según la Figura.
Nota:
Como dice las dos líneas, podemos usar comodines para filtrar
registros.
Aquí hemos usado el comodín “*” para indicarle que debe extraer a
Rpta. Sueldo Anual menor que 22000 ó mayor que 100000. Comodines
Recuerde restablecer todos los registros
Figura 9
Figura 10
Figura 11
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 14 de 174
todos los empleados cuyos Nombres empiezan con A o con D.
- Puede decirnos qué acción debemos añadir para obtener un listado
como el que se muestra en la figura 12?
¿Cuántos empleados ingresaron después del 90?
Ante todo, veamos el formato de fecha que está usando Excel. Para
ello hacemos clic en cualquier celda de la columna Fecha de Ingreso
(o Fecha de Nacimiento). En la barra de fórmulas indica que el
formato usado es el de “DD/MM/AA”. Por tanto, usando la opción
Personalizar, correspondiente a la Fecha de Ingreso, debemos
obtener como se indica
Nota: Como veremos, cuando estudiemos funciones de fecha, el
Excel usa a todos los datos de tipo fecha, como un entero.
- ¿Cuántos y quiénes son los empleados con Sueldo Anual mayor o
igual a $ 60,000 y menor que $ 80,000; que ingresaron después del
primero de Octubre del 84 y pertenecen al Departamento de
Ingeniería o Mercado?
Solución
o Primero usemos: <Datos> - <Filtro> - <Mostrar todo>
o Clic en <Sueldo Anual> - <Personalizar …> - <Es mayor o igual
que> -60000 <Y> - <Es menor o igual que > 80000 - <Aceptar>
o A continuación: Clic en <Fecha Ingreso> - <Personalizar …> -
<Es mayor o igual que > 01/10/1984 - <Aceptar>
o Finalmente: Clic en <Departamento> - <Personalizar…> - <Es
Igual a > Ingeniería - <Ó> - <Es igual a> - Mercado - <Aceptar>.
Terminemos con filtro automático mostrando todos los registros.
Filtrado complejo Respuesta: 14 Respuesta: 6 Respuesta: 5
Figura 12
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 15 de 174
3.2. FILTRO AVANZADO
La complejidad de la última pregunta resuelta usando Filtro
Automático, nos dice que éste es muy potente.
Sin embargo, tiene sus limitaciones, como es el caso de aplicar
autofiltro sucesivamente a otros campos, en base a un resultado
anterior. Por otro lado, el resultado obtenido se muestra en la misma
lista original; no es posible extraerlo hacia otro lugar de la hoja.
La opción Filtro Avanzado nos permitirá resolver las dos
inquietudes, como lo veremos a continuación.
En el caso de Filtro avanzado tenemos la capacidad de filtrar y
extraerlo hacia otro lugar de la hoja, o filtrar la lista sin moverlo.
Para usar Filtro Avanzado se debe definir tres rangos:
Rango de datos: Contiene todas filas y columnas (Registros)
Rango de Criterios: Contiene dos o más filas donde se ingresan las
condiciones de filtrado. No requiere incluir todas las columnas
Rango de Salida: Le indica al Excel el rango que debe usar para
colocar todos los registros que cumplen con los criterios indicados.
La siguiente figura muestra estos rangos
Rangos a tomarse en cuenta
Todos los rangos incluyen como primera fila a los nombres de campo. En el rango de Criterios dos o más condiciones de la misma fila cumplen con el conectivo lógico “Y”. Los criterios en más de una columna satisfacen el conectivo “O”. Para el rango de salida es suficiente hacer clic en una celda.
Figura 13
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 16 de 174
Nota:
Recuerde que para desactivar la opción de Autofiltro, debe
usar la secuencia <Datos> - <Filtro> - <Autofiltro>.
A continuación veremos algunos ejemplos
Pasos a seguir:
Paso 1: Copiar hacia un lugar determinado los nombres de los
campos (nombres de columna). Estos serán usados como rango de
criterios.
Paso 2: Ingresar en la siguiente fila o filas los valores que completan
el rango de criterio
Paso 3: Usar la secuencia: <Datos> - <Filtro> - <Filtro avanzado>.
En la ventana que se obtenga se deberá completar como se indica
en le siguiente figura.
Usando el archivo Relación de personal.xls, resuelva los siguientes
ejemplos.
Ejemplo 2
Obtenga un listado a partir de la celda J7, de todos aquellos
empleados del Departamento de Ingeniería.
Solución
Paso 1: Seleccionar el rango A7:H7 (este rango contiene los
nombres de los campos. Es mejor copiarlos todos). Copiar. Pegar a
partir de J1.
Si digita Ingenieria este no es igual a Ingeniería
Se activa cuando
se copia a otro
lugar.
Figura 14
Haga clic
aquí para
copiar a
extraer los
resultados a
otro lugar
Ingrese aquí
el rango
donde están
los criterios
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 17 de 174
Paso 2: En M2 digitar “Ingeniería”. Si desea copie este dato de
alguna celda de la columna respectiva. (Es más confiable).
Paso 3: Haga clic en el interior de la base de datos. Use la secuencia
<Datos> - <Filtro> - <Filtro Avanzado>. En la ventana de diálogo que
se obtiene complete según se indica en la figura 15.
Para ingresar el rango de salida en el cuadro de texto <Copiar a:>, es
suficiente hacer clic en J7.
No se olvide de activar la casilla Copiar a otro lugar
Finalmente, haga clic en <Aceptar> para obtener el resultado.
Observe que los registros extraídos cumplen con el criterio de ser
todos ellos del departamento de Ingeniería.
Nota:
Si en M2 hubiera digitado Ingeniería sin acento, el rango de
salida estaría vacía, sólo veríamos los nombres de columna
(nombres de campo).
Ejemplo 3
Obtenga un listado a partir de la celda J20, de todos aquellos
empleados del Departamento de Ingeniería y que pertenecen a la
Sección de Copiadoras.
Solución
Paso 1: Ya está completada (lo hicimos en el Ejemplo 1)
Paso 2: Digite (o copie) “Copiadoras” en la celda N2. Ahora ya
tenemos dos criterios: Que sea del Departamento de Ingeniería Y
pertenezca al Puesto de Copiadoras.
Nota:
Si no obtiene
resultados,
borre los
nombres de
columna de J7 y
repita el
procedimiento
indicado en este
ejemplo.
Así puede usar
el mismo rango
de salida varias
veces.
Figura 15
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 18 de 174
Paso 3: Active Copiar a otro lugar y en el rango de salida, haga clic
en J20.
Ejemplo 4
Obtenga un listado a partir de la celda P7, de todos aquellos
empleados del Departamento de Ingeniería, que pertenecen a la
Sección de Copiadoras y tienen un Sueldo Anual superior a $ 60,000
y menor que $ 80,000.
Nota: Copie el nombre del campo Sueldo Anual hacia R1.
En O2 digite >=60000 y en R2 digite <=80000. Cuando use el
comando, redefina el rango de criterio: J1:R2
Use el archivo BD Tabla.xls para resolver los siguientes
ejercicios.
Ejercicio 1
Obtenga la lista de todas los pedidos que se produjeron durante el
mes de Julio de 1996
Ejercicio 2
Obtenga todos los pedidos realizados por Buchanan, Steven, en todo
el mes de Julio de 1996.
Ejercicio 3
Obtenga todos los pedidos realizados por Davolio, Nancy o
Buchanan, Steven y que fueron remitidos a Alemania en el mes de
Julio del 96.
Ejercicio 4
Cúantos pedidos se realizaron usando la forma de Envío “United
Package” o “Federal Shipping” con un cargo mayor o igual a $ 80.0
pero menor que $ 120.0?
Y cómo se puede extraer los registros filtrados y tenerlos en otra
Planteamiento
de un caso
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 19 de 174
hoja?
Esto es sin duda una buena alternativa. Para ello es necesario dar
nombre de rango a la base de datos. Luego del cual, se puede filtrar
hacia cualquier hoja dentro del mismo libro.
4. CONSOLIDACIÓN
Introducción
Una de las grandes razones por las cuales se almacenan los datos en un
medio de almacenamiento electrónico es con la finalidad de generar
reportes o informes a partir de ellos. Estos documentos muestran, por lo
general, resúmenes de una gran cantidad de datos que, de otra manera,
resultaría en un informe difícil de interpretar.
Veamos un caso:
Suponga que Usted es el responsable del las ventas generadas a través
de 5 tiendas cada una con 12 cajeros. Al inicio de cada día recibe la
información de ventas de las tiendas remitidas por el responsable de
ventas de cada tienda. Si Ud. recibe diariamente 5 archivos, cómo los
puede “fusionar” en un solo archivo?; cómo puede sumarizar el monto de
las ventas?. Cómo los puede colocar estos montos uno al costado del
otro?. Si ahora necesita emitir informes semanales o mensuales para ser
remitidos a los departamentos de mercadeo, contabilidad, etc. cómo
debe preparar sus reportes o informes?
Estas y muchas otras dificultades surgen en múltiples casos similares al
descrito en el párrafo anterior.
La solución para todos estos problemas es la consolidación de los datos.
Se puede consolidar añadiendo datos una al costado de otro; se puede
consolidar columnas de datos sumando, promediando, etc. en una sola
columna; se puede consolidar de tal forma que, cuando los datos de la
consolidación cambian, los resultados en la consolidación también
cambian.
La imagen que se muestra en la figura 15, nos releva de todo comentario
adicional.
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 20 de 174
Pero cualquiera que sea la forma de consolidar, los datos deben haber
sido ingresados en todos y cada uno de los componentes de la
consolidación, exactamente en el mismo formato.
Observando el rango de las ventas del Lunes y Domingo, en la figura
anterior, podemos apreciar que se trata de 5 tiendas, los nombres de
estas y sus respectivos montos se encuentran en las mismas
ubicaciones, en el mismo orden y los rangos son iguales.
Ojo que la figura no hace referencia a que cada componente debe estar
en la misma hoja.
En una consolidación los rangos a ser consolidados pueden pertenecer a
a una misma hoja, a hojas diferentes o también a hojas de libros
diferentes, todos ellos abiertos.
En una consolidación hay situaciones en las que el rango de cada uno de
los componentes es el mismo; sin embargo en otros puede ser diferente.
En estos casos defina adecuadamente los rangos de cada bloque a ser
consolidad. Esto es natural si se consolidad las ventas de dos o más
tiendas de un supermercado.
Figura 16
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 21 de 174
El Excel dispone de la opción <Consolidar> del comando <Datos> para
resolver el problema de consolidación. Esto se muestra en la Figura 17.
Al usar la secuencia <Datos> - <Consolidar> se obtiene la ventana que
se muestra en la Figura 18.
Hay varias operaciones que se pueden realizar cuando se consolida.
Haga clic en el cuadro de lista <Función> para elegir lo deseado.
Cada uno de los rangos a ser consolidados serán ingresados en el
cuadro de texto <Referencia>
Si el rango está en otro libro, haga uso del botón <Examinar>, de otra
manera seleccione el rango de la misma hoja o haciendo clic
previamente en la etiqueta de la hoja donde se encuentra el rango.
Al hacer clic en el botón <Agregar>, el rango ingresado en <Referencia>
será añadido a la lista que se muestra en <Todas las referencias>.
Si alguno de estas referencias estuviera mal o no debe ser considerada,
selecciónelo de la lista y haga clic en el botón <Eliminar>
Figura 17
Figura 18
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 22 de 174
Use los botones de opción de <Usar rótulos en> según cómo desea
consolidar.
Nota:
Tenga cuidado de seleccionar la celda inicial del rango y que
esta sea la misma celda inicial de cada uno de los rangos
componentes de la consolidación.
A continuación veremos las diferentes formas de consolidar que permite
el programa Excel.
4.2. DE UNA MISMA HOJA
Ejemplo 1
Abra el archivo Ventas diarias.xls
Observe que en la primera hoja se dispone de 7 cuadros en los
cuales se registra el monto de las ventas de cada día de la semana,
de los seis días. En el rango A3:B9 se encuentra la información de la
Semana 1; en el rango A11:B17, A19:B25 y A27:B33 se encuentran
los montos de las ventas de las otras semanas del mes.
Se desea obtener un cuadro resumen de todas las ventas del mes a
partir de la celda E3.
Para ello siga el siguiente procedimiento:
- Haga clic en la celda E3
- Use la secuencia: <Datos> - <Consolidar>
- En el cuadro de lista <Función> debe decir Suma
- Haga clic en el cuadro de texto <Referencia>
- Seleccione el rango A3:B9. Luego haga clic en el botón
<Agregar>
- Seleccione el rango A11:B17. Luego haga clic en <Agregar>
- Seleccione el rango A19:B25. Luego haga clic en <Agregar>
- Repita lo mismo con el rango A27:B33
- Active la casilla <Fila superior> del Rótulo de etiquetas
- Haga clic en <Aceptar>
Es válido este resultado?. Como le falta la columna de la izquierda,
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 23 de 174
vamos a deshacer lo realizado haciendo clic en el botón Deshacer.
Ahora volvamos a usar <Datos> - <Consolidar …>
Active las dos opciones de rótulo <Fila superior> y <Columna
izquierda>; luego haga clic en <Aceptar>
Esta es una forma de consolidación en la cual se han “sumado” las
ventas de todas las semanas, uno al costado del otro.
Qué ocurre si sólo activa la opción <Columna izquierda> de las
opciones de <Rotulo>? Tiene sentido este resultado?.
Esta es otra forma de consolidar datos. En ella se ha obtenido el total
de las ventas mensuales por cada día. Aquí se han sumado las
ventas de todos los lunes, martes, etc.
Ejercicio 1
Usando los mismos rangos de datos, obtenga el Promedio de las
ventas por semana en las dos modalidades de consolidación.
Cuál de las dos formas de consolidación tiene sentido con esta
función?
Ejercicio 2
Use la función Max y Min para consolidar las ventas obteniendo los
resultados a partir de las celdas D3 y D11.
Ejercicio 3
Consolide en la hoja Resumen Semanal, las ventas semanales de la
hoja Ventas diarias.
Luego de haber seleccionado los rangos, debe tener la ventana que
se muestra en la Figura 19.
En esta forma de consolidar, podría usar la opción de <Crear
vínculo>?
Nota: Si tiene problemas con la opción de crear vínculo, deberá
eliminar las filas o columnas, si fuera necesario.
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 24 de 174
4.3. DE MÜLTIPLES HOJAS
Ejemplo 2
Como se dijo antes, se puede consolidar también datos
provenientes de diferentes hojas de cálculo.
Para ver este caso usaremos las hojas Semana 1, Semana 2,
Semana 3 y Semana 4 del mismo libro, Ventas diarias.xls. Vamos
a consolidar en la hoja Ventas del Mes.
Procedimiento:
Estando en la hoja Ventas del Mes, haga clic en la celda B3 (a partir
de esta celda estará la consolidación).
Use la secuencia <Datos> - <Consolidar …>
Vamos a consolidar usando la función Suma
Haciendo clic en el cuadro de texto <Referencia> haga clic en la
hoja Semana 1, luego seleccione el rango A2:B8 de esta hoja. Si es
necesario, modifique el rango que puede ya estar predefinido.
Haga clic en el botón <Agregar>.
Haga clic en la hoja Semana 2, luego clic en <Agregar>
Repita este procedimiento con las hojas Semana 3 y Semana 4.
Active las opciones <Fila superior> y <Columna izquierda>. Luego
haga clic en <Aceptar>.
Del mismo modo, haciendo clic en B12, por ejemplo, consolide el
promedio de las ventas, usando sólo la opción <Columna Izquierda>
Figura 19
Si algo sale mal, cambie los nombres de hoja por Sem1, Sem2, etc.
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 25 de 174
Nota:
Tenga cuidado de usar la opción crear vínculo.
Puede ser muy efectiva pero después no es fácil
deshacerlo.
Ejemplo 3
Abra el archivo Balance mes a mes.xls.
Inserte una nueva hoja de cálculo y muévalo hacia la derecha de de
la hoja Dic. Que esta nueva hoja se llame Balance anual.
a) Obtener una consolidación de totales en columna donde se
registra el balance de todos los meses.
b) Obtener una consolidación en el que se disponga el balance de
todos los meses.
Solución:
Caso a)
Haga clic en la celda de la hoja Balance anual, a partir de donde
quiere obtener los resultados de la consolidación.
Use la secuencia <Datos> - <Consolidar …>. Clic en <Referencias>
Haciendo clic en <Ene>, seleccione el rango B3:C52
Haga clic en <Agregar>
Repita estas dos líneas del procedimiento para todos los otros
meses. Luego active <Columna Izquierda> solamente. Clic en
<Aceptar>.
Caso b)
Haga clic en otra parte de la hoja
Use la secuencia <Datos> - <Consolidar …> Ya están definidos los
rangos.
Active adicionalmente la opción <Fila superior>, luego haga clic en
<Aceptar>.
Ejercicio 4
Inserte una nueva hoja al lado derecho de la última. Haga clic en A3.
Active la opción <Crear vínculo con los datos de origen> para una
nueva consolidación tomando en cuenta solamente <Columna
Qué ha ocurrido con las columnas? Puede usar una opción de Ordenar para resolver el problema
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 26 de 174
izquierda>. Cómo interpreta el efecto de esta opción?
Haga clic en el botón “+” del lado izquierdo. Allí se puede apreciar el
detalle de procedencia de los datos para esa celda consolidada.
Modifique ahora algún rubro del balance de Enero y Marzo, observe
si estos cambios afectan a la consolidación.
4.4. DE OTROS LIBROS
Cómo debemos proceder si se trata de consolidar datos
provenientes de hojas de diferentes libros?
Es común encontrar este tipo de problema en una empresa
corporativa. Los datos de una determinada operación realizada en
diferentes centros de acopio se están remitiendo como archivo o se
están modificando un único archivo compartido.
Si se trata de las ventas cuyos puntos de acopio remiten sus
archivos al final del día. Cómo se puede consolidar en un solo
archivo?
Veamos el siguiente ejemplo.
Ejemplo 4
Vamos a consolidar la información contenida en los archivos Datos
Venta Este.xls, Datos Venta Oeste.xls, Datos Venta Norte.xls y
Datos Venta Sur.xls en un nuevo libro que al grabarse se llamará
Ventas Consolidadas.xls.
Para ello siga el siguiente procedimiento:
Abra un nuevo libro vacío
Abra los archivos arriba mencionados
Haga clic en la celda B3 de la hoja 1, del nuevo libro.
Use la secuencia: <Datos> - <Consolidar …>
Haga clic en el cuadro de texto <Referencia>
Haga clic en el comando <Ventana> de la barra de Menú y
selecciones el libro Datos Venta Este
Seleccione el rango B3:I6
O haga clic en los nombres de los libros en la barra de tareas
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 27 de 174
Haga clic en el botón <Agregar>
Repita las cuatro últimas líneas seleccionando en cada caso los
otros libros.
Active las opciones de <Fila Superior> y <Columna Izquierda>
Haga clic en <Aceptar>
Grabe el libro de consolidación con el nombre Ventas
Consolidadas.
4.5. EJERCICIO
Abra el archivo Consolid01.xls
Grabe el mismo con el nombre Consolid02.xls
Obtenga consolidaciones en las hojas finales. En el primero una
consolidación total y en el segundo una consolidación mensual.
4.6. EJERCICIO
Abra el archivo Consolid01.xls
Obtenga un cuadro como el que se muestra en la figura 20.
4.7. EJERCICIO
Abra el archivo Consolidar meses.xls y consolide como se sugiere
en la últimas hojas.
Figura 20
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 28 de 174
CAPITULO II
INFORME DE TABLAS Y GRÁFICOS
DINÁMICOS. CONSOLIDACIÓN MÚLTIPLE.
CUBOS OLAP.
OBJETIVO
El objetivo de este Capítulo es proporcionar la información necesaria sobre Tablas y Gráficos
Dinámicos, Cubos OLAP y Consolidaciones múltiples a partir de informes dinámicos.
METAS
Al completar este Capítulo el participante estará en capacidad de
➢ Generar informes de tablas dinámicas
➢ Generar gráficos dinámicos a partir de tablas dinámicas
➢ Generar cubos OLAP usando el Asistente para cubos OLAP
➢ Obtener consolidaciones a partir de informes de tablas dinámicas
TEMAS A TRATARSE
1. INTRODUCCION
2. INFORMES DE TABLAS DINÁMICAS
2.1. DESDE UNA LISTA O BASE DE DATOS DE EXCEL
2.2. DESDE UNA FUENTE EXTERNA
2.3. RANGOS DE CONSOLIDACION MULTIPLES
2.4. GRAFICOS DINAMICOS
3. CUBOS OLAP
3.1. EJERCICIO 1
4. ANEXO 1: PREPARAR LOS DATOS PARA UN INFORME DINAMICO
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 29 de 174
1. INTRODUCCION
Toda empresa grande o pequeña, simple o compleja siempre está
manejando un conjunto de datos para generar nueva información en la
toma de decisiones, en el intercambio de datos o información, etc. En
consecuencia, todo empleado de la empresa estará manejando todo o
parte de la información o de los datos. Muchos de ellos generan la
información necesaria en forma de tablas o resúmenes para después
presentarlos a los niveles superiores de la empresa a fin de que ellos
puedan tomar decisiones correctas, precisas y oportunas.
Hoy en día las empresas corporativas generan grandes cantidades de
datos y dependen en gran medida de los informes que los empleados
preparan. Puesto que los requerimientos de información de cada
gerencia o departamento difieren uno de otro, la preparación de los
informes debe tomar en cuenta parte de los datos. Aquello que sea de su
interés..
Hasta hace una década, las dificultades de almacenamiento y velocidad
de proceso limitaban el manejo de grandes bases de datos. Sin embargo
el desarrollo tecnológico hace que estas empresas manejen grandes
cantidades de datos organizadas en bases de datos o tablas de gran
tamaño, tanto en número de registros como en los campos que la
definen. Los problemas de búsqueda y consulta requiere de modernas
formas de administración y gestión de datos. Tal es el caso de las
herramientas cada vez más conocidas y necesarias, tecnologías de
gestión de datos, como son DATA WAREHOUSE, DATA MART, etc.
Pero, volviendo a los informes, el responsable de ello debe obtener sus
informes en tablas, gráficos o resúmenes. Para ello debe resolver dos
tipos de conflictos:
✓ Cómo generar informes muy rápidamente
✓ Cómo generar un informe a partir de grandes cantidades de datos
En el presente módulo se resolverá estos problemas mediante el uso de
➢ Informe de tablas y gráficos dinámicos
➢ Cubos OLAP
➢ Consolidación múltiple
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 30 de 174
2. INFORME DE TABLAS DINAMICAS
Supongamos que Usted dispone de una gran cantidad de datos sobre las
ventas de cada producto que elabora la compañía. Ahora desea extraer
alguna información relevante para presentar su informe semanal. Para
ello se plantea resolver las siguientes interrogantes:
¿Cuáles son las ventas totales de cada producto por tienda?
¿Cuáles son las ventas totales de cada producto por región?
¿Qué productos se venden mejor en una semana?
¿Cuál fue la tienda o región con mayor volumen de ventas?
Etc.
Para resolver estas y muchas otras preguntas se dispone de las tablas
interactivas (Pívot Table), conocidas como Informe de Tablas Dinámicas.
Una Tabla Dinámica es una tabla con cierto diseño que le permite
visualizar la información de manera interactiva, en línea, al instante,
modificando su diseño a voluntad y requerimientos dinámicos. Y un
Informe de este tipo de tablas es lo que se conoce con el nombre de
Informe de Tablas Dinámicas.
Si Usted dispone de los siguientes datos de ventas
El uso de Tablas Dinámicas le permite generar el siguiente tipo de
informe que nos muestra las ventas totales por región.
Definición de
Tabla Dinámica
Figura 1
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 31 de 174
Podría generar también un informe de las ventas totales de cada
producto pero por región, como se muestra en la figura 3.
Igualmente podría generar las ventas totales por región pero detalladas
por vendedor, como se muestra en la siguiente figura.
O podría obtener un informe por región, pero detalladas por vendedor y
por cada producto. Esto es lo que se muestra en la siguiente figura 5.
Pero también, si las ventas estuvieran registradas por tiendas, podría
disponer de algunos de los tipos de informes de las figuras anteriores
pero agrupadas por tienda. En este caso, el quiebre de página se hace
por tienda. La siguiente figura muestra un informe en el cual en cada
página del informe se visualiza los totales de ventas por año, usando el
diseño de la figura anterior.
Figura 2
Figura 3
Figura 4
Figura 5
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 32 de 174
El programa Excel permite realizar todos los tipos de informes de tabla
dinámica descritos aquí y con muchas otras opciones, algunas de las
cuales las contemplaremos a continuación.
El Excel dispone de la opción <Informe de Tablas y Gráficos Dinámicos>
del comando <Datos> para obtener diversos tipos de informes como los
mostrados anteriormente y con muchas otras opciones.
Tipos de datos que se pueden utilizar
• Lista o base de datos de Excel
• Datos externos Por ejemplo, puede utilizar un archivo de base de datos, archivo de texto o datos de origen en Internet.
• Rangos de consolidación múltiples Puede combinar y resumir datos de varias listas de Excel.
• Otro informe de tabla dinámica
¿Va a utilizar los mismos datos para crear varios informes de tabla dinámica? Si es así, podrá ahorrar memoria y reducir el espacio en disco utilizando un informe de tabla dinámica existente para crear otro nuevo. De este modo, vinculará el informe de tabla dinámica original y el nuevo. (Por ejemplo, si actualiza los datos en el informe de tabla dinámica original, también se actualizarán los datos del nuevo informe y viceversa.)
Siga los siguientes pasos para generar sus informes y gráficos dinámicos
mediante el Excel:
Paso 0: Preparación de los datos
Antes de iniciar con el diseño del informe, se debe preparar los
datos. Esto significa que cualquiera que sea los datos a partir del
cual se desea generar algún tipo de informe, se debe adecuar los
datos bajo la concepción de una base de datos relacional; es
decir, que cada fila represente un registro y cada una de las
columnas represente un campo. Las columnas deben tener un
nombre adecuado, de preferencia que ocupe una celda dentro de
la fila.
Figura 6
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 33 de 174
Preparar los datos también significa el distinguir aquellos datos
que son cualitativos, como Región, Tienda, Departamentos,
fecha, nombre de productos, etc. Y los otros que son datos
cuantitativos, como totales, cantidad, costo, precio, etc. Los datos
cuantitativos pueden ser sumarizados; es decir, podemos sumar,
contar, promediar, encontrar su máximo o mínimo, etc. En
cambio los datos cualitativos sólo pueden ser contados; sirven
fundamentalmente para realizar referencias, fundamental para
los informes.
Elimine de la tabla de datos todos aquellos objetos como
gráficos, cuadros de texto, comentarios, botones, etc.
Paso 1: Use la secuencia <Datos> - <Informe de Tablas y Gráficos
Dinámicos>
Como se muestra en la
siguiente figura
A continuación obtendrá la siguiente ventana, que constituye la
ventana del asistente para crear Tablas y gráficos dinámicos.
En esta ventana tenemos la opción de usar
➢ datos provenientes de un libro de Excel
➢ datos provenientes de una fuente externa y
➢ datos provenientes de rangos de consolidación múltiples
Figura 7
Figura 8
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 34 de 174
Y los dos tipos de informes que se pueden obtener son
✓ Informe de Tabla dinámica
✓ Informe de gráfico dinámico
Después de hacer clic en el botón <Siguiente> se obtendrá una
pequeña ventana como se muestra en la figura 9.
Aquí se debe tomar en cuenta si el rango que se muestra es el
que le corresponde a todos los datos que van a ser incluidos en
el informe. Si así no fuera, haciendo clic en el cuadro de texto de
<Rango>, podemos seleccionar el rango manualmente; luego del
cual, se hará clic en <Siguiente>. Esto permitirá obtener la
ventana mostrada en la figura 10.
Ante todo se debe decidir si el informe debe ser emitido en la
misma hoja, donde están los datos o en una nueva hoja.
Naturalmente haremos clic en <Una hoja de cálculo nueva>.
En esta ventana se tiene la opción de pasar a una ventana donde
se debe diseñar le estructura del informe, en cuyo caso se debe
hacer clic en el botón <Diseño>. También podemos hacer uso
de <Opciones>. Por ahora sólo usaremos <Diseño>. Al hacer clic
en este botón, se obtiene la ventana que se muestra a
continuación.
Para que el
rango quede
seleccionado
en forma
automática,
primero debe
hacer clic al
interior de los
datos.
Figura 9
Figura 10
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 35 de 174
Según esta ventana podemos decir que un informe de tabla
contiene cuatro áreas o campos bien definidos:
El campo <Fila>, en donde se debe colocar el dato que debe
desplegarse hacia abajo.
El campo <Columna>, en el cual se puede colocar el campo que
queremos cruzar con el campo que está en el área de fila.
El campo <Página>, allí se debe insertar el dato que permitirá
hacer un quiebre. Esto permite obtener informes independientes.
El campo <Datos>. Este es el espacio reservado para colocar un
campo que va ser usado para sumar, contar, promediar, etc.
Nota:
Puede ocurrir que en algún tipo de informe se requiere más
de una variable en el área de columna. Si así fuera el caso,
se debe hacer clic en el botón <Opciones> de la figura 10.
La siguiente figura nos muestra un ejemplo de diseño en el que
parece ser que hay un error en la captura de la columna Monto.
Probablemente
aquí haya un
error en la
captura del dato
Monto.
Figura 11
Figura 12
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 36 de 174
Si lo que se desea es totales de la columna Monto en el área de
<Datos>, entonces deberá aparecer <Suma de Monto>. Si en
lugar de ello aparece <Cuenta de Monto>, es probable que dicha
columna contiene un dato de texto mas no un valor numérico.
2.1. DESDE UNA LISTA O BASE DE DATOS DEL EXCEL
Ejemplo 1
Abra el archivo Relación de Personal.xls
La figura 13 muestra una parte de este libro.
Bajo el concepto de base de datos, en este libro encontramos los
campos Cod Empl, Nombres, Puesto, Departamento, Sección,
Sueldo Anual, entre otros.
De esta lista sólo el campo Sueldo Anual puede ser usado para
totalizar, promediar, encontrar su varianza, el valor máximo, etc.
Siga el siguiente procedimiento para generar un informe de tabla
dinámica.
1. Haga clic al interior de los datos. Por ejemplo en B8.
2. Use la secuencia: <Datos> - <Informe de tabla y gráfico
dinámicos>
3. En la ventana siguiente deje activado: Lista o base de datos de
Excel y Tabla dinámica. Haga clic en <Siguiente>
4. En la siguiente ventana verifique si el rango es
. Si no lo fuera, seleccione todo el
rango de los datos incluyendo los nombres de columna. Luego
haga clic en <Siguiente>.
5. Active <Hoja de cálculo nueva> y haga clic en <Diseño>
Figura 13
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 37 de 174
6. En la ventana siguiente arrastre los botones de campo hacia
las áreas respectivas como se indica en la figura 14.
Arrastre los botones que se indican y suéltelos en las áreas
que se indican con la flecha. Al final, la ventana de diseño debe
quedar como sigue.
A continuación haga clic en <Aceptar> y en <Finalizar>
Con lo cual obtendrá el informe en una nueva hoja, como se
muestra en la siguiente figura 16.
Observe que el campo o área de página está vacía por cuanto
no insertamos ningún campo de datos.
Figura 14
Figura 15
Figura 16
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 38 de 174
El campo sección que se incluyó en el área de columna
muestra los valores de este campo que son: Copiadoras, Fax e
Impresoras.
El campo de datos Nombres se despliega hacia abajo y en
forma alfabética.
Y la variable que fue insertada en el área de Datos, Sueldo
Anual, figura en A3 como “Suma de Sueldo Anual” y sus
valores se muestran en la última columna del informe.
De este informe podemos decir que la empleada Abdul, Cathy
pertenece a la sección de Copiadoras y tiene como Sueldo
Anual la suma de 79306.56.
Como puede notar, hay dos objetos en la pantalla:
➢ La barra de herramientas de Tabla Dinámica
➢ La lista de campos
A continuación daremos una breve explicación de cada uno de
ellos.
Barra de herramientas de Tabla Dinámica
Como las otras barras de herramientas en el Office, ésta
permite realizar una serie de acciones sobre el informe que se
ha generado.
El primer botón <Tabla dinámica> permite actualizar datos, dar
formato, seleccionar, agrupar y mostrar detalle, crear fórmulas,
mostrar página, entre otros.
El segundo botón permite darle un determinado formato al
Si no está
activa, use
<Ver> -
<Barra de
Herramientas>
- <Tabla
dinámica>
Haga clic
aquí para
activar a este
cuadro
Figura 17
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 39 de 174
informe
El tercer botón permite construir gráficos a partir del informe
El último botón permite mostrar u ocultar la lista de campos
Cuadro de Lista de Campos
Este cuadro es muy útil en la modificación dinámica del diseño.
Este cuadro contiene la lista de los campos de datos.
Use los botones de campo para arrastrar a las diferentes áreas
del informe para añadir dicho campo.
Del mismo modo, haga clic en un botón de campo de datos del
informe y arrástrelo hacia el área de datos y suéltelo. De esta
forma quita un campo para poner otro o para modificar su
ubicación en el informe.
Ejercicio 1
Haga clic en el botón de Sección que está en el área de
Columna y arrástrelo hacia el área de fila y suéltelo en alguna
de las filas. El informe se verá como se muestra en la siguiente
figura 18.
Ejercicio 2
Ahora arrastre el botón de Sección hacia el área de página y
suéltela en ella. Del mismo modo, haga clic en el botón de
Departamento en el cuadro de la lista de campos y arrástrelo
hacia el área de fila. Allí debe soltarlo.
Figura 18
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 40 de 174
El resultado se verá como se muestra en la figura 19.
Ejercicio 3
Haga clic en el botón <Tabla dinámica> de la barra de
herramientas respectiva.
Haga clic en la opción <Mostrar página>
Como sólo se dispone de un campo en la lista siguiente, haga
clic en <Aceptar>.
Qué ha ocurrido?
Respuesta:
El informe relativo a cada uno de los valores del campo
Sección: Copiadoras, Fax e Impresoras, han sido puesto en
una nueva hoja independiente uno de otro.
Ejercicio 4
Abra el archivo VentasXP.xls
Haga un informe de tabla dinámica que permita mostrar la
información que se aprecia en la figura 20.
Figura 19
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 41 de 174
2.2. IMPORTACION DE DATOS
Las versiones anteriores del Excel permitían diseñar tablas dinámicas
usando la opción <Fuente de datos externa> de <Informe de tablas y
gráficos dinámicos> del comando <Datos>. Sin embargo, el uso de
esta opción, en la versión 2003 presenta serios inconvenientes pues
al realizar el procedimiento rigurosamente como lo indica el asistente
correspondiente, se llega a un error de conversión.
Expliquemos esto: Por la opción <Fuente de datos externa> el Excel
podía importar datos desde otras fuentes como archivos de texto,
datos provenientes del Access y muchas otras fuentes. Para ello el
Excel dispone de un motor de conversión para los diversos formatos.
El uso y selección de dicho motor (drivers) de conversión se realiza
durante el uso del asistente de conversión.
El proceso pasa por definir el formato en que se encuentran los
datos; para ello crea un archivo llamado schema.ini en donde
almacena los nombres de cada campo y su tipo. Al terminar la
conversión este archivo guarda los nombres de campo sin la
definición adecuada. Y como después el asistente termina cuando
recupera los datos convertidos y pretende colocarlos en una hoja del
Excel, no lo puede hacer pues la definición no se encuentra
adecuadamente guardada en el archivo mencionado.
Así las cosas, cómo podemos diseñar tablas dinámicas con datos
provenientes de fuente externa?
La versión 2003 del Excel dispone de una opción bastante completa
Figura 20
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 42 de 174
para resolver esta pregunta. Para ello debemos usar la secuencia:
<Datos> - <Obtener datos externos> -<Importar datos>.
Usaremos esta opción para primero importar los datos hacia una hoja
del Excel y, estando en formato Excel, pues usaremos la primera
opción: <Datos> - <Informe de tabla y gráfico dinámicos> - <Lista o
base de datos de Excel>.
Procedimiento para importar datos:
Use la secuencia: <Datos> - <Obtener datos externos> - <Importar
datos>
Entre los muchos tipos de archivo que se pueden importar tenemos:
MDB, DBF, PRN, TXT, CSV; etc.
En la ventana siguiente
Ubique el archivo y cambie el tipo según el tipo de archivo que desea
importar.
Siga completando la información que solicita el asistente de
conversión en las ventanas de diálogo que va abriendo.
Dependiendo del separador de campo que se haya usado en el
archivo, se deberá seleccionar el apropiado en la ventana que se
muestra a continuación.
Supongamos que se desea importar datos de un archivo cuyo
separador de campos es la coma; es decir “,”.
En esta ventana observe cómo se muestran los datos habiendo
elegido como separador de campo el tabulador.
Figura 21
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 43 de 174
Sin embargo al activar la casilla de verificación correspondiente a la
“coma”, podrá apreciar los datos como se muestra en la siguiente
figura.
Luego, en la ventana que siga, se deberá indicar el formato de los
datos. Use General cuando se tiene un campo numérico, convierta a
texto si el campo contiene datos literales o debe ser considerado de
tipo texto. Si el campo fuera fecha, pues deberá indicarlo, eligiendo
adecuadamente si es en formato DMA o MDA.
Finalmente los datos serán almacenados en la hoja de cálculo activa
y después de grabarlo con algún nombre, podrá disponer de ello para
diseñar su tabla dinámica.
Nota:
Antes de resolver propiamente los dos ejercicios siguientes, debe
importar los datos hacia una hoja del Excel.
Ejercicio 1
Genere un informe de tabla dinámica usando los datos del archivo
Figura 22
Figura 23
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 44 de 174
DataHouse Csv.csv. El diseño del informe debe contener el campo
Forma de pago en el área de fila, el campo Tienda en el área de
columna y en el área de datos debe estar el campo Monto.
Modifique el informe anterior de tal forma que en el área de datos se
tenga el Promedio del campo Cantidad
Ejercicio 2
Genere un informe de tabla dinámica usando los datos del archivo
DataHouse Esp.prn. Use el diseño empleado en el Ejercicio 1 y
además coloque en el área de Página el campo de datos Zona.
Haga que el informe del ejercicio anterior se muestre en páginas
diferentes para cada una de las zonas.
Recuerde que:
Debe usar la opción <Mostrar página> del botón de Tabla
dinámica, en la barra de Tabla dinámica.
2.3. RANGOS DE CONSOLIDACION MÚLTIPLES
Un informe de tabla o gráfico dinámico puede provenir también de
hojas de datos consolidados.
Si en el capítulo I hemos aprendido a consolidar y ahora disponemos
de un libro con varias hojas que son consolidaciones provenientes de
otras hojas (u otros libros), o simplemente tenemos un libro con
varias hojas, cómo podemos generar un informe de tabla o gráfico
dinámico a partir de estas múltiples hojas?
El programa Excel permite generar informes a partir de múltiples
hojas consolidadas o no.
ota: Sólo debe tenerse cuidado de elegir hojas no
consolidadas que tengan la forma de datos consolidados.
Ejemplo 1
Abra el archivo Ventas anuales.xls
Inserte una nueva hoja y colóquela al final de la hoja llamada
Año2005. Que esta nueva hoja se llame Reporte dinámico. Observe
que cada una de las hojas mantiene los mismos diseños de en la
presentación de los datos. Recuerde que esto era necesario para
Ojo con la
nota
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 45 de 174
realizar una consolidación.
Use el siguiente procedimiento para generar un informe de tabla
dinámica para el caso de múltiples hojas.
Procedimiento:
1. Active la hoja Reporte dinámico y ubique el cursor en A3
2. Use la secuencia <Datos> - <Informe de tablas y gráficos
dinámicos> (No se desespere ya veremos lo de gráficos)
3. Haga clic en la opción <Rangos de Consolidación múltiple> y
luego haga clic en <Siguiente>. Pasará a la ventana que se
muestra en la siguiente figura 24..
En esta ventana tenemos dos opciones:
➢ Crear un solo campo de página o
➢ Campos de página personalizados
El primero es el que Excel asume por omisión.
En el segundo se debe definir cada elemento que debe aparecer
como miembro en el campo de página. Hay un máximo de cuatro
elementos que se pueden incluir.
En este ejemplo usaremos la primera opción.
4. Haga clic en el botón <Siguiente> para pasar a la siguiente
ventana, la que se muestra en la Figura 25.
Puesto que esta ventana de diálogo es bastante familiar y nos
recuerda a una similar durante las etapas de consolidación, la
Figura 24
Figura 25
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 46 de 174
usaremos sin mayor explicación.
5. Haga clic en cada una de las hojas y defina el rango A3:B15;
haga clic en el <Agregar> y repita lo mismo para las otras hojas
hasta la que tiene por nombre Año2005.
6. Haga clic en el botón <Siguiente>. Esto le llevará a la ventana a
partir de la cual pasamos a diseñar el informe.
7. Haga clic en el botón <Diseño>. Lo que se obtiene es una
ventana similar a la siguiente figura 26.
Observe que ya están colocados los campos en cada una de las
áreas.
8. Haga clic en <Aceptar> y nuevamente clic en <Finalizar> de la
ventana siguiente.
La siguiente figura muestra el informe que se ha generado.
Grabe el libro con todos los cambios e informes con el nombre
Informe de Ventas.
Ejercicio 1
Muestre un informe sólo del Año2003
Ejercicio 2
En el informe del ejemplo 1, retire el campo Página 1 del informe. Si
obtiene resultado erróneo arregle el informe haciendo clic en el botón
Figura 26
Figura 27
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 47 de 174
de columna y desactivando <Cuenta de página 1>.
Ejercicio 3
Este es un ejercicio que debe ser desarrollado a partir de un libro que
contenga información práctica (de la empresa donde Usted labora). A
partir de dicho libro, genere un informe de tabla dinámica tomando en
cuenta que debe colocar un campo de dato en el área de página.
Luego haga que cada valor del campo página se muestre en una
hoja diferente. A continuación proceda a consolidar a partir de estas
hojas usando la opción <Rangos de consolidación múltiples>. Si
tiene alguna dificultad con los datos, elimine de cada hoja los
botones que le indican al Excel que se trata de hojas que
representan informes de tablas dinámicas.
2.4. GRAFICOS DINAMICOS
En cada ejemplo que se ha desarrollado hemos tenido la ocasión de
seleccionar la generación de un gráfico dinámico, en lugar de una
tabla dinámica.
Incluso después de haber generado un informe de tabla dinámico, se
puede generar un gráfico, a partir de dicho informe.
Esto último se puede realizar de manera directa, inmediata y sencilla
con solo hacer clic en el botón de gráfico de la barra de herramienta
de tabla dinámica.
Ejemplo 1
Abra el archivo DataHouse.xls y genere un informe con el diseño que
se muestra en la siguiente figura.
Gráficos
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 48 de 174
Figura 28
Haga clic en el botón del asistente para gráfico, de la barra de
herramienta Tabla Dinámica.
Obtendrá el siguiente gráfico.
Sin duda no es muy bueno que digamos. Es por la cantidad de
valores que tiene la variable NomProducto.
Como se hizo en un informe de tabla dinámica, arrastre el botón de
<Nomproducto> y suéltelo en el interior del área del gráfico. Lleve el
botón de <Zona> hacia el eje de categorías.
Ahora vuelva a la hoja donde tiene el informe. ¿Qué ha pasado?
Recuerde que todo esto es dinámico. Si modifica a un objeto (una
hoja de cálculo o libro es también un objeto), los otros también sufren
un cambio equivalente.
Figura 29
¡ Ojo !
También la
tabla se
modifica
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 49 de 174
Ponga el campo Tienda en al área de fila y Zona en el área de
columna. Luego vaya a la hoja donde se encuentra el gráfico. Lo que
se aprecia es un gráfico de columnas apiladas.
Retire el botón de Zona para tener un gráfico de columna relativo a
las ventas por tienda.
Ahora haga clic con el botón derecho para desplegar un menú
contextual, del cual debe seleccionar la opción <Formato de punto de
datos>. En la ventana que a continuación se obtiene, haga clic en la
ficha <Opciones>. Obtendrá una ventana similar a la siguiente figura.
En esta ventana reduzca el <Ancho del rango>; podría dejarlo en 30.
Haga clic en la casilla <Variar colores entre puntos>
Luego haga clic en <Aceptar>
Ejercicio
Abra el archivo Informe de Ventas.Xls
Para el informe allí obtenido genere un gráfico dinámico.
¿Qué ocurre con el gráfico si sólo selecciona un elemento en el área
de página del informe?
Figura 30
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 50 de 174
3. CUBOS OLAP
Introducción
Antes de hablar y generar un Cubo OLAP veamos algunos conceptos a
partir de los cuales tiene sentido hablar de Cubos OLAP.
DATA WAREHOUSE (Almacén de Datos)
Es una técnica para almacenar y manejar datos provenientes de varias
fuentes de una organización como un instrumento o medio de soporte a
la toma de decisiones.
Es también un repositorio de información, recogida de múltiples fuentes
almacenadas en un único esquema y que, normalmente reside en una
base de datos única.
Se mantienen en sistemas separados de las bases de datos
operacionales ya que ofrecen diferentes funciones y requieren diferentes
tipos de datos.
DATA MARTS
Es un conjunto de información contenida en un data warehouse y que
responde a las necesidades de un grupo de usuarios, por ejemplo cliente,
producto, ventas. Se implementan en sistemas departamentales de bajo
coste.
CUBO DE INFORMACION
Un cubo es una estructura para almacenar información proveniente de
grandes bases de datos o de otras estructuras más modernas de
almacenamiento como son los data warehouse y que permite realizar
análisis multidimensionales basados en ciertas formas de medición sobre
cruce de datos referenciales llamados dimensiones. La siguiente figura
es una muestra de cómo se entiende a un cubo de información.
Cubos OLAP
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 51 de 174
Cada cara del cubo permite visualizar ciertos datos como puede ser el
monto de la venta a través del tiempo (semanas, meses, trimestres,
años); regiones de ventas como Norte, Sur, Este; tipos de productos; etc.
Podemos estar interesados en construir un esquema de referencias
cruzadas (o jerarquizadas) de regiones con tipos de productos, por
ejemplo. En otros tipos de datos podemos crear esquemas jerarquizados
como el caso de consumo de energía eléctrica por departamento,
provincia, distrito y hogares. En este caso la variable que permite medir
es el monto de energía consumida. Se podría medir el promedio, el
consumo máximo o mínimo, etc.
Como se puede ver, estamos hablando de dos tipos de variable de datos:
Las que permite diseñar esquemas jerarquizados y aquellas que
permiten medir, contabilizar; es decir, sumarizar.
CUBO OLAP
Cuando estos cubos de información son procesados en línea para
generar diversos tipos de informes de tablas o gráficos dinámicos es
cuando hablamos de Cubos OLAP; es decir, el procesamiento de
analítico de datos en línea (On Line Analytical Processing).
Es una manera de organizar datos para que se ajusten al modo que
tienen los usuarios de analizarlos:
➢ En categorías jerárquicas y
➢ En valores de resumen previamente calculados
En Excel podemos construir cubos OLAP a través del uso de los
Informes de Tabla dinámicos y usando la opción: Fuente de Datos
Externa.
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 52 de 174
Ejemplo
Construir un cubo OLAP para la información contenida en el libro
Relación de Personal.xls
PREPARACIÓN DE LOS DATOS Abra el libro: Relación de Personal.xls
Agregue los campos: Apellido/Sección/Sueldo/Departamento al final de
las columnas.
Dar nombre a toda la tabla: GranTabla
Grabar como nuevo libro: Relación de Personal Ampliado.xls
A continuación presentamos el procedimiento de manera muy resumida.
Paso 1 Generación del Informe
<Datos>
<Informe de tablas y gráficos dinámicos>
<Fuente de datos externa> - <Siguiente>
<Obtener datos>
<Excel files> - <Aceptar>
Seleccionar unidad/carpeta/archivo - *.xls - <Aceptar>
Seleccionar GranTabla – Pasar a la derecha - <Siguiente>
<Siguiente>
<Siguiente>
Clic en <Crear un Cubo OLAP> - <Finalizar>
Paso 2 Asistente para Cubos OLAP(Analytical Processing On
Line)
<Siguiente>
Desactivar campos (CodEmpl) - <Siguiente>
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 53 de 174
Seleccione campos de datos y pase a la derecha como se muestra
<Siguiente>
Seleccionar una opción (última> - Seleccionar Unidad/Carpeta/y dar
nombre al cubo. La extensión de un cubo es *.CUB
<Siguiente>
<Finalizar>
Seleccionar Unidad/Carpeta/y luego dar un nombre a la consulta. Este
tendrá como extensión *.Oqy - <Siguiente>
Paso 3 Diseño dinámico del Informe
<Diseño>
Colocar las dimensiones en <Fila>
Colocar los campos resumen (Sueldo Anual) en el área de datos
Colocar campos de datos independientes en columna (los no
dimensionados)
<Siguiente>
<Finalizar>
Paso 4 Visualizar el informe dinámicamente
Clic en la dimensión colocada en fila
Clic en uno o más ítems (debe quedar doblemente seleccionado)
Paso 5 Gráfico
Clic en el asistente para gráfico (botón en la barra de Tabla dinámica)
Añadir/Cambiar/Retirar los botones del gráfico
Botón derecho sobre uno de los objetos – <Formato> Seleccionar
<Opciones>
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 54 de 174
Modificar ancho / Modificar colores
Botón derecho en el eje vertical – Modificar parámetros
Clic en los puntos del gráfico - Modificar su presentación tridimensional
3.1. EJERCICIO 1
Construya un cubo OLAP para la información contenida en el archivo
Data House.xls. Diseñe por lo menos cuatro dimensiones de hasta
tres niveles. Deje también campos individuales que permitan añadir
nuevos campos durante la generación del informe de tabla dinámico.
4. ANEXO 1: PREPARAR LOS DATOS PARA UN INFORME DINAMICO
Cómo preparar los datos
Para preparar los datos de un informe de tabla dinámica, lea la sección correspondiente al tipo de datos que está utilizando.
Preparar los datos de una lista o base de datos de Excel
Para preparar los datos de Excel, deberá tener en cuenta los siguientes puntos:
• Asegúrese de que la lista esté bien organizada ¡Es importante la claridad! Por ejemplo, asegúrese de que la primera fila de la lista contenga rótulos de columna ya que Excel utilizará estos datos para los nombres de campo en el informe. Asimismo, asegúrese de que cada columna contenga elementos similares; por ejemplo, incluya texto en una columna y valores numéricos en otra.
• Quite los subtotales automáticos No se preocupe porque el informe de tabla dinámica calculará los subtotales y totales generales.
• Si más adelante va a agregar más datos, asigne un nombre a los rangos Por lo tanto, cuando cree el informe de tabla dinámica, asegúrese de especificar el nombre al introducir el rango de datos de origen. De este modo, cada vez que agregue más datos al rango, podrá actualizar el informe de tabla dinámica para incluir los nuevos datos.
• Si desea utilizar datos filtrados, utilice el comando Filtro avanzado En el menú Datos, elija Filtro, haga clic en Filtro avanzado y, a continuación, ingrese el rango de datos; si usa nombre de rango, el filtrado será más versátil; active el botón Copiar a otro lugar. De este modo, se extraen los datos filtrados a otra ubicación en la hoja de cálculo y podrá utilizarlos en el informe de tabla dinámica. No filtre los datos en el mismo rango de datos ni utilice el comando Autofiltro. De este modo, algunos datos quedan ocultos pero el informe de tabla dinámica incluirá todos los datos de la lista.
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 55 de 174
Preparar los datos de un origen externo
Para preparar los datos externos, deberá tener en cuenta los siguientes puntos:
• Instale los controladores y herramientas necesarios Probablemente esté todo listo pero asegúrese de que tenga instalados Microsoft Query y los controladores ODBC (conectividad abierta de bases de datos) o controladores de origen de datos que necesite. Cuando cree un informe de tabla dinámica con el Asistente para tablas y gráficos dinámicas, utilizará Microsoft Query para recuperar los datos externos.
• En los siguientes casos, recupere los datos antes de iniciar el Asistente Compruebe la siguiente lista. Si contiene el método de recuperación de datos que utiliza Excel, no podrá utilizar Query desde el Asistente para tablas y gráficos dinámicos para recuperar los datos. En ese caso, siga las instrucciones que figuran a continuación para insertar los datos en un libro de Excel. A continuación, podrá utilizar el Asistente para seleccionar el rango de la hoja de cálculo que contenga los datos externos, al igual que en el caso de las listas de Excel.
Conexiones de datos de Office Los archivos de conexión de datos de Office (.odc) constituyen el método recomendado para recuperar datos externos para los informes. Podrá utilizarlos para recuperar datos de una sola tabla de base de datos o de un cubo OLAP, de modo que no necesite combinar los datos de más de una tabla en la base de datos externa, ni filtrar los datos para seleccionar determinados registros antes de crear el informe. Archivos de consulta y plantillas de informe Si desea utilizar un archivo de consulta (extensiones .dqy, .oqy o .rqy) para recuperar los datos, ábralo en Excel. Para utilizar una plantilla de informe (.xlt) que aún no incluya un informe de tabla dinámica, abra la plantilla. Consultas de parámetros. Si desea usar consulta de parámetros para recuperar los datos, debe crearla primero en Microsoft Query de modo que devuelva los datos a Excel. Observe que no puede crear una consulta de parámetros para recuperar datos de origen de bases de datos OLAP. Para obtener información sobre cómo crear consultas de parámetros, consulte la Ayuda de Microsoft Query. Consultas Web. Si desea utilizar una consulta Web para recuperar los datos a través de Internet, primero debe crearla y recuperar los datos en Excel. Para crear una consulta Web, elija Obtener datos externos en el menú Datos y, a continuación, haga clic en Nueva consulta Web.
Preparar los datos de otro informe de tabla dinámica
Para preparar los datos de otro informe de tabla dinámica, deberá tener en cuenta los siguientes puntos:
• Asegúrese de que ambos informes de tabla dinámica estén en el mismo libro Si el informe de tabla dinámica está en otro libro, copie el informe original al libro donde desee que aparezca el nuevo informe
• Compruebe la configuración de los campos de página En el informe de tabla dinámica original, es posible que haya cambiado la configuración de los campos de página de modo que recuperan
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 56 de 174
individualmente los datos externos de cada página. En ese caso, deberá restablecer los campos de página de modo que recuperen a la vez los datos externos de todos los elementos. Para comprobar la configuración, haga doble clic en cada campo de página y, a continuación, en Avanzado.
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 57 de 174
CAPITULO III
FUNCIONES MÁS COMUNES: MATEMÁTICAS,
LÓGICAS, ESTADÍSTICAS, DE TEXTO, DE
BASE DE DATOS, ESPECIALES,
FINANCIERAS. BÚSQUEDA EN TABLAS.
OBJETIVO
El objetivo de este Capítulo es proporcionar la información necesaria sobre las principales
funciones matemáticas, estadísticas, lógicas, de texto, base de datos y financieras, que posee el
programa Excel como parte de su librería.
METAS
Al completar este Capítulo el participante estará en capacidad de
➢ Usar funciones matemáticas como Entero(...), Redondear(...), Aleatorio.Entre(...), etc.
➢ Usar funciones las funciones lógicas: Si(...), Y(...), O(...)
➢ Usar funciones Estadísticas como Promedio(...), Max(...); Var(...), etc.
➢ Usar funciones de texto como Mayusc(...), NomPropio(...), Concatenar(...), etc.
➢ Usar funciones de base de datos como BDSuma(...), BDExtrae(...), BDMax(...), etc.
➢ Usar funciones especiales como Contar.Si(...),Sumar.Si(...),Indice(...), Coincidir(...), etc.
➢ Usar funciones financieras como Pago(...), Tasa(...), NPer(...), Tir(...)
➢ Usar funciones para búsqueda en tablas como son BuscarV(...) y BuscarH(...)
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 58 de 174
TEMAS A TRATARSE
Los temas a tratarse son los siguientes
1. FUNCIONES MATEMATICAS
2. FUNCIONES LOGICAS
3. FUNCIONES ESTADISTICAS
4. FUNCIONES DE TEXTO
5. FUNCIONES DE BASE DE DATOS
6. FUNCIONES FINANCIERAS
7. FUNCIONES ESPECIALES
8. FUNCIONES DE BUSQUEDA
9. ANEXO: FUNCIONES DE FECHA Y FUNCION TEXTO
1. INTRODUCCION
CONCEPTOS DE FUNCIÓN
Matemáticamente una función es una operación que permite obtener un determinado
resultado en base a la información proporcionada por un conjunto de argumentos y que
devuelve dicho resultado. Este resultado se recibe en una variable. El siguiente es la
notación usada para representar funciones:
Bajo la lupa
del Excel
Corresponderá
la celda activa
(donde está el
cursor).
Se hace clic en
la celda y se
digita la
función.
Este es el
nombre de la
función. Suma,
Max, Pago,
Aleatorio.Entre,
BDContar, etc.
Aquí se
colocan los
argumentos,
encerrados
entre
paréntesis y
separados por
comas.
Figura 1
),...,,(21 xxx n
fy =
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 59 de 174
En Excel se dispone de una gran cantidad de funciones organizadas todas ellas en 10
categorías: Matemáticas, Estadísticas, Financieras, Lógicas, etc.
Para usar una de estas funciones se dispone del Asistente para funciones, la que se
presenta como opción del comando <Insertar>.
Puede iniciar el uso del asistente usando <Insertar> - <Función> o haciendo clic en el icono
de la barra de fórmula.
Nota:
Si Ud. desea tenerlo como un botón dentro de la barra
Estándar (o la de formato), siga el siguiente procedimiento:
<Ver>-<Barra de herramientas>-<Personalizar>-<Comando>
Clic en <Insertar>. Del lado derecho arrastrar el botón
hacia una de las barras de herramientas y soltarlo.
A continuación se mostrará la ventana que se muestra en la figura 2.
En esta ventana se puede digitar, en el cuadro de texto <Buscar una función> o seleccionar
la categoría correspondiente en el cuadro de la lista haciendo clic en la flecha. En seguida
se listará todas las funciones relacionadas con el nombre que se digitó o las que
corresponden a la categoría seleccionada.
Luego de hacer clic en la función deseada y hacer clic en <Aceptar>, se pasará a una
ventana similar a la que se muestra en la figura 3.
El número de cuadros de texto que en ella se tenga dependerá del número de argumentos
de la función. Algunas como la función Aleatorio o la función Hoy no tienen argumentos,
Figura 2
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 60 de 174
pero sí es necesario el uso de ambos paréntesis.
Para ingresar el valor de cada argumento, será suficiente digitar el dato, la celda o el rango
de celdas, que constituye el valor de dicho argumento.
Puede hacer clic en su cuadro de texto y arrastrando la ventana a un costado, puede hacer
clic en la celda o rango de celdas que constituyen el argumento en cuestión.
Una función puede ser la única que se incluye en una fórmula. Una función puede ser parte
de una fórmula. Una función puede ser un parámetro de otra función.
Cualquiera que sea el caso, la fórmula debe ir precedida del signo igual, de un paréntesis o
a veces del signo “+”, aunque es más común el uso de del signo igual.
Observación:
En el desarrollo de Excel Avanzado, dejaremos de usar el
Asistente de funciones y más bien digitaremos las funciones.
Como facilidad, aprovecharemos la ventaja que nos proporciona
el Excel en su forma interactiva; es decir, al ingresar el nombre de
una función y digitar el paréntesis que abre, automáticamente
contaremos con la sintaxis en cuadro emergente, la que nos libera
del uso del asistente.
A continuación estudiaremos las funciones más comunes del Excel tomando en cuenta la
siguiente metodología.
A. Explicación de la misma
B. Sintaxis de la función
C. Ejemplo sencillo de aplicación
D. Algunas observaciones, si fueran necesarias
E. Otros ejemplos en segmentos de hoja
F. Al final del grupo de funciones de la misma categoría, un ejemplo y un ejercicio
Un
argumento
puede ser
una función.
Esta se
digitará
respetando
toda sus
sintaxis en el
cuadro de
texto.
Figura 3
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 61 de 174
2. FUNCIONES MATEMATICAS
2.1. FUNCIÓN ENTERO
Esta función devuelve la parte entera del número o contenido de la celda o de las
celdas1.
Sintaxis
=Entero(Número)
donde Número es un número, una celda o rango de celdas.
A B C D E F
1 Monto IGV(19%) Neto Entero(Neto)
2 Trimestre 1 3230 613.7 3843.7 3843
3 Trimestre 2 5780 1098.2 6878.2 6878
4 Trimestre 3 6270 1191.3 7461.3 7461
5 Trimestre 4 3580 680.2 4260.2 4260
6 Total
Ejemplos:
=Entero(D2)
=Entero(125.982)
2.2. FUNCIÓN REDONDEAR
Esta función redondea a Número en ndec decimales.
Sintaxis
=Redondear(Número,ndec)
donde Número es un número, una celda o rango de celdas.
Y ndec es un número entero.
El siguiente segmento de hoja muestra los resultados del uso de esta función.
A B C D E F
1 Monto IGV(19%) Neto Redondear(Neto)
2 Trimestre 1 3230 613.7 3843.7 3844
3 Trimestre 2 5780 1098.2 6878.2 6878
4 Trimestre 3 6270 1191.3 7461.3 7461.300
5 Trimestre 4 3580 680.2 4260.2 4260
6 Total
Trunca
decimales
Devuelve
3843
Devuelve
125
Figura 5
1 Generalmente cuando se desea tomar la parte entera de un rango de celdas lo que se hace es ingresar la
función en la primera celda y luego copiarla hacia las otras del rango. Pero también se puede hacer todo a la
vez. Para ello se debe dar el tratamiento de matriz a la fórmula. Si es así haga la siguiente: Seleccione el
rango donde debe quedar el resultado. Ingrese la fórmula =Entero(rango de datos). Presione
<CTRL>+<SHIFT>+<Enter>
Cómo
rango de
celdas?
Lea el pie
de página.
Figura 4
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 62 de 174
Ejemplos:
=Redondear(D2,0)
=Redondear(D4,3)
2.3. FUNCIÓN ALEATORIO
Devuelve un número decimal entre 0 y 1. No requiere de argumentos. El número de
dígitos decimales depende de la configuración del Excel.
Sintaxis
=Aleatorio()
Ejemplo
La sintaxis es en sí, un ejemplo.
Es útil en la generación de tablas de números aleatorios.
2.4. FUNCIÓN ALEATORIO.ENTRE
Esta función genera un valor numérico entero comprendido entre ValInicial y
ValFinal. Estos valores pueden ser celdas que contienen valores numéricos.
Sintaxis
=Aleatorio.Entre(ValInicial,ValFinal)
Ejemplos
=Aleatorio.Entre(10, 20)
=Aleatorio.Entre(5,25)/100
2.5. FUNCIÓN LOG (logaritmo)
Esta función devuelve el logaritmo de Valor en la base indicada por el segundo
argumento, Base. La función LOG10(…) es igual a LOG(…).
Sintaxis
=Log(Valor,Base)
donde Valor es el número o celda; Base es la base del logaritmo que se va a
emplear.
Ejemplo
=Log(10,10)
El siguiente segmento de hoja contiene una tabla en la cual se ha obtenido el
logaritmo de los números de la columna I, según la base indicada en cada una de
las columnas J, K, L y M.
Valor Base hacia la cual se obtiene el logaritmo
2 8 10 e
2016 10.9773 3.6591 3.3045 7.6089
2931 3.3321 6.1540 6.6788 3.9339
2463 6.4882 4.2976 4.1124 5.7016
4749 4.5272 5.8062 5.9871 4.8632
3432 5.3910 4.6283 4.5490 5.1469
Solución en
F2
Solución en
F4
Usado para
generar
decimales
de 0.05 a
0.25
Resultado
es 1
Log(2016,e)
?
Figura 6
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 63 de 174
2.6. FUNCIÓN EXP (Exponencial)
Devuelve el valor de eValor
donde e es la base de los logaritmos neperianos.
Sintaxis
=Exp(Valor)
donde Valor es un número o celda
Ejemplo
=Exp(1)
Observación
=Ln(Valor) es la función inversa de Exp(...). Devuelve el logaritmo neperiano
de Valor.
Ejemplo
=Ln(Exp(1))
2.7. FUNCIÓN RAIZ (raíz cuadrada)
Devuelve la raíz cuadrada de Valor.
Sintaxis
=Raiz(Valor)
donde Valor es un número o una celda
2.8. FUNCION SUMA (sólo daremos su sintaxis)
=Suma(Valor)
donde Valor es un número, una celda o rango de celdas.
2.9. EJEMPLO
Construya una hoja como la que se indica en el segmento de hoja anterior (Figura
6). Use la función aleatorio.entre para generar los valores de la primera columna.
Procedimiento:
a) En A1 ingrese el texto “Valor”
b) En B1 ingrese “Base hacia la cual se obtiene el logaritmo”
c) Seleccione B1:E1 combine y centre
d) En B2:E2 ingrese 2, 8, 10, =Exp(1). A E2 defina un formato personalizado
para que aparezca “e”.
e) En A3 ingrese =Aleatorio.Entre(1205,4873)
f) Copie y pegue en el rango A4: A7
g) En B3 digite: =Log($A3,B$2)
h) Copie esta fórmula hacia la derecha E3 y hacia abajo E7.
Devuelve el
valor de e
= 2.718282
Devuelve 1
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 64 de 174
2.10. EJERCICIO
En una hoja vacía ingrese en B2, “Calculo de devengados”. A4 ingrese “Fecha”. En
B4 ingrese “Entero”. En C4 ingrese “Red. 2 dec”. En D3 ingrese =Aleatorio(). Copie
hacia E3:G3. Que tenga formato porcentual con 2 decimales.
En D4 a G4 ingrese 2, 8, 10 y =Exp(1). Que en G4 aparezca “e”.
En A5 ingrese la siguiente fórmula:
=Aleatorio.Entre(Hoy(),”28/10/2009”)
Copie hacia abajo hasta completar 10 valores.
Obtenga las columnas B y C según indica su nombre
En la columna D, E, F y G calcule el Logaritmo del valor de la primera columna, en
la base según se indica, y multiplique por el porcentaje de la columna respectiva.
Ahora haga que la columna A tenga formato de Fecha
3. FUNCIONES LOGICAS
3.1. FUNCIÓN SI
El uso de esta función es muy frecuente en una hoja de cálculo. Por lo general
cuando se obtiene un resultado, éste se calcula de manera condicional. Tal es el
caso del cálculo del Impuesto a las utilidades. Se aplica el impuesto siempre que no
haya habido pérdidas; es decir, si la Utilidad antes de impuesto es positivo, entonces
se debe calcular el impuesto, en caso contrario el Impuesto es 0
Explicación
Esta función devuelve el resultado o valor de Expresion1 si la Condicion es
Verdadera; en caso contrario, devuelve el resultado o valor de Expresion2.
Sintaxis
=Si(Condición,Expresión1,Expresión2)
donde
Condición es una expresión que compara un valor con otro
Expresión1 y Expresión2 son expresiones o constantes reconocidos por el Excel.
Nota:
Expresion1, Expresion2 pueden contener, a su vez, a la misma función u otras
funciones válidas en el contexto.
Ejemplo 1
Supongamos que la celda A5 contiene 12. Si en B5 ingresamos:
=Si(A5>10,”Aprobado”,”Desaprobado”)
Respuesta:
Como la celda A5 contiene 12, la expresión literal de la función es: Como 12 es
mayor que 10, entonces pondrá en B5 la expresión “Aprobado”.
Y cuál habría sido el resultado si A5 tuviera el dato 5?. Allí se diría: Como 5 no es
mayor que 10, la comparación es falsa, luego en B5 pondrá “Desaprobado”.
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 65 de 174
Ejemplo 2
Dado los siguientes datos:
B C D
4 Monto imponible Impuesto Saldo Final
5 52035.60
6 28264.15
Cálculo de la columna C: Si el Monto imponible es mayor a 50000 el impuesto es
igual al 12% del Monto imponible; en caso contrario es el 8% del mismo.
La fórmula que se debe ingresar en C5 es:
=Si(B5>50000,0.12*B5,8%*B5)
Luego de copiar hacia la fila 6, los resultados se aprecian en el siguiente segmento
de hoja
Monto imponible Impuesto Saldo Final
52035.6 6244.272 45791.328
28264.15 2261.132 26003.018
Ejemplo 3
Usando los datos del ejemplo 2, suponga que ahora el impuesto se calcula usando
el siguiente criterio:
Si el monto imponible es negativo o cero, el impuesto es 0; si el Monto imponible es
mayor que 0 y menor o igual a 20,000 se aplica el 5% del Monto imponible; si es
mayor que 20,000 y menor o igual a 50,000 se aplica el 8%; si es superior a 50,000
se aplica el 12% del Monto imponible.
La función a digitarse en C5 es:
=Si(B5<=0,0,Si(B5<=20000,5%*B5,Si(B5<=50000,8%*B5,12%*B5)))
3.2. FUNCIÓN Y
Devuelve el valor lógico Verdadero si todas y cada una de las condiciones son
verdaderas. Si una de ellas no es verdadera, devuelve el valor lógico Falso.
Sintaxis
=Y(Condicion1,Condicion2,...,Condicionk)
donde Condicion1, Condicion2, ..., Condicionk son expresiones en las cuales se
establece un criterio de comparación entre un valor y otro.
Por lo general, esta función se usa como parte de la función Si.
Ejemplo 1
Si A5 = 12; B5 = 15; C5 = 10.
En lógica
es la
conjunción
:
p ^ q
p ^ q ^ r ^ s
Verdadero
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 66 de 174
=Y(A5>10,B5>10)
=Y(A5>10,B5>10,C5>10)
Ejemplo 2
Suponga que las notas de un alumno son las mismas que aparecen en el ejemplo 1.
Se desea obtener el promedio del alumno. Si no está aprobado en las dos primeras,
el promedio se obtiene tomando en cuenta la tercera prueba, en caso contrario se
desecha ésta.
=Si(Y(A5>10,B5>10),(A5+B5)/2,(A5+B5+C5)/3)
Cúal es el resultado si B5 = 10 y C5 = 15?
3.3. FUNCIÓN O
Devuelve el valor lógico Verdadero si todas o algunas de las condiciones es
verdadera. Es suficiente que una de ellas sea verdadera. Devuelve el valor lógico
Falso sólo si todas son falsas.
Sintaxis
=O(Condicion1,Condicion2,...,Condicionk)
donde Condicion1, Condicion2, ..., Condicionk son expresiones en las cuales se
establece un criterio de comparación entre un valor y otro.
Por lo general, esta función se usa como parte de la función Si.
Ejemplo 1
Si A5 = 12; B5 = 15; C5 = 10.
=O(A5>10,B5>10)
=O(A5>10,B5>10,C5>10)
Ejemplo 2
En el siguiente segmento de hoja se tiene el monto de compra que 5 clientes
realizaron un determinado Lunes.
Se trata de obtener una calificación para las ventas de ese día, usando el siguiente
criterio: Si por lo menos uno de los cinco clientes hizo una compra superior a los S./
500.0 entonces la calificación es “Venta exitosa” en caso contrario “Sin comentario”.
Falso
Devuelve
13.5
12.33
En lógica
es la
disyunción:
p v q
p v q v r v s
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 67 de 174
La fórmula que se debe ingresar en la celda B8 es:
=SI(O(B2>500,B3>500,B4>500,B5>500,B6>500),"Venta exitosa","Sin comentario")
Ejercicio
Qué fórmula debe ingresarse en B8 si la venta del día se califica como “Venta
exitosa” sólo si todas la ventas fueron superiores a S./ 500.0?
3.4. EJEMPLO
Abra el archivo Quinta Categoría.xls.
Resuelva el problema que allí se plantea.
Solución
De acuerdo a los datos, la celda I24 contiene el Monto total anual que debe ser
sometido al criterio del impuesto por quinta categoría.
Literalmente:
Como 37541.84 está en el rango 30,000 a 50,000 soles, el monto del impuesto será
el 8% del Monto total anual.
La fórmula a ingresar en I26 será:
=SI(I24<B26,0,SI(Y(I24>=A27,I24<B27),C27*I24,SI(Y(I24>=A28,I24<B28)
,C28*I24,C29*I24)))
3.5. EJERCICIO
Abra el archivo Quinta Categoría.xls. Presione <ctrl.>+z Valla al final de la hoja.
Resuelva el problema usando el criterio allí indicado.
4. FUNCIONES ESTADISICAS
4.1. FUNCIÓN CONTAR
Cambiar
O por Y
Figura 7
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 68 de 174
Devuelve el total de valores numéricos encontrado en el rango de celdas
especificadas en Valor.
Sintaxis
=Contar(Valor)
donde Valor constituye un rango de celdas
Cuenta el número de celdas que contienen números excluyendo aquellas que
contienen datos no numéricos o estén en blanco.
En el siguiente segmento de hoja
La fórmula que se ha usado es
=Contar(A2:C14)
Nota 1:
La función ContarA(Valores) permite contar celdas que
contienen valores numéricos o de texto; descartando claro
está aquellas que están vacías o en blanco.
Nota 2
La función Contar.Blanco(Valores) permite contar todas
las celdas que están en blanco o vacías, dentro del rango
especificado por Valores.
4.2. FUNCIÓN PROMEDIO
Devuelve el promedio de todos los elementos contenido en el argumento Valores.
Sintaxis
=Promedio(Valores)
donde Valores representa un o más valores numéricos o un rango de celdas.
Ejemplo 1
Si A5 = 10; A6 = 25; A7 = 20; A8 = 30, la fórmula
Figura 8
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 69 de 174
=Promedio(A5:A8)
Devuelve como resultado 21.25
Ejemplo 2
Si se digita las notas 12, 08, 16, 13, 18, 07 y 14 en el rango B10:B16 obtenga la nota
promedio en entero, con medio punto a favor del alumno
Solución
La función Promedio(B10:B16) devuelve el promedio con decimales; en este caso,
12.5714286.
Como se desea con medio punto a favor del alumno, 0.5714286 debe ser
incrementado en 0.5, con lo que se obtiene 13.0714. A este resultado le tomamos su
parte entera usando la función Entero.
La fórmula será: =Entero(Promedio(B10:B16)+0.5)
Nota:
Como estadísticas de posición tenemos también la Mediana y la Moda,
funciones que en Excel, tienen la siguiente sintaxis:
Mediana: =Mediana(Rango)
Moda : =Moda(rango)
4.3. FUNCIÓN VAR (varianza)
Devuelve la varianza de un conjunto de valores numéricos que pueden formar parte
del argumento o estar en un rango de celdas.
Sintaxis
=Var(Valores)
donde Valores representa uno o más valores o rango de celdas.
Ejemplo 1
Tomando en cuenta los datos del ejemplo 2 de la función, tenemos:
=Var(B10:B16)
Nota:
La raíz cuadrada de la varianza es la Desviación Estándar,
que, como función se tiene a =Desv.Est(Valores)
4.4. FUNCIÓN MAX (máximo)
Permite encontrar el máximo valor de todos aquellos especificados en los valores
dados como argumento o en el rango de celdas.
15.952381
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 70 de 174
Sintaxis
=Max(Valores)
donde Valores representa uno o más valores numéricos o un rango de celdas.
4.5. FUNCIÓN MIN (mínimo)
Permite encontrar el mínimo valor de todos aquellos especificados en los valores
dados como argumento o en el rango de celdas.
Sintaxis
=Min(Valores)
donde Valores representa uno o más valores numéricos o un rango de celdas.
El siguiente ejemplo presenta el uso de las funciones anteriores.
Ejemplo 1
En el siguiente segmento de hoja (Figura 9), se usan algunas de las funciones
estadísticas.
A continuación se da la secuencia para obtener los resultados.
Primero se da nombre de rango a B4:B10 como Ventas usando <Insertar> -
<Nombre> - <Crear> - <Aceptar>
Luego ingresar las siguientes fórmulas en las celdas respectivas:
En B12: =Suma(Ventas)
En B13: =Min(Ventas)
En B14: =Max(Ventas)
En B15: Promedio(Ventas)
En B16: =Var(Ventas)
En B17: =DesvEst(Ventas)
He aquí los resultados:
Ejercicio 1
Dado el siguiente segmento de hoja (Figura 10), calcule las filas y columnas
indicadas.
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 71 de 174
Ejercicio 2
Abra el archivo BanCordia.xls
Complete el cuadro con el valor de los estadísticos que se piden.
Recuerde que para encontrar el número de intervalos se debe usar la Ley de
Sturges: = 1 + 3.32 x Log(n)
4.6. FUNCIÓN FRECUENCIA
Permite obtener la frecuencia absoluta para cada uno de los intervalos en la tabla de
distribución de frecuencia.
Sintaxis
=Frecuencia(Rango_datos,Rango_Grupos)
donde
Rango_datos representa el rango de los datos
Rango_Grupos representa el rango de los límites superiores de todos los intervalos.
Nota Importante:
El tratamiento que se le da a esta función es matricial.
Por ello se debe tomar en cuenta los siguientes pasos:
a) Seleccionar el rango destino
b) Ingresar la función
Figura 9
Figura 10
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 72 de 174
c) Presionar <CTRL>+<SHIFT>+<ENTER>
Ejemplo
Abra el archivo BanCordia Agrupado.xls
Obtener el cuadro de distribución de frecuencia que allí se indica.
Luego obtenga un gráfico de barras con las marcas de clase como eje de
categorías.
Solución
➢ Siga el siguiente procedimiento:
➢ Que el rango B10:B849 se llame Saldo
➢ Obtener el máximo saldo en F24: =Max(Saldo)
➢ Obtener el mínimo saldo en F25: =Min(Saldo)
➢ Obtener el rango en F26 : =F24-F25
➢ Obtener número de intervalo en F27:
o =Entero(1+3.32*Log(Contar(Saldo,10))
➢ Obtener la longitud de intervalo en F28: = F26/F27
➢ En E11: =Min(Saldo)
➢ En F11: =E11+F28
➢ En E12: =F11
➢ En F12: =F12+$F$28
➢ Copiar E12:F12 hacia E13:F20
➢ En G11: =Promedio(E11:F11)
➢ Copiar G11 hacia G12:G20
➢ Seleccionar H11:H20
➢ Digitar: =Frecuencia(Saldo,F11:F20) No presione <Enter>
➢ Presionar <CTRL>+<SHIFT>+<Enter>
➢ Ingresar en I11: =H11/$H$21
➢ Copiar hacia H12:H20
➢ En J11 ingresar: =H11
➢ En J12 ingresar: =H12+J11
➢ Copiar J12 hacia J13:J20
➢ En K11 ingresar: =I11
➢ En K12 ingresar: =I12+K11
➢ Copiar de K12 hacia K13:K20
El gráfico:
➢ Seleccione el rango G11:H20
➢ Haga clic en el asistente para gráficos. Debe verificar que en el eje de
categorías esté la marca de clase. Con el botón derecho haga clic en una de las
barras del gráfico; clic en Formato de serie de datos; clic en <Opciones>;
reduzca la separación y haga clic en variar colores entre puntos
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 73 de 174
4.7. ESTIMACION.LINEAL
Esta es una de las funciones muy importantes y de especial interés en los temas de
Regresión de dos o más variables, ampliamente utilizado en modelos de regresión
en los cuales, para estimar sus parámetros o determinados coeficientes, se emplea
el Método de los Mínimos Cuadrados Ordinarios (MCO). Se fundamenta en lo
siguiente:
Dado el modelo Y = f (X1, X2, ..., Xk) expresado mediante
Y = β1X1 + β2X2 + ... + βkXk + β0
Se trata de encontrar los estimadores de los coeficientes βi a los cuales se les llama
parámetros de regresión.
Sintaxis
=Estimación.Lineal(ArgY,ArgX,ArgOrig,ArgTipo)
donde
ArgY : Es el rango de la variable dependiente (Vector Y)
ArgX : Es el rango de las variables independientes (Matriz X)
ArgOrig : 1 si se desea el valor de la constante (Intercepto), β0
ArgTipo : 1 Permite emitir el siguiente cuadro del ANOVA
Modo de usarlo:
a) Seleccionar el rango de salida. Si el modelo tiene k variables independientes y
se pide el valor del intercepto, entonces se debe seleccionar un rango de (k+1)
columnas por 5 filas.
b) Ingresar la función: =Estimacion.Lineal(RangoY,Rangos,1,1)
c) Presionar <CTRL>+<SHIFT>+<Enter>
El siguiente esquema muestra el orden de emisión de los resultados:
Xn Xn-1 X1 b Variables
mn mn-1 ... m1 b Coeficiente de cada variable
sen sen-1 ... se1 seb Error estándar de cada variable
r² Sey Coef.de determ. Error Est. Y
Fc Df F calculado Grados de lib.
Ssreg ssresid S.C.de la Regresión S.C.de residuales
La primera fila es un añadido no es parte del rango.
Si bien todo el cuadro es usado en un Análisis de Varianza, lo mínimo a
considerarse son los estimadores de los coeficientes (coeficiente de cada variable);
el coeficiente de determinación y el valor del estadístico F de Fisher (F calculado).
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 74 de 174
Ejemplo
Abra el archivo Regresion Multiple.xls
Use la función Estimacion.Lineal para obtener el cuadro del ANOVA arriba indicado
a partir de la celda B47.
Solución
Dar nombre de rango a B25:B44 como Y
Dar nombre de rango a C25:E:44 como X
Clic en B47
Seleccionar el rango B47:E51
Ingresar la fórmula: =Estimacion.Lineal(Y,X,1,1)
Presionar <CTRL>+<SHIFT>+<Enter>
Puesto que r² es 0.9875 entonces el grado de dependencia entre Y las columnas de
X, es bastante alta; es decir, el 98.75% de las veces Y se explica por el modelo.
Esto se ratifica observando el valor del Fc =421.6857.
Cuestión 1
Se puede saber cuál de las dos variables X depende más Y?
Cuestión 2
Hay algún grado de dependencia entre las X?
Ejercicio 1
En el mismo archivo, pero a partir de la fila 82 se plantea otro problema. Obtenga un
cuadro de ANOVA y diga si los niveles de venta de automóviles dependen de la
población y de la renta per cápita.
Ejercicio 2
Resuelva el problema que allí figura como OTRO SI DIGO.
4.8. FUNCIÓN COEF.DE.CORREL(Coef. de Correlación de X e Y)
Esta función da respuesta a la pregunta planteada en las dos cuestiones anteriores.
Sintaxis
=Coef.de.Correl(Arg1,Arg2)
donde
Arg1 y Arg2 son vectores o matrices en los cuales se encuentran las variables
sujetas a medir el grado de asociación entre ellas.
4.9. EJEMPLO
Obtenga el coeficiente de correlación entre cada una de las variables del primer
Lo
responde el
coeficiente
de
correlación.
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 75 de 174
modelo del archivo Regresion Multiple.xls.
Solución
Dar nombre a cada columna de dato. Yt, X1t, X2t y X3t.
A partir de la celda F23, ingrese el siguiente cuadro:
X1 X2 X3
Y
X1
X2
X3
En G25 digite: =Coef.de.Correl(Yt,X1t)
En H25 digite: =Coef.de.Correl(Yt,X2t)
En I25 digite : =Coef.de.Correl(Yt,X3t)
Complete las otras celdas del cuadro.
Al final obtendrá el siguiente resultado
X1 X2 X3
Y 0.98403 0.96733 0.99017
X1 1 0.98836 0.98932
X2 0.98836 1 0.98542
X3 0.98932 0.98542 1
Se puede apreciar que Y depende de X2 en menor grado que el de las otras.
Igualmente podemos decir que era suficiente obtener la triangular superior de esta
matriz para tener todos los coeficientes de correlación.
4.10. EJERCICIO
Abra el archivo Regresión Múltiple.xls y obtenga los coeficientes de correlación
entre todas las variables del problema propuesto y del Otro si digo.
Construya un gráfico de líneas para los tres problemas contenido en este archivo.
Agregue una línea de tendencia para el gráfico del primer problema de tipo
potencial, a partir de la serie Yt, usando extrapolación dos períodos hacia delante
y solicitando que incluya la ecuación el valor del r² en el gráfico.
5. FUNCIONES DE TEXTO
El programa Excel dispone también de una gran variedad de funciones para el
tratamiento de texto.
Nota:
Un texto es una cadena de caracteres formado por caracteres
literales, dígitos y algunos caracteres especiales.
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 76 de 174
Siendo algunas de estas funciones bastante sencillas, presentaremos su sintaxis, un
ejemplo directo y al final trataremos de incluir ejemplos en los cuales se aplique la
mayoría de ellas.
5.1. FUNCIÓN MAYUSC (mayúscula)
Devuelve a Texto en mayúscula.
Sintaxis
=Mayusc(Texto)
donde
Texto es una celda o una cadena de caracteres
Ejemplo
Si A5 = “centro de EsparCIMIENTO”
La fórmula: =Mayusc(A5)
devuelve CENTRO DE ESPARCIMIENTO
5.2. FUNCIÓN MINUSC (minúscula)
Devuelve a Texto en minúscula.
Sintaxis
=Minusc(Texto)
donde
Texto es una celda o una cadena de caracteres
Ejemplo
Si A5 = “centro de EsparCIMIENTO”
La fórmula: =Minusc(A5)
Devuelve: centro de esparcimiento
5.3. FUNCIÓN NOMPROPIO (Nombre propio)
Esta función convierte a Texto en mayúscula el primer carácter y minúscula los
siguientes. Si la cadena estuviera formado por varias “palabras”, cada una de ellas
empezará con mayúscula.
Sintaxis
=NomPropio(Texto)
donde
Texto es una celda o una cadena de caracteres
Ejemplo
Si A5 contiene “centro de esparcimiento La Coruña”,
La fórmula: =NomPropio(A5) devuelve
Centro De Esparcimiento La Coruña
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 77 de 174
5.4. FUNCIÓN CONCATENAR
Permite concatenar, añadir o juntar todas las cadenas contenidas como argumentos,
o el contenido de las celdas.
Sintaxis
=Concatenar(Texto1,Texto2,...,Textok)
donde
Texto1,Texto2,...,Textok es una celda o rango de celdas, o cadenas de caracteres
Ejemplo 1
Si D5 = “márquez”; E5 = “gonzález” y F5=”jessica ruth”, la fórmula
=Concatenar(D5:F5) devuelve márquezgonzálezjessica ruth
Ejemplo 2
Usando los datos del ejemplo 1, la fórmula
=Concatenar(D5,” “,E5,”, “,F5) devuelve el texto
márquez gonzález, jessica ruth
Si a este resultado le aplicamos la función NomPropio, mediante
=NomPropio(Concatenar(D5,” “,E5,”, “,F5))
Obtendremos
Márquez González, Jessica Ruth
Ejercicio
Abra el archivo Funciones de texto 1.xls
Obtenga la columna D de la hoja concatenar.
Use la función NomPropio de la hoja Nompropio para arreglar los datos.
5.5. FUNCIÓN LARGO
Devuelve la longitud en caracteres de la cadena contenida en una celda o como
argumento.
Sintaxis
=Largo(Texto)
donde
Texto es una celda o cadena de caracteres
Ejemplo
Si A5=”Universidad de Lima”, la fórmula
=Largo(A5)
Devuelve 19 caracteres.
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 78 de 174
5.6. FUNCIÓN IZQUIERDA
Extrae los n primeros caracteres de Texto.
Sintaxis
=Izquierda(Texto, n)
donde
Texto es una celda o cadena de caracteres
n es un número entero; es el número de caracteres a extraerse
Ejemplo 1
Si A5 contiene “Universidad de Lima”; la fórmula
=Izquierda(A5,11)
Devuelve Universidad
Ejemplo 2
Si B10 = “Universidad de Lima”; D10=”Ice”; la fórmula
=Mayusc(Izquierda(Concatenar(B10,” “,D10),Largo(D10))
devuelve UNI
5.7. FUNCIÓN DERECHA
Extrae los últimos n caracteres de Texto.
Sintaxis
=Derecha(Texto,n)
donde
Texto es una celda o cadena de caracteres
n es un número entero; es el número de caracteres a extraerse
Ejemplo 1
Si A5 contiene “Universidad de Lima”; la fórmula
=Derecha(A5,4)
Devuelve Lima
Ejemplo 2
Si B10 = “Universidad de Lima”; D10=”Ice”; la fórmula
=Mayusc(Derecha(Concatenar(B10,” “,D10),3*Largo(D10)-1))
Devuelve LIMA ICE
5.8. FUNCIÓN ENCONTRAR
Devuelve la posición a partir de la cual se encuentra incluido el primer argumento,
dentro del segundo.
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 79 de 174
Sintaxis
=Encontrar(Texto_Buscado,Dentro_de_Texto,Iniciando_En)
donde
Texto_Buscado es la cadena de caracteres que se busca y que debe estar
contenida en el segundo argumento.
Dentro_de_Texto es la celda o cadena de caracteres que contiene a
Texto_Buscado.
Iniciando_En es un número entero que indica la posición inicial a partir del cual inicia
la búsqueda.
Nota 1:
La cadena que se busca debe estar totalmente
contenida en la cadena donde se busca
Nota 2:
Si no se especifica el tercer argumento, la búsqueda se
inicia en el primer carácter del segundo argumento.
Ejemplo 1
Si A5 = “Diversidad en la unidad”; D5=”el cambio”; la fórmula
=Encontrar(“la”,A5)
devuelve 15
Ejemplo 2
Usando los datos del ejemplo 1, la fórmula
=Izquierda(A5,Encontrar(“la”,A5)-1)
devuelve “Diversidad en”
Ejemplo 3
Usando los datos del ejemplo 1, la fórmula
=Concatenar(Izquierda(A5,Encontrar(“la”,A5)-1),” “,D5)
devuelve “Diversidad en el cambio”
5.9. FUNCIÓN EXTRAE
Permite extraer de Texto, n caracteres a partir de Pos_Inicial
Sintaxis
=Extrae(Texto,Pos_Inicial,n)
donde
Texto es la celda o cadena de caracteres desde donde se extraerá
Pos_Inicial número entero que indica a partir de dónde se inicia la extracción
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 80 de 174
n indica el número de caracteres que serán extraídos.
Ejemplo 1
Si A5 = “Diversidad en la unidad”, D5 = “el cambio”, la fórmula
=Extrae(A5,Largo(D5)+1)
devuelve “Diversidad”
Ejemplo 2
Usando los datos del ejemplo 1, la fórmula
=EXTRAE(A5,ENCONTRAR("la",A5),LARGO(D5))
devuelve “la unidad”
Ejemplo 3
Usando los datos del ejemplo 1, la fórmula
=Concatenar(EXTRAE(A5,ENCONTRAR("la",A5),LARGO(D5)),” en “,D5)
Devuelve “la unidad en el cambio”
5.10. EJEMPLO
Abra el archivo Funciones de Texto 1.xls
Obtenga las columnas que se indican en la hoja Partes.
Solución
Haga clic en la Hoja Partes
Columna B
En B3 se debe obtener lo mismo que contiene A3, pero usando la función
NomPropio.
Digite en B3: =NomPropio(A3)
Columna C:
Para extraer el apellido paterno se puede usar la función Izquierda, cuántos
caracteres? Si se logra conocer la posición del espacio en blanco “ “, y a este le
restamos 1, tendremos la cantidad de caracteres que debe extraerse con la
función izquierda.
Digite en C3: =Izquierda(B3,Encontrar(“ “,B3)-1)
Columna D:
Ahora se trata de extraer el apellido materno. Este empieza después del espacio
en blanco, su posición lo da Encontrar(“ “,B3).
Del mismo modo, Encontrar(“,”,B3) -1 nos entrega la última posición del apellido
materno.
Si se resta Encontrar(“,”,B3)-Encontrar(“ “,B3)-1, tendremos la cantidad de
caracteres que debe extraerse. Por ello
Digite en D3: =Extrae(B3,Encontrar(“ “,B3)+1,Encontrar(“,”,B3)-Encontrar(“ “,B3)-1)
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 81 de 174
Columna E:
Ahora se trata de extraer los nombres. Para ello se debe usar la función derecha.
El número de caracteres que se debe extraer es igual a la longitud total de B3
(Largo(B3)) menos la posición de “,” (Encontrar(“, “,B3)). Por ello
Digite en E3: =Derecha(B3,Largo(B3)-Encontrar(”,”B3)-1)
Ahora copie las fórmulas para las otras filas.
5.11. EJERCICIO 1
Abra el archivo Funciones de Texto 1.xls
Obtenga lo que se pide en las columnas F, G y H de la hoja Extraer
Tome nota de que el año de ingreso se encuentra en el código del empleado.
5.12. EJERCICIO 2
Tomando en cuenta la hoja Ejercicio del libro Funciones de Texto 1.xls resuelva la
pregunta que allí se plantea.
Tome nota que el separador entre es el espacio en blanco. Se le sugiere que use
columnas temporales para extraer una parte y a partir de ella obtener lo que se
pide. Finalmente debe pasar a valor todas las fórmulas y eliminar las columnas de
trabajo.
6. FUNCIONES DE BASE DE DATOS
INTRODUCCION
Las funciones de la categoría de Base de Datos son aquellas que nos permiten obtener
ciertos resultados estadísticos basados en algún tipo de criterio.
Como en el caso de las funciones estadísticas, podemos contar, sumar, promediar;
encontrar el máximo, el mínimo, la varianza o la desviación estándar del conjunto de
registros de una base de datos, en el cual, algún campo en particular cumple con algún
criterio de selección.
Se distinguen de las mismas funciones estadísticas por cuanto el resultado que se
obtiene es condicional.
Los nombres de las funciones son los mismos que las funciones estadísticas, excepto
que se antepone “BD” a cada nombre.
Todas
ellas
empiezan
con BD
Todas ella
tienen los
mismos
argumentos
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 82 de 174
La sintaxis de todas ellas presenta el siguiente esquema
=BDNombre(Rango_Datos,Nombre_Campo,Rango_Criterio)
donde
Rango_Datos es el rango donde están todos los datos
Nombre_Campo es el nombre de campo o de columna, que va a ser sometido a la
acción de la función
Rango_Criterio es el rango que incluye dos celdas (dos filas): la primera contiene el
nombre de campo y la segunda contiene el valor, usado para filtrar la acción a llevarse a
cabo sobre el Nombre_Campo.
Nota:
La primera columna del Rango_Datos debe contener el
Nombre de campo tomado en cuenta en la primera celda del
Rango_Criterio.
A continuación las describiremos a cada una de ellas y en el ejemplo general
resolveremos un problema de aplicación.
6.1. FUNCIÓN BDSUMA
Permite encontrar la suma de todos los datos contenidos en el campo indicado por
el segundo argumento, y que cumplen con el criterio indicado en el tercer
argumento.
Sintaxis
=BdSuma(Rang_Datos,Nombre_Campo,Rango_Criterio)
donde los argumentos cumplen con lo dicho en la introducción.
Ejemplo
Dado el siguiente segmento de hoja,
A B C D E 1 Producto Tienda 1 Tienda 2 2 Papaya 5060 5210 3 Melocotones 4580 6370 4 Naranja 5190 4600 5 Naranja 7300 6620 6 Melocotones 8100 8210 7 Melocotones 6570 8070 8 Manzana 5430 6140 9 Melocotones 7880 8260 10 Naranja 5900 6540 11 Papaya 7980 4870 12 Naranja 7810 5990 13 Papaya 6920 7880 14
El segundo
argumento
puede ser
una celda
que
contiene al
nombre
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 83 de 174
15 Total Nro. Ventas Máxima Mínima 16 Producto 17 Melocotones
Encuentre el total de ventas de Melocotón en la tienda 2
Solución
De acuerdo a la forma cómo están ubicados los datos, use A1:C13 como rango de
datos, A16:A17 como rango de criterio y la celda C1 ya que contiene el nombre de
campo Tienda 2.
En B16 digite =BDSuma(A1:C13,C1,A16:A17)
6.2. FUNCIÓN BDCONTAR
Permite contar todos los registros contenidos en el campo indicado por el segundo
argumento, y que cumplen con el criterio indicado en el tercer argumento.
Sintaxis
=BdContar(Rang_Datos,Nombre_Campo,Rango_Criterio)
donde los argumentos cumplen con lo dicho en la introducción.
Ejemplo
Dado el segmento de hoja dado en la función BDSuma, cuántas ventas de
melocotón se realizaron en la Tienda 1?
Digte en C16: =BDContar(A1:C13,”Tienda 1”,A16:A17)
6.3. FUNCIÓN BDMAX
Permite obtener el máximo valor de todos los registros relativos al campo indicado
por el segundo argumento, y que cumple con el criterio indicado en el tercer
argumento.
Sintaxis
=BDMax(Rang_Datos,Nombre_Campo,Rango_Criterio)
donde los argumentos cumplen con lo dicho en la introducción.
Ejemplo
Dado el segmento de hoja dado en la función BDSuma, obtenga la máxima venta
realizada en la Tienda 2.
En D16 digite: =BdMax(A1:C13,”Tienda 2”,A16:A17)
6.4. FUNCIÓN BDMIN
Permite obtener el mínimo valor de todos los registros relativos al campo indicado
por el segundo argumento, y que cumple con el criterio indicado en el tercer
argumento.
Sintaxis
Resultado:
30910
Respuesta:
8260
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 84 de 174
=BDMin(Rang_Datos,Nombre_Campo,Rango_Criterio)
donde los argumentos cumplen con lo dicho en la introducción.
Ejemplo
Dado el segmento de hoja dado en la función BDSuma, obtenga la máxima venta
realizada en la Tienda 2.
En D16 digite: =BdMin(A1:C13,”Tienda 1”,A16:A17)
6.5. FUNCIÓN BDVAR
Permite obtener la varianza de los valores relativos al campo indicado por el
segundo argumento, y que cumplen con el criterio indicado en el tercer argumento.
Sintaxis
=BDVar(Rang_Datos,Nombre_Campo,Rango_Criterio)
donde los argumentos cumplen con lo dicho en la introducción.
Ejemplo
Dado el segmento de hoja dado en la función BDSuma, obtenga la varianza de las
ventas realizada en la Tienda 2.
En D16 digite: =BdVar(A1:C13,”Tienda 1”,A16:A17)
6.6. FUNCIÓN BDEXTRAER
Permite extraer el valor del campo indicado por el segundo argumento, que cumple
con el criterio indicado en el tercer argumento.
Sintaxis
=BDExtraer(Rang_Datos,Nombre_Campo,Rango_Criterio)
donde los argumentos cumplen con lo dicho en la introducción.
Ejemplo
Dado el segmento de hoja dado en la función BDSuma, en A16 digite “Tienda 2”; en
A17 digite =Max(C2:C16). Qué producto tuvo la máxima venta en la tienda 2?.
Para responder a esta pregunta En D16 digite
=BdExtraer(A1:C13,A1,A16:A17)
6.7. EJEMPLO
Abra el archivo Ventas Centro Plaza.xls.
En la hoja Venta diaria se registra la venta de 42 productos. Se trata de obtener
estadísticas de resumen respecto al Stock actual de cada uno de estos productos.
Se pide completar el cuadro que se muestra a partir de la columna R.
Solución
Respuesta:
4580
Respuesta:
2612158
Respuesta:
Melocotones
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 85 de 174
Como se puede apreciar, se trata de encontrar el total, número de ventas, máximo,
mínimo y el promedio del Stock para cada uno de los productos.
Para ello haga lo siguiente:
Dar nombre Productos al rango A4:O2024
Seleccione el rango R4:W46. Copie. Usando el botón derecho, haga clic en la celda
Y2 ; seleccione Pegado Especial; seleccione la casilla <Transponer> y luego haga
clic en <Aceptar>.
Observe que este formato se presta para lo que se pide. Sólo falta ingresar el
nombre del campo producto (NomProducto) en la fila 1, para cada uno de los
productos (Nombre de campo repetido).
Corte el contenido de Y2 y pegue en el rango Z1:BO1.
Digite StkActual en Y2.
Ahora, en Z3, ingrese la fórmula: =BdSuma(Productos,$Y$2,Z1:Z2)
En Z4: =BDCONTAR(Producto,$Y$2,Z1:Z2)
En Z5: =BDMAX(Producto,$Y$2,Z1:Z2)
En Z6: =BDMIN(Producto,$Y$2,Z1:Z2)
En Z7: =BDPROMEDIO(Producto,$Y$2,Z1:Z2)
Copie el rango Z3:Z7 hacia AA3:BO7
Copie el rango AA3:BO7 y pegue sobre sí mismo como valor.
Ahora seleccione el rango Y2:BO7; copie; haga clic con el botón derecho sobre la
celda R4; active la casilla Transponer y luego clic en <Aceptar>.
6.8. EJERCICIO 1
Abra el archivo-libro Funciones Base Datos.xls.
Este libro contiene una lista de pedidos realizados a la Empresa TRANSFERSA,
desde una diversidad de empresas cuya identificación se registra. Ellos solicitan
que se les atienda sus pedidos sobre dos categorías de productos: Café o Té. Se
tiene la cantidad de pedido, el precio por unidad y el total del pedido. En base a la
información contenida en este archivo, resuelva las siguientes preguntas:
P1. Defina todo el cuadro (Rango A6:H371) como Tabla: <Insertar> - <Nombre> -
<Definir> - Digite Tabla - <Aceptar>
P2. Defina el rango A1:H2 con el nombre Criterio1
P3. Defina como Criterio2 al rango A1:H3
P4. Copie la cabecera de la BD (A6:H6) hacia las filas 380, 385, 390, 395 y 400.
Defina los rangos con el nombre indicado (Criterio3, Criterio4, …) incluyendo
una fila adicional. En el último caso incluya dos filas adicionales.
Resuelva las siguientes preguntas, usando las celdas adecuadas según los criterios:
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 86 de 174
P5. Cuántos pedidos hizo la empresa con identificación BERGL?
=BDCONTARA(Tabla,”ID Empresa”,Criterio1) o también:
=BDContara(Tabla,3,C1:C2)
P6. Cuántos pedidos de Té hicieron BERGL o MEREP?
Antes ingresemos “Té” en E2. Ahora ingresamos la fórmula:
=BDContara(Tabla,3,Criterio2) o también
=BDContara(Tabla,3,C1:E4)
P7. A cuánto asciende el total de los pedidos de Té, de la empresa BERGL?
P8. Cuánto obtendría TRANSFERSA por la venta de Té a estas dos empresas?
P9. A cuánto asciende el total de pedidos de SAVEA, atendidos por Garnier?. Use
Criterio 3
P10. A cuánto asciende el máximo pedido hecho por BLAUE?. Use Criterio 4
P11. A cuánto asciende el máximo pedido de Café?. Use Criterio 5.
P12. Quién atendió el máximo pedido de Café?. Use Criterio 6. (Total ¿?)
6.9. EJERCICIO 2
Abra el archivo Estadist Pedidos.xls
Obtenga la columna de Monto Neto
Obtenga las mismas estadísticas tomadas en cuenta en el Ejemplo anterior para los
primeros 20 productos.
7. FUNCIONES FINANCIERAS
INTRODUCCION
El programa Excel dispone de una gran variedad de funciones para resolver problemas
financieros.
Dependerá del interés que tenga el usuario respecto a la importancia de las funciones.
7.1. FUNCIÓN PAGO
Devuelve el pago de un préstamo basándose en pagos constantes y a una
tasa de interés constante.
Sintaxis
=Pago(Tasa,NPer,Va,[VF],[Tipo])
Donde Tasa es el tipo de interés del préstamo; NPer es el número total de pagos del
préstamo; Va es el valor actual, o lo que vale ahora, la cantidad total de una serie de
pagos futuros, conocido también como el Principal; Vf es el valor futuro o un saldo
en efectivo que desea lograr después de efectuar el último pago. Si se omite se
asume que el valor futuro de un préstamo es 0.
Tipo es un valor que indica el vencimiento de los pagos. Si es 0, los pagos se
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 87 de 174
efectúan al final del período; si es 1 se efectúa al inicio.
Nota 1:
El monto devuelto por Pago incluye el capital y el interés pero no
incluye impuestos, pagos en reserva ni gastos que a veces se asocia
con un préstamo.
Nota 2:
Tanto Tasa como NPer deben estar expresados en la
misma unidad; es decir, si Tasa es un porcentaje anual,
Nper debe indicar años, por lo tanto la función devolverá
los pagos anuales que deben realzarse.
Ejemplo
La siguiente fórmula devuelve el pago mensual de un préstamo de $10000 con una
tasa de interés anual del 8% pagadero en 10 meses:
PAGO(8%/12, 10, 10000) es igual a $ -1,037.03.
Usando el mismo préstamo, con pagos vencen al comienzo del período, el pago es:
PAGO(8%/12, 10, 10000, 0, 1) es igual a $ -1,030.16.
La siguiente fórmula devuelve la cantidad que se le deberá pagar cada mes, si
presta $ 5,000 durante un plazo de cinco meses a una tasa de interés del 12%:
PAGO(12%/12, 5, -5000) es igual a $1030.20
Se puede utilizar PAGO para determinar otros pagos anuales. Por ejemplo, si desea
ahorrar $ 50000 en 18 años, ahorrando una cantidad constante cada mes, se puede
usar PAGO para determinar la cantidad que se debe ahorrar. Asumiendo que podrá
devengar un 6 % de interés en su cuenta de ahorros, puede usar PAGO para
determinar qué cantidad debe ahorrar cada mes:
PAGO(6%/12, 18*12, 0, 50000) es igual a $ -129.08
Si deposita $ 129.08 cada mes en una cuenta de ahorros que paga el 6% de interés,
al final de 18 años habrá ahorrado $ 50,000.
7.2. FUNCIÓN TASA
Devuelve el tipo o tasa de interés por período de una anualidad.
Sintaxis =TASA(Nper,Pago,Va,Vf,Tipo)
Si desea que el resultado sea positivo, use la función Abs.
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 88 de 174
Ejemplo
Para calcular la tasa de un préstamo de $ 8,000 a cuatro años con pagos mensuales
de $ 200:
TASA(48, -200,8000) es igual a 0.77 por ciento
Esta es la tasa mensual ya que el período es mensual. La tasa anual es 0.77%*12,
que es igual a 9.24 por ciento
7.3. FUNCIÓN NPER
Devuelve el número de períodos de una inversión basándose en los pagos
periódicos constantes y a una tasa de interés constante.
Sintaxis
=NPER(Tasa; Pago; Va; Vf; Tipo)
La descripción dada para los argumentos de las funciones anteriores, es válida en
esta función.
Ejemplo 1 NPer(12%/12, -100, -1000, 10000, 1) es igual a 60
NPer(1%, -100, -1000, 10000) es igual a 60
NPer(1%, -100, 1000) es igual a 11
Ejemplo 2
A B
1 Datos Descripción
2 12% Tasa de interés anual
3 -100 Pago efectuado en cada período
4 -1000 Valor actual
5 10000 Valor futuro
6 1 El pago vence al principio del período
El número de períodos de la inversión se obtiene usando:
=NPer(A2/12,A3,A4,A5,1) que devuelve 60.
Cuando los pagos se efectúan al final del período:
=NPer(A2/12,A3,A4,A5) que devuelve 60
Cuando el valor futuro es 0:
=NPer(A2/12,A3,A4) que devuelve -9,578.
7.4. FUNCIÓN VA (valor actual)
Permite obtener el valor actual de una inversión. El valor actual es el valor que tiene
la suma de una serie de pagos que se efectuarán en el futuro. Por ejemplo, cuando
se pide dinero prestado, la cantidad del préstamo es el valor actual para el
prestamista.
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 89 de 174
Sintaxis
=Va(Tasa,NPer,Pago,Vf,Tipo)
Ejemplo
Supongamos que desee comprar una póliza de seguros que pague $ 500 al final de
cada mes durante los próximos 20 años. El costo de la anualidad es $60,000 y el
dinero pagado devengará un interés del 8%. Para determinar si la compra de la
póliza es una buena inversión, use la función VA para calcular que el valor actual de
la anualidad
La fórmula es:
Va(0.08/12, 12*20, 500, , 0) lo que devuelve $ -59,777.15
El resultado es negativo, ya que muestra el dinero que pagaría (flujo de caja
negativo). El valor actual de la anualidad ($59,777.15) es menor que lo que pagaría
($ 60,000) y, por tanto, determina que no sería una buena inversión
7.5. FUNCIÓN VF (valor futuro)
Devuelve el valor futuro de una inversión, basándose en pagos periódicos
constantes y a una tasa de interés constante.
Sintaxis
=Vf(Tasa,NPer,Pago,Va,Tipo)
Ejemplo
Dado el siguiente segmento de hoja:
A B
1 Datos Descripción
2 6% Tasa de interés anual
3 10 Número de pagos
4 -200 Importe del pago
5 -500 Valor actual
6 1 El pago vence al principio del período
La fórmula =VF(A2/12;A3;A4;A5;A6) devuelve 2581.40 como valor futuro de la
inversión.
7.6. FUNCIÓN TIR (tasa interna de retorno)
Devuelve la tasa interna de retorno de los flujos de caja representados por los
números del argumento Valores.
Sintaxis
=Tir(Rango_de_Valores,Guess)
donde
Rango_de_Valores es el rango que contiene los valores para los cuales se desea
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 90 de 174
calcular la tasa interna de retorno.
Guess es un número que el usuario estima que se aproximará al resultado del Tir.
Los flujos de caja deben ocurrir en intervalos regulares meses o años. La tasa
interna de retorno equivale a la tasa de interés producida por un proyecto de
inversión con pagos (valores negativos) e ingresos (valores positivos) que ocurren
en períodos regulares.
Ejemplo
Dado el siguiente segmento de hoja, obtenga
A B
1 Datos Descripción
2 -70000 Costo inicial de un negocio
3 12000 Ingresos netos del primer año
4 15000 Ingresos netos del segundo año
5 18000 Ingresos netos del tercer año
6 21000 Ingresos netos del cuarto año
7 26000 Ingresos netos del quinto año
La tasa interna de retorno de la inversión después de 4 años:
=Tir(A2:A6) que devuelve -2%
La tasa interna de retorno después de 5 años:
=Tir(A2:A7) que devuelve 9%
Para calcular la tasa interna de retorno después de dos años; es decir, cuando el
rango es A2:A4, se deberá incluir el segundo argumento, como se indica en la
siguiente fórmula:
=Tir(A2:A4,2%) que devuelve -44%
7.7. EJERCICIO
Abra el archivo Aplicaciones Financieras.xls
Restaure todos los elementos que faltan en la hoja. Luego calcule lo que se pide en
la primera hoja. Cuando lo haya resuelto compare con la solución que se da en la
última hoja.
8. FUNCIONES ESPECIALES
A continuación desarrollaremos algunas funciones especiales que en ciertas situaciones
tienen una gran importancia y utilidad.
8.1. FUNCIÓN CONTAR.SI
Permite contar el número de registros o ítems de Rango_Datos que cumplen con la
condición planteada en Criterio.
Sintaxis
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 91 de 174
=Contar.Si(Rango_Datos,Criterio)
donde
Rango_Datos es el rango que contiene a los datos a ser contados
Criterio es una expresión con la cual se compara cada uno de los elementos que
conforman Rango_Datos.
Ejemplo
Abra el archivo Especiales 1.xls
Obtenga los dos cuadros de totales por Cliente y por Cajero
Solución
Antes de resolver el problema dé nombre a los rangos que va a usar, de tal forma
que facilite su trabajo. Usando <Insertar> - <Nombre> - <Crear> o <Definir> déle
nombre de rango a los siguientes nombres: Cajero, Monto, Tiempo_Minutos,
Tipo_Cliente, respectivamente.
Use como valor de criterio el contenido de la columna Tipo.
Primer cuadro
En la primera fila se trata de contar el número de clientes de tipo C que fueron
atendidos. Se debe contar el rango Tipo_Cliente usando el criterio que está en F6.
Para ello se debe contar condicionalmente.
En G6 digite: =Contar.Si(Tipo_Cliente,F6)
Copie esta fórmula hacia los otros tipos
Segundo Cuadro
En este caso,
En J6 digite: =Contar.Si(Cajero,I6)
Luego copie hacia los otros cajeros.
8.2. FUNCIÓN SUMAR.SI
Permite obtener la suma de todos los elementos contenidos en Rango_Suma, que
cumplen con el valor del criterio planteado en Criterio.
Sintaxis
=Sumar.Si(Rango_Datos,Criterio,Rango_Suma)
donde
Rango_Datos es el rango que contiene a los datos a ser sumados.
Criterio es una expresión con la cual se compara cada uno de los elementos que
conforman Rango_Datos.
Rango_Suma es el rango de los datos a ser sumandos siempre que cumplan el
criterio.
Ejemplo
Abra el archivo Especiales 1.xls. Obtenga los cuatro últimos cuadros referidos a
totales por Cliente y por Cajero.
Si no se usa
Rango_Suma,
debe estar
contenido en
Rango_Datos
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 92 de 174
Solución
Usando las mismas definiciones de rango usados en la función anterior,
resolveremos el problema para estos cuadros.
Cuadro 3:
En este caso Rango_Datos es Tipo_Cliente. El criterio está en F14. El Rango_Suma
es Monto.
En G14 digite: =Sumar.Si(Tipo_Cliente,F14,Monto)
Copiar hacia las otras filas
Cuadro 4:
En lugar de Tipo_Cliente use el rango Cajero. La fórmula en J14 es:
=Sumar.Si(Cajero,I14,Monto)
Luego copie hacia las otras filas
En el caso de los dos últimos cuadros se debe usar el nombre de rango
Tiempo_Minutos y repetir el mismo procedimiento anterior.
8.3. FUNCIÓN INDICE
Esta función es particularmente interesante cuando se trata de extraer un elemento
de una lista. La lista puede ser un rango de fila o columna o una matriz formada por
un rango de varias filas y columnas. En cualquiera de los casos extrae un elemento
del rango.
Permite extraer el elemento de la lista indicado por el segundo argumento.
Sintaxis
=Indice(Rango_Datos,Num_Fila [,Num-Col])
donde
Rango_Datos es el rango de los datos desde la cual se extraerá un elemento.
Rango_Datos puede ser un vector o matriz.
Num_Fila es un número de fila que indica el número de elemento del rango a ser
extraído.
Num_Col es opcional, si se usa indica el número de columna desde donde se
extraerá el elemento.
Ejemplo 1
Dado el siguiente segmento de hoja
A B 1 Cantidad 2 Pera 30 3 Manzana 25 4 Naranja 82 5 Fresa 43 6 Plátanos 58 7 Mandarina 64
La fórmula
=Indice(A2:A7,3) permite extraer el producto Naranja
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 93 de 174
Ejemplo 2
En el siguiente segmento de hoja, al ingresar el número 1 o 2 en la celda B1, en C2
debe visualizarse Boleta o Factura, respectivamente en negrita y tamaño 16.
A B C 1 2
Boleta 3 Factura
Solución
Haga clic en C2; modifique su tamaño a 16 y haga que esté en negrita. Haga que
A2:A3 tenga por nombre Formulario.
Digite ahora:=Indice(Formulario,B1)
En C2 se visualizara la palabra Boleta o Factura, según el número (1, 2) que se
ingrese en B1.
8.4. FUNCIÓN COINCIDIR
Devuelve la posición relativa de un elemento en una matriz que coincida con un
valor especificado, en un orden especificado.
Sintaxis
=Coincidir(Valor_Buscado, Matriz_Buscada,Tipo_Coincidencia)
donde
Valor_Buscado es el valor (numérico o texto) o una celda que se busca en la matriz,
o un valor al que se hace coincidir (de allí el nombre de la función).
Matriz_Buscada es un rango de celdas contiguas que contienen posibles valores de
búsqueda.
Tipo_Coincidencia es el número -1, 0, 1 que permite que se realice un tipo
determinado de coincidencia.
Si se usa -1: La función encuentra el menor valor que es mayor o igual al valor
buscado
Si se usa 0: La función encuentra el primer valor que coincide con el valor buscado.
Si se usa 1: La función encuentra el mayor valor que es menor o igual al valor
buscado.
8.5. EJEMPLO
Abra el archivo Ferretería el Iman.xls
Ingrese el código de producto. Si desea saber los códigos de los productos, puede ir
a la otra hoja haciendo clic en el mensaje inferior.
Para este código se debe obtener la descripción del producto y su precio. La
cantidad se debe ingresar como dato. Ingrese algunos productos y calcule los
totales. Asuma un descuento de 5%, además del IGV. Haga que al ingresar la
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 94 de 174
cantidad, se debe calcular automáticamente todas las filas. Si no hay datos los
cálculos deben estar en blanco o en cero.
Solución
Dar nombre a cada columna de la hoja Catálogo: Codigo, Descripción, Stock y
Precio.
En B5 digite la siguiente fórmula:
=INDICE(Descripcion,COINCIDIR(A5,Codigo,0))
En D5 digite una cantidad deseada (supuesta)
En E5 digite la siguiente fórmula:
=INDICE(Precio,COINCIDIR(A5,Codigo,0))
En F5 digite la siguiente fórmula:
=E5*D5
Si se ingresa nuevos productos se debe copiar dichas fórmulas.
Nota:
Puesto que en el ejemplo se pide que cuando no hay productos comprados, la hoja
debe quedar vacía, recalculemos todo de nuevo.
En B5: =Si(Largo(A5)>0, =INDICE(Descripcion,COINCIDIR(A5,Codigo,0)),””)
En E5: =Si(Largo(B5)>0, =INDICE(Precio,COINCIDIR(A5,Codigo,0)),””)
En F5: =Si(E5>0,E5*D5,””)
Copiar todas las fórmulas para las filas 6 a 14.
Calcular las celdas F15, F16, F17 y F18.
8.6. EJERCICIO
Abra el archivo Especiales 2.xls
Obtenga todos los resultados que se solicita
9. FUNCIONES DE BUSQUEDA EN TABLAS
INTRODUCCION
Hay dos funciones que, por su trascendencia respecto a otras funciones, las hemos
separado. Son las funciones para realizar búsqueda en tablas: La función para
búsqueda vertical: BUSCARV(...) y la función para búsqueda horizontal: BUSCARH(...).
Muchas operaciones realizadas en el mundo de las hojas de cálculo se realizan
mediante el uso de ciertos valores definidos como constantes, y como tales, se
encuentran almacenadas en listas, arreglos o alguna forma de almacén que, para
efectos de su tratamiento en Excel, se les denominan Tablas.
Claro está que el Excel también tiene otra forma de tratamiento a una tabla. Aquí lo
usaremos bajo la perspectiva de búsqueda de un determinado dato a través de su
coincidencia con algún valor en la misma.
9.1. FUNCIÓN BUSCARV (búsqueda vertical)
Esta función permite buscar un valor específico en la columna izquierda de una
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 95 de 174
matriz y devolviendo el elemento de la misma fila de la columna, determinada por el
tercer argumento de la función.
Sintaxis
=BuscarV(Valor_Buscado,Matriz_Donde_Busca,Nro_Columna,Ordenado)
donde
Valor_Buscado es el valor que se busca en la primera columna de la matriz. Puede
ser un valor o una celda.
Matriz_Donde_Busca es la matriz de datos en cuya primera columna se realiza la
búsqueda.
Nro_Columna es el número de columna de la matriz desde donde se debe extraer el
dato que corresponde a la fila donde se produjo la coincidencia.
Ordenado es un valor lógico: Verdadero / Falso (1 / 0). Si la tabla está ordenada se
deberá especificar Verdadero ó 1; en caso contrario, se usará Falso o 0.
Ejemplo 1
Dado el siguiente segmento de hoja
A B C
1 Densidad Viscosidad Temperatura
2 0.457 3.55 500
3 0.525 3.25 400
4 0.616 2.93 300
5 0.675 2.75 250
6 0.746 2.57 200
7 0.835 2.38 150
8 0.946 2.17 100
9 1.09 1.95 50
10 1.29 1.71 0
Si D5 = 0.675
La fórmula: =BuscarV(D5,$A$2:$C$10,2,0) devuelve 2.75
La fórmula: =BuscarV(0.75,$A$2:$C$10,2,1) devuelve 2.57
9.2. FUNCIÓN BUSCARH (búsqueda horizontal)
Esta función permite buscar un valor específico en la fila superior de una matriz y
devuelve el elemento de la misma columna en una fila, determinada por el un
argumento de la función.
Sintaxis
=BuscarH(Valor_Buscado,Matriz_Donde_Busca,Nro_Fila,Ordenado)
donde
Valor_Buscado es el valor que se busca en la primera fila de la matriz. Puede ser un
valor o una celda.
Matriz_Donde_Busca es la matriz de datos en cuya primera fila se realiza la
búsqueda.
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 96 de 174
Nro_Fila es el número de fila de la matriz desde donde se debe extraer el dato que
corresponde a la columna donde se produjo la coincidencia.
Ordenado es un valor lógico: Verdadero / Falso (1 / 0). Si la tabla está ordenada se
deberá especificar Verdadero ó 1; en caso contrario, se usará Falso o 0.
Ejemplo 1
Dado el siguiente segmento de hoja
A B C D E F
1 Wiese BCRED LIMA
CITY BANK
BSD
2 A plazo fijo 12% 11% 13% 10% 12%
3 A plazo var. 8% 9% 7% 8% 8%
4 Prést. Vivienda 10% 10% 12% 15% 13%
5 Prést. Vehículo 14% 15% 16% 14% 13%
6 Prést. Viaje 18% 16% 17% 20% 15%
Cuál es el porcentaje que se paga por un préstamo para vivienda en el banco de
Crédito?. Si A20 = BCRED, la fórmula:
=BuscarH(A20,$B$1:$F$6,4,0) devuelve 0.1 ó 10%.
9.3. EJEMPLO
Abra el archivo Búsqueda.xls.
Calcule la columna Total a Pagar del tercer cuadro.
Solución
Encontrar el precio en el cuadro 1. En B14 digitar la fórmula:
=BuscarV(A14,$A$6:$B$10,2,0). Esto devuelve 36
Como la cantidad se encuentra en la segunda tabla y para el cual se ingresa por el
precio, entonces buscaremos el precio (B14) en el rango E6:I7, se debe digitar en
C14:=BuscarH(B14,$E$6:$I$7,2,0)
En ambos casos se ha hecho uso de 0 en el último argumento ya que la búsqueda
debe ser exacta.
Ahora multiplique B14 con C14 para encontrar el total a pagar. Luego copie hacia
abajo.
9.4. EJERCICIO
Abra el archivo Ejercicio de Búsqueda 2.xls
Resuelva lo que se pide en cada hoja.
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 97 de 174
10. ANEXO: FUNCIONES DE FECHA
Todas las fechas en Excel son números enteros o reales
Si el número es entero, éste se convierte en fecha solamente
Si el número es real (con decimales), éste se convierte en fecha y hora
Por el echo de de registrar las fechas y las horas como números, el Excel permite realizar
diversos tipos de operaciones aritméticas con fechas, lo que comúnmente se llama aritmética
de fechas.
Entre las principales funciones de fecha tenemos las siguientes:
=Hoy()
Devuelve la fecha del día en formato: DD/MM/YYYY
= Ahora()
Devuelve la fecha y la hora del día en formato: DD/MM/YYYY HH:MM
=Fecha(Año,Mes,Dia)
Devuelve el número que representa la fecha en código de fecha y hora.
=Dias360(Fecha_Inicial,Fecha_Final,Método)
Devuelve el número de días entre dos fechas basándose en un año de 360 días
Si Método = Falso, usa formato USA, en caso contrario, formato europeo.
=DiaSem(Número_de_Serie,Tipo)
Devuelve un número de 1 a 7 que identifica el día de la semana
Si Tipo = 1 o se omite, se considera a Domingo como día 1
Si Tipo = 2 se considera a Lunes como primer día
Si Tipo = 3 se considera a Lunes como día 0
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 98 de 174
OTRO SI DIGO
FUNCION TEXTO
Esta función, de poco uso, resulta muy importante en algunas situaciones en los que se debe
extraer una parte de un dato que no tiene formato de texto o no es texto, pero previa
conversión a texto.
=Texto(Valor, Formato)
Convierte a Valor en texto bajo un formato especificado en Formato.
Ejemplo
Ingrese en una hoja vacía las siguientes fórmulas:
En C5: =Ahora()
En C7: =Texto(B5,”DD/MM/YYYY HH:MM”)
En B9: =Izquierda(C7,Encontrar(“ “,C7)-1)
En D9: =Extrae(C7,Encontrar(“ “,C7)+1,5)
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 99 de 174
CAPITULO IV
ASISTENTE PARA SUMA CONDICIONAL. TABLAS.
ESCENEARIOS. PLANTILLAS.
OBJETIVO
El objetivo de este Capítulo es proporcionar la información necesaria para realizar sumas
condicionales, manejar tablas de una y dos entradas, administrar escenarios y utilizar las plantillas
de Microsoft Excel y crear plantillas y bases de datos mediante el asistente para plantillas.
METAS
Al completar este Capítulo el participante estará en capacidad de
- Obtener sumas o totales condicionales bastante complejos
- Utilizar tablas de datos de una o dos entradas
- Crear y Administrar Escenarios
- Crear sus propias plantillas, saber usar las plantillas de Microsoft Excel y
- Usar el Asistente para Plantillas para crear plantillas y bases de datos asociadas a ellas.
TEMAS A TRATARSE
1. ASISTENTE PARA SUMA CONDICIONAL
2. TABLAS
3. ESCENARIOS
4. PLANTILLAS
* EJEMPLOS DE APLICACIONES EN CADA TEMA
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 100 de 174
1. ASISTENTE PARA SUMA CONDICIONAL
En muchas situaciones el uso de la función Si(...) ha resuelto nuestros
problemas de cálculos condicionales. En otras situaciones, como en el
caso del cálculo del impuesto de Quinta categoría, hemos tenido que
usar funciones anidadas.
También hemos visto que la función Contar.Si(...) o Sumar.Si(...) son
grandes herramientas de cálculo condicional.
Y más aún, hemos combinado las tres funciones mencionadas para
ciertos cálculos un poco caprichosos.
Claro, cuando en algún momento los valores que condicionan el cálculo
ha estado en forma de tabla, hemos preferido usar las funciones de
Búsqueda en Tablas como BuscarV(...) o BuscarH(...); asistido claro está,
por otras como Coincidir(...), Indice(...), etc.
Veamos:
Abra el archivo Totales condicionales.xls
Cómo se puede obtener el monto total por cada cajero?
Es fácil no?
Para el cajero Potter, es suficiente usar la fórmula:
=Sumar.Si(C1:C201,”Potter”,D2 :D201) Rpta. 6218
Lo mismo se puede hacer para cada uno de los cajeros, para cada Lugar
o para cada Zona.
Y si quisiera el monto total por cajero y por Lugar?
Peor aún, si se desea el monto total por Cajero por Lugar y por Zona?
Bueno, esto ya es diferente.
Aquí es cuando debemos hacer uso de otros recursos.
El Excel dispone de la herramienta Asistente para Suma Condicional
para resolver este tipo de problemas.
EJEMPLO
Obtenga el total del Monto obtenido por el cajero Platinum, en la tienda El
Dorado, de la zona Norte.
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 101 de 174
Solución
Para usar esta herramienta haga uso de la siguiente secuencia:
<Herramientas> - <Asistente> - <Suma Condicional>
Esto nos llevará a una ventana como se muestra en la siguiente figura 1.
Después de verificar que el rango es el correcto (se debe incluir los
nombres de las columnas (nombres de campo), haga clic en <Siguiente>.
A continuación obtendrá la ventana que se muestra en la siguiente figura
Seleccione las condiciones según se indica en el siguiente cuadro. En
cada caso, después de plantear la condición, haga clic en el botón
<Agregar condición>
Columna Es Este valor
Zona = Norte
Lugar = El Dorado
Cajero = Platinum
Figura 2
Figura 1
Verifica el
rango:A1:D201
Elija la columna
y el criterio.
La columna que
deseas sumar
Agrega todas
las condiciones
necesarias
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 102 de 174
Después de agregar todas las condiciones que deben cumplirse para
obtener la suma, haga clic en el botón <Siguiente> para pasar a la
ventana que se muestra en la siguiente figura 3.
En esta ventana se tiene las siguientes opciones:
➢ Copiar sólo la fórmula en una sola celda
➢ Copiar la fórmula y los valores condicionales (criterios). Active ésta.
En la siguiente ventana ingrese la celda o haga clic en la celda donde
desea que aparezca el valor por la condición utilizada. Esto se muestra
en la siguiente figura
Luego de hacer clic en <Siguiente> obtendrá una o más ventanas según
el número de condiciones que se agregaron en la ventana de la figura 2.
En este caso digite G4 y G5 en las dos ventanas que siguen.
La última ventana le pedirá la celda donde debe depositar la suma que
cumple con todas las condiciones establecidas.
Digite la celdas G6 para obtener en ella el resultado. Luego haga clic en
el botón <Terminar>.
Haga clic en la celda G6, donde está la suma y vea la fórmula que ha
Figura 3
Figura 4
G3
Digite aquí la
celda donde
desea que
aparezca el
criterio usado
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 103 de 174
utilizado esta herramienta. La solución era usar la función Suma sobre
argumentos que contienen decisiones condicionales especiales.
EJERCICIO
Abra el archivo BDVentas.xls y obtenga la cantidad total del Stock Actual
de acuerdo al siguiente cuadro:
Zona Almacén Distrito Precio Total de Stock
N Alfa Rimac <5.0
N Pitágoras Miraflores >20 y <= 40
N Einstein La Victoria >10 y <=30
S Alfa Jesús Maria <=20
S Beta Callao >10 y <=40
S Beta Jesús María <10
2. TABLAS
Si bien el Excel es un programa muy dinámico en el sentido de que
podemos realizar diversos tipos de análisis sobre un conjunto de
resultados con sólo variar ciertas celdas vinculadas con aquellas que
muestran el resultado, también encontramos en ellas cierta dificultad.
Expliquemos esto:
Suponga que se tiene el siguiente cuadro, en el cual se dispone de la
cantidad que se debe pagar mensualmente a una tasa anual del 12% y
en 60 meses.
A B
1 Préstamo 36800
2 Tasa anual 12%
3 Años 5
4
5 Pago
mensual S/. 818.60
La respuesta es pagar S/. 818.60 al final de cada mes durante 5 años y a
una tasa del 12%.
Si ahora nos preguntamos: Cuánto habrá que pagar mensualmente si la
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 104 de 174
tasa fuera de 10%? Y si fuera de 14%? Si ahora fuera del 8%?
La respuesta a cada pregunta la encontramos muy fácilmente: Basta con
digitar 10% en B2. Y al instante tendremos la respuesta. Lo mismo
podemos hacer para responder la pregunta si la tasa es de 14% o de 8%.
Y si ahora quiero comparar el pago mensual con una tasa del 12% con la
del 10% y la del 8%?.
Tendríamos que copiar en un papel para tener a la mano los pagos en
cada caso.
Sin embargo el Excel dispone de la opción Tabla del comando Datos
para resolver esta dificultad.
2.1. Tabla de una entrada
Ejemplo
Abra el libro Tabla de Entrada.xls que se encuentra en su carpeta.
Active la hoja Tabla de una entrada y observe la información que
corresponde al Análisis de un préstamo hipotecario. Calcule en B11
el pago mensual para este préstamo. Observe que en el rango
A12:A16 se indican variables que serán sustituidas en la celda C5.
Puesto que los diferentes valores que tomará la celda Tasa de
Interés se encuentran en columna: A12:A16, la celda variable, C5,
recibirá el nombre de Celda de entrada (columna).
Si los diferentes valores que tomara estuvieran en una fila, entonces
la celda variable (tasa) recibiría el nombre de Celda de entrada
(fila).
Para utilizar esta herramienta realice el siguiente procedimiento:
Seleccione el rango A11:B16
Ir al menú Datos, seleccione Tabla, aparecerá la siguiente ventana:
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 105 de 174
Ubíquese en la casilla Celda de entrada (columna) y de clic en la
celda $C$5 tal y como se muestra:
Haga clic en <Aceptar>.
Ahora observe cómo esta herramienta ha generado los pagos que el
usuario realizará considerando los distintos tipos de interés
indicados.
2.2. Tabla de dos entadas
Active la Hoja Tabla de dos entradas. En ella observará la
información que corresponde al de un préstamo hipotecario, como
en la hoja anterior. Asimismo se muestra la información que
corresponde a la Tasa de Interés, al Plazo y al importe del
préstamo.
En la celda A11 calcule el pago para este préstamo. Observe que
en el rango A12:A16 se indican variables que serán sustituidas en
la celda C5, como ya lo vimos en el caso de Tablas de una
entrada. Recuerde que la celda C5 se denomina Celda variable
columna.
Figura 5
Figura 6
Observe la posición de los datos. Esto es fundamental
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 106 de 174
Observe ahora que en el rango B11:F11 se indican variables que
serán sustituidas en la celda C6 que corresponde a diferentes
números de períodos de pago. Siguiendo lo anterior, en este caso,
para las filas se tendrá que la celda C6 recibe el nombre de Celda
variable fila.
Para utilizar esta herramienta realice el siguiente procedimiento:
Seleccione el rango A11:F16
Ir al menú Datos, seleccione Tabla.
En la ventana que aparece indicar como Celda de entrada (fila) la
celda $C$6 y como Celda de entrada (columna) la celda $C$5.
Observe cómo aparecerán los cálculos en función de los datos
indicados.
2.3. EJERCICIO
Prepare una hoja en la cual pueda aplicar el uso de Tablas de una o
dos entradas.
3. ESCENARIOS
Introducción
Un escenario es un entorno, un espacio o área en el cual se llevan a
cabo determinadas acciones, ambiente en el cual, todos los elementos
que la conforman realizan una función o mantienen un valor.
En el caso de una hoja de cálculo, dependiendo del tipo de información
que hay en ella, podemos tener un escenario en donde una o más celdas
variables mantienen un valor o pueden cambiar, afectando a otras que
dependen directa o indirectamente de ella.
El Excel dispone de la herramienta Escenarios que permite crear y
administrar este tipo de entornos.
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 107 de 174
Al usar la secuencia:
<Herramientas> - <Escenarios>, se obtiene la ventana que se muestra
en la siguiente figura:
Como es lógico, no hay ningún escenario definido. En el recuadro
superior se visualizará una lista de escenarios ya definidos; en al cuadro
de lista <Celdas cambiantes> se deberá tener la o las celdas que
cambian en el escenario seleccionado; y el tercer recuadro puede
contener un comentario breve relacionado con el escenario.
El botón <Mostrar> permite visualizar los efectos de las celdas
cambiantes en los otros componentes de la hoja.
El botón <Cerrar> termina el uso de la herramienta escenarios.
EL botón <Agregar> permite añadir nuevos escenarios a la lista. Cada
nuevo escenario tendrá un nombre y tendrá sus propias celdas
cambiantes y los valores que ellas tengan.
El botón <Eliminar> permite eliminar el escenario seleccionado de la lista
de escenarios definidos.
El botón <Modificar> permite cambiar los componentes del escenario
seleccionado.
El botón <Combinar> permite combinar escenarios existentes en otras
hojas o en otros libros.
El botón <Resumen> permite obtener, en nuevas hojas, todas las celdas
cambiantes y aquellas relacionadas directa o indirectamente.
Al hacer clic en el botón <Agregar> obtenemos una ventana como se
muestra en la siguiente figura:
Figura 7
7
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 108 de 174
En esta ventana se debe ingresar el nombre del escenario, la o las
celdas cambiantes y el comentario (si fuera necesario). Luego de hacer
clic en <Aceptar> se pasa a la ventana que se muestra en la Figura 9.
En esta ventana se debe ingresar los nuevos valores (valores objetivos)
que deben tomar estas celdas a las cuales el Excel las llama Celdas
cambiantes.
Al hacer clic en el botón <Aceptar> volverá a la ventana inicial, desde la
que podrá realizar algunas de las acciones que allí se indican, como es el
caso de mostrar el efecto de los cambios introducidos, hacer un tipo de
resumen, modificar los cambios ingresados, añadir nuevos escenarios o
cerrar el administrador de escenarios.
EJEMPLO
Abra el archivo Ejemplo de Escenarios.xls
La información contenida en este libro permite obtener el margen de
beneficio trimestral por la venta de un determinado producto. Se puede
apreciar que el margen del trimestre T3 es mucho más bajo respecto a
los otros trimestres. Preocupado por esta situación, se decide variar la
cantidad de dinero dedicada a Publicidad.
a) Si para el trimestre T3 se aumenta la publicidad a 12,000 $, cuál será
el margen de beneficio de dicho trimestre?.
b) Si se aumenta la inversión en publicidad en los trimestres T1 y T3 a
12,000 $ y 8,000 $ reduciendo a los otros a 5,000 $ y 10,000 $ ,
respectivamente, se logrará incrementar los márgenes de beneficios
de los trimestres 1 y 3?
Figura 8
Si son varias
celdas, haga clic
en cada celda
teniendo
presionada la
tecla <CTRL>
Figura 9
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 109 de 174
c) Cuál debe ser la máxima inversión en publicidad en el primer
trimestre de tal forma que se tenga el máximo porcentaje de margen
de beneficio de dicho trimestre?
Solución
Use la secuencia <Herramientas> - <Escenarios ...>
Complete la información que se solicita según la Figura 10, digitando el
nombre del escenario: Escenario 1; Celdas cambiantes: $D$11.
Haga clic en <Aceptar>. En la siguiente ventana, Figura 11, ingrese el
nuevo valor para la celda cambiante. Digite 12000 y luego haga clic en
<Aceptar>.
De acuerdo a esto se volverá a la primera ventana, en la cual se puede
apreciar que ya se tiene definido un primer escenario, como se muestra
en la siguiente figura 12.
Haga clic en <Mostrar>. Observe que, en lugar de aumentar el margen
de beneficio, este se ha reducido a 7%.
Cree un segundo escenario cambiando el valor de la celda cambiante a
una nueva inversión de 5,000. Luego haga clic en mostrar y observe el
resultado. Podrá observar que el margen se ha incrementado.
Vamos a guardar como resumen estos escenarios.
Para ello haga clic en el botón <Resumen>.
Figura 10
Figura 11
Figura 12
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 110 de 174
A continuación pasará a la ventana que se muestra en la siguiente figura.
Como puede ver, se dispone de dos tipos de informes:
➢ Un informe resumen
➢ Un informe de tabla dinámica
Haga clic en la primera opción y seleccione las celdas D16 como celda
donde queremos el resultado. Luego haga clic en <Aceptar>.
La figura anterior, Figura 14, muestra el informe que se ha generado.
Vuelva a la hoja de trabajo (Hoja1) y use la secuencia <Herramientas> -
<Escenarios> para volver a la lista de los escenarios.
En esta ventana haga clic en <Resumen> y seleccione la opción
<Informe de tabla dinámica> y luego haga clic en <Aceptar>. En una
nueva hoja tendrá los resultados en formato de tabla dinámica.
Esto se muestra en la siguiente figura.
Respuesta a la pregunta b)
Vuelva a usar la secuencia <Herramientas> - <Escenarios>.
Agregue un nuevo escenario llamado Escenario 3. En esta vez
seleccione las cuatro celdas de Publicidad: B16, C16, D16 y E16 con los
valores 12000, 5000, 8000 y 10000.
Respuesta a la pregunta c)
Si la inversión fuera nula en Publicidad para el primer trimestre T1 el
margen de beneficio será de 11%, aunque invirtiendo hasta 7000 se
logra el mismo margen.
Figura 14
Figura 13
Figura 15
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 111 de 174
EJERCICIO
Abra el archivo Escenario02.xls
Analice las ventas de Junio y Julio e intente modificar estos valores de tal
forma que se logre aumentar la cuota de participación de las tiendas con
menor porcentaje.
4. PLANTILLAS
Una plantilla es un libro como cualquier otro, excepto que es utilizado
como modelo o guía para generar hojas de cálculo con muy poca
modificación.
Si un usuario trabaja todos los días con un nuevo libro en el cual registra
las ventas diarias, es posible que sólo tenga que modificar ciertas celdas,
aquellas donde se ingresan los datos de venta. Las fórmulas, los
formatos, los gráficos vinculados, los objetos y tablas que en ella se
encuentren, probablemente no cambien. Luego de ingresar los datos
correspondientes del día, procede a grabarlo con otro nombre. Al día
siguiente y los siguientes, hace lo mismo. Siempre parte del libro del día
anterior para modificar los datos que varían y luego lo graba con otro
nombre.
El libro que siempre está abriendo es, para él, su plantilla. Eso es una
plantilla. Y dicho libro lo está usando como plantilla.
Excel distingue entre un libro y una plantilla. La extensión de un libro es
XLS, mientras que el de una plantilla es XLT. Cada vez que se abre una
plantilla, se crea un nuevo libro con el mismo contenido y con el nombre
de la plantilla al cual se añade “1”. Al grabar, se graba como libro.
Crear una plantilla
Para crear una plantilla tome en cuenta las siguientes recomendaciones:
➢ Complete su hoja de cálculo con todo lo que en ella desea tener:
fórmulas, etiquetas, tablas, objetos, gráficos, vínculos, botones,
formatos de celda, cuadros de texto, etc. Cuando todo está listo
continúe.
➢ Grabe el libro usando <Guardar como>.
➢ Cambie el tipo de archivo seleccionando <Plantillas (*.xlt), como se
muestra en la siguiente figura.
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 112 de 174
➢ Supongamos que la plantilla debe tener por nombre Ejemplo01.
Entonces el archivo tendrá por nombre Ejemplo01.xlt
➢ Cada vez que quiera crear una plantilla, lo tendrá que hacer a partir
de un libro. Excepto cuando abre la plantilla y la guarda como
plantilla.
Dónde lo graba?
Si Ud. no cambia la carpeta donde se debe guardar el archivo, el Excel lo
grabará en la carpeta Plantillas de la carpeta Microsoft, que está en
Datos de programa, correspondiente a la carpeta del usuario, digamos
Administrador, y ésta dentro de la carpeta Documents and Settings, de la
unidad C. La siguiente es la ruta.
C:\Documents and Settings\Administrador\Datos de Programa\Microsoft\Plantillas
Siendo la plantilla de un usuario, lógicamente lo graba en la carpeta del
usuario, en este caso, el usuario es el Administrador.
Las plantillas del Excel se encuentran grabadas en la carpeta Templates,
la que se encuentra tomando en cuenta la siguiente ruta:
C:\Archivos de programa\Microsoft Office\Templates\3082
Nota:
Esto depende de la instalación y configuración en cada
equipo.
Claro que puede guardarlo en cualquier otra carpeta, puede
incluso guardarlo en disquete. Solo que en estos casos,
cuando quiera abrirlo tendrá que hacerlo eligiendo su tipo de
archivo e indicando la carpeta o unidad donde está
guardada.
Cómo usar una plantilla
Para usar una plantilla creada previamente, puede usar una de las dos
opciones siguientes:
a) Si fue grabado en la carpeta de plantillas, use la secuencia:
<Archivo> - <Nuevo>. Esto le proporcionará el Panel de Tareas, lo
que se muestra en la figura de la izquierda, Figura 16.
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 113 de 174
A continuación haga clic en <En mi PC> para pasar a la ventana que
se muestra en la figura de la derecha (Figura 17).
En ese recuadro aparecerán las plantillas que Ud. ha creado.
b) Si fue grabado en otra carpeta
Use la siguiente secuencia:
<Archivo> - <Abrir>. Indique la unidad y carpeta donde lo grabó
Abra la plantilla tomando en cuenta que debe cambiar el tipo de
archivo a Plantilla (*.xlt).
Supongamos que desea abrir la plantilla Ejemplo01.xlt.
Qué ocurre después de abrir una plantilla?
En pantalla verá un libro cuyo nombre será Ejemplo011. Como se
muestra en la siguiente figura
Lo que Excel hace es abrir un libro a partir de la plantilla indicada.
Cuando se guarde lo que está en pantalla, se guardará como libro con el
nombre que le ha puesto (Ejemplo011) o con el nombre que Ud. desee.
Nota:
Una plantilla se abre para generar un libro a partir de ella. Se modifica,
actualiza o elimina su contenido y cuando se graba, se guarda como
libro, excepto si desea modificar la plantilla misma, en cuyo caso debe
tener cuidado de grabarlo como plantilla (*.xlt).
Plantillas de Excel
El programa Excel dispone de una gran variedad de plantillas, aunque en
su computadora habrá sólo 5 de ellas. Como se ha visto en el Panel de
Tareas, se puede abrir plantillas en línea desde las páginas de Microsoft
Figura 16 Figura 17
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 114 de 174
Office o desde vuestros propios alojamientos (sus páginas Web, las
plantillas de Intranet de su empresa grabadas en el Servidor, etc.).
Cuando se instala el Office o solo el programa Excel, las plantillas del
Excel se guardan en una carpeta especial, a partir del cual serán abiertas
como libro cada vez que queramos abrirlas. Como se dijo antes la ruta
para encontrar estas plantillas, es
C:\Archivos de programa\Microsoft Office\Templates\3082
Para abrir una de ellas use <Archivo> - <Nuevo> - <En mi PC...>. Al
obtener la nueva ventana haga clic en la ficha <Soluciones hojas de
cálculo>. En seguida verá lo que se muestra en la siguiente figura.
Entre las cinco plantillas que allí se encuentran está la hoja llamada
Amortización de Préstamo
Haga clic en esta plantilla y luego en <Aceptar>
Observe que ahora el nombre de libro es Amortización de préstamo1.
Lo que ve en pantalla es un modelo de plantilla. Muchas veces debe
haber trabajado con hojas similares a esta. Observe que sólo se tiene
acceso a ciertas celdas. Las otras están protegidas. Haga clic en la celda
D11. Allí verá un comentario. Si hace clic en las celdas en las cuales está
permitido hacerlas, verá en el cuadro de nombres (lado izquierdo de la
barra de fórmulas) el nombre de dicha celda. Despliegue la lista de este
cuadro. Todo lo que allí ve son nombres de celdas o de rango de celdas.
Esto es importante, como bien sabe, por muchas razones. Es
particularmente importante por lo que en esta sesión de plantilla diremos.
Ingrese los datos que se piden y al completarlos verá en cómo todo el
cuadro se completa proporcionándole una Tabla de amortización.
Grabe este libro como Tabla de amortización. Use <Guardar como>.
Observe el nombre con el que quiere guardarlo, la ubicación y el tipo de
archivo (xls). Es decir ya no es una plantilla. Grábelo con el nombre
indicado y en la carpeta que Ud. desee.
Figura 18
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 115 de 174
Nota 1:
Si desea guardarlo en su carpeta personal, como plantilla, borre todos
los datos que ha ingresado y grábelo como plantilla y con el nombre que
desee. A partir de ello podrá usar esta plantilla y no la de Excel.
Nota 2:
Si desea poner su propio diseño en esta plantilla, siga el siguiente
procedimiento:
<Herramientas> - <Macro> - <Editor de Visual Basic>. Obtendrá la
siguiente ventana:
Ahora vuelva a su hoja de cálculo y haga clic en cualquier celda.
Ahora puede ponerle cabecera y pie de página, puede insertarle su
propio logotipo, etc.
Y por su puesto lo grabará como plantilla en la carpeta de plantillas o en
su carpeta personal.
Nota 3:
Las plantillas pueden servirnos mucho más de lo que hasta aquí hemos
dicho. Esto se puede hacer con la versiones 2002 ó 2003 del Excel que
no tienen el Asistente de Plantillas en el comando <Datos>.
La otra gran ventaja que tiene la creación y manejo de plantillas es el
Haga clic en estos
dos lugares
Seleccione 0 -
xlNorestrictions
Figura 19
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 116 de 174
manejo de una base de datos vinculada a la plantilla y al libro que se
genera al abrir la plantilla.
Veamos la siguiente situación:
Durante el día se atendieron a los clientes entregándoles una factura
impresa proveniente de una hoja de cálculo, el cual fue generado a partir
de una plantilla. Para cada nuevo cliente se ha generado dos
documentos impresos: para el cliente y para Usted que de inmediato o al
día siguiente, se encarga de pasar ciertos datos hacia una hoja de
cálculo en el cual registra la información de facturación de todos los
clientes durante un mes. Dependiendo del volumen de ventas al final
estará manejando una gran base de datos de miles de registros. Pero su
trabajo es tedioso, aburrido y totalmente mecánico y repetitivo.
Así como se generó una plantilla, gracias a la cual ya no se necesita dar
ningún formato ni hacer cálculo, ¿se puede automatizar la copia de
ciertos datos y añadirlos en una nueva fila de la base de datos de
facturación? Claro... eso lo hace una macro. Usted puede crear una
macro para que copie de ciertas celdas de una hoja y pegue en otra. Al
final, lo único que Usted haría será presionar dos teclas o hacer clic en
un botón y resuelto el problema. Y si no sabe macros?
Por otro lado la macro no le va a permitir crear la base de datos. Salvo
que tenga dos macros: una para creación de la base de datos y otra para
actualizarla.
El Excel 2000 nos proporciona un asistente para crear plantillas y bases
de datos asociados a dichas plantillas y a los libros que se generan al
abrir las plantillas. A continuación vamos a usar el Asistente para
Plantillas.
Asistente para Plantilla
Nota:
El Excel 2000 tiene, en el comando Datos, una opción para usar el
Asistente para plantillas. Las versiones 2002 y el 2003 no disponen de
este asistente. No sabemos lo que ocurrirá después.
Cuando alguna función u opción no está activada lo que hacemos es
habilitar el complemento mediante la secuencia <Herramientas> -
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 117 de 174
<Complementos>. Se activa las que no lo están y después de <Aceptar>
ya se podrá usar. Y si dicho complemento no está, aún le queda instalar
algún otro componente que pudiera estar en la carpeta <AddIns> o
finalmente, se puede modificar la instalación para añadir todos los
complementos que fueran necesarios.
Sin embargo, el Asistente para Plantillas, que en el caso de las versiones
mencionadas no se encuentran. No es que pueda haberse instalado
incompleto, simplemente no se dispone del asistente.
Para resolver este inconveniente estas versiones disponen de una gran
cantidad de plantillas en sus páginas, desde las cuales podemos
descargarlas, pero no está entre ellas el complemento que nos permita
usar el Asistente.
Puesto que al consultar con la ayuda, la misma Microsoft nos autoriza a
descargarlo de alguna de sus páginas, entonces nosotros podemos
instalar dicho complemento y usarlo después.
Nosotros le entregaremos conjuntamente con los archivos de trabajo, un
archivo que contiene el complemento mencionado.
Para ello haremos uso del siguiente procedimiento.
Procedimiento
Paso 1: <Herramientas> - <Complementos> - <Examinar>. Ubique la
carpeta donde se encuentra los archivos de trabajo.
Seleccione el archivo AsisPlan.xla y luego haga clic en
<Aceptar>.
Paso 2: Responda a la ventana de diálogo en donde se le pide si
desea que lo agregue a la carpeta AddIns o desea instalarlo en
otra carpeta.
Paso 3. Al final Ud. tendrá, en la lista de complementos, una nueva
casilla indicando que ya está disponible el Asistente para
Plantillas. Haga clic en la casilla y luego <Acepte>.
USO DEL ASISTENTE PARA PLANTILLAS
Vamos a crear una plantilla y una base de datos usando el asistente.
Para ello vamos a elegir la plantilla del Excel llamada Factura.
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 118 de 174
Previo:
Supondremos que su carpeta de trabajo es Libros y que está en el primer
nivel de la unidad C; es decir la ruta de acceso es C:\Libros.
Procedimiento:
Abra la plantilla Factura usando <Archivo> - <Nuevo> - <Soluciones hoja
de cálculo> - <Factura> - <Aceptar>.
Observe la barra de título. El nombre del libro activo es Factura1.
Grábelo como libro, con el nombre Facturación en su carpeta de trabajo.
Modifique el diseño de este libro. Que el nombre de la empresa sea
SAMERSA BROTHERS S.A. Inserte la imagen El Pintor.jpg. Si no lo
permite, desproteja la hoja usando: <Herramientas> - <Proteger> -
<Desproteger hoja>. Al final de la hoja escriba. “Recuerde que se está
aplicando el 0.10% de impuesto ITF”. Y como mensaje de despedida:
“Muchas gracias y... Vuelva pronto !!!”.
Vuelva a grabar el libro con esta modificación y con el mismo nombre.
Con este libro vamos a crear una base de datos llamada Lista de
Facturación.xls. Esta lista contendrá los siguientes nombres de campos
(celdas de la hoja):
NroFactura/Cliente/Dirección/Ciudad/Teléfono/FechaPedido/
NroPedido/Represent/SubTotal/Flete/Impuestos/Total/ModoPago
Use la siguiente secuencia:
<Datos> - <Asistente para Plantillas... >. A continuación tendrá la
siguiente ventana
En el primer cuadro de texto se ingresa el nombre de la hoja a partir de
donde se está creando la plantilla. Deje con el nombre que aparece:
Figura 20
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 119 de 174
Facturación.xls
En el siguiente cuadro de texto ingrese la ruta y nombre para la plantilla.
Digite: C:\Libros\Plantilla para Facturación. (El nombre de la plantilla
será: Plantilla para Facturación.xlt)
Haga clic en <Siguiente>
Obtendrá la siguiente ventana:
En el primer cuadro de texto se debe seleccionar el tipo de base de datos
que se desea crear. Puede elegir Base de datos de Access o de la línea
del dbase. Nosotros elegiremos <Libro de Microsoft Excel> ya que la
plantilla es un archivo de Excel y genera un libro. Si se elige <Base de
datos de Access> se podrá usar totalmente en Access. Para ser usado
en Excel deberá realizarse una conversión previa y asistida.
En el siguiente cuadro de texto se debe ingresar la ruta y nombre de la
base de datos que se está creando. Como se dijo antes, el nombre será
Lista de Facturación. Su ruta será C:\Libros\Lista de Facturación.xls
(Observe que éste va a ser un libro como cualquier otro).
Nota:
Puede ser que no necesite crear una nueva base de datos, sino usar una
ya creada; en cuyo caso sólo necesita vincularla. En este caso esto es lo
que se estaría haciendo si se hace clic en el botón <Examinar> y se
ubica al archivo correspondiente.
Haga clic en <Siguiente>.
Si está creando una nueva base de datos, obtendrá la ventana mostrada
en la figura 22; en caso contrario, se visualizará la misma ventana pero
con la lista de los campos ya definidos.
Figura 21
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 120 de 174
Haga clic en el cuadro de texto 1: Primer nombre de campo
Haga clic en la celda M3 de la hoja. Vea cómo se inserta $M$3 en el
cuadro de texto 1.
Ahora haga clic en el cuadro de texto <Campo>. Digite allí, “NroFactura”
(encima del nombre que salga)
Haga clic en el cuadro de texto 2. Haga clic en D13 de la hoja.
Haga clic en el siguiente cuadro de texto (campo). Digite allí “Cliente”.
Continúe con cada uno de los campos arriba indicados hasta terminar
con ModoPago que corresponde a la
celda D38.
Haga clic en <Siguiente>. A
continuación obtendrá la siguiente
ventana.
Si en esta ventana hace clic en <Agregar> lo que le estará indicando al
Excel es que abra otros libros desde la cual se añadirán datos a esta
base de datos. En ella Usted dispondrá de medios para indicarle el lugar
y nombre de los archivos. Si no hay otro libro, se elegirá <Omitir>. En el
caso presente, haga clic en el botón <Omitir>.
Luego haga clic en <Siguiente>. Ahora se obtendrá una ventana
resumen donde se indica el nombre y la ubicación de la plantilla y la base
de datos que se está creando. Luego haga clic en <Terminar>.
Al hacer clic en <Terminar> y después de breves segundos de
Nombre de la hoja
Nombre del campo
Nombre de la hoja
Figura 22
Figura 23
Figura 24
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 121 de 174
procesamiento, volveremos al libro. Recuerde que partimos de este libro.
La plantilla y la base de datos ya se han creado.
Cómo añadir registros a la base de datos creada
Cierre el libro Facturación.xls que está abierto. Guarde los cambios.
Abra la plantilla Plantilla para Facturación.xlt desde donde lo guardó o
haciendo uso de <Archivo> - <Nuevo> - Seleccionar y <Aceptar>.
Complete una factura. Complete todos los datos. Por ejemplo:
Nro. De Factura: 1
Nombre: Ferreyros y Compañía
Dirección: Av. Los Carrizales 120
Ciudad: Trujillo
Teléfono: 075 – 276253
Fecha: 05/06/2004
Nro. de pedido: 129
Representante: Drago Runcimann
Cantidad: 12. En la siguiente fila: 5
Descripción: Bolsas de harina de trigo / Láminas de acero corrugado
Precio unitario: 125 / 360 (en soles)
Envío: 120
Impuestos: 19.01%
Medio de pago: Efectivo
Ahora use <CTRL> - <G> o <Archivo> - <Guardar> o haga clic en el
disquete.
Obtendrá la siguiente ventana.
Como estamos creando un nuevo registro (y los campos del registro se
encuentran en la hoja) haga clic en <Aceptar>.
Nota:
En cada nueva factura debemos usar la secuencia arriba indicada para
crear nuevo registro. Si usa la opción sin actualizar no se grabará nuevo
registro para esta factura.
Recuerde
abrir Plantilla
y no Libro.
Figura 25
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 122 de 174
Nota:
Después de aceptar se le pedirá que grabe el libro en uso. Si desea,
grabe el libro con el nombre que tenga o con otro, como cuando lo hacía
antes, cada vez que generaba nueva factura.
Vuelva a modificar los datos en la idea de tener otra factura para otro
cliente y cuyos datos deben ser guardados en la base de datos.
Nro. De Factura: 2
Nombre: Luis Martínez Salazar
Dirección: Av. Javier Prado. Estadio de la U
Ciudad: La Molina
Teléfono: 437 6753
Fecha: 15/08/2004
Nro. de pedido: 10
Representante: Luis Cobarrubias
Cantidad: 120. En la siguiente fila: 40
Descripción: Bolsas de cemento Andino / Camiones de arena gruesa
Precio unitario: 25 / 360 (en soles)
Envío: 50
Impuestos: 19.01%
Medio de pago: Efectivo
Use <CTRL> - <G>.
Haga clic en <Crear registro nuevo> - <Aceptar>
Ahora cierre el libro.
Abra la base de datos llamada Lista de Facturación.xls
Hemos creado dos registros en esta base de datos.
EJERCICIO
Abra la plantilla de Excel llamada Informe de gastos y genere, a partir
de ella, un nuevo libro con formato propio llamado Gastos, una plantilla
llamada Plantilla de Gastos y una base de datos llamada Datos de
Gastos. Ingrese 5 registros a la base de datos creada.
EJERCICIO
Escoja una de sus hojas con las cuales trabaja frecuentemente y que
tenga la característica de ser plantilla, arregle y dé un formato adecuado
y una buena presentación a esta hoja. Grabe como libro con el nombre
que Usted desea y a partir de ella, genere una plantilla y una base de
datos que tendrán por nombre los que Usted desee. Ingrese 5 registros a
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 123 de 174
la base de datos creada.
Esto es todo amigo
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 124 de 174
CAPITULO V
BUSCAR OBJETIVO.
SOLVER. APLICACIONES
OBJETIVO
El objetivo de este Capítulo es proporcionar la información necesaria para realizar Análisis de
Sensibilidad sobre una hoja de cálculo utilizando dos herramientas de análisis: Buscar Objetivo y
Solver.
METAS
Al completar este Capítulo el participante estará en capacidad de
➢ Realizar análisis de sensibilidad sobre diferentes situaciones usando la herramienta Buscar
Objetivo.
➢ Saber utilizar la herramienta Solver para resolver problemas de optimización en diferentes
aplicaciones como en el campo de la economía, en la administración; en general, en todo
problema susceptible de ser optimizado, sea maximizando o minimizando.
➢ Resolver problemas de la investigación de operaciones debidamente formulado
TEMAS A TRATARSE
1. INTRODUCCION
2. HERRAMIENTA BUSCAR OBJETIVO
3. EJEMPLOS Y APLICACIONES
4. HERRAMIENTA SOLVER
5. EJEMPLOS Y APLICACIONES
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 125 de 174
1. INTRODUCCION
Después de realizado un determinado cálculo y luego de observar el
resultado, nos preguntamos: Qué ocurre con el resultado si en lugar de
dar un cierto valor a una celda, se ingresa otro valor?. Mas aún, si al
observar los resultados de un determinado cálculo, quisiéramos que
fuera otro, cuál debería ser el valor que tomara algunos de los
componentes de dicho cálculo?
Concretemos:
Tomemos el siguiente segmento de hoja:
Si se hace un préstamo de S/. 30,000 a una tasa anual de 8% para
pagarlos mensualmente en 5 años, entonces se debe pagar en cada
mes la cantidad de S/. 608.29.
¿Cuánto se debe pagar mensualmente si aumentamos el período de
pago a seis años? Es decir, que ocurre si B3 se cambia a 6?
Respuesta:
Aquí es suficiente cambiar la celda B3 a 6. El pago a efectuarse en
cada período será de S/. 526.00
Pero, a la pregunta:
Si mi capacidad de pago fuera de S/. 650.0 mensualmente, ¿en
cuantos períodos lo pagaría?
Otra:
Planteado desde la perspectiva del prestamista: Si el cliente puede
=Pago(B2/12,B3*B4,B1)
Figura 1
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 126 de 174
pagar S/. 620 durante 60 meses, ¿Cuál debe ser la tasa anual que se
le debe cobrar? Es decir, Qué ocurre con la celda B2 si la celda B5 se
cambia por S/. 620.0?
Las dos últimas preguntas no pueden ser resueltas sin tener que usar
otra fórmula. A parte que habría que destruir la fórmula en B5. En
términos financieros, la segunda pregunta será resuelta usando la
función NPER(...) y la tercera pregunta será resuelta usando la función
TASA(...).
Las dos preguntas constituyen preguntas clásicas que son conocidas
como el “QUE OCURRE SI”, lo que en las antiguas hojas de cálculo
como el Lotus 123, el Multiplan o SuperCalc se conocían como las
herramientas WHAT IF y GOAL SEEKING.
El Excel permite realizar este tipo de análisis usando dos herramientas:
➢ Buscar Objetivo
➢ Solver
Observación Importante
Del segmento de hoja arriba mencionado, Figura 1,
podemos decir que la celda a la cual se le asigna el
nuevo valor, constituye el objetivo que queremos
alcanzar. Pero esto ocurre para algún valor de la
celda que queremos cambie. La primera es la celda
objetivo y la segunda constituye la celda cambiante,
la que alcanzará un valor a través de un número
determinado de iteraciones.
2. HERRAMIENTA BUSCAR OBJETIVO
Para usar esta herramienta haga uso de la siguiente secuencia:
<Herramientas> - <Buscar Objetivo>
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 127 de 174
A continuación obtendrá la siguiente ventana
En <Definir la celda> debe ingresar la celda que debe recibir un valor,
el cual queremos obtener como resultado final, como objetivo. Esta
celda recibe el nombre de Celda Objetivo.
En <con el valor> se debe ingresar el valor que queremos alcance
como objetivo la llamada Celda Objetivo
En <para cambiar la celda> se debe ingresar la celda que debe
alcanzar un valor para que la celda objetivo llegue a tener el valor
ingresado; a esta celda se le denomina Celda Cambiante.
El Excel, mediante un conjunto de iteraciones (que puede ser una
decena de miles) deberá encontrar un valor tal que, al recalcular la
fórmula contenida en la celda cambiante, se logrará el valor ingresado.
Por qué iteraciones?
Suponga que X = 5; Y = 2; Z = 2*X + 3*Y
En primer lugar, con los valores dados, Z = 16.
Si queremos (objetivo) que Z sea 21 (le estamos asignando 21 a Z),
cuál debe ser el valor de X?
Empecemos asignando a X = 5.
Si X = 5 entonces Z = 16
Si X = 4 entonces Z1 = 14
Como Z1 es menor que el valor anterior Z, y esto ocurre cuando X
disminuye en 1, entonces asignemos a X valores mayores que su valor
original.
Si X = 6 entonces Z1 = 18. Comparemos con el objetivo que es Z = 21
Figura 2
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 128 de 174
Como Z1 < Z seguimos iterando
Si X = 7 entonces Z1 = 20. Como Z1 < Z , seguimos iterando
Si X = 8 entonces Z1 = 22. Como Z1 > Z, asignamos un valor intermedio
entre el X anterior y el último; es decir X = 7.5.
Ahora bien, si X = 7.5 entonces Z1 = 21. Como Z1 = Z hemos logrado
el objetivo. Esto se ha logrado con X = 7.5 que constituye una solución
óptima cuyo valor objetivo es Z = 21. Y esto se ha logrado con 6
iteraciones.
Esto es exactamente lo que hace esta herramienta.
Por ello debemos remarcar los nombres de las celdas implicadas en
esta herramienta. Esto lo hacemos observando la siguiente figura.
3. EJEMPLOS Y APLICACIONES
EJEMPLO 1
Volvamos al ejemplo de la Figura 1. Aquí la volvemos a mostrar como
Figura 4.
Responderemos a la primera pregunta: Si B5 = S/. 650.00, ¿cuál debe
ser el valor de B3?
En este caso la celda objetivo será B5. La celda cambiante será B3.
Celda cambiante:
Esta celda deberá recibir un valor
como respuesta
del uso de Buscar Objetivo
Celda Objetivo: En esta celda se debe ingresar el
valor objetivo
Figura 3
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 129 de 174
Use la secuencia: <Herramientas> - <Buscar Objetivo>.
En la ventana que salga:
En <Definir la celda> ingrese B5
En <Con el valor> ingrese 650
En <Para cambiar la celda> ingrese B3
Haga clic en <Aceptar>
El resultado será 4.61186464. Que al multiplicar por el número de
períodos (12) se obtiene 55.3424 períodos; es decir, se logrará
amortizar el préstamo en 56 cuotas (naturalmente la última cuota
completa el saldo).
Respondamos a la segunda pregunta. Si el objetivo es pagar S/. 620
mensualmente, ¿cuál debe ser la tasa a la cual se debe realizar el
préstamo?.
Vuelva a usar Buscar Objetivo (haga que B3 sea 5)
Celda objetivo: 620
Celda cambiante: B2
Resultado: 8.81% anualmente.
EJEMPO 2
Abra el archivo Ejemplo de Escenarios.xls
Si el objetivo es alcanzar un margen de beneficio de 10% en el
trimestre 3, ¿cuánto debe ser lo que se invierta en publicidad?
Solución
<Herramientas> - <Buscar Objetivo>
Figura 4
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 130 de 174
En <Definir la celda> haga clic en D16.
En <Con el valor> ingrese 10%
En <Para cambiar la celda> ingrese D11
El resultado es: Ufff !!!! No hay solución. Sabe cuántas iteraciones ha
realizado el Excel?
Vuelva a usar la herramienta, pero ahora use 9% como objetivo.
El resultado: Dice que debe invertirse S/. 2900, lo que se logra en 90
iteraciones.
EJEMPO 3
Abra el archivo Buscar Objetivo Ejemplo 3.xls
En la primera hoja tenemos la venta de tres tipos de Cemento. En la
celda B19 se tiene el ingreso total por la venta de una cantidad de
bolsas de cemento de cada tipo. Es la suma de B7, B12 y B17. A su
vez estas dependen de la cantidad de bolsas de cemento vendido.
Si se desea tener un ingreso total de 5300, cuántas bolsas de cada tipo
se debe vender?
Solución
Aquí encontramos una limitación de esta herramienta, ya que sólo se
puede optimizar individualmente por cada tipo de cemento. Si bien
podemos ingresar las tres celdas cambiantes: B6, B11 y B16, sin
embargo Buscar Objetivo logra satisfacer el objetivo con sólo una celda
cambiante, las otras permanecen en 0.
Veamos: Active la ventana de Buscar Objetivo
En <Definir la celda> ingrese B19
En <Con el valor> ingrese 5300
En <Para cambiar la celda> Teniendo presionada la tecla <CTRL>
haga clic sucesivamente en las celdas: B6, B11 y B16. Luego haga clic
en <Aceptar>
La solución se logra vendiendo 286.49 bolsas de Cementos Lima, y 0
de las otras.
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 131 de 174
EJERCICIO 1
Abra el archivo Ejemplo de Escenarios.xls
Encuentre los márgenes óptimos en cada uno de los trimestres y luego
diga cuánto debe invertirse en publicidad en cada trimestre.
EJERCICIO 2
Abra el archivo Análisis de Venta.xls
Realice todos los cálculos en esta hoja. Al lado derecho de la hoja hay
datos que deben ser usados para todos los cálculos.
Use la herramienta Buscar Objetivo para determinar la cantidad óptima
de productos que se debe vender a fin de obtener $ 3,500 de Utilidad
después de impuestos.
4. HERRAMIENTA SOLVER
INTRODUCCION
Esta es una herramienta del Excel que permite resolver una gran variedad de problemas de
optimización.
Antes de ver el uso del Solver hagamos una breve introducción a los conceptos de
optimización sea en términos de maximización o de minimización.
Un problema de optimización consiste en encontrar aquellos valores de ciertas variables que
permiten optimizar a una función llamada Función Objetivo Z = f (x1, x2,..., xn). El conjunto de
valores de x1, x2,..., xn, reciben el nombre de variables de decisión y deben tomar valores tal
que hagan que Z alcance un valor máximo (o mínimo), lo que será llamado valor óptimo.
El método más conocido para encontrar el óptimo de una función es a través del análisis de
sus derivadas. Este método tiene dos limitaciones: no siempre la función es derivable y,
además, no siempre el óptimo nos da una solución que tenga sentido en la práctica.
Debido a la primera limitación, surgieron los métodos numéricos, que parten de una solución
inicial, y mediante algún algoritmo iterativo, mejoran sucesivamente la solución.
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 132 de 174
Debido a la segunda limitación, surgieron los métodos de optimización restringida. El nombre
se debe a que podemos ponerle restricciones a las variables y a la disponibilidad de los
recursos, de modo que cumplan una o más condiciones.
La restricción más común que se da en la práctica es que las variables deben ser no
negativas. Por ejemplo, no tiene sentido una "solución" que implique producir cantidades
negativas, o sembrar un número negativo de hectáreas, o llevar un número negativo de
paquetes.
Pero, en el mundo real, surgen naturalmente otras restricciones, debido a limitaciones de
horas de trabajo, capital, tiempo, insumos, o quizás deseamos imponer ciertos mínimos o
máximos de calidad, riesgo, etc.. Estas restricciones pueden y deben ser funciones de las
variables controlables o capaces de ser controladas.
Los modelos más sencillos de optimización restringida corresponden a modelos de
Programación Lineal, donde tanto la función objetivo como las restricciones son funciones
lineales, las variables deben ser no negativas, y pueden tomar cualquier valor real, no
necesariamente entero (si así fuera estamos en los modelos de Programación Entera).
LA HERRAMIENTA SOLVER
Solver es una herramienta para resolver y optimizar soluciones mediante el uso de métodos
numéricos.
Con Solver, se puede buscar el valor óptimo para una celda, llamada celda objetivo, en
donde se escribe la fórmula de la función objetivo f (x1, x2, ..., xn).
Solver cambia los valores de un grupo de celdas, denominadas celdas cambiantes, y que
estén relacionadas, directa o indirectamente, con la fórmula de la celda objetivo.
En estas celdas se encuentran los valores de las variables controlables x1, x2, ..., xn.
Se puede añadir restricciones a Solver, escribiendo una fórmula g(x1, x2, ..., xn ) en una celda,
y especificando que la celda deberá ser mayor o igual, igual, o menor o igual que otra celda
que contiene la constante cj. También puede especificar que los valores sean enteros, para
evitar dar resultados absurdos de algunos problemas, tales como que se necesitan 3,5
empleados. Solver2 ajustará los valores de las celdas cambiantes, para generar el resultado
especificado en la fórmula de la celda objetivo.
2 Algoritmos y Métodos Utilizados por Solver
Microsoft Excel Solver utiliza diversos métodos de solución, dependiendo de las opciones que seleccione.
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 133 de 174
EJEMPLO 1: Solución al problema planteado con Buscar Objetivo
Abra el archivo Buscar Objetivo Ejemplo 3.xls
Haga clic en la hoja Por Solver es la misma que la primera.
En esta hoja la celda Objetivo es B19. Las celdas cambiantes son B6, B11 y B16. Se desea
alcanzar un ingreso total de 5300.
Use la siguiente secuencia: <Herramientas> - <Solver>. Obtendrá la ventana anterior.
Complete los datos como se indican. Luego haga clic en <Resolver>
Luego de muy breves segundos Solver habrá encontrado una solución.
Para los problemas de Programación Lineal utiliza el método Simplex. Para problemas lineales enteros utiliza el método de ramificación y límite, implantado por John Watson y Dan Fylstra de Frontline Systems, Inc. Para problemas no lineales utiliza el código de optimización no lineal (GRG2) desarrollado por la Universidad Leon Lasdon de Austin (Texas) y la Universidad Allan Waren (Cleveland). Para obtener más información acerca del proceso de solución interno que utiliza Solver, póngase en contacto con: Frontline Systems, Inc. Páginas Web: http://www.frontline.com o más concretamente: http://frontline.us.merial.com/
Figura 5
Figura 6
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 134 de 174
Si se desea obtener un ingreso total de 5300 se debe vender 116 bolsas de Cementos Lima,
141 bolsas de Cemento Cañete y alrededor de 6 bolsas de Cementos Pacasmayo.
EJEMPLO 2: Solución buscando que maximizar los ingresos
Resuelva el mismo problema, pero en lugar de ingresar la cantidad a ser alcanzada, haga clic
en Maximizar. Luego haga clic en <Resolver>.
Cuál es la solución que Solver devuelve?. Le parece lógico este resultado?
Claro que sí. Puesto que el objetivo es maximizar los ingresos, éste será óptimo cuando se
venda infinitas bolsas de los tres tipos de cemento.
EJEMPLO 3: OPTIMIZACIÓN RESTRINGIDA POR SOLVER
Active la hoja “Por Solver con limitaciones”.
Ahora la cantidad de bolsas de cada tipo de cemento está limitada. Esto se observa en el lado
derecho de la hoja. La forma de ingresarlo en el Solver será como se aprecia en el cuadro de
texto que aparece en la parte inferior.
En F11 ingrese la fórmula: =B6+B11+B16
Abra la ventana del Solver (<Herramientas> - <Solver>)
Complete según como se indica:
Celda objetivo: Clic en B19
Clic en el botón de Maximizar
Cambiando las celdas: Usando <CTRL> haga clic en B6, B11 y B16
Haga clic en el botón <Agregar... > para ingresar las restricciones. Con ello pasará a la
siguiente ventana.
Haga clic en <Referencia de la celda> Haga clic en la celda B6. Seleccione “>=” y digite 0 en
<Restricción>, como se muestra en la siguiente figura.
Haga clic en <Agregar>
Proceda a ingresar todas las restricciones indicadas en el cuadro de texto
Figura 7
Figura 8
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 135 de 174
Ojo:
El total de bolsas: B6+B11+B16 es una fórmula que se ha ingresado en F11. Use esta celda
para plantear el último par de restricciones.
Cuando ingrese la última restricción, haga clic en <Aceptar> para volver a la ventana anterior.
Al final debe tener una ventana como se muestra en la siguiente figura.
Note que B6 >= 0 es la única que esta restringida con un valor (cero) todas las otras tienen
como celdas los límites que se indican. Vamos a cambiar la restricción B6>=0. Haga clic en
ella, luego haga clic en el botón <Cambiar>. Haciendo clic en el cero, haga clic en la celda G8.
Luego haga clic en <Aceptar>.
Finalmente haga clic en <Resolver>
Al cabo de muy breves segundos Solver emite la siguiente ventana donde ha encontrado una
solución y ha puesto los valores en las celdas variables (celdas cambiantes) y en B19 ha
encontrado un valor óptimo, que es el máximo ingreso que se puede lograr. Esto se muestra
en la siguiente ventana.
Según esto se debe vender
Cementos Lima : 320 bolsas
Cementos Cañete : 120 bolsas
Cemento Pacasmayo : 560 bolsas
Con lo cual se logrará maximizar el ingreso total igual a 9168.
Ahora, si desea que Solver mantenga la solución, haga clic en <Aceptar>.
Figura 9
Figura 10
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 136 de 174
Si desea guardar el escenario haga clic en <Guardar escenario>
Si desea mantener los valores originales sin ningún cambio, haga clic en <Restaurar valores
originales>.
Si desea obtener alguno o todos los tipos de informes, seleccione lo adecuado y luego haga
clic en <Aceptar>.
Volveremos después a explicar algo más respecto a estos Informes.
EJEMPLO 4: Solución de un problema de mercadotecnia.
Abra el archivo Ejemplo de Opt por Solver.xls
Este modelo proviene de los ejemplos de Solver de la versión 97 del programa Excel.
És un modelo típico de mercadotecnia que muestra las ventas en función de los gastos en
publicidad y de un factor de temporada. Esta función es no lineal y se expresa como:
Unidades vendidas = 35*factor de temporada*(publicidad+3000) ^ 0.5
El gráfico siguiente corresponde a un factor de temporada igual a 1.
Observe que las ventas aumentan a partir de una cifra base (quizás debido al personal de
ventas) al incrementar la publicidad, pero con una caída constante en el flujo de caja.
Por ejemplo, los primeros $ 5,000 de publicidad producen aproximadamente un incremento
de 1.200 unidades vendidas, pero los $ 5,000 siguientes producen cerca de 800 unidades
adicionales. La gráfica a partir de los datos, se muestra en la siguiente figura
Figura 11
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 137 de 174
Se puede utilizar Solver para averiguar si el presupuesto publicitario es escaso y si la
publicidad debe orientarse de otra manera durante algún tiempo, para aprovechar mejor el
factor de temporada.
Caso 1: Encontrar el valor óptimo (Maximizando) sujeto a un valor cambiante.
Se desea saber cuánto se debe gastar en publicidad para generar el máximo beneficio en el
primer trimestre. El objetivo es maximizar el beneficio cambiando los gastos en publicidad:
Celda Objetivo: B15; Celda cambiante: B11.
Active la herramienta Solver
En <Celda objetivo> ingrese o haga clic en B16
Clic la opción Máximo
En <Cambiando las celdas> haga clic en B11
Haga clic en <Resolver>
El resultado obtenido indica que un gasto en publicidad en el primer trimestre, T1, de $ 17,093
produce un beneficio máximo de $ 15,093. Sin embargo, el margen de beneficio disminuye.
En efecto, si se grafica la función beneficio para distintos valores de gastos de publicidad, se
obtendrá:
0
20,000
40,000
60,000
80,000
T1 T2 T3 T4
Unid. vendidas
Flujo caja
Figura 12
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 138 de 174
Observe que esta función es cóncava hacia abajo y tiene un solo máximo igual a $ 15,093
cuando el gasto en publicidad alcanza una cantidad igual a $ 17,093.
Caso 2: Encontrar el valor óptimo (Maximizando) sujeto a varios valores cambiantes.
Si se desea obtener el máximo beneficio, cuál debe ser el gasto en publicidad en los cuatro
trimestres?
En este caso la celda objetivo es F15.
Las celdas cambiantes son: B11, C11, D11 y E11.
Al hacer clic en <Resolver> se encontrará la siguiente solución:
Se reducen los márgenes de beneficio pero se logra obtener un beneficio máximo igual a $
79,706 con un gasto en publicidad según se indica en la figura.
Pero... Y si sólo se disponía de $ 40,000 para ser gastado en publicidad durante el año?
La solución a esta pregunta implica resolver el problema usando restricciones.
Caso 3: Encontrar el valor óptimo (Maximizando) sujeto a restricciones.
$ 17,093
Figura 13
Figura 14
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 139 de 174
En este caso la única restricción viene dada por la limitación del gasto en publicidad: Que el
total de gasto no puede ser mayor a $ 40,000.
Esto significa que B11+C11+D11+E11 debe ser menor o igual a 40000
Para ello verifique que en F11 haya una fórmula igual a la suma de gastos en publicidad
(=B11+C11+D11+E11). Usaremos esta celda como restricción.
Vuelva a activar el Solver.
A los datos ingresados en el caso anterior vamos a añadirle una restricción.
Haga clic en <Agregar>.
En <Referencia de la celda> Ingrese F11 y
En <Restricción> ingrese 40000
Después de hacer clic en <Aceptar>, haga clic en <Resolver>.
Solver ha encontrado la solución óptima para este caso. Esto se aprecia en la siguiente figura
Esta es la solución óptima para el problema.
Guardar el modelo
Solver dispone de la opción <Guardar el modelo> para almacenar la solución en un rango de
celdas determinadas.
Si está en la ventana <Resultados del Solver> haga clic en <Cancelar>
Vuelva a usar el Solver y haga clic en <Opciones> Obtendrá la siguiente ventana:
Figura 15
Figura 16
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 140 de 174
Aquí puede determinar las restricciones de no negatividad (que todas las variables ≥ 0), el
tiempo, el número de iteraciones, la precisión en los casos de comparación y algunos otros
criterios en los cuales se basará el Solver para encontrar una solución.
En esta ventana también disponemos de un botón para <Guardar el modelo> o para <Cargar
el modelo> (si ya hubiera sido guardado).
Haga clic en <Guardar el modelo>. Obtendrá la siguiente ventana.
En ella se debe ingresar las celdas donde guardará las restricciones del modelo.
Seleccione en la hoja las celdas H16:H19. Luego haga clic en <Aceptar>.
Al volver a la ventana de opciones, haga clic en <Aceptar> y luego clic en <Resolver>.
Guardar el escenario
Al hacer clic en <Guardar el escenario> podemos guardar el entorno en el cual se ha
obtenido la solución. Bastará con dar nombre al escenario.
En cuanto a los Informes
Como puede ver, Solver genera tres tipos de informes: Informe de Respuestas, de
Sensibilidad y de Límites. Particularmente el segundo informe es más útil en cuanto al análisis
de sensibilidad del modelo.
Finalmente haga clic en <Utilizar solución de Solver> para tener la solución en la hoja. Luego
haga clic en <Aceptar>
5. EJERCICIO 1
Abra el archivo Modelo ProTrac por Solver.xls
En este libro se enuncia el problema, se formula el modelo y se tiene esquematizado la celda
objetivo y las celdas cambiantes (variables de decisión). Resuelva el problema por Solver
encontrando una solución óptima.
6. EJERCICIO 2
Abra el archivo Solver Ejercicio 2.xls
Formule este problema como un modelo de programación lineal y encuentre la solución
óptima usando la herramienta Solver
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 141 de 174
CAPITULO VI
PROTEGER HOJAS Y LIBROS.
MACROS. MODULOS.
OBJETIVO
El objetivo de este Capítulo es proporcionar la información necesaria para proteger y desproteger
hojas y libros. Crear y ejecutar macros y módulos en interacción con las hojas de cálculo.
METAS
Al completar este Capítulo el participante estará en capacidad de
➢ Saber proteger y desproteger hojas y libros.
➢ Saber crear, ejecutar, modificar y eliminar macros
➢ Ejecutar macros desde un botón incluido en las barras de herramientas
➢ Escribir, modificar y ejecutar módulos en VBA
TEMAS A TRATARSE
1. PROTEGER Y DESPROTEGER HOJAS Y LIBROS
2. MACROS
2.1. CREACIÓN DE MACROS
2.2. EJECUCIÓN DE MACROS
2.3. ASIGNACIÓN DE BOTONES A UNA MACRO
2.4. BOTONES DE LA BARRA DE FORMULARIOS
2.5. EJERCICIOS
3. MÓDULOS
3.1. EJEMPLOS
3.2. EJERCICIOS
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 142 de 174
1. PROTEGER Y DESPROTEGER HOJAS Y LIBROS
Una de las deficiencias que tienen los archivos que manejamos es su
protección. Aquel que tiene acceso al archivo está en capacidad de
modificar su contenido. Si hablamos de una hoja de cálculo, quien abre
un libro puede modificar los datos, las fórmulas y todo lo que haya en
ella.
Claro está que en el momento de grabar el archivo existe la opción de
asignarle una contraseña que puede ser de sólo lectura o aquélla que no
le permite editar su contenido.
Pero si bien puede ser la solución en un archivo en general, en el caso
de una hoja de cálculo no siempre es buena. En una hoja de cálculo
existen celdas de datos con las cuales se calculan otras celdas y se
generan las soluciones. Al tener acceso de sólo lectura o al no poder
editarlas, no podríamos ingresar nuevos datos para generar otras
soluciones. En el capítulo anterior se vieron el uso e importancia de las
plantillas. Si no tuviéramos la facilidad de editar y modificar el contenido,
no podríamos usarlas para generar nuevos libros.
Por ello es que en Excel disponemos de la herramienta de Protección y
Desprotección de hojas y libros, que es lo que haremos ahora.
Usando <Herramientas> - <Proteger> accedemos a las opciones que se
muestran en la figura 1.
Según esto podemos proteger hojas, proteger libros, proteger y compartir
libros y permitir que los usuarios modifiquen rangos.
1.1. Proteger hoja
Al hacer clic en la opción <Proteger hoja> obtenemos la ventana
que se muestra en la siguiente figura:
Figura 1
Proteger hoja
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 143 de 174
Si se activa la primera casilla se estará protegiendo la hoja y el
contenido de todas las celdas bloqueadas.
Tenemos también la capacidad de ingresar una contraseña que
permita desproteger la hoja. Esto es, si la hoja ya está protegida, la
única forma de desprotegerla será ingresando la contraseña.
En la lista inferior se muestran un conjunto de acciones que
pueden quedar disponibles o inhabilitadas, según se active o no la
opción. Según la figura 2, el usuario podrá seleccionar celdas
bloqueadas y desbloqueadas, pero no podrá usar varias opciones
que tiene el comando Formato; no podrá insertar o eliminar filas o
columnas, ni crear vínculos, entre otras.
Nota:
Para proteger una parte de la hoja y desproteger otras se debe,
ante todo, desbloquear las celdas que deben quedar
desprotegidas, al proteger la hoja.
1.2. Ejemplo 1
Abra el archivo Formularios.xls.
Copie el rango B4:B16 hacia C4:G16
Use la secuencia:
<Herramientas> - <Proteger> - <Proteger hoja> - <Aceptar>
Ahora intente ingresar cualquier dato en una celda cualquiera.
Intente usar la secuencia <Formato> - <Celda>
Intente usar la secuencia <Formato> - <Columna/Fila>
Como puede ver, no es posible usar ninguna de estas opciones.
No se puede modificar el contenido de ninguna celda.
Figura 2
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 144 de 174
Pero observe Usted que en esta hoja necesitamos ingresar la
cantidad de productos vendidos en cada mes, en la fila 4.
Se requiere desproteger el rango B4:G4 y que el resto quede
protegido.
1.3. Ejemplo 2
Use el mismo archivo anterior.
Resolveremos la inquietud del ejemplo anterior.
Paso 1: Desbloquear el rango que debe quedar desprotegido
Seleccionar el rango B4:G4
Use <Formato> - <Celda> - <Proteger>
Haga clic en la casilla de Bloqueada
Clic en <Aceptar>
Paso 2: Proteger la hoja
Use la secuencia: <Herramientas> - <Proteger> - <Proteger hoja>
Pruebe si puede modificar las celdas desbloqueadas o aquellas
que no fueron desbloqueadas.
USO DE FORMULARIOS
Recuerde que debe haber copiado B4:B16 hacia C4:G16.
Aprovechemos de esta capacidad de protección en un Formulario.
El Excel nos permite usar Formularios.
Primero seleccione el rango A3:G16
Use la secuencia: <Datos> - <Formulario>
Formulario
Figura 3
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 145 de 174
Como se puede apreciar en la figura anterior, haciendo clic en la
barra de desplazamiento vertical de la ventana del formulario, se
puede visualizar el contenido de cada fila del cuadro. Las únicas
celdas que tienen cuadro de texto en los cuales tenemos opción a
ingresar un valor, corresponde justamente a las celdas que se
desprotegieron. Las otras celdas sólo permiten ver el contenido
pero no modificar ni editar su contenido.
Ingrese en Enero, 100. En Febrero 180. En los meses siguientes
150, 240, 240 y 320. Observe que todas las celdas que dependen
directa o indirectamente de B4:G4, sufren cambios, por cada
cambio en la cantidad vendida.
Diversas son las aplicaciones que se puede dar a esta herramienta
del Excel. En principio podemos pensar en utilizarlo para el ingreso
de datos hacia una hoja que debe realizar determinados cálculos;
se puede asignar el trabajo de ingresar los datos a terceras
personas quienes no deben tener acceso a la modificación de la
hoja; se puede desactivar todas las barras de herramientas y
objetos de la ventana del Excel a fin de presentar una pantalla de
aplicación; etc.
1.4. EJERCICIO
Abra el archivo Aplicaciones Financieras Proteg.xls
Calcule las celdas C6, C7 y C8.
Luego proteja la hoja de tal forma que sólo se pueda modificar las
celdas C1, C2, C3 y C5.
2. MACROS
Muchas personas o usuarios, responsables de la manipulación de datos,
por lo general siempre están haciendo las mismas acciones, sea que
esto signifique dar formato, realizar operaciones de cálculo, construir
gráficos, etc. Muchas de estas acciones los tienen que realizar con
mucha frecuencia, todas las semanas, todos los días, varias veces por
día. Si tiene que consolidar diariamente archivos de ventas provenientes
de varias tiendas, siempre está haciendo lo mismo, sobre las mismas
celdas, sobre los mismos rangos; tanto es así que puede hacerlo con los
ojos cerrados. Es una rutina lo que hace, siempre sigue una secuencia
fija de pasos para completar el trabajo con cada uno de los archivos.
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 146 de 174
Frente a esto, por qué no automatizar las operaciones que realiza?; esto
es, ¿podríamos instruirle al Excel para que almacene la secuencia de
pasos que debe realizar en cada proceso y pedirle después que lo haga,
cuantas veces queramos?
Sí es posible hacerlo. El programa Excel nos permitirá automatizar
muchos procesos. Esto lo hace mediante el manejo de MACROS.
Concepto de Macro
Una macro es un módulo compuesto por un conjunto de instrucciones
que permiten automatizar determinados procesos.
Supongamos que cada hoja nueva que creamos siempre lo hacemos a
partir de una plantilla:
- seleccionamos el rango A5:G6,
- aplicamos negrita,
- seleccionamos tamaño 14;
- seleccionamos fuente a Times New Roman.
- luego seleccionamos el rango A6:A18,
- insertamos borde;
- seleccionamos B6:G18,
- insertamos borde.
En lugar de estar realizando estas ocho acciones, podemos crear una
macro e invocarla para que ejecute los ocho pasos automáticamente.
Crear una macro en Excel implica grabar la secuencia de operaciones
que se desea automatizar y almacenarla en un módulo. Un módulo es un
programa y cada acción que realiza la macro es una instrucción del
programa. De manera que ejecutar una macro significa ejecutar el
programa que está detrás de la macro; grabar una macro significa
escribir el programa.
Pero nada de dicho módulo ni cómo se escribe el programa ni el lenguaje
que se usa, es necesario para manipular una macro.
La macro tendrá un nombre y una forma para ejecutarla.
Definición de
Macros
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 147 de 174
2.1. CREACIÓN DE MACROS
Una macro se puede crear usando la barra de herramientas del
Visual Basic. Para acceder a ella, use la siguiente secuencia:
<Ver> - <Barra de herramientas> - <Visual Basic>
La barra de herramientas del Visual Basic se muestra a
continuación.
El primer botón se convierte en un cuadrado cuando se está
grabando una macro y permite detener la grabación.
El segundo, un disco, permite iniciar la grabación de una nueva
macro.
El siguiente botón nos permite ingresar al editor del Visual Basic
El tercer botón permite modificar la seguridad que proporciona el
Excel para la acción de las macros.
El quinto botón nos permite acceder al cuadro de controles.
El sexto botón permite pasar del modo de edición de una macro al
modo de ejecución de la misma.
El último botón permite la manipulación de una secuencia de
comandos de Microsoft relacionados con el lenguaje XML mediante
el cual se pueden editar y modificar páginas Web relacionadas con
una o más hojas de un libro.
Otra forma de acceder al manejo de macros es usando la siguiente
secuencia:
<Herramientas> - <Macros>, con lo que dispondremos de las
siguientes opciones, mostradas en la figura 5:
Tiempo de diseño Editor de Visual Basic
Para grabar o detener grabación
Para ejecutar
Figura 4
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 148 de 174
La opción <Grabar nueva macro> se convierte en <Detener
grabación> cuando se está grabando la macro.
Nota:
➢ Antes de grabar una macro se debe tener muy claro las
operaciones que queremos grabar. Durante la sesión de
grabación de la macro se deberá evitar corregir una acción.
➢ Al terminar de grabar una macro se debe detener la grabación
antes de realizar cualquier acción en Excel.
EJEMPLO 1
Grabar una macro que, usando los datos del archivo Macros 01.xls,
calcule el total de las ventas diarias de todos los cajeros.
Solución
Abra el archivo Macros 01.xls
Active la hoja Macro 01.
Active la barra de herramientas de Visual Basic usando: <Ver> -
<Barra de herramientas> - <Visual Basic>
Haga clic en el botón para iniciar la grabación de una nueva
macro. Obtendrá la siguiente ventana:
En esta ventana se debe ingresar un nombre para la macro. Digite
Figura 6
Figura 5
Ejecutar,
Editar,
Modificar
macros Grabar nueva
macro
Ingrese una
letra pero no
las que usa
Excel.
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 149 de 174
Mac01. En términos de programación, Mac01 será el nombre del
programa que se generará cuando se grabe la macro.
En <Método abreviado> se debe ingresar una letra. De preferencia
debe ser una letra distinta de las que emplea el Excel para sus
métodos abreviados; por ejemplo <CTRL>+c es usado para copiar;
<CTRL>+v es usado para pegar; <CTRL>+n es usado para
negrita.
En consecuencia ingrese la letra z.
En el cuadro de texto <Guardar macro en> se puede guardar la
macro en un libro de macros personal, en este libro (el que está en
edición) o en un nuevo libro.
En este caso lo dejaremos con la opción <Este libro>
Finalmente podemos introducir un breve comentario en
<Descripción>. Podríamos digitar: “Esta macro calcula el total de
las ventas diarias de todos los cajeros”.
Haga clic en <Aceptar>
En este momento ya estamos en modo de grabación. Usted verá
en pantalla un botón adicional que nos permite detener
la grabación de la macro.
Nota:
Tenga en cuenta que todo lo que haga hasta que
detenga la grabación, será grabado en la macro
Mac01.
Realice las siguientes acciones:
Haga clic en F4
Ingrese la fórmula: =Suma(B4:E4) y presione <Enter>
Copie hacia las otras filas.
Haga clic en otra celda cualquiera (digamos A1)
Detenga la grabación
➢ Haciendo clic en el botón de pequeño recuadro,
➢ Haciendo clic en el segundo botón de la barra de herramientas
del Visual Basic o
➢ Usando la secuencia <Herramientas> - <Macro> - <Detener
grabación>
Si usa
<ctrl.>+c, el
Excel inhibe su
método
abreviado de
copiar y respeta
su macro.
Puede usar las
mayúsculas.
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 150 de 174
2.2. EJECUCIÓN DE MACROS
Vamos a ejecutar una macro previamente grabada.
Para ejecutar una macro puede usar cualquiera de las siguientes
opciones:
➢ Hacer clic en el primer botón de la barra de herramientas del
Visual Basic
➢ Usar <Herramientas> - <Macro> - <Macros ...> - <Seleccionar
la macro> - <Ejecutar>.
➢ Presionando simultáneamente: <CTRL>+tecla con el cual se
grabó la macro.
En este caso usemos esta última opción:
Pero antes, para ver el efecto de la macro, borre todo lo que se ha
calculado (rango F4:F9).
Presione: <CTRL>+z
Borre lo que ha calculado y vuelva a ejecutar
Vuelva a borrar y vamos a ejecutar la macro usando la segunda
opción:
<Herramientas> - <Macro> - <Macros>.
En la ventana siguiente seleccione la macro Mac01, como se
muestra en la siguiente figura:
Como puede apreciar, para ejecutar una macro se selecciona su
nombre de la lista y se hace clic en el botón <Ejecutar>.
Por esta ruta podemos también ejecutar la macro paso a paso,
podemos eliminar la macro seleccionada o podemos modificar la
macro.
Para modificar una macro deberá hacer clic en el botón
<Modificar> después de haber seleccionado el nombre de la
macro.
Pero modificar una macro significa editar el programa que se
Figura 7
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 151 de 174
generó durante la grabación de la macro.
Nota:
El modificar una macro nos llevará indefectiblemente a
la ventana del Editor del Visual Basic, que es donde
se modificará la macro. Si no desea usar esta
modalidad y la macro no hace lo que Usted desea, es
mejor eliminarla y grabar otra.
EJEMPLO 2
Abra el archivo Macros 01.xls
Active la hoja Macro 02
Grabe una macro que se llame Mac02; que su método abreviado
sea <CTRL>+<o>; que se guarde en este libro.
Que haga lo siguiente:
Que calcule la fila y columna de Total.
Rango B2:E2: que combine; negrita; borde; tamaño 14; Times New
Roman
Rango B3:D3: borde; relleno azul; tamaño 11; Arial
Celdas A16 y E3: borde; amarillo oscuro
Rango A4:A15: borde; arial; amarillo oscuro
Rango B4:D15: borde; canela; arial 10
Rango E4:E16 y B16:D16: borde; gris 25%
Detenga la grabación de la macro.
Borre todo lo que debe hacer la macro
Ejecute la macro
EJERCICIO
Abra el archivo Macros 01.xls
Active la hoja Macro03. Calcule todo lo que allí se pide y asigne un
formato que Usted cree conveniente.
EJERCICIO
Abra el archivo SesMac01.xls
Resuelva el problema que allí se pide.
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 152 de 174
2.3. ASIGNACIÓN DE BOTONES A UNA MACRO
Como puede haber comprobado en la sección anterior, el uso de
macros permite automatizar un conjunto de operaciones que, de
otra manera, resultaría hasta tediosa su manipulación. Toda vez
que tenga que realizar operaciones en las que siempre se está
haciendo lo mismo y sobre las mismas celdas o rangos de celda,
use macros.
Naturalmente las macros se convierten en herramientas muy
potentes si logramos modificar sus opciones ingresando al módulo
(programa) que está por detrás de la macro. Este es tema de la
siguiente sección.
Lo que ahora queremos es tomar en cuenta lo que ocurriría si
hacemos uso intensivo de macros. Quizás si tuviéramos más de 10
macros, nos van a faltar letras. En este caso se ppodría usar la
letras mayúsculas.
Sin embargo, corremos el riesgo de equivocarnos de la letra con la
cual se ejecuta una macro. En este caso podemos usar la
secuencia <Herramientas> - <Macro> - <Macros>. Claro está que
el procedimiento es más lento que el usar el método abreviado.
¿Podríamos usar botones como los que contienen las barras de
herramientas? Si así fuera, bastaría con hacer clic en el botón.
Sí podemos insertar botones en la ventana de la hoja y asignarle
una macro de manera que al hacer clic en dicho botón, se logre
ejecutar la macro.
Insertar un icono en una barra de herramientas de pantalla
Vamos a insertar el icono que nos permite asignarle una macro.
Para ello use la siguiente secuencia:
<Ver> - <Barra de herramientas> - <Personalizar> - <Comandos> -
<Macro>
Arrastre el único icono disponible hacia una posición dentro de as
barras que se muestran en la pantalla y suéltelo. Esto se muestra
en la siguiente figura.
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 153 de 174
Haga clic en el botón <Modificar selección>. Luego haga clic en
<Cambiar imagen del botón>, seleccione una de las imágenes que
están disponibles para cambiar el icono estándar de macro.
Nuevamente haga clic en <Modificar selección> y después en
<Asignar macro>. En este punto se obtendrá la ventana desde la
cual se podrá seleccionar la macro que se desea. Luego haga clic
en <Aceptar>.
Ahora, para ejecutar la macro será suficiente hacer clic en el botón
que acaba de crear.
Insertar un botón desde la barra de herramientas de
formularios
El botón que será usado para ejecutar una macro será extraído
desde la barra de herramientas de Formularios.
Para disponer de esta barra se debe usar la secuencia:
<Ver> - <Barra de herramientas> - <Formularios>
Que es lo que se aprecia en la siguiente figura.
En esta sección sólo usaremos el botón llamado <Botón>.
Haga clic en este botón
En la hoja activa despliegue un pequeño rectángulo en un área que
no está ocupado por los datos de la hoja.
Al soltar el botón izquierdo, de manera automática obtendrá la
Permite ejecutar macros o módulos
Figura 8
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 154 de 174
ventana en la cual se listan las macros grabadas.
Seleccione la macro a la cual quiere asignar el botón y haga clic en
<Aceptar>.
Estando activado el botón haga clic al interior de él para digitar un
nombre. Digite “Ejecuta macro”
Para desactivar la selección del botón haga clic en cualquier otro
sitio de la hoja.
Si pasa el puntero del ratón sobre el botón verá que aparece una
mano, la cual indica que podemos hacer clic en él y ejecutar la
macro.
2.4. OTROS BOTONES DE LA BARRA DE FORMULARIOS
Ahora vamos a usar algunos de los botones de la barra de
formulario de tal forma que podamos diseñar hojas de cálculo más
inteligentes y versátiles en su manejo.
Para qué podríamos usar el botón de opción?
Use este botón toda vez que tenga que activar un elemento de una
lista de opciones.
Por ejemplo si se trata de seleccionar una boleta o factura; si debe
responderse con un Si o No; si se tiene un conjunto de productos,
uno sólo de los cuales debe ser elegido.
Para qué podríamos usar la casilla de verificación?
Use este botón cuando tenga que seleccionar uno o más
elementos de una lista de elementos. Cada elemento tiene una
casilla de verificación.
Por ejemplo si dispone de una lista de productos y desea
seleccionar uno o más de ellos para ser extraídos; podrá hacer clic
en sus respectivas casillas para eliminarlos, copiarlos, etc.
Para qué podríamos usar el botón de cuadro combinado?
Use este botón para seleccionar un elemento de una lista de
elementos. En este caso se dispone de una lista. Se puede
navegar por toda la lista y seleccionar uno de ellos.
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 155 de 174
Para qué podríamos usar el botón barra de desplazamiento?
Se usará este botón si deseamos seleccionar un valor aumentando
o disminuyendo cada vez que se hace clic en los extremos del
botón.
Para qué podríamos usar el botón de control de número?
Como en el caso anterior, solo que aquí solo se visualiza el valor
que aumenta o disminuye.
EJEMPLO 1
Abra el archivo Macros 01.xls.
Active la hoja Botones.
Defina con el nombre Coche al rango P2:P13; con el nombre
Precio al rango Q2:Q13.
Estando en pantalla la barra de herramientas de Formularios, haga
clic en el botón de Cuadro combinado y despliegue un rectángulo
entre las celdas E9 y F9.
Haga clic con el botón derecho sobre este botón y en el menú
contextual que aparezca, haga clic en <Formato de control... >.
Haga clic en la ficha <Control>. Obtendrá la siguiente ventana.
Haga clic en <Rango de entrada> y digite Coche. Si el rango no
tuviera nombre deberá digitar el rango.
Haga clic en <Vincular con la celda> y luego haga clic en I1.
Haga clic en <Aceptar>
Si ahora hace clic en el botón, cuando se visualice una mano, verá
desplegarse la lista de los coches.
Seleccione un coche cualquiera y observe el valor que aparece en
Use una celda
algo alejada
de su área de
trabajo y
recuérdelo.
Figura 9
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 156 de 174
la celda I1.
En la celda B9 ingrese la fórmula: =Indice(Precio,I1)
Ahora ya tiene el precio del vehículo.
Vamos a colocar un botón de barra de desplazamiento para
manejar el descuento.
Para ello haga clic en dicho botón de la barra de Formularios
Despliegue un recuadro en la fila 11 y entre las columnas E y F.
Haga clic con el botón derecho, seleccione <Formato de control> y
haga clic en la ficha <Control>. En <Valor mínimo> deje en 0 y
<Valor máximo> ingrese 30. (Suponemos que no es posible un
descuento superior a los 30% de su valor). Haga clic en <Vincular
con la celda> y luego clic en I2.
Luego clic en <Aceptar>.
Para probar, haga clic en los extremos del botón y vea cómo va
cambiando el valor sea aumentando o disminuyendo. Observe el
valor en I2.
En la celda B10 ingrese la fórmula: =I2/100*B9
Calcule la celda de Préstamo, B11: =B9-B10
Ingrese en B12 la tasa de interés anual. Que sea 8%.
Vamos a desplegar un botón de número para obtener el número de
años. Haga clic en el botón <Control de número>, despliegue un
recuadro entre las filas 13 y 14; luego clic con el botón derecho y
seleccione <Formato de control>; haga clic en la ficha <Control> y
en <Valor máximo> digite 30. En <Vincular con la celda> digite I3.
Pruebe el botón haciendo clic en incrementar o disminuir y observe
el valor elegido en la celda I3.
En la celda B13 digite: =I3
Como en la celda B14 ya se había ingresado la fórmula que
permite calcular el pago mensual que debe hacerse para cancelar
el préstamo, se verá dicho monto automáticamente.
Ahora seleccione otro vehículo y observe cuánto debe pegar
mensualmente. Cambie el porcentaje de descuento o cambie el
número de años y vea cómo se obtiene los resultados de
inmediato.
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 157 de 174
EJEMPLO 2
Abra el archivo Plantas.xls
Active la hoja Venta. Haga clic en el botón Cuadro de Grupo y
despliegue un recuadro entre las celdas G5:H10.
En el nombre ingrese “Tipo de documento”.
Al interior de este cuadro despliegue dos botones de opción, uno
con el texto “Boleta” y otro con “Factura”
Haciendo clic con el botón derecho en cada uno de ellos, vaya a la
ventana de Formato de control y en la ficha Control haga clic en
<Vincular con la celda> haga clic en K3. Los dos botones deben
estar vinculados con K3.
En G2 digite: =SI($K$3=”1”,”BOLETA”,”FACTURA”)
Haga que esta celda esté en negrita y tamaño 16.
Despliegue otro Cuadro de Grupo entre las celdas G11 y H16 y un
tercero entre G17 y H20. El nombre para el primero que sea
“Módulo del cliente” y del tercero: “Cambio al día”.
En G13 ingrese el texto: Descuento especial.
Despliegue una barra de desplazamiento horizontal debajo de
Descuento especial. Vincule con la celda K2.
En G16 digite: =K2/100. Asigne formato porcentual a esta celda.
En G19 ingrese 3.48.
EN F6 digite: =SI(E6>0,D6*E6*$G$19,"")
Copie esta fórmula hasta F15.
En F16 digite: =SI(SUMA(F6:F15)>0,SUMA(F6:F15),"")
En F17 digite: =SI(F16>0,$K$2/100*F16,"")
En F18 digite: =SI(F16>0,19%*F16,"")
En F20 digite: =SI(F16>0,F16-F17+F18,"")
Pasemos ahora a la hoja Almacén.
Para cada producto y para cada equipo, despliegue un botón de
casilla de verificación alineados y al costado del precio de cada
producto. Al usar el botón derecho, Formato de Control y la ficha
Control, debe asignarle sucesivamente como celda vinculante, G1,
G2, G3, ..., G47, G48. Para los equipos: G54, G55, ..., G59.
En A67 digite: =SI(G1,A4,””)
Copie esta fórmula hasta A114. En A115 digite: =Si(G54,A54,””).
Luego copie hasta A120.
En B67 digite: =SI(A67>0,BUSCARV(A67,$A$4:$C$59,2,FALSO))
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 158 de 174
Copie esta fórmula hasta B120.
En C67 digite: =SI(A67>0,BUSCARV(A67,$A$4:$C$59,3,FALSO))
Copie hasta C120.
Vamos a grabar dos macros: El primero va permitir limpiar el área
donde se van a ir colocando los valores al hacer clic en cada
casilla; es decir, vamos a limpiar las celdas vinculantes, cada vez
que se atiende un nuevo pedido. La segunda macro va a
seleccionar los productos deseados, va copiar de la parte inferior
usando copiado especial con F5, hacia la hoja Venta y lo pegará en
el espacio destinado para los números, nombres y precio de los
productos.
Primera macro: Nombre: Limpiar
Estando en la hoja Almacén
<Herramientas> - <Macro> - <Grabar nueva macro>
Seleccione el rango G1:I60
Presione la tecla suprimir <Supr>.
Detenga la grabación de la macro.
Segunda macro. Nombre: Generar.
Use <Herramientas> - <Macro> - <Grabar nueva macro>
Haga clic en la etiqueta de la hoja <Ventas>
Seleccione el rango B6:E15. Presione la tecla <Supr>
Haga clic en la etiqueta de la hoja <Almacén>.
Seleccione el rango A67:A120
Presione <F5>. Haga clic en <Especial>. Como el rango
seleccionado contiene fórmula, haga clic en <Celdas con fórmula>,
luego desactive las otras excepto <Números>. Luego <Aceptar>.
Copie.
Haga clic en la hoja <Venta>. Luego clic en B6. Pegue.
Haga clic en la hoja Almacén. Seleccione el rango B67:B120;
presione <F5>. Clic en <Especial>. Seleccione <Celdas con
fórmulas> y deje activado sólo <Números>. Copie.
Haga clic en la hoja <Venta>. Clic en C6. Pegue.
Repita el procedimiento con C67:C120 y pegue en D6.
Detenga la grabación.
Asignar botones a las dos macros.
Haga clic en el botón Botón de la barra de herramientas de
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 159 de 174
Formularios. Despliegue un recuadro al obtener la ventana de
macros, selecciona la macro Limpiar. Luego haga que se nombre
sea “Nuevo”.
Despliegue otro botón para la macro Generar. Que este botón
tenga por nombre “Generar orden”.
Para ejecutar, primero presione el botón <Nuevo>
Luego haga clic en las casillas de las plantas y equipo que Usted
desee.
A continuación presione el botón <Generar>.
2.5. EJERCICIO
Abra el archivo Análisis de venta.xls
En las columnas Q hasta W hay información respecto a los
productos, su precio, costo, descuento.
Inserte un botón de Cuadro combinado que le permite seleccionar
un producto de la lista y luego complete toda la hoja ingresando las
fórmulas que le permita calcular toda la hoja.
Si desea grabe una macro para que haga todos los cálculos
después de haberse obtenido el nombre del producto en A4
mediante el uso del botón.
3. MODULOS
Un módulo es un programa escrito en un determinado lenguaje de
programación de computadora. Por lo general un módulo resuelve una
parte específica de un problema. Desde este punto de vista, la solución
de un problema por la computadora se realiza mediante la ejecución de
varios módulos debidamente secuenciados y en lo posible hasta pueden
ejecutarse paralelamente dos o más módulos (ejemplo: los threads).
Al grabar una macro en Excel lo que se hace es grabar un módulo. Cada
acción grabada en una macro constituye una instrucción en el módulo. El
lenguaje que se emplea para escribir estos módulos es el Visual Basic
Applications (VBA). El VBA no es el lenguaje Visual Basic; es un lenguaje
compuesto por una gran cantidad de propiedades y métodos que sólo
son posibles usarlos en una hoja de cálculo del Excel. Un programador
experimentado en Visual Basic no se sentirá disminuido al escribir
módulos y funciones en VBA; sino que muy por el contrario puede
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 160 de 174
realizar su trabajo de manera mucho más sencilla y manejando objetos
como Libros, hojas de cálculo, rango de celdas, etc.
El Excel dispone de este lenguaje para escribir módulos y funciones
capaces de resolver problemas altamente complejos, limitados sólo por la
capacidad del autor.
Para ello dispone del Editor del Visual Basic, donde se ingresa las
instrucciones del módulo o función y en el cual se modifica el módulo que
pone en ejecución a una macro.
Para escribir un módulo se debe ingresar al Editor del Visual Basic.
Antes de acceder al Editor del Visual Basic, abra un libro vacío.
Para abrir el editor use la secuencia:
<Herramientas> - <Macro> - <Editor de Visual Basic>, como se muestra
en la siguiente figura
A continuación se tendrá la siguiente ventana.
Figura 10
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 161 de 174
En esta ventana tenemos algunos objetos que los vamos a describir muy
brevemente.
La Ventana de Proyectos. En esta ventana se tiene un conjunto de
carpetas utilizadas tanto por el Excel como por el usuario. Las carpetas
de la parte superior contienen funciones y módulos propios de las hojas
que emplea el Excel. Esas carpetas están protegidas con una
contraseña.
Luego se tiene una lista de carpetas en las cuales se encuentra una por
cada libro (WorkBook) que estuviera abierto; en este caso Libro1. Dentro
cada WorkBook se tiene las hojas (WorkSheet) que contiene el libro.
Estas hojas tienen un nombre y una etiqueta. Su etiqueta es Hoja1
(Sheet1) y su nombre es Hoja1 (Sheet1) o el que el usuario le da, como
es el caso de Hoja2 que se llama Trabajo.
Al final de esta lista de carpetas tenemos las carpetas de módulos
(siempre que el libro tenga módulos en alguna de sus hojas; en este caso
no se ve esta carpeta ya que el libro no tiene ningún módulo o macro en
él. Dentro de la carpeta Módulos (Module) pueden haber más de un
Insertar Módulo
o Formulario
Volver al Excel
Ventana de
Proyecto
Para ver código
Hoja activa
Segunda hoja
Aquí estará la
carpeta de
módulos
Ventana de
propiedades
Ver/Ocultar
hoja
Figura 11
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 162 de 174
módulo (Módulo1, Módulo2, etc.). Estos se ejecutan probablemente al ser
invocadas por una de ellas. Si en una misma sesión se grabara tres
macros, los módulos correspondientes a estas macros estarán
contenidos en Módulo1. Nosotros podemos insertar todos los módulos
que queramos.
Para insertar o añadir módulos se hace uso del comando <Insertar> del
Editor, como lo indica la figura anterior.
Este comando permite también, entre otras cosas, insertar o añadir
nuevos Formularios (UserForm). Estos son los documentos llamados
formularios a los cuales se tiene acceso en el lengua Visual Basic. A este
formulario le acompañará la ventana del Cuadro de Controles, con los
cuales se diseña el formulario.
Para visualizar los módulos contenidos en un libro, hoja o carpeta de
módulo, haga clic en él primero y luego haga clic en el botón del extremo
izquierdo de la parte superior de la ventana de proyecto <Ver código>.
En la parte inferior se encuentra la Ventana de Propiedades. En ella se
encuentran todas las propiedades y su valor definido para ellas, de los
objetos existentes en la Ventana de Proyectos.
Haga clic en la hoja2 (Trabajo) de libro que aparece en la Ventana de
Proyectos y vea como se modifica la Ventana de Propiedades.
Una de las primeras propiedades es (Name), la que nos permite darle
nombre a la Hoja y es el que se usará al escribir el módulo. Algunas
líneas más abajo (9) tenemos también la propiedad Name. Este es el
nombre que se visualiza en la etiqueta de la hoja al estar en el objeto
Excel.
Una de las últimas propiedades es el estado de la hoja (oculta o visible).
Haga clic en el lado derecho de la propiedad <Visible>. Use la barra de
desplazamiento vertical, si fuera necesario. Seleccione de la lista el valor
o atributo <0 – xlSheetHidden>. Ahora vuelva al Excel haciendo clic
como se indica en la figura anterior o haciendo clic en la barra de tareas
del Windows. Vea si se puede visualizar la hoja2 llamada Trabajo.
Retorne al Editor, seleccione la Hoja2 en la ventana de proyecto y, en la
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 163 de 174
ventana de propiedades, en Visible, seleccione <-1 – xlSheetVisible>.
A continuación vamos a crear algunos ejemplos de módulos muy
elementales, para luego completar con uno que tenga cierta utilidad.
3.1. EJEMPLOS
Ejemplo 1
Escriba un módulo que permita emitir un mensaje de bienvenida.
Solución
Estando en una hoja vacía, use la secuencia <Herramientas> -
<Macro> - <Editor de Visual Basic>.
Use la secuencia <Insertar> - <Módulo> para añadir un nuevo
módulo. Obtendrá la siguiente ventana.
Al digitar Sub Saludos y presionar <Enter>, automáticamente
obtendrá lo siguiente:
Sub Saludos()
|
End Sub
Todo módulo tiene nombre; en este caso se le ha dado el nombre
Saludos. Después del nombre y encerrado entre paréntesis, vienen
los argumentos del módulo. Si el módulo no tiene argumentos, se
debe colocar necesariamente los paréntesis. Del mismo modo, todo
módulo termina con la última instrucción que hace que el Visual
Basic sepa que allí termina el módulo.
Al interior de estas dos líneas de inicio y final del módulo se deben
ingresar todas las instrucciones que queremos que sean ejecutadas
al ejecutarse el módulo.
Digite aquí:
Sub Saludos
Presione
<Enter>
Figura 12
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 164 de 174
Ingrese ahora la instrucción siguiente:
MsgBox (“Hola !, Bienvenidos al Excel con VBA”)
Vamos a ejecutar este módulo llamados Saludos.
Para ejecutar un módulo se debe tomar en cuenta la barra de
herramientas del Visual Basic en la ventana del Editor.
Estando el cursor en el interior del módulo, haga clic en <Ejecutar
módulo>.
Como puede ver, el resultado de la ejecución del módulo se hace en
los predios de la hoja activa del libro activo, del Excel.
Haga clic en <Aceptar> en la ventana que ha emitido el Método
MsgBox(...).
Nota Previa:
Un módulo es un programa
Un módulo está formado por un conjunto de instrucciones
Una instrucción es una expresión formada por variables y
métodos y funciones; como las fórmulas en Excel.
En un módulo se puede incluir procedimientos a los cuales se
les llama métodos y funciones, que devuelven un valor.
Una variable tiene un nombre: Stot, X, ahora, X21, Cadena05;
etc. Una variable contiene un valor. Este valor puede ser un
número o una cadena de caracteres.
Una variable recibe un valor usando: variable = Expresión.
Ejemplos:
X1 = “SAMESA”
Ir al
Excel Insertar
módulo
Ejecutar un
módulo
Detener la
ejecución
Figura 13
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 165 de 174
Canti = 120
PreUnit=5.87
Monto = Canti+PreUnit
Titulo = X1+” Producciones”
Al inicio del módulo use DIM para definir las variables
Explicación de MsgBox(...)
Este es un procedimiento o método del Visual Basic que permite
emitir en una ventana un determinado mensaje o el valor de las
variables contenidas como argumentos.
Tiene muchos argumentos. Para lo que queremos es suficiente decir
que MsgBox puede ser usado para emitir mensajes o valores de
variables.
Ejemplos:
MsgBox(“Ingresa un número.”)
MsgBox(“El monto total es “ & Stot & “ soles.”)
Texto = MsgBox(“Bienvenido....!”,,”Ventanita de Saludos”)
Observación:
En un módulo podemos insertar en una o más líneas de comentarios.
Un comentario es un texto cualquiera precedida por apóstrofe ( ‘ ).
Ejemplo 2
Con qué función o método se ingresan datos para las variables?
Solución
La función que permite el ingreso de datos es
Variable = InputBox(Mensaje, Titulo)
Al ejecutarse, en la ventana Título , se emite el Mensaje y en un
cuadro de texto, se debe ingresar por teclado el dato solicitado.
Ejemplos
Nombre = InputBox(“Ingresa tu nombre”)
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 166 de 174
Apel = InputBox(“Ahora tu apellido paterno....”)
Titulo = “Hola” + Nombre + “ “ + Apel
Edad = InputBox(“Ahora ingresa tu edad”,Titulo)
MsgBox(“Gracias ...”)
MsgBox(“Te llamas: “ & Nombre & Apel & “ y tienes “ & Edad & “
años”)
Inserte otro módulo
Digite: Sub Dialogo y presione <Enter>
Digite todo el ejemplo al interior de este módulo
Ejecute el módulo
Observación:
1. Note que hay palabras que necesitaban de un espacio en blanco;
en otros sí les hemos puesto.
2. No es necesario grabar el contenido del Editor. Es suficiente
grabar el libro activo para que los módulos se graben. Cada vez
que se abra el libro, estarán disponibles los módulos.
Vamos a formalizar el uso de las variables en el módulo
Todas las variables que se usen debieran ser declaradas antes de
ser usadas. Esto se hace con DIM.
La sintaxis es: DIM Variable AS Tipo
Si la variable va a contener cadena de caracteres entonces declare
como de tipo STRING; si va a contener valor numérico, declare como
de tipo INTEGER si será entero o DOUBLE si tendrá decimales.
Pero cuando a una variable se usa en más de una forma, use el tipo
Variant (una variable de tipo string puede recibir datos de tipo Char).
Ejemplo:
Dim Titulo, Apel, Nombre As String
Dim Edad As Integer
¿Cómo podemos hacer que estos datos se almacenen en
determinadas celdas de la hoja activa?
Como se trata de manejar las hojas del Excel, debemos entonces
seleccionar la hoja (lo mismo se hace si se trata con varios libros que
estén abiertos). Para ello se emplea la siguiente sentencia:
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 167 de 174
Sheets(Indice).Select
en donde Indice puede ser el número de hoja o el nombre de la hoja:
Hoja1, Hoja2, Trabajo, ec.
Ejemplo:
Sheets(1).Select
Sheets(“Hoja1”).Select
Sheets(“Trabajo”).Select
Nota:
Para activar el uso de una hoja: Use una de las siguientes:
- Sheets(nombre).Activate ó Sheets(n).Activate
- WorkSheets(nombre).Activate
Para hacer uso de una celda de una hoja use una de las siguientes
formas:
- Range(celda).Select
- Cells(NroFila,NroColumna).Select
Donde celda, si es nombre de celda, va encerrada entre comillas; si
es el nombre de una variable, debe ser una variable de tipo Variant o
String.
Ejemplos
Range(“A5”).Select Activa la celda A5
Dato = Range(“B8”) Deja en Dato el contenido de B8
Ejemplo 3
Si está en Excel vaya a la ventana del Editor del Visual Basic.
Ubíquese al final del módulo (antes de End Sub).
Digite lo siguiente:
' Vamos a activar la hoja llamada Hoja3
Sheets("Hoja3").Select
Range("A5") = Nombre
Range("B5") = Apel
Range("C5") = Edad
Ahora vuelva a ejecutar el módulo y cuando termine, vaya el Excel y
observe el contenido de la tercera hoja, Hoja3.
Y cómo podemos pasar datos de una hoja a otra?
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 168 de 174
Solución
El procedimiento para responder a esta pregunta consiste en lo
siguiente:
Seleccionar la hoja donde están los datos a ser pasados
Asignar los datos de las celdas respectivas a variables
Seleccionar la destino
Asignar el contenido de las variables hacia las celdas donde las
queremos asignar.
Vuelva al Editor.
Ubíquese al final del módulo (antes de End Sub)
Digite las siguientes líneas
' Pasemos datos de una hoja a otra
Dato1 = Range("A5")
Dato2 = Range("B5")
Dato3 = Range("C5")
Sheets("Trabajo").Select
Cells(1, 5) = Dato1
Cells(1, 6) = Dato2
Cells(1, 7) = Dato3
Vuelva a ejecutar el módulo
Haciendo clic en las dos hojas (Hoja3 y Trabajo) observe lo que ha
ocurrido.
Ejemplo 4
Cómo podemos ingresar un conjunto de 10 valores aleatorios
hacia 10 celdas de una hoja?
Para responder a la pregunta debemos saber primero ejecutar una o
más sentencias repetidamente.
Para ello introducimos las siguientes sentencias:
For Indice = ValInicial TO ValFinal [STEP Incremento]
……..
Next Indice
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 169 de 174
Permite ejecutar todas las instrucciones contenidas en el alcance del
For --- Next, variando de uno en uno a una variable de control
(llamado aqui Indice) desde un valor inicial ValInicial hasta el último
valor , ValFinal; cuando termina continua con la siguiente instrucción
después de Next
While Condicion
.........
Wend
donde Condicion establece una comparación entre una variable de
control y una constante o expresión. Mientras la condición que se
compara es verdadera, se ejecutan todas las instrucciones dentro de
While – Wend; en el momento que ya no se cumple, el control pasa a
la siguiente instrucción después de Wend.
Ejemplos a
Suma = 0
For I = 1 To 10
Suma = Suma + i
Next
Ejemplo b
J=1
Product = 1
While J < 11
Suma = Suma*J
WEnd
Sheets(2).Select
Cells(5,2) = Product
El primer ejemplo obtiene en Suma, la suma de los 10 primeros
números 1, 2, ..., 10.
El segundo ejemplo obtiene el producto de los 10 primeros números
y después lo deposita en la celda B5, de la hoja2.
Ejemplo
Vamos a ingresar 10 números aleatorios hacia las celdas C5 hasta
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 170 de 174
C14 de la hoja Trabajo.
Vaya al Editor del Visual Basic.
Inserte nuevo módulo
Ingrese: Sub Iterar y presione <Enter>
Ingrese ahora las siguientes líneas:
Sheets(“Trabajo”).Select
For I = 5 To 14
Cells(I, 3) = “=Rnd”
I = I + 1
Next
Explicación:
Empieza con I = 1; Celda (I, 3) es la celda C5. En esta celda deja un
número aleatorio entre 0 y 1.
Ahora vuelve e I =2; en C (6, 3); es decir, en C6 deja otro valor
Ejecute el módulo y observe la hoja Trabajo.
Ejemplo 5
Vamos a ingresar las ventas de un cliente y calcular el monto de su
compra, aplicar el impuesto IGV(19%). Primero lo haremos para un
solo producto.
Procedimiento:
Seleccionar la hoja
Ingresar la cabecera de las columnas en la fila 2: Producto, Cantidad,
PrUnitario, Monto, Impuesto, Neto a Pagar.
Se pedirá con InputBox todos los datos y se depositarán en la fila 3.
Se calculará el Monto, el IGV y el Neto a Pagar.
Fin de proceso
Nota:
Necesitamos una variable numérica entera (Integer) para la cantidad
y otra de tipo real (Double) para el precio. También necesitamos para
el monto del impuesto y para el Neto a Pagar; ambas de tipo Double.
Esto implica que debemos declararlos con DIM al comienzo.
Todas las
fórmulas se
ingresan entre
comillas y
claro,
precedidas
por el signo
igual.
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 171 de 174
Para este módulo vamos a usar la misma carpeta anterior. Para ello,
después de dejar una o más líneas por debajo del último End Sub
que tenga, digite:
Sub Ventas y presione <Enter>
Ingrese el siguiente código:
Dim Cantidad As Integer
Dim PreUnit, Monto, Impuesto, PegoNeto As Double
Dim Produc As String
' Seleccionar el libro
Sheets("Trabajo").Select
' Ponemos los títulos en la fila 2
Range("A2") = "Producto"
Range("B2") = "Cantidad"
Cells(2, 3) = "PreUnit"
Cells(2, 4) = "Monto"
Cells(2, 5) = "Igv (19%)"
Cells(2, 6) = "Pago Neto"
' Ahora vamos a ingresar datos usando InputBox
' y depositando directamente en la celda
Cells(3, 1) = InputBox("Nombre del producto")
Cells(3, 2) = Val(InputBox("Cantidad"))
Cells(3, 3) = Val(InputBox("Cual es el precio unitario ?"))
' Cálculo del Monto: Precio * Cantidad
Cells(3, 4) = Cells(3, 2) * Cells(3, 3)
' Cálculo del IGV
Cells(3, 5) = 0.19 * Cells(3, 4)
' Cálculo de Pago Neto
Cells(3, 6) = Cells(3, 4) + Cells(3, 5)
Ahora ejecute este módulo.
Ejemplo 6
Asignación de un botón para este módulo.
Solución
Como puede haber notado, nuestros módulos sólo pueden ser
ejecutados desde el Editor del Visual Basic.
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 172 de 174
Usando la barra de herramientas de Formulario vamos a colocar un
botón de comando (Botón) en una parte de la hoja en donde
queremos los resultados del módulo.
Estado en la hoja Trabajo, active la barra de herramientas de
Formularios (<Ver> - <Barras de herramientas> - <Formularios>
Haga clic en el botón Botón
Despliegue un recuadro entre las celdas G3 y H3.
En la ventana que se emita, seleccione el nombre del módulo (aquí
asume que este es una macro) y luego haga clic en <Aceptar>.
Ahora haga clic sobre el botón (debe estar seleccionado) para
cambiar de nombre. Digite <Ventas>.
Haga clic en otro sitio o presione <Esc>.
Haga clic en el botón (previamente limpie las celdas donde trabaja el
módulo).
Ejemplo Final
Inserte las instrucciones necesarias para que el módulo Ventas
permita ingresar varias ventas hasta que se decida que ya no hay
más ventas y luego calcule el monto total del Pago Neto.
Solución
No podemos usar la instrucción For ... Next ya que éste ejecuta un
número de veces determinado.
Usaremos el While ... WEnd.
La variable de control será Ok (variable de tipo carácter, Char).
Después de ingresar el precio del producto, deberá preguntar si
desea continuar. Se digitará “S” o “s” para seguir. Esto se depositará
en la variable Ok. Si el contenido de Ok no es “S” o “s”, dará por
terminado el bucle y pasará a emitir el Monto Total del Neto a Pagar.
Vaya al Editor del Visual Basic
Modifique el contenido del módulo tomando en cuenta el siguiente
listado.
Dim Ok As String
Dim TotalNeto As Double
Dim Cantidad As Integer
Dim PreUnit, Monto, Impuesto, PegoNeto As Double
Dim Produc As String
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 173 de 174
Dim NLineas As Integer
' Seleccionar el libro
Sheets("Trabajo").Select
' Ponemos los títulos en la fila 2
Range("A2") = "Producto"
Range("B2") = "Cantidad"
Cells(2, 3) = "PreUnit"
Cells(2, 4) = "Monto"
Cells(2, 5) = "Igv (19%)"
Cells(2, 6) = "Pago Neto"
' Ahora vamos a ingresar datos usando InputBox
' y depositando directamente en la celda
TotalNeto = 0
NLineas = 3
Ok = "S"
While UCase(Ok) = "S"
Cells(NLineas, 1) = InputBox("Nombre del producto")
Cells(NLineas, 2) = Val(InputBox("Cantidad"))
Cells(NLineas, 3) = Val(InputBox("Cual es el precio unitario ?"))
' Cálculo del Monto: Precio * Cantidad
Cells(NLineas, 4) = Cells(NLineas, 2) * Cells(NLineas, 3)
' Cálculo del IGV
Cells(NLineas, 5) = 0.19 * Cells(NLineas, 4)
' Cálculo de Pago Neto
Cells(NLineas, 6) = Cells(NLineas, 4) + Cells(NLineas, 5)
TotalNeto = TotalNeto + Cells(NLineas, 6)
NLineas = NLineas + 1
Ok = InputBox("Desea continuar? S/N")
Wend
Cells(NLineas + 2, 6) = TotalNeto
Ahora vaya a Excel y ejecute el módulo haciendo clic en el botón
Ventas.
Nota:
Puesto que para cada prueba debe seleccionar el rango A1:F6,
introduzca la siguiente línea para limpiar el contenido.
MANUAL DE EXCEL AVANZADO ILMER CÓNDOR
Página 174 de 174
Después de Sheets(“Trabajo”).Select
Ingrese la línea:
Range("A1:F20").ClearContents
Ahora ejecute sin borrar la prueba anterior.
3.2. EJERCICIOS
EJERCICIO 1
Escriba un módulo que le permita obtener una tabla de valores como
se indica en el siguiente cuadro.
A B C D
Cuadrado de N Cubo de N Raíz cuadrada Logaritmo(10)
1
2
3
EJERCICIO 2
Abra el archivo Formularios.xls y Pro forma de pago.xls
Asigne un botón a la macro que almacena los datos en la Hoja
Resumen
Asigne un nuevo botón a la macro que trabaja con la hoja
Proformas.