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

Вход

Регистрация

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

 

= Мир MS Excel/Вставить значение в зависимости от найденого текста в ячейки - Мир MS Excel

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

Excel 2016
Подскажите как составить формулу. Нужно проверять наличие текста в ячейках столбца С и в зависимости от этого вставлять значения в ячейки столбца E. Другими словами из текста нужно выцеплять вес. Формулу ниже не позволяет ввести так как много условий вложенности. А с массивом чет не разобрался, может есть вариант попроще?
Код
=ЕСЛИ(ЕЧИСЛО(НАЙТИ("50 г";$C13));0,05;ЕСЛИ(ЕЧИСЛО(НАЙТИ("62,5 г";$C13));0,0625;ЕСЛИ(ЕЧИСЛО(НАЙТИ("140 г";$C13));0,14;ЕСЛИ(ЕЧИСЛО(НАЙТИ("160 г";$C13));0,16;ЕСЛИ(ЕЧИСЛО(НАЙТИ("280 г";$C13));0,28;ЕСЛИ(ЕЧИСЛО(НАЙТИ("200 г";$C13));0,2;ЕСЛИ(ЕЧИСЛО(НАЙТИ("150 г";$C13));0,15;ЕСЛИ(ЕЧИСЛО(НАЙТИ("500 г";$C13));0,5;"ОШИБКА"))))))))
К сообщению приложен файл: file.xls (35.0 Kb)


Сообщение отредактировал atrom - Воскресенье, 11.08.2019, 17:32
 
Ответить
СообщениеПодскажите как составить формулу. Нужно проверять наличие текста в ячейках столбца С и в зависимости от этого вставлять значения в ячейки столбца E. Другими словами из текста нужно выцеплять вес. Формулу ниже не позволяет ввести так как много условий вложенности. А с массивом чет не разобрался, может есть вариант попроще?
Код
=ЕСЛИ(ЕЧИСЛО(НАЙТИ("50 г";$C13));0,05;ЕСЛИ(ЕЧИСЛО(НАЙТИ("62,5 г";$C13));0,0625;ЕСЛИ(ЕЧИСЛО(НАЙТИ("140 г";$C13));0,14;ЕСЛИ(ЕЧИСЛО(НАЙТИ("160 г";$C13));0,16;ЕСЛИ(ЕЧИСЛО(НАЙТИ("280 г";$C13));0,28;ЕСЛИ(ЕЧИСЛО(НАЙТИ("200 г";$C13));0,2;ЕСЛИ(ЕЧИСЛО(НАЙТИ("150 г";$C13));0,15;ЕСЛИ(ЕЧИСЛО(НАЙТИ("500 г";$C13));0,5;"ОШИБКА"))))))))

Автор - atrom
Дата добавления - 11.08.2019 в 16:07
Pelena Дата: Воскресенье, 11.08.2019, 16:34 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 19158
Репутация: 4411 ±
Замечаний: ±

Excel 365 & Mac Excel
atrom, дайте теме название, отражающее суть задачи (например, Найти часть текста и вставить в другую ячейку), и приложите файл с примером в соответствии с Правилами форума


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщениеatrom, дайте теме название, отражающее суть задачи (например, Найти часть текста и вставить в другую ячейку), и приложите файл с примером в соответствии с Правилами форума

Автор - Pelena
Дата добавления - 11.08.2019 в 16:34
_Boroda_ Дата: Воскресенье, 11.08.2019, 18:37 | Сообщение № 3
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Так нужно?
Код
=ПОДСТАВИТЬ(СЖПРОБЕЛЫ(ЛЕВБ(ПРАВБ(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(ПОДСТАВИТЬ(C3;"г";));" ";ПОВТОР(" ";99));297);99));".";",")/1000


*Добавил /1000, файл перевложил
К сообщению приложен файл: file_2.xls (35.5 Kb)


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


Сообщение отредактировал _Boroda_ - Воскресенье, 11.08.2019, 21:19
 
Ответить
СообщениеТак нужно?
Код
=ПОДСТАВИТЬ(СЖПРОБЕЛЫ(ЛЕВБ(ПРАВБ(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(ПОДСТАВИТЬ(C3;"г";));" ";ПОВТОР(" ";99));297);99));".";",")/1000


