Формулы представляют собой выражения, с помощью которых можно выполнять вычисления, возвращать данные, манипулировать содержимым других ячеек, проверять условия и т. д.. Формула может включать функции, ссылки на ячейки или имена, операторы и константы.
Для создания формулы необходимо выполнить следующие шаги:
ПРИМЕЧАНИЕ: В ячейке, содержащей формулу, отображается результат вычислений, но не сама формула. MS Excel отображает фактическую формулу в Строке Формул (Formula Bar) (Рисунок 1).
Операторы, используемые при создании формул
Оператор |
Значение |
Пример |
Арифметические операторы служат для выполнения арифметических операций |
||
+ (знак плюс) |
Сложение |
А1+А2 |
– (знак минус) |
Вычитание |
A1 - А2 |
Отрицание |
– A1 |
|
* (звездочка) |
Умножение |
A1* А2 |
/ (косая черта) |
Деление |
A1/ А2 |
% (знак процента) |
Процент |
50% |
^ (крышка) |
Возведение в степень |
A1 |
Операторы сравнения используются для сравнения двух значений. Результатом сравнения является логическое значение: либо ИСТИНА, либо ЛОЖЬ |
||
= (знак равенства) |
Равно |
(А1=В1) |
> (знак больше) |
Больше |
(А1>В1) |
< (знак меньше) |
Меньше |
(А1<В1) |
>= (знак больше и равно) |
Больше или равно |
(А1>=В1) |
<= (знак меньше и равно) |
Меньше или равно |
(А1<=В1) |
<> (знак меньше и больше) |
Не равно |
(А1<>В1) |
Текстовый оператор конкатенации используется для объединения нескольких текстовых значений |
||
& (амперсанд) |
Объединение последовательностей знаков в одну последовательность |
"Фамилия"&" "&"Имя |
Операторы ссылок используются для ссылки на ячейки других листов текущей книги и на другие книги |
||
: (двоеточие) |
Ставиться между ссылками на первую и последнюю ячейки смежного диапазона |
B5:B15 |
; (точка с запятой) |
Ставиться между ссылками на смежные ячейки |
B5;D5;D15 |
(пробел) |
Оператор пересечения множеств служит для ссылки на общие ячейки двух диапазонов |
B7:D7 C6:C8 |
ПРИМЕЧАНИЕ: Если в одной формуле используется несколько операторов, Microsoft Excel выполняет операции в порядке, указанном в приведенной ниже таблице.
Оператор |
Описание |
двоеточие; пробел; точка с запятой |
Операторы ссылок |
– |
Знак "минус" |
% |
Процент |
^ |
Возведение в степень |
* и / |
Умножение и деление |
+ и - |
Сложение и вычитание |
& |
Объединение двух текстовых строк в одну |
=; < >;<=;>=;<> |
Сравнение |
ПРИМЕЧАНИЕ:Чтобы изменить порядок выполнения формулы, заключите ее часть, которая должна быть выполнена первой, в скобки.
Формула массива представляет собой формулу, при помощи которой можно выполнять различные вычисления с одним или несколькими элементами в массиве. Формулы массива могут возвращать как отдельное значение, так и множество значений.
Формула массива, расположенная в нескольких ячейках, называется формулой с несколькими ячейками, а формула массива, находящаяся в одной ячейке, носит название формулы с одной ячейкой.
При использовании формул массива вы получаете следующие преимущества:
Согласованность. Каждая из ячеек, в которые была помещена формула массива, содержит одну и ту же формулу. Такая согласованность помогает обеспечить более высокую точность результатов.
Безопасность. Компонент формулы массива с несколькими ячейками нельзя переписать. Например, если вы попытаетесь удалить формулу массива из одной ячейки, вы получите информационное сообщение (Рисунок 2) о невозможности выполнения данной операции.
Меньший размер файлов. При использовании формул массива, множественные значения в массиве вычисляются с помощью одной формулы.
Создание формул массива
Для создания формулы массива выполните следующие действия:
ПРИМЕЧАНИЕ:
В формулу массива с несколькими ячейками нельзя вставить пустые строки или удалить строки из нее.
Функции - заранее определенные формулы, которые выполняют вычисления по заданным величинам, называемым аргументами, и в указанном порядке, определяемом синтаксисом. Функции MS Excel позволяют выполнять как простые, так и сложные вычисления, связанные с решением определенных задач.
Некоторые вычисления могут быть выполнены как с помощью формул, так и с помощью аналогичных им функций. Например:
Формула =C7+D7+E7 - складывает содержимое ячеек C7, D7 и E7.
Функция =СУММ(C7:E7) - суммирует диапазон смежных ячеек C7:E7.
Функции, используемые в программе MS Excel, имеют следующий синтаксис:
=ФУНКЦИЯ (аргумент1, аргумент2,...)
В некоторых случаях может потребоваться использование функции в качестве одного из аргументов другой функции. Рисунок 4 демонстрирует, пример использования функций СРЗНАЧ и СУММ в качестве аргументов функции ЕСЛИ.
ПРИМЕЧАНИЕ: При работе с английской версией MS Excel названия функций пишутся латинскими буквами.
Если вы хорошо знакомы с используемыми функциями, вы можете вводить их вручную непосредственно в ячейки рабочего листа. Для этого:
ПРИМЕЧАНИЕ: Как только вы начнете вводить имя функции, MS Excel отобразит под ячейкой динамический список допустимых функций и имен (Рисунок 5).
ПРИМЕЧАНИЕ:
ПРИМЕЧАНИЕ: В зависимости от функции ее аргументами могут быть: число, текст, логическое значение (ИСТИНА (TRUE) и ЛОЖЬ (FALSE)), ссылка на ячейку или диапазон ячеек (смежных или не смежных), формулы или функции. В каждом конкретном случае необходимо использовать соответствующий тип аргумента.
ПРИМЕЧАНИЕ: MS Excel отобразит формулу в строке формул, как только вы ее введете. В ячейке отобразится результат вычисления.
Для быстрого выполнения некоторых вычислений без запуска мастера функций можно воспользоваться кнопкой Сумма.
Эта кнопка имеется на двух вкладках ленты:
Вкладка Главная группа Редактирование – кнопка Сумма ;
Вкладка Формулы группа Библиотека функций - кнопка Автосумма.
Для вычисления суммы чисел в смежных ячейках следует:
Для вычисления суммы произвольно расположенных ячеек:
Кроме вычисления суммы, кнопку Сумма можно использовать при вычислении среднего значения, определения количества числовых значений, нахождения максимального и минимального значений. В этом случае необходимо щелкнуть по стрелке кнопки Сумма и выбрать необходимое действие (Рисунок 9):
Среднее (функция СРЗНАЧ) - расчет среднего арифметического;
Число (функция СЧЁТ) - определение количества числовых значений;
Максимум (функция МАКС) - вычисление максимального значения;
Минимум (функция МИН) - вычисление минимального значения.
Вы можете упростить процедуру создания формулы, используя Мастер Функций, который предоставляет доступ ко всем встроенным функциям MS Excel.
Для того чтобы создать функцию с помощью мастера выполните следующие шаги:
ПРИМЕЧАНИЕ: Последние 10 недавно использованных функций находятся в соответствующей категории — 10 недавно использовавшихся (Most Recently Used).
Все функции MS Excel категоризированы – сгруппированы в категории в соответствии с их функциональностью.
При работе с Мастером функций чтобы найти нужную вам функцию необходимо указать категорию, к которой она относится. В категории Полный алфавитный перечень, перечислены в алфавитном порядке все доступные функции. Что позволяет найти функцию, которую вы хотите вставить, даже не зная категории функции.
Если вы знаете, к какой категории относится нужная вам функция, для ее вызова вы можете воспользоваться соответствующей кнопкой категории в группе Библиотека функций на вкладке Формулы (Рисунок 13) не прибегая к помощи Мастера функций:
Статистические – перечислены статистические функции, такие как: средняя величина, медиана, ковариация и отклонение.
Инженерные – содержит функции, которые помогут вам при решении инженерных прикладных задач. Эти функции позволяют работать с комплексными числами, а также выполнять преобразование единиц одной системы исчисления или измерения в единицы исчисления или измерения другой системы
Аналитические – содержит функции для выполнения аналитических вычислений и прогнозирования результатов.
Проверка свойств и значений - проверяют содержимое ячейки на предмет кодов ошибки, логических значений, чисел или текста.
Совместимость – содержит функции оставленные в MS Excel 2010 для совместимости с более ранними версиями MS Excel.
ПРИМЕЧАНИЕ: Категории Полный алфавитный перечень и Работа с базой данных доступны только в окне диалога Мастер функций. Категория Работа с базой данных содержит функции, которые позволяют выполнять различные вычислительные операции с элементами базы данных.
Excel содержит десятки математических функций. Некоторые из них предназначены для групп узких специалистов, таких как инженеры или статистики, в то время как другие настолько полезны, что могут встретиться практически в любой таблице.
Пример использования функции СУММЕСЛИ:
Ошибки в формулах и функциях могут привести к ошибочным значениям, а также вызвать непредсказуемые результаты. Если формула содержит ошибку, не позволяющую выполнить вычисления или отобразить результат, MS Excel отобразит сообщение об ошибке. Таблица содержит описание ошибок, возникающих при работе с формулами, и указаны возможные причины, которые могли вызвать данную ошибку.
Таблица. Ошибки в формулах
Обозначение |
Причина возникновения |
Возможное решение |
#### |
Столбец недостаточно широк; Дата и время отрицательными числами |
Измените ширину столбца; Проверьте корректность ввода данных |
#ЗНАЧ! (#VALUE!) |
Используется недопустимый тип аргумента или операнда. Например, вместо числа используется текст. | Вместо арифметических операторов используйте для выполнения арифметических операций над содержимым ячеек с текстом функции. |
#ДЕЛ/0 (#DIV/0) |
Деление числа на 0 (ноль) или на ячейку, в которой нет значения. |
Например, если ошибка возникает в формуле =A1/A2, можно заменить ее формулой =ЕСЛИ(A2=0;"";A1/A2), ч тобы она возвращала пустую строку или формулой =ЕСЛИ(A2=0;0;A1/A2), чтобы она возвращала 0. |
#ИМЯ? (#NAME?) |
MS Excel не может распознать имя, используемое в формуле |
Убедитесь в том, что имя, используемое вформуле, действительно существует; Если формула содержит ссылки на ячейки других листов или книг, а имя другой книги или листа содержит небуквенные символы или пробел, это имя необходимо заключить в одиночные кавычки ('). |
#Н/Д (#N/A) |
Значение недоступно функции или формуле | Если в несколько ячеек введена формула массива, проверьте, что диапазоны, используемые в формуле, содержат то же количество строк и столбцов; Задайте все обязательные аргументы для функции, которая возвращает ошибку; Убедитесь в том, что аргументы функции верны и помещены в правильные позиции. |
#ССЫЛКА! (#REF!) |
Ссылка на ячейку указана неверно | Проверьте аргументы функции и убедитесь, что они ссылаются на допустимые ячейки. |
#ЧИСЛО! (#NUM) |
В функции, требующей числовой аргумент, используется неверный тип данных; Реультат формулы дает число слишком большое или слишком малое для представления в Excel. | Убедитесь в том, что в функции используются только числовые аргументы; Измените формулу так, чтобы ее результат находился в диапазоне от-1*10307 до 1*10307. |
#ПУСТО! (#NULL) |
Задано пересечение двух областей, которые в действительности не имеют общих ячеек. Оператором пересения областей является пробел между ссылками. | Проверьте аргументы функции и убедитесь, что они ссылаются на допустимые диапазоны ячеек. |
При возникновении ошибки MS Excel отображает в левом верхнем углу ячейки зеленый треугольник (индикатор ошибки). При выборе такой ячейки появляется — смарт-тег проверки ошибок (Рисунок 15).
Если на листе уже выполнялась проверка ошибок, то ошибки, которые были пропущены, не будут отображаться, пока их состояние не будет сброшено. Для сброса состояния пропущенных ошибок выполните следующие действия:
Для исправления ошибки можно воспользоваться списком действий, предоставляемым cмарт-тегом проверки ошибок. В случае если будет выбран пункт Пропустить ошибку (Ignore Error), такая ошибка при последующих проверках отображаться не будет.
Для проверки ошибок необходимо выполнить следующие шаги:
Проверка правильности формул и поиск источника ошибки могут быть затруднены, если у формул есть влияющие или зависимые ячейки.
Влияющие ячейки — это ячейки, на которые ссылается формула.
Зависимые ячейки — это ячейки, которые содержат формулы, ссылающиеся на другие ячейки.
Для упрощения анализа формул и функций можно воспользоваться командами группы Зависимости формул на вкладке Формулы (Рисунок 17).
Чтобы определить ячейки, данные из которых используются в формуле (влияющие ячейки), выполните указанные ниже действия:
Синие стрелки указывают на ячейки без ошибок, а красные — на ячейки, вызывающие ошибки. Если на значение выделенной ячейки влияют данные с другого листа или книги, черная стрелка будет указывать на значок листа (Рисунок 18, а). Книгу, на которую ссылается выделенная ячейка, нужно открыть до трассировки этих зависимостей.
Чтобы убрать стрелки трассировки по одному уровню за раз, начиная с наиболее отдаленной зависимой ячейки, на вкладке Формулы в группе Зависимости формулнажмите стрелку рядом с кнопкой Убрать стрелки и выберите пункт Убрать стрелки к влияющим ячейка. Чтобы убрать следующий уровень стрелок трассировки, нажмите эту кнопку еще раз.
Чтобы выявить формулы, которые ссылаются на определенную ячейку (зависимые ячейки), выполните указанные ниже действия:
Синие стрелки указывают на ячейки без ошибок, а красные — на ячейки, вызывающие ошибки. Если на выделенную ячейку есть ссылка с другого листа или книги, черная стрелка из ячейки будет указывать на значок листа (Рисунок 19а).
Чтобы убрать стрелки трассировки на вкладке Формулы в группе Зависимости формул нажмите кнопку Убрать стрелки.
Чтобы убрать стрелки трассировки по одному уровню за раз, начиная с наиболее отдаленной зависимой ячейки, на вкладке Формулы в группе Зависимости формул
нажмите стрелку рядом с кнопкой Убрать стрелки и выберите пункт Убрать стрелки к зависимым ячейкам . Чтобы убрать следующий уровень стрелок трассировки, нажмите эту кнопку еще раз.
Чтобы выделить цветом влияющие аргументы в формуле, выделите ячейку и нажмите клавишу [F2].
Чтобы выделить ячейку на другом конце стрелки, дважды щелкните стрелку. Если ячейка находится на другом листе или в другой книге, дважды щелкните черную стрелку, чтобы вызвать диалоговое окно Перейти. Затем в списке Перейти дважды щелкните нужную ссылку.
Все стрелки трассировки исчезнут в случае изменения формулы, на которую они указывают, вставки или удаления столбцов или строк, а также если удалить или переместить ячейки.
Чтобы выделить ячейку на другом конце стрелки, дважды щелкните стрелку. Если ячейка находится на другом листе или в другой книге, дважды щелкните черную стрелку, чтобы вызвать диалоговое окно Перейти. Затем в списке Перейти дважды щелкните нужную ссылку.
Все стрелки трассировки исчезнут в случае изменения формулы, на которую они указывают, вставки или удаления столбцов или строк, а также если удалить или переместить ячейки.
© ООО «Знанио»
С вами с 2009 года.