Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Запрет на ввод определенных значений в диапазон ячеек
 
#1
29.04.2018 00:19:02
Добрый вечер всем !

Помогите составить формулу для Проверка данных - Другой  для решения такого:

На листе Лист1 имеется диапазон ячеек P4:P200 куда вводятся данные.
На листе Лист2 имеется диапазон ячеек D2:D40 содержащий список значений (которые можно вносить на Лист1 в вводимый диапазон)
Другие значения (отличные от значений Лист2 !$D$2:$D$40) нельзя вносить на Лист1!$P$4:$P$200.

Решения типа Проверка данных - Список не подходит, тк дает сбоку от ячейки выпадающий список значений который не нужен.
Можно ли по другому - формулой через Проверка данных - Другой ?
 
 
 
#2
29.04.2018 00:24:38
СЧЁТЕСЛИ?
 
 
 
#3
29.04.2018 00:38:09
Как понимаю тут надо както сравнивать введенное значение в ячейку со значениями Лист2  а СЧЕТ ЕСЛИ  вроде просто считает?   но к сожалению познаний не хватает
Изменено: andreyka33 - 29.04.2018 00:41:55
 
 
 
#4
29.04.2018 00:44:26
Цитата
andreyka33 написал: Можно ли по другому
По-другому можно: Вы показываете файл-пример.
 
 
 
#5
29.04.2018 00:50:28
Вашего файла нет - смотрите мой.
 
 
 
#6
29.04.2018 01:00:45
вот пример
 
 
 
#7
29.04.2018 01:11:05
Формула рабочая в вашем примере для ввода в A1
Код
=СЧЁТЕСЛИ(Лист2!A1:A10;A1)>0

Я ее попробовал под себя изменить так например  - но так не заработало для ввода в диапазон A1:A5 к сожалению  
Код
=СЧЁТЕСЛИ(Лист2!A1:A10;A1:A5)>0
 
 
 
#8
29.04.2018 01:16:32
Это уже массив
=СУММПРОИЗВ(--(СЧЕТЕСЛИ(Лист2!A1:A10;A1:A5)>0))
 
 
 
#9
29.04.2018 01:28:50
чтото не пошло у меня с вашей формулой  - кроме А1 на остальных ячейках выкидывает   хотя вношу набором значения из списка на листе 2  
Изменено: andreyka33 - 29.04.2018 01:29:46
 
 
 
#10
29.04.2018 01:34:15
Сразу не посмотрел, что для проверки данных...
Зачем задавать массив? Ведь нужна проверка для одной конкретной ячейки.
А посмотрите на диапазон второго листа в ячейках ниже. Ничего не смущает? Диапазон закрепить нужно:
=СЧЁТЕСЛИ(Лист2!$A$1:$A$10;A1)=0
 
 
 
#11
29.04.2018 01:40:15
Ничего не понял конечно -
те диапазоном не ввести ??
те каждую на каждую ячейку прописывать надо?
Код
=СЧЁТЕСЛИ(Лист2!$A$1:$A$10;A1)=0
=СЧЁТЕСЛИ(Лист2!$A$1:$A$10;A2)=0
=СЧЁТЕСЛИ(Лист2!$A$1:$A$10;A3)=0  и тд?
 
 
 
#12
29.04.2018 01:52:15
Выделить диапазон, записать формулу в проверку данных
 
 
 
#13
29.04.2018 01:53:32
Благодарю Vikttur ! - понял что вы имели в виду
Так получилось
Код
=СУММПРОИЗВ(--(СЧЕТЕСЛИ(Лист2!$A$1:$A$10;A1:A5)>0))
Изменено: andreyka33 - 29.04.2018 02:02:44
 
 
 
#14
29.04.2018 02:02:55
Нет! Формулы из сообщения №10 достаточно.
 
 
 
#15
29.04.2018 02:05:23
Только, наверное, если запрет ввода данных из диапазона, следует поменять "больше" на "равно"?
 
 
 
#16
29.04.2018 03:42:00
рано радовался но добился бм стабильной работы на этой формуле (все варианты выше не работают c  диапазоном - все проверял и разные варианты еще с = и >)
Код
=СЧЁТЕСЛИ(Лист2!$A$1:$A$10;A5:A1)>0
но 2 косяк вылез - при удалении строки из диапазона Лист1 (где применяем формулу) формула сбивается на формулу ниже.  Файл приложил - такто работает но с удалением строки описанные проблемы. На примере можно попробовать - удалить любую строку A1 до А5 и зайти в  Проверка данных - Тип данных - Другой и увидеть измененную формулу
Код
=СЧЁТЕСЛИ(Лист2!$A$1:$A$10;A1048573:A1)>0
Изменено: andreyka33 - 29.04.2018 04:09:38
 
 
 
#17
29.04.2018 05:16:49
Вариант без проверки данных.
 
 
 
#18
29.04.2018 08:04:30
Может так
=(COUNTIF(Лист2!$A$1:$A$10;A1)>0)*(COUNTIFS(A:A;A1)<2)
Изменено: БМВ - 29.04.2018 08:04:46
По вопросам из тем форума, личку не читаю.
 
 
 
#19
29.04.2018 12:59:37
Спасибо за поддержку всем кто откликнулся !
В общем через формулы при дальнейшем редактировании строк могут косяки вылазить
а вариант Юрий М через макрос железно работает
по варианту БМВ тоже все стабильно - но при удалении 1 строки в диапазоне   вводимый диапазон сокращается на 1 строку
 
 
 
#20
29.04.2018 13:05:59
Цитата
andreyka33 написал:
но при удалении 1 строки в диапазоне   вводимый диапазон сокращается на 1 строку
почему меня это не удивляет? :-)
По вопросам из тем форума, личку не читаю.
 
 
 
#21
29.04.2018 13:15:25
andreyka33, посмотрите еще раз внимательно на формулу в сообщении №10
 
 
 
#22
12.05.2018 16:08:53
По своей задаче нашел эту тему. Скажите, разве нельзя через проверку данных сделать запрет на ввод данных в ДИАПАЗОНЕ?? Для одной ячейки сделать условие, что если в определенной ячейке пустота, то запрещать ввод данных получилось, а сделать тоже самое для диапазона не получается.
Накидал пример, он во вложении. Тоесть  делаю проверку данных для диапазона, запрет на ввод в этом диапазоне не работает.
Подскажите?
 
 
 
#23
12.05.2018 17:14:42
Выделяйте диапазон и добавляйте проверку данных. При этом, если Вы уже ранее в часть диапазона добавляли проверку, то Excel любезно предложит эту проверку распространить на другие ячейки. Формулы для проверки условий для диапазона вводятся аналогично формулам для ячеек диапазона, так что будьте внимательны к абсолютной/относительной адресации (знак доллара!)
Изменено: sokol92 - 12.05.2018 18:40:01
Владимир
 
 
 
#24
12.05.2018 19:29:38
ESavin, нужно описывать условие, чтобы помогающие меньше времени тратили.
Как понял: запретить ввод данных, если в ячейке B2 нет даты ().  У Вас это пописано, единственное. что Вы не сделали - не закрепили сылку на строку.
 
 
 
Страницы: 1
Наверх