Сортировка

Работа с данными

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

Для операций по вводу, удалению и корректировке данныхак это показано на рис. вая различные критерии поиска.ем. (отдельном) листе. специальностей.

в Excel имеется встроенное средство – форма ввода данных.

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

Данные > Форма.

С помощью появившейся формы можно выполнить все указанные операции.

Для «цивильного» вызова этой формы создадим на листе кнопку с именем «Данные» и для нее создадим макрос следующего содержания:

Private Sub Работа_с_ данными()

Range("C11").Select ‘Переход на ячейку БД

CommandBars.FindControl(ID:=860).Execute ‘Вызов формы ввода данных

ActiveWorkbook.Names("Данные_продаж").Delete

ActiveCell.CurrentRegion.Name = "Данные_продаж"

End Sub

При вводе или удалении данных размеры БД могут изменяться. По этой причине в макрос добавлены еще две команды:

первая – удаляет имеющееся имя БД:

вторая – определяет новый размер БД и присваивает ему только что удаленное имя.

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

Рис. 5.4. Интерфейс реализации операции Сортировка

С помощью предлагаемого интерфейса сортировка выполняется следующим образом:

– из списка «Сортировать по …» выбирается поле сортировки (на рис. 5.4 уже выбрано поле «Дата продажи») и щелчок по кнопке «Сортировать».

Создание со списка полей

– на текущем листе (где–то в стороне, так, чтобы этого потом не было видно на экране) печатается список полей:

	P	Q	R
		Дата продажи
		Код товара
		Наименование
		Количество
		Цена
		Сумма

– вызываем панель форм (Вид > Панели > Инструментов >Формы), на ней выбираем элемент «Поле со списком» и рисуем его в районе ячейки D3 (как на рис. 5.4);

– ставим мышь на нарисованный элемент, щелчком ПКМ вызываем контекстное меню и выбираем пункт «Формат объекта», при этом откроется окно формата создаваемого списка (рис. 5.5):

Рис. 5.5. Окно Формат элемента управления

– в поле «Формировать список по диапазону» указать местоположение списка полей;

– в поле «Связь с ячейкой» указать ячейку, в которую будет записываться номер выбранного поля.

– щелкнуть «Ok».

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

Создание макроса для сортировки

Выполните команды: Сервис > Макрос > Начать запись > На запрос об имени макроса напечатайте «Сортировка» > «Ok» > Установите курсор в C11 > Данные > Сортировка > В качестве поля сортировки выберите «Наименование» > «Ok» > Сервис > Макрос > Остановить запись.

Создание кнопки для запуска макроса

– с панели «Формы» взять элемент «Кнопка» и нарисовать ее районе ячейки E3 (как на рис. 5.4):

– на запрос о назначении макроса указать макрос «Сортировка»;

– исправить надпись на кнопке.

Если сейчас щелкнуть по созданной кнопке, то данные должны будут отсортироваться по полю «Наименование».

Модификация макроса

Точно так же можно сделать кнопки для сортировки по остальным полям. Но все это как–то «не смотрится». Тем более, что работа кнопки никак не зависит от выбранного в списке поля сортировки.

Посмотрим, что записано в созданном макросе.

Выполним команды Сервис > Макрос > Макросы > Выбрать макрос «Сортировка» > Изменить.

Появится текст макроса.

Sub Сортировка()

Range("C11").Select

Range("Данные_продаж").Sort Key1:=Range("E12"),Order1:=xlAscending,Header:= _

xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

DataOption1:=xlSortNormal

End Sub

Номинальное знание английского языка позволяет понять записанные команды и по возможности изменить их.

Первая команда соответствует переходу на ячейку «С11» (когда мы щелкнули по ней).

Вторая команда очень длинная, занимает три строчки и выполняет метод сортировки для диапазона «Данные_продаж».

Основная часть команды – Range("Данные_продаж").Sort выполняет сортировку выделенной части. Остальные компоненты – это параметры сортировки, которые можно частично или все удалить.

Нас интересует параметр Key1, который определяет поле сортировки. Его значение, равное E12, соответствует столбцу E, в котором находится поле «Наименование». Если сейчас вместо E11 напечатать G11 и в Excel щелкнуть по кнопке «Сортировка», то сортировка произойдет по полю «Цена».

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

В Excel для обращения к ячейкам существует два способа.

Первый – с помощью объекта Range (как в приведенном макросе).

Второй – с помощью объекта Cells следующего формата:

Cells(Номер строки, Номер столбца).

Способы эквиваленты и используются по ситуации. Например, вместо Range(«C11») вполне можно записать Cells(11, 3).

Поэтому макрос можно переписать следующим образом:

Sub Сортировка()

Dim k As Integer ‘Объявляем переменную целого типа

Range("C11").Select ‘Выделяем ячейку C11

k=Range(“Q11”) ‘Определяем номер выбранного пункта

Range("Данные_продаж").Sort Key1:=Cells(12,k+2), Header:=xlGuess

End Sub

Здесь из параметров сортировки оставлен лишь два параметра – ключ сортировки и наличие заголовка.

Перепечатайте (перекопируйте) указанный текст макроса и убедитесь, что он нормально работает.

Дата добавления: 2014-10-31