Galapagos Tech Blog

株式会社ガラパゴスのメンバーによる技術ブログです。

使えるGoogleスプレッドシートの関数まとめ

こんにちは、最近Googleスプレッドシートの関数の勉強をしているテストチームのあべです。

今回は、ソラで覚えて、使えるようになっておいて損はないぞ!とおもった関数をいくつかご紹介したいと思います。

(ちなみに)関数の勉強を始めた経緯

もともと、私は普通科の学校で教わる程度のごくごく簡単な関数しか知りませんでした(SUMとかAVERAGEとか)。
新卒で弊社に入社後、業務をしていくなかで、よく使うGoogleスプレッドシートの関数を学んでおいたほうが作業効率があがるのでは?と思い勉強をはじめました。

アジェンダ

紹介する関数は以下です。

1. 【ROW】行番号を表示する
2. 【IF】条件を満たしているかどうかで、処理を変える
3. 【VLOOKUP】指定範囲を検索して、必要な値を取り出す
4. 【IFERROR】エラーになったときの処理を指定する
5. 【COUNTIF】条件に一致する要素の個数を数える
6. 【COUNTA】データの入ったセルの個数を数える
7. 【NETWORKDAYS】稼働日数を計算する

1. 【ROW】行番号を表示する

作業をしていて、表に番号をつけることがあると思います。そんなとき、上から1,2…と入力するのはなかなかめんどうです。
オートフィルを使えば一気にできるよ!と思われるかもしれませんが、行の入れ替えをすると順番があべこべになってしまいます。 そんなときに ROW を使えば、一気に行番号を表示させることができます。また、入れ替え等であべこべになることもありません。

ROW([セル参照])

 行番号を返すセルを、セル参照に指定します。指定しなかった場合は、関数を入力したセルの行番号が返されます。

例:表の2行目を1として番号をつけたい場合、 A2 に以下を入力します。

=ROW()-1

f:id:glpgsinc:20180620151510p:plain

あとは、オートフィルを使えば一気に番号をつけることができます。

2. 【IF】条件を満たしているかどうかで、処理を変える

個人的には、いままで作業をしている中で1番使用頻度が高いです。

IF(論理式, TRUE値, FALSE値)  
  
倫理式を定義して、それが真である場合と偽である場合に返す値を指定します。

例:返却日の入力欄である E2 が空白のセルの場合は”未”、そうでない場合は"済"と H2 に入力したいときは、 H2 に以下を入力します。

=IF(E2="","未","済")

f:id:glpgsinc:20180620154512p:plain

未返却のものには"未"、返却済みのものには”済”と表示させることができました。

3. 【VLOOKUP】指定範囲を検索して、必要な値を取り出す

業務で使っている一覧表などから、欲しい情報を得たいときがあると思います。そんなときに使えるのが VLOOKUP です。

VLOOKUP(検索キー, 範囲, 番号, [並べ替え済み])

検索したい文字列やキーを 検索キー に指定して、指定した 範囲 から検索します。
検索キーと一致した行の左端を1列目とし、得たい情報がある列を数値として 番号 に指定します。
並び替え済み には、完全一致の場合はFalse、部分一致の場合はTrueと指定します。

例: H2 に入力した端末番号の返却状況を H3 に表示させたいとき、H3 に以下を入力します。

=VLOOKUP(H2,A2:E12,5,False)

f:id:glpgsinc:20180620160939p:plain

指定した端末番号の返却状況を表示させることができました。

4. 【IFERROR】エラーになったときの処理を指定する

結果にエラーが出たときの処理を指定できる関数です。計算ミスや値の指定ミスでエラーが出るのはいいですが、意図しない理由でエラーが出た場合に使えます。

IFERROR(値, [エラー値])

正常の場合は1番目の引数である 値 を返し、エラー値が出た場合は2番目の引数である エラー値 を返します。

例:上の3.【VLOOKUP】で入力した関数は、 H2 が空白だった場合はエラーが表示されてしまいます。

f:id:glpgsinc:20180620161707p:plain

エラーが出ていても問題はありませんが、なんとなく気持ちがよくないです。IFERROR で H2 が空白の場合は空白で返すよう、 H3 に以下を入力します。

