Абсолютні, відносні та змішані посилання на комірки в Excel

Робочий лист Excel складається з осередків. На ці осередки можна посилатися, вказавши значення рядка та значення стовпця.

Наприклад, A1 буде відноситися до першого рядка (позначеного як 1) і першого стовпця (позначеного як A). Так само B3 буде третім рядком і другим стовпцем.

Сила Excel полягає в тому, що ви можете використовувати ці посилання на осередки в інших осередках під час створення формул.

Тепер є три види посилань на комірки, які можна використовувати в Excel:

• Відносні посилання на комірки
• Абсолютні посилання на комірки
• Змішані посилання на комірки

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

Що таке відносні посилання на комірки в Excel?

Дозвольте мені на простому прикладі пояснити концепцію відносних посилань на комірки Excel.

Припустимо, у мене є набір даних, показаний нижче:

Щоб розрахувати загальну суму кожного елемента, нам потрібно помножити ціну кожного елемента на кількість цього елемента.

Для першого елемента формула в осередку D2 буде B2 * C2 (як показано нижче):

Тепер замість того, щоб вводити формулу для всіх осередків одну за одною, ви можете просто скопіювати комірку D2 і вставити її в інші комірки (D3: D8). Коли ви це зробите, ви помітите, що посилання на комірку автоматично налаштовується, щоб посилатися на відповідний рядок. Наприклад, формула в осередку D3 стає B3 * C3, а формула в D4 стає B4 * C4.

Ці посилання на комірки, які налаштовуються при копіюванні комірки, називаються відносні посилання на комірки в Excel.

Коли використовувати відносні посилання на комірки в Excel?

Відносні посилання на комірки корисні, коли вам потрібно створити формулу для діапазону осередків, і формула повинна посилатися на відносне посилання на комірку.

У таких випадках ви можете створити формулу для однієї комірки та скопіювати її у всі комірки.

Що таке абсолютні посилання на комірки в Excel?

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

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

Комісія становить 20% і вказана в осередку G1.

Щоб отримати розмір комісії за кожний продаж товару, використовуйте таку формулу в осередку E2 і скопіюйте її для всіх осередків:

= D2 * $ G $ 1

Зверніть увагу, що у посиланні на комірку є два знаки долара ($), в яких зазначена комісія: $г$2.

Що робить знак долара ($)?

Символ долара, доданий перед номером рядка і стовпця, робить його абсолютним (тобто. запобігає зміні номера рядка і стовпця при копіюванні до інших осередків).

Наприклад, у наведеному вище випадку, коли я копію формулу з комірки E2 в E3, вона змінюється з = D2 * $ G $ 1 = D3 * $ G $ 1.

Зверніть увагу, що поки D2 змінюється на D3, $G$1 не змінюється.

Оскільки ми додали символ долара перед G і ​​1 в G1, це не дозволить змінити посилання на комірку при її копіюванні.

Отже, це робить посилання на комірку абсолютною.

Коли використовувати абсолютні посилання на комірки в Excel?

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

Хоча ви також можете жорстко закодувати це значення у формулі (тобто використовувати 20% замість $ G $ 2), розміщення його в комірці і подальше використання посилання на комірку дозволяє вам змінити його в майбутньому.

Наприклад, якщо структура вашої комісії зміниться і ви тепер виплачуєте 25% замість 20%, ви можете просто змінити значення в осередку G2 і всі формули автоматично оновляться.

Що таке змішані посилання на комірки Excel?

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

Можуть бути два типи змішаних посилань на комірки:

• Рядок блокується, а стовпець змінюється при копіюванні формули.
• Стовпець блокується, а рядок змінюється під час копіювання формули.

Погляньмо, як це працює, на прикладі.

Нижче наведено набір даних, в якому вам необхідно розрахувати три рівні комісії на основі відсоткового значення в осередках E2, F2 та G2.

Тепер можна використовувати силу змішаного посилання для розрахунку всіх цих комісій за допомогою однієї формули.

Введіть наведену нижче формулу в комірку E4 та скопіюйте для всіх осередків.

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

Давайте проаналізуємо кожне посилання на осередок і зрозуміємо, як воно працює:

• $ B4 (і $ C4) - У цьому засланні знак долара стоїть прямо перед позначенням стовпця, але не перед номером рядка. Це означає, що при копіюванні формули в комірки праворуч посилання залишиться таким самим, як і стовпець. Наприклад, якщо ви скопіюєте формулу з E4 до F4, це посилання не зміниться. Однак, коли ви скопіюєте його, номер рядка зміниться, оскільки він не заблокований.
• 2 канадські долари - У цьому посиланні знак долара стоїть прямо перед номером рядка, а позначення стовпця немає знака долара. Це означає, що при копіюванні формули по осередках посилання не зміниться, оскільки номер рядка заблоковано. Однак, якщо ви скопіюєте формулу праворуч, алфавіт стовпця зміниться, оскільки він не заблокований.

Як змінити посилання з відносною на абсолютну (або змішану)?

Щоб змінити посилання з відносною на абсолютне, необхідно додати знак долара перед позначенням стовпця та номером рядка.

Наприклад, A1 - це відносне посилання на комірку, і вона стане абсолютною, коли ви зробите її $A$1.

Якщо у вас є тільки кілька посилань, які потрібно змінити, ви можете легко змінити ці посилання вручну. Таким чином, ви можете перейти до рядка формул і відредагувати формулу (або вибрати комірку, натиснути F2, а потім змінити її).

Однак швидший спосіб зробити це – використовувати поєднання клавіш – F4.

Коли ви вибираєте посилання на комірку (у рядку формул або в комірці в режимі редагування) та натискаєте F4, воно змінює посилання.

Припустимо, у вас є посилання = A1 у комірці.

Ось що відбувається, коли ви вибираєте посилання та натискаєте клавішу F4.

• Натисніть клавішу F4 один раз: Посилання на комірку зміниться з A1 на $A$1 (замість «відносної» стане «абсолютна»).
• Двічі натисніть клавішу F4: Посилання на комірку зміниться з A1 на A$1 (зміниться на змішане посилання, де рядок заблоковано).
• Тричі натисніть клавішу F4: Посилання на комірку зміниться з A1 на $A1 (зміниться на змішане посилання, де стовпець заблоковано).
• Натисніть клавішу F4 чотири рази: Посилання на комірку знову стає A1.

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

При написанні формули Excel знак $ у посиланнях на комірки збиває з пантелику багатьох користувачів. Але все пояснюється просто. Знак долара у посиланні на комірку Excel служить лише однієї мети - він вказує Excel, змінювати чи змінювати посилання при копіюванні формули до інших комірки. І в цьому короткому підручнику дається повна інформація про цю чудову функцію.

Важливість посилань на осередки Excel важко переоцінити. Дізнайтеся різницю між абсолютними, відносними та змішаними посиланнями, і ви будете на півдорозі до освоєння потужності та універсальності формул та функцій Excel.

Всі ви, напевно, бачили знак долара ($) у формулах Excel і запитували, що це таке. Дійсно, на ту саму комірку можна посилатися чотирма різними способами, наприклад, A1, $A$1, $A1 і A$1.

Знак долара у посиланні на комірку Excel впливає лише одну річ - він вказує Excel, як звертатися з посиланням під час переміщення чи копіювання формули до інших комірки. У двох словах, використання знака $ перед координатами рядка та стовпця створює абсолютне посилання на комірку, яка не змінюватиметься. Без знака $ посилання є відносною і змінюватиметься.

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

Що таке посилання на комірку Excel?

Простіше кажучи, посилання на комірку в Excel – це адреса комірки. Вона вказує на Microsoft Excel, де шукати значення, яке ви хочете використовувати у формулі.

Наприклад, якщо ви введете просту формулу =A1 у комірку C1, Excel перенесе значення з комірки A1 до C1:

Як уже говорилося, поки ви пишете формулу для окрема клітина Ви можете використовувати будь-який тип посилання, зі знаком долара ($) або без нього, результат буде однаковим:

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

Примітка. Крім Довідковий стиль A1 де стовпці визначаються літерами, а рядки – числами, також існують Довідковий стиль R1C1 де рядки та стовпці позначені цифрами (R1C1 позначає рядок 1, стовпець 1).

Оскільки A1 - це стиль посилань за промовчанням в Excel, і він найчастіше, у цьому підручнику ми розглянемо лише посилання типу A1. Якщо хтось зараз використовує стиль R1C1, ви можете вимкнути його, натиснувши кнопку Файл > Опції > Формули , а потім зняти прапорець Довідковий стиль R1C1 коробки.

Посилання на відносний осередок Excel (без знака $)

A відносне посилання в Excel - це адреса комірки без знака $ в координатах рядка та стовпця, наприклад A1 .

Коли формула з відносними посиланнями на комірки копіюється в іншу комірку, посилання змінюється на основі відносного положення рядків та стовпців. За промовчанням всі посилання в Excel є відносними. Наступний приклад показує, як відносні посилання.

Припустимо, у вас є наступна формула в осередку B1:

Якщо ви скопіюєте цю формулу в інший ряд в тому ж стовпці, скажімо, в комірці B2, формула коригуватиметься для рядка 2 (A2*10), оскільки Excel передбачає, що ви хочете помножити значення в кожному рядку стовпця A на 10.

Якщо ви скопіюєте формулу з відносним посиланням на комірку в інша колонка в одному і тому ж рядку, Excel змінить посилання на колонку відповідно:

І якщо ви скопіюєте або перемістите формулу Excel з відносним посиланням на комірку в ще один рядок і ще один стовпець обидва посилання на стовпці та рядки зміниться:

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

Використання відносного посилання на Excel - приклад формули

Припустимо, що у вашому робочому аркуші є колонка з цінами в доларах США (колонка B), і ви хочете конвертувати їх у євро. Знаючи курс конвертації долара США у євро (0,93 на момент написання статті), формула для рядка 2 виглядає так =B2*0.93 Зверніть увагу, що ми використовуємо відносне посилання на комірку Excel без знака долара.

При натисканні клавіші Enter формула буде розрахована, і результат одразу ж з'явиться у комірці.

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

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

Ось і все! Формула скопійована в інші осередки з відносними посиланнями, які правильно налаштовані для кожного окремого осередку. Щоб переконатися, що значення в кожному осередку розраховане правильно, виберіть будь-яку осередок і перегляньте формулу в рядку формул. У цьому прикладі я вибрав комірку C4 і бачу, що посилання на комірку у формулі відноситься до рядка 4, так, як і повинно бути:

Абсолютне посилання на комірку Excel (зі знаком $)

An абсолютне посилання в Excel - це адреса комірки зі знаком долара ($) у координатах рядка чи стовпця, наприклад $A$1 .

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

Наприклад, якщо у клітинці A1 у вас 10, і ви використовуєте символ абсолютне посилання на комірку ( $A$1 ), формула =$A$1+5 завжди повертатиме 15, незалежно від того, в які інші осередки буде скопійована ця формула. З іншого боку, якщо ви напишете ту саму формулу зі значенням відносне посилання на комірку ( A1 ), а потім скопіюйте його в інші осередки стовпця, для кожного рядка буде обчислено інше значення. Наступне зображення демонструє різницю:

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

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

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

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

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

Приклад 1. Відносні та абсолютні посилання на комірки для обчислення чисел

У нашому попередньому прикладі з цінами в доларах США та євро ви можете не вводити у формулу обмінний курс.Замість цього ви можете ввести це число в якусь комірку, наприклад, C1, і зафіксувати посилання на це комірку у формулі за допомогою знака долара ($), як показано на наступному знімку екрана:

У цій формулі (B4*$C$1) є два типи посилань на комірки:

• B4 - відносний посилання на комірку, яка коригується для кожного рядка, та
• $C$1 - абсолютний посилання на комірку, яка ніколи не змінюється незалежно від того, куди копіюється формула.

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

Приклад 2. Відносні та абсолютні посилання на комірки для обчислення дат

Іншим поширеним використанням абсолютних та відносних посилань на комірки в одній формулі є обчислення дат Excel на основі сьогоднішньої дати.

Припустимо, у вас є список дат доставки в стовпці B і ви ввели поточну дату в C1 за допомогою функції TODAY(). Ви хочете знати, через скільки днів кожен товар буде доставлений, і ви можете обчислити це за допомогою наступної формули: =B4-$C$1

І знову ми використовуємо два типи посилань у формулі:

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

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

