Ref-sgain.ru

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

Как скопировать формулу, не меняя ссылки на ячейки в Excel?

Ссылки в Excel

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

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

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

Ссылки в Excel

Относительные ссылки позволяют изменять направление ячеек по строкам и столбцам при копировании формулы в другое место документа. Другими словами, если вы скопируете формулу из ячейки A3 в ячейку C3, то новые адреса ячеек C1 и C2 будут использоваться для вычисления количества.

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

Рассмотрим следующий пример. Имеется таблица, содержащая название товара, его цену и сколько единиц было продано. Для каждого товара подсчитаем общую сумму. Используя ячейку D6, напишите формулу: =B6*C6. Как вы видите, в формуле есть относительная ссылка на ячейки.

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

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

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

В Excel перед ссылкой на абсолютный адрес ячейки необходимо добавить знак «$» перед именами столбцов и строк. В качестве альтернативы установите курсор после адреса ячейки и нажмите клавишу «F4». Например, в примере сумма в ячейке A3 теперь рассчитывается на основе абсолютной ссылки на ячейку A1.

Вычислим сумму для ячеек D1 и D2. В ячейку D3 скопируем формулу из A3 . Как вы видите, результат равен 25 вместо 24. Это потому, что в формуле использовалась абсолютная ссылка на ячейку $A$1 . Из-за этого вместо ячеек D1 и D2 были использованы $A$1 и D2.

В качестве примера рассмотрим следующую таблицу: в ней есть название товара и его цена. Чтобы определить цену товара для продажи, необходимо рассчитать НДС. НДС составляет 20%, значение вводится в ячейку B9 . Введите формулу расчета в ячейку C6 .

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

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

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

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

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

Читайте так же:
Как создать гистограмму с накоплением в Excel?

Ссылка на ячейку из другого рабочего листа в формуле будет выглядеть следующим образом: Sheet1!A1 — имя листа, восклицательный знак, адрес ячейки. Если в имени рабочего листа используется пробел, он должен быть заключен в одинарные кавычки: «Итоговые» — «Итоговые»!A1.

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

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

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

Формулу можно исправить, если сослаться на ячейку B1 как на абсолютную: $B$1, и растянуть ее на весь столбец.

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

Если вы хотите сослаться на другую книгу Excel в формуле, поместите название книги в квадратные скобки. Например, в ячейке A1 книги Book1 создайте ссылку на ячейку A3 книги Reference. Для этого в ячейке A1 поставьте «=», в квадратных скобках укажите название книги с расширением, затем название рабочего листа из этой книги, поставьте «!» и адрес ячейки.

Упомянутую книгу необходимо открыть.

Ссылка на файл или гиперссылка в Excel

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

Выберите ячейку, в которую нужно вставить гиперссылку. Это может быть число, текст, вычисляемая формула или пустая ячейка. Перейдите на вкладку «Вставка» и нажмите кнопку «Гиперссылка».

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

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

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

Ссылки в Excel

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

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

Читайте так же:
Как создать диаграмму по подсчету значений в Excel?

Рассмотрим простой пример. Вам нужно сложить два числа. Это можно легко сделать, набрав «=» в свободной ячейке (например, внизу), а затем используя знак «+» для обозначения добавляемых ячеек. Если числа большие, лучше всего складывать их с помощью функции sum, указывая сразу весь диапазон суммы.

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

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

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

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

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

Где буква представляет столбец, а число — строку.

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

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

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

Расчет доли

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

Ошибка при использовании относительной ссылки

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

Выставление абсолютной ссылки

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

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

Смесь абсолютных и относительных ссылок возможна, когда фиксируется только одна строка или столбец:

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

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

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

Читайте так же:
Как скрыть кнопки разворачивания и сворачивания в сводной таблице?

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

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

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

Окно разрыва внешних связей

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

Стиль ссылок

Еще одна вещь о ссылках. Иногда они выглядят несколько необычно. Это особый стиль ссылок, где адрес ячейки A1 назван R1C1 (первая строка (R1), первый столбец (C1)). Ссылки в таком формате иногда бывают полезны, но очень редко. Поэтому возвращение нормального вида адресу ячейки не помешает. Вы должны снять флажок со стиля ссылки R1C1 после выбора команды Файл * Параметры * Формулы.

5. Расчеты в Excel

Excel рассматривает содержимое ячеек, начинающееся со знака «=», как формулы. Существует несколько способов создания формул, включая числовые константы, функции Excel и ссылки на ячейки. Завершить ввод формулы можно нажатием клавиши Enter или щелчком по кнопке в строке формул. При активации ячейки введенная формула отображается в строке формул. Результаты отображаются в ячейке и в строке формул при активации ячейки.

Примечание. Чтобы увидеть формулы в ячейках таблицы, нужно в диалоговом окне СервисПараметры на вкладке Вид в области Параметры окнаустановить флажок Формулы. Для возвращения к обычному виду ячеек необходимо сбросить этот флажок.

Читайте так же:
Как случайным образом отсортировать или перемешать данные в списке в таблицах Google?

Эмпирическое правило при использовании формул в Excel гласит: если вычисляемое значение зависит от других ячеек таблицы, всегда следует использовать формулу со ссылками на эти ячейки. Ссылка задается путем указания адреса ячейки. На рисунке 5.1 показан пример вычисления в ячейке C2 с использованием формулы: = A2*B2

Ссылку на ячейку можно указать двумя способами:

  1. ввести адрес ячейки с клавиатуры;
  2. по ходу ввода формулы щелкать на нужной ячейке.

Второй маршрут — более быстрый и удобный.

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

  1. активизировать ячейку С2;
  2. ввести с клавиатуры знак "=";
  3. щелкнуть в ячейке А2;
  4. ввести с клавиатуры знак " *";
  5. щелкнуть в ячейке В2;
  6. нажать <Enter>.

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

Копирование формул

Копирование формул в соседние ячейки осуществляется автозаполнением, т.е. перетаскиванием маркера заполнения ячейки с формулой в соседние ячейки (столбец или строку). Это самый удобный и быстрый способ копирования.

  1. выделить диапазон для заполнения (включая ячейку с введенной формулой) и выполнить команду меню Правка />Заполнить />Вниз (если копирование выполняется по столбцу).
  2. протянуть маркер заполнения ячейки с формулой правой кнопкой мыши, в появившемся контекстном меню выбрать нужную команду
    • Копирование ячеек;
    • Заполнение только значений.

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

Формулы после копирования

Модель после скопирования

Формула после копирования

Относительные и абсолютные ссылки

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

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

На рисунке 5.3 показан пример расчета налога с использованием формулы:

Налог = Стоимость * НДС

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

5%

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

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

Ссылки на ячейки могут быть смешанными, то есть с абсолютным адресом строки и относительным адресом столбца, или наоборот:

A$2 — фиксированная строка.

$A2 — фиксированный столбец;

Тип адресации ( относительная, абсолютная, смешанные) меняется повторными нажатиями клавиши <F4>при вводе адреса ячейки в формулу или при редактировании формулы.

Имена ячеек для абсолютной адресации

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

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

Название текущей ячейки (строки):

Первый способ:

  1. Щелкните в поле адреса строки формул, введите имя;
  2. Нажмите клавишу .
  1. выполнить команду Вставка />Имя />Присвоить ;
  2. в диалоговом окне ввести имя.

Обратите внимание, что удаление имени вызовет ошибку, если оно уже было использовано в формуле (сообщение — «# имя?) «).

Просмотр зависимостей

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

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

Для просмотра влияющих ячеек, нужно сделать текущей ячейку с формулой и выполнить команду Сервис />Зависимости формул />Влияющие ячейки.

Если нужно увидеть, в какой формуле имеется ссылка на текущую ячейку, то следует выполнить команду Сервис />Зависимости формул />Зависимые ячейки.

Все зависимости в таблице изображаются стрелками. Для удаления стрелок служит команда Сервис />Зависимости формул />Убрать все стрелки.

При необходимости просмотра многих зависимостей удобно отобразить панель инструментов Зависимости командой Сервис />Зависимости формул />Панель зависимостей.

Редактирование формул

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

Изменение ссылки в формуле:

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

Изменение типа адресации:

  • Выделите адрес ячейки, дважды щелкнув мышью;
  • Нажмите клавишу .

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

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

Копирование ячеек с формулами

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

  1. вставить на другой лист только числовое значение ячейки;
  2. скопировать содержимое ячейки с учетом ссылок.

Скопировать числовое значение :

  1. Поместите содержимое ячейки (диапазона) в буфер обмена;
  2. Переключитесь на другой лист и выберите команду Paste Special из меню Paste (или из контекстного меню ячейки вставки);
  3. Активируйте радиокнопку Value.

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

Копинг с сохранением зависимости:

  1. Перейдите на целевой лист, активируйте ячейку для вставки и введите знак «=»;
  2. Нажмите на ярлык исходного листа и выберите ячейку для копирования — ее адрес вместе с именем рабочего листа будет отображен в строке формул (например, =Ark1!F8;
  3. Нажмите кнопку .

Изменение значений влияющих ячеек на исходном листе изменяет значение, скопированное на целевой лист.

Формулы со ссылками на другие листы

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

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector