Заполнение и редактирование таблицы с помощью форм

Для того, чтобы таблица могла быть распознана Microsoft Excel в качестве базы данных, достаточно наличия строки с заголовками полей. Создав ее, и, по возможности, заполнив первую строку-запись, можно приступать к вводу остальных данных в таблицу. Можно делать это вручную, но многим пользователем удобно пользоваться формой – специальным диалоговым окном (рис. 14.3). Особенность формы в том, что Microsoft Excel автоматически распознает структуру базы данных на основании введенных формул и предлагает для редактирования только те поля, которые содержат первичные (вводимые, а не вычисляемые) данные. То есть при заполнении таблицы через окно формы внести изменения в ячейки с формулами нельзя!

В Microsoft Excel 2010 кнопка « Форма» для отображения диалогового окна формы по умолчанию не вынесена ни на одну из вкладок ленты инструментов. Однако ее можно добавить на панель быстрого доступа (она находится в категории «Все команды»).

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

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

При работе с диалоговым окном форм следует быть внимательным: правки, внесенные при помощи окна в базу данных, в том числе удаление строк, отменить нельзя! Такое поведение присуще большинству программ класса СУБД. Если же база данных была предварительно оформлена как таблица, стандартное средство отмены ошибочных действий будет работать.

Фильтрация

Особенностью любой базы данных, даже самой простой, является то, что записей в ней достаточно много. Часто возникает необходимость отобразить на экране только те из них, которые удовлетворяют некоторому условию (например, значение поля «Результат» меньше или равно нулю). Можно вручную изучить таблицу и скрыть ненужные строки, но это трудоемкий и длительный процесс. Автоматическое отображения только нужных записей в базе данных называется фильтрацией.

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

В диалоговом окне формы (рис. 14.3) фильтрация осуществляется с помощью кнопки «Критерии». При нажатии на эту кнопку в полях отображаются ранее введенные критерии фильтрации (если фильтрация задается в первый раз, то поля пустые). После ввода необходимых критериев (например, ввод «>5000» в поле «Цена») можно нажимать кнопки «Назад» и «Далее» для навигации по отобранным записям. Для перехода из режима редактирования критериев к последней просматриваемой записи служит кнопка «Правка».

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

Наиболее простым и интуитивно понятным способом фильтрации записейявляется Автофильтр. Если выделить любую ячейку таблицы или базы данных и воспользоваться кнопкой « Фильтр» в группе Сортировка и фильтр вкладки Данные ленты инструментов, то рядом с именами полей появятся кнопки . При нажатии на кнопку раскрывается список (рис. 14.4), содержащий команды сортировки (см. ниже) и фильтрации. В частности, можно фильтровать ячейки по цветам заливки (подменю Фильтр по цвету; это особенно удобно, если заливка была сформирована средствами условного форматирования) и по значению (Числовые фильтры для ячеек с числовыми значениями, Текстовые фильтры для ячеек с текстом, Фильтр по дате для ячеек с датами или временем).

Числовые фильтры позволяют находить ячейки, число в которых равно, не равно, больше, меньше, больше или равно, меньше или равно заданному, а также лежит в диапазоне между двумя заданными. Все соответствующие команды открывают диалоговое окно рис. 14.5, в котором в верхнем выпадающем списке уже выбран нужный оператор (в случае команды Между … в верхнем списке стоит «Больше или равно», а в нижнем – «Меньше или равно»).

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

Фильтр по дате позволяет отображать ячейки с датами, равными заданной, предшествующими ей или следующими за ней, ячейки, лежащие в интервале между двумя заданными датами. Также можно найти ячейки, относящиеся к предыдущему, текущему или следующему дню, неделе, месяцу, кварталу, году, ячейки с датой от начала текущего года и до текущей даты, а также все даты в выбранном месяце или квартале текущего года. Выбор команд подменю Равно…, До…, Между…, После… также открывает окно, аналогичное рис. 14.5, только справа от полей в нем присутствуют кнопки «». Нажатие на кнопку открывает небольшое окошко с календарем, упрощая ввод конкретной даты в правое поле.

Команда Настраиваемый фильтр… из подменю меню команд Числовые фильтры,Текстовые фильтры или Фильтры по дате также открывает окно, аналогичное рис. 14.5. В любом режиме в окне можно задать два условия. Условия должны выполняться одновременно (связь «И»), либо достаточно выполнения одного из заданных условий (связь «ИЛИ») для отображения записей. Отметим, что в выпадающих списках окна присутствуют условия как числовой, так и текстовой фильтрации. Помимо рассмотренных выше, здесь присутствуют также текстовые условия «Не начинается с» и «Не заканчивается на».

Команда Первые 10… в подменю числовых фильтров позволяет отобразить самые большие или малые значения в таблице. В ее диалоговом окне (рис. 11.6) можно задать непосредственно количество отображаемых элементов или определенный процент от общего количества записей в таблице (например, 10 элементов в таблице из 600 записей даст 10 строк, а 10% – 60 строк). Здесь также указывается, нужны ли самые большие или, наоборот, самые малые элементы.

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

Также в меню кнопки автофильтра (рис. 14.4) присутствует список всех встречающихся значений поля со строкой поиска. Можно установить флажки на любых значениях, чтобы фильтровать таблицу именно по ним, либо снять некоторые флажки, чтобы скрыть соответствующие строки таблицы. Установка флажка «Выделить все» автоматически устанавливает флажки на всех значениях, отключая фильтрацию.

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

Если автофильтр был настроен любым способом, его кнопка в соответствующем поле примет вид . Флажок «Выделить все» в выпадающем списке автофильтра или команда Удалить фильтр с имени поля там же снимают все условия фильтрации и отображают все записи списка, но при этом кнопки  автофильтра не исчезают. Также можно нажать кнопку « Очистить» в группе Сортировка и фильтр вкладки Данные. Чтобы скрыть кнопки автофильтра, необходимо отжать кнопку « Фильтр» на вкладке Данные.

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

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

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

Пример диапазона условий показан на рис. 14.7. Будут оставлены на экране только те записи, где дата лежит в пределах 27-29 мая 2011г, точкой продажи являются Магазины №1 и №3, а количество проданных телефонных аппаратов марки Samsung не менее 3 шт. Так как имеется два магазина, которые должны быть связаны связью «ИЛИ», они займут две строки, тогда однострочные условия по дате и объему продаж придется скопировать в обе строки диапазона.

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

Рис. 14.7 Пример диапазона условий

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

При задании условий можно использовать формулы с функциями (должны возвращать логическое значение, т.е «ИСТИНА» или «ЛОЖЬ»). Используемая в условии формула должна находиться в столбце-поле диапазона условий, для которого задается условие, ссылки в ней должны указывать на первую запись таблицы. Если формула включает ссылки на несколько столбцов-полей, ее необходимо разместить в отдельном столбце диапазона условий, ячейка первой строки которого (ячейка для имени столбца) должна быть пустой.

В окнах рис. 14.4 или рис. 14.5 для задания условий можно использовать специальные символы. Символ «*» (звездочка) обозначает любое количество любых символов; символ «?» (знак вопроса) – один любой символ. Данные символы можно комбинировать с любыми буквами, цифрами и другими символами. Например, ввод условия «Петр*» в поле «Фамилия» позволит найти в оформленной таблице или базе данных всех людей с фамилиями Петр, Петро, Петров, Петровский, Петрович, Петрушевич и т.д. Если в качестве простых символов нужно будет указать символы «*» или «?», их заменяют на комбинации «~*» и «~?» соответственно.

Флажок «Только уникальные записи» в окне рис. 14.8 полезен, если в списке содержится несколько одинаковых строк (записей). Установка флажка заставляет Microsoft Excel отобразить только первую из повторяющихся записей. Установка этого флажка без указания диапазона условий позволяет скрыть все повторения в таблице.

Для оформленных таблиц существует и более радикальный способ избавиться от дублирующихся записей: их можно полностью удалить. Для этого служит кнопка « Удалить дубликаты» в группе Сервис вкладки Конструктора таблиц. Будет открыто диалоговое окно (рис. 14.9), в котором перечислены поля таблицы (если у таблицы есть заголовки и в окне справа стоит флажок) или адреса столбцов. По умолчанию все флажки напротив полей установлены – в этом случае при нажатии на кнопку ОК будкт удалены (кроме самой первой) полные дубликаты – записи, у которых все поля совпадают. Если установлено несколько флажков – будут удаляться только записи с совпадающими соответствующими полями. Наконец, если оставить флажок только напротив одного поля – Microsoft Excel оставит в таблице по одной записи на каждое значение поля. Кнопки вверху окна позволяют быстро снять или поставить все флажки сразу.

Рис. 14.9 Диалоговое окно удаления дубликатов из оформленной таблицы

Для отключения расширенного фильтра также используется кнопкой « Очистить» в группе Сортировка и фильтр вкладки Данные.

Microsoft Excel не позволяет комбинировать автофильтр и расширенный фильтр. При включении расширенного фильтра автофильтр будет автоматически отключен (а кнопки  – скрыты), и наоборот, включение автофильтра отменяет расширенный фильтр.

Как автофильтр, так и расширенный фильтр в Microsoft Excel носят статический характер: содержимое полей проверяется на соответствие условиям лишь непосредственно в момент фильтрации. Если в предварительно отфильтрованную оформленную таблицу или базу данных добавить записи, которые, согласно условию фильтрации, не должны отображаться, автоматического их сокрытия не произойдет. В этой связи в группе Сортировка и фильтр вкладки Данные имеется кнопка « Повторить». Нажатие на нее приводит к повторной фильтрации оформленной таблицы или базы данных по заданным ранее условиям, иными словами, обновление фильтра. Поскольку при отключении фильтров кнопкой « Очистить» условия фильтрации утрачиваются, кнопка « Повторить» также становится неактивной.