*Добавил /1000, файл перевложил

Автор - _Boroda_
Дата добавления - 11.08.2019 в 18:37
Светлый Дата: Воскресенье, 11.08.2019, 19:35 | Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
Ещё вариант:
Код
=ПОДСТАВИТЬ(ПРАВБ(ПОДСТАВИТЬ(ЛЕВБ(C3;ПОИСК("г ";C3)-1);" ";"     ");8);".";ПСТР(1/2;2;1))/1000
*Более надёжно:
Код
=ПОДСТАВИТЬ(ПРАВБ(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(ЛЕВБ(C3;ПОИСК("г ";C3)-1));" ";"     ");7);".";ПСТР(1/2;2;1))/1000


Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Воскресенье, 11.08.2019, 19:41
 
Ответить
СообщениеЕщё вариант:
Код
=ПОДСТАВИТЬ(ПРАВБ(ПОДСТАВИТЬ(ЛЕВБ(C3;ПОИСК("г ";C3)-1);" ";"     ");8);".";ПСТР(1/2;2;1))/1000
*Более надёжно:
Код
=ПОДСТАВИТЬ(ПРАВБ(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(ЛЕВБ(C3;ПОИСК("г ";C3)-1));" ";"     ");7);".";ПСТР(1/2;2;1))/1000

Автор - Светлый
Дата добавления - 11.08.2019 в 19:35
bmv98rus Дата: Понедельник, 12.08.2019, 07:45 | Сообщение № 5
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4098
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
Светлый, боюсь что надежнее не становится. При наличии "г" на конце слова, будет сбой.


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
СообщениеСветлый, боюсь что надежнее не становится. При наличии "г" на конце слова, будет сбой.

Автор - bmv98rus
Дата добавления - 12.08.2019 в 07:45
Светлый Дата: Понедельник, 12.08.2019, 08:15 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
При наличии "г" на конце слова, будет сбой
В примере таких значений нет. Не стал думать в этом направлении. Перед "г" нет пробела - такой вариант есть. Учёл.
Добавим пробел при поиске:
Код
=ПОДСТАВИТЬ(ПРАВБ(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(ЛЕВБ(C3;ПОИСК("г ";C3&" ")-1));" ";"     ");7);".";ПСТР(1/2;2;1))/1000


Программировать проще, чем писать стихи.
 
Ответить
Сообщение
При наличии "г" на конце слова, будет сбой
В примере таких значений нет. Не стал думать в этом направлении. Перед "г" нет пробела - такой вариант есть. Учёл.
Добавим пробел при поиске:
Код
=ПОДСТАВИТЬ(ПРАВБ(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(ЛЕВБ(C3;ПОИСК("г ";C3&" ")-1));" ";"     ");7);".";ПСТР(1/2;2;1))/1000

Автор - Светлый
Дата добавления - 12.08.2019 в 08:15
atrom Дата: Понедельник, 12.08.2019, 09:40 | Сообщение № 7
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Спасибо всем, я правда пока не понял что делает формула по шагам, но работает)) Несколько вопросов:
1. СЖПРОБЕЛЫ убирает пробелы, но потом вы добавляете поиск с пробелом так понимаю (C3;ПОИСК("г ";C3&" ") зачем?
2. На сколько строк это рассчитано? или без разницы?
 
Ответить
СообщениеСпасибо всем, я правда пока не понял что делает формула по шагам, но работает)) Несколько вопросов:
1. СЖПРОБЕЛЫ убирает пробелы, но потом вы добавляете поиск с пробелом так понимаю (C3;ПОИСК("г ";C3&" ") зачем?
2. На сколько строк это рассчитано? или без разницы?

Автор - atrom
Дата добавления - 12.08.2019 в 09:40
_Boroda_ Дата: Понедельник, 12.08.2019, 09:54 | Сообщение № 8
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
В двух словах
У Светлого формула ищет букву г с пробелом справа, берет всё с первого символа до найденного, сжимает пробелы, вместо одного пробела вставляет пять, берет из полученного правые 7 символов, меняет точку на системный разделитель и делит на 1000
У меня формула убирает букву г, сжимает пробелы, вместо пробела подставляет 99 пробелов, из полученного берет 297 символов справа, а потом 99 символов слева, сжимает пробелы и меняет точку на запятую и делит на 1000 (если короче, то убирает букву г и из полученного берет третье слово справа)
Если универсально с системными разделителями, то
Код
=ПОДСТАВИТЬ(СЖПРОБЕЛЫ(ЛЕВБ(ПРАВБ(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(ПОДСТАВИТЬ(C3;"г";));" ";ПОВТОР(" ";99));297);99));".";ПСТР(1/2;2;1))/1000

Недостатки
С: никакое слово, расположенное в предложении до размера, не должно оканчиваться на букву г
Я: размер должен быть указан третьим словом справа (не считая одиночной буквы г, тогда четвертым)
К сообщению приложен файл: 2715486875_1.xls (40.5 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеВ двух словах
У Светлого формула ищет букву г с пробелом справа, берет всё с первого символа до найденного, сжимает пробелы, вместо одного пробела вставляет пять, берет из полученного правые 7 символов, меняет точку на системный разделитель и делит на 1000
У меня формула убирает букву г, сжимает пробелы, вместо пробела подставляет 99 пробелов, из полученного берет 297 символов справа, а потом 99 символов слева, сжимает пробелы и меняет точку на запятую и делит на 1000 (если короче, то убирает букву г и из полученного берет третье слово справа)
Если универсально с системными разделителями, то
Код
=ПОДСТАВИТЬ(СЖПРОБЕЛЫ(ЛЕВБ(ПРАВБ(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(ПОДСТАВИТЬ(C3;"г";));" ";ПОВТОР(" ";99));297);99));".";ПСТР(1/2;2;1))/1000

Недостатки
С: никакое слово, расположенное в предложении до размера, не должно оканчиваться на букву г
Я: размер должен быть указан третьим словом справа (не считая одиночной буквы г, тогда четвертым)

Автор - _Boroda_
Дата добавления - 12.08.2019 в 09:54
bmv98rus Дата: Понедельник, 12.08.2019, 12:24 | Сообщение № 9
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4098
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
длинно , массивно, и с иными ограничениями :-)
Код
=MAX(IFERROR(--SUBSTITUTE(MID(SUBSTITUTE(SUBSTITUTE(C3;" г";"г");" ";REPT(" ";999));FIND(CHAR(1);SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C3;" г";"г");"г";CHAR(1);ROW($A$1:$A$99));" ";REPT(" ";999)))-15;15);".";MID(1/2;2;1));))/1000
К сообщению приложен файл: Copy_of_1081.xlsx (16.8 Kb)


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщениедлинно , массивно, и с иными ограничениями :-)
Код
=MAX(IFERROR(--SUBSTITUTE(MID(SUBSTITUTE(SUBSTITUTE(C3;" г";"г");" ";REPT(" ";999));FIND(CHAR(1);SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C3;" г";"г");"г";CHAR(1);ROW($A$1:$A$99));" ";REPT(" ";999)))-15;15);".";MID(1/2;2;1));))/1000

Автор - bmv98rus
Дата добавления - 12.08.2019 в 12:24
atrom Дата: Понедельник, 12.08.2019, 18:12 | Сообщение № 10
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
bmv98rus, у вас специально раскрашивается ячейка в которой что то пошло не так? а соседние столбцы H и i зачем?
 
Ответить
Сообщениеbmv98rus, у вас специально раскрашивается ячейка в которой что то пошло не так? а соседние столбцы H и i зачем?

Автор - atrom
Дата добавления - 12.08.2019 в 18:12
bmv98rus Дата: Понедельник, 12.08.2019, 19:21 | Сообщение № 11
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4098
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
atrom, это не у меня, это от предыдущего поста осталось, но показывает как отрабатывает ограничение того или иного варианта. Мой тоже не идеален
К сообщению приложен файл: 2055138.xlsx (11.7 Kb)


Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Понедельник, 12.08.2019, 19:25
 
Ответить
Сообщениеatrom, это не у меня, это от предыдущего поста осталось, но показывает как отрабатывает ограничение того или иного варианта. Мой тоже не идеален

Автор - bmv98rus
Дата добавления - 12.08.2019 в 19:21
atrom Дата: Среда, 14.08.2019, 09:37 | Сообщение № 12
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
А можно сделать поиск только буквы " г " с пробелами с двух сторон? тогда не будет зависеть от наличия слов с буквой г
 
Ответить
СообщениеА можно сделать поиск только буквы " г " с пробелами с двух сторон? тогда не будет зависеть от наличия слов с буквой г

Автор - atrom
Дата добавления - 14.08.2019 в 09:37
_Boroda_ Дата: Среда, 14.08.2019, 10:14 | Сообщение № 13
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Можно. Но работать будет не совсем верно. См. строку 10 примера - "280г"
Нужен как можно больший список реально существующих разнотипных вариантов, тогда мы сможем что-нибудь близкое к совсем правильному варианту придумать


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеМожно. Но работать будет не совсем верно. См. строку 10 примера - "280г"
Нужен как можно больший список реально существующих разнотипных вариантов, тогда мы сможем что-нибудь близкое к совсем правильному варианту придумать

Автор - _Boroda_
Дата добавления - 14.08.2019 в 10:14
atrom Дата: Среда, 14.08.2019, 12:42 | Сообщение № 14
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
что то не заметил, возможно в качестве примера я просто первые несколько строчек скопировал с одним весом, а потом вручную менял под разные веса и опечатался. скорее всего " г " везде с пробелами, спасибо что обратили внимание.
 
Ответить
Сообщениечто то не заметил, возможно в качестве примера я просто первые несколько строчек скопировал с одним весом, а потом вручную менял под разные веса и опечатался. скорее всего " г " везде с пробелами, спасибо что обратили внимание.

Автор - atrom
Дата добавления - 14.08.2019 в 12:42
_Boroda_ Дата: Среда, 14.08.2019, 16:42 | Сообщение № 15
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
До килограмма. Вернее, 5 знаков, включая точку
Код
=ПОДСТАВИТЬ(ПСТР(C3;ПОИСК(" г ";C3)-5;5);".";ПСТР(1/2;2;1))%/10

Кстати, судя по формуле из первого поста, у Вас текст выгружается не 62.5, а 62,5?
Тогда возможно сработает и
Код
=ПСТР(C3;ПОИСК(" г ";C3)-5;5)%/10
К сообщению приложен файл: 2055138_11.xlsx (12.2 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеДо килограмма. Вернее, 5 знаков, включая точку
Код
=ПОДСТАВИТЬ(ПСТР(C3;ПОИСК(" г ";C3)-5;5);".";ПСТР(1/2;2;1))%/10

Кстати, судя по формуле из первого поста, у Вас текст выгружается не 62.5, а 62,5?
Тогда возможно сработает и
Код
=ПСТР(C3;ПОИСК(" г ";C3)-5;5)%/10

Автор - _Boroda_
Дата добавления - 14.08.2019 в 16:42
atrom Дата: Пятница, 16.08.2019, 15:12 | Сообщение № 16
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
задачка усложнилась малость)
1. 62.5 г а не 62,5 г
2. " г "граммы с пробелами с обоих сторон
3. после наименования может быть слово типа распродажа
4. в наименовании может быть два веса, нужно брать первый слева
сюда подошла формула Светлый, разве что я бы четко указал пробелы слева и справа, типа так
Код
=ПОДСТАВИТЬ(ПРАВБ(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(ЛЕВБ(C13;ПОИСК(" г ";C13)-1));" ";"     ");7);".";ПСТР(1/2;2;1))/1000

или дополнительно отсекал символы +-, правда не везде нужно
К сообщению приложен файл: file2.xls (85.5 Kb)
 
Ответить
Сообщениезадачка усложнилась малость)
1. 62.5 г а не 62,5 г
2. " г "граммы с пробелами с обоих сторон
3. после наименования может быть слово типа распродажа
4. в наименовании может быть два веса, нужно брать первый слева
сюда подошла формула Светлый, разве что я бы четко указал пробелы слева и справа, типа так
Код
=ПОДСТАВИТЬ(ПРАВБ(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(ЛЕВБ(C13;ПОИСК(" г ";C13)-1));" ";"     ");7);".";ПСТР(1/2;2;1))/1000

или дополнительно отсекал символы +-, правда не везде нужно

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

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