追蹤
Excel Trump 知識+ 知識名人
關於部落格
分享 Excel 功能、公式、函數、密技、線上教學...
  • 1374658

    累積人氣

  • 504

    今日人氣

    3

    追蹤人氣

Excel 的函數能否計算用符號(燈號)表示的數值總和?

q

狀況描述:話說我自己用Excel做了一份日本動畫評分表
設計了許多評分項目,湊合成滿分100分的綜合評比
每個項目的欄位不是用數字,而是用全形的圓圈符號來表示分數
●代表1分,○代表0.5分,故3.5分會表示為「●●●○」

因為不是用數字,所以合計分數時我都是慢慢用心算加
(如果改成數字自然可以用SUM,但重點就是我希望用符號)
請問如果維持這種符號表示法,能不能用函數自動合計分數呢?

例如:A1~E1 的5個欄位內容分別是:
●●●○ | ●●● | ●●●● | ●●○ | ●●●○
要在 F1 的欄位計算總和 = 3.5 + 3.0 + 4.0 + 2.5 + 3.5 = 16.5
也就是程式會將●算成數值 1.0,將○算成數值 0.5,全部加起來
Excel的函數能否做到這樣的功能?怎麼做呢?
(我就只有使用 ● 和 ○ 兩種符號,沒有其他的)

另外,當我算出一部動畫的總得分後(0~100之間)
最後有一個欄位,會根據得分而給予不同的等級表示
例如:90分以上叫"最高級"、80~89分叫"優良級"、以此類推…
需要一個像C語言的 Switch... Case... 的判別式
而且每個 Case (等級) 的值不是 A B C,而是像上面的自訂文字
這樣的功能可能靠函數做到嗎?

上面提到的2件工作,我到目前為止都是手動處理的
然而隨著資料量 (tuple) 增加,想更新表格內容是越來越困難
希望熟悉函數的朋友幫幫忙,看是否能讓程式自動進行處理。
提供20分贈點,衷心期盼能有 Excel 高手賜教,謝謝!


 

a 



■ 取代多餘空白字元:
在輸入資料時,難免有時會不小心多按了幾個空白鍵。但是,筆者以下所使用的公式,多了空白字元,將會計算不正確,因此需使用「取代」功能,移除多餘空白字元。方法:
1.選取有●○(以下稱為燈號)之所有儲存格範圍
2.編輯\取代
3.「尋找目標」輸入一個空白字元
4.「取代成」完全不輸入
5.按下「全部取代」

■ 建立等級對照表:
建立黃色區域之等級對照表,這些資料都是直接輸入,沒有任何公式。

■ 建立公式:
G2 公式 =SUMPRODUCT(LEN(SUBSTITUTE(B2:F2,"●",""))*0.5+LEN(SUBSTITUTE(B2:F2,"○",""))*1)
H2 公式 =VLOOKUP(G2,$J$4:$K$13,2)

上述兩個公式,都可以直接往下複製,完成。


補充:
■ 以下為避免空白字元影響公式運算結果的原始公式:
=SUMPRODUCT(LEN(SUBSTITUTE(SUBSTITUTE(Sheet1!B2:F2," ",""),"●",""))*0.5+LEN(SUBSTITUTE(SUBSTITUTE(Sheet1!B2:F2," ",""),"○",""))*1)

■ 若定義一個名稱 :
y =SUBSTITUTE(Sheet1!B2:F2," ","")

則公式可簡化為:
=SUMPRODUCT(LEN(SUBSTITUTE(y,"●",""))*0.5+LEN(SUBSTITUTE(y,"○",""))*1)
相簿設定
標籤設定
相簿狀態