103589385 manual de excel avanzado

72
Ing. Noel Antonio Pavón L. 1 UNIVERSIDAD NACIONAL DE INGENIERÍA Programa de Atención Empresarial

Upload: wuimedia

Post on 06-Aug-2015

129 views

Category:

Documents


6 download

TRANSCRIPT

Page 1: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

1

UNIVERSIDAD NACIONAL DE

INGENIERÍA

Programa de Atención Empresarial

Page 2: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

2

Curso Avanzado de Microsoft Excel 2010

Docente: Ing. Noel Antonio Pavón López

Email: [email protected]

[email protected]

Managua, Febrero 2011.

Page 3: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

3

Introducción:

Microsoft Office Excel, más conocido como Microsoft Excel, es una aplicación para manejar

hojas de cálculo. Este programa es desarrollado y distribuido por Microsoft, y es utilizado

normalmente en tareas financieras y contables.

Excel es un programa del tipo Hoja de Cálculo que permite realizar operaciones con

números organizados en una cuadrícula. Es útil para realizar desde simples sumas hasta

cálculos de préstamos hipotecarios.

Una hoja de cálculo es un programa que es capaz de trabajar con números de forma sencilla

e intuitiva. Para ello se utiliza una cuadrícula donde en cada celda de la cuadrícula se pueden

introducir números, letras y gráficos.

Por ejemplo, para sumar una serie de números sólo tienes que introducirlos uno debajo de

otro, como harías en un papel, colocarte en la celda donde irá el resultado y decirle a Excel

que quieres hacer la suma de lo que tienes encima (ya veremos más adelante cómo se hace

exactamente, pero es muy fácil).

Quizás pienses que para hacer una suma es mejor utilizar una calculadora. Pero piensa qué

ocurre si te equivocas al introducir un número en una suma de 20 números, tienes que

volver a introducirlos todos; mientras que en Excel no importa si te equivocas al introducir

un dato, simplemente corriges el dato y automáticamente Excel vuelve a calcularlo todo. Esto

es importante cuando los cálculos son un poco más complicados,

imagina que estás haciendo la declaración de la renta a mano y al final descubres un error,

tendrías que volver a calcularlo todo. Si lo haces con Excel sólo tienes que corregir un dato.

Esta característica de recálculo automático te permite también hacer simulaciones

fácilmente. Por ejemplo, si estás calculando lo que tendrás que pagar al mes al pedir un

préstamo hipotecario, basta que vayas introduciendo diferentes cantidades en el importe del

préstamo para que veas lo que tendrías que pagar en cada caso.

Page 4: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

4

Vamos a ver otro ejemplo que nos servirá para ver más características de Excel.

En esta imagen tienes una sencilla factura realizada con Excel. Puedes observar como las

columnas se numeran por letras A,B,C,... y las filas por números 1,2,3,... En la columna D se

ha calculado el producto de las columnas B y C. En la celda D12 se ha calculado el IVA,

debajo de la barra de herramientas puedes ver la fórmula que se ha utilizado =D11*0,16 es

decir, el producto de lo que hay en la celda D11 multiplicado por 0,16.

También puedes ver en este ejemplo cómo se puede utilizar texto en cualquier parte de la

hoja de cálculo, incluso podríamos haber puesto un gráfico con el logotipo de la ferretería.

Otra cosa buena de Excel es que no es necesario saber matemáticas para utilizarlo. En

muchas ocasiones es suficiente con utilizar las operaciones básicas. Por supuesto, si sabes

matemáticas mucho más partido podrás sacar de Excel.

Aunque en este ejemplo no se ve, Excel también es capaz de dibujar gráficos a partir de los

datos introducidos, del estilo de los gráficos en forma de tarta y en forma de barras que se

ven en las encuestas. Excel se puede utilizar para multitud de cosas, tanto en el plano

personal como en el plano profesional. Desde llevar las cuentas familiares hasta los

más complejos cálculos financiero.

Conceptos de Excel

Aquí podrás encontrar los conceptos más básicos que utiliza Excel. Para entender mejor

cada uno de los conceptos explicados te aconsejamos abrir otra ventana con Excel y

comprobarlo.

Page 5: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

5

Libro de trabajo

Un libro de trabajo es el archivo que creamos con Excel, es decir, todo lo que hacemos en

este programa se almacenará formando el libro de trabajo. Los libros de trabajo de Excel

tienen la extensión .XLS para que el ordenador los reconozca como tal.

Cuando se inicia una sesión de Excel automáticamente se abre un nuevo libro de trabajo con

el nombre provisional de Libro1. Esto lo puedes comprobar en la pantalla de Excel, en la

Barra de título en la parte superior de la ventana verás como pone Microsoft Excel - Libro1.

Cada vez que empezamos un nuevo trabajo con Excel el número del libro irá variando

dependiendo de cuántos se hayan creado en esta sesión. Así si empezamos otro trabajo, el

nombre que se asigna será Libro2, el siguiente Libro3, y así sucesivamente.

Cuidado que el nombre asignado sólo sirve como referencia para identificar los trabajos

mientras no se hayan guardado, en ningún caso significa que el archivo ya se encuentra

guardado.

Un libro de trabajo está formado por varias hojas, en principio constará de 3 hojas aunque el

número de éstas puede variar entre 1 y 255, Si miras en la parte inferior de la ventana de

Excel encontrarás las diferentes hojas del libro de trabajo, cada una de ellas nombradas de la

forma Hoja1, Hoja2...

Los libros de trabajo son una gran herramienta de organización, ya que por ejemplo todas las

hojas referidas a un mismo proyecto o trabajo podrían agruparse en un sólo libro.

Hoja de cálculo

La hoja de cálculo es uno de los distintos tipos de hojas que puede contener un libro de

trabajo. Es una herramienta muy útil para todas aquellas personas que trabajen con gran

cantidad de números y necesiten realizar cálculos u operaciones con ellos.

Es como una gran hoja cuadriculada formada por 256 columnas y 65.536 filas.

Las hojas de cálculo están formadas por columnas y filas.

Una columna es el conjunto de celdas seleccionadas verticalmente. Cada columna se nombra

por letras, por ejemplo A, B, C,.......AA, AB,........IV.

Page 6: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

6

Cada fila se numera desde 1 hasta 65536 y es la selección horizontal de un conjunto de

celdas de una hoja de datos.

La intersección de una columna y una fila se denomina Celda y se nombra con el nombre de

la columna a la que pertenece y a continuación el número de su fila, por ejemplo la primera

celda pertenece a la columna A y la fila 1 por lo tanto la celda se llama A1. Si observas la

ventana de Excel podrás comprobar todo lo explicado anteriormente.

Cuando el cursor está posicionado en alguna celda preparado para trabajar con ésta, dicha

celda se denomina Celda activa y se identifica porque aparece más remarcada que las demás.

De igual forma tenemos la fila activa, fila donde se encuentra la celda activa y columna activa,

columna de la celda activa.

Otro concepto muy importante en una hoja de cálculo es el de Rango, que es un bloque

rectangular de una o más celdas que Excel trata como una unidad. Los rangos son vitales en

la Hoja de Cálculo, ya que todo tipo de operaciones se realizan a base de rangos. Más

adelante veremos las distintas formas de definir un rango.

Page 7: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

7

Función

Una función es una fórmula predefinida por Excel que opera sobre uno o más valores

(argumentos) en un orden determinado (estructura). El resultado se mostrará en la celda

donde se introdujo la formula.

El tipo de argumento que utiliza una función es específico de esa función. Así, los

argumentos pueden ser números, texto, valores lógicos como VERDADERO o FALSO,

matrices, valores de error como #N/A o referencias de celda. Un argumento puede ser una

constante, una fórmula o incluso otra función.

Excel cuenta con una gran variedad de funciones dependiendo del tipo de operación o

cálculo que realizan. Estas funciones pueden ser matemáticas y trigonométricas, estadísticas,

financieras, de texto, de fecha y hora, lógicas, de base de datos, de búsqueda y referencia y de

información.

Barra de Función

Estructura de una función

La sintaxis de cualquier función es:

=nombre_funcion(argumento1;argumento2;…;argumentoN)

Esto es:

1. Signo igual (=).

2. Nombre de la función.

3. Paréntesis de apertura.

4. Argumentos de la función separados por puntos y comas.

5. Paréntesis de cierre.

Insertar función

Cuando cree una fórmula que contenga una función, el cuadro de diálogo Insertar función le

ayudará a introducir las funciones de la hoja de cálculo. A medida que se introduzca una

función en la fórmula, el cuadro de diálogo Insertar función irá mostrando el nombre de la

función, cada uno de sus argumentos, una descripción de la función y de cada argumento, el

resultado actual de la función y el resultado actual de toda la fórmula.

Page 8: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

8

Para introducir una fórmula, cuenta con dos opciones:

1. Puede escribir la formula directamente en la celda que mostrará el resultado. Si el

nombre de la formula es correcto Excel indicará los argumentos de la misma.

2. Puede utilizar la ayuda de Excel (cuadro de diálogo Insertar función), este asistente

mostrará el nombre de la función, los argumentos, ademas una descripción de la

función y de cada argumento.

Funciones Anidadas

Excel permite el utilizar funciones como uno de los argumentos de otra función, esto se

conoce como funciones anidadad. Por ejemplo:

=SI(PROMEDIO(A1:A10)>50;SUMA(B1:B10);0)

La siguiente función SI compara con 50 el resultado producido por la función PROMEDIO

(función anidada), si es mayor a 50, invoca a la función SUMA (función anidada).

Resultados válidos

Cuando se utiliza una función anidada como argumento, ésta deberá devolver el mismo tipo

de valor que el que utilice el argumento.

Límites del nivel de anidamiento

Una fórmula puede contener como máximo siete niveles de funciones anidadas.

Fórmula

Las fórmulas en Excel son expresiones que se utilizan para realizar cálculos o procesamiento

de valores, produciendo un nuevo valor que será asignado a la celda en la cual se introduce

dicha fórmula. En una fórmula, por lo general, intervienen valores que se encuentran en

una o más celdas de un libro de trabajo. Las fórmulas están conformadas por operadores de

cálculo, operandos y, con frecuencia, por funciones. Para introducir una fórmula en una

celda, se debe entrar como primer carácter el signo igual ( El signo igual = le indica a Excel

que los caracteres que le siguen constituyen una fórmula ). Cuando se escribe la fórmula no

se deben dejar espacios en blanco dentro de la misma.

Por ejemplo, la fórmula =A5+A4*3 expresa que se multiplique el valor que contiene

la celda A4 por el valor constante 3 y, a continuación, se le sume el valor que contiene la

celda A5 al anterior resultado ( como se verá mas adelante la multiplicación * tiene mayor

prioridad que la suma + ). Si la celda A5 contiene el valor numérico 15, la celda A4

contiene el valor numérico 4, entonces, esta fórmula al ser calculado por el

Excel producirá 27 como resultado final, el cual será asignado a la celda en la que se

ingresó la fórmula.

Page 9: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

9

Ejemplo de una fórmula en la barra de funciones

Componentes de una fórmula

Una fórmula en Excel puede contener cualesquiera de los siguientes elementos: referencias a

celdas, constantes, operadores y funciones.

Por ejemplo, la fórmula que permite calcular el área de la superficie que está dentro de una

circunferencia, A = p R2

, se puede escribir en Excel como:

=PI()*B1^2

En esta fórmula podemos identificar los elementos nombrados anteriormente:

- Referencias a celdas: B1. Se hace referencia al valor que contiene la celda B1.

- Constantes: el valor 2. Números o valores escritos directamente en una fórmula.

- Operadores: ^ y *. El operador ^ (acento circunflejo) eleva un número a una potencia, y

el operador * (asterisco) multiplica.

- Funciones: la función PI(). Esta función al ser invocada devuelve el valor de pi:

3,141592...

Algoritmo:

En matemáticas, ciencias de la computación y disciplinas relacionadas, un algoritmo es un

conjunto preescrito de instrucciones o reglas bien definidas, ordenadas y finitas que permite

realizar una actividad mediante pasos sucesivos que no generen dudas a quien lo ejecute.

Dados un estado inicial y una entrada, siguiendo los pasos sucesivos se llega a un estado final

y se obtiene una solución.

Pseudocódigo

Pseudocódigo es la descripción de un algoritmo que asemeja a un lenguaje de programación

pero con algunas convenciones del lenguaje natural (de ahí que tenga el prefijo pseudo, que

significa falso). El pseudocódigo no está regido por ningún estándar.

Ejemplo:

Si sqr(b)>= 4*a*c entonces

x=(-b+sqrt(b^2-4*a*c))/2a

Sino

Page 10: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

10

Escribir "No existen raíces reales."

Fin si

Comenzando a Trabajar con Excel.

Funciones Lógicas.

Función SI

Devuelve un valor si la condición especificada es VERDADERO y otro valor si dicho

argumento es FALSO.

Utilice SI para realizar pruebas condicionales en valores y fórmulas

Sintaxis

SI(prueba_lógica;valor_si_verdadero;valor_si_falso)

Prueba_lógica es cualquier valor o expresión que pueda evaluarse como VERDADERO o

FALSO. Por ejemplo, A10=100 es una expresión lógica; si el valor de la celda A10 es igual a

100, la expresión se evalúa como VERDADERO. De lo contrario, se evaluará como

FALSO. Este argumento puede utilizar cualquier operador de comparación.

Valor_si_verdadero es el valor que se devuelve si el argumento prueba_lógica es

VERDADERO. Por ejemplo, si este argumento es la cadena de texto "Dentro de

presupuesto" y el argumento prueba_lógica se evalúa como VERDADERO, la función SI

muestra el texto "Dentro de presupuesto". Si el argumento prueba_lógica es VERDADERO

y el argumento valor_si_verdadero está en blanco, este argumento devuelve 0 (cero). Para

mostrar la palabra VERDADERO, utilice el valor lógico VERDADERO para este

argumento. Valor_si_verdadero puede ser otra fórmula.

Valor_si_falso es el valor que se devuelve si el argumento prueba_lógica es FALSO. Por

ejemplo, si este argumento es la cadena de texto "Presupuesto excedido" y el argumento

prueba_lógica se evalúa como FALSO, la función SI muestra el texto "Presupuesto

excedido". Si el argumento prueba_lógica es FALSO y se omite valor_si_falso, (es decir,

después de valor_si_verdadero no hay ninguna coma), se devuelve el valor lógico FALSO. Si

prueba_lógica es FALSO y valor_si_falso está en blanco (es decir, después de

Page 11: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

11

valor_si_verdadero hay una coma seguida por el paréntesis de cierre), se devuelve el valor 0

(cero). Valor_si_falso puede ser otra fórmula.

Ejemplo:

Para dar inicio al trabajo con la función SI, se hará uso del archivo: 1.- Funciones Lógicas,

comenzando por el libro “Estado Profesor”

El enunciado del ejercicio cita de la siguiente manera:

Utilizando la función SI, completar las columnas Estado y Profesor de acuerdo con la

siguiente información:

1) Columna Estado: Las personas que cumplen con el condicional C, se encuentran en

estado ACTIVO y los que se encuentren en condicional R su estado es inactivo.

2) Columna Profesor: Catalogar el profesor en A si el Nro. Insc. es menor o igual a 20

y B para las demás personas.

Inicialmente deberá para efectos prácticos crear el Algoritmo.

Creando el Algoritmo:

Columna Estado:

SI Condicional = C

Entonces

Estado = Activo

Si no

Estado = Inactivo

Fin SI

Para poder indicarle a Excel lo que deseamos que haga deberemos ingresar la fórmula en la

barra de funciones:

Observación:

Page 12: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

12

Como podrá darse cuenta el programa evalúa únicamente cuando la letra almacenada en la

celda D10 es igual a “C” pondrá la palabra “Activo”, para cualquier otro caso escribirá la

palabra inactivo, no tomando en cuenta que solamente existen dos tipos de estado C y R,

¿qué pasa si por error se ingresa en la columna estado otro carácter que no sean C ó R?.

Haga las correcciones o validaciones necesarias para evitar esto de tal manera que cuando se

ingrese un carácter distinto a “C” ó “R” despliegue un mensaje de error: “Estado Inválido”.

Columna Profesor: Catalogar el profesor en A si el Nro. Insc.(número de inscritos) es menor

o igual a 20 y B para las demás personas.

Escribiendo el Algoritmo:

SI Inscritos < ó = 20

Entonces

Profesor= “A”

Si NO

Profesor = “B”

Fin SI

Pasando a Excel el algoritmo:

Observación:

Haga las correcciones necesarias para que el programa no acepte valores negativos.

Trabajando con el libro: “Categoría Trabajo por Horas”

Completar la columna Trabajo por Horas mediante la siguiente clasificación, utilizando la

función SI y relacionando la fórmula mediante celdas absolutas y relativas.

Categoría Trabajo por horas

1 8 hs.

2 10 hs.

Page 13: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

13

Conceptos Importantes

Cuando en Excel se introduce una fórmula que hace referencia a otra u otras celdas, hay

varias formas de establecer dichas referencias.

Una referencia relativa indica la posición relativa de una celda con respecto a la que contiene

la fórmula y cuando se copia en otra posición, modifica y actualiza las posiciones. Es una

referencia posicional o relativa, por ejemplo, C4. Una referencia absoluta es una

introducción explícita y única de otra celda en un cálculo, no de su posición relativa.

En la hoja de cálculo se trabaja normalmente con referencias relativas. Es el tipo de

referencia que emplea Excel por defecto, si no se indica nada que lo modifique. Para trabajar

con referencias absolutas se debe especificar escribiendo el signo $ delante de la letra de la

columna y del número de fila. Por ejemplo $A$3 se refiere exclusivamente a la celda A3 y, al

aplicar llenados -hacia abajo o hacia la derecha-, u operaciones de copiar y pegar, las

referencias que tengan el signo $ delante no serán actualizadas. Si se supone que la celda B8

contiene la fórmula: =B7, al copiar la fórmula en la celda C8, esta será: =C7. Para copiar B8

en C8, debe seleccionarse la primera y elegir Edición|Copiar, seleccionar a continuación C8

y elegir Edición|Pegar. Es equivalente a llenar hacia la derecha desde B8 hasta C8 Con este

ejemplo, se puede comprobar la actualización de referencias en su caso más simple. Si, por

el contrario, la celda B8 contiene la fórmula: =$B$7, al copiar la fórmula en la celda C8, esta

será: =$B$7: las referencias absolutas no se han actualizado.

En la figura siguiente, si se copia la fórmula de la celda B8 en C8, Excel copia las referencias

que B8 hace, de forma relativa, a otras celdas, y las transforma para que la fórmula de C8

calcule el total de la Extensión. Si B8 era el cálculo de la suma de las celdas que tiene

encima, C8 es lo mismo, pero para su columna.

Una vez entendido esto procedamos a elaborar el algoritmo del ejercicio.

SI Categoría = 1

Entonces

Trabajo por Horas = 8 Horas

Si NO

Trabajo por Horas = 10 Horas

Fin Si

Ingresando en la barra de fórmulas el algoritmo:

Page 14: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

14

Tome NOTA:

¿Qué sucede si se ingresa un valor distinto a 1 ó 2 en la columna Categoría?, haga las

correcciones necesarias para evitar esto.

Conectivos Lógicos: “Y” – “O”

Función “Y”

Descripción

Devuelve VERDADERO si todos los argumentos se evalúan como VERDADERO;

devuelve FALSO si uno o más argumentos se evalúan como FALSO.

Un uso común de la función Y es expandir la utilidad de otras funciones que realizan

