miércoles, 1 de agosto de 2018

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








sábado, 14 de julio de 2018

La nueva función SI.CONJUNTO en Excel


Cuando un usuario traspasa cierta línea y nivel en su utilización de Excel, descubre que la función SI es sumamente práctica para obtener un valor en una celda. Ese valor que obtenemos está condicionado a que se cumpla una prueba lógica del tipo A1 = 5 (por ejemplo). Pero también es posible obtener un valor condicionado a que se cumplan dos pruebas o tres pruebas... y así sucesivamente. De hecho, hay posibilidad de anidar 64 funciones SI para condicionar con 64 pruebas lógicas.


El problema es que anidar demasiadas funciones requiere una cuidada planificación para no errar en la lógica. En el caso de que algo haya ido mal en esa planificación (o en el caso de que necesitemos modificar a posteriori la fórmula), cuando tenemos más de 5 o 6 funciones SI anidadas, todo se complica de forma extrema.

Microsoft ha sido sensible a ello y ha creado una función que soluciona este problema con una función que realiza interiormente esos anidamientos. Tan solo tenemos que ordenar convenientemente las pruebas lógicas y los resultados requeridos.

Por ejemplo, si queríamos anidar 7 funciones SI para determinar el día de la semana en función del número que hay en la celda A1 (del 1 al 7), hasta ahora podíamos hacerlo así:

=SI(A1 =1;"Lunes";SI(A1=2;"Martes";SI(A1=3;"Miércoles";SI(A1=4;"Jueves";SI(A1=5;"Viernes";SI(A1=6;"Sábado";SI(A1=7;"Domingo";"")))))))

(Este es un ejemplo relativamente sencillo, pero la fórmula puede complicarse muchísimo más)

La cuestión es que, ahora, con la nueva función SI.CONJUNTO de Excel, la fórmula puede quedar así:

=SI.CONJUNTO(A1=1;"Lunes";A1=2;"Martes";A1=3;"Miércoles";A1=4;"Jueves";A1=5;"Viernes";A1=6;"Sábado";A1=7;"Domingo")

Además de que es algo más corta la fórmula, hay algo que determina de forma importantísima la mayor facilidad de manejo: solo hay una función con sus dos únicos paréntesis de apertura y cierre.

Por lo tanto, la sintaxis de la función SI.CONJUNTO es:

SI.CONJUNTO (prueba_lógica_1; valor_si_verdad_1, prueba_lógica_2;valor_si_verdad_2....)

Donde:

prueba_lógica_1 es la primera comparación utilizada (con operadores del tipo >, <, =, >=, <=, <>, etc.)
valor_si_verdad_1 es el primer valor que exigimos si prueba_lógica_1 resulta VERDADERO
....
prueba_lógica_127 es la primera comparación utilizada (con operadores del tipo >, <, =, >=, <=, <>, etc.)
valor_si_verdad_127 es el primer valor que exigimos si prueba_lógica_1 resulta VERDADERO

Podemos incluir 127 pruebas lógicas con sus respectivas 127 opciones de valor_si_verdad.

También es necesario comentar que si la función SI.CONJUNTO no encuentra ninguno de los valores que se le proponen, devuelve un error del tipo #N/A.

La función SI.CONJUNTO está disponible también en Excel Online, como podemos observar a continuación:









En Office 365 para Macintosh la función SI.CONJUNTO también está disponible, como podemos ver:






José Manuel Pomares Medrano







miércoles, 13 de junio de 2018

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




domingo, 29 de abril de 2018

La instrucción Resume para control de errores en VBA - Excel


Cuando se produce un error en Excel, se genera un objeto llamado Err que contiene un número de error. La instrucción Resume (en cualquiera de sus formas) reinicia el valor de Err a 0 (cero) y Excel deja de estar bajo una situación de excepción. Resume tiene tres formas sintácticas: Resume, Resume Next y Resume Etiqueta.

Resume
 
La instrucción Resume lleva la línea de ejecución al punto donde se produjo el error. Debe utilizarse fuera de la línea de código donde está la instrucción On Error. Como podemos ver en el ejemplo siguiente, esto se utiliza para dar una nueva oportunidad a la línea que produjo el error:


