12 простых приёмов для эффективной работы в Excel

Все, о чем вы хотели знать, но боялись спросить.
Человеку
непосвященному программа работы с таблицами Excel кажется огромной,
непонятной и оттого — пугающей. На самом деле это удобный инструмент, и
если знать небольшие хитрости, то можно сильно сократить время на
выполнение обычных функций.
1. Быстро добавить новые данные в диаграмму:
Если
для вашей уже построенной диаграммы на листе появились новые данные,
которые нужно добавить, то можно просто выделить диапазон с новой
информацией, скопировать его (Ctrl + C) и потом вставить прямо в
диаграмму (Ctrl + V).
2. Мгновенное заполнение (Flash Fill)
Эта
функция появилась только в последней версии Excel 2013, но она стоит
того, чтобы обновиться до новой версии досрочно. Предположим, что у вас
есть список полных ФИО (Иванов Иван Иванович), которые вам надо
превратить в сокращённые (Иванов И. И.).
Чтобы выполнить такое
преобразование, нужно просто начать писать желаемый текст в соседнем
столбце вручную. На второй или третьей строке Excel попытается
предугадать наши действия и выполнит дальнейшую обработку автоматически.
Останется только нажать клавишу Enter для подтверждения, и все имена
будут преобразованы мгновенно.
Подобным образом можно извлекать имена из email’ов, склеивать ФИО из фрагментов и т. д.
3. Скопировать без нарушения форматов
Вы,
скорее всего, знаете про «волшебный» маркер автозаполнения — тонкий
чёрный крест в правом нижнем углу ячейки, потянув за который можно
скопировать содержимое ячейки или формулу сразу на несколько ячеек.
Однако есть один неприятный нюанс: такое копирование часто нарушает
дизайн таблицы, т. к. копируется не только формула, но и формат ячейки.
Этого можно избежать, если сразу после протягивания чёрным крестом
нажать на смарт-тег — специальный значок, появляющийся в правом нижнем
углу скопированной области.
Если выбрать опцию «Копировать
только значения» (Fill Without Formatting), то Microsoft Excel скопирует
вашу формулу без формата и не будет портить оформление.
4. Отображение данных из таблицы Excel на карте
В
последней версии Excel 2013 появилась возможность быстро отобразить на
интерактивной карте ваши геоданные, например продажи по городам и т. п.
Для этого нужно перейти в «Магазин приложений» (Office Store) на вкладке
«Вставка» (Insert) и установить оттуда плагин Bing Maps.
Это
можно сделать и по прямой ссылке с сайта, нажав кнопку Add. После
добавления модуля его можно выбрать в выпадающем списке «Мои приложения»
(My Apps) на вкладке «Вставка» (Insert) и поместить на ваш рабочий
лист. Останется выделить ваши ячейки с данными и нажать на кнопку Show
Locations в модуле карты, чтобы увидеть наши данные на ней.
При желании в настройках плагина можно выбрать тип диаграммы и цвета для отображения.
5. Быстрый переход к нужному листу
Появится оглавление, и на любой нужный лист можно будет перейти мгновенно.
Также можно создать на отдельном листе оглавление с гиперссылками. Это чуть сложнее, но зачастую удобнее.
6. Преобразование строк в столбцы и обратно
Если вам когда-нибудь приходилось руками перекладывать ячейки из строк в столбцы, то вы оцените следующий трюк:
Выделите диапазон.
Скопируйте его (Ctrl + C) или, нажав на правую кнопку мыши, выберите «Копировать» (Copy).
Щёлкните
правой кнопкой мыши по ячейке, куда хотите вставить данные, и выберите в
контекстном меню один из вариантов специальной вставки — значок
«Транспонировать» (Transpose).
В старых версиях Excel нет такого
значка, но можно решить проблему с помощью специальной вставки (Ctrl +
Alt + V) и выбора опции «Транспонировать» (Transpose).
7. Выпадающий список в ячейке
Если
в какую-либо ячейку предполагается ввод строго определённых значений из
разрешённого набора (например, только «да» и «нет» или только из списка
отделов компании и т. д.), то это можно легко организовать при помощи
выпадающего списка:
Выделите ячейку (или диапазон ячеек), в которых должно быть такое ограничение.
Нажмите кнопку «Проверка данных» на вкладке «Данные» (Data — Validation).
В выпадающем списке «Тип» (Allow) выберите вариант «Список» (List).
В
поле «Источник» (Source) задайте диапазон, содержащий эталонные
варианты элементов, которые и будут впоследствии выпадать при вводе.
Продвинутые
трюки из той же серии: выпадающий список с наполнением, связанные
выпадающие списки, выпадающий список с фотографией и т. д.
8. «Умная» таблица
Если
выделить диапазон с данными и на вкладке «Главная» нажать
«Форматировать как таблицу» (Home — Format as Table), то наш список
будет преобразован в «умную» таблицу, которая (кроме модной полосатой
раскраски) умеет много полезного:
Автоматически растягиваться при дописывании к ней новых строк или столбцов.
Введённые формулы автоматом будут копироваться на весь столбец.
Шапка такой таблицы автоматически закрепляется при прокрутке, и в ней включаются кнопки фильтра для отбора и сортировки.
На появившейся вкладке «Конструктор» (Design) в такую таблицу можно добавить строку итогов с автоматическим вычислением.
9. Спарклайны
Спарклайны
— это нарисованные прямо в ячейках миниатюрные диаграммы, наглядно
отображающие динамику наших данных. Чтобы их создать, нажмите кнопку
«График» (Line) или «Гистограмма» (Columns) в группе «Спарклайны»
(Sparklines) на вкладке «Вставка» (Insert). В открывшемся окне укажите
диапазон с исходными числовыми данными и ячейки, куда вы хотите вывести
спарклайны.
После нажатия на кнопку «ОК» Microsoft Excel создаст
их в указанных ячейках. На появившейся вкладке «Конструктор» (Design)
можно дополнительно настроить их цвет, тип, включить отображение
минимальных и максимальных значений и т. д.
10. Восстановление несохранённых файлов
Пятница.
Вечер. Долгожданный конец ударной трудовой недели. Предвкушая отдых, вы
закрываете отчёт, с которым возились последнюю половину дня, и в
появившемся диалоговом окне «Сохранить изменения в файле?» вдруг
зачем-то жмёте «Нет».
Опустевший офис оглашает ваш истошный
вопль, но уже поздно — несколько последних часов работы пошли псу под
хвост и вместо приятного вечера в компании друзей вам придётся
восстанавливать утраченное.
На самом деле, есть неслабый шанс
исправить ситуацию. Если у вас Excel 2010, то нажмите на «Файл» —
«Последние» (File — Recent) и найдите в правом нижнем углу экрана кнопку
«Восстановить несохранённые книги» (Recover Unsaved Workbooks). В Excel
2013 путь немного другой: «Файл» — «Сведения» — «Управление версиями» —
«Восстановить несохранённые книги» (File — Properties — Recover Unsaved
Workbooks). Откроется специальная папка из недр Microsoft Office, куда
на такой случай сохраняются временные копии всех созданных или
изменённых, но несохранённых книг.
11. Сравнение двух диапазонов на отличия и совпадения
Весьма
часто при работе в Excel возникает необходимость сравнить два списка и
быстро найти элементы, которые в них совпадают или отличаются. Самый
быстрый и наглядный способ сделать это:
Выделите оба сравниваемых столбца (удерживая клавишу Ctrl).
Выберите
на вкладке «Главная» — «Условное форматирование» — «Правила выделения
ячеек» — «Повторяющиеся значения» (Home — Conditional formatting —
Highlight Cell Rules — Duplicate Values).
Выберите вариант «Уникальные» (Unique) в раскрывающемся списке.
12. Подбор (подгонка) результатов расчёта под нужные значения
Вы
когда-нибудь подбирали входные значения в вашем расчёте Excel, чтобы
получить на выходе нужный результат? В такие моменты чувствуешь себя
матёрым артиллеристом, правда? Всего-то пара десятков итераций «недолёт —
перелёт», и вот оно, долгожданное «попадание»!
Microsoft Excel
сможет сделать такую подгонку за вас, причём быстрее и точнее. Для этого
нажмите на вкладке «Данные» кнопку «Анализ „что если“» и выберите
команду «Подбор параметра» (Insert — What If Analysis — Goal Seek). В
появившемся окне задайте ячейку, где хотите подобрать нужное значение,
желаемый результат и входную ячейку, которая должна измениться. После
нажатия на «ОК» Excel выполнит до 100 «выстрелов», чтобы подобрать
требуемый вами итог с точностью до 0,001.