エクセルで列の一致項目のみを他シートに行単位で表示する方法は？

質問

sheet２のＡ１に書き込まれた文字をsheet１のＢ５以降全て及びＢ５～Ｂ１００の範囲の中から一致する文字の行をｓｈｅｅｔ２の５行目から全て書き出す方法を教えて下さい。

回答No.8

daidai024 · ベストアンサー

うまくいったようで、よかったですね。
ANo.6をベースに、補足と追加質問の回答です。

(1)項目を追加する場合
検索範囲と列番号の両方を変更する必要があります。

検索範囲
Sheet1!$A:$F→Sheet1!$A:$M

列番号
A列を2とすると、B列は3、、、、M列は13になります。

例として、Sheet2のM5は、
=IF(ROW()-4>$B$1,"",VLOOKUP($A$1&(ROW()-4),Sheet1!$A:$M,13,FALSE))

ANo.7　はおまけなので、OFFSET()、MATCH()は新たに質問を立ててください。

(2)隠したＡ列の再表示方法
Sheet1の左上のます（１の上でＡの左）をクリックして全体を指定します。
表示→列→再表示
これで隠した列がすべて表示されます。

(3)日付の表示のそろえ
これはわかりません。私の場合は左寄せにしています。何か良い方法があるかもしれませんので新たに質問してください。

お礼

うまく出来ました。
＞例として、Sheet2のM5は
の例は大変分りやすく助かりました。
細かなことまで色々と有難うございました。

回答No.7

daidai024

VLOOKUP関数を使わない方法もあります。
先ほどの回答でうまくいかなかった場合は、こちらでどうぞ。

A5に=IF(ROW()-4>$B$1,"",OFFSET(Sheet1!$A$1,MATCH($A$1&(ROW()-4),Sheet1!$A:$A,0)-1,1,1,1))
B5に=IF(ROW()-4>$B$1,"",OFFSET(Sheet1!$A$1,MATCH($A$1&(ROW()-4),Sheet1!$A:$A,0)-1,2,1,1))
C5に=IF(ROW()-4>$B$1,"",OFFSET(Sheet1!$A$1,MATCH($A$1&(ROW()-4),Sheet1!$A:$A,0)-1,3,1,1))
D5に=IF(ROW()-4>$B$1,"",OFFSET(Sheet1!$A$1,MATCH($A$1&(ROW()-4),Sheet1!$A:$A,0)-1,4,1,1))

お礼

こちらの方法も試してみました。

VLOOKUP関数同様うまくいきました。
どちらを使っても同じなのでしょうか？別件にはなりますが、隠したＡ列の再表示方法と日付列の段々（文字数の違いで年/月/日の位置が揃いません）を無くす方法が分りません。
改めて質問として投稿致したほうがよければその様に致します。

有難うございました。

補足

度々申し訳ありません。

ＡＮo1の補足でＡ～Ｅまでしか書いてありませんがＤ以降にも項目を追加して使用しようと思い列順に（□）の部分の数を単純に増やして行いましたがうまくいきません。（ANo7の1,□,1,1　ANo6の,Sheet1!$A:$F,□,FALSE）

sheet１の５行以降に書かれた任意件数の項目全て（今回使用したいのは作業列Aを除きB～M列まで）処理したいのですが方法が分りません。

回答No.6

daidai024

うまくいかないですね。
こちらではまともに表示されるのですが。EXCEL2002です。

＃Ｎ／Ａのエラーは　VLOOKUP関数によるもので、検索値が見つからないときにエラーとなります。入力に間違いはないでしょうか。
VLOOKUP関数の検索の型は、正規表現はFALSEですが、0としていました。
ヘルプの表示のとおりにFALSEに直して、再度掲載します。もう一度ためしてみてください。

Sheet1のA列を選択して、右クリック→挿入
A列を作業用セルにします。
A5に　=C5&COUNTIF($C$5:C5,C5)
以下、下にコピー
A列を選択し、右クリック→表示しない

