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

Вход

Регистрация

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

 

= Мир MS Excel/Поиск по ячейкам с игнорированием знаков препинания и пробел - Мир MS Excel

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

Excel 2010
Добрый день.

В приложении фрагмент прайса. Мне нужно осуществлять поиск по артикулу, но так чтоб он игнорировал знаки препинания и пробелы.

Т.е. я воожу значение 68524 и он должен указать на ячейку с этим значением 68.524.

45.654
68.524
688.54
45 559

Помогите пожалуйста
К сообщению приложен файл: prays_31.03.16_.xls (37.0 Kb)
 
Ответить
СообщениеДобрый день.

В приложении фрагмент прайса. Мне нужно осуществлять поиск по артикулу, но так чтоб он игнорировал знаки препинания и пробелы.

Т.е. я воожу значение 68524 и он должен указать на ячейку с этим значением 68.524.

45.654
68.524
688.54
45 559

Помогите пожалуйста

Автор - fantasy1133
Дата добавления - 01.04.2016 в 17:22
akobir Дата: Пятница, 01.04.2016, 17:27 | Сообщение № 2
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 201
Репутация: 9 ±
Замечаний: 0% ±

Excel 2010
fantasy1133, попробуйте избавиться от всех ненужных символов, заменив их на пустоту. Или Вам важно разделение?


e-mail: akobir.ismailov@gmail.com
 
Ответить
Сообщениеfantasy1133, попробуйте избавиться от всех ненужных символов, заменив их на пустоту. Или Вам важно разделение?

Автор - akobir
Дата добавления - 01.04.2016 в 17:27
fantasy1133 Дата: Пятница, 01.04.2016, 17:53 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Мне важно найти артикул и соответсвенно наименование к нему. Мне важно чтоб он искал артикул вне зависимости от пробелом и точек

Как это сделать, подскажите пожалуйста.
 
Ответить
СообщениеМне важно найти артикул и соответсвенно наименование к нему. Мне важно чтоб он искал артикул вне зависимости от пробелом и точек

Как это сделать, подскажите пожалуйста.

Автор - fantasy1133
Дата добавления - 01.04.2016 в 17:53
abtextime Дата: Пятница, 01.04.2016, 17:56 | Сообщение № 4
Группа: Проверенные
Ранг: Ветеран
Сообщений: 828
Репутация: 117 ±
Замечаний: 0% ±

Excel 2010
УФ

Код
=НЕ(ЕОШИБКА(ПОИСК($J$1;ПОДСТАВИТЬ(ПОДСТАВИТЬ($B2;".";"");"-";""))))


Контекст вводится в ячейку J1
К сообщению приложен файл: _prays_31.03.16.xls (41.5 Kb)


Сообщение отредактировал abtextime - Пятница, 01.04.2016, 17:57
 
Ответить
СообщениеУФ

Код
=НЕ(ЕОШИБКА(ПОИСК($J$1;ПОДСТАВИТЬ(ПОДСТАВИТЬ($B2;".";"");"-";""))))


Контекст вводится в ячейку J1

Автор - abtextime
Дата добавления - 01.04.2016 в 17:56
fantasy1133 Дата: Пятница, 01.04.2016, 18:04 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Спасибо большое. то что нужно, но прайс у нас просто огромный и пролистывать его в поискать выделенных ячеек будет сложно. Можно как то эту проблему решить?
 
Ответить
СообщениеСпасибо большое. то что нужно, но прайс у нас просто огромный и пролистывать его в поискать выделенных ячеек будет сложно. Можно как то эту проблему решить?

Автор - fantasy1133
Дата добавления - 01.04.2016 в 18:04
fantasy1133 Дата: Пятница, 01.04.2016, 18:20 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
И ещё подскажите пожалуйста, а то я в экселе не понимаю ничего. Эту формулу куда вставлять? А то прайсы каждую неделю буду обновляться и нужно постоянно формулу вписывать.
 
