Построение списка должников
Отчет
По лабораторной работе №7
«Обработка данных в Excel. Сортировка, фильтры, итоги»
Выполнил:
ст. гр. ЭП-11д
Мельник Д. М.
Принял:
к.т.н., доцент
Фисун С.Н.
Севастополь
Содержание
1. Цель работы.. 3
2. Постановка задачи. 3
3. Краткие теоретические сведения. 3
3.1 Списки. 3
3.2 Сортировка данных. 3
3.3 Фильтрация данных в списке. 4
3.3.1 Автофильтр. 4
3.3.2 Расширенный фильтр. 4
3.4 Подсчет итогов в списках. 6
4. Ход выполнения работы.. 6
4.1 Построение списка должников. 6
4.2 Построение списков поздних дат. 6
4.3 Выполнение сортировки. 6
4.4Выполнение автофильтра. 7
4.5 Выполнение расширенного фильтра. 7
4.6 Подведение итогов. 7
5. Анализ результатов, выводов. 7
Цель работы
Получить практические навыки при работе с данными в Excel-таблицах.
Постановка задачи
1) Изучить теоретические сведения к лабораторной работе
2) Выполнить Практические задания 1 - 7
3) Оформить отчёт по лабораторной работе
Краткие теоретические сведения
Списки
Основным способом представления данных в Microsoft Excel является список. Список – последовательность строк с однотипными данными. Каждая строка может быть разбита на поля. Первая строка списка список отводится для указания имен полей. Если имя поля требует нескольких строк, то высоту первой строки увеличивают.
При создании списка необходимо выполнять следующие правила:
1) На одном листе не следует помещать более одного списка;
2) Следует отделять список от других данных на листе, хотя бы одной пустой строкой и пустым столбцом;
3) Для имен столбцов следует использовать шрифт, тип данных, выравнивание, формат, рамку отличные от тех, которые используются для данных списка.
4) Чтобы отделить имена столбцов от данных, следует поместить рамку по нижнему краю ячеек первой строки.
Сортировка данных
Список можно отсортировать по алфавиту, по значению, в хронологическом порядке в соответствии с содержимым определенного поля.
Чтобы отсортировать весь список необходимо выделить одну ячейку в списке и выбрать в меню Данные – Сортировка.
Для быстрой сортировки на панели инструментов «Стандартная» находятся кнопки
 |  |
Ключевым полем для сортировки в этом случае является столбец с выделенной ячейкой.
Фильтрация данных в списке
С помощью фильтров можно выводить и просматривать только те данные, которые удовлетворяют определенным условиям.
Для этих целей в меню Данные – Фильтр есть две команды Автофильтр и Расширенный фильтр.
Автофильтр
По этой команде Excel помещает раскрывающиеся списки непосредственно в заголовки столбцов (там появляется кнопка). Щелкнув по этой кнопке можно получить список всех уникальных элементов столбца. Если в этом списке выделить некоторое значение, то из исходного списка будут удалены все строки, кроме тех, которые содержат выделенное значение.
Элемент столбца, который выделен в раскрывающемся списке, называется критерием фильтра. Можно продолжить фильтрацию списка с помощью критерия из другого столбца.
Чтобы удалить ранее введенный критерий, надо выбрать критерий ВСЕ.
Чтобы быстро восстановить исходный список надо выполнить команду Данные – Фильтр – Показать все.
С помощью автофильтра можно устанавливать и более сложные критерии (пользовательские). Для этого выбираем критерий «Условие», в результате чего появится диалоговое окно.
Расширенный фильтр
При необходимости использовать более сложные критерии отбора записей используется команда Данные – Фильтр – Расширенный фильтр. В отличие от автофильтра результаты фильтрации выносятся в другое место рабочего листа, не портя исходной таблицы.
Чтобы воспользоваться командой Расширенный фильтр, предварительно необходимо создать таблицу критериев, расположив ее на том же листе.
Обычно для этого копируют имена полей списка на свободное пространство. Количество строк в таблице определяется только количеством критериев поиска. Например, для реализации критерия из задания 4.2 необходимо было бы создать таблицу из двух строк:
Сумма долга
>100
Если критерий составной, то применяется следующее правило: столбцы в таблице критериев соединяются союзом «И», а строки – союзом «ИЛИ». Например, если Вас интересуют должники с суммой долга от 100 до 200гр или с сумой к возврату более 200гр. то таблица критериев будет выглядеть так:
| Сумма долга | Сумма долга | Итого к возврату |
| >100 | <200 | |
| >200 |
Результирующую таблицу можно помещать, как на место исходного списка, так и вне его, задав в диалоговом окне левую верхнюю ячейку будущей таблицы результата.
Расширенный фильтр используется и в том случае, если требуется вычисляемый критерий. Предположим необходимо вывести всех должников у кого сумма к возврату выше среднего значения по всем должникам.
Для записи такого критерия нужна формула. К формулам в таблице критериев расширенного фильтра предъявляются следующие требования:
1) формула должна выводить логическое значение ЛОЖЬ или ИСТИНА. После выполнения поиска на экран выводятся только те строки, для которых результатом будет ИСТИНА;
2) формула должна ссылаться хотя бы на один столбец (первое значение в столбце) в списке;
3) в таблице критериев заголовок над формулой не выполняется.
4) Примерный вид таблицы критериев для данного примера будет:
| =Е4>СРЗНАЧ($E$4:$E$18) |
где Е4:Е18 данные по суммам к возврату исходного списка. Обратите внимание, что ячейка для названия столбца пустая и что использована абсолютная адресация ячеек (знак $).
Подсчет итогов в списках
Обычно для подведения итогов используется команда Автосумма.Более полные итоги можно получить, используя команду Данные – Итоги.
Последовательность действий в этом случае может быть следующая:
1) Отсортируйте список по столбцу, для которого необходимо подвести промежуточный итог. Например, если Вы работаете с таблицей, содержащей информацию о продавцах и проданных ими товарах, то, чтобы просуммировать единицы продукции, проданные каждым лицом в списке продавцов, требуется отсортировать список по столбцу продавцов.
2) Укажите ячейку в этом списке.
3) Выберите команду Итоги в меню Данные.
4) Выберите столбец, содержащий группы, по которым необходимо подвести итоги, из списка При каждом изменении в. Это должен быть тот столбец, по которому проводилась сортировка списка на шаге 1.
5) Выберите функцию, необходимую для подведения итогов, из списка Операция, например СУММА.
6) Выберите столбцы, содержащие значения, по которым необходимо подвести итоги, в списке Добавить итоги по.
Ход выполнения работы
Построение списка должников.
1) Ввод данных (имён, дат, внесение цифр)
2) Написание формул пени и суммы к возврату