セルの中の複数の異なる文字列を以下のように処理したいのですが、Excelの標準の文字列操作の関数で試行錯誤してみたのですが、どうもうまくできませんでしたので、ご存知の方がいらっしゃいましたらご教授ください。VBAで処理しないとできないかもしれませんがよろしくお願いします。
あるリストのC列に備考欄が設けてあり、次のようなデータが入っています。
4/30 みかんを買った(強制改行して)
5/1 りんごを売った
5/2 ぶどうを食べた
5/5 すいかを買った
このセルを調べて、
(1) セル内に"みかん"と"りんご"という文字列があったら、これを取り出して、右隣のセルに表示 (結果)⇒ みかん りんご
(2) セル内に"みかん"と"りんご"という文字列があったら、これを"A","B"に置き換えて、右隣のセルに表示 (結果)⇒ A B
(3) セル内の数値データと"を食べた"、"を買った"、"を売った"を取り除いて、右隣のセルに表示 (結果) ⇒ みかん りんご ぶどう すいか
(4) セル内に"か"を含む文字列があったら個数に関係なく"A"に置換して右隣のセルに表示 (結果) ⇒ A
すべて取り出したい文字列(上の例ではみかん、りんご)や置換したい文字列(A、B)以外の文字列は全てクリアして表示しないようにします。少し複雑ですが、やり方をご存知の方がいらっしゃいましたら、お教えください。
No.2ベストアンサー
- 回答日時:
こんにちは
(1)セル内に"みかん"と"りんご"という文字列があったら、これを取り出して、右隣のセルに表示。「みかん」が複数出てくる場合、その数の分だけ「みかん」と表示。
=REPT("みかん ",(LEN(C1)-LEN(SUBSTITUTE(C1,"みかん","")))/LEN("みかん"))
&REPT("りんご ",(LEN(C1)-LEN(SUBSTITUTE(C1,"りんご","")))/LEN("りんご"))
(2)セル内に"みかん"と"りんご"という文字列があったら、これを"A","B"に置き換えて、右隣のセルに表示。「みかん」が複数出てくる場合、その数の分だけ「A」と表示。
=REPT("A ",(LEN(C1)-LEN(SUBSTITUTE(C1,"みかん","")))/LEN("みかん"))
&REPT("B ",(LEN(C1)-LEN(SUBSTITUTE(C1,"りんご","")))/LEN("りんご"))
(3)セル内の数値データと"を食べた"、"を買った"、"を売った"を取り除いて、右隣のセルに表示
=REPT("みかん ",(LEN(C1)-LEN(SUBSTITUTE(C1,"みかん","")))/LEN("みかん"))
&REPT("りんご ",(LEN(C1)-LEN(SUBSTITUTE(C1,"りんご","")))/LEN("りんご"))
&REPT("ぶどう ",(LEN(C1)-LEN(SUBSTITUTE(C1,"ぶどう","")))/LEN("ぶどう"))
&REPT("すいか ",(LEN(C1)-LEN(SUBSTITUTE(C1,"すいか","")))/LEN("すいか"))
(4)セル内に"か"を含む文字列があったら個数に関係なく"A"に置換して右隣のセルに表示
=IF(COUNTIF(C1,"*か*"),"A","")
以下のページもご参考になるのでは?
【エクセル技道場】-関数-セル内の単語数カウント
http://www2.odn.ne.jp/excel/waza/function.html#S …
参考URL:http://www2.odn.ne.jp/excel/waza/function.html#S …
janmaさん、ご回答ありがとうございます。関数だけでもこのような処理ができるとは、本当に勉強になりました。janmaさんがご教授くださった関数を解析しようと関数を調べまくりました。その間何度「なるほど~~」と発したことでしょう!!こういう関数の組合せはよく使われる手法なのでしょうか?それともjanmaさんの豊富なご経験からすっと思い描かれるものなのでしょうか?いずれにしても、いろいろ困難なことに突き当たるたびに得るものは大きいなぁと重い知らされました。これからも、1つ1つ解決して知識を身に付けていきたいと思います。本当にありがとうございました。今後もまた何かありましたら、ご教授を賜りたいと思いますので、よろしくお願いします。
No.5
- 回答日時:
>april21さんはACCESSは使っていらっしゃいますか?もし使用されているのであれば、このような処理はACCESSでもできるのでしょうか?
殆ど統合ソフトで事足りるので触った程度です。
データベースですから条件を指定して抽出してレポートにすれば良いのでは?
>データが大量なので、やはりデータベース専門のソフトで管理し、必要なデータだけ取り出して、処理する方がいいのでは?
ACCESSでデータベースを作るほどのものなのかはお仕事の内容もわかりませんし
判断できません。
中長期的にデータを集めてそれをベースに必要な情報(条件に該当する)を集める
というのがACCESSに向いてると思います。
(テーブルとか色々決めないといけないので、形をある程度決める必要がある)
そこまでしなくてもその場その場で処理できれば良いのであればexcel。
(形を特に決める必要はない)
私が思ってるだけですから間違っててもお許しを。(私は一般人(^^ゞ)
データベースの方で質問された方が良いのでは?
april21さん、ご回答ありがとうございます。おっしゃるとおり、データベースはテーブルの設計がとても重要だそうですね。将来的にデータをどのような条件で処理するのかはっきりした形が決まってからの方がよさそうですね。当面Excelでがんばってみようと思います。度々厄介なご相談をしてしまうかもしれませんが、気が向いたときなど、どうぞお力をお貸しいただければ嬉しく思います。ところで、april21さんは本当に一般人なのでしょうか???実は凄腕のプロだったりするのでは???(^_^;)今回も早々の御返事本当にありがとうございました。
No.4
- 回答日時:
(3)が答えになってなかったので追加
ARC様の関数を使わせて頂いて
E列の条件1に「*買った*」、関数をG列に(右に表示するので関数のC1の部分は
列はFで「*買った*」と同じ行に変更(E4に「*買った*」ならF4に
april21さん、いつも心行き届いたご回答ありがとうございます。これで文字列操作に関する点で、私たちの業務に必要なことはほとんど完璧に対応できます。本当に助かりました。ところで、april21さんはACCESSは使っていらっしゃいますか?もし使用されているのであれば、このような処理はACCESSでもできるのでしょうか?というのもデータが大量なので、やはりデータベース専門のソフトで管理し、必要なデータだけ取り出して、処理する方がいいのでは?という案が出ているのです。もし何かアドバイスなどありましたら、また何かの機会にでもご意見を聞かせていただければ…と思います。今回はどうもありがとうございました。
No.3
- 回答日時:
解決されたようなので
前回、書いたデータ処理のVBAを使って
(1)条件1に「*みかん*」 条件2に「*りんご*」C列だから上のセル
の両方に3を入力するデータ「みかん りんご」
(2)↑と同じで、入力するデータ「A B」
(3)は「編集」-「検索」で"を食べた"、"を買った"、"を売った"をそれぞれ
検索して置き換えの文字に何も入力しないで「置換」をクリック。
(これだと元のセルが変えられちゃうけど・・・何かの時にお役立て下さい^_^;)
(4)条件1に「*か*」C列だから上のセルに3を条件2の方は0に
入力するデータ「A」
いちおう、参加してみました。m(__)m(確かめてません)
april21さん、ご回答ありがとうございます。前回april21さんに作っていただいたプログラムのおかげで仕事が予定よりずっと早く進んでおります。あのプログラムをそれぞれ処理するリストに応じて部分的に書換えて使わせて頂いておりますが、まさかこの文字列処理にも使えるなんて!!でも確かにそうですね。ただ文字列を"取り出す”とか"置換"するという処理的な部分ばかりにしか目が向いていませんでした。早速プログラムを書換えて実行してみました。大成功でした。本当にありがとうございます。april21さんに作っていただいたVBAは、頭を柔らかくして違った角度から検証してみれば、もっといろいろな処理をするのに使用できそうですね。私の"とっておき"のVBAとしてずっと大事に(?)していきます。私の上司はすっかり気をよくしてデータ処理の仕事を私どもにばかりまわしてくるようになってしまったので、これからもExcelでの仕事はどんどん増えそうです。また困難にぶつかると思われますので、ご迷惑だとは思いますが、どうぞお力をお貸しくださいませ。今回もまたまた丁寧なご教授ありがとうございました。
No.1
- 回答日時:
(1)
=TRIM(IF(ISERROR(FIND("みかん",C1)),"","みかん ") & IF(ISERROR(FIND("りんご",C1)),"","りんご "))
※ただし、「みかん」が複数出てくる場合でも、1回しか「みかん」と表示されません。
「みかん」の数だけ「みかん」と表示したい場合は、VBA使うしかないと思います。
(2)
=TRIM(IF(ISERROR(FIND("みかん",C1)),"","A ") & IF(ISERROR(FIND("りんご",C1)),"","B "))
※(1)の変形であるため、検索対象が複数存在する場合は(1)と同じになります。
(3)
SUBSTITUTE関数が使えそうなのですが、Excelの仕様で、関数は最大7つまでしかネストできないみたいなので、0→"" , 1→"",…と置換していっても、7まで置換した時点でエラーになります。
VBAで数字を削除する関数「NumKill()」をあらかじめ作成しておけば、以下のような感じで書けると思います。
=TRIM(NumKill(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C1,"*1
"," "),"/",""),"を買った",""),"を食べた",""),"を売った","")))
※*1の位置には改行(Alt + Enter)を入れて下さい。
Public Function NumKill(SrcString As String) As String
Dim i As Long
NumKill = SrcString
For i = 0 To 9
NumKill = Replace(NumKill, CStr(i), "", , , vbTextCompare)
Next i
End Function
(4)
=IF(ISERROR(FIND("か",C1)),"","A")
ARCさん、早々のご回答ありがとうございます。以前もお力を貸していただきましたね。こちらはなかなか進歩せずで恥ずかしい限りです。本題のほうですが、このようにいくつかの関数を組み合わせれば、VBAだけに頼らずとも複雑な処理がある程度はできるのですね。大変勉強になりました。早速やってみましたが見事に要望とおりの処理が実行できました。大変助かります。さらに数字を削除するVBAまでご教授くださり恐縮です。このVBAはまた別のリストの処理に使用できると思います。実際にあるのです。商品番号に「DoCoMo1234」というようなデータがあって、この数字だけを一気に取りたいというようなことが・・・。本当にありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) EXCEL VBA 単語置き換え について質問です ブック名 ぶぶぶ シート名 ししし セル V3〜 3 2023/03/08 01:41
- Excel(エクセル) 名前と日付が一致する箇所にフラグを立てる関数が知りたいです 4 2022/08/11 02:24
- Excel(エクセル) Excelでの検索結果を含む行だけを表示させたい 5 2023/03/10 17:08
- Excel(エクセル) Excelのマクロで、特定のセルから順番に値を取得したい 5 2022/12/06 15:34
- Visual Basic(VBA) A列にある値をB列・C列にVBAで切り出し 3 2022/04/09 19:20
- Excel(エクセル) 関数EXACT(文字列,文字列)とexcelVBA 3 2022/04/14 15:07
- Visual Basic(VBA) エクセルVBAについて 2 2023/01/31 16:21
- Excel(エクセル) Excelの文字列を数字に変換する方法について 6 2023/07/31 21:18
- Excel(エクセル) 【Excel】指定した文字列に該当する行を重複しないようにリスト 3 2022/03/30 12:27
- Word(ワード) Wordの表中の文字を選択した時の白黒反転の違い 1 2023/04/25 12:13
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
教えてください!!
-
7文字以上のフルーツをできるだ...
-
スポンジケーキってどのくらい...
-
Excel MATCH関数で検索範囲内...
-
中黒「・」か読点「、」か?
-
ハーブソルトを代用できるもの...
-
職場で隣の人についてです。飲...
-
エクセルで数字を入力したら文...
-
Excelでの全通りの組み合わせ出...
-
10年前のにんにく漬け、食べて...
-
外はふわふわで(柔らかい)、...
-
果物が大嫌いな私、果物代わり...
-
ライム(果物)を漢字になおす...
-
Excelの関数 SUBSTITUTEとIF関...
-
摘み取る 摘み採る
-
エクセル 同名の場合はB列にC...
-
フランス語で「梨」を何と呼ぶ...
-
梶井基次郎の檸檬
-
歯を磨いた後にミカンを食べる...
-
デコポンの漢字よ由来
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
スポンジケーキってどのくらい...
-
7文字以上のフルーツをできるだ...
-
中黒「・」か読点「、」か?
-
Excel MATCH関数で検索範囲内...
-
ハーブソルトを代用できるもの...
-
Excelでの全通りの組み合わせ出...
-
教えてください!!
-
エクセル 同名の場合はB列にC...
-
エクセルで数字を入力したら文...
-
しらたきと吐き気
-
「であり」と「であって」の使...
-
バルサミコ酢とバルサミコソー...
-
りんごの皮農薬大丈夫?洗わず...
-
果物が大嫌いな私、果物代わり...
-
職場で隣の人についてです。飲...
-
Excelの関数 SUBSTITUTEとIF関...
-
スルメを食べた後にみかんを食...
-
「○○って」と「○○て」 書き言...
-
キノコは野菜ですか?栗は果物...
-
クーの白ぶどう
おすすめ情報