Використання обчислюваних стовпців у таблиці Excel

Обчислювані стовпці в таблицях Excel – це фантастичний засіб для ефективного введення формул. Вони дають змогу ввести одну формулу в одну клітинку, а потім вона автоматично розшириться до решти стовпця окремо. Не потрібно використовувати команди Заливка або Копіювання. Це може бути неймовірно економія часу, особливо якщо у вас багато рядків. І те ж саме відбувається, коли ви змінюєте формулу; зміни також розширяться до решти обчислюваного стовпця.

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

Створення обчислюваного стовпця

Створення таблиці. Якщо ви не знайомі з таблицями Excel, докладні відомості див. в статті Огляд таблиць Excel.
Вставте новий стовпець у таблицю. Це можна зробити, ввівши стовпець одразу праворуч від таблиці, і Excel автоматично розширить таблицю. У цьому прикладі ми створили новий стовпець, ввівши "Загальний підсумок" у клітинку D1.
Поради.:
- Стовпець таблиці також можна додати на вкладці Основне . Просто клацніть стрілку поруч із кнопкою Вставити > Вставити стовпці таблиці ліворуч.
Введіть потрібну формулу та натисніть клавішу Enter.

У цьому випадку ми ввели =sum(, а потім вибрали стовпці "Кв. 1 " і "Кв. 2 ". У результаті Програма Excel побудувала формулу =SUM(Таблиця1[@[Кв. 1]:[Кв. 2]])). Це називається формулою структурованого посилання , унікальною для таблиць Excel. Структурований формат посилань дає змогу таблиці використовувати однакову формулу для кожного рядка. Звичайною формулою Excel для цього буде формула =SUM(B2:C2), яку потім потрібно скопіювати або заповнити до решти клітинок у стовпці.

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

Примітки.:

Якщо скопіювати або розповсюдити формулу на всі клітинки в пустому стовпці таблиці, також буде створено обчислюваний стовпець.
Якщо ввести або перемістити формулу в стовпець таблиці, який уже містить дані, обчислюваний стовпець не створиться автоматично. Однак відображається кнопка Параметри автовиправлення , яка дає змогу перезаписати дані, щоб створити обчислюваний стовпець.
Якщо ввести нову формулу, відмінну від наявних формул в обчислюваному стовпці, стовпець автоматично оновиться новою формулою. Ви можете скасувати оновлення та зберегти лише одну нову формулу з кнопки Параметри автовиправлення . Зазвичай це не рекомендовано, оскільки він може перешкоджати автоматичному оновленню стовпця в майбутньому, оскільки не знатиме, яку формулу потрібно розширити після додавання нових рядків.
Якщо ви ввели або скопіювали формулу в клітинку пустого стовпця та не хочете зберігати новий обчислюваний стовпець, двічі натисніть кнопку Скасувати . Також можна двічі натиснути клавіші Ctrl+Z

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

Сповіщення про неузгоджену формулу в таблиці Excel

Примітка.: Виняткові ситуації, пов’язані з обчислюваними стовпцями, виникають у таких випадках:

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

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

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

Сповіщення про помилку з'явиться, лише якщо ввімкнуто фонову перевірку помилок. Якщо повідомлення про помилку не відображається, перейдіть до розділу Параметри> >формул, > переконайтеся, що встановлено прапорець Увімкнути фонову перевірку помилок.

Якщо ви використовуєте Excel 2007, натисніть кнопку Office , а потім – Параметри Excel > Формули.
Якщо використовується комп'ютер Mac, у рядку меню відкрийте Excel і виберіть пункт Параметри > Формули & Списки > перевірка помилок.

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

Увімкнення або вимкнення обчислюваних стовпців
1. На вкладці Файл виберіть пункт Параметри.
  
  Якщо ви використовуєте Excel 2007, натисніть кнопку Office , а потім – Параметри Excel.
2. Виберіть категорію Правопис.
3. У розділі Параметри автовиправлення натисніть кнопку Параметри автовиправлення.
4. Перейдіть на вкладку Автоформат під час введення.
5. У розділі Автоматично під час роботи встановіть або зніміть прапорець Заповнювати формули в таблицях, щоб створювати обчислювані стовпці , щоб увімкнути або вимкнути цей параметр.
  
  Порада.: Ви також можете натиснути кнопку Параметри автовиправлення , яка відображається в стовпці таблиці після введення формули. Натисніть кнопку Параметри автовиправлення елемента керування, а потім зніміть прапорець Заповнювати формули в таблицях, щоб створювати обчислювані стовпці, щоб вимкнути цей параметр.
Якщо ви використовуєте комп'ютер Mac, у головному меню виберіть Excel, а потім – Параметри > Формули та Списки > Таблиці & Фільтри > Автоматично заповнювати формули.
Припинення автоматичного створення обчислюваних стовпців

Ввівши першу формулу в стовпець таблиці, натисніть кнопку Параметри автовиправлення , яка з'явиться, а потім виберіть пункт Припинити автоматичне створення обчислюваних стовпців.

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