Умные Таблицы Excel – секреты эффективной работы

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

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

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

Сегодня мы поговорим о том, какие бывают свойства у документа, и как можно их просматривать и редактировать в Excel 2010 и 2013. Прочитав эту статью, Вы также узнаете,…

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

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

Заголовки (числовые – для строк и буквенные – для столбцов) на листах Excel помогают легко просматривать данные и ссылаться на них. Однако, в некоторых случаях заголовки отвлекают, и…

Вероятно, до настоящего момента Вы никогда не задумывались о том, какого цвета линии сетки в Excel, но если Вам наскучил стандартный серый цвет линий или Вы хотите использовать…

Вам нравится последняя версия пакета Microsoft Office? Если вы установили ее, то, несомненно, заметили все эти новые функции и усовершенствования, которые были добавлены.

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

В последнее время все больше людей работают в Excel с планшетных компьютеров и прочих устройств с сенсорным экраном. Это доставляет определенные неудобства, поскольку стандартный интерфейс Excel рассчитан в…

Не всем пользователям Microsoft Excel удобно работать с предустановленными на Ленте вкладками. Иногда гораздо практичнее создать свою собственную вкладку с требуемым набором команд. В данном уроке мы покажем…

По умолчанию 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, о которых вы знаете, делитесь ими в комментариях!

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

• https://exceltable.com/nastroyki/
• https://office-guru.ru/excel/interfeis-nastroika
• https://lifehacker.ru/uskorennaja-rabota-v-excel/