domingo, 23 de junio de 2019

Listas Personalizadas

Sabemos que Excel cuenta con una cierta cantidad de Listas que nos sirven a la hora de rellenar u ordenar alguna tabla de datos.

Ahora veremos cómo podemos modificar o incrementar esta lista según nuestras necesidades.
Imaginemos una tabla de datos (ventas, compras, etc) que necesitamos ordenarla por mes. Seleccionaremos el rango de la tabla y desde menú Datos, Ordenar optaremos por campo MESES.

Pero es posible que nuestro ejercicio fiscal no coincida con el inicio de año o sea enero, sino que corresponda a otro período como puede ser Abril-Marzo.
En ese caso necesitamos otro ordenamiento lo que nos llevará  a crear nuestra propia lista.
Seleccionaremos nuevamente el rango de la tabla, menú Datos-Ordenar. Dejamos nuevamente la columna MESES y en Criterio de ordenación optamos por Lista Personalizada.

Esto nos abrirá una segunda ventana desde la que podemos crear otra lista personal. En el campo de la derecha se ingresará el primer mes (Abril para nuestro caso) presionando Enter a continuación. Y así completaremos la tabla de los 12 meses. A continuación presionamos el botón Agregar y luego Aceptar para guardar la nueva lista.

Ya tenemos una nueva lista para ordenar tablas por meses. 

NOTA: para quitar una lista personal se puede ingresar desde la opción ORDENAR como lo hemos hecho para crearla. Seleccionamos la lista que deseamos quitar y presionamos el botón  Eliminar y Aceptar para guardar los cambios.

OTRO MODO DE CREAR-ELIMINAR LISTAS PERSONALES:
Esta tarea también puede ser realizada desde el botón Archivo (Opciones de Excel), Avanzadas y buscar hacia abajo el botón que menciona 'Modificar Listas Personalizadas.
Este botón nos abrirá la ventana de la imagen anterior, tanto para crear como para eliminar listas agregadas.

Veamos el siguiente ejemplo:
Con frecuencia requerimos en nuestros libros una lista de Provincias, Sucursales o algún otro tipo de información repetitiva. Entonces ya dejaremos creada la lista desde esta herramienta de 'Listas Personalizadas'.
Podemos trabajar directamente desde la ventana que se observa en imagen anterior, como en el ejemplo de los meses, o previamente completaremos un rango de celdas y luego lo importamos.


Estas listas creadas aparecerán en cada libro Excel hasta que decidamos eliminarlas desde esta misma ventana.

VER VIDEO N° 26.

jueves, 13 de junio de 2019

BUSCAR información mediante VBA

Siguiendo con el tema de las búsquedas ya comentadas en entradas del mes de Febrero 2019 vamos a desarrollar 2 tipos de búsquedas simples, es decir con datos únicos.
NOTA: En una búsqueda siempre se debe contemplar la posibilidad de que no se encuentre el dato buscado.

1 - Con método FIND: 
Contamos con una hoja donde se encuentran 2 tablas semestrales (Hoja2). La intención es volcar datos de esas tablas a otra hoja (Hoja1) donde se encuentra el resumen anual.
En este ejemplo solo se tendrá 1 registro o contrato por cada mes.



La macro colocada en un módulo será la siguiente y se ejecutará desde la Hoja1 (hoja resumen anual).
Sub macroBusca()
'x Elsamatilde
'recorre la col A de hoja1... hoja activa
Sheets("Hoja1").Select
fini = Range("A" & Rows.Count).End(xlUp).Row
'tomo el rango desde fila 2 hasta la última con datos (fini) ....AJUSTAR
For i = 2 To fini
    dato = Range("A" & i)
   Set busco = Sheets("Hoja2").Range("B:H").Find(dato, LookIn:=xlValues,  lookat:=xlWhole)
        'si lo encuentra vuelca el resultado en col B y C de hoja 1
    If Not busco Is Nothing Then
        Range("B" & i) = busco.Offset(0, 1)   'devuelve info a derecha
        Range("C" & i) = busco.Offset(0, 2)
    Else
        'opcional: dejar un texto en celdas B y C
        Range("B" & i) = "Sin datos": Range("C" & i) = "Sin datos"
    End If
'sigue con la otra fila
Next i
MsgBox "Fin de la búsqueda"
End Sub

Con el método FIND programado de este modo (con control de dato encontrado o no - ver aclaraciones en entrada de Febrero 2019) le indicamos qué resultado debe volcar en caso de no encontrar algún registro o mes en este caso.

2- Con resultado de la función BUSCARV:
Para este caso, contaremos con una tabla de datos de varias columnas, de las que solo necesitaremos obtener el resultado de dos de ellas. La tabla resumen será igual a la anterior.

Suponiendo que en nuestra tabla de resumen no queremos colocar fórmulas sino solamente el valor encontrado, la macro a utilizar sería la siguiente:
Sub macroFormulaControlada()
'x Elsamatilde
Sheets("Hoja1").Select
'recorre la col A de la hoja activa hasta la última celda con datos.
fini = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To fini
    dato = Range("A" & i)
    'el control de error nos permite devolver otro valor o texto en caso de dato no encontrado
    On Error Resume Next
    Range("B" & i) = Application.WorksheetFunction.VLookup(dato, Sheets("Hoja2").Range("B:D"), 2, False)
    'si la función devolvió error se coloca un texto aclaratorio
    If Err.Number > 0 Then Range("B" & i) = "Sin datos"
    Range("C" & i) = Application.WorksheetFunction.VLookup(dato, Sheets("Hoja2").Range("B:D"), 3, False)
    If Err.Number > 0 Then Range("C" & i) = "Sin datos"
Next i
                     End Sub

Aquí se contempla si la función devuelve un número de error (Err.Number > 0) . Sería como cuando la función BuscarV nos devuelve el valor #N/A . Y en ese caso colocaremos otro resultado.

Descargar ejemplo desde aquí

                                   Ver VIDEO N° 25.

martes, 9 de abril de 2019

Comentarios en Excel con VBA

En entrada de fecha Noviembre 2018 (video 17 del canal) hablamos de los estilos en Comentarios: diferentes formatos de fuente, colores, formas e incluso con imágenes.

En esta oportunidad veremos cómo programar el agregado de comentarios en diferentes eventos.

Al cambio en celdas.
1 - El primer ejemplo se trata de agregar un comentario a medida que vamos rellenando la tabla con datos. Si el importe registrado en col C es < a cierto valor se indicará con un comentario.
Nota: para este ejemplo también es válida la opción de dejar un formato condicional. Pero el tema que nos ocupa hoy son los comentarios.

En el Editor, seleccionaremos el objeto HOJA donde se encuentre nuestra tabla de datos y allí colocaremos el siguiente código. El método utilizado es AddComment.

      Private Sub Worksheet_Change(ByVal Target As Range)
      'no se evalúan los cambios en fila 1
      If Target.Row = 1 Then Exit Sub
      'si el cambio se da en col 3 (C)
      If Target.Column = 3 Then
          'si la cifra es < 100 se deja nota
          If Target.Value < 100 Then Target.AddComment "Atención con este importe."
      End if
      End Sub



2 - En el segundo ejemplo se trata de agregar texto a comentarios que ya se encuentran en una celda.
Se trata de rellenar una tabla de días. Como son diferentes usuarios los que pueden registrar información se coloca un comentario por cada usuario que ha modificado las celdas de un rango de días. La información que se agrega es el nombre de usuario y el importe que ha registrado.

      Private Sub Worksheet_Change(ByVal Target As Range)
      'no se evalúan los cambios en fila 1
      If Target.Row = 1 Then Exit Sub
      'si el cambio se da en tabla de días ubicada a partir de col G
      If Target.Column > 6 Then
          'se agrega cada usuario e importe registrado en celdas de días
          On Error Resume Next
          comento = Target.Comment.Text
          'si es la 1ra vez se crea el comentario
          If comento = "" Then
              Target.AddComment Application.UserName & "_" & Target.Value
          Else
              Target.Comment.Text Text:=comento & Chr(10) & Application.UserName & "_" &          Target.Value
          End If
      End If
      End Sub

Primero se evalúa si la celda ya tiene comentarios. Si no lo tiene se agregará con el método AddComment visto en punto anterior.
Si lo tiene, se agrega un salto de línea y el nuevo comentario que será el nombre del usuario y el importe ingresado.


Agregar comentarios desde un proceso general.
También es posible contar con un proceso que se ejecutará desde cualquier otro evento, por ejemplo al clic de un botón, y allí evaluar si colocar o no un comentario en algunas celdas.
En el ejemplo se trata de armar un resumen de cierta tabla de datos y evaluar el resultado de cierta columna. Si es < a cierto valor se dejará un comentario en esa celda.

      Sub pasaTotal()
      'x Elsamatilde
      'fin del rango de datos
      fini = Range("E1").End(xlDown).Row
      desti = Range("E" & Rows.Count).End(xlUp).Row + 1
      'se copia el rango E:F
      Range("E2:F" & fini).Copy
      Range("E" & desti).PasteSpecial xlPasteValues
      'se recorre el rango y coloca comentarios si el total es < 10
      For i = desti To Range("E" & Rows.Count).End(xlUp).Row
          If Range("F" & i) < 10 Then Range("F" & i).AddComment "Revisar valor"
      Next i
      End Sub
Esta macro se coloca en un módulo del Editor. Y se ejecutará desde un atajo de teclado, un botón o desde el mismo menú Desarrollador (o Programador). También es posible que sea llamada desde otro proceso con la instrucción:
      Call pasaTotal


El tema Comentarios da todavía para mucho más. En entradas siguientes seguiré desarrollando el tema.
                                   Descargar ejemplo desde aquí

                                   Ver VIDEO N° 24.

martes, 12 de febrero de 2019

El método FINDNEXT para realizar búsquedas contínuas.

Siguiendo con el tema iniciado en video 16 de mi canal (entrada actualizada en mes de Febrero 2019) para una búsqueda con el método FIND, vamos a tratar ahora de realizar una búsqueda continua, con más de un criterio.
Para ello haremos uso del método FINDNEXT.

Opción 1:
Partiremos de una tabla de varias columnas donde una de ellas será la del primer criterio (mes) y otra contendrá datos del segundo criterio (art.). La tarea será calcular el acumulado de registros que coincidan en MES y ART.

Si bien esto puede ser resuelto con un filtro y la función Subtotales, vamos a ver cómo sería la programación en VBA utilizando los métodos FIND para encontrar el primer elemento y FINDNEXT para continuar la búsqueda hasta cubrir toda la tabla.
En el Editor de macros insertaremos un botón (que lo asociaremos al botón 'Actualizar' de la hoja) y allí copiaremos el siguiente código.

     Sub busquedaRepetidos()
     'x Elsamatilde
     'busca el valor de F1 en todas las apariciones en hoja activa
     dato = Range("F1")
     'se inicia la búsqueda en col A de la hoja activa
     Set busco = Range("A:A").Find(dato, LookIn:=xlValues, lookat:=xlWhole)
     'si no se encuentra ninguna coincidencia se notifica y finaliza el proceso
     If busco Is Nothing Then MsgBox "Dato no encontrado": Exit Sub
     'guarda la 1er fila encontrada
     filx = busco.Row
     'inicia un bucle
     Do
         'compara la col B con criterio en G1
         If busco.Offset(0, 1) = [G1] Then
             totx = totx + busco.Offset(0, 2)
         End If
         'repite la búsqueda
         Set busco = Range("A:A").FindNext(busco)
     Loop While Not busco Is Nothing And busco.Row <> filx
     'terminó la búsqueda. Se coloca el acumulado en H1
     [H1] = totx
     End Sub

Opción 2:
También podríamos utilizar método FINDNEXT para obtener el acumulado de todo enero, sin uso del segundo criterio. Lo único que se quitaría en este caso es la comparación con la celda G1.
La macro en la parte del bucle DO....LOOP WHILE nos quedará del siguiente modo:

     'inicia un bucle
     Do
         totx = totx + busco.Offset(0, 2)
         'repite la búsqueda
         Set busco = Range("A:A").FindNext(busco)
     Loop While Not busco Is Nothing And busco.Row <> filx


Descargar libro de ejemplo desde aquí.

VIDEO N° 23

Ver más macros con bucles en nuevo manual Bucles en Excel.

Búsquedas con VBA - Método FIND

Entre las funciones Excel quizás la estrella de todas es BUSCARV, la que nos permite buscar un dato y devolver el contenido de otra columna de la fila encontrada.

En VBA podría decir que la estrella es FIND , que nos permite buscar un dato y obtener su ubicación para luego movernos o devolver otros valores en relación a la referencia encontrada.

La instrucción con todos sus argumentos es:
Find (What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

What: dato a buscar
After: a partir de dónde se realiza la búsqueda
Lookin: búsqueda entre valores (xlValues) o fórmulas (xlFomulas)
LookAt: coincidir con el contenido total (xlWhole) o parcial (xlPart) de la celda.
SearchOrder: realizar la búsqueda por filas (xlByRows) o por columnas (xlByColumns)
SearchDirection: búsqueda siguiente (xlNext) o anterior (xlPrevious).

Pero como hay varias sintaxis posibles vamos a ver aquellas que pueden llegar a fallar (ejemplos 1 a 4) y aquellas más recomendables (ejemplos 5 a 7)

Ejemplo 1: sin control de error en caso de dato no encontrado
       
      Cells.Find (qué se busca, a partir de dónde se busca, cómo se busca).Activate

Con Cells  le estamos indicando la hoja completa, no acotada a un rango en especial.
Con Activate o Select le estamos diciendo que cuando lo encuentre ya lo deje seleccionado.

Ejemplo 2:

       If j = 1 Then Set busca = ha.Find(numero)
      If j > 1 Then Set busca = ha.FindNext(busca)
      celda = busca.Address


Pero en cualquiera de los 2 casos, el código fallará si no encuentra el dato y por lo tanto no lo puede seleccionar o no puede obtener la dirección de la celda ya que no se encontró el dato a buscar.

Entonces habrá que colocar previamente una instrucción como la siguiente:
      On Error goto NoEncontrado
       Cells.Find (qué se busca, a partir de dónde se busca, cómo se busca).Activate

Y una entrada donde se instruya qué hacer en caso de no encontrar el dato.
      NoEncontrado:
           MsgBox "El dato buscado no se encuentra en la hoja activa."
           Exit sub

NOTA: también es posible utilizar On Error Resume Next  pero esto hará que siga con el resto de las instrucciones lo que en algún momento dará un resultado equivocado.

Ejemplo 3: acotando el rango de búsqueda pero a partir de celda seleccionada
   
      Range("A1:H1").Find(What:="marzo", After:=ActiveCell, SearchOrder:=xlByRows).Select

La misma instrucción puede escribirse del siguiente modo. En este caso habrá que colocar una coma por cada argumento que se omite.
     Range("A1:H1").Find("marzo", ActiveCell, , xlByRows).Select

Aquí le indicamos el rango y con Select le instruímos que seleccione la celda encontrada.
Pero con After le estamos indicando que lo busque a partir de la celda activa.
Y esto nos dará error si la celda activa se encuentra a continuación del dato buscado o en otras filas.

Ejemplo 4: no indicando orden o dirección de búsqueda cuando el dato puede encontrarse en distintas celdas.
En la siguiente imagen se observa que el texto 'marzo' se encuentra en 2 lugares.

Para que no nos devuelva un dato erróneo debemos instruirle a Excel en qué dirección buscarlo con el argumento SearchOrder. En este ejemplo le indicamos que lo busque por columnas por lo que devolverá el de la celda A3:
        Cells.Find("marzo", , , SearchOrder:=xlByColumns).Select

Ejemplo 5: 
El método de búsqueda guarda los argumentos LookIn, LookAt y SearchOrder de la última búsqueda efectuada. Por lo tanto si no recordamos cuáles fueron esos argumentos utilizados debemos declararlos en nuestra instrucción para no correr riesgos de obtener resultados erróneos.
Una instrucción segura debiera incluir entonces:

     Range("A1:D8").Find("ma", Lookin:=xlvalues, lookAt:=xlPart, SearchOrder:=xlbyRows)



LookAt:=xlPart indica que el texto buscado debiera estar incluido en el contenido de la celda.
LookAt:=xlWhole indica que el texto debe coincidir con el contenido de la celda


Ejemplo 6: 
Al realizar búsquedas dentro de fórmulas, los argumentos que debemos incluir son:
LookIn:=xlFormulas
LookAt:=xlPart


Ejemplo 7: utilizando una variable declarada con SET
Este método puede ser el más preciso considerando la declaración de argumentos según cada caso.
Aquí buscamos en un rango de otra hoja, por columnas, un texto parcial y luego ejecutamos varias acciones con la celda encontrada. Si no se encuentra coincidencias se envía un mensaje.
     Sub buscando()
     Set ho2 = Sheets("Hoja2")
     Set busco = ho2.Range("A1:D8").Find("ma", LookIn:=xlValues, lookAt:=xlPart,     SearchOrder:=xlByColumns)
     If Not busco Is Nothing Then
         'podemos obtener todas las referencias del dato encontrado sin necesidad de seleccionarlo
         filx = busco.Row
         colx = busco.Column
        'traer un rango a partir de la celda encontrada
         rgo = Range(Cells(filx, colx), Cells(filx, colx + 2)).Address
         ho2.Range(rgo).Copy Destination:=ActiveCell
         'colorear celda encontrada
         busco.Interior.ColorIndex = 3
     Else
         MsgBox "No se encontró el dato buscado en la Hoja 2"
     End If
     End Sub

NOTA: Este método no requiere que se seleccione la hoja para obtener las referencias de la celda encontrada ni para realizar alguna tarea allí, como copiarla, darle color, obtener un rango a partir de la celda encontrada y tantas otras tareas.

Descargar ejemplo desde aquí.

ver VIDEO N° 16.


sábado, 26 de enero de 2019

Minigráficos - Formatos con gráficos

Si necesitamos graficar rápidamente algún informe o ciertas columnas de una hoja de datos podemos recurrir a 2 herramientas que nos dejarán resultados en la misma columna (o en otra auxiliar). Ocupando menor espacio que un gráfico tradicional.

1 - Formatos con gráficos:
Entre los nuevos estilos de Formato Condicional podemos observar el que simula un gráfico de barras:
Se selecciona el rango que se desea graficar y se elige algún estilo de los mostrados en la opción 'Barras de datos'.
El resultado puede verse como en este ejemplo:
También podemos agregar otro formato más, como el de 'Escalas de color', quedándonos los 2 formatos superpuestos de este modo:

2- Minigráficos:
Esta herramienta requiere de una columna auxiliar donde colocaremos el minigráfico por cada fila de nuestro informe.
Seleccionamos la celda donde dejaremos el minigráfico y desde menú Insertar, Minigráfico se elegirá el modelo.

Se nos abrirá la siguiente ventana que nos permite seleccionar el rango de datos.

Si hemos seleccionado una sola celda para el gráfico, debemos seleccionar solo una fila de datos. Luego arrastraremos el resultado al resto de las celdas que necesitemos mostrar un gráfico.

Otro modo sería seleccionar todas las celdas de resultado (aquellas donde vamos a dejar el gráfico) antes de llamar a la herramienta. Y desde la ventana seleccionar todas las filas de datos.

En imagen siguiente vemos el resultado de 2 modelos de minigráficos: columnas y líneas.
Para borrar el gráfico de alguna celda seleccionarla con clic derecho, optar por Minigráfico, Borrar.

Para modificar el aspecto de los gráficos seleccionar una celda y desde el menú Herramientas de Minigráficos, Diseño se nos presentarán varias opciones: ajuste de color, puntos más altos o más bajos, etc.


VER VIDEO N° 22.

domingo, 13 de enero de 2019

Cómo crear Funciones personales

Si bien Excel presenta gran cantidad de funciones y en cada versión se van agregando otras nuevas, en ocasiones no encontramos la que nos hace falta para ciertos cálculos especiales.
En esos casos, generalmente recurrimos a una macro. Pero si necesitamos realizar ese cálculo de modo frecuente.... ¿porqué no desarrollar nuestra propia función?

En este video se puede seguir el paso a paso de cómo se crearon 2 funciones personales:
1- Encontrar un número en una matriz:
    =BUSCARM(nro; rango)


2- Sumar de modo alterno en un rango de una columna.
     = SUMA_ALTERNA(rango)

Las funciones se desarrollan en un módulo del Editor.
IMPORTANTE: las funciones personales deben ser desarrolladas para ser utilizadas en cualquier escenario. Verificar si el resultado obtenido coincide con los resultados esperados en los distintos escenarios.

A continuación los códigos para cada función que encontrarán en libro que se deja para descargar.

      Function BUSCARM(dato, rango)
      'buscar en la matriz
      For Each cd In Range(rango.Address)
          If cd.Value = dato Then BUSCARM = cd.Address(False, False)
      Next cd
      End Function

      Function SUMA_ALTERNA(rango)
      '1er fila y col del rango
      fil = Range(rango.Address).Row
      col = Range(rango.Address).Column
      'total de filas
      filas = Range(rango.Address).Rows.Count
      'se recorre el rango en saltos de 2
      For i = fil To filas + fil Step 2
          tot = tot + Cells(i, col)
       Next i    
     SUMA_ALTERNA = tot
      End Function


3- Sumar de modo alterno en un rango de una columna, con criterio.
Si además necesitamos sumar según alguna condición al estilo SUMAR.SI, agregaremos como segundo argumento el criterio deseado:
     = SUMA_ALTERO(rango;criterio)
Lo que cambiará es la instrucción de suma, incuyendo la condición. En este ejemplo se sumarán los conceptos que incluyan el texto 'Adaptador')

      Function SUMA_ALTERO(rango, criterio)
      'instrucciones
      For i = fil To filas + fil Step 2
          If InStr(1, Cells(i, col - 1), criterio) > 0 Then tot = tot + Cells(i, col)
      Next i
      'resto de instrucciones
      End Sub

Descargar libro de ejemplo
Ver VIDEO N° 21.