Excel вычитает другой столбец из одного столбца

Обновлено: 21.11.2024

Если вам нужно одновременно вычесть одно значение из диапазона ячеек, в Excel это можно сделать несколькими способами.

Например, предположим, что у вас есть баллы учащихся и вы хотите вычесть 5 баллов из всех баллов, вы можете сделать это с помощью простой формулы (и некоторых других простых методов, которые мы рассмотрим в этом руководстве)

Когда вам нужно вычесть несколько значений ячеек из определенного числа, лучший способ сделать это – поместить число в одну из ячеек (на том же или другом листе) и применить простую формулу.

В этом руководстве по Excel я покажу вам три полезных приема, которые можно использовать для вычитания нескольких ячеек из одной ячейки в Excel.

Среди них вы увидите, как настроить свою формулу для решения этой проблемы, классный трюк с использованием функции Специальная вставка и если вы готовы к этому , немного кодирования VBA!

Итак, приступим!

Оглавление

Вычитание нескольких ячеек из ячейки с помощью формулы

Допустим, у вас есть набор данных, как показано ниже (ячейки B2:B11), и вы хотите вычесть каждое из этих значений из значения в ячейке A2.

Проще всего это сделать с помощью простой формулы вычитания.

Вот как это сделать:

Нажмите ячейку в пустом столбце, например C2, и введите в строке формул следующую формулу: =A2-B2 расположение A2, щелкнув перед, после или между ссылкой на A2 и нажав клавишу F4. Обратите внимание, что расположение ячейки A2 меняется на $A$2. Вы также можете вручную добавить эти знаки доллара, если хотите.
Нажмите клавишу возврата/ввода на клавиатуре.
Потяните вниз маркер заполнения в правом нижнем углу ячейки C2, чтобы скопировать формулу для всех ячеек (C2:C11). Теперь вы получите целый столбец ячеек, содержащих разницу между ячейкой A2 и ячейками B2:B11.

Примечание. Преимущество использования метода формул заключается в том, что результат является динамическим. Если вы измените значение в ячейке A2, результирующие данные будут автоматически обновлены. И один недостаток этого метода заключается в том, что для получения результата вам потребуется дополнительный столбец (что в большинстве случаев не является такой большой проблемой).

Если вы не хотите, чтобы результат был связан с ячейкой A2, вы можете преобразовать эти формулы в значения.

Теперь, если вам интересно, почему мы нажали F4 и изменили ссылку A2 на $A$2.

Причина этого в том, что мы хотели сделать ссылку на ячейку A2 абсолютной, а не относительной ссылкой.

Поэтому, чтобы сделать это еще более ясным, позвольте мне быстро рассказать вам об абсолютных и относительных ссылках.

Разница между абсолютными и относительными ссылками на ячейки в Excel

Абсолютные и относительные ссылки на ячейки реагируют по-разному при копировании в другие ячейки. Они также по-разному представлены в формулах. Например, относительная ссылка на ячейку в столбце A строки 1 представлена как A1. Однако абсолютная ссылка на ту же ячейку представляется как $A$1.

По умолчанию все ссылки на ячейки являются относительными ссылками. Чтобы преобразовать ссылку на ячейку в абсолютную ссылку, вам нужно добавить к ней знак доллара ($) или просто нажать клавишу F4, когда курсор включен, до или после ссылки.

Когда вы копируете и вставляете ячейку с относительной ссылкой, ссылка на ячейку корректируется в соответствии с местом вставки. Однако относительная ссылка никогда не изменяется. и всегда относится к одной и той же ячейке, независимо от того, куда она вставлена.

Например, если вы скопируете формулу =A1 – B1 из строки 1 в строку 2, формула станет =A2 – B2. Однако если вы скопируете формулу =$A$1 – B1 из строки 1 в строку 2, формула станет $A$1 – B2.

Обратите внимание, ссылка в первом операнде не изменилась, так как она была указана как абсолютная ссылка ($A$1). Однако ссылка на ячейку во втором операнде изменилась с B1 на B2.

В нашем примере мы хотим, чтобы ссылка на ячейку A2 оставалась постоянной, когда формула копируется и заполняется другими ячейками в столбце C. Поэтому мы преобразовали ссылку A2 в абсолютную ссылку.

Вычитание нескольких ячеек из ячейки с помощью специальной вставки

Как я уже сказал, есть несколько способов снять шкуру с этого кота в Excel.

Итак, вот еще один простой способ вычесть диапазон ячеек из определенного значения.

Если вы хотите вычесть, скажем, ячейки B2:B11 из ячейки A2 и заменить разницу обратно в ячейках B2:B11, вот еще один интересный прием, который вы можете использовать:

Выберите ячейку A2.
Чтобы скопировать, нажмите CTRL+C (или щелкните правой кнопкой мыши и выберите "Копировать").
Выберите ячейки B2:B11.
Щелкните правой кнопкой мыши в любом месте выделенного фрагмента и выберите параметр Специальная вставка. Откроется диалоговое окно «Специальная вставка».
В диалоговом окне Специальная вставка выберите Вычесть (в параметрах операции).
Нажмите "ОК".

Вы заметите, что числа в выбранном диапазоне (B2:B11) изменились там, где теперь находятся результирующие данные после вычитания значения в ячейке A2.

Теперь вы можете удалить значение в ячейке A2, если хотите (поскольку результат не связан с этой ячейкой).

Если вы хотите сохранить исходные данные, сделайте копию ячеек, а затем выполните описанную выше операцию над скопированными ячейками.

Вычитание нескольких ячеек из ячейки с помощью VBScript

Если вы готовы немного программировать и вам удобно использовать VBscript, вот третий метод, который вы можете применить.

Прежде чем вы начнете, вот код VBA, который мы собираемся использовать:

Удерживая нажатой клавишу CTRL на клавиатуре, выберите все ячейки B2:B11. Скопируйте этот код и сохраните его в надежном месте, например в файле блокнота, чтобы вы могли использовать его позже.

На ленте меню Разработчик выберите Visual Basic.
Когда откроется окно VBA, нажмите Вставить->Модуль. Скопируйте приведенный выше код и вставьте его в окно модуля.
Запустите код, нажав кнопку на панели инструментов вверху.
Закройте окно VBA.

Когда вы вернетесь к своему рабочему листу, вы обнаружите, что все выбранные вами значения ячеек заменены их индивидуальными отличиями от ячейки A2.

Однако будьте осторожны, этот метод навсегда изменяет значения в выбранных вами ячейках. Итак, если вы хотите сохранить исходные данные, сделайте копию своих данных в отдельном столбце, а затем выполните операцию.

В приведенном выше методе мы создали макрос, который проходит через каждую выбранную ячейку и вычитает значение из значения в ячейке A2.

Если вы хотите вычесть эти ячейки из какой-либо другой ячейки, просто замените «A2» в строке 3 на ссылку на нужную ячейку.

Этот метод удобен, когда вам нужно вычесть несколько таких столбцов (или диапазонов ячеек) из значения в определенной ячейке. Вместо многократного выполнения формулы или специальной вставки вы можете сделать это быстрее с помощью VBA.

Заключение

В этом руководстве по Excel мы рассмотрели три различных способа вычитания нескольких значений ячеек из одной ячейки в Excel. Среди них мы увидели, как вы можете использовать формулу, специальную функцию вставки и VBScript.

Вы можете использовать любой из вышеперечисленных методов, который вам удобен. Если вы знаете какие-либо другие более эффективные методы, сообщите нам об этом в комментариях.

Вычесть число из нескольких ячеек

Чтобы вычесть число из диапазона ячеек, щелкните ячейку, в которой вы хотите отобразить результат, и введите «=» (равно) и ссылку на ячейку первого числа, затем «-» (минус) и число, которое вы хотите вычесть.

В этом примере начните с ячейки E2 (200). В ячейке F2 будет отображаться Цена в E2, уменьшенная на 10. Итак, в ячейке F2 введите:

Затем эту формулу можно скопировать вниз по столбцу в строки ниже. Для этого просто щелкните ячейку, в которую вы ввели формулу, и поместите курсор в правый нижний угол ячейки. Когда он изменится на знак плюса, перетащите его вниз к остальным строкам, где вы хотите применить формулу (в этом примере, F2:F6).

В результате относительной ссылки на ячейку константа (в данном примере 10) остается неизменной, но адрес ячейки меняется в зависимости от строки, в которой вы находитесь (поэтому F3=E3-10 и т. д.).< /p>

Вычесть несколько ячеек с помощью специальной вставки

Вы также можете вычесть число из нескольких ячеек и вернуть результат в виде числа в той же ячейке.

<р>2. После этого выберите ячейки, из которых вы хотите вычесть значение, и щелкните правой кнопкой мыши диапазон данных (в данном примере E11:E15). В раскрывающемся меню нажмите "Специальная вставка".

<р>3. Появится окно «Специальная вставка». В разделе «Операция» выберите «Вычесть», затем нажмите «ОК».

После этого вы увидите результат в виде числа в тех же ячейках. (В этом примере из каждого значения из диапазона данных E11:E15 было вычтено 10.) Этот метод лучше, если вы не хотите отображать результаты в новом столбце, поскольку новые (уменьшенные) результаты отображаются в исходные ячейки.

Вычесть столбец со ссылками на ячейки

Чтобы вычесть целый столбец из другого с помощью ссылок на ячейки, выберите ячейку, в которой вы хотите отобразить результат, и введите «=» (равно) и ссылку на ячейку для первого числа, затем «-» (минус) и ссылка на ячейку, которую вы хотите вычесть.

Для этого примера рассчитайте разницу между Ценой 1 (A2) и Ценой 2 (B2). Итак, в ячейку C2 введите:

В Excel нет функции ВЫЧИТАНИЕ. Однако есть несколько способов вычитания чисел в Excel. Готовы ли вы улучшить свои навыки работы с Excel?

<р>2. Приведенная ниже формула вычитает значение в ячейке A2 и значение в ячейке A3 из значения в ячейке A1.

<р>3. Как вы понимаете, эта формула может быть довольно длинной. Просто используйте функцию СУММ, чтобы сократить формулу. Например, приведенная ниже формула вычитает значения в диапазоне A2:A9 из значения в ячейке A1.

Посмотрите на скриншот ниже. Чтобы вычесть числа в столбце B из чисел в столбце A, выполните следующие действия.

4б. Затем выберите ячейку C1, нажмите в правом нижнем углу ячейки C1 и перетащите ее вниз к ячейке C6.

Посмотрите на скриншот ниже. Чтобы вычесть число из диапазона ячеек, выполните следующие действия.

5б. Затем выберите ячейку B1, нажмите в правом нижнем углу ячейки B1 и перетащите ее вниз к ячейке B6.

Объяснение: когда мы перетаскиваем формулу вниз, абсолютная ссылка ($A$8) остается неизменной, а относительная ссылка (A1) меняется на A2, A3, A4 и т. д.

Если вы не сторонник формул, используйте Специальную вставку для вычитания в Excel без использования формул!

<р>7. Щелкните правой кнопкой мыши и выберите "Копировать" (или нажмите CTRL + c).

Примечание: чтобы вычесть числа в одном столбце из чисел в другом столбце, на шаге 6 просто выберите диапазон вместо ячейки.

Формула вычитания в Excel упрощает вычитание чисел, ячеек, процентов, дат, матриц, времени и т. д. Он начинается с оператора сравнения «равно» (=), за которым следует первое число, знак минус и второе число. Например, чтобы вычесть 2 и 5 из 15, примените формулу «=15-2-5». Возвращает 8.

При вычитании первое число — это уменьшаемое (из которого нужно вычесть число), а второе — вычитаемое (число, которое нужно вычесть). Полученное число и есть разница.

Арифметический оператор «минус» (-) помещается между уменьшаемым и вычитаемым.

Как использовать формулу вычитания в Excel?

Формула вычитания Excel проста в использовании. Давайте рассмотрим следующие примеры, чтобы понять, как это работает.

В следующей таблице показаны некоторые значения в столбцах A и B. Мы хотим вычесть значения в столбце B из значений в столбце A.

Шаги по вычитанию значений столбца B из значений столбца A перечислены ниже:

Примечание. Ссылки B4 и A4 можно либо ввести, либо выбрать, щелкнув соответствующие ячейки.

Примечание. Если значения в столбце A или столбце B изменяются, результат автоматически обновляется в столбце D.

Найдите сотрудников, имеющих право на вознаграждение.

Шаг 1. Введите следующую формулу.

Чтобы рассчитать доход, полученный сверх цели (5000 рупий), мы вычитаем последний (столбец C) из первого (столбец B). Поскольку целевая сумма одинакова для всех сотрудников, мы используем абсолютную ссылку. Абсолютная ссылка Абсолютная ссылка в Excel — это тип ссылки на ячейку, в которой ячейки, на которые ссылаются, не изменяются, как это было в относительной ссылке. Нажав f4, мы можем создать формулу для абсолютной ссылки. читать дальше $C$19.

Примечание. При абсолютной ссылке на ячейку знак доллара ($) помещается перед буквой столбца и номером строки, как в $C$19. Это сохраняет столбец и строку постоянными.

Шаг 2. Нажмите клавишу «Ввод». Окончательный результат появится в столбце D, как показано на следующем рисунке.

Для простоты мы сохранили формулы в столбце E и дали различия в столбце D. Сотрудники с отрицательными значениями различий (столбец D) не имеют права на вознаграждение.

Следовательно, Генри и Федрик не имеют права на участие, а остальные получают подарочный ваучер.

Правила, регулирующие формулу вычитания в Excel

Он всегда начинается с оператора сравнения "равно" (=).
Требуется вставка арифметического оператора "минус" (-) между двумя значениями.
Он используется как в простых, так и в сложных математических задачах, где требуется сравнение двух значений.
Его можно использовать в сочетании с другими арифметическими операторами, такими как "плюс" (+), "процент" (%), "косая черта" (/), "звездочка" (*) и т. д.

Часто задаваемые вопросы

В MS Excel нет функции ВЫЧИТАНИЕ. Однако он облегчает вычитание, вставляя знак «минус» (-) между двумя значениями. Основная формула вычитания сформулирована следующим образом:

"=число 1-число 2"

Использование формулы Excel для вычитания описано в следующих шагах:
• Введите оператор сравнения «равно» (=).
• Введите первое число, затем знак «минус» (-) и второе число. Либо выберите ячейки, содержащие значения.
• Нажмите клавишу «Ввод», и результат появится в ячейке, где была введена формула.

Существуют различные способы вычитания нескольких ячеек из одной. Например, давайте вычтем ячейки A12, A13, A14 и A15 из ячейки A1.

Выберите любой из следующих трех методов:

• Применить формулу «=A1-A12-A13-A14-A15».
• Применить формулу «=A1-СУММ(A12:A15)».
• Поместите знак «минус» (-) перед числами в ячейках A12, A13, A14 и A15. Примените формулу «=СУММ(A1;A12:A15)».

Для расчета времени используйте следующую формулу:

"=Время окончания-время начала"

Например, ячейки A6 и B6 содержат 2:30:00 и 7:00:00 соответственно. Количество часов между двумя значениями времени рассчитывается по формуле «=B6-A6». Разница 4:30:00.

Примечание. Для получения правильных результатов примените формат времени к ячейке, содержащей формулу.