スプレッドシートというものは軽く見られがちだが、今週 Adam Engst が解説するように、現代のスプレッドシートにはただ単に計算を実行するよりも一段優れたことができる。その実例として、Adam は複雑な会計報告からデータを抽出してそれをもっと読みやすく理解しやすいものにする予算編成ツールをGoogle Sheets で作った体験を物語る。Apple の衛星通信による緊急 SOS 機能を iPhone 14 で使うことで、マウイ島の山火事で人命を救う役に立ったというニュースが届いた。今週の Do You Use It? アンケートでは、Adam が長年 Mac を使ってきた人たちの間での Launchpad の利用度について報告するとともに、新たにプロキシアイコンの使用について尋ねる。また、SanDisk ポータブル SSD の不具合と、CNET が SEO の目的で記事を削除していることについても少し考察する。今週注目すべき Mac アプリのリリースは Mactracker 7.12.7 と Cyberduck 8.6.3 だ。
以前は Twitter として知られていたサービス上に、Michael J. Miraflor が投稿した：
私の兄弟の女友達の従兄弟とその家族は、Maui で山火事が突然発生した時、火に囲まれ車の中に閉じ込められた。
携帯電話サービスは使えなかった。救急対応者と連絡を取れる唯一の方法はApple Emergency SOS しかなかった。文字通り彼らの命を救った。
私は、その 34 分間の地獄がどんなものであったか想像することすら出来ない。Emergency SOS は魔法であるが ("衛星通信による緊急 SOS と Find My 機能をテストする" 21 November 2022 参照)、そして iPhone はその使い方を順を追って説明してくれはするが、周り全部を火に囲まれたバンの中に閉じ込められた状態でそれをその通りにすると言うのは気が狂いそうになるぐらい大変であったろう。感想二つ：
- その iPhone の緊急連絡先は同時に恐怖に襲われたであろうし、このチャットを受け取って安心したであろう、受け取ったのはそれが終わってからなのであろうと思うが。
- 特に若者にとっては、この緊急サービス対応者が全部大文字でタイプしてきたのは、更にストレスのかかるものだったかも知れない。
何れにせよ、衛星経由の Emergency SOS サービスを開発したことに対して Appleに、 追い詰められた状況下で冷静さを保ち続けたこの iPhone 14 ユーザーに、この緊急サービス対応者に、そして実際に救い出してくれた消防署に対して賞賛を送りたい。
この様な話を通して、Apple がこの衛星経由の Emergency SOS 機能を、初期の２年間の無料期間の後にも、何らかの方法で利用ベースの課金にすると言った事を考える事を望みたい。ハワイの歴史的な町で山火事に囲まれてしまうかもしれない事態を予想して、事前に加入申し込みする人など誰もいないであろう。
討論に参加
我々の最初の記事 ""Do You Use It?" 調査を紹介する" (28 July 2023) で、皆さんはどれ位 Launchpad をお使いになるかを問うた。Launchpad は iOS の Home 画面経験を Mac にもたらそうとするグリッド型のランチャーである (元を辿れば 1990 年代初期の Apple の At Ease に影響を受けていたのかも知れない)。Apple はLaunchpad アプリを macOS に内蔵させていて、デフォルトの Dock 上には二つ目の場所に置き、更にキーボードショートカットやトラックパッドジェスチャを使っての呼び出しをし易くしている。
Launchpad は Mac に新たに来たわけではなく、2011 年に OS X 10.7 Lion で導入された。Apple の意図は、iPhone や iPad から Mac に来た人達に馴染みのあるユーザー経験を提供しようというものであったのであろう。我々の調査の結果は、多くの TidBITS 読者は - 殆どは昔からの Mac ユーザー - Launchpad を基本的に無視していることを示していて、800 人を超える我々の調査に対する回答者の 70% が一度も使ったことがないと言っている。しかしながら、Apple が定常的に言っているように、Mac 購入者のほぼ半分はこのプラットフォームに新しい人達だと言うことからすると、TidBITS の読者は典型的な代表とは言えないのかもしれない。
残りの 30% の内、毎日 (Daily) 使うと言ったのはたったの 7% で、残りの 23% はしょっちゅう (Frequently) か、たまに (Occasionally) に属する。私はこれ等の頻度に対してラベル付けをする時に意図的に曖昧にしたが、私の全般的な考え方は以下のようなものである：
- Daily: もし一つの機能を毎日使うのであれば、それはもう既に自分の作業習慣に組み込まれたものと言える。それは一日に数百回使う、或いは数回しか使わない事を意味するかも知れないが、何れにしろ、それはテック生活の一つの主柱と言える。
- Frequently: 私は皆さんにその使用頻度をもっと具体的に区分して尋ねたくなかった。何故ならば、Launchpad の様なものを、週に一回、二回、或いは三回立ち上げるかどうかを知っている人はまずいないであろうからである。 "Frequently" は、"Launchpad を毎日は使わないが、それを開くのも珍しくはない" という様な感じを代表してくれると思った。
- Occasionally: この選択肢は Launchpad を余り通常ではないやり方でたまには使うという人のためである - アプリを Mac App Store からアンインストールするためにこの様な使い方をする人もいる (下記)。
もし Launchpad を試してみたい、或いはデフォルトのキーボードショートカットやトラックパッドジェスチャが邪魔をするというのであれば、macOS 13 Ventura の System Settings > Keyboard > Keyboard Shortcuts > Launchpad & Dock か System Settings > Trackpad > More Gestures でそれを制御することも出来る。(macOS 12 Monterey かそれ以前での System Preferences のネスト化された場所でも同様である。)
Launchpad が立ち上がった後の使い方は簡単である。アイコンをクリックすればそのアプリが起動される。幾つか文字をタイプすれば - 最初に Search フィールドをクリックする必要もない - 示されているアイコンをフィルター出来る。アイコンを再配置するにはドラッグする。一つのアイコンを別のアイコンの上にドラッグすればその両方を含むフォルダが作られ、そこに他のアイコンを入れたり出したり出来る。フォルダをクリックすればそれが開く。Option キーを押すか、一つのアイコンを長押しすれば揺れモードになり、複数のアイコンを並び替えたり、X バッジをクリックする事で Mac App Store アプリをアンインストール出来る。必要なら、背景をクリックすれば揺れモードから離れられる。
Launchpad の 存在理由 は起動アプリなので、何故それを使わないかを説明した人の多くはアプリを手早く起動する他の方法を挙げた。代表例を挙げると：
Launchpad に関する問題の一つは、iOS Home 画面のように、デフォルトでは 35 の項目、5 行 x 7 列で、しか表示しないことである。もし Applications フォルダに 35 以上のアプリを持っていれば、Launchpad は追加の画面を生成する。Apple はこの格子サイズをアイコンと名前をより小さな MacBook 画面上でも読めることを考慮して決めたのかもしれないが、エンジニア達が格子状項目の構文解析の認知的負荷についての人的要因研究に基づいてこの格子サイズを決めたと思えるなら素晴らしいのだが。
何れにしろ、Applications フォルダにあるアプリの数に応じて (それ等全てが現れる)、Launchpad が有用性が高いのか低いのかに関して意見は分かれる。Launchpad は iOS と iPadOS にある App Library 機能を持たないので、画面数を減らすには二つの方法がある ("iOS 14 の App Library についてよくある質問" 9 September 2020 参照)：
- フォルダを作る： 上記したように、複数のアプリを含むフォルダを作成出来るが、整理するのは、iOS や iPadOS でと同様、手間がかかる。もしこの途を取るのであれば、自分の構成をバックアップするための Laine Lee のスクリプトを検討されたい。.
- 格子を調整する： 格子サイズを、例えば 10 x 7 に変更するには、下記のコマンドを Terminal に入力する。デフォルトサイズに戻すには、下記コマンドを末尾の
-int # 無しで繰り返すとデフォルトサイズに戻れる。このヒントをくれた David C. に感謝する！
defaults write com.apple.dock springboard-columns -int 10
defaults write com.apple.dock springboard-rows -int 7
killall Dock
私は iPhone から Mac に来たユーザーのどのぐらいの割合の人がアプリを開くデフォルトのやり方として Launchpad を見ているか知りたいと思うが、その数を決めるやり方を全く思い付けない。
討論に参加
私にはスプレッドシートを本格的に使った経験は一度もない。ライターとしての私の日常はワードプロセッサとテキストエディタの中で費やされてきた。もちろん私はスプレッドシートの使い方を知っているし、あとで調整したり更新したりする必要のある計算をするためにならば喜んでスプレッドシートを使いたいと思う。最近になるまでは、何か月もスプレッドシートを使わないまま過ごすことがよくあった。使わなかった理由の一部は、身近にいた人々がスプレッドシートをレイアウトプログラムとして使っていたからだ。その人たちはその書類をページレイアウトアプリで作成するツールもスキルも持ち合わせていなかった。これはある意味誘惑的だ。テキストをセルの中に入力するだけで。その“ページ”のどこへでも置けてしまうからだ。けれどもその作業が一回限りのものでない場合、Excel でそのページレイアウトの中にネジを一本打ち込んだだけで悲惨な結果に結び付いてしまう。
でもここ一・二年の間に、私は考え方を変えた。結局のところ、自分の内に潜むスプレッドシートの達人を解き放つには、それに相応しいプロジェクトこそが必要だったということなのだろう。せいぜい SUM と AVERAGE くらいしか使う機会がなかった私がマルチシートの検索の妙技を発見するまでの、この非常に個人的な進化の物語をここにお届けすることによって、驚くべきツールへと発展しつつある現代のスプレッドシートを、読者の皆さんご自身ももっと使いこなせるのではないかという気持ちになって頂けるならば嬉しい。
ツールの話が出たのでここで一つ断っておきたいが、私がしたことは Apple の Numbers や Microsoft Excel でも可能だろうとは思うけれども、私は Google Sheets を使って作業する道を選んだ。それは必ずしもこれが最もパワフルなスプレッドシートだからという訳ではなくて (比べたらどうなのか私は何も知らない)、私たちの場合は自分のスプレッドシートが複数の人たちから常に見えるようになっていることが必要で、そのための手間が最小限に抑えられていることが重要だったからだ。近頃の私が執筆仕事の大半を Google Docs でしているのも全く同じ理由からだ。それに、オンライン上にある私の書類を私のどのデバイスからでも開けるのがとてもありがたい。
予算編成ツールを設計する
2020 年に私は Finger Lakes Runners Club (FLRC) の代表者になったが、パンデミックのせいで 2 月より後の競技会がすべてキャンセルになってしまい、その結果クラブの財政運用が混乱に陥った。2020 年末に当時の会計担当者が 2021 年の予算を組む際には完全に推測によるしかなく、一年を通じて更新せざるを得ない状況となった。復活できる競技会もあれば、できないものもあったからだ。年度の途中で会計担当者が離任したので、私は友人の Charlie に頼んでその役割を引き継いでもらった。最初の仕事は 2022 年の予算を組むことだった。調べてみると、前任の会計担当者のやり方はごく単純なスプレッドシートで、手で入力した数を足し合わせる以外のことをほとんどしていなかった。私は数字を手でタイプし直すなんて嫌だ。そのためにコンピュータがあるのだから！ そこで私はより良い方法を考え始めた。
最初に問うべきはは予算の目的であった。FLRC は毎年 20 回程度競技会をしていて、私たちとしてはその多くが少しずつは収益を上げ、最低でも収支トントンになって欲しいと思っていた。そのうち 2 つは大規模な競技会で、これがクラブの収入の大きな部分を提供し、運用と活動、そして貯金に使われる。クラブ全体として継続可能な結果に結び付く限り、個々の競技会の監督が具体的にどれだけの額を支出しても私たちは気にしていなかった。過去には時として競技会の監督が思慮に欠ける計画を立てたために、大きな損失が出て問題になったこともあった。なので私は競技会の監督が普通でない出費をしようとする場合にそれを一定の範囲内に抑えられるようなガードレールを作りたかった。
最初に検討したのは QuickBooks Online だ。クラブはこれを帳簿に使っているので、予算編成にも使えないかと考えたのだ。確かにその機能はあるのだが、二つの理由で現実的でなかった。第一に、そのインターフェイスには過去の重要な情報が提供されないので、過去を材料に次の年の予算を見積もることができない。第二に、予算をさまざまな方法で書き出すことはできるけれども、私たちの場合は収入と支出を記録すると同時にその競技会の財務状況がどうなのかを監督に逐一報告するため、書き出しを頻繁にする必要があった。そこで、私は Google Sheets に目を向けた。これなら、競技会の監督がリンクを一つクリックするだけでその競技会の財務状況がどうなのかを最新の数字で見ることができるからだ。
競技会の現場の数字を見るための鍵となる報告書は QuickBooks Online にある活動明細書だ。個々の競技会がクラスとなり、書き出されればそれが一つのカラムとなる。個々の行はアカウント (登録者、賞品、掲示、その他) で、これはアカウントのチャートから取り込まれる。きちんとした表形式のデータだが、競技会もアカウントも数が多いのでどのフォーマットにしても上下や左右に何度もスクロールしなければならず、とても読みにくい。また、私としては個々の競技会を他と切り離して表示できるようにしたかった。巨大な PDF やスプレッドシートの中から自分の競技会を探し出して見て欲しいと監督に依頼するようでは、どうにもやって行けない。
こうして私に次のひらめきが舞い降りた。私はスプレッドシートのベテランでもなければプログラマーでもないかもしれないが、抽象化を理解することはできる。プログラミングをする際には、データをハードコード化するのを避けるために変数を使う。myVariable という変数は、そこに何を入れるかに応じて持つ値が変わる。スプレッドシートではセルの参照を使う。これで、例えばセル C32やカラム M の現在の内容に対して何らかの操作をすることができる。さらに素晴らしいことに、一つのスプレッドシートは複数個のシートを持つことができ、一つのシートの中のセルを別のシートから参照できる。
そして突然、私は自分が構築すべきスプレッドシートのアーキテクチャを心に描くことができた。一つのシートに、活動明細書が記される。これは、直接 QuickBooks Online から書き出されたものだ。それから個々の競技会ごとに別々のシートがあって、活動明細書から取り込んだデータを表示する。もっと素晴らしいことに、毎年の活動明細書を 2016 年まで遡って読み込むことができ、年ごとの履歴データもカラムに表示できる。この履歴データを利用することで実際に予算を組む際の参考になり、Charlie と私は過去のデータを (またそれら全体の平均も) 参照することで、次の年の収入と支出を予想しやすくなる。
魔法の公式を構築する
難しいのは私の構想を実装するところだった。一つのシートでデータを検索してそれを別のシート上に表示することが可能だと知ってはいたものの、実際にどうすればよいかは知らなかった。データをハードコード化した概念実証はすぐに作れて、それは前進だったけれども、どうすればそれを抽象化できるかを見極めるまでにはかなり時間が掛かった。最終的に、たくさんのウェブ検索と、多くの実例の研究、さらには Help > Function List を何度も開いて Google Sheets に何ができるか調べた結果として、私は次のような処方に行き着いた。
=IFERROR(HLOOKUP($A$2,INDIRECT("'"&E$4&" Overall'!$A$5:$X$250"), Utility!$A5, FALSE))
これを説明するのはなかなか大変だが、魔法の秘密はここにある：
IFERROR: このように公式全体を IFERROR 関数で包めば、残りの公式の部分が動作に失敗しても #N/A のようなエラーを返さずに空白を返す。失敗は主としてはるか昔の情報の中にデータが欠けているような場合に起こる。HLOOKUP: この HLOOKUP 関数が作業の大部分を担う。つまり、領域を検索して鍵を探し、見つかったカラムの中のセルの値を返す。これを設定するのに最も時間が掛かった。$A$2: 最初にすべきは HLOOKUP のための検索鍵を指定することだ。私の目標は特定の競技の情報を検索することだったので、当初私はここに競技の名前をハードコード化して、HLOOKUP が毎年の活動明細書の中からそれを見つけられるようにした。それで動作はしたけれども、そのようにすれば個々の競技シートごとに公式をカスタマイズし直さなければならなくなる。可能ではあるけれども、面倒であると同時に誤りを招きやすい。その代わりに私は変数を作成して、競技の名前をセル A2 に入れておき、絶対参照 $A$2 で検索するようにした。($ という文字は、公式を別のセルへコピーしても指定されたカラムや行が変わらないようにする。) 競技のために新規にシートを作成する際には、その競技の名前をセルに入力するだけでこの公式が正しいデータを検索するようになる。INDIRECT: HLOOKUP が必要とする次の情報は、検索すべき範囲だ。ここでもハードコード化を使って 2023 Overall!A5:X250 のようにして今年の活動明細書の中で必要なデータを探すようにもできるけれども、それを抽象化してどの年にも使えるようにするのはなかなか難しかった。(最初の４つのカラムは QuickBooks Online からの読み込みでマージされたものだったので検索を A1 から始めると失敗に終わる。なので A1 からでなく A5 から検索を始めなければならなかった。) この INDIRECT 関数が決定的に重要なのは、この関数が文字列によって指定されたセル参照を返すからだ。厳密な正確さが求められるので、この文字列を正しく構築するまでに長い時間が掛かった。"'"&E$4&" Overall'!$A$5:$X$250": 個々の履歴カラムはそれぞれ付随する年のシートからデータを探す必要があったが、年をハードコード化するのは望ましくなかった。私が見つけた解決法は、'"&E$4&" Overall' を使ってセル E4 から年を取り出し、それと単語 Overall をくっ付けることで正しいシートタイトルにすることだった。E は相対的 (つまり $ が付いていない) なので、この公式を別のカラムへコピーしても引き続き行 4 (こちらは $ が付いているので絶対的) から年が読み込まれる。絶対的な範囲 !$A$5:$X$250 を指定するのは簡単だった。一番難しかったのはダブルとシングルのハッシュマークの使い分けと、文字列連結オペレータの & を正しい場所に置くことだった。試行錯誤を続けて、たぶん１時間ほど費やしただろうか。今の私はこれがどのように動いているのか理解しているけれども、動くようになる前はまだきちんと理解できていなかった。私は恥知らずにも実例から盗み取る名人なので、きっと私が試した実例のどれかが私に正しいやり方を教えてくれたに違いない。Utility!$A5: この Utility というシートは何かというと、ここには数字を含んだカラムが一つだけあって、A1 には 1、A2 には 2 という風に、最後の A250 の 250 まで並べてある。これを作った理由は HLOOKUP に何らかの方法で番号付けができるように、その数が値として返されるようにする必要があったからだ、私にはセルの内容を引用する以外に数を増やして行く方法が見つからなかった。混乱してしまうが、この番号付けは行 5 から始まる必要があった。私の検索範囲は A5:X2500 だからだ。そこで、行 5 の番号が 1、行 2 の番号が 6、という風になっていなければならない。これはとてつもなく紛らわしく、正直言って私は具体的な番号をいろいろ差し込んで正しい結果が返るまで試行錯誤することでようやく正しい方法を見つけることができた。A の直前の $ はそれを絶対的にしてそのカラム上で一定になるようにするためで、Aの直前に $ がないのはそれを相対的にして必要に応じてインクリメントさせるためだ。FALSE: 最後に、HLOOKUP 関数の FALSE オプションは検索対象の行が並べ替えられないようにするという指定だ。それがどういう結果を生むのか私にはよく分からないが、私のデータは並べ替えを受けていないので、それに合わせておいただけだ。
この公式が見事なのは、私が必要とするデータをすべて一挙に取り込めるからだ。最初にシートを作成して、競技の名前を A2 に入れ、2016 から 2023 まで毎年のカラムを作っておく。それから 2023 の下の最初のセルにこの公式を入れ、右の方へそれをコピーすることで毎年の最初の行を埋め、そして最後にそれを下の方へコピーして残りの行を埋める。
(スプレッドシートの経験があまりない人たちのために言い添えると、公式をコピーする技は必ずある。Google Sheets と Excel では、セル右下のハンドルをドラッグすればよい。Numbers では、セルの望みの側にある黄色のハンドルをドラッグする。そうすれば公式が行き先のセルにコピーされ、行やカラムへの相対的な参照情報も適切にインクリメントされる。セル参照にインクリメントを加えずセルからセルへ公式を入れたい場合には、行とカラム双方の参照の前に $ を付けるか、または (公式を含むセルでなく) その公式のテキストそのものをコピーしてペーストするかすればよい。)
データをフィルターしてフォーマットする
この記事の初めの方に載せた活動明細書のスクリーンショットをよくご覧になれば、アカウント名の後に (deleted) とか、さらには (deleting)(deleted) とさえ書いてあるものがあることにお気付きかもしれない。実はこれらは QuickBooks Online の変な挙動の副産物であって、そのためいろいろと厄介な作業が必要になった。なぜかを説明しよう。
私たちのアカウントの様相は時と共に変化してきたし、何人もの会計担当者や経理係が入れ替わり立ち替わりしてアカウントを追加したり削除したりしてきた。この Budget Tool に私がデータを入れた最も古い年である 2016 年以後には一度も使われていないアカウントさえある。でも、上記の私の魔法の公式をご覧になれば、これがセル参照のみによって働くことがお分かりだろう。私としては、Food アカウントが毎年決まって Overall シートの行 87 に置かれている必要がある。標準の QuickBooks Online 活動明細書には活動のあった行のみが表示されるので、そこから報告書を作ると年ごとに Food がどの行に来るか全く予想がつかない。
幸いにも、QuickBooks Online には報告書にすべてのアカウントを含めるオプションがある。そのオプションを使えば、活動明細の報告書はすべてのカテゴリーを毎年同じ行にしてくれる。それが可能となる理由は、QuickBooks Online が決してアカウントを削除しないからだ。削除とマークされたものはただ表示から隠されるだけだ。
けれども、こうして不要な行まで全部含め散らかすことで別の問題も起こる。私たちの競技会のほとんどはそれぞれせいぜい 10 個から 20 個ほどのアカウントしか持たないのに、この活動明細書には何と 204 個もの行がある。提示方法としてこれは深刻な問題だ。
いったいなぜそんなにたくさんのアカウントがあるのか？ 今年の初め頃、私は通常の QuickBooks Online アカウントから TechSoup を通じたアカウントに切り替えれば料金が一年あたり $800 以上節約できると知った。TechSoup が、非営利団体を対象にテクノロジーの値引き提供をしているからだ。かなり前に Jeff Porten が TechSoup についての記事を TidBITS に書いてくれた (2018 年 10 月 18 日の記事“TechSoup: あなたの非営利団体に大幅な割引価格でテクノロジー導入を”参照) のでその名前は私の記憶に残っていたのだが、年額で $75 値引きの QuickBooks Online 購読を提供しているとは知らなかった。ただ、既存の QuickBooks Online 購読を TechSoup アカウントに切り替えるために新しい“会社”をセットアップしなければならず、データをそこへ転送してから、従来の購読をキャンセルした。それは複雑で、ストレスの多い作業であった上に、約束された返金を受けるために Intuit のサポート担当者の援助を受ける必要まであった。それでも、最後にはすべてうまく行った。
残念なことに、QuickBooks Online で新しい会社をセットアップするためには、直ちにバックアップからデータを読み込む場合でさえも、まず定型のアカウントチャートを使わなければならない。これらの定型アカウントはその後すべて削除したけれども、活動明細報告書の中には大量の削除済みアカウントが残って散らかる結果となった。一度も活動に加わっていないアカウントは消えて欲しいと思うのだけれども、消すための唯一の手段はそれらを統合することのようだ。理論的には、不要なアカウントを全部一つのアカウントに統合してからそのアカウントを削除すればよいのだろうが、現実的にそんな作業はやりにくく、誤りを起こしやすく、うまく行かないことも多い。なので私は諦めて活動明細書の見苦しさを我慢することにした。
私たちの Budget Tool の初期のバージョンでは、個々の競技会を手で開いてチェックし、関係するデータを含まない行を手で隠していた。でもこの作業はとても面倒な上に、私が隠しておいた未使用のアカウントをその競技会が後になって使い始めた場合に厄介な混乱を引き起こした。QuickBooks Online の削除済み定型アカウントが大量に生じた結果 Budget Tool のリファクタリングを余儀なくされた際に、私はもっと良い方法があるはずだと考えた。
Google Sheets にはフィルタリングの機能があって、この機能が行を隠したり表示したりできる。私は Total カラムを作って、そこに 2016 年から 2023 年までを合計した数字を入れた。その合計額そのものに興味はなかったけれども、そのカラムにフィルター (Data > Create Filter を選ぶ) を作って、ゼロでないものを表示している行のみを表示するようにした。するとたちまち、不要なデータがすべて消え失せた。
でも一つだけ難点があった。私のシートにはすべて Revenues (収入) と Expenditures (支出) の行がある。Revenues の見出しはすべてのデータより上に表示されるのでこのフィルターからは安全だが、Expenditures はシートの真ん中あたりに表示されていて付属するデータを持たないのでフィルターが隠してしまう。Expenditures が隠れないようにするため、私はそのカラムの一つに 1 を入力するようにした。これにはもっと他に良い解決策があるのかもしれない。
また、青く表示される Revenues と Expenditures の見出し行の他に、グレイに表示される Total Revenues と Total Expenditures の行と、黒く表示される Net Revenue の行もある。シートを読みやすくするため、これらは他と違うフォーマットで表示させたいと思う。当初はシートごとに手作業でフォーマットを付けていた。確かに面倒だけれども、思ったほど手数は掛からなかった。Google Sheets には Edit > Paste Special > Format Only コマンドがあるからだ。望ましいフォーマットの付いたセルをコピーしておき、行を選択して Command-Option-V を押せば、コピーしておいたセルのフォーマッティングのみがペーストされる。
それでもなお、さらなる抽象化を目指す私の探求の先にはもっと良いやり方があるはずだと分かっていた。Google Sheets には conditional formatting (条件フォーマット) という機能があって、範囲内にあるセルを公式に基づいてフォーマットすることができる。私はずっと以前から負の数を赤く表示させるためにこれを使ってきたけれども、特別の行をフォーマットするルールを作ればよいと気付いた。一つヒントがある： Format > Conditional Formatting を選ぶと、現在選択されているセルに適用可能なルールのみが表示される。だから、このコマンドを選んで何も表示されなければ、まず影響を受ける範囲内にあるセルを一つ選んでおくのがよい。
それが機能するためのヒントはこの公式にある：=REGEXMATCH($A2:$A210,"Net Revenue") だ。REGEXMATCH は正規表現 (grep) 関数で、範囲内にあるテキストを検索する。このプロジェクトのあらゆるところで言えることだが、ここでも私はいろいろな実験を通じて機能させる方法を見つけた。ルールによっては範囲が変になることもあったが理由はよく分からない。上記の中の範囲をよりシンプルな A4:N211 に変えてみたけれども何の問題もなかった。
ここまでのことが済めば、あとは追加のカラムをいくつか作成して Charlie と私が予算作成のために計算できるようにした。さきほどのスクリーンショットでオレンジ色の背景で示されているものだ。これらは収入と支出を合計するための簡単な計算をして、純収入を計算する。Notes カラムは特定の数字をどのように予想したかの覚え書きだ。時には、Scratchpad カラムを作って見積額の変更が競技会の総損益にどう影響するかプレビューできるようにすることもある。これもやはりオレンジ色の背景のカラムで、予算と同じ計算式を使う。
行を隠したりフォーマッティングしたりしてきたのは、すべての競技会、General、 Total を合わせて全部で 22 枚ものシートがあるからだ。ここに何か一つ変更を施そうと思えば、それを 22 回施さなければならないので面倒だ。そこで最後の改訂として、行を隠したりフォーマットしたりする抽象化を施す度に、個々のシートを再作成しなければならなかった。それは別に難しいことではなく、シートを複製して、A2, に記入されている表示名と競技会名を変更し、フィルターを更新するだけのことだ。けれどもそれを終えても、その後の変更についてはまた 22 回繰り返さなければならない。例えば、来年のためにいずれ個々のシートに 2024 というカラムを追加しなければならなくなる。理想的な世界においては、抽象化した機能を一つのスプレッドシートの中の複数のシートに手軽に適用できるはずだ。ひょっとするとそういうものも既に可能であって、ただ私が気付いていないだけなのかもしれない！
いつもの使い方
最後に、私たちが普段 Budget Tool をどのように使っているかについて、いくつかコメントを書いておこう。
まず第一に、経理係がその月のすべてのデータを QuickBooks Online に入力し終えた時点で、私たちはすべての行を含む活動明細報告書を走らせて、それを Excel フォーマットに書き出す。それをそのまま Google Sheets に読み込ませて一つのシートの内容を上書きするのも可能だけれども、私は書き出したものを一旦 Excel で開いて、データをコピーし、Google Sheets に切り替えて、対応する年のシートの上にペーストする方が楽だと気付いた。
(QuickBooks Online と Google Sheets の間で自動的にデータを動かせるようなオンライン統合はあるのだろうか？ 私が知る限りそれに最も近いのは Skyvia だが、Intuit はそのために必要な報告書への API アクセスを提供していない。)
新しいデータが Google Sheets に取り込まれたら、すべてが (本当にあらゆるものが) 即座に更新されるので、私たちはシートに目を通して個々の競技会の状況を調べることができる。予算で想定された金額と大きくかけ離れた数字があれば、何が原因かを調べようと試みる。(たいていの場合、それは単なる分類の誤りによるものだ。)
私たちが遭遇した唯一の障害は、これまで使われていないアカウントにデータを持っている競技会があった場合だ。その場合、私はフィルターを更新するために、すべてを選択してからもう一度ゼロの選択を外さなければならない。でもそういう事態は今のところまだ一度しか起こっていない。
競技会の監督との間でデータを共有することに関しては、ここで紹介したい最後の Google Sheets の技がある。Google Sheets の右上隅にある青い Share (共有) ボタンをクリックすると、そこで提供されるリンクは最初のシートへ行く。私たちの場合は Total シートだ。別に悪いことではないけれども、できればその競技会のシートが直接開く方が望ましい。そのためには、特定のシートのセル A1 を Control-クリックして、一番下の View More Cell Actions > Get Link To This Cell を選ぶ。これで、そのシートへのカスタムリンクがクリップボードにコピーされるので、それを共有することができる。
共有することによってせっかく作ったシートや公式が誰かに (うっかりと、あるいは悪意を持って) 削除されたり編集されたりするのではないかと思う人もいるだろう。私の体験上はほとんどの人が共同作業の書類を十分それと意識して何にも触らないようにしてくれるので問題は起こらないようだが、この種の共有されたスプレッドシートを保護する方法は三つある：
- Share access: 私は力ずくのやり方を選んだ。私と Charlie 以外のすべての人のアクセス権を Commenter に設定したのだ。その人たちが変更を加えたければ可能なのだが、その際に私がその変更を承認または却下できる。そのような事態はまだ起こっていない。
- シートや範囲を保護する: もっとずっと細かなコントロール方法として、Data > Protect Sheets and Ranges オプションを使えばスプレッドシートの特定の一部分について誰が何をできるかを具体的に指定できる。私はこのような細かな設定をまだ必要と感じたことがない。
- 過去のバージョン: Google Sheets はあらゆる変更についてのバージョン履歴を保持しているので、必要ならばそのスプレッドシートを丸ごと過去のバージョンに戻すことが可能だ。たとえ誰かが望ましくない変更を施したとしても、それ以前の状態に簡単に戻せる。ただしそこでの問題は、すぐにはそのことに気付かずその後に正当な変更を数多く施してしまった場合だ。
この記事で取り上げたものが私のプロジェクトのみに適用される極めて個別のケースであることは承知しているけれども、現代のスプレッドシートが単なる計算の実行を超えてずっと多くのことができるという考え方だけはお伝えできたと願いたい。読者の皆さんご自身の仕事の中でも、これに似たテクニックを使うやり方について少しはヒントとなれたかもしれない。スプレッドシートは、本格的なプログラミング環境と比べてもそう大きくかけ離れたものではない。実際、マクロやスクリプトに対応していることも多く、行やカラムの制約を超えたことも可能になる。でも、それはまた別の機会の話にしよう。
討論に参加
