EXCELで連絡網

シート1には名簿のデータが入っています。
シート2に連絡網の表があって、そこにデータを入れて行きたいと思います。

連絡網の一番上は会長、次段は役員で、それは別のところから持ってきます。
ここまではできています。
3段目以降に一般の人の氏名と電話番号を入れたいのですが、班毎に人数が
違います。40人いる班は、左の列から順に10人ずつわりふり、10人しかいない班は
画像のように列1、2は3人ずつ、列3,4は2人ずつ、という風に、人数を均等に割り振り
たいのですが、関数で出来ますでしょうか？
空欄の表が残るのは構いません。

よろしくお願い致します。

No.5 ベストアンサー 回答者： CoalTar 回答日時： 2010/12/24 07:46

作業列としてA列と16,17行を使います

A11セル 希望とする班番号
A13セル 班の人数
=COUNTIF(Sheet1!B:B,A11)
A16セル 班の先頭の人の行番号
=MATCH($A$11,Sheet1!$B:$B,0)

A18セル 1
A18:A20セルを選択して下へオートフィル、連番となる

B16セル 1
B16:G16セルを選択して下へオートフィル、連番となる

B17セル 記載される人数
=INT($A$13/4)+(MOD($A$13,4)>=1)
以下同様
H17セル =INT($A$13/4)+(MOD($A$13,4)>=2)
N17セル =INT($A$13/4)+(MOD($A$13,4)=3)
T17セル =INT($A$13/4)

B18セル 氏名
=IF($A18>B$17,"",
VLOOKUP($A18+SUM($B$17:B$17,-B$17),
INDEX(Sheet1!$C:$C,$A$16):INDEX(Sheet1!$E:$E,$A$16+$A$13),2,FALSE))
B19セル 電話
=IF(B18="","",
VLOOKUP($A18+SUM($B$17:B$17,-B$17),
INDEX(Sheet1!$C:$C,$A$16):INDEX(Sheet1!$E:$E,$A$16+$A$13),3,FALSE))

B18:B19セルを選択して コピー→それぞれの記入セルに貼り付け。

この回答へのお礼

丁寧に教えていただき、どうもありがとうございました。
これから良く見て、実際に試して見たいと思います。
大変たすかりました。どうもありがとうございました。

お礼日時：2010/12/27 13:20

No.6 回答者： CoalTar 回答日時： 2010/12/24 08:03

＃5です。

先ほどの回答はかなりまどろっこしいことをしている上
制約条件があったので、改良案です。

B18セル 氏名
=IF($A18>B$17,"",INDEX(Sheet1!$D:$D,SUM($A18-1,$A$16,$B$17:B$17,-B$17)))
B19セル 電話
=IF($A18>B$17,"",INDEX(Sheet1!$E:$E,SUM($A18-1,$A$16,$B$17:B$17,-B$17)))
B18:B19セルを選択して コピー→それぞれの記入セルに貼り付け。


# OFFSET関数は揮発性(計算と関係ないセルを入力/削除しても再計算する)があるので
# 極力使わないほうが良いです。

No.4 回答者： shinkami 回答日時： 2010/12/23 22:45

役員毎の開始行、人数、必要ユニット数、開始ユニット位置の表を作成して

これを参照してマクロを作成します。

※10名分を1ユニットとします。
※10名未満の役員は1ユニットでよいのでは
※名簿と連絡網は同一のsheet上に作成し、印刷時は印刷範囲を設定します。

※以下は1役員の連絡網作成のマクロです。

Sub 連絡網作成()
Dim I, N, M
Columns("G:I").Select
Selection.ClearContents
Selection.NumberFormatLocal = "G/標準"
N = Range("E3")
M = Round(N / 2 + 0.1, 0)
For I = 1 To M
Range("G" & 3 * I) = Range("C" & I + 2)
Range("G" & 3 * I + 1) = Range("D" & I + 2)
Next I
For I = M + 1 To N
Range("I" & 3 * (I - M)) = Range("C" & I + 2)
Range("I" & 3 * (I - M) + 1) = Range("D" & I + 2)
Next I
Range("A1").Select
End Sub
添付と見比べて位置関係をチェックしてください。

この回答へのお礼

丁寧に教えていただきまして、どうもありがとうございました。
マクロは使ったことがないので、これからよく見て、実際におしえていただいた通りにやってみて、勉強したいと思います。
どうもありがとうございました。

お礼日時：2010/12/27 13:18

No.3 回答者： nishi6 回答日時： 2010/12/23 13:15

久しぶりにのぞいてみました。

今は画像が使えるんですね。使い方が分かりません。。。

データは削除などせず、出力の算式は２つ（実質1つか）、設定で２つにしました。分かりやすくするためにセル範囲に範囲名を付けています。


【シート１での設定です】
まず、データはシート1にあり、地区、班ごとの処理の都度、データの削除はしません。地区も複数あり、地区、班、番号で昇順にソートされているとします。出力する地区と班を入力することになります。

処理を単純にするためにセル範囲に範囲名を付けます。

データ内のセルを１つ選び、Ctrl+Shift+*でデータをすべて選択します。（*はアスタリスク）
この状態で、メニューから挿入＞名前＞作成とし、上端行だけにチェックしOK。
これで地区、班、番号、氏名、電話番号というセル範囲が登録されました。確認は名前ボックスで範囲名を確認して下さい。


【シート２での設定です】
今、セル範囲A18:S46が出力用に使われているはずです。計算を楽にするためにセルに数値を入力します。以下、入力するセルは例です。なにか使ってあれば別セルにしてください。（既に使ってあればそちらを動かすのが賢明です。多分）

A1:=0、G1:=1、M1:=2、S1:=3　　横の座標になります。
U18:=0、U21:=1、U24:=2、・・・、U45：＝9　　縦の座標です。３行おきになっています。

出力する地区、班を入力するセルを設定します。
U7:=地区、U8:=班、U9:=最初、U10:=人数　と入力します。
U7:V10を選択して、挿入＞名前＞作成とし、左端列だけチェックしOK。

式を入力します。
　「最初」は指定した地区、班の先頭位置の計算。
　　　セルV9:=SUMPRODUCT((地区<$V$7)+(地区=$V$7)*(班<$V$8))+1
　「人数」は指定した地区、班の人数の計算。
　　　セルV10:=SUMPRODUCT((地区=$V$7)*(班=$V$8))

A18:=IF($U18*4+A$1<人数,INDEX(氏名,最初+$U18+A$1*INT(人数/4)+MIN(MOD(人数,4),A$1)),"")
A19:=IF($U18*4+A$1<人数,INDEX(電話番号,最初+$U18+A$1*INT(人数/4)+MIN(MOD(人数,4),A$1)),"")
とします。設定と位置が合っていれば、上式をコピーすればうまくいくでしょう。

この２つのセルの算式をコピーし、他の出力セルに貼り付けてください。

後は、出力したい地区と班を U7、U8 に入力してください。これだけで連絡網ができているはずです。

当方、Excel2000（古い）で確認しました。

この回答へのお礼

とても丁寧にご指導いただき、どうもありがとうございました。
座標を使った方法は初めてなので、大変勉強になりました。
これからじっくり良く見て考えたいと思います。
どうもありがとうございました。

お礼日時：2010/12/27 13:15

No.2 回答者： MackyNo1 回答日時： 2010/12/22 18:27

極めて複雑な数式を入れ子にすれば、1つのセルで表示することも可能ですが、以下のように1行補助行を設けて、このセルに表示データ数を表示させるのが良いと思います。

A1セルに班の番号が入力されている場合、以下の式にそれぞれ数式を入力してしてください。

A17セル
=ROUNDUP(COUNTIF(Sheet1!$B:$B,$A$1)/4,0)

G17セル=ROUNDUP((COUNTIF(Sheet1!$B:$B,$A$1)-SUM($A$17:F17))/(5-((COLUMN(G17)-1)/6+1)),0)

このG17:L17セルを選択して右方向にオートフィルコピー。

A18セル
=IF(A$17<(ROW(A1)-1)/3+1,"",OFFSET(Sheet1!$D$1,MATCH($A$1,Sheet1!B:B,0)+(ROW(A1)-1)/3-1,0))

A19セル
=IF(A18="","",VLOOKUP(A18,Sheet1!$D:$E,2,0))

A18:A20を選択して下方向にオートフィルコピー

G18セル
=IF(G$17<(ROW(A1)-1)/3+1,"",OFFSET(Sheet1!$D$1,MATCH($A$1,Sheet1!$B:$B,0)+(ROW(A1)-1)/3-1+SUM($A$17:F$17),0))

G19セル
=IF(G18="","",VLOOKUP(G18,Sheet1!$D:$E,2,0))

G18:L20を選択して、下方向および右方向にオートフィルコピー。

上記の数式を設定して、17行目を非表示にしてA1セルに班番号を入力すると、ご希望のレイアウトの連絡表が作成できます。

連絡表にすでに罫線などが設定してある場合は、右クリックでオートフィルして「書式なし」を選択してください。

この回答へのお礼

大変丁寧にご説明いただき、どうもありがとうございました。
これからじっくり良く見て勉強させていただきます。
右クリックでオートフィルして「書式なし」とできるのを、初めて知りました！
これからいっぱい使いそうです。
どうもありがとうございました。

お礼日時：2010/12/27 13:13

No.1 回答者： nattocurry 回答日時： 2010/12/22 15:10

レイアウトが不明瞭なので、ちょっと答えにくいですねぇ。

連絡網の3段目以降のそれぞれのセルのアドレスは？

１　４　７　９
２　５　８　１０
３　６
ではなく、
１　２　３　４
５　６　７　８
９　１０
としたほうが少し簡単になると思いますが、それではダメですか？

この回答へのお礼

お目にとめていただいて、どうもありがとうございます。

セルのアドレスは、

氏名A18G18M18S18
電話A19G19M19S19

氏名A21G21M21S21
電話A22G22M22S22

.
.
.

という感じで10段です。
番号順に流したいので、

1479
25810
36


の順に出したいのです。
シート1のデータは、自分の班だけ残して削除してしまって構わないです。
ただ、連絡網は勝手に出来上がってほしいのです。
A列の最大値+1がG列に入るようにしたいのですが、無理でしょうか？

お礼日時：2010/12/22 16:06