какие типы ссылок на ячейки в ms excel бывают выделите все
Типы ссылок на ячейки в формулах Excel
Если вы работаете в Excel не второй день, то, наверняка уже встречали или использовали в формулах и функциях Excel ссылки со знаком доллара, например $D$2 или F$3 и т.п. Давайте уже, наконец, разберемся что именно они означают, как работают и где могут пригодиться в ваших файлах.
Относительные ссылки
Это обычные ссылки в виде буква столбца-номер строки ( А1, С5, т.е. «морской бой»), встречающиеся в большинстве файлов Excel. Их особенность в том, что они смещаются при копировании формул. Т.е. C5, например, превращается в С6, С7 и т.д. при копировании вниз или в D5, E5 и т.д. при копировании вправо и т.д. В большинстве случаев это нормально и не создает проблем:
Смешанные ссылки
Абсолютные ссылки
Все просто и понятно. Но есть одно «но».
Действительно абсолютные ссылки
Если ввести в ячейку формулу:
=ДВССЫЛ(«C5»)
=INDIRECT(«C5»)
то она всегда будет указывать на ячейку с адресом C5 вне зависимости от любых дальнейших действий пользователя, вставки или удаления строк и т.д. Единственная небольшая сложность состоит в том, что если целевая ячейка пустая, то ДВССЫЛ выводит 0, что не всегда удобно. Однако, это можно легко обойти, используя чуть более сложную конструкцию с проверкой через функцию ЕПУСТО:
=ЕСЛИ(ЕПУСТО(ДВССЫЛ(«C5″));»»;ДВССЫЛ(«C5»))
=IF(ISBLANK(INDIRECT(«C5″));»»;INDIRECT(«C5»))
Какие бывают ссылки в Excel
Здравствуйте, дорогие читатели. Я решил выделить эту тему в отдельный пост, потому что многие не знают какие бывают ссылки в Эксель и не представляют, чем они отличаются друг от друга. Ежедневно я получаю вопросы от читателей, которые неправильно применяют ссылки на ячейки. Из-за этого, использование формул может давать ошибочные результаты. Уделите этому тексту 5 минут, чтобы потом не было неожиданных результатов вычислений.
Ссылки в Эксель
Давайте разберемся какие типы ссылок бывают в программе. Сразу уточню, для записи в одной ячейке, тип используемой ссылки не имеет значения. Но если вы будете копировать формулу в другое место, использование разных ссылок даёт разные результаты. Так вот, ссылки на ячейки бывают трёх видов:
Относительные ссылки в Excel удобно использовать, когда нужно применить одну и ту же формулу к списку данных. Тогда при копировании формула берет исходные данные из нужного пункта списка автоматически.
Как видите, в относительных ссылках не ставятся знаки «$», и программа не «замораживает» ни одну из координат.
Я в практике одинаково часто использую все три типа ссылок, поскольку они позволяют решать разные задачи при копировании формул. При этом, все они используются постоянно.
Как изменить тип ссылки в Эксель
Изменение типа ссылки – очень простая задача, которую можно выполнить двумя путями. Как вы уже поняли, чтобы координаты не меняли своего значения при копировании, нужно устанавливать перед ними знак «$». И делается это так:
Внешние ссылки в Эксель
Ссылки могут указывать на ячейки на том же листе, на другом листе той же книги, на листе другой книги. Эти ссылки имеют свои особенности:
Как видите, ссылки на другие ячейки могут выглядеть достаточно сложно. Не беспокойтесь, вам не придётся набирать их вручную, программа всё сделает сама, вам нужно лишь кликнуть на ячейку, чтобы сослаться на нее. Подробно об этом я рассказал в статье о правилах написания формул.
Надеюсь, вы хорошо усвоили тему этой статьи. В ней не было ничего сложного, но чёткое понимание работы ссылок – залог правильной работы формул. Если у вас есть вопросы по теме поста – пишите в комментариях!
Спасибо за прочтение, до встречи на страницах блога OfficeЛЕГКО!
Добавить комментарий Отменить ответ
2 комментариев
Пожалуй, статья не является полной (по крайней мере, на свой вопрос я в ней ответ не нашёл).
При пользовании EXCEL встретился термин «ПРОСТАЯ ССЫЛКА»! Не пойму, что он означает.
В частности, указание на необходимость /возможность использования «простой ссылки» даётся при неправильном (по меркам EXCEL, естественно) написании ОГРАНИЧЕНИЙ, используемых при применении опции «ПОИСК РЕШЕНИЯ» (буквально появляется следующее сообщение: «Ограничение должно быть числом, ПРОСТОЙ ССЫЛКОЙ или формулой с числовыми значениями»).
Исходя из специфики решаемой задачи в данном случае интерес может представлять только ПРОСТАЯ ССЫЛКА.
Буду признателен автору, если он пояснит значение этого термина (разумеется, если знает это).
Заранее спасибо. С уважением, Виктор (Московская область)
Виктор, в данном случае программа просит указать ссылку на ячейку из данной книги, в которой содержится число или результат вычисления. Не используйте в поле гиперссылки, а также вычисления с указанием ссылок на другие ячейки. Указывайте целевое значение значение напрямую, или ссылку на ячейку с этим значением, или формулу, которая не содержит ссылок на ячейки, только значения и операторы.
Microsoft Excel/Ссылки
Содержание
Общие сведения [ править ]
Ссылка в Microsoft Excel — адрес ячейки или связного диапазона ячеек. В каждом листе Excel может быть 256 (2 в 8 степени) столбцов и 65536 (2 в 16 степени) строк (В Excel 2007 16384 (2 в 14 степени) столбцов и 1048576 (2 в 20 степени) строк). Адрес ячейки определяется пересечением столбца и строки, как в шахматах или морском бое, например: A1, C16. Адрес диапазона ячеек задается адресом верхней левой ячейки и нижней правой, например: A1:C5.
Виды представления ссылок [ править ]
Есть два вида представления ссылок в Microsoft Excel:
Включить стиль ссылок 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». Квадратных скобок нет, то есть указано не относительно смещение по отношению к положению формула, а абсолютное смещение по отношению к всему листу (вспомните абсолютную и относительную системы координат).
Типы ссылок (типы адресации) [ править ]
Ссылки в Excel бывают 3-х типов:
Если вы ставите в какой-то ячейке знак «=», затем щелкаете левой кнопкой мыши на какой-то ячейке, Excel подставляет после «=» относительную ссылку на эту ячейку. Эта ссылка «запоминает», на каком расстоянии (в строках и столбцах) Вы щелкнули ОТНОСИТЕЛЬНО положения ячейки, где поставили «=» (смещение в строках и столбцах). Например, вы щелкнули на ячейку 3-мя столбцами левее и на 2 строки выше. Если после нажатия Enter потянуть вниз за маркер автозаполнения, эта формула скопируется во все ячейки, через которые мы протянули. И в каждой ячейке эта ссылка будет указывать на ячейку, расположенную на 3 столбца влево и 2 строки вверх ОТНОСИТЕЛЬНО положения ссылки. Это можно проверить, дважды щелкнув на одной из скопированных формул, или выделив ее, и нажав F2. Для лучшего понимания вспомните, как ходит шахматный конь. Он ходит буквой «Г» и из центра доски бьёт 8 клеток. «Упростим» немного правило хода коня: представим, что он может ходить только одной буквой «Г» — 2 клетки вперед и одну вправо. На какую бы клетку доски мы не поставим коня, каждый раз он ОТНОСИТЕЛЬНО своего положения отсчитывает смещение в строках и столбцах — 2 строки вверх и один столбец влево. Точно таким же образом работают относительные ссылки, только правило их «хода» задает пользователь. Каждый раз, когда мы тянем за маркер автозаполнения формула, содержащую относительные ссылки, Excel пересчитывает адреса всех относительных ссылок в ней в соответствии с их «правилом хода» (у каждой относительной ссылки в формуле может быть свое «правило»).
Как было сказано выше, если потянуть за маркер автозаполнения формулу, содержащую относительные ссылки, Excel пересчитает их адреса. Если же в формуле присутствуют абсолютные ссылки, их адрес останется неизменным. Проще говоря — абсолютная ссылка всегда указывают на одну и ту же ячейку.
Смешанные ссылки являются наполовину абсолютными и наполовину относительными. Знак доллара в них стоит или перед буквой столбца или перед номером строки. Это самый сложный для понимания тип ссылки. Например, в ячейке записана формула «=A$1». Ссылка A$1 относительная по столбцу A и абсолютная по строке 1. Если мы потянем за маркер автозаполнения эту формулу вниз или вверх, то ссылки во всех скопированных формулах будут указывать на ячейку A1, то есть будет вести себя как абсолютные. Однако, если потянем вправо или влево — ссылки будет вести себя как относительные, то есть Excel будет пересчитывать ее адрес. Таким образом, формулы, созданные автозаполнением, будут использовать один и тот же номер строки ($1), но изменится номер столбца (A, B, C. ).
Именованные ячейки [ править ]
Для упрощения работы с ячейками или диапазоном ячеек можно дать ей/им имя, и затем обращаться к ячейке или диапазону не по его адресу, а по символьному имени. Именованные ячейки можно использовать везде, где можно использовать то значение, на которое указывает ссылка.
Примечание! Именованные ячейки по умолчанию являются абсолютными ссылками.
Для создания именованной ячейки нужно выделить нужную ячейку или диапазон, затем щелкнуть в текстовое поле (см. рис. ниже) ввести свое имя и нажать Enter. Можно использовать русские имена. Имя не может содержать пробелов и начинаться с цифры. Именованная ячейка может ссылаться на несвязный диапазон ячеек (выделенный с «Ctrl»).
Для вставки именованной ячейки можно воспользоваться кнопкой со стрелкой вниз:
или нажать клавишу «F3», откроется следующее окно:
Пример использования: «=СУММ(tablica_1);»
Ссылки в Excel
Когда пользуешься различными формулами Excel, то приходится обращать внимание на ссылки. Ссылки в Excel, как известно, бывают разными: относительными, абсолютными, внешними, в виде имен диапазонов и др.
Первым делом уясним, что такое относительные и абсолютные ссылки и зачем конструкторы Excel их придумали. Вопрос не праздный т.к. при работе с большими массивами данных формула прописывается обычно один раз, а затем «протягивается» (копируется) на другие ячейки (например, в итоговой строке или итоговом столбце таблицы). Корректность такого «протягивания» сильно зависит от типа ссылки. Здесь есть два варианта. Первый: диапазон, на который ведет ссылка, «перемещается» вслед за копируемой ячейкой, то есть остается на одном месте относительно ячейки с формулой. Второй: ссылка ведет на один и тот же адрес независимо от того, куда копируется формула. Недооценка важности вида ссылки приводит к ошибкам в расчетах и необходимости коррекции формул. Все это как минимум тормозит работу. Помните, что каждое лишнее действие вносит свой вклад в бесполезно потраченное время.
Рассмотрим простой пример. Нужно сложить два числа. Сделать это легко, прописав в свободной ячейке (например, внизу) знак «=» и затем через знак «+» сослаться на складываемые ячейки. Если чисел много, то суммировать лучше через функцию СУММ, указав сразу весь диапазон суммирования.
Как несложно заметить, вместо чисел мы делаем ссылки на складываемые ячейки или сразу на целый диапазон. Ссылки вместо чисел – это основное отличие Excel от калькулятора. Но чтобы они работали правильно, неплохо бы различать абсолютные и относительные ссылки.
Относительные ссылки Excel
Возьмем чуть более сложный пример, когда нужно вычислить сумму для нескольких столбцов или, как иногда говорят, подбить итоги. Чаще всего формулу вводят в одну ячейку и затем «протягивают» в нужном направлении.
Как видим, при копировании формулы диапазон суммирования автоматически перемещается вслед за формулой, позволяя за минимум действий рассчитать итоговую строку. Подобный расчет возможен благодаря относительности ссылок, которые как бы привязаны к ячейке с формулой, а не к адресу, на который указывают. Если формула копируется вправо, то и вычисляемый диапазон смещается туда же. Если вниз или вверх, то же самое произойдет с диапазоном вычисления. По умолчанию ссылки в рамках одной книги Excel делаются относительными и имеют следующий вид:
где буква обозначает столбец, а число – строку.
Однако бывают ситуации, когда ссылка должна оставаться на месте, независимо от того, куда копируется формула.
Абсолютные ссылки
Рассмотрим другой пример, когда нужно рассчитать долю каждого значения к итогу. Введем формулу для расчета доли первого числа.
Теперь попробуем «протянуть» формулу вниз для расчета остальных долей. Сумма долей должна получиться ровно 100%. Однако уже на втором значении видно: что-то пошло не так.
Таким же образом можно «закрепить» ссылку на валютный курс, пошлину, налог и прочее, что находится в одном месте для многих ссылающихся ячеек.
Смешанные ссылки
Помимо абсолютных и относительных ссылок встречаются еще и т.н. смешанные ссылки, когда фиксируется только строка или столбец:
Закрепленная строка никак не влияет на абсолютность столбца, а абсолютный столбец не зависит от относительности строки. Для выбора правильного варианта нужно знать, куда копируется ячейка с формулой и не будет никаких проблем. Строго говоря, для расчета доли в примере выше ссылку на итог можно было сделать смешанной, т.к. фиксация была необходима только по строке. Но если формула копируется и по вертикали, и по горизонтали, тогда уж точно не обойтись без полной абсолютной ссылки.
» src=»https://sp-ao.shortpixel.ai/client/to_webp,q_glossy,ret_img,w_204,h_77/https://statanaliz.info/wp-content/plugins/a3-lazy-load/assets/images/lazy_placeholder.gif» data-lazy-type=»image» data-src=»https://sp-ao.shortpixel.ai/client/to_webp,q_glossy,ret_img,w_204,h_77/https://statanaliz.info/wp-content/uploads/2015/01/ssylki-06_F4.gif» alt=»Последовательное нажатия » width=»204″ height=»77″ />
Ссылки на другие листы и книги
Excel умеет ссылаться не только в пределах одного листа, но и на другие листы и даже другие файлы Excel. Такой функционал значительно расширяет диапазон вычислений. Если в формулах есть ссылки на другие файлы, то следует обратить внимание на следующее. Во-первых, при открытии файла с внешними связями Excel назойливо напоминает об этом выскакивающим посреди экрана окошком. Это, конечно, помогает не забывать, что в файле есть внешние ссылки, но лучше, чтобы их вообще не было. Во-вторых, при изменении или удалении внешнего источника мгновенно меняются и результаты вычислений, что нужно далеко не всегда. В третьих, если возникнут ошибки в расчетах, то на обнаружение их источника может потребоваться дополнительное время, т.к. данные находятся в другом файле. В общем, сразу после вычислений с использованием данных из других книг внешние ссылки рекомендуется удалить: Данные → Подключения → Изменить связи → Разорвать связь.
Все внутренние формулы останутся неизменными, а формулы с внешними ссылками превратятся в значения. Убрать ссылки Excel можно и по-другому. Для этих же целей можно использовать более топорный метод. Нужно просто скопировать диапазон с формулами и тупо вставить его сверху в виде значений (через специальную вставку). Правда, в значения превратятся уже все формулы, а не только внешние.
Стиль ссылок
И еще кое-что про ссылки. Иногда они выглядят несколько непривычно. Это особый стиль ссылок, при котором адрес ячейки A1 имеет наименование R1C1 (первая строка (R1), первая колонка (C1)). Такой стиль ссылок иногда может быть полезен, но довольно редко. Поэтому неплохо уметь возвращать нормальный вид адреса ячейки. Для этого нужно выполнить следующие действия: Файл → Параметры → Формулы и далее нужно снять галочку с пункта Стиль ссылки R1C1.
Основные типы ссылок на ячейки в Excel
При работе в Excel обходиться без использования ссылок практически невозможно.
Рассмотрим на практике использование основных типов ссылок на ячейки в Excel.
Относительная ссылка в Excel
Относительная ссылка — это ссылка вида A1 (т.е. буква столбца + номер строки).
Основная особенность таких ссылок — при протягивании или копировании формулы в другие ячейки ссылка смещается.
Другими словами, при копировании вниз ссылка A1 превратится в A2, A3 и т.д., при копировании вправо — в B1, C1 и т.д.:
Смешанная ссылка в Excel
Смешанная ссылка — это ссылка вида $A1 или A$1.
Знак доллара ($) служит фиксированием столбца или строки.
Иными словами, если мы поставим $ перед буквой столбца (например, $B5), то ссылка не будет изменяться по столбцам, но будет изменяться по строкам (при протягивании формула сместится на $B5, $B6, $B7 и т.д.). Аналогично, если знак $ поставить перед номером строки (например, B$5), то ссылка не будет изменяться по строкам, но будет изменяться по столбцам (при перемещении формула сдвинется на C$5, D$5, E$5 и т.д.).
Разберем использование смешанных ссылок на построении стандартной таблицы умножения:
В данном примере любая формула таблицы является произведением значений из столбца A и строки 2.
Добавляя в формулу расчета знак $ (например, G$2*$A8) мы последовательно фиксируем столбец и строку.
Абсолютная ссылка в Excel
Абсолютная ссылка — это ссылка вида $A$1.
Её особенность в том, что она не изменяется при копировании или протягивании формулы в другие ячейки.
В данном случае знак $ ставится как перед буквой столбца, так и перед номером строки, т.е. формула полностью фиксируется.
Абсолютная ссылка часто применяется, когда необходимо умножить или разделить диапазон ячеек на одно и тоже число.
Например, перевести данные в рубли по определенному курсу, или перевести данные в тысячи/миллионы/миллиарды:
