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

Вход

Регистрация

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

 

= Мир MS Excel/Как найти элемент из списка в нескольких ячейках? - Мир MS Excel

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

Excel 2013
Доброго всем дня!

ДАНО:
В одной из двух ячеек одной строки есть информация об юр.форме организации (ИП, ООО, ЗАО и прочее).

НЕ МОГУ РАЗОБРАТЬСЯ КАК:
а) найти в какой
б) если в первой, то поменять местами ячейки
в) в третью колонку выставить тип организации без названия (ИП без Иванов К.К.)

Файл-пример во вложении.

Сложности:
- Форм может быть до 15-20 штук (ИП, ООО, ЗАО, НП, КГУП, ГУП, УП, АО ...)
- поиск должен должен или учитывать регист или должен искать слова целиком (чтобы не срабатывал ИП на "экипировка")

Можно ли это сделать формулами и как? Или проще макросом?
К сообщению приложен файл: peremeshannie1.xlsx (9.4 Kb)
 
Ответить
СообщениеДоброго всем дня!

ДАНО:
В одной из двух ячеек одной строки есть информация об юр.форме организации (ИП, ООО, ЗАО и прочее).

НЕ МОГУ РАЗОБРАТЬСЯ КАК:
а) найти в какой
б) если в первой, то поменять местами ячейки
в) в третью колонку выставить тип организации без названия (ИП без Иванов К.К.)

Файл-пример во вложении.

Сложности:
- Форм может быть до 15-20 штук (ИП, ООО, ЗАО, НП, КГУП, ГУП, УП, АО ...)
- поиск должен должен или учитывать регист или должен искать слова целиком (чтобы не срабатывал ИП на "экипировка")

Можно ли это сделать формулами и как? Или проще макросом?
Автор - Mikez
Дата добавления - 17.05.2016 в 08:25
pabchek Дата: Вторник, 17.05.2016, 09:16 | Сообщение № 2
Группа: Проверенные
Ранг: Ветеран
Сообщений: 931
Репутация: 218 ±
Замечаний: 0% ±

Excel 2007
Здравствуйте!
Вот, пока корявенько, но работает. С созданием списка юрформ.
Код
=ЕСЛИОШИБКА(ИНДЕКС(юрформа;ПРОСМОТР(;-1/(ПОИСК(" "&юрформа&" ";" "&A2&" "));СТРОКА(юрформа)));ИНДЕКС(юрформа;ПРОСМОТР(;-1/(ПОИСК(" "&юрформа&" ";" "&B2&" "));СТРОКА(юрформа))))

Уже сократил )))
Код
=ИНДЕКС(юрформа;ПРОСМОТР(;-1/(ПОИСК(" "&юрформа&" ";" "&A2&B2&" "));СТРОКА(юрформа)))
(файл перезалил)
Список автопополняемый - просто пишите название формы внизу списка
Кошмар. Не проснулся с утра. Еще проще
Код
=ПРОСМОТР(;-1/(ПОИСК(" "&юрформа&" ";" "&A2&B2&" "));юрформа)
К сообщению приложен файл: 8364321.xlsx (10.3 Kb)

"Учиться, учиться и еще раз учиться!"
WM: R399923528092

Сообщение отредактировал pabchek - Вторник, 17.05.2016, 09:25
 
Ответить
СообщениеЗдравствуйте!
Вот, пока корявенько, но работает. С созданием списка юрформ.
Код
=ЕСЛИОШИБКА(ИНДЕКС(юрформа;ПРОСМОТР(;-1/(ПОИСК(" "&юрформа&" ";" "&A2&" "));СТРОКА(юрформа)));ИНДЕКС(юрформа;ПРОСМОТР(;-1/(ПОИСК(" "&юрформа&" ";" "&B2&" "));СТРОКА(юрформа))))

Уже сократил )))
Код
=ИНДЕКС(юрформа;ПРОСМОТР(;-1/(ПОИСК(" "&юрформа&" ";" "&A2&B2&" "));СТРОКА(юрформа)))
(файл перезалил)
Список автопополняемый - просто пишите название формы внизу списка
Кошмар. Не проснулся с утра. Еще проще
Код
=ПРОСМОТР(;-1/(ПОИСК(" "&юрформа&" ";" "&A2&B2&" "));юрформа)
Автор - pabchek
Дата добавления - 17.05.2016 в 09:16
_Boroda_ Дата: Вторник, 17.05.2016, 09:31 | Сообщение № 3
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16674
Репутация: 6479 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Цитата Mikez, 17.05.2016 в 08:25, в сообщении № 1
или учитывать регист или должен искать слова целиком

НАЙТИ ищет по регистру, а приклеивание пробелов вначале и в конце ООО даст слово целиком
Использован именованный диапазон (посмотреть можно, нажав Контрл F3) "Тип", формула для него
Код
=" "&ИНДЕКС(Лист1!$G:$G;2):ИНДЕКС(Лист1!$G:$G;СЧЁТЗ(Лист1!$G:$G))&" "
можно добавлять и удалять данные
Для столбцов С и Д формула почти одинаковая (формула массива, вводится одновременным нажатием Контрл Шифт Ентер)
Код
=ЕСЛИ(СЧЁТ(НАЙТИ(Тип;" "&A2&" "));B2;A2)&""

Для столбца Е обычная формула
Код
=ЕСЛИОШИБКА(СЖПРОБЕЛЫ(ПРОСМОТР(;-НАЙТИ(Тип;" "&D2&" ");Тип));"")


pabchek, Виктор, ПОИСК и так даст ошибку при отсутствии искомого, поэтому конструкция 1/ПОИСК не нужна, она ведь делается для генерации ошибки при нуле, а там нулей нет, там и так ошибка.
К сообщению приложен файл: peremeshannie1_.xlsx (13.1 Kb)

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

НАЙТИ ищет по регистру, а приклеивание пробелов вначале и в конце ООО даст слово целиком
Использован именованный диапазон (посмотреть можно, нажав Контрл F3) "Тип", формула для него
Код
=" "&ИНДЕКС(Лист1!$G:$G;2):ИНДЕКС(Лист1!$G:$G;СЧЁТЗ(Лист1!$G:$G))&" "
можно добавлять и удалять данные
Для столбцов С и Д формула почти одинаковая (формула массива, вводится одновременным нажатием Контрл Шифт Ентер)
Код
=ЕСЛИ(СЧЁТ(НАЙТИ(Тип;" "&A2&" "));B2;A2)&""

Для столбца Е обычная формула
Код
=ЕСЛИОШИБКА(СЖПРОБЕЛЫ(ПРОСМОТР(;-НАЙТИ(Тип;" "&D2&" ");Тип));"")


pabchek, Виктор, ПОИСК и так даст ошибку при отсутствии искомого, поэтому конструкция 1/ПОИСК не нужна, она ведь делается для генерации ошибки при нуле, а там нулей нет, там и так ошибка.
Автор - _Boroda_
Дата добавления - 17.05.2016 в 09:31
pabchek Дата: Вторник, 17.05.2016, 09:56 | Сообщение № 4
Группа: Проверенные
Ранг: Ветеран
Сообщений: 931
Репутация: 218 ±
Замечаний: 0% ±

Excel 2007
Цитата _Boroda_, 17.05.2016 в 09:31, в сообщении № 3
ПОИСК и так даст ошибку
Александр. Так то оно так, но если я пишу
Код
=ПРОСМОТР(;ПОИСК(" "&юрформа&" ";" "&A2&B2&" ");юрформа)
то выдается ошибка

И, откровенно говоря, я не понял зачем у тебя столько допстолбцов?
К сообщению приложен файл: 0483968.xlsx (10.2 Kb)

"Учиться, учиться и еще раз учиться!"
WM: R399923528092

Сообщение отредактировал pabchek - Вторник, 17.05.2016, 10:00
 
Ответить
Сообщение
Цитата _Boroda_, 17.05.2016 в 09:31, в сообщении № 3
ПОИСК и так даст ошибку
Александр. Так то оно так, но если я пишу
Код
=ПРОСМОТР(;ПОИСК(" "&юрформа&" ";" "&A2&B2&" ");юрформа)
то выдается ошибка

И, откровенно говоря, я не понял зачем у тебя столько допстолбцов?
Автор - pabchek
Дата добавления - 17.05.2016 в 09:56
pabchek Дата: Вторник, 17.05.2016, 10:15 | Сообщение № 5
Группа: Проверенные
Ранг: Ветеран
Сообщений: 931
Репутация: 218 ±
Замечаний: 0% ±

Excel 2007
Цитата pabchek, 17.05.2016 в 09:56, в сообщении № 4
то выдается ошибка

А, ну конечно, если я правильно понимаю, ПРОСМОТР ищет в списке по возрастанию. И если мы опускаем первый аргумент, то подразумевается Ноль. Соответственно искомое должно быть меньше ноля поэтому и"-1/"

"Учиться, учиться и еще раз учиться!"
WM: R399923528092
 
Ответить
Сообщение
Цитата pabchek, 17.05.2016 в 09:56, в сообщении № 4
то выдается ошибка

А, ну конечно, если я правильно понимаю, ПРОСМОТР ищет в списке по возрастанию. И если мы опускаем первый аргумент, то подразумевается Ноль. Соответственно искомое должно быть меньше ноля поэтому и"-1/"
Автор - pabchek
Дата добавления - 17.05.2016 в 10:15
_Boroda_ Дата: Вторник, 17.05.2016, 10:17 | Сообщение № 6
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16674
Репутация: 6479 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Цитата pabchek, 17.05.2016 в 09:56, в сообщении № 4
зачем у тебя столько допстолбцов?

