[[20170114101712]]　『日付が同じなら、「記念日」の文字を繋げてまとめ』（マリオ）　ページの最後に飛ぶ

『日付が同じなら、「記念日」の文字を繋げてまとめたい』（マリオ）

よろしくお願いいたします。
日付が同じなら、「記念日」の文字を繋げてまとめたい
（マクロじゃなく、セクセルの★関数を使用して）

説明）
・「D3:E12の範囲の表」→「A3:B9の範囲の表」にする。
・B3セル→★スペース文字で繋げると、●「給料日　大寒」
・B6セル→★スペース文字で繋げると、■「給料日　5月の第3金曜　N氏の誕生日」
・「A3:B9の範囲の表」は、A8を見ると、日付が昇順になってなが、★昇順でもよい。
・F列より右の列を★作業列と使用してもＯＫ

  |A         |B     |C|D          |E
01|          |      | |           |
02|日付      |記念日| |日付       |記念日
03|2017/01/20|●    | |2017/01/20 |給料日
04|2017/02/18|給料日| |2017/02/18 |給料日
05|2017/04/20|給料日| |2017/04/20 |給料日
06|2017/05/20|■    | |2017/05/20 |給料日
07|2017/06/20|給料日| |2017/06/20 |給料日
08|2017/06/18|父の日| |2017/05/20 |5月の第3金曜
09|2017/06/21|夏至  | |2017/06/18 |父の日
10|          |      | |2017/01/20 |大寒
11|          |      | |2017/06/21 |夏至
12|          |      | |2017/05/20 |N氏の誕生日

＜使用 Excel：Excel2013、使用 OS：Windows10 ＞

 もし、D:Eの表に、ある日の登録が1000個あったら なんとか かんとか あれ これ それ ・・・・　と 1000個つなぐ？？
 それを　関数で？？？

（β） 2017/01/14(土) 10:34

 専門家さんなら、素晴らしいアイデアが浮かぶんでしょうけど、素人なので力技のＵＤＦです。
 ブックを開いた後、一度、記憶させたD:E列情報は、その後、追加変更になっても反映しない欠陥商品です。
 （常にその時点での状態を反映させる方法もありますが、ちょっと重くなりますのでとりあえず）

 標準モジュールに。

 Dim dic As Object

 Function WhatDay(d As Long) As String
    Dim c As Range
    If dic Is Nothing Then
        Set dic = CreateObject("Scripting.Dictionary")
        For Each c In Range("D3", Range("D" & Rows.Count).End(xlUp))
            If Not dic.exists(c.Value2) Then Set dic(c.Value2) = CreateObject("System.Collections.ArrayList")
            dic(c.Value2).Add c.Offset(, 1).Value
        Next
    End If

    WhatDay = Join(dic(d).ToArray, " ")

 End Function

 セル側は　=WHATDAY(A3) といったように記述します。

（β） 2017/01/14(土) 10:55

 とりあえず記念日10個まで対応

 G3:=IF(A3="","",A3)
 下にコピー

 H3:=IFERROR(INDEX($E$3:$E$102,SMALL(IF($D$3:$D$102=$G3,ROW($A$1:$A$100)),COLUMN(A1))),"")
 CtrlキーとShiftキーとEnterキーを同時に押す
 Q列までコピー後、下にコピー

 R3:=TRIM(CONCATENATE(H3,"　",I3,"　",J3,"　",K3,"　",L3,"　",M3,"　",N3,"　",O3,"　",P3,"　",Q3))
 下にコピー

 B3:=IF(R3="","",R3)
 下にコピー
（bi） 2017/01/14(土) 11:04

 あいかわらず　ＵＤＦですが。

 常にその時点のものを反映させるバージョンです。

 セル側は =WHATDAY2(A3,D$3:E$16) といったように指定します。

 Function WhatDay2(d As Long, List As Range) As Variant
    Dim w As Variant
    Dim x As Long
    Dim c As Range

    ReDim w(1 To List.Rows.Count)

    For Each c In List.Columns(1).Cells
        If c.Value = d Then
            x = x + 1
            w(x) = c.Offset(, 1)
        End If
    Next

    If x > 0 Then
        ReDim Preserve w(1 To x)
        WhatDay2 = Join(w, " ")
    Else
        WhatDay2 = CVErr(xlErrNA)
    End If

 End Function

（β） 2017/01/14(土) 11:05

 ・A列、D列の日付はそれぞれ最大で何行ぐらいになるのか？
 ・D列で同じ日付は最大で何個ぐらいになるのか？

 といったことが不明ですが、、

 F列を作業列にします。

 F3 =IF(D3="","",IFERROR(LOOKUP(1,0/($D$2:D2=D3),$F$2:F2)&"　","")&E3)

 下コピー

 B3 =IF(A3="","",IFERROR(LOOKUP(1,0/($D$3:$D$100=A3),$F$3:$F$100),""))

 下コピー

 とか。
（笑） 2017/01/14(土) 11:59

 ■F列を作業列にする案その2

 F3 =IF(D3="","",E3&IFERROR("　"&VLOOKUP(D3,D4:$F$101,3,FALSE),""))
                                               ~~~~~~
 下コピー（F100までコピーする場合。波線部は「100」よりも大きな行数にすること）

 B3 =IF(A3="","",IFERROR(VLOOKUP(A3,$D$3:$F$100,3,FALSE),""))

 下コピー

 とか。
（笑） 2017/01/14(土) 12:53

 >>（β）さん
 回答ありがとうございます。
 お久しぶりです。マクロはちょっと嫌です(^^♪

 >>（bi）さん
 回答ありがとうございます。
 作業列を増やしても、
 「CtrlキーとShiftキーとEnterキーを同時に押す」処理は、
 したくありません。すいません。

（マリオ） 2017/01/14(土) 18:49

 >>（笑）さん

 エクセルで、カレンダーを作成してます。

 ■回答
 (1)A列、D列の日付はそれぞれ最大で何行ぐらいになるのか？
 800行あれば十分です。
 （理由）
 去年6日分+今年365日+閏年1日+来年365日なので、
 最大でも、6+365+1+365=737かな…？

 (2)D列で同じ日付は最大で何個ぐらいになるのか？
 最大で5つまで取り扱いで十分です。
 行の上から順に、5つめの重複までを処理すれば十分。
 6つめ以上の重複は、処理しないで結構。
 ただし、「6つ以上の重複があります」
 の警告文を指定セルに表示する。

 （理由）
 ・結合した文字列を表示するセル幅が、全角14文字ぐらいが限界だから。
 ・記念日の文字列が最低、全角2文字（例えば、大寒）と仮定すると、
 5つの文字列を全角のスペース（全角1文字）でつなげたら、
 2+1+2+1+2+1+2+1+2=14になるため、
 5つの文字列を結合した場合の最低文字数は、14文字だから。

 *******************************************************
 (3)ちなみに、A列、B列は、上に詰める必要はないです。下表を参照！
 A,B列のセル範囲を「名前の定義」でリスト化したいだけだから。

 上の(2)「（笑）さんの質問」で、ひらめきましたが、
 5つの作業列に、重複している日付の★行番号を
 Row()関数を使って、入れていけばいいのかな？
 簡単な方法としては…。

   |A         |B     |C|D          |E
 01|          |      | |           |
 02|日付      |記念日| |日付       |記念日
 03|2017/01/20|●    | |2017/01/20 |給料日
 04|2017/02/18|給料日| |2017/02/18 |給料日
 05|2017/04/20|給料日| |2017/04/20 |給料日
 06|2017/05/20|■    | |2017/05/20 |給料日
 07|2017/06/20|給料日| |2017/06/20 |給料日
 08|          |      | |2017/05/20 |5月の第3金曜
 09|2017/06/18|父の日| |2017/06/18 |父の日
 10|          |      | |2017/01/20 |大寒
 11|2017/06/21|夏至  | |2017/06/21 |夏至
 12|          |      | |2017/05/20 |N氏の誕生日

（マリオ） 2017/01/14(土) 18:52

 >>ちなみに、A列、B列は、上に詰める必要はないです

 ということは、A列も、自動作成　ということを意図してたわけですか？

 これまでに上がった回答では A列ありき　という扱いだったと思います。

（β） 2017/01/14(土) 19:57

 関数素人が口出しする場面ではありませんが

 >>6つめ以上の重複は、処理しないで結構。
 >>ただし、「6つ以上の重複があります」
 >>の警告文を指定セルに表示する。

 笑さんの方式はいくつ同じ日付があってももれなく連結する数式だと理解しています。
 ですから、警告文をセルに表示するより、B列に条件付書式を設定して色塗りすればいかがでしょう。

 文字数が 14文字以上　とか　全角スペースが４個以上といった条件で。

 ところで、A列も自動生成なら、ネットから拾ってきた数式ですが

 A3 : =IF(COUNT(D3:D1000),MIN(D3:D1000),"")
 A4 : =IFERROR(IF(A3="","",IF(A3=MAX(D$3:D$1000),"",LARGE(D$3:D$1000,RANK(A3,D$3:D$1000)-1))),"")

 A4 を下にフィルコピー。
 これで、一意の日付が昇順に並ぶようです。

（β） 2017/01/14(土) 21:04

 こんばんわ。

 >カレンダーを作成してます。

 との事なので、見た目や印刷時に見えなければ良いですか？
 後詰めたり並べ替える必要は無いとの事なので、
 作業列を使わず条件付き書式で不要なセルのフォントを白にする案です。

 A3 =D3
 B3 =IF(COUNTIF(D:D,A3)>5,"6つ以上の重複があります",E3&IFERROR(" "&OFFSET(B3,MATCH(D3,D4:D1000,0),0,1,1),""))
 下にフィルコピー

 条件付き書式でA・B列全体に設定で、
 数式 =OR($D1="",COUNTIF($A$1:$A1,$D1)>1)
 フォントの色を白

（sy） 2017/01/15(日) 00:06

 失礼します。

 皆さんの域に到達するには、あと100年ほどかかると思いますが、１つ下の同じ日付のものと連結することで
 結果的に、最初に現れるその日付に対して、1000個でも2000個でも、関数で、さらっと連結できる！

 力技のゴリゴリコードのプログラム屋にとって、発想というか、着眼点、脱帽です。

 ところで、私がアップした式の A3。　D列の日付が全くなく空白ということはないだろうと思いますので
 単に、=MIN(D3:D1000) でよかったですね。

（β） 2017/01/15(日) 08:31

 >（笑）さん
 「2017/01/14(土) 11:59」の回答、完璧です。ありがとうございます。
 βさん、biさん、syさん、回答ありがとうございました。

 ----------------------------------------------------------------
 ■数式でないのは、D列、E列、「A2セルの日付」、「B2セルの記念日」
 最終的に、このようになりました。

     |[A]       |[B]                              |[C]|[D]       |[E]         
 [1] |          |                                 |   |          |            
 [2] |日付      |記念日                           |   |日付      |記念日      
 [3] |          |                                 |   |2017/01/20|給料日      
 [4] |2017/02/18|給料日                           |   |2017/02/18|給料日      
 [5] |2017/04/20|給料日                           |   |2017/04/20|給料日      
 [6] |          |                                 |   |2017/05/20|給料日      
 [7] |2017/06/20|給料日                           |   |2017/06/20|給料日      
 [8] |          |                                 |   |2017/05/20|5月の第3金曜
 [9] |2017/06/18|父の日                           |   |2017/06/18|父の日      
 [10]|2017/01/20|給料日　大寒                     |   |2017/01/20|大寒        
 [11]|2017/06/21|夏至                             |   |2017/06/21|夏至        
 [12]|2017/05/20|給料日　5月の第3金曜　N氏の誕生日|   |2017/05/20|N氏の誕生日 
 ----------------------------------------------------------------
     |[F]                              |[G]
 [1] |重複あり                         | 12
 [2] |                                 |   
 [3] |給料日                           |  2
 [4] |給料日                           |  1
 [5] |給料日                           |  1
 [6] |給料日                           |  3
 [7] |給料日                           |  1
 [8] |給料日　5月の第3金曜             |  2
 [9] |父の日                           |  1
 [10]|給料日　大寒                     |  1
 [11]|夏至                             |  1
 [12]|給料日　5月の第3金曜　N氏の誕生日|  1
 ----------------------------------------------------------------
 ■G1式（D列の最終行を求めます）、F1式（※INDIRECTを使用！）を入力
 G1=LOOKUP(1,0/(D:D<>""),ROW(D:D))
 F1=IF(COUNTIF($G$3:INDIRECT("G"&$G$1),">=2")>0,"重複あり","重複なし")
 ----------------------------------------------------------------
 ■次に、G3式（※INDIRECTを使用！）、F3式、B3式（※INDIRECTを使用！）、
    A3式を入れてから、それぞれ最終行までフィルコピー
 G3=COUNTIF($D3:INDIRECT("D"&$G$1),D3)
 F3=IF(D3="","",IFERROR(LOOKUP(1,0/($D$2:D2=D3),$F$2:F2)&"　","")&E3)
 B3=IF(G3<>1,"",IFERROR(LOOKUP(1,0/($D$3:INDIRECT("D"&$G$1)=A3),$F$3:INDIRECT("F"&$G$1)),""))
 A3=IF(G3<>1,"",D3)
 ----------------------------------------------------------------
 ■最後に、条件付き書式の設定
 （その１）
 選択するセル範囲は、G列、A列、B列　★=$A:$A,$B:$B,$G:$G
 式は、★=AND($G1<>1,ROW($G1)>=3,ISNUMBER($G1))
 書式は、セルを赤く塗りつぶす

 （その2）
 選択するセル範囲は、★=$F$1
 式は、★=$F$1="重複あり"
 書式は、セルを赤く塗りつぶす
 ----------------------------------------------------------------

（マリオ） 2017/01/18(水) 19:09

コメント返信：

[ 一覧(最新更新順) ]

[[20170114101712]] 『日付が同じなら、「記念日」の文字を繋げてまとめ』（マリオ） ページの最後に飛ぶ

[[20170114101712]]　『日付が同じなら、「記念日」の文字を繋げてまとめ』（マリオ）　ページの最後に飛ぶ