Краткие теоретические сведения. Лабораторная работа N 6

Лабораторная работа N 6.

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

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

Продолжительность - 4 часа.

Содержание теоретического минимума

1. Расширенный фильтр.

2. Промежуточные итоги.

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

а) Создание сводных таблиц.

б) Изменение сводных таблиц.

в) Создание новых таблиц из сводных.

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

а) Виды макросов.

б) Способы создания макросов.

в) Места сохранения макросов в Excel.

г) Создание и сохранение макросов.

д) Создания макросов форматирующих ячейки.

е) Настройка меню и панелей инструментов.

ж) Удаление макросов.

Краткие теоретические сведения

1. Расширенный фильтр.

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

При использовании расширенного фильтра условия фильтрации задаются на рабочем листе. Основой расширенного фильтра является множество условий. Чтобы их задать, в начало таблицы вставляют строку с заголовками столбцов и несколько пустых строк. Это множество строк называется диапазоном условий отбора. Между значениями условий и списком должна находиться хотя бы одна пустая строка.

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

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

Использование расширенного фильтра:

1. Выделите место для диапазона условий отбора (вставьте несколько пустых строк) и скопируйте в него заголовки столбцов исходного списка.

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

3. Выделите любую ячейку исходного списка и вызовите команду Меню → Данные → Фильтр → Расширенный фильтр (2007:вкладка Данные → группа Сортировка и фильтр → Дополнительно). Откроется диалоговое окно «Расширенный фильтр».

4. Проверьте, правильно ли задана ссылка на диапазон ячеек исходного списка. Иначе щёлкните в поле «Диапазон условий» и с помощью мыши введите нужный диапазон.

5. Укажите ссылку на диапазон условий отбора в поле «Диапазон условий».

6. Выполните одно из действий:

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

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

Внимание! Убедитесь, что на листе достаточно места для копируемых данных.

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

7. Установите флажок «Только уникальные записи», чтобы исключить из результирующего списка одинаковые записи.

8. Щёлкните на кнопке ОК для выполнения фильтрации данных.

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

Для отключения расширенного фильтра выберите команду Меню → Данные → Фильтр → Отобразить всё (2007:вкладка Данные → группа Сортировка и фильтр → Очистить).

2. Промежуточные итоги.

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

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

Внимание! Для вычисления промежуточных итогов не требуется вставлять новые ячейки и вводить дополнительные формулы.

Для вычисления промежуточных итогов нужно:

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

2. Выделить любую ячейку в списке.

3. Вызвать команду Меню → Данные → Итоги (2007:вкладка Данные → группа Структура → Промежуточные итоги).

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

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

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

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

7. Если необходимо, можно установить или сбросить флажки «Заменить текущие итоги», «Конец страницы между группами» и «Итоги под данными».

При необходимости сохранить старые промежуточные итоги (например, когда необходимо получить одновременно минимальные и максимальные значения) сбросьте флажок «Заменить текущие итоги».

8. Щёлкнуть на кнопке ОК, чтобы сформировать промежуточные итоги.

Для удаления промежуточных итогов выберите команду Меню → Данные → Итоги (2007:вкладка Данные → группа Структура → Промежуточные итоги) и щёлкните на кнопке «Убрать всё».

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

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

а) Создание сводных таблиц.

Любая сводная таблица содержит четыре области: область страницы, область столбцов, область строк и область данных. Каждой из этих областей соответствует область макета таблицы в диалоговом окне «Макет» из «Мастера сводных таблиц и диаграмм» (см. создание сводной таблицы).

При создании сводной таблицы нужно:

1. Выделить любую ячейку в списке с анализируемыми данными.

2. Вызвать команду Меню → Данные → Сводная таблица (2007:вкладка Вставка → группа Таблицы → Сводная таблица). Откроется мастер сводных таблиц и диаграмм.

3. На первом шаге работы мастера выбрать тип исходных данных – список или отдельная база данных Microsoft Excel, внешний источник, или задать несколько источников или уже существующую сводную таблицу (2007: шаги 1,2 и частично 3 здесь совмещены).

Также если требуется, задайте построение диаграммы, связанной с создаваемой сводной таблицей. (По умолчанию мастер создаёт сводную таблицу без диаграммы.) Для открытия следующего окна мастера щёлкните на кнопке Далее.

Окно мастера сводных таблиц и диаграмм – шаг 1 для Office 2003

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

5. На третьем шаге работы мастера необходимо:

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

Окно мастера сводных таблиц и диаграмм – шаг 3 для Office 2003

· Щёлкнуть на кнопке Макет для открытия одноимённого диалогового окна для задания структуры и установки параметров создаваемой сводной таблицы. В котором:

¾ Перетащить кнопки полей в области макета таблицы. Необходимо разместить хотя бы по одному полю в области «Строка», «Столбец» и «Данные». Заполнять область «Страница» необязательно.

Кнопки с названием полей, которые содержат сопоставляемые данные, размещают в областях «Строка» и «Столбец».

Никаких ограничений на перемещение кнопок полей не существует.

Окно макета мастера сводных таблиц и диаграмм для Office 2003

¾ После перетаскивания кнопки поля в область «Данные» (по умолчанию используется в области функция СУММ), если необходимо изменить метод обработки этого поля, дважды щёлкните на кнопке поля в области данных – откроется диалоговое окно «Вычисление поля сводной таблицы». Укажите метод обработки в поле «Операция». Задать дополнительные вычисления можно с помощью щелчка на кнопке «Дополнительно». В открывшейся панели выберите функцию, а также поле и элемент, необходимые для вычислений. Щёлкните на кнопке ОК для возврата к диалоговому окну «Макет» мастера сводных таблиц и диаграмм.

Окно мастера сводных таблиц и диаграмм для Office 2007

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

¾ Для завершения работы по созданию макета сводных таблиц и для возвращения к третьему окну мастера щёлкните на кнопке ОК.

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

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

6. Щелчок на кнопке Готово обеспечит закрытие мастера и создание сводной таблицы.

Одновременно со сводной таблицей на экране откроется панель инструментов «Сводные таблицы», но если таковая не появится, открыть её возможно с помощью команды Меню → Вид → Панели инструментов → Сводные таблицы. Указанная панель инструментов предназначена для изменения внешнего вида и параметров созданной таблицы.

б) Изменение сводных таблиц.

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

Как можно преобразовать сводную таблицу:

¾ Путём перетаскивания кнопок из одной области в другую;

¾ Удалить одно из полей путём перетаскивания кнопки поля за пределы таблицы;

¾ Добавить поле, путём выделения его в списке полей сводной таблицы и перетаскивания его в нужную область.

в) Создание новых таблиц из сводных.

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

1. Убедиться, что кнопка поля по которому будут подводиться итоги, помещена в область «Страница».

2. Выделить ячейку в сводной таблице и щёлкнуть на ней правой кнопкой мыши и выбрать в контекстном меню команду «Отобразить детали» раздела «Группа и структура» или щёлкнуть на одноимённой кнопке панели инструментов.

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

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

Макрос – это набор инструкций, которые программа выполняет по команде запуска. Инструкции могут соответствовать простым нажатиям клавиш или сложным наборам команд меню. Макросы обычно создаются при необходимости в регулярном выполнении операций, которые повторяются в одном и том же порядке. Они записываются на языке программирования Visual Basic для приложений (for Applications).

а) Виды макросов.

Выделяют три основные разновидности макросов:

· Командные макросы, которые состоят из операторов, соответствующих тем или иным командам или параграфам диалоговых окон, записанных в определённом порядке. Основным предназначение таких макросов является изменение внешнего вида окна или объекта.

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

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

б) Способы создания макросов.

Создать макросы можно следующими способами:

