Лабораторная работа №22

Наименование работы: Сортировка данных, фильтр, работа с листами рабочей книги

Цель работы: Ознакомиться с общими сведениями об управлении листами рабочей книги, удалении и переименовании листов; составлять формулы, содержащие адреса ячеек на разных листах рабочей книги; составлять таблицы на основе данных из другой таблицы, расположенной на другом листе рабочей книги. Формировать ОК-2, ОК-4, ОК-5, ОК-6, ПК-1.2, ПК-1.3, ПК-1.6, ПК-3.1, ПК-3.2

Литература:

1. Кунтаева Г.Х., Строева Л.Р. Пакеты прикладных программ. Учебное пособие, 2015

2. Фуфаев Э.В., Фуфаева Л.И. Пакеты прикладных программ, 2014.

Задание:

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

Создайте заготовки таблицы самостоятельно (см. образец на рис.1). Заголовок размещен в двух строках таблицы, применен полужирный стиль начертания шрифта, весь текст выровнен по центру, а «Налоги» – по центру выделения. Измените ширину столбца (в зависимости от объема вводимой информации) и обрамление таблицы. В данном случае использовано сложное обрамление, когда снята часть рамок. Важно по предложенному образцу определить реальное положение ячеек и выполнить соответствующее обрамление, выделяя различные блоки ячеек.

Рисунок 1

Задайте формат числа «денежный» для ячеек, содержащих суммы.

Теперь заполните столбцы D и E формулами для расчета профсоюзного и пенсионного налогов. Примем профсоюзный и пенсионный налоги, составляющими по 1% от оклада. Удобно ввести формулу в одну ячейку, а затем распространить ее на оба столбца. Самое важное – не забыть про абсолютные ссылки, так как профсоюзный и пенсионный налоги нужно брать от оклада, т.е. ссылаться только на столбец С «Оклад». Примерный вид формулы: =$С3*1% или =$С3*0,01 или =$С3*1/100. После ввода формулы в ячейку D3 ее нужно распространить вниз (протянув за маркер выделения) и затем вправо на один столбец. Теперь формулами уже заполнены столбцы D и E.

Столбец F следует заполнить формулой для расчета подоходного налога. Подоходный налог вычисляется по формуле: 12% от Оклада за вычетом минимальной заработной платы и пенсионного налога. Примерный вид формулы: =(С3-Е3-110)*12% или =(С3-Е3-110)*12/100 или =(С3-Е3-110)*0,12.

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

Заполните столбцы «Фамилия, имя, отчество», «Оклад» и «Число детей» после того, как ввели все формулы. Результат будет вычисляться сразу же после ввода данных в ячейку. При желании можно воспользоваться режимом формы для заполнения таблицы. Заполнение таблицы можно производить следующим образом:

- Наберите любые несколько фамилий (сотрудников предприятия) в произвольном порядке или см. образец заполненной таблицы на рис.2.

- В столбце С наберите оклады сотрудников согласно штатному расписанию (надерите любые числа или см. образец заполненной таблицы на рис.2).

- В последнем столбце «Число детей» проставьте любые числа от 0 до 3.

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

Рисунок 2

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

Перейдите с помощью мыши на Лист 2. Переименуйте его в «Детские». На этом листе создайте ведомость на получение компенсации на детей на основе таблица начислений. Подготовьте пустую таблицу, состоящую из трех столбцов.

Наберите заголовки столбцов: в ячейку А1 – «ФИО», в ячейку В1 – «Сумма», в С1 – «Подпись» (см. образец на рис.3). Выполните обрамление таблицы.

Список фамилий внесите автоматически с помощью связи межу листами. Для этого выделите ячейку А2 листа «Детские» и введите в нею формулу: =Начисления!В3, где имя листа обозначается восклицательным знаком, а В3 – адрес ячейки, в которой размещена первая фамилия сотрудника на листе «Начисления». Эту формулу можно не набирать в ручную, а перейти на лист «Начисления», выделит мышью ячейку, содержащую первую фамилию списка и нажмите Enter. Вернитесь на лист «Детские» и распространите полученную формулу вниз столбца А.

В ячейке В2 аналогичным образом поместите формулу: =Начисления!Н3* 53.10, где Н3 – адрес первой ячейки на листе «Начисления», содержащей число детей. Заполните этой формулой остальные ячейки столбца В. Примените к ним денежный формат числа.

Для того чтобы список в таблице на листе «Детские» состоял только из сотрудников, имеющих детей, установите фильтр по наличию детей. Для этого выполните команду Данные®Фильтр®Автофильтр и в раскрывающемся списке «Сумма» выберите критерий (Условие). В левом верхнем поле окна пользовательского автофильта выберите больше или равно, а в верхнем правом поле выберите минимальное ненулевое число из списка (в нашем случае это 53,13). Нажмите ОК.

Вставьте в самом верхе дополнительную строку и наберите заголовок этого листа: «Ведомость на получение детской компенсации». В окончательном варианте отфильтрованная таблица должна иметь следующий вид:

Рисунок 3

Порядок выполнения работы:

1. Получить допуск к работе;

2. Запустить Microsoft Excel и выполнить задания;

3. Составить отчет;

4. Ответить на контрольные вопросы.

Содержание отчета:

1. Наименование, цель занятия, задание;

2. Выполненное задание;

3. Ответы на контрольные вопросы.

Контрольные вопросы:

1. Какие типы данных можно сортировать в Excel?

2. Что определяет ключ сортировки?

3. Укажите максимальное количество ключей сортировки.

4. Назовите способы связывания рабочих листов

5. Какой тип связывания рабочих листов предпочтительнее?

6. Для чего предназначено связывание рабочих листов?

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

8. Что такое «Автофильтр» и как с его помощью осуществлять выборку данных по запросу пользователя?

9. Как производится копирование и вставка данных в ячейки?

10. Каким образом можно скопировать находящиеся в ячейке только формулы, только значения, только параметры?

11. Как объединить несколько ячеек при написании, например, заголовка таблицы?

12. Как разъединить объединенные ячейки (вернуться в исходное состояние)?

ПРИЛОЖЕНИЕ

Сортировка данных в Excel используется для переупорядочения строк в списке в соответствии с содержимым конкретных столбцов. Сортировать можно как текстовые, так и числовые данные. При этом первые сортируются в алфавитном или в обратном алфавитном порядке, а вторые – либо в порядке убывания, либо в порядке возрастания. Excel автоматически определяет тип сортируемых данных.

Команда Сортировка может быть использована для любого интервала ячеек. Для сортировки записей выделите все ячейки, а затем вызовите команду Меню → Данные → Сортировка. Откроется диалоговое окно «Сортировка диапазона».

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

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

Microsoft Excel использует следующие основные принципы при сортировке:

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

2. При использовании более одного ключа записи с одинаковыми значениями первого ключа будут отсортированы по второму, а затем и по третьему ключу.

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

4. Скрытые строки не перемещаются, если только они не являются частью структуры.

5. Параметры сортировки - выбранный столбец, порядок (возрастающий или убывающий) и направление сортировки (сверху вниз или слева направо) - сохраняются от предыдущего применения сортировки, пока Вы не измените их, не выберите иной список или не измените метки столбцов.

6. Microsoft Excel позволяет также сортировать только выделенные строки или столбцы, или данные только в одной строке или столбце.

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

Для сортировки списка можно воспользоваться и кнопками сортировки на стандартной панели инструментов. Однако этот быстрый способ сортировки имеет существенный недостаток: Excel не позволяет проверить правильность выделения списка, а, следовательно, правильность сортировки.

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

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

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

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

Например: [Книга3.xls]Лист2!$А$5. 

Каждая ссылка устанавливает связь между двумя рабочими книгами. Рабочая книга, содержащая ссылку на ячейку с данными, называется исходной, а та, которая содержит ссылку на ячейку с данными из другой книги – зависимой.

Создать связь можно следующими способами: используя открытую рабочую книгу или указав имя файла рабочей книги на диске. Первый способ намного проще. В случае его применения одновременно откройте обе рабочие книги. (Исходную и зависимую.) Расположите эти книги на экране рядом. Для этого выберите Меню → Окно → Сравнить рядом с → и в открывшемся диалоговом окне «Сравнить рядом» выберите нужный файл.

Следует так разместить исходную и зависимые рабочие книги, чтобы одновременно видеть ячейки с формулами и исходными данными.

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

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

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

Анализ данных с использованием автофильтра. Фильтры, интегрированные в Excel, упрощают процесс ввода записей в список и их удаление из него, а также процесс поиска информации. Фильтры используются для отображения на экране записей, удовлетворяющих определённому условию, и временного сокрытия всех остальных записей. Записи, которые не соответствуют заданному критерию, при фильтрации скрываются, однако их расположение остаётся прежним.

Для задания автофильтра установите курсор в какое-либо поле таблицы; выберите режим Меню → Данные → Автофильтр.

Автоматически строится всплывающие меню по каждому столбцу. В меню задаются параметры для выборки записей:

§ показать все записи,

§ показать первые 10 записей,

§ условие (позволяет задать другое условие фильтрации),

§ показать записи равные значению, указанному в меню,

§ сортировка по возрастанию,

§ сортировка по убыванию.

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

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

Чтобы вновь отобразить весь список, установите для всех столбцов в качестве условия элемент «Все» или вызовите команду Меню → Данные → Фильтр → Отобразить все.

Отмена фильтра выполняется повторным выбором режима Автофильтр.

Автофильтр позволяет делать выборку записей только в текущей таблице. Если требуется поместить выбранные записи в отдельную таблицу, то необходимо использовать расширенный фильтр.

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

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

По не отфильтрованному участку таблицы можно вычислять суммы, произведения, производить некоторые другие операции так, будто никаких скрытых строк у нас нет. Для этого требуется в режиме фильтрации поставить мышь в свободную ячейку, нажать на кнопку  (автосумма) на стандартной панели инструментов и выделить все показанные на листе строки. В результате посчитается специальная функция ПРОМЕЖУТОЧНЫЕ ИТОГИ (категории «математические»). В ней первым аргументом является номер математической или статистической операции, а вторым – интервал вычислений. Вот некоторые номера операций:

1 – вычисление среднего по неотфильтрованным данным;

2 и 3 – подсчёт количества чисел и непустых ячеек;

4 и 5 – вычисление максимума и минимума;

6 – произведение;

7 и 8 – стандартное отклонение;

9 – суммирование неотфильтрованных данных;

10 и 11 – дисперсия и т.д.

Фильтрацию удобно использовать при создании диаграммы, иллюстрирующей только определённую часть информации из списка. 

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