excel、メニュー表から値段だけ抜き出す関数

質問

各セルに
カレー　500円
かつ丼　600円
うどん　400円（通常450円）
きつねうどん　500円
ナポリタン　600円
と入っています。ここから値段だけ抜き出したいと思っています。
データの形式は、順に文字・１スペース・金額・円は決まっていて、
円の後ろに追加で文字・数値が入る場合があります。
最初の三つだけであればmid関数・find関数でどうにかできましたが、
「きつねうどん」以降では処理ができません。
どのようは方法がありますでしょうか。
宜しくお願いします。

回答No.6

kagakusuki · ベストアンサー

回答No.4です。

>もし良ければ考え方を教えてもらえないでしょうか
>これは難しいです

　いえ、長いだけで構造自体はさほど複雑なものでは御座いません。
　まず、SUBSTITUTE関数とは、

SUBSTITUTE(元の文字列, 検索文字列, 置換文字列, [置換対象])

という形式で記述する関数で、「元の文字列」の中に含まれている「検索文字列」と同じ文字列を、「置換文字列」に置き換える関数です。
　回答No.4の関数の中には

SUBSTITUTE(A2," ","　")

という箇所が2回出て来ますが、これは「元の文字列」の所にA2が指定されていて、「検索文字列」の所に半角スペースが指定されていて、「置換文字列」の所に全角スペースが指定されていますから、、「A2セルに入力されている文字列の中に含まれている全ての半角スペースを全角スペースに置換する関数」という事になります。
　何故、半角スペースを全角スペースに置換するのかと申しますと、料理名と金額の間に挟まっているスペースの位置から金額がどこから始まっているのかを判断する際に、「料理名と金額の間に挟まっているスペース」が半角スペースなのか全角スペースなのかが不明であったため、そのままではFIND関数でスペースの位置を求める際に、半角スペースの位置を求めれば良いのか、それとも全角スペースの位置を求めれば良いのか判りませんから、どちらであったとしてもスペースの位置を求める事が出来る様にするために、半角スペースを全角スペースに置換してから全角スペースの位置を求める様にしている訳です。
　処で、

>メニューには
>コーヒー ブラック 300円
>と空白が二つあるものがありました。

という問題を解決し、金額が始まる正しい位置を求めるためには、「『スペース』+『数字以外の文字』」という組み合わせとなっている箇所のスペースは無視して、「『スペース』+『(1～9の)数字』」という組み合わせとなっている箇所のスペースの位置を求める必要があります。
　料理の価格なのですから、スペースの次に来る数字が「0」やマイナスである事はあり得ませんので、「0」や「-」に関しては処理対象とする必要が無く、

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ","　"),1,9),2,9),3,9),4,9),5,9),6,9),7,9),8,9)

という部分の中の、

SUBSTITUTE(A2," ","　")

の部分でまず半角スペースを全角スペースに置換し、

SUBSTITUTE(SUBSTITUTE(A2," ","　"),1,9)

の部分で「1」を「9」に置換し、

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ","　"),1,9),2,9)

の部分で「2」を「9」に置換し、

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ","　"),1,9),2,9),3,9)

の部分で「3」を「9」に置換し、・・・という具合に1～8の数字を順次数字の「9」に置換して行く事によって、金額の先頭に来る数字をが必ず9になる様にしている訳です。
　例えば、A2セルに入力されている文字列が「コーヒー アメリカン 305円」というものだった場合には、

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ","　"),1,9),2,9),3,9),4,9),5,9),6,9),7,9),8,9)

の部分が返す値は「コーヒー　アメリカン　909円」になる訳です。
　そこで

FIND("　9",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ","　"),1,9),2,9),3,9),4,9),5,9),6,9),7,9),8,9))

という具合にFIND関数を使用して「　9」という文字列が現れている位置を求めれば、その1文字後ろの所からが金額という事になる訳です。
　尚、後で説明する際に解り易くするために、この

FIND("　9",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ","　"),1,9),2,9),3,9),4,9),5,9),6,9),7,9),8,9))

という部分の事を、これ以降は

「『スペース』+『数字』の位置を求める関数」

と仮称して表現する事に致します。

　この様にすれば金額が始まっている位置は求める事が出来ますが、「うどん　400円（通常450円）」の様な例もある以上、「『スペース』+『数字』」より後ろの部分を全て取り出してしまったのでは金額を求めた事になりません。
　そのため、LEFT関数を使用して「円」が現れている位置の1文字前の所までだけを取り出す必要がありますが、「円」の文字が付くのは金額の後だけとは限らず、例えば「高円寺メタルめし」などの様にもしかしますと料理名の中に「円」の文字が含まれているものもあるかも知れませんので、単純に

FIND("円",A2)

などの様にして最初に現れる「円」位置を求めたのでは、金額部分よりも前の位置を求めてしまう恐れがあり、その「円」の位置よりも前の部分を取り出したのでは、金額の部分が含まれなくなってしまう恐れがあります。
　そのため、FIND関数を使う際に

FIND("円",A2,「『スペース』+『数字』の位置を求める関数)

などの様にして、

FIND(検索文字列, 対象, [開始位置])

の中の「開始位置」(文字の検索を開始し始める位置)の所に、前述の「『スペース』+『数字』の位置を求める関数」を入れ子にして使用する事で、「『スペース』+『数字』」の所よりも後の所に現れる「円」のみをFIND関数の検索対象とする事で、金額の後ろに付く「円」の文字位置を求めている訳です。
　「円」の位置は金額を表している数字よりも1つ後ろの位置にあるのですから、「円」の文字自体は必要ありませんので、

LEFT(A2,FIND("円",A2,「『スペース』+『数字』の位置を求める関数)-1)

とする事で、「円」の1つ前の所までの文字列を取り出している訳です。
　ここでもこの

LEFT(A2,FIND("円",A2,「『スペース』+『数字』の位置を求める関数)-1)

という部分の事を、これ以降は

「『円』の1つ前の所までの文字列を求める関数」

と仮称して表現する事に致します。

　さて、こうして取り出した「『円』の1つ前の所までの文字列」には、料理名やスペース等の金額ではない部分が含まれています。
　この余計な部分を除去するのに用いているのがREPLACE関数で、REPLACE関数は

REPLACE(元の文字列, 開始位置, 文字数, 置換文字列)

という形式で記述される関数で、「元の文字列」の中の「開始位置」番目の文字列から「文字数」分の長さの文字列を、「置換文字列」の所で指定した文字列に置換する関数です。
　例えば、

REPLACE("ABCDE",2,3,"FGHI")

とした場合には、「ABCDE」の中の先頭から2文字目である「B」から3文字分の長さの文字列である「BCD」を置換対象として、「FGHI」に置き換えた「AFGHIE」が返されます。
　そこで、

REPLACE(「『円』の1つ前の所までの文字列を求める関数」,1,「『スペース』+『数字』の位置を求める関数」,)

とする事で、「『円』の1つ前の所までの文字列」の1文字目から「『スペース』+『数字』の位置」と同じ長さの文字列の部分を消去(「置換文字列」の所に何も指定していないため置換後には何もなくなる)している訳です。

　只、このままでは得られる結果は、数字のみからなるデータではあっても、数字のみからなる文字列データに過ぎず、数値データとはなりませんから、上記の数式に　+0　を付け足す事によってExcelに計算処理を行わせる事で数値データに変換しています。

　後は、例えば「刺身定食　時価」などの様に金額に変換できないデータがあった場合にもエラーとならない様にするために、上記の部分をIFERROR関数で囲い、金額に変換できない場合には「(不明)」と表示させるようにした上で、元データのセルが空欄の場合にまで「(不明)」と表示されてしまう事を防ぐために、IF関数で囲って

=IF(A2="","",IFERROR関数)

としている訳です。

お礼

良く分かりました。ありがとうございます。
空白が全角と半角の問題など、
入力する段階で規格化しておくと
手間が省けるのですね！
「高円寺」に対する措置なども大変ためになりました。

回答No.7

bunjii

>いきなり実際の関数を見てひもとくのが難しいので、「考え方」を教えてもらえないでしょうか。
A列の文字列から数値に変換する数字の右端までを処理対象にします。
A1セルの場合は"カレー　500"です。
=LEFT(A1,FIND("円",A1)-1) → "カレー　500"
対象の文字列を先頭から順に1文字ずつ乗算すると文字列では#VALUE!が帰りますのでこれをIFERROR関数で0に置き換えます。
{=IFERROR(MID(LEFT(A1,FIND("円",A1)-1),ROW(A1:A7),1)*1,0)} → {0;0;0;0;5;0;0}
省略すると　↓
{=IFERROR(MID(A1,ROW(A1:A7),1)*1,0)} → {0;0;0;0;5;0;0}
{}で括られた表現は配列値を意味します。
この数式は配列演算になるのでCtrl+Shift+Enterで確定します。
ROW(A1:A7) → {1;2;3;4;5;6;7} はA1の先頭から順に1文字取り出すためのパラメータとして利用しますのでセルの値を参照していません。
IFERROR関数の中で各桁の値に10^nしないと目的の値を得られません。
従って、次のように修正します。
{=IFERROR(MID(A1,ROW(A$1:A$7),1)*10^(ROW(A7)-1)/10^(ROW(A1:A7)-1),0)}
　　　↓
{0;0;0;0;500;0;0}
この配列値をSUM関数で合計すれば目的の値になります。
但し、A列の数字の位置が不規則になっていますのでROW(A1:A7)のようなパラメータは固定できませんのでOFFSET関数に置き換えました。
OFFSET([参照],[行数],[列数],[高さ],[幅])で高さを可変にすれば対象文字列の長さに対応できます。
前回の数式を一部簡略化して下記の数式に組立れば良いでしょう。
=SUM(IFERROR(MID(A1,ROW(OFFSET($A$1,0,0,FIND("円",A1)-1,1)),1)*INDEX(10^(ROW(OFFSET($A$1,FIND("円",A1)-1,0,1,1))-1)/10^ROW(OFFSET($A$1,0,0,FIND("円",A1)-1,1)),0),0))

お礼

ありがとうございました。

回答No.5

bunjii

>うどん　400円（通常450円）
>後ろの方の値段はいりません。
>コーヒー ブラック 300円
>と空白が二つあるものがありました。
少々面倒なデータですね。
数字はすべて半角文字で入力されていて、文字列の先頭から"円"の左側に目的の数字があるとすれば、次の数式で数値化できます。
但し、Excel 2007以降のバージョンに限ります。
=SUM(IFERROR(MID(LEFT(A1,FIND("円",A1)-1),ROW(OFFSET($A$1,0,0,FIND("円",A1)-1,1)),1)*INDEX(10^(ROW(OFFSET($A$1,FIND("円",A1)-1,0,1,1))-1)/10^ROW(OFFSET($A$1,0,0,FIND("円",A1)-1,1)),0),0))
尚、この数式は計算過程で配列値を扱いますので数式の確定にはCtrl+Shift+Enterの打鍵が必要です。

お礼

ありがとうございます。

補足

ありがとうございます。
これは…難しいですね…
offset…は他人の作ったもので見たことがあるのですが、
value!になるセルが不具合があるのですが、
良く分からないので手動で修正しているんですね…苦手…
いきなり実際の関数を見てひもとくのが難しいので、
「考え方」を教えてもらえないでしょうか。
宜しくお願いします。

回答No.4

kagakusuki

>一つの式でという考えにとらわれていました。
>三つのセルを使うと簡単にできますね。

　1つのセルでも出来ますよ。

>メニューには
>コーヒー ブラック 300円
>と空白が二つあるものがありました。

という事であれば、一例としてA2セルに
コーヒー ブラック 300円
等の元の文字列が入力されていた場合、値段だけ抜き出して表示させるセルには次の様な関数を入力して下さい。

=IF(A2="","",IFERROR(REPLACE(LEFT(A2,FIND("円",A2,FIND("　9",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ","　"),1,9),2,9),3,9),4,9),5,9),6,9),7,9),8,9)))-1),1,FIND("　9",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ","　"),1,9),2,9),3,9),4,9),5,9),6,9),7,9),8,9)),)+0,"(不明)"))

※但し、上記の関数を使う事が出来るのは、ExcelのバージョンがExcel2007かそれ以降のものである場合に限ります。

お礼

ありがとうございます。

補足

ありがとうございます。
これは…難しそうですね…
ううーん、もし良ければ考え方を教えてもらえないでしょうか
これは難しいです…

回答No.3

neKo_quatre

自分が試したら、フツーに出来ましたが。

FIND関数で最初の空白を探す
FIND関数で最初の空白以降の"円"を探す
MID関数で最初の空白の次から"円"の手前までの文字を切り出す

> カレー　500円

なら、
最初の空白は4文字目
円は8文字目
抜き出すべきなのは4+1=5文字目から8-4-1=3文字

> きつねうどん　500円

なら、
最初の空白は7文字目
円は11文字目
抜き出すべきなのは7+1=8文字目から11-7-1=3文字

式一本で書こうとか横着せずに、作業列を使って途中の数字を確認しながらどこがおかしいのかチェックするのが良いです。

お礼

ありがとうございます。
大半がうまくできました。

補足

ありがとうございます。
やっとのことで一つのセルに関数二つを入れて式を作れたので、
一つの式でという考えにとらわれていました。
三つのセルを使うと簡単にできますね。
しかし、問題がありました。
メニューには
コーヒー ブラック 300円
と空白が二つあるものがありました。
少ないので手入力でも出来ますが、
これもうまく処理する方法はありますでしょうか。
どなたか宜しくお願いします。

回答No.2

k-josui

書いてある通りなら品名と値段の間にスペースがあるので「データーの区切り位置」で分割すれば簡単です。
http://enjoy.sso.biglobe.ne.jp/archives/cell_divide/

どうしても関数が使いたければ文字列の長さと、スペース位置を読み出して計算させればいいでしょう。

お礼

ありがとうございます。
できました。
元データを保持する必要がない場合に
これを使わせてもらいます！

補足

ありがとうございます。
できました。
元データを保持する必要がない場合に
これを使わせてもらいます！

回答No.1

msMike

》 円の後ろに追加で文字・数値が入る場合があります
その場合はどうしたいのですか？

お礼

ありがとうございます。

補足

後ろの方の値段はいりません。
また、
コーヒー ブラック 300円
と空白が二つあるものがありました。
少ないので手入力でも出来ますが、
これもうまく処理する方法はありますでしょうか。
どなたか宜しくお願いします。