[[20040917155349]]　『数字と文字列が混在したセルの計算』（ice）　ページの最後に飛ぶ

『数字と文字列が混在したセルの計算』（ice）

たとえば「１年２０人」「２年３５人」のようにセルに数値と文字列が入っています。これらのセルをサム関数などで人数だけ合計するにはどうすればよいでしょう。

ＯＳはＸｐ、エクセルは２００２を使っています。

 必ず年が入ってる、必ず人が入っている、人数の数値は全角ですか。
 上記3つはどうでしょう。
 （川野鮎太郎）

必ず年と人は入っていますが、数字は全角とは限らないようです。この他予期せぬ文字列たとえば「ＰＴＡ」とかの入る可能性もあります。(ice)　

 年と人の間には、数字しかないことが条件でよければ、以下でどうでしょう。
 A列とB列に入っているとして、C1セルに
 =--ASC(MID(A1,FIND("年",A1,1)+1,(LEN(A1)-FIND("年",A1,1)-1)))--ASC(MID(B1,FIND("年",B1,1)+1,(LEN(B1)-FIND("年",B1,1)-1)))

 （川野鮎太郎）

 もし以下ような配置であればB1に　=--ASC(MID(A1,FIND("年",A1,1)+1,(LEN(A1)-FIND("年",A1,1)-1)))
      A            B
 1 1年２０人	20
 2 ３年35人	35
 3 4年40人	40
 4 1年２０人	20
 5 ２年３２人	32

	        147 =SUM(B1:B5)
 （川野鮎太郎）

川野様ありがとうございます。集計は縦に行いますの下の方法で一応計算はできました。但し、ＰＴＡとかが入るとエラーになりますね。書式設定かなんかで文字列を無視させることは出来ないのでしょうか？(ice)

 B列に　=SUBSTITUTE(ASC(A1),"PTA","")　と入れて、
 C列に  =--ASC(MID(B1,FIND("年",B1,1)+1,(LEN(B1)-FIND("年",B1,1)-1)))
 で、どうでしょうか。
 （川野鮎太郎）

サブスティチュート関数というのは複数指定出来るのでしょうか？　出来れば出てきた予期せぬ文字列をブランクに置き換えてやればＯＫなんですね。ワイルドカード指定出来ると便利なのにと思いました（笑）。本日はお手数をかけました。誠にありがとうございました。また何かございましたら宜しくお願いいたします。(ice)

 ネスト（階層を増やす）してあげれば大丈夫でしょう。
 =SUBSTITUTE(SUBSTITUTE(ASC(A1),"PTA",""),"予期せぬ文字","")
 （川野鮎太郎）

 文字列の最後が必ず？？人として、
=LOOKUP(10^17,--RIGHT(SUBSTITUTE(A1,"人",""),ROW($A$1:$A$4)))
ではいかがでしょう。
(KAMIYA)

 ぉお!!(ﾟﾛﾟ屮)屮
 ま・こ・と・に・申し上げにくいのですが・・・・
 なぜにその式で右側の数値だけ取得できるのか教えていただけますか・・・(^_^A;
 （川野鮎太郎）

 このへんは常連の(LOOKUP)さんの受け売りなんですが・・・。
"1年２０人"のデータで解説。
SUBSTITUTE(A1,"人","")で"1年２０"になります。
これはすぐご理解いただけると思いますが、
　
RIGHT("1年２０",ROW($A$1:$A$4))　で次の配列が返ります。
{"０";"２０";"年２０";"１年２０"}
この時点では文字列です。定番の「--」で数値化を図ります。
　
 --RIGHT("1年２０",ROW($A$1:$A$4))　で次の配列が返ります。
{0;20;#VALUE!;#VALUE!}
数値化できないものがエラーになります。
　
 =LOOKUP(10^17,{0;20;#VALUE!;#VALUE!})
LOOKUPで「とても大きい数値（10^17）」以下の「数値」を
検索しますから、配列中の最大の「数値」、「20」が
結果として返ります。
私が理解しているのはこのような流れですが・・・・。
うまく伝わります？
(KAMIYA)

 早速の解説ありがとうございます。
 上記で大体は判りました(^_^A;
 ただ1箇所だけ、まだわからない部分があります。

RIGHT("1年２０",ROW($A$1:$A$4))　で次の配列が返ります。 {"０";"２０";"年２０";"１年２０"}　この部分です。

 なぜそのような配列になるんでしょう・・・う～ん(/-_-＼)
 ROW($A$1:$A$4)もなぜ4行なのでしょうか。
 むずかしい＿/￣|○ il||li
 (￣０￣;アッ　4行は右から4つめまでを比較で、9999人まで対応できるんですね。
 ε-(´o｀) ホッ！　納得～！　スッキリしました。
 ありがとうございました。
 （川野鮎太郎）

 ちゃんと伝わったようで安心しました。
LEFT関数、MID関数もそうですが、
(LOOKUP)さんの数式を研究しているときに、
RIGHT("1年２０",ROW($A$1:$A$4))のようにすると、
Ctrl+Shift+Enterで配列式にしなくても、ちゃんと配列を返しているのを
みつけて、「なんて便利な関数だ(゜o゜)！！！」
こういう使い方を意図して設計されている関数だとしたら、
たいしたものです。 文字数に配列が許容されるとは、Helpには
一言も載っていない･・・・。
いまさら気がつきましたが、もしかしたら、
[[20040915220033]] 『文字数が一定でない値から1文字だけ抽出』(セニョますい) 
ここでの数式に応用できたかもしれないなぁ。
(KAMIYA)

KAMIYA様
素晴らしい方法をお教えいただき、誠にありがとうございました。今回の疑問点は
すべて解決し、懸案だった集計表、成績表が滞りなく完成いたしました。
川野様にも何度もお手を煩わせ、誠にありがとうございました。今後もよろしく
ご教授下さい。（ice）

コメント返信：

[ 一覧(最新更新順) ]

[[20040917155349]] 『数字と文字列が混在したセルの計算』（ice） ページの最後に飛ぶ

[[20040917155349]]　『数字と文字列が混在したセルの計算』（ice）　ページの最後に飛ぶ