Создание информационной системы средствами МС Excel и VBA

                                                                                                                                                                                                     

ФЕДЕРАЛЬНОЕ АГЕНТСТВО  ПО ОБРАЗОВАНИЮ 

ГОСУДАРСТВЕННОЕ ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ ВЫСШЕГО ПРОФЕССИОНАЛЬНОГО ОБРАЗОВАНИЯ

 

Камская Государственная Инженерно-Экономическая Академия

 

 

Кафедра «ПИУ»

 

 

Курсовая  работа

 

по дисциплине «Информатика»

на тему «Создание  информационной системы средствами МС Excel и VBA

(База данных «Сведения о багаже»)

 

 

 

 

 

 

                       Выполнил: студент гр. 2119

                       Нигматуллина В. Д.

                       № зачётной кн. 2081917

 

                                                                  Проверил: ст. преподаватель   

                                                       Хасанова Ф. С.        

 

 

 

Набережные Челны

2009

 

Содержание

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Введение

В современном мире информация приобретает  все большее значение, объемы и  размеры её увеличиваются. Необходимость систематизирования информации заставила человека изобретать различные схемы, каталоги, таблицы для удобного использования данных. Век компьютеризации предоставляет возможность быстрого и удобного систематизирования данных с помощью баз данных.  
          База данных является организованной на машинном носителе совокупностью взаимосвязанных данных и содержит сведения о различных сущностях одной предметной области реальных объектах, процессах, событиях или явлениях. Простым примером базы данных являются картотеки в больницах, ведь там информация хранится строго в определенном порядке и по определенным разделам. В настоящее время базы данных расцениваются как электронные хранилища информации, доступ к которым осуществляется с одного или нескольких компьютеров в зависимости от архитектуры. Работа с подобными автоматизированными информационными системами осуществляется при помощи систем управления базами данных это программные средства, предназначенные для создания, наполнения, обновления и удаления баз данных.  
          Целью данной курсовой работы является изучение основных понятий, определений, свойств баз данных и систем управления ими. Объектом исследования является получение практического навыка по созданию баз данных и систем управления базами данных. Предметом исследования является компоненты данной среды, используемые для создания баз данных и систем управления ими.

 

 

 

 

 

 

 

 

 

 

 

 

Задание

Багаж пассажира характеризуется  количеством вещей и общим  весом вещей. Создать файл багаж, содержащий сведения о багаже нескольких пассажиров. Сведения о багаже каждого пассажира представляют собой запись с двумя полями: одно поле целого типа (количество вещей) и одно-действительное (вес в килограммах).

  Выяснить, имеется ли пассажир, багаж которого состоит из  одной вещи весом менее 30 кг. Найти максимальный и минимальный вес багажа среди багажей всех пассажиров. Вычислить общий вес багажа пассажиров одного из рейсов. Найти пассажира, чей вес багажа равен 23 кг. На основании веса всех багажей, вычислить средний вес багажа. Вычислить среди всех рейсов количество билетов с номером 1 .Вычислить общий вес и общее количество вещей багажа каждого рейса. Найти пассажиров, чей вес багажа более 20кг. и рассчитать, внесённую ими доплату. Создать сводную таблицу. Упорядочить информацию. Построить диаграмму.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Титульный лист

При открытии документа «Курсовая  работа» перед нами выходит титульный  лист, который, представляет своеобразное меню данной курсовой работы (рис.1):

Рис.1

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

Например для кнопки «Расширенный фильтр» :

Sub врасширфильтр()

'

' врасширфильтр Макрос

' Макрос записан 10.05.2009 (Венера)

'

 

'

    Sheets("Расширенный фильтр").Select

    ActiveWindow.SmallScroll Down:=-36

End Sub

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

Разработка форм

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

С помощью формы можно:

заносить данные в таблицу;

просматривать или корректировать данные;

удалять данные;

отбирать записи по критерию.

