跳到主要内容

如何在Excel中反转单元格中值的符号？

当我们使用excel时，工作表中既有正数也有负数。 假设我们需要将正数更改为负数，反之亦然。 当然，我们可以手动更改它们，但是如果需要更改数百个数字，则此方法不是一个好的选择。 是否有任何快速的技巧来解决此问题？

使用选择性粘贴功能反转单元格中值的符号

用VBA代码反转单元格中值的符号

反转单元格中值的符号 Kutools for Excel 很快

箭头蓝色右气泡 使用选择性粘贴功能反转单元格中值的符号

我们可以使用 选择性粘贴 Excel中的函数，请执行以下操作：

1。 分接头号 -1 在空白单元格中并将其复制。

2。 选择要反转值符号的范围，右键单击并选择 选择性粘贴。 看截图：

3. 在 选择性粘贴 对话框，单击 所有类型 来自的选项 酱 和 乘 选项 操作。 看截图：

4。 然后点击 OK，并且该范围内数字的所有符号均已反转。

5。 根据需要删除数字-1。

一次反转所有数字的符号

Kutools for Excel“ 改变价值观 实用程序可以帮助您将正数更改为负数，反之亦然，还可以帮助您反转值的正负号并将尾随的负号修复为正常值。  点击下载 Kutools for Excel!

Kutools for Excel：具有300多个方便的Excel加载项，可以在30天内免费试用，没有任何限制。 立即下载并免费试用！

箭头蓝色右气泡 用VBA代码反转单元格中值的符号

同样，我们可以使用VBA代码来反转单元格中值的符号。 但是我们必须知道如何让VBA来做这件事。 我们可以按照以下步骤进行操作：

1. 选择要反转单元格中值的符号的范围。

2。 点击 开发商 > Visual Basic中 在Excel中， 适用于应用程序的Microsoft Visual Basic 将显示窗口，或使用快捷键（Alt + F11）激活它。 然后点击 插页 > 模块，然后复制并粘贴以下VBA代码：

Sub Convert()
Dim C As Range
For Each C In Selection
C.Value = -C.Value
Next C
End Sub

3. 然后点击 文档反向符号 6 按钮运行代码。 所选范围内的数字符号立即被反转。

箭头蓝色右气泡 反转单元格中值的符号 Kutools for Excel 很快

我们可以使用 改变价值观 的特点 Kutools for Excel.

Kutools for Excel : 带有300多个便捷的Excel加载项，可以在30天内免费试用. 
前往下载
30天免费试用购买
贝宝/ MyCommerce

安装后 Kutools for Excel，请执行以下操作：

1. 选择要反转数字符号的范围。

2。 点击 库工具 > 内容 > 改变价值观…，请参见屏幕截图：

3。 在 改变价值观 对话框，检查 反转所有值的符号，请参见屏幕截图：

doc值反向符号5

4。 然后点击 OK or 使用。 数字的所有符号均已反转。

改变价值观 of Kutools for Excel 还可以 修复尾随的负号，C将所有负值保留为正数 和 将所有正值更改为负值。 有关更多详细信息 改变价值观，请访问： 更改值的符号功能描述.

点击下载并免费试用 Kutools for Excel 现在！

箭头蓝色右气泡 反转单元格中值的符号 Kutools for Excel

Kutools for Excel：具有300多个方便的Excel加载项，可以在30天内免费试用，没有任何限制。 立即下载并免费试用！

相关文章：

最佳办公生产力工具

热门特色: 查找、突出显示或识别重复项   |  删除空白行   |  合并列或单元格而不丢失数据   |   不使用公式进行四舍五入 ...
超级查询: 多条件VLookup  |   多值VLookup  |   跨多个工作表的 VLookup   |   模糊查询 ....
高级下拉列表: 快速创建下拉列表   |  依赖下拉列表   |  多选下拉列表 ....
列管理器: 添加特定数量的列  |  移动列  |  切换隐藏列的可见性状态  |  比较范围和列 ...
特色功能: 网格焦点   |  设计图   |   大方程式酒吧   |  工作簿和工作表管理器   |  资源库 （自动文本）   |  日期选择器   |  合并工作表   |  加密/解密单元格   |  按列表发送电子邮件   |  超级筛选   |   特殊过滤器 （过滤粗体/斜体/删除线...）...
前 15 个工具集:  12 文本 工具 (添加文本, 删除字符，...）   |   50+ 图表 类型 (甘特图，...）   |   40+ 实用 公式 (根据生日计算年龄，...）   |   19 插入 工具 (插入二维码, 从路径插入图片，...）   |   12 转化 工具 (小写金额转大写, 货币兑换，...）   |   7 合并与拆分 工具 (高级组合行, 分裂细胞，...）   |   ... 和更多

增强您的 Excel 技能 Kutools for Excel，体验前所未有的效率。 Kutools for Excel 提供 300 多种高级功能，可提高工作效率并节省时间。  单击此处获取您最需要的功能...

kte选项卡201905

Office Tab 为 Office 带来选项卡式界面，让您的工作更轻松

  • 在Word，Excel，PowerPoint中启用选项卡式编辑和阅读，发布者，Access，Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50％，并减少数百次鼠标单击！
Comments (15)
No ratings yet. Be the first to rate!
Aaron
Aaron
This comment was minimized by the moderator on the site
Tried to do the developer option and I get a:
Run-time error '438':
Object doesn't support this property or method


Any ideas?
Reply
0
0
Taylor
Taylor
This comment was minimized by the moderator on the site
I have a mac version of excel and the paste special doesn't allow me to do that :-/ dying.
Reply
0
0
Bob
Bob     Taylor
This comment was minimized by the moderator on the site
Yes. Yes it does. Enter "-1" in a cell. Copy that cell. Highlight all the values whose sign you wish to flip. Opt-Cmd-V opens paste special dialog box, choose multiply from the Operations section (I also find it beneficial to choose "values" in the Paste Section rather than "all" to preserve any formatting).
Reply
0
0
Emoployee
Emoployee
This comment was minimized by the moderator on the site
Thx Bro for the useful thread
Reply
0
0
Hasan
Hasan
This comment was minimized by the moderator on the site
Thank you so much, Sir. You helped me a lot. I hope I can return the favor. Thank you so much.
Reply
0
0
CHRISTINE
CHRISTINE
This comment was minimized by the moderator on the site
thank you i had just posted in the SUN system with the srong month. this has helped me to reverse all the 195 entries within 7 minutes...
Reply
0
0
David K
David K
This comment was minimized by the moderator on the site
From my perspective it seems there is no foolproof way to simply remove a negative sign: Multiplying by -1 can screw up subsequent cell values if each one is using a formula. VBA requires saving in a different format. Kutools requires you to download/purchase an add-on. You can format a column to show negatives as positives, but it turns the font Red.
Reply
0
0
Kevin Cox
Kevin Cox
This comment was minimized by the moderator on the site
I'm looking to add multiple $ to cells with formulas example (=((I29+AM29+AV29+BH29+CG29+CP29+DS29+AJ78+BA91+BU91+CO91+DK78)-(V25+EF25+EN25))/4 Is there away to do this quickly...
Reply
0
0
Michaelq
Michaelq
This comment was minimized by the moderator on the site
This is tangentially related. I have a workbook in Excel 2013 that's baffling me. I get a #VALUE! error with the formula =SUM(8-(F4:F23)) in cell F2. When I reverse that, =SUM(F4:F23)-8), I get (#) where (#) is the negative of what I expect. But when I use #Jonathon's formula with my working formula in it, I again get the #VALUE! error. How is this #VALUE! error even possible? What I want is for F2 to be a positive number that's the leftover balance when F4:F24 is less that 8.
Reply
0
0
Vikas
Vikas     Michaelq
This comment was minimized by the moderator on the site
Michaelq please use abs function. It turns negative and positive values both to positive one.
Reply
0
0
wolfstevent
wolfstevent
This comment was minimized by the moderator on the site
Very cool trick. I imported transactions from BofA and AMEX and of course they use different signs for debits. One multiply paste later and I'm all fixed up. Thanks.
Reply
0
0
JAVED KHAN
JAVED KHAN
This comment was minimized by the moderator on the site
In a New Cell simply Multiply a the Cell-value by -1. It will reverse automatically. No need to perform such a long procedures. e.g if the value in B1 is -25 then in Cell C1 enter the formula : =B1 * -1 :-)
Reply
0
0
Jonathan
Jonathan     JAVED KHAN
This comment was minimized by the moderator on the site
OMG THANK YOU SOOO MUCH. I have been looking all over the place for a way to simply do this. I was trying to find a way to have Excel take the SUM of a group of numbers and make it negative so that it would automatically do all the rest of the math on the sheet correctly. If anyone else is looking to do something similar, using the information I just obtained from #JAVED KHAN, enter the following function (this is just an example, change the cell references to match the ranges you are needing to use) =SUM(B2:H2) * -1 This will now take the sum of the range and automatically change the value to a negative number.
Reply
0
0
CPH1810
CPH1810     JAVED KHAN
This comment was minimized by the moderator on the site
You sir have just blown my mind with your Excel Ninja ways, I tip my hat to you sir :lol:
Reply
0
0
wolfstevent
wolfstevent     JAVED KHAN
This comment was minimized by the moderator on the site
The goal was to change an entire range of cells easily, not just a single cell. The paste multiply method is basically a one step procedure to do just that.
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations