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

Вход

Регистрация

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

 

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

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

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

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

т.е. результатом должно быть 1, 2, 3 или значение не найдено.
Автор - heaven33rus
Дата добавления - 18.07.2017 в 10:58
sboy Дата: Вторник, 18.07.2017, 11:10 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2566
Репутация: 724 ±
Замечаний: 0% ±

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

Яндекс: 410016850021169
 
Ответить
Сообщение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
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16674
Репутация: 6479 ±
Замечаний: 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 (27.5 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
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
Группа: Друзья
Ранг: Старожил
Сообщений: 1199
Репутация: 313 ±
Замечаний: 0% ±

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

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

Код
=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
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4480
Репутация: 1114 ±
Замечаний: 0% ±

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

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


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

Формат ячейки 0;;"значение не найдено"
Автор - AlexM
Дата добавления - 18.07.2017 в 11:34
sboy Дата: Вторник, 18.07.2017, 11:37 | Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2566
Репутация: 724 ±
Замечаний: 0% ±

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

Яндекс: 410016850021169
 
Ответить
СообщениеAlexM, там есть повторяющиеся значения...
Автор - sboy
Дата добавления - 18.07.2017 в 11:37
_Boroda_ Дата: Вторник, 18.07.2017, 11:37 | Сообщение № 7
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16674
Репутация: 6479 ±
Замечаний: 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 (29.0 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
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
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4480
Репутация: 1114 ±
Замечаний: 0% ±

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


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
Сообщение
Цитата sboy, 18.07.2017 в 11:37, в сообщении № 6 ()
там есть повторяющиеся значения
тогда так
Код
=МАКС(ИНДЕКС(A1:A22*(B33=C1:BX22);))
Автор - AlexM
Дата добавления - 18.07.2017 в 11:44
vikttur Дата: Вторник, 18.07.2017, 11:46 | Сообщение № 9
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2941
Репутация: 526 ±
Замечаний: 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
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2566
Репутация: 724 ±
Замечаний: 0% ±

Excel 2010
[offtop] Прям закидали красотой :)

Яндекс: 410016850021169
 
Ответить
Сообщение[offtop] Прям закидали красотой :)
Автор - sboy
Дата добавления - 18.07.2017 в 11:49
heaven33rus Дата: Вторник, 18.07.2017, 12:50 | Сообщение № 11
Группа: Пользователи
Ранг: Участник
Сообщений: 91
Репутация: 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 из 1
  • 1
Поиск:

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