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.