pruebas lógicas. Por ejemplo, la función SI realiza una prueba lógica y, luego, devuelve un

valor si la prueba se evalúa como VERDADERO y otro valor si la prueba se evalúa como

FALSO. Con la función Y como argumento prueba_lógica de la función SI, puede probar

varias condiciones diferentes en lugar de sólo una.

Sintaxis

Y(valor_lógico1; [valor_lógico2]; ...)

La sintaxis de la función Y tiene los siguientes argumentos (argumento: valor que

proporciona información a una acción, un evento, un método, una propiedad, una función o

un procedimiento.):

valor_lógico1 Obligatorio. La primera condición que desea probar se puede

evaluar como VERDADERO o FALSO.

valor_lógico2, ... Opcional. Las condiciones adicionales que desea probar se

pueden evaluar como VERDADERO o FALSO, hasta un máximo de 255

condiciones.

Page 15: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

15

Función “O”

Devolverá VERDADERO si alguno de los argumentos es VERDADERO; devolverá

FALSO si todos los argumentos son FALSO.

Sintaxis

O(valor_lógico1;valor_lógico2; ...)

Valor_lógico1, Valor_lógico2, ... son de 1 a 255 condiciones que se desea comprobar y

que pueden tener el resultado de VERDADERO o FALSO.

Observaciones

Los argumentos deben evaluarse como valores lógicos, como VERDADERO O

FALSO, o bien en matrices (matriz: utilizada para crear fórmulas sencillas que

producen varios resultados o que funcionan en un grupo de argumentos que se

organizan en filas y columnas. Un rango de matriz comparte una fórmula común;

una constante de matriz es un grupo de constantes utilizadas como un argumento.) o

referencias que contengan valores lógicos.

Si un argumento matricial o de referencia contiene texto o celdas vacías, dichos

valores se pasarán por alto.

Si el rango especificado no contiene valores lógicos, la función O devuelve el valor

de error #¡VALOR!.

Para poder entender mejor cómo trabajan las funciones “Y” ,“O” procedamos a abrir el

libro: “Conectivos "Y"-"O" ”

El enunciado es el siguiente:

COMPLETAR LAS COLUMNAS CON LA SIGUIENTE INFORMACIÓN:

1) Función O: determinar las personas que pertenecen al turno N o que son del sexo F

2) Función Y: determinar las personas que pertenecen al turno N y que son del sexo F

3) Función O: determinar las personas que pertenecen al turno T o que son del sexo M

o cuya condición sea C

Page 16: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

16

4) Función Y: determinar las personas que pertenecen al turno N y que son del sexo M

y cuya condición sea R

Para el primer caso la solución sería:

El segundo Caso:

El tercer caso:

El cuarto caso:

Importante:

Una vez comprendido el uso y funcionamiento de estas dos funciones podrá hacer uso de

ellas para validar mejor los ejemplos del condicional SI (Libros: “Estado Profesor” y

“Categoría Trabajo por Horas”).

Funciones anidadas dentro de funciones

En algunos casos, puede que deba utilizar una función como uno de los argumentos

(argumento: valores que utiliza una función para llevar a cabo operaciones o cálculos. El tipo

de argumento que utiliza una función es específico de esa función. Los argumentos más

comunes que se utilizan en las funciones son números, texto, referencias de celda y

nombres.) de otra función. Por ejemplo, la siguiente fórmula utiliza una función anidada

PROMEDIO y compara el resultado con el valor 50.

Page 17: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

17

Resultados válidos Cuando se utiliza una función anidada como argumento, deberá

devolver el mismo tipo de valor que el que utilice el argumento. Por ejemplo, si el argumento

devuelve un valor VERDADERO o FALSO, la función anidada deberá devolver

VERDADERO o FALSO. Si éste no es el caso, Microsoft Excel mostrará el valor de error

#¡VALOR!

Límites del nivel de anidamiento

Una fórmula puede contener como máximo siete niveles de funciones anidadas. Si la

Función B se utiliza como argumento de la Función A, la Función B es una función de

segundo nivel. Por ejemplo, la función PROMEDIO y la función SUMA son ambas

funciones de segundo nivel porque son argumentos de la función SI. Una función anidada

dentro de la función PROMEDIO será una función de tercer nivel, etc.

Trabajando con condicionales anidados.

Abra el archivo: “Funciones lógicas Anidadas” iniciaremos con el libro: “SI.O”

El enunciado dice lo siguiente:

Completar la columna "Ingresa a nueva sección" de acuerdo a la información de la tabla

Poner en la columna SI cuando la persona pertenezca a la categoría 2 o que su sueldo sea

mayor a 800, de lo contrario se pone NO.

Ingresa a nueva sección Condición

SI Categoría 2

SI

Sueldo mayor a

800000

NO Otros

Construyendo el algoritmo:

SI Categoría = 2 ó Sueldo > 800000

Entonces

Ingresa Nueva Sección= “SI”

Si NO

Ingresa Nueva Sección= “NO”

Fin SI

Pasando a fórmula el algoritmo:

Page 18: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

18

Libro: “SI.Y”

Una compañía desea realizar una selección, en donde eligen al personal inicialmente de

acuerdo al criterio de edad. Por tanto, en la columna de elección debe aparecer Elegido/a,

cuando la persona esté en un rango de edad entre los 22 y 45 años de no cumplir con ello, se

debe poner en la columna Rechazado/a.

Criterio de elección

Elegido/a Entre 22 y 45

Rechazado/a Menor de 21 y mayor

de 46

Construyendo el Algoritmo:

Si Edad > ó = 22 y Edad < ó =45

Entonces:

Elección = “Elegido/a”

Si No

Elección = “Rechazado/a”

Fin SI

Escribiendo la Fórmula en Excel:

Libro:” SI-SI”

Completar la columna"Total horas", de acuerdo a la información de la tabla 1 y se debe hacer

referencias de celdas. Allí se consigna el total de horas de acuerdo a la categoría que tenga la

persona.

Tabla 1

Categoría Total horas

1 8 hs.

2 10 hs.

3 7 hs.

Page 19: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

19

Construyendo el Algoritmo:

Si Categoría = 1

Entonces

Total Horas = 8 hrs

Si No

Si Categoría =2

Entonces

Total Horas = 10 hrs

Si No

Total Horas = 7 hrs

Fin SI

Fin SI

Escribiendo el algoritmo en lenguaje de Excel:

Tarea:

Haga las modificaciones necesarias para que Excel valide cuando se ingresa un valor que no

esté contemplado en los criterios a evaluar, esto para cada uno de los libros del archivo.

Page 20: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

20

Trabajando con el Archivo: “3.- Funciones Lógicas Anidadas 2.”

Libro: “Variado”

Resolución de la columna Pago:

Completar la columna pago de acuerdo a los siguientes argumentos, ya que el precio se considera

de acuerdo al Instrumento:

Instrumentos Precios a

abonar

Violín Guitarra $ 70

Piano Contrabajo $ 45

El resto $ 50

NOTA: Recuerde que si aún se le dificulta realizar de manera directa el ejercicio, puede comenzar

por elaborar el Algoritmo. Dicho algoritmo será el siguiente:

Si Instrumento = Violín ó Instrumento = Guitarra

Entonces

Pago = 70

Si No

Si Instrumento = Piano ó Instrumento = Contrabajo

Entonces

Pago = 45

Si no

Pago = 50

Fin Si

Fin Si

Recomendación Adicional: Haga Uso de Referencia a Celdas para evitar estar escribiendo entre

comillas las cadenas de caracteres, lo evitará que la fórmula se haga más extensa.

La respuesta del ejercicio en la barra de funciones de Excel será la siguiente:

Page 21: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

21

Solución Columna Descuento.

Completar la columna descuento, de acuerdo al instrumento y antigüedad del alumno en la

institución

Instrumento Antigüedad Descuento

Guitarra mayor o igual a 12 y menor o igual a 20 años 10%

Piano menor o igual a 11 años 20%

Resto 0%

Rellenar la columna Antigüedad de manera aleatoria con valores entre 1 a 25, hágalo a su criterio

puede usar la siguiente fórmula para evitar estar escribiendo datos:

Para el primer dato de dicha columna digite el valor de 1, luego en el siguiente registro (F22)

escriba: =F22+1, y de igual manera para el resto.

Luego procedemos a trabajar en la solución de la columna Descuento.

Page 22: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

22

El algoritmo es:

Si Instrumento = Guitarra y Antigüedad >=12 y Antigüedad <=20

Entonces

Descuento = Pago * 10%

Si no

Si Instrumento = Piano y Antigüedad <= 11

Entonces

Descuento = Pago *20%

Si no

Descuento = 0

Fin SI

Fin SI

Recomendación Adicional: Haga Uso de Referencia a Celdas para evitar estar escribiendo entre

comillas las cadenas de caracteres (esto en los casos que sea posible), lo evitará que la fórmula se

haga más extensa.

La respuesta del ejercicio en la barra de funciones de Excel será la siguiente:

Page 23: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

23

Columna Pago Total

El pago total es restar del pago el descuento generado.

La respuesta es sencilla:

Aplicando Formato Condicional

Descripción de Formato Condicional:

Con el formato condicional puedes hacer que sus datos numéricos ofrezcan información gráfica

adicional. Si es necesario ofrecer una mejor visualización de tus datos numéricos en tus hojas de

cálculo sin tener que recurrir a los gráficos, Excel 2007 ofrece un gran número de posibilidades en

esta área.

Desde el botón “Formato Condicional” del “Grupo Estilo” correspondiente a la “Ficha Inicio” tiene

disponible usa serie de posibilidades para ofrecer una visualización gráfica de los valores números

en las propias celdas.

Modo de emplearlo:

Seleccione un rango de celdas, y dentro del menú desplegable del botón “Formato Condicional” seleccione la opción “Barras de Datos” y dentro de ella el color que quiere utilizar. Como vera su rango de celdas ofrece la misma información que antes pero gracias a esta opción del Formato Condicional es mucho más sencillo identificar la información numérica global.

Seleccione otro rango de celdas, y dentro del menú desplegable del botón “Formato Condicional” selecciona la opción "Escalas de color" y dentro de ella el rango de colores que quiere utilizar.

Por último selecciona otro rango de celdas, y dentro del menú desplegable del botón “Formato Condicional” selecciona la opción “Conjunto de Iconos” y dentro de ella el rango de iconos que quiere utilizar.

Adicionalmente puede marcar gráficamente conjuntos de celdas que cumplan determinadas

condiciones ( Opciones : “Resaltar Reglas de Celdas, Reglas de la Parte Superior e Inferior”).

Page 24: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

24

Page 25: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

25

Vamos a aplicar formato condicional a:

Columna Instrumento:

Resaltar aquellos registros cuyo dato sea “Guitarra”

Inicialmente deberá bloquear el rango de celdas a las que se le desea aplicar el formato

condicional.

Una vez bloqueado ir a la pestaña inicio y seleccionar formato condicional:

Seleccionar Resaltar reglas de Celdas, luego verá varias opciones, escoja la que se

ajuste a su necesidad, en este caso es: “Es igual a…”, se desplegará una ventana

como la siguiente:

Complete la información que le solicita la ventana:

Page 26: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

26

Haciendo referencia las celdas pondrá =$A$4 y en la opción con

Aplique la opción de relleno que sea de su elección

El resultado será el siguiente:

Aplique formato condicional a la columna pago a aquellos registros cuyo valor a pagar sea de 70 $.

Aplique formato condicional a la Antigüedad pago a aquellos registros cuyo valor sea mayor o

igual a 10.

Aplique formato condicional a la columna Descuento a aquellos registros cuyo valor sea 0.

Page 27: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

27

Siempre con el archivo: 3.- Funciones Lógicas Anidadas, Libro: “Clase”, Complete las columnas

“Ingresa a una nueva sección” y “Bonificación” de acuerdo a la información que se le pide.

Las soluciones son las siguientes:

Para “Ingresa a una nueva sección”:

En este caso solamente basta con abrir el archivo Funciones Lógicas anidadas y escribir en la barra

de fórmulas en el primer registro del Archivo Funciones Lógicas Anidadas lo siguiente:

='[2.- Funciones Lógicas Anidadas.xlsx]SI.O'!$F$11

Esto lo que significa es que importará los datos almacenados en el archivo Funciones Lógicas

Anidadas.

Y dará como resultado:

Deberá hacerlo uno a uno.

Para “Bonificación”

=SI(O(F13=2,G13>=800000),G13*10%,SI(Y(G13>800000,G13<1200000),G13*7%,G13*5%))

Como podrá observar el nivel de complejidad pude ir incrementando, en este caso se hace uso de

una combinación de 3 funciones en una misma fórmula, lo cual no debe de extrañarnos puesto

que en las funciones anidadas pueden existir hasta 7 (siete) niveles.

Page 28: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

28

A continuación trabajaremos con el archivo Viviendas Urbanas Rurales. En este archivo pondrá en

práctica las funciones estudiadas (SI, O, Y) y además se hará uso de la función Max, Contar Si,

Sumar Si, Buscar Valor,

Iniciamos con el primer enunciado del ejercicio:

1.- Contar cuantas provincias tienen más viviendas urbanas que rurales.

Para dar solución a ello haremos uso de la función MAX

Descripción de la Función MAX

Devuelve el valor máximo de un conjunto de valores.

Sintaxis

MAX(número1;número2; ...)

Número1, número2... son de 1 a 255 números de los que desea encontrar el valor máximo.

Observaciones

Los argumentos pueden ser números, o nombres, matrices o referencias que contengan

números.

Se tienen en cuenta los valores lógicos y las representaciones textuales de números

escritos directamente en la lista de argumentos.

Si el argumento es una matriz o una referencia, sólo se utilizarán los números contenidos

en la matriz o en la referencia. Se pasarán por alto las celdas vacías, los valores lógicos o

el texto contenidos en la matriz o en la referencia.

Si el argumento no contiene números, MAX devuelve 0.

Los argumentos que sean valores de error o texto que no se pueda traducir a números

provocan errores.

Si desea incluir valores lógicos y representaciones textuales de números en una referencia

como parte del cálculo, utilice la función MAXA.

Con esta función contaremos cuál es el Valor Máximo en la columna % Viviendas Rurales

Page 29: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

29

A continuación se hace uso de la función Contar Si

Descripción de Contar SI

La función CONTAR.SI cuenta el número de celdas dentro de un rango que cumplen un solo

criterio especificado por el usuario. Por ejemplo, puede contar todas las celdas que comienzan con

cierta letra, o bien todas las celdas que contienen un número mayor o menor que un número

especificado por el usuario. Por ejemplo, suponga que tiene una hoja de cálculo que contiene una

lista de tareas en la columna A y el primer nombre de la persona asignada a cada tarea en la

columna B. Puede usar la función CONTAR.SI para contar cuántas veces aparece el nombre de una

persona en la columna B y, de esa forma, determinar cuántas tareas tiene asignadas. Por ejemplo:

=CONTAR.SI(B2:B25,"Nancy")

La sintaxis de la función CONTAR.SI tiene los siguientes argumentos (argumento: valor que

proporciona información a una acción, un evento, un método, una propiedad, una función o un

procedimiento.):

Contar.Si(rango,criterio)

rango Obligatorio. Una o más celdas que se van a contar, incluidos números o nombres,

matrices o referencias que contengan números. Los valores en blanco y los de texto no se

tienen en cuenta.

criterios Obligatorio. Número, expresión, referencia de celda o cadena de texto que

determina las celdas que se van a contar. Por ejemplo, los criterios pueden expresarse

como 32, ">32", B4, "manzanas" o "32".

Volviendo al ejercicio…

Se procede a contar cuántas provincias tienen el % de viviendas rurales mayor a 39.02

Queda de la siguiente manera:

Page 30: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

30

Hasta aquí hemos dado respuesta al primer enunciado del ejercicio.

Ahora nos pregunta: Hallar la cantidad total de viviendas rurales de aquellas provincias con

porcentaje de viviendas urbanas mayor al 90%

Para dar solución se hace uso de la Función Sumar Si

Descripción de Sumar Si

La función SUMAR.SI sirve para sumar los valores en un rango (rango: dos o más celdas de una

hoja. Las celdas de un rango pueden ser adyacentes o no adyacentes.) que cumple los criterios que

se especifican. Por ejemplo, supongamos que en una columna que contiene números, desea

sumar sólo los valores que son mayores que 5. Puede usar la siguiente fórmula:

=SUMAR.SI(B2:B25,">5")

En este ejemplo, los criterios se aplican a los mismos valores que se están sumando. Si lo desea,

puede aplicar los criterios a un rango y sumar los valores correspondientes en un rango distinto.

Por ejemplo, la fórmula =SUMAR.SI(B2:B5, "Juan", C2:C5) suma sólo los valores del rango C2:C5,

donde las celdas correspondientes al rango B2:B5 son iguales a "Juan."

La sintaxis de la función SUMAR.SI cuenta con los argumentos (argumento: valor que proporciona

información a una acción, un evento, un método, una propiedad, una función o un

procedimiento.) siguientes:

Sumar.Si(rango,Criterio,rango_Suma)

rango Obligatorio. El rango de celdas que se desea evaluar según los criterios

especificados. Las celdas de cada rango deben ser números, o bien nombres, matrices o

referencias que contengan números. Los valores en blanco y los de texto no se tienen en

cuenta.

Page 31: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

31

criterios Obligatorios. El criterio en forma de número, expresión o texto, que determina

las celdas que se van a sumar. Por ejemplo, los criterios pueden expresarse como 32,

">32", B5, 32, "32", "manzanas" u HOY().

Importante Cualquier criterio de texto o cualquier criterio que incluya los símbolos

lógicos o matemáticos debe estar entre comillas dobles ("). Si el criterio es numérico, las

comillas dobles no son obligatorias.

rango suma Opcional. Las celdas reales para agregar, si desea agregar celdas a las ya

especificadas en el argumento rango. Si se omite el argumento rango_suma, Excel agrega

las celdas que están especificadas en el argumento rango (las mismas celdas a las que se

aplica los criterios).

Volviendo al ejercicio la solución se muestra:

El punto 3 dice:

Agregar una columna a la tabla, de modo que muestre los siguientes mensajes:

"Altamente urbanizada" si el % de viv. Urbanas supera al 90%

"Medianamente urbanizada" si % de viv. Urbanas supera el 70%

"Urbanización baja" en el resto de los casos.

Para este caso se hace uso de función SI.

La solución es:

Page 32: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

32

=SI(D11>90,"Altamente Urbanizada",SI(D11>70,"Medianamente Urbanizada","Urbanización

Baja"))

El cuarto enunciado cita:

Crear una consulta de modo que al ingresar el nombre de la provincia podamos obtener:

Cualquiera de los datos de la tabla en forma individual con un texto que aclare qué tipo de dato se

está consultando.

EJEMPLO:

Ingreso: Tucumán

Obtengo el dato de la columna 2: 273759

Aparece un texto que indica que tipo de dato estoy viendo: "Total de viviendas del pais"

Para dar respuesta a este ejercicio se utilizará la Función Buscar Valor(BuscarV)

La descripción de la misma es la siguiente:

Page 33: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

33

BUSCARV(valor_buscado;matriz_buscar_en;indicador_columnas;ordenado)

Valor_buscado Valor que se va a buscar en la primera columna de la matriz (matriz: utilizada para

crear fórmulas sencillas que producen varios resultados o que funcionan en un grupo de

argumentos que se organizan en filas y columnas. Un rango de matriz comparte una fórmula

común; una constante de matriz es un grupo de constantes utilizadas como un argumento.) de

tabla. Valor_buscado puede ser un valor o una referencia. Si valor_buscado es inferior al menor de

los valores de la primera columna de matriz_buscar_en, BUSCARV devuelve al valor de error #N/A.

Matriz_buscar_en Dos o más columnas de datos. Use una referencia a un rango o un nombre de

rango. Los valores de la primera columna de matriz_buscar_en son los valores que busca

valor_buscado. Estos valores pueden ser texto, números o valores lógicos. Las mayúsculas y

minúsculas del texto son equivalentes.

Indicador_columnas Número de columna de matriz_buscar_en desde la cual debe devolverse el

valor coincidente. Si el argumento indicador_columnas es igual a 1, la función devuelve el valor de

la primera columna del argumento matriz_buscar_en; si el argumento indicador_columnas es igual

a 2, devuelve el valor de la segunda columna de matriz_buscar_en y así sucesivamente. Si

indicador_columnas es:

Si es inferior a 1, BUSCARV devuelve al valor de error #VALUE!

Si es superior al número de columnas de matriz_buscar_en, BUSCARV devuelve el valor

de error #REF!

Ordenado Valor lógico que especifica si BUSCARV va a buscar una coincidencia exacta o

aproximada:

Si se omite o es VERDADERO, se devolverá una coincidencia exacta o aproximada. Si no

localiza ninguna coincidencia exacta, devolverá el siguiente valor más alto inferior a

valor_buscado.

Los valores de la primera columna de matriz_buscar_en deben estar clasificados según un

criterio de ordenación ascendente; en caso contrario, es posible que BUSCARV no

devuelva el valor correcto. Para obtener más información, vea Ordenar datos.

Page 34: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

34

Si es FALSO, BUSCARV sólo buscará una coincidencia exacta. En este caso, no es necesario

ordenar los valores de la primera columna de matriz_buscar_en. Si hay dos o más valores

en la primera columna de matriz_buscar_en, se utilizará el primer valor encontrado. Si no

se encuentra una coincidencia exacta, se devolverá el valor de error #N/A.

Dando solución a lo que se nos pide en el ejercicio:

De igual manera se hace para el resto de las columnas lo que cambiará es el indicador de

columnas.

Quinto enunciado:

De acuerdo a los índices de deficiencia habitacional por región que figuran en la tabla auxiliar

construir una consulta que muestre cuántas viviendas deberían cosntruirse en la provincia

consultada. (El cálculo que debes hacer es: cantidad de viviendas de la prov. X % Deficiencia

Habitacional / 100)

Región Deficiencia Habitacional (%)

1 15

2 20

3 25

4 22

5 28

Page 35: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

35

La Respuesta será la siguiente:

=SI(G11=$B$63,B11*$C$63/100,SI(G11=$B$64,B11*$C$64/100,SI(G11=$B$65,B11*$C$65/100,SI(

G11=$B$66,B11*$C$66/100,B11*$C$67/100))))

Recuerde que para todos los casos si se le dificulta crear la fórmula directamente en la barra de

funciones, puede iniciar elaborando el algoritmo.

Page 36: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

36

Trabajando con Filtros Avanzados

Los filtros avanzados se utilizan para poder seleccionar casos específicos que nos interesen de

una tabla, así si tenemos una tabla con una cantidad considerable de registros podremos ubicar

aquellos elementos que cumplan los criterios que yo necesito.

El procedimiento es el siguiente:

Trabajaremos con el archivo plantilla para filtros avanzados.

Inicialmente se tiene una base de datos con la lista de los trabajadores de una empresa:

Genero Edad Función Salario Mensual

Años de laborar

Experiencia en el cargo

Masculino 35 Gerente 25000 6 4

Masculino 45 Técnico 8000 10 10

Femenino 55 Secretaria 4500 15 12

Femenino 31 Vice Gerente 22000 4 3

Masculino 28 Mantenimiento 5500 3 3

Masculino 35 Mantenimiento 5500 5 5

Masculino 40 Contador 8000 7 10

Femenino 56 Contador 8000 24 25

Femenino 48 Secretaria 3500 12 13

Femenino 33 Cajera 4500 5 6

Femenino 39 Cajera 4500 3 4

Masculino 21 Chofer 3800 1 2

Masculino 25 Electricista 4300 4 5

Masculino 46 Fontanero 3500 7 8

Masculino 48 Informático 10000 9 9

Femenino 25 Informático 10000 1 1

Femenino 27 Cocinera 3500 3 4

Femenino 28 Afanadora 2800 4 5

Femenino 31 Abogado 9000 6 6

Femenino 45 Relaciones Públicas 8700 10 11

Femenino 40 Relaciones Públicas 8700 9 10

Masculino 46 Carpintero 3800 8 9

Masculino 44 Contador 8000 7 8

Masculino 45 Analista 12000 3 4

Masculino 49 Programador 9000 3 4

Ahora suponga que desea establecer un filtro en el cual aparecerán solamente aquellas personas

cuyo género sea Masculino y cuyas edades sean mayores de 35 años:

Page 37: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

37

Pues bien, el criterio lo deberemos indicar en las celdas destinadas para ello.

A continuación seleccionar la pestaña Datos, ubicamos la opción filtros avanzados:

Nos cargará una ventana en la cual nos va a reconocer de manera automática el rango donde se

encuentran los datos que se desean evaluar, y luego le pediremos que de los resultados en otro

sitio dentro de la misma hoja

La ventana de filtro avanzado ya completa con la información se verá de la siguiente manera:

Page 38: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

38

Con la información ya ingresada en la ventana anterior veremos el resultado siguiente:

Veremos que solamente se desplegarán en la tabla nueva aquellos registros que cumplen con el

criterio que se ha establecido, en este caso, aquellos trabajadores del sexo masculino y con edad

superior a los 35 años.

Subtotales:

Los sub-totales se utilizan en Excel cuando trabajamos con listas de datos ordenadas.

Sirven para realizar cálculos totales y parciales de dichas listas. Imagínate este

ejemplo:

Page 39: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

39

Vamos a crear una lista con los subtotales por empresas acumulando las

ganancias debajo de la columna de Beneficios de forma que podamos ver los totales

parciales: - Selecciona todo el rango de datos (A1:E11)

- Accede a Datos - Subtotales... - Deja las opciones tal y como sigue a continuación:

Para cada cambio en EMPRESA (con esto agruparemos por empresas)

Usar función SUMA

Agregar subtotal a BENEFICIOS

- Acepta el cuadro de diálogo.

Observa que han aparecido los sub-totales bajo la columna de Beneficios

agrupados por países. A la izquierda aparecen unos signos que controlan el nivel de

desglose del sub-total. Podemos aumentar o disminuir el nivel del subtotal pulsando en

los signos + y - o bien en los números que aparecen sobre estos signos.

- Vuelve a seleccionar la lista de datos y vuelve a la opción Datos - Subtotales

- Cambia la opción Usar función y escoge la función Promedio

- Activa la casilla Agregar subtotal a GASTOS

- Desactiva la casilla Reemplazar subtotales actuales (si no la desactivamos,

perderíamos los subtotales conseguidos).

- Acepta el cuadro de diálogo.

Ahora hemos añadido los promedios de gastos a parte de los sub-totales anteriores

de los Beneficios. Con esta opción podemos conseguir niveles de desglose en grandes hojas de cálculo.

Si quisiéramos que desapareciesen los sub-totales, deberíamos acceder a Datos -

Subtotales - Quitar todos

Si queremos realizar sub-totales de otro campo (por ejemplo por países),

deberíamos primero ordenar la tabla por esa columna.

Page 40: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

40

Clase Práctica (Archivo Agencia de Publicidad)

Una empresa de publicidad registra parte de la información de su gestión en un libro de Excel. Ud.

debe colaborar en la administración de la misma, realizando los puntos que se solicitan a

continuación.

1) En la hoja DATOS, se pide:

a) Mostrar para la columna HORARIO, la información correspondiente al código de

horario, datos que se localizan en la hoja CODIGOS.

b) En la columna TIPO COMERCIAL, y teniendo en cuenta la cantidad de segundos que

duran los comerciales, se debe mostrar el siguiente texto:

a.1) CORTO, para aquellos comerciales menores a 15 segundos inclusive;

b.1) MEDIO, para aquellos comerciales entre 16 y 30 segundos inclusive;

c.1) LARGO, para aquellos comerciales mayores a 30 segundos.

c) Calcular la columna COSTO, teniendo en cuenta que los importes se localizan en la hoja

TARIFAS, discriminados por medio de comunicación y tipo de horario.

d) Calcular la columna DESCUENTO, en la que se debe mostrar el importe de un

descuento, que se le realiza a los costos de los comerciales de las empresas, según el rubro al que

pertenecen. Tener en cuenta que este descuento sólo se realizará a las empresas con

denominación SA y que los porcentajes de descuento se localizan en la hoja TARIFAS.

Calcular la columna TOTAL, dato que debe mostrarse en pesos, según la cotización

establecida en la hoja TARIFAS, y en el que se debe considerar el costo y el descuento

correspondiente.

2) En una hoja a la que deberá nombrar como ESTADISTICA, se solicita:

a) Mostrar un cuadro en el que se listen la cantidad de segundos utilizados por medio de

comunicación y por tipo de horario.

b) Mostrar un cuadro en el que se listen los promedios (en duración) de los comerciales

por rubro de las empresas.

3) En una hoja a la que deberá nombrar como CONSULTAS, se solicita:

a) Crear una ficha de consulta, la que permita, luego de ingresar el nombre de una

empresa, que se desplieguen los siguientes datos automáticamente: rubro, medio de

comunicación y tipo de horario, para la empresa determinada.

Page 41: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

41

Crear una tabla dinámica

Para crear una tabla dinámica se usará el Asistente para tablas dinámicas: un conjunto

interactivo de cuadros de diálogo que permiten elegir los datos fuente y la distribución de la

tabla dinámica. Como ejemplo de su utilización se aplicará al siguiente conjunto de datos:

Trabajaremos con el archivo de Excel Tablas dinámicas.

Una vez cargado el archivo procederemos a seleccionar en la pestaña insertar Tablas Dinámicas:

Luego se abrirá una ventana que nos guiará paso a paso sobre la forma en que deseamos

organizar la tabla dinámica. Cabe señalar que para este ejemplo la fuente de los datos que serán

Page 42: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

42

utilizados es un archivo de Excel, pero habrá ocasiones en que no necesariamente se utilizarán

archivos creados en tablas de Excel, en tal caso se hace uso de la opción: fuente de datos externa.

El asistente es el que sigue:

Inicialmente se seleccionarán los datos que se desean analizar para lo cual se establecerá el rango

donde se encuentra la información.

A continuación se debe indicar la ubicación donde se volcará el informe.

Luego presionar el botón Aceptar.

Page 43: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

43

El resultado es el siguiente:

Nótese que inicialmente la tabla dinámica no contiene datos, esto es porque al lado derecho de

la pantalla de Excel aparecen las opciones necesarias para organizar mi informe.

Así ubicaremos por ejemplo en campos de columna los vendedores, y en campos de fila los meses

y en la opción “Coloque datos aquí” las ventas.

Page 44: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

44

El resultado es:

Ahora podremos ver un informe que me indica la cantidad de dinero por mes que cada vendedor

logró realizar.

Además podemos tener diferentes opciones en el sentido que por ejemplo están disponibles las

siguientes funciones para tablas dinámicas:

Para ello basta con dar doble click en

También podemos crear tablas dinámicas a partir de datos externos.

Page 45: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

45

Creando tablas dinámicas con conexión a un origen de datos externos.

NOTA:

Acá se muestra únicamente el procedimiento para mandar a llamar archivos de bases de datos

en Excel y a partir de ellos conformar nuestra tabla dinámica.

Para trabaja con conexión a un origen de datos realice lo siguiente: 1. Abra un nuevo libro en MS Excel 2. Ir al menú Datos 3. Haga clic en Obtener datos externos 4. De clic en De otras fuentes

5. Seleccione Desde Microsoft Query

6. Despliega:

Page 46: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

46

7. De la pestaña Base de datos seleccione <Nuevo origen de datos>

8. Ingrese el nombre que desea para la conexión 9. Seleccione el driver 10. De clic en Conectar 11. Localice el directorio del archivo Una vez localizado el archivo podrá estructurar su tabla dinámica.

Page 47: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

47

Crear Gráficos Dinámicos

Un informe de gráfico dinámico representa gráficamente los datos de un informe de tabla

dinámica (informe de tabla dinámica: informe de Excel interactivo de tablas cruzadas que resume y

analiza datos, como registros de una base de datos, de varios orígenes, incluidos los que son

externos a Excel.). Puede cambiar el diseño y los datos mostrados en un informe de gráfico

dinámico igual que en un informe de tabla dinámica.

El procedimiento para crear un gráfico dinámico es similar al de crear una tabla dinámica, así pues

los pasos son los siguientes:

Seleccionamos la pestaña Insertar y en la esquina superior izquierda escogemos grafico dinamico:

Una vez hecho esto Excel nos pedirá que seleccionemos el origen o el rango de datos a graficar

Y además deberemos indicar dónde deseamos volcar los datos dela tabla y el gráfico dinámico.

Para efectos de este ejercicio seleccionar la opción una hoja de cálculos existentes.

Page 48: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

48

El resultado se muestra a continuación:

lo que resta es organizar los datos que pasarán a formar parte la tabla y del gráfico dinámico:

El resultado final dependerá de la estructura que usted desea para su tabla y gráfico:

Cabe señalar que es posible cambiar o modificar cualquiera de los elementos que forman parte del

gráfico que se haya generado. Bastará con hacer doble click sobre el elemento que se desea

modificar e inclusive podrá modificar el tipo de gráfico.

Page 49: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

49

Trabajando con dos libros de Excel a la vez:

Es posible trabajar con dos libros a la vez, para ello deberá cargar los dos libros y una vez hecho

esto podrá mandar a llamar los datos de un libro y hacer uso de las funciones que estime

conveniente.

El procedimiento es sencillo:

Basta con anteponer el signo “=” en la barra de fórmulas e ir al libro del cual se desean obtener los

datos:

La sintaxis es:

=´[Nombre del archivo]Nombre de la hoja!´ Celda

Adicional a ello es posible que varios usuarios trabajen con un mismo libro a la vez para ello siga

los siguientes pasos:

En el menú Herramientas, elija Compartir libro y, a continuación, haga clic en la ficha Modificación.

Active la casilla de verificación Permitir la modificación por varios usuarios a la vez y, a

continuación, haga clic en Aceptar.

Cuando se solicite, guarde el libro.

En el menú Archivo, haga clic en Guardar como y guarde el libro compartido en una ubicación de

Page 50: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

50

red a la que otros usuarios puedan tener acceso. Utilice una carpeta de red compartida, no un

servidor Web.

Notas

Todos los usuarios que tienen acceso a un recurso de red tienen acceso sin restricción al libro

compartido, a menos que se utilice el comando Proteger hoja (menú Herramientas, submenú

Proteger) para restringir el acceso.

Importar archivos desde Microsoft Access a Excel.

Es posible importar archivos que no necesariamente hayan sido creados en Excel, pero que tengan

información tabular, como por ejemplo los archivos de bases de datos creados en Access.

Para importarlos siga las siguientes instrucciones:

1.- Hacer click en la pestaña datos seleccionar la primera opción a la izquierda de su pantalla

Luego le pedirá el programa que le de la ubicación donde se encuentra almacenado el archivo que

se desea importar.

Page 51: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

51

A continuación le pedirá que le indique cuál de las tablas de la base de datos es la que desea

importar:

Esto es debido a que una base de datos puede contener “N” cantidad de bases de datos.

Luego le indicará cómo desea ver los datos:

NOTA : si selecciona la opción ver datos como tabla Excel importará la tabla completa, no así

cuando se selecciona la opción de tabla dinámica o informe de tabla y gráfico dinámico, de ser

esta la opción solamente podrá ver los datos como tabla dinámica pero no la tabla conteniendo

todos los datos originales de la base de datos.

Page 52: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

52

Importar archivos de texto:

Seleccione la pestaña datos:

Seleccionar la opción desde texto.

A continuación indicarle la ruta donde se encuentra almacenado el archivo de texto:

Luego el programa desplegará un asistente que le guiará paso a paso sobre la forma en que fueron

almacenados los datos y le pedirá que seleccione los delimitadores de la información contenida en

el archivo txt:

Page 53: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

53

Finalmente le solicita la ubicación dentro del libro en la cual va a desplegar los datos:

Page 54: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

54

CLASE PRÁCTICA

Con el archivo Facturas sacar la siguiente información

1.- Agregar una columna para clasificar las facturas en Vencidas y No Vencidas de acuerdo al

siguiente criterio:

Serán facturas vencidas todas aquellas cuyo tiempo sea superior a los 75 días a partir de la fecha

de emisión de dicha factura en correspondencia con la fecha de hoy.

2.- agregar una columna en la que se mostrara la cantidad de días que tiene de haber sido emitida

cada factura.

3.- Cuantas facturas están con el estatus de vencidas y no vencidas?

4.- A cuánto asciende el monto por cobrar en concepto de facturas vencidas?

5.- A cuánto asciende el monto por cobrar en concepto de facturas no vencidas?

6.- Agregar el siguiente grafico dinámico:

7.- Crear una consulta de tal manera que cuando se ingrese el número de factura se pueda

visualizar el monto su estatus (vencida o no vencida), el monto de la factura y la cantidad de días

que tiene de emitida dicha factura.

8.- Elaborar un filtro en el que se muestren únicamente aquellas facturas cuya cantidad de días de

vencimiento sea superior a los 200 días.

9.- Elaborar un filtro en el que se muestren únicamente aquellas facturas cuyo estatus sea No

Vencidas e indicar el monto máximo y el mínimo para este tipo de facturas

10.- aplicar formato condicional a aquellas facturas cuyo monto sea superior a los 35000, esto lo

hará en la matriz principal.

11.- Suponga que la empresa va a establecer un plazo de 2 años para pagar a aquellas facturas que

tienen más de 200 días de haber sido emitidas, pero con una tasa de interés del 12% anual. Hacer

el cálculo del pago mensual que se le entregara a la empresa para cada factura que cumpla con el

criterio de más de 200 días vencida.

Page 55: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

55

Creando Mis Propias Funciones.

Para crear funciones definidas por el usuario en Excel siempre debemos crear un módulo en el

editor de Visual Basic con el código de la función.

Para ello debemos agregar la pestaña Visual Basic en Excel pues no la carga automáticamente. La

pestaña es:

Para ello debemos ir al menú inicio de Excel y abrir la ventana de opciones:

AQUÍ

Una vez en esta opción ir al menú Personalizar y en el cuadro de lista comandos disponibles

seleccionar la ficha programador

Page 56: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

56

A continuación seleccionar Visual Basic y hacer click en el comando agregar:

Page 57: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

57

Damos click en aceptar. Una vez hecho esto volvemos a la interface de trabajo de Excel ya con la

ícono de visual basic cargado en la esquina superior izquierda de la pantalla:

A continuación procedemos a activarlo, nos cargará una ventana que es en donde procederemos

a crear nuestra función:

Ahora vamos al Menú Insertar y seleccionamos Módulo:

Procedemos a escribir el código:

Function Bono(HoraExtra, Basico)

If HoraExtra > 500 And HoraExtra < 1000 Then

Bono = Basico * 0.02

Page 58: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

58

Else

If HoraExtra > 1001 And HoraExtra < 2000 Then

Bono = Basico * 0.0325

Else

Bono = Basico * 0.04

End If

End If

End Function

Veamos la explicación del código:

Function: es la sentencia que nos permite crear una función.

Bono: Es el nombre de la función que estamos creando, dicho nombre lo define el usuario.

(HoraExtra,Basico): Son variables también definidas por el usuario y son los valores que recibe

nuestra función como parámetros o valores de entrada.

A continuación viene lo que se conoce como el cuerpo de la función en ella van contenidas las

sentencias o instrucciones de lo que se desea que haga la función que se está creando:

If, end If: Condicional SI. Si se cumple con una prueba lógica ejecutará una serie de

instrucciones(Valor si Verdadero), si no se cumple la prueba lógica ejecutará otra serie de

instrucciones (Valor si Falso).

La sintaxis de IF es.

IF Prueba Lógica Then

Valor SI Verdadero

Else

Valor SI FALSO

End If

End Function: Estas palabras indican el fin de la función.

Page 59: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

59

A continuación damos click en el menú Archivo y le decimos Cerrar y Volver a Excel

Ahora ha llegado el momento de poner a prueba nuestra función. Para ello nos ubicamos en la

celda donde vamos a hacer uso de ella en este caso será en la celda B4 de la Hoja Colilla Pago,

Luego la mandamos a llamar de la misma forma como cualquier función de las ya definidas por

defecto en Excel es decir así:

Page 60: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

60

Importante: Al momento de hacer uso de la función debe hacerse con el cuidado de ingresar los

parámetros en el orden correcto, es decir, si en Visual se pide primeramente Hora Extra y luego

Básico así se deberá enviar los parámetros a la función:

Function Bono(HoraExtra, Basico)

En este caso el valor almacenado en la celda A4 será tomado por la variable HoraExtra, y el valor

almacenado en la celda B1 será asignado a la variable Basico. Luego estos valores serán

utilizados en el cuerpo de la función para realizar las operaciones contenidas en la misma.

Otro aspecto importante es:

1.- Las funciones definidas por el usuario solamente tienen validez en el archivo en el que están

siendo creadas.

2.- Cuando vaya a guardar el archivo que contiene la función nueva deberá dar las siguientes

opciones:

Menú Principal de Excel:

Guardar como

Libro de Excel Habilitado Para Macros.

Page 61: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

61

Sentencia Switch.

En Excel, la función Switch evalúa una lista de expresiones y devuelve el valor

correspondiente a la primera expresión en la lista que es True.

La sintaxis de la función del interruptor es:

Switch (expression1, valor1, expression2, valor2, ... expression_n, value_n)

expression1, expression2, expression_n es una lista de expresiones que se evalúan.

La función del interruptor está buscando a la primera expresión que se evalúa como verdadero

valor1, valor2, ... value_n es una lista de valores

La función del interruptor devolverá el valor asociado a la primera expresión que se evalúa como

verdadero.

Por ejemplo:

Switch (IdProveedor = 1, "IBM", IdProveedor = 2, "HP", IdProveedor = 3, "NVIDIA")

En este ejemplo, si IdProveedor es 1, entonces la función del interruptor volverá "IBM".

Si IdProveedor es 2, entonces la función del interruptor volverá "HP".

Si IdProveedor es 3, entonces la función del interruptor volverá "NVIDIA".

SI IdProveedor fuera un valor diferente a los estipulados devolverá el mensaje: “Error Proveedor

no Existe”

Page 62: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

62

El código de VBA

La función del interruptor sólo se puede utilizar en el código VBA. Por ejemplo:

Valori = Switch (IdProveedor = 1, "IBM", IdProveedor = 2 "HP", IdProveedor = 3, "NVIDIA")

Haciendo el ejemplo en Excel:

Function proveedor(valor)

Dim cadena As String

cadena = "Error"

If valor >= 1 And valor <= 3 Then

proveedor = Switch(valor = 1, "IBM", valor = 2, "HP", valor = 3, "NVIDIA")

Else

proveedor = "Error proveedor no existe"

End If

End Function

Haciendo el llamado a la función “proveedor”

Modificando el ejercicio del archivo: “formato de entradas y salidas”.

Crear una función cuyo nombre sea “Viaticos” la cual recibirá como valores de entrada el salario

básico y las horas extras. Hará uso de los mismos parámetros que con la función Bono es decir:

Si Horas Extras (Salario) se encuentra entre C$ 500 y menor o igual a C.$ 1000 recibirá un bono del

2 % sobre el Salario básico, si es mayor o igual a C.$ 1001 pero inferior o igual a C.$ 2000 el bono

Page 63: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

63

será del 3.25 % sobre el Salario básico, para el resto de los casos el Bono será de 4 % siempre

sobre el salario básico.

Como recordará el código en visual basic haciendo uso de la sentencia IF era:

Function Bono(HoraExtra, Basico)

If HoraExtra <= 500 Then

Bono = 0

Else

If HoraExtra > 500 And HoraExtra <= 1000 Then

Bono = Basico * 0.02

Else

If HoraExtra >= 1001 And HoraExtra < 2000 Then

Bono = Basico * 0.0325

Else

Bono = Basico * 0.04

End If

End If

End If

End Function

Ahora hacienda uso de la Sentencia de control Switch la función tendrá las siguientes líneas de

código:

Function Viaticos(extra, basic)

Viaticos = Switch(extra <= 500, 0, extra > 500 And extra <= 1000, basic * 0.02, extra >= 1001 And

extra < 2000, basic * 0.0325, extra >= 2000, basic * 0.04)

End Function

Como verá las líneas de código se simplifican y el resultado es el mismo.

Page 64: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

64

Práctica:

1.- A la vista de los datos que se presentan en el siguiente formato de factura, obtener:

El precio bruto, neto y con IVA para cada producto

Los Totales bruto y con IVA de la factura

Artículo Precio

Unitario

Sin IVA

IVA Cantidad Sub Total

Sin IVA

Impresora

29.900

2

CPU Pentium II

110.000

4

Monitor color

52.000

4

teclado

600

4

Ratón

1.500

4

SUB TOTALES

DESCUENTO

GRAN TOTAL

Los descuentos serán: hasta 50000 5%

50001 - 150000 8%

Más de 150000 12%

Page 65: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

65

Las listas en Excel

Las listas es una de las opciones que más se utiliza en Excel. Permiten almacenar datos en

forma de columnas a modo de base de datos para posteriormente realizar cálculos, consultar

datos, realizar sub-totales, etc.

Normalmente, una lista contiene las cabeceras de los datos en la primera fila. Estas cabeceras son

los títulos de los campos. Un campo es un dato individual con un nombre propio.

Validación de los datos

Imagina que existe una celda que tendrá siempre un dato elegido de entre una lista. En vez

de escribir manualmente ese dato, podemos crear una lista desplegable, restringir entradas,

limitar el número de caracteres de la celda, mostrar mensajes de ayuda, etc.

1. Sitúa el cursor en la celda D2.

2. Accede a Datos - Validación y te aparecerá el siguiente cuadro:

En la lista desplegable Configuración podemos elegir qué valores serán válidos para la

celda activa. Desplégala y observa las distintas posibilidades de la misma.

3. Elige finalmente la opción Lista. Te aparecerá una casilla de texto; escribe lo siguiente

(separado coma): Pedernales,Barahona,Azua,Bani,Neyba,Jimani

4. Acepta el cuadro de diálogo. Observarás que ha aparecido una flecha típica de las

listas desplegables:

Page 66: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

66

5. Prueba a desplegarla y observa su contenido:

Algunas de las aplicaciones de las listas pueden ser: Elaboración de Presupuestos; Trabajar con

encuestas, Elaborar Horarios, etc.; en fin todo dependerá de la creatividad que cada usuario pueda

tener.

Ejemplo de aplicación 1:

A continuación trabajar con el archivo Validación de Listas-Encuestas, en él cree todas las listas y

posteriormente llenar la hoja de encuestas que se muestra en la página siguiente:

Page 67: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

67

Nombre Asentamiento

Distrito No. Familias

No. Viviendas

Fam. Exterior

Tipos de Ingresos

No. Personas en la Vivienda

No. De Cuartos

Tamaño del Lote

Hialea 1 Distrito 4 2 Familias 2 Viviendas

SI Trabajo por Cuenta Propia

4 a 7 Habitantes

Más de 2 Cuartos

Más de 120 Mts2

Candelaria Distrito 2 1 Familia 1 Vivienda SI Salario 4 a 7 Habitantes

Más de 2 Cuartos

Menos de 80 Mts2

Manchester Distrito 2 2 Familias 2 Viviendas

SI Salario 4 a 7 Habitantes

Más de 2 Cuartos

81 a 120 Mts2

Hialea 1 Distrito 4 2 Familias 1 Vivienda NO Trabajo por Cuenta Propia

4 a 7 Habitantes

Más de 2 Cuartos

81 a 120 Mts2

Naciones Unidas

Distrito 5 1 Familia 1 Vivienda SI Remesas Familiares

1 a 3 Habitantes

2 Cuartos Menos de 80 Mts2

Candelaria Distrito 2 Más de 2 Familias

2 Viviendas

SI Salario Más de 7 Habitantes

2 Cuartos 81 a 120 Mts2

Manchester Distrito 2 2 Familias 1 Vivienda NO Trabajo por Cuenta Propia

Más de 7 Habitantes

Más de 2 Cuartos

Más de 120 Mts2

La Cruz Distrito 3 1 Familia 1 Vivienda SI Trabajo por Cuenta Propia

1 a 3 Habitantes

1 Cuarto Menos de 80 Mts2

Hialea 1 Distrito 4 Más de 2 Familias

2 Viviendas

NO Salario 4 a 7 Habitantes

2 Cuartos Más de 120 Mts2

Naciones Unidas

Distrito 5 1 Familia 1 Vivienda NO Salario 4 a 7 Habitantes

2 Cuartos Menos de 80 Mts2

