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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Теперь у нас есть имена Александра Гамильтона и Томаса Джефферсона.

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

Когда мы скопировали ячейки из столбца C в столбец B, он обновил ссылочный столбец слева = ВПРАВО (A2, LEN (A2) - НАЙТИ («,», 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:

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

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

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

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

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

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

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

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

Новый бюджет выглядит так:

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

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

Перепишите первое вычисление в C4 так, чтобы оно показывало = B4 * $ B $ 1, как показано ниже:

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

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

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

Имена могут относиться к одной ячейке или диапазону, и вы можете использовать имя в формуле, например = 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.