Трехмерные ссылки
Трехмерные ссылки используются при необходимости анализа данных из одной и той же ячейки или одного и того же диапазона ячеек на РАЗНЫХ листах одной книги. Трехмерная ссылка включает в себя ссылку на ячейку или диапазон, перед которой ставятся имена листов. Например, формула
=СУММ (Лист2:Лист3!A130:J130 )
суммирует все значения, содержащиеся в ячейках A130 : J130 на всех листах в диапазоне от Лист2 до Лист3 включительно (имена листов назначаются по умолчанию, но пользователь может назначать собственные имена, поэтому между листами с именами Лист2, Лист3 может стоять произвольное количество листов с разными именами) .
•Трехмерные ссылки могут быть использованы в функциях: СУММ, СРЗНАЧ, СЧЁТ, МАКС, МАКСА, МИН, МИНА, ПРОИЗВЕД, и т.д.
•Трехмерные ссылки нельзя использовать в формулах массива (т.е. выполняющих несколько вычислений над одним или несколькими наборами значений и возвращающих один или несколько результатов).
Изменения в трехмерных ссылках при перемещении, копировании, вставке или удалении листов
Нижеследующие примеры поясняют, какие изменения происходят в трехмерных ссылках при перемещении, копировании, вставке и удалении листов, на которые такие ссылки указывают. Предположим, что используется формула
=СУММ ( Лист2 : Лист6 ! A2 : A5 )
, суммирующая содержимое ячеек с A2 по A5 с лист2 по лист6 включительно.
Вставка или копирование. Если между листом 2 и листом 6 книги вставить новые листы, Microsoft Excel добавит в сумму содержимое ячеек с A2 по A5 на новых листах (по умолчанию значения 0).
Удаление. Если между листом 2 и листом 6 книги удалить листы, Microsoft Excel исключит из суммы содержимое ячеек удаленных листов.
Перемещение. Если переместить листы, находящиеся между листом 2 и листом 6 книги, и разместить их таким образом, что они будут расположены перед листом 2 или после листа 6, Microsoft Excel исключит из суммы содержимое ячеек перемещенных листов.
Перемещение граничного листа. Если переместить лист 2 или лист 6 в новое место книги, Microsoft Excel включит в сумму содержимое ячеек листов, находящихся между листом 2 и листом 6 включительно.
Удаление граничного листа. Если удалить лист 2 или лист 6, Microsoft Excel вкл ючит в сумму содержимое ячеек листов, находившихся между ними. Соответственно, начало или конец диапазона изменятся на Лисе3 или Лист5.
Стиль ссылок r1c1
Этот стиль ссылок нумерует как строки, так и столбцы. Стиль ссылок R1C1 полезен при вычислении положения столбцов и строк в макросах (Макрос. Действие или набор действий, используемые для автоматизации выполнения задач. Макросы записываются на языке программирования Visual Basic для приложений (VBA).). В стиле ссылок R1C1 Microsoft Excel указывает положение ячейки буквой «R», за которой идет номер строки, и буквой «C», за которой идет номер столбца.
|
Ссылка |
Значение |
|
R [ -2 ] C |
Относительная ссылка в том же стлб. стл.столбце |
|
R [ 2 ] C [ 2 ] |
Относительная ссылка на ячейку, расположенную на две строки ниже и на два столбца правее |
|
R 2 C 2 |
Абсолютная ссылка на ячейку, расположенную во второй строке и во втором столбце |
|
R [ -1 ] |
Относительная ссылка на строку, расположенную выше текущей ячейки |
|
R |
Абсолютная ссылка на текущую строку |
При записи макроса Microsoft Excel записывает некоторые команды с использованием стиля ссылок R1C1. Например, если записывается такая команда как нажатие кнопки Автосумма для вставки формулы, суммирующей диапазон ячеек, Microsoft Excel использует при записи формулы стиль ссылок R1C1, а не A1.
ПРИМЕРЫ ФОРМУЛ
|
|
12 |
13 |
14 |
|||
|
4 |
1 |
|
4 |
|||
|
5 |
3 |
|
8 |
|||
|
6 |
5 |
|
12 |
|||
|
7 |
7 |
|
7 |
|||
|
|
12 |
13 |
14 |
|||
|
4 |
1 |
|
=RC[-2]+R[1]C[-2] |
|||
|
5 |
3 |
|
=RC[-2]+R[1]C[-2] |
|||
|
6 |
5 |
|
=RC[-2]+R[1]C[-2] |
|||
|
7 |
7 |
|
=RC[-2]+R[1]C[-2] |
|||
Обратить внимание, что при протягивании формул формула не изменяется, но значения вычисляются корректно, так как адреса яччек определяются относительно текущей ячейки –результата.
Чтобы включить или выключить стиль ссылок R1C1
1.Выберите пункт Параметры Excel - > Формулы -> Стиль ссылок R1C1 ( включить-выключить)
Операции в формулах
В Microsoft Excel включено четыре вида операций (операторов):
-
арифметические,
-
текстовые,
-
операторы сравнения,
-
и операторы ссылок.
Арифметические операторы.
Служат для выполнения арифметических операций, таких как сложение, вычитание, умножение. Операции выполняются над числами. Используются следующие арифметические операторы.
Оператор Значение (приме)
+ (знак плюс) Сложение 3
– (знак минус) Вычитание 3–1
Отрицание –1
* (звездочка) Умножение 3*3
/ (косая черта) Деление 3/3
% (знак процента) Процент 20%
^ (крышка) Возведение в степень 3^2
Операторы сравнения.
Используются для сравнения двух значений. Результатом сравнения является логическое значение: либо ИСТИНА, либо ЛОЖЬ.
Оператор Значение (пример)
= (знак равенства) Равно A1=B1
> (знак больше) Больше A1>B1
< (знак меньше) Меньше A1<B1
>= (знак больше или равно) Больше или равно A1>=B1
<= (знак меньше или равно) Меньше или равно A1<=B1
<> (знак не равно) Не равно A1<>B1
Текстовый оператор конкатенации.
Амперсанд (&) используется для объединения нескольких текстовых строк в одну строку.
Примеры
А10 & B10
"Северный " & " ветер"
Операторы ссылки.
Для описания ссылок на диапазоны ячеек используются следующие операторы.
Оператор Значение (пример)
: (двоеточие) Ставится между ссылками на (B5:B15)
первую и последнюю ячейки диапазона.
Такое сочетание является ссылкой на диапазон
; (точка с запятой) Объединение нескольких ссылок (СУММ(B5:B15;D5:D15))
в одну ссылку
(пробел) Оператор пересечения множеств, (B7:D7 C6:C8)
служит для ссылки на общие ячейки
двух диапазонов
Порядок выполнения действий в формулах
Формулы вычисляют значения в определенном порядке. Формула в Microsoft Excel всегда начинается со знака равенства (=). Знак равенства свидетельствует о том, что последующие знаки составляют формулу. Элементы, следующие за знаком равенства, являются операндами, разделяемыми операторами вычислений. Формула вычисляется слева направо, в соответствии с определенным порядком для каждого оператора в формуле.
Если в одной формуле используется несколько операторов, Microsoft Excel выполняет операции в порядке, показанном в следующей таблице. Если формула содержит операторы с одинаковым приоритетом — например операторы деления и умножения — они выполняются слева направо.
Приоритеты в порядке убывания
|
: (двоеточие) (один пробел) , (запятая) |
Операторы ссылок. |
|
– |
Знак «минус» Арифметические |
|
% |
Процент |
|
^ |
Возведение в степень |
|
* и / |
Умножение и деление |
|
+ и – |
Сложение и вычитание |
|
& |
Объединение двух текстовых строк в одну |
|
= < > <= >= <> |
Сравнение |
Для того чтобы изменить порядок выполнения, заключите часть формулы, которая должна выполняться первой, в скобки.
|
|
L |
J |
K |
|
4 |
1 |
|
ИСТИНА |
|
5 |
3 |
|
8 |
|
6 |
5 |
|
|
|
7 |
7 |
|
|
|
=L4<L5+L6>L7 |
|||
|
=СУММ(L4:L7 L5:L6) |
|||