エンジニアリングリード1年目を終えて

2021年の3月からエンジニアリングリード（以下、EL）という職位に就き、気がつけば1年と5ヶ月が経過していました。

なんとなく文字に起こしておきたいことがある気がするので、とりあえず書いてみます（まとまるかわからないけど）。

GMOペパボ株式会社におけるELとは

まず前提として、自分は現在GMOペパボ株式会社（以下、ペパボ）に所属しています。

ペパボにおけるエンジニア職位制度は次のような構造になっています。

出典: ペパボのエンジニアの各種制度 2020 夏 - Pepabo Tech Portal

4等級以降のキャリアが「専門職としてのライン」と「マネジメントとしてのライン」に分かれており、ELは後者の4等級として定義されています。

平易な表現をすると「マネジメントも行なうエンジニア」ということですね。

ELとマネージャーの違い

「マネジメントを行なう」という表現を見聞きすると、世の中でよく言われる「マネージャー」や「管理職」と同じことをやるのか？という疑問が出てくると思います。

ペパボでは「マネージャー」という職位は6等級として別途存在しています。（その下位の職位として「サブマネージャー」という4等級の職位も存在します）

マネージャーに求められる要件は大きく4つです:

• ビジネスパフォーマンス（組織目標、予実管理など）
• リスクマネジメント（法的リスク、セキュリティリスクなど）
• ピープルマネジメント（労務管理、エンパワーメントなど）
• ペパボピープル（社内文化、社内ルールなど）

この4要件を細分化したN個（なんとなく具体的な数字は伏せます）の項目にもとづいてマネージャーの評価が行なわれます。 （そのほかマネージャー間の360度評価もあるようですが割愛します）

一方で「マネジメントも行なうエンジニア」としてのELは、上記のマネージャー要件のうち一部の項目が評価の対象となります。具体的には次のとおりです:

• ビジネスパフォーマンスの一部（組織目標）
• リスクマネジメントの一部（セキュリティリスク）
• ピープルマネジメントの一部（エンパワーメント）

このことからELは、

「マネージャー要件における技術的な観点が不可欠な項目（プロダクト開発による組織目標の達成、継続的なメンテナンスによるセキュリティリスクの回避、技術的な助言を伴う成長支援、など）について成果を出すことが求められている」

と捉えることができると思います。

また、マネージャーに求められる予実管理、労務管理、社内文化などの項目は、ELに求められる項目には含まれていないことも大きな違いです。

ELに対する技術的な評価

ELはあくまでエンジニア職のなかの1つの職位です。 なので、もちろんエンジニアとして技術的な観点で成果を出すことも求められます。

ELの技術的な評価は、上に示したエンジニア職位制度の全体像の図における3等級エンジニア「アドバイザー」として評価が行なわれます。

ELに求められること

ここまでをまとめると、ELは

• マネージャー要件の一部（技術的な観点が不可欠な）項目で成果を出すこと
• 3等級エンジニアと同等の水準で技術的な成果を出すこと

が求められています。（と、自分は思っています）

1年目でやってきたこと

1年目（2021年3月〜2022年2月末）の期間は
ビジネスパフォーマンス : リスクマネジメント : ピープルマネジメント = 1 : 6 : 3
くらいの比率でやってきたと思います。

会計周りのシステム化

経理部門の方と密にコミュニケーションを取りながら、残高計算や売上計算のシステム化、会計基準の変更など対応をしました。 これは数字に誤りがあると会社としてリスクを抱えることになるので、リスクマネジメントの一環として取り組んでいました。

脆弱性診断やシステムバージョンアップの対応

自分が担当しているいくつかのサービスのなかで一番古いサービスについて、Ruby、Rails、そのほか各種ライブラリのバージョンアップや脆弱性診断の結果に対する対応を行ないました。これもリスクマネジメントに関する取り組みです。

オンボーディング、1on1、評価

