jueves, 12 de diciembre de 2013

Contando secuencias en un rango de Excel.

Hace un par de días un usuario del blog me contactó preguntándome por la manera de construir una secuencia 'especial' sobre un rango de celdas:

Lo que yo requiero es contar la secuencia de las columnas pero de una forma simplificada
La columna A:
3A, 1I, 2A, 1G, 1E, 1B, 1A, 1E, 4A.
… y asi por cada columna, la idea es como un resumen, pero no solo contando sino conservando el orden


Partiremos de la siguiente serie de datos (nos centraremos en la columna A):

Contando secuencias en un rango de Excel.


El asunto está claro, necesitamos construir, de alguna manera, el conteo de elementos continuos en el rango a evaluar. Es decir,a cada salto por diferencias entre el elemento anterior y siguiente, mostraremos el número de repeticiones habidas. Y lo haremos sin emplear macros, mediante fórmulas.

Para hacerlo sencillo de explicar construiremos dos columnas auxiliares que nos apoyarán en la obtención de lo buscado.
En nuestra Aux1 dispondremos, para nuestro ejemplo, la siguiente fórmula en la celda F2:
=SI(FILAS($A$1:A1)=1;1;SI(A2=A1;F1+1;1))
con ella conseguimos que para el primer valor del rango aparezca un 1:
SI(FILAS($A$1:A1)=1;1;...
y para el resto de filas aplica lo importante
...;SI(A2=A1;F1+1;1)
es decir, cuando haya un cambio de elemento, asignamos de nuevo el valor 1, y en caso que sea el mismo elemento acumulamos +1 al valor anterior.
Vemos el resultado en la imagen:

Contando secuencias en un rango de Excel.



En este primer paso hemos conseguido tener numerada las series continuas que aparezcan en nuestro rango (columna A), preparando el terreno para nuestra segunda Axuliar, donde en G2 insertaremos la fórmula:
=SI(A2=A3;"";A2)
y arrastrando hacia abajo:

Contando secuencias en un rango de Excel.


Lo conseguido con este segundo paso es claro, mostrar sólo valores en aquellos casos en los que se produce un salto o cambio de elemento en la secuencia a estudiar.

El último paso es sencillo, concatenamos los elementos de nuestras dos auxilares... en la celda H2 insertamos:
=SI(G2="";"";F2&G2)
pero sólo en los casos que nos interesa, es decir, en las filas que se produce cambio.


Realmente el trabjo está conseguido, podemos ver para nuestra columna A evaluada el conteo de elementos, por intervalos de salto, manteniendo el orden.

Sólo nos queda un paso final, tener esa secuencia en una sola celda.
Para ello seguiremos las siguientes instrucciones:
1- situarnos en la celda donde queramos ver el resultado final, por ejemplo, celda H18
2- teclea =H2:H16&" " en la barra de formulas
3- aprieta la tecla F9 (lo que convertirá la 'fórmula' en una matriz).
4- borra las llaves que se pusieron al principio y al final, que representaba la matriz.
5- escribe =ESPACIOS(CONCATENAR( al principio de la barra de formulas y )) al final de la barra de formulas
6- presiona Enter

Vemos el proceso en este corto video:

Contando secuencias en un rango de Excel.



Aunque algo manual y estático, este pequeño truco nos evita emplear macros o funciones UDF para conseguir el resultado.
El truco consiste en concatenar, al emplear =H2:H16&" ", el elemento seguido de un espacio, lo que genera más de un caracter espacio en aquellos casos que el elemento de H2:H16 esté sin valor; que luego corregimos con al función ESPACIOS que elimina espacios repetidos, dejando únicamente un espacio entre elemento resultante.


Si quisieramos tener separado por coma los diferentes elementos, sería suficiente anidar toda nuestra fórmula en una función SUSTITUIR:
=SUSTITUIR(H18;" ";",")


Para automatizar más la tarea (olvidándonos de la parte manual expuesta), podríamos construir una sencilla función VBA personalizada (UDF) con el siguiente código:

Function CONCATENARCELDAS(Rango As Excel.Range) As String
Application.Volatile
'Bucle para recorrer todas las celdas del rango
For Each celda In Rango.Cells
    'Si la celda está NO vacía, entonces la concatenamos al anterior
    If celda.Value <> "" Then
        concatenado = concatenado & ", " & celda.Value
    End If
Next celda

'Se elimina el ; y espacio inicial
concatenado = Right(concatenado, Len(concatenado) - 2)

CONCATENARCELDAS = concatenado

End Function















6 comentarios:

  1. Muchas gracias, gran creatividad!!! ya estaba pensando en como poner todo en una macro, cosa que solo fuera pegar la matriz y ejecutarla.

    Pero al probarla haciendo el paso a paso en la tercera parte del concatenado me salio un error: "Ha especificado demasiados argumentos para esta función" La matriz que tengo ahorita es de 425 filas por 650 columnas.

    Luis G

    ResponderEliminar
    Respuestas
    1. Hola Luís,
      lógicamente hay que tener en cuenta que las funciones de Excel en general tienen una limitción de 255 argumentos..
      En tu caso, sobrepasas el límite, por lo que sería mejor aplicaras las UDF expuesta.

      Saludos cordiales

      Eliminar
  2. Buenos dias,Si pueden ayudarme tengo en una hoja de Excel notas de 3 lapsos L1,L2;L3 y necesito que el resultado del Promedio cuando sea menor que 10 sea de color Rojo.Gracias de antemano por la ayuda.

    ResponderEliminar
    Respuestas
    1. Hola Carlos,
      en la celda, por ejemplo D1, donde tengas calculado el PROMEDIO de L1, L2 y L3 configura un Formato Condicional con la siguiente fórmula:
      =$D$1<10
      y aplicas el formato que quieres.. relleno de celda rojo.
      Saludos

      Eliminar
  3. Hola, se podría hacer algo similar pero para identificar secuencias? Como patrones, es decir, que el excel identifique una secuencia y cuando lo haga, que emita un resultado (por ejemplo: la secuencia A, B, C, D si se da en ese orden, que aparezca un “CORRECTO” o similar en una casilla contigua…¿Habría alguna manera de hacerlo? Gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      sería necesario conocer el contexto en el que se mueven esos valores, pero en principio se podrían reconocer secuencias de 4 elementos con condicionales y funciones lógicas...
      Slds

      Eliminar