☰

предостережение:

tag-icon • tag-icon google-spreadsheets
предостережение:

У меня есть набор ячеек, в которых смешаны однозначные рейтинговые числа с текстом рядом с ними (рейтинги от 1 до 9). Я хотел бы извлечь числа для каждой ячейки и получить среднее значение. Я знаю о таких функциях, как Search и RegexExtract, но не понимаю, как их использовать, чтобы получить все числа, поскольку они, похоже, возвращают только первое совпадение.

Вот пример текста в одной ячейке:

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

Для приведенного выше текста я собираюсь создать массив {1,5,9,4,7,2} и передать его функции усреднения.

Я также использую Google Sheets, но большинство стандартных функций Excel должны работать так же. Спасибо.

решение1

Я хотел бы предложить решение в два шага, которое решит эту проблему:

предостережение:

  • Представленный ниже метод предназначен для пользователей Excel, поскольку VBA не работает с Google Sheet.
  • Файл Excel можно преобразовать в XLSXформат, подходящий для редактирования в Google Sheet.
  • SUMPRODUCTтакже работает с Google Таблицами.

введите описание изображения здесь

Шаг 1:

  • Либо нажмитеАlt+F11 илирНажмите наСнажмите TAB и из выпавшего меню нажмитеВiew Код для получения редактора VB.

  • Скопи ипСохранить этот код какМodule, затем вернитесь к листу.

    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

  • Введите эту формулу в ячейку AI5:=SplitNum(AH1,TRUE)

Шаг 2:

  • ПолучитьАVERAGE введите эту формулу в ячейку AJ5.

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

Пользователи Google Таблиц могут использовать эту формулу:

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

Примечание. -При необходимости измените ссылки на ячейки в формуле.

решение2

Если вам нужно получить AVERAGEэти значения в Таблицах, попробуйте (с данными в A1:

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

Вместо того, чтобы пытаться извлечь каждое число, мы просто удаляем нецифровые значения; затем разделяем и усредняем их. Иногда это удобный метод — удалить то, что нам не нужно, вместо того, чтобы пытаться извлечь то, что нам нужно.

введите описание изображения здесь

ДляЭксельпользователи, если у вас есть Excel 2016+ с этой TEXTJOINфункцией, вы можете использовать этомножествоформула:

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

Поскольку это формула массива, вам нужно "подтвердить" ее, удерживая ctrl+ shiftпри нажатии enter. Если вы сделаете это правильно, Excel поместит фигурные скобки {...}вокруг формулы, как показано в строке формул

Связанный контент