特定の文字と数字の組み合わせを足し算したい

Excelの表の中のある1列に、
① 文字のみ（例：遅・早・浦・特・欠などの１文字）
② 数字と文字の組み合わせ（例：0.1欠・0.5欠　など小数点第1位までの数字と欠の連結のみ）
③ 数字（0.5　・１のみ）
④ 空白のセル
①~④のどれかが入っています。

この列中にある、「欠」のついたものだけを足し算したいのですが
計算式を教えてください。
文字のみの「欠」は数字の１でカウントします。
上の内容で言えば答えは0.1欠＋0.5欠＋欠＝1.6です。
そして答えには欠の文字をとって表示したいです。

質問者からの補足コメント

• 式の中の標準とは何でしょうか。
  教えていただけますか。

  No.4の回答に寄せられた補足コメントです。 補足日時：2016/06/30 13:36

• 

  enunokokoroさん
  計算式ためしてみました。
  添付の表のＡ１セルが０．１欠の時は出来たのですが、Ａ１のセルを空白にしたり
  １を入力したりすると計算式がうまくいきませんでした。
  何故だかわかりますか。

  No.3の回答に寄せられた補足コメントです。 補足日時：2016/06/30 14:19

No.8 ベストアンサー 回答者： CoalTar 回答日時： 2016/06/30 16:53

d-q-t-pさんの受け売りで

=SUMPRODUCT(0+TEXT(SUBSTITUTE(A1:A10,"欠",0),"0.0;;1;!0"))-SUM(A1:A10)

この回答へのお礼

回答ありがとうございます。
試してみたら出来ました。
出来ましたらこの式の意味を教えていただけ
ないでしょうか。

お礼日時：2016/07/01 08:52

No.9 回答者： CoalTar 回答日時： 2016/07/01 14:04

>なるほど、配列数式ってこうやって使えるのですね！

とのことなので、配列数式に関することは、排除します。
また、合計も分かると思うので排除
=(0+TEXT(SUBSTITUTE(A1,"欠",0),"0.0;;1;!0"))-SUM(A1)

数式の内側から、SUBSTITUTE関数で、"欠"を0に置き換える
(1) 文字のみ（例：遅・早・浦・特・欠などの１文字）
　パターン(1)「欠」のみ「0」の数字に置き換わる
　パターン(2)その他の文字列は、そのまま
(2) 数字と文字の組み合わせ（例：0.1欠・0.5欠　など小数点第1位までの数字と欠の連結のみ）
　パターン(3) 欠が外れて 数字(0.1,0.5 など)となる
(3) 数字（0.5　・１のみ）
　パターン(3) 数字(0.1,0.5 など)となる
(4) 空白のセル
　パターン(4) 空白の文字列 "" となる
まず、上記4パターンに分類される。

次に、TEXT関数、数字と思われるものは数値として扱われる。戻り値は数字(文字列)
"0.0;;1;!0"
正;負;0;文字列 であることから
パターン(3)において、正の場合は 、"0.0"なので小数点第一位まで返す。
　(ただし、負の場合はないと考え、定義していない。)
パターン(1) 0の場合、「1」の数字を返す
パターン(2)(4)の場合、文字列なので「0」の数字を返す。

これですべて数字を返したので、0+ つまり、0を足す計算をさせて数値化する。

ただし、
(3) 数字（0.5　・１のみ）
の場合も、合計の対象となってしまうため、あとで数値で入力してあるもののみ引く。
合計の意味もあるが、文字列は計算対象としない(エラーにしない)意味も持つ。

聞かれれば答えるつもりではいたが、文章説明は意外とめんどいなぁ(^_^;

この回答へのお礼

丁寧でわかりやすい説明をありがとうございました。
感謝いたします。

お礼日時：2016/07/01 14:21

No.7 回答者： d-q-t-p 回答日時： 2016/06/30 16:39

> でも、標準の文字がこの式には必要であることはわかりました。

必要ではないです。条件があいまいなので大事を打っただけで。
「0.0」でも多分問題ないし マイナスの値は考慮しなくてもいい
のかもしれません。

> ③を省く方法も知りたいですね。
それをどう扱いたいのかが不明なので無視しました。省きたけれ
ば SUMの結果を引くだけです。

この回答へのお礼

数式の意味はいまだわかりませんが、③を省く考え方は
わかりました。
教えてもらえば簡単ですが、自分では考えられなかったです。
ありがとうございました。

お礼日時：2016/07/01 08:50

No.6 回答者： enunokokoro 回答日時： 2016/06/30 14:55

No.2の回答者です。

補足されて気が付きました。
配列数式の一部に記載漏れがありました。
正しくは、こちら↓になります。

=SUM(IF(RIGHT(列範囲,1)="欠",IF(列範囲="欠",1,LEFT(列範囲,LEN(列範囲)-1)*1),0))

再度、数式バー上で[ Ctrl+Shift+Enter ]キーを押し配列数式にします。
LEN関数の部分で修正することを忘れていました。

ちなみに、No.4の回答にある式は③を無視することを考慮していませんね。
私も勉強になる式でしたが、③を省く方法も知りたいですね。
TEXT関数と表示形式と感嘆符
http://www.eurus.dti.ne.jp/yoneyama/Excel/kansu/ …
http://www.eurus.dti.ne.jp/yoneyama/Excel/user_s …
http://www.eurus.dti.ne.jp/yoneyama/Excel/dic/di …

この回答へのお礼

出来ました！
うれしいです！！
ありがとうございました!(^^)!

お礼日時：2016/06/30 16:14

No.5 回答者： d-q-t-p 回答日時： 2016/06/30 14:22

> 式の中の標準とは何でしょうか。

表示形式の「Ｇ/標準」を省略しただけのものです。
というかそこだけ分かってどうするのでしょう。

この回答へのお礼

ん～、表示形式の標準はなんとなく意味はわかるのですが
式の中に入ると意味がわからないです。
でも、標準の文字がこの式には必要であることはわかりました。
これを深入りしても頭こんがらがるようなのでやめときます(^_^;)
教えていただきありがとうございました。

お礼日時：2016/06/30 16:18

No.4 回答者： d-q-t-p 回答日時： 2016/06/30 12:16

#1です。

表の悪さを数式でカバーする癖を付けるとろくなことがないと思います。
作業計算を嫌がるのもあまりいい考えではないように思います。

=SUMPRODUCT(TEXT(SUBSTITUTE(A1:A10,"欠",0),"標準;-標準;!1;!0")-0)

No.3 回答者： enunokokoro 回答日時： 2016/06/30 11:44

No.2の回答者です。

配列数式を使えば、No.2での回答にある式を応用できます。
こちら↓の「列範囲」は計算したい範囲をして下さい。

=SUM(IF(RIGHT(列範囲,1)="欠",IF(列範囲="欠",1,LEFT(列範囲,LEN(A1)-1)*1),0))

数式バー上で[ Ctrl+Shift+Enter ]キーを押すと、配列数式になります。
http://excel-magic.com/post-32/
http://www.office-qa.com/Excel/ex69.htm

この回答へのお礼

なるほど、配列数式ってこうやって使えるのですね！
いまいち使い方がわからなかったのですが
勉強になりました。
ありがとうございました。

お礼日時：2016/06/30 13:48

No.2 回答者： enunokokoro 回答日時： 2016/06/29 19:13

式としてはダメな例ですが。

A列にデータがあるとして、以下のような式で作業列を用意します。
=(IF(RIGHT(A1,1)="欠",IF(A1="欠",1,LEFT(A1,LEN(A1)-1)*1),0))

欠がある場合のみ0以外の数値を返しますので、合計できると思います。

この回答へのお礼

回答ありがとうございます。
作業列をつくらないで一発計算は
難しいですか？
計算式、参考にさせていただきますね。

お礼日時：2016/06/30 10:50

No.1 回答者： d-q-t-p 回答日時： 2016/06/29 17:26

無理して１セルに複数の情報を入れるからそんなことになるのであって

最初から区分と数値を別セルに分けて入力すればいいでしょう。

=COUNTIF(区分列,"欠")+SUMIF(区分列,"欠",数値列)

必要なら後で情報をくっつければいいです。

この回答へのお礼

回答ありがとうございます。
たしかにおっしゃる通り列を増やせばいいのですが、
既に使用している表のため、変更するより何か打つ手
がないか調べたかったのです。

お礼日時：2016/06/30 10:50