Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1
RSS
Связанные выпадающие списки с динамическими именованными диапазонами.
 
#1
17.03.2011 17:02:03
Народ, помогите, плиз...  
Я с формулами и именованными диапазонами на Вы...  
Мне бы лучше макрос какой разобрать, подшлифовать.  
А тут потребовалось ОЧЕНЬ СРОЧНО (завтра к обеду надо согласовать со всеми и начать юзать) сделать форму АКТА монтажа/демонтажа оборудования узлов связи.  
И при этом не макросами (наша "бюстгалтерия" этого слова как огня боится и у всех максимальная степень защиты стоит, а как только Ёксель начинает при открытии файла кричать, что в нём макросы, сразу отказываются его даже смотреть)  
 
Проблема в создании связанных выпадающих списков из динамических именованных диапазонов.  
 
Тему "Связанные выпадающие списки" в "Примерах" почитал.  
Попробовал. Наткнулся на те же грабли, что и многие: функция ДВССЫЛ() отказывается работать с динамическими диапазонами.  
В дискуссии к теме увидал, что народ как-то это обходит, но формулы, при этом используемые слишком сложны для меня... Да ещё и в аглицкой локали написаны.  
Я максимально упростил пример.  
ПОМОЖИТЕ, КТО МОЖЕТЕ....
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
 
 
#2
17.03.2011 17:36:30
Для списка С5:  
=ИНДЕКС(СПИСКИ!$N$2:$O$25;1;ПОИСКПОЗ(АКТ!$C$4;СПИСКИ!$N$1:$O$1;0)):ИНДЕКС(СПИСКИ!$N$2:$O$25;ПОИСКПОЗ("яя";ИНДЕКС(СПИСКИ!$N$2:$O$25;;ПОИСКПОЗ(АКТ!$C$4;СПИСКИ!$N$1:$O$1;0));1);ПОИСКПОЗ(АКТ!$C$4;СПИСКИ!$N$1:$O$1;0))
 
 
 
#3
17.03.2011 20:56:02
Виктор, спасибо за помощь.  
Только моего знания не хватает чтобы понять, куда формулу вставлять...  
В формулу списка проверки ячейки С5? - Ругается, что диапазон должен быть на той же странице...  
Или это формула именованного диапазона на странице СПИСКИ?  
Тогда в С5 должно быть имя этого диапазона?  
Ща буду пробовать...
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
 
 
#4
17.03.2011 21:01:23
Виктор, СПАСИБО!  
Получилось!  
Только, наверное, чтобы не привязываться к положению ячейки на листе АКТ, лучше дать ей имя и в формуле заменить АКТ!С5 на это имя?  
Завтра буду пробовать с реальным файлом на работе. Надеюсь, что успею к "времени Ч"
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
 
 
#5
17.03.2011 21:34:33
Алексей, парой имен можно обойтись
 
 
 
#6
17.03.2011 21:39:27
Да, и еще, вот эту часть в формуле Виктора:  
ПОИСКПОЗ("яя"....  
Заменил на СЧЁТЕСЛИ(...  
Так как ПОИСКПОЗ("яя"...  не увидит числа, если они будут в столбцах.
 
 
 
#7
17.03.2011 21:49:01
не, kim,  
что-то не так...  
в списке выбора типов оборудования должны быть только значения "СТАТИВ" и "ОБОРУДОВАНИЕ", а не вся первая строка листа СПИСКИ!  
А если я изменяю ваш диапазон    
Тип_оборудования=СПИСКИ!$A$1:$Q$1  
на  
Тип_оборудования=СПИСКИ!$N$1:$O$1  
то вместо стативов и оборудования во вторичный список проверки притягиваются фамилии и Ф.И.О.
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
 
 
#8
17.03.2011 22:29:05
Значит я неправильно понял, думал всю строку нужно. Исправил. Впрочем у Виктора тоже самое.
 
 
 
#9
17.03.2011 22:53:35
Офф: Лешка, предатель! К формулистам, значит?  
П.С. Игорь, Виктор, здравствуйте.  
Шучу, конечно:-)
Я сам - дурнее всякого примера! ...
 
 
 
#10
18.03.2011 00:43:52
Алексей, нам ли бояться летучих смещений? :-)  
См. вложение
 
 
 
#11
18.03.2011 02:15:06
Поиронизировал, пора и объясниться :-)  
Мы часто неосознанно избегаем использования летучих функций в формулах имен, в частности, в формулах динамических диапазонов для выпадающих списков. При этом формулы становятся длинными и плохо читаемыми. Но дело в том, что опасения насчет пересчета формул таких имен при каждом пересчете формул листа (т.е общего замедления Excel) лишены оснований.  
 
Формула динамического диапазона, на основании которой строится выпадающий список, не пересчитывается при пересчете листа, независимо от того, используются в ней летучие функции или нет. Пересчет формул имен происходит только при попытке выбора значения из выпадающего списка.  
 
А вот формула ячейки, которая ссылается на имя с летучими функциями, действительно сама может стать  летучей, но только если в формуле этой ячейки срабатывают условия для обращения к имени.  
Например, если имя ЭтоДата имеет в RefersTo летучую формулу =СЕГОДНЯ()  
то формула ячейки =ЕСЛИ(A1=1; ЭтоДата) станет летучей, только если A1=1  
 
Раз уж затронул эту тему, напомню, что формулы условного форматирования в ячейках видимой части экрана пересчитываются при любом изменении ячеек этой (и только этой) видимой части, или даже при перерисовке видимой части экрана от навигации.  Поэтому и имена, на которые ссылаются формулы условного форматирования, также пересчитываются. Это нужно учитывать, но не бояться :-)  
 
Вывод: в формулах именованных диапазонов для выпадающих списков можно и полезно использовать летучие функции.
 
 
 
#12
18.03.2011 02:35:01
{quote}{login=ZVI}{date=18.03.2011 12:43}{thema=}{post}  
...См. вложение{/post}{/quote}  
 
 
ZVI разбирал Ваш пример, в попытках применить на своем примере  
http://www.planetaexcel.ru/forum.php?thread_id=25550  
могли бы Вы мне помочь - хотелось бы посмотреть на ваше решение
 
 
 
#13
18.03.2011 03:19:31
В той теме 29 сообщений и несколько авторитетных попыток разобраться.  
Т.е. тема явно не для ночных разборок :-)  
Давайте лучше я отвечу по своему примеру, если что не ясно.  
 
Посмотрите еще вот эту тему, там есть разные варианты списков в сочетании с макросами:  
http://www.planetaexcel.ru/forum.php?thread_id=6538
 
 
 
#14
18.03.2011 08:27:55
РЕБЯТА,  
О Г Р О М Н О Е   С П А С И Б О  вам всем, что откликнулись.  
Мне даже стыдно: вопрос задал, сам из сети свалил в 10 вечера, а вы тут до 3-х утра парились... :-(  
 
=СИМВОЛ(196)&СИМВОЛ(224)&СИМВОЛ(32)&СИМВОЛ(236)&СИМВОЛ(232)&СИМВОЛ(237)&СИМВОЛ(243)&СИМВОЛ(254)&СИМВОЛ(242)&СИМВОЛ(32)&СИМВОЛ(226)&СИМВОЛ(224)&СИМВОЛ(241)&СИМВОЛ(32)&СИМВОЛ(35)&СИМВОЛ(205)&СИМВОЛ(47)&СИМВОЛ(196)&СИМВОЛ(44)&СИМВОЛ(32)&СИМВОЛ(35)&СИМВОЛ(199)&СИМВОЛ(205)&СИМВОЛ(192)&СИМВОЛ(215)&СИМВОЛ(33)&СИМВОЛ(32)&СИМВОЛ(44)&СИМВОЛ(35)&СИМВОЛ(209)&СИМВОЛ(209)&СИМВОЛ(219)&СИМВОЛ(203)&СИМВОЛ(202)&СИМВОЛ(192)&СИМВОЛ(33)&СИМВОЛ(32)&СИМВОЛ(44)&СИМВОЛ(35)&СИМВОЛ(196)&СИМВОЛ(197)&СИМВОЛ(203)&СИМВОЛ(47)&СИМВОЛ(48)&СИМВОЛ(33)&СИМВОЛ(32)&СИМВОЛ(232)&СИМВОЛ(32)&СИМВОЛ(35)&СИМВОЛ(200)&СИМВОЛ(204)&СИМВОЛ(223)&СИМВОЛ(63)  
 
:-)  
 
Обязательно попробую разобраться с советом ZVI. Но позже - сейчас меня пока вполне устраивает решение Виктора. Только имя ячейке, где выбор одного из связанных списков делается, присвою, в его формулу вставлю и займусь "наведением внешнего вида" на листе АКТ, который будет виден начальству (лист СПИСКИ я сделаю VeryHidden чтобы до него могли добраться только те, кто умеет юзать VBE).
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
 
 
#15
18.03.2011 09:08:48
P.S. Серёга! Не боись за мою ориентацию.    
Я не сам, а под давлением безвыходных обстоятельств... Надеюсь, это не надолго. А потом я опять вернусь к своим любимым макросам, которые я в отличие от формул массива могу понять и более-менее умею сам писАть...
 
 
 
#16
18.03.2011 09:24:55
Владимир, отличная лекция :)  
Вопрос "витал в воздухе" давно. Как-то когда-то мельком проскочила информация о связке имена+летучие_функции. Я тогда понял так, что имена, как и условное форматирование, летучи всегда. Все хотел поднять этот вопрос. Но, чтобы задать его на форуме, нужно было сесть и проверить самому, а до этого не дошло (время? лень?).  
То, что пересчитываются ТОЛЬКО "видимое форматирование", тоже ново.  
Спасибо.
 
 
 
#17
18.03.2011 13:37:36
Практически всё, что хотел, сделал.  
Связка списков работает. Ещё раз СПАСИБО.  
Но в процессе "полировки" потребовалось изменить число типов оборудования...  
В стольких местах пришлось изменять число столбцов!  
В связи с этим встал вопрос:  
Как в формуле указать число столбцов именованного диапазона?  
Ну, например:  
Есть именованный диапазон, заголовков столбцов таблицы: ШАПКА=B1:D1  
Для выполнения операций с данными в этих столбцах мне необходимо задать первую строку данных и размер диапазона данных(пусть 99 строк)  
Вроде бы всё просто:  
ПЕРВАЯ_СТРОКА=СМЕЩ(ШАПКА;1;0;1;3)  
ДИАПАЗОН_ДАННЫХ=СМЕЩ(ШАПКА;1;0;99;3)  
При фиксированном числе столбцов и строк не трудно один раз и руками вбить во все формулы цифру 3 - число столбцов в ШАПКЕ  
Но если в процессе эксплуатации таблицы понадобится добавить ещё один столбец, то придётся не только в формуле диапазона ШАПКА исправлять D1 на E1, но и в формулах ПЕРВАЯ_СТРОКА и ДИАПАЗОН_ДАННЫХ изменять 3 на 4.  
А как можно в формулах, описывающих диапазоны ПЕРВАЯ_СТРОКА и ДИАПАЗОН_ДАННЫХ, вместо прямого задания числа столбцов ШАПКИ прописать переменную, равную её текущей ширине?  
Тогда при изменении размерности диапазона данных нужно будет только изменить размер ШАПКИ, а ПЕРВАЯ_СТРОКА и ДИАПАЗОН_ДАННЫХ уж сами подстроятся под неё.
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
 
 
#18
18.03.2011 13:42:55
Сам спросил, а сейчас Справку почитал и сам же всё понял - просто опустить (в хорошем смысле слова) аргумент [ширина] в функции СМЕЩ и тогда ширина будет равна ширине ШАПКИ
С уважением, Алексей (ИМХО: Excel-2003 - THE BEST!!!)
 
 
 
Страницы: 1
Читают тему
Наверх