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

Вход

Регистрация

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

 

= Мир MS Excel/Проверка на наличие значения в диапазоне значений - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Проверка на наличие значения в диапазоне значений (Формулы/Formulas)
Проверка на наличие значения в диапазоне значений
heaven33rus Дата: Вторник, 18.07.2017, 10:58 | Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 64
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
Здравствуйте.
Никак не могу решить простенький вроде вопрос :-)
В таблице есть три диапазона со значениями (я их подкрасил разными цветами в таблице)
Нужна формула (я какую-то уже вписал в ячейку B31, но она не работает что-то), которая будет проверять, к какому диапазону относится значение из ячейки и B33.

т.е. результатом должно быть 1, 2, 3 или значение не найдено.
К сообщению приложен файл: 2976314.xlsx(27Kb)
 
Ответить
СообщениеЗдравствуйте.
Никак не могу решить простенький вроде вопрос :-)
В таблице есть три диапазона со значениями (я их подкрасил разными цветами в таблице)
Нужна формула (я какую-то уже вписал в ячейку B31, но она не работает что-то), которая будет проверять, к какому диапазону относится значение из ячейки и B33.

т.е. результатом должно быть 1, 2, 3 или значение не найдено.

Автор - heaven33rus
Дата добавления - 18.07.2017 в 10:58
sboy Дата: Вторник, 18.07.2017, 11:10 | Сообщение № 2
Группа: Проверенные
Ранг: Ветеран
Сообщений: 851
Репутация: 226 ±
Замечаний: 0% ±

Excel 2010
heaven33rus, Добрый день. В случае не найденного значения будет 0
Код
=(СУММПРОИЗВ(--(C1:BX3=B33))>0)+2*(СУММПРОИЗВ(--(C4:BX12=B33))>0)+3*(СУММПРОИЗВ(--(C13:BX22=B33))>0)
К сообщению приложен файл: 3246356.xlsx(28Kb)
 
Ответить
Сообщениеheaven33rus, Добрый день. В случае не найденного значения будет 0
Код
=(СУММПРОИЗВ(--(C1:BX3=B33))>0)+2*(СУММПРОИЗВ(--(C4:BX12=B33))>0)+3*(СУММПРОИЗВ(--(C13:BX22=B33))>0)

Автор - sboy
Дата добавления - 18.07.2017 в 11:10
_Boroda_ Дата: Вторник, 18.07.2017, 11:14 | Сообщение № 3
Группа: Модераторы
Ранг: Местный житель
Сообщений: 10992
Репутация: 4564 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Еще массивный вариант
Код
=ЕСЛИ(СЧЁТ(1/(B33=C1:BX3));1;ЕСЛИ(СЧЁТ(1/(B33=C4:BX12));2;ЕСЛИ(СЧЁТ(1/(B33=C13:BX22));3;"значение не найдено")))

Еще
Код
=ЕСЛИОШИБКА(ПОИСКПОЗ(МАКС((B33=C1:BX22)*СТРОКА(C1:C22));{0:4:13});"значение не найдено")
К сообщению приложен файл: 2976314_1.xlsx(28Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеЕще массивный вариант
Код
=ЕСЛИ(СЧЁТ(1/(B33=C1:BX3));1;ЕСЛИ(СЧЁТ(1/(B33=C4:BX12));2;ЕСЛИ(СЧЁТ(1/(B33=C13:BX22));3;"значение не найдено")))

Еще
Код
=ЕСЛИОШИБКА(ПОИСКПОЗ(МАКС((B33=C1:BX22)*СТРОКА(C1:C22));{0:4:13});"значение не найдено")

Автор - _Boroda_
Дата добавления - 18.07.2017 в 11:14
jakim Дата: Вторник, 18.07.2017, 11:33 | Сообщение № 4
Группа: Друзья
Ранг: Ветеран
Сообщений: 791
Репутация: 205 ±
Замечаний: 0% ±

Предлагаю вариант с функцией АГРЕГАТ.

Код
=IFERROR(INDEX(B$1:B$22;AGGREGATE(15;6;ROW($1:$100)/($C$1:$BX$22=$B$33);ROWS($33:33)));"")
К сообщению приложен файл: 1555391.xlsx(29Kb)
 
Ответить
Сообщение
Предлагаю вариант с функцией АГРЕГАТ.

Код
=IFERROR(INDEX(B$1:B$22;AGGREGATE(15;6;ROW($1:$100)/($C$1:$BX$22=$B$33);ROWS($33:33)));"")

Автор - jakim
Дата добавления - 18.07.2017 в 11:33
AlexM Дата: Вторник, 18.07.2017, 11:34 | Сообщение № 5
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3571
Репутация: 899 ±
Замечаний: 0% ±

Excel 2003
Код
=СУММПРОИЗВ(A1:A22*(B33=C1:BX22))

Формат ячейки 0;;"значение не найдено"
К сообщению приложен файл: 2976314_01.xlsx(27Kb)



Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
Сообщение
Код
=СУММПРОИЗВ(A1:A22*(B33=C1:BX22))

Формат ячейки 0;;"значение не найдено"

Автор - AlexM
Дата добавления - 18.07.2017 в 11:34
sboy Дата: Вторник, 18.07.2017, 11:37 | Сообщение № 6
Группа: Проверенные
Ранг: Ветеран
Сообщений: 851
Репутация: 226 ±
Замечаний: 0% ±

Excel 2010
AlexM, там есть повторяющиеся значения...
 
Ответить
СообщениеAlexM, там есть повторяющиеся значения...

Автор - sboy
Дата добавления - 18.07.2017 в 11:37
_Boroda_ Дата: Вторник, 18.07.2017, 11:37 | Сообщение № 7
Группа: Модераторы
Ранг: Местный житель
Сообщений: 10992
Репутация: 4564 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Ой блин! Столбец А -то я и не заметил!
Тогда наверное (с учетом того, что в разных диапазонах не может быть одинаковых значений)
Код
=СУММПРОИЗВ(A1:A22*(B33=C1:BX22))/СЧЁТЕСЛИ(C1:BX22;B33)

или
Код
=СРЗНАЧ(ЕСЛИ(B33=C1:BX22;A1:A22))

Еще массивка
Код
=МАКС(СЧЁТЕСЛИ(B33;C1:BX22)*A1:A22)

Тьфу, торможу
Код
=МАКС((B33=C1:BX22)*A1:A22)
К сообщению приложен файл: 8698267.xlsx(29Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеОй блин! Столбец А -то я и не заметил!
Тогда наверное (с учетом того, что в разных диапазонах не может быть одинаковых значений)
Код
=СУММПРОИЗВ(A1:A22*(B33=C1:BX22))/СЧЁТЕСЛИ(C1:BX22;B33)

или
Код
=СРЗНАЧ(ЕСЛИ(B33=C1:BX22;A1:A22))

Еще массивка
Код
=МАКС(СЧЁТЕСЛИ(B33;C1:BX22)*A1:A22)

Тьфу, торможу
Код
=МАКС((B33=C1:BX22)*A1:A22)

Автор - _Boroda_
Дата добавления - 18.07.2017 в 11:37
AlexM Дата: Вторник, 18.07.2017, 11:44 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3571
Репутация: 899 ±
Замечаний: 0% ±

Excel 2003
там есть повторяющиеся значения
тогда так
Код
=МАКС(ИНДЕКС(A1:A22*(B33=C1:BX22);))



Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
Сообщение
там есть повторяющиеся значения
тогда так
Код
=МАКС(ИНДЕКС(A1:A22*(B33=C1:BX22);))

Автор - AlexM
Дата добавления - 18.07.2017 в 11:44
vikttur Дата: Вторник, 18.07.2017, 11:46 | Сообщение № 9
Группа: Друзья
Ранг: Старожил
Сообщений: 2313
Репутация: 401 ±
Замечаний: 0% ±

В столбец А:
Код
=ПОИСКПОЗ($B$33;C1:BX1;)

Ошибку, если мешает, можно убрать с помощью ЕСЛИ или ЕСЛИОШИБКА
Результат:
Код
=ЕСЛИОШИБКА(ПРОСМОТР(99;A1:A22);"нет")
 
Ответить
СообщениеВ столбец А:
Код
=ПОИСКПОЗ($B$33;C1:BX1;)

Ошибку, если мешает, можно убрать с помощью ЕСЛИ или ЕСЛИОШИБКА
Результат:
Код
=ЕСЛИОШИБКА(ПРОСМОТР(99;A1:A22);"нет")

Автор - vikttur
Дата добавления - 18.07.2017 в 11:46
sboy Дата: Вторник, 18.07.2017, 11:49 | Сообщение № 10
Группа: Проверенные
Ранг: Ветеран
Сообщений: 851
Репутация: 226 ±
Замечаний: 0% ±

Excel 2010
[offtop] Прям закидали красотой :)
 
Ответить
Сообщение[offtop] Прям закидали красотой :)

Автор - sboy
Дата добавления - 18.07.2017 в 11:49
heaven33rus Дата: Вторник, 18.07.2017, 12:50 | Сообщение № 11
Группа: Пользователи
Ранг: Участник
Сообщений: 64
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
Спасибо всем большое!
взял вот этот вариант
Код
ЕСЛИ(СЧЁТ(1/(B33=C1:BX3));1;ЕСЛИ(СЧЁТ(1/(B33=C4:BX12));2;ЕСЛИ(СЧЁТ(1/(B33=C13:BX22));3;"значение не найдено")))
 
Ответить
СообщениеСпасибо всем большое!
взял вот этот вариант
Код
ЕСЛИ(СЧЁТ(1/(B33=C1:BX3));1;ЕСЛИ(СЧЁТ(1/(B33=C4:BX12));2;ЕСЛИ(СЧЁТ(1/(B33=C13:BX22));3;"значение не найдено")))

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

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