Какие существуют способы написания формул со ссылками

Ссылка указывает на ячейку или диапазон ячеек, которые содержат данные и используются в формуле. Ссылки позволяют:

использовать данные в одной формуле, которые находятся в разных частях таблицы.

использовать в нескольких формулах значение одной ячейки.

Имеются два вида ссылок:

относительные- зависящие от положения формулы;
абсолютные — не зависящие от положения формулы.

Различие между относительными и абсолютными ссылками проявляется при копировании формулы из текущей ячейки в другие ячейки.

Относительные ссылки. Относительная ссылка определяет расположение ячейки с данными относительно ячейки, в которой записана формула. При изменении позиции ячейки, содержащей формулу, изменяется и ссылка.

При копировании формулы вдоль столбца или строки относительная ссылка корректируется:

смещение на один столбец — изменение в ссылке одной буквы в имени столбца.

смещение на одну строку — изменение в ссылке номера строки на единицу.

Например при копировании формулы из ячейки А2 в ячейку В2, С2 и D2 относительная ссылка автоматически изменяется и рассмотренная выше формула приобретает вид: =B1^2, =C1^2, =D1^2. При копировании этой же формулы в ячейки А3 и А4 получим соответственно =A2^2, =A3^2 (рисунок 3).

Абсолютные ссылки. Абсолютная ссылка всегда ссылается на ячейку, расположенную в определенном (фиксированном) месте. Перед каждой буквой и цифрой ставится знак $, например $B$2. При изменении позиции ячейки, которая имеет формулу, абсолютная ссылка не изменяется. При копировании формулы вдоль строк и столбцов абсолютная ссылка не корректируется (рисунок 4).

Смешанные ссылки. Смешанная ссылка содержит абсолютно адресуемый столбец и относительно адресуемую строку ( $A1) или относительно адресуемый столбец и абсолютно адресуемую строку ( A$1). При изменении позиции ячейки, относительная часть адреса изменяется, а абсолютная часть адреса не изменяется. При копировании формулы вдоль строк и столбцов относительная ссылка корректируется, а абсолютная ссылка нет (рисунок 5).

Источник

Какие существуют способы написания формул со ссылками

Модуль 2
«Формулы»

Ссылки. Типы ссылок(относительные, абсолютные, смешанные). Виды представления ссылок. Именованные ссылки. Формулы в Microsoft Excel. Использование текста в формулах. Использование ссылок в формулах. Операторы. Арифметические операторы. Логические операторы. Оператор объединения 2-х строк текста в одну. Операторы ссылок. Выражения.

Ссылки

Ссылка в Microsoft Excel — адрес ячейки или связного диапазона ячеек. В каждом листе Excel может быть 256 (2 в 8 степени) столбцов и 65536 (2 в 16 степени) строк. Адрес ячейки определяется пересечением столбца и строки, например: A1, C16. Адрес диапазона ячеек задается адресом верхней левой ячейки и нижней правой, например: A1:C5.

Типы ссылок (типы адресации):

Ссылки в Excel бывают 3-х типов:

Относительные ссылки (пример: A1);

Абсолютные ссылки (пример: $A$1);

Смешанные ссылки (пример: $A1 или A$1, они наполовину относительные, наполовину абсолютные).

Знак $ (доллар) здесь никакого отношения к денежным единицам не имеет, это лишь способ указать Excel тип ссылки. Различия между разными типами ссылок можно увидеть, если потянуть за маркер автозаполнения активной ячейки или диапазона ячеек, содержащих формулу со ссылками.

Относительные ссылки

Если поставить в какой то ячейке знак «=», затем щелкнуть левой кнопкой мыши на какой то ячейке, Excel подставляет после «=» относительную ссылку на эту ячейку. Каждый раз, когда мы тянем за маркер автозаполнения или копируем Формулу, содержащую относительные ссылки, Excel пересчитывает адреса всех относительных ссылок в ней в соответствии с новым положением ячейки.

Абсолютные ссылки

Чтобы сделать относительную ссылку абсолютной, достаточно поставить знак «$» перед буквой столбца и адресом строки, например $A$1. Более быстрый способ — выделить относительную ссылку и нажать один раз клавишу «F4», при этом Excel сам проставит знак «$». Если второй раз нажать «F4», ссылка станет смешанной такого типа A$1, если третий раз — такого $A1, если в четвертый раз — ссылка опять станет относительной. И так по кругу.

Смешанные ссылки

Смешанные ссылки являются наполовину абсолютными и наполовину относительными. Знак доллара в них стоит или перед буквой столбца или перед номером строки.
Например, в ячейке записана формула «=A$1». Ссылка A$1 относительная по столбцу A и абсолютная по строке 1. Если мы потянем за маркер автозаполнения эту формулу вниз или вверх, то ссылки во всех скопированных форумулах будут указывать на ячейку A1, то есть будет вести себя как абсолютные. Однако, если потянем вправо или влево — ссылки будут вести себя как относительные, то есть Excel будет пересчитывать их адрес. Таким образом, формулы, созданные автозаполнением, будут использовать один и тот же номер строки ($1), но изменится номер столбца (A, B, C. ).

Читайте также: Способы организации основных производственных процессов
Виды представления ссылок

Есть два вида представления ссылок в 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». Квадратных скобок нет, то есть указано не относительно смещение по отношению к положению формула, а абсолютное смещение по отношению к всему листу.

Именованные ссылки

Для упрощения работы с адресом ячейки или диапазона ячеек можно дать ему имя, и затем обращаться к ячейке или диапазону не по его адресу, а по символьному имени. Именованные ссылки можно использовать везде, где можно использовать то значение, на которое указывает ссылка.