Вставка записей  с помощью формы

  1. Укажите ячейку списка, начиная с которой следует добавлять записи.
  2. Выберите команду Форма в меню Данные.
  3. Нажмите кнопку Добавить.
  4. Введите поля новой записи, используя клавишу TAB для перемещения к следующему полю. Для перемещения к предыдущему полю используйте сочетание клавиш SHIFT+TAB.

Чтобы добавить запись в список, нажмите клавишу ENTER. По завершении набора последней  записи нажмите кнопку Закрыть, чтобы добавить набранную запись и выйти из формы.

Если поле списка содержит формулу, то в форме выводится  ее результат. Изменять это поле в  форме нельзя. При добавлении записи, содержащей формулу, результат формулы не будет вычислен до нажатия клавиши ENTER или кнопки Закрыть. Чтобы отменить добавление записи, нажмите кнопку Вернуть перед нажатием клавиши ENTER или кнопки Закрыть. Microsoft Excel автоматически добавляет запись при переходе к другой записи или закрытии формы.

Поиск записей в списке с помощью  формы

Для перемещения  на одну запись нажмите на стрелки  полосы прокрутки в диалоговом окне. Чтобы переместиться на 10 записей, нажмите полосу прокрутки между стрелками.

Нажмите кнопку Далее для перехода к следующей  записи списка и Назад — для  перехода к предыдущей.

Чтобы задать условия  поиска или условия сравнения, нажмите  кнопку Критерии. Введите критерии в форме. Чтобы найти совпадающие  с критериями записи, нажмите кнопки Далее или Назад. Чтобы вернуться  к правке формы, нажмите кнопку Правка.

Запишем макрос к кнопке «Вызов формы»:

Sub примененформы()

'

' примененформы  Макрос

' Макрос записан  21.05.2009 (Венера)

'

'

    ActiveWindow.ScrollWorkbookTabs Position:=xlLast

    Sheets("Применение формы").Select

    ActiveWindow.SmallScroll Down:=-27

End Sub

Теперь, нажимая на кнопку «Вызов формы» на лист выводится следующие окно (рис.2):

    Рис.2

Теперь, выбрав одно из действий мы сможем изменить нашу базу данных (добавить, или удалить  строку). На рисунке 3 изображена БД

Рис.3

 

 

 

Сортировка данных

Чтобы выполнить сортировку списка, нужно выделить соответствующий диапазон ячеек, выбрать команду Данные/Сортировка. В появившемся окне (рис.4) задаются ключи сортировки, порядок сортировки. Всего можно задать 3 ключа сортировки. Порядок сортировки может быть «По возрастанию» и «По убыванию».

 Рис.4

Я сделаю сортировку базы данных по рейсу. Для этого мне придётся воспользоваться  сортировкой в особом порядке. Чтобы воспользоваться собственным порядком сортировки, его нужно предварительно создать, выделяем базу данных и :

  1. с помощью команды Сервис/Параметры вызвать окно Параметры
  2. перейти на вкладку Списки
  3. в поле Элементы списка ввести элементы, разделяя их нажатием клавиши Enter
  4. после ввода всех элементов нажать кнопку Добавить и закрыть окно Параметры

 Рис.5

Теперь, мы имеем собственные параметры для сортировки в особом порядке по рейсам.(рис.6)

  Рис. 6

При нажатии на конопку «Сортировка по рейсу» на листе появится автоматически отсортированная БД, т. к. был в ходе всех действий был записан макрос для данной кнопки:

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

'

' сортировка Макрос

' Макрос записан 08.05.2009 (Венера)

'

 

'

    ActiveWindow.SmallScroll Down:=-30

    Range("A1:H36").Select

    Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Key2:=Range("H2") _

        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=8, MatchCase:= _

        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _

        :=xlSortNormal

    ActiveWindow.SmallScroll Down:=-6

End Sub

А при нажатии на кнопку «База  данных» выведется первоначальная БД :

Sub базасортировка()

