APLICACIONES ESTADÍSTICAS

USANDO MS EXCEL

ILMER CÓNDOR

ÍNDICE

PRESENTACIÓN

El presente libro tiene por objeto utilizar toda la potencia del programa Microsoft Excel, para resolver problemas de aplicación de la Estadística Descriptiva e Inferencial.

Desde esta perspectiva, no es un libro de Estadística ni tampoco un manual de uso del Excel. Del mismo modo, no se debe entender que sea un compendio de procedimientos ni  un recetario que le indica al lector lo que debe hacer sin saber para qué o por qué.

Los principales temas de la estadística son tratados de manera resumida y aplicados usando las funciones o herramientas del Excel y otros procedimientos elaborados por el autor, mediante el uso de lenguaje de aplicaciones (VBA) disponibles en el Excel, a los cuales se recurre en todos los casos a través de archivos creados con en el mismo programa Excel, todos los cuales son parte inherente al presente libro. Todas las figuras e imágenes y muchos de los datos, mostrados en tablas, han sido generados por el autor, en muchos casos con la ayuda de la función aleatorio.entre(…).

Al enfocar cada nuevo tema, hacemos una breve explicación del mismo, no de su  fundamentación teórica sino de un breve repaso del mismo, con la finalidad de refrescar dicho tema en el lector, a fin de que sepa cuándo y porqué usar una u otra herramienta estadística y cuáles de esas herramientas dispone el Excel, para resolver su problema.

Por estas razones creemos que el presente libro se deberá convertir en una herramienta obligatoria del estudiante de cualquiera de las estadísticas de una institución superior.
 

PARTE I

CAPÍTULO 1

MICROSOFT EXCEL

1.1       Introducción

1.2       Descripción básica

1.3       Algunas herramientas y procedimientos del Excel

1.4       Macros

1.5       El lenguaje Visual Basic para aplicaciones

1.6       Controles y formularios

1.7       Problemas propuestos

1.1.      INTRODUCCIÓN

Tomando como premisa lo dicho en la presentación, en este capítulo nos dedicaremos a realizar una breve exposición del programa MS Excel y de sus principales características, en términos muy generales. Esto lo haremos en la primera sección; en la segunda trataremos de exponer algunas herramientas y funciones del Excel con el propósito de utilizarlos más adelante. En la tercera sección nos dedicaremos al estudio de los macros; en este caso lo haremos con cierto detenimiento por su importancia y por lo poco que se conoce de ella. Del mismo modo, en la cuarta sección nos dedicaremos al estudio del lenguaje Visual Basic apara Aplicaciones (VBA) con la idea de darle potencia y flexibilidad a las macros. En la quinta sección daremos una breve explicación del uso de los controles y los formularios a fin de facilitar el acceso a las macros y potenciar la interactividad de las hojas de cálculo con el usuario. Finalmente, en la sexta sección, trataremos de resolver ciertos problemas prácticos que sean de utilidad para el lector.

1.2.      DESCRIPCIÓN BÁSICA

El ancho de cada columna puede ser modificada según las necesidades del usuario. 

Del mismo modo, la altura de las filas, se puede aumentar o disminuir.

Celda y rango de celdas

Cada celda de la hoja se define usando la columna y la fila correspondiente a la celda. Por ejemplo las celdas A1, B5, M253, etc.

Un rango de celdas es un conjunto de celdas; es un arreglo rectangular de celdas. El rango se denota nombrando la celda inicial y final, separado por  “:”.

Por ejemplo:
A1:A1    Rango formado por una celda;
A5:A12    Rango formado por 8 celdas de la columna A, empezando en la fila 5;
B3:E3    Rango formado por 4 celdas de la fila 3, empezando en la columna B;
C5:E11    Rango formado por 21 celdas desde la celda C5 hasta la celda E11.

En la figura 1.2 se muestran estos rangos. En ella hemos puesto la celda inicial y final del rango. Una celda se selecciona haciendo clic en ella con el botón izquierdo.

Se puede seleccionar una celda o rango de celdas. Del mismo modo, se puede seleccionar una o más columnas enteras o una o más filas enteras. Si se desea seleccionar filas o columnas no contiguas se deberá usar la tecla [Ctrl] y hacer clic en aquellas que deben ser seleccionadas.

La celda que está seleccionada constituye la celda activa. En la figura 2 la celda activa es la celda C1. Esto se muestra a la izquierda de la barra de fórmula, éste espacio recibe el nombre de Cuadro de nombres. Si la celda seleccionada fuera B4, en el cuadro de nombres aparecería B4.

Libros … Hojas

Dijimos que el Excel genera una matriz electrónica muy grande. Cada una de esas matrices constituye una hoja de cálculo, bajo la concepción del MS Excel. Un conjunto de hojas de cálculo, conforman un libro.

De manera que cuando se ejecuta el programa Excel, éste crea un libro compuesto por un número predeterminado (se puede modificar) de hojas.

Nota:

El número de hojas predeterminadas en las dos versiones que describimos es 3.
En Ms Excel 2003:

-    El máximo número de hojas que se pueden disponer es de 32.

En Ms Excel 2007

-    He llegado a insertar 1027 hojas en un libro (claro, mediante macros; dejo esta pequeña curiosidad para el que sepa algo de macros con VBA).
-    ¿Alguien podría saber cuántas hojas se puede insertar en un libro? Respuesta: 180354.
-     ¿Y cuántas hojas se puede insertar en MS Excel 2010, MS Excel 2013? Y en MS Excel 2016?


Cuando se graba el contenido de una hoja, se graba también el contenido (si hubiera) de todas las hojas del libro. Esto implica que lo que se graba es un libro y no una hoja independiente de las otras.

Del mismo modo, cuando se abre un libro, éste se abre con todas sus hojas.

Activar y desactivar opciones y demás elementos de una hoja

En Ms Excel 2003

Abra el archivo Opciones para indicarle cómo puede activar o desactivar barras de herramientas, opciones, etc.

En Ms Excel 2007 , Ms 2010 y Ms Excel 2013

Abra el archivo Opciones para aprender el uso de opciones en esta versión. Verá que algunas opciones no tienen efecto pues originalmente fueron configuradas para la versión 2003.

Barra de fórmula



Es la barra en la cual se visualiza el contenido de la celda activa.
Si la celda contiene un dato, el valor del mismo se visualiza en la celda activa y en la barra de fórmula.
Si el contenido de la celda activa es una fórmula, en la celda se visualiza el resultado de la ejecución de la fórmula mientras que en la barra de fórmula se visualiza el contenido; es decir, la fórmula.

En la figura 1.4, la celda activa es la celda C3, su contenido es la compleja fórmula que se visualiza en la barra de fórmula y el resultado del cual, se visualiza en la celda C3.

Cuadro de nombres

El cuadro de nombres es la casilla en el que se visualiza la celda activa. De manera que si Ud. desea saber cuál es la celda donde se encuentra el cursor, es suficiente que observe esta casilla. Allí se visualiza también el nombre del rango que estuviera seleccionado.

En la forma práctica, podemos usar esta casilla para definir el nombre de un rango. ¿Porqué darle nombre a un rango? Lea la siguiente sección y trate de comprenderla bien. El uso de nombres de rango le da elegancia a toda fórmula que se pueda tener en una hoja de datos y facilita la comprensión de la misma.

Nombre de rango

Si bien todas las celdas de la gigantesca matriz electrónica tienen un nombre, como C1, B125, etc.; cada una de ellas o más aún, un rango de celdas puede tener un nombre particular.

Suponga que C5 contiene la tasa igual a 5% que se aplica a un rango de celdas. Si después de 6 meses, luego de haber trabajado con múltiples hojas, libros y cálculos, Ud. vuelve a revisar la fórmula que contiene a C5, le será difícil recordar qué representa su contenido; pero si dicha celda se llamara “Tasa” le será fácil recordarlo.

Sugerencia:

En lo posible, acostúmbrese a usar nombre de celda o nombre de rango para realizar todas sus operaciones de cálculo en una hoja.

¿Cómo se da nombre a una celda o rango de celdas?

Para dar nombre de rango a una celda o rango de celdas, use una de las siguientes opciones (hay otras, para nuestros propósitos es suficiente una de las dos):

Opción 1:

-    Seleccione la celda o el rango de celdas
-    Haga clic al interior del [Cuadro de nombres]
-    Digite el nombre que desea darle a la celda o rango seleccionado
-    Presione [Enter]

Opción 2:

En Ms Excel 2003

-    Seleccione la celda orango de celdas
-    Use la secuencia: [Insertar] - [Nombre] - [Definir]
-    Digite el nombre que desea
-    Haga clic en [Aceptar]

En Ms Excel 2007 y Ms Excel 2010 y Ms Excel 2013

-    Seleccionar la celda o rango de celdas
-    [Ficha Fórmulas] - En el grupo [Nombres definidos] - [Asignar nombre a un rango] - Digitar el nombre. Si es necesario seleccionar el ámbito. Tome nota de que dicho nombre será válido en todo el libro o sólo en la hoja donde se le definió. Del mismo modo, puede tener el mismo nombre de rango definido en hojas diferentes. Para usarlo el nombre del rango deberá estar precedido por el nombre de la hoja: Por ejemplo: Hoja1!Tasa.

¿Cómo se elimina el nombre de una celda o rango de celdas?

En MS Excel 2003:

-    Use la siguiente secuencia: [Insertar] - [Nombre] - [Definir]
-    Seleccione el nombre a ser eliminado
-    Haga clic en [Eliminar]
-    Haga clic en [Cerrar]

Nota:

Como habrá visto, la secuencia anterior permite también modificar o cambiar el nombre de una celda o rango de celdas.

Nota:

Los nombres de celdas o rango de celdas son válidos en todo el libro. Puede usarlo en la hoja donde lo definió o en cualquier otra hoja que conforma el libro.


En Ms Excel 2007 , Ms Excel 2010 y Ms Excel 2013:

[Fórmulas] - [Administrador de nombres] - En la ventana que se obtenga seleccionar el nombre que se desea borrar y hacer clic en la pestaña [Eliminar].

¿Cómo se puede obtener la lista de nombres de rango, existentes en un libro?

Simplemente haga clic en el botón del [Cuadro de nombres] (lado izquierdo de la barra de fórmulas). También se accede a la lista de nombres de rango usando F3.

Ahora veamos en detalle la novedad de la versión 2007 respecto a nombre de rango y a su administración.


En Ms Excel 2007 , Ms Excel 2010 y Ms Excel 2013

Además de hacer uso del cuadro de nombres para definir un nombre de rango, como lo hemos indicado, en esta versión los nombres de rango y otras opciones se encuentran en la ficha [Fórmulas], como se indica en la siguiente imagen la cual se obtiene haciendo clic en dicha ficha.




En Ms Excel 2010:



Primera forma:

-    Seleccione la celda o el rango de celdas al cual se le dará un cierto nombre
-    Haga clic en [Asignar nombre a un rango] del grupo [Nombres definidos]
-    Digite el nombre en el cuadro correspondiente a Nombre
-    Haga clic en [Aceptar]

Segunda forma

-    Seleccione las celdas o rango de celdas incluyendo la primer fila o primera columna la cual contiene el nombre que deseamos que tenga dichas celdas.
-    Haga clic en [Crear desde la selección] del grupo [Nombres definidos].
-    Si los nombres se encuentran en la primera fila, desactive [Columna izquierda]. Si los nombres se encuentran en la primera columna, desactive [Fila superior].
-    Haga clic en [Aceptar]

Ejemplo 01

Abra el archivo Pago de intereses. Vaya a la hoja Pasiva. Haga clic en la celda B3. En el cuadro de nombres digite TasaBC y presione [Enter].

Ahora vamos a darle nombre al rango C3:C5, pero quisiéramos que su nombre sea el contenido de la celda C2. Para ello
-    Seleccionamos el rango C2:C5
-    Hacemos clic en [Crear desde la selección]
-    Estando activa [Fila superior] hacemos clic en [Aceptar]

Deseamos asignar nombre a las otras columnas. Para ello seleccione el rango D2:I5 y luego haga clic en [ Crear desde la selección] y luego haga clic en [Aceptar].

Observe que en este caso estamos asignando nombres de rango a 7 rangos de celdas a la vez; en este caso, usando [Fila Superior].


Nota:

Observe el nombre asignado a la última columna: El nombre tiene un espacio en blanco y para que éste sea válido, ha insertado un guión bajo. Los nombres de rango no pueden tener espacios en blanco.


Finalmente vamos a darle el nombre Monto a la celda B7. Para ello seleccionamos el rango A7:B7 y luego hacemos clic en [ Crear desde la selección]. Observe ahora que la opción activa es [Columna izquierda]. Luego en [Aceptar]

Uso de la opción [Asignar nombre a un rango]

Esta opción permite definir el nombre de rango a un conjunto de celdas previamente seleccionadas o seleccionadas después de hacer clic en esta opción. En este caso, en las tres versiones que estamos comentando, la ventana que se obtiene es la siguiente:

En esta imagen se dispone del rango seleccionado; ahora se deberá ingresar el nombre del rango y elegir el ámbito. Si hace clic en el cuadro de nombres (ámbito) verá que podemos definir el nombre para ser conocido en todo el libro o en una hoja en particular. Esto implica que podemos usar el mismo nombre en hojas diferentes; pero si va a ser definido en el libro, el nombre será único.


Crear, Editar para Modificar o Eliminar el nombre de un rango

Si se desea crear, modificar o eliminar el nombre de un rango, en Ms Excel 2007 y Ms Excel 2010, se puede hacer uso del [Administrador de nombres] que se encuentra en el grupo [Nombres definidos].

Al hacer clic en esta opción se obtiene la siguiente ventana:

Como se puede apreciar en la ventana de la izquierda, el libro no tiene ningún nombre definido. Sólo está activo el botón [Nuevo], mediante el cual se puede crear nombres de rangos. Al hacer clic en este botón, se obtiene la ventana de diálogo descrito al hacer uso de la opción [Asignar nombre a un rango].

En cambio en la ventana de la derecha, se dispone de los nombres creados en el último ejemplo.

Al seleccionar uno de los nombres, se activan los botones [Editar] y [Eliminar], lo cual significa que si se selecciona un nombre y se hace clic en [Editar] obtendremos la ventana de diálogo antes mencionada. Esto permitirá redefinirla.

Del mismo modo, si se selecciona un nombre y se hace clic en [Eliminar] se podrá eliminar dicho nombre.

Ejemplo 02 (Ms Excel 2007 y Ms Excel 2010)

Eliminar los nombres que hemos creado. Para ello debemos usar [Fórmulas] - [Administrador de nombres], hacer clic en el nombre a ser eliminado y luego hacer clic en [Eliminar]. En la siguiente imagen, luego de seleccionar el nombre Año_2009, se ha hecho clic en [Eliminar], con lo cual se ha obtenido la ventana en la que debemos hacer clic en [Aceptar] si realmente se desea eliminar el nombre seleccionado o hacer clic en [Cancelar] si no se desea eliminarlo.

Combinar celdas

Una o más celdas se pueden combinar para ser tratadas como una celda. Esto es útil en muchos casos sea como título de una tabla de varias columnas o para una mejor presentación de los datos de una hoja.

Procedimiento



- Seleccione las celdas que desea combinar
- Haga clic en el icono o botón   que permite combinar y centrar.
- Si desea puede alinearlo a la izquierda o derecha o dejarlo centrado haciendo clic en los botones de la barra de herramienta de formato que se muestra en pantalla.

Nota:
En Excel 2007, Ms Excel 2010 y Ms Excel 2013 Luego de seleccionar la celda o rango de celdas a ser combinadas, debe acceder a la ficha [Inicio]; en ella, en el grupo [Alineación], se encuentra el icono que permite combinar y centrar.

Copiar y Pegar
En Excel se puede copiar valores y fórmulas de una celda o rango de celdas y luego pegarlas en otra celda o rango de celdas. Para ello,
- Seleccione la celda o rango de celdas que se desea copiar;
- Use [Ctrl] + [C] para copiar (puede usar el forma que Ud. desee)
- Haga clic en la celda (o celda inicial del rango) hacia donde desea pegar
- Use [Ctrl]+[V] para pegar lo que se copió al portapapeles (memoria). La ventaja de pegar usando [Ctrl]+[V] o hacer clic en [Copiar] de la ficha [Inicio] le permite disponer de   . Más adelante describiremos a este botón.

Uso del portapapeles en MS Excel 2007, Ms Excel 2010 y Ms Excel 2013

En esta versión el uso del portapapeles tiene algunas opciones que deseamos comentarla pues resulta altamente beneficiosa en ciertas situaciones y que optimiza el uso de la hoja de cálculo:
Para acceder al portapapeles debemos usar el grupo [Portapapeles] de la ficha [Inicio]. La imagen siguiente muestra este grupo(Versión 2007 y 2010), en la cual, luego de copiar el contenido de una o más celdas, el icono [Pegar] ha quedado activado.

      


Como se puede apreciar, después de copiar un cierto rango de celdas y colocar el cursor en donde se desea pegar, al hacer clic en la flecha del icono [pegar], se despliega una lista de opciones de pegado.

A la opción [Pegado especial] se puede acceder también haciendo clic con el botón derecho del ratón.
Si hubiéramos hecho clic en el cuerpo del icono, se habría pegado sin dar paso a estas opciones.

Después de pegar:
Después pegar lo copiado, se visualizará el icono de opciones de pegado, que es lo que se muestra en este caso:
Si se despliega la flecha de este icono     se dispondrá de las siguientes acciones:

• Mantener formato de origen
• Mantener tema de destino
• Coincidir con formato de destino
• Formato de números y valores
• Mantener ancho de columnas de origen
• Sólo formato
• Vincular celda

Para evitar problemas de pegado de columnas de diferente ancho o amplitud se debe seleccionar la opción Mantener ancho de columnas de origen.

Ejemplo 03

Abra el archivo Ejemplo 01. En la Hoja 2. Esto se muestra en la Figura 1.10.

a)      Copie los nombres y los precios de los productos del Cuadro 1 al  Cuadro 2.

b)      Calcule el Monto de los ingresos para Licuadora. Copie esta fórmula para obtener el ingreso de los otros productos. Observe y analice la fórmula en cada caso.

c)      Ahora calcule el nuevo precio (precio proyectado) de Licuadoras para el año 2006. Copie esta fórmula para los otros productos usando el mismo procedimiento empleado en el caso b). Observe los resultados.

Solución

a)      Para copiar los nombres de los productos:

-       Seleccione el rango A3:A5

-       Use [Ctrl] + [C] para copiar los nombres

-       Haga clic en la celda A12

-       Use [Ctrl] + [V] para pegar lo copiado

Para copiar los precios, seleccione el rango B3:B5, copie y pegue en B12.

b)      Calcular el Monto de licuadoras

-       Haga clic en D3

-       Ingrese la fórmula: =B3*C3

-       Presione [Enter]

-       Copie el contenido de D3 hacia D4:D5.

Observaciones:

El contenido de D3 es =B3*C3

Al copiar esta fórmula de D3 a D4, la fórmula cambia a =B4*C4; es decir, al copiar una fila hacia abajo, las filas de las celdas que conforman la fórmula, cambian a 4.

Al copiar de D3 hacia D5, estamos avanzando dos filas, como tal, las filas de la fórmula =B3*C3 también cambiarán a =B5*C5

Esto significa que las fórmulas cambian según la forma cómo se copia.

c)      Precio proyectado para el 2006, vemos que el precio se incrementa en 5%

-       En el caso de Licuadoras, en C12 ingrese la fórmula: =B12*C10.

-       Copie esta fórmula hacia el rango C13:C14.

        Según el problema, en C13 debiera estar la fórmula =B13*C10; sin embargo, aparece la fórmula =B13*C11. Si hace clic en C14 verá que el contenido de C14 es =B14*C12, lo que también no es correcto. Como es lógico, al avanzar una fila hacia abajo, la fórmula  que se obtiene no es correcta. Las fórmulas deben ser: En C12: =B12*C10; en C13: =B13*C10 y en C14: =B14*C10. Esto nos sugiere conocer algo más sobre celdas.


Celdas relativas y celdas absolutas

Como la celda C10 no debe cambiar,  debemos fijarla de alguna manera. Esto se hace usando el concepto de celdas absolutas. Una celda absoluta toma la forma $C$10. En este caso, al copiar a cualquier lugar la fórmula que lo contiene, la celda C10 permanecerá fija; es decir, no cambia la fila y no cambia la columna. Al usar   $C10 se podrá copiar a la izquierda o derecha permaneciendo fija la columna C. Por el contrario si C$10 se copia hacia arriba o abajo la fila 10 permanecerá fija.
La celda C10  constituye una celda relativa. Cuando se copia la fórmula que la contiene, cambiará relativamente al copiado, como lo vimos en el caso a) y b) del ejemplo anterior.

Luego ingrese en C12 la fórmula =B12*C$10 o también =B12*$C$10. Y copie.
La figura 1.11 muestra las fórmulas que se usan para los cálculos respectivos.



Modificar la altura de una fila

Si desea modificar la altura de una fila, ejecute el siguiente procedimiento:

-    Seleccione la (s) fila (s) a la (s) cual (es) desea modificar su altura
-    [Formato] - [Fila] - [Ancho]
-    Digite el ancho deseado
-    [Aceptar]

En el Ms Excel 2007 , Ms Excel 2010 y Ms Excel 2013
Para el uso de los atributos de una celda o rango de ellas, use la opción [Formato] del grupo [Celdas] de la ficha [Inicio].

Ejercicio 01

Abra el archivo Ejemplo 01. En la Hoja1 calcule las ventas proyectadas para el 2006 en el cual las ventas del 2005 se incrementan en el 5%. Use celdas absolutas. Luego grabe los resultados como REj01.xlsx

Ventaja en el uso de nombre de celda o rango de celdas

Solución para el caso c) del ejemplo 1, usando nombre de celda:
-    Que la celda C10 se llame Tasa. Para ello, haga clic en C10; digite en el cuadro de nombres la palabra Tasa y presione [Enter].
-    Ahora en C12 digite la fórmula: =B12*tasa
-    Copie el contenido de C12 hacia el rango C13:C14.
-    Observe los resultados.
-    Grabe el libro como con el mismo nombre.

Ejercicio 02

Estando abierto el archivo Ejemplo 01.xls, haga que la celda C1 se llame Incre y vuelva a resolver el ejercicio 1 usando el nombre de C1. Vuelva a grabar el archivo con el mismo nombre.

Las hojas de un libro

Como ya hemos dicho, en Excel un libro está compuesto de un conjunto de hojas. El número de hojas habilitadas cuando se abre un libro nuevo, puede ser diferente en una instalación. Cada usuario define el número de hojas que debe tener un libro nuevo.

Para definir cuántas hojas debe tener un libro cuando se crea, use la secuencia:
-    [Herramientas] - [Opciones] - [General]
-    Haga clic en el botón de [Número de hojas en un nuevo libro] para seleccionar el número de hojas deseadas. Si lo desea, digite la cantidad.

Esta opción no modifica el número de hojas en el libro activo; tendrá efecto cuando se vuelva a ejecutar el Excel.
En cada hoja se pude colocar diferente tipo de información, aunque los datos de uno pueden servir para hacer cálculos en las otras hojas.





Para pasar de una hoja a otra, hacer clic en su nombre  o etiqueta de hoja.
Para seleccionar una hoja, haga clic en la etiqueta o nombre de la hoja
Para agrupar o seleccionar varias hojas, use la siguiente secuencia:
-    Seleccione la primera hoja
-    Presione la tecla [Ctrl]
-    Haga clic en  cada una de las hojas a ser seleccionadas

Para desagrupar hojas, use una de las siguientes opciones:
-    Botón derecho sobre una de las hojas agrupadas y luego [Desagrupar]
-    Clic en la etiqueta de cualquier hoja no agrupada (no seleccionada)

Para insertar una nueva hoja
-    Use la secuencia: [Insertar] - [Hoja de cálculo]

Otra forma:
-    Haga clic con el botón derecho sobre la hoja donde desea insertar
-    Seleccione la opción [Insertar]
-    Seleccione [Hoja de cálculo]

Si desea mover una hoja

Para cambiar de nombre a una hoja use una de las siguientes opciones:

-    Haga doble clic en el nombre actual y digite el nombre deseado
-    Haga doble clic con el botón derecho y seleccione [Cambiar nombre]
-    Use la secuencia: [Formato] - [Nombre] - [Cambiar nombre]

Para eliminar una hoja haga clic con el botón derecho sobre la hoja, clic en [Eliminar]  y luego [Aceptar]. Si fueran varias, antes seleccione a todas ellas.
Para ocultar una hoja, use la secuencia: [Formato] - [Hoja] - [Ocultar]
Para mostrar una hoja oculta, use la secuencia: [Formato] - [Hoja] - [Mostrar] a continuación, seleccione la hoja a ser mostrada y luego clic en [Aceptar].
Para cambiar el color de la etiqueta de hoja

-    Botón derecho sobre la etiqueta de hoja, [Color de etiqueta], seleccionar un determinado color y luego [Aceptar].
-    [Formato] - [Hoja] - [Color de etiqueta], seleccionar un color, [Aceptar].

Para insertar una imagen de fondo, use [Formato] - [Hoja] - [Fondo], seleccione una imagen o fondo de pantalla y luego clic en [Aceptar].

Llenado automático de rango de celdas

Hay dos formas de rellenar un rango de celdas a partir de una o más, que contienen los datos iniciales, a partir de los cuales se genera la serie.



Hay dos formas de rellenar un rango de celdas a partir de una o más, que contienen los datos iniciales, a partir de los cuales se genera la serie.

a)    Usando el ratón

Supongamos que la celda A3 contiene “Enero”.  Si deseamos rellenar las celdas A4:A14 con los siguientes meses, basta con copiar esta celda hacia el rango solicitado usando (necesariamente) el ratón. Para ello, ubique el puntero del ratón en la esquina inferior derecha del cuadro que se forma en la celda activa. En la figura de la derecha (Figura 1.13) al llevar el puntero del ratón a la esquina inferior derecha, éste se ha convertido en “+”. En estas condiciones, arrastre hacia abajo hasta cubrir la celda A14.

Haga lo mismo si desea ingresar los nombres de los días; para el cual debe ingresar en la celda inicial, el nombre del primer día.
En general, todo texto que termina en número e ingresado en una celda, puede servir para generar una serie de valores secuenciales. Por ejemplo, Tienda 1, Tienda 2, etc.

En MS Excel 2003

b)    Usando la secuencia [Edición] - [Rellenar] - [Series]

Esta secuencia permite generar series numéricas que siguen determinado patrón. Se puede generar series lineales, geométricas, cronológicas o de autor relleno.



Al usar la secuencia anterior, obtendrá la siguiente ventana (Figura 1.14)


En Ms Excel 2010 y Ms Excel 2013
Usando [Inicio] - [Grupo Modificar] - [Botón Rellenar] - [Series]
podemos obtener la misma ventana.

Según esta ventana, puede generar series en filas o columnas. Puede fijar activar tendencia o fijar un incremento.

En el caso de activar [Tendencia] el incremento es unitario o la diferencia entre las celdas iniciales con datos. Si hubiera más de dos celdas con datos, calcula la tendencia con ellos.

Si selecciona [Lineal], los datos se incrementan según lo especificado.

Si se usa la opción [Geométrica], la diferencia entre los datos iniciales se usa para generar una serie geométrica: Por ejemplo: 2, 5, 15, 45, 135,…

Si elige [Cronológica] los datos deben tener formato de fecha. El llenado se hace según la tendencia o el incremento.

Puede ser más útil la primera forma pues en muchos casos se requiere de una lista de meses, días, semanas, años o de una lista de texto pero enumerada. En todos estos casos ingrese el primer valor en la primera celda y luego copie.

En Ms Excel 2007 , Ms Excel 2010 y Ms Excel 2013

El auto relleno se efectúa usando la siguiente secuencia: [Inicio] - [Modificar] - [Rellenar]. Con lo cual se obtiene la imagen de la izquierda. Al hacer clic en la flecha de [Rellenar] se obtiene las opciones que se muestran en la imagen de la derecha.




Dando formato a los datos de una hoja

Los datos contenidos pueden tener dos tipos de formatos: Formato de texto o formato numérico.

En el caso de Formato de texto, hablamos de poner en negrita, subrayado, estilo de fuente, tamaño, etc. En el caso de Formato numérico estamos hablando del número de decimales, millares, porcentaje, signo monetario, etc.

Al usar la secuencia [Formato] - [Formato de celdas] se obtiene la ventana mostrada en la figura 16.

Dando formato a los datos de una hoja

Los datos contenidos pueden tener dos tipos de formatos: Formato de texto o formato numérico.

En el caso de Formato de texto, hablamos de poner en negrita, subrayado, estilo de fuente, tamaño, etc. En el caso de Formato numérico estamos hablando del número de decimales, millares, porcentaje, signo monetario, etc.

Al usar la secuencia [Formato] - [Formato de celdas] se obtiene la ventana mostrada en la figura 16.

Esta ventana contiene un gran número de formatos que se puede usar en una hoja, clasificadas según el tipo de dato y formato que se desea usar. Para ello dispone de 6 fichas o pestañas. Para todas ellas se ingresa usando [Inicio] - [Celdas]

Use [Formato] - [Formato de celdas ] - [Número]

Para dar formato a las celdas que contienen datos numéricos, sea sin formato (General), con decimales, monetario, fecha, hora, porcentaje, etc.

Un caso particular es el uso de la opción [Personalizada]. Permite definir un formato de acuerdo a las exigencias del usuario, siempre que sean válidas para el Excel.

Use [Formato] - [Formato de celdas ] - [Alineación]

Para alinear el contenido de las celdas seleccionadas a la izquierda, a la derecha o centrarlo. Puede también alinearlo verticalmente siempre que la altura de la celda lo permita.

Use [Formato] - [Formato de celdas ] - [Fuente]

Para dar a los caracteres que conforman el dato contenido en las celdas seleccionadas como tamaño, negrita, subrayado, estilo de fuente, etc.

Use [Formato] - [Formato de celdas ] - [Bordes]

Para ponerle cierto tipo de borde a los datos contenidos en las celdas seleccionadas.

Use [Formato] - [Formato de celdas ] - [Tramas]

Para definir el color de la fuente, de fondo de las celdas, etc., a las celdas

Use [Formato] - [Formato de celdas ] - [Proteger]

Para desactivar el bloqueo que las celdas tienen activadas por omisión. Al estar desactivada una celda y estando protegida la hoja, se puede modificar el contenido de las celdas desbloqueadas. Esto es útil si en el uso posterior de la hoja se desea modificar ciertos datos y proteger otros, fundamentalmente las que contienen fórmulas o datos que no deben cambiar.

Nota curiosa:

-    Si se hace doble clic en la línea que separa las etiquetas de columna, logrará que el ancho de la columna izquierda se ajuste automáticamente al contenido de mayor longitud.
-    Si desea colocar un texto en más de una línea, pero dentro de la misma celda, después de digitar una parte del texto, presione [Alt]+[Enter] y siga digitando. Luego presione [Enter].

Formato condicional

Así como podemos asignarle diversos tipos de formato a una celda o rango de celdas, así también podemos definir un determinado formato para una o más celdas pero de manera condicional.

¿Y qué uso tiene esta forma de dar formato?

Por ejemplo, si en una lista de aspirantes a 5 puestos de trabajo,  quisiéramos resaltar a los que aprobaron las calificaciones, usando [Formato condicional] podríamos poner en azul las notas de los probados y en rojo a los reprobados.

Ahora suponga que en la columna C tenemos las fechas de inicio de un conjunto de proyectos y en la columna E las fechas de finalización de los mismos. Usando [Formato condicional] podríamos saber los proyectos que terminan en una fecha, o  podríamos saber los proyectos que terminan en un año y cuyo costo sea menor que una cantidad.

Podríamos también usar [Formato condicional] para agrupar una lista ordenada que satisface determinados rangos.

Ejemplo 04

Abra el archivo Ejemplo 02. Calcule el promedio sumando todas las notas y dividiendo entre 4. Que el promedio tenga dos decimales. Luego use Formato condicional para hacer que
a)    Nota 1, Nota 2, Nota 3 y Nota 4 estén en azul las aprobadas y rojo las desaprobadas.
b)    El promedio esté en rojo las notas por debajo de 11.00; en verde las notas mayores o iguales a 11.00 pero menores que 15 y, en azul, de tamaño 12 y subrayadas, las notas mayores o iguales a 15. Grabe el archivo como REj02.xlsx.

Solución

En MS Excel 2003

Primero calcule el promedio. En F6 digite: = (B6+C6+D6+E6)/4. Luego copie.
Seleccione el rango de los promedios: F6:F33.
Use [Formato] - [Celdas…] - [Número] y haga que tenga 2 decimales.

a)    Use el siguiente procedimiento:
-    Seleccione el rango B6:E33
-    Use la secuencia: [Formato] - [Formato condicional]
-    Complete la ventana de diálogo según se muestra en la figura 1.17


-    El primer cuadro de lista debe estar en [Valor de celda]; en el segundo se debe seleccionar “Menor que”; digitar 11 (para los desaprobados)

-    Haga clic en [Formato] para definir el color que debe tener las notas menores que 11. En este caso seleccione rojo en [Color]; luego clic en [Aceptar].

-    Como debemos poner en azul las notas aprobadas, debemos definir una segunda condición. Por ello haga clic en [Agregar].

-    Seleccione “Mayor o igual a”; digite 11; haga clic en [Formato]; seleccione el color azul; haga clic en [Aceptar]

-    Haga clic en [Aceptar]

b)    Procedimiento:
-    Seleccione el rango F6:F33
-    Use la secuencia: [Formato] - [Formato condicional]
-    Seleccione [Menor que]; digite 11; clic en [Formato]; [Color] Rojo; [Aceptar]
-    Clic en [Agregar].
-    Seleccione [Entre]; Digite primer valor, 11; el segundo valor, 14. Clic en [Formato]; seleccione color Verde; clic en [Aceptar].
-    Clic en [Agregar]  (Sólo se puede definir 3 condiciones)
-    Seleccione “Mayor o igual que”; clic en [Formato]; seleccione Azul y [Subrayado] y elija [Simple], luego [Aceptar]
-    Clic en [Aceptar]


En MS Excel 2007 , Ms Excel 2010 y Ms Excel 2013

En esta versión el Formato condicional es mucho más completo.

a)   
-    Calculamos la columna Promedio
-    Seleccionamos el rango B6:E33.
-    Hacemos clic en [Formato condicional] del grupo [Estilos] de la ficha [Inicio] para desplegar la diversidad de opciones de formato condicional.
-    Haremos clic en [Resaltar reglas de celdas] - [Es mayor que]
-    En el cuadro que se emite ingresamos 10 (para los aprobados].
-    En el cuadro [con] desplegamos la lista y seleccionamos [Formato personalizado]
-    En la siguiente ventana seleccionamos [Color] Rojo y hacemos clic en [Negrita]
-    Luego de hacer clic en [Aceptar] volvemos a usar el formato condicional pero ahora elegimos [Es menor que] digitando 11 y seleccionando Color: Rojo en la opción [Formato personalizado].

b)   
-    Seleccionamos el rango F6:F33
-    En formato condicional, seleccionamos la opción [Resaltar reglas de celda] - [Entre]
-    En el recuadro ingresamos 11 y 14.
-    En [Con] seleccionamos [Formato personalizado] y en [Color] elegimos [verde] para luego hacer clic en [Aceptar]
-    A continuación elegimos [Es mayor que] de [Resaltar reglas de celda] y seleccionamos el color Azul
-    Finalmente seleccionamos [Es menor que] de la misma opción y elegimos el color rojo. En todos los casos usamos Negrita a fin de resaltar el color de fuente

Ejemplo 05

Abra el archivo Formato condicional y resuelva lo que allí se pide usando el procedimiento que se indica tanto en MS Excel 2003 como en el MS Excel 2007. Guarde la modificación al archivo.

Ejercicio 03

Abra el archivo Ejemplo 02 y vaya a la hoja cuya etiqueta es “Hoja2”. Tomando en cuenta la columna de duración, haga que se liste en rojo los proyectos que tienen
a)    una duración mayor de un año
b)    un costo entre $ 100,000 y $ 400,000
c)    costo menor que $ 100,000; en rojo; entre $ 100,000 y $ 400,000, azul y verde los proyectos que tienen un costo mayor o igual a $ 400,000. Grabe el libro como REj02.xls

Ejercicio 04

Abra el archivo Ejemplo 03. Resuelva cada una de las preguntas que se plantean en la hoja llamada Primera. Grabe el archivo como REj03.xls.

Solución

Pregunta 01:

En MS Excel 2003
-    Use la secuencia: [Herramientas] - [Opciones] - [Ver]
-    Desactive la opción [Líneas de división]
-    [Aceptar]

En Ms Excel 2007 , Ms Excel 2010 y Ms Excel 2013
-    Hacer clic en la ficha [Vista]
-    En el grupo [Mostrar u ocultar] hacer clic en  [Líneas de cuadrícula]

Las siguientes preguntas serán resueltas usando Ms Excel 2007 , Ms Excel 2010 y Ms 2013

Pregunta 02:
Usando la secuencia anterior, desactive [Encabezado de fila y columna]

Pregunta 03.
-    En la celda A3 digite Ene y presione [Enter]
-    Ahora copie usando el ratón y arrastrando hasta Diciembre que debe ser Dic.

Pregunta 04
-    En la celda C30, digite Precio; presione [Alt] + [Enter]; luego digite Unitario.
-    Haga lo mismo con Monto de; presione [Alt]+[Enter]; luego digite la Venta.
-    Seleccione A30:D30; clic en el tarro de pintura; seleccione el color Verde lima y que el texto esté resaltado; es decir, en negrita.
-    Haga que cada una de estas celdas tenga Borde de cuadro grueso.
-    Seleccione el rango A31:A37; elija el color Azul pálido; Borde de cuadro grueso.
-    Repita lo mismo con las columnas B, C y D, del mismo rango; use color Canela para unas de las columnas y Borde de cuadro grueso.

Pregunta 05.
-    Haga clic en [Insertar] - [Formas]; luego seleccione [Flechas de bloque] y elija la que corresponde; luego píntelo de amarillo.

Pregunta 06.
-    Seleccione el rango D31:D37; presione la tecla [Supr]
-    En D31 digite: =B31*C31
-    Copie esta fórmula hacia el rango D32:D37

Pregunta 07.
-    Seleccione el rango D31:D37; copie por el método que sepa o use [Ctrl]+[C]
-    Presione el botón derecho del ratón; seleccione [Pegado especial]
-    Seleccione de la lista [Valores]; luego haga clic en [Aceptar]

Pregunta 08.

Estando en la hoja Primera, posicione el puntero del ratón en la división de las etiquetas de columna A y B. Cuando logre convertir el puntero en     presione el botón izquierdo y observe el ancho de la columna. Debe obtener 30.0; en el caso de la columna A de la hoja Segunda, obtendrá 10.71.

Para darle el ancho pedido, arrastre hacia la izquierda o derecha hasta obtener la medida indicada.

Seleccione el rango A20:D27 de la hoja Primera; presione [Ctrl]+[C] para copiar. Luego haga clic en la celda A5 de la hoja Segunda y use [Ctrl]+[V] para pegar.

Funciones básicas

=Raíz(A2)   

El siguiente segmento de hoja contiene el uso de algunas de estas funciones:

Algunas funciones matemáticas
=Entero(A2)	Devuelve la parte entera de A2, truncando la parte decimal (si tuviera).
=Redondear(A2,n)	Devuelve el valor de A2 con “n” decimales redondeando al inmediato anterior o siguiente.
=Residuo(A2,B2)	Devuelve el residuo de dividir A2 por B2.
Devuelve la raíz cuadrada de A2
=Potencia(A2,p)	Devuelve como resultado el valor del contenido en A2, elevado a “p”.
=Potencia (64,1/3)	devuelve la raíz cúbica de 64; esto es, 4.
=Pi()	Devuelve el valor de pi :  3.14159265358979
=Exp(-B2)	Devuelve como resultado de elevar a –B2 el número “e”.
=Log10(A2)	Devuelve el logaritmo decimal de A2
=Abs(A2)	Devuelve el valor absoluto de A2
=Aleatorio()	Devuelve un número aleatorio entre 0 y 1.
=Aleatorio. Entre (a, b)	Devuelve un número aleatorio entero entre a y b.
Observación: En el libro Formto condicional, hemos usado esta función para ver el efecto
=Fact(B8)	Devuelve el factorial de B2. En este caso 5! = 120
=Combinat(B2,B8)	Devuelve el número de combinaciones de B2 tomados de B8 en B8
=MMult(MatrizA,MatrizB,…)	Devuelve el producto matricial de sus argumentos
=MDeterm(Matriz)	Devuelve el determinante de una matriz cuadrada
=MInversa(MatrizA)	Devuelve la inversa de la MatrizA
=Suma(Dato1,Dato2,…)	Devuelve la suma de todos los argumentos
=Subtotales(n,d1,d2,…)	Devuelve el promedio, suma, producto, de d1,d2,… según el valor de “n”
El siguiente segmento de hoja contiene el uso de algunas de estas funciones:

Ejemplo 06

En el siguiente segmento de hoja se dan los datos de dos vectores y dos matrices.



Vamos a realizar algunas operaciones con los vectores y matrices
Antes de realizar algún tipo de operación, vamos a darle nombre de rango a los vectores y matrices.

Que el rango B4:B9 se llame A. El rango C4:C9 se debe llame B
Que el rango F4:I8 se llame P. Que el rango L4:O7 se llame Q y C12:H12, Bt.

Funciones matemáticas de uso frecuente en la Estadística

Además de casi todas las funciones vistas anteriormente usados en todo tipo de aplicaciones, el Excel dispone de otras funciones matemáticas de intensa aplicación en el campo de la Estadística. A continuación describiremos su sintaxis, una breve explicación y un ejemplo de aplicación de algunas de estas funciones matemáticas.

Función SumaProducto

Su sintaxis:
=SumaProducto(Arg1, Arg2,…, Argk)

Devuelve la suma de los productos de todos los argumentos. Si los argumentos fueran matrices, multiplica elemento por elemento y luego devuelve la suma de ellos.

Ejemplo 07

a)    Aplicado a celdas o datos constantes

El uso de la fórmula: =SumaProducto (4,5,3,2)  da como resultado 120
Si A2 = 5; A3 = -2; A4 = 4; A5 = 3; la fórmula:
=SumaProducto (A2,A3,A4,A5) devuelve como resultado: -120

Tomemos en cuenta el segmento de hoja que se muestra en la figura 18.

b)    Función SumaProducto aplicado a dos vectores

Si en la celda B24 se digita la fórmula (Recuerde que A y B son nombres de rango)
=SumaProducto(A, B)

Se obtiene como resultado: 60

c)    En la celda Q24 digite la fórmula (aquí también A es nombre del rango (matriz):
=SumaProducto (MInversa (Q)),Q) ¿qué obtiene?

Se obtiene como respuesta: -3.337912088

Ejemplo 08

Dado el siguiente segmento de hoja. Haga que el rango de datos del cuadro de



créditos otorgados se llame Préstamo y el cuadro de las tasas, se llame Tasa.

Use matrices para responder a las siguientes preguntas:

¿Cómo puede obtener el cuadro “Ingreso mensual por sector”?
Respuesta: Seleccione el rango y digite: =Préstamo*Tasa

¿Cómo puede obtener el “ingreso total recaudado al final del año?
Respuesta: Haga clic en N23 y digite: =SumaProducto (Préstamo, Tasa)

Función Sumar.Si

Su sintaxis: =Sumar.Si (RDatos, Cond, SCol)

Devuelve la suma de todos aquellos valores ubicados en las filas de la columna SCol, que satisfacen la condición especificada en el argumento Cond, que actúa sobre la primera columna de RDatos.

 
Ejemplo 09

Dado el segmento de hoja que se muestra en la figura 1.21,



La fórmula que permite obtener la cantidad de “Equipo sonido” vendido durante los 15 días es: =Sumar.Si(B2:B11,F3,C2:C11). Esto fórmula se ha digitado en G3.
El primer argumento (columna B) contiene los nombres de los productos.
El segundo argumento (celda F3) contiene el nombre: “Equipo sonido”. Este es el criterio.

El tercer argumento (columna C) contiene los valores que se deben sumar si el valor del criterio se encuentra en el rango de los datos. En G3 debe tenerse como resultado: 13.

Observe que el rango de datos (primer argumento) usado para calcular los montos de venta por cada día de la semana, incluye otras columna que no intervienen en la suma.

Nota 1
En cuanto al criterio, se pudo haber digitado “Equipo sonido” en lugar de F3. Pero ello no habría servido para copiar la fórmula hacia abajo.

La primera columna del rango de datos que constituye el primer argumento, debe contener valores sobre los que actúa el criterio.

Nota 3
Si el rango de datos (argumento 1) contiene a los datos que se van a sumar, no será necesario el uso del tercer argumento.

Nota curiosa:
Puedo usar la función suma con mayores recursos (o potencia) que la función Sumar. si(…)?
La función Sumar.si, le permite sumar las celdas un rango que cumple con alguna condición expresada en la misma columna o por lo general en otra columna. Como vimos en la figura 21, podemos sumar los montos de las ventas de los lunes solamente, de los jueves, etc.; del mismo modo podemos sumar las ventas de un determinado tipo de artículo; pero cómo podemos sumar las ventas del artículo: Equipo de sonido, pero sólo de los jueves?
Aquí está la solución:

=Suma((A2:A11=”Jueves”)*(B2:B11=”Equipo de sonido”)*D2:D11)
Qué le parece?

Ahora veamos el siguiente problema:

Función Sumar.Si.Conjunto

No puede ser !!!

Han creado otra función !!!. Ya no sirve lo que mi pobre cerebro hizo con la función Suma.

En efecto: Mediante la función Sumar.Si.Conjunto podemos realizar operaciones de suma condicional mucho más complejas, como en el caso descrito líneas arriba con la función suma, solo que ahora de una forma mucho más elegante y sencilla de entender y usar.

Su sintaxis:
=Sumar.Si.Conjunto(SCol,RDatos1,Cond1,RDatos2,Cond2,…)

En esta sintaxis se puede apreciar que la secuencia del Sumar.Si ha cambiado. Ahora la columna que se debe usar para sumar está como primer argumento. Y los siguientes argumentos se usan por pares: El rango de datos que contiene uno o más elementos que coinciden con el valor del criterio dado (RDatos1) y el criterio mismo (Cond1). Esta pareja se repite una o más veces, según lo que se requiera.


Nota:
Esta y otras similares pero que están en las funciones estadísticas, pertenecen al MS Excel 2007 y las versiones siguientes.

Ejemplo 10

Abra el archivo Estadist Ventas. Como puede apreciar, en la hoja Semana1 se dispone de las ventas de una semana, correspondientes a cinco productos, realizadas en varias tiendas, de una cierta zona, de un distrito y en un día determinado de la semana. Se desea obtener las estadísticas que se muestran en la hoja llamada Estadísticas.

a)    Complete el primer cuadro de la hoja Estadísticas
b)    Complete el tercer cuadro de la misma hoja

Solución:
Antes de resolver las preguntas vamos a darle nombre de rango a cada una de las columnas de los datos de la hoja Semana1. Para ello seleccione todos los datos; es decir, el rango A2:J597. A continuación, use la siguiente secuencia para asignar los nombres de la primera fila a cada una de las columnas de datos:  [Formulas] - [Crear desde la selección]. En la ventana que sale asegúrese que solo quede activada [Fila superior] y luego haga clic en [Aceptar]. Verifique.
a)    En la celda B3 de la hoja Estadísticas, debemos obtener la venta total realizadas en la tienda “Mega Plaza” correspondiente a la Zona “Este”. La primera condición es Tienda = “Mega Plaza” y la segunda Zona = “Este”. En consecuencia la función a ser digitada en B3 es:
=SUMAR.SI.CONJUNTO(Total,Tienda,$A3,Zona,B$2)
A continuación copie hacia abajo y a la derecha. Se ha usado $A3 de forma que cuando se copie a las otras columnas, se mantenga fija la columna A; del mismo modo, cuando se copie hacia abajo, la fila 2 debe permanecer fija, por ello se tiene la celda B$2.
Podrá apreciar que sólo hay ventas en “Lago Plaza”, las otras son cero.

b)    En este caso se trata de sumar la columna Total, tomando en cuenta tres condiciones: En B29 se debe digitar:

=SUMAR.SI.CONJUNTO(Total,Dia,$A29,Zona,B$28,Distrito,B$27)

Observación:
Como puede apreciar, en B27, C27, D27 y E27 hemos digitado “Lima”, pero sólo se ve el contenido de C27. Habrá otra forma de resolver este problema? Lo dejamos para Ud.

Ejercicio 05:

Complete el segundo cuadro de la hoja Estadísticas.

Nota:
Las siguientes sumatorias las veremos mediante ejemplos más adelante.

Función Suma.Cuadrados



Sintaxis:

=Suma.Cuadrados(Dato1,Dato2,…)

Devuelve la suma de los cuadrados de cada uno de los argumentos. Estos pueden ser valores, celdas con valores numéricos o matrices.

Función SumaX2masY2  
 
Sintaxis: =SumaX2masY2(Dato1,Dato2)

Devuelve la suma de los cuadrados de los dos argumentos. Esta función se diferencia de la anterior en que sólo tiene dos argumentos. Los argumentos pueden ser valores elementales o arreglos (matrices).

Función SumaXmenosY2

Sintaxis: =SumaXmenosY2

Devuelve la suma del cuadrado de la diferencia de X e Y.   Estos pueden ser valores elementales o arreglos (matrices).

En la siguiente figura se dispone de un ejemplo de estas funciones.



Previamente definir los rangos A2:A9 como X, B2:B9 como Y, C2:C9 como Z

El Excel dispone de muchas funciones estadísticas, algunas de las cuales se muestran en la siguiente tabla. Además de éstas y de las que no las mencionamos, el Excel dispone de las llamadas herramientas estadísticas que caen en el terreno del Análisis de Datos. Esta es una opción que se encuentra en la ficha [Datos].

Función Contar.si

Devuelve el número de elementos de un rango de datos que coinciden con un determinado criterio.

Su sintaxis: =Contar.si(RangoDatos,Criterio)

El primer argumento hace referencia a un rango de datos, algunos de cuyos elementos contienen el valor mostrado en el segundo argumento.
Por ejemplo: =Contar.si(RangoDia,”Lunes”). Si RangoDia es un rango de celdas que contienen los nombres de los días de semana, esta función devuelve el número de veces que la palabra “Lunes” se repite en dicho rango.

Función Contar.Si.Conjunto

Esta función devuelve el número de elementos en los que rango de datos (primer argumento) son iguales al valor indicado por el criterio (segundo argumento) , el rango de datos del tercer argumento coinciden con el valor del criterio del cuarto argumento, etc.

Su Sintaxis: =Contar.Si.Conjunto(Rango1,Crit1, Rango2,Crit2, …)
Observe que los rangos y criterios van en pareja; es decir, el valor de cada criterio debe estar contenido o no, en el rango respectivo.
Según la sintaxis de la función, se puede usar para un número indeterminado de condiciones de conteo condicional.

Función Promedio.Si
A diferencia de la función Sumar.si o Contar.si, esta función viene a ser el cociente entre Sumar.si dividido por Contar.si.

Su sintaxis: =Promedio.Si(RangoDatos,Criterio,RangoPromedio)

Devuelve el promedio de todos los datos de RangoPromedio para todos los casos en los cuales RangoDatos contiene el valor del criterio dado en el segundo argumento.

Observe que el rango a ser usado para el promedio está en el tercer argumento.

Función Promedio.Si.Conjunto

Esta función devuelve el promedio de los datos contenidos en el primer argumento, en los que, los valores de cada criterio se encuentran en su correspondiente del rango de datos.

Su sintaxis: =Promedio.Si.Conjunto(RangoProm,Rango1,Crit1,Rango2,Crit2,…)

Ejemplo 11

En el siguiente segmento de hoja, Figura 1.25 se muestran los datos y algunas tablas:

a) Completar la primera tabla:
    Puesto que se trata de contar el número de ventas de la tienda Lago Plaza, debemos usar la función Contar.Si usando como rango de datos, $J$4:$J$23 y como valor de criterio, la celda P6. En consecuencia la fórmula a ser digitada en Q6 es: =Contar.Si($J$4:$J$23,P6). Compruebe que hay 4 ventas.

b) Completar la segunda tabla:
     En este caso, en Q11, se trata de saber cuántas ventas se realizaron en la tienda Plaza Norte, correspondiente a la zona Norte.
     En este caso la fórmula es:
     =CONTAR.SI.CONJUNTO($J$4:$J$23,$P11,$L$4:$L$23,Q$10)
     Compruebe que los resultados son diferentes a lo que se muestra.



c) Completar la siguiente tabla:
     Ahora se trata de usar la función Promedio.Si
     La fórmula a ser digitada en Q18 es:
     =PROMEDIO.SI($K$4:$K$23,P$18,N4:N23)

d) Completar la última tabla:
     Según la tabla, en la celda Q24 debemos hallar el promedio de todas las ventas realizadas el día jueves en el Distrito de San Borja. Esto se logra con la fórmula:

     =PROMEDIO.SI.CONJUNTO(N4:N23,$K$4:$K$23,$P24,$M$4:$M$23,Q$23)




Funciones lógicas

Función Si

Su sintaxis: =Si(Condición, Expr_SiEs_Verdadera, Exp._SiEs_Falsa)

Devuelve el resultado de ejecutar la expresión dada en el segundo argumento si la evaluación de Condición resulta verdadera; en caso contrario, devuelve el resultado de ejecutar la expresión que se da en el tercer argumento.[

Esta función puede ser tan compleja dependiendo del problema.

Cuando sea posible, use la función Contar.si, Sumar.si, etc. en lugar de usar la función Si.

Ejemplo12

=Si(14]12,12*5+8,12+5*8)                  Devuelve 68 ya que 14 es mayor que 12.
=Si(14[12,”Tarde”,”Mañana”)               Como la condición que se compara es falsa, devuelve la palabra Mañana.

Ejemplo 13

Suponga que A5 = 15; A6 = 08, A7 = 12, la función

=Si(A5]A6,A7*10,A7/10)                   En este caso devuelve 120
=Si(A5[=(A6+A7),A7*10,A7/10)       Aquí devuelve 120.    (A6+A7 = 20; A5 = 15)

=Si(Promedio(A5:A7)]10.5,”Aprobado”,”Desaprobado”)             Devuelve Aprobado


Función Y

Sintaxis: =Y(Condicion1,Condicion2,…)

Devuelve VERDADERO si todas las condiciones son verdaderas y devuelve FALSO si una de ellas o más, es falsa.

Ejemplo 14

Suponga que A5 = 15; A6 = 08,  A7 = 12, la función

=Y(A5]10,A7]10)    Devuelve VERDADERO ya que A5 y A7 son mayores a 10

=Y(A5]10,A6]10,A7]10)    Devuelve FALSO ya que A6 no es mayor que 10

Función O

Sintaxis: =O(Condicion1,Condicion2,…)

Devuelve VERDADERO si por lo menos una de las condiciones que se compara resulta VERDADERO. Esta función devuelve FALSO sólo cuando todas ellas resultan FALSAS.

Ejemplo 15

Suponga que A5 = 15; A6 = 08,  A7 = 12, la función
=O(A5]10,A6]10,A7]10)    Devuelve VERDADERO.

Ejemplo 16

Dado el siguiente segmento de hoja, mostrado en la figura 1.26, la nota final (columna F), se debe obtener de la siguiente manera: Si el promedio de las tres primeras notas es aprobatoria, este promedio es la nota final; en caso contrario, el promedio final se debe obtener tomando en cuenta la cuarta nota pero eliminando la nota más baja.
Tomar en cuenta que el promedio debe considerar el medio punto a favor del alumno.




Funciones de Texto

Estas funciones tienen efecto sobre una cadena de caracteres sea para convertirlas en mayúscula, minúscula, extraer una parte de ella, concatenar una con otra, etc.

A continuación desarrollaremos algunas funciones más conocidas y de uso común.

=Mayusc(Texto)=Minusc(Texto)=Limpiar(Texto)

Ejemplo 17

En el siguiente segmento de hoja se muestra el uso de algunas de estas funciones.



Las funciones que se han usado en cada caso son las siguientes:


En G3: =CONCATENAR(A3," ",B3,", ",C3)

En G7: =IZQUIERDA(A7,ENCONTRAR(" ",A7)-1)

En G11: =ESPACIOS(A11)


Funciones de búsqueda en tablas

Función BuscarV

]Sintaxis: =BuscarV(Dato_Buscado,Rango_Tabla,NCol,Tipo)

Devuelve el valor ubicado en la columna NCol, cuya fila contiene el dato que se busca en la tabla Rango_Tabla. Tipo puede ser VERDADERO si la tabla está ordenada o FALSO si no lo está.
Esto se puede entender también que con FALSO o 0 se pide una coincidencia exacta y con VERDADERO o 1, una coincidencia aproximada.

Función BuscarH

Sintaxis: =BuscarH(Dato_Buscado,Rango_Tabla,NFila,Tipo)

Devuelve el valor ubicado en la Fila NFila, cuya columna contiene el dato que se busca en la tabla Rango_Tabla. Tipo puede ser VERDADERO si la tabla está ordenada o FALSO si no lo está. Esto se puede entender también que con FALSO o 0 se pide una coincidencia exacta y con VERDADERO o 1, una coincidencia aproximada.

Ejemplo 18

El siguiente segmento de hoja, Figura 1.28, contiene un ejemplo de uso de estas funciones

Antes de calcular las celdas en blanco, démosle nombre a cada tabla:
Rango A2:D15 se llamará TabProd
Rango G2:J3 se llamará TabDes

Para obtener el precio del primer producto:
Digitar en B19: =BuscarV(A19,TabProd,2,0)    Esto devuelve 200

Para obtener el Monto del descuento: Primero extraemos el Tipo de descuento de la tabla TabProd. A continuación, usamos este resultado para buscarlo en la segunda tabla TabDes; cuando lo encuentre, extrae de la segunda fila el porcentaje del descuento. Este porcentaje multiplicado por el Precio, constituye el Monto del descuento.
Digitar en C19: =BuscarH(BuscarV(A19,TabProd,4,0),TabDes,2,0)*B19.



El Monto Neto es: =B19-C19
Copie el rango B19:D19 para los otros productos, hacia el rango B20:D21.

Funciones de Fecha y Hora

La siguiente lista muestra algunas de las funciones relacionadas con fechas y hora.

Ejemplo 19

Para poder usar las fórmulas se debe dar nombre de rango a las siguientes celdas:

B2: Nombre:     FechaHoy
B4:         FechaHoraHoy
B6:            Dia
B7:            Mes
B8:            Año

Ejemplo 20

Abra el archivo Fechas y estudie y analice el manejo de diversas funciones de fecha que se han utilizado en la hoja curiosidades.
Noten cómo se puede saber el día que se debe volver de un descanso, cómo se manejan los feriados y del mismo modo, la forma de hacer el seguimiento de los días de descanso.

Ejercicio 06

Ingrese en la celda A1 de una hoja vacía la función =Ahora().
Luego complete la siguiente tabla:

Funciones financieras

De la gran cantidad de funciones financieras que dispone el Excel, la siguiente lista presenta algunas de las más comunes.

El archivo Ejemplo de funciones financieras.xlsx contiene algunas funciones que se describen en las siguientes líneas.

Función Pago

Sintaxis: =Pago(Tasa,Nper,Va,[Vf],Modo)

Calcula el monto que se debe pagar en cada período por un préstamo realizado a una Tasa (en períodos), por el número de períodos Nper, al inicio del período (Modo = 0) o al final del período (Modo = 1).

El valor actual Va representa el monto del préstamo. El valor futuro es el saldo que queda al final de los pagos amortizados. Lo usual es que si se amortiza una deuda, el pago se completa en el último período y por tanto Vf = 0.

Función Tasa

Sintaxis: =Tasa(Nper,Pago,Va,[Vf],Modo)

Calcula la tasa que se debe pagar por período por un préstamo (Va), que se amortiza mensualmente (Pago)  al inicio o final del período.

Función NPer

Sintaxis: =NPer(Tasa,Pago,Va,[Vf],Modo)

Devuelve el número de pagos periódicos que se debe efectuar por un préstamo (Va) a una tasa (Tasa) periódica.

Función Va

Sintaxis: =Va(Tasa,Nper,Pago,[Vf],Modo)

Devuelve el valor actual de una inversión o préstamo. Es igual a la suma de una serie de pagos que se efectuarán en el futuro, durante los NPer períodos.

El siguiente segmento de hoja, Figura 1.30, muestra el uso de estas funciones:



Nota:
Los archivos:  Funciones financieras.doc y Funciones financieras.pdf contienen abundante información respecto de la mayoría de las funciones financieras contenidas en el MS Excel. Sugerimos que lo revisen.


Ejercicio 07

Abra el archivo Funciones de búsqueda y complete el cuadro de la hoja Ejemplo 1 usando los datos de la hoja Tablas.

Ejercicio 08

Usando el mismo archivo complete la tabla que se muestra en la hoja Ejemplo 2 usando los datos de la hoja BdPostulantes.

Primero resuelva para la lista de los códigos que aparecen en la columna J y luego modifique sus fórmulas a fin de que la consulta se haga para todos los postulantes.

1.3 ALGUNAS HERRAMIENTAS Y PROCEDIMIENTOS DEL EXCEL

MS Excel dispone de una gran variedad de procedimientos y herramientas que, complementados con las funciones, potencian la utilidad del mismo.

A continuación desarrollaremos de manera simplificada, el procedimiento de Ordenar y Filtrar datos así como diseñar Informes de tablas dinámicos. La herramienta Análisis de Datos será desarrollado extensivamente más adelante.

¿Qué es una base de datos?

Una base de datos puede ser entendida de manera simple como un conjunto de datos debidamente organizados. El hecho que estén “debidamente organizados” implica que poseen una estructura. Esta estructura puede ser del tipo jerárquica o relacional.

Una hoja electrónica como el Excel utiliza arquitectura de bases de datos del tipo relacional. Desde la óptica del Excel, un conjunto de datos, contenidos en una hoja  constituye una base de datos, si cada una de sus filas representa a un elemento (registro) de la base de datos y cada una de sus columnas (campos) describe a dicho registro.

En resumen, las filas identifican a los registros y las columnas constituyen los campos del registro.

En el siguiente segmento de hoja se muestra una parte de una base de datos.



Este segmento muestra una parte de una gran base de datos de empleado en la que cada fila identifica a un empleado y cada columna describe los atributos de dicho empleado. Cada campo tiene nombre o cabecera de columna.
En la siguiente figura 1.32 se muestra dos tablas relacionadas por el código de empleado de tal forma que se puede ingresar a una de ellas y extraer datos de un determinado empleado, mediante la clave de conexión, que en MS Access se denomina clave principal.



Desde el punto de vista del MS Access y otros gestores de datos, una base de datos es un repositorio de una o más tablas, relaciones, informes, formularios, etc. La versión 2007 del Excel utiliza este concepto de base de datos y le da el concepto de tabla a lo que en las versiones anteriores lo usaba como base de datos.

Si tuviéramos una tabla de postulantes a la Universidad de San Marcos o, si por otro lado se tratara de tabla de los clientes de un banco, estaríamos pensando en una gran masa de datos. En ese caso la recuperación de datos y las consultas representan un gran problema. De allí la importancia del buen tratamiento que debemos darle a los datos.

Por lo general, los dos grandes problemas que se tienen con una base de datos o simplemente tabla, son las operaciones de búsqueda y consulta. Buscamos un registro que cumpla con ciertos criterios, con la intención de extraer y copiar aquellos que cumplen dichos criterios; del mismo modo, consultamos por un determinado registro con la idea de informarnos. Por estas razones es importante el ordenamiento de los datos.

A continuación usaremos dos procedimientos del Excel: Ordenar y Filtrar datos.

Ordenar datos usando MS Excel 2003

Abra el archivo Relación de personal
Observe que cada fila constituye un registro el cual contiene los datos de un empleado, cada uno de los cuales es mostrado en una columna.

a)    Se desea ordenar por Departamento.
Use el siguiente procedimiento:
o    Haga clic al interior de los datos
o    Use: [Datos] - [Ordenar].
o    Complete la ventana según se muestra en la figura 1.33. Luego clic en [Aceptar]



b)    Ahora deseamos ordenarlo por Departamento, Sección y luego por Nombres

Complete los datos según se muestra en la figura 1.34.



Limitación de la versión 2003:

Según se muestra la imagen de la figura 1.34, en el MS Excel 2003 sólo podemos ordenar nuestros datos hasta por tres niveles o categorías. Como veremos a continuación, en la versión MS Excel 2007 esta limitación no existe.

Ordenar datos usando MS Excel 2007 y versiones siguientes

-    Abra el archivo Relacion de Personal.xls
-    Seleccionamos todo el rango de datos (En esta versión es suficiente dejar el cursor al interior de los datos).
-    Hacemos clic en [Ordenar y filtrar] del grupo [Modificar], de la ficha [Inicio]
-    A continuación hacemos clic en [Orden personalizado], con lo cual se obtiene la siguiente ventana:




-    En [Ordenar por] seleccionar Departamento y dejar [Criterio de ordenación] en A a Z para que ordene alfabéticamente de A a Z

Para resolver la parte b): Haga clic en [Agregar nivel] luego seleccione Seccion de la lista de [Ordenar por]. Vuelva a [Agregar nivel] y seleccione Apellidos. Finalmente haga clic en [Aceptar]

Ejercicio 09

Abra el archivo Balance mal consolidado. Podemos apreciar que los datos que se tienen en la hoja Consolidado, no se encuentran ordenados por meses: la columna B contiene el balance de Abril, cuando debiera contener el balance de Enero. Usando el comando [Datos], ordene esta hoja de manera adecuada.

Sugerencia para MS Excel 2007 y versiones siguientes::

Primero seleccione el rango B3:M43. Al ingresar a la opción [Orden personalizado] use [Opciones] para cambiar de columna a fila (De izquierda a derecha) como Opciones de ordenamiento y luego despliegue la lista de [Criterio de ordenación] y seleccione la opción [Lista]; finalmente seleccione la opción que se adecúe a los datos.


Filtrar registros

El Filtro automático sólo lo explicaremos debido a su sencillez.

Este procedimiento permite visualizar todos los registros que cumplen ciertos criterios al cual se conoce como filtrado de registros. El filtrado lo hace en la misma tabla, ocultando los que no cumplen con los criterios.

En Ms Excel 2003

Usando el mismo archivo, Relación de Personal.xls, use la secuencia:

[Datos] - [Filtro] - [Autofiltro]
A continuación tendrá un conjunto de botones al costado de los nombres de campos, como se muestra en la figura 1.36.




-    Haga clic en la fecha del campo [Departamento], de la lista, seleccione Mercado. ¿Qué resultado obtiene?
-    Para restablecer todos los registros se debe usar: [Datos] - [Filtro] - [Mostrar todo]. Esto permite deshacer el filtrado y volver a utilizar todos los registros.
-    Si ahora se desea tener a todos los empleados de Diseño y que pertenecen a la sección Fax, ¿qué debemos hacer? En la lista de [Departamento] seleccione Diseño; luego despliegue la lista de [Sección] y seleccione Fax.

En Ms Excel 2007, Ms Excel 2010 y Ms Excel 2013

Dejando el cursor al interior de los datos, haga clic en [Ordenar y filtrar] y luego [Filtro].

También puede acceder a filtro automático usando la secuencia: [Datos] - [Filtro]

Ejercicio 10

Usando el mismo archivo, resuelva las siguientes cuestiones:
a)    Todos los empleados cuyo sueldo anual es mayor a 30,000 y menor que 80,000
b)    Todos los empleados del Departamento de Administración, cuyo sueldo sea mayor que 30,000 y menor que 80,000
c)    Todos los empleados que ingresaron después de 1990
d)    Todos los empleados que ingresaron a partir del 10 de Octubre de 1985, pertenecen al departamento de administración o Ingeniería, pertenecen a la sección Copiadoras y su sueldo se encuentra por debajo de 30,000 o por encima de 90,000.


Ejemplo de filtro avanzado

En el caso de la opción de [Filtro avanzado] debemos tomar en cuenta lo siguiente:
-    Dónde se encuentran los datos a ser filtrados: Esto constituye el Rango de datos
-    Dónde se encuentran los valores a ser tomado como criterios: Rango de criterios
-    Dónde queremos que sean emitidos los resultados: Rango de salida.

Nota:
En Excel 2003 si lo que se desea es filtrar hacia otra hoja, el rango de datos debe tener un nombre de rango. En la versión 2003 no lo requiere.

Usemos el mismo archivo Relación de personal

En Excel 2003 use la secuencia: [Datos] - [Filtro] - [Filtro avanzado].

En MS Excel 2007 use la secuencia: [Datos] - [Avanzadas]
-    Complete los datos según la siguiente figura 1.37. Observe que estamos mostrando las ventanas obtenidas en ambas versiones del Excel. Luego haga clic en [Aceptar]



Tabla dinámica

Una tabla dinámica es una tabla de resumen. Si usamos la información contenida en el archivo Relación de personal.xls, podríamos obtener una tabla que contenga los montos anuales pagados en sueldo en una lista por departamentos, detallado por sección.

La característica de esta tabla, para ser llamado una Tabla Dinámica, es, la capacidad de modificar dinámicamente (en el instante) la estructura de la tabla original por otra en la que se podría tener la lista de empleados por Puesto, dentro de cada Sección, de cada Departamento y mostrando cada uno su sueldo anual.

Ejemplo usando Ms Excel 2003

Usemos el siguiente procedimiento:

-    Abra el archivo Relación de personal.xls.
-    [Datos] - [Informe da tablas y gráficos dinámicos] - Activar [Lista o base de datos de Microsoft Excel] y [Tabla dinámica] - [Siguiente]
-    En la siguiente ventana se debe tener el rango $A$6:$H$125 - [Siguiente]
-    A continuación active [En hoja de cálculo nueva] - Clic en [Diseño]



-    Complete el diseño de acuerdo a la figura 1.38. Para ello arrastre los botones (campos) hacia las posiciones (áreas de filas, de columna y de datos) según se indica en la figura.
-    Haga clic en [Aceptar]  y al volver a la ventana anterior, clic en [Finalizar].

Ejemplo complementario

La tabla obtenida es dinámica ya que podemos modificar su estructura según nuestras necesidades y gustos.

A partir de la tabla obtenida, vamos a generar otra que nos muestre los sueldos anuales pagados a los empleados distribuidos por Puesto, por Sección y por Departamento.

-    Para ello arrastre el botón del campo [Sección] y suéltelo debajo del campo  [Departamento]; arrastre el botón del campo [Puesto] y suéltelo debajo del campo [Sección] y finalmente arrastre el botón del campo [Apellido] y suéltelo debajo del campo [Puesto].

Ejemplo usando Ms Excel 2007 , Ms Excel 2010 y Ms Excel 2013

Abra el archivo Relación de personal. Deje el cursor dentro de los datos.
Use la secuencia:
 [Insertar]-[Tabla dinámica]. A continuación verá la siguiente ventana de diálogo:
Como puede ver, ya el rango de datos está seleccionado. Dejemos que la tabla dinámica lo cree en una nueva hoja. Haga clic en [Aceptar].




Esta es la ventana que se obtendrá a continuación que, como puede apreciar es totalmente diferente a la ventana del Excel 2003.




En el lado izquierdo tenemos la estructura vacía de la tabla. En el lado derecho está la lista de los campos de la tabla y en su parte inferior espacios vacíos donde se irán insertando los campos que seleccionemos. Los campos categóricos (que hacen referencia a variables cualitativas) pueden ser insertados en los rótulos de fila, columna o informe (cabecera). Si se coloca en Fila, los valores del campo se desplegarán por fila; se desplegarán por columna si se inserta como rótulo de columna y, cada imagen (tabla) mostrada corresponderá a un valor o a todos los valores de la variable que se coloque como rótulo de informe.

En este ejemplo, haga clic en la casilla del campo Departamento y observe que dicho campo se inscribe en el rótulo de fila y en la tabla vacía ya tenemos los valores de este campo.

Ahora haga clic en la casilla del campo Sueldo Anual y observe el rótulo de Valores y también la situación de la tabla.

Finalmente arrastre el campo Sección y suéltelo en el rótulo de columna (si sólo hace clic en su casilla se inscribirá debajo de Departamento, en el rótulo de fila).

Si desea cambiar la estructura de esta tabla, es suficiente arrastrar el campo indicado y soltarlo fuera de los rótulos.

1.4.    MACROS

Todas las operaciones que se realiza en Excel, están formados por una serie de acciones debidamente secuenciadas. Este conjunto de acciones constituye un procedimiento. Este procedimiento puede ser, por ejemplo, calcular una determinada columna de totales para un conjunto de datos almacenados en celdas prefijadas; preparar un formato de ingreso de datos; configurar un área de impresión; extraer una parte de una base de datos a otra hoja usando el filtro avanzado; etc.
Mucho de estos procedimientos se deben realizar continuamente, siempre sobre las mismas celdas y con las mismas celdas, aunque con diferentes datos.

Una forma de simplificar este tipo de trabajo repetitivo y hasta tedioso, es mediante la creación de Macros. El programa Excel se convierte en un buen entorno para ejecutar nuestras macros.

Una macro es un procedimiento que permite automatizar una secuencia de acciones realizadas sobre una hoja de cálculo o un libro, o sobre grupo de ellos. Automatizar significa que dicho procedimiento puede ejecutar la serie acciones de manera automática y en forma repetitiva y siempre sobre las mismas celdas.

En Excel es suficiente asociar dos teclas o crear un botón, para ejecutar una macro.

A continuación crearemos algunas macros de uso general. Posteriormente estaremos en capacidad de crear macros más sofisticadas de tal forma que podamos automatizar ciertos cálculos repetitivos en la solución de problemas estadísticos.

La siguiente sección nos permitirá usar el lenguaje VBA para potenciar una macro.

Cómo crear Macros

Crear una macro significa grabar la secuencia de acciones que constituyen la macro. Una macro ya grabada puede ser ejecutada, modificada, o eliminada.

En MS Excel 2003

Para grabar una nueva macro se dispone de dos formas:

-    Usando la secuencia: [Herramientas] - [Macros] - [Grabar nueva macro]
-    Usando la barra de herramientas del Visual Basic.

Barra de herramientas de Visual Basic

Para disponer en pantalla de la barra de herramientas del Visual Basic, use
[Ver] - [Barra de Herramientas] - [Visual Basic]



Iniciar la grabación de una macro


Active la grabadora de macros. Para ello use uno de los siguientes procedimientos:
-    [Ver] - [Barra de herramientas] - [Visual Basic]. En esta barra haga clic en    .
-    [Herramientas] - [Macro] - [Grabar nueva macro]
-    Si está presente la barra de herramientas del Visual Basic, haga clic en    .

En la ventana que se obtenga complete la información como se indica en la siguiente figura.



La macro puede grabarse en el libro en uso, en un libro nuevo o en el libro de  macros personal.

Después de hacer clic en [Aceptar], obtendrá el siguiente botón:



Si desea que su macro se ejecute siempre a partir de una celda fija, el botón de “Referencia relativa” (botón del lado derecho) debe estar desactivado. Pero si lo que desea es que la macro se ejecute a partir de la celda que Ud. desee, haga clic en el botón “Referencia relativa” para activarla.

Ahora ya está en capacidad de iniciar la grabación de las acciones que debe ejecutar su macro.

En Ms Excel 2007 , Ms Excel 2010 y Ms Excel 2013

En esta versión también dispone de dos formas para acceder a la ventana que le permita definir el nombre de la macro y el método abreviado para ejecutarla:

Primera forma:

Use la siguiente secuencia: [Vista] - [Macro] - [Grabar macro]. Obtendrá una ventana de diálogo similar a lo descrito líneas arriba.

En el lado izquierdo de la barra de estado del Excel verá un pequeño botón cuadrado, similar a lo comentado líneas arriba, que le permitirá detener la grabación de la macro, cuando así  lo desee.

Segunda forma

(necesario cuando se requiere usar el cuadro de controles de formulario o ActiveX, que se encuentran en la ficha [Programador]):

Nota:
En Ms Excel 2013 la ficha [Programador] no existe; en su lugar s encuantra la ficha [Desarrollador].

Para activar la ficha [Programador] use [Opciones de Excel] - Clic en [Mostrar ficha programador].

Hacer clic en la ficha [Programador] y luego clic en [Grabar macro] en el grupo [Código].


Ejemplo 21

La explicación corresponde a la versión 2003, pero que no se diferencia mucho al hacerlo en la versión 2007.

Primero abra el archivo Ejemplo 01. Grabe una macro llamada Mac01, que permita calcular la columna de Venta Total y el Porcentaje de contribución. Que el método abreviado usado sea [Ctrl]+[k]

Solución:
Haga clic en el botó   para iniciar grabación de la macro (En el 2007: [Programador] - [Grabar macro] en el grupo Código)

Complete la ventana que sigue como se indica en la figura 1.44



Ahora realice las siguientes operaciones:
-    Haga clic en C4
-    Ingrese la fórmula: =B4*$C$1 y presione [Enter]
-    Copie el contenido de C4 hacia el rango C5:C15
-    En C16 ingrese la fórmula: =Suma(C4:C15) y presione [Enter]
-    Haga clic en D4.
-    Ingrese la fórmula: =C4/$C$16 y presione [Enter]
-    Copie esta fórmula hacia el rango D5:D15.

Para detener la grabación de la macro haga clic en [Detener grabación]   (En el 2007, haga clic en el mismo botón, pero que se encuentra en el lado izquierdo de  la barra de estado).

Antes de ejecutar esta macro, borre el contenido del rango C4:D16.

Para ejecutar la macro presione [Ctrl] + [k]

Guardar un libro que contenga macros

En el caso de la versión 2003:

Guarde el libro con el mismo nombre o con el nombre que Ud. desee.

En el caso de la versión Excel 2007 y las versiones siguientes:

Guarde el libro usando [Botón del Office] - [Guardar como] - [Libro de Excel habilitado para macros], en cuyo caso, la extensión del libro es “xlsm”. Si no se guarda usando esta opción, se perderá la macro.

Nota:
Las macros grabadas en la versión 2003 pueden ser ejecutadas en las versiones siguientes, pero no al revés.

Ejemplo 22

Grabe una macro llamada Mac02 que permita obtener la columna Ingreso para cada una de las tiendas de la hoja Ejemplo 2 del libro Ejmacros01.xls. Método abreviado: [Ctrl]+[m]

Solución

Antes de iniciar con la grabación de la macro, debemos tomar en cuenta la forma de resolver el problema.

La hoja Ejemplo 2 contiene la cantidad vendida de ciertos productos en cada una de las tiendas. Se necesita calcular el Ingreso obtenido. Puesto que el ingreso es el precio del producto por la cantidad vendida, y el precio de cada uno de los productos lo tenemos en la hoja Tabla de productos, debemos usar la función BuscarV para obtener el precio de los productos y multiplicarlo por la cantidad. Felizmente la hoja Tabla de productos ya tiene definido a todo el rango como TabProd y el precio está en su columna 5. Esto significa que en C5 de la hoja Ejemplo 2, debemos ingresar:
=BuscarV($A5,TabProd,5,0)*B5

Nota:
Usamos $A5 porque deseamos copiar esta fórmula para las otras columnas.

A continuación se copia hacia abajo y a las otras columnas de Ingreso y se termina el problema.

Pasemos a iniciar la grabación de la macro:
-    Use una de las formas de iniciar la grabación de una macro, en [Nombre de macro] ingrese Mac02; en [Método abreviado] ingrese m; que la macro quede grabada en [Este libro]. Ahora haga clic en [Aceptar].
-    Haga clic en C5. Ingrese la fórmula: =BuscarV($A5,TabProd,5,0)*B5
-    Copie hacia el rango C6:C13
-    Ahora copie el rango C5:C13 y pegue en las columnas de ingreso de las otras tiendas usando [Ctrl]+v que es más sencillo y directo.

Borre todos los cálculos realizados y ejecute la macro usando [Ctrl] + [m]

Ejemplo 23

Grabe una macro llamada Mac03 que permita crear, en la hoja Ejemplo 3 del libro EjMacros01.xls, el formato de tabla mostrado en la hoja Ejemplo 2. Método abreviado: [Ctrl]+[r]. Grabe una nueva macro llamada Mac03A, sin la opción Referencia relativa. Método abreviado: [Ctrl] + [y]. La macro debe borrar todos los formatos aplicados con la macro Mac03. Grabe otra macro llamada Mac03B en el [Libro de macros personal] a fin de crear el formato en un nuevo libro. Método abreviado: [Ctrl]+[o]. La macro debe hacer exactamente lo mismo que Mac03.

Solución

-    Inicie grabación completando la ventana según se pide en el ejemplo.
-    Ingrese los siguientes datos: En B2: CALCULO DEL INGRESO POR TIENDA y POR PRODUCTO; en B3, D3 y F3 ingrese Tienda 1, Tienda 2, Tienda 3 y Tienda 4; en B4, D4,  F4 y H4; ingrese Cantidad; en C4, E4,  G4 y I4; digite Ingreso ; en A4 ingrese Productos. En A5 ingrese “Papa blanca”
-    Seleccione el rango A5:A13 y haga que tenga borde de cuadro grueso. Haga lo mismo con las otras columnas. Las columnas C, E, G, I (del mismo rango, deben tener color de fondo: celeste.
-    Finalmente seleccione B3:C3 y haga clic en combinar celdas. Repita esto con las otras tiendas.
-    Detenga la grabación de la macro

Para grabar la macro Mac03A, sin usar [Referencia relativa] asegúrese que dicho botón esté desactivado antes de iniciar con la primera acción dentro de la macro.

Para grabar la macro Mac03B en el [Libro de macros personal] en la ventana de [Grabar macro] seleccione esta opción.

Para ejecutar esta macro, primero grabe el libro en uso (si lo desea, pero habilitado para macros). Cierre todos los libros. Ahora proceda a cerrar el Excel. Tenga cuidado al cerrar el Excel. Allí le pedirá si desea actualizar el libro de macros personal. Haga clic en [Si].

Abra el Excel vacío y use el método abreviado para ejecutar la macro.

Ejemplo 24.

Macro para el uso de [Formularios] del comando [Datos]

Grabe una macro llamada Mac04 que permita ingresar datos a la hoja Ejemplo 4, usando el Formulario del Excel. La macro debe estar en el libro de macros personal. Luego de ingresar los datos debe grabar y cerrar el libro. Método abreviado: [Ctrl]+[t]

Solución:

Ante todo, luego de acceder a la hoja llamada Ejemplo 4, elimine las dos primeras filas que no contienen datos (esto es necesario para el uso de formularios).
Iniciar la grabación de una nueva macro. Darle el nombre Mac04, método abreviado: [Ctrl]+[t]. [Guardar esta macro]: en el libro de macros personal [Aceptar].

Seleccione el rango A1:I11
Use la secuencia: [Datos] - [Formulario]
Ingrese los datos (cualquier valor como ejemplo). Use flecha abajo para cambiar a otro registro. Repita el llenado de algunos datos más.
Al terminar, haga clic en el botón [Cerrar] del formulario  y detenga la grabación de la macro.
Borre los datos ingresados y ejecute la macro grabada.

Nota:

Puesto que la macro se va a grabar en el Libro de Macros personal, debe tener cuidado al cerrar el Excel, pues será el momento en que se nos pregunte si deseamos grabar la modificación del libro de macros personal. En este caso haremos clic en [Si]. Claro que si no desea guardar las modificaciones hechas, hará clic en [No]
 

Ejemplo 25.

Macro para filtro avanzado

Grabar una macro para extraer una parte de la hoja Tabla de productos del libro EjMacros01. Esta macro debe realizar el procedimiento del filtro avanzado para extraer algunos datos hacia la hoja Ejemplo 5. Como ejemplo, extraiga todas las ventas cuya categoría es Legumbres.

Solución

-    Copie los nombres de campo de la hoja Tabla de productos (rango B3:F3) hacia el rango B2:F2 de la hoja Ejemplo 5.
-    Digite Legumbres en D3.

Observación importante:

Todo el rango de los datos debe tener un nombre de rango. En este caso se llama Productos.
-    Inicie la grabación de la macro: Nombre: Extraer; método abreviado: [Ctrl]+[j]
-    Como ya estamos en grabación, usaremos el procedimiento de Filtro avanzado: [Datos] - [Filtro] - [Filtro avanzado]. Active [Copiar a otro lugar]. En [Rango de la lista] digite Productos. En [Rango de criterios] seleccione el rango B2:F3. En [Copiar a] Haga clic en [B5]. Finalmente haga clic en [Aceptar] y luego detenga la grabación de la macro.
-    Para probar, borre sólo la cabecera del rango de salida (B5:F5) y ejecute la macro grabada.

Para modificar la extracción, digite en C3, Kilo; en F3: [4.0. Borre el contenido de D3. A continuación vuelva a borrar la cabecera B5:F5 y ejecute la macro.

Ejercicio 11

Grabe una macro que permita borrar la cabecera del rango de salida de la macro creada en el ejemplo anterior. Use las dos macros de manera apropiada. Vuelva a grabar el libro habilitado para macros.

1.5 EL LENGUAJE VISUAL BASIC PARA APLICACIONES

Introducción

En algunos de los ejemplos de macro que hemos grabado en la sección anterior se presentaba la necesidad de cambiar alguna parte de la macro para que ésta pudiera realizar su trabajo con algún cambio.

Pero este cambio no puede darse pues la macro es rígida y siempre realiza las mismas operaciones sobre las mismas celdas; jamás cambia, salvo si cambiamos los datos. En el ejemplo 25 hemos grabado una macro que realiza el procedimiento de filtro avanzado. El rango de criterio que usa es B2:F3. ¿Y si quisiéramos que extraiga las ventas de Frejol canario o Frejol caballero? Esto implicaría modificar el rango de criterio de B2:F4.

Del mismo modo, si quisiéramos que la salida se obtenga en otra parte de la misma hoja, deberíamos cambiar de la celda inicial que, en el ejemplo mencionado es B5.

Por esta razón, si pudiéramos ingresar como datos el rango de criterio así como la celda inicial de la salida e incluso decidir en qué hoja debe dejar el resultado del filtrado, la macro adquiriría una gran potencia y utilidad.

Esto se puede lograr modificando la macro. Pero esto implica modificar el programa que está por detrás de la macro. Dicho programa o procedimiento está compuesto por un conjunto de instrucciones u órdenes escritas en el lenguaje del Visual Basic Applications (VBA), propio de los objetos de Microsoft Office y válidos globalmente en el Word, Powerpoint, Excel, etc. Claro que cada uno de ellos tiene sus propios métodos y objetos.

En el caso del Excel, mediante el Lenguaje del VBA podemos hacer uso de celdas, rango de celdas, columnas, filas, gráficos, hojas, libros. Cada uno de estos elementos constituyen objetos, los cuales poseen un conjunto de propiedades, capaces de sufrir cambios mediante el uso de los métodos (procedimientos o funciones).

Para modificar una macro, para insertar un módulo o escribir el código de un procedimiento o para codificar funciones, se debe abrir el Editor del Visual Basic. Esto se hace por cualquiera de las siguientes formas:

En el Excel 2003

-  [Herramientas] - [Macro] - [Editor de Visual Basic]

-  [Alt]+  [F11]

-  Haciendo clic en el botón     de la barra de herramientas de [Visual Basic]

En el Excel 2007 y versiones siguientes

- Ficha [Programador, Desarrollador] - [Visual Basic] del grupo código

-  [Alt] + F11


El Editor del Visual Basic

La figura 45 muestra la ventana del Editor del Visual Basic. Haremos una breve descripción de los principales elementos de esta ventana.

La ventana del Explorador de proyectos:

Muestra una lista de proyectos correspondientes a los objetos del Excel así como los libros que el usuario tiene abiertos. Del mismo modo, en la parte inferior de la lista se muestra las carpetas en los cuales se alojarán los módulos creados de manera directa o durante la grabación de una macro; allí también se alojarán la carpeta de los formularios. En la parte superior se dispone de los botones de Ver Código    , que permite editar el módulo; Ver Objeto   , que permite volver al Excel y Alternar carpetas , que permite pasar de un proyecto a un elemento del mismo (del libro a una hoja, por ejemplo)
 

La ventana de propiedades

Contiene un conjunto de propiedades pertenecientes a los diferentes objetos contenidos en el Excel. Estas propiedades se pueden modificar directamente o mediante la programación en los procedimientos contenido en un módulo.



Editar un módulo

En la ventana de proyectos, ubicamos la carpeta Módulo; luego de abrir esta carpeta veremos al interior uno o más módulos; al hacer doble clic en uno de ellos, tendremos al lado derecho, el contenido del módulo seleccionado.

También puede ver el código seleccionando el módulo y haciendo clic en [Ver código], como se indica en la figura 1.45.

Crear un módulo o función

Estando en la ventana del Editor del VBA, use [Insertar] - [Módulo]

A continuación, en la ventana de proyectos aparecerá una nueva carpeta Módulo. Al abrir esta carpeta aparecerá Módulo1. Si hacemos doble clic en Módulo1, en el lado derecho tendremos el contenido de este módulo el cual estará formado por uno o más procedimientos. Si la carpeta módulo ya existía entonces sólo se agregará un nuevo módulo dentro de dicha carpeta y aparecerá inscrito el primer módulo con el nombre Módulo1.

Un procedimiento se identifica con las palabras Sub   y End Sub

Sintaxis de un procedimiento:

[Private / Public  [ Static] Sub NombreDelProcedimiento[(Argumentos)]

-  Instrucciones

-  

End Sub

Todo aquellos que está entre corchetes se considera opcional. Por ello podemos apreciar que todo procedimiento tendrá como mínima sintaxis:

Sub Nombre()

End Sub

Se usará Private si el procedimiento es accesible sólo para los procedimientos contenidos en dicho módulo.
Se usará Public si se desea que el procedimiento sea accesible por todos los procedimientos de todos los módulos.

Se usará Static si las variables locales se conservan en diferentes llamadas al procedimiento.

Sentencias de uso frecuente

Haremos una muy breve enumeración de algunas sentencias del VBA que nos permitirán el uso de los objetos contenidos en el Excel.

Para crear un módulo debe digitar: Sub NombreModulo y presionar [Enter]. Automáticamente obtendrá:

Sub NombreModulo()

End Sub

Al interior de estas dos sentencias deberá escribir todo lo que el módulo debe hacer.  Si desea crear una función, deberá digitar: Function NombreFuncion [(…..)] y presionar [Enter], luego del cual obtendrá

Function Nombre ([….])

Los corchetes indican que su contenido es opcional
...
Nombre = Expresión

End Function
Si la función requiere de argumentos, debe ingresarlos dentro de los paréntesis.

La siguiente tabla muestra una lista de algunas instrucciones de fácil comprensión y las que nos permitirá modificar algunas macros de forma que éstas sean interactivas y se conviertan en macros de mayor potencia.
 

Ejemplo 26:

Responder la pregunta hecha en el párrafo 2, de la página 82.

- Abra el archivo Ej Macros01.
-    Usando [Alt] - [F11] abra el Editor del VBA
-    Abra la carpeta Módulos y seleccione el módulo que contiene el procedimiento relativo a la macro Extraer.
-    Cambie el rango “B2:F3” por “B3:F4”
-    Cambie también “B5” por “B10”
-    Ahora ingrese Frejol canario en B3 y Frejol caballero en B4
-    Ahora ejecute la macro usando [Ctrl] + [j]

Ejemplo 27:

Abra el archivo Fármacos
Escriba un módulo procedimiento Listado que le permita extraer todos los productos farmacéuticos que empiezan con los caracteres ingresados desde el teclado. El módulo debe añadir una nueva hoja y darle nombre Lista. En ella debe dejar los resultados.

Solución

Luego de insertar un nuevo módulo en la ventana del Editor, digite: Sub Listado y presione [Enter]. A continuación escriba las siguientes instrucciones:

‘
    Sheets.Add
    ActiveSheet.Name = "Lista"
    Sheets("Articulo").Select
    Range("A1:D1").Select
    Selection.Copy
    Sheets("Lista").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
'   Ingresaremos el dato hacia la variable Texto
    Cadena = UCase(InputBox("Ingresa parte de la descripción del producto"))
'   Reduciremos los espacios en blanco de la cadena ingresada
    Cadena = Trim(Cadena)
    NChar = Len(Cadena)
    Sheets("Articulo").Select
    Range("B2").Select
    NReg = Range(Selection, Selection.End(xlDown)).Count
    I = 2
    K = 1
    With Sheets("Articulo")
    While I [= NReg
        If Cadena = UCase(Trim(Left(Cells(I, 2), NChar))) Then
            K = K + 1
            Sheets("Lista").Cells(K, 2) = .Cells(I, 2)
            Sheets("Lista").Cells(K, 3) = .Cells(I, 3)
            Sheets("Lista").Cells(K, 4) = .Cells(I, 4)
        End If
        I = I + 1
    Wend
    End With
Sheets(“Lista”).Select
Range(“E3”).Select

1.6 CONTROLES Y FORMULARIOS

La utilización de macros para automatizar una serie de procedimientos manuales en un libro nos proporciona grandes ventajas. Puesto que las macros son bastante rígidas, ya vimos que, mediante algunas instrucciones del VBA podemos darle mayor potencia e interactividad a las mismas. Sin embargo, si en un mismo libro tenemos varias macros, puede ocurrir que las letras disponibles no son suficientes o podríamos confundirnos de una macro con otra.

Para resolver esta dificultad disponemos de los llamados Controles que se encuentran en la barra de herramienta de Formularios y también en la barra de Herramientas del Cuadro de Controles Activex, aunque los de mayor sencillez en su uso son los primeros.

En MS Excel 2003

Usando la secuencia: [Ver] - [Barra de herramientas] - [Formularios] obtenemos la barra que se muestra a continuación:



En MS Excel 2007

Usando la secuencia: [Programador, Desarrollador] - [Insertar], obtenemos la lista de todos los controles (de formulario y ActiveX); será suficiente hacer clic en uno de ellos y trazar un recuadro en la hoja activa. Aquí usaremos sólo los controles de formularios.

Al hacer clic en cualquiera de los mencionados, debemos trazar un pequeño rectángulo (puede ser el ancho de una celda).

En el caso del botón [Botón], al soltarlo se despliega una ventana donde aparece una lista de macros disponible. Se seleccionará macro deseada y luego hacer clic en [Aceptar]. En el caso de otros controles, se deberá hacer clic con el botón derecho del ratón para obtener un menú contextual en el cual se distingue dos opciones

-    Asignar macro
-    Formato de control

El primero le permite asignar el botón a una macro que aparecerá en la lista (si en el libro hubiera alguna). Luego de seleccionar la macro, hacer clic en [Aceptar].

El segundo nos permite pasar a una nueva ventana, hacer clic en la ficha [Control] y definir (común a todos los controles) el rango o nombre de rango asociado a este control y la celda de la hoja con la que se desea vincular el resultado del uso del control.

Es importante tomar en cuenta esta celda de vinculación. Por ejemplo si se trata de usar el control Cuadro combinado, se deberá ingresar el rango de la lista de los datos, cuando se use el botón se visualizará dentro del mismo, la lista, y al seleccionar a uno de los elementos, el número de este elemento en la lista, se visualizará en la celda con la cual se vinculó el control.


Al lado del uso de los botones de control de formulario que permite asignar éstos a una macro, también podemos aprovechar las opciones que nos permite dicho botón; pero esto se consigue mediante el uso de la función Indice, no vista antes.

Ejemplo de la función Indice:

Sintaxis de la función Indice:  =Indice(Rango,NúmeroDeElemento)

Explicación: Devuelve el valor del elemento cuya posición dentro de Rango está indicada por NúmeroDeElemento.
Supongamos que se tiene el siguiente segmento de hoja mostrada en la figura 1.47.




a)    Para saber el nombre del mes que se encuentra en la posición 7 de la lista dada por el rango A3:A14, en B18 digitamos: =Indice(A3:A14,9). Esto devuelve Noviembre.
b)    Suponga que en la celda B17 se desea tener la máxima venta del rango B3:B14, correspondiente a la Tienda 1. Entonces en B17 se debe ingresar =Max(B3:B14). Si en B18 se desea el mes de máxima venta, se deberá digitar:
=Indice(A3:A14,B18).


Volvamos a los controles

Ejemplo para asignar el control Botón a la macro Mac01 creada en el Ejemplo 21.

-    Abra el archivo Ej Macros01.
Active la barra de herramientas de Formulario.
-    Haga clic en el botón [Botón] y trace en una parte de la hoja un rectángulo. Al soltar el rectángulo, se genera inmediatamente una ventana conteniendo la lista de todas las macros existentes en el libro activo.
-    Seleccione Mac01 y luego haga clic en [Aceptar]
-    Haga clic en el botón para cambiar el nombre, digamos por “Calcular”.
-    Después de desactivar la selección (puede hacer clic en otro lugar), verá que aparece una manito. Haga clic para ejecutar la macro Mac01.

Ejercicio12

Asignar un botón Botón cuyo nombre debe sea Filtrar y asignarlo a la macro Extraer, creado en el Ejemplo 25.

Ejercicio13

Asignar un botón Botón cuyo nombre sea Borrar y asignarlo a la macro creada en el Ejercicio 11.


Ejemplo de uso del botón Cuadro combinado

El botón Cuadro combinado es bastante útil si se trata de seleccionar un elemento de una lista. A partir de esta selección, se puede obtener información relacionada directa o indirectamente.

Abra el archivo Uso de teléfono
Deseamos obtener en la hoja Control de llamadas, la información de un empleado insertando un botón de cuadro combinado para sus apellidos, a partir del cual, debemos obtener los otros datos de dicho empleado, como se presenta en la hoja Muestra.

Para ello siga el siguiente procedimiento:

-    Active la hoja Control de llamadas.
-    Luego de hacer clic en el botón de Cuadro combinado, trace un rectángulo en la celda C8.
-    Presione el botón derecho; seleccione [Formato de control] - [Control]
-    En la siguiente ventana, ingrese Nombres en [Rango de entrada] y en [Vincular con la celda] digite C4.
-    Desactive la selección del botón haciendo clic en otra celda. Ahora haga clic en el botón, despliegue la lista y seleccione uno y vea en C4.

Ahora vamos a usar el número de empleado para extraer toda la información solicitada de dicho empleado. La tabla que contiene los datos de la hoja Personal, se llama Personal.

-    En C6 digite: =BuscarV(C4,Personal,2,0). Esto extraerá el código del empleado

Debemos extraer primero el código del cargo de la tabla Personal y luego lo buscamos en la tabla Cargos de la hoja Cargo. Como =BuscarV(C4,Personal,4,0) nos devuelve el código del cargo, debemos buscar éste código en la otra tabla.

-    Para ello digitamos en C11:
-    =BUSCARV(BUSCARV(C4,Personal,4,0),Cargos,2,0)
-    Para el sueldo, en C13 digitamos:
-    =BUSCARV(BUSCARV(C4,Personal,4,0),Cargos,3,0)
-    Para el sueldo en dólares, en G13 digitamos: =C13/TCambio
-    Para obtener el nombre del departamento, debemos extraer primero el código del departamento de la tabla Personal y buscar este código en la tabla Depto de la hoja Departamento. Esto lo hacemos ingresando en C15 la fórmula: =BuscarV(BuscarV(C4,Personal,5,0),Depto,2,0)
-    Finalmente obtendremos el Nivel de restricción digitando en C17, la fórmula: =BUSCARV(BUSCARV(C4,Personal,6,0),Restric,2,0)

1.7 PROBLEMAS PROPUESTOS

1.    Escriba un procedimiento (macro) que lea dos números y que imprima usando MsgBox la suma, el producto y el cociente del segundo entre el primero.

2.    Vuelva a codificar el problema anterior pero contemplando la posibilidad de que no debe dividirse por 0. Si así fuera debe emitir el mensaje: “Divide por cero”.

3.    Escriba un código que permita leer n pares de números. En cada iteración debe leer los dos número y calcular la suma, el producto, la suma de las raíces cuadradas de cada uno de ellos. Al final debe imprimir usando MsgBox los cálculos realizados.

4.    Escriba un procedimiento que permita leer una cadena de caracteres de la forma: aaa.aa, bbb.bb. El procedimiento debe separar los números y en cada iteración debe hallar la suma de cada uno de ellos. El procedimiento termina cuando se lee una cadena vacía.

5.    Escriba un procedimiento que lea una cadena de caracteres. Que cuente el número de vocales y consonantes contenidos en dicha cadena y los imprima usando el MsgBox.

6.    Escriba un procedimiento que lea n cadenas de caracteres y que use un contador para saber cuántos dígitos pares e impares se ingresó. Debe contar también el número cada una de las vocales y el número de consonantes.

7.    Escriba un procedimiento que abra el archivo Emple. Que se ubique en la hoja Datos y que inserte una nueva hoja. Que inserte otra hoja antes de la primera y otra después de la última hoja.

8.    Escriba un procedimiento que active el libro Emple.xlsx que fue abierto en el problema anterior y que borre la hoja Tempo; que le ponga el nombre Tempo a la primera hoja y que lo grabe con el mismo nombre y termine cerrándolo.

9.    Escriba un procedimiento que permita ejecutar un filtro avanzado usando los criterios definidos en la hoja Criterios del archivo Emple .

10.    Escriba uno o más procedimientos que le permitan construir una calculadora que tenga diversas opciones de cálculo así como crear gráficos. Vea el Calculadora Ice.

Siguiente sesión.