Excel：関数の引数指定について

質問

こんにちは。
現在Excelで、ある単語がどの文章に出てくるかの集計をしたいと考えています。

シート1：
A列：文番号（例：x1）
B列：日本語の文章（例：今日はいい天気だ）（列全体に「文章」というラベル付けがしてあります）

シート2：
A列：単語番号（例：y1）
B列：単語（例：セルB1＝今日）

このとき、シート2のC列に、その単語が出てきた文章の文番号を入れたいと考えています。（この例でいうとx1）

IF関数とCOUNTIF関数を利用して
IF(COUNTIF（文章,"*"&B1&"*"),hoge,"")
という形にするところまでは出来たのですが、「hoge」の部分に何を入れればいいかが分かりません。
「hoge」の部分を
VLOOKUP(B1,文章,1,FALSE)　や
OFFSET(文章,0,-1)
にしてみたのですが、その単語が含まれていない、おかしな文番号が入ってしまいます。
これらの関数の「文章」の所を書きかえればいいと思うのですが、何にすればいいかが分かりません。

もし分かる方がいらっしゃったら教えていただきたいです。
よろしくお願いします。

回答No.9

tom04 · ベストアンサー

続けてお邪魔します。

＞ただ、シート1のB列C列の両方に文字等を入れると、マクロを実行しても結果が出なくなってしまいました。

確かにB3.C3セルに何らかの項目名を入力すると表示されませんね。
何もSheet2に表示されない！というコトであれば、怪しいと思われるのが
フィルタを掛けた場合、すべての行が非表示になっているのかもしれません。
（前回のコードではSheet2のB列文字列を「含む」でフィルタを掛けるようにしています）

＞.Range("D3").AutoFilter field:=1, Criteria1:="*" & wS.Cells(i, "B") & "*"
を
＞.Range("A3").AutoFilter field:=4, Criteria1:="*" & wS.Cells(i, "B") & "*"
としてみてください。

今度はちゃんと表示されれば良いのですが・・・m(_ _)m

お礼

ありがとうございます。

↑で教えていただいた部分を書き直したところ、きちんと結果が表示されるようになりました！

初めてマクロを扱ったのですが、全然分からないことばかりで、何度もお聞きしてしまい申し訳ありませんでした。
でもおかげでデータ管理が楽になり、非常に助かりました(>_<)
これを機にマクロについて簡単な所だけでも勉強してみようかと思います・・・！

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

回答No.8

tom04

No.5・7です。

補足に関して・・・
＞思い当たる節としては
（1）シート1のA1,B1セルが空白でなく文字が入っていること
（2）シート2のA2,B2セルも同様であること
（3）シート1のB,C列とシート2のC~F列も文字が入っています）
（4）文番号、単語番号を付けるのに関数を用いていること
＞ぐらいです

（1）～（4）すべてについては全く問題ないと思います。
ただし、G列以降に項目等がある場合はG列以降すべてを消去していますので、項目等も消えてしまいます。

おそらく、Sheet名がネックになっているのではないでしょうか？
ご自身のSheetの配置で
元データがSheet見出しの一番左側・表示させたいSheetがSheet見出しの左から2番目に配置してあるという前提で

前回のコードの
＞Set wS = Worksheets("Sheet2")
を
＞Set wS = Worksheets(2)
に

＞With Worksheets("Sheet1")
を
＞With Worksheets(1)
に変更してみてください。

これでSheet名に関係なくマクロが実行されます。
（どんなSheet名になっていても構いません）

＞Worksheets(1)
はSheet見出しの一番左側のSheet
＞Worksheets(2)
はSheet見出しの左から2番目のSheet
というコトになります。m(_ _)m

補足

ありがとうございます。

教えていただいた通りシート名の部分を変えてみたのですが、それでも上手く出来ませんでした。

そこで、新規のファイルに文番号、文章、単語番号、単語のみを（もとのファイルと同じ行・列になるように）コピーしてきてマクロを実行してみると、ちゃんと結果が出ました！

ただ、シート1のB列C列の両方に文字等を入れると、マクロを実行しても結果が出なくなってしまいました。
（B列だけ・C列だけなら大丈夫だったのですが、なぜか両方に入れるとだめになります）
うーん、いったいなぜなのでしょうか(-_-;)

回答No.7

tom04

No.5です。
補足を読みました。

＞A4,A5,A6...（列方向）：文番号
＞D4,D5,D6...（列方向）：文章
＞シート2
＞A9,A10,A11...（列方向）：単語番号
＞B9,B10,B11...（列方向）：単語
＞で、結果はG,H,I...列に出力させたいと考えています。

というコトは↓の画像のようにSheet1の3行目はタイトル行・Sheet2の8行目もタイトル行になっているという前提のコードです。
コード内に若干の説明を加えていますので、
適宜変更してみてください。
やり方は前回同様で標準モジュールです。

Sub Sample2() 'この行から
'★変数の宣言
Dim i As Long, lastRow As Long, lastCol As Long, wS As Worksheet
'★Sheet2を　wS　という変数に格納
Set wS = Worksheets("Sheet2")
'★Sheet2の最終行取得
lastRow = wS.Cells(Rows.Count, "B").End(xlUp).Row
'★Sheet2の最終列取得
lastCol = wS.UsedRange.Columns.Count
Application.ScreenUpdating = False
'★G列以降にデータがあれば
If lastCol > 6 Then
'★7列目～最終列を消去
Range(wS.Columns(7), wS.Columns(lastCol)).ClearContents
End If
'★Sheet1の
With Worksheets("Sheet1")
'★Sheet1の最終行取得
lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
'★Sheet2のB列9行目～最終行まで
For i = 9 To wS.Cells(Rows.Count, "B").End(xlUp).Row
'★Sheet1のD3セルでフィルタを掛ける
.Range("D3").AutoFilter field:=1, Criteria1:="*" & wS.Cells(i, "B") & "*"
'★フィルタを掛けたあと4行目以降にデータがあれば
If .Cells(Rows.Count, "A").End(xlUp).Row > 3 Then
'★A4セル～A列最終行までの可視セルを
Range(.Cells(4, "A"), .Cells(lastRow, "A")).SpecialCells(xlCellTypeVisible).Copy
'★Sheet2のG列、i行に貼り付け
wS.Cells(i, "G").PasteSpecial Paste:=xlPasteAll, Transpose:=True
'★次の行へ（Sheet2の i 行）
End If
Next i
.AutoFilterMode = False
End With
Application.ScreenUpdating = True
End Sub 'この行まで

今度はどうでしょうか？m(_ _)m

補足

ありがとうございます。

コードの説明も付け加えてくださったお蔭でより分かりやすくなりました。

シート1、2の内容は添付してくださったものとほぼ同じなので、↑のシート名だけを自分のものに変更して実行したのですが、G列以降に何も表示されませんでした。
添付してくださった画像と同じエクセルを作って実行してみると、正しく実行されて結果も表示されたので、自分のものが悪いのだと思うのですが・・・。

思い当たる節としては
・シート1のA1,B1セルが空白でなく文字が入っていること
・シート2のA2,B2セルも同様であること
（シート1のB,C列とシート2のC~F列も文字が入っています）
・文番号、単語番号を付けるのに関数を用いていること
ぐらいです。

とりあえず、もう少し考えてみます。

回答No.6

kkkkkm

> 理想としては、シート1のB列に「今日はいい天気だ」と入力されたら、シート2のB列が「今日」「天気」となっている部分の隣のセル（例：C1とC2）に文番号を表示させたいなと思っていますが、それは難しいのでしょうか？

検索用のワードが入るセルが一個という意識で回答していましたので、ミスリードしてしまったようです。

条件が数個でしたら作業列を数個作って隠してしまえば簡単ですが、多数になるとちょっと…という感じになります。配列でどうにかなるのかと思いましたが、私のつたない頭では解決に至りませんでした。

私が実際に作るとしても、No.5のtom04さんのようにマクロで対応してしまいます。

お礼

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

＞検索用のワードが入るセルが一個という意識で回答していましたので、ミスリードしてしまったようです。
こちらこそ、分かり辛い質問の仕方をしてしまい申し訳ないです。

Excelで関数を使ったことがほとんど無かったので、色々と勉強になりました。
何度も回答をくださって本当に助かりました。
ありがとうございました。

回答No.5

tom04

こんばんは！
横からお邪魔します。

解釈が違っていたらごめんなさい。
↓の画像で左側がSheet1で右側のSheet2のように表示させたい！という解釈です。
VBAになってしまいますが、一例です。

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

Sub Sample1() 'この行から
Dim i As Long, lastRow As Long, lastCol As Long, wS As Worksheet
Set wS = Worksheets("Sheet2")
lastRow = wS.Cells(Rows.Count, "B").End(xlUp).Row
lastCol = wS.UsedRange.Columns.Count
Application.ScreenUpdating = False
If lastCol > 2 Then
Range(wS.Columns(3), wS.Columns(lastCol)).ClearContents
End If
With Worksheets("Sheet1")
lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To wS.Cells(Rows.Count, "B").End(xlUp).Row
.Range("A1").AutoFilter field:=2, Criteria1:="*" & wS.Cells(i, "B") & "*"
If .Cells(Rows.Count, "A").End(xlUp).Row > 1 Then
Range(.Cells(2, "A"), .Cells(lastRow, "A")).SpecialCells(xlCellTypeVisible).Copy
wS.Cells(i, "C").PasteSpecial Paste:=xlPasteAll, Transpose:=True
End If
Next i
.AutoFilterMode = False
End With
Application.ScreenUpdating = True
End Sub 'この行まで

※　関数でないので、データ変更があるたびにマクロを実行する必要があります。
的外れなら無視してください。m（_ _）m

補足

ありがとうございます。
マクロですか、難しそうですね・・・。

解釈はtom04さんの考え方で合っています。

質問の際、簡単にするためにシートの内容を最初の質問のようにしたのですが、実際は
シート1
A4,A5,A6...（列方向）：文番号
D4,D5,D6...（列方向）：文章
シート2
A9,A10,A11...（列方向）：単語番号
B9,B10,B11...（列方向）：単語
で、結果はG,H,I...列に出力させたいと考えています。

この場合、教えてくださったマクロのどの部分を書きかえれば大丈夫でしょうか？
お時間のあるときでいいので教えていただけると助かります。

回答No.4

kkkkkm

> 面倒そうだなとスルーしていたのですが、

確かに、見た目は面倒そうですが、sou-e9 さんの場合だと、シート1のB列から,"*"&Sheet2!$B$1&"*"を探してそれに連番を振る列を作り、その連番を1から順番に見つけて見つかった行のA列のデータを表示するという理屈です。

その連番を見つけるキーがROW(A1)という関数になります。ROW関数は指定したセルの行番号を返します。=ROW(A1)は1を=ROW(A2)は2を返します。関数の中で上下方向にフィルした結果に連番を利用したい場合よく使う手です。左右方向にフィルする場合はCOLUMN関数を利用します。

作業列の式は(参照ページのD列)
=IFERROR(IF(MATCH("*"&Sheet2!$B$1&"*",B2,0)>0,COUNTIF($B$2:B2,"*"&Sheet2!$B$1&"*"),""),"")
になります。

結果は(作業列をD列とした場合)表示したい行に以下の式を必要なだけコピーでいけそうですがいかがでしょう。
=IFERROR(IF(MAX($D$2:$D$11)<COLUMN(A1),"",INDEX($A$2:$A$11,MATCH(COLUMN(A1),$D$2:$D$11,0))),"")

補足

詳しく教えていただきありがとうございます。

まだ少しサイトを見ながら勉強中でちゃんと仕組みが理解できていないのですが、
教えていただいたやり方だと、確かに「今日」という単語が含まれる複数の文の文番号を取り出すことができると思いますが、
例えばシート2のB1セルに「今日」、B2セルに「天気」とあった場合、教えていただいた方法だと1個の単語に対してしか文との照合が出来ないですよね？（"*"&Sheet2!$B$1&"*"というように$マークで固定しているため）
理想としては、シート1のB列に「今日はいい天気だ」と入力されたら、シート2のB列が「今日」「天気」となっている部分の隣のセル（例：C1とC2）に文番号を表示させたいなと思っていますが、それは難しいのでしょうか？

説明が上手くできず申し訳ないです。
よろしくお願いします。

回答No.3

kkkkkm

> x1とx2の両方を出したい（同じセル内に「x1x2」か、同じ行のセルに「x1」「x2」としたい）のですが、何かいい方法はあるでしょうか？

こちらのページが参考になると思いますのでチャレンジしてみてください。

http://www.eurus.dti.ne.jp/~yoneyama/Excel/waza/fukusu_data.html

上記のページで以下の部分

2.作業列の連番順にデータを取り出します。
G2セルは=IF(MAX($D$2:$D$11)<ROW(A1),"",INDEX(A$2:A$11,MATCH(ROW(A1),$D$2:$D$11,0)))　と入力します。

と書かれている部分はたぶん「G5セルは」の誤植のような気がします。

補足

ありがとうございます。

教えていただいたサイトは自分で検索していたときにも見つけていて、面倒そうだなとスルーしていたのですが、やっぱりこのサイトの方法でやっていかないと駄目みたいですね・・・
とりあえず自分で頑張ってみようと思います。
また何か分からないところがあったらお聞きするかもしれませんが、どうぞよろしくお願いします。

回答No.2

kkkkkm

> ・単語と文章の一致がないときに#N/Aになってしまう（理想としては空白にしたい）
> こちらだけ教えていただきたいと思いますm(__)m

2003だと
=IF(ISNA(INDEX(Sheet1!A1:B30,MATCH("*"&B1&"*",Sheet1!B1:B30,0),1)),"",INDEX(Sheet1!A1:B30,MATCH("*"&B1&"*",Sheet1!B1:B30,0),1))

2007以降だと
=IFERROR(INDEX(Sheet1!A1:B30,MATCH("*"&B1&"*",Sheet1!B1:B30,0),1),"")

としてください。

参照は元の回答のままですので、適宜変更してください。

補足

ありがとうございます。
IFERRORで直すことが出来ました。

あと1つ質問があるのですが、
教えていただいた方法でやると、例えば「今日」という単語がx1とx2の2つの文章に入っていた場合、x1しか結果として出ないです。
x1とx2の両方を出したい（同じセル内に「x1x2」か、同じ行のセルに「x1」「x2」としたい）のですが、何かいい方法はあるでしょうか？

よろしくお願いします。

回答No.1

kkkkkm

以下の式で出ませんでしょうか。とりあえず30行までしか指定してません。

=INDEX(Sheet1!A1:B30,MATCH("*"& B1 & "*",Sheet1!B1:B30,0),1)

お礼

補足の補足です

・列全体にオートフィルすると、変わって欲しいMATCHの検査値である「B1」だけでなく、A1やB30などもすべて1つずつずれてしまう
に関しては絶対参照を使うことで解決できました。

・単語と文章の一致がないときに#N/Aになってしまう（理想としては空白にしたい）
こちらだけ教えていただきたいと思いますm(__)m

補足

ありがとうございます。

教えていただいた数式で一応出来たのですが
・列全体にオートフィルすると、変わって欲しいMATCHの検査値である「B1」だけでなく、A1やB30などもすべて1つずつずれてしまう
・単語と文章の一致がないときに#N/Aになってしまう（理想としては空白にしたい）
という問題が発生してしまいます。
これらを直すことも出来るでしょうか？

Excelの関数はほとんど使ったことが無く初心者なので聞くことばかりで申し訳ないのですが、教えていただけると助かります。