Excel: Ссылки на ячейки и книги
Часто при работе с регулярно меняющимися данными, мы вынуждены вручную менять их значения во многих таблицах (отчетах), где они используются для расчета. В такой ситуации ссылки в Excel помогут значительно упростить работу с данными.
К примеру, можно разместить данные о ценах на товары на одном листе, а во всех расчетных таблицах прописать ссылки на ячейки листа с ценами. При изменении цены, необходимо будет изменить данные только на листе цен, на всех других листах, где имеются ссылки, изменение пройдет автоматически.
I. Чтобы создать ссылку на ячейку из другого листа той же книги необходимо:
1. Выделить ячейку, в которую необходимо вставить ссылку.
2. Вставить знак «=»
3. Перейти, с помощью вкладок листов, на лист, из которого будем брать данные.
4. Выделить ячейку с необходимым значением.
Таким образом, мы получим ссылку на ячейку из Листа 2, и в исходной ячейке отобразится нужное значение.
То же самое можно было получить, если вручную ввести в исходную ячейку формулу «=Лист2!В2».
Для заполнения остальных ячеек таблицы, можно протянуть формулу с помощью маркера заполнения,
Теперь при изменении цен на Листе 2 , автоматически будут меняться и значения цен в таблице «Объем продаж».
Если необходимо вставить ссылку на ячейку, находящуюся в текущей книге, но на другом листе, нужно в строке формул ввести ссылку в виде:
= Имя листа ! Адрес ячейки
II. Аналогично можно создать ссылку на ячейку из другой книги.
Первый способ — в ячейку первой книги вносим знак «=», переходим ко второй книге, где выбираем нужную ячейку. Далее нажимаем Enter .
Второй способ — вручную прописать ссылку в ячейку.
Пример ссылки, если книги находятся в одном каталоге: =[Книга1.xlsx]Лист1!A1
=[ Имя книги ] Имя листа! Адрес ячейки
Если книга, на которую вы ссылаетесь, расположена в другом каталоге, то необходимо указать полный путь доступа к ней непосредственно в формуле.
Пример ссылки на ячейку в книге, находящейся в каталоге C:\Мои документы:
=C:\ Мои документы \ [Книга1.xlsx]Лист1!A1
покупка
Как привязать ячейки формулы (изменить ссылку на ячейку на абсолютную) в Excel?
Обычно при создании формулы ссылки на ячейки являются относительными, поэтому результат вычисления будет изменен автоматически, когда вы скопируете и вставите их в другое место. Чтобы привязать ячейки формулы и сделать результат постоянным, необходимо изменить ссылку на ячейку в формулах на абсолютную. Следующие два метода покажут вам, как с этим бороться.
Easily convert formula references in bulk (such as relative to absolute) in Excel
Click Kutools > Convert Refers. The Kutools for Excel‘s Convert Formula References utility helps you easily convert all formula references in bulk in selected range such as convert all relative to absolute at once in Excel. See below screenshot:
Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 60 days. Read More Download the free trial now
Привязка ячеек (изменение ссылки на ячейку на абсолютную) нажатием клавиши F4
Вы можете изменить ссылку на ячейку формулы на абсолютную, нажав клавишу F4. Пожалуйста, сделайте следующее.
1. Дважды щелкните ячейку формулы, чтобы перейти в режим редактирования.
2. Щелкните ссылку на ячейку, а затем нажмите F4 до тех пор, пока перед номером строки и номером столбца не появятся знаки $. Смотрите скриншот:
2. Продолжайте выбирать ссылку на другую ячейку в текущей ячейке формулы и повторяйте шаг 2, пока все ссылки на ячейки не будут изменены на абсолютные.
Массовая привязка ячеек (изменить ссылку на ячейку на абсолютную) с помощью Kutools for Excel
Если вы хотите привязать все выбранные ячейки формулы одновременно, вы можете попробовать утилиту Convert Refers в Kutools for Excel.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. Выберите ячейки формулы, в которых нужно изменить все ячейки, на которые есть ссылки, на абсолютные, затем щелкните Кутулс > Больше > Преобразовать ссылки.
2. в Преобразование ссылок на формулы в диалоговом окне выберите К абсолютному вариант, а затем щелкните OK кнопку.
Затем все ячейки, на которые есть ссылки в выбранных формулах, немедленно преобразуются в абсолютные.
Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Как зафиксировать ссылку в Excel?
Это необходимо для того, чтобы, когда вы протягивали формулу, ссылка на ячейку не смещалась. Например, для расчета коэффициента сезонности января (см. вложение) мы средние продажи за январь (пункт 2 см. вложение) делим на среднегодовые продажи за 3 года (пункт 3 см. вложение). Если мы просто протянем ячейку вниз, чтобы рассчитать коэффициенты для других месяцев, то для февраля мы получим, что среднегодовые продажи за февраль разделятся на ноль, а не на среднегодовые продажи за 3 года.
Как зафиксировать ссылку на ячейку, чтобы, когда мы протягивали формулу, ссылка не смещалась?
Для этого в строке формул выделяете ссылку, которую хотите зафиксировать:
и нажимаете клавишу «F4». Ссылка станет со значками $, как на рисунке:
это означает, что если вы протяните формулу, то ссылка на ячейку $F$4 останется на месте, т.е. зафиксирована строка ‘4’ и столбец ‘F’. Если вы еще раз нажмёте клавишу F4, то ссылка станет F$4 — это означает, что зафиксирована строка 4, а столбец F будет перемещаться.
Если еще раз нажмете клавишу «F4», то ссылка станет $F4:
Это означает, что зафиксирован столбец F и он не будет перемещаться, когда вы будите протаскивать формулу, а ссылка на строку 4 будет двигаться.
Если ссылки имеют вид R1C1, то полностью зафиксированная ячейка будет иметь вид R4C6 :
Если зафиксирована только строка (R), то ссылка будет R 4 C[-1]
Если зафиксирован только столбец (С), то ссылка будет иметь вид R C6
Для того, чтобы зафиксировать диапазон, необходимо его выделить в строке формул в Excel и нажать клавиши “F4”.
Предлагаю вам самостоятельно проделать описанные выше операции, и если будут вопросы, задать их в комментариях к данной статье.
Присоединяйтесь к нам!
Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:
- Novo Forecast Lite — автоматический расчет прогноза в Excel .
- 4analytics — ABC-XYZ-анализ и анализ выбросов в Excel.
- Qlik Sense Desktop и QlikView Personal Edition — BI-системы для анализа и визуализации данных.
Тестируйте возможности платных решений:
- Novo Forecast PRO — прогнозирование в Excel для больших массивов данных.
Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.
Как привязать ячейку к ячейке в excel
Информация в 600 ячеек, в нескольких столбцах, хочу первый столбец отсортировать от мин до макс, как сделать так чтобы остальные столбцы соответсвовали первому? в поиске не нашел, заранее спасибо!
Пользователь
Сообщений: 65 Регистрация: 01.01.1970
23.04.2012 09:23:46
Выделяете все свои столбцы с данными и сортируете по первому столбцу.
Пользователь
Сообщений: 47199 Регистрация: 15.09.2012
23.04.2012 10:31:39
Выделить данные, Данные-Фильтр или Данные-Сортировка.
Сообщений: 60969 Регистрация: 14.09.2012
Контакты см. в профиле
23.04.2012 10:52:16
А зачем вообще выделять? Если правее сортируемого диапазона нет никаких данных,- активируем любую ячейку первого столбца и жмём кнопочку сортировки.
23.04.2012 11:07:45
?! А если «специалист» столбы с данными пустил через один пустой. А если «шапка» над данными с прорехами. А если не усвоены правила XL по организации и оформлению данных для фмльтра и сортировки. Словом, выделение — не помешает, если. Ох! Уж сколько их может быть — этих если. 😉 -97137-
Сообщений: 60969 Регистрация: 14.09.2012
Контакты см. в профиле
23.04.2012 11:10:52
Если «специалист» — тады беда 🙂 Согласен.
Страницы: 1
Читают тему
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |