Лабораторная работа № 4. Обработка и анализ данных в MS Excel.

Промежуточные итоги

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

Материал для работы: файл-заготовка ЛР4.xlsx.

Результат работы: файлы ЛР4_Задание_ФАМ.xlsx.

Выполнение работы

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

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

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

Для проверки выполнения работы сохраняйте результаты в файлах с именем ЛР4_Задание_Фам.xlsx. Каждый раз для выполнения нового задания Вам необходимо будет открывать файл ЛР4.xlsx.

На основе данных о сотрудниках организации (файл ЛР4.xlsx) получите сведения о количестве мужчин и женщин. Для этого:

отсортируйте в алфавитном порядке данные столбца Пол;

выберите вкладка Данные/Промежуточный итог. В окне диалога установите соответствующие параметры (рисунок 13). Так как необходимо определить только количество женщин и мужчин, то в списках При каждом изменении в и Добавить итоги по установите флажок в поле Пол. Данные о

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

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

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

Чтобы расположить итоговую строку над строкой данных, устанавливается флажок Итоги под данными.

 просмотрите полученную таблицу. В левом верхнем углу появились три кнопки [1], [2] и [3], позволяющие выводить на экран различные уровни детализации данных: [3] – основной экран, [2] –только итоговые значения, [1] – только общее число;

Рисунок 13 – Установка параметров в окне диалога команды Промежуточный итог.

 перейдите на дополнительный экран [2]. В соответствующих ячейках I18, I38 содержатся сгенерированные формулы итоговых значений. Щелчок мышью по значку + позволяет раскрыть список и получить полные сведения о сотрудниках по каждому полу, а по значку – скроет список.

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

 выделите ячейки с текстом. На вкладке Главная выберите Найти и выделить/Заменить. В поле Найти наберите Количество. Поле Заменить на не заполняйте, т.к. текст необходимо удалить. Для завершения нажмите кнопку Заменить все;

 выделите только необходимые для построения строки. Постройте круговую диаграмму. Оформите все необходимые элементы: название, подписи категорий, значение в %. Обратите внимание, что гистограмма корректно отображается только при активном [2] экране.

Определите среднее   значение   оклада   по   каждому   виду   работы   в организации:

отсортируйте по алфавиту столбец Вид работы;

выберите команду промежуточный итог и установите параметры так, как показано на рисунке 14.

Рисунок 14 – Установка параметров в окне диалога для определения среднего оклада по каждому виду работ.

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

 последовательно выберите Главная/Редактирование/Сортировка и фильтр/Настраиваемая сортировка;

 в окне диалога команды установите два уровня сортировки: 1-ый – Образование, 2-ой – Вид работы/ОК;

 установите параметры как показано на рисунке 15. Обратите внимание, что первым указывается то поле, по которому выбирается операция (количество сотрудников по тому или иному виду образования), а затем указывается поле по каким группам будут подводиться итоги (по каждому виду работ).

Рисунок 15 – Установка параметров в окне диалога

для определения количества сотрудников с тем или иным видом образования по каждому виду работ.

Задания для самостоятельной работы

1.   Определите  количество  сотрудников  и   постройте  соответствующие диаграммы:

имеющих высшее, среднее, среднее специальное и иное образование; проживающих в столице, областных центрах, городах и т.д;

в каждом подразделении.

2.   Определите средний оклад сотрудников по: виду работы;

по подразделению.

3.   Определите минимальный оклад: в каждом подразделении;

штатных сотрудников и совместителей

4.   Определите количество:

штатных и совместителей в каждом подразделении;

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

5.   ¶ Определите количество сотрудников, родившихся в тот или иной год.