☰

這個 SUMPRODUCT 和 MID 公式到底在做什麼？

tag-icon • tag-icon microsoft-excel worksheet-function
這個 SUMPRODUCT 和 MID 公式到底在做什麼？

在 Excel 電子表格中，我有文字條目H23、w67、P45等，我想對這些文字條目的數字部分（即 、23、67）求和45。

我有公式

=SUMPRODUCT((0&MID($H2:$S2,1+ISERROR(LEFT($H2:$S2)+0),10))+0)

它可以完成這項工作，但我不確定它在做什麼，而且我不願意在沒有完全理解它的情況下使用它。誰能解釋一下公式嗎？

答案1

從單一單元格公式開始以避免混淆。

=SUMPRODUCT((0&MID($H2,1+ISERROR(LEFT($H2)+0),10))+0)
  1. 您的範例資料均以字母開頭，但其ISERROR(LEFT($H2:$S2)+0)目的是查找數字或字母作為第一個字元並添加1如果是一封信。這向左功能預設為單一最左邊的字符，並且 TRUE 被視為1FALSE 是0。如果最左邊的字元是字母，那麼在任何數學運算中使用它都會產生錯誤（例如<字元>+ 0) 所以1將被添加到1在裡面起始編號的參數中控功能它將開始於2。如果沒有產生錯誤則0將被添加到1在裡面起始編號範圍。
  2. 這10是 MID 函數將嘗試檢索的最大字元數。可用的資源可能較少，它會檢索這些資源。
  3. 如果根本沒有數字，則求和函數嘗試將零長度字串（基本上是空白）轉換為數字時會感到窒息。為了防止這種情況，一個0是 MID 操作傳回的結果的前綴。
  4. MID 操作的結果（以及附加的0) 是一個字串，而不是數字。只要一切順利，結果應該都是數字。包含所有數字的字串可以透過乘以轉換為真數，而無需更改其數值1或添加0。例如如果你添加0到“123”你會得到一個真實的數字123。

這就是所有的內部操作。您所要做的就是將其擴展到多列並允許使用求和函數作為包裝。

=SUMPRODUCT((0&MID($H2:$S2,1+ISERROR(LEFT($H2:$S2)+0),10))+0)

將列鎖定為絕對，但行保持相對，向下填充後續行。

相關內容