こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

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

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  □□□

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

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

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

投稿日時 - 2011-05-11 18:09:20

QNo.6730341

困ってます

質問者が選んだベストアンサー

 まず、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セルに入力されている顧客名を、適時書き替えて下さい。

投稿日時 - 2011-05-11 19:47:31

お礼

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

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

投稿日時 - 2011-05-12 17:47:41

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(6)

ANo.6

回答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列目を表示させることになります。

投稿日時 - 2011-05-12 16:39:16

お礼

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

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

投稿日時 - 2011-05-12 17:39:14

ANo.5

エクセルの関数経験が少ないから、>関数で作成したいと思っていますなんていっているのだろうが、
(1)既に出ているようにエクセル関数で抜き出し問題は難しいのだ
(2)顧客a1つ分だけでも難しいのに、顧客b、・・があるのでさらに難しい
(3)さらに顧客a,顧客b・・・を別シートにするのは自動では不可能に近い。(人間がそのシートに別の式を入れないでという意味。式複写を使うような方式ではできないという意味)
ーー
だから普通のユーザーは、操作でやるべきなんだ。
それは、データーフィルターフィルタオプションの設定が使える。
ーー
シートごとに同じような操作が必要で、面倒というなら、
それでさらにVBAで簡略化する方法を考えた。
VBAの経験無いだろうが、質問のテーマだけなら、難しい関数の意味を考えるのとひかくして、そんなに変わりは無いだろうとおもう(どちらも難しいだろうが)。
ーー
データーフィルターフィルタオプションの設定について
まずマクロの記録というのがあって、其れで1回分(1顧客分)のマクロのコードがどうなるか判る。
それを顧客数だけ繰返すコードを付け加え(て実行す)る。
例データ Sheet1 A-C列
地区氏名係数
大阪aa12
大阪bb33
京都cc24
京都dd31
神戸ee21
神戸ff34
大阪gg53
京都hh23
大阪ii11
奈良jj25
大阪kk27
奈良ll13
各地区ごとにシートを分ける。
ーーー
大阪、京都、神戸、奈良の4地区あるのでSheet1以外に、白紙の4シート用意する。
そしてすべてにシートの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
地区
大阪
地区氏名係数
大阪aa12
大阪bb33
大阪gg53
大阪ii11
大阪kk27
Sheet3
地区
京都
地区氏名係数
京都cc24
京都dd31
京都hh23
以下の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:C20
(上記3つの範囲指定は、キー入力も出来るが、すべてマウスで指定すると良い。Sheet1を指定するときはSheet1のシートタブをクリックして範囲を囲む)
OK
操作だけでやる場合は、Sheet2-Sheet5の4つの各シートで操作を繰り返す。

投稿日時 - 2011-05-12 10:38:08

お礼

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

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

投稿日時 - 2011-05-12 15:17:03

ANo.4

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

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

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

=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)))

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

投稿日時 - 2011-05-12 07:44:28

補足

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

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

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

投稿日時 - 2011-05-12 16:01:06

ANo.2

こんばんは!
顧客の数だけ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

大変申し訳ございませんが、この投稿に添付された画像や動画などは、「BIGLOBEなんでも相談室」ではご覧いただくことができません。 OKWAVEよりご覧ください。

マルチメディア機能とは?

投稿日時 - 2011-05-11 18:45:27

お礼

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

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

投稿日時 - 2011-05-12 17:43:31

ANo.1

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

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

投稿日時 - 2011-05-11 18:30:41

お礼

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

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

投稿日時 - 2011-05-12 17:45:26

あなたにオススメの質問