Ответить
СообщениеИ ещё подскажите пожалуйста, а то я в экселе не понимаю ничего. Эту формулу куда вставлять? А то прайсы каждую неделю буду обновляться и нужно постоянно формулу вписывать.

Автор - fantasy1133
Дата добавления - 01.04.2016 в 18:20
TimSha Дата: Пятница, 01.04.2016, 18:24 | Сообщение № 7
Группа: Проверенные
Ранг: Ветеран
Сообщений: 627
Репутация: 94 ±
Замечаний: 0% ±

Excel 2013 Pro +
я в экселе не понимаю ничего.

off Так может на начальные курсы записаться?! А если это - ваша работа, то заказывать решение, а еще подумать - нужна ли вам такая работа... Без обид, пжлста... ;)


"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. ;)
 
Ответить
Сообщение
я в экселе не понимаю ничего.

off Так может на начальные курсы записаться?! А если это - ваша работа, то заказывать решение, а еще подумать - нужна ли вам такая работа... Без обид, пжлста... ;)

Автор - TimSha
Дата добавления - 01.04.2016 в 18:24
abtextime Дата: Пятница, 01.04.2016, 18:25 | Сообщение № 8
Группа: Проверенные
Ранг: Ветеран
Сообщений: 828
Репутация: 117 ±
Замечаний: 0% ±

Excel 2010
1. Можете сделать умную таблицу или автофильтр и фильтровать по цвету (пример в файле)
2. Формулы вводить так: выделяете диапазон действия - потом Главная - Условное форматирование - Создать правило - Использовать формулу для определения форматируемых ячеек

Всё можно сделать намного симпатичнее, но макросами
К сообщению приложен файл: 2889953.xls (43.5 Kb)
 
Ответить
Сообщение1. Можете сделать умную таблицу или автофильтр и фильтровать по цвету (пример в файле)
2. Формулы вводить так: выделяете диапазон действия - потом Главная - Условное форматирование - Создать правило - Использовать формулу для определения форматируемых ячеек

Всё можно сделать намного симпатичнее, но макросами

Автор - abtextime
Дата добавления - 01.04.2016 в 18:25
abtextime Дата: Пятница, 01.04.2016, 18:30 | Сообщение № 9
Группа: Проверенные
Ранг: Ветеран
Сообщений: 828
Репутация: 117 ±
Замечаний: 0% ±

Excel 2010
TimSha ,

[offtop]Я бы не сказал, что УФ с формулами (а не стандартными средствами) начальный уровень.
Я уже делал довольно изощренные расчетные модули на Excel, но УФ вообще не применял и особо не знал. Появилась задача, которая требовала УФ - почитал. Вот сейчас никак в тему с массивными формулами не въеду - просто жизнь не заставляет, для текущих задач с лихвой хватает уже известного мне инструментария.
 
Ответить
СообщениеTimSha ,

[offtop]Я бы не сказал, что УФ с формулами (а не стандартными средствами) начальный уровень.
Я уже делал довольно изощренные расчетные модули на Excel, но УФ вообще не применял и особо не знал. Появилась задача, которая требовала УФ - почитал. Вот сейчас никак в тему с массивными формулами не въеду - просто жизнь не заставляет, для текущих задач с лихвой хватает уже известного мне инструментария.

Автор - abtextime
Дата добавления - 01.04.2016 в 18:30
fantasy1133 Дата: Пятница, 01.04.2016, 18:39 | Сообщение № 10
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Ещё вопросики:

-А как указать поле в которое будет вводится условие?
-И как удалить все пробелы в артикулах?
 
Ответить
СообщениеЕщё вопросики:

-А как указать поле в которое будет вводится условие?
-И как удалить все пробелы в артикулах?

Автор - fantasy1133
Дата добавления - 01.04.2016 в 18:39
abtextime Дата: Пятница, 01.04.2016, 18:40 | Сообщение № 11
Группа: Проверенные
Ранг: Ветеран
Сообщений: 828
Репутация: 117 ±
Замечаний: 0% ±

