
Изменение отрицательных чисел на положительные в Excel

Большинство людей, работающих с электронными таблицами Excel, имеют дело с числами в больших / малых наборах данных. А когда вы работаете с числами, у вас будут все их типы (положительное, отрицательное, десятичное, дата / время).
Одна из наиболее распространенных задач, которые нам часто приходится выполнять, — это преобразование этих чисел из одного формата в другой.
И, наверное, самый распространенный — когда для некоторых расчетов необходимо заменить отрицательные числа на положительные (убрать знак минус). Опять же, в Excel есть несколько способов сделать это.
В этом уроке я покажу вам несколько простых способов заменить отрицательные числа на положительные в Excel (используя формулы, методы копирования и вставки и другие замечательные методы).
Так что, если вам интересно, читайте дальше!
Умножьте на (-1), чтобы преобразовать отрицательное число в положительное
Если у вас есть столбец, полный чисел, и вы хотите быстро получить числа, в которых отрицательные значения были преобразованы в положительные значения, вы можете легко сделать это, умножив эти отрицательные значения на -1.
Но вам также нужно убедиться, что вы умножаете только отрицательные числа, а не положительные.
Предположим, у нас есть набор данных, как показано ниже:
Ниже приведена формула, которая преобразует отрицательные числа в положительные и оставляет без изменений остаток: = ЕСЛИ (A2> 0, A2, -A2)
Вышеупомянутая формула использует функцию ЕСЛИ, чтобы сначала проверить, является ли число положительным или нет. Если он положительный, знак не меняется, а если он отрицательный, к ссылке добавляется отрицательный знак, что дает нам только положительное число.
Если у вас также есть текстовые значения в наборе данных, эта функция проигнорирует их (и будут изменены только отрицательные значения)
Теперь, когда у вас есть желаемый результат, вы можете преобразовать эти формулы в значения (и скопировать их в исходные данные, если они вам не нужны).
Используйте функцию ABS, чтобы изменить все отрицательные числа на положительные
В Excel есть специальная функция, которая удаляет отрицательный знак и выдает абсолютное значение.
.. Функция ABS
Предположим, у нас есть набор данных, показанный ниже, и мы хотим изменить отрицательные значения на положительные.
Ниже приведена формула, которая сделает это:
= АБС (A2)
Приведенная выше функция ABS не влияет на положительные числа, но преобразует отрицательные числа в положительные.
Теперь, когда у вас есть желаемый результат, вы можете преобразовать эти формулы в значения (и скопировать их в исходные данные, если они вам не нужны).
Одним из небольших недостатков функции ABS является то, что она может работать только с числами. Если у вас есть текстовые данные в некоторых ячейках и вы используете функцию ABS, она даст вам #VALUE! ошибка.
Умножьте с помощью специальной вставки, чтобы перевернуть знак
Если вы хотите изменить знак числа (т.е изменить отрицательный на положительный и положительный на отрицательный), вы также можете использовать эту специальную технику вставки умножения.
Допустим, у нас есть набор данных, показанный ниже, и мы хотим перевернуть знак:
Ниже приведены шаги по изменению знака с помощью специальной вставки:
- В любой пустой ячейке на листе введите -1.
- Скопируйте эту ячейку (со значением -1)
- Выберите диапазон, в котором вы хотите перевернуть знак.
- Щелкните правой кнопкой мыши одну из выбранных ячеек
- Щелкните Специальная вставка. Откроется диалоговое окно «Специальная вставка.
- В разделе «Вставить» выберите «Значения».
- В параметрах операции выберите «Умножить».
- Нажмите ОК
- Убрать -1 из ячейки
Вы можете заметить, что приведенные выше шаги мгновенно меняют знак числа (т. Е. Положительные числа меняются на отрицательные, а отрицательные — на положительные).
Но что, если вы хотите преобразовать только отрицательные числа в положительные, а не наоборот?
В этом случае вам нужно каким-то образом сначала выбрать все отрицательные числа, а затем выполнить вышеуказанные шаги.
Вот как выбрать в Excel только отрицательные числа:
- Выбрать весь набор данных
- Удерживая нажатой клавишу Ctrl, нажмите клавишу F. Откроется диалоговое окно «Найти и заменить».
- В поле «Найти» введите — (знак минус)
- Нажмите «Найти все
- Удерживая нажатой клавишу Ctrl, нажмите клавишу A.
Вышеупомянутые шаги выберут только отрицательные ячейки. Теперь, когда у вас выделены эти ячейки, вы можете использовать технику «Специальная вставка», чтобы изменить знак только отрицательных чисел.
Этот метод имеет два преимущества по сравнению с методом формул (два описанных ранее метода):
- Вам не нужно добавлять дополнительный столбец, а затем использовать формулу для получения результата в этом столбце. Вы можете использовать его в существующем наборе данных.
- Нет необходимости преобразовывать формулы в значения (поскольку полученный результат уже является значением, а не формулой)
Мигающая заливка для удаления отрицательного знака
Flash Fill — это новая функция, представленная в Excel 2013.
Это позволяет быстро идентифицировать модели, а затем предоставить результат, в котором модель была применена ко всему набору данных.
Это можно использовать, когда у вас есть имена и вы хотите разделить имя и фамилию. Как только вы наберете имя в соседней ячейке несколько раз, Flash Fill определит шаблон и предоставит вам все имена.
Точно так же вы можете использовать его, чтобы быстро удалить отрицательный знак из числа, в то время как положительные значения остаются прежними.
Ниже приведен набор данных, в котором у меня есть отрицательные числа, и я хочу изменить их на положительные значения.
Ниже приведены шаги по замене отрицательных чисел на положительные с помощью Flash Fill:
- В поле рядом с таблицей данных вручную введите ожидаемый результат. В этом примере я вручную введу 874
- В ячейке ниже введите ожидаемый результат (в данном примере — 162).
- Выберите обе ячейки
- Поместите курсор в нижний правый угол выделения. Он изменится на знак плюса
- Щелкните и перетащите, чтобы заполнить столбец (или дважды щелкните)
- Щелкните значок Параметры автозаполнения.
- Щелкните «Мгновенная заливка.
Вышеупомянутые шаги дадут вам ожидаемый результат, когда знак минус был удален.
При использовании этого метода помните, что все в Excel заключается в угадывании шаблона. Поэтому вам нужно хотя бы показать Excel, что вы конвертируете отрицательное число в положительное.
Это означает, что вам придется вручную вводить ожидаемый результат, пока не будет покрыто хотя бы одно отрицательное число.
Преобразование отрицательных чисел в положительные одним щелчком мыши (VBA)
Наконец, вы также можете использовать VBA для преобразования отрицательных значений в положительные.
Я бы порекомендовал использовать этот метод, если вам приходится делать это часто. Возможно, вы регулярно получаете набор данных из базы данных или от коллег, и вам приходится делать это каждый раз.
Если это так, вы можете создать и сохранить код макроса VBA в своей личной книге макросов и разместить VBA на панели быстрого доступа. Поэтому в следующий раз, когда вы получите набор данных там, где вам нужно, выберите данные и щелкните значок в QAT…
… И готово!
Не волнуйтесь, я покажу вам точные шаги, чтобы заставить его работать.
Ниже приведен код VBA, который преобразует отрицательные значения в положительные значения в выбранном диапазоне:
Sub ChangeNegativetoPOsitive () для каждой ячейки в выделенном списке Если Cell.Value В приведенном выше коде используется цикл For Next для перебора каждой выбранной ячейки. Используйте оператор IF, чтобы проверить, является ли значение ячейки отрицательным или нет. Если значение отрицательное, знак меняется на противоположный, в противном случае он игнорируется.
Вы можете добавить этот код в обычную форму в книге (если хотите использовать его только в этой книге). И если вы хотите использовать этот код макроса в любой книге в вашей системе, вы можете сохранить его в своей личной книге макросов.
Теперь позвольте мне показать вам, как добавить этот код на панель быстрого доступа (шаги будут такими же, если вы сохраните этот код в отдельной книге или в PMW)
- Откройте книгу, в которой у вас есть данные
- Добавить код VBA в книгу (или PMW)
- Щелкните параметр «Настроить панель быстрого доступа» в QAT.
- Щелкните Дополнительные команды».
- В диалоговом окне «Параметры Excel» щелкните раскрывающийся список «Выбрать команды из».
- Щелкните Макросы. Это покажет вам все макросы в вашей книге (или личной книге макросов).
- Нажмите кнопку «Добавить».
- Щелкните ОК.
Теперь у вас будет значок макроса в QAT.
Чтобы использовать этот макрос одним щелчком, просто выберите и щелкните значок макроса.
Примечание. Если вы сохраняете код макроса VBA в книге, вам необходимо сохранить книгу в формате с поддержкой макросов (XLSM).