=IFERROR(VLOOKUP(H2,A2:E12,5,False),"")

f:id:glpgsinc:20180620162114p:plain

H2 が空白でも、エラーが表示されなくなりました。

5. 【COUNTIF】条件に一致する要素の個数を数える

指定した範囲の中で、条件に一致する要素の個数を数える関数です。

COUNTIF(範囲, 条件)

範囲 の中で指定した 条件 に一致する要素の個数が表示されます。

例: N2 にメーカー「サムスン」の端末数を表示したいとします。 範囲 はメーカーの列とし、 M2 (サムスン)に該当する要素の個数を数えたいときは、 N2 に以下を入力します。

=COUNTIF(D2:D21,M2)

f:id:glpgsinc:20180620162839p:plain

サムスンの端末の個数が表示されました。

6. 【COUNTA】データの入ったセルの個数を数える

指定した範囲で、データの入ったセルの個数を数える関数です。
COUNTA の良いところは、似た関数 COUNT が数値の個数を数える(文字列等は数えない)のに対して、”データの入ったセル”すべてを数えてくれる点です。弊社では、項目書のテストケースを数えるのに COUNTA が使われたりしています。

COUNTA(値1, [値2, ...])

数えたい値、または範囲を指定します。

例:表に書いてある端末すべての個数を N4 に表示したいとき、以下を N4 に入力します。今回は、値の範囲を端末名の列に指定しました。

=COUNTA(B2:B21)

f:id:glpgsinc:20180620170038p:plain

端末名の列にデータが入ったセルが数えられ、表にある端末の合計を数えることができました。

7. 【NETWORKDAYS】稼働日数を計算する

タスクをいつまでにどの順番で、それぞれ何日で終わらせるか?を考えるとき、またはチームで管理するときに、カレンダーを見て稼働日数をいちいち数えるのはめんどうですよね。(数え間違えるかもしれないし)
そんなときに使える関数です。土日を除外した、平日の稼働日数を数えてくれます。

NETWORKDAYS(開始日, 終了日, [祝日])

稼働開始日と終了日を指定します。祝日等で月〜金曜日がお休みの場合は、 祝日 で指定します。

例:稼働開始日が06/14、終了日が07/31の稼働日数を D2 に表示したいとします。開始日と終了日を指定し、また7/16は海の日でお休みになるので、 祝日 に F2 を指定した以下を D2 に入力します。

=NETWORKDAYS(B2,C2,$F$2)

f:id:glpgsinc:20180620172407p:plain

7/16を除いた稼働日数33日を表示させることができました。

さいごに

使えるようになっておいて損はない関数を7つご紹介しました。
読んでくださったみなさまに、少しでもお役に立てれば幸いです。
また、上記の関数を組み合わせれば、さまざまな処理が実行できるようになります。

まだまだ関数勉強中なので、また「これつかえる!」と思ったものがあればご紹介したいと思います。
読んでいただきありがとうございました!

ところで

弊社ではエンジニアを募集しています。ご興味をお持ちのかたはぜひ弊社採用ページをご覧ください。

www.glpgs.com


参考: Docs editors Help

Create MLでMNISTの分類器を作ってみた

こんにちは。iOSチームの高橋です。好きな拡張子は.xhtmlです。

今回はWWDC 2018で発表されたCreate MLについて、セッションの内容を踏まえつつ、簡単なデモ(みんな大好きMNIST)で評価をしてみました。

注意:この記事はベータ版の内容をもとに書かれています。実際に公開されるバージョンでは事情が異なっている可能性がありますのでご注意ください。

Create MLとは

昨年のWWDCでは、Kerasなどの機械学習フレームワークで学習させたモデルを読み込んで推論を行うCore MLが発表されました。 このblogでも、以前に一度Core MLを使ったスタイル変換について紹介しています。

gtech.hatenablog.com

しかし、Core MLでできるのは学習済みモデルを使った推論のみで、学習自体は別のフレームワークを使って行う必要がありました。 それはKerasであったりTensorFlowであったり、あるいはLIBSVMであったり様々ですが、いずれも多かれ少なかれ機械学習に関する専門的知識を必要としたり、 データの整備やモデルの評価などの煩雑な(しかし重要な)作業を必要とするものでした。

Create MLを使えば、基本的なワークフローを理解するだけで、以下のようなタスクに対応する機械学習モデルを簡単に作ることができます。

  • 画像分類
  • テキスト分類
  • 表データからの回帰・分類

以下では画像分類モデルの作りかたを見ていきます。

ワークフロー

機械学習モデルの構築は、次のようなステップで行われます。

  1. データの収集・整備
  2. 学習
  3. 評価

Create MLは、このそれぞれのステップをものすごく簡略化するためのAPIを提供しています。

データの整備

学習させるデータは、モデルに与えられる前に十分に整備されている必要があります。 たとえば画像ならばサイズを揃えたりする必要がありますし、文字列ならば適切なトークン化(文字単位、あるいは形態素単位)と符号化が必要になるでしょう。

Create MLはこれを全部肩代わりしてくれます。開発者がすべきことは単にディレクトリに画像やテキストを放り込むだけです。 ラベルはサブディレクトリの名前として与えられます。たとえばMNISTならばこんな風になります:

train_data/
├ 0/
| ├ 00007.png
| ├ 00010.png
| └ ...
├ 1/
| └ ...
└ ...

また、(今回は使いませんでしたが)画像データのオーグメンテーション1にも対応しており、 学習時にオプションを与えることで自動的に処理してくれます。

学習

画像分類モデルの学習は二通りのやりかたが用意されています。Create ML UIを使う方法と使わない方法です。まずは前者から説明します。

macOS用のPlaygroundを作成し、Assistant editorを開いた状態で以下のようなコードを実行します。

import CreateMLUI

let builder = MLImageClassifierBuilder()
builder.showInLiveView()

すると下図のようなUIが出現します(画像は公式のリファレンスから引用しています)。 f:id:glpgsinc:20180611214817p:plain

あとは"Drop Images To Begin Training"のところに上で用意したフォルダをドロップするだけで学習が開始します。 今回はMNISTを1000枚に絞った小さなデータセット2(学習用800枚とテスト用200枚)を作成し、それを学習させてみたところ、Training精度が86%、Validation精度が82%となりました。 Validationが表示されるということは何らかのハイパーパラメータを振っているのだと思いますが、そのあたりの詳細はよくわかりません。

ところで、もちろんコードで書く方法も提供されています。MLImageClassifier構造体のイニシャライザに学習データディレクトリのURLを与えるだけで学習がスタートします。

import CreateML
let trainDirectory = URL(string: "file:///path/to/train/data")!
let classifier = try! MLImageClassifier(trainingData: .labeledDirectories(at: trainDirectory))

このイニシャライザには他にparametersという引数をとり、学習する最大ステップ数やデータオーグメンテーションの種類などを渡すことができます。 筆者はFeatureExtractorTypeという列挙体が気になるのですが、今のところ1種類しか定義されていないようです。いずれは他のものも使えるようになるのかしら。

評価

学習が終わったら、評価をせねばなりません。Create ML UIを使った場合は、学習が終了すると精度表示の下に"Drop Images To Begin Testing"というボックスが表示されるので、 テストデータをそこにドロップすれば評価が実行されます。今回のデータセットでは85%の精度が出ました3

800枚で85%というのがどのくらいなのか、比較のためにTensorFlowで簡単なモデル4を書いて実験してみたところ、81.5%の精度を得ました。 どうやら同等以上の性能が出るようです。これは転移学習5がうまくいっているためと思われます。 しかもコードはたったの3行で。これはうれしいですね。

モデルの書き出し

十分に精度の高いモデルが得られたら、それをmlmodelファイルに書き出します。 Create ML UIを使った場合はライブビュー上のメニューを展開するとモデルのメタデータを設定して保存するボタンが出てくるので、それだけでOKです(下の画像も公式のリファレンスから引用しています)。 f:id:glpgsinc:20180614153543p:plain

コードで書いた場合は、MLImageClassifierwrite(to:metadata:)メソッドを呼ぶことで、指定したURLにモデルファイルが書き出されます。

let outputURL = URL(string: "file:///path/to/output")!
try! classifier.write(to: outputURL)

書き出されたモデルのサイズはなんとたったの152KBでした。これも転移学習のおかげでパラメータの大部分をOS側に載せることに成功しているためです6

まとめと感想

ということで、Create MLを使って簡単な手書き数字識別モデルを作成することができました。 たった3行のコーディングで、機械学習の専門知識なしにそれなりの精度のモデルが作れてしまうのは便利だな、と思います。 モデルサイズが小さく抑えられるのも実用面で魅力的ですね。

一方で、せっかく転移学習をしているわりには精度がTensorFlowと同程度だったのが気懸かりではありますが、 これは元の学習データとMNISTが大きく異なるデータセットだったからではないかと考えています。 (そう考えると、CIFAR-10などで比較したら違う結果になっていたかもしれません)

また、今回はデータセットの準備が面倒だったので試さなかったテキスト分類や回帰モデルなども、 同じように非常にシンプルなコードで簡単にモデルを作成できるようなので、次の機会があれば試してみたいと思います。

ところで(いつもの)

弊社ではエンジニアを募集しています。ご興味をお持ちのかたはぜひ弊社採用ページをご覧ください。

www.glpgs.com

以上です。


  1. 画像のフリップや回転などの加工でデータ量を水増しすること

  2. 大きなデータセットが用意できない/するのがめんどくさいようなケースを想定しています。

  3. ただし、何度か実験すると精度はけっこうばらつくようです。これは学習時のTraining/Validationデータの分割がランダムであることなどに起因するのだと思われます(実際何をやっているのかはわかりませんが)。

  4. アーキテクチャは畳み込み+プーリング3層+全結合2層。Weight decayあり。ステップ数はCreate MLにあわせて10としました。ちなみに、両者のステップ数を100にしたときは、Create MLが87%、TensorFlowが84.5%でした。

  5. すでに大量のデータで学習されたモデルの一部だけを少量の新しいデータで学習し直すことで、少量のデータから高精度なモデルを作成する手法です。

  6. ちなみにTensorFlowからのmlmodel書き出しは試していませんが、チェックポイントのサイズが80MBほどあったので、まあその程度でしょう。

【Android】Notification Channelのお話

こんにちは。Androidエンジニア(たまにiOSエンジニア)のほかりです。 今回は今更ながらAndroid 8から追加されたNotificationChannelについてお話しようと思います。

背景

ご存知の方も多いかと思いますが、

  • 新規アプリは2018/08から
  • 既存アプリは2018/11から

targetSdkVersionを26以上(2018/06時点では27が最新)にしないとGooglePlayにアップロードできなくなります。

※ 今後も新しいOSバージョンが出るたびにこの要求されるSDKバージョンは上がっていきます。

ソースはこちら

まぁこのこと自体はtargetSdkVersionを26以上にしてビルドすれば良いのですが、現在わかっていることで一つ問題になることがあります。 すでにタイトルでネタバレしているのですが、Notificationのことです。

Android 8 (Oreo)からNotificationにChannelという新たな概念が登場しました。 targetSdkVersionが26未満の場合は特に関係なかったのですが、26以上にする場合NotificationChannelを少なくとも一つは実装する必要があります。 なのでNotificationChannelの実装方法などを紹介していきます!!

ユーザー視点からの変更点

Android端末の「設定」→「アプリ」→「通知」で表示される項目が変わりました。 Android8端末ならアプリのラウンチャーアイコン長押しからアプリ設定に飛んでもいいですね。

ではどういう風に見た目が変わるのかをAndroid 7 と 8で比べてみます。 通知確認用に作ったアプリの設定画面でみてみましょう!

Android 7までの通知設定画面

f:id:glpgsinc:20180605160925p:plain

通知を表示するのか・表示しないのかという極端な設定項目しかありません。

Android 8からの通知設定画面

f:id:glpgsinc:20180605160918p:plain

カテゴリというグループで「お知らせ」と「重要」というチャンネルが追加されていて、 チャンネル毎に通知を表示するのか・表示しないのか設定することができるようになりました。

※ チャンネル名はアプリ側で実装しています。

グループのカスタマイズ

実装次第では「カテゴリ」となっていたグループを別の名前に変更することができます。 未指定だと「カテゴリ」になります。

f:id:glpgsinc:20180605160928p:plain

実装

では違いがわかったところで、いよいよ実装していきます。

「グループの作成」、「チャンネルの作成」、「通知」をする機能を持ったNotificationHelperというクラスを作ってみました。 最後に全体像をお見せしますが、初めは説明も兼ねて細かくコードを載せていきます。 言い忘れていましたがKotlinで実装しています。

stringを定義

<?xml version="1.0" encoding="utf-8"?>
<resources>
    <string name="notification_group_push">Push通知</string>
    <string name="notification_group_app">アプリ内通知</string>

    <string name="notification_channel_news">お知らせ</string>
    <string name="notification_channel_important">重要</string>
</resources>

グループとチャンネルの列挙型を定義

    /**
     * グループ.
     */
    enum class Group(val resId: Int) {
        Push(R.string.notification_group_push),
        App(R.string.notification_group_app)
    }

    /**
     * 通知チャンネル.
     */
    enum class Channel(
            val resId: Int,
            val importance: Int,
            val color: Int,
            val visibility: Int,
            val group: Group
    ) {
        News(
                R.string.notification_channel_news,
                NotificationManager.IMPORTANCE_MIN,
                Color.GREEN,
                Notification.VISIBILITY_PRIVATE,
                Group.Push
        ),
        Important(
                R.string.notification_channel_important,
                NotificationManager.IMPORTANCE_HIGH,
                Color.BLUE,
                Notification.VISIBILITY_PRIVATE,
                Group.App
        )
    }

グループとチャンネルを作成

    if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.O) {

        // グループ生成
        val createGroup: (Group) -> NotificationChannelGroup =
                fun(group: Group) = NotificationChannelGroup(
                        getString(group.resId),  // グループID
                        getString(group.resId))  // グループ名
        manager.createNotificationChannelGroups(arrayListOf(createGroup(Group.Push), createGroup(Group.App)))

        // チャンネル生成
        val createChannel: (Channel) -> NotificationChannel =
                fun(channel: Channel) = NotificationChannel(
                        getString(channel.resId), // チャンネルID
                        getString(channel.resId), // チャンネル名
                        channel.importance) // 優先度
                        .apply { group = getString(channel.group.resId) } // グループと紐付け
            manager.createNotificationChannels(arrayListOf(createChannel(Channel.News), createChannel(Channel.Important)))
        }
    }

初めにバージョンをチェックしているのは古いOSバージョンで通知チャンネルの実装を行うと落ちるからです。 ※ ちょっと面倒くさがってIDと名前を同じにしてしまっています。

通知の発行処理

    fun post(channel: Channel) {
        val pending = TaskStackBuilder.create(this)
                .addNextIntent(Intent(this, MainActivity::class.java))
                .getPendingIntent(0, PendingIntent.FLAG_UPDATE_CURRENT)

        val notificationBuilder = NotificationCompat.Builder(this, getString(channel.resId)) // チャンネル指定
                .setGroup(getString(channel.group.resId)) // グループ指定
                .setColor(channel.color)
                .setSmallIcon(R.mipmap.ic_launcher_round)
                .setStyle(NotificationCompat.BigTextStyle()
                        .setBigContentTitle("Title")
                        .bigText("Message"))
                .setAutoCancel(true)
                .setVisibility(channel.visibility)
                .setContentIntent(pending)

        manager.notify(notifyId.incrementAndGet(), notificationBuilder.build())
    }

ここの細かいところは割とテキトーに書いてしまいましたが、注目していただきたいところはコメントしてあるところです。 - NotificationCompat.Builderのインスタンス生成時にチャンネルIDを指定します。 - その後setGroupでグループIDを指定します。

あらかじめ作っておいたグループ・チャンネルを指定しないと通知が表示されませんのでご注意を!

ソースコード全体

/**
 * Notification周りのヘルパークラス.
 */
internal class NotificationHelper(context: Context?): ContextWrapper(context) {

    /**
     * グループ.
     */
    enum class Group(val resId: Int) {
        Push(R.string.notification_group_push),
        App(R.string.notification_group_app)
    }

