Перейти до основного матеріалу

Абсолютний довідник Excel (як зробити та використовувати)

Під час посилання на клітинку у формулі в Excel типовим типом посилання є відносне посилання. Ці посилання змінюватимуться, коли формулу буде скопійовано в інші клітинки на основі відповідних стовпців і рядків. Якщо ви хочете зберегти посилання постійним, незалежно від місця копіювання формули, вам потрібно використовувати абсолютне посилання.

Безкоштовно завантажте файл зразка зразок doc


Відео: Абсолютна довідка

 


Що таке абсолютне посилання

 

Абсолютне посилання — це тип посилання на клітинку в Excel.

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

Абсолютне посилання створюється шляхом додавання знака долара ($) перед посиланнями на стовпці та рядки у формулі. Наприклад, щоб створити абсолютне посилання для комірки A1, ви повинні представити її як $A$1.

Абсолютні посилання корисні, коли ви хочете посилатися на фіксовану клітинку або діапазон у формулі, яку буде скопійовано в кілька клітинок, але ви не бажаєте, щоб посилання змінювалося.

Наприклад, діапазон A4:C7 містить ціни продуктів, і ви хочете отримати податок, що підлягає сплаті для кожного продукту, на основі податкової ставки в клітинці B2.

Якщо ви використовуєте відносне посилання у формулі, як-от «=B5*B2», повертаються деякі неправильні результати, коли ви перетягуєте ручку автоматичного заповнення вниз, щоб застосувати цю формулу. Оскільки посилання на клітинку B2 буде змінюватися відносно клітинок у формулі. Тепер формула в клітинці C6 має вигляд «=B6*B3», а формула в клітинці C7 — «=B7*B4».

Але якщо ви використовуєте абсолютне посилання на клітинку B2 із формулою «=B5*$B$2», це гарантує, що ставка податку залишається незмінною для всіх клітинок, коли формулу перетягують вниз за допомогою ручки автоматичного заповнення, результати правильні.

Використання відносного посилання   Використання абсолютного посилання
абсолютне посилання на документ 3 1   абсолютне посилання на документ 4 1

Як зробити абсолютні посилання

 

Щоб створити абсолютне посилання в Excel, вам потрібно додати знаки долара ($) перед посиланнями на стовпці та рядки у формулі. Існує два способи створення абсолютного посилання:

Вручну додайте знаки долара до посилання на клітинку

Ви можете вручну додати знаки долара ($) перед посиланнями на стовпці та рядки, які потрібно зробити абсолютними під час введення формули в клітинку.

Наприклад, якщо ви хочете додати числа в комірках A1 і B1 і зробити обидва абсолютними, просто введіть формулу "=$A$1+$B$1". Це забезпечить незмінність посилань на клітинки під час копіювання або переміщення формули в інші клітинки.

Або якщо ви хочете змінити посилання в існуючій формулі в клітинці на абсолютні, ви можете вибрати клітинку, а потім перейти до панелі формул, щоб додати знаки долара ($).

Використання комбінації клавіш F4 для перетворення відносного посилання на абсолютне
  1. Двічі клацніть клітинку з формулою, щоб увійти в режим редагування;
  2. Помістіть курсор на посилання на клітинку, яке потрібно зробити абсолютним;
  3. прес F4 клавіша на клавіатурі для перемикання типів посилань, доки знаки долара не будуть додані перед посиланнями на стовпці та рядки;
  4. прес Що натомість? Створіть віртуальну версію себе у щоб вийти з режиму редагування та застосувати зміни.

Клавіша F4 може перемикати посилання між відносним посиланням, абсолютним посиланням і змішаним посиланням.

A1 → $A$1 → A$1 → $A1 → A1

абсолютне посилання f4 перемикач 1

Якщо ви хочете зробити всі посилання у формулі абсолютними, виділіть всю формулу в рядку формул і натисніть F4 клавіша для перемикання типів посилань, доки знаки долара не будуть додані перед посиланнями на стовпці та рядки.

A1+B1 → $A$1+$B$1 → A$1+B$1 → $A1+$B1 → A1+B1

абсолютне посилання f4 перемикач 2


Використовуйте абсолютне посилання з прикладами

 

У цій частині наведено 2 приклади, щоб показати, коли та як використовувати абсолютні посилання у формулі Excel.

Приклад 1. Обчислити відсоток від загальної суми

Припустімо, що у вас є діапазон даних (A3:B7), що містить продажі кожного фрукта, а клітинка B8 містить загальну кількість продажів цих фруктів, тепер ви хочете обчислити відсоток продажів кожного фрукта від загальної кількості.

Загальна формула для розрахунку відсотка від загального:

Percentage = Sale/Amount

Використовуйте відносне посилання у формулі, щоб отримати відсоток першого фрукта так:

=B4/B8

Коли перетягуєте ручку автоматичного заповнення вниз, щоб обчислити відсоток інших фруктів, #DIV/0! помилки будуть повернені.

Оскільки під час перетягування маркера автоматичного заповнення, щоб скопіювати формулу до клітинок нижче, відносне посилання B8 автоматично налаштовується на інші посилання на клітинки (B9, B10, B11) на основі їх відносного положення. А комірки B9, B10 і B11 порожні (нулі), коли дільник дорівнює нулю, формула повертає помилку.

Щоб виправити помилки, у цьому випадку вам потрібно зробити посилання на клітинку B8 абсолютним ($B$8) у формулі, щоб воно не змінювалося під час переміщення або копіювання формули куди завгодно. Тепер формулу оновлено до:

=B4/$B$8

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

Приклад 2 Пошук значення та повернення до відповідного значення відповідності

Припустимо, що ви хочете знайти список імен у D4:D5 і повернути їхні відповідні зарплати на основі імен співробітників і відповідної річної зарплати, наданої в діапазоні (A4:B8).

Загальна формула для пошуку:

=VLOOKUP(lookup_value, table_range, column_index, logical)

Якщо ви використовуєте відносне посилання у формулі для пошуку значення та повернення відповідного значення відповідності, як це:

=VLOOKUP(D4,A4:B8,2,FALSE)

Потім перетягніть ручку автоматичного заповнення вниз, щоб знайти значення нижче, буде повернено помилку.

Коли ви перетягуєте маркер заповнення вниз, щоб скопіювати формулу в клітинку нижче, посилання у формулі автоматично зменшуються на один рядок. У результаті посилання на діапазон таблиці, A4:B8, стає A5:B9. Оскільки «Lisa: неможливо знайти в діапазоні A5:B9, формула повертає помилку.

Щоб уникнути помилок, використовуйте абсолютне посилання $A$4:$B$8 замість відносного посилання A4:B8 у формулі:

=VLOOKUP(D4,$A$4:$B$8,2,FALSE)

Потім перетягніть маркер автозаповнення вниз, щоб отримати зарплату Лізи.


 

2 кліки, щоб пакетно зробити посилання на клітинки абсолютними за допомогою Kutools

 

Незалежно від того, вводите ви вручну чи використовуєте комбінацію клавіш F4, ви можете змінювати лише одну формулу за раз у Excel. Якщо ви хочете зробити посилання на клітинки в сотнях формул абсолютними в Excel, Перетворити реферали інструмент Kutools для Excel може допомогти вам впоратися з роботою в 2 кліки.

Виберіть клітинки формули, посилання на клітинки яких потрібно зробити абсолютними, клацніть Кутулс > Більше (fx) > Перетворити реферали. Потім виберіть До абсолютного варіант і клацніть Ok or Застосовувати. Тепер усі посилання на клітинки вибраних формул перетворено на абсолютні.

примітки:

Відносне посилання та змішане посилання

 

Окрім абсолютного посилання, існує ще два типи посилання: відносне посилання та змішане посилання.

Відносне посилання це типовий тип посилання в Excel, без знаків долара ($) перед посиланнями на рядки та стовпці. І коли формула з відносними посиланнями копіюється або переміщується в інші комірки, посилання автоматично змінюватимуться на основі їх відносного розташування.

Наприклад, якщо ви вводите формулу в клітинку, наприклад «=A1+1», а потім перетягуєте ручку автозаповнення вниз, щоб заповнити цю формулу в наступній клітинці, формула автоматично зміниться на «=A2+1».

Змішане посилання складається як з абсолютного посилання, так і з відносного посилання. Іншими словами, змішане посилання використовує знак долара ($), щоб фіксувати рядок або стовпець, коли формула копіюється або заповнюється.

Візьмемо для прикладу таблицю множення, у рядках і стовпцях перераховані числа від 1 до 9, які ви будете множити одне на одне.

абсолютне посилання на документ 15 1

Для початку ви можете використати формулу «=B3*C2» у клітинці C3, щоб помножити 1 у клітинці B3 на число (1) у першому стовпці. Однак, перетягнувши маркер автозаповнення вправо, щоб заповнити інші клітинки, ви помітите, що всі результати неправильні, крім першого.

абсолютне посилання на документ 16 1

Це пояснюється тим, що коли ви копіюєте формулу праворуч, позиція рядка не зміниться, але позиція стовпця змінюється з B3 на C3, D3 тощо. У результаті формули в правих клітинках (D3, E3, тощо) змінити на "=C3*D2", "=D3*E2" і так далі, якщо ви дійсно хочете, щоб вони були "=B3*D2", "=B3*E2" і так далі.

У цьому випадку вам потрібно додати знак долара ($), щоб заблокувати посилання на стовпець «B3». Використовуйте наступну формулу:

=$B3*C2

Тепер, коли ви перетягуєте формулу вправо, результати правильні.

абсолютне посилання на документ 17 1

Потім вам потрібно помножити число 1 у клітинці C2 на числа в рядках нижче.

Коли ви копіюєте формулу вниз, позиція стовпця клітинки C2 не зміниться, але позиція рядка змінюється з C2 на C3, C4 тощо. У результаті формули в клітинках нижче змінюються на «=$B4C3», "=$B5C4" тощо, що призведе до неправильних результатів.

абсолютне посилання на документ 18 1

Щоб вирішити цю проблему, змініть «C2» на «C$2», щоб посилання на рядок не змінювалося під час перетягування ручки автоматичного заповнення вниз для заповнення формул.

=$B3*C$2

абсолютне посилання на документ 19 1

Тепер ви можете перетягнути ручку автоматичного заповнення праворуч або вниз, щоб отримати всі результати.

абсолютне посилання на документ 20 1


Що запам'ятовується

 
  • Підсумок посилань на комірки

    тип Приклад Підсумки
    Абсолютна довідка $ 1 $ Ніколи не змінюйте, коли формула копіюється в інші комірки
    Відносна довідка A1 Посилання на рядок і стовпець змінюються залежно від відносної позиції, коли формула копіюється в інші клітинки
    Змішане посилання

    $A1/A$1

    Посилання на рядок змінюється, коли формула копіюється в інші клітинки, але посилання на стовпець фіксується/Посилання на стовпець змінюється, коли формула копіюється в інші клітинки, але посилання на рядок фіксується;
  • Як правило, абсолютні посилання ніколи не змінюються під час переміщення формули. Однак абсолютні посилання автоматично регулюватимуться, коли рядок або стовпець додається або видаляється зверху чи зліва на аркуші. Наприклад, у формулі «=$A$1+1», коли ви вставите рядок у верхній частині аркуша, формула автоматично зміниться на «=$A$2+1».

  • Команда F4 клавіша може перемикатися між відносним посиланням, абсолютним посиланням і змішаним посиланням.

Найкращі інструменти продуктивності офісу

🤖 Kutools AI Aide: Революціонізуйте аналіз даних на основі: Інтелектуальне виконання   |  Згенерувати код  |  Створення спеціальних формул  |  Аналізуйте дані та створюйте діаграми  |  Викликати функції Kutools...
Популярні функції: Знайдіть, виділіть або визначте дублікати   |  Видалити порожні рядки   |  Об’єднайте стовпці або клітинки без втрати даних   |   Раунд без Формули ...
Супер пошук: VLookup за кількома критеріями    Багатозначний VLookup  |   VLookup на кількох аркушах   |   Нечіткий пошук ....
Розширений розкривний список: Швидке створення випадаючого списку   |  Залежний спадний список   |  Виберіть розкривний список, що вибирається ....
Менеджер колонок: Додайте конкретну кількість стовпців  |  Перемістити стовпці  |  Перемкнути статус видимості прихованих стовпців  |  Порівняйте діапазони та стовпці ...
Особливості: Фокус сітки   |  Перегляд дизайну   |   Велика панель формул    Диспетчер робочих книг і аркушів   |  Бібліотека ресурсів (автотекст)   |  Вибір дати   |  Об’єднайте робочі аркуші   |  Шифрування/розшифрування клітинок    Надсилайте листи за списком   |  Супер фільтр   |   Спеціальний фільтр (фільтр жирний/курсив/закреслений...) ...
Топ-15 наборів інструментів12 текст Tools (додати текст, Видалити символи, ...)   |   50 + Графік типи (діаграма Ганта, ...)   |   40+ Практичний Формули (Розрахуйте вік на основі дня народження, ...)   |   19 вставка Tools (Вставте QR-код, Вставити зображення зі шляху, ...)   |   12 Перетворення Tools (Числа до слів, Валютна конверсія, ...)   |   7 Злиття та розділення Tools (Розширені комбіновані ряди, Розділені клітини, ...)   |   ... і більше

Покращуйте свої навички Excel за допомогою Kutools для Excel і відчуйте ефективність, як ніколи раніше. Kutools для Excel пропонує понад 300 додаткових функцій для підвищення продуктивності та економії часу.  Натисніть тут, щоб отримати функцію, яка вам найбільше потрібна...

Опис


Вкладка Office Передає інтерфейс із вкладками в Office і значно полегшує вашу роботу

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations