Относительная и абсолютная ссылка на ячейки и форматирование

2024 Автор: Geoffrey Carr | [email protected]. Последнее изменение: 2023-12-17 10:57

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

ШКОЛЬНАЯ НАВИГАЦИЯ

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

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

Что такое ссылка на ячейку?

«Ссылка на ячейку» означает ячейку, к которой относится другая ячейка. Например, если в ячейке A1 есть = A2. Тогда A1 относится к A2.

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

Клетки в электронной таблице относятся к строкам и столбцам. Столбцы вертикальны и помечены буквами. Строки горизонтальны и помечены цифрами.

Первой ячейкой в электронной таблице является A1, что означает, что столбец A, строка 1, B3 относится к ячейке, расположенной во втором столбце, третьей строке и так далее.

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

Типы ссылок на ячейки

Существует три типа ссылок на ячейки.

Абсолютный - это означает, что ссылка на ячейку остается прежней, если вы копируете или перемещаете ячейку в любую другую ячейку. Это делается путем привязки строки и столбца, поэтому она не изменяется при копировании или перемещении.

Относительно - относительная ссылка означает, что адрес ячейки изменяется при копировании или перемещении; то есть ссылка на ячейку относительно ее местоположения.

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

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

Давайте обратимся к этому предыдущему примеру - предположим, что в ячейке A1 есть формула, которая просто говорит = A2. Это означает, что вывод Excel в ячейке A1 все равно вводится в ячейку A2. В ячейке A2 мы набрали «A2», поэтому Excel отображает значение «A2» в ячейке A1.

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

Когда вы перемещаете ячейку вправо, число столбцов увеличивается. Когда вы перемещаете его, число строк увеличивается. Ячейка, на которую он указывает, ссылается на ячейку. Это показано ниже:

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

Мы скопировали ячейку с двумя столбцами вправо и четыре вниз. Это означает, что мы изменили ячейку, на которую ссылаются два и четыре вниз. A1 = A2 теперь C5 = C6. Вместо ссылки на A2, теперь ячейка C5 относится к ячейке C6.

Показанное значение равно 0, потому что ячейка C6 пуста. В ячейке C6 мы вводим «Я C6», и теперь C5 отображает «Я C6».

Пример: текстовая формула

Попробуем еще один пример. Помните из урока 2, где нам нужно было разделить полное имя на имя и фамилию? Что происходит, когда мы копируем эту формулу?

Напишите формулу = RIGHT (A3, LEN (A3) - НАЙТИ (",", A3) - 1) или скопируйте текст в ячейку C3. Не копируйте фактическую ячейку, только текст, скопируйте текст, иначе он обновит ссылку.

Вы можете отредактировать содержимое ячейки в верхней части электронной таблицы в поле рядом с местом, где указано «fx». Это поле больше, чем ячейка широко, поэтому ее легче редактировать.

Теперь у нас есть:

Ничего сложного, мы только что написали новую формулу в ячейку C3. Теперь скопируйте C3 в ячейки C2 и C4. Соблюдайте приведенные ниже результаты:

Используйте курсор для выделения ячеек C2, C3 и C4. Наведите курсор на ячейку B2 и вставьте содержимое. Посмотрите, что произошло - мы получим ошибку: «#REF». Почему это?

Когда мы скопировали ячейки из столбца C в столбец B, он обновил ссылку на один столбец слева = RIGHT (A2, LEN (A2) - FIND (",", A2) - 1).

Он изменил каждую ссылку на A2 на столбец слева от A, но слева от столбца A. нет столбца. Поэтому компьютер не знает, что вы имеете в виду.

Например, новая формула в B2 = RIGHT (#REF!, LEN (#REF!) - FIND (",", # REF!) - 1), а результат - #REF:

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

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

Под «диапазоном» мы подразумеваем более одной ячейки. Например, (C1: C10) означает все ячейки из ячейки C1 в ячейку C10. Таким образом, это столбец ячеек. Другой пример (A1: AZ1) - это верхняя строка из столбца A в столбец AZ.

Если диапазон пересекает пять столбцов и десять строк, вы указываете диапазон, записывая верхнюю левую ячейку и нижнюю правую, например, A1: E10. Это квадратная область, которая пересекает строки и столбцы, а не только часть столбца или части строки.

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

Теперь скопируйте формулу в ячейке C3 (= B3 + C2) в остальную часть столбца, чтобы получить текущий баланс для нашего бюджета. Excel обновляет ссылку на ячейку при ее копировании. Результат показан ниже:

Как вы можете видеть, каждое обновление новой ячейки родственник в новое место, поэтому ячейка C4 обновляет свою формулу до = B4 + C3:

Cell C5 обновляется до = B5 + C4 и так далее:

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

Абсолютная ссылка не изменяется при перемещении или копировании ячейки. Мы используем знак $, чтобы сделать абсолютную рекомендацию - помнить, что подумайте о знаке доллара как о якоре.

Например, введите формулу = $ A $ 1 в любую ячейку. $ Перед столбцом A означает не изменять столбец, $ перед строкой 1 означает, что не меняйте столбец при копировании или перемещении ячейки в любую другую ячейку.

Как вы можете видеть в приведенном ниже примере, в ячейке B1 мы имеем относительную ссылку = A1.Когда мы скопируем B1 в четыре ячейки ниже нее, относительная ссылка = A1 изменится на ячейку слева, поэтому B2 станет A2, B3 становятся A3 и т. д. Эти ячейки, очевидно, не имеют значения, поэтому выход равен нулю.

Однако, если мы используем = $ A1 $ 1, например, в C1, и мы копируем его в четыре ячейки ниже него, эта ссылка является абсолютной, поэтому она никогда не изменяется, а выход всегда равен значению в ячейке A1.

Предположим, что вы отслеживаете свой интерес, например, в приведенном ниже примере. Формула в C4 = B4 * B1 представляет собой «процентную ставку» * «баланс» = «процент в год».

Теперь вы изменили свой бюджет и сэкономили дополнительные 2 000 долларов, чтобы купить взаимный фонд. Предположим, что это фонд с фиксированной ставкой, и он платит ту же процентную ставку. Введите новую учетную запись и баланс в электронную таблицу, а затем скопируйте формулу = B4 * B1 из ячейки C4 в ячейку C5.

Новый бюджет выглядит следующим образом:

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

Excel выделяет ячейки, к которым ссылается формула. Вы можете видеть выше, что ссылка на процентную ставку (B1) перемещается в пустую ячейку B2. Мы должны были сделать ссылку на абсолют B1, написав $ B $ 1, используя знак доллара, чтобы привязать ссылку к строке и столбцу.

Перепишите первый расчет в C4 следующим образом: B4 * $ B $ 1, как показано ниже:

Затем скопируйте эту формулу с C4 на C5. Теперь электронная таблица выглядит следующим образом:

Поскольку мы скопировали формулу на одну ячейку вниз, т. Е. Увеличили строку на единицу, новая формула = B5 * $ B $ 1. Процентная ставка по взаимным фондам рассчитывается правильно, поскольку процентная ставка привязана к ячейке B1.

Это хороший пример того, когда вы можете использовать «имя» для ссылки на ячейку. Имя является абсолютной ссылкой. Например, чтобы присвоить ячейке B1 имя «процентная ставка», щелкните правой кнопкой мыши ячейку и выберите «define name».

Имена могут ссылаться на одну ячейку или диапазон, и вы можете использовать имя в формуле, например = interest rate * 8 - это то же самое, что и запись = $ B $ 1 * 8.

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

Смешанные ссылки - это когда или ряд или же столбец закреплен.

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

Введите стоимость за акр как = $ B2 * C2 в ячейке D2. Вы говорите, что хотите привязать цену за фунт. Затем скопируйте эту формулу в другие строки в том же столбце:

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

Мы добавим два столбца: «фунт семян в инвентаре», а затем «значение инвентаря». Теперь скопируйте ячейку D2 в F4 и обратите внимание, что ссылка на строку в первой части исходной формулы ($ B2) обновляется до строки 4, но столбец остается фиксированным, потому что $ привязывает его к «B.»

Это смешанная ссылка, потому что столбец является абсолютным, а строка относительна.

Циркулярные ссылки

Циркулярная ссылка - это когда формула относится к самому себе.

Например, вы не можете написать c3 = c3 + 1. Этот вид вычисления называется «итерацией», означающим, что он повторяется. Excel не поддерживает итерацию, потому что он вычисляет все только один раз.

Если вы попытаетесь сделать это, набрав SUM (B1: B5) в ячейке B5:

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

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

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

«Рабочая книга» представляет собой набор «рабочих листов». Проще говоря, это означает, что вы можете иметь несколько таблиц (рабочих листов) в одном файле Excel (книга). Как вы можете видеть в приведенном ниже примере, наша рабочая книга содержит много листов (красным цветом).

Рабочие листы по умолчанию называются Sheet1, Sheet2 и т. Д. Вы создаете новый, нажимая «+» в нижней части экрана Excel.

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

Или вы можете просто дважды щелкнуть по вкладке и переименовать ее.

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

Сегодняшняя дата
Курс конвертации валют от доллара к евро
Все, что имеет отношение ко всем листам в книге

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

Если мы посмотрим на лист «кредит», мы можем увидеть ссылку на сумму кредита:

Далее …

Мы надеемся, что теперь у вас есть четкое понимание ссылок на ячейки, включая относительные, абсолютные и смешанные. Там, безусловно, много.

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