Лабораторная работа Работа с объектами Microsoft Excel.Формы. Основные объекты MS Excel

Аннотация: Лекция посвящена описанию объектной модели MS Excel и подробному описанию методов, свойств и событий Application.

13.1. Особенности программирования для MS Excel

Microsoft Office Excel - это популярные электронные таблицы. Обычно, программируя для этой программы, преследуют такие цели:

  • Автоматизация вычислений.
  • Автоматизация ввода и обработки информации.
  • Работа с базами данных - вывод, ввод, анализ, визуализация информации.
  • Анализ финансовой и другой информации.
  • Создание систем для организации автоматизированного ввода данных
  • Математическое моделирование .

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

  • Первый вид (стиль A1) - это имя, состоящее из буквенного имени столбца и номера строки. Например, A1 - ячейка, находящаяся на пересечении столбца А (первого) и первой строки.
  • Другой вид - это индексы ячеек (стиль именования R1C1 ). Для адресации ячейки в таком стиле указывают номер строки (R - Row - строка) и номер столбца (C - Column - столбец), на пересечении которых расположена ячейка. Строки изначально пронумерованы, а номера столбцов начинаются с 1 - первому столбцу соответствует столбец A , второму - В и т.д. Например, (2, 3) - это адрес ячейки, находящийся на пересечении второй строки и третьего столбца, то есть, если переложить это на стиль A1 - получим ячейку С2 (рис. 13.1 .)


Рис. 13.1.

Для выполнения большинства операций в MS Excel применяются следующие объекты.

  • Excel.Application (Приложение) - объект, представляющий приложение Microsoft Excel, аналогичен Word.Application .
  • Workbook ( Рабочая книга ) - представляет рабочую книгу - аналог документа Microsoft Word. Однако, в Word мы работаем с данными, расположенными в документе, а в Excel на пути к данным есть еще один объект - рабочий лист.
  • Worksheet (Рабочий лист) - книга в MS Excel разбита на рабочие листы. Именно на листе расположены ячейки, которые могут хранить информацию и формулы.
  • Range (Диапазон) - может быть представлен в виде одной ячейки или группы ячеек. Этот объект заменяет множество объектов для работы с элементами документов (character , word и т.д.), которые применяются в Microsoft Word. В результате работа с листом становится очень четкой и удобной - чтобы работать с какой-либо ячейкой, надо знать лишь ее имя (в формате A1) или адрес (R1C1 ).
  • QueryTable (Таблица запросов) - этот объект используют для импорта в Microsoft Excel информации из баз данных. Подключение к базе данных , запрос информации и т.д. производятся средствами объекта, а итоги запроса выгружаются на лист MS Excel в виде обычной таблицы.
  • PivotTable (Сводная таблица) - это особый вид электронной таблицы Excel - она позволяет в интерактивном режиме обобщать и анализировать большие объемы информации, в частности, взятой из базы данных.
  • Chart (Диаграмма) - представляет собой диаграмму. Обычно их используют для визуализации данных.

Давайте начнем рассмотрение объектной модели MS Excel с объекта Application .

13.2. Объект Application

MsgBox Excel.Application.Name Листинг 13.1. Вывести имя приложения

После выполнения программы в окне сообщения отобразится имя приложения - в данном случае - Microsoft Excel . Очевидно, что свойство Name объекта Application возвращает имя приложения.

Теперь рассмотрим наиболее важные методы и свойства Application . Некоторые из них похожи на таковые в MS Word . Например, метод Quit точно так же, как в Word , закрывает приложение , свойство Visible отвечает за видимость окна программы и т.д.

13.3. Методы Application

13.3.1. Calculate - принудительный пересчет

Этот метод, вызываемый для объекта Application , позволяет пересчитать все открытия книги. Его же можно вызывать для отдельных книг (объект Workbook ) листов ( Worksheet ), ячеек и их диапазонов (Range ). Например, код из листинга 13.2 . позволяет пересчитать все открытые книги.

Application.Calculate Листинг 13.2. Пересчитать все открытые книги

13.3.2. GoTo - переход в ячейку

13-02-Excel GoTo.xlsm - пример к п. 13.3.2.

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

Полный вызов метода выглядит так:

Goto(Reference, Scroll)

В качестве параметра Reference используется ссылка на ячейку или диапазон ячеек , которые должны быть выделены после вызова метода. Так же здесь может быть использовано имя макроса - тогда он будет запущен.

Параметр Scroll отвечает за "перемотку" листа Excel к выделенным ячейкам - так, чтобы левый верхний угол выделения совпадал бы с левым верхним углом отображаемой области листа. Если Scroll установлен в True - лист перематывается, если в False - нет.

Например, такой вызов (листинг 13.3 .) позволяет выделить ячейку H500 на активном листе.

Application.Goto _ Reference:=ActiveSheet.Range("H500"), _ Scroll:=True Листинг 13.3. Выделить ячейку H500

Как видите, обращение к активному листу очень напоминает обращение к активному документу в MS Word. Обратите внимание на то, что мы используем полное обращение к методу - Application.GoTo - как вы знаете, обычно свойства и методы объекта Application можно использовать в коде, не указывая этого объекта. Однако, если в этом случае не указать Application , то вместо метода GoTo программа попытается выполнить оператор безусловного перехода GoTo .

13.3.3. SendKeys - имитация нажатий на клавиши клавиатуры

13-03-Excel SendKeys.xlsm - пример к п. 13.3.3.

Очень интересный метод - позволяет передавать в активное окно приложения нажатия клавиш. Полный вызов метода выглядит так:

SendKeys(Keys, Wait)

Параметр Keys позволяет задавать клавиши, нажатия которых будут переданы приложению. Метод поддерживает эмуляцию как алфавитно-цифровых, так и управляющих клавиш , для которых применяется специальная кодировка. Алфавитно-цифровые клавиши указываются при вызове в своем обычном виде Например, для передачи символа "Ф" нужно указать его при вызове метода и т.д.

Чтобы передать приложению нажатия клавиши Backspace - используйте код {BS} . Для передачи нажатия кнопки Enter используйте значок ~ (

Microsoft Excel (также иногда называется Microsoft Office Excel) - программа для работы с электронными таблицами, созданная корпорацией Microsoft для Microsoft Windows, Windows NT и Mac OS. Она предоставляет возможности экономико-статистических расчетов, графические инструменты и, за исключением Excel 2008 под Mac OS X, язык макропрограммирования VBA (Visual Basic для приложений). Microsoft Excel входит в состав Microsoft Office и на сегодняшний день Excel есть одним из наиболее популярных программ в мире.

Ценной возможностью Excel есть возможность писать код на основе Visual Basic для приложений (VBA). Этот код пишется с использованием отдельного от таблиц редактора. Управление электронной таблицей осуществляется с помощью объектно-ориентированной модели кода и данных. С помощью этого кода данные входных таблиц будут мгновенно обделываться и отображаться в таблицах и диаграммах (графиках). Таблица становится интерфейсом кода, разрешая легко работать, изменять его и руководить расчетами.

С помощью Excel можно анализировать большие массивы данных. В Excel можно использовать больше 400 математических, статистических, финансовых и других специализированных функций, связывать разные таблицы между собой, выбирать произвольные форматы представления данных, создавать иерархические структуры. Воистину безграничные методы графического представления данных: кроме нескольких десятков встроенных типов диаграмм, можно создавать свои, что настраиваются типы, помогают наглядно отобразить тематику диаграммы. Те, кто только осваивает работу по Excel, по достоинству оценят помощь "мастеров" - вспомогательных программ, которые помогают при создании диаграмм. Они, как добрые волшебники, задавая наводящие вопросы о предвиденных дальнейших шагах и показывая, в зависимости от планированного ответа, результат, проведут пользователя "за руку" за всеми этапами построения диаграммы кратчайшим путем.

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

В Microsoft Excel есть два основных типа объектов: книга и письмо.

Книга в Microsoft Excel представляет собой файл, который используется для обработки и хранение данных. Каждая книга может состоять из нескольких листов, поэтому в одном файле можно поместить разнообразные сведения и установить между ними необходимые связи.

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

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

В Microsoft Excel очень много разнообразных функций, например:

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

2. Функции даты и времени – большинство функций этой категории ведает преобразованиями даты и времени в разные форматы. Две специальные функции СЕГОДНЯ и ТДАТА вставляют в каморку текущую дату (первая) и дату и время (вторая), обновляя их при каждом вызове файла или при внесение любых изменений в таблицу.

5. Ссылка и массивы. В этой категории находятся функции, которые разрешают обратиться к массиву данных (по колонке, строке, прямоугольному интервалу) и получить из него разнообразнейший информацию: номера столбцов и строк, у него входят, их количество, содержимое нужного вам элемента массива; можно найти, в какой каморке этого массива нужное число или текст и т.д.

6. Текст – В этой группе десятка два команд. С их помощью можно сосчитать количество символов в воротничке, включая пробелы (ДЛСТР), узнать код символа (КОДСИМВ), узнать, какой символ стоит первым (ЛЕВСИМВ) и последним (ПРАВСИМВ) в строке текста, поместить в активную каморку некоторое количество символов из другой воротнички (ПСТР), поместить в активную каморку весь текст из другого каморки большими (ПРОПИСН) или сточными буквами (СТРОЧН), проверить, или совпадают две текстовые каморки (СОВПАД), найти некоторый текст (ПОИСК, НАЙТИ) и заменить его другим (ЗАМЕНИТЬ).

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

8. Работа с базой данных – здесь можно найти команды статистического учета (БДДИСП - дисперсия по выборке из базы, БДДИСПП - дисперсия по генеральной совокупности, ДСТАНДОТКЛ - стандартное отклонение по выборке), операции со столбцами и строками базы, количество непустых (БСЧЕТА) или (БСЧЕТ) ячеек и т.д.

9. Мастер диаграмм – встроенная программа EXCEL, что упрощает работу с основными возможностями программы.

Назначение MS Excel.

MS Excel – одна из самых популярных сегодня программ электронных таблиц. Ею пользуются ученые, бухгалтеры, журналисты и т.д., с ее помощью ведут разнообразные таблицы, списки и каталоги, составляют финансовые и статистические отчеты, подсчитывают состояние торгового предприятия, обрабатывают результаты научного эксперимента, ведут учет, готовят презентационные материалы. Возможности Excel очень высоки. Обработка текста, управление базами данных – программа настолько мощна, что во многих случаях превосходит специализированные программы-редакторы или программы баз данных. Такое многообразие функций может поначалу запутать, чем заставить применять на практике. Но по мере приобретения опыта начинаешь по достоинству ценить то, что границ возможностей Excel тяжело достичь.

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

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

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

У Excel есть еще масса преимуществ. Это очень гибкая система "растет" вместе с потребностями пользователя, меняет свой вид и подстраивается под Вас. Основу Excel составляет поле клеток и меню в верхней части экрана. Кроме этого на экране могут быть расположены до 10 панелей инструментов с кнопками и другими элементами управления. Есть возможность не только использовать стандартные панели инструментов, но и создавать свои собственные.

Заключение.

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


©2015-2019 сайт
Все права принадлежать их авторам. Данный сайт не претендует на авторства, а предоставляет бесплатное использование.
Дата создания страницы: 2016-08-08

Лабораторная работа

ИЗУЧЕНИЕ ИНТЕРФЕЙСА ТАБЛИЧНОГО ПРОЦЕССОРА MS EXCEL 2010. ПРОСТЫЕ ВЫЧИСЛЕНИЯ

Цели работы:

1)ознакомиться с возможностями табличного процессора;

2)ознакомиться с пользовательским интерфейсом программы MS Excel 2010;

3) ознакомиться с основными приемами работы в MS Excel 2010.

Задание:

1) изучите п. 1 «Учебный материал»;

2) выполните задания, приведенные в п. 2;

3) ответьте на контрольные вопросы (п. 3).

УЧЕБНЫЙ МАТЕРИАЛ

Основные понятия табличного процессора

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

Функции табличного процессора весьма разнообразны:

§ создание и редактирование таблиц;

§ оформление и печать таблиц;

§ создание многотабличных документов, объединенных формулами;

§ построение диаграмм;

§ работа с ЭТ как с базой данных; выборка данных по запросам;

§ создание итоговых и сводных таблиц;

§ решение задач типа «что-если» путем подбора параметров;

§ решение оптимизационных задач;

§ создание программ-макросов с помощью встроенного языка программирования Visual Basic for Applications (VBA).

Табличный процессор Excel является составной частью интегрированного программного пакета Microsoft Office.

После запуска Excel 2010 на экране появляется окно следующей структуры:

1. Заголовок программы .

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

Рис. 1. Панель быстрого доступа

3. Лента. В Excel 2010 меню и панели инструментов выполнены в виде ленты. Команды упорядочены в логические группы, собранные на вкладках. Вкладки ориентированы на выполнение задач. Группы на каждой вкладке разбивают задачу на ее составляющие. Кнопки команд в каждой группе служат для выполнения команд или отображения меню команд. Значок (кнопка) группы – маленький квадрат в правом нижнем углу группы элементов во вкладке (рис. 2). Щелчок по значку открывает соответствующее этой группе диалоговое окно или область задач для расширения функциональных возможностей. Например, значок группы Шрифт вкладки Главная открывает диалоговое окно Формат ячейки . А значок группы Буфер обмена отображает область задач Буфер обмена .Не каждая группа имеет значок.



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

Рис. 2. Кнопки групп

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

Перемещаться по ленте можно с помощью клавиатуры: если нажать клавишу , на ленте появятся подсказки для перехода к соответствующей вкладке (рис. 3).

Рис. 3. Клавиши перемещения по ленте

Перейдя на вкладку, можно увидеть подсказки по клавишам перехода по текущей вкладке (рис. 4).

Рис. 4. Клавиши перехода вкладки Главная

Заменить ленту панелями инструментов или меню предыдущих версий приложения Microsoft Excel нельзя. Удалить ленту также нельзя. Однако, чтобы увеличить рабочую область, ленту можно скрыть (свернуть). Способы:

1. Нажмите кнопку Свернуть ленту , расположенную в правой части линии названий вкладок. Для возврата нажать на кнопку Развернуть ленту .

2. Для быстрого скрытия/отображения ленты достаточно двойного щелчка по заголовку любой вкладки.

3. Ленту можно свернуть и иначе:

a) щелкните правой кнопкой мыши в любом месте ленты;

b) в контекстном меню выберите команду Свернуть ленту .

4. Чтобы свернуть или восстановить ленту, можно также нажать комбинацию клавиш .

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

1) щелкните правой кнопкой мыши в любом месте ленты;

2) в контекстном меню выберите команду Настройка ленты .

Также настройка ленты производится в окне ПараметрыExcel в категории Настройка ленты меню Файл .

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

5. Мини-панели инструментов. Мини-панели инструментов (рис. 5) содержат основные наиболее часто используемые элементы для оформления текста документа, рисунков, диаграмм и других объектов. В отличие от других приложений Office 2010 (Word, PowerPoint и др.) в Excel 2010 мини-панель не отображается автоматически при выделении фрагмента листа. Для ее отображения щелкните правой кнопкой по выделенной области. Состав элементов мини-панелей постоянный и неизменный.


