Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Контроль полноты заполнения столбца таблицы, формула
 
#1
22.06.2015 15:59:50
Здравствуйте!
В таблице 2 столбца. Первый столбец - Разделы. Второй - Сегменты. Определены справочники. Например, в справочнике Сегментов определено 3 разных сегмента. Возникла задача проконтролировать, что в таблице для какого-то конкретного раздела заданы ВСЕ сегменты, определенные в справочнике сегментов.
Гораздо понятнее, если посмотреть файл примера. В массивных формулах соображаю пока не очень хорошо, прошу помощи. Заранее спасибо.
с уважением,
Батьянов Денис
 
 
 
#2
22.06.2015 16:06:19
Добрый день.
Может, как-то так?

Код
=ЕСЛИ(СУММ(($E$7:$E$13=I7)*ЕСЛИОШИБКА(($F$7:$F$13=Сегменты);0))>=3;"Аллё!";"Не Аллё!")

ФОрмула массива. Вставлять в J7  и тянуть вниз.


Не, отставить. Неправильно работает.
Думаю дальше...
Изменено: Пытливый - 22.06.2015 16:13:49
Кому решение нужно - тот пример и рисует.
 
 
 
#3
22.06.2015 16:26:37
Вот эту идею не вкурил...
Код
($F$7:$F$13=Сегменты)
UPD: Пардон, начало доходить. Забыл, что так у меня называется справочник :)
Изменено: dsb75 - 22.06.2015 16:28:11
с уважением,
Батьянов Денис
 
 
 
#4
22.06.2015 16:34:08
Обычная:
=ЕСЛИ(СЧЁТЕСЛИ($E:$E;$I7)=СЧЁТЗ(Сегменты);"всё OK";"не всё OK")

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
 
 
#5
22.06.2015 16:51:45
JayBhagavan,так не пойдет. Разделы должны быть заполнены, а не просто присутствовать в нужном количестве.
 
 
 
#6
22.06.2015 22:08:55
[USER]Пытливый[/USER], а вы знаете это вполне рабочий вариант. Спасибо!
Что вас смутило?
Изменено: dsb75 - 23.06.2015 22:06:54
с уважением,
Батьянов Денис
 
 
 
#7
22.06.2015 23:06:04
Хотя да, есть проблемы, когда количество разделов не совпадает с количеством сегментов. Будем думать...
с уважением,
Батьянов Денис
 
 
 
#8
22.06.2015 23:28:51
Обязательно формулами?
 
 
 
#9
22.06.2015 23:52:39
Нет, VBA не надо. Не к месту он тут.
с уважением,
Батьянов Денис
 
 
 
#10
22.06.2015 23:54:35
Ну, в принципе чуть лучше показала себя формула:
Код
=СУММ((НЕ(ЕНД(ПОИСКПОЗ(ДанныеСегменты;Сегменты;0))))*(ДанныеРазделы=$E$7))=ЧСТРОК(Сегменты)
остановлюсь пока на ней.
с уважением,
Батьянов Денис
 
 
 
#11
22.06.2015 23:56:37
Код
=ЕСЛИ(СУММ(СУММПРОИЗВ((I7=$E$7:$E$13)*($B$2=$F$7:$F$13))>0;СУММПРОИЗВ((I7=$E$7:$E$13)*($B$3=$F$7:$F$13))>0;СУММПРОИЗВ((I7=$E$7:$E$13)*($B$4=$F$7:$F$13))>0)>=3;"ОК";"Не ОК")
Если сегментов будет много, то эта формула не удобна, а для трех сегментов сойдет.
 
 
 
#12
23.06.2015 00:03:02
Цитата
dsb75 написал: лучше показала себя формула
разве?
Изменено: ikki - 23.06.2015 00:03:53
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#13
23.06.2015 00:05:52
Цитата
dsb75 написал: Не к месту он тут
Интересно, а где к месту? ))
 
 
 
#14
23.06.2015 00:16:16
UDF намного проще, понятней и абсолютно одинакова для любого кол-ва сегментов
но изобретение лисапедов - конечно, личное право каждого :)
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#15
23.06.2015 14:35:28
ikki, спасибо за затраченные усилия, но право не стоило.
Я думаю, что вы не будете спорить, что где-то должна проходить грань, когда задача должна решаться формулами, а когда - через VBA UDF. Так вот конкретно по данной задаче я считаю, что тут вполне достаточно потенциала формул массива. Соглашусь, что часто VBA код куда понятнее многих формул массива. Кстати, метафора "изобретений лисапедов" чаще применима к ярым сторонникам VBA (уверен - не к вам).
с уважением,
Батьянов Денис
 
 
 
#16
23.06.2015 14:39:04
Цитата
dsb75 написал: чуть лучше показала себя формула...
НЕ(ЕНД заменяется на ЕЧИСЛО

Формула массива:
=--(СЧЁТ(1/ЧАСТОТА(ЕСЛИ($E$7:$E$14=I7;ПОИСКПОЗ($F$7:$F$14;Сегменты;));{1;2;3}))=3)
1 - если в строках раздела присутствуют все сегменты.
 
 
 
#17
23.06.2015 15:53:56
Цитата
dsb75 написал:
Я думаю, что вы не будете спорить, что где-то должна проходить грань, когда задача должна решаться формулами
безусловно.
Денис, сорри - не обратил сначала внимания на Вашу подпись (или Вы ее позже поменяли?)
иначе даже и начинать не стал бы. хотя подобные udf-ки пишутся за несколько минут, но, судя по Вашему сайту - Вы и сами это знаете. ))

имхо, просто достаточно писать в теме: "нужно решение ТОЛЬКО формулами"
;)
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#18
23.06.2015 21:54:41
Цитата
ikki написал: не обратил сначала внимания на Вашу подпись (или Вы ее позже поменяли?)
Да вы и не обязаны опознавать всех мало-мальски сведущих в Excel :) Нет, подпись не менял.
Цитата
достаточно писать в теме: "нужно решение ТОЛЬКО формулами"
Будем знать, спасибо за подсказку :)
с уважением,
Батьянов Денис
 
 
 
#19
23.06.2015 21:57:26
vikttur, спасибо! Обязательно попробую ваш вариант. А если мне заранее не известно, сколько у меня сегментов (не уверен, но скорее всего {1;2;3} относится к ним)?

Цитата
gling написал: Если сегментов будет много, то эта формула не удобна, а для трех сегментов сойдет.
Спасибо! Их может быть больше трёх, к сожаланию.

Цитата
ikki написал: разве?
Да, не без изъянов, но в реальной задаче (это адаптированный пример) у меня там есть отдельная проверка на то, чтобы сочетания раздел-сегмент были уникальными и ситуация задвоенности сегмента у какого-либо раздела будет просигнализирована. Поэтому, как временный вариант пойдёт, наверное.
с уважением,
Батьянов Денис
 
 
 
#20
23.06.2015 22:11:43
Свои сообщения можно дополнять.

Цитата
dsb75 написал:  если мне заранее не известно, сколько у меня сегментов
Массив констант можно заменить на СТРОКА(1:30) - с запасом, а вместо тройки в конце формулы - ЧСТРОК(Сегменты)
 
 
 
Страницы: 1
Наверх