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

Вход

Регистрация

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

 

= Мир MS Excel/ВПР с маской - Мир MS Excel

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

Excel 2010
Доброго времени суток,

Дано: лист№1 со списком счетов (столбец) формата "Счет А", "Счет Б", "Счет В" итд; лист№2 со списком счетов (столбец) формата "Счет А", "Счет Б от 90 до 180", "Счет В 25" итд.

Задача: если в листе №1 отсутствуют счета, которые присутствуют в листе №2, то напротив данного счета в листе№2 должно выводиться сообщение FAIL, в противном случае PASS. Т.е. проверку проходят счета формата "Счет А*", "Счет Б*", итд.

Я попытался решить задачу с помощью формулы вида : ЕСЛИ(ВПР([искомое значение с маской];таблица;№ряда;ложь)="#Н/Д";FAIL;PASS)
Идея собственно в том, что если ВПР не находит значений по маске, то отображается FAIL, противном случае PASS.

Данный вариант не работает, а как сделать по другому ума не приложу :( Опыта с Excel'ем крайне мало.

Подскажите, пожалуйста, можно ли как-нибудь модифицировать эту функцию, чтобы она справилась с задачей, или это совсем невозможно?
Тогда в каком направлении искать? <_<
К сообщению приложен файл: 4excelworld.xlsx (8.9 Kb)


Сообщение отредактировал lalalaexcel - Среда, 23.07.2014, 21:47
 
Ответить
СообщениеДоброго времени суток,

Дано: лист№1 со списком счетов (столбец) формата "Счет А", "Счет Б", "Счет В" итд; лист№2 со списком счетов (столбец) формата "Счет А", "Счет Б от 90 до 180", "Счет В 25" итд.

Задача: если в листе №1 отсутствуют счета, которые присутствуют в листе №2, то напротив данного счета в листе№2 должно выводиться сообщение FAIL, в противном случае PASS. Т.е. проверку проходят счета формата "Счет А*", "Счет Б*", итд.

Я попытался решить задачу с помощью формулы вида : ЕСЛИ(ВПР([искомое значение с маской];таблица;№ряда;ложь)="#Н/Д";FAIL;PASS)
Идея собственно в том, что если ВПР не находит значений по маске, то отображается FAIL, противном случае PASS.

Данный вариант не работает, а как сделать по другому ума не приложу :( Опыта с Excel'ем крайне мало.

Подскажите, пожалуйста, можно ли как-нибудь модифицировать эту функцию, чтобы она справилась с задачей, или это совсем невозможно?
Тогда в каком направлении искать? <_<
Автор - lalalaexcel
Дата добавления - 23.07.2014 в 21:45
mus013 Дата: Среда, 23.07.2014, 22:15 | Сообщение № 2
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 102
Репутация: 25 ±
Замечаний: 0% ±

Excel 2007
Добрый вечер, lalalaexcel, посмотрите, пож-та, не судите строго
К сообщению приложен файл: 4excelworld_1.xlsx (9.9 Kb)

Помогать, действительно важно и нужно. «Может, это и не изменит мир, но точно изменит вас!»
 
Ответить
СообщениеДобрый вечер, lalalaexcel, посмотрите, пож-та, не судите строго
Автор - mus013
Дата добавления - 23.07.2014 в 22:15
gling Дата: Среда, 23.07.2014, 22:18 | Сообщение № 3
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2643
Репутация: 739 ±
Замечаний: 0% ±

2010
Здравствуйте. Попробуйте такую формулу
Код
=ЕСЛИ(СЧЁТЕСЛИ(баланс!$A$1:$A$4;ЛЕВСИМВ(Лист3!A1;6))>0;"PASS";"FAIL")
Реальные счета думаю придется как то подогнать, возможно не через ЛЕВСИМВ

ЯД-41001506838083
 
Ответить
СообщениеЗдравствуйте. Попробуйте такую формулу
Код
=ЕСЛИ(СЧЁТЕСЛИ(баланс!$A$1:$A$4;ЛЕВСИМВ(Лист3!A1;6))>0;"PASS";"FAIL")
Реальные счета думаю придется как то подогнать, возможно не через ЛЕВСИМВ
Автор - gling
Дата добавления - 23.07.2014 в 22:18
AlexM Дата: Среда, 23.07.2014, 22:23 | Сообщение № 4
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1130 ±
Замечаний: 0% ±

Excel 2003
еще вариант
Код
=ЕСЛИ(ЕНД(ПРОСМОТР(1;ПОИСК(баланс!A$1:A$4;A1)));"PASS";"FAIL")
К сообщению приложен файл: 4excelworld_2.xls (24.0 Kb)


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

Сообщение отредактировал AlexM - Среда, 23.07.2014, 22:23
 
Ответить
Сообщениееще вариант
Код
=ЕСЛИ(ЕНД(ПРОСМОТР(1;ПОИСК(баланс!A$1:A$4;A1)));"PASS";"FAIL")
Автор - AlexM
Дата добавления - 23.07.2014 в 22:23
lalalaexcel Дата: Среда, 23.07.2014, 22:42 | Сообщение № 5
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Господа, всем спасибо за Вашу помощь, но по-видимому я не правильно выразил суть задачи, либо Ваши варианты не подошли :

Пример:
1. "счет 3 абв" должен пройти проверку, т.к. на листе1 присутствует "счет 3"
2. "счет 7 2а" не проходит проверку, т.к. на листе1 отсутствует "счет 7"

Прилагаю книгу с эталоном значений, которые должны отобразиться.
К сообщению приложен файл: 4excelworld_3.xlsx (9.3 Kb)
 
Ответить
СообщениеГоспода, всем спасибо за Вашу помощь, но по-видимому я не правильно выразил суть задачи, либо Ваши варианты не подошли :

Пример:
1. "счет 3 абв" должен пройти проверку, т.к. на листе1 присутствует "счет 3"
2. "счет 7 2а" не проходит проверку, т.к. на листе1 отсутствует "счет 7"

Прилагаю книгу с эталоном значений, которые должны отобразиться.
Автор - lalalaexcel
Дата добавления - 23.07.2014 в 22:42
Pelena Дата: Среда, 23.07.2014, 22:48 | Сообщение № 6
Группа: Админы
Ранг: Местный житель
Сообщений: 19521
Репутация: 4634 ±
Замечаний: ±

Excel 365 & Mac Excel
Простая формула
Код
=ЕСЛИ(СУММПРОИЗВ(--(ЕЧИСЛО(ПОИСК(Лист1!$A$1:$A$6;Лист2!A2))));"PASS";"FAIL")

или формула массива
Код
=ЕСЛИ(ИЛИ(ЕЧИСЛО(ПОИСК(Лист1!$A$1:$A$6;Лист2!A2)));"PASS";"FAIL")
К сообщению приложен файл: 3092281.xlsx (10.0 Kb)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеПростая формула
Код
=ЕСЛИ(СУММПРОИЗВ(--(ЕЧИСЛО(ПОИСК(Лист1!$A$1:$A$6;Лист2!A2))));"PASS";"FAIL")

или формула массива
Код
=ЕСЛИ(ИЛИ(ЕЧИСЛО(ПОИСК(Лист1!$A$1:$A$6;Лист2!A2)));"PASS";"FAIL")
Автор - Pelena
Дата добавления - 23.07.2014 в 22:48
AlexM Дата: Среда, 23.07.2014, 22:54 | Сообщение № 7
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1130 ±
Замечаний: 0% ±

Excel 2003
В моем решении перепутано Fail и Pass
исправил
Код
=ЕСЛИ(ЕНД(ПРОСМОТР(1;ПОИСК(Лист1!A$1:A$6;A2)));"FAIL";"PASS")
и массивная, почти как у Елены
Код
=ЕСЛИ(ИЛИ(1-ЕОШ(ПОИСК(Лист1!A$1:A$6;A2)));"PASS";"FAIL")
К сообщению приложен файл: 4excelworld_3.xls (25.5 Kb)


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

Сообщение отредактировал AlexM - Среда, 23.07.2014, 23:12
 
Ответить
СообщениеВ моем решении перепутано Fail и Pass
исправил
Код
=ЕСЛИ(ЕНД(ПРОСМОТР(1;ПОИСК(Лист1!A$1:A$6;A2)));"FAIL";"PASS")
и массивная, почти как у Елены
Код
=ЕСЛИ(ИЛИ(1-ЕОШ(ПОИСК(Лист1!A$1:A$6;A2)));"PASS";"FAIL")
Автор - AlexM
Дата добавления - 23.07.2014 в 22:54
lalalaexcel Дата: Среда, 23.07.2014, 22:58 | Сообщение № 8
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Благодарю за решение!
Пойду разбираться в новых для себя функциях :)
 
Ответить
СообщениеБлагодарю за решение!
Пойду разбираться в новых для себя функциях :)
Автор - lalalaexcel
Дата добавления - 23.07.2014 в 22:58
_Boroda_ Дата: Среда, 23.07.2014, 23:17 | Сообщение № 9
Группа: Админы
Ранг: Местный житель
Сообщений: 16912
Репутация: 6616 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
И еще одна
Код
=ЕСЛИ(СЧЁТ(ПОИСК(Лист1!A$1:A$6;A2));"PASS";"FAIL")

формула массива
К сообщению приложен файл: 3092281_1.xlsx (9.8 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеИ еще одна
Код
=ЕСЛИ(СЧЁТ(ПОИСК(Лист1!A$1:A$6;A2));"PASS";"FAIL")

формула массива
Автор - _Boroda_
Дата добавления - 23.07.2014 в 23:17
AlexM Дата: Среда, 23.07.2014, 23:28 | Сообщение № 10
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1130 ±
Замечаний: 0% ±

Excel 2003
Еще можно сделать формат ячеек "PASS";;"FAIL"
А в ячейке оставить только логическую часть массивной формулы Александра
Код
=СЧЁТ(ПОИСК(Лист1!A$1:A$6;A2))
К сообщению приложен файл: 4excelworld_4.xls (25.5 Kb)


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

Сообщение отредактировал AlexM - Среда, 23.07.2014, 23:31
 
Ответить
СообщениеЕще можно сделать формат ячеек "PASS";;"FAIL"
А в ячейке оставить только логическую часть массивной формулы Александра
Код
=СЧЁТ(ПОИСК(Лист1!A$1:A$6;A2))
Автор - AlexM
Дата добавления - 23.07.2014 в 23:28
AlexM Дата: Среда, 23.07.2014, 23:42 | Сообщение № 11
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1130 ±
Замечаний: 0% ±

Excel 2003
Цитата lalalaexcel, 23.07.2014 в 22:58, в сообщении № 8
Пойду разбираться в новых для себя функциях
Запутали и так. ;)


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
Сообщение
Цитата lalalaexcel, 23.07.2014 в 22:58, в сообщении № 8
Пойду разбираться в новых для себя функциях
Запутали и так. ;)
Автор - AlexM
Дата добавления - 23.07.2014 в 23:42
Мир MS Excel » Вопросы и решения » Вопросы по Excel » ВПР с маской (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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