FUNCIONES DE EXCEL by Stefy Garces

CARRERA DE DOCENCIA EN INFORMATICA Stefy

TABLA DE CONTENIDOS TABLA DE CONTENIDOS ..........................................................................................................................2 INTRODUCCIÓN.....................................................................................................................................5 DESARROLLO ........................................................................................................................................6 FORMATO CONDICIONAL ................................................................................................................6 FUNDAMENTOS DE FORMATO CONDICIONAL ............................................................................................6 Razón para utilizar el formato condicional ........................................................................................ 6 Ejemplos de formato condicional ...................................................................................................... 6 ¿Cómo se aplica el formato condicional? .......................................................................................... 6 RESALTAR CELDAS CON FORMATO CONDICIONAL ........................................................................................7 Reglas de formato condicional ............................................................................................................ 8 APLICAR FORMATO CONDICIONAL A VALORES DUPLICADOS O VALORES ÚNICOS..............................................10 Formato condicional a valores duplicados ........................................................................................ 10 Formato condicional a valores únicos ............................................................................................... 12 REGLAS SUPERIORES E INFERIORES PARA ANALIZAR DATOS..........................................................................14 Reglas superiores e inferiores de formato condicional..................................................................... 14 BUSCAR VALORES USANDO FORMATO CONDICIONAL .................................................................................16 Buscar en Excel con formato condicional.......................................................................................... 16 Fórmula para encontrar valores ........................................................................................................ 17 BORRAR REGLAS DE FORMATO CONDICIONAL ...........................................................................................19 Eliminar formato condicional de celdas seleccionadas ..................................................................... 19 Eliminar formato condicional de todas las celdas ............................................................................. 20 Eliminar algunas reglas de formato condicional ............................................................................... 20 FUNCIÓN SI .....................................................................................................................................21 Sintaxis de la función SI .................................................................................................................... 21 Ejemplos de la función SI .................................................................................................................. 22 Utilizar una función como prueba lógica ......................................................................................... 22

........................................................................................................................................................26 FUNCIONAMIENTO DE LAS TABLAS DINÁMICAS .........................................................................................26 CÓMO CREAR UNA TABLA DINÁMICA ......................................................................................................29 Crear una tabla dinámica en Excel 2010 ........................................................................................... 29 PARTES DE UNA TABLA DINÁMICA ..........................................................................................................33 Partes de una tabla dinámica en Excel .............................................................................................. 33 DAR FORMATO A UNA TABLA DINÁMICA ..................................................................................................34 FORMATO DE VALORES EN UNA TABLA DINÁMICA .....................................................................................36 FILTRAR UNA TABLA DINÁMICA ..............................................................................................................38

FUNCIONES DE EXCEL.

Utilizar una función como valor de regreso ..................................................................................... 23

SEGMENTACIÓN DE DATOS EN TABLAS DINÁMICAS ....................................................................................39 MODIFICAR CAMPOS DE UNA TABLA DINÁMICA ........................................................................................41 Lista de campos de una tabla dinámica ............................................................................................ 42 Quitar un campo de una tabla dinámica ........................................................................................... 42 Mover un campo de una tabla dinámica........................................................................................... 43 Nuevo campo en una tabla dinámica ................................................................................................ 43 CREAR UN GRÁFICO DINÁMICO ..............................................................................................................44 Cómo crear un gráfico dinámico en Excel ......................................................................................... 44 MOVER UN GRÁFICO DINÁMICO ............................................................................................................46 Mover un gráfico dinámico a otra hoja ............................................................................................. 46 Modificar el estilo de un gráfico dinámico ........................................................................................ 47 CAMBIAR ORIGEN DE DATOS DE UNA TABLA DINÁMICA ..............................................................................47 LA FUNCIÓN BUSCARV EN EXCEL ....................................................................................................48 Crear una tabla de búsqueda ............................................................................................................ 48 Sintaxis de la función BUSCARV ........................................................................................................ 49 Ejemplo de la función BUSCARV........................................................................................................ 50 Errores comunes al usar la función BUSCARV ................................................................................... 51 ¿QUÉ ES UNA MACRO DE EXCEL?...........................................................................................................52 Un lenguaje de programación ........................................................................................................... 52 PARA QUÉ SIRVE UNA MACRO EN EXCEL ..................................................................................................53 Automatización de tareas ................................................................................................................. 53 ¿Cómo se ve una macro en Excel? .................................................................................................... 53 Creación de una macro ..................................................................................................................... 54 MOSTRAR LA FICHA PROGRAMADOR .................................................................................................. ….54 Mostrar la ficha Programador en Excel 2010 .................................................................................... 54 Grupos de la ficha Programador ....................................................................................................... 56 CREAR UNA MACRO .............................................................................................................................56 PROGRAMANDO EN VBA .....................................................................................................................58 Principios fundamentales .................................................................................................................. 58 Propiedades y Métodos .................................................................................................................... 59

La función MsgBox en VBA ................................................................................................................ 60 Ejecutar macro .................................................................................................................................. 60 El objeto Workbook y el objeto Worksheet ...................................................................................... 61 Abrir un libro de Excel ....................................................................................................................... 61 Hacer referencia a un libro en VBA ................................................................................................... 61 Acceder las hojas de un libro............................................................................................................. 62

FUNCIONES DE EXCEL.

Subrutinas en VBA ............................................................................................................................. 60

Agregar una nueva hoja .................................................................................................................... 62 EL OBJETO APPLICATION EN VBA..........................................................................................................62 Colecciones del objeto Application .................................................................................................. 63 Propiedades del objeto Application................................................................................................. 63 Métodos del objeto Application ...................................................................................................... 64 DEPURAR MACROS EN EXCEL ......................................................................................................64 Depurar código VBA ......................................................................................................................... 64

FUNCIONES DE EXCEL.

BIBLIOGRAFÍA .....................................................................................................................................66

INTRODUCCIÓN Trabajar con hojas de cálculo resulta a menudo desalentador. Los usuarios principiantes suelen tener dificultades para encontrar las características que necesitan para llevar a cabo una tarea. Los avanzados verán a menudo que algunas funciones adicionales que les gustaría poder utilizar no existen actualmente. Uno de los principales objetivos de diseño de es ofrecer a ambos tipos de usuarios (y a los que se encuentran entre un grupo y otro) las herramientas necesarias para lograr una mayor productividad. Excel le ofrece ahora a los usuarios principiantes e intermedios más confianza para trabajar con hojas de cálculo, ya que facilita el conocimiento de las funciones existentes a la vez que proporciona una mayor inteligencia para garantizar a los usuarios que no están cometiendo errores. En el caso de usuarios avanzados, Excel se centra en hacer que la ejecución de las tareas habituales resulte más sencilla e intuitiva.

FUNCIONES DE EXCEL.

Trabaje de manera más inteligente con Excel, el programa de cálculo y análisis de Office. Proporciona a los profesionales las herramientas que necesitan para administrar información de negocios y para los usuarios en general les ayuda a aprovechar al máximo la información

DESARROLLO FORMATO CONDICIONAL 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:

¿Cómo se aplica el formato condicional? Debes seleccionar 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.

FUNCIONES DE EXCEL.

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

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.

Resaltar celdas con formato condicional

FUNCIONES DE EXCEL.

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.

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:

FUNCIONES DE EXCEL.

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:

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:

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

FUNCIONES DE EXCEL.

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

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. Formato condicional a valores duplicados

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.

FUNCIONES DE EXCEL.

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

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

FUNCIONES DE EXCEL.

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:

Formato condicional a valores únicos

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

FUNCIONES DE EXCEL.

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.

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.

FUNCIONES DE EXCEL.

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 único dentro de la lista desplegable.

Reglas superiores e inferiores para analizar datos

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:

FUNCIONES DE EXCEL.

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.

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, solo 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 disminuar 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.

FUNCIONES DE EXCEL.

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 nigún argumento, el cuadro de diálogo de esta regla solamente solicta 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. Buscar valores usando formato condicional Seguramente utilizas las hojas de Excel para almacenar grandes cantidades de datos, como el catálogo de clientes de la empresa, el registro de las ventas de productos, etc. Con esa gran cantidad de información es indispensable encontrar los datos precisos en todo momento. Buscar en Excel con formato condicional

Para este ejemplo utilizaré los datos de la columna B y utilizaré la celda B2 como el cuadro de búsqueda, es decir, la celda donde colocaré el término/texto que deseo entontrar en los datos. Ahora debemos crear la regla que aplique el formato condicional y para eso se deben seleccionar las celdas con los datos e ir al comando Formato condicional y seleccionar la opción Nueva regla:

FUNCIONES DE EXCEL.

En esta ocasión te mostraré cómo utilizar el formato condicional para buscar dentro de una hoja de Excel y resaltar las celdas coincidentes. Observa la siguiente imagen:

Fórmula para encontrar valores Selecciona la opción Utilice una fórmula que determine las celdas para aplicar formato. En la caja de texto coloca la siguiente fórmula, que explicaré en un instante: =Y($B$2<>"",ENCONTRAR(MINUSC($B$2),

MINUSC(B4)))

FUNCIONES DE EXCEL.

Se mostrará el cuadro de diálogo Nueva regla de formato:

También haz clic en el botón Formato… para seleccionar el formato que se aplicará a las celdas que cumplan la regla.

FUNCIONES DE EXCEL.

La fórmula que acabas de escribir compara el texto de la celda de búsqueda ($B$2) con las celdas que contienen los datos. La comparación se hace a través de la función ENCONTRAR que compara ambos valores. Puedes observar también que para ambos valores utilicé la función MINUSC que los convierte en minúsculas antes de hacer la comparación de manera que la búsqueda no sea sensible a mayúsculas y minúsculas. Finalmente, la intención de la función Y es evitar que el formato se aplique a todas las celdas cuando la celda $B$2 esté vacía.

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

FUNCIONES DE EXCEL.

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. Eliminar algunas reglas de formato condicional

FUNCIONES DE EXCEL.

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. FUNCIÓN SI 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.

FUNCIONES DE EXCEL.



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: =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")

FUNCIONES DE EXCEL.

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

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")

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.

FUNCIONES DE EXCEL.

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:

Observaciones Es posible anidar hasta 64 funciones SI como argumentos valor_si_verdadero y valor_si_falso para crear pruebas más complicadas. (Vea el ejemplo 3 para ver una muestra de funciones SI anidadas). Como alternativa, para comprobar muchas condiciones, plantéese usar las funciones BUSCAR, BUSCARV o BUSCARH. (Vea el ejemplo 4 para obtener una muestra de la función BUSCARH.) Cuando los argumentos valor_si_verdadero y valor_si_falso se evalúan, la función SI devuelve el valor devuelto por la ejecución de las instrucciones. Si uno de los argumentos de la función SI es una matriz, cada elemento de la matriz se evaluará cuando se ejecute la instrucción SI. Microsoft Excel proporciona funciones adicionales que se pueden utilizar para analizar los datos en función de una condición. Por ejemplo, para contar el número de veces que una cadena de texto o un número aparecen dentro de un rango de celdas, utilice las funciones de hoja de cálculo CONTAR.SI y CONTAR.SI.CONJUNTO. Para calcular una suma basándose en una cadena de texto o un número de un rango,

FUNCIONES DE EXCEL.

utilice las funcionesSUMAR.SI y SUMAR.SI.CONJUNTO.

TABLAS DINÁMICAS. ¿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.

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.

FUNCIONES DE EXCEL.

El verdadero problema de las tablas dinámicas

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.

FUNCIONES DE EXCEL.

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.

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:

FUNCIONES DE EXCEL.

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:

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.

FUNCIONES DE EXCEL.

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

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.

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.

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

FUNCIONES DE EXCEL.

Crear una tabla dinámica en Excel 2010

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

FUNCIONES DE EXCEL.

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.

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

FUNCIONES DE EXCEL.

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.

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.

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.

FUNCIONES DE EXCEL.

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:

Partes de una tabla dinámica Una vez que has aprendido cómo crear una tabla dinámica en Excel es conveniente detenerse un momento para conocer las partes que la componen y comprender el funcionamiento de cada una de ellas.

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. 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.

FUNCIONES DE EXCEL.

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 ficha Diseño es una ficha contextual, por lo que deberás seleccionar la tabla dinámica para que se muestre.



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 bandas.

FUNCIONES DE EXCEL.

Esta ficha está dividida en tres grupos.



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.

FUNCIONES DE EXCEL.

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.

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:

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

FUNCIONES DE EXCEL.

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.

FUNCIONES DE EXCEL.

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:

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.

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

FUNCIONES DE EXCEL.

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.

Para mostrar de nuevo todos los valores de los campos filtrados debes hacer clic en el botón de filtrado y seleccionar la opción Borrar 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.

FUNCIONES DE EXCEL.

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.

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:

FUNCIONES DE EXCEL.

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

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.

Modificar campos de una tabla dinámica 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.

FUNCIONES DE EXCEL.

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.

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.

Quitar un campo de una tabla dinámica 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.

FUNCIONES DE EXCEL.

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

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.

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.

FUNCIONES DE EXCEL.

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

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. Crear un gráfico dinámico 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 comprensió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.

FUNCIONES DE EXCEL.

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

FUNCIONES DE EXCEL.

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:

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. 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.

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.

FUNCIONES DE EXCEL.

Mover un gráfico dinámico a otra hoja

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:

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.

FUNCIONES DE EXCEL.

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.

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.

LA FUNCIÓN BUSCARV EN EXCEL 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.

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.

FUNCIONES DE EXCEL.

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.

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

FUNCIONES DE EXCEL.

La función BUSCARV tiene 4 argumentos:



 



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_buscadonos 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, 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,

=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:

FUNCIONES DE EXCEL.

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 (,):

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.



  

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.

FUNCIONES DE EXCEL.

Errores comunes al usar la función BUSCARV

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. 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

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.

FUNCIONES DE EXCEL.

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.

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.

¿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.

FUNCIONES DE EXCEL.

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.

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. 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.

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.

FUNCIONES DE EXCEL.

Mostrar la ficha Programador en Excel 2010

FUNCIONES DE EXCEL.

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.

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. 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.

FUNCIONES DE EXCEL.

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.

FUNCIONES DE EXCEL.

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.

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.

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. 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.

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

FUNCIONES DE EXCEL.

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.

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. 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).

FUNCIONES DE EXCEL.

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.

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. 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.

FUNCIONES DE EXCEL.

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. 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 una colecció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: 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:

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

FUNCIONES DE EXCEL.

Application.Workbooks(1).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 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

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.

FUNCIONES DE EXCEL.

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 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

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.

FUNCIONES DE EXCEL.

El objeto Application tiene muchas propiedades como para mencionarles todas en esta ocasión, pero algunas de las más importantes son las siguientes:

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

impresiones:",

Title:="Imprimir", Default:=1, Type:=1) Esta instrucción hará que Excel muestre un cuadro de diálogo pidiendo al usuario que ingrese el número de impresiones que desea realizar. El número ingresado por el usuario se guardará en la variable Impresiones.

Depurar macros en Excel

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.

FUNCIONES DE 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:

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.

FUNCIONES DE EXCEL.

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:

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.

BIBLIOGRAFÍA http://office.microsoft.com/es-mx/help/funcion-si-HP010069829.aspx http://exceltotal.com/cambiar-origen-de-datos-de-una-tabla-dinamica/ http://exceltotal.com/la-funcion-buscarv-en-excel/ http://www.monografias.com/trabajos10/el_prog/el_prog.shtml#ixzz3LFHbJyZr http://exceltotal.com/formato-condicional-en-excel/

FUNCIONES DE EXCEL.

http://exceltotal.com/la-funcion-si-en-excel/