'

' базасортировка Макрос

' Макрос записан 08.05.2009 (Венера)

'

 

'

    ActiveWindow.SmallScroll Down:=-33

    Range("A1:H36").Select

    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _

        OrderCustom:=8, MatchCase:=False, Orientation:=xlTopToBottom, _

        DataOption1:=xlSortNormal

End Sub

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Автофильтр и функция ВПР

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

Чтобы включить автофильтр, нужно  воспользоваться командой Данные/Фильтр/Автофильтр. Excel выведет кнопки со стрелками (кнопки авто фильтра) рядом с каждым заголовком столбца.(рис. 7)

  Рис.7

При помощи автофильтра мне следует  найти пассажирей чей вес багажа более 20кг. Для этого произведем фильтрацию по столбцу «Вес багажа». Выберем пункт «Условие»,перед нами появится окно (рис.8) выберем соответствующие условие.

    Рис. 8    

При выполнении всех этих действии следует запустить автоматическую запись макроса для кнопки «Автофильтр»

Sub автофильтр()

'

' автофильтр Макрос

' Макрос записан 17.05.2009 (Венера)

'

 

'

    ActiveWindow.SmallScroll Down:=-36

    Range("A1:H36").Select

    Selection.AutoFilter

    Selection.AutoFilter Field:=7, Criteria1:=">20", Operator:=xlAnd

    Range("C40").Select

End Sub

 Рис.9

При помощи автофильтра я нашла  пассажиров, которым предстоит внести доплату, за превышение веса багажа(рис.9). Для багажа каждого пассажира будет оформлена следующая квитанция (рис.10):

  Рис. 10

Создам таблицу доплата(рис.11):

Пассажир

Превышение(кг.)

Доплата

     
     
     
     

Рис.11

Куда введем фамилии пассажиров, чей багаж более 20 кг. Рассчитаем количество превышающих килограмм багажа каждого из пассажиров: =G5-C58   где G5-ячейка веса багажа(одного из пассажиров), а ячейка C58=20 (соответственно). Рассчитаем доплату из расчета, что 1кг. Превышения веса равен 100 руб. Воспользуемся функцией ВПР( ) (вертикальное поисковое решение)(рис.12). Применяя эту функцию мы должны ответить на след. вопросы:

  • что ищем
  • где осуществляется поиск
  • что является результатом поиска
  • как точно нужно искать

Рис.12

=ВПР(G50;$G$49:$H$52;2;ЛОЖЬ)*H46   домножив формулу функции ВПР() на ячейку,где указана сумма доплаты за 1 кг., получаем в руб. доплату которую необходимо будет ввести пассажирам. (рис. 13)

  Рис. 13

Так же при проведении всех выше описанных  действий, я запустила автоматическую запись макросов для кнопки «Доплата»:

Sub доплата()

'

' доплата Макрос

' Макрос записан 17.05.2009 (Венера)

'

 

'

    Range("G49").Select

    ActiveCell.FormulaR1C1 = "Нуреева Р. Р."

    Range("G50").Select

    ActiveCell.FormulaR1C1 = "Голубина С. В."

    Range("G51").Select

    ActiveCell.FormulaR1C1 = "Курбанаев М. Х."

    Range("G52").Select

    ActiveCell.FormulaR1C1 = "Бочкарёв Д. Р."

    Range("H49").Select

    ActiveCell.FormulaR1C1 = "=R[-44]C[-1]-R[9]C[-5]"

    Range("H50").Select

    ActiveCell.FormulaR1C1 = "=R[-40]C[-1]-R[8]C[-5]"

    Range("H51").Select

    ActiveCell.FormulaR1C1 = "=R[-21]C[-1]-R[7]C[-5]"

    Range("H52").Select

    ActiveCell.FormulaR1C1 = "=R[-20]C[-1]-R[6]C[-5]"

    Range("I49").Select

    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],R49C7:R52C8,2,FALSE)"

    Selection.FillDown

    Range("I49").Select

    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],R49C7:R52C8,2,FALSE)*R[-3]C[-1]"

    Range("I50").Select

    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],R49C7:R52C8,2,FALSE)*R[-4]C[-1]"

    Range("I51").Select

    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],R49C7:R52C8,2,FALSE)*R[-5]C[-1]"

    Range("I52").Select

    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],R49C7:R52C8,2,FALSE)*R[-6]C[-1]"

    Range("I53").Select

      End Sub

Для сброса всех действий на этом листе  создала кнопку «Сброс» :

Sub сбросдоплата()

'

' сбросдоплата Макрос

' Макрос записан 17.05.2009 (Венера)

'

 

'

    Range("G49:I52").Select

    Selection.ClearContents

    ActiveWindow.SmallScroll Down:=-12

    Selection.AutoFilter

    ActiveWindow.SmallScroll Down:=18

    Range("F41").Select

End Sub

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Расширенный фильтр

При помощи расширенного фильтра необходимо выяснить, имеется ли пассажир(ы), багаж  котор(ого)(ых) состоит из одной вещи весом менее 30кг.- представим это  условие, как диапазон условий расширенного фильтра (рис.14)

Рис. 14

Расширенный фильтр является более  гибким средством отбора записей  из БД, чем Автофильтр и позволяет  задавать:

  • условия, соединенные логическим оператором ИЛИ, для нескольких столбцов
  • три и более условий для конкретного столбца
  • вычисляемые условия

чтобы воспользоваться расширенным  фильтром, нужно выбрать команду  меню Данные/Фильтр/Расширенный фильтр. На экране появится диалоговое окно (рис.15):

 Рис.15

После нажатия на кнопку ОК, на рабочем листе, увидим пассажиров, которые соответствуют выше указанным условиям (рис.16):

Рис.16

Макрос для кнопки «Условие»:

Sub условиерасшир()

'

' условиерасшир Макрос

' Макрос записан 10.05.2009 (Венера)

'

 

'

    ActiveWindow.SmallScroll Down:=-33

    Range("F1:G1").Select

    Selection.Copy

    ActiveWindow.SmallScroll Down:=15

    Range("B38").Select

    ActiveSheet.Paste

    Range("B39").Select

    Application.CutCopyMode = False

    ActiveCell.FormulaR1C1 = "1"

    Range("C39").Select

    ActiveCell.FormulaR1C1 = "<30"

    Range("B38:C39").Select

    With Selection

        .HorizontalAlignment = xlGeneral

        .VerticalAlignment = xlBottom

        .WrapText = False

        .Orientation = 0

        .AddIndent = False

        .IndentLevel = 0

        .ShrinkToFit = False

        .ReadingOrder = xlContext

        .MergeCells = False

    End With

    Selection.Borders(xlDiagonalDown).LineStyle = xlNone

    Selection.Borders(xlDiagonalUp).LineStyle = xlNone

    With Selection.Borders(xlEdgeLeft)

        .LineStyle = xlContinuous

        .Weight = xlThin

        .ColorIndex = xlAutomatic

    End With

    With Selection.Borders(xlEdgeTop)

        .LineStyle = xlContinuous

        .Weight = xlThin

        .ColorIndex = xlAutomatic

    End With

    With Selection.Borders(xlEdgeBottom)

        .LineStyle = xlContinuous

        .Weight = xlThin

        .ColorIndex = xlAutomatic

    End With

    With Selection.Borders(xlEdgeRight)

        .LineStyle = xlContinuous

        .Weight = xlThin

        .ColorIndex = xlAutomatic

    End With

    With Selection.Borders(xlInsideVertical)

        .LineStyle = xlContinuous

        .Weight = xlThin

        .ColorIndex = xlAutomatic

    End With

    With Selection.Borders(xlInsideHorizontal)

        .LineStyle = xlContinuous

        .Weight = xlThin

        .ColorIndex = xlAutomatic

    End With

    Range("D38").Select

End Sub

Так же, при выполнение расширенного фильтра записала макрос, для кнопки «Расширенный фильтр» :

Sub расширфильтр()

'

' расширфильтр Макрос

' Макрос записан 10.05.2009 (Венера)

'

 

'

    ActiveWindow.SmallScroll Down:=-21

    Range("A1:H36").Select

    Range("A1:H36").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _

        Range("B38:C39"), Unique:=False

    ActiveWindow.SmallScroll Down:=-18

End Sub

Макрос для кнопки «Сброс» служащей для сброса всех действий произведенных на данном листе:

Sub сбросрасширфильтр()

'

' сбросрасширфильтр Макрос

' Макрос записан 10.05.2009 (Венера)

'

 

'

    ActiveWindow.SmallScroll Down:=-3

    ActiveSheet.ShowAllData

    ActiveWindow.SmallScroll Down:=12

    Range("B38:C39").Select

    Selection.ClearContents

    Selection.Borders(xlDiagonalDown).LineStyle = xlNone

    Selection.Borders(xlDiagonalUp).LineStyle = xlNone

    Selection.Borders(xlEdgeLeft).LineStyle = xlNone

    Selection.Borders(xlEdgeTop).LineStyle = xlNone

    Selection.Borders(xlEdgeBottom).LineStyle = xlNone

    Selection.Borders(xlEdgeRight).LineStyle = xlNone

    Selection.Borders(xlInsideVertical).LineStyle = xlNone

    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

    Selection.Interior.ColorIndex = xlNone

    Range("E39").Select

End Sub

 

 

 

Нахождение max и min

Найдём при помощи функции БД максимальный и минимальный вес  багажа, из всех багжей пассажиров 578 рейса. Для этого воспользуемся функциями ДМАКС и ДМИН. ДМАКС – возвращает наибольшее число в столбце списка или БД, которое удовлетворяет заданным условиям. ДМИН- возвращает наименьшее число в столбце списка или БД, которое удовлетворяет заданным условиям.

Для начала я создала диапазон критериев – max, min вес багажа должен находится среди багажа пассажиров 578 рейса(рис.17):

  Рис.17

Вызываем функции БД и выбираем ДМАКС, перед нами выводится окно, куда мы вносим запрашиваемые данные (рис.18):

Рис.18

После, вызываем функцию ДМИН, и  в выведенное окно вновь вносим запрашиваемые данные (рис.19):

  Рис.19

После произведённых действий получаем следующее (рис.20):

Рис.20

Макрос для кнопки «Нахождение max»:

Sub Дмакс()

'

' Дмакс Макрос

' Макрос записан 08.05.2009 (Венера)

'

'

    Range("D39").Select

    ActiveCell.FormulaR1C1 = _

        "=DMAX(R[-38]C[-3]:R[-3]C[4],R[-38]C[3],R[-1]C[-1]:RC[-1])"

End Sub

Макрос для кнопки «Нахождение min»:

Sub Дмин()

'

' Дмин Макрос

' Макрос записан 08.05.2009 (Венера)

'

'

    ActiveWindow.SmallScroll Down:=-6

    Range("E39").Select

    ActiveCell.FormulaR1C1 = _

        "=DMIN(R[-38]C[-4]:R[-3]C[3],R[-38]C[2],R[-1]C[-2]:RC[-2])"

End Sub

Кнопка «Сброс» действий:

Sub сброс()

' сброс Макрос

' Макрос записан 08.05.2009 (Венера)

'

'

    Range("D39:E39").Select

    Selection.ClearContents

    Range("E44").Select

End Sub

Среднее значение

Мне необходимо вычислить средний  вес багажа воспользовавшись данными  о весе багажа всей БД. Для этого  я воспользуюсь функцией БД- ДСРЗНАЧ.  ДСРЗНАЧ – вычисляет среднее значение в столбце списка или БД среди значений, удовлетворяющих заданным условиям. Диапазон критериев в моей задаче будет выглядеть следующим образом(рис.21):

  Рис.21

