Разработка информационной системы

 

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

     Очевидно, что колонки С, D, E и F должны заполняться случайно, а колонки G и H будут заполняться исходя из данных справочника по товарам.

     Для заполнения колонок случайными данными можно использовать имеющуюся в Excel функцию генерации случайных чисел – СЛЧИС(). Она генерирует  случайные числа из диапазона 0..1. Для генерации целых чисел из произвольного диапазона используется формула:

     =А  + ЦЕЛОЕ((В – А+1)*СЛЧИС())

     где А – нижняя граница необходимого диапазона;

            В – верхняя граница диапазона;

            ЦЕЛОЕ – имеющаяся в Excel функция округления дробных чисел. 

     Начнем  с колонки «Дата продажи». В А6 вводим формулу:

     = 1 + ЦЕЛОЕ(30 * СЛЧИС())

     Смысл формулы в том, что в данной колонке генерируется случайный номер дня продаж – всего 30 дней.

     Для перевода номера дня в даты в отдельную  ячейку (например, в A1) вводим начальную дату продаж – пусть это будет 01.10.09. Задаем для этой ячейки формат «общий». В ней получится число 40087. Это будет число дней, прошедших с сначала прошлого века (с 1900 года).

     Число же 40086 будет соответствовать сдвигу номера дня продаж на номер современного дня начала продаж. Поэтому в С6 формулу:

     = А6 + 40086

     и копируем ее на 300 строк данного столбца. После каждой манипуляции с данными их значения меняются. Это свойство функции СЛЧИС.

     Чтобы избавиться от этого эффекта:

     - копируем столбец В в буфер;

     - переместимся в ячейку G6 и произведем вставку данных командой Правка > Специальная ставка > Значения (обратите внимание: в ячейках столбца G остались только значения, которые уже не будут меняться);

     - скопируем столбец G в столбец В и преобразуем данные этого столбца в формат «дата»;

     - очистить столбец G.

     По  аналогичной схеме заполняется  колонка «Код товара»:

     - в А6 вводится формула 

     = 1+ ЦЕЛОЕ(14 *СЛЧИС())

     (здесь  6 – количество товаров);

     - формула копируется на 3000 строк;

     - путем перекопированния избавляемся  от формулы.

     Аналогично  заполняется колонка «Количество»:

     - в А6 вводится формула 

     = 1+ ЦЕЛОЕ(3 *СЛЧИС())

     - формула копируется на 3000 строк;

     - путем перекопированния избавляемся  от формулы.

     Для заполнения столбца E в E6 вводим формулу:

     =ВПР(D6;Прайс-лист;2)

     и копируем ее на 300 строк.

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

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

     РозничнаяЦена = ОптоваяЦена*(1+Наценка)

     При реализации в Excel эта формула должна «сама» по коду товара из столбца D брать с листа Товары» значения оптовой цены и наценки. Для этого также используется функция ВПР. Т.е. в G6 вводится формула:

     = ВПР(D6; Прайс-лист;4) * (1 + ВПР(D6; Прайс-лист;5))

     В первой ВПР оптовая цена берется из четвертой колонки справочной таблицы, а во второй – наценка берется из пятой колонки справочной таблицы. Данная формула копируется на весь столбец  G.

     В столбец H вводится формула расчета суммы покупки (с последующим копированием):  = F6 * G6.

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

      

Рисунок 3.

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

- из  списка выбирается поле сортировки  и «Ok».

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

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

 

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

- ставим  мышь на нарисованный элемент,  щелчком ПКМ вызываем контекстное меню и выбираем пункт «Формат объекта»:

 

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

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

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

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

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

      - с панели «Формы» взять элемент  «Кнопка» и нарисовать ее районе ячейки E7 (как на рисунке):

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

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

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

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

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

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

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

    Range("C11").Select

    Range("C11:H303").Sort Key1:=Range("E12"), Order1:=xlAscending, Header:= _

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

        DataOption1:=xlSortNormal

End Sub

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

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

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

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

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

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

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

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

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

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

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

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

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

    Range("C11").Select    ‘

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

    Range("C11:H303").Sort Key1:=Cells(11,k+2), Header:=xlGuess

End Sub 

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

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

      На  рис.4 приведен возможный вариант интерфейса для организации поиска.

      Поиск производится следующим образом:

- в группе  полей «Критерии поиска» вводятся  нужные значения;

- щелкается  кнопка «Найти». 

Кнопка  «Очистить» предназначена для очистки  результатов поиска.

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

      Рисунок 4.

Итак, поэтапно.

      Макрос  для кнопки «Найти». Выполним команды Сервис > Макрос > Начать запись > На запрос об имени макроса напечатать имя «Найти» > Установить курсор в B8 > Данные > Фильтр > Расширенный фильтр > В окне «Расширенный фильтр» в поле «Исходный диапазон» указать адрес основной базы> В поле «Диапазон условий» указать $С$10:$H$11 > Установить переключатель в опции «Скопировать результат в другое место» > В поле «Поместить результат в диапазон» указать $C$17:$H$17 > Ok >  Сервис > Макрос > Остановить запись.