Як відняти в Excel (відняти клітинки, стовпець, дати/час)

Хоча Excel - це чудовий інструмент для аналізу даних, багато людей використовують його для базових арифметичних обчислень, таких як додавання, віднімання, множення та ділення.

Якщо ви новачок у програмі Excel і цікавитесь, як відняти в програмі Excel, ви опинилися в потрібному місці.

У цьому підручнику я покажу вам, як відняти в Excel (відняти клітинки, діапазони, стовпці тощо).

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

Тож давайте до цього!

Віднімання клітинок/значень у Excel

Почнемо з дуже простого прикладу, де у мене є два значення (скажімо 200 і 100), і я хочу відняти їх і отримати результат.

Ось як це зробити:

1. Виділіть клітинку, де потрібно відняти, і введіть знак рівності (=)
2. Введіть перше значення
3. Введіть знак віднімання (знак мінус -)
4. Введіть другий номер
5. Натисніть Enter

Вищезазначені кроки виконуватимуть обчислення у комірці та відображатимуть результат.

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

У наведеному вище прикладі ми жорстко кодували значення в комірці. Це означає, що ми вручну ввели значення 200 та 100 у клітинку.

Ви також можете використовувати подібну формулу, якщо у вас є ці значення у клітинках. У цьому випадку замість введення значень вручну можна скористатися посиланням на комірку.

Припустимо, у вас є два значення в комірці В1 і В2 (як показано нижче), і ви хочете відняти значення в комірці В2 від значення в комірці В1

Нижче наведена формула, яка дозволить це зробити:

= В1-В2

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

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

Віднімання значення з цілої колонки

Тепер перейдемо до трохи більш розширених обчислень віднімання.

У наведеному вище прикладі у нас було два значення, які ми хотіли відняти.

Але що, якщо у вас є список значень у стовпці, і ви хочете відняти одне конкретне значення з усього цього стовпця.

Знову ж таки, ви можете легко це зробити в Excel.

Припустимо, у вас є набір даних, як показано нижче, і ви хочете відняти значення 10 з кожної клітинки у стовпці А.

Нижче наведено кроки для цього.

1. У клітинку В2 введіть формулу: = А2-10

2. Копіювати клітинку В2
3. Виберіть клітинки від B3 до B12
4. Вставте скопійовану клітинку

Коли ви це зробите, Excel скопіює формулу в клітинку B2, а потім застосує її до всіх клітинок, куди ви вставили скопійовану клітинку.

А оскільки ми використовуємо посилання на клітинку у формулі (A2), Excel автоматично коригує посилання на клітинку, коли вона зменшується.

Наприклад, у комірці В3 формула набуде значення = А3-10, а в комірці В4 формула стане А4-10.

Якщо використовується Microsoft 365

Спосіб, який я розглянув вище, працюватиме з усіма версіями Excel, але якщо ви використовуєте Microsoft 365, то ви можете використовувати ще простішу формулу.

Припустимо, у вас однакові дані (з даними у стовпці А), і ви хочете відняти 10 з кожної комірки, ви можете скористатися наведеною нижче формулою у комірці В2:

= A2: A12-10

Це воно!

Вам не потрібно турбуватися про копіювання та вставлення формули в інші клітинки, оскільки Excel про це подбає.

Це називається формулою динамічного масиву, де результат повертає не одне єдине значення, а масив значень. І ці значення потім переносяться на інші клітинки у стовпці

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

Віднімання значення клітинки з цілої колонки

У наведеному вище прикладі я відняв 10 з кількох клітинок у стовпці.

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

Припустимо, у вас є набір даних, як показано нижче, де потрібно відняти значення в комірці D2 з усіма клітинками у стовпці А.

Нижче наведено кроки для цього.

1. У комірку С2 введіть формулу: = A2- $ D $ 2

2. Копіювати клітинку С2
3. Виберіть клітинку від C3 до C12
4. Вставте скопійовану клітинку

У цьому прикладі я використав формулу A2- $ D $ 2, яка гарантує, що під час копіювання формули для всіх інших клітинок у стовпці С значення, яке я віднімаю, залишається незмінним, тобто клітинка В2.

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

Коли ви скопіюєте цю формулу в клітинку C3, вона стане A3- $ D $ 2, а в клітинку C4-A4- $ D $ 2.

Отже, хоча перша частина посилання постійно змінюється з A3 на A4, коли ми копіюємо її, абсолютна посилання не змінюється,

Це дозволяє нам відняти однакове значення з усіх клітинок у стовпці А.

Якщо використовується Microsoft 365

Якщо ви використовуєте Microsoft 365 і маєте доступ до динамічних масивів, ви також можете скористатися формулою нижче:

= A2: A12-D2

З динамічними масивами вам не потрібно турбуватися про зміну посилань. Вона сама про це подбає.

Віднімання декількох клітин з однієї клітини

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

Припустимо, у вас є набір даних, як показано нижче, і ви хочете відняти всі значення у стовпці В від значення у комірці А2.

Нижче наведено кроки для цього.

1. У клітинці С2 введіть формулу: = $ A $ 2-B2
2. Копіювати клітинку С2
3. Виберіть клітинку від C3 до C12
4. Вставте скопійовану клітинку

Логіка, яка використовується тут, точно така ж, як і вище, де я заблокував посилання $ A $ 2, додавши знак $ перед алфавітом стовпця та номером рядка.

Таким чином, посилання на клітинку $ A $ 2 не змінюється, коли ми копіюємо її у стовпці С, але друга посилання формули (В2) постійно змінюється, коли ми спускаємося по комірці.

