Термин Объекты Excel (понимаемый в широком смысле, как объектная модель Excel) включает в себя элементы, из которых состоит любая рабочая книга Excel. Это, например, рабочие листы (Worksheets ), строки (Rows ), столбцы (Columns ), диапазоны ячеек (Ranges ) и сама рабочая книга Excel (Workbook ) в том числе. Каждый объект Excel имеет набор свойств, которые являются его неотъемлемой частью.
Например, объект Worksheet (рабочий лист) имеет свойства Name (имя), Protection (защита), Visible (видимость), Scroll Area (область прокрутки) и так далее. Таким образом, если в процессе выполнения макроса требуется скрыть рабочий лист, то достаточно изменить свойство Visible этого листа.
В Excel VBA существует особый тип объектов – коллекция . Как можно догадаться из названия, коллекция ссылается на группу (или коллекцию) объектов Excel. Например, коллекция Rows – это объект, содержащий все строки рабочего листа.
Доступ ко всем основным объектам Excel может быть осуществлён (прямо или косвенно) через объект Workbooks , который является коллекцией всех открытых в данный момент рабочих книг. Каждая рабочая книга содержит объект Sheets – коллекция, которая включает в себя все рабочие листы и листы с диаграммами рабочей книги. Каждый объект Worksheet состоит из коллекции Rows – в неё входят все строки рабочего листа, и коллекции Columns – все столбцы рабочего листа, и так далее.
В следующей таблице перечислены некоторые наиболее часто используемые объекты Excel. Полный перечень объектов Excel VBA можно найти на сайте Microsoft Office Developer (на английском).
| Объект | Описание |
|---|---|
| Application | Приложение Excel. |
| Workbooks | Коллекция всех открытых в данный момент рабочих книг в текущем приложении Excel. Доступ к какой-то конкретной рабочей книге может быть осуществлён через объект Workbooks при помощи числового индекса рабочей книги или её имени, например, Workbooks(1) или Workbooks(“Книга1”) . |
| Workbook | Объект Workbook
– это рабочая книга. Доступ к ней может быть выполнен через коллекцию Workbooks
при помощи числового индекса или имени рабочей книги (см. выше). Для доступа к активной в данный момент рабочей книге можно использовать ActiveWorkbook
.
Из объекта Workbook можно получить доступ к объекту Sheets , который является коллекцией всех листов рабочей книги (рабочие листы и диаграммы), а также к объекту Worksheets , который представляет из себя коллекцию всех рабочих листов книги Excel. |
| Sheets | Объект Sheets – это коллекция всех листов рабочей книги. Это могут быть как рабочие листы, так и диаграммы на отдельном листе. Доступ к отдельному листу из коллекции Sheets можно получить при помощи числового индекса листа или его имени, например, Sheets(1) или Sheets(“Лист1”) . |
| Worksheets | Объект Worksheets – это коллекция всех рабочих листов в рабочей книге (то есть, все листы, кроме диаграмм на отдельном листе). Доступ к отдельному рабочему листу из коллекции Worksheets можно получить при помощи числового индекса рабочего листа или его имени, например, Worksheets(1) или Worksheets(“Лист1”) . |
| Worksheet | Объект Worksheet
– это отдельный рабочий лист книги Excel. Доступ к нему можно получить при помощи числового индекса рабочего листа или его имени (см. выше).
Кроме этого Вы можете использовать ActiveSheet для доступа к активному в данный момент рабочему листу. Из объекта Worksheet можно получить доступ к объектам Rows и Columns , которые являются коллекцией объектов Range , ссылающихся на строки и столбцы рабочего листа. А также можно получить доступ к отдельной ячейке или к любому диапазону смежных ячеек на рабочем листе. |
| Rows | Объект Rows – это коллекция всех строк рабочего листа. Объект Range , состоящий из отдельной строки рабочего листа, может быть доступен по номеру этой строки, например, Rows(1) . |
| Columns | Объект Columns – это коллекция всех столбцов рабочего листа. Объект Range , состоящий из отдельного столбца рабочего листа, может быть доступен по номеру этого столбца, например, Columns(1) . |
| Range | Объект Range
– это любое количество смежных ячеек на рабочем листе. Это может быть одна ячейка или все ячейки листа.
Доступ к диапазону, состоящему из единственной ячейки, может быть осуществлён через объект Worksheet при помощи свойства Cells , например, Worksheet.Cells(1,1) . По-другому ссылку на диапазон можно записать, указав адреса начальной и конечной ячеек. Их можно записать через двоеточие или через запятую. Например, Worksheet.Range(“A1:B10”) или Worksheet.Range(“A1”, “B10”) или Worksheet.Range(Cells(1,1), Cells(10,2)) . Обратите внимание, если в адресе Range вторая ячейка не указана (например, Worksheet.Range(“A1”) или Worksheet.Range(Cells(1,1)) , то будет выбран диапазон, состоящий из единственной ячейки. |
Приведённая выше таблица показывает, как выполняется доступ к объектам Excel через родительские объекты. Например, ссылку на диапазон ячеек можно записать вот так:
Workbooks("Книга1").Worksheets("Лист1").Range("A1:B10")
Присваивание объекта переменной
В Excel VBA объект может быть присвоен переменной при помощи ключевого слова Set :
Dim DataWb As Workbook Set DataWb = Workbooks("Книга1.xlsx")
Активный объект
В любой момент времени в Excel есть активный объект Workbook – это рабочая книга, открытая в этот момент. Точно так же существует активный объект Worksheet , активный объект Range и так далее.
Сослаться на активный объект Workbook или Sheet в коде VBA можно как на ActiveWorkbook или ActiveSheet , а на активный объект Range – как на Selection .
Если в коде VBA записана ссылка на рабочий лист, без указания к какой именно рабочей книге он относится, то Excel по умолчанию обращается к активной рабочей книге. Точно так же, если сослаться на диапазон, не указывая определённую рабочую книгу или лист, то Excel по умолчанию обратится к активному рабочему листу в активной рабочей книге.
Таким образом, чтобы сослаться на диапазон A1:B10 на активном рабочем листе активной книги, можно записать просто:
Range("A1:B10")
Смена активного объекта
Если в процессе выполнения программы требуется сделать активной другую рабочую книгу, другой рабочий лист, диапазон и так далее, то для этого нужно использовать методы Activate или Select вот таким образом:
Sub ActivateAndSelect() Workbooks("Книга2").Activate Worksheets("Лист2").Select Worksheets("Лист2").Range("A1:B10").Select Worksheets("Лист2").Range("A5").Activate End Sub
Методы объектов, в том числе использованные только что методы Activate или Select , далее будут рассмотрены более подробно.
Свойства объектов
Каждый объект VBA имеет заданные для него свойства. Например, объект Workbook имеет свойства Name (имя), RevisionNumber (количество сохранений), Sheets (листы) и множество других. Чтобы получить доступ к свойствам объекта, нужно записать имя объекта, затем точку и далее имя свойства. Например, имя активной рабочей книги может быть доступно вот так: ActiveWorkbook.Name . Таким образом, чтобы присвоить переменной wbName имя активной рабочей книги, можно использовать вот такой код:
Dim wbName As String wbName = ActiveWorkbook.Name
Ранее мы показали, как объект Workbook может быть использован для доступа к объекту Worksheet при помощи такой команды:
Workbooks("Книга1").Worksheets("Лист1")
Это возможно потому, что коллекция Worksheets является свойством объекта Workbook .
Некоторые свойства объекта доступны только для чтения, то есть их значения пользователь изменять не может. В то же время существуют свойства, которым можно присваивать различные значения. Например, чтобы изменить название активного листа на “Мой рабочий лист “, достаточно присвоить это имя свойству Name активного листа, вот так:
ActiveSheet.Name = "Мой рабочий лист"
Методы объектов
Объекты VBA имеют методы для выполнения определённых действий. Методы объекта – это процедуры, привязанные к объектам определённого типа. Например, объект Workbook имеет методы Activate , Close , Save и ещё множество других.
Для того, чтобы вызвать метод объекта, нужно записать имя объекта, точку и имя метода. Например, чтобы сохранить активную рабочую книгу, можно использовать вот такую строку кода:
ActiveWorkbook.Save
Как и другие процедуры, методы могут иметь аргументы, которые передаются методу при его вызове. Например, метод Close объекта Workbook имеет три необязательных аргумента, которые определяют, должна ли быть сохранена рабочая книга перед закрытием и тому подобное.
Чтобы передать методу аргументы, необходимо записать после вызова метода значения этих аргументов через запятую. Например, если нужно сохранить активную рабочую книгу как файл .csv с именем “Книга2”, то нужно вызвать метод SaveAs объекта Workbook и передать аргументу Filename значение Книга2 , а аргументу FileFormat – значение xlCSV :
ActiveWorkbook.SaveAs "Книга2", xlCSV
Чтобы сделать код более читаемым, при вызове метода можно использовать именованные аргументы. В этом случае сначала записывают имя аргумента, затем оператор присваивания “:= ” и после него указывают значение. Таким образом, приведённый выше пример вызова метода SaveAs объекта Workbook можно записать по-другому:
ActiveWorkbook.SaveAs Filename:="Книга2", :=xlCSV
В окне Object Browser редактора Visual Basic показан список всех доступных объектов, их свойств и методов. Чтобы открыть этот список, запустите редактор Visual Basic и нажмите F2 .
Рассмотрим несколько примеров
Пример 1
Этот отрывок кода VBA может служить иллюстрацией использования цикла For Each . В данном случае мы обратимся к нему, чтобы продемонстрировать ссылки на объект Worksheets (который по умолчанию берётся из активной рабочей книги) и ссылки на каждый объект Worksheet отдельно. Обратите внимание, что для вывода на экран имени каждого рабочего листа использовано свойство Name объекта Worksheet .
"Пролистываем поочерёдно все рабочие листы активной рабочей книги "и выводим окно сообщения с именем каждого рабочего листа Dim wSheet As Worksheet For Each wSheet in Worksheets MsgBox "Найден рабочий лист: " & wSheet.Name Next wSheet
Пример 2
В этом примере кода VBA показано, как можно получать доступ к рабочим листам и диапазонам ячеек из других рабочих книг. Кроме этого, Вы убедитесь, что если не указана ссылка на какой-то определённый объект, то по умолчанию используются активные объекты Excel. Данный пример демонстрирует использование ключевого слова Set для присваивания объекта переменной.
В коде, приведённом ниже, для объекта Range вызывается метод PasteSpecial . Этот метод передаёт аргументу Paste значение xlPasteValues .
"Копируем диапазон ячеек из листа "Лист1" другой рабочей книги (с именем Data.xlsx) "и вставляем только значения на лист "Результаты" текущей рабочей книги (с именем CurrWb.xlsm) Dim dataWb As Workbook Set dataWb = Workbooks.Open("C:\Data") "Обратите внимание, что DataWb – это активная рабочая книга. "Следовательно, следующее действие выполняется с объектом Sheets в DataWb. Sheets("Лист1").Range("A1:B10").Copy "Вставляем значения, скопированные из диапазона ячеек, на рабочий лист "Результаты" "текущей рабочей книги. Обратите внимание, что рабочая книга CurrWb.xlsm не является "активной, поэтому должна быть указана в ссылке. Workbooks("CurrWb").Sheets("Результаты").Range("A1").PasteSpecial Paste:=xlPasteValues
Пример 3
Следующий отрывок кода VBA показывает пример объекта (коллекции) Columns и демонстрирует, как доступ к нему осуществляется из объекта Worksheet . Кроме этого, Вы увидите, что, ссылаясь на ячейку или диапазон ячеек на активном рабочем листе, можно не указывать этот лист в ссылке. Вновь встречаем ключевое слово Set , при помощи которого объект Range присваивается переменной Col .
Данный код VBA показывает также пример доступа к свойству Value объекта Range и изменение его значения.
"С помощью цикла просматриваем значения в столбце A на листе "Лист2", "выполняем с каждым из них арифметические операции и записываем результат "в столбец A активного рабочего листа (Лист1) Dim i As Integer Dim Col As Range Dim dVal As Double "Присваиваем переменной Col столбец A рабочего листа "Лист2" Set Col = Sheets("Лист2").Columns("A") i = 1 "Просматриваем последовательно все ячейки столбца Col до тех пор "пока не встретится пустая ячейка Do Until IsEmpty(Col.Cells(i)) "Выполняем арифметические операции со значением текущей ячейки dVal = Col.Cells(i).Value * 3 - 1 "Следующая команда записывает результат в столбец A "активного листа. Нет необходимости указывать в ссылке имя листа, "так как это активный лист рабочей книги. Cells(i, 1).Value = dVal i = i + 1 Loop
Объектная модель MS Excel и других приложений Microsoft Office устроена следующим образом. На нулевом уровне иерархии существует центральный объект, в который встроены другие объекты, составляющие первый уровень иерархии. В каждый из объектов первого и последующих уровней могут быть встроены объекты следующего уровня. Встраивание реализуется с помощью свойств объектов. Свойства могут быть терминальными, не являющимися объектами, и свойствами, которые возвращают объекты при их вызове.
Объектов в этой иерархии очень много. Учитывая, что у каждого объекта, в свою очередь, большое количество свойств, методов и событий, изучать их достаточно сложно. Поэтому мы рассмотрим только основные объекты. На первых порах достаточно использовать Application (Приложение), Workbook (Рабочую книг}"), Worksheet (Рабочий лист), Range (Диапазон ячеек). Много в этой иерархии и коллекций: Workbooks – это коллекция рабочих книг, Worksheets – коллекция рабочих листов, Sheets – коллекция всех листов (рабочих листов и листов диаграмм), Charts – коллекция диаграмм.
Полное описание свойств, методов и событий каждого объекта можно найти в справочной системе. Для этого в редакторе Visual Basic откройте окно справки, в строке поиска наберите "Application Object Members" (для объекта Application) и перейдите но найденной гиперссылке. Для других объектов вместо Application используйте нужное имя класса.
Объект Application
На вершине объектной модели находится объект Application. Он представляет само приложение Excel. Через этот объект осуществляется доступ ко всем остальным объектам. Однако обычно свойства и методы этого объекта используют, не указывая слово Application. Например, вместо Application. ActiveCell.Value можно написать просто ActiveCell.Value. Основные свойства объекта приведены в табл. 12.21.
Таблица 12.21
Основные свойства объекта Application
|
Свойство |
Описание |
|
Active Workbook, |
Активные объекты (рабочая книга, окно, лист, диаграмма, ячейка) |
|
WorkSheetFunction |
Представляет собой объект – контейнер, в котором находятся функции Excel. "Гак, например, мы можем обратиться в программе на VBA к функции Excel ФАКТР, вычисляющей факториал: WorksheetFunction.Fact(5). Имена функций в русскоязычной версии Excel не совпадают со свойствами объекта Worksheet Function. Чтобы узнать имя нужного свойства (имя функции в англоязычной версии), запишите макрос, использующий эту функцию на рабочем листе, и посмотрите имя функции в тексте макроса |
|
Коллекция всех рабочих книг |
|
|
Выделенный объект в активном окне. Тип объекта зависит от текущего выделения. Возвращается Nothing, если в активном окне нет выделенного объекта |
Метод Quit объекта Application завершает работу Excel. Например, следующий код сохраняет все открытые книги Excel и завершает работу приложения:
For Each wb In Application.Workbooks
Application.Quit
Объект Workbook и коллекция Workbooks
Каждая рабочая книга представляется объектом Workbook, а их коллекция – объектом Workbooks. Методы коллекции Workbooks приведены в табл. 12.22.
Таблица 12.22
Методы коллекции Workbooks
Указанные методы имеют параметры. Подробнее о параметрах можно прочитать в справочной системе. Проще всего это сделать, вызвав контекстную справку: установите курсор в тексте программы на имени интересующего свойства или метода и нажмите клавишу F1 .
Основные свойства и методы объекта Workbook приведены в табл. 12.23 и 12.24.
Таблица 12.23
Основные свойства объекта Workbook
Таблица 12.24
Основные методы объекта Workbook
В большинстве примеров вводной части раздела мы рассматривали возможности VBA без связи с информацией, находящейся на рабочем листе. Лишь несколько ситуаций продемонстрировали синтаксические конструкции, которые позволяли извлекать и записывать данные в ячейки листов Microsoft Excel. В этой части раздела мы детально рассмотрим объекты, которые позволяют работать с информацией, содержащейся в рабочих книгах Microsoft Excel. Приводимые здесь примеры, являются фундаментом для более сложных разработок, рассматриваемых в последующих статьях.
Файл рабочей книги Excel представлен в объекте Workbook, который имеет большое количество свойств и методов. Справочная информация по ним присутствует как в электронной справке по VBA, так и в . Мы не будем углубляться в чисто справочную информацию и во вводной части рассмотрим только те сведения, с которыми далее встретимся в приведенных примерах.
Так, свойство Worksheets объекта Workbook представляет семейство всех рабочих листов книги. И для обращения к конкретному листу книги с помощью этого свойства следует просто указать в качестве параметра номер листа, что выглядит так: Worksheets(номер листа) . В качестве номера листа выступает просто его порядковый номер в книге Microsoft Excel. Другой вариант заключается в указании в качестве параметра названия листа: Worksheets ("Название листа") . Одним из наиболее часто программируемых событий, связанных с книгой в целом, является событие Open , которое происходит при открытии рабочей книги. Так, если мы хотим, чтобы при открытии книги выполнялись определенные действия, следует расположить необходимый программный фрагмент внутри процедуры Workbook_Open . Программирование этого события будет рассматриваться в части приводимых далее примеров.
Следующим объектом в порядке иерархии после Workbook является объект Worksheet , представляющий рабочий лист. Из многообразия методов этого объекта широко используется Activate , который существует и для семейства листов Worksheets, о котором мы уже сказали выше. Например, если при работе на первом листе книги Microsoft Excel требуется активизировать второй лист, то синтаксис программной строки в процедуре (выполняться она может, например, по щелчку на кнопке) должен выглядеть следующим образом: Worksheets(2).Activate . Если же требуется активизировать лист, называемый Информация о фирмах , то следует написать в процедуре такую конструкцию: Worksheets("Информация о фирмах").Activate .
Пользователи Microsoft Excel знают, что это приложение предлагает сервис, связанный с защитой рабочих книг и составляющих их листов. Так, метод Protect (семейства Worksheets) обеспечивает защиту рабочего листа от внесения в него изменений. Для программной установки защиты с паролем (пароль указывается в параметре Password этого метода) листа с названием Сотрудники можно поступить следующим образом:
| 1 2 | Worksheets("Сотрудники" ).Protect Password:="zv2345" , _ DrawingObjects:=True , Contents:=True , Scenarios:=True |
Worksheets("Сотрудники").Protect Password:="zv2345", _ DrawingObjects:=True, Contents:=True, Scenarios:=True
Существует и соответствующий метод Unprotect , позволяющий снять защиту с листа. Для только что установленной защиты метод ее снятия: Worksheets("Сотрудники").Unprotect Password:="zv2345" . Любая практическая работа в Microsoft Excel, так или иначе, касается информации в ячейках. Для работы с ячейками в VBA существует объект Range (в переводе - диапазон ячеек). Использование этого объекта требует задания параметра диапазона ячеек, которые нас интересуют. Это может быть одна ячейка или группа ячеек. Так, если мы напишем Worksheets(1).Range ("С5").Value = 7 , то это означает, что в ячейку С5 первого листа мы программно записываем число 7. Здесь используется основное свойство объекта Range - Value . Буквально оно означает значение или содержимое ячейки (группы ячеек). В следующей конструкции в совокупность ячеек программно вводится одинаковый набор букв АБСДЕ: Worksheets(1).Range("A1:А3").Value = "АБСДЕ" .
Другой способ работы с ячейками реализуется с помощью объекта Cells , и синтаксис его использования выглядит следующим образом: Cells (номер строки, номер столбца) . Фактически с точки зрения их использования рассматриваемые объекты похожи. Например, получить в переменной Z значение ячейки D5 можно двумя разными способами: Z = Range("D5").Value или Z = Cells(5,4).Value . В качестве примера программной конструкции на данную тему можно привести следующее присвоение:
| 1 2 | Worksheets(2).Range("C5" ).Value = _ Worksheets(3).Cells(5, 1).Value. |
Worksheets(2).Range("C5").Value = _ Worksheets(3).Cells(5, 1).Value.
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
Для облегчения ввода данных в таблицу в Excel можно воспользоваться специальными формами, которые помогут ускорить процесс заполнения табличного диапазона информацией. В Экселе имеется встроенный инструмент позволяющий производить заполнение подобным методом. Также пользователь может создать собственный вариант формы, которая будет максимально адаптирована под его потребности, применив для этого макрос. Давайте рассмотрим различные варианты использования этих полезных инструментов заполнения в Excel.
Форма заполнения представляет собой объект с полями, наименования которых соответствуют названиям колонок столбцов заполняемой таблицы. В эти поля нужно вводить данные и они тут же будут добавляться новой строкой в табличный диапазон. Форма может выступать как в виде отдельного встроенного инструмента Excel, так и располагаться непосредственно на листе в виде его диапазона, если она создана самим пользователем.
Теперь давайте рассмотрим, как пользоваться этими двумя видами инструментов.
Способ 1: встроенный объект для ввода данных Excel
Прежде всего, давайте узнаем, как применять встроенную форму для ввода данных Excel.
- Нужно отметить, что по умолчанию значок, который её запускает, скрыт и его нужно активировать. Для этого переходим во вкладку «Файл» , а затем щелкаем по пункту «Параметры» .
- В открывшемся окне параметров Эксель перемещаемся в раздел «Панель быстрого доступа»
. Большую часть окна занимает обширная область настроек. В левой её части находятся инструменты, которые могут быть добавлены на панель быстрого доступа, а в правой – уже присутствующие.
В поле «Выбрать команды из» устанавливаем значение «Команды не на ленте» . Далее из списка команд, расположенного в алфавитном порядке, находим и выделяем позицию «Форма…» . Затем жмем на кнопку «Добавить» .
- После этого нужный нам инструмент отобразится в правой части окна. Жмем на кнопку «OK» .
- Теперь данный инструмент располагается в окне Excel на панели быстрого доступа, и мы им можем воспользоваться. Он будет присутствовать при открытии любой книги данным экземпляром Excel.
- Теперь, чтобы инструмент понял, что именно ему нужно заполнять, следует оформить шапку таблицы и записать любое значение в ней. Пусть табличный массив у нас будет состоять из четырех столбцов, которые имеют названия «Наименование товара» , «Количество» , «Цена» и «Сумма» . Вводим данные названия в произвольный горизонтальный диапазон листа.
- Также, чтобы программа поняла, с каким именно диапазонам ей нужно будет работать, следует ввести любое значение в первую строку табличного массива.
- После этого выделяем любую ячейку заготовки таблицы и щелкаем на панели быстрого доступа по значку «Форма…» , который мы ранее активировали.
- Итак, открывается окно указанного инструмента. Как видим, данный объект имеет поля, которые соответствуют названиям столбцов нашего табличного массива. При этом первое поле уже заполнено значением, так как мы его ввели вручную на листе.
- Вводим значения, которые считаем нужными и в остальные поля, после чего жмем на кнопку «Добавить» .
- После этого, как видим, в первую строку таблицы были автоматически перенесены введенные значения, а в форме произошел переход к следующему блоку полей, который соответствуют второй строке табличного массива.
- Заполняем окно инструмента теми значениями, которые хотим видеть во второй строке табличной области, и снова щелкаем по кнопке «Добавить» .
- Как видим, значения второй строчки тоже были добавлены, причем нам даже не пришлось переставлять курсор в самой таблице.
- Таким образом, заполняем табличный массив всеми значениями, которые хотим в неё ввести.
- Кроме того, при желании, можно производить навигацию по ранее введенным значениям с помощью кнопок «Назад» и «Далее» или вертикальной полосы прокрутки.
- При необходимости можно откорректировать любое значение в табличном массиве, изменив его в форме. Чтобы изменения отобразились на листе, после внесения их в соответствующий блок инструмента, жмем на кнопку «Добавить» .
- Как видим, изменение сразу произошло и в табличной области.
- Если нам нужно удалить, какую-то строчку, то через кнопки навигации или полосу прокрутки переходим к соответствующему ей блоку полей в форме. После этого щелкаем по кнопке «Удалить» в окошке инструмента.
- Открывается диалоговое окно предупреждения, в котором сообщается, что строка будет удалена. Если вы уверены в своих действиях, то жмите на кнопку «OK» .
- Как видим, строчка была извлечена из табличного диапазона. После того, как заполнение и редактирование закончено, можно выходить из окна инструмента, нажав на кнопку «Закрыть» .
- После этого для предания табличному массиву более наглядного визуального вида можно произвести форматирование.
Способ 2: создание пользовательской формы
Кроме того, с помощью макроса и ряда других инструментов существует возможность создать собственную пользовательскую форму для заполнения табличной области. Она будет создаваться прямо на листе, и представлять собой её диапазон. С помощью данного инструмента пользователь сам сможет реализовать те возможности, которые считает нужными. По функционалу он практически ни в чем не будет уступать встроенному аналогу Excel, а кое в чем, возможно, превосходить его. Единственный недостаток состоит в том, что для каждого табличного массива придется составлять отдельную форму, а не применять один и тот же шаблон, как это возможно при использовании стандартного варианта.
- Как и в предыдущем способе, прежде всего, нужно составить шапку будущей таблицы на листе. Она будет состоять из пяти ячеек с именами: «№ п/п» , «Наименование товара» , «Количество» , «Цена» , «Сумма» .
- Далее нужно из нашего табличного массива сделать так называемую «умную» таблицу, с возможностью автоматического добавления строчек при заполнении соседних диапазонов или ячеек данными. Для этого выделяем шапку и, находясь во вкладке «Главная» , жмем на кнопку «Форматировать как таблицу» в блоке инструментов «Стили» . После этого открывается список доступных вариантов стилей. На функционал выбор одного из них никак не повлияет, поэтому выбираем просто тот вариант, который считаем более подходящим.
- Затем открывается небольшое окошко форматирования таблицы. В нем указан диапазон, который мы ранее выделили, то есть, диапазон шапки. Как правило, в данном поле заполнено все верно. Но нам следует установить галочку около параметра «Таблица с заголовками» . После этого жмем на кнопку «OK» .
- Итак, наш диапазон отформатирован, как «умная» таблица, свидетельством чему является даже изменение визуального отображения. Как видим, помимо прочего, около каждого названия заголовка столбцов появились значки фильтрации. Их следует отключить. Для этого выделяем любую ячейку «умной» таблицы и переходим во вкладку «Данные»
. Там на ленте в блоке инструментов «Сортировка и фильтр»
щелкаем по значку «Фильтр»
.
Существует ещё один вариант отключения фильтра. При этом не нужно даже будет переходить на другую вкладку, оставаясь во вкладке «Главная» . После выделения ячейки табличной области на ленте в блоке настроек «Редактирование» щелкаем по значку «Сортировка и фильтр» . В появившемся списке выбираем позицию «Фильтр» .
- Как видим, после этого действия значки фильтрации исчезли из шапки таблицы, как это и требовалось.
- Затем нам следует создать саму форму ввода данных. Она тоже будет представлять собой своего рода табличный массив, состоящий из двух столбцов. Наименования строк данного объекта будут соответствовать именам столбцов основной таблицы. Исключение составляют столбцы «№ п/п»
и «Сумма»
. Они будут отсутствовать. Нумерация первого из них будет происходить при помощи макроса, а расчет значений во втором будет производиться путем применения формулы умножения количества на цену.
Второй столбец объекта ввода данных оставим пока что пустым. Непосредственно в него позже будут вводиться значения для заполнения строк основного табличного диапазона.
- После этого создаем ещё одну небольшую таблицу. Она будет состоять из одного столбца и в ней разместится список товаров, которые мы будем выводить во вторую колонку основной таблицы. Для наглядности ячейку с заголовком данного перечня («Список товаров» ) можно залить цветом.
- Затем выделяем первую пустую ячейку объекта ввода значений. Переходим во вкладку «Данные» . Щелкаем по значку «Проверка данных» , который размещен на ленте в блоке инструментов «Работа с данными» .
- Запускается окно проверки вводимых данных. Кликаем по полю «Тип данных» , в котором по умолчанию установлен параметр «Любое значение» .
- Из раскрывшихся вариантов выбираем позицию «Список» .
- Как видим, после этого окно проверки вводимых значений несколько изменило свою конфигурацию. Появилось дополнительное поле «Источник» . Щелкаем по пиктограмме справа от него левой клавишей мыши.
- Затем окно проверки вводимых значений сворачивается. Выделяем курсором с зажатой левой клавишей мыши перечень данных, которые размещены на листе в дополнительной табличной области «Список товаров» . После этого опять жмем на пиктограмму справа от поля, в котором появился адрес выделенного диапазона.
- Происходит возврат к окошку проверки вводимых значений. Как видим, координаты выделенного диапазона в нем уже отображены в поле «Источник» . Кликаем по кнопке «OK» внизу окна.
- Теперь справа от выделенной пустой ячейки объекта ввода данных появилась пиктограмма в виде треугольника. При клике на неё открывается выпадающий список, состоящий из названий, которые подтягиваются из табличного массива «Список товаров» . Произвольные данные в указанную ячейку теперь внести невозможно, а только можно выбрать из представленного списка нужную позицию. Выбираем пункт в выпадающем списке.
- Как видим, выбранная позиция тут же отобразилась в поле «Наименование товара» .
- Далее нам нужно будет присвоить имена тем трем ячейкам формы ввода, куда мы будем вводить данные. Выделяем первую ячейку, где уже установлено в нашем случае наименование «Картофель» . Далее переходим в поле наименования диапазонов. Оно расположено в левой части окна Excel на том же уровне, что и строка формул. Вводим туда произвольное название. Это может быть любое наименование на латинице, в котором нет пробелов, но лучше все-таки использовать названия близкие к решаемым данным элементом задачам. Поэтому первую ячейку, в которой содержится название товара, назовем «Name» . Пишем данное наименование в поле и жмем на клавишу Enter на клавиатуре.
- Точно таким же образом присваиваем ячейке, в которую будем вводить количество товара, имя «Volum» .
- А ячейке с ценой – «Price» .
- После этого точно таким же образом даем название всему диапазону из вышеуказанных трех ячеек. Прежде всего, выделим, а потом дадим ему наименование в специальном поле. Пусть это будет имя «Diapason» .
- После последнего действия обязательно сохраняем документ, чтобы названия, которые мы присвоили, смог воспринимать макрос, созданный нами в дальнейшем. Для сохранения переходим во вкладку «Файл» и кликаем по пункту «Сохранить как…» .
- В открывшемся окне сохранения в поле «Тип файлов» выбираем значение «Книга Excel с поддержкой макросов (.xlsm)» . Далее жмем на кнопку «Сохранить» .
- Затем вам следует в своей версии Excel и включить вкладку «Разработчик» , если вы это до сих пор не сделали. Дело в том, что обе эти функции по умолчанию в программе отключены, и их активацию нужно выполнять принудительно в окне параметров Excel.
- После того, как вы сделали это, переходим во вкладку «Разработчик» . Кликаем по большому значку «Visual Basic» , который расположен на ленте в блоке инструментов «Код» .
- Последнее действие приводит к тому, что запускается редактор макросов VBA. В области «Project» , которая расположена в верхней левой части окна, выделяем имя того листа, где располагаются наши таблицы. В данном случае это «Лист 1» .
- После этого переходим к левой нижней области окна под названием «Properties»
. Тут расположены настройки выделенного листа. В поле «(Name)»
следует заменить кириллическое наименование («Лист1»
) на название, написанное на латинице. Название можно дать любое, которое вам будет удобнее, главное, чтобы в нем были исключительно символы латиницы или цифры и отсутствовали другие знаки или пробелы. Именно с этим именем будет работать макрос. Пусть в нашем случае данным названием будет «Producty»
, хотя вы можете выбрать и любое другое, соответствующее условиям, которые были описаны выше.
В поле «Name» тоже можно заменить название на более удобное. Но это не обязательно. При этом допускается использование пробелов, кириллицы и любых других знаков. В отличие от предыдущего параметра, который задает наименование листа для программы, данный параметр присваивает название листу, видимое пользователю на панели ярлыков.
Как видим, после этого автоматически изменится и наименование Листа 1 в области «Project» , на то, которое мы только что задали в настройках.
- Затем переходим в центральную область окна. Именно тут нам нужно будет записать сам код макроса. Если поле редактора кода белого цвета в указанной области не отображается, как в нашем случае, то жмем на функциональную клавишу F7 и оно появится.
- Теперь для конкретно нашего примера нужно записать в поле следующий код:
Sub DataEntryForm()
Dim nextRow As Long
nextRow = Producty.Cells(Producty.Rows.Count, 2).End(xlUp).Offset(1, 0).Row
With Producty
If .Range("A2").Value = "" And .Range("B2").Value = "" Then
nextRow = nextRow - 1
End If
Producty.Range("Name").Copy
.Range("A2").Formula = "=IF(ISBLANK(B2), """", COUNTA($B$2:B2))"
If nextRow > 2 Then
Range("A2").Select
End If
.Range("Diapason").ClearContents
End With
End SubНо этот код не универсальный, то есть, он в неизменном виде подходит только для нашего случая. Если вы хотите его приспособить под свои потребности, то его следует соответственно модифицировать. Чтобы вы смогли сделать это самостоятельно, давайте разберем, из чего данный код состоит, что в нем следует заменить, а что менять не нужно.
Итак, первая строка:
Sub DataEntryForm()
«DataEntryForm» — это название самого макроса. Вы можете оставить его как есть, а можете заменить на любое другое, которое соответствует общим правилам создания наименований макросов (отсутствие пробелов, использование только букв латинского алфавита и т.д.). Изменение наименования ни на что не повлияет.
Везде, где встречается в коде слово «Producty» вы должны его заменить на то наименование, которое ранее присвоили для своего листа в поле «(Name)» области «Properties» редактора макросов. Естественно, это нужно делать только в том случае, если вы назвали лист по-другому.
Теперь рассмотрим такую строку:
nextRow = Producty.Cells(Producty.Rows.Count, 2).End(xlUp).Offset(1, 0).Row
Цифра «2» в данной строчке означает второй столбец листа. Именно в этом столбце находится колонка «Наименование товара» . По ней мы будем считать количество рядов. Поэтому, если в вашем случае аналогичный столбец имеет другой порядок по счету, то нужно ввести соответствующее число. Значение «End(xlUp).Offset(1, 0).Row» в любом случае оставляем без изменений.
If .Range("A2").Value = "" And .Range("B2").Value = "" Then
«A2» — это координаты первой ячейки, в которой будет выводиться нумерация строк. «B2» — это координаты первой ячейки, по которой будет производиться вывод данных («Наименование товара» ). Если они у вас отличаются, то введите вместо этих координат свои данные.
Переходим к строке
Producty.Range("Name").Copy
В ней параметр «Name» означат имя, которое мы присвоили полю «Наименование товара» в форме ввода.
В строках
Cells(nextRow, 2).PasteSpecial Paste:=xlPasteValues
.Cells(nextRow, 3).Value = Producty.Range("Volum").Value
.Cells(nextRow, 4).Value = Producty.Range("Price").Value
.Cells(nextRow, 5).Value = Producty.Range("Volum").Value * Producty.Range("Price").Valueнаименования «Volum» и «Price» означают названия, которые мы присвоили полям «Количество» и «Цена» в той же форме ввода.
В этих же строках, которые мы указали выше, цифры «2» , «3» , «4» , «5» означают номера столбцов на листе Excel, соответствующих колонкам «Наименование товара» , «Количество» , «Цена» и «Сумма» . Поэтому, если в вашем случае таблица сдвинута, то нужно указать соответствующие номера столбцов. Если столбцов больше, то по аналогии нужно добавить её строки в код, если меньше – то убрать лишние.
В строке производится умножение количества товара на его цену:
Cells(nextRow, 5).Value = Producty.Range("Volum").Value * Producty.Range("Price").Value
Результат, как видим из синтаксиса записи, будет выводиться в пятый столбец листа Excel.
В этом выражении выполняется автоматическая нумерация строк:
If nextRow > 2 Then
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A" & nextRow)
Range("A2:A" & nextRow).Select
End IfВсе значения «A2» означают адрес первой ячейки, где будет производиться нумерация, а координаты «A» — адрес всего столбца с нумерацией. Проверьте, где именно будет выводиться нумерация в вашей таблице и измените данные координаты в коде, если это необходимо.
В строке производится очистка диапазона формы ввода данных после того, как информация из неё была перенесена в таблицу:
Range("Diapason").ClearContents
Не трудно догадаться, что («Diapason» ) означает наименование того диапазона, который мы ранее присвоили полям для ввода данных. Если вы дали им другое наименование, то в этой строке должно быть вставлено именно оно.
Дальнейшая часть кода универсальна и во всех случаях будет вноситься без изменений.
После того, как вы записали код макроса в окно редактора, следует нажать на значок сохранения в виде дискеты в левой части окна. Затем можно его закрывать, щелкнув по стандартной кнопке закрытия окон в правом верхнем углу.
- После этого возвращаемся на лист Excel. Теперь нам следует разместить кнопку, которая будет активировать созданный макрос. Для этого переходим во вкладку «Разработчик» . В блоке настроек «Элементы управления» на ленте кликаем по кнопке «Вставить» . Открывается перечень инструментов. В группе инструментов «Элементы управления формы» выбираем самый первый – «Кнопка» .
- Затем с зажатой левой клавишей мыши обводим курсором область, где хотим разместить кнопку запуска макроса, который будет производить перенос данных из формы в таблицу.
- После того, как область обведена, отпускаем клавишу мыши. Затем автоматически запускается окно назначения макроса объекту. Если в вашей книге применяется несколько макросов, то выбираем из списка название того, который мы выше создавали. У нас он называется «DataEntryForm» . Но в данном случае макрос один, поэтому просто выбираем его и жмем на кнопку «OK» внизу окна.
- После этого можно переименовать кнопку, как вы захотите, просто выделив её текущее название.
В нашем случае, например, логично будет дать ей имя «Добавить» . Переименовываем и кликаем мышкой по любой свободной ячейке листа.
- Итак, наша форма полностью готова. Проверим, как она работает. Вводим в её поля необходимые значения и жмем на кнопку «Добавить» .
- Как видим, значения перемещены в таблицу, строке автоматически присвоен номер, сумма посчитана, поля формы очищены.
- Повторно заполняем форму и жмем на кнопку «Добавить» .
- Как видим, и вторая строка также добавлена в табличный массив. Это означает, что инструмент работает.
