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

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

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

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

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

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

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

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

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

В Excel можно быстро отобразить на интерактивной карте ваши геоданные, например продажи по городам. Для этого нужно перейти в «Магазин приложений» (Office Store) на вкладке «Вставка» (Insert) и установить оттуда плагин «Карты Bing» (Bing Maps). Это можно сделать и по прямой ссылке с сайта, нажав кнопку Get It Now.

После добавления модуля его можно выбрать в выпадающем списке «Мои приложения» (My Apps) на вкладке «Вставка» (Insert) и поместить на ваш рабочий лист. Останется выделить ваши ячейки с данными и нажать на кнопку Show Locations в модуле карты, чтобы увидеть наши данные на ней. При желании в настройках плагина можно выбрать тип диаграммы и цвета для отображения.

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

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

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

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

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

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

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

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

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

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

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

9. Спарклайны

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

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

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

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

На самом деле есть шанс исправить ситуацию. Если у вас Excel 2010, то нажмите на «Файл» → «Последние» (File → Recent) и найдите в правом нижнем углу экрана кнопку «Восстановить несохранённые книги» (Recover Unsaved Workbooks).

В Excel 2013 путь немного другой: «Файл» → «Сведения» → «Управление версиями» → «Восстановить несохранённые книги» (File — Properties — Recover Unsaved Workbooks).

В последующих версиях Excel следует открывать «Файл» → «Сведения» → «Управление книгой».

Откроется специальная папка из недр Microsoft Office, куда на такой случай сохраняются временные копии всех созданных или изменённых, но несохранённых книг.

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

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

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

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

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

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

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

Организация рабочего пространства с помощью изменения параметров и настроек в Excel. Пользовательское управление инструментами для удобной работы в программе.

Изменение параметров и настроек

Как создать новую вкладку и кнопки в Excel.Создание пользовательских вкладок и групп инструментов. Добавление кнопок, которых нет на ленте по умолчанию. Управление вкладками: добавление, удаление, скрытие и отображение.Настройка панели быстрого доступа в Excel.Управление и настройка панели быстрого доступа. Добавление большего количества кнопок и способы размещения панели в интерфейсе программы.Скрыть и показать ярлыки листов в Excel.Решить проблему отсутствия закладок на листы в Microsoft Excel очень просто. Нужно правильно настроить программу. Скрываем и отображаем листы на нижней панели в один клик.Как восстановить в Excel поврежденный файл или несохраненный.Восстановить поврежденные файлы можно самостоятельно с помощью возможностей MS Office или компьютера. Решается эта задача различными способами: извлечением данных, перемещением книги.Как закрепить строку и столбец в Excel при прокрутке.Инструмент закрепить области позволяет зафиксировать шапку таблицы, столбец, одновременно несколько строк и столбцов при горизонтальной или вертикальной прокрутке листа.Как убрать сетку в Excel 2010 полностью и частично.Два интересных решения для скрытия сетки ячеек листа. Скрыть в настройках и возможность скрывать отдельными частями сетки.Завершение работы в программе и закрытие окон рабочих книг в Excel.Особенности завершения работы. Закрытие книг и сессии программы. Инструмент для управления окнами.Сохранение книги Excel с эскизом в файл.Настройка программных средств для удобного сохранения документов. Понятие рабочей книги как файла. Особенности расширения xlsx.Полезные советы по открытию файлов рабочих книг в Excel.Настройка программы для удобного открытия файлов. Обзор возможностей функций используемых при открытии часто используемых документов.Быстрое перемещение курсора по ячейкам листа Excel.Быстрая навигация курсора по ячейкам листа с помощью горячих клавиш и других эффективных инструментов. Настройка направления перемещения курсора после нажатия ВВОД.125631621.06.2015 Скачать пример

Очень скоро нас с вами ждет очередная версия Excel 2016. На данный момент для всех желающих уже доступна бесплатная Technical Preview версия Office 2016 для ознакомления. Давайте посмотрим, что же нового и вкусного нам готовят в Редмонде.

Общий вид

Как видно из предыдущего скриншота, общий вид интерфейса не сильно изменился. Подложка ленты стала зеленой, а сама лента — серой и это, на мой взгляд, хорошо — активную вкладку видно более отчетливо и лента не сливается с листом, как это было в прошлом Excel. Названия вкладок попрощались с ПРОПИСНЫМИ — мелочь, а приятно.

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

… и угольно-черный:

Небогато для программы, на которую миллиард пользователей по всему миру пялятся по 5-10 часов в сутки иногда. По дизайну еще есть куда расти — это факт. (Прим. автора: я один устал от этого плоского безликого flat-design везде и кругом?)

Помощник

В правом верхнем углу экрана появилось поле Помощника. Это, своего рода, реинкарнация знаменитой Скрепки — быстрая встроенная поисковая система по всем функциям и инструментам Excel. В это поле можно начать вводить название команды или функции и Помощник оперативно выдаст список подсказок, которыми можно воспользоваться:

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

Надеюсь, что в будущем эта штука будет не просто искать в справке, а поддерживать голосовой ввод и понимать русскоязычную морфологию — тогда можно будет просто сказать Excel’ю что вы хотите сделать: «Сделай квартальный отчет по регионам и отправь его шефу!»

Новые типы диаграмм

Последний раз Microsoft добавляла новые типы диаграмм в Excel аж в 1997 году — почти 20 лет назад! И вот, наконец, лед в этом вопросе тронулся (не без дружеских пенделей разработчикам от участников сообщества MVP, скажу по секрету). В Excel 2016 появилось сразу аж 6 принципиально новых типов диаграмм, большинство из которых в старых версиях можно было построить только с помощью специальных надстроек или танцев с бубном. Теперь же все делается в два движения. Итак, встречайте:

Водопад (Waterfall Chart)

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

Иерархическая (Treemap Chart)

Специфический тип диаграммы для наглядного отображения распределения какого-либо параметра по категориям в виде эдакого прямоугольного «лоскутного одеяла». Причем можно использовать двойной уровень вложенности категорий (города внутри страны). Удобно использовать для визуализации, например, прибыли по регионам или выручки по категориям товаров. В старых версиях построение такой диаграммы было крайне затруднительно и обычно требовало установки дополнительных надстроек.

Солнечные лучи (Sunburst Chart)

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

Парето (Pareto Chart)

Классическая диаграмма для визуализации «закона 80/20» или «закона Парето», о котором многие, думаю, как минимум слышали. В общем виде он формулируется как «20% усилий дают 80% результата». Применительно к бизнесу, это уточняется до «20% товаров дают 80% выручки», «20% клиентов создают 80% проблем» и т.д. В такой диаграмме наглядно отображается гистограммой суммарная выручка по каждому товару и, одновременно, оранжевый график показывает накопленную долю выручки. Там, где линия пересекает 80% (около Ананаса) и можно мысленно провести вертикальную линию, чтобы отделить ключевые товары (слева от Ананаса) от неважных (справа от Ананаса). Мегаполезная диаграмма для ABC-анализа и подобных ему вещей.

Ящик с Усами (BoxPlot Chart)

• нижний (25%) и верхний (75%) квантили — нижняя и верхняя границы бокса
• выбросы — в виде отдельных точек

Частотная гистограмма (Histogram Chart)

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

Power Query

Надстройка для импорта данных Power Query, ранее поставлявшаяся для Excel 2013 отдельно, теперь встроена по-умолчанию. На вкладке Данные (Data) она представлена в виде группы Скачать & Преобразовать:

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

После загрузки полученные данные можно средствами Power Query еще и обработать, «доводя до ума»:

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

Сводные таблицы

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

Очень полезная штука, когда в вашей таблице десятки столбцов + вы еще и вычисляемых полей от себя добавили.

Во-вторых, если сводная таблица фильтруется Срезом или Шкалой, и вы делаете двойной щелчок по ячейке с данными, чтобы «провалиться» в подробности, то теперь при этом учитываются выбранные на срезах и шкалах параметры (раньше они игнорировались, как будто нет ни срезов, ни шкалы вообще).

Инструменты прогнозирования

Excel 2016 получил несколько новых инструментов для прогнозирования. Во-первых, в категории Статистические (Statistical) появились функции для вычисления прогноза по методу экспоненциального сглаживания:

• ПРЕДСКАЗ.ETS.ДОВИНТЕРВАЛ — вычисляет доверительный интервал для прогноза
• ПРЕДСКАЗ.ETS.СЕЗОННОСТЬ — выявляет сезонность в данных и вычисляет ее период
• ПРЕДСКАЗ.ETS.STAT — выдает подробные статистические данные по числовому ряду для вычисляемого прогноза

Также появился удобный инструмент для составления прогнозов «на лету» — кнопка Лист прогноза на вкладке Данные (Data):

Если выделить исходные данные (периоды или даты и значения) и нажать эту кнопку, то мы увидим вот такое окно:

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

Приятная штука. Раньше мы, например, на тренинге по прогнозированию делали такое вручную «от» и «до» — и занимало это очень прилично времени.

Также в этой версии несколько привычных математических и статистических функций переехало в категорию Совместимость (Compatibility), т.к. вместо них появились их более совершенные «потомки».

Итоговые выводы

Хорошо, что появились, наконец, новые типы диаграмм, которые все давно ждали, но есть еще куда расти — за кадром остались проектные диаграммы (Gantt, Timeline), диаграммы-шкалы («термометры») и т.д. Про то, что спарклайнов давно можно было сделать не три типа, а существенно больше, как в оригинале — тоже молчу.

Приятно, что в программу по-умолчанию встраивают полезные надстройки (Power Query, Power Pivot), но можно было бы тогда уже и на Fuzzy Lookup с Power Map расщедриться. К сожалению, пока — нет.

И лично мне жаль, что мы так и не увидим, похоже, в новой версии Excel 2016 ни продвинутых инструментов для работы с диапазонами (сравнение диапазонов, например), ни улучшений в среде программирования Visual Basic (которую с 1997 года не меняли), ни новых функций типа ВПР2 или СуммыПрописью.

Надеюсь дожить до момента, когда это все в Excel появится, а пока что придется пользоваться привычными костылями.

Используемые источники:

• https://lifehacker.ru/uskorennaja-rabota-v-excel/
• https://exceltable.com/nastroyki/
• https://www.planetaexcel.ru/techniques/11/227/