別シートへコピーする関数

質問

エクセルの関数について詳しくないので質問させてください。
下記のような文書を完成させる関数の式を教えていただけますか？

Sheet1の文字列を、顧客ごとに別シートにコピペして、伝票を作成します。
同顧客の2行目以降もコピーする式（sheet2のA2以降に入れる式）がわかりません。

【Sheet1】
　A 　　　B 　　　C 　　　D
1 顧客a 111 　AAA 　○○○
2 顧客a 222　 BBB 　▲▲▲
3 顧客b 333 　CCC 　□□□
4 顧客c 444 　DDD 　●●●
5 顧客c 555 　EEE　　△△△
・・・

【Sheet2】
　A 　　　B 　　　C
1 顧客a
2 111 　AAA 　○○○
3 222 　BBB 　▲▲▲

【Sheet3】
　A 　　　B 　　　C
1 顧客b
2 333 　CCC 　□□□

顧客ごとの件数は毎月変わります。

本当はマクロを組むべきだと思うのですが、マクロの知識はないので、可能であれば関数で作成したいと思っています。

分かりづらい点ありましたら補足いたします。
よろしくお願い致します。

回答No.3

kagakusuki · ベストアンサー

まず、Sheet2のA2セルに次の数式を入力して下さい。

=IF(ROWS($A$2:$A2)>COUNTIF(Sheet1!$A:$A,$A$1),"",INDEX(Sheet1!B:B,SUMPRODUCT(ROW(Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH("゛",Sheet1!$A:$A,-1)))*(Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH("゛",Sheet1!$A:$A,-1))=$A$1)*(COUNTIF(OFFSET(Sheet1!$A$1,,,ROW(Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH("゛",Sheet1!$A:$A,-1)))),$A$1)=ROWS($A$2:$A2)))))

　次に、Sheet2のA2セルをコピーして、Sheet2のB2～C2の範囲に貼り付けて下さい。
　次に、Sheet2のA2～C2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

　後は、Sheet2のA1セルに顧客名を入力すると、その顧客のデータ集計結果が表示されます。
　他の顧客に関しては、Sheet2自体のコピーシートを作り、各シートのA1セルに入力されている顧客名を、適時書き替えて下さい。

お礼

できました！！！
ありがとうございます！

ずっと不便に思っていた書類の作成だったので本当に助かります。

回答No.6

KURUMITO

回答No4です。
例えばシート2のA2セルに入力する式では次のようになっていますね。

=IF(OR($A$1="",COUNTIF(Sheet1!$E:$E,$A$1&ROW(A1))=0),"",INDEX(Sheet1!$A:$D,MATCH($A$1&ROW(A1),Sheet1!$E:$E,0),COLUMN(B1)))

ここではINDEX関数を使ってシート1からのデータを表示させようとしています。
INDEX関数は通常INDEX(範囲、行番号、列番号）の形の引数を入力することになります。シート1で対象となる行番号はA1セルの顧客名と行番号を結合された文字列をシート1のE列から検索して求めています。列番号はシート1の2列目をシート2の1列目に表示させるのですからCOLUMN(B1)と入力しています。単に列番号を求めるためのものですからCOLUMN(B2)と入力しても問題はありません。
シート2のB列ではシート1の3列目すなわちC列を表示させることになるわけで、A2セルに入力した式はB2セルではCOLUMN(C1)となってシート1の3列目を表示させることになります。

お礼

うまくいかないままですが、式の意味は理解できたので、もう少し考えてみます。

ご丁寧に二度もありがとうございます。
新たな関数も覚えられ、とても勉強になりました。

回答No.5

imogasi

エクセルの関数経験が少ないから、＞関数で作成したいと思っていますなんていっているのだろうが、
（１）既に出ているようにエクセル関数で抜き出し問題は難しいのだ
（２）顧客a１つ分だけでも難しいのに、顧客b、・・があるのでさらに難しい
（３）さらに顧客a,顧客b・・・を別シートにするのは自動では不可能に近い。（人間がそのシートに別の式を入れないでという意味。式複写を使うような方式ではできないという意味）
ーー
だから普通のユーザーは、操作でやるべきなんだ。
それは、データーフィルターフィルタオプションの設定が使える。
ーー
シートごとに同じような操作が必要で、面倒というなら、
それでさらにVBAで簡略化する方法を考えた。
VBAの経験無いだろうが、質問のテーマだけなら、難しい関数の意味を考えるのとひかくして、そんなに変わりは無いだろうとおもう（どちらも難しいだろうが）。
ーー
データーフィルターフィルタオプションの設定について
まずマクロの記録というのがあって、其れで１回分（１顧客分）のマクロのコードがどうなるか判る。
それを顧客数だけ繰返すコードを付け加え（て実行す）る。
例データ　Sheet1　A-C列
地区 氏名 係数
大阪 aa 12
大阪 bb 33
京都 cc 24
京都 dd 31
神戸 ee 21
神戸 ff 34
大阪 gg 53
京都 hh 23
大阪 ii 11
奈良 jj 25
大阪 kk 27
奈良 ll 13
各地区ごとにシートを分ける。
ーーー
大阪、京都、神戸、奈良の４地区あるのでSheet1以外に、白紙の４シート用意する。
そしてすべてにシートのA1:A2に
Sheet2　A1:A2
地区
大阪
Sheet3　A1:A2
地区
京都
Sheet4　A1:A2
地区
神戸
Sheet5　A1:A2
地区
奈良
と入れておく。見出しの地区は必須。
この作業はとりあえずは人間が入力するで我慢する。学習が進めばVBAで出来るが。
ーー
ツールーマクローVBE－標準モジュールに
Sub Macro3()
For i = 1 To 4
Sheets(i + 1).Select
Sheets(i + 1).Range("a1").Activate
Sheets("Sheet1").Range("A1:C100").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("A1:A2"), CopyToRange:=Range("A3:C100"), Unique:= _
False
Next i
End Sub
を入れて実行する。
ーー
結果
Sheet2
地区
大阪
地区 氏名 係数
大阪 aa 12
大阪 bb 33
大阪 gg 53
大阪 ii 11
大阪 kk 27
Sheet3
地区
京都
地区 氏名 係数
京都 cc 24
京都 dd 31
京都 hh 23
以下のSheet4,Sheet5は掲載略
ーー
参考
Sheet2で操作して（大阪地区分抜き出し）マクロの記録を取ると
Sub Macro4()
Sheets("Sheet1").Range("A1:C13").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("A1:A2"), CopyToRange:=Range("A3:C19"), Unique:= _
False
End Sub
のようなのが、作られる。上記ではC19をC100に変えているのは実際のデータ行数より余裕を見て指定するため。
実際より多くても大丈夫。
For i = 1 To 4
Sheets(i + 1).Select
Sheets(i + 1).Range("a1").Activate
の部分で地区数（質問の場合は顧客数）シート回分繰返すために入れているコード。
言いたいことはほとんどマクロの記録がそのまま使えるということ。
ーーー
Sheet2においての操作は
データーフィルターフィルタオプションの設定
抽出先　指定した範囲
リスト範囲　Sheet1!A1:C20　（実際は＄付き）
条件範囲　A1:A2
抽出範囲　A3:C２０
（上記３つの範囲指定は、キー入力も出来るが、すべてマウスで指定すると良い。Sheet1を指定するときはSheet1のシートタブをクリックして範囲を囲む）
OK
操作だけでやる場合は、Sheet2-Sheet5の４つの各シートで操作を繰り返す。

お礼

マクロのコードをご丁寧に教えて頂いてありがとうございます。
おっしゃってる通りに作成できましたが、sheet2の形式は決められたものなので、いじることができません。
なので今回のコードだと思うようにいきませんでした。

マクロのが断然便利なので勉強したいと思います。

回答No.4

KURUMITO

分かり易くて、しかもデータの数が多くなっても計算に負担がかからない方法は作業列を使って対応することです。
シート１では１行目には項目名が有るとして２行目から下方にデータが入力されているとします。
E２セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A2="","",A2&COUNTIF(A$2:A2,A2))

その後はシート２を表示させてからから顧客名ごとに用意されたすべてのシートについてシート見出しのところでShiftまたはCtrlキーを押しながらそれらのシート名をクリックして作業グループを作ります。
シート２のA２セルには次の式を入力してC２セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(OR($A$1="",COUNTIF(Sheet1!$E:$E,$A$1&ROW(A1))=0),"",INDEX(Sheet1!$A:$D,MATCH($A$1&ROW(A1),Sheet1!$E:$E,0),COLUMN(B1)))

その後にシート見出しで右クリックして「作業グループの解除」を選択します。
これらの操作を行うことで顧客名ごとに用意されたすべてのシートに式が入力された状態になりますのでそれぞれのシートのA１セルに顧客名を入力するだけでお望みの表が表示されます。

補足

表示されませんでした。
多分、実際の伝票とはセルの場所が違うので、私がうまく変更できていないのだと思います。

お伺いしたいのですが、「COLUMN(B1)」はなんのための式ですか？
素人で本当に申し訳ないのですが、Sheet2のB1は空欄なので不思議に思いました。
列番号を知るための関数だということはわかるのですが。

よろしければ教えてください。

回答No.2

tom04

こんばんは！
顧客の数だけSheetがあるという訳ですよね？

そういうことだとしての一例です。
画面左下にある最初の顧客Sheetを開き、Shiftキーを押しながら最後の顧客Sheet見出しをクリックします。
これですべての顧客Sheetがグループ化されましたので、一つのSheetに数式を入力すればすべてのSheetに同じ数式が入ります。
元データはSheet1にあるとします。

↓の画像のSheet2（顧客a）SheetのA2セルに
=IF(COUNTIF(Sheet1!$A:$A,$A$1)<ROW(A1),"",INDEX(Sheet1!B$1:B$1000,SMALL(IF(Sheet1!$A$1:$A$1000=$A$1,ROW($A$1:$A$1000)),ROW(A1))))

これは配列数式になってしまいますので、この画面からA2セルにコピー＆ペーストする場合は貼り付け後、F2キーを押します。
編集可能になりますので、Shift+Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。
これを列方向と行方向にオートフィルでコピーするとすべてのSheetが画像のような感じになります。

参考になれば良いのですが・・・m(__)m

お礼

実際の画面までつけていただきありがとうございました。
やってみたのですが、実際の形式だと結合セルが含まれるためエラーになってしまいました。

私の説明不足ですね。すみませんでした。

回答No.1

mu2011

一例です。
フィルタオプションの設定は如何でしょうか。（これをマクロ記録すればワン操作も可能です）
参考のＵＲＬを添付しますのでご検討下さい。

参考URL：

http://www.eurus.dti.ne.jp/~yoneyama/Excel/filter3.htm

お礼

フィルタオプションのマクロ記録は使えそうです。
ありがとうございます。

知識不足でうまくいかなかったのですが、今後も役立ちそうなので勉強します。