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

Часто приходится иметь дело с MS Excel? Тогда не пропустите новую порцию советов по оптимизации работы в этой программе. Секретами поделился Николай Павлов — автор проекта «Планета Excel».

Excel

Совсем недавно на страницах Лайфхакера мы поделились с вами небольшими хитростями, которые позволяют оптимизировать работу в MS Excel. А сегодня хотим предложить вашему вниманию новую порцию советов для ускорения действий в этой программе. О них расскажет Николай Павлов — автор проекта «Планета Excel», меняющего представление людей о том, что на самом деле можно сделать с помощью этой замечательной программы и всего пакета Office. Николай является IT-тренером, разработчиком и экспертом по продуктам Microsoft Office, Microsoft Office Master, Microsoft Most Valuable Professional. Вот проверенные им лично приёмы для ускоренной работы в Excel. ↓ 

Быстрое добавление новых данных в диаграмму

Если для вашей уже построенной диаграммы на листе появились новые данные, которые нужно добавить, то можно просто выделить диапазон с новой информацией, скопировать его (Ctrl + C) и потом вставить прямо в диаграмму (Ctrl + V).

ezgif.com-save

Мгновенное заполнение (Flash Fill)

Эта функция появилась только в последней версии Excel 2013, но она стоит того, чтобы обновиться до новой версии досрочно. Предположим, что у вас есть список полных ФИО (Иванов Иван Иванович), которые вам надо превратить в сокращённые (Иванов И. И.). Чтобы выполнить такое преобразование, нужно просто начать писать желаемый текст в соседнем столбце вручную. На второй или третьей строке Excel попытается предугадать наши действия и выполнит дальнейшую обработку автоматически. Останется только нажать клавишу Enter для подтверждения, и все имена будут преобразованы мгновенно.

Мгновенное заполнение (Flash Fill)

Подобным образом можно извлекать имена из email’ов, склеивать ФИО из фрагментов и т. д.

Копирование без нарушения форматов

Вы, скорее всего, знаете про «волшебный» маркер автозаполнения — тонкий чёрный крест в правом нижнем углу ячейки, потянув за который можно скопировать содержимое ячейки или формулу сразу на несколько ячеек. Однако есть один неприятный нюанс: такое копирование часто нарушает дизайн таблицы, т. к. копируется не только формула, но и формат ячейки. Этого можно избежать, если сразу после протягивания чёрным крестом нажать на смарт-тег — специальный значок, появляющийся в правом нижнем углу скопированной области.

Если выбрать опцию «Копировать только значения» (Fill Without Formatting), то Microsoft Excel скопирует вашу формулу без формата и не будет портить оформление.

ezgif.com-save (1)

Отображение данных из таблицы Excel на карте

В последней версии Excel 2013 появилась возможность быстро отобразить на интерактивной карте ваши геоданные, например продажи по городам и т. п. Для этого нужно перейти в «Магазин приложений» (Office Store) на вкладке «Вставка» (Insert) и установить оттуда плагин Bing Maps. Это можно сделать и по прямой ссылке с сайта, нажав кнопку Add. После добавления модуля его можно выбрать в выпадающем списке «Мои приложения» (My Apps) на вкладке «Вставка» (Insert) и поместить на ваш рабочий лист. Останется выделить ваши ячейки с данными и нажать на кнопку Show Locations в модуле карты, чтобы увидеть наши данные на ней.

Отображение данных из таблицы Excel на карте

При желании в настройках плагина можно выбрать тип диаграммы и цвета для отображения.

Быстрый переход к нужному листу

Если в вашей книге количество рабочих листов перевалило за 10, то ориентироваться в них становится трудновато. Щёлкните правой кнопкой мыши по любой из кнопок прокрутки ярлычков листов в левом нижнем углу экрана.

Быстрый переход к нужному листу

Появится оглавление, и на любой нужный лист можно будет перейти мгновенно.

Также можно создать на отдельном листе оглавление с гиперссылками. Это чуть сложнее, но зачастую удобнее.

Преобразование строк в столбцы и обратно

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

  1. Выделите диапазон.
  2. Скопируйте его (Ctrl + C) или, нажав на правую кнопку мыши, выберите «Копировать» (Copy).
  3. Щёлкните правой кнопкой мыши по ячейке, куда хотите вставить данные, и выберите в контекстном меню один из вариантов специальной вставки — значок «Транспонировать» (Transpose).

ezgif.com-save (2)

В старых версиях Excel нет такого значка, но можно решить проблему с помощью специальной вставки (Ctrl + Alt + V) и выбора опции «Транспонировать» (Transpose).

Выпадающий список в ячейке

Выпадающий список в ячейке

Если в какую-либо ячейку предполагается ввод строго определённых значений из разрешённого набора (например, только «да» и «нет» или только из списка отделов компании и т. д.), то это можно легко организовать при помощи выпадающего списка:

  1. Выделите ячейку (или диапазон ячеек), в которых должно быть такое ограничение.
  2. Нажмите кнопку «Проверка данных» на вкладке «Данные» (Data — Validation).
  3. В выпадающем списке «Тип» (Allow) выберите вариант «Список» (List).
  4. В поле «Источник» (Source) задайте диапазон, содержащий эталонные варианты элементов, которые и будут впоследствии выпадать при вводе.

Выпадающий список в ячейке

Продвинутые трюки из той же серии: выпадающий список с наполнением, связанные выпадающие списки, выпадающий список с фотографией и т. д.

“Умная” таблица

«Умная» таблица

Если выделить диапазон с данными и на вкладке «Главная» нажать «Форматировать как таблицу» (Home — Format as Table), то наш список будет преобразован в «умную» таблицу, которая (кроме модной полосатой раскраски) умеет много полезного:

  • Автоматически растягиваться при дописывании к ней новых строк или столбцов.
  • Введённые формулы автоматом будут копироваться на весь столбец.
  • Шапка такой таблицы автоматически закрепляется при прокрутке, и в ней включаются кнопки фильтра для отбора и сортировки.
  • На появившейся вкладке «Конструктор» (Design) в такую таблицу можно добавить строку итогов с автоматическим вычислением.

Спарклайны

Спарклайны — это нарисованные прямо в ячейках миниатюрные диаграммы, наглядно отображающие динамику наших данных. Чтобы их создать, нажмите кнопку «График» (Line) или «Гистограмма» (Columns) в группе «Спарклайны» (Sparklines) на вкладке «Вставка» (Insert). В открывшемся окне укажите диапазон с исходными числовыми данными и ячейки, куда вы хотите вывести спарклайны.

Спарклайны

После нажатия на кнопку «ОК» Microsoft Excel создаст их в указанных ячейках. На появившейся вкладке «Конструктор» (Design) можно дополнительно настроить их цвет, тип, включить отображение минимальных и максимальных значений и т. д.

Восстановление несохранённых файлов

Пятница. Вечер. Долгожданный конец ударной трудовой недели. Предвкушая отдых, вы закрываете отчёт, с которым возились последнюю половину дня, и в появившемся диалоговом окне «Сохранить изменения в файле?» вдруг зачем-то жмёте «Нет».

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

На самом деле, есть неслабый шанс исправить ситуацию. Если у вас Excel 2010, то нажмите на «Файл» — «Последние» (File — Recent) и найдите в правом нижнем углу экрана кнопку «Восстановить несохранённые книги» (Recover Unsaved Workbooks). В Excel 2013 путь немного другой: «Файл» — «Сведения» — «Управление версиями» — «Восстановить несохранённые книги» (File — Properties — Recover Unsaved Workbooks). Откроется специальная папка из недр Microsoft Office, куда на такой случай сохраняются временные копии всех созданных или изменённых, но несохранённых книг.

Восстановление несохранённых файлов

Сравнение двух диапазонов на отличия и совпадения

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

  1. Выделите оба сравниваемых столбца (удерживая клавишу Ctrl).
  2. Выберите на вкладке «Главная» — «Условное форматирование» — «Правила выделения ячеек» — «Повторяющиеся значения» (Home — Conditional formatting — Highlight Cell Rules — Duplicate Values).
  3. Выберите вариант «Уникальные» (Unique) в раскрывающемся списке.

Восстановление несохранённых файлов

Бонус: другие способы сравнить два диапазона и найти отличия/совпадения.

Подбор (подгонка) результатов расчёта под нужные значения

Вы когда-нибудь подбирали входные значения в вашем расчёте Excel, чтобы получить на выходе нужный результат? В такие моменты чувствуешь себя матёрым артиллеристом, правда? Всего-то пара десятков итераций «недолёт — перелёт», и вот оно, долгожданное «попадание»!

Microsoft Excel сможет сделать такую подгонку за вас, причём быстрее и точнее. Для этого нажмите на вкладке «Вставка» кнопку «Анализ „что если“» и выберите команду «Подбор параметра» (Insert — What If Analysis — Goal Seek). В появившемся окне задайте ячейку, где хотите подобрать нужное значение, желаемый результат и входную ячейку, которая должна измениться. После нажатия на «ОК» Excel выполнит до 100 «выстрелов», чтобы подобрать требуемый вами итог с точностью до 0,001.

Подбор (подгонка) результатов расчёта под нужные значения

Если этот подробный обзор охватил не все полезные фишки MS Excel, о которых вы знаете, делитесь ими в комментариях!

Андрей Алексеенко
2014-07-10 19:33:28
у меня "Анализ Что если?" не на вкладке Вставка, на вкладке Данные
Алексей Черненко
2014-07-10 20:58:20
вместо протягивания формулы вниз (схватив за правый нижний угол) использую двойной клик по этому углу.протягивает до первой пустой ячейки слева.
Oleg Bondarenko
2014-07-10 21:15:57
Все еще нужна помощь в поиске функций на ленте
Денис Байгозин
2014-07-10 22:16:16
Из-за нее до сих пор сижу на 2003-ем. Кастомизация кнопок важнее накрашеннх ногтей .
Oleg Bondarenko
2014-07-10 22:36:52
И на ОпенЛибраОфис, кстати.
Игорь
Игорь
2014-07-10 23:08:58
Не первый год работаю с Excel. За добавление данных в диаграмму и "Что если" спасибо! Мгновенное заполнение - круть, но только из-за этого переходить на 2013 не хочу.
Nyck
2014-07-10 23:34:21
этот список куда интерейсней, чем предыдущий пост про Word. общий совет для всех программ: учите комбинации клавиш. они на порядок ускоряют работу. плюс рекомендую заглянуть на ресурс "планета Excel" там много шикарных советов
Nata
2014-07-11 06:58:01
Спасибо за полезную инфу. Пригодится в работе.
Руслан Черненко
2014-07-11 10:36:21
Советы для чайников! Excel на много круче, многообразнее и интереснее. Может просто я сижу за ним до 10 часов в день и эта статья для меня не откровение?
Илья Селин
2014-07-11 10:52:37
Уже воспользовался функцией условного форматирования, сэкономил немало времени. Очень полезная статья, спасибо!
Me Ku
2014-07-11 11:25:31
Ниужели ничего нового?
Руслан Черненко
2014-07-11 11:29:07
Совсем ничего
Марина Меламуд
2014-07-11 13:52:19
Действительно, век живи - век учись.... Спасибо!
Эдуард Агиян
2014-07-11 14:04:21
Спасибо:)
Артем Суровцев
2014-07-11 14:16:05
Еще очень полезный функции для частого использования - удаление дубликатов (в разделе Данные) позволяет оставить только уникальные записи по столбцу или диапазону и разбиение данных из одной ячейки на несколько, например через пробелы или знаки препинания
Оксана Запевалова
Оксана Запевалова
2014-07-11 15:17:18
Рады, что для многих советы оказались полезны)
Nurba 99
2014-07-12 15:42:46
Ну так. поделись .Если ты профи - а там посмотрим.
Не Подарок
2014-07-13 03:16:41
Полезные очень подсказки!!! Думаю для кого-то будет не лишним узнать, что пользуясь Эксэлем на русском, после изучения и работы его на английском, понять что имеется ввиду не так просто. Собственный опыт меня вверг в ужас, когда я приехал на полгода работать в Москву и готовил документы на Эксэль и в Ворд на компах работодателя. Пока известные мне иконки совпадали я более менее еще держался, хотя переводить взад и перёд занимает время и кучу неудобства. К своему ужасу или позору, многие русские слова я не знал и не мог переложить с лёгкостью на мою английскую базу, что жутко нервировало. В результате я плюнул и стал делать всё на своём лаптопе с англо-язычной версией, что привело к трудностям моих коллег, что-либо добавлять в моих документах или работать с ними. Это я к тому пишу, что взаимо-зачёта не получится и надо иметь две версии и "играть" с ними постоянно, если конечно в этом есть профессиональная необходимость.
Yaroslav Tselikovskiy
2014-07-13 15:21:26
Коллега 2 недели из общей спецификации делал сводную вручную. Я ему показал сводную таблицу экселя и за 10 мин все сделалось... думал он меня убъет..
Nikolay Pavlov
2014-07-13 16:15:51
Не за что. Рад, что смог помочь.
Nikolay Pavlov
2014-07-13 19:05:28
Очень рекомендую скачать интерактивное флэш-руководство http://office.microsoft.com/ru-ru/support/HA101794130.aspx Тыкаете кнопку в старом интерфейсе (в меню или на панелях инструментов) и мультик покажет вам, где она теперь находится в новом (на ленте).
Nikolay Pavlov
2014-07-13 19:09:38
Да, перевод местами странный: ВПР = VLOOKUP ЧИСТРАБДНИ = NETWORKDAYS СЖПРОБЕЛЫ = TRIM и т.д. Когда ведешь тренинги, то постоянно приходится прыгать с версии на версию и это весьма напрягает. Именно поэтому я всегда в статьях все команды дублирую для русского и англоязычного Excel.
Nikolay Pavlov
2014-07-13 19:22:17
Спасибо :)
Nikolay Pavlov
2014-07-13 19:25:36
В Excel 2013 кроме мгновенного заполнения еще много всего появилось. Одно построение сводных сразу по нескольким таблицам чего стоит (не нужно предварительно связывать их ВПР) - см. http://www.planetaexcel.ru/techniques/8/158/
Игорь
Игорь
2014-07-13 19:55:16
Благодарю! Давно на этот сайт не заглядывал.
Oleg Bondarenko
2014-07-13 20:16:27
Спасибо. Разослал коллегам)
Misterious Being
2014-07-14 06:04:36
Вот еще бы меню там появилось. Из хорошей программы сделали чёрти что. С меню работать гораздо удобнее чем прыгать по закладкам.
Nikolay Pavlov
2014-07-14 08:35:38
Привычка - сильная штука, конечно, но могу сказать по личному опыту, что новички ленточный интерфейс осваивают заметно легче и быстрее.
Ольга Балычева Реброва
2014-07-14 14:43:54
))))))))), такие еще есть? Начальство много не знает по видимому).
Nikolay Pavlov
2014-07-14 14:51:53
О, еще как есть! На каждом тренинге вижу минимум пару человек, для которых сводные становятся открытием дня (или года - кому как). Эмоции из серии "где ж вы были раньше", порой, весьма бурные.
Yaroslav Tselikovskiy
2014-07-14 16:29:12
Я тоже когда-то был молодым и думал, что любую беду можно решить максимум скрпитом VBA. Сейчас я понимаю, что именно на таких все и держится. Знания компа, конечно очень помогают, но не являются определяющими в работе.
Vladimir Kondratiev
2014-07-14 16:43:14
Спасибо, полезно. Для What If в 2010 версии путь другой, не Insert, а Data.
Анжей Муркотам
2014-07-15 09:44:21
Спасибо. Нормально сделано. а по гугл доксам нечто подобное будет? все таки сейчас время "облачных решений", я не указываю майкрософтовское облако, там то все понятно.
Андрей Андреев
2014-07-16 08:54:55
Не всегда работает корректно если есть фильтр по столбцам, будьте внимательны.
Андрей Андреев
2014-07-16 09:00:44
Большое спасибо Николаю, многому научился именно благодаря его сайту. p.s а кнопка транспонировая таки есть в 2010м )
Evgenia Loginova
2014-07-16 12:08:50
А в меня можно кинуть инструкцией по сводной таблице? Плииизззз
Nikolay Pavlov
2014-07-16 15:21:11
Легко. Ловите http://www.planetaexcel.ru/techniques/8/130/ Там даже обучающий видеоролик есть ;)
Nikolay Pavlov
2014-07-16 16:20:35
Не за что. Рад, что смог помочь.
Вера Колонтай
2014-07-16 18:46:51
Спасибо! Полезная информация!
Артем Кокорин
2014-07-17 15:38:30
Круто! О многом не знал! gif-картинки - супер!
Lilia Fatkulina
2014-07-17 19:24:33
Большое спасибо за полезную инфу. Кое-что я-таки использовала и ранее, и сейчас готова опробовать новенькое. Очень интересно. Хотелось бы почаще таких демонстрационных публикаций!
Denis Batyanov
2014-07-18 23:28:36
Всё верно, он никогда не был на вкладке Вставка
Denis Batyanov
2014-07-18 23:29:39
С фильтром протягивать не следует, тут вы правы.
Denis Batyanov
2014-07-18 23:32:58
Вы жестоки. Дайте ему прочесть это: http://perfect-excel.ru/publ/excel/svodnye_tablicy/vvedenie_v_svodnye_tablicy/5-1-0-40
Denis Batyanov
2014-07-18 23:39:47
Да, это вы напрасно. Лента меню гораздо удобнее старого меню Excel. Попробуйте и черех неделю вы не захотите вернуться с старому Excel меню!
Denis Batyanov
2014-07-18 23:42:23
Исчерпывающее сравнение двух списков в Excel можно получить тут: http://perfect-excel.ru/publ/excel/makrosy_i_programmy_vba/rasshirennoe_sravnenie_dvukh_kolonok/7-1-0-20
Denis Batyanov
2014-07-18 23:43:17
Почитать подробнее про умные таблицы: http://perfect-excel.ru/publ/excel/razlichnye_instrumenty_excel/umnye_tablicy/10-1-0-33
Svetlana Feiguine
2014-07-28 17:12:39
В 2013 удобно организовать всплывающие подсказки на приятном для пользователя языке - допустим, интерфейс на английском, а screen tip на русском, или наоборот
Андрей Андреев
2014-08-01 17:12:55
Сама протяжка корректна. не корректен именно даблклик =)
Alin Lyrviss
2014-08-19 01:06:59
За восстановление несохраненных файлов Вам ОГРОООМНОЕ ЧЕЛОВЕЧЕСКОЕ СПАСИБО!!! И нечеловеческое тоже:)
Юлия Артамонова
2014-10-26 00:02:04
Большое спасибо!
Andrey Lezhnetyov
2015-10-02 12:35:35
Для вставки значений (например) из браузера в Excel (не только в Excel) без форматирования - можно использовать Ctrl+Win+V. Также можно вынести кнопку вставки только значений на Панель быстрого доступа, чтобы копировать данные с одного листа и без форматирования вставлять в другом месте не нарушая форматирование. Очень спасает и экономит время.?