PDF con 62 mini lecciones de VBA para Excel. Serie #SabíasQueEnVBA

Al igual que hiciera con los 62 trucos de Excel de la serie gratuita #SabíasQueEnExcel, también con la serie #SabíasQueEnVBA voy a ofrecer a mis lectores y seguidores, en este mismo artículo, un eBook con 62 mini-lecciones de VBA para Excel:

Este trabajo es la recopilación, en PDF, de la serie que he ido publicando a diario en Facebook, Twitter y LinkdeIn, bajo el hashtag #SabíasQueEnVBA.

Según he podido comprobar con los comentarios, puedo decir que he quedado muy satisfecho de la utilidad ofrecida por este curso gratuito. Ha sido un placer compartir con la comunidad de usuarios y usuarias de Excel estos conocimientos.

Sin más dilación, ahí va el enlace para la descarga:

eBook PDF #SabíasQueEnVBA

Gracias por la acogida y preparaos para el próximo #SabíasQue...


José Manuel Pomares Medrano

PDF con 62 trucos de Excel. Serie #SabíasQueEnExcel

Durante varios meses he estado lanzando todos los días laborales un truco de Excel, de forma gratuita y simultánea en tres plataformas distintas: Facebook, Twitter y LinkedIn. Lo hacía bajo una etiqueta o "hashtag" llamada #SabíasQueEnExcel y, lo que en un principio se convirtió en una prueba para ver la aceptación, la verdad es que ha ido calando en mis seguidores hasta el punto de que, muchos de ellos, cuando me veían personalmente me felicitaban y me animaban a continuar con la serie declarándose "fans" de la misma.


Así las cosas, darle continuidad a algo que satisfacía a tantos usuarios de Excel no ha sido difícil, puesto que sabía que cada día, lo que escribía no caería en saco roto.

Una vez terminada la serie, me planteé hacer una recopilación. El motivo es claro: muchos seguidores no habrán podido guardar todos los trucos en algún lugar apto para ser consultado frecuentemente. El resultado es un ebook en formato PDF que recoge todos los trucos con algunos consejos extra que no aparecían en cada truco diario.

El enlace para descargar el ebook es:

eBook PDF #SabíasQueEnExcel

Gracias por la buenísima acogida y por haber compartido el PDF de forma masiva en las RRSS.
¿Estáis preparados para el próximo #SabíasQueEnVBA ?


José Manuel Pomares Medrano

Cómo ajustar en Excel el ancho de varias columnas (o filas) a la vez

El truco que vamos a mostrar en este artículo es sorprendentemente desconocido por muchos usuarios que nos son tan principiantes en el uso de hojas de cálculo. Así pues, me animo en este momento a plasmar la fácil solución a este frecuente problema cuando trabajamos en Excel.

Como ya habrá adivinado el lector por la imagen anterior, pero sobre todo por el título de este artículo, vamos a mostrar como ajustar el ancho de varias columnas (o filas) de una forma muy rápida (y por lo tanto eficiente). En los ejemplos, representaremos un ajuste de columnas, pero el ajuste de filas se hace de forma similar y no es necesario repetirlas.

PASO 1

Seleccionar todas aquellas columnas cuyo ancho queremos ajustar. Lo podemos hacer fácilmente con el ratón, haciendo clic en el título (letra) de la columna de la izquierda y, SIN SOLTAR, extender la selección hacia los títulos de las columnas que también queremos ajustar:

PASO 2

Ahora tenemos dos opciones:

• La primera es hacer un ajuste fino introduciendo una medida de ancho de columna en el cuadro de diálogo que obtenemos al hacer clic en la opción "Ancho de columna" del menú contextual que emerge al hacer clic con el botón derecho del ratón en cualquier título de columna de los que tenemos seleccionados:

• La segunda opción es hacer el ajuste de forma manual, haciendo clic con el botón izquierdo del ratón entre título y título de las columnas seleccionadas y, SIN SOLTAR, arrastrar hacia la derecha (si queremos que tengan mayor anchura) o hacia la izquierda (si queremos que tengan menor anchura).

El resultado de estas acciones que hemos estudiado es que todas las columnas se ajustan al ancho de la columna que hemos establecido en el último paso arrastrando hacia la derecha o hacia la izquierda.

TRUCO

Si en vez de hacer clic y arrastrar (hacia la derecha o hacia la izquierda) el cursor especial que nos aparece, lo que hacemos es DOBLE CLIC, ¡¡¡ todas las columnas se ajustan al ancho perfecto para que en todas ellas se pueda visualizar el contenido de todas las celdas:

En Macintosh, el ajuste manual con el ratón se hace de la misma forma que hemos explicado en Windows, pero en el caso que querer hacer un ajuste fino introduciendo una medida, el menú lo tenemos en la ficha INICIO y desplegando el botón "Formato" del grupo "Celdas":

José Manuel Pomares Medrano

Colorear automáticamente la celda activa en Excel

Son varias las formas de conseguir lo que promete el título de este artículo. Una de las más sencillas y que no genera problemas es la que vamos a explicar a continuación en 2 sencillos pasos:

PASO 1 (establecer un formato condicional):

• Seleccionamos las celdas que queremos que se coloreen automáticamente al ser seleccionadas.
• Estando en la pestaña Inicio, hacemos clic en el botón "Formato condicional" (que está dentro del grupo de botones "Estilos") y elegimos "Nueva regla". Después, seleccionamos la opción "Utilice una fórmula que determine las celdas para aplicar formato" y escribimos en el campo que hay más abajo la fórmula: =Y(FILA()=CELDA("fila");COLUMNA()=CELDA("columna")):

• Aceptamos todos los cuadros para salir.

PASO 2 (provocar que el formato condicional se active al cambiar de celda):

La utilidad "Formato condicional" de Excel, no hace su trabajo (aplicar el formato según una condición) si no hay algo que provoque un recálculo de datos y/o de pantalla (en realidad, Excel casi siempre está calculando y recalculando datos y pantalla: cuando cambiamos de hoja, cuando introducimos un dato en una celda, etc.). En nuestro caso, pretendemos que un formato condicional se aplique tan sólo al cambiar la selección de una celda.... y en este caso, solo con eso no es suficiente para que Excel haga algún recálculo. Por ello, debemos ayudarle introduciendo algo de código en la propia hoja donde están nuestras celdas con formato condicional. Esto se hace de la siguiente forma:

• Hacemos clic con el botón derecho en la pestaña de la hoja y elegimos "Ver código".

• Escribimos exactamente el siguiente código en la ventana de código:

• Cerramos la ventana del editor de VBA.
• ¡¡Ya está !! Debe funcionar. Eso sí... si el archivo no contenía macros anteriormente, a la hora de cerrar y guardar, solicitará que se guarde como un archivo preparado para albergar macros (con extensión .xlsm) , para lo cual, debemos contestar "No" al siguiente cuadro que obtenemos al intentar cerrar y/o guardar:

         .... y guardar como ..

Si desea llevar a la práctica todo lo que le hemos ofrecido en este artículo, puede asistir a nuestros cursos prácticos de Excel o puede solicitarlos in company:

5 bromas en Excel para... ¡Inocentes! ¡Inocentes!

En este caso, vamos a hacer un post desenfadado, ya que se acercan las Navidades y... concretamente el día de los Santos Inocentes. Así pues, para aquellos que quieran poner en un "apurillo" a algún compañero de oficina de forma transitoria, reversible y acompañado de alguna sonrisa, va este artículo.

Nos encomendamos pues, a varias cosas. Una de ellas, que el receptor de la broma no sea un gran experto en Excel y "caiga" nuestra broma. Otra, es que tenga el suficiente sentido del humor como para encajarla. Otra cuestión más, es que... el jefe no esté cerca... jeje.

Broma #1    Nivel: fácil

Provocar que cada vez que el "inocente" presione Enter en Excel, la selección pase a la celda de la izquierda, en vez de hacia abajo.

Para "perpetrar" la broma, debemos introducirnos en su Excel y hacer clic en el Botón de Office (si es Excel 2007) o en la ficha "Archivo" (si es Excel 2010 o 2013) y después en "Opciones de Excel". Una vez ahí, seleccionar "Avanzadas" y después, dentro de "Opciones de edición", seleccionar "Izquierda" debajo de la opción "Después de presionar Entrar, mover selección"

Se aplica a cualquier libro que se abra en Excel.

Broma #2    Nivel: fácil

Provocar que la rueda del ratón, en vez de tener la función de desplazar hacia arriba o hacia abajo la hoja de cálculo, tenga la función de hacer zoom de pantalla.

Para ello, debemos conseguir tener en pantalla el mismo cuadro de "Opciones de Excel" de la broma anterior y también el mismo menú "Avanzadas"...  y también dentro de "Opciones de edición", pero algo más abajo:

Se aplica a cualquier libro que se abra en Excel.

Broma #3    Nivel: fácil

Provocar que los números de fila y los nombres de las hojas, se vean en el lado derecho de la pantalla, en vez de en el izquierdo. Esta broma será efectiva sólo cuando el "inocente" active la hoja que nosotros queramos:

Activamos la hoja sobre la que queremos hacer la broma. Vamos al cuadro de "Opciones de Excel" utilizado en las bromas anteriores y después en el menú "Avanzadas", pero en este caso dentro del apartado "Mostrar opciones para esta hoja", activamos la casilla:

El resultado será...

Broma #4    Nivel: fácil

Provocar que al "inocente" le desaparezcan de Excel todas las fichas o menús de la cinta de opciones.

De nuevo vamos al cuadro de "Opciones de Excel", pero esta vez activamos el menú "Personalizar la cinta de opciones". Después, en el panel de la derecha, desactivamos todas las casillas que corresponden a las fichas de Excel:

El resultado sería:

             Publicidad (vídeo en HD de software para Plan de viabilidad económica):

Broma #5    Nivel: avanzado

Provocar que al "inocente" le aparezca, al abrir un archivo, un mensaje en pantalla que podremos personalizar.

Para conseguir esto, debemos hacer lo siguiente:

• Abrir el archivo sobre el que queremos hacer la broma.
• Presionar Alt+F11 para abrir el editor de VBA
• Hacer doble clic en el objeto "ThisWorkbook" del proyecto que lleve el nombre del archivo:

•  Seleccionar "Workbook" en el desplegable:

 ..... y vemos cómo automáticamente se escribe en la ventana de abajo unos protocolos de inicio y final de macro (o procedimiento).

• Escribimos entre los protocolos de incio y final, exactamente el siguiente código, seguido de la frase-broma que queramos (entre comillas):

          MsgBox "La grua acaba de sancionar y arrastrar al vehículo con matrícula 4444-HHM"

...para que quede así:

• Cerramos el editor de VBA y hacemos "Guardar como..." con el nombre del archivo original... pero con un tipo de archivo "Libro de Excel habilitado para macros" . La próxima vez que el "inocente" abra el archivo, obtendrá un mensaje como este:

Para que le funcionen las macros a nuestro "inocente" debemos habilitarselas antes, claro. En este artículo podemos ver cómo se hace.

Para revertir esta broma, basta con eliminar el código de donde se puso, guardar y salir.

Si desea llevar a la práctica todo lo que le hemos ofrecido en este artículo, puede asistir a nuestros cursos prácticos de Excel o puede solicitarlos in company:

Detectar valores duplicados en una columna de Excel

De forma distinta al artículo anterior, en el que se trataba de "Detectar diferencias entre 2 columnas en Excel", vamos en esta ocasión a aprender un "truco" para detectar valores repetidos más de una vez, dentro de una misma columna.

Insistimos en que vamos a "detectar", no a eliminar duplicados. Para esto último, Excel tiene una herramienta que podemos encontrar en la ficha Datos / grupo Herramientas de Datos / Quitar duplicados

Además, y de la misma forma que lo hacíamos en el artículo anterior, lo vamos a hacer con una utilización avanzada de "Formato condicional". Es decir, sin necesidad que crear una columna auxiliar con fórmulas, sino que incluiremos una función "dentro" del formato condicional.

Detectar un valor repetido más de una vez en una columna (forma sencilla)

Si sólo queremos saber los valores que están repetidos (independientemente del nº de veces), se puede conseguir directamente utilizando una de las opciones de Formato Condicional (después de seleccionar el rango donde suponemos que habrá valores repetidos):

Ficha Inicio / Formato condicional / Resaltar reglas de celdas / duplicar valores

Detectar un valor repetido x veces en una columna (forma avanzada)

Vamos a aprender a conseguir lo mismo que antes, pero de una forma algo más avanzada, lo que nos permitirá tener más control en algunos casos. Este mayor control consiste en que, no sólo podremos saber  los valores que están repetidos más de una vez, sino que podemos discriminar aquellos que están repetidos más de x veces sólamente.... o bien aquellos que están repetidos exactamente x veces.

En primer lugar, debemos seleccionar el rango de celdas al cual queremos aplicar la detección de diferencias. Elegimos, por ejemplo, la columna 2 (el rango C3:C9) porque queremos saber si en esa columna ha hay algún valor repetido más de una vez. ¡¡Atención, se debe seleccionar con el ratón de arriba hacia abajo, para que quede activa la celda de arriba y después funcione el formato condicional!!

Vamos a la ficha Inicio / Formato condicional.../Nueva regla... / Utilice una fórmula que determine... y escribimos en el campo "Dar formato a los valores donde esta fórmula sea verdadera" la siguiente fórmula: 

El siguiente paso es dar el formato deseado desde el botón que encontraremos más abajo y presionamos "Aceptar". El resultado será similar al siguiente:

El motivo de utilizar la función =CONTAR.SI ($C$3:$C$9;C3)>1 es que conseguimos que el formato condicional reconozca en cada celda si el valor contenido en ella está repetido más de una vez (por ello se escribe > 1 al final) en todo el rango C3:C9.

Vemos que el rango C3:C9 se escribe con forma de referencia absoluta: $C$3:$C$9;C3 , sin embargo, la referencia C3 NO se debe escribir entre "signos dólar". El motivo de que la celda C3 deba ser una referencia relativa es posibilitar al formato condicional que se aplique a todo el rango (el formato condicional, aunque no lo hayamos puesto, va leyendo celda a celda aumentando un nº de fila: C3, C4, C5, C6...etc.).

Es muy fácil intuir que si queremos detectar sólo los valores que están repetidos más de dos veces, deberíamos cambiar >1  por  >2 ..... y las demás posibilidades son fáciles de imaginar. Por ejemplo, podríamos marcar aquellos valores que estén repetidos exactamente 3 veces, tan sólo cambiando > por =3.

Si desea llevar a la práctica todo lo que le hemos ofrecido en este artículo, puede asistir a nuestros cursos prácticos de Excel o puede solicitarlos in company:

Detectar diferencias entre 2 columnas en Excel

Es muy frecuente que los usuarios de Excel necesiten detectar diferencias entre dos columnas o tablas de Excel. Esto se puede hacer de varias formas, pero en este caso, vamos a hacer que la detección se haga mediante "formato condicional" y que las diferencias encontradas en una columna B respecto de una columna A se marquen con un color de fondo distinto.

Nos podemos encontrar con varios casos o varias necesidades distintas:

Detectar diferencias entre pares de celdas de una misma fila

En primer lugar, debemos seleccionar el rango de celdas al cual queremos aplicar la detección de diferencias. Es decir, seleccionamos el rango C3:C9 porque queremos saber si en esa columna ha cambiado algún valor comparado con su pareja en la columna 1. ¡¡Atención, se debe seleccionar con el ratón de arriba hacia abajo, para que quede activa la celda de arriba y después funcione el formato condicional!!

Vamos a la ficha Inicio / Formato condicional.../Nueva regla... / Utilice una fórmula que determine... y escribimos en el campo "Dar formato a los valores donde esta fórmula sea verdadera" la siguiente fórmula:  

El siguiente paso es dar el formato deseado desde el botón que encontraremos más abajo y presionamos "Aceptar". El resultado será similar al siguiente:

...viendo que han quedado resaltadas las celdas de la columna 2 que suponen una diferencia respecto de sus parejas en la columna 1.

Detectar diferencias en cualquier parte de las columnas

En al caso anterior, sólo se detectaban diferencias entre parejas de celdas. Aunque en la columna 2 hubiesen los mismos valores que en la columna 1, tan sólo con cambiarlos de orden, ya se detectaban diferencias.

Ahora necesitamos detectar si en la columna 2 aparece algún valor que no esta en la columna 1, no importándonos la coincidencia de orden de los demas valores. Dicho sobre un ejemplo, en la siguiente tabla, necesitamos que aparezcan resaltados los valores "S" y "P" de la columna  2, puesto que no existían en la columna 1. Los demás valores de la columna 2, aunque en distinto orden, sí existían en la columna 1:

Para conseguir que los valores "S" y "P" de la columna  2 queden resaltados, debemos seleccionar los datos de la columna 2 de la misma forma que lo hicimos en el otro ejercicio y vamos al mismo cuadro de diálogo. Posteriormente, la fórmula a introducir sería:

...y el resultado...

Explicación para la función BUSCARV:

El primer argumento de la función BUSCARV es la primera celda de la columna 2 donde se van a resaltar las diferencias (¡¡NO debe ser una referencia absoluta. Es decir, no debe tener los sígnos de dólar!!). El segundo argumento es el rango de celdas donde se buscarán las diferencias en la columna 1. El tercer argumento es un 1 porque necesitamos que se busque en la única columna del rango (la B). El cuarto argumento indica que se buscará una coincidencia exacta aunque no esté la columna B ordenada.

Explicación para la función SI.ERROR:

El primer argumento de la función SI:ERROR es la propia función BUSCARV. Esto es, SI:ERROR evaluará lo que se obtenga con BUSCARV. El segundo argumento de la función SI:ERROR es un valor lógico (VERDADERO = 1) que obtendremos si BUSCARV da un error (si NO encuentra lo que busca y por lo tanto hay una diferencia).

Si desea llevar a la práctica todo lo que le hemos ofrecido en este artículo, puede asistir a nuestros cursos prácticos de Excel o puede solicitarlos in company:

Aplicar una operación matemática a todo un rango en Excel

Unas de las utilidades menos conocidas en Excel, son las que están dentro del comando "Pegado especial...", el cual es una especie de "navaja suiza" ... con múltiples utilidades.

A modo de truco de productividad, vamos a ver una de esas utilidades poco extendidas entre los usuarios de Excel. Se trata de la posibilidad de aplicarle a todo un rango, una operación matemática con un número que previamente hemos copiado mediante Ctrl+C o mediante el botón "Copiar". Esta posibilidad se encuentra dentro del cuadro "Pegado especial..." cuando accedemos a él desplegando el comando "Pegar":

¿Cómo se hace?

PASO 1 

Copiar la celda donde se encuentra el número que queremos "sumar, restar, multiplicar o dividir" todo el rango.

PASO 2 

Seleccionar el rango sobre el que queremos actuar (hacer la operación matemática):

PASO 3 

Seleccionar "Pegado especial..." desplegando el botón "Pegar":

... y después seleccionamos la operación que deseamos, en el cuadro de diálogo que emerge:

... y hacemos click en el botón "Aceptar".

Como ejemplo, hemos utilizado la función "sumar" de entre las 4 que ofrece Excel. Por lo tanto, el resultado de sumar la cantidad de 200 € al rango, es:

José Manuel Pomares Medrano