Информатика_Excel_лаб_раб. Л. Г. Чернова пакеты прикладных программ раздел iii. Microsoft Excel лабораторный практикум новотроицк 2010 2 удк 681. 31 378 ббк 32.
 Скачать 1.91 Mb.
|
|
Excel Word - мощная многофункциональная система обработки текстов. С помощью Word можно напечатать приказ, оформить 89 служебную записку, подготовить научно-технический отчет, письмо или любой другой документ, содержащий диаграммы, таблицы, рисунки и т.д. Принятие решений сегодня невозможно без точной и своевременной информации, представленной в удобной для анализа и обработки форме. Это требует применения других приложений, входящих в состав пакета Microsoft Office, например Microsoft Excel для выполнения сложных вычислений, различных видов анализа и создания диаграмм. Использование нескольких приложений необходимо также для создания сложных составных документов, содержащих данные из различных источников. Примером составного документа может служить подготовленный в Word бухгалтерский отчет, который должен содержать данные о продажах на сегодняшний день. Эти данные находятся в электронной таблице Microsoft Excel. В нее постоянно заносятся актуальные данные, поэтому в отчете должны учитываться все изменения. С этой целью следует создать составной документ Word и включить в него соответствующий раздел электронной таблицы Excel, образовав связи между таблицей и отчетом. В результате при каждом изменении электронной таблицы будет автоматически изменяться отчет. С помощью средств создания сводных таблиц, предоставляемых Excel, пользователь может сгруппировать необходимые данные в соответствии со своими требованиями. Связывание и внедрение объектов Основное отличие между связыванием и внедрением обусловлено местом хранения информации. Внедрение означает включение данных электронной таблицы, диаграммы и т.д. в документ другого приложения, например Word. Будучи внедренным, объект становится частью документа Word. Связанные же объекты хранятся в исходном файле, а документ Word содержит только ссылку на местоположение исходного файла. 1 Связывание объектов 90 Чтобы создать связь между документом Word и сводной таблицей Excel, необходимо предварительно запустить оба приложения. Упражнение 5.7. На основе таблицы созданной в Microsoft Excel, рисунок 5.12, создать в Microsoft Word отчет об объеме продаж. Рисунок 5.12 – Сводная таблица Запустите Microsoft Word. Оформите заголовочную часть отчета, рисунок 5.13. Рисунок 5.13 – Заголовочная часть отчета Перейдите в Microsoft Excel и создайте таблицу, рисунок Выделите таблицу. Во вкладке Главная группа Буфер обмена Excel выберите команду Копировать, чтобы скопировать таблицу в буфер обмена После копирования таблицы, удовлетворяющей всем требованиям к ней, перейдите в документ Word и установите курсор в том месте (под оформленным заголовком, где должны находиться данные из таблицы Excel. Во вкладке Главная группа Буфер обмена Word активизируйте команду Вставить / Специальная вставка. В появившемся окне выберите элемент Лист Microsoft Excel (объект, рисунок 5.14. Активизируйте переключатель Связать, чтобы образовать связь. Если активен переключатель Вставить, содержимое буфера обмена вставляется в документ, но связь при этом не устанавливается. Если нет необходимости в полном отображении сводной таблицы, установите переключатель В виде значка. Рисунок 5.14 – Окно Специальной вставки После нажатия кнопки ОК выделенные данные таблицы 92 появятся в документе Word. При этом размер файла документа Word не увеличится, так как сводная таблица не сохраняется в нема изображение на экране формируется из самого источника. После вставки сводной таблицы все изменения исходных данных в Excel будут автоматически выполняться в отчете Word. 2 Редактирование связанного объекта Редактирование связанной информации выполняется вис- ходном приложении. Простейший способ открытия исходного приложения – двойной щелчок на связанной информации в документе. После выполнения данного действия открывается приложение, в котором был создан связанный объект, и этот объект выводится на экран для редактирования. 3 Обновление связи По умолчанию задано автоматическое обновление связи активизируется переключатель автоматическое в области Обновление диалогового окна Связи, те. при изменении информации в исходном документе она обновляется ив документе Word, если он открыт. Связи обновляются автоматически и при открытии документа Разрыв связи Для разрыва связи предназначена кнопка Разорвать связь диалогового окна Связь. После нажатия этой кнопки необходимо подтвердить разрыв связи в открывшемся окне запроса. После разрыва связи связанная информация остается в документе Word, однако, она уже не может обновляться. Восстановить связь нельзя никаким другим способом, кроме команды отмены меню Правка. III Задания для самостоятельной работы 93 Задание 1. На основе таблицы Товар, лабораторная работа 4, рисунок 4.15. Создать сводную таблицу, поместив код покупателя в область фильтра, в область строк расположить даты покупок, а в область столбцов наименования товаров. Проанализировать полученную таблицу. Задание 2. В сводной таблице, полученной в задании 1 сгруппировать данные понедельным периодам, по месяцам. Задание 3. На основесводной таблицы, полученной при выполнении упражнения 5.3, в Word создайте отчет по количеству проданного товара каждого наименования. IV Контрольные вопросы 1 Для чего предназначены связи между листами книги 2 Как создаются связи между листами и файлами 3 Для чего применяется многооконный режим 4 Как создать сводную таблицу 5 Для чего предназначены сводные таблицы 6 Как отображаются изменения в сводной таблице ив связанных таблицах при изменении в исходной таблице 7 Как производится группировка по датам в сводных таблицах Как в документ Microsoft Word вставить таблицу, созданную в Microsoft Excel? 94 Лабораторная работа № 6 Тема Макросы I Цель работы Научиться создавать, вызывать и редактировать макросы. II Теоретическое введение и упражнения Если при работе с Excel возникла необходимость несколько раз выполнить одну и туже последовательность действий, то вы можете записать эту последовательность под определенным именем. Записанная под определенным именем последовательность действий называется макросом. Записанный макрос можно вызывать для выполнения из основного меню при помощи кнопки на панели инструментов или на рабочей области, а также комбинацией клавиш. Макрос может быть назначен графическому объекту Макрос записывается на языке Visual Basic for Applicanons. Текст макроса можно вызвать для редактирования. Создание макроса При составлении различных отчетов и сводок в качестве названий строк часто используются названия месяцев. Упражнение 6.1. Создайте макрос, который будет выводить на экран столбец с названиями месяцев года. Установите курсор в любую ячейку листа, за исключением А. Выполните команду Вид Макросы Запись макроса. Появляется окно диалога Запись макроса, рисунок 6.1. Введите в поле ввода Имя макроса название создаваемого макроса Месяц_абс. В поле ввода Описание введите текст Вводит названия месяцев. Введите клавишу быстрого вызова макроса. Для этого перейдите в поле ввода Ctrl, переключитесь на 95 латинский регистр, удалите находящуюся там букву и введите букву m. После этого нажмите кнопку ОК. Начиная с этого момента, осуществляется запись макроса. Рисунок 6.1 – Окно записи макроса Выполните последовательность действий, которую будет выполнять макрос установите курсор в ячейку А введите слово январь поместите указатель мыши в правый нижний угол ячейки А, при этом указатель мыши изменит видна черный крестик нажмите кнопку мыши и, удерживая ее нажатой, продолжите выделение до ячейки А. Для остановки записи макросавыполните Вид Макросы Остановить запись. Примечание если не будет выполнено условие первого шага, то есть курсор будет находиться в ячейке А, то макрос будет правильно работать только в том случае, когда перед нажатием клавиш [Ctrl]+[m] поместите курсор будет в ячейке А. Листу присвойте имя Месяц и сохраните файл как tab6.xlsm. Макрос записан и теперь нажатие клавиш [Ctrl]+[m] вызовет появление в ячейках А1:А12 названий месяцев. Приза- писи макроса не был включен режим записи с относительными 96 ссылками, поэтому названия месяцев будут появляться только в ячейках А1:А12 (созданный макрос является макросом с абсолютными ссылками. Для устранения этого неудобства предназначен режим записи с относительными ссылками. Упражнение 6.2. Создайте макрос с относительными ссылками для размещения заголовка таблицы продаж компьютеров в произвольном месте рабочей книги. Перейдите на Лист файла tab6.xlsm. Выполните команду Вид Макросы Запись макроса. Введите в поле ввода Имя макроса название создаваемого макроса - Заголовок В поле ввода Описание введите текст Вводит названия компьютеров. Введите клавишу быстрого вызова макроса. Для этого перейдите в поле ввода Ctrl, переключитесь на латинский регистр и введите букву q. Нажмите кнопку ОК. Перед созданием макроса включите режим записи с относительными ссылками. Для этого выполните Вид Макросы Относительные ссылки. Относительные ссылки будут использоваться до конца текущего сеанса работы вили до повторного нажатия кнопки Относительная ссылка. Выполните последовательность действий, которую будет выполнять макрос в туже ячейку где находился курсор до начала записи макроса введите заголовок таблицы Тип компьютера нажмите клавишу [Епtеr], введите тип первого компьютера СР_35_SК и нажмите клавишу Та Аналогично введите следующие типы компьютеров СР_40_SR, СР_55_LS, СР_55_SX. Для остановки записи макросавыполните Вид Макросы Остановить запись. Для выполнения макроса Заголовок нужно нажать [Ctrl]+[q]. В том месте таблицы, где расположен курсор, появится созданный заголовок. Этот макрос отличается от макроса созданного в предыдущем примере тем, что заголовок таблицы можно поместить в любом месте рабочей книги. 97 Просмотр текста макроса Упражнение 6.3. Просмотрите текст макроса Месяц_абс. Для входа в режим редактирования макроса выполните команду Вид Макросы Макросы. Появляется окно диалога Макрос, рисунок 6.2, в котором после выбора нужного для редактирования макроса становятся доступными кнопки, в том числе кнопка Изменить. Рисунок 6.2 – Список макросов Нажмите кнопку Изменить, рисунок 6.2. Появится текст макроса Месяц_абс, записанный на языке Visual Basic: Sub Месяц_абс() ' ' Месяц_абс Макрос ' Вводит названия месяцев ' ' Сочетание клавиш Ctrl+m ' 98 Range("A1").Select ActiveCell.FormulaR1C1 = "Январь" Range("A1").Select Selection.AutoFill Destination:=Range("A1:A12") Type:=xlFillDefault Range("A1:A12").Select End Sub Таким образом появляется возможность изменять текст программы, записанной на языке Visual Basic for Application, что позволяет писать собственные программы. Удаление макросов Если макрос назначен клавише быстрого доступа, графическому объекту или кнопке, то процесс удаления является стандартным. Необходимо выполнить Вид Макросы Макросы. Появляется окно диалога Макрос, содержащее список макросов текущей рабочей книги. После выбора макроса, который предпола- гется удалить, станут доступными кнопки окна диалога Макрос, в том числе кнопка Удалить. Для удаления выбранного макроса достаточно нажать эту кнопку. Переименование макроса Специальной команды для переименования макроса нет, а часто возникает потребность исправить имя макроса или присвоить ему другое имя. Для этого нужно войти в режим редактирования макроса ив тексте программы исправить заголовок. Упражнение 6.4. Измените имя макроса Месяц_абс на Месяцы с абсолютными_адресами. Выполните Вид Макросы Макросы ив появившемся окне диалога Макрос выберите макрос Месяц_абс и нажмите клавишу Изменить. Первая строка является комментарием. 99 Замените Месяц_абс на Месяц_с_абсолютными_адре- сами. Соответствующим образом измените комментарий. В результате получим новый текст программы макроса Sub Месяц_с_абсолютными_адресами() ' ' Месяц_с абсолютными_адресами Макрос ' Вводит названия месяцев ' ' Сочетание клавиш Ctrl+m ' Range("A1").Select ActiveCell.FormulaR1C1 = "Январь" Range("A1").Select Selection.AutoFill Destination:=Range("A1:A12"), Type:=xlFillDefault Range("A1:A12").Select End Sub Новое имя автоматически заменит старое в списках макросов. По клавише быстрого вызова будет вызываться макрос с новым именем. Ноне все замены происходят автоматически. Если макросу были назначены графические объекты или кнопки, то нужно самим сделать для них переназначения. Назначение макроса графическому объекту Макрос может вызываться не только нажатием клавиш быстрого вызова, но и нажатием кнопки мыши на графическом объекте. Для назначения макроса графическому объекту необходимо наличие такого объекта на экране. Упражнение 6.5. Назначьте макрос Месяц_абс графическому объекту. 100 Чтобы нарисовать закрашенный овал или прямоугольник, выполните Вставка Иллюстрации Фигуры \ Основные фигуры. Выберите место для объекта. Поместите указатель мыши в нужное место таблицы и, удерживая нажатой кнопку мыши, нарисуйте объект — прямоугольник или овал. Для выбора заливки объекта нажмите правую кнопку мыши на активном объекте. Появится меню, в котором выберите пункт Формат фигуры. Появится окно диалога Формат фигуры, в котором выбирается цвет и вид заливки. Для ввода надписи нажмите правую кнопку мыши на активном объекте. Появится меню, в котором выберите пункт Изменить текст Введите требуемый текст и нажмите кнопку мыши вне текстового окна. Построен графический объект, рисунок 6.3. Рисунок 6.3 – Назначение макроса графическому объекту Назначим созданному графическому объекту. нажмите правую кнопку мыши на графическом объекте. Появится меню, в котором выберите пункт Назначить макрос. Появится окно диалога Назначить макрос объекту со списком макросов. Выберите требуемый макрос и нажмите на нем кнопку мыши. Имя макроса появится в верхней строке окна диалога. Нажмите кнопку ОК. Произойдет выход из режима назначения макроса. Нажмите кнопку мыши в любом месте вне объекта. 101 Макрос назначен графическому объекту. Если теперь поместить указатель мыши на этот объект, то белый крестик перейдет в изображение руки с отогнутым указательным пальцем. Нажатие левой кнопки мыши приводит к выполнению макроса, который назначен этому объекту. Назначение макроса кнопке Рассмотрим назначение макроса кнопке. Эта кнопка может быть пользовательской, стандартной кнопкой Ехсеl или кнопкой, расположенной на листе. Для ее расположения на листе нужно отобразить еще одну вкладку Разработчик. Для ее отображения необходимо щелкнуть по кнопке Microsoft Office. Выберать команду Параметры Excel. Затем во вкладке Основные диалогового окна Параметры Excel установить соответствующий флажок. На вкладке Разработчик собраны средства создания макросов и форма также функции для работы с XML. Файлы и шаблоны предыдущих версий могли содержать пользовательские панели инструментов. В этом случае при открытии таких файлов в Excel 2007 появляется еще одна постоянная вкладка – Надстройки. Сначала необходимо нарисовать кнопку, а затем назначить ей макрос. Для этого выполнить Разработчик Элементы управления Вставить. На появившейся панели инструментов Элементы управления формы, рисунок 6.4, нажать кнопку, соответствующую добавляемому элементу управления (например элемент Кнопка. 102 Рисунок 6.4 – Панель элементов управления Установить курсор в то место, где будет располагаться кнопка и, удерживая нажатой кнопку мыши, выделить прямоугольник, где будет расположена кнопка. Как только отпустится кнопка мыши, появится окно диалога Назначить макрос объекту. Надо выбрать требуемый макрос из списка Имя макроса. Чтобы изменить надпись на кнопке надо нажать правую кнопку мыши на созданной кнопке ив появившемся меню выбе- рать Изменить текст ввести вместо номера кнопки требуемый текст. Эта операция нужна для входа в режим форматирования надписи наследующем шаге. Затем нажать правую кнопку мыши на созданной кнопке, в появившемся меню выбрать команду Формат объекта. Появится окно диалога форматирования. Теперь следует выбрать вкладки Выравнивание и Шрифт и установить там нужные параметры надписи на кнопке. Теперь можно нажать кнопку ОК для выхода из режима форматирования. Кнопка с назначенным ей макросом создана. Упражнение 6.6. Закрепите макрос Заголовок за кнопкой на рабочем листе Воспользуемся приведенной выше последовательностью действий для создания кнопки на рабочем листе. Приведем пример назначения макроса Заголовок кнопке на рабочем листе с надписью Заголовок. Сначала создадим кнопку с изображением надписи Заголовок, а затем назначим ей макрос Заголовок Нажмите кнопку Кнопка на панели инструментов Элементы управления формы. Укажите место, где будет располагаться кнопка. В окне диалога Назначить макрос объекту выберите макрос Заголовок, и нажмите кнопку ОК. Нажмите правую кнопку мыши на созданной кнопке и вместо текста с номером кнопки введите слово Заголовок. Нажмите кнопку мыши вне созданной кнопки. Нажмите правую кнопку мыши на созданной кнопке. Появится меню со списком команд. 103 Выберите Формат объекта. Появится окно диалога форматирования Выберите вкладку Выравнивание и установите переключатель горизонтального и вертикального выравнивания в положение по центру. Выберите вкладку Шрифт и установите стиль надписи на кнопке Arial и размер 14. Нажмите кнопку ОК. Нажмите кнопку мыши вне кнопки. III Задания для самостоятельной работы Задание 1. Используя базу данных Адресно-телефонная книга, лабораторная работа №4, создать макрос для выбора абонентов проживающих на улице Советской и номерами телефонов начинающихся на 7. Задание 2. Используя данные лабораторной работы №3 любое упражнение, создать макрос для построения диаграммы. IV Контрольные вопросы 1 С какой целью создаются макросы 2 Как создается макрос 3 В чем отличие макроса с абсолютными ссылками от макроса с относительными ссылками 4 Как переименовать макрос 5 Как назначить макросу клавишу быстрого вызова 6 Как назначается макрос графическому объекту, расположенному на рабочем листе Excel,? 7 Как удалить макрос |