Задания для лабораторной работы

Задание 1

Самостоятельно постройте график функции

y(x)= на интервале [-4;2]

Для задания модуля используйте функцию ABS

Задание 2

Постройте диаграммы следующего вида:

Таблица 1			Лепестковая
Динамика объема работ, выполненных
по договорам строительного подряда в 2008 году
Месяц	Объем работ, млн.руб.
Январь	131,5
Февраль	103,8
Март	146,9
Апрель	131,1
Май	179,1
Июнь	318,9
Июль	443,1
Август	447,8
Сентябрь	460,1
Октябрь	306,8
Ноябрь	280,6
Декабрь	274,5
Таблица 2
Среднемесячная заработная плата работников	Гистограмма
предприятий по отраслям экономики
Промышленность	646,3	762,8	804,2	1128,2	1574,2
Строительство	780,1	815,4	826,3
Сельское хоз-во	351,7	372,7
Транспорт	789,1	973,1
Связь	903,1	1166,9
Таблица 3
Состав лиц, совершивших преступления (человек)	Линейчатая
Мужчины
Женщины
Таблица 4
Распределение оборота розничной торговли по формам собственности
(в процентах к итогу)	Линейчатая нормированная
Негосударственная	90,9
Потреб.кооперация		5,6	5,6	4,3
Государственная	9,1
Таблица 5
Естественное движение населения (тыс.чел)	График
Родившиеся	22,6	22,5	23,4	23,2	24,7
Умершие	38,4	36,9		36,7	37,8
Таблица 6.
Пассажирооборот по видам транспорта общего пользования (млн.пассажиро-км)
в 2000г.		Круговая
	2000г.
Ж/д	1560,6
Автобусный	1514,8
Трамвайный	954,2
Троллейбусный	369,9
Внутренний водный	8,8
Воздушный

4 Контрольные вопросы

1. Основные понятия диаграмм: ряды данных, категории, тип, элементы диаграмм.

2. Основные правила построения диаграмм в MS Excel.

3. Создание диаграмм с использованием Мастера диаграмм.

4. Создание графиков функций. Какой тип диаграммы необходимо использовать для построения графиков?

 

 

Лабораторная работа № 4: Работа со списками

1.Теоретический материал

С помощью Microsoft Excelможно создавать и обрабатывать списки данных.

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

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

В поле списка Сортировать по (рис.26) выбирается поле, по которому будут отсортированы данные, и тип сортировки:

по возрастанию – цифры сортируются по возрастанию, текст – в алфавитном порядке, логические выражения – ЛОЖЬ предшествует ИСТИНА.

по убыванию – сортировка в обратном порядке.

В поле списка Затем по указывается поле, по которому будут отсортированы данные, имеющие одинаковые значения в первом ключевом поле. Во втором поле Затем по указывается поле, по которому будут отсортированы данные, имеющие одинаковые значения в первых двух ключевых полях.

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

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

Формы данных

При выполнении характерных для баз данных операций, таких как поиск, сортировка, подведение итогов, Microsoft Excel автоматически рассматривает таблицу как базу данных.

При просмотре, изменении, добавлении и удалении записи в списке данных, а также при поиске записей по определенному критерию удобно использовать формы данных. При обращении к команде Форма меню Данные Microsoft Excel считывает данные и создает диалоговое окно формы данных (рис.27). В форме данных на экран выводится одна запись. При вводе или изменении данных в полях этого окна изменяется содержимое соответствующих ячеек базы данных.

Для использования форм данных таблица должна иметь имена столбцов. Имена столбцов становятся именами полей в форме данных. Поле соответствует каждому столбцу таблицы. Форма данных автоматически раскрывается так, чтобы вывести на экран сразу все поля в данной таблице, до 32 полей за один раз. С помощью полосы прокрутки можно прокручивать записи базы данных. Позиция выведенной записи указывается в верхнем правом углу. Передвигаться по полям формы можно с помощью мыши и клавиш Tab (вниз), Shift+Tab (вверх). В правой части окна расположены следующие кнопки.

Команда Фильтр меню Данные позволяет отыскивать и использовать нужное подмножество данных в списке. В отфильтрованном списке выводятся на экран только те строки, которые содержат определенное значение или отвечают определенным критериям, при этом другие строки скрываются. Для фильтрации данных используются команды Автофильтр и Расширенный фильтр пункта Фильтр меню Данные.

Автофильтр

