Как отключить структурированные ссылки в excel
Обновлено: 05.07.2024
Этот вопрос я слышу почти на каждом тренинге, да и на нашем форуме он всплывает с завидной периодичностью. Давайте, наконец, разберемся - что же это за хитрый режим ссылок, как с ним бороться и для чего он, собственно говоря, вообще предназначен.
Что это
Классическая и всем известная система адресации к ячейкам листа в Excel представляет собой сочетание буквы столбца и номера строки - морской бой или шахматы используют ту же идею для обозначения клеток доски. Третья сверху во втором столбце ячейка, например, будет иметь адрес B3. Иногда такой стиль ссылок еще называют "стилем А1". В формулах адреса могут использоваться с разным типом ссылок: относительными (просто B3), абсолютными ($B$3) и смешанного закрепления ($B3 или B$3). Если с долларами в формулах не очень понятно, то очень советую почитать тут про разные типы ссылок, прежде чем продолжать.
Однако же, существует еще и альтернативная малоизвестная система адресации, называемая "стилем R1C1". В этой системе и строки и столбцы обозначаются цифрами. Адрес ячейки B3 в такой системе будет выглядеть как R3 C2 (R=row=строка, C=column=столбец). Относительные, абсолютные и смешанные ссылки в такой системе можно реализовать при помощи конструкций типа:
- R C - относительная ссылка на текущую ячейку
- R2 C2 - то же самое, что $B$2 (абсолютная ссылка)
- R C5 - ссылка на ячейку из пятого столбца в текущей строке
- R C[-1] - ссылка на ячейку из предыдущего столбца в текущей строке
- R C[2] - ссылка на ячейку, отстоящую на два столбца правее в той же строке
- R[2] C[-3] - ссылка на ячейку, отстоящую на две строки ниже и на три столбца левее от текущей ячейки
- R5 C[-2] - ссылка на ячейку из пятой строки, отстоящую на два столбца левее текущей ячейки
- и т.д.
Ничего суперсложного, просто слегка необычно.
Как это включить/отключить
Мало кто использует этот режим осознанно. Обычно он случайно включается сам, например, при открытии кривых выгрузок из 1С в Excel и в некоторых других ситуациях. Отключить его совсем несложно. Самый простой путь:
В Excel 2007/2010: кнопка Офис (Файл) - Параметры Excel - Формулы - Стиль ссылок R1C1 (File - Excel Options - Formulas - R1C1-style)
В Excel 2003 и старше: Сервис - Параметры - Общие - Стиль ссылок R1C1 (Tools - Options - General - R1C1-style)
Если вам приходится делать это часто, то имеет смысл создать простой макрос, переключающий эти два режима туда-обратно:
Можно сохранить его в личную книгу макросов и повесить на кнопку на панели инструментов или на сочетание клавиш (как это сделать описано тут).
Где это может быть полезно
А вот это правильный вопрос. Если звезды зажигают, то это кому-нибудь нужно. Есть несколько ситуаций, когда режим ссылок R1C1 удобнее, чем классический режим А1:
-
При проверке формул и поиске ошибок в таблицах иногда гораздо удобнее использовать режим ссылок R1C1, потому что в нем однотипные формулы выглядят не просто похоже, а абсолютно одинаково. Сравните, например, одну и ту же таблицу в режиме отладки формул (CTRL+
Найти ошибку в режиме R1C1 намного проще, правда?
- Если большая таблица с данными на вашем листе начинает занимать уже по нескольку сотен строк по ширине и высоте, то толку от адреса ячейки типа BT235 в формуле немного. Видеть номер столбца в такой ситуации может быть гораздо полезнее, чем его же буквы.
- Некоторые функции Excel, например ДВССЫЛ (INDIRECT) могут работать в двух режимах - A1 или R1C1. И иногда оказывается удобнее использовать второй.
- В коде макросов на VBA часто гораздо проще использовать стиль R1C1 для ввода формул в ячейки, чем классический A1. Так, например, если нам надо сложить два столбца чисел по десять ячеек в каждом (A1:A10 и B1:B10,) то мы могли бы использовать в макросе простой код:
т.к. в режиме R1C1 все формулы будут одинаковые. В классическом же представлении в ячейках столбца С все формулы разные, и нам пришлось бы писать код циклического прохода по каждой ячейке, чтобы определить для нее формулу персонально, т.е. что-то типа:
Ссылки по теме
Спасибо за разъяснение. Всегда больше всего интересовал вопрос "почему". На мелких таблицах RC режим бесил, на крупных стал более понятен, но все на уровне интуиции. Для такого можно использовать функцию СМЕЩ (OFFSET) - она выдает ссылку на диапазон, сдвинутый относительно исходного на определенное число строк и столбцов.Например =СМЕЩ(A1;3;2) даст ссылку на B3
Ещё можно присвоить нужной ячейке имя (выделить её, далее Меню-Вставка-Имя-Присвоить), например, Мой_рост, а потом использовать его в конструкции (=. +Мой_рост/. ). К моменту обращения к этой ячейке (т.е. до начала расчета) там должно находиться нужное/конкретное значение. Именованную ячейку можно "перетаскивать" в любое другое место, даже на другой лист, и там она будет с именем "Мой_рост".
Кстати, вышесказанное работает в любом стиле - "R1C1" и "A1".
Для именно программирования он, кстати, однозначно удобнее, абсолютно согласен.
Недавно открыл для себя ещё одну мега удобную вещь,которую можно сделать только с этим стилем. На сайте вроде не встречал, так что поделюсь с читателями, может кому пригодится.
Если в формуле хочется использовать именованные диапазоны (RangeNames), то есть вместо формулы B3*B4 иметь что-то вроде Объем * Цена, то обычно выделяют всю строку(столбец) с исходными данными, то есть получается массив (или вектор - назовите как хотите), например "Выручка" - В3:X3, "Цена" - B4:X4. Тогда, конечно, можно вводить эти имена в формулу, и формула примет более благообразный и понятный вид, но есть несколько но
- при использовании некоторых формул, например МАКС, Excel будет использовать весь массив в качестве аргумента, то есть если у вас две цены Цена1; Цена 2, то с формулой Макс (Цена1, Цена2) посчитается не максимальная цена из двух цен в текущем столбце, а максимальная цена за весь период.
- Если зайти в режим редактирования - то будет подсвечиваться весь диапазон - тоже не всегда удобно и понятно, откуда берутся значение для формулы
Но можно задать не статичный диапазон, а смещающийся. Надо зайти в режим R1C1 и при создании диапазона не фиксировать столбец, то есть вместо R3C2:R3C24 вписать R3C. Тогда в любой ячейке Excel при использовании этого диапазона будет использоваться значение из третьего ряда и текущего столбца, причём даже в режиме ссылок A1. И, что самое интересное, в режиме А1 адрес диапазона будет меняться, в зависимости от того, в какой ячейке вы находитесь. По мне, так это какая-то магия, если честно. Таки образом решаются вышеупомянутые проблемы.
Надеюсь, понятно объяснил. Пока писал, понял, что можно подобную вещь и без этого стиля ссылок сделать, но всё равно не так красиво получится.
Работа со структурированными ссылками поддерживается только в таблицах, к которым применен табличный стиль форматирования в программе Excel (так называемых «умных» таблицах ).
«Умные» таблицы имеют имя (по умолчанию – Таблица1 , Таблица2 и т.д.) и состоят их следующих областей:
Формулы со структурированными ссылками оперируют не адресами ячеек и диапазонов ячеек, а именами таблицы и столбцов таблицы, а также названиями областей таблицы.
Ниже приведены примеры обычных и структурированных ссылок.
Ссылка на диапазон ячеек столбца Продажи
таблицы Таблица1
Ссылка на область данных таблицы Таблица2
Структурированные ссылки могут использоваться как в таблице, так и за ее пределами.
Если формула со структурированной ссылкой расположена непосредственно в таблице, то указывать название таблицы в данной ссылке не нужно. Если имя таблицы по какой-либо причине указывается в ссылке, то оно автоматически удаляется при вводе формулы. Например, формула =СУММ(Таблица1[Продажи]) будет автоматически исправлена на =СУММ([Продажи]) .
Если формула со структурированной ссылкой расположена за пределами таблицы, то указание имени таблицы является обязательным условием.
Чтобы узнать имя таблицы, выделите в ней любую ячейку. Имя таблицы отобразится на панели инструментов, в разделе Таблица .
Чтобы скопировать имя таблицы, выделите его щелчком мыши.
Структурированные ссылки могут обращаться к следующим данным таблицы:
Вся таблица, включая строку заголовка и строку итогов (при их наличии)
Только строка заголовка
Только строки данных
Только строка итогов
Ячейка на пересечении текущей строки и указанного столбца (неявное пересечение)
Ниже приведены примеры структурированных ссылок.
Столбцы с N по M
Ввод формул, которые содержат структурированные ссылки, осуществляется стандартными способами.
Чтобы указать в формуле название «умной» таблицы, выполните следующие действия:
1. Начните вводить название таблицы. В выпадающем списке отобразятся названия всех «умных» таблиц, которые содержатся в документе.
2. Выберите название требуемой таблицы с помощью мыши или клавиш клавиатуры:
Название таблицы подставится в формулу.
1. Введите символ [ (левая квадратная скобка). В выпадающем списке отобразятся названия столбцов и указатели на области данных таблицы.
2. Выберите требуемый элемент списка с помощью мыши или клавиш клавиатуры:
Выбранный элемент подставится в формулу.
«Умные» таблицы отображаются в приложении «МойОфис Таблица» в том виде, в котором они были сохранены в приложении Excel. Например, если в Excel строка итогов была отключена, то в «МойОфис Таблица» таблица отображается без строки итогов. Включение и отключение областей таблицы, а также фильтрации в строке заголовка в приложении «МойОфис Таблица» не поддерживается.
При необходимости строку итогов можно создать вручную. Для этого введите формулу в любую ячейку строки, следующей за последней строкой таблицы.При работе с «умной» таблицей можно выполнять следующие операции:
Если в таблицу необходимо добавить столбец, все ячейки которого содержат одну и ту же формулу, создайте вычисляемый столбец . Для создания вычисляемого столбца можно использовать любой столбец таблицы, который не содержит данных.
Чтобы сделать столбец вычисляемым, введите в любую ячейку данного столбца требуемую формулу и нажмите клавишу Enter .
Все ячейки столбца автоматически заполнятся введенной формулой.
Для вычисляемых столбцов действуют следующие правила:
Авторасширение «умной» таблицы выполняется при вводе данных в смежные ячейки, расположенные в столбце справа от «умной» таблицы. Если в таблице отсутствует строка итогов, то авторасширение также выполняется при вводе данных в смежные ячейки, расположенные в строке, следующей за последней строкой таблицы.
Данные могут вводиться следующими способами:
Если в смежный столбец вводится формула, то данный столбец становится вычисляемым.Структурированные ссылки начинаются с таблиц Excel. Как только таблицы создаются в Excel, он автоматически создает для вас структурированные ссылки.
Теперь взгляните на изображение ниже.
- Шаг 1: Я дал ссылку на ячейку В3. Вместо того, чтобы отображать ссылку как B2, она отображается как Таблица 1[@Sales]. Вот Таблица 1 это имя таблицы, а @Продажи это столбец, о котором мы говорим. Все ячейки в этом столбце обозначаются именем таблицы, за которым следует имя заголовка столбца.
- Шаг 2: Теперь я изменю имя таблицы на Таблица данных и измените заголовок столбца на Количество.
- Шаг 3: Чтобы изменить имя таблицы, поместите курсор внутри таблицы> перейдите в Дизайн> Имя таблицы.
- Шаг 4: Упомяните название таблицы как Таблица данных.
- Шаг 5: Теперь изменим ссылку на ячейку B3.
Итак, мы поняли, что структурированная ссылка состоит из двух частей. Имя таблицы и имя столбца.
Примеры
Используя структурированные ссылки, вы можете сделать формулу динамической. В отличие от обычных ссылок на ячейки, он позволяет формуле быть активной в случае добавления и удаления в диапазоне данных.
Позвольте мне применить формулу СУММ как для нормального диапазона, так и для таблицы Excel.
Формула СУММ для нормального диапазона.
Формула СУММЫ для таблицы Excel.
Позвольте мне добавить несколько строк к данным как обычных таблиц, так и таблиц Excel. Я добавил к данным 2 позиции, теперь вижу разницу.
Структурированная ссылка в таблице Excel показывает обновленное значение, но нормальный диапазон данных не показывает обновленные значения, если вы не внесете некоторые изменения в формулу вручную.
А теперь рассмотрим еще один пример. У меня есть информация о названии продукта, количестве и цене. Используя эту информацию, мне нужно прийти к продажной стоимости.
Чтобы получить стоимость продаж, формула: Кол-во * Цена. Применим эту формулу к таблице.
Формула говорит [@QTY] * [@PRICE]. Это более понятно, чем обычная ссылка на В2 * С2. Мы не получаем имя таблицы, если помещаем формулу в таблицу.
Проблемы с структурированными ссылками Excel
При использовании структурированных ссылок мы сталкиваемся с некоторыми проблемами, которые перечислены ниже.
У структурированных ссылок также есть свои проблемы. Все мы знакомы с применением формулы Excel и ее копированием или перетаскиванием в другие оставшиеся ячейки. Это не тот же процесс в структурированных ссылках. Это работает немного иначе.
Теперь посмотрим на пример ниже. Я применил формулу СУММ в Excel для нормального диапазона.
Если я хочу суммировать цену и продажную стоимость, я просто скопировать и вставить или перетащить текущую формулу в две другие ячейки, и она даст мне СУММУ значения цены и продажной стоимости.
Теперь примените ту же формулу для таблицы Excel для столбца Qty.
Теперь у нас есть сумма столбца Qty. Как и в случае с обычным диапазоном, формула скопирует текущую формулу и вставит ее в столбец Price, чтобы получить сумму Price.
Боже мой. В столбце Цена не отображается сумма; скорее, он по-прежнему показывает только общую сумму столбца Qty. Таким образом, мы не можем скопировать и вставить эту формулу в соседнюю ячейку или любую другую ячейку, чтобы ссылаться на соответствующий столбец или строку.
Перетащите формулу, чтобы изменить ссылку
Теперь мы знаем его недостатки. Мы больше не можем выполнять копирование и вставку со структурированными ссылками. Тогда как нам преодолеть это ограничение?
Решение очень простое. Нам просто нужно перетащить формулу вместо копирования. Выберите ячейку с формулой, используйте маркер заполнения и перетащите его в оставшиеся две ячейки, чтобы изменить ссылку столбца на Цена и Стоимость продажи.
Теперь мы обновили формулы, чтобы получить соответствующие итоги.
Мы видели одну проблему со ссылками на структуру, и мы также нашли решение, но у нас есть еще одна проблема: мы не можем сделать вызов как абсолютную ссылку, если мы перетаскиваем формулу в другие ячейки.
Давайте теперь посмотрим на приведенный ниже пример. У меня есть таблица продаж с несколькими записями, и я хочу объединить данные с помощью функции СУММЕСЛИ в excel.
Теперь я применим функцию СУММЕСЛИ, чтобы получить консолидированные значения продаж для каждого продукта.
Я применил формулу для января месяца. Поскольку это структурированная ссылка, мы не можем скопировать и вставить формулу в оставшиеся два столбца. Ссылка на февраль и март не изменится, поэтому я перетащу формулу.
Ой!! Я не получил никаких значений в столбце «февраль и март». В чем будет проблема . Внимательно посмотрите на формулу.
Мы перетащили формулу с января месяца. В функции СУММЕСЛИ первым аргументом является диапазон критериев. Sales_Table [Product] так как мы перетащили формулу. Он изменился на Продажи_Стол [Jan].
Так как же нам с этим бороться ?? Нам нужно сделать первый аргумент, то есть столбец Product как абсолютный, а другие столбцы как относительную ссылку. В отличие от обычной ссылки, у нас нет возможности использовать клавишу F4 для изменения типа ссылки.
Решение состоит в том, что нам нужно продублировать ссылочный столбец, как показано на изображении ниже.
Теперь мы можем перетащить формулу на другие два столбца. Диапазон критериев будет постоянным, и ссылки на другие столбцы изменятся соответственно.
Совет профессионала: Чтобы сделать строку ROW абсолютной ссылкой, нам нужно сделать запись двойной ROW, но нам нужно поставить символ @ перед именем ROW.
= Sales_Table[@[Product]:[Product]]
Как отключить структурированную справку в Excel?
Если вы не являетесь поклонником структурированных ссылок, вы можете отключить их, выполнив следующие действия.
Этим видео открываю новую рубрику в своем блоге, которая будет называться Лайфхак. Life в переводе с англ. – “жизнь, быт”. Hack – “взломать, взлом”. В этой рубрике я буду делиться советами с помощью которых можно сделать свою жизнь и работу проще, лучше.
Программа Excel выполняет множество разных полезных функций. Но иногда, когда работаешь с большим объемом информации стандартные методы не помогают, как например в случае с редактированием (удалением) ссылок в ячейках Excel.
Далее я обычно копирую все слова, чтобы переместить их в Эксель. Там мне их проще обработать и затем прямо из Excel добавить в рекламную кампания Яндекс.Директ.
Как видно из рисунка выше скопированный текст вставляется вместе со ссылками. И теперь, если я наведу мышкой на ячейку с текстом чтобы, например, изменить слово или сделать Заглавную букву в начале предложения, то вместо редактирования текста у меня откроется ссылка “зашитая” в это слово.
А если таких ячеек сотни? Удаление каждой ссылки вручную из ячейки превратится в утомительное занятие. Поэтому нам нужно найти способ как сделать это все очень быстро.
Что мы делаем для того, чтобы удалить (отредактировать) ссылки в ячейках, когда их много?
В любой свободной ячейке поставьте цифру 1 или любую другую:
Затем на ячейке с цифрой 1 нажмите правой кнопкой мыши и выберите Копировать.
Вокруг ячейки в этот момент “забегает” рамка, подтверждающая, что ячейка скопирована. Затем нажмите на клавиатуре кнопку Ctrl и удерживая ее выделите мышкой те ячейки в которых нужно удалить ссылки.
После этого отжимаем клавишу Ctrl и кликаем по правой кнопке мыши, где далее нужно выбрать Специальная вставка.
В открывшемся меню выберите Умножить и жмите Ок.
На этом все, ваши ссылки будут удалены. Правда сначала может показаться, что ссылки остались, так как текст останется синим и подчеркнутым. Если вас это напрягает, то выделите нужные ячейки, уберите подчеркивание и задайте нужный вам цвет:
На этом все. Если есть какие-нибудь вопросы по экселю, спрашивайте в комментариях – по возможности буду писать новые инструкции.
Читайте также: