コンテンツデザイン部の江川です。
データを扱う際に皆さんは何を使っていますでしょうか。様々あるとは思いますが、多くがexcelかGoogle スプレッドシート(以下スプレ)のどちらかを使用していると思います。
excelとスプレどちらを使うにも利点がありますが、多くの方がスプレってクラウド版のexcelでしょ。くらいの認識なのではないでしょうか。
私も最初はそうだったのですが、実は結構excelとスプレって違うんです。
そこで今回はクラウド化の波に乗ってexcelからスプレに変えた方やちょっとしたデータをみんなで触れる方が便利だからとスプレを使用している方に、今日から使えるスプレならではの便利関数を紹介します。
１.ARRAYFORMULA
スプレといえばこれ！といった関数です。スプレを愛用している方々の中にはこの関数が使えるからスプレにしているという方も少なくないでしょう。
ARRAYFORMULAとは一つのセルの式を配列形式で表示する関数です。
？
という方にもわかりやすいように、言い換えると一つのセルの数式をそれ以下の行or列に適応する関数です
以下のように点数から成績の値を算出する数式をIFS文で90点以上ならA、50~89はB、31~49がC、それ以下は赤点と定義します。
C3に数式を入れるともちろんC3セルに結果が算出され、この式を下に伸ばすと数式も下に伸びていきます。
しかし、ARRAYFORMULA関数を同じセルに適用すると……
このように、IFSのみの場合だとB3と一つのセルしか指定できませんでしたがARRAYFORMULAを加えることによってB3:Bの範囲全ての結果がC3:Cに反映されるようになります。
イメージでは数式の中でセルを指定していた部分を範囲指定に変えることでARRAYFORMULA式が機能するようになります。もちろんB3:B50のように特定の範囲も指定できます。
これの何が便利かというと利点は２つあります。
・セルを下に伸ばさなくても自動で入力されること。
→大したことないと思うかもしれませんが使ってみるとこれがすごく便利です。Excelで行っていた作業を単純に１段階減らすことになり、なおかつ漏れやミスが減ることが大きいです。
・計算処理が圧倒的に軽いこと。
→外部の方とデータを共有する時などに大きなデータでも、この関数を使って軽量化することでお互いストレスフリーで作業することができます。逆にスプレを共有して大量のセルに参照数式が入っているととても重くなり、お互いの作業効率が落ちてしまうことになるので気をつけましょう。
こちらがARRAYFORMULAを使った数式です。
ARRAYFORMULA(配列数式または、非配列関数で配列を使用)
例：ARRAYFORMULA(IFS(~~~)
2.IMPORTRANGE
こちらはスプレッドシートを使いこなす上で非常に重要な関数となっています。
これは先ほどのARRAYFORMULAフォルダとは違うIMPORTRANGEというタイトルのスプレのA1セルにIMPORTRANGE関数を適用したものです。
別のスプレッドシートから値を参照できるわけですね。
この関数は
=IMPORTRANGE(“スプレッドシートキー”,”そのスプレの範囲指定”)
となります。
スプレッドシートキーというのはそのスプレのURLのことです。関数にはhttps://~から入っていますが、キー部分は1H7Uj~の部分なので本来であれば不必要ですがどちらでも問題はありません。
なので今回で一番スマートなのは
=IMPORTRANGE(“1H7Uj2vFP64dCYtaKI_cEm7zlc0mYEowD5J2d9tRBfj0″,”シート1!A:C”)
ですね。
Excelでも別のフォルダからの参照はできるのですが、スプレの大きな違いはクラウド管理だということ。
IMPORTRANGEで出されたデータは元のスプレの値をリアルタイムで持ってこれるのでやはり複数人で同じデータを触る際に便利です。
いちいちURLをコピペするのが手間な人向け小技
画像のようなリストを作って、B列のキーを使う予定のフォルダにIMPORTRANGEします。
そのフォルダで別シートを参照する際はセル指定でIMPORTRANGEが使えます。
=IMPORTRANGE(‘画像シートの名前’!B2,”参照元シート名!範囲”)
つまり=IMPORTRANGE( まで入力してセルをクリックするだけでURLを入力しなくてもよくなる！という寸法ですね。
正直、上記の2つの関数をマスターするだけでスプレッドシートを使用していく上で十分に快適なデータ管理を行えるでしょう。
なので残り3つはgoogleならではの簡単で便利な関数を紹介します。
3.IMPORTHTML
例えばサイトで画像のような表のデータをスプレに入れたい時どうしますか？
いちいち手入力するのは手間ですよね。しかも頻繁に内容が変わるようなサイトの内容だったら毎回確認して変わってたら書き換えないといけない。
そんなお悩みを解決するのがIMPORTHTMLです。
こちらの関数はHTML内の表やリストをそのままスプレに持ってくることができます。
頻繁に使う機能ではないですが、資料やデータを作る際の時間短縮になると思います
=IMPORTHTML(“URL”,”table or list”,指数)
画像の場合だとURLのHTML内で２つ目のtableを展開しています。
ゲーム会社だと公式HPに載せるスケジュールテーブルとゲーム内の時間がズレないようにこういった関数を使って整合性を取る！なんて形もありかと思います！
4.IMAGE
スプレを他人に共有する際。資料として渡すのであれば画像があると伝わりやすいですよね。
イメージとしてネットで拾った画像を共有することは少なからずあると思います。
そんなとき、一々検索して画像を保存して貼り付けて…って自然にやると思いますが意外とめんどくさかったりします。
そこでこのIMAGE関数。
URLがあれば画像をスプレに表示させることができます。
=IMAGE(“URL”,モード,高さ,幅)
モードは以下のようにセル依存、アスペクト比依存、サイズ依存、自由のようにサイズを指定できます。
| 1 | アスペクト比を変えずに、セル内に収まるように画像サイズを変更します。 |
| 2 | アスペクト比を無視して、セル内に収まるように画像を引き延ばすか決めます。 |
| 3 | 画像を元のサイズのままにします。その場合、画像がトリミングされることがあります。 |
| 4 | カスタムサイズに変更することができます。 |
デザイナーさんなどは自社のサイトに画像を載せた際、そこにリンクしたこの関数を使うといちいち更新しなくて良いので便利かもしれません。
5.GOOGLETRANSLATE
英語のデータやいろんな英文を一気に翻訳したいときにこの関数は役立ちます。
この関数はセルのテキストをGoogle翻訳する関数なのです。
Google翻訳の利点といえばなんと言ってもコスト以上の精度と翻訳言語数にあります。
無料でここまでパッと翻訳できるサービスは他にないでしょう。
もちろん文章が長ければ精度は落ちますが、それでも昔に比べて相当な精度で翻訳を行ってくれます。
画像は単語帳をリスト化した物ですが、こう言った自分なりの単語帳を作る時などにも使えるかもしれませんね。
翻訳する数が膨大な場合はこの関数を使って一覧にすると纏まったデータになるかと思います。
最後に：次回へ続く
いかがでしたでしょうか？スプレを使う上で必須のARRAYFORMULAとIMPORTRANGE、その他３つは作業の効率化に役立つような小技的関数でした。
次回はデータ整理に便利な関数４選を紹介しようと思います。
