Коллекция QueryTables, объект QueryTable.

Основное назначение объекта QueryTable — работа с набором значений, возвращаемых из базы данных. Этот объект доступен в Excel и при помощи графического интерфейса через меню Данные -> Импорт внешних данных -> Импортировать данные. При помощи объектов QueryTable можно разместить набор записей, полученных с источника данных, на листе Excel для выполнения с ним различных операций (например, анализа). QueryTable удобно использовать для "односторонней" работы с источником данных, когда данные только скачиваются с источника в Excel, но изменять их с сохранением изменений на источнике не нужно.

Как обычно, для того, чтобы создать объект QueryTable и разместить его на листе, нужно использовать специальную коллекцию QueryTables, которая принадлежит рабочему листу (объекту Worksheet) и доступна через его одноименное свойство. Свойства и методы объекта QueryTables — стандартные, как у большинства рассмотренных нами коллекций. Подробного рассмотрения заслуживает только метод Add(), при помощи которого и создается объект QueryTable (с одновременным добавлением в коллекцию). Этот метод принимает три параметра:

· Connection — источник данных для QueryTable (в виде объекта типа Variant).

· Destination — куда вставлять полученную QueryTable. Передается объект Range, и вставка производится начиная с верхнего левого угла этой ячейки.

· SQL — при помощи этого необязательного параметра можно определить SQL-запрос, который будет выполняться к источнику данных ODBC. Тот же запрос можно определить при помощи одноименного свойства объекта QueryTable.

Непосредственно помещение объекта QueryTable на лист производится при помощи метода QueryTable.Refresh(). Без него объект QueryTable будет создан только в оперативной памяти.

Теперь — о самых важных свойствах и методах объекта QueryTable:

· BackgroundQuery — может ли выполнение запроса производится в фоновом режиме, пока пользователь выполняет в Excel другие действия. По умолчанию true, в false следует переводить только тогда, когда пользователь действиями в Excel может как-то помешать нормальной работе приложения.

· CommandText — текст команды SQL, то есть текст запроса, который передается на источник. Сосуществует совместно с аналогичным свойством SQL (которое оставлено для обратной совместимости) и имеет перед ним приоритет. При передаче QueryTable готового Recordset недоступно.

· CommandType — тип передаваемой в CommandText команды (вся таблица, SQL-запрос, имя куба и т.п.). При работе с готовым Recordset также недоступно.

· Connection — строка подключения, та самая, которую можно передать при вызове метода Add() коллекции QueryTables. Опять-таки при работе с готовым Recordset недоступно.

· Destination — второй параметр, который передавался методу Add(). Возвращает объект Range, представляющий первую (верхнюю левую ячейку) диапазона, занимаемого на листе объектом QueryTable. После создания QueryTable доступен только на чтение.

· EnableEditing — может ли пользователь изменять на графическом экране свойства объекта QueryTable. Если перевести в false (по умолчанию true), то пользователь сможет только обновлять QueryTable.

· EnableRefresh — может ли пользователь обновлять QueryTable, получая заново данные (с источника или Recordset);

· FieldNames — очень полезное свойство. Позволяет отключить вставку полученных с источника названий столбцов в первую строку QueryTable. По умолчанию true (вставлять названия столбцов).

· Name — имя объекта QueryTable. По умолчанию — ExternalData_номер.

· Parameters — возможность получить доступ к коллекции Parameters, набору параметров запроса. Возможности практически такие же, как для работы с параметрами объекта Recordset.

· PreserveColumnInfo и PreserveFormatting — сохранять ли информацию о столбцах (сортировке, фильтрации и т.п.) и форматировании после обновления QueryTable. По умолчанию — все сохранять.

· QueryType — возможность выяснить (свойство доступно только на чтение), что использовалось при создании QueryTable — Recordset, прямой доступ к таблице, SQL-запрос и т.п.

· Recordset — возможность получить ссылку на объект Recordset, который использовался для создания QueryTable или сменить его для объекта QueryTable (изменения вступят в силу только после вызова метода Refresh()).

