Создание и ведение списков – ЧАСТЬ 5

СОВЕТ

Как видите, список типов данных не включает тип Текст. В списке есть  элемент Длина текста, но это не означает, что вводиться должен именно текст. Это условие просто создает ограничение по длине (например, от 5 до 50 символов), которое будет проверяться в случае ввода текста. Чтобы отсекать любые другие данные, кроме текста, выберите тип Другой и затем введите формулу =ЕТЕКСТ(адрес), где адрес — это ссылка на проверяемую ячейку.

Список допустимых значений

Для  определенного  типа данных  можно  перечислить  варианты  допустимых  значений, выбрав пункт Список в списке Тип данных и указав диапазон ячеек, занимаемый вашим списком. Допустим, вы хотите, чтобы в  ячейку А1 нельзя было вводить ничего иного, кроме значений Север, Юг,  Восток или  Запад. Тогда введите их в любой другой части рабочей  книги  —  например, в  ячейках  Z1:Z4.  Затем  выделите ячейку  А1,  выберите команду  Проверка  в  меню  Данные,  выберите вариант Список  в   раскрывающемся

списке Тип данных и укажите Z1:Z4 в качестве источника допустимых значений.

Проверка вводимых данных при помощи формулы

Чтобы использовать для диагностики данных формулу, выберите в списке  Тип данных пункт Другой и  введите формулу в появившемся поле  Формула (Formula). Формула должна ссылаться на проверяемую  ячейку  (при необходимости  и  на другие ячейки) и вырабатывать в качестве вычисляемого значения ИСТИНА (TRUE) или ЛОЖЬ (FALSE). Соответственно, чтобы убедиться, что значение в ячейке А1 больше, чем в В1, выделите ячейку А1 и наберите в поле Формула выражение =А1>В1.

В   случае,   когда   требуется   задать   более   одного   критерия проверки,   используйте  логические функции И (AND) и/или  ИЛИ (OR). Например,  критерию «С1 больше, чем А1, но меньше В1» соответствует формула =И(С1>А1,С1<В1).

Если перед выбором  команды Проверка выделен  диапазон ячеек,  Excel  надлежащим образом  обновит все  относительные ссылки. Так,  если вы  выделите блок  А1:А10 и введете выражение =А1>В1 в качестве  проверяющей формулы, программа гарантирует, что значение в ячейке А1 не будет больше В1, в А2 — больше В2 и т. д. Чтобы сделать ссылку абсолютной, добавьте в нужных местах знаки доллара. Например, для того чтобы все  значения  диапазона А1:А10  превышали величину в  В1,   измените формулу  на

=А1>$В$1. Как обычно, вы можете нажать клавишу F4 после ввода  ссылки на ячейку,

чтобы преобразовать ссылку в абсолютную.

Задание подсказки

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

Задание вида и текста сообщения об ошибке

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

На вкладке Сообщение об ошибке вы также можете выбрать вид  сообщения: Останов (Stop), Предупреждение (Warning) и Сообщение (Information). Это окажет влияние на значок,  отображаемый  рядом  с  текстом  уведомления, и   последовательность  действий пользователя. По умолчанию (вариант Останов) ему позволено заново ввести данные или отменить  ввод.   При  выборе   варианта   Предупреждение    пользователь   получит

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

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

Обычный  порядок  ввода  новой  информации  в  список —  в  первую  пустую  строку  в нижней его части. Но есть и  другой, куда более  удобный  путь — с помощью команды Форма   (Form)   меню    Данные.    Команда   Форма   генерирует  диалоговое   окно, облегчающее добавление данных в список и  выполнение других операций  со списком. Рисунок  28.12   демонстрирует  такую   форму   для   списка    сотрудников  из    ранее рассмотренного примера (см. рис. 28.1).

Рис. 28.12. Команда Форма генерирует диалоговое окно, облегчающее работу со списком

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

Почему  на  рис. 28.12  столбец  Возраст  представлен  не  полем?  Потому  что  это  вы-

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

Добавление строк

Чтобы добавить в список новую строку, нажмите кнопку Добавить (New). Форма будет очищена с  целью  ввода  значений  для  новой  записи.  Для  вставки еще  одной  строки

повторно  нажмите кнопку  Добавить.  Чтобы  вернуться  в  лист,  щелкните на  кнопке

Закрыть (Close).

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

Поиск записей

Для  поиска определенных  записей (строк)  в  списке служит кнопка  формы  Критерии (Criteria). При ее нажатии Excel открывает новую форму, включающую в себя поля ввода для всех столбцов, в том числе  и для вычисляемых  (рис. 28.13). Если вы введете критерии в несколько полей, то после щелчка на кнопке Назад (Find Next) или Далее (Find Prev) будут найдены записи, проходящие  по всем критериям. (Как вы увидите, в Excel есть и другие способы  поиска записей, в  том  числе удовлетворяющих  хотя  бы  одному  из заданных условий, а не всем вместе.) В варианте,  показанном на рис. 28.13, вы можете найти всех  сотрудников возраста  50  лет  и   младше,  чьи годовые  оклады  не  меньше

$40 000.

Рис.  28.13.  После  нажатия  кнопки  Далее  или  Назад  вы  перейдете  к   следующей  (или предыдущей) записи, удовлетворяющей обоим критериям

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

При вводе  условия  по  дате  допускается  использовать любой  формат,  который  Excel распознает в качестве даты. Например, чтобы получить информацию по самым молодым членам коллектива, родившимся  начиная с  1 января 1980 года, введите >1.01.80, >1

Январь 1980 или значение в любом другом из многочисленных форматов даты.

Сортировка списков и диапазонов

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

Сортировка по одному столбцу

Чтобы  отсортировать диапазон по  значениям одного  столбца,  например  по  столбцу Фамилия на рис. 28.1, выделите одну ячейку в этом  столбце. Затем нажмите кнопку Сортировка по возрастанию (Sort Ascending) на панели инструментов  Стандартная (Standard)  (для  упорядочивания  столбца  в  возрастающем  числовом или  алфавитном порядке) или  кнопку Сортировка по убыванию (Sort Descending) для  сортировки в обратном направлении. В результате данные будут расположены в заданном порядке по столбцу, в котором выделена  ячейка.  При выборе команды Сортировка (Sort) в меню Данные  (Data)  открывается  диалоговое окно  Сортировка  диапазона  (Sort).  Когда текущий  список  подвергается   этому   действию  в   первый   раз,   окно   Сортировка диапазона (рис. 28.14) настроено по умолчанию. Если список уже ранее сортировался, оно будет выглядеть, как в момент предыдущего вызова.

Рис. 28.14. Excel распознает размеры списка, а также наличие или отсутствие строки заголовков столбцов

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

диапазон данных по (My Data Range Has) в положение Подписям  (первая строка диапазона) (Header Row). И даже если программа по  какой-то причине  не заметила строки заголовка и выбрала вариант Обозначениям столбцов листа (No Header Row), это легко исправить, перед тем как нажать кнопку ОК.

Источник: Эффективная работа: Microsoft Office Excel 2003 / М. Додж, К. Стинсон — СПб.: Питер, 2005. — 1088 с: ил.

Похожие посты:

Вы можете оставить комментарий, или ссылку на Ваш сайт.

Оставить комментарий