我正在嘗試在 Excel 中創建一個休假跟蹤器。我想計算大寫字符 V 的頻率（一整天的假期，1 天）和小寫字符 v 的頻率（半天的假期，0.5 天）。
因此，對于范圍 c7:ag7，將下 v 的總和添加為 0.5，將上 v 的總和添加為 1
VV vvv（2 個上層和 3 個下層）相當于 3.5 天的假期。
有人可以推薦一個“最佳”的 Excel 公式或函式嗎？
uj5u.com熱心網友回復：
假設每個單元格不包含 V 和 v 的多個實體，
=SUM(IFS(EXACT($C$7:$AG$7,"V"),1,EXACT($C$7:$AG$7,"v"),1/2,TRUE,0))
會做的。（在 Excel 365 之前作為陣列公式輸入；即 Ctrl Shift Return。）
uj5u.com熱心網友回復：
我制定了這個公式，它滿足我的需求
= SUM(
0.5*(LEN(January[@[1]:[31]])-LEN(SUBSTITUTE(January[@[1]:[31]],"v","")))
0.5*(LEN(January[@[1]:[31]])-LEN(SUBSTITUTE(January[@[1]:[31]],"p","")))
0.5*(LEN(January[@[1]:[31]])-LEN(SUBSTITUTE(January[@[1]:[31]],"s","")))
0.5*(LEN(January[@[1]:[31]])-LEN(SUBSTITUTE(January[@[1]:[31]],"j","")))
0.5*(LEN(January[@[1]:[31]])-LEN(SUBSTITUTE(January[@[1]:[31]],"m","")))
(LEN(January[@[1]:[31]])-LEN(SUBSTITUTE(January[@[1]:[31]],"V","")))
(LEN(January[@[1]:[31]])-LEN(SUBSTITUTE(January[@[1]:[31]],"P","")))
(LEN(January[@[1]:[31]])-LEN(SUBSTITUTE(January[@[1]:[31]],"S","")))
(LEN(January[@[1]:[31]])-LEN(SUBSTITUTE(January[@[1]:[31]],"J","")))
(LEN(January[@[1]:[31]])-LEN(SUBSTITUTE(January[@[1]:[31]],"M","")))
)
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/417864.html
標籤：