Вызываем функции БД, затем среди  всех функций выбираем – ДСРЗНАЧ, перед нами выходит окно, которое  запрашивает данные необходимые для вычисления среднего значения (рис.22):

Рис.22

И получаем средний вес багажа (рис.23):

 Рис.23

Так же и при вычислении среднего значения я записала макрос для кнопки «Функция ДСРЗНАЧ» :

Sub средзнач()

'

' средзнач Макрос

' Макрос записан 08.05.2009 (Венера)

'

 

'

    Range("F38").Select

    ActiveCell.FormulaR1C1 = _

        "=DAVERAGE(R[-37]C[-5]:R[-2]C[2],R[-37]C[1],R[-37]C[1]:R[-2]C[1])"

End Sub

А так же создала кнопку «Сброс»  на данном листе, для автоматического  удаления вычисленного среднего веса:

Sub сброссред()

'

' сброссред Макрос

' Макрос записан 08.05.2009 (Венера)

'

 

'

    Range("F38").Select

    Selection.ClearContents

End Sub

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Сумма

Вычислим общий вес багажа для  рейса 387. Для этого, не сомненно, придётся использовать функцию БДСУММ. БДСУММ – суммирует числа в столбце списка или БД, которые удовлетворяют заданным условиям. Самым первым действием создадим диапазон критериев (рис.24)

 Рис.24

После обращаемся к функциям БД  и выбираем функцию – БДСУММ, на рабочий лист выводится окно, куда мы вносим запрашиваемые, для данной функций данные (рис.25):

 Рис.25

При нажатии на кнопку ОК, мы получаем общий вес багажа всех пассажиров рейса 387 (рис.26):

  Рис.26

При использовании данной функции  мною была запущена запись макросов для кнопки «Функция БДСУММ» :

Sub суммавеса()

'

' суммавеса Макрос

' Макрос записан 08.05.2009 (Венера)

'

 

'

    ActiveWindow.SmallScroll Down:=-9

    Range("D39").Select

    ActiveCell.FormulaR1C1 = _

        "=DSUM(R[-38]C[-3]:R[-3]C[4],R[-38]C[3],R[-1]C[-1]:RC[-1])"

    With Selection

        .HorizontalAlignment = xlCenter

        .VerticalAlignment = xlBottom

        .WrapText = False

        .Orientation = 0

        .AddIndent = False

        .IndentLevel = 0

        .ShrinkToFit = False

        .ReadingOrder = xlContext

        .MergeCells = False

    End With

End Sub

А так же для кнопки «Сброс» действий на этом листе:

Sub сброссумма()

'

' сброссумма Макрос

' Макрос записан 08.05.2009 (Венера)

'

 

'

    Range("D39").Select

    Selection.ClearContents

End Sub

 

 

 

 

 

 

 

Извлечение

Из всей БД мне необходимо найти  пассажира, чей вес багажа равен 23 кг. Для этого я воспользуюсь функцией БИЗВЛЕЧЬ. БИЗВЛЕЧЬ – извлекает отдельное значение из столбца списка или БД, которое удовлетворяет заданным усолвиям. Если такой ячейки не обнаружено, возвращает значение #ЗНАЧ!. Если заданным условиям удовлетворяют несколько ячеек, то возвращается ошибочное значение # ЧИСЛО!

Для начала, необходимо создать диапазон критериев (рис.27):

 Рис.27

После вызываем функции БД и  находим и выбираем среди всех функций- БИЗВЛЕЧЬ. Вносим необходимые данные в появившееся окно (рис.28):

 Рис.28

Нажав на кнопку ОК мы получим следующее (рис.29):

 Рис.29

Макрос для кнопки «Функция БИЗВЛЕЧЬ»:

Sub извлечь()

'

' извлечь Макрос

' Макрос записан 08.05.2009 (Венера)

'

 

'

    Range("E39").Select

    ActiveCell.FormulaR1C1 = _

        "=DGET(R[-38]C[-4]:R[-3]C[3],R[-38]C[-3],R[-1]C[-1]:RC[-1])"

    With Selection

        .HorizontalAlignment = xlCenter

        .VerticalAlignment = xlBottom

        .WrapText = False

        .Orientation = 0

        .AddIndent = False

        .IndentLevel = 0

        .ShrinkToFit = False

        .ReadingOrder = xlContext

        .MergeCells = False

    End With

End Sub

Макрос для кнопки «Сброс» действий на данном листе:

Sub сбросизвлечь()

'

' сбросизвлечь Макрос

' Макрос записан 08.05.2009 (Венера)

'

 

'

    Range("E39").Select

    Selection.ClearContents

    Range("E44").Select

End Sub

 

 

 

 

 

 

 

Счёт

В задании требуется среди всей БД найти кол-во билетов с № 1. В этом поможет функция БСЧЁТ. БСЧЁТ – подсчитывает кол-во ячеек, содержащих числа, в столбце списка или БД среди всех записей, удовлетворяющих заданным условиям.

Создадим диапазон критериев (рис.30):

 Рис. 30

Вызовем функции БД и выберем -  БСЧЁТ. В появившемся перед нами окне БСЧЁТ, вводим данные (рис.31):

 Рис.31

После на рабочем листе получаем следующее (рис.32):

Рис.32

Была создана кнопка «Функция БСЧЁТ», при нажатии на которую, благодаря  записанным макросам происходит автоматический счёт всех билетов с данным номером:

Sub счёт()

'

' счёт Макрос

' Макрос записан 08.05.2009 (Венера)

'

 

'

    ActiveWindow.SmallScroll Down:=3

    Range("E39").Select

    ActiveCell.FormulaR1C1 = _

        "=DCOUNT(R[-38]C[-4]:R[-3]C[3],R[-38]C[3],R[-1]C[-1]:RC[-1])"

    With Selection

        .HorizontalAlignment = xlCenter

        .VerticalAlignment = xlBottom

        .WrapText = False

        .Orientation = 0

        .AddIndent = False

        .IndentLevel = 0

        .ShrinkToFit = False

        .ReadingOrder = xlContext

        .MergeCells = False

    End With

End Sub

Сброс всех действий на данном листе  кнопка «Сброс»:

Sub сброссчёт()

'

' сброссчёт Макрос

' Макрос записан 08.05.2009 (Венера)

'

 

'

    Range("E39").Select

    Selection.ClearContents

End Sub

 

 

 

 

 

 

 

 

Промежуточные итоги

Команда Данные/Итоги. . . создаёт промежуточные и общие итоги. Для подведения итогов можно использовать различные функции: Сумма, Кол-во значений, Среднее. Максимум, Минимум, Произведение и др. Мне необходимо вычислить общий вес и общее количество вещей багажа каждого рейса.

Для начала отсортируем исходный список по полю Рейс (рис.33):

Рис.33

Затем воспользуемся командой Данные/Итоги…на экране появится диалоговое окно (рис.34):

 Рис.34

При каждом изменении в столбце  Рейс, будет проводится операция сложения (суммы) кол-ва и веса багажа, по данному рейсу (рис.35):

Рис.35

Макрос для кнопки «Итоги»:

Sub итоги()

'

' итоги Макрос

' Макрос записан 10.05.2009 (Венера)

'

 