Debemos interpretar en el código que si no existe una hoja llamada NuevaHoja, se producirá un error y la línea de ejecución salta a la etiqueta Error. Entonces, se comprueba el número de error y si, efectivamente, ha sido por culpa de la inexistencia de la hoja, se crea una nueva. La instrucción Resume se encarga de devolver la línea de ejecución al lugar donde se produjo el error y, por lo tanto, todo continuará correctamente.

Resume Next
 
La instrucción Resume Next Se utiliza siempre inmediatamente después de On Error, Por lo tanto, hablamos de On Error Resume Next. Esta modalidad de Resume omite la línea que provoca el error y enruta la ejecución a la línea siguiente a la que provocó el error. La utilización de esta instrucción debe hacerse cuando ya sabemos que el procedimiento no provoca errores en condiciones normales y queremos que el programa no se detenga y no muestre ningún error bajo una eventualidad que no podemos conocer a priori.

Resume Etiqueta
 
La instrucción Resume Etiqueta lleva la línea de ejecución a una etiqueta que podemos situar en cualquier parte del código. Esto se utiliza si no tenemos intención de solucionar el problema que causó el error y lo que deseamos es continuar con la ejecución en algún punto concreto. Utilizando On Error Resume Etiqueta reinciamos Err = 0, a diferencia del uso de On Error GoTo.



 

José Manuel Pomares Medrano





lunes, 8 de enero de 2018

Mantener siempre actualizada una Tabla Dinámica en Excel (2)


Hace más de cinco años, en este mismo blog, escribí un artículo donde explicaba la forma de "Mantener actualizada constantemente una tabla dinámica". En esa ocasión, el artículo estaba referido a que la tabla refleje constantemente los datos de la tabla origen, pero se asumía que el rango de datos no cambiaba de tamaño. Pero, ¿qué ocurre si el rango de datos de la tabla - origen de datos crece o decrece?. Esta es una pregunta que me han hecho muchas veces últimamente y, además, desde varios ámbitos (empresarial y académico). Así pues, aquí propongo un par de soluciones para este problema, pero para casos distintos.

CASO 1: la tabla origen de datos puede tener formato de Tabla de Excel.

Si la tabla donde están los datos de origen puede tener formato de Tabla de Excel y esto no causa ningún inconveniente, el problema ya no lo es, puesto que tan solo aplicando ese formato a la tabla-origen (y haciendo que nuestra tabla dinámica está basada en ella), la tabla dinámica siempre tendrá como origen de datos la totalidad de datos, aunque hayan crecido o disminuido.

Para aplicar un formato de Tabla de Excel a un rango de celdas donde hay datos, tenemos que asegurarnos de que no hay celdas combinadas y de que cada columna tiene un título de encabezado. Después, desde la ficha Inicio, hacemos clic en "Dar formato como tabla":


Ahora, veremos que aparece una galería de estilos donde tenemos que elegir el que más nos guste. Posteriormente, debemos confirmar los datos del cuadro de diálogo que aparece... y .... ya está.

Por último, sería recomendable dar un nombre a la nueva Tabla de Excel. Lo podemos hacer desde la ficha Diseño (la ficha contextual que aparece cuando tenemos seleccionada una celda de la nueva Tabla). Podemos ver a la izquierda un campo para editar el nombre de la Tabla de Excel.

Una vez hecho todo esto, debemos tener en cuenta que...
  • Si ya teníamos la tabla dinámica construida y basada en el antiguo rango de datos, debemos decirle a Excel ahora que la tabla dinámica debe basarse en nuestra nueva Tabla de Excel. Para ello, debemos ir a la ficha Analizar (teniendo alguna celda de la tabla dinámica seleccionada), hacer clic en "Cambiar origen de datos..." y seleccionar la nueva tabla completamente.

 
  • Si tenemos que construir "desde cero" nuestra tabla dinámica, tan solo tenemos que hacerlo normalmente, seleccionando como origen de datos nuestra Tabla de Excel.
CASO 2: la tabla origen de datos NO puede tener formato de Tabla de Excel.

Si nuestra tabla - origen de datos no puede tener formato de Excel (por cualquier motivo), la solución debe ser radicalmente distinta y debemos recurrir a las macros. Aunque... ¡tranquilos!, tan solo son unas líneas de código.

El proceso sería el siguiente:

  • Hacemos clic con el botón derecho en la pestaña de la hoja donde está la tabla dinámica que debe estar actualizada constantemente. Acto seguido, se abrirá el editor de VBA con una ventana grande a la derecha. Ahí debemos escribir lo siguiente:
       Private Sub Worksheet_Activate()
          ActiveSheet.PivotTables("MiTD").ChangePivotCache _
          ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
          SourceData:=Sheets("MiHoja").Range("B8").CurrentRegion)
      End Sub


      Donde MiTD debe ser sustituido por el nombre de la tabla dinámcia (el nombre de una TD lo podemos ver si tenemos una celda de la misma seleccionada, hacemos clic en la ficha Analizar y miramos a la izquierda).

     Donde MiHoja debe ser sustituido por el nombre de la hoja donde está la tabla - origen de datos.

      Y donde B8 es un ejemplo, pero cada usuario debe escribir ahí la referencia de la primera celda de la tabla - origen de datos (por ejemplo, el primer título de los encabezados). Una condición a tener en cuenta es que la tabla - origen de datos no debe tener filas completas vacías.
  •  Cerramos el editor de VBA, cerramos Excel y aceptamos Guardar los cambios.
Y ya lo tenemos.

La clave de este código la expresión "CurrentRegion", que es la que, de forma automática, detecta cuál es el rango de datos de la tabla, siempre que la referencia de ejemplo B8 sea una celda de la tabla. Por otro lado, comentar que el código se ha introducido en el evento WorkSheet_Activate, por lo tanto, la actualización del rango se realiza cada vez que seleccionamos la hoja de la tabla dinámica.


José Manuel Pomares Medrano





miércoles, 6 de diciembre de 2017

Cómo hacer en Excel un moderno gráfico de anillo en 3 pasos


 Artículo actualizado el 06 / Dic. / 2017 
(Ver al final:  Gráfico de anillo que cambia de color, según los valores de origen)

No hay dashboard (cuadro de mando) que se precie hoy en día sin gráfico de anillo. Es una realidad y, sinceramente, creo que está justificada si pensamos que es un tipo de gráfico que expresa de forma práctica, limpia y rotunda un porcentaje.


Este tipo de gráficos es adecuado para expresar lo "alcanzado" comparado con lo que nos falta por alcanzar. Esto, como es entendible, es aplicable a multitud de situaciones en el mundo económico - financiero y también en otras.

Vamos a aprender a hacer un gráfico de este tipo intentando agregar alguna particularidad que lo haga más atractivo, si cabe. Tomaremos como ejemplo, el realizado para la imagen anterior.

PASO 1 (creación del gráfico de anillo)

Partimos de unos datos, que son los que proporcionan el valor a representar, de los cuales seleccionamos las dos cantidades que vemos a continuación:

 

Activamos la ficha INSERTAR y después elegimos "Gráfico de anillo"


PASO 2 (personalización del gráfico de anillo)

Hacemos clic con el botón derecho del ratón en cualquier parte del anillo del gráfico y seleccionamos la opción "Dar formato a serie de datos". Al hacerlo obtenemos un panel a la derecha (si trabajamos con Excel 2013) o un cuadro de diálogo (si trabajamos con Excel 2007/10) en el que podemos ajustar el "Tamaño del agujero del anillo", así como el ángulo de giro y la separación de secciones:


Hasta ahora tenemos...


... pero nenesitamos deshacernos del título y de la leyenda haciendo clic con el botón izquierdo del ratón en cada uno de esos elementos (para seleccionarlos) y después presionando la tecla "Supr".

También sería conveniente seleccionar desde el borde todo el gráfico y eliminar el relleno (que es blanco hasta ahora) y el propio borde que viene por defecto. Para cambiar estas dos características, debemos tener el gráfico seleccionado y activar la ficha FORMATO para después utilizar las herramientas de "Relleno de forma " y "Contorno de forma" eligiendo, respectivamente las opciones "Sin relleno" y "Sin contorno":


Has aquí tendremos esto...


... pero sería más estético si elimináramos la cuadricula de Excel desde la ficha VISTA (NO teniendo seleccionado el gráfico y después desactivando la casilla de verificación "Líneas de cuadrícula") y también si redujéramos el tamalo del gráfico desde cualquier manija de las esquinas.

También podemos personalizar el color de las secciones del gráfico. Basta con seleccionar cada una de ellas con dos clics separados (para que quede seleccionada una sección sola) y elegir el color desde la herramienta "Relleno de forma" que conocemos de pasos anteriores.

PASO 3 (título y círculo central del gráfico)

Para el título, elegimos una celda que esté situada en la parte inferior del lugar que elijamos para el gráfico y simplemente lo escribimos y aplicamos el formato deseado.

Para el círculo central, debemos activar la ficha INSERTAR, después elegir "Elipse" y a continuación extender el cursor en diagonal PRESIONANDO AL MISMO TIEMPO LA TECLA "MAYÚS" para que la elipse sea un círculo perfecto.

Una vez hecho esto, y asegurándonos de que aún está seleccionado el círculo, ponemos el cursor en la barra de fórumulas y escribimos una fórmula que haga referencia a la celda donde tenemos el porcentaje (INTRO cuando ya esté escrita la fórmula):


Ahora, con el círculo seleccionado aún, y desde la ficha INICIO, podemos:
  • Utilizar las herramientas de tamaño y color de fuente para personalizar el porcentaje
  • Eliminar el borde del círculo (teniéndolo seleccionado, desde la ficha FORMATO / Relleno de formato)
  • Alinear el porcentaje correctamente (centrado vertical y horizontalmente) desde los botones siguientes de la ficha INICIO:



Una vez hecho lo anterior, solo queda centrar el círculo sobre el gráfico y también colocar todo encima de una celda que contenga el título deseado:



El espacio de Mac

En cuanto a las diferencias en Mac respecto de todo lo dicho hasta ahora, diremos que:

  • Para crear el gráfico debemos ir al siguiente menú (eligiendo la opción "Anillos"):


  • Para crear el círculo central, podemos ir al botón (que vemos a la izquierda de la imagen) que activa el cuadro de diálogo "Multimedia"...



... el resultado es, por supuesto, similar:



Actualización 6/12/2017


Gráfico de anillo que cambia de color, según valores de origen

A petición de un lector en un comentario de este artículo, lo he actualizado para mostrar cómo es posible obtener el efecto de un gráfico que cambia de color cuando los valores superan o no alcanzan una cantidad.

Para conseguirlo, debemos realizar, en principio, todo lo que se muesrta en la imagen de abajo...


... son dos gráficos de anillos exactamente iguales (especialmente en tamaño), excepto tres cosas:
  • Uno tiene como origen de datos las celdas D2:D3 y el otro, las celdas D19:D20
  • El otro tiene la porción de anillo protagonista de color naranja - rojo y el otro la tiene azul
  • Un gráfico no tiene color de fondo (está sin relleno)
Las celdas de origen de datos tienen unas fórmulas que, dependiendo de los valores que hay que evaluar (celdas B19:B20) no devuelven un valor de error o el valor que se debe mostrar. Puesto que cuando los valores son errores, el gráfico no puede mostrarlos... ese gráfico se convierte en transparente y deja que se vea el otro gráfico... que sí tiene valores.

Finalmente, tan solo queda poner un gráfico (el transparente) exactamente sobre el otro para que el efecto sea la visualización de los datos para uno de los casos....


y la visualización de los datos.... para otro de los casos...




José Manuel Pomares Medrano






martes, 21 de noviembre de 2017

Trabajo con arrays en VBA para Excel


Un array (arreglo, matriz o composición ordenada) es una variable que puede almacenar y gestionar varios valores colocándolos en la memoria RAM de nuestro equipo en forma de matriz. Los arrays pueden tener una disposición matricial de una dimensión (como una simple lista de elementos), de dos dimensiones (en modo tabla, donde las filas son una de las dimensiones y las columnas la otra), pero también pueden tener una disposición en tres dimensiones (como un cubo) o incluso en n dimensiones.




En Excel, en la mayoría de casos se trabaja con arrays de 1 o de 2 dimensiones, Por ejemplo:




Los arrays se suelen utilizar para procesar grandes cantidades de datos de forma muy rápida. Están indicados en los casos en los que la información que hay en un conjunto de datos (normalmente un rango de Excel) debe ser procesada (reorganizada, calculada, modificada, etc.).

La filosofía de trabajo con un array debe ser clara:
  1. Crear el array
  2. Asignar valores (llenar el array) con la información (normalmente se hace mediante bucles For...Next)
  3. Volcar la información en un rango de Excel o en un objeto
1. Crear un array

OPCIÓN A). Solo declarar el array (y después dimensionarlo)

Por ejemplo:

   Dim miArray() As TipoDato
   ReDim miArray(miVariable, 7)

... o también...
   Dim miArray() As TipoDato
   ReDim miArray(3, Range("A1"))
 
Esta opción se utiliza cuando el número de elementos de cada dimensión no es conocido en el momento de escribir la línea de la declaración. Un ejemplo de esto es cuando ese número de elementos deben ser variables, constantes o referencias a rangos de Excel. Hay que tener en cuenta que, en principio, solo es posible dimensionar un array con un par de números. Es decir, el código siguiente no sería admitido:

   Dim miArray(miVariable,4) As Integer

Así pues, esta Opción A (como hemos visto anteriormente) consistiría en declarar el array con la instrucción Dim y posteriormente, dimensionarla con ReDim... ¡¡Qué SÍ permite variables, constantes y referencias a celdas!!.

 OPCIÓN B). Declarar el array y dimensionarlo al mismo tiempo

Por ejemplo:

   Dim miArray(6,3) As Integer

2. Asignar valores al array (llenarlo)

OPCIÓN A). Posición a posición con líneas de código

Por ejemplo:

   miArray(2,3) = Range("A1")
   miArray(4,3) = 836

OPCIÓN B). Posición a posición mediante un bucle

Por ejemplo:

   Dim miArray(6) As String

   For i = 1 To 6
      miArray(i) = i
   Next i

En esta fase de llenado o asignación de valores al array es cuando se puede procesar la información al mismo tiempo que se produce el llenado. Por ejemplo:

   Dim miArray(6) As String

   For i = 1 To 6
      miArray(i) =Left(Cells(i, 3)) & "-" & Right(Cells(i, 4))
   Next i

Con el código de arriba se llena la única columna de miArray* con los 3 caracteres de la izquierda de los datos que hay en las 6 primeras celdas de columna 3 de Excel, más un guion, más los 3 caracteres de la derecha que hay en las 6 primeras celdas de la columna 4 de Excel.

*Trabajando con arrays de una sola dimensión (una sola columna o fila) no es necesario especificar un par (x, y)

3. Volcar la información

Una vez que el array está lleno con la información procesada (si hubiera sido necesario), lo normal es volcar los datos en un Rango de Excel o en un objeto, como puede ser un combo - box o un cuadro de lista de un UserForm. Un ejemplo del primer caso sería:

Range("A1:A6")  = miArray

¡¡Atención!!, el rango destino debe tener el mismo tamaño (filas x columnas) que el array.
Otra cuestión que podemos apreciar en la última línea de código de ejemplo es que, al hacer referencia al array de forma global (no por posiciones), no es necesario aludir al par (x, y).



Los arrays tienen algunas ventajas frente a las variables - matriz:
  1. Los arrays pueden ser dimensionados para cualquier tipo de dato, no solo el tipo variant.
  2. Los arrays pueden ser llenados de valores uno a uno (posición a posición) desde la primera vez que se les asignan valores. Recordemos que las variables-matriz deben ser llenadas en bloque desde un rango de celdas de Excel.
  3. Los arrays pueden tener más de 2 dimensiones y, además, estas pueden ser variables, referencias a rangos, constantes, etc.

José Manuel Pomares Medrano