セル内の文字列操作について

質問

Excelでセル内の文字列を操作したいのですが、Excelの標準の関数では出来そうにないので、どなたかお力をお貸しください。

1つのセル内に、文字列や数値が複数入っています。

（例）
Excel 12 りんご Word 11

このセルを調べて、数値のデータが複数入っている場合、最大値のみを表示させ、残りの数値データは削除したいのです。

（結果）
Excel 12 りんご Word

この処理の対象はセル内の数値データであって文字列には作用させず、結果的には文字列はそのまま表示させたいのです。

Excelの文字列に関する関数を調べてみたのですが、該当するものがなく、ＶＢＡを使用しなくてはならないのかもしれません。処理するデータが大量なため、ＶＢＡマクロなどで処理できれば大変ありがたいのですが、どなたかご存知の方がいらっしゃいましたら、ご教授くださいませ。どうぞよろしくお願いいたします。

回答No.3

nishi6 · ベストアンサー

今は決算が終わり息抜き状態です。そろそろエンジンをかけ始める時期です。

後半部分を少し手を加えています。
最初に現れた最大値を残し、２つ目以降の最大値は消しています。

With Application
myStr = .Substitute(myStr, maxNum, "@") '最大値を置き換える
For pot = 1 To Len(myStr)
If InStr("0123456789", Mid(myStr, pot, 1)) Then
Mid(myStr, pot, 1) = " "
End If
Next
'***追加***
If maxCot > 1 Then
pot = InStr(myStr, "@")
myStr = Left(myStr, pot) & .Substitute(Right(myStr, Len(myStr) - pot), "@", "")
End If
'********************
myStr = .Substitute(myStr, "@", maxNum) '最大値を元に戻す
myStr = .Trim(myStr) '余分な空白を除去(ワークシート関数)
End With
.Offset(rw, 2) = myStr
'*** 修正 *** コメント行にする
'If maxCot > 1 Then .Offset(rw, 3) = "最大値：" & maxCot & "個"
'
rw = rw + 1 '次の行へ

お礼

nishi6さん、早々のご回答本当にありがとうございます。私のわがままなお願いを聞いてくださって恐縮です。早速会社で実行してみたのですが、希望とおりの処理が出来ました。これで業務がとても楽になります。本当にありがとうございました。nishi6さんがこれからお仕事が忙しくなられてしまうということで、あまりご相談に乗っていただけることは少なくなってしまいそうですね。今回の質問で、april21さんにもご回答頂いているのですが、私にとってはお二人とも先生のような親近感を抱いております。（こんなことはお二人にとってはご迷惑だとは思うのですが…）別件なのですが、前回nishi6さんに考えていただいたVBA（２つの表を統合するマクロ：TougouList)なんですが、sheet1,sheet2それぞれの表のA列はソートしておく必要がありますでしょうか?会社の同僚がA列がソートされていない表に対してTougouListマクロを実行した際、うまくいかなかったといっておりましたので、もしお分かりでしたら教えていただけますでしょうか?それそれのA列は「Excel_001」というようなデータが入っております。表によってはその部分がまったくソートされていないものがあります。でもそれはわざとソートしていないのです。業務上その順番がとても重要なのですが、そういうタイプの表に対してTougouListを実行してもうまく処理できますでしょうか?もしかしたら、同僚の操作が悪かったのかもしれません。ただ同僚はマクロを実行する前にそれぞれの表のA列をソートしてから実行したのですが、（その部分はうまく処理できるのですが、2つの表が統合された後にまたもとの順番に戻すことが出来ないといっておりました。）TougouListマクロは本当に私達にはなくてはならないものになっておりますので、さまざまなタイプの表に対応させることが出来ればと思いご質問させていただきました。この質問の場でお伺いするのは気がひけるのですが、折がありましたらお答え頂ければと思います。またまた厄介なことを申しまして申し訳ありませんが、どうぞよろしくお願いいたします。

回答No.9

april21

>april21さんがおっしゃられていた処理の手順と言うのはアルゴリズムということでしょうか？
そうですね。
No.73913の処理や今回の処理をフローチャートにすれば分かりやすいのでは？

(^^)/~~~

回答No.8

april21

既にお試しならお分かりだと思うのですが最大値が複数の場合セル内の文字列の左側の数値を表示
するという条件が満たされてません。
MATCH関数でどの列のセルが最大値と同じ数値であるかを調べる。
COLUMN関数で調べてるセルがどの列かを調べる。
Ａ列とＤ列が最大値だとし
A列を調べた時はMATCH関数では1となり、COLUMN関数でも1
D列を調べた時はMATCH関数では1となり、COLUMN関数でも4

前回の条件式にMATCH(最大値を求めたセル,A1:G11,0)-COLUMN(列を調べたいセル)を付け足す。
（1-1=0　1-4=-3　だから最大値にプラスすると最大値に同じになるのはA列だけ。）

=IF(OR(TYPE(A1)=2,A1=LARGE(A1:E1,1)+MATCH(H1,A1:G1,0)-COLUMN(A1))=TRUE,A1,"")&IF(OR(TYPE(B1)=2,B1=LARGE(A1:E1,1)+MATCH(H1,A1:G1,0)-COLUMN(B1))=TRUE,B1,"")&・・・・こんな感じでA～Eまで

もっと良い方法があると思いますがこんな感じでも結果は得られるという事で・・。

では、頑張ってください。(^^)/~~~

回答No.7

april21

>Excelのセルに値を入れるということは内部では変数に値を入れているということになるのでしょうか？
データを入れておく箱のような物が変数ですからセルもそうでしょうね。

ＶＢＡがまったくわからないのであれば標準の関数を使って作ってみたほうが良いと
思いますよ。
そうするとどんな関数があるとかがわかってきますし処理させる手順がはっきりしてきますから。
手順がわかればＶＢＡで記述する事も出来るはずです。

今回の処理を例にすると
文字データと数値データが混在するから処理できないわけですよね？

だから、どうしたら文字データと数値データを分ける事が出来るかを考える。
■SEARCH関数で空白が入ってる位置を調べてMID関数で抜き出して5つのセルに分ける。（または「データ」-「区切り位置」で分ける）

分けた文字列を1つのセルにする
■セルは＆で繋がる。（=A1＆B1＆C1＆D1&E1）
でも、このまま繋いでしまったら元のまま。
文字データと最大値のみの表示にするという条件が付いてますから
文字データを調べるにはTYPE関数、最大値はLARGE関数
IF(OR(TYPE(A1)=2,A1=LARGE(A1:E1,1))=TRUE,A1,"")＆IF(OR(TYPE(B1)=2,B1=LARGE(A1:E1,1))=TRUE,B1,"")・・・こんな感じでA1～E1まで。

元データがいくつもある
■数式のセルをクリックして右下が+に変わったらドラッグ。

ものすごぉ～くセルが長くなってしまいますが結果は得られますね？
セル内に文字が増えてもEまでだったのをＦＧＨＩと増やしていけば良いだけですし
数式が長くて書けないのであればOR(TYPE(A1)=2,A1=LARGE(A1:E1,1)の値を別の
セルにいれれば短くなるし・・・。

セルは沢山必要だし同じ事をいっぱい入力しないといけないけど・・出来ますよね。

ここまで出来ればどんな関数を使えば良いかもどんな処理をすれば良いかもわかるのでＶＢＡの記述方法を少し覚えれば簡単に出来ると思います。
ヘルプ、本を読んだりしてる時に「あ、この部分はＶＢＡに替えられる」という
のがわかってくるはずです。

＞よく何かひとつの言語をマスターされた方は他の言語も比較的早くマスターすることが出来ると聞いております。
言語がわかるとかわからないとかでなくどういう処理手順をさせたいかって事がわかってれば良いのだと思います。
誰かにお願いしても手順を説明しなくては思ったような結果は得られません。

お礼

april21さん、私の些細なたわごとにご丁寧にお答え下さり、本当にありがとうございます。実は私は大変プログラミングに興味を持つようになりました。(april21さんやnishi6さんのようにパソコンに自在に処理させてみたいなぁとゆめをみています。）少しずつ時間を見つけてはプログラミングの本を読んだりしています。やはりいきなりC言語やJavaは難しいと思いますので、自分が普段使用しているソフトの中で少しでもプログラミングに触れることができるVBAから挑戦してみようかな、と言う気持ちでいるのす。april21さんがおっしゃられていた処理の手順と言うのはアルゴリズムということでしょうか？確かにコンピュータにどういう処理をさせたいのかその手順を記述することがプログラミングですよね。その手順をどういう風に書くのか分かれば、言語は何だっていいのですよね。同じ結果は得られるのだと思います。今回のapril21さんのご回答で、私はまた目覚めてしまいました。本当にVBAを頑張って修得したいと思います。やってみないと分かりませんが、こういうことを考えることは結構性に合っているかな？と思っていたりもしています。これからもExcelのことではたくさんご相談してしまうと思いますが、どうぞ嫌気を指さずにお力をお貸しくださいませ。今回は貴重なご指標をありがとうございました。

回答No.6

nishi6

＞A列がソートされていない表に対してTougouListマクロを実行した際、うまくいかなかったといっておりましたので・・・

質問の意味から考えて、ソートされているものとして考えていました。基本的にはマージ処理の変形と考えられますのでソートは必須でしょう。ソートされていないデータなら一旦ソートしてから次の処理を行うと思います。
それはさておき、現実的な対応方法としては、
１．ソートしていない状態でSheet1のＥ列に連番を振っておきます。(Sheet1を元に戻すため)
２．同様にソートしていない状態でSheet2のＦ列に連番を振っておきます。(Sheet2を元に戻すため)
３．この状態で２シートともＡ列をキーにして昇順にソートします。
　　ソート範囲はＡ～Ｅ列(Sheet1)またはＡ～Ｆ列(Sheet2)です。
４．TougouListの対象列を２つ増やして実行します。(マクロを修正します)
５．ここで分からないのが「またもとの順番に戻す」という意味です。２シートを統合しているわけですし、どちらを基準に元に戻すかが問題になりそうです。統合リストを結果としてとらえ、Sheet1、Sheet2それぞれ毎に元に戻すのなら統合リストのＥ、Ｆ列でソートすれば答えは得られると思います。

お礼

nishi6さん、早速のご回答本当にありがとうございます。すぐにお答えできずに申し訳ありませんでした。実は会社の同僚と一緒にその表の処理をいっしょにやっておりまして、帰りがAM1：00になってしまいました。実はこれは同僚が2つの同種類の表をたてに合体させて、一気にTougouListマクロを実行しようとしたためにうまく行かなかったのでした。大変申し訳ありません。同僚が楽をしようと比較的データの少ない表を縦につなげて(同種類の表のためフィールド名などが同じ）、処理しようとしたため、A列をソートすると上の表と下の表は当然混ざってしまいますよね。それをマクロ処理した後にもとに戻そうと思ったらしいのです。いくらnishi6さんが完璧なプログラムを作ってくださっても、使う私たちが勝手に仕様を変えてはうまく行かなくて当然ですね。失礼を申し上げて本当に申し訳ありませんでした。同僚にはちゃんと1つずつマクロ処理するようにと言っておきましたのでどうかお許し下さいませ。これを読んでいる皆様、nishi6さんは本当にすばらしいプログラムを作ってくださったのですよ。うまく行かなかったのは私たちのせい、つまりoperationが悪かったのです。混乱するようなことを言って本当に申し訳ありませんでした。これからもよろしくお願い致します。

回答No.5

april21

ちょっと補足
↓で
文字間に必ず空白（特定の物）が入るのであれば標準の関数だけでも出来ます。
って書いたのですが入ってなくてても出来ます（入ってればわりと簡単と言う意味です）

☆nishi6さん
さすが、すばやいご回答！
お忙しくなるのですね。お身体お大事にm(__)m

お礼

april21さん、補足いただきまして、ありがとうございます。ここで少し的外れかもしれませんが、お伺いしたいのですが、よくプログラミングの本の中で「変数」という言葉を聞きますが、Excelのセルに値を入れるということは内部では変数に値を入れているということになるのでしょうか？Excelはアプリケーションなのでまったく違ったことをいっているかもしれませんね。（おかしなことを伺って申し訳ありません。）
　april21さんもくれぐれもお体には気をつけてくださいね。nishi6さんもどうかお仕事頑張ってください。まずはお礼にて…。

回答No.4

april21

＞今回の処理はExcelではむりなのでは？
文字間に必ず空白（特定の物）が入るのであれば標準の関数だけでも出来ます。
今回の処理以外にもご自分で応用して使いたいのであればその方が・・・。
＞april21さんの技術に対してもただただ「すごいなぁ」と驚嘆させられるばかりです。
そんな風に言われると・・・何て言ったらいいか・・・実はExcelを使い始めたの
4月からなんです。
だから、私をあまり買い被らないように^_^;
慣れた言語で考えてＶＢＡの記述は(?_?)って感じです。

nish6さんに勉強になるなんて書かれてたので恐縮してますm(__)m

お礼

april21さん、ご回答ありがとうございます。さきのnishi6さんのところでも書いたのですが、お二人はわたしにとっては本当に先生という存在になっております。（馴れ馴れしいことを申し上げて恐縮なのですが…）そしてapril21さんがExcelをはじめてまだほんの1～2ヶ月だなんて本当に驚きです。何か他の言語を専門になさっているのですか?よく何かひとつの言語をマスターされた方は他の言語も比較的早くマスターすることが出来ると聞いております。だとしたら、本当にすごいですね。でも本当にお二人にはたくさんのことを教えていただき、そのご好意にお答えするためにも私ももっともっと勉強していきたいと思っておりますので、これからもどうぞよろしくお願いいたします。

回答No.2

nishi6

少し長くなってしまいました。Ａ列にデータがあり、Ｃ列に結果を出力します。短くするため、データ中には「@」が無いものとして書いています。不都合があれば対応できますが・・・。
セル内に、文字と数値が何個あっても機能するはずです。
最大値が複数ある場合は置換えしません。隣のセルに最大値の個数を表示します。

Sub maxVALstr()
Dim myStr As String 'セルの入力値
Dim pot As Integer '文字カウンタ
Dim elm As String '数値と認識した文字ブロック(ワーク)
Dim maxNum As Long '数値と認識した文字の最大値
Dim maxCot As Integer '最大値の個数
Dim rw As Long '行カウンタ

With Range("A1") 'A列を調べる
While .Offset(rw, 0) <> ""
myStr = .Offset(rw, 0) & " " 'スペースを1つ加えておく
maxCot = 0: elm = "": maxNum = 0
For pot = 1 To Len(myStr) '*** 最大数値を取り出す ***
If InStr("0123456789", Mid(myStr, pot, 1)) Then
elm = elm & Mid(myStr, pot, 1) '数値のみ取り出し
Else
If elm <> "" Then '数値を取り出す
Select Case True
Case maxNum < elm
maxCot = 1: maxNum = elm
Case maxNum = elm
maxCot = maxCot + 1
End Select
elm = ""
End If
End If
Next
'
With Application
myStr = .Substitute(myStr, maxNum, "@") '最大値を置き換える
For pot = 1 To Len(myStr)
If InStr("0123456789", Mid(myStr, pot, 1)) Then
Mid(myStr, pot, 1) = " "
End If
Next
myStr = .Substitute(myStr, "@", maxNum) '最大値を元に戻す
myStr = .Trim(myStr) '余分な空白を除去(ワークシート関数)
End With
.Offset(rw, 2) = myStr
If maxCot > 1 Then .Offset(rw, 3) = "最大値：" & maxCot & "個"
'
rw = rw + 1 '次の行へ
Wend
End With
End Sub

お礼

nishi6さん、早速のご回答ありがとうございます。いつもいつも本当にありがとうございます。毎回お世話をおかけして恐縮です。本当にお見事です。まさに私どもの希望通りの処理が出来ました。しかも最大値が複数あるときの個数まで隣の列に出力して下さるなんで感激でございます。本当に大助かりです。そして、せっかくこんな素晴らしいＶＢＡを頂きましたので、このマクロを最大限に業務に使用したと思いますので、図々しいお願いではございますが最大値が複数あるときは、その表示を1つだけにすることは可能でしょうか？
（例）
A1に
　Excel　12　330　りんご　105　東京　330　
　というデータがある場合、

（結果）
　Excel　330　りんご　　東京　
　という風に表示させることが出来ましたら、とてもありがたいのですが…。

nishi6さんにはいつもお世話になっている上こちらから要望を申し上げるのは大変恐縮ではございますが、せっかくnishi6さんが考えてくださったＶＢＡを一番ベストな状態で大いに使用して、ご好意を最大限生かしたいと思いますので、もし、お時間がありましたら、手を加えていただければとお願い申し上げます。
本当にお時間があるときでかまいませんので、どうぞ負担に感じないでくださいませ。本当にいつもいつもありがとうございます。

回答No.1

april21

Ａ列使用

Sub smalldel()

Dim Words, MyString, suu(1), small, i As Integer, c As Integer
For c = 1 To Range("A1").CurrentRegion.Rows.Count
MyString = Cells(c, 1)
N = 0
small = ""
suu(0) = ""
suu(1) = ""
For i = 1 To Len(MyString)
word = Mid(MyString, i, 1)
If Asc(word) > 47 And Asc(word) < 58 Then suu(N) = suu(N) & word Else If suu(0) <> "" Then N = 1
Next i
If Val(suu(0)) > Val(suu(1)) Then small = suu(1) Else small = suu(0)
If Val(suu(0)) <> Val(suu(1)) Then
Cells(c, 1).Select
Selection.Replace small, ""
End If
Next c
End Sub

お礼

april21さん、早速のご回答ありがとうございます。いつもお世話ばかりおかけして、恐縮です。今回の処理はExcelではむりなのでは？とあきらめていました。
私はExcelでは1つのセルの中のデータは、見た目では文字列や数値など複数のデータが入っているように見えても、内部では全て数値化されて1つのデータに変換されてしまうのではと思っておりました。そして数値の比較や判断は他のセルとの参照が基本なのだと思っておりましたので同一セル内で数値を比較したり判断することは不可能だろうと思い、今回ばかりは手動で処理しなくてはならないかなぁ…ととても重い気分でいましたので本当に感激しております。またこのようなことがExcelで出来る！！という新たな発見を嬉しく思います。本当にやってやれないことはないのでは？と思えるほどよく出来たツールですね。またいつもながらこういうことを可能にしてしまうapril21さんの技術に対してもただただ「すごいなぁ」と驚嘆させられるばかりです。本当に毎回素晴らしいご回答を下さりありがとうございます。もう早速実行してみましたが、万事OKでございました。いつもながら、お見事ですね。また、ご相談することがあると思いますが、どうぞ、お力をお貸しくださいませ。今回はありがとうございました。