Excel 對黃色單元格求和?

01-29

目的：每付一筆款項就會標黃，進度條自動更新支付比例。
方法：試過Alt+F11添加模塊代碼的方法和條件格式相結合，並不理想
問題：是否有更簡單的方法,比如sumif=（J2:O2, ??條件為有背景的單元格, J2:O2）對求黃色單元格求和？
補充1：有其他實現此目的的手段亦可。
謝謝

按你說的要求，用公式函數難以完成。

換一個思路吧：插入一個輔助列「已付款次數」，每次付款後，更新已付款次數，用條件格式標註已付款的。已付款對應單元格會根據C列的次數添加單元格底色。效果如下圖

製作方法：

1、選定D2：L2單元格區域，點擊「開始－條件格式」

2、按下圖進行設置條件公式和格式，設置完後點擊確定退出。

條件公式為:

=COLUMN(D2)&<=$C2+3

公式解釋：

上面公式的用於判斷「當前單元格的列號是否小於等於（C2+3）」，如果是，計算結果為true，則應於指定的格式。

column()函數為計算列號。column(D2)為計算D2單元格的列號，結果為2；column()為計算當前單元格的列號，因而此公式也可直接寫為

=COLUMN()&<=$C2+3

注意：使用條件格式時一定要注意單元格引用的類型。

3、將D2：L2單元格區域下拉填充至D3：L10

4、B2的公式為：

=SUM(OFFSET(D2,0,0,1,C2))/A2

按下圖設置B列的條件格式為「數據條」

比較簡單的實現方法是，增加一個輔助列，標記狀態，對已付狀態的行SUMIF就可以了

網上搜kpmg excel tools下載並裝上，有專門的顏色單元格求和的插件

標黃後求和的需求，標準Excel公式無法做到，除非用VBA的自定義函數。

Input一個Range，取Cells.Interior.ColorIndex判斷是否要SUM的對象，用VBA實現並不難。

但是通用性很差，外部數據稍微改一下就函數返回結果就不正常了。

所以我不建議用VBA來實現。

這個問題你逆向思維一下。

為什麼不設計成，標識輔助單元格之後求和並自動標黃呢？

用了輔助行之後，使用簡單的Sumproduct就解決問題了。

而且因為輔助行的數值正好也是布爾值，所以條件格式連if都不用寫，直接用offset函數就行了。

至於應用範圍就是求和行的目標單元格了。

至於輔助行，如果覺得有礙觀瞻的話看，可以把他們隱藏掉。

另外輔助行和求和行沒有必要做掉一行夾一行的，可以做成塊狀的，條件格式和Sumproduct的行偏移量放大一點就行了，這樣擴張性會比較好。

至於VBA的實現方法，我簡單做了個自定義函數，代碼如下

具體使用方法和SUM函數類似。

按照你的業務邏輯，如果第一個單元格沒有顏色的話，無條件返回0，

如果第一個單元格有顏色，後面的單元格的顏色和第一個顏色一致，返回這些單元格的合計。

但是，因為是自定義函數，即使加了Application.Volatile，在單元格該顏色的情況下不能自動刷新，需要手動enter一下。

Public Function SUM_BY_COLOR(input1 As Range)


Application.Volatile
mySUM = 0

If input1.Cells(1, 1).Interior.ColorIndex = -4142 Then mySUM = 0 Else For i = 1 To input1.Rows.Count For j = 1 To input1.Columns.Count If input1.Cells(i, j).Interior.Color = input1.Cells(1, 1).Interior.Color Then mySUM = mySUM + input1.Cells(i, j) End If Next Next End If

希望能幫到你。

輔助列可以很好解決問題，我換個思路。

利用SUM函數不計算文本格式數字實現加總、單元格標黃。

1、條件格式：付款金額區域單元格J2公式=SUM（J2）=0，若為真，填充黃色。格式刷全部付款區域。 "-------文本格式單元格求和為0

2、進度條公式=(H2-sum(J2：Z2))/H2 』------總額-未付金額求已付金額

3、每次付款，將對應單元格數值前加『號，變為文本格式。條件格式自動將單元格標黃。

缺點是空白單元格也被標黃，可以用其他條件格式取消。也可以調換為未付款單元格加』號

條件格式是無法與格式相提並論的，在vb里很難操作。