Як закріпити клітинку у формулі Excel

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

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

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

Лист складається з клітинок. Кожен з них містить певну інформацію. Інші комірки можуть використовувати його в розрахунках. Але як вони розуміють, звідки взяти дані? Це допомагає їм створювати посилання.

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

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

1 спосіб

Щоб зберегти адреси стовпців і рядків, виконайте такі дії:

1. Клацніть клітинку, що містить формулу.
2. Клацніть на рядку формул потрібної нам комірки.
3. Натисніть F4.

Як наслідок, посилання на клітинку зміниться на абсолютне. Його можна впізнати за характерним знаком долара. Наприклад, якщо клацнути клітинку B2, а потім клавішу F4, посилання виглядатиме так: $B$2.

Що означає знак долара перед частиною адреси клітинки?

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

2 спосіб

Цей спосіб практично не відрізняється від попереднього, тільки потрібно двічі натиснути F4. наприклад, якщо у нас була клітинка B2, то після цього вона стане B$2. Простіше кажучи, таким чином вдалося виправити лінію. У цьому випадку літера стовпця зміниться.

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

3 спосіб

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

4 спосіб

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

Закріплення комірок для великого діапазону

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

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

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

1. Клітини.
2. Macro.
3. Функції різних типів.
4. Посилання та масиви.

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

1. Виберіть діапазон.
2. Відкрийте вкладку VBA-Excel, яка з’явиться після встановлення. 
3. Відкрийте меню «Функції», де знаходиться параметр «Блокувати формули».
   

   

4. Далі з'явиться діалогове вікно, в якому потрібно вказати необхідний параметр. Цей аддон дозволяє закріплювати стовпець і стовпець окремо, разом, а також видаляти вже існуюче закріплення за допомогою пакета. Після вибору необхідного параметра за допомогою відповідного перемикача необхідно підтвердити свої дії кнопкою «ОК».

Приклад

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

У нашому прикладі для цього потрібно ввести формулу =B2*C2. Це досить просто, як бачите. На її прикладі дуже легко описати, як можна виправити адресу клітинки або її окремого стовпця чи рядка. 

Звичайно, в цьому прикладі ви можете спробувати перетягнути формулу вниз за допомогою маркера автозаповнення, але в цьому випадку комірки будуть автоматично змінені. Отже, в комірці D3 буде інша формула, де цифри будуть замінені, відповідно, на 3. Далі за схемою – D4 – формула набуде вигляду = B4 * C4, D5 – аналогічно, але з номер 5 і так далі.

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

Припустимо, нам потрібно визначити доларовий дохід. Давайте помістимо його в клітинку B7. Трохи поностальгуємо і вкажемо вартість 35 рублів за долар. Відповідно, щоб визначити виручку в доларах, необхідно суму в рублях розділити на курс долара.

Ось як це виглядає на нашому прикладі.

Якщо ми, як і в попередньому варіанті, спробуємо прописати формулу, то не вдасться. Так само формула зміниться на відповідну. У нашому прикладі це буде так: =E3*B8. Звідси ми бачимо. що перша частина формули перетворилася на E3, і ми поставили перед собою це завдання, але нам не потрібно змінювати другу частину формули на B8. Тому нам потрібно перетворити посилання на абсолютне. Ви можете зробити це, не натискаючи клавішу F4, просто поставивши знак долара.

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

Сама формула буде виглядати так:

=D2/$B$7

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

Посилання на клітинки в макросах

Макрос — це підпрограма, яка дозволяє автоматизувати дії. На відміну від стандартного функціоналу Excel, макрос дозволяє відразу задати конкретну комірку і виконати певні дії всього за кілька рядків коду. Корисно для пакетної обробки інформації, наприклад, якщо немає можливості встановити надбудови (наприклад, використовується комп'ютер компанії, а не особистий).

Спочатку вам потрібно зрозуміти, що ключовим поняттям макросу є об’єкти, які можуть містити інші об’єкти. За електронну книгу (тобто документ) відповідає об’єкт Workbooks. Він містить об’єкт Sheets, який є сукупністю всіх аркушів відкритого документа. 

Відповідно клітини є об’єктом Cells. Він містить усі клітинки певного аркуша.

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

Наприклад, рядок коду, що містить посилання на клітинку C5, виглядатиме так:

Робочі зошити (“Book2.xlsm”). Аркуші (“List2”). Клітинки (5, 3)

Робочі зошити (“Book2.xlsm”). Аркуші (“List2”). Клітинки (5, “C”)

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

В цьому випадку лінія буде виглядати так.

Робочі зошити (“Book2.xlsm”). Аркуші (“List2”). Діапазон (“C5”)

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

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

Висновки

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