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

Чтобы эффективно работать со списком, следуйте изложенным ниже правилам:

? в   верхней   строке   каждого   столбца   должен   находиться   уникальный   заголовок,

характеризующий содержимое столбца;

? каждый столбец должен содержать информацию одного типа;

? под данные каждого типа, по которым вы хотите осуществлять сортировку, поиск или другие операции, нужно отвести отдельный столбец;

? в списке не должно быть пустых строк или столбцов;

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

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

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

Здесь  мы  использовали  команду  Закрепить  области  (Freeze  Panes)  для  фиксации верхней строки, чтобы она все время оставалась на экране при прокрутке списка, а линия, расположенная ниже верхней строки, является границей закрепленной области.

СМОТРИ ТАКЖЕ

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

«Закрепление областей» главы 5.

Для  целостного  восприятия  списка  лучше  выделить  текст  в  верхней  строке  жирным шрифтом.  Это  удобно  для  всех,  кто  работает  со  списком,  включая  вас  самих,  но  не обязательно.  Поскольку  первая  и   вторая  строки  в  списке  на  рис.  28.1  содержат информацию различного типа — например, в столбцах С и D в первой строке содержится текст,  а  ниже  —  даты,  Excel  распознает  верхнюю  строку  как  строку  заголовков  и учитывает это при сортировке списка. Если  вы  создадите сводную таблицу на основе списка, его заголовки в верхней строке будут задействованы в качестве названий полей.

Использование заголовков в формулах

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

на  365,25  (число  дней  в  году)  и  округления  полученного  результата  до  ближайшего целого при помощи функции ЦЕЛОЕ (INT). В каждой  ячейке этого столбца, начиная с G2, записана формула

=ЦЕЛ0Е((СЕГОДНЯ()-Дата рождения)/365.25) Она

примечательна в нескольких отношениях.

Первая  и  наиболее  важная  особенность  состоит  в  том,  что  в  формуле  фигурирует заголовок  столбца  D  (Дата  рождения)  для  извлечения  значений  из  этого  столбца. Использование  заголовка  вместо  точных  ссылок  D2,  D3  и  т.  д.  облегчает  чтение  и понимание формулы. Причем мы не обязаны определять текст Дата рождения в качестве имени диапазона D:D (или любой части столбца D). Поскольку  этот текст находится в ячейке D1, Excel понимает, что он относится ко всем нижележащим ячейкам списка.

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

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

В-третьих, хотя символ пробела формально является оператором пересечения (например, формула =7:7 В:В возвратит «Евгений» —  значение ячейки на пересечении строки 7 и столбца В), Excel без всяких возражений принимает заголовок столбца Дата рождения и не  пытается  найти  пересечение  диапазонов  с  именами  Дата  и  рождения.  В  этом отношении    механизм    интерпретации    формул,    содержащих    заголовки,    является дружественным  и  бесконфликтным.  (Чего  нельзя  сказать  о  более  старом  механизме присвоения имен диапазонам, то есть о команде Вставка ? Имя ? Присвоить (Insert

? Name ? Define). В именах, определенных отсюда, символы пробела недопустимы.)

Включение режима использования заголовков в формулах

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

1.   В меню Сервис (Tools) выберите команду Параметры (Options).

2.   В диалоговом окне Параметры (Options) откройте вкладку Вычисления

(Calculation).

3.   На вкладке Вычисления (рис. 28.2) установите флажок Допускать названия диа-

пазонов (Accept Labels In Formulas).

Рис. 28.2. Удобные формулы Excel, понимающие заголовки, не будут  работать, пока вы не установите флажок Допускать названия диапазонов

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

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

Включение режима использования заголовков в формулах для всех книг

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

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

2.   В меню Файл (File) выберите команду Сохранить (Save). В списке Тип файла (Save As Type) диалогового  окна  Сохранение  документа (Save As) выберите  вариант Шаблон (Template).

3.   Сохраните файл под именем Книга (Book) в папке XLStart,  вложенной в папку, в которой   находится   файл   Excel.exe.   (В   процессе   стандартной   установки   Office исполняемый  файл  Excel  обычно  помещается  в  папку  C:\Program Files\ Microsoft Office\ Office11.) При отсутствии в вашей системе папки XLStart просто создайте ее.

Если папка XLStart содержит шаблон Книга.xlt (Book.xlt), он становится  образцом для всех новых книг.

ВНИМАНИЕ

Хотим  предостеречь  от  соблазна  применить  заголовки  строк  из  столбца  А  в  формулах столбца G на рис. 28.1. Например, содержимое ячейки G2 мы можем переписать как

=ЦЕЛ0Е((СЕГОДНЯ()-Пашков Дата рождения)/365,25)

В итоге формула станет даже более ясной, a Excel успешно распространит новую ее версию до конца столбца, так что в ячейке G3 окажется выражение

=ЦЕЛ0Е((СЕГ0ДНЯ()-Андреева Дата рождения)/365,25)

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

Вспомогательные средства Excel для создания списков

Excel предоставляет несколько вспомогательных средств, облегчающих создание списков:

? автоматическое распространение форматов и формул;

? автозавершение ввода значений в ячейки;

? переход по клавише Enter;

? пользовательские списки.

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

Автоматическое форматирование по имеющемуся образцу

По мере создания или расширения списка Excel осуществляет поиск образцов, на которые он  может  опереться  при  форматировании.  Если   вы,  например,  вводите  в  столбец

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

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

ПРИМЕЧАНИЕ

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

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

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

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

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