1. Запустить встроенное средство автоматической записи макросов – макрорекордер и выполнять последовательность действий, которые необходимо включить в макрос. Затем остановить макрорекордер. Обычно это касается простых макросов.

2. Ввести коды макроса в окне редактора Visual Basic и ввести операторы VBA.

Обычно при создании сложных макросов объединяют два эти способа в один.

в) Места сохранения макросов в Excel.

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

Личная книга макросов (Personal Macro Workbook) – это книга, которая автоматически загружается при запуске Excel. Обычно она невидима и макросы из неё можно вызывать из любой рабочей книги.

Макросы, сохранённые в текущем документе, называются локальными. Глобальными называют макросы, сохранённые в личной книге макросов.

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

г) Создание и сохранение макросов.

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

Чтобы создать макрос первым способом нужно:

1. Запустить встроенное средство автоматической записи макроса. Меню → Сервис → Макрос → Начать запись. Откроется окно «Запись макроса».

Прежде чем работать с макросами в Microsoft Excel 2007 следует провести некоторый подготовительный этап. Для этого нажмите кнопку Office → кнопку « Параметры Excel» (внизу в правом углу) → слевараздел «Основные» → справагруппа «Основные параметры работы с Excel» → поставить галочку у пункта «Показывать вкладку “Разработчик” на ленте». Таким образом, на экране появиться новая вкладка «Разработчик». Теперь для того, чтобы начать автоматически записывать макрос (или запустить макрорекордер) нужно перейти на вкладку «Разработчик» → группа Код → Запись макроса.

2. Ввести имя макроса и краткое его описание в соответствующие поля диалогового окна «Запись макроса».

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

4. Задайте комбинацию клавиш для вызова макроса (если необходимо).

5. Щёлкните на кнопке ОК, и начнётся запись макроса.

6. Выполните действия, которые следует включить в макрос.

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

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

7. После выполнения всех шагов по вводу макроса щёлкните на кнопке «Остановить запись» на панели инструментов «Остановка записи».

Для запуска макроса нужно:

1. Вызвать команду Меню → Сервис → Макрос → Макросы (2007:вкладка Разработчик → группа Код → Макросы).

2. Выбрать в списке нужный макрос.

3. Щёлкнуть на кнопке Выполнить.

д) Создания макросов форматирующих ячейки.

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

е) Настройка меню и панелей инструментов.

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

Для добавления команды запуска макроса в меню или на панель инструментов необходимо:

1. Откройте документ, содержащий локальный макрос.

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

Или выберите команду Меню → Вид → Панели инструментов → Настройка.

3. В открывшемся диалоговом окне «Настройка», перейдите на вкладку «Команды».

4. Выберите в списке «Категории» элемент «Макросы».

5. Перетащите элемент «Настраиваемая команда меню» или «Настраиваемая кнопка» из списка «Команды» на панель инструментов или в меню и поместите их в нужное место.

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

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

7. Укажите нужный макрос в диалоговом окне «Назначить макрос» и щёлкните на кнопке ОК для назначения макроса новой команде меню или кнопке.

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

8. Для изменения названия кнопки или командного меню, отредактируйте содержимое поля «имя» в контекстном меню.

В именах команд может использоваться символ амперсанда.

9. Закройте диалоговое окно «Настройка».

Для Microsoft Excel 2007: вкладка Разработчик → группа Элементы управления → кнопка «Вставить» → раздел «Элементы управления формы» → выбрать кнопку «Элемент управления формы» и нарисовать кнопку нужного размера. После открытия окна «Назначить макросу объект», выбрать нужный макрос и нажать кнопку ОК).

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

ж) Удаление макросов.

Удалить макрос можно двумя способами:

1) Переписать макрос заново под тем же именем, если не устраивает работа первоначального;

2) Вызвать команду Меню → Сервис → Макрос → Макросы (2007:вкладка Разработчик → группа Код → Макросы) → выбрать необходимый макрос и щёлкнуть на кнопке Удалить.