Как сделать отрицательное число положительным в Excel

Обновлено: 20.11.2024

Большинство людей, работающих с электронными таблицами Excel, имеют дело с числами в больших/маленьких наборах данных.

И когда вы работаете с числами, у вас будут все их типы (положительные, отрицательные, десятичные, дата/время).

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

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

И опять же, есть несколько способов сделать это в Excel.

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

Так что, если вам интересно, продолжайте читать!

Это руководство охватывает:

Умножить на минус 1, чтобы преобразовать отрицательное число в положительное

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

Но вы также должны убедиться, что вы умножаете только отрицательные числа, а не положительные.

Предположим, у вас есть набор данных, как показано ниже:

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

Приведенная выше формула использует функцию ЕСЛИ, чтобы сначала проверить, является ли число положительным или нет. Если он положительный, знак не меняется, а если отрицательный, к ссылке добавляется отрицательный знак, что дает нам только положительное число.

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

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

Используйте функцию ABS, чтобы изменить все отрицательные числа на положительные

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

.. функция ABS

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

Ниже приведена формула, которая это сделает:

Приведенная выше функция ABS не влияет на положительные числа, но преобразует отрицательные числа в положительные значения.

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

Умножение с использованием специальной вставки для изменения знака

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

Предположим, у вас есть набор данных, как показано ниже, и вы хотите изменить знак:

Ниже приведены действия по обращению знака с помощью специальной вставки:

Вы заметите, что приведенные выше шаги мгновенно изменяют знак числа (т. е. положительные числа становятся отрицательными, а отрицательные числа становятся положительными).

Но что, если вы хотите преобразовать только отрицательные числа в положительные числа, а не наоборот?

В этом случае вам каким-то образом сначала нужно выбрать все отрицательные числа, а затем выполнить описанные выше шаги.

Вот как выбрать только отрицательные числа в Excel:

  1. Выбрать весь набор данных
  2. Удерживая нажатой клавишу Control, нажмите клавишу F. Откроется диалоговое окно «Найти и заменить».
  3. В поле «Найти» введите – (знак минус)
  4. Нажмите "Найти все".
  5. Удерживая нажатой клавишу Control, нажмите клавишу A.

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

У этого метода есть два преимущества по сравнению с методом формулы (два метода, рассмотренные ранее):

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

Быстрая заливка для удаления знака минуса

Мгновенная заливка — это новая функция, представленная в Excel 2013.

Это позволяет быстро определить шаблоны, а затем предоставить результат, в котором шаблон был применен ко всему набору данных.

Один из способов использования этого — когда у вас есть имена, и вы хотите разделить имя и фамилию. Как только вы пару раз наберете имя в соседней ячейке, Flash Fill определит шаблон и выдаст вам все имена.

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

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

Ниже приведены шаги по преобразованию отрицательных чисел в положительные с помощью Flash Fill:

  1. В соседнем с данными поле введите ожидаемый результат вручную. В этом примере я вручную введу 874
  2. В ячейке под ним введите ожидаемый результат (в данном примере – 162).
  3. Выберите обе ячейки.
  4. Поместите курсор в правую нижнюю часть выделения. Он изменится на значок плюса.
  5. Нажмите и перетащите, чтобы заполнить столбец (или дважды щелкните)
  6. Нажмите значок "Параметры автозаполнения".
  7. Нажмите "Быстрая заливка".

Вышеуказанные шаги дадут ожидаемый результат, если знак "минус" будет удален.

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

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

Преобразование отрицательных чисел в положительные одним щелчком мыши (VBA)

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

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

В таком случае вы можете создать и сохранить код макроса VBA в личной книге макросов и поместить VBA на панель быстрого доступа. Таким образом, в следующий раз, когда вы получите набор данных, где вам нужно это сделать, вы просто выберите данные и щелкните значок в QAT…

… и готово!

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

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

