Файл: Лабораторный практикум в Excel.doc

Далее выбрать нужную функцию (максимум):

Получим сводную таблицу

В сводной таблице, есть возможность оставлять только нужную информацию, используя простую фильтрацию (это видно, по расположенной стрелочке выбора, возле поля СТРАНА и поля ПРОФЕССИЯ), например, оставим только данные страны РОССИЯ:

Получим:

Лабораторная работа № 9
Использование элементов управления и макросов

Использование в таблицах элементов управления и макросов позволяют упростить работу пользователя и в процессе работы избежать ошибок.

Макрос – последовательность команд, которые выполняются автоматически после запуска макроса. Запустить макрос можно или нажатием соответствующих клавиш, или с помощью элементов управления (кнопка, переключатель и т.д.).

ВНИМАНИЕ: действие макроса нельзя отменить с помощью команды «Отменить…»

Задание:

1. Поместить на лист элементы управления:

- «счетчик» для установки постоянных данных;

- «кнопка» для выполнения операций: различные виды сортировки, работа со строчками, подведение итогов и удаление итогов;

- «поле со списком» для выбора условия фильтрации;

- «переключатель» для выполнения фильтрации.

2. Создать макросы для выполнения следующих операций:

- сортировка: по тестовому столбцу, по числовому столбцу, по двум столбцам;

- вставка строчки, копирование строчки, удаление строчки;

- показать итоги, убрать итоги;

- выполнить фильтрацию, показать все.

Методические указания по выполнению работы.

1. Сделать копию листа лабораторной работы № 5, удалить результат фильтрации и оформить в соответствии с Приложением 3.

2. Увеличить высоту строк, в которых будут вставлены элементы управления (счетчики).

3. Добавить 4 дополнительные строки для размещения в них элементов управления для выполнения сортировки, работы со строчками, подведения итогов, фильтрации.

4. Расположить на панели инструментов группу «Разработчик», для этого при работе в Excel 7

• Откройте вкладку Offyce

• выбрать Параметры Excel

• установить галочку в окне Показывать вкладку «Разработчик» на ленте.
  
  

ПРИМЕЧАНИЕ. Копию листа помещать в отчет как точечный рисунок.

Для расположения группы «Разработчик»на ленте», в Excel большей версии:

• Откройте вкладку Файл

• Выберите пункт Параметры

• Выберите пункт Настроить ленту

• В разделе Настройка ленты в списке Основные вкладки установите флажок Разработчик

Счетчик

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

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

2. Щелкнуть по счетчику правой кнопкой мыши и в контекстном меню выбрать команду «Формат объекта».

3. Установить «Минимальное значение», «Максимальное значение», «Шаг изменения» исходя из условия задачи. Перечисленные параметры имеют только целые положительные значения.

4. В поле «Связь с ячейкой» указать ячейку, в которую будут помещены данные.

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

1. Десятичные дроби

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

2. Отрицательное число

В поле «Максимальное значение» ввести число в двое больше абсолютного максимального значения данных. Во вспомогательную ячейку поместить результат счетчика, а в ячейку с данными поместить разность между вспомогательной ячейкой и абсолютным максимальным значением данных. Чтобы счет данных начинался с «0» в поле «Текущее значение» поставить абсолютное максимальное значение данных.

3. Проценты

Работать с процентами так же, как и с десятичными дробями, только формат ячейки должен быть «Процентный».

4. Дата

В EXCEL даты представляются числами от 1 до 65380. Число 1 соответствует 1 января 1900 года. Дате 1 января 2000 года соответствует номер 36526.

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

Создание макроса

Перед тем как, поместить на лист элемент управления «Кнопка» желательно создать макрос, который будет запускаться этой кнопкой.

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

1. На вкладке Разработчик в группе Код нажмите кнопку Безопасность макросов.

2. В группе Параметры макросов выберите переключатель Включить все макросы, а затем дважды нажмите кнопку ОК.

 На вкладке Разработчик в группе Код нажмите кнопку Запись макроса.

 В поле Имя макроса введите имя макроса.  

 Первый символ имени макроса должна быть буква. Последующие символы могут быть букв, цифр или знаков подчеркивания. Нельзя использовать пробелы в имени макроса; знак подчеркивания работает также в качестве разделителей слов.  Чтобы назначить сочетание клавиш с клавишей CTRL для запуска макроса, в поле Сочетание клавиш введите любую строчную или прописную букву.

   Выбранное сочетание клавиш заменяет все совпадающие стандартные сочетания клавиш Excel на то время, пока открыта книга, содержащая данный макрос.

 В списке Сохранить в выберите книгу, в которой нужно сохранить макрос.

 Введите описание макроса в поле Описание.

 Для начала записи макроса нажмите кнопку ОК.

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

 На вкладке Разработчик в группе Код нажмите кнопку Остановить запись.

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

Кнопка

Кнопка создается после записи макроса. На панели «Формы» нажать кнопку «Кнопка» и с помощью мыши поместить изображение кнопки в нужное место. После этого откроется окно «Назначить макрос», в списке макросов выбрать требуемый макрос и нажать кнопку «ОК». Кнопку переименовать.

Вставка строки.

1. Поставить курсор в любое место листа.

2. Включить запись макроса.

3. Поставить курсор в любую строчку таблицы кроме 1 и последней.

4. Выделить строчку в которой стоит курсор, нажав одновременно кнопки «Shift» и «пробел» (для выделения столбца надо нажать кнопки «Ctrl» и «пробел»).

5. Выполнить команду «Копировать», затем щелкнуть правой кнопкой мыши по выделенной строке и в контекстном меню выбрать команду «Добавить скопированные ячейки».

6. В новой строчке удалить из ячеек исходные данные (оставить только формулы) и поставить курсор в начало строчки.

7. Остановить запись макроса.

Копирование строки

1. Поставить курсор в строчку, которую надо скопировать.

2. Включить запись макроса.

3. На панели инструментов «Остановить запись» нажать кнопку «Относительная ссылка»

4. Выделить строчку в которой стоит курсор, нажав одновременно кнопки «Shift» и «пробел» (для выделения столбца надо нажать кнопки «Ctrl» и «пробел»).

5. Выполнить команду «Копировать», затем щелкнуть правой кнопкой мыши по выделенной строке и в контекстном меню выбрать команду «Добавить скопированные ячейки».

6. Остановить запись макроса.

Удаление строки

1. Поставить курсор в строчку, которую надо удалить.

2. Включить запись макроса.

3. На панели инструментов «Остановить запись» кнопка «Относительная ссылка»

должна быть нажата.

4. Выделить строчку в которой стоит курсор, нажав одновременно кнопки «Shift» и «пробел» (для выделения столбца надо нажать кнопки «Ctrl» и «пробел»).

5. Выполнить команду «Удалить».

6. Остановить запись макроса.

Поле со списком

Форма «Поле со списком» используется для выбора из списка данных с последующим их использованием.

1. Под таблицей создать область вспомогательных данных (на приведенном примере это таблица из двух столбцов «Индекс» и «Поставщик»).

2. На панели «Формы» нажать кнопку «Поле со списком» и с помощью мышки поставить изображение поля в нужное место.

3. Щелкнуть правой кнопкой мыши по полю со списком и в открывшемся контекстном меню выбрать команду «Формат объекта».

4. На вкладке «Элемент управления» окна «Формат элемента управления» выполнить следующие установки:

- в поле «Формировать список по диапазону» поместить ссылки на ячейки, данные из которых будут помещены в список (на приведенном примере это ячейки с данными столбца «Поставщик» основной таблицы);

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

• в поле «Количество строк списка» - количество элементов списка, которое будет появляться на экране одновременно.

Применение функции «ИНДЕКС»

Функция «ИНДЕКС» используется для поиска данных из массива данных по номеру строки и столбца. В данном примере «ИНДЕКС» используется для нахождения «Поставщика» по номеру строки.

1. Функцию «ИНДЕКС» поместить в ячейку столбца «Поставщик» во вспомогательной области данных.

2. Для вставки функции использовать мастер функций. В поле «Массив» вставить ссылки на те же ячейки, что и в поле «Формировать список по диапазону» (см. раздел «Поле со списком». В поле «Номер_строки» ссылка на ту же ячейку, что и в поле «Связь с ячейкой».

Переключатель

1. Создать макрос для выбора поставщика. Для выбора поставщика использовать расширенный фильтр. Диапазон условий в этом случае будет область вспомогательных данных.

2. На напели инструментов «Формы» нажать кнопку «Переключатель» и с помощью мышки поместить изображение переключателя в нужное место.

3. Щелкнуть правой кнопкой мыши по переключателю и в открывшемся контекстном меню выбрать команду «Назначить макрос».

4. В окне «Назначить макрос объекту» выбрать требуемый макрос.