先日、エクセルを使ったお仕事の入社試験問題の記事が話題になってましたが、ウチの職場はExcelからGoogleのスプレッドシートへかなりの部分を移行しようとしている真っ最中。
Excel仕事だけではダメなんですよ。
移行担当になってるもんですから、毎日スプレッドシートと格闘中。
独りで、通常の仕事の合間にやってるもんだから、まぁ進まない進まない。
元々「数年後になると誰が作ったかわからなくなる謎の動きをするVBA」を作る専門なので、GASも任されまして。
ノンプログラマーなんですが、何せVBAすら誰もいじれない（マクロで記録して終わりって感じの）職場。
個人情報が多いのでPCの制限が厳しいんですよね。
Gmailとその周辺は許されてるがインストールは不可って感じ。
なのでVBAとGASくらいしか手元にないってのもある。
今回は、先日のExcelのお仕事のごとく
「スプレッドシートを使ったお仕事」の基本問題
を作ったらどんな感じかな？と思い、4問だけ考えてみました。
このブログでは、あまり書かない珍しいジャンルですが。
ノンプログラマーにも優しいスプレッドシートの基本操作と関数のみ。
どれも基本中の基本ばかりなので難易度低目ですが、どうぞ。
【スポンサーリンク】
【Q1】
未入力のスプレッドシートがあります。
A1セルからA100セルに1から100までの連番を入れてください。
【A1】
Excelに慣れているとA1セルに“1”を入力して下にドラッグしてしまうんですが、スプレッドシートの場合、A1に“1”、A2に“2”を入れてドラッグしないと全部“1”になってしまうんですね。
ですから一番イージー（直感的）な操作は、
A1セルに1、A2セルに2を入れ、選択してから右下のハンドルをA100までドラッグする。
ですね。
ちなみにA1セルにROW関数"=ROW(A1)"を入れてドラッグしても可能。
ROW関数は、行番号を返す関数なのでA1を参照先に指定すれば“1”が表示されるという仕組み。
もっとスマートなやり方もありますが、それは次の問題の解答欄で。
※ROW - ドキュメント エディタ ヘルプ
【Q2】
“2018/4/14”と日付が入力されたセルがあります。
このセルの日付を、“2018年4月14日”に変えてください。
【 A2】
Excelで言う“セルの書式設定”操作ができるかどうかを確かめる問題です。
Excelだと右クリックなんかでもなんとかなるんですが、スプレッドシートの場合、上部メニューから
“表示形式＞数字＞表示形式の詳細設定＞その他の日付や時刻の形式”
を選択します。
表示されるメニューから“カスタムの日付と時刻の形式”で下部の年月日が表示されている例を選択するか、直接yyyy"/"mm"/"ddの"/"部分を書き換えてしまいましょう。
【Q3】
シートのA2:B13に作家アガサ・クリスティの作品名と執筆年、E2:F13に作品に登場する探偵の名前が入力されています。
C2:C13の空白セルに作品に登場する探偵の名前を、関数を使い入力してください。
ただし関数はC2だけに入力するとします。
【A3】
“=ARRAYFORMULA(VLOOKUP(B2:B13,E2:F13,2,FALSE))”
【A3解説】
三谷幸喜のドラマ「黒井戸殺し」を観ながら書いたのでこの問題になりました。
VLOOKUPを使うのはExcel仕事でも定番。
一応、簡単に説明すると、
“=VLOOKUP(参照セル,戻り値を含む参照範囲,戻り値の列番号,近似値or完全一致)”
ただ、この問題はExcelではなくスプレッドシートに関する操作ですので、ポイントはVLOOKUPではなく
“関数はC2のみに入力する”
部分になります。
まずVLOOKUPから考えますが、C2セルに入れる関数は、
“=VLOOKUP(B2,E2:F13,2,FALSE)”
がシンプルかと思います。
これをC2セルに入力するとB2セル“スタイルズ荘の怪事件”の横に“ポワロ”が表示されます。
ここでスプレッドシート独自のARRAYFORMULA関数を使います。
ARRAYFORMULAは公式から引用すると、
配列数式から返された値を複数行または複数列に表示したり、非配列関数で配列を使用したりすることができます。
ARRAYFORMULA - ドキュメント エディタ ヘルプ
スプレッドシート仕事では、これがなきゃ始まりません。
一行だけに入れると一気に入力され、関数をコピペする地獄（途中の関数が壊れる地獄もある）から解放されるやつです。
ARRAYFORMULAでVLOOKUPをネストにすると
“=ARRAYFORMULA(VLOOKUP(B2,E2:F13,2,FALSE))”
になりますが、これだけでは意味がありません。
ARRAYFORMULAで複数列に表示させるには、VLOOKUPの参照セルをB2だけではなくB13まで広げる必要があります。
ですから
“=ARRAYFORMULA(VLOOKUP(B2:B13,E2:F13,2,FALSE))”
にしましょう。
参照先を範囲で指定してあげれば、戻り値を表示するセルもそれに応じて広がります。
入力セルの動作を他セルでも真似するんですね。
ちなみに参照セルが不確定（動的）な場合、“B2:B”でもこの関数動きます。
下画像のような感じになります。
ただ、この場合、参照セルが空白の場合“#N/A”が表示されてしまいます。
そこでExcel定番のIFERRORを使って、
“=ARRAYFORMULA(IFERROR(VLOOKUP(B2:B,E2:F13,2,FALSE),""))”
なんて書き方もあります。
ちなみにARRAYFORMULAって綴りが長いのでショートカットが準備してあります。
セルにイコール“=”を入力し、次いで、
| WINDOWS | Ctrl ＋ Shift + Enter |
| MAC | Command + Shift + Enter |
でARRAYFORMULAが入力されるのでお試しあれ。
さて、ここでQ1の書き方に少し戻ります。
連番を打つ場合にもARRAYFORMULAは使えるんですね。
100までドラッグなんてめんどくさいでしょう？
Q1では、ROW関数（セル参照の行番号を返す）も使えると説明しました。
関数をわざわざドラッグしなくてもROW関数がA1からA100まで入力すれば連番が入力されるんですから、A1セルに
“=ARRAYFORMULA( ROW(A1:A100))”
と入力すれば一瞬で連番が作成されます。
ちなみに当たり前ですが、どのセルにでもこの関数をまんま入力すればそこから連番が作成されます。
【Q4】
※スプレッドシートAのシート1
※スプレッドシートBのシート1
作品名と短編・長編の種類がA:Bに入力されたスプレッドシートAのシート1（上画像）と、作品名だけが入力されたスプレッドシートBのシート1（下画像）があります。
スプレッドシートB＞シート1のB2:B13セルに各作品が短編か長編かを入力してください。
ただし関数はB2セルだけ入力することとします。
【A4】
“=ARRAYFORMULA(VLOOKUP(A2:A13,IMPORTRANGE("スプレッドシートAのID","シート1!A2:B13"),2,FALSE))”
【A4解説】
Q2が解ければQ3も同じく
“=ARRAYFORMULA(VLOOKUP(A2:A13,A2:B13,2,FALSE))”
なのはわかります。
ただこの設問では「別のシートを参照先にする」ことが求められます。
同じスプレッドシート（ブック？）内であれば参照セルの前にシート名をつければ動きます。
仮にこれが、隣のシート2を参照するだけであれば、
“=ARRAYFORMULA(VLOOKUP(A2:A13,'シート2!'A2:B13,2,FALSE))”
で動くんですね。
ただこの設問の場合は隣のシートではないので、IMPORTRANGE関数を使います。
指定したスプレッドシートからセルの範囲を読み込みます。
IMPORTRANGE - ドキュメント エディタ ヘルプ
これまたスプレッドシート仕事では、なくてはならない関数の一つ。
IMPORTRANGEですが、ちょっとクセがあります。
参照に際して一番最初に参照の許可をしてあげなければならないんですが、複雑な計算の関数に組み込むとうまく参照の許可が動かない場合があります。
ですから先に許可を与えてから関数を作って行きます。
1.まず参照先シート（スプレッドシートA）アドレスの
https://docs.google.com/spreadsheets/d/hogehoge/edit#gid=XXXXXXX
このhogehoge部分を確認します。
2.次にスプレッドシートB＞シート1のどこかのセルに
=IMPORTRANGE("hogehoge","シート1!A1")
と入力してください*1。
3.すると、
#REF!と共にアクセス許可を求めるポップアップが表示されるので、“アクセスを許可”をクリックしてください。
これで他スプレッドシートの参照が可能になりました。
一度許可さえ与えてやれば学習するので、上記の関数は消してしまっても大丈夫です。
次に先刻の
“=ARRAYFORMULA(VLOOKUP(A2:A13,'シート1!'A2:B13,2,FALSE))”
を加工。
他スプレッドシートを参照するようにIMPORTRANGEをネストします。
スプレッドシートB＞シート1のB2セルに、
“=ARRAYFORMULA(VLOOKUP(A2:A13,IMPORTRANGE("hogehoge","シート1!A2:B13"),2,FALSE))”
と入力してください。
するとB2からB13にそれぞれの短編・長編が表示されます。
お疲れさまでした。
最後に
……といった感じですがどーでしょう？
簡単すぎましたかね。
実際、毎日仕事をしているとIMPORTRANGEはまだしも、ARRAYFORMULAは使う。
データの管理と参照なので上に書いてるようなのは、毎日ちまちま書いてる。
ただExcelにない関数も結構あるので是非使えるようになって欲しいもの。
SQLライクな関数“=QUERY”も使えれば初心者卒業って感じでしょうが、そこまで求めても仕方ない。
QUERY使わなくてもフィルターかければ通用してしまうので。
QUERY - ドキュメント エディタ ヘルプ
なので上記のような感じになりました。
このくらい使えていれば、普段の仕事に支障はなさそうですし多少の効率化にはなる。
カスタム関数作れっていうわけじゃないんですから覚えれば簡単。
スプレッドシートの勘所は、getのたびにAPIを叩いて重くなりがちなGASの比重を下げて単純化して関数で始末するバランス部分でもあるかと。
だから関数もそれなり、GASもそれなりってのがいい。
ただ実際は、Excel使えるってもVBA書けるわけじゃなし、スプレッドシートをいじれてもGAS書けるわけじゃなし。
正直、VBAくらいは
「参照先のデータがズレたから参照も直しといてねー、宜しくー」
なんてノンプログラマーでも修正出来るようになって欲しいですけどね。
どーせ
Dim hoge as String
Dim i as Long
for i=2 to 200
hoge = cells(i,8)
next i
とかなんとかforでダラダラ順ぐり順ぐりに取得する単純なやつなんですし、ズレてもセルの番号くらい数えられるだろっていう。
近々、スプレッドシートでマクロが使えるようになるそうですが、「これうまく動かないから直しといてー」って依頼がVBAからGASに変わるだけなんじゃねーかっていう。
はぁ……。
以上、お目見汚しでございました。
売り上げランキング: 3,415
*1:ちなみにhogehoge部分にhttp://〜のアドレス全部を入力しても大丈夫です