При некоторых расчетах в электронных таблицах Microsoft Excel получается так, что отрицательные числа становятся положительными или положительные отрицательными, что нужно изменить, нормализовав тем самым отображение содержимого.
Я представлю два метода, выполняющих перевод значений в ячейках, а вы выберите тот, который придется по душе.
Способ 1: Формула + вспомогательный столбец
Этот метод хорош тем, что не требует ручной манипуляции со специальными вставками, о которых пойдет речь в следующем варианте. Однако придется использовать формулу, чтобы достичь желаемого результата. Дело в том, что если просто умножить ячейки на -1, а затем перенести их, в пустых клетках появится 0.
Если же пустых клеток у вас нет, просто умножьте значение на -1 во вспомогательным столбце, растяните формулу, а затем перенесите ее так, как это будет показано в конце следующей инструкции.
Что касается создания функции, то в этом случае идеальным вариантом будет ЕСЛИ, чтобы исключить пустые ячейки. Давайте более детально разберемся с ее созданием и настройкой вспомогательного столбца.
-
Напротив текущей таблицы в пустой клетке введите =ЕСЛИ(), объявив тем самым интересующую нас функцию.
-
Затем впишите условие A1<>»». Это означает, что действие нужно выполнить, когда в ячейке нет пустоты.
-
После этого введите само действие, то есть умножьте ячейку на -1, записав это как A1*-1;. После единицы обязательно поставьте точку с запятой.
-
Если же условие не удовлетворяется, то есть ячейка пустая, ее нужно оставить пустой. Укажите это в формуле как «».
-
Нажмите Enter и ознакомьтесь с результатом. Вы видите, что получили значение без минуса или с ним, в зависимости от изначального числа.
-
Растяните функцию на необходимое число клеток в сторону и вниз, чтобы покрыть все ячейки таблицы, после чего скопируйте ее, нажав сочетание клавиш Ctrl + C.
-
Щелкните правой кнопкой мыши по первому значению изначальной таблицы и из контекстного меню выберите параметр вставки «Значение». Иконку вы видите на следующем скриншоте.
-
Как видно, вставка прошла успешно – теперь вспомогательный столбец можно удалить, что никак не повлияет на отображение оригинальных чисел.
Внимательно ознакомьтесь со скриншотами, и у вас все получится! Если хотите, просто вставьте готовую формулу, заменив номер ячейки на свой. Выглядит она так: =ЕСЛИ(A1<>»»;A1*-1;»»).
Комьюнити теперь в Телеграм
Подпишитесь и будьте в курсе последних IT-новостей
Подписаться
Способ 2: Умножение на значение
Данный метод тоже подразумевает умножение ячейки на -1, чтобы убрать отрицательное или положительное значение. Однако в этом случае вспомогательный столбец не понадобится. Вся сложность заключается лишь в том, чтобы отделить пустые ячейки, о чем вы и узнаете в следующей инструкции.
-
В любой пустой ячейке напишите -1, затем скопируйте ее (именно ячейку, а не значение).
-
Выделите таблицу с данными и нажмите Ctrl + G для вызова необходимого меню.
-
В окне «Переход» кликните по кнопке «Выделить».
-
Отметьте маркером пункт «Константы» и примените изменения.
-
Сейчас вы снимаете выделение с тех ячеек, в которых ничего не содержится, что и видно на скриншоте ниже.
-
Остается кликнуть по любой ячейке ПКМ и выбрать пункт «Специальная вставка».
-
В блоке «Вставить» отметьте маркером «Значения».
-
После этого укажите операцию «Умножить». Вы уже поняли, что таким образом число -1, скопированное ранее, умножится на все значения в выделенных ячейках.
-
После нажатия на «ОК» в окне вы увидите, что изменения вступили в силу, а в пустых ячейках 0 не появился.
Два этих метода крайне просты в реализации. Используйте любой из них на ваше усмотрение, результат будет одинаковым.
Содержание:
- Умножьте на минус 1, чтобы преобразовать отрицательное число в положительное
- Используйте функцию ABS, чтобы изменить все отрицательные числа на положительные
- Умножьте с помощью специальной вставки, чтобы перевернуть знак
- Мигающая заливка для удаления отрицательного знака
- Преобразование отрицательных чисел в положительные одним щелчком мыши (VBA)
Посмотреть видео — Преобразование отрицательного числа в положительное в Excel
[lyte id=’qDM6gbGjUkE’ /]
Большинство людей, работающих с электронными таблицами Excel, имеют дело с числами в больших / малых наборах данных.
И когда вы работаете с числами, у вас будут все их типы (положительные, отрицательные, десятичные, дата / время).
Одной из распространенных задач, которые нам часто приходится выполнять, является преобразование этих чисел из одного формата в другой.
И, наверное, самый распространенный — когда вам нужно заменить отрицательные числа на положительные (убрать отрицательный знак) для некоторых расчетов.
И снова в Excel есть несколько способов сделать это.
В этом уроке я покажу вам несколько простых способов изменить отрицательные числа на положительные в Excel (используя формулы, технику копирования и вставки и другие замечательные методы).
Так что, если вам интересно, продолжайте читать!
Умножьте на минус 1, чтобы преобразовать отрицательное число в положительное
Если у вас есть столбец, полный чисел, и вы хотите быстро получить числа, в которых отрицательные значения были преобразованы в положительные, вы можете легко сделать это, умножив эти отрицательные значения на -1.
Но вы также должны убедиться, что вы умножаете только отрицательные числа, а не положительные.
Предположим, у вас есть набор данных, как показано ниже:
Ниже приведена формула, которая преобразует отрицательные числа в положительные, а остальные оставит без изменений:
= ЕСЛИ (A2> 0; A2; -A2)
Вышеупомянутая формула использует функцию ЕСЛИ, чтобы сначала проверить, является ли число положительным или нет. Если он положительный, знак не меняется, а если отрицательный, к ссылке добавляется отрицательный знак, что дает нам только положительное число.
Если у вас также есть текстовые значения в наборе данных, эта функция будет игнорировать это (и будут изменены только отрицательные значения)
Теперь, когда у вас есть требуемый результат, вы можете преобразовать эти формулы в значения (и скопировать их поверх исходных данных, если они вам не нужны).
Используйте функцию ABS, чтобы изменить все отрицательные числа на положительные
В Excel есть специальная функция, которая удаляет отрицательный знак и дает вам абсолютное значение.
… Функция ABS
Предположим, у вас есть набор данных, показанный ниже, и вы хотите изменить отрицательные значения на положительные.
Ниже приведена формула, которая сделает это:
= АБС (A2)
Вышеупомянутая функция ABS не влияет на положительные числа, но преобразует отрицательные числа в положительные.
Теперь, когда у вас есть требуемый результат, вы можете преобразовать эти формулы в значения (и скопировать их поверх исходных данных, если они вам не нужны).
Одним из незначительных недостатков функции ABS является то, что она может работать только с числами. Если у вас есть текстовые данные в некоторых ячейках и вы используете функцию ABS, она даст вам #VALUE! ошибка.
Умножьте с помощью специальной вставки, чтобы перевернуть знак
Если вы хотите изменить знак числа (т. Е. Изменить отрицательный на положительный и положительный на отрицательный), вы также можете использовать эту специальную технику умножения вставки.
Предположим, у вас есть набор данных, показанный ниже, и вы хотите перевернуть знак.
Ниже приведены инструкции по изменению знака с помощью специальной вставки:
- В любой пустой ячейке на листе введите -1.
- Скопируйте эту ячейку (со значением -1)
- Выберите диапазон, в котором вы хотите перевернуть знак.
- Щелкните правой кнопкой мыши любую из выбранных ячеек
- Щелкните Специальная вставка. Откроется диалоговое окно Специальная вставка.
- В разделе «Вставить» выберите «Значения».
- В параметрах операции выберите «Умножить».
- Нажмите ОК
- Удалить -1 из ячейки
Вы могли бы заметить, что приведенные выше шаги мгновенно меняют знак числа на противоположный (т.е. положительные числа становятся отрицательными, а отрицательные числа становятся положительными).
Но что, если вы хотите преобразовать только отрицательные числа в положительные, а не наоборот?
В этом случае вам каким-то образом сначала нужно выбрать все отрицательные числа, а затем выполнить вышеуказанные шаги.
Вот как выбрать в Excel только отрицательные числа
- Выбрать весь набор данных
- Удерживая клавишу Ctrl, нажмите клавишу F. Откроется диалоговое окно «Найти и заменить».
- В поле «Найти» введите — (знак минус)
- Нажмите «Найти все«
- Удерживая контрольную клавишу, нажмите клавишу A.
Вышеупомянутые шаги будут выбирать только те ячейки, которые имеют отрицательный знак. Теперь, когда у вас выделены эти ячейки, вы можете использовать технику специальной вставки, чтобы изменить знак только отрицательных чисел.
Этот метод имеет два преимущества по сравнению с методом формул (два метода, описанные до этого):
- Вам не нужно добавлять дополнительный столбец, а затем использовать формулу для получения результата в этом столбце. Вы можете использовать это в существующем наборе данных.
- Вам не нужно преобразовывать формулы в значения (поскольку результат, который вы получаете, уже является значением, а не формулой)
Мигающая заливка для удаления отрицательного знака
Flash Fill — это новая функция, представленная в Excel 2013. Это позволяет вам быстро идентифицировать закономерности, а затем дать вам результат, в котором шаблон был применен ко всему набору данных.
Это можно использовать, когда у вас есть имена, и вы хотите разделить имя и фамилию. Как только вы несколько раз наберете имя в соседней ячейке, Flash Fill определит шаблон и выдаст вам все имена.
Точно так же вы можете использовать его, чтобы быстро убрать отрицательный знак от числа, а положительные значения остаются неизменными.
Ниже приведен набор данных, в котором у меня есть отрицательные числа, и я хочу изменить их на положительные значения.
Ниже приведены шаги по замене отрицательных чисел на положительные с помощью Flash Fill:
- В поле рядом с таблицей с данными введите ожидаемый результат вручную. В этом примере я вручную введу 874
- В ячейке под ним введите ожидаемый результат (162 в этом примере).
- Выберите обе ячейки
- Поместите курсор в нижнюю правую часть выделения. Он изменится на значок плюса
- Щелкните и перетащите, чтобы заполнить столбец (или дважды щелкните)
- Щелкните значок Параметры автозаполнения.
- Нажмите на Flash Fill.
Вышеупомянутые шаги дадут вам ожидаемый результат, где отрицательный знак был удален.
При использовании этого метода вам нужно помнить, что Excel полагается на угадывание шаблона. Так что вам придется хотя бы показать Excel, что вы конвертируете отрицательное число в положительное.
Это означает, что вам придется вручную вводить ожидаемый результат, пока вы не накроете хотя бы одно отрицательное число.
Преобразование отрицательных чисел в положительные одним щелчком мыши (VBA)
И, наконец, вы также можете использовать VBA для преобразования отрицательных значений в положительные.
Я бы порекомендовал использовать этот метод, если вам приходится делать это часто. Возможно, вы регулярно получаете набор данных из базы данных или от коллег, и вам нужно делать это каждый раз.
В таком случае вы можете создать и сохранить код макроса VBA в личной книге макросов и поместить VBA на панель быстрого доступа. Таким образом, в следующий раз, когда вы получите набор данных, в котором вам нужно это сделать, вы просто выберите данные и щелкните значок в QAT…
… И все будет готово!
Не волнуйтесь, я покажу вам точные шаги, как это настроить и запустить.
Ниже приведен код VBA, который преобразует отрицательные значения в положительные значения в выбранном диапазоне:
Sub ChangeNegativetoPOsitive () для каждой выделенной ячейки, если Cell.Value <0, то Cell.Value = -Cell.Value End, если следующая ячейка End Sub
В приведенном выше коде используется цикл For Next для просмотра каждой выделенной ячейки. Он использует оператор IF, чтобы проверить, является ли значение ячейки отрицательным или нет. Если значение отрицательное, знак меняется на обратный, если нет, он игнорируется.
Вы можете добавить этот код в обычный модуль в книге (если вы хотите использовать его только в этой книге). И если вы хотите использовать этот код макроса в любой книге в вашей системе, вы можете сохранить его в личной книге макросов.
Вот шаги, чтобы получить личную книгу макросов (PMW).
Вот шаги, чтобы сохранить этот код в PMW.
Теперь позвольте мне показать вам, как добавить этот код на панель быстрого доступа (шаги одинаковы, независимо от того, сохраняете ли вы этот код в отдельной книге или в PMW)
- Откройте книгу, в которой у вас есть данные
- Добавьте код VBA в книгу (или в PMW)
- Нажмите на опцию «Настроить панель быстрого доступа» в QAT.
- Нажмите «Дополнительные команды».
- В диалоговом окне «Параметры Excel» нажмите раскрывающийся список «Выбрать команды из».
- Щелкните Макросы. Это покажет вам все макросы в книге (или в личной книге макросов).
- Нажмите кнопку «Добавить».
- Нажмите ОК.
Теперь у вас будет значок макроса в QAT.
The majority of employees who work with Excel spreadsheets are responsible for dealing with numbers included in either large or small datasets. When you work with numbers, you will encounter all varieties of them, including positive and negative numbers, decimals, date and time, and so on. The process of converting these numbers from one format to another is one of the more typical tasks that a lot of us need to accomplish on a regular basis.
When performing some computations, you may find that you need to convert negative numbers to positive numbers or to zero, that is removing the negative sign. This is probably the situation that arises the most frequently. If you are patient enough, you can certainly do it manually by going through each instance one at a time.
The majority of us are likely to consider sneaky tactics. Excel has a number of shortcuts that make it possible to convert negative numbers to positive numbers quickly and easily.
In this tutorial, we are going to learn to change the negative numbers to positive numbers through the following example.
Using the ABS Function
Use the built-in ABS function to convert negative integers to positive numbers while keeping positive numbers unchanged
Step 1
In our example, we have some negative numbers along with some positive numbers in our Excel sheet, as shown in the following screenshot.
Step 2
Then, in a cell which is empty, add the below given formula in formula bar and press enter.
In our example, we have selected B2 and added the below given formula.
=ABS(A2)
In our example, we apply formula for A2 cell. You can change as per your need. See the below given image.
Step 3
You can select the range in which you want this formula to be applied by dragging the fill handle to the desired location. See the below given image.
Using the IF Function
The IF function does a logical test, and it returns True value if the test is successful, and it returns a False value if the test is unsuccessful.
Step 1
In the above example, we can apply the below given formula to make the negative number to positive number.
=IF(A2>0,A2,-A2)
After entering the formula press enter. See the below given image.
Step 2
You can select the range in which you want this formula to be applied by dragging the fill handle to the desired location. See the below given image.
Conclusion
In this tutorial, we explained two methods (ABS function and IF function) that you can use in Excel to convert negative numbers to positive numbers.
I will show you a few ways to change negative numbers to positive numbers and back again in Excel.
Don’t forget to download the accompanying workbook so you can follow along with the tutorial.
Method 1 — Convert Negative Values to Positive Values
You can use the «paste special» method to change numbers from negative to positive or positive to negative.
- Add a cell with -1 in it near your negative numbers:
- Copy the cell with the -1, just select the cell and hit Ctrl + C:
- Select the negative numbers:
- Right-click over the negative numbers and click Paste Special:
- Select the option Multiply and then hit OK:
- Now, you have a colum of positive values:
- Simply delete the -1 and you are done.
Method 2 — Convert Positive Values to Negative Values
To convert any series of positive numbers to negative numbers, simply follow the exact same steps as in Method 1 above; the only difference is that you will start with positive numbers in the original list this time. Remember to keep -1 the same since multiplying a positive number by a negative number will create a negative result.
Method 3 — ABS() Function to Convert Negative Value to Positive Values
You can also use the ABS() or absolute value function to return a number without its sign. This will create a series of positive numbers from any set of negative numbers or a mix of negative and positive numbers.
Whereas Method 1 may cause you to end up with mixed results if you had some negative and some positive values in the starting list, this method will only allow for positive numbers to be created, no matter what.
- Enter =ABS(Cell Reference) into the first cell next to the list of numbers:
Note that the cell reference refers to the first number in the list. - Copy the function down the entier list by selecting the first cell, B1, and double-clicking the bottom-right side of the cell:
- Now, you can stop here if you don’t mind having two columns of data, one containing the raw data and the other containg functions, or, you can continue to make it one list — copy the new column of numbers (the positive ones):
- Then select the original column of numbers and hit Alt + E + S + V and Enter (this will copy-paste-special values over the original list):
- Now, delete the second column that has all of the functions and you are done.
Notes
Use whichever method is easiest for you. I prefer Method 3 just because I can do it in a couple seconds using keyboard shortcuts that I am familiar with.
The benefit of the first method is that it can be used to go from positive to negative or negative to positive.
The benefit of the second method is that, if you want a list of only positive values and the original list contains a mix of positive and negative values, the second method will ensure that all values end up being positive, whereas Method 1 and 2 will simply reverse the sign of each number, regardless of whether or not all of the values in the list are positive, negative, or a mix of the two.
Similar Content on TeachExcel
Make All Numbers in a List Positive in Excel
Tutorial:
Take a list of numbers and make them all positive, regardless of whether the list contain…
Convert Time to Minutes and Vice Versa in Excel
Tutorial: How to convert a time into minutes in Excel and also how to get minutes back into a time f…
Make Column Headings Numbers instead of Letters — Make R1C1 Style References in Excel 2007
Tutorial: In Microsoft Excel you reference columns as letters by default — A1, B3, C5, etc. But you …
Format Cells in The Number (Numerical) Number Format in Excel
Macro: This free Excel macro formats selected cells in the Number or Numerical number format in E…
Format Cells in The Long Date Number Format in Excel
Macro: This free Excel macro formats a selection of cells in the Long Date number format in Excel…
Format Cells in The Short Date Number Format in Excel
Macro: This free Excel macro applies the Short Date number format to a selection of cells in Exce…
Subscribe for Weekly Tutorials
BONUS: subscribe now to download our Top Tutorials Ebook!
Когда вы обрабатываете операции в Excel, иногда вам может потребоваться заменить отрицательные числа на положительные или наоборот. Есть ли какие-нибудь быстрые приемы, которые можно применить для изменения отрицательных чисел на положительные? Эта статья познакомит вас со следующими трюками для простого преобразования всех отрицательных чисел в положительные и наоборот.
- Измените отрицательные числа на положительные с помощью специальной функции Вставить;
- Использование кода VBA для преобразования всех отрицательных чисел диапазона в положительные;
- Легко изменить отрицательные числа на положительные с помощью Kutools for Excel.
Измените отрицательные числа на положительные с помощью специальной функции Вставить
Вы можете изменить отрицательные числа на положительные, выполнив следующие действия:
1. Введите номер -1 в пустой ячейке, затем выберите эту ячейку и нажмите Ctrl + C ключи для его копирования.
2. Выделите все отрицательные числа в диапазоне, щелкните правой кнопкой мыши и выберите Специальная … из контекстного меню. Смотрите скриншот:
Ноты:
(1) Удержание Ctrl клавишу, вы можете выбрать все отрицательные числа, щелкая их по одному;
(2) Если у вас есть Kutools for Excel установлен, вы можете применить его Выберите специальные ячейки функция быстрого выбора всех отрицательных чисел. Получите бесплатную пробную версию!
3, И Специальная вставка появится диалоговое окно, выберите Все вариант из Вставить, наведите на Размножаться вариант из Эксплуатация, нажмите OK. Смотрите скриншот:
4. Все выбранные отрицательные числа будут преобразованы в положительные числа. Удалите число -1, как вам нужно. Смотрите скриншот:
Легко изменить отрицательные числа на положительные в указанном диапазоне в Excel
По сравнению с удалением отрицательного знака из ячеек вручную по одной, Kutools for Excel’s Изменить знак ценностей Функция предоставляет чрезвычайно простой способ быстро изменить все отрицательные числа на положительные при выборе.
Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия 30-день, кредитная карта не требуется! Get It Now
Использование кода VBA для преобразования всех отрицательных чисел диапазона в положительные
Как профессионал в Excel, вы также можете запустить код VBA, чтобы изменить отрицательные числа на положительные.
1. Нажмите клавиши Alt + F11, чтобы открыть окно Microsoft Visual Basic для приложений.
2. Откроется новое окно. Нажмите Вставить > Модули, затем введите в модуль следующие коды:
Sub Positive
Dim Cel As Range
For Each Cel In Selection
If IsNumeric(Cel.Value) Then
Cel.Value = Abs(Cel.Value)
End If
Next Cel
End Sub3. Затем нажмите Run или нажмите F5 нажмите клавишу для запуска приложения, и все отрицательные числа будут заменены на положительные. Смотрите скриншот:
Быстро и легко меняйте отрицательные числа на положительные с помощью Kutools for Excel
Большинство пользователей Excel не хотят использовать код VBA, есть ли какие-нибудь быстрые приемы для изменения отрицательных чисел на положительные? Kutools for excel может помочь вам легко и комфортно добиться этого.
Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия 30-день, кредитная карта не требуется! Get It Now
1. Выберите диапазон, содержащий отрицательные числа, которые вы хотите изменить, и нажмите Кутулс > Содержание > Изменить знак ценностей.
2. Проверить Измените все отрицательные значения на положительные под Эксплуатацияи нажмите Ok. Смотрите скриншот:
Теперь вы увидите, что все отрицательные числа меняются на положительные, как показано ниже:
Внимание: С этим Изменить знак ценностей вы также можете исправить отрицательные знаки в конце, изменить все положительные числа на отрицательные, поменять местами знак всех значений и изменить все отрицательные значения на ноль. Получите бесплатную пробную версию!
(1) Быстро измените все положительные значения на отрицательные в указанном диапазоне:
(2) Легко изменить знак всех значений в указанном диапазоне:
(3) Легко изменить все отрицательные значения на ноль в указанном диапазоне:
(4) Легко исправить конечные отрицательные знаки в указанном диапазоне:
Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия 30-день, кредитная карта не требуется! Get It Now
Демонстрация: измените отрицательные числа на положительные или наоборот с помощью Kutools for Excel
Относительные статьи:
Лучшие инструменты для работы в офисе
Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%
- Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
- Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
- Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
- Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
- Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Комментарии (31)
Номинальный 4.5 из 5
·
рейтинги 1