logo

Блог Александра Томма

О том, как заставить Microsoft Office работать на Вас

27 полезных навыков для комфортного пользования Excel ч.2

Дата: 19 ноября 2018 Категория: Excel
Поделиться, добавить в закладки или распечатать статью

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

Множественная вставка формулы или значений

Если вам нужно вставить одни и те же данные в несколько ячеек, или в массив – предварительно выделите нужные диапазоны для вставки. Теперь пишите формулу, число, строку. Что угодно для вставки сразу во все выделенные ячейки.

Когда контент написан – жмите Ctrl+Enter. После этого, данные будут вставлены во все выделенные элементы. Вам не придется копировать и вставлять его несколько раз. Получаем значительное ускорение работы.

Быстрая настройка относительных ссылок

Очень хорошо, что в Екселе есть относительные ссылки. Благодаря этому, скопированные формулы не требуют редактирования. Например, если в ячейке записано =А1, и эту ячейку скопировать на следующую строку, то в новой формуле уже будет =A2. Если скопировать в столбец справа – будет =B1.

Отлично, только не всегда это нужно. К примеру, когда есть таблица с исходными данными, ссылка на которые не должна изменяться при копировании. Чтобы этого добиться, мы в ссылке пишем знак доллара перед той координатой, которую нужно «заморозить»:

  • =$A$1 – заморожены и строки, и столбцы
  • =$A1 – заморожены только столбцы
  • =A$1 – заморожены только строки
  • =A1 – ничего не заморожено

Очень гибко, правда не очень удобно писать знаки доллара вручную. Для быстрого перебора вариантов заморозки используйте такой алгоритм:

  1. Поставьте курсор в ту ссылку, которую нужно замораживать
  2. Нажмите F4, чтобы установить заморозку
  3. Нажимайте F4 последовательно несколько раз, чтобы выбрать тот вариант заморозки, который нужен

Гораздо проще нажать F4, чем писать знаки доллара вручную.

Перетаскивание формул

Когда Вы записали формулу и нужно изменить место ее расположения, простое копирование, скорее всего, не подойдет из-за описанных выше относительных ссылок. Можно решить этот вопрос тремя способами:

  1. Зафиксировать координаты, как указано в предыдущем пункте. Теперь можно скопировать формулу без потери ссылок
  2. Скопировать текст формулы. Установите курсор в исходную ячейку и скопируйте формулу, как текст, прямо из строки формул. Теперь вставляйте ее в любую другую ячейку, ссылки не изменятся. Но форматирование ячеек не скопируется
  3. Переместите исходную ячейку перетягиванием. Выделите ее и наведите мышь на рамку ячейки, чтобы курсор стал четырехнаправленной стрелкой. Тяните мышью клетку в новое место. Будет перенесено и содержимое и форматирование.

Копирование нескольких формул

Если Вам нужно скопировать несколько формул так, чтобы сохранились относительные ссылки – заставьте Excel думать, что Ваши формулы – это текст. Например, можно заменить знак «=» вначале формулы на другой символ:

  1. Выделите весь копируемый диапазон формул
  2. Нажмите Ctrl+F и в открывшемся окне перейдите на вкладку «Заменить»
  3. В поле «Найти» пишем «=» (без кавычек), а в поле «Заменить на» — знак «#»
  4. Кликаем Заменить все. Теперь вместо формул в выделенном диапазоне – текст. Копируете его в новое место одним из доступных способов.
  5. После копирования в обоих диапазонах делаем обратную процедуру – заменяем решетку на знак равенства. Формулы снова заработают.

Просто и быстро, пользуемся.

Копирование формул двойным кликом

Когда у Вас есть массив с данными, для которых построчно нужно применить одну и ту же формулу, действуйте так:

  • Запишите нужную формулу в самой верхней строке
  • Сделайте двойной клик по маленькому квадратному маркеру в нижнем правом углу ячейки с формулой
  • Наслаждайтесь, как быстро и легко Вы скопировали формулы во все строки столбца

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

Проверка всех формул на листе

Когда на листе есть очевидная ошибка, а формул достаточно много, чтобы их проверить, отобразите все формулы на листе. Для этого нажмите на ленте Формулы – Зависимости формул – Показать формулы.

Далее будет легче найти ошибку. Больше на эту тему я писал в этой статье.

Как выделить все формулы на листе

Иногда нужно найти все ячейки на листе, в которых содержатся формулы. Сделаем так. Жмем F5 и в открывшемся окне кликаем Выделить и Ок.

Далее выбираем Формулы и снова Ок. Excel выделит все ячейки, в которых содержатся формулы.

