Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Как функцией извлечь из диапазона сумму чисел после опр.слов - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Как функцией извлечь из диапазона сумму чисел после опр.слов (Формулы/Formulas)
Как функцией извлечь из диапазона сумму чисел после опр.слов
kNNeR Дата: Воскресенье, 08.11.2020, 15:44 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 45
Репутация: 9 ±
Замечаний: 0% ±

Excel 2019
Здравствуйте.
Помогите решить задачу.

Есть диапазон "Лист1!H7:J"
В нем находится много-много различного текста.
Также в столбце E7:E на Лист1 - находятся отметки года.

На листе "Список" - находится таблица, которую нужно заполнить.
В ячейке C5 - этого листа (над таблицей) - указан год.
В столбце "B" этой таблицы находятся ключевые слова.

Как пользовательской функцией извлечь из диапазона "Лист1!H7:J" - от ключевого слова - до ограничителя - числовые отметки и просуммировать их с учетом года (заданного в ячейке C5) ?

(ограничитель это что угодно, что не является числом, например символ "," или "-" или просто буква.
Например в текстовой отметке "mw.я5," - по ключевому слову mw.я будет извлечено число до первого символа не являюзегося цифрой - то есть "5")
К сообщению приложен файл: 9071315.xls (90.5 Kb)
 
Ответить
СообщениеЗдравствуйте.
Помогите решить задачу.

Есть диапазон "Лист1!H7:J"
В нем находится много-много различного текста.
Также в столбце E7:E на Лист1 - находятся отметки года.

На листе "Список" - находится таблица, которую нужно заполнить.
В ячейке C5 - этого листа (над таблицей) - указан год.
В столбце "B" этой таблицы находятся ключевые слова.

Как пользовательской функцией извлечь из диапазона "Лист1!H7:J" - от ключевого слова - до ограничителя - числовые отметки и просуммировать их с учетом года (заданного в ячейке C5) ?

(ограничитель это что угодно, что не является числом, например символ "," или "-" или просто буква.
Например в текстовой отметке "mw.я5," - по ключевому слову mw.я будет извлечено число до первого символа не являюзегося цифрой - то есть "5")

Автор - kNNeR
Дата добавления - 08.11.2020 в 15:44
прохожий2019 Дата: Воскресенье, 08.11.2020, 17:23 | Сообщение № 2
Группа: Проверенные
Ранг: Старожил
Сообщений: 1241
Репутация: 317 ±
Замечаний: 0% ±

365 Beta Channel
на примере объясните как получилось 911 в 2018? потому что по файлу у меня выходит 13
 
Ответить
Сообщениена примере объясните как получилось 911 в 2018? потому что по файлу у меня выходит 13

Автор - прохожий2019
Дата добавления - 08.11.2020 в 17:23
kNNeR Дата: Воскресенье, 08.11.2020, 17:26 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 45
Репутация: 9 ±
Замечаний: 0% ±

Excel 2019
прохожий2019, 911 - это я просто так цифру написал, потому что у меня нет этой пользовательской функции.
Если у вас получается 13 - значит 13.
 
Ответить
Сообщениепрохожий2019, 911 - это я просто так цифру написал, потому что у меня нет этой пользовательской функции.
Если у вас получается 13 - значит 13.

Автор - kNNeR
Дата добавления - 08.11.2020 в 17:26
прохожий2019 Дата: Воскресенье, 08.11.2020, 17:49 | Сообщение № 4
Группа: Проверенные
Ранг: Старожил
Сообщений: 1241
Репутация: 317 ±
Замечаний: 0% ±

365 Beta Channel
сама UDF parse[vba]
Код
Function parse(r, p$)
    s = 0
    With CreateObject("VBScript.RegExp")
        .Pattern = "(" & p & ")(\d+)"
        For Each cell In r
            If .test(cell) Then s = s + .Execute(cell)(0).SubMatches(1)
        Next
    End With
    parse = s
End Function
[/vba]
использование:
Код
=parse(ЕСЛИ(Лист1!$E$7:$E$33=$C$5;Лист1!$H$7:$J$33;"");B8)

проблемой отсутствия года в каждой строке не занимался - тупо протянул
К сообщению приложен файл: 2208735.xls (104.5 Kb)


Сообщение отредактировал прохожий2019 - Воскресенье, 08.11.2020, 17:54
 
Ответить
Сообщениесама UDF parse[vba]
Код
Function parse(r, p$)
    s = 0
    With CreateObject("VBScript.RegExp")
        .Pattern = "(" & p & ")(\d+)"
        For Each cell In r
            If .test(cell) Then s = s + .Execute(cell)(0).SubMatches(1)
        Next
    End With
    parse = s
End Function
[/vba]
использование:
Код
=parse(ЕСЛИ(Лист1!$E$7:$E$33=$C$5;Лист1!$H$7:$J$33;"");B8)

проблемой отсутствия года в каждой строке не занимался - тупо протянул

Автор - прохожий2019
Дата добавления - 08.11.2020 в 17:49
kNNeR Дата: Воскресенье, 08.11.2020, 18:18 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 45
Репутация: 9 ±
Замечаний: 0% ±

Excel 2019
прохожий2019, спасибо.
Все работает.
Спасибо.
 
Ответить
Сообщениепрохожий2019, спасибо.
Все работает.
Спасибо.

Автор - kNNeR
Дата добавления - 08.11.2020 в 18:18
kNNeR Дата: Воскресенье, 08.11.2020, 21:40 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 45
Репутация: 9 ±
Замечаний: 0% ±

Excel 2019
Цитата прохожий2019, 08.11.2020 в 17:49, в сообщении № 4 ()
проблемой отсутствия года в каждой строке не занимался - тупо протянул

Подскажите - как же сделать так , чтобы функция - не считала те строки - в которых не проставлен год ?

У меня же написано в вопросе - что речь идет о диапазоне "Лист1!H7:J"
То есть J - не имеет конкретного адреса строки - он идет вниз от 7 строки до бесконечности.
А у вас указана 33 строка: Лист1!$H$7:$J$33

В общем почему-то если диапазон составляет 2000 строк - эта функция выдает ошибку формулы "#ЗНАЧ"


Сообщение отредактировал kNNeR - Воскресенье, 08.11.2020, 21:49
 
Ответить
Сообщение
Цитата прохожий2019, 08.11.2020 в 17:49, в сообщении № 4 ()
проблемой отсутствия года в каждой строке не занимался - тупо протянул

Подскажите - как же сделать так , чтобы функция - не считала те строки - в которых не проставлен год ?

У меня же написано в вопросе - что речь идет о диапазоне "Лист1!H7:J"
То есть J - не имеет конкретного адреса строки - он идет вниз от 7 строки до бесконечности.
А у вас указана 33 строка: Лист1!$H$7:$J$33

В общем почему-то если диапазон составляет 2000 строк - эта функция выдает ошибку формулы "#ЗНАЧ"

Автор - kNNeR
Дата добавления - 08.11.2020 в 21:40
прохожий2019 Дата: Воскресенье, 08.11.2020, 21:56 | Сообщение № 7
Группа: Проверенные
Ранг: Старожил
Сообщений: 1241
Репутация: 317 ±
Замечаний: 0% ±

365 Beta Channel
Подскажите - как же сделать так , чтобы функция - не считала те строки - в которых не проставлен год ?
где об этом в исходном сообщении? где об этом в примере? ах да,
это я просто так цифру написал


То есть J - не имеет конкретного адреса строки - он идет вниз от 7 строки до бесконечности.
бесконечный диапазон будет обрабатываться бесконечно долго, сомнительно, что вам это нужно

В общем почему-то
в общем это только слова, не подкрепленные примером

зато вы нарушаете правила двух форумов и игнорируете замечания модераторов - дело ваше, конечно, но рассчитывать на помощь с таким отношением весьма самонадеянно
 
Ответить
Сообщение
Подскажите - как же сделать так , чтобы функция - не считала те строки - в которых не проставлен год ?
где об этом в исходном сообщении? где об этом в примере? ах да,
это я просто так цифру написал


То есть J - не имеет конкретного адреса строки - он идет вниз от 7 строки до бесконечности.
бесконечный диапазон будет обрабатываться бесконечно долго, сомнительно, что вам это нужно

В общем почему-то
в общем это только слова, не подкрепленные примером

зато вы нарушаете правила двух форумов и игнорируете замечания модераторов - дело ваше, конечно, но рассчитывать на помощь с таким отношением весьма самонадеянно

Автор - прохожий2019
Дата добавления - 08.11.2020 в 21:56
прохожий2019 Дата: Воскресенье, 08.11.2020, 22:34 | Сообщение № 8
Группа: Проверенные
Ранг: Старожил
Сообщений: 1241
Репутация: 317 ±
Замечаний: 0% ±

365 Beta Channel
речь идет о диапазоне "Лист1!H7:J"
и если вы именно так записываете диапазон, то не только моя UDF, но и любая экселевская функция выдаст ошибку
 
Ответить
Сообщение
речь идет о диапазоне "Лист1!H7:J"
и если вы именно так записываете диапазон, то не только моя UDF, но и любая экселевская функция выдаст ошибку

