Excel предоставляет возможность подвести промежуточные и окончательные итоги по полям БД, вставив необходимые строки для подсчитанных сумм . Кроме того, Excel может отображать БД в режиме структуры, что позволяет выводить на экран только промежуточные итоги.
Добавление итогов осуществляется после предварительной сортировки БД по нужному полю (например, по номеру группы) следующей последовательностью действий:
| Группа | Данные | Всего |
| 219 | Среднее по полю Высш.матем. | 4 |
| Среднее по полю Информ. | 3,666666667 | |
| Среднее по полю История Украины | 3,333333333 | |
| Среднее по полю Физика | 4 | |
| 221 | Среднее по полю Высш.матем. | 3,666666667 |
| Среднее по полю Информ. | 4,666666667 | |
| Среднее по полю История Украины | 4,333333333 | |
| Среднее по полю Физика | 4 | |
| 223 | Среднее по полю Высш.матем. | 4 |
| Среднее по полю Информ. | 3,666666667 | |
| Среднее по полю История Украины | 4 | |
| Среднее по полю Физика | 4 | |
| 235 | Среднее по полю Высш.матем. | 3,333333333 |
| Среднее по полю Информ. | 3,666666667 | |
| Среднее по полю История Украины | 4,333333333 | |
| Среднее по полю Физика | 3,333333333 | |
| 241 | Среднее по полю Высш.матем. | 3,666666667 |
| Среднее по полю Информ. | 4,333333333 | |
| Среднее по полю История Украины | 4 | |
| Среднее по полю Физика | 4 |
Добавление итогов осуществляется после предварительной сортировки БД по нужному полю (например, по номеру группы) следующей последовательностью действий:
- Выбрать Данные –- Итоги, в результате чего БД будет автоматически выделена и на экране появится диалоговое окно « Промежуточные итоги ».
- В строке «При каждом изменении в » указать поле, при изменении значения которого следует подводить промежуточный итог, для чего щелкнуть на стрелке справа и выбрать нужное название поля ( например, «Группа» для нашей БД).
- В строке «Операция» , щелкнув на стрелке справа , выбрать в появившемся перечне возможных операций необходимую ( например, «Среднее» ).
- Для выполнения той же операции с данными в других полях необходимо отметить их названия, щелкнув в списке строки «Добавить итоги по»(например, названия всех предметов и поля Ср.балл ).
- Убедиться, что параметр «Заменить текущие итоги» установлен и щелкнуть на кнопке ОК.
Excel выполнит указанную операцию и добавит промежуточные итоги в тех столбцах с данными, на основании которых подсчитывались результаты. В конец БД будет добавлена строка с общим итогом по всей БД (см. Рис.1.1).
Excel можно перевести в режим структуры , чтобы отобразить на экране только часть итоговой информации, что очень важно при работе с БД, особенно большими. Щелчками на маленьких кнопках со знаком «минус» и с цифрами «1», «2», «3» можно прятать или выводить на экран различные уровни структуры .БД. Чтобы убрать данные по группе 219 в приведенной БД, достаточно щелкнуть по кнопке со знаком «минус» слева от строки 33 с промежуточными результатами по этой группе (см. Рис.1.1). Чтобы убрать с экрана все, кроме промежуточных и общих итогов , нужно щелкнуть по кнопке второго уровня (с цифрой «2»).Чтобы убрать все, кроме общего итога, нужно щелкнуть по кнопке «1». Чтобы возвратить на экран все записи, нужно щелкнуть по кнопке «3».
В Excel имеется 12 функций , используемых для анализа данных из баз данных. Каждая из этих функций, имеющих обобщенное название БДФункция, использует три аргумента: база_данных, поле и критерий
БДФункция(база_данных;поле;критерий)
Эти три аргумента ссылаются на интервалы ячеек на рабочем листе, которые используются данной функцией.
База_данных – это интервал ячеек, формирующих БД.
Поле определяет столбец, используемый функцией. Аргумент поле может быть задан как текст с названием столбца в двойных кавычках (например, «Информ.»)или как число, задающее положение столбца в БД (например, 7 для того же поля ).
Критерий – это ссылка на интервал ячеек , задающих условия для функции. Функция возвращает данные из списка, которые удовлетворяют условиям, определенным диапазоном критериев. Диапазон критериев включает копию названия столбца, для которого выполняется подведение итогов. Ссылка на критерий может быть введена как интервал ячеек (например, B24:B25).
В приведенном примере подсчитывается количество оценок каждого вида по результатам экзамена по информатике в рассматриваемой БД. В ячейках G19:G22 использованы БДФункции БСЧЕТ, которая просматривает в БД в интервале ячеек A1:K16 записи в 7-ом столбце с именем «Информ.» и подсчитывает количество пятерок по критерию в диапазоне B24:B25, четверок – в C24:C25, троек и двоек – в D24:D25 и E24:E25.
| A | B | C | D | E | F | G |
| 19 | Информатика | К-во 5 | =БСЧЁТ(A1:K16;7;B24:B25) | |||
| 20 | К-во 4 | =БСЧЁТ(A1:K16;7;C24:C25) | ||||
| 21 | К-во 3 | =БСЧЁТ(A1:K16;7;D24:D25) | ||||
| 22 | К-во 2 | =БСЧЁТ(A1:K16;"Информ.";E24:E25) | ||||
| 23 | ||||||
| 24 | Информ. | Информ. | Информ. | Информ. | ||
| 25 | =5 | =4 | =3 | =2 | ||
| 26 |
С помощью БДФункций ДМАКС и ДМИН можно найти максимальный и минимальный элемент столбца для записей, удовлетворяющих критерию. Функции БДСУММ и ДСРЗНАЧ позволяют найти сумму и среднее значения элементов указанного поля, соответствующих записям, которые удовлетворяют критерию.
Каждый раз, когда две программы Windows работают с общими данными, используется Буфер обмена. Любой выделенный объект, например блок текста в Word-документе или интервал в электронной таблице либо другой фрагмент данных этих программ, может быть вырезан или скопирован щелчком мыши на кнопке Удалить в буфер или Копировать в буфер на панели инструментов. После этого Windows хранит эту информацию в памяти компьютера, пока пользователь не укажет место вставки и не вставит копию в активный документ. При этом неважно, принадлежат ли источник копирования и целевой документ к одному типу или это различные документы, например, таблица Excel и текст Word,- процедура выглядит совершенно одинаково. Следует помнить, что в любой момент времени Буфер обмена способен хранить только один объект.
При работе с БД возникает необходимость создать новую БД, содержащую часть данных из одной БД и часть из другой или просто дополнительные данные, которые не нужны были при работе с исходной БД. Но при этом необходимо, чтобы все изменения в исходной БД автоматически учитывались во вновь созданной БД и не только в БД, но и в любом другом документе Microsoft Office, использующем как составную часть данные электронной таблицы. Тогда мало скопировать через буфер обмена данные из одной БД в другую, необходимо их связать. Создание связи между документом и электронной таблицей начинается с копирования данных в Буфер обмена. Однако вместо использования команды Вставить для вставки из Буфера обмена необходимо использовать команду Специальная вставка.
Порядок действий:
- В электронной таблице выделить интервал, который необходимо скопировать.
- Щелкнуть правой кнопкой мыши на выделенной области и в появившемся контекстном меню выбрать Копировать (или щелкнуть на панели инструментов кнопку копирования).
- Переключиться в документ, в который следует скопировать данные выделенной области (это может быть новая БД или документ Word).
- Выбрать Правка – Специальная вставка, в результате чего появится диалоговое окно «Специальная вставка».
- Выбрать опцию Связать, щелкнув мышью на нужном положении переключателя.
- Убедившись, что в поле Как выделен подсветкой пункт Лист Microsoft Excel Объект, щелкнуть на кнопке OK.
В результате на экране появится копия интервала из электронной таблицы. Над этим материалом нельзя выполнять операции Word, хотя он будет похож на Word-таблицу после вставки в Word-документ.
Для примера создадим БД, содержащую поля «№», «Фамилия», «Имя», «Отчество», «Группа» и «Стипендия», такие же, как в нашей БД. Дополним новую БД полями «Дата рождения» и «Возраст», задав соответствующую формулу для вычисления возраста: (СЕГОДНЯ() – «Дата рождения»)/365 (лет).
Возраст, таким образом, будет меняться, с каждым днем увеличиваясь на 1/365, так как выражение в скобке определяет разность между днем сегодняшним и датой рождения в днях. Можно ввести в БД еще одно поле, вычисляющее возраст в днях.
Эти все операции выполняются по правилам действий над датами в Excel. Свяжем новую БД с исходной, чтобы можно было учитывать любые изменения, возникающие в исходной БД в общих полях, без дополнительной корректировки новой БД.
Теперь изменим в исходной БД оценку студента Горца по информатике на «5», что повысит его средний балл и обеспечит стипендией в размере 12гр., а также скорректируем имя и отчество студента Прокопенко: как видим, данные изменились автоматически как в новой БД, так и в данном Word-документе.
Литература:
1. Методические указания к проведению вычислительной практики №1
2. Конспект лекций по курсу «Информатика и компьютерная техника».
3. Руденко В.Д., Макарчук О.М., Патланжоглу М.О. Практичний курс інформатики. /За ред. Мадзігона В.М.-К.: Фенікс, 1997.-307 с.
На этапе проектирования базы данных задаем структуру базы, определяем количество, наименование и типы полей базы, определяем для вычисляемых данных формулы, по которым они вычисляются.