Рис. 5. Мини-панель инструментов и контекстное меню

6. Строка формул , состоящая из трех частей:

a) адреса активной ячейки ;

b) кнопок Отмена , Ввод , которые появляются в режиме ввода или правки, и кнопки Вставка функции f x ;

c) содержимого активной ячейки.

Для просмотра и редактирования содержимого выделенной ячейки можно увеличить высоту строки формул. Для этого щелкните по кнопке Развернуть строку формул . Если и в этом случае отображается не все содержимое ячейки, можно воспользоваться полосой прокрутки. Для того чтобы привести строку формул в исходное состояние, щелкните по кнопке Свернутьстроку формул. Убрать/отобразить строку формул можно следующим образом: Файл ® Параметры ® Дополнительно ® категория Экран ® Показывать строку формул .

7. Координатная строка – содержит имена столбцов.

8. Координатный столбец – содержит номера строк.

9. Рабочее поле.

10. Горизонтальная и вертикальная полосы прокрутки .

11. Ярлычки листов с кнопками перехода по рабочим листам.

12. Строка состояния , в которой указываются режимы работы Готово , Ввод , Правка ; режимы просмотра книги Обычный, Разметка страницы, Страничный ; масштабная линейка.

Основные объекты Excel

1. Столбец . Таблица Excel содержит 16384 столбца(2 14). Столбцыидентифицированы латинскими буквами. Так как их только 26, то после Z обозначение столбцов идет сдвоенными буквами AA, AB, AC, …, GA, GB, GC, …, HX, HY, HZ, а после столбца ZZ – строенными ААА, ААВ, ААС, …, AAZ, ABA , … Заканчивается нумерация на столбце XFD. Чтобы быстро перейти к первому или последнему столбцу (строке) рабочего листа, нужно нажать клавишу <Ctrl> и соответствующую клавишу управления курсором.

2. Строка – их в таблице 1048576 (2 20).

3. Ячейка – место пересечения строки и столбца. Каждая ячейка имеет уникальный адрес, в котором указывается имя столбца и номер строки, на пересечении которых она расположена. Примеры использования адресов ячеек в стиле A1 показаны в таблице 1.

Таблица 1

Стиль ячеек A1

Excel поддерживает альтернативную систему указания ячеек, называемую R1C1 (от англ. слов Row – строка и Column – колонка). В этой системе и столбцы, и строки таблицы пронумерованы, а номер строки предшествует номеру столбца. Например, ячейка А1 называется R1C1 (строка 1, столбец 1). Ячейка В1 – это R1C2 (строка 1, столбец 2). Перейти к альтернативному стилю и обратно можно, зайдя в меню Файл ® Параметры ® Формулы ® категория Работа с формулами ® стиль ссылок R1C1 .

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

4. Блок ячеек – это прямоугольник, в котором указываются адреса ячеек левого верхнего и нижнего правого углов, разделенных двоеточием, например А1:С5.Если в выполняемом действии указан блок ячеек, то задействованы все его ячейки.

5. Рабочий лист – это созданная таблица для решения задачи, диаграмма, макрос, рисунок. Стандартное имя листа – Лист1, Лист2, …. С рабочими листами можно выполнять следующие действия :

§ переименование;

§ удаление;

§ вставка;

§ перемещение;

§ копирование.

Эти действия выполняются с помощью контекстного меню при установленном указателе мыши на ярлычке листов или в группе Ячейки вкладки Главная (рис. 6).

Рис. 6. Группа Ячейки вкладки Главная

Меню Файл ® Параметры ® Дополнительно ® категория Показать параметры для следующей книги ® Показывать ярлычки листов позволяет убрать/отобразить ярлычки листов.

6. Рабочая книга – это файл, который хранится на диске и содержит один или несколько листов. По умолчанию рабочая книга имеет имя Книга1, Книга 2 , …. Сохранить файл можно с помощью пункта меню Файл ® Сохранить . Команда Сохранить как выбирается, если нужно сохранить уже записанный на диск файл под новым именем. Файлам Excel 2010 по умолчанию присваивается расширение .xlsx .

7. Диаграмма – это графическое отображение данных таблицы. Может храниться на отдельном листе, а может сопровождаться текстом или таблицей.

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

9. Модули Visual Basic – программы, называемые макросами и созданные на языке программирования Visual Basic.

Типы данных

Как правило, в ЭТ используются следующие типы данных:

1. Текст – любая последовательность символов, используемая в основном для заголовков таблиц, строк, столбцов и комментариев.

2. Число . В ячейке Excel можно отобразить три типа числовых данных (констант):

a) целые числа – это последовательность цифр от 0 до 9 со знаком или без него: +25; –100.

b) вещественные числа с фиксированной запятой – это десятичные дроби, в которых целая часть отделяется от дробной запятой: 28,25; –3,765.

c) вещественные числа с плавающей запятой – это числа, записанные в следующей форме: 1,5Е+03 или 2Е-08 . Такую запись еще называют экспоненциальной формой записи числа (научный формат).

По умолчанию правильно введенное число выравнивается по правому краю ячейки. Неправильно введенное число считается текстом и выравнивается по левому краю. Если число не поместилось по ширине ячейки, то вся ячейка заполняется символом # (рис. 7).

Рис. 7. Вид чисел на рабочем листе

3. Формула – это выражение, состоящее из чисел, адресов ячеек, функций и знаков операций и начинающееся со знака = . Например:

А1*20%+12*A1*D12.

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

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

5.Дата – это дата, представленная в различных форматах, с которой можно производить арифметические и логические операции. Например, 21.10.2011; 21 октября 2011 г. и др.

Копирование ячеек

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

1) вкладка Главная ® группа Буфер обмена ® команда Копировать ;

2) с помощью команды Копировать контекстного меню;

3) с помощью комбинации клавиш ;

4) с помощью техники «перетащить-и-оставить» при нажатой клавише . В этом случае рядом с указателем мыши появляется знак + (признак копирования);

5) с использованием маркера в правом нижнем углу копируемой ячейки. При отсутствии этого маркера необходимо воспользоваться командой Файл ® Параметры ® Дополнительно ® категория Параметры правки ® Разрешить маркеры заполнения и перетаскивания ячеек .

Фризен Ирина Григорьевна – кандидат педагогических наук, преподаватель информационных технологий, автор многочисленных работ в сфере информационных технологий.

Настоящее учебное пособие написано в соответствии с государственной программой изучения дисциплины для средних специальных учебных заведений по специальности 230103 «Автоматизированные системы обработки информации и управления».

Офисное программирование

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

Учебное пособие ставит перед собой цель помочь студентам изучить данную дисциплину в полном объеме, отвечающем требованиям государственного стандарта по дисциплине.

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

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

Книга:

Разделы на этой странице:

Использование объектов Range и Selection

В Excel наиболее важным является объект Application. Объект Application (приложение) является главным в иерархии объектов Excel и представляет само приложение Excel. Он имеет более 120 свойств и 40 методов. Эти свойства и методы предназначены для установки общих параметров приложения Excel. В иерархии Excel объект Workbook (рабочая книга) идет сразу после объекта Application и представляет файл рабочей книги. Рабочая книга хранится либо в файлах формата XLS (стандартная рабочая книга), либо XLA (полностью откомпилированное приложение). Свойства и методы рабочей книги позволяют работать с файлами. Однако наиболее «употребляемым» на практике является объект Range, который наилучшим образом отображает возможности использования VBA в Excel (о свойствах объекта Range см. табл. 19, о методах – табл. 20).

В иерархии Excel объект Range (диапазон) идет сразу после объекта worksheet. Объект Range является одним из ключевых объектов VBA. Объект selection (выбор) возникает в VBA двояко – либо как результат работы метода Select, либо при вызове свойства selection. Тип получаемого объекта зависит от типа выделенного объекта. Чаще всего объект Selection принадлежит классу Range, и при работе с ним можно использовать свойства и методы объекта Range. Интересной особенностью объектов Range и Selection является то, что они не являются элементами никакого семейства объектов.

При работе с объектом Range необходимо помнить, как в Excel ссылаются на ячейку рабочего листа.

Задание групп строк и столбцов с помощью объекта Range

Если в диапазоне указываются только имена столбцов или строк, то объект Range задает диапазон, состоящий из указанных столбцов или строк. Например, Range («а: с») задает диапазон, состоящий из столбцов а, в и с, а Range(«2:2») – из второй строки. Другим способом работы со строками и столбцами являются методы Rows (строки) и columns (столбцы), возвращающие коллекции строк и столбцов. Например, столбцом а является columns (1), а второй строкой – Rows (2).

Связь объекта Range и свойства Cells

Так как ячейка является частным случаем диапазона, состоящим только из единственной ячейки, объект Range также позволяет работать с ней. Объект Cells (ячейки) – это альтернативный способ работы с ячейкой. Например, ячейка А2 как объект описывается Range («A2») или Cells (l, 2). В свою очередь, объект cells, вкладываясь в Range, также позволяет записывать диапазон в альтернативном виде, который иногда удобен для работы, а именно Range(«А2:СЗ») и Range(Cells(1,2), Cells(3,3)) определяют один и тот же диапазон.

Таблица 19

Свойства объекта Range




Методы объекта Range



Методы объекта Range, использующие команды Excel

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

Метод AutoFill

Метод AutoFill (автозаполнение) автоматически заполняет ячейки диапазона элементами последовательности. Метод AutoFill отличается от метода DataSeries тем, что явно указывается диапазон, в котором будет располагаться прогрессия. Вручную этот метод эквивалентен расположению указателя мыши на маркере заполнения выделенного диапазона (в который введены значения, порождающие создаваемую последовательность) и протаскиванию маркера заполнения вдоль диапазона, в котором будет располагаться создаваемая последовательность.

Синтаксис:

объект. AutoFill(диапазон, тип)

Аргументы:

Диапазон Диапазон, с которого начинается заполнение тип Допустимые значения: xlFillDefault, xlFillSeries, xlFillCopy, xlFillFormats, xlFillValues,xlFillDays, xlFillWeekdays, xlFillMonths, xlFillYears, xlLinearTrend, xlGrowthTrend. По умолчанию xlFillDefault

Метод AutoFilter

Метод AutoFilter (автофильтр) представляет собой простой способ запроса и фильтрации данных на рабочем листе. Если AutoFilter активизирован, то каждый заголовок поля выделенного диапазона данных превращается в поле с раскрывающимся списком. Выбирая запрос на вывод данных в поле с раскрывающимся списком, осуществляется вывод только тех записей, которые удовлетворяют указанным условиям. Поле с раскрывающимся списком содержит следующие типы условий: Все (All), Первые десять (Тор 10), Условие (Custom), конкретный элемент данных, Пустые (Blanks) и Непустые (NonBlanks). Вручную метод запускается посредством выбора команды Данные, Фильтр, Автофильтр (Data, Filter, AutoFilter).

При применении метода AutoFilter допустимы два синтаксиса.

Синтаксис 1:

Объект. AutoFilter

В этом случае метод AutoFilter выбирает или отменяет команду Данные, Фильтр, Автофильтр (Data, Filter, AutoFilter), примененную к диапазону, заданному в аргументе объект.

Синтаксис 2:

Объект. AutoFilter (field, criteria1, operator, criteria2)

В этом случае метод AutoFilter выполняет команду Данные, Фильтр, Автофильтр (Data, Filter, AutoFilter) по критериям, указанным в аргументе.

Аргументы:

field Целое, указывающее поле, в котором производится фильтрация данных

Criteria1 Задают два возможных условия фильтрации и criteria2 поля. Допускается использование строковой постоянной, например 101, и знаков отношений >, <,>=, <=, =, <>

operator Допустимые значения: X1And (логическое объединение первого и второго критериев); X1or (логическое сложение первого и второго критериев)

При работе с фильтрами полезны метод showAllData и свойства FilterMode и AutoFilterMode.

Метод ShowAllData Показывает все отфильтрованные и неотфильтрованные строки рабочего листа

свойство FilterMode Допустимые значения: True (если на рабочем листе имеются отфильтрованные данные со скрытыми строками), False (в противном случае)

Свойство AutoFilterMode Допустимые значения: True (если на рабочем листе выведены раскрывающиеся списки метода AutoFilter), False (в противном случае)

Метод GoalSeek

Метод GoalSeek (подбор параметра) подбирает значение параметра (неизвестной величины), являющееся решением уравнения с одной переменной. Предполагается, что уравнение приведено к виду: правая часть является постоянной, не зависящей от параметра, который входит только в левую часть уравнения. Вручную метод GoalSeek выполняется с помощью команды Сервис, Подбор параметра (Tools, Goal Seek). Метод GoalSeek вычисляет корень, используя метод последовательных приближений, результат выполнения которого, вообще говоря, зависит от начального приближения. Поэтому для корректности нахождения корня надо позаботиться о корректном указании этого начального приближения.

Синтаксис:

Объект. GoalSeek(Goal, ChangingCell)

Аргументы:

Объект Ячейка, в которую введена формула, являющаяся правой частью решаемого уравнения. В этой формуле роль параметра (неизвестной величины) играет ссылка на ячейку, указанную в аргументе ChangingCell

Goal Значение левой части решаемого уравнения, не содержащей параметра

Точность, с которой находится корень и предельно допустимое число итераций, используемых для нахождения корня, устанавливается свойствами Maxchange и Maxiterations объекта Application. Например, определение корня с точностью до 0,0001 максимум за 1000 итераций устанавливается инструкцией:

With Application

Maxiterations = 1000

MaxChange = 0.0001

Вручную эти величины устанавливаются на вкладке Вычисления (Calculation) диалогового окна Параметры (Options), вызываемого командой Сервис, Параметры (Tools, Options).

Метод Sort

Сортировка позволяет выстраивать данные в лексикографическом порядке по возрастанию или убыванию. Метод sort осуществляет сортировку строк списков и баз данных, а также столбцов рабочих листов с учетом до трех критериев, по которым производится сортировка. Сортировка данных вручную совершается с использованием команды Данные, Сортировка (Data, Sort).

Синтаксис:

Объект. Sort(key1, order1, key2, order2, key3, order3, header, orderCustom, matchCase, orientaticn)

Аргументы:

Объект Диапазон, который будет сортироваться

Order1

order2 Задает порядок упорядочивания. Допустимые значения: xlAscending (возрастающий порядок); xlDescending (убывающий порядок)

header Допустимые значения: xlYes (первая строка диапазона содержит заголовок, который не сортируется); xlNo (первая строка диапазона не содержит заголовка, по умолчанию считается данное значение); xlGuess (Excel решает, имеется ли заголовок)

orderCustom Пользовательский порядок сортировки. По умолчанию используется Normal

matchCase Допустимые значения: True (учитываются регистры) и False (регистры не учитываются)

orientation Допустимые значения: xlTopToBottom (сортировка осуществляется сверху вниз, т. е. по строкам); xlLeftToRight (слева направо, т. е. по столбцам)

Например, диапазон А1:С20 рабочего листа лист1 сортируется следующей командой в порядке возрастания так, что первоначальная сортировка происходит по первому столбцу этого диапазона, а второстепенная – по второму:

Worksheets(«Лист»).Range(«A1: C20»).Sort _

key1:=Worksheets(«Sheet1»).Range(«A1»), _

key2:=Worksheets («Sheet1»).Range («B1»)

Округление чисел

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

1 способ

Функция Round

X= round(2.505, 2)

Значение х будет 2,5, а не 2,51.

Поэтому часто не используется.

2 способ

Функция Format

sngОкругление=Format(SngНеокругленное, “#, 0.00”)

3 способ

Функция FormatNumber

SngОкругление= FormatNumber(sbgНеокругленное, 2)

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

Примечание. Переменная, в которую помещается округленное значение, должна иметь тип string, single, double, decimal, currency или variant, но не тип integer или long.

Приведение данных

Для приведения введенных данных к нужному типу в VBA включен обширный набор функций, одна из которых – CDBL. Синтаксис:

CDbl(выражение)

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

А = Cdbl(textBoxN.text)

После чего с данной переменной можно работать.

Для выведения значений непосредственно в ячейки книги Excel удобно использовать объект Range:

range(«A5»).value = a

Функцией, обратной по действию к CDbl, является функция CStr – она переводит числа в строки и удобна для вывода результата либо в ячейку на лист, либо в то или иное текстовое окно.

TextBoxN.text = CStr(.Range(«A8»).value)

– считывание значения с ячейки и вывод его в текстовое окно.

Функция Trim (строка) возвращает копию строки, из которой удалены пробелы, находящиеся в начале и конце строки.

Создание VBA-программ

Использование метода GoalSeek

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


Рис. 92. Разработанная форма примера 41 в рабочем состоянии

Технология выполнения

1. Запустите приложение Excel, сохраните документ.

2. Перейдите в редактор VBA.

3. Создайте форму согласно приведенному рис. 92.

4. На листе Excel расположите необходимый текст (оформление), предусмотрев соответствующие ячейки вывода информации (рис. 93).


Рис. 93. Вывод результатов на лист excel после запуска формы примера 41

5. Обработайте кнопки.

Кнопка Вычислить

Private Sub CommandButton1_Click()

Dim a, b, c As Double

a = CDbl(TextBox1.Text)

b = CDbl(TextBox2.Text)

c = CDbl(TextBox3.Text)

With ActiveSheet

Range(«b3»).Value = a

Range(«b4»).Value = b

Range(«b5»).Value = c

Range(«b6»).FormulaLocal = «=b3*b7^3+b4*sin(b7)»

Range(«b6»).GoalSeek Goal:=c, changingCell:=Range(«b7»)

TextBox4.Text = CStr(.Range(«b7»).Value)

TextBox4.Text = FormatNumber(TextBox4.Text, 2)

Кнопка Закрыть

Private Sub CommandButton2_Click()

Процедура инициализации формы

Private Sub UserForm_initialize()

Worksheets(1).Visible = False

Использование методов AutoFill при заполнении таблиц

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

Москва 2007

Курс лекций

Муратова С.Ю.

Кафедра автоматизированных систем управления

ФЕДЕРАЛЬНОЕ АГЕНТСТВО ПО ОБРАЗОВАНИЮ

ГТУ «МОСКОВСКИЙ ГОСУДАРСТВЕННЫЙ ИНСТИТУТ СТАЛИ И СПЛАВОВ»

Прикладная информатика

(наименование направления)

Информатика и вычислительная техника

(наименование направления)

Макросы и приложения

(наименование дисциплины)


Введение. 5

1. Объекты Excel 6

1.1. Объекты, их свойства и методы.. 6

1.1.1. Свойства: присвоение и использование значений. 6

1.1.2. Примеры методов рабочей книги Excel 8

1.1.2.1. Вызов метода. 8

1.1.2.2. Передача аргумента в метод. 9

1.2.1. Семейства как объекты.. 12

1.2.3. Исключение: объект Range 13

1.3. Иерархия объектов Excel 14

1.3.1. Иерархическая структура. 15

1.3.2. Доступ к объектам через свойства и методы.. 17

1.4. Объект Application. 18

1.4.1. Свойства объекта Application. 18

1.4.2. Методы объекта Application. 19

1.5. Объект Workbook и семейство Workbooks. 20

1.5.1. Свойства объекта Workbook и семейства Workbooks. 20

1.5.2. Методы объекта Workbook и семейства Workbooks. 21

1.5.3. Функции, используемые для работы с файлами. 21

1.5.4. Функция MsgBox. 24

1.5.5. Функция InputBox. 28

1.6. Объект Worksheet 29

1.6.1. Свойства объекта Worksheet и семейства Worksheets. 29

1.6.2. Методы объекта Worksheet и семейства Worksheets. 30

1.7. Объект Range. 31

1.7.1. Свойства объекта Range. 31

1.7.2. Методы объекта Range. 32

2. Переменные. 33

2.1. Допустимые имена. 33

2.2. Типы данных переменных VBA.. 33

2.3. Описание переменной. 34

2.4. Использование переменных. 35

2.5. Преимущества переменных. 36

2.6. Объектные переменные. 37

2.6.1. Задание объектной переменной. 38

2.6.2. Объектные переменные общего типа. 38

2.6.3. Объектные переменные конкретных типов. 39

2.6.4. Преимущества объектных переменных. 39

2.7. Неявное описание переменных и тип Variant 40

2.8. «Плюсы» и «минусы» типа Variant 41

2.9. Обязательное описание переменных. 42

2.10. Типы данных по умолчанию.. 44

2.11. Пользовательские типы данных. 45

3. Массивы.. 46

3.1. Объявление массива. 48

3.2. Использование массива. 49

3.3. Номер первого элемента и границы массива. 51

3.4. Динамические массивы.. 52

3.5. Пять функций для работы с массивами. 56

3.5.1. Функция Array. 56

3.5.2. Функция Erase. 56

3.5.3. Функция IsArray. 57

3.5.4. Функции LBound и UBound. 58


4. Константы.. 58

5. Вызов одной программы из другой. 59

5.1. Передача данных при вызове программы.. 60

6. Функции. 63

7. Область видимости переменных, констант, подпрограмм и функций. 65

7.1. Область видимости переменных. 66

7.1.1. Переменные уровня процедуры.. 66

7.1.2. Переменные уровня модуля. 68

7.1.3. Переменные уровня проекта. 69

7.1.4. Сохраняемые переменные. 70

7.1.5. Область видимости подпрограмм и функций. 71

7.1.6. Сохраняемые подпрограммы и функции. 72

8. Управляющие структуры.. 73

8.1. Управляющая инструкция If-Then-Else. 74

8.1.1. Дополнительное условие Elself. 76

8.2. Управляющая инструкция For-Next 77

8.3. Управляющая инструкция While-Wend. 81

8.4. Управляющая инструкция Do-Loop. 87

8.5. Управляющая инструкция Select Case. 77

8.6. Управляющая инструкция For-Each-Next 81

8.6.1. Инструкция For-Each-Next с многомерными массивами. 83

8.6.2. Инструкция For-Each-Next с семействами. 84

9. Инструкция With. 89

10. Встроенные функции VBA.. 91

10.1. Математические функции. 92

10.2. Функции проверки типов. 92

10.3. Функции преобразования форматов. 92

10.4. Функции обработки строк. 93

10.5. Функции времени и даты.. 95

11. Пользовательский интерфейс. 95

11.1. Форма (UserForm) 99

11.1.1. Вставка формы: 99

11.1.2. Основные свойства и методы формы: 101

11.1.3. События формы.. 102

11.2. Элементы управления. 102

11.2.1. Некоторые общие свойства элементов управления. 103

11.2.2. Соглашения об именах. 105

11.2.3. Некоторые общие методы элементов управления. 105

11.2.4. Общие события элементов управления. 106

11.3. Кнопка (СоmmandButton) 107

11.4. Поле (TextBox) 108

11.5. Надпись (Label) 109

11.5.1. Пример создания формы для ввода пароля. 109

11.6. Список (ListBox) 112

11.6.1. Основные свойства элемента управления ListBox. 113

11.6.2. Методы ListBox. 115

11.6.3. Заполнение списка. 115

11.7. Поле со списком (ComboBox) 120

11.8. Флажок (CheckBox) 121

11.9. Выключатель (ToggleButton) 126

11.10. Переключатель (OptionButton) 126

11.11. Элементы управления: полоса прокрутки (ScrollBar) и счетчик (SpinButton) 127

11.11.1. Пример создания формы для нахождения суммы чисел. 128

11.12. Создание нестандартных меню и панелей инструментов. 131

11.12.1. Методы объекта CommandBar 132

11.12.2. Свойства объекта CommandBar 133

11.12.3. Семейство CommandBarControls и объект CommandBarControl 134

11.12.4. Пример создания/удаления панели инструментов. 136

11.12.5. Пример создания/удаления меню.. 138

12. События объектов Workbook и Worksheet 139

12.1. События объекта Workbook. 139

12.2. События объекта Worksheet 141


Введение

Одним из эффективных средств создания информационных систем или автоматизированных рабочих мест является программа MicroSoft Excel, которая предоставляет разработчику возможность использовать одновременно преимущества визуального программирования и электронной таблицы. Языком визуального программирования в MicroSoft Excel, равно как в других приложениях MicroSoft Office, является Visual Basic for Applications (VBA). VBA можно отнести к языкам объектно-ориентированного программирования (ООП), в которых данные и код объединяются в нечто единое целое, называемое объект.

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

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


Основным понятием в Excel является объект. Говоря коротко,объект - это нечто, чем можно управлять и что можно программировать. Модель объектов Excel содержит более 100 собственных элементов и несколько - общих для всех приложений Office. Диапазон объектов Excel очень широк - от простых прямоугольников или текстовых полей до таких сложных структур, как сводные таблицы и диаграммы.

Каждый из объектов Excel предназначен для выполнения определенного действия, необходимого для анализа данных. Создание приложения заключается в объединении нужных объектов средствами языка программирования VBA.