[[20170316093727]]　『2つのシートを1つにまとめたい』（ゆずひこ）　ページの最後に飛ぶ

『2つのシートを1つにまとめたい』（ゆずひこ）

はじめまして。
わからない点があったので質問させてください。

シート1　

　　　名前　　ログイン名
1行目　Aさん　　　user1
2行目　Bさん　　　user2
3行目　Cさん　　　user3

シート2

　　　　ログイン名　　所属部署
1行目　　user1　　　　営業部
2行目　　user1　　　　横浜事業所
3行目　　user2　　　　総務G
4行目　　user2　　　　人事G
5行目　　user2　　　　　本社
6行目　　user3　　　　管理部
7行目　　user3　　　　大阪事業所

************************************************************

このような2つのシートがあるとき、以下のようにログイン名をキーに1つのシートをまとめたい場合はどのような関数を使用すればよろしいのでしょうか。

シート1

　　　名前　　　ログイン名　　所属部署　　　・・・　　　　　・・・
1行目　Aさん　　　user1　　　　営業部　　　横浜事業所
2行目　Bさん　　　user2　　　　総務G　　　　人事G　　　　　本社
3行目　Cさん　　　user3　　　　管理部　　　大阪事業所

ご回答の程、よろしくお願い致します。

＜使用 Excel：Excel2016mac、使用 OS：MacOSX ＞

 確認だけ。

 1）Sheet2で、同じログイン名は連続してかたまってますが、実際の表もそうなっているのか？（1件の例外もなく）
 　 同じログイン名が離れて出てくることはあり得ないのか、ということです。

 2）Sheet2は全部で何行ぐらいの表なのか？

 3）ログイン名は実際何種類あるのか？（例では3種類）

 ひとまず以上です。
（笑） 2017/03/16(木) 12:47

コメントありがとうございます。

まず1つめのご質問に関しては、同じログイン名は連続でかたまっています。
同じログイン名で離れているということは、ありません。

2つ目のご質問に関しては、500行程です。

3つめのご質問に関しては、ログイン名は300種類ほどあります。

ご確認お願い致します。
（ゆずひこ） 2017/03/16(木) 15:20

 どちらのシートもA列からで、1行目見出し、2行目からデータだとして。

 ■同じログイン名は、例外なく全部かたまっている場合

 C2 =IF(COUNTIF(Sheet2!$A:$A,$B2)<COLUMN(A1),"",INDEX(Sheet2!$B:$B,MATCH($B2,Sheet2!$A:$A,0)+COLUMN(A1)-1))

 右と下にコピー

 COLUMN(A1) が2か所ありますが、A1セルの値とは関係ないので変更しないように。

 Sheet2で、同じログイン名が離れて出てきてもエラーにはなりません。
 間違った所属部署名が表示されます。

 一応･･･
 ■同じログイン名が離れて出てくる可能性がある場合

 C2 =IFERROR(INDEX(Sheet2!$B:$B,SMALL(INDEX((Sheet2!$A$2:$A$700<>$B2)*10^9+ROW($A$2:$A$700),0),COLUMN(A1))),"")

 これも COLUMN(A1) を変更しないように。

 基本的な数式とは言えない、ということと、
 データ量が多い場合、ひとつ目の数式より計算速度の面で劣ると思います。

 参考まで。
（笑） 2017/03/16(木) 16:41

 ＞データ量が多い場合、ひとつ目の数式より計算速度の面で劣ると思います。

 あまり変わらないかもしれない･･･

 ひとつ目の式の場合、
 Sheet1のC列に件数、D列以降に所属部署を表示でもよければ、

 C2 =COUNTIF(Sheet2!A:A,B2)　　下にコピー

 D2 =IF($C2<COLUMN(A1),"",INDEX(Sheet2!$B:$B,MATCH($B2,Sheet2!$A:$A,0)+COLUMN(A1)-1))

 右と下にコピー

 これだったらふたつ目よりは軽くなると思います。
 参考まで。
（笑） 2017/03/16(木) 17:23

回答いただきありがとうございます。

なんとか出来ました。

C2 =COUNTIF(Sheet2!A:A,B2)　こちらは理解できたのですが

D2 =IF($C2<COLUMN(A1),"",INDEX(Sheet2!$B:$B,MATCH($B2,Sheet2!$A:$A,0)+COLUMN(A1)-1)) こちらの式に関して解説していただけないでしょうか。

理解力がなく大変申し訳ございませんが、ご回答いただけますと幸いです。
何卒よろしくお願い致します。
（ゆずひこ） 2017/03/16(木) 17:56

 どこまで理解できて、何がわからないんですかね？
 それを書いてもらわないと説明もとりとめのないものになってしまいますよ。

 ▼ポイントはCOLUMN関数。
 この関数は列番号を返します。COLUMN(A1) はA列の列番号「1」です。
 =COLUMN(A1)　という式を右方向へコピーすると、=COLUMN(B1)、=COLUMN(C1)、=COLUMN(D1) ･･･と変化し、
 値も2、3、4、･･･に変わる。

 これを踏まえて最初に提示された7件のサンプルデータで説明すると（1行目見出しだとして）
 Sheet1の user2（3行目）の場合

 MATCH($B3,Sheet2!$A:$A,0) は、Sheet2のA列を上から見て「user2」が最初に入力されているのは何行目か、ということ。
 この場合「4」になる（user1なら「2」、user3なら「7」）

 ■C3セル
 =COUNTIF(Sheet2!A:A,B3)　は「3」

 ■D3セル
 =IF($C3<COLUMN(A2),"",INDEX(Sheet2!$B:$B,MATCH($B3,Sheet2!$A:$A,0)+COLUMN(A2)-1)) 
         ~~~~~~~~~~                                                 ~~~~~~~~~
 COLUMN(A2) は「1」なので、
 MATCH($B3,Sheet2!$A:$A,0)+COLUMN(A2)-1　→　4＋1－1　→　「4」
 Sheet2のB4セルの値が返り「総務G」が表示される。

 ■E3セル
 =IF($C3<COLUMN(B2),"",INDEX(Sheet2!$B:$B,MATCH($B3,Sheet2!$A:$A,0)+COLUMN(B2)-1))
         ~~~~~~~~~~                                                 ~~~~~~~~~
 MATCH($B3,Sheet2!$A:$A,0)+COLUMN(B2)-1　→　4＋2－1　→　「5」
 Sheet2のB5セルの値が返り「人事G」が表示される。

 ■F3セル
 =IF($C3<COLUMN(C2),"",INDEX(Sheet2!$B:$B,MATCH($B3,Sheet2!$A:$A,0)+COLUMN(C2)-1))
         ~~~~~~~~~~                                                 ~~~~~~~~~
 MATCH($B3,Sheet2!$A:$A,0)+COLUMN(C2)-1　→　4＋3－1　→　「6」
 Sheet2のB6セルの値が返り「本社」が表示される。

 ■G3セル
 =IF($C3<COLUMN(D2),"",INDEX(Sheet2!$B:$B,MATCH($B3,Sheet2!$A:$A,0)+COLUMN(D2)-1))
     ~~~~~~~~~~~~~~
 C3は「3」、COLUMN(D2)は「4」
 =IF($C3<COLUMN(D2),"", ･･･　この条件が成立するので「""」が返る → 何も表示されない

 右方向にどの列までコピーしたのかわかりませんけど、H列以降も同様です（何も表示されない）

 以上、参考まで。
（笑） 2017/03/17(金) 01:26

ご回答頂きありがとうございます。
どこがわからないのか明記せず、申し訳ございません。

・IF($C2<COLUMN(A1),"",INDEX(Sheet2!$B:$B,MATCH($B2,Sheet2!$A:$A,0)+COLUMN(A1)-1))
これは$C2<COLUMN(A1)であるならば、
真のときは""（つまり空白）、
偽のときはINDEX(Sheet2!$B:$B,MATCH($B2,Sheet2!$A:$A,0)+COLUMN(A1)-1)である。

・$C2<COLUMN(A1)
これはC2の値がA1の列番号（1）よりも小さい
というのは所属部署数が1よりも小さい場合ということであり、
これはつまり部署数がない（C2の値が0）場合である。

そのため
D2=IF($C2<COLUMN(A1),"",INDEX(Sheet2!$B:$B,MATCH($B2,Sheet2!$A:$A,0)+COLUMN(A1)-1))
この式は、所属部署がない場合は空白になるということは理解できました。

ただ、
INDEX(Sheet2!$B:$B,MATCH($B2,Sheet2!$A:$A,0)+COLUMN(A1)-1)
この式がいまいち理解できません。

理解力が乏しく大変恐縮ですが、ご回答いただけますと幸いです。

（ゆずひこ） 2017/03/17(金) 11:26

 ＞INDEX(Sheet2!$B:$B,MATCH($B2,Sheet2!$A:$A,0)+COLUMN(A1)-1) 
 ＞この式がいまいち理解できません。

 そこは前回の説明で最も力を込めたところなんですけど、
 説明を読んでも理解できなかったのはどこですか？
 それを書いてもらわないと、結局前回の説明をくり返すことになります。

 1）その式から「+COLUMN(A1)-1」を外した ↓ の式の意味はわかりますか？
  　=INDEX(Sheet2!$B:$B,MATCH($B2,Sheet2!$A:$A,0))

 　 MATCH($B2,Sheet2!$A:$A,0) は前回説明したとおり、サンプルデータで言うと
 　 B2が user1 だったら「2」、user2 だったら「4」、user3 だったら「7」になる。

 　 つまり、B2が「user1」だったら
    =INDEX(Sheet2!$B:$B,MATCH($B2,Sheet2!$A:$A,0))
            ↓
  　=INDEX(Sheet2!$B:$B,2)
  　                   ~~~
 　 ということになり、Sheet2のB2セルの値「営業部」が返る。

  　これは理解できますか？

 2）=IF($C2<COLUMN(A1),"",INDEX(Sheet2!$B:$B,MATCH($B2,Sheet2!$A:$A,0)+COLUMN(A1)-1))

 　↑ の式を右方向にどれだけコピーしても、
 　変化するのはCOLUMN関数の引数（A1のところ）だけ、というのはわかってますか？

 　COLUMN(A1) は「1」、COLUMN(B1) は「2」、COLUMN(C1) は「3」、･･･というのは理解できてますか？

 　B2が「user1」で、MATCH($B2,Sheet2!$A:$A,0) が「2」だったら、

   ■D2セル
   =INDEX(Sheet2!$B:$B,2+COLUMN(A1)-1)
 　　　　　　↓
   =INDEX(Sheet2!$B:$B,2+1-1)
 　　　　　　↓　　　　
 　=INDEX(Sheet2!$B:$B,2)　になるので、Sheet2のB2セルの値「営業部」が返る。

   ■E2セル
   =INDEX(Sheet2!$B:$B,2+COLUMN(B1)-1)
 　　　　　　↓
   =INDEX(Sheet2!$B:$B,2+2-1)
 　　　　　　↓　　　　
 　=INDEX(Sheet2!$B:$B,3)　になるので、Sheet2のB3セルの値「横浜事業所」が返る。

   ここまで理解できますか？

 とりあえず以上です。
（笑） 2017/03/17(金) 17:23

ようやく理解できました！
何度もご回答いただきありがとうございました！
（ゆずひこ） 2017/03/17(金) 18:07

コメント返信：

[ 一覧(最新更新順) ]

[[20170316093727]] 『2つのシートを1つにまとめたい』（ゆずひこ） ページの最後に飛ぶ

************************************************************

[[20170316093727]]　『2つのシートを1つにまとめたい』（ゆずひこ）　ページの最後に飛ぶ