Comentarios en celdas - una alternativa

Todo usuario de Excel conoce la posibilidad de insertar comentarios en las celdas de la hoja. Los comentarios son una manera muy práctica de asociar observaciones al contenido de la celda. Un pequeño triángulo rojo en el ángulo superior derecho de la celda nos indica la existencia del comentario

Con el método tradicional es muy fácil  introducir y modificar comentarios, pero en ciertas situaciones pueden presentarse inconvenientes. Por ejemplo, según las deficiones por defecto, el comentario se hace visible cuando apuntamos a la celda que lo contiene. Esto puede ocultar el contenido de celdas contiguas que pueden ser necesarias para las acciones a tomar por el usuario. Otro inconveniente es que el comentario desaparece al dejar de apuntar a la celda lo cual limita su uso para dar instrucciones al usuario.

Una alternativa es usar cuadros de texto u cualquier otra forma, combinados con un poco de código Vba (macros) para hacerlos aparecer o desaparecer.

Veamos cómo aplicar esta técnica al ejemplo de la imagen más arriba.

Empezamos por agregar un icono que indique la posibilidad de recibir instrucciones usando, por ejemplo, una imagen prediseñada

Luego creamos un cuadro de texto con las instrucciones y activamos el panel de selección

Tal como hicimos con el icono de información, agregamos un icono "x" al cuadro de texto que nos servirá para que el usuario pueda cerrar (volver invisible) el cuadro.

En el panel cambiamos el nombre por defecto de los objetos por algo más explícito; por ejemplo, en lugar de "CuadroTexto 3" hacemos un clic sobre el nombre  lo reemplazamos por "ctInstrucciones". Esto nos será útil para simplificar nuestro código

Ahora tenemos que crear el código para volver visible o invisible el cuadro de texto. Activamos la grabadora de macros y ocultamos el cuadro de texto pulsando el "ojo" a la derecha del nombre del objeto en el panel de selección; hacemos lo mismo con la imagen de la "X".

El código resultante es el siguiente:

Sub ocultar_objetos()
'
' ocultar_objetos Macro
'
    ActiveSheet.Shapes.Range(Array("ctInstrucciones")).Visible = msoFalse
    ActiveSheet.Shapes.Range(Array("imCerrar")).Visible = msoFalse
End Sub

Como sucede con todo código creado por la grabadora, podemos simplificarlo a éste:

Sub ocultar_objetos()
'
'
    ActiveSheet.Shapes. _
        Range(Array("ctInstrucciones", "imCerrar")).Visible = msoFalse

End Sub

Ahora necesitamos un código para mostrar los objetos, para lo cual sencillamente copiamos el código anterior cambiando el valor "msoFalse" a "msoTrue"

Sub mostrar_objetos()
'
'
    ActiveSheet.Shapes. _
        Range(Array("ctInstrucciones", "imCerrar")).Visible = msoTrue

End Sub

El último paso es ligar la macro para mostrar los objetos al icono "i" yla macro para volverlos invisibles al icono "x"

Este video demuestra el funcionamiento

Búsqueda horizontal con COINCIDIR

Lo más memoriosos de mis lectores seguramente recordarán la épica nota sobre búsqueda con la función COINCIDIR en columnas múltiples. En esa nota señalábamos que  la función COINCIDIR nos permite calcular la posición que ocupa un elemento determinado en un rango, pero que si el rango de búsqueda comprende más de una columna (o fila), la función da un resultado de error.
En la nota mostrábamos dos formas de realizar la búsqueda con COINCIDIR a lo largo de varias columnas: con fórmulas y con macros.

Ahora consideremos este problema: dada esta matriz de valores numéricos

queremos encontrar en qué fila aparece por primera vez el número 8. Como puede apreciarse el 8 aparece por primera vez en la fila 2.

Si aplicamos la fórmula que postulamos en la nota anterior, veremos que recibimos un resultado erróneo: ka fórmula da el resultado 3 cuando debería ser 2.

=SI.ERROR(COINCIDIR(H4,C4:C13,0),SI.ERROR(COINCIDIR(H4,D4:D13,0),SI.ERROR(COINCIDIR(H4,E4:E13,0),"ND")))

La fórmula realiza la búsqueda en el primer vector y si no encuentra una coincidencia, realiza la búsqueda en el segundo vector y así sucesivamente. Es decir, la búsuqeda en la matriz es "vertical", cuando lo que queremos hacer es una búsqueda "horizontal".

La solución obvia es usar vectores "horizontales", por ejemplo =SI.ERROR(COINCIDIR(H4,C4:E4,0),SI.ERROR(COINCIDIR(H4,C5:E5,0)....etc. El inconveniente de esta fórmula es obvio: en lugar de tres funciones COINCIDIR, tendríamos que combinar 10 funciones, tantas como las filas de la tabla. Imaginemos ahora una tabla con 1000 filas!

La solución en este caso es usar una función definida por el usuario que llamaremos "fila_celda"

Function fila_celda(Valor_Buscado, Matriz_Busqueda As Range)
    Dim rngCell As Range
  
    For Each rngCell In Matriz_Busqueda
        If rngCell.Value = Valor_Buscado Then
            fila_celda = rngCell.Row - Matriz_Busqueda(1).Row + 1
            Exit Function
        End If
    Next rngCell
   
    fila_celda = "inexistente"

End Function

Esta función usa dos argumentos: Valor_buscado y Matriz_Busqueda

La función realiza la búsqueda a lo largo de las filas como puede verse en el vector del argumento Matriz?Busqueda: 11,1,11;9,8,3;...(nótese el uso de la coma y el punto y coma).

El código de la función debe guardarse en un módulo común del editor de Vb. Para que estpe disponibles para todos los cuadernos abiertos, podemos guardarlo en el libro Personal.

Una vez guardada, la función aparecerá en el asistente de las funciones bajo la categoría "Definida por el usuario"

Copiar filas de una tabla filtrada en Excel con Vba (macros)

Numerador automático para facturas es una de las páginas más populares de este blog. Una de las consultas más frecuentes es cómo hacer para guardar copias de las facturas, así que, después de varios años (la nota es del 2008), decidí rehacer el modelo que incluye, además de corregir varios bugs, la posibilidad de manejar los datos de las facturas en una base de datos.
Toda esta introducción viene a cuento de que una de las rutinas que tuve que desarrollar para el nuevo modelo, que estaré publicando en breve, se basa en filtrar las filas de la base de datos y copiarlas a otra hoja (lo hoja que contiene la copia de la factura).
En esta nota mostraré dos métodos eficientes para hacerlo. Como ejemplo utilizaremos los datos de ventas de la base de datos Northwind

Dado que los métodos de Excel, como Autofiltro, suelen ser más eficientes que el código que podamos escribir, podemos grabar las acciones y luego mejorar el código reemplazando, por ejemplo, las referencias a rangos por variables.
Supongamos que queremos copiar a otra hoja todas las ventas a la Argentina. Después de aplicar Autofiltro, grabamos las acciones para el copiado obteniendo este código

Sub Macro1()

'
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Hoja2").Select
    ActiveSheet.Paste
End Sub

Hacemos dos mejoras a este código

• usamos la propiedad CurrentRegion de Range en lugar de Selection.End(xlToRight) y Selection.End(xlDown),

• eliminamos los Select abreviando el código

Sub Macro2()
'
    Selection.CurrentRegion.Copy
    Sheets("Hoja2").Paste
 
End Sub


Pero existe una posibildad más sencillla usando AutoFilter.Range

Sub Macro3()

    ActiveSheet.AutoFilter.Range.Copy
    Sheets("Hoja2").Paste

End Sub

La ventaja de usar AutoFilter.Range es que funciona aún si la celda activa no pertenece a la tabla filtrada.

Gráficos Excel - Fijar máximos del eje vertical con macro

En la nota anterior sobre el tema mencionamos que la solución tradicional para coordinar el máximo del eje de las Y (eje de los valores en términos de Excel) es usar una macro.
El método manual, con fórmulas y series auxiliares, tiene la ventaja que puede ser usado también en entornos donde las macros están bloqueadas, por ejemplo,  por el administrador de la red. Las deventajas son menores pero existen:

• el valor máximo es determinado por el algoritmo de Excel, por lo que siempre será cercano al valor deseado pero no siempre exactamente igual;
• hay que modificar cada uno de las gráficos que queremos coordinar.

Las macros son más flexibles y eficientes, ya que podemos establecer exactamente el valor que queremos que aparezca como máximo y podemos aplicarlas a todos los gráficos de la hoja.

El código es muy sencillo y podemos usarlo de dos maneras distintas: como sub (macro común) o como evento. Al usarlo como sub, tenemos que crear también la forma de disparar el código, por ejemplo, ligando la macro a un botón; al usar eventos podemos hacer que la macro corra cuando, por ejemplo, el usuario cambia algún dato en la tabla de los datos.

Como en el ejemplo de la nota anterior, tenemos dos gráficos basados en dos tablas de datos y en la celda A3 calculamos el valor máximo de ambas tablas (hemos creado el nombre "cellValMax" que se refiere a la celda A3)

Como podemos ver en la imagen, la escala del eje de las Y es diferente en cada gráfico y por lo tanto la comparación visual es engañosa.
Como mencionamos más arriba podemos usar el código como sub (macro) o como evento; el código es el mismo pero la forma de hacerlo correr es distinta.

Uso del código como sub (macro)

En un módulo compun del editor de Vb, ponemos este código

Sub coordinar_max_graficos()
   Dim dbMax As Double
   Dim objCht As ChartObject

   dbMax = Range("cellValMax").Value
  
   For Each objCht In ActiveSheet.ChartObjects
      With objCht.Chart.Axes(xlValue)
        .MaximumScale = dbMax
        .MinimumScale = 0
      End With
   Next objCht
End Sub

En la hoja que contiene los gráficos agregamos un botón ligado a la macro




Uso del código como evento

Para usarlo como evento el código debe ir en un módulo de la hoja que contiene los gráficos. Una de las formas de acceder a este módulo es apunta con el mouse a la pestaña con el nombre de la hoja y activar la opción Ver código

En nuestro caso vamos a crear un evento de manera que cuando el usuario haga algún cambio en la hoja, se dispare el código. Para esto usamos un evento del tipo Change

Como puede apreciarse, el código es el mismo que en la rutina sub.

Extraer una muestra aleatoria de datos de una lista en Excel

En una nota anterior mostré una técnica para extraer muestras aleatorias de listas en Excel. Esta técnica incluía el uso de las funciones ALEATORIO.ENTRE, INDICE, CONTARA y una columna auxiliar con la función CONTAR.SI.

Deambulando por la Internet me topé con esta técnica sugerida por Bob Umlas que usa Filtro Avanzado y la función ALEATORIO.

Como ya hemos visto Filtro Avanzado acepta el uso de fórmulas en el área de criterios, lo que le da una gran flexibilidad.

La idea en la técnica de Umlas es usar la fórmula "=ALEATORIO()<x" como criterio, donde "x" es un número mayor que 0 y menor que 1.

Por ejemplo, si queremos extraer una muestra que represente el 10% de la lista usamos "=ALEATORIO()<0.1"

En este ejemplo tenemos una lista de 100 nombres y queremos extraer una muestra aleatoria de 10



Esta técnica tiene un inconveniente: no siempre el resultado será el número esperado. En el ejemplo arriba se puede apreciar que hay 12 nombres en la muestra. Si aplicamos varias veces el filtro veremos que el tamaño de la muestra puede ser mayor o menor que 10.

Para solucionar este problema podemos repetir la acción hasta que la muestra extraída tenga el tamaño deseado. Para automatizar el proceso usamos una macro simple. Grabamos con el grabador de macros las acciones y la macro resultante la asociamos a un botón



Para ahorrarnos el trabajo de contar cuantos nombres hay en la muestra podemos agregar una celda de control con la función CONTARA()

Formato condicional en gráficos de Excel - Tercera nota

En las notas anteriores del tema mostré códigos para generar efectos de formato condicional en gráficos de Excel. En la primera publiqué un código para crear una gama de colores en gráficos con una única serie. En la segunda nota, ampliamos el código para gráficos con más de una serie.

El código de esta tercera nota permite señalar máximos y/o mínimos en gráficos de Excel. Los códigos anteriores se basan en los valores del eje de las categorías. Es decir, suponen que los valores de las series están ordenados, de mayor a menor o de menor a mayor, en el eje de la X.

El código que publico en esta nota, evalúa los puntos de la serie elegida, determina los puntos con los valores máximos y mínimos, y los rellena con el color elegido por el usuario. Éste, a su vez, puede elegir señalar sólo el máximo, sólo el mínimo o ambos.

Siguiendo con nuestro ejemplo

al seleccionar el gráfico y apretar el botón Aplicar, se abre un formulario donde podemos elegir qué serie formar,

 qué señalar y que color darle a nuestras elecciones (rojo, verde o azul)

y al elegir el Verde para el máximos obtenemos este resultado

El cuaderno con el ejemplo y el código puede descargarse aquí.

Formato condicional en gráficos de Excel - segunda nota

En la primer nota del tema mostré una macro para aplicar un formato condicional sencillo a un gráfico de Excel. Esa macro aplica el formato siempre a los puntos de la primer serie de datos del gráfico. Es decir, que si tenemos más de una serie de datos en nuestro gráfico, no podemos elegir a qué serie aplicar el formato.

En esta nota mostraré una macro que nos permite elegir la serie de datos a la cual queremos aplicar el formato. Recordemos que estamos hablando de un formato muy sencillo (una gama de colores combinado de izquierda a derecha) y que en notas posteriores trataremos el tema de señalar máximos y mínimos de una serie y el formato de acuerdo al valor del punto de la serie.

Empecemos por agregar una serie de datos a nuestro ejemplo que ahora muestra los saldos de las cuentas para los años 2011 y 2012

Nuestra macro ahora evalúa cuantas series hay en el gráfico. Si hay más de una serie, se abre un formulario que nos permite elegir la serie a la cual queremos aplicar el formato

Una vez elegida la serie, elegimos los parámetros del formato (ver la explicación en la nota anterior)

y obtenemos el resultado buscado

El cuaderno con el ejemplo y los códigos puede descargarse aquí. También pueden descargar este complemento e instalarlo en la máquina. Luego pueden crear un icono en la barra de acceso rápido para poder utilizarlo con comodidad en todo cuaderno.

Formato condicional en gráficos de Excel - primera nota

Excel no tiene un método incorporado para aplicar formato condicional en gráficos. En el pasado mostramos técnicas que nos permiten aplicar formatos dinámicos en gráficos. Pero formato condicional va más allá de eso.

Supongamos este gráfico, donde los puntos de la serie (cantidad de cuentas según saldo) están ordenados según la categoría (monto del saldo)

Ahora supongamos que queremos aplicar algo similar al formato condicional de manera que el color de las columnas donde las columnas tengan una gama de colores que vayan del del rojo para el primer punto (los saldos más negativos) al verde (los saldos más positivos). Es decir, esto:

Una posibilidad es hacerlo manualmente. El método manual tiene dos inconvenientes:

• Es tedioso y cansador si se trata de muchos puntos
• La elección de los colores de la gama es dificultosa

Como con toda tarea cansadora, aburrida y dificultosa, la solución es desarrollar una macro.

Mi solución se basa en la función RGB del Vba.

Algunas palabras sobre el tema. RGB significa (en inglés) Rojo (Red), Verde (Green) y Azul (Blue). La combinación de estos colores primarios de la luz permite generar una amplia gama de colores. Cada gama de estos colores está representado por un número que va de 0 (ausencia del color) a 255. La función RGB tiene tres argumentos, uno para cada color primario. Esta tabla muestra algunos ejemplos:

El cuaderno se puede descargar apretando el símbolo de Excel en la barra inferior de la imagen. Cambiando los números en el rango de valores (cualquier número entero entre 0 y 255) poderá verse en la columna "Color" el color resultante.

Este video muestra como RGB genera colores a medida que vamos cambiando aleatoriamente los valores de los argumentos



En esta primera nota mostraré una macro sencilla para formar los puntos de una serie en el gráfico con una gama de colores (rojo a verde, azul a rojo, etc.). Esta macro puede usarse para el  caso de un gráfico con una única serie de datos.
En las próximas notas veremos como transformar la macro para gráficos con más de una serie de datos y como adaptarla para señalar máximos y mínimos de una serie.

En esta macro hacemos los siguiente:

1. Evaluamos si el objeto activo es un gráfico, en caso contrario aparecerá una advertencia pidiendo al usuario que elija un gráfico.
2. Aparecerá un formulario donde el usuario debe señalar un valor inicial para cada color. Las posibilidades son "255" (empieza con este valor y se va reduciendo hasta cero); "0" (empieza en cero y va aumentando hasta 255); "255-K" (constante 255) y "0-K" (constante 0).
3. Calculamos el "salto" de cada color dividiendo 256 por el número de puntos de la serie. Con este factor aumentamos o disminuimos el valor inicial del color en intervalos constantes. En caso de 255-K o 0-K, el valor es contante.
4. Con un loop For...Next vamos generando los valores de la función RGB para cada punto y lo aplicamos al punto de la serie.

Este modelo consiste en una macro, un userform (formulario) con los controles para la elección de los colores y sus correspondientes eventos. Este video muestra como funciona la macro.



El cuaderno con el ejemplo y los códigos puede descargarse aquí. También pueden descargar este complemento e instalarlo en la máquina. Luego pueden crear un icono en la barra de acceso rápido para poder utilizarlo con comodidad en todo cuaderno.

Como señalo más arriba, en las próximas notas subire un código más completo (gráficos con más de una serie y posibilidad de señalar máximos y mínimos).

Convertir fórmulas en valores según criterio.

En ciertas situaciones en Excel tenemos que convertir fórmulas en constantes. Por ejemplo, si extraemos valores de un cuaderno remoto usando BUSCARV, y queremos eliminar las fórmulas para evitar errores involuntarios, reducir el peso del archivo o mejorar la velocidad de cálculo.

Ya hemos mostrado como usar Ir A-Especial para convertir seleccionar todas las celdas que contengan fórmulas. Una vez seleccionadas, usamos Copiar-Pegado Especial-Valores.

Pero, ¿qué hacemos cuando queremos convertir sólo las celdas que contienen una función específica? Por ejemplo, todas las celdas que contienen BUSCARV en este cuadro de ganancias. Los datos los extraemos de un archivo remoto usando BUSCARV.

Luego de extraer los valores queremos eliminar las fórmulas BUSCARV pero no las fórmulas con las que calculamos las ganancias en las filas 6, 9 ,11 y 13.

Una posibilidad sería usar buscar y reemplazar (Ctrl+L) – buscar todos, seleccionar todos los resultados y apretar cerrar. Como ya mostramos, Excel selecciona todos los valores; luego podemos usar Copiar, pero si intentamos usar Pegado Especial-Valores esto es lo que veremos

La solución es usar una macro.

Sub formula_to_number_with_criteria()
    Dim strStringCriteria As String
    Dim rngCell As Range
    Dim lCounter As Long
   
    If Selection.Count < 2 Then
        MsgBox "Debe seleccionar por lo menos dos celdas", vbInformation
        Exit Sub
    End If
   
    'criterio de busqueda
    strStringCriteria = Application.InputBox(prompt:="Enter formula identifier", _
                                                Title:="Indentifier", Type:=2)
                                               
    'si no se ingresa criterio se cierra la rutina
    If Len(strStringCriteria) = 0 Then
        MsgBox "No se ingreso ningun criterio - no se puede realizar la operacion", vbCritical
        Exit Sub
    End If
   
   
    lCounter = 0
    For Each rngCell In Selection
        If rngCell.HasFormula Then
            If InStr(1, rngCell.FormulaLocal, strStringCriteria) > 0 Then
                rngCell = rngCell.Value
                lCounter = lCounter + 1
            End If
        End If
    Next rngCell
   
    If lCounter = 0 Then
        MsgBox "No se encontro ninguna celda con el criterio", vbInformation
    Else
        MsgBox lCounter & " celdas fueron modificada", vbInformation
    End If
     
End Sub


Esta macro hace lo siguiente:

• Comprueba que se hayan elegido por lo menos dos celdas
• Abre un formulario para que el usuario ingrese el criterio de búsqueda
• Busca todas las celdas en el rango seleccionado que cumplen con el criterio y reemplaza la fórmula por el valor
• Al terminar las operaciones produce un mensaje con el número de celdas que se han modificado.

Un detalle a tomar en cuenta es el uso de la propiedad FormulaLocal. Esto es necesario para que el código vea la versión local de la función (BUSCARV en nuestro caso) y no la versión nativa (inglés, VLOOKUP).
Podemos comprobar esto usando la ventana Inmediate del editor de Vba

Fechas en combobox

Ya hemos tratado en este blog sobre la posibilidad de incrustar controles directamente en hojas de Excel. En particular hemos mostrado las bondades de usar cuadros combinados de la colección de controles ActiveX (combobox) para crear listas desplegables.

Ciertos problemas surgen cuando queremos usar una combobox incrustada en la hoja para desplegar fechas. Veamos este ejemplo: en la hoja tenemos un rango con fechas al que le hemos asignado un nombre (fechas); hemos incrustado un cuadro combinado (combobox) para que el usuario elija una de esas fechas y ésta aparezca en la celda E5. También nos hemos preocupado de darle a E5 el formato de fecha

Al desplegar las fechas esto es lo que veremos

pero al elegir la fecha las cosas se complican

El formato de fecha se ha perdido tanto en la celda ligada como en el cuadro combinado. Lo que vemos ahora es el número de serie que representa la fecha.

Pero si miramos con un poco más de atención veremos que hay un segundo problema. El valor aparece alineado a la izquierda, lo que nos sugiere que se trata de un valor de texto, no numérico. Efectivamente, el valor que pasa de la combobox a la celda es textual. Para remediar esta situación tendremos que programar un evento de la combobox.

El código del evento debe ir en el módulo de la hoja que contiene el cuadro combinado. Podemos acceder al módulo desde el editor de Vba seleccionando el objeto Sheet correspondiente

o seleccionando la combobox en la hoja y seleccionando Ver Código en el menú contextual (para poder seleccionar el objeto debemos activar la opción Modo Diseño en Programador-Controles)

En el módulo ponemos este código

Private Sub ComboBox1_Change()
    ComboBox1.Value = CDate(ComboBox1.Text)
End Sub

Ahora veremos el formato adecuado en la celda y en el cuadro combinado. Pero si prestamos atención veremos que el valor sigue siendo texto.

Si no queremos realizar ninguna operación con el valor que pasamos a la celda ligada, podemos terminar aquí nuestra tarea. Pero en caso contrario tendremos que convertir el texto en valor numérico. Recordemos que la celda ligada ya tiene formato de fecha.

Para que esto suceda agregamos una línea de código en el evento

Private Sub ComboBox1_Change()
    ComboBox1.Value = CDate(ComboBox1.Text)
    Range(ComboBox1.LinkedCell).FormulaR1C1 = CDate(ComboBox1.Text)
End Sub

Con este código el valor en la celda ligada será numérico.

Todo sobre listas desplegables en Excel, técnicas avanzadas y descarga gratuita de ejemplos  en la Caja de Herramientas Excel - Listas Desplegables de JLD. Ver la nota o ir a la página de descarga de la guía.

Matrices de valores únicos en funciones de Excel

El origen de esta nota es esta consulta:

"tengo una tabla de alumnos con las calificaciones de nueve asignaturas. Quiero extraer las tres mejores notas en orden decreciente, sin repetición".

Para ejemplificar veamos parte de esta tabla:

La función que nos permite extraer el valor de la lista de acuerdo a su jerarquía en la lista es K.ESIMO.MAYOR (orden decreciente) y K.ESIMO.MENOR (orden ascendente). La sintaxis de estas funciones es sencilla:

=K.ESIMO.MAYOR(matriz de valores, número de orden)

Así, para el alumno 1 (fila 2) la fórmula

=K.ESIMO.MAYOR($B2:$J2,1)

da como resultado 95.

El problema con esta función es que no ignora los valores repetidos. Si usamos ahora

=K.ESIMO.MAYOR($B2:$J2,2)

para obtener la segunda mejor notas en orden decreciente, el resultado será nuevamente 95, cuando el resultado que buscamos es 86.

Para obtener los resultados esperados necesitamos que el argumento "matriz de valores" de la función sea una matriz de valores únicos.

En esta nota mostraré dos soluciones posibles a este problema: 1) usar columnas auxiliares; 2) usar una FDU (función definida por el usuario).

Solución con columnas auxiliares.

A la izquierda de la matriz insertamos un número de columnas equivalente al número de columnas de la matriz

La celda A2 contiene la fórmula

 =SI(CONTAR.SI($L2:L2,L2)=1,L2,"#")

Esta fórmula evalúa si el valor de la celda L2 aparece por primera vez en la fila; en caso afirmativo da el valor de la celda L2; en caso negativo da como resultado el símbolo #.

EL valor 95 aparece por segunda vez en la celda N2 y por eso el valor resultante en la celda C2 es #.

En la función CONTAR.SI anclamos la columna en la referencia a la primer celda del rango ($L2) de manera que al copiar la fórmula a lo largo de las columnas el rango de evaluación se va extendiendo ($L2:M2 en la celda B2, $L2:N2 en la celda C2 y así sucesivamente).

Ahora podemos usar los rangos de las columnas A:I para nuestros cálculos

En la fórmula

=K.ESIMO.MAYOR($A2:$I2,V$1) 

usamos los valores de la fila 1 como el argumento de orden, fieles al principio de no evitar el uso de constantes en las fórmulas.

Podemos mejorar esta solución funcionalmente convirtiendo todo el rango (columnas auxiliares, matriz de datos y columnas con resultados) en una tabla (Insertar-Tablas-Tabla). De esta manera al agregar alumnos, todas las fórmulas se copian automáticamente

Solución FDU (función definida por el usuario – macro).

La función valUnicos crea una matriz de valores únicos que podemos usar dentro de las funciones de Excel, en nuestro caso en K.ESIMO.MAYOR.

Esta función usa la técnica que ya mostramos en la nota sobre cómo extraer valores únicos.

El código de la función es el siguiente:

Function valUnicos(rngValores As Range)
    Dim iX As Integer
    Dim arrTemp()
    Dim collUnicos As New Collection
    Dim vcollItem As Variant
    Dim rngCell As Range
   
    On Error Resume Next
    For Each rngCell In rngValores
        collUnicos.Add rngCell, CStr(rngCell)
    Next rngCell
    On Error GoTo 0
       
    ReDim arrTemp(collUnicos.Count)
    For iX = 1 To collUnicos.Count
        arrTemp(iX - 1) = collUnicos.Item(iX)
    Next iX
   
    valUnicos = arrTemp
   
End Function

Como siempre recomiendo, guardamos el código en un módulo del cuaderno Personal.xls(b) de manera de poder usar la función en todo cuaderno abierto.

Esta función utiliza un único argumento: un rango de la hoja. En la función usamos el objeto Collection para descartar los valores repetidos en el rango; luego pasamos los ítems de la colección a una matriz (array).

En nuestro ejemplo, combinamos esta función con K.ESIMO.MAYOR para obtener los resultados deseados

De la misma manera podemos combinarla con otras funciones (K.ESIMO.MENOR, CONTAR, etc). Por ejemplo

=CONTAR(valUnicos(B2:J2))

da como resultado 7 ya que el 95 y el 86 se repiten.


El cuaderno con el código y los ejemplos se puede descargar aquí.

Tablas dinámicas – cambiar la función CONTAR por SUMA

Para totalizar los valores en los campos introducidos en el área de valores Excel utiliza la siguiente regla:

• Si todos los valores en el campo (columna de la tabla de datos) de origen son numéricos, utiliza SUMA.
• Si alguno de los valores no es numérico, utiliza CONTAR.

El problema con esta regla es que, a diferencia de la función SUMA usada en una celda de la hoja, las celdas vacías son consideradas no numéricas. Esto produce no pocos dolores de cabeza ya que basta que una de las celdas no contenga ningún valor para que la función usada para resumir los valores sea CONTAR.

El problema se agrava cuando tenemos varios campos de valores ya que Excel no cuenta con un método para cambiar la función de resumen de todos los campos de una sola vez. Si tenemos una tabla con muchos campos de valores, nuestro sufrido usuario deberá cambiar manualmente la función de cada uno de los campos.

En este ejemplo tenemos una tabla con los datos de una cadena de comercios para los meses de enero, febrero y marzo. Los datos son: zona, mes, número de visitantes, número de compradores (cuantos de los visitantes realizaron alguna compra) y ventas.

Como puede apreciarse, faltan los datos de marzo de la zona Sur, por lo que las celdas están vacías.

Al crear un reporte dinámico, veremos que Excel usa CONTAR para resumir los datos en lugar de SUMA, que era lo que esperábamos

En este estado de cosas podemos optar por una de estos tres caminos:

1. modificar una a una la función de resumen
2. rellenar las celdas vacías con ceros (lo que se puede hacer eficientemente con Ir a-Especial)
3. usar esta macro para cambiar la función programáticamente:

Sub change_pt_function()
'Cambia la funcion de los campos de valor de CONTAR a SUMA
'Desarrollada por Jorge Dunkelman - JLDExcelsp.blogspot.com

    Dim pt As PivotTable
    Dim iDataFieldsCount As Integer
    Dim iX As Integer
       
    On Error Resume Next
    Set pt = ActiveCell.PivotTable
    On Error GoTo 0
   
    If pt Is Nothing Then
        MsgBox "La celda seleccionada no pertenece a una tabla dinamica", _
                    vbCritical, _
                    "Cambiar funcion en TD"
        Exit Sub
    End If
   
    iDataFieldsCount = pt.DataFields.Count
   
    For iX = 1 To iDataFieldsCount
        With pt.DataFields(iX)
            If .Function = xlCount Then
            .Function = xlSum
            .Name = Replace(.Name, "Cuenta de", "Suma de")
            End If
        End With
    Next iX
       
End Sub

Esta macro recorre todos los campos en el área de los valores; en caso que la función de resumen sea CONTAR, la reemplaza por SUMA.

Para usar la macro hay que seleccionar previamente alguna celda de la tabla dinámica, por eso usamos un MSgBox para informar al usuario en caso que no se haya seleccionado un celda de la tabla dinámica.
Otro detalle es que al usar la propiedad DataFields, el nombre del campo (label) no cambia al cambiar la función. Por eso, suponiendo que el usuario no ha cambiado el nombre por defecto del campo ("Cuenta de…"), reemplazamos "Cuenta de…" por "Suma de…" programáticamente.
Si no queremos cambiar los nombres de los campos, podemos poner un apóstrofe (') al comienzo de la sentencia .Name = Replace(.Name, "Cuenta de", "Suma de"), de manera que no sea ejecutada.

Para usar este código con comodidad podemos agregar un icono en la barra de acceso rápido.

Encontrar la última celda en una hoja de Excel

Una de las tareas frecuentes al escribir código Vba, muchas veces al modificar código de una macro grabada, es tener que determinar la última fila (o celda o columna) usada en la hoja.
Si nos basamos en una macro que hemos grabado al usar el método Ir-Especial-Última celda,

veremos esta sentencia

Range("A1").SpecialCells(xlCellTypeLastCell).Select

El problema con este método es que Excel recalcula la ubicación de la última celda usada sólo cuando guardamos el archivo. Esto significa que si ingresamos un valor en una celda remota y luego lo borramos, Excel seguirá considerando esa celda como la última. Y como si esto no fuera poco, también si cambiamos el formato de una celda remota, aún si ingresar ningún valor, Excel la considerará la última celda usada.

Para asegurarnos de encontrar siempre la última celda usada, es decir, la celda más remota que contiene algún valor, tenemos que usar un código basado en el método Range.Find

Sub ultima_celda()
    Dim UltimaFila As Long

    If WorksheetFunction.CountA(Cells) > 0 Then

        UltimaFila = Cells.Find(What:="*", After:=[A1], _
              SearchOrder:=xlByRows, _
              SearchDirection:=xlPrevious).Row

         MsgBox UltimaFila, vbInformation, "Ultima fila"

    End If

End Sub

Este código encuentra la última celda y nos informa cuál es la fila con un mensaje.

Este video nos muestra la diferencia entre ambos métodos

Extraer valores únicos de rangos discontinuos

Esta nota trata sobre cómo extraer valores únicos de rangos que contienen más de una columna o rangos discontinuos.

Excel tiene dos métodos incorporados para esta tarea. En Excel 2007-2013 ambos se encuentran en la pestaña Datos: Filtro Avanzado y Quitar duplicados

Filtro Avanzado nos permite hacerlo con relativa facilidad usando la posibilidad, como mostramos en este video:



Con Quitar duplicados la técnica es un poco más elaborada, ya que incluye copiar la lista a un rango apartado y allí extraer los duplicados (si estamos interesados en guardar la lista original)




Las limitaciones de estos métodos comienzan cuando queremos extraer valores únicos de rangos discontinuos o de rangos que contienen más de una columna.

Podemos hacerlo con un código relativamente sencillo, similar al que mostramos en la nota sobre listas desplegables dependientes publicada hace poco. Este código se basa en el objeto Collection. El código es el siguiente


Sub extraerUnicos_Hoja()
'extraer valores unicos de rangos de varias columnas o no continuos
'Jorge Dunkelman - JLD Excel Blog, mayo 2013

    Dim collUnicos As New Collection
    Dim vcollItem As Variant
    Dim rngCell As Range, rngDatos As Range, rngLista As Range
    Dim lCounter As Long
 
    Set rngDatos = Application.InputBox(prompt:="Seleccione rango/s con datos", Type:=8)
    Set rngLista = Application.InputBox(prompt:="Seleccione la primera celda de la lista", Type:=8)
 
    On Error Resume Next
    For Each rngCell In rngDatos
        collUnicos.Add rngCell, Cstr(rngCell)
    Next rngCell
    On Error GoTo 0
 
    lCounter = 0
    For Each vcollItem In collUnicos
        rngLista.Offset(lCounter, 0) = CStr(vcollItem)
        lCounter = lCounter + 1
    Next vcollItem
 
End Sub


Con este código definimos el rango que contiene los datos (que puede contener varias columnas o ser discontinuo, pero todos los datos deben estar en la misma hoja), definimos la celda desde donde queremos empezar a pegar la lista de registros únicos y el código la genera.

Por ejemplo, en esta matriz de 6 filas por tres columnas (18 valores) donde hay 5 valores únicos (a, b, c, d y e)



Para que este código sea realmente útil debemos agregar algunas líneas para manejar errores que pueden ocurrir durante el proceso (por ejemplo, si el usuario selecciona un rango de datos con una sola celda o si aprieta el botón Cancel del InputBox). El código completo es el siguiente

Sub extraerUnicos_Hoja()
'extraer valores unicos de rangos de varias columnas o no continuos
'Jorge Dunkelman - JLD Excel Blog, mayo 2013

    Dim collUnicos As New Collection
    Dim vcollItem As Variant
    Dim rngCell As Range, rngDatos As Range, rngLista As Range
    Dim lCounter As Long
 
    On Error GoTo errCancel 'si se aprieta Cancel
 
    Set rngDatos = Application.InputBox(prompt:="Seleccione rango/s con datos", Type:=8)
    If rngDatos.Count < 2 Then
        MsgBox "Debe seleccionar un rango con mas de dos celda", vbCritical
        Exit Sub
    End If
 
    Set rngLista = Application.InputBox(prompt:="Seleccione la primera celda de la lista", Type:=8)
    If rngLista.Count <> 1 Then
        MsgBox "Seleccione solamente una celda", vbCritical
        Exit Sub
    End If
 
    On Error Resume Next
    For Each rngCell In rngDatos
        collUnicos.Add rngCell, Cstr(rngCell)
    Next rngCell
    On Error GoTo 0
 
    lCounter = 0
    For Each vcollItem In collUnicos
        rngLista.Offset(lCounter, 0) = CStr(vcollItem)
        lCounter = lCounter + 1
    Next vcollItem
 
    Exit Sub
 
errCancel:
Exit Sub
End Sub

Intercambiar rangos en Excel

En todo proceso de diseño de una hoja en Excel es prácticamente inevitable tener que reordenar rangos (filas, columna o celdas).
Por lo general nos basta con arrastrar el rango a la posición deseada. Pero en no pocos casos debemos intercambiar posiciones, poner un rango en lugar del otro.
La técnica más común es usar Copiar (o Cortar) y Pegar, usando una ubicación transitoria, o arrastrando una de las columnas (o filas o celdas) a un área "libre" de la hoja y moviendo luego los objetos a la posición deseada

Una forma más eficiente es seleccionar uno de los rangos y manteniendo apretada la tecla Mayúsculas (Shift), apuntar con el mouse al borde indicado y arrastrar el rango a la posición del rango a intercambiar

Lo mismo puede hacer con celdas o filas.

En ciertas situaciones estas técnicas pueden ser engorrosas, en particular cuando queremos realizar el intercambio entre celdas que se encuentran en distintas columnas. Por ejemplo, si tenemos una lista de turnos de trabajo, donde los operarios piden con cierta frecuencia cambiar su turno:

Con un poco de código Vba (macros) podemos crear una herramienta útil para intercambiar celdas con facilidad:

Sub InterCambiarCeldas()
    Dim rngCell2 As Range
    Dim cellTemp As String
   
    'verificar que se haya elegido solo una celda
    If Selection.Count > 1 Then
        MsgBox "Debe elegirse solo una celda", vbCritical
        Exit Sub
    End If
   
    'verificar que la celda contenga un valor
    If Len(ActiveCell) = 0 Then
        MsgBox "Celda vacia", vbExclamation
        Exit Sub
    End If
     
    On Error GoTo errCancel
   
    Set rngCell2 = Application.InputBox(prompt:="Elija la celda a intercambiar", _
                        Title:="Celda a intercambiar", Type:=8)
    If rngCell2.Count <> 1 Then
        MsgBox "Debe elegirse solo una celda", vbCritical
        Exit Sub
    End If
    If Len(rngCell2) = 0 Then
        MsgBox "La celda elegida esta vacia", vbExclamation
        Exit Sub
    End If

   
    cellTemp = ActiveCell.Value
   
    ActiveCell.Value = rngCell2.Value
    rngCell2 = cellTemp
   
    Exit Sub
   
errCancel:
If Err.Number = 424 Then
MsgBox "Operacion cancelada", vbExclamation
Exit Sub
End If


End Sub

El código va en un módulo común del editor de Vba, y para que podamos usarlo en todo cuaderno abierto lo guardamos en un módulo del cuaderno Personal.xls(m). Par apoder usar la macro con facilidad le asignamos un atajo de teclado

o un ícono en la barra de acceso rápido

Con este código todo lo que tenemos que hacer es seleccionar la primera celda a intercambiar, apretar Ctrl-Mayúsculas-Enter para accionar la macro, elegir la segunda celda y apretar Ok. Si elegimos más de una celda o una celda vacía, aparece un aviso y el código termina; lo mismo si el usuario aprieta el botón Cancelar del al forma