Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
Цель: Знакомство с графическим представлением табличных данных в MS Excel.
Темы: Работа с диаграммами. Использование основных типов диаграмм. Создание и редактирование диаграмм.
1. Введите таблицу, представленную на рис.7.1, на первый и второй листы книги.
Рис.7.1
2. Научитесь создавать диаграммы на листе Диаграмма и на рабочем листе.
2.1 Выделите рабочий диапазон таблицы А4:G6, и нажмите клавишу F11 для быстрого построения гистограммы на отдельном листе.
2.2. Познакомьтесь с командами вкладки Работа с диаграммами – Конструктор - Тип и поменяйте гистограмму на нормированную гистограмму и проанализируйте полученный результат, верните прежний тип гистограммы.
2.3. Используя команду Работа с диаграммами – Конструктор – Данные – Строка/столбец, измените ориентацию рядов диаграммы, затем верните диаграмму к прежнему виду.
2.4. Познакомьтесь с экспресс - макетами диаграммы и примените один из них, для возврата используйте команду экспресс – макет 11.
2.5. Снабдите диаграмму элементами диаграммы, перечень которых можно найти на вкладке Работа с диаграммами – Макет. На диаграмме должны быть подписи данных, легенда, название диаграммы, а также названия осей и таблица значений.
2.6. Выберите маркер диаграммы из ряда Факт с наибольшим значением, увеличьте размер шрифта подписи данных этого маркера и измените его заливку. Используйте команду Формат выделенного фрагмента на вкладке Работа с диаграммами - Макет или Работа с диаграммами - Формат.
2.7. Постройте на рабочем поле первого листа аналогичную гистограмму. Обратите внимание на команду Работа с диаграммами – Конструктор – Расположение, которая позволит расположить диаграмму на отдельном листе или непосредственно в текущем.
2.8. Добавьте новую строку в исходную таблицу, в которой будет рассчитано среднее значение между плановыми и фактическими показателями, и отредактируйте гистограмму, указав новый диапазон данных (Работа с диаграммами – Конструктор – Данные – Выбрать данные). Замените тип диаграммы для ряда среднего значения на график и используйте для него вспомогательную ось. Снабдите гистограмму всеми элементами диаграммы (п.2.5) и оформите ее по своему усмотрению. Сохраните книгу.
3. Познакомьтесь с диаграммами разных типов, предоставляемых Excel и расположите их на отдельных листах. Каждый лист должен иметь имя, соответствующее типу диаграммы, расположенной на нем.
3.1.Постройте диаграмму с областями (Area).
3.2.Постройте линейчатую диаграмму (Bar).
3.3.Постройте диаграмму типа график (Line).
3.4.Постройте круговую диаграмму для фактических показателей (Pie).
3.5.Постройте кольцевую диаграмму (Doughnut).
3.6.Постройте лепестковую диаграмму - "Радар" (Radar).
3.7.Постройте точечную диаграмму (XY).
3.8.Постройте объемную круговую диаграмму плановых показателей (3-D_Pie).
3.9.Постройте объемную гистограмму (3-D_Column).
3.10.Постройте объемную диаграмму с областями (3-D_Area).
4. Научитесь редактировать диаграммы2.
4.1. В диаграмме "График" замените тип диаграммы для данных, обозначающих "План", на круговую и назовите лист "Line_Pie".
4.2. Отредактируйте круговую диаграмму, созданную на листе "Pie", так, как показано на рис.7.2.
4.3. Отредактируйте линейные графики так, как показано на рис.7.3.
Рис.7.2 Рис.7.3
4.4. Научитесь редактировать объемные диаграммы.
4.4.1. Установите "поворот" диаграммы вокруг оси Z для просмотра:
фронтально расположенных рядов (угол 0 о);
под углом в 30 о;
под углом в 180 о;
4.4.2. Измените перспективу, сужая и расширяя поле зрения.
4.4.3. Измените порядок рядов, представленных в диаграмме.
5. Предъявите результаты преподавателю.
Цель: Знакомство с методами обработки данных, организованных в списки.
Темы: Сортировка, редактирование, просмотр, поиск и извлечение данных в списках MS Excel.
1 .Сортировка списка по строкам.
1.1. Введите таблицу, приведенную на рис.8.1.
Рис.8.1
1.2. Пользуясь командой Главная – Редактирование – Сортировка и фильтр - Настраиваемая сортировка, отсортируйте список:
Проанализируйте полученные результаты.
1.3. Пользуясь сортировкой и командой Данные – Структура – Промежуточные итоги, ответьте на вопросы а) - с).
2. Сортировка списка по столбцам.
2.1. Введите таблицу, представленную на рис.8.2, на новый лист книги.
2.2. Отсортируйте ее так, чтобы предметы (названия полей) располагались в алфавитном порядке. Транспонируйте таблицу с помощью команд Главная – Буфер обмена - Копировать и Главная – Буфер обмена – Вставить - Транспонировать. Добавьте в конец таблицы строку "Средний балл" и заполните ее, введя соответствующую формулу.
2.3.Выполните сортировки по столбцам.
2.3.1. Переставьте столбцы так, чтобы фамилии студентов расположились в обратном алфавитном порядке.
2.3.2. Переставьте столбцы так, чтобы в первых колонках были худшие учащиеся (с минимальным средним баллом).
2.3.3. Переставьте столбцы так, чтобы в первых колонках были учащиеся, хорошо успевающие по математике, но с провалами по физкультуре.
2.3.4. Переставьте столбцы так, чтобы фамилии студентов расположились в алфавитном порядке, снова транспонируйте таблицу и поместите ее, начиная с клетки A25.
3. Обработка списков с помощью формы.
3.1. Активизируйте лист с исходной таблицей (рис.8.1).
3.2. Активизируйте окно Форма, предварительно поместив его на панель быстрого доступа (Office – Параметры Excel – Настройка – Команда не на ленте – Добавить (в панель быстрого доступа),
просмотрите все записи в списке;
"примите" (добавить) на работу одного инженера;
"увольте" (удалить) одного водителя (используя кнопку "Критерии");
сотруднице Абрамовой поменяйте фамилию на Иванова (используя кнопку "Критерии").
Рис.8.2
4. Измените структуру таблицы П.8.1.
4.1. Введите дополнительное поле "Оклад" после поля "Пол" и заполните его осмысленными значениями.
4.2. После поля "Оклад" добавьте еще три поля: "Надбавки", "Налоги", "К выплате".
4.3. Установите надбавки в размере 1000 руб. женщинам старше 50 лет. Поиск соответствующих лиц выполняйте через форму.
4.4. В обычном режиме редактирования заполните поле "Налог" - 5% от суммы оклада и надбавки, если она не превышает порога в 10 000 тыс. руб., и 10%, если свыше. При заполнении поля используйте функцию ЕСЛИ.
4.5. Запишите формулу в поле "К выплате" ("Оклад" + "Надбавки" - "Налоги").
5. Предъявите результаты преподавателю.
Цель: Освоить возможности фильтрации табличных данных.
Темы: Фильтрация данных с помощью автофильтра. Расширенная фильтрация и сложные критерии.
1. Фильтрация записей с помощью функции автофильтра.
1.1. Для выполнения задания скопируйте итоговый список сотрудников из Задания №8 в новую книгу.
1.2. Примените автофильтр, пользуясь командой Данные – Сортировка и фильтр - Фильтр, или Главная – Редактирование – Сортировка и фильтр – Фильтр, определите:
сколько в фирме женщин и каков их средний заработок;
троих самых великовозрастных, независимо от пола;
какой максимальный оклад имеет сотрудница, не получающая надбавку;
кто из мужчин живет в центральном районе (телефон начинается от 310 до 315);
каков суммарный заработок у менеджеров и инженеров;
сколько в фирме работает Ивановых, и каков их суммарный оклад;
сколько сотрудников получают больше 3 000 руб., но меньше 10 000 тыс. руб. Сколько из них женщин;
сколько сотрудников получают больше 10 000 руб. или меньше 3000 руб., и кто из них не получает надбавки;
2. Расширенная фильтрация. Для активизации расширенной фильтрации воспользуйтесь командой Данные – Сортировка и фильтр – Дополнительно.
2.1. Для выполнения задания скопируйте "Ведомость оценок" (рис.8.2 из Задания № 8), расположите ее в начале чистого листа; определите область критериев справа от таблицы.
2.2. Определив область для извлечения данных под таблицей, найдите студентов, имеющих:
тройки по математике; тройки по математике, но четверки по физике;
двойки по математике, но пять по физике и средний балл больше 3,5 (предварительно добавив в таблицу столбец "Ср. балл");
тройки по математике или тройки по физике;
двойку по любому предмету (хотя бы одну).
2.3. Извлеките (на месте) данные о студентах, имеющих:
средний балл меньше 4;
средний балл больше, чем 3,5 и оценку по математике больше 3;
средний балл не меньше, чем 4,5 или меньше 4, но по биологии - 5;
средний балл больше, чем 3,5, но меньше 43.
2.4. Извлеките только фамилии студентов4:
не имеющих двоек; имеющих хотя бы одну двойку.
не имеющих двоек и имеющих средний балл не меньше 4;
3. Предъявите результаты преподавателю.
Цель: Научиться создавать связи между таблицами.
Темы: Создание и использование связей между данными. Использование «объемных формул».
1. Создайте три таблицы, содержащие сведения о ценах на программные продукты, по образцу, приведенному на рис.10.1. Для каждого месяца первого квартала на отдельном листе книги Имя_10_1 создается собственная таблица с названием "Прайс-лист (Месяц)", где месяц - Январь, Февраль, Март.
1.1. При создании таблиц организуйте связи между таблицами "Прайс-лист (Январь)" и таблицами "Прайс-лист (Февраль)" и "Прайс-лист (Март)", для чего скопируйте диапазон ячеек А3:В13 январской таблицы цен в буфер, перейдите в таблицу "Прайс-лист (Февраль)" и воспользуйтесь командами Главная – Буфер обмена – Вставить – Вставить связь (или Главная – Буфер обмена – Вставить – Специальная вставка – Вставить связь). Аналогично установите связь с таблицей "Прайс-лист(Март)".
Рис.10.1
1.2. Переменную часть таблиц (столбец "Цена") отредактируйте согласно данным, приведенным на рис.10.1. Переименуйте листы, дав им соответствующие имена (Январь, Февраль, Март).
1.3. Посмотрите, как выглядят ссылки в строке формул при активизации связанных ячеек в таблицах февраля и марта. Изменив содержимое ячейки А7 в январской таблице, просмотрите, как изменится соответствующая ячейка в февральской таблице. Попытайтесь изменить текст в ячейке А7 февральской таблицы и сделайте выводы о направленности установленной связи.
2. Создайте таблицы "Отгрузка (Январь)", "Отгрузка (Февраль)" и "Отгрузка (Март)"по образцу, приведенному на рис.10.2, пользуясь режимом группового заполнения, и дайте листам книги названия: Отгр_ЯНВ, Отгр_ФЕВ, Отгр_МАР.
2.1. В ячейке D4 запишите формулу, обеспечивающую ссылку на таблицу "Прайс_лист (Январь)". Эта формула приведена в строке формул, показанной на рис.10.2 в верхней части.
2.2. Скопируйте формулу в ячейки D5:D13.
2.3. Запишите в ячейку D14 формулу, выполняющую суммирование по столбцу "Итого" (ячейки D4:D13).
2.4. Активизируйте команду Формулы – Зависимости формул – Влияющие ячейки для ячейки D14.
2.5. Установите курсор в ячейку D4 и отобразите влияющие ячейки. Пронаблюдайте, как отображается зависимость от внешней таблицы "Прайс_лист (Январь)", связанной с таблицей "Отгрузка(Январь)". Обратите внимание, как в строке формул выглядит формула со ссылкой на ячейку из другой таблицы, и из каких элементов состоит эта ссылка.
2.6. Сохраните созданную книгу с шестью листами под именем Имя_10_1.
2.7. Сохраните копию книги под именем Имя_10_2.
2.8. Удалите из книги Имя_10_1 листы "Отгр_ЯНВ", "Отгр_ФЕВ" и "Отгр_МАР", сохранив в ней только прайс_листы.
Рис.10.2
3. Оставьте открытыми обе книги. Заполните таблицу "Отгрузка(Февраль)" книги Имя_10_2 , пользуясь "Прайс_листом(Февраль)" книги Имя_10_1.
3.1. В ячейке D4 запишите формулу, обеспечивающую ссылку на таблицу "Прайс_лист (Февраль)". Эта формула приведена в строке формул, показанной на рис.10.3.а, в верхней части.
3.2. Скопируйте формулу в ячейки D5:D13.
4. Заполните таблицу "Отгрузка(Март)" книги Имя_10_2, пользуясь "Прайс_листом(Март)" книги Имя_10_1 аналогично п.3.1 и 3.2
4.1. Закройте книгу Имя_10_1. Просмотрите формулу в D4.Она приведена в строке формул, показанной на рис.10.3.б, в верхней части.
4.2. Запишите в ячейки D14 отгрузочных листов формулу, выполняющую суммирование по столбцу "Итого" (ячейки D4:D13).
Рис.10.3.а Рис.10.3.б
5. Создайте новую таблицу "Суммарный доход за три месяца", в которой будут сведены итоговые значения выручки за все кварталы за счет организации "трехмерной связи", т.е. связи между одинаковыми клетками однотипных таблиц. Принцип создания такой таблицы представлен на рис.10.4. В создаваемой таблице запишите две формулы для получения одного и того же значения, но в одной из них запишите формулу с непосредственным обращением к каждой таблице, а в другой - с обращением к блоку таблиц, так называемую "объемную" формулу. Примеры записи таких формул приведены на рис.10.4 непосредственно под ячейками В4, В7 и выделены курсивом.
Рис.10.4
6. Предъявите результаты преподавателю.
Цель: Знакомство с механизмом консолидации данных.
Темы: Консолидация «по положению». Консолидация «по категориям». Консолидация со связью.
1. Создайте три таблицы, содержащие сведения о поставляемых товарах, по образцу, приведенному на рис.11.1. Для каждого месяца первого квартала на отдельном листе книги Имя_11_1 создается собственная таблица с названием "Поставки товаров в месяце", где месяц - январь, февраль, март. При создании таблиц пользуйтесь режимом "группового заполнения листов" или копирования данных.
1.1. Переменная часть таблиц (столбцы "Объем" и "Дата") должна соответствовать данным, приведенным на рис.11.1. Переименуйте листы, дав им соответствующие имена (Янв, Фев, Мар).
Рис.11.1
2. Вставьте новый лист, дав ему имя "Конс_данные". Скопируйте в него заголовок таблицы и откорректируйте его соответствующим образом (рис.11.3). Установите курсор в первую свободную ячейку (А3).
Рис.11.2
2.1. Активизируйте диалоговое окно Консолидация с помощью команд Данные – Работа с данными - Консолидация, и, последовательно указывая в поле Ссылка необходимые адреса консолидируемых областей, сформируйте их полный список, состоящий из трех записей, как представлено на рис.11.2.
2.2. В поле Функция оставьте функцию вычисления суммы и укажите, что в качестве имен (названий строк) будут выбираться данные из левого столбца (А) консолидируемой области. Укажите на необходимость создания динамической связи с исходными данными.
Рис.11.3
2.3. Выполните консолидацию. Сравните полученные результаты с приведенными на рис.11.3.
2.4. Просмотрите созданную структуру, последовательно показывая или скрывая уровни этой структуры. Откройте второй (внутренний) уровень для поставщиков из С.Петербурга и Череповца. Сравните полученный результат с представленным на рис.11.4.
Рис.11.4 Рис.11.5
2.5. Пользуясь командами Формулы – Зависимости формул – Влияющие ячейки, проследите влияющие ячейки для ячеек С7, С10, С38. Убедитесь в правильности полученных результатов.
2.6. Раскройте структуру для первых трех консолидированных данных и просмотрите формулы в столбце С (Формулы – Зависимости формул – Влияющие ячейки – Показать формулы). Верните отображение результатов вычислений.
2.7. Меняя данные в ячейках листов "Янв", "Фев", "Мар", проследите за автоматическим пересчетом общей итоговой суммы (ячейка С38) и частичных сумм в ячейках С10, С26 и т.д.
3. Сохраните созданную книгу с четырьмя листами под именем Имя_11_1.
3.1. Откройте новую книгу и создайте в ней одну таблицу, имеющую аналогичную предыдущим структуру и содержащую данные за второй квартал. Образец такой таблицы на рис.11.5. Назовите лист с таблицей "2кварт". Сохраните созданную книгу под именем Имя_11_2.
3.2. Сверните окно рабочей книги.
Рис.11.6
3.3. На новом листе книги Имя_11_1 выполните консолидацию четырех диапазонов ячеек - трех из листов "Янв", "Фев", "Мар" книги Имя_11_1, а четвертого из соответствующего диапазона книги Имя_11_2 листа "2кварт". Пользуйтесь кнопкой Обзор диалогового окна Консолидация. Обратите внимание на структуру ссылки при задании области консолидации из неактивной книги. Проверьте результат и сравните его с тем, что представлен на рис.11.6.
3.4. Закройте книгу Имя_11_2. Обратите внимание на структуру ссылки при задании области консолидации из закрытой книги.
3.5. Выполните консолидацию данных из четырех таблиц (аналогично пункту 3.3), но задав в качестве обработки данных вычисление среднего значения консолидируемых данных. Сравните полученный результат с представленным на рис.11.7.
4. Добавьте в книгу еще один лист, именовав его как "Конс_данные2", и выполните на этом листе консолидацию данных, расположенных в таблицах листов "Янв", "Фев", "Мар" и "Апр" (структура таблицы листа "Апр" приведена на рис.11.8), обратив внимание на задание консолидируемой области для листа "Апр".
Рис.11.7
4.1. Проверьте правильность структуры таблицы на листе "Конс_данные2", сравнив ее с представленной на рис.11.9.
Рис.11.8 Рис.11.9
5. Предъявите результаты преподавателю.
Цель: Научиться создавать сводные таблицы.
Темы: Изменение структурной организации данных с использованием сводных таблиц. Обработка данных (фильтрация, группировка, подведение итогов) в сводных таблицах. Вычисления в сводных таблицах. Представление и анализ данных. Сводные диаграммы.
1. Создайте новую книгу. На листе этой книги создайте таблицу (рис.12.1), содержащую данные о продажах автомобилей тремя гипотетическими фирмами (Альфа, Бета и Гамма) за первое полугодие 2009 года. Назовите лист "Продажи" и сохраните книгу.
Рис.12.1
2. Выполните команду Вставка – Таблицы – Сводная таблица, предварительно установив курсор в исходную таблицу. В диалоговом окне «Создание сводной таблицы» выберите диапазон данных для анализа и местоположение сводной таблицы (на новом листе).
2.1. Для создания таблицы, приведенной на рис.12.2, заполните макет сводной таблицы в панели Список полей сводной таблицы, поместив поля таблицы в соответствующие области: поле Марка – в Названия строк, поле Фирма – в Названия столбцов, а поле Цена – в область Значения. Полученная в результате этих действий сводная таблица, а также панель Список полей сводной таблицы показаны на рис.12.2.
2.2 Сформулируйте вопрос, на который отвечает созданная сводная таблица.
2.3. Познакомьтесь с вкладкой Работа со сводными таблицами. Измените функцию суммирования по полю Цена на среднее значение, установив курсор на числовые данные таблицы и выбрав из контекстного меню команду Итоги по…или команду Работа со сводными таблицами – Параметры – Активное поле – Параметры поля – Операция. Измените название поля на Средняя цена, используя текстовое поле Пользовательское имя в том же диалоговом окне.
2.4. Поменяйте ориентацию строк и столбцов таблицы, пользуясь раскрывающимися списками в областях панели «Список полей сводной таблицы».
2.5. Измените форматирование числовых значений сводной таблицы, применив денежный формат (Работа со сводными таблицами – Параметры – Активное поле – Параметры поля). Примените стили к сводной таблице (Работа со сводными таблицами – Конструктор – Параметры стилей сводной таблицы).
Рис.12.2
2.6. Рассмотрите возможности отображения и сокрытия итогов вычислений (Работа со сводными таблицами – Конструктор – Макет).
2.7. Скройте и восстановите заголовки полей (Работа со сводными таблицами – Параметры – Показать или скрыть – Заголовки полей).
2.8. Постройте диаграмму, отображающую данные сводной таблицы, используя команды Работа со сводными таблицами – Параметры – Сервис – Сводная диаграмма.
3. Сформируйте сводную таблицу, позволяющую оценить количество продаж и сумму продаж трех фирм различных марок машин. Таблица представлена на рис.12.3.
Рис.12.3
4. Сформируйте сводную таблицу, позволяющую оценить сумму продаж и даты продаж. Таблица представлена на рис.12.4. Проанализируйте данные таблицы.
4.1. Сгруппируйте данные по месяцам, затем по кварталам, затем по месяцам и кварталам (Работа со сводными таблицами – Параметры – Группировать – Группировка по полю). Таблица представлена на рис.12.5.а. Проанализируйте данные таблицы.
Рис.12.4
4.2. Примените к датам продажи группировку по выделенному, выделив сначала группу зимнее-летних месяцев, а затем группу остальных. После выделения обратитесь к команде Работа со сводными таблицами – Параметры – Группировать – Группировка по выделенному. Таблица представлена на рис.12.5.б. Проанализируйте данные таблицы.
|
Рис.12.5.а |
Рис.12.5.б |
5. Создайте новую сводную таблицу, которая будет отображать суммарную цену и количество проданных каждой фирмой автомобилей в зависимости от года выпуска автомобиля и даты продажи. Сгруппируйте данные о годах выпуска с шагом в пять лет. Таблица представлена на рис.12.6. Проанализируйте данные таблицы.
Рис.12.6
5.1. Постройте диаграммы, отображающие полученные результаты
6. Создайте сводную таблицу, представленную на рис.12.7. Используйте в ней вычисляемое поле Сумма в рублях. Для его создания примените команду Работа со сводными таблицами – Параметры – Сервис – Формулы - Вычисляемое поле. Дайте имя полю Сумма в рублях, а в поле Формула введите формулу =30*Цена.
Рис.12.7
7. Предъявите результаты преподавателю.
Цель: Знакомство с технологиями обмена данными в приложениях MS Office.
Темы: Приложения – источники и приемники данных. Технология встраивания и связывания объектов (OLE). Динамический обмен данными.
1. Обмен данными через буфер обмена (вставка).
1.1. Табличный процессор Excel в качестве источника данных.
1.1.1. Запустите табличный процессор Excel и создайте в нем таблицу, приведенную на рис.13.1. Сохраните рабочую книгу под именем Имя_13_1.
1.1.2. Запустите текстовый процессор Word. Откройте окно нового документа. Установите размеры окон в Excel и Word, как показано на рис.13.1. Введите в окне документа Word две строки текста: "Начало документа" и "Продолжение документа".
Рис.13.1
1.1.3. Скопируйте в буфер обмена таблицу из диапазона A1:D5 рабочего листа книги Имя_13_1.
1.1.4. Вставьте в документ Word таблицу из ячеек А1:D5 рабочего листа книги Имя_13_1, используя буфер обмена.
1.1.5. Вставьте в документ Word таблицу из ячеек А1:D5 рабочего листа книги Имя_13_1 как неформатированный текст. Для этого используйте режим специальной вставки и укажите в качестве типа принимаемых данных "Неформатированный текст".
1.1.6. Вставьте в документ Word таблицу из ячеек А1:D5 рабочего листа книги Имя_13_1 как текст в формате RTF. Используйте режим специальной вставки и укажите в качестве типа принимаемых данных "Текст в формате RTF". Образец такой вставки представлен на рис.13.2.
1.1.7. Вставьте в документ Word таблицу из ячеек А1:D5 рабочего листа книги Имя_13_1 как рисунок. Используйте режим специальной вставки и укажите в качестве типа принимаемых данных "Рисунок".
1.1.8. Вставьте в документ Word таблицу из ячеек А1:D5 рабочего листа книги Имя_13_1 как лист Microsoft Excel (объект). Используйте режим специальной вставки и укажите в качестве типа принимаемых данных "Лист Microsoft Excel (объект)".
1.1.9. Внесите в документ комментарии, расположенные перед соответствующим вставленным фрагментом. Комментарий должен содержать текст, поясняющий тип вставленных данных, например "Ниже вставлен текст в формате RTF".
1.1.10. Внимательно познакомьтесь с поведением вставленных объектов при попытке их активизации. Для этого дважды щелкните мышью на каждой из вставленных таблиц.
1.1.11. Особое внимание уделите последней таблице. Внимательно просмотрите, как при активизации вставленного объекта запускается программа-источник данных, и как стандартная панель инструментов текстового процессора Word подменяется панелью Excel.
1.1.12. Оцените возможности и удобства (или неудобства) механизма "Редактирования на месте", который реализуется в данном случае.
Рис. 13.2
1.2. Табличный процессор Excel в качестве приемника данных.
1.2.1. Запустите текстовый процессор Word. Откройте окно нового документа и создайте в нем таблицу, приведенную на рис.13.3, воспроизведя элементы оформления таблицы (ширина столбца (2 см), шрифт, обрамление и заполнение как показано на рис.13.3).
Рис.13.3
1.2.2. Откройте новый рабочий лист в окне табличного процессора Excel. Установите размеры окон Word и Excel, как показано на рис.13.3.
1.2.3. Вставите созданную таблицу в диапазон ячеек А1:С4, используя буфер обмена операционной системы MS Windows.
1.2.4. Вставьте созданную таблицу в диапазон ячеек А6:С9 как текст, используя режим специальной вставки и указав в качестве типа данных "Текст".
1.2.5. Обратите внимание на то, что происходит с оформлением вставляемых документов. Отформатируйте таблицу в диапазоне А6:С9 таким образом, чтобы она точно соответствовала оригиналу (таблице из текстового процессора Word).
2. Встраивание (внедрение) объектов.
2.1. Табличный процессор Excel в качестве приемника данных.
2.1.1. Используя таблицу на рис.13.4 как источник, встроить (внедрить) ее в тот же рабочий лист книги Имя_13_1, начиная с ячейки А9 как "Объект Документ Microsoft Word".
2.1.2. Используя таблицу на рис.13.4 как источник, встроить (внедрить) ее в тот же рабочий лист книги Имя_12_1 с ячейки А14 как "Объект Документ Microsoft Word", но заменив ее реальное отображение условной пиктограммой.
Рис.13.4
2.1.3. Внимательно познакомьтесь с поведением внедренных таблиц, поочередно активизируя их двойным щелчком мыши на поверхности внедренной таблицы или пиктограммы.
2.1.4. Измените размеры области, занимаемой таблицей, внедренной как "Объект Документ Microsoft Word" (А14...), переместив правую границу внедренного объекта на линию, разделяющую столбцы С и D. Пронаблюдайте поведение объекта.
2.1.5. Отметьте диапазон ячеек рабочего листа, который покрывает все вставленные и внедренные таблицы. Очистите все содержимое ячеек. Убедитесь, что все внедренные объекты сохранились, и сделайте выводы об их поведении и возможностях работы с ними.
2.2. Табличный процессор Excel в качестве контейнера стандартных объектов.
2.2.1. Откройте новый лист в рабочей книге Excel. Используя механизм внедрения (встраивания) объектов, разместите на этом листе все объекты, приведенные на рис.13.5. Для создания этих стандартных объектов следует использовать дополнительные программы "надстройки", входящие в комплект MS Office. Вызов дополнительных программ осуществляется посредством меню Вставка – Текст - Объект и Вставка - Иллюстрации с дальнейшим указанием типа объекта.
Рис.13.5
2.3. Связывание объектов.
2.3.1. Табличный процессор Excel в качестве источника связей.
Рис.13.6
2.3.1.1. Создайте на новом листе книги Имя_13_1 таблицу, приведенную на рис.13.6. В окне документа Word внедрите таблицу из листа Excel, установив связи с таблицей-источником. Тип внедренной таблицы - "Неформатированный текст".
Для выполнения связывания выполните последовательность действий:
скопируйте таблицу-источник в документе Excel в буфер редактирования;
перейдите в приложение-приемник (Word);
выполните команду Главная – Буфер обмена - Вставить – Специальная вставка, выбрав тип данных "Неформатированный Текст";
включите режим установления связи (переключатель "Связать").
Рис.13.7
2.3.1.2. В окне документа Word внедрите таблицу из листа Excel, установив связи с таблицей-источником. Тип внедренной таблицы - "Текст в формате RTF". Образец представлен на рис.13.6 (вторая таблица).
Рис.13.8
2.3.1.3. В окне Word внедрите таблицу из Excel, установив связи с таблицей-источником. Тип внедренной таблицы - "Лист Microsoft Excel" (рис.13.7).
2.3.1.4. В окне Word внедрите таблицу из Excel, установив связи с таблицей-источником. Тип внедренной таблицы - "Рисунок" (рис.13.7).
2.3.1.5. Для созданных связанных таблиц просмотрите действие механизма связи, меняя в исходной таблице числовые данные.
2.3.2. Табличный процессор Excel в качестве приемника связей.
2.3.2.1. Откройте окно для нового документа Word. Создайте и оформите в нем три таблицы, приведенные на рис.13.8. В книге Имя_13_1 откройте новый рабочий лист. Внедрите на этот лист (в область с ячейки А1...), установив связь с источником данных, первую (верхнюю) таблицу из документа Word как "Текст". Для выполнения связывания выполните традиционную последовательность действий:
скопируйте таблицу-источник в документе Word в буфер редактирования;
перейдите в приложение-приемник (Excel);
выполните команду Главная – Буфер обмена - Вставить – Специальная вставка, выбрав тип данных "Текст";
включите режим установления связи (переключатель "Связать").
2.3.2.2. Внедрите на рабочий лист (диапазон с А6...), установив связь с источником данных, вторую (среднюю) таблицу из документа Word как "Объект Документ Microsoft Word".
2.3.2.3. Внедрите на рабочий лист (диапазон с А10...), установив связь с источником данных, третью таблицу из документа Word как "Объект Документ Microsoft Word".
2.3.2.4. Поочередно изменяя значения данных в ячейках трех исходных таблиц, пронаблюдайте, как меняются данные в таблицах приложения-приемника.
2.3.3. Управление установленными связями.
2.3.3.1. Активизируйте команду Office - Параметры Excel – Дополнительно – При пересчете этой книги и проверьте установку флажка Сохранять значения внешних связей.
Рис.13.9
2.4. Динамический обмен данными.
2.4.1. Excel в качестве источника и приемника данных.
2.4.1.1. Откройте окно нового документа Word. Создайте в этом окне таблицу, приведенную на рис.13.9. В этой таблице столбец "К выдаче" и значение суммы не заполнять.
2.4.1.2. Откройте новый лист в окне Excel. Пользуясь механизмом связывания, создайте в ячейках А1:D4 таблицу на основе исходной таблицы из Word. (Главная – Буфер обмена - Вставить – Специальная вставка – Текст и переключатель "Связать").
2.4.1.3. Дополните таблицу необходимым столбцом Е, записав в него соответствующие формулы. В ячейках Е2:Е4 запишите разности данных в столбцах С и D ("Начислено" - "Удержано"), а в ячейке Е5 - формулу для вычисления общей суммы, назначенной к выдаче по всем лицам.
2.4.1.4. Установите индивидуальные связи для ячеек столбца "К выдаче" и строки "Сумма" с соответствующими ячейками таблицы Word. Структура описанных связей представлена на рис.13.9. Просмотрите установленные связи, пользуясь командой Данные – Изменить связь.
2.4.1.5. Изменяя исходные данные в таблице Word, пронаблюдайте, как работают каналы динамической связи приложений, и как, соответственно, меняются данные в таблице Excel, и как они вновь передаются в Word.
2.4.2. Обмен графическими объектами.
2.4.2.1. Дополните таблицу, созданную ранее в рабочем листе Excel, графиком (3-х мерная столбиковая диаграмма), созданным на основе данных таблицы, приведенной на рис.13.10.
Рис.13.10
2.4.2.2. Пользуясь механизмом связывания объектов, представьте данный график в документе Word. Обратите внимание, как меняется диалог, в котором от пользователя запрашивается тип связываемых данных.
2.4.2.3. Изменяя числовые данные в исходной таблице Word, пронаблюдайте, как меняются данные и графики в листе Excel, и как они передаются обратно в документ Word.
3. Предъявите результаты преподавателю.
Цель: Знакомство со способами создания и использования макросов в MS Excel.
Темы: Создание и выполнение макросов. Макросы с относительными ссылками. Вложенные процедуры. Создание и применение пользовательских функций. Работа с модулем.
1. Создание и выполнение макросов.
1.1. Активизируйте вкладку «Разработчик», если она не представлена среди других ленточных вкладок. Для этого используйте команду Office – Параметры Excel – Основные - Показывать вкладку «Разработчик» на ленте.
1.2. Пользуясь командой Разработчик – Код – Запись макроса, задайте для создаваемого макроса имя "СтарыйАдрес", сочетание клавиш, сохранение в «этой книге» и сведения об авторе макроса в разделе описания (рис.14.1).
1.3. Начните запись макроса, позволяющего выполнить ввод в ячейки А5, А6, А7 следующий текст: А5: ПТС
А6: Ул. Большая Морская, д.3
А7: г. Санкт - Петербург.
(В процессе записи макроса старайтесь избегать ошибок и исправлений!)
1.4. Завершите запись макроса, воспользовавшись командой Разработчик – Код – Остановить запись или нажав соответствующую кнопку в строке состояния.
Рис.14.1
1.5. Очистите рабочий лист и перейдите в ячейку A5. Выполните созданный макрос, воспользовавшись командой Разработчик – Код – Макросы - Выполнить.
1.6. Снова очистите рабочий лист и повторите выполнение макроса, предварительно перейдя в любую другую ячейку.
1.7. Сравните результаты выполнения макроса в п.п. 1.5 и 1.6.
1.8. Очистите рабочий лист и запустите макрос, пользуясь сочетанием клавиш, которые были назначены при записи макроса. Если они не были назначены, то сделайте это с помощью команды Разработчик – Код – Макросы – Параметры.
1.9. Просмотрите содержимое макроса в редакторе Visual Basic, воспользовавшись командой Разработчик – Код – Visual Basic или командой Разработчик – Код – Макросы – Изменить или сочетанием клавиш Alt+F11 (Рис.14.2)
Рис.14.2
1.10. Измените текст в Module1, заменив номер дома в адресе ("3" замените на "3/5", вручную записав новый текст).
1.11. Вернитесь в окно Excel с помощью команды редактора Visual Basic File – Close and Return to Microsoft Excel или сочетания клавиш Alt+F11, выполните макрос "СтарыйАдрес" и убедитесь в его работоспособности.
2. Создание и выполнение макросов с относительными ссылками.
2.1. Создайте новый макрос под именем "НовыйАдрес" в режиме создания макроса с относительными ссылками.
2.1.1. Очистите рабочий лист ("Лист1").
2.1.2. Пользуясь командой Разработчик – Код – Запись макроса, активизируйте диалоговое окно Запись макроса, задайте для создаваемого макроса имя "НовыйАдрес" и заполните остальные поля.
2.1.3. Включите режим использования относительных ссылок, воспользовавшись командой Разработчик – Код – Относительные ссылки.
2.1.4. Выполните запись макроса, который будет вводить в три вертикально расположенные ячейки следующий текст:
ГУТ им. проф М А Бонч-Бруевича
г. Санкт-Петербург,
наб. реки Мойки, д.61
2.1.5. Закончите запись макроса нажатием соответствующей кнопки.
2.2. Перейдите в редактор Visual Basic и просмотрите содержимое макроса "Новый Адрес" (рис.14.3). Сравните тексты макросов "СтарыйАдрес" и "НовыйАдрес".
2.3. Вернитесь в окно Excel, поочередно выполните макросы "СтарыйАдрес" и "Новый Адрес". Отметьте различия в выполнении макросов.
2.4. Выполните оба макроса, пользуясь сочетаниями клавиш, назначенными при записи макросов. Если они не были назначены, то сделайте это с помощью команды Разработчик – Код – Макросы – Параметры.
Рис.14.3
3. Создание и использование вложенных процедур.
3.1. Откройте редактор Visual Basic (лист "Модуль1"). Внесите в программный код VBA изменения, показанные на рис.14.4.
Рис.14.4
В результате должна быть создана новая процедура, названная "Formatting". Макрос "СтарыйАдрес" вызывает процедуру "Formatting" в третьей строке программного кода. В процедуре "Formatting" использована функция MsgBox (вывод окна сообщения), которая позволяет приостановить выполнение процедуры "Formatting" до нажатия кнопки ОК и выдать подходящее случаю сообщение.
3.2. Вернитесь на лист Excel и выполните макрос "СтарыйАдрес".
3.3. Измените текст процедуры "Formatting" следующим образом:
установите размер шрифта 16 пт.;
установите зеленый цвет шрифта (индекс зеленого цвета - 50);
уберите подчеркивание текста.
3.4. Выполните макрос "СтарыйАдрес", внимательно проследив за правильностью его выполнения с учетом сделанных изменений.
3.5. Внесите изменения в текст макроса "СтарыйАдрес" таким образом, чтобы процедура "Formatting" действовала на ячейку A7.
3.6. Внесите изменения в текст макроса "СтарыйАдрес" таким образом, чтобы процедура "Formatting" действовала на ячейки A5, A6, A7.
3.7. Внесите изменения в текст макроса "Новый Адрес" таким образом, чтобы выполнение макроса "НовыйАдрес" начиналось с процедуры "Formatting".
3.8. Проследите за правильностью выполнения макросов "СтарыйАдрес" и "НовыйАдрес" с учетом сделанных в п.п. 3.5 - 3.7 изменений.
4. Создание и применение пользовательских функций.
4.1. Создайте таблицу, приведенную на рис.14.5, не заполняя ячейки С2:С4.
Рис.14.5
4.2. Откройте редактор Visual Basic (Alt+F11).
4.3. Вставьте новый лист модуля (Insert – Module).
4.4. Введите в лист модуля текст функции, позволяющей вычислить размер надбавки в зависимости от количества продаваемого товара. Текст такой функции приведен на рис.14.6.
Рис.14.6
4.5. Перейдите на лист Excel, содержащий таблицу (рис.14.5). Введите вручную в ячейку С2 функцию "Надбавка(количество, цена)", используя в качестве аргументов функции ссылки на ячейки А2 и В2.
4.6. В ячейку С3 введите формулу для вычисления надбавки, пользуясь Мастером функций (категория Определенные пользователем).
4.7. В ячейку С4 введите формулу для вычисления надбавки, пользуясь копированием формул.
4.8. Изменяя данные в ячейках А2:А4, убедитесь в правильности вычислений, производимых созданной функцией.
5. Предъявите результаты преподавателю.
Цель: Знакомство со способами создания пользовательского интерфейса
в MS Excel.
Темы: Вызов и выполнение макросов. Кнопки – команды на панели быстрого доступа. Назначение макросов объектам в листе. «Автоматически» выполняемые макросы (Auto_open).
1.Создание кнопки – команды на панели быстрого доступа.
1.1. Воспользуйтесь командой Office – Параметры Excel – Настройка и выберите Макросы в списке Выбрать команды из, укажите последовательно на макросы НовыйАдрес и СтарыйАдрес, нажмите кнопку Добавить. Затем измените изображения кнопок с помощью кнопки Изменить и введите имя в поле Отображаемое имя, которое в виде подсказки будет появляться при наведении мыши на кнопку - команду. (рис.14.7).
Рис.14.7
1.2. Поочередно выполните макросы "СтарыйАдрес" и "НовыйАдрес", используя созданные кнопки - команды.
2. Назначение макросов объектам в листе.
2.1. Назначение макросов командным кнопкам в листе.
2.1.1. Выберите команду Разработчик – Элементы управления – Вставить - Элементы управления формы. В палитре элементов выберите кнопку и поместите ее на листе. В диалоговом окне Назначить макрос объекту свяжите кнопку с одним из двух созданных макросов. Повторите эти действия для создания второй кнопки и свяжите ее со вторым макросом.
2.1.2. Отформатируйте объекты (кнопки) таким образом, чтобы их размеры не превышали 2 ячеек в ширину и 2 ячеек в высоту. На каждой кнопке должна быть надпись, поясняющая назначение кнопки и оформленная жирным шрифтом размером 10 пунктов.
2.1.3. Проверьте правильность выполнения макросов с помощью созданных кнопок.
2.2. Назначение макросов объектам.
2.2.1. С помощью команды Вставка – Иллюстрации – Фигуры создайте на рабочем листе два объекта из категории Фигуры, выбранные произвольно.
2.2.2. Используя контекстное меню, для каждого объекта назначьте макрос, который будет выполняться при активизации объекта.
2.2.3. Проверьте правильность выполнения макросов с помощью созданных графических объектов.
2.2.4. Назначьте макросы двум другим объектам (SmartArt, WordArt, Клипы, Иллюстрации) по собственному выбору и проверьте их работоспособность.
3. Предъявите полученные результаты преподавателю, а затем тщательно удалите объекты, созданные на панели быстрого доступа. Для этого используйте контекстное меню и команду Удалить с панели быстрого доступа.
4. Задание для самостоятельной работы.
4.1. Перейдите в режим ручного пересчета.
Рис.14.8
4.2. Создайте таблицу, приведенную на рис.14.8. Для этой таблицы создайте макрос с именем "Расчеты", который будет вычислять средние значения роста и веса, записывая их в соответствующие ячейки. Для вызова этого макроса создайте элемент управления типа "командная кнопка", при выборе (нажатии) которой будет выполняться расчет средних значений.
4.3. Восстановите режим автоматических вычислений.
5. Автоматически выполняемые макросы.
5.1. Создайте в новой книге макрос под именем «Auto_open», записывающий на лист в диапазон ячеек (В2:В4) три имени (Лена, Зина, Нина) и обрамляющий эти имена красной рамкой. Очистите лист книги от введенных данных и сохраните книгу под именем «Книга_МакОткр» в формате «Книга Excel с поддержкой макросов». Закройте книгу.
5.2. Откройте книгу «Книга_МакОткр» и убедитесь, что созданный макрос выполняется «автоматически» при открытии книги.
6. Предъявите результаты преподавателю.
Цель: Создание и использование экранных форм для пользовательских диалоговых окон в приложениях MS Excel.
Темы: Создание пользовательских форм в редакторе Visual Basic. Свойства форм. Элементы управления форм и панель инструментов.
Задачи: Отобразить в табличной области наименование, цену и количество изделий первого или второго сорта, пользуясь табличными данными (рис.15.1) и экранной формой, представленной на рис.15.2.
1. Создайте таблицу, представленную на рис.15.1. Дайте листу имя "Склад" и сохраните в файле «Формы».
Рис.15.1
2. Создайте макросы, необходимые для выполнения работы.
2.1. Создайте в автоматическом режиме два макроса СОРТ1 и СОРТ2, которые, используя расширенный фильтр, извлекут из списка (рис.15.1) товары 1-го и 2-го сортов. Критерии для извлечения данных следует записать до начала записи макросов, расположив первый (для сорта 1) в диапазоне G1:G2, а второй (для сорта 2) в диапазоне H1:H2. Извлеченные данные разместите в диапазонах, начинающихся с ячеек А15 и А25 соответственно. Запись макроса следует начать с команды активизации листа (щелчком по ярлыку листа). Проверьте правильность работы макросов, пользуясь командой Разработчик – Код – Макросы - Выполнить. Повторно проверьте работу макросов, сделав предварительно активным Лист2 книги.
2.2. Создайте макрос под именем ОЧИСТКА, который очищает содержимое ячеек диапазона результатов выборки (А15:D35) и проверьте его работоспособность.
3. Создайте первую экранную (пользовательскую) форму.
3.1. С помощью команды Разработчик – Код – Visual Basic или сочетания клавиш Alt+F11 активизируйте окно редактора Visual Basic Editor (VBE) и ознакомьтесь с его составляющими:
В верхней левой части редактора расположено окно проекта Project Explorer, а в нижней - окно свойств Properties Window, отображающее свойства активного объекта. Каждый проект содержит совокупность кодов и объектов VBA, обеспечивающих работу данного проекта и принадлежащих книге Excel. Компоненты, содержащие коды представлены в виде иерархии папок – Microsoft Excel Objects. К ним относятся листы книги Excel, модули (Modules) и формы (Forms), создаваемые пользователем.
3.2. Просмотрите созданные в автоматическом режиме макросы в Модуле1, находящемся в папке Modules. Для этого раскройте папку Modules и дважды щелкните на Модуле1.
3.3. Создайте пользовательскую форму, пользуясь командой меню редактора Visual Basic Insert - UserForm. Новая форма по умолчанию имеет имя UserForm1 (свойство Name) и такой же заголовок (свойство Caption), который отображается в строке заголовка окна формы.
3.3.1. Познакомьтесь с содержанием окна свойств формы, предварительно выделив объект – форму (View – Properties Window). Свойства могут быть сгруппированы по категориям или по алфавиту. Замените значение свойства формы Caption, которое отображается в заголовке на "Выбор сорта". Обратите внимание на имя формы – свойство объекта Name, которое используется для обращения к объекту и пока остается прежним, но может быть изменено по желанию пользователя.
3.3.2. Используйте по собственному усмотрению свойства формы, устанавливающие ее фон (BackColor)и расположение на экране (StartUpPosition).
3.3.3. Сохраните файл и активизируйте форму, нажав клавишу F5 или выбрав команду Run - Run Sub/UserForm, предварительно выделив форму. Закройте окно формы и вернитесь в редактор VBE.
3.3.4. Активизируйте панель инструментов (Toolbox), содержащую элементы управления, которые могут быть размещены в окне формы, выбором команды View – Toolbox, если она не отображена на экране.
3.3.5. Выберите на панели инструментов кнопки (CommandButton) и расположите их на форме как показано на рис.15.2. Используйте команду Format для изменения размеров кнопок и упорядочивания их расположения.
3.3.6. Активизируйте поочередно каждую кнопку и измените ее свойство Caption, которое обеспечивает вывод надписи на кнопке, в соответствии с рис.15.2
3.3.7. Выполните двойной щелчок мыши на первой кнопке. При этом в правой части экрана появится область кодов данной формы и шаблон процедуры обработки события, которая выполняется при щелчке мыши на выбранной кнопке. Название процедуры стандартно и состоит из имени объекта - CommandButton1 и события, которое ее вызывает – Click, записанные через подчеркивание.
Private Sub CommandButton1_Click()
Код процедуры
End Sub
Рис.15.2
3.3.8. Скопируйте код макроса Сорт1 из Модуля1 и вставьте его в тело процедуры CommandButton1_Click (на место, обозначенное "Код процедуры").
3.3.9. Повторите п.3.3.7.-3.3.8 для кнопок Сорт2 и Очистка.
Переключиться между окнами объектов и кодов можно с помощью кнопок View Code и View Object панели инструментов окна проектов.
3.3.10. Проверьте работоспособность кнопок в режиме отладки (F5).
3.3.11. Для кнопки Закрыть введите код Me.Hide. Проверьте работоспособность кнопки.
3.3.12. Активизируйте форму с помощью элемента управления – кнопки с листа Лист1 (склад). Для этого вернитесь на лист "склад" и с помощью команды Разработчик – Элементы управления – Вставить – Элементы ActiveX поместите кнопку на листе. Активизируйте окно ее свойств с помощью команды Разработчик – Элементы управления – Свойства и свойству Caption присвойте значение "Выбор сорта". После двойного щелчка на кнопке впишите в шаблон предложенной процедуры обработки события код UserForm1.Show. Обратите внимание на то, что код при этом сохраняется в объекте Лист1 (склад). Вернитесь на лист “склад”, выйдите из режима Конструктора и запустите форму с листа "склад" с помощью кнопки "Выбор сорта". Проверьте ее работу.
3.3.13. Замените код в процедурах событий кнопок Сорт1, Сорт2 и Очистка на вызов соответствующих процедур, записанных в виде макросов в Модуле1. Для этого в теле процедур событий запишите названия вызываемых процедур. Например, код кнопки Сорт1 будет выглядеть следующим образом:
Private Sub CommandButton1_Click()
Сорт1
End Sub
3.3.14. Проверьте работоспособность формы.
Цель: Знакомство с методами управления формами (обработка событий) средствами VBA.
Темы: Использование полей форм для ввода и обработки данных. Обработка событий.
Задачи: Отобразить в текстовых полях экранной формы (рис.15.3) характеристики (цену, количество и сорт) товара, наименование которого выбрано пользователем в раскрывающемся списке. Установка флажка указывает на те характеристики товара, которые пользователь желает увидеть в соответствующих текстовых полях экранной формы. Форма основана на данных таблицы, представленной на рис.15.1.
1. Создайте вторую экранную форму со свойством Name, имеющим значение UserForm2 и свойством Caption со значением СКЛАД
1.1. Расположите элементы управления в форме так, как показано на рис.15.3.
1.2. Поместите результат выбора элемента раскрывающегося списка ComboBox1 в ячейку A10 листа «склад».
Для этого сформируйте процедуру обработки события для объекта ComboBox1. Двойной щелчок на объекте активизирует окно его кодов. Выберите в левом раскрывающемся списке окна кодов объект ComboBox1, а в правом – событие Change, при котором элемент, выбранный из списка, будет помещен в ячейку А10 листа «склад». Эта процедура имеет вид:
Private Sub ComboBox1_Change()
Sheets("склад").Range("A10").Value = UserForm2.ComboBox1.Value
End Sub
Рис.15.3
1.3. Сформируйте процедуры обработки событий для трех флажков CheckBox1, CheckBox2 и CheckBox3 и события Click. В этих процедурах будет установлена связь между флажками и соответствующими ячейками листа «склад» (А11 – А13). В этих ячейках будет помещен результат установки флажка – логическое значение ИСТИНА, если флажок установлен, и логическое значение ЛОЖЬ - если не установлен. (Начальное значение False в этих ячейках устанавливается в процедуре инициализации формы).
Процедуры имеют вид, показанный для первого флажка:
Private Sub CheckBox1_Click()
Sheets("склад").Range("A11").Value = CheckBox1.Value
End Sub
1.4. Перейдите в режим отображения кодов формы и для объекта UserForm выберите из правого раскрывающегося списка событий событие Initialize, которое происходит при отображении формы на экране.
При инициализации формы сформируйте раскрывающийся список ComboBox1 с помощью метода AddItem, а также установите исходные значения в ячейках листа «склад», связанных с флажками так как показано далее:
Private Sub UserForm_Initialize()
' формирование раскрывающегося списка
ComboBox1.AddItem "бетон"
ComboBox1.AddItem "двери"
ComboBox1.AddItem "доски"
ComboBox1.AddItem "кирпич"
ComboBox1.AddItem "плитка"
ComboBox1.AddItem "рамы"
ComboBox1.AddItem "трубы"
' первоначально в списке отображается первый элемент с индексом (ListIndex) =0
ComboBox1.ListIndex = 0
' установка исходных значений False в ячейки листа, связанные с флажками
Sheets("склад").Range("A11").Value = False
Sheets("склад"). Range("A12").Value = False
Sheets("склад"). Range("A13").Value = False
End Sub
1.5. Сформируйте в рабочем листе «склад»в ячейках С11:С13 формулы, которые позволят по известному названию товара из списка и известному значению флажка вывести значения цены, количества и сорта выбранного товара. Формулы должны содержать функции ЕСЛИ и ПРОСМОТР. Например, формула, записанная в клетку С11 для определения цены товара, будет выглядеть следующим образом:
ЕСЛИ(А11;ПРОСМОТР(А10;А2:А8;В2:В8);" ")
1.6. Сформируйте процедуру обработки события для кнопки «результат». В этой процедуре необходимо отобразить значения цены, количества и сорта из диапазона ячеек С11:С13 в текстовых полях TextBox1, TextBox2 и TextBox3. Процедура имеет вид:
Private Sub CommandButton1_Click()
TextBox1.Value = Sheets("склад"). Range("C11").Value
TextBox2.Value = Sheets("склад"). Range("C12").Value
TextBox3.Value = Sheets("склад"). Range("C13").Value
End Sub
1.7. Сформируйте процедуру обработки события для кнопки «закрыть». Процедура имеет вид:
Private Sub CommandButton2_Click()
Me.Hide
End Sub
1.8. Проверьте работоспособность экранной формы в отладочном режиме с помощью клавиши F5 или команды Run Sub/UserForm.
1.9. Создайте в листе модуля процедуру Show_form. Тело процедуры должно содержать код
UserForm2.Show.
Sub Show_form()
UserForm2.Show
End Sub
1.10. Создайте в рабочем листе произвольный графический объект и назначьте ему процедуру, активизирующую созданную экранную форму.
1.11. Проверьте работоспособность процедуры.
Цель: Знакомство со стандартными диалоговыми окнами.
Темы: Использование функции MsgBox для сообщений и управления приложением.
1. Создайте окно сообщений, приведенное на рис.15.4.
Рис.15.4
1.1. На листе модуля создайте процедуру "Выход", один из вариантов текста которой приведен далее.
Sub Выход()
msg=MsgBox("Завершить работу? ", vbYesNo+vbQuestion+vbDefaultButton2, "Выход")
If msg = vbYes Then
ActiveWorkbook.Save
ActiveWindow.Close
Else
Exit Sub
End If
End Sub
1.2. Создайте в листе «склад» произвольный графический объект и назначьте ему процедуру "Выход". Проверьте работоспособность процедуры.
2. Предъявите результаты преподавателю.
2 Оформление надписи "показатели производства" на рис.7.2 производится факультативно.
3 Реализацию логической функции И для данных одного столбца осуществлять повторным размещением в области критериев заголовка данного столбца.
4 Для выполнения этого задания в ячейку, с которой будет начинаться область для извлечения данных, ввести название заголовка столбца "Фамилия".