Excel 2010
fantasy1133 ,

Подумайте еще о том, чтобы для быстрого обзора прайса применить сводные таблицы. Посмотрите файл.

Это как идея, а не как решение ...
К сообщению приложен файл: 6885514.xls (54.5 Kb)
 
Ответить
Сообщениеfantasy1133 ,

Подумайте еще о том, чтобы для быстрого обзора прайса применить сводные таблицы. Посмотрите файл.

Это как идея, а не как решение ...

Автор - abtextime
Дата добавления - 01.04.2016 в 18:40
Manyasha Дата: Пятница, 01.04.2016, 18:41 | Сообщение № 12
Группа: Модераторы
Ранг: Старожил
Сообщений: 2198
Репутация: 898 ±
Замечаний: 0% ±

Excel 2010, 2016
вариант с udf для доп. столбца. Вернет все цифры из строки, тогда искать можно по этому доп столбцу
[vba]
Код
Function return_numbers(s As String)
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "\d"
        Set obj = .Execute(s)
        With obj
            If .Count = 0 Then return_numbers = "": Exit Function
            For i = 0 To .Count - 1
                resStr = resStr & .Item(i)
            Next i
        End With
    End With
    return_numbers = resStr
End Function
[/vba]

Чтобы увидеть функцию нажмите alt+f11 (или Вкладка Разработчик - Visual Basic)
К сообщению приложен файл: 123456.xls (49.0 Kb)


ЯД: 410013299366744 WM: R193491431804
 
Ответить
Сообщениевариант с udf для доп. столбца. Вернет все цифры из строки, тогда искать можно по этому доп столбцу
[vba]
Код
Function return_numbers(s As String)
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "\d"
        Set obj = .Execute(s)
        With obj
            If .Count = 0 Then return_numbers = "": Exit Function
            For i = 0 To .Count - 1
                resStr = resStr & .Item(i)
            Next i
        End With
    End With
    return_numbers = resStr
End Function
[/vba]

Чтобы увидеть функцию нажмите alt+f11 (или Вкладка Разработчик - Visual Basic)

Автор - Manyasha
Дата добавления - 01.04.2016 в 18:41
abtextime Дата: Пятница, 01.04.2016, 18:45 | Сообщение № 13
Группа: Проверенные
Ранг: Ветеран
Сообщений: 828
Репутация: 117 ±
Замечаний: 0% ±

Excel 2010
Как указать поле? это уже от Вас зависит, оформляйте, как хотите . В формуле для УФ это поле сейчас фигурирует как $J$1, слева от него стоит слово "поиск"

чистку лишних пробелом можно добавить еще одним вложенным ПОДСТАВИТЬ() - p.s. наверняка есть более изящные решения, не суть ...
Код

=НЕ(ЕОШИБКА(ПОИСК($J$1;ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ($B3;".";"");"-";"");" ";"")))
 
Ответить
СообщениеКак указать поле? это уже от Вас зависит, оформляйте, как хотите . В формуле для УФ это поле сейчас фигурирует как $J$1, слева от него стоит слово "поиск"

чистку лишних пробелом можно добавить еще одним вложенным ПОДСТАВИТЬ() - p.s. наверняка есть более изящные решения, не суть ...
Код

=НЕ(ЕОШИБКА(ПОИСК($J$1;ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ($B3;".";"");"-";"");" ";"")))

Автор - abtextime
Дата добавления - 01.04.2016 в 18:45
fantasy1133 Дата: Пятница, 01.04.2016, 18:47 | Сообщение № 14
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Manyasha,

Отличный вариант, только не ищет по этим значением поиск через CTRL+F
[moder]У меня все ищет. Область поиска - значения поставьте[/moder]


Сообщение отредактировал Manyasha - Пятница, 01.04.2016, 18:53
 
