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

Вход

Регистрация

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

 

= Мир MS Excel/Проверка наличия заданного фрагмента текста в ячейке. - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Проверка наличия заданного фрагмента текста в ячейке. (Формулы)
Проверка наличия заданного фрагмента текста в ячейке.
Ralf Дата: Среда, 03.07.2013, 14:08 | Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 71
Репутация: 5 ±
Замечаний: 0% ±

Салют!
Есть одна таблица, данные организованы в виде списка.
Все поля кроме "Вид услуг" экспортируются из 1С. Значения в поле "Вид услуг" ставлю вручную, в зависимости от информации в соседних полях.
Помогите придумать формулу покороче для "Вид услуг", которая возвращала бы значения на основе поля "Документ":
1) Если значение содержит "пломб" или "прибор" (или поле "Аналитика Кт" содержит "Услуги ЦЭЛ"), возвращаем "прочие", если нет - идём дальше
2) Если значение содержит "аренд", возвращаем "аренда", если нет - возвращаем "ку"
Оригинальный файл XLSM (содержит макросы), так что можно наверное и пользовательской функцией
К сообщению приложен файл: 51.xls (27.0 Kb)


Сообщение отредактировал Ralf - Среда, 03.07.2013, 14:21
 
Ответить
СообщениеСалют!
Есть одна таблица, данные организованы в виде списка.
Все поля кроме "Вид услуг" экспортируются из 1С. Значения в поле "Вид услуг" ставлю вручную, в зависимости от информации в соседних полях.
Помогите придумать формулу покороче для "Вид услуг", которая возвращала бы значения на основе поля "Документ":
1) Если значение содержит "пломб" или "прибор" (или поле "Аналитика Кт" содержит "Услуги ЦЭЛ"), возвращаем "прочие", если нет - идём дальше
2) Если значение содержит "аренд", возвращаем "аренда", если нет - возвращаем "ку"
Оригинальный файл XLSM (содержит макросы), так что можно наверное и пользовательской функцией
Автор - Ralf
Дата добавления - 03.07.2013 в 14:08
Serge_007 Дата: Среда, 03.07.2013, 14:33 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Код
=ВЫБОР(СУММ(ЕЧИСЛО(ПОИСК("аренд";B2))*1;ЕЧИСЛО(ПОИСК("пломб";B2))*2)+1;"ку";"аренда";"прочие")

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
Код
=ВЫБОР(СУММ(ЕЧИСЛО(ПОИСК("аренд";B2))*1;ЕЧИСЛО(ПОИСК("пломб";B2))*2)+1;"ку";"аренда";"прочие")
Автор - Serge_007
Дата добавления - 03.07.2013 в 14:33
Ralf Дата: Среда, 03.07.2013, 14:45 | Сообщение № 3
Группа: Пользователи
Ранг: Участник
Сообщений: 71
Репутация: 5 ±
Замечаний: 0% ±

Serge_007, ого какая оригинальная формула! Отлично работает и масштабируется, благодарю за содействие
 
Ответить
СообщениеSerge_007, ого какая оригинальная формула! Отлично работает и масштабируется, благодарю за содействие
Автор - Ralf
Дата добавления - 03.07.2013 в 14:45
vikttur Дата: Среда, 03.07.2013, 20:27 | Сообщение № 4
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2941
Репутация: 526 ±
Замечаний: 0% ±

[offtop]-Прибор?
-120!
-Что 120?
-А что Прибор?[/offtop]
В формулу добавил слово из анекдота smile
В первой части - вариант без ЕЧИСЛО (больше примеров, хороших и разных!):
Код
=ЕСЛИ(ПОИСК("аренд";B2&"аренд")<ДЛСТР(B2);"аренда";ЕСЛИ(ИЛИ(ЕЧИСЛО(ПОИСК({"пломб";"прибор"};B2)));"прочие";"ку"))


Сообщение отредактировал vikttur - Среда, 03.07.2013, 20:27
 
Ответить
Сообщение[offtop]-Прибор?
-120!
-Что 120?
-А что Прибор?[/offtop]
В формулу добавил слово из анекдота smile
В первой части - вариант без ЕЧИСЛО (больше примеров, хороших и разных!):
Код
=ЕСЛИ(ПОИСК("аренд";B2&"аренд")<ДЛСТР(B2);"аренда";ЕСЛИ(ИЛИ(ЕЧИСЛО(ПОИСК({"пломб";"прибор"};B2)));"прочие";"ку"))
Автор - vikttur
Дата добавления - 03.07.2013 в 20:27
AlexM Дата: Четверг, 04.07.2013, 00:35 | Сообщение № 5
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1129 ±
Замечаний: 0% ±

Excel 2003
Еще можно так
Код
=ВЫБОР(ЕОШ(ПОИСК("аренд";B2))+ЕОШ(ПОИСК("пломб";B2))*2;"прочие";"аренда";"ку")

и формула массива
Код
=ВЫБОР(СУММ(ЕОШ(ПОИСК({"аренд":"пломб"};B2))*{1:2});"прочие";"аренда";"ку")


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Сообщение отредактировал AlexM - Четверг, 04.07.2013, 00:52
 
Ответить
СообщениеЕще можно так
Код
=ВЫБОР(ЕОШ(ПОИСК("аренд";B2))+ЕОШ(ПОИСК("пломб";B2))*2;"прочие";"аренда";"ку")

и формула массива
Код
=ВЫБОР(СУММ(ЕОШ(ПОИСК({"аренд":"пломб"};B2))*{1:2});"прочие";"аренда";"ку")
Автор - AlexM
Дата добавления - 04.07.2013 в 00:35
DV Дата: Четверг, 04.07.2013, 03:47 | Сообщение № 6
Группа: Друзья
Ранг: Обитатель
Сообщений: 286
Репутация: 194 ±
Замечаний: 0% ±

Excel 2010 RUS
Если уж исходить из ТЗ, то должно быть так:
Код
=ЕСЛИ(СЧЁТ(ПОИСК({"пломб";"прибор"};B2);ПОИСК("Услуги ЦЭЛ";D2));"прочие";ЕСЛИ(СЧЁТ(ПОИСК("аренд";B2));"аренда";"ку"))
 
Ответить
СообщениеЕсли уж исходить из ТЗ, то должно быть так:
Код
=ЕСЛИ(СЧЁТ(ПОИСК({"пломб";"прибор"};B2);ПОИСК("Услуги ЦЭЛ";D2));"прочие";ЕСЛИ(СЧЁТ(ПОИСК("аренд";B2));"аренда";"ку"))
Автор - DV
Дата добавления - 04.07.2013 в 03:47
AlexM Дата: Четверг, 04.07.2013, 08:48 | Сообщение № 7
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1129 ±
Замечаний: 0% ±

Excel 2003
Цитата (DV)
Если уж исходить из ТЗ, то должно быть так:

Точно, проглядел.
тогда так, формула массива
Код
=ВЫБОР(СУММ(ЕОШ(ПОИСК({"аренд":"пломб"};B2))*{1:2})^ЕОШ(ПОИСК("Услуги ЦЭЛ";D2));"прочие";"аренда";"ку")


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
Сообщение
Цитата (DV)
Если уж исходить из ТЗ, то должно быть так:

Точно, проглядел.
тогда так, формула массива
Код
=ВЫБОР(СУММ(ЕОШ(ПОИСК({"аренд":"пломб"};B2))*{1:2})^ЕОШ(ПОИСК("Услуги ЦЭЛ";D2));"прочие";"аренда";"ку")
Автор - AlexM
Дата добавления - 04.07.2013 в 08:48
Serge_007 Дата: Четверг, 04.07.2013, 08:53 | Сообщение № 8
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Цитата (DV)
Если уж исходить из ТЗ
А можно и читать между строк smile

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
Цитата (DV)
Если уж исходить из ТЗ
А можно и читать между строк smile
Автор - Serge_007
Дата добавления - 04.07.2013 в 08:53
Ralf Дата: Четверг, 04.07.2013, 17:14 | Сообщение № 9
Группа: Пользователи
Ранг: Участник
Сообщений: 71
Репутация: 5 ±
Замечаний: 0% ±

