Использование ссылок в формулах
Ссылка является идентификатором ячейки или группы ячеек в книге. При создании формул, содержащих ссылки на ячейки, формула связывается с ячейками книги. Значение формулы зависит от содержимого ячеек, на которые указывают ссылки, и оно изменяется при изменении содержимого этих ячеек.
С помощью ссылок в формулах можно использовать данные, находящиеся в различных местах листа, или использовать значение одной и той же ячейки в нескольких формулах. Кроме того, можно ссылаться на ячейки, находящиеся на других листах книги, или в другой книге, или даже на данные другого приложения.
В Excel существуют три типа ссылок: относительные, абсолютные, смешанные.
Относительная ссылка указывает на ячейку, основываясь на ее положении относительно ячейки, в которой находится формула, например «на две строки выше». При перемещении формулы относительная ссылка изменяется, ориентируясь на ту позицию, в которую переносится формула. Например, если в клетке С1 записано =А1+В1, то при копировании ее в клетку С2 формула будет иметь следующие относительные ссылки =А2+В2; при копировании в D1 запишится =В1+С1.
Абсолютными являются ссылки на ячейки, имеющие фиксированное расположение на листе. Эти ссылки не изменяются при копировании и при перемещении формул. Абсолютная ссылка содержит знак $ перед именем столбца и именем строки. Например, в ячейке С1 записано: =$A$1+$B$1. Тогда при копировании в С2 там получится = $A$1+$B$1.
Смешанные ссылки - это ссылки, являющиеся комбинацией относительных и абсолютных ссылок. Например, $D7.
Примеры ссылок: А:А – ссылка на все ячейки столбца А;
1:3 – ссылка на все ячейки строк 1, 2, 3;
Ссылки на ячейки других листов книги имеют следующий формат:
имя раб.листа! ссылка на ячейку, например, Лист2!А1:А10.
Если имя рабочего листа содержит пробелы, то оно заключается в одинарные кавычки, например, ‘лицевой счет’!А1:А10.
Excel позволяет ссылаться на диапазон ячеек нескольких рабочих листов. Такая ссылка называется объемной. Например, Лист1:Лист5!$A$1:$D$3.
Ссылки на ячейки других книг имеют следующий формат:
[имя книги]имя раб.листа! ссылка на ячейку, например: [книга2]Лист3!Е5:Е15.
Excel позволяет использовать в формулах, создаваемых в рабочих листах, заголовки вместо ссылок. В этом случае для идентификации ячеек, находящихся в строках и столбцах таблицы, используются соответствующие заголовки. Когда вместо ссылок формула содержит текст, Excel поступает следующим образом:
- если формула содержит заголовок столбца или строки, Excel считает, что пользователь хочет использовать диапазон ячеек, расположенных ниже заголовка столбца таблицы или справа от заголовка строки;
- если формула содержит заголовок столбца (или строки), отличного от того, в котором она находится, Excel считает, что пользователю нужна ячейка, расположенная на пересечении столбца (или строки) с таким заголовком и строки (или столбца), где расположена эта формула.
Формулы, содержащие заголовки, можно копировать и вставлять. При этом Excel автоматически настраивает их на нужные столбцы и строки. При изменении заголовков столбцов и строк таблицы Excel автоматически изменяет и заголовки, использовавшиеся в формулах.
Следует всегда оставлять пустую строку снизу и пустой столбец справа от таблицы, содержащей формулы, в которых используются заголовки. В противном случае результаты могут быть непредсказуемыми, особенно если имеется несколько уровней итоговых формул.
Формулы, содержащие заголовки, можно применять только в том же листе, где находится таблица.
Диапазону ячеек и ячейке можно присвоить имя, причем иногда присвоение имени ячейкам или диапазонам ячеек в формулах облегчает работу по расшифровке ссылок в них.
Использование имен ячеек или диапазонов ячеек позволяет уменьшить вероятность появления ошибок в формулах.
Чтобы присвоить имя ячейке или диапазону:
нужно выделить ячейку или диапазон ячеек и выбрать пиктограмму Присвоить имя группы Определенные имена вкладки ФОРМУЛЫ выбрать команду Присвоить имя при этом появитьсяокно Созданиеимени (рис. 9.6);
ввести имя ячейке или диапазону ячеек, причем, если в имени содержится пробел, то нужно использовать вместо пробела подчеркивание, и нажать кнопку ОК.
Рис.9.6. Диалоговое окно Создание имени.
Основные функции Excel
Функция – это специальная заранее подготовленная формула, которая выполняет операции над заданными значениями и возвращает результат. Значения, над которыми функция выполняет операции, называются аргументами. В качестве аргументов могут выступать числа, текст, логические значения, ссылки. Аргументы могут быть представлены константами или формулами. Формулы в свою очередь могут содержать другие функции, т.е. аргументы могут быть представлены функциями. Функция, которая используется в качестве аргумента, является вложенной функцией. Excel допускает до семи уровней вложения функций в одной формуле.
В общем виде любая функция может быть записана в виде:
=<имя_функции>(аргументы)
Существуют следующие правила ввода функций:
имя функции всегда вводится после знака «=»;
аргументы заключаются в круглые скобки, указывающие на начало и конец списка аргументов;
между именем функции и знаком «(« пробел не ставится;
вводить функции рекомендуется строчными буквами. Если ввод функции осуществлен правильно, Excel сам преобразует строчные буквы в прописные.
Для ввода функций можно использовать Мастер функций, вызываемый нажатием пиктограммы Вставить функцию группы Библиотека функций вкладки ФОРМУЛЫ. Мастер функций (рис. 9.7) позволяет выбрать нужную функцию из списка и выводит для нее панель формул. На панели формул отображаются имя и описание функции, количество и тип аргументов, поле ввода для формирования списка аргументов, возвращаемое значение.
Excel содержит широкий набор функций, позволяющих выполнять стандартные вычисления. К основным категориям функций относятся:
1). Математические позволяют производить простые и сложные математические вычисления, например вычисление суммы диапазона ячеек, вычисление суммы ячеек диапазона, удовлетворяющих указанному условию, округление чисел и прочее.
Рис.9.7. Диалоговое окно Мастера функций.
2). Инженерные, предназначенные для выполнения инженерного анализа (функции для работы с комплексными переменными; преобразования чисел из одной системы счисления в другую; преобразование величин из одной системы мер в другую).
3). Логические, предназначенные для проверки выполнения условия или нескольких условий (ЕСЛИ, И, ИЛИ, НЕ, ИСТИНА, ЛОЖЬ).
4).Статистические, предназначенные для выполнения статистического анализа данных.
5).Финансовые, предназначенные для осуществления типичных финансовых расчетов, таких как вычисление суммы платежа по ссуде, объема периодической выплаты по вложению или ссуде, стоимости вложения или ссуды по завершении всех платежей.
6).Текстовые функции, предназначенные для обработки текста (преобразование, сравнение, сцепление строк текста и т.д.).
7).Функцииработы с датой и временем. Они позволяют анализировать и работать со значениями даты и времени в формулах.
8).Функцииссылки и массивы осуществляют поиск в списках или таблицах. Например, для поиска значения в таблице используйте функцию ВПР, а для поиска положения значения в списке — функцию ПОИСКПОЗ.
9). Функциипроверки свойств и значений позволяют перехватывать ошибки и значения #Н/Д и предотвращать их распространение по рабочему листу.
10).Аналитические функции предназначены для работы с аналитическими кубами, которые позволяют создавать отчеты по базе данных оперативной аналитической обработки (OLAP).
11).Функцииработа с базой данных предназначены для анализа данных из списков или баз данных.
12) Функциинадстроек и автоматизации позволяют расширить возможности Microsoft Excel за счет добавления пользовательских команд и новых функций.
Работа с ошибками
Если при наборе формулы были допущены ошибки, то в ячейку будет выведено значение ошибки. В Excel определено семь ошибочных значений:
1). #ДЕЛ/0! - попытка деления на 0. Эта ошибка обычно возникает, если в формуле делитель ссылается на пустую ячейку.
2). #ИМЯ? – в формуле используется имя, отсутствующее в списке имен диалога ПРИСВОЕНИЕ ИМЕНИ. Excel также вводит это ошибочное значение в том случае, когда строка символов не заключена в двойные кавычки.
3). #ЗНАЧ! – выдается при указании аргумента или операнда недопустимого типа, например, введена математическая формула, которая ссылается на текстовое значение, а также в том случае, когда Excel не может исправить формулу средствами автоисправления.
4). #ССЫЛКА! – отсутствует диапазон ячеек, на который ссылается формула (возможно он удален).
5). #Н/Д – нет данных для вычислений. Аргумент функции или операнд формулы является ссылкой на ячейку, не содержащую данные. Любая формула, которая ссылается на ячейки, содержащие #Н/Д, возвращает значение #Н/Д.
6). #ЧИСЛО! – задан неправильный аргумент функции, например, √(-5). #ЧИСЛО! может также указывать на то, что значение формулы слишком велико или слишком мало и не может быть представлено на листе.
7). #ПУСТО! – в формуле указано пересечение диапазонов, но эти диапазоны не имеют общих ячеек.
При поиске ошибок целесообразно использовать вспомогательную функцию отслеживания зависимостей, которая позволяет графически представить на экране связи между различными ячейками. Эта функция представляет на экране влияющие и зависимые ячейки. Влияющие ячейки – это ячейки, значения которых используются формулой, расположенной в активной ячейке. Ячейка, которая имеет влияющие ячейки, всегда содержит формулу. Зависимые ячейки – это ячейки, содержащие формулы, в которых имеется ссылка на активную ячейку. Ячейка, которая имеет зависимые ячейки, может содержать формулу или константное значение.
Функция позволяет:
просмотреть все влияющие ячейки; будут указаны все ячейки, на которые есть ссылки в формуле активной ячейки;
просмотреть все зависимые ячейки; будут указаны все ячейки, в которых есть ссылка на активную ячейку;
определить источник ошибки; прослеживается путь появления ошибки до ее источника. Excel выделяет ячейку, которая содержит первую формулу в цепочке ошибок, рисует стрелки из этой ячейки к выделенной и выводит окно сообщения. После нажатия кнопки ОК Excel рисует стрелки из ячеек, которые содержат значения, вовлекаемые в ошибочные вычисления.
Отслеживание зависимостей выполняется командами группы Зависимости формул вкладки Формулы (рис.9.8).
Рис. 9.8 Функция Отслеживания зависимостей.
Вычисление на листе
Вычисление – это процесс расчета формул с последующим выводом результатов в виде значений в ячейках, содержащих формулы. При изменении значений в ячейках, на которые ссылаются формулы, значения формул обновляются (т.е. происходит повторное вычисление). Этот процесс называется перерасчетом. Он затрагивает только те ячейки, которые содержат ссылки на изменившиеся ячейки.
По умолчанию Excel выполняет пересчет всегда, когда изменения воздействуют на значения ячеек. Если пересчитывается достаточно много ячеек, в левой части строки состояния появляются слова «Расчет ячеек» и некоторое число. Число показывает процент выполненного перерасчета ячеек. Процесс пересчета можно прервать. При вводе какой-либо команды и значения в ячейку во время выполнения пересчета, Excel приостановит обновление вычислений и продолжит его, когда пользователь закончит операцию.
При внесении изменений в большую книгу с множеством формул бывает удобно отказаться от автоматического пересчета и выполнять его вручную. В этом случае Excel будет выполнять пересчет только тогда, когда пользователь даст явно соответствующую команду. Чтобы установить ручное обновление вычислений, следует выполнить команду Параметры вычислений – режим ВРУЧНУЮ группы Вычисление вкладки ФОРМУЛЫ. Теперь при выполнении изменений, которые обычно вызывают обновление вычислений, в строке состояния будет выведено слово «Вычислить». Чтобы обновить значения формул после изменений, следует нажать кнопку F9. Excel вычислит значения всех ячеек во всех листах, на которые воздействуют изменения, сделанные после последнего пересчета. Чтобы пересчитать только активный лист, следует нажать комбинацию клавиш Ctrl и F9 или выбрать пиктограмму Произвести вычисления группы Вычисление вкладки ФОРМУЛЫ.
Даже если установлено ручное обновление вычислений, Excel обычно пересчитывает всю книгу при ее сохранении на жестком диске. Чтобы отменить такой режим, следует в диалоговом окне ПараметрыExcel категория Формулы в разделе Параметры вычислений снять флажок ПЕРЕСЧИТЫВАТЬ КНИГУ ПЕРЕД СОХРАНЕНИЕМ.
Циклическая ссылка – это формула, которая зависит от своего собственного значения. При обнаружении циклической ссылки Excel выдает сообщение об ошибке. Многие циклические ссылки могут быть разрешены. Для установки этого режима следует в диалоговом окне ПараметрыExcel категория Формулы в разделе Параметры вычислений установить флажок ВКЛЮЧИТЬ ИТЕРАТИВНЫЕ ВЫЧИСЛЕНИЯ. В этом случае Excel пересчитывает заданное число раз все ячейки во всех открытых листах, которые содержат циклическую ссылку. Если установлен флажок ВЛЮЧИТЬ ИТЕРАТИВНЫЕ ВЫЧИСЛЕНИЯ, можно задать предельное число итераций (по умолчанию 100) и относительную погрешность (по умолчанию 0,001). Excel выполняет пересчет указанное предельное число раз или до тех пор, пока изменение значений между итерациями не станет меньше заданной относительной погрешности (рис.9.9).
Рис.9.9. Диалоговое окно Параметры Excel категория Формулы
При использовании циклических ссылок целесообразно установить ручной режим вычислений. В противном случае программа будет пересчитывать циклические ссылки при каждом изменении значений в ячейках.
9.9 Автоввод и автозаполнение ячеек рабочего
листа
Сущность автоввода заключается в том, что если при вводе текста несколько символов совпадают с текстом ячеек данного столбца, то автоввод автоматически закончит ввод на основе уже существующего текста. Отказаться от автоввода можно, продолжая ввод нужного текста.
Автозаполнение предназначено для ввода повторяющихся данных или данных, продолжающих начатые ряды. Автозаполнение работает с числами, датами, днями недели и месяцами. Технология выполнения автозаполнения следующая:
1). Ввести в ячейку первое (начальное) значение. Ячейка помечается маркером (маленьким квадратом) в правом нижнем углу.
2). Установить указатель мыши на этот маркер. Он превратится в крест.
3). Удерживая нажатой левую клавишу мыши, протащить указатель (крест) по ячейкам, выделяя тем самым диапазон ячеек для автозаполнения.
4). Отпустить указатель мыши.
Автозаполнение ведет себя по-разному в зависимости от типа данных начального значения.
Если данные имеют тип «Дата», то при выполнении автозаполнения по указанной технологии будет получен ряд значений, начиная от начального с шагом 1. Если выделенный диапазон ячеек надо заполнить одним и тем же значением, то перемещать маркер при выделении диапазона следует с нажатой клавишей Ctrl. При необходимости управлять выводимой последовательностью значений (заполнять значения по рабочим дням, месяцам, годам, а также с определенным шагом, отличным от 1) следует перемещать маркер при выделении диапазона, удерживая не правую, а левую клавишу мыши. Тогда на экран будет выведено оперативное (контекстное) меню, команды которого позволяют управлять процессом автозаполнения.
Если данные имеют тип «Число», то при выполнении автозаполнения по указанной технологии происходит заполнение одним и тем же значением. Для продолжения ряда следует при выделении диапазона держать нажатой клавишу Ctrl.
Заполнение дней недели и месяцев осуществляется в соответствии со списками значений, которые известны Excel. Элементами этих списков являются данные типа «Текст». Пользователь может создать собственные списки автозаполнения. Для этого следует в диалоговом окне ПараметрыExcel категория Основные в разделе Основные параметры работы с Excel нажать кнопку Изменить списки. Если элементы создаваемого списка не содержатся в рабочем листе, то в диалоговом окне Списки в поле ЭЛЕМЕНТЫ СПИСКА ввести нужные значения и нажать кнопку ДОБАВИТЬ. Если элементы списка содержатся в ячейках рабочего листа, то в поле ИМПОРТ СПИСКА ИЗ ЯЧЕЕК следует ввести нужный диапазон и нажать кнопку ИМПОРТ. Созданный список теперь будет доступен функции автозаполнения, достаточно в качестве начального значения указать первое значение из списка. Выделенный диапазон ячеек будет автоматически заполняться элементами списка.
Используя возможности автозаполнения, можно также выполнять копирование формул в диапазон ячеек. Для этого следует установить указатель мыши на маркер (маленький квадрат) в правом нижнем углу ячейки с формулой. Указатель мыши примет форму креста. Удерживая левую клавишу мыши, протащить указатель по нужному диапазону.