Top-office11.ru

IT и мир ПК
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Ссылки в Excel

Ссылки в Excel

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

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

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

Простое суммирование

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

Ссылка на другой лист в Excel

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

Ссылка на ячейку с другого листа в формуле будет выглядеть следующим образом: Лист1!А1 – название листа, знак восклицания, адрес ячейки. Если в названии листа используются пробелы, то его нужно взять в одинарные кавычки: ‘Итоговые суммы’ – ‘Итоговые суммы’!А1.

Например, рассчитаем значение НДС для товаров. Таблица, в которой будет рассчитываться формула, находится на Листе1, значение НДС находится на листе с названием Все константы.

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

Возвращаемся на Лист1. В ячейку С6 пишем формулу для расчета НДС: ставим «=», затем выделяем ячейку В6 и делаем ссылку на ячейку В1 с другого листа.

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

Если изменить название листа Все константы на Все константы1111, то оно автоматически поменяется и в формуле. Точно также, если на листе Все константы изменить значение в ячейке В1 с 20% на 22%, то формула будет пересчитана.

Для того чтобы сделать ссылку на другую книгу Excel в формуле, возьмите ее название в квадратные скобки. Например, сделаем ссылку в ячейке А1 в книге с названием Книга1 на ячейку А3 из книги с названием Ссылки. Для этого ставим в ячейку А1 «=», в квадратных скобках пишем название книги с расширением, затем название листа из этой книги, ставим «!» и адрес ячейки.

Книга, на которую мы ссылаемся, должна быть открыта.

Циркулярная ссылка в Excel

значит ссылку на ту же ячейку, в которой мы работаем, радиальная ссылка — это тип всплывающего окна либо предупреждения, отображаемого в excel, что мы используем радиальную ссылку в нашей формуле, и расчет быть может неправильным, к примеру, в ячейке A1, если я напишу формула = A1 * 2, это радиальная ссылка, так как снутри ячейки A1 я употреблял ссылку на ячейку конкретно A1.

Растолковал

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

Циркулярная ссылка

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

Читать еще:  Как подключить PS4 к монитору без HDMI

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

Как включить / отключить циклические ссылки в Excel?

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

  • Шаг 1: Перейти в файл,
  • Шаг 2: см. в крайнем клике по функции.
  • Шаг 3: Перейдите к формулам, см. Параметр расчета вверху и включите итеративный расчет. Потом нажмите ОК.

Циркулярный справочник, шаг 3

  • Шаг 4: Опосля того, как вы включите опцию Итеративного вычисления на вкладке формул. Пожалуйста, проверьте и укажите характеристики, как обозначено ниже:
    • Наибольшее количество итераций: Он указывает, как нередко уравнение следует пересчитывать. Чем выше количество акцентов, тем больше времени занимает вычисление..
      • Наибольшее изменение: Он описывает более резкую разницу меж расчетными плодами. Чем меньше число, тем наиболее четкий итог вы получите и тем больше времени будет нужно Excel для проверки рабочего листа.

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

      Как употреблять радиальную ссылку в Excel?

      Ниже приведены данные.

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

      Циркулярный справочный пример 1-1

      • Шаг 2: Ячейка, содержащая общее значение, возвратилась к 41 300 заместо 413 опосля включения итеративного вычисления.

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

      • Чтоб отыскать радиальную ссылку, поначалу перейдите на вкладку «Формулы», щелкните опцию «Проверка ошибок», а потом перейдите к «Циркулярные ссылки», и тут отображается крайняя введенная радиальная ссылка:

      Круговой пример 1-3

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

      Справочный пример 1-5

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

      Почему не рекомендуется употреблять циркулярную ссылку?

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

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

      Выпадающий список в Excel с данными с другого листа/файла

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

      1. Делаем активной ячейку, куда хотим поместить раскрывающийся список.
      2. Открываем параметры проверки данных. В поле «Источник» вводим формулу: =ДВССЫЛ(“[Список1.xlsx]Лист1!$A$1:$A$9”).

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

      Как убрать циклические ссылки

      Убрать циклические ссылки из основного контента несложно: достаточно найти их, открыть редактор CMS и вручную удалить или изменить ссылки.

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

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

      Рассмотрим один из них — удаление ссылки из логотипа на WordPress-сайте.

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

      Нужно сделать так, чтобы при открытии главной страницы ссылка в логотипе не была активной, а с других страниц ссылка в логотипе вела на главную. Поможет в этом условие if/else. Модифицируем код вывода логотипа с учетом условия, вставляем его в header.php и сохраняем изменения:

      Теперь рассмотрим, как удалить циклическую ссылку из заголовка h1 текущей страницы на CMS WordPress. Для этого в файле header.php найдите строку, которая отвечает за вывод заголовка h1. В зависимости от темы синтаксис будет отличаться. Например, она может выглядеть так:

      Эту строку нужно заменить на такой код вывода заголовка:

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

      Но скрипты будут работать только на стороне пользователя, поэтому реализация с помощью PHP более предпочтительна.

      Некоторые вебмастера «борются» с циклическими ссылками путем преобразования их в якорные вида http://site.ru/#!. С точки зрения юзабилити это бесполезно, ведь сами ссылки остаются, и пользователи при клике по ним попадают на те же страницы.

      Также для удаления циклических ссылок есть специальные плагины. Например, для WordPress — «Remove Redundant Links».

      Плагин конвертирует ссылки вида:

      в обычный текст со всплывающим заголовком “You are here.” (надпись можете поменять):

      Также удаляются ссылки вида:

      После установки плагина циклические ссылки деактивируются автоматически. Единственный недостаток — «слетают» стили, поэтому приходится прописывать их вручную.

      Устраняем циклические ссылки

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

      Например, эта формула не является работоспособной поскольку находится в ячейке D3 и ссылается на себя же. Ее нужно вырезать из этой ячейки, выделив формулу в строке формул и нажав CTRL+X, после чего вставить в другой ячейке, выделив ее и нажав CTRL+V.

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

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

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

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

      Создание циклической ссылки

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

        Выделяем элемент листа A1 и записываем в нем следующее выражение:

      Создание простейшей циклической ссылки в Microsoft Excel

      Ячейка ссылается сама на себя в Microsoft Excel

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

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

      Число 5 в ячейке в Microsoft Excel

      Ссылка в ячейке в Microsoft Excel

      Одна ячейка ссылается на другую в Microsoft Excel

      Установка ссылки в ячейке в Microsoft Excel

      Пометка циклической связи в Microsoft Excel

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

      Расчет выручки в таблице в Microsoft Excel

        Чтобы зациклить формулу в первой строчке, выделяем элемент листа с количеством первого по счету товара (B2). Вместо статического значения (6) вписываем туда формулу, которая будет считать количество товара путем деления общей суммы (D2) на цену (C2):

      Вставка циклической ссылки в таблицу в Microsoft Excel

      Циклическая ссылка в таблице в Microsoft Excel

      Маркер заполнения в Microsoft Excel

      Циклические ссылки скопированы в таблице в Microsoft Excel

      Поиск циклических ссылок

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

        Итак, если при запуске файла Excel у вас открывается информационное окно о том, что он содержит циклическую ссылку, то её желательно отыскать. Для этого перемещаемся во вкладку «Формулы». Жмем на ленте на треугольник, который размещен справа от кнопки «Проверка наличия ошибок», расположенной в блоке инструментов «Зависимости формул». Открывается меню, в котором следует навести курсор на пункт «Циклические ссылки». После этого в следующем меню открывается список адресов элементов листа, в которых программа обнаружила цикличные выражения.

      Поиск циклических ссылок в Microsoft Excel

      Переход к ячейке с циклической ссылкой в Microsoft Excel

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

      Сообщение о циклической ссылке на панели состояния в Microsoft Excel

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

      Циклическая ссылка на другом листе в Microsoft Excel

      Исправление циклических ссылок

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

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

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

      Выражение в программе в Microsoft Excel

      Статическое значение в Microsoft Excel

      Циклическая ссылка в ячейке таблицы в Microsoft Excel

      Ссылка заменена на значения в Microsoft Excel

      Циклических ссылок в книге нет в Microsoft Excel

      Разрешение выполнения цикличных операций

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

      Блокировка циклических ссылок в Microsoft Excel

        Прежде всего, перемещаемся во вкладку «Файл» приложения Excel.

      Перемещение во вкладку Файл в Microsoft Excel

      Переход в окно параметров в Microsoft Excel

      Переход во вкладку Формулы в Microsoft Excel

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

      Включение итеративных вычислений в Microsoft Excel

      Ячейки с циклическими формулами отображают корректные значения в Microsoft Excel

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

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

      ЗакрытьМы рады, что смогли помочь Вам в решении проблемы.

      Помимо этой статьи, на сайте еще 12369 инструкций.
      Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.

      Отблагодарите автора, поделитесь статьей в социальных сетях.

      ЗакрытьОпишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Ссылка на основную публикацию
Adblock
detector