Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Формулы, которые автоматически распознают размер диапазона - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Формулы, которые автоматически распознают размер диапазона (Формулы/Formulas)
Формулы, которые автоматически распознают размер диапазона
Aleksio Дата: Вторник, 04.10.2016, 00:10 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 18
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Приветствую! Вопрос по теоретической части. Оптимизирую свои формулы, чтобы не замедляли работу файла и считали быстрее...

Нашёл в справке Microsoft такую информацию:
Цитата
Многие встроенные функции Excel (SUM, SUMIF) эффективно вычисляют ссылки на столбцы целиком, поскольку они автоматически распознают последнюю использованную строку в столбце. Тем не менее, функции вычисления массивов, такие как SUMPRODUCT, либо не могут обрабатывать ссылки на столбцы целиком, либо вычисляют все ячейки в столбце.

Пользовательские функции не поддерживают автоматическое распознавание последней использованной строки в столбце и, соответственно, зачастую вычисляют ссылки на столбцы целиком неэффективно. Тем не менее, запрограммировать пользовательские функции на распознавание последней использованной строки достаточно легко.

https://msdn.microsoft.com/ru-ru/library/office/ff726673(v=office.14).aspx#xlMinUsedRange

Вопрос: Какие именно формулы способны автоматически распознавать размер диапазона и не обрабатывать лишние пустые строки?
И работает ли это автоматическое распознавание, если ссылка на столбец в другой книге (возможно в зависимости от того, открыты или закрыта другая книга)

Попробовал для эксперимента у себя открыть все книги, на которые вели ссылки - работать стало шустрее, но всё равно заметно тормозило.
Решил изменить ссылки в формулах ВПР и ИНДЕКС(ПОИСПОЗ), которые ссылались на другие книги на весь столбец - на фиксированный диапазон с запасом ячеек, в итоге стало работать гораздо шустрее. Но в вычисляемой книге полно всяких формул с поиском по всему столбцу - при замене на фиксированный диапазон ничего явного не меняется. Отсюда напрашивается вывод, что при ссылках на другие книги - автоматическое распознавание диапазона не работает, но работает в данной книге... Но с другой стороны много всяких файлов, которые ссылаются на другие книги на весь столбец, и при этом ничего не тормозит... Не могу уловить взаимосвязь.

Вот хотелось бы узнать побольше о том, что сказано в справке Microsoft... может быть какие-то формулы умеют распознавать диапазон, и для тех формул, что умеют автоматически распознавать - не ограничивать диапазон, ибо часто неудобно ограничивать, даже с запасом ячеек. Да и к тому же, если формула умеет автоматически распознавать, то по идее заведомо фиксированный диапазон с запасом ячеек будет больше, и обрабатываться будет дольше... Но может я и не прав.
Заранее благодарен за ссылки на эту тему (именно про сравнение преимуществ фиксированных диапазонов с запасом ячеек с диапазоном на столбец), ибо мало что нашёл в Интернете.


Сообщение отредактировал Aleksio - Вторник, 04.10.2016, 00:12
 
Ответить
СообщениеПриветствую! Вопрос по теоретической части. Оптимизирую свои формулы, чтобы не замедляли работу файла и считали быстрее...

Нашёл в справке Microsoft такую информацию:
Цитата
Многие встроенные функции Excel (SUM, SUMIF) эффективно вычисляют ссылки на столбцы целиком, поскольку они автоматически распознают последнюю использованную строку в столбце. Тем не менее, функции вычисления массивов, такие как SUMPRODUCT, либо не могут обрабатывать ссылки на столбцы целиком, либо вычисляют все ячейки в столбце.

Пользовательские функции не поддерживают автоматическое распознавание последней использованной строки в столбце и, соответственно, зачастую вычисляют ссылки на столбцы целиком неэффективно. Тем не менее, запрограммировать пользовательские функции на распознавание последней использованной строки достаточно легко.

https://msdn.microsoft.com/ru-ru/library/office/ff726673(v=office.14).aspx#xlMinUsedRange

Вопрос: Какие именно формулы способны автоматически распознавать размер диапазона и не обрабатывать лишние пустые строки?
И работает ли это автоматическое распознавание, если ссылка на столбец в другой книге (возможно в зависимости от того, открыты или закрыта другая книга)

Попробовал для эксперимента у себя открыть все книги, на которые вели ссылки - работать стало шустрее, но всё равно заметно тормозило.
Решил изменить ссылки в формулах ВПР и ИНДЕКС(ПОИСПОЗ), которые ссылались на другие книги на весь столбец - на фиксированный диапазон с запасом ячеек, в итоге стало работать гораздо шустрее. Но в вычисляемой книге полно всяких формул с поиском по всему столбцу - при замене на фиксированный диапазон ничего явного не меняется. Отсюда напрашивается вывод, что при ссылках на другие книги - автоматическое распознавание диапазона не работает, но работает в данной книге... Но с другой стороны много всяких файлов, которые ссылаются на другие книги на весь столбец, и при этом ничего не тормозит... Не могу уловить взаимосвязь.

Вот хотелось бы узнать побольше о том, что сказано в справке Microsoft... может быть какие-то формулы умеют распознавать диапазон, и для тех формул, что умеют автоматически распознавать - не ограничивать диапазон, ибо часто неудобно ограничивать, даже с запасом ячеек. Да и к тому же, если формула умеет автоматически распознавать, то по идее заведомо фиксированный диапазон с запасом ячеек будет больше, и обрабатываться будет дольше... Но может я и не прав.
Заранее благодарен за ссылки на эту тему (именно про сравнение преимуществ фиксированных диапазонов с запасом ячеек с диапазоном на столбец), ибо мало что нашёл в Интернете.

Автор - Aleksio
Дата добавления - 04.10.2016 в 00:10
Pelena Дата: Вторник, 04.10.2016, 11:09 | Сообщение № 2
Группа: Модераторы
Ранг: Экселист
Сообщений: 9842
Репутация: 2252 ±
Замечаний: 0% ±

Excel 2010 & Mac Excel 2011
Здравствуйте.
Если вопрос чисто теоретический, то вот немного теории про динамические диапазоны


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеЗдравствуйте.
Если вопрос чисто теоретический, то вот немного теории про динамические диапазоны

Автор - Pelena
Дата добавления - 04.10.2016 в 11:09
Aleksio Дата: Среда, 05.10.2016, 00:36 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 18
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Здравствуйте.
Если вопрос чисто теоретический, то вот немного теории про динамические диапазоны

Pelena, спасибо, что откликнулись. Да, вопрос чисто теоретический, но ваш совет весьма сомнительный, даже вредительский, или не по теме...

1) Во многих статьях про оптимизацию работы Excel сказано, что использование умных таблиц не рекомендуется. Ваш совет как раз приведёт к обратному - замедлению работы Excel. Да и исходя из собственного опыта могу сказать тоже самое.

2) Что касается второй части статьи по ссылке - тоже есть большие сомнения, что нагрузив расчёты ещё одной формулой "ПОВТОР" - мы тем самым ускорим вычисления, сжав диапазон... И ещё больше эти сомнения усиливаются, т.к. это не просто прибавление функции к связке индекс-поискпоз, а использование связки индекс-поискпоз-повтор как ссылки на диапазон. Т.е., если используется формула индекс-поискпоз для поиска значения, мы вместо обычной ссылки на диапазон (допустим, на столбец или диапазон с запасом) - используем, например, в формуле индекс-поискпоз, ещё раз индекс-поискпоз-повтор, да ещё и с запасом ячеек, от которого пытались избавиться. Вы действительно думаете, что тем самым вы ускорите процесс вычисления? Я сильно сомневаюсь.

Собственно, а вы сами на практике проверяли то, что советуете?


Сообщение отредактировал Aleksio - Среда, 05.10.2016, 00:49
 
Ответить
Сообщение
Здравствуйте.
Если вопрос чисто теоретический, то вот немного теории про динамические диапазоны

Pelena, спасибо, что откликнулись. Да, вопрос чисто теоретический, но ваш совет весьма сомнительный, даже вредительский, или не по теме...

1) Во многих статьях про оптимизацию работы Excel сказано, что использование умных таблиц не рекомендуется. Ваш совет как раз приведёт к обратному - замедлению работы Excel. Да и исходя из собственного опыта могу сказать тоже самое.

2) Что касается второй части статьи по ссылке - тоже есть большие сомнения, что нагрузив расчёты ещё одной формулой "ПОВТОР" - мы тем самым ускорим вычисления, сжав диапазон... И ещё больше эти сомнения усиливаются, т.к. это не просто прибавление функции к связке индекс-поискпоз, а использование связки индекс-поискпоз-повтор как ссылки на диапазон. Т.е., если используется формула индекс-поискпоз для поиска значения, мы вместо обычной ссылки на диапазон (допустим, на столбец или диапазон с запасом) - используем, например, в формуле индекс-поискпоз, ещё раз индекс-поискпоз-повтор, да ещё и с запасом ячеек, от которого пытались избавиться. Вы действительно думаете, что тем самым вы ускорите процесс вычисления? Я сильно сомневаюсь.

Собственно, а вы сами на практике проверяли то, что советуете?

Автор - Aleksio
Дата добавления - 05.10.2016 в 00:36
_Boroda_ Дата: Среда, 05.10.2016, 07:55 | Сообщение № 4
Группа: Модераторы
Ранг: Экселист
Сообщений: 9348
Репутация: 3922 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Сударь, Вам никто пока не говорил, что Вы невежа? Если нет, то позвольте сообщить Вам об этом.
[p.s.]Да за нашу Леночку я ваще порву нафик! И, уверен, что не только я.


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеСударь, Вам никто пока не говорил, что Вы невежа? Если нет, то позвольте сообщить Вам об этом.
[p.s.]Да за нашу Леночку я ваще порву нафик! И, уверен, что не только я.

Автор - _Boroda_
Дата добавления - 05.10.2016 в 07:55
китин Дата: Среда, 05.10.2016, 08:05 | Сообщение № 5
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3413
Репутация: 543 ±
Замечаний: 0% ±

Excel 2007;Excel 2010
Собственно, а вы сами на практике проверяли то, что советуете?

а самому проверить религия не позволяет? или только троллить умеем?
Да за нашу Леночку я ваще порву нафик!


Не судите строго:я пытаюсь научиться
ЯД 41001877306852/WM R249698041931; Z239672726538
 
Ответить
Сообщение
Собственно, а вы сами на практике проверяли то, что советуете?

а самому проверить религия не позволяет? или только троллить умеем?
Да за нашу Леночку я ваще порву нафик!

Автор - китин
Дата добавления - 05.10.2016 в 08:05
buchlotnik Дата: Среда, 05.10.2016, 08:24 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 2049
Репутация: 613 ±
Замечаний: 0% ±

2010, 2013, 2016 RUS / ENG
Цитата
а вы сами на практике проверяли то, что советуете?
а вы как думаете? Коль неуверены - попробуйте, это, говорят, полезно.
Цитата
Да за нашу Леночку я ваще порву нафик!


платная помощь:
ЯД: 410012595572239; WM: 311017577133
buchlotnik@mail.ru
 
Ответить
Сообщение
Цитата
а вы сами на практике проверяли то, что советуете?
а вы как думаете? Коль неуверены - попробуйте, это, говорят, полезно.
Цитата
Да за нашу Леночку я ваще порву нафик!

Автор - buchlotnik
Дата добавления - 05.10.2016 в 08:24
Pelena Дата: Среда, 05.10.2016, 09:02 | Сообщение № 7
Группа: Модераторы
Ранг: Экселист
Сообщений: 9842
Репутация: 2252 ±
Замечаний: 0% ±

Excel 2010 & Mac Excel 2011
вы сами на практике проверяли то, что советуете?

Да зачем? Вопрос ведь
чисто теоретический

[p.s.]Мальчики,
[/p.s.]


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
Сообщение
вы сами на практике проверяли то, что советуете?

Да зачем? Вопрос ведь
чисто теоретический

[p.s.]Мальчики,
[/p.s.]

Автор - Pelena
Дата добавления - 05.10.2016 в 09:02
SLAVICK Дата: Среда, 05.10.2016, 09:07 | Сообщение № 8
Группа: Модераторы
Ранг: Старожил
Сообщений: 1834
Репутация: 613 ±
Замечаний: 0% ±

2007,2010,2013,2016
[offtop]Однако - интересно получается - просите совета - получаете - не проверив строите свои догадки.
При этом "наезжая" на человека, который Вам ничего не должен.
Если бы это был Ваш подчиненный - тогда пожалуйста, а так Вы зарплату нам не платите, и требовать чего - то не вправе.
Если есть сомнения, или несогласие - приводите живые примеры что к чему, а голословно утверждать
ваш совет весьма сомнительный, даже вредительский, или не по теме..

это уж извольте...
Да и исходя из собственного опыта могу сказать тоже самое.
- я уверен на 100% что у Николая (Автора статьи) и у Лены опыта поболее будет. Что касается Николая - так он вообще удостоен награды MVP.
А теперь скажите чем можете похвастаться Вы?
[/offtop]
Теперь по теме:
Есть поговорка: "Заставь дурака Богу молится он и лоб расшибет"
Это я к тому что все нужно использовать в меру, и с умом.
1) Во многих статьях про оптимизацию работы Excel сказано, что использование умных таблиц не рекомендуется.

Старая, но хорошая статья по оптимизации вычислений в excel есть тут:
Цитата
https://msdn.microsoft.com/ru-ru/library/office/ff726673(v=office.14).aspx#xlAllowExtraData

А теперь из личного опыта - если использовать Умные таблицы в качестве больших массивов, и производить в них основные вычисления - да.
Если использовать их в качестве таблиц параметров - нет + очень удобно - можно на одном листе разместить несколько десятков таблиц, и при их перемещении не боятся потери диапазона. + очень удобно обращаться к ним из макросов.
2) Что касается второй части статьи по ссылке - тоже есть большие сомнения, что нагрузив расчёты ещё одной формулой "ПОВТОР"

Есть "тяжелые" формулы - так вот количество ячеек для них имеет большое значение(например формулы массива, суммпроизв...) и добавление поискпоз + повтор однозначно может ускорить процесс основного вычисления. А Повтор - вообще мало нагружает систему.
Собственно, а вы сами на практике проверяли то, что советуете?

Мы то проверяли - а ВЫ?
Я сильно сомневаюсь.

И солидарен со всеми выше:
Да за нашу Леночку я ваще порву нафик


Иногда все проще чем кажется с первого взгляда.
 
Ответить
Сообщение[offtop]Однако - интересно получается - просите совета - получаете - не проверив строите свои догадки.
При этом "наезжая" на человека, который Вам ничего не должен.
Если бы это был Ваш подчиненный - тогда пожалуйста, а так Вы зарплату нам не платите, и требовать чего - то не вправе.
Если есть сомнения, или несогласие - приводите живые примеры что к чему, а голословно утверждать
ваш совет весьма сомнительный, даже вредительский, или не по теме..

это уж извольте...
Да и исходя из собственного опыта могу сказать тоже самое.
- я уверен на 100% что у Николая (Автора статьи) и у Лены опыта поболее будет. Что касается Николая - так он вообще удостоен награды MVP.
А теперь скажите чем можете похвастаться Вы?
[/offtop]
Теперь по теме:
Есть поговорка: "Заставь дурака Богу молится он и лоб расшибет"
Это я к тому что все нужно использовать в меру, и с умом.
1) Во многих статьях про оптимизацию работы Excel сказано, что использование умных таблиц не рекомендуется.

Старая, но хорошая статья по оптимизации вычислений в excel есть тут:
Цитата
https://msdn.microsoft.com/ru-ru/library/office/ff726673(v=office.14).aspx#xlAllowExtraData

А теперь из личного опыта - если использовать Умные таблицы в качестве больших массивов, и производить в них основные вычисления - да.
Если использовать их в качестве таблиц параметров - нет + очень удобно - можно на одном листе разместить несколько десятков таблиц, и при их перемещении не боятся потери диапазона. + очень удобно обращаться к ним из макросов.
2) Что касается второй части статьи по ссылке - тоже есть большие сомнения, что нагрузив расчёты ещё одной формулой "ПОВТОР"

Есть "тяжелые" формулы - так вот количество ячеек для них имеет большое значение(например формулы массива, суммпроизв...) и добавление поискпоз + повтор однозначно может ускорить процесс основного вычисления. А Повтор - вообще мало нагружает систему.
Собственно, а вы сами на практике проверяли то, что советуете?

Мы то проверяли - а ВЫ?
Я сильно сомневаюсь.

И солидарен со всеми выше:
Да за нашу Леночку я ваще порву нафик

Автор - SLAVICK
Дата добавления - 05.10.2016 в 09:07
Aleksio Дата: Четверг, 06.10.2016, 00:22 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 18
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Если кого-то оскорбил или кому-то нагрубил - извиняюсь. Но не ясно отчего такая реакция.
Сегодня из интереса попробовал ранее предложенные два способа на практике, а вдруг всё-таки я ошибаюсь... Но, как и ожидалось, файл от этого только стал больше тормозить.

Собственно возвращаясь к теме вопроса.
1) Какие именно формулы способны автоматически распознавать размер диапазона и не обрабатывать лишние пустые строки, как сказано в справке Microsoft?
2) Работает ли это, если ссылка на другую книгу
3) работает ли, если другая книга закрыта

На это так и не получил ответа. Только какая-то куча негатива, обозвали ни за что ни про что, по обсуждали, навесили ярлык... Люди, отчего вы такие озлобленные? Если это вызвало моё сомнение, и, как оказалось, оправданное, то извиняюсь. Всем добра и мира.
 
Ответить
СообщениеЕсли кого-то оскорбил или кому-то нагрубил - извиняюсь. Но не ясно отчего такая реакция.
Сегодня из интереса попробовал ранее предложенные два способа на практике, а вдруг всё-таки я ошибаюсь... Но, как и ожидалось, файл от этого только стал больше тормозить.

Собственно возвращаясь к теме вопроса.
1) Какие именно формулы способны автоматически распознавать размер диапазона и не обрабатывать лишние пустые строки, как сказано в справке Microsoft?
2) Работает ли это, если ссылка на другую книгу
3) работает ли, если другая книга закрыта

На это так и не получил ответа. Только какая-то куча негатива, обозвали ни за что ни про что, по обсуждали, навесили ярлык... Люди, отчего вы такие озлобленные? Если это вызвало моё сомнение, и, как оказалось, оправданное, то извиняюсь. Всем добра и мира.

Автор - Aleksio
Дата добавления - 06.10.2016 в 00:22
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Формулы, которые автоматически распознают размер диапазона (Формулы/Formulas)
Страница 1 из 11
Поиск:

Яндекс цитирования
© 2010-2016 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!