Prefijos o sufijos de Excel

Question

Tengo una columna en Excel que contiene una lista delimitada por comas:

Header
1, 61
61
1, 61, 161
5, 55

Me gustaría extraer estos datos para poder contar las apariciones de cada elemento y obtener los siguientes resultados:

Count of Items
1    |    2
5    |    1
55   |    1
61   |    3
161  |    1

Intenté contar con "*" pero es un desastre porque en este caso tengo prefijos o sufijos (1,61,161)

¡POR FAVOR AYUDA!

Answer 1

Opción 1:

Me gustaría sugerir UDF (función definida por el usuario) que no solo cuenta la aparición de números separados por comas sino que también cuenta el texto.

Cómo funciona:

• PrensaAlt+F11para obtener el editor VB entoncesCopiar&Pegareste código comoMódulo.

  Option Explicit
  
  Function CountOccurrence(SearchRange As Range, Phrase As String) As Long
  
  Dim RE As Object, MC As Object
  Dim sPat As String
  Dim V As Variant
  Dim I As Long, J As Long
  
  V = SearchRange
  
  Set RE = CreateObject("vbscript.regexp")
  With RE
  .Global = True
  .MultiLine = True
  .ignorecase = True
  .Pattern = "(?:^|,\s*)" & Phrase & "(?:\s*,|$)"
  End With
  
  For I = 1 To UBound(V, 1)
  If RE.test(V(I, 1)) Then J = J + 1
  Next I
  
  CountOccurrence = J
  
  End Function
  

• Ingrese los criterios en el rango H18:H26, luego ingrese esta fórmula en la celda I18y complétela.

=CountOccurrence($G$18:$G$24,H18)

Opcion 2:

Ingrese esta fórmula en la celda I18y complétela.

=SUMPRODUCT(--ISNUMBER(FIND(H18,$G$18:$G$24)))

Ajuste las referencias de celda según sea necesario.

Answer 2

1. Primero necesitas dividir tus números en celdas separadas:
   • selecciona tus datos
   • en la pestaña de datos seleccione "dividir en columnas"
   • seleccione "delimitado", siguiente
   • los datos seleccionados están separados por "coma", finalizar
2. ahora puedes usar CONTAR.SI, por ejemplo
   =COUNTIF($A$2:$C$5,E2)

Answer 3

Parece que ya tiene una solución, pero agregaré una solución que no sea VBA y que maneje datos dinámicos. Utiliza algunas columnas auxiliares que puede completar previamente en un rango arbitrariamente grande. Cuando no haya datos asociados, las celdas estarán en blanco. Algunas de las columnas auxiliares podrían eliminarse; se incluyen para minimizar la repetición, pero todas las columnas auxiliares se pueden ocultar.

Sus datos están en la columna A. La columna B determina la cantidad de valores en cada entrada en función de la cantidad de comas. La fórmula en B3 es:

=IF(ISBLANK(A3),"",LEN(A3)-LEN(SUBSTITUTE(A3,",",""))+1)

La columna C es solo el recuento acumulativo de componentes de la columna B. C2 se ingresa como 0. La fórmula en C3 es:

=IF(ISBLANK(A3),"",SUM(B3:B$3))

Complete las columnas B y C con tantas filas como pueda tener datos. Siempre puedes ampliar esas columnas si es necesario.

La columna E es sólo por conveniencia. Proporciona un índice para los valores analizados. Puede codificar 1y luego agregarle 1 para cada fila sucesiva. Sin ninguna buena razón, lo basé en el número de fila (lo -2de la fórmula siguiente es para ajustar los valores para que comiencen en 1). Las celdas que superan el número total de valores quedan en blanco. Mi fórmula en E3:

=IF(ROW()-2>MAX($C$2:$C$10),"",ROW()-2)

La columna F es sólo para evitar la repetición de fórmulas. Extrae la entrada relevante de la Columna A desde la cual se analizará el componente actual. La fórmula en F3 es:

=IFERROR(OFFSET($A$2,MATCH(E3-1,$C$2:$C$10,1),0),"")

Encuentra la entrada adecuada comparando el número de componente en la columna E con el recuento acumulado de componentes en la columna C.

La columna G son los valores de los componentes analizados, todos en una única columna contigua con la que es fácil trabajar. La fórmula en G3 es:

=IFERROR(TRIM(MID(SUBSTITUTE(F3,",",REPT(" ",LEN(F3))),(E3-INDEX($C$2:$C$10,MATCH(E3-1,$C$2:$C$10,1))-1)*LEN(F3)+1,LEN(F3))),"")

Esto determina qué elemento analizar de la entrada de la columna F restando el recuento acumulativo de elementos del último registro de entrada "completado" del número de elemento actual.

Las columnas E a G deben propagarse a suficientes filas para cubrir el número previsto de valores de componentes (al menos varias veces el número de filas de datos). Tenga en cuenta que todas las fórmulas anteriores que se refieren al rango $C$2:$C$10 deben ajustarse para incluir el rango completo de sus datos.

Ahora que tiene todos los elementos analizados en una buena columna, hay varias formas de agregarlos y obtener los recuentos. Utilicé una tabla dinámica, que también te proporciona la lista de valores únicos al mismo tiempo.

Seleccione el rango completo previamente completado de la columna G para la tabla dinámica. Utilice ese campo para la ventana de filas y la ventana de valores (seleccione contar como agregación). El rango incluirá espacios en blanco en las filas no utilizadas, así que utilice el filtro integrado para anular la selección de espacios en blanco.

Cuando los datos cambien, simplemente actualice la tabla dinámica y verifique que los valores de elementos nuevos estén seleccionados en el filtro.