Посилання на змішану комірку Excel

Змішане посилання на комірку в Excel - це посилання, в якому фіксується або буква стовпця, або номер рядка. Наприклад, $A1 та A$1 - це змішані посилання. Але що означає кожна з них? Все дуже просто.

Як ви пам'ятаєте, абсолютне посилання в Excel містить 2 знаки долара ($), які фіксують і стовпець, і рядок. У змішаному посиланні на комірку лише одна координата фіксована (абсолютна), а інша (відносна) змінюватиметься залежно від відносного положення рядка або стовпця:

• Абсолютний стовпець та відносний рядок Коли формула з таким типом посилання копіюється в інші осередки, знак $ перед літерою стовпця фіксує посилання на вказаний стовпець, щоб вона ніколи не змінювалася. Відносне посилання на рядок без знака долара залежить від рядка, в який копіюється формула.
• Відносний стовпець та абсолютний рядок Наприклад, $1. У цьому типі посилання посилання на рядок не зміниться, а посилання на стовпець зміниться.

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

Використання змішаного посилання на Excel - приклад формули

У цьому прикладі ми знову використовуватимемо таблицю конвертації валют. Але цього разу ми не обмежуватимемося лише конвертацією USD - EUR. Ми збираємося конвертувати доларові ціни в низку інших валют, все з єдина формула !

Для початку давайте введемо коефіцієнти конверсії в якийсь ряд, скажімо, в ряд 2, як показано на скріншоті нижче. А потім напишемо лише одну формулу для лівого верхнього осередку (C5 у цьому прикладі), щоб обчислити ціну в євро:

Де $B5 – доларова ціна в тому ж ряду, а C$2 – курс конвертації долара США в євро.

А тепер скопіюйте формулу до інших осередків стовпця C, а потім автоматично заповніть інші стовпці тією самою формулою, перетягнувши ручку заповнення. В результаті у вас буде 3 різних стовпця цін, правильно розрахованих на основі відповідного обмінного курсу у рядку 2 у тому ж стовпці. Щоб перевірити це, виберіть будь-яку комірку в таблиці та перегляньте формулу в рядку формул.

Наприклад, виділимо комірку D7 (у стовпці GBP). Тут ми бачимо формулу =$B7*D$2 який бере ціну в доларах США у B7 і множить її на значення в D2, яке є курсом конвертації USD-GBP, саме те, що лікар прописав :)

А тепер давайте розберемося, як виходить, що Excel точно знає, яку ціну взяти і який обмінний курс помножити. Як ви вже здогадалися, вся річ у змішаних посиланнях на осередки ($B5*C$2).

• $B5 - абсолютний стовпець та відносний рядок Тут ви додаєте знак долара ($) лише перед літерою стовпця, щоб прив'язати посилання до стовпця A, тому Excel завжди використовує вихідні ціни у доларах США для всіх перетворень. Посилання на рядок (без знака $) не фіксується, тому що ви хочете розрахувати ціни для кожного рядка окремо.
• C$2 - відносний стовпець та абсолютний рядок Оскільки всі курси валют перебувають у рядку 2, ви фіксуєте посилання рядок, ставлячи знак долара ($) перед номером рядка.І тепер, у який би рядок ви не копіювали формулу, Excel завжди шукатиме курс валют у рядку 2. А оскільки посилання на стовпець є відносною (без знака $), вона буде скоригована для стовпця, в який копіюється формула.

Як зробити посилання на цілий стовпець або рядок в Excel

Коли ви працюєте з листом Excel, який має змінну кількість рядків, ви можете захотіти послатися на всі осередки у певному стовпці. Щоб послатися на весь стовпець, просто введіть літеру стовпця двічі та двокрапку між ними, наприклад A:A .

Посилання на всю колонку

Крім посилань на комірки, посилання на весь стовпець може бути абсолютною і відносною, наприклад:

• Абсолютне посилання на стовпець , як $A:$A
• Посилання на відносний стовпець, наприклад, A:A

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

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

Посилання на весь ряд

Для звернення до всього рядка використовується той самий підхід, тільки замість букв стовпців вводяться номери рядків:

• Абсолютне посилання на рядок , наприклад, $1:$1
• Відносне посилання на ряд, наприклад, 1:1

Теоретично, ви також можете створити змішане посилання на весь стовпець або змішаний весь - посилання на ряд, наприклад, $A:A або $1:1 відповідно. Я говорю "в теорії", тому що не можу придумати практичного застосування таких посилань, хоча приклад 4 доводить, що формули з такими посиланнями працюють саме так, як повинні.

Приклад 1. Посилання на весь стовпець Excel (абсолютне та відносне)

Припустимо, у вас є кілька чисел у стовпці B, і ви хочете дізнатися їх загальне та середнє значення. Проблема в тому, що щотижня до таблиці додаються нові рядки, тому написання звичайної формули SUM() або AVERAGE() для фіксованого діапазону осередків не підходить. Натомість ви можете послатися на весь стовпець B:

=SUM($B:$B) - використовуйте знак долара ($), щоб зробити абсолютний посилання на весь стовпець яка фіксує формулу стовпці B.

=SUM(B:B) - напишіть формулу без $, щоб відносний посилання на весь стовпець які будуть змінюватися в міру копіювання формули до інших стовпців.

Порада. При написанні формули клацніть букву стовпця, щоб додати до формули посилання на весь стовпець. Як і у випадку з посиланнями на комірки, Excel за умовчанням вставляє відносне посилання (без знака $):

Так само ми напишемо формулу для розрахунку середньої ціни в усьому стовпці B:

У цьому прикладі ми використовуємо відносне посилання на весь стовпець, тому наша формула буде правильно скоригована при копіюванні її в інші стовпці:

Примітка. При використанні посилання на весь стовпець у формулах Excel ніколи не вводите формулу в будь-якому місці одного стовпця. Наприклад, може здатися гарною ідеєю ввести формулу =SUM(B:B) в одну з порожніх нижніх нижніх осередків стовпця B, щоб отримати підсумок в кінці того ж стовпця. Не робіть цього! Це створить так зване кругове посилання та формула поверне 0.

Приклад 2. Посилання на весь рядок в Excel (абсолютне та відносне)

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

= СЕРЕДНЕ($2:$2) - людина абсолютний посилання на весь ряд фіксується на певному рядку за допомогою символу долара ($).

=AVERAGE(2:2) - a відносний посилання на весь ряд змінюватиметься при копіюванні формули до інших рядків.

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

Приклад 3. Як послатися на весь стовпець, за винятком перших кількох рядків

Це дуже актуальна проблема, тому що досить часто перші кілька рядків у робочому аркуші містять вступні положення або пояснювальну інформацію, і ви не хочете включати їх до своїх обчислень. На жаль, Excel не дозволяє використовувати посилання типу B5:B, які включають усі рядки в стовпці B, починаючи з рядка 5. Якщо ви спробуєте додати таке посилання, ваша формула, швидше за все, поверне значення #NAME error.

Натомість ви можете вказати максимальний ряд Щоб ваше посилання включало всі можливі рядки в цьому стовпці. В Excel 2016, 2013, 2010 та 2007 максимум становить 1048576 рядків і 16384 стовпця. У попередніх версіях Excel максимум рядків становить 65 536, а стовпців - 256.

Отже, щоб знайти середнє значення для кожного стовпця цін у наведеній нижче таблиці (стовпці з B по D), введіть наступну формулу в комірку F2, а потім скопіюйте її в комірки G2 та H2:

Якщо ви використовуєте функцію SUM, ви також можете відняти рядки, які хочете виключити:

Приклад 4. Використання змішаного посилання на весь стовпець Excel

Як я вже згадував декількома абзацами раніше, в Excel можна також зробити змішане посилання на весь стовпець або весь рядок:

• Змішане посилання на стовпець, наприклад $A:A
• Посилання на змішаний ряд, наприклад $1:1

Тепер давайте подивимося, що станеться, якщо скопіювати формулу з такими посиланнями до інших осередків. Припустимо, що ви ввели формулу =SUM($B:B) у певному осередку, F2 у цьому прикладі. Коли ви копіюєте формулу в сусідній правий осередок (G2), вона змінюється на =SUM($B:C) тому що перший B зафіксований знаком $, а другий - ні. У результаті формула складе всі числа у стовпцях B і C. Не впевнений, що це має якусь практичну цінність, але ви можете захотіти дізнатися, як це працює:

Декілька слів застереження! Не використовуйте занадто багато посилань на цілі стовпці/рядки в робочому аркуші, оскільки вони можуть сповільнити роботу Excel.

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

Коли ви пишете формулу Excel, знак $ можна, звичайно, набрати вручну, щоб змінити відносне посилання на комірку на абсолютну або змішану. Або ви можете натиснути F4, щоб прискорити процес. Щоб поєднання клавіш F4 спрацювало, ви повинні перебувати в режимі редагування формули:

1. Виберіть комірку з формулою.
2. Увійдіть у режим редагування, натиснувши клавішу F2, або двічі клацніть на осередку.
3. Виберіть посилання на комірку, яку потрібно змінити.
4. Натисніть F4 для перемикання між чотирма типами посилань на комірки.

Якщо ви вибрали відносне посилання на комірку без знака $, наприклад, A1, то при натисканні клавіші F4 відбувається перемикання між абсолютним посиланням з обома знаками долара, наприклад, $A$1, абсолютним стовпцем $A1, а потім назад до відносного посилання A1.

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

Сподіваюся, тепер ви повністю розумієте, що таке відносні та абсолютні посилання на комірки, і формули Excel зі знаками $ більше не є загадкою. У наступних статтях ми продовжимо вивчати різні аспекти посилань на комірки Excel, такі як посилання на інший робочий лист, 3d-посилання, структуроване посилання, кругове посилання і т.д. А поки я дякую вам за прочитання і сподіваюся побачити вас у наступних статтях наш блог наступного тижня!

Michael Brown

Майкл Браун - захоплений технологічний ентузіаст, що прагне спростити складні процеси за допомогою програмних інструментів. Маючи більш ніж десятирічний досвід роботи в технологічній галузі, він відточив свої навички у Microsoft Excel та Outlook, а також у Google Sheets та Docs. Блог Майкла присвячений тому, щоб ділитися своїми знаннями та досвідом з іншими, надаючи прості поради та навчальні посібники для підвищення продуктивності та ефективності. Чи ви є досвідченим професіоналом або новачком, у блозі Майкла ви знайдете цінну інформацію та практичні поради, які допоможуть вам максимально ефективно використовувати ці важливі програмні інструменти.

Як у таблиці Excel використовувати змішані посилання

У попередній статті я порівнював використання абсолютних та відносних посилань на комірки. Ця рада розкриває додатковий тип посилання на комірку: у змішаному засланні або частина зі стовпцем, або частина з рядком є ​​абсолютною (і, отже, не змінюється при копіюванні та вставці формули).Змішані посилання на комірки використовуються нечасто, але, як ви дізнаєтеся з цієї статті, у деяких ситуаціях вони можуть значно полегшити вашу роботу.

Абсолютне посилання на комірку містить два знаки долара. Змішане посилання на комірку містить, для порівняння, лише один знак долара. Ось два приклади змішаних посилань: = А1 і = А $ 1 . У першому прикладі частина посилання, що відповідає стовпцю А, є абсолютною, а частина, що відповідає рядку 1, - відносною. У другому прикладі частина посилання, що відповідає стовпцю, - відносна, а частина, що відповідає рядку, - абсолютна. На рис. 70.1 показано таблицю, в якій використання змішаних посилань буде найоптимальнішим вибором. До речі, якщо ви готуєте реферат або курсову роботу, зверніть увагу на відгуки про компанію Заочник.

Мал. 70.1. Таблиця, в якій потрібно створити змішані посилання на комірки

Формули в таблиці розраховують площу для різних значень довжини та ширини. Формула в комірці С3 наступна: = $ В3 * С $ 2 . Обидві посилання на комірки є змішаними. Посилання на комірку В3 включає абсолютне посилання на стовпець $В, а посилання на комірку С2 включає абсолютне посилання на рядок $2. В результаті цю формулу можна копіювати вздовж і впоперек, і розрахунки будуть вірні. Наприклад, формула в комірці F7 така: = $ B7 * F $ 2 . Якби осередок С3 використовувала або абсолютні, або відносні посилання, копіювання формули призвело б до невірного результату.