ячейки, столбцы, проценты, даты и время
В учебнике показано, как выполнять вычитание в Excel с помощью знака минус и функции СУММ. Вы также научитесь вычитать ячейки, целые столбцы, матрицы и списки.
Вычитание — одно из четырех основных арифметических действий, и каждый ученик начальной школы знает, что для вычитания одного числа из другого используется знак минус. Этот старый добрый метод работает и в Excel. Какие вещи вы можете вычитать в своих рабочих листах? Просто любые вещи: числа, проценты, дни, месяцы, часы, минуты и секунды. Вы даже можете вычитать матрицы, текстовые строки и списки. Теперь давайте посмотрим, как вы можете все это сделать.
Формула вычитания в Excel (формула минус)
Для ясности функция ВЫЧИТАНИЕ в Excel не существует. Чтобы выполнить простую операцию вычитания, вы используете знак минус (-).
Базовая формула вычитания в Excel проста:
знак равнономер 1–номер 2
Например, чтобы вычесть 10 из 100, напишите приведенное ниже уравнение и получите в результате 90:
=100-10
Чтобы ввести формулу на лист, выполните следующие действия:
- В ячейке, в которой вы хотите отобразить результат, введите знак равенства (=).
- Введите первое число, затем знак минус, а затем второе число.
- Завершите формулу, нажав клавишу Enter.
Как и в математике, вы можете выполнять более одной арифметической операции в рамках одной формулы.
Например, чтобы вычесть несколько чисел из 100, введите все эти числа, разделенные знаком минус:
=100-10-20-30
Чтобы указать, какая часть формулы должна быть вычислена первой, используйте круглые скобки. Например:
=(100-10)/(80-20)
На скриншоте ниже показаны еще несколько формул для вычитания чисел в Excel:
Как вычесть ячейки в Excel
Чтобы вычесть одну ячейку из другой, вы также используете минус формула но укажите ссылки на ячейки вместо фактических чисел:
знак равноячейка_1 – ячейка_2
Например, чтобы вычесть число в B2 из числа в A2, используйте следующую формулу:
=А2-В2
Вам не обязательно вводить ссылки на ячейки вручную, вы можете быстро добавить их в формулу, выбрав соответствующие ячейки. Вот как:
- В ячейке, где вы хотите вывести разницу, введите знак равенства (=), чтобы начать формулу.
- Нажмите на ячейку, содержащую уменьшаемое число (число, из которого нужно вычесть другое число). Его ссылка будет добавлена в формулу автоматически (A2).
- Введите знак минус (-).
- Щелкните ячейку, содержащую вычитаемое (число, которое необходимо вычесть), чтобы добавить его ссылку в формулу (B2).
- Нажмите клавишу Enter, чтобы завершить формулу.
И у вас будет результат, подобный этому:
Как вычесть несколько ячеек из одной ячейки в Excel
Чтобы вычесть несколько ячеек из одной и той же ячейки, вы можете использовать любой из следующих методов.
Способ 1. Знак минус
Просто введите несколько ссылок на ячейки, разделенные знаком минус, как мы делали при вычитании нескольких чисел.
Например, чтобы вычесть ячейки B2:B6 из B1, создайте формулу следующим образом:
=B1-B2-B3-B4-B5-B6
Способ 2. Функция СУММ
Чтобы сделать формулу более компактной, сложите вычитаемые (B2:B6) с помощью функции СУММ, а затем вычтите сумму из уменьшаемого (B1):
=B1-СУММ(B2:B6)
Способ 3. Суммировать отрицательные числа
Как вы помните из курса математики, вычесть отрицательное число — это то же самое, что сложить его. Итак, сделайте все числа, которые вы хотите вычесть, отрицательными (для этого просто введите знак минус перед числом), а затем используйте функцию СУММ, чтобы сложить отрицательные числа:
=СУММ(B1:B6)
Как вычесть столбцы в Excel
Чтобы вычесть 2 столбца построчно, напишите формулу минуса для самой верхней ячейки, а затем перетащите маркер заполнения или дважды щелкните значок плюса, чтобы скопировать формулу во весь столбец.
В качестве примера давайте вычтем числа в столбце C из чисел в столбце B, начиная со строки 2:
=В2-С2
Из-за использования относительных ссылок на ячейки формула будет правильно корректироваться для каждой строки:
Вычесть одно и то же число из столбца чисел
Чтобы вычесть одно число из диапазона ячеек, введите это число в какую-нибудь ячейку (в данном примере F1) и вычтите ячейку F1 из первой ячейки в диапазоне:
=B2-$F$1
Ключевым моментом является блокировка ссылки на вычитаемую ячейку с помощью знака $. Это создает абсолютную ссылку на ячейку, которая не изменяется независимо от того, куда копируется формула. Первая ссылка (B2) не заблокирована, поэтому она меняется для каждой строки.
В результате в ячейке C3 у вас будет формула =B3-$F$1; в ячейке C4 формула изменится на =B4-$F$1 и так далее:
Если дизайн вашего рабочего листа не позволяет разместить дополнительную ячейку для вычитаемого числа, ничто не мешает вам жестко закодировать ее непосредственно в формуле:
=В2-150
Как вычесть проценты в Excel
Если вы хотите просто вычесть один процент из другого, вам поможет уже знакомая формула минуса. Например:
=100%-30%
Или вы можете ввести проценты в отдельные ячейки и вычесть эти ячейки:
=А2-В2
Если вы хотите вычесть процент из числа, т.е. уменьшить число на процент, то используйте эту формулу:
знак равноЧисло * (1 – %)
Например, вот как можно уменьшить число в A2 на 30%:
=А2*(1-30%)
Или вы можете ввести процент в отдельную ячейку (например, B2) и ссылаться на эту ячейку, используя абсолютную ссылку:
=A2*(1-$B$2)
Для получения дополнительной информации см. Как рассчитать процент в Excel.
Как вычесть даты в Excel
Самый простой способ вычитания дат в Excel — ввести их в отдельные ячейки и вычесть одну ячейку из другой:
знак равноДата окончания – Дата начала
Вы также можете указать даты непосредственно в своей формуле с помощью функции ДАТА или ДАТАЗНАЧ. Например:
=ДАТА(2018,2,1)-ДАТА(2018,1,1)
=ДАТАЗНАЧ(“1/2/2018”)-ДАТАЗНАЧ(“1/1/2018”)
Более подробную информацию о вычитании дат можно найти здесь:
Как вычесть время в Excel
Аналогично строится формула вычитания времени в Excel:
знак равноВремя окончания–Время начала
Например, чтобы получить разницу между временем в A2 и B2, используйте эту формулу:
=А2-В2
Чтобы результат отображался правильно, обязательно примените формат времени к ячейке формулы:
Вы можете добиться того же результата, указав значения времени непосредственно в формуле. Чтобы Excel правильно понял время, используйте функцию ВРЕМЗНАЧ:
=ВРЕМЗНАЧ(“16:30”)-ВРЕМЗНАЧ(“12:00”)
Для получения дополнительной информации о вычитании времени см.:
Как сделать вычитание матрицы в Excel
Предположим, у вас есть два набора значений (матриц) и вы хотите вычесть соответствующие элементы наборов, как показано на снимке экрана ниже:
Вот как это можно сделать с помощью одной формулы:
- Выберите диапазон пустых ячеек с тем же количеством строк и столбцов, что и ваши матрицы.
- В выделенном диапазоне или в строке формул введите формулу вычитания матриц:
=(А2:С4)-(Е2:G4) - Нажмите Ctrl + Shift + Enter, чтобы сделать его формулой массива.
Результаты вычитания появятся в выбранном диапазоне. Если щелкнуть любую ячейку в результирующем массиве и посмотреть на строку формул, то вы увидите, что формула заключена в {фигурные скобки}, что является визуальным признаком формул массива в Excel:
Если вам не нравится использовать формулы массива на ваших рабочих листах, вы можете вставить обычную формулу вычитания в самую верхнюю левую ячейку и скопировать вправо и вниз столько ячеек, сколько строк и столбцов вашей матрицы.
В этом примере мы могли бы поместить приведенную ниже формулу в C7 и перетащить ее в следующие 2 столбца и 2 строки:
=А2-С4
Из-за использования относительных ссылок на ячейки (без знака $) формула будет корректироваться в зависимости от относительного положения столбца и строки, в которые она копируется:
Вычитание текста одной ячейки из другой ячейки
В зависимости от того, хотите ли вы рассматривать прописные и строчные символы как одинаковые или разные, используйте одну из следующих формул.
Формула с учетом регистра для вычитания текста
Чтобы вычесть текст одной ячейки из текста в другой ячейке, используйте функцию ПОДСТАВИТЬ, чтобы заменить вычитаемый текст пустой строкой, а затем ОБРЕЗАЙТЕ лишние пробелы:
ОТРЕЗАТЬ(ЗАМЕНИТЬ(полный текст, text_to_subtract,””))
С полным текстом в A2 и подстрокой, которую вы хотите удалить в B2, формула выглядит следующим образом:
=ОТРЕЗАТЬ(ПОДСТАВИТЬ(A2,B2,””))
Как видите, формула прекрасно работает для вычитания подстроки из начала и из конца строки:
Если вы хотите вычесть один и тот же текст из диапазона ячеек, вы можете «жестко закодировать» этот текст в своей формуле.
В качестве примера удалим слово «Яблоки» из ячейки A2:
=ОТРЕЗАТЬ(ПОДСТАВИТЬ(A2;”Яблоки”,””))
Чтобы формула работала, убедитесь, что текст введен точно, включая регистр символов.
Формула без учета регистра для вычитания текста
Эта формула основана на том же подходе — замене вычитаемого текста пустой строкой. Но на этот раз мы будем использовать функцию REPLACE в сочетании с двумя другими функциями, которые определяют, с чего начать и сколько символов нужно заменить:
- Функция ПОИСК возвращает позицию первого вычитаемого символа в исходной строке, игнорируя регистр текста. Этот номер идет на start_num аргумент функции ЗАМЕНА.
- Функция LEN находит длину подстроки, которую необходимо удалить. Этот номер идет на num_chars аргумент ЗАМЕНИТЬ.
Полная формула выглядит следующим образом:
ОТДЕЛКА(ЗАМЕНА(полный текстПОИСК(text_to_subtract, полный текст), ЛЕН(text_to_subtract),””))
Применительно к нашему выборочному набору данных он принимает следующий вид:
=ОТРЕЗАТЬ(ЗАМЕНИТЬ(A2,ПОИСК(B2,A2),ДЛСТР(B2),””))
Где A2 — исходный текст, а B2 — удаляемая подстрока.
Вычесть один список из другого
Предположим, у вас есть два списка текстовых значений в разных столбцах, причем меньший список является подмножеством большего списка. Возникает вопрос: как удалить элементы меньшего списка из большего списка?
Математически задача сводится к вычитанию меньшего списка из большего:
Большой список: {“A”, “B”, “C”, “D”}
Меньший список: {“A”, “C”}
Результат: {“Б”, “Г”}
В терминах Excel нам нужно сравнить два списка на наличие уникальных значений, т.е. найти значения, которые появляются только в большем списке. Для этого используйте формулу, описанную в разделе Как сравнить два столбца на наличие различий:
=ЕСЛИ(СЧЁТЕСЛИ($B:$B, $A2)=0, “Уникальный”, “”)
Где A2 — первые ячейки большого списка, а B — столбец, содержащий меньший список.
В результате уникальные значения в большом списке помечаются соответствующим образом:
И теперь вы можете фильтровать уникальные значения и копировать их куда хотите.
Вот как вы вычитаете числа и ячейки в Excel. Чтобы поближе ознакомиться с нашими примерами, загрузите наш образец рабочей тетради ниже. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Практическая рабочая тетрадь
Примеры формул вычитания (файл .xlsx)
