EXCEL AVANZADO 2007.pdf

8/17/2019 EXCEL AVANZADO 2007.pdf

1/69

Excel2007Suite Ofimática

Huérfanos 863 – Piso 2, Santiago.

+56 (2) 439 88 00 www.edutecno.com

2007

Avanzado


2/69




Disposiciones generalesI.- Con la finalidad de optimizar el aprendizaje de nuestros estudiantes y prestar un mejor

atención, Edutecno dispuso de una serie de normas que todos los participantes deben respetar.Esto asegurará una mejora continua tanto del desarrollo de nuestras clases como una optimizaciónde los conocimientos entregados a los alumnos.

Apagar o poner en silencio el celular al momento de entrar a clasesNo se deben entrar líquidos ni comida al salón.

Las clases comienzan puntualmente según el horario establecido entre la Empresa yEdutecno.

Las personas que lleguen después de los 10 primeros minutos de comenzada la clase,serán considerados como atrasados.

Ante cualquier reclamo se debe pedir el libro de sugerencias en recepción.

Si el curso se realiza fuera de las oficinas de Edutecno, enviar sus sugerencias al [email protected]

II.- Además ponemos en conocimiento de nuestros alumnos y encargados de capacitaciónlos requisitos que deben cumplir los estudiantes para aprobar un curso.

La asistencia mínima para aprobar es de un 75 %.

La nota final del alumno se obtendrá de un 80 % examen práctico, 10 % participación enclases y 10 % asistencia.

Una vez finalizada la capacitación, Edutecno enviará a la empresa un certificado con lanota final de la persona y porcentaje de asistencia.

Los alumnos que falten a la evaluación final, deberán comunicarse directamente con elencargado de capacitación de su empresa para justificar su inasistencia y a través de él

coordinar la rendición de la prueba pendiente. Este proceso debe realizarse en un plazo noposterior a 1 semana después de finalizado el curso, pasado este tiempo, el alumno seráreprobado por nota.

mailto:[email protected]:[email protected]:[email protected]


3/69




ContenidoDISPOSICIONES GENERALES ........................................................................................ 2

CONTENIDO ..................................................................................................................... 3

INTRODUCCIÓN ............................................................................................................... 5 CONSOLIDAR ................................................................................................................... 6

OPCIONES DE ESQUEMA ............................................................................................... 9

CREAR ESQUEMAS ..................................................................................................................... 10

BORRAR ESQUEMAS .................................................................................................................. 13

OPTIMIZANDO USANDO BUSCAR OBJETIVO Y SOLVER ........................................... 14

BUSCAR OBJETIVO ......................................................................................................................... 14

SOLVER .......................................................................................................................................... 16

INSTALACIÓN DE COMPLEMENTO SOLVER ............................................................................... 16

USO DE SOLVER ......................................................................................................................... 16

INFORMES ................................................................................................................................. 19

ESCENARIOS POSIBLES CON ADMINISTRADOR DE ESCENARIOS ......................... 21

SOLVER Y EL ADMINISTRADOR DE ESCENARIOS ....................................................................... 23

VALIDACIÓN ................................................................................................................... 24

CONFIGURACIÓN DE UNA VALIDACIÓN ........................................................................................ 24

MENSAJES ...................................................................................................................................... 26

FORMATO CONDICIONAL ............................................................................................. 28

APLICACIÓN DE UN FORMATO CONDICIONAL .............................................................................. 28

MANEJO DE DATOS EN MSEXCEL ............................................................................... 31

FILTROS .......................................................................................................................................... 31

FILTRO AVANZADO .................................................................................................................... 31

FUNCIONES DE “FORMULARIO” .................................................................................................... 34

BOTONES ................................................................................................................................... 35

FUNCIONES DE BÚSQUEDA ........................................................................................................... 35

FUNCIONES DE BASE DE DATOS .................................................................................................... 39


4/69




RANGO DE CRITERIOS................................................................................................................ 39

BDSUMA, BDPROMEDIO, BDCONTAR ....................................................................................... 40

TABLAS DINÁMICAS ...................................................................................................... 43

MS-QUERY ..................................................................................................................... 46

MACROS ......................................................................................................................... 53

EL GRABADOR DE MACROS ........................................................................................................... 54

EJECUTAR UNA MACRO ................................................................................................................. 56

MÉTODO ABREVIADO ................................................................................................................ 56

FICHA PROGRAMADOR, OPCIÓN MACROS ............................................................................... 56

EL LENGUAJE VBA .......................................................................................................................... 57

EL EDITOR DE VISUAL BASIC ...................................................................................................... 57

DESCRIPCIÓN DEL LENGUAJE .................................................................................................... 59

COLORES DISTINTIVOS .............................................................................................................. 61

ANÁLISIS DE LA MACRO ANTERIOR ........................................................................................... 61

OTRAS INSTRUCCIONES DE MACROS ............................................................................................ 63

ESTRUCTURAS DE CONTROL ......................................................................................................... 67

IF ................................................................................................................................................ 68

SELECT CASE .............................................................................................................................. 68


5/69




Introducción

El objetivo final de cualquier curso de MSExcel 2007 avanzado es construir macros que permitanautomatizar tareas frecuentes y complejas.

Sin embargo, es importante tener en cuenta que la mejor macro no siempre es una macro:

Esto implica que se debe apartar la idea ( que muchas personas tienen ), de considerar las macros comoherramientas casi mágicas con las que podemos conseguir resultados extraordinarios. Ya estas personas tenderán a tratar de resolver cualquier tarea que parezca compleja por medio de macros.

Éste es un lamentable error, ya que en la mayoría de las ocasiones existen herramientas deMSExcel que pueden realizar nuestros requerimientos en forma más sencilla, rápida y eficiente quesi se construyera una macro.

Por esto, en una primera etapa nos abocaremos al estudio de algunas herramientas que nosevitarán la construcción de algunas macros en el futuro.

También se verá, en una segunda parte, el proceso de construcción de macros y su utilidadpráctica para automatizar las tareas más comunes y complejas.

Para efectos prácticos se entenderá una secuencia de instrucciones separados por“/” como la secuencia de instrucciones en un menú. Así si se señala: “ejecute de la ficha Insertar/Nombre/Crear”

es equivalente a “ ejecute de la ficha principal Insertar , dentro de éste seleccione la opción Nombre yluego en éste Crear”.


6/69




Consolidar

Es frecuente en MSExcel encontrar libros que contienen una misma tabla repetida en varias hojas.Estas tablas es la información de detalle que luego servirá para construir una tabla resumen.También es posible que las tablas a partir de las cuales se construirá el resumen se encuentrenubicadas en la misma hoja.

En ambos casos podemos hacer uso de la función Consolidar, la que nos permite generar una tablaresumen a partir de dos o más tablas de detalle. El único requisito para usar esta función es quelas tablas que contienen los datos a resumir tengan idéntica estructura, tanto en columnas como

en filas.

Explicación desde el ejemploa) Construya la siguiente tabla en la Hoja 1. Luego cópiela a las Hojas 2 y 3, cambiando

las cifras:

Entonces debemos ubicarnos en la Hoja 4, en la celda A2, ya que es allí dondequeremos que comience la tabla consolidada (o resumen).

b) Enseguida ejecutar de la Ficha Datos / Consolidar , lo que hará aparecer el cuadroque se muestra a continuación:


7/69




Ilustración 1 Cuadro de Diálogo Consolidar

Una vez que aparece este cuadro, los pasos a seguir son los siguientes:

c) Seleccionar la función con la cual se construirá la tabla resumen (Suma, Contar,Promedio, Mínimo, Máximo, etc.)

d) Seleccionar la casilla “ Referencia”, y luego marcar el rango A2:C9 en la Hoja1 y

presionar el botón “ Agregar ”. Repetir este paso con las Hojas 2 y 3.

e) En la sección “ Usar rótulos en”, seleccionar “Fila superior”y “Columna izquierda” f) Seleccionar la opción “Crear vínculos con los datos de origen” g) Presionar “Aceptar”

Las opciones “Usar rótulos en” le indican a MSExcel que dichos datos no deben seroperados pero sí copiados a la tabla resumen. La opción “Crear vínculos con losdatos de origen” permite que la tabla resumen se actualice automáticame nte cadavez que se efectúa un cambio en los datos base. Esta opción, sin embargo, no puede

ser utilizada cuando los datos de origen se encuentran en la misma hoja donde sepretende dejar la tabla resumen.

Luego de estos pasos, en la Hoja 4 aparecerá una tabla de la misma estructura quela anterior, pero cuyos datos consistirán en el resultado de la función que hayamosaplicado a los datos de origen, y cuyo aspecto es el siguiente:

Los números de las filas de MSExcel se encuentran saltados (2, 6, 10, 14, etc.), loque indica que existen filas que no se están mostrando:


8/69




Al costado izquierdo de los números de fila, encontramos además unosbotones con el signo “ +” 1.Al presionar el primero de estos botones, encontrará que aparecen las filas3, 4 y 5 que estaban ocultas, y las que contienen el detalle de lo queestamos viendo en la fila 6.

Los botones numerados 1 y 2 que se encuentran al lado izquierdo de lasfichas de las columnas, nos indican el nivel de detalle que podemosvisualizar, siendo el número 1 sólo el resumen, y el número 2 el detalle delos datos.

