Как связать ячейки в excel на разных листах
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
Как связать ячейки в excel на разных листах
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
Excel: Ссылки на ячейки и книги
Часто при работе с регулярно меняющимися данными, мы вынуждены вручную менять их значения во многих таблицах (отчетах), где они используются для расчета. В такой ситуации ссылки в Excel помогут значительно упростить работу с данными.
К примеру, можно разместить данные о ценах на товары на одном листе, а во всех расчетных таблицах прописать ссылки на ячейки листа с ценами. При изменении цены, необходимо будет изменить данные только на листе цен, на всех других листах, где имеются ссылки, изменение пройдет автоматически.
I. Чтобы создать ссылку на ячейку из другого листа той же книги необходимо:
1. Выделить ячейку, в которую необходимо вставить ссылку.
2. Вставить знак «=»
3. Перейти, с помощью вкладок листов, на лист, из которого будем брать данные.
4. Выделить ячейку с необходимым значением.
Таким образом, мы получим ссылку на ячейку из Листа 2, и в исходной ячейке отобразится нужное значение.
То же самое можно было получить, если вручную ввести в исходную ячейку формулу «=Лист2!В2».
Для заполнения остальных ячеек таблицы, можно протянуть формулу с помощью маркера заполнения,
Теперь при изменении цен на Листе 2 , автоматически будут меняться и значения цен в таблице «Объем продаж».
Если необходимо вставить ссылку на ячейку, находящуюся в текущей книге, но на другом листе, нужно в строке формул ввести ссылку в виде:
= Имя листа ! Адрес ячейки
II. Аналогично можно создать ссылку на ячейку из другой книги.
Первый способ — в ячейку первой книги вносим знак «=», переходим ко второй книге, где выбираем нужную ячейку. Далее нажимаем Enter .
Второй способ — вручную прописать ссылку в ячейку.
Пример ссылки, если книги находятся в одном каталоге: =[Книга1.xlsx]Лист1!A1
=[ Имя книги ] Имя листа! Адрес ячейки
Если книга, на которую вы ссылаетесь, расположена в другом каталоге, то необходимо указать полный путь доступа к ней непосредственно в формуле.
Пример ссылки на ячейку в книге, находящейся в каталоге C:\Мои документы:
=C:\ Мои документы \ [Книга1.xlsx]Лист1!A1
Exceltip
Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки
Создание связи между таблицами Excel
Опубликовано 08.06.2013 Автор Ренат Лотфуллин
Связь между таблицами Excel – это формула, которая возвращает данные с ячейки другой рабочей книги. Когда вы открываете книгу, содержащую связи, Excel считывает последнюю информацию с книги-источника (обновление связей)
Межтабличные связи в Excel используются для получения данных как с других листов рабочей книги, так и с других рабочих книг Excel. К примеру, у вас имеется таблица с расчетом итоговой суммы продаж. В расчете используются цены на продукт и объем продаж. В таком случае имеет смысл создать отдельную таблицу с данными по ценам, которые будут подтягиваться с помощью связей первой таблицы.
Когда вы создаете связь между таблицами, Excel создает формулу, которая включает в себя имя исходной книги, заключенную в скобки [], имя листа с восклицательным знаком на конце и ссылку на ячейку.
Создание связей между рабочими книгами
- Открываем обе рабочие книги в Excel
- В исходной книге выбираем ячейку, которую необходимо связать, и копируем ее (сочетание клавиш Ctrl+С)
- Переходим в конечную книгу, щелкаем правой кнопкой мыши по ячейке, куда мы хотим поместить связь. Из выпадающего меню выбираем Специальная вставка
- В появившемся диалоговом окне Специальная вставка выбираем Вставить связь.
Есть еще один, более простой, вариант создания связи между таблицами. В ячейку, куда мы хотим вставить связь, ставим знак равенства (так же как и для обычной формулы), переходим в исходную книгу, выбираем ячейку, которую хотим связать, щелкаем Enter.
Вы можете использовать инструменты копирования и автозаполнения для формул связи так же, как и для обычных формул.
Прежде чем создавать связи между таблицами
Прежде чем вы начнете распространять знания на свои грандиозные идеи, прочитайте несколько советов по работе со связями в Excel:
Делайте легко отслеживаемые связи. Не существует автоматического поиска всех ячеек, содержащих ссылки. Поэтому, используйте определенный формат, чтобы быстро идентифицировать связи с другими таблицами, в противном случае, документ, содержащий ссылки, может разрастись до такой степени, что его будет трудно поддерживать.
Автоматические вычисления. Исходная книга должна работать в режиме автоматического вычисления (установлено по умолчанию). Для переключения параметра вычисления перейдите по вкладке Формулы в группу Вычисление. Выберите Параметры вычислений –> Автоматически.
Избегайте циклические ссылки. Циклические связи – когда две рабочие книги содержат ссылки друг на друга – могут быть причиной медленного открытия и работы файла.
Обновление связей
Для ручного обновления связи между таблицами, перейдите по вкладке Данные в группу Подключения. Щелкните по кнопке Изменить связи.
В появившемся диалоговом окне Изменение связей, выберите интересующую вас связь и щелкните по кнопке Обновить.
Разорвать связи в книгах Excel
Разрыв связи с источником приведет к замене существующих формул связи на значения, которые они возвращают. Например, связь =[Источник.xlsx]Цены!$B$4 будет заменена на 16. Разрыв связи нельзя отменить, поэтому прежде чем совершить операцию, рекомендую сохранить книгу.
Перейдите по вкладке Данные в группу Подключения. Щелкните по кнопке Изменить связи. В появившемся диалоговом окне Изменение связей, выберите интересующую вас связь и щелкните по кнопке Разорвать связь.
Вам также могут быть интересны следующие статьи
- Как сравнить два столбца в Excel — методы сравнения данных Excel
- Формулы таблиц Excel
- Функция СЖПРОБЕЛЫ в Excel с примерами использования
- Четыре способа использования ВПР с несколькими условиями
- Что если отобразить скрытые строки в Excel не работает
- Седьмой урок обучающего курса — Основы Excel — Управление несколькими рабочими листами
- Пятый урок курса по основам Excel — Печать в программе
- Шестой урок онлайн курса по основам Excel — Управление рабочим листом
- Четвертый урок курса по основам Excel — Изменение ячеек
- Третий урок курса по основам Excel — Форматирование рабочих листов
Рубрика: Основы | Метки: связи, Формулы | 8 комментариев | Permalink
8 комментариев
Спасибо! очень полезный материал! Пожалуйста, исправьте опечатку:
«В исходной книге выбираем ячейку, которую необходимо связать, и копируем ее (сочетание клавиш Ctrl+V)»
Думаю должно быть «Ctrl+С»
Спасибо большое, исправил)
Спасибо, очень помогли)
Добрый день!
Спасибо! Интересная информация. Подскажите, связь между файлами не нарушится, если изменить название файла Источник?
Константин
Добрый день.
Есть вопрос: как мультиплицировать связи, т.е. копировать готовые связи в файле, применять их к другим ячейкам и изменяя свойства скопированных связей «подкачивать» информацию из другого файла?
например:
Есть файлы «Январь», «февраль», «март» с показателями, форма файлов (ячейки, столбцы, строки) идентичны, изменяются только данные.
Задача сделать файл «сводка», в котором построчно
январь
февраль
март
свести данные из каждого файла.
легко получается сделать связи по строчке январь, и дальше нужно опять ВРУЧНУЮ делать связи для февраля и марта
Хотелось бы связи января «скопировать» в строки февраля и марта и настроить связь в каждой строчке на файл соответствующего месяца. Заранее спасибо.
p.s. сам потыкал, не получается. все скопированные связи он видит как одну и ту же связь, хоть сто раз её в этом файле вставь( во вкладке Данные\изменить связи, остаётся один набор связей), а хотелось бы что бы после Ctrl+V было 2, 3, 4 и.т.д. наборов связей которые уже можно было настраивать. Если не сложно подскажите как это можно сделать?
Добрый день.
Создаю связанные таблицы для создания бланков заказа, чтобы не вводить 2 раза одни и те же значения, а также для того, чтобы была создана база данных клиентов.
Итого: Нужен документ с данными клиентов, Исходный бланк заказов должен автоматически копировать данные о клиенте, разумеется эти данные изменяются. Создал таблицу бланка заказов, создал таблицу Базы данных клиентов с основной информацией для заполнения бланка. Создал связь, все вписывается автоматически. Как теперь выйти из ситуации, чтобы в следующий раз, при внесении сведений о новом клиенте, сохранить предыдущего клиента и в бланк вписать новые данные? Я подумал, что смогу простым добавлением новой строки поверх созданного клиента обнулить бланк заказа и внести новые данные, однако при таком ходе данные в бланке остаются неизменными, при том, что исходные координаты изменились, по. идее в бланке должны быть пустые значения. Можно ли как-то решить эту задачу? Привязать значение строки в исходной книге?