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

Вход

Регистрация

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

 

= Мир MS Excel/Парсер объёма в полном наименовании номенклатуры - Мир MS Excel

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

Excel 2016
Добрый день! Есть задача, которую смог решить только костылями, через формулу + фильтр. Но хотелось бы иметь решение с формулой без лишних танцев с бубном и без макросов.

Есть список номенклатуры, в каждом элементе которого есть обозначение объёма вида *ml, где * - от 1 до 10000.

Есть границы Start и End диапазона для сортировки. Обе границы включены в поиск.

Необходимо вывести объём числом или "-" в зависимости, от того, подходит ли элемент обозначенным границам диапазона.

Я поверхностно знаю формулы Excel, смог пока дойти лишь до
Код
ПСТР([@NAME];ПОИСК("ml";[@NAME])-3;5)
и последующей фильтрацией столбца с ручным выбором подходящих вариантов.

NB. Возможно, что в номенклатуре подстрока "ml" может встретиться как часть названия до обозначения объёма "*ml".
К сообщению приложен файл: 4666994.xlsx(12.9 Kb)


Сообщение отредактировал TSC - Среда, 25.05.2022, 09:17
 
Ответить
СообщениеДобрый день! Есть задача, которую смог решить только костылями, через формулу + фильтр. Но хотелось бы иметь решение с формулой без лишних танцев с бубном и без макросов.

Есть список номенклатуры, в каждом элементе которого есть обозначение объёма вида *ml, где * - от 1 до 10000.

Есть границы Start и End диапазона для сортировки. Обе границы включены в поиск.

Необходимо вывести объём числом или "-" в зависимости, от того, подходит ли элемент обозначенным границам диапазона.

Я поверхностно знаю формулы Excel, смог пока дойти лишь до
Код
ПСТР([@NAME];ПОИСК("ml";[@NAME])-3;5)
и последующей фильтрацией столбца с ручным выбором подходящих вариантов.

NB. Возможно, что в номенклатуре подстрока "ml" может встретиться как часть названия до обозначения объёма "*ml".

Автор - TSC
Дата добавления - 24.05.2022 в 17:45
Anis625 Дата: Вторник, 24.05.2022, 22:11 | Сообщение № 2
Группа: Проверенные
Ранг: Ветеран
Сообщений: 631
Репутация: 30 ±
Замечаний: 0% ±

Excel 2013
TSC,
Файл нужен и желательно показать (без формул) как вы хотите видеть результат
 
Ответить
СообщениеTSC,
Файл нужен и желательно показать (без формул) как вы хотите видеть результат

Автор - Anis625
Дата добавления - 24.05.2022 в 22:11
китин Дата: Среда, 25.05.2022, 07:40 | Сообщение № 3
Группа: Модераторы
Ранг: Экселист
Сообщений: 6853
Репутация: 1050 ±
Замечаний: 0% ±

Excel 2007;2010;2016
TSC, - Прочитайте Правила форума
- Приложите файл с исходными данными и желаемым результатом (можно вручную) в формате Excel размером до 500 кб согласно п.3 Правил форума
- Оформите формулу тегами (в режиме правки поста выделите формулу и нажмите кнопку fx, пояснялка здесь)


Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852
 
Ответить
СообщениеTSC, - Прочитайте Правила форума
- Приложите файл с исходными данными и желаемым результатом (можно вручную) в формате Excel размером до 500 кб согласно п.3 Правил форума
- Оформите формулу тегами (в режиме правки поста выделите формулу и нажмите кнопку fx, пояснялка здесь)

Автор - китин
Дата добавления - 25.05.2022 в 07:40
TSC Дата: Среда, 25.05.2022, 09:15 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 22
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Извините, я сделал файл, но почему-то он не прикрепился при создании темы. Прикрепил в первое сообщение


Сообщение отредактировал TSC - Среда, 25.05.2022, 09:16
 
Ответить
СообщениеИзвините, я сделал файл, но почему-то он не прикрепился при создании темы. Прикрепил в первое сообщение

Автор - TSC
Дата добавления - 25.05.2022 в 09:15
_Boroda_ Дата: Среда, 25.05.2022, 09:19 | Сообщение № 5
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16212
Репутация: 6296 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
У меня не скачивается


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеУ меня не скачивается

Автор - _Boroda_
Дата добавления - 25.05.2022 в 09:19
TSC Дата: Среда, 25.05.2022, 09:33 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 22
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
А так?
К сообщению приложен файл: 3969076.xlsx(12.9 Kb)
 
Ответить
СообщениеА так?

Автор - TSC
Дата добавления - 25.05.2022 в 09:33
msi2102 Дата: Среда, 25.05.2022, 09:51 | Сообщение № 7
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 134
Репутация: 65 ±
Замечаний: 0% ±

Excel 2007
Если перед объемом идет "edp" то можно так
Код
=--СЖПРОБЕЛЫ(ПСТР(Data[@NAME];НАЙТИ("edp";Data[@NAME])+3;НАЙТИ("ml";Data[@NAME])-(НАЙТИ("edp";Data[@NAME])+3)))


Сообщение отредактировал msi2102 - Среда, 25.05.2022, 09:51
 
Ответить
СообщениеЕсли перед объемом идет "edp" то можно так
Код
=--СЖПРОБЕЛЫ(ПСТР(Data[@NAME];НАЙТИ("edp";Data[@NAME])+3;НАЙТИ("ml";Data[@NAME])-(НАЙТИ("edp";Data[@NAME])+3)))

Автор - msi2102
Дата добавления - 25.05.2022 в 09:51
bigorq Дата: Среда, 25.05.2022, 10:01 | Сообщение № 8
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 116
Репутация: 25 ±
Замечаний: 0% ±

Нет
перед объемом идет "edp

ниже и edc есть
 
Ответить
Сообщение
перед объемом идет "edp

ниже и edc есть

Автор - bigorq
Дата добавления - 25.05.2022 в 10:01
китин Дата: Среда, 25.05.2022, 10:03 | Сообщение № 9
Группа: Модераторы
Ранг: Экселист
Сообщений: 6853
Репутация: 1050 ±
Замечаний: 0% ±

Excel 2007;2010;2016
вот такой монстр
Код
=ЕСЛИОШИБКА(ЕСЛИ(И(--СЖПРОБЕЛЫ(ПСТР(Data[@NAME];(ПОИСК("ml";Data[@NAME]))-ДЛСТР(Start);ДЛСТР(Start)))>=Start;--СЖПРОБЕЛЫ(ПСТР(Data[@NAME];(ПОИСК("ml";Data[@NAME]))-ДЛСТР(Start);ДЛСТР(Start)))<=End);--СЖПРОБЕЛЫ(ПСТР(Data[@NAME];(ПОИСК("ml";Data[@NAME]))-ДЛСТР(Start);ДЛСТР(Start)));"-");"-")

поправил
К сообщению приложен файл: 5889438.xlsx(13.7 Kb)


Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852


Сообщение отредактировал китин - Среда, 25.05.2022, 10:06
 
Ответить
Сообщениевот такой монстр
Код
=ЕСЛИОШИБКА(ЕСЛИ(И(--СЖПРОБЕЛЫ(ПСТР(Data[@NAME];(ПОИСК("ml";Data[@NAME]))-ДЛСТР(Start);ДЛСТР(Start)))>=Start;--СЖПРОБЕЛЫ(ПСТР(Data[@NAME];(ПОИСК("ml";Data[@NAME]))-ДЛСТР(Start);ДЛСТР(Start)))<=End);--СЖПРОБЕЛЫ(ПСТР(Data[@NAME];(ПОИСК("ml";Data[@NAME]))-ДЛСТР(Start);ДЛСТР(Start)));"-");"-")

поправил

Автор - китин
Дата добавления - 25.05.2022 в 10:03
_Boroda_ Дата: Среда, 25.05.2022, 10:13 | Сообщение № 10
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16212
Репутация: 6296 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
TSC, а что Вы дальше с этим делать будете? Другими словами - Вам отображение нужно или реальные значения в тексте или в числах?
Короче, 3 варианта.
С Условным форматированием
Код
=--СЖПРОБЕЛЫ(ПРАВБ(ПОДСТАВИТЬ(ПСТР([@NAME];1;МАКС(ЕСЛИОШИБКА(НАЙТИ(СТРОКА($1:$10)-1&"ml ";[@NAME]&" ");)));" ";ПОВТОР(" ";99));99))

Текстовый результат
Код
=ТЕКСТ(--СЖПРОБЕЛЫ(ПРАВБ(ПОДСТАВИТЬ(ПСТР([@NAME];1;МАКС(ЕСЛИОШИБКА(НАЙТИ(СТРОКА($1:$10)-1&"ml ";[@NAME]&" ");)));" ";ПОВТОР(" ";99));99));"[<100]-;[>125]-;0")

С обычным форматированием, скрывающим нули
Код
=--ТЕКСТ(--СЖПРОБЕЛЫ(ПРАВБ(ПОДСТАВИТЬ(ПСТР([@NAME];1;МАКС(ЕСЛИОШИБКА(НАЙТИ(СТРОКА($1:$10)-1&"ml ";[@NAME]&" ");)));" ";ПОВТОР(" ";99));99));"[<100]\0;[>125]\0;0")


Учитывается возможность наличия таких текстов
12 PAml RFUMEURS FRANCAIS VILLANDRY edp 100ml TESTER
24 ELIXIR mlAZUR edp 100ml TESTER
19-69 L'AIR BAmlRBES edp 100ml
К сообщению приложен файл: 3969076_2.xlsx(17.4 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995


Сообщение отредактировал _Boroda_ - Среда, 25.05.2022, 10:15
 
Ответить
СообщениеTSC, а что Вы дальше с этим делать будете? Другими словами - Вам отображение нужно или реальные значения в тексте или в числах?
Короче, 3 варианта.
С Условным форматированием
Код
=--СЖПРОБЕЛЫ(ПРАВБ(ПОДСТАВИТЬ(ПСТР([@NAME];1;МАКС(ЕСЛИОШИБКА(НАЙТИ(СТРОКА($1:$10)-1&"ml ";[@NAME]&" ");)));" ";ПОВТОР(" ";99));99))

Текстовый результат
Код
=ТЕКСТ(--СЖПРОБЕЛЫ(ПРАВБ(ПОДСТАВИТЬ(ПСТР([@NAME];1;МАКС(ЕСЛИОШИБКА(НАЙТИ(СТРОКА($1:$10)-1&"ml ";[@NAME]&" ");)));" ";ПОВТОР(" ";99));99));"[<100]-;[>125]-;0")

С обычным форматированием, скрывающим нули
Код
=--ТЕКСТ(--СЖПРОБЕЛЫ(ПРАВБ(ПОДСТАВИТЬ(ПСТР([@NAME];1;МАКС(ЕСЛИОШИБКА(НАЙТИ(СТРОКА($1:$10)-1&"ml ";[@NAME]&" ");)));" ";ПОВТОР(" ";99));99));"[<100]\0;[>125]\0;0")


Учитывается возможность наличия таких текстов
12 PAml RFUMEURS FRANCAIS VILLANDRY edp 100ml TESTER
24 ELIXIR mlAZUR edp 100ml TESTER
19-69 L'AIR BAmlRBES edp 100ml

Автор - _Boroda_
Дата добавления - 25.05.2022 в 10:13
msi2102 Дата: Среда, 25.05.2022, 10:14 | Сообщение № 11
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 134
Репутация: 65 ±
Замечаний: 0% ±

Excel 2007
ниже и edc есть

С налету не вышло. Не помню когда появился ФИЛЬТР.XML, по моему 2013
[vba]
Код
=--ПОДСТАВИТЬ(ВПР("*ml*";ФИЛЬТР.XML("<t><s>"&ПОДСТАВИТЬ(ПОДСТАВИТЬ([@NAME];"&";"");" ";"</s><s>")&"</s></t>";"//s");1;0);"ml";"")
[/vba]
К сообщению приложен файл: 5997034.xlsx(15.0 Kb)


Сообщение отредактировал msi2102 - Среда, 25.05.2022, 10:18
 
Ответить
Сообщение
ниже и edc есть

С налету не вышло. Не помню когда появился ФИЛЬТР.XML, по моему 2013
[vba]
Код
=--ПОДСТАВИТЬ(ВПР("*ml*";ФИЛЬТР.XML("<t><s>"&ПОДСТАВИТЬ(ПОДСТАВИТЬ([@NAME];"&";"");" ";"</s><s>")&"</s></t>";"//s");1;0);"ml";"")
[/vba]

Автор - msi2102
Дата добавления - 25.05.2022 в 10:14
msi2102 Дата: Среда, 25.05.2022, 10:45 | Сообщение № 12
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 134
Репутация: 65 ±
Замечаний: 0% ±

Excel 2007
Учитывается возможность наличия таких текстов
12 PAml RFUMEURS FRANCAIS VILLANDRY edp 100ml TESTER
24 ELIXIR mlAZUR edp 100ml TESTER
19-69 L'AIR BAmlRBES edp 100ml

Попробовал переписать с учетом условий, длинненько получилось :)
[vba]
Код
=СУММ(ЕСЛИОШИБКА(ЕСЛИ(ПОИСК("ml";ФИЛЬТР.XML("<t><s>"&ПОДСТАВИТЬ(ПОДСТАВИТЬ([@NAME];"&";"");" ";"</s><s>")&"</s></t>";"//s"))>0;ПОДСТАВИТЬ(ФИЛЬТР.XML("<t><s>"&ПОДСТАВИТЬ(ПОДСТАВИТЬ([@NAME];"&";"");" ";"</s><s>")&"</s></t>";"//s");"ml";""))*1;0))
[/vba]
К сообщению приложен файл: 2444249.xlsx(15.5 Kb)


Сообщение отредактировал msi2102 - Среда, 25.05.2022, 10:46
 
Ответить
Сообщение
Учитывается возможность наличия таких текстов
12 PAml RFUMEURS FRANCAIS VILLANDRY edp 100ml TESTER
24 ELIXIR mlAZUR edp 100ml TESTER
19-69 L'AIR BAmlRBES edp 100ml

Попробовал переписать с учетом условий, длинненько получилось :)
[vba]
Код
=СУММ(ЕСЛИОШИБКА(ЕСЛИ(ПОИСК("ml";ФИЛЬТР.XML("<t><s>"&ПОДСТАВИТЬ(ПОДСТАВИТЬ([@NAME];"&";"");" ";"</s><s>")&"</s></t>";"//s"))>0;ПОДСТАВИТЬ(ФИЛЬТР.XML("<t><s>"&ПОДСТАВИТЬ(ПОДСТАВИТЬ([@NAME];"&";"");" ";"</s><s>")&"</s></t>";"//s");"ml";""))*1;0))
[/vba]

Автор - msi2102
Дата добавления - 25.05.2022 в 10:45
TSC Дата: Среда, 25.05.2022, 12:55 | Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 22
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
С налету не вышло. Не помню когда появился ФИЛЬТР.XML, по моему 2013

Спасибо! Не знал о такой функции, почитаю о ней
 
Ответить
Сообщение
С налету не вышло. Не помню когда появился ФИЛЬТР.XML, по моему 2013

Спасибо! Не знал о такой функции, почитаю о ней

Автор - TSC
Дата добавления - 25.05.2022 в 12:55
TSC Дата: Среда, 25.05.2022, 12:59 | Сообщение № 14
Группа: Пользователи
Ранг: Новичок
Сообщений: 22
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
вот такой монстр


У меня почему-то некорректно работает, но если покопаться, думаю разберусь) В любом случае спасибо)
 
Ответить
Сообщение
вот такой монстр


У меня почему-то некорректно работает, но если покопаться, думаю разберусь) В любом случае спасибо)

Автор - TSC
Дата добавления - 25.05.2022 в 12:59
TSC Дата: Среда, 25.05.2022, 13:04 | Сообщение № 15
Группа: Пользователи
Ранг: Новичок
Сообщений: 22
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
TSC, а что Вы дальше с этим делать будете? Другими словами - Вам отображение нужно или реальные значения в тексте или в числах?


Да хочу просто сделать удобный фильтр для файла где 30 тысяч строк)
Спасибо, всё верно работает.

А вообще работа со строками в Excel регистрозависимая?
 
Ответить
Сообщение
TSC, а что Вы дальше с этим делать будете? Другими словами - Вам отображение нужно или реальные значения в тексте или в числах?


Да хочу просто сделать удобный фильтр для файла где 30 тысяч строк)
Спасибо, всё верно работает.

А вообще работа со строками в Excel регистрозависимая?

Автор - TSC
Дата добавления - 25.05.2022 в 13:04
_Boroda_ Дата: Среда, 25.05.2022, 13:30 | Сообщение № 16
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16212
Репутация: 6296 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
А вообще работа со строками в Excel регистрозависимая?

А это как написать. ПОИСК - нет, НАЙТИ - да, ПОДСТАВИТЬ тоже да


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
А вообще работа со строками в Excel регистрозависимая?

А это как написать. ПОИСК - нет, НАЙТИ - да, ПОДСТАВИТЬ тоже да

Автор - _Boroda_
Дата добавления - 25.05.2022 в 13:30
msi2102 Дата: Среда, 25.05.2022, 15:37 | Сообщение № 17
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 134
Репутация: 65 ±
Замечаний: 0% ±

Excel 2007
Можно ещё UDF
[vba]
Код
Function ml(cell$)
    With CreateObject("VBScript.RegExp")
        .Global = True: .Pattern = "\d+\s?(?=ml)"
        If .test(cell) Then ml = --Trim(.Execute(cell)(0)) Else ml = "Отсутствует"
    End With
End Function
[/vba]
К сообщению приложен файл: 3969076_1.xlsm(23.3 Kb)
 
Ответить
СообщениеМожно ещё UDF
[vba]
Код
Function ml(cell$)
    With CreateObject("VBScript.RegExp")
        .Global = True: .Pattern = "\d+\s?(?=ml)"
        If .test(cell) Then ml = --Trim(.Execute(cell)(0)) Else ml = "Отсутствует"
    End With
End Function
[/vba]

Автор - msi2102
Дата добавления - 25.05.2022 в 15:37
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Парсер объёма в полном наименовании номенклатуры (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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