Todo esto que se ha mencionado en el párrafo anterior sólo es visible si la tabla consolidada fueconstruida en una hoja distinta a los datos de origen y si seleccio namos la opción “Crear vínculoscon datos de origen” al momento de consolidar.

Si deseamos agregar o quitar una referencia a las que están consideradas en la tabla resumen,solamente se debe ubicar en la primera celda de la tabla resumen, volver a seleccionar la opciónConsolidarde la ficha Datos y se verá, en la casilla “ Todas las referencias” los rangos que estánconsiderados en la consolidación. Para eliminar, se debe seleccionar el rango de la lista y luegopresionar el botón “ Eliminar ”.

Para agregar, basta con seleccionar un nuevo rango y luego presionar “ Agregar ”.

1 Interprete “+” como “para ver más”


9/69




Opciones de esquema

Al usar la herramienta Consolidar (también en Subtotales) habrá notado que aparecen algunosbotones a la izquierda de los títulos de fila, y en la parte superior izquierda de la hoja, botones denivel de esquema, que en el ejemplo anterior van desde el 1 al 2.

Los botones de esquema, por lo general, sólo son aprovechados por los usuarios cuando utilizanlas dos herramientas que los generan, sin saber que pueden ser obtenidos manualmente, alsolicitarlo a MSExcel.

En la planilla siguiente, utilizaremos estas opciones:

Aquí, por ejemplo, (en la última columna) tenemos un Total Final que, en su cálculo, depende detodas las columnas numéricas que están a la derecha de Fecha.

Por lo tanto, podríamos desear poder ocultar las columnas intermedias para ver un resumen,(cuando estimemos conveniente ) consistente en sólo el Total Final junto al día de la operación.

De la misma forma, podemos querer ocultar el detalle diario para ver, solamente, los totales deese día.


10/69




Crear Esquemas

Para crear esquemasa) Seleccionar en primer lugar las filas o columnas que constituirán un grupo de

esquema. En este caso seleccionar las filas desde 4 hasta 9.

b) Seleccionar la FichaDatos /Agrupar . Obtendremos el siguiente resultado:

Observe que junto a la fila 10 aparece un botón de Esquema que actualmente tieneel signo menos (-)2. Al presionar ese botón, el detalle del día 22 se oculta, y MSExcelsólo nos muestra el Total Día que nosotros hemos calculado.

2 El signo menos [ - ] interprételo como “para ver menos”


11/69




c) Repita el mismo procedimiento para las filas 11 a 16 para obtener el resultado quese muestra en la imagen siguiente:

d) Ahora podemos seleccionar desde la fila 4 a 17 (es decir, el detalle de ambos días,más los subtotales de cada uno de ellos) y volver a seleccionar la fichaDatos/Agrupar Con esto obtendremos en un sólo grupo todo el detalle, peroademás se completan los niveles de esquema en la parte superior hasta llegar alnúmero 3:

Tal como en las herramientas de MSExcel que usan esquemas, aquí el nivel 1muestra solamente el Total General, el nivel 2 muestra los subtotales, y el nivel 3muestra todos los detalles.

Ahora bien, en las columnas la situación es un poco más compleja: Monto, Tasa y Plazo son datosque, si bien están relacionados conceptualmente, son completamente independientes entre ellos:

La Cuota está calculada en base a la función financiera PAGO, que utiliza comoparámetros los tres valores anteriores.ElTotal parcial se obtiene de multiplicar la cuota por el plazo.


12/69




El monto del Seguro es un 2% del monto original,ElTotal Finales la suma del Total más el Seguro.

Como puede ver, no existe una relación como en las filas, donde existen detalles que simplementeson sumados en subtotales y luego en un total general.

Agruparemos los datos de las columnas de acuerdo a criterios de orden personal, que nonecesariamente son matemáticos.

En primer lugar, construiremos un grupo con el detalle de Monto, Tasa y Plazo y Cuota, quedebiera verse resumido en el Total. Para esto:

a) seleccionar las columnas desde B hasta E, como se muestra en la figura:

Note que, al estar centrado dentro de la planilla el título “Créditos Colocados”, MSExcel nopermite seleccionar un grupo de columnas. Para solucionar esto es necesario seleccionar el título,y a través de la ficha Inicio , deshabilitar la casilla “Combinar celdas”.

Una vez seleccionadas las columnas, debe ir a la ficha Datos /Agrupar . El resultado debiera sersimilar al de la imagen siguiente:

Como puede observar, ahora:

las columnas desde B hasta E se encuentran agrupadas.Sobre la columna F hay un botón de esquema que permite ocultar dichas columnas.Sobre los títulos de filas han aparecido dos botones de niveles de esquema.

Ahora construiremos un segundo grupo que permite ocultar los detalles para dejar a la vista sólo

el Total y el Total Final.Si seleccionamos las columnas Total y Seguro, y luego seleccionamos la opción de la fichaDatos/Agrupar , obtendremos un resultado indeseado:


13/69




Al no existir una relación matemática entre las columnas como la que se da entre las filas, MSExcelasume que lo que deseamos hacer es extender el esquema recién construido para que abarquedos columnas más.

La única forma de solucionar el problema es desagrupar (cortar) el grupo construido. Para ello:

a) Seleccionar la columna en la que se quiere desagrupar (cortar) el grupo. En nuestroejemplo, se trata de la columna F.

Seleccionar dentro de la ficha Datos/ Desagrupar .

Una vez seleccionada la opción y presionado Aceptar , obtendremos el siguiente resultado:

Ahora, tal como deseábamos al comienzo, tenemos en un grupo las celdas que determinan elTotal, y en otro el Seguro. Al ocultar los detalles, obtendremos:

Borrar EsquemasPara eliminar esquemas que se han creado, basta con seleccionar las columnas o filas incluidas enel grupo a eliminar.

Una vez realizado esto, debe ir a la ficha Datos /Desagrupar . Con esto, el grupo desaparece, junto alos botones de esquema que MSExcel había generado.


14/69




Optimizando usando Buscar Objetivo y Solver

Dos herramientas de gran utilidad en la agilización de cálculos complejos son

Buscar ObjetivoSolver

Solver es una herramienta más compleja que Buscar Objetivo.

Buscar ObjetivoLa herramienta Buscar Objetivo permite alcanzar un valor determinado de una celda (que contieneuna fórmula) modificando el valor contenido en otra celda de la que tiene dependencia, ya que alestar relacionada directa o indirectamente, MSExcel por medio de iteraciones puede una formulao función, con la primera.

Para entender el funcionamiento de esta herramienta se desarrollará el siguiente ejemplo:

Explicación desde el ejemploa) Construya la tabla siguiente, calculando en la celda E3 el promedio, por medio del

Asistente de Funciones.

b) Posiciónese en la en la celda E3, donde esta contenido el promedio de costoregional, y ejecute de la ficha Datos/Analisis Y Si/Buscar objetivo. Esto haráaparecer el siguiente cuadro de diálogo:


15/69




Ilustración 2 Cuadro de Diálogo BuscarObjetivo

c) En Definir la celda, haga un click en la celdade la planilla que desea que tome undeterminado valor.

d) En “Con el valor” escriba el valor que deseaque tome esa celda. (En este caso, elobjetivo es hacer que el promedio llegue a4000, por lo que debemos escribir ese valoren la casilla)

e) En “ para cambiar la celda” haga un click en la que celda que se desea tome un valordeterminado. En este ejemplo, se debe hacer click en la celda B8, que es la quecontiene el costo de la Región 5.Con esto buscamos cambiar el valor de la celda objetivo, E3, desde 4.420,9 a 4.000.Pero este ajuste lo realizaremos cambiando el valor de la celda B8, cambiando(bajando) su costo.

f) Al presionar Aceptar, obtendremos el siguiente cuadro:Se puede ver que el valor de la Región 5 ha variado, para ajustar el promedio a4.000. El cuadro de diálogo que vemos en la figura anterior nos da dos opciones: Aceptar y Cancelar .

Al Aceptar , Buscar Objetivo cambia definitivamente el valor de la celda B8 por elnuevo valor, dejando el promedio en 4.000.


16/69




AlCancelar , volvemos a los valores originales.

Una variación del ejercicio anteriorAhora bien, ¿qué ocurre si no sólo deseamos cambiar el valor de la Región 5, sino que además elde las Regiones 7 y 10? ¿Qué ocurre si, deseando cambiar el valor de esas tres celdas, queremos

además que el valor de una de ellas no supere ni baje de un valor determinado? Para este tipo deproblemas, debemos utilizar Solver .

SolverComo se mencionaba en el párrafo precedente, Solver es una herramienta que cumple similaresfunciones que Buscar objetivo, pero es más compleja, permitiendo solucionar problemas como losdescritos más arriba.

Puede ser, sin embargo, que en algunos casos no aparezca la opción Solver en la fichaHerramientas. Esto es porque no se ha instalado la opción. Solver es un Complemento que por

defecto está desintalada por ser una herramienta de carácter avanzado.

Instalación de Complemento SolverPara instalar Solver En estos casos se debe seleccionar la opción Complementos en la fichaHerramientas, y seleccionar la casilla Solver. Después de esto, la opción Solver estará disponible.

Uso de SolverPara ver el funcionamiento de esta herramienta, construiremos la siguiente tabla:

Ilustración 3 Ejemplo aplicado de Solver

Esta tabla contiene las siguientes formulas, enlas siguientes celdas:

Valor de Venta =B4*B19

Costo de Venta =B4*B20

Ganancia Bruta =B6-B7

Total Gastos =SUMA(B10:B12)

Ganancia Neta =B8-B13

Margen de Ganancia =B15/B8


17/69




Lo que se desea es:

Ajustar el margen de ganancia de 8% a 12% (Objetivo),Cambiando los valores de las celdas “Unid. Vendidas”, “Sueldos” y “Publicidad”(variables o como lo señala MSExcel “Celdas cambiantes”).

Para resolver esto:a) Seleccionar la celda B17 (celda activa), que es la que contiene el valor que

deseamos cambiar, y que está relacionada con las celdas ya mencionadas 3.b) Seleccionar la ficha Datos/Solver , lo que hará aparecer el siguiente cuadro de

diálogo:

Ilustración 4 Cuadro de Diálogo Parámetros de Solverc) En la casilla Celda objetivo confirme la celda cuyo valor desea modificar (si la

seleccionó previamente debería ser la B17.d) En la casillaValor de la celda objetivo dispone de las opciones “Máximo”, “Mínimo”

y “Valores de:”. Aquí debe seleccionar la opción que más se ajuste a la resolucióndel problema para el ejemplo es “Valores de”.

e) En la casilla Cambiando las celdas se debe hacer click en las celdas que deseacambiar para obtener el valor objetivo.

Cada vez que selecciona una celda, debe presionar ";" lo que indicará que sonceldas separadas. También es posible seleccionar, si fuese necesario, un rangocompleto.

f) Estos son los datos mínimos necesarios para el funcionamiento de Solver, por loque se debe presionar Resolver. Esto hará aparecer el siguiente cuadro:

3 Esta selección sólo ayuda a que esté preseleccionada en el cuadro de diálogo Solver


18/69




Ilustración 5 Cuadro de Diálogo Resultados de Solver

El mensaje de “Resultados de Solver” indica que los datos de la planill a hancambiado para ajustar el valor pedido.

La opción Utilizar solución de Solverhará que los antiguos valores seanreemplazados por aquellos que permiten que nuestro margen de ganancia sea

12%. Es decir aplica los valores que Optimizan la funciónLa opción Restaurar valores originales hará que no sean tomados los valores

que Solver ha entregado como solución, lo que implica que los valoresantiguos (y por ende, el margen de 8%) se mantengan.

g) Ambas opciones deben ser ratificadas por el botón Aceptar. Además, en amboscasos puede optar a obtener cualquiera de los informes que Solver genera (verpunto “0 Informes” página 19).

Puede utilizar el botón Guardar Escenario, para combinar Solver con el Administrador de Escenarios (ver , herramienta que será vista más adelante.

RestriccionesUna de las grandes diferencias entre Solvery Buscar objetivoes que Solverpermite darle aMSExcel restricciones que limiten los cambios que hará a las celdas.

En nuestro ejemplo, podemos pedirle a MSExcel que deseamos modificar la celda margen deganancia a 12%, cambiando el precio del producto, el cual no debe, sin embargo, superar los 42.

Explicación desde el ejemploPara hacer esto, en la etapa de definición de parámetros a optimizar (Ilustración 4 Cuadro de DiálogoParámetros de Solver página 17) :

a) Presionar el botón Agregar , en la sección “Sujetas a las siguientes restricciones”, loque hará aparecer el siguiente cuadro de diálogo:


19/69




Ilustración 6 Cuadro de Diálogo Agregar Restricción

b) Indicar en Referencia de la celda la celda a la cual desea poner restricción.c) Indicar el comparador lógico. Dispone de:

>= Mayor que

= Igual a


20/69




Reporte: Muestra una lista con la celda objetivo y las celdas ajustables con susvalores originales y sus valores finales, las restricciones y la información acerca delas mismas.

Informe de Sensibilidad

Facilita información acerca de la sensibilidad de la solución a que se realicen pequeños cambios enla fórmula definida en el cuadro Definir celda objetivo del cuadro de diálogo Parámetros deSolver o de las restricciones. No se genera este informe para los modelos que tengan restriccionesenteras.

En modelos no lineales, el informe facilita los valores para las gradientes y los multiplicadores deLagrange. En los modelos lineales, el informe incluye costos reducidos, otros precios, coeficientede objetivos (con aumentos y disminuciones permitidos) y rangos de restricciones hacia laderecha.

Reporte : muestra el gradiente reducido (precio sombra) de cada variable y losmultiplicadores de Lagrange (indicador de holgura de restricción) para cadarestricción

El informe de LímitesEl informe de Límites es válido sólo cuando hemos sujeto alguna celda a restricción. En este caso,este informe nos muestra cuál habría sido el valor de la celda objetivo de cumplir con el límiteinferior y/o superior que hubiésemos puesto como restricción.

Reporte: Muestra una lista con la celda objetivo y las celdas ajustables con susvalores correspondientes, los límites inferior y superior de cada variable que hacen

posible una solución factible, así como los valores del objetivo. No se genera esteinforme para los modelos que tengan restricciones enteras. Los límites inferior ysuperior son los límites de cada variable que hacen una solución factible (quecumpla las restricciones)


21/69




Escenarios Posibles con Administrador de Escenarios

El Administrador de Escenarios nos permite manejar diversas combinaciones para una o variasceldas, para ver su efecto sobre una o varias celdas relacionadas a través de formulas. Además,podemos definir diversos escenarios que afecten a celdas diferentes.

Para revisar esta herramienta, utilizaremos la tabla que vimos en el capítulo dedicado a Solver, conel margen de ganancia en 8%.

Para comenzar a utilizar esta herramienta, debemos dirigirnos a la ficha Datos/ Análisis Y Si

/Administrador de Escenarios , la que hará aparecer el siguiente cuadro de diálogo:

Ilustración 7 Cuadro de Diálogo de Administrador de Escenarios

Por medio del botón Agregar se comienzan a definir los nuevos escenarios, a través del siguientecuadro de diálogo:


22/69




Ilustración 8 Cuadro de Diálogo de Administrador de Escenarios – Agregar Escenario

En Nombre del Escenario debemos señalar el nombre que identificará el nuevo

escenario.En Celdas cambiantes debemos definir las celdas o rangos que cambiarán susvalores.En Comentarios podemos hacer una breve descripción del escenario, para su mejorcomprensión a futuro. Para el ejemplo (Ilustración 3 Ejemplo aplicado de Solverpágina 16) las celdas cambiantes serán B10:B12 (prescindiendo del número de unidadesvendidas)

Al presionar Aceptar tendremos acceso al siguiente cuadro, donde establecemos los valores quetomarán las celdas bajo ese escenario:

Los valores que aparecen son los que actualmente contiene la celda. Deberán ser modificados de

acuerdo a los valores que deseamos que tome nuestro escenario. Estas casillas sólo aceptanvalores, y no referencias.

Podemos repetir los pasos anteriores para crear más de un escenario con el fin de analizarmúltiples combinaciones de datos.

Si lo que se desea es modificar un escenario ya existente, se debe seleccionar dicho escenario en lalista que nos entrega el Administrador, y enseguida presionar el botón Modificar. MSExcel nosmostrará el cuadro que indica las celdas cambiantes con sus valores (ver figura anterior).


23/69




Si, por el contrario, se desea eliminar un escenario, se debe seleccionar este escenario y luegopresionar el botón Eliminar.

Solver y el Administrador de EscenariosVimos que Solver, al momento de hallar una solución, nos ofrecía la opción “Guardar Escenario…”.

Al ejecutar dicho botón, agregaremos un escenario al Administrador, cuyas celdas cambiantesserán las que hayamos señalado a Solver, y sus valores, los que Solver hubiese encontrado comosolución para ajustar la celda objetivo.

El nombre de este escenario será “ Solución de Solver ”.


24/69




ValidaciónEn MSExcel es posible condicionar el ingreso de datos dentro de una planilla, de acuerdo a ciertoscriterios. Estas condiciones se aplican por medio de una Validación.

Configuración de una validaciónPara configurar las opciones de validación, basta con ubicarse en las celdas a las cuales queremosestablecer condiciones en su uso. A continuación, debe seleccionar la ficha Datos/Validación .Aparecerá el siguiente cuadro de diálogo:

Ilustración 9 Cuadro de Diálogo Validación de Datos

El Cuadro de Diálogo anterior, permite establecer las condiciones que impondremos en la celdacada vez que se ingresen datos en las celdas seleccionadas.

La opciónCualquier valor , que viene por omisión, implica que las celdas seguirán funcionandocomo lo hacen normalmente en MSExcel. Es decir, aceptarán cualquier valor (incluidos textos) quedeseemos ingresar.

La opciónNúmero entero sólo permite ingresar números, los que no pueden ser fraccionarios (elnúmero 5,42, por ejemplo, no estaría permitido). En caso de seleccionar esta opción, debeestablecer otras restricciones, como el número mínimo, máximo, o el intervalo dentro del cualdebe encontrarse el valor para ser aceptado.

Decimal permite ingresar números que contengan decimales. Al igual que en el caso anterior,debemos establecer el intervalo dentro del cual debe encontrarse el número para ser aceptado.


25/69




La opciónLista nos permite establecer una lista de posibles valores (limitar los datos a una lista),que se debe indicar como rango, y luego, en la celda para la cual establecimos esta validación,genera una lista desplegable que permite seleccionar el elemento a ingresar.

Explicación desde el ejemplo

En el ejemplo siguiente:

Lista: se muestra una pequeña planilla que contiene los departamentos de unaempresa,Celda a aplicar Validación: la celda C1 queremos que el usuario sólo pueda ingresaruno de los departamentos existentes ( los de la lista anterior):

a) Al estar la lista ya construida, sólo necesitamos ubicarnos en la celda que vamos avalidar; en nuestro caso, C1. Luego seleccionamos la opción en la fichaDatos/Validación . Aparecerá el cuadro de diálogo ya visto, en el cualseleccionamos la opción Lista:

Ilustración 10 Cuadro de Diálogo Validación de Datos. Ejemplo

Como puede ver, en el cuadro de diá logo aparece la casilla “Origen”. En ella debemos ingresar el

rango en el cual se encuentran los componentes de la lista que permitiremos en esta celda, que ennuestro caso corresponde al rango A2:A5.

A la derecha, puede ver las casillas “Omitir blancos”, que al estar seleccionada, acepta el ingresode celda vacía como válida. La casilla “Celda con lista desplegable”, al estar seleccionada, mostraráuna lista desplegable en la celda para que podamos seleccionar el ítem de la lista. Una vezestablecidos los parámetros, al presionar activar la celda C1 mostrará un botón de lista que alpresionar, se verá así:


26/69




La opción de validaciónFecha sólo permitirá datos de este tipo. Al seleccionar esta opción se debeestablecer un intervalo. Lo mismo ocurre cuando seleccionamos la opción Hora.

Longitud del texto nos permite establecer cuál será el tamaño mínimo o máximo que debe tenerun texto para ser aceptado (medido en caracteres) o bien, el intervalo dentro del cual debeencontrarse la cantidad mencionada.

MensajesLas opciones de validación de MSExcel permiten dos tipos de mensajes: el mensaje de entrada y el

mensaje de error.

El mensaje de entrada debe ser establecido en la ficha “Mensaje entrante” en el cuadro de diálogode Validación.

Este mensaje puede incluir cualquier frase o palabra que el usuario establezca, y será mostradocada vez que se seleccione la celda.

Habitualmente se utiliza para incluir mensajes de advertencia que le indiquen al usuario el tipo dedato que se espera introduzca.

Los mensajes de error, que se establecen en la ficha “Mensaje de error” del cu adro de diálogoValidación, nos permiten establecer qué mensaje le será mostrado al usuario cada vez que ingreseun dato no permitido. Al igual que el anterior, este mensaje admite cualquier texto que queramosfijar.

En caso de no establecer ningún mensaje de error, MSExcel usa el mensaje que trae incorporado,que se muestra a continuación:

Ilustración 11 Validación - Ejemplo de Cuadro de error en Validación de Datos

El mensaje anterior se generó porque el texto ingresado en la celda C1 no forma parte de la listaA2:A5 que configuramos inicialmente


27/69




Como ha podido ver, las opciones de validación pueden ser de mucha utilidad cuando necesitamosque los datos de nuestros usuarios respondan a ciertas características y no sean ingresados demanera arbitraria. Además, permiten evitar errores en las fórmulas, al obligar al usuario a ingresarnúmeros que sean válidos para los cálculos ingresados en nuestra planilla.


28/69




Formato Condicional

Un formato condicional es una propiedad que se le da a la celda que consiste en que se puedepersonalizar un formato dependiente del valor contenido en alguna celda u condición lógica queresulte de una función.

El formato se limita a las cualidades de formato celda, por lo que la altura de fila o ancho decolumna no son considerados

Aplicación de un formato condicionala) Seleccione las celdas cuyo formato condicional desee agregar, modificar o quitar.b) En la ficha Inicio, haga clic en Formato condicional.

Dentro de las alternativas aparecen para números (entre, mayores, menores, etc)


29/69




Para los que estan en los rangos superiores o inferiores

Para generar “barras” dentro de la celda

Para incluirle iconos


30/69




O para agregar reglas personales


31/69




Manejo de Datos en MSExcel

El manejo de datos en MSExcel está referido principalmente a la posibilidad de manipular, para suanálisis, bases de datos. Como base de datos consideraremos aquellas tablas que estánorganizadas en columnas, a las que llamaremos campos. Las filas representan grupos de datosreferentes a un mismo ítem, a los que llamaremos registros. Dentro de las opciones de manejo dedatos de MSExcel, revisaremos principalmente Subtotales, Filtros, funciones de Búsqueda,Formulario y Tablas Dinámicas.

FiltrosLos filtros son herramientas de manejo de datos que nos permiten seleccionar información dentrode una base, para su análisis. Dentro de los filtros, existen dos tipos que difieren en suscaracterísticas y manejo:

Autofiltro (Curso Intermedio)Filtro Avanzado, que es lo que revisaremos a continuación.

Filtro AvanzadoFiltro Avanzadonos permite, al igual que Filtro, seleccionar registros en una tabla de MSExcel. ElFiltro Avanzado, entre otras mejoras, cuenta con:

La posibilidad de rescatar el resultado de lo filtrado y copiarlo en otro lugar dellibro.Además es posible seleccionar sólo algunos campos o columnas del total original.

Es atractivo en la potencialidad de rescate y flexibilidad en el manejo de datos, sin embargo, cabeseñalar que su manejo no es tan fácil, como en el caso del Autofiltro.


32/69




Para ver el funcionamiento de esta opción, construya la tabla siguiente:

En dicha tabla, la información está contenida encuatro columnas o campos: Parte, Precio, Empresay Localidad, conteniendo un total de 13 registros.

Enseguida encontramos los mismos encabezadosque en el caso anterior. Pero estos no contienenregistros, sino solamente el texto “>2200” bajo elrótulo “Pr ecio”. A esta área la llamaremos rangode criterios.

A continuación encontramos solamente dos de los

cuatro encabezados: Parte y Empresa. Esta área esconocida como rango de destino.

La opción Filtro Avanzado se encuentra en la fichaDatos/ Filtro Avanzado . Al seleccionar estaopción, tenemos acceso al siguiente cuadro dediálogo:

Ilustración 12 Cuadro de Diálogo Filtroavanzado

Acción:La primera opción que encontramos es la “ Acción ” que el Filtro va a realizar. Las posibilidades son:

filtrar la lista sin moverla a otro lugar (que es lo que hace el Autofiltro),copiar a otro lugar. Permite dejar intacta nuestra base original y obtener en otrolugar el resultado del filtro (rango de destino).

INFORME DE VENDEDORES

Parte Precio

Empresa

Localidad

123 1.230 Log Madrid213 2.190 Log Madrid126 3.425 Car Madrid134 3.490 Log Sevilla142 3.555 Car Sevilla150 3.620 Car Parla158 3.685 Mun Parla166 3.750 Mun Sevilla114 3.280 Car Madrid218 1.221 Log Sevilla230 1.230 Log Parla196 1.230 Log Parla140 2.190 Mun Sevilla

Parte Precio

Empresa

Localidad

>2200

Parte Empresa


33/69




Rango de ListaUna vez que hemos seleccionado la segunda acción, debemos indicarle el Rango de la Lista . Paraindicarlo con el puntero podemos presionar el botón que contiene la flecha diagonal de color rojo,lo que minimizará el cuadro de diálogo, permitiéndome seleccionar el rango.

Rango de criteriosA continuación se debe indicar el rango de criterios. Este rango está compuesto , en todos loscasos, por un encabezado que debe ser copia fiel del encabezado de la base , más los criterios quequeremos que MSExcel use para filtrar.

Estas son las dos líneas que encontramos a continuación de la base. En este caso, el único campoque contiene un criterio para filtrado es el campo “Precio”, del cual sele ccionaremos sólo aquellasPartes cuyo valor sea inferior a 2.200.

Copiar a otro lugarSi en la acción seleccionamos la opción “ Copiar a otro lugar ”, debemos indicar en la tercera casillael lugar de destino del filtro. Este rango está señalado por la repetición de los encabezados de lascolumnas. Pero, a diferencia del rango de criterios, éste no necesariamente debe incluir todas lascolumnas ni deben estar en el mismo orden. Solamente debe incluir los encabezados de lascolumnas que queremos que el filtro rescate, y en el orden que deseemos que las presente. Ennuestro ejemplo, MSExcel sólo nos mostrará las columnas Parte y Empresa.

Podemos hacer uso, también, de la casilla “Sólo registro únicos”, la que hará que MSExcel nomuestre los registros seleccionados y repetidos. Esto es que en el caso de mostrar como Rango deLista sólo los campos Empresa y Localidad, mostrará las combinaciones únicas de Empresa-

Localidad. Listado mucho menor a los 213 registros.Es importante destacar que el rango de criterios debe tener exactamente la misma descripción deencabezados que la base original, independiente de si asignamos o no algún criterio a todos loscampos. Este rango, además, presenta la posibilidad de usar dos criterios. Así, por ejemplo:

Parte Precio

Empresa

Localidad

>2200 Madrid

o bien:

Parte Precio

Empresa

Localidad

>2200

Madrid


34/69




Caso 1En el primer caso, por estar “>2200” y “Madrid” en la misma fila, MSExcel mostr ará aquellosregistros cuyo

{ precio sea mayor a 2.200 }Y {cuya localidad sea Madrid }.

Caso 2En el segundo caso, al estar en filas distintas, MSExcel mostrará aquellos registros cuyo{precio sea mayor a 2.200 }O {cuya localidad sea Madrid }.

Como se puede ver:

al poner los criterios de filtrado en la misma fila, MSExcel los considera como un Ylógico, donde será necesario que el registro cumpla con ambas condiciones para serincluidos en el resultado. Poner los criterios en filas separadas, en cambio, hará queMSExcel los tome como un O lógico, debiendo un registro cumplir con un sólo de losdos criterios para aparecer en el resultado.

Funciones de “Formulario” Pese a lo equívoco del nombre, estas funciones de MSExcel permiten administrar una base dedatos, tanto para agregar o eliminar registros como para seleccionar un grupo de registros a travésde algún criterio.

Para acceder a estas funciones debemos estar posicionados en cualquier celda de las quecomponen la base de datos, para luego buscar de la cinta de comandos personalizados

Formulario . Esto nos mostrará la siguiente ventana:


35/69




Como puede verse, las fichas que aparecen en la ventana corresponden a los nombres de loscampos de nuestra base de datos. Las casillas con información que vemos al lado de las fichascorresponden a los datos del primer registro de la base de datos.

La barra de desplazamiento vertical que se encuentra entre los datos y los botones nos permite

movernos a través de los registros que componen la base.El mensaje “1 de 36”, que aparece sobre el botón Nuevo indica que el registro que vemos es elprimero de un total de 36 registros disponibles.

Podemos realizar modificaciones a los datos que componen el registro en forma inmediata.

BotonesNuevo : Este botón nos permite agregar un nuevo registro a la base de datos. Al

presionarlo, aparecerán las casillas de datos vacías para agregar nuevainformación. Este nuevo registro es agregado al final de la base de datoscomo una nueva fila de la planilla.

Eliminar : Tal como su nombre lo indica, este botón elimina el registro activo, que es elque vemos en la ventana Formulario. Esta acción no se puede deshacer.

Restaurar : Si hemos hecho alguna modificación al registro activo y deseamos deshacerla,debemos presionar este botón. Este botón no restaura los registros que fueroneliminados.

Buscar… : Estos botones (Buscar Anterior y Buscar Siguiente) nos permitendesplazarnos hacia delante y atrás entre los registros, una vez que hemosdefinido criterios de selección.

Criterios : Este botón hará aparecer la ventana Formulario con todas las casillas vacías.En estas casillas debemos escribir el criterio por medio del cual se deseahacer un filtrado de la base. Al presionar este botón el resto de los botonescambia de nombre. El botón Borrar y el botón Restaurar limpian las casillas

para criterios. Los botones Buscar… nos permiten desplazarnos a través delos registros. El botón Formulario nos devuelve a la ventana original devisualización de datos.

Cerrar : Cierra la ventana Formulario.Los criterios de selección de registros pueden ser puestos para un sólo campo o para varioscampos simultáneamente. Así, por ejemplo, podemos seleccionar a aquellos que trabajen en eldepartamento Diseño, y en la sección Fax.

Al establecer criterios, la barra de desplazamiento dejará de moverse a través de todos losregistros, para hacerlo sólo a través de los registros que cumplen con los criterios establecidos. Losbotones Buscar… comenzarán a trabajar de la misma forma.

Funciones de BúsquedaLas funciones de búsqueda son pequeñas funciones que nos permiten, a través de un dato, buscarla información correspondiente en la base. Esta información corresponderá a un registro puntualde la base de datos.

Existen dos de estas funciones, Buscar Vertical y Buscar Horizontal, las que trabajan en forma casiidéntica.


36/69




La función Buscar Vertical se utiliza cuando la estructura de la base de datos es la tradicional, esdecir, los campos en sentido vertical, mostrados de izquierda a derecha, y los registros en sentidohorizontal, mostrados de arriba a abajo. Es necesario, para que esta función trabaje de la formaesperada, que el valor a buscar se encuentre en el primer campo o columna, y que esta última seencuentre ordenada de menor a mayor.


37/69




Para ver el desempeño de la función, construya la siguiente tabla:

RUT APELLIDO NOMBRE CARGO

1234-5 PEREIRA NANCY ASISTENTE COMERCIAL

6789-1 CAUDILLO JOSE LUIS REPRESENTANTE1011-2 HURTADO YVONNE REPRESENTANTE

9834-6 FABRE JULIAN REPRESENTANTE

5694-8 LATORRE JEANNINE REPRESENTANTE

5364-8 KAPLAN PATRICIA ASISTENTE COMERCIAL

1012-7 SIYAMA FRANCISCO REPRESENTANTE

6548-9 KAPLAN JULIAN ASISTENTE COMERCIAL

Ordene enseguida la tabla por RUT (Para esto, debe seleccionar la tabla, ir a la fichaDatos/Ordenar , y luego indicar que desea hacerlo por RUT).

Si creó la tabla a partir de la celda A1, escriba lo siguiente a partir de la celda A15:

rut:

apellido:

nombre:

cargo:

En la celda B17 (junto a “apellido:”), escriba lo siguiente:

=BUSCARV($B$15;$A$1:$D$9;2;FALSO)

En la celda aparecerá el texto “#N/A”. Para subsanar este error, escriba el Rut “1234 -5” en la celdaB15. Esto hará que, en lugar de #N/A aparezca la palabra “PEREIRA”.

Podemos ver, entonces, que la función BUSCARV busca un valor en el primer campo de una basede datos, para luego devolver el valor de ese registro en una columna señalada por nosotros. Lasintaxis de la función es, entonces, la siguiente:

=BUSCARV(valor_a_buscar;matriz_de_busqueda;indicador_de_columna;ordenado)

Como se vio en el ejemplo, valor_a_buscar puede ser una referencia (una celda que contiene elvalor), pudiendo también ser un valor determinado. En este último caso, el valor debe ir entrecomillas (por ejemplo, “1234 -5”). Es preferible, para darle mayor dinamismo a la formula, trabajarcon referencias.


38/69




Ahora podemos copiar la formula al resto de las celdas (“nombre” y “cargo”), pero cuidando decambiar el indicador de columna (3 para “nombre” y 4 para “cargo”). Veremos entonces que, junto a los textos, aparecen los datos correspondientes al Rut que hemos anotado en celda B15.Podemos cambiar este RUT, y veremos que la información mostrada en las celdas cambia, deacuerdo al RUT que hayamos ingresado.

En la función antes indicada aparecerá una opción llamada “Ordenado”, éste nos permitirá unamejor búsqueda y entrega de información al momento de usar BUSCARV. En el ejemplo anteriorse uso la opción de FALSO, ya que buscara un valor exacto en la Matriz de Búsqueda, ésta es lamás utilizada, pero en este capitulo veremos el uso de la otra opción, VERDADERO.

Para ver el desempeño de la opción, construya la siguiente tabla:

UBIC.SUELDOBASE BONO

Sucursal 2 $ 693.520


Casa Matriz $ 338.453






Además cree la siguiente matriz de búsqueda

SUELDOBASE BONO

$ 100.000 10%

$ 200.00012%

$ 300.000 15%

$ 400.000 17%

$ 500.000 20%

$ 600.00025%

La finalidad del ejercicio es colocar en la tabla mayor y en la columna BONO el porcentajecorrespondiente dependiendo de su SUELDO BASE.


39/69




Si observa el ejercicio no existen valores exactos para los sueldos base buscados, por lo queBUSCARV deberá entregar los valores más próximos debajo de lo graduado en la tabla menor, estoes por ejemplo:

Si se desea buscar el porcentaje de $190.000 y existe una graduación de 10% para los sueldos entre

$100.000 y $199.999, BUSCARV le dará este porcentaje y no el 20% de la siguiente graduación,independiente que este más cerca del $200.000 que de $100.000.

En la celda C2 (junto a $693.520), escriba lo siguiente:

=BUSCARV(B2;$A$11:$B$17;2;VERDADERO)

En la celda aparecerá el valor correspondiente a este Sueldo Base, o sea, 25%

Para el caso que nuestra base tenga una estructura en filas (las filas representan los campos y lascolumnas los registros), debemos verificar que la fila en la que deseamos buscar datos sea laprimera, y que se encuentre ordenada de menor a mayor. Enseguida, la formula a utilizar es lasiguiente:

=BUSCARH(valor_a_buscar;matriz_de_busqueda;indicador_de_fila)

Como esta base no contiene los campos en columnas sino en filas, el último no corresponde alnúmero de columna, sino a la fila de la cual deseamos extraer los datos.

Funciones de Base de DatosLas funciones de Base de Datos son funciones incorporadas en MSExcel que permiten hacerdistintos cálculos matemáticos a partir de la información contenida en una lista de MSExcel que

tenga estructura de base de datos.

Estas funciones son similares a las funciones normales que utilizamos a diario en MSExcel y quefueron explicadas al comienzo de este manual. Sin embargo, la gran diferencia estriba en laposibilidad de usar criterios que hacen que una suma considere sólo los valores que cumplen condeterminada condición, por ejemplo.

Puesto que no es el objetivo revisar todas y cada una de las funciones de Base de Datos, sólo seconsiderarán las de uso más popular, puesto que de esa forma se revisará la manera de usarlas,que es aplicable a aquellas funciones que no serán analizadas en esta sección.

Rango de CriteriosLas funciones de Base de Datos requieren para su operatoria que exista, además de la base dedatos, una zona donde se encuentre el nombre del campo al cual deseamos aplicar algunacondición, el que debe ser idéntico al nombre del campo en la base de datos (para evitarproblemas con esto, se recomienda copiar el nombre del campo desde la base de datos al rangode criterios). Bajo este rótulo irá el criterio que utilizaremos para realizar nuestros cálculos.

Para revisar estas funciones utilizaremos la planilla mostrada a continuación.


40/69




En la celda H1 de la planilla, escriba o copie el rótulo SALARIO, puesto que sobre ese campoaplicaremos nuestros criterios. Bajo esta celda escriba “>35000”, que es el criterio queutilizaremos.

La parte superior de la planilla debiera verse como muestra la imagen de la página siguiente:

De ahora en adelante, las celdas H1 y H2 serán nuestro rango de criterios. Todas las operacionesque realicemos considerarán sólo los registros que cumplan con la condición “SALARIO mayor que

35.000”.

En estas operaciones pueden utilizarse múltiples criterios para una misma función. En ese caso, elrango de criterios debe construirse de la forma ya indicada, agregando hacia la derecha losnombres de los campos que llevarán criterios.

BDSUMA, BDPROMEDIO, BDCONTARComo ejemplo de cómo operan estas funciones, revisaremos ahora tres casos. En la celda H5obtendremos la suma de los salarios de nuestra base de datos, que sean mayores a 35.000. En lacelda H6 obtendremos el promedio de esos salarios. Y en la celda H7 obtendremos la cantidad deregistros que cumplen con la condición mencionada.

El primer paso, entonces, es ubicarnos en la celda donde queremos aparezca el primer resultado.En nuestro ejemplo, se trata de la celda H5.

Una vez allí, llamamos al Asistente de funciones de MSExcel


41/69




En la sección izquierda del cuadro de diálogo, que muestra las categorías en las cuales estánclasificadas las funciones de MSExcel, seleccionamos “Base de Datos”. Aparecerán, a la derecha,las funciones de dicha categoría, ordenadas alfabéticamente.

Busque y seleccione la función BDSUMA. Al presionarAceptar , se abre el siguiente cuadro de

diálogo, que nos pide los parámetros que esta función requiere.

En la casilla “Base_de_datos” se debe ingresar el rango que contiene la base de datos completa,incluyendo los encabezados de los campos (si ha usado un nombre de área para definir el rango dela base de datos, puede escribirlo en la casilla, recordando no usar comillas). En nuestro caso, elrango de la base de datos es A1:F37.

La casilla “Nombre_de_campo” nos pregunta por el nombre del campo que contiene los valores

que, en este caso, deseamos sumar. El nombre del campo debe ser escrito entre comillas. Si lodesea, también tiene la posibilidad de usar el número que ocupa la columna a calcular dentro de labase de datos. Este número no debe ir en comillas. En nuestro caso, entonces, podemos usarindistintamente el número 5 (puesto que los sueldos están en la quinta columna de la base dedatos), o bien “SALARIO”.

Finalmente, debemos indicar, en la casilla “Criterios”, el rango d e criterios que deseamos utilizar, yque, en nuestro caso, corresponde al rango H1:H2.

La ventana debiera aparecer de la siguiente forma:


42/69




Una vez que hemos completado la información, presionamos el botón Aceptar . En la celda H5debiera aparecer el valor $ 238.800, que es la suma de todos aquellos sueldos mayores a $ 35.000.

Para calcular el promedio de esos sueldos, debemos ubicarnos en primer lugar en la celda H6 yluego llamar al Asistente de funciones. En la categoría “Base de D atos” debemos selecciona r lafunción BDPROMEDIO. Esta función nos pedirá los mismos parámetros que la función anterior, yque, en este caso al menos, son los mismos ya ingresados. Es decir, base de datos es A1:F37, lacolumna a sumar es “SALARIO” o 5, y los criterios se encuentra n en H1:H2. Una vez ingresada lainformación y ya presionado el botón Aceptar , obtendremos el valor $ 39.800.

Para finalizar, realizamos exactamente la misma operación, pero ahora con la función BDCONTAR.A través de ella sabremos el número de personas cuyo salario es superior a $ 35.000 es de 6.


43/69




Tablas DinámicasSi se construye la tabla siguiente, podremos ver que los cálculos que aparecen en la parte inferiorpresentan ciertas dificultades a la hora de ser realizados:

Producto Año

Mes

Ventas Unidades

Vendedor

Región

Lácteos 1992 Ago 4.873 2.730 Buchanan NorteLácteos 1992 Dic 7.686 5.563 Davolio NorteVerduras 1992 Jul 797 3.868 Buchanan NorteLácteos 1992 Jul 7.612 3.656 Buchanan SurLácteos 1992 Jun 5.575 9.970 Davolio EsteVerduras 1992 Oct 8.165 983 Buchanan SurVerduras 1992 Oct 7.191 39 Davolio NorteLácteos 1992 Oct 3.338 1.695 Davolio OesteVerduras 1993 Apr 450 9.265 Davolio EsteLácteos 1993 Dic 2.733 2.790 Davolio OesteVerduras 1993 Dic 7.047 9.888 Davolio OesteLácteos 1993 Ene 6.544 9.550 Davolio OesteVerduras 1993 Feb 4.923 8.160 Davolio SurLácteos 1993 Feb 8.076 3.670 Davolio SurLácteos 1993 Mar 8.751 1.773 Buchanan OesteLácteos 1993 Mar 2.741 6.290 Davolio NorteVerduras 1993 Oct 6.955 8.722 Buchanan EsteVerduras 1993 Sep 2.956 1.242 Buchanan OesteLácteos 1993 Sep 75 3.216 Buchanan Este

Total ventas 96.488Total unidades 93.070 1992 1993Ventas Davolio Norte 17.618 14.877 2.741Ventas Davolio Sur 12.999 12.999Ventas Davolio Este 6.025 5.575 450Ventas Buchanan Norte 5.670 5.670Ventas Buchanan Sur 15.777 15.777Ventas Buchanan Este 7.030 7.030

Como se ve, en el cuadro resumen inferior se pretende conocer las ventas divididas por Vendedor,y a su vez divididas por región. Estas, finalmente, se dividen por año. Estos cálculos, como se

puede imaginar, son bastante complejos y tediosos de realizar, puesto que es necesario irseleccionando celda por celda hasta obtener el resultado buscado. Este procedimiento puedeoriginar que se comentan errores al seleccionar las celdas, los que a simple vista podrían no sernotados, pero luego ser bastante significativos.

En lugar de hacer este tipo de cálculos, entonces, podemos utilizar las tablas dinámicas. Ésta es,seguramente, una de las herramientas más poderosas y versátiles que MSExcel posee para el


44/69




análisis de datos. Además, es bastante sencilla de usar, puesto que cuenta con asistente que nosguía a través de los cuatro pasos necesarios para llegar a nuestra tabla.

Para comenzar a construirla, debemos ir a Insertar/Tabla Dinámica . Aparecerá entonces elsiguiente cuadro de diálogo:

Este es el primer paso de un total de dos, y consiste en indicar el origen de los datos con los cualesse construirá la tabla. La opción por omisión un rango, de habernos ubicado dentro de la base dedatos aparece marcado automaticamente. También se pueden usar datos externos (como unabase de datos Dbase, Foxpro, Etc.), rangos que hayan sido usados con una función Consolidar uotra Tabla Dinámica. Una vez indicado el origen de los datos, se presiona el botón Siguiente >.

Para diseñar la tabla dinámica de toman los campos de la tabla y se van ubicando en los distintossectores fila, columna, datos o valores los elementos que se usarán,

Panel de TablaDinámica

Vista de TablaDinámica


45/69




La sección PAGINA hará que ese campo, por ejemplo Año, sea convertido en una lista desplegable,la que nos muestra los años disponibles. Al seleccionar alguno de ellos, el contenido de la tablacambiará para mostrarnos sólo los datos correspondientes a ese Año. También se incluye, en lalista desplegable, la opción “(T odos)”, que nos mostrará toda la información, independiente delAño.

La sección COLUMNA nos permite ubicar los campos que deseamos que nuestra tabla muestreordenados en columnas. Puede ser ubicado más de un campo en esta sección. De ubicar más deun campo, el primer campo ubicado aparecerá subdividido por el segundo. De ubicar un tercercampo, éste subdividirá, a su vez, al segundo.

La sección FILA funciona exactamente igual que la sección COLUMNA, con la diferencia que losdatos puestos allí estarán ordenados en filas.

La sección DATOS sirve para ubicar los campos que deseamos que la tabla muestre comoresultado o valor.


