ЯК в Excel закріпити клітинку у формулі :: як зафіксувати значення у формулі при автозаповнення в excel

Формули в MS Excel за замовчуванням є «легкими». Це означає, наприклад, що при автозаповнення осередків по стовпці у формулі буде автоматично змінюватися ім'я рядка. Те ж саме відбувається з ім'ям стовпця при автозаповнення рядка. Щоб цього уникнути, достатньо поставити знак $ у формулі перед обома координатами осередки. Однак при роботі з цією програмою досить часто ставляться завдання складніше.

Інструкція

У найпростішому випадку, якщо формула використовує дані з однієї книги, при вставці функції в полі введення значень запишіть координати фіксованої комірки в форматі $ A $ 1. Наприклад, вам необхідно підсумувати значення по стовпці B1: B10 зі значенням в комірці А3. Тоді в рядку функцій запишіть формулу в наступному форматі:

= СУММ ($ A $ 3-B1).

Тепер при автозаповнення буде змінюватися тільки ім'я рядки другого доданка.

Аналогічним способом можна підсумувати дані з двох різних книг. Тоді у формулі треба буде вказати повний шлях до осередку закритої книги у форматі:

=СУММ($A$3-'Имя_диска:Каталог_пользователяИмя_пользователяИмя_папки[Имя_файла.xls]Лист1'!А1).

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

= СУММ ($ A $ 3- [Імя_файла.xls] Лист1! А1).

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

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

В меню «Правка» виберіть пункт «Спеціальна вставка» і у вікні, натисніть кнопку «Вставити зв'язок». За умовчанням в клітинку буде вписано вираз у форматі:

= [Кніга2.xls] Лист1! $ А $ 1.

Однак цей вираз буде виводитися тільки в рядку формул, а в самій комірці буде вписано його значення. Якщо вам необхідно пов'язати кінцеву книгу з варіаційним рядом з вихідної, приберіть знак $ із зазначеної формули.

Тепер в наступному стовпці вставте формулу підсумовування в звичайному форматі:

= СУММ ($ A $ 1-B1),

де $ A $ 1 - адреса фіксованою осередку в кінцевій книзі;
В1 - адреса комірки, що містить формулу зв'язку з початком варіаційного ряду іншої книги.

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