Смешанная ссылка в excel как сделать

Многие пользователи успешно выполняют поставленные перед ними задачи и без применения разных типов ссылок.

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

А можно нажать «F4» несколько раз в нужном месте и в результате выполнить тот же объем работ, но с гораздо меньшими затратами времени.

Смешанные ссылки являются наполовину абсолютными и наполовину относительными.

Смешанная ссылка указывается, если при копировании и перемещении не меняется номер строки или наименование столбца. При этом символ $ в первом случае ставится перед номером строки, а во втором — перед наименованием столбца.

Пример:

• В$5, D$12 – смешанная ссылка, не меняется номер строки;
• $B5, $D12 — смешанная ссылка, не меняется наименование столбца.

Изменение типа ссылки производится циклически, в результате последовательных нажатий функциональной клавиши F4 в то время, когда курсор находится в тексте ссылки. Если, например, имеется ссылка на ячейку А1, то при каждом нажатии клавиши F4 вид ссылки в строке формул будет изменяться:

А1 → $A$1 → A$1 → $А1 → А1 →$A$1 и т. д.

Применение смешанных ссылок

Пример 1

• В ячейке В1 записана формула «=$A1».
• Ссылка $A1 абсолютная по столбцу и относительная по строке.
• Если мы потянем за Маркер заполнения эту формулу вправо,  то ссылки во всех скопированных формулах будут указывать на ячейку A1, названия столбцов изменяться не будут, то есть ссылки будут вести себя как абсолютные.
• Если потянем вниз — ссылки будут вести себя как относительные, то есть Excel будет пересчитывать их адрес. Таким образом, созданные формулы,  будут использовать один и тот же столбец (А),  но номера строк в них будут меняться (1,2,3…)

Пример 2

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

Для заполнения таблицы используем смешанные ссылки.

1. Рассчитаем оплату труда для Андреева.
2. Для этого в ячейку С3 введем формулу: «=В3*С2»
3. Теперь  необходимо скопировать формулу в строке «Андреев»

за 2 часа работы в день он получит 400 рублей	200 * 2 = 400
за 3 часа — 600 рублей	200 * 3 = 600
за 4 часа — 800 рублей	200 * 4 = 800
и т.д.

Оплата в час (200 рублей) не изменяется (значение ячейки В3). Меняется только количество отработанных часов (ячейки С2, D2, E2 …). Значит, для того, чтобы менять количество отработанных часов, надо, чтобы программа меняла название столбца, но не трогала номер строки. То есть, формула для расчета зарплаты Андреева должна быть такой: =В3*С$2

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

Андреев за 2 часа получит 200 рублей	200 * 2 = 400
Борисов за 2 часа получит 360 рублей	180 * 2 = 360
Сергеев за 2 часа получит 440 рублей	220 * 2 = 440

Из таблицы видно, что не изменяется отработанное время (значение ячейки С2).  Меняется оплата за час (ячейки В3, В4, В5). Значит, для того, чтобы менять оплату за час, надо, чтобы программа меняла номер строки, но не трогала название столбца. Получаем формулу: =$В3*С$2

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

Можно сначала протянуть  формулу по строке Андреева, а потом скопировать вниз (на Борисова и Сергеева):

Можно и наоборот – сначала скопировать вниз, а потом – в сторону.

Полученные результаты:

Полученные результаты в режиме просмотра формул:

Пример 3

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

Для расчета Цены с наценкой для товара (артикул 12456) укажем  в ячейке  С3 формулу  =B3*(1+C2).

• Теперь необходимо внести изменения в  формулу, которые позволят с помощью копирования заполнить значения во всей таблице.
• При «протаскивании» формулы по столбцам нам необходимо, чтобы столбец B (с ценами) был зафиксирован, для этого в формуле перед ссылкой В3  ставим знак $ ($B3).
• Аналогично, при «протаскивании» формулы по строкам, нам необходимо зафиксировать строку 2 (проценты наценки), для этого в формуле в ссылке С2 ставим знак $ перед 2 (С$2) .
• В ячейке C3, таким образом, получилась формула =$B3*(1+C$2).

При протаскивании по диапазону С3 : Е7 такая формула дает правильные значения в каждой ячейке таблицы.

Семинары. Вебинары. Конференции

Актуальные темы. Лучшие лекторы Москвы и РФ. Сертификаты ИПБР. Более 30 тематик в месяц.

Источник: https://lk.usoft.ru/allfunction/f_Excel/f_e_20140403

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

Абсолютные и относительные ссылки в Excel используются при создании формул, которые ссылаются на другую ячейку или диапазон. Если в формуле Excel используется знак доллара ($) в ссылках на ячейку, то многих пользователей Excel это путает.

Но этот знак имеет простое объяснение – он обозначает, следует ли изменять или нет ссылку, когда формула копируется в другие ячейки, и в свою очередь информирует пользователя, что данная ссылка является абсолютной ссылкой.

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

Когда вы разберетесь в различии между абсолютными, относительными и смешанными ссылками, можно сказать, что вы на полпути к освоению мощности и универсальности формул и функций Excel.

Все вы, вероятно, видели знак доллара ($) в формулах Excel и задавались вопросом, что это такое. Действительно, вы можете ссылаться на одну и ту же ячейку четырьмя различными способами, например A1, $A$1, $A1 и A$1.

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

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

Поэтому необходимо потратить несколько минут на изучение того, когда использовать абсолютные, относительные и смешанные ссылки в Excel.

Что такое ссылка на ячейку?

Проще говоря, ссылка на ячейку в Excel является адресом ячейки. Он сообщает MS Excel, где искать значение, которое вы хотите использовать в формуле.

Например, если вы введете простую формулу =A1 в ячейку C1, Excel вытащит значение из ячейки A1 в C1:

Абсолютные и относительные ссылки в Excel – Ссылка на ячейку в Excel

Как уже упоминалось, если вы пишете формулу для одной ячейки, то вы можете использовать любой ссылочный тип, со знаком доллара(смешанная или абсолютная ссылка) или без него (относительная ссылка), результат будет таким же:

Абсолютные и относительные ссылки в Excel – Использование $ в формулах Excel

Но если вы хотите переместить или скопировать формулу на листе, очень важно, чтобы вы выбрали правильный ссылочный тип, чтобы формула правильно копировалась в другие ячейки. В следующих пунктах приведены подробные объяснения и примеры формул для каждого ссылочного типа ячейки.

Относительная ссылка в Excel

Относительная ссылка – это обычная ссылка, которая содержит в себе букву (столбец) и номер (строка) без знака $, например, D14, G5, A3 и т.п.

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

По умолчанию все ссылки в Excel являются относительными ссылками. В следующем примере показано, как работают относительные ссылки.

• Предположим, что у вас есть следующая формула в ячейке B1:
• = A1*10
• Если вы скопируете эту формулу в другую строку в том же столбце, например, в ячейку B2, формула будет корректироваться для строки 2 (A2*10), потому что Excel предполагает, что вы хотите умножить значение в каждой строке столбца А на 10.

Абсолютные и относительные ссылки в Excel – Относительная ссылка в Excel

Если вы копируете формулу с относительной ссылкой на ячейку в другой столбец в той же строке, Excel соответственно изменит ссылку на столбец:

Абсолютные и относительные ссылки в Excel – Копирование формулы с относительной ссылкой в другой столбец

При перемещении или копировании формулы с относительной ссылкой на ячейку в другую строку и другой столбец, ссылка изменится как на столбец так и на строку:

Абсолютные и относительные ссылки в Excel – Копирование формулы с относительной ссылкой в другой столбец и другую строку

Как вы видите, использование относительных ссылок на ячейки в формулах Excel является очень удобным способом выполнения одних и тех же вычислений на всем рабочем листе. Чтобы лучше проиллюстрировать это, давайте рассмотрим конкретный пример относительной ссылки.

Пример относительных ссылок в Excel

Пусть у нас есть электронная таблица, в которой отражены наименование, цена, количество и стоимость товаров.

Абсолютные и относительные ссылки в Excel – Исходные данные

Нам нужно рассчитать стоимость для каждого товара. В ячейке D2 введем формулу, в которой перемножим цену товара А и количество проданных единиц. Формула в ячейке D2 ссылается на ячейку B2 и C2, которые являются относительными ссылками. При перетаскивании маркера заполнения вниз на ячейки, которые необходимо заполнить, формула автоматически изменяется.

Абсолютные и относительные ссылки в Excel – Относительные ссылки

Ниже представлены расчеты с наглядными формулами относительных ссылок.

Абсолютные и относительные ссылки в Excel – Относительные ссылки (режим формул)

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

Абсолютная ссылка в Excel

Абсолютные ссылки используются в противоположной ситуации, то есть когда ссылка на ячейку должна остаться неизменной при заполнении или копировании ячеек. Абсолютная ссылка обозначается знаком $ в координатах строки и столбца, например $A$1.

Знак доллара фиксирует ссылку на данную ячейку, так что она остается неизменной независимо от того, куда смещается формула. Другими словами, использование $ в ссылках ячейках позволяет скопировать формулу в Excel без изменения ссылок.

Абсолютные и относительные ссылки в Excel – Абсолютная ссылка в Excel

Например, если у вас есть значение 10 в ячейке A1, и вы используете абсолютную ссылку на ячейку ($A$1), формула = $A$1+5 всегда будет возвращать число 15, независимо от того, в какие ячейки копируется формула.

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

Следующее изображение демонстрирует разницу абсолютных и относительных ссылок в MS Excel:

Абсолютные и относительные ссылки в Excel – Разница между абсолютными и относительными ссылками в Excel

В реальной жизни вы очень редко будете использовать только абсолютные ссылки в формулах Excel. Тем не менее, существует множество задач, требующих использования как абсолютных ссылок, так и относительных ссылок, как показано в следующем примере.

Пример использования абсолютной и относительных ссылок в Excel

Пусть в рассматриваемой выше электронной таблице необходимо дополнительно рассчитать десятипроцентную скидку. В ячейке Е2 вводим формулу =D2*(1-$H$1). Ссылка на ячейку $H$1 является абсолютной ссылкой на ячейку, и она не будет изменяться при заполнении других ячеек.

Абсолютные и относительные ссылки в Excel – Абсолютная ссылка

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

Абсолютные и относительные ссылки в Excel – Переключение между относительной, абсолютной ссылкой и смешанными ссылками

Или вы можете сделать абсолютную ссылку, введя символ $ вручную с клавиатуры.

Если вы используете Excel for Mac, то для преобразования относительной в абсолютную ссылку или в смешанные ссылки используйте сочетание клавиш COMMAND+T.

Абсолютные и относительные ссылки в Excel – Абсолютная ссылка (режим формул)

Таким образом в отличии от относительных ссылок, абсолютные ссылки не изменяются при копировании или заполнении. Абсолютные ссылки используются, когда нужно сохранить неизменными строку и столбец ячеек.

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

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

Как вы помните, абсолютная ссылка в Excel содержит 2 знака доллара ($), которые блокируют как столбец, так и строку. В смешанной ссылке фиксируется только одна координата (абсолютная), а другая (относительная) изменяется в зависимости от относительного положения строки или столбца:

• Абсолютный столбец и относительная строка, например $A1. Когда формула со смешанной ссылкой копируется в другие ячейки, знак $ перед буквой столбца блокирует ссылку на указанный столбец, чтобы он никогда не менялся. Ссылка относительной строки, без знака доллара, изменяется в зависимости от строки, в которую копируется формула.
• Относительный столбец и абсолютная строка, например A$1. В этой смешанной ссылке ссылка на строку не изменится, а ссылка на столбец будет меняться.

Абсолютные и относительные ссылки в Excel – Смешанные ссылки

Ниже вы представлен пример использования обоих типов смешанных ссылок.

Пример смешанных ссылок в Excel

1. Если нам нужно узнать какая будет стоимость с учетом 10%, 25% и 30% скидки, то в ячейку Е3 вводим формулу =$D3*(1-E$2), фиксируя таким образом столбец D (стоимость) и строку 2 (скидку). В данной формуле используются две смешанные ссылки:
2. $D3 — Абсолютный столбец и относительная строка
3. E$2 — Относительный столбец и абсолютная строка

Абсолютные и относительные ссылки в Excel – Пример использования смешанных ссылок

Заполняем ячейки по столбцам и по строкам. В режиме формул расчеты со смешанными ссылками будут выглядеть так:

Абсолютные и относительные ссылки в Excel – Пример использования смешанных ссылок (режим формул)

При заполнении диапазона E3:G6 такая формула со смешанными ссылками дает соответствующие значения в каждой ячейке.

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

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

Источник: https://naprimerax.org/absoliutnye-i-otnositelnye-ssylki-v-excel

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

В Excel существует два типа ссылок: абсолютные и относительные. Эти ссылки ведут себя по-разному при копировании и заполнении ячеек. Относительные ссылки изменяются когда вы копируете формулу из одной ячейки в другую, а абсолютные ссылки, напротив, не меняются вне зависимости от того, куда бы вы их ни скопировали.

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

По умолчанию, все ссылки в Excel относительные. Когда вы копируете ссылку из одной ячейки в другую, она автоматически изменяется относительно позиции столбца и строки новой ячейки к ячейке, из которой вы скопировали ссылку.

Например, если вы скопируете формулу =A1+B1 из строки 1 в строку 2, то формула изменится на =A2+B2.

Относительные ссылки полезны в том случае, когда нам нужно повторить один и тот же расчет на несколько столбцов и строк.

Как создать и скопировать формулу с относительными ссылками

На примере ниже мы хотим создать формулу, которая поможет в калькуляции итогового счета за заказ в ресторане. Мы хотим в колонке “Итог” рассчитать сумму к оплате за каждое блюдо, в зависимости от его стоимости за единицу и количества. Для этого, мы создадим одну формулу с относительной ссылкой и скопируем ее на все строки таблицы. Для этого проделаем следующие шаги:

• Выделим первую ячейку, в столбце “Итог” в которой будет создана наша формула:

• Вставим в ячейку D2 формулу, которая перемножает цену за блюдо и количество: =B2*C2.
• Нажмите клавишу “Enter” на клавиатуре. Формула произведет расчет и его результат вы увидите в ячейке D2.

• Зажав левую клавишу мыши, протяните ячейку D2 за правый нижний угол по всему диапазону ячеек D3:D12. Таким образом, вы скопируете формулу из ячейки D2 и перенесете ее на каждую ячейку диапазона.

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

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

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

Для создания абсолютной ссылки используется знак доллара “$”. С его помощью вы можете зафиксировать от изменений столбец, строку или всех вместе:

• $A$2 – столбец и строка не изменяются при копировании формулы;
• A$2 – при копировании формулы не меняется только строка;
• $A2 – столбец не изменяется при копировании формулы.

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

Как создать и скопировать формулу с абсолютными ссылками

В нашем примере мы будем использовать в ячейке E1 – 18% как значение НДС для расчета налога на товары в колонке D.

Для правильного расчета нам потребуется использовать абсолютную ссылку $E$1 в нашей формуле, так как нам важно, чтобы  стоимость каждого товара перемножалась на ставку НДС, указанную в ячейке E1. Ниже рассмотрим как мы, будем это делать:

• Выделим ячейку, в которую мы хотим вставить формулу для расчета налога. В нашем примере это ячейка D3.

• Напишем формулу, рассчитывающую сумму налога для каждого товара, с учетом его стоимости и количества =(B3*C3)*$E$1.

• Протянем полученную формулу на все ячейки в диапазоне D4:D13.

• Дважды кликните на любой ячейке из диапазона D4:D13 и убедитесь, что формула сработала корректно. Важно убедиться, что вы правильно указали ссылку на ячейку $E$1 в абсолютном формате.

Как создать ссылки на другие листы в Excel

Зачастую, нам в расчетах требуется задействовать данные с разных листов файла Excel. Для этого, при создании ссылки на ячейку из другого листа нужно использовать название листа и восклицательного знака на конце (!). Например, если вы хотите создать ссылку на ячейку A1 на листе Sheet1, то ссылка на эту ячейку будет выглядеть так:

=Sheet1!A1

ВАЖНО! Если в название листа, на ячейку с которого вы ссылаетесь есть пробелы, то название этого листа в ссылке должно быть заключено в кавычки (‘ ‘). Например, если название вашего листа Бюджет Финал, то ссылка на ячейку A1 будет выглядеть так:

‘Бюджет Финал’!A1

На примере ниже, мы хотим добавить в таблицу ссылку на ячейку, в которой уже произведены вычисления между двумя листами Excel файла. Это позволит нам использовать одно и то же значение на двух разных листах без перезаписи формулы или копирования данных между рабочими листами. Для этого проделаем следующие шаги:

• Выберем ячейку, на которую мы хотим сослаться и обратим внимание на название листа. В нашем случае это ячейка E14 на вкладке “Меню”:

• Перейдем на лист и выберем ячейку, в которой мы хотим поставить ссылку. В нашем примере это ячейка B2.

• В ячейке B2 введем формулу, ссылающуюся на ячейку E14 с листа “Меню”: =Меню!E14
• Нажмем клавишу “Enter” на клавиатуре и увидим в ячейке B2 значение ячейки E14 с листа “Меню”.

Если, в дальнейшем, вы переименуете лист, на который вы ссылались, то система автоматически обновит формулу.

Источник: https://excelhack.ru/absolutnie-i-otnositelnie-ssylki-v-excel/

Абсолютные и относительные ссылки в Excel, чем они отличаются, смешанная адресация, как закрепить ссылку на ячейку в Эксель

Каждый человек работающий в Excel встречался с понятием адресаций или использовал ссылки в своих документах. Благодаря своим возможностям табличный процессор Эксель, входящий в состав стандартного пакета MS Office, обеспечивает надежное хранение и быструю обработку больших массивов данных. Частью этого функционала являются ссылки.

Типы адресаций

Ссылки в Excel передают в себе адрес определенных ячеек или связанных диапазонов. На одном рабочем листе одновременно могут находиться более 2 миллиардов ячеек, образующих рабочую область таблицы.

Адрес активной определяют название столбца и строки, в пересечении которых и расположена ячейка. Например, С8. Это означает, что данные находятся в восьмой строке и третьем столбце под названием C.

Ссылка на Range (диапазон) задается через адреса двух его углов (верхний левый и нижний правый) Для примера, B2:C6.

В  Excel есть четыре типа ссылок, каждый из которых предназначен для своих операций — относительные, смешанные, абсолютные и ссылки на отдельные листы.

Относительная адресация

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

Абсолютная адресация

Иногда происходит так, что изменять адрес при растягивании формулы не нужно. Для этого подходят абсолютные ссылки в Excel. С их помощью могут быть реализованы константы и значения, которые не должны изменяться при любых условиях. Создается такая ссылка путем добавления знака американского доллара ($). Его необходимо установить перед адресом, как строки, так и столбца.

Смешанная адресация

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

  Что делать, если лист или книга в Excel защищены паролем – как снять защиту

Использование относительных ссылок

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

1. Кликаем на D2:
2. Для подсчета «Итого» необходимо умножить данные из столбцов B и C, т.е вписываем формулу =B2*C2.
3. Нажимаем Ввод (Enter), после чего формула будет вычислена и результат запишется в D2.
4. Значение из D2 можно растянуть на все строки. Делается это с помощью функции автозаполнения, которая представляет собой квадрат, расположенный справа внизу в выделенной ячейке. «Протягиваем» маркер с помощью мыши только до пятой строки. После нее данных нет, и в последующих ячейках будут нули.

Часто при создании такого рода адресаций появляются ошибки, поскольку пользователи забывают об относительности адреса ячейки. Используя в формуле какую-либо константу, ее просто добавляют без фиксирования символом $.

Это приводит к таким ошибкам как «#ПУСТО!», «#ДЕЛ/0» и другим. Иногда бывает так, что нужно растянуть формулу с зафиксированной строкой или столбцом.

В таких случаях смешанная адресация может сэкономить время.

Использование абсолютных ссылок

С помощью одно простого знака $ ссылка стандартного типа превращается в абсолютную, т.е. ссылка закрепляется за ячейкой. Поставьте доллар перед строкой или столбцом в нужном адресе или выделите его и нажмите клавишу F4 для автоматического проставления знака в формуле. Вышеприведенный пример с использованием абсолютной адресации будет выглядеть следующим образом:

1. Кликаем на D2.
2. Вписываем уже знакомую формулу =B2*C2, выделяем ее и нажимаем F4.
3. Нажимаем Enter и получаем то же самое.
4. Однако теперь протягивая маркер, будет получаться везде одно и то же значение равное 4,00 р., т.е. жестко зафиксированные адреса.

  Работа со сводными таблицами в MS Excel

В этом случае можно создать смешанную ссылку, например =B2*$C$2 будет означать, что С2 будет всегда 4, независимо от того, куда будет перемещена формула, а В2 будет изменяться.

Сквозная ссылка через лист

Использование данных из второго листа возможно благодаря реализованной сквозной адресации.

Для использования значения, расположенного на другом листе, нужно после «=» в поле вписать название листа, восклицательный знак и адрес ячейки, которая содержит это значение.

Например, «=’Лист2’!A1». Можно и просто после «=» перейти на следующий лист, выбрать необходимую ячейку, вернуться обратно и нажать Ввод.

Источник: http://composs.ru/absolyutnuie-i-otnositelnie-ssilki-v-excel/

Типы ссылок на ячейки в формулах Excel

39310 27.10.2012 Скачать пример

Если вы работаете в Excel не второй день, то, наверняка уже встречали или использовали в формулах и функциях Excel ссылки со знаком доллара, например $D$2 или F$3 и т.п. Давайте уже, наконец, разберемся что именно они означают, как работают и где могут пригодиться в ваших файлах.

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

Это обычные ссылки в виде буква столбца-номер строки ( А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»))

Ссылки по теме

Источник: https://www.planetaexcel.ru/techniques/2/83/

Шаг 56.
Абсолютные и относительные

ссылки

   
На этом шаге мы рассмотрим абсолютные и относительные ссылки.

   
По умолчанию Excel создает в формулах относительные ссылки. На рисунке 1 показана рабочая таблица с формулой, введенной в ячейку D2. Эта
формула, в которой по умолчанию используются относительные ссылки, выглядит
следующим образом: = В2 * С2.

Рис. 1. Пример рабочей таблицы с формулой, введенной в ячейку D2

   
Если скопировать эту формулу в две ячейки, расположенные под ячейкой D2, то Excel вставит копии приведенной формулы, при этом видоизменит их следующим образом:

• В ячейку D3 будет скопирована формула = В3 * СЗ;
• В ячейку D4 будет скопирована формула = В4 * С4.

   
Другими словами, Excel изменяет ссылки на ячейки в соответствии с новым положением формулы.

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

   
Однако бываю случаи, когда необходимо, чтобы адреса ячеек копировались без изменений. Для этого используют абсолютные ссылки. Пример формулы, содержащей абсолютную ссылке, показан на рисунке 2.

Рис. 2. Формула, в которой используется абсолютная ссылка на ячейку B6

   
В данном примере в ячейке В6 содержится значение ставки налога на продажу. Формула в ячейке D2 выглядит следующим образом: = В2 * С2 * $В$б

   
В ссылке на ячейку В6 перед буквой столбца и перед номером строки стоят знаки доллара. Это означает, что данная ссылка является абсолютной. При копировании этой формулы в две ячейки снизу будут получены следующие формулы.

• В ячейку D3 будет скопирована формула = B3 * C3 * $B$6;
• В ячейку D4 будет скопирована формула = B4 * C4 * $B$6.

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

   
В абсолютной ссылке используется два знака доллара: один — перед именем столбца, а другой —перед номером строки. Однако в Excel существуют также смешанные ссылки, в которых только одна часть адреса (относящаяся к строке или ко столбцу) является абсолютной. Приведем все возможные типы ссылок на ячейки:

• A1 — относительная ссылка;
• $A$1 — абсолютная ссылка;
• $A1 — смешанная ссылка (абсолютной является координата столбца);
• A$1 — смешанная ссылка (абсолютной является координата строки).

   
На рисунке 3 показана таблица, в каждой ячейке которой должно находиться значение, взятое из столбца А, умноженное на значение из строки 1. Формула в ячейке В2 выглядит следующим образом: = B$1 * $A2.

Рис. 3. Пример использования смешанных ссылок на ячейки

   
Приведенная формула содержит две смешанные ссылки на ячейки. В ссылке B$1 координата строки является абсолютной, тогда как координата столбца — относительной.

А в ссылке $А2, наоборот, координата строки является относительной, тогда как координата столбца — абсолютной. Данную формулу можно скопировать в диапазон В2 : Е5, и в каждой ячейке будут содержаться правильные формулы.

Например, формула в ячейке Е5 будет выглядеть следующим образом: = Е$1 * $А5.

   
Неотносительные (т.е. абсолютные или смешанные) ссылки можно ввести вручную, вставив в нужных местах знаки доллара. Можно также воспользоваться удобным клавиатурным эквивалентом — клавишей F4. При вводе ссылки на ячейку — либо вручную, либо путем, указания — нужно нажать клавишу F4 несколько раз, чтобы программа «прокрутила» по циклу все четыре типа ссылок.

   
Например, если в начале формулы ввести = A1, то первое нажатие клавиши F4 преобразует ссылку на ячейку в $А$1, второе — в А$1, третье — в $А1, а четвертое вернет ей первоначальный вид — А1. Нажимать клавишу F4 необходимо до тех пор, пока не появится нужный тип ссылки.

•    
  На следующем шаге мы рассмотрим организацию ссылок на ячейки за пределами текущего рабочего листа.
• Предыдущий шаг
  Содержание
  Следующий шаг

Источник: http://it.kgsu.ru/MSExcel/excel056.html

InformatikUrok » Blog Archive » Как создать абсолютную и смешанную ссылку?

Помните, на прошлом занятии мы рассматривали, что во время копирования формул, адреса в формуле смещаются.

Чаще всего именно это нам и нужно, но бывают ситуации, когда адрес ячейки нужно «зафиксировать», чтобы он не изменялся при копировании формул. Такое фиксирование можно сделать с помощью знака $.

Рассмотрим пример и разберемся, что такое абсолютная ссылка и как ее создать. Кстати, по ходу дела затронем вопрос и о смешанных и относительных ссылках.

Давайте создадим таблицу, в которой отразим перевод денежных сумм, указанных в американской валюте, в украинские гривны за период с 1 октября по 10 октября 2013г. Обменный курс введем в отдельной ячейке. Для расчета украинской гривны введем формулу в ячейку С2=В2*D2 и скопируем эту формулу с помощью маркера автозаполнения в диапазон С3:С11. Вот, что у нас получилось:

Получилось, что результат мы получили только в ячейке С2, а в остальных ячейках нули, т.к. при копировании формулы =В2*D2, адреса ячеек сместились: в ячейке С3формула стала =В3*D3, в ячейке С4 формула =В4*D4 и т.д.

Значения в ячейках  диапазона В3:В11 — числа, а вот в ячейках D3:D11 – пусто, поэтому Excel автоматически в формулу подставил нули.

Чтобы при копировании формул ячейку D2 зафиксировать, нужно поставить знак доллара и записать адрес $D$2.

Исправим формулу и посмотрим, что получиться. Кстати, забегая, вперед, скажу, что ссылка $D$2 называется абсолютной.

Как создать абсолютную ссылку? Знак доллара можно ввести с клавиатуры, нажав одновременно Shift и цифра 4,  а можно исправить готовую ссылку, выделив адрес D2 и нажать функциональную клавишу F4.

После копирования формулы $D$2 в диапазоне D3:D11 появились числа, т.к. знак $ зафиксировал от изменения столбец D и строку 2. Такие ссылки, в которых зафиксированы и столбец и строка, называются абсолютными.

Можно было в нашем примере использовать формулу D$2. Ссылка  D$2 – фиксирует только строку 2. Такое фиксирование применяют, если формула копируется по вертикали. Кстати, самостоятельно можете проверить, что эта формула тоже  работает для нашего примера.

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

Кстати, если несколько раз нажать клавишу F4, когда выделен адрес, то мы получим смешанные ссылки: D$2, $D2.

Правила использования смешанных ссылок:

• Ссылка D$2 не изменяется, если копировать по вертикали формулу.
• Ссылка $D2 не изменяется, если копировать по горизонтали формулу.

Ссылки, которые изменяются в обоих направлениях, называются относительными.

В нашей формуле мы тоже использовали относительную ссылку В2, которая потом изменилась на В3 и т.д.

Итак, сегодня мы узнали,

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

Источник: http://InformatikUrok.ru/ms-excel/kak-sozdat-absolyutnuyu-i-smeshannuyu-ssyilku

Ссылки в Excel

Когда пользуешься различными формулами Excel, то приходится обращать внимание на ссылки. Ссылки в Excel, как известно, бывают разными: относительными, абсолютными, внешними, в виде имен диапазонов и др. 

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

при работе с большими массивами данных формула прописывается обычно один раз, а затем «протягивается» (копируется) на другие ячейки (например, в итоговой строке или итоговом столбце таблицы). Корректность такого «протягивания» сильно зависит от типа ссылки. Здесь есть два варианта.

Недооценка важности вида ссылки приводит к ошибкам в расчетах и необходимости коррекции формул. Все это как минимум тормозит работу. Помните, что каждое лишнее действие вносит свой вклад в бесполезно потраченное время.

Рассмотрим простой пример. Нужно сложить два числа. Сделать это легко, прописав в свободной ячейке (например, внизу) знак «=» и затем через знак «+» сослаться на складываемые ячейки. Если чисел много, то суммировать лучше через функцию СУММ, указав сразу весь диапазон суммирования.

Как несложно заметить, вместо чисел мы делаем ссылки на складываемые ячейки или сразу на целый диапазон. Ссылки вместо чисел – это основное отличие Excel от калькулятора. Но чтобы они работали правильно, неплохо бы различать абсолютные и относительные ссылки.

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

Возьмем чуть более сложный пример, когда нужно вычислить сумму для нескольких столбцов или, как иногда говорят, подбить итоги. Чаще всего формулу  вводят в одну ячейку и затем «протягивают» в нужном направлении.

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

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

Если вниз или вверх, то же самое произойдет с диапазоном вычисления. По умолчанию ссылки в рамках одной книги Excel делаются относительными и имеют следующий вид:

• =A1,
• где буква обозначает столбец, а число – строку.
• Однако бывают ситуации, когда ссылка должна оставаться на месте, независимо от того, куда копируется формула.

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

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

Теперь попробуем «протянуть» формулу вниз для расчета остальных долей. Сумма долей должна получиться ровно 100%. Однако уже на втором значении видно: что-то пошло не так.

Числитель «поехал» вниз правильно, а вот знаменатель должен был остаться на месте. Чтобы все получилось правильно, нужно зафиксировать ссылку на итог, чтобы она не «ездила» вслед за формулой. Короче говоря, ссылку на итоговую сумму нужно сделать абсолютной, для чего перед названием строки и столбца ставится значок доллара $ ( в английской раскладке):

Таким же образом можно «закрепить» ссылку на валютный курс, пошлину, налог и прочее, что находится в одном месте для многих ссылающихся ячеек.

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

Помимо абсолютных и относительных ссылок встречаются еще и т.н. смешанные ссылки, когда фиксируется только строка или столбец:

=A$1

=$A1

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

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

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

Как быстро установить символ доллара $

Если ставить значок доллара с клавиатуры классическим методом, то потребуется вначале перейти в английскую раскладку, а затем нажать Shift+4. Скажем прямо, это долго и неудобно.

Гораздо быстрее войти в режим редактирования формул с помощью клавиши F2 (если вы набираете формулу вручную, то уже находитесь в этом режиме), установить курсор на нужной ссылке и нажать F4. Ссылка начнет менять свой режим «абсолютности».

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

Очень удобно, не нужно переключать раскладку и искать $. Эта же кнопка работает и в диалоговом окне «Вставки функции». Например, при написании формулы ВПР или СУММЕСЛИ нужно внимательно смотреть на ссылающиеся диапазоны, поэтому значки $ используются очень часто.

Как я уже говорил выше, если диапазон, на который нужно сослаться находится в той же книге (файле Excel), пусть даже и на другом листе, то Excel по умолчанию устанавливает относительные ссылки (без значка «$»). А вот ссылки на другие книги Excel по умолчанию делает абсолютными (вставляет «$» где только можно).

Но про другие листы и книги поговорим в следующем параграфе.

Ссылки на другие листы и книги

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

Во-первых, при открытии файла с внешними связями Excel назойливо напоминает об этом выскакивающим посреди экрана окошком. Это, конечно, помогает не забывать, что в файле есть внешние ссылки, но лучше, чтобы их вообще не было.

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

В общем, сразу после вычислений с использованием данных из других книг внешние ссылки рекомендуется удалить: Данные → Подключения → Изменить связи → Разорвать связь.

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

Стиль ссылок

И еще кое-что про ссылки. Иногда они выглядят несколько непривычно. Это особый стиль ссылок, при котором адрес ячейки A1 имеет наименование R1C1 (первая строка (R1), первая колонка (C1)).

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

Для этого нужно выполнить следующие действия: Файл → Параметры → Формулы и далее нужно снять галочку с пункта Стиль ссылки R1C1.

Всего доброго и до новых встреч.

Поделиться в социальных сетях:

Источник: https://statanaliz.info/excel/funktsii-i-formuly/ssylki-v-excel/