46/69




MS-Query

Microsoft Query se usa para recuperar datos desde un origen de datos externo.

Puede utilizar Microsoft Query para crear una consulta y recuperar los datos que desee de unorigen de datos externo. Por ejemplo, puede recuperar datos de Microsoft MSExcel acerca de unproducto específico por región. También puede crear una consulta sencilla utilizando el Asistentepara consultas o puede crear una más compleja utilizando las funciones avanzadas de MicrosoftQuery.

Para utilizar Microsoft Query con el fin de recuperar datos externos, debe:Tener acceso a un origen de datos externo Si los datos no se encuentran en su equipo local, esposible que tenga que pedir una contraseña, un permiso de usuario o cualquier otra informaciónacerca de cómo conectarse a la base de datos al administrador de la base de datos externa.

Para trabajar en este capitulo usaremos 2 Hojas de un Libro de MSExcel.

Hoja 1 llamada Datos Personales

Por ejemplo veremos datos como: NOMBRE, CARGO, TELÉFONO Y FAX.

Hoja 2 llamada Datos Trabajo


47/69




Por ejemplo veremos datos como: NOMBRE, DIRECCIÓN, CIUDAD Y PAÍS.

Lo que haremos con MS-Query es unir las dos tablas en una nueva Hoja, deberemos asignar antesque todo Nombres Rango a las tablas de cada una de las Hojas. A la primera la llamaremos Base1(Datos Personales) y a la segunda Base2 (Datos Trabajo).

En una Hoja nueva iniciaremos la opción de MS-Query. Ésta se encuentra en la barra de menúDatos / Obtener datos externos / Nueva Consulta de base de datos ...

Aparecerá la siguiente ventana con opciones para obtener orígenes de datos externos.


48/69




Seleccionaremos la opción MSExcel Files* y luego presionaremos ACEPTAR.

Aparecerá la siguiente ventana donde tendrá que buscar el archivo (libro) que contiene las doshojas con información (datos personales y trabajo). Después de encontrar este archivopresionaremos ACEPTAR.

Veremos el Asistente para Consultas activado para comenzar a trabajar, mostrándonos los doslibros (base1 y base2), recuerde que estos corresponden a los nombres rango que asignamos a lastablas en las 2 hojas.

Veremos en la parte izquierda de esta ventana los nombres rangos con un “+” el que nos permitirási lo presionamos ver los diferentes títulos de la tabla, repetir lo mismo para la base 2.

Seleccionaremos para la base1 los campos NOMBRE, DIRECCIÓN, CIUDAD, PAÍS y de la base2CARGO, TELÉFONO y FAX.


49/69




Para enviar los campos al área de la derecha de la pantalla podemos hacer doble click sobre la

descripción. Luego de seleccionar presionaremos SIGUIENTE .

Podremos observar la siguiente ventana de advertencia que nos indicara que no existe unarelación para las dos tablas que estamos trabajando y que deberemos unirla o relacionarlas

manualmente. Las descripciones o nombre de titulo que nos permitirán la unión o relación será elNOMBRE, única manera de relacionar que para un NOMBRE existe una dirección y que para esemismo NOMBRE existe un cargo.

Presionaremos ACEPTAR para relacionar las dos tablas.


50/69




Como la tabla no esta relacionada nos muestra la unión de filas (registros) de ambas tablas 10 x 10(base1 x base2), esto es 100 filas.

Para que nos entregue la unión de todos los campos o columnas (títulos) debemos relacionar ounir las dos tablas por el campo NOMBRE de la siguiente manera:

Presione NOMBRE de la base1 y sin soltarlo parece en el NOMBRE de la base2, luego suelte el Clicky vera una línea delgada uniendo las dos tablas.

Ahora se verán las filas de información correspondiente a las dos tablas unidad en la consulta oQuery (10 filas de información).


51/69




Por otra parte, antes de insertar la nueva tabla resultante en nuestro nuevo archivo (libro)podemos generar algún criterio especial para estos datos. Si presionar el botón de Ocultar o

Mostrar los Criterios ( ) de la barra de botones veremos que aparecerá una nueva área en laventana de MS-Query.

Aquí seleccionaremos a los clientes que suCARGO sea igual a “Propietario”. Para estoseleccionaremos en el área de criterios el tituloCARGO y luego en la parte de abajo VALORescribirá Propietario.

Veremos que después de escribir y presionar[ENTER] se filtraran las filas correspondiente alos datos del criterio escrito

Por ultimo al cerrar la MS-Query nos preguntara en que celda de nuestro nuevo archivo (libro)deseamos situar los datos provenientes de nuestra consulta.

Área de Criterios


52/69




Seleccionaremos la que viene activada en la ventana Hoja de cálculo existente, en la posición $A$1y luego presionaremos ACEPTAR.

Aparecerá en nuestra hoja los siguientes datos:


53/69




Macros

En palabras sencillas, una macro es una serie de órdenes que el usuario da a Microsoft MSExcel,con el fin de automatizar ciertas tareas.

Estas órdenes están escritas en un código especial, derivado del lenguaje de programación máspotente que posee Microsoft: Visual Basic for Applications. Este lenguaje está orientado a objetos,lo que quiere decir que cada cosa que nosotros vemos en la planilla de cálculo MSExcel es unobjeto que posee ciertas propiedades a las que se pueden asignar distintos valores. Sobre estosobjetos, además, podemos ejecutar diversas acciones.

Así, por ejemplo, un grupo de celdas (rango) es un objeto que, por ejemplo, puede serseleccionado, o bien, puede ser cambiada la fuente (tipo de letra) de esa selección. En el primercaso, se trata de una acción ( seleccionar ) y en el segundo se trata de una propiedad a la queasignamos un valor.

Dicho ahora entonces, en términos más técnicos, una macro es una serie de instrucciones enlenguaje VBA (Visual Basic for Applications), las que consisten en acciones sobre un objeto o en elcambio de alguna de sus propiedades.

Para obtener una macro, sin embargo, no es necesario —en un nivel básico— escribir cada una delas instrucciones de VBA. MSExcel incluye una herramienta para el trabajo de macros quesimplifica tremendamente la tarea. Se trata del Grabador de Macros. Esta herramienta almacena,desde que se activa hasta que se detiene, todas las acciones que realicemos en MSExcel, al tiempoque las va traduciendo a lenguaje VBA. Luego esta macro puede ser ejecutada por el usuario, loque hará que MSExcel repita, en el mismo orden y secuencia, las instrucciones almacenadasdurante el proceso de grabación.

Este sistema de generación de macros es bastante útil cuando no se poseen los conocimientossuficientes, pero al replicar solamente acciones ya realizadas, es absolutamente rígido y bastantelimitado.

Es por estas razones que en un primer momento revisaremos el Grabador de macros, con el fin depoder utilizar esta herramienta para construir, al menos, la base de nuestra macro, para másadelante ver el uso de variables, de controles, cuadros de diálogo y estructuras de control, que nospermitirán darle a la macro toda la flexibilidad que necesita.


54/69




El Grabador de MacrosEn MSExcel existen dos caminos para construir una macro. El primero, y el más sencillo, es utilizarel Grabador de macros de MSExcel. Éste permite ejecutar una serie de pasos, los que sonregistrados para luego ser reproducidos en el mismo orden y secuencia original.

La reproducción de los pasos realizados durante la grabación de la macro es estricta, por lo que sirealizamos n acciones equivocadas antes de realizar lo que deseábamos originalmente, esas n acciones equivocadas se repetirán cada vez que ejecutemos nuestra macro. Dado este métodoparticular de trabajo, se recomienda confeccionar una pauta, tan detallada como sea posible, delas acciones a ejecutar, para evitar errores durante la grabación de la macro y que ésta realiceacciones innecesarias o repetitivas.

Para trabajar con las macros debemos activar la ficha Programador y eso se hacen entrando a lasopciones de Excel y dando el tiquet en “Show Developer tab in the Ribbon”, como se muestra en laimagen

Para conocer el grabador de macros, construiremos una macro que permita dar formato a lostítulos de nuestras planillas.

En la celda A1 de una hoja en blanco escriba lo siguiente:

Bajo este título se encuentra nuestra planilla imaginaria, la cual abarca hasta la columna F. Por lotanto, queremos que el formato del título sea: centrado entre las columnas A y F, letra Comic Sans


55/69




MS4, tamaño 18, cursiva, de color azul, y el fondo de las celdas en color amarillo. Antes de aplicarel formato, entonces, activaremos el grabador de macros de la siguiente forma:

a) Seleccione el rango A1:F1 (que es el rango al que aplicaremos el formato).b) Ejecute de la ficha Herramientas/Macro/Grabar nueva macro.

Aparecerá la siguiente ventana:

En la primera casilla, debemos escribir el nombre que daremos a la macro. Este nombre puede sercualquier palabra que no sea utilizada por MSExcel (por ejemplo, si estamos haciendo una macropara imprimir, no podemos usar el nombre Imprimir, pero sí cualquier otra palabra similar). En lacasilla para método abreviado debemos presionar Control más alguna tecla particular. Esto haráque cada vez que presionemos Control + tecla la macro se ejecute. La casilla “Guardar macro en:”nos entrega las siguientes opciones:

o “Este libro”, lo que hace que la macro sea parte del libro que estamosusando. Esto impide que podamos utilizar la macro cuando este libro seencuentre cerrado.

o “Libro de macros personal”. Este libro se abrirá, en forma oculta, cada vezque iniciemos Microsoft MSExcel, con lo que la macro estará siempredisponible para ser usada en todos los libros.

o “Libro Nuevo”, que ubica la macro en un nuevo libro. Tiene las mismasdesventajas que la primera opción.

No siempre la mejor opción es guardar las macros en el libro personal de macros, puesto que ésteusará espacio en memoria, lo que hará trabajar más lento el equipo, y en ocasiones la macro estáhecha para trabajar precisamente en un libro particular y no es válida para otros libros (porejemplo, una macro que realice complejos cálculos a partir de cierta información particular).

La descripción es un comentario que no tiene más incidencia que, a la hora de ver un listado de las

macros disponibles podamos ver una leyenda que nos permita identificar cada macro.

Una vez ingresada toda la información, presionamos el botón Aceptar . En la barra de estadoaparecerá el mensaje “Grabando”

4 Si esta fuente no se encuentra disponible en su PC, utilice una fuente alternativa.


56/69




Ahora debemos centrar el texto en el rango A1:F1, ejecutar la ficha Inicio /Formato /Celdas , y allí

escoger la ficha Fuente . Debe indicar, entonces, la letra Comic Sans MS, el estilo Regular Cursiva,el tamaño 18 y el color azul. Una vez hecho esto, presionar Aceptar. Enseguida seleccionar, en labarra de herramientas, el color de fondo amarillo. Nuestro rango debiera aparecer así:

En ese momento debemos detener la grabación.

Podemos seleccionar el rango que acabamos de dar formato, ir a la ficha Inicio /Borrar/Formatos .Con esto quedará el texto tal como estaba antes de comenzar a grabar la macro. Volvamos aseleccionar el rango A1:F1 y presionemos Control+ la tecla que hayamos seleccionado en elmétodo abreviado.

Si el formato vuelve a ser el de la figura precedente, nuestra macro está funcionando. ¡Y sinescribir una sola línea.

Ejecutar una macroExisten varias formas de ejecutar una macro, entre las que destacan:

Método Abreviado.

Menú Herramientas, opción Macro.Botón personalizado.Botón en la barra de herramientas.A partir de otra macro.

Método abreviadoEl método abreviado sólo puede ser asignado al momento de iniciar el grabador de macros. Ésteconsiste en una tecla, que presionada junto a Control (también se puede usar la combinaciónControl+Mayús+Letra ) nos permite ejecutar inmediatamente una macro.

Ficha Programador, opción MacrosAl seleccionar dicha opción, aparecerá un cuadro con un listado de las macros con que contamos.Sólo debemos seleccionar la macro que deseamos ejecutar, y luego presionar el botón Ejecutar .


57/69




El lenguaje VBA

Como vimos en la sección anterior, nuestra macro funcionará sin que hayamos escrito una solalínea de comandos (llamada también línea de código). Sin embargo, cada acción realizada durantela grabación, como mencionamos en la introducción, se traduce en una instrucción VBA.

Este lenguaje es una derivación, especialmente creada para los programas miembros de Office, dellenguaje de programación Visual Basic. Por lo tanto, las macros son programas construidos porExcel (cuando usamos el grabador) o por nosotros mismos.

El Editor de Visual Basic


58/69




Ahora bien, estos programas se escriben en una parte especial de Excel llamada “Editor de VisualBasic”. A este se puede acceder a través de la opción Macro de la ficha Herramientas. Al llamarlo,tendremos acceso a la siguiente ventana:

En esta ventana podemos encontrar varias secciones, que se describen a continuación.

La parte superior contiene la barra de Menú del Editor de Visual Basic, que contiene todas lasopciones que podemos utilizar en esta sección. Inmediatamente más abajo encontramos la barrade herramientas, que contiene botones que permiten un acceso más rápido a las mismas opcionescontenidas en la barra de menú.

En la parte superior izquierda encontramos la ventana de proyecto. Cualquier libro de Excel esconsiderado, por el Editor de Macros, como un proyecto. Este proyecto está subdividido en lashojas del Libro (Hoja1, Hoja2, etc.) más un objeto que engloba a las anteriores y que identifica anuestro libro completo (“This Workbook”). En el caso de que nuestro libro contenga macros, estasaparecen dentro de una carpeta, que forma parte del proyecto, y que recibe el nombre de“Módulo 1”. Por lo general, las macros grabadas en una misma sesión se almacenan en un mismomódulo, mientras que las macros grabadas en sesiones posteriores van generando nuevos


59/69




módulos. Sin embargo, para fines de orden, en el caso de proyectos grandes que incluyen muchasmacros, es posible generar los módulos que deseemos y distribuir en ellos nuestras macros (Parainsertar un módulo, simplemente tiene que ir a la ficha Insertar y escoger Módulo).

La ventana que aparece inmediatamente abajo es la que nos muestra las propiedades del objetoque tengamos seleccionado. En la imagen precedente podemos ver que el objeto seleccionado enla ventana “Proyecto” es “Módulo 1”, y en la ventana inferior vemos que su única propiedad es“(Name)” (Nombre), la que actualmente tiene el valor “Módulo 1”, el que puede s er cambiado por

el usuario.

En la ventana de la derecha podemos ver el contenido, en términos de código, del módulo 1, elque consiste precisamente en la macro creada a través del grabador. En esta ventana podemosescribir al igual que en cualquier procesador de palabras básico, utilizando el teclado y algunoscomandos como Cortar, Copiar y Pegar. Para agilizar la escritura de código, el Editor de VisualBasic nos va presentando ayuda a través de una pequeña ventana emergente que contiene losnombres de las instrucciones y su sintaxis:

Descripción del Lenguaje

Como ya se mencionó, el lenguaje VBA está orientado a objetos. Es decir, básicamente susinstrucciones consisten en cambios al valor de una propiedad de un objeto o a acciones realizadassobre ese objeto. Esto determina que las instrucciones del lenguaje hagan referencia en primerlugar al objeto y enseguida a la propiedad o acción. Estas instrucciones se escriben en ordensecuencial, y es de esta misma forma como se ejecutarán. Vale decir, la ejecución ocurrirá de laprimera a la última instrucción pasando por todas las intermedias, a menos que incluyamos ciertasinstrucciones que corten o redireccionen la ejecución. La estructura de sintaxis básica del lenguajees, entonces:objeto.propiedad[.subpropiedad] = valor

Por ejemplo:

ActiveCell.Font.Name = “Times New Roman”

Donde ActiveCell es la celda actualmente seleccionada, Font es la propiedad Fuente (tipo de letra)y Name es la subpropiedad nombre de la fuente, mientras que “Times New Roman” es el v alor queestamos asignando a esa propiedad5.

5 No todas las propiedad se encuentran divididas en subpropiedades, y las que sí los estánno necesariamente poseen las mismas. Esto variará, entonces, de una propiedad a otra.


60/69




Otro ejemplo:

Range(“A1:F1”).Select

Donde Range(“A1:F1”) indica que el objeto es el rango comprendido entre las celdas A1 y F1,mientras que Select es la acción a realizar sobre ese objeto. En este caso, seleccionarlo.

A pesar de lo señalado, existen ciertas instrucciones que tienen su sintaxis propia, la que serárevisada más adelante.

Otras características del lenguaje

Líneas de inicio y términoToda macro, contenida dentro de un módulo, tiene un par de líneas de instrucciones que nopueden dejar de ir. Estas líneas indican el inicio y el término de la macro, y por lo tanto sonabsolutamente necesarias para la ejecución de las mismas.

Estas líneas son las siguientes:

Sub nombre_de_la_macro()

Donde Sub es la palabra clave que identifica el comienzo de nuestra macro. A continuación va elnombre con que identificamos la macro y enseguida un juego de paréntesis que indican que estamacro no necesita parámetros6. Además de esta línea inicial existe una línea final:

End SubEsta línea le dice a Excel que la macro comenzada más arriba ha llegado a su fin.

Líneas de comentariosEn ocasiones es necesario agregar a nuestras macros ciertas indicaciones que nos sirvan anosotros o a otros para identificar ciertos procesos, señalar determinados eventos, etc.

Estas líneas no intervienen en el funcionamiento de la macro y pueden ser escritas en lenguajecorriente. Para que sean comentarios, sin embargo, deben estar precedidas de un apóstrofe („).Este símbolo es el que le señala a Excel que todo lo que viene enseguida y hasta el fin de la líneaes un comentario.

Por ejemplo:

„ Esta macro es un ejemplo

O bien:

Worksheets(“Hoja1”).Select „ Estamos seleccionando la hoja 1

En el primer ejemplo, toda la línea es un comentario, mientras que en el segundo caso, la líneaincluye una instrucción (Worksheets(“Hoja1”).Select) y enseguida el comentario, a partir de unapóstrofe.

6 Además de las macros de comandos (macros que ejecutan y automatizan tareas) existenmacros de función, que realizan el cálculo que el usuario haya estipulado, a partir dedeterminados parámetros. En esos casos los parámetros van en paréntesis.


61/69




Col

Documents

EXCEL AVANZADO 2007.pdf