Oc-windows.ru

IT Новости из мира ПК
6 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Конструктор в excel 2020

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

Как быстро добавить данные, создать умную таблицу или спасти несохранённый файл.

Автор проекта «Планета Excel», разработчик и IT-тренер.

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

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

Microsoft Excel

трюки • приёмы • решения

Режим конструктора и элементы ActiveX в VBA Excel

В наших статьях мы подробно рассмотрим практические примеры, связанные с использованием программирования, которое заложено в Microsoft Office. Оно основывается на языке VBA, название которого в полном виде выглядит так Visual Basic for Application. Учитывая, что все рассматриваемые разработки будут использовать VBA, наша первоочередная задача познакомиться сданным языком.

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

Рис. 1.4. Закладка Основные окна Параметры Excel

Предполагается, что читатель хотя бы в незначительной степени знаком с чисто пользовательскими возможностями Microsoft Excel прошлых версий. Важно отметить, что наибольший эффект будет наблюдаться, если запустить Microsoft Excel параллельно с чтением наших статей и выполнять все описанные разработки на компьютере. При рассмотрении примеров на протяжении всего изучения нам понадобятся элементы ActiveX, которые по-другому можно назвать Элементы управления.

И в качестве первого шага необходимо в окне, показанном на рис. 1.2, щелкнуть кнопкой Параметры Excel. В результате перед вами откроется новое окно с набором закладок. Здесь на закладке Основные следует сделать установку для отображения на ленте вкладки Разработчик, что реализуется с помощью флажка Показывать вкладку «Разработчик» на ленте (рис. 1.4).

В Microsoft Excel элементы ActiveX можно размещать и на пользовательских формах, и на самих рабочих листах (на верхнем графическом слое, где располагаются рисунки, диаграммы и другие объекты). Часто вставка нескольких элементов ActiveX значительно упрощает работу с данными на рабочем листе. Все элементы управления делятся на две группы: элементы управления формами и элементы управления ActiveX. Оба набора элементов управления имеют свои преимущества и недостатки. В общем случае элементы управления формами проще в применении, но зато элементы ActiveX являются более гибкими. В наших примерах мы будем использовать исключительно элементы ActiveX.

В результате окно приложения Microsoft Excel дополнится новыми пиктограммами, которые нам потребуются в дальнейшем (рис. 1.5). Среди тех, которыми мы будем активно пользоваться на протяжении всей книги, отметим пиктограмму с надписью Вставить. С се помощью на рабочем листе можно размещать элементы ActiveX, которые существенно дополняют функциональность книг Microsoft Excel. Они типичны для различных приложений Windows (речь идет о кнопках, полосах прокрутки, текстовых окнах, переключателях, списках и т. д.). Кроме пиктограммы Вставить мы будем активно пользоваться и другими присутствующими на ленте Режим конструктора, Свойства, Visual Basic и Макросы.

Перед тем как начать какие-либо действия с тем или иным элементом ActiveX, его необходимо поместить на рабочий лист. Это легко выполнить, если воспользоваться пиктограммой Вставить. На рис. 1.6 показано размещение на рабочем листе элемента ActiveX Кнопка.

Рис. 1.5. Отображение вкладки Разработчик на ленте

При наведении курсора мыши на пиктограмму элемента ActiveX Кнопка в качестве подсказки появляется еще один вариант его названия: элемент управления «Кнопка». Далее по тексту мы будем использовать оба варианта названия — элементы ActiveX и элементы управления.

Для того чтобы перенести элемент ActiveX на рабочий лист, необходимо щелкнуть на панели инструментов на его пиктограмме левой кнопкой мыши и далее переместить курсор мыши на рабочий лист. Курсор мыши примет вид, аналогичный математическому знаку «плюс». Теперь следует нажать левую кнопку мыши и, не отпуская ее, переместить мышь вправо и вниз, а затем отпустить ее левую кнопку. В результате на рабочем листе мы увидим изображение появившегося элемента ActiveX, окруженного маркерами (рис. 1.6).

Рис. 1.6. Размещение кнопки на рабочем листе в режиме конструктора

После размещения элемента ActiveX на рабочем листе активизируется кнопка Режим конструктора, которая позволяет пользователю далее работать в одном из двух режимов. Один из них так и называется режим конструктора. В этом случае можно работать с элементами ActiveX для создания необходимого интерфейса на рабочем листе. Также в этом режиме пользователю предоставляется возможность создавать программные разработки.

Читать еще:  Ключевое поле excel

Появление маркеров (см. рис. 1.6), окружающих элемент ActiveX, как раз и является признаком того, что мы работаем в режиме конструктора (кнопка Режим конструктора в этом случае выглядит нажатой). На начальном этапе создания разработки работа в режиме конструктора сводится к размещению элементов управления на рабочем листе и изменению их свойств. Далее производится программирование различных событий, связанных с элементами управления, листами и книгой в целом.

Важно отметить, что после размещения элемента ActiveX на рабочем листе, в нашей книге (можно даже сказать — на данном листе книги) появился новый элемент, который фактически представляет собой «программный» объект. Аналогичным образом на рабочих листах книг Excel можно размещать и другие элементы ActiveX.

Рис. 1.7. Вид рабочего листа при выходе из режима конструктора

Другой режим можно назвать режимом выполнения или режимом работы (какого-то однозначно принятого названия не существует). Чтобы перейти в этот режим (то есть выйти из режима конструктора), необходимо отжать кнопку Режим конструктора (рис. 1.7). После этого в нашем случае можно просто пощелкать кнопкой, которую мы создали на рабочем листе (функциональные действия, которые можно обеспечить в этом случае, мы рассмотрим в дальнейшем). Далее опять вернемся в режим конструктора. С помощью маркеров, окружающих кнопку, можно легко изменить се размеры. Также с помощью мыши можно перемещать созданную кнопку по рабочему листу.

Microsoft Excel

трюки • приёмы • решения

Режим конструктора и элементы ActiveX в VBA Excel

В наших статьях мы подробно рассмотрим практические примеры, связанные с использованием программирования, которое заложено в Microsoft Office. Оно основывается на языке VBA, название которого в полном виде выглядит так Visual Basic for Application. Учитывая, что все рассматриваемые разработки будут использовать VBA, наша первоочередная задача познакомиться сданным языком.

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

Рис. 1.4. Закладка Основные окна Параметры Excel

Предполагается, что читатель хотя бы в незначительной степени знаком с чисто пользовательскими возможностями Microsoft Excel прошлых версий. Важно отметить, что наибольший эффект будет наблюдаться, если запустить Microsoft Excel параллельно с чтением наших статей и выполнять все описанные разработки на компьютере. При рассмотрении примеров на протяжении всего изучения нам понадобятся элементы ActiveX, которые по-другому можно назвать Элементы управления.

И в качестве первого шага необходимо в окне, показанном на рис. 1.2, щелкнуть кнопкой Параметры Excel. В результате перед вами откроется новое окно с набором закладок. Здесь на закладке Основные следует сделать установку для отображения на ленте вкладки Разработчик, что реализуется с помощью флажка Показывать вкладку «Разработчик» на ленте (рис. 1.4).

В Microsoft Excel элементы ActiveX можно размещать и на пользовательских формах, и на самих рабочих листах (на верхнем графическом слое, где располагаются рисунки, диаграммы и другие объекты). Часто вставка нескольких элементов ActiveX значительно упрощает работу с данными на рабочем листе. Все элементы управления делятся на две группы: элементы управления формами и элементы управления ActiveX. Оба набора элементов управления имеют свои преимущества и недостатки. В общем случае элементы управления формами проще в применении, но зато элементы ActiveX являются более гибкими. В наших примерах мы будем использовать исключительно элементы ActiveX.

В результате окно приложения Microsoft Excel дополнится новыми пиктограммами, которые нам потребуются в дальнейшем (рис. 1.5). Среди тех, которыми мы будем активно пользоваться на протяжении всей книги, отметим пиктограмму с надписью Вставить. С се помощью на рабочем листе можно размещать элементы ActiveX, которые существенно дополняют функциональность книг Microsoft Excel. Они типичны для различных приложений Windows (речь идет о кнопках, полосах прокрутки, текстовых окнах, переключателях, списках и т. д.). Кроме пиктограммы Вставить мы будем активно пользоваться и другими присутствующими на ленте Режим конструктора, Свойства, Visual Basic и Макросы.

Перед тем как начать какие-либо действия с тем или иным элементом ActiveX, его необходимо поместить на рабочий лист. Это легко выполнить, если воспользоваться пиктограммой Вставить. На рис. 1.6 показано размещение на рабочем листе элемента ActiveX Кнопка.

Рис. 1.5. Отображение вкладки Разработчик на ленте

При наведении курсора мыши на пиктограмму элемента ActiveX Кнопка в качестве подсказки появляется еще один вариант его названия: элемент управления «Кнопка». Далее по тексту мы будем использовать оба варианта названия — элементы ActiveX и элементы управления.

Для того чтобы перенести элемент ActiveX на рабочий лист, необходимо щелкнуть на панели инструментов на его пиктограмме левой кнопкой мыши и далее переместить курсор мыши на рабочий лист. Курсор мыши примет вид, аналогичный математическому знаку «плюс». Теперь следует нажать левую кнопку мыши и, не отпуская ее, переместить мышь вправо и вниз, а затем отпустить ее левую кнопку. В результате на рабочем листе мы увидим изображение появившегося элемента ActiveX, окруженного маркерами (рис. 1.6).

Рис. 1.6. Размещение кнопки на рабочем листе в режиме конструктора

После размещения элемента ActiveX на рабочем листе активизируется кнопка Режим конструктора, которая позволяет пользователю далее работать в одном из двух режимов. Один из них так и называется режим конструктора. В этом случае можно работать с элементами ActiveX для создания необходимого интерфейса на рабочем листе. Также в этом режиме пользователю предоставляется возможность создавать программные разработки.

Читать еще:  Рабочий лист ms excel это

Появление маркеров (см. рис. 1.6), окружающих элемент ActiveX, как раз и является признаком того, что мы работаем в режиме конструктора (кнопка Режим конструктора в этом случае выглядит нажатой). На начальном этапе создания разработки работа в режиме конструктора сводится к размещению элементов управления на рабочем листе и изменению их свойств. Далее производится программирование различных событий, связанных с элементами управления, листами и книгой в целом.

Важно отметить, что после размещения элемента ActiveX на рабочем листе, в нашей книге (можно даже сказать — на данном листе книги) появился новый элемент, который фактически представляет собой «программный» объект. Аналогичным образом на рабочих листах книг Excel можно размещать и другие элементы ActiveX.

Рис. 1.7. Вид рабочего листа при выходе из режима конструктора

Другой режим можно назвать режимом выполнения или режимом работы (какого-то однозначно принятого названия не существует). Чтобы перейти в этот режим (то есть выйти из режима конструктора), необходимо отжать кнопку Режим конструктора (рис. 1.7). После этого в нашем случае можно просто пощелкать кнопкой, которую мы создали на рабочем листе (функциональные действия, которые можно обеспечить в этом случае, мы рассмотрим в дальнейшем). Далее опять вернемся в режим конструктора. С помощью маркеров, окружающих кнопку, можно легко изменить се размеры. Также с помощью мыши можно перемещать созданную кнопку по рабочему листу.

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

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

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

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

  1. Выделить любую ячейку, содержащую данные, которые должны будут войти в таблицу.
  2. В ленте меню выбрать вкладку Вставка [Insert], в раскрывшейся группе команд Таблицы [Tables] необходимо выбрать команду Таблица [Table].

  1. Появится диалоговое окно, в котором Excel автоматически предложит границы диапазона данных для таблицы

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

  1. ОК.

Присвоение имени таблице

По умолчанию при создании таблицы Excel ей присваивается стандартное имя: Таблица1, Таблица2 и т.д. Если имеется только одна таблица, то можно ограничиться этим именем. Но удобнее присвоить таблице содержательное имя.

  1. Выделить ячейку таблицы.
  2. На вкладке Конструктор [Design], в группе Свойства [Properties] ввести новое имя таблицы в поле Имя таблицы нажать клавишу Enter.

Требования к именам таблиц аналогичны требованиям к именованным диапазонам.

Форматирование таблиц

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

  1. Выделить ячейку таблицы.
  2. На вкладке Конструктор [Design] выбрать нужное оформление в группе Стили таблиц [Table Styles].

Вычисления в таблицах

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

  1. На вкладке Конструктор [Design] в группе Параметры стилей таблиц [Table Style Options], выбрать Строка итогов [Total Row].

  1. В появившейся новой строке Итог [Total] выбрать поле, в котором нужно обработать данные, и в раскрывающемся меню выбрать нужную функцию.

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

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

Для проведения расчетов и помещения результатов в новое поле, достаточно ввести формулу в одну ячейку этого поля. Excel автоматически размножит ее по всем ячейкам данного поля. Если правильно установлены параметры Excel , при вводе формулы в нее записываются не адреса ячейки, а имена полей.

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

  1. Выбрать вкладку Файл [File] или кнопку Офис [Office], в зависимости от версии Excel; затем вкладку Параметры [Options].
  2. В разделе Формулы [Formulas], в группе Работа с формулами [Working with formulas], отметить пункт Использовать имена таблиц в формулах [Use table name in formulas].
  3. OK.

Преобразование таблицы в обычный диапазон

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

  1. На вкладке Конструктор [Design] выбрать группу Сервис [Tools].
  2. Выбрать вкладку Преобразовать в диапазон [Convert to Range].

  1. Нажать на кнопку Да [Yes].
Ссылка на основную публикацию
Adblock
detector