Здравствуйте, друзья. В первой части этой статьи мы рассмотрели 13 полезных умений, которые делают вашу работу более продуктивной. Сегодня продолжим становиться эффективными пользователями программы. На очереди еще 14 советов, к которым стоит прислушаться.
Множественная вставка формулы или значений
Если вам нужно вставить одни и те же данные в несколько ячеек, или в массив – предварительно выделите нужные диапазоны для вставки. Теперь пишите формулу, число, строку. Что угодно для вставки сразу во все выделенные ячейки.
Когда контент написан – жмите Ctrl+Enter. После этого, данные будут вставлены во все выделенные элементы. Вам не придется копировать и вставлять его несколько раз. Получаем значительное ускорение работы.
Быстрая настройка относительных ссылок
Очень хорошо, что в Екселе есть относительные ссылки. Благодаря этому, скопированные формулы не требуют редактирования. Например, если в ячейке записано =А1, и эту ячейку скопировать на следующую строку, то в новой формуле уже будет =A2. Если скопировать в столбец справа – будет =B1.
Отлично, только не всегда это нужно. К примеру, когда есть таблица с исходными данными, ссылка на которые не должна изменяться при копировании. Чтобы этого добиться, мы в ссылке пишем знак доллара перед той координатой, которую нужно «заморозить»:
-
=$A$1 – заморожены и строки, и столбцы
-
=$A1 – заморожены только столбцы
-
=A$1 – заморожены только строки
-
=A1 – ничего не заморожено
Очень гибко, правда не очень удобно писать знаки доллара вручную. Для быстрого перебора вариантов заморозки используйте такой алгоритм:
-
Поставьте курсор в ту ссылку, которую нужно замораживать
-
Нажмите F4, чтобы установить заморозку
-
Нажимайте F4 последовательно несколько раз, чтобы выбрать тот вариант заморозки, который нужен
Гораздо проще нажать F4, чем писать знаки доллара вручную.
Перетаскивание формул
Когда Вы записали формулу и нужно изменить место ее расположения, простое копирование, скорее всего, не подойдет из-за описанных выше относительных ссылок. Можно решить этот вопрос тремя способами:
- Зафиксировать координаты, как указано в предыдущем пункте. Теперь можно скопировать формулу без потери ссылок
-
Скопировать текст формулы. Установите курсор в исходную ячейку и скопируйте формулу, как текст, прямо из строки формул. Теперь вставляйте ее в любую другую ячейку, ссылки не изменятся. Но форматирование ячеек не скопируется
-
Переместите исходную ячейку перетягиванием. Выделите ее и наведите мышь на рамку ячейки, чтобы курсор стал четырехнаправленной стрелкой. Тяните мышью клетку в новое место. Будет перенесено и содержимое и форматирование.
Копирование нескольких формул
Если Вам нужно скопировать несколько формул так, чтобы сохранились относительные ссылки – заставьте Excel думать, что Ваши формулы – это текст. Например, можно заменить знак «=» вначале формулы на другой символ:
-
Выделите весь копируемый диапазон формул
-
Нажмите Ctrl+F и в открывшемся окне перейдите на вкладку «Заменить»
-
В поле «Найти» пишем «=» (без кавычек), а в поле «Заменить на» — знак «#»
-
Кликаем Заменить все. Теперь вместо формул в выделенном диапазоне – текст. Копируете его в новое место одним из доступных способов.
-
После копирования в обоих диапазонах делаем обратную процедуру – заменяем решетку на знак равенства. Формулы снова заработают.
Просто и быстро, пользуемся.
Копирование формул двойным кликом
Когда у Вас есть массив с данными, для которых построчно нужно применить одну и ту же формулу, действуйте так:
-
Запишите нужную формулу в самой верхней строке
-
Сделайте двойной клик по маленькому квадратному маркеру в нижнем правом углу ячейки с формулой
-
Наслаждайтесь, как быстро и легко Вы скопировали формулы во все строки столбца
Это быстрее и легче, чем копировать вручную. Правда, есть особенность. Столбец слева от того, в котором Вы работаете, должен быть заполнен данными полностью. Иначе, формула может скопироваться лишь до первой пустой строки.
Проверка всех формул на листе
Когда на листе есть очевидная ошибка, а формул достаточно много, чтобы их проверить, отобразите все формулы на листе. Для этого нажмите на ленте Формулы – Зависимости формул – Показать формулы.
Далее будет легче найти ошибку. Больше на эту тему я писал в этой статье.
Как выделить все формулы на листе
Иногда нужно найти все ячейки на листе, в которых содержатся формулы. Сделаем так. Жмем F5 и в открывшемся окне кликаем Выделить и Ок.
Далее выбираем Формулы и снова Ок. Excel выделит все ячейки, в которых содержатся формулы.
Далее можно их очистить, оформить, скопирвать, делать прочие необходимые манипуляции.
Как вычислить часть формулы в Экселе
Когда нужно проверить свои вычисления, часто необходимо проверить, какой результат возвращает та или иная часть сложной формулы. Это легко можно сделать:
Открываете формулу для редактирования и выделяете ту ее часть, которую нужно пересчитать отдельно.
Далее, нажимаете F9. Если выделенный отрезок вычисляется, программа заменит его на результат вычисления.
Если нужно сохранить результаты действия – нажмите Enter. Если же Вы хотите вернуть формулу в исходное состояние – нажмите Esc.
Пошаговое вычисление формулы
Еще один инструмент для проверки вычислений – пошаговое выполнение. Выделите ячейку с формулой и нажмите на ленте Формулы – Зависимости формул – Вычислить формулу.
В открывшемся окне можно нажимать кнопку Вычислить, программа заменит подчеркнутый фрагмент на итоговый результат.
Таким образом, Вы можете поэтапно проверить работу формулы и возможно, найти ошибку. Останется только и исправить и повторить процедуру при желании.
Вставка значений вместо формул
Один из вопросов, которые мне задают чаще всего – как заменить формулы результатом вычислений. Отвечаю сразу всем: выделяете массив с формулами, нажимаете Ctrl+C, чтобы его скопировать.
Далее пользуемся инструментом Специальная вставка. Жмем на ленте Главная – Буфер обмена – Вставить – Специальная вставка. В открывшемся окне выбираем Значения и жмем Ок. Все, формулы заменены своими результатами.
Быстрые арифметические операции с массивами
Предположим, у Вас есть файл с большими таблицами. И Вам понадобилось какие-то ячейки изменить на одну и ту же величину. К примеру, добавить 10% ко всем значениям из второго столбца на примере:
Выберите любую пустую ячейку и запишите в нее значение 1,1, скопируйте эту ячейку. Выделите весь столбец, который нужно изменить. Нажмите на ленте Главная – Буфер обмена – Вставить – Специальная вставка. Выберите Значение и поставьте галку Умножить.
Добавить рисунок
Все элементы выделенного массива будут умножены на 1.1, то есть, добавлено 10%. Доступны операции: сложить, вычесть, умножить, разделить.
Применяйте имена вместо адресов ячеек
В одной из своих статей я рассказывал, как присваивать имена ячейкам и диапазонам. Советую активно использовать эту возможность.
Например, диапазон ячеек А1:А12 можно назвать «продажи_2019». Какая из приведенных формул будет более читабельной: =СУММ(А1:А12) или =СУММ(продажи_2019)? Очевидно, что вторая.
Один из способов задать имя – выделите нужные ячейки и нажмите на ленте Формулы – Определенные имена – Присвоить имя. Более подробно эта тема раскрыта здесь.
Применение имен в уже написанных формулах
Когда Вы определили имена, они не появятся в уже написанных формулах автоматически. А что делать, если хочется получить максимальный эффект от именования?
Кликните на ленте Формулы – Определенные имена – Присвоить имя (стрелка вниз) – Применить имена. Откроется простое диалоговое окно. Выберите нужные имена и нажмите Ок. Программа заменит ссылки их именами в существующих формулах.
Продвинутое использование имен
Используйте именование для создания новых переменных или констант. Приведу два примера:
-
Вы используете в расчетах особый коэффициент, устанавливаемый в вашей организации и регулярно изменяющийся. Прописывать его формулах неудобно, т.к. постоянно нужно будет править большое количество ячеек. Можно выделить для коэффициента отдельную ячейку и ссылаться на нее. А можно не расходовать место на листе и сохранить именованную константу.
Нажмите на ленте Формулы – Определенные имена – Присвоить имя. В поле «Имя» запишите информативное название константы, а в поле «Диапазон» — ее значение. Жмите Ок. Теперь это имя можно использовать, как константу.
-
У Вас есть ячейка, в которой указан показатель. Вы можете создать переменную, в которой этот показатель будет корректироваться. Запишем по аналогии с предыдущим примером. Пусть будет так:
Сейчас мы создали именованную переменную и можем применять ее в расчетах
Думаю, пока этого хватит, чтобы Вы практиковали грамотный и продуктивный Excel. Впереди еще множество интересных статей на блоге officelegko.com. А если у Вас остались вопросы по этому посту – задавайте их в комментариях, обязательно отвечу!