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

Вход

Регистрация

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

 

= Мир MS Excel/10 советов как улучшить работу формул в Excel. - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин  
Мир MS Excel » Работа и общение » Неформальное общение » 10 советов как улучшить работу формул в Excel. (Вольный перевод: что-то оттуда, что-то от себя.)
10 советов как улучшить работу формул в Excel.
Rioran Дата: Четверг, 30.04.2015, 11:26 | Сообщение № 1
Группа: Авторы
Ранг: Ветеран
Сообщений: 903
Репутация: 290 ±
Замечаний: 0% ±

Excel 2013
Всем привет и хорошего настроения!

Захотелось перевести статью с одного из зарубежных форумов. Ссылка на оригинал. Не со всем в статье согласен, но старался держаться основной линии автора. Возможно, кому-то будет интересно.
**************

10 СОВЕТОВ КАК УЛУЧШИТЬ РАБОТУ ФОРМУЛ В EXCEL

1). Используйте таблицы для хранения данных.

Начиная с Excel 2007 можно держать связанные друг с другом данные в таблицах (Преобразование данных: "Главная" - "Стили" - "Форматировать как таблицу"). К данным в таблицах можно обращаться формулами с помощью структурных ссылок. Также данные таких таблиц можно использовать как источник для сводных таблиц. Если Вы меняете количество строк в таблице - формулы по столбцам автоматически становятся динамическими. Например, если у Вас есть таблица "Sales", то можно собрать сумму по столбцу "Turn_Over" просто введя формулу:

Код
=СУММ(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). Узнавайте новые формулы и играйте с ними.

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


Роман, Москва, voronov_rv@mail.ru
Яндекс-Деньги: 41001312674279


Сообщение отредактировал Rioran - Четверг, 30.04.2015, 11:28
 
Ответить
СообщениеВсем привет и хорошего настроения!

Захотелось перевести статью с одного из зарубежных форумов. Ссылка на оригинал. Не со всем в статье согласен, но старался держаться основной линии автора. Возможно, кому-то будет интересно.
**************

10 СОВЕТОВ КАК УЛУЧШИТЬ РАБОТУ ФОРМУЛ В EXCEL

1). Используйте таблицы для хранения данных.

Начиная с Excel 2007 можно держать связанные друг с другом данные в таблицах (Преобразование данных: "Главная" - "Стили" - "Форматировать как таблицу"). К данным в таблицах можно обращаться формулами с помощью структурных ссылок. Также данные таких таблиц можно использовать как источник для сводных таблиц. Если Вы меняете количество строк в таблице - формулы по столбцам автоматически становятся динамическими. Например, если у Вас есть таблица "Sales", то можно собрать сумму по столбцу "Turn_Over" просто введя формулу:

Код
=СУММ(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
DJ_Marker_MC Дата: Четверг, 30.04.2015, 11:41 | Сообщение № 2
Группа: Друзья
Ранг: Ветеран
Сообщений: 991
Репутация: 213 ±
Замечаний: 0% ±

Excel 2019
Хорошая статейка.
Не совсем правда понял что имеется ввиду в 7 пункте.
 
Ответить
СообщениеХорошая статейка.
Не совсем правда понял что имеется ввиду в 7 пункте.

Автор - DJ_Marker_MC
Дата добавления - 30.04.2015 в 11:41
Rioran Дата: Четверг, 30.04.2015, 11:50 | Сообщение № 3
Группа: Авторы
Ранг: Ветеран
Сообщений: 903
Репутация: 290 ±
Замечаний: 0% ±

Excel 2013
что имеется ввиду в 7 пункте

Как я понял, автор предлагает данные держать отдельно, а все расчёты - отдельно. Это не то, чтобы ускоряет работу формул, но позволяет лучше себя контролировать. Тут я с автором согласен.


Роман, Москва, voronov_rv@mail.ru
Яндекс-Деньги: 41001312674279
 
Ответить
Сообщение
что имеется ввиду в 7 пункте

Как я понял, автор предлагает данные держать отдельно, а все расчёты - отдельно. Это не то, чтобы ускоряет работу формул, но позволяет лучше себя контролировать. Тут я с автором согласен.

Автор - Rioran
Дата добавления - 30.04.2015 в 11:50
DJ_Marker_MC Дата: Четверг, 30.04.2015, 12:14 | Сообщение № 4
Группа: Друзья
Ранг: Ветеран
Сообщений: 991
Репутация: 213 ±
Замечаний: 0% ±

Excel 2019
ну это 1000%... у меня практически всегда есть скрытый лист "Исходка" или же исходные данные загнаны в powerpivot
 
Ответить
Сообщениену это 1000%... у меня практически всегда есть скрытый лист "Исходка" или же исходные данные загнаны в powerpivot

Автор - DJ_Marker_MC
Дата добавления - 30.04.2015 в 12:14
Nic70y Дата: Суббота, 02.05.2015, 16:03 | Сообщение № 5
Группа: Друзья
Ранг: Экселист
Сообщений: 8705
Репутация: 2260 ±
Замечаний: 0% ±

Excel 2010
Используйте именованные диапазоны и формулы.
возможно я ошибаюсь, но по-моему имена не упрощают, а перегружают файл.


ЮMoney 41001841029809
 
Ответить
Сообщение
Используйте именованные диапазоны и формулы.
возможно я ошибаюсь, но по-моему имена не упрощают, а перегружают файл.

Автор - Nic70y
Дата добавления - 02.05.2015 в 16:03
Мир MS Excel » Работа и общение » Неформальное общение » 10 советов как улучшить работу формул в Excel. (Вольный перевод: что-то оттуда, что-то от себя.)
  • Страница 1 из 1
  • 1
Поиск:

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