Ускорение работы — часть вторая
Давно ничего не писал, был занят на новой работе. Попытаюсь теперь реабилитироваться, период адаптации прошел, самые срочные вопросы решены, так что можно приняться за то, что я люблю больше всего — за Excel. Итак, сегодняшняя тема — ускорение работы.
Ранее я уже писал об этом, но сейчас я работаю с гораздо большими массивами данных и приходится углубляться в некоторые тонкости. В принципе то, о чем я сейчас буду писать, касается работы только с большими объемами информации. Естественно, эти же методы годятся, если у вас не очень сильный или просто старенький компьютер.
Итак, для начала надо знать, что Excel пересчитывает все формулы на листе (а точнее, в книге) при изменении листа. Когда таблицы несложные, это незаметно. Но вот у меня сейчас этих таблиц достаточно много и во многих есть формулы массива , плюс подстановочные таблицы используют некоторые сложные функции. Немного изменишь формулу в одной ячейке и на тебе, Excel на пару секунд отключается из этого мира. Какой из этого следует вывод? Надо или отключить этот пересчет или сделать формулы попроще.
Чтобы отключить пересчет нужно зайти на вкладку «Формулы», там есть группа «Вычисление», нужно задать установить в Параметрах вычислений режим «Вручную». Разумеется, это надо делать только на период, пока таблица конструируется, т.е. когда формулы изменяются. Потом уже можно переключиться в автоматический режим, когда вы уже вовсю пользуетесь своими таблицами.
Насчет формул попроще у меня следующие методы:
- Стараюсь пореже использовать формулы массива (тема, которую я раскрыл в своем самоучителе ). Они тяжеловесы и жрут много памяти. Поэтому я постарался их оставить только там, где без них никак, заменив простыми, хотя и двух-трехэтажными формулами.
- С удивлением обнаружил, что условное форматирование тоже активный потребитель памяти. Оставил только на таблицах проверки данных (это один из приемов, все хочу его расписать, но никак не могу воткнуть в какую-нибудь общую тему).
- Есть таблицы, которые вытаскиваются из каких-либо отчетов. Т.е., получается много ссылок ,которые подгружают данные при каждом открытии. Чтобы сильно не грузиться, взял старые отчеты, объединил в одном файле и тем самым убрал много формул. Убрал путем замены формул просто данными ( Спецвставка — значения).Есть риск, что старые отчеты все-таки будут где-нибудь изменены, а у меня тогда будут устаревшие данные. Но на этот случай подготовил через сводные таблицы контрольные операции. Их можно запускать раз в месяц на предмет соответствия данных, а пока что пойдет. В сумме должна быть экономия времени нехилая.
- Постарался не задействовать в формуле слишком большие диапазоны. Например, для сводных таблиц я раньше указывал целые столбцы, но теперь приходится указывать размеры адекватные (если объем таблицы данных не меняется, то точные).
- Использую более простые формулы. Навскидку: вместо ВПР иногда применяю ИНДЕКС. Причина проста — ИНДЕКС просто вытаскивает данные, а ВПР обрабатывает весь массив.
«Глаза боятся, а руки делают»
P.S. Понравилась статья? Подпишитесь на рассылку в правой части страницы (Бесплатный курс «Топ-10 инструментов Excel») и будьте в курсе новых событий.
Ускорение работы — часть вторая : 4 комментария
Здравствуйте! У меня вопрос. Есть столбец ключевых слов. И в нотепаде и в блокноте. Каждая строка в кавычках. При вставлении в Эксэль кавычки пропадают. ЧТо сделать чтоб не пропадали?
День добрый.
Возможно, есть более верный вариант, но я в таких случаях просто добавляю кавычки по новой.
Самое простое — =СЦЕПИТЬ(«»»»;A1;»»»»), где A1 — ваша строка.
И здесь пошумлю
Итак, для начала надо знать, что Excel пересчитывает все формулы на листе (а точнее, в книге) при изменении листа
Нет! Формула на листе пересчитываются:
а) если изменяются входные данные именно этой формулы;
б) если в формуле есть летучие (волатильные функции), в этом случае — пересчет при любых изменениях на листе.
Летучих немного: СМЕЩ, ТДАТА, СЕГОДНЯ, ИНФОРМ, ДВССЫЛ, СЛЧИС, СЛУЧМЕЖДУ, СМЕЩ… еще пара-тройка.
«Временно летучие» — формулы, в которых происходит вычисление диапазонов. Например:
=СУММ(B2:ИНДЕКС(B2:B100;17))
Эти пересчитываются при открытии книги и , естественно, и при изменении исходных данных.
Топ 20 самых важных формул MS Excel
Microsoft Excel — один из самых популярных инструментов анализа данных в мире. Все больше фирмы используют это программное обеспечение и много людей ежедневно пользуются Excel. Однако, по моему опыту, только немногие из них в полной мере используют возможности, которые может предложить эта программа. Я представляю Вам мой список из топ 20 самых полезных формул Excel, которые каждый должен использовать для повышения эффективности. Функции Excel также очень рекомендуются для вычислений, так как они могут значительно уменьшить количество ошибок. И последнее, но не менее важное: изучение этих функций — прекрасная возможность улучшить Ваши знания Excel и стать уверенным пользователем MS Excel.
Table of Contents
1. Sum
«Sum» — это, наверное, самая простая, но и самая важная функция Exceл. Вы можете использовать эта формула для вычисления суммы диапазона ячеек.
В этом примере первая формула суммирует ячейки от A1 до A10. Вторая суммирует только ячейки A1 и A10. Вы можете попробовать с ним и попытаться использовать разные диапазоны, чтобы привыкнуть к этой функций. Обратите внимание, что вы также можете использовать числа внутри этой функции вместо ссылок.
2. Average
Еще одна очень важная функция. Как следует из названия, эта формула оценивает среднее число из диапазона ячеек. Предположим, что мы имеем диапазон чисел в столбце A.
Это вернет среднее значение ячеек от A1 до A10.
Другая очень необходимая формула. Она относительно простая в использовании, но и очень мощная. Вы можете использовать эту функцию, чтобы проверить, является ли данный оператор истинным или ложным, и чтобы вернуть указанное число.
Здесь формула проверит если продажи в ячейке «A2» более 100 €. Если это заявление верно, формула возвращает «да». Если продажи ниже 100 евро, формула возвращает «нет».
4. Sumif
Sumif — еще одна очень важная формула Excel. Формула будет суммировать числа, если выполняется определенное условие.
Давайте рассмотрим приведенный выше пример. У нас есть торговые агенты, которые регистрируют определенное количество продаж. Мы хотим узнать общее количество продаж для каждого агента. Функция = SUMIF (A1: A20, A2, B1: B20) суммирует значения ячеек B1: B20, которые соответствуют тексту в ячейке A2 (в случае «Ивайло»). Парень зарегистрировал общий объем продаж 325 €. Мы можем использовать формулу для расчета оборота других агентов.
5. Countif
Countif — очень полезная функция, которая работает как sumif. Он суммируется при определенном условии. Разница между sum и count состоит в том, что функция count суммирует количество ячеек, где определенное условие истинно. Напротив, суммирующая функция суммирует значения внутри ячеек.
Формула в приведенном выше примере проверяет, сколько продаж были выше чем 100 евро и возвращает количество продаж. Вы также можете включить более одного критерия, используя countif для этой цели.
PS: Если вы не уверены в синтаксисе функции, вы всегда можете использовать функцию «insert function» в Excel (просто нажмите знак функции в левой части функциональной панели). В следующем примере мы продемонстрируем, как это работает.
6. Counta
Эта формула подсчитывает количество ячеек определенного диапазона, которые не являются пустыми. Это очень полезно, когда у вас есть тысячи строк, и вы хотите знать, где все это кончается.
Это даст вам количество строк, которые содержат значение, текст или любой другой символ.
7. Vlookup
Вероятно, самая мощная функция Excel. Очень полезно, когда вам приходится иметь дело с более чем одной таблицей, разными листами или даже другого файла. Vlookup позволяет вам извлекать данные из одной таблицы в другую.
Продолжим этот пример, добавив еще один столбец «А» с номерами клиентов.
Обратите внимание, что есть информация о продажах и номерах клиентов, но имена клиентов отсутствуют. Однако у нас есть еще один лист Excel (Sheet2) с именами клиентов и номерами клиентов.
Вместо того, чтобы тратить свое время, чтобы посмотреть с Sheet1 на Sheet2 и искать совпадение вручную для каждой отдельной строки, мы можем использовать следующую формулу:
Эта формула ищет значение в ячейке A2 (112 в нашем случае) в листе2, столбцы A & B (знаки «$» означают, что мы всегда хотим иметь постоянный диапазон поиска). Затем функция возвращает значение из второго столбца. Последняя часть формулы, мы пишем 0. Таким образом, мы делаем Excel не искать совпадение в порядке чисел.
Теперь попробуйте создать собственную формулу vlookup, используя опцию insert function.
В нашем случае это выглядит так:
8. Left, Right, Mid
Left, right и mid функции — очень важные функции, которые позволяют извлекать определенное количество символов из строки
В нашем примере с формулой left мы берем первый символ с левой стороны. С формулой right мы можем сделать то же самое, но с символами справа налево. Имейте в виду, что пустое пространство также является символом.
9. Trim
Еще одна исключительная функция MS Excel. Функция trim удаляет пустые пространства после слов, когда их больше одного. Это может случиться довольно часто в Excel. Возможно, что информация извлекается из разных баз данных и заполняется ненужными пустым пространством между словами. Это может быть огромной проблемой, потому что ваши формули Excel могут не работать. Чтобы избавиться от раздражающих пустых пространств, мы используем формулу trim. = TRIM(A1)
В этом примере формула удалит лишние пробелы, если она найдет несколько.
10. Concatenate
Это удобная функция Excel, которая помогает, когда мы хотим объединить ячейки. Здесь мы хотим объединить ячейки A1, A2 и A3. Это можно сделать с помощью следующей функции:
Таким образом, у нас не будет пробелов между словами. Чтобы добавить пробелы между «I» и «love», нам нужно добавить пробел. Это можно решить, добавив кавычки внутри функции:
PS: Вы также можете объединить ячейки, просто добавив:
11. Len
Еще одна прекрасная формула, которая может применяться во многих ситуациях. Она широко используется для построения более сложных формул.
Эта функция будет подсчитывать количество символов в ячейке.
12. Max
Функция max возвращает наибольшее значение из диапазона.
Эта формула будет выглядеть в ячейках A2: A8, и тогда она извлечет самое высокое значение из этого диапазона.
13. Min
Функция min функционирует аналогично функции max, однако она дает противоположный результат. Он извлекает самое низкое значение из диапазона данных.
Формула из приведенного выше примера вернет наименьшее число из выбранных ячеек в столбце «A».
14. Days
Это функция, когда вам нужно вычислить количество дней между двумя днями в электронной таблице.
Формула вернет количество прошедших дней.
15. Networkdays
Иногда вы хотите работать только с рабочими днями. Эта функция работает очень похоже на приведенный выше пример. Разница в том, что он возвращает рабочие дни, прошедшие, за исключением выходных.
16. SQRT
Это довольно просто и довольно полезно одновременно. Как следует из названия, формула дает квадратный корень из числа
Это вернет квадратный корень из 1444 (38). Вы также можете использовать ссылку на ячейку здесь, как и во многих других формулах Excel.
17. Now
Иногда вам нужно знать текущую дату, когда вы открываете электронную таблицу Excel.
Это вернет текущую дату. Не забудьте установить формат на сегодняшний день. Вы можете попробовать с ним столько, сколько хотите. Вы можете, например, добавлять или вычитать дни. = Now () -14 вернет дату до двух недель.
18. Round
Функция Round очень полезна, когда вам приходится работать с округленными номерами. Excel может отображать числа для определенного символа, но сохраняет исходный номер и использует его для вычислений. Однако в некоторых случаях это может быть проблемой, и нам нужно работать только с округленными номерами. В таких случаях функция ROUND обращается к нашей помощи. = ROUND(B1,2)
В этом примере число округляется до второго десятичного знака.
19.Roundup
Это вариация формулы round, которая может очень помочь в некоторых случаях. Онa округляет значение до ближайшего целого числа. Это очень полезно в сочетании с другими формулами Excel. Давайте посмотрим на этот пример:
Здесь мы хотим найти в каком квартале текущего года находится текущую дату. Секрет этой формулы — простая математика. Функция принимает номер месяца, например, январь равен 1, февраль равен 2 и т. Д., А затем делит его на 3, округляя до ближайшего большего числа.
20. Rounddown
Rounddown работает аналогично Roundup, однако она округляет число до ближайшего целого числа
Excel имеет много полезных функций. В этой статье я попытался представить наиболее важные формулы в MS Excel наилучшим образом. Я считаю, что эти функции являются очень хорошей базой для тех из вас, кто хочет развивать свои умения Excel. Посредством освоения этих формул вы можете легко справляться с повседневными ситуациями, которые могут возникать при работе с Excel, и это действительно хорошая возможность для дальнейшего развития ваших навыков.
Эксель формулы которые работают быстрее
Пожалуйста, помогите оптимизировать формулы или перевести их в макросы.
Суть проблемы:
Есть массив данных по продажам, кусок которого приведен в прикрепленном примере. Задача — в этом массиве вычислить клиентов, которые сделали отгрузку, соответствующую заданному условию (на листе "Условия"). То есть клиент должен взять три вида товара не менее двух штук каждого вида за установленный период.
Для этого есть 7 формул, которые вычисляют эту самую отгрузку, а именно:
1 формула. Определяет отгрузку 1 клиента в месяц (сцепляет 2 значения)
2. Определяет отгрузку 1 вида товара по 1 клиенту в месяц (сцепляет 3 значения)
3. Вычисляет по массиву, соответствует ли вид, канал сбыта и период условиям
4. Определяет, соответствует ли накопленная отгрузка каждого учитываемого вида товара минимальной выборке и отсекает повторения учета (то есть учитывается только 1 раз)
5. Учитывает каждый сработавший вид товара
6. Определяет кол-во видов товара, выбранного 1 клиентом и отсекает повторения учета (то есть учитывается только 1 раз)
7. Учитывает клиента, сработавшего по установленным условиям.
Ну соответственно обработка массива осуществляется макросом, который копирует формулу из 2 строки по столбцу и вычисляет значения.
Проблема состоит в том, что при массиве в 1000 строк макрос пару секунд, при массиве в 5000 строк — 5 минут, в 10 000 строк — 15 минут, а при массиве в 15 000 строк зависает из-за вычисления накопительных формул.
Помогите, как-нибудь решить эту проблему? Может как-то можно оптимизировать формулы или сделать аналогичные вычисления макросом?
Эксель формулы которые работают быстрее
Всем привет и хорошего настроения!
Захотелось перевести статью с одного из зарубежных форумов. Ссылка на оригинал. Не со всем в статье согласен, но старался держаться основной линии автора. Возможно, кому-то будет интересно.
**************
10 СОВЕТОВ КАК УЛУЧШИТЬ РАБОТУ ФОРМУЛ В EXCEL
1). Используйте таблицы для хранения данных.
Начиная с Excel 2007 можно держать связанные друг с другом данные в таблицах (Преобразование данных: «Главная» — «Стили» — «Форматировать как таблицу»). К данным в таблицах можно обращаться формулами с помощью структурных ссылок. Также данные таких таблиц можно использовать как источник для сводных таблиц. Если Вы меняете количество строк в таблице — формулы по столбцам автоматически становятся динамическими. Например, если у Вас есть таблица «Sales», то можно собрать сумму по столбцу «Turn_Over» просто введя формулу:
Формула будет работать корректно даже если в таблице уменьшилось или увеличилось количество строк.
На заметку: в 2003-м Excel есть аналог таблиц (Exel lists).
2). Используйте именованные диапазоны и формулы.
Используя именованные диапазоны и формулы Вы упростите восприятие вычислений. Более того, есть возможность провести промежуточные расчеты один раз в именованной функции вместо того, чтобы рассчитывать их отдельно для каждой ячейки. Это снимет часть вычислительной нагрузки и ускорит работу книги Excel.
3). Используйте сводные таблицы.
Бывают случаи, когда формулы нам в принципе не нужны, но мы их используем просто потому, что можем. Но разве не проще использовать сводную таблицу, где множество итогов можно получить всего в несколько кликов мышью? После построения сводной таблицы, можно ссылаться на её значения с помощью формулы ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ или простыми ссылками на её ячейки. Это избавит от необходимости выполнять необязательные вычисления. Если данные меняются, достаточно просто перейти на вкладку «Данные» и обновить все сводные в одно действие. Обновление пройдёт лучше, если исходные данные хранятся в формате таблиц.
Часто причиной медленной работы книги Excel бывает большой объём данных. И сводные таблицы были разработаны именно для того, чтобы обрабатывать большие массивы информации.
4). Сортируйте данные.
Ещё одна причина не ахти какой скорости — попытки что-то найти в куче НЕ отсортированных данных. У Excel нет выбора и ему приходится искать иголку в стоге сена. Очень часто мы получаем НЕ сортированные данные из разных источников. Отсортировав данные и использовав корректное операторы в поисковых формулах можно значительно ускорить работу книги. Если сортировать что-то приходиться регулярно — можно запустить простейший макрос сразу, как только данные обновлены.
5). Вычисление книги вручную.
Скорость — это суровая плата за сложность структуры и обилие возможностей. Одна из таких возможностей — моментальное отражение проведённых в файле изменений на результатах вычислений. Но для корректной работы книги нам далеко не всегда нужно, чтобы проходила цепочка вычислений после каждого нажатия. Зачастую, вполне достаточно переключить расчёт формул в ручной режим и проводить вычисления когда это действительно требуется. Это можно сделать, например, нажатием клавиши F9. Обратите внимание, что Excel проходит по формулам каждый раз, когда Вы сохраняете файл.
6). Старайтесь отказываться от волатильных формул.
В Excel существует особый класс формул — волатильные формулы. Они пересчитываются при ЛЮБОМ изменении в книге. Примером волатильных формул служат: СЛЧИС, СЕГОДНЯ, ТДАТА, СМЕЩ и т.д. И если лист содержит множество волатильных формул, при малейшем изменении эти формулы должны быть пересчитаны. Что сверх необходимого нагружает лист вычислениями.
Что делать? Просто откажитесь от волатильных формул. Например, чем использовать СМЕЩ для построения динамического диапазона — задействуйте ИНДЕКС. Как НЕ волатильная формула он должен работать быстрее. А ещё лучше — используйте таблицы.
7). Держите формулы на отдельном листе.
Формулы — движущая сила любой Excel книги или модели. Собрав их на отдельном листе (или нескольких), Вы уменьшите вероятность ошибок, пробелов или повторов. Работа над ускорением файла становится элементарной задачей, когда все формулы собраны в одном месте. Плюс это придаёт файлу шарм структурности и организованности.
8). Пишите хорошие формулы.
Вот несколько советов, на которые можно ориентироваться:
— Встроенные формулы удобнее Ваших собственных. Например, СУММЕСЛИМН проще заполняется аргументами и такая же быстрая, как СУММПРОИЗВ.
— Не тратьте силы Excel на работу с целым столбцом, когда требуется всего несколько значений. Не стоит писать СУММ(A:A), когда заведомо известно, что значения будут в диапазоне А1:А10.
— Используйте ЕСЛИОШИБКА для обработки ошибок вместо усложненных конструкций типа ЕСЛИ(ЕОШИБКА())
— Уберите или исправьте ошибки формул (деление на ноль, ошибки имени и т.д.).
— Уберите или сократите использование массивных формул.
— Удалите совсем или уменьшите количество ссылок на другие книги.
— Удалите любые именованные диапазоны, которые выдают ошибку или несуществующие ссылки.
— Старайтесь использовать альтернативные формулы. Это не только отточит ум, но и позволит находить интересные решения.
— Не рассчитывайте цифры, которые Вам не нужны.
— Не считайте что-либо дважды, а лучше используйте первый результат во втором вычислении.
9). Суровые времена требуют суровых мер.
Иногда, что бы Вы не делали, скорость книги всё равно оставляет желать лучшего. Ниже несколько жестких решений:
— Замена формул на значения. Сохраните формулы отдельно (или только в первой строке данных), Ctrl+C, Ctrl+Alt+V, только значения и форматы чисел.
— Сборка книги с нуля. Позволяет выбросить всё лишнее, о чём даже нельзя было догадаться.
— Замените внешние ссылки на данные собственно актуальными данными. При необходимости импортируйте данные методом копи-пасты.
— Уменьшите функциональность. Ведь пользователь наверняка как-нибудь проживет без пары-другой фишек, верно?
— Найдите альтернативное решение. Пытаться сделать в Excel абсолютно всё — глупо. Зачем копать молотком, если есть лопата? Проверьте, вдруг есть какой другой инструмент, который делает то же самое лучше и быстрее.
10). Узнавайте новые формулы и играйте с ними.
Оптимизация — вещь не определённая, до конца не регламентированная. Это продолжительный, развивающийся процесс. Всегда нужно учить новые формулы, новые применения и играть с ними. И через это откроются новые способы улучшить тормозящие книги.
Всем привет и хорошего настроения!
Захотелось перевести статью с одного из зарубежных форумов. Ссылка на оригинал. Не со всем в статье согласен, но старался держаться основной линии автора. Возможно, кому-то будет интересно.
**************
10 СОВЕТОВ КАК УЛУЧШИТЬ РАБОТУ ФОРМУЛ В EXCEL
1). Используйте таблицы для хранения данных.
Начиная с Excel 2007 можно держать связанные друг с другом данные в таблицах (Преобразование данных: «Главная» — «Стили» — «Форматировать как таблицу»). К данным в таблицах можно обращаться формулами с помощью структурных ссылок. Также данные таких таблиц можно использовать как источник для сводных таблиц. Если Вы меняете количество строк в таблице — формулы по столбцам автоматически становятся динамическими. Например, если у Вас есть таблица «Sales», то можно собрать сумму по столбцу «Turn_Over» просто введя формулу:
Формула будет работать корректно даже если в таблице уменьшилось или увеличилось количество строк.
На заметку: в 2003-м Excel есть аналог таблиц (Exel lists).
2). Используйте именованные диапазоны и формулы.
Используя именованные диапазоны и формулы Вы упростите восприятие вычислений. Более того, есть возможность провести промежуточные расчеты один раз в именованной функции вместо того, чтобы рассчитывать их отдельно для каждой ячейки. Это снимет часть вычислительной нагрузки и ускорит работу книги Excel.
3). Используйте сводные таблицы.
Бывают случаи, когда формулы нам в принципе не нужны, но мы их используем просто потому, что можем. Но разве не проще использовать сводную таблицу, где множество итогов можно получить всего в несколько кликов мышью? После построения сводной таблицы, можно ссылаться на её значения с помощью формулы ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ или простыми ссылками на её ячейки. Это избавит от необходимости выполнять необязательные вычисления. Если данные меняются, достаточно просто перейти на вкладку «Данные» и обновить все сводные в одно действие. Обновление пройдёт лучше, если исходные данные хранятся в формате таблиц.
Часто причиной медленной работы книги Excel бывает большой объём данных. И сводные таблицы были разработаны именно для того, чтобы обрабатывать большие массивы информации.
4). Сортируйте данные.
Ещё одна причина не ахти какой скорости — попытки что-то найти в куче НЕ отсортированных данных. У Excel нет выбора и ему приходится искать иголку в стоге сена. Очень часто мы получаем НЕ сортированные данные из разных источников. Отсортировав данные и использовав корректное операторы в поисковых формулах можно значительно ускорить работу книги. Если сортировать что-то приходиться регулярно — можно запустить простейший макрос сразу, как только данные обновлены.
5). Вычисление книги вручную.
Скорость — это суровая плата за сложность структуры и обилие возможностей. Одна из таких возможностей — моментальное отражение проведённых в файле изменений на результатах вычислений. Но для корректной работы книги нам далеко не всегда нужно, чтобы проходила цепочка вычислений после каждого нажатия. Зачастую, вполне достаточно переключить расчёт формул в ручной режим и проводить вычисления когда это действительно требуется. Это можно сделать, например, нажатием клавиши F9. Обратите внимание, что Excel проходит по формулам каждый раз, когда Вы сохраняете файл.
6). Старайтесь отказываться от волатильных формул.
В Excel существует особый класс формул — волатильные формулы. Они пересчитываются при ЛЮБОМ изменении в книге. Примером волатильных формул служат: СЛЧИС, СЕГОДНЯ, ТДАТА, СМЕЩ и т.д. И если лист содержит множество волатильных формул, при малейшем изменении эти формулы должны быть пересчитаны. Что сверх необходимого нагружает лист вычислениями.
Что делать? Просто откажитесь от волатильных формул. Например, чем использовать СМЕЩ для построения динамического диапазона — задействуйте ИНДЕКС. Как НЕ волатильная формула он должен работать быстрее. А ещё лучше — используйте таблицы.
7). Держите формулы на отдельном листе.
Формулы — движущая сила любой Excel книги или модели. Собрав их на отдельном листе (или нескольких), Вы уменьшите вероятность ошибок, пробелов или повторов. Работа над ускорением файла становится элементарной задачей, когда все формулы собраны в одном месте. Плюс это придаёт файлу шарм структурности и организованности.
8). Пишите хорошие формулы.
Вот несколько советов, на которые можно ориентироваться:
— Встроенные формулы удобнее Ваших собственных. Например, СУММЕСЛИМН проще заполняется аргументами и такая же быстрая, как СУММПРОИЗВ.
— Не тратьте силы Excel на работу с целым столбцом, когда требуется всего несколько значений. Не стоит писать СУММ(A:A), когда заведомо известно, что значения будут в диапазоне А1:А10.
— Используйте ЕСЛИОШИБКА для обработки ошибок вместо усложненных конструкций типа ЕСЛИ(ЕОШИБКА())
— Уберите или исправьте ошибки формул (деление на ноль, ошибки имени и т.д.).
— Уберите или сократите использование массивных формул.
— Удалите совсем или уменьшите количество ссылок на другие книги.
— Удалите любые именованные диапазоны, которые выдают ошибку или несуществующие ссылки.
— Старайтесь использовать альтернативные формулы. Это не только отточит ум, но и позволит находить интересные решения.
— Не рассчитывайте цифры, которые Вам не нужны.
— Не считайте что-либо дважды, а лучше используйте первый результат во втором вычислении.
9). Суровые времена требуют суровых мер.
Иногда, что бы Вы не делали, скорость книги всё равно оставляет желать лучшего. Ниже несколько жестких решений:
— Замена формул на значения. Сохраните формулы отдельно (или только в первой строке данных), Ctrl+C, Ctrl+Alt+V, только значения и форматы чисел.
— Сборка книги с нуля. Позволяет выбросить всё лишнее, о чём даже нельзя было догадаться.
— Замените внешние ссылки на данные собственно актуальными данными. При необходимости импортируйте данные методом копи-пасты.
— Уменьшите функциональность. Ведь пользователь наверняка как-нибудь проживет без пары-другой фишек, верно?
— Найдите альтернативное решение. Пытаться сделать в Excel абсолютно всё — глупо. Зачем копать молотком, если есть лопата? Проверьте, вдруг есть какой другой инструмент, который делает то же самое лучше и быстрее.
10). Узнавайте новые формулы и играйте с ними.
Оптимизация — вещь не определённая, до конца не регламентированная. Это продолжительный, развивающийся процесс. Всегда нужно учить новые формулы, новые применения и играть с ними. И через это откроются новые способы улучшить тормозящие книги. Rioran
Роман, Москва, voronov_rv@mail.ru
Яндекс-Деньги: 41001312674279
Сообщение Всем привет и хорошего настроения!
Захотелось перевести статью с одного из зарубежных форумов. Ссылка на оригинал. Не со всем в статье согласен, но старался держаться основной линии автора. Возможно, кому-то будет интересно.
**************
10 СОВЕТОВ КАК УЛУЧШИТЬ РАБОТУ ФОРМУЛ В EXCEL
1). Используйте таблицы для хранения данных.
Начиная с Excel 2007 можно держать связанные друг с другом данные в таблицах (Преобразование данных: «Главная» — «Стили» — «Форматировать как таблицу»). К данным в таблицах можно обращаться формулами с помощью структурных ссылок. Также данные таких таблиц можно использовать как источник для сводных таблиц. Если Вы меняете количество строк в таблице — формулы по столбцам автоматически становятся динамическими. Например, если у Вас есть таблица «Sales», то можно собрать сумму по столбцу «Turn_Over» просто введя формулу:
Формула будет работать корректно даже если в таблице уменьшилось или увеличилось количество строк.
На заметку: в 2003-м Excel есть аналог таблиц (Exel lists).
2). Используйте именованные диапазоны и формулы.
Используя именованные диапазоны и формулы Вы упростите восприятие вычислений. Более того, есть возможность провести промежуточные расчеты один раз в именованной функции вместо того, чтобы рассчитывать их отдельно для каждой ячейки. Это снимет часть вычислительной нагрузки и ускорит работу книги Excel.
3). Используйте сводные таблицы.
Бывают случаи, когда формулы нам в принципе не нужны, но мы их используем просто потому, что можем. Но разве не проще использовать сводную таблицу, где множество итогов можно получить всего в несколько кликов мышью? После построения сводной таблицы, можно ссылаться на её значения с помощью формулы ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ или простыми ссылками на её ячейки. Это избавит от необходимости выполнять необязательные вычисления. Если данные меняются, достаточно просто перейти на вкладку «Данные» и обновить все сводные в одно действие. Обновление пройдёт лучше, если исходные данные хранятся в формате таблиц.
Часто причиной медленной работы книги Excel бывает большой объём данных. И сводные таблицы были разработаны именно для того, чтобы обрабатывать большие массивы информации.
4). Сортируйте данные.
Ещё одна причина не ахти какой скорости — попытки что-то найти в куче НЕ отсортированных данных. У Excel нет выбора и ему приходится искать иголку в стоге сена. Очень часто мы получаем НЕ сортированные данные из разных источников. Отсортировав данные и использовав корректное операторы в поисковых формулах можно значительно ускорить работу книги. Если сортировать что-то приходиться регулярно — можно запустить простейший макрос сразу, как только данные обновлены.
5). Вычисление книги вручную.
Скорость — это суровая плата за сложность структуры и обилие возможностей. Одна из таких возможностей — моментальное отражение проведённых в файле изменений на результатах вычислений. Но для корректной работы книги нам далеко не всегда нужно, чтобы проходила цепочка вычислений после каждого нажатия. Зачастую, вполне достаточно переключить расчёт формул в ручной режим и проводить вычисления когда это действительно требуется. Это можно сделать, например, нажатием клавиши F9. Обратите внимание, что Excel проходит по формулам каждый раз, когда Вы сохраняете файл.
6). Старайтесь отказываться от волатильных формул.
В Excel существует особый класс формул — волатильные формулы. Они пересчитываются при ЛЮБОМ изменении в книге. Примером волатильных формул служат: СЛЧИС, СЕГОДНЯ, ТДАТА, СМЕЩ и т.д. И если лист содержит множество волатильных формул, при малейшем изменении эти формулы должны быть пересчитаны. Что сверх необходимого нагружает лист вычислениями.
Что делать? Просто откажитесь от волатильных формул. Например, чем использовать СМЕЩ для построения динамического диапазона — задействуйте ИНДЕКС. Как НЕ волатильная формула он должен работать быстрее. А ещё лучше — используйте таблицы.
7). Держите формулы на отдельном листе.
Формулы — движущая сила любой Excel книги или модели. Собрав их на отдельном листе (или нескольких), Вы уменьшите вероятность ошибок, пробелов или повторов. Работа над ускорением файла становится элементарной задачей, когда все формулы собраны в одном месте. Плюс это придаёт файлу шарм структурности и организованности.
8). Пишите хорошие формулы.
Вот несколько советов, на которые можно ориентироваться:
— Встроенные формулы удобнее Ваших собственных. Например, СУММЕСЛИМН проще заполняется аргументами и такая же быстрая, как СУММПРОИЗВ.
— Не тратьте силы Excel на работу с целым столбцом, когда требуется всего несколько значений. Не стоит писать СУММ(A:A), когда заведомо известно, что значения будут в диапазоне А1:А10.
— Используйте ЕСЛИОШИБКА для обработки ошибок вместо усложненных конструкций типа ЕСЛИ(ЕОШИБКА())
— Уберите или исправьте ошибки формул (деление на ноль, ошибки имени и т.д.).
— Уберите или сократите использование массивных формул.
— Удалите совсем или уменьшите количество ссылок на другие книги.
— Удалите любые именованные диапазоны, которые выдают ошибку или несуществующие ссылки.
— Старайтесь использовать альтернативные формулы. Это не только отточит ум, но и позволит находить интересные решения.
— Не рассчитывайте цифры, которые Вам не нужны.
— Не считайте что-либо дважды, а лучше используйте первый результат во втором вычислении.
9). Суровые времена требуют суровых мер.
Иногда, что бы Вы не делали, скорость книги всё равно оставляет желать лучшего. Ниже несколько жестких решений:
— Замена формул на значения. Сохраните формулы отдельно (или только в первой строке данных), Ctrl+C, Ctrl+Alt+V, только значения и форматы чисел.
— Сборка книги с нуля. Позволяет выбросить всё лишнее, о чём даже нельзя было догадаться.
— Замените внешние ссылки на данные собственно актуальными данными. При необходимости импортируйте данные методом копи-пасты.
— Уменьшите функциональность. Ведь пользователь наверняка как-нибудь проживет без пары-другой фишек, верно?
— Найдите альтернативное решение. Пытаться сделать в Excel абсолютно всё — глупо. Зачем копать молотком, если есть лопата? Проверьте, вдруг есть какой другой инструмент, который делает то же самое лучше и быстрее.
10). Узнавайте новые формулы и играйте с ними.
Оптимизация — вещь не определённая, до конца не регламентированная. Это продолжительный, развивающийся процесс. Всегда нужно учить новые формулы, новые применения и играть с ними. И через это откроются новые способы улучшить тормозящие книги. Автор — Rioran
Дата добавления — 30.04.2015 в 11:26