Якщо використовується Microsoft 365

Якщо ви використовуєте Microsoft 365 і маєте доступ до динамічних масивів, ви також можете скористатися формулою нижче:

= A2-B2: B12

Віднімання клітинок у двох стовпцях

У більшості практичних випадків у вас буде два стовпці, де потрібно відняти клітинки в кожному стовпці (в одному рядку) і отримати результат.

Наприклад, припустимо, що у вас є значення доходів та витрат у двох стовпцях, і ви хочете обчислити чистий дохід (що є різницею між доходами та витратами)

Ось як це зробити:

1. У клітинку С2 введіть формулу: = В2-С2

2. Копіювати клітинку С2
3. Виберіть клітинку від C3 до C12
4. Вставте скопійовану клітинку

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

Якщо використовується Microsoft 365

Спосіб, який я розглянув вище, працюватиме з усіма версіями Excel, але якщо ви використовуєте Microsoft 365, то ви можете використовувати ще простішу формулу.

Припустимо, що у вас однакові дані, і ви хочете відняти два стовпці, можна скористатися формулою нижче:

= B2: B11-C2: C11

Зауважте, що це стало можливим, оскільки Excel у Microsoft 365 має щось, що називається динамічними масивами. Якщо у вас їх немає, то вона не зможе використовувати цю формулу.

Відняти дати в Excel

Значення дат і часу зберігаються як числа у задній частині програми Excel.

Наприклад, 44197 представляє дату 01 січня 2021 року, а 44198 - дату 2 січня 2021 року.

Це дозволяє нам легко відняти дати в Excel і знайти різницю.

Наприклад, якщо у вас є дві дати, ви можете відняти ці дані і дізнатися, скільки днів пройшло між цими двома датами.

Припустимо, у мене є набір даних, як показано нижче, де у мене є "Дата початку" та "Дата завершення", і я хочу дізнатися кількість днів між цими двома датами.

Проста формула віднімання дасть мені результат.

= В2-А2

Існує ймовірність, що Excel надасть вам результат у форматі дати замість числа, як показано вище.

Це іноді трапляється, коли Excel намагається бути корисним і вибирає формат із сусіднього стовпця.

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

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

Детальніше про віднімання дат у Excel можна прочитати тут

Відняти час в Excel

Як і дати, навіть значення часу зберігаються як числа в Excel.

У той час як денна частина дати буде представлена ​​цілим числом, частина часу буде представлена ​​десятковим числом.

Наприклад, 44197,5 означатиме 01 січня 2021 12:00, а 44197,25 - 01 січня 2021 09:00

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

А оскільки це числа, їх можна легко відняти.

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

Ось формула, яка дасть нам різницю між цими значеннями часу:

= В2-А2

Існує велика ймовірність того, що Excel змінить формат стовпця результату, щоб відобразити різницю як значення часу (наприклад, вона може показувати вам 9:00 замість 0,375). Ви можете легко змінити це, перейшовши на вкладку Домашня сторінка та вибравши Загальні зі спадного меню формату.

Зауважте, що у випадку часу ви отримаєте значення у десяткових дробах, але якщо ви хочете, щоб це було в годинах, хвилинах та секундах, ви можете зробити це, помноживши десяткове значення на 24 (для отримання годин) або 24*60 для отримання хвилин і 24*60*60 для отримання секунд.

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

= (B2-A2)*24

Відняти відсотки в Excel

Віднімання відсотків від числа в Excel трохи відрізняється від віднімання двох цілих чисел після коми.

Припустимо, у вас є два відсотки (як показано нижче), і ви хочете відняти одне від іншого, ви можете використовувати просту формулу віднімання

Але якщо ви хочете відняти відсоткове значення з непроцентного значення, вам потрібно зробити це інакше.

Припустимо, у клітині В1 є 100, і ви хочете відняти 20% від цього значення (тобто від 100 відняти 20% від 100).

У цьому випадку ви можете скористатися наведеною нижче формальною формою:

= B1*(1-20%)

Ви також можете скористатися формулою нижче:

= B1-B1*20%

Якщо у клітинці є значення відсотка, можна також використати посилання на клітинку. Наприклад, якщо у вас є набір даних, як показано нижче, і ви хочете відняти значення відсотка у комірці В2 від числа у В1.

Тоді можна скористатися формулою нижче:

= B1-B1*B2

Віднімаємо за допомогою спеціальної вставки

І, нарешті, ви також можете скористатися спеціальним трюком Paste для віднімання в Excel.

Це корисно, коли потрібно швидко відняти певне значення з усього стовпця.

Припустимо, у вас є набір даних, як показано нижче, і ви хочете відняти 100 з кожного з цих чисел.

Нижче наведено кроки для цього.

1. У порожній клітинці введіть значення, яке потрібно відняти від усього стовпця. У цьому прикладі я введу це значення в клітинку D1

2. Скопіювати клітинку D1 (це клітинка, де ви ввели це значення, яке потрібно відняти)

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

5. У спеціальному діалоговому вікні виберіть значення як параметр Вставити

6. У розділі Операції виберіть Віднімання

7. Натисніть OK
8. Видаліть значення, яке ви ввели в клітинку на кроці 1

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

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

Отже, це різні способи використання Excel відняти значення/відсотки, клітинки та стовпці.

Розуміння цих основних концепцій допоможе вам найбільш ефективно використовувати інструмент електронних таблиць Excel.

Сподіваюся, цей підручник був вам корисним.