Calcular una fecha futura en ExcelEn ocasiones necesitamos calcular la fecha que corresponde a cierta cantidad de días posteriores a la fecha actual, por ejemplo, ¿qué fecha será dentro de 10 días? Afortunadamente este tipo de cálculo es muy fácil de realizar en Excel.

Determinar una fecha final

Si queremos conocer la fecha de conclusión de una tarea y sabemos su fecha de inicio y su duración, entonces será tan simple como hacer una suma en Excel, por ejemplo:

=HOY() + 10

La función HOY nos devolverá la fecha actual y al sumar el valor 10 obtendremos la fecha que corresponde a diez días posteriores al día de hoy. Este cálculo funciona para cualquier fecha y lo puedes observar en el siguiente ejemplo donde hago la misma operación pero con diferentes fechas de inicio y duración:

¿Cómo es que Excel puede calcular una fecha futura con una simple suma? Para poder comprender mejor este comportamiento de la herramienta debemos conocer la manera en que Excel trabaja con fechas.

Las fechas en Excel son números

Los cálculos de fechas son muy sencillos de realizar porque las fechas en Excel son en realidad números enteros. Si tienes tiempo utilizando Excel sabrás que todas las celdas tienen un valor, y que dicho valor puede desplegarse en pantalla de una manera diferente lo cual sucede con las fechas: su valor es numérico pero se despliega en pantalla con un formato que estamos acostumbrados a reconocer como fecha. Por ejemplo, si en una celda de Excel colocas el valor 1 y posteriormente le das el formato de fecha corta, observarás que Excel cambia el valor desplegado de la celda por la fecha 01/01/1900.

De esta manera sabemos que la fecha 01/01/1900 tiene asignado el número 1 y por lo tanto cualquier otra fecha será un número entero que representará la cantidad de días que han transcurrido a partir del 01/01/1900. En la siguiente imagen he agregado una columna donde he copiado las fechas del ejemplo anterior y he cambiado su formato a General para ver el número entero que le corresponde a cada fecha. Posteriormente, he abierto el cuadro de diálogo Formato de celdas, donde al seleccionar la categoría General se muestra el número entero correspondiente a la celda D2:

Observa que la fecha final tiene asignado el número 41285 que es precisamente la suma de 41275 + 10. Es por esta razón que los cálculos con fechas son muy fáciles de realizar para Excel ya que realmente estará trabajando con números enteros que podrá sumar o restar fácil y rápidamente.

Ejemplo: Calcular la fecha de parto

Como hemos aprendido hasta ahora, si sumamos un valor entero a una fecha estaremos sumando cierta cantidad de días. Ahora hagamos un ejemplo con mayor grado de complejidad y que es calcular la fecha de parto de un bebé a partir de una fecha determinada.

Para realizar este cálculo utilizaré la Regla de Naegle que indica que la fecha probable de parto se obtiene de la siguiente manera: A partir de la fecha de la última regla de la mujer sumar un año, restar tres meses y finalmente sumar 7 días. Hagamos este cálculo paso por paso.

Para sumar un año a la fecha inicial podría sumar el valor 365, pero haré uso de las funciones FECHA, AÑO, MES y DIA para comprobar su funcionamiento. Así que considera la siguiente fórmula:

=FECHA(AÑO(A2)+1, MES(A2), DIA(A2))

La función FECHA genera una fecha válida en Excel a partir de los valores numéricos del año, mes y día. En este caso obtendré el año de la fecha original que se encuentra en la celda A2 y le sumaré el valor 1 con lo cual obtendré de inmediato el valor del año siguiente. Posteriormente indicaré tanto el mes como el día de la fecha original. Observa el resultado de esta fórmula:

El siguiente paso para calcular la fecha de parto indica que debemos restar tres meses al resultado anterior y para eso utilizaré de nueva cuenta la función FECHA de la siguiente manera:

=FECHA(AÑO(B2), MES(B2)-3, DIA(B2))

Observa que en esta ocasión será el segundo argumento de la función FECHA el que sufra una modificación para indicar que deseo restar tres meses a la fecha original. Como resultado obtendremos la fecha deseada:

Como último paso, el procedimiento de cálculo nos indica que debemos sumar 7 días a la fecha anterior por lo que será necesario hacer dicha suma en el tercer argumento de la función FECHA tal como lo puedes observar en la siguiente imagen:

De esta manera hemos calculado una fecha futura a partir de una fecha inicial conociendo la cantidad de años, meses y días a sumar o restar. Y como seguramente lo estarás deduciendo, todo el cálculo anterior se puede hacer con una sola fórmula de la siguiente manera:

=FECHA(AÑO(A2)+1, MES(A2)-3, DIA(A2)+7)

Calcular una fecha futura en días laborables

Los ejemplos que hemos desarrollado hasta ahora hacen los cálculos con días naturales es decir, tomando en cuenta todos los días del calendario. Sin embargo, es muy común vernos en la necesidad de calcular una fecha futura pero considerando los días de descanso semanal.

Por ejemplo, supongamos que para los datos de nuestro primer ejemplo necesitamos sumar la duración en días laborables a partir de la fecha de inicio, es decir, sin contabilizar los sábados y los domingos. Para lograr este tipo de cálculo podemos utilizar la función DIA.LAB de la siguiente manera:

Debes tener cuidado de no confundir la función DIA.LAB con la función DIAS.LAB ya que la diferencia es solamente una letra. La función DIA.LAB calculará una fecha a partir de un número especificado de días laborables. También es importante recordar que esta función siempre considerará el fin de semana como los días sábado y domingo y no hay nada que podamos hacer para modificar dicho comportamiento. Pero si necesitas usar días de descanso diferentes, entonces debes utilizar la función DIA.LAB.INTL que en su tercer argumento nos permitirá elegir de un listado de opciones para los días de descanso:

Si elijo el número 17 para nuestros datos de ejemplo, se calculará la fecha final considerando el día sábado como el único día de descanso semanal y por lo tanto obtendremos el siguiente resultado:

Es así como al utilizar ambas funciones, DIA.LAB y DIA.LAB.INTL, podemos calcular una fecha futura en Excel considerando los días de descanso semanal. La primera función tiene establecidos los días de descanso como sábado y domingo mientras que la función DIA.LAB.INTL nos permitirá elegir los días de descanso que mejor convengan para nuestros cálculos.

Determinar una fecha de vencimiento en ExcelTengo una columna de datos con fechas de inicio de contratos y una segunda columna con la duración de cada uno de ellos. Necesito una tercera columna que calcule la fecha de vencimiento del contrato.

Duración en días

Si la columna de duración está en días, entonces el cálculo es muy sencillo porque solamente necesito sumar ambas columnas para obtener la fecha de vencimiento.

Duración en meses

Por el contrario, una duración en meses nos obliga a utilizar algunas funciones para obtener el resultado correcto.  La primera función que utilizaremos será la función FECHA, la cual nos ayuda a obtener una fecha en base a un número de año, mes y día. Considera la siguiente fórmula:

=FECHA(2011, 12, 14)

Con esta fórmula obtendremos una fecha para el 14 de diciembre del 2011. Esta función será clave para calcular la fecha de vencimiento de nuestro ejemplo porque es precisamente el segundo parámetro de la función (los meses) donde haremos la suma de la duración del contrato para obtener la fecha de vencimiento correcta. Iremos paso por paso.

El primer parámetro de la función FECHA es el año por lo que utilizaré la función AÑO para extraer el año de la fecha de inicio de la siguiente manera:

El segundo paso en este procedimiento es extraer el mes de la fecha inicio utilizando la función MES y además sumarle el número de meses de la duración. Esto hará que Excel sume los meses necesarios que necesitamos para llegar a la fecha de vencimiento.

El paso final será extraer los días de la fecha de inicio con la función DIA.

Excel hará los cálculos adecuados por nosotros y nos devolverá la fecha de vencimiento al transcurrir el número de meses especificado.

Es importante notar que la función FECHA ajusta automáticamente el año en caso de que sea necesario. En nuestro ejemplo, al sumar 2 meses a la fecha 30 de diciembre del 2011 forzosamente tendría que dar como resultado una fecha en el año 2012 y Excel hace el cálculo correctamente.

Duración en años

En caso de que tuviera la duración del contrato en años, entonces debo hacer un procedimiento similar al anterior solo que la suma de la duración se debe incluir en el primer argumento de la función FECHA que es el parámetro para el año.

Calcular el tiempo transcurridoCalcular la diferencia entre dos fechas en Excel es algo muy sencillo. En este artículo mostraré cómo calcular ese tiempo transcurrido entre ambas fechar y presentar el resultado en diferentes unidades de tiempo como años, meses, semanas, etc.

Supongamos que tenemos la fecha de nacimiento de una persona y queremos calcular el tiempo que ha vivido hasta el día de hoy. Observa la siguiente imagen:

Tiempo transcurrido en días

Puede observar que la línea que se encuentra sombreada es la de los días transcurridos y la razón principal es porque esa es la operación más sencilla de todas. Para obtener la cantidad de días entre dos fechas es suficiente con realizar una resta entre ambas. En este ejemplo la fórmula utilizada es: =B3-B2

Excel regresar el tiempo transcurrido en días utilizando decimales y por motivos de presentación se los he removido, sin embargo tú puedes dejar el formato con decimales si así lo deseas.

Calcular las horas, minutos y segundos

El siguiente paso natural es calcular las horas, lo cual es sumamente sencillo una vez que tengo el número de días. Solamente debo multiplicar el resultado en días por 24: =A8 * 24

Y una vez que tengo las horas puedo obtener los minutos haciendo una multiplicación por 60 que es la cantidad de minutos en una hora: =A9 * 60

Finalmente, y como has de suponer, para obtener los segundos multiplico el resultado anterior de nuevo por 60: =A10 * 60 y así obtengo los segundos transcurridos entre ambas fechas.

Tiempo en semanas

Como puedes observar el cálculo de días es la parte central para el resto de cálculos y las semanas no son la excepción ya que las podemos obtener fácilmente realizando una simple división: =A8 / 7

Días laborables

El cálculo de días laborales entre dos fechas ya no lo podemos realizar con una simple operación aritmética, pero afortunadamente existe la función DIAS.LAB la cual realiza el cálculo por nosotros, solamente es necesario especificar ambas fechas como los argumentos de la función y Excel nos devolverá el resultado correcto: =DIAS.LAB(B2, B3)

Meses y años

Obtener los meses es una tarea un tanto más complicada, porque los meses no tienen la misma cantidad de días. Lo mismo sucede con los años ya que tenemos los años bisiestos los cuales tienen un día adicional.

Sin embargo Excel provee de una función que nos ayuda con este cálculo, pero debo advertirte que ésta no es una función tradicional, de hecho no la podrás encontrar en la documentación de Excel y sin embargo la podremos utilizar. La función es conocida como la función SIFECHA y aunque en este artículo no entraré en los detalles de ésta función observa cómo me ayuda a obtener los meses transcurridos entre dos fechas:

De una manera similar, esta función obtiene los años transcurridos:

Espera el siguiente artículo donde hablaré con mucho más detalle sobre la función SIFECHA la cual ha permanecido oculta por mucho tiempo y aún sigue presente en Excel 2010.

Fechas en ExcelExcel almacena una fecha como si fuera un número entero. Al utilizar un sistema como éste, Excel puede sumar, restar y comparar fácilmente las fechas sin necesidad de hacer alguna conversión ni cálculo especial.

Por ejemplo, la fecha 1/1/2000 está representada por el número de serie 36526 y la fecha 05/10/2000 tiene asignado el valor 36804. Como puedes imaginar, al realizar una operación con fechas se utiliza su número de serie para facilitar las operaciones.

El primero y último día de Excel

Excel reconoce todas las fechas posteriores a 1/1/1900. Es por eso que el primer día del año 1900 tiene asignado el número 1. Por el contrario, el último día que Excel reconoce es el 31/12/9999 el cual tiene asignado el número 2958465.

Valor numérico de una fecha

Es realmente sencillo conocer el valor numérico de una fecha, solamente cambia el formato de la celda que contiene una fecha a un formato General y Excel desplegará su valor numérico.

La función FECHA

Finalmente mencionaré la función FECHA la cual nos ayuda a obtener el número de serie que le corresponde a una fecha dados el día, el mes y el año.

Esta función es de gran utilidad para convertir datos a un formato fecha en caso de que tengas el día, mes y año en columnas separadas. Una vez convertidos al tipo de dato adecuado se podrán hacer operaciones con las fechas sin dificultad alguna.

Días laborables con ExcelLa función DIAS.LAB nos ayuda a obtener el total de días laborables entre dos fechas, sin embargo tiene un inconveniente y es que asume que los días de fin de semana son el sábado y el domingo. ¿Qué hacer si queremos considerar días de fin de semana diferentes?

La función DIAS.LAB

Primero comprobaré lo que he dicho. Así que utilizaré la función DIAS.LAB para calcular los días laborables del mes de enero del 2011. Observa el siguiente ejemplo.

Efectivamente existen 21 días laborables en el mes de enero considerando los sábados y domingos como fin de semana. Pero si quisiera tomar como días de descanso el viernes y el sábado el número de días laborables del mes sería diferente. Para obtener el resultado deseado utilizaré la función DIAS.LAB.INTL.

La función de Excel DIAS.LAB.INTL

La función DIAS.LAB.INTL permite especificar los días del fin de semana que deseo utilizar. Observa cómo al introducir la fórmula Excel me permite seleccionar el fin de semana adecuado:

Al elegir los días viernes y sábado como el fin de semana a considerar, el resultado de días laborables para el mes de enero del 2011 cambia a 22 días.

Varios países del medio oriente tienen un fin de semana establecido en los días viernes y sábado, lo cual ha sido una de las razones por las que se ha introducido la función DIAS.LAB.INTL en Excel.

Aunque es poco probable que estés leyendo este artículo desde alguno de estos países, es más probable que alguna vez tengas algún trato comercial con alguno de ellos o simplemente la compañía donde laboras desea hacer un cálculo de días laborables utilizando algún día de la semana en especial.

Fórmula para convertir grados decimales a grados, minutos y segundosHablando de trigonometría y los sistemas de medición de ángulos nos encontramos con que existen dos sistemas de medición muy comunes: el sistema decimal y el sistema sexagesimal. En esta ocasión crearemos una fórmula en Excel para convertir del sistema decimal al sexagesimal.

El sistema sexagesimal

El sistema sexagesimal es un sistema que divide cada unidad en 60 unidades de orden inferior, es decir, es un sistema en base 60. Este sistema lo podemos ver reflejado en la medida del tiempo, pero también es ampliamente utilizado para medir la amplitud de los ángulos donde las unidades de medición son los grados, minutos y segundos.

Hoy en día existen aplicaciones de computadora, como AutoCAD, que hacen mediciones de ángulos y que nos permiten elegir un sistema de medición, ya sea el sistema de grados decimales o el sistema de grados, minutos y segundos (sexagesimal). Por esta razón se hace conveniente encontrar un método eficaz para convertir los valores entre ambos sistemas.

Algoritmo de conversión

Antes de presentar la fórmula de Excel que realiza esta conversión, es importante mostrar los pasos para convertir un valor del sistema decimal al sistema sexagesimal y así podremos comprobar que la fórmula propuesta funcionará correctamente. Como ejemplo convertiremos el valor decimal 121.135° al sistema sexagesimal de la siguiente manera:

1. La parte entera de los grados decimales será también los grados del sistema sexagesimal (121°).

2. Multiplicar los decimales del valor original por 60 (.135 * 60 = 8.1).3. La parte entera del resultado anterior serán los minutos (8’).4. Multiplicar los decimales del paso 2 por 60 (.1 * 60 = 6).5. El resultado del paso anterior serán los segundos (6’’).6. Tomar los resultados de los pasos 1, 3 y 5 para formar el resultado final (121°8’6’’).

Siguiendo estos mismos pasos podremos convertir grados decimales a grados, minutos y segundos sin mayor problema, así que basados en este algoritmo de conversión puedes comprobar que los siguientes resultados son correctos:

10.46° = 10°27’36’’

36.30° = 36°18’0’’ 132.39° = 132°23’24’’

Fórmula en Excel para convertir grados decimales

Aunque nuestro impulso inicial sería implementar una fórmula en Excel que haga la conversión paso por paso tal como lo describe el algoritmo de la sección anterior, la verdad es que en Excel existe un método mucho más sencillo.

La solución se basa en el hecho de que las horas ya tienen un sistema de medición sexagesimal y Excel opera perfectamente con dicho sistema. Por si no lo sabes, las fechas en Excel son números enteros entre 1 y 2958465, además el tiempo en Excel también es representado por números decimales entre 0.0 y 0.99999999. Así que bajo este principio seguiremos los siguientes pasos para realizar la conversión en Excel.

1. Dividir el valor decimal entre 24 para obtener su equivalencia en días y horas de acuerdo al sistema de medición de Excel.

2. Aplicar el formato adecuado al resultado anterior para desplegarlo como horas, minutos y segundos el cual es también un sistema de medición sexagesimal.

Bajo este entendido, si los grados decimales se encuentran en la celda A2, la fórmula a utilizar será la siguiente:

=TEXTO(ABS(A2/24), "[h]° mm' ss''")

La función ABS nos ayuda a obtener el valor absoluto de los grados decimales divididos entre 24 el cual será un valor expresado en días y horas de Excel. El siguiente paso es aplicar el formato adecuado a través de la función TEXTO. Observa que el formato de las horas se coloca entre corchetes [] para indicar a Excel que muestre los días como horas. El resultado de aplicar esta fórmula es el siguiente:

Los valores decimales mostrados en la imagen son los mismos valores de ejemplo utilizados previamente y los valores sexagesimales devueltos por nuestra fórmula son exactamente los mismos que hemos obtenido por el procedimiento descrito en la sección anterior.

De esta manera la función TEXTO nos ayuda a convertir grados decimales a grados, minutos y segundos de una manera rápida y eficiente en Excel.

INICIO

FUNCIONES ACERCA

Cómo eliminar datos repetidos en ExcelExcel es una herramienta ampliamente utilizada para generar reportes, los cuales dependerán de la precisión y calidad de los datos, por lo que en más de una ocasión necesitaremos eliminar datos repetidos en Excel para poder generar reportes efectivos.

No importa de dónde provengan los datos, la realidad es que será una acción que tendremos que realizar frecuentemente por lo que es importante conocer las diferentes alternativas que tenemos para eliminar duplicados en Excel.

Eliminar repetidos con Filtro avanzado

La primera alternativa que podemos utilizar es el Filtro avanzado que está ubicado en la ficha Datos > Ordenar y filtrar > Avanzadas. Al hacer clic en el botón Avanzadas se mostrará el cuadro de diálogo Filtro avanzado donde debemos asegurarnos de elegir las siguientes opciones:

Copiar a otro lugar: Copiará el resultado a otro rango de celdas. Rango de lista: Debes colocar el rango que contiene los datos originales. Copiar a: Indica la celda donde se iniciará la copia de los datos. Sólo registros únicos: Esta es la opción más importante de todas ya que se encargará

de hacer una copia de los registros únicos eliminando así los datos repetidos.

Al hacer clic en el botón Aceptar se hará una copia de los registros únicos y serán colocados en la celda indicada:

De esta manera habremos eliminado los datos repetidos sin haber modificado los datos originales. Este mismo método lo podemos utilizar para eliminar datos repetidos en más de una columna, por ejemplo, en la siguiente imagen puedes observar que he obtenido los valores únicos de la combinación de columnas Nombre y Apellido.

La única diferencia con el primer ejemplo es que ahora estoy pidiendo a Excel copiar los registros únicos que se encuentran en las columnas A y B y como resultado obtendré los valores en las columnas D y E. El método del Filtro avanzado para la eliminación de

datos repetidos es ampliamente utilizado ya que dicho comando ha estado presente durante varias versiones de Excel.

El comando Quitar duplicados

A partir de Excel 2007 se introdujo el comando Quitar duplicados que con un solo clic nos permite eliminar los datos repetidos del rango que hayamos seleccionado previamente. El comando Quitar duplicados está localizado en la ficha Datos, dentro del grupo Herramientas de datos. Al pulsar este comando se mostrará el siguiente cuadro de diálogo:

El comando Quitar duplicados detectará los encabezados en los datos y marcará automáticamente la opción “Mis datos tienen encabezados” en caso de haber encontrado alguno. Además se mostrará la lista de columnas donde se buscarán los valores duplicados y podremos seleccionar las columnas que necesitamos incluir en la validación. Al pulsar el botón Aceptar se eliminarán los datos repetidos y podremos ver un mensaje con los resultados del comando recién ejecutado:

Es importante recordar que al eliminar datos repetidos con el comando Quitar duplicados estaremos afectando directamente los datos originales.

Eliminar repetidos con Tabla dinámica

Otro método que podemos utilizar para eliminar datos repetidos en Excel es utilizar una tabla dinámica y para ello debemos seleccionar cualquier celda de los datos originales y pulsar el botón Insertar > Tabla dinámica. Eso mostrará el cuadro de diálogo Crear tabla dinámica y al pulsar el botón Aceptar tendremos una nueva tabla dinámica donde debemos arrastrar el campo que contiene los datos duplicados a la sección Etiquetas de fila:

De inmediato la tabla dinámica removerá cualquier dato repetido y mostrará los valores únicos bajo la columna Etiquetas de fila. Será suficiente con copiar las celdas de la tabla dinámica y pegar dichos valores únicos a cualquier otro rango de celdas para poder utilizarlo. Si además quieres saber el número de veces que aparece cada uno de los valores en los datos originales será suficiente con arrastrar el mismo campo a la sección Valores:

Ahora ya conoces diferentes alternativas para eliminar datos repetidos en Excel. Puedes experimentar con todas ellas y elegir la que mejor se acomode a tus necesidades.

Cómo obtener el nombre de mes en ExcelCuando trabajamos con fechas es común tener la necesidad de extraer el mes para desplegarlo por su nombre, ya sea para colocarlo en una celda o para concatenarlo con otra cadena de texto. Hoy revisaremos un método muy sencillo para obtener el nombre de mes en Excel.

Confusión con el formato de fecha

Algunos pensarían que cambiando el formato de la fecha, por un formato que despliegue el nombre del mes, sería posible utilizar la función EXTRAE para obtener el nombre del mes. Sin embargo, pronto se dan cuenta de que eso no es posible dada la naturaleza de las fechas en Excel.

Las fechas jamás podrán ser tratadas como cadenas de texto ya que son valores numéricos. Lo que sucede es que Excel cambia la manera de desplegar el número en pantalla aplicando ciertos formatos personalizados de celdas de manera que se muestre como una fecha para nosotros.

Para comprobar lo que acabo de mencionar haré un pequeño ejemplo. En la siguiente imagen puedes observar la misma serie de fechas en las columnas A, B y C donde la

única diferencia es el formato de la fecha. Si la columna C fuera una cadena de texto, entonces podría extraer el primer carácter de la izquierda con la siguiente fórmula:

=EXTRAE(C2,1,1)

Ahora observa el resultado de la fórmula anterior al aplicarla sobre los datos de ejemplo:

El resultado de la función EXTRAE es el número 4 aun cuando dicho número no forma parte de la fecha mostrada en la celda C2. Lo que sucede es que la fecha 1 de enero de 2013 tiene asignado el número 41275 y la función EXTRAE nos está devolviendo acertadamente el primer dígito de dicho número.

Fórmula para obtener el nombre de mes

Ahora que ya sabemos que no podemos tratar las fechas como cualquier otra cadena de texto, es momento de revelar la función que nos ayudará a obtener el nombre de mes en Excel. Para hacer esta extracción utilizaremos la función TEXTO cuyo objetivo es precisamente convertir un valor numérico en una cadena de texto y de paso aplicar un formato. La fórmula a utilizar será la siguiente:

=TEXTO(A2, "mmmm")

La celda A2 contiene la fecha 01/01/2013, así que le corresponde el mes de enero el cual será desplegado con el formato “mmmm”:

Observa que al copiar la fórmula hacia abajo obtendremos el mes para cada una de las fechas del rango. El resultado será el mismo sin importar la columna que utilicemos ya que todas las columnas tienen las mismas fechas. Otra opción de formato disponible para extraer el nombre de mes es la siguiente:

=TEXTO(B2, "mmm")

En lugar de utilizar cuatro letras “m”, este formato utiliza solamente tres, lo que ocasiona que el mes se despliegue con su abreviatura de tres letras de la siguiente manera:

Ambos formatos nos permiten obtener el nombre de mes en Excel, ya sea desplegando su nombre completo o su forma abreviada. Si quieres aprender un poco más sobre la función TEXTO y el manejo de fechas consulta el artículo Extraer información de una fecha.

El Editor de Visual BasicEl 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.

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.

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

Creando una función VBA

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 retornar un valor. 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 BUSCARV. 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. Es importante insertar el código dentro de un Módulo tal como se muestra en el artículo Tu primera macro con VBA. 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

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

INICIO

FUNCIONES ACERCA

Nombres de rango dinámicosExcel nos permite poner nombres a los rangos de celdas de manera que los podamos identificar adecuadamente al usarlos en nuestras fórmulas, pero en esta ocasión te mostraré cómo hacer que ese nombre se refiera a un grupo de celdas que va en aumento.

Al definir un nombre de rango comenzamos por seleccionar los datos y posteriormente asignarles un nombre. En la siguiente imagen puedes observar que he asignado el nombre MiTabla al rango de celdas A1:A3:

Si agrego un nuevo dato por debajo del rango definido no se incluirá de manera automática:

Sería necesario redefinir el nombre del rango para incluir la nueva celda. Sin embargo, podemos hacer que un nombre de rango esté definido por una fórmula y de esa manera actualizar automáticamente los datos que deben ser incluidos.

Rangos dinámicos con DESREF

Para lograr este objetivo utilizaremos la función DESREF que nos permite crear una referencia a un rango. Esta es la sintaxis de la función:

DESREF(ref, filas, columnas, [alto], [ancho])

La sintaxis de la función DESREF nos indica que el primer argumento debe ser la celda “inicial” sobre la cual se basará la referencia. En base a los datos del ejemplo anterior, colocaré como primer argumento la celda A1 que es la primera celda con datos. El segundo y tercer argumento de la función nos permiten especificar cuantas filas y

columnas nos moveremos de la celda “inicial”. En nuestro ejemplo no queremos movernos de esa celda, así que estos parámetros serán siempre cero.

El cuarto y quinto argumento son el alto y ancho de la referencia que queremos crear y aquí es donde viene la parte interesante porque queremos decir a Excel que deseamos todas las celdas que tienen un contenido. Para lograr nuestro cometido debemos utilizar la función CONTARA, la cual nos ayuda a contar las celdas que no están vacías. Para contar las filas que no están vacías utilizo la siguiente función:

=CONTARA($A:$A)

Y para contar las columnas que no están vacías:

=CONTARA($1:$1)

Con los parámetros ya definidos podemos decir que utilizaremos la función DESREF de la siguiente manera suponiendo que los datos se encuentran en la Hoja1:

=DESREF(Hoja1!$A$1, 0, 0, CONTARA(Hoja1!$A:$A), CONTARA(Hoja1!$1:$1))

Esta fórmula siempre nos devolverá el rango que incluye las celdas que tienen un valor y que son adyacentes a la celda A1.

Editar el nombre de rango

Ahora solo resta modificar la definición del nombre de rango MiTabla para que utilice esta fórmula. Para ello debo ir a la ficha Fórmulas y oprimir el botón Administrador de nombres, se mostrará el nombre de rango previamente definido y en el cuadro de texto de la parte inferior se deberá reemplazar su definición por la fórmula anterior:

Con esta nueva definición del rango MiTabla no importarán las filas que agreguemos ya que siempre serán consideradas en el rango. En el siguiente ejemplo, puedes observar cómo voy agregando nuevos valores en la columna A y son considerados automáticamente en la suma de la celda D5:

Fijar la fecha y hora en ExcelEn ocasiones necesitamos insertar la fecha y hora actuales en una celda de nuestra hoja y que dicho valor permanezca fijo después de haberlo insertado. Muchos intentarán hacerlo con las funciones de Excel, pero pronto se darán cuenta que no es posible.

Insertar fecha y hora con funciones

Al utilizar funciones de Excel para insertar la fecha y la hora, el valor mostrado en la celda será actualizado automáticamente al momento de recalcular las fórmulas y por lo tanto los valores de la fecha y hora jamás serán fijos.

Puedes comprobar este comportamiento utilizando la función AHORA, que nos devuelve tanto la fecha como la hora actual. Es probable que al ingresar la función se muestre solamente la fecha, pero podemos cambiar el formato personalizado de la celda por dd/mm/aaaa hh:mm para mostrar también la hora:

Deja pasar algunos segundos hasta que el reloj de tu equipo haya avanzado al menos un minuto, pulsa la tecla F9 y verás que el valor de la hora se actualizará a la misma hora del computador. Por esta razón, no podemos fijar la fecha y hora en Excel utilizando funciones.

Fijar fecha y hora con un atajo de teclado

La única alternativa que tenemos en Excel para evitar que la fecha y hora se actualicen constantemente es introducir sus valores directamente en una celda. Pero ya que esta acción requeriría de un ingreso manual, podemos utilizar los siguientes atajos de teclado para facilitar las cosas:

Insertar la fecha actual: Ctrl + , (coma) Insertar la hora actual: Ctrl + : (dos puntos)

Estos atajos de teclado son válidos para una versión de Excel en castellano. Si tienes una versión en inglés, entonces deberás utilizar el atajo Ctrl + ; (punto y coma) para insertar la fecha actual y el atajo Ctrl + Shift + ; (punto y coma) para la hora.

Macro para insertar fecha y hora actuales

También tenemos la opción de crear una macro para insertar los valores de la fecha y hora actuales en una celda. Eso lo podemos lograr gracias a la función Now en VBA y para mostrarlo comenzaré insertando un botón de comando ActiveX en la hoja actual y después haré doble clic sobre el botón para insertar el siguiente código:

1

2

3

Private Sub CommandButton1_Click()

    Range("B1").Value = Now

End Sub

Esta macro asigna el valor de la función Now a la celda B1 de la hoja actual. Al pulsar el botón obtendré el siguiente resultado:

Con este método he insertado tanto la fecha como la hora actual en la misma celda. Si quisiera insertar solamente la fecha entonces debería utilizar la función Date en lugar de la función Now y si quisiera insertar solamente la hora debería utilizar la función Time.

Si al utilizar este método la celda muestra el error “######” será porque la columna está demasiado angosta como para mostrar la fecha y hora, pero se resolverá fácilmente con tan solo aumentar el ancho de la columna. Descarga el libro de trabajo y comprueba el uso de estas tres funciones VBA que serán útiles para fijar la fecha y hora en Excel.

Cómo resaltar la fila de la celda activa en ExcelCuando tienes una tabla en Excel con muchas columnas es fácil perder de vista la fila a la que pertenecen los datos. Hoy aprenderemos cómo resaltar la fila de la celda activa de manera que podamos distinguir fácilmente todos los valores de una fila.

Nuestro objetivo será crear un apoyo visual para identificar fácilmente los datos que pertenecen a la misma fila de la celda activa. Al momento de seleccionar una nueva celda, la fila anterior quedará sin resaltar y la nueva fila será resaltada. Observa la siguiente animación donde queda claro nuestro objetivo:

El código VBA que desarrollaremos hará que todas las hojas de nuestro libro tengan este comportamiento sin necesidad de copiarlo en cada una de ellas. Así que sin mayor preámbulo, comencemos con el desarrollo de esta funcionalidad.

Crear una variable VBA global

El primer paso será crear una variable global en VBA donde almacenaremos el valor de la celda activa “actual” y el de la celda activa “anterior”. Esto es necesario ya que al movernos a una nueva fila debemos quitar cualquier formato de la fila previamente resaltada y por lo tanto necesito almacenar una referencia a dicha fila. Para crear esta variable debes abrir el Editor de Visual Basic y agregar un módulo de código haciendo clic derecho sobre el nombre del proyecto > Insertar > Módulo:

Una vez creado este módulo debemos colocar la siguiente línea de código:

1Public celdaActiva(1) As Range

El hecho de que la variable sea Public nos permite tener acceso a ella desde cualquier otro módulo de código. Esta variable es un arreglo en VBA que no es más que una colección  de “casillas” donde podemos almacenar valores. En este caso la variable celdaActiva tiene dos casillas del tipo Range las cuales podemos acceder con los índices 0 y 1.

Inicializar la variable celdaActiva

En este punto la variable celdaActiva creada en el paso anterior aún está vacía, así que haremos uso del evento Workbook_Open para indicarle cuál es la celda activa al momento de abrir nuestro libro de Excel. Inserta el siguiente código dentro del evento Workbook_Open del objeto ThisWorkbook:

1

2

3

4

Private Sub Workbook_Open()

    Set celdaActiva(1) = ActiveCell

    ActiveCell.EntireRow.Interior.Color = RGB(255, 145, 145)

End Sub

Al abrir nuestro libro se ejecutará el código del evento WorkbookOpen y por lo tanto el elemento 1 del arreglo celdaActiva tendrá una referencia a la celda activa de nuestro libro. La segunda instrucción que se ejecuta cambiará el valor de la propiedad  ActiveCell.EntireRow.Interior.Color para establecer un color de fondo para toda la fila de la celda activa. En este caso utilizo la función RGB para elegir un color de fondo con una combinación específica de colores rojo, verde y azul. Si quieres saber un poco más sobre los colores en Excel y la función RGB, consulta el artículo Evaluar el color de fondo de una celda.

Además de inicializar la variable celdaActiva al momento de abrir nuestro libro de Excel, lo haremos también al momento de activar una nueva hoja lo que significa que debemos hacer uso del evento Workbook_SheetActivate que también se encuentra dentro del objeto ThisWorkbook. Después de haber insertado el mismo código para ambos eventos, deberás tener una vista en el Editor de Visual Basic como la siguiente:

Resaltar la fila de la celda activa

Ahora solo nos resta resaltar la fila de la celda activa en el momento en que seleccionamos una nueva celda. Ese instante está representado en Excel por el evento Workbook_SheetSelectionChange donde colocaremos el siguiente código:

1

2

3

4

5

6

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

    Set celdaActiva(0) = celdaActiva(1)

    Set celdaActiva(1) = Target

    celdaActiva(0).EntireRow.Interior.Color = xlNone

    Target.EntireRow.Interior.Color = RGB(255, 145, 145)

End Sub

Ya que este evento se “dispara” en el momento en que seleccionamos una nueva celda, la primera instrucción almacena el valor de la celda activa anterior dentro de celdaActiva(0) e inmediatamente después guardamos la referencia a la nueva celda activa dentro de celdaActiva(1) y que en ese instante está referenciada por la variable Target. Las últimas dos instrucciones cambian el color de fondo de las filas. Primero igualamos el color de celda activa anterior a xlNone que significa que removeremos cualquier color de fondo y enseguida establecemos un color de fondo con la función RGB para la fila de la nueva celda activa. Al finalizar todos estos pasos tendremos un código como el siguiente:

Eso será todo. Al haber seguido estos pasos tendrás un libro de Excel que resaltará la fila de la celda activa en todas las hojas.

Observaciones sobre el método utilizado

El método utilizado para resaltar la fila de la celda activa en Excel mostrado en este artículo modifica el valor de la propiedad Interior.Color de cada una de las celdas de la fila. Por esta razón, si tus celdas tienen aplicado algún color, éste será removido al momento de seleccionar una celda de la misma fila.

Es importante recordar que los colores que serán removidos serán los aplicados en el relleno de las celdas a través de las herramientas de formato. Los colores aplicados por formato condicional quedarán intactos ya que dichos colores se almacenan en una propiedad diferente. De hecho, si tus datos tienen reglas de formato condicional, dichos colores estarán sobre puestos al color aplicado por el código de este ejemplo.

Resaltar fila y columna de la celda activa

Una variante interesante del ejemplo mostrado anteriormente es agregar algunas líneas de código para resaltar la columna de la celda activa y de esa manera ubicarla tanto horizontal como verticalmente. El código a utilizar será el siguiente:

1

2

3

4

5

Private Sub Workbook_Open()

    Set celdaActiva(1) = ActiveCell

    ActiveCell.EntireRow.Interior.Color = RGB(255, 145, 145)

    ActiveCell.EntireColumn.Interior.Color = RGB(255, 145, 145)

End Sub

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

    Set celdaActiva(1) = ActiveCell

    ActiveCell.EntireRow.Interior.Color = RGB(255, 145, 145)

    ActiveCell.EntireColumn.Interior.Color = RGB(255, 145, 145)

End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

    Set celdaActiva(0) = celdaActiva(1)

    Set celdaActiva(1) = Target

    celdaActiva(0).EntireRow.Interior.Color = xlNone

    celdaActiva(0).EntireColumn.Interior.Color = xlNone

    Target.EntireRow.Interior.Color = RGB(255, 145, 145)

    Target.EntireColumn.Interior.Color = RGB(255, 145, 145)

End Sub

Observa que lo único que he hecho es agregar las líneas de código que hacen referencia a la propiedad EntireColumn.Interior.Color que resaltan la columna de la celda activa. Por supuesto que esta versión de código también necesitará de una variable global declarada en un Módulo de manera similar que en el primer ejemplo. El resultado de utilizar este código es el siguiente:

Cómo restar en ExcelCuando comenzamos a utilizar las hojas de cálculo es normal preguntarnos cómo realizar las operaciones aritméticas más elementales como sumar y multiplicar, pero estoy seguro que cuando eras principiante en Excel te preguntaste en más de una ocasión cómo restar en Excel.

¿La función RESTA en Excel?

Una de las primeras funciones que aprendemos a utilizar en Excel es la función SUMA, así que es prácticamente inevitable razonar que: Si existe una función para sumar, seguramente debe existir una función para restar. Sin embargo, después de hacer una búsqueda exhaustiva, todo usuario que comienza en Excel se entera de que la función RESTA no existe.

A muchos les parece extraño que no exista una función para restar en Excel, pero todo comienza a hacer más sentido cuando nos damos cuenta de que podemos restar números utilizando la función SUMA. Lo único que debemos hacer es utilizar valores negativos y dejar que la función se  encargue de realizar la operación aritmética. Considera como ejemplo la siguiente fórmula:

=SUMA(10, -3)

En esta fórmula he colocado el valor -3 como segundo argumento de la función SUMA y por lo tanto al realizar el cálculo obtendré el resultado de la operación diez menos tres:

Ya que la función SUMA nos permite indicar valores numéricos o  referencias, entonces también es posible restar el valor de varias celdas de la siguiente manera:

Observa que he especificado valores negativos en las celdas B1 y C1 y por lo tanto la función SUMA en realidad estará haciendo una resta de dichos números. De esta manera podemos concluir que al no existir la función RESTA en Excel, podemos utilizar la función SUMA siempre y cuando indiquemos los  valores negativos.

El operador aritmético para la resta

Una segunda alternativa que tenemos para restar en Excel es utilizar directamente el operador aritmético para la resta que es el guion medio (-). Este operador lo podemos utilizar ya sea con valores numéricos o con referencias de celdas. Observa cómo resto el valor de las celdas B1 y C1 al valor de la celda A1 utilizando este operador:

A diferencia del ejemplo anterior que utilizaba la función SUMA, en esta ocasión los valores de todas las celdas son positivos y el operador se encarga de hacer las restas correspondientes. Son los operadores los que nos permiten utilizar Excel como si fuera una calculadora, por ejemplo, podemos escribir fórmulas como la siguiente para mezclar operaciones de suma y resta:

=A1+B1-C1-D1

También es posible mezclar el operador de resta con la función SUMA para restar un conjunto de números a otro. En la siguiente imagen hago la suma de todos los costos de una empresa y el resultado lo resto a los ingresos para obtener la utilidad bruta.

Si quieres saber un poco más sobre los operadores aritméticos en Excel consulta el artículo: Tipos de operadores en Excel.

Restar con la función IM.SUSTR

La función IM.SUSTR está presente desde la versión de Excel 2007 y es utilizada para restar dos números complejos, pero la verdad es que son muy pocas las personas que hacen operaciones con este tipo de números.

Lo único que debemos saber es que un número complejo está formado por un número real y un número imaginario. Los números que hemos utilizado hasta ahora en este artículo son reales, así que esta función puede ayudarnos perfectamente a restar en Excel, pero debemos recordar que solo nos permitirá restar dos números a la vez. Observa la siguiente imagen donde hago uso de esta función:

La celda C1 muestra correctamente el resultado de la operación 10 menos 3. La única peculiaridad del resultado es que el número 7 está alineado a la izquierda, lo cual nos indica que Excel lo ha identificado como una cadena de texto. Esto se debe a que los números complejos son representados en Excel como cadenas de texto, así que debes tener eso en cuenta si alguna vez decides utilizar la función IM.SUSTR para hacer una resta en Excel.

Filtros en ExcelLos filtros en Excel nos permiten buscar un subconjunto de datos que cumpla con ciertos criterios. Generalmente todo comienza cuando tenemos un rango de celdas con información y queremos ver solamente aquellas filas que cumplen con ciertas condiciones.

Por ejemplo, en la siguiente imagen se pueden ver los datos de ventas de una empresa. ¿Cómo puedo tener una vista con todas las filas que pertenecen a Hugo? Eso sería una tarea muy difícil de lograr si no tuviéramos la facilidad de crear filtros en Excel.

Cómo crear filtros en Excel

Para crear un filtro podemos utilizar el comando Filtro que se encuentra en la ficha Datos dentro del grupo Ordenar y filtrar.

Al pulsar el botón Filtro se colocarán flechas en el extremo derecho de cada uno de los encabezados de columna de nuestros datos indicando que podemos hacer uso de los filtros. El comando Filtro también podrás seleccionar desde Inicio > Modificar > Ordenar y filtrar > Filtro.

Otra manera de crear un filtro es transformar nuestros datos en una tabla de Excel, lo cual insertará los filtros además de aplicar un formato especial a los datos.

Cómo usar los filtros en Excel

Para filtrar la información debemos elegir una columna y hacer clic en la flecha de filtro correspondiente para mostrar las opciones de filtrado. Todos los filtros, en la parte inferior, mostrarán una lista de valores únicos con una caja de selección a la izquierda de cada uno.

Una opción que tenemos para filtrar los datos es elegir de manera individual aquellos valores que deseamos visualizar en pantalla. También podemos utilizar la opción (Seleccionar todo) para marcar o desmarcar todos los elementos de la lista. En la imagen anterior he elegido el nombre Hugo de manera que el filtro mostrará solamente las filas con dicho nombre.

Al pulsar el botón Aceptar se ocultarán las filas que no cumplen con el criterio de filtrado establecido. Observa que la flecha de filtro de la columna Vendedor ha cambiado para indicarnos que hemos aplicado un filtro. Además, los números de fila de Excel se muestran en un color diferente indicándonos que existen filas ocultas.

Filtrar por varias columnas

Si queremos segmentar aún más los datos mostrados en pantalla podemos filtrar por varias columnas. En el ejemplo anterior filtré las filas pertenecientes a Hugo, pero si además necesito saber las que pertenecen a la región Norte y Sur, entonces debo seleccionar dichas opciones dentro del filtro de la columna Región:

Al aceptar estos cambios se mostrarán solamente las filas que cumplen ambos criterios. Observa que ambas columnas habrán cambiado sus iconos para indicarnos que se ha aplicado un filtro en cada una de ellas.

Esto demuestra que es posible crear tantos filtros como columnas tengamos en nuestros datos y entre más criterios de filtrado apliquemos mucha mayor será la segmentación de datos que obtendremos.

Cómo quitar un filtro en Excel

Para quitar un filtro aplicado a una columna debemos hacer clic en la flecha del filtro y seleccionar la opción Borrar filtro de “Columna” donde Columna es el nombre de la columna que hemos elegido. Esta acción eliminará el filtro de una sola columna, pero si tenemos filtros aplicados a varias columnas y deseamos eliminarlos todos con una sola acción, entonces debemos pulsar el comando Borrar que se encuentra en la ficha Datos > Ordenar y filtrar.

Filtrar en Excel buscando valores

Ya hemos visto que todos los filtros muestran una lista de valores únicos de la cual podemos seleccionar uno o varios de ellos y justo por arriba de dicha lista de valores se muestra un cuadro de texto que nos permite hacer una búsqueda. Por ejemplo, en la siguiente imagen he colocado la palabra “este” en el cuadro de búsqueda y como resultado se ha modificado la lista de valores mostrando solo aquellos donde se ha encontrado dicha palabra:

Cuando tenemos una lista muy grande de valores únicos y no podemos identificar fácilmente  aquellos que deseamos seleccionar, podemos utilizar el cuadro de búsqueda para encontrar los valores que necesitamos. También es posible utilizar caracteres comodines como el asterisco (*) o el símbolo de interrogación (?) tal como si hiciéramos una búsqueda aproximada en Excel de manera que podamos ampliar los resultados de búsqueda.

Filtros de texto en Excel

Además de las opciones ya mencionadas para filtrar en Excel, cuando en una columna se detecta el tipo de dato texto, se mostrará una opción de menú llamada Filtros de texto como la siguiente:

Al elegir cualquiera de estas opciones se mostrará un cuadro de diálogo que nos permitirá configurar cada uno de los criterios disponibles. Por ejemplo, al elegir la opción Comienza por se mostrará el siguiente cuadro de diálogo:

Si colocamos la letra “a” en el cuadro de texto junto a la opción “comienza por”, entonces Excel mostrará solamente los elementos de la columna Vendedor que comiencen por la letra “a”.

Filtros de número en Excel

De manera similar, si Excel detecta que una columna contiene valores numéricos, nos permitirá utilizar filtros específicos para dicho tipo de dato tal como lo puedes observar en la siguiente imagen:

A diferencia de los Filtros de texto, Excel nos permitirá utilizar los Filtros de número para mostrar valores que sean mayores o iguales que otro o simplemente aquellos que son superiores al promedio.

Filtros de fecha en Excel

Las fechas son el tipo de dato que más opciones de filtrado nos proporcionan, tal como lo muestra la siguiente imagen:

Excel nos permitirá filtrar las fechas por días específicos como hoy, mañana o ayer e inclusive por períodos de tiempo más largos como semanas, meses, trimestres o años con tan solo seleccionar la opción adecuada.

Filtrar por color en Excel

No podíamos pasar por alto y dejar de hablar de la opción de Filtrar por color que nos ofrece Excel. Para que esta opción se habilite es necesario que las celdas tengan aplicado un color de relleno ya sea por una regla de formato condicional o modificando directamente el color de relleno con las herramientas de formato. En nuestro ejemplo he aplicado una regla de formato condicional para aquellas celdas que tengan un valor superior a $850 en la columna Total.

Una vez que las celdas tienen un color de relleno, al hacer clic en el filtro de la columna Total se mostrará habilitada la opción Filtrar por color y dentro de ella podré elegir alguno de los colores presentes en la columna.

Sumar y contar celdas por color de formato condicionalHace algunos meses publique el artículo Operaciones con colores en Excel y de inmediato recibí preguntas sobre la posibilidad de utilizar dichas funciones para evaluar los colores aplicados a una celda a través del formato condicional.

Las funciones creadas en ese artículo evalúan la propiedad Interior.Color que tiene cada una de las celdas en Excel, sin embargo, el formato condicional no utiliza dicha propiedad sino que tiene su propia “versión” para almacenar el color de fondo de una celda. Por esa razón no es posible utilizar dichas funciones para evaluar colores establecidos a través de una regla de formato condicional.

Así que hoy crearemos una nueva función VBA (UDF) para sumar y contar celdas por color de formato condicional. Pero antes de iniciar con el código hablaremos un poco sobre los objetos y propiedades que debemos evaluar para obtener el color de fondo proveniente de un regla de formato condicional.

La colección FormatConditions

Primero debemos recordar que un mismo rango de celdas en Excel puede estar sujeto a varias reglas de formato condicional al mismo tiempo, así que para guardar esa lista de

formatos condicionales se creó la colección FormatConditions en VBA la cual enumera todas las reglas de formato condicional aplicadas en un rango.

Para comprender el funcionamiento de esta colección haremos un ejemplo. Considera la siguiente lista de números en el rango A1:A10 donde he aplicado una regla de formato condicional que resalta en color rojo los valores mayores a 750.

Ahora insertaré un botón de comando ActiveX en la misma hoja y colocaré el siguiente código VBA en su evento Click:

1

2

3

Private Sub CommandButton1_Click()

    MsgBox Range("A1:A10").FormatConditions.Count

End Sub

La única línea de código ejecutada se encargará de mostrar el valor de la propiedad Count de la colección FormatConditions la cual contiene el recuento de las reglas de formato condicional para el rango indicado. Al hacer clic sobre el botón de comando se mostrará el siguiente mensaje:

El mensaje nos indica que el rango A1:A10 tiene una sola regla de formato condicional. Ahora crearé una segunda regla para el mismo rango que resaltará de color verde todas las celdas con un valor menor a 250. Una vez creada la regla de formato condicional, volveré a pulsar el botón de comando y el número mostrado en el mensaje habrá aumentado debido a la nueva regla creada:

Es así como la colección FormatConditions nos permite obtener información sobre las reglas de formato condicional aplicadas a un rango de celdas. Ahora centraremos nuestra atención a una propiedad específica de dicha colección.

La propiedad FormatCondition.Interior.Color

Cada regla de formato condicional almacena el estilo que aplicará a las celdas que cumplan con las condiciones establecidas y específicamente el color de relleno se almacena en la propiedad Interior.Color. Para demostrar el valor de esta propiedad agregaré un nuevo botón de comando con el siguiente código:

1

2

3

4

5

6

Private Sub CommandButton2_Click()

    For i = 1 To Range("A1:A10").FormatConditions.Count

        MsgBox "Regla " & i & vbLf & _

            "Color: " & Range("A1:A10").FormatConditions(i).Interior.Color

    Next i

End Sub

El código anterior recorre todos los elementos de la colección FormatConditions y para cada elemento mostrará un mensaje con el número de regla y su color de relleno que está almacenado en la propiedad Interior.Color. Al pulsar el botón obtengo el siguiente resultado:

El color devuelto será un valor entre 0 y 16777215 que corresponde a una de las combinaciones de colores primarios (rojo, verde y azul) que se pueden formar en Excel. Si quieres saber un poco más sobre los colores en Excel consulta el artículo Evaluar el color de fondo de una celda.

Los colores mostrados con el código anterior son los colores pertenecientes a cada una de las reglas de formato condicional y no el color de una celda específica. Para conocer el color de formato condicional aplicado a una celda será necesario encontrar la regla que se cumple sobre dicha celda para entonces obtener el color correspondiente.

Macro para obtener el color de una celda

El desafío más grande al crear una macro para obtener el color de una celda es descubrir la regla de formato condicional que está activa. Para eso utilizamos un bucle For Next que recorrerá toda la colección de formatos haciendo una evaluación de cada regla para descubrir si está activa.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

Function COLORFC(Celda As Range) As Long

'Indicará si la relga de formato condicional está activa

Dim ReglaActiva As Boolean

'Recorrer todas las reglas de formato condicional para la celda indicada

For i = 1 To Celda.FormatConditions.Count

    'Evaluar la regla FormatConditions(i)

    With Celda.FormatConditions(i)

    'Si la regla está basada en el valor de la celda

    If .Type = xlCellValue Then

    'Identificar el operador de la regla y evaluar si está activa

    Select Case .Operator

    Case xlBetween:      ReglaActiva = Celda.Value >= Evaluate(.Formula1) _

                         And Celda.Value <= Evaluate(.Formula2)

    Case xlNotBetween:   ReglaActiva = Celda.Value <= Evaluate(.Formula1) _

                         Or Celda.Value >= Evaluate(.Formula2)

    Case xlEqual:        ReglaActiva = Evaluate(.Formula1) = Celda.Value

    Case xlNotEqual:     ReglaActiva = Evaluate(.Formula1) <> Celda.Value

    Case xlGreater:      ReglaActiva = Celda.Value > Evaluate(.Formula1)

    Case xlLess:         ReglaActiva = Celda.Value <

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

Evaluate(.Formula1)

    Case xlGreaterEqual: ReglaActiva = Celda.Value >= Evaluate(.Formula1)

    Case xlLessEqual:    ReglaActiva = Celda.Value <= Evaluate(.Formula1)

    End Select

    'Si la regla es una expresión (Fórmula)

    ElseIf .Type = xlExpression Then

        Application.ScreenUpdating = False

        Celda.Select

        ReglaActiva = Evaluate(.Formula1)

        Range(ActiveCell.Address).Select

        Application.ScreenUpdating = True

    End If

    'Devolver el color si la regla está activa

    If ReglaActiva Then

        COLORFC = .Interior.Color

        Exit Function

    End If

    End With

Next i

End Function

Las primeras líneas de código se encargan de recorrer todas las reglas de formato condicional para la celda proporcionada. La primera validación importante es sobre la propiedad Type que nos permite saber el tipo de regla que estamos analizando, ya sea

del tipo xlCellValue o del tipo xlExpression. Es importante mencionar que existen más tipos de reglas de formato condicional, de hecho la enumeración XlFormatConditionType contiene 14 tipos diferentes, pero solamente los dos tipos mencionados anteriormente tienen una manera de ser evaluados desde VBA. Por esta razón la función COLORFC considera en primer lugar las reglas de formato condicional que evalúan directamente el valor de una celda.

El cuadro de diálogo anterior muestra precisamente la lista de reglas de formato condicional que son del tipo xlCellValue y que por lo tanto serán reconocidas por nuestra función. Ahora hagamos una prueba con los datos de ejemplo para ver cómo la función COLORFC devuelve el color aplicado a cada celda de acuerdo a la regla de formato condicional activa. Recuerda que las reglas creadas resaltan de rojo las celdas con un valor superior a 750  y de color verde los valores menores a 250.

Nuestra función VBA funciona correctamente al devolver el código de color adecuado para cada celda. El otro tipo de reglas consideradas en la función COLORFC son aquellas basadas en una expresión, que generalmente son las reglas basadas en una fórmula que contiene funciones de Excel. Pero debo darte una mala noticia, esta opción solo funcionará si tenemos Excel en inglés. Y a continuación explico por qué.

Al momento de crear una regla de formato condicional que utilice una función de Excel, colocaremos su nombre en castellano. Dicha fórmula se almacenará en una propiedad de la regla de formato condicional y al momento de evaluarla desde VBA la cadena de texto contendrá el nombre de la función en español lo cual ocasionará un error ya que VBA solamente entiende las funciones de Excel por su nombre en inglés. Aún así he dejado esta funcionalidad porque existe un porcentaje de usuarios que utilizan Excel en

inglés y ellos podrán obtener el color de las celdas que utilicen reglas de formato condicional basadas en fórmulas.

Después de todas estas aclaraciones (que eran necesarias), tenemos una función que nos devolverá el color aplicado a una celda desde una regla de formato condicional, así que ahora solo debemos utilizarla para sumar o contar celdas por color.

Sumar por color de formato condicional

Para sumar por color de formato condicional crearé una nueva función VBA de la siguiente manera:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

Function SUMARPORCOLORFC(CeldaColor As Range, Rango As Range) As Double

Dim Celda As Range

Dim Total As Double

Dim Color As Long

Color = COLORFC(CeldaColor)

For Each Celda In Rango.Cells

    If COLORFC(Celda) = Color Then

        Total = Total + Celda.Value

    End If

Next Celda

SUMARPORCOLORFC = Total

End Function

El primer argumento de la función es una celda que contiene el color por el cual deseamos sumar, así que una de las primeras acciones es obtener dicho color con la función COLORFC. Posteriormente se hará un recorrido por todo el rango de celdas para sumar el valor de aquellas que tengan el mismo color. Observa cómo trabaja nuestra función con los datos de ejemplo:

La función devuelve correctamente la suma de las celdas A1 y A9 que son las que tienen el mismo color que la celda A1 que ha sido indicada como el primer argumento de la función.

Contar por color de formato condicional

Si en lugar de sumar deseamos contar las celdas que tienen un mismo color, será suficiente con modificar la función anterior para que en lugar sumar el valor de la celda se sume el valor 1 por cada celda con el color indicado. El código de esta nueva función es el siguiente:

1

2

3

4

5

6

7

8

9

10

11

12

Function CONTARPORCOLORFC(CeldaColor As Range, Rango As Range) As Integer

Dim Celda As Range

Dim Total As Integer

Dim Color As Long

Color = COLORFC(CeldaColor)

For Each Celda In Rango.Cells

    If COLORFC(Celda) = Color Then

        Total = Total + 1

    End If

Next Celda

13

14

15

16

17

CONTARPORCOLORFC = Total

End Function

Al utilizar esta función con los mismos datos de ejemplo podrás observar que la función CONTARPORCOLORFC nos devuelve el valor 2 que corresponde a las celdas con el mismo color que la celda A3:

Descarga el libro de trabajo para comenzar a sumar y contar celdas por color de formato condicional en tus propios libros de trabajo. Solo recuerda que las funciones creadas en este artículo validarán solo las reglas de formato condicional basadas en el valor de la celda. También recuerda que podrás evaluar reglas basadas en fórmulas solamente si tienes una versión en inglés de Excel.

Cómo multiplicar horas por dinero en ExcelTal vez te ha sucedido que llega el momento del pago a los empleados de la empresa y tienes lista la información en Excel con las horas trabajadas durante la semana. Ya has calculado el total de horas a pagar y solo hace falta la multiplicación por la tarifa establecida, sin embargo solo obtienes montos incorrectos.

¿Cómo se hace la multiplicación de horas por dinero en Excel? Todo indicaría que es un cálculo muy sencillo pero por más que lo intentas solo consigues datos erróneos. En la siguiente imagen podrás darte cuenta a lo que me refiero.

La celda C9 tiene la fórmula =C7*C8 que es la multiplicación del total de horas trabajadas por la tarifa indicada. El resultado debería ser $4,050 pero en su lugar tenemos solo $168.75 que es un monto incorrecto.

Si analizamos las celdas involucradas en el cálculo nos damos cuenta que la tarifa por hora es simplemente el valor numérico 100. Por otro lado la celda C7 hace la suma del rango C2:C6 y la única peculiaridad es que tiene aplicado un formato personalizado para desplegar correctamente la suma de horas y minutos:

¿Cuál de los dos valores es el culpable del error? Definitivamente no es el valor numérico de la tarifa por hora, así que el problema debe ser ocasionado por la suma de horas.

Las horas en Excel son decimales

El problema con la multiplicación de tiempo por dinero es que las horas en Excel no son en realidad lo que parece. Para darnos cuenta del valor real de la celda C7 basta con aplicar el formato General a dicha celda:

Las 40 horas y 30 minutos que inicialmente desplegaba la celda C7, son en realidad el valor numérico 1.6875 y es la razón por la cual el monto a pagar calculado en la celda C9 nos devuelve como resultado el monto $168.75.

Al trabajar con datos de tiempo en Excel vemos desplegadas las horas y minutos tal como los conocemos, pero su valor real es un número decimal entre 0.0, para las 00:00:00 horas, y hasta 0.99999999 que representa las 23:59:59 horas. Eso quiere decir que el valor entero 1 significa un día completo de 24 horas y por tal motivo el valor de la celda C7 significa que tenemos 1 día entero y 0.6875 de otro día.

Multiplicar horas por dinero en Excel

Para resolver este problema del valor decimal de las horas será suficiente con agregar la multiplicación por 24 de manera que se haga la conversión del valor decimal a la cantidad correcta de horas. Observa cómo al incluir esta multiplicación en la fórmula de la celda C9 obtenemos el resultado correcto:

Ahora ya sabes cómo multiplicar horas por dinero en Excel de manera que puedas calcular y pagar correctamente a cada empleado por las horas trabajadas en la empresa.

Insertar símbolos y caracteres especialesExcel nos permite insertar símbolos dentro de las celdas de una manera fácil y rápida a través del cuadro de diálogo Símbolos. Algunos de los caracteres que podremos insertar serán los símbolos de monedas, letras del alfabeto griego o caracteres especiales como el símbolo de derechos reservados.

Para insertar un símbolo en Excel debes ir a la ficha Insertar y pulsar el botón Símbolo que se encuentra dentro del grupo Símbolos.

Se mostrará el cuadro de diálogo Símbolo el cual contiene las pestañas Símbolos y Caracteres especiales.

En la pestaña Símbolos puedes cambiar el tipo de fuente seleccionada para que se muestren símbolos diferentes. Te recomiendo seleccionar las fuentes Symbol, Webdings y Wingdings donde encontrarás una gran cantidad de símbolos que pueden ser de utilidad y que generalmente no están presentes en el resto de las fuentes. Una vez que hayas encontrado el símbolo que deseas insertar solamente debes seleccionarlo y pulsar el botón Insertar y el símbolo se insertará en la celda activa.

Por otro lado, en la pestaña Caracteres especiales encontrarás una lista de algunos caracteres especiales que son usados frecuentemente como el símbolo de Copyright o de Marca registrada. De igual manera deberás seleccionar el carácter que necesitas y pulsar el botón Insertar para incluirlo en la celda activa. Una vez que hayas terminado de insertar los símbolos necesarios debes pulsar el botón Cerrar y estarás de regreso en la hoja de Excel.

Referencias 3D

Una referencia nos ayuda a identificar una celda o rango de celdas y de esta manera podemos indicar a Excel el lugar exacto donde buscar los valores o los datos que deseamos utilizar. Hoy hablaré de manera especial de las referencias 3D en Excel.

Referencias de celdas tradicionales

Para comprender mejor el beneficio de las referencias 3D, hagamos un ejemplo utilizando referencias de celdas de la manera tradicional. Supongamos que tengo un libro de Excel con 12 hojas, una para cada mes del año y dentro de cada hoja, en la celda B2, tengo el dato del total de ventas del mes.

Ahora deseo crear una nueva hoja que tendrá la suma de las celdas B2 de todas las hojas. Para obtener esta suma podría sumar cada una de las celdas B2 de todas las hojas de la siguiente manera:

Con esta fórmula obtendré la suma adecuada, sin  embargo me tardaré algunos segundos en construir la formula y probablemente cometeré algún error al introducirla. Para evitar cualquier problema, podemos hacer esta misma suma de una manera mucho más rápida utilizando una referencia 3D.

Crear una referencia 3D en Excel

Cuando deseamos utilizar la misma celda de varias hojas podemos crear una referencia 3D. Para nuestro ejemplo la referencia 3D para las celdas B2 de todas las hojas de los meses la formamos de la siguiente manera:

ENE:DIC!B2

La referencia 3D indica a Excel que debe considerar todas las hojas de Excel comprendidas entre la hoja ENE y la hoja DIC y tomar el valor de la celda B2 de todas ellas. Al indicar esta referencia como el argumento a la función SUMA obtendremos el resultado deseado:

En este ejemplo he utilizado una referencia 3D para indicar una sola celda, sin embargo las referencias 3D también se pueden utilizar con rangos de celdas.

El Asistente para tablas y gráficos dinámicosPor muchos años he utilizado el Asistente para tablas y gráficos dinámicos en Excel para crear mis tablas dinámicas, sin embargo a partir de Excel 2007 este Asistente ya no está disponible en la Cinta de opciones y no es tan simple encontrarlo, pero hoy te mostraré cómo hacerlo.

Encontrando el asistente para tablas dinámicas

Un método para mostrar el Asistente para tablas y gráficos dinámicos es utilizar el atajo de teclado: ALT+T+B. (Si tienes Excel en inglés el atajo es: ALT+D+P). La verdad es que no es tan sencillo ingresar esta combinación de teclas ya que si no se hace de manera adecuada se abrirá el cuadro de diálogo para crear una Tabla de datos.

El método que yo sigo es pulsar la tecla ALT por 2 segundos y mientras la mantengo pulsada, ingresar la combinación de las teclas T+B. Pero no te preocupes si no puedes mostrar el Asistente con este método ya que existe otra alternativa que explico a continuación.

Otro método para mostrar el Asistente para tablas y gráficos dinámicas es agregarlo a la barra de herramientas de acceso rápido y para hacerlo debes hacer clic derecho sobre la barra de herramientas y seleccionar la opción Personalizar barra de herramientas de acceso rápido y se mostrará el cuadro de diálogo Opciones de Excel. En la sección “Comandos disponibles en” deberás seleccionar la opción Todos los comandos y entonces buscar el comando correspondiente al Asistente:

De esta manera podrás tener siempre disponible en la barra de herramientas el Asistente para tablas y gráficos dinámicos que nos ayudará en la creación de tablas dinámicas.

Crear una tabla dinámica con el Asistente

La primera de las opciones del Asistente nos permite crear una tabla dinámica con los datos que tengamos en nuestras hojas de Excel, por ello esta opción es llamada Lista o base de datos de Microsoft Excel.

Al seleccionar esta opción y hacer clic en Siguiente, se mostrará el segundo paso del proceso que nos permitirá especificar el rango de celdas en las que estará basada nuestra tabla dinámica.

Una vez seleccionado el rango adecuado, pulsamos el botón Siguiente y podremos decidir si deseamos crear la tabla dinámica en una hoja de cálculo nueva o en una hoja ya existente.

Pulsamos el botón Finalizar y nuestra tabla dinámica habrá s

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.

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.

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.

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 funcón SUMA a los valores y la tabla dinámica que resulta después de hacer esta configuración es la siguiente:

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.

Formularios en ExcelLos formularios en Excel son un método para ingresar datos a nuestras hojas y son de mucha utilidad porque nos ayudan a evitar errores en la captura de información. Podemos comparar los  formularios de Excel con los formularios impresos en papel.

¿Qué es un formulario en Excel?

Seguramente has llenado un formulario en papel, tal vez cuando has acudido a alguna oficina de gobierno para realizar un trámite, así que sabes de lo que estoy hablando. Estos formularios contienen instrucciones precisas de lo que debes escribir  y contienen los recuadros necesarios para ingresar los datos.

Los formularios en Excel no son tan diferentes de los formularios impresos ya que de igual manera proveen de los espacios necesarios para ingresar los datos. Los formularios en Excel utilizan objetos especiales conocidos como controles de formulario que son precisamente los que nos permiten agregar campos de texto, listas, botones de opción entre otras cosas más.

Tipos de formularios en Excel

Cuando hablamos de formularios en Excel, es necesario recordar que podemos crear tres tipos diferentes de formularios:

Formulario de datos. Hojas de cálculo con controles de formulario o controles ActiveX. Formularios de usuario en VBA.

A continuación describiré brevemente cada uno de ellos.

Formulario de datos

A través de un formulario de datos podemos mostrar al usuario la información de una sola fila de una tabla.  En este formulario se puede hacer la edición de la información e inclusive crear un nuevo registro para la tabla.

Excel puede generar automáticamente un formulario de datos para cualquier tabla. Un detalle importante que debes saber sobre los formularios de datos es que si una celda contiene una fórmula no podrás modificar dicha fórmula mediante el formulario sino que solamente se mostrará el resultado del cálculo sin poder editarlo.

Para saber más sobre este tema consulta el artículo: Agregar filas a una tabla de Excel.

Hoja de cálculo con controles de formulario

Ya que las celdas de una hoja de Excel nos sirven para introducir información, podemos pensar en una hoja como un gran formulario. De esta manera, si agregamos controles de formulario a nuestra hoja podremos crear formularios de entrada de datos muy útiles. Por ejemplo, podemos agregar a nuestra hoja un control de cuadro de lista para facilitar la selección de los elementos.

De igual manera podemos agregar botones, cuadros combinados, casillas de verificación y otros controles más que nos ayudarán a crear formularios avanzados.

Formulario de usuario en VBA

Los formularios de usuario en VBA, también conocidos como UserForm, son cuadros de diálogo que hacen uso de controles de formulario para solicitar información al usuario. Estos formularios son creados desde el Editor de Visual Basic y administrados desde código VBA.

Una vez que se ha creado el formulario de usuario podemos arrastrar y soltar los controles que están disponibles desde el Cuadro de herramientas:

Si quieres crear un formulario básico en VBA, sigue los pasos descritos en el artículo Formularios en VBA.

Los diferentes tipos de formularios en Excel nos evitarán dolores de cabeza ya que podremos tener cierto grado de control sobre el ingreso de datos que otras personas realizan sobre nuestras hojas de Excel.

Base de datos en ExcelUna base datos es un conjunto de datos que ha sido organizado bajo un mismo contexto y cuya información está almacenada y lista para ser utilizada en cualquier momento. Las bases de datos pueden almacenar información sobre personas, productos, ventas o cualquier otra cosa.

Una base de datos organiza la información relacionada en tablas las cuales están compuestas por columnas y filas. Una tabla tendrá un número específico de columnas, pero tendrá cualquier número de filas.

Si observas con detenimiento la imagen anterior, notarás que una tabla se asemeja mucho a una hoja de Excel la cual también está dividida en columnas y filas. Por esa razón Excel ha sido utilizado por mucho tiempo, y por muchas personas, como un repositorio de datos.

Diseño de bases de datos en Excel

Si diseñas y organizas adecuadamente la información dentro de tu libro de Excel podrás consultar tus datos de una manera fácil y eficiente, así que en esta ocasión hablaremos sobre las mejores prácticas para crear una base de datos en Excel que te permitirán organizar y estructurar adecuadamente la información.

Como ejemplo analizaremos el caso de una pequeña librería que desea crear un catálogo de sus libros en Excel así como tener una lista de sus clientes y las órdenes de compra. Antes de crear la base de datos en Excel comenzaremos por crear el diseño siguiendo los pasos descritos en el artículo Diseño de bases de datos.

Paso 1: Identificar las tablas de la base de datos

Nuestro primer paso en el diseño de una base de datos en Excel será identificar las tablas que ocuparemos para organizar la información. Para nuestro ejemplo de la librería podemos mencionar las siguientes tablas:

Para identificar adecuadamente las tablas de una base de datos debes poner atención en las agrupaciones naturales de la información, es decir, en los sujetos que interactúan en la problemática analizada. En este caso los sujetos principales son los libros, los clientes y las órdenes.

Paso 2: Determinar los campos de las tablas

Una vez que hemos identificado las tablas debemos indicar el nombre de cada una de sus columnas (campos). El nombre del campo deberá ser descriptivo y nos ayudará a identificar cada una de las propiedades de un elemento de la tabla.

Por ejemplo, para cada libro en venta en la librería guardaremos su código ISBN, el título, el autor y el precio por lo que dicha tabla quedará definida de la siguiente manera:

Los campos de cada tabla dependerán de la información que está a tu disposición para ser ingresada en la base de datos. Por ejemplo, algunas empresas o negocios tendrán interés  en capturar más información sobre sus clientes lo cual hará que su tabla de Clientes tenga más campos que los mostrados en el ejemplo anterior.

Paso 3: Identificar la llave primaria de cada tabla

Una llave primaria nos permitirá identificar de manera única cada uno de los registros de las tablas. En el caso de la tabla Libros, el ISBN será la llave primaria ya que es un código único internacional para cada libro publicado por lo que dicho número jamás se repetirá.

En el caso de las tablas para Órdenes y Clientes crearemos un número consecutivo que identificará de manera única cada una de las filas.

Paso 4: Identificar las relaciones entre tablas

Este paso es de utilidad para asegurarnos que podremos vincular la información de las tablas a través de la relación que existe entre dos de sus campos. Por ejemplo, si para una determinada orden de compra quiero saber el título del libro que ha sido vendido, bastará con relacionar la columna ISBN de la tabla Órdenes con la columna ISBN de la tabla Libros para conocer el título.

De manera similar, si deseo conocer el cliente para una determinada orden de compra, solo deberé seguir la relación indicada por el campo IDCliente en ambas tablas.

Paso 5: Identificar datos repetidos en tablas

Aun cuando pensemos que hemos terminado con el diseño de nuestra base de datos, es importante hacer un breve ejercicio con algunos datos reales para identificar la posible existencia de datos repetidos y tomar las decisiones adecuadas para evitarlos.

Por ejemplo, considera el caso de la tabla Libros donde podríamos tener uno o más títulos escritos por el mismo autor. En ese caso, estaríamos repitiendo el nombre y apellido del autor en varios registros de la tabla.

Para evitar la repetición de datos lo recomendable es crear una nueva tabla que almacene la información de los autores y hacer referencia a dicha tabla a través de su campo llave tal como se muestra en la siguiente imagen:

De esta manera evitaremos la repetición en el nombre de los autores y también se evitará la captura de un sin número de variantes del mismo. Para seguir con nuestro análisis, haremos una captura de datos de prueba de una orden, por ejemplo:

Estos son los datos de la orden de compra número 1 donde el cliente ha comprado 3 libros, siendo dos de esas copias del mismo título. El problema con estos datos es que se

repite el número de orden y eso no sería posible ya que esa es nuestra llave primaria en la tabla y no puede repetirse. Además para cada registro se repite la Fecha, así como las columnas IDCliente y TipoPago. Para resolver este problema debemos crear una tabla adicional que almacenará los libros de cada orden de compra de la siguiente manera:

Es así como el haber identificado datos repetidos en las tablas nos ha hecho tomar la decisión de agregar tablas adicionales a nuestro diseño para minimizar el espacio utilizado por nuestra base de datos que será de gran importancia al momento de que los datos crezcan. De esta manera nuestro diseño final queda de la siguiente manera:

Crear la base de datos en Excel

Una vez que tenemos el diseño de la base de datos podemos crearla fácilmente en Excel siguiendo dos recomendaciones:

Cada tabla del diseño será una tabla de Excel. Colocar cada tabla en su propia hoja de Excel.

El motivo para utilizar tablas de Excel es que existen múltiples beneficios de utilizar tablas entre los cuales están los filtros y el reconocimiento automático de nuevas filas y columnas. Por otro lado, la razón por la cual colocar cada tabla en su propia hoja de Excel es para facilitar el crecimiento de los datos ya que si colocas varias tablas en una

misma hoja probablemente tendrás que moverlas o redefinirlas cada vez que alguna de ellas aumente de tamaño y se sobrepongan entre sí.

Consultar la base de datos en Excel

Una vez que se ha creado la base de datos en Excel y comiencen a crecer los datos, seguramente te encontrarás con la necesidad de relacionar la información de diferentes tablas para obtener datos complementarios. Por ejemplo, considera las siguientes dos tablas:

La Hoja.Ordenes contiene una tabla llamada Ordenes y la Hoja.Clientes contiene la tabla Clientes. Si al consultar la tabla Ordenes deseo conocer el Email del cliente que ha efectuado dicha compra, puedo utilizar funciones de Excel para obtener dicha información. Ya que estoy utilizando tablas de Excel, puedo hacer uso de las referencias estructuradas, por ejemplo:

=BUSCARV(Ordenes[IDCliente], Clientes, 5,FALSO)

Esta fórmula buscará el valor de la columna IDCliente, que pertenece a la tabla Ordenes, dentro de la tabla Clientes y devolverá la columna 5 de esta última tabla. Observa el resultado de la fórmula:

El resultado de la fórmula es correcto ya que la orden número 1 fue hecha por el cliente con un IDCliente igual a 5 y que corresponde a Luis cuyo Email es el mostrado en la celda F2. De esta manera puedes relacionar la información de las diferentes tablas en tu base de datos en Excel, ya sea utilizando la función BUSCARV o la combinación de funciones INDICE/COINCIDIR ya que ambas nos permite crear fórmulas para buscar datos.

Si quieres practicar un poco más con este tipo de fórmulas, descarga el libro de trabajo que contiene el ejemplo que acabamos de realizar. Antes de finalizar este artículo, es necesario hacer algunas aclaraciones importantes sobre las bases de datos en Excel, pero para eso necesito explicar un poco sobre los sistemas de gestión de bases de datos.

Sistemas de gestión de bases de datos (DBMS)

Varias décadas atrás, cuando las computadoras comenzaron a hacerse más rápidas y eficientes en el manejo de información, se hizo necesaria la creación de un sistema que se encargase de las operaciones con los datos. Desde la década de los años sesenta se crearon los primeros sistemas de gestión de bases de datos (DBMS por sus siglas en inglés), cuya finalidad era facilitar el almacenamiento, modificación y extracción de los datos.

El objetivo principal de un DBMS es coordinar y proteger el acceso a la información y dicho objetivo se logra porque los usuarios o aplicaciones solo pueden obtener datos utilizando alguna interfaz o lenguaje de alto nivel como SQL y el sistema de gestión de bases de datos se encargará de devolver la información solicitada ya que el usuario, o aplicación, jamás tiene contacto directo con los datos.

Es muy común que en el campo de la informática se hable de que una base de datos debe tener un DBMS para ser considerada como una verdadera base de datos. Un ejemplo de un DBMS es el producto de Microsoft conocido como SQL Server que es un motor de base de datos diseñado para el ambiente empresarial.

Excel no es un DBMS

Aunque exista una gran cantidad de personas que utilizamos Excel para almacenar información, eso no lo convierte en un sistema de gestión de bases de datos. Excel no tiene un servicio que controle la inserción o eliminación de los datos sino que el mismo usuario puede hacerlo directamente en la aplicación.

Algunas desventajas que tenemos al utilizar Excel para almacenar nuestros datos son las siguientes:

Solo un usuario puede acceder a la información al mismo tiempo. Excel comenzará a ponerse lento cuando la base de datos crezca en gran manera. No es posible establecer un nivel de seguridad avanzado como para proteger ciertos

datos de ciertos usuarios.

Conclusión

La realidad es que hay millones de usuarios de Excel que utilizamos la herramienta como un repositorio de datos y aunque no es formalmente un sistema de gestión de bases de datos, es posible utilizarlo para guardar información útil.

Mi recomendación es que si te encuentras con que alguna de las desventajas de Excel te está causando dolores de cabeza con tu trabajo, entonces considera fuertemente la opción de utilizar algún DBMS como Microsoft SQL Server o Access. Si las capacidades de Excel han sido hasta ahora suficientes para ti, entonces te recomiendo seguir los pasos descritos en este artículo para hacer uso de las mejores prácticas en el diseño y construcción de bases de datos en Excel y hacer más eficiente el trabajo y organización de la información.

Crear una imagen de un rango de celdasExcel nos permite convertir un rango de celdas en una imagen. Esta imagen puede ser totalmente estática o también puede ser una imagen que refleja los cambios de los datos originales.

Imagen estática de un rango

Para crear una fotografía de un rango de celdas debes seleccionar todas las celdas y oprimir la combinación de teclas Ctrl + C para copiarlas al portapapeles. Después debes pulsar el botón Imagen que se encuentra en el menú desplegable del botón Pegar.

El resultado será una imagen que será colocada justo encima del rango de celdas original por lo que debes mover con el ratón la nueva imagen hacia una nueva ubicación. Al seleccionar la imagen se mostrará la ficha contextual  Herramientas de imagen la cual te permitirá aplicar un formato.

La imagen que creamos es estática, es decir, los cambios que hagamos al rango de celdas original no se verán reflejados en la imagen.

Creando una imagen vinculada

Para crear una imagen que se actualice con los datos seguimos un procedimiento similar al anterior pero al momento de pulsar el botón Pegar seleccionamos el comando Imagen vinculada.

Excel pegará la imagen en la hoja y quedará vinculada al rango de celdas original. Si hacemos algún cambio en los datos se verán reflejados dentro de la imagen. De igual manera podremos aplicar el formato que necesitemos, en este ejemplo he colocado la imagen sobre una forma.

Puedes darte cuenta que una imagen está vinculada porque al seleccionarla se mostrará dentro de la barra de fórmulas el rango de celdas al que está vinculada. Si deseas desvincular la imagen será suficiente con borrar el contenido de la barra de fórmulas.

Macro para copiar datos de una hoja a otra en Excel

Cuando necesitas copiar celdas de una hoja a otra en Excel existe la opción de copiar los datos mediante fórmulas, sin embargo dicho método hace un uso extensivo de funciones y además es necesario agregar una hoja intermedia para remover los datos no deseados.

Hoy crearemos una macro para copiar datos de una hoja a otra y podrás ver que este método es mucho más directo y además deja nuestras hojas sin tantas fórmulas. Al desarrollar esta macro nos aseguraremos de que se pueda copiar un rango completo de celdas o que se puedan copiar solamente las celdas visibles después de haber aplicado un filtro. Para nuestro ejemplo trabajaremos con los siguientes datos:

Declarar e inicializar objetos

Comenzaré por agregar un módulo de código y definir la subrutina CopiarCeldas que será la encargada de realizar la copia de los datos y que contendrá los objetos necesarios para referenciar las hojas de origen y destino así como los rangos de celdas correspondientes.  De hecho, para la técnica utilizada en esta macro, solo será necesario indicar la esquina superior izquierda del rango que deseamos copiar.

Al momento de personalizar esta macro para copiar tus propios datos, deberás realizar las siguientes configuraciones:

Configurar los objetos wsOrigen y wsDestino con el nombre de la hoja que contiene los datos originales y la hoja hacia la cual serán copiados.

Configurar la constante celdaOrigen con la referencia de celda ubicada en la esquina superior izquierda del rango de celdas que será copiado. La macro detectará y copiará automáticamente las columnas y filas adyacentes.

Configurar la constante celdaDestino con la referencia de celda en donde se comenzará a pegar el rango copiado.

Las últimas dos instrucciones del código mostrado anteriormente inicializan las variables rngOrigen y rngDestino con la información de los objetos previamente configurados.

Copiar y pegar datos con VBA

Después de haber definido los objetos a utilizar solo nos restará copiar las celdas de la hoja origen y pegarlas en la hoja destino.

La primera línea hace la selección de la celda origen, que es la esquina superior izquierda del rango de datos original. En seguida se aumenta la selección hacia abajo (xlDown), hasta la última fila adyacente y posteriormente hacia la derecha (xlToRight). Pronto verás que esta técnica de selección de celdas será útil para copiar las celdas visibles.

Finalmente se llama el método Copy y en seguida el método PasteSpecial sobre el rango destino. El parámetro xlPasteValues pega solamente los valores, pero si quieres pegar inclusive el formato de las celdas, entonces deberás utilizar el parámetro xlPasteAll.

Macro para copiar datos en Excel

Ahora que ya tienes una idea clara de las instrucciones utilizadas en la elaboración de esta macro, te dejo el código completo de la subrutina CopiarCeldas:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

Sub CopiarCeldas()

'Definir objetos a utilizar

Dim wsOrigen As Excel.Worksheet, _

    wsDestino As Excel.Worksheet, _

    rngOrigen As Excel.Range, _

    rngDestino As Excel.Range

'Indicar las hojas de origen y destino

Set wsOrigen = Worksheets("Origen")

Set wsDestino = Worksheets("Destino")

'Indicar la celda de origen y destino

Const celdaOrigen = "A1"

Const celdaDestino = "A1"

'Inicializar los rangos de origen y destino

Set rngOrigen = wsOrigen.Range(celdaOrigen)

Set rngDestino = wsDestino.Range(celdaDestino)

19

20

21

22

23

24

25

26

27

28

29

30

31

'Seleccionar rango de celdas origen

rngOrigen.Select

Range(Selection, Selection.End(xlDown)).Select

Range(Selection, Selection.End(xlToRight)).Select

Selection.Copy

'Pegar datos en celda destino

rngDestino.PasteSpecial xlPasteValues

Application.CutCopyMode = False

End Sub

Copiar datos de una hoja a otra

Para probar nuestra macro recién creada insertaré un botón de formulario en la hoja que contiene los datos originales y al momento de que Excel muestre el cuadro de diálogo Asignar macro seleccionaré la subrutina CopiarCeldas. Como resultado tendremos un botón que nos permitirá copiar datos de una hoja a otra en Excel.

Para nuestra primera prueba dejaré el código de la macro tal como fue desarrollado, es decir con la celdaOrigen haciendo referencia a la celda A1 y lo mismo para la celdaDestino. Al pulsar el botón copiar, la hoja Destino de nuestro libro se verá de la siguiente manera:

Los datos se han copiado correctamente de la hoja Origen a la hoja Destino. Las celdas no tienen formato porque la instrucción VBA que usamos para pegar hace uso del parámetro xlPasteValues.

Copiar celdas visibles

Para el segundo ejemplo haremos los siguientes ajustes. Primero borraré los datos de la hoja Destino. Después aplicaré un filtro a la columna Categoría en los datos de la hoja Origen.

Finalmente editaré la macro (línea 28) para cambiar el parámetro xlPasteValues por el parámetro xlPasteAll de la siguiente manera:

rngDestino.PasteSpecial xlPasteAll

Con estos ajustes realizados, pulsaremos el botón Copiar ubicado en la hoja Origen y obtendremos el siguiente resultado en la hoja Destino:

En esta ocasión se han copiado solamente las celdas visibles y además tienen el mismo formato que las celdas originales.

Ahora descarga el libro de trabajo y comienza a utilizar la macro para copiar datos de una hoja a otra en Excel. Solo recuerda que debes configurar cada una de las variables en el código VBA de acuerdo al nombre de las hojas de tu libro e indicar las referencias a las celdas de origen y destino.

Controles ActiveX en ExcelLos 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.

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:

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.

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 Excel

En la versión de Excel 2010 algunos controles de formulario han dejado de ser soportados y no podemos utilizarlos más en nuestras hojas. Ese es el caso del control de formulario conocido como campo de texto. Sin embargo, dentro de la lista de controles ActiveX seguimos teniendo disponible el control llamado Cuadro de texto lo cual puede hacer atractiva la opción de utilizar controles ActiveX en Excel en lugar de controles de formulario

Marca de agua en ExcelPara insertar una marca de agua en Excel es necesario utilizar una técnica alternativa ya que la herramienta no tiene una funcionalidad integrada para esta tarea. En esta ocasión mostraré cómo utilizar un texto o una imagen como marca de agua.

Texto como marca de agua en Excel

Una vez que te encuentras en la hoja donde deseas insertar un texto como marca de agua debes hacer clic sobre la ficha Insertar y pulsar el botón Encabez. pie pág. que se encuentra dentro de la ficha Texto.

Se insertará una nueva área tanto para el Encabezado como para el Pie de página dentro de la hoja de Excel. Dentro del Encabezado ingresa el texto que deseas que se muestre como marca de agua. Selecciona el tamaño adecuado de la fuente así como su color.

Finalmente, para mover el texto hacia abajo posiciona el cursor al inicio de la palabra y presiona la tecla Entrar cuantas veces sea necesario para tener el texto a la altura que deseas.

Imagen como marca de agua en Excel

Para colocar una imagen como marca de agua en Excel debemos utilizar el mismo procedimiento anterior dentro del Encabezado de una hoja solo que en esta ocasión en lugar de escribir un texto se insertará una imagen. Una vez que estamos listos para insertar el Encabezado debemos ir a la ficha contextual Herramientas para encabezado y pie de página y pulsar el botón Imagen.

Se mostrará un cuadro de diálogo que nos permitirá buscar la imagen que deseamos insertar y al pulsar el botón Insertar se mostrará el texto &[Imagen]  como parte del Encabezado de la hoja.

Si deseas ver la imagen que has insertado en el encabezado es suficiente con hacer clic sobre cualquier celda fuera del Encabezado y Excel mostrará la imagen.

Si deseas mover la imagen hacia abajo deberás seguir la misma técnica mostrada anteriormente que es posicionarte al principio del texto del Encabezado, que en este caso sería antes del símbolo &, y pulsar la tecla Entrar para mover la imagen hacia abajo.

Si necesitas cambiar el tamaño de la imagen que acabamos de insertar como marca de agua debes seleccionar el botón Dar formato a la imagen que se encuentra en la ficha contextual Herramientas para encabezado y pie de página.

Se mostrará un cuadro de diálogo con las propiedades de la imagen y podrás experimentar con ellas hasta lograr los ajustes necesarios.

Ordenar hojas de Excel alfabéticamenteLas hojas de un libro de Excel nos permiten gestionar adecuadamente nuestros datos, pero si llegamos a tener una cantidad considerable de hojas se dificultará el encontrar algunas de ellas. La solución sería ordenar las hojas de Excel alfabéticamente de manera que se nos facilite su búsqueda.

Para este ejemplo utilizaré un libro de Excel que contiene 183 hojas que han sido insertadas de manera aleatoria, cada una con el nombre de un país, pero con tantas hojas en el libro se ha convertido en un dolor de cabeza el encontrar la hoja correspondiente a cualquier país, así que he tomado la decisión de ordenarlas alfabéticamente.

Por supuesto que tendríamos la opción de ordenar hoja por hoja de manera manual, pero eso nos llevaría un tiempo considerable por lo que en esta ocasión crearemos una macro para ordenar hojas en Excel y automatizar por completo este proceso.

Macro para ordenar hojas de Excel alfabéticamente

La macro es muy sencilla ya que haremos un recorrido de todas las hojas del libro utilizando dos bucles For-Next. El primer bucle nos servirá para tomar hoja por hoja y el segundo bucle para comparar cada una de ellas con el resto de las hojas del libro. Si al hacer la comparación descubrimos que alguna hoja debe ir detrás de la otra haremos el movimiento correspondiente. Para crear esta macro abriré el Editor de Visual Basic y crearé un nuevo módulo (Insertar > Módulo) donde colocaré el siguiente código VBA:

1

2

3

4

5

6

7

8

9

10

11

Sub OrdenarHojas_Ascendente()

For a = 1 To Sheets.Count

    For s = a + 1 To Sheets.Count

        If UCase(Sheets(a).Name) > UCase(Sheets(s).Name) Then

            Sheets(s).Move Before:=Sheets(a)

        End If

    Next s

Next a

End Sub

Observa con detenimiento que este código hace la comparación de dos hojas a la vez con la siguiente línea de código:

If UCase(Sheets(a).Name) > UCase(Sheets(s).Name) Then

Nuestro código utiliza dos variables para recorrer todas las hojas. La variable “a” indica la hoja actual y la variable “s” será la hoja siguiente a la hoja actual. La función Ucase convierte en mayúsculas el nombre de ambas hojas para poder compararlas correctamente y utilizamos el operador mayor que (>) para saber si la hoja actual es mayor que la hoja siguiente.

En programación, una cadena de texto será mayor que la otra si sus letras iniciales aparecen después en el alfabeto.  Lo que evaluamos con nuestra instrucción es si el nombre de la hoja actual aparece después (en el alfabeto) que el nombre de la hoja siguiente. Si dicha condición se cumple, entonces será necesario moverlas de posición para comenzar a ordenar alfabéticamente. Eso es precisamente lo que hacemos con la siguiente línea de nuestro código:

Sheets(s).Move Before:=Sheets(a)

Ordenar hojas de Excel de manera descendente

Antes de probar nuestra macro, crearemos una variante que nos permitirá ordenar las hojas de Excel de manera descendente. Los cambios serán mínimos y los puedes ver en el siguiente código:

1

2

3

4

5

6

7

8

9

10

11

Sub OrdenarHojas_Descendente()

For a = 1 To Sheets.Count

    For s = a + 1 To Sheets.Count

        If UCase(Sheets(a).Name) < UCase(Sheets(s).Name) Then

            Sheets(s).Move Before:=Sheets(a)

        End If

    Next s

Next a

End Sub

Puedes notar que existen solamente dos pequeños cambios, el primero de ellos es evidente ya que he cambiado el nombre de la subrutina para indicar que se trata de un orden descendente. El segundo cambio es el uso del operador menor que (<) en la comparación de los nombres de las hojas. Esta simple modificación nos permitirá cambiar la dirección de ordenación de las hojas para comenzar en la letra Z y terminar en la letra A.

Ordenar hojas de Excel con una macro

Una vez creadas ambas subrutinas probaremos el funcionamiento de cada una de ellas. Para hacerlo puedes hacer clic en la ficha Vista > Macros para

mostrar el cuadro de diálogo Macro el cual enlistará el nombre de las macros recién creadas.

Este cuadro de diálogo también lo puedes abrir desde la ficha Programador > Código > Macros. Una vez abierto el cuadro de diálogo deberás elegir la macro deseada y pulsar el botón Ejecutar. Si elegimos la subrutina OrdenarHojas_Ascendente, el resultado será el siguiente:

Si por el contrario, elegimos la macro llamada OrdenarHojas_Descendente, las hojas del libro se comenzarán a ordenar a partir de la letra Z y en orden descendente tal como lo puedes observar en la siguiente imagen:

De esta manera podemos ordenar hojas de Excel ya sea de manera ascendente o descendente con tan solo ejecutar una macro y así podremos encontrar rápidamente la hoja que necesitemos. Puedes descargar el libro de trabajo para probar el ordenamiento de hojas por ti mismo y además copiar la macro desarrollada en este artículo.

Orden personalizado en ExcelExcel nos permite ordenar los datos en un rango por cualquiera de sus columnas utilizando el comando “Ordenar de A a Z” para aplicar un orden ascendente o el comando “Ordenar de Z a A” para un orden descendente pero existen ocasiones en las que necesitamos aplicar un orden personalizado en Excel.

El comando Orden personalizado en Excel

El comando Orden personalizado se encuentra en la ficha Inicio > Modificar > Ordenar y Filtrar.

Este comando abrirá el cuadro de diálogo Ordenar el cual nos permitirá ordenar los datos por múltiples columnas e inclusive ordenarlas por el color de las celdas. También podremos crear un orden personalizado para cualquiera de las columnas basándonos en una lista personalizada.

Ordenar por varias columnas en Excel

Cuando trabajamos en Excel es muy fácil ordenar una tabla de datos por cualquiera de sus columnas ya sea de manera ascendente o descendente, pero a veces necesitamos ordenar por varias columnas y la única manera de hacerlo es utilizando el cuadro de diálogo Ordenar.

Por ejemplo, considera un rango de datos con información sobre órdenes de compra donde queremos ordenar la información por el nombre de la ciudad y posteriormente por la fecha de la orden. Todo comienza por seleccionar cualquiera de las celdas de datos y pulsando el comando Inicio > Modificar > Ordenar y Filtrar > Orden personalizado.

Una vez que se abre el cuadro de diálogo deberás elegir las opciones para el primer nivel que son tres: Columna, Ordenar según y Criterio de ordenación. Para la opción Columna encontrarás una lista con todos los encabezados de columna de la tabla de datos y solo deberás elegir aquella por la cual se realizará el ordenamiento. Para nuestro ejemplo será la columna Ciudad.

La opción Ordenar según la dejaremos con su valor predeterminado que es Valores y la opciónCriterio de ordenación como A a Z ya que deseamos aplicar un orden ascendente en la columna Ciudad. Para agregar un segundo nivel de ordenación debes pulsar el botón Agregar nivel lo cual nos permitirá indicar la segunda columna por la cual deseamos ordenar los datos.

Observa que al detectar que la columna Fecha orden es de tipo fecha, las opciones para el Criterio de ordenación cambian y podemos seleccionar la opción De más antiguos a más recientes que nos permite ordenar las fechas de manera ascendente. Para aplicar los dos niveles de ordenación definidos debemos pulsar el botón Aceptar y obtendremos un resultado como el siguiente:

Las ciudades han sido ordenadas alfabéticamente y en el caso de la ciudad de Monterrey se ha colocado primero la orden del mes siete y posteriormente la del mes ocho. De esta misma manera podrás agregar tantos niveles de ordenación como necesites y Excel aplicará cada uno de ellos en el orden indicado.

Gestionar los niveles de ordenación

Como ya hemos visto en la sección anterior, el botón Agregar nivel nos permite crear nuevos criterios de ordenación para poder ordenar los datos por varias columnas. Pero, si por alguna razón decidimos eliminar alguno de ellos será suficiente con hacer clic sobre dicho criterio para seleccionarlo y en seguida pulsar el botón Eliminar nivel.

Así mismo el cuadro de diálogo Ordenar muestra el botón llamado Copiar nivel el cual nos permite hacer una copia de cualquiera de los criterios previamente creados. Solo será necesario seleccionar el  criterio deseado y pulsar el botón Copiar nivel para crear una copia y posteriormente modificar sus propiedades.

En el ejemplo mostrado previamente, los criterios de ordenación se aplicaron en el mismo orden en el que aparecen en el listado. Si por alguna razón deseas modificar ese orden podrás hacerlo utilizando los botones de flechas que están al lado del botón Copiar nivel que son conocidos como los botones Subir y Bajar.

En nuestro ejemplo ordenamos los datos por la columna Ciudad y posteriormente por la columnaFecha orden. Si queremos invertir dicho orden, será suficiente con hacer clic en el primer nivel y después hacer clic en el botón Bajar para invertir el orden en que serán aplicados los criterios de ordenación. Observa el resultado de esta acción:

Ahora los datos están ordenados primero por fecha y luego por ciudad. Puedes observar que para aquellas filas que tienen la misma fecha, las ciudades aparecen en orden ascendente (alfabético) por ejemplo, para el día 01/07/2010 aparece primero Barcelona y luego Monterrey o para el día 01/08/2010 se muestra Bogotá y después Monterrey.

Ordenar con lista personalizada en Excel

Otra de las posibilidades de ordenamiento que tenemos con el comando Orden personalizado en Excel es que podemos indicar un criterio basado en una lista personalizada. Con esta opción no estaremos limitados a ordenar siempre de manera ascendente o descendente sino que podremos definir nuestra propia secuencia de ordenamiento.

Si deseamos ordenar nuestros datos de ejemplo por su categoría, pero en un orden especial que no sea alfabético, entonces debemos hacer lo siguiente. Abrimos el cuadro de diálogo Ordenar e indicamos la columna Categoría y en Criterio de ordenación seleccionaremos la opción Lista personalizada.

Inmediatamente se mostrará el cuadro de diálogo Listas personalizadas donde podremos definir el orden específico en que deseamos ordenar la columna. En el panel izquierdo seleccionamos la opción “NUEVA LISTA” y a la derecha ingresaremos las entradas de lista de la siguiente manera:

Si observas con detenimiento verás que los elementos indicados no están en orden alfabético, ni ascendente ni descendente, así que la única manera de

lograr este tipo de ordenamiento es utilizando la lista personalizada. Para terminar pulsamos el botón Agregar para hacer permanente la creación de la lista personalizada y finalmente pulsamos Aceptar para regresar al cuadro de diálogo Ordenar donde observarás que la nueva lista personalizada se muestra como el Criterio de ordenación. Al aplicar este ordenamiento los datos quedarán de la siguiente manera:

En este ejemplo he creado la lista personalizada ingresando cada uno de los elementos dentro del cuadro de texto de entradas de lista, pero si tienes una cantidad considerable de elementos por ingresar, entonces te convendrá crear la lista personalizada desde un rango de celdas. Este método implica crear la lista personalizada antes de intentar ordenar los datos y los pasos para su creación los puedes encontrar en el artículo Crear listas personalizadas en Excel.

Orden personalizado por color

Además de los beneficios mencionados anteriormente, el comando Orden personalizado nos permite ordenar nuestros datos por el color de relleno de la celda, por el color de la fuente e inclusive por el conjunto de iconos de formato condicional desplegado dentro de las celdas.

Para mostrar esta funcionalidad he creado una regla de formato condicional en la columna Total para resaltar aquellas celdas que tienen un valor superior a los $2000 y ahora deseo ordenar los datos para colocar dichas celdas en la parte superior de la tabla. Lo único que debo hacer es que al momento de crear el nivel de ordenación elegiré la opción Color de celda de la siguiente manera:

Excel detectará automáticamente los posibles colores para una misma columna y nos permitirá elegir entre ellos. Después de elegir el color deseado y pulsar el botón Aceptar obtendremos el siguiente resultado:

El funcionamiento será el mismo si deseas ordenar por el color de fuente o también si deseas implementar un ordenamiento por el ícono de la celda.

El orden personalizado en Excel será de mucha ayuda cuando necesites ordenar tus datos por varias columnas, ya sea alfabéticamente o utilizando una lista personalizada para aplicar un criterio de ordenación especial. También nos será de gran ayuda para ordenar  los datos basados en los colores de las celdas o de la fuente.

Agregar texto al resultado de una fórmula en ExcelTodas las celdas en Excel pueden contener alguno de los siguientes datos: un valor numérico, una cadena de texto o una fórmula. En ocasiones creamos fórmulas que nos devuelven valores numéricos y necesitamos agregar texto al resultado de la fórmula para hacer más entendible su significado. Eso lo podrás lograr fácilmente haciendo una concatenación de los valores, pero debes saber que habrá una consecuencia.

Supongamos que tengo una columna con cierta cantidad de días para varias actividades y la celda B12 hace la suma de todos los valores numéricos.

Lo que deseo hacer es que dicha celda muestre el resultado de la suma y además la palabra “días”. A continuación analizaremos las opciones que tenemos para hacer esto.

Utilizar la función CONCATENAR

La función CONCATENAR nos permite unir dos o más valores de una celda y como resultado obtener una nueva cadena de texto. Esta función acepta hasta 255 valores, así que no tendremos problema alguno en utilizarla para nuestro ejemplo. La fórmula a utilizar será la siguiente:

=CONCATENAR(SUMA(B2:B11), " días")

El primer valor a concatenar será la fórmula original y el segundo argumento de la función será precisamente la cadena de texto “ días” considerando un espacio en blanco para marcar la separación entre el resultado de la función SUMA y la palabra días. Observa el resultado de esta fórmula:

Utilizar el operador de concatenación

Es posible obtener el mismo resultado anterior utilizando uno de los operadores en Excel, que es el operador de concatenación y que se representa con el símbolo &. De esta manera, en lugar de utilizar la función CONCATENAR, podemos utilizar la siguiente fórmula:

=SUMA(B2:B11) & " días"

Observa que el resultado de esta fórmula será idéntico a nuestro ejemplo anterior:

Sin importar cuál de los dos métodos anteriores utilices tendremos la misma consecuencia que explicaré a continuación. El resultado original de la función SUMA era un valor numérico pero el resultado de nuestras fórmulas es una cadena de texto. Esto implica que el valor contenido en la celda B12 ya no podrá utilizarse en otro cálculo numérico porque ahora es simplemente texto.

El resultado de las dos fórmulas anteriores en la celda B12 se ha alineado a la izquierda indicando que es una cadena de texto. En la siguiente imagen puedes observar cómo al intentar sumar el número uno al resultado de la celda B12 obtendré el error #¡VALOR! ya que no se pueden realizar cálculos con cadenas de texto:

Si no te interesa mantener el valor numérico del resultado, entonces cualquiera de los métodos anteriores será suficiente para mostrar el valor deseado dentro de la celda, pero si quieres utilizar ese resultado en otros cálculos, tendrás que utilizar un método alterno.

Mantener el valor numérico del resultado

La única manera de mantener el valor numérico de una celda aun cuando se despliegue un texto junto al resultado de su fórmulaes utilizar el formato personalizado de celdas. Sin utilizar fórmulas adicionales, más que la función SUMA original, cambiaré el formato de la celda B12 de la siguiente manera (Clic derecho sobre la celda > Formato de celdas):

El formato personalizado # “ días” mostrará esta última palabra dentro de la celda sin afectar el valor numérico de la misma. Observa el resultado de este cambio en el formato de la celda:

Para comprobar que la celda sigue conservando su valor numérico haré la misma prueba anterior al sumar el número uno al resultado de la celda B12 y obtendré el resultado correcto:

Agregar texto a una fecha en Excel

Los métodos mostrados anteriormente funcionan también para las fechas ya que las fechas en Excel son valores numéricos pero debes saber que si concatenas una celda que contiene una fecha con un texto obtendrás el valor numérico de la fecha tal como se puede observar en la siguiente imagen:

Para resolver este problema deberás utilizar la función TEXTO que nos ayudará a aplicar el formato adecuado a la fecha al momento de hacer la concatenación. Por ejemplo:

Si quieres conocer todo el detalle de ese tipo de fórmulas consulta el artículo: Convertir fecha a texto en Excel. Sin embargo, tal como en los primeros ejemplos de este artículo, existe la solución con fórmulas pero también una solución con el formato personalizado de celdas y en este caso podríamos aplicar un formato a la celda B12 de la siguiente manera:

Al aplicar este formato personalizado seguiremos teniendo el valor numérico de la celda lo cual hará que se muestre en pantalla un formato de fecha identificable para los usuarios.

La celda mantiene el valor de la fecha y lo puedes comprobar porque la barra de fórmulas solamente muestra la fecha de la celda y no muestra el texto, el cual es solo parte del formato de la celda.

Es así como puedes agregar texto al resultado de una fórmula en Excel, ya sea utilizando el método de la función CONCATENAR o del operador de concatenación (&) sabiendo que el resultado de ambas fórmulas será una cadena de texto. También puedes utilizar el método del formato personalizado el cual mantendrá el valor numérico de la celda.

Cómo cargar datos a un ComboBox en ExcelEl Cuadro combinado, también conocido como ComboBox, es un control ActiveX que nos permite crear una lista desplegable de la cual podremos seleccionar alguno de sus elementos. Pero antes de poder utilizar este control, es necesario saber cómo cargar datos a un ComboBox a partir de una lista de valores en una hoja de Excel.

Para este ejemplo consideraré dos posibles escenarios. El primero supone que el control ha sido agregado directamente a la hoja de Excel y el segundo escenario supondrá que el ComboBox se ha insertado en un Formulario VBA. Para ambos casos tomaré los valores de la columna A y los insertaré como los elementos del cuadro combinado.

Cargar datos al ComboBox en una hoja de Excel

El caso más simple es cuando has agregado el Cuadro combinado directamente a la hoja de Excel ya que para agregar sus elementos bastará con hacer clic derecho sobre el control ActiveX y seleccionar la opción Propiedades. Se mostrará el listado de propiedades y en la propiedadListFillRange deberás indicar el rango donde se encuentran los datos.

Después de aceptar los cambios tendremos listo nuestro ComboBox:

Cargar datos al ComboBox en un formulario VBA

En el caso donde el control está dentro de un formulario VBA, es necesario indicar por código los elementos que serán agregados al ComboBox de la siguiente manera:

1

2

3

Private Sub UserForm_Initialize()

Dim rango, celda As Range

4

5

6

7

8

9

10

Set rango = Worksheets("Ejemplo1").Range("A1:A7")

For Each celda In rango

    ComboBox1.AddItem celda.Value

Next celda

End Sub

Con este código recorremos todas las celdas del rango A1:A7 y por cada celda agregamos un nuevo elemento al ComboBox. Observa que el código está dentro del evento UserForm_Initializelo que ocasionará que la carga de datos se haga en el momento en que se inicialice el formulario.

Si la lista de elementos de la columna A fuera fija, con cualquiera de los ejemplos anteriores será suficiente para cargar datos al ComboBox, pero si dicha lista crece continuamente y necesitas que los nuevos elementos sean considerados dentro del ComboBox, entonces debemos cambiar un poco el enfoque de la solución para considerar el uso de un nombre de rango dinámico.

Rango dinámico para cargar datos al ComboBox

Para crear un rango dinámico es necesario utilizar la función DESREF y la función CONTARA la cual nos ayudará a conocer las celdas con valores dentro de la columna que contiene los datos. Para crear el nombre debemos ir a la ficha Fórmulas > Nombres definidos > Asignar nombre.

Creamos el nombre MiLista que nos servirá para hacer referencia a este rango dinámico de celdas. En el último cuadro de texto, donde generalmente colocamos una referencia a un rango de celdas, colocaremos la siguiente fórmula:

=DESREF(Ejemplo2!$A$1,0,0,CONTARA(Ejemplo2!$A:$A))

Esta fórmula devolverá una referencia a un rango que inicia en la celda A1 (de la hoja Ejemplo2) y termina N filas por debajo. La cantidad de filas a desplazarse hacia abajo será el resultado de la función CONTARA que cuenta las celdas no vacías de la columna A.

De esta manera queda creado nuestro nombre de rango dinámico que utilizaremos para cargar datos al ComboBox. Si quieres saber un poco más sobre la creación de este tipo de nombres te recomiendo leer el artículo Nombres de rango dinámicos. En las siguientes secciones haremos uso de este rango dinámico recién creado.

ComboBox en hoja de Excel con rango dinámico

Si nuestro ComboBox se encuentra en la hoja de Excel, podemos hacer referencia al rango dinámico abriendo la ventana de propiedades y para la propiedad ListFillRange colocar el nombre del rango dinámico:

Sin embargo, con esta adecuación no es suficiente para que la actualización del ComboBox sea automática. Si dejamos las cosas así, los datos se refrescarán solamente hasta que cerremos y volvamos a abrir el libro, así que lo mejor será agregar código adicional a nuestra hoja para asegurarnos de que la actualización se hace en cuanto modificamos los elementos de la columna A.

1

2

3

4

5

6

7

8

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A:A")) Is Nothing Then

        With ComboBox1

            .Value = ""

            .ListFillRange = "=MiLista"

        End With

    End If

End Sub

Con este código nos aseguramos de que, al momento de agregar o eliminar un elemento de la columna A, el ComboBox refrescará automáticamente sus elementos.

ComboBox en Formulario VBA con rango dinámico

Para hacer que un ComboBox que está ubicado dentro de un formulario cargue sus datos desde el rango dinámico, debemos utilizar el siguiente código:

1

2

3

4

5

6

7

8

9

10

Private Sub UserForm_Initialize()

Dim rango, celda As Range

Set rango = Range("MiLista")

For Each celda In rango

    ComboBox1.AddItem celda.Value

Next celda

End Sub

La única diferencia con el primer formulario es que el rango indicado es precisamente el nombre del rango dinámico. Al momento de abrir el formulario se cargará el ComboBox con todos los elementos de la columna A.

En este caso no es necesario agregar código adicional ya que cada vez que abramos el formulario se actualizarán los elementos del ComboBox.

Solo resta que descargues el libro de trabajo para seguir probando con los ejemplos mostrados en este artículo. En el archivo descargado encontrarás dos hojas, la primera contiene los primeros dos ejemplos del artículo y la segunda hoja contiene los ejemplos que hacen uso del rango dinámico.

Color intermitente en celda de ExcelUna manera muy fácil de llamar la atención hacia una celda específica de nuestra hoja es estableciendo un color de fondo para la misma, pero si eso no fuera suficiente, podemos crear un efecto de color intermitente en la celda de manera que sea casi inevitable dirigir la mirada hacia ella.

Es muy probable que existan diferentes métodos para desarrollar este tipo de solución, y sería difícil explicarlos todos, así que en esta ocasión me enfocaré en crear el efecto de celda parpadeante utilizando los estilos de celda y un poco de programación en VBA. Empecemos por explicar un poco sobre dichos estilos de celda ya que será el primer paso en el desarrollo de nuestra solución.

Paso 1: Crear estilo de celda

Los estilos de celda los encontramos en la ficha Inicio dentro del grupo Estilos. Si haces clic en el botón Estilos de celda podrás ver una gama de estilos previamente definidos en Excel.

Tenemos la opción de utilizar alguno de los estilos definidos o si lo deseas, puedes crear tu propio estilo haciendo clic en la opción Nuevo estilo de celda que abrirá un cuadro de diálogo que nos permitirá configurar cada uno de los aspectos del nuevo estilo.

Cada una de las cajas de selección mostradas corresponde a una de las pestañas del cuadro de diálogo Formato de celdas el cual se abrirá al hacer clic en el botón Aplicar formato y nos permitirá indicar los valores específicos para el nuevo estilo. Sin embargo, para nuestro ejemplo no crearé un nuevo estilo, si no que crearé un estilo basado en otro ya existente y para eso haré clic derecho sobre el estilo llamado “Énfasis2” que se encuentra dentro de los estilos de celda predefinidos y seleccionaré la opción Duplicar dentro del menú emergente. Se mostrará el cuadro de diálogoEstilo y cambiaré el Nombre del estilo por “Intermitente”.

Es importante recordar este nuevo nombre de estilo porque haremos referencia a él dentro del código VBA. Finalmente debemos aplicar este nuevo estilo a la celda que tendrá el efecto de intermitencia.

Paso 2: Crear las macros

Sin más preámbulos, te mostraré el código de las subrutinas que nos ayudarán a aplicar un color intermitente en la celda de Excel. Posteriormente haré una explicación detallada del código.

1 Dim Siguiente As Date

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

Dim EstiloColor As Double

Dim Iniciado As Boolean

Const EstiloNombre As String = "Intermitente"

Const EstiloColor2 As Double = 0

Sub Iniciar()

    Siguiente = Now + TimeValue("00:00:01")

    IniciarColor

    With ActiveWorkbook.Styles(EstiloNombre)

        If .Interior.Color = EstiloColor Then

            .Interior.Color = EstiloColor2

        Else

            .Interior.Color = EstiloColor

        End If

    End With

    Application.OnTime Siguiente, "Iniciar"

End Sub

Private Sub IniciarColor()

    If Iniciado = False Then

        EstiloColor = ActiveWorkbook.Styles(EstiloNombre).Interior.Color

        Iniciado = True

    End If

29

30

31

32

33

34

35

36

37

38

End Sub

Sub Detener()

    Application.OnTime Siguiente, "Iniciar", schedule:=False

    ActiveWorkbook.Styles(EstiloNombre).Interior.Color = EstiloColor

End Sub

Primero explicaré el objetivo de las variables y constantes definidas en las primeras cinco líneas de código:

Siguiente: Define el intervalo del siguiente parpadeo de la celda. Es del tipo Date porque almacenaré un valor de tiempo determinado entre parpadeos.

EstiloColor: Guardará el color de fondo del estilo de celda que hayamos definido para dar el efecto de intermitencia.

Iniciado: Esta variable me ayudará a saber si ya se ha inicializado el valor de la variableEstiloColor. En un principio el valor de esta variable será falso, pero en cuanto se asigne un valor a la variable EstiloColor, la variable Iniciado tendrá un valor verdadero.

EstiloNombre: Esta constante será el nombre del estilo que creamos en al Paso 1 y que para nuestro ejemplo será el estilo “Intermitente”.

EstiloColor2: Será el color con el que se alternará el color de fondo original del estilo de celda que para nuestro ejemplo será el color negro (0).

En el código mostrado previamente están definidas tres subrutinas y una de ellas es privada, lo que significa que no será mostrada en la lista de macros, así que comenzaré explicando la subrutinaIniciar que es donde comienza todo. La primera línea de código establece el valor de la variableSiguiente con el valor del momento actual (Now) más 1 segundo.  Esto quiere decir que entre un cambio de color y el otro habrá 1 segundo de diferencia.

La segunda instrucción es el llamado a la subrutina privada IniciarColor, así que analicemos de una vez el código de dicha subrutina. Lo único que hacemos dentro de IniciarColor es evaluar el valor de la variable Iniciado para saber si necesitamos establecer el valor de la variable EstiloColor con el valor del color original del estilo de celda. Este procedimiento se ejecutará solo la primera vez ya que de inmediato establecemos el valor de la variable Iniciado igual a True y por lo tanto la próxima vez que se llame esta subrutina la evaluación resultará falsa.

Ahora que la variable EstiloColor ya tiene el color original del estilo de celda regresaremos a la subrutina Iniciar y podemos ver que la siguiente instrucción es precisamente la evaluación de la propiedad Color del estilo de celda. Si el valor del color de fondo es igual al color original, entonces lo cambiaré por el color definido en la variable EstiloColor2, de lo contrario regresamos al color original. Esta alternancia en el valor de la propiedad Interior.Color es precisamente lo que dará el efecto de color intermitente a la celda de Excel.

La subrutina Iniciar finaliza con la instrucción Application.OnTime que establece la nueva ejecución de la subrutina en el intervalo definido por la variable Siguiente. Puedes ver que hay una instrucción similar dentro de la subrutina Detener, con la diferencia de que se agrega el argumentoschedule:=False para detener la ejecución cíclica de la subrutina Iniciar.

Es así como la subrutina Detener para por completo el parpadeo de la celda y finalmente restablece el color original del estilo de celda. Ahora que tenemos una mejor idea de lo que hacen las subrutinas, debemos dar el paso final para activar el color intermitente en la celda.

Color intermitente en una celda de Excel

Tal como lo sugerí en el primer paso, debemos aplicar el estilo de celda “Intermitente” a aquella celda que deseamos dar el efecto de parpadeo. Una vez hecho esto, solo restará ir a la ficha Vista, pulsar el botón Macros y finalmente elegir la macro Iniciar:

Al pulsar el botón Ejecutar la celda, o celdas, que tengan el estilo “Intermitente” comenzarán a parpadear, es decir a alternar su color de fondo tal como se observa en la siguiente animación:

Para detener la ejecución de la macro debemos ir de nueva cuenta a la ficha Vista, pulsar el botónMacros, seleccionar la macro Detener y pulsar el botón Ejecutar.

Observaciones importantes

En este ejemplo he elegido los colores rojo y negro como los colores alternantes. El color rojo fue elegido gráficamente al momento de crear el estilo de celda, pero el valor numérico del color negro fue colocado directamente en el código. Si quieres alternar con un color diferente al negro, será indispensable conocer su valor numérico y la única manera hacerlo en Excel es a través de código VBA.

Te recomiendo consultar el artículo Operaciones con colores en Excel donde encontrarás la función OBTENERCOLOR que te ayudará a obtener el valor numérico de cualquier color de fondo de una celda. Es muy importante recordar que los colores que podemos controlar con los métodos mostrados son aquellos que han sido aplicados con las herramientas de estilo de Excel y no funcionará con los colores aplicados por formato condicional ya que dicha información de color se guarda en propiedades muy diferentes.

Otra cosa que vale la pena mencionar es que en este ejemplo no se ha modificado el color de la fuente, pero puede llegar a ser necesario si dicho color no contrasta adecuadamente con el color de fondo que hayas elegido. En ese caso será necesario modificar también el color de la fuente establecido en el estilo de celda el cual estará guardado en la propiedad Font.Color.

Para no dejar dudas a esta última observación, en el archivo de descarga encontrarás un segundo libro de trabajo donde agrego las instrucciones VBA para cambiar también el color de la fuente de la celda que está parpadeando. Solo resta que descargues el libro de trabajo y compruebes por ti mismo los métodos que he mostrado para aplicar un color intermitente a una celda de Excel.

Conversión de unidades en ExcelLas unidades de medida han estado presentes a lo largo de la historia de la humanidad y son adoptadas por convención para medir una determinada magnitud física. Sin embargo, hoy en día no todos los países del mundo han adoptado el mismo sistema de medición por lo que se hace necesario recurrir a la conversión de unidades para encontrar los valores equivalentes entre ambos sistemas.

Cuando hablamos de la conversión de unidades nos referimos primordialmente a la conversión entre el Sistema Internacional de Unidades y el Sistema anglosajón de unidades que son los dos sistemas más reconocidos en el mundo, aunque este último es utilizado de manera oficial solamente por tres países: Estados Unidos, Liberia y Birmania.

Conversión de unidades en Excel

Debido al uso tan frecuente de las unidades de medida y la necesidad de hacer conversiones entre los diferentes sistemas, Excel ha incluido la función CONVERTIR desde la versión de Excel 2007. Los argumentos de esta función son los siguientes:

Número: El número que se va a convertir. Desde_unidad: La unidad de medida del Número que se va a convertir. A_unidad: Las unidades del resultado devuelto por la función.

De esta manera, si queremos convertir 95 grados Fahrenheit a grados Centígrados podemos utilizar la siguiente fórmula:

=CONVERTIR(95,"F","C")

Al utilizar esta fórmula en nuestra hoja de cálculo obtendremos el resultado correcto, que son 35 grados Centígrados:

Como puedes notar, el segundo y tercer argumento de la función CONVERTIR utilizan abreviaturas para indicar las unidades de medida de origen y destino, así que es de suma importancia conocer dichas abreviaturas que nos permitirán convertir unidades en Excel y para eso dejaré una lista detallada en la última sección de este artículo.

Planilla de conversión de unidades en Excel

Para facilitar la conversión de unidades en Excel he creado una planilla que contiene todos los tipos de medidas entre los cuales se puede realizar una conversión utilizando la función CONVERTIR. La manera de utilizarla es muy sencilla, por ejemplo, si deseo convertir 100 gramos a libras el primer paso será colocar el valor 100 en la celda que se encuentra en la intersección de la fila Gramo y la columna Gramo:

Como resultado, todas las celdas de la fila Gramo mostrarán el valor equivalente así que el segundo paso será obtener el valor que se encuentra en la columna Libra el cual me indicará que 100 gramos es equivalente a 0.22

libras. En este ejemplo he utilizado la tabla de Peso y masapero en la planilla encontrarás 10 tablas con las diferentes categorías de unidades de medida que podemos utilizar con la función CONVERTIR.

Descarga la planilla de conversión de unidades en Excel.

Unidades de medida en Excel

A continuación encontrarás el listado completo de unidades junto con sus abreviaturas tal como las debemos utilizar en la función CONVERTIR en Excel:

Unidades de peso y masa

“g” – Gramo “sg” – Slug “lbm” – Libra (masa) “u” – U (unidad de masa atómica) “ozm” – Onza (masa)

Unidades de longitud

“m” – Metro “mi” – Milla terrestre “Nmi” – Milla náutica “in” – Pulgada “ft” – Pie “yd” – Yarda “ang” – Angstrom “pica” – Pica

Unidades de tiempo

“yr” – Año “day” – Día “hr” – Hora “mn” – Minuto “sec” – Segundo

Unidades de presión

“Pa” – Pascal “atm” – Atmósfera “mmHg” – mm de mercurio (torr)

Unidades de fuerza

“N” – Newton “din” – Dina “lbf” – Libra fuerza

Unidades de energía

“J” – Julio “e” – Ergio “cal” – Caloría IT “eV” – Electronvoltio “HPh” – Caballos de fuerza-hora “Wh” – Vatio-hora “flb” – Libra-pie “BTU” – BTU (Unidad térmica inglesa) “c” – Caloría termodinámica

Unidades de potencia

“HP” – Caballos “W” – Vatio

Unidades de magnetismo

“T” – Tesla “ga” – Gauss

Unidades de temperatura

“C” – Grado Celsius “F” – Grado Fahrenheit “K” – Kelvin

Unidades de capacidad

“tsp” – Cucharadita “tbs” – Cucharada “oz” – Onza líquida “cup” – Taza “pt” – Pinta de EE.UU. “uk_pt” – Pinta del Reino Unido “qt” – Cuarto de galón “gal” – Galón “l” – Litro

Cómo ejecutar una macro al abrir ExcelSi quieres ejecutar ciertas acciones automáticamente al momento de abrir Excel, es posible grabar o escribir una macro que sea ejecutada en el momento de abrir un libro. En esta ocasión te mostraré dos alternativas para ejecutar una macro al abrir Excel, uno de los métodos es ideal para aquellos que apenas comienzan con la programación de macros y el otro método para aquellos usuarios que son un poco más experimentados.

El primer método que mostraré hará uso de la grabadora de macros. Y como probablemente lo sabes, una vez que activamos la grabación, todas las acciones realizadas en la interfaz de Excel serán grabadas hasta el momento en que detengamos dicha grabación. El truco para nuestro primer ejemplo radica en el momento en que guardamos la macro.

La subrutina Auto_Open en Excel

Para beneficio de todos los lectores, en especial para aquellos que no tienen experiencia con macros, haremos este ejercicio paso a paso. Para comenzar con la grabación de una macro debes pulsar el botón Grabar que se encuentra en el extremo inferior izquierdo de la barra de estado de Excel:

Esto abrirá el cuadro de diálogo Grabar macro, donde tendremos que nombrar forzosamente la macro como Auto_Open.

El nombre de la macro deberá ser Auto_Open porque esa es la manera en que Excel sabrá que existe código que deberá ejecutar al abrirse. Si no colocas este nombre a la macro, entonces no sucederá nada al abrir el libro de Excel. Para comenzar la grabación de la macro deberás hacer clic en el botón Aceptar.

Para este primer ejemplo nuestro objetivo será colocar en la celda B1 la fecha y hora actual de manera que al momento de abrir el libro de Excel quede

registrado el momento en que se abrió el archivo. Para lograr esto realizaré las siguientes acciones (recuerda que estoy grabando la macro):

1. Seleccionar la celda B12. En la barra de fórmulas insertar la fórmula =AHORA() y pulsar Entrar.3. Seleccionar la celda B1 y copiar su contenido (CTRL+C).4. Hacer clic derecho sobre la celda B1 y seleccionar Opciones de pegado >

Valores.5. Pulsar la tecla Esc.6. Pulsar el botón Detener.

El botón Detener lo encontrarás en la barra de estado, en el mismo lugar donde inicialmente se encontraba el botón Grabar. Con esto habremos terminado de grabar la macro y tendrás un resultado como el siguiente:

La macro que acabamos de grabar hace lo siguiente: Primero inserta la función AHORA en la celda B1 para obtener la fecha y hora actuales. En seguida copia el valor de la celda B1 y lo vuelve a pegar sobre sí misma como un valor, de esta manera la celda ya no tendrá una fórmula sino un valor de fecha y hora que permanecerá estático mientras trabajamos con el libro.

Finalmente debemos guardar el libro asegurándonos de guardarlo como un Libro de Excel habilitado para macros. La próxima vez que abras este libro, el valor de la celda B2 se actualizará automáticamente con la fecha y hora actuales. Es así como podrás ejecutar una macro al abrir Excel utilizando la subrutina Auto_Open.

Observaciones del primer método

Una vez que has creado la macro llamada Auto_Open podrás ver su código VBA desde la ficha Vista > Macros y dentro del cuadro de diálogo Macro deberás seleccionarla y pulsar el botónModificar.

Esto abrirá el Editor de Visual Basic y podrás modificar el código de la subrutina si así lo deseas, aunque si eres un usuario principiante esto te parecerá un tanto intimidante, pero aun así te recomiendo revisar el código de la macro recién creada ya que es un buen método para aprender más sobre programación en VBA.

Si quieres sobrescribir la subrutina Auto_Open sin editar el código VBA, puedes hacerlo con la Grabadora de macros. Lo único que debes hacer es volver a grabar la macro y colocar de nueva cuenta el nombre Auto_Open y Excel enviará una notificación para saber si deseas sobrescribirla.

El evento Workbook_Open en Excel

Con el método anterior estaremos siempre obligados a tener una subrutina llamada Auto_Openpero si eres un usuario más experimentado y deseas un poco más de control para ejecutar una macro al abrir Excel te interesará el segundo método que describiré a continuación.

Comenzaremos por abrir el Editor de Visual Basic desde la ficha Programador o con el atajo de teclado ALT+F11 . Posteriormente vamos al

menú Insertar > Módulo para crear un nuevo módulo de código donde colocaremos la subrutina que se ejecutará al abrir Excel. Para nuestro ejemplo utilizaré el siguiente código:

1

2

3

4

5

Sub Iniciar()

MsgBox "Bienvenido a Excel"

End Sub

El nombre de la subrutina no es relevante y para el ejemplo la he nombrado como Iniciar pero podrás utilizar cualquier nombre. Dentro de esta subrutina puedo colocar cualquier código VBA que deseo que se ejecute al abrirse Excel, pero por simplicidad del ejemplo solamente abriré un cuadro de diálogo con un mensaje de bienvenida. En este momento deberás tener una vista del Editor de Visual Basic como la siguiente:

El siguiente paso es hacer clic sobre el objeto ThisWorkbook que se encuentra enlistado en el panel izquierdo y posteriormente debemos seleccionar de las listas desplegables las opcionesWorkbook y Open tal como se muestra en la siguiente imagen:

Dentro del evento Workbook_Open deberás colocar el nombre de la subrutina previamente creada. Cada vez que un libro de Excel es abierto, se revisa automáticamente el evento Workbook_Open y se ejecuta cualquier código especificado dentro del mismo. Lo único que resta es guardar el código y el libro del ejemplo. Para probar nuestro código deberás cerrar el libro y volver a abrirlo para entonces confirmar que se mostrará el mensaje previamente programado.

Ejecutar una macro al abrir Excel

Con los dos ejemplos anteriores tendrás herramientas suficientes para poder ejecutar una macro al abrir Excel. Ya sea que quieras utilizar la subrutina Auto_Open o que desees crear tu propia subrutina para posteriormente llamarla desde el evento Workbook_Open.

En ambos ejemplos anteriores, la macro se ejecutará el abrir específicamente el libro para el cual ha sido creada. Si quieres que una macro se ejecute al momento de abrir cualquier libro de Excel en tu equipo, entonces tendrás que colocar el código dentro del Libro de macros personal pero este procedimiento solamente lo recomiendo para aquellos que tienen amplia experiencia en el tema ya que estarán modificando el comportamiento de Excel para todos los libros.

Un último ejemplo

En el archivo que podrás descargar con los ejemplos anteriores he incluido un tercer libro que contiene una macro que autoincrementa el valor de una celda cada vez que se abre dicho libro.

Este tercer ejemplo hace uso del evento Workbook_Open por lo que es muy similar al segundo ejemplo que hicimos, con la única diferencia de que he editado el código de la subrutina Iniciar de la siguiente manera:

1

2

3

4

5

6

7

8

Sub Iniciar()

Worksheets("Hoja1").Range("B1").Value = _

    Worksheets("Hoja1").Range("B1").Value + 1

ThisWorkbook.Save

End Sub

El código se encarga de guardar el libro después de incrementar el valor de la celda. Tal vez una mejora a dicho ejemplo sería proteger la celda de manera que nadie pueda modificar su valor y por lo tanto siempre conocer las veces que ha sido abierto el libro, pero eso lo dejo a tu criterio e imaginación.

Solo resta descargar el archivo comprimido que contiene todos los ejemplos mostrados en este artículo y que comiences a crear macros que se ejecuten al abrir un libro de Excel.

Referencias en ExcelLas referencias en Excel son parte fundamental de la hoja de cálculo ya que a través de ellas podemos identificar cada una de las celdas de un libro de trabajo y de esa manera acceder al valor contenido en cualquiera de ellas.

El objetivo de este artículo es aclarar la mayor cantidad de dudas sobre el tema de referencias en Excel y será útil para aquellas personas que comienzan a utilizar la herramienta pero también para aquellos que ya tienen un poco de tiempo utilizando la hoja de cálculo y no han escuchado sobre temas como las referencias 3D o el estilo de referencia F1C1. Los temas de los que hablaré en este artículo son los siguientes:

¿Qué son las referencias en Excel?

Tipos de referencias en Excel Referencias relativas en Excel Referencias absolutas en Excel Referencias mixtas en Excel Cómo cambiar entre tipos de referencia Referencias a rangos de celdas Referencias a celdas en otra hoja Referencias a celdas en otro libro Referencias 3D El estilo de referencia F1C1

Ya que todos los valores de una hoja de cálculo están almacenados en las celdas, es de suma importancia para los usuarios de Excel conocer la manera en que deberán acceder a dichos valores utilizando las referencias. Sin más preámbulo, comenzaremos con este estudio de las referencias en Excel.

¿Qué son las referencias en Excel?

Todo usuario de Excel debe saber que todas las hojas están divididas en columnas y filas. Esta división se hace evidente en la interfaz gráfica de Excel de la siguiente manera:

Por debajo de la barra de fórmulas se encuentran los encabezados de columna identificados por una letra.

En el extremo izquierdo están los encabezados de fila que muestran un número. En el área de trabajo tenemos una cuadrícula que delimita el área de cada celda de la

hoja y donde se puede observar que cada una de ellas pertenece a una determinada columna y fila.

En la imagen anterior, la celda seleccionada (celda activa)es la celda que se encuentra bajo la columna B y en la línea 3 y por lo tanto su dirección dentro de la hoja será B3. La referencia de una celda siempre constará de dos partes: la primera parte indicará la letra (o letras) de la columna a la que pertenece y la segunda parte indicará su número de fila.

Excel siempre nos ayudará a conocer fácilmente la referencia de la celda activa. En primer lugar, el encabezado de la columna y fila de la celda seleccionada estarán resaltados en un color diferente al resto de los encabezados. Además el Cuadro de nombres siempre mostrará la referencia de la celda activa tal como lo puedes observar en la siguiente imagen:

En conclusión, la referencia de una celda nos indica su ubicación dentro de una hoja, es decir, nos ayuda a conocer rápidamente la columna y fila a las que pertenece.

Tipos de referencias en Excel

Una referencia siempre será la dirección de una celda dentro de una hoja y siempre estará formada por la columna y fila en donde se ubica la celda. Así que, cuando hablamos de tipos de referencias en Excel estamos hablando sobre los diferentes tipos de comportamiento que pueden tener las referencias al momento de ser copiadas a otras celdas.

Es muy fácil copiar fórmulas en Excel, pero ¿qué sucede con las referencias de dicha fórmula al momento de hacer la copia? Es ahí en donde su comportamiento dependerá del tipo de referencia que se haya utilizado y analizaremos las alternativas que tenemos en las próximas tres secciones.

Referencias relativas en Excel

De manera predeterminada, las referencias en Excel son relativas. El término relativo significa que al momento de copiar una fórmula, Excel modificará las referencias en relación a la nueva posición donde se está haciendo la copia de la fórmula. Por ejemplo, supongamos que en la celda C1 tenemos la fórmula =A1+B1. En la siguiente imagen puedes observar el resultado de esta operación si tenemos los valores 5 y 3 en dichas celdas:

Si ahora copio (Ctrl+C) la fórmula de la celda C1 y la pego (Ctrl+V) en la celda C3, obtendré el siguiente resultado:

La fórmula que ha sido copiada a la celda C3 nos devolverá como resultado el valor cero. Al revisar la fórmula de dicha celda nos daremos cuenta que eso se debe a que Excel ha modificado automáticamente las referencias de la fórmula. En lugar de la celda A1 ahora tenemos la celda A3, y en lugar de la celda C1 que teníamos originalmente, ahora tenemos la celda C3.

En este ejemplo hemos copiado la fórmula hacia una celda de la misma columna, pero dos filas por debajo y eso es lo que indicará a Excel el ajuste a realizar en las referencias. Ya que no hubo cambio en la columna, ésta permanecerá igual en la nueva

fórmula, pero ya que copiamos una celda que está dos filas hacia abajo, Excel realizará dicho ajuste sumando la misma cantidad de filas a las referencias. Ahora observa lo que sucede si vuelvo a copiar la fórmula de la celda C1 y la pego en la celda F2.

En esta ocasión la nueva celda se encuentra una fila hacia abajo y tres columnas a la derecha de la celda original y por lo tanto las referencias se adecúan a tal movimiento añadiendo tres columnas y una fila a cada referencia de la fórmula copiada.

Las referencias relativas son muy útiles cuando necesitamos aplicar una misma fórmula sobre un rango de datos. Por ejemplo, en la siguiente imagen puedes ver una hoja donde las columnas A y B tienen valores numéricos y ahora necesito colocar la multiplicación de ambos valores en la columna C. Para hacerlo rápidamente, coloco la fórmula =A1*B1 en la celda C1 y posteriormente copio dicha fórmula hacia debajo:

De esta manera obtendremos automáticamente el resultado correcto para cada fila ya que Excel modifica las referencias en cada fórmula mientras es copiada hacia abajo.

Referencias absolutas en Excel

Hay ocasiones en las que necesitamos “fijar” la referencia a una celda de manera que permanezca igual aún después de ser copiada. Si queremos impedir que Excel modifique las referencias de una celda al momento de copiar la fórmula, entonces debemos convertir una referencia relativa en absoluta y eso lo podemos hacer anteponiendo el símbolo “$” a la letra de la columna y al número de la fila de la siguiente manera:

Si tomamos el primer ejemplo de la sección anterior y utilizamos la fórmula =$A$1+$B$1 en la celda C1 y posteriormente copiamos dicha fórmula a la celda C3, obtendremos el siguiente resultado:

En este caso la celda C3 devolverá el mismo resultado que la celda C1 porque ambas fórmulas hacen referencia a las mismas celdas aún después de haberla copiado. Tendremos el mismo efecto al copiar la fórmula de la celda C1 hacia la derecha:

Al convertir las referencias en absolutas, no importa a donde copiemos la fórmula, dichas referencias permanecerán siempre fijas. Para dejar en claro el uso de las referencias absolutas, supondremos una tabla de datos como la siguiente:

La celda C2 hace el cálculo del área para el primer círculo tomando en cuenta el valor de Pi que está almacenado en la celda F1. En este momento la referencia a la celda F1 es relativa y si copio la formula hacia abajo obtendré el siguiente resultado:

Al copiar la fórmula hacia abajo, Excel modifica la referencia a la celda F1 y le agrega una fila más para convertirla en una referencia a la celda F2 que es el comportamiento natural de las referencias relativas. Para hacer que nuestras fórmulas hagan siempre referencia a la celda F1 debemos transformar dicha referencia en absoluta:

Observa con detenimiento que la única referencia que he convertido en absoluta es la de la celda $F$1. La otra referencia a la celda B2 la he dejado como relativa porque necesito que sea modificada por Excel para tomar el valor del radio de cada círculo conforme se copie la fórmula hacia abajo. El resultado después de copiar la fórmula será el siguiente:

Todas las fórmulas harán referencia a la celda F1 porque está establecida como absoluta, pero Excel modificará correctamente las referencias a las celdas de la columna B para obtener el resultado correcto.

Con esto hemos cubierto los dos tipos de referencias en Excel más utilizados. Si quieres ver algunos ejemplos adicionales sobre estos dos tipos de referencias te recomiendo consultar el siguiente video tutorial: Referencias relativas y absolutas en Excel.

Referencias mixtas en Excel

En los ejemplos anteriores sobre referencias absolutas utilizamos el símbolo “$” para fijar tanto la columna como la fila. Sin embargo, es posible fijar solo la columna o fijar solo la fila y a estas variaciones se les conoce como referencias mixtas.

Si queremos fijar solamente la columna, anteponemos el símbolo “$” a la letra de la columna y dejamos la fila sin dicho símbolo. Si por el contrario deseamos fijar solamente la fila, entonces anteponemos el símbolo “$”al número de la fila. En la siguiente imagen podrás ver un resumen de los tipos de referencias que hemos revisado hasta ahora:

Si colocas la fórmula =$A1 dentro de la celda B1 y copias la fórmula hacia la derecha verás que la fórmula se mantiene igual en todas las columnas porque hemos indicado que deseamos tener la columna $A fija. Por el contrario, si copias la fórmula hacia abajo, Excel modificará el número de fila:

Sin embargo, si cambiamos el tipo de referencia mixta de la celda B1 por la fórmula =A$1 y copiamos hacia la derecha y hacia abajo, obtendremos un resultado diferente al anterior ya que habremos fijado solamente la fila:

Estos son los cuatro tipos de referencias de los cuales puedes elegir al momento de construir tus fórmulas. Recuerda que esta clasificación se basa en el comportamiento de la referencia al copiar una fórmula.

Cómo cambiar entre tipos de referencia

Un consejo muy útil para hacer más eficiente el tiempo invertido al ingresar cualquier referencia en una fórmula es el uso de la tecla F4 cuando nos encontramos en el modo de edición. Cuando el cursor de edición se encuentra sobre una referencia y pulsamos la tecla F4, Excel cambiará el tipo de referencia sin la necesidad de que ingresemos manualmente el símbolo “$”. Hagamos el siguiente ejemplo para conocer el funcionamiento de este atajo de teclado:

1. Selecciona la celda B1 e ingresa la fórmula =A1 pero no pulses la tecla Entrar.2. El cursor de edición se encontrará parpadeando al final de la referencia y si pulsas la

tecla F4 una vez, la referencia se transformará automáticamente en absoluta =$A$1.3. Si vuelves a pulsar la tecla F4 tendrás una referencia mixta como =A$1.4. Y si pulsas una cuarta vez la tecla F4, la referencia será cambiada por la otra opción de

referencia mixta =$A1.5. Al pulsar una quinta vez la tecla F4 volverás a la referencia relativa original.

La tecla F4 solamente cambiará la referencia que está sobre el cursor de edición así que, si quieres aplicar este cambio en varias referencias de una misma fórmula, entonces deberás colocar el cursor sobre cada referencia que será cambiada y pulsar F4 cada vez.

Referencias a rangos de celdas

Hasta ahora hemos visto cómo hacer referencia a una sola celda, pero Excel nos permite hacer referencia a un conjunto de celdas adyacentes utilizando el operador de rango que está representado por los dos puntos (:) y que deberá colocarse entre dos referencias de celdas. Sin embargo, esas dos referencias de celdas no son al azar, sino que deben ser el extremo superior izquierdo y el extremo inferior derecho de las celdas adyacentes a las que deseamos referirnos.

Considera el siguiente grupo de celdas adyacentes donde cada una de ellas tiene un valor numérico. La referencia para este rango de celdas será B2:D4 y puedes ver que dicho rango es reconocido por Excel al momento de hacer la suma.

Esto definitivamente es una gran ventaja ya que de lo contrario tendríamos que indicar todas las referencias a las nueve celdas, pero con este método podemos referirnos fácilmente a todo el rango de celdas. Solo debes recordar que la primera referencia corresponde a la celda del extremo superior izquierdo y la segunda referencia a la celda inferior derecha.

En este ejemplo ambas referencias son relativas, pero nada impide que utilicemos referencias absolutas al crear una referencia a un rango, por ejemplo: $B$2:$D$4. Todas las reglas que explique anteriormente para las referencias relativas, absolutas y mixtas también aplicarán para las referencias a rangos de celdas.

Referencias a celdas en otra hoja

En todos los ejemplos mostrados hasta este punto he utilizado referencias dentro de la misma hoja pero en más de una ocasión nos veremos en la necesidad de hacer referencia a una celda que se encuentra en una hoja diferente.

La única regla que debemos seguir es utilizar el nombre de la hoja donde se encuentra la celda de nuestro interés e inmediatamente colocar el signo de exclamación (¡) seguido de la referencia a la celda. De esta manera, si queremos referirnos a la celda C5 de la Hoja2, debemos hacerlo de la siguiente manera:

Una práctica que es altamente recomendable en las referencias a celdas de otra hoja, es rodear el nombre de la hoja con comillas simples (‘) ya que eso evitará cualquier error en caso de que el nombre de la hoja tenga un espacio en blanco, por ejemplo:

Para saber más sobre este tipo de referencias te recomiendo consultar el artículo Hacer referencia a celdas de otras hojas en Excel.

Referencias a celdas en otro libro

Si la celda que necesitas acceder se encuentra en otro libro, entonces deberás indicar el nombre del libro encerrado entre corchetes [] antes del nombre de la hoja de la siguiente manera:

Este tipo de referencia funcionará solamente si el libro especificado está abierto y además está guardado en la misma ubicación que el libro actual. En caso contrario será necesario indicar la ruta completa donde se encuentra dicho libro:

Observa que en este último ejemplo el nombre del libro y la hoja, así como la ruta, están encerrados entre comillas sencillas lo cual es ampliamente recomendable en caso de tener espacios en blanco en los nombres de las carpetas de la ruta, o en el nombre del libro u hoja.

Referencias 3D

Las referencias 3D son útiles cuando necesitamos hacer operaciones con las mismas celdas en diferentes hojas. Por ejemplo, si vamos a sumar la celda A1 de la Hoja1, Hoja2 y Hoja3, normalmente utilizaríamos la siguiente fórmula:

=SUMA(Hoja1!A1, Hoja2!A1, Hoja3!A1)

A diferencia de esta nomenclatura, las referencias 3D nos permiten indicar las mismas referencias de una manera abreviada:

=SUMA(Hoja1:Hoja3!A1)

Es muy importante mencionar que no es posible utilizar este tipo de referencias con cualquier función de Excel. Las funciones matemáticas y estadísticas son las que permitirán su uso pero no podrás utilizar este tipo de referencias en funciones de búsqueda como BUSCARV o COINCIDIR.

Además de acceder una sola celda en varias hojas utilizando las Referencias 3D, también puedes crear una referencia a un rango de celdas de la siguiente manera:

=SUMA(Hoja1:Hoja3!A1:C25)

El estilo de referencia F1C1

Los ejemplos de este artículo han utilizado el estilo de referencia A1 que es el predeterminado en Excel y que indica que las columnas serán identificadas por una letra, o combinación de letras, y que las filas serán identificadas por un número. Existe otro estilo de referencia en Excel, que no es tan común, pero que es necesario que lo conozcas por si llegas a encontrar algún equipo donde Excel utiliza el estilo de referencia F1C1 el cual identifica a las columnas y a las filas por su número. Así que en lugar de utilizar letras para las columnas, este estilo de referencia utiliza números para todas ellas.

Ya que sería difícil distinguir el número de la columna del número de la fila en una misma referencia, se antepone la letra “F” al número de la fila y la letra “C” al número de la columna. Algunos ejemplos de equivalencias en ambos estilos de referencia son los siguientes:

Para que Excel utilice este tipo de referencia debes ir a Archivo > Opciones > Fórmulas > Trabajando con fórmulas > Estilo de referencia F1C1.

Al seleccionar esta opción y aceptar los cambios, notarás de inmediato un cambio en los encabezados de columna ya que dejarán de ser letras y se mostrarán como números. Además podrás observar el estilo de referencia F1C1 en el Cuadro de nombres y ya no estará permitido utilizar el estilo de referencia anterior.

SI tu libro tenía fórmulas previamente ingresadas entonces Excel se encargará de mostrar automáticamente las referencias con el nuevo estilo y no será necesaria tu intervención en el cambio. Si quieres regresar al estilo de referencia A1 será suficiente con desmarcar la opción Estilo de referencia F1C1 del cuadro de diálogo Opciones de Excel.

La verdad es poco probable que alguna vez te veas forzado a utilizar el estilo de referencia F1C1, pero el concepto de utilizar valores numéricos para referirse a las columnas y filas es muy utilizado al programar en VBA ya que es mucho más fácil referirse programáticamente a las columnas por su número que por su letra.

Conclusión

Es importante que conozcas todos los aspectos de las referencias en Excel ya que juegan un papel fundamental en el uso de la hoja de cálculo y las fórmulas. Si comprendes correctamente su funcionamiento podrás evitar una gran cantidad de errores, y resultados incorrectos, que se generan en Excel por el uso incorrecto de las referencias.

Pegar valores en Excel con el tecladoTodos sabemos que el método abreviado de teclado para pegar es CTRL+V, sin embargo este método en Excel pega todo el contenido de una celda incluyendo su formato. Si queremos pegar solamente el valor de una celda debemos utilizar el Pegado especial.

De todas las opciones que existen dentro de Pegado especial, la opción de pegar Valores es sin duda la más utilizada. Ya que utilizamos este comando frecuentemente deberíamos tener un método abreviado de teclado para hacerlo, sin embargo no existe uno hasta el momento.

Agregar el comando a la barra de herramientas

Una alternativa es agregar a la barra de herramientas de acceso rápido el comando Pegar valores. Para ello debemos abrir el menú Personalizar barra de herramientas de acceso rápido y seleccionar la opción Más comandos.

En la lista de comandos disponibles seleccionamos la opción Todos los comandos y buscamos el comando Pega valores y lo agregamos a la barra de herramientas de acceso rápido.

Para conocer el método abreviado de teclado para este nuevo botón bastará con pulsar la tecla ALT una sola vez para que Excel muestre la tecla que debemos pulsar:

En este ejemplo, Excel ha asociado el número 4 al comando Pega valores dentro de la barra de herramientas de acceso rápido lo cual indica que podremos utilizar el método abreviado ALT+4 para pegar valores en Excel con el teclado.

Crear una macro para pegar valores

Otra opción para pegar valores en Excel con el teclado es crear una macro y asignarle una tecla de método abreviado. La macro que nos ayuda a pegar valores es la siguiente:

1Sub PegarValores()

2

3Selection.PasteSpecial Paste:=xlValues

End Sub

Una vez que hayas creado la macro, abre el cuadro de diálogo Macro (ALT + F8) y en la lista de macros disponibles selecciona la macro recién creada y pulsa el botón Opciones. Se mostrará el cuadro de diálogo Opciones de la macro donde podremos asignar una tecla de método abreviado.

Una vez elegido el método abreviado para la macro podremos ejecutarla con solo pulsar la combinación de teclas establecidas.

INICIO FUNCIONES ACERCA

Numerar filas automáticamente en ExcelPara numerar filas automáticamente en Excel tenemos dos alternativas: utilizar el controlador de relleno o utilizar la función FILA.  A continuación te mostraré ambos métodos.

Números consecutivos en Excel

Antes de aprender a generar números consecutivos en Excel debemos aprender lo que es el Controlador de relleno. El controlador de relleno es el pequeño cuadro negro que está ubicado en la esquina inferior derecha de la celda activa. Observa la siguiente imagen:

Cuando colocas el puntero del ratón sobre el controlador de relleno, el puntero cambia a una cruz negra. El controlador de relleno es de mucha utilidad para generar números consecutivos en Excel y para hacerlo debemos seguir los siguientes pasos:

1. Selecciona la primera celda que tendrá un número y escribe el valor inicial. Por ejemplo el número 1.

2. En la celda inferior escribe el siguiente valor de la serie lo cual establecerá un patrón de números a seguir. Escribe por ejemplo el número 2.

3. Selecciona ambas celdas y arrastra el controlador de relleno hacia abajo hasta llegar al número que deseas.

Observa la siguiente animación que dejará claro este procedimiento:

A medida que vamos arrastrando el controlador de relleno, Excel va insertando valores de acuerdo al patrón previamente establecido.  Es por eso que es importante establecer al menos los dos primeros números de manera que Excel identifique adecuadamente el patrón a seguir. Por ejemplo, si en las primeras dos celdas coloco los números 2 y 4 al numerar las filas con el controlador de relleno obtendremos una serie de números diferente a la anterior:

Numerar filas con la función FILA

Otro método que podemos utilizar para numerar filas automáticamente en Excel es a través de la función FILA. Este método mostrará siempre el número de la fila de cada celda por lo que siempre tendremos un incremento lineal. Para este método también utilizamos el controlador de relleno para copiar la misma fórmula hacia abajo. Observa la siguiente animación:

Una ventaja de utilizar la función FILA es que la podemos combinar fácilmente con una cadena de caracteres para generar números consecutivos alfanuméricos.

Inclusive la función FILA nos puede ayudar a generar códigos de números consecutivos muy específicos como 000-001, 000-002, etc. Estos números pueden ser de mucha utilidad para indicar códigos de productos o números de orden de compra. Para obtener este tipo de numeración de filas debemos utilizar la siguiente fórmula:

=TEXTO(FILA(), "000-000")

Observa el resultado de esta fórmula:

Convertir fórmulas en valores en ExcelHay ocasiones en las que después de realizar algún cálculo con los datos de nuestra hoja necesitamos reemplazar las fórmulas por sus valores de manera que dicho valor permanezca fijo y no vuelva a cambiar al momento de actualizar los cálculos de la hoja. En esta ocasión te mostraré tres métodos para realizar esta conversión en Excel incluyendo la creación de una macro para automatizar dicha tarea.

Primero haré un ejemplo para dejar en claro la problemática a la que me refiero. Supongamos que en la celda B1 de mi hoja necesito insertar la fecha y hora actuales utilizando la función AHORA.

Todo funciona perfectamente hasta que cierro el libro y lo vuelvo a abrir ya que en ese momento la función AHORA actualizará automáticamente el valor de la celda B1 con un valor de hora diferente.

Esta actualización automática también sucederá si se vuelven a calcular las fórmulas del libro es decir, cuando se introduce una nueva fórmula en cualquier celda del libro o cuando se pulsa la tecla F9. Así que, para fijar el valor de la celda B1 será necesario convertir la fórmula en valor y eso será precisamente lo que trataremos en las siguientes secciones.

Convertir fórmulas en valores con Pegado Especial

El primer método que te mostraré para convertir fórmulas en valores en Excel funciona con el Pegado Especial y es muy fácil de utilizar. Todo comienza por seleccionar la celda que contiene la fórmula y pulsamos la combinación de teclas Ctrl+C o también podemos hacer clic en el botón Copiar.

Sin moverse de la celda seleccionada, pulsamos la combinación de teclas Ctrl+V, o también puedes hacer clic en el botón Pegar, y de inmediato se mostrará el icono de las

Opciones de pegado y deberás hacer clic en la flecha para desplegar todas las opciones y hacer clic en Solo valores.

Una vez que hayamos pegado los valores habrá desaparecido la fórmula de la celda B1 y tendremos solamente un valor, así que sin importar que se vuelvan a calcular las fórmulas del libro o que volvamos a cerrar y abrir el libro, el valor de la celda B1 permanecerá exactamente igual. En la siguiente imagen puedes observar cómo la barra de fórmula ya no muestra la función AHORA para la celda B1 sino que muestra un valor fijo.

De la misma manera en que he copiado y pegado la celda B1 es posible copiar y pegar varias celdas al mismo tiempo de manera que podamos reemplazar todas las fórmulas por valores en un solo paso. Por ejemplo, en la siguiente imagen puedes ver que la columna Nombre completo hace una concatenación de los valores en las columnas Nombre y Apellido.

Para reemplazar todas las fórmulas de la columna Nombre completo será suficiente con seleccionar el rango C4:C6, copiar las celdas y volverlas a pegar sobre sí mismas asegurándonos de elegir la opción Solo valores.

De esta manera habrás convertido tres fórmulas a sus respectivos valores en un solo paso y este mismo método lo podrás utilizar con cuantas celdas sea necesario.

Convertir fórmulas en valores con F9

El segundo método que puedes utilizar para convertir fórmulas en valores es pulsar la tecla F9 al  momento de estar introduciendo la fórmula o al momento en que la estás editando. En la siguiente animación te muestro cómo hacer esta conversión al momento de introducir una fórmula.

En este ejemplo he pulsado la tecla F9 justamente en el momento en que he terminado de introducir la fórmula pero también es posible hacerlo cuando la fórmula ya ha sido

introducida previamente y para eso deberás editar la fórmula y pulsar la tecla F9. Es importante recordar que este método funciona solamente cuando estás en el modo de edición de una fórmula y por lo tanto podrás convertir solo una fórmula a la vez.

Macro para convertir fórmulas en valores

Si eres de los usuarios que continuamente están convirtiendo fórmulas en valores en Excel, te sugiero crear una macro para realizar dicha conversión con un atajo de teclado y de esa manera hacer más eficiente el uso de tu tiempo. Para crear este macro iré a la ficha Programador y abriré el Editor de Visual Basic y posteriormente seleccionaré la opción Insertar > Módulo y pegaré el siguiente código:

1

2

3

4

5

6

7

8

9

10

11

12

Sub ConvertirFormulas()

    'Copiar la selección de celdas

    Selection.Copy

    'Pegado Especial utlizando xlPasteValues

    Selection.PasteSpecial Paste:=xlPasteValues

    'Salir del modo de copiado

    Application.CutCopyMode = False

End Sub

Una vez copiado el código deberás guardar la nueva macro haciendo clic en el botón Guardar de la barra de herramientas del Editor de Visual Basic.

Una vez guardados los cambios podrás cerrar la ventana del Editor y entonces asignar el atajo de teclado a la nueva macro para lo cual deberás ir a la ficha Vista en Excel y pulsar el botón Macros.

Después de seleccionar la macro recién creada deberás pulsar el botón Opciones el cual mostrará un nuevo cuadro de diálogo donde podremos asociar un atajo de teclado. Para este ejemplo será la combinación de teclas CTRL+k tal como se muestra en la siguiente imagen:

Deberás pulsar el botón Aceptar para cerrar este cuadro de diálogo y posteriormente pulsar el botón Cancelar para cerrar el cuadro de diálogo anterior. Para probar la macro será suficiente con seleccionar el rango C4:C6 y pulsar la combinación de teclas CTRL+k para convertir las fórmulas en valores.

En el archivo de descarga he dejado las fórmulas tal como las ves en la imagen anterior de manera que puedas probar este comportamiento por ti mismo.

En esta ocasión hemos revisado tres alternativas para convertir fórmulas en valores en Excel ya sea utilizando la opción de Pegado Especial, o pulsando la tecla F9 al momento de ingresar o editar una fórmula, o a través de una macro la cual nos permitirá realizar la conversión utilizando un atajo de teclado.

Calendario 2015 en Excel

Uno de los recursos más solicitados en Excel Total es el calendario anual que tiene como objetivo el ayudar a todos los usuarios a organizar y planear las actividades del año. Hoy ha llegado el momento de publicar el calendario 2015 en Excel que, al igual que las versiones anteriores, será de utilidad para recordar todas las fechas importantes.

Al final de este artículo encontrarás el vínculo de descarga del archivo ZIP que deberás descomprimir antes de poder acceder a los archivos Excel que contienen el calendario 2015. Pero antes de hacerlo te sugiero leer las siguientes recomendaciones.

Tipo de archivo del calendario 2015

Las versiones anteriores del calendario han sido implementadas utilizando Libros de Excel tradicionales (XLSX), pero el calendario 2015 hace uso de macros por lo que ha sido creado en un Libro de Excel habilitado para macros (XLSM) que al abrirlo mostrará algunas advertencias. Si estás utilizando Excel 2010 o superior, es muy probable que la primera advertencia sea para indicar la activación de la Vista protegida.

Si se muestra dicha advertencia deberás hacer clic en el botón Habilitar la edición y en seguida se mostrará una segunda advertencia indicando que las macros han sido deshabilitadas. Para tener la funcionalidad completa del calendario deberás pulsar el botón Habilitar contenido.

Si estás utilizando Excel 2007, deberás hacer clic en el botón Opciones y posteriormente elegir la opción Habilitar este contenido. Una vez que hayas realizado estas acciones tendrás disponible todas las mejoras realizadas a la nueva versión del calendario 2015 en Excel.

Mejoras al calendario 2015 en Excel

La primera mejora implementada en el calendario 2015 es la inclusión de categorías para los días festivos del año de manera que exista una distinción visual entre cada uno de ellos. De manera predeterminada he incluido las categorías Festivo (Verde), Aniversario (Azul), Vacaciones (Amarillo), Importante (Anaranjado) y Fecha límite (Rojo).

Sin duda esta mejora será de gran ayuda para identificar visualmente cualquier fecha importante. Lo mejor de todo es que la asignación de la categoría se hará en un solo lugar y se replicará automáticamente en todos los calendarios disponibles. Más adelante explicaré con detalle lo sencillo que es asignar una categoría para cualquier día del año así como algunas opciones de personalización de estas categorías.

La segunda mejora en el calendario 2015 es la inserción automática de comentarios para cualquier día del año y es la razón por la cual se utilizan las maros. Por ejemplo, si para el día 1 de enero del 2015 elijo la categoría Festivo y además proporciono el comentario “Año Nuevo”, dicho texto será insertado automáticamente como un comentario en la celda para dicho día y será mostrado al momento de colocar el puntero del ratón sobre la celda:

Esta inserción de comentario se hace automáticamente con una macro y de igual manera se encargará de actualizar o de eliminar los comentarios al momento de indicarlo en la hoja correspondiente. En las próximas secciones explicaré con detenimiento la manera de elegir una categoría y de indicar este tipo de comentarios para cualquier día del año.

Calendario 2015 para imprimir

La primera hoja del libro Excel, llamada Completo, es la versión del calendario 2015 que nos permitirá imprimir todos los meses del año en una sola hoja con orientación vertical.

Como ya lo mencioné en la sección anterior, este calendario reflejará automáticamente los diferentes colores para aquellos días que tengan una categoría especial y además desplegará los comentarios de celda para los días donde los hayamos especificado.

Calendario 2015 por meses

La segunda hoja del libro te permitirá imprimir de manera individual cada uno de los meses del calendario 2015 en una hoja con orientación horizontal.

A diferencia del formato anterior, en este calendario por meses cada día del año está dividido en dos celdas. La celda superior es la que contiene la fecha para cada día y será la celda a la que se aplicará el color de relleno de acuerdo a la categoría seleccionada así como el comentario en caso de tenerlo .

La celda inferior de cada día es de libre captura y podrás insertar cualquier texto que te ayude a organizar y mantener el control de las actividades del día.

Mini calendario 2015 en Excel

La hoja Mini contiene el mini calendario 2015 que imprime todos los días del año en una tabla horizontal de tan solo 13 filas lo cual permite tener una visión diferente de todos los meses del año. Al igual que los calendarios anteriores, podrás identificar fácilmente aquellos días que tengan alguna categoría especial así como sus respectivos comentarios.

La hoja Diario del calendario 2015

La cuarta hoja del calendario 2015 en Excel es totalmente nueva y es el lugar donde podremos elegir una categoría e insertar algún comentario para cualquier día del año. Esta hoja consta de tres columnas: La columna Fecha que es una lista de los 365 días del año y que no deberá ser modificada. La columna Categoría es el lugar donde podrás elegir de la lista desplegable alguna de las 5 categorías posibles para cada día.

Al elegir alguna de las categorías, automáticamente se aplicará el color de fondo correspondiente a la celda de la columna Fecha y al mismo tiempo se aplicará el color de fondo en las hojas Completo, Mensual y Mini para el mismo día. En cualquier momento podrás cambiar la selección y reflejará automáticamente el cambio de color en todos los calendarios. Te recuerdo que de manera predeterminada las categorías tienen asignados los siguientes colores:

Festivo – Verde Aniversario – Azul Vacaciones – Amarillo Importante – Anaranjado Fecha límite – Rojo

Si quieres remover la categoría de algún día será suficiente con seleccionar dicha celda y pulsar la techa Suprimir. Por último, la tercera columna de la hoja Diario nos permite indicar el comentario que será insertado en los calendarios de las hojas Completo, Mensual y Mini.

Es así como la hoja Diario nos permitirá elegir la categoría de cada día del año y por lo tanto su color de relleno, así como los comentarios que serán insertados automáticamente en los diferentes calendarios. Finalmente debo decir que para eliminar

un comentario será suficiente con seleccionar la celda correspondiente bajo la columna Comentarios de la hoja Diario y pulsar la tecla Suprimir.

La hoja Configuración del calendario 2015

El calendario 2015 en Excel tiene de manera predeterminada 5 categorías para cada día del año y cada una de ellas tiene un color asignado. La hoja Configuración te permitirá cambiar la descripción de cada categoría en caso de necesitarlo y dicho cambio deberás hacerlo bajo la columna Categoría.

Por ejemplo, si deseas renombrar las categorías por el nombre de cada color, la tabla quedará de la siguiente manera:

Esto ocasionará que en la hoja Diario, al momento de elegir una categoría para cualquier día, la lista de nombres disponibles muestre los cambios hechos en la hoja Configuración.

Es importante tener cuidado con la hoja Configuración ya que es necesario mantener sin cambios la columna ID porque dicho identificador es utilizado para actualizar el color de relleno en los diferentes calendarios. Por otro lado, la columna Color solamente se muestra con motivos ilustrativos y el hecho de cambiar el color de relleno de esas celdas no ocasionará un cambio en los colores asignados a las categorías.

Si deseas hacer un cambio en los colores asignados a las categorías, o agregar categorías nuevas, será necesario hacer algunos ajustes avanzados dentro del libro los cuales explicaré en la siguiente sección pero que solo recomiendo si tienes experiencia trabajando con formatos condicionales.

Ajustes avanzados del calendario 2015

Los colores de relleno de las diferentes categorías son aplicados a todos los calendarios a través del uso de reglas de formato condicional. De manera predeterminada el calendario 2015 tiene predefinidas 5 categorías y por lo tanto cada hoja del libro tiene 5 reglas de formato condicional. Si deseas cambiar el color utilizado por alguna categoría, deberás cambiar el color de relleno de la regla de formato condicional correspondiente dentro de cada hoja del libro.

La imagen anterior muestra el Administrador de reglas de formato condicionales para la hoja Completo y puedes observar las 5 reglas para las diferentes categorías. Para cambiar el color de una categoría deberás editar la regla de formato condicional correspondiente y elegir el nuevo color de relleno. Este cambio lo deberás realizar en cada una de las hojas.

Si por el contrario deseas agregar una nueva categoría al calendario, deberás comenzar por agregar su descripción en la hoja Configuración. Por ejemplo, en la siguiente imagen he agregado la categoría Celebración con el identificador 6 y un color púrpura.

Recuerda que el color indicado en esta hoja es solamente ilustrativo y solo es útil para recordar el color asignado a la categoría pero no tiene efecto alguno sobre el calendario. El siguiente paso será crear una nueva regla de formato condicional dentro de cada hoja del libro indicando una fórmula similar a la siguiente:

=SI.ERROR(INDICE(DiasCategorias, B5-FechaInicio+1),0)=6

Observa que al final de la fórmula está indicado el número 6 que es el identificador asignado a la nueva categoría en la hoja Configuración. Las reglas de formato condicional en las demás hojas serán muy similares y también deberán ser igualadas al número 6 pero te recomiendo tomar como base las fórmulas de las reglas ya existentes en cada hoja porque no todas las fórmulas hacen referencia a la celda B5, sino que se usa la celda C3 o la celda A2 dependiendo de la ubicación donde inician las celdas con las fechas para cada hoja.

Con estos dos ajustes habrás creado una nueva categoría dentro del calendario 2015 y al momento de elegirla dentro de la hoja Diario se aplicará el color de relleno correspondiente en los calendarios de las diferentes hojas.

Descarga el calendario 2015 en Excel

Al igual que en otras ocasiones, al descargar el archivo comprimido encontrarás dos versiones del calendario. Una versión considera la semana de Domingo a Sábado y la otra versión es para una semana de Lunes a Domingo.

Agrupar por fechas una tabla dinámicaPodemos agrupar por fechas una tabla dinámica con tan solo seguir algunos pasos sencillos. Supongamos que tengo una tabla de datos que contiene las ventas de diciembre del 2011 y de enero del 2012 de la siguiente manera:

Después de crear una tabla dinámica basada en este rango de datos obtenemos el siguiente resultado:

Los datos en la tabla dinámica se ven exactamente igual que el rango de datos origen porque no hemos agrupado por fechas.

Agrupar por fechas una tabla dinámica

Para agrupar por fechas una tabla dinámica debemos hacer clic derecho en cualquier celda de la columna de fechas y seleccionar la opción de menú Agrupar. Se mostrará el cuadro de diálogo Agrupar.

En este cuadro de diálogo debes seleccionar las opciones Meses y Años de manera que la tabla dinámica haga una clara distinción entre los meses de 2011 y 2012. Al pulsar el botón Aceptar tendremos los datos agrupados por fecha.

Para desagrupar los datos debemos hacer clic derecho sobre una celda de la columna de fechas y seleccionar la opción Desagrupar.

Uso de nombres en ExcelCuando hacemos referencia a una celda utilizamos su dirección: columna y fila, pero Excel nos permite crear nombres para referirnos a esa misma celda o rango de celdas. Después de definir un nombre lo podremos utilizar dentro de nuestras fórmulas.

Crear un nombre en Excel

La manera más sencilla de crear un nombre es utilizar el cuadro de nombres que se encuentra en la barra de fórmulas. El procedimiento es muy simple, solamente seleccionamos la celda y escribimos su nombre dentro del cuadro de nombres:

Lo mismo podemos hacer para un rango de celdas. Selecciona todas las celdas del rango y escribimos su nombre:

El Administrador de nombres en Excel

Una vez que hayas creado varios nombres podrás administrarlos utilizando el Administrador de nombres. Este comando lo encontrarás en la ficha Fórmulas dentro del grupo Nombres definidos. Al pulsar el botón se mostrará el cuadro de diálogo Administrador de nombres:

Desde este cuadro de diálogo podrás editar y eliminar los nombres previamente creados. También podrás crear nuevos nombres al pulsar el botón Nuevo.

Utilizar los nombres en Excel

Podemos utilizar los nombres en Excel dentro de nuestras fórmulas y de esta manera hacer que la fórmula sea más fácil de entender. En el siguiente ejemplo calcularé el porcentaje de comisión que le corresponde a Angélica por las ventas realizadas durante el primer trimestre del año:

Observa que en la fórmula he utilizado la función SUMA para obtener el total de las ventas. La fórmula SUMA(Ventas) equivale a tener la fórmula SUMA(B4:B6) pero ya que dicho rango tiene un nombre asignado puedo utilizarlo en lugar de la dirección. Finalmente el resultado de la suma es multiplicado por el nombre Comisión que corresponde a la celda B1.

De esta manera podemos decir que la fórmula

=SUMA(Ventas) * Comisión

es equivalente a tener la siguiente fórmula

=SUMA(B4:B6) * B1

El uso de nombres en Excel nos facilitará la compresión de las fórmulas dentro nuestras hojas y serán de ayuda para cualquier otra persona que necesite revisar o auditar nuestras fórmulas. Utiliza esta propiedad de Excel para mejorar tu productividad con la herramienta.

La declaración If-Then en VBAEn 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 sentencia 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ón Entonces 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:

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

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.

Ejemplo de casilla de verificación en ExcelLas casillas de verificación permiten al usuario seleccionar un valor, o desmarcarlo, con tan solo hacer clic sobre la casilla. A continuación veremos un ejemplo de uso del Control ActiveX casilla de verificación en Excel.

El ejemplo que desarrollaremos en esta ocasión es el siguiente. Cierta empresa organizadora de eventos necesita crear un formato en Excel para capturar el nombre de los asistentes a su próximo congreso.

En este formato se desea tener una casilla de verificación que indicará si la persona cuenta con una membresía en cuyo caso el costo del evento será diferente. De esta manera, al seleccionar la casilla de verificación necesitamos modificar el costo del evento para cada asistente. Observa la siguiente imagen.

En la celda B3 tenemos el costo normal del evento y en la celda B4 el costo del evento con membresía. Las casillas de verificación están bajo la columna Miembro y el precio del evento se muestra bajo la columna Costo. En caso de seleccionar la casilla de verificación el valor del costo deberá modificarse por el valor de la celda B4.

Vincular casilla de verificación a una celda

El primer paso será vincular la casilla de verificación a una celda. Para ello abrimos las propiedades de cada control y especificamos la celda a la que quedará vinculada en la propiedad LinkedCell.

En la imagen superior puedes notar que el control ChecBox1 ha quedado vinculado a la celda E8. De la misma manera hacemos para los otros controles vinculándolos a celdas

diferentes. En la siguiente imagen puedes ver que todas las cajas de verificación han quedado vinculadas a una celda:

Cuando una casilla de verificación está seleccionada, la celda vinculada mostrará el valor VERDADERO, de lo contrario mostrará el valor FALSO. De hecho, si modificas directamente el valor de la celda vinculada entre FALSO y VERDADERO, verás también reflejado el cambio en la casilla de verificación sin necesidad de que hayas hecho clic sobre el control.

Crear fórmula utilizando celda vinculada

Ya que las celdas vinculadas nos avisan si la casilla de verificación está o no seleccionada, las podemos utilizar para crear una fórmula que determine el costo que se debe seleccionar. La fórmula a utilizar en la celda C8 es la siguiente:

=SI(E8, $B$4, $B$3)

La función SI nos ayuda a validar si la celda vinculada tiene un valor FALSO o VERDADERO.  Si es FALSO, entonces se mostrará el precio normal (B4), pero si es verdadero quiere decir que la casilla de verificación está seleccionada y por lo tanto debemos mostrar el precio de membresía (B5). Observa el resultado de la fórmula cuando la casilla de verificación está seleccionada:

Ejemplo de casillas de verificación

Finalmente ocultaré la columna E para que no se vean las celdas vinculadas y colocaré la fórmula de suma en la celda C11 para mostrar el costo total. Observa el comportamiento de la columna Costo y la suma total al momento de seleccionar las casillas de verificación.

Casilla de verificación en VBAUna 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).

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:

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

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.ValueEnd 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 = 1If 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.

La cámara fotográfica de ExcelLa cámara fotográfica de Excel nos permite capturar una imagen de un rango de celdas para después poder insertarla en alguna otra parte de la hoja o del libro pero con la ventaja de que no serán imágenes estáticas sino “dinámicas”.

Encontrar la cámara fotográfica

De manera predeterminada Excel 2010 no muestra la cámara fotográfica así que debemos personalizar la barra de herramientas de acceso rápido para agregar dicho comando.

Una vez que hayas abierto el cuadro de diálogo Opciones de Excel, que se encuentra en la ficha Archivo, debes ir a la sección Barra de herramientas de acceso rápido y seleccionar la opción Comandos que no están en la cinta de opciones y desplazarte en la lista de comandos hasta encontrar el comando Cámara.

Al pulsar el botón Agregar se añadirá el comando Cámara a la Barra de herramientas de acceso rápido.

Usar la cámara fotográfica en Excel

Para ejemplificar el uso de la cámara fotográfica en Excel haremos el siguiente ejemplo. Tengo un rango de datos que quiero copiar, pero necesito que dicha copia se actualice automáticamente al realizar una modificación en los datos originales. El primer paso es seleccionar las celdas que contienen los datos.

El segundo paso será pulsar el comando Cámara, el cual tomará la fotografía de las celdas seleccionadas y nos permitirá pegar la imagen en cualquier otra área de la misma hoja o en otra hoja del libro con tan solo hacer clic. Observa cómo realizo esta acción:

Puedes notar que Excel trata este elemento como una imagen ya que nos permite rotarla e inclusive cambiarla de tamaño. También podrás observar que al seleccionarla se mostrará la ficha contextual Herramientas de imagen sin embargo no es cualquier imagen. Observa cómo al modificar los datos en la tabla original, se actualiza la información dentro de la imagen:

En este ejemplo he pegado la imagen de la cámara fotográfica en la misma hoja, sin embargo puedes pegarla en otra hoja distinta y obtener el mismo resultado en la actualización de los datos.