О формулах
Структура формулы
Формулы представляют собой выражения, по которым выполняются вычисления. Формула всегда начинается со знака равно (=). Формула может включать функции, ссылки на ячейки или имена, операторы и константы.
Например, в формуле
=СУММ(В2:В8)*30
СУММ() - функция;
В2 и В8 - ссылки на ячейки;
: (двоеточие) и * (звездочка) - операторы;
30 - константа.
Функции - заранее определенные формулы, которые выполняют вычисления по заданным величинам, называемым аргументами, и в указанном порядке. Структура функции: имя функции, открывающая скобка, список аргументов, разделенных точками с запятой, закрывающая скобка. Аргументом функции может быть число, текст, логическое значение, массив, значение ошибки, ссылка на ячейку. В качестве аргументов используются также константы, формулы, или функции. В каждом конкретном случае необходимо использовать соответствующий тип аргумента.
Ссылка указывает на ячейку или диапазон ячеек листа, которые требуется использовать в формуле. Можно задавать ссылки на ячейки других листов текущей книги и на другие книги. Ссылки на ячейки других книг называются связями.
Оператором называют знак или символ, задающий тип вычисления в формуле. Существуют математические, логические операторы, операторы сравнения и ссылок.
Константой называют постоянное (не вычисляемое) значение. Формула и результат вычисления формулы константами не являются.
Операторы
Арифметические операторы
Арифметические операторы служат для выполнения арифметических операций, таких как сложение, вычитание, умножение. Операции выполняются над числами. Используются следующие арифметические операторы.
| Оператор
| Значение
| Пример
| | + (знак плюс)
| Сложение
| A1+A2
| | - (знак минус)
| Вычитание/Отрицание
| A1-A2-A1
| | * (звездочка)
| Умножение
| A1*A2
| | / (косая черта)
| Деление
| A1/A2
| | % (знак процента)
| Процент
| 50%
| | ^ (крышка)
| Возведение в степень
| A1^2
|
Операторы сравнения
Операторы сравнения используются для сравнения двух значений. Результатом сравнения является логическое значение: либо ИСТИНА, либо ЛОЖЬ.
| Оператор
| Значение
| Пример
| | = (знак равенства)
| Равно
| (A1=B1)
| | > (знак больше)
| Больше
| (A1>B1)
| | < (знак меньше)
| Меньше
| (A1<B1)
| | >= (знак больше и равно)
| Больше или равно
| (A1>=B1)
| | <= (знак меньше и равно)
| Меньше или равно
| (A1<=B1)
| | <> (знак меньше и больше)
| Не равно
| (A1<>B1)
|
Текстовый оператор конкатенации
Текстовый оператор амперсанд (&) используется для объединения нескольких текстовых значений.
Операторы ссылок
Операторы ссылок используют для описания ссылок на диапазоны ячеек.
| Оператор
| Значение
| Пример
| | : (двоеточие)
| Ставится между ссылками на первую и последнюю ячейки диапазона
| B5:B15
| | ; (точка с запятой)
| Оператор объединения
| B5:B15;D5:D15
| | (пробел)
| Оператор пересечения множеств, служит для ссылки на общие ячейки двух диапазонов
| B7:D7 C6:C8
|
Создание и редактирование формул
С использованием клавиатуры вводят операторы (знаки действий), константы, скобки и, иногда, функции. С использованием мыши выделяют ячейки и … 1. Выделите ячейку, в которую требуется ввести формулу.
2. Введите = (знак равенства).
Для создания формул с функциями обычно используют группу Библиотека функций вкладки Формулы (рис. 2).
Рис. 2. Вкладка Формулы
При редактировании ячейки, как и при вводе формулы, ссылки на ячейки и границы вокруг соответствующих ячеек выделяются цветом (см. рис. 1).
Для изменения ссылки на ячейки и/или диапазон ячеек достаточно перетащить…
Перемещение и копирование формул
Перемещать и копировать ячейки с формулами можно точно так же, как и ячейки с текстовыми или числовыми значениями.
Кроме того, при копировании ячеек с формулами можно пользоваться возможностями специальной вставки. Это позволяет копировать только формулу без копирования формата ячейки.
При перемещении ячейки с формулой содержащиеся в формуле ссылки не изменяются. При копировании формулы ссылки на ячейки могут изменяться в зависимости от их типа (относительные или абсолютные).
Использование ссылок в формулах
Ссылки на ячейки других листов и книг
При использовании в создаваемых формулах ссылок на ячейки других листов и книг в процессе создания формулы следует перейти на другой лист текущей книги или в другую книгу и выделить там необходимую ячейку.
При каждом переходе на другой лист, его имя автоматически добавляется к ссылке на ячейку. Имя листа заключено в одинарные кавычки (апострофы). Имя листа и адрес ячейки разделены служебным символом ! (восклицательный знак).
Например, в формуле в ячейке Е18 в таблице на рис. 13 использована ячейка В2 листа Курсы валют текущей книги.
Рис. 13. Ссылка на ячейку другого листа текущей книги
При переходе к ячейке другой книги, имя файла в квадратных скобках автоматически добавляется к ссылке на ячейку. Например, в формуле в ячейке Е18 в таблице на рис. 6.14 использована ячейка В2 листа ЦБ файла Валюты.xlsx.
Рис. 14. Ссылка на ячейку другой книги
Относительные и абсолютные ссылки
По умолчанию ссылки на ячейки в формулах относительные, то есть адрес ячейки определяется на основе расположения этой ячейки относительно ячейки с формулой. При копировании ячейки с формулой относительная ссылка автоматически изменяется. Именно возможность использования относительных ссылок и позволяет копировать формулы.
Например, при копировании автозаполнением ячейки Е2 (рис. 15) на нижерасположенные ячейки, в ячейке Е3 будет формула =С3*D3, в ячейке Е4 будет формула =С4*D4 и т. д.
Рис. 15. Копирование формул
В некоторых случаях использование относительных ссылок недопустимо. Например, в таблице на рис. 16 при копировании ячейки Е2 на нижерасположенные ячейки ссылка на ячейки С2 и D2 должны изменяться, а ссылка на ячейку В20 должна оставаться неизменной.
Рис. 16. Использование абсолютных ссылок
Для того чтобы ссылка на ячейку при копировании не изменялась, необходимо использовать абсолютные ссылки. Абсолютная ссылка ячейки имеет формат $A$1, где $ - служебный символ, показывающий абсолютную ссылку.
Чтобы ссылка на ячейку была абсолютной, после указания ссылки на ячейку следует нажать клавишу F4. Ссылку можно преобразовать из относительной в абсолютную и при редактировании ячейки с формулой. К заголовкам столбца и строки в адресе ячейки следует добавить служебный символ $. Например, для того чтобы ссылка на ячейку В20 стала абсолютной, необходимо ввести $В$20.
Ссылка может быть не только относительной или абсолютной, но и смешанной.
Ссылка формата A$1 является относительной по столбцу и абсолютной по строке, т.е. при копировании ячейки с формулой выше или ниже, ссылка изменяться не будет. А при копировании влево или вправо будет изменяться заголовок столбца.
Ссылка формата $A1 является относительной по строке и абсолютной по столбцу, т.е. при копировании ячейки с формулой влево или вправо ссылка изменяться не будет. А при копировании выше или ниже будет изменяться заголовок строки.
Например, в ячейке Е2 таблицы на рис. 16 достаточно было ввести смешанную ссылку В$20.
Рис. 17. Использование трехмерных ссылок
1. Выделите ячейку, в которую нужно ввести функцию.
Использование имен ячеек и диапазонов
Использование имен
Имя - слово или строка знаков, представляющих ячейку, диапазон ячеек, формулу или константу. Имена можно использовать в любом листе книги или только в отдельном выбранном листе. При использовании в формулах имена ячеек являются абсолютными ссылками.
1. Выделите ячейку или диапазон ячеек.
2. В группе Определенные имена вкладки Формулы нажмите кнопку Присвоить… 3. В окне Создание имени в поле Имя введите имя ячейки или диапазона (рис. 18).
Во избежание возможных ошибок при использовании имен в процессе создания формулы следует в группе Определенные имена вкладки Формулы щелкнуть…
Рис. 20. Вставка имени в формулу
Удаление имени
Ненужное или ошибочное имя можно удалить.
1. В группе Определенные имена вкладки Формулы нажмите кнопку Диспетчер имен.
2. В окне Диспетчер имен выделите имя и нажмите кнопку Удалить (рис. 22).
Рис. 22. Удаление имени
Проверка ошибок
Проверка ошибок при создании и редактировании формул
При обнаружении лишних символов операторов, лишних или недостающих скобок и т. п. выводится сообщение об ошибке в окне предупреждения. Например,…
Рис. 23. Сообщение об ошибке во введенной формуле
При выделении ячейки с ошибкой рядом с ней появляется кнопка Источник ошибки. Если щелкнуть по кнопке, появится меню, в котором указан тип ошибки,…
Рис. 26. Кнопка и меню кнопки Источник ошибки
Для отображения ячеек, входящих в формулу в качестве аргументов, необходимо выделить ячейку с формулой и нажать кнопку Влияющие ячейки в группе…
Рис. 28. Трассировка влияющих ячеек
Организация вычислений
Установка режима вычислений
По умолчанию Microsoft Excel автоматически пересчитывает значения ячеек, которые зависят от других ячеек, содержащих изменившиеся значения. Этот тип пересчета позволяет избежать ненужных вычислений. Пересчет также производится при открытии книги.
Для выбора режима вычислений щелкните по кнопке Параметры вычислений в группе Вычисление вкладки Формулы и выберите нужный режим (рис. 6.30).
Рис. 30. Изменение режима вычислений
После этого для пересчета формул во всех листах текущей книги следует нажать кнопку Пересчет группы Вычисление вкладки Формулы (см. рис. 30) или клавишу F9, а для пересчета формул только на текущем листе - кнопку Пересчет группы Произвести вычисления вкладки Формулы (см. рис. 30) или комбинацию клавиш Shift + F9.
При открытии книги, созданной в текущей версии Microsoft Excel, пересчитываются только формулы, которые зависят от измененных ячеек.
При открытии книги, созданной в более ранней версии Microsoft Excel, все… При закрытии файла, созданного в старых версиях Microsoft Excel, даже если в нем не производилось никаких действий,…
|
