Типы ссылок
Относительные и абсолютные ссылки в формулах





Основные правила составления формул в таблицах Excel. Логика копирования и переноса формул. Зачем ячейке нужны знаки доллара $ и как их поставить.

Конспекты

Курсы

Практикум


Скачать файл
Теги: Бесплатный курс Excel, самоучитель Excel, конспекты тренинга Excel, без регистрации.

Относительные и абсолютные ссылки в формулах

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

Ввод формулы
Начнем курс вычислений с основ - разберемся, что и как посчитать в Excel, какие возникают трудности при вычислениях и как их преодолевать - в программе достаточно много фишек, о которых стоит знать. Размер используемых таблиц небольшой, но сделано это исключительно в учебных целях - чтобы Вы успели увидеть и понять, как работают инструменты и оценить результат. В жизни конечно таблицы будут нескромно большого размера :)
Чтобы освежить в памяти основы ввода и копирования формул, будем составлять план на следующий месяц. Все данные для расчетов в таблице прикрепленного внизу страницы файла уже содержатся, нам остается заполнить столбец «Сумма в у.е.» Таблицы № 1
Для этого выделяем ячейку, в которой должен получиться результат, ставим знак "равно" и умножаем ячейку с ценой на ячейку с количеством: D4=C4*B4.

По завершении ввода формулы обязательно жмем Enter (!)

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

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

Зачем нужен знак доллара?
Далее предлагаю разобраться с нюансами заполнения столбца Таблицы № 2 «Сумма в руб. дата 2», в котором получившуюся в предыдущем столбце сумму в условных единицах нужно перевести в рубли. Курсы условных единиц указаны справа от таблицы в диапазоне ячеек H4:H7. Чтобы произвести вычисления и пересчитать сумму в рублях пишем следующую формулу для ячейки E4=D4*H5

Если попробовать скопировать получившуюся формулу в остальные строки таблицы прямо сейчас, то в следующей же ячейке столбца "Сумма в руб. дата 2" формула будет выглядеть как Fx:E5=D5*H6 – результат очевидно будет некорректным, так как внутренние ссылки (а к ним относятся ссылки на ячейки других листов этой же книги) имеют по умолчанию относительный тип адресов, что означает буквально следующее: при копировании ячейки с формулой все ячейки, участвующие в расчетах, сохраняют свое положение относительно друг друга, а в данном случае это совсем не то, что нам нужно – адрес ячейки H5 меняться не должен, поэтому нужно изменить тип адресов с относительного на абсолютный:
1. Переходим в режим редактирования ячейки E4 (Double-клик по ячейке либо через строку формул либо F2
2. Помещаем курсор в любое место адреса ячейки, адрес которой хотим зафиксировать – в примере это H5 (выделять этот адрес полностью необходимости нет) жмем на клавиатуре F4 – появляются два знака доллара $ и завершаем ввод формулы нажатием на клавишу Enter

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

Для чего нужен смешанный тип адресов, с одним знаком доллара $?
Если перед Вами стоит задача, похожая на смоделированную в Таблице № 2, то перемножить в каждой ячейке норму расхода на план выпуска (она указана в 18-ой строке сразу под названием филиала) занятие долгое даже в такой небольшой таблице. Потому при написании первой формулы в верхнем левом углу таблицы нужно определиться – значение каких элементов адреса не должно меняться при копировании ячейки с формулой вниз и/или вправо

В примере – при копировании вправо в адресе ячейки B19 не должна меняться буква столбца (но при копировании вниз норму каждый раз нужно брать из следующей строки), а при копировании вниз в адресе ячейки C18 не должен меняться номер строки (но при копировании вправо план нужно каждый раз брать из следующего столбца)

И осталось подвести итоги в Таблице № 2
Наверняка Вы уже работа с автосуммой. Но для решения следующей задачи хочу предложить не совсем обычную схему – для подведения итогов в таблице мы привыкли вставлять формулу в одну итоговую ячейку и копировать ее по столбцам (а потом то же самое проделать для строк) – но можно решить вопрос гораздо эффектнее
Попробуем подвести итоги в таблице быстро: выделяем всю таблицу плюс захватите в область выделения ту пустую строку и тот пустой столбец, в которых должны оказаться итоги. Для быстрого выделения таблицы можно выбрать одну любую заполненную ячейку этой таблицы и нажать Ctrl + * например, а далее при одной нажатой клавише Shift по одному разу нажать стрелку вправо и стрелку вниз, соответствующие направлению, в котором от выделенной таблицы находятся нужные нам строки и столбцы:

И далее не снимая выделения нажмите значок автосуммы, расположенный на вкладке Формулы – Библиотека функций – Автосумма [Formulas - Function library - AutoSum] либо нажмите комбинацию клавиш Alt + = и результат получится следующий:

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

Похожие материалы

Условное форматирование строк по формуле
Условное форматирование. По формуле.
Настройка правил условного форматирования с использованием формулы: выделение столбцов (проверка текста в шапке таблицы), обработка пустот, выделение повторов.

Относительные и абсолютные ссылки в формулах
Типы ссылок
Основные правила составления формул в таблицах Excel. Логика копирования и переноса формул. Зачем ячейке нужны знаки доллара $ и как их поставить.

Как разделить текст ячейки Excel по разным столбцам, используя числовую закономерность расположения знаков или символ-разделитель
Текст по столбцам
Как разделить текст по разным столбцам (колонкам), если в исходном документе разнородные фрагменты текста записаны в одной ячейке?

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

Заказчики

Я сотрудничаю с многими известными компаниями. Вот некоторые из них:

ВТБ МегаФон Глобус Абсолют

Связаться с автором

Оставьте ваше имя и адрес электронной почты, в ближайшее время я свяжусь
с вами для консультации и отвечу на вопросы!



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