提示:其他語言是Google翻譯的。 你可以訪問 English 版本。
登入
x
or
x
x
註冊
x

or

如何在Excel中用0或其他特定值填充空白單元格?

假設您有一個填充了數百個空白單元格的大型工作表,現在您希望用您喜歡的特定值填充空白單元格,例如0的數量。 當然,您可以手動填充或逐個複制粘貼,但這不是填充大空白單元格的好選擇。 今天,我會介紹一些快速的技巧來解決這個問題。

使用“轉至特殊”功能使用0或其他特定值填充空白單元格

使用VBA代碼填充0或其他特定值的空白單元格

用Kutools for Excel快速填充0或其他特定值的空白單元格

使用Kutools for Excel填充空白單元格的值高於/低於/向右/左


用0或任何其他特定值填充空白單元格:

Kutools for Excel填充空白單元格 功能可以幫助您使用上面的值填充空白單元格,使用班輪值,並根據需要使用特定值。

DOC填充細胞與 -  0-9,9

Kutools for Excel:比200方便的Excel加載項,可以在60天免費試用。 下載並免費試用現在!



箭頭藍色右泡 使用“轉至特殊”功能使用0或其他特定值填充空白單元格

去特別 函數,你可以用任何你想要的值來填充空白單元格。

1。 選擇要填充空白單元格的範圍。

2。 點擊 主頁 > 查找和選擇 > 去Specia我打開了 去特別 對話框。 然後選擇 空白 選項 選擇 部分。 看截圖:

DOC填充細胞與 -  0-1,1

3。 然後點擊 OK。 所有的空白單元格將被選中。 請不要做任何事情,只需輸入您想要的數字或文字,然後按 Ctrl + Enter 鍵在一起,並且所有的空白單元格都填充了文本“null”。 查看截圖:

DOC填充細胞與 -  0-2,2  2 DOC填充細胞與 -  0-3,3

當然你也可以用0的號碼填充這些選中的空白單元格。


箭頭藍色右泡 使用VBA代碼填充0或其他特定值的空白單元格

您也可以應用以下VBA代碼來填充具有特定值的空白單元格。 請做如下:

1. 突出顯示範圍包含多個空白單元格。

2. 點擊 開發人員 > Visual Basic中,一個新的 Microsoft Visual Basic的應用程序 窗口將會顯示,點擊 插入 > 模塊,然後將以下代碼複製並粘貼到模塊中:

Sub FillEmptyBlankCellWithValue()
Dim cell As Range
Dim InputValue As String
On Error Resume Next
InputValue = InputBox("Enter value that will fill empty cells in selection", _
"Fill Empty Cells")
For Each cell In Selection
If IsEmpty(cell) Then
cell.Value = InputValue
End If
Next
End Sub

3. 點擊 DOC-乘法計算-3 按鈕來運行代碼,並出現一個提示框。 您可以輸入任何值到您需要的空白框中。 看截圖:

DOC填充細胞與 -  0-4,4

提示: 如果您需要用0編號填充空白單元格,則需要在此對話框中輸入0。

4。 然後點擊 OK 結束這個過程。 並且指定的值一次插入到空白單元格中。


箭頭藍色右泡 用Kutools for Excel快速填充0或其他特定值的空白單元格

如果你已經安裝 Kutools for Excel,其 填充空白單元格...函數可以幫助您快速方便地填充空白單元格的值,線性值或固定值。

Kutools for Excel : 與超過300方便的Excel加載項,在60天免費試用沒有限制.

安裝後 Kutools for Excel請按照以下步驟進行:

1. 點擊 Kutools > 插入 > 填充空白單元格,看截圖:

DOC填充細胞與 -  0-5,5

2. 選擇你想填補的空白單元格的範圍,並在 填充空白單元格 對話框,檢查 固定值 來自 填充 選項,並輸入特定的值或文本 實現價值 框中,看截圖:

DOC填充細胞與 -  0-6,6

3。 然後點擊 OK or 應用。 所有的空白單元格都被填充了指定的值。 看截圖:

DOC填充細胞與 -  0-7,7  2 DOC填充細胞與 -  0-8,8

點擊下載並免費試用Kutools for Excel Now!


箭頭藍色右泡 使用Kutools for Excel填充空白單元格的值高於/低於/向右/左

如果你想填充空白單元格的值在上面或下面, Kutools for Excel填充空白單元格 也可以幫你一個忙。 請做如下:

1。 選擇要用上面的值填充空白的數據范圍,然後啟用 填充空白單元格 功能。

2。 在 填充空白單元格 對話框中選擇 基於價值 選項下 填充 部分,然後選擇要填充空白單元格的選項,可以根據需要填充它們,向上,向左和向右,請參閱截圖:

DOC填充細胞與 -  0-10,10

3。 然後點擊 Ok or 應用 按鈕,空白單元格已填充上面的數據,請參閱截圖:

DOC填充細胞與 -  0-11,11  2 DOC填充細胞與 -  0-12,12

點擊下載並免費試用Kutools for Excel Now!

有了這個 填充空白單元格 實用程序,您還可以使用線性值填充空白單元格, 點擊這裡了解更多.


箭頭藍色右泡填充值高於/線性值/具體值的空白單元格

Kutools for Excel:比200方便的Excel加載項,可以在60天免費試用。 下載並免費試用現在!


相關文章:

用線性值填充空白單元格

用上面的值填充空白單元格


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

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

Office選項卡為Office提供選項卡式界面,使您的工作更輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,Publisher,Access,Visio和Project。
  • 在同一窗口的新選項卡中打開並創建多個文檔,而不是在新窗口中。
  • 通過50%提高您的工作效率,每天為您減少數百次鼠標點擊!
官方底部
Say something here...
symbols left.
You are guest ( Sign Up? )
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.
    mutaqinabdullah1 · 7 months ago
    Artikelnya sangat bagus
  • To post as a guest, your comment is unpublished.
    Yogesh · 2 years ago
    Thanks.. Much useful..
  • To post as a guest, your comment is unpublished.
    ghazale · 2 years ago
    thanks a lot for saving the time :)
  • To post as a guest, your comment is unpublished.
    Latifur Rofik · 3 years ago
    boss, for example i make template with excel
    in the template there is no empty field,
    evidently after i download the template i found field with empty value
    nah.... my question
    how if i will insert the empty value with '0' but thats automatic from the template
    thankssss boss your answer very help me please
  • To post as a guest, your comment is unpublished.
    Morgan · 3 years ago
    How would you be able to replicate this within a file containing multiple worksheets - so that it can fill in the blanks of multiple (or all) the worksheets at the same time?
  • To post as a guest, your comment is unpublished.
    Sarband · 3 years ago
    Hi
    It's very useful.
    Thank you very much.
  • To post as a guest, your comment is unpublished.
    adib · 3 years ago
    Hi
    I have problem with inserting specific values existing in a column like (A), to blank cells of another column like (B), automatically.
    because of so many rows, the manual cell filling is very difficult. if possible, please help me. a very short example of my data as follow:
    A B
    3 --
    9 86
    9 87
    17 --
    45 --
    102
    104
    210
    --
    215
    --
  • To post as a guest, your comment is unpublished.
    son · 3 years ago
    it's very useful.
    Thank you so much!!!
  • To post as a guest, your comment is unpublished.
    nara · 3 years ago
    thank you so much. The trick is really good!!!
  • To post as a guest, your comment is unpublished.
    Debbie · 4 years ago
    Thank you very much for the tutorial on: How to fill blank cells with 0 or other specific value in Excel? Was a life-saver in this large workbook Im pressed for time with. It was easy to follow. I would note that not all Excel books have quite the same ribbon labels, ie Find & Select or Edit. I came across: [b] "Go To Special" Shortcut: ctrl + g[/b]. Someone else might find useful as well.
  • To post as a guest, your comment is unpublished.
    Shiva · 4 years ago
    These tools are very useful. I really wonder about the solutions provided by them. It will save lot of time. Thanks.
  • To post as a guest, your comment is unpublished.
    Sandeep Thopte · 4 years ago
    how to multipale rows unmerge
  • To post as a guest, your comment is unpublished.
    Per · 4 years ago
    Is there a way to stop this when reaching two blank cells in column "A"?


    Sub Test()
    'This macro will fill all "blank" cells with the text "Test"
    'When no range is selected, it starts at A1 until the last used row/column
    'When a range is selected prior, only the blank cell in the range will be used.
    On Error GoTo ErrHandler:
    Selection.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "Test"
    Exit Sub
    ErrHandler:
    MsgBox "No blank cells found", vbDefaultButton1, Error
    Resume Next
    End Sub
  • To post as a guest, your comment is unpublished.
    Kristine · 4 years ago
    So helpful! Saved me so much time! Thank you!!
  • To post as a guest, your comment is unpublished.
    omer · 4 years ago
    Thanks for the tips.
    I Also wanna thank my private genius Ohad!
  • To post as a guest, your comment is unpublished.
    Sophie · 4 years ago
    Amazing! Thank you! Saved me SO much time! Had to fill many many blanks with 0s to correctly analyse my data, life saver!
  • To post as a guest, your comment is unpublished.
    Tara · 5 years ago
    Perfect instructions, very easy to follow, saved me loads of time cleaning up several sheets. Thank you!
  • To post as a guest, your comment is unpublished.
    Anand · 5 years ago
    its very easy and productive by using Go To Special function .
    Thanks... :-)
  • To post as a guest, your comment is unpublished.
    Smithe646 · 5 years ago
    garcinia cambogia extract weight loss results She is to make pill ddcgdkcbddcdcdef
  • To post as a guest, your comment is unpublished.
    Karthik Srinivasan · 6 years ago
    Thank you for the tutorial. Really helpful.
  • To post as a guest, your comment is unpublished.
    Sherri · 6 years ago
    Thank you for this - [b]Using Go To Special function to fill blank cells with 0 or other specific value[/b] :lol:
  • To post as a guest, your comment is unpublished.
    Norfolk · 6 years ago
    For larger areas use the .specicalcells method;

    Sub FillEmptyBlankCellWithValue()
    'Fill an empty or blank cell in selection
    'with value specified in InputBox
    Dim cell As Range
    Dim InputValue As String
    On Error Resume Next
    'Prompt for value to fill empty cells in selection
    InputValue = 0 'InputBox("Enter value that will fill empty cells in selection", _
    "Fill Empty Cells")
    'Test for empty cell. If empty, fill cell with value given
    If Selection.Cells.Count = 1 Then Exit Sub
    With Selection
    .Cells.SpecialCells(xlCellTypeBlanks).Select
    With Selection
    .Cells.Value = 0
    End With
    End With
    'For Each cell In Selection
    'If IsEmpty(cell) Then
    'cell.Value = InputValue
    'End If
    'Next
    End Sub