Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1
RSS
Формула для создания динамического именованного диапазона
 
#1
07.11.2011 17:54:33
Добрый день.  
Возник вопрос, почему для определения динамического диапазона зачастую используется летучая СМЕЩ ? Вот и в приемах (http://www.planetaexcel.ru/tip.php?aid=93) то же.  
Почему бы не использовать обычный ИНДЕКС ? Может тут где-то есть загвоздка ?  
 
В примере два диапазона, один определяется через ИНДЕКС, другой через СМЕЩ:  
=Лист1!$A$2:ИНДЕКС(Лист1!$A:$A;СЧЁТЗ(Лист1!$A:$A))  
=СМЕЩ(Лист1!$B$2;;;СЧЁТЗ(Лист1!$B:$B)-1)  
 
Проверял, корректно работают оба.    
Главный вопрос корректно ли задание динамического диапазона через функцию ИНДЕКС, и чем такой вариант хуже/лучше привычного варианта со СМЕЩ?
Редко но метко ...
 
 
 
#2
07.11.2011 17:58:52
Файл не смотрел.  
Для имен СМЕЩ() лучше - короче :)  
Летучесть роли не играет.  
 
Ссылки нет, привожу текст "лекции":  
ZVI. О летучести имен и УФ  
Мы часто неосознанно избегаем использования летучих функций в формулах имен, в частности, в формулах динамических диапазонов для выпадающих списков. При этом формулы становятся длинными и плохо читаемыми. Но дело в том, что опасения насчет пересчета формул таких имен при каждом пересчете формул листа (т.е общего замедления Excel) лишены оснований.  
Формула динамического диапазона, на основании которой строится выпадающий список, не пересчитывается при пересчете листа, независимо от того, используются в ней летучие функции или нет. Пересчет формул имен происходит только при попытке выбора значения из выпадающего списка.  
А вот формула ячейки, которая ссылается на имя с летучими функциями, действительно сама может стать летучей, но только если в формуле этой ячейки срабатывают условия для обращения к имени.  
Например, если имя ЭтоДата имеет в RefersTo летучую формулу =СЕГОДНЯ()  
то формула ячейки =ЕСЛИ(A1=1; ЭтоДата) станет летучей, только если A1=1  
Раз уж затронул эту тему, напомню, что формулы условного форматирования в ячейках видимой части экрана пересчитываются при любом изменении ячеек этой (и только этой) видимой части, или даже при перерисовке видимой части экрана от навигации. Поэтому и имена, на которые ссылаются формулы условного форматирования, также пересчитываются. Это нужно учитывать, но не бояться :-)  
Вывод: в формулах именованных диапазонов для выпадающих списков можно и полезно использовать летучие функции.
 
 
 
#3
07.11.2011 18:03:54
{quote}{login=vikttur}{date=07.11.2011 05:58}{thema=}{post}  
Для имен СМЕЩ() лучше - короче :)  
Летучесть роли не играет.  
{/post}{/quote}  
 
И это говорит наиглавнейший противник летучих формул ). Куда катится планета ? ))  
Так есть все таки разница в предложенных вариантах кроме количества букав ?
Редко но метко ...
 
 
 
#4
07.11.2011 18:09:40
Контрольный:    
=СМЕЩ(Лист1!$B$2;;;ПОИСКПОЗ("яя";Лист1!$B:$B;1)-1)  
Мне так больше нравится :)
 
 
 
#5
07.11.2011 18:30:27
{quote}{login=vikttur}{date=07.11.2011 06:09}{thema=}{post}Контрольный:    
{/post}{/quote}  
 
Спасибо vikttur.  
Может еще есть мнения у старожилов и не только ?
Редко но метко ...
 
 
 
#6
07.11.2011 18:44:20
Чаще всего пользователи работают с такими объемами данных что применение "летучих" функций не сильно сказывается, но вот когда из этого уже вырастает проблема - нужно думать об оптимизации обработки и использовании макросов, а не формул.  
ЗЫ мы сталкиваемся на форуме уже с критичными объемами чаще чем в реальной жизни так, как люди начинают искать решение проблем обработки больших массивов:)  
ЗЫ 2 и даже если формулы работают медленно - предпочитаю попить чайку, но не делать работу ручками:)
 
 
 
#7
07.11.2011 18:56:58
Сталкивался с тем, что СМЕЩ - единственный вариант. Была тема (не смог с ходу найти), там надо было просмотреть ряд частичных сумм строки и определить, сумма до какого элемента не превосходит заданную, типа, на сколько месяцев хватит товара. Так вот, комбинация типа  
{ПОИСКПОЗ(...;СУММ(A1:ИНДЕКС(А1:А12;;СТРОКА(2:12)))}  
не работает, а так работает:  
{ПОИСКПОЗ(...;ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ(A1;;;(СТРОКА(1:12)))}
 
 
 
#8
07.11.2011 18:57:45
Игорь, полностью разделяю Вашу позицию, чем быть антагонистом "массивной летучести", лучше попить чайку или с девчонками посплетничать.  
:))  
 
-----  
12350
"..Сладку ягоду рвали вместе, горьку ягоду я одна."
 
 
 
#9
07.11.2011 18:59:02
В общем, поиском по "ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ(" довольно много тем находится.
 
 
 
#10
07.11.2011 19:02:17
{quote}{login=Казанский}{date=07.11.2011 06:59}{thema=}{post}В общем, поиском по "ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ(" довольно много тем находится.{/post}{/quote}Просто ПРОМЕЖУТОЧНЫЕ.ИТОГИ и ИНДЕКСом не работают...Да и СМЕЩение еще правильно нужно задать...
 
 
 
#11
07.11.2011 19:06:35
Народ, а почему у меня формула vikttur не работает (((
 
 
 
#12
07.11.2011 19:09:59
Она для буковков, а не цифирьков.
 
 
 
#13
07.11.2011 19:19:22
спасибо, буду знать
 
 
 
#14
07.11.2011 21:04:26
{quote}{login=Владимир}{date=07.11.2011 06:57}{thema=}{post}...чем быть антагонистом "массивной летучести", лучше попить чайку или с девчонками посплетничать.  
 
Я не антагонист. И чай люблю, и с девчонками :)  
 
Любил применять СМЕЩ(). Когда узнал ДВССЫЛ() - тоже очень понравилась.  
Но... Чем дальше в лес, тем хуже компас :)  
Хорошая вещь оптимизация - и подумать, и не навредить.  
 
Вот думал, что СМЕЩ() летуча везде, старался в именах ее избегать, благо ИНДЕКС() в помощь. Но со СМЕЩ() оптимальнее. Теперь умный, знаю :)  
Хотя вряд ли сильно умный - ведь сколько еще не знаю!
 
 
 
#15
07.11.2011 21:09:35
{quote}{login=Igor67}{date=07.11.2011 06:44}{thema=}{post}Чаще всего пользователи работают с такими объемами данных что применение "летучих" функций не сильно сказывается...{/post}{/quote}  
Тут не согласен. Большие таблицы и с формулами нормально работают. На форум часто выкладываются только примеры с небольшим объемом и обрезанными таблицами. На самом деле (иногда это видно по структуре данных) таблицы "мощные" и применение в них "массивных" формул (а можно же столбец-другой добавить) - вред.
 
 
 
#16
07.11.2011 22:14:03
Ещё одна формула динамического диапазона, "для трудоголиков" :-)  
Пример:  
=ИНДЕКС($C$1:$C$5;2):ИНДЕКС($C$1:$C$5;ЧСТРОК($C$1:$C$5)-1)  
Она включает диапазон столбца таблицы с заголовком и строкой (ячейкой) итогов (которые могут быть и пустыми),  
но возвращает диапазон только внутренних ячеек таблицы, без шапки и обуви.  
Минусы формулы:  
- чуть длиннее;  
- чтобы добавить новую запись, надо (сделав неимоверное усилие) выделить ячейку строки итогов и ... вставить новую строку.  
Плюсы формулы (естественно, не летуча):  
+ при вставке новой строки автоматом сохраняется форматирование;  
+ можно удалить даже первую (после заголовка) строку;  
+ формулу (или имя формулы) можно запихнуть в итоговую ячейку внутри СУММ() или, например, ПРОМЕЖУТОЧНЫЕ.ИТОГИ(...),    
и спокойно добавлять/удалять записи таблицы.
 
 
 
Страницы: 1
Читают тему
Наверх