エクセル関数で別シートへのコピーを行う方法
- エクセルの関数を使用して、Sheet1のデータを顧客ごとに別シートにコピーする方法について教えてください。
- 同じ顧客の2行目以降もコピーするための式（Sheet2のA2以降に入れる式）がわかりません。
- 毎月変わる顧客ごとの件数に対応した関数で、マクロの知識がなくても実現したいです。
- ベストアンサー
別シートへコピーする関数
エクセルの関数について詳しくないので質問させてください。 下記のような文書を完成させる関数の式を教えていただけますか？ 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 □□□ 顧客ごとの件数は毎月変わります。 本当はマクロを組むべきだと思うのですが、マクロの知識はないので、可能であれば関数で作成したいと思っています。 分かりづらい点ありましたら補足いたします。 よろしくお願い致します。
- sunflower_smile
- お礼率87% (7/8)
- オフィス系ソフト
- 回答数6
- ありがとう数5
- みんなの回答 （6）
- 専門家の回答
質問者が選んだベストアンサー
まず、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セルに入力されている顧客名を、適時書き替えて下さい。
その他の回答 (5)
- KURUMITO
- ベストアンサー率42% (1835/4283)
回答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列目を表示させることになります。
お礼
うまくいかないままですが、式の意味は理解できたので、もう少し考えてみます。 ご丁寧に二度もありがとうございます。 新たな関数も覚えられ、とても勉強になりました。
- imogasi
- ベストアンサー率27% (4737/17068)
エクセルの関数経験が少ないから、＞関数で作成したいと思っていますなんていっているのだろうが、 （１）既に出ているようにエクセル関数で抜き出し問題は難しいのだ （２）顧客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の形式は決められたものなので、いじることができません。 なので今回のコードだと思うようにいきませんでした。 マクロのが断然便利なので勉強したいと思います。
- KURUMITO
- ベストアンサー率42% (1835/4283)
分かり易くて、しかもデータの数が多くなっても計算に負担がかからない方法は作業列を使って対応することです。 シート１では１行目には項目名が有るとして２行目から下方にデータが入力されているとします。 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は空欄なので不思議に思いました。 列番号を知るための関数だということはわかるのですが。 よろしければ教えてください。
- tom04
- ベストアンサー率49% (2537/5117)
こんばんは！ 顧客の数だけ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
お礼
実際の画面までつけていただきありがとうございました。 やってみたのですが、実際の形式だと結合セルが含まれるためエラーになってしまいました。 私の説明不足ですね。すみませんでした。
- mu2011
- ベストアンサー率38% (1910/4994)
一例です。 フィルタオプションの設定は如何でしょうか。（これをマクロ記録すればワン操作も可能です） 参考のＵＲＬを添付しますのでご検討下さい。
お礼
フィルタオプションのマクロ記録は使えそうです。 ありがとうございます。 知識不足でうまくいかなかったのですが、今後も役立ちそうなので勉強します。
関連するQ&A
- Ｅｘｃｅｌシートの統合するマクロなどがありましたらお教えください。
各支店（約200）からExcelシートで送信されてくる報告内容を別のシートに一気にまとめるマクロ等はないでしょうか？ご存知の方よろしくお願いします。 （例） Ａ支店 項目 aaa bbb ccc ddd eee 10 20 30 0 0 8 8 0 0 0 以下約300件 Ｂ支店 項目 aaa bbb ccc ddd eee 11 22 33 0 0 9 9 0 0 0 以下約300件 別シートに 項目 aaa bbb ccc ddd eee 10 20 30 0 0 8 8 0 0 0 11 22 33 0 0 9 9 0 0 0 以下省略 よろしくお願いします
- ベストアンサー
- オフィス系ソフト
- EXCELのVBAで空白列を削除して左づめにできますか？
いつも参考にしています。まだ、マクロの記録にチョット手を加えて自動化？している程度の腕前ですが？ 仕事の都合で下記の問題を解決しなくてはならなくなり、皆様のお知恵をお借りしたいと思い質問させて頂きます。 エクセルのワークシートに空白のセルがランダムに入っているデータがあります。これを、ある列まで（例ではD列までの左の空白セルをなくして左づめのデータとしたいのです。 （例） | A | B | C | D | E | 1|AAA|BBB|CCC|DDD|EEE| 2| |BBB|CCC|DDD|EEE| 3|AAA| |CCC|DDD|EEE| 4|AAA| |CCC| |EEE| 5| | | |DDD|EEE| ↓ | A | B | C | D | E | 1|AAA|BBB|CCC|DDD|EEE| 2|BBB|CCC|DDD| |EEE| 3|AAA|CCC|DDD| |EEE| 4|AAA|CCC| | |EEE| 5|DDD| | | |EEE| ここで、E列以降は詰めないでほしいのです。 できれば、VBAで実現したいのですが、どのような考えで、どのようなコード？を書けばよいのか教えてください。 質問の仕方も用途を得ないで、失礼な質問になっていると思いますが、よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- Excelの関数に詳しい方、お願いします。
わかりづらい説明かもしれませんが、よろしくお願いします。 例として、 AAA*BBB*CCC*DDD*EEE*FFF AAA*BBB*CCC*DDD*EEE*FFF AAA*BBB*CCC*DDD*EEE*FFF AAA*BBB*CCC*DDD*EEE*FFF AAA*BBB*CCC*DDD*EEE*FFF ・ ・ ・ といった文字列がエクセルの先頭列Ａ１～Ａ100までずらりと並んでいるとします。 各アルファベットには任意の数字（日付など）が入るとして、この並んだ百件のデータからＣＣＣの部分の最大値を表示するための適切な関数があれば教えていただけないでしょうか？ 現在少々急いでいるため言葉足らずで申し訳ありませんが、よろしくお願いいたします.
- ベストアンサー
- オフィス系ソフト
- Sheet2にある情報をSheet1のA1に１列で
マクロを作りたいのですが、以下のようなものをどうすればできるかで詰まっています。 Sheet2にある情報をSheet1のA1に１列（A列B列C列の順）で並ぶようにしたいです。 Sheet2の内容は変わることがあるので、データがどの行まで入っているかはマクロを走らせるときにチェックしないとわからないかと思います。 [Sheet1] 111 222 333 aa bb aaa bbb ccc ddd eee -------------------------- [Sheet2] A B C 1 111 aa aaa 2 222 bb bbb 3 333 ccc 4 ddd 5 eee 素人な質問かもしれないですが、調べてもわからなかったので、教えていただけると幸いです。
- ベストアンサー
- オフィス系ソフト
- 別シートの値を検索して一致したものを合計する
excelvba初心者です。 お手数おかけします。 難問なのかどうかもよく分からず。。困っています。 シート１ シート２ シート３ AAA 100 AAA ５０ BBB 20 BBB 200 CCC 150 CCC 30 DDD 300 QQQ 250 DDD 10 EEE 400 WWW 350 RRR 40 上記の値を決められた順番に別シートに集計したい(その時に、値を1/10にしたい） シート４ AAA 150 BBB 220 CCC 180 DDD 310 EEE 400 FFF 0 GGG 0 www 350 findでAAAを検索し見つかった値を ifにて条件分岐させるものをつくったものの プロシャーシーが大きすぎと言われてできません。 どなたか教えていただけないでしょうか。
- ベストアンサー
- 洗濯機
- エクセルのマクロについて教えて下さい。
エクセルのマクロについて教えて下さい。 Sub Ref() Dim ax As String Dim num As Integer, i As Integer Dim arr As Variant Dim tex As String Range("A1").Select ax = ActiveCell.Formula arr = Split(ax, ",") For i = 0 To UBound(arr) num = i + 1 Cells(num, 1).Value = arr(i) Next i For i = 1 To 10 ActiveCell.Offset(, 1).Select tex = ActiveCell.Formula Selection.Resize(num, 1).Select Selection.Formula = tex Selection.Resize(1, 1).Select Next i End Sub このマクロを10行ほどまで対応させたいです。 例として2行の表ですが、 A B C D E F １ C100,C101,C102,C103 aaa bbb ccc ddd eee ２ C104,C105,C106,C107 とうい表を、 A B C D E F １ C100 aaa bbb ccc ddd eee ２ C101 aaa bbb ccc ddd eee ３ C102 aaa bbb ccc ddd eee ４ C103 aaa bbb ccc ddd eee ５ C104 aaa bbb ccc ddd eee ６ C105 aaa bbb ccc ddd eee ７ C106 aaa bbb ccc ddd eee ８ C107 aaa bbb ccc ddd eee という表にしたいです。 結合してから展開しようと考えたのですが １列目の文字列の最後にカンマが無い場合、ある場合がありまして、 対応する事が出来ませんでした。 マクロ初心者なので教えてください。 よろしくお願いします。
- ベストアンサー
- Windows XP
- ■マクロを使い別のワークブック＆シートを参照して表示させる
■マクロを使い別のワークブック＆シートを参照して表示させる AとBのワークブックがあり、集計をしてワークブックBシートaに（ ） の中身を入れたい場合マクロで記述するにはどうしたらいいでしょうか ？ ワークブックA（出荷部門報告） No. 商品番号 注文数 出荷完了（1：完了、０：未完） 1 AAA 50 1 2 BBB 30 0 3 CCC 10 1 4 DDD 20 0 ワークブックB シートa（管理） 未出荷商品番号 残必要数 必要日数 （BBB） （15） （1) （DDD) (19) （19) シートb(生産部門報告） 商品番号 生産完了 BBB 10 BBB 5 CCC 10 DDD 1 シートc（調達部門報告） 商品番号 生産必要日数/1 AAA 25 BBB 30 CCC 3 DDD 1 ＜ワークブックBシートaに入るのは出荷未完（０）のみ＞ ＜シートｂ、ｃは追加（変更）されていきます＞ 質問で意味が分からない部分がありましたら、追加いたしますのでよろ しくお願い致します。
- ベストアンサー
- その他（プログラミング・開発）
- Excelで同一のデータを抽出して横並びに
A B C D 商品コード 注文数 商品コード 注文数 aaa 15 ccc 4 bbb 12 ddd 6 ccc 10 aaa 22 ddd 6 eee 10 eee 5 bbb 7 fff 8 kkk 9 kkk 7 lll 4 上記のようなデータを 以下のように同じ商品コードを横並びに表示させたいのですが どういったcountifとかで試したのですが上手くいかず、どのような関数を使えば可能でしょうか？ A B C D 商品コード 注文数 商品コード 注文数 aaa 15 aaa 22 bbb 12 bbb 7 ccc 10 ccc 4 ddd 6 ddd 6 eee 5 eee 10 fff 8 kkk 7 kkk 9 lll 4 宜しくお願い致します。
- ベストアンサー
- Excel（エクセル）
- エクセルのリスト表からマトリクス表に丸いを付けたい
初めて質問させていただきます。 Excel2016を使用しています。 リスト表（A列は重複なし、B列は重複あり） | A | B | 1 | AAA | 111 | 2 | BBB | 222 | 3 | BBB | 444 | 4 | CCC | 222 | 5 | CCC | 333 | 6 | DDD | 333 | 7 | EEE | 333 | 8 | EEE | 444 | マトリクス表（1行目とA列の値は、既に埋め込まれているものです） | A | B | C | D | E | 1 | | 111 | 222 | 333 | 444 | 2 | AAA | ○ | | | | 3 | BBB | | ○ | | ○ | 4 | CCC | | ○ | ○ | | 5 | DDD | | | ○ | | 6 | EEE | | | ○ | ○ | VLOOKUP、INDEX、MATCHなどの関数で考えてみましたが、 正解が見えてきませんでした。 何かよい方法はありますでしょうか。 ぜひよろしくお願いします。
- ベストアンサー
- Excel（エクセル）
- 複数のシートから抽出
検索値シート1【B】111で行い、検索範囲をシート2～4の【B】列全てを検索して一個だけ検索できたら【A】列AAAをシート1の【C】に検索結果を表示させます。シート2～4全て内容は違います。 シート1【B】列500以上全て検索値とします。関数でやり方を探しております。お分かりの方がいらっしゃれば教えてください。 シート2～4 【A】 【B】 AAA ・ 111 BBB ・ 222 CCC ・ 333 DDD ・ 444 EEE ・ 555 シート1 【A】 【B】 【C】 111 222 333
- 締切済み
- その他(業務ソフトウェア)
お礼
できました！！！ ありがとうございます！ ずっと不便に思っていた書類の作成だったので本当に助かります。