はもちくわ

コードについて自分なりの解釈を書いてます。

【ExcelVBA】オートシェイプとフォームコントロールでつくるリストボックスウインドウ（ユーザーフォーム不使用）

 

Excelのユーザーフォームを使わないで、フォームコントロールとオートシェイプを使って、データを選択するリストボックスを作っていきます。

今回、サンプルで使うデータは次のような、今の野菜相場データです。

このデータはあくまでサンプル用です。
皆さんは、業務で使う実際のデータの形を想像しながら見ていただければと思います。

はい、最初の一手は、データの横にフォームコントロールボタンを配置してください。ActiveXじゃない方ですよ。

次に、配置したボタンが隠れるように、リストボックスウインドウを作成します。
サンプルでは次のようにしました。

デザインは皆さん好き好きで作ってください。もっと影の距離伸ばしたり、立体的にしたりと……。普通のオートシェイプと同じように扱って大丈夫です。

このサンプルを作る上で、やって頂きたい事は、「オブジェクトの選択と表示」の一覧から、オブジェクトの名前の変更です。
変更するのは、マクロ（VBA）を登録したり、マクロから動かしたい図形だけで大丈夫です。先ほど、ウインドウの下に作ったボタンも、ここで名前変更お願いします。その他の動かさない図形は変更しなくても大丈夫です。

この変更しなくてはいけない理由なのですが、 過去記事でも触れましたが、勝手に割り振られた図形の名前は、ファイルのコピーなどによって、名前が割り振り直される事があります。
図形が少なければ振り直されても、偶然一致するかもしれませんが、このように多い場合はそうはいきません。
なので、VBAのコード上で図形を名前で指定するような場合、名前が変更されないように、先に名前をつけておきます。ユーザーがつけた名前は、割り振りはされませんからね。

形が決まったら、ウインドウに使っている図形全部をまとめてグループ化しましょう。そして、グループにも名前をつけてください。
グループの中にさらにグループを作りたい方は、先に小さいグループを作っておいて、最後にまとめてグループ化すればよいでしょう。
上のスクショでは、矢印の上にテキストボックスが乗っている状態です。ここは、テキストボックスの方が大きくて上にあるので、選択されるのはテキストボックスになります。そのため、名前はテキストボックスだけ変更してもらえれば大丈夫です。
スクショをよく見ると、実は図形の矢印も名前が変更されています。これは私が最初にサンプルを作成した際に、テキスト無しの矢印だけで作成したんです。ですが、ちょっとわかりにくいかな…と思いましたので、テキストをあとから足したというわけです。その名残りです（笑）
また、図形につける名前ですが、あとあとVBAのコード上で入力するので、できればアルファベットが良いと思います。
もちろん、カナ、かな、漢字でも全く問題ないですが、ごくまれに、VBA上で予想外のことが起こることもあって、日本語はちょっとやっかい（特にMacは）なので、そのあたりは、好みにあわせてやってください。WindowsのVBAに限って言えば、日本語変数だってありです。（Macは論外ですが（笑））



 

それでは、このリストボックスでどんな動きをさせるのかを説明していきます。
まず、最初に作ったボタンを押すと、データが入ったシート上に、先ほど作ったリストボックスウインドウが開き、上の野菜リストボックスに一覧が表示されます。

上の野菜リストボックスの中から、１つデータを選択し、追加矢印ボタンを押すと、選択されたリストが下の選択リストボックスに移ります。

逆に、下から上のリストボックスへ移動するには、下の選択リストボックスで１つデータを選んで、削除矢印ボタンで上に移動します。

ここで、1つ注意点です。
以前の記事でも書いてますが、このフォームコントロールのリストボックスは、残念ながら複数選択が使えません。
ユーザーフォーム、AcitveXは使えるんですけどね〜。
コントロールの書式の中に複数選択はあるし、選択だけはできるようになるんです。
でも、選択したリストはデータとして扱う方法がありません。選んだリスト番号が拾えないのです。ひょっとしたら扱える人が世界のどこかにいるかもしれません。私は無理でした。もし、使える人いらっしゃいましたら、ぜひコメントで教えてください！

そして、生産地コンボボックスで生産地を選ぶと、上の野菜リストボックスで生産地フィルターをかけられます。このフィルターは下の選択ボックスには連動しないので、先に選んでいる別の生産地の野菜は消えません。

そして、全選択ボタンは、野菜リストボックスに表示されているもの全部が選択されて下に移動します。

当然、すべて表示していれば全部移動します。
同様に、全削除ボタンは、下から上のリストボックスにデータが移ります。生産地フィルターかかっている場合は、フィルターが適用されて表示されますので、フィルター外のものは表示されません。

もちろん、消えたわけではないので、生産地フィルータを変えるか、外せば、野菜リストボックスに再度表示されます。
そして、下の選択リストボックスにデータが表示されている時に、決定ボタンを押すと、メッセージウインドウに選択したデータが表示されます。

これはサンプルなので、意味のないメッセージウインドウ表示になってますが、実運用にする場合、この結果を、書類作成にしたり、なにか別のソフト用にデータをエクスポートしたりなど、業務に合わせて使っていただければ……と、ちょっとは使える？！かなと思います。



さて、どんなコードで動いているか…ですが、その前に、どういう仕組みでリストボックスが制御されているか説明していきます。

動いているウィンドウを見ると、リストボックスに表示されているデータが、実際に移動して、動いているよう見えますが、実際はそうではなく、リストボックスの表示は、各操作のたび、毎回全部消え、再度データを表示しているだけです。
動いているのは、１つのデータテーブルの表示判定カラム（列）の内容だけなのです。
なので、画面のリストボックスウィンドウは、各ボタンがデータ操作用コントローラー、ウインドウが結果表示用の画面というのが一番しっくりきます。
判定用のカラムが動くと言いましたが、実際どうなっているかと言いますと、０がデータ一覧リストボックス（上側）に表示、１が選択リストボックス（下側）に表示、2がフィルター外の非表示、となっています。これをボタンによって変化させているだけです。図にするとこんな感じですかね。

なので、大きなコードの流れとしては、
リスト操作→リスト全クリア→リスト再表示
が繰り返されるだけです。

 

それでは、順番にコードを見ていきます。

まずは環境整備のための前準備します。
関数のスコープ（有効範囲）の対策や、画面や自動計算、イベントの停止などプロシージャが動くごとに必要になるものを先にまとめます。
関数スコープの話は、関数をどこで宣言するのか？という過去記事もありますので、なんのことかわからない方は、あとで見てみてください。
簡単にいうと、普通は、コードのまとまりごとにしか使えない関数を、コードをまたいで使えるように準備しましょう！ってことです。

今回、リストを操作するデータは、シートデータを２次元配列に入れて使っています。
配列ではなく、シートのデータをそのまま使うことも可能ですが、今回は詳しく書きません。のちほど、さらっと触れています。

それでは、変数をプロジェクト内すべてで使えるように宣言していきます。今回コードを書くモジュールの一番上に書いてもらえればよいです。

Public item_list() As Variant, l_sheet As Worksheet, area() As Variant

パブリック宣言で使う変数は、リスト操作のためのデータリスト用の動的配列、データ一覧を格納するワークシート、産地リスト用の動的配列の３つです。

次は、VBA定番の画面更新、自動計算、イベントの操作用です。

Sub sc_update(ck As Boolean)
    '画面の更新、自動計算、イベントの一時中止と再開
    'Falseで一時中止、Trueで再開
With Application
    .ScreenUpdating = ck
    .EnableEvents = ck
    If ck=False Then
       .Calculation = xlCalculationManual’手動計算
    Else
       .Calculation = xlCalculationAutomatic'自動計算
    End If
End With
End Sub

今回のサンプルではあまり多用しないので、別に準備しなくてもよかったなと思ってます（笑）
それでも、このコードはVBAで何かしら作成している方は、本当によく使うと思います。
使ったことなくて、処理速度で困っている人は、ひとまず、コードの初めにFalse、コードの終わりにTrueで使ってみて！
あとですね、これ、Funcitonにしてないですが、引数を渡せるプロシージャにしてます。このようにプロシージャでも、カッコ内に引数の変数を宣言すれば、Functionみたいに受けた引数で処理ができるようになります。

 

次はパブリック宣言した２次元配列にデータを格納します。

Sub list_load()
Dim i As Long, co As Long, ro As Long
Dim j As Long, fl As Boolean

'シートにあるデータの列数をカウント
i = 1
Do While l_sheet.Cells(1, i) <> ""
    i = i + 1
Loop

'Do~Loopを１で抜けてくるのはループ回ってない＝データ無し
If i = 1 Then
    MsgBox "リストデータが見当たりません", , "データエラー"
    Exit Sub'処理中断
End If

'+1で抜けるので−１して配列カラム数を登録
'配列は０スタートなので列数としては＋１
co = i-1

i = 2'シートデータの行数はじまり
ro = 0'野菜データ配列の列数
a_ro = 0'産地データ配列の列数
'産地動的配列〜使う前に数指定（リセット）
ReDim area(0)
'野菜動的配列〜使う前に数指定（リセット）
ReDim item_list(co,0)

'シートデータをループで下方向にチェックしながら配列に格納
Do While l_sheet.Cells(i, 1) <> ""
    
    ReDim Preserve item_list(co, ro)'野菜動的配列の列追加処理（データ維持）
    'リストを横に移動しながら配列に格納
    For j = 0 To co - 1
        item_list(j, ro) = l_sheet.Cells(i, j + 1)
    Next j
        '配列カラムの最後はリスト表示判定を入れる
        item_list(co, ro) = 0 'リスト表示にしておく
    ro = ro + 1'野菜配列の列をカウントアップ
    
    fl = False'同じ産地の判定用スイッチ
    '産地配列の中とシートデータの配列を比較して同じものがないかチェック
    For j = 0 To UBound(area)
        If area(j) = l_sheet.Cells(i, 2) Then
        　　'同じものがあったらスイッチONにしてループ抜ける
            fl = True
            Exit For
        End If
    Next j
    '産地がデータになかったら処理
    If fl = False Then
        a_ro = a_ro + 1'産地配列の列数を増やす（１から代入）
        ReDim Preserve area(a_ro)'産地動的配列の列追加処理
        area(a_ro) = l_sheet.Cells(i, 2)'データ追加（1つ目はわざと空白）
    End If
    '次のシートデータへ
    i = i + 1
Loop

End Sub

これでリスト一覧が配列に格納されました。

配列は慣れないとやっかいに感じるかもしれませんが、２次元配列は仮想ワークシートとして処理速度を上げるのに使ったりと、けっこう応用できるので、ぜひ慣れて頂きたいな〜と思います。
そう言いながら、私、上手に配列を説明する自信がないので、詳しく説明はやめておきます。
申し訳ないですが、ご自身で勉強してください（笑）

ここでは、２次元配列を仮想シートと考えたとき、ちょっとわからなくなる点だけ、さらっといきます。

２次元配列を仮想シートと考えると、データの位置を表す時、コード上では、
「配列名（列数、行数）」
と書きます。
ワークシートのセルの位置を指定する時は、
「Worksheet(n).Cell(行数、列数）」
と、行と列が反対になります。
この違いがわからなくなるんですよね。

この原因は、VBAの多次元の動的配列の特性にあります。それは、「最後の次元しか数を増やせない」という特性です。
この配列の特性の中で、仮想ワークシートを表現する訳です。

では、ワークシートの方をみてみましょう。
今回の野菜データサンプルです。

どうでしょうか？データが増える可能性って縦方向か横方向かどちらだと思います？


そうですよね？縦方向の方が可能性高いですよね。
野菜の種類が増える、産地が増える、価格が変わる、単位が変わる、どの場合も縦方向にデータが増えていきます。データベースもそうですけど、レコード（行）は増えても、あまり項目（カラム）って増えないですよね。
産地に項目として市が加わるとか、全く可能性がないとは言いませんが、データの形が決まっている場合、なかなか形を変えるってないですよね。

この配列とデータ、2つの特性をすりあわせて考えると、２次元配列は２次元目が増える、データは行側が増える、、、、ね、、、行、列が反対になるというわけですね。
なので、配列は「項目数、レコード数」と書くって、覚えちゃうしかない（笑）です。
そして、もう１点。
ワークシートのセルの左上端は「１」です。「Cell」で座標を指定するのも、「１、１」始まりですね。これ、配列は基本「０、０」始まりです。1つずれます。まあ、１スタートにもできますが、1つずれるよって覚えておいた方が早いです（笑）

あと、これもVBA特有のルールなんですが、動的配列の数を変更したいときは、変更する数を先に「宣言」しなくてはいけません。宣言の方法は、上のコード内にもありますが、サイズ変更ができる「ReDim」をつかいます。
ただですね、これそのまま「ReDim」だけ使うと、サイズ変更と同時にデータも初期化しちゃいます。
つまり、配列に入っていたデータがすべて消えるということです。
なので、データを維持したままサイズ変更したいときは、「Redim Preserve」を使います。



はい、つぎはいよいよウインドウの動きを作っていきます。
まず、データ一覧をリストボックスに表示させるものを作ります。これは追加、削除どちらの操作でも使うパーツになります。

Sub list_set()
Dim li_name As Variant
Dim i As Long, co As Long, ro As Long

'ウインドウの名前を数字で変更できるように配列にする
li_name = Array("item_list", "select_list")
'表示判定のカラム番号を得る
co = UBound(item_list, 1)

'2つのリストボックスの内容を全て消す
With l_sheet
    .Shapes("item_list").ControlFormat.RemoveAllItems
    .Shapes("select_list").ControlFormat.RemoveAllItems
End With

'判定用の数字と表示ウインドウをあわせてリスト表示する
For i = 0 To UBound(item_list, 2)
    If item_list(co, i) <> 2 Then'２は表示しない
    With l_sheet.Shapes(li_name(item_list(co, i))).ControlFormat
        .AddItem item_list(0, i) & "  ---  " & item_list(1, i) & "  ---  " & item_list(5, i)
    End With
    End If
Next i

End Sub

フォームコントロールのリストボックスを操作するのは、上のコードのように
Worksheet(n).Shapes(名前).ControlFormat.
とします。これでオブジェクトを取得できるわけです。ここから派生するプロパティを使って操作していくわけです。コントロールの書式ウインドウで、できる範囲のこと操作可能です。
上のコード内では、「RemoveAllItems」でリストをクリア、「AddItem」をループさせてリストを１行ずつ登録してます。

ちょっと、変わったことしているのは、リストボックスの名前の使い方ですかね。
これは、配列にリストボックスの名前をいれると、自動的に1つ目に「０」が、2つ目に「１」の要素数が割り当てられます。この数字を、リスト表示判定用の数字とリンクさせたという訳です。こうすることで、ループ内ででてきた判定用の数字がそのまま使えるようになっています。
あと、これ、単体で動かすとエラー出ます。ワークシート変数の中が空っぽになってます。さきに動作確認するときは、変数「l_sheet」にワークシートを代入してください。くわしくは次に出てきます。

 

それでは、次は、ウインドウを開いたり、閉じたりをさせます。
まず、開きます。

Sub window_open()
'画面の更新など一時停止
Call sc_update(False)

'関数にワークシートを代入
Set l_sheet = ThisWorkbook.Worksheets("データ一覧")
'ワークシート内の図形の編集を無効にする
l_sheet.Protect DrawingObjects:=True

'データを配列に格納
Call list_load

'産地コンボボックスをリセット
With l_sheet
    .Shapes("p_area").ControlFormat.RemoveAllItems
End With

'産地コンボボックスのリストを登録
For i = 0 To UBound(area)
    l_sheet.Shapes("p_area").ControlFormat.AddItem area(i)
Next i

'ウインドウを表示する
l_sheet.Shapes("select_window").Visible = True

'リスト表示
Call list_set

End Sub

ウインドウを開く前に準備してきたプロシージャーをここで一気に使っています。
「Call」で始まるコードの箇所ですね。画面を一時停止、データを配列に格納、リスト表示がそうです。

そして、変数「l_sheet」にワークシートを代入してます。ワークシートは様々な情報の集まりであるオブジェクトになります。そのため、代入するには、「Set」を代入式の前につけないといけません。
そのあと、オートシェイプの図形にプロテクトかけてます。これは一回かけてしまえば、コードが止まっても状態は維持されます。イベントの停止なども同様です。復帰にはリボンから操作するか、コード上で復帰させなくてはいけません。
このワークシートの代入とオートシェイプのガードの実行タイミングですが、ここではこの仕組みが動き始めるウインドウのオープン動作に入れますが、ワークシートかワークブックのOpenとかActiveとかのイベントにおいてもよいかもしれません。

次にウインドウを閉じます。

Sub window_close()

'リストボックスとコンボボックスをリセット
With l_sheet
    .Shapes("item_list").ControlFormat.RemoveAllItems
    .Shapes("select_list").ControlFormat.RemoveAllItems
    .Shapes("p_area").ControlFormat.RemoveAllItems
    .Shapes("select_window").Visible = False'ウインドウ非表示
End With

'画面の更新の再開
Call sc_update(True)

End Sub

消すだけですので、シンプルですね。
リストボックスとコンボボックスの値をリセットして、ウインドウを隠すだけです。イベントも最初に止めてますので、最後に再開させて終了です。

実は、オープンの時も、最初にリストボックスとコンボボックスの値をリセットしてます。前の値が残らないようにする処置なのですが、この処理が走るなら、ウインドウが閉じるとき、リストボックスの値は消さなくても、この仕組み上問題ありません。
それでもですね、VBAを使わない状態でウインドウを編集したりするなど、意外とコード以外でもリストボックスを見る機会があります。値が残っていると、いろいろ余計なリスクがありますので、きれいにして終わる方が良いと思います。



次は、全追加ボタン処理です。

Sub all_item_add()
Dim i As Long, co As Long

'配列内の１次元目の最大数を得る（判定カラム）
co = UBound(item_list, 1)

For i = 0 To UBound(item_list, 2)
　　'ループさせながら判定が０のリストを１に変える
    If item_list(co, i) = 0 Then item_list(co, i) = 1
Next i

'リスト表示
Call list_set

End Sub

もう表示させるコードは書いてますので、リストの判定の数字だけをループで調べて変更するだけです。
フィルターを設置してますので、表示されている「０」だけを「１」にしてます。こうすることで「２」は「２」のままになり、フィルターがかかったままになるわけです。
フィルターがなければ無条件で全部「１」にすればよいです。簡単ですね。
あと、最初に判定するカラム（列）を見つけるために「UBound」で配列の上限要素数、つまりアッパー（U）を調べてます。要素というのは、まあ、配列枠の枠番みたいなものです。なので、配列の中で一番大きな枠番は何番ですか？って聞いてます。関数名のあと、カンマ区切りで指定する数字は、次元数です。仮想ワークシートとして考えると、１次元目は列数ですね。なので、ループのFor文の上限は「２」になってますので、仮想ワークシートなら「行数」を調べている事になります。データ総数ですね〜。

このサンプルでは、判定カラムの場所は調べなくても、自分で設定して７列目にあることはわかってます。配列の要素（枠番）は０スタートですから、ベタ打ちで「６」としてループ内で判定してもよいのですが、、、まあ、こうしておけば、判定カラムが常に最後のカラムというルールさえあれば、カラム（項目）数が増えてもそのまま対応できる なあって、だけです。
この「UBound」ですが、VBAはやたら使います。配列を全部ループさせるときの上限しらべたり、要素数を先に得たりと、、、で逆に「LBound」で下限（ロウL）も調べられます。コードにあわせて配列の開始要素数を変えたりすることもあるので、下限も得る機会はでてきます。私はほとんど使わないですが（笑）

はい、それでは、次いきます。
順番からすると、全削除、、、ですが、全削除は、全追加と違って動作後の産地フィルターの影響を考えなくてはなりません。
全追加は、フィルターがかかっているもの（結果）について確定されるので、動作後にフィルターが影響することはありませんでした。
ですが、削除の場合は、フィルターがかかっているものがすでに選択した中にある場合、そのまま戻すと設定されているフィルターが無視されている状態になります。
なので、フィルター処理から作っていきます。

Sub area_filter()
Dim i As Long, co As Long, li_cnt As Long, li_no As Long

'産地コンボボックスで選択されているリスト番号を得る
li_cnt = 0
With l_sheet
  li_no = .Shapes("p_area").ControlFormat.ListIndex
End With

'産地の選択ない（０）場合はリスト番号を１（空白）にする
'リスト作成時に１行目にわざと空白いれてます。
If li_no = 0 Then li_no = 1

'判定カラムを調べる
co = UBound(item_list, 1)

For i = 0 To UBound(item_list, 2)
    If area(li_no - 1) = "" Then
    　　'空白ならフィルー解除（２→０）
        If item_list(co, i) = 2 Then item_list(co, i) = 0
    ElseIf item_list(1, i) = area(li_no - 1) Then
    　　'産地が一致ならフィルター解除（２→０）
        If item_list(co, i) = 2 Then item_list(co, i) = 0
    Else
    　　'産地が不一致ならフィルター設定（０→２）
        If item_list(co, i) = 0 Then item_list(co, i) = 2
    End If
Next i

'リスト表示
Call list_set

End Sub

リスト表示、大活躍ですよね。同じ処理は分けておくと楽になることが、よくわかるかと思います。
ループ内の処理については特に解説なしで良さそうですね。
なので、リストボックスの使い勝手の悪さ（笑）を解説します。
見た目にリストを選択できて、ユーザービリティの面から言えば、とても親切でわかりやすくなりますよね。
でも、作成する側からすれば、もうちょっとなんとかならんのか？！という面もあります。私だけ感じてるだけかもしれませんが、、、、。
その１つがこの上のコード中にもあります。
コンボボックスで選択されているリスト番号をゲットしている箇所ありますね。
「li_no = .Shapes("p_area").ControlFormat.ListIndex」
これっす。
これ、値はわかなんないんすよ（笑）
なので、上のコード内では、配列とリスト一覧をリンクさせておき、選ばれた番号から、値がすぐにわかるように作ってます。
せめてさ〜、1つでもいいからリストを判別できる値がわかるようにならんかね。
そうすれば、判別IDをリストにいれておいて、あとでデータベースの中をループさせて、選ばれたIDに該当するデータを探すみたいなことができるようになります。

言っても、できないので、リストに対応する一覧データをつねに持つようにしましょう。シート上でやろうと思ったら、それこそ別シートつくたり、列つくったりが必要です。ああ、使い勝手は悪い〜。

「表計算ソフトだからさ。そういうのはAccessとかSQL serverでやってよ　┐(´д｀)┌ﾔﾚﾔﾚ」

とか言ってそう（笑）

 

次は全削除ボタン処理いきます。

Sub all_item_del()
Dim i As Long, co As Long

'判定カラムを調べる
co = UBound(item_list, 1)

For i = 0 To UBound(item_list, 2)
　　'ループさせながら判定が１のリストを０に変える
    If item_list(co, i) = 1 Then item_list(co, i) = 0
Next i

'フィルター処理を実行
Call area_filter

End Sub

今度は最後がリスト表示でななく、フィルター処理になってます。これは先ほど説明したとおり、下のリストから上のリストに戻す時、フィルターがかかっていた場合、フィルターを優先させるためですね。リスト表示のフローはフィルターの最後にも入ってますので、この中ではフィルターだけ実行すればよいことになります。その他は、全選択とリスト判定が逆になるだけでフローは一緒です。

 

次、選択されたリストを追加する処理をみてみます。

Sub item_add()
Dim i As Long, co As Long, li_cnt As Long, li_no As Long

'野菜リストボックスで選択されているリスト番号を得る
li_cnt = 0
With l_sheet
  li_no = .Shapes("item_list").ControlFormat.ListIndex
End With

'リストが選択されてなかったら処理中断
If li_no = 0 Then
    MsgBox "リストが選択されていません", , "リスト選択エラー"
    Exit Sub
End If

'判定カラムを調べる
co = UBound(item_list, 1)


li_cnt = 0
For i = 0 To UBound(item_list, 2)
　　'表示判定が０のリストだけカウントする
    If item_list(co, i) = 0 Then
        li_cnt = li_cnt + 1
        'リストカウントと０カウントが一緒なら選択データである
        If li_no = li_cnt Then
        　　'表示判定を１にする
            item_list(co, i) = 1
            'ループ抜ける
            Exit For
        End If
    End If
Next i

'リスト表示
Call list_set

End Sub

ここのポイントはリストボックスで選択されているリスト番号をどうやってデータ一覧の番号と一致させるか？ですね。
先ほど書きましたが、リストボックスから得られる情報は、「選択されたデータはリストの中で何番目なのか？」だけです。
産地コンボボックスの時は、表示リストと配列リストは一致していました。なので、順番も同じですからすぐに選択データを導きだせました。
でも、このサンプルの野菜データ一覧は、2つのリストに分けて表示するし、フィルターがかかれば表示されないリストも出てきます。つまり、リストが３つに分かれます。そして、ユーザーの選択によって状態はコロコロ変わります。
そうなると、リストボックスから得られる情報は、データ一覧の全体の順番の中では使えない、意味が無いものになります。
そう、「全体の順番」では意味がない。

「選択されたリストでの順番」つまり表示判定に使っている、同じ番号を数えていけばいいわけです。
ここでは、上のリストに表示される判定番号は「０」です。なので、コード中も「０」だけ数えて、リストインデックスと一致したら、ループ抜けるわけですね〜。
ちょっとわかりにくいですかね？
リスト表示を想像していただいたらよいと思います。毎回表示するときは、一回全部消して、リストを上から表示判定をみて順番に表示させてますよね？

 

はい、次は選択されたリストを削除します。

Sub item_del()
Dim i As Long, co As Long, li_cnt As Long, li_no As Long

'選択リストボックスで選択されているリスト番号を得る
li_cnt = 0
With l_sheet
  li_no = .Shapes("select_list").ControlFormat.ListIndex
End With

'リストが選択されてなかったら処理中断
If li_no = 0 Then
    MsgBox "リストが選択されていません", , "リスト選択エラー"
    Exit Sub
End If

'判定カラムを調べる
co = UBound(item_list, 1)

li_cnt = 0
For i = 0 To UBound(item_list, 2)
　　'表示判定が１のリストだけカウントする
    If item_list(co, i) = 1 Then
        li_cnt = li_cnt + 1
        'リストカウントと１カウントが一緒なら選択データである
        If li_no = li_cnt Then
        　　'表示判定を０にする　
            item_list(co, i) = 0
            'ループ抜ける
            Exit For
        End If
    End If
Next i

'フィルター処理を実行
Call area_filter

End Sub

全選択と全削除の関係と一緒で、選択リストを追加する、削除するも、判定と最後のCallで呼び出すプロシージャがリスト表示かフィルター処理 かの違いで、フロー自体は一緒ですね。

はい、これでコードは最後です。
決定ボタン処理です。

Sub result_item()
Dim i As Long, co As Long, re_come As String
Dim j As Long

'判定カラムを調べる
co = UBound(item_list, 1)

For i = 0 To UBound(item_list, 2)
    '表示判定が１のリストだけ探す
    If item_list(co, i) = 1 Then
    　　'表示判定が１だったら、コメント用変数にデータを入れる
        For j = 0 To co - 1
            re_come = re_come & item_list(j, i)
            If j < co - 2 Then
                re_come = re_come & " -- "
            Else
                re_come = re_come & vbCrLf
            End If
        Next j
    End If
Next i
'コメント表示
MsgBox re_come, , "選択結果"

End Sub

最後はメッセージボックスを出すだけの、なんだこりゃの処理ですが、確定されたデータの抽出方法だけ見てもらえればと思います。
選択されたリストの追加、削除と同様なので、あえて説明は不要だとおもいますが、決定ボタンを押された時、どのリストが選択されているのかを判定する方法は、判定カラムの数字が１になっているものを探せばいいということになります。

あとは、一番最初につくったオートシェイプとフォームコントロールにマクロを登録して完了です！
登録は画面をみながらできるので省略しますが、解説が必要な方はコメントください。記事に追加します。
ひとつだけ気をつけておきたいことですが、マクロをすべて登録し終わったら、手動でウインドウを見えなくしてください。
オブジェクトの選択から目のマークを閉じてもらえればよいです。
このサンプルコードの起点は、ウインドウオープンになってます。そのため、起点コードから始まらないと、エラーが鬼のように出ます（笑）
そう言った理由で、ウインドウを出しっぱなしでファイルを保存して閉じた場合も同じことが起きます（笑）
回避したい場合、ワークシート、ブックのイベントで強制的にウインドウを閉じるコードを入れるか、どこから始めてもエラーがなくなるように、ワークシートの変数設定、配列リスト作成の処理をすべての動作起点のプロシージャの最初に持ってきて、先に実行させてください。それでいけると思います。



ここから、おまけです。
配列を使わずに、ワークシートのデータを直接リストボックスへ表示した場合のコードをさらっと紹介します。
この方法は処理速度も落ちますし、判定用の列（カラム）を作らなくてなりません。また、このサンプルの産地データのように、リスト用のリストが必要な場合、別の場所に、そのリストを作らなくてはなりません。
それでも、配列より視覚でリストを見ながら作れるので、理解は１００倍しやすいとは思います。
それでは、リスト表示するプロシージャを使って、配列じゃないパターンで書いていきます。
リスト表示用の判定カラムは列の最後に作成済みという仮定です。

Sub list_set()
Dim li_name As Variant
Dim i As Long, co As Long, ro As Long

'ウインドウの名前を数字で変更できるように配列にする
li_name = Array("item_list", "select_list")
'表示判定のカラム番号を得る
co = UBound(item_list, 1)

'2つのリストボックスの内容を全て消す
With l_sheet
    .Shapes("item_list").ControlFormat.RemoveAllItems
    .Shapes("select_list").ControlFormat.RemoveAllItems
End With

'判定用の数字と表示ウインドウをあわせてリスト表示する
i=2
Do While l_sheet.Cells(i,1)<>""
    If l_sheet.Cells(i,7) <> 2 Then'２は表示しない
    With l_sheet.Shapes(li_name(Val(l_sheet.Cells(i,7).Value))).ControlFormat
        .AddItem l_sheet.Cells(i,1) & "  ---  " & l_sheet.Cells(i,2) & "  ---  " & l_sheet.Cells(i,6)
    End With
    End If
    i=i+1
Loop

End Sub

こんな感じですかね。
あとは各ボタンによって表示判定用の数字をワークシート上で直に変更するようにすれば、同じように動くと思います。
ただ、産地コンボボックスは、別の場所にリストつくらないとダメですよ。リストは手で作ってもいいですし、VBAで自動化してもいいです。



それでは今回はここまでです。
ながながとありがとうございます。

【JavaScript】計算中ウインドウの実装に困った話（setTimeoutとかコールバックとか）

ナンバーズ３の当選番号を暦別に集計して、それを過去に遡って検証して、結果を表示するサイトを現在作っているのですが、計算の種類、量が膨大で、指定した日付の集計結果を出すだけで５秒ぐらいかかっちゃうんです（笑）
パッパッとページを見ていて、画面がフリーズしていたら、事前情報がなければ、フリーズしていることすら気づかず流れていってしまう（笑）
そんなわけで、「処理中・・・」みたいな表示をつけたいなと思い立ったわけです。

自己流で関数処理の最初と最後にclassListのtoggleつかって表示、非表示をつけてみたり、関数を分けて、別の関数でまとめて実行させてみたりと、試してみたのですが、どうやってもできなくて。

 

で、いつもは、解決しなかった・・・という話が多い私ですが、今回は、なんとか解決できたんです。
でも、結構たどり着くまでが大変で（笑）
そんな同じ境遇の方が一人でもいて、この記事見てすぐに答えに辿り着ければ幸いです（笑）

まず、処理中のグルグルとかアニメーションとかコメントとかを出したいと、ネット情報をみて回ると、大抵、「setTimeout」とか非同期処理「Promise」とか「コールバック関数」とかにあたります。
初めて知ったのですが、JavaScriptは１つのfunctionが走っていると処理が完了するまで、他は動かないんですね〜。少しずつ処理された箇所が動くみたいなことも全くない。
だから、functionの中の冒頭に表示をおいて、最後に非表示をおくと、結局処理が完了した後に結果だけが残るから非表示だけになって、表示すらされない。
そこで、「setTimeout」で割り込んでみたり、表示を先にさせて、時間のかかる処理の方の開始を遅らせてみたり・・・・全然できません。「Promise」を使って処理が完了したら実行っていうコードも試してみたけどだめで。
調べたもの全部使って、コードを書く順番、functionの実行方法の変更・・・。もう計算方法自体を変えるかぁ・・・・と、諦めかけたとき、救世主がQiitaにいました（笑）

Qiita[JavaScript] 画面の再描画を待つ・画面を強制的に再描画する

救世主によると、「時間のかかる計算処理をするとき、事前にDOMを変更しても反映されないことがある」「setTimeout()を０にしても効かないことがある」とのこと。


それそれ！！今の私よ！！

というわけで、この方のおかげで実装できました。
いや〜。大変だった。
処理中の待機表示のサイトはたくさんあるけど、HTMLの要素をCSSで表示する、しないとか、ウィンドウのデザインやら、読み込みが終わったら表示を消す方法とかは、たくさんあるんですけど、今回自分の状況にあったものがなかなかなくて苦労しました。

こちらが実装画面です。

ここからアニメつけたりしますが、ひとまず思い通りに動くものができてよかった。まだPHP計算のほうは触ってないけど、これから徐々にやっていきます。

ちなみに、５秒もかかる計算でなにやっているのかも書かせてもらいますね。後半ならダラダラ書いても怒られないかな（笑）
ナンバーズ３の当選数字と暦がデータベースに入っていて、くじの開催日と同じ暦で、各位にどの数字が何回出たかを計算します。初期設定では、30回分だすように設定しています。遡る回数も自分で選んで計算できますが、ナンバーズ３が1340回以前は開催曜日が固定で同じ条件でないので、集計からは省くように設定してます。
また、当選番号の出現数の集計だけではなく、遡った回数の中でヒットした同じ暦の日で、全く同じ集計をしたとき、何回出現した数字が当選したかをさらに集計します。これが結構かかる（笑）
そうして導きだした数字が推移値と傾向値です。
推移値はいわゆる総平均。検証したすべての回で当選番号が出た出現数の平均です。それで結果としての推移が見れるわけですね。
傾向値は、推移値で集計した当選場号の出現回数を時系列で並べて、1個ずつずらしながら自己相関係数を取り、コレログラムを作成して、相関関係が一番高い場所を暫定周期として、その次に出る出現回数の傾向として表示します。
推移値と傾向値の計算は大して時間かからんのです。それよりも同じ条件で検証するのがすごいかかる（笑）
例えば、同じ曜日で過去150回遡って、30回ヒットした場合、その30回すべてで同じように150回遡って集計して、そのなかで当選した番号が何回出たかを記録していくわけです。これ、曜日だけじゃなくて、六曜、九星、干支、直、宿すべてで計算し、さらに、ナンバーズ３は風車の的に矢を打って番号を導き出してますので、その的から集計する当選番号も集計します。
これも暦別にやってますので、計算量としてはバカみたいに多いんですよ（笑）
こんだけやっても当たりの精度期待できませんけどね〜（笑）

よかったらページをご覧ください。

自動倉庫440

JavaScriptがiPhoneで動いてないじゃないかっ！

ナンバーズ３を暦別に集計するWebページを、PHPで作って、やはり、PHPではリロードが多くなるから・・・という理由でJavaScriptに書き換えて、やっと動くようになったのに、ふと自分のiPhoneから見てみたら、表示が初期表示の？と０で埋め尽くされているじゃないかっ！！

もちろん、ブラウザのJavaScript設定はONです。だって、PHPのページで使っていた、onclickのタイミングで計算してテキストを差し替える動きはちゃんとしてたしね。じゃあ、どこでやっちまってんだ？？何かコードの書き方のまずいところがあるのかな？？でも、スマホからエラー見えないし、よくわからない。

ひとまず、コードの中に、処理の内容詳細を書いたalertを処理後に挟みまくって、iPhoneでみてみたんです。
多分、動かなくなった時点で処理がとまるから、そこを追ってみれば原因がわかるんじゃないか？って考えたんです。
そしたら、最初のページ読み込み完了後に動くfunciotnがスタートしたあとにすぐ止まってる（笑）
処理のきっかけをonloadじゃなくて、DOMツリー構造が読みこまれたら動く方使っていたから、それがいかんのかな？とおもってたのに、そこは正常と（笑）
どうも、Sessionストレージが使えない気がするな・・・・。
これが使えないなら、JavaScriptの意味ないからなぁ。といって、PCでリロードばっかりもどうかと思うし・・・・。


調べたら、どうも原因らしきものがあったんですが、専門用語ばっかりでわからない（笑）だれか簡単に説明してくれ（笑）


まあ、今の私のスキルでは、解決できそうにないので、CSSのメディアクエリ使って、ごまかすことにします。
画面サイズがスマホならPHPページへ、PCならJavaScriptページへ飛ぶように、displayの設定で、リンクを表示したり、消したりすればひとまず逃げられるか（笑）
ああ、もし、動かなかった時・・・・の救済ボタンもいるか？！

まあ、また今度にしよう〜。

これだと、サーバー引越しはPHPとJavaScript両方整備しないといけなくなったな・・・・。まあ、やるけどさ（笑）

任意の期間のウィークデーをカウントすることを考えた（JavaScript）

もっと良い方法があるはず！
Webページ上で、ナンバーズ３の未来の開催回をざっとしらべたいと思いまして、期間中の月から金、いわゆるウィークデーの日数を計算するロジックを考えてました。
ひとまず、期待した結果は得られたけど、無駄っぽい分岐の上、長い（笑）
もっといい方法ないかな〜。

そんなわけで、JavaScriptで考えていたコードを下に貼ります。
まあ、基本的な命令ばっかりだから、言語が何であれ、どれでも一緒なのかも（笑）

//計算期間終了日（データベースから受けるのでハイフンつなぎ）
let e_day_arr = データベースの期間終了日.split('-');
//JSの「月」は0〜11になるみたいなので、実数から−１する
let e_day = new Date(e_day_arr[0],e_day_arr[1]-1,e_day_arr[2]);

//期間開始日(上と同じ；データだけ違う)
let s_day_arr = データベースの期間開始日.split('-');
let s_day = new Date(s_day_arr[0],s_day_arr[1]-1,s_day_arr[2]);

//日時の差を取得（結果はミリ秒単位になる）
let diff_ms = e_day - s_day;
//ミリ秒を日数に変換（ミリ→秒→分→時間→日）
let diff_d = parseInt(diff_ms / 1000 / 60 / 60 / 24);

//ウィークデー概算
//期間開始日の曜日を取得(日→0始まり)
let s_week = s_day.getDay();
//期間中、何週あるか調べる（7で割った商）
let syou = Math.floor(diff_d / 7);
//期間中、週未満の日付を調べる（７で割った余）
let amari = diff_d % 7;
//週にウィークデーは5日なので×５
var w_day = syou * 5;

//あまり日数を考える（最大6）
if(amari>0){
        switch(s_week){
          case 0:
              if(amari == 6){
                amari = 5;
              }
              break;
          case 1:
              if(amari >= 5){
                amari = 4;
              }
              break;
          case 2:
              if(amari == 4 || amari == 5){
                amari = 3;
              }else if(amari == 6){
                amari = 4;
              }
              break;
          case 3:
              if(amari == 3 || amari == 4){
                amari = 2;
              }else if(amari >= 5){
                amari = amari - 2 ;
              }
              break;
          case 4:
              if(amari <= 3){
                amari = 1;
              }else{
                amari = amari-2;
              }
              break;
          case 5:
              if(amari >= 2){
                amari = amari - 2;
              }else{
                amari = 0;
              }
              break;
          case 6:
              if(amari >= 1){
                amari = amari - 1;
              }
              break;
          default:
        }
          w_day = w_day + amari;
}

どっかまとめられるかなぁ〜。できたら週の戻り値使いたいよなぁ。

上の何週離れているかの計算あとに、残り日数を考えるのに使った表がこちらになります。

先に経過期間を７で割り算して経過週を出しました。ということは、経過週の終わりの曜日はというと・・・・はい、そうです。7日後ですから、同じ曜日です。
そこから、７で割れなかった、あまりの日数の経過を考えればいいわけです。
そして、７で割った余りということは、最大値は６になります。各曜日、6日経過の様子を表現できればいいということになります。
じゃあ、その様子をずらっと並べて考えてみる。それが上の表です。
法則ありそうだな〜って・・・・う〜ん・・・・・・。

「ああ！もう、めんどくさいから全部条件分岐してしまえ！！（笑）」

って、なったのが結果が上のJavaScriptコードです（笑）

実際にページで運用して検証してみました。

このように計算は問題なく動いてます！これでクジの開催のない未来回も集計計算できるようになりました。予想するときの参考データにどうぞ。
リンクは下からどうぞ。

自動倉庫No.４４０「ししまる」

ナンバーズ３の当選番号を暦別に集計するページをつくった話（MySQLからJavaScriptへ）

プログラミングの勉強を兼ねて、PHPでMySQLを使ってナンバーズ３の当選番号を暦別に集計したり、傾向値を計算したりしてました。
人に見せない前提で、結果だけわかればいいというWebページでしたので、ログイン機能つけて、自分専用にしていたのですが、サーバー閉鎖に伴って、引越しをしなくてはならなくなり、いい機会だから公開してみようかな・・・。と思い立ったのです。
最初は、単純に引越しをしながら、見え方だけ整備していたのですが、PHPとMySQLだと、計算のたびにリロードが必要になってしまうではないか・・・・・・。と、気づいてしまいました。
「JavaScript使った方が親切だよな・・・・。」と・・・・。
「どうせ見られないページなんだから、面倒くさいし、やめちまえ！！」という自分と、「これも勉強だよ! 」という自分。さらに、デベロッパーツール のエラーとの戦いを乗り越え（笑）、やっと単純集計のところまで書き換え完了しました。
こんな面倒なことやったんだから、ブログのネタも最近ないし、コードを見比べる記事でも書こうっ！となりまして（笑）よかったらお付き合いください。
コードの説明の前に、どんな動きができるかを説明しますね。

ページのリンクは下です。実際に動かしてみてもらえれば、よりわかるかと思いますので、よかったら動かしてみてください。

自動倉庫No.440

まず、メインから数字選択くじのナンバーズ３のページに入ると、次のような感じで表示されます。

次に、集計結果の表の説明です。

次に操作パネルと合計表の説明をします。

とまあ、これだけのページなんですが、結構かかってしまいました。
サーバーサイドのPHPからフロントエンドのJavaScriptへの変更だからものすごい大変・・・・というわけではなくて（笑）
単に、肉体労働のバイト掛け持ちがしんどかっただけです（笑）ホント、非正規雇用大変（笑）・・・・。

話を戻しまして、確かに、実行タイミングがページ表示の前から後に変わるので、ほぼ書き換えにはなります。ですが、集計計算の考え方は同じですから、そのまま使えます。
では、まず、このタイミングの変更によって、どうロジックを変更したかまとめていきます。

こんな感じです。
PHPメインの時、JavaScriptメインのように、MySQLから１回集計用のテーブルをもらって、PHPで集計という方法を当初とっていたのですが、データベースに接続後、そのまま暦の種別分、SQLのWHEREを変更しながらループさせて、一次集計をしたテーブルを作成しておくと、PHPのコードがスリムで読みやすくなったのでこの方法をとってました。
まあ、それはさておき、比べてどうでしょうか？
集計とHTMLの処理をフロントエンドに持ってきたので、再計算処理がフロントエンドだけで完結できるようになりました。これでページを開いている間は、リロードなしで動かせます！

それでは、コードを変更する際に、大きな違いが出たところをちょっと書いてきます。

まずですね。PHPでリロードする前提で作成していたときは、データベースに繋ぎ放題なので、状況に合わせてテーブルを引っ張ってこれます。最新のデータを引き出そうとすれば、連想配列に入れた最後のデータを見ればいいだけです。じゃあ、PHPで連想配列の最後を抽出するコードはと言えば、

変数名=end(連想配列);

で簡単に抜けます。
ですが、JavaScriptでは、集計用テーブルは１つです。まあ、複数準備しちゃえば、PHPと同じように

変数名=配列.at(-1);

で抜けますが、今回私は、暦を主とした１つのテーブルにしました。RIGHT（LEFT)JOINを使って2つのテーブルをつなげたわけです。暦は毎日あるのに対して、ナンバーズ３の当選番号はウィークデーだけです。そうすると、くじの開催のない日はNULLになります。しかも暦は向こう二年ぐらいデータベースにぶち込んでいますので、このテーブル上のデータの最後を抜くと、くじの最新回にならず、当選番号や開催回は確実にNULLになります。
なので、くじの最新開催回を得るには、連想配列を最後からさかのぼって、NULLじゃなくなるところが最新回というコードに変更する必要がありました。

for(let i=連想配列.length-1;i>-1;i--){
   if(連想配列[i].カラム名!==null){
        変数名＝連想配列.at(i);
        break;
   }
}

次ですが、PHPとJavaScriptの挙動の大きな違い、リセットが手動か自動かの違いです。PHPはリロードしてはじめから作り直しますので、自動リセットです。一方、JavaScriptはHTMLを差し替えていくだけですから、何か変更したら、そのまま残ります。今回のことで言うと、集計表の当選番号は赤枠、赤字でした。これ、基準日を変更して、当選番号が変わったら、消さないとどんどん赤枠が増えていくだけです。なのでスタイルの変更とリセットが対になっていないといけません。今回は、classでスタイルを指定していたので、classList使いました。


    document.getElementById('id名').classList.remove('クラス名');//クラス消すとき
    document.getElementById('id名').classList.add('クラス名');//クラス増やすとき

これをループの中で使う感じです。id名を工夫すればループでも生きますよ。
私がよく使う手は、id名に数字をつなげます。例えば、変数iがループのカウンターとすると、コード上は

document.getElementById('id名'+i).classList.remove('クラス名');//クラス消すとき

のようにid名にプラス変数（数値）で数字をくっつけます。気をつける点は、文字列の結合でプラスを使っているので、変数iに数値の計算をしようとして、そのまま続けて「＋」を使うと、文字列結合という判断になってしまい、数値計算できません。そういう時は、数値計算部分をかっこでくくればよいです。

と、まあ、PHPからJavaScriptへの書き換えで大きくハマったのはこんなところです。あとはほぼ同じロジックで書き換えちゃえばなんとかなりました。
次は、自己相関の計算と、傾向値の計算を書き換えていこうと思います。これはPHPでもちょっと複雑なので、ちゃんとコードを読み返さないとできなそう（笑）

それでは、今回はここまでです。
おつきあいありがとうございます。
よかったら自作ページ覗いていただけると励みになります。
とりあえず、次の開催回でしたら、当選番号が？表記で、該当の集計はされます。

こんな感じで表示されます。さらに未来も集計表示されますが、集計の下の方にある、的移動数値（グリーン）がまだ正確な計算ではありません。まだ使えるのは次回開催日だけですが、よかったら集計を使っていただければうれしいです。

【MySQL】リレーショナルデータベースを使ったデータの抽出

バイトを掛け持ちしてから、ずっとやる気がおきなくて、放置していたサーバーの引越しを、やっと再開しました。
自分が昔に書いたコードを整理しながらやってるんですが、MySQLも使っていたので、一緒にデータベースも整理してて・・・・。
で、ふと思い出したんですよ。
「そういえば、始めた頃、SQLのリレーション知らなくて、無駄にデータのカラム増やしてたっけなぁ・・・・」と・・・・。

ちょっとリレーションを勉強しようかと、いろいろページを見てみると、外部キーをどうするとか、IDをどうとか・・・・・いろいろあるんですが、
私が勉強してきたページって、テーブルの設計だけで終わっちゃうものが多くて、結局、実際にどう使うのか分からないんです。（笑）
そんなの、私だけかも知れんのですがね（笑）
確かに、テーブルの設計とか、重複しないユニークな共通するキーとか、すごい大事だと思うんですが・・・・・・

そもそも！「リレーションにならんのだろうか？？」と、気がついた時点で、おそらく共通するキーはすでにテーブルにあったりするんじゃないでしょうか？私はそうでした。
なので！検索して知ろうと思った時は、どうやってデータを呼ぶのか？？が一番知りたいんですよね。

そんなわけで、テーブルの作り方とか、設計とか、phpMyAdminの使い方とか・・・・・そこは、詳しく書きません。別に調べると山のようにでてくるので、そちらでお願いします（笑）
私は、どうやってSQL書いて抽出するのか？を主に書いていこうと思います。

まず、リレーションにする条件を簡単に説明しておきますと、繋げる２つのテーブルに、重複しないユニークデータで且つ、2つに共通するデータが存在する状態になっているかということです。これさえあれば、phpMyAdmin側で、特に何かを設定しなければいけないというものはありません。あと、テーブルのカラム名は重複しないようにしておきたいです。カラムの重複エラーが出ても、まあ動くんですけど、いざPHPの連想配列に入れて使うときに、重複してるカラムはうまく使えない・・・・みたいな不具合が出ます。重複をさける理由としては十分すぎるぐらいの不具合かと（笑）

で、今回、説明に使うサンプルは、私が実際に自分のサイトで扱っているテーマ、ナンバーズ３の当選数字と暦にします！
さて、この２つですが、ナンバーズ３は１日１回の開催で、土日には開催がありません。一方、暦にはいろいろ種類がありますが、どの種類も１日１個で、毎日あります。
データ総数は違ってきますが、どちらも同じ日に1つのデータなのは共通してますね。
なので、先ほど書いた、リレーションできる条件の、重複しないユニークデータで且つ、どちらにも存在するデータ・・・・。ここでは「日付」ということになります。

比べなくてもわかると思いますが、ちょっと直近２週間ぐらい並べてみましょう。

こんな感じにまとめられます。なんか、こういう書き方すると、日付をphpMyAdminの方で関連づけないといけないみたいに見えちゃいますが・・・・。こういった条件で、独立したテーブルが2つあると思ってください。

それでは早速、ナンバーズ３の開催日すべてに暦を合体させて抽出してみましょう。

SQL文は次のとおりです。

SELECT* FROM numbers3 INNER JOIN koyomi ON numbers3.kaisai = koyomi.day

となります。この文を実行するとテーブルはこうなります。

それでは、SQL文を分解して意味をみていきます。

SELECT* FROM numbers3

もうこれは絶対使うやつですね。普通にnumbers3テーブルを呼び出すSQL文です。
これに、 INNER JOINでkoyomi テーブルをくっつけて、ON以降で結合する条件を書きます。ここでは、numbers3テーブルのkaisaiカラム（numbers3.kaisai）とkoyomiテーブルのdayカラム（koyomi.day）は共通キー（=）つまり一緒だよって・・・・という意味です（笑）

さらにこの文には、通常どおりWHEREも付けられます。
このままこのテーブルで例をあげますね。
例えば、koyomiの火曜日のものを抽出するとしましょう。

SELECT* FROM numbers3 INNER JOIN koyomi ON numbers3.kaisai=koyomi.day WHERE koyomi.week='火';

これでデータは次のように抽出されます。

ほら！ナンバーズ３のテーブルには曜日のカラムが無くても、リレーションを使って暦と結合すれば、曜日で絞ることもできるんです！使い方はご理解いただけたでしょうか？

このように、結合するSQL文までを通常のテーブル呼び出しSQL文として考えちゃえば、WHEREの使い方は同じです。１つテーブルの時と違うのは、結合すると繋げたカラムを1つのテーブルとして使えるようになるわけですね〜。
このように、結合後のSQL文の使い方は変わらないので、ANDとかOR、BETWEENなども使えます！まあ・・・・私、BETWEENはあまり使わないですけど（笑）

また、２つのデータベースの繋げ方もいくつか種類があって、INNER JOINのところを変えて使い分けます。私はINNER JOIN以外ほとんど使ったことないですが（笑）
どう変えるのか？と言いますと、LEFT JOINとRIGHT JOINとします。
これは、JOINで繋げるテーブルのLEFTなら左側、RIGHTなら右側はすべてのレコードが抽出され、指定されてない側のレコードはONで指定した条件に一致するレコードがくっつきます。今回のサンプルテーブルだと、暦（koyomi）側を指定すれば、すべての暦が表示されて、ナンバーズ３（numbers3）の開催日にデータがくっつきます。データのない開催のない日は、Nullになります。例えば、INNER JOINの文と同じように暦を右にした場合、RIGHT JOINで結合すると次のようになります。

使い道はありそうな形ですよね。
そして、逆にLEFT JOINでナンバーズ３側を指定すると、この条件のテーブルですと、INNER JOINと同じ形になります。

あと、CROSS JOINってやつもいますが・・・・今回のサンプルには向きません。総当たりの組み合わせとか、商品一覧表なんかをつくるときに便利なのかなぁ・・・。まあ、私いまガッツリ肉体労働なんで全く使いませんので詳しくないです（笑）



さて、どうでしたか？みなさんの何かに、ちょっとでも役立つことがあったでしょうか？？（笑）
そうならいいなと願いつつ・・・・。今回はここまでです。ここまで読んでいただき、ありがとうございます。

おまけ

今回紹介しました、ナンバーズ３の当選数字と暦別の集計は、下のリンクで展示しております。
集計したって、関連なんて無いんですが・・・、次は何が来るかなぁ・・・・・なんて、思いながら見てみるのも面白いかと（笑）

自動倉庫No.440「ししまる」

【Excel】関数だけで勤務時間表の先月末の勤怠実績データを自動で反映させる方法（過去記事の合体技）

ああ、、、非正規社員にとって恐怖の、、、、、、あの大型連休が、、、、、
ゴールデンウィークがついにやってきてしまいますね。
全米は泣かないけど非正規社員は全員泣いてるはず（苦笑）

とりあえず！今のうちに、やれるだけガンガン仕事して、収入を蓄えておかないと、、、、連休あとの給料日が怖い！！（笑）
ってことで、、、ただいま休みも睡眠時間もゴリゴリ削って対策中っす！！

こんな状況なのに、なんでこんなもの？！書いているのか？といいますと、
実は、前回書いた、「動的にシートを指定する」記事に説明不足があることにふと気が付きまして、、、
直接過去記事に補足説明を追加しようかなと、思っていたのですが、いや、どうせ書くなら、実務で使えるやつを作りたいっ！！

それでも、さすがにゼロから新しいものを作る余裕は無いので、過去記事の「関数だけで「２４時間対応」の勤務時間表をつくってみる」がちょうどいい題材なので、この記事で書いた表を使って、前回記事の「セルの値によって参照するシートを変化させる方法」の補足説明をしたいなと思います。



では、早速。。。。
以前つくった24時間対応の勤務時間表を下のように準備します。

このように、２名分、２ヶ月ずつの勤務実績ダミーデータを入れたシートを準備しまして、１枚だけデータ無しで新しい年月のシートを準備しました。
もし、試しに同じように作ってみたいという方は、連動して参照先シートが変わった時に、変わったことがすぐわかるような、ダミーデータにしておくとデバッグが楽でいいと思います。

そうしましたら、勤務実績データの入っていない、新しい年月のシートを使って次のような動きをさせます。

使うメインの関数はINDEX＋MATCHの縦横検索です。この関数の組み合わせの中で、セル、配列の参照する場所がありますが、そこでINDIRECTを使ってシートを動的に指定します。
ここのセルD6に入れる関数式はこうなります。見やすいようにインデントつけてみます。このインデントがついたまま、Excelのセルにコピペしても動きます。

=INDEX(
  INDIRECT(TEXT(DATE(YEAR($A$1),MONTH($A$1),0),"'yyyy年m月") & " " & $F$1 & "!A:F"),
    MATCH($B6,INDIRECT(TEXT(DATE(YEAR($A$1),MONTH($A$1),0),"'yyyy年m月") & " " & $F$1 & "!B:B1"),0),
 COLUMN()
)

過去記事で使った関数しか使ってませんので、改めてそれぞれの関数の説明はしませんが、前回の記事で説明不足だった点がこの中にあります。
前回、INDIRECT関数を使って、シート名を指定するとき、'（シングルクォーテーション）で括ってませんでしたが、今回は括っています。 ADDRESS関数を使ってシート名を指定する場合は、勝手に'が入るので意識しませんが、自分で文字列をつなげてシート名を作成する場合で、「半角スペース」がシート名に入る場合は、'でシート名を括らないといけません。
「半角スペース」が入らないなら括らなくてもいいですし、「全角スペース」の場合も括らなくてもいいです。

なんで半角スペーズはダメで、全角スペースはいいの？？って言われると困りますが（笑）

えー、これは、私なりの解釈ですが、、、全角は人間界で使う言語、半角はPC界で使う言語・・・・。
みたいな感じです（笑）。
PCさんにとって、全角文字は別の意味を持たない「絵」みたいなものなのですが、半角文字は別の意味として理解できちゃうものがあるんですね。
なので、「半角スペース」として名前に使いたいときは、PCさんに「これは半角スペースです！」って説明しないといけないわけです。
PCさんは、寂しかりやなので、自分にわかるメッセージがあると、うれしくなって勝手に自分用に解釈しちゃうんです。

なので、PCさんによく話しかけてあげていると、括らなくてもいいらしい。。。。（←ウソ）

 

あとは、入力したセルから第１週目の実績入力欄に書式なしフィルすれば反映されるようになります。

こんな感じですね。
先月の日付が出る可能性があるのは第１週目だけですから、関数をいれておくのはここだけでいいです。
そして、参照先の先月シートには、今月の日付はありませんので、今月の日付になっているところから、エラー表示が出ています。
エラーを消す処理をしてもいいのですが、フォーマット用のシートとして、運用するならこのままの方が分かりやすいと思います。

なぜかというと、今月の実績を入力するためにシートを準備するのはおそらく、先月末から今月初めの間の月１回だけですね。
その準備は、フォーマットシートをコピーして、年月と名前を入力して、シート名を変えて、、、というように準備していくと思います。
そして、準備が整い、実際にデータを入力しようとする前に、不要な関数式を消しておきたいですが、、、月１回しか使わない上に、エラー表示も出ないだと、、、、多分、消し忘れちゃいます（笑）
さらに、こういった実績入力系は、自分だけが使う状況というのは少ないと思うので、不要な関数式を消し忘れてしまうと、
「これ消していいんですか？」とか、
「自動で実績が入らないですが・・・」とか
「関数が間違ってますよ？」とか、
いろいろ面倒なやりとりをせねばならなくなります（笑）
それは避けたいところですよね。
まあ、好みではありますので、エラーを消したい！と思われる方は、関数の大外にエラー回避の条件分岐をつけて使ってください。

 



さて、これで終わり・・・
とはいかなくて（笑）

このまま上の関数式を法定休日欄にフィルしてみますと・・・・

とまあ、よく見る「あるある」です（笑）
なので回避方法も、よく見る「あるある」でしょうね。
そして、いくつか方法があります。

まず、関数式でなんとかする方法。

めちゃくちゃ式が長くなるので、この方法は避けたい、、、、と、思うかもしれませんが、IF文の中に、同じ式を２ついれるだけなので、コピペで簡単につくれます。式もここだけなので、私はアリだと思います。

 

そして、次の方法は本当によく見るやつ。

このように「見えなくする」と「受け側の式を工夫する」の組み合わせです。

どれが一番いいですか？？ってことはなくて、どれも一緒です。
自分が理解しやすくて、使いやすいのが一番かなと思います。

 

最後に、おまけです。
機能として、あっても、なくてもいいものですが、もし、気に入ってもらえて、使えるPC環境なら使ってみてください。

今回はシート名にルールをつけましたが、フォルダ名やファイル名、人の名前の入力方法なんかでも、こういった名前などをつけるルールを社内や部署内で決めたとします。


でも、、、、結局、「ルールは破るためにある！」って状況になりませんか？（笑）
特に全角、半角の数字やスペースね。
半角数字と全角数字が混在しているケースもよくみたな。見つけて直すのに結構大変だったっけ（笑）
あと、氏名の間にスペースを入れるのか、入れないのか？全角なのか半角なのか？！問題もよくありますね（笑）

このようなことは、人間の目だと意味もわかるし、区別もつくので、大きな問題にならないことが多いですが、PCさんから見た場合、全角と半角は全くの別物です。
特に、今回のような、セルに入力された値から一致するシート名を探すというのは、値が一致するという前提があって、はじめて成り立つ関数管理方法です。このような管理方法にとって、入力ルールが破られることは、、、、かなりの致命傷になります。

なので、そういったことを少しでも回避できるようにするため、アラートを表示させる方法のご紹介です。

使う関数はCELL関数。
これは、セルの情報を表示する関数です。
先に使い方を書いておきます。

=CELL("情報の種類",参照先)

まず、参照先ですね。これは情報を得たいセルを指定するところです。省略もできますが、省略するとアクティブになっているセルの情報になるので、セルの選択を変えるたびにうるさく値が変化し、動作も遅くなります。なので、省略の使い方は、、、、よく考えた方がよいかと（笑）
次に、情報の種類ですが。これはたくさんありまして、書式だったり、列数、行数、色、などなど、、、、なんかVBAでオブジェクトから情報を引き出すみたいな動きで、とても便利に使えそうなのですが、、、ほとんどが他の関数でできるので、CELL関数の使い道はほぼないかと思います（笑）。残念！
そんな残念なCELL関数ですが、情報の種類の中のfilenameだけは、「シート名を得る」と検索したら絶対に出てくる、シート名を得るために生まれた救世主！唯一無二の関数なのです！（笑）
まあ、誇張しすぎて、言い過ぎに聞こえますが、、、実際、シート名を得るにはこれしかないです。シート名だけを得る方法もほぼ１つに確立されていますので、検索してみてはいかがでしょうか？


「えっ？ここで説明はしないの？？？」

ははは、、、、今回、シート名は参照しますが、抜き出しません！
では、どのように使うのかといいますと、こんな感じで使います。

そして、それぞれのセルに入っている関数式ですが、
まず、年月の下、セルA3です。

=IFERROPR(IF(FIND(TEXT($A$1,"yyyy年m月"),CELL("filename",$A$1))>0,"","想定外の結果です。管理者に連絡を！"),"注！シート名と年月が違います")

そして、名前の下、セルF3です。

=IFERROPR(IF(FIND($F$1,CELL("filename",$F$1))>0,"","想定外の結果です。管理者に連絡を！"),"注！シート名と名前が違います")

となっています。
CELL("filename",$A$1)をするとフルパスのファイル名＋シート名が得られます。その文字列の中から年月、名前と同じ文字列があるかどうかをFIND関数で探し、ヒットすれば文字の位置が得られますので、結果０より大きくなり、異常なしで何も表示させないようにします。
そして、同じ文字列が見つからなかった場合、FIND関数は０ではなく、エラーになります。なので数式の大外にエラー判定を置いておいて、エラーなら文字列がなかったことになり、コメントをセルに表示します。
さらに、０にはならないはずなのに０になったら、、、、もう想定外です。これ出るなら是非見てみたいので管理者を呼んでもらいましょう（笑）
このようにコメントは管理者の言葉として伝えたいことを書けばよいです。フォントや色も設定すればよりよいかと思います。

 

このようにシート名を確認し、それによってアラートを出し、ユーザー自身に確認してもらう方法をとっています。
シート名かセルの値のどちらかがルール通りならば、ガードの意味は出ますが、どちらも同じ間違いをした場合は、ガードをすり抜けます。さすがにどっちかはルールを守っててほしいもんですけどね（笑）


さて、なぜ、このように抜け道がある不完全なアラートを出す方法にしているのかといいますと、
シート名とセルの値を一致させるだけが目的であれば、シート名からそれぞれ年月と名前を抜き出し、セルの値としてやれば簡単です。その場合は、先ほど検索してみては？と案内した方法を使って実現すればよいと思います。絶対にセルの値とシート名は一致しますからね。ですが、この方法はシート名同士の入力ルールの一致はわかりませんね。
今回一番実現したいことは、動的に別シートを参照することです。
ということは、個別のシートごとにシート名とセルの値が一致していることが大事なわけではなく、すべてのシート名がルールに沿っていることが大事なわけです。
なので、手入力するたびにアラートを出すガードを設定しておいて、ルールにあってますか？と気づいてもらう機会を増やす方法をとっているわけです。
不完全なガードなので、結局は管理者がしっかり確認するしかないのですが、管理者の代わりに微力ではありますがアラートの文面が一役かっているという感じです。なので、文面は管理する人が考えましょう。
また、このアラートと一緒に、入力ガードをつけておくのも手です。もうこれ以上長く書くとバイトがシンドくなるのでサラッといかせてもらいますが、例えば、名前のセル側に全角、半角ガードをつけるのも手ですね。こんな感じ。

=IF($F$1=ASC($F$1),"","おい！全角スペース入れただろ！")

これで全角スペースが入ったらアラートでます。
ちなみに、このASCをJISにすれば全角チェックになります。

そして、年月の場合、そもそもこのシートは日付としてセルに入力してもらっているので、データが日付かどうかで判断すればいいっすね

=IFERROPR(IF(VALUE($A$1)>0,"","なんで0いれた？"),"日付で指定しろって言ったやん！")

なんてガードもかけれますね（笑）




さて、今回はこれで終わりです。
何か役に立つものがあったでしょうか？あればいいのですが（笑）
それでは、ここまで読んでいただきありがとうございます。

誤字脱字はまた後日確認いたします。。。。ご勘弁を。

追記

サンプルファイルをダウンロードできるページつくりました。
下にリンク作っておきます。

自動倉庫No.440「ししまる」