Об операторах в формулах
Операторами обозначаются операции, которые следует выполнить над операндами формулы. В Microsoft Excel включено четыре вида операторов: арифметические, текстовые, операторы сравнения и операторы ссылок.
Типы операторов
Арифметические операторы. Служат для выполнения арифметических операций, таких, как сложение, вычитание, умножение. Операции выполняются над числами. Используются следующие арифметические операторы.
Арифметический оператор
Значение (пример)
Вычитание(3–1) Отрицание (–1)
Возведение в степень (3^2)
Операторы сравнения. Используются для сравнения двух значений. Результатом сравнения является логическое значение ИСТИНА, либо ЛОЖ Ь.
Оператор сравнения
Значение (пример)
>= (знак больше или равно)
Больше или равно (A1>=B1)
<= (знак меньше или равно)
Меньше или равно (A1<=B1)
Текстовый оператор
Значение (пример)
Объединение последовательностей знаков в одну последовательность («Северный»&»ветер»)
Оператор ссылки. Для описания ссылок на диапазоны ячеек используются следующие операторы.
Оператор ссылки
Значение (пример)
Ставится между ссылками на первую и последнюю ячейки диапазона. Такое сочетание является ссылкой на диапазон (B5:B15)
; (точка с запятой)
Оператор объединения. Объединяет несколько ссылок в одну ссылку (СУММ(B5:B15;D5:D15))
Оператор пересечения множеств, служит для ссылки на общие ячейки двух диапазонов (B7:D7 C6:C8)
Приоритет оператора
Если в одной формуле используется несколько операторов, Microsoft Excel выполняет операции в порядке, показанном в приводимой ниже таблице. Если формула содержит операторы с одинаковым приоритетом, например операторы деления и умножения, они выполняются слева направо.
Возведение в степень
Умножение и деление
Сложение и вычитание
Объединение двух текстовых строк в одну
Использование круглых скобок
Для того чтобы изменить порядок выполнения, заключите часть формулы, которая должна выполняться первой, в скобки. Например, результатом следующей формулы будет число 11, поскольку Microsoft Excel выполняет умножение до сложения. В данной формуле число 2 умножается на 3, а затем к результату добавляется число 5.
Если же с помощью скобок изменить синтаксис, Microsoft Excel сложит 5 и 2, а затем умножит результат на 3; результатом этих действий будет число 21.
В приведенном ниже примере значение формулы выполняется в следующем порядке: определяется значение B4+25, затем полученный результат делится на сумму значений в ячейках D5, E5 и F5.
О константах в формулах
Константа представляет собой готовое (не вычисляемое) значение. Например, дата 09.10.2008, число 210 и текст “Прибыль за квартал” являются константами. Выражение и его значение константами не являются. Если в формуле в ячейке не содержится ссылок на другие ячейки (например, формула имеет вид =30+70+110), значение в такой ячейке изменяется только после изменения формулы вручную.
Что такое операнд в excel
=F7*С14+B12
Формула состоит из трех операндов (аргументов), которые разделены математическими операторами умножения и сложения. Аргументы представлены ссылками на ячейки электронной таблицы.
=ПРОИЗВЕД(А1:А6)+3
Формула из двух аргументов. Первый — функция, второй — константа. Разделитель — математический оператор сложения.
=15+2*0,2-10
В формуле 4 аргумента, представленных константами. Три математических оператора: сложение, умножение, вычитание
=А15*3+2*6
В формуле 4 аргумента: ссылка на ячейку и три константы. Операторы — два умножения и один — сложения.
- Константа
- Ссылка на ячейку
- Функция
Константы – текстовые или числовые значения, которые вводятся в ячейку и не могут изменяться во время вычислений
Пример:
Ссылка на ячейку или группу ячеек – способ, которым указывается конкретная ячейка или несколько ячеек.
Пример:
Ссылка на отдельную ячейку является ее координатой.
Значение пустой ячейки равно 0.
Ссылки на ячейки бывают двух типов:
- Относительные – ячейки обозначаются относительным смещением от ячейки с формулой (например: F7).
- Абсолютные – ячейки обозначаются координатами ячеек в сочетании со знаком $ (например: $F$7).
- Комбинация предыдущих типов (например: F$7) – смешанные.
При копировании формул относительные ссылки изменяются на размер перемещения!
Операторами обозначаются операции, которые следует выполнить над операндами формулы
В Microsoft Excel включено четыре вида операторов:
- арифметические операторы
- операторы сравнения
- операторы ссылок
- текстовые операторы
Служат для выполнения арифметических операций, таких как сложение, вычитание, умножение
Арифметические операции выполняются над числами!
Используются арифметические операторы:
Используются для сравнения двух значений. Результатом сравнения является логическое значение: либо ИСТИНА, либо ЛОЖЬ.
Для описания ссылок на диапазоны ячеек
Для объединения нескольких текстовых строк в одну
- Формула в Microsoft Excel всегда начинается со знака равенства (=). Знак равенства свидетельствует о том, что последующие знаки составляют формулу
- Элементы, следующие за знаком равенства, являются операндами, разделяемыми операторами вычислений
- Формулы вычисляют значения в определенном порядке. Порядок определяется обычными математическими законами: формула вычисляется слева направо, в соответствии с определенным порядком (приоритетом) для каждого оператора в формуле
- Если формула содержит операторы с одинаковым приоритетом — например операторы деления и умножения — они выполняются слева направо
- Если в одной формуле используется несколько операторов, Microsoft Excel выполняет операции в порядке, показанном в таблице
Повышение приоритета операций нужно для обеспечения правильности вычислений.
Для повышения приоритета операции используют круглые скобки
Допустим, необходимо составить формулу для вычисления выражения, представленного в виде дроби:
Правильный результат достигается только в третьем случае. Числитель делится на знаменатель.
Если формула в ячейке не может быть правильно вычислена, Microsoft Excel выводит в ячейку сообщение об ошибке
Если формула содержит ссылку на ячейку, которая содержит значения ошибки, то вместо этой формулы также будет выводиться сообщение об ошибке
#### – ширина ячейки не позволяет отобразить число в заданном формате;
#ИМЯ? – Microsoft Excel не смог распознать имя, использованное в формуле;
#ДЕЛ/0! – в формуле делается попытка деления на нуль;
#ЧИСЛО! – нарушены правила задания операторов, принятые в математике;
#Н/Д – такое сообщение может появиться, если в качестве аргумента задана ссылка на пустую ячейку;
#ПУСТО! – неверно указано пересечение двух областей, которые не имеют общих ячеек;
#ССЫЛКА! – в формуле задана ссылка на несуществующую ячейку;
Операторы в Excel
Под операторами в Excel подразумеваются специальные символы, которые используются в формулах для выполнения определенных вычислений. В MS Excel cсуществуют 4 вида операторов, такие как арифметические, логические, операторы ссылок и текстовый оператор.
Арифметические операторы в Excel
Арифметические операторы предназначены для выполнения арифметических операций, таких как сложение, вычитание, умножение, деление и т.д.
В Excel доступны шесть арифметических операторов. В следующей таблице объясняется, что каждый из них делает.
Статья: «создание и использование формул в excel»
Формулы являются одним из мощных инструментов математической обработки данных в MS Excel. Формула вводится в ячейку рабочего листа, как и любая другая информация. Если формула введена правильно, MS Excel производит вычисление и его результат помещает прямо в ячейку. Содержимым ячейки по-прежнему является набранная формула: в режиме редактирования ячейки она появляется на экране, кроме того, указав на ячейку (сделав ее активной), содержащееся в ней 2 математическое выражение можно увидеть и отредактировать в строке формул. Но в обычном режиме MS Excel показывает в ячейке только результат вычислений. Этот результат можно использовать и в других формулах, ссылаясь на ячейку. Любые формулы начинаются со знака равенства (=). Без знака равенства вводимые данные, если они не представляют собой числа, воспринимаются как текст. В формулу могут входить числовые константы, операторы, адреса ячеек и функции
Операции и приоритеты
Установка флажка «Формулы» на вкладке «Вид» диалогового окна«Параметры» (меню «Сервис») обеспечивает отображение самих формул, а не вычисленных по ним значений.
Первым символом ячейки должен быть знак равенства. Только в этом случае MS Excel воспринимает ее содержимое как формулу. Затем вводится математическое выражение, по которому будет вычисляться содержимое ячейки. В отличие от Mathematics, Mathcad, Equation Editor и других специализированных программ, MS Excel не имеет средств конструирования математических формул в их естественном виде. Пользователь должен набирать выражение в виде текста в одну строку. Для этого необходимо знать синтаксис формулы. Опишем его. Любое математическое выражение состоит из операндов и операторов. В MS Excel операндами являются константы (числовые, текстовые, даты или времени), ссылки на другие ячейки, имена переменных и функций, а также математические выражения.
Операторы — обозначения математических операций, совершаемых над операндами. Например, в формуле =А4 * (b+2) ссылка А4 и выражение b+2 являются операндами, а знак умножения " * " — оператором. В свою очередь, операнд b+2 состоит из операндов b (имя переменной), 2 (число) и оператора сложения " + ". Между частями формулы для удобочитаемости можно добавлять пробелы. Для группирования операндов, как и в математике, служат круглые скобки.
Перечислим типы операндов формул MS Excel
Служат для выполнения арифметических операций, таких как сложение, вычитание, умножение. Операции выполняются над числами. Используются следующие арифметические операторы.
Арифметический оператор
Значение (пример)
Вычитание (3–1)
Отрицание (–1)
Возведение в степень (3^2)
Используются для сравнения двух значений. Результатом сравнения является логическое значение: либо ИСТИНА, либо ЛОЖЬ.
>= (знак больше или равно)
Больше или равно (A1>=B1)
<= (знак меньше или равно)
Меньше или равно (A1<=B1)
Амперсанд (&) используется для объединения нескольких текстовых строк в одну строку.
Текстовый оператор
Значение (пример)
Объединение последовательностей знаков в одну последовательность («Северный»&«ветер»)
Оператор ссылки
Для описания ссылок на диапазоны ячеек используются следующие операторы.
Оператор ссылки
Значение (пример)
Ставится между ссылками на первую и последнюю ячейки диапазона. Такое сочетание является ссылкой на диапазон (B5:B15)
; (точка с запятой)
Оператор объединения. Объединяет несколько ссылок в одну ссылку (СУММ(B5:B15;D5:D15))
Оператор пересечения множеств, служит для ссылки на общие ячейки двух диапазонов (B7:D7 C6:C8)
Порядок выполнения действий в формулах
Формулы вычисляют значения в определенном порядке. Формула в Microsoft Excel всегда начинается со знака равенства (=). Знак равенства свидетельствует о том, что последующие знаки составляют формулу. Элементы, следующие за знаком равенства, являются операндами, разделяемыми операторами вычислений. Формула вычисляется слева направо, в соответствии с определенным порядком для каждого оператора в формуле.
Приоритет оператора
Если в одной формуле используется несколько операторов, Microsoft Excel выполняет операции в порядке, показанном в следующей таблице. Если формула содержит операторы с одинаковым приоритетом — например операторы деления и умножения — они выполняются слева направо.
Возведение в степень
Умножение и деление
Сложение и вычитание
Объединение двух текстовых строк в одну
Использование круглых скобок
Для того чтобы изменить порядок выполнения, заключите часть формулы, которая должна выполняться первой, в скобки. Например, результатом следующей формулы будет число 11, поскольку Microsoft Excel выполняет умножение до сложения. В данной формуле число 2 умножается на 3, а затем к результату добавляется число 5.
Если же с помощью скобок изменить синтаксис, Microsoft Excel сложит 5 и 2, а затем умножит результат на 3; результатом этих действий будет число 21.
В приведенном ниже примере скобки вокруг первой части формулы определяют следующий порядок вычислений: определяется значение B4+25, затем полученный результат делится на сумму значений в ячейках D5, E5 и F5.
Ввод формул
Формулу можно вводить либо непосредственно в ячейку, либо в строку формул. Если в ячейку введена формула, то после нажатия Enter в этой ячейке отобразится результат вычислений по этой формуле. Если сделать данную ячейку текущей, то сама формула будет отображена в строке формул, а в ячейке – результат вычислений.
Например, пусть требуется ввести в ячейку А1 сумму чисел 10 и 20. Для этого надо выделить ячейку А1 и ввести
=10+20 . (рис. 1)
После нажатия Enter в текущей ячейке А1 появилось вычисленное значение – число 30, а в строке формул – сама формула, по которой произведено вычисление. (рис. 2)
Ссылка в Microsoft Excel — адрес ячейки или связного диапазона ячеек. В каждом листе Excel может быть 256 (2 в 8 степени) столбцов и 65536 (2 в 16 степени) строк. Адрес ячейки определяется пересечением столбца и строки, как в шахматах или морском бое, например: A1, C16. Адрес диапазона ячеек задается адресом верхней левой ячейки и нижней правой, например: A1:C5.
Виды представления ссылок
Есть два вида представления ссылок в Microsoft Excel:
Стиль ссылок R1C1 (здесь R — row (строка), C — column (столбец)).
Включить стиль ссылок R1C1 можно в настройках «Сервис» —> «Параметры» —> закладка «Общие» —> галочка «Стиль ссылок R1C1»
Если включен классический вид и в ячейке с адресом A1 находится формула «=B3» (B3 — относительная ссылка), то после переключения в вид R1C1 она примет вид «=R[2]C[1]». В режиме R1C1 в квадратных скобках указывается относительное смещение. В скобках после R указано смещение в строках (row) — две строки вниз (вниз, потому что число положительное, если бы стояло «R[-2]» — было бы вверх). В скобках после C указано смещение в столбцах — 1 столбец вправо (если бы было C[-1] — влево). В общем случае направления вниз и вправо считаются положительными, вверх и влево — отрицательными.
Если включен классический вид и в ячейке с адресом A1 находится формула «=$B$3» ($B$3 — абсолютная ссылка), то после переключения в вид R1C1 она примет вид «=R3C2». Квадратных скобок нет, то есть указано не относительно смещение по отношению к положению формула, а абсолютное смещение по отношению к всему листу (вспомните абсолютную и относительную системы координат).
В приведенном ниже примере функция СРЗНАЧ используется для расчета среднего значения в диапазоне B1:B10 на листе «Маркетинг» в той же самой книге.
Ссылка на другой лист в той же книге
Обратите внимание на то, что имя листа и восклицательный знак (!) предшествуют ссылке на диапазон ячеек.
Типы ссылок (типы адресации):
Ссылки в Excel бывают 3-х типов:
Относительные ссылки (пример: A1);
Абсолютные ссылки (пример: $A$1);
Смешанные ссылки (пример: $A1 или A$1, они наполовину относительные, наполовину абсолютные).
Знак $ здесь никакого отношения к денежным единицам не имеет, это лишь способ указать Excel тип ссылки. Различия между разными типами ссылок можно увидеть, если потянуть за маркер автозаполнения активной ячейки или диапазона ячеек, содержащих формула со ссылками.
Относительные ссылки
Относительная ссылка в формуле, например A1, основана на относительной позиции ячейки, содержащей формулу, и ячейку, на которую указывает ссылка. При изменении позиции ячейки, содержащей формулу, изменяется и ссылка. При копировании формулы вдоль строк и вдоль столбцов ссылка автоматически корректируется. По умолчанию в новых формулах используются относительные ссылки. Например, при копировании относительной ссылки из ячейки B2 в ячейку B3, она автоматически изменяется с =A1 на =A2.
Скопированная формула с относительной ссылкой
Абсолютные ссылки
Абсолютная ссылка ячейки в формуле, например $A$1, всегда ссылается на ячейку, расположенную в определенном месте. При изменении позиции ячейки, содержащей формулу, абсолютная ссылка не изменяется. При копировании формулы вдоль строк и вдоль столбцов абсолютная ссылка не корректируется. По умолчанию в новых формулах используются относительные ссылки, и для использования абсолютных ссылок надо выбрать соответствующий параметр. Например, при копировании абсолютной ссылки из ячейки B2 в ячейку B3, она остается прежней =$A$1.
Скопированная формула с абсолютной ссылкой
Смешанные ссылки
Смешанная ссылка содержит либо абсолютный столбец и относительную строку, либо абсолютную строку и относительный столбец. Абсолютная ссылка столбцов приобретает вид $A1, $B1 и т. д. Абсолютная ссылка строки приобретает вид A$1, B$1 и т. д. При изменении позиции ячейки, содержащей формулу, относительная ссылка изменяется, а абсолютная ссылка не изменяется. При копировании формулы вдоль строк и вдоль столбцов относительная ссылка автоматически корректируется, а абсолютная ссылка не корректируется. Например, при копировании смешанной ссылки из ячейки A2 в ячейку B3, она изменяется с =A$1 на =B$1.
Скопированная формула со смешанной ссылкой
Именованные ссылки
Для упрощения работы с адресом ячейки или диапазона ячеек можно дать ему имя, и затем обращаться к ячейке или диапазону не по его адресу, а по символьному имени. Именованные ссылки можно использовать везде, где можно использовать то значение, на которое указывает ссылка.
Для создания именованной ссылки нужно выделить нужную ячейку или диапазон, затем щелкнуть в текстовое поле (см. рис. ниже) ввести свое имя и нажать Enter. Можно использовать русские имена. Имя не может содержать пробелов и начинаться с цифры. Именованная ссылка может ссылаться на несвязный диапазон ячеек (выделенный с «Ctrl»).
Для вставки именованной ссылки можно воспользоваться кнопкой со стрелкой вниз:
или нажать клавишу «F3», откроется следующее окно:
Пример использования: "=СУММ(tablica_1);"
Стиль трехмерных ссылок
Трехмерные ссылки используются при необходимости анализа данных из одной и той же ячейки или диапазона ячеек на нескольких листах одной книги. Трехмерная ссылка включает в себя ссылку на ячейку или диапазон, перед которой ставятся имена листов. Microsoft Excel использует все листы, хранящиеся между начальным и конечным именами, указанными в ссылке. Например, формула =СУММ(Лист2: Лист13!B5) суммирует все значения, содержащиеся в ячейке B5 на всех листах в диапазоне от Лист2 до Лист13 включительно.
Трехмерные ссылки могут быть использованы для создания ссылок на ячейки на других листах, определения имен и создания формул с использованием следующих функций: СУММ, СРЗНАЧ, СРЗНАЧА, СЧЁТ, СЧЁТЗ, МАКС, МАКСА, МИН, МИНА, ПРОИЗВЕД, СТАНДОТКЛОН, СТАНДОТКЛОНА, СТАНДОТКЛОНП, СТАНДОТКЛОНПА, ДИСП, ДИСПА, ДИСПР и ДИСПРА.
Трехмерные ссылки нельзя использовать в формулах массива.
Трехмерные ссылки нельзя использовать вместе с оператором пересечения (пробел), а также в формулах, использующих неявное пересечение.
Изменения в трехмерных ссылках при перемещении, копировании, вставке или удалении листов
Нижеследующие примеры поясняют, какие изменения происходят в трехмерных ссылках при перемещении, копировании, вставке и удалении листов, на которые такие ссылки указывают. Предположим, что используется формула " =СУММ(Лист2: Лист6!A2:A5)", суммирующая содержимое ячеек с A2 по A5 с лист2 по лист6 включительно.
Вставка или копирование. Если между листом 2 и листом 6 книги вставить новые листы, Microsoft Excel добавит в сумму содержимое ячеек с A2 по A5 на новых листах.
Удаление. Если между листом 2 и листом 6 книги удалить листы, Microsoft Excel исключит из суммы содержимое ячеек удаленных листов.
Перемещение. Если переместить листы, находящиеся между листом 2 и листом 6 книги, и разместить их таким образом, что они будут расположены перед листом 2 или после листа 6, Microsoft Excel исключит из суммы содержимое ячеек перемещенных листов.
Перемещение граничного листа. Если переместить лист 2 или лист 6 в новое место книги, Microsoft Excel включит в сумму содержимое ячеек листов, находящихся между листом 2 и листом 6 включительно.
Удаление граничного листа. Если удалить лист 2 или лист 6, Microsoft Excel включит в сумму содержимое ячеек листов, находившихся между ними.
Стиль ссылок R1C1
Также можно использовать стиль ссылок, в котором нумеруются как строки, так и столбцы. Стиль ссылок R1C1 полезен при вычислении положения столбцов и строк в макросах. В стиле ссылок R1C1 Microsoft Excel указывает положение ячейки буквой «R», за которой идет номер строки, и буквой «C», за которой идет номер столбца.
Относительная ссылка (Относительная ссылка. В формуле — адрес ячейки, определяемый на основе расположения этой ячейки относительно ячейки, содержащей ссылку. При копировании ячейки относительная ссылка автоматически изменяется. Относительные ссылки задаются в форме A1.) на ячейку, расположенную на две строки выше и в том же столбце
Относительная ссылка на ячейку, расположенную на две строки ниже и на два столбца правее
Абсолютная ссылка (Абсолютный адрес ячейки. Часть формулы, являющаяся адресом ячейки, и ссылающаяся на данную ячейку независимо от местоположения ячейки с формулой. Абсолютный адрес ячейки имеет формат $A$1.) на ячейку, расположенную во второй строке и во втором столбце
Относительная ссылка на строку, расположенную выше текущей ячейки
Абсолютная ссылка на текущую строку
При записи макроса Microsoft Excel записывает некоторые команды с использованием стиля ссылок R1C1. Например, если записывается такая команда как нажатие кнопки Автосумма для вставки формулы, суммирующей диапазон ячеек, Microsoft Excel использует при записи формулы стиль ссылок R1C1, а не A1.
Выберите пункт Параметры в меню Сервис и перейдите на вкладку Общие.
В меню Сервис установите или снимите флажок Стиль ссылок R1C1.
Об именах в формулах
Для ссылки на ячейки в столбцах и строках можно использовать заголовки этих столбцов и строк листа. Также для представления ячеек, диапазонов ячеек, формул или констант можно создавать имена. Заголовки можно использовать в формулах, содержащих ссылки на данные на том же листе; для представления группы ячеек, находящейся на другом листе, этой группе следует присвоить имя.
Использование текста в формулах
С текстовыми значениями можно выполнять математические операции, если текстовые значения содержат только следующие символы:
Цифры от 0 до 9, + — е Е /
Еще можно использовать пять символов числового форматирования:
При этом текст должен быть заключен в двойные кавычки.
При выполнении вычислений Excel преобразует числовой текст в числовые значения, так результатом вышеуказанной формулы будет значение 88.
Для объединения текстовых значений служит текстовый оператор & (амперсанд). Например, если ячейка А1 содержит текстовое значение «Юрий», а ячейка А2 — «Кордык», то введя в ячейку А3 следующую формулу =А1&А2, получим «ЮрийКордык». Для вставки пробела между именем и фамилией надо написать так =А1&" "&А2. Амперсанд можно использовать для объединения ячеек с разными типами данных. Так, если в ячейке А1 находится число 10, а в ячейке А2 — текст «мешков», то в результате действия формулы =А1&А2, мы получим «10мешков». Причем результатом такого объединения будет текстовое значение.
«Ввод данных в Excel || Эксель || имена ячеек Excel»
Использование имён
Имена ячеек служат не только для того, чтобы с их помощью обращаться к отдельным ячейкам и диапазонам, но и для их использования в формулах. Например, у вас есть простая формула в ячейке КЗ. По ней вы вычисляете общую сумму к выплате, умножая количество часов (ячейка 13), в течение которых вы работали на клиента, на почасовой тариф, установленный для данного клиента (ячейка J3). Обычно подобная формула вводится в ячейку КЗ в таком виде:
Однако если вы присвоили имя Часы ячейке 13 и имя Тариф ячейке J3, можете ввести формулу
в ячейку КЗ. Эта формула намного проще для понимания, нежели =13 * J3.
Для ввода формулы с использованием имен ячеек выполните следующие действия
1. Присвойте имена своим ячейкам, как описано выше в этой главе.
В данном примере имя Часы присвоено ячейке 13, а имя Тариф — ячейке J3.
Расположите табличный курсор в ячейке, в которую будет введена формула.
В нашем примере это ячейка КЗ.
Введите с клавиатуры знак равенства (=), обозначающий начало формулы.
Выберите первую ячейку, входящую в формулу, выделив ее имя в окне списка в
строке формул (как описано выше).
В нашем примере мы ссылаемся на ячейку 13, выбирая ее имя Часы из окна списка имен.
5. Введите с клавиатуры арифметический оператор, используемый в данной
формуле.
В нашем примере это символ «звездочка» (*) для обозначения операции умножения.
6. Укажите вторую ссылку на ячейку, которая присутствует в формуле, выбрав ее
имя из окна списка имен в строке формул.
В данном примере вы выбираете ячейку J3 по ее имени Тариф в окне списка.
7. Щелкните на кнопке Ввод (Enter) или нажмите клавишу <Enter> для заверше
ния ввода формулы.
В рассматриваемом примере Excel вводит формулу =Часы*Тариф в ячейку КЗ.
Обратите внимание: нельзя использовать маркер заполнения для копирования формул, в которых применялись имена ячеек, а не их адреса При копировании формул, использующих имена, Excel переносит исходные формулы без их изменения в соответствии с новым местоположением (другими словами, рассматривает имена ячеек как абсолютные ссылки). Изучите следующий раздел этой главы, в котором рассказывается, как избежать этих сложностей, а также описаны способы использования заголовков строк и столбцов таблицы для идентификации ячеек при создании и копировании формул.
Формулы в рабочей таблице вычисляются мгновенно. Если изменить значение в любой ячейке использующейся в формуле, то эта формула выдаст новый результат без малейших усилий с Вашей стороны. Это происходит в том случае, если в Excel установлен режим автоматических вычислений (установлен по умолчанию). В этом режиме при вычислении формул Excel придерживается следующих правил:
Если Вы вносите изменения — например, вводите или редактируете данные или формулы, — Excel мгновенно производит вычисления по тем формулам, в которых используются новые или отредактированные данные.
Если во время выполнения длинных вычислений Вам понадобится что-то изменить в рабочей таблице, то программа временно прекратит вычисления и возобновит их, когда Вы закончите редактирование.
Вычисления по формулам производятся в естественном порядке. Другими словами, если в формуле из ячейки D12 используется результат вычислений по формуле из ячейки D11, то сначала будет вычислена формула из ячейки D11, a потом — из D12.
Впрочем, иногда возникает необходимость управлять процессом вычисления по формулам. Например, если создать рабочую таблицу с тысячами сложных формул, то обнаружится, что Excel производит вычисления с достаточно низкой скоростью. В подобном случае нужно установить в Excel ручной режим вычислений . Это можно сделать с помощью вкладки Вычисления диалогового окна Параметры (рис. 3).
Рис. 3. Диалоговое окно Параметры вкладка Вычисления
Чтобы выбрать ручной режим вычислений, надо установить флажок опции Вручную . При переключении в ручней режим вычислений, автоматически активизируется опция пересчет перед сохранением . Поэтому, если Вы хотите ускорить процесс сохранения файла, снимите флажок этой опции.
Если во время работы в ручном режиме вычислений у Вас остаются какие-либо невычисленные формулы, то в строке состояния появится надпись Вычислить . Для перерасчета формул можно воспользоваться следующими комбинациями клавиш:
F9. Пересчитывает формулы во всех открытых рабочих книгах.
Shift + F9. Пересчитывает только формулы активного рабочего листа. Формулы из других рабочих листов этой же рабочей книги пересчитываться не будут.
Ctrl + Alt + F9. Ускоряет полный пересчет формул.
Режим вычислений, установленный в Excel, относится не только к конкретному рабочему листу. Переход в другой режим вычислений влияет на все открытые рабочие книги, а не только на текущую рабочую книгу.
Присваивание имён константам и формулам
Имена ячеек в формуле
Для присвоения имени служит команда «Присвоить» в меню «Вставка», под меню «Имя». Вверху диалогового окна нужно ввести имя, внизу — ссылку на ячейку (диапазон ячеек), или математическое выражение. Затем следует нажать на кнопку «Добавить». Новое имя войдет в список имен, расположенный на этом же диалоговом окне, и станет доступным для использования в формулах. Любое из имен можно удалить, нажав на одноименную кнопку. Для изменения ссылки, соответствующей определенному имени, следует указать на него мышью, отредактировать выражение внизу окна, и нажать на Вставить. Все присвоенные имена сохраняются в рабочей книге и остаются доступными при последующем открытии файла.
Для перехода на определенную ячейку или выделения заданного
диапазона ячеек достаточно в поле имени строки формул ввести ссылку
на ячейку (диапазон ячеек), или присвоенное имя. Чтобы войти в режим
редактирования поля имени, необходимо щелкнуть по нему мышью.
При именовании ячейки или диапазона ячеек руководствуйтесь такими правилами.
Имена диапазонов должны начинаться с буквы, но ни в коем случае не с цифры. Например, вместо 01 Итоги используйте ИтогиО!.
Имена диапазонов не могут содержать пробелов. Вместо пробела используйте знак подчеркивания для соединения различных частей имени. Например, вместо Итоги 01 используйте Итоги_01.
Имена диапазонов не должны иметь ничего общего с координатами ячеек.
Например, вы не можете дать ячейке имя Q1, так как это действующая координата ячейки. Лучше используйте что-нибудь вроде Q1 ^продажи.
Для назначения имени ячейке или диапазону ячеек выполните следующие действия.
Выделите ячейку или диапазон ячеек, которым необходимо присвоить имя.
Щелкните на адресе ячейки в поле Имя (Name) в строке формул.
Excel выделит адреса ячеек в поле имени.
Введите в поле имени имя выбранной ячейки или диапазона ячеек.
При вводе имени диапазона ячеек придерживайтесь соглашений Excel об именах (обратитесь к списку, приведенному выше в этом разделе).
4. Нажмите клавишу <Enter>.
Чтобы выбрать именованную ячейку или диапазон в рабочей таблице, щелкните на имени этого диапазона в раскрывающемся списке поля Имя (этот список открывается при щелчке на кнопке, расположенной справа от адреса диапазона в строке формул).
Именованные константы
В некоторых формулах применяются константы, например 7,5-процентный налог или 10-процентная скидка. Константа — это число или текст, дата или время, введенные непосредственно в формулу. Число, дата и время вводятся в соответствии с правилами, описанными в пункте,0. Текстовая константа, дата и время заключаются в двойные кавычки/
Если вы не хотите вводить эти константы в ячейки электронной таблицы, чтобы впоследствии использовать их в формулах, создайте отдельный набор именованных констант, которые будут применяться в формулах электронной таблицы.
Например, чтобы создать константу Налог со значением 7,5%, выполните следующие действия.
Выберите команду Вставка/Имя/Присвоитъ (Insert/Name/Create), чтобы
открыть диалоговое окно Присвоение имени (Define Name).
В диалоговом окне Присвоение имени в поле Имя (Name) введите имя кон
станты (в нашем примере — Налог).
Щелкните в поле Формула (Refers to) и замените текущий адрес на выбранное
значение (7,5%).
Щелкните на кнопке Добавить (Add).
Щелкните на кнопке ОК, чтобы закрыть диалоговое окно.
Присвоив константе имя, можно использовать его в формулах электронной таблицы следующим образом.
1. Наберите имя, присвоенное константе, в выбранной области формулы.
2. Вставьте имя, назначенное константе, выполнив команду Вставка/Имя/Вставить (insert/Name/Paste), а затем указав требуемое имя в диалоговом окне Вставить имя (Paste Name).
Заключение
Рассмотрев различные функции создания и использования формул, можно с уверенностью сказать, что возможности Excel весьма разнообразны. Созданным формулам присваиваются имена, которые используются в дальнейшем как упрощенная и более удобная формулировка.
Различные ссылки помогают усовершенствовать пользовательские функции. Тем самым, знание и более глубокое изучение формул улучшают и ускоряют работу пользователя.
Предметный указатель
Константа -, 16
Оператор, 4
синтаксис, 4
Ссылка, 7, 8
Приложение
1. Написать формулы, заполнения диапазона А1:A100 (лист1) равномерно распределенными случайными числами из отрезка [-3,55; 6,55], а диапазона B1:B100 (лист1) случайными целыми числами из отрезка [-20;80]. Скопировать значения указанных диапазонов на Лист2., увеличив вдвое значения второго диапазона. На Листе3 написать формулы:
2. нахождения среднего арифметического, построенных диапазонов Листа2;
3. максимального и минимального элементов, построенных диапазонов Листа2;
4. суммы трех наименьших элементов, построенных диапазонов Листа2;
положительного элемента, который чаще всего встречается в построенных диапазонах Листа2
Для заданного диапазона ячеек рабочего листа Excel.
Написать формулы вычисляющие:
Сумму элементов диапазона, значения которых попадают в отрезок [-5; 10].
Количество элементов диапазона больших некоторого числа, записанного в ячейке рабочей таблицы (например, из ячейки D9).
Количество элементов диапазона, значение которых меньше среднего значения элементов диапазона
Список используемой литературы
Веретенникова Е.Г., Патрушина С.М., Савельева Н.Г. Информатика: Учебное пособие. Серия «Учебный курс».- Ростов н/Д: Издательский центр «Март», 2002.- 416с.
Левкович О.А. Основы компьютерной грамотности: учебное пособие/ О.А.Левкович, Е.С.Шелкоплясов, Т.Н.Шелкоплясова.- 3-е изд.-Мн.: ТетраСистемс, 2005.-528с.
Подкопаев Д.П. Обработка и анализ экономической информации в Microsoft Excel: Пособие для студентов экон. спец./ Д.П.Подкопаев, В.И.Яшкин.- Мн.: БГУ, 2001.-50с.