Ответить
СообщениеManyasha,

Отличный вариант, только не ищет по этим значением поиск через CTRL+F
[moder]У меня все ищет. Область поиска - значения поставьте[/moder]

Автор - fantasy1133
Дата добавления - 01.04.2016 в 18:47
abtextime Дата: Пятница, 01.04.2016, 18:50 | Сообщение № 15
Группа: Проверенные
Ранг: Ветеран
Сообщений: 828
Репутация: 117 ±
Замечаний: 0% ±

Excel 2010
По варианту Manyasha

А Вы скопируйте формулы в значения - Ctrl-C - Ctrl-Alt-V- "значения"


Сообщение отредактировал abtextime - Пятница, 01.04.2016, 18:50
 
Ответить
СообщениеПо варианту Manyasha

А Вы скопируйте формулы в значения - Ctrl-C - Ctrl-Alt-V- "значения"

Автор - abtextime
Дата добавления - 01.04.2016 в 18:50
fantasy1133 Дата: Пятница, 01.04.2016, 19:16 | Сообщение № 16
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Manyasha, Подскажите пожалуйста по подробней где и как это прописать. Мне нужно будет каждую неделю это проделовать
 
Ответить
СообщениеManyasha, Подскажите пожалуйста по подробней где и как это прописать. Мне нужно будет каждую неделю это проделовать

Автор - fantasy1133
Дата добавления - 01.04.2016 в 19:16
Manyasha Дата: Пятница, 01.04.2016, 19:31 | Сообщение № 17
Группа: Модераторы
Ранг: Старожил
Сообщений: 2198
Репутация: 898 ±
Замечаний: 0% ±

Excel 2010, 2016
Открываете редактор VBA
alt+f11 (или Вкладка Разработчик - Visual Basic)

Слева во вкладке Project щелкаете правой кнопкой мышки - insert - module. Копируете в появившееся окно код функции. (Или просто мышкой перетащите модуль из проекта моего файла в проект Вашего).


в ячейку на листе вписываете формулу
Код
=return_numbers(B2)

Протягиваете ее вниз.

Чтобы найти какое-то значение: ctrl+f - Параметры - Область поиска: значения.
К сообщению приложен файл: 0091399.jpg (37.3 Kb)


ЯД: 410013299366744 WM: R193491431804
 
Ответить
СообщениеОткрываете редактор VBA
alt+f11 (или Вкладка Разработчик - Visual Basic)

Слева во вкладке Project щелкаете правой кнопкой мышки - insert - module. Копируете в появившееся окно код функции. (Или просто мышкой перетащите модуль из проекта моего файла в проект Вашего).


в ячейку на листе вписываете формулу
Код
=return_numbers(B2)

Протягиваете ее вниз.

Чтобы найти какое-то значение: ctrl+f - Параметры - Область поиска: значения.

Автор - Manyasha
Дата добавления - 01.04.2016 в 19:31
fantasy1133 Дата: Пятница, 01.04.2016, 20:16 | Сообщение № 18
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
abtextime, не помогло(
 
Ответить
Сообщениеabtextime, не помогло(

Автор - fantasy1133
Дата добавления - 01.04.2016 в 20:16
fantasy1133 Дата: Пятница, 01.04.2016, 20:28 | Сообщение № 19
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Что не так блин((
 
Ответить
СообщениеЧто не так блин((

Автор - fantasy1133
Дата добавления - 01.04.2016 в 20:28
fantasy1133 Дата: Пятница, 01.04.2016, 20:35 | Сообщение № 20
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Не получается что то
К сообщению приложен файл: 4615963.jpg (18.0 Kb)
 
Ответить
СообщениеНе получается что то

Автор - fantasy1133
Дата добавления - 01.04.2016 в 20:35
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Поиск по ячейкам с игнорированием знаков препинания и пробел (Формулы/Formulas)
  • Страница 1 из 2
  • 1
  • 2
  • »
Поиск:

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