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

Вход

Регистрация

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

 

= Мир MS Excel/Поиск, вывод значений из столбца с учетом двух критериев - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Поиск, вывод значений из столбца с учетом двух критериев (Word)
Поиск, вывод значений из столбца с учетом двух критериев
vorona1986 Дата: Пятница, 07.11.2014, 19:37 | Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 56
Репутация: 0 ±
Замечаний: 40% ±

Excel 2010
Доброго времени суток, время не позволяет долго гуглить с решением проблемы. Прошу помощи параллельно на данном форуме. В работе таблица с данными, B3:B8 - фио людей, 5 столбцов с определенными продуктами (С и G) по которым вычислителем является число в %. Поставил себе задачу согласно двух критериев "тип продукта" и "% выполнения". которые через выпадающий список будет выбираться в ячейках J2 и J3 выводить список ф.и.о. которые попадают под критерии заданные в ячейках J2 и J3.

Начал путь от формулы
Код
=INDEX(B3:B8;MATCH($J$2&$J$3;C2:C8&D2:D8&E2:E8&F2:F8&G2:G8;0))


Но как понимаю проблема в связке амперсанда, а точнее в "" $J$2&$J$3;C2:C8&D2:D8&E2:E8&F2:F8&G2:G8;0"", и количестве аргументов. Подтолкине на правильный путь. Всем зелень
К сообщению приложен файл: 2760520.xlsx (9.7 Kb)


Сообщение отредактировал vorona1986 - Пятница, 07.11.2014, 19:39
 
Ответить
СообщениеДоброго времени суток, время не позволяет долго гуглить с решением проблемы. Прошу помощи параллельно на данном форуме. В работе таблица с данными, B3:B8 - фио людей, 5 столбцов с определенными продуктами (С и G) по которым вычислителем является число в %. Поставил себе задачу согласно двух критериев "тип продукта" и "% выполнения". которые через выпадающий список будет выбираться в ячейках J2 и J3 выводить список ф.и.о. которые попадают под критерии заданные в ячейках J2 и J3.

Начал путь от формулы
Код
=INDEX(B3:B8;MATCH($J$2&$J$3;C2:C8&D2:D8&E2:E8&F2:F8&G2:G8;0))


Но как понимаю проблема в связке амперсанда, а точнее в "" $J$2&$J$3;C2:C8&D2:D8&E2:E8&F2:F8&G2:G8;0"", и количестве аргументов. Подтолкине на правильный путь. Всем зелень
Автор - vorona1986
Дата добавления - 07.11.2014 в 19:37
krosav4ig Дата: Пятница, 07.11.2014, 21:06 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 2347
Репутация: 997 ±
Замечаний: 0% ±

Excel 2007,2010,2013
как-то так
upd.
заменил файл
К сообщению приложен файл: 3110107.xlsx (11.0 Kb)

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

Сообщение отредактировал krosav4ig - Пятница, 07.11.2014, 22:11
 
Ответить
Сообщениекак-то так
upd.
заменил файл
Автор - krosav4ig
Дата добавления - 07.11.2014 в 21:06
Nic70y Дата: Суббота, 08.11.2014, 10:04 | Сообщение № 3
Группа: Друзья
Ранг: Экселист
Сообщений: 9028
Репутация: 2374 ±
Замечаний: 0% ±

Excel 2010
еще вариант, тоже массивной:
Код
=ЕСЛИОШИБКА(ИНДЕКС(B$1:B$8;НАИМЕНЬШИЙ(ЕСЛИ(ЕЧИСЛО(1/((ИНДЕКС(C$3:G$8;;ПОИСКПОЗ(J$2;C$2:G$2;0))>=ЛЕВБ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(J$3;" и больше";);"-";"  ");3)%)*(ИНДЕКС(C$3:G$8;;ПОИСКПОЗ(J$2;C$2:G$2;0))<=--ПРАВБ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(J$3;" и больше";);"-";"  ");4))));СТРОКА(B$3:B$8);1000000);СТРОКА(J1)));"")


переделал (не учел 1 момент)
К сообщению приложен файл: 712.xlsx (11.0 Kb)

ЮMoney 41001841029809

Сообщение отредактировал Nic70y - Суббота, 08.11.2014, 10:10
 
Ответить
Сообщениееще вариант, тоже массивной:
Код
=ЕСЛИОШИБКА(ИНДЕКС(B$1:B$8;НАИМЕНЬШИЙ(ЕСЛИ(ЕЧИСЛО(1/((ИНДЕКС(C$3:G$8;;ПОИСКПОЗ(J$2;C$2:G$2;0))>=ЛЕВБ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(J$3;" и больше";);"-";"  ");3)%)*(ИНДЕКС(C$3:G$8;;ПОИСКПОЗ(J$2;C$2:G$2;0))<=--ПРАВБ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(J$3;" и больше";);"-";"  ");4))));СТРОКА(B$3:B$8);1000000);СТРОКА(J1)));"")


переделал (не учел 1 момент)
Автор - Nic70y
Дата добавления - 08.11.2014 в 10:04
vorona1986 Дата: Суббота, 08.11.2014, 19:46 | Сообщение № 4
Группа: Пользователи
Ранг: Участник
Сообщений: 56
Репутация: 0 ±
Замечаний: 40% ±

Excel 2010
Цитата Nic70y, 08.11.2014 в 10:04, в сообщении № 3
еще вариант, тоже массивной:

Спасибо буду пробовать, надеюсь что все получится. Нашел правда один момент, в таблице с исходными данными где допустим указано 110% выполнения, при выборе "100% больше" будут ли учитываться показатели , или так же данные в диапазоне 75% будут ли учитываться при выборе типа 60%-99% ?
 
Ответить
Сообщение
Цитата Nic70y, 08.11.2014 в 10:04, в сообщении № 3
еще вариант, тоже массивной:

Спасибо буду пробовать, надеюсь что все получится. Нашел правда один момент, в таблице с исходными данными где допустим указано 110% выполнения, при выборе "100% больше" будут ли учитываться показатели , или так же данные в диапазоне 75% будут ли учитываться при выборе типа 60%-99% ?
Автор - vorona1986
Дата добавления - 08.11.2014 в 19:46
vorona1986 Дата: Суббота, 08.11.2014, 19:47 | Сообщение № 5
Группа: Пользователи
Ранг: Участник
Сообщений: 56
Репутация: 0 ±
Замечаний: 40% ±

Excel 2010
Цитата krosav4ig, 07.11.2014 в 21:06, в сообщении № 2
как-то так
upd.
заменил файл


УДП насколько понимаю возможно реализовать только в экселе ?.... планирую в будущем полностью перенести проект в гугл таблицы.
Если есть подскажите источники или литературу что бы досконально освоить работу с формулами, в инете много но везде ересь в принципе
 
Ответить
Сообщение
Цитата krosav4ig, 07.11.2014 в 21:06, в сообщении № 2
как-то так
upd.
заменил файл


УДП насколько понимаю возможно реализовать только в экселе ?.... планирую в будущем полностью перенести проект в гугл таблицы.
Если есть подскажите источники или литературу что бы досконально освоить работу с формулами, в инете много но везде ересь в принципе
Автор - vorona1986
Дата добавления - 08.11.2014 в 19:47
ShAM Дата: Суббота, 08.11.2014, 20:16 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 1347
Репутация: 249 ±
Замечаний: 0% ±

Excel 2010
upd - сокр.от update - обновить. :)
То, что Вы предполагаете это udf (User Defined Functions).
Цитата vorona1986, 08.11.2014 в 19:47, в сообщении № 5
Если есть подскажите источники или литературу что бы досконально освоить работу с формулами, в инете много но везде ересь в принципе

На форуме есть библиотека. Но лучше тусить на форумах, изучая темы, пробуя помогать другим. ;)
 
Ответить
Сообщениеupd - сокр.от update - обновить. :)
То, что Вы предполагаете это udf (User Defined Functions).
Цитата vorona1986, 08.11.2014 в 19:47, в сообщении № 5
Если есть подскажите источники или литературу что бы досконально освоить работу с формулами, в инете много но везде ересь в принципе

На форуме есть библиотека. Но лучше тусить на форумах, изучая темы, пробуя помогать другим. ;)
Автор - ShAM
Дата добавления - 08.11.2014 в 20:16
krosav4ig Дата: Воскресенье, 09.11.2014, 01:58 | Сообщение № 7
Группа: Друзья
Ранг: Старожил
Сообщений: 2347
Репутация: 997 ±
Замечаний: 0% ±

Excel 2007,2010,2013
немного сократил свою формулу
Код
=ЕСЛИОШИБКА(ИНДЕКС(B:B;НАИМЕНЬШИЙ(ЕСЛИ((C$2:G$2=J$2)*ЕЧИСЛО(C$3:G$8)*(C$3:G$8<=ВЫБОР(СУММ(ЕЧИСЛО(ПОИСК({"-";" "};J$3))*{1;2};1);J$3;--ЗАМЕНИТЬ(J$3;1;ПОИСК("-";J$3););9^9))*(C$3:G$8>=ВЫБОР(СУММ(ЕЧИСЛО(ПОИСК({"-";" "};J$3))*{1;2};1);J$3;ЛЕВБ(J$3;ПОИСК("-";J$3)-1)%;ЛЕВБ(J$3;ПОИСК(" ";J$3)-1)%));СТРОКА(B$3:B$8);"");СТРОКА(O1)));"")
К сообщению приложен файл: 31101071.xlsx (12.0 Kb)

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

Сообщение отредактировал krosav4ig - Воскресенье, 09.11.2014, 01:59
 
Ответить
Сообщениенемного сократил свою формулу
Код
=ЕСЛИОШИБКА(ИНДЕКС(B:B;НАИМЕНЬШИЙ(ЕСЛИ((C$2:G$2=J$2)*ЕЧИСЛО(C$3:G$8)*(C$3:G$8<=ВЫБОР(СУММ(ЕЧИСЛО(ПОИСК({"-";" "};J$3))*{1;2};1);J$3;--ЗАМЕНИТЬ(J$3;1;ПОИСК("-";J$3););9^9))*(C$3:G$8>=ВЫБОР(СУММ(ЕЧИСЛО(ПОИСК({"-";" "};J$3))*{1;2};1);J$3;ЛЕВБ(J$3;ПОИСК("-";J$3)-1)%;ЛЕВБ(J$3;ПОИСК(" ";J$3)-1)%));СТРОКА(B$3:B$8);"");СТРОКА(O1)));"")
Автор - krosav4ig
Дата добавления - 09.11.2014 в 01:58
vorona1986 Дата: Понедельник, 10.11.2014, 17:35 | Сообщение № 8
Группа: Пользователи
Ранг: Участник
Сообщений: 56
Репутация: 0 ±
Замечаний: 40% ±

Excel 2010
Эксель - полет нормальный. Пробовал перевести в ОО и ггулдок (проблематика - ошибка синтаксиса) :(
 
Ответить
СообщениеЭксель - полет нормальный. Пробовал перевести в ОО и ггулдок (проблематика - ошибка синтаксиса) :(
Автор - vorona1986
Дата добавления - 10.11.2014 в 17:35
krosav4ig Дата: Вторник, 11.11.2014, 01:49 | Сообщение № 9
Группа: Друзья
Ранг: Старожил
Сообщений: 2347
Репутация: 997 ±
Замечаний: 0% ±

Excel 2007,2010,2013
для гуглдок формула
Цитата
=ArrayFormula(IFERROR(INDEX(B$1:B$65536;SMALL(IF((C$2:G$2=J$2)*ISNUMBER(C$3:G$8)*(C$3:G$8<=CHOOSE(SUM(ISNUMBER(SEARCH({"-"\" "};J$3))*{1\2};1);J$3;--REPLACE(J$3;1;SEARCH("-";J$3););9^9))*(C$3:G$8>=CHOOSE(SUM(ISNUMBER(SEARCH({"-"\" "};J$3))*{1\2};1);J$3;LEFT(J$3;SEARCH("-";J$3)-1)%;LEFT(J$3;SEARCH(" ";J$3)-1)%));ROW(B$3:B$8););ROW(O1)));))

для OO Calc (у мну 4.1.1, в 3 введенные формулы работают, но новые ввести не получится)
Цитата
=T(INDEX(B$1:B$65536;SMALL(IF((C$2:G$2=J$2)*ISNUMBER(C$3:G$8)*(C$3:G$8<=CHOOSE(SUM(ISNUMBER(SEARCH({"-";" "};J$3))*{1;2};1);J$3;SUBSTITUTE(REPLACE(J$3;1;SEARCH("-";J$3);"");"%";"")%;9^9))*(C$3:G$8>=CHOOSE(SUM(ISNUMBER(SEARCH({"-";" "};J$3))*{1;2};1);J$3;LEFT(J$3;SEARCH("-";J$3)-1)%;LEFT(J$3;SEARCH(" ";J$3)-1)%));ROW(B$3:B$8);4^8);ROW(O1))))
К сообщению приложен файл: 31101071.ods (15.5 Kb)

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

Сообщение отредактировал krosav4ig - Вторник, 11.11.2014, 12:35
 
Ответить
Сообщениедля гуглдок формула
Цитата
=ArrayFormula(IFERROR(INDEX(B$1:B$65536;SMALL(IF((C$2:G$2=J$2)*ISNUMBER(C$3:G$8)*(C$3:G$8<=CHOOSE(SUM(ISNUMBER(SEARCH({"-"\" "};J$3))*{1\2};1);J$3;--REPLACE(J$3;1;SEARCH("-";J$3););9^9))*(C$3:G$8>=CHOOSE(SUM(ISNUMBER(SEARCH({"-"\" "};J$3))*{1\2};1);J$3;LEFT(J$3;SEARCH("-";J$3)-1)%;LEFT(J$3;SEARCH(" ";J$3)-1)%));ROW(B$3:B$8););ROW(O1)));))

для OO Calc (у мну 4.1.1, в 3 введенные формулы работают, но новые ввести не получится)
Цитата
=T(INDEX(B$1:B$65536;SMALL(IF((C$2:G$2=J$2)*ISNUMBER(C$3:G$8)*(C$3:G$8<=CHOOSE(SUM(ISNUMBER(SEARCH({"-";" "};J$3))*{1;2};1);J$3;SUBSTITUTE(REPLACE(J$3;1;SEARCH("-";J$3);"");"%";"")%;9^9))*(C$3:G$8>=CHOOSE(SUM(ISNUMBER(SEARCH({"-";" "};J$3))*{1;2};1);J$3;LEFT(J$3;SEARCH("-";J$3)-1)%;LEFT(J$3;SEARCH(" ";J$3)-1)%));ROW(B$3:B$8);4^8);ROW(O1))))
Автор - krosav4ig
Дата добавления - 11.11.2014 в 01:49
Эль8731 Дата: Суббота, 13.07.2024, 00:58 | Сообщение № 10
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

Добрый день!
Простите, если не в ту тему (мало знаю вообще Эксель)(((

Есть данные из интернет отчета компании о проданных товарах с номерами заказов и данные из нашей базы/сайта. Получаем их через скачивание файла (сторонней компании) и копировании в интернете наших данных -> вставка в файл сторонней компании, в соседний лист))). формат данных соответственно может быть любым, и не обязательно цифры(((( как выяснила, из нашей базы данные вообще втсавлются еще и с гиперссылкой :'(

Задача: выявить, если среди знаков (их может быть больше чем надо) в диапазоне ячеек столба находятся символы/буквы/цифры/знаки, совпадающие с данными то в поисковом диапазоне, найденные знаки вычеркиваются.

Идеально, если будут сравниваться данные, находящиеся в ячейке на другом листе того же файла, а не вводить сами знаки ручками в каждую формулу - это идеальный вариант.
Так как данные вставляются в эксель из разных мест, формат может быть какой угодно, но точно, что данные (которые надо найти и диапазон поиска находятся в столбце, а не в разброс по таблице)))),

Пробовала GPT чат (на минимлаьную задачу о просто вычеркивании хотя бы из нового столбца с итогом поиска, но формулы как-то не принимаются экселем(((( =IF(SUMPRODUCT(--ISNUMBER(SEARCH($C$1:$C$5, A1))) > 0, "Found", "Not Found")

Возможно ли такое чтобы исключить ручно-глазной труд?

P.S. На 1 скрине - столбец, из которого надо взять данные
2 скрин - столбец, где надо искать совпадение и идеально, вычеркивать найденные данные, а не все содержание ячейки.

Надеюсь, у меня получилось как-то внятно объяснить))))

К сообщению приложен файл: 2927232.png (151.5 Kb) · 2618523.png (130.6 Kb)

Простите, как вытащить скриншоты не знаю((( http://www.excelworld.ru/forum/12-68-349218-16-1720796558 это ссылка на исходный пост, и прошу прощения, что скрины такого качества, этот сайт с ними сделал невероятное((((( если можете вставить скриншоты, буду премного благодарна


Сообщение отредактировал Эль8731 - Суббота, 13.07.2024, 01:02
 
Ответить
СообщениеДобрый день!
Простите, если не в ту тему (мало знаю вообще Эксель)(((

Есть данные из интернет отчета компании о проданных товарах с номерами заказов и данные из нашей базы/сайта. Получаем их через скачивание файла (сторонней компании) и копировании в интернете наших данных -> вставка в файл сторонней компании, в соседний лист))). формат данных соответственно может быть любым, и не обязательно цифры(((( как выяснила, из нашей базы данные вообще втсавлются еще и с гиперссылкой :'(

Задача: выявить, если среди знаков (их может быть больше чем надо) в диапазоне ячеек столба находятся символы/буквы/цифры/знаки, совпадающие с данными то в поисковом диапазоне, найденные знаки вычеркиваются.

Идеально, если будут сравниваться данные, находящиеся в ячейке на другом листе того же файла, а не вводить сами знаки ручками в каждую формулу - это идеальный вариант.
Так как данные вставляются в эксель из разных мест, формат может быть какой угодно, но точно, что данные (которые надо найти и диапазон поиска находятся в столбце, а не в разброс по таблице)))),

Пробовала GPT чат (на минимлаьную задачу о просто вычеркивании хотя бы из нового столбца с итогом поиска, но формулы как-то не принимаются экселем(((( =IF(SUMPRODUCT(--ISNUMBER(SEARCH($C$1:$C$5, A1))) > 0, "Found", "Not Found")

Возможно ли такое чтобы исключить ручно-глазной труд?

P.S. На 1 скрине - столбец, из которого надо взять данные
2 скрин - столбец, где надо искать совпадение и идеально, вычеркивать найденные данные, а не все содержание ячейки.

Надеюсь, у меня получилось как-то внятно объяснить))))

К сообщению приложен файл: 2927232.png (151.5 Kb) · 2618523.png (130.6 Kb)

Простите, как вытащить скриншоты не знаю((( http://www.excelworld.ru/forum/12-68-349218-16-1720796558 это ссылка на исходный пост, и прошу прощения, что скрины такого качества, этот сайт с ними сделал невероятное((((( если можете вставить скриншоты, буду премного благодарна
Автор - Эль8731
Дата добавления - 13.07.2024 в 00:58
Pelena Дата: Суббота, 13.07.2024, 08:08 | Сообщение № 11
Группа: Админы
Ранг: Местный житель
Сообщений: 19409
Репутация: 4558 ±
Замечаний: ±

Excel 365 & Mac Excel
Эль8731, Прочитайте уже Правила форума и создайте свою тему. Эта тема закрыта.
Как создать тему здесь в картинках.

Цитата Эль8731, 13.07.2024 в 00:58, в сообщении № 10 ()
прошу прощения, что скрины такого качества, этот сайт с ними сделал невероятное(

зачем нам кузнец скрин, нам кузнец скрин не нужен (с)
Ответ неудобно рисовать на картинке

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеЭль8731, Прочитайте уже Правила форума и создайте свою тему. Эта тема закрыта.
Как создать тему здесь в картинках.

Цитата Эль8731, 13.07.2024 в 00:58, в сообщении № 10 ()
прошу прощения, что скрины такого качества, этот сайт с ними сделал невероятное(

зачем нам кузнец скрин, нам кузнец скрин не нужен (с)
Ответ неудобно рисовать на картинке
Автор - Pelena
Дата добавления - 13.07.2024 в 08:08
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Поиск, вывод значений из столбца с учетом двух критериев (Word)
  • Страница 1 из 1
  • 1
Поиск:

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