Тема: Способы обработки данных на рабочем листе.

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

Теоретическая часть.

Существуют 5 методов объекта Range, обеспечивающих мощные средства для работы с данными:

· GoalSeek – реализует надстройку «Подбор параметра». Подбираются входные значения для формулы, написанной в данной ячейке, до тех пор, пока не будет подобрано желаемое значение. С помощью этой надстройки можно определить, например, необходимый для получения заданной прибыли рост производства или объем продаж, обеспечивающий окупаемость. Изменяемая ячейка должна содержать число (не формулу) и должна влиять на результат, который требуется получить. Это влияние необязательно должно быть непосредственным: ячейка может не использоваться в формуле как аргумент.

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

Администрация хочет повысить заработную плату сотруднику Иванову И.И. Какую зарплату нужно назначить данному работнику, чтобы после всех положенных выплат он на руки получил 3500 руб. Чтобы решить задачу, нужно применить метод GoalSeek. У него указываются два аргумента:

o Goal - цель поиска, или результат, который необходимо получить в заданной формуле;

o ChangingCell - изменяемая ячейка, или ссылка на ячейку, задающую входные данные, которые должны меняться для достижения желаемого результата.

Для данного примера запишем следующим образом:

Range(“h7”).GoalSeek Goal:=3500, ChangingCell:=Range(“B7”)

После применения метода данные на рабочем листе сразу изменятся и примут вид:

· Sort – реализует сортировку данных. Сортировка – это упорядочение записей по определенному полю. Для осуществления сортировки, данные должны быть организованы в виде списка, т.к. сортировка основывается на заголовках столбцов. Если в столбце, по которому произвели сортировку много одинаковых элементов, то имеет смыл производить сортировку по второму полю. Тогда будут отсортированы те записи, у которых в первом поле сортировки были одинаковые значения. Одновременно можно сортировать до 3-х полей. Поэтому метод Sort имеет следующие аргументы:

o Key1 – диапазон, соответствующий первому ключу сортировки;

o Order1 – порядок сортировки по первому ключу. Задается константами: xlAscending – сортировка по возрастанию, xlDescending - сортировка по убыванию;

o Key2 - диапазон, соответствующий первому ключу сортировки;

o Order2 - порядок сортировки по первому ключу. Задается константами: xlAscending – сортировка по возрастанию, xlDescending - сортировка по убыванию;

o Key3 - диапазон, соответствующий первому ключу сортировки;

o Order3 - порядок сортировки по первому ключу. Задается константами: xlAscending – сортировка по возрастанию, xlDescending - сортировка по убыванию;

o Header – определяет, является ли первая строка строкой заголовков столбцов. Задается константами: xlYes – первая строка является строкой заголовка и в сортировке не участвует; xlNo – первая строка не является строкой заголовка и в сортировке участвует; xlGuess – Excel сам определяет, является ли первая строка строкой заголовков столбцов.

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

Отсортируем список, хранящийся в файле «Турист_товары.xls» по полю Доход в порядке возрастания. Если обращаемся к конкретному диапазону сортировки, то ключ сортировки указывается ссылкой на соответствующую ячейку заголовка данного столбца:

Range(“b3:h543”).Sort key1:=Range(“g3”), order1:=xlAscending, header:=xlYes

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

Range(“b3”).CurrentRegion.Sort key1:=”Доход”, order1:=xlAscending, header:=xlYes

Отсортируем список, хранящийся в файле «Турист_товары.xls» по полю Категория в порядке возрастания, при совпадении категорий по поля Продукт в порядке возрастания, а при совпадении и Категории и Продукта по полю Доход в порядке убывания.

Range(“b3”).CurrentRegion.Sort Key1:=”Категория”, order1:=xlAscending, Key2:=”Продукт”, order2:=xlAscending, key3:=”Доход”, order3:=xlDescending, header:=xlYes

· SubTotal – позволяет создавать различные виды промежуточных итогов. Промежуточные итоги автоматически вставляются в список данных, основываясь на изменениях в определенных полях данных. Важно, чтобы до вызова метода данные были нужным образом отсортированы. В противном случае данные не будут правильно сгруппированы и промежуточные итоги не могут быть правильно вычислены. Применяя данный метод, задаются: порядковый номер столбца, по которому вычисляются промежуточные итоги; одна из 11-ти функций для вычисления промежуточных итогов; массив целых чисел, указывающих номера столбцов, по которым подводятся итоги. Поэтому метод имеет следующие аргументы:

o groupby – номер столбца или поля, по которому вычисляются промежуточные итоги.

o function – одна из 11 функций для вычисления промежуточных итогов. Функции задаются константами (xlsum, xlmin, xlmax, xlaverage, и т.д.);

o totallist – массив целых чисел, указывающих номера столбцов или полей, по которым производятся вычисления промежуточных итогов;

o replace (true/false) – если значение true, то существующие промежуточные итоги будут заменены. Значение false используется для создания сложных итогов.

Используем для подведения промежуточных итогов список, хранящийся в файле «Турист_товары.xls». Пусть для каждой категории товаров необходимо подвести промежуточные итоги и вывести на экран суммарный доход и прибыль от продажи товаров по каждой категории.

Range("b3").CurrentRegion.Sort Key1:="Категория", order1:=xlAscending, header:=xlYes

Range("b3").CurrentRegion.Subtotal 3, xlSum, Array(6, 7)

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

Range("b3").CurrentRegion.RemoveSubtotal

· AutoFilter – реализует простой способ запроса к данным на рабочем листе. Фильтрация – это возможность отобрать из списка записи по заданному критерию. Остальные записи временно скрываются и не выводятся на экран. Метод AutoFilter имеет следующие аргументы:

o Field – номер поля, по которому осуществляется фильтрация;

o Criteria1 – первый критерий фильтрации;

o Оperator – оператор для объединения двух условий фильтрации. Оператор может принимать одно из двух значений: xlOr (соответствует логической операции ИЛИ, объединяет два условия), xlAnd (соответствует логической операции И, пересечение двух условий);

o Criteria2 – второй критерий фильтрации;

o VisibleDropDoun (True/False) – логическое выражение, указывающее, отобразить или скрыть раскрывающийся список.

Метод AutoFilter, применяемый без параметров, только активизирует авто фильтр. Свойство AutofilterMode рабочего листа позволяет проверить установлен ли на рабочем листе автофильтр.

If Worksheets("Лист1").AutoFilterMode = False Then

Range("b3").AutoFilter

End If

Чтобы убрать раскрывающиеся списки у заголовков всех столбцов, запишем:

Range("b3").AutoFilter field:=Array(1, 2, 3, 4, 5, 6, 7), Visibledropdown:=False

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

Range("b3").AutoFilter 4, "Шлем", xlOr, "Перчатки"

Чтобы снять автофильтр, нужно его еще раз активизировать:

Range("b3").AutoFilter

· Consolidate – реализует консолидацию (объединение данных) из нескольких диапазонов, расположенных на разных листах рабочих книг. Применяя данный метод, задают адреса источников консолидации в аннотации R1C1; функцию для консолидации, которая может быть использована для объединения таблиц; и способ консолидации. Поэтому метод имеет следующие аргументы:

· source – массив текстовых строк, задающих адреса источников консолидации в нотации R1C1;

· function – функция, задающая способ объединения данных. Задается константами (xlSum, xlMax, xlMin, xlAverage,…..)

· topRow (True/False) – если значение True, то консолидация базируется на сходстве заголовков столбцов в исходных данных;

· leftColumn (True/False) – если значение True, то консолидация базируется на сходстве заголовков строк в левых столбцах исходных данных;

· creareLinks (True/False) – если значение True, то консолидирующая таблица связывается с исходными данными.

Пусть требуется на листе «Лист4» начиная с ячейки В3, создать таблицу, которая объединяет данные из 3-х таблиц, находящихся на первых3-х листах данной книги.

With Worksheets(“Лист4”).Range(“B3”)

.Consolidate source:=Array(“Лист1!A2:H20”, “Лист1!A2:H20”, “Лист1!A2:H20”),_ function:=xlSum, topRow:=True, leftColumn:=True, createLinks:=True

.Autoformat format:=xlClassuc2

end with

Дата добавления: 2014-01-04