Команда Автофильтр устанавливает кнопки скрытых списков (кнопки со стрелками) непосредственно в строку с именами столбцов (рис.28). С их помощью можно выбирать записи базы данных, которые следует вывести на экран. После выделения элемента в открывшемся списке, строки, не содержащие данный элемент, будут скрыты. Например, если в скрытом списке поля Цена выбрать 99 грн., то будут выведены только записи, у которых в поле Цена содержится значение 99 грн.

 

Рис.28

Если в поле списка выбрать пункт Условие …, то появится окно Пользовательский автофильтр (рис.29). В верхнем правом списке следует выбрать один из операторов (равно, больше, меньше и др.), в поле справа – выбрать одно из значений. В нижнем правом списке можно выбрать другой оператор, и в поле по левую сторону – значение. Когда включен переключатель И, то будут выводиться только записи, удовлетворяющие оба условия. При включенном переключателе ИЛИ будут выводиться записи, удовлетворяющие одному из условий. Например, в окне на рис.29 введены условия для вывода записей по изделиям с ценой больше 99 грн. и меньше 187 грн.

Для вывода нескольких записей с самым большим или самым малым значением по любому полю следует в скрытом списке поля выбрать пункт Первые 10. В диалоговом окне Наложение условия по списку в первом поле со счетчиком необходимо выбрать количество записей, а в поле справа выбрать – наибольших или наименьших.

Чтобы вывести все данные таблицы, необходимо вызвать команду Отобразить все или отменить команду Автофильтр меню Данные, подменю Фильтр.

 

Рис.29

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

Команда Расширенный фильтр позволяет фильтровать данные с использованием диапазона критериев для вывода только записей, удовлетворяющих определенным критериям (рис.30). При повторной фильтрации будут просматриваться все строки, и скрытые, и открытые. Значение переключателей и полей окна Расширенный фильтр следующие:

 

Рис.30

фильтровать список на месте – переключатель, скрывающий строки, которые не удовлетворяют указанному критерию;

скопировать результат в другое место – копирует отфильтрованные данные на другой рабочий лист или на другое место на этом же рабочем листе;

Исходный диапазон – поле, определяющее диапазон, который содержит список, подлежащий фильтрации;

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

Поместить результат в диапазон – поле, определяющее диапазон ячеек, в который копируются строки, удовлетворяющие определенным условиям; это поле активно только в том случае, если выбран переключатель скопировать результат в другое место;

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

Для установления сложных критериев необходимо:

· вставить несколько строк в верхней части рабочего листа;

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

· при использовании критериев сравнения, имена критериев должны быть идентичны именам столбцов, которые проверяются;

· в строках, расположенных под строкой с именами проверяемых столбцов, ввести критерии, которым должны соответствовать ячейки проверяемых столбцов;

· выбрать в меню Данные пункт Фильтр, затем – Расширенный фильтр, и в диалоговом окне ввести условия фильтрации.

 

Рис.31

Для объединения критериев с помощью условного оператора И следует указать критерии в одной и той же строке, а для объединения критериев с помощью условного оператора ИЛИ следует ввести критерии в разных строках. Например, диапазон критериев на рис.31 выводит на экран все записи, имеющие в столбце Цена значения больше 50 и меньше 200.

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

Чтобы снова вывести все записи следует в меню Данные выбрать пункт Фильтр, затем пункт Отобразить все.

Итоги

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

· по какому полю (полям) выполнять группировку (сортировку);

· над значениями какого поля (полей) выполнять итоговые операции;

· какие итоговые операции следует применить

Для вычисления итогов используется команда меню Данные-Итоги.

MS Excel позволяет автоматически выполнять подведение итогов двух видов:

· простых промежуточных итогов (список предварительно сортируется по одному полю);

· вложенных промежуточных итогов (список предварительно сортируется по нескольким полям).

Вычисление простых итогов с использованием одной операции для различных полей:

1. Выполнить сортировку по тому полю, значения которого должны быть объединены в группу.

2. Установить курсор в любую ячейку списка.

3. Меню Данные-Итоги. Заполнить элементы появившегося диалогового окна.

Вычисление простых итогов с использованием различных операций для полей:

1. Выполнить сортировку по тому полю, значения которого должны быть объединены в группу.

2. Установить курсор в любую ячейку списка.

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

Вычисление вложенных итогов:

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

2. Установить курсор в любую ячейку списка.

3. Выполнить итоги столько раз, сколько задано операций для внешней группы записей.

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

Для того, чтобы отменить итоги и восстановить список, используется команда меню Данные-Итоги, командная кнопка Убрать все диалогового окна Промежуточнеы итоги.

Консолидация

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

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

Существует два основных метода консолидации данных – консолидация по расположению и консолидация по категориям (заголовкам).

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

При консолидации по категории используются заголовки строк и столбцов. Операция выполняется над данными, расположенными в строках и столбцах с одинаковыми названиями. Консолидация по категориям предоставляет большую свободу для организации данных в исходных диапазонах.

 

Выполнение консолидации:

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

2. Меню Данные-Консолидация. При этом появляется окно, в которое необходимо ввести следующие данные и нажать <ОК>:

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

Замечание 2: адрес консолидируемого диапазона удобнее вводить указанием мыши, для этого перейти на нужный лист и обвести мышью нужный диапазон. Для занесения адреса в список диапазонов нажать кнопку Добавить. Ввод адреса и добавление его в список выполняется столько раз, сколько имеется консолидируемых списков. Если был введен неверный адрес диапазона, то для удаления его из списка нажать кнопку Удалить.

Замечание 3: если при изменении данных в исходных таблицах следует автоматически изменять данные в отчете, то включить флажок Создавать связи с исходными данными. При этом результаты консолидации должны располагаться на отдельном листе, иначе будет выдано сообщение об ошибке.

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

Практическая часть

Создайте на листе Ведомость таблицу (рис. 32).

Рис. 32

Зададим контроль значений для поля Код поставщика. Выделив контролируемые значения выберем команду меню Данные – Проверка и зададим значения как на рис. 33.

 

 

Рис. 33

 

После этого, невозможно будет ввести значение, кроме указанных.

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

Рис. 34

Для простоты дальнейшей работы присвоим имена справочникам. Для этого нужно выделить диапазоны с данными и выполнить команду Вставка – Имя – Присвоить (рис. 35). Набрав имя щелкнуть Добавить.

Рис. 35

Аналогично присвойте имя справочнику Товары.

Подставлять данные из справочников по коду или по имени товара можно с помощью функций ПРОСМОТР или ВПР (Вертикальный просмотр). Разберем подробнее обе функции. Основное требование для использования этих функций: данные в столбце по которому будет производиться поиск (в нашем случае это первые столбцы) должны быть отсортированы в порядке возрастания!

Например, подставим данные по поставщикам с использованием функции ПРОСМОТР. Установите курсор в первую ячейку столбца Поставщики и вставьте функцию ПРОСМОТР (рис. 36).

Рис. 36

Данная функция имеет два формата. Нам удобнее работать со вторым списком аргументов: искомое_значение; массив.

Задайте аргументы, как показано на рис. 37. Искомое значение (код поставщика в основной таблице – для 4 строки B4)– будет найдено в первом столбце указанного массива (Поставщики) и значение из второго столбца этого же массива.

После того, как функция будет скопирована на весь столбец он будет заполнен именами поставщиков.

 

Рис. 37

С использованием функции ПРОСМОТР возможно заполнить и столбец Цена, однако мы воспользуемся другой функцией – ВПР, которая имеет более широкие возможности. Установите курсор в первую ячейку столбца Цена и вставьте функцию ВПР (рис. 38).

Рис. 38

Аргументы функции:

Искомое значение - ячейка с наименованием товара,который надо найти в Таблице Товары.

Номер столбца – из него данные будут подставлены в поле Цена (в нашем случае – это 2-ой столбец справочника)

Интервальный просмотр – поставим 0.

Нажимаем ОК, и любым способом копируем функцию в другие ячейки поля Цена.

Принципиальным отличием функции ВПР является возможность подставлять данные из справочника, в котором больше, чем 2 столбца, например, справочник Поставщики мог содержать Адрес, р/счет и др., а справочник Товары – артикул, страну-производитель и др.

Кроме того, если данные отсутствуют в справочнике, то функция ПРОСМОТР вернет значение из соседней строки, а это неверно. У функции ВПР аргумент Интервальный_просмотр = 0 позволяет избежать такой ситуации.

 

Создадим возможность задавать единицы измерения из выпадающего списка. Выберем пункт меню Данные – Проверка и зададим условия как на рис. 39.

Рис. 39

После этого заполните Единицы измерения значениями.

Заполните оставшиеся столбцы как на рис 40, в столбце Стоимость вставьте соответствующую формулу (Количество*Цена). С помощью Автосуммирования вычислите Итого.

 

Рис. 40

 

Для получения отчетов разной формы удобно использовать Сводные таблицы (Данные – Сводная таблица). Перетаскивая различные поля из Списка полей в Область строк и Область столбцов можно получать таблицы требуемой формы. (рис. 41). Заметим, что предварительно нужно выделить таблицу с заголовками столбцов, затем согласиться со всеми предложениями Мастера Сводной таблицы.

Рис. 41

Перетаскивая поле Товар в Область строк, поле Дата в Область столбцов, поле Сумма в Область элементов данных мы получим динамику получения различных товаров (рис. 42).

Рис. 42

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

· для поставщиков – суммы поставок по датам;

· для товаров – количество поставок по датам.

 

4 Контрольные вопросы

1. Выполнение сортировки по одному полю.

2. Какое действие необходимо всегда выполнять перед подведением итогов и зачем?

3. Подведение простых итогов с одной операцией по одному полю.

4. Подведение простых итогов с несколькими операциями по одному полю.

5. Подведение вложенных итогов.

6. Как удалить итоги.

7. Понятие консолидации.

8. Виды консолидации, их отличие.

9. Выполнение консолидации.

10. Фильтраяция данных.

11. Для чего используются формы данных?

 

Лабораторная работа №5: «Работа в среде СУБД MS Access»

1.Теоретический материал

База данных — это совокупность данных, организованная с определенной целью. Здесь слово организованная означает, что указанная совокупность включает данные, которые сохра­няются, имеют определенный формат (отформатированы), к ним может быть обеспечен доступ (доступны), и они могут быть представлены потребителю информации в приемлемом виде (репрезентативны). Упоминанием в определении цели подчеркивается, что состав данных должен соответ­ствовать некоторой задаче (или классу задач): с одной стороны, в базе не должны содержаться данные, не имеющие отношения к задаче, а с другой — должна содержаться вся информация, касающаяся задачи.

Система управления базой данных (СУБД) служит средством, с помощью кото­рого прикладные программы или пользователи работают с данными базы. Примером СУБД является MS Access.

Главными элементами модели данных являются объ­екты и отношения. Объекты часто представляют в виде существительных, а отношения — в виде глаголов.

Объекты представляют вещи, которые пользователи считают важными в моделируемой нами части реальности. Примерами объектов могут быть люди, автомобили, деревья, дома, молотки и книги. Это конкретные объ­екты. Концептуальными объектами будут компании, навыки, организации, проекты товаров, деловые операции, штатное расписание.

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

Допустим, что некто по имени Иванов Иван Иванович является элементом объ­ектного множества ЧЕЛОВЕК. В компьютерной реализации этого объектного множества Иванов будет обозначаться некоторым ключом, допустим, «13948226». Его имя (Иванов Иван Иванович), номер страховки, дата рож­дения, рост, вес и другая информация будет записана в виде данных и связана в базе данных с ключом, представляющим его. Пользователи увидят только эти данные. Они никогда не встретят число 13948226 в связи с Ивановым. Но система будет пользоваться этим ключом, ассоциированным с Ивановым, во всех возмож­ных многочисленных отношениях в базе данных.

Первичные ключи решают проблемы, связанные с традиционными ти­пами ключей. Например, во многих системах очень сложно изменить значе­ние ключа. Номер страховки часто используется в качестве ключа для одно­значного обращения к информации о человеке. Что происходит, если номер страховки введен неверно? Поскольку этот номер имеет важное значение вне системы, его необходимо исправить. Но это может привести к огромному ко­личеству проблем в базе данных, так как на значение номера страховки мо­гут быть многочисленные ссылки. Этой проблемы можно избежать путем использования идентификаторов, поскольку они определяются системой и не имеют смысла вне ее. Если номер страховки Иванова Ивана Ивановича введен не­верно, мы просто исправим его. Это изменение не повлияет больше ни на что в базе данных, так как больше ничего не ссылается на номер страховки.

Мощность - максимальное количество элементов одного объектного множества, связанных с одним элементом другого объектного множества.

Например, в одной строке продаж может быть только наименование одного товара, т.е. от продаж к товару мощность отношения 1:1; однако одно и то же наименование товара может встречаться и в других строках продаж, т.е. мощность отношения товар-продажи равен 1:∞.