EXCELの関数や数式について

質問

お世話になります。

EXCELで以下の数式が作れなくて困ってます。

Sheet1（すでに作成されている）のA1セルの内容

　マウス:590円 キーボード:1,500円 CPU:18,000円　

マウス～CPUまでひとつのセルに入力されてます。

これを、Sheet2のセルAに”商品名”　セルBに”価格の数字”が自動的に代入されるようにしたいと思ってます。
数字の桁は最高で8桁までです。

Sheet2

　　 セルA　　　 セルB　　
1　 マウス　　　　 590　　
2　 キーボード　 1,500　　
3　 CPU　　　　 18,000　　

よろしくお願いいたします。

回答No.4

bunjii · ベストアンサー

>実は、別な表でも応用が利くため、式を作りたいという訳があります。
>マウス:590円 キーボード:1,500円 CPU:18,000円の場合に”マウス:”と”円”の間にある、数字部分だけを取り出す式を作りたいのです。
Sheet1のA1セルに"マウス:590円 キーボード:1,500円 CPU:18,000円"と入力されているものとしてSheet2のA列に商品名、B列に価格の数値のみを左から順に抽出することを検証してみました。

Sheet2のA1へ次の数式を入力します。
=MID(Sheet1!A$1,FIND("→",SUBSTITUTE("円 "&Sheet1!A$1,"円 ","→",ROW())),FIND("←",SUBSTITUTE(Sheet1!A$1,":","←",ROW()))-FIND("→",SUBSTITUTE("円 "&Sheet1!A$1,"円 ","→",ROW())))
Sheet2のB1セルには次の数式を入力します。
=VALUE(MID(Sheet1!A$1,FIND("→",SUBSTITUTE(Sheet1!A$1,":","→",ROW()))+1,FIND("←",SUBSTITUTE(Sheet1!A$1,"円","←",ROW()))-FIND("→",SUBSTITUTE(Sheet1!A$1,":","→",ROW()))-1))
Sheet2のA1とB1を下へ3行目までコピーします。
結果は添付画像のとおりです。
尚、今回の検証では4行目までコピーすると#VALUEエラーになります。
エラーを避けるにはExcel 2007以降のとき、IFERROR関数でエラーのとき空欄にすれば良いでしょう。

お礼

ありがとうございます。
mike_gさんのご回答と合わせてしばらく考えてみます。

商品文字列を任意の値に変えて、他の表にも応用できるかやってみます。
大変参考になりました。
実は、私はもう結構な年寄りで、目は見えないし、頭も働かなくなってきています。
そのせいか、物を調べることが下手になっていて、皆様のご教授は本当にありがたいです。

年に負けずに頭の訓練も兼ねて頑張ってやってみます。
老人は頭働かさないとホント、ボケていきます・・・。

Sheet作って式をコピペで試してみたところ、商品名表示の文字数が合いませんでしたが、これくらいは自分で考えて解決させなくっちゃ、です。

しばらく悩んでみますね。

回答No.7

bunjii

>Sheet作って式をコピペで試してみたところ、商品名表示の文字数が合いませんでしたが、これくらいは自分で考えて解決させなくっちゃ、です。
元データの文字で商品名と価格の区切りのコロンと価格の円と次の商品名の区切りの空白が全角文字を混在させると正しい処理ができません。
Sheet1のA1セル全体をASC関数で半角文字に変換するとカタカナの濁点と半濁点で文字数が変化するので区切り位置の誤差が生じます。
元データの前処理をするときはSUBSTITUTE関数で全角空白と全角コロンを半角に置換してください。
Sheet1!A$1
　　　↓
SUBSTITUTE(SUBSTITUTE(Sheet1!A$1,"　"," "),"：",":")

お礼

おっしゃるとおりの前処理をしたらOKになりました。

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

回答No.6

noname#204879

[No.2補足]へのコメント、
》 ”マウス:”と”円”の間にある、数字部分だけを取り出す式を作りたいのです
「その式さえ作れれば」好いと仰るなら簡単です。添付図参照
B1: =MID(A1,FIND(":",A1)+1,FIND("円",A1)-FIND(":",A1)-1)

「マウス:でもキーボードでも、文字列を代えて使えると思う」貴方のお手並みを拝見したいものです。

お礼

ありがとうございます。
bunjiiさんのご回答と合わせてしばらく考えてみます。

商品文字列を任意の値に変えて、他の表にも応用できるかやってみます。
大変参考になりました。
実は、私はもう結構な年寄りで、目は見えないし、頭も働かなくなってきています。
そのせいか、物を調べることが下手になっていて、皆様のご教授は本当にありがたいです。

年に負けずに頭の訓練も兼ねて頑張ってやってみます。
老人は頭働かさないとホント、ボケていきます・・・。

回答No.5

tom04

こんばんは！

お望みの方法ではないかもしれませんが、VBAでの一例です。
Sheet1のA列データが何行あっても対応できるようにしてみました。
コロン「:」とスーペースは半角という前提です。

Alt＋F11キー　→　メニュー　→　挿入　→　標準モジュール　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペースト　→　Excel画面に戻りマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() 'この行から
Dim i As Long, k As Long, cnt As Long
Dim str As String, wS As Worksheet, myArry
Set wS = Worksheets("Sheet1")
With Worksheets("Sheet2")
.Range("A1").CurrentRegion.ClearContents
For i = 1 To wS.Cells(Rows.Count, "A").End(xlUp).Row
If InStr(wS.Cells(i, "A"), ":") > 0 Then
myArry = Split(wS.Cells(i, "A"), " ")
cnt = (i - 1) * 2 + 1
For k = 0 To UBound(myArry)
str = myArry(k)
With .Cells(k + 1, cnt)
.Value = Left(myArry(k), InStr(myArry(k), ":") - 1)
.Offset(, 1) = Replace(Mid(myArry(k), InStr(myArry(k), ":") + 1, Len(myArry(k))), "円", "")
End With
Next k
End If
Next i
End With
End Sub 'この行まで

※　関数でないのでSheet1のデータ変更があるたびに
マクロを実行する必要があります。m(_ _)m

回答No.3

bunjii

>一行にメモ書きのように入力されているものが528行あるため、表にしてそれぞれの品名と金額をまとめたいと思っています。
1行に複数データがあるときは要素ごとに区分けしてから整理する必要があるでしょう。
品名と価格がコロン（:）で区切られ、其々が空白文字で区切られていると言う条件を利用してデータの「区切り位置」を活用すれば商品名、価格、商品名、価格、・・・・のように区分けできます。
その後にC列の商品名とD列の価格の組み合わせをA列の最後尾に移動する操作で良いと思います。
更にE列とF列の組み合わせをA列の最後尾に移動すると言う操作を繰り返せば目的に合うシートになるでしょう。
難しい数式を考えなくても短時間で完了すると思われます。

補足

ありがとうございます。

実は、別な表でも応用が利くため、式を作りたいという訳があります。

マウス:590円 キーボード:1,500円 CPU:18,000円の場合に
”マウス:”と”円”の間にある、数字部分だけを取り出す式を作りたいのです。
その式さえ作れれば、マウス:でもキーボードでも、文字列を代えて使えると思うので。

やはり、かなり複雑で難しい式になってしまうのですね・・・。

回答No.2

noname#235808

おはようございます
台風すごいですね。そちらはいかがでしょうか？

ひとつのセルに一行、全部入ってますから、自動で認識させるのは、
だいぶ複雑になります。
エクセルには、メモ帳で書いたようなテキストデータを読み取る
機能があります。

作業としては、
データを全て選択してコピー
新規のメモ帳を開いてペーストしてテキストファイルとして保存
エクセルで保存したテキストファイルを開いて編集
といった流れです

詳しくは以下のリンクで
http://span.jp/office2010_manual/excel2010/data/exchange.html

お礼

LipLapさん、ご回答ありがとうございました。

年寄りには、EXCELはややこしやです（苦笑）
でも、ボケ防止に役立ってます。

補足

ありがとうございます。

実は、別な表でも応用が利くため、式を作りたいという訳があります。

マウス:590円 キーボード:1,500円 CPU:18,000円の場合に
”マウス:”と”円”の間にある、数字部分だけを取り出す式を作りたいのです。
その式さえ作れれば、マウス:でもキーボードでも、文字列を代えて使えると思うので。

やはり、かなり複雑で難しい式になってしまうのですね・・・。

回答No.1

noname#204879

Ａ列に入力されている商品名は、常に３個ですか？
そうでなければ最小個数と最大個数は？
「商品名:価格」間のスペースは必ず半角スペースがあって、その個数は１個ですか？それとも全半角混在で個数もバラバラ？

補足

ご回答ありがとうございます。

一行にメモ書きのように入力されているものが528行あるため、表にしてそれぞれの品名と金額をまとめたいと思っています。

・A列の商品名　→　13品目
・”マウス:590円_キーボード:1,500円_CPU:18,000円”　のスペース
　→スペース（_部）は、半角スペース１か全角スペース１のどちらかで区切られていそうですが、半角２とかの混在はあるかもしれません。
　個数は行によりバラバラで最大７個です。（最小１・最大7）

よろしくお願いいたします。