Формули - це життя та кров електронних таблиць Excel. І в більшості випадків вам не потрібна формула лише в одній клітці або в декількох клітинках.
У більшості випадків вам доведеться застосувати формулу до всього стовпця (або великий діапазон клітинок у стовпці).
Excel дає вам кілька різних способів зробити це за допомогою декількох клацань миші (або комбінації клавіш).
Давайте розглянемо ці методи.
Двічі натиснувши на ручку автозаповнення
Один із найпростіших способів застосувати формулу до всього стовпця-це використати цей простий трюк подвійного клацання мишею.
Припустимо, у вас є набір даних, як показано нижче, де потрібно розрахувати комісію за кожного представника з продажу у стовпці С (де комісія складе 15% вартості продажу у стовпці В).
Формула цього буде такою:
= В2*15%
Нижче наведено спосіб застосування цієї формули до всього стовпця C:
- У клітинці A2 введіть формулу: = B2*15%
- Вибравши клітинку, ви побачите маленький зелений квадрат у нижній правій частині виділення.
- Наведіть курсор на маленький зелений квадрат. Ви помітите, що курсор змінюється на знак плюс (це називається ручкою автозаповнення)
- Двічі клацніть лівою клавішею миші
Наведені вище кроки автоматично заповнюють весь стовпець до клітинки, де у вас є дані у сусідньому стовпці. У нашому прикладі формула буде застосована до комірки C15
Щоб це працювало, у сусідньому стовпці не повинно бути даних, і в ньому не повинно бути порожніх клітинок. Якщо, наприклад, у стовпці В є порожня клітинка (скажімо, комірка В6), то цей подвійний клік із автоматичним заповненням застосовуватиме формулу лише до комірки С5
Коли ви використовуєте маркер автозаповнення для застосування формули до всього стовпця, це еквівалентно копіюванню формули вручну. Це означає, що посилання на клітинку у формулі відповідно зміниться.
Наприклад, якщо це абсолютна посилання, вона залишатиметься такою, як є, поки формула застосовується до стовпця, додати, якщо це відносне посилання, то вона зміниться, коли формула буде застосована до комірок нижче.
Перетягуючи ручку автозаповнення
Однією з проблем із вищевказаним методом подвійного клацання є те, що він зупиниться, як тільки він виявить порожню клітинку в сусідніх стовпцях.
Якщо у вас невеликий набір даних, ви також можете перетягнути маркер заповнення вручну, щоб застосувати формулу до стовпця.
Нижче наведено кроки для цього.
- У клітинці А2 введіть формулу: = В2*15%
- Вибравши клітинку, ви побачите маленький зелений квадрат у нижній правій частині виділення
- Наведіть курсор на маленький зелений квадрат. Ви помітите, що курсор змінюється на знак плюс
- Утримуйте ліву клавішу миші та перетягніть її до комірки, де потрібно застосувати формулу
- Коли закінчите, залиште клавішу миші
Використання опції заповнення (вона на стрічці)
Інший спосіб застосувати формулу до всього стовпця - скористатися опцією заповнення на стрічці.
Щоб цей метод працював, спочатку потрібно виділити клітинки у стовпці, де потрібно мати формулу.
Нижче наведено кроки використання методу заповнення:
- У клітинці A2 введіть формулу: = B2*15%
- Виберіть усі клітинки, до яких потрібно застосувати формулу (включаючи клітинку С2)
- Натисніть вкладку Домашня сторінка
- У групі редагування натисніть на значок заповнення
- Натисніть «Заповнити»
Наведені вище кроки б взяли формулу з комірки С2 і заповнили її усіма виділеними клітинками
Додавання заповнення на панелі швидкого доступу
Якщо вам потрібно часто використовувати опцію заповнення, ви можете додати її на панель швидкого доступу, щоб використовувати її одним натисканням кнопки (і вона завжди видно на екрані).
T0 додайте його на панель швидкого доступу (QAT), перейдіть до опції «Заповнити», клацніть правою кнопкою миші на ній, а потім натисніть «Додати на панель швидкого доступу»
Тепер ви побачите піктограму "Заповнити" у QAT.
Використання комбінації клавіш
Якщо ви віддаєте перевагу використанню комбінацій клавіш, ви також можете скористатися комбінацією клавіш нижче, щоб досягти функціональності заповнення:
CONTROL + D (утримуйте клавішу управління, а потім натисніть клавішу D)
Нижче наведено кроки для використання комбінації клавіш для заповнення формули:
- У клітинці A2 введіть формулу: = B2*15%
- Виберіть усі клітинки, до яких потрібно застосувати формулу (включаючи клітинку С2)
- Утримуйте клавішу Control, а потім натисніть клавішу D.
Використання формули масиву
Якщо ви використовуєте Microsoft 365 і маєте доступ до динамічних масивів, ви також можете використовувати метод формули масиву, щоб застосувати формулу до всього стовпця.
Припустимо, у вас є набір даних, як показано нижче, і ви хочете розрахувати комісію у стовпці C.
Нижче наведена формула, яку можна використати:
= B2: B15*15%
Це формула масиву, яка повертає 14 значень у комірці (по одному для B2: B15). Але оскільки у нас є динамічні масиви, результат не обмежиться одноклітинкою, а перекинеться, щоб заповнити весь стовпець.
Зверніть увагу, що ви не можете використовувати цю формулу в кожному сценарії. У цьому випадку, оскільки наша формула використовує вхідне значення з сусіднього стовпця і як таку саму довжину стовпця, у якому ми хочемо отримати результат (тобто 14 клітинок), вона працює чудово тут.
Але якщо це не так, це може бути не найкращий спосіб скопіювати формулу до всього стовпця
За допомогою копіювання та вставлення комірки
Інший швидкий і відомий метод застосування формули до всього стовпця (або вибраних клітинок у всьому стовпці)-це просто скопіювати комірку, у якій є формула, і вставити її в ті клітинки у стовпці, де вам потрібна ця формула.
Нижче наведено кроки для цього.
- У клітинці A2 введіть формулу: = B2*15%
- Скопіюйте клітинку (використовуйте комбінацію клавіш Control + C у Windows або Command + C у Mac)
- Виберіть усі клітинки, до яких потрібно застосувати ту саму формулу (за винятком комірки C2)
- Вставте скопійовану клітинку (Control + V у Windows та Command + V у Mac)
Одна відмінність між цим методом копіювання-вставлення та всіма методами, перерахованими нижче, полягає в тому, що за допомогою цього методу ви можете вибрати лише вставити формулу (а не вставити будь-яке з форматування).
Наприклад, якщо клітинка С2 має синій колір клітинки, усі описані до цього часу методи (крім методу формули масиву) не тільки копіюватимуть та вставлятимуть формулу до всього стовпця, а й вставлятимуть форматування (наприклад, колір комірки) , розмір шрифту, жирний шрифт/курсив)
Якщо ви хочете застосувати лише формулу, а не форматування, виконайте наведені нижче дії.
- У клітинці A2 введіть формулу: = B2*15%
- Скопіюйте клітинку (використовуйте комбінацію клавіш Control + C у Windows або Command + C у Mac)
- Виділіть усі клітинки, до яких потрібно застосувати ту саму формулу
- Клацніть правою кнопкою миші виділення
- У параметрах, що з’являються, натисніть «Спеціальна вставка»
- У діалоговому вікні «Спеціальна вставка» натисніть на опцію Формули
- Натисніть OK
Вищезазначені кроки забезпечать копіювання лише формули у вибрані комірки (і жодне з форматування не додається до неї).
Отже, це деякі з швидких і простих методів, які можна використовувати для застосування формули до всього стовпця в Excel.
Сподіваюся, вам цей підручник був корисним!
