[[20171019071939]] 『「名簿の集計」[右近] について』(右近) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『「名簿の集計」[右近] について』(右近)

投稿
[[20160210102416]] 『名簿の集計』(右近) 
について...

お世話になります。
以前、お尋ねさせていただいた内容をさらに深める形で質問させていただきます。
シート1に以下のような表があるとします。

  A    B        C         D       E・・・
  番号 担当者   氏名      行政区
1 1    aさん    山田花子  A     
2 2    bさん    鈴木太郎  B
3 3    cさん    阿部次郎  C
4 4    aさん    佐藤美子  A
5 5    cさん    山下泰広  C
6 6  dさん    棟田靖之 D
7 7  dさん    安室太郎 D
8 8  aさん    佐藤次郎 A
9 9  dさん    久保田理 E
・
・
・

シート2にシート1のデータを以下のように表示させたいと考えています。
以前も教えていただきましたが、以前のお尋ねと少し違っております。
違っているところは、B列の担当者を追加したことと、D列の行政区が一つしかない場合(上記ではB地区)の表示方法を以下のようにしたいと考えています。

   A    B         C       D
   番号 担当者    行政区  氏名
2  1    aさん     A      山田花子          
3  2                      佐藤美子
4  3    (3名)             佐藤次郎
5  4    bさん     B      鈴木太郎
6       (1名) 
7  5    cさん     C      阿部次郎
8  6    (2名)             山下泰広
9  7    dさん     D      棟田靖之
10 8                      安室太郎
11 9    (3名)     E      久保田理

上記のように、B地区は1名しかいませんが、1名の場合は上記のように必ず空白行を追加する。
必ず、B列に担当者ごとの人数を()書きで入るようにする。
同一担当者で、複数の地区を担当している場合もある。
地区名はあくまで重複するものは表示しない。

以上のような形に、できれば関数で表示させたいと考えています。
どうぞよろしくお願いします。

< 使用 Excel:Excel2016、使用 OS:Windows10 >


(ピボットテーブルで集計して必要なとこ挿入したり記入したりじゃだめなのかなぁ。。。)
(まっつわん) 2017/10/19(木) 08:09

出来れば、関数で何とかできればと思います。
よろしくお願いします。
(右近) 2017/10/19(木) 08:12

 >出来れば、関数で何とかできればと思います。

 数式処理に向いていないような気がするんですが、マクロに鞍替えする気はありませんか?

 その気が無い場合、以下、ちょっとお聞きします。

 (1)Sheet1は、実際何行くらいある表なんですか?

 (2)Sheet1のA列の番号は連番ですね(欠番なし)

 (3)Sheet2のA列の番号は意味があるんでしょうか? 必要なんですか?
   Sheet1にある番号とも違いますので、この番号の使い道が分からないのですが・・
   余り意味のないものであれば、出来れば無視したいです。

 (4)担当者名と行政区名は、重複の無いリストをどこか空いているエリアに書き出して置くことは出来ませんか?
   それとも、それ等はやたらに変動するので、そんなリストは作って置けないですか?

(半平太) 2017/10/19(木) 15:48


半平太さま、後ほどご回答いたします。
ありがとうございます。
よろしくお願いいたします。
(右近) 2017/10/19(木) 16:04

出来れば、関数で作成したいです。
ご質問いただいたことについて、以下のとおり回答いたします。

(1)Sheet1は、実際何行くらいある表なんですか?

余裕をもたせて、100行あれば大丈夫です。

(2)Sheet1のA列の番号は連番ですね(欠番なし)

はい。そのとおりです。 Sheet2には、空白行が入る可能性がありますので、以下のような関数を入れています。 =IF(I8="","",ROW()-7-COUNTIF($I$8:I8,"")) ← セル番号はあくまで例です。

(3)Sheet2のA列の番号は意味があるんでしょうか? 必要なんですか?
  Sheet1にある番号とも違いますので、この番号の使い道が分からないのですが・・
  余り意味のないものであれば、出来れば無視したいです。

はい。あります。(2)のようにして連番にしております。 Sheet1との番号と違いますが、必要です。できれば、無視しないでいただきたいです。

(4)担当者名と行政区名は、重複の無いリストをどこか空いているエリアに書き出して置くことは出来ませんか?
  それとも、それ等はやたらに変動するので、そんなリストは作って置けないですか?

おっしゃるとおり、別シートに基本情報という形で、担当者名一覧を作成しています。 ここからリストで選択する仕様にしております。

以上、お答えいたします。
半平太さん、どうぞよろしくお願いいたします。
(右近) 2017/10/19(木) 16:18


追加でお尋ねです。
Sheet1のE列に、地区のさらに詳細の場所が以下のとおり入っているとして、

  A    B        C         D       E・・・
  番号 担当者   氏名      行政区
1 1    aさん    山田花子  A     
2 2    bさん    鈴木太郎  B
3 3    cさん    阿部次郎  C
4 4    aさん    佐藤美子  A      aa
5 5    cさん    山下泰広  C
6 6  dさん    棟田靖之 D
7 7  dさん    安室太郎 D      dd
8 8  aさん    佐藤次郎 A
9 9  dさん    久保田理 E
・
・
・

以下のように同じ行政区の中でもE列に入力されているところだけを表示させることは可能でしょうか。
もちろん、全てに入っているわけではありません。

   A    B         C       D
   番号 担当者    行政区  氏名
2  1    aさん     A      山田花子          
3  2              aa      佐藤美子
4  3    (3名)             佐藤次郎
5  4    bさん     B      鈴木太郎
6       (1名) 
7  5    cさん     C      阿部次郎
8  6    (2名)             山下泰広
9  7    dさん     D      棟田靖之
10 8              dd      安室太郎
11 9    (3名)     E      久保田理

最初に質問しておくべきでした。
申し訳ありません。ご教授のほどよろしくお願いします。

(右近) 2017/10/19(木) 17:42


データ数に応じて1行増えるとか、2列分の情報を1列にまとめるとか(これだけなら末尾列を探せばできるか…)、数式で処理するには無理っぽい内容に思いますよ。 せめて、1名ならば括弧の前に改行を入れて、行数は変えない、とかなら…。 A列の計算式も、説明にないI列を参照しているし、I列にも手を入れないとA列空欄が表現できなそうだし、頑張っても無駄に終わりそう。

どうしてもこの表に変換するならば、マクロで処理すべきと思います。それが嫌ならば、手作業編集するしか思いつきません。
(???) 2017/10/19(木) 18:00


 >別シートに基本情報という形で、
 >担当者名一覧を作成しています。 ここからリストで選択する仕様にしております。

 別シートにそのリストが
 こうなっているものと仮定します
  ↓
   行  ___A___  ___B___
   1  行政区   担当者 
   2  A       aさん  
   3  B       bさん  
   4  C       cさん  
   5  F       dさん  
   6  D              
   7  E              

<Sheet1 サンプルデータ>

  行  __A__  ___B___  ____C____  ___D___
   1  番号   担当者   氏名       行政区 
   2     1   aさん    山田花子   A     
   3     2   bさん    鈴木太郎   B     
   4     3   cさん    阿部次郎   C     
   5     4   aさん    佐藤美子   A     
   6     5   cさん    山下泰広   C     
   7     6   dさん    棟田靖之   D     
   8     7   dさん    安室太郎   D     
   9     8   aさん    佐藤次郎   A     
  10     9   dさん    久保田理   E     

<Sheet2 結果図>

  行 __A__ ___B___ ___C___ ____D____ _E_ ___F___ ___G___ ___H___ ___I___ ____J____ ______K______
   1 番号  担当者  行政区  氏名          KEY         10  行政区  担当者  担当先数  同左1個のキー
   2    1  aさん   A      山田花子      10,102  10,102  A      aさん          3               
   3    2                  佐藤美子      20,203  10,105  B      bさん          1          20203
   4    3  (3名)           佐藤次郎      30,304  10,109  C      cさん          2               
   5    4  bさん   B      鈴木太郎      10,105  20,203  F      dさん          3               
   6       (1名)                         30,306  20,203  D                                     
   7    5  cさん   C      阿部次郎      60,507  30,304  E                                     
   8    6  (2名)           山下泰広      60,508  30,306                                         
   9    7  dさん   D      棟田靖之      10,109  60,507                                         
  10    8                  安室太郎      60,610  60,508                                         
  11    9  (3名)   E      久保田理              60,610                                         
  12                                                                                            

<Sheet2に入力する数式>

 (1) G1セル =SUM(J:J)+COUNT(K:K)

 (2) A2セル =IF(OR(B2&D2="",RIGHT(B2,3)="1名)"),"",MAX(A$1:A1)+1)
 (3) B2セル =IF(G2="","",IF(INT(G2/10000)<>INT(N(G3)/10000),"("&VLOOKUP(INDEX(Sheet1!B:B,MOD(G2,100)),$I$2:$J$50,2,FALSE)&"名)",IF(INT(G2/10000)=INT(G1/10000),"",INDEX(Sheet1!B:B,MOD(G2,100)))))
 (4) C2セル =IF(OR(INT(N(G2)/100)=INT(N(G1)/100),D2=""),"",INDEX(Sheet1!D$1:D$100,MOD(G2,100)))
 (5) D2セル =IF(G$1<ROW(AB1),"",IF(RIGHT(B2,3)="1名)","",INDEX(Sheet1!C$1:C$100,MOD(G2,100))))
 (6) F2セル =IF(Sheet1!B2="","",IFERROR(MATCH(Sheet1!$B$2:$B$100,Sheet1!$B$2:$B$100,0)*10000+MATCH(Sheet1!D2,$H$2:$H$50,0)*100+ROW(),""))
 (7) G2セル =IF(G$1<ROW(Z1),"",AGGREGATE(15,6,($F$2:$F$100,$K$2:$K$50),ROW(Z1)))
 (8) H2セル =INDEX(別シート!A:A,ROW())&""
 (9) I2セル =INDEX(別シート!B:B,ROW())&""
 (10) J2セル =IF(I2="","",COUNTIF(Sheet1!B:B,I2))
 (11) K2セル =IF(J2=1,INDEX(F$2:F$10,MATCH(I2,Sheet1!B$2:B$100,0)),"")

 ※「A2k〜K2」の数式を下にコピー

(半平太) 2017/10/19(木) 19:28


半平太さん、ありがとうございます。
後ほど、やってみます!

ただ、以下の形も実現可能でしょうか?

以下のように同じ行政区の中でもE列に入力されているところだけを表示させることは可能でしょうか。
もちろん、全てに入っているわけではありません。

   A    B         C       D
   番号 担当者    行政区  氏名
2  1    aさん     A      山田花子          
3  2              aa      佐藤美子
4  3    (3名)             佐藤次郎
5  4    bさん     B      鈴木太郎
6       (1名) 
7  5    cさん     C      阿部次郎
8  6    (2名)             山下泰広
9  7    dさん     D      棟田靖之
10 8              dd      安室太郎
11 9    (3名)     E      久保田理
(右近) 2017/10/19(木) 19:48

 >追加でお尋ねです。 
 >Sheet1のE列に、地区のさらに詳細の場所が以下のとおり入っているとして、

 あれ? こんな追加があったんだ。

 でも、よく分からないですね。
 山田花子のE列にaaが入っていたらどうするんですか?

(半平太) 2017/10/19(木) 20:10


山田花子には入っていない、つまり、1行目はあくまで行政区のAしか入っていないと仮定したらいかがですか?
色々言って申し訳ありません。
(右近) 2017/10/19(木) 20:18

万一、E列にaaが入っていたら、
A aa のような表示にすることは可能でしょうか?
(右近) 2017/10/19(木) 20:31

 >万一、E列にaaが入っていたら、 
 >A aa のような表示にすることは可能でしょうか?

 仕様が固まっていない状況なら、キリがないので私はドロップアウトします。

 へ変更
  ↓
 C2セル =IF(OR(INT(N(G2)/100)=INT(N(G1)/100),D2=""),"",INDEX(Sheet1!D$1:D$100,MOD(G2,100)))&" "&INDEX(Sheet1!E:E,IFERROR(MOD(G2,100),9999))

(半平太) 2017/10/19(木) 20:37


おはようございます。
半平太さん、今一度、ご教授願います。
半平太さんからご提示いただいた関数を入れてみたところ、結果が出てきません。
もしかすると、別シートに作成している担当者の担当地区一覧に問題があるのか?と思います。
仕様をきちんとお伝えしていないのがそもそもの原因なのですが、実際には以下のようになっています。

A列に担当者名、B列に電話番号、C列からE列に地区名が入っています。

各担当者が担当する地区は、最大4箇所ありますので、C列からF列に地区名を入れております。
担当者によって、担当地区数は異なります。