спасибо всем отписавшимся, пока остановился на безмассивном варианте
Код
=ЕСЛИ(ЕЧИСЛО(ПОИСК("ЦЭЛ";D7))*1=1;"прочие";ВЫБОР(СУММ(ЕЧИСЛО(ПОИСК("аренд";B7))*1;ЕЧИСЛО(ПОИСК("пломб";B7))*2;ЕЧИСЛО(ПОИСК("прибор";B7))*3)+1;"ку";"аренда";"прочие";"прочие"))

распознаёт виды услуг безошибочно, в случае чего буду корректировать, главная-то задача решена
 
Ответить
Сообщениеспасибо всем отписавшимся, пока остановился на безмассивном варианте
Код
=ЕСЛИ(ЕЧИСЛО(ПОИСК("ЦЭЛ";D7))*1=1;"прочие";ВЫБОР(СУММ(ЕЧИСЛО(ПОИСК("аренд";B7))*1;ЕЧИСЛО(ПОИСК("пломб";B7))*2;ЕЧИСЛО(ПОИСК("прибор";B7))*3)+1;"ку";"аренда";"прочие";"прочие"))

распознаёт виды услуг безошибочно, в случае чего буду корректировать, главная-то задача решена
Автор - Ralf
Дата добавления - 04.07.2013 в 17:14
AlexM Дата: Четверг, 04.07.2013, 21:52 | Сообщение № 10
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1129 ±
Замечаний: 0% ±

Excel 2003
Еще без массивный вариант
Код
=ВЫБОР(СУММ(ИНДЕКС(ЕОШ(ПОИСК({"аренд":"пломб"};B2))*{1:2};))^ЕОШ(ПОИСК("Услуги ЦЭЛ";D2));"прочие";"аренда";"ку")

или
Код
=ВЫБОР(СУММПРОИЗВ(ЕОШ(ПОИСК({"аренд":"пломб"};B2))*{1:2})^ЕОШ(ПОИСК("Услуги ЦЭЛ";D2));"прочие";"аренда";"ку")


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Сообщение отредактировал AlexM - Четверг, 04.07.2013, 21:54
 
Ответить
СообщениеЕще без массивный вариант
Код
=ВЫБОР(СУММ(ИНДЕКС(ЕОШ(ПОИСК({"аренд":"пломб"};B2))*{1:2};))^ЕОШ(ПОИСК("Услуги ЦЭЛ";D2));"прочие";"аренда";"ку")

или
Код
=ВЫБОР(СУММПРОИЗВ(ЕОШ(ПОИСК({"аренд":"пломб"};B2))*{1:2})^ЕОШ(ПОИСК("Услуги ЦЭЛ";D2));"прочие";"аренда";"ку")
Автор - AlexM
Дата добавления - 04.07.2013 в 21:52
Ralf Дата: Понедельник, 08.07.2013, 12:00 | Сообщение № 11
Группа: Пользователи
Ранг: Участник
Сообщений: 71
Репутация: 5 ±
Замечаний: 0% ±

AlexM, а разве фигурные скобки не указывают на массив?

Господа, формула ВЫБОР в некоторых случаях возвращает ошибку #ЗНАЧ, это если оба условия истинны и она не знает, что выбрать. По мере обкатки на реальных данных понадобилось добавить условия: B2 содержит "счетчик", (может одновременно содержать "пломб", а может и "пломб" одновременно быть с "прибор" в одной фразе. Счетчик с прибором одновременно пока ни разу не встречались, скорее всего такой вариант исключён.) И ещё на случай, когда оператор поленился ввести описание - приходится ориентироваться по сумме. Если сумма = 231 или сумма =527 - в услуги ставим "прочие".
Вот такая формула у меня получилась для Вида услуг:
Код
=ЕСЛИ(ИЛИ(G2=231;G2=527);"прочие";ЕСЛИ(ЕЧИСЛО(ПОИСК("ЦЭЛ";D2))*1=1;"прочие";ЕСЛИ(ИЛИ(ЕЧИСЛО(ПОИСК("пломб";B2))*1=1;ЕЧИСЛО(ПОИСК("прибор";B2))*1=1;ЕЧИСЛО(ПОИСК("счетчик";B2))*1=1;);"прочие";ЕСЛИ(ЕЧИСЛО(ПОИСК("аренд";B2))*1=1;"аренда";"ку"))))


Как бы её покороче записать через "Выбор()" или более наглядно, чтоб самому не запутаться во вложенных ЕСЛИ


Сообщение отредактировал Ralf - Понедельник, 08.07.2013, 12:02
 
Ответить
СообщениеAlexM, а разве фигурные скобки не указывают на массив?

Господа, формула ВЫБОР в некоторых случаях возвращает ошибку #ЗНАЧ, это если оба условия истинны и она не знает, что выбрать. По мере обкатки на реальных данных понадобилось добавить условия: B2 содержит "счетчик", (может одновременно содержать "пломб", а может и "пломб" одновременно быть с "прибор" в одной фразе. Счетчик с прибором одновременно пока ни разу не встречались, скорее всего такой вариант исключён.) И ещё на случай, когда оператор поленился ввести описание - приходится ориентироваться по сумме. Если сумма = 231 или сумма =527 - в услуги ставим "прочие".
Вот такая формула у меня получилась для Вида услуг:
Код
=ЕСЛИ(ИЛИ(G2=231;G2=527);"прочие";ЕСЛИ(ЕЧИСЛО(ПОИСК("ЦЭЛ";D2))*1=1;"прочие";ЕСЛИ(ИЛИ(ЕЧИСЛО(ПОИСК("пломб";B2))*1=1;ЕЧИСЛО(ПОИСК("прибор";B2))*1=1;ЕЧИСЛО(ПОИСК("счетчик";B2))*1=1;);"прочие";ЕСЛИ(ЕЧИСЛО(ПОИСК("аренд";B2))*1=1;"аренда";"ку"))))


Как бы её покороче записать через "Выбор()" или более наглядно, чтоб самому не запутаться во вложенных ЕСЛИ
Автор - Ralf
Дата добавления - 08.07.2013 в 12:00
AndreTM Дата: Понедельник, 08.07.2013, 12:12 | Сообщение № 12
Группа: Друзья
Ранг: Старожил
Сообщений: 1762
Репутация: 501 ±
Замечаний: 0% ±

2003 & 2010
Цитата (Ralf)
формула ВЫБОР в некоторых случаях возвращает ошибку #ЗНАЧ, это если оба условия истинны и она не знает, что выбрать
Это означает, что вы не разобрались, как работает ВЫБОР(). И у вас формула "условия" для него возвращает в какой-то момент значение либо <=0, либо больше количества вариантов выбора...

Skype: andre.tm.007
Donate: Qiwi: 9517375010
 
Ответить
Сообщение
Цитата (Ralf)
формула ВЫБОР в некоторых случаях возвращает ошибку #ЗНАЧ, это если оба условия истинны и она не знает, что выбрать
Это означает, что вы не разобрались, как работает ВЫБОР(). И у вас формула "условия" для него возвращает в какой-то момент значение либо <=0, либо больше количества вариантов выбора...
Автор - AndreTM
Дата добавления - 08.07.2013 в 12:12
DV Дата: Понедельник, 08.07.2013, 12:41 | Сообщение № 13
Группа: Друзья
Ранг: Обитатель
Сообщений: 286
Репутация: 194 ±
Замечаний: 0% ±

Excel 2010 RUS
Без ВЫБОРа:
Код
=ЕСЛИ(ИЛИ(G4=231;G4=527;СЧЁТ(ПОИСК({"пломб";"прибор";"счетчик"};B4);ПОИСК("ЦЭЛ";D4)));"прочие";ЕСЛИ(СЧЁТ(ПОИСК("аренд";B4));"аренда";"ку"))

Это не формула массива.
Чуть короче:
Код
=ЕСЛИ(ИЛИ(G2={231:527};СЧЁТ(ПОИСК({"пломб";"прибор";"счетчик"};B2);ПОИСК("ЦЭЛ";D2)));"прочие";ЕСЛИ(СЧЁТ(ПОИСК("аренд";B2));"аренда";"ку"))


Сообщение отредактировал DV - Понедельник, 08.07.2013, 14:09
 
Ответить
СообщениеБез ВЫБОРа:
Код
=ЕСЛИ(ИЛИ(G4=231;G4=527;СЧЁТ(ПОИСК({"пломб";"прибор";"счетчик"};B4);ПОИСК("ЦЭЛ";D4)));"прочие";ЕСЛИ(СЧЁТ(ПОИСК("аренд";B4));"аренда";"ку"))

Это не формула массива.
Чуть короче:
Код
=ЕСЛИ(ИЛИ(G2={231:527};СЧЁТ(ПОИСК({"пломб";"прибор";"счетчик"};B2);ПОИСК("ЦЭЛ";D2)));"прочие";ЕСЛИ(СЧЁТ(ПОИСК("аренд";B2));"аренда";"ку"))
Автор - DV
Дата добавления - 08.07.2013 в 12:41
Ralf Дата: Понедельник, 08.07.2013, 14:14 | Сообщение № 14
Группа: Пользователи
Ранг: Участник
Сообщений: 71
Репутация: 5 ±
Замечаний: 0% ±

DV, wacko у меня прям разрыв шаблона случился когда въехал

Немного модифицировал формулу, чтобы понизить приоритет условию G2={231:527}, подумал тут, что возможна ситуация. когда суммы 231 или 527 руб будут перечислены за аренду (маловероятно, но всё же)

Код
=ЕСЛИ(ИЛИ(СЧЁТ(ПОИСК({"пломб";"прибор";"счетчик"};B3);ПОИСК("ЦЭЛ";D3)));"прочие";ЕСЛИ(СЧЁТ(ПОИСК("аренд";B3));"аренда";ЕСЛИ(G3={231:527};"прочие";"ку")))
 
Ответить
СообщениеDV, wacko у меня прям разрыв шаблона случился когда въехал

Немного модифицировал формулу, чтобы понизить приоритет условию G2={231:527}, подумал тут, что возможна ситуация. когда суммы 231 или 527 руб будут перечислены за аренду (маловероятно, но всё же)

Код
=ЕСЛИ(ИЛИ(СЧЁТ(ПОИСК({"пломб";"прибор";"счетчик"};B3);ПОИСК("ЦЭЛ";D3)));"прочие";ЕСЛИ(СЧЁТ(ПОИСК("аренд";B3));"аренда";ЕСЛИ(G3={231:527};"прочие";"ку")))
Автор - Ralf
Дата добавления - 08.07.2013 в 14:14
DV Дата: Понедельник, 08.07.2013, 14:32 | Сообщение № 15
Группа: Друзья
Ранг: Обитатель
Сообщений: 286
Репутация: 194 ±
Замечаний: 0% ±

Excel 2010 RUS
Вот эту часть
Код
G3={231:527}

замените на
Код
ИЛИ(G3={231:527})

иначе при 527 не сработает
И ИЛИ в первом случае тогда не нужно:
Код
=ЕСЛИ(СЧЁТ(ПОИСК({"пломб";"прибор";"счетчик"};B2);ПОИСК("ЦЭЛ";D2));"прочие";ЕСЛИ(СЧЁТ(ПОИСК("аренд";B2));"аренда";ЕСЛИ(ИЛИ(G2={231:527});"прочие";"ку")))


Сообщение отредактировал DV - Понедельник, 08.07.2013, 14:40
 
Ответить
СообщениеВот эту часть
Код
G3={231:527}

замените на
Код
ИЛИ(G3={231:527})

иначе при 527 не сработает
И ИЛИ в первом случае тогда не нужно:
Код
=ЕСЛИ(СЧЁТ(ПОИСК({"пломб";"прибор";"счетчик"};B2);ПОИСК("ЦЭЛ";D2));"прочие";ЕСЛИ(СЧЁТ(ПОИСК("аренд";B2));"аренда";ЕСЛИ(ИЛИ(G2={231:527});"прочие";"ку")))
Автор - DV
Дата добавления - 08.07.2013 в 14:32
Ralf Дата: Понедельник, 08.07.2013, 15:28 | Сообщение № 16
Группа: Пользователи
Ранг: Участник
Сообщений: 71
Репутация: 5 ±
Замечаний: 0% ±

ok
 
Ответить
Сообщениеok
Автор - Ralf
Дата добавления - 08.07.2013 в 15:28
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Проверка наличия заданного фрагмента текста в ячейке. (Формулы)
  • Страница 1 из 1
  • 1
Поиск:

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