Відносна і абсолютна посилання на клітинку і форматування

У цьому уроці ми обговорюємо посилання на комірку, як копіювати або переміщувати формулу, і форматувати клітини. Для початку, давайте уточнимо, що ми розуміємо під посиланнями на клітини, які є основою для більшої сили та універсальності формул і функцій. Конкретне розуміння того, як працюють посилання на клітини, дозволить вам отримати максимальну віддачу від ваших таблиць 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.

Тепер припустимо, що нам потрібно звільнити місце в нашій таблиці для отримання додаткових даних. Потрібно додати стовпці вище і рядки ліворуч, тому потрібно перемістити клітку вниз і вправо, щоб звільнити місце.

По мірі переміщення комірки праворуч число стовпців збільшується. Під час переміщення вниз номер рядка збільшується. Клітинка, на яку він вказує, посилання на клітинку, також змінюється. Це показано нижче:

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

Ми скопіювали клітку дві колони праворуч і чотири вниз. Це означає, що ми змінили стільницю, на яку вони посилаються, дві і чотири вниз. A1 = A2 зараз є C5 = C6. Замість того, щоб посилатися на A2, тепер клітина C5 відноситься до осередку C6.

Показане значення дорівнює 0, оскільки клітина C6 порожня. У камері C6 ми набираємо “Я C6” і тепер C5 відображає “Я C6”.

Приклад: Формула тексту

Давайте спробуємо інший приклад. Пам'ятайте з уроку 2, де ми повинні були розділити повне ім'я на ім'я та прізвище? Що відбувається, коли ми копіюємо цю формулу?

Напишіть формулу = ПРАВО (A3, LEN (A3) - FIND (“,”, A3) - 1) або скопіюйте текст у клітинку C3. Не копіюйте фактичну клітинку, тільки текст, копіюйте текст, інакше він буде оновлювати посилання.

Ви можете редагувати вміст комірки у верхній частині електронної таблиці у вікні, розташованому поруч із позначкою "fx". Це поле довше клітинки, тому його легше редагувати.

Тепер у нас є:

Нічого складного, ми тільки що написали нову формулу в клітинку C3. Тепер скопіюйте C3 до осередків C2 і C4. Дотримуйтесь наведених нижче результатів:

Тепер ми маємо перші імена Олександра Гамільтона і Томаса Джефферсона.

За допомогою курсора виділіть клітинки C2, C3 і C4. Наведіть курсор на клітинку B2 і вставте вміст. Подивіться, що сталося - ми отримуємо помилку: "#REF". Чому це так?

Коли ми скопіювали клітини з стовпця C у стовпець B, воно оновило посилання на один стовпчик ліворуч = ПРАВО (A2, LEN (A2) - FIND (“,”, A2) - 1).

Вона змінила кожне посилання на А2 до стовпця ліворуч від А, але ліворуч від стовпця А стовпця немає, так що комп'ютер не знає, що ви маєте на увазі.

Нова формула в 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 Ці осередки, очевидно, не мають введеного значення, тому вихідний сигнал дорівнює нулю.

Однак, якщо ми використовуємо = $ A1 $ 1, наприклад, у С1 і копіюємо його до чотирьох клітин нижче, то посилання є абсолютним, таким чином, він ніколи не змінюється, і вихід завжди дорівнює значенню в комірці 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, вибравши перейменування та ввівши нову назву.

Або просто двічі клацніть на вкладці та перейменуйте її.

Синтаксис для посилання на робочий аркуш = cellheet! Cell. Ви можете використовувати цей вид посилання, коли одне і те ж значення використовується в двох робочих таблицях, приклади яких можуть бути:

• Сьогоднішня дата
• Курс конвертації валют від доларів до євро
• Все, що стосується всіх листів у робочій книзі

Нижче наводиться приклад робочого аркуша «інтерес» з посиланням на робочий аркуш «позика», клітинка B1.

Якщо ми подивимося на «кредитний» аркуш, можна побачити посилання на суму кредиту:

Далі йде далі…

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

Саме для сьогоднішнього уроку, на уроці 4, ми обговоримо деякі корисні функції, які ви можете знати для щоденного використання Excel.