最初にお伝えすべきことでした。申し訳ありません。
今一度、どうぞよろしくお願いします。
(右近) 2017/10/20(金) 06:32


 テスト用データと数式を当方と合わせてください。

 沢山の数式とデータをこの掲示板から転記するのは面倒と思いますので、
 後記マクロでそれらを自動的に埋めちゃってください。 

 必ず、新規ブックでテストしてください。

 <自動埋込み手順>
  新規ブックの新規シート(どのシートでも結構です)の「シート見出し」を右クリックして、
 「コードの表示(V)」を選ぶと画面中央に白いエリアが表れます。(VBE画面です)

  その白いエリアに後記マクロをコピぺし、F5キーを押下してください
   (すると、マクロ「onlyOnce」が実行され、自動的に数式とテストデータが入力されます)

  ※実行は1回だけですので、終わったら「Ctrl+Z」でコードを消去し、Alt+F11でエクセルに戻って下さい

 自動埋め込み結果図

 <Sheet1>                    ┃ <別シート>
  行  __A__  ___B___  ____C____  ___D___  _E_    ┃   行  ____A____  ____B____  ___C___  ___D___  ___E___
   1  番号   担当者   氏名       行政区          ┃    1  担当者名   電話番号   地区名1  地区名2  地区名3
   2     1   aさん    山田花子   A              ┃    2  aさん      03-・・    A       A2      A3    
   3     2   bさん    鈴木太郎   B              ┃    3  bさん      4-・・     B1      B       B3    
   4     3   cさん    阿部次郎   C       ff     ┃    4  cさん      5-・・     C1      C2      C     
   5     4   aさん    佐藤美子   A       aa     ┃    5  dさん      6-・・     D       E1      E     
   6     5   cさん    山下泰広   C              ┃
   7     6   dさん    棟田靖之   D              ┃
   8     7   dさん    安室太郎   D       dd     ┃
   9     8   aさん    佐藤次郎   A              ┃
  10     9   dさん    久保田理   E              ┃

 <Sheet2>
  行 __A__ ___B___ ___C___ ____D____ _E_ ___F___ ___G___ ___H___ ___I___ ___J___ ___K___ ____L____ ______M______
   1 番号  担当者  行政区  氏名          KEY          10 担当者  地区名1 地区名2 地区名3 担当先数  同左1個のキー
   2    1  aさん   A      山田花子      1001002 1001002 aさん   A      A2     A3            3               
   3    2          aa      佐藤美子      2002003 1001005 bさん   B1     B      B3            1        2002003
   4    3  (3名)           佐藤次郎      3003004 1001009 cさん   C1     C2     C             2               
   5    4  bさん   B      鈴木太郎      1001005 2002003 dさん   D      E1     E             3               
   6       (1名)                         3003006 2002003                                                        
   7    5  cさん   C ff   阿部次郎      6001007 3003004                                                        
   8    6  (2名)           山下泰広      6001008 3003006                                                        
   9    7  dさん   D      棟田靖之      1001009 6001007                                                        
  10    8          dd      安室太郎      6003010 6001008                                                        
  11    9  (3名)   E      久保田理              6003010                                                        

  ’以下、シートモジュールに貼り付けるマクロ

 Private Sub onlyOnce()

     ThisWorkbook.Sheets.Add After:=Sheets(Sheets.Count), Count:=2

     With Sheets("Sheet1")
         .Range("A1").Value = "番号"
         .Range("B1").Value = "担当者"
         .Range("C1").Value = "氏名"
         .Range("D1").Value = "行政区"
         .Range("A2").Value = 1
         .Range("B2,B5,B9").Value = "aさん"
         .Range("C2").Value = "山田花子"
         .Range("D2,D5,D9").Value = "A"
         .Range("A3").Value = 2
         .Range("B3").Value = "bさん"
         .Range("C3").Value = "鈴木太郎"
         .Range("D3").Value = "B"
         .Range("A4").Value = 3
         .Range("B4,B6").Value = "cさん"
         .Range("C4").Value = "阿部次郎"
         .Range("D4,D6").Value = "C"
         .Range("E4").Value = "ff"
         .Range("A5").Value = 4
         .Range("C5").Value = "佐藤美子"
         .Range("E5").Value = "aa"
         .Range("A6").Value = 5
         .Range("C6").Value = "山下泰広"
         .Range("A7").Value = 6
         .Range("B7:B8,B10").Value = "dさん"
         .Range("C7").Value = "棟田靖之"
         .Range("D7:D8").Value = "D"
         .Range("A8").Value = 7
         .Range("C8").Value = "安室太郎"
         .Range("E8").Value = "dd"
         .Range("A9").Value = 8
         .Range("C9").Value = "佐藤次郎"
         .Range("A10").Value = 9
         .Range("C10").Value = "久保田理"
         .Range("D10").Value = "E"
     End With

     With Sheets("Sheet3")
         Rem 生データのセルをまとめて処理
         .Range("A1").Value = "担当者名"
         .Range("B1").Value = "電話番号"
         .Range("C1").Value = "地区名1"
         .Range("D1").Value = "地区名2"
         .Range("E1").Value = "地区名3"
         .Range("A2").Value = "aさん"
         .Range("B2").Value = "03-・・"
         .Range("C2").Value = "A"
         .Range("D2").Value = "A2"
         .Range("E2").Value = "A3"
         .Range("A3").Value = "bさん"
         .Range("B3").Value = "4-・・"
         .Range("C3").Value = "B1"
         .Range("D3").Value = "B"
         .Range("E3").Value = "B3"
         .Range("A4").Value = "cさん"
         .Range("B4").Value = "5-・・"
         .Range("C4").Value = "C1"
         .Range("D4").Value = "C2"
         .Range("E4").Value = "C"
         .Range("A5").Value = "dさん"
         .Range("B5").Value = "6-・・"
         .Range("C5").Value = "D"
         .Range("D5").Value = "E1"
         .Range("E5").Value = "E"

         .Name = "別シート"
     End With

     With Sheets("Sheet2")

         Rem 生データのセルをまとめて処理
         .Range("A1").Value = "番号"
         .Range("B1,H1").Value = "担当者"
         .Range("C1").Value = "行政区"
         .Range("D1").Value = "氏名"
         .Range("F1").Value = "KEY"
         .Range("I1").Value = "地区名1"
         .Range("J1").Value = "地区名2"
         .Range("K1").Value = "地区名3"
         .Range("L1").Value = "担当先数"
         .Range("M1").Value = "同左1個のキー"

         Rem 数式セルをまとめて処理
         .Range("G1").FormulaR1C1Local = "=SUM(C[5])+COUNT(C[6])"
         .Range("A2:A110").FormulaR1C1Local = "=IF(OR(RC[1]&RC[3]="""",RIGHT(RC[1],3)=""1名)""),"""",MAX(R1C:R[-1]C)+1)"
          .Range("B2:B109").FormulaR1C1Local = "=IF(RC[5]="""","""",IF(INT(RC[5]/1000000)<>INT(N(R[1]C[5])/1000000),""(""&VLOOKUP(INDEX(Sheet1!C,MOD(RC[5],1000)),R2C8:R50C12,5,FALSE)&""名)"",IF(INT(RC[5]/1000000)=INT(R[-1]C[5]/1000000),"""",INDEX(Sheet1!C,MOD(RC[5],1000)))))"
         .Range("C2:C110").FormulaR1C1Local = "=TRIM(IF(OR(INT(N(RC[4])/1000)=INT(N(R[-1]C[4])/1000),RC[1]=""""),"""",INDEX(Sheet1!R1C[1]:R100C[1],MOD(RC[4],1000)))&"" ""&INDEX(Sheet1!C[2],IFERROR(MOD(RC[4],1000),9999)))"
         .Range("D2:D110").FormulaR1C1Local = "=IF(R1C[3]<ROW(R[-1]C[24]),"""",IF(RIGHT(RC[-2],3)=""1名)"","""",INDEX(Sheet1!R1C[-1]:R100C[-1],MOD(RC[3],100))))"
         .Range("F2:F110").FormulaR1C1Local = "=IF(Sheet1!RC[-4]="""","""",IFERROR(MATCH(Sheet1!R2C2:R100C2,Sheet1!R2C2:R100C2,0)*1000000+MATCH(Sheet1!RC[-2],INDEX(R2C[3]:R50C[5],MATCH(Sheet1!RC[-4],R2C[2]:R50C[2],0),0),0)*1000+ROW(),""""))"
         .Range("G2:G110").FormulaR1C1Local = "=IF(R1C<ROW(R[-1]C[19]),"""",SMALL((R2C6:R100C6,R2C13:R50C13),ROW(R[-1]C[19])))"
         .Range("H2:H50").FormulaR1C1Local = "=INDEX(別シート!C[-7],ROW())&"""""
         .Range("I2:K50").FormulaR1C1Local = "=INDEX(別シート!C[-6],ROW())&"""""
         .Range("L2:L50").FormulaR1C1Local = "=IF(RC[-4]="""","""",COUNTIF(Sheet1!C[-10],RC[-4]))"
         .Range("M2:M50").FormulaR1C1Local = "=IF(RC[-1]=1,INDEX(R2C[-7]:R100C[-7],MATCH(RC[-5],Sheet1!R2C[-11]:R100C[-11],0)),"""")"
          .Range("B110").FormulaR1C1Local = "=IF(RC[5]="""","""",IF(INT(RC[5]/1000000)<>INT(N(#REF!)/1000000),""(""&VLOOKUP(INDEX(Sheet1!C,MOD(RC[5],1000)),R2C8:R50C12,5,FALSE)&""名)"",IF(INT(RC[5]/1000000)=INT(R[-1]C[5]/1000000),"""",INDEX(Sheet1!C,MOD(RC[5],1000)))))"

     End With
 End Sub

(半平太) 2017/10/20(金) 11:05


半平太さん、すばらしいです。
ただただ、感動しております。

ただ、今一度、以下のように列の追加と配列を変えたいのです。
Sheet1の電話番号は、別シートからVLOOKUP関数にてもってきたいと考えています。
また、その電話番号をSheet2で、B列の担当者とセットで同じセルに表示させたいです。
書式設定の折り返し等を行って。。。

大変厚かましいですが、今一度どうぞよろしくお願いいたします。

<Sheet1>                    

  行  __A__  ___B___  ___C___  ___D___  __E__  __D__  ____D____  ____F____
   1  番号   担当者   電話番号 行政区          曜日   氏名       生年月日 
   2     1   aさん    00-0000  A              月金   山田花子   S00.00.00
   3     2   bさん    00-0000  B                     鈴木太郎   S00.00.00
   4     3   cさん    00-0000  C       ff     月水金 阿部次郎   H00.00.00
   5     4   aさん    00-0000  A       aa            佐藤美子   S00.00.00
   6     5   cさん    00-0000  C                     山下泰広   S00.00.00
   7     6   dさん    00-0000  D                     棟田靖之   H00.00.00
   8     7   dさん    00-0000  D       dd     火木   安室太郎   H00.00.00
   9     8   aさん    00-0000  A                     佐藤次郎   S00.00.00
  10     9   dさん    00-0000  E                     久保田理   S00.00.00

<Sheet2>

  行 __A__ ___B___ ___C___ __D__  ____D____  ___E___   _E_ ___F___ ___G___ ___H___ ___I___ ___J___ ___K___ ____L____ ______M______
   1 番号  担当者  行政区  曜日   氏名       生年月日      KEY          10 担当者  地区名1 地区名2 地区名3 担当先数  同左1個のキー
           電話番号
   2    1  aさん   A      月金   山田花子   S00.00.00     1001002 1001002 aさん   A      A2     A3            3               
           00-0000
   3    2          aa             佐藤美子   S00.00.00     2002003 1001005 bさん   B1     B      B3            1        2002003
   4    3  (3名)                  佐藤次郎   S00.00.00     3003004 1001009 cさん   C1     C2     C             2               
   5    4  bさん   B             鈴木太郎   S00.00.00     1001005 2002003 dさん   D      E1     E             3               
           00-0000
   6       (1名)                                           3003006 2002003                                                        
   7    5  cさん   C ff   月水金 阿部次郎   H00.00.00     6001007 3003004                                                        
           00-0000
   8    6  (2名)                  山下泰広   S00.00.00     6001008 3003006                                                        
   9    7  dさん   D             棟田靖之   H00.00.00     1001009 6001007                                                        
           00-0000
  10    8          dd      火木   安室太郎   H00.00.00     6003010 6001008                                                        
  11    9  (3名)   E             久保田理   S00.00.00     6003010          
(右近) 2017/10/20(金) 12:45

 Sheet1とSheet2に対して、「列挿入・列入替」して、
 標準的なVLOOKUP関数を使って、引っ張ってくればいい話ですよね?

 それは、そちらで対応してください。

 そんなに簡単な事じゃないという話でしたら、その事情を書いてください。

(半平太) 2017/10/20(金) 13:16


半平太さん、ありがとうございます。

列挿入、列入替を行って、また、マクロの修正を試みたのですが、

やはり、かなり私には困難なもので・・・

申し訳ありません。
(右近) 2017/10/20(金) 14:47


 マクロはもう必要ないですけどぉ?

 新規ブックでマクロを実行すれば、当初のレイアウトでは完成ですよね。

 その後、手操作で「列の挿入や、列の入替」をすれば、既にある数式は自動的に変更されるので、
 新しい列に追加で必要になったVLOOKUPの数式を書き足せばいいハズです。

(半平太) 2017/10/20(金) 15:10


すみません。
私のやり方が間違っているのですね。
シート1に列の挿入、入替えなどを行うと、シート2のデータが真っ白になってしまい、
見えなくなってしまいます。
入替えなどを行った後、シート2の関数を修正する必要があるのでしょうか?
(右近) 2017/10/20(金) 15:21

 >入替えなどを行った後、シート2の関数を修正する必要があるのでしょうか?

 ないです。自動的にエクセルが修正してくれます。

 「列の挿入・削除」をマクロでやるなら(先の完成版を作ったあと)
  こんなのを実行すれば出来ます。 
  ↓
 Sub ColumnInsertExchange()
     With Sheets("Sheet1")
         .Columns("C:L").Insert Shift:=xlToRight
         .Columns("N:O").Cut
         .Columns("D:E").Insert Shift:=xlToRight
         .Columns("O:O").Cut
         .Columns("G:G").Insert Shift:=xlToRight
     End With

     With Sheets("Sheet2")
         .Columns("E:E").Insert Shift:=xlToRight
         .Columns("D:D").Insert Shift:=xlToRight

     End With
 End Sub

 ’※どこのシートモジュールでもいいです。貼り付け後「F5」で実行。あと消去。

(半平太) 2017/10/20(金) 16:29


半平太さん、ありがとうございます。
希望どおりの形になりました。本当に感謝いたします。
このように自由自在に思う形を作れたら、楽しいでしょうね。改めてそう感じました。

半平太さん、ここからはもし可能ならばですが、以前から手動式で作成していたものにあった機能を再現できれば、完璧なのですが、一応仕様について説明させていただきます。

?@シート2のB列の担当者の電話番号をシート3から参照させて、以下のように表示させたいです。
あくまで、一つのセルに「aさん 00-0000」という形です。
<Sheet2>

  行 __A__ ___B___ ___C___ __D__  ____D____  ___E___   _E_ ___F___ ___G___ ___H___ ___I___ ___J___ ___K___ ____L____ ______M______
   1 番号  担当者  行政区  曜日   氏名       生年月日      KEY          10 担当者  地区名1 地区名2 地区名3 担当先数  同左1個のキー
           電話番号
   2    1  aさん   A      月金   山田花子   S00.00.00     1001002 1001002 aさん   A      A2     A3            3               
           00-0000
   3    2          aa             佐藤美子   S00.00.00     2002003 1001005 bさん   B1     B      B3            1        2002003
   4    3  (3名)                  佐藤次郎   S00.00.00     3003004 1001009 cさん   C1     C2     C             2               
   5    4  bさん   B             鈴木太郎   S00.00.00     1001005 2002003 dさん   D      E1     E             3               
           00-0000
   6       (1名)                                           3003006 2002003                                                        
   7    5  cさん   C ff   月水金 阿部次郎   H00.00.00     6001007 3003004                                                        
           00-0000
   8    6  (2名)                  山下泰広   S00.00.00     6001008 3003006                                                        
   9    7  dさん   D             棟田靖之   H00.00.00     1001009 6001007                                                        
           00-0000
  10    8          dd      火木   安室太郎   H00.00.00     6003010 6001008                                                        
  11    9  (3名)   E             久保田理   S00.00.00     6003010          

?Aシート1のI列に「休」という文字を入れると、シート2のB列の(〇名)の人数に反映させたいのです。名前そのものは残っても構わないのですが、人数に反映させられればと考えています。

半平太さん、どうぞよろしくお願いいたします。

(右近) 2017/10/20(金) 21:57


 >シート2のB列の担当者の電話番号をシート3から参照させて、
  ↑
 電話番号はシート1にもあったと思うので、そっちから取ってくれば簡単に対応出来ると思うのですが、
 こっちは何のことかわからないです。
  ↓
 >Aシート1のI列に「休」という文字を入れると、シート2のB列の(〇名)の人数に反映させたいのです。
 >名前そのものは残っても構わ以下のように表示させたいです。 

 誰の休みで、何を数えて○名にするのか、さっぱり分かりません?
 (いままでは単にシート1のB列にある各担当者の数ですよね?)

 名前そのものは残るというのも何だかよく分からないです。誰の名前が残ったり、排除したりするんですか?

 ※今日・明日、雨の中、出かけないとならないです‥トホホ

(半平太) 2017/10/21(土) 06:14


つたない説明で分かりづらくて申し訳ありません。

>電話番号はシート1にもあったと思うので、そっちから取ってくれば簡単に対応出来ると思うのですが、

 こっちは何のことかわからないです。
はい。おっしゃるのは分かります。ただ、すでにシート2のB列に組まれている関数をいかに修正して、担当者&電話番号を一つのセルに表示させたらよいのかが分からないという意味です。
上記シート2イメージ図のB2セルに、担当者&電話番号を表示させたいのです。要は上記図のB列に表示されている担当者名に電話番号を追加したいというものです。当然、一つのセルに入れるためには、書式設定で折り返すなどします。

  ↓
 >誰の休みで、何を数えて○名にするのか、さっぱり分かりません?
 (いままでは単にシート1のB列にある各担当者の数ですよね?)
 名前そのものは残るというのも何だかよく分からないです。誰の名前が残ったり、排除したりするんですか?

シート1の列(氏名)が休みの場合、I列に「休」(何でもよいのですが)の文字を入力すると、シート2のB列(担当者)のをカウントした数字から差し引く仕様にしたいのです。このB列の(◯名)は、担当者の数をカウントしてはいますが、私としては氏名(いわゆる対象者数)の数と考えて表示させています。
名前そのものは残る〜というのは無視していただいて構いません。これについては、条件付き書式で取り消し線を入れたいと思います。

(右近) 2017/10/21(土) 09:32


半平太さん、どうぞご教授のほどよろしくお願いします。
私も今日、明日と娘のバレーの遠征で遠方に来ております。
(右近) 2017/10/21(土) 09:44

 >B列(担当者)のをカウントした数字から差し引く仕様にしたいのです

 根本的な仕様の変更ですね。

 今まで1件とカウントしていたものが、今度は0件になることもあり得る事になりますね。
 そのケースに該当したら、Sheet2は一体どうなるべきなのか、こちらはさっぱり分かりません。

 また、2件だったものが、1件に減ったケースは・・・?

 この際、ここは閉じて、新規質問で全貌をご説明いただいた上で、フレッシュな回答者に考えて貰った方がいいと思います。

(半平太) 2017/10/22(日) 19:12


 基本的には今までと変わらず、件数表示だけ「休みを除く」と言うことなら。

 新規ブックの新規シートの「シートモジュール」に下記マクロを貼り付けて、F5 で実行。(実行は一回のみ、以後コードは消去)

 Private Sub onlyOnce()
    ThisWorkbook.Sheets.Add After:=Sheets(Sheets.Count), Count:=2
    Sheets(3).Name = "別シート"

    With Sheets("別シート")
        Rem 生データのセルをまとめて処理
        .Range("A1").Value = "担当者名"
        .Range("B1").Value = "電話番号"
        .Range("C1").Value = "地区名1"
        .Range("D1").Value = "地区名2"
        .Range("E1").Value = "地区名3"
        .Range("A2").Value = "aさん"
        .Range("B2").Value = "03-000a"
        .Range("C2").Value = "A"
        .Range("D2").Value = "A2"
        .Range("E2").Value = "A3"
        .Range("A3").Value = "bさん"
        .Range("B3").Value = "03-000b"
        .Range("C3").Value = "B1"
        .Range("D3").Value = "B"
        .Range("E3").Value = "B3"
        .Range("A4").Value = "cさん"
        .Range("B4").Value = "03-000c"
        .Range("C4").Value = "C1"
        .Range("D4").Value = "C2"
        .Range("E4").Value = "C"
        .Range("A5").Value = "dさん"
        .Range("B5").Value = "03-000d"
        .Range("C5").Value = "D"
        .Range("D5").Value = "E1"
        .Range("E5").Value = "E"

    End With

    With Sheets("Sheet1")
        Rem 生データのセルをまとめて処理
        .Range("A1").Value = "番号"
        .Range("B1").Value = "担当者"
        .Range("C1").Value = "電話番号"
        .Range("D1").Value = "行政区"
        .Range("F1").Value = "曜日"
        .Range("G1").Value = "氏名"
        .Range("H1").Value = "生年月日"
        .Range("A2").Value = 1
        .Range("B2,B5,B9").Value = "aさん"
        .Range("D2,D5,D9").Value = "A"
        .Range("G2").Value = "山田花子"
        .Range("I2:I3,I9").Value = "休"
        .Range("A3").Value = 2
        .Range("B3").Value = "bさん"
        .Range("D3").Value = "B"
        .Range("G3").Value = "鈴木太郎"
        .Range("A4").Value = 3
        .Range("B4,B6").Value = "cさん"
        .Range("D4,D6").Value = "C"
        .Range("E4").Value = "ff"
        .Range("G4").Value = "阿部次郎"
        .Range("A5").Value = 4
        .Range("E5").Value = "aa"
        .Range("G5").Value = "佐藤美子"
        .Range("A6").Value = 5
        .Range("G6").Value = "山下泰広"
        .Range("A7").Value = 6
        .Range("B7:B8,B10").Value = "dさん"
        .Range("D7:D8").Value = "D"
        .Range("G7").Value = "棟田靖之"
        .Range("A8").Value = 7
        .Range("E8").Value = "dd"
        .Range("G8").Value = "安室太郎"
        .Range("A9").Value = 8
        .Range("G9").Value = "佐藤次郎"
        .Range("A10").Value = 9
        .Range("D10").Value = "E"
        .Range("G10").Value = "久保田理"

        Rem 数式セルをまとめて処理
        .Range("C2:C10").FormulaR1C1Local = "=VLOOKUP(RC[-1],別シート!C[-2]:C[-1],2,FALSE)"

        Rem 標準外書式セルをまとめて処理
        .Range("C2:C10").NumberFormatLocal = "m""月""d""日"""

    End With

    With Sheets("Sheet2")
        Rem 生データのセルをまとめて処理
        .Range("A1").Value = "番号"
        .Range("B1").Value = "担当者" & Chr(10) & "電話番号"
        .Range("C1").Value = "行政区"
        .Range("D1").Value = "曜日"
        .Range("E1").Value = "氏名"
        .Range("F1").Value = "生年月日"
        .Range("H1").Value = "KEY"
        .Range("J1").Value = "担当者"
        .Range("K1").Value = "地区名1"
        .Range("L1").Value = "地区名2"
        .Range("M1").Value = "地区名3"
        .Range("N1").Value = "担当先数"
        .Range("O1").Value = "同左(除休)"
        .Range("P1").Value = "同左1個のキー"

        Rem 数式セルをまとめて処理
        .Range("I1").FormulaR1C1Local = "=SUM(C[5])+COUNT(C[7])"
        .Range("A2:A110").FormulaR1C1Local = "=IF(OR(RC[1]&RC[4]="""",RIGHT(RC[1],3)=""1名)""),"""",MAX(R1C:R[-1]C)+1)"
        .Range("B2:B110").FormulaR1C1Local = "=IF(RC[7]="""","""",IF(INT(RC[7]/1000000)<>INT(N(R[1]C[7])/1000000),""(""&VLOOKUP(INDEX(Sheet1!C,MOD(RC[7],1000)),R2C10:R50C15,6,FALSE)&""名)"",IF(INT(RC[7]/1000000)=INT(R[-1]C[7]/1000000),"""",INDEX(Sheet1!C,MOD(RC[7],1000))&CHAR(10)&INDEX(Sheet1!C[1],MOD(RC[7],1000)))))"
        .Range("C2:C110").FormulaR1C1Local = "=TRIM(IF(OR(INT(N(RC[6])/1000)=INT(N(R[-1]C[6])/1000),RC[2]=""""),"""",INDEX(Sheet1!R1C[1]:R100C[1],MOD(RC[6],1000)))&"" ""&INDEX(Sheet1!C[2],IFERROR(MOD(RC[6],1000),9999)))"
        .Range("E2:E110").FormulaR1C1Local = "=IF(R1C[4]<ROW(R[-1]C[26]),"""",IF(R[-1]C[4]=RC[4],"""",INDEX(Sheet1!R1C[2]:R100C[2],MOD(RC[4],100))))"
        .Range("H2:H110").FormulaR1C1Local = "=IF(Sheet1!RC[-6]="""","""",IFERROR(MATCH(Sheet1!R2C2:R100C2,Sheet1!R2C2:R100C2,0)*1000000+MATCH(Sheet1!RC[-4],INDEX(R2C[3]:R50C[5],MATCH(Sheet1!RC[-6],R2C[2]:R50C[2],0),0),0)*1000+ROW(),""""))"
        .Range("I2:I110").FormulaR1C1Local = "=IF(R1C<ROW(R[-1]C[20]),"""",SMALL((R2C8:R100C8,R2C16:R50C16),ROW(R[-1]C[20])))"
        .Range("J2:J50").FormulaR1C1Local = "=INDEX(別シート!C[-9],ROW())&"""""
        .Range("K2:M50").FormulaR1C1Local = "=INDEX(別シート!C[-8],ROW())&"""""
        .Range("N2:N50").FormulaR1C1Local = "=IF(RC[-4]="""","""",COUNTIF(Sheet1!C[-12],RC[-4]))"
        .Range("O2:O50").FormulaR1C1Local = "=IF(RC[-5]="""","""",RC[-1]-COUNTIFS(Sheet1!C[-13],RC[-5],Sheet1!C[-6],""休""))"
        .Range("P2:P50").FormulaR1C1Local = "=IF(RC[-2]=1,INDEX(R2C[-8]:R100C[-8],MATCH(RC[-6],Sheet1!R2C[-14]:R100C[-14],0)),"""")"

        Rem 標準外書式セルをまとめて処理
        .Columns("B:B").WrapText = True
    End With
 End Sub
(半平太) 2017/10/23(月) 00:15

半平太さん、お忙しい中にありがとうこざいます。
すごいです。こちらの要望どおりの結果がでました。
ただ、シート1に4行、シート2に11行挿入をしたところ、データが全くエラーとなってしまいました。
それぞれのセルの関数を確認、修正する必要があるのか見ていますが、うまくいきません。
まったく、面倒をおかけして申し訳ありません。今一度ご教授願います。
(右近) 2017/10/23(月) 16:51

 >ただ、シート1に4行、シート2に11行挿入をしたところ、
 >データが全くエラーとなってしまいました。

  何か当方が想定していない使い方をしているような気がしますが、
 取りあえず、以下お尋ねします。

 「シート1の何処に4行、シート2の何処に11行挿入」したのでしょうか?
  こちらで再現できる具体的情報を書いてください。

(半平太) 2017/10/23(月) 20:03


ご返事ありがとうございます。
想定外の使い方をしているんでしょうか?すいません。
まず、シート1の1行目に4行を挿入しました。
シート2も1行目に11行を挿入しました。
これは、いずれのシートもタイトルなどを手入力で入れるためです。
ところが、シート2の13行目以下がエラーとなってしまいました。
一つ一つ関数を修正してみてますが、うまく復元できません。
申し訳ありません。よろしくお願いします。
(右近) 2017/10/23(月) 20:46

半平太さん、それから以前にも教えていただいたんですが、列を挿入、削除した場合もエラーとなってしまいます。
シート1のC列に1列挿入して、別の列のコピーして、コピー元の列を削除すると、シート2がエラーとなってしまいます。
半平太さんのご説明では、列の挿入、削除をしても自動的に関数が修正されるとのことでしたので、何度となく、列の挿入、削除を試してみたんですが、何度やってもエラーとなってしまいます。
重ね重ね申し訳ありませんが、どうぞよろしくお願いします。
(右近) 2017/10/23(月) 22:40

 >これは、いずれのシートもタイトルなどを手入力で入れるためです。 

 タイトルの為の 行挿入 でしたか。
 それは想定としてはあり得る事ですが、初めに言ってください。

 そちらでレイアウトを変更する都度、こちらで対応することは致しません。
 ※質問はレイアウトを確定させてから行ってください。

 なので、この回答で私のレスは最終となります。

 ※レイアウト変更以外で問題が発生したのならば対応します。

  新規ブックの新規シートモジュールに貼り付けて実行(1回のみ。あと消去)

  Private Sub onlyOnce()

      ThisWorkbook.Sheets.Add After:=Sheets(Sheets.Count), Count:=2

      Sheets(3).Name = "別シート"

      With Sheets("別シート")
          Rem 生データのセルをまとめて処理
          .Range("A1").Value = "担当者名"
          .Range("B1").Value = "電話番号"
          .Range("C1").Value = "地区名1"
          .Range("D1").Value = "地区名2"
          .Range("E1").Value = "地区名3"
          .Range("A2").Value = "aさん"
          .Range("B2").Value = "03-000a"
          .Range("C2").Value = "A"
          .Range("D2").Value = "A2"
          .Range("E2").Value = "A3"
          .Range("A3").Value = "bさん"
          .Range("B3").Value = "03-000b"
          .Range("C3").Value = "B1"
          .Range("D3").Value = "B"
          .Range("E3").Value = "B3"
          .Range("A4").Value = "cさん"
          .Range("B4").Value = "03-000c"
          .Range("C4").Value = "C1"
          .Range("D4").Value = "C2"
          .Range("E4").Value = "C"
          .Range("A5").Value = "dさん"
          .Range("B5").Value = "03-000d"
          .Range("C5").Value = "D"
          .Range("D5").Value = "E1"
          .Range("E5").Value = "E"
      End With

      With Sheets("Sheet1")
          Rem 生データのセルをまとめて処理
          .Range("A5").Value = "番号"
          .Range("B5").Value = "担当者"
          .Range("C5").Value = "電話番号"
          .Range("D5").Value = "行政区"
          .Range("F5").Value = "曜日"
          .Range("G5").Value = "氏名"
          .Range("H5").Value = "生年月日"
          .Range("A6").Value = 1
          .Range("B6,B9,B13").Value = "aさん"
          .Range("D6,D9,D13").Value = "A"
          .Range("G6").Value = "山田花子"
          .Range("A7").Value = 2
          .Range("B7").Value = "bさん"
          .Range("D7").Value = "B"
          .Range("G7").Value = "鈴木太郎"
          .Range("I7").Value = "休"
          .Range("A8").Value = 3
          .Range("B8,B10").Value = "cさん"
          .Range("D8,D10").Value = "C"
          .Range("E8").Value = "ff"
          .Range("G8").Value = "阿部次郎"
          .Range("A9").Value = 4
          .Range("E9").Value = "aa"
          .Range("G9").Value = "佐藤美子"
          .Range("A10").Value = 5
          .Range("G10").Value = "山下泰広"
          .Range("A11").Value = 6
          .Range("B11:B12,B14").Value = "dさん"
          .Range("D11:D12").Value = "D"
          .Range("G11").Value = "棟田靖之"
          .Range("A12").Value = 7
          .Range("E12").Value = "dd"
          .Range("G12").Value = "安室太郎"
          .Range("A13").Value = 8
          .Range("G13").Value = "佐藤次郎"
          .Range("A14").Value = 9
          .Range("D14").Value = "E"
          .Range("G14").Value = "久保田理"

          Rem 数式セルをまとめて処理
          .Range("C6:C14").FormulaR1C1Local = "=VLOOKUP(RC[-1],別シート!C[-2]:C[-1],2,FALSE)"

      End With

      With Sheets("Sheet2")
          Rem 生データのセルをまとめて処理
          .Range("A12").Value = "番号"
          .Range("B12").Value = "担当者" & Chr(10) & "電話番号"
          .Range("C12").Value = "行政区"
          .Range("D12").Value = "曜日"
          .Range("E12").Value = "氏名"
          .Range("F12").Value = "生年月日"
          .Range("H12").Value = "KEY"
          .Range("J12").Value = "担当者"
          .Range("K12").Value = "地区名1"
          .Range("L12").Value = "地区名2"
          .Range("M12").Value = "地区名3"
          .Range("N12").Value = "担当先数"
          .Range("O12").Value = "同左(除休)"
          .Range("P12").Value = "同左1個のキー"
          .Range("Q12").Value = "POS"
          .Range("Q13").Value = 1

          Rem 数式セルをまとめて処理
          .Range("I12").FormulaR1C1Local = "=SUM(R13C14:R200C14)+COUNT(R13C16:R200C16)"
          .Range("A13:A121").FormulaR1C1Local = "=IF(OR(RC[1]&RC[4]="""",R[-1]C[8]=RC[8]),"""",MAX(R12C:R[-1]C)+1)"
            .Range("B13:B121").FormulaR1C1Local = "=IF(RC[7]="""","""",IF(INT(RC[7]/1000000)<>INT(N(R[1]C[7])/1000000),""(""&VLOOKUP(INDEX(Sheet1!R5C:R105C,MOD(RC[7],1000)),R13C10:R61C15,6,FALSE)&""名)"",IF(INT(RC[7]/1000000)=INT(R[-1]C[7]/1000000),"""",INDEX(Sheet1!R5C:R105C,MOD(RC[7],1000))&CHAR(10)&INDEX(Sheet1!R5C[1]:R105C[1],MOD(RC[7],1000)))))"
          .Range("C13:C121").FormulaR1C1Local = "=TRIM(IF(OR(INT(N(RC[6])/1000)=INT(N(R[-1]C[6])/1000),RC[2]=""""),"""",INDEX(Sheet1!R5C[1]:R104C[1],MOD(RC[6],1000)))&"" ""&INDEX(Sheet1!C[2],IFERROR(MOD(RC[6],1000),9999)))"
          .Range("E13:E121").FormulaR1C1Local = "=IF(R12C[4]<RC[12],"""",IF(R[-1]C[4]=RC[4],"""",INDEX(Sheet1!R5C[2]:R104C[2],MOD(RC[4],100))))"
            .Range("H13:H121").FormulaR1C1Local = "=IF(Sheet1!R[-7]C[-6]="""","""",IFERROR(MATCH(Sheet1!R[-7]C[-6],Sheet1!R6C2:R104C2,0)*1000000+MATCH(Sheet1!R[-7]C[-4],INDEX(R13C[3]:R61C[5],MATCH(Sheet1!R[-7]C[-6],R13C[2]:R61C[2],0),0),0)*1000+RC[9]+1,""""))"
          .Range("I13:I121").FormulaR1C1Local = "=IF(R12C<RC[8],"""",SMALL((R13C8:R111C8,R13C16:R61C16),RC[8]))"
          .Range("J13:J61").FormulaR1C1Local = "=INDEX(別シート!C[-9],RC[7]+1)&"""""
          .Range("K13:K61").FormulaR1C1Local = "=INDEX(別シート!C[-8],RC[6]+1)&"""""
          .Range("L13:L61").FormulaR1C1Local = "=INDEX(別シート!C[-8],RC[5]+1)&"""""
          .Range("M13:M61").FormulaR1C1Local = "=INDEX(別シート!C[-8],RC[4]+1)&"""""
          .Range("N13:N61").FormulaR1C1Local = "=IF(RC[-4]="""","""",COUNTIF(Sheet1!C[-12],RC[-4]))"
          .Range("O13:O61").FormulaR1C1Local = "=IF(RC[-5]="""","""",RC[-1]-COUNTIFS(Sheet1!C[-13],RC[-5],Sheet1!C[-6],""休""))"
          .Range("P13:P61").FormulaR1C1Local = "=IF(RC[-2]=1,INDEX(R13C[-8]:R111C[-8],MATCH(RC[-6],Sheet1!R6C[-14]:R104C[-14],0)),"""")"
          .Range("Q14:Q121").FormulaR1C1Local = "=R[-1]C+1"
          .Range("Q14:Q121") = .Range("Q14:Q121").Value

      End With
  End Sub

 >シート1のC列に1列挿入して、別の列のコピーして、コピー元の列を削除すると、シート2がエラーとなってしまいます。 

 「削除」は含まれません。数式が壊れます。

 私が言ったのは「列の挿入や、列の入替」です。

 こんなサイトでやり方が解説されています。
  ↓
