Друзья, добро пожаловать на канал "Ты ж программист!"✨
Давненько у нас не было статей по изучению Excel или рассмотрению некоторых лайфхаков. Выкоротал немного времени и решил продолжить рубрику по изучению.
Таким образом, чтобы равномерно выпустить материал с советами, которые могут помочь лучше разбираться с формулами, разбиваю на две части материал.
💾Использование именованных диапазонов
В формулах удобнее всего использовать именованный диапазон, т.е. давать имя какому-то ряду и обращаться к нему по имени.
Для создания именного диапазона выберите нужный диапазон в программе и нажмите:
- ПКМ и в контекстном меню выберите Присвоить имя. Заполните поле Имя и по этому параметру можно теперь обращаться как к диапазону;
- На ленте вкладка Формулы -> раздел Определенные имена -> Присвоить имя.
Таким образом, есть возможность провести промежуточные расчеты один раз в именованной функции, что позволит снизить вычислительные нагрузки.
💾Сортировка данных
Сортировка данных может значительно ускорить работу книги Excel.
Если в вашей книге или на листе довольно часто что-то меняется, то рекомендуем сортировать данные по нужным вам правилам или использовать макрос. Вот вариант кода для примера:
Sub Сортировка()
Range("A:R").Select
Selection.Sort Key1:=Range("Январь"), Order1:=xlAscending, Key2:=Range("Февраль"), _
Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:= _
xlSortNormal
End Sub
Range("A:R").Select - отвечает за диапазон в пределах которого произойдёт сортировка.
Key1:=Range("Январь") - Поле, по которому будем сортировать данные. В качестве диапазона указал именованный диапазон и прошлого пункта. Можно указать нужный диапазон ячеек в кавычках через знак двоеточия.
Key2:=Range("Февраль") - Второе поле, по которому произведём сортировку совместно с первым полем.
Order2:=xlAscending - порядок сортировки. Если xlAscending, то по возрастанию. Для убывания укажите xlDescending
Header:=xlYes - учитывать или нет заголовок. Если стоит xlYes, то учитывать. Если xlNo, то не учитывать.
💾Отказываемся от статичных формул
Не предлагаем вам совсем уж избегать и не использовать статические формулы. Просто будьте бдительны и осторожны с ними. Они пересчитываются при любом изменении в книге. Примером таких формул служат: СЛЧИС, СЕГОДНЯ, ТДАТА, СМЕЩ и т.д.
Таким образом, формула СЕГОДНЯ будет каждый день брать за "сегодня" то число, которое "сегодня" по календарю.
💾Стараемся писать хорошие формулы
Я предлагаю вам рассмотреть несколько советов, которые, надеюсь, помогут вам улучшить ваши формулы:
- не тратьте ресурсы Excel на работу с целым столбцом, если вам необходимо указать всего пару или десяток значений;
- готовые формулы, что встроены в программу, на первых порах будут удобнее ваших собственных;
- по возможности старайтесь не использовать массивные формулы на пару листов;
- проверяйте ваши формулы на логические ошибки - те, о которых программа вам не сообщит до применения формулы, например, ошибка в имени списка, динамическая переменная, деление на 0;
- старайтесь избегать в формулах ссылки на другие книги и файлы, в которых содержится часть данных для расчёта. Для этого есть листы в книгах;
- следите за именованными диапазонами. Если вы добавили строки после последней ячейки, указанной в диапазоне, то автоматически расширение не произойдёт;
- не дублируйте формулы для повторного расчёта, а лучше используйте результат расчёта, на основании которого проводите последующий расчёт.
💾Узнаем что-то новое, экспериментируем
Призываю вас изучать новые формулы по мере необходимостей или появления новых задач. Всегда есть что-то, о чём можно не знать.
Изучение - непрерывный и продолжительный, а так же, развивающийся процесс: всегда нужно учить новые формулы, новые возможности их применения.
Таким образом, рано или поздно, вам удастся оптимизировать и автоматизировать множество своих задач.