Приведенный выше код использует цикл For Next для просмотра каждой выбранной ячейки. Он использует оператор IF, чтобы проверить, является ли значение ячейки отрицательным или нет. Если значение отрицательное, знак меняется на противоположный, а если нет, то игнорируется.

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

Теперь позвольте мне показать вам, как добавить этот код на панель быстрого доступа (шаги одинаковы, независимо от того, сохраняете ли вы этот код в отдельной книге или в PMW)

Теперь у вас будет значок макроса в QAT.

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

Примечание. Если вы сохраняете код макроса VBA в книге, вам необходимо сохранить книгу в формате с поддержкой макросов (XLSM)

Чтобы преобразовать отрицательные числа в положительные и оставить положительные числа (т. е. получить абсолютные значения), используйте встроенную функцию ABS. В примере показана формула в C6:

Функция ABS полностью автоматическая. Все, что вам нужно сделать, это указать число, и ABS вернет абсолютное значение.

Преобразуйте отрицательные числа на месте

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

  1. Добавить -1 к ячейке и скопировать в буфер обмена
  2. Выберите отрицательные числа, которые хотите преобразовать.
  3. Используйте «Специальная вставка» > «Значения + умножение».

В видео на этой странице показан этот метод и многие другие специальные сочетания клавиш для вставки.

СЧЁТЕСЛИ подсчитывает количество ячеек в диапазоне, соответствующих заданным критериям. В этом случае критерии предоставляются как "

В этом примере цель состоит в том, чтобы преобразовать отрицательные числа в столбце B в ноль и оставить положительные числа без изменений. По сути, мы хотим заставить отрицательные числа равняться нулю. С функцией MAX Функция MAX предлагает элегантное решение: = MAX (.

Функция ABS в Excel возвращает абсолютное значение числа. ABS преобразует отрицательные числа в положительные, а на положительные числа это не влияет.

Обучение формулам Excel

Формулы — это ключ к выполнению задач в Excel. В этом ускоренном обучении вы научитесь использовать формулы для работы с текстом, работы с датами и временем, поиска значений с помощью ВПР и ИНДЕКС и ПОИСКПОЗ, подсчета и суммирования с использованием критериев, динамического ранжирования значений и создания динамических диапазонов. Вы также узнаете, как устранять неполадки, отслеживать ошибки и устранять проблемы. Мгновенный доступ. Подробности здесь.

Загрузить более 100 важных функций Excel

Получите более 100 функций Excel, которые вы должны знать, в одном удобном PDF-файле.

Темы

Ключевые функции

Здравствуйте! Меня зовут Дэйв Брунс. Я управляю компанией Exceljet вместе со своей женой Лизой. Наша цель — помочь вам быстрее работать в Excel. Мы создаем короткие видеоролики и наглядные примеры формул, функций, сводных таблиц, условного форматирования и диаграмм. Подробнее.

Вывод: изучите 2 способа изменить знак числа с положительного на отрицательное или с отрицательного на положительный в Excel.

Уровень навыков: начальный

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

Как и все в Excel, эту проблему можно решить несколькими способами. В этой статье и видео мы рассмотрим, как поменять местами знаки с помощью формулы и с помощью меню «Специальная вставка» в Excel.

Видео: 2 способа поменять местами положительные и отрицательные числовые знаки

Первый способ довольно прост. Мы можем написать формулу для умножения значения ячейки на минус 1 (-1).

Это работает с ячейками, содержащими как положительные, так и отрицательные числа. Результат формулы:

  • Положительные числа будут преобразованы в отрицательные числа.
  • Или отрицательные числа будут преобразованы в положительные числа.

Затем мы можем скопировать/вставить формулу, чтобы получить результаты для всех ячеек в столбцах B и C.

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

Это можно сделать с помощью копирования и вставки. Мы скопируем ячейку, содержащую -1, затем используем операцию «Умножение» в меню «Специальная вставка». Это выполнит умножение выбранных ячеек, в которые мы вставляем данные.

Вот краткий анимационный ролик, показывающий, как выполнять операцию "Специальная вставка".

Я объясняю это более подробно в видео выше, но вот шаги, чтобы изменить знак числа с помощью специальной вставки:

  1. Введите -1 в пустой ячейке листа.
  2. Выделите ячейки, содержащие -1, и скопируйте их.
  3. Выберите ячейки, содержащие числа, которые вы хотите инвертировать.
  4. Откройте меню «Специальная вставка». Щелкните правой кнопкой мыши > «Специальная вставка…» (Alt, E, S).
  5. Нажмите переключатель «Умножить» в разделе «Операция» меню «Специальная вставка».
  6. Нажмите "ОК".

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

Переворачивать знак только в непустых ячейках

В нашем исходном примере столбцы B и C содержат несколько пустых ячеек. Если мы выберем все ячейки в диапазоне, включая пустые ячейки, а затем выполним специальную вставку, пустые ячейки будут преобразованы в 0 (нули).

Теперь мы можем оставить эти пустые ячейки пустыми. Мы можем сделать это, используя меню «Перейти к специальному», чтобы сначала выбрать непустые ячейки (константы/формулы) перед выполнением вставки.

Я объясню это более подробно в видео выше, но вот шаги:

  1. Скопируйте ячейку, содержащую -1.
  2. Выберите диапазон для вставки, включая пустые ячейки.
  3. Откройте меню «Перейти к специальному» (вкладка «Главная» > «Найти и выбрать» > «Перейти к специальному…»)
  4. Нажмите переключатель «Константы», чтобы выбрать ячейки, содержащие значения (не пустые).
  5. Нажмите "ОК".
  6. Будут выбраны ячейки, содержащие значения (не пустые).
  7. Выполните операцию "Специальная вставка умножения" (щелкните правой кнопкой мыши > "Специальная вставка" > "Умножение" > "ОК").

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

Обратить знак только в видимых ячейках

Мы также можем использовать операцию «Специальное умножение вставки» для отфильтрованного диапазона. Сначала мы хотим выбрать видимые ячейки только в отфильтрованном диапазоне. Это можно сделать в меню «Перейти к специальному» или с помощью сочетания клавиш Alt+;

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

Операция "Специальное умножение вставки" также работает с ячейками, содержащими формулы. Ячейки в приведенном ниже примере содержат формулу ВПР. Мы можем изменить знаки результатов, но сохранить формулу.

Когда мы выполняем специальную вставку для ячейки, содержащей формулу, формула изменяется, чтобы добавить *-1 к ее концу. Это означает, что мы НЕ теряем формулы.

Мы также можем использовать здесь меню «Перейти к специальному», чтобы сначала выбрать ячейки, содержащие формулы (не пустые), прежде чем выполнять специальную вставку.

Сохраняйте свои листы положительными

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

Какие приемы вы используете, чтобы поменять местами числовые знаки? Пожалуйста, оставьте комментарий ниже с любыми вопросами или предложениями. Спасибо! 🙂

Преобразование положительного числа в отрицательное должно быть простым, верно? В конце концов, вам нужно всего лишь добавить знак минус перед числом.

Но что, если у вас есть целый список положительных чисел, которые нужно преобразовать в отрицательные?

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

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

В этом уроке мы покажем вам три таких способа:

  • Использование функции Специальная вставка
  • Использование формулы
  • Использование VBA

Давайте рассмотрим каждый из этих методов один за другим.

Оглавление

Использование формулы для превращения положительных чисел в отрицательные

Первый метод (метод Специальная вставка) в основном меняет знак чисел, поэтому если у вас есть положительные числа, они будут преобразованы в отрицательные и наоборот.

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

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

Обратите внимание, что на этот раз в списке есть как положительные, так и отрицательные числа.

Мы хотим сделать отрицательными только положительные числа, но оставить отрицательные числа такими, какие они есть.

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

Формула 1: Использование функции ЕСЛИ

В первой формуле используется функция ЕСЛИ. Введите следующую формулу в ячейку B2 и нажмите клавишу возврата:

Затем скопируйте эту формулу в остальные ячейки столбца, используя маркер заполнения. Теперь вы должны получить все отрицательные числа в столбце B.

Формула просто проверяет, превышает ли значение в соответствующей ячейке столбца A 0 (или положительное число).

Если это положительное число, формула вернет значение, полученное путем его умножения на -1 (отрицательный эквивалент числа).

Однако, если это 0 или отрицательное число, формула возвращает то же значение, что и есть.

Формула 2: Использование функции ABS

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

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

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

Затем скопируйте эту формулу в остальные ячейки столбца, используя маркер заполнения. Теперь вы должны получить все отрицательные числа в столбце B.

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

Приведенная выше формула затем умножает это значение на -1, что позволяет выразить его в отрицательной форме. Результат, который вы получите, показан ниже:

После того, как вы получите все свои результаты, вы можете скопировать результирующие значения из столбца B (используя сочетание клавиш CTRL+C), а затем Вставить как значение поверх столбца A (используя сочетание клавиш CTRL+Shift+V) в запишите исходные значения. Затем вы можете удалить столбец B.

Использование специальной функции вставки Excel для преобразования положительных чисел в отрицательные

Этот метод удобен, если все ваши числа положительные и вы хотите изменить знаки всех из них на отрицательные.

Допустим, у вас есть следующий список положительных чисел:

  1. Введите -1 в любую пустую ячейку рабочего листа. Для нашего примера поместим его в ячейку C2:

  1. Нажмите CTRL+C на клавиатуре, чтобы скопировать это значение.
  2. Выделите диапазон ячеек, содержащих числа, которые вы хотите сделать отрицательными.

  1. Щелкните правой кнопкой мыши выделенный фрагмент и выберите Специальная вставка в появившемся контекстном меню.

  1. Откроется диалоговое окно Специальная вставка, в котором можно выбрать различные параметры вставки.
    1. В категории Операция установите переключатель рядом с пунктом Умножение.

    1. Нажмите "ОК".
    2. Знаки всех выбранных ячеек должны измениться на отрицательные.
    1. Теперь вы можете удалить значение -1 из ячейки C2.

    Как это произошло?

    Опция Умножить функции Специальная вставка умножает скопированные значения на значения в целевых ячейках. Под «целевыми ячейками» мы подразумеваем ячейки, которые вы хотите вставить.

    Поскольку мы скопировали значение «-1» перед операцией Специальная вставка, все выбранные нами ячейки просто умножались на скопированное значение (-1). Это сделало все значения отрицательными.

    Примечание. Этот метод просто умножает выбранные значения на -1. Поэтому, если в вашем списке уже есть отрицательное значение, этот метод просто сделает это число положительным.

    Использование VBA для превращения положительных чисел в отрицательные

    Третий метод использует код VBA, чтобы программно сделать диапазон чисел отрицательным. Вот код VBA, который вы можете использовать:

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

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

    Чтобы применить этот скрипт к вашему набору данных, выполните следующие действия:

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

    1. Теперь ваш код готов к запуску. Закройте окно VBA.
    2. Перейдите на рабочий лист и выберите диапазон ячеек, с которыми вы хотите работать (в нашем примере это ячейки A2:A8).
    3. Перейдите в раздел Макросы->Разработчик.
    4. Откроется диалоговое окно Макросы. Выберите макрос с именем MakeNegative из отображаемого списка макросов.

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

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

    Поэтому мы рекомендуем вам сохранить резервную копию ваших данных перед запуском скрипта.

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

    Метод "Специальная вставка" прекрасно работает, если вы хотите просто поменять местами знаки всех ваших чисел.

    Однако, если вы хотите преобразовать только положительные числа в отрицательные (оставив отрицательные числа такими, какие они есть), то лучше использовать метод формул и метод VBA.

    Читайте также: