エクセルのデータ処理

質問

A列に英文データがあります。前から数えて指定の語（１語または２語）だけを（　　　　）に置き換えたいのです。
A列
English and French are spoken in Canada.

B列 4
C列
English and French ( 　　　 ) spoken in Canada.

D列　　are

E列 4,5
F列
English and French ( 　　　 ) ( 　　　 ) in Canada.

G列　　are spoken

１つ１つ手作業で変換していたのですが、関数とかでうまく処理できる方法があるでしょうか。よろしくお願いします。

回答No.7

fumufumu_2006 · ベストアンサー

ANo.6です。
>>この状態で、シートに戻って、B1の数字を変えてみてください。
>>モジュールが表示され、先ほど赤く反転させた場所が、黄色の反転表示になるはずです。
>黄色に反転しません。
マクロが有効になっていないか、名前が違うか、場所が違うか、その他か・・・
と言う訳で、以下を試してください。

まず、新規作成された、何も無いブックで試してください。
Excelの画面で[ツール][マクロ][マクロ]で、[マクロ名]に[makeQuestion]と入力して[作成]ボタンを押す。
すると、画面がVBE(VBAを編集する画面)に変わります。
1行追加して、下記のようにしてください。
Sub makeQuestion()
MsgBox "test"
End Sub
次に、Excelの画面で[ツール][マクロ][マクロ]で、[makeQuestion]を選択して（これしかないので選択されているはずです）、[実行]ボタンを押す。
これで、メッセージボックスで"test"と表示しなければ、マクロが実行できない状態です。
その場合は、gooやgoogleで「excel マクロが実行できない」で調べて、実行できるようにしてください。
ただ、ANo.3への捕捉で、
>実行しますと、「１３　型が一致しません」というエラーが出ます。
という状況から、マクロは実行できるはずなので、それは無いとは思いますが・・・
以下は、メッセージが表示した場合です。
Sub makeQuestion()
の「Sub」を「Function」に変更してみてください。
Function makeQuestion()
MsgBox "test"
End Function
と「End Sub」が自動的に「End Function」に変わると思います。
この状態で、Excelに戻り、C1に「=makeQuestion()」と入力してください。
メッセージボックスで"test"と表示するはずです(C1の値は0になる）。
これが表示しない場合は・・・打ち込みミスしてないか、「=」や「()」を忘れていないかを確認してください。
以上で、関数が動く事が確認できます。
もう一度標準モジュールの画面に戻って、先ほどの3行のプログラムを消して、その場所にANo.2のモジュールをコピーしてください。（同じ関数名があるとエラーになります）
一応[デバッグ][VBAProjectのコンパイル]で、エラーが出ない事を確認してください。
Excelに戻って
A1:English and French are spoken in Canada
B1:4
C1:=makequestion(A1,B1,20)
D1:=makeanswer(A1,B1)
E1:4,5
F1:=makequestion(A1,E1,20)
G1:=makeanswer(A1,E1)
を入れてください。
これでもC1,D1,F1,G1に何も表示しないでしょうか？
または、期待した文字列にならないでしょうか？

期待した通りに表示されたら、うまく動かなかったブックのモジュールのなどを比較してみてください。

お礼

なんだか入れ違いになったようですみません。先ほど下に成功と感動とお礼の言葉を送ったとこです。どうもありがとうございました。
うまくできて大変喜んでおります。

回答No.6

fumufumu_2006

ANo.2です。

A1:English and French are spoken in Canada
B1:4
C1:=makequestion(A1,B1,20)
D1:=makeanswer(A1,B1)
E1:4,5
F1:=makequestion(A1,E1,20)
G1:=makeanswer(A1,E1)は入れております。
の状態(C1,D1,F1,G1は関数なので"="から入力)で、C1,D1,F1,G1に問題と答えが表示しませんか?
または、何か（問題そのままとか）が表示されますか?
何も表示しない場合は、標準モジュールの
Function makeQuestion(str As String, n As String, Optional answerSize As Integer = 0) As String
の部分にカーソルを合わせて、ファンクションキーのF9を押してください（ブレークポイントの設定）。
赤色の反転表示になると思います。
この状態で、シートに戻って、B1の数字を変えてみてください。
モジュールが表示され、先ほど赤く反転させた場所が、黄色の反転表示になるはずです。
モジュールが表示されない場合は、関数名が違っているか、マクロが無効になっています。

p.s.
ブレークポイントの設定を解除する場合は、同じ場所で再びファンクションキーのF9を押してください
または、[デバッグ][すべてのブレークポイントの解除]を実行する。

お礼

うまくできました。すごいですね。一瞬に希望通りの文が作成できて感動しています。今まで手作業で（　　　　）に変換していたので、ウソみたいに楽になります。
よくわかるように説明までていねいにつけていただいて本当にありがとうございました。
うまくできないのは、私の初歩的な不慣れで、あちこちいじくっていました。最初は何も変化しなかったのに、次は#NAME?　に変わっていたのです。
それを見過ごしていまして、やっとご指摘していただいた関数名が違っているに気づきました。
Function makeAnswer　大文字と小文字の違いだとやっとたどりついた次第です。
2度にわたって、ごていねいに教えていただきまして本当にありがとうございました。

補足

重ねてご回答ありがとうございます。
やはり何も起こりません。

>赤色の反転表示になると思います。
この通りになります。

>この状態で、シートに戻って、B1の数字を変えてみてください。
モジュールが表示され、先ほど赤く反転させた場所が、黄色の反転表示になるはずです。
黄色に反転しません。

モジュールが表示されない場合は、関数名が違っているか、マクロが無効になっています。
マクロは最初に有効をクリックしています。
C、D、F、G列は　#NAME?　となったままです。
やはりどっかの操作がまずいのでしょうか。
関数名はあっていると思うのですが。
せっかくコードを作っていただいたのに申し訳ありません。
何かわかることがあればよろしくお願いします。

回答No.5

noname#204879

C1: =REPLACE(A1,FIND("_",SUBSTITUTE(A1," ","_",B1-1)),FIND("_",SUBSTITUTE(A1," ","_",B1))-FIND("_",SUBSTITUTE(A1," ","_",B1-1))," ( 　　　 )")
D1: =MID(A1,FIND("_",SUBSTITUTE(A1," ","_",B1-1))+1,FIND("_",SUBSTITUTE(A1," ","_",B1))-FIND("_",SUBSTITUTE(A1," ","_",B1-1))-1)

お礼

ANo.2さんの処理でうまくできました。
それにしても関数だけで、うまくできるものですね。少し、関数を調べながら自分でもと思って、試みたのですが、REPLACE、FIND、SUBSTITUTEと組み合わさると、訳がわからなくなりました。

大変なお手数をおかけしました。ありがとうございました。

補足

回答ありがとうございます。
関数だけでうまくできるものですね。
先頭の語と最後の語を（　　　）にするのがエラーになります。

あと、２語に(　　　）をしたいときが無理ですね。
一度、（　　　）１つに変換してH列に文を作り、それをもう一度この関数でやろうとしましたが、（（　　　　　））のようになってしまいました。
これが解決できれば助かるのですが。
よろしくお願いします。

回答No.4

mitarashi

#1です。
・ユーザー定義関数を入力したC1に何かエラーは出ますか？
・メニューバーのfxから、ユーザー定義関数のリストを表示させたときに、testが表示されますか？
・マクロを有効にする設定になっていますか？
・VBEで、下記コードも追加して、test2を実行※したとき、B2に変換した文字列が表示されますか？（シート名はご使用環境に合わせてください）
Sub test2()
Debug.Print test(Sheets("Sheet1").Range("A1").Value, 3)
Sheets("Sheet1").Range("B1").Value = test(Sheets("Sheet1").Range("A1").Value, 3)
End Sub
※メニューバーのツール／マクロ／マクロから、test2を実行するか、VBEでSub test2のところで、メニューの▲をクリックしてください。

お礼

ANo.2さんの処理でうまくできました。
私の不慣れで大変なお手数をおかけしました。ありがとうございました。

回答No.3

imogasi

この程度になると関数では無理でVBAでしょう。
それも処理の流れのコントロール（例えばフラグ下記ではfndを使わざるを得ないものは）が難しいものと思う。
標準モジュールに
Sub test01()
d = Range("A65536").End(xlUp).Row '最下行
For i = 1 To d '最下行まで繰り返し
s = Cells(i, "A") 'A列
s1 = Split(s, " ") 'スペースで分離
k = Split(Cells(i, "B"), ",") 'カンマで区切りで分離
s2 = ""
fnd = "N" 'B列の数に該当したかのフラグ。その初期値。
For j = 0 To UBound(s1)
For l = 0 To UBound(k)
If j = k(l) - 1 Then
fnd = "Y"
End If
Next l
If fnd = "Y" Then 'B列数字に該当ー＞（）を入れる
s2 = s2 & " ( ) "
fnd = "N"
Else 'B列数字に該当せず
s2 = s2 & " " & s1(j)
End If
Next j
'----結果をC,D列にセット
Cells(i, "C") = s2
For l = 0 To UBound(k)
Cells(i, "D") = Cells(i, "D") & " " & s1(k(l) - 1)
Next l
Next i
End Sub
ーーー
例データ
English and French are spoken in Canada. 4 English and French ( ) spoken in Canada. are
English and French are spoken in Canada. 4,5 English and French ( ) ( ) in Canada. are spoken
English and French are spoken in Canada. 4,6 English and French ( ) spoken ( ) Canada. are in
English and French are spoken in Canada. 1,7 ( ) and French are spoken in ( ) English Canada.
English and French are spoken in Canada. 2,4,6 English ( ) French ( ) spoken ( ) Canada. and are in
ーー
こういう手続式のプログラム（コマンドの前後の按配が正しい必要があるもの）はテストが十分必要です。テストが十分といえないですがよろしく。

お礼

ANo.2さんの処理でうまくできました。
私の不慣れで大変なお手数をおかけしました。ありがとうございました。

補足

回答ありがとうございます。
実行しますと、「１３　型が一致しません」というエラーが出ます。
私にはよくわかりません。
よろしくお願いします。

回答No.2

fumufumu_2006

こんなのはどうでしょうか？

これもユーザー関数です。
ユーザー関数は基本的には答えを返すだけなので、問題を作る関数と答えを作る関数をわけました。
標準モジュールに下のモジュールをコピーしてもらえれば、
makeQuestion(文章 , 答えの単語位置 [,答え欄の幅])
makeAnswer(文章 , 答えの単語位置)
と言う関数が使えるようになります。
makeQuestionの答え欄の幅は省略可能ですが、省略すると答えの文字数に比例した幅になります。

用意ができたら、質問のA列-G列が1行目だった場合、C1,D1,F1,G1を以下にしてみてください。
C1=makequestion(A1,B1,20)
D1=makeanswer(A1,B1)
F1=makequestion(A1,E1,20)
G1=makeanswer(A1,E1)

'以下を標準モジュールへ
'問題を作る
'str:元の文
'n:カッコにする単語の番号(複数はカンマで区切る)
'answerSize:答えの幅を固定幅にする場合(省略可)
Function makeQuestion(str As String, n As String, Optional answerSize As Integer = 0) As String
Dim w() As String
Dim p() As String
Dim i As Integer
Dim spaceSize As Integer
w = Split(str, " ")
p = Split(n, ",")
For i = 0 To UBound(p)
If answerSize > 0 Then
spaceSize = answerSize
Else
spaceSize = Len(w(p(i) - 1)) + 5 '()内サイズは答えの幅に適当な定数を加えた幅 +5は適当に調節してください
End If
w(p(i) - 1) = "(" & Space(spaceSize) & ")"
Next
makeQuestion = Join(w, " ")
End Function

'答を作る
'str:元の文
'n:カッコにする単語の番号(複数はカンマで区切る)
Function makeAnswer(str As String, n As String) As String
Dim w() As String
Dim p() As String
Dim i As Integer
Dim ans As String
w = Split(str, " ")
p = Split(n, ",")
For i = 0 To UBound(p)
ans = ans & w(p(i) - 1) & " "
Next
makeAnswer = Trim(ans)
End Function

補足

早々とご回答ありがとうございます。
当方、VBEに素人で、標準モジュールにコードを貼り付けて実行したものの何も起こりません。今まではいろんなデータ処理をユーザー関数を教えてもらってうまくいっていました。
C1=makequestion(A1,B1,20)
D1=makeanswer(A1,B1)
F1=makequestion(A1,E1,20)
G1=makeanswer(A1,E1)は入れております。

操作のどこがおかしいのかわかりません。初歩的なことかと思いますが対処法をよろしくお願いします。

回答No.1

mitarashi

ユーザー関数を作成してみました。標準モジュールに下記のコードを貼り付けて下さい。
A1セルに目的の文字列がある場合、
=test(A1,3,4) または　=test(A1,3)
という様に式を入れます。置換する文字列数は1～2個まで対応しておりますが、オプション引数と、Ifの処理を追加してやれば、もっと増やせます。
Function test(targetString As String, Optional OPT1 As Long, Optional OPT2 As Long)
Dim words As Variant
Dim buf As String
Dim i As Long

words = Split(targetString, " ")
For i = 0 To UBound(words)
If i + 1 = OPT1 Then words(i) = "( )"
If i + 1 = OPT2 Then words(i) = "( )"
Next i
buf = words(0)
For i = 1 To UBound(words)
buf = buf + " " + words(i)
Next i
test = buf
End Function
実は、ユーザー関数でもOptionalキーワードが有効な事を初めて知りました。当方Excel2000での検証です。

補足

早々とご回答ありがとうございます。
当方、VBEに素人で、標準モジュールにコードを貼り付けて実行したものの何も起こりません。今まではいろんなデータ処理をユーザー関数を教えてもらってうまくいっていました。C1に=test(A1,3)を入れてあります。
操作のどこがおかしいのかわかりません。初歩的なことかと思いますが対処法をよろしくお願いします。