ExcelのVLOOKUP関数の高速化

今回はExcelの小技ということで、検索でよく使うVLOOKUP関数の高速化についてです。

VLOOKUP関数は、第四引数にTRUEかFALSEかを設定します。
通常はFALSEで使用すると思うのですが、TRUEを指定するとあいまい検索になります。
「あいまい検索」と言われると部分一致検索のようなものを思い浮かべるかもしれませんが、実際は二分検索だそうです。
二分検索については情報処理技術者試験でも出題されるので各自調べてほしいのですが、平たく言うと検索対象のデータが昇順にソートされていることを条件に高速に検索する方法です。
Excelの場合は文字コード(SJIS)の昇順に並べる必要があります。

FALSEの場合は線形検索(上から順次検索)になるので、検索対象のデータ量がN倍になると検索にかかる時間もN倍になります。
しかし、TRUEの場合は二分検索になるので、検索対象のデータ量がN倍になっても検索にかかる時間はlogN(底は2)倍の増加で済みます。
例えば、データ量が2倍になった場合は、FALSEだと2倍の時間がかかるようになりますが、TRUEだと1.414…倍になります。データ量が3倍になった場合は、FALSEだと3倍の時間、TRUEだと1.732…倍になります。

注意点としては、TRUEにした場合は満たす値が無かった場合にも値を返すようになるということがあります。
検索条件を満たす値が無かった場合は、満たす値未満の最も大きな値を返します。
例えば、以下のようにデータ並んでいて11を検索した場合は、けが返ります。
10 け
12 こ

FALSEの場合と同じように一致するデータが無かった場合に#N/Aとしたい場合は、以下のようにする必要があります。
=IF(VLOOKUP(検索値,範囲,1,TRUE)=検索値,VLOOKUP(検索値,範囲,列番号,TRUE),NA())
まずは検索条件と同じデータが存在するかどうかを調べ、存在する場合のみ検索をする、存在しない場合は#N/Aとする、ということをしています。
VLOOKUP関数を2回発行しているのでデータ量が少ないとFALSEの場合よりも時間がかかる場合もありますが、データ量が増えてくると効果を発揮します。

ここで注意点なのですが、漢字をキーにして検索する場合は、フィルタからの並び替えは不可です。
フィルタから並び替えると、文字コードの昇順ではなく、読み仮名の昇順に並んでしまうためです。

漢字を文字コードの昇順に並び替えるためには、以下の手順を踏む必要があります。

1.「データ」タブ→「並び替え」を選択

2.「オプション」を選択

3.「ふりがなを使わない」を選択

4.「列」を当該列、「並び替えのキー」を「値」、「順序」を「昇順」とする

5.「OK」を押下すると文字コードの昇順に並び替えられる


いかがでしたでしょうか。

VLOOKUP関数は普段の業務でも良く使用する関数だと思うのですが、調べてみると意外と奥深いことがわかります。
二分検索を使用した高速検索、漢字の文字コード順の並び替えについては、知っておいて損はないと思います。

業務で役に立つ小ネタがありましたら、また紹介しようと思います!

java8:関数型インターフェースの背景にある考え方

【前置き】

Java8から関数型インターフェースが使用可能になりました。
具体的に「ラムダ式」「Stream」「Optional」「Files」と言った方がわかりやすいでしょうか。

関数型インターフェースの使用を半ば強制されるフレームワークが登場していたり(例:Apache Spark)、関数型インターフェースでJavaを書く開発者も増えてきたので、目にすることも多くなってきたかと思います。

関数型インターフェースは関数型プログラミングをサポートするものであるため、従来からJavaでサポートされていたオブジェクト指向プログラミングとは発想が異なります。
そのため、従来のJavaを学習してきた方にとっては抵抗感を感じるものであると思います。

今回の記事では、抵抗感を少しでも減らすために、関数型プログラミングの考え方を簡単に紹介したいと思います。

【サンプルコード】

言葉で説明するよりも先にサンプルコードを見た方がわかりやすいと思うので、サンプルコードを先に紹介します。
年齢のリストから30代の人数を数える、というプログラムです。
ごく短いプログラムですので、お付き合いください。

・FunctionTest.java

・実行結果

【関数型プログラミングの考え方】

関数型プログラミングでは、以下のことを実現しようとしています。
色々難しい用語(例えば「副作用」等)はあるのですが、今回は用語を使わずに簡潔にまとめます。

・内部状態(State)を排除する

最も本質的な考え方です。

関数型プログラミングでは、内部状態を排除することを目的としています。
「内部状態」とは、上記のコードで言うと「count」や「i」を指します。

内部状態が入りこんでしまうと、内部状態により関数の結果が変わってしまうため、内部状態を把握する必要が出てきてしまい、可読性が悪化します。
(把握のために「count」や「i」をトレースする必要が出てきてしまう)
把握しきれずに意図しないバグを出してしまうことも珍しくありません。
内部状態を排除して、品質を上げよう、という発想です。

また、コンピュータにとっては内部状態は重要ですが、人間にとってはやりたいことを実現できれば良く、内部状態は重要ではありません。
重要ではない記述を削減することでコードを完結にしたい、という発想もあります。

Java8のラムダ式では、ラムダ式の外部で定義された変数の値をラムダ式の内部で変更することを禁止されています(コンパイルエラーになる)。
その背景には、内部状態の排除があると思っています。

・自然言語に近い形で処理を記述する

これは、コードが簡潔になった結果生じた副次的な考え方かもしれません。

関数型プログラミングでは、関数を組み合わせることにより処理を実現します。
関数を次々とつなぎ合わせるように記述することで、ソースコードが自然言語に近い形になります。
わかりやすく言えば、ソースコード自体がコメントのようになります。

例えば、サンプルコードでは「年齢のリストから30代の人数を数える」という処理を行おうとしています。
従来のプログラミングでは、これを実現するためにforループとかカウント用の変数を使用しており、何をしているのか把握するためには、内部状態をトレースして意図を汲み取る必要があります。
しかし、関数型プログラミングでは、
「list.stream().filter(x -> x >= 30 && x <= 39).count()」→
「listを30<=x<=39でfilterしてcountする」
と読めるため、
「年齢のリストから30代の人数を数える」
という処理であることを自然に把握することができます。


いかがでしたでしょうか。

IT業界、特にSIer業界だと、情報処理技術者試験を軸にして知識を身に付けることが多いかと思います。
しかし、情報処理技術者試験では手続き型プログラミングやオブジェクト指向プログラミングを中心とした出題で、関数型プログラミングが扱われることは全く言って良いほどありません。
そのためとっつきにくさは拭えないと思いますが、先進的な企業を中心に関数型プログラミングを取り入れる企業も出てきています。
これからのことを考えると、せめて、関数型プログラミングに対する抵抗感は払拭するべきではないかと思っています。

今回はこれで締めくくりたいと思います。
では、また来週!

保守性の高いコードを作成するために心がけるべきこと

ソースコードは作って終わりではなく、その後何年、何十年にもわたって保守開発が行われます。
また、保守開発を行う開発者もその間に入れ替わります。
ソースコードを作る際は、このことを踏まえて高品質・低コストで保守開発ができるようにする必要があります。

難しい話をするとデザインパターンやフレームワークの話になるのですが、今回は新人も含めて最低限心がける必要があることを挙げていきます。

1.適切な変数名やメソッド名を与える

変数名・メソッド名を与える際は、その変数やメソッドが何をするのかわかるような名前にする必要があります。
例えば、「a」や「hoge」といった変数名は不可で、「loopEndFlag」や「commodityCode」といった意味のある変数名にする必要があります。

また、現場毎で命名規則が決められていることも多いので、それに倣った命名をする必要があります。
命名規則を無視すると、他のソースコードとの統一性が失われて読みにくいソースコードになったり、影響分析等のためにキーワードで検索する時に引っかからなくなったりします。

2.適切にコメントを記述する

コメントを入れることで、その箇所で何をしているのかがわかりやすくなります。
しかし、コメントを入れれば良いというものではなく、意味のあるコメントである必要があります。

例えば、

といったソースをそのまま日本語にしただけのようなコメントは不可で、

といった業務的な意味を書く必要があります。

また、ソースコードの先頭には「ソース名」「処理概要」「変更日」「変更概要」「変更者」といった情報を記述するのが一般的です。
メソッドの先頭には「メソッド名」「処理概要」「引数」「戻り値」「出力され得る例外」といった情報を記述するのが一般的です。

コメントの書き方も、現場毎で決まっていることが多いです。

3.分かりやすいロジックを心がける

保守開発の際にソースコードのロジックを読み解くことも多いので、if文のネスト(if文の中のif文)が多すぎる、goto文で制御があちこちに飛ぶ、といったロジックが分かりにくくなるような書き方も避けた方が良いです。
また、if文やfor文等を使う際は、インデント(左側のスペース)を適切に入れて、構造が分かりやすくなるようにした方が良いです。
(インデントの入れ方は各言語の入門書を真似れば良いです)

なお、新人の内はあまり気にする必要はありませんが、使用する文法も入門書に載っているものを中心にした方が無難です。
自分の現場で広まっているなら良いのですが、そうでないのに新しい文法やマイナーな文法を使うと、他の開発者(特に新人)から見て理解しにくくなることがあります。

4.ハードコーディングは原則禁止

ハードコーディングとは、マスタデータをソースコードの中に持たせることです。

例えば、

のような書き方は不可で、商品コードの一覧を持たせたいなら、データベースやファイルに持たせてそこから取得するべきです。

マスタデータは、追加や修正や削除が行われることがあります。
マスタデータをデータベースやファイルに持たせていない場合、追加・修正・削除が行われる度に、ソースコードを修正しコンパイルする必要が出てきて保守工数が増加します。

更に言うと、マスタコード読み込みのような色々なソースコードで使われる処理は、共通処理として別のソースコードに切り出すべきです。
これをしないと、修正する際に修正漏れが発生する可能性が高くなります。
大抵の場合、使うべき共通処理は現場毎やプロジェクト毎で決められているので、それに従う必要があります。

5.仕様書とソースコードを合わせる

仕様書には、ソースコードがどのような意図で作成されているのか、他のソースコードとどのような連携をしているのか、等の設計思想が記載されています。
しかし、仕様書がソースコードと乖離していた場合、仕様書から調査する時に実際の実装を誤って理解してしまいます。 そのことにより、保守開発でバグが生まれる原因になります。
それを防ぐために、ソースコードが仕様書から乖離した時は、仕様書もソースコードに合わせて修正するべきです。


いかがでしたでしょうか。

学生時代からコーディングしていたという人は少なからずいらっしゃると思うのですが、今回述べたことは学生時代のコーディングでは習慣として身に付きにくい所だと思います。
(私が新人だった頃もコーディング経験者の同期がいたのですが、その同期が研修で書いたコードを見ると、変数名が「a」とか「b」とかの適当な名前で、ソースコードが読み辛かったのが記憶に残っています)
同じソースコードを担当者を変えながら何年も何十年も保守開発していくのは社会人ならではだと思いますので、保守しやすいソースコードを書く習慣が身に付いていない方は、保守のしやすさを是非意識していただければと思っています。

では、また来週!

unix/linux:perlの複数命令を1行のコマンドで実行する(例:文字列のバイト位置走査)

perlはファイル操作や正規表現に優れているスクリプト言語であるため、コマンドライン上でperlを駆使することができれば作業の幅が広がります。
perlの-eオプションによりコマンドライン上で実行可能となり、1つ1つの命令を ; で区切ることで複数命令を記述可能となるため、これを使いこなすことでスクリプトを作らなくともperlの機能を作業に使うことができるようになります。
コマンドを予め作成して本番作業時にコピペしたい場合やオペレーターに作業を依頼する場合等、スクリプトを気軽に作れない場合も少なくないので、そのような場合に効果を発揮します。

以下は、ファイルの中から特定の文字列のバイト位置を走査する例です。
ファイルの先頭で見つかった場合は0、次のバイトで見つかった場合は1、…といった具合で値が返ります。
見つからなかった場合は-1が返ります。
(例えば、改行コード無しのファイルで特定のデータをcutで除外したい時に、除外する位置を確認するのに使えます)


いかがでしたでしょうか。
perlという言語自体は知っている方が多いと思いますが、それをコマンドラインから実行して作業を効率化できる、というのは盲点ではなかったでしょうか。

ちなみに、今回紹介しませんでしたが、perl -eには便利な追加オプションがいくつもあります。
(標準入力(インプット)を1行1行処理、改行の強制付与、等)
「perl ワンライナー」で検索すると参考になるページが出てくるので、更に複雑な操作を行いたい場合は調べてみると良いでしょう。

では、また来週!

実務で良く見かけるループ処理

今回の記事では、実務で良く使われるループ処理のパターンを挙げていきます。

1.二重ループ

ループの内側にループがあるというのは良くあるパターンです。
javaでサンプルを書くと以下のようなパターンです。

実行結果は

となります。

何をしているのかと言うと、
・2次元配列に格納した値を順番に取り出している
・ただし、5を取り出したらその時点で処理を終了する
ということをしています。
(実務では、1次元目が親項目、2次元目が子項目のことが多いです)

このパターンのループの場合、原則として、内側のループの終了条件は外側のループの終了条件を内包しています。
今回の例で言うと、
・内側のループの終了条件…!endFlag && j < arraySizeY
・外側のループの終了条件…!endFlag
となっています。

このような場合、内側のループにしかない終了条件に着目すると、それぞれのループで何をしているのか把握しやすくなります。
今回の例で言うと、内側のループにしかない終了条件として「j < arraySizeY」があります。
この終了条件に着目することで、内側のループでは2次元目の配列を順番に読んでいる、ということを把握することができます。

2.リトライ処理

タイミングによって失敗する可能性がある処理を行う場合、失敗してもすぐに異常終了しないように、その処理が失敗した際にリトライをかける制御を入れることがあります。
例えば、通信やデータベースアクセス等で良く使われる制御です。

フローチャートで言うと以下のようになります。

「処理が失敗した時だけループを継続する」というロジックになっていたら、このパターンである可能性が高いです。

なお、今回のフローチャートでは省略していますが、無限ループで負荷がかからないように、ループ時にスリープを入れたり、ループ回数を設定したりすることも多いです。

3.先読みRead

レコードの中身を見てループ継続条件を判断する場合は、1レコード目のみループの前に先読みして、2レコード目以降はループの中で読み込む、ということをします。

フローチャートで言うと以下のようになります。
(EOFの場合の処理は省略しています)

派生パターンとしては、コントロールブレイクやマッチング処理があります。
詳しくは以下のページを参照してください。
コントロールブレイク
マッチング処理

なお、ファイルの場合はReadですが、SQLのカーソルの場合はFetchです。どちらにしても制御としては同じです。


いかがでしたでしょうか。

上記の3つのパターンが出てくるソースコードを分析する機会があったのですが、若手の方が分析に苦労していたので、今回の記事を書こうと思いました。
今回記事にしたことは半ば暗黙知化していることなので、それを文章にすることで少しでも他の技術者の助けになれれば幸いです。

これからも、参考になる情報を記事にしていきたいと思います!