Присвоение ячейкам и диапазонам имен

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

Работа с именами ячеек в Microsoft Excel 2010 осуществляется при помощи кнопок группы Определенные имена на вкладке Формулы ленты инструментов. Данная группа показана на рис. 11.4.

Чтобы присвоить имя ячейке или диапазону ячеек, следует выделить ячейку (ячейки) и воспользоваться кнопкой « Присвоить имя». Откроется диалоговое окно «Присвоение имени» (рис. 11.5). В поле «Имя:» сверху вводится имя ячейки, и, при желании, в поле «Примечание:» – ее текстовое описание.

В поле «Область:» задается область действия имени. Это может быть как отдельный лист книги, так и вся книга целиком. Ели в качестве области действия имени указан лист, использовать эти имя на других листах будет невозможно.

Также можно создавать имена, не указывающие ни на какие ячейки. В диалоговом окне присвоения имени в строку «Диапазон:» вместо ссылки на диапазон ячеек можно ввести любое значение, текст или формулу. Так можно организовать хранение в книге Microsoft Excel констант, не занимая под них отдельные ячейки. Для изменения такой константы ее значение редактируется в окне, аналогичном рис. 11.5. Напомним, что и формулы, и константы должны начинаться со знака «=»!

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

Имя ячеек не может начинаться с цифры, быть схожим с адресом ячейки (например, «FQ1999») и содержать пробелы или знаки препинания. Вместо пробелов используется символ «_» (подчеркивание). Прописные и строчные буквы в именах можно использовать, но Microsoft Excel их не различает.

В диалоговом окне рис. 11.5 и множестве других диалоговых окон Microsoft Excel имеются поля, в которые предполагается вводить ссылки на ячейки или диапазоны ячеек (строка «Диапазон:», например). В каких-то случаях удобно напечатать текст ссылки прямо в поле окна, но зачастую проще указать нужные ячейки мышью на экране. Однако само окно, занимая на экране место, может помешать выделить нужный диапазон ячеек. Освободить место позволяет кнопка  в конце поля. При нажатии на нее диалоговое окно «сворачивается»: от него остается лишь заголовок и строка для ввода ссылки. Мышью эту мини-версию окна можно перетащить в любое место экрана. Далее следует выделить ячейки (ввести текст ссылки вручную, тем не менее, также можно) и нажать кнопку  в конце строки. Диалоговое окно вернется к первоначальному виду, а строка окажется заполненной. При желании можно отредактировать ссылку и в поле полноразмерного диалогового окна.

Если таблица уже отформатирована, т.е. на листе находятся столбцы и строки с числовыми данными, а слева, справа, сверху или снизу от них находятся строки или столбцы с текстом (например, «Цена», «Количество», «Сумма»), существует возможность автоматически создать имена для диапазонов ячеек на основании этих названий. Следует выделить ячейки так, чтобы и значения, и текст входили в выделение, и воспользоваться кнопкой « Создать из выделенного». Откроется диалоговое окно рис. 11.6. В окне следует установить флажки тех сторон выделенного диапазона, где лежат нужные названия. После нажатия кнопки OK будут созданы имена. Пустые ячейки будут проигнорированы. Текст из ячеек-заголовков при этом никуда не исчезнет.

Имена ячеек можно использовать в формулах. Если строке 3 было присвоено имя «Строка_3», а столбцу D – имя «Столбец_4», то ссылку на ячейку D3 можно записать как «=Столбец_4 Строка_3» (напомним, что пробел в формулах Microsoft Excel – это оператор пересечения). Если ссылка лежит в том же столбце (или той же строке), что и ячейка, на которую ссылаются, в ссылке достаточно указать имя строки (или столбца). Например, ссылка на ячейку D3 из ячейки F3 будет иметь вид «=Столбец_4» (т.е. пересечение «Столбца 4 » со строкой, в которой лежит ссылка), а ссылка из ячейки D8 – «=Строка_3» (т.е. пересечение «Строки 3» со столбцом, в котором записана ссылка). Следует помнить, что такие сокращенные ссылки будут работать только в тех столбцах (строках), для которых созданы имена. Если в примере выше были поименованы столбцы с А по H («Столбец_1» – «Столбец_8» соответственно), то ссылка вида «=Строка_3» из ячейки H8 будет работать, а из ячейки K8 – уже нет.

Также удобно использовать имена диапазонов ячеек. Например, если ячейки диапазона B4:F16 содержат среднегодовые температуры по годам и городам, можно присвоить имя «Температура» всему диапазону, и вместо формулы «=СРЗНАЧ(B4:F16)» записать формулу «=СРЗНАЧ(Температура)». Во втором случае при записи формулы допустить ошибку намного сложнее.

