Методические указания по решению задачи

Решение задачи предполагает работу со сформированной средствами MS Excel базой данных (списком), которая включает создание базы данных, просмотр записей, редактирование содержимого записей, добавление и удаление записей базы данных, сортировку, фильтрацию, структуризацию записей базы данных. Ниже приведены основные правила и приемы работы со списками (на примере), изучение которых позволит правильно выполнить задачу.

Формирование базы данных в MS Excel

При создании (формировании) базы данных следует придерживаться некоторых основных правил:

· название поля базы данных (столбца таблицы) должно занимать только одну ячейку электронной таблицы, т. е. не допускается использование объединенных ячеек;

· если название поля базы данных выходит за пределы ячейки, то следует установить перенос слов (выделить ячейку или диапазон ячеек Þ меню Формат ячеек Þ на вкладке «Выравнивание» установить флажок «переносить по словам» Þ ОК);

· все данные одного поля должны иметь одинаковый формат;

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

Выполнение Сортировки

Для выполнения сортировки следует

· выделить любую одну ячейку внутри базы данных;

· вкладка Данные Þ Сортировка …;

· в диалоговом окне Сортировка в списке поля «Сортировать по» выбрать имя первого поля, по которому осуществляется сортировка;

· при необходимости сортировки по нескольким полям воспользоваться кнопкой Добавить уровень;

· в списке поля «Затем по» – имя второго поля сортировки;

· нажать ОК.

Работа с Фильтром

Фильтр позволяет выбирать из базы данных записи, удовлетворяющие заданным условиям отбора.

Для вызова Фильтра следует выделить любую одну ячейку внутри базы данных Þ вкладка Данные Þ кнопка Фильтр. В результате все названия полей (столбцов) будут представлять собой раскрывающиеся списки. Для выбора из базы данных необходимых записей следует раскрыть список соответствующего поля и выбрать из него конкретное значение показателя или варианты «Числовые фильтры», «Текстовые фильтры», «Фильтры по дате», «Пустые» и др. При выборе пунктов «Числовые фильтры», «Текстовые фильтры» или «Фильтры по дате» в диалоговом окне следует задать соответствующие условия, например, при выборе величины, которая находится в промежутке от 20000 до 40000, диалоговое окно будет иметь вид, приведенный на рис. 1.

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

 

 

Рис.1. Вид диалогового окна «Пользовательский автофильтр»

Работа с Расширенным фильтром

Инструмент Расширенный фильтр позволяет организовать отбор записей базы данных по более сложным (по сравнению с Фильтром) критериям. При этом используется интервал критериев, в который одновременно заносятся все условия фильтрации по всем необходимым полям.

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

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

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

 

Пример 1

 

	А	В
…
	Оклад	Возраст
	>25000	>50

В примере 1 интервал критериев (А14:В15) содержит поля «Оклад» и «Возраст». Записанные условия позволяют выбрать из базы данных те записи, для которых значение в поле «Оклад» превышает 25000 руб. И в поле «Возраст» превышает 50 лет.

 

Пример 2

 

 

	А	В
…
	Оклад	Возраст
	>25000
		>50

В примере 2 интервал критериев (А14:В16) содержит поля «Оклад» и «Возраст». Записанные условия позволяют выбрать из базы данных те записи, для которых значение в поле «Оклад» превышает 25000 руб. ИЛИ в поле «Возраст» превышает 50 лет.

Для выполнения фильтрации с использованием Расширенного фильтра следует:

· выделить любую одну ячейку внутри базы данных;

· вкладка Данные Þ кнопка Дополнительно;

в открывшемся диалоговом окне «Расширенный фильтр» (рис. 2):

· проверить правильность исходного диапазона (диапазон ячеек, в котором размещается база данных) и при необходимости внести изменения;

· установить курсор мыши в область «Диапазон условий» и выделить диапазон ячеек, в котором содержится сформированный интервал критериев;

· установить переключатель «скопировать результат в другое место»;

· установить курсор мыши в область «Поместить результат в диапазон:» и выделить на рабочем листе ячейку, начиная с которой будет размещаться полученный результат выборки;

· нажать ОК.

 

Рис. 2. Вид диалогового окна «Расширенный фильтр»

Получение итоговых данных

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

Для получения промежуточных итогов следует:

· упорядочить записи базы данных по группировочным признакам (например, по полю «Отдел»);

· выделить любую одну ячейку внутри базы данных;

· вкладка Данные Þ кнопка Промежуточный итог;

в появившемся диалоговом окне «Промежуточные итоги» (рис.3):

· в области «При каждом изменении в:» щелчком по кнопке открыть список значений и выбрать из него значение группировочного признака;

· аналогичным образом из списка области «Операция:» выбрать вид функции (например, Сумма или Среднее);

· в области «Добавить итоги по:» установить флажки для тех полей, по которым следует получить итоги;

· нажать ОК.

Рис. 3. Вид диалогового окна «Промежуточные итоги»

 

Примечание. Если необходимо получить итоги с использованием нескольких функций, то при повторном вычислении промежуточных итогов в диалоговом окне «Промежуточные итоги» следует убрать флажок «Заменить текущие итоги». Для просмотра только итоговых строк следует использовать номера уровней, которые появляются слева от названий столбцов электронной таблицы (при выборе 1-го уровня отображаются только строки общих итогов, 2-го и последующих – строки промежуточных и общих итогов, а при выборе самого младшего уровня отображаются все записи базы данных и все итоговые строки).

Работа с диаграммами

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

· она не должна содержать объединенных ячеек,

· название каждого столбца должно занимать только одну ячейку;

· каждый показатель должен занимать только одну ячейку;

· допустимо использовать флажок «Переносить по словам» на вкладке «Выравнивание» диалогового окна «Формат ячеек» для записи информации в ячейке в несколько строк;

· не должно быть пустых строк или столбцов.

Для построения диаграммы следует выделить на листе исходные данные и использовать соответствующие кнопки группы Диаграммы вкладки Вставка.

Построенную диаграмму в целом и ее отдельные элементы можно форматировать, используя кнопки на вкладках Конструктор, Макет, Формат или команды контекстного меню, для чего следует выделить объект, вызвать требуемую команду и задать необходимые параметры. Можно изменять размеры диаграммы, используя маркеры размеров, а также ее местоположение на рабочем листе, перемещая диаграмму при нажатой левой кнопке мыши в нужное место. При изменении исходных данных автоматически будет меняться и диаграмма (если были удалены строки или столбцы исходной таблице, добавлены строки или столбцы внутри таблицы). Если новые данные добавляются в конец таблицы, то для отображения этих данных на диаграмме следует изменить диапазон исходных данных, используя мышь (расширить область исходных данных) или команду Выбрать данные из контекстного меню или на вкладке Конструктор.

Рассмотрим примеры различных типов диаграмм, построенных на основе данных табл. 2.

 

 

 

Таблица 2

Результаты зимней сессии

 

Факультет	Количество студентов, получивших оценки «пять» и «четыре»	Количество студентов, имеющих оценку «три»	Количество студентов, имеющих оценку «два»	Качественная успеваемость
ПЧ	Т	Д	КУ
Экономический				38,5
Коммерции и сервиса				37,0
Юридический				33,3
Финансов и учета				39,5

 

Качественная успеваемость по каждому факультету рассчитывается по формуле

КУ(i)=ПЧ(i)/ (ПЧ(i)+Т(i)+Д(i)) · 100,

где i – индекс факультета.

Построим гистограмму, отображающую соотношение студентов, имеющих оценки «пять» и «четыре», «три» и «два» полученные в период зимней сессии на всех факультетах (рис. 4), а также смешанную диаграмму, в которой количество студентов, имеющих оценки «пять» «четыре», «три» представлено в виде гистограммы, а качественная успеваемость – в виде линейного графика (рис. 5).

 

 

Рис. 4. Пример гистограммы

 

Рис. 5. Пример смешанной диаграммы