無限不可能性ドライブ

『ニューラルネットワーク自作入門』に刺激されてExcelVBAでニューラルネットワークを作ってみたものの、やっぱり数学やらなきゃと思い少しずつやってきたのもあって、自分の知識の整理とかそういった感じです。

【VBA編】設計

前回まででデータの読み込み処理の作成が完了しました。
今回からは実際にニューラルネットワークを作るフェーズに移っていきます。
その前に、どのように実装するかを簡単に見ていきましょう。

以前の記事もあわせて参考にしてください。
celaeno42.hatenablog.com

作成するニューラルネットワークのイメージ

f:id:celaeno42:20181201115558p:plain

今回作成するニューラルネットワークは、入力層1層、隠れ層2層、出力層1層からなり、それぞれを「classInputLayer(入力層クラス)」「classHiddenLayer(隠れ層クラス)」「classOutputLayer(出力層クラス)」(のインスタンス)として実装します。

入力層では「がくの長さ」「がくの幅」「花弁の長さ」「花弁の幅」の4つの入力を受け取る必要があるため、「classInputUnit(入力ユニットクラス)」を4つ定義します。

隠れ層1層めと2層めのユニット数は任意ですが、ここではそれぞれ3つと4つにしており、それぞれを「classUnit(ユニットクラス)」で定義します。

出力層では「Iris-setosa」「Iris-versicolor」「Iris-virginica」の3種類に分類するので、ユニットを3つ、「classUnit(ユニットクラス)」で定義しています。

入力層のユニットと隠れ層、出力層のユニットの実装クラスが異なるのは、入力層では入力値をそのまま次の層(隠れ層)に渡すだけで、特に演算等が不要なためです。

また、全体を通しての制御などは標準モジュールに「mdlSupervisor」モジュールを作成して行います。
他に、活性化関数の演算などは標準モジュールに「ML」モジュール(マシンラーニング用モジュール)を作成して行います。

では、必要な準備を整えておきましょう。

モジュールとシートの追加

上記であげた標準モジュール、クラスモジュールを追加しましょう。
今回追加するモジュールは以下の通りです。

【標準モジュール】

オブジェクト名 用途
mdlSupervisor 全体の制御に使用
ML マシンラーニングの演算に使用


【クラスモジュール】

オブジェクト名 用途など
classInputLayer 入力層の管理に使用
classHiddenLayer 隠れ層の管理に使用
classOutputLayer 出力層の管理に使用
classInputUnit 入力用ユニット
classUnit 隠れ層、出力層用ユニット


また、今後必要となるシートもあわせて追加しておきましょう。
シートはオブジェクト名も変えておくのを忘れないでください。
今回追加するシートは以下の通りです。

オブジェクト名 シート名 用途
ws_Test_Result テスト結果 テスト結果の出力に使用
ws_Train_Result 訓練結果 訓練結果の出力に使用
ws_W_H1 w_h1 隠れ層1層めの重みの出力に使用
ws_W_H2 w_h2 隠れ層2層めの重みの出力に使用
ws_W_Out w_out 出力層の重みの出力に使用
ws_WI_H1 wi_h1 隠れ層1層めの重みの初期値の出力に使用
ws_WI_H2 wi_h2 隠れ層2層めの重みの初期値の出力に使用
ws_WI_Out wi_out 出力層の重みの初期値の出力に使用


すべて追加するとこのようになります。
f:id:celaeno42:20181205214831p:plain

いよいよ次回からはニューラルネットワークのコードを書いていきます。


f:id:celaeno42:20181212233850p:plain

【VBA】VBAからPythonのコードを実行してみる(2)【Python】

VBAにないなら Python のを使えばいいじゃない…の続き

前回は VBA から Python のコードを呼び出して、セルの値を取得→加工→書き込むという処理をみてみました。
今回は os.walk を使ってファイル一覧を取得する処理を実装してみましょう。

celaeno42.hatenablog.com

まずはPythonだけで実行するコード

Python でファイル一覧を取得する処理を記述します。
まずは Python だけで実行できるコードを書いてみましょう。

【xl_getFolderFileList.py】

import os

def getFolderFileList(path):
    for folder, subfolders, files in os.walk(path):
        for file in files:
            filepath = os.path.join(folder, file)
            print(filepath)

getFolderFileList(r'C:\Users\xxxx\Desktop')

実行すると、デスクトップにあるファイルやサブフォルダのファイル一覧を表示します。

さて、これを VBA から実行するように書き換えてみましょう。

VBAから実行する Pythonコード

【xl_getFolderFileList.py】

import os
import xlwings as xw

def getFolderFileList_VBA():
    path = xw.Range('A1').value
    row = 2
    for folder, subfolders, files in os.walk(path):
        for file in files:
            filepath = os.path.join(folder, file)
            rng = 'A' + str(row)
            xw.Range(rng).value = filepath 
            row += 1

まずは、xlwings をインポートして xw で参照できるようにしています。
また、[ path = xw.Range('A1').value ] で、A1セルからパスを取得するようにしています。
ファイルリストの表示は 2行目から行うので、row の初期値に 2 を設定し、os.walk の処理に入ります。
filepath が取得できたら、表示先セルを指定して filepath を表示し、次に備えて row をインクリメントします。

Pythonだけで実行するコード」と見比べると、Excel のセルとのやり取り部分が増えていますが、基本的な部分は同じことがわかると思います。

では、この Pythonコードを VBA から呼び出す処理を書いてみましょう。

VBA から Python を呼び出すコード

VBA - 標準モジュール】

Public Sub getFolderFileList()
    Call RunPython("import xl_getFolderFileList; xl_getFolderFileList.getFolderFileList_VBA()")
End Sub

書き方は前回のコードと同様です。
ファイル名と呼び出すプロシージャ名が変わったくらいですね。

では、A1セルに対象フォルダのパスを記述して実行してみましょう。
今回はデスクトップに作った「無限不可能性ドライブ」フォルダを対象にしてみました。
実行結果はこのようになります。
f:id:celaeno42:20181127235752p:plain

サブフォルダまで検索したファイル一覧が作成されました。

さて、今回のサンプルでは、対象となるフォルダはA1セルに設定されたパスのフォルダのみです。
これを例えば、A列にはA1セルに設定されたパスのフォルダ、B列にはB1セルに設定されたパスのフォルダ…
というようにするにはどうしたらよいでしょう。

VBAの呼び出し側で Pythonの関数の引数となる値を渡してあげればよさそうです。

引数を受け取る Pythonコード

今回は引数として Excel の列番号(col)を受け取るようにしてみました。
前回のコードとの違いは Range() のセルの指定部分だけです。
今回は行番号と列番号で指定しています。
かっこが2重になっている(タプルになっている)ことに注意してください。

【xl_getFolderFileList.py】

import os
import xlwings as xw

def getFolderFileList_VBA2(col):
    path = xw.Range((1, col)).value
    row = 2
    for folder, subfolders, files in os.walk(path):
        for file in files:
            filepath = os.path.join(folder, file)
            xw.Range((row, col)).value = filepath
            row += 1

では、VBAから呼び出してみましょう。

VBA から Python を呼び出すコード(引数あり)

VBA - 標準モジュール】

Public Sub getList()
    Call getFolderFileList2(1)
    Call getFolderFileList2(2)
End Sub

Private Sub getFolderFileList2(ByRef col As Long)
    Call RunPython("import xl_getFolderFileList; xl_getFolderFileList.getFolderFileList_VBA2(" & col & ")")
End Sub

引数の指定部分は、まぁ、よくある文字列との連結の書き方ですね。

では、A1セル、B1セルに対象フォルダのパスを記述し、「getList()」プロシージャを実行してみましょう。
A1セルには先ほどと同じフォルダ、B1セルには「Documents」フォルダ内の「vba」フォルダを指定しています。

実行結果はこのようになりました。
f:id:celaeno42:20181128231708p:plain
A列とB列でファイルの一覧が取得されています。

まとめ

サブフォルダ内も含むファイル一覧の取得も、Python の関数を使うことで比較的簡単に実現できたのではないでしょうか。
他にも Python でやったほうが簡単な処理があると思いますので、VBAだとちょっと…という場合には、このような方法も選択肢としてはありかなと思います。

ただし…わざわざ Python を呼び出しているためか、VBAのみで記述するよりは遅いのでまぁ、それを許容できるかというのもあるかもしれませんね。



おまけ

Excelとの直接の連携を考えないなら単純にこのような方法もあります。
コマンドプロンプトから [ >python ファイル名.py ] で実行するのと同じなので、
xlwings や pywin32 は不要です。

Pythonのコードはメッセージボックスを表示するだけの簡単なものです。

【helloworld.py】

from tkinter import messagebox

messagebox.showinfo('vba2py', 'Hello World! from VBA')

VBA - 標準モジュール】

Option Explicit

Public Sub pythonコードを実行()
    Dim obj As Object
    Dim pyPath As String
    
    Set obj = CreateObject("WScript.Shell")
    
    'Pythonファイルのパスはフルパスを設定するようにしたほうがいいかも
    pyPath = "C:\Users\xxxx\xxxxxxxx\helloworld.py"

    Call obj.Run("Python " & pyPath, WaitOnReturn:=True)
    
    Set obj = Nothing
    
End Sub


【実行結果】
f:id:celaeno42:20181124222122p:plain


※※ ノンプロ研アドベントカレンダー7日目の記事です ※※
adventar.org

【VBA】VBAからPythonのコードを実行してみる(1)【Python】

VBAにないなら Python のを使えばいいじゃない

ExcelVBAを書いていて「この処理 Python で書けば楽なのに…」とか思うことってないでしょうか。
例えば、

 サブフォルダを含めてファイルの一覧を取得したいけどVBAでどう書いたっけ?
 Python なら os.walk で簡単に書けるのになぁ…VBAにも os.walk みたいな関数があればいいのに。。

とか。

今回はそんなときのために Excel VBA から Python のコードを実行する方法についてみていきたいと思います。
Windows環境でPythonの環境構築が終わっている前提です。)

(準備1)xlwings と pywin32 のインストール

まずは必要なものをインストールします。

xlwings

コマンドプロンプトを起動してpip インストールでインストールしましょう。

pip install xlwings

Pythonの対話モードなどでインストールの確認をします。

>>> import xlwings

エラーにならなければOKです。

pywin32

こちらも同様に。

pip install pywin32

インストールの確認(pywin32 でなく win32 です)

>>> import win32

ちなみに、Anaconda の入っている私の環境では両方ともインストール済みでした。

(準備2)xlwings.bas ファイルを探して Excel VBA にインポートする

xlwings のインストールが正常に完了していれば、xlwings.bas ファイルがどこかにあるはずです。
Windows の検索機能を利用するなどしてファイルの場所を特定しましょう。
なお、私の環境では以下のフォルダにありました。

C:\Users\xxxx\Anaconda3\pkgs\xlwings-0.10.2-py36_0\Lib\site-packages\xlwings

ファイルのありかがわかったら、Excel VBA にインポートします。
VBE を開いて「ファイルのインポート」で xlwings.bas をインポートしてください。
インポートすると標準モジュールに「xlwings」というモジュールが読み込まれます。

これでひととおりの準備が完了です。

ここまでできたら、ファイルをいったん保存しておきましょう。
今回は「xl2py.xlsm」というファイル名にしてみました。
ついでに、今後のコードを書くための標準モジュールも追加しておきましょう。

f:id:celaeno42:20181126231450p:plain

(お試し)簡単なコードで動作確認

まずは簡単なコードで動作の確認をしてみましょう。
A1セルのテキストをコピーしてA3セルに貼り付ける処理を作ってみます。
貼り付ける前に若干の加工もしてみましょう。

(お試し)Pythonコードの準備

VBAから呼び出す Python のコードを準備しましょう。
ファイル名は「copy_text.py」とし、先ほど作成した Excel ファイル(xl2py.xlsm)と同じフォルダに作成します。

【copy_text.py】

import xlwings as xw

def copyAddText():
    txt = xw.Range('A1').value
    txt += ', I am the Doctor.'
    xw.Range('B3').value = txt

xlwings を import し xw でアクセスできるようにしています。
copyAddText 関数の内容はそれほど説明することはないかと思います。
txt = xw.Range('A1').value で A1 セルの値を変数 txt に格納しています。
その後加工して B3 セルに格納しなおしています。

(お試し)VBAからの呼び出し

上記で作成した Pyhon コードを VBA から呼び出す処理を記述します。
今回は標準モジュールにコードを書いています。

VBA - 標準モジュール】

Option Explicit

Public Sub copyText()
    Call RunPython("import copy_text; copy_text.copyAddText()")
End Sub

RunPythonプロシージャ の引数に呼び出すPythonファイルと関数の情報を渡します。
引数の書き方は、[ "import pythonファイル名; pythonファイル名.関数名()" ] というような感じになっています。
拡張子の .py は不要です。
ちなみに RunPython プロシージャは先ほど Excel にインポートした xlwingsモジュール内に記述されています。

(お試し)VBAの実行

では、VBAを実行してみましょう。
Python のコードでは、A1 セルの値を取得していたので、実行前に A1 セルに「Hello」と入力しておきます。
f:id:celaeno42:20181125235350p:plain


VBAの「copyText()」を実行すると…
f:id:celaeno42:20181126232006p:plain

このようになります。
無事 Python のコードが実行されて、その結果が Excel のセルに反映されました!

次回はいよいよ「Pythonコードでのファイル一覧取得」についてみていきます。


※※ 私が10月から参加させていただいているノンプロ研のアドベントカレンダー3日目の記事です。 ※※
※※ ノンプロ研はこの12月で開始からちょうど1年だそうです。おめでたいですね! ※※
adventar.org

【VBA編】データの標準化

前回はそれぞれのシートにCSVデータを読み込む処理を作成しました。
今回は読み込んだデータを標準化する処理を書いていきます。

標準化

「標準化」とは、データを「平均が0、分散が1になるように変換する」ことです。
標準化を行うことで、学習の効率がよくなるそうなので、今回使用するデータについても標準化することにします。
グラフで見るとこんな感じ。
f:id:celaeno42:20181118000955p:plain

がくの長さを x 軸、がくの幅を y 軸にとってデータをプロットしています。
青い点が標準化前のデータ(元のデータ)、オレンジの点が標準化後のデータです。
標準化後のデータは原点 O の周辺に集まっているのがわかると思います。

標準化の計算

標準化は個々のデータについておこなっていくのですが、Excelの関数では、

  =STANDARDIZE(x, 平均, 標準偏差)

を使って求めることができます。
なお、引数の x は 元の値ですが、

  平均は「=AVERAGE(データ)」
  標準偏差は「=STDEV.P(データ)」

でそれぞれ求めることができます。

標準化後の値を z とすると、数式では以下のようにあらわされます。


\displaystyle z = \frac{x - \mu}{\sigma} = \frac{標準化前の値 - 平均}{標準偏差}


(今回のコードではワークシート関数を使うのでこの式は利用しません。)

では、標準化のコードを書いていきましょう。

必要なシートの追加

訓練データとテストデータそれぞれの標準化後のデータを格納するシートを追加します。
標準化後のデータを入力データとして利用するので、それぞれのシート名には「(入力)」とつけています。
オブジェクト名はそれぞれ「ws_Train_Data_Input」、「ws_Test_Data_Input」としました。
f:id:celaeno42:20181130204519p:plain


定数の宣言

標準モジュール「G」に以下の列挙型の定数を宣言しておきます。
「ラベル列」というのは、データの正解(値)が格納されている列です。
今回であれば「アヤメの種類」が格納されている列です。

'[G - 標準モジュール]

Public Enum RW
    DATA_TITLE = 1             'データのタイトル行
    DATA_START = 2             'データの開始行
End Enum

Public Enum CL
    DATA_LABEL = 1             'データのラベル列
    DATA_START = 2             'データの開始列
End Enum

標準化のコード

「mdlReadData」に以下のコードを書きましょう。
引数として、元データのあるシート、書き込み先のシートを取っています。
 元データのあるシートは、「ws_Train_Data」もしくは「ws_Test_Data」
 書き込み先のシートは、「ws_Train_Data_Input」もしくは「ws_Test_Data_Input」
が呼び出し元でセットされます。

'[mdlReadData - 標準モジュール]

'ワークシートのデータを標準化して書き込み先のワークシートに書き込む
'[引数] <- aWsData : Worksheet / 元データがあるシート, aWsStandardized : Worksheet / 標準化したデータを書き込む先のシート
'[戻り値] -> なし
Private Sub standardizeData(ByRef aWsData As Worksheet, ByRef aWsStandardized As Worksheet)
    Dim r As Long
    Dim c As Long
    Dim i As Long
    Dim eRow As Long
    Dim eCol As Long
    Dim mean As Double                           '平均値
    Dim std As Double                               '標準偏差
    
    aWsStandardized.Cells.Clear
    
    With aWsData
    
        '最終行と最終列を取得する
        eRow = .Cells(Rows.Count, 1).End(xlUp).Row
        eCol = .Cells(1, Columns.Count).End(xlToLeft).Column
        
        '列ごとに処理
        For c = CL.DATA_START To eCol
            
            '平均値と標準偏差を求める
            mean = WorksheetFunction.Average(.Range(.Cells(RW.DATA_START, c), .Cells(eRow, c)).Value)
            std = WorksheetFunction.StDev_P(.Range(.Cells(RW.DATA_START, c), .Cells(eRow, c)).Value)
            
            '標準化を行う
            For r = RW.DATA_START To eRow
                aWsStandardized.Cells(r, c).Value = WorksheetFunction.Standardize(CDbl(.Cells(r, c).Value), mean, std)
            Next
            
        Next
        
        'タイトル行とラベル列をコピー貼り付け
        .Rows(RW.DATA_TITLE).Copy Destination:=aWsStandardized.Rows(RW.DATA_TITLE)
        .Columns(CL.DATA_LABEL).Copy Destination:=aWsStandardized.Columns(CL.DATA_LABEL)
        
    End With
    
End Sub

1列目はラベルなので、2列目から処理を開始します。(CL.DATA_START は 2です。)
列ごとにワークシート関数で平均「mean」と標準偏差「std」を求めています。
平均と標準偏差が求まったら、元データの値を「=STANDARDIZE(x, 平均, 標準偏差)」のワークシート関数を使って標準化し、標準化後の値を書き込み先のシートに書き込みます。
すべての列について上記処理を行い、最後にタイトル行とラベル列を書き込み先シートにコピーして終了です。

標準化処理の呼び出し

では、上記の標準化処理を呼び出してデータを標準化しましょう。
前回作成した「Click_データ読み込み()」プロシージャにコードを追加します。
追加するのは、
 [ Call standardizeData(ws_Train_Data, ws_Train_Data_Input) ]
 [ Call standardizeData(ws_Test_Data, ws_Test_Data_Input) ]
の2行です。
それ以外の変更点はありません(コメントを除く)。

'[mdlReadData - 標準モジュール]

'データの読み込み
Public Sub Click_データ読み込み()
    Dim filePath As String
    
    Application.ScreenUpdating = False
    
    '訓練データの読み込み
    filePath = Range(G.RNG_TRAIN_DATA_PATH).Value
    Call readData(ws_Train_Data, filePath)
    '訓練データを標準化する
    Call standardizeData(ws_Train_Data, ws_Train_Data_Input)

    
    'テストデータの読み込み
    filePath = Range(G.RNG_TEST_DATA_PATH).Value
    Call readData(ws_Test_Data, filePath)
    'テストデータを標準化する
    Call standardizeData(ws_Test_Data, ws_Test_Data_Input)

    Application.ScreenUpdating = True
    
    MsgBox "データを読み込みました。", vbOKOnly + vbInformation
    
End Sub

処理の確認

では、メインシートの「データ読み込み」ボタンをクリックしてデータを読み込んでみましょう。
「訓練データ(入力)」シート、「テストデータ(入力)」シートにそれぞれ標準化後の値が書き込まれていると思います。

【訓練データ(入力)】シート(ws_Train_Data_Input)
f:id:celaeno42:20181130213440p:plain

【テストデータ(入力)】シート(ws_Test_Data_Input)
f:id:celaeno42:20181130213643p:plain

【VBA編】データの読み込み

必要なシート、モジュールの追加

前回は読み込むデータのパスを取得するまでの処理を作りましたので、今回はそのデータを読み込む処理を作ります。
訓練データ、テストデータはそれぞれ別のシートに読み込ませたいので、「訓練データ」と「テストデータ」の2つのシートを追加します。
オブジェクト名はそれぞれ「ws_Train_Data」、「ws_Test_Data」としています。
また、読み込む処理を書くためのモジュールも追加しましょう。
標準モジュールを追加し、オブジェクト名を「mdlReadData」とします。

f:id:celaeno42:20181117230044p:plain

CSV読み込み処理

今回使用するデータはCSV形式のデータとなっています。
Windowsのメモ帳でそのまま開くとこんな感じ。
なお、訓練データとテストデータはデータの件数が異なるだけです。

f:id:celaeno42:20181117231036p:plain:w400

単にCSVファイルを読み込むだけの処理なのでどういう方法でも構いませんが、ここでは次のようなコードにしてみました。

'[mdlReadData - 標準モジュール]
Option Explicit

'データの読み込み
Public Sub Click_データ読み込み()
    Dim filePath As String
    
    Application.ScreenUpdating = False
    
    '訓練データの読み込み
    filePath = Range(G.RNG_TRAIN_DATA_PATH).Value
    Call readData(ws_Train_Data, filePath)

    
    'テストデータの読み込み
    filePath = Range(G.RNG_TEST_DATA_PATH).Value
    Call readData(ws_Test_Data, filePath)

    Application.ScreenUpdating = True
    
    MsgBox "データを読み込みました。", vbOKOnly + vbInformation
    
End Sub

'データを読み込んで書き込み先のワークシートに書き込む
'[引数] <- aWsData : Worksheet / 読み込んだデータを書き込む先のシート, aFilePath : String / CSVデータのファイル名
'[戻り値] -> なし
Private Sub readData(ByRef aWsData As Worksheet, ByRef aFilePath As String)
    Dim fileNo As Long
    Dim r As Long
    Dim i As Long
    Dim data As String
    Dim datas() As String
    Dim dataLength As Long
    Const DELIMITER As String = ","
    
    With aWsData
    
        .Cells.Clear
        
        r = 1
        fileNo = FreeFile
        Open aFilePath For Input As #fileNo
            Do Until EOF(1)
                Line Input #fileNo, data
                datas = Split(data, DELIMITER)
                dataLength = UBound(datas)
                
                'splitはoption base 1の影響を受けないのでインデックスは0からスタート
                For i = 0 To dataLength
                    .Cells(r, i + 1).Value = datas(i)
                Next
                
                r = r + 1
            Loop
        Close #fileNo
    
    End With
    
End Sub

「Click_データ読み込み()」はボタンがクリックされたときに呼ばれる処理、「readData()」が実際のCSV読み込み処理です。
ここでは特に難しいことはしていません。

コードが書けたら、メインシートの「データ読み込み」ボタンに「Click_データ読み込み()」プロシージャを登録して、実際にデータファイルが読み込めるか試してみましょう。
正しく書けていれば、訓練データシート、テストデータシートにこのようにデータが読み込まれます。
(並び順は読み込むファイルによって異なります。)

f:id:celaeno42:20181117232201p:plain:w400

【VBA編】メイン画面の作成

メイン画面の作成

メイン画面を下記のように作成します。

f:id:celaeno42:20181115232506p:plain

シート名は「ws_Main」としておきます。
また、「G」という名前で標準モジュールを作成しておきましょう。
ここにはグローバルで使用する定数や変数をまとめておきます。

f:id:celaeno42:20181116214312p:plain

定数の宣言

パスを格納するセルのアドレスを定数として宣言します。

'[G - 標準モジュール]
Option Explicit

Public Const RNG_TRAIN_DATA_PATH As String = "C2"   '訓練データのパスを格納するセル
Public Const RNG_TEST_DATA_PATH As String = "C3"    'テストデータのパスを格納するセル

データの選択

訓練データとテストデータのファイルを選択する処理を作ります。
「C2」セルか「C3」セルをダブルクリックするとファイル選択ダイアログが表示されるようにします。
ファイル選択ダイアログでファイルを選択すると、ダブルクリックしたセルにパスが表示されます。
ダブルクリック時の処理なので「ws_Main」シートの「Worksheet_BeforeDoubleClick()」プロシージャに記述します。

'[ws_Main - シートオブジェクト]
Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim filePath As String
    
    If Target = Range(G.RNG_TRAIN_DATA_PATH) _
        Or Target = Range(G.RNG_TEST_DATA_PATH) Then
        
        Cancel = True
        
        filePath = Application.GetOpenFilename("CSV ファイル,*.csv")
        
        If filePath <> "False" Then
            Target.Value = filePath
        End If
        
    End If
    
End Sub


コードが書けたら正しく動作するか確認しておきましょう。

f:id:celaeno42:20181116221550p:plain


データのダウンロード先を再掲しておきます。
訓練用データ:https://1drv.ms/u/s!Akn_IZSOKLJ-1gKVR6dEZOntRsIh
テストデータ:https://1drv.ms/u/s!Akn_IZSOKLJ-1gHgfYyKccBTvuTX
(webで表示すると1行目が文字化けしてますが、ダウンロードすると正常に表示されます(Shift-JIS))

【VBA】文字列検索【GAS】

某所でのお題を若干改変して「スプレッドシートから特定の文字列を検索してそのアドレスを出力する処理」としてみました。
この表の中から「Excel」という文字列を探して、アドレスを出力します。
GASの勉強という側面もあるので、Findとかは使わずに地味に For で回す処理にしてあります。

f:id:celaeno42:20181109233420p:plain

VBA

Option Explicit

Public Sub textSearch()
    Dim vRange As Range
    Dim r As Long
    Dim c As Long
    Dim searchText As String
    Dim res As String
    
    searchText = "Excel"
    
    Set vRange = ActiveSheet.UsedRange
    
    res = ""
    For r = 1 To vRange.Rows.Count
        For c = 1 To vRange.Columns.Count
            If Cells(r, c).Value = searchText Then
                res = Cells(r, c).Address
                GoTo BREAK
            End If
        Next
    Next
    
BREAK:

    If res = "" Then
        Debug.Print "文字列「" & searchText & "」は見つかりません。"
    Else
        Debug.Print "文字列「" & searchText & "」は[" & res & "]にあります。"
    End If
    
End Sub

特段特別な書き方はしていません。
唯一、二重の For から一気に抜けるために GoTo を使っていることくらいでしょうか。
(他の言語の break 文をイメージ)


【GAS】

function textSearch() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var values = sheet.getDataRange().getValues();
  var res = '';
  
  var searchText = 'Excel';
  
  LABEL:
  for (var i=0; i<values.length; i++) {
    for (var j=0; j<values[i].length; j++) {
      if (searchText == values[i][j]) {
        res = sheet.getRange(i + 1, j + 1).getA1Notation();
        break LABEL;
      }
    }
  }

  if (res == '') {
    res = '文字列「' + searchText + '」は見つかりません。'
  } else {
    res = '文字列「' + searchText + '」は[' + res + ']にあります。'
  }
  
  Logger.log(res);
  
}

こちらも二重ループから一気に抜けるためにラベルを使っています。
Javaも同じ書き方ですが、どうもこのラベルの使い方は慣れないです…