Клецова Информационные технологии електронные таблицы и 2011

Рис.2.5

Рис.2.6 51

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

ние об ошибке в диалоговом окне Проверка вводимых зна-

чений ввести текст сообщения об ошибке в поле Сообщение и, по желанию, заголовок сообщения об ошибке в поле Заголовок

(рис.2.7).

Рис.2.7

На вкладке Сообщение об ошибке также можно выбрать вид сообщения: Останов, Предупреждение, Сообщение.

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

52

оставить это значение в ячейке. В случае типа Сообщение пользователь увидит диагностическое сообщение с запретом на повторный ввод.

Формы при работе со списками

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

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

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

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

Кнопка Форма отсутствует на Ленте. Но ее можно добавить на панель быстрого доступа. Для этого нужно выполнить пункт меню Office-Параметры Excel, на вкладке Настройка в поле Вы-

брать команды из выбрать опцию Все команды, в левом списке выбрать элемент Форма. Нажатием на кнопку Добавить элемент будет отображен в правом списке (перемещен на панель быстрого доступа). Для сохранения операции необходимо нажать на кнопку ОК.

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

53

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

В верхней части формы Excel выводит имя листа (а не книги), содержащего таблицу, для которой предназначена эта форма. Непосредственно ниже строки заголовка окна выстраиваются поля согласно заголовкам столбцов таблицы (рис.2.8). Форма позволяет одновременно отображать до 32 полей таблицы. Если в список уже введены строки, справа от заголовков столбцов будут выведены значения из первой строки таблицы. (При открытии формы текущей становится первая запись, независимо от того, какая ячейка в таблице в настоящий момент активна) [1].

Чтобы добавить в таблицу новую строку, необходимо нажать на кнопку Добавить. Форма будет очищена с целью ввода значений для новой записи. После щелчка на кнопке Добавить или Закрыть, введенные данные появятся в первой пустой строке таблицы в конце таблицы. Можно также нажать клавишу Enter, что эквивалентно щелчку на кнопке Добавить. Если таблица содержит формулы, то они автоматически появятся в новых записях.

Рис.2.8

Форму ввода можно использовать не только для ввода данных. Она позволяет редактировать существующие записи, просматри-

54

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

Форма ввода содержит следующие дополнительные кнопки:

•Удалить. Удаляет текущую запись.

•Вернуть. Отменяет все внесенные в текущую запись изменения. Эта кнопка работает до тех пор, пока не будет нажата кноп-

ка Добавить.

•Назад. Осуществляется переход к предыдущей записи таблицы. Если установлен критерий отбора, то будет осуществлен переход к предыдущей записи, удовлетворяющей данному критерию.

•Далее. Осуществляется переход к следующей записи, удовлетворяющей данному критерию.

•Критерии. Очищает поля для ввода критериев, по которым будут отбираться записи. После этого можно использовать кнопки Назад и Далее, чтобы найти подходящую запись.

•Закрыть. Закрывает форму и записывает введенные данные

врабочий лист.

При изменении записи, содержащей формулу, результат формулы не будет вычислен до нажатия клавиши Enter или кнопки Закрыть). Для перемещения к следующему полю формы можно использовать клавишу Tab, а для перемещения к предыдущему полю – сочетание клавиш Shift+Tab. После изменения данных необходимо нажать клавишу Enter для обновления текущей записи и перехода к следующей записи. По завершению изменения записей необходимо нажать кнопку Закрыть для выхода из формы.

При нажатии кнопки Критерии Excel открывает новую форму, включающую в себя поля ввода для всех столбцов. Если пользователь введет критерии в несколько полей, то после щелчка на кнопке Назад или Далее будут найдены записи, проходящие по всем критериям. При вводе текстового условия программа присоединит к нему неявный символ подстановки «звездочка», т.е. если набрать букву Б в поле Фамилия, то Excel будет искать все фамилии, начинающиеся с буквы Б.

55

Промежуточные итоги в анализе списков

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

При выборе команды Промежуточные итоги раздела Структура вкладки Данные на экране отобразится диалоговое окно Промежуточные итоги (рис.2.9).

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

Рис.2.9 56

В результате на листе появятся промежуточные итоги по введенным пользователем значениям. Слева от списка появятся знаки уровня («плюс» и «минус»). Их можно использовать для изменения состава отображения данных согласно их уровню.

Рис.2.10

Рис.2.11

Рис.2.12 57

Чтобы показать только общие итоги, необходимо щелкнуть на знаке строки уровня 1 (рис.2.10). Чтобы показать только промежуточные результаты и общие итоги, необходимо щелкнуть на знаке уровня 2 (рис.2.11), чтобы показать весь список, промежуточные результаты и общие итоги, необходимо щелкнуть на знаке уровня

3 (рис.2.12).

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

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

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

Отфильтровать список – значит скрыть все строки за исключением тех, которые удовлетворяют заданным условиям отбора. В Excel доступны три типа фильтров: Автофильтр для простых условий отбора, Настраиваемый (пользовательский)

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

Автофильтр

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

Если выбрать во вкладке Данные Ленты в разделе Сортировка и фильтр пункт меню Фильтр, то Excel отобразит кнопки со стрелками (кнопки автофильтра) рядом с каждым заголовком столбца (см. рис.2.2). При создании из диапазона ячеек таблицы Excel автоматически для каждого столбца отобразит кнопки автофильтра. Щелчок на кнопке автофильтра раскрывает список уникальных значений в этом столбце, предлагая использовать их для задания условий отбора строк (рис.2.13).

Список значений может содержать до 10000 строк. В списке значений выделенного диапазона или столбца около значений, которые должны быть отображены, необходимо поставить «галоч-

58

ку», а около значений, которых нужно скрыть, «галочку» нужно снять.

Рис.2.13

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

После задания условия отбора строк в автофильтре на экране отобразятся только те строки, которые соответствуют заданному условию, остальные строки будут скрыты (рис.2.14).

Рис.2.14

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

59

Для удаления всех кнопок автофильтра необходимо во вкладке

Данные Ленты в разделе Сортировка и фильтр повторно выбрать пункт меню Фильтр. Для таблицы Excel (базы данных) кнопки автофильтра будут сняты только после преобразования таблицы в диапазон ячеек.

Настраиваемый (пользовательский) фильтр

Настраиваемый (пользовательский) фильтр открывается также из панели автофильтра. Для него предназначены команды, расположенные немного выше списка значений автофильтра. Эти команды отличаются друг от друга, в зависимости от формата данных, содержащихся в выделенных ячейках [3].

Например, для поля формата «дата» , Excel предлагает возможные критерии отбора, отображенные на рис.2.15.

При выборе одной из опций, например, «после», откроется окно для ввода значений критериев (рис.2.16).

Рис.2.15

При задании условий на текстовые значения можно использовать подстановочные знаки: знак вопроса (?) – для замещения лю-

60