Далее можно их очистить, оформить, скопирвать, делать прочие необходимые манипуляции.

Как вычислить часть формулы в Экселе

Когда нужно проверить свои вычисления, часто необходимо проверить, какой результат возвращает та или иная часть сложной формулы. Это легко можно сделать:

Открываете формулу для редактирования и выделяете ту ее часть, которую нужно пересчитать отдельно.

Далее, нажимаете F9. Если выделенный отрезок вычисляется, программа заменит его на результат вычисления.

Если нужно сохранить результаты действия – нажмите Enter. Если же Вы хотите вернуть формулу в исходное состояние – нажмите Esc.

Пошаговое вычисление формулы

Еще один инструмент для проверки вычислений – пошаговое выполнение. Выделите ячейку с формулой и нажмите на ленте Формулы – Зависимости формул – Вычислить формулу.

В открывшемся окне можно нажимать кнопку Вычислить, программа заменит подчеркнутый фрагмент на итоговый результат.

Таким образом, Вы можете поэтапно проверить работу формулы и возможно, найти ошибку. Останется только и исправить и повторить процедуру при желании.

Вставка значений вместо формул

Один из вопросов, которые мне задают чаще всего – как заменить формулы результатом вычислений. Отвечаю сразу всем: выделяете массив с формулами, нажимаете Ctrl+C, чтобы его скопировать.

Далее пользуемся инструментом Специальная вставка. Жмем на ленте Главная – Буфер обмена – Вставить – Специальная вставка. В открывшемся окне выбираем Значения и жмем Ок. Все, формулы заменены своими результатами.

Быстрые арифметические операции с массивами

Предположим, у Вас есть файл с большими таблицами. И Вам понадобилось какие-то ячейки изменить на одну и ту же величину. К примеру, добавить 10% ко всем значениям из второго столбца на примере:

Выберите любую пустую ячейку и запишите в нее значение 1,1, скопируйте эту ячейку. Выделите весь столбец, который нужно изменить. Нажмите на ленте Главная – Буфер обмена – Вставить – Специальная вставка. Выберите Значение и поставьте галку Умножить.

Добавить рисунок

Все элементы выделенного массива будут умножены на 1.1, то есть, добавлено 10%. Доступны операции: сложить, вычесть, умножить, разделить.

Применяйте имена вместо адресов ячеек

В одной из своих статей я рассказывал, как присваивать имена ячейкам и диапазонам. Советую активно использовать эту возможность.

Например, диапазон ячеек А1:А12 можно назвать «продажи_2019». Какая из приведенных формул будет более читабельной: =СУММ(А1:А12) или =СУММ(продажи_2019)? Очевидно, что вторая.

Один из способов задать имя – выделите нужные ячейки и нажмите на ленте Формулы – Определенные имена – Присвоить имя. Более подробно эта тема раскрыта здесь.

Применение имен в уже написанных формулах

Когда Вы определили имена, они не появятся в уже написанных формулах автоматически. А что делать, если хочется получить максимальный эффект от именования?

Кликните на ленте Формулы – Определенные имена – Присвоить имя (стрелка вниз) – Применить имена. Откроется простое диалоговое окно. Выберите нужные имена и нажмите Ок. Программа заменит ссылки их именами в существующих формулах.

Продвинутое использование имен

Используйте именование для создания новых переменных или констант. Приведу два примера:

  1. Вы используете в расчетах особый коэффициент, устанавливаемый в вашей организации и регулярно изменяющийся. Прописывать его формулах неудобно, т.к. постоянно нужно будет править большое количество ячеек. Можно выделить для коэффициента отдельную ячейку и ссылаться на нее. А можно не расходовать место на листе и сохранить именованную константу.

    Нажмите на ленте Формулы – Определенные имена – Присвоить имя. В поле «Имя» запишите информативное название константы, а в поле «Диапазон» — ее значение. Жмите Ок. Теперь это имя можно использовать, как константу.

  2. У Вас есть ячейка, в которой указан показатель. Вы можете создать переменную, в которой этот показатель будет корректироваться. Запишем по аналогии с предыдущим примером. Пусть будет так:

    Сейчас мы создали именованную переменную и можем применять ее в расчетах

Думаю, пока этого хватит, чтобы Вы практиковали грамотный и продуктивный Excel. Впереди еще множество интересных статей на блоге officelegko.com. А если у Вас остались вопросы по этому посту – задавайте их в комментариях, обязательно отвечу!

Поделиться, добавить в закладки или распечатать статью

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *