Note: The other languages of the website are Google-translated. Back to English
登入  \/ 
x
or
x
註冊  \/ 
x

or

如何在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. 然後點擊 doc-reverse-sign-6 按鈕運行代碼。 所選範圍內的數字符號立即被反轉。


箭頭藍色右氣泡 使用Kutools for Excel快速反轉單元格中的值的符號

我們可以使用 改變價值觀 的特點 Kutools for Excel.

Kutools for Excel : 帶有300多個便捷的Excel加載項,可以在30天內免費試用

安裝後 Kutools for Excel,請執行以下操作:

1. 選擇要反轉數字符號的範圍。

2。 點擊 庫工具 > 內容 > 改變價值觀…,請參見屏幕截圖:

3。 在 改變價值觀 對話框,檢查 反轉所有值的符號,請參見屏幕截圖:

doc值反向符號5

4。 然後點擊 OK or 登記。 數字的所有符號均已反轉。

改變價值觀 Kutools for Excel也可以 修復尾隨的負號,C將所有負值保留為正數將所有正值更改為負值。 有關更多詳細信息 改變價值觀, 請拜訪 更改值的符號功能描述.

點擊下載並立即免費試用Excel的Kutools!


箭頭藍色右氣泡 使用Kutools for Excel反轉單元格中值的符號

Kutools for Excel:具有300多個方便的Excel加載項,可以在30天內免費試用,沒有任何限制。 立即下載並免費試用!

相關文章:


最佳辦公效率工具

Kutools for Excel解決了您的大多數問題,並使您的生產率提高了80%

  • 重用: 快速插入 複雜的公式,圖表 以及您以前使用過的任何東西; 加密單元 帶密碼 創建郵件列表 並發送電子郵件...
  • 超級公式欄 (輕鬆編輯多行文本和公式); 閱讀版式 (輕鬆讀取和編輯大量單元格); 粘貼到過濾範圍...
  • 合併單元格/行/列 不會丟失數據; 拆分單元格內容; 合併重複的行/列...防止細胞重複; 比較範圍...
  • 選擇重複或唯一 行; 選擇空白行 (所有單元格都是空的); 超級查找和模糊查找 在許多工作簿中; 隨機選擇...
  • 確切的副本 多個單元格,無需更改公式參考; 自動創建參考 到多張紙; 插入項目符號,複選框等...
  • 提取文字,添加文本,按位置刪除, 刪除空間; 創建和打印分頁小計; 在單元格內容和註釋之間轉換...
  • 超級濾鏡 (將過濾方案保存並應用於其他工作表); 高級排序 按月/週/日,頻率及更多; 特殊過濾器 用粗體,斜體...
  • 結合工作簿和工作表; 根據關鍵列合併表; 將數據分割成多個工作表; 批量轉換xls,xlsx和PDF...
  • 超過300種強大功能。 支持Office / Excel 2007-2019和365。支持所有語言。 在您的企業或組織中輕鬆部署。 完整功能30天免費試用。 60天退款保證。
kte選項卡201905

Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 每天將您的工作效率提高50%,並減少數百次鼠標單擊!
officetab底部
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Aaron · 3 years ago
    Tried to do the developer option and I get a:
    Run-time error '438':
    Object doesn't support this property or method


    Any ideas?
  • To post as a guest, your comment is unpublished.
    Taylor · 5 years ago
    I have a mac version of excel and the paste special doesn't allow me to do that :-/ dying.
    • To post as a guest, your comment is unpublished.
      Bob · 4 years ago
      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).
  • To post as a guest, your comment is unpublished.
    Emoployee · 5 years ago
    Thx Bro for the useful thread
  • To post as a guest, your comment is unpublished.
    Hasan · 5 years ago
    Thank you so much, Sir. You helped me a lot. I hope I can return the favor. Thank you so much.
  • To post as a guest, your comment is unpublished.
    CHRISTINE · 6 years ago
    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...
  • To post as a guest, your comment is unpublished.
    David K · 6 years ago
    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.
  • To post as a guest, your comment is unpublished.
    Kevin Cox · 6 years ago
    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...
  • To post as a guest, your comment is unpublished.
    Michaelq · 6 years ago
    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.
    • To post as a guest, your comment is unpublished.
      Vikas · 6 years ago
      Michaelq please use abs function. It turns negative and positive values both to positive one.
  • To post as a guest, your comment is unpublished.
    wolfstevent · 7 years ago
    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.
  • To post as a guest, your comment is unpublished.
    JAVED KHAN · 7 years ago
    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

    :-)
    • To post as a guest, your comment is unpublished.
      Jonathan · 6 years ago
      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.
    • To post as a guest, your comment is unpublished.
      CPH1810 · 7 years ago
      You sir have just blown my mind with your Excel Ninja ways, I tip my hat to you sir :lol:
    • To post as a guest, your comment is unpublished.
      wolfstevent · 7 years ago
      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.