Формулы Excel

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

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

Результатом вычисления формулы, включающей арифметические операции, является числовое значение. Формула с операторами сравнения дает в результате логическое значение TRUE (Истина) или FALSE (Ложь).

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

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

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

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

Чтобы определить количество проданных за квартал видеокарт каждого вида, необходимо произвести соответствующие вычисления в ячейках G4: G9. В частности, ячейка G4 должна содержать формулу, которая вычисляет сумму значений, хранящихся в ячейках D4: F4. Создать ее можно, введя в ячейку с клавиатуры следующее выражение: D4+E4+F4

Ввод каждой формулы завершается нажатием клавиши Enter, после чего в ячейке появляется результат вычислений. Но фактическим содержимым ячейки по-прежнему является формула, которую можно увидеть в строке формул при активизации этой ячейки, а также в режиме редактирования содержимого ячейки (данный режим устанавливается после нажатия клавиши F2).

По умолчанию на вкладке Edit (Правка) диалогового окна Options (Параметры) установлен флажок Edit directly in cell (Правка прямо в ячейке). В этом случае ввод формулы осуществляется непосредственно в активной ячейке, и по мере ввода формула появляется в строке формул. Если данный флажок отключен, после ввода знака равенства курсор переходит в строку формул и последующие действия по формированию формулы выполняются там.

Существуют и другие, более быстрые методы ввода формул, в частности метод «наведи и щелкни" — (point-and-click). Он особенно удобен, если формула должна содержать ссылки на ячейки, находящиеся на большом расстоянии друг от друга.

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

1.2 Отображение формул По умолчанию на рабочем листе отображаются не формулы, а результаты, и для того чтобы увидеть формулу, необходимо сделать ячейку, в которой она находится, активной. Чтобы иметь возможность просматривать все формулы непосредственно на рабочем листе, на вкладке View (Вид) диалогового окна Options (Параметры) следует установить флажок Formulas (формулы). В результате этого ширина столбцов будет автоматически увеличена, благодаря чему их обзор заметно улучшится. При переходе в данный режим появляется панель инструментов Formula Auditing (Зависимости), которая будет описана далее.

Таблица-пример представлена в режиме отображения формул

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

Абсолютная ссылка указывает на конкретную ячейку. При перемещении или копировании формулы такая ссылка не изменяется, поскольку она задает фиксированную позицию на рабочем листе. Признаком абсолютной ссылки является наличие двух знаков доллара ($) — перед именем столбца и перед номером строки. В смешанных ссылках имеется один знак доллара. Если он стоит, например, перед именем столбца, то мы имеем абсолютную ссылку на столбец и относительную — на строку.

Поясним принцип применения ссылок разного типа на примере нашей таблицы и рассчитаем выручку, получаемую каждый месяц от продажи видеоплат всех типов. Формулы для расчета будут расположены в столбцах Н, I, J. Начнем с создания формулы для ячейки Н4. Нетрудно догадаться, что она должна выглядеть так: =C4*D4

То есть мы должны умножить цену одной видеокарты на количество единиц, проданных за месяц. Аналогичная формула для расчета выручки за второй месяц, находящаяся в ячейке 14, будет иметь такой вид: =С4*Е4

А формула для расчета выручки за третий месяц, вводимая в ячейку J4, такой: =C4*F4

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

Если же в формуле ячейки Н4 сделать ссылку на столбец С абсолютной, то мы сможем путем копирования этой формулы заполнить правильными формулами весь нужный нам диапазон. При копировании в первом операнде формулы изменяется только номер строки (С5, С6, С7 и т. д.), а ссылка на столбец остается постоянной.

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

excel ссылка редактирование формула ячейка Создание абсолютной или смешанной ссылки

1. Выберите ячейку, в которой будет размещен результат.

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

3. Когда ввод формулы будет завершен, нажмите клавишу Enter.

4. Заполните формулой все остальные ячейки (с помощью маркера заполнения).

1.4 Ссылки на листы и книги Формулы могут содержать ссылки на другие листы рабочей книги и даже на другие книги. Создавая такие ссылки, нужно придерживаться определенных правил, иначе не избежать появления ошибок при вычислениях. В ссылке на другой лист имя листа указывается перед адресом ячейки и отделяется от него восклицательным знаком: Sheetl! D4.

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

Если удалить лист, на содержимое которого существует ссылка в формуле, соответствующие ссылки будут заменены сообщением об ошибке #REF! (#ССЫЛКА!), а результат вычислений не станет отображаться. После удаления содержимого влияющей ячейки ее значение при вычислениях будет считаться равным 0.

Ссылка на ячейку /фугой рабочей книги (внешняя ссылка) создается аналогичным образом. Во время ввода формулы во второй рабочей книге следует активизировать окно первой рабочей книги и выбрать ячейку, на которую нужно создать ссылку. Во внешней ссылке, помимо имени листа и адреса ячейки, указывается имя рабочей книги, которое заключается в квадратные скобки:

[имя_книги.х!5]имя_листа!$А$ 1

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

Создание ссылок на листы и книги

1. Выберите ячейку, в которой будет размещен результат.

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

3. Завершив ввод формулы, нажмите клавишу Enter.

В случае закрытия зависимой рабочей книги и удаления исходной ссылка на удаленный документ в формуле заменяется сообщением об ошибке #REF! (#ССЫЛКА!). Если файл с исходной рабочей книгой был перемещен, ссылку в формуле необходимо изменить вручную. Это нужно сделать и при открытии рабочей книги, которая содержит ссылки на еще закрытые книги.

Обновить существующие связи можно, не открываяисходную рабочую книгу. Эту операцию придется производить в том случае, если она не будет выполняться автоматически. Чтобы открыть диалоговое окно Edit Links (Изменение связей), в котором можно произвести обновление связей, следует активизировать команду Edit > Links (Правка > Связи).

Обновить все связи текущей рабочей книги, не открывая исходные файлы, позволяет кнопка Update Values (Обновить). Посредством кнопки Open Source (Открыть) можно активизировать рабочую книгу, выделенную в диалоговом окне Edit Links (Изменение связей). После нажатия кнопки Change Source (Изменить) открывается диалоговое окно Change Source (Изменить источник), где пользователь может задать другой путь к документу, с которым установлена связь. После щелчка на кнопке Break Link (Разорвать связь) происходит разрыв связи, сопровождающийся подстановкой значения, полученного последним. Кнопка Check Status (Состояние) используется для проверки всех связей.

1.5 Формулы массива Если над данными определенного диапазона нужно произвести однотипные операции, процесс формирования необходимых формул можно упростить, создав для диапазона формулу массива, которая будет связана со всеми его ячейками. Например в столбцах Н, I и J хранятся суммы выручки от продажи видеокарт за каждый из трех месяцев. Для определения общей суммы выручки за квартал по первому наименованию следовало бы в ячейку К4 поместить формулу =H4+I4+J4, а затем скопировать ее в другие ячейки столбца. Однако в подобных случаях удобнее использовать формулу массива. Чтобы создать ее, нужно выделить ячейки К4: К9 — диапазон, определяющий массив, и ввести следующую формулу (для указания диапазонов их можно выделять мышью):

=H4:H9+I4;I9+J4:J9

Чтобы действие формулы распространялось на все выделенные ячейки массива, ее ввод следует завершить нажатием комбинации клавиш Ctrl+Shift+ Enter. После этого в каждой ячейке диапазона 14:19 появится приведенная ниже формула массива (фигурные скобки являются признаком формулы такого типа):

=H4:H9+I4;I9+J4:J9

Создание формулы массива

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

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

3. Нажмите комбинацию клавиш Ctrl+Shift+ Enter.

Изменить формулу массива можно в режиме редактирования. Однако, как только вы попытаетесь завершить редактирование формулы нажатием клавиши Enter, программа снова выдаст сообщение о недопустимости выполняемого действия. Для того чтобы подтвердить необходимость внесения изменений в формулу массива, следует воспользоваться комбинацией клавиш Ctrl+Shift+Enter.

Мы рассмотрели формулу массива, возвращающую несколько значений. Однако несложно создать и такую формулу, которая возвращала бы только одно значение. С ее помощью удобно, в частности, рассчитывать суммы произведений. Такая формула представлена на рис.

1.6 Режимы пересчета формул По умолчанию на вкладке Calculation (Вычисления) диалогового окна Options (Параметры) активизирован переключатель Automatic (автоматически), который обеспечивает автоматический пересчет формул после каждой операции ввода данных. Правда, при этом ввод каждого нового значения требует пересчета связанных с ними значений во всей рабочей книге.

Однако если лист содержит много сложных формул, автоматический режим их пересчета может стать причиной замедления работы программы. В таком случае его целесообразно отключить. Сделать это можно путем установки переключателя Manual (вручную). Если этот переключатель установлен, вычисления производятся только после нажатия клавиши F9 или одной из кнопок вкладки Calculation (Вычисления) — кнопки Calc Now (F9) (Вычислить (F9)) или Calc Sheet (Пересчет листа).

При нажатии кнопки Calc Now (F9) (Вычислить (F9)) осуществляется пересчет данных во всех открытых листах, включая таблицы данных, а также происходит обновление диаграмм всех этих листов. Кнопка Calc Sheet (Пересчет листа) предназначена для активизации операции пересчета данных на активном рабочем листе, а также для преобразования связанных с ним диаграмм и таблиц данных.

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

Установка флажка Recalculate before save (Пересчет перед сохранением) предписывает, что данные должны пересчитываться перед каждой операцией сохранения документа.

При установке переключателя в положение Automatic except tables (автоматически кроме таблиц) автоматически будут пересчитываться все зависящие формулы, но не данные в таблицах. Для пересчета данных необходимо нажать кнопку Calc Now (F9) (Вычислить (F9)) или клавишу F9.

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

При выборе имени для диапазона ячеек следует придерживаться определенных правил. Имя может содержать до 255 знаков и состоять из букв, цифр, символов точки и подчеркивания. Начинаться оно должно с буквы или со знака подчеркивания. Кроме того, имя не должно быть похожим на ссылку (например, таким как A3 или С$ 2), однако оно может состоять из нескольких слов, разделенных символами точки и подчеркивания. Регистр символов в имени Excel не различает.

1.7.1 Присвоение имен ячейкам и диапазонам ячеек Чтобы присвоить имя ячейке или диапазону ячеек, нужно сначала их выделить. После этого необходимо щелкнуть в крайнем левом поле строки формул, где находится адрес текущей ячейки. В результате в этом поле появится курсор ввода, а адрес ячейки будет выделен. Введите имя диапазона, например Апрель, и нажмите клавишу Enter.

Существует и другой способ именования диапазонов: вызвать команду Insert >Name > Define (Вставка > Имя > Присвоить) и ввести имя диапазона в поле Names in workbook (Имя) диалогового окна Define Name (Присвоение имени).

Поле Refers to (Формула) диалогового окна Define Name (Присвоение имени) предназначено для отображения ссылки на выделенный диапазон ячеек в том виде, в каком она будет входить в формулу. Первым указывается имя листа. Затем следуют адреса крайних ячеек диапазона, снабженные символами доллара, что характерно, как вы знаете, для абсолютных ссылок. Если формируется относительная ссылка, символ доллара следует удалить.

Имена диапазонов ячеек активной книги перечисляются в списке под полем ввода имени. Если отметить имя некоторого диапазона ячеек, а затем закрыть окно, щелкнув на кнопке О К, то указанный диапазон будет выделен.

Присвоение, изменение и удаление имен ячеек

1. Выделите ячейку (или диапазон ячеек), которой нужно присвоить имя.

