Списки в excel. Сортировка списков. Фильтры

Цель работы: ознакомиться с принципом создания базы данных на основе электронных таблиц EXCEL.

Теоретические сведения

Общие положения

 

При проектировании различного рода систем (энергетических, транспортных и др.) и при работе на предприятиях приходится иметь де­ло с большими объемами информации различного рода: данными об оборудовании, учетом материалов, кадровым учетом, статистическими и бухгалтерскими ведомостями.

Эта информация должна быть упорядо­ченной, доступной и допускающей корректировку. Информация, храни­мая в упорядоченном виде как совокупность данных о каком-либо объек­те или по какому-либо вопросу, называется базой данных. В базе данных энергетического предприятия может храниться ин­формация о параметрах воздушных и кабельных линий, трансформаторов, генера­торов и другого энергетического оборудования, стоимостные показатели этого оборудования, размеры стандартных строительных конструкций, списочный состав работников предприятия и т.п.

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

Создание списков

 

Для того чтобы создать список в EXCEL, рекомендуется придержи­ваться следующего порядка действий:

· введите имя рабочего листа, т.е. вместо стандартного Лист1 укажите другое название, например "Трансформаторы" (дважды щелкните левой кла­вишей мыши по имени листа "Лист1" или в меню Формат выполните команду Лист } Переименовать);

· введите заголовки столбцов, например: Марка, Мощность, Напряжение, Коэффициент трансформации;

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

· в меню Данные выполните команду Форма... На экране возникает диалоговое окно (рисунок 12.1);

· заполните поля ввода, например: ТДН

115/11

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

· окончив записи, нажать Закрыть.

 

 

Рисунок 12.1 - Диалоговое окно списка

Рекомендации по созданию списка

 

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

Организация списка

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

2. Помещайте подобные объекты в один столбец. Спроектируйте список таким образом, чтобы все строки содержали подобные объекты в одном столбце.

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

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

5. Отображение строк и столбцов. Перед внесением изменений в список убедитесь в том, что все скрытые строки и столбцы отображены. Если строки или столбцы списка скрыты, данные могут быть непреднамеренно удалены.

Формат списка

1. Использование форматированных подписей столбцов. Создайте подписи столбцов в первой строке списка. EXCEL использует подписи при создании отчетов, поиске и оформлении данных. Шрифт, выравнивание, шаблон, граница и формат прописных и строчных букв, присвоенные заголовкам столбцов списка, должны отличаться от формата, присвоенного строкам данных. Перед вводом подписей столбцов ячейкам должен быть присвоен текстовый формат.

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

3. Отсутствие пустых строк и столбцов. В самом списке не должно быть пустых строк и столбцов. Это упрощает идентификацию и выделение списка.

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

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

Использование списка в качестве базы данных

 

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

· столбцы списков становятся полями базы данных;

· заголовки столбцов становятся именами полей базы данных;

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

Корректировка списка

 

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

 

Изменение записей в списке с помощью Формы:

· выделите ячейку в списке, который подлежит корректировке;

· в меню Данные выберите команду Форма…;

· найдите запись, которую необходимо изменить;

· измените данные в записи. Используйте клавишу TAB для перемещения к следующему полю формы и сочетание клавиш SHIFT+TAB для перемещения к предыдущему полю;

· после изменения данных нажмите клавишу ENTER для обновления текущей записи и перехода к следующей записи;

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

Примечания:

- если поле списка содержит формулу, то в Форме выводится ее результат. Изменять это поле в Форме нельзя;

- при изменении записи, содержащей формулу, результат формулы не будет вычислен до нажатия клавиши ENTER или кнопки Закрыть;

- чтобы отменить изменения записи, нажмите кнопку Вернуть, пока эта запись активна;

- EXCEL автоматически обновляет запись при переходе к другой записи или закрытии Формы;

- для того чтобы добавить строку или столбец, в меню Вставка

выберите Строки или Столбцы.

Сортировка списка

 

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

Чтобы выполнить сортировку списка:

· в меню Данные выполнить команду Сортировка... (или щелкнуть по кнопке Сортировать по убыванию или Сортировать по возрастанию на панели инструментов);

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

 

 

Рисунок 12.2 - Диалоговое окно Сортировка диапазона

Фильтрация списка

 

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