10 лайфхаков, которые помогут упростить работу в Excel

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

Лайфхаки сделаны на примере EXCEL 2013-2016, но будут работать и в более ранних/поздних версиях.

1. Сохранить формулу как значение. Копировать только данные без форматирования

Довольно часто мы работаем со сложными документами, которые содержат ряд многоступенчатых расчетов, происхождение которых нас мало интересует. А главная цель – скопировать необходимую информацию в наш файл. Если мы данные ячеек, которые вычислялись при помощи формул, скопируем в свой документ при помощи команд Копировать + Вставить, то получим не совсем то, что ожидали:

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

Команду Вставить как значения можно вызвать при помощи нажатия правой клавиши мыши и вызова контекстного меню Специальная вставка.

Ускорить это процесс можно вынесением данной команды на панель быстрого запуска:

Найти эту команду можно следующим образом: нажать стрелочку на данной панели ➤ выбрать меню Другие команды ➤ в разделе Все команды найти необходимую и добавить на панель. После этого данной командой можно будет воспользоваться кратким сочетанием клавиш <ALT>+порядковый номер данной команды на ленте. В моем случае <ALT>+1.

2. Скопировать формулу так, чтобы сохранились оригинальные ссылки

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

Чтобы формула заработала в скопированной ячейке нужно выполнить следующую последовательность действий: на исходной ячейке В1 нажимаем кнопку F2 ➤ в строке формул копируем данную формулу

При помощи команды Вставить вставляем данную формулу в нужную ячейку. И получаем нужные данные:

3. Быстро найти ячейки на листе, содержащие формулы

Если Вы работаете в своем документе, то вопрос этот скорее всего не возникнет. Но если нужно быстро разобраться в чужом – Вам эти знания скорее всего пригодятся. Чтобы буквально за одну секунду получить данные о том, какие ячейки содержат формулы, необходимо: зайти на вкладку Главная ➤ группа Редактирование ➤ меню Найти и выделить ➤ Формулы. Сразу по факту запуска команды на листе будут выделены все ячейки, содержащие формулы.

4. Создать в ячейке список

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

Как это сделать: заходим на вкладку Данные ➤ группа Работа с данными ➤ Проверка данных ➤ и в открытом окне на вкладке Параметры для типа данных выбираем Список, а источником может быть либо часть Вашего документа, либо введенные вручную через запятую данные. Также на этой вкладке Вы сможете для себя найти еще много интересных возможностей.

5. Быстро вставить или удалить строку/столбец

Когда работаете в таблице с большим количеством строк и столбцов, удобно работать с клавиатуры и не прибегать к помощи мыши. Для того, чтобы работа велась оперативно, предлагаем Вам ознакомиться с несколькими клавиатурными сокращениями:

1. <Shift>+<пробел> - выделить всю строку
2. <Ctrl>+<пробел> -выделить весь столбец
3. <Ctrl>+<-> - удалить выделенный столбец/строку
4. <Ctrl>+<+> - добавить строку/столбец перед выделенным

6. Сортировка по нескольким столбцам

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

Для этого, предварительно выделив нужную нам таблицу, проще воспользоваться командой Сортировка (вкладка Данные ➤ группа Сортировка и фильтр). В открывшемся окне мы в таком же порядке добавляем поля таблицы:

И в итоге получаем таблицу в ожидаемом виде:

7. Автоматически заполнить дни недели / рабочие дни и пр

Для того, чтобы мы не писали все эти данные вручную, создатели Excel о нас уже позаботились и нам достаточно в первую ячейку ввести Пн и за нижний правый уголок ячейки протянуть курсор до нужного места, после чего в возникшем контекстном меню выбрать нужную нам команду, в данному случае - команду Заполнить:

Точно также можно заполнить в таблице по порядку даты, например, только рабочих дней (эти и некоторые другие команды Вы увидите во все том же контекстном меню):

8. Быстро размножить формулу на весь столбец

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

Но если в таблице есть хоть одна пустая строка, именно на ней застопорится распространение формулы:

Для этого проще поступить так: через команду Копировать скопировать формулу верхней ячейки ➤ выделить весь интересующий диапазон ➤ воспользоваться командой Вставить, тогда все формулы правильно проставятся в весь выделенный диапазон:

9. Умная таблица

Excel предусмотрела возможность работать как с диапазонами, так и с умными таблицами. В предыдущем пункте мы создали таблицу, представляющую диапазон данных. Но если мы хотим в один клик придать ему приятное форматирование, чтобы в выбранном формате при необходимости добавлялись столбцы и строки, то можно преобразовать диапазон в умную таблицу. Для этого в данном диапазоне выделяем ячейку и на вкладке Вставка ➤ группа Таблицы ➤ нажимаем команду Таблица. Весь наш диапазон выделится автоматически и таблица сразу преобретет новое форматирование:

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

10. Сделать так, чтобы шапка документа печаталась на каждой странице

Если Ваша таблица занимает несколько листов, то вполне логично, что Вы хотите, чтобы ее шапка разместилась на каждом из них. Для этого можно воспользоваться командой Печатать заголовки на вкладке Разметка страницы в группе Параметры страницы. Так, для необходимого элемента (в нашем случае строка) выделяем диапазон:

После этого шапка таблицы распечатается на каждом листе документа: