☰

加入配方成分（淨值與毛值）

tag-icon • tag-icon microsoft-excel worksheet-function
加入配方成分（淨值與毛值）

我想在一定範圍內添加公式的組成部分。我們有一個部分顯示特定項目和多個特定項目的淨現金流量。

例如，=8+9-5在多個儲存格中顯示“12”。如果這些值不是在單元格級別上進行淨值化的，則可以輕鬆使用公式SUMIF僅添加正值和負值單元格，但由於它是在單元格級別上進行淨值化的，所以我正在嘗試深入研究基礎值並使用與SUMIF公式等效，但適用於所有基礎組件。

對於上面的範例，我試圖取得一個表示「+17」的範圍的總計儲存格，以及一個顯示「-5」的儲存格。

我怎樣才能做到這一點？

答案1

最重要的問題是如何從工作表存取另一個單元格的公式字串。沒有現有的工作表函數可以執行此操作。因此，您必須先執行以下兩件事之一：1）使用查找和替換來更改單元格的內容，以便它們不再保存公式而是保存字串，或者 2）使用 VBA訪問單元格的公式字串。

(1) 可以透過選擇要操作的儲存格然後按Ctrl+H開啟「取代」對話方塊來完成。搜尋=（假設您感興趣的唯一公式與您的範例類似，並且不包含後續的等號），然後不替換任何內容。這會將公式=8+9-5顯示值 12 的儲存格變更為顯示字串 的儲存格8+9-5。

(2)可以透過存取單元格屬性來完成.Formula。例如，要取得儲存格 A1 的公式，您可以使用非常簡單的 UDF，如下所示：

Public Function GetFormula(r as Range) as String
   GetFormula = r.Formula
End Function

一旦獲得這些字串，無論透過哪種方法，您都必須巧妙地使用字串操作函數，例如 、FIND和LEN，VALUE以提取所需的值。因為這樣做會浪費你我的時間，所以我不會在這裡這樣做。

答案2

將工作表新增至您的工作簿；我們稱之為「設定」。

在A1 中放置8，在A2 中放置9，在A3 中放置-5。複製與原始工作表中各列中存在的具有常數的公式一樣多的列。 （根據範圍的組織方式，您可能需要設定表格使用 A1、B1、C1 並向下複製。其想法是建立與原始表上的範圍形狀相符的範圍。）

在原始工作表上將所有公式替換=8+9-5為=Setup!A1+Setup!A2+Setup!A3。這可以透過計算公式的尋找和取代操作來完成。

現在，您可以在「設定」表上建立條件公式，而不是在原始表中建立硬編碼值。

=sumif(Setup!A1:A3,">0")

=sumif(Setup!A1:A3,"<0")

相關內容