0

MANUAL

1

CONCEPTOS BÁSICOS

Pantalla Inicial

Libros, hojas y celdas

Excel es el programa de hoja de cálculo más utilizado del mundo y es parte de la suite Microsoft Office. Existen otros programas de hoja de cálculo en el mercado, pero Excel se ha convertido en el líder indiscutible.

Excel nos permite manipular datos de muchas maneras ya sea para crear un presupuesto, gráficas, importar datos desde diferentes fuentes, realizar un análisis financiero, crear tableros de mando y muchas cosas más. Excel es una herramienta muy versátil y de un gran potencial.

Archivos de Excel

Todo el trabajo que se realiza en Excel se guarda en un archivo, el cual también es conocido como libro y aquí es donde viene el concepto más importante: Cada libro de Excel está formado por hojas y cada hoja a su vez está dividida en múltiples celdas.

2

Muchos usuarios nuevos de Excel se sienten intimidados al principio por estos términos, pero es de mucha importancia que los comprendas y que te familiarices pronto con ellos. Para ayudar en este proceso te mostraré cómo se reflejan estos conceptos dentro del programa.

Libro de Excel

En la parte superior de la ventana de Excel puedes observar que de manera predeterminada se nombra a un nuevo archivo como Libro1. Ese nombre lo podrás cambiar después, pero debes recordar que un archivo de Excel también es conocido como un libro.

Hojas de un libro

Ahora observa la parte inferior izquierda de la ventana de Excel y notarás unas pestañas con los nombres Hoja1, Hoja2 y Hoja3.

Esto quiere decir que este libro tiene tres hojas. Después aprenderemos a eliminar alguna de ellas o agregar más hojas, solo recuerda que un libro puede estar formado por una o más hojas.

Celdas de una hoja

Finalmente observa que la parte central de la pantalla es una cuadrícula que forma pequeños recuadros. Cada uno de estos recuadros es conocido como celda.

3

Para moverte entre dichas celdas puedes utilizar las flechas del teclado ya sea para moverte hacia arriba o para moverte hacia abajo, hacia la derecha o hacia la izquierda. Así que no olvides este concepto básico de Excel: Un libro está formado por hojas y cada hoja está dividida en celdas.

Columnas y filas

Las hojas de Excel están formadas por columnas y filas las cuales dan forma a las celdas. De esta manera cada celda tiene una dirección única dentro de la hoja que está precisamente definida por la columna y la fila done está ubicada.

Columnas de una hoja de Excel

Las hojas de un libro de Excel 2010 tienen un máximo de 16,384 columnas y están identificadas por letras siendo la última columna la XFD. Este máximo de columnas está presente desde la versión 2007.

En versiones anteriores, como la versión de Excel 2003, el máximo de columnas de una hoja era de 256.

Filas de una hoja de Excel

En Excel 2010 podemos tener hasta 1,048,576 filas lo cual nos da el espacio necesario para la mayoría de nuestras necesidades.

4

De igual manera, en las versiones anteriores a Excel 2007 el máximo de filas permitidas en una hoja era de 65,536.

Dirección de una celda

Cada celda de una hoja tiene una dirección que estará formada por la columna a la que pertenece seguida por el número de fila. Por ejemplo la dirección de la siguiente celda es D7 porque dicha celda se encuentra bajo la columna D y pertenece a la fila 7.

La dirección de una celda también la puedes observar en el cuadro de nombres.

5

La cinta de opciones

A partir de Excel 2007 se introdujo un nuevo elemento a la interfaz de usuario de Excel y que es conocido como la cinta de opciones la cual nos da una nueva manera de organizar los comandos dentro del programa.

La cinta de opciones viene a reemplazar a los menús y barras de herramientas que se utilizaban anteriormente. Dentro de la cinta de opciones cada uno de los comandos pertenece a un grupo y que a su vez pertenece a una ficha.

Las fichas de Excel 2010

Las fichas que se muestran de manera predeterminada en la cinta de opciones son las siguientes:

1. La ficha Inicio tiene comandos que utilizamos frecuentemente, como Copiar y Pegar. También podemos dar formato a las fuentes, a los textos y a los números.

2. La ficha Insertar contiene comandos que permiten agregar algunos objetos a las hojas de trabajo, como las tablas, las imágenes y los gráficos.

3. La ficha Diseño de página nos permite seleccionar algunos temas para nuestras hojas de trabajo. También podemos configurar la página y prepararla para su impresión.

4. La ficha Fórmulas contiene la biblioteca de funciones que organiza adecuadamente todas las fórmulas de Excel y nos permite utilizarlas fácilmente para realizar cálculos.

5. La ficha Datos nos permite importar datos que provienen de fuentes externas como Access y archivos de texto. Así mismo podemos encontrar comandos que nos ayudan a ordenar y filtrar nuestros datos.

6. La ficha Revisar nos muestra comandos para verificar la ortografía de nuestros documentos y también nos permite insertar comentarios en las hojas de cálculo.

7. La ficha Vista contiene comandos para cambiar la apariencia de los datos en pantalla y nos permite hacer acercamientos para tener una mejor visualización.

6

Comandos de Excel

Los botones de comando en Excel 2010 son de diferentes tipos. Pueden ser botones simples, botones de activación, botones desplegables o botones combinados. Cada uno de estos botones tendrá una función específica dentro de Excel.

Botones simples

Este es el tipo de botón de comando más simple porque es suficiente con pulsarlo una sola vez para ver reflejada la acción de manera inmediata. Ejemplos de estos botones simples son el comando Aumentar tamaño de fuente y el comando Aumentar decimales.

Botones de activación

Los botones de activación son aquellos que permanecen activos después de ser pulsados y solamente se desactivan cuando se vuelve a hacer clic sobre ellos. Una manera de identificarlos fácilmente es porque al estar activados el botón mantiene un color diferente.

Los comandos Negrita, Cursiva y Centrar son ejemplos de este tipo de botones.

Botones desplegables

Estos botones son los que muestran un menú al momento de pulsarlos.

Algunos ejemplos de este tipo de botones son el comando Formato condicional, Dar formato como tabla y Estilos de celda que se encuentran dentro del grupo Estilos de la ficha Inicio.

Botones combinados

Un botón combinado es el que tiene una parte de botón simple y una parte de botón desplegable. El botón Pegar es una ejemplo de este tipo de botón porque en la parte superior es un botón simple y en la parte inferior es un botón desplegable. Otro ejemplo de comando que tiene este comportamiento es el comando Formato de número de contabilidad.

7

La barra de herramientas de acceso rápido

La barra de herramientas de acceso rápido en Excel 2010 nos da la oportunidad de tener los comandos que más utilizamos siempre a la vista. Estos comandos son independientes a los que se muestran en la cinta de opciones.

Cada usuario de Excel puede personalizar la barra de herramientas de acceso rápido agregando los botones de comando que más utilice. Agregar nuevos botones es muy sencillo ya que solamente debemos elegir en la cinta de opciones el comando que deseamos agregar y hacer clic con el botón secundario del ratón sobre el botón del comando para seleccionar la opción Agregar a la barra de herramientas de acceso rápido del menú contextual que se muestra.

Consideraciones especiales de la barra de herramientas

Es importante mencionar que no es posible modificar el tamaño de los botones que se muestran en la barra de herramientas de acceso rápido. Para algunas personas el tamaño de los botones es muy pequeño y desearían agrandarlos, pero no es posible. La única alternativa que se tiene es cambiar la resolución de pantalla del equipo para verlos más grandes.

Otra consideración especial es que la barra de herramientas de acceso rápido siempre ocupa una sola línea de botones. Nunca se acomodarán los botones en dos o más líneas.

La barra de fórmulas

La barra de fórmulas en Excel es un elemento de suma importancia. Es a través de esta barra que podemos introducir el contenido de una celda así como modificarlo. Además nos facilita la inserción de fórmulas dentro del programa.

La barra de fórmulas se encuentra ubicada entre la cinta de opciones y el área de trabajo de la hoja. Esta barra despliega los datos o las fórmulas contenidas en la celda activa y nos permite hacer una edición de esos datos o fórmulas.

En el siguiente ejemplo la barra de fórmulas muestra el valor de la celda B1.

8

En este otro ejemplo vemos que la barra de fórmulas despliega la fórmula que está contenida en la celda C1 mientras que el resultado de dicha fórmula es mostrado dentro de la celda.

La barra de estatus

La barra de estatus, también conocida como la barra de estado de Excel, se encuentra en la parte inferior de la ventana de la aplicación y nos provee de información útil para conocer el estado actual de Excel.

Además dentro de la barra de estado hay controles donde podremos elegir el tipo de vista que deseamos tener en la aplicación, ya sea una vista Normal, una vista de Diseño de página o una Vista previa de salto de página.

Cuando trabajamos con datos numéricos en Excel y seleccionamos dichas celdas, la barra de estado nos muestra información relevante sobre los datos como el promedio, el recuento y la suma si necesidad de realizar alguna acción adicional.

9

Personalizar la barra de estado de Excel

Al hacer clic derecho sobre la barra de estado se mostrará un menú con todas las opciones que nos permitirán personalizar la barra. Algunos indicadores que podemos agregar son el Bloqueo de mayúsculas el cual indicará cuando la tecla bloqueo de mayúsculas esté presionada. Así mismo podemos agregar las opciones Mínima y Máxima que nos darán información adicional sobre las celdas que seleccionemos.

La barra de estado siempre nos dará información oportuna sobre las acciones que ejecutamos dentro del programa.

Crear nuevas hojas de Excel

Cuando abres un nuevo libro, Excel coloca de manera predeterminada tres hojas, sin embargo puedes crear nuevas hojas así como renombrarlas y también eliminar aquellas que ya no necesites.

Crear una hoja en Excel

La manera más rápida y fácil de crear una hoja en Excel es a través del icono que se muestra justo al lado del nombre de las hojas.

10

Con tan solo oprimir dicho icono se crearán nuevas hojas instantáneamente.

Renombrar una hoja

Para renombrar una hoja será suficiente con hacer doble clic sobre su nombre para que Excel permita editar el texto.

Eliminar una hoja

Si estás seguro de querer eliminar una hoja solamente debes hacer clic derecho sobre su nombre y seleccionar la opción de menú Eliminar.

Debes estar totalmente seguro de querer eliminar una hoja porque dicha acción no se puede deshacer. La última oportunidad que tienes en caso de que accidentalmente elimines una hoja es no guardar los cambios para Excel mantenga la hoja y sea mostrada la próxima vez que abras el libro.

11

Introducir datos a una hoja

Una vez que has abierto Excel el programa está listo para recibir cualquier dato que proporciones. Ingresar información en el programa es una tarea muy sencilla pero que muchos usuarios principiantes requieren dominar.

Ingresar datos a una hoja de Excel

Se puede ingresar datos en Excel de diversas fuentes, pero la manera más básica es introducirla uno mismo a través del teclado. Solo es importante saber que después de ingresar el texto dentro de una celda será necesario pulsar la tecla Entrar o la tecla Tabulador.

Otra manera de ingresar información a Excel es a través de la importación de datos. Es posible tener un archivo de texto que ha sido generado por alguna otra aplicación y traer los datos a Excel. Este tema es más avanzado como para detallarlo en esta ocasión, pero puedes echar un vistazo al artículo Importar archivos de texto para darte una idea de ese procedimiento. Así mismo se puede importar una base de datos Access y utilizar la información en Excel e inclusive se pueden importar datos desde Internet.

Aunque son varios los métodos para ingresar datos en Excel, el más sencillo de todos es utilizar el teclado del equipo para introducir los valores en las celdas de nuestra hoja de Excel.

Editar el contenido de una celda

Es posible que después de haber ingresado todos tus datos a una hoja de Excel te des cuenta de que existen algunos errores en las celdas por lo que será necesario realizar alguna modificación y edición de su contenido.

Para editar las celdas podemos utilizar varios métodos.

Reemplazo total del contenido

Este método sugiere reemplazar todo el texto de una celda por uno nuevo. Para realizar es suficiente con posicionarse sobre la celda que será editara e introducir el nuevo texto lo cual reemplazará por completo el contenido anterior.

Modo de edición de una celda

Si solamente necesitas modificar alguna letra o símbolo del texto de una celda y no deseas hacer un reemplazo total, entonces puedes entrar al modo de edición haciendo doble clic sobre la celda a modificar. De inmediato se mostrará un cursor intermitente que te permitirá editar el contenido de la celda sin necesidad de eliminarlo todo.

Otra manera de entrar al modo de edición de una celda es seleccionándola y pulsando la tecla F2.

Editar una celda desde la barra de fórmulas

12

Una tercera opción para editar el contenido de una celda es a través de la barra de fórmulas. Solo necesitas seleccionar la celda a modificar y hacer las correcciones directamente en la barra de fórmulas.

Si por alguna razón cometes alguna equivocación al estar editando las celdas puedes hacer uso del comando Deshacer que se encuentra en la barra de herramientas de acceso rápido. Para conocer más sobre este comando consulta el artículo: Deshacer y rehacer en Excel.

Copiar y pegar celdas

En ocasiones estarás en la necesidad de repetir la información contenida en alguna o en varias celdas en otro libro, en otra hoja o simplemente necesitas repetir la información algunas filas abajo, para ello puedes utilizar la funcionalidad de copiar y pegar.

Copiar celdas en Excel

Copiar es tan sencillo como seleccionar la celda o las celdas y pulsar el botón Copiar que se encuentra en la ficha Inicio dentro del grupo Portapapeles.

Una vez copiadas las celdas será necesario seleccionar la ubicación donde se pegarán los datos. Es en este momento que puedes seleccionar una hoja diferente o inclusive ir a otro libro de Excel donde se pegarán los datos.

Pegar celdas en Excel

Una vez que has elegido el destino adecuado solamente resta pulsar el botón Pegar que se encuentra en la ficha Inicio dentro del grupo Portapapeles.

13

Atajos de teclado para copiar y pegar

Si la acción de copiar y pegar la realizarás en repetidas ocasiones será mejor utilizar un atajo de teclado para evitar pulsar tantas veces los botones. Para copiar una celda puedes utilizar la combinación de teclas CTRL + C y para pegar las celdas copiadas puedes utilizar la combinación CTRL + V.

Guardar un archivo

Cuando guardas un archivo de Excel lo puedes almacenar en la unidad de disco duro de tu equipo, en una memoria flash o en un CD o DVD. Pero independientemente del medio donde se guarde debes hacerlo frecuentemente para no perder los cambios hechos.

Evitar perder todo el trabajo que has hecho en Excel y adopta la buena práctica de guardar de manera constante tu archivo. Es tan fácil como seleccionar el comando Guardar o el comando Guardar como de la ficha Archivo.

Un atajo de teclado para guardar un archivo es CTRL + G, si tienes el software en español, pero si tienes la versión en inglés de Excel entonces el método abreviado de teclado será CTRL + S.

Guardar como versión anterior de Excel

14

Si vas a compartir tu archivo con alguien que no tenga Excel 2007 o Excel 2010 entonces será necesario guardar el archivo de una manera especial de manera que pueda ser abierto por versiones anteriores de Excel.

Abre el cuadro de diálogo Guardar como y asegúrate de seleccionar la opción Libro de Excel 97-2003.

Puedes aprovechar y echar un vistazo a la lista Tipo para saber en qué otros formatos es posible guardar un archivo de Excel. Descubrirás que puedes guardar un libro como archivo PDF, o como archivo de texto, e inclusive como página web.

Manipular filas y columnas

Aun cuando pareciera que ya tenemos listos nuestros datos en ocasiones es necesario agregar una nueva fila o una nueva columna para poder añadir mayor detalle a la información. En Excel es muy fácil insertar una columna o una fila.

Insertar filas y columnas

Podemos insertar una fila o columna con la opción Insertar del menú emergente que se muestra al hacer clic derecho sobre el encabezado de una fila o columna. Pero existe otro método que también podemos emplear para realizar esta acción.

En la ficha Inicio, dentro del grupo Celdas, pulsa el menú desplegable del botón Insertar y podrás observar las opciones de menú Insertar filas de hoja y la opción Insertar columnas de hoja.

15

Eliminar filas y columnas

Al igual que para eliminar una fila o una columna con el menú emergente de los encabezados, también podemos utilizar el menú desplegable del comando Eliminar que está dentro del grupo Celdas de la ficha Inicio.

Los comandos Eliminar filas de hoja y Eliminar columnas de hoja borrarán las filas o columnas correspondiente a la celda activa o en su caso las filas o columnas del rango de celdas seleccionado.

Debes tener cuidado antes de eliminar alguna fila o columna ya que Excel no solicitará confirmación alguna sino que procederá de inmediato a la eliminación de las celdas especificadas. Por supuesto que siempre puedes recurrir al comando Deshacer en caso de eliminar información accidentalmente.

Ordenar información

Al ordenar los datos contenidos en una hoja de Excel podemos llegar a comprender mejor la información. Los comandos de orden en Excel nos permiten ordenar los datos de manera ascendente y descendente.

Ordenar rápidamente en Excel

Podemos ordenar los datos de manera rápida utilizando los comandos A-Z y Z-A. Solamente selecciona una celda que pertenezca a la columna por la que se realizará el ordenamiento y selecciona la opción Ordenar de A-Z (ascendente) o la opción Ordenar de Z-A (descendente) que son parte del menú desplegable del botón Ordenar y filtrar de la ficha Inicio.

16

Estos mismos comandos los puedes encontrar en la ficha Datos como parte del grupo Ordenar y filtrar.

Ordenar por más de una columna

Si quieres ordenar los datos por más de una columna puedes utilizar el comando Orden personalizado que también encuentras en el menú desplegable del botón Ordenar y filtrar en la ficha Inicio. Este comando mostrará el cuadro de diálogo Ordenar el cual nos permitirá especificar todas las columnas por las que deseamos ordenar los datos.

De la lista Ordenar por selecciona la columna por la que se ordenará y de la lista Criterio de ordenación elige el tipo de ordenamiento ya sea ascendente o descendente. Para agregar una columna adicional debes oprimir el botón Agregar nivel y de nuevo especificar los valores para cada una de las listas. Al pulsar el botón Aceptar se aplicará el orden especificado para cada una de las columnas.

17

Filtrar información

Para agregar un filtro a una tabla debes seleccionar cualquier celda que forme parte de los datos y entonces hacer clic en el comando Filtro que se encuentra dentro del botón Ordenar y filtrar en la ficha Inicio.

El mismo comando Filtro lo puedes encontrar en la ficha Datos dentro del grupo Ordenar y filtrar.

Una vez aplicado el filtro aparecerán flechas al lado derecho de cada columnas las cuales serán menús desplegables donde aparecerán las opciones de filtrado para la columna.

Filtrar una columna

Con los filtros agregados a nuestra tabla podemos comenzar a filtrar información. Podemos filtrar los datos de una columna al hacer clic sobre la flecha desplegable del filtro de columna y podremos observar la lista de los valores únicos de la columna.

18

Debemos seleccionar aquellos valores que deseamos observar en pantalla y entonces pulsar el botón Aceptar. Excel ocultará aquellas filas que no cumplan con el criterio especificado. Observa que el primer elemento de la lista es la opción (Seleccionar todo) la cual nos ayuda a marcar o desmarcar todas las cajas de selección con solo un clic.

Filtrar por dos o más columnas

Una vez que has filtrado los datos por una columna puedes refinar aún más los resultados al aplicar un filtro adicional a una segunda o tercera columna. Solamente vuelve a elegir la flecha desplegable de filtro de otra columna y selecciona las opciones de filtrado.

Borrar un filtro específico

Para borrar el filtro de alguna columna en específico solamente vuelve a pulsar la flecha desplegable de filtro y selecciona la opción Eliminar filtro.

19

Borrar todos los filtros

Si en lugar de borrar un solo filtro deseas eliminar todos los filtros de los datos, entonces debes ir a la ficha Datos y dentro del grupo Ordenar y filtrar pulsar el botón Borrar.

20

FORMATOS Formato de números

El formato de números en Excel es utilizado para cambiar la apariencia de los números que se muestran como valores de las celdas. Al cambiar o modificar el formato no cambiamos el valor del número sino solamente su apariencia.

La lista de formatos del grupo Número

Al aplicar un formato a un número mejoramos su apariencia y hacemos que los números sean más fáciles de entender. Los formatos más comunes para los números incluyen la adición del signo de porcentaje (%), separador de miles (,), el símbolo de moneda ($) y la cantidad de decimales a mostrar. Estos comandos los puedes encontrar en la ficha Inicio en el grupo Número.

De manera predeterminada todas las celdas tienen un formato conocido como formato General el cual no tiene ningún formato específico y despliega los valores tal como son, es decir, sin ningún símbolo, coma, etc.

Una manera rápida de cambiar el formato es utilizar la lista de Formatos que se muestra en el grupo Número. Solamente pulsa la lista desplegable para observar las opciones disponibles y seleccionar la que mejor se adapte a tus necesidades.

21

El formato puede ser aplicado a una sola celda o a varias celdas a la vez, solamente debes hacer la selección previa de las celdas antes de aplicar el formato.

El cuadro de diálogo Formato de celdas

Otro método de elegir el formato adecuado para los números es a través del cuadro de diálogo Formato de celdas el cual se muestra con tan solo pulsar el indicador de cuadro de diálogo del grupo Número.

22

Formato de fechas y horas

Las fechas son un tipo de dato muy común dentro de Excel y aprender a darles el formato adecuado es de suma importancia para poder presentar la información de manera adecuada. De igual manera el formato de horas es importante.

Formatos de fecha y hora en Excel

De manera predeterminada en la lista de formatos que se encuentra en la ficha Inicio dentro del grupo Número tenemos un par de formatos para fecha y otro para hora.

23

Sin embargo en el cuadro de diálogo Formato de celdas tenemos muchas más opciones de donde elegir. Asegúrate de elegir la categoría Fecha o la categoría Hora para tener acceso a los diferentes tipos de formato adicionales:

24

Fecha y hora en la misma celda

Es posible tener celdas que contengan un valor tanto de fecha como de hora. Esto es posible al separar la información por un espacio tal como se muestra a continuación:

Observa cómo la barra de fórmulas muestra el contenido de la celda tanto con la fecha como con la hora sin embargo el valor de la celda solamente muestra la fecha. Esto se debe a que la celda tiene un formato de fecha. Si cambiamos al formato de la celda por un formato de hora entonces la celda se mostrará de manera diferente:

25

Para que la celda muestre ambos datos debe tener un formato personalizado el cual podemos elegir dentro del cuadro de diálogo Formato de celdas.

Alineación de celdas

Los comandos de Alineación que observas en la ficha Inicio nos ayudan a darle la orientación adecuada al texto dentro de cada celda así como a combinar celdas independientes para formar una sola celda.

Ajustar el texto de una celda

El comando Ajustar texto nos ayuda a que el contenido de una celda se ajuste de manera que sea visibles sin necesidad de ajustar el ancho de la columna.

26

Excel ajustará el texto de la celda insertando saltos de línea de manera que sea visible con el ancho especificado de la columna. Esto por supuesto afectará el alto de la fila como puedes observar en la imagen superior.

Puedes ajustar la alineación vertical de las celdas que no se ajustaron con los comandos Alinear en la parte superior,Alinear en el medio y con el comando Alinear en la parte inferior.

Combinar y centrar en Excel

El comando Combinar y centrar nos permite seleccionar varias celdas y hacer una sola de todas ellas. Una vez que se han combinado las celdas se centrará el texto. Este botón tiene algunas opciones adicionales. Si pulsas la pequeña flecha que se muestra a la derecha del botón se mostrarán dichas opciones.

27

La primera es la opción ya comentada Combinar y centrar. La segunda opción es Combinar horizontalmente la cual combinará cada una de las filas de las celdas seleccionadas. La tercera opción es Combinar celdas la cual nos permite unir celdas tanto horizontal como verticalmente. La última opción nos ayuda a Separar celdas combinadas.

Bordes y relleno

Los bordes de celda que aplicamos en nuestra hoja de Excel aparecerán al momento de imprimir nuestro documento, pero también nos ayudan a resaltar y dividir adecuadamente los datos de manera que podamos visualizar la información correctamente.

Bordes de celdas

Para aplicar un borde podemos utilizar el botón Bordes que se encuentra en el grupo Fuente de la ficha Inicio.

También podemos configurar los bordes de una celda desde el cuadro de diálogo Formato de celdas en la pestaña Bordes.

28

Este cuadro de diálogo nos permitirá elegir un estilo de línea para los bordes así como un color adecuado.

Relleno de celdas

Además de poder utilizar el botón Color de relleno de la ficha Inicio, podemos seleccionar un color de relleno para nuestra celda desde el cuadro de diálogo Formato de celdas dentro de la pestaña Relleno.

Desde la pestaña Relleno podremos seleccionar un color sólido así como Efectos de relleno los cuales nos permiten aplicar un efecto de color degradado. De igual manera podemos elegir un Estilo de Trama para rellenar las celdas.

29

Estilos de celda

Excel nos provee de estilos de celda predefinidos los cuales podemos utilizar para aplicar un formato a nuestras celdas de manera rápida y conveniente. Y por si fuera poco también podemos definir y crear nuestros propios estilos de celda.

Crear tu propio estilo de celda

Una vez que tienes ese estilo de celda que aplicarás continuamente a tus reportes, lo mejor es guardarlo para que esté disponible en todo momento. Solamente selecciona la celda que contiene el formato adecuado y pulsa el botón desplegableEstilos de celda y selecciona la opción Nuevo estilo de celda. Se mostrará el cuadro de diálogo Estilo.

30

Pulsa el botón Aceptar y habrás creado un nuevo estilo de celda personalizado. Una vez creado el estilo podrás acceder a él desde la sección Personalizada de la galería de estilos de celda. Si por alguna razón necesitas eliminar un estilo de celda personalizado, debes hacer clic derecho sobre su nombre y seleccionar la opción Eliminar.

Copiar formato de celdas

Excel nos facilita la aplicación de formatos de celda al darnos un comando que hace una copia de un formato y nos permite pegarlo en otra ubicación. Para utilizar este comando primero debemos seleccionar la celda o rango de celdas que tienen el formato que deseamos copiar y entonces pulsar el botón Copiar formato que se encuentra en el grupo Portapapeles de la ficha Inicio.

Una vez copiado el formato procedemos a seleccionar las celdas destino las cuales adoptarán el mismo formato que las celdas origen.

Copiar ancho de columna

El botón Copiar formato también nos permite copiar los anchos de columna. Solamente selecciona el título de la columna que tienen al ancho que deseamos copiar, pulsa el botón Copiar formato y finalmente haz clic en el título de la columna que adoptará el nuevo ancho de columna.

31

Formato personalizado de celdas en Excel

Con el formato personalizado de celdas en Excel podemos dar una apariencia particular al valor de dicha celda. Un formato personalizado puede contener hasta 4 códigos diferentes dentro de la misma cadena de texto.

Formato personalizado de una celda

Para asignar un formato personalizado a una celda en Excel solamente debes hacer clic derecho sobre la celda deseada y seleccionar la opción Formato de celdas. Al mostrarse el cuadro de diálogo debes asegurarte de estar en la sección Número y elegir la opción Personalizada:

32

El formato personalizado de la celda se debe especificar dentro del cuadro de texto Tipo. Un formato personalizado es una cadena de texto que indicará a Excel la manera en cómo debe mostrarse el valor contenido en una celda.

El formato personalizado en Excel

En términos de formato personalizado de celdas en Excel, cada celda puede tener 4 posibles valores: positivos, negativos, cero y texto. De esta manera un formato personalizado nos permite especificar un código para cada uno de ellos dentro de la misma cadena de texto con la condición de separarlos por un punto y coma (;) con el siguiente orden:

Cada uno de los recuadros de la imagen representa un código, de manera que podemos indicar a Excel que trate de manera diferente el valor de una celda de acuerdo al tipo de valor que tenga.

Ejemplo de formato personalizado de una celda

33

Para tener claro la manera en que Excel aplica un formato personalizado a una celda crearemos el siguiente formato:

[Azul]Estándar;[Rojo]-Estándar;[Negro]Estándar;[Verde]Estándar

La palabra Estándar significa que Excel dará un trato “normal” al valor de la celda, es decir, no modificará su apariencia. Sin embargo, entre cada corchete [] he especificado un color diferente de acuerdo al valor de la celda.

Si el valor de la celda es positivo debe mostrarse de color azul, si es negativo de color rojo y además he colocado el símbolo “-“ de manera que Excel coloque el símbolo negativo precediendo el valor de la celda. Si el valor es cero entonces se mostrará de color negro y si es de tipo texto será de color verde. En la siguiente animación podrás observar cómo se comporta una celda que tiene este formato personalizado:

Al usar formato personalizado de celdas en Excel solamente tendremos disponibles 8 nombres de colores para modificar la apariencia de una celda: Negro, Verde, Blanco, Azul, Magenta, Amarillo, Aguamarina, Rojo.

Existe otra opción que nos permite elegir un color por su código y tendremos disponibles hasta 56 colores diferentes. Este código lo podemos especificar de la siguiente manera:

[Color5]Estándar;[Color20]-Estándar;[Color40]Estándar;[Color50]Estándar

A diferencia del ejemplo anterior donde colocamos directamente el nombre del color, en este ejemplo debemos indicar el número del color que deseamos aplicar.

Códigos de formato personalizado

Ya hemos visto cómo modificar el color de una celda de acuerdo a su valor pero también podemos modificar otros elementos como la cantidad de decimales que deseamos mostrar. En el siguiente ejemplo he especificado un formato personalizado que hará que siempre se muestren 3 decimales en todas las celdas. Observa el resultado:

34

El código # nos ayuda a representar la posición de un número y hemos indicado que deseamos tener 3 posiciones después del punto decimal. Para conocer más sobre el código # así como otros códigos te sugiero consultar el articulo Códigos de formato personalizado.

Formato personalizado a texto

Una pregunta muy frecuente de los usuarios de Excel es saber si se puede aplicar un formato personalizado a un texto. En realidad los formatos personalizados fueron creados para dar formato a números y por consecuencia también a las fechas ya que las fechas en Excel son también números.

Los formatos personalizados solo nos permiten efectuar algunas acciones con texto como hacer que se despliegue un carácter en cierta posición cuando acompaña a un número. En el siguiente ejemplo he utilizado el formato personalizado #! que colocará el signo de exclamación al final del valor de la celda.

Observa cómo la barra de fórmulas muestra que el valor de la celda es 365 pero el formato personalizado indica a Excel que se agregue el símbolo “!” al final del valor. Así como agregamos un solo

35

carácter también podemos agregar una palabra completa al final de un número. El formato personalizado que utilizaré será el siguiente: #.## “pesos”

De nueva cuenta observa la barra de fórmulas y observa que el valor de la celda no considera la palabra “pesos” sino que es agregada por el formato personalizado. Tanto los caracteres como las palabras se pueden colocar en cualquier posición que queramos, solamente debemos indicarlo correctamente dentro del formato personalizado.

Recuerda que el formato personalizado de celdas en Excel no es una manera de manipular cadenas de texto sino que fue creado primordialmente para ayudarnos a dar formato a datos numéricos. Puedes leer un artículo complementario que escribí sobre el tema de formatos personalizados de números.

36

Fórmulas de Excel Las fórmulas de Excel son lo que dan un tremendo poder a nuestras hojas de cálculo. Sin las fórmulas nuestras hojas de cálculo serían como cualquier otro documento creado en un procesador de palabras. Utilizamos las fórmulas de Excel para realizar cálculos en los datos de una hoja y obtener los resultados actualizados cada vez que los datos cambien.

¿Qué son las fórmulas de Excel?

Una fórmula de Excel es un código especial que introducimos en una celda. Ese código realiza algunos cálculos y regresa un resultado que es desplegado en la celda.

Existen millones de variaciones de fórmulas porque cada persona creará la fórmula que mejor se adapte a sus necesidades específicas. Pero sin importar la cantidad de fórmulas que vaya a crear, todas deberán seguir las mismas reglas en especial la regla que indica que todas las fórmulas deben empezar con un símbolo igual (=). Considera la siguiente fórmula para la celda A1 ingresada en la barra de fórmulas:

Al pulsar la tecla Entrar obtendremos el resultado calculado por Excel y el cual será mostrado en la celda A1:

Nunca debemos olvidar introducir el símbolo igual al inicio de una fórmula de lo contrario Excel tratará el texto introducido como si fuera cualquier otro texto. Observa lo que sucede en la celda B1 al no especificar el signo igual al inicio del texto:

Una celda contiene el símbolo igual y esa celda muestra el resultado de la operación, mientras que la otra celda solamente muestra el texto de la ecuación pero no realiza ningún cálculo.

37

Partes de una fórmula de Excel

Todas las fórmulas de Excel consisten de cualquier de los siguientes elementos:

Constantes o texto. Un ejemplo de una constante es el valor 7. Un texto también puede ser utilizado dentro de una fórmula pero siempre deberá estar encerrado por dobles comillas como “Marzo”.

Referencias de celda. En lugar de utilizar constantes dentro de nuestras fórmulas, podemos utilizar referencias de celdas que apuntarán a la celda que contiene el valor que queremos incluir en nuestra fórmula

Operadores. Los operadores utilizados en Excel son los mismos operadores matemáticos que conocemos como el símbolo + para la suma o el símbolo * para la multiplicación.

Funciones de Excel. Dentro de las fórmulas de Excel podemos utilizar funciones de Excel. Un ejemplo de una función de Excel es la función SUMA la cual podemos incluir como parte de una fórmula.

38

Constantes y referencias de celda

Las fórmulas nos permiten utilizar Excel como si fuera una calculadora, solamente debemos introducir la ecuación en la Barra de fórmulas, pulsar la tecla Entrar y Excel calculará el resultado. En el siguiente ejemplo puedes observar un ejemplo de fórmula que utiliza solamente números:

Estos números son constantes, lo que significa que su valor nunca cambiará y la fórmula siempre regresará el mismo resultado mientras tanto y no modifiquemos los números. Sin embargo una de las ventajas más grandes al utilizar fórmulas en Excel es que podemos utilizar referencias de celda dentro de la ecuación.

Referencias de celda en fórmulas

Las referencias de celda nos ofrecerán un mayor grado de flexibilidad en nuestras fórmulas. Considera el siguiente ejemplo

La celda A2 tiene el valor 1, la celda B2 el valor 2 y la celda C2 el valor 3. En la celda D2 crearé la siguiente fórmula =A2+B2+C2 y al pulsar la tecla Entrar obtendré el resultado de la operación:

La diferencia entre utilizar contantes y referencias de celda es que el resultado de una fórmula con referencias de celdadependerá del valor de otras celdas. Si el valor de cualquiera de las celdas referenciadas cambia, entonces el resultado de la fórmula también será actualizado.

Si actualizo el valor de la celda B2 por 6, tan pronto como pulse la tecla Entrar habrá dos actualizaciones en la hoja de cálculo. En primer lugar se actualizará el valor de la celda B2 y en segundo lugar el resultado de la fórmula de la celda D2 también será actualizado.

39

Observa que el resultado de la fórmula de la celda D1 no cambia porque sus elementos son siempre constantes.

De esta manera puedes observar la flexibilidad que ofrece el utilizar referencias de celda dentro de nuestras fórmulas ya que podemos controlar su resultado sin la necesidad de editar la fórmula.

Referencias absolutas y relativas Una referencia en Excel identifica a una celda (o rango de celdas) dentro de una hoja de trabajo y le hace saber a Excel en dónde buscar los valores que se quieren utilizar en una fórmula. Pero ¿Cuál es la diferencia entre las referencias absolutas y las referencias relativas?

Referencias relativas en Excel

Una referencia relativa es cuando Excel puede modificar libremente dicha referencia para ajustarla al utilizarla dentro de una fórmula. Por ejemplo, si la fórmula de la celda D1 es la siguiente:

=C1*2

Si arrastramos el controlador de relleno hacia abajo, Excel copiará la fórmula y la ajustará de manera que la referencia se modifique automáticamente conforme va avanzando la fila.

En este ejemplo la referencia C1 de la fórmula se fue incrementando automáticamente a C2, C3, C4 y C5 conforme fue cambiando de fila.

40

Referencias absolutas en Excel

Ahora analicemos el caso de las referencias absolutas. Una referencia es absoluta cuando Excel no la puede ajustar para adaptarse a la fórmula conforme cambia de fila o de columna. Las referencias absolutas permanecen constantes sin importar a dónde se copie la fórmula y se definen utilizando el símbolo “$”. Por ejemplo, la referencia $A1 significa que en esta referencia la columna A será siempre fija mientras que la fila podría ajustarse automáticamente. Por otro lado, la referencia A$1 significa que la fila 1 permanecerá siempre fija. Si quieres que tanto la columna como la fila permanezcan siempre fijas la referencia debe ser $A$1.

Con un ejemplo similar al anterior veamos lo que sucede cuando hacemos que la referencia sea absoluta. Nota que ahora la fórmula de la celda D1 está escrita de la siguiente manera:

=$C$1*2

Observa que sin importar a qué fila se copió la fórmula, la referencia siempre se mantiene hacia $C$1. Es decir, aun cuando se halla copiado la fórmula a filas diferentes la referencia dentro de la fórmula permaneció constante. Es importante que entiendas la diferencia entre estos dos tipos de referencias porque te permitirá escribir fórmulas efectivas en Excel.

Precedencia de operadores aritméticos

Un operador es un símbolo que especifica el tipo de cálculo matemático que se desea realizar en una fórmula, por ejemplo la suma o la multiplicación. Si una fórmula contiene varios operadores, Excel realiza dichos cálculos en un orden predeterminado.

Precedencia de operadores

Las formulas en Excel son calculadas de izquierda a derecha comenzando a leer después del signo igual (=) y calculando los valores de acuerdo a la precedencia de los operadores. El orden en que son calculadas las operaciones es el siguiente:

1. Porcentaje [%]

2. Exponenciación [^]

3. Multiplicación [*] y división [/]

4. Suma [+] y resta [-]

41

Seguramente el concepto quedará claro con un ejemplo. Considera la siguiente fórmula: =5+3*4-6/2

Las multiplicaciones y divisiones se calcularán primero obteniendo el siguiente resultado: =5+12-3 . El

número 12 es el resultado de multiplicar 3*4 y el número 3 es el resultado de la división 6/2. Finalmente se realizará la suma y la resta dando como resultado 14 que es precisamente el resultado que obtenemos en Excel:

Uso de paréntesis con operadores aritméticos

Aun cuando existe un orden predeterminado para los operadores, podemos influir en la precedencia de operadores al utilizar paréntesis los cuales tendrán la preferencia sobre los operadores. Considera la

siguiente fórmula: =(5+3)*4-6/2

Esta es una fórmula similar a la anterior solamente que he colocado un paréntesis para la suma (5+3), la cual

será calculada antes que cualquier otra cosa dejando el siguiente resultado: =8*4-6/2 . Ahora que ya no hay

paréntesis Excel aplicará el orden predeterminado empezando con las multiplicaciones y divisiones para

obtener el resultado: =32-3 y finalmente hará la resta para obtener el resultado final de 29.

Observa cómo Excel efectivamente obtiene este resultado:

Ya conoces el orden de la precedencia de operadores aritméticos en Excel y debes tomarlo muy en cuenta al momento de crear fórmulas ya que definitivamente influirán en el resultado obtenido.

Comparar valores en Excel

Para poder comparar valores en Excel debemos hacer uso de los operadores de comparación. A través de estos operadores podremos saber si un valor es mayor, igual o diferente al valor con el cual lo estamos comparando.

Al utilizar los operadores de comparación recibiremos como respuesta un valor VERDADERO o un valor FALSO de acuerdo a como se haya evaluado la expresión.

Operador Igual a (=)

42

Para comparar dos valores y saber si son iguales podemos utilizar el operador igual a (=). Observa el siguiente ejemplo:

La columna C contiene las expresiones de comparación entre los valores de la columna A y la columna B. Observa cómo para la celda C2 el resultado es FALSO por que los valores comparados son diferentes.

En la imagen superior he colocado unos paréntesis alrededor de la comparación de manera que podamos diferenciar el operador igual a (=) del signo igual que identifica el inicio de la fórmula. Sin embargo podemos quitar los paréntesis y la fórmula funcionará correctamente.

Operador Mayor que (>)

El operador mayor que (>) nos permite saber si un valor es mayor que otro. Observa cómo funciona este operador en los datos de ejemplo:

Podemos también juntar este operador con el signo de igual de manera que tengamos una comparación mayor que o igual a(>=) la cual podremos utilizar para comparar valores. Observa la diferencia en los resultados al utilizar este operador:

43

Operador Menor que (<)

El operador menor que (<) verifica que el valor de la izquierda de la expresión sea menor que el valor de la derecha.

De la misma manera podemos unir el operador menor que con el signo igual para tener una comparación menor que o igual a(<=). Observa los resultados:

Operador No igual a (<>)

El último operador de comparación es el operador de desigualdad (<>). Este operador nos ayuda a saber si los valores comparados son diferentes, en cuyo caso obtendremos un valor VERDADERO.

Si los valores son iguales entre sí, entonces el operador nos regresará un valor FALSO como es el caso de la celda C4 del ejemplo.

Es importante familiarizarse con los operadores de comparación porque serán de gran utilidad al momento de trabajar con otras funciones de Excel.

44

Ingresar fórmulas en Excel

Para ingresar fórmulas en Excel debemos iniciar siempre introduciendo el símbolo igual (=) de manera que indiquemos a Excel que la celda contendrá una fórmula en lugar de un texto. Excel nos da la oportunidad de utilizar diferentes métodos para ingresar nuestras fórmulas.

Ingresar una fórmula manualmente

La manera más simple de introducir una fórmula es capturando todo el texto que la compone directamente en la celda o en la barra de fórmulas. Tal como cualquier otro texto podemos utilizar las flechas para movernos entre el texto así como realizar cualquier edición con el teclado.

Ingresar fórmulas con el ratón

Existe un método alterno que nos permite ingresar una fórmula de una manera más rápida y menos susceptible a errores. Con este método utilizamos el ratón para seleccionar las celdas que forman parte de una fórmula.

Este método inicia igual que todos: introduciendo el símbolo igual (=) y cada vez que necesitamos introducir una referencia a una celda debemos seleccionarla con el ratón en lugar de introducirla con el teclado.

45

Ingresar fórmulas con flechas

Este método es similar que el anterior. La diferencia es que en lugar de utilizar el ratón para seleccionar las celdas utilizamos las flechas del teclado para movernos en la hoja de Excel hacia la celda a la que deseamos crear una referencia en nuestra fórmula.

Utilizar autocompletar para ingresar una función

Las fórmulas de Excel pueden utilizar funciones en cuyo caso podemos hacer uso de la funcionalidad de Autocompletar la cual hace más sencillo introducir una función.

En este ejemplo utilicé las facilidades del autocompletar y también del ratón para introducir las referencias de las celdas dentro de la función.

Insertar nombres de rango en fórmulas

En Excel podemos tener celdas o rangos de celdas con nombres asociados y podemos utilizar dichos nombres en nuestras fórmulas. En el siguiente ejemplo el rango de celdas B2:B7 tiene el nombre Ventas y el rango C2:B7 el nombre Gastos. Podemos utilizar estos nombres en nuestras fórmulas de la siguiente manera:

46

La condición para utilizar un nombre de rango en nuestras fórmulas es que debemos conocer previamente el nombre al menos saber la letra inicial para obtener la lista de nombres disponibles. Si no conocemos el nombre del rango ni la letra inicial del nombre podemos pulsar la tecla F3 para desplegar el cuadro de diálogo Pegar nombre el cual nos dejará seleccionar el nombre de una lista.

Nota: Si no existen nombres previamente definidos, al oprimir la tecla F3 no sucederá nada.

Editar fórmulas en Excel

Después de haber ingresado una fórmula es probable que tengamos la necesidad de realizar alguna modificación. Para editar una fórmula podemos seguir cualquier de las siguientes opciones:

Seleccionar la celda y editar la fórmula directamente en la barra de fórmulas.

Hacer doble clic sobre la celda y editar la fórmula directamente en la celda.

Seleccionar la celda y pulsar la tecla F2 para editar la fórmula en la celda.

47

Asignar nombres a celdas o rangos

Hasta ahora he utilizado el estilo de referencia A1 para referirme tanto a una celda como a un rango pero también existe la posibilidad de crear un nombre descriptivo que los represente adecuadamente.

Asignar un nombre a un rango de celdas

Estos nombres se pueden utilizar dentro de una fórmula para ayudar en la compresión de la misma posteriormente. Para asignar un nombre a una celda sigue los siguientes pasos. Selecciona la celda o rango a la que asignarás un nombre y haz clic en el cuadro Nombre que se encuentra en el extremo izquierdo de la barra de fórmulas:

Escribe el nombre que deseas y presiona Entrar.

Otra manera de crear un nombre para un rango es desde la ficha Fórmulas y el botón Asignar nombre.

Una vez que hayas seleccionado el rango de celdas oprime este botón y se mostrará el cuadro de diálogo Nombre nuevo:

48

En la caja de texto Nombre coloca el nombre que asignarás a la celda o rango y oprime el botón Aceptar.

Utilizar un nombre en una fórmula

Como ejemplo final utilizaré el nombre que acabamos de crear dentro de una fórmula para que observes cómo Excel interpreta correctamente el nuevo nombre del rango, observa la barra de fórmulas:

Ahora ya sabes que puedes nombrar tanto celdas como rangos y utilizar ese nombre dentro de tus fórmulas para facilitar tu trabajo.

49

FUNCIONES

Insertar funciones

El cuadro de diálogo Insertar función en Excel 2010 simplifica el uso de las funciones en nuestras hojas ya que este cuadro de diálogo nos brinda ayuda para localizar la función adecuada y nos da información sobre sus argumentos.

Si utilizas el cuadro de diálogo Insertar función podrás evitar utilizar el teclado y podrás hacer prácticamente todo con el puntero del ratón.

Desplegar el cuadro de diálogo Insertar función

Existen tres maneras de mostrar este cuadro de diálogo. La primera es haciendo clic sobre el botón Insertar función de la ficha Fórmulas. La otra alternativa es utilizar el icono mostrado en la barra de fórmulas el cual se muestra como fx. El tercer método es seleccionar la opción de menú Más funciones que se muestra al pulsar el botón Autosuma.

Seleccionar la categoría de la función de Excel

De manera predeterminada se mostrará la categoría de funciones Usadas recientemente. Esto permitirá que hagas una revisión rápida sobre la lista para saber si la función que buscas se encuentra ahí.

50

De lo contrario, puedes seleccionar la categoría de la función que estás buscando para poder encontrarla rápidamente.

Si no conoces la categoría de la función tienes dos alternativas, la primera es seleccionar la opción Todo dentro de la lista desplegable lo cual mostrará todas las funciones de Excel y podrás buscar entre ellas. La otra opción que tienes es utilizar el cuadro Buscar una función para introducir el nombre de la función que buscar y oprimir el botón Ir para permitir que Excel encuentre dicha función.

Seleccionar la función

Una vez que has encontrado la función que necesitas, debes seleccionarla y hacer clic en el botón Aceptar o también puedes hacer doble clic sobre su nombre y de inmediato Excel mostrará el cuadro de diálogo Argumentos de Función.

51

Ingresar los argumentos de la función

Dentro de este nuevo cuadro de diálogo deberás seleccionar las celdas que contienen cada uno de los argumentos de la función. Una vez que hayas terminado de especificar los argumentos deberás pulsar el botón Aceptar para terminar con la inserción de la función de Excel.

Una ventaja del cuadro de diálogo Argumentos de función es que provee una descripción de ayuda para cada uno de los argumentos de la función utilizada de manera que si has olvidado alguno de ellos puedas rápidamente recordar el uso de cada uno de los argumentos.

Referencias absolutas y relativas

Una referencia en Excel identifica a una celda (o rango de celdas) dentro de una hoja de trabajo y le hace saber a Excel en dónde buscar los valores que se quieren utilizar en una fórmula. Pero ¿Cuál es la diferencia entre las referencias absolutas y las referencias relativas?

Referencias relativas en Excel

Una referencia relativa es cuando Excel puede modificar libremente dicha referencia para ajustarla al utilizarla dentro de una fórmula. Por ejemplo, si la fórmula de la celda D1 es la siguiente:

=C1*2

52

Si arrastramos el controlador de relleno hacia abajo, Excel copiará la fórmula y la ajustará de manera que la referencia se modifique automáticamente conforme va avanzando la fila.

En este ejemplo la referencia C1 de la fórmula se fue incrementando automáticamente a C2, C3, C4 y C5 conforme fue cambiando de fila.

Referencias absolutas en Excel

Ahora analicemos el caso de las referencias absolutas. Una referencia es absoluta cuando Excel no la puede ajustar para adaptarse a la fórmula conforme cambia de fila o de columna. Las referencias absolutas permanecen constantes sin importar a dónde se copie la fórmula y se definen utilizando el símbolo “$”. Por ejemplo, la referencia $A1 significa que en esta referencia la columna A será siempre fija mientras que la fila podría ajustarse automáticamente. Por otro lado, la referencia A$1 significa que la fila 1 permanecerá siempre fija. Si quieres que tanto la columna como la fila permanezcan siempre fijas la referencia debe ser $A$1.

Con un ejemplo similar al anterior veamos lo que sucede cuando hacemos que la referencia sea absoluta. Nota que ahora la fórmula de la celda D1 está escrita de la siguiente manera:

=$C$1*2

Observa que sin importar a qué fila se copió la fórmula, la referencia siempre se mantiene hacia $C$1. Es decir, aun cuando se halla copiado la fórmula a filas diferentes la referencia dentro de la fórmula permaneció constante. Es importante que entiendas la diferencia entre estos dos tipos de referencias porque te permitirá escribir fórmulas efectivas en Excel.

53

Función Excel SUMA

Nombre en inglés: SUM

Devuelve el subtotal de una lista de valores aplicando la operación indicada.

Sintaxis

SUMA(número1, *número2+, …)

número1 (Obligatorio): Primer número (o rango de celdas) a considerar en la suma.

número2 (Opcional): Números (o rangos de celdas) adicionales a sumar. Se pueden especificar hasta

255 números adicionales.

Ejemplos

SUMA(1,3,4) = 8

SUMA(A1:A5) = Suma de los valores en el rango A1:A5

Función Excel PROMEDIO

Nombre en inglés: AVERAGE

Obtiene el promedio de los números especificados.

Sintaxis

PROMEDIO(número1, *número2+, …)

número1 (obligatorio): Este parámetro puede ser un número ó también puede ser un rango de celdas

que contiene el conjunto de números a promediar.

número2 (opcional): A partir del segundo número los parámetros son opcionales. De igual manera

puedes colocar un número u otro rango de celdas de donde la función obtendrá más valores a

promediar.

Ejemplos

PROMEDIO(73, 52, 87, 13, 39) = 52.8

54

Función Excel CONTAR

Nombre en inglés: COUNT

Cuenta la cantidad de celdas que contienen números.

Sintaxis

CONTAR(valor1, *valor2+, …)

valor1 (obligatorio): Primer celda a considerar o el rango donde se desea contar.

valor2 (opcional): Celdas o rangos adicionales a considerar. Hasta 255 elementos.

Ejemplos

CONTAR(A1:A5) = Cuenta las celdas dentro del rango A1:A5 que contienen números

Función Excel CONTARA

Nombre en inglés: COUNTA

Cuenta la cantidad de celdas que no están vacías.

Sintaxis

CONTARA(valor1, *valor2+, …)

valor1 (obligatorio): Primera celda o rango donde se desea contar.

valor2 (opcional): Celdas o rangos adicionales a considerar. Hasta 255 elementos.

Ejemplos

CONTARA(A1:A5) = Cuenta las celdas dentro del rango A1:A5 que no están vacías

Artículos relacionados

55

Función Excel PRODUCTO

Nombre en inglés: PRODUCT

Multiplica todos los números proporcionados como argumentos.

Sintaxis

PRODUCTO(número1, *número2+, …)

número1 (obligatorio): El primer número a multiplicar.

número2 (obligatorio): El segundo número a multiplicar y hasta un máximo de 255.

Ejemplos

PRODUCTO(5, 7) = 35PRODUCTO(8, 3, 9) = 216

La función MAX en Excel

La función MAX en Excel es de gran ayuda siempre que necesitemos obtener el valor máximo de un

conjunto de valores que puede ser una lista de números ubicados en uno o varios rangos de nuestra hoja de

Excel.

Sintaxis de la función MAX

56

Número1 (obligatorio): El primer número de la lista o el rango de celdas que contiene los números a

evaluar.

Número2 (opcional): El segundo número de la lista o un rango de celdas con números adicionales a

incluir en la evaluación.

Podemos especificar un máximo de 255 números en la función MAX en caso de enlistarlos directamente

como argumentos pero podemos especificar rangos de celdas como argumentos de la función que a su vez

pueden contener varios números que serán evaluados.

La función MAX considera números negativos y el cero, por lo que dentro de una lista de números negativos

el valor máximo podría ser el cero. Observa el siguiente ejemplo:

Ejemplos de la función MAX

En el ejemplo anterior pudiste observar que podemos especificar los números directamente en la función

MAX, sin embargo de esta manera solamente podríamos tener hasta 255 números. Para aumentar la

cantidad de números que podemos incluir debemos colocarlos en un rango de celdas y especificar dicho

rango como argumento de la función.

De la misma manera podemos indicar varios rangos para obtener el valor máximo de todos ellos. Observa

que en el siguiente ejemplo los rangos proporcionados a la función MAX son de diferente tamaño y no

existe problema alguno para obtener el resultado correcto:

57

El mayor de una columna con la función MAX

La función MAX nos permite obtener fácilmente el número mayor de toda una columna utilizando la

siguiente fórmula:

=MAX(A:A)

De esta manera he especificado a la función MAX que tome en cuenta todas las celdas de la columna A.

El comando Autosuma en la ficha Inicio

58

Otra manera de acceder a la función MAX en Excel es utilizar el comando Autosuma que se encuentra en la

ficha Inicio. Este método funciona de la siguiente manera, en primer lugar seleccionamos el rango de celdas

que deseamos evaluar (M1:M10) y posteriormente desplegamos el menú del botón Autosuma para

seleccionar la opción Máx.

Excel insertará la función MAX y como argumento colocará el rango seleccionado previamente. En este

ejemplo la función MAX en insertada en la celda M11 que es justo por debajo del rango seleccionado:

La función MAX en Excel será de gran ayuda cada vez que necesitemos encontrar el valor máximo de un

grupo de números.

59

La función MIN en Excel

La función MIN en Excel nos devuelve el valor mínimo de un conjunto de valores. Los argumentos de la

función MIN pueden ser los números, las celdas o los rangos que contienen los valores que deseamos

evaluar.

Sintaxis de la función MIN

Número1 (obligatorio): El primer número a considerar o la celda o rango que contiene los números.

Número2 (opcional): El segundo número de la lista o la celda o rango que contiene valores adicionales

a considerar.

A partir del segundo argumento todos los demás serán opcionales y hasta un máximo de 255 argumentos.

Sin embargo, esto no quiere decir que solamente podemos incluir 255 números en la función MIN ya que

podremos indicar rangos con una cantidad de números mayor.

Al realizar la evaluación de los números especificados la función MIN tomará en cuenta también los

números negativos y el cero.

Ejemplos de la función MIN

A continuación mostraré algunos ejemplos utilizando la función MIN para dejar en claro su

funcionamiento. Para el primer ejemplo, utilizaré la función MIN especificando los números que deseo

evaluar como argumentos de la función:

60

La función MIN devuelve el número más pequeño de la lista especificada. Ahora bien, haré este mismo

ejemplo pero ahora tomando los números de un rango de celdas:

El resultado es el mismo que el anterior solo que en este ejemplo solamente estamos utilizando un solo

argumento de lafunción MIN para obtener el valor mínimo de una lista de diez números que se encuentra

en un rango.

Ya que la función MIN acepta como argumentos rangos de celdas que contienen una lista de números,

entonces podemos evaluar varias listas de números y obtener el valor mínimos de todas ellas. A

continuación observa cómo son evaluados tres rangos de celdas para obtener el valor mínimo:

61

El valor regresado por la función MIN es el número 4 que se encuentra en la celda E8. De esta manera

puedes observar que la cantidad de números evaluados por la función MIN es prácticamente ilimitada. La

única restricción que tenemos es que podemos especificar hasta 255 rangos como argumentos de la

función.

Excluir ceros de la función MIN

En ocasiones nos encontramos con una lista de números que contiene ceros pero necesitamos encontrar el

valor mínimo mayor a cero. Para ello podemos utilizar la función MIN junto con la función SI para agregar

una condición:

=MIN(SI(A1:A10>0, A1:A10))

Lo importante de esta fórmula es que debe ser utilizada en forma matricial por lo que deberás pulsar la

combinación de teclas CTRL + MAYÚS + ENTRAR al terminar de introducirla. Observa en el siguiente ejemplo

cómo la función MIN devuelve el valor 4 en lugar del valor cero.

La función MIN en Excel siempre nos ayudará a obtener el valor mínimo de una lista de valores.

62

La función REDONDEAR en Excel

La función REDONDEAR en Excel nos ayuda a redondear un número a una cantidad de decimales especificados. La cantidad de decimales especificados puede ser un número positivo, negativo o cero.

Sintaxis de la función REDONDEAR

La función REDONDEAR tiene dos argumentos obligatorios:

Número (obligatorio): El número que va a ser redondeado.

Núm_decimales (obligatorio): La cantidad de decimales a la que se desea redondear.

Ejemplos de la función REDONDEAR

En la celda A1 tengo el valor 16.475 y utilizaré la función REDONDEAR con diferentes valores para el segundo argumento de manera que podamos observar la diferencia.

Cuando el segundo argumento de la función REDONDEAR es mayor a cero entonces el número se redondea a la cantidad de decimales especificada. Si colocamos un cero como segundo argumento, entonces se

63

redondeará hacia el número entero más próximo. Por el contrario, si especificamos un número negativo, entonces la función REDONDEAR hace el redondeo hacia la izquierda del separador decimal.

Ahora observa el valor 2.3928 siendo redondeado a una, dos y tres posiciones decimales.

Debes recordar que la función REDONDEAR hace siempre un redondeo hacia arriba a partir del número 5, de lo contrario el redondeo ser realizará hacia abajo. Considera los siguientes ejemplos:

REDONDEAR(4.845,2) = 4.85

REDONDEAR(4.844,2) = 4.84

La función CONCATENAR en Excel

La función CONCATENAR en Excel nos permite unir dos o más cadenas de texto en una misma celda lo cual es muy útil cuando nos encontramos manipulando bases de datos y necesitamos hacer una concatenación.

Sintaxis de la función CONCATENAR

La función CONCATENAR tiene una sintaxis muy sencilla donde cada argumento será un texto que se irá uniendo al resultado final. El máximo de argumentos que podemos especificar en la función es de 255 y el único obligatorio es el primer argumento.

64

Texto1 (obligatorio): El primer texto que se unirá a la cadena de texto final.

Texto2 (opcional): El segundo texto a unir al resultado final. Todos los argumentos son opcionales a partir del segundo argumento.

Ejemplo de la función CONCATENAR

La función CONCATENAR nos puede ayudar en casos en los que la información está distribuida en varias columnas y deseamos integrar la información en una sola. Por ejemplo, tengo información de los nombres de los empleados en 3 columnas diferentes:

Para unir el nombre y los apellidos de todos los empleados en la columna D podemos seguir los siguientes pasos.

1. En la celda D2 comenzaré a escribir la función CONCATENAR especificando el primer argumento:

=CONCATENAR(A2,

2. Como segundo argumento debo especificar un espacio vacío de manera que el Nombre y el Apellido

paternos permanezcan separados en la nueva cadena de texto: =CONCATENAR(A2," ",

65

3. Ahora puedo especificar el tercer y cuarto argumento que será el Apellido paterno con su respectivo espacio:

=CONCATENAR(A2," ",B2," ",

4. Y como último paso en el quinto argumento irá el Apellido materno: =CONCATENAR(A2," ",B2," ",C2)

La función CONCATENAR nos da el resultado esperado en la celda D2:

Sólo resta copiar la fórmula hacia abajo para que Excel realice la concatenación de los demás nombres.

La función CONTAR.SI en Excel

La función CONTAR.SI en Excel nos da la oportunidad de contar el número de celdas de un rango que cumplan con un criterio establecido. Solamente cuando el valor de dichas celdas cumple la condición es considerada dentro de la cuenta.

Sintaxis de la función CONTAR.SI

Solamente existen dos argumentos para la función CONTAR.SI que son los siguientes:

66

Rango (obligatorio): El rango que contiene los valores que se desean contar.

Criterio (obligatorio): La condición que determinará las celdas que serán contadas.

El criterio puede ser un número o una expresión que indicará la condición a cumplir, por ejemplo: 55, “>26”, “VERDE”. El criterio no hará diferencia entre mayúsculas y minúsculas. La función CONTAR.SI solamente permite especificar una sola condición, en caso de necesitar aplicar varias condiciones se puede utilizar la función CONTAR.SI.CONJUNTO.

Ejemplo de la función CONTAR.SI

Tengo una lista de artículos y cada uno tiene asociado su color. Ahora necesito contar el total de artículos que son de color azul y para ello utilizaré la siguiente fórmula:

=CONTAR.SI(B2:B11, "azul")

Al aplicar esta fórmula a los datos de la columna B obtengo la cuenta correcta:

67

Puedes observar que el segundo argumento de la función CONTAR.SI está todo en minúsculas mientras que las celdas de la columna B tienen la primera letra en mayúsculas, sin embargo el valor “Azul” es considerado dentro de la cuenta por lo que comprobamos que la condición no es sensible a mayúsculas y minúsculas.

Recuerda que la función CONTAR.SI nos permitirá contar el número de celdas dentro de un rango que cumplan con el criterio que se haya especificado en el segundo argumento de la función.

CONTAR.SI.CONJUNTO en Excel

La función CONTAR.SI.CONJUNTO en Excel nos permite contar los elementos de un rango que cumplen con los criterios definidos. Esta función nos permite especificar hasta 127 criterios.

Sintaxis de la función CONTAR.SI.CONJUNTO

La función CONTAR.SI.CONJUNTO tiene dos argumentos obligatorios y los demás opcionales.

Rango_criterios1 (obligatorio): El rango de celdas que será evaluado con el Criterio1.

Criterio1 (obligatorio): El criterio con que se evaluará el Rango_criterios1.

Rango_criterios2 (opcional): El rango de celdas que será evaluado con el Criterio2.

Criterio2 (opcional): El criterio que evaluará el Rango_criterios2.

A partir del Rango_criterios2, todos los argumentos son opcionales y se permiten hasta 127 rangos y criterios a evaluar por la función CONTAR.SI.CONJUNTO. Cada rango especificado debe tener la misma cantidad de filas (o columnas) que los anteriores.

Ejemplo de la función CONTAR.SI.CONJUNTO

68

Para ejemplificar el uso de la función CONTAR.SI.CONJUNTO haremos un ejemplo muy sencillo. Tengo una lista de personas (hombres y mujeres) que están en un rango de edad entre los 15 y los 25 años de edad.

La primera cuenta que necesito hacer es la de conocer el número total de hombres y haré dicha cuenta con la función CONTAR.SI.CONJUNTO de la siguiente manera:

=CONTAR.SI.CONJUNTO(B2:B16, "Hombre")

El resultado de esta fórmula lo puedes observar en la celda F1 de la siguiente imagen:

Contar valores con más de una condición

El verdadero potencial de la función CONTAR.SI.CONJUNTO es cuando necesitamos especificar más de un criterio en nuestra cuenta. Por ejemplo, si en la lista anterior necesito contar los hombres mayores de 18 años puedo utilizar la siguiente fórmula:

=CONTAR.SI.CONJUNTO(B2:B16, "Hombre", C2:C16, ">18")

El resultado se muestra en la celda F2 de la siguiente imagen:

69

Cuando necesites contar elementos en Excel utilizando múltiples criterios, la función CONTAR.SI.CONJUNTO será de mucha utilidad.

La función SUMAR.SI en Excel

La función SUMAR.SI en Excel nos permite hacer una suma de celdas que cumplen con un determinado criterio y de esta manera excluir aquellas celdas que no nos interesa incluir en la operación.

Sintaxis de la función SUMAR.SI

La función SUMAR.SI tiene tres argumentos que explicaré a continuación.

Rango (obligatorio): El rango de celdas que será evaluado.

70

Criterio (obligatorio): La condición que deben cumplir las celdas que serán incluidas en la suma.

Rango_suma (opcional): Las celdas que se van a sumar. En caso de que sea omitido se sumaran las celdas especificadas en Rango.

El Criterio de la suma puede estar especificado como número, texto o expresión. Si es un número hará que se sumen solamente las celdas que sean iguales a dicho número. Si el criterio es una expresión podremos especificar alguna condición de mayor o menor que.

Si el Criterio es un texto es porque seguramente necesito que se cumpla una condición en cierta columna que contiene datos de tipo texto pero realizar la suma de otra columna que tiene valores numéricos. Todos estos casos quedarán más claros con los siguientes ejemplos.

Ejemplos de la función SUMAR.SI

El primer ejemplo es muy sencillo ya que de una lista de valores aleatorios quiero sumar todas las celdas que contienen el número 5.

Recuerda que la función SUMAR.SI no realiza una cuenta de las celdas que contienen el número 5, de lo contrario el resultado habría sido 2. La función SUMAR.SI encuentra las celdas que tienen el número 5 y suma su valor. Ya que las celdas A2 y A7 cumplen con la condición establecida se hace la suma de ambas celdas lo cual da el número 10 como resultado.

Ahora cambiaré la condición a una expresión y sumare aquellas celdas que sean menores a 3. Observa el resultado de esta nueva fórmula.

71

Ventas de un vendedor

Ahora utilizaremos un criterio en texto y el tercer argumento de la función SUMAR.SI el cual nos deja especificar un rango de suma diferente al rango donde se aplica el criterio. En el siguiente ejemplo tengo una lista de vendedores y deseo conocer el total de ventas de un vendedor específico.

Para obtener el resultado colocaré el rango A2:A10 como el rango que debe ser igual al texto en la celda F1. El tercer argumento de la función contiene el rango C2:C20 el cual tiene los montos que deseo sumar.

La celda F2 que contiene la función SUMAR.SI muestra la suma de las ventas que pertenecen a Juan y excluye el resto de celdas. Podría modificar un poco esta fórmula para obtener las ventas de un mes específico. Observa el resultado de esta adecuación en la celda F5:

72

La función SUMAR.SI.CONJUNTO en Excel

La función SUMAR.SI.CONJUNTO en Excel nos permite sumar los valores de un rango de celdas que cumplen con varias condiciones. A diferencia de la función SUMAR.SI que permite un solo criterio, la función SUMAR.SI.CONJUNTO permite hasta 127 criterios.

Sintaxis de la función SUMAR.SI.CONJUNTO

La función SUMAR.SI.CONJUNTO tiene dos argumentos:

73

Rango_suma (obligatorio): El rango de celdas que contiene los valores a sumar.

Rango_criterios1 (obligatorio): El rango de celdas que será evaluado por el Criterio1.

Criterio1 (obligatorio): El criterio que deben cumplir las celdas del Rango_criterios1.

Rango_criterios2 (opcional): El segundo rango de celdas que será evaluado por el Criterio2.

Criterio2 (opcional): El criterio que deben cumplir las celdas del Rango_criterios2.

Los valores de las celdas del rango_suma se sumarán solamente si cumplen con los criterios especificados. Los criterios pueden utilizar caracteres comodín como el signo de interrogación (?) para indicar un solo carácter o un asterisco (*) que indicará una secuencia de caracteres.

Solo el Rango_criterios1 y Criterio1 son obligatorios, a partir de ellos podemos especificar múltiples combinaciones de Rango_criterio y Criterio hasta un máximo de 127.

Ejemplo de la función SUMAR.SI.CONJUNTO

Tengo una lista de CDs vendidos en un día en una tienda de música. Me interesa sumar el número total de CDs vendidos que tengan exactamente 12 canciones:

74

La columna que se sumará será la columna Vendidos la cual contiene el número total de CDs vendidos para cada álbum. La columna Canciones es el Rango_criterios1 porque contiene la información que será evaluada.

Finalmente la condición que se debe cumplir se encuentra en la celda B17. Puedes ver en color verde las celdas que cumplen con la condición y cuya celda Vendido termina siendo sumada por la función SUMAR.SI.CONJUNTO.

Múltiples criterios con la función SUMAR.SI.CONJUNTO

Modificando un poco el ejemplo anterior, ahora deseo sumar el número total de CDs vendidos que tengan más de 11 canciones y cuyo precio sea mayor a $4. Para resolver este caso necesitaré especificar un segundo criterio que se aplicará a la columna Precio.

En este segundo ejemplo he agregado otro rango de criterios que es la columna Precio y su respectivo criterio que se encuentra en la celda B18. Puedes ver en color verde aquellas celdas que cumplen con ambas condiciones y cuya columna Vendidos dan como resultado la suma de la celda B19.

La función SUMAR.SI.CONJUNTO en Excel será de gran utilidad cuando necesitamos aplicar más de un criterio antes de realizar una suma.

75

La función SI en Excel

La función SI en Excel es parte del grupo de funciones Lógicas y nos permite evaluar una condición para determinar si es falsa o verdadera. La función SI es de gran ayuda para tomar decisiones en base al resultado obtenido en la prueba lógica.

Sintaxis de la función SI

Además de especificar la prueba lógica para la función SI también podemos especificar valores a devolver de acuerdo al resultado de la función.

Prueba_lógica (obligatorio): Expresión lógica que será evaluada para conocer si el resultado es VERDADERO o FALSO.

Valor_si_verdadero (opcional): El valor que se devolverá en caso de que el resultado de la Prueba_lógica sea VERDADERO.

Valor_si_falso (opcional): El valor que se devolverá si el resultado de la evaluación es FALSO.

La Prueba_lógica puede ser una expresión que utilice cualquier operador lógico o también puede ser una función de Excel que regrese como resultado VERDADERO o FALSO.

Los argumentos Valor_si_verdadero y Valor_si_falso pueden ser cadenas de texto, números, referencias a otra celda o inclusive otra función de Excel que se ejecutará de acuerdo al resultado de la Prueba_lógica.

Ejemplos de la función SI

Probaremos la función SI con el siguiente ejemplo. Tengo una lista de alumnos con sus calificaciones correspondientes en la columna B. Utilizando la función SI desplegaré un mensaje de APROBADO si la calificación del alumno es superior o igual a 60 y un mensaje de REPROBADO si la calificación es menor a 60. La función que utilizaré será la siguiente:

76

=SI(B2>=60,"APROBADO","REPROBADO")

Observa el resultado al aplicar esta fórmula en todas las celdas de la columna C.

Utilizar una función como prueba lógica

Es posible utilizar el resultado de otra función como la prueba lógica que necesita la función SI siempre y cuando esa otra función regrese como resultado VERDADERO o FALSO. Un ejemplo de este tipo de función es la función ESNUMERO la cual evalúa el contenido de una celda y devuelve el valor VERDADERO en caso de que sea un valor numérico. En este ejemplo quiero desplegar la leyenda “SI” en caso de que la celda de la columna A efectivamente tenga un número, de lo contrario se mostrará la leyenda “NO”.

=SI(ESNUMERO(A2), "SI", "NO")

Este es el resultado de aplicar la fórmula sobre los datos de la hoja:

77

Utilizar una función como valor de regreso

Como último ejemplo mostraré que es posible utilizar una función para especificar el valor de regreso. Utilizando como base el ejemplo anterior, necesito que en caso de que la celda de la columna A contenga un valor numérico se le sume el valor que colocaré en la celda D1. La función que me ayudará a realizar esta operación es la siguiente:

=SI(ESNUMERO(A2), SUMA(A2, $D$1), "NO")

Como puedes observar, el segundo argumento es una función la cual se ejecutará en caso de que la prueba lógica sea verdadera. Observa el resultado de esta fórmula:

Sólo en los casos donde la función SI era verdadera se realizó la suma. De la misma manera podríamos colocar una función para el tercer argumento en caso de que el resultado de la prueba lógica fuera falso.

78

Función SI anidada

La función SI es una de las funciones más utilizadas en Excel. Lo que ésta función hace es probar si una condición es verdadera ó falsa. Si la condición es verdadera entonces la función hace alguna cosa, y si la condición es falsa entonces se hace otra cosa diferente.

Explicación de la función SI anidada

Una función SI anidada es cuando una segunda función SI se coloca dentro de la primera de manera que pruebe alguna condición adicional. Las funciones SI anidadas aumentan la flexibilidad de la función al ampliar el número de posibles resultados a probar.

Supongamos que necesitamos hacer una equivalencia de una columna de letras a números de la siguiente manera: A = 5 y B = 10. Si la celda es igual a “A” entonces el valor será 5, pero si la celda es “B” entonces el valor será 10. Esto lo podemos resolver con la siguiente función SI anidada:

SI(celda = "A", 5, SI(celda = "B", 10))

El resultado será el siguiente:

¿Qué pasaría si ahora en lugar de dos letras tenemos tres? Es decir, que si encontramos una “C” debemos colocar el valor 20. La función SI anidada será la siguiente:

SI(celda = "A", 5, SI(celda = "B", 10, SI(celda = "C", 20)))

Una función SI anidada puede ayudarte a encontrar una buena solución a tu problema pero no debes hacer un uso excesivo de esta funcionalidad.Excel 2010 soporta hasta 64 funciones SI anidadas, pero seguramente no llegarás ni a la mitad de esa cantidad antes de que se dificulte entender la lógica empleada en todas esas funciones. Mientras tanto disfruta de tus funciones SI anidadas.

La función O en Excel

79

La función O es una de las funciones lógicas de Excel y como cualquier otra función lógica solamente devuelve los valores VERDADERO o FALSO después de haber evaluado las expresiones lógicas que se hayan colocado como argumentos.

Sintaxis de la función O

La función O en Excel nos ayudará a determinar si al menos uno de los argumentos de la función es VERDADERO.

Valor_lógico1 (obligatorio): Expresión lógica que será evaluada por la función.

Valor_lógico2 (opcional): A partir del segundo argumento las expresiones lógicas a evaluar con opcionales hasta un máximo de 255.

La única manera en que la función O devuelva el valor FALSO es que todas las expresiones lógicas sean falsas. Si al menos una expresión es verdadera entonces el resultado de la función O será VERDADERO.

Ejemplos de la función O

Para comprobar el comportamiento de la función O haremos un ejemplo sencillo con la siguiente fórmula:

=O(1=2, 3>4, 5<>5, 7<=6, 8>=9)

Si analizas con detenimiento cada una de las expresiones verás que todas son falsas y por lo tanto la función O devolverá el valor FALSO. Observa el resultado:

80

Como mencioné anteriormente, la función O devolverá un valor VERDADERO si al menos una de las expresiones lógicas es verdadera. En nuestro ejemplo modificaré solamente la primera expresión para que sea 1=1 de manera que tenga la siguiente fórmula:

=O(1=1, 3>4, 5<>5, 7<=6, 8>=9)

Esto deberá ser suficiente para que la función O devuelva un valor VERDADERO:

Funciones como argumento de la función O

Podemos utilizar funciones como argumentos de la función O siempre y cuando devuelvan VERDADERO o FALSO como resultado. En el siguiente ejemplo utilizo las funciones ESNUMERO y ESTEXTO para evaluar el tipo de dato de las celdas B1 y B2.

Ya que la celda B1 es un número la función ESNUMERO regresa el valor VERDADERO. Por otro lado la celda B2 es efectivamente una cadena de texto y por lo tanto la función ESTEXTO devuelve el valor VERDADERO. En consecuencia la función O también regresa el valor VERDADERO. Ahora intercambiaré los valores de las celdas B1 y B2 de manera que tanto la función ESNUMERO como la función ESTEXTO devuelvan FALSO.

81

La función Y en Excel

La función Y en Excel es una función lógica que nos permitirá evaluar varias expresiones lógicas y saber si todas ellas son verdaderas. Será suficiente con que cualquiera de las expresiones sea falsa para que el resultado de la función también sea FALSO.

Sintaxis de la función Y

Los argumentos de la función Y en Excel son los siguientes:

Valor_lógico1 (obligatorio): Expresión lógica que será evaluada por la función.

Valor_lógico2 (opcional): Expresiones lógicas a evaluar, opcional hasta un máximo de 255.

La función Y solamente regresará el valor VERDADERO si todas las expresiones lógicas evaluadas son verdaderas. Bastará con que una sola expresión sea falsa para que la función Y tenga un resultado FALSO.

Ejemplos de la función Y

Observa la siguiente fórmula y te darás cuenta de que todas las expresiones son verdaderas:

=Y(5=5, 1<3, 8>6)

Ya que todas las expresiones son verdaderas, la función Y devolverá también el valor VERDADERO.

82

Ahora agregaré una cuarta expresión que será falsa: 7<>7. Observa cómo será suficiente para que la función Y devuelva el valor FALSO:

Argumentos de la función Y

Cada uno de los argumentos de la función Y puede ser una expresión lógica como en los ejemplos anteriores pero también puede ser una función de Excel que devuelva un valor FALSO o VERDADERO.

Observa el siguiente ejemplo donde he combinado una expresión lógica con la función ES.PAR y la función ES.IMPAR las cuales evalúan un número para indicarnos si es par o impar respectivamente.

Ya que tanto la expresión lógica como las funciones son evaluadas como verdaderas, entonces la función Y nos un resultado VERDADERO. Ahora observa como al cambiar solamente el argumento de la función ES.IMPAR con el número 6 (en lugar de 5), causará que la función Y nos devuelva el valor FALSO.

Si alguna vez necesitas estar seguro de que todo un grupo de expresiones lógicas sean verdaderas, la función Y en Excel será una excelente opción para hacerlo.

La función BUSCARV en Excel

83

La función BUSCARV en Excel nos permite buscar un valor dentro de un rango de datos, es decir, nos ayuda a obtener el valor de una tabla que coincide con el valor que estamos buscando. Un ejemplo sencillo que podemos resolver con la función BUSCARV es la búsqueda dentro de un directorio telefónico.

Si queremos tener éxito para encontrar el teléfono de una persona dentro del directorio telefónico de nuestra localidad debemos tener el nombre completo de la persona. Posteriormente habrá que buscar el nombre dentro del directorio telefónico para entonces obtener el número correcto.

Crear una tabla de búsqueda

Para poder utilizar la función BUSCARV debemos cumplir con algunas condiciones en nuestros datos. En primer lugar debemos tener la información organizada de manera vertical con los valores por debajo de cada columna. Esto es necesario porque la función BUSCARV recorre los datos de manera vertical (por eso la “V” en su nombre) hasta encontrar la coincidencia del valor que buscamos.

Por ejemplo, nuestro directorio telefónico debería estar organizado de la siguiente manera:

Otra condición que forzosamente debemos cumplir es que la primera columna de nuestros datos debe ser la columna llave, es decir, los valores de esa columna deben identificar de manera única a cada una de las filas de datos. En este ejemplo la columna Nombre servirá como la columna llave porque no hay dos personas que se llamen igual.

Algo que debemos cuidar con la tabla de búsqueda es que si existen otras tablas de datos en la misma hoja de Excel debes dejar al menos una fila en blanco por debajo y una columna en blanco a la derecha de la tabla donde se realizará la búsqueda. Una vez que la tabla de búsqueda está lista podemos utilizar la función BUSCARV.

Sintaxis de la función BUSCARV

La función BUSCARV tiene 4 argumentos:

84

Valor_buscado (obligatorio): Este es el valor que se va a buscar en la primera columna de la tabla. Podemos colocar el texto encerrado en comillas o podemos colocar la referencia a una celda que contenga el valor buscado. Excel no hará diferencia entre mayúsculas y minúsculas.

Matriz_buscar_en (obligatorio): La tabla de búsqueda que contiene todos los datos donde se tratará de encontrar la coincidencia del Valor_buscado.

Indicador_columnas (obligatorio): Una vez que la función BUSCARV encuentre una coincidencia del Valor_buscado nos devolverá como resultado la columna que indiquemos en este argumento. El Indicador_columnas es el número de columna que deseamos obtener siendo la primera columna de la tabla la columna número 1.

Ordenado (opcional): Este argumento debe ser un valor lógico, es decir, puede ser falso o verdadero. Con este argumento indicamos si la función BUSCARV realizará una búsqueda exacta (FALSO) o una búsqueda aproximada (VERDADERO). En caso de que se omita este argumento o que especifiquemos una búsqueda aproximada se recomienda que la primera columna de la tabla de búsqueda esté ordenada de manera ascendente para obtener los mejores resultados.

Ejemplo de la función BUSCARV

Para probar la función BUSCARV con nuestra tabla de búsqueda ejemplo que contiene información de números telefónicos seguimos los siguientes pasos:

1. En la celda E1 colocaré el valor que deseo buscar.

2. En la celda E2 comienzo a introducir la función BUSCARV de la siguiente manera: =BUSCARV(

3. Hago clic en la celda E1 para incluir la referencia de celda e introduzco una coma (,) para concluir con el primer argumento de la función:=BUSCARV(E1,

85

4. Para especificar el segundo argumentos debo seleccionar la tabla de datos sin incluir los títulos de columna. Para el ejemplo será el rango de datos A2:B11. Una vez especificada la matriz de búsqueda debo introducir una coma (,) para finalizar con el segundo argumento:=BUSCARV(E1,A2:B11,

5. Como tercer argumento colocaré el número 2 ya que quiero que la función BUSCARV me devuelva el número de teléfono de la persona indicada en la celda E1. Recuerda que la numeración de columnas empieza con el 1 y por lo tanto la columna Teléfono es la columna número 2. De igual manera finalizo el tercer argumento con una coma (,):=BUSCARV(E1,A2:B11,2,

6. Para el último argumento de la función especificaré el valor FALSO ya que deseo hacer una búsqueda exacta del nombre.=BUSCARV(E1,A2:B11,2,FALSO)

Observa el resultado de la función recién descrita:

Una ventaja de haber colocado el valor buscado en la celda E1 es que podemos modificar su valor para buscar el teléfono de otra persona y la función BUSCARV actualizará el resultado automáticamente.

86

Errores comunes al usar la función BUSCARV

Si la columna llave no tiene valores únicos para cada fila entonces la función BUSCARV regresará el primer resultado encontrado que concuerde con el valor buscado.

Si especificamos un indicador de columna mayor al número de columnas de la tabla obtendremos un error de tipo #REF!

Si colocamos el indicador de columna igual a cero la función BUSCARV regresará un error de tipo #VALOR!

Si configuramos la función BUSCARV para realizar una búsqueda exacta, pero no encuentra el valor buscado, entonces la función regresará un error de tipo #N/A.

La función BUSCARV es una de las funciones más importantes en Excel. Es necesario que dediques tiempo para aprender correctamente su uso y verás que podrás sacar mucho provecho de esta función.

La función BUSCARH en Excel

La función BUSCARH en Excel busca un valor dentro de una fila y devuelve el valor que ha sido encontrado o un error #N/A en caso de no haberlo encontrado. Esta función es similar, en cierto sentido, a la función BUSCARV.

Cuando utilizar la función BUSCARH

Debemos utilizar la función BUSCARH cuando el valor que estamos buscando se encuentra en una fila de alguna tabla de datos. Por el contrario, la función BUSCARV realiza la búsqueda en una columna.

87

Sintaxis de la función BUSCARH

La función BUSCARH tiene tres argumentos que son los siguientes:

Valor_buscado (obligatorio): El valor que estamos buscando.

Matriz_buscar_en (obligatorio): El rango que contiene los valores y que debe ser una fila.

Indicador_filas (obligatorio): El número de fila que contiene el valor que regresará la función.

Ordenado (opcional): El valor debe ser FALSO si queremos una coincidencia exacta o VERDADERO para una coincidencia aproximada.

Si la función BUSCARH no encuentra el valor que está siendo buscado regresará el valor de error #N/A.

Ejemplo de la función BUSCARH

En la siguiente tabla tengo la información de los artículos que ha vendido cada uno de los vendedores en los últimos meses. Como título de columnas están los nombres de los vendedores y como filas los meses.

88

Ahora quiero saber los productos vendidos en el mes de Febrero por Paco y para ello puedo utilizar la función BUSCARH de la siguiente manera:

=BUSCARH("Paco", B1:D6, 3,FALSO)

El primer argumento es “Paco” porque es el vendedor que estoy buscando. El segundo argumento contiene todo el rango de datos sin incluir la columna de meses (columna A) porque no me interesa dicha información.

El tercer argumento es el número de fila que deseo que la función BUSCARH regrese como resultado. Ya que la fila con los nombres es la fila uno, entonces la fila para el mes de febrero es la fila número 3.

Finalmente coloca el valor FALSO en el cuarto argumento para indicar que deseo una coincidencia exacta al buscar a Paco. El resultado de esta fórmula es el siguiente:

Si quisiera busca la información del mismo mes para Luis, entonces la función cambiará de argumentos de la siguiente manera:

=BUSCARH("Luis", B1:D6, 3,FALSO)

Si por el contrario quiero conocer los productos vendidos por Hugo en el mes de Abril, entonces la función sería la siguiente:

=BUSCARH("Hugo", B1:D6, 5,FALSO)

De esta manera, la función BUSCARH nos permite hacer una búsqueda en una fila (búsqueda horizontal) y encontrar fácilmente el valor requerido.

89

La función ESERROR en Excel

La función ESERROR en Excel nos ayuda a comprobar si un valor es un error y nos devuelve el valor VERDADERO o FALSO. El valor evaluado puede ser una celda o una fórmula cuyo resultado será evaluado.

Sintaxis de la función ESERROR

Valor (obligatorio): Es el valor que se desea evaluar.

Ejemplos de la función ESERROR

La función ESERROR evalúa el valor de una celda para saber si contiene alguno de los errores de Excel. Observa la siguiente imagen que contiene todos los errores posibles en Excel y a su lado el resultado de la función ESERROR.

90

Sólo en el caso en donde la celda tenga un valor de error la función ESERROR devolverá el valor VERDADERO. En el ejemplo anterior las celdas A9, A10 y A11 no contienen un error y por lo tanto la función ESERROR devuelve el valor FALSO.

Errores en fórmulas – Parte 1

Seguramente Excel te ha mostrado alguna vez un mensaje de error mientras trabajas. Los errores son los mensajes que aparecen dentro de una celda antecedidos del símbolo #. A continuación analizaremos cada uno de los errores en Excel y la manera de solucionar el problema.

Error ##########

Si observas una celda llena de símbolos # lo que Excel está tratando de decir es que el contenido de esa celda no cabe dentro de ella.

Solución: Ajusta el ancho de la columna y el mensaje desaparecerá de inmediato.

Error #¡DIV/0!

El error se produce cuando estás dividiendo un valor entre cero.

Solución: Remover la división entre cero.

Error #N/A

Este error significa que Excel no encuentra el valor al que se está refiriendo. Es decir, el valor no está disponible. Este tipo de error es muy común al utilizar la función BUSCARV.

Solución: Utiliza la función ESERROR para atrapar el error: =SI(ESERROR(BUSCARV(…)), “VALOR NO

ENCONTRADO”, BUSCARV(…))

Error #¿NOMBRE?

La razón más común por la que se genera este error es el haber escrito mal el nombre de una función. También se produce por escribir mal un rango nombrado.

Solución: Asegúrate de haber escrito el nombre de la función correctamente así como todos los rangos nombrados que utilices en las funciones.

91

En una siguiente publicación continuaremos con la revisión de los errores de Excel.

Errores en fórmulas – Parte 2

Los errores que a veces Excel nos muestra al utilizar una fórmula son algo que nos ha sucedido a todos. En esta ocasión continuaremos con la revisión de cada uno de ellos. En la publicación anterior revisamos los primeros cuatro tipos de errores y ahora es el momento de revisar los siguientes tipos de error:

Error #¡NULO!

Este error es poco común, pero generalmente es consecuencia de haber especificado incorrectamente un rango. Una fórmula que regresará este tipo de error es la siguiente:

=SUMA(A1:A5 B1:B5)

El error se produce porque olvidé colocar el separador (,) entre los rangos especificados.

Solución: Asegúrate de que has especificado correctamente los rangos dentro de la fórmula.

Error #¡NUM!

Hay ocasiones en que los cálculos en Excel son muy exhaustivos y las fórmulas llegan a calcular valores mucho más grandes que las capacidades de Excel y es cuando obtienes un error de este tipo.

Solución: Verifica que no estás llamando repetitivamente a las funciones y que generen un número demasiado grande.

Error #¡REF!

Este es uno de los errores más comunes de Excel y sucede cuando una celda trata de referenciar otra celda que no puede ser encontrada porque se ha eliminado. Por ejemplo, escribe la siguiente fórmula:

=SUMA(D3:D5, E3:E5,F3:F5)

Posteriormente elimina la columna E. Inmediatamente se mostrará este tipo de error.

Solución: Verifica que los rangos a los que hace referencia la fórmula aún existan o de lo contrario realiza los ajustes necesarios.

92

Error #¡VALOR!

Es generado por utilizar argumentos de tipo texto cuando en realidad la función espera valores numéricos. Considera la siguiente función:

=SUMA("a", "b")

La función SUMA regresará este tipo de error porque los argumentos no nos numéricos.

Solución: Verifica que los tipos de datos de los argumentos son los adecuados, en especial los argumentos que esperan un valor numérico.

Con esta publicación hemos terminado de revisar los tipos de errores que puedes llegar a encontrar en Excel. La próxima vez que encuentres un error tendrás una mejor idea de cómo solucionar el problema.

La función SUBTOTALES en Excel

La función SUBTOTALES en Excel nos ayuda a calcular el subtotal de una lista de valores. Lo interesante es que podemos indicar a la función SUBTOTALES el tipo de operación que deseamos aplicar sobre los valores.

Sintaxis de la función SUBTOTALES

Núm_función (obligatorio): Valor numérico (del 1 al 11) que indica el tipo de función que debe ser utilizada en el cálculo como SUMA, CONTAR, PROMEDIO, etc.

93

Ref1 (obligatorio): El rango que contiene los valores.

Ref2 (opcional): Un segundo rango con valores adicionales a considerar. Este argumento es opcional y hasta el argumento 254 para proveer rangos adicionales.

A continuación la tabla de valores numéricos que indican las operaciones que deseamos realizar con la función SUBTOTALES:

Ejemplo de la función SUBTOTALES

Tengo una lista de valores en el rango A1:A10 y deseo utilizar la función SUBTOTALES para calcular el promedio para lo cual utilizaré la siguiente fórmula:

Observa que al especificar el número 1 como el primer argumento de la función SUBTOTALES estamos indicando que se utilice la función PROMEDIO. Si por otro lado quisiera obtener la suma de dos listas de números puedo utilizar la función SUBTOTALES de la siguiente manera:

94

Filas ocultas con la función SUBTOTALES

Un posible inconveniente de la función SUBTOTALES es que al momento de ocultar alguna de las filas que tienen valores incluidos en la operación se tiene un impacto en el resultado. En la siguiente imagen puedes observar un ejemplo donde hago una cuenta de los elementos del rango A1:A10.

Sin embargo, si oculto las filas 4 y 5, la función SUBTOTALES no refleja ningún cambio y sigue tomando en cuenta las filas ocultas:

95

Si queremos pasar por alto los valores ocultos podemos hacer uso de un identificador de función diferente para el primer argumento de la función SUBTOTALES:

Para el ejemplo anterior, si utilizamos el valor 102 en lugar de 2 como el primer argumento de la función SUBTOTALESobtendremos el siguiente resultado:

En este caso la función SUBTOTALES ha pasado por alto los valores de las filas ocultas. Es importante mencionar que esta funcionalidad de no tomar en cuenta los valores ocultos solamente funcionará con filas ocultas y no con columnas ocultas.

96

La función BDCONTAR en Excel

La función BDCONTAR en Excel cuenta los registros dentro de una columna numérica que permanecen después de aplicar un grupo de criterios sobre los datos. La función se puede utilizar sobre cualquier rango de celdas siempre y cuando se incluya un nombre para cada columna.

Sintaxis de la función BDCONTAR

La función BDCONTAR tiene tres argumentos obligatorios:

Base_de_Datos (obligatorio): El rango de celdas que compone la base de datos en donde haremos la consulta. La primera fila del rango contiene los nombres de cada columna.

Nombre_de_campo (obligatorio): La columna, que contiene datos numéricos y sobre la cual se hará la cuenta de registros. Es posible escribir el nombre de la columna o el número que represente la posición de la columna dentro del rango.

Criterios (obligatorio): El rango de celdas donde están especificadas las condiciones que deben cumplirse. El rango de criterios debe tener en la primera fila el nombre de la columna a la cual se aplicará el criterio.

Ejemplo de la función BDCONTAR

En la siguiente imagen podrás observar una base de datos en el rango A4:D9 que contiene información de productos. Deseo saber la cantidad de registros de la tabla que tienen un IDProducto mayor a 25032.

97

La condición a cumplir está indicada en el rango A1:A2 el cual tiene un título de columna idéntico al título de la tabla donde están los datos. La función que he colocado en la celda G2 es la siguiente:

=BDCONTAR(A4:D9, "IDProducto", A1:A2)

El primer argumento es precisamente el rango de celdas que contiene los datos. El segundo argumento es el nombre de la columna que tiene los datos numéricos que serán contados. El tercer argumento especifica las celdas donde se encuentra la condición que se debe cumplir. La función DBCONTAR de la celda G1 devuelve el resultado correcto porque solamente existen dos registros que tienen un IDProducto mayor a 25032 que son las filas 8 y 9.

Criterios para varias columnas

En el ejemplo anterior solamente usé un criterio para la columna IDProducto, sin embargo es posible indicar a la función BDCONTAR un criterio para más de una columna. En el siguiente ejemplo obtendré la cuenta de los registros que tienen un IDPRODUCTO diferente a 25032 y que además tengan una cantidad de 5:

98

La diferencia más importante con el primer ejemplo es que he ampliado el rango de criterios para incluir la

celda B2. =BDCONTAR(A4:D9, "IDProducto", A1:B2)

Solamente la fila 6 cumple con la condición de tener un IDProducto diferente a 25032 y además tener una cantidad igual a 5. Como te puedes dar cuenta, es posible indicar un criterio para cada columna y la función BDCONTAR contará los registros que cumplan con todas la condiciones. En el siguiente ejemplo puedes observar que he indicado un criterio adicional para la columna Costo Total:

El único cambio es de nuevo el tercer argumento de la función BDCONTAR para indicar el rango correcto donde se encuentran todos los criterios a cumplir. Bajo estas condiciones, son las filas 8 y 9 que cumplen con todos los criterios.

Múltiples criterios para una misma columna

También es posible indicar a la función BDCONTAR varios criterios para una misma columna y lo único que debemos hacer es colocar el nuevo criterio en una nueva fila dentro del rango de criterios. Observa el siguiente ejemplo:

99

La función BDCONTAR contará los registros que tengan una cantidad igual a 2 e igual a 5 que incluye a los registros en las filas 6, 7, y 8. Ahora observa el siguiente ejemplo:

En este caso la función BDCONTAR contará los registros que tienen una cantidad igual a 2 y además los registros que tienen una cantidad igual a 5 que además tienen un costo unitario mayor a 5. En este ejemplo solo las filas 6 y 7 cumplen con los criterios especificados. Así que ahora ya sabes que al trabajar con bases de datos, la función BDCONTAR nos ayudará a contar aquellos registros que cumplan con los criterios que especifiquemos.

Función Excel BDEXTRAER

Nombre en inglés: DGET

Extrae un solo valor de una columna de la base de datos que cumple con los criterios indicados.

Sintaxis

BDEXTRAER(base_de_datos, nombre_de_campo, criterios)

base_de_datos (obligatorio): Rango de celdas que compone la base de datos. La primera fila del rango contiene los títulos de cada columna

nombre_de_campo (obligatorio): La columna que contiene el valor que se extraerá.

criterios (obligatorio): Rango de celdas que contiene los criterios a cumplir.

Ejemplos

BDEXTRAER(A4:E20, “Nombre”, A1:A2) = Devuelve el valor de la celda de la columna “Nombre” que cumple con la condición específicada en A1:A2

100

Función Excel BDMAX

Nombre en inglés: DMAX

Obtiene el valor máximo de una columna de la base de datos considerando solo los registros que cumplen con los criterios establecidos.

Sintaxis

BDMAX(base_de_datos, nombre_de_campo, criterios)

base_de_datos (obligatorio): Rango de celdas que compone la base de datos. La primera fila del rango contiene los títulos de cada columna

nombre_de_campo (obligatorio): La columna de la cual se obtendrá el valor máximo.

criterios (obligatorio): Rango de celdas que contiene los criterios a cumplir.

Ejemplos

BDMAX(A4:E20, “Edad”, A1:E2) = Devuelve el valor máximo de la columna “Edad” que cumple con las condiciones específicadas en A1:E2

Función Excel BDMIN

Nombre en inglés: DMIN

Obtiene el valor mínimo de una columna de la base de datos considerando solo los registros que cumplen con los criterios establecidos.

Sintaxis

BDMIN(base_de_datos, nombre_de_campo, criterios)

base_de_datos (obligatorio): Rango de celdas que compone la base de datos. La primera fila del rango contiene los títulos de cada columna

nombre_de_campo (obligatorio): La columna de la cual se obtendrá el valor mínimo.

criterios (obligatorio): Rango de celdas que contiene los criterios a cumplir.

Ejemplos

BDMIN(A4:E20, “Edad”, A1:E2) = Devuelve el valor mínimo de la columna “Edad” que cumple con las condiciones específicadas en A1:E2

101

Función Excel BDSUMA

Nombre en inglés: DSUM

Suma los valores de una columna de la base de datos que cumplen con las condiciones especificadas.

Sintaxis

BDSUMA(base_de_datos, nombre_de_campo, criterios)

base_de_datos (obligatorio): Rango de celdas que compone la base de datos. La primera fila del rango contiene los títulos de cada columna

nombre_de_campo (obligatorio): La columna con los valores a sumar.

criterios (obligatorio): Rango de celdas que contiene los criterios a cumplir.

Ejemplos

BDSUMA(A4:E20, “Edad”, A1:E2) = Obtiene la suma de la columna “Edad” que cumplen con los criterios establecidas en A1:E2

Función Excel BDPROMEDIO

Calcula el promedio de los valores de una columna de la base de datos que cumplen con los criterios establecidos.

Sintaxis

BDPROMEDIO(base_de_datos, nombre_de_campo, criterios)

base_de_datos (obligatorio): Rango de celdas que compone la base de datos. La primera fila del rango contiene los títulos de cada columna

nombre_de_campo (obligatorio): La columna que contiene los valores que serán promediados.

criterios (obligatorio): Rango de celdas que contiene los criterios a cumplir.

Ejemplos

BDPROMEDIO(A4:E20, “Edad”, A1:E2) = Obtiene el promedio de la columna “Edad” que cumplen con las condiciones establecidas en A1:E2

102

FILTROS

Filtrar registros de una tabla

Existe una característica en Excel conocida como Auto filtrar que permite esconder los registros de una tabla de Excel excepto aquellos que deseamos ver. Al filtrar los registros de una tabla sólo estamos mostrando un subconjunto de los datos y de esa manera tenemos una mejor visibilidad de los mismos para realizar un buen análisis.

Filtrar datos en Excel

El filtrado no re-ordena los datos, simplemente oculta de manera temporal los registros que no cumplen con los criterios especificados. Para aplicar un filtro sobre los datos de una tabla sigue los siguientes pasos. Haz clic dentro de la tabla y selecciona el comando Ordenar y Filtrar que se encuentra en el grupo Modificar de la ficha Inicio y dentro de las opciones mostradas selecciona Filtro:

Inmediatamente aparecen flechas de filtrado al lado de los encabezados de cada columna. Si ya habías dado formato a los datos como tabla previamente, entonces estas flechas de filtrado ya estaban presentes. Ahora haz clic sobre la flecha de filtrado de la columna que deseas filtrar y Excel desplegará una lista que incluye cada uno de los valores únicos de la columna.

Para aplicar el filtro en los datos remueve la marca de selección de la opción (Seleccionar todo). Esto limpiará todas las cajas de selección permitiéndote seleccionar solamente las opciones que estás interesado ver:

103

Filtrar por varios criterios o columnas

Puedes seleccionar múltiples cajas de selección para filtrar por varios criterios. Al hacer clic en el botón Aceptar Excel desplegará solamente los datos que cumplen son la selección realizada. También puedes aplicar filtros en varias columnas a la vez, solamente repite los pasos que acabamos de revisar para cualquier otra columna.

Podrás notar que una columna tiene un filtro aplicado porque el ícono de flecha de filtrado es reemplazado por un ícono de filtro.

Remover un filtro

Para remover un filtro puedes hacer clic sobre el ícono de filtro de la columna deseada y seleccionar la opción Borrar filtro

Si has aplicado filtros a varias columnas y deseas quitar todos los filtros al mismo tiempo puedes hacer clic en el comando Ordenar y filtrar y seleccionar la opción Borrar

104

Filtrar números en Excel

Excel provee múltiples opciones para filtrar datos numéricos como filtrar los valores superiores o inferiores, filtrar los valores que son mayores o menores a un número específico, etc. Ya hemos visto cómo filtrar registros de una tabla y cómo Excel coloca automáticamente flechas de filtrado para cada columna.

Filtros de número en Excel

Pero existe una opción de menú especial para las columnas que son numéricas y que revisaremos a continuación. Haz clic sobre el icono de filtrado de una columna de datos numéricos y selecciona la opción de menú Filtros de número. Observa las opciones disponibles:

Primero probaremos la opción Diez mejores. Esta opción muestra el cuadro de diálogo Autofiltro de las diez mejores

En la primera lista de selección podrás decidir si deseas obtener los valores superiores o inferiores. En la segunda opción puedes especificar el número de elementos que deseas observar (desde 1 hasta 500). En la última lista de selección eliges si deseas filtrar los elementos o filtrar por su porcentaje. Un ejemplo de porcentaje podría ser obtener los 10 clientes superiores por el porcentaje de ventas. Una vez que has especificado los valores adecuados haz clic en Aceptar y Excel desplegará los registros que cumplen con el criterio especificado.

Otra de las opciones de Filtros de número es filtrar por Superior del promedio o Inferior del promedio. Estas opciones son directas y en cuanto las selecciones Excel aplicará el filtro automáticamente. Por último podrás observar que el resto de los filtros posibles se puede realizar a través del cuadro de diálogo Autofiltro personalizado

105

A través de este cuadro de diálogo podrás hacer comparaciones como: Igual a, mayor que, menor que, entre, etc. Solamente debes especificar la condición que necesitas y podrás filtrar números en Excel.

Filtrar datos por fecha

Si los datos de tu tabla incluyen fechas, puedes realizar un filtro en base a esas columnas de manera que puedas analizar sólo el conjunto de información que te interesa. Haz clic sobre la flecha de filtrado de la columna que contiene las fechas:

Filtros de fecha en Excel

Selecciona la opción de menú Filtros de fecha y podrás ver toda la gama de opciones que tienes disponibles para filtrar la información. Dentro de la lista encontrarás dos clases de filtros, aquellos que necesitan de un argumento para poder filtrar (filtros comunes) y aquellos que están basados en una fecha de comparación que generalmente es el día actual (filtros dinámicos).

Por ejemplo, al hacer clic en la opción Entre, se mostrará el cuadro de diálogo Autofiltro personalizado que permitirá capturar los argumentos necesarios para aplicar el filtro.

106

Si por el contrario eliges un filtro dinámico como la opción de menú Hoy, Excel aplicará de inmediato el filtro utilizando la fecha actual del sistema como referencia. Los mismo sucede con los filtros Ayer, Próxima semana, Mes pasado, Este año, etc.

Recuerda que para quitar los filtros aplicados a una columna puedes hacer clic en el comando Borrar que se encuentra en la ficha Datos.

Utilizar fórmulas en tablas de Excel

Las tablas de Excel nos ayudan en gran manera a organizar nuestros datos, pero es inevitable utilizar fórmulas para obtener información adicional sobre dicha información. Por ello en esta ocasión revisaremos algunos métodos para utilizar fórmulas en tablas de Excel.

En primer lugar he convertido un rango de datos en una tabla utilizando el comando Tabla que se encuentra en la ficha insertar.

Lo primero que debes saber es que, sin haber ninguna acción adicional, Excel ha nombrado a este rango de celdas como Tabla1 y lo podemos comprobar abriendo el Administrador de nombres que se encuentra en la ficha Fórmulas dentro del grupo Nombres definidos.

Utilizar la fila de totales

Si queremos calcular el total de alguna de las columnas de una tabla, ni siquiera tenemos que utilizar una fórmula sino que solamente debemos insertar la fila de totales de la tabla. Para ellos debemos seleccionar cualquier celda de la tabla e ir a la ficha contextual Herramientas de tabla.

107

Deberás marcar la opción Filas de totales y Excel insertará una nueva fila a la tabla. La fila de totales nos permite elegir entre varias fórmulas que pueden ser aplicadas. Al seleccionar alguna celda de la fila de totales se mostrará la lista de funciones disponibles.

De esta manera puede hacer clic sobre la celda de total que corresponde a la columna “Proyectado” e insertar la suma. Quiero que observes con detenimiento el tipo de fórmula que Excel muestra en la barra de fórmulas para la celda de la fila de totales que acabo de modificar:

Sorprendentemente Excel no utiliza la función SUMA sino que utiliza la función SUBTOTALES la cual en su primer argumento nos permite utilizar un número el cual identificará a la función a utilizar y en este caso el número 109 equivale a la función SUMA.

El segundo argumento de la función SUBTOTALES es el nombre de la columna de la tabla, que para este ejemplo es la columna “Proyectado”.

Si para esta misma columna realizo el cambio de función a Promedio podrás observar que en lugar de utilizar el número 109 se utilizará ahora el número 101 en la función SUBTOTALES:

108

Utilizando fórmulas dentro de una tabla

Como has visto en el ejemplo anterior, podemos utilizar los nombres de columnas para hacer cálculos con la información de nuestra tabla. Ese tipo de referencia es conocida como referencia estructurada.

Para hacer un ejemplo de una fórmula con referencias estructuradas agregaremos una nueva columna a nuestra tabla llamada Diferencia la cual tendrá la resta entre la columna Proyectado y la columna Real y para ello utilizaremos la fórmula [@Proyectado]-[@Real].

Si después de introducir el símbolo “=” en la celda D2 nos movemos con las flechas del teclado hacia la celda B2, Excel introducirá automáticamente el nombre *@Proyectado+. Después ingresamos el símbolo “-“para realizar la resta y finalmente nos movemos hacia la celda C2 y se insertará automáticamente el nombre [@Real].

Al momento de pulsar la tecla Entrar, se propagará automáticamente la fórmula por toda la columna. Este es un comportamiento natural de las tablas de Excel que nos ahorrará algunas acciones adicionales al ingresar nuestras fórmulas.

109

Fundamentos de formato condicional

El formato condicional en Excel es una manera de hacer que la herramienta aplique un formato especial para aquellas celdas que cumplen con ciertas condiciones. Por ejemplo, se puede utilizar el formato condicional para cambiar el color de las celdas que tienen un valor negativo.

Razón para utilizar el formato condicional

Siempre tienes la opción de aplicar manualmente un formato a cada una de las celdas que cumplen con una condición, pero eso se puede convertir en un proceso largo y repetitivo, especialmente si tienes una tabla de datos muy grande y que cambia frecuentemente. Es por eso que el formato condicional puede hacer más fácil la tarea de cambiar automáticamente el formato de la celda que cumple con ciertos criterios.

Ejemplos de formato condicional

Formato especial para todos los valores entre 20 y 30:

Resaltar los valores por debajo del promedio (Para este ejemplo el promedio es 22.2).

110

¿Cómo se aplica el formato condicional?

Debes selccionar las celdas a las que se les aplicará el formato condicional y después hacer clic en el comando Formato condicional que aparece en el grupo Estilos de la ficha Inicio.

Como ya mencioné previamente, al seleccionar alguna de las opciones del menú, el formato se aplicará para aquellas celdas que cumplan con cierta condición o regla. Un formato condicional en Excel está siempre basado en una regla que posteriormente se podrá editar si así lo deseas. Las reglas que se crean para los formatos condicionales se pueden dividir en dos grandes grupos:

Reglas basadas en valores de celda: Estas reglas se basan en el mismo valor de la celda (Mayor que, Menor que, Igual a, Entre, etc.).

Reglas basadas en fórmulas: Estas reglas ofrecen mayor flexibilidad porque puedes aplicar un formato especial utilizando una fórmula donde podrás aplicar una lógica más compleja. Por lo mismo es un poco más complicado de aprender, pero una vez que lo hagas seré muy intuitivo de utilizar.

Por ahora estudia estos conceptos básicos y en los siguientes días analizaremos algunas de las opciones que tenemos para aplicar el formato condicional.

Resaltar celdas con formato condicional

En el artículo fundamentos de formato condicional hemos aprendido las bases sobre este tema y en esta publicación aprenderemos sobre la primera de las opciones de menú de formato condicional: Resaltar reglas de celdas.

111

Esta opción de menú nos dará la oportunidad de destacar celdas que cumplan con la regla de formato condicional especificada. Cada opción muestra su propio cuadro de diálogo solicitando los argumentos necesarios para crear la regla.

Reglas de formato condicional

La opción Es mayor que muestra el siguiente cuadro de diálogo:

En el primer cuadro de texto deberás colocar un número a partir del cual se aplicará el formato especificado. Para este ejemplo coloqué el número 50 en la caja de texto, dejé el formato predeterminado y el resultado fue el siguiente:

112

Las opciones Es menor que, Es igual a, Texto que contiene, esperan que ingreses un solo valor con el cual comparar las celdas a las que se aplicará el formato. La opción Entre muestra un cuadro de diálogo diferente porque solicita 2 valores que delimitarán el rango de valores a buscar:

La opción Una fecha muestra una lista de opciones de donde podrás seleccionar la que mejor se adapte a tus necesidades.

Por supuesto, es necesario que tus celdas tengan datos de tipo fecha de manera que esta regla de formato condicional pueda encontrar valores coincidentes. Finalmente la opción Duplicar valores te permitirá resaltar ya sea los valore únicos ó los valores duplicados dentro de tus datos:

113

Solamente debes elegir la opción adecuada de la lista de selección y se aplicará la regla de formato condicional adecuada.

Aplicar formato condicional a valores duplicados o valores únicos

En esta ocasión veremos cómo aplicar formato condicional a aquellas celdas que son únicas en nuestra lista de datos ó por el contrario, aplicar formato condicional a aquellas celdas que se repiten.

Fomato condicional a valores duplicados

En primer lugar deberás seleccionar el rango de datos al cual se le aplicará el formato condicional:

114

Después deberás ir a la ficha Inicio y en el grupo Estilos hacer clic sobre Formato Condicional y posteriormente desplegar el menú Resaltar reglas de celdas y elegir la opción Duplicar valores.

Se mostrará el cuadro de diálgo Duplicar Valores.

Deja las opciones predeterminadas y haz clic en Aceptar. Todos los valores son resaltados excepto el día Sábado porque es el único de toda la lista que no se repite:

115

Formato condicional a valores únicos

Ahora vamos a invertir el formato, es decir, queremos que el formato se aplique a los valores únicos de nuestros datos y en este caso significará que solamente la celda del día Sábado quedará con formato. De nuevo selecciona el rango de datos y haz clic en la ficha Inicio en el grupo Estilos selecciona el comando Formato condicional y a continuación en Administrar reglas.

116

Eso mostrará el cuadro de diálogo Administrador de reglas de formato condicionales.

En el cuadro de diálogo podrás observar la regla que acabamos de crear. Haz clic sobre la regla para seleccionarla y posteriormente clic sobre el botón Editar regla lo cual mostrará el cuadro de diálogo Editar regla de formato. Sobre este nuevo cuadro de diálogo selecciona la opción única dentro de la lista desplegable.

117

Haz clic en el botón Aceptar. Y de nuevo haz clic sobre Aceptar y se aplicará la nueva regla sobre los datos dejando solamente al día Sábado con un formato especial por ser el único valor que no se repite.

118

El formato condicional es una característica muy poderosa de Microsoft Excel que te ayudará a presentar adecuadamente los datos.

Reglas superiores e inferiores para analizar datos

Después de haber introducido los conceptos fundamentales de formato condicional, ahora analizaremos con un poco más de detenimiento algunos tipos de reglas de formato condicional especiales. Estas reglas se muestran dentro del menú Formato condicional y dentro de la opción Reglas superiores e inferiores.

Reglas superiores e inferiores de formato condicional

La regla 10 superiores te permitirá resaltar de manera inmediata las celdas que contengan los 10 valores con mayor valor dentro del rango de celdas. El cuadro de diálogo de esta regla es el siguiente:

119

Observa que puedes cambiar el número de elementos a seleccionar, por lo que en lugar de los 10 superiores podrías seleccionar los 20 superiores si así lo deseas. La regla 10 inferiores funciona de manera similar, sola que Excel obtendrá las celdas con menor valor dentro del rango.

La regla 10% de valores superiores es similar a las reglas anteriores, pero en lugar de indicar un número específico de celdas se introduce un porcentaje de celdas a seleccionar. Por ejemplo, dentro de un rango que contiene 20 celdas, si se especifica un 10% entonces el formato condicional se aplicará a las 2 celdas con mayor valor porque el 10% de 20 celdas son 2 celdas. El cuadro de diálogo es el siguiente:

De la misma manera puedes aumentar o disminuir el porcentaje. Si especificas un 20% para un rango de 20 celdas, entonces el formato condicional se aplicará a 4 celdas. La regla 10% de valores inferiores selecciona el 10% de celdas del rango que tenga el menor valor.

Finalmente la regla Por encima del promedio hace dos cosas. En primer lugar calcula el valor promedio de las celdas del rango seleccionado y posteriormente aplica el formato condicional a todas aquellas celdas que tienen un valor por encima del promedio recién calculado. Al no tener ningún argumento, el cuadro de diálogo de esta regla solamente solicita el formato a aplicar:

La regla Por debajo del promedio seleccionará todas las celdas que tengan un valor inferior al promedio. También puedes consultar el artículo Resaltar celdas con formato condicional para conocer más sobre otras reglas de formato condicional.

Resaltar fines de semana con formato condicional

Cuando estás creando un calendario de proyecto ó un reporte a veces es de utilidad resaltar los días que son fines de semana. Eso lo puedes hacer fácilmente y rápido aplicando una regla de formato condicional. En primer lugar deberás tener listos los datos:

120

Ahora selecciona todo el rango de fechas y en la ficha Inicio haz clic sobre Formato condicional y selecciona la opción Nueva regla:

121

Regla de formato condicional para fines de semana

Esto mostrará el cuadro de diálogo Nueva regla de formato y deberás seleccionar la opción Utilice una fórmula que determine las celdas para aplicar formato:

En el cuadro de fórmula deberás colocar la siguiente fórmula:

=DIASEM(A1, 2) > 5

Ahora solo falta indicar el color del fondo de las celdas de fin de semana. Haz clic en el botón Formato y en la sección Relleno selecciona el color de tu preferencia para marcar los fines de semana:

122

Finalmente la regla de formato queda de la siguiente manera:

Al hacer clic sobre Aceptar, las celdas que contengas una fecha de fin se semana se resaltarán con el color previamente definido:

123

Consulta más información sobre la función DIASEM

Resaltar filas alternas

Muy a menudo cuando presentamos los datos en una tabla muy grande se dificulta distinguir la información que pertenece a la misma fila. Para poder ayudar en la lectura se puede resaltar el fondo de las filas alternas con un color especial de manera que pueda distinguirse la información entre filas diferentes. Por supuesto que puedes alcanzar este formato utilizando el funcionamiento implícito de las tablas, pero si quieres probar una opción diferente continúa leyendo.

Crear la regla de formato condicional

En primer lugar deberás seleccionar los datos a los que darás formato.

124

Ve a la ficha Inicio y dentro de Formato condicional selecciona la opción Nueva regla lo cual mostrará el cuadro de diálogo Nueva regla de formato.

125

Fórmula para resaltar filas alternas

IMPORTANTE: Si ya has instalado el SP1 de Office 2010, la función RESTO regresa su nombre a RESIDUO.

Selecciona la opción Utilizar una fórmula que determine las celdas para aplicar formato y en el cuadro Dar formato a los valores donde esta fórmula sea verdadera coloca la siguiente función:

=RESTO(FILA(),2)=0

Lo que ésta fórmula significa es que cualquier fila que sea par se le aplicará un formato diferente. En caso de que quieras que sean las filas impares las que reciban el formato diferente entonces la función deberá ser:

=RESTO(FILA(),2)=1

Por otro lado, si lo que quieres es resaltar columnas, entonces puedes utilizar la siguiente función:

=RESTO(COLUMNA(),2)=0

Una ventaja de este método es que podrías resaltar cada 3 filas, solamente utiliza la siguiente fórmula:

=RESTO(FILA(),3)=0

Finalmente debes elegir el formato de las filas para lo cual debes dar clic en el botón Formato y se mostrará el cuadro de diálogo Formato de celdas. Para este ejemplo elegiremos un color de relleno diferente.

126

Después haz clic en el botón Aceptar y posteriormente de nuevo en Aceptar para cerrar el cuadro de diálogo Editar regla de formato. Los datos que habían sido seleccionados recibirán el formato de acuerdo a la regla recién creada.

127

Ahora ya conoces un truco interesante del formato condicional para resaltar filas alternas en Excel. Obtén más información sobre la función RESTO.

Borrar reglas de formato condicional

Una vez que has analizado la información y aplicado algún formato condicional es probable que desees retornar al estado inicial de la hoja de Excel, es decir, remover cualquier formato condicional aplicado a los datos.

Eliminar formato condicional de celdas seleccionadas

Una manera de remover los formatos condicionales es seleccionar las celdas que tienen el formato que deseas eliminar y hacer clic sobre Formato condicional y seleccionar la opción Borrar reglas y posteriormente Borrar reglas de las celdas seleccionadas.

Excel quitará de inmediato cualquier formato condicional que esté aplicado sobre las celdas seleccionadas.

Eliminar formato condicional de todas las celdas

El segundo método para remover los formatos es seleccionar la segunda opción del menú previamente mencionado: Borrar reglas de toda la hoja. Al seleccionar esta opción debes estar seguro de que no necesitas ningún formato condicional en toda la hoja de Excel ya que se borrarán todos y cada uno de ellos.

128

Eliminar algunas reglas de formato condicional

El último método que te mostraré se debe utilizar en caso de tener varias reglas de formato y solamente querer remover una sola de ellas. De igual manera debes seleccionar las celdas que tienen el formato y hacer clic en Formato condicional y posteriormente en la opción Administrar reglas, lo cual mostrará el siguiente cuadro de diálogo:

Este cuadro de diálogo contiene todas las reglas creadas para las celdas seleccionadas. Elige la regla que deseas borrar y haz clic en el botón Eliminar regla y posteriormente en el botón Aceptar. Excel removerá la regla seleccionada y dejará el resto de las reglas tal como estaban siendo aplicadas sobre los datos.

129

GRÁFICOS

Partes de un gráfico de Excel

Un gráfico de Excel está formado por diferentes partes que incluyen el área del gráfico, las series de datos, ejes, leyendas, rótulos del eje, entre otros. El siguiente gráfico muestra las partes de un gráfico de Excel que necesitas conocer:

Área del gráfico. Esta es el área que se encuentra definida por el marco del gráfico y que incluye todas sus partes.

Título del gráfico. Texto descriptivo del gráfico que se coloca en la parte superior.

Puntos de datos. Es un símbolo dentro del gráfico (barra, área, punto, línea) que representa un solo valor dentro de la hoja de Excel, es decir que su valor viene de una celda.

130

Series de datos. Son los puntos de datos relacionados entre sí trazados en un gráfico. Cada serie de datos tiene un color exclusivo. Un gráfico puede tener una o más series de datos a excepción de los gráficos circulares que solamente pueden tener una serie de datos.

Ejes. Un eje es la línea que sirve como referencia de medida. El eje Y es conocido como el eje vertical y generalmente contiene datos. El eje X es conocido también como el eje horizontal y suele contener las categorías del gráfico.

Área de trazado. Es el área delimitada por los ejes e incluye todas las series de datos.

Líneas de división. Son líneas opcionales que extienden los valores de los ejes de manera que faciliten su lectura e interpretación.

Título de eje: Texto descriptivo que se alinea automáticamente al eje correspondiente.

Leyenda. Un cuadro que ayuda a identificar los colores asignados a las series de datos.

Cómo crear un gráfico en Excel

Lo primero que debes hacer para crear un gráfico en Excel es organizar los datos que deseas trazar. Es decir, acomodar los datos en columnas y filas de manera que Excel pueda “entender” la información y crear el gráfico.

Observa el siguiente ejemplo de datos:

Una vez organizada la información debes seleccionar el rango completo para indicar a Excel que trazará el gráfico utilizando como base dicha información:

131

Antes de continuar debo decir que es importante conocer los tipos de gráficos en Excel de manera que puedas tomar la decisión adecuada sobre el gráfico a crear.

Crear un gráfico en Excel

El siguiente paso para crear un gráfico en Excel es ir a la ficha Insertar, y dentro del grupo Gráficos hacer clic en el tipo de gráfico que has decidido crear. Para este ejemplo he decidido crear un gráfico de columnas por lo que haré clic sobre Columna y seleccionaré la primera de las opciones:

Justo después de haber seleccionado el gráfico que deseas Excel lo incrustará en la hoja:

132

Este gráfico ha sido creado con las opciones predeterminadas de Excel, pero pronto aprenderemos a modificarlas y poder agregar características adicionales al gráfico.

Tipos de gráficos en Excel

Elegir entre los diferentes tipos de gráficos en Excel para mostrar la información adecuadamente es de suma importancia. Cada tipo de gráfico desplegará la información de una manera diferente así que utilizar el gráfico adecuado ayudará a dar la interpretación correcta a los datos.

Tipos de gráficos más utilizados

Estos son los tipos de gráficos más utilizados en Excel:

Gráficos de columna. Este tipo de gráfico hace un énfasis especial en las variaciones de los datos a través del tiempo. Las categorías de datos aparecerán en el eje horizontal y los valores en el eje vertical. Frecuentemente se compara este tipo de gráfico con los gráficos de barra, donde la diferencia principal es que en los gráficos de barra las categorías aparecen en el eje vertical.

133

Gráficos de línea. Un gráfico de línea muestra las relaciones de los cambios en los datos en un período de tiempo. Este gráfico es comparado con los gráficos de área, pero los gráficos de línea hacen un énfasis especial en las tendencias de los datos más que en las cantidades de cambio como lo hacen los gráficos de área.

Gráficos circulares. También conocidos como gráficos de pie (en inglés) o gráficos de pastel. Estos gráficos pueden contener una sola serie de datos ya que muestran los porcentajes de cada una de las partes respecto al total.

Gráficos de Barra. Un gráfico de barra hace un énfasis en la comparación entre elementos en un período de tiempo específico. Este tipo de gráfico incluye cilindros, conos y pirámides.

134

Gráficos de Área. Los gráficos de área muestran la importancia de los valores a través del tiempo. Un gráfico de área es similar a un gráfico de línea, pero ya que el área entre las líneas está relleno, el gráfico de área le da una mayor importancia a la magnitud de los valores que lo que puede hacer un gráfico de línea.

Gráficos XY (Dispersión). Los gráficos de dispersión son útiles para mostrar la relación entre diferentes puntos de datos. Este tipo de gráfico utiliza valores numéricos para ambos ejes en lugar de utilizar categorías en alguno de los ejes como en los gráficos anteriores.

Existen otros tipos de gráficos en Excel como los de superficie, anillos, burbuja, pero los analizaremos en una publicación posterior. Los gráficos presentados el día de hoy son los tipos de gráficos más utilizados en Excel y pronto aprenderás a obtener el mayor provecho de cada uno de ellos.

Modificar el tipo de gráfico

Cuando has insertado un gráfico en Excel aparecen tres fichas contextuales en la cinta de opciones y una de ellas es la Ficha Diseño. Los comandos de esta ficha los puedes utilizar para modificar el tipo de gráfico y también su diseño.

En esta ocasión hablaremos un poco sobre el grupo de comandos Tipo que se encuentra dentro de la Ficha Diseño:

Cambiar el tipo de gráfico

Si haces clic sobre el botón Cambiar tipo de gráfico se mostrará un cuadro de diálogo que te permitirá elegir un nuevo tipo. Solamente haz clic sobre el ícono del nuevo tipo y acepta los cambios.

135

Por supuesto, debes tomar en cuenta que cada tipo de gráfico puede tener requerimientos especiales en cuanto a la información que puede trazar. Lo más común es hacer un cambio entre el mismo conjunto de tipos, es decir, cambiar de un gráfico de columna agrupada a un gráfico de columna agrupada 3D ó a un gráfico de cilindro agrupado. Pero no hay mejor manera de aprender que haciendo una prueba, así que no tengas temor de experimentar ya que siempre podrás regresar al tipo de gráfico que tenías inicialmente.

El segundo comando dentro del grupo Tipo es Guardar como plantilla. Este comando permite guardar las configuraciones de los gráficos de manera que puedas utilzarlas posteriormente. Es una característica de Excel muy útil cuando tu trabajo es generar muchos reportes que utilizan los mismos tipos de gráficos una y otra vez. Ya he publicado previamente sobre este comando así que es mejor que consultes el artículo: Plantillas de gráficos.

Dar formato a un elemento de gráfico

Al seleccionar un gráfico de Excel se muestra la ficha contextual Formato la cual contiene una gran cantidad de comandos que permiten aplicar un formato a cualquier parte del gráfico. Para poder aplicar el formato debes seleccionar primero la parte del gráfico que deseas modificar.

Seleccionar un elemento de gráfico

Mi recomendación es utilizar la lista desplegable de elementos de gráfico que se encuentra en la ficha Formato dentro del grupo Selección actual:

136

Como puedes observar, esta lista contiene todos y cada uno de los elementos de tu gráfico. Al hacer clic sobre cualquiera de los elementos de la lista se mostrarán unos controladores de selección alrededor del elemento para indicar que ha sido seleccionado.

Aparte de este método de selección a través de la lista de elementos, también puedes seleccionar cualquier parte de un gráfico haciendo clic directamente sobre dicho elemento lo cual colocará los mismos controladores de selección que acabas de observar.

Dar formato a un elemento de gráfico de Excel

Ahora que el elemento ya ha sido seleccionado, puedes hacer cualquier cambio de formato a través de los comandos de la ficha Formato y solamente se aplicarán al elemento recién seleccionado. Para este ejemplo haré clic sobre uno de los Estilos de forma predeterminados:

137

Al ejecutar el comando Excel aplicará de inmediato el formato correspondiente al elemento de gráfico seleccionado:

Puedes modificar los contornos, el color de relleno, aplicar estilos de WordArt y algunos efectos de formas. Solamente asegúrate de tener seleccionado el elemento adecuado antes de aplicar el formato. Debes tener cuidado al estar haciendo estos cambios porque al seleccionar un elemento de gráfico lo puedes eliminar fácilmente con tan solo oprimir la tecla Suprimir.

138

TABLAS DINAMICAS

¿Qué es una tabla dinámica?

Una tabla dinámica es una de las herramientas más poderosas de Excel, pero también es una de las características que más usuarios de Excel se sienten intimidados a utilizar. Si eres uno de ellos te estás perdiendo de utilizar una gran herramienta de Excel.

Las tablas dinámicas te permiten resumir y analizar fácilmente grandes cantidades de información con tan sólo arrastrar y soltar las diferentes columnas que formarán el reporte.

Reportes flexibles

Es verdad que puedes formar muy buenos reportes con lo que ya sabes de Excel, pero imagina la siguiente situación. Ya has creado un gran reporte que compara el total de ventas en las diferentes regiones del país, pero ahora tus superiores han pedido que hagas otro reporte que compare las ventas de cada región con los tipos de productos vendidos. Y por si fuera poco, después de terminar el segundo reporte, te piden un tercero para comparar las ventas de los productos pero ahora por cada ciudad del país. Es muy probable que tengas que empezar desde cero para crear los nuevos reportes.

Afortunadamente Excel tiene la funcionalidad de tablas dinámicas que ayuda a resolver este problema. Al utilizar una tabla dinámica podrás crear los reportes sin escribir una sola fórmula, pero lo más notable será que podrás arreglar el reporte de una manera dinámica de acuerdo a tus necesidades.

El verdadero problema de las tablas dinámicas

Muchos usuarios de Excel evitan el uso de las tablas dinámicas porque parecieran muy complicadas a primera vista. Aunque las tablas dinámicas pueden parecer desafiantes, la realidad es que el problema radica en que muy pocas veces se explican adecuadamente. En varias ocasiones he leído libros o tutoriales en Internet que utilizan términos como “Análisis multidimensional”, que aunque es un concepto importante, el presentar este tema a un principiante con este lenguaje solo lo intimidará desde un principio.

A partir de hoy iniciaré con una serie de artículos que hablarán sobre tablas dinámicas. Mi objetivo es presentarlas con un lenguaje sencillo de manera que puedas entender fácilmente y pronto puedas utilizar adecuadamente esta característica de Excel que te hará más productivo.

139

Funcionamiento de las tablas dinámicas

Las tablas dinámicas en Excel permiten agrupar datos en una gran cantidad de maneras diferentes para poder obtener la información que necesitamos. En esta ocasión explicaré el funcionamiento básico de una tabla dinámica.

Supongamos que tienes una tabla de datos que contiene mucha información sobre las ventas de la compañía entre las cuales se encuentra una columna con los productos de la empresa, también la ciudad donde se vende y las ventas correspondientes para cada ciudad.

140

Te han solicitado un reporte con el total de ventas por ciudad y el total de ventas por producto. Así que lo que necesitas hacer es sumar las ventas para cada ciudad y sumar las ventas de cada producto para obtener el reporte. En lugar de utilizar fórmulas podemos utilizar una tabla dinámica para obtener el resultado deseado. Una tabla dinámica nos permite hacer una comparación entre diferentes columnas de una tabla. Puedes imaginarte una tabla dinámica de la siguiente manera:

Lo primero que debemos hacer es especificar los campos de nuestra tabla de datos que vamos a comparar. Elegimos las ciudades como las filas de nuestra tabla dinámica:

141

Excel tomará todos los valores de ciudades de nuestra tabla de datos y los agrupará en la tabla dinámica, es decir, colocará los valores únicos de la columna de datos eliminando las repeticiones. Ahora hacemos lo mismo para especificar las columnas de la tabla dinámica.

Finalmente elegimos una columna de valores numéricos que serán calculados y resumidos en la tabla dinámica:

142

Así tendrás un reporte que compara las ciudades y los productos y para cada combinación obtendrás el total de ventas. Lo más importante que quiero transmitir con este artículo es que las tablas dinámicas te permiten elegir entre todas las columnas de una tabla de datos y hacer comparaciones entre ellas para poder realizar un buen análisis de la información.

En la siguiente publicación tomaremos este mismo ejemplo pero ahora haciéndolo con Excel para dejar claro el concepto.

Cómo crear una tabla dinámica

Las tablas dinámicas en Excel reciben su nombre por su capacidad de cambiar dinámicamente la información agrupada con tan solo rotar las columnas o filas de la tabla. En esta ocasión veremos un ejemplo claro de cómo crearlas.

Crear una tabla dinámica en Excel 2010

Haz clic sobre cualquier celda de la tabla de datos que se desea considerar en la nueva tabla dinámica.

143

Ahora selecciona el comando Tabla dinámica que se encuentra dentro del grupo Tablas de la ficha Insertar.

Se mostrará el cuadro de diálogo Crear tabla dinámica. Si es necesario podrás ajustar el rango de datos que se considerará en la tabla dinámica.

144

En este mismo cuadro de diálogo se puede elegir si se desea colocar la tabla dinámica en una nueva hoja de Excel o en una ya existente. Haz clic en el botón Aceptar y se creará la nueva tabla dinámica.

145

Excel agregará en la parte izquierda del libro la tabla dinámica y en la parte derecha la lista de campos. Esta lista de campos está dividida en dos secciones, primero la lista de todos los campos de los cuales podremos elegir y por debajo una zona a donde arrastraremos los campos que darán forma al reporte ya sea como columna, fila, valor o como un filtro.

Para completar la tabla dinámica debemos arrastrar los campos al área correspondiente. Siguiendo el ejemplo propuesto del artículo anterior, colocaré como columna el campo Producto y como fila al campo Ciudad. Finalmente como valores colocaré el campo Ventas.

De manera predeterminada Excel aplica la función SUMA a los valores y la tabla dinámica que resulta después de hacer esta configuración es la siguiente:

146

Utilizando una tabla dinámica fue posible crear un reporte de una manera fácil y sin la necesidad de utilizar fórmulas. Pronto veremos cómo se pueden elaborar tablas dinámicas más complejas que permitirán realizar un análisis profundo de la información.

Partes de una tabla dinámica

En los artículos anteriores hemos visto cómo funcionan las tablas dinámicas y cómo crearlas en Excel. Ahora analizaremos con un poco más de detalle cada una de las partes que las conforman.

Partes de una tabla dinámica en Excel

Justo cuando se ha creado una tabla dinámica se muestra en la parte derecha de la hoja la lista de campos disponibles y por debajo las áreas donde podemos arrastrar dichos campos. Estas áreas denotan cada una de las partes de una tabla dinámica.

Filtro de informe. Los campos que coloques en esta área crearán filtros para la tabla dinámica a través de los cuales podrás restringir la información que ves en pantalla. Estos filtros son adicionales a los que se pueden hacer entre las columnas y filas especificadas.

Etiquetas de columna. Esta área contiene los campos que se mostrarán como columnas de la tabla dinámica.

Etiquetas de fila. Contiene los campos que determinan las filas de la tabla dinámica.

147

Valores. Son los campos que se colocarán como las “celdas” de la tabla dinámica y que serán totalizados para cada columna y fila.

Una vez especificados los campos para cada una de las áreas, la tabla dinámica cobra vida. Puedes tener una tabla dinámica funcional con tan solo especificar las columnas, filas y valores. Los filtros son solamente una herramienta para mejorar el análisis sobre los datos de la tabla dinámica.

Dar formato a una tabla dinámica

Una vez que has creado una tabla dinámica, Excel permite aplicarle formato fácilmente como si fuera una tabla de datos. La ficha Diseño incluye comandos especiales para aplicar formato a una tabla dinámica.

La dicha Diseño es una ficha contextual, por lo que deberás seleccionar la tabla dinámica para que se muestre.

Esta ficha está dividida en tres grupos.

148

Diseño. Este grupo permite agregar subtotales y totales generales a la tabla dinámica así como modificar aspectos básicos de diseño.

Opciones de estilo de tabla dinámica. Las opciones de este grupo permiten restringir la selección de estilos que se muestran en el grupo que se encuentra justo a su derecha. Es decir, si seleccionas la opción “Filas con bandas”, entonces se mostrarán a la derecha los estilos que tienen filas con bandas.

Estilos de tabla dinámica. Muestra la galería de estilos que se pueden aplicar a la tabla dinámica. Con tan sólo hacer clic sobre el estilo deseado se aplicará sobre la tabla.

Puedes hacer clic en el botón Más del grupo Estilos de tabla dinámica para ver todos los estilos disponibles. Con tan sólo elegir alguno de los estilos se aplicará inmediatamente a la tabla dinámica.

149

Formato de valores en una tabla dinámica

En esta ocasión te mostraré cómo dar formato rápidamente a los valores agrupados de una tabla dinámica de manera de puedan tener el formato de número adecuado. Solamente sigue los siguientes pasos:

Supongamos la siguiente tabla dinámica:

150

Para dar formato a los valores numéricos debes hacer clic sobre el campo correspondiente dentro del área Valores.

Del menú mostrado debes seleccionar la opción Configuración de campo de valor.

Se mostrará el cuadro de diálogo Configuración de campo de valor.

151

Debes hacer clic en el botón Formato de número y se mostrará el cuadro de diálogo Formato de celdas donde podrás seleccionar el formato deseado:

Después de hacer la selección adecuada acepta los cambios y de inmediato se aplicará el nuevo formato a todos los valores de la tabla dinámica:

152

Filtrar una tabla dinámica

Puedes filtrar y ordenar la información que se encuentra dentro de una tabla dinámica utilizando los filtros que Excel coloca de manera predeterminada en el reporte como Etiquetas de columna y Etiquetas de fila.

Al seleccionar cualquier de las opciones del filtro, la información será resumida y solamente mostrará un subconjunto de los datos de la tabla dinámica.

Una vez que se ha aplicado un filtro, Excel reemplaza el icono predeterminado para indicar que ese campo está siendo actualmente filtrado.

153

Para mostrar de nuevo todos los valores de los campos filtrados debes hacer clic en el botón de filtrado y seleccionar la opciónBorrar filtro de.

De igual manera puedes ordenar instantáneamente los valores de la tabla dinámica. Solamente haz clic sobre el botón de Etiquetas de fila o Etiquetas de columna y elige la opción Ordenar de A a Z o la opción Ordenar de Z a A.

Segmentación de datos en tablas dinámicas

La segmentación de datos en tablas dinámicas es una nueva característica de Excel 2010 que permite hacer un filtrado de los datos dentro de una tabla dinámica. De esta manera puedes filtrar fácilmente la información por más de una columna.

En primer lugar debes hacer clic sobre cualquier celda de la tabla dinámica y posteriormente en la ficha Opciones y dentro del grupo Ordenar y filtrar deberás hacer clic sobre el comando Insertar Segmentación de datos.

154

Excel mostrará el cuadro de diálogo Insertar segmentación de datos.

En este cuadro deberás seleccionar los campos que deseas utilizar como filtros en la tabla dinámica y Excel colocará un filtro para cada campo seleccionado:

Para filtrar la información de la tabla dinámica es suficiente con hacer clic sobre cualquiera de las opciones del filtro.

155

Excel ajustará la información de la tabla dinámica de acuerdo a las opciones seleccionadas. Para mostrar de nuevo toda la información puedes hacer clic en el botón Borrar filtro que se encuentra en la esquina superior derecha de cada panel.

Podrás agregar tantos filtros como campos disponibles tengas en la tabla dinámica, lo cual te permitirá hacer un buen análisis de la información.

Modificar campos de una tabla dinámica

156

Las tablas dinámicas son muy fáciles de manipular y de modificar. Excel permite cambiar los campos de la tabla de una manera muy sencilla y reestructurar la información con tan solo arrastrar los nuevos campos.

Lista de campos de una tabla dinámica

Las modificaciones las haremos en la Lista de campos de la tabla dinámica que se encuentra en la parte derecha de la pantalla. Esta lista se mostrará con tan solo hacer clic en cualquier celda de la tabla dinámica.

Si por alguna razón esta lista de campos no aparece en tu libro de Excel, debes activarla con el comando Lista de campo que se encuentra dentro del grupo Mostrar de la ficha Opciones.

Quitar un campo de una tabla dinámica

157

Para remover un campo arrástralo fuera del área donde se encuentra y cuando el puntero del ratón se convierta en una “X” suelta el botón del ratón para eliminar el campo. Otra manera de eliminar el campo es haciendo clic sobre él y seleccionar la opción Quitar campo.

Mover un campo de una tabla dinámica

Para mover un campo ya existente hacia una nueva ubicación solamente arrástralo de un área a otra o de igual manera selecciona cualquiera de las opciones de menú que permiten mover el campo a cualquiera de las áreas disponibles: Mover al filtro de informe, Mover a rótulos de fila, Mover a rótulos de columna o Mover a valores.

Con este método puedes cambiar fácilmente las columnas por filas y viceversa.

158

Nuevo campo en una tabla dinámica

Finalmente para agregar un nuevo campo puedes arrastrarlo desde la lista de campos hacia el área deseada. También puedes marcar la caja de selección del campo lo cual hará que sea agregado a alguna de las áreas predeterminadas.

Las tablas dinámicas son un elemento de análisis de información de Excel muy poderoso y esa fortaleza proviene de la facilidad que provee para manipular la información de acuerdo a tus necesidades.

Modificar el tipo de cálculo de una tabla dinámica

De manera predeterminada Excel utiliza la función SUMA al momento de crear los totales y subtotales de los valores de una tabla dinámica sin embargo es probable que necesites utilizar alguna otra función diferente como el promedio o la cuenta de elementos.

Para cambiar la función que se utiliza en los valores resumidos debes hacer clic sobre el campo de valor y seleccionar la opción de menú Configuración de campo de valor:

En el cuadro de diálogo mostrado podrás cambiar la función que deseas utilizar:

159

Los valores disponibles son los siguientes:

Cuenta. Contabiliza el número de registros. Éstas es la opción predeterminada si el campo es de tipo texto (en lugar de la Suma).

Promedio. Obtiene el promedio de los elementos.

Máx. Obtiene el valor numérico más alto.

Mín. Obtiene el valor numérico más bajo.

Producto. Multiplica todos los valores numéricos de ese campo, si existe algún campo de texto será ignorado.

Contar números. Obtiene el número de registros con valor numérico.

Desvest. Obtiene la desviación estándar.

Desvestp. Obtiene la desviación estándar en función de la población.

Var. Calcula la varianza de una muestra.

Varp. Calcula la varianza en función de la población.

Al aceptar los cambios Excel aplicará de inmediato el nuevo tipo de cálculo seleccionado.

Crear un gráfico dinámico

160

Un gráfico dinámico es un gráfico que está basado en los datos de una tabla dinámica y que se ajusta automáticamente al aplicar cualquier filtro sobre la información. Con este tipo de gráfico podrás ayudar en la compresión de los datos de una tabla dinámica.

Cómo crear un gráfico dinámico en Excel

Para crear un gráfico dinámico debes hacer clic sobre cualquier celda de la tabla dinámica que servirá como base del gráfico y posteriormente hacer clic sobre el comando Gráfico dinámico que se encuentra dentro del grupo Herramientas de la ficha Opciones.

Se mostrará el cuadro de diálogo Insertar gráfico de donde podrás seleccionar el tipo de gráfico que deseas utilizar.

Una vez que has seleccionado el gráfico adecuado Excel lo insertará en la hoja de trabajo. Observa cómo el gráfico se modifica al momento de aplicar algún filtro sobre la tabla dinámica:

161

De igual manera puedes filtrar la información utilizando los botones de filtrado que aparecen dentro del gráfico dinámico:

Para eliminar un gráfico dinámico solamente debes seleccionarlo y pulsar la tecla Suprimir.

162

Mover un gráfico dinámico

Es probable que encuentres útil el mover un gráfico dinámico a su propia hoja de Excel para poder trabajar con él de manera independiente e inclusive aplicar algún estilo y formato personalizado.

Mover un gráfico dinámico a otra hoja

Para mover un gráfico dinámico debes seleccionarlo y posteriormente seleccionar el comando Mover gráfico que se encuentre en el grupo Ubicación dentro de la ficha Diseño.

Se mostrará el cuadro de diálogo Mover gráfico y podrás seleccionar una nueva hoja donde será colocado.

Modificar el estilo de un gráfico dinámico

Ya sea que muevas el gráfico a una nueva hoja de Excel o lo dejes en su ubicación original puedes modificar su estilo con los comandos de la ficha Diseño, Presentación y Formato:

163

Dentro de la ficha Diseño encontrarás los comandos necesarios para seleccionar un nuevo estilo del gráfico dinámico. Con los comandos de la ficha Presentación podrás personalizar aún más el gráfico dinámico agregando títulos, rótulos de eje y leyendas. Finalmente la ficha Formato permitirá refinar los estilos de forma del gráfico creado.

Cambiar origen de datos de una tabla dinámica

Es probable que el origen de datos de una tabla dinámica haya cambiado de ubicación y ahora tengas que considerar este cambio en tu tabla dinámica. A continuación unos pasos muy sencillos para modificar el origen de los datos.

Haz clic sobre la tabla dinámica y selecciona el comando Cambiar origen de datos que se encuentra en la ficha Opciones dentro del grupo Datos.

Se mostrará el cuadro de diálogo Cambiar origen de datos de tabla dinámica el cual te permitirá ampliar (o reducir) o cambiar el rango de los datos de la tabla dinámica.

Acepta los cambios y la tabla dinámica reflejará el nuevo rango de datos que has especificado.

164

MACROS

Qué es una macro de Excel?

Si utilizas Excel frecuentemente es posible que en alguna ocasión te hayas encontrado ejecutando una misma serie de acciones una y otra vez. Esas acciones que haces repetidas veces se podrían automatizar con una macro.

Una macro es un conjunto de comandos que se almacena en un lugar especial de Excel de manera que están siempre disponibles cuando los necesites ejecutar.

Por ejemplo, si todas las mañanas creas un reporte de ventas y en ese reporte siempre das el mismo formato a los textos, se podría crear una macro para que lo haga automáticamente por ti. Las macros se utilizan principalmente para eliminar la necesidad de repetir los pasos de aquellas tareas que realizas una y otra vez.

Un lenguaje de programación

Las macros se escriben en un lenguaje de computadora especial que es conocido como Visual Basic for Applications (VBA). Este lenguaje permite acceder a prácticamente todas las funcionalidades de Excel y con ello también ampliar la funcionalidad del programa.

Pero no te preocupes si no eres un programador de computadoras, Excel provee de una herramienta especial que permite crear una macro sin necesidad de conocer los detalles del lenguaje de programación. Aunque si aceptas el desafío y te introduces en el mundo de la programación VBA pronto te convertirás en un Ninja de Excel.

Verás que crear una macro en Excel no es tan complicado y será una manera fácil y rápida de eliminar esas tareas repetitivas que todos los días te quitan minutos preciados de tu tiempo.

Para qué sirve una macro en Excel

¿Para qué sirve una macro en Excel? Una macro nos ayuda a automatizar aquellas tareas que hacemos repetidamente. Una macro es una serie de instrucciones que son guardadas dentro de un archivo de Excel para poder ser ejecutadas cuando lo necesitemos.

Automatización de tareas

Seguramente estás familiarizado con procesos de automatización en el ámbito industrial. Un ejemplo muy claro son las plantas ensambladoras de automóviles donde existen robots que han sustituido tareas que antes eran hechas por humanos. La automatización trajo beneficios como mayor eficiencia y productividad de las plantas y un mejor aprovechamiento del tiempo del personal al reducir la cantidad de tareas repetitivas que realizaban.

De la misma manera las macros nos ayudan a eliminar esas tareas repetitivas de nuestro trabajo cotidiano al permitirnos utilizar mejor nuestro tiempo en el análisis de los datos y en la toma de decisiones.

165

¿Cómo se ve una macro en Excel?

Las macros son escritas en un lenguaje de computadora conocido como VBA por sus siglas en inglés (Visual Basic for Applications). Como cualquier otro lenguaje de computadora debemos aprender a utilizar los comandos que nos ayudarán a indicar a Excel lo que deseamos hacer con nuestros datos.

Aprender el lenguaje VBA no es nada complicado y se puede lograr fácilmente. Lo que toma un poco más de tiempo es pulir nuestras habilidades de programación. Lo que quiero decir con esto es que para ser un buen programador de macros debes dedicar tiempo en resolver múltiples problemas en donde puedas llevar al límite el lenguaje VBA.

Creación de una macro

Las macros se crean con el Editor de Visual Basic el cual nos permitirá introducir el código con las instrucciones que serán ejecutadas por la macro.

Existe otro método que es utilizar la Grabadora de macros la cual irá grabando todas las acciones que realicemos en Excel hasta que detengamos la grabación. Una vez grabada la macro podremos “reproducir” de nuevo las acciones con tan solo un clic.

Mostrar la ficha Programador

Si quieres escribir una nueva macro o ejecutar una macro previamente creada, entonces debes habilitar la ficha Programador dentro de la cinta de opciones. Para mostrar esta ficha sigue los siguientes pasos.

Mostrar la ficha Programador en Excel 2010

166

Haz clic en la ficha Archivo y elige la sección Opciones. Se mostrará el cuadro de diálogo Opciones de Excel donde deberás seleccionar la opción Personalizar cinta de opciones.

En el panel de la derecha deberás asegurarte de seleccionar la ficha Programador.

Acepta los cambios y la ficha se mostrará en la cinta de opciones.

167

Grupos de la ficha Programador

El grupo Código tienes los comandos necesarios para iniciar el Editor de Visual Basic donde se puede escribir directamente código VBA. También nos permitirá ver la lista de macros disponibles para poder ejecutarlas o eliminarlas. Y no podríamos olvidar mencionar que en este grupo se encuentra el comando Grabar macro el cual nos permite crear una macro sin necesidad de saber sobre programación en VBA.

El grupo Complementos nos permite administrar y habilitar complementos como el Solver.

El grupo Controles incluye funcionalidad para agregar controles especiales a las hojas de Excel como los controles de formulario que son botones, casillas de verificación, botones de opción entre otros más que serán de gran utilidad para ampliar la funcionalidad de Excel.

El grupo XML permite importar datos de un archivo XML así como opciones útiles para codificar archivos XML. Finalmente el grupo Modificar solamente contiene el comando Panel de documentos.

Aunque pueden parecer intimidantes los comandos de la ficha Programador con el paso del tiempo te irás familiarizando poco a poco con cada uno de ellos.

La grabadora de macros

Puedes crear una macro utilizando el lenguaje de programación VBA, pero el método más sencillo es utilizar la grabadora de macros que guardará todos los pasos realizados para ejecutarlos posteriormente.

La grabadora de macros en Excel 2010

La grabadora de macros almacena cada acción que se realiza en Excel, por eso es conveniente planear con antelación los pasos a seguir de manera que no se realicen acciones innecesarias mientras se realiza la grabación. Para utilizar la grabadora de macros debes ir a la ficha Programador y seleccionar el comando Grabar macro.

168

Al pulsar el botón se mostrará el cuadro de diálogo Grabar macro.

En el cuadro de texto Nombre de la macro deberás colocar el nombre que identificará de manera única a la macro que estamos por crear. De manera opcional puedes asignar un método abreviado de teclado el cual permitirá ejecutar la macro con la combinación de teclas especificadas.

La lista de opciones Guardar macro en permite seleccionar la ubicación donde se almacenará la macro.

Este libro. Guarda la macro en el libro actual.

Libro nuevo. La macro se guarda en un libro nuevo y que pueden ser ejecutadas en cualquier libro creado durante la sesión actual de Excel.

Libro de macros personal. Esta opción permite utilizar la macro en cualquier momento sin importar el libro de Excel que se esté utilizando.

También puedes colocar una Descripción para la macro que vas a crear. Finalmente debes pulsar el botón Aceptar para iniciar con la grabación de la macro. Al terminar de ejecutar las acciones planeadas deberás pulsar el botón Detener grabación para completar la macro.

169

Crear una macro

En esta ocasión mostraré cómo crear una macro en Excel utilizando la grabadora de macros. La macro será un ejemplo muy sencillo pero permitirá ilustrar el proceso básico de creación. Voy a crear una macro que siempre introduzca el nombre de tres departamentos de una empresa y posteriormente aplique un formato especial al texto. Para iniciar la grabación debes ir al comando Grabar macro que se encuentra en la ficha Programador lo cual mostrará el siguiente cuadro de diálogo.

Observa cómo he colocado un nombre a la macro y además he especificado el método abreviado CTRL+d para ejecutarla posteriormente. Una vez que se pulsa el botón Aceptar se iniciará la grabación. Observa con detenimiento los pasos.

170

Al terminar los pasos se pulsa el comando Detener grabación y la macro habrá quedado guardada. Para ejecutar la macro recién guardada seleccionaré una nueva hoja de Excel y seleccionaré el comando Macros.

171

Al pulsar el comando Macros se mostrará la lista de todas las macros existentes y de las cuales podrás elegir la más conveniente. Al hacer clic sobre el comando Ejecutar se realizarán todas las acciones almacenadas en la macro y obtendrás el resultado esperado. Por supuesto que si utilizas el método abreviado de teclado de la macro entonces se omitirá este último cuadro de diálogo.

El código de la Grabadora de macros

Una manera muy interesante de descubrir y aprender más sobre código VBA es analizar el código generado por la Grabadora de macros. Para este ejemplo grabaremos una macro muy sencilla que solamente cambie el color de la fuente de la celda actual.

Para comenzar debemos ir a la ficha Programador y pulsar el comando Grabar macro lo cual mostrará el cuadro de diálogo donde asignaré un nombre a la macro que estoy por crear.

172

Pulsa el botón Aceptar y se comenzarán a grabar todas las acciones, así que debes actuar con cuidado porque se grabará absolutamente todo. Para la macro que estoy grabando solo haré lo siguiente: iré a la ficha Inicio y pulsaré el comando Color de fuente y seleccionaré el color rojo para la celda activa.

Una vez hecho esto debo detener la grabación de la macro y una alternativa para hacerlo es pulsar el icono que se muestra en la barra de estado.

173

Ahora que ya hemos generado la macro, pulsa el botón Macros que se encuentra en el grupo Código de la fichaProgramador. Se mostrará el cuadro de diálogo Macro que enlista todas las macros que hemos creado.

Selecciona la macro recién creada y pulsa el botón Modificar. Esto abrirá el Editor de Visual Basic y mostrará el código generado para la macro.

174

Observando este código podemos aprender varias cosas. Para empezar observamos que el objeto Selection tiene una propiedad llamada Font que es la que hace referencia a la fuente de la celda o rango seleccionado. A su vez, la propiedad Font tiene otra propiedad llamada Color que es precisamente la que define el color rojo de nuestra celda.

Aunque este ha sido un ejercicio muy sencillo, cuando tengas curiosidad o duda sobre qué objetos utilizar al programar en VBA considera utilizar la Grabadora de macros para darte una idea del camino a seguir.

Establecer seguridad de macros

La seguridad es un tema importante al hablar de macros en Excel. Si abres algún archivo que contenga una macro maliciosa puedes causar algún tipo de daño al equipo. De manera predeterminada Excel no permite ejecutar macros automáticamente.

Sin embargo, si estás creando tus propias macros y deseas remover esta protección porque sabes que no existe código malicioso, entonces puedes modificar la configuración para habilitar todas las macros. Para hacerlo debes seguir los siguientes pasos: Haz clic en la ficha Archivo y posteriormente en Opciones. Dentro del cuadro de diálogo mostrado selecciona la opción Centro de confianza y posteriormente pulsa el botón Configuración del centro de confianza. Se mostrará el cuadro de diálogo Centro de confianza.

Dentro de la sección Configuración de macros selecciona alguna de las opciones disponibles.

175

Deshabilitar todas las macros sin notificación. Deshabilita las macros y permite ejecutar solamente aquellas que estén almacenadas en un lugar confiable. Los lugares confiables se configuran en la sección Ubicaciones de confianza del mismo cuadro de diálogo.

Deshabilitar todas las macros con notificación. Muestra una alerta de seguridad advirtiendo sobre la intención de ejecutar una macro de manera que se pueda decidir si se desea ejecutar. Esta es la opción predeterminada de Excel.

Deshabilitar todas las macros excepto las firmadas digitalmente. Solamente se podrán ejecutar las macros que están firmadas digitalmente.

Habilitar todas las macros. Permite ejecutar todas las macros sin enviar alguna notificación al usuario. Esta opción es útil si se ejecutan múltiples macros totalmente confiables. Esta opción es la que corre los mayores riesgos al ejecutar una macro de una fuente desconocida.

Una vez seleccionada la opción deseada se debe pulsar el botón Aceptar para hacer los cambios permanentes.

Programando en VBA

Excel 2010 es una de las herramientas de software más poderosas para el manejo, análisis y presentación de datos. Aun y con todas sus bondades, en ocasiones Excel no llega a suplir algunas necesidades específicas de los usuarios.

Afortunadamente Excel cuenta con VBA que es un lenguaje de programación que permite extender las habilidades del programa para cubrir nuestros requerimientos. Utilizando VBA se pueden desarrollar nuevos algoritmos para analizar la información o para integrar a Excel con alguna otra aplicación como Microsoft Access.

Principios fundamentales

La programación en VBA puede ser un tanto misteriosa para la mayoría de los usuarios de Excel, sin embargo una vez que se comprenden los principios básicos de programación en VBA se comenzarán a crear soluciones robustas y efectivas.

El primer concepto importante a entender es que cada elemento de Excel es representado en VBA como un objeto. Por ejemplo, existe el objeto Workbook que representa a un libro de Excel. También existe el objeto Sheet que representa una hoja y el objeto Chart para un gráfico.

El segundo concepto importante a entender es que cada uno de estos objetos tiene propiedades y métodos. Para explicar mejor este concepto utilizaré una analogía.

Propiedades y Métodos

Supongamos que tenemos el objeto auto. Así es, un auto como el que manejamos todos los días para ir al trabajo. Este auto tiene varias propiedades como son: marca, modelo, color, tipo de transmisión las cuales ayudan a describir mejor al auto. También hay propiedades que indican su estado actual como por ejemplo gasolina disponible, temperatura del aceite, velocidad, kilómetros recorridos entre otras propiedades más. Podemos decir que las propiedades de un objeto nos ayudan a describirlo mejor en todo momento.

176

Por otro lado tenemos los métodos de un objeto que en resumen son las acciones que podemos realizar con dicho objeto. Por ejemplo, con nuestro auto podemos hacer lo siguiente: encenderlo, avanzar, vuelta a la izquierda, vuelta a la derecha, reversa, detener, apagar, etc. Todas las acciones que se pueden llevar a cabo con un objeto son conocidas como métodos.

Volviendo al terreno de Excel, el objeto Workbook tiene propiedades como ActiveSheet (Hoja activa), Name (Nombre), ReadOnly (Solo Lectura), Saved (Guardado) y algunos de sus métodos son Save (Guardar), Close (Cerrar), PrintOut(Imprimir), Protect (Proteger), Unprotect (Desproteger).

Será dificil mencionar todos los objetos de Excel y sus propiedades en esta publicación, pero lo importante a recordar en este ocasión es que cada elemento de Excel está siempre representado por un objeto en VBA y cada objeto tiene a su vezpropiedades y métodos que nos permitirán trabajar con nuestros datos.

El Editor de Visual Basic

El Editor de Visual Basic, VBE por sus siglas en inglés, es un programa independiente a Excel pero fuertemente relacionado a él porque es el programa que nos permite escribir código VBA que estará asociado a las macros.

Existen al menos dos alternativas para abrir este editor, la primera de ellas es a través del botón Visual Basic de la ficha Programador.

El segundo método para abrir este programa es, en mi opinión, el más sencillo y rápido y que es a través del atajo de teclado: ALT + F11. El Editor de Visual Basic contiene varias ventanas y barras de herramientas.

177

En la parte izquierda se muestra el Explorador de proyectos el cual muestra el proyecto VBA creado para el libro actual y además muestra las hojas pertenecientes a ese libro de Excel. Si por alguna razón no puedes visualizar este módulo puedes habilitarlo en la opción de menú Ver y seleccionando la opción Explorador de proyectos.

178

El Explorador de proyectos también nos ayuda a crear o abrir módulos de código que se serán de gran utilidad para reutilizar todas las funciones de código VBA que vayamos escribiendo.

Dentro del Editor de Visual Basic puedes observar una ventana llamada Inmediato que está en la parte inferior. Esta ventana es de mucha ayuda al momento de escribir código VBA porque permite introducir instrucciones y observar el resultado inmediato. Además, desde el código VBA podemos imprimir mensajes hacia la ventana Inmediato con el comando Debug.Printde manera que podamos depurar nuestro código. Si no puedes observar esta ventana puedes mostrarla también desde el menú Ver.

El área más grande en blanco es donde escribiremos el código VBA. Es en esa ventana en donde escribimos y editamos las instrucciones VBA que dan forma a nuestras macros.

Es importante familiarizarnos con el Editor de Visual Basic antes de iniciar con la creación de macros.

Tu primera macro con VBA

Ahora que ya sabes lo que es el Editor de Visual Basic para Aplicaciones puedo mostrarte un ejemplo muy sencillo para crear una macro. Lo primero que debes hacer es ir a la ficha Programador y hacer clic en el botón Visual Basic.

Creación de un módulo

Una vez dentro del Editor debes hacer clic derecho sobre el título del proyecto y dentro del menú seleccionar la opción Insertar y posteriormente Módulo.

179

Se creará la sección Módulos y dentro de la misma se mostrará el módulo recién creado. Puedes saber que el módulo está abierto porque su nombre se muestra en el título entre corchetes.

Si el módulo no está abierto solamente deberás hacer doble clic sobre él. Posiciónate en el área de código e introduce las siguientes instrucciones:

Antes de avanzar explicaré con detalle las instrucciones mostradas.

Subrutinas en VBA

El primer concepto que explicare es la instrucción Sub que es la abreviación de la palabra subrutina. Una subrutina no es más que un conjunto de instrucciones que se ejecutarán una por una hasta llegar al final de la subrutina que está especificado por la instrucción End Sub.

180

Las subrutinas nos ayudan a agrupar varias instrucciones de manera que podamos organizar adecuadamente nuestro código. Una subrutina siempre tiene un nombre el cual debe ser especificado justo después de la instrucción Sub y seguido por paréntesis.

La función MsgBox en VBA

La subrutina que acabamos de crear para este ejemplo solamente tiene una instrucción dentro la cual hace uso de la funciónMsgBox. Esta función nos ayuda a mostrar una ventana de mensaje de manera que podamos estar comunicados con el usuario sobre cualquier error o advertencia que necesitamos darle a conocer. Para este ejemplo he utilizado la forma más sencilla de la función MsgBox la cual solamente tiene un solo argumento que es precisamente el mensaje que necesitamos mostrar en pantalla al usuario.

Ejecutar macro

Para probar nuestro código bastará con pulsar el botón Ejecutar que se encuentra dentro de la barra de herramientas.

En cuanto se pulsa el botón se ejecutará el código recién ingresado y obtendremos el resultado en pantalla.

Listo, has creado tu primera macro la cual muestra una ventana de mensajes y despliega el texto especificado en la función MsgBox. Para guardar la macro recuerda que debes guardar el archivo como Libro de Excel habilitado para macros, de lo contrario perderás el código del módulo creado.

Utilizar comentarios en VBA

181

Utilizar comentarios dentro del código VBA es una de las mejores prácticas que debes adoptar desde que inicias en el mundo de la programación en Excel. Los comentarios harán que tu código sea fácil de entender.

Un comentario en VBA es una línea dentro del código que no será tomada en cuenta al momento de realizar la ejecución. Los comentarios serán solo visibles por ti al momento de editar el código dentro del Editor de Visual Basic.

Para agregar un comentario será suficiente con colocar una comilla sencilla (‘) al inicio de la línea. Después de colocar la comilla sencilla debes escribir el comentario y al terminar de insertar la línea Excel colocará automáticamente el texto en color verde indicando que ha reconocido la línea como un comentario en VBA.

He visto en más de una ocasión que muchas personas no tienen esta buena práctica al programar en VBA y el problema se presentará cuando pase el tiempo y tengan que modificar el código pero ya no recuerden la lógica implementada ni lo que significa cada una de las variables.

Aunque pareciera una actividad aburrida, créeme que te ahorrará mucho tiempo cuando te veas en la necesidad de modificar tu código. Además, si por alguna razón necesitas que otra persona haga modificaciones al código le serán de gran ayuda los comentarios que hayas agregado.

Comentar varías líneas de código

Como ya lo mencioné, un comentario en VBA será omitido al momento de la ejecución. En ocasiones cuando está haciendo pruebas con tu código VBA deseas evitar que ciertas líneas de código se ejecuten y una manera de hacer es comentando dichas líneas.

Para comentar varias líneas de código en una macro, sin la necesidad de estar colocando la comilla sencilla al principio de cada una de las líneas, puedes seguir los siguientes pasos. En primer lugar selecciona todas las

182

líneas de código que deseas convertir en comentarios y posteriormente oprime el botón Bloque con comentarios de manera que Excel coloque todas las comillas sencillas (‘) a cada línea de código seleccionada.

De la misma manera puedes remover las comillas sencillas si pulsas el botón Bloque sin comentarios que se encuentra justo al lado derecho del botón Bloque con comentarios.

Si no puedes ver los botones anteriores en el Editor de Visual Basic es porque seguramente tienes oculta la barra de herramientas de Edición. Para mostrarla, haz clic derecho sobre un área libre del menú superior y seleccionar la opción Edición.

Navegando el modelo de objetos

183

Excel tiene un modelo de objetos el cual es una jerarquía de todos los objetos que podemos utilizar desde el lenguaje VBA. En la parte superior de la jerarquía se encuentra el objeto Application y todos los demás objetos estarán por debajo de él.

Acceder a objetos inferiores

Para tener acceso a los objetos que están por debajo del objeto Application podemos utilizar el punto. El punto nos ayuda a navegar por la jerarquía hacia un nivel inferior. Observa lo que se muestra en el Editor de Visual Basic al colocar un punto después del objeto Application:

Por ejemplo, si deseamos poner en negritas el texto de la celda A1 debemos llegar al objeto Range el cual nos dará acceso a modificar la propiedad Bold de la siguiente manera:

Aunque esta línea de código puede tomarnos un poco de tiempo en escribirla, pero describe por completo la jerarquía de los objetos.

Objetos predeterminados

Existe una funcionalidad intrínseca de VBA conocida como objetos predeterminados la cual nos permite omitir la escritura de algunos objetos y aun así tener un código funcional. Por ejemplo, en la sentencia mostrada previamente podemos omitir el objeto Application y tener nuestro código funcionando correctamente:

Inclusive podemos omitir los objetos ActiveWorkbook y ActiveSheet sabiendo que el código se ejecutará siempre sobre el libro activo y la hoja que esté activa al momento de la ejecución:

Referencias completas a objetos

184

A algunas personas les gusta utilizar las referencias completas a los objetos, es decir, especificar toda la ruta completa hasta llegar al objeto deseado. Una razón para hacer esto es porque da una claridad absoluta sobre la ubicación exacta de cada objeto lo cual ayudará a evitar cualquier mala interpretación del código.

Si decides no hacer uso de los objetos predeterminados sino que deseas utilizar las referencias completas hacia cada objeto aún hay una manera de ahorrar algunas líneas de código. Supongamos las siguientes instrucciones en VBA:

Podemos ahorrar algunas palabras de este código haciendo uso del bloque With de la siguiente manera.

Las colecciones de objetos Workbooks y Worksheets

Un objeto en VBA puede contener otro objeto y ese objeto a su vez puede contener otro objeto y así sucesivamente. La raíz de todos los objetos en VBA se encuentra en el objeto Application el cual a su vez contiene las colecciones de objetos Workbooks y Worksheets.

El objeto Workbook y el objeto Worksheet

El objeto Workbook representa un libro de Excel y el objeto Worksheet representa una hoja de un libro de Excel. Como sabemos, un libro de Excel puede tener más de una hoja lo cual significa que un objeto Workbook puede contener más de un objeto Worksheet.

Ya que no hay límite en el número de hojas que puede tener un libro, se volvería complicado organizar esta relación entre los objetos Workbook y Worksheet y por esta razón se crearon las colecciones de objetos. De esta manera un objeto Workbook tiene asociada una colección de objetos Worksheets la cual contiene los objetos Worksheet que representan las hojas de ese libro de Excel.

De la misma manera, el objeto Application no tiene asignados directamente todos los libros de Excel sino que tiene unacolección de objetos Workbooks la cual incluirá todos los objetos Workbook de los libros de Excel que abramos en nuestro código VBA.

Abrir un libro de Excel

Para abrir un libro de Excel en VBA podemos utilizar el método Open del objeto Workbooks de la siguiente manera:

185

Application.Workbooks.Open Filename:="C:Libro1.xlsx"

Esta instrucción abrirá el archivo ubicado en “C:Libro1.xlsx” y lo agregará a la colección de objetos Workbooks. De esta manera podemos abrir tantos archivos como sean necesarios y para cada uno de ellos se creará un objeto Workbook el cual será almacenado dentro de Workbooks.

Hacer referencia a un libro en VBA

Una vez que hemos abierto los archivos que necesitamos podremos hacer referencia a cada uno de ellos a través de la colección de objetos Workbooks de la siguiente manera:

Application.Workbooks(1).Activate

El número que observas dentro de los paréntesis indica el índice del objeto Workbook dentro de la colección de objetos Workbooks. De manera predeterminada el índice 1 será para el libro de Excel que contiene el código VBA y a partir de ahí la numeración será de acuerdo al orden en que hayamos abierto otros archivos.

Si conocemos el nombre del libro podemos utilizarlo en lugar del índice y tener una instrucción como la siguiente:

Application.Workbooks("Libro1.xlsx").Activate

La colección de objetos Workbooks nos permitirá acceder a todos los libros que hayamos abierto dentro de nuestra aplicación VBA.

Acceder las hojas de un libro

De igual manera podemos acceder las hojas de cualquier libro a través de su colección de objetos Worksheets. Esta colección también puede ser accedida por el índice de cada una de las hojas del libro:

Application.Workbooks(1).Worksheets(1).Range("A1").Value = "Hola"

Esta instrucción accede a la hoja con el índice 1 y coloca el valor “Hola Mundo” en la celda A1. También podemos acceder a una hoja a través de su nombre en caso de que lo conozcamos:

Application.Workbooks(1).Worksheets("Hoja1").Range("A1").Value = "Hola"

Agregar una nueva hoja

A través de la colección de objetos Worksheets podemos crear nuevas hojas en un libro. Observa la siguiente instrucción:

Worksheets.Add

Observa que no he iniciado la instrucción anterior con el objeto Application, ni tampoco está precedida por el objeto Workbooks. Esta es una sintaxis aceptable dentro de VBA e indica que se agregará una nueva hoja al libro que esté activo en ese momento. Este es un método abreviado que podemos utilizar si estamos

186

seguros de que el libro activo es el libro al que deseamos agregar una nueva hoja. De lo contrario, podemos especificar tota la ruta completa:

Application.Workbooks("Libro1.xlsx").Worksheets.Add

Ahora ya sabemos que VBA tiene un objeto para representar los libros de Excel (Workbook) y otro objeto para representar las hojas de un libro (Worksheet). Ambos tipos de objetos son almacenados dentro de colecciones de objetos que son conocidas como Workbooks, que se refiere a la colección de libros que se han abierto y Worksheets que es la colección de hojas que pertenecen a un determinado libro.

El objeto Application en VBA

Cuando escribimos macros con VBA trabajamos con múltiples objetos que pueden ejecutar nuestras instrucciones adecuadamente, pero el objeto Application está en el nivel más alto de la jerarquía del modelo de objetos de Excel.

El objeto Application simboliza a Excel mismo y nos da acceso a opciones y configuraciones a nivel de la aplicación. Muchas de las opciones que podemos modificar con el objeto Application son las mismas que encontramos en la ficha Archivo dentro del cuadro de diálogo Opciones de Excel.

Ya que el objeto Application es el objeto principal dentro de VBA todos los demás objetos derivan de él. Es por ello que encontrarás frecuentemente instrucciones que comienzan especificando el objeto Application:

Application.ActiveSheet.Name = "Reporte de Ventas"

Sin embargo, VBA nos permite, en la mayoría de los casos, omitir la escritura del objeto Application ya que supone que todos los demás objetos provienen de él. De esta manera la siguiente instrucción también es válida.

ActiveSheet.Name = "Reporte de Ventas"

Colecciones del objeto Application

El objeto Application tiene algunas colecciones que son de mucha utilidad como Sheets, Columns y Rows. La colección Sheets nos permite acceder a todas las hojas de un libro:

Application.Sheets.Count

Es muy importante mencionar que la colección Sheets se referirá al libro de Excel que se encuentre activo en el momento de ejecutar esta instrucción. Las colecciones Columns y Rows nos permitirán acceder a las columnas y filas de la hoja activa.

Application.Columns(5).Select

Application.Rows(5).Select

Propiedades del objeto Application

187

El objeto Application tiene muchas propiedades como para mencionarles todas en esta ocasión, pero algunas de las más importantes son las siguientes:

ActiveWorkbook. Devuelve un objeto Workbook que representa el libro de Excel activo.

ActiveSheet. Regresa un objeto Worksheet que representa a la hoja que esté actualmente seleccionada (activa).

ActiveCell. Devuelve un objeto Range que representa la celda activa dentro de la hoja activa en el libro de Excel activo.

ThisWorkbook. Esta propiedad devolverá un objeto Workbook que representará el libro que contiene la macro que está siendo ejecutada.

Métodos del objeto Application

Uno de los métodos más utilizados del objeto Application es el método InputBox que nos ayuda a mostrar un cuadro de diálogo que solicita al usuario el ingreso de algún valor. Observa la siguiente línea de código:

Impresiones = Application.InputBox(Prompt:="Número de impresiones:", _

Title:="Imprimir", Default:=1, Type:=1)

Esta instrucción hará que Excel muestre un cuadro de diálogo pidiendo al usuario ingresar el número de impresiones que desea realizar. El número ingresado por el usuario se guardará en la variable Impresiones.

El libro de macros personal en Excel

Cuando creamos una macro en Excel podemos guardarla en el libro actual o podemos guardarla en el libro de macros personal. La ventaja de guardar una macro en el libro de macros personal es que nuestra macro estará disponible para cualquier libro.

El libro de macros personal

El libro de macros personal es en realidad un archivo oculto llamado personal.xlsb y que es cargado cada vez que iniciamos Excel. Si tienes Windows 7 podrás encontrar el archivo personal.xlsb en la siguiente carpeta:

C:\Usuarios\[Usuario]\AppData\Roaming\Microsoft\Excel\XLSTART

188

En la ruta que observas arriba [Usuario] es el nombre de tu usuario en el equipo. Además la carpeta AppData es una carpeta oculta por lo que no la encontrarás directamente en el navegador de Windows a menos que habilites la vista de archivos ocultos.

Creación del libro de macros personal

El libro de macros personal se crea la primera vez que se guarda una macro en él. Para hacerlo, crea una macro y especifica que deseas guardarla en el Libro de macros personal.

Cuando guardes el archivo Excel verás un mensaje preguntando si deseas guardar los cambios realizados al libro de macros personal, para lo cual deberás pulsar el botón Guardar.

El libro de macros personal en el Editor de Visual Basic

Una vez que el libro de macros personal ha sido creado lo podrás ver dentro del Editor de Visual Basic:

189

Por debajo del nombre VBAProject (PERSONAL.XLSB) encontrarás la carpeta Módulos y dentro de ella encontrarás todas las macros que se hayan guardado en el libro de macros personal organizadas en módulos.

Si deseas eliminar algún módulo solamente deberás hacer clic derecho sobre él y seleccionar la opción Quitar Módulo.

Tipos de errores en VBA

No todas las cosas funcionan bien a la primera y seguramente te encontrarás con errores al programar en Excel. Existen dos tipos de errores en VBA: errores de sintaxis y errores en tiempo de ejecución.

Errores de sintaxis en VBA

Un error de sintaxis ocurre cuando tenemos un error con el lenguaje VBA, es decir, cuando intentamos hacer algo que no está permitido. Este tipo de errores son los más fáciles de localizar porque el Editor de Visual Basic está configurado para avisarnos en el momento en que encuentra un error de este tipo en nuestro código.

Los errores de sintaxis en VBA surgen cuando intentamos insertar algún operador o alguna instrucción de VBA en un lugar que no le corresponde. Observa la siguiente imagen:

190

En este ejemplo he intentado utilizar la palabra Next en lugar del tipo de dato de la variable. Es por eso que el Editor de Visual Basic muestra un mensaje de error de compilación. La palabra Next es parte del lenguaje VBA pero la he utilizado en el lugar inapropiado y por eso obtengo el error.

De igual manera el Editor de Visual Basic notará si hemos utilizado una palabra que no pertenece al lenguaje VBA. En el siguiente ejemplo he confundido la instrucción Mod (módulo) y he colocado la palabra Mud.

Estos son solo unos ejemplos de errores de sintaxis que podemos cometer pero como lo he mencionado antes, el Editor de Visual Basic nos alertará sobre dichos errores y podremos detectarlos y corregirlos.

Errores en tiempo de ejecución

191

Un error en tiempo de ejecución ocurre cuando nuestra aplicación ya está siendo ejecutada e intenta hacer alguna acción que no está permitida por Excel o por Windows. Esto ocasionará que nuestra aplicación colapse o que Excel deje de responder.

Este tipo de errores son más difíciles de encontrar pero aun así se podrán encontrar algunos de ellos al hacer pruebas y depuración de nuestra aplicación. Algunos ejemplos de este tipo de errores son los siguientes:

Intentar realizar una operación no permitida por el ordenador. Por ejemplo una división entre cero o intentar sumar una cadena de texto y un valor Double.

Intentar utilizar una librería de código que no está accesible en ese momento.

Utilizar un bucle con una condición que nunca se cumple.

Tratar de asignar un valor que está fuera de los límites de una variable.

Existen muchas otras razones por las que podemos tener un error en tiempo de ejecución. La mejor manera de prevenir estos errores será haciendo una depuración de nuestro código pero eso será tema de otro artículo.

Lo importante por ahora es estar consiente de estos dos tipos de errores en VBA y saber que debemos estar atentos para corregir todos los errores de sintaxis de nuestro código y minimizar al máximo los posibles errores de ejecución.

Depurar macros en Excel

Cuando nos encontramos con errores en nuestras macros podemos depurar el código utilizando el Editor de Visual Basic para encontrar fácilmente los errores que pueda contener nuestro código VBA. Considera la siguiente macro:

Depurar código VBA

Para iniciar con la depuración del código podemos seleccionar la opción de menú Depuración > Paso a paso por instrucciones o simplemente pulsar la tecla F8.

192

Esto hará que se inicie la ejecución en la primera línea, la cual se mostrará con un fondo amarillo indicando que esa instrucción es la que esta por ejecutarse.

Para continuar con la depuración debemos pulsar de nuevo la tecla F8 hasta llegar al final del código. Cada vez que pulsamos la techa F8 suceden las siguientes cosas:

1. Excel ejecuta la instrucción que está sombreada en color amarillo

2. Si Excel encuentra un error en la instrucción, entonces enviará un mensaje de error.

3. Por el contrario, si no hubo error en dicha instrucción, entonces Excel marcará en amarillo la siguiente instrucción a ejecutar.

De esta manera podemos ejecutar cada una de las líneas de nuestro código VBA y validar que no exista error alguno. Regresando a nuestro ejemplo, al momento de llegar a la tercera instrucción y pulsar la tecla F8, Excel enviará el siguiente mensaje de error:

193

El mensaje nos advierte que el objeto no admite esa propiedad o método y se está refiriendo al objeto Range en donde el método Value no está escrito de manera correcta y por lo tanto el depurador de VBA no reconoce dicha propiedad. Pulsa el botón Aceptar para cerrar el cuadro de diálogo y poder corregir el error en el código.

Ya hemos hablado sobre los diferentes tipos de errores en VBA y la depuración nos ayudará a probar nuestro código y a encontrar la gran mayoría de los errores que podamos tener. Es probable que al principio veas a la depuración como un trabajo muy exhaustivo pero cuando tus programas y macros comiencen a crecer entonces verás todos los beneficios que nos da la depuración de macros en Excel.

Variables en VBA

Cuando programamos en VBA frecuentemente necesitamos un repositorio para almacenar el resultado de alguna operación. Las variables en VBA son utilizadas para guardar valores y su tipo dependerá de clase da dato que deseamos guardar dentro de ellas.

En VBA existen variables de tipo entero que almacenan números, variables de tipo doble que también almacenan números pero con decimales, variables de tipo texto para guardar una cadena de caracteres entre algunos otros tipos de variables. A continuación haremos una revisión de cada uno de estos tipos.

Variables de tipo entero

Las variables de tipo entero son utilizadas para guardar números enteros. Debemos utilizar la palabra clave Integer para declarar una variable de este tipo.

Dim x As Integer

x = 6

En la primera instrucción estoy declarando la variable con el nombre “x” y estoy indicando que será del tipo Integer. “Declarar una variable” significa avisar a Excel sobre la existencia de dicho repositorio para guardar información. En la segunda instrucción asigno el valor 6 a la variable “x”.

Variables de tipo doble

194

Las variables de tipo doble pueden almacenar números con el doble de precisión incluyendo números decimales. La palabra clave para este tipo de variables es Double.

Dim x As Double

x = 3.1416

Aunque las variables de tipo doble pueden almacenar números enteros sin problema, no es recomendable hacerlo porque estaremos desperdiciando espacio en la memoria del ordenador. Es decir, el tamaño reservado para una variable doble es el adecuado para guardar números decimales, si solo guardamos un número entero quedará especio sin utilizar. Por lo tanto es recomendable utilizar siempre el tipo de variable adecuado para cualquier número.

Variables de tipo texto

Una variable de tipo texto se declara con la palabra clave String. En el siguiente código declararé la variable título y posteriormente le asignaré un valor.

Dim libro As String

libro = "Programación en Excel"

Variables de tipo lógico

Una variable de tipo lógico es aquella que puede almacenar solamente dos valores: falso o verdadero. La palabra clave para definir estas variables es Boolean.

Dim continuar As Boolean

continuar = True

La primera línea declara la variable “booleana” y en la segunda le asignamos un valor. Solamente podemos asignar dos valores a este tipo de variables: True (verdadero) y False (falso).

Estos son los tipos de variables básicos en VBA. Existen algunos más que iré tratando en artículo posteriores. Mientras tanto es indispensable que aprendas a declarar adecuadamente las variables en VBA porque será inevitable hacer uso de ellas dentro de nuestros programas.

Cadenas de texto en VBA

Existen varias funciones en VBA que podemos utilizar para manipular cadenas de texto. A continuación revisaremos algunas de estas funciones VBA y observaremos el resultados de cada una de ellas.

Para iniciar con esta revisión, debes colocar un botón de comando dentro de una hoja de Excel y después hacer doble clic sobre él para introducir el código.

195

Unir cadenas de texto

Para unir dos (o más) cadenas de texto podemos utilizar el operador &. Observa el siguiente código:

El resultado de este código es el siguiente:

196

La función Left

La función Left en VBA nos ayuda a extraer un número determinado de caracteres a la izquierda de la cadena de texto.

En el código he especificado los 7 caracteres a la izquierda de la cadena de texto. El resultado es el siguiente:

La función Right

La función Right nos permite extraer caracteres a la derecha de una cadena de texto. Observa el siguiente código:

En esta función la cuenta de caracteres se hace de derecha a izquierda siendo el último carácter de la cadena de texto el primero que extraerá la función Right. Para este ejemplo he pedido los últimos 5 caracteres a la derecha de la cadena de texto:

197

La función Len

La función Len nos ayuda a conocer la longitud de una cadena de texto, es decir, la cantidad de caracteres que conforman a una cadena.

La función Len contará cada uno de los caracteres de la cadena y regresará un número:

La función InStr

La función InStr devuelve la posición de un carácter dentro de la cadena. Supongamos que quiero encontrar la posición de la letra “M” dentro de la cadena que contiene el valor “Hola Mundo”.

198

Es importante resaltar que la función InStr es sensible a mayúsculas y minúsculas. Observa cómo he especificado buscar la letra “M” (mayúscula) y el resultado de la función es el siguiente:

La función InStr encontró la letra “M” en la posición número 6 comenzando desde la izquierda. Si en lugar de la letra “M” busco la letra “m” (minúscula), la función InStr devolverá el valor 0 (cero) indicando que no ha encontrado dicha letra.

Además de indicar letras individuales en la función InStr, también podemos especificar palabras completas por ejemplo:

cadena = "Hola Mundo"

InStr (cadena, "Mundo")

Por ejemplo, al buscar la palabra “Mundo” dentro de la cadena de texto obtendremos como resultado la posición número 6 ya que en esa posición comienza la palabra “Mundo”.

La función Mid

Con la función Mid podemos extraer una subcadena de otra cadena de texto con tan solo especificar la posición inicial de la subcadena y su longitud. Observa el siguiente ejemplo:

199

La función Mid se moverá a la posición 15 de la cadena y a partir de ahí contará 7 caracteres y devolverá como resultado la cadena comprendida entre ambas posiciones. En nuestro ejemplo, la palabra “funcion” es la que se encuentra entre dichas posiciones.

Las funciones de texto en VBA nos ayudarán a manipular adecuadamente las cadenas de texto y podremos obtener los resultados que necesitamos.

La declaración If-Then en VBA

En ocasiones necesitamos ejecutar algunas líneas de código de nuestra macro solamente cuando alguna condición se haya cumplido. La declaración If-Then nos permite validar una condición para tomar una decisión adecuada.

La declaración If-Then en VBA es la más básica de todas las declaraciones de control de flujo que son aquellas declaraciones que nos permiten tomar decisiones en base a una condición. Esta declaración la podemos traducir como Si-Entonces y la utilizaremos en situaciones donde necesitamos realizar la siguiente evaluación: Si se cumple la condiciónEntonces haz esto.

Ejemplo de la declaración If-Then

Para probar el funcionamiento de la declaración If-Then inserta un botón de comando (Control ActiveX) en una hoja de Excel y haz doble clic sobre él para colocar el siguiente código:

200

En el primer paso se hace la declaración de las variables que utilizaré en el resto del código.

En el segundo paso asigno el valor de la celda A1 a la variable calificación.

El tercer paso contiene la declaración If-Then y que prueba Si el valor de la variable calificación es mayor o igual a 60. En caso de ser verdadero Entonces se asigna el valor “Aprobado” a la variable resultado.

El último paso es asignar el valor de la variable resultado a la celda B2.

Ahora observa el resultado al ejecutar esta macro.

La declaración Else

Parece que todo funciona muy bien en el código anterior pero aún lo podemos mejorar agregando la declaración Else de manera que tengamos una declaración de la forma If-Then-Else. Esta variante nos permite hacer la siguiente evaluación: Si se cumple la condición Entonces haz esto De lo contrario haz otra cosa.

La declaración Else en VBA nos permite indicar otro bloque de instrucciones que se deben ejecutar en caso de que la condición sea falsa. De esta manera podemos tomar una acción determinada en caso de que la condición se cumpla o en caso de que no se cumpla.

Ahora modificaré el ejemplo anterior para asegurarme de que en caso de que la condición de calificación mayor o igual a 60 no se cumpla se despliegue el resultado “reprobado”. Observa el siguiente código.

201

En el tercer paso puedes observar la declaración If-Then-Else. Ahora observa el efecto de este cambio al momento de ejecutar el código:

Por último quiero que observes que en este segundo ejemplo la declaración If-Then-Else termina con la declaración End If. Siempre que utilicemos la declaración If-Then o la declaración If-Then-Else debemos terminar con End If.

La única ocasión donde no se termina con End If es cuando la declaración If-Then se puede colocar en una sola línea como es el caso del primer ejemplo de este artículo.

Acceder celdas con VBA

Existe un par de maneras para acceder las celdas de nuestras hojas utilizando VBA. Podemos utilizar el objeto Range y también podemos utilizar el objeto Cells. A continuación revisaremos ambos objetos.

Seleccionar una celda

Si deseamos seleccionar la celda B5 podemos utilizar cualquiera de las dos instrucciones siguientes:

202

Range("B5").Select

Cells(5, 2).Select

El objeto Cells tiene como primer argumento el número de fila y como segundo argumento el número de columna.

Seleccionar un rango

Para seleccionar un rango de celdas lo más conveniente es utilizar el objeto Range de la siguiente manera:

Range("A1:D5").Select

El objeto Cells no nos permite seleccionar un rango porque solamente podemos especificar una celda a la vez.

Establecer el valor de una celda

Para establecer el valor de una celda podemos utilizar alguna de las siguientes instrucciones:

Range("B5").Value = 500

Cells(5, 2).Value = 600

Ventaja del objeto Cells

Es mucho más común encontrarse el objeto Range en las aplicaciones VBA, sin embargo el objeto Cells ofrece una ventaja que debemos considerar cuando necesitamos hacer un recorrido programático por varias celdas ya que será muy sencillo especificar las filas y columnas utilizando una variable numérica.

For i = 1 To 10

For j = 1 To 5

Cells(i, j).Value = i * j

Next j

Next i

El bucle For-Next en VBA

En términos de programación, un bucle es una instrucción que nos permitirá ejecutar repetidamente un conjunto de instrucciones hasta que se cumpla la condición que hayamos especificado. Los bucles también son conocidos como ciclos.

La instrucción For-Next

203

El bucle For-Next es una de las instrucciones más útiles al programar en VBA. La sintaxis de esta instrucción es la siguiente:

For inicialización de variable To límite

{Conjunto de instrucciones que se repetirán}

Next incrementar variable

Inicialización de variable: Ya que la instrucción For Next repite un conjunto de instrucciones un número de veces específico, debemos inicializar una variable que irá contando cada una de las repeticiones. Es común encontrar la instrucción escrita como For i = 1 lo cual indica que la variable i llevará la cuenta de las repeticiones que deseamos que inicien en 1.

Límite: Además de inicializar la variable que llevará la cuenta de las repeticiones, debemos especificar un límite donde se detendrá el ciclo. Este límite es indicado con la instrucción To. De esta manera, si deseamos hacer un bucle que vaya desde 1 hasta 5 la instrucción la escribiremos como For i = 1 To 5.

Incrementar variable: El final del conjunto de instrucciones se indica con la instrucción Next y que va seguida del nombre de la variable que lleva la cuenta para incrementar su valor en uno. Así podemos terminar el bucle con la instrucción Next i.

Ejemplo de un bucle For-Next

A continuación un ejemplo muy sencillo de un bucle For-Next donde la única instrucción que se repite es la de mostrar una ventana de diálogo con el valor de la variable i:

Con este bucle provocaremos que se muestre una ventana de diálogo 5 veces y en cada una de ellas se mostrará el valor actual de la variable i que comenzará con 1 y terminará con 5. Observa el resultado:

204

Ya que la variable i comienza con el valor 1, el primer cuadro de diálogo muestra el mensaje “i = 1”, después “i = 2” y así sucesivamente hasta llegar al límite.

El bucle For-Next en VBA nos ayudará a crear ciclos que ejecutarán un conjunto de instrucciones hasta alcanzar el límite que hayamos especificado.

Operadores lógicos en VBA

Los operadores lógicos más comunes en VBA son: And y Or. Cada uno de estos operadores es de mucha utilidad para evaluar condiciones y tomar decisiones adecuadas sobre el código que será ejecutado.

El operador lógico And

El operador lógico And es el operador que nos ayuda a forzar el cumplimiento de dos condiciones. Este operador lo traducimos como “Y” de manera que para ejecutar un bloque de código se debe cumplir la condición1 Y la condición2.

En el siguiente ejemplo tengo la calificación de dos exámenes. Solamente si ambos exámenes tienen una calificación mayor a 70, entonces el estudiante será aprobado, de lo contrario la calificación será reprobatoria.

205

El código que se ejecutará al pulsar el botón será el siguiente:

Al ejecutar este código obtendremos el resultado “Aprobado” ya que ambos exámenes tienen una calificación mayor a 70:

De esta manera comprobamos que el operador lógico And nos ayuda a forzar que ambas condiciones se cumplan. En cambio, si el valor de una de las celdas es menor a 70, entonces tendremos un resultado diferente:

206

El operador lógico And devolverá el valor verdadero solamente cuando ambas condiciones se cumplan y será suficiente con que una de ellas no se cumpla para obtener un resultado negativo.

El operador lógico Or

El operador lógico Or lo traducimos como “O” y nos permitirá saber si al menos una de las condiciones se cumple, es decir, si la condición1 O la condición2 se cumplen.

Si cambiamos un poco el ejemplo anterior y decimos que es suficiente que alguna de las dos calificaciones sea mayor a 70 para que el estudiante sea aprobado, entonces podemos modificar el código de la siguiente manera:

Si alguna de las calificaciones es mayor a 70, entonces el estudiante será aprobado:

La única manera en que el operador lógico Or nos devuelva un valor falso es que ninguna de las condiciones se cumpla. En nuestro ejemplo, el alumno estará reprobado solamente cuando ambas calificaciones sean menores a 70:

207

Podemos concluir que al evaluar dos condiciones, los operadores And y Or se comportarán de la siguiente manera:

Eventos en VBA

Los eventos en VBA nos ayudan a monitorear las acciones que realizan los usuarios en Excel de manera que podamos controlar la acción a tomar cuando el usuario hace algo específico como el activar una hoja o hacer clic en alguna celda.

Ejemplos de eventos en VBA

Algunos ejemplos de eventos en VBA son los siguientes:

WorkbookOpen: El usuario abre un libro de Excel.

WorkbookActivate: El usuario activa un libro de Excel.

SelectionChange: El usuario cambia la selección de celdas en una hoja.

Para descubrir los eventos que tiene un objeto es suficiente con abrir el Editor de Visual Basic y posteriormente el Examinador de objetos (F2). En el panel izquierdo se mostrarán los objetos y en el panel derecho las propiedades, métodos y eventos de dicho objeto. Podrás distinguir los eventos porque tienen un icono en forma de rayo (color amarillo):

208

Un ejemplo de eventos en VBA

El ejemplo que crearemos en esta ocasión es para activar un cuadro de diálogo con el mensaje “Bienvenido a la Hoja 2” y que se mostrará cuando activemos la Hoja2 de nuestro libro. Para comenzar, debes seleccionar el objeto Hoja2 del panel izquierdo del Editor de Visual Basic y posteriormente seleccionar la opción Worksheet:

Esto creará automáticamente la subrutina para el evento SelectionChange pero podemos fácilmente crear otro evento seleccionándolo de la lista de Procedimientos. Para este ejemplo crearé el evento Activate:

209

Una vez creada la subrutina para el evento Activate solamente insertaré el código para que se muestre el mensaje dentro del cuadro de diálogo:

Ahora que hemos definido una acción asociada al evento Activate de la Hoja2, el mensaje se mostrará cada vez que actives la Hoja2.

Los eventos en VBA son de mucha utilidad porque nos ayudan a controlar el momento exacto en que deseamos ejecutar algún bloque de código al iniciarse alguna acción por el usuario.

Arreglos en VBA

Los arreglos en VBA pueden ser entendidos como un grupo de variables contenidas dentro de otro repositorio. Dentro de un arreglo podemos referirnos a un valor específico (elemento) utilizando su posición (índice).

Para comprender mejor lo que es un arreglo observa la siguiente imagen:

210

Un arreglo es una colección de “casillas” que contendrán variables individuales. Casa casilla tendrá un número de índice el cual nos permitirá asignar u obtener el valor que contiene.

Crear un arreglo en VBA

Para crear un arreglo en VBA utilizamos la siguiente instrucción:

Dim Paises(1 To 5) As String

Con este código estamos creando el arreglo llamado Paises que tendrá 5 elementos y estamos indicando que cada uno de los elementos será del tipo String, es decir, cadenas de texto. Una vez que ha sido creado el arreglo podemos asignar sus valores de la siguiente manera.

Paises(1) = "Argentina"

Paises(2) = "Colombia"

Paises(3) = "España"

Paises(4) = "México"

Paises(5) = "Perú"

Acceder un elemento del arreglo

Para acceder cualquier elemento del arreglo simplemente colocamos el nombre del arreglo seguido por paréntesis y el número de índice del elemento que necesitamos. Por ejemplo, para desplegar un mensaje con el nombre de país España puedo utilizar la siguiente instrucción:

MsgBox Paises(3)

El resultado de esta instrucción será el siguiente:

211

Fecha y hora en VBA

Los valores de fecha y hora en VBA pueden ser manipulados de diversas maneras. En esta ocasión aprenderemos cómo obtener el año, mes y día en VBA y cómo hacer operaciones básicas con fechas.

Para realizar estos ejemplos debes colocar un control de botón en una hoja de Excel y colocar las líneas de código mostradas.

Obtener la fecha y hora actual

Para obtener la fecha actual en VBA utilizamos la función Date y para obtener la hora actual usamos Now.

En este ejemplo las variables fechaActual y horaActual contienen la fecha y horas actuales respectivamente.

Obtener el año, mes y día

En el ejemplo anterior he obtenido la fecha actual en la variable fechaActual, sin embargo, si deseo mostrar solamente el año puedo utilizar la función Year.

212

El resultado de este código es el siguiente:

Al trabajar con fechas podremos obtener el mes utilizando la función Month y para obtener el día la función Day.

Obtener la hora, minuto y segundo

Para obtener la hora de la variable horaActual utilizaremos la función Hour de la siguiente manera:

El resultado es el siguiente:

213

Para obtener el minuto y el segundo podremos utilizar las funciones Minute y Second.

Convertir una cadena de texto en fecha

Ya hemos visto que la función Date nos devuelve la fecha actual, pero podemos utilizar otra función que nos permitirá convertir una cadena de texto en una fecha. La función que utilizaremos para este será la función DateValue.

El único argumento de la función DateValue es la cadena de texto que convertirá en fecha. El resultado de esta conversión es el siguiente:

Sumar días a una fecha

Para sumar días a una fecha en VBA utilizaremos la función DateAdd. Esta función nos permite especificar la cantidad exacta de días a sumar:

214

Observa el resultado de sumar 5 días a la fecha original:

El primer argumento de la función DateAdd determina la unidad de tiempo que será sumada. En este ejemplo especifiqué “d” para indicar días, pero podemos utilizar otras medidas de tiempo:

“yyyy” para años

“m” para meses

“d” para días

“ww” para semanas

Las fechas y horas son un tipo de dato muy común con el que seguramente tendrás que trabajar al crear tus macros. Es importante que aprendas a utilizar las funciones VBA que nos permitirán manipular adecuadamente la información.

Funciones VBA

El lenguaje de programación VBA contiene un número considerable de funciones que podemos utilizar para construir código en Excel. Cuando estás escribiendo código, puedes introducir la palabra VBA seguida de un punto y verás una lista desplegable de estas funciones.

La siguiente tabla provee una descripción breve de algunas de las funciones VBA más utilizadas.

215

FUNCIÓN DESCRIPCIÓN

Abs Regresa el valor absoluto de un número

Asc Obtiene el valor ASCII del primer carácter de una cadena de texto

CBool Convierte una expresión a su valor booleano

CByte Convierte una expresión al tipo de dato Byte

CCur Convierte una expresión al tipo de dato moneda (Currency)

CDate Convierte una expresión al tipo de dato fecha (Date)

CDbl Convierte una expresión al tipo de dato doble (Double)

CDec Convierte una expresión al tipo de dato decimal (Decimal)

Choose Selecciona un valor de una lista de argumentos

Chr Convierte un valor ANSI en valor de tipo texto

CInt Convierte una expresión en un dato de tipo entero (Int)

CLng Convierte una expresión en un dato de tipo largo (Long)

CreateObject Crea un objeto de tipo OLE

CStr Convierte una expresión en un dato de tipo texto (String)

216

CurDir Regresa la ruta actual

CVar Convierte una expresión en un dato de tipo variante (Var)

Date Regresa la fecha actual del sistema

DateAdd Agrega un intervalo de tiempo a una fecha especificada

DateDiff Obtiene la diferencia entre una fecha y un intervalo de tiempo especificado

DatePart Regresa una parte específica de una fecha

DateSerial Convierte una fecha en un número serial

DateValue Convierte una cadena de texto en una fecha

Day Regresa el día del mes de una fecha

Dir Regresa el nombre de un archivo o directorio que concuerde con un patrón

EOF Regresa verdadero si se ha llegado al final de un archivo

FileDateTime Regresa la fecha y hora de la última modificación de un archivo

FileLen Regresa el número de bytes en un archivo

FormatCurrency Regresa un número como un texto con formato de moneda

FormatPercent Regresa un número como un texto con formato de porcentaje

217

Hour Regresa la hora de un valor de tiempo

IIf Regresa un de dos partes, dependiendo de la evaluación de una expresión

InputBox Muestra un cuadro de diálogo que solicita la entrada del usuario

InStr Regresa la posición de una cadena de texto dentro de otra cadena

InStrRev Regresa la pocisión de una cadena de texto dentro de otra cadena pero empezando desde el final

Int Regresa la parte entera de un número

IsDate Regresa verdadero si la variable es una fecha

IsEmpty Regresa verdadero si la variable está vacía

IsError Regresa verdadero si la expresión es un valor de error

IsNull Regresa verdadero si la expresión es un valor nulo

IsNumeric Regresa verdadero si la variable es un valor numérico

Join Regresa una cadena de texto creada al unir las cadenas contenidas en un arrreglo

LCase Regresa una cadena convertida en minúsculas

Left Regresa un número específico de caracteres a la izquierda de una cadena

218

Len Regresa la longitud de una cadena (en caracteres)

LTrim Remueve los espacios a la izquierda de una cadena

Mid Extrae un número específico de caracteres de una cadena de texto

Minute Regresa el minuto de una dato de tiempo

Month Regresa el mes de una fecha

MsgBox Despliega un cuadro de dialogo con un mensaje especificado

Now Regresa la fecha y hora actual del sistema

Replace Reemplaza una cadena de texto con otra

Space regresa una cadena de texto con el número de espacios especidicados

Split Regresa un arreglo formado for cadenas de texto que formaban una sola cadena

Str Regresa la representación en texto de un número

Right Regresa un número especificado de carecteres a la derecha de una cadena de texto

Rnd Regresa un número aleatorio entre 0 y 1

Round Redondea un número a una cantidad específica de decimales

RTrim Remueve los espacios en blanco a la derecha de una cadena de texto

219

Second Regresa los segundos de un dato de tiempo

StrComp Compara dos cadenas de texto

StrReverse Invierte el orden de los caracteres de una cadena

Time Regresa el tiempo actual del sistema

Timer Regresa el número de segundos desde la media noche

TimeValue Convierte una cadena de texto a un númer de serie de tiempo

Trim Remueve los espacios en blanco al inicio y final de una cadena de texto

TypeName Obtiene el nombre del tipo de dato de una variable

UCase Convierte una cadena de texto en mayúsculas

Val Regresa el número contenido en una cadena de texto

Weekday Regresa un número que representa un día de la semana

WeekdayName Regresa el nombre de un día de la semana

Year Obtiene el año de una fecha

Creando una función VBA

220

Como hemos visto en el artículo Tu primera macro con VBA, una subrutina nos ayuda a organizar y agrupar las instrucciones en nuestro código. El día de hoy te mostraré cómo crear una función VBA, la cual es similar a una subrutina excepto por una cosa.

A diferencia de las subrutinas, las funciones VBA fueron diseñadas para regresar un valor. Así es, a través de una función podemos agrupar código que nos ayudará a hacer algún cálculo específico y obtener un resultado de regreso.

Una función VBA también es conocida como Función Definida por el Usuario, UDF por sus siglas en inglés, y una vez creada puede ser utilizada de la misma manera que las funciones incluidas en Excel como la función SUMAR o la función CONSULTAV. Esto hace que las funciones VBA sean una herramienta muy poderosa.

A continuación mostraré una función que toma un rango y regresa la suma de cada una de sus celdas. Posteriormente iré explicando el detalle de la función.

La palabra clave Function

La primera línea de código comienza con la palabra Function la cual define el inicio de la función. Observa también cómo la última línea de código es End Function que está especificando el término de la función.

Inmediatamente después de la palabra clave Function se debe especificar el nombre de la función que en este ejemplo es MiSuma seguida de paréntesis que de manera opcional pueden contener una lista de parámetros.

Parámetros de una función VBA

Los parámetros son el medio por el cual pasamos información de entrada a la función. Algunas funciones necesitarán de dichas entradas para realizar algún cálculo y algunas otras no, es por ello que los parámetros de una función son opcionales. Puedes incluir tantos parámetros como sean necesarios y solamente debes recordar separarlos por una coma.

Un parámetro no es más que una variable y por lo tanto puedes observar que en el ejemplo he definido la variable rango que será del tipo Range.

Valor de retorno

221

Como mencioné al principio, la característica principal de una función es que puede regresar un valor. Es por eso que al definir una función se debe indicar el tipo del valor de retorno que tendrá dicha función. En este caso el valor de retorno será de tipoDouble y se está especificado por las palabras As Double que aparecen después de los paréntesis.

Cuerpo de la función VBA

Una vez definida la función se pueden especificar todas las instrucciones que serán ejecutas. En el ejemplo he comenzado por definir un par de variables, la variable celda que será del tipo Range y la variable resultado del tipo Double. En ésta última variable es donde se irá acumulando la suma de todas las celdas.

La parte central de la función se encuentra en la instrucción For Each ya que realiza un recorrido por todas las celdas del rango que fue especificado como parámetro. Para cada celda que se encuentra se va sumando su contenido en la variable resultado.

Retornando el valor

Una vez que se han hecho los cálculos necesarios, es importante regresar el valor. Para hacerlo es indispensable igualar el nombre de la función al valor o variable que contiene el valor que se desea regresar. En nuestro ejemplo, la variable resultado es la que contiene la suma de todas las celdas por lo que se iguala con el nombre de la función en la línea MiSuma = resultado.

Probando la función VBA

Finalmente probaré la funciónVBA recién creada dentro de una hoja de Excel. Tal como lo definimos en el código, el único parámetro de la función debe ser un rango del cual me regresará la suma de los valores de la celda. Observa el siguiente ejemplo.

Aunque la función MiSuma hace lo mismo que la función de Excel SUMAR, nos ha servido de ejemplo para introducir el tema de las funciones en VBA. Con este ejemplo tan sencillo hemos creado nuestra primera función VBA.

Controles

Controles de formulario en Excel

Por Moisés Ortíz el 19 diciembre, 2011

222

Los controles de formulario en Excel son objetos que podemos colocar dentro de una hoja y que nos darán funcionalidad adicional para interactuar mejor con los usuarios y tener un mejor control sobre la información.

Podemos utilizar estos controles para ayudar a los usuarios a seleccionar elementos de una lista predefinida o permitir que el usuario inicie una macro con tan solo pulsar un botón. Los controles de formulario en Excel se encuentran dentro de la ficha Programador dentro del grupo Controles. Solamente pulsa el botón Insertar y observarás cada uno de ellos:

Justo por debajo de los controles de formulario podrás observar el grupo de controles ActiveX pero sus diferencias y similitudes las discutiremos en otro artículo. Por ahora nos enfocaremos solamente en los controles de formulario.

¿Cómo insertar un control de formulario en Excel?

Para insertar cualquiera de los controles de formulario debes seleccionarlo del menú desplegable y hacer clic sobre la hoja de Excel arrastrando el borde para “dibujar” el contorno del control. Observa este procedimiento.

Los diferentes controles de formulario

223

Existen diferentes tipos de controles de formulario en Excel que ofrecen diversos tipos de funcionalidad e interacción con el usuario. Desde una simple etiqueta hasta controles que permiten una selección múltiple de sus opciones. A continuación una breve descripción de cada uno de ellos.

Barra de desplazamiento. Al hacer clic en las flechas se va desplazando la barra dentro de un intervalo predefinido.

Botón. El botón nos permite ejecutar una macro al momento de hacer clic sobre él.

Botón de opción. Nos permite una única selección dentro de un conjunto de opciones.

Casilla de verificación. Permite la selección o no selección de una opción.

Control de número. Nos ayuda a aumentar o disminuir un valor numérico.

Cuadro combinado. Es una combinación de un cuadro de texto con un cuadro de lista.

Cuadro de grupo. Agrupa varios controles dentro de un rectángulo.

Cuadro de lista. Muestra una lista de valores de los cuales podemos elegir una sola opción o múltiples opciones de acuerdo a la configuración del control.

Etiqueta. Permite especificar un texto o breves instrucciones en el formulario.

Controles de formulario no disponibles en Excel 2010

Los controles de formulario han estado presentes por varias versiones de Excel, sin embargo existen algunos controles que ya no pueden ser utilizados en Excel 2010 como lo son el Campo de texto, el Cuadro combinado de lista y el Cuadro combinado desplegable. Sin embargo podemos alcanzar funcionalidad similar utilizando controles ActiveX.

Controles ActiveX en Excel

Los controles ActiveX son un tipo de controles que nos permiten agregar funcionalidad de formularios a nuestros libros de Excel. Existe otro tipo de controles que es conocido como Controles de formulario y que tienen una funcionalidad similar, sin embargo existen algunas diferencias entre ambos tipos.

Controles ActiveX y controles de formulario

Los controles de formulario fueron introducidos desde la versión 4 de Excel y por lo tanto han estado presentes en la aplicación por más tiempo que los controles ActiveX los cuales comenzaron a ser utilizados a partir de Excel 97. Ya que los controles ActiveX fueron introducidos posteriormente ofrecen más posibilidades de configuración y formato que los controles de formulario. Ambos tipos de controles se encuentran en la ficha Programador.

224

La diferencia más significativa entre ambos es la manera en como podemos obtener información de los controles al momento de interactuar con el usuario. Los controles de formulario solamente responderán después de que el usuario ha interactuado con ellos, como después de haber pulsado el botón. Por el contrario, los controles ActiveX responden de manera continua a las acciones del usuario lo cual nos permite realizar acciones como cambiar el tipo de puntero del mouse que se muestra al colocar el puntero del ratón sobre el botón.

Propiedades de los controles ActiveX

A diferencia de los controles de formulario, los controles ActiveX tienen una serie de propiedades que podemos configurar pulsando el botón Propiedades que se encuentra dentro del grupo Controles de la ficha Programador.

Antes de poder ver las propiedades de un control ActiveX debemos pulsar el botón Modo Diseño el cual nos permitirá seleccionar el control y posteriormente ver sus propiedades. Cada tipo de control ActiveX mostrará una ventana de Propiedades con sus propias características. A continuación un ejemplo de la ventana Propiedades para un botón de comando ActiveX:

225

Controles ActiveX con subrutinas VBA

Otra diferencia entre los controles de formulario y los controles ActiveX es que los primeros pueden tener asignada una macro y al hacer clic sobre el control de formulario se iniciará la ejecución de dicha macro.

Los controles ActiveX no tienen asignada una macro explícitamente sino que podemos asignar código VBA para cada evento del control. Un evento de un control ActiveX puede ser el evento de hacer clic sobre el control, el evento de hacer doble clic, el evento de obtener el foco sobre el control ActiveX, entre otros eventos más.

Para asignar código a uno de los eventos de un control ActiveX solamente debemos hacer clic derecho sobre él y seleccionar la opción Ver código.

226

Esto mostrará el Editor de Visual Basic con una subrutina para el evento Click() donde podremos escribir

nuestro código.

En la lista desplegable de la derecha podemos observar la lista de eventos disponibles para nuestro control ActiveX y para los cuales podemos escribir código VBA. Al seleccionar cualquiera de dichos eventos se insertará una nueva subrutina que podremos utilizar. Son precisamente la gran cantidad de eventos disponibles para los controles ActiveX lo que los hace controles muy poderosos que podemos utilizar en nuestros formularios.

Otra ventaja de los controles ActiveX

En la versión de Excel 2010 algunos controles de formulario han dejado de ser soportados y no podemos utilizarlos más. Ese es el caso del control de formulario conocido como campo de texto. Sin embargo, dentro de la lista de controles ActiveX tenemos disponibles un control llamado Cuadro de texto lo cual puede hacer atractiva la opción de utilizar controles ActiveX en lugar de controles de formulario.

227

Cuadro de texto en Excel

El cuadro de texto es un control ActiveX que muestra un campo vacío donde el usuario puede introducir cualquier texto. En esta ocasión revisaremos cómo incrustar un cuadro de texto y hacer referencia al mismo desde código VBA.

Insertar un cuadro de texto en Excel

Para insertar un cuadro de texto en una hoja de Excel debes ir a la ficha Programador y hacer clic en el botón Insertar y entonces hacer clic sobre la opción Cuadro de texto de la sección Controles ActiveX.

El puntero del ratón se convertirá en una cruz la cual nos permitirá dibujar el cuadro de texto sobre la hoja de Excel. Una vez dibujado el cuadro de texto podrás hacer clic derecho sobre él y seleccionar la opción Propiedades para conocer el nombre que la ha sido asignado.

También puedes ver las propiedades de cualquier control seleccionándolo primero y pulsando el botón Propiedades que se encuentra dentro del grupo Controles de la ficha Programador.

228

Modificar valor del cuadro de texto

Si deseas colocar un texto dentro del cuadro de texto desde VBA puedes utilizar una instrucción como la siguiente:

TextBox1.Text = "Texto inicial"

Esto hará que el cuadro de texto se vea de la siguiente manera:

Leer valor del cuadro de texto

Si quieres leer el valor del cuadro de texto y colocarlo en una celda podemos utilizar la siguiente instrucción:

Range("A1").Value = TextBox1.Text

El valor de la celda será el mismo que el del cuadro de texto.

Si deseas limpiar el cuadro de texto puedes utilizar la siguiente instrucción:

TextBox1.Text = ""

Cuadro de lista en VBA

El cuadro de lista es un control ActiveX que nos permite desplegar una serie de opciones de las cuales el usuario puede realizar una selección. Podemos configurar el cuadro de lista para permitir seleccionar uno o varios elementos de la lista.

Dibujar un cuadro de lista

229

Para insertar un cuadro de lista en una hoja de Excel debemos ir a la ficha Programador y pulsar el botón Insertar para seleccionar la opción Cuadro de lista (control ActiveX).

Especificar los elementos del cuadro de lista

Una alternativa para indicar los elementos de un cuadro de lista es a través de la propiedad llamada ListFillRange. Para ello debemos abrir las propiedades del control e indicar el rango de celdas que contiene los elementos:

En este ejemplo he especificado que los valores sean tomados del rango A1:A6, lo cual da como resultado un cuadro de lista con los valores especificados en dicho rango:

230

Elementos del cuadro de lista por código

Otra alternativa para indicar los elementos de un cuadro de lista es a través de código VBA. Para ello puedes incluir el siguiente código en el evento Workbook_Open:

De esta manera cuando se abra el libro se agregarán las opciones al cuadro de lista llamado ListBox1.

Asociar una celda al cuadro de lista

Adicionalmente podemos asociar una celda al cuadro de lista la cual mostrará la selección que hagamos. Para hacer esta asociación debemos especificar la dirección de la celda en la propiedad LinkedCell:

231

De esta manera, cada vez que hagamos una selección de alguna de las opciones del cuadro de lista se verá reflejado su valor en la celda asociada:

Cuadro combinado en VBA

Excel tiene un control ActiveX conocido como Cuadro combinado el cual también es llamado comúnmente por su nombre en inglés: Combo Box. Este control nos permite crear listas desplegables en nuestros formularios.

Para insertar un Cuadro combinado debemos ir a la ficha Programador y dentro del botón Insertar pulsar el comando Cuadro combinado (control ActiveX):

232

Después de dibujar el Cuadro combinado tendrás un resultado como el siguiente:

Agregar elementos al cuadro combinado

Si quiero que el Cuadro combinado muestre los valores del rango A1:A5, puedo utilizar la propiedad ListFillRange donde puedo indicar este rango:

233

Como resultado obtendré los valores de las celdas como elementos del Cuadro combinado:

Diferencia con el cuadro de lista

Una diferencia importante entre el Cuadro combinado y el cuadro de lista es que el primero permite que el usuario capture una opción diferente a las mostradas en la lista. En el ejemplo anterior no aparecía el día domingo como parte de los elementos, sin embargo puedo capturar el día domingo dentro del cuadro de lista.

Si quieres evitar que el usuario introduzca sus propios valores existen dos opciones:

Utilizar el control ActiveX Cuadro de lista en lugar del Cuadro combinado

Utilizar el Cuadro combinado pero validar la opción seleccionada con código VBA de manera que nos aseguremos que el usuario ha seleccionado una opción de la lista. Un ejemplo de código de validación es el siguiente:

234

Asociar una celda al cuadro combinado

Al igual que con otros controles ActiveX, podemos asocias una celda al cuadro combinado de manera que muestre el elemento de la lista que haya sido seleccionado. Esta configuración la hacemos en la propiedad LinkedCell:

Cuando selecciones un elemento del cuadro combinado se reflejará dicha selección en la celda indicada en la propiedad LinkedCell:

Casilla de verificación en VBA

Una casilla de verificación es un control ActiveX que podemos utilizar para permitir que un usuario marque una opción y por lo tanto poder conocer sus preferencias al verificar dicho valor en código VBA.

El control ActiveX casilla de verificación

Para insertar este control debemos ir al comando Insertar y seleccionar la opción Casilla de verificación (Control ActiveX).

235

Después de dibujar este control tendrás el siguiente resultado:

Propiedades de una casilla de verificación

La primera propiedad que desearas modificar de una casilla de verificación será su propiedad Caption que es la que almacena el texto desplegado dentro del control ActiveX. Por ejemplo, si deseo que el control muestre el texto “Mayor de edad” debo hacer el cambio de la siguiente manera:

236

La otra propiedad de una casilla de verificación que tal vez quieras modificar será la propiedad Value que de manera predeterminada tendrá el valor False lo cual indica que el control se mostrará desmarcado. Si deseas que el control se muestre marcado de manera predeterminada, debes poner el valor True en la propiedad Value:

Valor de una casilla de verificación

237

Una casilla de verificación nos dirá su ha sido seleccionada o no, es decir, nos devolverá un valor FALSO o VERDADERO. Para leer este valor debemos acceder a la propiedad Value del control de la siguiente manera:

Private Sub CheckBox1_Click()

Range("C4").Value = CheckBox1.Value

End Sub

Esta línea de código coloca el valor de la casilla de verificación en la celda C4. De esta manera, al seleccionar la casilla de verificación obtendré el siguiente resultado:

Para validar en VBA si la casilla de verificación tiene un valor u otro podemos utilizar un código como el siguiente:

If CheckBox1.Value = True Then Range("C4").Value = 1

If CheckBox1.Value = False Then Range("C4").Value = 0

La primera línea valida si el control ActiveX tiene un valor verdadero y de ser así coloca el número 1 en la celda C4. Si la casilla de verificación no ha sido seleccionada (falso) entonces colocará el número cero en la celda C4.

Botón de opción en VBA

Un botón de opción es un control ActiveX que nos permitirá seleccionar una sola opción dentro de un grupo de botones de opción. A diferencia de las casillas de verificación, los botones de opción dependen uno del otro.

El botón de opción en Excel

Para insertar un botón de opción hacemos clic en el comando Insertar de la ficha Programador.

238

Un solo botón de opción no hace mucho sentido, así que siempre agregamos dos o más botones de opción para permitir que usuario haga una selección de cualquiera de ellos.

Una vez que se ha agregado un segundo botón de opción podrás notar que al seleccionar uno de ellos se desmarcarán todos los demás.

La propiedad Caption

De manera predeterminada Excel colocará el botón de opción con un nombre como OptionButton1. Para cambiar este texto debemos editar la propiedad Caption del botón de opción.

239

El botón de opción en VBA

Para saber si un botón de opción ha sido seleccionado podemos acceder a su propiedad Value de la siguiente manera:

Private Sub OptionButton1_Click()If OptionButton1.Value = True Then MsgBox ("Has seleccionado la

opción Hombre")End Sub

La propiedad Value es la que nos indica si el control está seleccionado, en cuyo caso, la propiedad será igual a True. Para este ejemplo, al validar que el botón de opción está seleccionado, se mostrará un cuadro de diálogo con un mensaje sobre la opción seleccionada.

Formularios en VBA

Los formularios en VBA no son más que un cuadro de diálogo de Excel donde podremos colocar controles que nos ayudarán a solicitar información del usuario. Podremos colocar cajas de texto, etiquetas, cuadros combinados, botones de comando, etc.

Crear un formulario en Excel

Los formularios de Excel son creados desde el Editor de Visual Basic donde debemos seleccionar la opción de menú Insertar y posteriormente la opción UserForm.

240

Inmediatamente se mostrar un formulario en blanco y de igual manera podrás observar el Cuadro de herramientas:

Si no ves el Cuadro de herramientas puedes seleccionar el menú Ver y la opción Cuadro de herramientas.

Agregar controles al formulario

241

Para agregar un control al formulario debes seleccionarlo del Cuadro de herramientas y dibujarlo sobre el formulario. En mi formulario he agregado etiquetas y cuadros de texto así como un par de botones de comando:

El texto de las etiquetas se modifica en la propiedad llamada Caption. Para realizar este cambio solamente selecciona el control y se mostrará la ventana de Propiedades donde podrás hacer la modificación. De igual manera el texto desplegado en los botones de comando se modifica en su propiedad Caption.

Código para el botón Cancelar

El botón cancelar cerrará el formulario sin guardar la información capturada en ningún lugar. El código que debemos utilizar es el siguiente:

Private Sub CommandButton2_Click()

Unload Me

End Sub

Para agregar este código puedes hacer doble clic sobre el control. La sentencia “Unload Me” cerrará el formulario.

Código para el botón Aceptar

A diferencia del botón Cancelar, el botón Aceptar colocará los datos de las cajas de texto en las celdas A1, B1 y C1. El código utilizado es el siguiente:

Private Sub CommandButton1_Click()

Worksheets("Hoja1").Range("A1").Value = Me.TextBox1.Value

Worksheets("Hoja1").Range("B1").Value = Me.TextBox2.Value

Worksheets("Hoja1").Range("C1").Value = Me.TextBox3.Value

End Sub

242

Al pulsar el botón Aceptar se transferirán los valores de los controles TextBox hacia las celdas de la Hoja1.

Botón para abrir formulario

Para facilitar la apertura del formulario puedes colocar un botón ActiveX en la hoja con el siguiente código:

Private Sub CommandButton1_Click()

UserForm1.Show

End Sub

Probar el formulario

Observa cómo cada uno de los botones realiza la acción correcta al pulsarlos: