回覆列表
-
1 # Excel小王子
-
2 # Excel到PowerBI
要用Excel函式或公式求某列複雜算式*前數值的和難度很大,對於大部分Excel普通使用者來說要掌握非常難,如果需要實現自動計算或一鍵重新整理,一是可以用VBA寫自定義函式,二是可以使用Excel2016的新功能Power Query(Excel2010或Excel2013可到微軟官方下載相應的外掛)來完成。
其中寫VBA需要寫程式碼,對很多Excel新手來說也會很困難,因此,個人推薦使用第二種方法——新功能Power Query,只要滑鼠點點點的簡單操作即可,並且可以在資料來源更新的情況下一鍵重新整理,實現步驟如下:
一、資料載入到Power Query二、複製一列用於拆分三、按分隔符(+)拆分列四、提取分隔符(*)前的文字五、更改提取出來的文字為整數六、分組合並計算七、將結果返回Excel,可以一鍵重新整理以上透過Excel的新功能Power Query簡單的滑鼠操作即完成了如題所需的星號前資料求和問題,而且關鍵的是這個結果是可以隨著源資料的更新而一鍵重新整理的。
-
3 # 老四和你學E表
SUM((LEN((SUBSTITUTE("+"&A1,"+"&ROW($1:100)&"*",,COLUMN(A:Z))))<len(A1))*row($1:100))
這個短不??哈哈,這題這麼多大神,向880和泥瓦匠致敬
這在Excel應用技術中是一個相對比較複雜的問題了,多數人可能束手無策。但也不非無解,一般高手可能直接會用VBA利用正則表示式來處理這類問題。不過Excel函式精妙無比,處理這類問題還是能搞定的。下面請看演示:
如題需要求下圖中表達式裡星號前面的數字和。
正確結果如下圖所示。
那麼應該如何寫這個函式公式呢?下面就是見證奇蹟的時刻!
利用這個公式=SUM(IFERROR(--LEFT(SUBSTITUTE(TRIM(MID(SUBSTITUTE(A2,"+",REPT(" ",99)),COLUMN(A:E)*99-98,99)),"*",REPT(" ",99)),9),)),即可完成上述問題的數字求和。
這是一個數組公式,需要同時按CTRL+ SHIFT+ENTER鍵錄入公式。思路是替換字元並擷取適當字元長度,取得星號前的數字,然後用--將文字字元轉化為數值,用IFERROR遮蔽掉空文字錯誤值,最後用SUM求得和值。