Математическое описание задачи

Основные положения.

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

Для обозначения объекта, представляющего электронную таблицу, в Excel принят термин рабочий лист. Рабочий лист - это множество ячеек, каждая из которых принадлежит некоторому столбцу и некоторой строке. Строки и столбцы идентифицируются, т.е. столбцы именуются, а строки нумеруются. По умолчанию рабочий лист имеет 256 столбцов с именами от А до IV и 65536 строки. Рабочий лист представлен на рисунке 1.

Рис. 1

Постановка задачи.

Спроектировать базу данных в Excel, содержащую следующие данные.

Входные данные:

- название санатория;

- количество мест для отдыхающих;

- численность персонала;

- общая площадь помещений;

- год постройки;

- название города (местности), где расположен санаторий;

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

Функции, выполняемые информационной технологией:

1. Заполнение и редактирование таблиц базы данных.

2. Формирование списка санаториев, численность персонала в которых в расчёте на одного отдыхающего не ниже запрашиваемой величины.

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

4. Подсчет суммарной площади помещений санаториев, принадлежащих каждому ведомству.

5. Формирование отчета, включающего полные сведения о санаториях, срок эксплуатации которых составляет более 20 лет.

Выходные данные:

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

Математическое описание задачи.

Ai=Bi /Сi , где Ai – численность персонала на одного отдыхающего.

Bi- численность персонала,

Сi – количество мест,

1.4 Создание базы данных (БД).

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

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

Сначала задаются имена полей БД, затем вводим данные в остальные ячейки. Вычисляемые поля заполняются в последнюю очередь. Заголовок таблицы целесообразно формировать с помощью надписи.

Ввод формул.

Любая формула начинается со знака =. Формулы могут содержать:

- числа и относительные ссылки =2*А2

- абсолютные ссылки =A8$С$7

- функции = СУММ(A1:B5)

Копировать ячейки можно несколькими способами:

1. С помощью буфера обмена

- Выделить диапазон копируемых ячеек

- Правка копировать

- Правка вставить (ряде случаев также надо выделить тот диапазон ячеек, куда копируем).

2. Методом заполнения.

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

Процесс заполнения БД с помощью формы.

1. создать заголовок таблицы Главная.

2. задать имена полей БД.

3. выделить ячейки A2:H2 (заголовки) и выполнить команду Данные - Форма. После этого появится форма (рис 2). Теперь надо ввести данные во все ячейки, кроме вычисляемого поля, переход осуществляется с помощью кнопки (tab) или мышью. Заполнив первую запись нажать на кнопку далее, также и с остальными записями.

4. закончив вводить данные, щелкнуть на кнопке закрыть.

Форма приведена на рисунке 2

Сначала заполняем таблицу( название продукта, количество, цена, дата завоза, нормативный срок реализации(в днях), данные о поставщике, название страны )

Рис. 2

Пример заполненной таблицы:

Рис.3

Для отображения формул вместо результатов надо выполнить команду Сервис -Параметры и на вкладке Вид поставить флажок около параметра Формула. Таблица с формулами приведена на рисунке 4.

Рис. 4

Поиск данных.

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

Рассмотрим поиск данных на примере поиска ведомств которым принадлежат санатории, Для этого надо раскрыть список поля «Ведомство» и выбрать ведомство.

Рис.5

Далее раскрыть список поля «Год» и выбрать команду (Условие…).

Результат выполнения данного примера на рис. 6.

Рис. 6

Создание макросов.

Макрос - это программа, записанная на встроенном в Excel языке VISUAL BASIC for application (VBA). Макрос может быть написан программистом или создан автоматически макрорекордером. Макрос, созданный этим макрорекордером, запоминает с момента его записи все действия пользователя, в том числе и ошибочные.

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

- табличный курсор поместить внутрь таблицы

- выполнить команду Сервис- Макрос - Начать запись

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

рис.7

- после нажатия Ok макрорекордер начинает запись макроса и записывает до тех пор, пока не будет выполнена команда Сервис - макрос -Остановить запись.

- Выполнить действия, которые записывает макрос:

1. Команда Данные – Фильтр - Автофильтр

2. Раскрыть список поля “Город” и выбрать условие.

Рис.8

Ввести значение меньше «Равно»

Нажать ОК.

- Остановить запись макроса.

Запуск макроса.

- Восстановить исходное состояние таблицы путем снятия автофильтра

- Убедиться, что табличный курсор находится внутри таблицы

- Выполнить команду Сервис- Макрос- Макросы, выделить требуемый макрос и щелкнуть на кнопке “ Выполнить ”

Типичный командный макрос имеет следующие элементы:

- Операторы sub и end sub располагаются в конце и начале макроса

- Имя макроса следует после оператора sub

- Тело макроса, то есть часть макроса, заключенная между операторами sub и end sub ,оно состоит из последовательности операторов, каждый из которых соответствует выполненному во время записи макроса действию.

Для удобства работы создается третий макрос, возвращающий таблицу в исходное состояние.

- Команда Сервис - Макрос - Начать запись

- Присвоить макросу имя

- Команда Данные - Фильтр – Отобразить все

- Остановить запись макроса