3等級で入社したメンバーのオンボーディング、部署内のエンジニアのうちの3名の評価および定期的な1on1を行ないました。このうち2名は2等級から3等級に昇格し、自分のことのように嬉しかったです。もちろん本人たちの頑張りの賜物なんですが、その頑張りがしっかり評価に反映されるよう、毎月の1on1と評価の際の資料作成フォローを妥協なく行なった自負があります。これはピープルマネジメントに関する取り組みだと捉えています。

セールに向けたパフォーマンスチューニング

細かい対応をいくつかやりました。これはビジネスパフォーマンスに関する取り組みだと捉えています。

就任前後での変化

VPoEもSELもELも、人間（それはそう）

週イチでVPoE、SEL（ELの上位職位）、ELで集まって経営会議の内容や各部門のトピックを共有する会をやっています。

就任前はそのメンバーと仕事について話す場はあまりなかった（部署が異なるメンバーは特に）ので、各部署でエンジニアをバーンと引っ張っているスーパーマンたち、みたいな印象がありました。

就任後は毎週の共有会で、悩んでいることを解決するまでの過程を知ったり、喜ばしいトピックをみんなでワイワイしたり、微妙なトピックについてみんなで「うげぇ〜」と言ったりして、みんな人間なのよなぁ〜という当たり前のことを思ったりしました。

これまでよりも広い範囲で物事を考える

部署を跨いで話す機会が増えたことで、自分の部署で抱えてる課題を同じ課題をあの部署でも抱えてるな〜などがわかるので一緒に解決できないか〜と考えるようになりました。

どうやったらもっと良くなるか考える

現状を受けとめて、どうやったらよくできるかを前向きに考えるようになりました。

根は結構ネガティブでネガティブシンキングには自信があるんですが、周囲のSELやELの影響を受けてか、大変なことでもポジティブに進めることができるようになってきました。

メンバーにもっとやってもらえるように、と考える

評価を担当するようになったからか、エンジニアメンバーに対して

• 今やっていることをもっと拡張してもらうためには
• 今やっていないことをできるようになるためには

という観点で、どうすればいいか考えるようになりました。

今後やっていきたいこと

ビジネスパフォーマンスをもっとやっていく

1年目は特に、組織目標を達成するための動きが全然できてなかったので、2年目はやっていきます。

比較的最近のアクションだとPayPayでお買い物できるようにしたり、セールやCM対策のパフォーマンス改善に取り組んだり、ブックマーク機能でお買い物を便利にしたり、などやってました。もっともっとやっていきます。

自分の中にリトルSELを育てる

先日同じ部署のSELである@kurotakyと1on1をして、自分をエンパワーメントしようとしてくれているのを感じました。

お互いに感じている課題を共有して、期待していることも話してもらい、よっしゃ、より高い視点と広い視野でもっと色々やっていくぞ、という気持ちになったので、日々のお仕事を「これ@kurotakyだったら何て言う？ほかのSELだったら何て言う？」という視点で考えながら過ごして行こうと思います。

とはいえ無理せず着実に

無理して何かが壊れてしまっては、結果的にやりたいことが実現できなくなってしまうので、適度にやすんで自分をコントロールしながらやっていきます。

おわりに

思ったよりもたくさん書いちゃいました。ELについて書きたいな〜とずっと思っていたので書けてよかったです。

昼からジョナサンでハイボールとイカフライを食べながら書きました！うまい！よっしゃ〜、おしまい！

発表資料をつくるときに気をつけていること

先日とある発表資料をレビューする機会がありまして、自分はこういうことに気をつけているよ〜というフィードバックをしたんですが、フィードバックをしながら「あ、自分これ、結構大事にしているなぁ」と改めて気づいたので書き留めておきます。

気をつけていること

気をつけていることはこの3点です。

1. 想定するターゲット（聴講者、読者）は誰なのか
2. この発表の前、ターゲットはどういう状態なのか
3. この発表を経て、ターゲットにどのような状態になってほしいのか

気をつけている、というか、この3点を直接的に資料に盛り込んでしまうことが多々あります。

1. あなたに聴いてほしいんです
2. あなたはいまこういう状態なんじゃないかなぁ？とわたしは想像しています
3. この発表のあと、あなたにこういう状態になってもらいたいなと思ってます

といった感じですね。

伝えたかったことがまとまる

これを書いておくと、自分自身が「結局だれになにを伝えたかったんだっけ？」と迷子になるのを防げます。

あと、案外、そもそも伝えたいことがふわふわしてたりします。

ターゲットがどういうシーンでこの資料に触れるんだっけ？オフラインで同じ空間で発表を聴いてるんだっけ、それとも自宅でオンラインで？あるいは録画を見る？電車で資料を読むだけ？なども、大事かなと思ってます。

心構え

聴く側（読む側）も心構えができます。

例えば

「私はこの発表のターゲットのド真ん中だな。しっかり聴こう」とか

「自分は過去にはターゲットにいたけど、今は少し違う位置にいるな。その視点からターゲットの人に役立つ質問とか助言とかできるかも」とか

「自分は全然ターゲットじゃないや。聴かないで他のことしよう」とか

です。

立体的になる

結果的に構造が立体的になると思っています。

「発表している人」と「それ以外」という二分類の構造じゃなくて、

「発表している人」と「ターゲットとなる人」と「そのほか多種多様な人」という構造になります。

「そのほか多種多様な人」の中で「発表している人」のねらいに共感した人は、ねらいに沿ったアクションをしてくれる（発表中にSNSで補足説明をしてくれたり、発表後の雑談でフォローしてくれたり）ことがあって、そうなるとラッキーです。

あとは「ターゲットとなる人」が予想より少なかったとか、「こういうターゲットにした方がよかったんじゃないか」などのフィードバックも得やすくなるかなって思ってます。

みなさんはどうですか

みなさんは何か気をつけてることありますか？

ご意見ご感想をお待ちしてます。ではまた。

Discordでの音声収録: Craigを使ってみた

Discordでの音声収録の手段として Craig というボイスチャット録音Botを使ってみました。

はじめに

Craigとは...

Craigは、Discordでマルチトラック・マルチチャンネル録音するためのVC録音Botです。Craigが録音した音声を、それぞれの発言ユーザーごとに分割された音楽ファイルとして取得できます。ユーザーごとに音声を調整・カット・編集できるため、ポッドキャストや実況プレイなどの録音に最適です。

です。ポッドキャストの音声収録をDiscordでやってみたいと思い至って、Craigに辿り着きました。この記事の想定読者と参考資料は次のとおりです。

想定読者:

• Discordの基本的な使い方はわかる
• Discordのボイスチャットを手軽に録音したいと思っている

参考資料:

1. Craig Records! (Craigの公式サイト)
2. 題名「バーチャルポッドキャストの録音方法 ~ DiscordとCraig

Discordの音声設定を調整する

参考資料2に記載されているとおりに設定を調整します。参考資料2の文章を引用しながら説明していきます。

ユーザーネームの左下のユーザー設定の歯 車アイコンをクリックして、左側の音声•ビデオをクリックする。

話している最中は、入力モードを音声検出に設定するとプッシュトゥトークの様に常時ボタン を押し続ける必要がない。

Discordの音声入力に「プッシュトゥトーク」というモードがあることを初めて知りました。デフォルトでは「音声検出」になっていると思います。

「プッシュトゥトーク」はキーボードの指定したキーを押し続けている間だけ音を拾ってくれるモードなんですね。「基本はミュートにしているけどしゃべりたい時だけキーを押して喋る」というシーンで役立ちそう。Zoomでもミュート時に同じ機能があるはず（確かスペースキー）。

Discordは話し始めと最後をカットしてしまう傾向があるので、入力 感度を決定し「自動」を無効化してからスライダーを左端に移動し、できるだけ音声が途切れ なくする必要がある。

Discordが自動で音声を色々チューニングしてくれているという話は何度か聞いていたけど、話し始めと最後をカットするなどもやっているんですね👀自分が音声収録をする目的はポッドキャストで、できるだけそのままの音声を使いたいので、カットされては困ります。記事どおりに設定を変更しました。

エコー除去と音量調節の自動化を無効化することで、複数の人達が同時 に離す時に迷惑にならない役立ちとなる。設定によってはノイズ低減もオフ状態にする必要が ある。結局は全ての設定をテストすることでポッドキャストの音声を改善できる。

ほ〜、エコー除去などの音声処理もやっているんですね👀 複数人での会話の際にこの辺りの処理が余計になる場合がある、ということらしいです。自分の場合はふたりで会話する音声を収録するので、ここも記事どおりに設定を変更しました。

Craigを使う

まずは https://craig.chat/home から、自身のDiscordサーバーにCraigを招待します。招待が完了したら、CraigのBotがテキストチャンネルに入るはずです。

つぎに、適当なボイスチャンネルを開始します。その状態でテキストチャンネルで /join コマンドを打ちます。

すると、いま自分が入っているボイスチャンネルにCraigのBotが参加し、「Now Recording」という音声とともに録音が開始されます。

録音を終了するときは /stop コマンドを打ちます。

録音された音声データはCraigのWeb画面から様々な形式でダウンロードすることができます。Web画面のURLはCraigのBotから送信されてきます。/join コマンドを打った直後にこのようなダイレクトメッセージを受信しているはずです。

ダウンロードできるデータ形式は次のとおりです。

バックアップ用Bot「Giarc」も使う

実はCraigには、Giarcというバックアップ用のBotも存在しています。

https://craig.chat/home/giarc/

CraigはDiscord用の素晴らしい録音ツールですが、バグやハードウェア障害は常に付き物です。この問題を少しでも緩和するために、Craigの2番目のインスタンスであるGiarcを、Craigとは異なる地域にある別のサーバーで動かしています。 GiarcはCraigの代役なので、Craigと一緒に使用する必要があります。両方のBotをサーバーに招待し、VCに参加させてください。

使い方はCraigと同じです。

使ってみての感想

手軽に利用できて心地良かったです。オープンソース https://github.com/CraigChat/craig なのもすごい。感謝ですね〜。

Craigで録音した音声データは、一部の会話が飛んでしまっていてヒヤヒヤしましたが、Giarcのほうは欠損なく録音できていたのでなんとかなりました。両方あってよかったです。

Giarcのページを改めてみてみると

Giarcは フランスで、Craigは カナダで稼働しています。そのため、もしあなたがヨーロッパやアジアから接続している場合、CraigではなくGiarcのダウンロードリンクを使った方がいいかもしれません。ご自由にどうぞ！

とのことだったので、今後はGiarcをメイン、Craigをバックアップ、という形で使おうと思います。

あとこれはZoomとDiscordとを比較しての感想ですが、Discordの方が断然会話しやすかったです。 Zoomと比べると「遅延が少ない」「会話が重なったときに音声がつぶれにくい」という印象を受けました。

引き続きDiscordを使っていこうと思います。 Craig以外の録音手段もあれば知りたいと思っているので、知っている方がいたらぜひ教えてください〜。ではまた。

Railsアプリケーションで遅い画面を産まないためにチェックすること

Webアプリケーションを運用する中で「この画面が遅いね、もっと動作を速くしたいね」というシーンが度々ありました。 その都度、その時思いつくさまざまな対応をしてきたので、それを思い出しながら雑にメモしておきます。

自分は主にRailsでアプリケーション開発をしているので、その前提で「遅い画面を産まないためにチェックすること」という視点で書き残します。 （思いついたら追記します）

Railsのキャッシュ機構を活用できそうか

Rails のキャッシュ機構 - Railsガイド を頭に入れておきましょう。 どれも便利です。

あと、オプションのところに記載されている race_condition_ttl も便利です。使いましょう。

マルチプロセスで同じエントリが同時に再生成されてキャッシュが無効になる（dog pile効果とも呼ばれます）ときに発生する競合状態を防止するのに使います。このオプションは、新しい値の再生成が完了していない状態で、無効になったエントリを再利用してよい時間を秒で指定します。

N+1問題が発生してないか

初歩的な話ですが preload と eager_load を適切に使っているかを確認します。

この辺りの挙動については2014年の記事ですが ActiveRecordのjoinsとpreloadとincludesとeager_loadの違い - Qiita が分かりやすいです。

個人的には includes はコードリーディングしたときに挙動を想像しにくいと感じるのであまり積極的には使いません。*1

目的を意識しながら preload と eager_load を使い分けて実装するようにしています。*2

N+1問題の検知については、 bullet を導入して、CIの実行時に bullet がテストコードでN+1問題の発生を検知したらCIをfailさせるように設定しておくと便利です。

適切にindexが張られているか

クエリの実行計画をみます。EXPLAIN しましょう。

巨大なテーブル同士のJOINや不要なJOINがないか

巨大なJOINがいくつもある場合は、テーブル構成を見直すきっかけになります。

また、古い機能の名残りでいまは必要のないJOINが含まれていた、といった場合もありそうなので合わせてチェックしましょう。

Materialized View が活用できそうか

例えばランキング表示などために既存のテーブルをいくつか組み合わせて集計する必要がある時など、画面へのアクセスのたびに集計用の重いクエリが発行されてしまう場合があります。そんな時は Materialized View を作って集計結果を記録しておくと便利です。ただし、即時性が必要とされない画面だけで使える手段ですね（1時間毎に最新の集計結果になっていればOK、などであれば適していそう）

Railsアプリケーションの場合は scenic がとても便利です。

*1: includes した association に対する joins, references などによって発行されるクエリの挙動が変わるため

*2:既存コードで includes が使われている場合はそのまま使うことはあります

Podcastの音源を編集するときにやっていること

2021年10月からPodcast てくてくラジオ • A podcast on Anchor をやっています。

自分はエピソード番号が奇数の回の編集をやっていて、エピソード17 から音源を編集し始めたので、どんな感じでやっているかを記録しておきます。（2週間に一度しか作業しないから、作業方法を忘れてしまう。自分のためにかきます）

前提

• Podcastのパーソナリティは二人です（二人分の音源を編集します）
• 収録はZoomの録画機能を使っています
• 編集はGarageBandを使っています

事前準備

Zoomの設定

Zoomの「設定 > レコーディング > 参加者ごとに個別のオーディオファイルにレコーディング」をONにします。

編集の際に二人の声の大きさを調整したいので、この設定をしています。

BGMを探す

フリー音源をダウンロードできるWebサイトからBGMを探しておきます。

直近ではこのBGMを使っています。

• Make a field Musicさんの「Miniature house」
• MusMusさんの「FM Kid's」

収録

Zoomミーティングの録画機能でおしゃべりする様子を録画します。

ミーティングを終了すると、このようなファイルができます。

編集

GarageBandでの作業です。

二人の音源と1曲目BGMを貼り付ける

音声ファイルをドラッグ&ドロップで貼り付けます。

音量をざっくり調整する

聴きやすくなるように調整します。

てくてくラジオでは、相対的に自分の声が聞き取りづらい印象がある（声が低いから？それとも小さい？）ので、雑にボリュームを +3.0dB しています。

また、BGMは大きすぎるのでいったん -35.0dB に設定します。

会話音声の冒頭をカットし、BGMに合わせる

録画ボタンを押してから話を始めるまでに余計な音声があるので、まずはそれをカットします。

shift キーを押しながら二人の会話音源をアクティブにして、

カットしたい位置に再生ヘッドを移動した状態で command + t をし、

不要になった部分を消します。

その後、BGMの良きタイミングに合わせて会話が始まるように、会話音源の開始位置を左右に調整します。

BGMの音量を冒頭だけ大きくする

BGMの冒頭だけ大きくして、会話が始まる頃には小さくなるように設定します。

GarageBandの「オートメーション」機能をONにします。

これをすると、音声トラックに黄色い横線が表示されます。

この黄色い線上の任意の箇所をクリックすると黄色い丸ぽちができ、それを上下に動かすことでボリュームを調整することができます。

最初は -10.0dB で、会話が始まる直前にぐっと下げて -35.0dB になるように設定しています。

BGMの繰り返しを設定する

BGMのトラックを右クリックして、ループをONに設定します。

話題の転換のタイミングで、2曲目のBGMに切り替える

この作業はこれまでやってきた作業を組み合わせて実現します。

1. 話題の転換点で会話音声をカットする
2. 1曲目のBGMのボリュームを0にする
3. 2曲目のBGMのトラックを追加する
4. BGMの冒頭が大きく、会話の開始時には小さくなるように調整する
5. 会話の開始位置を調整する
6. BGMの繰り返しを設定する

会話音声の最後をカットし、BGMをフェードアウトする

おわりに

だいたいこんな感じで作業しています。

作業時間は「会話音声の時間 + 10~15分」くらいです。

GarageBandに詳しい方がいたら「もっとこうやると良いよ〜とか作業がラクになるよ〜」などのアドバイスをいただけると嬉しいです。 （特に「オートメーション」機能を使っているのに手動でポチポチと音量を調整しており、「"オートメーション"という機能名からして自動で音量をいい感じ調整してくれる機能なのでは...？使い方が違うのか...？」と疑っています。）

ではまた。

GoogleスプレッドシートでFILTERした範囲を結合する

またGoogleスプレッドシートの話です。

このような2つのテーブルがあるとします。

これらのテーブルをもとに

• num が奇数の行だけを抜き出して縦方向に結合したテーブルA
• num が偶数の行だけを抜き出して縦方向に結合したテーブルB

を作りたいとしましょう。

結論ファースト

これでいけます。

テーブルA

=FILTER(
  {
    IFNA(FILTER($A$3:$B$5,MOD($A$3:$A$5,2)=1),{"",""});
    IFNA(FILTER($D$3:$E$5,MOD($D$3:$D$5,2)=1),{"",""})
  },
  {
    IFNA(FILTER($A$3:$A$5,MOD($A$3:$A$5,2)=1),{""});
    IFNA(FILTER($D$3:$D$5,MOD($D$3:$D$5,2)=1),{""})
  }<>""
)

テーブルB

=FILTER(
  {
    IFNA(FILTER($A$3:$B$5,MOD($A$3:$A$5,2)=0),{"",""});
    IFNA(FILTER($D$3:$E$5,MOD($D$3:$D$5,2)=0),{"",""})
  },
  {
    IFNA(FILTER($A$3:$A$5,MOD($A$3:$A$5,2)=0),{""});
    IFNA(FILTER($D$3:$D$5,MOD($D$3:$D$5,2)=0),{""})
  }<>""
)

解説

テーブルA

テーブルAを作る手順をやっていきましょう。

まずはテーブル1から奇数の行だけを抜き出してみます。FILTERを使えば簡単です。

=FILTER($A$3:$B$5,MOD($A$3:$A$5,2)=1)

同じ要領でテーブル2からも奇数の行だけを抜き出してみましょう。

=FILTER($D$3:$E$5,MOD($D$3:$D$5,2)=1)

この2つを縦方向に結合しましょう！

縦方向の結合は ={範囲;範囲} という式で書くことができます。

={
  FILTER($A$3:$B$5,MOD($A$3:$A$5,2)=1);
  FILTER($D$3:$E$5,MOD($D$3:$D$5,2)=1)
}

お〜、できました！（ほんとか？）

テーブルB

この勢いで、テーブルBも作っていきましょう。 テーブルBは偶数の行を抜き出したいので、↑の式を MOD(ほげ,2) = 0 で書き換えれば良いはずですね。

={
  FILTER($A$3:$B$5,MOD($A$3:$A$5,2)=0);
  FILTER($D$3:$E$5,MOD($D$3:$D$5,2)=0)
}

あら〜？エラーになってしまいました。

それもそのはず、FILTER関数は条件に一致する行が見つからないとき #N/A を返します。 ref: FILTER - ドキュメント エディタ ヘルプ

今回はテーブル1に偶数の行がないので、

={
  FILTER($A$3:$B$5,MOD($A$3:$A$5,2)=0);  <- ここで #N/A エラーになる
  FILTER($D$3:$E$5,MOD($D$3:$D$5,2)=0)
}

という状況です。

配列の #N/A エラーを回避する

#N/A エラーを回避する方法としてIFNA関数があります。

https://support.google.com/docs/answer/9365944?hl=ja IFNA 関数 - ドキュメント エディタ ヘルプ

IFNA関数の第一引数には「#N/A エラーになる可能性がある値」を、第二引数には「第一引数が #N/A だった場合に代わりに表示する値」を指定します。

つまり、今回の場合では

={
  IFNA(FILTER($A$3:$B$5,MOD($A$3:$A$5,2)=0),ほげ);
  IFNA(FILTER($D$3:$E$5,MOD($D$3:$D$5,2)=0),ふが)
}

という感じにしてあげれば、テーブル1またはテーブル2に偶数の行がなくても #N/A エラーは発生しなくなる、ということですね！

さて、次に問題となるのは、↑の ほげ や ふが になにを入れればいいの？ということです。

いまIFNA関数の第一引数に渡している FILTER($A$3:$B$5,MOD($A$3:$A$5,2)=0) と FILTER($D$3:$E$5,MOD($D$3:$D$5,2)=0) は、どちらもN行2列の配列です（それぞれ条件を満たす行があれば）。

このN行2列の配列を縦方向に結合しようとしているわけです。

複数の配列を縦方向に結合するとき、列の数が一致している必要があります。

つまり、 #N/A エラーになった時も常に同じ列数の配列を返すようにしてあげれば良い、ということですね。

今回は1行2列の空文字列の配列 {"",""} を返すことにしましょう。

={
  IFNA(FILTER($A$3:$B$5,MOD($A$3:$A$5,2)=0),{"",""});
  IFNA(FILTER($D$3:$E$5,MOD($D$3:$D$5,2)=0),{"",""})
}

お〜、ここまできました。

無駄な空行を無くす

↑の画像は、テーブル1に偶数の行がないために1行2列の空文字列の配列 {"",""} が表示されていて、不要な空行が残ってしまっています。

最後にまたFILTER関数を使って

=FILTER(
  該当の範囲, 
  該当の範囲の1列目<>""
)

という式を作れば完了です。

これを書き換えると、こうなります。

=FILTER(
  {
    IFNA(FILTER($A$3:$B$5,MOD($A$3:$A$5,2)=0),{"",""});
    IFNA(FILTER($D$3:$E$5,MOD($D$3:$D$5,2)=0),{"",""})
  },
  {
    IFNA(FILTER($A$3:$A$5,MOD($A$3:$A$5,2)=0),{""});
    IFNA(FILTER($D$3:$D$5,MOD($D$3:$D$5,2)=0),{""})
  }<>""
)

はい、これが「結論ファースト」で示したテーブルBの式ですね！

改めてテーブルA

さて、テーブルAはテーブルBの奇数版なのでMODのところだけ書き換えて

=FILTER(
  {
    IFNA(FILTER($A$3:$B$5,MOD($A$3:$A$5,2)=1),{"",""});
    IFNA(FILTER($D$3:$E$5,MOD($D$3:$D$5,2)=1),{"",""})
  },
  {
    IFNA(FILTER($A$3:$A$5,MOD($A$3:$A$5,2)=1),{""});
    IFNA(FILTER($D$3:$D$5,MOD($D$3:$D$5,2)=1),{""})
  }<>""
)

ですね！

おわりに

GoogleスプレッドシートでFILTERした範囲を結合する方法をまとめました。

「配列の #N/A エラーを回避する」というのがミソですね。

ではまた！

Googleスプレッドシートで FULL OUTER JOIN っぽい挙動を実現する

Google スプレッドシートで2つの表を突合したいシーンがありました。

具体的には

• 特定の列をkeyとして、一方に存在してもう一方に存在しない行はどれか
• keyが同じ行は両方の表に存在するが、別の列の値が異なっている行はどれか

を知りたい、というシーンです。

FULL OUTER JOIN (完全外部結合) のようなことができればそれらを簡単に調べることができるのですが、Google スプレッドシートにその機能はないのでそれっぽい挙動を実現しようと試みました。

結論

以下の手順で実現できました。

1. 結合したい各表のkeyとなる列の範囲を指定する
2. 縦方向の範囲結合 {範囲1;範囲2} でkeyとなる列を縦に並べる
3. UNIQUE を使ってkeyの重複を排除する
4. keyの各行に対して、結合したい各表から VLOOKUP で必要な値を取得する

FULL OUTER JOIN (完全外部結合) とは

いつもお仕事でお世話になっている PostgreSQL のドキュメントに基づいて説明します。

https://www.postgresql.jp/document/13/html/queries-table-expressions.html

「限定的な結合」というセクションにある通り、テーブル結合の構文は次の通りです。

T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 ON boolean_expression

T1 , T2 は結合対象のテーブルのことですね。

これを FULL OUTER JOIN に絞って書き換えておくとこうです。

T1 FULL [OUTER] JOIN T2 ON boolean_expression

PostgreSQL の構文ではこの OUTER は省略可能です。

FULL OUTER JOIN については次のように説明されています。

まず、内部結合が行われます。 その後、T2のどの行の結合条件も満たさないT1の各行については、T2の列をNULL値として結合行が追加されます。 さらに、T1のどの行でも結合条件を満たさないT2の各行に対して、T1の列をNULL値として結合行が追加されます。

具体例を見てみましょう。ドキュメントの具体例をそのまま使います。

テーブル t1

num | name
-----+------
   1 | a
   2 | b
   3 | c

テーブル t2

num | value
-----+-------
   1 | xxx
   3 | yyy
   5 | zzz

を想定して、完全外部結合をすると

SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   2 | b    |     |
   3 | c    |   3 | yyy
     |      |   5 | zzz
(4 rows)

このような結果を得られます。

Google スプレッドシートで実現する

テーブル t1, t2 を用意しました。

次の4ステップで完全外部結合を実現します。

1. 結合したい各表のkeyとなる列の範囲を指定する
2. 縦方向の範囲結合 {範囲1;範囲2} でkeyとなる列を縦に並べる
3. UNIQUE を使ってkeyの重複を排除する
4. keyの各行に対して、結合したい各表から VLOOKUP で必要な値を取得する

1. 結合したい各表のkeyとなる列の範囲を指定する

各テーブルの num 列の値をkeyとして完全外部結合をしたいわけですね。

それぞれの範囲は $A$3:$A$5, $D$3:$D$5 です。

2. 縦方向の範囲結合 {範囲1;範囲2} でkeyとなる列を縦に並べる

まずはすべての num を洗い出すために {範囲1;範囲2} という記法を使って num を縦方向に並べます。

ちなみに{範囲1;範囲2} という記法については Google スプレッドシートで配列を使用する - ドキュメント エディタ ヘルプ に記載があります。

複数の範囲を単一の連続する範囲に結合できます。たとえば、A1〜A10 の値を D1〜D10 の値と統合するには、={A1:A10; D1:D10} の数式を使って連続した列に範囲を作成します。

3. UNIQUE を使ってkeyの重複を排除する

num の値に重複があるので UNIQUE 関数を使って重複を排除します。

ref: UNIQUE 関数 - ドキュメント エディタ ヘルプ

4. keyの各行に対して、結合したい各表から VLOOKUP で必要な値を取得する

あとは各行のそれぞれの列の値を次のような式で取得するだけです。

• t1.num: VLOOKUP($G3,$A$3:$B$5,1,FALSE)
• t1.value: VLOOKUP($G3,$A$3:$B$5,2,FALSE)
• t2.num: VLOOKUP($G3,$D$3:$E$5,1,FALSE)
• t2.value: VLOOKUP($G3,$D$3:$E$5,2,FALSE)

結果はこちら

まとめ

Google スプレッドシートで FULL OUTER JOIN っぽい挙動を実現する方法について書きました。

Google Apps Scriptで実装する方法もありますが、関数の組み合わせで実現する手段を知っておくと幅広いひとに活用してもらえそうだと思ったので記事にまとめることにしました。

この方法を模索する過程で範囲の結合をする記法があることを知れて、これは他にも色々と活用できそうなのでお得でした。

最近スプレッドシートをいじることが多いので、また何か見つけたら記事書こうと思います。ではまた。