☰

advertencia:

tag-icon • tag-icon google-spreadsheets
advertencia:

Tengo un conjunto de celdas que tienen números de calificación de un solo dígito mezclados con texto al lado (calificaciones del 1 al 9). Lo que me gustaría hacer es, para cada una de las celdas, extraer los números y producir un promedio. Conozco funciones como Buscar y RegexExtract, pero no entiendo cómo usarlas para obtener todos los números, ya que parece que solo devuelve la primera coincidencia.

Aquí hay una muestra del texto en una sola celda:

A - 1
B - 5
C - 9
D -4
E -7
F -  2

Para el texto anterior, estoy buscando crear una matriz {1,5,9,4,7,2} y pasarla a la función promedio.

También estoy usando Google Sheets, pero la mayoría de las funciones estándar de Excel deberían funcionar igual. Gracias.

Respuesta1

Me gustaría sugerir una solución de dos pasos que solucionará el problema:

advertencia:

  • El método que se muestra a continuación es para usuarios de Excel, ya que VBA no funciona para Google Sheet.
  • El archivo de Excel se puede convertir a un XLSXformato de archivo que sea adecuado/editable con Google Sheet.
  • SUMPRODUCTTambién funciona con la hoja de Google.

ingrese la descripción de la imagen aquí

Paso 1:

  • O presioneAlt+F11 oRHaga clic derecho enSpresione TAB y desde el menú emergente presioneVVer código para obtener el editor VB.

  • Ccopiar yPAGGuarde este código comoMETROodulo, luego regresa a la Hoja.

    Public Function SplitNum(pWorkRng As Range, pIsNumber As Boolean) As String

Dim xLen As Long
Dim xStr As String
xLen = VBA.Len(pWorkRng.Value)
For i = 1 To xLen
xStr = VBA.Mid(pWorkRng.Value, i, 1)
If ((VBA.IsNumeric(xStr) And pIsNumber) Or (Not (VBA.IsNumeric(xStr)) And Not (pIsNumber))) Then
    SplitNum = SplitNum + xStr
End If
 Next
End Function

  • Ingrese esta fórmula en la celda AI5:=SplitNum(AH1,TRUE)

Paso 2:

  • LlegarAPROMEDIO ingrese esta fórmula en la Celda AJ5.

    =SUMPRODUCT(--MID(AI5,ROW(INDIRECT("1:" & LEN(AI5))),1))/Len(AI5)

Los usuarios de Google Sheet pueden utilizar esta fórmula:

=AVERAGE(SPLIT(AH1,CONCATENATE(SPLIT(AH1,".0123456789"))))

NÓTESE BIEN -Ajuste las referencias de celda en la fórmula según sea necesario.

Respuesta2

Si lo que quieres es el AVERAGEde esos valores, en Hojas, prueba (con tus datos en A1:

=average(split(trim(REGEXREPLACE(A1,"\D+"," "))," "))

En lugar de intentar extraer cada número, simplemente eliminamos los que no son dígitos; luego divídalos y promedielos. A veces es una técnica útil para eliminar lo que no queremos, en lugar de intentar extraer lo que sí queremos.

ingrese la descripción de la imagen aquí

ParaSobresalirusuarios, si tienen Excel 2016+ con la TEXTJOINfunción, pueden usar estoformaciónfórmula:

=AVERAGE(ABS(FILTERXML("<t><s>" & SUBSTITUTE(TEXTJOIN(" ",TRUE,A1:A6)," ","</s><s>") & "</s></t>","//s[number()=number()]")))

Dado que se trata de una fórmula matricial, debes "confirmarla" manteniendo presionado ctrl+ shiftmientras presionas enter. Si hace esto correctamente, Excel colocará llaves {...}alrededor de la fórmula como se observa en la barra de fórmulas.

información relacionada