Лабораторная работа № 1. Создание и оформление электронной таблицы

MS Excel. Поиск информации

Цель работы: научиться создавать и оформлять таблицу, производить поиск информации средствами MS Excel (поиск, фильтр, сортировка).

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

Результат работы: файл ЛР1_ФАМ.xlsx.

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

Создание и оформление таблицы. На основе файла ЛР1.xlsx создайте электронную таблицу для работы с кадровой информацией.

 откройте файл. Вставьте в начале таблицы пустые строки. Заполните их данными (рисунок 6 приложения к лабораторной работе);

 установите шрифт Times…, обычный, 12 пт. Для строки заголовка примените начертание полужирный;

 для всей таблицы установите выравнивание по левому краю. Для строки заголовка – по центру и по середине;

             соответствующих ячейках установите перенос слов (рисунок 3);

Рисунок 3 – Окно диалога Формат ячеек.

 после столбца Образование вставьте столбец Пол и заполните его соответствующими данными;

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

добавьте столбец с № п/п. Для его заполнения используйте команды Главная/Редактирование/Заполнить/Прогрессия или наберите первые два значения, задав тем сам порядок нумерации. Выделите ячейки со значениями и скопируйте их «мышью», зажав левую клавишу в правом нижнем углу выделенного блока;

Рекомендуется всегда использовать столбец с номерами строк. Он позволит вернуться к первоначальному расположению строк после нескольких сортировок. Для этого достаточно выполнить сортировку этого столбца (№п/п).

 проведите форматирование ячеек согласно набранному в нем тексту, применив команды вкладка Главная/Ячейки/Формат/Автоподбор высоты строки (ширины столбца);

 обрамите таблицу и установите заливку цветом для строки заголовка.

Подготовьте таблицу к печати.

 установите параметры страницы так, чтобы таблица помещалась на лист А4 (Разметка страницы/Параметры страницы);

установите печать строки заголовка на каждой странице;

заголовок всей таблицы разместите в верхнем колонтитуле в центре.

Вставьте номер страницы (нижний колонтитул, справа);

 проверьте    правильность    оформления,    выбрав    предварительный просмотр.

Сортировка и фильтр данных. Для проведения сортировки данных в алфавитном порядке или по возрастанию/убыванию, а также отбора сведений по определенному критерию (по месту жительства, образованию, должности и т.п.) используются команды вкладка Главная/ Редактирование/Сортировка и фильтр. На вкладке Данные также существует группа команд Сортировка и фильтр. Если же необходимо постоянно изменять критерии сортировки и отбора данных, то для упрощения анализа данных следует использовать автофильтр (установить в строке заголовка выпадающий список команд).

 выделите строку заголовка. Выберите команды Главная/ Редактирование/Сортировка и фильтр/Фильтр или клавиши Ctrl+Shift+L В ячейках строки заголовка появится кнопка со стрелкой . Это означает, что автофильтр включен;

 отсортируйте таблицу в алфавитном порядке фамилий. Щелкните по кнопке фильтра в ячейке Фамилия, выберите Сортировка от А до Я. Обратите внимание: значок списка изменился, фамилии выстроены в

алфавитном порядке, а диапазон сортировки автоматически расширен для всех данных таблицы;

 верните таблицу к исходному состоянию, отсортировав по возрастанию порядковых номеров (№ п/п).

Для сортировки таблицы одновременно по нескольким условиям применяют команду Пользовательская сортировка.

Рисунок 4 – Окно диалога команды Пользовательская сортировка.

для добавления уровней сортировки (Сортировать по, Затем по …) нажмите кнопку Добавить уровень;

обратите внимание на флажок Мои данные содержат заголовки.

Установка флажка необходима, если таблица содержит строку заголовка;

в таблице определите номер строки сотрудника по фамилии Каменева. Выберите команды Главная/Редактирование/Найти и выделить/Найти. Заполните окно диалога необходимыми данными, проверьте установку параметров;

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

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

Отсортируйте таблицу согласно полученному сотрудниками образованию: среднее, профессионально-техническое, среднее специальное, высшее.

создайте         настраиваемый         список.          Выберите         команды

Файл/Параметры/Дополнительно/Общие/Изменить списки;

в окне диалога наберите данные списка, нажмите кнопку Добавить.

Затем ОК для создания списка;

выберите команду Пользовательская сортировка в столбце Образование. При необходимости удалите лишние уровни. Установите Сортировать по Образование. В области Порядок выберите Настраиваемый список;

в окне диалога выделите созданный ранее список. Завершите выполнение команды. Данные таблицы буду отсортированы по уровню образования.

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

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

в столбце Образование щелкните по кнопке фильтра. В окне диалога снимите флажок (Выделить все) и установите флажок высшее;

завершите действия. На экране появится список сотрудников с высшим образованием. В информационной строке появится сообщение Найдено записей: 23 из 35;

установите флажок (Выделить все) для отмены условий фильтра.

Получите список пяти сотрудников, которые получают наименьший оклад. выберите в столбце Оклад Числовые фильтры/Первые 10…;

в окне диалога установить в области Показать: наименьших 5.

Получите список сотрудников, фамилии которых начинаются на букву К. выберите в столбце Фамилия Текстовые фильтры/начинается с…

Введите необходимые условия в окне диалога. На экране появится список

фамилий, начинающихся на К; отмените заданные условия.

Есть условия, реализовать которые не получается одним действием по установке фильтра. Фильтр необходимо применять несколько раз. Для получения информации по таким условиям можно использовать функции MS Excel и команду Данные/Сортировка и фильтр/Дополнительно.

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

создайте диапазон условий для фильтра. Наберите в ячейке Q1 Прием (этот заголовок должен отличаться от заголовка столбца таблицы). В ячейке Q2 формулу =ИЛИ(МЕСЯЦ(F2)=6; МЕСЯЦ(F2)=7; МЕСЯЦ(F2)=8);

выберите команду   Дополнительно.   В    окне    диалога   установите необходимые параметры (рисунок 5);

Рисунок 5 – Окно диалога Расширенный фильтр.

найдено количество записей 11.

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

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

1.   Получите список сотрудников:

в алфавитном порядке фамилий, имен, отчеств; по дате рождения, начиная с наиболее ранней;

в алфавитном порядке подразделений и фамилий. Определите номер строки сотрудника с фамилией Воронин (строку выделите цветом);

по типу населенного пункта (городской поселок, город, районный центр, областной центр, столица);

пяти сотрудников, которые имеют наибольший оклад; сотрудников отдела разработки ПО, родившихся в июне месяце; штатных сотрудников, родившихся в 1970-ые годы.

2.   Определите количество сотрудников

фамилия которых состоит из 6 букв;

фамилия которых начинается на Т и состоят из 5 букв; женщин, имеющих высшее образование;

номер телефона которых начинается на 22; оклад которых больше 500 рублей; принятых на работу осенью;

имеющих высшее образование и родившихся в Минске.

3.   Найдите сотрудников

принятых на работу в январе 2015 года. Результаты работы подтвердите копией экрана;

проработавших в организации менее 10 лет; оклад которых более 500 и менее 800 рублей; принятых на работу зимой.

4.   ¶Используя функции MS Excel постройте диапазон условий и получите список сотрудников, родившихся весной и имеющих оклад более 450 и менее 800 рублей.