Это не допстолбцы, это пункт "б" в первом вопросе - перемещение названий во второй столбец, а "что это" в первый
Цитата pabchek, 17.05.2016 в 09:56, в сообщении № 4
но если я пишу
А ты пиши
=ПРОСМОТР(;-ПОИСК...
Нам ведь нужны вторым аргументом ПРОСМОТРа ошибки и значения, МЕНЬШИЕ первого, а первый мы не указываем, следовательно, он равен нулю. ПОИСК даст положительные числа, а минусом перед ним мы переводим их в отрицательные

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

Это не допстолбцы, это пункт "б" в первом вопросе - перемещение названий во второй столбец, а "что это" в первый
Цитата pabchek, 17.05.2016 в 09:56, в сообщении № 4
но если я пишу
А ты пиши
=ПРОСМОТР(;-ПОИСК...
Нам ведь нужны вторым аргументом ПРОСМОТРа ошибки и значения, МЕНЬШИЕ первого, а первый мы не указываем, следовательно, он равен нулю. ПОИСК даст положительные числа, а минусом перед ним мы переводим их в отрицательные
Автор - _Boroda_
Дата добавления - 17.05.2016 в 10:17
pabchek Дата: Вторник, 17.05.2016, 11:07 | Сообщение № 7
Группа: Проверенные
Ранг: Ветеран
Сообщений: 931
Репутация: 218 ±
Замечаний: 0% ±

Excel 2007
Цитата _Boroda_, 17.05.2016 в 10:17, в сообщении № 6
=ПРОСМОТР(;-ПОИСК...
Ну вобщем да, на 2 символа короче. "1/" уже как-то на автомате ставиться
Цитата pabchek, 17.05.2016 в 11:07, в сообщении № 7
это пункт "б"
нда... это моя вечная невнимательность((

"Учиться, учиться и еще раз учиться!"
WM: R399923528092

Сообщение отредактировал pabchek - Вторник, 17.05.2016, 11:08
 
Ответить
Сообщение
Цитата _Boroda_, 17.05.2016 в 10:17, в сообщении № 6
=ПРОСМОТР(;-ПОИСК...
Ну вобщем да, на 2 символа короче. "1/" уже как-то на автомате ставиться
Цитата pabchek, 17.05.2016 в 11:07, в сообщении № 7
это пункт "б"
нда... это моя вечная невнимательность((
Автор - pabchek
Дата добавления - 17.05.2016 в 11:07
Mikez Дата: Вторник, 17.05.2016, 11:24 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Вы какие-то небожители )
Благодарю!
 
Ответить
СообщениеВы какие-то небожители )
Благодарю!
Автор - Mikez
Дата добавления - 17.05.2016 в 11:24
_Boroda_ Дата: Вторник, 17.05.2016, 11:25 | Сообщение № 9
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16674
Репутация: 6479 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Цитата pabchek, 17.05.2016 в 11:07, в сообщении № 7
на 2 символа короче

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

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

Не в этом дело. Деление значительно более тяжелая операция, чем смена знака. Для больших массивов в тяжелом файле это может быть очень существенно.
Автор - _Boroda_
Дата добавления - 17.05.2016 в 11:25
pabchek Дата: Вторник, 17.05.2016, 11:31 | Сообщение № 10
Группа: Проверенные
Ранг: Ветеран
Сообщений: 931
Репутация: 218 ±
Замечаний: 0% ±

Excel 2007
[offtop]
Цитата _Boroda_, 17.05.2016 в 11:25, в сообщении № 9
Деление значительно более тяжелая операция

Да, конечно! Урок усвоен :)[/offtop]

"Учиться, учиться и еще раз учиться!"
WM: R399923528092
 
Ответить
Сообщение[offtop]
Цитата _Boroda_, 17.05.2016 в 11:25, в сообщении № 9
Деление значительно более тяжелая операция

Да, конечно! Урок усвоен :)[/offtop]
Автор - pabchek
Дата добавления - 17.05.2016 в 11:31
Mikez Дата: Четверг, 26.05.2016, 05:57 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Формулы не справились на большом объёме. Похоже без макроса не обойтись: http://www.excelworld.ru/forum/10-23880-1
 
Ответить
СообщениеФормулы не справились на большом объёме. Похоже без макроса не обойтись: http://www.excelworld.ru/forum/10-23880-1
Автор - Mikez
Дата добавления - 26.05.2016 в 05:57
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Как найти элемент из списка в нескольких ячейках? (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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