Candelaria Distrito 2 2 Familias 2 Viviendas

SI Trabajo por Cuenta Propia

Más de 7 Habitantes

Más de 2 Cuartos

81 a 120 Mts2

Manchester Distrito 2 1 Familia 1 Vivienda SI Remesas Familiares

1 a 3 Habitantes

2 Cuartos Menos de 80 Mts2

La Cruz Distrito 3 Más de 2 Familias

Más de 2 Viviendas

NO Trabajo por Cuenta Propia

Más de 7 Habitantes

Más de 2 Cuartos

Más de 120 Mts2

Hialea 1 Distrito 4 1 Familia 1 Vivienda SI Salario 4 a 7 Habitantes

Más de 2 Cuartos

81 a 120 Mts2

Naciones Unidas

Distrito 5 2 Familias 2 Viviendas

NO Salario 4 a 7 Habitantes

2 Cuartos Más de 120 Mts2

Candelaria Distrito 2 1 Familia 1 Vivienda SI Remesas Familiares

1 a 3 Habitantes

2 Cuartos 81 a 120 Mts2

Manchester Distrito 2 Más de 2 Familias

Más de 2 Viviendas

SI Trabajo por Cuenta Propia

Más de 7 Habitantes

Más de 2 Cuartos

Más de 120 Mts2

La Cruz Distrito 3 1 Familia 2 Viviendas

NO Salario 4 a 7 Habitantes

2 Cuartos 81 a 120 Mts2

Hialea 1 Distrito 4 2 Familias 1 Vivienda SI Trabajo por Cuenta Propia

Más de 7 Habitantes

Más de 2 Cuartos

81 a 120 Mts2

Naciones Unidas

Distrito 5 1 Familia 1 Vivienda NO Salario 1 a 3 Habitantes

1 Cuarto Menos de 80 Mts2

La Cruz Distrito 3 Más de 2 Familias

Más de 2 Viviendas

SI Salario Más de 7 Habitantes

Más de 2 Cuartos

Más de 120 Mts2

Page 68: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

68

Manchester Distrito 2 1 Familia 1 Vivienda NO Salario 4 a 7 Habitantes

2 Cuartos Menos de 80 Mts2

Candelaria Distrito 2 Más de 2 Familias

Más de 2 Viviendas

NO Trabajo por Cuenta Propia

Más de 7 Habitantes

Más de 2 Cuartos

Más de 120 Mts2

Hialea 1 Distrito 4 2 Familias 2 Viviendas

SI Salario Más de 7 Habitantes

Más de 2 Cuartos

Más de 120 Mts2

Naciones Unidas

Distrito 5 1 Familia 1 Vivienda SI Remesas Familiares

1 a 3 Habitantes

1 Cuarto Menos de 80 Mts2

Manchester Distrito 2 2 Familias 2 Viviendas

SI Trabajo por Cuenta Propia

Más de 7 Habitantes

Más de 2 Cuartos

Más de 120 Mts2

La Cruz Distrito 3 1 Familia 1 Vivienda NO Salario 4 a 7 Habitantes

2 Cuartos Menos de 80 Mts2

Candelaria Distrito 2 2 Familias 1 Vivienda NO Remesas Familiares

4 a 7 Habitantes

2 Cuartos Menos de 80 Mts2

Naciones Unidas

Distrito 5 1 Familia 1 Vivienda SI Trabajo por Cuenta Propia

4 a 7 Habitantes

2 Cuartos Menos de 80 Mts2

Hialea 1 Distrito 4 Más de 2 Familias

1 Vivienda NO Salario Más de 7 Habitantes

Más de 2 Cuartos

Más de 120 Mts2

La Cruz Distrito 3 Más de 2 Familias

2 Viviendas

SI Remesas Familiares

4 a 7 Habitantes

2 Cuartos Más de 120 Mts2

Manchester Distrito 2 2 Familias 2 Viviendas

NO Trabajo por Cuenta Propia

Más de 7 Habitantes

Más de 2 Cuartos

81 a 120 Mts2

Candelaria Distrito 2 1 Familia 1 Vivienda SI Remesas Familiares

1 a 3 Habitantes

2 Cuartos Menos de 80 Mts2

Una vez ingresadas las encuestas, proceda a sacar el resumen de los datos en la hoja Consolidados

del mismo libro.

Ejemplo de Aplicación 2:

Elaborar el presupuesto que se presenta en la hoja siguiente, trabajar con el archivo Validación de

Listas – Presupuestos.

Page 69: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

69

Actividad Primaria Sub Etapas U/M Cant. Precio Unit. Precio Total

Preliminares Limpieza Inicial Gbl 1.00 800.00 800.00

Trazo y Nivelación Gbl 1.00 1500.00 1500.00

Demoliciones M2 10.00 250.00 2500.00

Otros trabajos preliminares Gbl 1.00 1000.00 1000.00

Fundaciones Excavación estructural M3 0.36 500.00 180.00

Formaletas Ml 6.00 350.00 2100.00

Concreto M3 0.36 3580.50 1288.98

Acero de refuerzo Lbs 100.00 9.00 900.00

Mampostería Bloques de cemento M2 9.00 500.00 4500.00

Estructura de Concreto Acero de refuerzo Lbs 500.00 9.00 4500.00

Concreto estructural M3 0.50 3580.50 1790.25

Techos y Fascias Estructura de acero Lbs 200.00 12.00 2400.00

Cubierta de laminas de zinc (especificar calibre) M2 15.00 114.83 1722.44

Hojalatería Ml 3.00 400.00 1200.00

Aislante para techos Glb 1.00 500.00 500.00

Fascias Ml 3.00 303.80 911.40

Canales Ml 3.00 500.00 1500.00

Acabados Repello corriente M2 18.00 150.00 2700.00

Enchape de Azulejos M2 6.00 325.50 1953.00

Afinado M2 18.00 200.00 3600.00

Cielos Rasos Esqueleteado de Madera M2 12.00 130.20 1562.40

Forro de PLYCEM M2 12.00 173.60 2083.20

Pisos Conformación y compactación M2 12.00 60.00 720.00

Cascote M2 12.00 350.00 4200.00

Ladrillo corriente M2 12.00 250.00 3000.00

Pulido y abrillantado de piso M2 12.00 110.00 1320.00

Particiones Forro de Gypsum M2 14.00 477.40 6683.60

Carpintería Fina Estante de madera Ml 3.00 5425.00 16275.00

Puertas Reparación de puertas con cerradura C/U 2.00 250.00 500.00

Ventanas Reparación de ventanas de aluminio y vidrio C/U 1.00 350.00 350.00

Obras Sanitarias Obras civiles Glb 1.00 1000.00 1000.00

Tuberias y accesorios para aguas negras Ml 12.00 40.00 480.00

Tuberia y accesorios para agua potabe Ml 24.00 25.00 600.00

Artefactos sanitarios Glb 1.00 1000.00 1000.00

Accesorios sanitarios Glb 1.00 500.00 500.00

Electricidad Obras civiles Glb 1.00 1600.00 1600.00

Canalizaciones Ml 18.00 651.00 11718.00

Alambrados Ml 18.00 350.00 6300.00

Pintura Pintura corriente M2 36.00 217.00 7812.00

Pintura Anticorrosiva Glb 1.00 700.00 700.00

Limpieza final Glb 1.00 500.00 500.00

Entrega y detalles Glb 1.00 2129.00 2129.00

Limpieza Final y Entrega Limpieza regular M2 24.00 100.00 2400.00

Entrega de Detalles Glb 1.00 1000.00 1000.00

Limpieza Final Días 3.00 500.00 1500.00

Indemnización Indemnización de daños causados por fuerza mayor Glb 1.00 5600.00 5600.00

Aplicar Sub totales para sacar un consolidad de los costos por etapas.

Page 70: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

70

BONO:

¿Cómo agregar un botón de comando que me permita desplazarme por las diferentes hojas de

un mismo libro?

Ejemplo:

En la hoja portada del archivo Reporte Calibraciones, con el dato del código del cliente presente el

nombre del cliente, la fecha de calibración de los equipo y pueda visualizar usando el botón como

link los certificados de calibración de los equipos del cliente que se encuentran en hojas

independiente del mismo libro.

Inicialmente deberá ingresar a las opciones de Excel para activar los siguientes complementos:

En la opción personalizar Cinta de Opciones, Buscar las fichas de herramientas y allí agregar las

herramientas de PROGRAMADOR:

Luego se verá la ventana de Excel con esta pestaña:

Page 71: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

71

A continuación seleccionar en la ficha Programador, Insertar Controles de Active X

En esta parte ubicar el botón de comando.

El siguiente paso será crear dicho botón:

1) Haz un clic-secundario sobre la etiqueta con el nombre de la hoja donde incrustaste el botón de comando 2) selecciona "ver código" 3) copia/pega (o escribe) las siguientes líneas (obviamente, modificas al nombre real de la hoja y el del botón): Private Sub CommandButton1_Click() Worksheets("nombre de alguna hoja existente").Select End Sub. NOTA: Estos son los comandos básicos para que cuando de un click en el botón este cargue la hoja que

nosotros deseamos, pero como el ejemplo nos pide cargar diferentes hojas en dependencia de los

códigos que se generan en una celda el código del botón de comando será el siguiente:

Private Sub CommandButton1_Click()

If Range("H3").Value = "W36" Then

Worksheets("W36").Select

Else

If Range("H3").Value = "W37" Then

Worksheets("W37").Select

Else

If Range("H3").Value = "W38" Then

Worksheets("W38").Select

Else

Page 72: 103589385 Manual de Excel Avanzado

Ing. Noel Antonio Pavón L.

72

If Range("H3").Value = "W39" Then

Worksheets("W39").Select

Else

If Range("H3").Value = "W40" Then

Worksheets("W40").Select

Else

Worksheets("W41").Select

End If

End If

End If

End If

End If

End Sub