2. Выберите команду Insert > Name t Define (Вставка > Имя > Присвоить).

3. В поле Names in workbook (Имя) введите имя и щелкните на кнопке Add (Добавить).

4. При необходимости изменить имя выделите его в списке, замените новым и щелкните на кнопке Add (Добавить).

5. Чтобы удалить имя, выделите его в списке и щелкните на кнопке Delete (Удалить).

1.7.2 Присвоение имен константам и формулам С помощью диалогового окна Define Name (Присвоение имени) можно присваивать имена константам и формулам. Для этого в поле Names in workbook (Имя) достаточно ввести нужное имя, а в поле Refers to (Формула) константу или знакравенства и формулу. После нажатия кнопки Add (Добавить) это имя появится в списке диалогового окна.

1.7.3 Создание имен на основе заголовков В качестве имени диапазона можно использовать содержимое нескольких входящих в его состав ячеек, что значительно упрощает работу с таблицами. Выделите в таблице диапазон ячеек и вызовите команду Insert > Name > Create (Вставка >Имя > Создать). В результате откроется диалоговое окно Create Names (Создать имена).

В этом диалоговом окне нужно указать, где в выделенном диапазоне расположены ячейки, текст которых должен использоваться в качестве имен. По умолчанию установлен флажок Top row (в строке выше), определяющий, что имена столбцов диапазона будут сформированы из текстовых данных его верхней строки. Установите нужный флажок и подтвердите свой выбор нажатием кнопки ОК. Сгенерированные описанным способом имена отобразятся в поле имен, располо женном в строке формул. В таблице, представленной на следующем рисунке, столбцы озаглавлены как Апрель, Май и Июнь, а формула для суммирования данных в столбце D содержит ссылки в виде имен диапазонов (см. строку формул).

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

Создание имен из заголовков строк или столбцов

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

2. Выберите команду Insert > Name > Create (Вставка > Имя > Создать), для того чтобы открыть диалоговое окно Create Names (Создать имена).

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

4. Щелкните на кнопке ОК. Имена будут созданы, а окно закрыто.

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

Это можно сделать следующим образом. Перейдите на другой лист, вызовите там команду Insert > Name > Paste (Вставка >Имя > Вставить), и на экране появится диалоговое окно Paste Name (Вставка имени).

Щелкните в этом окне на кнопке Paste List (Все имена), и в рабочий лист будет вставлен весь список имен с указанием адресов диапазонов. При необходимости удалить какие-либо из имен воспользуйтесь кнопкой Delete (Удалить) в диалоговом окне Define Name (Присвоение имени).

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

Применение имен в формулах

1. Начните ввод формулы.

2. Когда нужно будет ввести в формулу имя, выберите таковое в окне Paste Name (Вставка имени), которое вызывается посредством команды Insert > Name > Paste (Вставка > Имя > Вставить).

3. Нажмите клавишу Enter.

Если в формулах, содержащих ссылки на диапазон, использовались адреса ячеек, то после присвоения диапазону имени их можно заменить этим именем. Причем предварительно выделять ячейки с формулами не нужно, за исключением того случая, когда ссылки необходимо заменить только в выделенном диапазоне. Далее следует вызвать команду Insert > Name > Apply (Вставка > Имя >Применить), с тем чтобы открыть диалоговое окно Apply Names (Применение имен).

Выберите имя, которым будут заменены соответствующие ссылки в формулах, нажмите кнопку ОК, и программа автоматически выполнит данную операцию. Правда, относительные ссылки при этом будут преобразованы в абсолютные. Чтобы избежать подобной ситуации, необходимо снять флажок Ignore Relative/Absolute (Игнорировать тип ссылки), вследствие чего будет установлен режим замены только абсолютных ссылок.

1. Microsoft Office Excel 2003. Учебный курс / В. Кузьмин

2. Подробное руководство по созданию формул в Excel 2002 / Джон Уокенбах