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

Question

こんにちは。
現在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)
にしてみたのですが、その単語が含まれていない、おかしな文番号が入ってしまいます。
これらの関数の「文章」の所を書きかえればいいと思うのですが、何にすればいいかが分かりません。

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

tom04 · Accepted Answer

続けてお邪魔します。

＞ただ、シート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

tom04 · Answer

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

tom04 · Answer

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

kkkkkm · Answer

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

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

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

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

tom04 · Answer

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

解釈が違っていたらごめんなさい。
↓の画像で左側が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

kkkkkm · Answer

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

確かに、見た目は面倒そうですが、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))),"")

kkkkkm · Answer

> 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セルは」の誤植のような気がします。

kkkkkm · Answer

> ・単語と文章の一致がないときに#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),"")

としてください。

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

kkkkkm · Answer

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

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

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

続けてお邪魔します。

No.5・7です。

この回答への補足

No.5です。

この回答への補足

こんばんは！

この回答への補足

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

この回答への補足

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

この回答への補足

> ・単語と文章の一致がないときに#N/Aになってしまう（理想としては空白にしたい）

この回答への補足

以下の式で出ませんでしょうか。

この回答への補足

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング