Приветствую! Вопрос по теоретической части. Оптимизирую свои формулы, чтобы не замедляли работу файла и считали быстрее...
Нашёл в справке Microsoft такую информацию:
Цитата
Многие встроенные функции Excel (SUM, SUMIF) эффективно вычисляют ссылки на столбцы целиком, поскольку они автоматически распознают последнюю использованную строку в столбце. Тем не менее, функции вычисления массивов, такие как SUMPRODUCT, либо не могут обрабатывать ссылки на столбцы целиком, либо вычисляют все ячейки в столбце.
Пользовательские функции не поддерживают автоматическое распознавание последней использованной строки в столбце и, соответственно, зачастую вычисляют ссылки на столбцы целиком неэффективно. Тем не менее, запрограммировать пользовательские функции на распознавание последней использованной строки достаточно легко.
Вопрос: Какие именно формулы способны автоматически распознавать размер диапазона и не обрабатывать лишние пустые строки? И работает ли это автоматическое распознавание, если ссылка на столбец в другой книге (возможно в зависимости от того, открыты или закрыта другая книга)
Попробовал для эксперимента у себя открыть все книги, на которые вели ссылки - работать стало шустрее, но всё равно заметно тормозило. Решил изменить ссылки в формулах ВПР и ИНДЕКС(ПОИСПОЗ), которые ссылались на другие книги на весь столбец - на фиксированный диапазон с запасом ячеек, в итоге стало работать гораздо шустрее. Но в вычисляемой книге полно всяких формул с поиском по всему столбцу - при замене на фиксированный диапазон ничего явного не меняется. Отсюда напрашивается вывод, что при ссылках на другие книги - автоматическое распознавание диапазона не работает, но работает в данной книге... Но с другой стороны много всяких файлов, которые ссылаются на другие книги на весь столбец, и при этом ничего не тормозит... Не могу уловить взаимосвязь.
Вот хотелось бы узнать побольше о том, что сказано в справке Microsoft... может быть какие-то формулы умеют распознавать диапазон, и для тех формул, что умеют автоматически распознавать - не ограничивать диапазон, ибо часто неудобно ограничивать, даже с запасом ячеек. Да и к тому же, если формула умеет автоматически распознавать, то по идее заведомо фиксированный диапазон с запасом ячеек будет больше, и обрабатываться будет дольше... Но может я и не прав. Заранее благодарен за ссылки на эту тему (именно про сравнение преимуществ фиксированных диапазонов с запасом ячеек с диапазоном на столбец), ибо мало что нашёл в Интернете.
Приветствую! Вопрос по теоретической части. Оптимизирую свои формулы, чтобы не замедляли работу файла и считали быстрее...
Нашёл в справке Microsoft такую информацию:
Цитата
Многие встроенные функции Excel (SUM, SUMIF) эффективно вычисляют ссылки на столбцы целиком, поскольку они автоматически распознают последнюю использованную строку в столбце. Тем не менее, функции вычисления массивов, такие как SUMPRODUCT, либо не могут обрабатывать ссылки на столбцы целиком, либо вычисляют все ячейки в столбце.
Пользовательские функции не поддерживают автоматическое распознавание последней использованной строки в столбце и, соответственно, зачастую вычисляют ссылки на столбцы целиком неэффективно. Тем не менее, запрограммировать пользовательские функции на распознавание последней использованной строки достаточно легко.
Вопрос: Какие именно формулы способны автоматически распознавать размер диапазона и не обрабатывать лишние пустые строки? И работает ли это автоматическое распознавание, если ссылка на столбец в другой книге (возможно в зависимости от того, открыты или закрыта другая книга)
Попробовал для эксперимента у себя открыть все книги, на которые вели ссылки - работать стало шустрее, но всё равно заметно тормозило. Решил изменить ссылки в формулах ВПР и ИНДЕКС(ПОИСПОЗ), которые ссылались на другие книги на весь столбец - на фиксированный диапазон с запасом ячеек, в итоге стало работать гораздо шустрее. Но в вычисляемой книге полно всяких формул с поиском по всему столбцу - при замене на фиксированный диапазон ничего явного не меняется. Отсюда напрашивается вывод, что при ссылках на другие книги - автоматическое распознавание диапазона не работает, но работает в данной книге... Но с другой стороны много всяких файлов, которые ссылаются на другие книги на весь столбец, и при этом ничего не тормозит... Не могу уловить взаимосвязь.
Вот хотелось бы узнать побольше о том, что сказано в справке Microsoft... может быть какие-то формулы умеют распознавать диапазон, и для тех формул, что умеют автоматически распознавать - не ограничивать диапазон, ибо часто неудобно ограничивать, даже с запасом ячеек. Да и к тому же, если формула умеет автоматически распознавать, то по идее заведомо фиксированный диапазон с запасом ячеек будет больше, и обрабатываться будет дольше... Но может я и не прав. Заранее благодарен за ссылки на эту тему (именно про сравнение преимуществ фиксированных диапазонов с запасом ячеек с диапазоном на столбец), ибо мало что нашёл в Интернете.Aleksio
Сообщение отредактировал Aleksio - Вторник, 04.10.2016, 00:12
Здравствуйте. Если вопрос чисто теоретический, то вот немного теории про динамические диапазоны
Pelena, спасибо, что откликнулись. Да, вопрос чисто теоретический, но ваш совет весьма сомнительный, даже вредительский, или не по теме...
1) Во многих статьях про оптимизацию работы Excel сказано, что использование умных таблиц не рекомендуется. Ваш совет как раз приведёт к обратному - замедлению работы Excel. Да и исходя из собственного опыта могу сказать тоже самое.
2) Что касается второй части статьи по ссылке - тоже есть большие сомнения, что нагрузив расчёты ещё одной формулой "ПОВТОР" - мы тем самым ускорим вычисления, сжав диапазон... И ещё больше эти сомнения усиливаются, т.к. это не просто прибавление функции к связке индекс-поискпоз, а использование связки индекс-поискпоз-повтор как ссылки на диапазон. Т.е., если используется формула индекс-поискпоз для поиска значения, мы вместо обычной ссылки на диапазон (допустим, на столбец или диапазон с запасом) - используем, например, в формуле индекс-поискпоз, ещё раз индекс-поискпоз-повтор, да ещё и с запасом ячеек, от которого пытались избавиться. Вы действительно думаете, что тем самым вы ускорите процесс вычисления? Я сильно сомневаюсь.
Собственно, а вы сами на практике проверяли то, что советуете?
Здравствуйте. Если вопрос чисто теоретический, то вот немного теории про динамические диапазоны
Pelena, спасибо, что откликнулись. Да, вопрос чисто теоретический, но ваш совет весьма сомнительный, даже вредительский, или не по теме...
1) Во многих статьях про оптимизацию работы Excel сказано, что использование умных таблиц не рекомендуется. Ваш совет как раз приведёт к обратному - замедлению работы Excel. Да и исходя из собственного опыта могу сказать тоже самое.
2) Что касается второй части статьи по ссылке - тоже есть большие сомнения, что нагрузив расчёты ещё одной формулой "ПОВТОР" - мы тем самым ускорим вычисления, сжав диапазон... И ещё больше эти сомнения усиливаются, т.к. это не просто прибавление функции к связке индекс-поискпоз, а использование связки индекс-поискпоз-повтор как ссылки на диапазон. Т.е., если используется формула индекс-поискпоз для поиска значения, мы вместо обычной ссылки на диапазон (допустим, на столбец или диапазон с запасом) - используем, например, в формуле индекс-поискпоз, ещё раз индекс-поискпоз-повтор, да ещё и с запасом ячеек, от которого пытались избавиться. Вы действительно думаете, что тем самым вы ускорите процесс вычисления? Я сильно сомневаюсь.
Собственно, а вы сами на практике проверяли то, что советуете?Aleksio
Сообщение отредактировал Aleksio - Среда, 05.10.2016, 00:49
Сударь, Вам никто пока не говорил, что Вы невежа? Если нет, то позвольте сообщить Вам об этом. [p.s.]Да за нашу Леночку я ваще порву нафик! И, уверен, что не только я.
Сударь, Вам никто пока не говорил, что Вы невежа? Если нет, то позвольте сообщить Вам об этом. [p.s.]Да за нашу Леночку я ваще порву нафик! И, уверен, что не только я._Boroda_
[offtop]Однако - интересно получается - просите совета - получаете - не проверив строите свои догадки. При этом "наезжая" на человека, который Вам ничего не должен. Если бы это был Ваш подчиненный - тогда пожалуйста, а так Вы зарплату нам не платите, и требовать чего - то не вправе. Если есть сомнения, или несогласие - приводите живые примеры что к чему, а голословно утверждать
Да и исходя из собственного опыта могу сказать тоже самое.
- я уверен на 100% что у Николая (Автора статьи) и у Лены опыта поболее будет. Что касается Николая - так он вообще удостоен награды MVP. А теперь скажите чем можете похвастаться Вы? [/offtop] Теперь по теме: Есть поговорка: "Заставь дурака Богу молится он и лоб расшибет" Это я к тому что все нужно использовать в меру, и с умом.
А теперь из личного опыта - если использовать Умные таблицы в качестве больших массивов, и производить в них основные вычисления - да. Если использовать их в качестве таблиц параметров - нет + очень удобно - можно на одном листе разместить несколько десятков таблиц, и при их перемещении не боятся потери диапазона. + очень удобно обращаться к ним из макросов.
2) Что касается второй части статьи по ссылке - тоже есть большие сомнения, что нагрузив расчёты ещё одной формулой "ПОВТОР"
Есть "тяжелые" формулы - так вот количество ячеек для них имеет большое значение(например формулы массива, суммпроизв...) и добавление поискпоз + повтор однозначно может ускорить процесс основного вычисления. А Повтор - вообще мало нагружает систему.
[offtop]Однако - интересно получается - просите совета - получаете - не проверив строите свои догадки. При этом "наезжая" на человека, который Вам ничего не должен. Если бы это был Ваш подчиненный - тогда пожалуйста, а так Вы зарплату нам не платите, и требовать чего - то не вправе. Если есть сомнения, или несогласие - приводите живые примеры что к чему, а голословно утверждать
Да и исходя из собственного опыта могу сказать тоже самое.
- я уверен на 100% что у Николая (Автора статьи) и у Лены опыта поболее будет. Что касается Николая - так он вообще удостоен награды MVP. А теперь скажите чем можете похвастаться Вы? [/offtop] Теперь по теме: Есть поговорка: "Заставь дурака Богу молится он и лоб расшибет" Это я к тому что все нужно использовать в меру, и с умом.
А теперь из личного опыта - если использовать Умные таблицы в качестве больших массивов, и производить в них основные вычисления - да. Если использовать их в качестве таблиц параметров - нет + очень удобно - можно на одном листе разместить несколько десятков таблиц, и при их перемещении не боятся потери диапазона. + очень удобно обращаться к ним из макросов.
2) Что касается второй части статьи по ссылке - тоже есть большие сомнения, что нагрузив расчёты ещё одной формулой "ПОВТОР"
Есть "тяжелые" формулы - так вот количество ячеек для них имеет большое значение(например формулы массива, суммпроизв...) и добавление поискпоз + повтор однозначно может ускорить процесс основного вычисления. А Повтор - вообще мало нагружает систему.
Если кого-то оскорбил или кому-то нагрубил - извиняюсь. Но не ясно отчего такая реакция. Сегодня из интереса попробовал ранее предложенные два способа на практике, а вдруг всё-таки я ошибаюсь... Но, как и ожидалось, файл от этого только стал больше тормозить.
Собственно возвращаясь к теме вопроса. 1) Какие именно формулы способны автоматически распознавать размер диапазона и не обрабатывать лишние пустые строки, как сказано в справке Microsoft? 2) Работает ли это, если ссылка на другую книгу 3) работает ли, если другая книга закрыта
На это так и не получил ответа. Только какая-то куча негатива, обозвали ни за что ни про что, по обсуждали, навесили ярлык... Люди, отчего вы такие озлобленные? Если это вызвало моё сомнение, и, как оказалось, оправданное, то извиняюсь. Всем добра и мира.
Если кого-то оскорбил или кому-то нагрубил - извиняюсь. Но не ясно отчего такая реакция. Сегодня из интереса попробовал ранее предложенные два способа на практике, а вдруг всё-таки я ошибаюсь... Но, как и ожидалось, файл от этого только стал больше тормозить.
Собственно возвращаясь к теме вопроса. 1) Какие именно формулы способны автоматически распознавать размер диапазона и не обрабатывать лишние пустые строки, как сказано в справке Microsoft? 2) Работает ли это, если ссылка на другую книгу 3) работает ли, если другая книга закрыта
На это так и не получил ответа. Только какая-то куча негатива, обозвали ни за что ни про что, по обсуждали, навесили ярлык... Люди, отчего вы такие озлобленные? Если это вызвало моё сомнение, и, как оказалось, оправданное, то извиняюсь. Всем добра и мира.Aleksio