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


 

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



Інструкція

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

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

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


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

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

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


  3. Однак при такому записі, якщо ви збираєтеся додавати або видаляти рядки / стовпці у вихідній книзі перед першою осередком потрібного діапазону, в кінцевій книзі значення у формулі будуть змінюватися. При вставці порожніх рядків вище вихідної комірки, замість другого доданка у формулі кінцевої книги виявляться нулі. Щоб цього не відбувалося, книги потрібно зв’язати між собою.
  4. Для цього в кінцеву книгу доведеться додати стовпець зв’язку. Відкрийте вихідну книгу та виберіть у ній комірку, значення якої має бути зафіксовано незалежно від операцій з таблицею. Скопіюйте це значення в буфер обміну. Перейдіть на лист в кінцевій книзі, де буде міститися формула.
  5. У меню «Правка» виберіть пункт «Спеціальна вставка» і у вікні, натисніть кнопку «Вставити зв’язок». За замовчуванням в клітинку буде вписано вираження у форматі:

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

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


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

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

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


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