http://did2memo.net/2017/03/24/excel-swap-columns/
 【Excel】「列」や「行」を入れ替える方法(ドラッグアンドドロップで手軽に行う方法)

(半平太) 2017/10/23(月) 23:58


半平太さん、本当にありがとうございます。
早速、仕事中にやってみました。ところが・・・

シート1は、104行目まで入力が可能ですよね?
ところがですね、シート1の104行目に斉藤と入力をして、
シート2を確認すると、以下のように該当する箇所に入ってくれません。
本来ならば、シート1の斉藤は、シート2の棟田★のところに
表示されなければならないと思います。
逆に、棟田★が変なところに登場してきており、斉藤がどこにも登場してきません。
ちなみに、103行目より上はきちんと表示してくれます。
それとも、103行目までで、104行目以下は範囲外なのでしょうか。

これは、半平太さんがご教授いただいたマクロをご指示どおりに実行しております。
まことに恐れ入りますが、今一度ご確認とご教授のほどよろしくお願いいたします。

シート1
__5__番号__担当者__行政区_氏名
__6___1____aさん___A_____山下
__7___2____bさん___C_____井上
__8___3____cさん___D_____鈴木
__9___4____cさん___D_____小川
_10___5____dさん___E_____古賀
_11___6____dさん___E_____石井
_12___7____dさん___E_____中村
_13___8____dさん___E_____遠藤
_14___9____dさん___E_____棟田★
_:
104__99____aさん___A_____斉藤

シート2
_12__番号__担当者__行政区_氏名
_13___1____aさん___A_____山下
_14___2____(2名)__________棟田★
_15___3____bさん___C_____井上
_17________(1名)
_18___4____cさん___D_____鈴木
_19___5____(2名)__________小川
_20___6____dさん___E_____古賀
_21___7___________________石井
_22___8___________________中村
_23___9___________________遠藤
_24__10____(5名)__________棟田★
_:
(右近) 2017/10/24(火) 16:38


 >それとも、103行目までで、104行目以下は範囲外なのでしょうか。
 済みませーん。この辺りは、アバウトに作りました。m(__)m

 >余裕をもたせて、100行あれば大丈夫です。
 との事だったので、現実には70、80くらいかなと踏んでいました。
 もっと余裕を取るべきでした。

 まず、Q列(POS連番)を150くらいまで下に伸ばしてください

 それから、13行目の数式の変更が必要になります。以下に変更してください。

 ※下方へのコピーは140行目辺りまでにしてください。但し、P3は60行下くらいでいいと思っています。(そんなに担当者は居ないですよね?)

 (1) A13セル =IF(OR(B13&E13="",I12=I13),"",MAX(A$12:A12)+1)
 (2) B13セル =IF(I13="","",IF(INT(I13/1000000)<>INT(N(I14)/1000000),"("&VLOOKUP(INDEX(Sheet1!B$5:B$200,MOD(I13,1000)),$J$13:$O$61,6,FALSE)&"名)",IF(INT(I13/1000000)=INT(I12/1000000),"",INDEX(Sheet1!B$5:B$200,MOD(I13,1000))&CHAR(10)&INDEX(Sheet1!C$5:C$200,MOD(I13,1000)))))
 (3) C13セル =TRIM(IF(OR(INT(N(I13)/1000)=INT(N(I12)/1000),E13=""),"",INDEX(Sheet1!D$5:D$200,MOD(I13,1000)))&" "&INDEX(Sheet1!E:E,IFERROR(MOD(I13,1000),9999)))
 (4) E13セル =IF(I$12<Q13,"",IF(I12=I13,"",INDEX(Sheet1!G$5:G$200,MOD(I13,100))))
 (5) H13セル =IF(Sheet1!B6="","",IFERROR(MATCH(Sheet1!B6,Sheet1!$B$6:$B$200,0)*1000000+MATCH(Sheet1!D6,INDEX(K$13:M$61,MATCH(Sheet1!B6,J$13:J$61,0),0),0)*1000+Q13+1,""))
 (6) I13セル =IF(I$12<Q13,"",SMALL(($H$13:$H$200,$P$13:$P$61),Q13))

 (7) P13セル =IF(N13=1,INDEX(H$13:H$111,MATCH(J13,Sheet1!B$6:B$200,0)),"")

(半平太) 2017/10/24(火) 17:29


半平太さん、ありがとうございます。
家に帰ってからやってみます。
本当に感謝いたします。
つくづく、知識の高さに関心するばかりです。
私が低過ぎですかね・・・。

(右近) 2017/10/24(火) 17:32


早速、半平太さんのご指示どおりに修正しました。
ところが、前述の★マーク改善されませんでした。
A列、B列、C列、E列、H列、I列は13〜140行目まで、P列は13〜72行目まで、Q列は13〜150行目までコピーしました。
再びマクロを実行する必要があるのですか?
よろしくお願いいたします。ありがとうございます。

(右近) 2017/10/24(火) 22:33


半平太さん、これは新規の質問であげた方がよいでしょうか。

?@シート2で、行政区とaa等の住所についてですが、これを行政区なら左寄せ、住所なら右寄せみたいに表示させることは可能でしょうか。

?Aシート1の曜日のフォントに色を付けて、シート2にもその色を反映させることは可能でしょうか。
例えば、月水金という場合に、それぞれに色を付ける場合もあります。ただ、赤と青のように最大2色しか使いません。

これらが可能になれば、自分の中で思い描く完璧な形になるのですが・・・。
別の新規で質問した方がよいでしょうか。ご指導いただけると有難いです。

(右近) 2017/10/24(火) 23:04


 >再びマクロを実行する必要があるのですか? 

 ありません。

 >(4) E13セル =IF(I$12<Q13,"",IF(I12=I13,"",INDEX(Sheet1!G$5:G$200,MOD(I13,100))))
  (4) E13セル =IF(I$12<Q13,"",IF(I12=I13,"",INDEX(Sheet1!G$5:G$200,MOD(I13,1000))))
                                       ↑
 0を一つ増やしてください(100→1000) −−−−−−−−−−−−−−−−−−−−┘
 その後、下にコピーしてください。

 H13は、キッチリ200行目迄コピーしてください。
 I13は、キッチリ200行目迄コピーしてください。
 Q列は、キッチリ200行目迄、連番を延長してください。最終は「188」となる。

 ーーーー以上で完動しない場合、ーーーーーーーーーーー

 H列13から下に不連続な空白が無いか目視してください。

 見つかった場合、その行番号と同じ位置にある「Sheet1の担当地区」が間違っていないかチェックしてください。

 1担当者の地区名は3つまでと認識しております。
 それ以外の地区記号が入力されていないかどうかチェック願います。

(半平太) 2017/10/24(火) 23:36


半平太さん、すごいです!!
E13セルの 0 を一つ増やしたことで、改善されました!!

(右近) 2017/10/24(火) 23:44


 >?@シート2で、行政区とaa等の住所についてですが、
 >これを行政区なら左寄せ、住所なら右寄せみたいに表示させることは可能でしょうか。

 別々の列なら普通にセルの書式を設定すればいいと思うのですが、そんなことではないのですか?

 >?Aシート1の曜日のフォントに色を付けて、シート2にもその色を反映させることは可能でしょうか。
 >例えば、月水金という場合に、それぞれに色を付ける場合もあります。ただ、赤と青のように最大2色しか使いません。

 書式は数式で反映できません。

 条件付き書式で同じ条件で色付けと言うようなことは出来るかも知れませんが、
 要件の詳細を確認しないと何とも言えません。

 新規で質問して、フレッシュな回答者から意見を聞いて貰った方がいいです。
 私は、現在進行している問題点の解決で手一杯です。

(半平太) 2017/10/24(火) 23:45


半平太さんのおかげで、凄い凄いデータをつくることができました。
本当に感謝しております。ここまで、説明不足や私の不備だらけにも快くご対応いただき、本当に感謝でいっぱいです。
あと二つの課題については、半平太さんがおっしゃるように、新規で質問させてもらおうと思います。

本当にありがとうございました。今後ともどうぞよろしくお願いいたします。

(右近) 2017/10/24(火) 23:54


半平太さん、今一度の質問というかご相談です。

昨日質問させていただいた以下のことについてです。

シート2で、行政区とaa等の住所についてですが、これを行政区なら左寄せ、住所なら右寄せみたいに表示させることは可能でしょうか。

この内容を変更してですね、いずれにしても左寄せにして、aaの場合は半角又は全角のスペースを空けるというのでは、関数の修正でできないでしょうか?

昨日完結の御礼を言ったばかりですが、今一度お願いいたします。

(右近) 2017/10/25(水) 20:26


 aaって住所のことだったんですか、じゃ、行政区とは同じ列ですね (-_-;)

 行政区(C列)の数式はいままで、下式だったと思うんですが、タイトル用に11行挿入したものに完全には対応できていなかったです m(__)m
              
 >C13セル =TRIM(IF(OR(INT(N(I13)/1000)=INT(N(I12)/1000),E13=""),"",INDEX(Sheet1!D$5:D$104,MOD(I13,1000)))&" "&INDEX(Sheet1!E:E,IFERROR(MOD(I13,1000),9999)))

 新数式
   C13セル =IF(OR(INT(N(I13)/1000)=INT(N(I12)/1000),E13=""),"",INDEX(Sheet1!D$5:D$200,MOD(I13,1000)))&REPT(" ",INDEX(Sheet1!E$5:E$30000,IFERROR(MOD(I13,1000),9999))<>"")&INDEX(Sheet1!E$5:E$30000,IFERROR(MOD(I13,1000),9999))

(半平太) 2017/10/25(水) 21:44


半平太さん、この数式はC列ですか??
C13の結果は、13542 と出たのですが・・・
(右近) 2017/10/25(水) 23:07

 > この数式はC列ですか?? 

 行政区は、Sheet2のC列にあると認識していますが?

 もし、そちら独自で色々と列の入替を行っている場合は、
 こちらでは何がどうなっているのか分からないので、数式は作れません。

(半平太) 2017/10/25(水) 23:39


すいません。
その後、シート1の列を入れ替えていました。
きちんと該当列に修正したところ、うまくいきました。
しかも、希望通りaaは全角スペースを空けることができました。
本当にありがとうございます。
半平太さんは、開発とかをされるお仕事なんですか?つくづく、凄いなと感心するばかりです。
今後ともどうぞよろしくお願いいたします。
(右近) 2017/10/25(水) 23:46

コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.