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

На всех прошлых уроках формулы и функции ссылались в границах 1-го листа. На данный момент незначительно расширим способности их ссылок.

Excel дозволяет созодать ссылки в формулах и функциях на остальные листы и даже книжки. Можно создать ссылку на данные отдельного файла. К слову в таковой метод можно вернуть данные из покоробленного файла xls.

Ссылка на лист в формуле Excel

Доходы за январь, февраль и март введите на 3-х отдельных листах. Позже на четвертом листе в ячейке B2 просуммируйте их.

Возникает вопросец: как создать ссылку на иной лист в Excel? Для реализации данной задачки делаем последующее:

Заполните Лист1, Лист2 и Лист3 потому что показано выше на рисунке.
Перейдите на Лист4, ячейка B2.
Поставьте символ «=» и перейдите на Лист1 чтоб там щелкнуть левой кнопкой мышки по ячейке B2.
Поставьте символ «+» и повторите те же деяния предшествующего пт, но лишь на Лист2, а позже и Лист3.
Когда формула будет иметь последующий вид: =Лист1!B2+Лист2!B2+Лист3!B2, нажмите Enter. Итог должен получиться таковой же, как на рисунке.

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

Ссылка на лист незначительно различается от классической ссылки. Она состоит из 3-х частей:

Имя листа.
Символ восклицания (служит как разделитель и помогает зрительно найти, к какому листу принадлежит адресок ячейки).
Адресок на ячейку в этом же листе.

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

Ссылка на лист в иной книжке Excel

Ссылка на лист в иной книжке имеет уже 5 частей. Смотрится она последующим образом: =’C:Docs[Отчет.xlsx]Лист1′!B2.

Описание частей ссылки на другую книжку Excel:

Путь к файлу книжки (опосля знака = раскрывается апостроф).
Название файла книжки (название файла взято в квадратные скобки).
Имя листа данной для нас книжки (опосля имени запирается апостроф).
Символ восклицания.
Ссылка на ячейку либо спектр ячеек.

Данную ссылку следует читать так:

книжка размещена на диске C: в папке Docs;
название файла книжки «Отчет» с расширением «.xlsx»;
на «Лист1» в ячейке B2 находится значение на которое ссылается формула либо функция.

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

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

Ссылки в Excel

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

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

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

Относительная ссылка дозволяет изменять адресок ячеек по строчкам и столбцам при копировании формулы в другое пространство документа. Другими словами, если скопировать формулу из ячейки А3 в ячейку С3, то для расчета суммы возьмутся новейшие адреса ячеек: С1 и С2.

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

Разглядим последующий пример. Есть таблица, в которую внесены: наименование продукта, его стоимость и количество проданных единиц. Посчитаем итоговую сумму для каждой единицы. В ячейку D6 пишем формулу: =В6*С6. Видите ли, ссылки на ячейки в формуле относительные.

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

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

Интересно почитать: Как в excel группировать ячейки

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

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

Давайте посчитаем сумму для ячеек D1 и D2. В ячейку D3 скопируем формулу из А3. Видите ли, итог заместо 24 – 25. Все из-за того, что в формуле была применена абсолютная ссылка на ячейку $A$1. Потому в расчете использовались не ячейки D1 и D2, а ячейки $A$1 и D2.

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

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

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

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

Ссылка на иной лист в Excel

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

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

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

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

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

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

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

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

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

Ссылка на файл либо ссылка в Excel

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

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

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

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

Интересно почитать: Как в excel сложить текстовые ячейки в

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

Функция ДВССЫЛ в Excel

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

Функция ДВССЫЛ очень полезна тем, что при использовании данной функции есть возможность изменять ссылки на ячейки и спектры в формуле не изменяя при всем этом саму формулу.
Иными словами, введенная формула =B2 схожа формуле =ДВССЫЛ("B2"), но в первом варианте мы оперируем ссылкой, а во 2-м — текстом, который можно изменять.

Описание функции ДВССЫЛ

ДВССЫЛ(ссылка_на_ячейку; [a1])
Возвращает ссылку, заданную текстовой строчкой.

Ссылка на ячейку(неотклонимый аргумент) — ссылка в виде текста вида A1 либо R1C1;
A1(необязательный аргумент) — вид ссылки, в случае когда аргумент воспринимает значение ИСТИНА (либо опущен), то ссылка трактуется как вид A1, когда воспринимает значение ЛОЖЬ, то как вид R1C1.

Примеры использования функции ДВССЫЛ

Пример 1. Ссылка на ячейку

Начнем с обычной задачки, который мы уже отчасти разобрали.
Введем случайное значение в ячейку A1, сейчас чтоб создать ссылку на ячейку введем формулу =ДВССЫЛ("A1"), к примеру, в ячейку A2:

Пример 2. Ссылка на иной лист

Мало усложним задачку, и применим формулу ДВССЫЛ для ссылки на иной лист.
Перейдем на хоть какой иной лист книжки и вводим формулу =ДВССЫЛ("Пример_1!A1"), где лист Пример_1 — лист из первого примера:

Пример 3. Функции

Разглядим примеры с одновременным применением функции ДВССЫЛ и остальных функций.

Функция СУММ

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

Таблица с данными по продажам

Функция СУММ с прямой ссылкой на спектр решает эту задачку, к примеру, можно применить формулу =СУММ(B2:B5) для подсчета продаж апельсинов.
Но тогда при изменении периода нам придется поменять и спектр в начальной формуле.
Обойдем эту делему записав спектр в текстовом виде с внедрением ссылок на остальные ячейки — запишем формулу =СУММ(ДВССЫЛ(B15&2&":"&B15&(1+$A16))), где ячейка A16 отвечает за номер периода:

Расписывая по шагам данную формулу, мы в конце получим формулу =СУММ(B2:B5), что нам и требовалось.

Функция ПОИСКПОЗ

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

Записываем в оценку кандидатов формулу =ДВССЫЛ("G"&ПОИСКПОЗ(B2;$F$1:$F$6;1)), где при помощи функции ПОИСКПОЗ находим относительное положение оценки кандидата в аспекты оценок, а функцией ДВССЫЛ подтягиваем полученную оценку для всякого кандидата.

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

excel вставить значение с другого листа

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

Перейдем на лист «Прайс-лист». Поглядим, что мы тут имеем. Стоимость первого продукта в рублях — доски для сноуборда «Balance» — находится в ячейке «D6». Запомним это.

Перебегаем на наш лист. Отмечаем ячейку, где обязана размещаться стоимость доски «Balance» в рублях. В этом случае это ячейка «Е2». В строке формул пишем последующее выражение: «=’Прайс-лист’!D6». В апострофах (‘…’) пишем заглавие листа в документе, откуда мы собираемся брать данные. Опосля восклицательного знака идет заглавие ячейки. Ничего сложного.

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

Аналогично вносим формулы вычисления во все другие ячейки.

Сейчас начинаем вычислять сумму приобретенного продукта в каждой строке. Для этого нужно стоимость помножить на количество. Верно?

Примечание: Мы стараемся как можно оперативнее обеспечивать вас животрепещущими справочными материалами на вашем языке. Эта страничка переведена автоматом, потому ее текст может содержать некорректности и грамматические ошибки. Для нас принципиально, чтоб эта статья была для вас полезна. Просим вас уделить пару секунд и сказать, посодействовала ли она для вас, при помощи клавиш понизу странички. Для удобства также приводим ссылку на оригинал (на британском языке) .

Интересно почитать: Сцепить ячейки в excel

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

К примеру может потребоваться скопировать лишь итоговое значение формулы в ячейку на другом листе. Либо, может потребоваться удалить значения, которые можно употреблять в формуле, скопировав значение итог формулы в другую ячейку на листе. В случае обычной скопировать выделенный фрагмент такие деяния приведет к появлению ошибки ссылку Недопустимая ячейка (#REF!) обязана отображаться в мотивированной ячейке, так как больше не будет ссылки на ячейки, содержащие значения, которые употребляются в формуле.

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

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

На вкладке Основная в группе буфер обмена нажмите клавишу Копировать либо нажмите сочетание кнопок CTRL + C.

Выделите левую верхнюю ячейку область вставки.

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

На вкладке Основная в группе буфер обмена нажмите клавишу Вставить и нажмите клавишу Вставить значения. Либо нажмите Сочетание кнопок Ctrl + Alt + V , чтоб показать диалоговое окно Особая вставка.

Изберите значения и нажмите клавишу ОК.