Операторы в формулах и их иерархия
Относительные ссылки
Это обычные ссылки в виде буква столбца-номер строки ( А1, С5, т.е. "морской бой"), встречающиеся в большинстве файлов Excel. Их особенность в том, что они смещаются при копировании формул. Т.е. C5, например, превращается в С6, С7 и т.д. при копировании вниз или в D5, E5 и т.д. при копировании вправо и т.д. В большинстве случаев это нормально и не создает проблем.
Смешанные ссылки
Иногда тот факт, что ссылка в формуле при копировании "сползает" относительно исходной ячейки - бывает нежелательным. Тогда для закрепления ссылки используется знак доллара ($), позволяющий зафиксировать то, перед чем он стоит. Таким образом, например, ссылка $C5 не будет изменяться по столбцам (т.е. С никогда не превратится в D, E или F), но может смещаться по строкам (т.е. может сдвинуться на $C6, $C7 и т.д.). Аналогично, C$5 - не будет смещаться по строкам, но может "гулять" по столбцам. Такие ссылки называют смешанными.
Абсолютные ссылки
Ну, а если к ссылке дописать оба доллара сразу ($C$5) - она превратится в абсолютную и не будет меняться никак при любом копировании, т.е. долларами фиксируются намертво и строка и столбец.
Самый простой и быстрый способ превратить относительную ссылку в абсолютную или смешанную - это выделить ее в формуле и несколько раз нажать на клавишу F4. Эта клавиша гоняет по кругу все четыре возможных варианта закрепления ссылки на ячейку: C5 → $C$5 → $C5 → C$5 и все сначала.
|
|
|
Все просто и понятно. Но есть одно "но".
Предположим, мы хотим сделать абсолютную ссылку на ячейку С5. Такую, чтобы она ВСЕГДА ссылалась на С5 вне зависимости от любых дальнейших действий пользователя. Выясняется забавная вещь - даже если сделать ссылку абсолютной (т.е. $C$5), то она все равно меняется в некоторых ситуациях. Например: Если удалить третью и четвертую строки, то она изменится на $C$3. Если вставить столбец левее С, то она изменится на D. Если вырезать ячейку С5 и вставить в F7, то она изменится на F7 и так далее. А если мне нужна действительно жесткая ссылка, которая всегда будет ссылаться на С5 и ни на что другое ни при каких обстоятельствах или действиях пользователя?
Действительно абсолютные ссылки
Решение заключается в использовании функции ДВССЫЛ (INDIRECT), которая формирует ссылку на ячейку из текстовой строки. Если ввести в ячейку формулу:
=ДВССЫЛ("C5")
=INDIRECT("C5")
то она всегда будет указывать на ячейку с адресом C5 вне зависимости от любых дальнейших действий пользователя, вставки или удаления строк и т.д. Единственная небольшая сложность состоит в том, что если целевая ячейка пустая, то ДВССЫЛ выводит 0, что не всегда удобно. Однако, это можно легко обойти, используя чуть более сложную конструкцию с проверкой через функцию ЕПУСТО:
|
|
|
=ЕСЛИ(ЕПУСТО(ДВССЫЛ("C5"));"";ДВССЫЛ("C5"))
=IF(ISBLANK(INDIRECT("C5"));"";INDIRECT("C5"))
6.3.4. Формулы и функции
Ввод формул
С помощью формул можно выполнять вычисления и анализ данных рабочего листа.
В поле имени строки формул представлен адрес или имя активной ячейки.
Поле имени используется также для присвоения имен диапазонам ячеек или для перехода к поименованным диапазонам. Имена диапазонов находятся в списке, открываемом щелчком мыши на кнопке со стрелкой, расположенной справа от поля имени. В поле имени можно указать адрес ячейки (или диапазона ячеек) и быстро перейти к нужной ячейке или выделить диапазон ячеек.
В поле содержимого строки формул отображено содержимое активной ячейки. Ввод содержимого ячейки выполняется, как правило, непосредственно в самой ячейке. После щелчка мышью в строке формул (в строке формул будет представлен курсор ввода) содержимое ячейки может быть изменено и в строке формул.
В качестве содержимого ячейки, в которой помещена формула, в поле содержимого строки формул отображена формула, в то время как в самой ячейке представлен результат вычисления формулы.
|
|
|
Составные части формулы
Формула может представлять собой сочетание констант, операторов, ссылок, функций и имен диапазонов. Все формулы в Excel начинаются со знака равенства. Приведем несколько примеров формул разного состава:
1. Только константы и операторы: = 100 + 1235 / (34 + 67) * 42
Подобные формулы встречаются довольно редко. Они статичны, т. е. не зависят от содержимого других ячеек рабочего листа.
2. Ссылки на ячейки и операторы: = B2 + C2 + D2 / D1S * AS1
Результат вычисления формулы зависит от содержимого других ячеек.
3. Заголовки столбцов и строк: = Служащий Отдел Заработная плата
Если строка и столбец таблицы имеют имена (заголовки), то для ссылки на ячейку, стоящую на пересечении этих строки и столбца, достаточно записать их в формуле, разделив пробелом.
4. Имена диапазонов ячеек: = Заработная плата : Налоги
5. Функции рабочего листа: = CУMM (D2:D10) = ABS(D2)
6. Функции, ссылки, имена диапазонов ячеек, константы, операторы: = СУММ (2:10) / $А$1 * - Налоги + 100
Операторы в формулах и их иерархия
Операторы определяют способ вычисления результата (значения) на основании отдельных элементов формулы. Excel различает три вида операторов
|
|
|
1. Арифметические операторы.
Используются для выполнения арифметических операций и возвращают в качестве результата числовое значение. + , – Сложение, вычитание; * , / Умножение, деление; % Определение значения процента; ^ Возведение в степень.
2. Операторы сравнения.
Сравнивают значения и возвращают в качестве результата логические значения ИСТИНА или ЛОЖЬ.
= Равно; < , > Меньше, больше; <= Меньше или равно; >= Больше или равно; <> Не равно.
3. Текстовой оператор.
& Объединяет отдельные фрагменты текста. Оператор определения значения процента следует вводить непосредственно после числового значения. В этом случае числовое значение при выполнении вычислений будет разделено на 100.
Если формула содержит несколько операторов, то они будут обработаны в формуле в следующей последовательности: – Знак отрицательного числа; % Оператор определения процента; ^ Возведение в степень; * , / Умножение, деление; + , – Сложение, вычитание; & Объединение текста.
На последней ступени в иерархии расположены операторы сравнения.
Если формула содержит несколько операторов с одинаковым уровнем приоритета, они будут обработаны слева направо.
Если последовательность вычислений, определяемая приоритетами используемых в формуле операторов, должна быть изменена, используйте круглые скобки. Представленные в скобках выражения будут обработаны в первую очередь, и полученный результат использован при выполнении дальнейших вычислений в формуле.
Если в формуле присутствует открывающая круглая скобка, то далее в этой формуле должна присутствовать и соответствующая ей закрывающая. Чтобы улучшить контроль за уровнем вложения скобок, при вводе закрывающей скобки соответствующая открывающая выделяется на непродолжительное время полужирным начертанием.
Соответствующая пара скобок будет отображена в полужирном начертании, если курсор ввода при перемещении в строке формул или в самой ячейке в режиме правки будет помещен в позицию непосредственно за открывающей или перед закрывающей скобкой.
При применении ссылок в формулах Excel предоставляет в распоряжение пользователя дополнительно три адресных оператора. Эти операторы всегда имеют приоритет перед остальными операторами.
Ввод формулы
Формулы практически всегда содержат ссылки на ячейки, содержимое которых используется в вычислениям.
- Поместите курсор мыши на ячейку, в которой вы хотите ввести формулу.
- Укажите в качестве первого символа знак равенства.
- Введите часть формулы вплоть до позиции первой ссылки. Формулу следует вводить с клавиатуры как обычный текст.
- Задайте ссылку на нужную ячейку или на диапазон ячеек. Ссылка на ячейку может быть задана в формуле двумя способами: щелчком на нужной ячейке (метод указания) и вводом с клавиатуры.
- Введите оставшуюся часть формулы. Завершите ввод формулы нажатием клавиши [Enter].
В ячейке представлен результат вычисления формулы. Саму формулу можно теперь увидеть только в строке формул.
В формуле вы можете использовать имена диапазонов ячеек или заголовки. Список определенных в текущей рабочей книге имен можно открыть, щелкнув на кнопке со стрелкой, расположенной справа от поля имени строки формул. Вы можете также нажать клавишу “F5” и выбрать имя диапазона в диалоговом окне.
Дата добавления: 2018-04-04; просмотров: 245; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!