エクセル関数

質問

Win／ＸＰ　エクセル2003使用です。
エクセルの関数についてお聞きしたいです。

2つのシートがあります。
1つは、顧客の一覧が、毎月集計されているシート（シート(1)）です。

　　会社　　担当者　
１　Ａ社　　□君　
２　Ｂ社　　□君　
３　Ｃ社　　□君
４　Ｄ社　　▲君
５　Ｅ社　　▲君
６　Ｆ社　　〇君
７　Ｇ社　　〇君

毎月、会社の数も、担当者の数も変わります。

担当者の列は担当者ごとに
並べ替え機能で、並べ替えます。

もう一枚のシート（シート(2)）に、
担当者ごとの担当会社一覧表を作成します。

１　□君　　　　　　　　
２　　　　　Ａ社
３　　　　　Ｂ社
４　　　　　Ｃ社
５　　　　　空白
（省略）
11　▲君
12　　　　　Ｄ社
13　　　　　Ｅ社
14　　　　　空白
15　　　　　空白
（省略）
21　〇君　　
22　　　　　Ｆ社
23　　　　　Ｇ社
24　　　　　空白
25　　　　　空白

シート(2)を作成するのが目的です。

現在、この作業をシート(1)からシート(2)へ
毎月、会社名をコピー＆ペーストして作成しています。

この作業を、関数を組んで自動的に
入力できるようにしたいと思っています。

シート(1)は、500行くらいになります。
シート(2)の担当者ごとの行は、10行（9社の固定）で、
今回はそれ以上増えない想定でＯＫです。

担当の会社が増えると、
シート(2)の空白のセルに自動的に新しい担当企業が入力され、
担当の会社が減ると自動的に空白になるようにしたいのです。
（会社の順番などは問いません。毎月洗い替えのイメージです）

何卒、よろしくお願いします。

回答No.4

kagakusuki · ベストアンサー

今仮に、シート(1)において、会社名が入力されている列をA列、担当者名が入力されている列をB列、シート(2)において、担当者名が入力されている列をA列、会社名が入力されている列をB列であるものとします。
　又、御質問文では、シート(2)の1行目に「□君」と入力されていますが、シート(2)のA1セルには「担当者」、シート(2)のB1セルには「会社」と入力しておいた方が解りやすいと思いますので、シート(2)において各担当者名はA2セル、A12セル、A22セル、A32セル・・・・と言う具合に、A2セルから始まって、10行毎に入力されているものとします。
　まず、適当な列（例えば、Sheet(3)のA列）の

1行目に　　担当者
2行目に　　□君
3行目に　　▲君
4行目に　　〇君

という具合に入力して、担当者名のリストを作成して下さい。
　次に、シート(2)の

A1セルに　　担当者
B1セルに　　会社

と入力して下さい。
　次に、シート(2)のA2セルに次の数式を入力して下さい。

=IF(AND(MOD((ROW()-2),10)=0,INT((ROW()-2)/10)<=COUNTIF(Sheet3!$A$2:$A$99,"><")),INDEX(Sheet3!$A$2:$A$99,INT((ROW()-2)/10)+1),"")

　次に、シート(2)のB2セルに次の数式を入力して下さい。

=IF(OR(INDEX($A:$A,FLOOR(ROW()-2,10)+2)="",ROW()=FLOOR(ROW()-2,10)+2,MOD((ROW()-2),10)>COUNTIF(シート(1)!$B:$B,INDEX($A:$A,FLOOR(ROW()-2,10)+2))),"",INDEX(シート(1)!$A:$A,SUMPRODUCT(ROW(シート(1)!$A$1:$A$999)*(シート(1)!$B$1:$B$999=INDEX($A:$A,FLOOR(ROW()-2,10)+2))*(COUNTIF(OFFSET(シート(1)!$B$1,,,ROW(シート(1)!$A$1:$A$999)),INDEX($A:$A,FLOOR(ROW()-2,10)+2))=MOD((ROW()-2),10)))))

　そして、シート(2)のA2～B2の範囲をコピーして、同じ列の3行目から「担当者数×10＋1」行目にかけての範囲に(オーバーしても構いません)、貼り付けて下さい。

　以上で完了です。

　尚、シート(2)のA2セルの数式は、担当者数が98人までの場合に対応しています。
　もし、担当者数が98人以上になる場合には、数式中の99と記述されている箇所を、適時、より大きな数に変更して下さい。
　又、シート(2)のB2セルの数式は、シート(1)の表の行数（1行目も含む）が999行以下の場合に対応しています。
　もし、シート(1)の表の行数が999行以上になる場合には、数式中の999と記述されている箇所を、適時、より大きな数に変更して下さい。

お礼

ずいぶんと前の質問のお礼となってしまいましたが、無事、完成することができました。本当に助かりました。　今後ともよろしくお願いします。

回答No.3

mu2011

一例です。
恐縮ですが、シート(2)の表示形式をＡ列を担当者として右列に会社名を抽出していますが如何でしょうか。
（１）Sheet2を選択→データ→フィルタ→フィルタオプションの設定、「指定した範囲」を選択、リスト範囲欄にSheet1!$B:$B、抽出範囲欄にA1、「重複するレコードは無視する」を選択→ＯＫ

（２）Sheet2のB2に以下の数式を入力、入力完了時にshift＋ctrl＋enterキーを同時押下し、縦横に必要分コピー
=IF(COUNTIF(Sheet1!$B:$B,$A2)>=COLUMN(A1),INDEX(Sheet1!$A:$A,SMALL(IF(Sheet1!$B$1:$B$500=$A2,ROW(Sheet1!$A$1:$A$500),9999),COLUMN(A1))),"")

因みに並び替えの操作によるグループ化は不要と思いますので省略できます。

お礼

ずいぶんと前の質問のお礼となってしまいましたが、無事、完成することができました。本当に助かりました。　今後ともよろしくお願いします。

補足

みなさま、早速にご回答いただきありがとうございました。

どの方法が良いか調べさせていただきたいと思いますので
少し時間をください。

今後ともよろしくお願いします。

回答No.2

KURUMITO

シート1にお示しの表があるとして1行目は項目名で2行目から下方にデータがあるとします。
Ｃ列は作業列でＣ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A2="","",IF(COUNTIF(B$2:B2,B2)=1,CEILING(MAX(C$1:C1),10)+2,MAX(C$1:C1)+1))

シート2にお求めの表を表示させるとしてＡ列には担当者名を2行目から下方に、Ｂ列には会社名を表示させることにします。
Ａ2セルには次の式を入力してＢ2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(AND(COLUMN(A1)=1,MOD(ROW(A1),10)=1,ROW(A1)<MAX(Sheet1!$C:$C)),INDEX(Sheet1!$B:$B,MATCH(ROW(A1)+2,Sheet1!$C:$C,0)),IF(OR(COLUMN(A1)=1,COLUMN(A1)>2,ROW(A1)>MAX(Sheet1!$C:$C),COUNTIF(Sheet1!$C:$C,ROW(A1))=0),"",INDEX(Sheet1!$A:$A,MATCH(ROW(A1),Sheet1!$C:$C,0))))

お礼

ずいぶんと前の質問のお礼となってしまいましたが、無事、完成することができました。本当に助かりました。　今後ともよろしくお願いします。

補足

みなさま、早速にご回答いただきありがとうございました。

どの方法が良いか調べさせていただきたいと思いますので
少し時間をください。

今後ともよろしくお願いします。

回答No.1

kool_noah

やりたいことってこういうことですかね？

http://allabout.co.jp/gm/gc/297817/

お礼

ずいぶんと前の質問のお礼となってしまいましたが、無事、完成することができました。本当に助かりました。　今後ともよろしくお願いします。

補足

みなさま、早速にご回答いただきありがとうございました。

どの方法が良いか調べさせていただきたいと思いますので
少し時間をください。

今後ともよろしくお願いします。