Новости:

Прикрепить к сообщению можно только файлы xls, gif, jpg, rar, zip,7z, bas, frm, cls, doc размером до 150 Кб.

Главное меню

Проверка пересечения перечня значений в ячейках

Автор samun, 09.01.2012, 14:26

« назад - далее »

samun

Подскажите, пожалуйста, т.к. не могу найти, как сделать:
Нужно что бы в ячейке Итог!C3 устанавливалось значение 1 если все значении  в представленные диапазоне ячеек 'Показатели инвестора'!B3:B7 встречаются в диапазоне ячеек 'Показатели проекта'!B3:B7
Математически это можно изобразить так:
Если ('Показатели инвестора'!B3:B7 * 'Показатели проекта'!B3:B7) = 'Показатели инвестора'!B3:B7 то Итог!C3:=1 иначе Итог!C3:=0
Знак * - тут означает пересечение множеств

Диапазоны 'Показатели инвестора'!B3:B7 и 'Показатели проекта'!B3:B7  заполняются с использование проверки данных – список из листа справочника
Образец в прикрепленном фале.

ShAM

Файл прочитать не могу, т.к. ексель 2003. По описанию попробуйте:
=ЕСЛИ(СУММ(--ЕЧИСЛО(ПОИСКПОЗ('Показатели инвестора'!B3:B7;'Показатели проекта'!B3:B7;0));--ЕЧИСЛО(ПОИСКПОЗ('Показатели проекта'!B3:B7;'Показатели инвестора'!B3:B7;0)))=10;1;0)
формула массива (вводится одновременным нажатием Ctrl/Shift/Enter)

samun


ShAM

Вам кто-то помешал подставить формулу в нужное место?
:)

samun

#4
нет просто Вы сказали что не открылся файл.
сделал ни много иначе, т.к. может быть что что поля в диапазоне  'Показатели инвестора'!B3:B7 и диапазоне 'Показатели проекта'!B3:B7; могут быть пустыми я сделал чуть чуть иначе а именно
если ячейка в диапазоне  'Показатели инвестора'!B3:B7 пустая то соответствующим ячейкам из диапазона ='Показатели инвестора'!C3:C7 присваивается 0,2
или
если
ячейка в диапазоне  содержит значение 'Показатели инвестора'!B3:B7 хоть раз встречающееся в диапазоне 'Показатели проекта'!B3:B7; то соответствующим ячейкам из диапазона ='Показатели инвестора'!C3:C7 присваивается 0,2  
иначе соответствующим ячейкам из диапазона ='Показатели инвестора'!C3:C7 присваивается 0

ну т.к. надо что бы все поля из диапазона   'Показатели инвестора'!B3:B7 были представлены в диапазоне  'Показатели проекта'!B3:B7;  то формула выглядит вот так
=ЕСЛИ(СУММ('Показатели инвестора'!C3:C7)=1;1;0)
Может я где ошибся?

ShAM

Тогда может так без дополнительного столбца.

samun

=ЕСЛИ(СУММ(ЕЧИСЛО(ПОИСКПОЗ('Показатели инвестора'!B3:B7;'Показатели проекта'!B3:B7;0))+ЕПУСТО('Показатели инвестора'!B3:B7))=5;1;0)

ПОИСКПОЗ('Показатели инвестора'!B3:B7;'Показатели проекта'!B3:B7;0) - вернет номера вхождения множества 'Показатели инвестора'!B3:B7 в множества 'Показатели проекта'!B3:B7;0 т.е. числа {4;1;2;5;#Н/Д}
Функция ЕЧИСЛО ({4;1;2;5;#Н/Д} ) вернет массив {Истина;Истина;Истина;Истина;ЛОЖЬ}
Функция ЕПУСТО ('Показатели инвестора'!B3:B7) вернет массив {ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;Истина;}
{ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;Истина;}  + {Истина;Истина;Истина;Истина;ЛОЖЬ}   даст (1;1;1;1;1)
Ну и СУММ (1;1;1;1;1)  = 5
С помощью ЕПУСТО('Показатели инвестора'!B3:B7)) учли пустые строки

Ну что тут скажешь респект изящное решение

samun

#7
как было
=ЕСЛИ(СУММ(ЕЧИСЛО(ПОИСКПОЗ('Показатели инвестора'!B3:B7;'Показатели проекта'!B3:B7;0))+ЕПУСТО('Показатели инвестора'!B3:B7))=5;1;0)
как стало
=ЕСЛИ(СУММ(ЕЧИСЛО(ПОИСКПОЗ('Показатели инвестора'!B3:B7;'Показатели проекта'!B3:B7;0))+ЕПУСТО('Показатели инвестора'!B3:B7))=ЧСТРОК('Показатели инвестора'!B3:B7);1;0)
вместо "=5" записал  "ЧСТРОК('Показатели инвестора'!B3:B7)"
формула массива (вводится одновременным нажатием Ctrl/Shift/Enter)
ссори

_Boroda_

еще вариант
=--(СУММ(--('Показатели инвестора'!B3:B7=ТРАНСП('Показатели проекта'!B3:B7)))=СЧЁТЗ('Показатели инвестора'!B3:B7))
формула массива
Скажи мне, кудесник, любимец ба'гов...



Яндекс-деньги: 41001632713405
Webmoney: R289877159277; Z102172301748; E177867141995

ShAM

_Boroda_, формула жуть. :)
Но вот так, вроде бы, не работает, а самому подправить мозгов не хватает.

_Boroda_

А это потому, что в показателях проекта пустые ячейки. Тода так можно.
=--(СУММ(--('Показатели инвестора'!B3:B7=ТРАНСП('Показатели проекта'!B3:B7)))=МАКС('Показатели инвестора'!A3:A1000))

а на самом деле, если уж имена используете, то см. вложение. Так все работает без изменений и можно добавлять строки на оба листа с показателями
Скажи мне, кудесник, любимец ба'гов...



Яндекс-деньги: 41001632713405
Webmoney: R289877159277; Z102172301748; E177867141995

ShAM

Спасибо, _Boroda_. Полчаса сидел смотрел на формулы и не мог понять, что же изменилось, пока не догадался в имена заглянуть. :)