盆栽の管理向上をめざして １７：棚場のスタッツを表示する【盆カン2公開】
盆栽世界大会の決勝戦の中継TV。庭に並んだ盆栽をバックに盆栽に関するスタッツのテロップが大きく映し出されている。実写風。
はじめに
AppSheetを用いたアプリ作りは粗削りではあるが、ダイナミックに機能を追加できるのでとても気に入ってます。
ここのところ、盆栽計測装置「Bonsai Dock」の開発に取り組んでおりましたので、「盆カン」の話題が少なくなっておりましたが、日々改良を続けておりました。
Statsビューを作りたい
盆栽管理ソフト「盆カン」を知り合いに紹介する機会が増えてきました。
「たくさんお持ちですね。何鉢あるの？」
「どんな木がお好きですか？」
といった質問に即座に答えられない自分がいます。
こんなとき、スポーツ中継でよく見られるStatsビューがあるといいな。と思いました。
ボール支配率、シュート数、コーナーキック数などなど。
棚場の盆栽鉢を特徴づけるデータをうまく引っ張り出してこれないものだろうか？
AppSheetであれこれするのはどうも苦手なもので
AppSheetなら簡単にできるかも。と思い探し始めましたが、そんなに都合よく見つかるものではありませんでした。AppSheetを使うコツは、あれこれこだわらず簡単にできることを受け入れるスタンスが良いのかもしれません。ひょっとしたら、ちゃんと用意されているのかもしれませんが、今回は探し出すことはできませんでした。
Googleスプレッドシートの力を信じてみよう
元データの「My盆カン」に「Stats」シートを作り、そこにデータを集め、AppSheetで表示すればよい。ということを思いつきました。
そういえばAppSheetに出会う前はGoogleスプレッドシートでゴリゴリ作りこんでいました。関数でゴリゴリ作るのも嫌ではありません。
ここはひとつ、Googleスプレッドシートの力を信じて進んでみることにしました。
こんな感じです。
AppSheetで扱いやすくするため、「StatsID」、「項目」、「値」としました。
思いつくままに、項目を書き出し、その内容を関数を使って表示するようにしました。
ST001 / 現存する鉢数
=C4-C3
ST002 / なくなった鉢数
=COUNTIF('盆栽鉢T'!D2:D,"<>")
ST003 / 登録鉢数
=ROWS('盆栽鉢T'!A2:A)
ST004 / 次の盆栽鉢ID
=C4+1
ST005 / 記録数
=ROWS('記録T'!A2:A)
ST006 / 最新記録日
=TEXT(MAX('記録T'!C2:C),"yyyy/mm/dd hh:mm:ss")
ST007 / 最多記録数
=MAX(ARRAYFORMULA(COUNTIF('記録T'!B2:B, UNIQUE('記録T'!B2:B))))
ST008 / 最多記録鉢ID
=MODE('記録T'!B2:B)
ST009 / 活動記録数
=ROWS('活動記録T'!A2:A)
ST010 / 最新活動記録日
=TEXT(MAX('活動記録T'!B2:B),"yyyy/mm/dd hh:mm:ss")
多分自力ではここまでの表現はできなかったと思います。ネット検索とChatGPTの力を借りています。
例えば、「最多記録数」というのは「記録T」シートの中で一番多く撮影された盆栽鉢の数を表しています。ネット検索をあれこれした結果、生み出したのですが、なぜそうなるのか全く理解できませんでした。
そこで、ChatGPTを使って、解説してもらうことをひらめきました。
あなたはGoogleスプレッドシートの人気インストラクターです。丁寧な説明が受講生に人気があります。下記の式について、わかりやすく説明してください。：
もちろんです！対象の式があれば教えてください。その式を分かりやすく説明します。
=MAX(ARRAYFORMULA(COUNTIF('記録T'!B2:B, UNIQUE('記録T'!B2:B))))
この数式は、Googleスプレッドシートで特定の列内にあるデータの中から、最も頻繁に出現する値の出現回数を求めるためのものです。では、段階的に分解して説明しますね。
1. '記録T'!B2:B
これはスプレッドシートの範囲を示しています。'記録T'!B2:Bは、「記録T」というシートの列Bの2行目から最後の行までのすべてのデータを指しています。
2. UNIQUE('記録T'!B2:B)
次に、UNIQUE関数です。これは指定された範囲に含まれるユニーク（重複しない）値のリストを作成します。つまり、この場合、列Bにあるすべてのデータから重複を除いた値のリストを作ります。
3. COUNTIF('記録T'!B2:B, UNIQUE('記録T'!B2:B))
COUNTIF関数は、指定された範囲内で、条件に一致するセルの数をカウントします。ここでは、'記録T'!B2:Bの範囲で、UNIQUE('記録T'!B2:B)で得られた各ユニークな値がそれぞれ何回出現するかをカウントしています。
4. ARRAYFORMULA
ARRAYFORMULAは、数式を配列に対して一括処理するために使われます。通常、COUNTIF関数は単一の値に対してのみ動作しますが、ARRAYFORMULAを使うことで、複数のユニークな値に対して一度にCOUNTIFを適用しています。
5. MAX
最後にMAX関数が登場します。これは、配列や範囲内の最大値を返します。ここでは、ユニークな値の中で最も出現回数が多いものを見つけ、その回数を返します。
まとめ
この数式は、「記録T」シートのB列にあるデータを調べ、その中で最も頻繁に出現する値の出現回数を求めています。たとえば、B列に「A」が5回、「B」が3回、「C」が2回ある場合、この数式は「5」を返します。
その続きで質問があります。「記録T」シートのB列にあるデータを調べ、その中で最も頻繁に出現する値そのものを返す式を教えてください。
最も頻繁に出現する値そのものを返すためには、INDEXとMODE関数を組み合わせた数式を使用します。以下の数式を使うと、「記録T」シートのB列にあるデータの中で最も頻繁に出現する値そのものを取得できます。
```
=MODE('記録T'!B2:B)
```
数式の説明:
- MODE関数:
MODEは指定された範囲内で最も頻繁に出現する値を返します。範囲内の値が全て異なる場合（最頻値が存在しない場合）や、データがない場合はエラーが返されることに注意してください。
応用:
もし複数の最頻値が存在する場合でも、最も小さい（または大きい）頻出値を返すことができますが、MODE関数はその中の一つを返すように設計されています。
この数式を使うことで、最も頻繁に出現する値自体をシンプルに取得することができます。
といった感じです。
プロンプトのおかげで、とても丁寧でわかりやすい解説をしてもらうことができました。
Googleスプレッドシートを作り始めたときにさらに解説してもらうだけではなく、追加で新しいことも教えてもらいました。
MODE関数を使うとこんなにすっきり書けるようになるなんて驚きです。
MODE＝頻出値か。確かに。
あとはAppSheetで表示するだけ
ここまでくれば、あとは表示するだけです。新しいViewを作り、Statsシートを表示してみました。
これらの値がすぐに見えるようになり、非常に便利になりました。
そしてここまでの機能を「盆カン2」に盛り込んで公開してみました
公開ポートフォリオ
「盆カン2」を選びます。
「Copy and Customize」を選びます。
本当は、バージョンアップ版としてリリースしたいところなのですが、どのようにしたらよいかわからないので、別アプリにしています。
さいごに
今回は小ネタのご紹介にとどめようと思っていたのですが、ChatGPTを使いだしたあたりから楽しくなって、「盆カン2」の公開まで進めることができました。
思えば、ここ最近の盆カンの改造テーマはデータの「見える化」だったかもしれません。IoTの４段階「監視」、「制御」、「最適化」、「自律化」の中でも初手からなかなか進めないでいます。
これからもこの盆カンを中心に装置つくりを進めていきたいと思います。
今回も最後までお読みいただき誠にありがとうございます。