'

    ActiveWindow.SmallScroll Down:=-27

    Range("A1:H36").Select

    Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlGuess, _

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

        DataOption1:=xlSortNormal

    Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(6, 7), _

        Replace:=True, PageBreaks:=False, SummaryBelowData:=True

    ActiveWindow.SmallScroll Down:=15

    Range("I42").Select

    ActiveWindow.SmallScroll Down:=3

    Range("A44:H45").Select

    Selection.Borders(xlDiagonalDown).LineStyle = xlNone

    Selection.Borders(xlDiagonalUp).LineStyle = xlNone

    With Selection.Borders(xlEdgeLeft)

        .LineStyle = xlContinuous

        .Weight = xlThin

        .ColorIndex = xlAutomatic

    End With

    With Selection.Borders(xlEdgeTop)

        .LineStyle = xlContinuous

        .Weight = xlThin

        .ColorIndex = xlAutomatic

    End With

    With Selection.Borders(xlEdgeBottom)

        .LineStyle = xlContinuous

        .Weight = xlThin

        .ColorIndex = xlAutomatic

    End With

    With Selection.Borders(xlEdgeRight)

        .LineStyle = xlContinuous

        .Weight = xlThin

        .ColorIndex = xlAutomatic

    End With

    With Selection.Borders(xlInsideVertical)

        .LineStyle = xlContinuous

        .Weight = xlThin

        .ColorIndex = xlAutomatic

    End With

    With Selection.Borders(xlInsideHorizontal)

        .LineStyle = xlContinuous

        .Weight = xlThin

        .ColorIndex = xlAutomatic

    End With

    Range("J39").Select

End Sub

Макрос для кнопки «Сброс» всех итогов, возврат к БД:

Sub итогисброс()

'

' итогисброс Макрос

' Макрос записан 10.05.2009 (Венера)

'

 

'

    ActiveWindow.SmallScroll Down:=-27

    Range("A1:H45").Select

    ActiveWindow.SmallScroll Down:=0

    Selection.RemoveSubtotal

    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _

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

        DataOption1:=xlSortNormal

    ActiveWindow.SmallScroll Down:=0

End Sub

 

 

 

 

 

 

 

 

 

 

 

Сводная таблица

Сводные таблицы позволяют объединить данные базы данных и представить в различных вариантах. Например, нам нужно узнать общее кол-во вещей багажа каждого рейса. Для этого выбираем команду Данные / Сводная таблица. Затем с помощью «Мастера сводных таблиц и диаграмм» указываем диапазон содержащий исходные данные и выбираем место расположения сводной таблицы. Из «Списка полей сводной таблицы», выбираем «Багаж (кол-во)», также берем «Рейс», затем поочередно курсором перетаскиваем их в сводную таблицу. В итоге, получаем сводную таблицу (рис.36):

 Рис.36

Сводная диаграмма

На основе сводной таблицы можно  создать и сводную диаграмму. Выделим какую-либо ячейку на сводной  таблице, и кликнем правой кнопкой  мыши, из предложенного списка выберем  «Сводная диаграмма»  и автоматически  на др. листе выведется диаграмма  на основе данных сводной таблицы.(рис.37) :

Рис.37

Макрос для перехода от сводной  диаграммы к сводной таблице:

Sub ксводной()

'

' ксводной Макрос

' Макрос записан 21.05.2009 (Венера)

'

 

'

    ActiveWindow.ScrollWorkbookTabs Position:=xlLast

    Sheets("Сводная таблица").Select

End Sub

И наоборот, макрос для перехода от сводной таблицы к диаграмме:

Sub диаграммсвод()

'

' диаграммсвод Макрос

' Макрос записан 21.05.2009 (Венера)

'

 

'

    Sheets("Сводная диаграмма").Select

End Sub

 

 

 

 

 

 

 

 

 

 

 

 

 

Диаграмма

Программа Microsoft Excel предоставляет пользователю широкие возможности для визуализации числовых данных из таблиц. Двумерное изображение при этом называется диаграммой, объемное – гистограммой. Числовые ряды можно представить в виде графиков. Не важно, какую форму представления данных вы выберите, порядок действий будет один и тот же. При этом будет работать программа, которая называется Мастером диаграмм. Пользователю только необходимо в окне диалога определить параметры изображения.