Sheet2のA1に　交通費　と入力
B1に　=COUNTIF(Sheet1!C:C,A1)
C1に　件　と入力
A5に=IF(ROW()-4>$B$1,"",VLOOKUP($A$1&(ROW()-4),Sheet1!$A:$F,2,FALSE))　　　　
B5に=IF(ROW()-4>$B$1,"",VLOOKUP($A$1&(ROW()-4),Sheet1!$A:$F,3,FALSE))
C5に=IF(ROW()-4>$B$1,"",VLOOKUP($A$1&(ROW()-4),Sheet1!$A:$F,4,FALSE))
D5に=IF(ROW()-4>$B$1,"",VLOOKUP($A$1&(ROW()-4),Sheet1!$A:$F,5,FALSE))
A5のセルの書式設定を日付にする。
B5のセルの書式設定を文字列にする。
C5のセルの書式設定を数値にし、桁区切りにチェックを入れる。
D5のセルの書式設定を文字列にする。
A5～D5を選択し、下にコピー
ツール→オプション→表示→ゼロ値にチェックがついていたらはずす
シート名Sheet2を右クリック→移動またはコピー→コピーを作成にチェック→OK
Sheet2(2)のA1に昼食代と入力。

シート名Sheet2を右クリック→移動またはコピー→コピーを作成にチェック→OK
Sheet2(3)のA1に使用料と入力。

シート名を好みのものに直す。
以上。

お礼

＞ヘルプの表示のとおりにFALSEに直して
うまくいきました。お手間をとらせ大変有難うございました。
私のエクセルバージョンは2002（10.2614.2625）でした。

回答No.5

daidai024

たびたびすみません。間違えました。

誤
B2に　=COUNTIF(Sheet1!C:C,A1)
B3に　件　と入力

正
B1に　=COUNTIF(Sheet1!C:C,A1)
C1に　件　と入力

補足

訂正有難うございます。

Ａ１に入力されＢ１の件数分だけＡ，Ｂ，Ｃ，Ｄの５行以降に件数行全て#N/Aのエラーが表示されてしまいます。

回答No.4

daidai024

すみません。記入に間違いがありました。
改めて掲載します。

作業用セルを使う方法です。
Sheet1のA列を選択して、右クリック→挿入
A列を作業用セルにします。
A5に　=C5&COUNTIF($C$5:C5,C5)
以下、下にコピー
A列を選択し、右クリック→表示しない

Sheet2のA1に　交通費　と入力
B2に　=COUNTIF(Sheet1!C:C,A1)
B3に　件　と入力
A5に　=IF(ROW()-4>$B$1,"",VLOOKUP($A$1&(ROW()-4),Sheet1!$A:$F,2,0))
B5に　=IF(ROW()-4>$B$1,"",VLOOKUP($A$1&(ROW()-4),Sheet1!$A:$F,3,0))
C5に　=IF(ROW()-4>$B$1,"",VLOOKUP($A$1&(ROW()-4),Sheet1!$A:$F,4,0))
D5に　=IF(ROW()-4>$B$1,"",VLOOKUP($A$1&(ROW()-4),Sheet1!$A:$F,5,0))
A5のセルの書式設定を日付にする。
B5のセルの書式設定を文字列にする。
C5のセルの書式設定を数値にし、桁区切りにチェックを入れる。
D5のセルの書式設定を文字列にする。
A5～D5を選択し、下にコピー
ツール→オプション→表示→ゼロ値にチェックがついていたらはずす
シート名Sheet2を右クリック→移動またはコピー→コピーを作成にチェック→OK
Sheet2(2)のA1に昼食代と入力。

シート名Sheet2を右クリック→移動またはコピー→コピーを作成にチェック→OK
Sheet2(3)のA1に使用料と入力。

シート名を好みのものに直す。
以上。

補足

訂正有難うございます。
sheet２の手順を最後まで行いました。sheet２A1に交通費と入力しましたがB2の件数値のみの表示となりA1、B2、B3以外は空白となります。
sheet１　A列は確認の為表示状態で行ってます。

回答No.3

zap35

Sheet2のA5セルに以下の式をコピーして、下方向、および右方向にコピーしてください。その後各列のセルの書式を適切なものに再修正してください。

=IF(COUNTIF(Sheet1!$B:$B,$A$1)-ROW()+5>0,INDEX(Sheet1!A$1:A$100,LARGE(INDEX((Sheet1!$B$5:$B$100=$A$1)*ROW(Sheet1!$B$5:$B$100),),COUNTIF(Sheet1!$B:$B,$A$1)-ROW()+5)),"")

ただしこの式だとSheet1で空白だったセルは０と表示されます。
これがまずい場合は「ツール」→「オプション」でゼロ値は表示しない設定にします。「オプションで設定するのは困る」というのであればA5セルの式を以下に変更すれば良いです（ただし長いですよ）

=IF(COUNTIF(Sheet1!$B:$B,$A$1)-ROW()+5>0,IF(OFFSET(Sheet1!A$1,LARGE(INDEX((Sheet1!$B$5:$B$100=$A$1)*ROW(Sheet1!$B$5:$B$100),),COUNTIF(Sheet1!$B:$B,$A$1)-ROW()+5)-1,0)="","",INDEX(Sheet1!A$1:A$100,LARGE(INDEX((Sheet1!$B$5:$B$100=$A$1)*ROW(Sheet1!$B$5:$B$100),),COUNTIF(Sheet1!$B:$B,$A$1)-ROW()+5))),"")

お礼

手順が分らず申し訳ありません。ＡＮo６、７で出来ました。

有難うございました。

補足

ご回答有難うございます。

手順通りに行った後、sheet２　A１に交通費と入力するとsheet１をそのまま全て表示されてしまいます。
sheet２　A1が空白ですと表示は消えます。

回答No.2

daidai024

作業用セルを使う方法です。
Sheet2のA列を選択して、右クリック→挿入
A列を作業用セルにします。
A5に　=C5&COUNTIF($C$5:C5,C5)
以下、下にコピー
A列を選択し、右クリック→表示しない

Sheet2のA1に　交通費　と入力
B2に　=COUNTIF(Sheet1!C:C,A1)
B3に　件　と入力
A5に　=IF(ROW()-4>$B$1,"",VLOOKUP($A$1&(ROW()-4),Sheet1!$A:$F,2,0))
A6に　=IF(ROW()-4>$B$1,"",VLOOKUP($A$1&(ROW()-4),Sheet1!$A:$F,3,0))
A7に　=IF(ROW()-4>$B$1,"",VLOOKUP($A$1&(ROW()-4),Sheet1!$A:$F,4,0))
A5～A7を選択し、下にコピー
ツール→オプション→表示→ゼロ値にチェックがついていたらはずす
シート名Sheet2を右クリック→移動またはコピー→コピーを作成にチェック→OK
Sheet2(2)のA1に昼食代と入力。

シート名Sheet2を右クリック→移動またはコピー→コピーを作成にチェック→OK
Sheet2(3)のA1に使用料と入力。

シート名を好みのものに直す。
以上。

補足

ご回答有難うございます。

<Sheet2のA列を選択して、右クリック→挿入
sheet１のA列を選択して作業をしました。

以降手順の通り式を張りつけ行いましたがsheet２、３のB2に件数が表示されますが他何も表示されません。

よく理解できてないのかも知れませんが宜しくお願いします。

回答No.1

noname#204879

「書き込まれた文字」が「sheet２のＡ１」でなく例えば「sheet２のＡ２」で良ければ、[フィルタオプションの設定]で可能かと。

お礼

申し訳ありません。　よく理解出来ませんでした。

回答有難うございました。

補足

エクセルのヘルプで調べましたがよく分かりませんでした。
sheet１のＢ５より異なった科目が書き込まれています。sheet２，３，４と各シートのＡ１に科目を入れsheet１のＢ５以降の同一科目の行のみを全て書き込みたいです。
sheet１には
　　　　　Ａ　　　　　Ｂ　　　　　Ｃ　　　　　Ｄ　　　　　Ｅ
５　 2007/2/10 交通費　　 2,000 　　　 　　
６ 　2007/2/10 昼食代　　 650
７ 　2007/2/10 使用料　　 3,200 　6人分
８ 　2007/2/17 交通費　　 1,200
９ 　2007/2/19 交通費　　 260
10 　2007/2/20 昼食代　　 630

sheet２（A1に交通費）
　　　　　Ａ　　　　　Ｂ　　　　　Ｃ　　　　　Ｄ　　　　　Ｅ
５　 2007/2/10 交通費　　 2,000 　　　 　　
６ 　2007/2/17 交通費　　 1,200
７ 　2007/2/19 交通費　　 260
・
・
sheet３（A1に昼食代）
　　　　　Ａ　　　　　Ｂ　　　　　Ｃ　　　　　Ｄ　　　　　Ｅ
５　 2007/2/10 昼食代　　 650
６ 　2007/2/20 昼食代　　 630
・
・