Построение графиков и диаграмм.

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

2 На гистограмме ввести для каждого сектора название, объясняющее, какой показатель отражает этот сектор.

3 Построить столбчатую гистограмму, отображающую распределение успеваемости по видам занятий одновременно в первом и втором семестрах. Если не получится "собрать" гистограмму с двух листов, попробовать предварительно создать отдельную таблицу на новом листе, скопировав туда нужные данные. При операции копирования методом СПЕЦИАЛЬНАЯ ВСТАВКА скопируйте ЗНАЧЕНИЯ ячеек. В противном случае получите сообщение об ошибке.

 

IV. Требования к защите.

ЗНАТЬ ответы на контрольные вопросы.

Лабораторная работа № 7

Тема

Функции работы с базами данных в MS EXCEL

Цель: изучение функций работы с базами данных.

I. Теоретическая часть.

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

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

В программе Microsoft Excel имеется 12 функций рабочего листа, используемых для анализа данных из списков или баз данных. Каждая из этих функций имеет обобщенное название БДФункция, использует три аргумента: база_данных, поле и критерий. Эти три аргумента ссылаются на интервалы ячеек на рабочем листе, которые используются данной функцией. Обращение к БД-функциям выполняется через Мастер функций (категория Работа с базой данных) или через ввод названия функции и ее параметров с клавиатуры. Синтаксис функции имеет вид:

БДФункция(база_данных;поле;критерий)

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

поле определяет столбец, используемый функцией. Поля данных в списке должны содержать идентифицирующее имя в первой строке. Аргумент поле может быть задан как текст с названием столбца в двойных кавычках: "Алгебра" или "Информатика" в приведенном ниже примере базы данных, или как число, задающее положение столбца в списке: 1 – для первого поля (Студент), 2 – для второго поля (История), и так далее;

критерий – это ссылка на интервал ячеек, задающих условия для функции. Функция возвращает данные из списка, которые удовлетворяют условиям, определенным диапазоном критериев. Диапазон критериев включает копию названия столбца, для которого выполняется подведение итогов, в списке. Ссылка на критерий может быть введена как интервал ячеек: A1:F2 в приведенном ниже примере базы данных, или как имя интервала: "Критерии".

При этом следует иметь в виду следующее:

1) Любой интервал, который содержит, по крайней мере, одно название столбца и, по крайней мере, одну ячейку под названием столбца с условием, может быть использован как аргумент-критерий БДФункции. Например, если интервал G1:G2 содержит название столбца “Программирование” в ячейке G1 и величину 10 000 в ячейке G2, то можно определить интервал “СоответствуетПрибыли” и использовать это имя как аргумент критерий в функции баз данных.

2) Несмотря на то, что диапазон критериев может располагаться в любом месте таблицы, не стоит помещать его внизу списка. Это связано с тем, что данные, добавляемые в список командой Форма меню Данные, вставляются с первой строки после списка. Если эта строка не пустая, Microsoft Excel не сможет добавить данные в список.

3) Диапазон критериев не должен перекрываться со списком.

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

Некоторые функции базы данных

1. ДСРЗНАЧ(база_данных;поле;критерий) –возвращает среднее значение выбранных фрагментов базы данных.

2. БСЧЁТ(база_данных;поле;критерий) –подсчитывает количество ячеек, содержащих числа, в столбце списка или базы данных указанном поле всех записей, удовлетворяющих заданным условиям. Аргумент поле не является обязательным. Если аргумент поле опущен, то функция БСЧЁТ подсчитывает количество записей в базе данных, отвечающих критериям.

3. БСЧЁТА(база_данных;поле;критерий) –подсчитывает все непустые ячейки в столбце списка или базы данных, которые удовлетворяют заданным условиям.

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

5. ДМАКС(база_данных;поле;критерий) –возвращает максимальное значение среди выделенных записей базы данных.

6. ДМИН(база_данных;поле;критерий) – возвращает минимальное значение среди выделенных записей базы данных.

7. БДПРОИЗВЕД(база_данных;поле;критерий) –перемножает значения определенных полей записей базы данных, удовлетворяющих условию.

8. БДСУММ(база_данных;поле;критерий) – суммирует числа в поле столбца записей базы данных, удовлетворяющих условию.

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

1. Что представляет собой функция?

2. Что такое база данных?

3. Какой синтаксис имеют функции баз данных?

4. Назвать некоторые функции базы данных.

 

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

1. Создать таблицу успеваемости группы студентов – базу данных по результатам сессии. Каждая запись содержит информацию об одном студенте. Диапазон A5:E11 имеет название БазаДанных, а диапазон A1:F3 – Критерий.

 

 

	А	B	C	D	E	F
	Студент	История	Алгебра	Информатика	Программирование	История
		>2				<5
	Г*
	Студент	История	Алгебра	Информатика	Программирование
	Аникин
	Букин
	Гурин
	Грушин
	Титова
	Яшкин

 

2. Вычислить функции:

• БСЧЁТ(БазаДанных;"Алгебра";A1:F2) = 3 – функция просматривает записи о студентах, которые сдали историю на 3 и 4 и подсчитывает количество числовых полей Алгебра в этих записях.

• БСЧЁТА(БазаДанных;"Программирование";A1:F2) = 3 – функция просматривает записи о студентах, которые сдали историю на 3 и 4 и подсчитывает количество непустых полей Программирование в этих записях.

• ДМАКС(БазаДанных;"Программирование";A1:A3) = 5 – определяется лучшая оценка по программированию, полученная студентами в сессию.

• ДМИН(БазаДанных;"Программирование";A1:B2) = 3 – это низшая оценка по программированию, полученная студентами, которые сдали историю.

• БДСУММ(БазаДанных;"Программирование";A1:A2) = 24 – это сумма всех оценок по программированию.

• БДСУММ(БазаДанных;"Программирование";A1:F2) = 12 – это сумма оценок по программированию, полученных студентами, которые сдали историю на 3 и 4.

• БДПРОИЗВЕД(БазаДанных;"Информатика";A1:B2) = 900 – это произведение оценок по информатике, полученных студентами, которые сдали историю.

• ДСРЗНАЧ(БазаДанных;"Информатика";A1:B2) = 4 – это средняя оценка по информатике для студентов, сдавших историю.

• ДСРЗНАЧ(БазаДанных;3;БазаДанных) = 3.666667 – это средняя оценка по алгебре.

• БИЗВЛЕЧЬ(БазаДанных;"Информатика";Критерий) – возвращает значение ошибки #ЧИСЛО!, потому что критерию удовлетворяет более чем одна запись.

3. Выполнить задание:

1) определить:

• число студентов, получивших хотя бы одну тройку;

• найти студента, получившего по информатике 2, и вывести на экран полную информацию по нему;

• найти сумму стипендий студентов, получивших по информатике 4 и по физике 5.

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

3) Записать фамилии студентов в алфавитном порядке.

 

IV. Требования к защите.

ЗНАТЬ ответы на контрольные вопросы.

 

Лабораторная работа №8

Тема