エクセル２０００で教えて下さい。

質問

エクセル２０００で下記のような表を作りたいのですが
教えて下さい。
シート１を入力すれば勝手にシート２の表が作成されるように
したいのです。
シート１のD列に数値が入った場合のみ、その行のすべてが
シート２の上から順番に並ぶようにしたいのです。
参考までに大阪なら数値は必ず「入」の列に入ります。
東京なら数値は必ず「出」の列に入ります。
すべて関連付けされていますが名前は１００種類くらいあります。
同じ東京と入った場合でも数値もその都度変わります。
どなたかご教授下さい。

例
（シート１）
　　　　A　　　　　　　B　　　　　　C　　　　　　D
１　　日付　　　　　名前　　　　　出　　　　　入
２　　10/20 　　　　東京　　　　２００　　　　
３　　10/20　　　　　大阪　　　　　　　　　　３００
４ 　10/20　　　　　名古屋　　　４００
５ 　10/21　　　　　福岡　　　　３００　　　
６ 　10/21　　　　　仙台　　　　５５０
７ 　 10/22　　　　　京都　　　　　　　　　　９００
８　　10/23　　　　　東京　　　　５００
９ 　10/23　　　　　京都　　　　　　　　　　７５０

（シート２）
　　　　A　　　　　　　B　　　　　　C　　　　　　D
１　　日付　　　　　名前　　　　　出　　　　　入
２　　10/20　　　　　大阪　　　　　　　　　　３００
３ 　 10/22　　　　　京都　　　　　　　　　　９００
４ 　 10/23　　　　　京都　　　　　　　　　　７５０

回答No.15

Wendy02 · ベストアンサー

こんばんは。Wendy02です。

それでは、出なかったですね。

A4:

=IF(ROW($A1)>COUNT(Sheet1!$D$4:$D$310),"",INDEX(Sheet1!$A$4:$D$310,SMALL(INDEX(ROW($A$4:$A$310)*ISNUMBER(Sheet1!$D$4:$D$310)-2,,),COUNTBLANK(Sheet1!$D$4:$D$310)+ROW($A1))-1,COLUMN(A1)))

B4:(書式は数値系の"0,00#" など)

=IF(ROW($A1)>COUNT(Sheet1!$D$4:$D$310),"",INDEX(Sheet1!$A$4:$D$310,SMALL(INDEX(ROW($A$4:$A$310)*ISNUMBER(Sheet1!$D$4:$D$310)-2,,),COUNTBLANK(Sheet1!$D$4:$D$310)+ROW($A1))-1,COLUMN(D1)))

D4:
=IF(ROW($A1)>COUNT(Sheet1!$D$4:$D$310),"",INDEX(Sheet1!$A$4:$D$310,SMALL(INDEX(ROW($A$4:$A$310)*ISNUMBER(Sheet1!$D$4:$D$310)-2,,),COUNTBLANK(Sheet1!$D$4:$D$310)+ROW($A1))-1,COLUMN(B1)))

後は、フィルダウンでコピーします。
範囲が広がり、数式を直すときは、編集-置換で、310 →400 などと直してください。一括で直すことが出来るようにしてあります。これでどうでしょうか？

お礼

Wendy02さん
お陰でなんとか上手くいきました。
いろんな意味で勉強になりました。
本当に最後まで丁寧に御教授下さり感謝しております。
今、仕事の中で少しでも便利になるよういろんなフォーマットを作成して
いるところです。
また次のフォーマットを作成中でお世話になる事も有ると思いますが
その時はまた懲りずに御教授下されば有り難いです。
今回は本当にお世話になり有難うございました。

回答No.14

Wendy02

こんばんは。Wendy02です。

Sheet2:
A4

=IF(ROW($A1)>COUNT(Sheet1!$B$4:$B$54),"",INDEX(Sheet1!$A$4:$D$54,SMALL(INDEX(ROW($A$4:$A$54)*ISNUMBER(Sheet1!$B$4:$B$54)-2,,),COUNTBLANK(Sheet1!$B$4:$B$54)+ROW($A1))-1,COLUMN(A1)))

で、右にフィル・ドラッグでコピーしてみてください。
(一括で範囲を置換できるように、工夫を加えてみました。範囲を決めて行ってみてください。)

B4:は、書式を標準か数値にして、C4:は削除、D4は、そのままで
出てくるかと思います。

Sheet1 のB列は、数値が入っていることが条件です。

お礼

Wendy02さん
本当に何度もすみません。
シート１は当初の質問から列は変わっていません。
行が４行目からになっただけでした。

シート１
　　　　A　　　　　　　B　　　　　　C　　　　　　D
３　　日付　　　　　名前　　　　　出　　　　　入
４　　10/20 　　　　東京　　　　２００　　　　
５　　10/20　　　　　大阪　　　　　　　　　　３００
６ 　10/20　　　　　名古屋　　　４００
７ 　10/21　　　　　福岡　　　　３００　　　
６ 　10/21　　　　　仙台　　　　５５０
７ 　 10/22　　　　　京都　　　　　　　　　　９００
８　　10/23　　　　　東京　　　　５００
９ 　10/23　　　　　京都　　　　　　　　　　７５０

でシート２が
　　　　A　　　　　　B　　　　　　C　　　　　　D
３　　日付　　　　　　入　　　　　　　　　　　名前　　
４　　10/20　　　　　３００　　　　　　　　　大阪
５ 　 10/22　　　　　９００　　　　　　　　　京都
６ 　10/23　　　　　７５０　　　　　　　　　　京都

C列は空白です。

本当に何度も何度もこちらの間違いばかりで申し訳ございません。
お付合いして頂いている事、心から感謝しております。

補足

↓に一つ書き忘れました。
シート１の行は３００行くらいになりそうです。

回答No.13

Wendy02

こんにちは。Wendy02です。
記録をみると、こちらは、以下のような表で作られています。

もしかしたら、話が２転３転して、元とは違うものになっていませんか？もう一度、確認しなおしたほうがよいですね。

Sheet2　－結果：
　　　A　　　　　　　B　　　　　　C　
１
２
３　　日付　　　　　　入　　　　名前
４　　10/20　　　　３００　　　大阪
５ 　 10/22　　　　９００　　　京都
６ 　 10/23　　　　７５０　　　京都

Sheet1：データ

　　　A　　　　　B　　　　　C　　　　D
１
２
３　　日付　　　　入　　　出　　　名前
４　10月20日　　　　　　　　　　　東京
５　10月20日　　300　　　　　　　大阪
６　10月20日　　　　　　　400　　名古屋
７　10月21日　　　　　　　300　　福岡
８　10月21日　　　　　　　550　　仙台
９　10月22日　　900　　　　　　　京都
10　10月23日　　　　　　　500　　東京
11　10月23日　　750　　　　　　　京都

お礼

度々すみません。
私の説明が間違えていました。大変申し訳ございません。
Sheet2ですがC列は空白のままで後に手作業で文字を入力します。
それでD列に名前が入ります。
Sheet1、Sheet2共に項目が３行目、開始が４行目で上記の通りです。
ですので式が入力されるのはSheet2のA4、B4、D4になると思います。

回答No.12

Wendy02

こんにちは。Wendy02です。

それでは、こんな風になります。
余計なところまで直して混乱されてしまうかもしれませんが、SMALLの中の「104-4 = 100」 の長さの原則は保たれていますよね。

Sheet2 のA4
=IF(ROW($A1)>COUNT(Sheet1!$B$4:$B$104),"",INDEX(Sheet1!$A$4:$D$104,SMALL(INDEX(ROW($A$2:$A$102)*ISNUMBER(Sheet1!$B$4:$B$104),,),COUNTBLANK(Sheet1!$B$4:$B$104)+ROW($A1))-1,COLUMN(A1)))

B4
=IF(ROW($A1)>COUNT(Sheet1!$B$4:$B$104),"",INDEX(Sheet1!$A$4:$D$104,SMALL(INDEX(ROW($A$2:$A$102)*ISNUMBER(Sheet1!$B$4:$B$104),,),COUNTBLANK(Sheet1!$B$4:$B$104)+ROW($A1))-1,COLUMN(B1)))

C4
=IF(ROW($A1)>COUNT(Sheet1!$B$4:$B$104),"",INDEX(Sheet1!$A$4:$D$104,SMALL(INDEX(ROW($A$2:$A$102)*ISNUMBER(Sheet1!$B$4:$B$104),,),COUNTBLANK(Sheet1!$B$4:$B$104)+ROW($A1))-1,COLUMN(D1)))

お礼

何度も大変有難うございます。
上記の三番目の式の上にあるＣ４はＤ４ですよね？
いずれにしても何度もあれこれと行ったのですが上手くいきません。
エラーも表示されないのですが数値も入力されません。
計算が手動になっている訳でもなくちゃんと自動になっています。

回答No.11

sige1701

参考までに、こんな感じでもいいかな
=IF(ROW($A1)>COUNT(Sheet1!$D$2:$D$102),"",INDEX(Sheet1!A:A,SMALL(INDEX((Sheet1!$A$2:$A$102="")*1000+ROW($A$2:$A$102),),ROW(A1)))

作業用列を使用する方がいいのかな
（シート１）
　　　　A　　　　　　　B　　　　　　C　　　　　　D　　　　E
１　　日付　　　　　名前　　　　　出　　　　　入
２　　10/20 　　　　東京　　　　２００　　　　
３　　10/20　　　　　大阪　　　　　　　　　　３００
４ 　10/20　　　　　名古屋　　　４００
５ 　10/21　　　　　福岡　　　　３００　　　

E2=IF(D2="","",ROW())

（シート２）
　　A　 B　　　　　　C　　　　　　D
１　　 日付　　　　　名前　　　　　出　　　　　入
２　　 10/20　　　　　大阪　　　　　　　　　　３００
３ 　 10/22　　　　　京都　　　　　　　　　　９００
４ 　 10/23　　　　　京都　　　　　　　　　　７５０
A列を挿入して
A2=IF(ROW(A1)>COUNT(Sheet1!E:E),"",SMALL(Sheet1!E:E,ROW(A1)))
B2=IF($B2="","",INDEX(Sheet1!A:A,$B2))

お礼

有難うございました。
是非参考にさせて頂きます。

回答No.10

Wendy02

こんばんは。Wendy02です。

>実は実際のファイルは列はそのままなんですが、行がA2ではなくA4からなんです。

それは、Sheet2の方ではないですよね。もし、そうだったら、例えば、私の数式を、そのまま、A4 に貼り付けます。

しかし、あれこれ試されたようですから、どうやら、Sheet1 のほうのようですね。

こんなにも複雑になると、なかなか分かりにくいです。

コツというのではないのですが、複雑なので、こういう配列式には、「配列の原則」というもので、適用したほうが、簡単です。

数式の中に、ROW(*****) というのがあります。

原則的には、1から始まるように出来ているのですが、今回は、イレギュラーで、「2」から始まっています。それは、場所が変わっても、「2」は、「2」なのです。理由は、INDEX 関数のデータ範囲を基準にしているからなのです。この名前のことを、「インクリメント(increment＝増加)」と呼んでいます。名前なんかどうでもよいことですが。

次に、『配列の共通部分参照』と呼ばれているもので、そのROWの中の範囲(つまり長さ)は、いつも、他の範囲と同じでないと、エラーが出てしまう、ということなのです。

例えば、以下は、項目が４行目、データは　５行目 から始まるとすると、長さは、105-5 =100 なので、配列に関わる部分(SMALLの中の式)は、全部、100 の長さでないといけません。200だったら、200に合わせます。

Sheet2 のA列(項目が４行目、データは　５行目から)

=IF(ROW($A1)>COUNT(Sheet1!$B$5:$B$105),"",INDEX(Sheet1!$A$5:$D$105,SMALL(INDEX(ROW($A$2:$A$102)*ISNUMBER(Sheet1!$B$5:$B$105),,),COUNTBLANK(Sheet1!$B$5:$B$105)+ROW($A1))-1,COLUMN(A1)))

私は、理屈で考えたわけではなくて、原則に当てはめただけで、修正することが出来ます。
Sheet1 のデータをドラッグすると、どこが変わるか、105行までないので、数式が連動して動いてくれませんので、「Sheet1!$B$5:$B$100」のままになっています。そうすると、『配列の共通部分参照』の原則が崩れて、エラーがでてしまいます。だから、SMALLの中の配列数式の範囲の長さを合わせてあげさえすれば、直るわけです。
→例：「Sheet1!$B$5:$B$105」

後は、お分かりになるかと思います。上手くいかないようでしたら、全部、数式を書き出します。なお、こういうのは、みんな、最初は、誰かから見よう見まねで教わったことで、私にも最初の時があります。

お礼

何度も丁寧にお答え頂き本当に感謝しております。
Sheet1、Sheet2共に項目が３行目、データが４行目からのスタートです。
列は変わりません。
今、見ながらあてはめたのですがまたエラーが出ました。
どこがおかしいのか悪戦苦闘しております。
しかしどこかがおかしいからこそこうなるのだと自分に言い聞かせながら
確認しています。
再度チャレンジしてみます。

回答No.9

Wendy02

こんばんは。#6のWendy02です。

#6 のお礼の中のご質問は、すでに、rin01さんからのご説明があったので、こちらは割愛いたします。rin01さんのと比較すると、私のは、残念ながらこちらの出来があまり良くないのは、最初から分かっています。（ご質問者から見れば、解が出れば優劣を言うのはヘンに思えるとは思いますが、回答者側からすると、暗黙の了解があります^^;)

私の書いたこのパターンは、ある程度知られた配列数式ですが、私は、こちらの勉強は、1年以上遠ざかったせいで、新しいパターンが登場したようです。変わらないと思っていたけれど、他の方の数式を含め、少しずつ、新しいパターンが増えているようです。

板を汚すようで申し訳ないのですが、一応、責任で、私のも修正しておきます。テータ範囲は、2行目～100行目にしておきます。

Sheet2のA2

=IF(ROW($A1)>COUNT(Sheet1!$B:$B),"",INDEX(Sheet1!$A$2:$D$100,SMALL(INDEX(ROW(Sheet1!$A$2:$A$100)*ISNUMBER(Sheet1!$B$2:$B$100),,),COUNTBLANK(Sheet1!$B$2:$B$100)+ROW($A1))-1,COLUMN(A1)))

B2:
=IF(ROW($A1)>COUNT(Sheet1!$B:$B),"",INDEX(Sheet1!$A$2:$D$100,SMALL(INDEX(ROW(Sheet1!$A$2:$A$100)*ISNUMBER(Sheet1!$B$2:$B$100),,),COUNTBLANK(Sheet1!$B$2:$B$100)+ROW($A1))-1,COLUMN(B1)))

C2
=IF(ROW($A1)>COUNT(Sheet1!$B:$B),"",INDEX(Sheet1!$A$2:$D$100,SMALL(INDEX(ROW(Sheet1!$A$2:$A$100)*ISNUMBER(Sheet1!$B$2:$B$100),,),COUNTBLANK(Sheet1!$B$2:$B$100)+ROW($A1))-1,COLUMN(D1)))

お礼

Wendy02さん
何度も有難うございました。
>板を汚す
だなんてとんでもないです。
ここまで丁寧に自分が書かれた事に対して責任を持ってフォローされて
いるのを見て私自身の生活の参考になりました。
本当にありがとうございました。

しかし、実は実際のファイルは列はそのままなんですが
行がA2ではなくA4からなんです。
で、一部変更して行ったんですが何度やってもエラーが出ます。
再度ご教授して頂けないでしょうか？

回答No.8

rin01

こんにちは～♪
>が、しかしその後の流れで実はシート２の配列が変わりました。

　　　A　　　　　　　B　　　　　　C　　　　　　D
１　　日付　　　　　入　　　　名前　　　　

sheet2の抽出する項目の順序が
変るんですよね～。。。

私の式も、Wendyさんの式も同じですが

最初の場合、A2にアップした式を入れて、右へ下へコピーで

Ａ　　　　Ｂ　　　Ｃ　　Ｄ
日付　　名前　　出　　入

の列順序で、抽出ですから一旦、A2～D2まで式をコピーして
(1)B2の式を数式バーから、コピーしてC2の数式バーへ貼り付け。
(2)D2の式を数式バーから、コピーしてB2の数式バーへ貼り付け。
(3)D列の式をクリアします。

で、
Ａ　　　Ｂ　　　Ｃ
日付　　入　　名前　
の順にデータが、抽出されますので。。。。

ちなみに、私の式の場合は

A2: =IF(COUNT(Sheet1!$D:$D)>=ROW(A1),INDEX(Sheet1!A:A,SMAL(INDEX
(SUBSTITUTE((Sheet1!$D$2:$D$100<>"")*1,0,10^7)*RO($2:$100),),ROW
(A1))),"")

B2: =IF(COUNT(Sheet1!$D:$D)>=ROW(D1),INDEX(Sheet1!D:D,SMAL(INDEX
(SUBSTITUTE((Sheet1!$D$2:$D$100<>"")*1,0,10^7)*RO($2:$100),),ROW
(D1))),"")

C2: =IF(COUNT(Sheet1!$D:$D)>=ROW(B1),INDEX(Sheet1!B:B,SMAL(INDEX
(SUBSTITUTE((Sheet1!$D$2:$D$100<>"")*1,0,10^7)*RO($2:$100),),ROW(B1))),"")

と入れて下にコピーでもいいです。。。

それから
Wendyさんへ
いつもフォローありがとうございます。。。

。。。。Ms.Rin～♪♪

お礼

大変有難うございました。
単純な方法で変更は出来たわけですね。
しかし、実は実際のファイルは列はそのままなんですが
行がA2ではなくA4からなんです。
で、一部変更して行ったんですが何度やってもエラーが出ます。
再度ご教授して頂けないでしょうか？

回答No.7

kiki3567

No.1補足です。マクロの登録方法をご説明します。
　(1)対象のファイルを開き、ツール→マクロ→Microsoft Visual Basic Editor
　(2)Module1(コード)という画面に切り替わるのでここにNo.1で書いたマクロをコピー＆ペーストする
　(3)表示→Microsoft Excelで元の画面に戻る
☆ここからマクロを実行させるボタンの登録です。
　(4)ツール→ユーザー設定
　(5)「コマンド」タブの「分類」の中で「マクロ」を選択
　(6)「分類」の中で「ユーザー設定ボタン」を選択し、そのままドラッグして画面上のアイコンが並んであるエリアの好きな場所に配置する
　(7)今配置したアイコンをクリックすればマクロが実行されます

※マクロのコードとしてはNo.5さんの方がカッコ良いです。目的としている結果は同じと思われますので両方試されてみてはいかがでしょうか。マクロが使えるようになると応用範囲が広がって便利ですよ。

お礼

有難うございました。
お礼が遅くなった事をお詫び致します。
大変参考になりました。

回答No.6

Wendy02

こんにちは。
#2のrin01さんの「この回答へのお礼」のエラーについて考えてみました。

数式の位置の場所が違うのは、良く調べていただくしかありませんが、実際のデータでは、

(Sheet1!$D$2:$D$100<>"")　
ROW($2:$100)

と書かれてある範囲を広げればよいのではないでしょうか？

ちなみに、同じような数式を私も考えてみました。

範囲を、500行まで広げてあります。もし、足らなければ、範囲を広げて・・・、と言いたいところですが、このような配列数式は、基本的には、参照セルが、数式内で、延べ5000個レベルにしておきたいので、あまり広げたりすると、動きが緩慢になってしまいますから、マクロで行ったほうがよいです。

シート2の　A2 から、

=IF(ROW($A1)>COUNT(Sheet1!$D:$D),"",INDEX(Sheet1!$A$2:$D$500,SMALL(INDEX(ROW(Sheet1!$A$2:$A$500)*ISNUMBER(Sheet1!$D$2:$D$500),,),COUNTBLANK(Sheet1!$D$2:$D$500)+ROW($A1))-1,COLUMN(A1)))

必要数の縦行と横の列4つ。後は、書式を整え、「0」は消します。

お礼

有難うございます。
お陰でうまくいきました。
が、しかしその後の流れで実はシート２の配列が変わりました。
　　　A　　　　　　　B　　　　　　C　　　　　　D
１　　日付　　　　　　入　　　　名前　　　　　　　　　　
２　　10/20　　　　３００　　　大阪　　　　　　　　　　
３ 　 10/22　　　　９００　　　京都　　　　　　　　　　
４ 　 10/23　　　　７５０　　　京都　　　　

D列には後に文字を入力するつもりです。
で、教えて頂いた式を参考に入力したのですが
どうもうまくいきません。
どうすれば良いのか教えていただけると大変助かります。

回答No.5

merlionXX

No4のerlionXXです。
ちょっと修正させてください。

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 4 Then Exit Sub
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
ActiveSheet.AutoFilterMode = False
Rows(1).AutoFilter
Rows(1).AutoFilter Field:=4, Criteria1:="<>"
Sheets("Sheet2").Cells(1, 1).CurrentRegion.ClearContents
Range(Cells(1, 1), Cells(1, 1).SpecialCells(xlLastCell)).SpecialCells(xlCellTypeVisible).Copy
Sheets("Sheet2").Cells(1, 1).PasteSpecial
.CutCopyMode = False
ActiveSheet.AutoFilterMode = False
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

お礼

有難うございました。
参考にさせて頂きます。