· Refreshing — это свойство принимает значение true на момент выполнения фонового запроса к источнику. Если выполнение запроса слишком затянулось, его можно прервать при помощи метода CancelRefresh().

· ResultRange — пожалуй, самое важное свойство объекта QueryTable. Как правило, данные из базы данных перекачиваются в Excel для дальнейшей обработки. Это свойство позволяет получить диапазон, который включает в себя все ячейки, вставленные на лист из объекта QueryTable, чтобы потом применить к ним различные функции (обычно по столбцам или по строкам). Чтобы этот метод сработал, обязательно нужно провести вставку данных QueryTable на лист при помощи метода Refresh. После этого можно использовать то, что возвращает это свойство, как обычный диапазон. Самый простой способ продемонстрировать работу эту метода — воспользоваться кодом

У объекта QueryTable есть также два события: BeforeRefresh и AfterRefresh. Они срабатывают соответственно перед началом загрузки данных с источника и после окончания загрузки.

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

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

Части сводной таблицы:

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

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

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

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

Для построения сводных таблиц в Microsoft Excel предусмотрен специальный инструмент - Мастер сводных таблиц. Чтобы воспользоваться этим инструментом, необходимо выбрать команду Pivot Table Report (Сводная таблица) из меню Data (Данные). Причем, если в качестве исходных данных для сводной таблицы нужно использовать не весь список, лучше предварительно выделить требуемый диапазон.

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

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

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

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

25. Диаграммы.Объект_Chart.
Создание диаграмм является одним из ключевых свойств приложения Excel. При этом можно выбрать целое множество типов, подходящих практически ко всем вообразимым данным. Существуют такие общие типы, как гистограммы, линейные графики, круговые диаграммы и более специализированные, такие как диаграммы рассеивания, и биржевые диаграммы. Имеется полное управление многими аспектами отображения диаграмм, такими как цвета фона, текста и линий, заголовки, легенды и оси.

Рабочая книга Excel имеет два способа отображения диаграмм. Первый метод отображает каждую диаграмму на отдельном рабочем листе, называемом листом диаграммы (chart sheet). Лист диаграммы содержит только одну диаграмму и не содержит колонок и -строк данных. Второй метод отображает диаграмму внедренной на стандартном рабочем листе. В обоих случаях вид диаграмм остаетсяодинаковым — отличается только способ их отображения.

Листы диаграмм представляются коллекцией Charts,’ которая содержит объект Chart для каждого листа диаграммы заданной рабочей книги (объекта Workbook) или активной рабочей книги (объект Application). Таким образом, сле дующий фрагмент выведет на печать все листы диаграмм рабочей книги

Добавить новый лист диаграммы в коллекцию можно следующим методом Charts.Add()

Объект Chart

Каждая диаграмма в рабочей книге, будь она внедренной или расположенной на собственном листе, представлена объектом chart. Данные, которые отображает диаграмма, устанавливаются в методе SetSourceData объекта Chart. Его синтаксис:

MyChart.SetSourceData(Source, PlotBy)

Аргумент Source является ссылкой на объект Range, в котором находятся данные для отображения в диаграмме. Этот аргумент является обязательным. Необязательный аргументPlotBy определяет, будут ли данные организованы в серии по строкам (xlRows, по умолчанию) или по столбцам (xlColumns). Данные, выводящиеся на диаграммы, обычно организованы в прямоугольную таблицу, вкоторой левая колонка и верхняя строка содержат метки, идентифицирующие данные. Метод содержит и другие аргументы, но они не являются общими. Еще одной важной составляющей создания диаграммы является задание типа диаграммы. Он устанавливается в свойстве ChartType объекта Chart, его возможными значениями являются константы xlChartType. В приложении Excel

имеется 14 основных типов диаграмм, таких как гистограммы, линейные графики и т.п. каждый их этих типов имеет несколько подтипов. Например, кон станта xlColumnCkastered задает обычную гистограмму, a xlColumnStacked —гистограмму с накоплением и т.п. Вместо перечисления здесь всех констант типов диаграмм, я предлагаю для этого воспользоваться окном Object Explorer.

Не нашли, что искали? Воспользуйтесь поиском: