Материал: ЭКСЕЛЬ2 2013 Методичка Расширенные возможности

86

Microsoft Excel 2013. Уровень 2. Расширенные возможности

Проверка вводимых значений

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

С помощью проверки данных можно упростить ввод повторяющихся данных, сформировав из них выпадающий список.

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

Установка ограничений на ввод данных

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

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

1.Выделить диапазон ячеек.

2.На вкладке Данные [Data], в группе Работа с данными [Data Tools], выбрать

Проверка данных [Data Validation].

3.В диалоговом окне Проверка вводимых значений [Data Validation] на вкладке Параметры

[Option], задать Условие проверки [Validation criteria] – выбрать тип данных и поставить ограничение на вводимые значения.

Любое значение [Any value] – нет ограничений на ввод данных.

Целое число [Whole number] – ввод только целых чисел.

Действительное [Decimal] – ввод как целых, так и дробных чисел.

Дата [Date] – ограничение на ввод даты.

Время [Time] – ввод только времени.

Длина текста [Text length] –

ограничение на количество вводимых символов.

Другой [Custom] - предоставляется возможность более широко контролировать ввод данных, условие проверки задается с помощью формулы.

Список [List] – ввод данных из предлагаемого списка (нет ограничений по типу данных). Максимум может содержать 32767 элементов. Источник [Source] списка можно:

Ввести с клавиатуры, разделяя через точку с запятой ; (разделитель зависит от региональных настроек – может быть запятая , ).

87

Microsoft Excel 2013. Уровень 2. Расширенные возможности

Выделить ячейки с любого листа текущей книги.

Выбрать именованный диапазон с помощью клавиши F3 .

Применив типы проверок Целое число или Действительное, внести в ячейку можно будет только число. Однако стоит помнить, что даты и время Excel воспринимает и хранит именно как числовые значения, поэтому ввод даты и времени тоже будет разрешен, если он не противоречит остальным условиям проверки.

4.На вкладке Сообщение для ввода [Input Message] ввести подсказку об ограничениях при вводе данных. Подсказка будет отображаться в активной ячейке при условии, что выбран параметр Отображать

подсказку, если ячейка является текущей [When input message when cell is selected].

5.На вкладке Сообщение об ошибке

[Error Alert] в поле Вид [Style], выбрать:

Останов [Stop] – запрет ввода неверных данных, появляется сообщение об ошибке.

Предупреждение [Warning] – ввод неверных данных допускается, при подтверждении пользователем – Да

[Yes].

Сообщение [Information] – ввод неверных данных разрешается при подтверждении пользователем - ОК.

Сообщение об ошибке будет выводиться на экран, при условии, что установлен флажок Выводить сообщение об ошибке [Show error alert after invalid data is entered].

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

88

Microsoft Excel 2013. Уровень 2. Расширенные возможности

Поиск неверных данных

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

Для этого на вкладке Данные [Data] в группе Работа с данными [Data Tools], открыть список кнопки Проверка данных [Data Validation] и выбрать Обвести неверные данные [Circle Invalid Data].

На текущем листе все неверные данные будут обведены красным цветом. Программа только указывает неверные данные, задача пользователя – внести изменения.

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

Для принудительного удаления обводки неверных данных, надо на вкладке Данные [Data] в группе Работа с данными

[Data Tools], открыть список кнопки Проверка данных [Data Validation] и выбрать Удалить обводку неверных данных [Clear Validation Circles].

ПРАКТИКУМ:

В открытом файле Проверка данных.

На листе ЗАДАНИЕ выполнить задание

Сохранить изменения в файле и закрыть его.

САМОСТОЯТЕЛЬНЫЕ УПРАЖНЕНИЯ

Открыть файл УПРАЖНЕНИЯ ЭКСЕЛЬ2. Выполнить задание на листе 18.

89

Microsoft Excel 2013. Уровень 2. Расширенные возможности

Модуль 5. МАКРОСЫ

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

Макрос представляет собой набор команд – программу на языке VBA (язык программирования

Visual Basic for Applications).

Существует два способа создания макроса:

Первый способ – запись действий с помощью макрорекордера. Простой способ создания макроса, не требует навыков программирования. Запустив запись макроса, пользователь выполняет последовательность действий, а макрорекордер создает текст программы на VBA.

Второй способ – написание алгоритма действий, используя язык программирования VBA. Сложный способ написания макроса, требуются навыки программирования, но позволяет создавать более мощные и более гибкие программы. Для создания макроса таким способом следует запустить редактор VBA и писать алгоритм макроса «с чистого листа».

Необходимо помнить, что книга Excel (*.xlsx) не может содержать макросы, поэтому следует сохранить файл в нужном формате: Книга Excel с поддержкой макросов (*.xlsm) [Excel MacroEnabled Workbook] или Двоичная книга Excel (*.xlsb) [Excel Binary Workbook].

Для работы с макросами необходимо отобразить вкладку Разработчик [Developer].

1.Выбрать Файл [File], Параметры [Options].

2.В разделе Настройка ленты [Customize Ribbon]в группе Настроить ленту [Customize Ribbon], выбрать Разработчик [Developer].

3.ОК.

Запись макросов

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

1. Запустить макрорекордер для записи макроса на вкладке Разработчик [Developer], в группе

Код [Code], нажать кнопку Запись макроса [Record Macro].

2.В диалоговом окне Запись макроса [Record Macro]:

в поле Имя макроса [Macro Name] ввести имя. Имя макроса может начинаться с буквы или подчеркивания, затем могут следовать буквы, цифры, подчеркивание. Недопустимо использовать пробелы. Имя не может совпадать с именем или адресом ячейки.

90

Microsoft Excel 2013. Уровень 2. Расширенные возможности

Сочетание клавиш [Shortcut key] (не обязательно) – можно назначить в сочетании с клавишей Ctrl любую букву

для быстрого запуска макроса с клавиатуры. Назначенное сочетание клавиш будет заменять при совпадении стандартное сочетание клавиш до тех пор, пока открыта книга с макросом.

в списке Сохранить в [Store macro in]

выбрать место сохранения макроса:

Новая книга [New Workbook] – создается

книга, вместе с которой будет сохранен макрос. Для доступа к макросу – открыть книгу.

Эта книга [This Workbook] – макрос сохраняется в текущей книге. Для доступа из других книг надо открыть книгу с макросом.

Личная книга макросов [Personal Macro Workbook] – макрос будет доступен в любой книге на этом компьютере. В этом случае макрос записывается в скрытую личную книгу макросов Personal.xlsb.

в поле Описание [Description] ввести текст – комментарий к действиям макроса (не обязательно)

ОК.

3.Выполнить набор действий, которые нужно записать.

Если макрос делается для разных диапазонов, то перед выполнением действия надо нажать кнопку Относительные ссылки [Use Relative References] в группе Код [Code]

вкладки Разработчик [Developer].

Если макрос будет применяться к одним и тем же ячейкам, то кнопку Относительные ссылки [Use Relative References] нажимать не надо.

4.Остановить запись – на вкладке Разработчик [Developer], в группе Код [Code], нажать кнопку

Остановить запись [Stop Recording].

ПРАКТИКУМ:

Открыть файл 05 Источник.

Сохранить файл с именем 05 Макросы и типом Книга Excel с поддержкой макросов.

На рабочем листе ЗАДАНИЕ1 выполнить задание «запись макроса».

Сохранить изменения в файле и закрыть его.

Параметры безопасности для работы с макросами

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