    /**
     * 通知チャンネル.
     */
    enum class Channel(
            val resId: Int,
            val importance: Int,
            val color: Int,
            val visibility: Int,
            val group: Group
    ) {
        News(
                R.string.notification_channel_news,
                NotificationManager.IMPORTANCE_MIN,
                Color.GREEN,
                Notification.VISIBILITY_PRIVATE,
                Group.Push
        ),
        Important(
                R.string.notification_channel_important,
                NotificationManager.IMPORTANCE_HIGH,
                Color.BLUE,
                Notification.VISIBILITY_PRIVATE,
                Group.App
        )
    }

    companion object {
        private val notifyId = AtomicInteger(0)
    }

    private val manager: NotificationManager by lazy {
        getSystemService(Context.NOTIFICATION_SERVICE) as NotificationManager
    }

    init {
        if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.O) {

            // グループ生成
            val createGroup: (Group) -> NotificationChannelGroup =
                    fun(group: Group) = NotificationChannelGroup(getString(group.resId), getString(group.resId))
            manager.createNotificationChannelGroups(arrayListOf(createGroup(Group.Push), createGroup(Group.App)))

            // チャンネル生成
            val createChannel: (Channel) -> NotificationChannel =
                    fun(channel: Channel) = NotificationChannel(
                            getString(channel.resId),
                            getString(channel.resId),
                            channel.importance).apply {
                        group = getString(channel.group.resId)
                    }
            manager.createNotificationChannels(arrayListOf(createChannel(Channel.News), createChannel(Channel.Important)))
        }
    }

    /**
     * Notificationをpostします.
     *
     * @param channel: Channel
     */
    fun post(channel: Channel) {
        val pending = TaskStackBuilder.create(this)
                .addNextIntent(Intent(this, MainActivity::class.java))
                .getPendingIntent(0, PendingIntent.FLAG_UPDATE_CURRENT)

        val notificationBuilder = NotificationCompat.Builder(this, getString(channel.resId)) // チャンネル指定
                .setGroup(getString(channel.group.resId)) // グループ指定
                .setColor(channel.color)
                .setSmallIcon(R.mipmap.ic_launcher_round)
                .setStyle(NotificationCompat.BigTextStyle()
                        .setBigContentTitle("Title")
                        .bigText("Message"))
                .setAutoCancel(true)
                .setVisibility(channel.visibility)
                .setContentIntent(pending)

        manager.notify(notifyId.incrementAndGet(), notificationBuilder.build())
    }
}

注意事項

ここまで頑張って実装してもうまく動作しない場合というのがあります。 それはFCMの通知メッセージをアプリがバックグラウンドで動作中の時に受信した場合です。

Firebaseの公式ドキュメントにも記載されていますが、 通知メッセージの場合は通知トレイに直接通知されてしまうので、アプリ側でハンドリングすることができません。

その場合、一部の設定項目をAndroidManifestファイルに定義することができます。 定義できる項目は以下の通りです。

  • アイコン
  • アイコンの背景色とアプリ名の文字色
  • デフォルトチャンネル(一つのみ)

具体的な定義の仕方は以下の通りです。

<!--アイコン-->
<meta-data
    android:name="com.google.firebase.messaging.default_notification_icon"
    android:resource="@drawable/ic_stat_ic_notification" />

<!--アイコンの背景色とアプリ名の文字色-->
<meta-data
    android:name="com.google.firebase.messaging.default_notification_color"
    android:resource="@color/colorAccent" />

<!--デフォルトチャンネル-->
<meta-data
    android:name="com.google.firebase.messaging.default_notification_channel_id"
    android:value="@string/default_notification_channel_id"/>

上記の通り、設定できる項目がかなり限られてしまいグループを指定することができません。 またデフォルトチャンネルの指定を忘れても通知は表示されますが、勝手に変な名前のチャンネル名が登録されます。

まとめ

Android 8以上におけるNotification Channelの紹介 & 実装方法の説明をしました。 これから慌ててtargetSdkVersionを26以上に上げるよって方々の参考になると嬉しいです。 それでは良きAndroidライフを!