Материал: 5703

6

например, найти студентов, под фамилией «Иванов», можно осуществить поиск в столбце под названием "Фамилия".

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

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

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

Если какому-либо диапазону ячеек на текущем листе присвоено имя "База_данных", Excel будет считать этот диапазон списком. Списком может являться любая таблица данных. Таким же образом, в качестве диапазона критериев и диапазона для извлечения могут выступать любые диапазоны, именованные или нет.

Excel определит диапазон базы данных следующим образом. Будет сделана попытка найти самую верхнюю строку, содержащую текст. В случае успеха эта строка станет строкой заголовков, а все непустые строки под заголовками будут рассмотрены как список. Если текстовая строка не найдена, в качестве списка будут выделены все соседние непустые ячейки вокруг активной. В любом случае самая верхняя строка найденного диапазона интерпретируется как строка имен полей, нижележащие строки – как строки данных.

При создании списков следует придерживаться таких рекомендаций:

Над списком всегда располагайте строку заголовков с уникальным именем для каждого поля.

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

Если вы не хотите использовать имя "База_данных" или создаете несколько списков, то располагайте каждый создаваемый список так, чтобы он был ограничен краями рабочего листа и пустыми

7

ячейками, либо так, чтобы со всех сторон списка были пустые ячейки. В этом случае по одной выделенной ячейке Excel сможет автоматически определить весь список.

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

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

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

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

Рекомендуется располагать на рабочем листе только один список, так как в рабочей книге допустимо иметь много листов, содержащих по одному списку. Не рекомендуется дублировать информацию в списках и базах данных. Впоследствии с помощью Excel можно реорганизовать файлы данных оптимально. Если количество записей (строк) в списке измеряется тысячами, пользуйтесь средствами Microsoft Access вместо Excel. Access может эффективно обрабатывать большие массивы данных и при необходимости экспортировать отфильтрованную информацию в виде рабочих листов Excel.

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

Если списку присвоено имя "База_данных" или некоторое другое, следует добавлять строки внутрь диапазона или использовать для этих целей

8

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

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

Имена полей должны удовлетворять следующим требованиям. Имена полей не должны превышать 255 символов. Только названия непосредственно над данными используются в качестве имен полей. Можно помещать дополнительный текст в строках выше, но Excel при обработке списка этот текст использовать не будет. Чтобы правильно работал фильтр, имена полей должны быть уникальными, т. е. не должны совпадать друг с другом. Не вставляйте между строкой заголовков и данными строку с пустыми ячейками или с пунктирной линией.

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

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

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

Вкачестве диапазона критериев может использоваться любой диапазон ячеек. Ему можно присвоить любое имя. Если на листе определить имя "Критерии", то соответствующие этому имени ячейки будут использоваться в качестве диапазона критериев по умолчанию. Точное соответствие имен полей списка и имен, указанных и диапазоне критериев», проще всего обеспечить с помощью команд Копировать и Вставить.

10

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

Структура таблицы

Заполните вашу таблицу десятью позициями продаж, представленными на Рис. 1.

Лист 2Форма. Скопируйте таблицу на второй лист книги. Зайдите курсором в диапазон данных. Выберите команду Данные – Форма. Запустится форма с вашими заголовками, пустые поля рядом с каждым заголовком предназначены для ввода данных в соответствующий столбец списка. Форма облегчает ввод данных в большие базы, например с большим количеством столбцов, которые не помещаются на экране. Через форму можно ввести новые данные, найти строки по заданному условию, обновить имеющиеся данные и удалить строки из списка. Используя кнопки Добавить, Удалить, введите через форму пять следующих позиций продаж, смотри