Автор - прохожий2019
Дата добавления - 08.11.2020 в 22:34
kNNeR Дата: Понедельник, 09.11.2020, 02:08 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 45
Репутация: 9 ±
Замечаний: 0% ±

Excel 2019
Цитата прохожий2019, 08.11.2020 в 22:34, в сообщении № 8 ()
не только моя UDF, но и любая экселевская функция выдаст ошибку

Нет, я записываю нормально.
И в общем - функция тоже работает нормально.

Но есть одно ограничение.
Функция работает только тогда - когда текст во всех ячейках диапазона - либо отсутствует вовсе, либо короткий.
Если текст хотя бы в одной ячейке из указанного диапазона - длинный (например 400 символов) - то функция тут же выдает ошибку.
Она почему-то не может воспринимать длинный текст в ячейке.

Мне вот этот момент непонятен - почему пользовательская функция может воспринимать короткий текст, и не может воспринимать длинный текст.
Нигде в коде записей об ограничении количества символов - я не вижу.
К сообщению приложен файл: 239.xls (103.5 Kb)


Сообщение отредактировал kNNeR - Понедельник, 09.11.2020, 02:09
 
Ответить
Сообщение
Цитата прохожий2019, 08.11.2020 в 22:34, в сообщении № 8 ()
не только моя UDF, но и любая экселевская функция выдаст ошибку

Нет, я записываю нормально.
И в общем - функция тоже работает нормально.

Но есть одно ограничение.
Функция работает только тогда - когда текст во всех ячейках диапазона - либо отсутствует вовсе, либо короткий.
Если текст хотя бы в одной ячейке из указанного диапазона - длинный (например 400 символов) - то функция тут же выдает ошибку.
Она почему-то не может воспринимать длинный текст в ячейке.

Мне вот этот момент непонятен - почему пользовательская функция может воспринимать короткий текст, и не может воспринимать длинный текст.
Нигде в коде записей об ограничении количества символов - я не вижу.

Автор - kNNeR
Дата добавления - 09.11.2020 в 02:08
прохожий2019 Дата: Понедельник, 09.11.2020, 12:32 | Сообщение № 10
Группа: Проверенные
Ранг: Старожил
Сообщений: 1241
Репутация: 317 ±
Замечаний: 0% ±

365 Beta Channel
почему пользовательская функция может воспринимать короткий текст
она-то всё прекрасно воспринимает - см. файл

функция тут же выдает ошибку
а пошагово посмотреть выполнение формулы - ничего, что ошибку выдает ЕСЛИ()? И это ограничение не моего кода, а мелкомягкого
К сообщению приложен файл: 7226830.xls (104.5 Kb)
 
Ответить
Сообщение
почему пользовательская функция может воспринимать короткий текст
она-то всё прекрасно воспринимает - см. файл

функция тут же выдает ошибку
а пошагово посмотреть выполнение формулы - ничего, что ошибку выдает ЕСЛИ()? И это ограничение не моего кода, а мелкомягкого

Автор - прохожий2019
Дата добавления - 09.11.2020 в 12:32
прохожий2019 Дата: Понедельник, 09.11.2020, 15:04 | Сообщение № 11
Группа: Проверенные
Ранг: Старожил
Сообщений: 1241
Репутация: 317 ±
Замечаний: 0% ±

365 Beta Channel
лечение:
Код
=parse(ИНДЕКС(Лист1!H:H;МИН(ЕСЛИ(Лист1!$E$7:$E$34=$C$5;СТРОКА(Лист1!$E$7:$E$34);""))):ИНДЕКС(Лист1!J:J;МАКС(ЕСЛИ(Лист1!$E$7:$E$34=$C$5;СТРОКА(Лист1!$E$7:$E$34);"")));B8)
К сообщению приложен файл: 6783476.xls (107.5 Kb)
 
Ответить
Сообщениелечение:
Код
=parse(ИНДЕКС(Лист1!H:H;МИН(ЕСЛИ(Лист1!$E$7:$E$34=$C$5;СТРОКА(Лист1!$E$7:$E$34);""))):ИНДЕКС(Лист1!J:J;МАКС(ЕСЛИ(Лист1!$E$7:$E$34=$C$5;СТРОКА(Лист1!$E$7:$E$34);"")));B8)

Автор - прохожий2019
Дата добавления - 09.11.2020 в 15:04
kNNeR Дата: Понедельник, 09.11.2020, 15:24 | Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 45
Репутация: 9 ±
Замечаний: 0% ±

Excel 2019
Понятно
 
Ответить
СообщениеПонятно

Автор - kNNeR
Дата добавления - 09.11.2020 в 15:24
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Как функцией извлечь из диапазона сумму чисел после опр.слов (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!