Практическое занятие 14
Тема: Работа с базой данных в MS Excel.
Порядок выполнения работы
Задание 1. Cоздание формы для базы данных
1.1. Оформить строку заголовка столбцов базы данных и внести следующей строке информацию о первом объекте:
При этом необходимо отформатировать каждую ячейку: установить текстовый и числовой форматы данных. В вычисляемые ячейки вводятся расчетные формулы. Так в приведенном примере в столбцы «Ср.балл» и «Стипендия» введены формулы:
| Ср.балл | Стипендия |
| =СРЗНАЧ(D2:G2) | =ЕСЛИ(H2<4;0;ЕСЛИ(H2>4;600)) |
Примечание. В столбцы D:G вводятся оценки, а в ячейку H 2 – средний балл. Стипендия вычисляется по формуле: если Ср.балл меньше 4, стипендия не начисляется (=0); если Ср.балл больше 4 начисляется стипендия 600р.
Добавление инструмента Формы.
Чтобы добавить инструмент Форма данных 
на панель Быстрого доступа, выполните следующие действия:
Щелкните стрелку, расположенную рядом с панелью быстрого доступа, и выберите пункт Другие команды.
В списке выбрать команды из выберите Все команды и в списке выберите кнопку формы .
Нажмите кнопку Добавить, а затем нажмите кнопку ОК.
На панели Быстрого доступа нажмите кнопку формы .
Создание формы базы данных.
Выделить обе строки (с именами полей и формулами) и выбрать команду Форма из панели Быстрого доступа. После этого Excel анализирует введенную в первые две строки информацию и создает форму с данными, в которой сверху вниз перечислены все имена полей в левой части и элементы первой записи в текстовых окнах рядом с ними. Текстовые окна у вычисляемых полей отсутствуют.
Важно! Убедитесь, что в диапазоне данных нет пустых строк.
Форму данных можно создать на основе диапазонов или таблиц, в которых содержится не более 32 столбцов. По возможности уменьшите количество столбцов. В итоге создается форма для ввода информации в базу данных
Форму данных можно использовать для добавления, поиска, изменения и удаления строк в диапазоне или таблице.
Использование формы данных
В форме данных нажмите кнопку Добавить.
Введите данные для новой строки.
Переход к следующему полю производится с помощью клавиши TAB, а к предыдущему — с помощью сочетания SHIFT + TAB.
Указав нужные данные, нажмите клавишу ВВОД. Строка будет добавлена в нижнюю часть диапазона или таблицы Таким образом создайте базу данных по образцу.
Обработка данных в БД.
Любая информация должна быть упорядочена. Расположите записи в алфавитном порядке фамилий.
Скопируйте лист и назовите Рейтинг. Для определения рейтинга студентов список группы нужно отсортировать по убыванию среднего балла.
Для сортировки элементов в БД необходимо выполнить такие действия:
- Щелкнуть на любой ячейке БД.
- Выбрать команду Сортировка из меню Данные, в результате чего Excel раскроет диалоговое окно Сортировка диапазона.
- Щелкнуть на стрелке в группе «Сортировать по» и выбрать главный ключ сортировки (поле, по которому должна быть выполнена сортировка).
- Выбрать восходящее «по возрастанию» или нисходящее «по убыванию» упорядочение, щелкнув по переключателю справа.
- Если нужно выполнить еще одно упорядочивание внутри первого, щелкнуть на стрелке в следующей группе «Затем по» и выбрать второе поле сортировки и затем – восходящий или нисходящий порядок.
- При необходимости дальнейшего иерархического упорядочения выбрать поле и порядок упорядочения в последнем списке «В последнюю очередь, по».
- Для отказа от выбора полей и порядка сортировки достаточно, не выходя из этого диалогового окна, снова открыть список полей и выбрать «(не сортировать)».
- Щелкнуть на ОК или нажать Enter.
Использование фильтра для поиска данных
Удобным инструментом поиска по критериям являются фильтры. Фильтр обрабатывает весь список в электронной таблице и отображает на экране только те данные, которые требуется. Преимущество этого подхода заключается в том, что с такой выборкой можно работать автономно как с новой таблицей, не загружая экран избыточной информацией.
Для активизации нужного фильтра следует выполнить такие действия:
- Щелкнуть где-либо в БД.
- Выбрать Данные – Фильтр, в результате чего Excel добавит раскрывающийся список к каждой ячейке с именем поля в строке заголовков.
- Щелкнуть на кнопке списка поля, по значению которого нужно выполнить отбор записей, и выбрать один из предложенных вариантов фильтра, щелкнув по нему.
После этого Excel покажет только записи, содержащие в этом поле выбранное значение (все остальные будут временно скрыты). Отфильтрованные записи можно скопировать на другой лист рабочей книги или выдать на печать. Для этого нужно просто:
- выделить диапазон;
- выбрать команду Копировать (или нажать <Ctrl+C>);
- переместить табличный курсор в первую ячейку таблицы на новом листе и выбрать команду Вставить (или нажать <Ctrl+V>);- нажать Enter.
После копирования отфильтрованных записей можно восстановить отображение всех записей БД, щелкнув на кнопке списка, по которому выполнялась фильтрация (кнопка выделена голубым цветом), и выбрав в раскрывшемся списке пункт «(Все)».
Создание сводных таблиц
Одним из наиболее мощных средств Excel по работе с БД являются сводные таблицы, которые полезны как для анализа, так и для обобщения информации, хранящейся в БД, на рабочих листах, во внешних файлах. Сводные таблицы позволяют выводить информацию с различной степенью детализации. Для создания сводных таблиц в Excel имеется специальный инструмент Мастер сводных таблиц, в зависимости от версии позволяющий выполнять работу в 3 или в 4 шага.
Разумеется, создавать сводные таблицы имеет смысл только по БД, содержащим значительный объем информации. Расширим первоначальную БД хотя бы до 15 записей и введем новое поле «Группа». Упорядочим список по алфавиту и скорректируем порядок, т.е. данные в поле «№».Получим такую БД:
| № | Фамилия | Имя | Отчество | Группа | Высш. матем. | Информ. | История Укр. | Физика | Ср.балл | Стипендия |
| Абдельгадир | Мусса | Ибрагимович | 3,75 | |||||||
| Антонова | Вера | Борисовна | 3,5 | |||||||
| Бирюкова | Галина | Олеговна | 4,5 | |||||||
| Борисова | Нина | Павловна | 4,75 | |||||||
| Вовченко | Александра | Александровна | ||||||||
| Горец | Анатолий | Владимирович | 3,75 | |||||||
| Грант | Анатолий | Семенович | 4,75 | |||||||
| Дмитренко | Петр | Павлович | 3,25 | |||||||
| Дмитренко | Виталий | Игоревич | 4,25 | |||||||
| Замовский | Эдуард | Федорович | 3,25 | |||||||
| Иванов | Иван | Иванович | ||||||||
| Клочко | Георгий | Константинович | ||||||||
| Новиков | Олег | Валентинович | 3,25 | |||||||
| Прокопенко | Виталий | Викторович | 3,5 | |||||||
| Соловьев | Руслан | Анатольевич |
Для такой БД можно составить сводную таблицу стипендий или среднего балла по каждому предмету и по всем экзаменам для каждой группы отдельно. Последовательность действий при создании сводной таблицы должна быть следующей:
- Выполнить команды Данные ––Сводная таблица.
- В первом диалоговом окне «Мастер сводных таблиц – шаг 1 из 4 » установить переключатель в положение, определяющее, где находятся данные для сводной таблицы: в нашем случае следует щелкнуть на первом положении переключателя «В списке или базе данных Microsoft Excel ».
- Нажать кнопку Далее, в результате чего появится второе диалоговое окно «Мастер сводных таблиц- шаг 2 из 4».
- В поле «Диапазон» указать, в каком диапазоне находятся исходные данные для сводной таблицы (в нашем случае это $A$1:$K$16) и нажать кнопку Далее.
- В третьем диалоговом окне «Мастер сводных таблиц – шаг 3 из 4» необходимо указать структуру сводной таблицы, т.е. определить, данные какого поля должны использоваться в качестве заголовков строк и каких – в качестве заголовков столбцов, для чего перетащить их названия, представленные в окне в виде кнопок, в соответствующие области «Строка» (в нашем случае –«Группа») и «Столбец» (в нашем случае – ничего).
- В этом же диалоговом окне в область «Данные» перетащить название поля (или полей), данные которого подлежат обработке (в нашем случае – «Стипендия» или «Средний балл» или названия всех предметов для определения среднего балла по каждому экзамену).
- Задать правило, по которому должна осуществляться обработка, щелкнув дважды по кнопке, перемещенной в область «Данные», и выбрав в появившемся диалоговом окне «Вычисление поля сводной таблицы» нужную операцию (для примера о стипендии – «Сумма», в других примерах – «Среднее»), в результате чего в поле «Имя» появятся названия операции и поля, по которому она будет выполняться.
- В диалоговом окне «Мастер сводных таблиц – шаг 4 из 4 » нужно задать некоторые параметры, определяющие вид сводной таблицы:
- в поле окна «Поместить таблицу в» указать адрес левой верхней ячейки таблицы на текущем листе или оставить поле пустым, чтобы таблица разместилась в начале нового рабочего листа;
- указать название сводной таблицы в поле «Название таблицы»;
- установить флажки «Общие итоги по столбцам» и «Общие итоги по строкам», если нужно;
- для создания дополнительной копии данных установить флажок «Сохранить данные с макетом таблицы»;
- установить флажок «Автоматически форматировать таблицу» для использования средств автоформата Excel.
- Для вывода сводной таблицы на экран после этого щелкнуть на кнопке Готово.
Для детализации данных сводной таблицы необходимо дважды щелкнуть на названии поля и в открывшемся диалоговом окне «Показать детали» указать, по какому полю необходимо выполнить детализацию.
| Сумма по полю Стипендия | ||
| Группа | Фамилия | Всего |
| Абдельгадир | ||
| Вовченко | ||
| Грант | ||
| 221 Всего | ||
| Горец | ||
| Иванов | ||
| Клочко | ||
| 223 Всего | ||
| Общий итог | ||
В приведенной сводной таблице выполнена детализация по полю «Группа 221» и по полю «Группа 223», по остальным группам детализация не задавалась.
Следующая сводная таблица дает возможность проанализировать результаты экзаменов по каждому предмету и сравнить успехи групп, детализация здесь не выполнялась, но при необходимости это может быть сделано.
В Excel 2000 Мастер сводных таблиц предлагает выполнение тех же действий по созданию сводных таблиц, но только с помощью трех диалоговых окон. Создание структуры и задание параметров сводной таблицы выполняется после нажатия кнопок Макет и Параметры в диалоговом окне «Мастер сводных таблиц – шаг 3 из 3».
Excel предоставляет возможность подвести промежуточные и окончательные итоги по полям БД, вставив необходимые строки для подсчитанных сумм. Кроме того, Excel может отображать БД в режиме структуры, что позволяет выводить на экран только промежуточные итоги.
Добавление итогов осуществляется после предварительной сортировки БД по нужному полю (например, по номеру группы) следующей последовательностью действий:
| Группа | Данные | Всего |
| Среднее по полю Высш.матем. | ||
| Среднее по полю Информ. | 3,666666667 | |
| Среднее по полю История Украины | 3,333333333 | |
| Среднее по полю Физика | ||
| Среднее по полю Высш.матем. | 3,666666667 | |
| Среднее по полю Информ. | 4,666666667 | |
| Среднее по полю История Украины | 4,333333333 | |
| Среднее по полю Физика | ||
| Среднее по полю Высш.матем. | ||
| Среднее по полю Информ. | 3,666666667 | |
| Среднее по полю История Украины | ||
| Среднее по полю Физика | ||
| Среднее по полю Высш.матем. | 3,333333333 | |
| Среднее по полю Информ. | 3,666666667 | |
| Среднее по полю История Украины | 4,333333333 | |
| Среднее по полю Физика | 3,333333333 | |
| Среднее по полю Высш.матем. | 3,666666667 | |
| Среднее по полю Информ. | 4,333333333 | |
| Среднее по полю История Украины | ||
| Среднее по полю Физика |
Добавление итогов осуществляется после предварительной сортировки БД по нужному полю (например, по номеру группы) следующей последовательностью действий:
- Выбрать Данные –- Итоги, в результате чего БД будет автоматически выделена и на экране появится диалоговое окно «Промежуточные итоги ».
- В строке «При каждом изменении в » указать поле, при изменении значения которого следует подводить промежуточный итог, для чего щелкнуть на стрелке справа и выбрать нужное название поля (например, «Группа» для нашей БД).
- В строке «Операция», щелкнув на стрелке справа, выбрать в появившемся перечне возможных операций необходимую (например, «Среднее»).
- Для выполнения той же операции с данными в других полях необходимо отметить их названия, щелкнув в списке строки «Добавить итоги по»(например, названия всех предметов и поля Ср.балл).
- Убедиться, что параметр «Заменить текущие итоги» установлен и щелкнуть на кнопке ОК.
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 | |
| Информатика | К-во 5 | =БСЧЁТ(A1:K16;7;B24:B25) | |||||
| К-во 4 | =БСЧЁТ(A1:K16;7;C24:C25) | ||||||
| К-во 3 | =БСЧЁТ(A1:K16;7;D24:D25) | ||||||
| К-во 2 | =БСЧЁТ(A1:K16;"Информ.";E24:E25) | ||||||
| Информ. | Информ. | Информ. | Информ. | ||||
| =5 | =4 | =3 | =2 | ||||
С помощью БДФункций ДМАКС и ДМИН можно найти максимальный и минимальный элемент столбца для записей, удовлетворяющих критерию. Функции БДСУММ и ДСРЗНАЧ позволяют найти сумму и среднее значения элементов указанного поля, соответствующих записям, которые удовлетворяют критерию.
1.4. Обмен данными.
Каждый раз, когда две программы Windows работают с общими данными, используется Буфер обмена. Любой выделенный объект, например блок текста в Word-документе или интервал в электронной таблице либо другой фрагмент данных этих программ, может быть вырезан или скопирован щелчком мыши на кнопке Удалить в буфер или Копировать в буфер на панели инструментов. После этого Windows хранит эту информацию в памяти компьютера, пока пользователь не укажет место вставки и не вставит копию в активный документ. При этом неважно, принадлежат ли источник копирования и целевой документ к одному типу или это различные документы, например, таблица Excel и текст Word,- процедура выглядит совершенно одинаково. Следует помнить, что в любой момент времени Буфер обмена способен хранить только один объект.
При работе с БД возникает необходимость создать новую БД, содержащую часть данных из одной БД и часть из другой или просто дополнительные данные, которые не нужны были при работе с исходной БД. Но при этом необходимо, чтобы все изменения в исходной БД автоматически учитывались во вновь созданной БД и не только в БД, но и в любом другом документе Microsoft Office, использующем как составную часть данные электронной таблицы. Тогда мало скопировать через буфер обмена данные из одной БД в другую, необходимо их связать. Создание связи между документом и электронной таблицей начинается с копирования данных в Буфер обмена. Однако вместо использования команды Вставить для вставки из Буфера обмена необходимо использовать команду Специальная вставка.
Порядок действий:
- В электронной таблице выделить интервал, который необходимо скопировать.
- Щелкнуть правой кнопкой мыши на выделенной области и в появившемся контекстном меню выбрать Копировать (или щелкнуть на панели инструментов кнопку копирования).
- Переключиться в документ, в который следует скопировать данные выделенной области (это может быть новая БД или документ Word).
- Выбрать Правка – Специальная вставка, в результате чего появится диалоговое окно «Специальная вставка».
- Выбрать опцию Связать, щелкнув мышью на нужном положении переключателя.
- Убедившись, что в поле Как выделен подсветкой пункт Лист Microsoft Excel Объект, щелкнуть на кнопке OK.
В результате на экране появится копия интервала из электронной таблицы. Над этим материалом нельзя выполнять операции Word, хотя он будет похож на Word-таблицу после вставки в Word-документ.
2.1.1. Структура базы данных
В соответствии с заданием база данных должна содержать следующую информацию:
| A | B | C | D | E | F | G | H | I | |
| ФИО | Бригада | Специальность | Оклад | Стаж | Премия | Начисленная сумма | Подоходный налог | К выплате | |
2.1.2. Определение формул для вычисляемой части базы данных. В создаваемой базе несколько вычисляемых полей Определим зависимости, по которым вычисляются значения в этих полях. Введем условные обозначения, которые будем использовать при составлении формул:
Премия – П;
Оклад – О;
Стаж- С;
Начисленная сумма - НС;
Подоходный налог - ПН;
1.Премия.
В соответствии с условиями премия начисляется сотрудникам, проработавшим определенное время на фирме. Для сотрудников со стажем от 2-х до 5-ти лет премия составит 15% оклада, со стажем более 5 лет 25% оклада.
При использовании Мастера функции логическое выражение для вычисления премии приобретет вид:
П= Если (С <= 2; 0; если(С>5;0.25*O;0.15*O)), причем коэффициент увеличения премии в бригаде №2 – 1,4
2.Начисленная сумма. Значение начисленной суммы определяется как результат сложения значения оклада и премии.
НС=П+О.
3.Подоходный налог.
Подоходный налог определяется в зависимости от величины начисленной суммы: не облагаются налогом суммы до 70 грв. включительно; при начисленной сумме более 250 грв. подоходный налог составляет 20% от суммы; в остальных случаях подоходный налог равен 10% от суммы.
ПН= 
При использовании Мастера функций логическое выражение примет вид:
ПН=Если(НС<=70;0;если(НС>250;0.2*НС;0,1*НС))
4. К выплате. Значение определяется как разность Начисленной суммы и Подоходного налога.
К_вып=НС-ПН
2.2. Создание базы данных.
2.2.1 Создание заголовка таблицы и первой строки.
Для создания таблицы раскрываем приложение Excel Microsoft Office. После ввода названия таблицы заполняем заголовки столбцов и форматируем их. Для этого выбираем команды меню Формат Столбец Ширина и устанавливаем необходимые значения (в соответствии с количеством символов в каждом поле, оговоренном в структуре базы данных). После ввода заголовков столбцов выбираем соответствующий вид форматирования. Для этого используем пункты меню Формат Ячейки и активизируем соответствующие вкладки: Число, Выравнивание, Шрифт. В числовых полях задаем 2 знака после точки, выравнивание в тестовых полях устанавливаем по правому краю ячейки, выравнивание числовых полей выполняем по центру.
После форматирования заголовков стролбцов приступаем к заполнению 1-й строки. Вводим исходные данные и в вычисляемых полях (выделены зеленым цветом) записываем расчетные формулы.
| № | ФИО | Бригада | Специальность | Оклад | Стаж | Премия | Начисленная сумма | Подоходный налог | К выплате |
| Абдельгадир М.И. | №1 | ||||||||
| Антонова В.Б. | №2 | ||||||||
| Бирюкова Г.О. | №1 | ||||||||
| Борисова Н.П. | №1 | ||||||||
| Вовченко А.А. | №2 | ||||||||
| Горец А.В. | №2 | ||||||||
| Грант А.С. | №1 | ||||||||
| Дмитренко П.П. | №2 | ||||||||
| Дмитренко В.И. | №2 | ||||||||
| Замовский Э.Ф. | №1 | ||||||||
| Иванов И.И. | №1 | ||||||||
| Клочко Г.К. | №2 | ||||||||
| Новиков О.В. | №2 | ||||||||
| Прокопенко В.В. | №2 | ||||||||
| Соловьев Р.А. | №1 |
2.2. Заполнение таблицы с помощью Мастера форм.
Дальнейшее заполнение данных выполняем с помощью пунктов меню Данные Форма (в этот момент курсор должен быть установлен на ячейке в области создаваемой таблицы). При этом открывается окно диалога Мастера форм с заполненной первой записью исходной базы данных.
Щелкая на клавише «Добавить» в окне диалога и последовательно заполняя пустые поля исходными данными, создаем исходную базу данных.
2.3. Ведение базы данных.
Ведение базы данных заключается в корректировке существующих данных, добавлении новых, удалении полей, содержащих ненужную или ошибочную информацию. В соответствии с заданием выполним операции по редактированию, добавлению, удалению информации. При работе в среде электронных таблиц для этого могут быть использованы различные средства.
2.3.1. Редактирование полей.
Добавим к существующей базе поле, которое отражает порядковый номер записей в базе. Для этого установим курсор в ячейку первого столбца и активизируем пункт меню Вставка Столбцы.
2.2. Редактирование записей
Для удаления записей из базы данных необходимо эти записи выделить и активизировать команды меню Конструктор/Удалить Строку. В результате выполнения этих действий строка, в которой был установлен курсор будет удалена. Для добавления строк в базу данных необходимо выполнить следующие действия: активизировать команды меню Конструктор /Вставка Строки. В результате в базу данных будет добавлена пустая строка над строкой, в которой находился курсор. Далее заносим нужные сведения в добавленную строку.
.
2.4.Начальная обработка данных.
2. 4.1. Добавление суммы по столбцам.
Добавим в числовых полях суммирование по столбцам.
Формулы для вычисления сумм в ячейках F27, G27,H27,I27,J27 будут выглядеть соответствующим образом.
F27=СУММ(F7:F26); G27=СУММ(G7:G26); H27=СУММ(H7:H26);
I27=СУММ(I7:I26) J27=СУММ(J7:J26)
2.4.2. Добавление суммирования по критерию.
Используем функцию СУММЕСЛИ для добавления суммы по заданному условию. В соответствии со справкой Мастера формул функция может быть представлена в общем виде как СУММЕСЛИ(диапазон; условие; диапазон_суммирования) В нашем случае просуммируем премиальный фонд ветеранам труда (стаж должен превышать 10 лет). Расчетная формула примет вид:
G27=СУММЕСЛИ(E7:E26;>10;G7:G26)
Результат использования формулы приведен на Рис. 2.10.
2.5. Сортировка базы данных. 2.5.1. Простая сортировка по полю.
Для обычной сортировки базы данных по одному полю (по возрастанию или по убыванию) необходимо воспользоваться пунктом меню Данные Сортировка или пиктограммами на графическом меню.
Сортировка по возрастанию Сортировка по убыванию
При этом курсор должен быть установлен в поле, которое будем сортировать. Для сортировки по возрастанию по полю «Стаж» установим курсор на ячейку в этом поле и выберем направление сортировки «по возрастанию». Результат сортировки представим на
2.5.2 Сортировка по нескольким полям.
Для проведения более сложной сортировки (по нескольким полям) откроем окно диалога «Сортировка диапазона» Для этого выполним команды меню Данные Сортировка. Чтобы сортировка выполнялась по двум или трем полям в окне диалога для каждого диапазона задаем направление сортировки. Наименование диапазонов выбираем в окне диалога, раскрывая список наименований (щелкаем последовательно по областям окна диалога «Сортировать по», «Затем по», «В последнюю очередь по»), и указываем направление сортировки по каждому полю (убывание/возрастание). В нашем примере выполним сортировку по трем критериям: по полю «Стаж», затем по полю «Специальность» и в последнюю очередь по полю «Оклад».Для всех трех критериев задаем направление сортировки «По возрастанию».
2.6. Формы представления информации, содержащейся в базе данных. 2.6.1. Добавление промежуточных итогов.
Добавление промежуточных и окончательных итогов выполняется после сортировки исходной базы по выбранному полю. Выполним сортировку исходной базы по полю «Бригада» и добавим промежуточные и общий итоги. Для добавления итогов откроем окно диалога «Промежуточные итоги». Для этого выполняем команды меню Данные Итоги. В окне диалога зададим поле, в котором будет отслеживаться изменение значений (например поле «Бригада»). Затем укажем в строке «Операция» тот вид операции, который нужен для выполнения задания(Сумма, Среднее и т.д.). В списке окна диалога «Добавить итоги по» укажем, каких еще полях базы данных необходимо выполнить аналогичные действия.
Аналогичные действия выполним для добавления итогов по полю «Специальность». Отсортируем исходную базу по полю «Специальность» по возрастанию и при каждом изменении в этом поле добавим промежуточные итоги. Определим суммарные значения начисленной суммы и подоходного налога по специальностям. Для этого в окне диалога «Промежуточные итоги» выберем поле, изменения в котором приведут к добавлению итогов в таблицу. Это поле «Специальность», и установим операцию для выполнения («Сумма»). Установим в окне диалога, для каких полей будет выполняться эта операция (Начислено, По/нал).
2.7. Анализ информации, содержащейся в базе данных.
2.7.1 Вычисление статистических характеристик
Простейшая математическая обработка числовых полей выполняется с помощью Мастера функций (раздел статистических функций МАКС, СРЗНАЧ, МИН). Для более удобной работы представим результаты в числовом и формульном виде. Для представления в формульном виде воспользуемся меню MS EXCEL Сервис Параметры Вид Параметры окна Формулы.
2.7.2. Работа с функциями из раздела Базы данных
В разделе Базы данных из Мастера функций представлено более 10 специальных функций для работы с базами данных. В задании выполнялось суммирование денежных выплат для рабочих специальности «Маляр» со стажесм более 10 лет. Для этого использовалась функция БДСУММ(база_данных; поле; критерий). В качестве диапазона исследуемых данных было выбрана исходная база. Суммирование производилось по полю «К выплате», или по 10-му столбцу базы, при этом учитывались ограничения по специальности и по стажу, которые записаны в ячейках B28:С29.
2.8. Выборочное использование данных
Выборочное использование данных представляет систему отбора данных из исходной базы по какому-либо признаку и дальнейшую обработку этих данных. Обычно применяют для этих целей пользовательский автофильтр и расширенный фильтр.
2.8.1. Пользовательский автофильтр.
Подключение пользовательского автофильтра происходит при активизации меню Данные-Фильтр-Пользовательский автофильтр. Затем устанавливается критерий для фильтрации данных(для выбранного поля раскрываем список, который помещен в строке заголовков полей базы данных. Если необходимо убрать пользовательский автофильтр активизируем команду Данные Фильтр Автофильтр.
Выберем из исходной базы записи, которые соответствуют следующему критерию отбора: Специальность «сварщик». Установим этот критерий в окне диалога
Далее установим фильтр для выбора информации из исходной базы по полю стаж. Необходимо отобрать рабочих со стажем от 5 до 10 лет Установим фильтр используя команды меню Данные Фильтр Автофильтр и раскрываем список в поле, по которому предполагается выполнять фильтрацию. Выберем из списка параметр «Условие». В открывшемся окне диалога установим критерии для фильтра.. Для поля стаж используем операторы «больше или равно» и меньше или равно» и задаем числовые значения соответственно 5 и 10.
8.2 Расширенный фильтр. Возможности расширенного фильтра значительно больше, чем у пользовательского. Подключаем его при помощи команды меню Данные Фильтр Раширенный С помощьюрасширенного фильтра выберем из исходной базы записи, у которых в поле «специальность» будут значения «сварщик» или «маляр» и стаж этих рабочих должен превышать 10 лет. Для создания круговой диаграммы выберем таблицу данных, для которых эта диаграмма будет строиться. Ввиду того, что исходная база содержит 20 строк, построим диаграмму для предварительно отфильтрованной базе по полю № бригады (Бригада №1). Укажем диапазоны данных, которые будем использовать. Для этого выделим столбцы ФИО и К выплате. Выделяем при нажатой клавише CTRL. Далее с помощью мастера диаграмм устанавливаем вид выводимой информации на поле диаграммы (легенда, заголовок, доли значений и т.д.) и место расположения диаграммы (на отдельном листе или рядом с таблицей.
Круговая диаграмма распределения выплат по бригаде №1.
Для построения следующей даграммы из перечня возможных видов в списке Мастера диаграмм выберем разновидность «График». Для всех сотрудников фирмы построим график распределения денежных выплат.