Для создания именованной ссылки нужно выделить нужную ячейку или диапазон, затем щелкнуть в текстовое поле (см. рис. ниже) ввести свое имя и нажать Enter. Можно использовать русские имена. Имя не может содержать пробелов и начинаться с цифры. Именованная ссылка может ссылаться на несвязный диапазон ячеек (выделенный с «Ctrl»).

Для вставки именованной ссылки можно воспользоваться кнопкой со стрелкой вниз:

или нажать клавишу «F3», откроется следующее окно:

Пример использования: «=СУММ(tablica_1);»

Формулы в Microsoft Excel

Excel — программируемый табличный калькулятор. Все расчеты в Excel выполняют формулы. Формулой Excel считает все, что начинается со знака «=». Если в ячейке написать просто «1+1», Excel не будет вычислять это выражение. Однако, если написать «=1+1» и нажать Enter, в ячейке появится результат вычисления выражения — число 2. После нажатия Enter формула не пропадает, ее можно увидеть снова, если сделать двойной щелчок по ячейке, или если выделить ее и нажать F2 или просто нажать Ctrl+Апостроф. Также ее можно увидеть в панели инструментов «Строка формул», если опять же выделить ячейку. После двойного щелчка, нажатия F2 или после щелчка в строке формул, можно изменить формулу, и для завершения нажать клавишу Enter.

В формуле можно использовать различные типы операторов (арифметические и т. п.), текст, ссылки на ячейку или диапазон ячеек, круглые скобки, именованные диапазны. Естественно, в формулах соблюдается приоритет выполнения операций (умножение выполняется раньше сложения и т. п.). Для изменения порядка выполнения операций используются круглые скобки.

Читайте также: Анимация способы создания анимации
Использование текста в формулах

Если в формуле используется текст, то он обязательно должен быть заключен в двойные кавычки. Если написать формулу «=мама», Excel выдаст ошибку, а если написать «=»мама»» — все ок, корректная формула.

Использование ссылок в формулах

Для того, чтобы вставить в формулу адрес ячейки (ссылку на ячейку), не обязательно писать его вручную. Проще поставить знак «=», затем левой кнопкой щелкнуть на нужной ячейке или выделить нужный диапазон ячеек. При этом Excel подставит в формулу ссылку автоматически.

Если в формуле используется несколько ссылок, то каждой из них Excel дает свой цвет. Это очень удобно. Пример: напишите в какой либо ячейке формулу «=A1+D1», нажмите Enter, затем два раза щелкнете по ячейке. В ячейке вы увидите формулу с разноцветными ссылками, а вокруг ячеек A1 и D1 будут прямоугольники соответствующих цветов. Гораздо проще найти, куда указывет ссылка, по цвету прямоугольника, чем просматривать буквы столбцов и номера строк. Наведите курсор мыши на один из разноцветных прямоугольников и перетащите левой кнопкой за границу в другое место. Вы увидите, что при этом меняются и адреса ячеек в формуле — часто это самый быстрый способ подправить адреса в формуле, особенно после копирования маркером автозаполнения.

Операторы

Операторы в Excel бывают бинарные и унарные. Бинарные операторы работают 2 значениями. Например, оператор «*» умножает число слева от себя на число справа от себя. Если число слева или справа опустить, то Excel выдаст ошибку.

Унарные операторы оперируют одним значением. Пример унарных операторов: унарный «+» (ничего не делает), унарный «-» (меняет знак числа справа на противоположенный) или знак «%» (делит число слева на 100).

Арифметические операторы

«+» — сложение (Пример: «=1+1»);

«-» — вычитание (Пример: «=1-1»);

«*» — умножение (Пример: «=2*3»);

«/» — Деление (Пример: «=1/3»);

«^» — Возведение в степень (Пример: «=2^10»);

«%» — Процент (Пример: «=3 %» — преобразуется в 0,03; «=37*8 %» — нашли 8 % от 37). То есть если мы дописываем после числа знак «%», то число делится на 100.

Логические операторы

«>» — больше;

« =» — больше, либо равно;

« » — неравно (проверка на неравенство).

Оператор объединения 2-х строк текста в одну

Оператор «&» (амперсанд) служит для «склеивания» между собой двух текстовых строк. Например, в ячейке A1 текст «мама», в ячейке A2 текст «мыла раму». В A3 пишем формулу «=A1 & A2». В результате в ячейке A3 появится текст «мамамыла раму». Как видим, пробел между двумя строками автоматически не ставится. Чтобы вставить этот пробел, нужно изменить формулу вот так: «=A1 & » » & A2».

Операторы ссылок

: (двоеточие). Ставится между ссылками на первую и последнюю ячейку диапазона. Такое сочетание является ссылкой на диапазон (A1:A15);

; (точка с запятой). Объединяет несколько ссылок в одну ссылку (СУММ(A1:A15;B1:B15));

(пробел). Оператор пересечения множеств. Служит для ссылки на общие ячейки двух диапазонов (B7:D7 C6:C8).

Выражения

Выражения в Excel бывают арифметические и логические. Арифметическое выражение (например, «=2*(2+5)», результат — 14) в результате дает числовое значение (положительное, отрицательное, дробное число). Логическое выражение (например, «=3>5», результат — логическое значение «ЛОЖЬ»)в результате может дать лишь 2 значения: «ЛОЖЬ» или «ИСТИНА» (одно число либо больше другого, либо не больше, других вариантов нет).

Источник