При составлении формул не обязательно набирать имена ячеек или диапазонов с клавиатуры. Можно воспользоваться кнопкой « Использовать в формуле». Кнопка раскрывается в список имен ячеек. Если нужного имени в списке нет, можно воспользоваться командой Вставить имена… внизу списка. Откроется диалоговое окно, которое содержит полный список имен, выбрать имя, и оно будет вставлено в активную ячейку как абсолютная ссылка. При создании или редактировании формулы это же меню позволяет вставить имя в любое место формулы (определяется положением курсора ввода в строке формул).

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

Флажок «Использовать имена строк и столбцов» разрешает или запрещает использовать имена, созданные через диалоговое окно рис. 11.6. Также в исходном состоянии окна присутствует кнопка «Параметры», которая разворачивает окно (на рис. 11.7 оно показано в развернутом виде), добавляя дополнительные опции. Нижние флажки разрешают или запрещают получать в результате замены сокращенные ссылки (вида «=Строка_3» для ссылок на ячейки из того же столбца, см. пример выше). Переключатель в самом низу позволяет выбрать порядок следования имен. От этого может зависеть удобство чтения формул с именами.

Для централизованного управления именами ячеек служит диалоговое окно, показанное на рис. 11.8. Для вызова этого окна служит кнопка « Диспетчер имен» в той же группе Определенные имена вкладки Формулы. Основную часть окна занимает таблица, где перечислены все именованные ячейки и диапазоны и приведены их свойства.

Как было показано в Лабораторной работе №10, применение к диапазону ячеек стиля таблицы превращает этот диапазон в оформленную таблицу Microsoft Excel. Особенности работы с таблицами будут рассмотрены в Лабораторной работе №14, сейчас же отметим, что таблица также имеет свое имя. Это имя отображается в диалоговом окне рис. 11.8 со значком  (имена ячеек и диапазонов отображаются со значком ).

Щелчком по заголовкам столбцов таблицы в окне рис. 11.8 можно изменить порядок сортировки полей в таблице. Кнопка «Фильтр» позволяет применять к строкам в окне фильтрацию – отображать не все именованные поля, а только некоторые из них. В частности, доступны следующие режимы фильтрации: Имена на листе/Имена в книге – показать либо те имена, областью действия которых является отдельные листы, либо те, область действия которых – вся книга; Имена с ошибками/Имена без ошибок – показать либо только те именованные ячейки, вычисление формул в которых привело к возникновению ошибок (см. Лабораторную работу №12), либо только те, при вычислении значений которых ошибок не возникло; Определенные имена/Имена таблиц – показывать только имена ячеек и их диапазонов, как введенных вручную, так и созданных автоматически, либо показывать только имена таблиц. Microsoft Excel позволят применять несколько фильтров одновременно.

Рис. 11.8 Диалоговое окно диспетчера имен ячеек и диапазонов

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

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

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

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

Если перед открытием окна рис. 11.9 и нажатием кнопки «Выделить» вручную выделить диапазон ячеек, то после нажатия «ОК» в окне рис. 11.9 останутся выделенными только те ячейки из этого диапазона, которые удовлетворяют заданным в окне условиям. Если предварительно ничего не выделять, выделение по условиям применится ко всему листу.

Диалоговое окно рис. 11.10 позволяет выделять следующие типы ячеек:

Ø ячейки с примечаниями;

Ø ячейки с константами – значениями, непосредственно введенными пользователем;

Ø ячейки с вычисляемыми формулами (и константы, и формулы могут быть числовыми, текстовыми, логическими, также можно выделять формулы с ошибками; см. Лабораторную работу №12);

Ø пустые ячейки;

Ø области, в которых содержится активная ячейка (областью называется группа ячеек с содержимым, отделенная от других групп ячеек как минимум одной пустой строкой или столбцом);

Ø массивы, т.е. группы ячеек, содержимое которых вычисляется при помощи общей формулы массива, см. выше;

Ø вставленные в книгу объекты OLE (рисунки, формулы Equation и т.д.);

Ø ячейки в группе, значения которых отличаются от значений в первом или последнем столбце группы (пункт «Отличия по строкам», если выделение осуществлено слева направо, то ищутся отличия от первого столбца, если справа налево – то от последнего);

Ø ячейки в группе, значения которых отличаются от значений в первой или последней строке группы (пункт «Отличия по столбцам»);

Ø ячейки, значения которых посредством ссылок используются в формулах, хранящихся в выделенной группе ячеек (пункт «Влияющие ячейки», см. Лабораторную работу №12);

Ø ячейки, в формулах которых посредством ссылок используются значения из выделенной группы ячеек (пункт «Зависимые ячейки»);

Ø последнюю ячейку листа, т.е. ячейку на пересечении последних заполненных строки и столбца;

Ø только видимые ячейки (ячейки из скрытых строк и столбцов не будут выделены; удобно, когда нужно скопировать в другое место таблицу без скрытых строк или столбцов);

Ø ячейки с условным форматированием (см. ниже);

Ø ячейки с условиями на ввод данных (см. ниже).