まだプログラマーですが何か？

プログラマーネタ中心。たまに作成したウェブサービス関連の話も https://twitter.com/dotnsf

タグ：spreadsheet

僕のことを個人的に知ってる人はご存知かもしれませんが、自分は今から25年ちょっと前に「ロータス株式会社」という企業でアプリケーション開発を担当していました。具体的に開発を担当していた製品の中には、昭和を代表する（苦笑）表計算ソフト 1-2-3（「ワンツースリー」）も含まれていました。ここ10年くらいはウェブアプリばかり作っていますが、まだ頭（や性格）がキレキレだった頃はパソコン向けのネイティブアプリも作っていた時代がありました。

自分が開発を直接担当していたのは Microsoft Windows 向けの 32bit 版でしたが、当時はまだ PC-DOS 版を無理やり Windows 向けに拡張した 16bit 版や、PC-DOS 版自体も 1995 年まではバージョンアップ対応をしていました。そんなこともあって当時の担当製品は今でも仮想マシン（VM）の形で残していたりします。今となっては貴重な PC-DOS/V(V7) の稼働環境だと思っています：
2022060400


※ついでにコメントしておくと、Lotus Notes バージョン１などの OS/2 資産も VM 化して残しています。自分が作った製品への愛は高めだと自認しています。


この PCDOS VM の中に懐かしいツール類と一緒に Lotus 1-2-3 R2.5J がインストールされています（赤く囲っているのが当時悪名高かったライセンスファイルです）：
2022060401


今回、この環境を持ち出したのは理由があります。先日、スラドにこのようなタレコミがあったのを発見しました：
Lotus 1-2-3、Linux に移植される

（自分も 1-2-3 愛は大きい方だと思ってますが）自分も勝てなさそうな 1-2-3 愛を持っていそうな Tavis 氏がインターネット・アーカイブに転がっていた保存されていた SysV 版の 1-2-3 for UNIX を使って Linux 版（elf 版）を作ることに成功した、というものでした。未だに 1-2-3 への愛を持っているのは自分くらいかと思っていたのですが、世界は広いです。 Tavis さん、Good-job! d(o^


（横道）上記画像でファイラーと呼ばれていた FD を使ったスクリーンショットを掲載していますが、実は FD は Linux に移植済みだったりします。興味ある方はこのコマンド（Ubuntu 向け）を試してみてください：
$ sudo apt install fdclone

$ fd
2022060411



（横道おわり）で、そんな記事に触発されたというわけではないのですが、自分も久しぶりに上記 PC-DOS 版 1-2-3 環境を使ってせっかくなので何か試してみようと思いました。実は以前から気になっていたことがあって、それを検証する機会とさせていただきました。

その気になっていたことというのは、みなさんも一度は耳にしたことがあると思うのですが「ソフトウェア製品に実装された機能の多くは使われない」というものです。製品ベンダーとしては利用者からの要望を形にして様々な機能を実装しているのですが、ほとんどのケースでその多くは使われることがない、というものです。そういう自分も、例えばプログラミングエディタに VSCode を使っていますが、自分に必要な機能は当然のように知ってますが、正直知らない機能が多くあります（良く言えば「困ったときにすぐに調べることができるので、使わない間は知らなくてもよい」わけです）。現在、表計算ソフトといえば「エクセル」ですが、このエクセルも（特に最近のバージョンは）非常に多くの機能が付与されていて、噂では方眼紙としても使うことができるその全てを熟知している人って存在するのだろうか？？とすら思ってしまうほどです。

エクセルを「最低限」（この定義が難しい）使えるようになるにはどんなことを知っていればいいんだろう？？　という需要もあるようで、YouTube を見ていてもエクセルの基本機能を紹介するものが多く見つかります。

・・・と、そこで閃きました。現在のエクセルで「基本」とされている機能って、Lotus 1-2-3 の頃にはどの程度存在していたのだろうか？　という命題です。特に他意はないのですが、この手のエクセル基本機能を調べようとググった結果の上位にあったもので、YouTube でエクセルの基本機能等を紹介しているシリーズを見つけ、そのシリーズ中の「【Excel基本】初心者が最初に覚える関数12選」の中で紹介されている１２個の関数を基本機能とみなして、これらの関数が DOS 版 Lotus 1-2-3 の中の関数としてどの程度カバーされていたのか、を検証してみることにしました（勝手に使わせていただきました m(__)m）：
https://www.youtube.com/watch?v=cfGKFhSE6uA



なお、この動画で紹介されていた関数１２選は以下のような内容でした：
１　SUM関数
２　AVERAGE関数
３　COUNT関数
４　COUNTA関数
５　IF関数
６　COUNTIF関数
７　COUNTIFS関数
８　SUMIF関数
９　SUMIFS関数
10　VLOOKUP関数
11　IFERROR関数
12　XLOOKUP関数


比較対象は Windows 版の Lotus 1-2-3 と DOS 版の Lotus 1-2-3 を・・・と考えていたのですが、今回の12個の関数について検証した結果としてはこの２つに差異はありませんでした（Windows 版に存在している関数は DOS 版にも存在していて、Windows 版に存在していない関数は DOS 版にもありませんでした）。ということもあって、以下では DOS 版での検証結果のみを紹介します。


検証方法としては、上記の YouTube 動画で「エクセルの基本関数」として紹介されている機能と互換性のある関数が DOS 版の Lotus 1-2-3 でも存在しているかどうかを確認する、という形としました。なお動画内で対象としているエクセルは最新の 2021 で、私が検証に使ったのは DOS 版 Lotus 1-2-3 R2.5J というバージョンです。このバージョン自体は 1995 年にリリースされた、DOS の日本語対応版としては最終バージョンですが、基本的な設計は R2 から変わっておらず、1986 年の設計を引き継いでいます（つまり昭和時代のソフトウェアです）。


【検証準備】
（VM 内の）PC-DOS に Lotus 1-2-3 R2.5J をインストールし、適当な表を作った上で関数の検証を行います：
2022060402


【検証１　SUM関数】
指定エリア内の数値を合計する、という（本当の意味で基本的な）関数です。これは Lotus 1-2-3 R2.5J では @SUM 関数として実装されていました（下図の赤枠部分参照）。なお Lotus 1-2-3 ではマクロ関数は全て @XXX という形式になっていて、「アット関数」と呼ばれています：
2022060403


【検証２　AVERAGE関数】
指定エリア内の数値の平均値を求める関数です。これも Lotus 1-2-3 R2.5J では @AVG 関数として実装されていました（下図の赤枠部分参照）：
2022060404


【検証３　COUNT関数】
指定エリア内に数値のセルがいくつ存在するかをカウントする関数です。これも Lotus 1-2-3 R2.5J では @PURECOUNT 関数として実装されていました（下図の赤枠部分参照）。1-2-3 にも @COUNT 関数は存在しているのですが少し用途が違っていました：
2022060405


【検証４　COUNTA関数】
指定エリア内に空白ではないセルがいくつ存在するかをカウントする関数です。これも Lotus 1-2-3 R2.5J では @COUNT 関数として実装されていました（下図の赤枠部分参照）。1-2-3 の @COUNT 関数はエクセルの COUNTA 関数で、エクセルの COUNT 関数は 1-2-3 の @PURECOUNT 関数として存在しているのですが少し用途が違っていました：
2022060406


【検証５　IF関数】
条件分岐処理をする関数です。これは Lotus 1-2-3 R2.5J でも @IF 関数として実装されていました（下図の赤枠部分参照）：
2022060407


【検証６　COUNTIF関数】
指定エリア内に条件を満たすセルの数かいくつ存在するかをカウントする関数です。これは Lotus 1-2-3 R2.5J には互換関数が存在していません。


【検証７　COUNTIFS関数】
指定エリア内に複数の条件を満たすセルの数かいくつ存在するかをカウントする関数です。これは Lotus 1-2-3 R2.5J には互換関数が存在していません。


【検証８　SUMIF関数】
指定エリア内で条件を満たすセルだけを対象とした SUM 関数です。これは Lotus 1-2-3 R2.5J には互換関数が存在していません。


【検証９　SUMIFS関数】
指定エリア内で複数の条件を満たすセルだけを対象とした SUM 関数です。これは Lotus 1-2-3 R2.5J には互換関数が存在していません。


【検証10　VLOOKUP 関数】
指定エリア内で特定の値をもつ列を見つけた場合に、指定された別の列の値を求める関数です。これは Lotus 1-2-3 R2.5J でも @VLOOKUP 関数として実装されていました（下図の赤枠部分参照）：
2022060409



【検証11　IFERROR 関数】
エラーが発生した場合の処理を指定する関数です。これは Lotus 1-2-3 R2.5J には互換関数はありませんでしたが @IF 関数と @ISERR 関数を組み合わせる形で同様の機能を実装できました（下図の赤枠部分参照）：
2022060408


【検証12　XLOOKUP関数】
VLOOKUP 関数の上位互換となる関数です。エラー発生時の処理も関数内に含めることができます。これはエクセル 2021 で登場した新関数でもあり、Lotus 1-2-3 R2.5J には互換関数が存在していません。


【検証結果まとめ】
というわけで、検証結果をまとめるとこのようになりました：
#エクセルの関数1-2-3 の互換関数
1SUM@SUM
2AVERAGE@AVG
3COUNT@PURECOUNT
4COUNTA@COUNT
5IF@IF
6COUNTIF -
7COUNTIFS -
8SUMIF -
9SUMIFS -
10VLOOKUP@VLOOKUP
11IFERROR@IF と @ISERR
12XLOOKUP -


エクセルの基本関数１２個のうち、６個は互換関数が（くどいようですが昭和時代の）1-2-3 にも存在していて、１つは他の関数の組み合わせでなんとかなる、という感じでした。「現在の基本」のうちの半分くらいは当時から使えていた、ということになります。1-2-3 は「条件付きで○○をする」という関数が全般的に弱い感じがしますが、逆に言うとそのような需要があってエクセルで実装されていったのだと思われます。特筆したいのは VLOOKUP 関数で、これは今でも中級者以上が比較的使う機会の多い関数だと思っていますが、その関数が 1-2-3 でも実装されていたんですね。オーパーツのような話で、私も少し驚きました。



なお、今回のブログエントリを書く際の参考文書はこちらでした。今となっては貴重品な、ナツメ社のハンドブックシリーズです：
IMG_20220530_080350_947



タグ ：
#excel
#lotus
#1-2-3
#spreadsheet
#function

タイトルの通りです。obniz に赤外線人感センサー（HC-SR501）をつなげて人の存在を確認し、なんらかの変化があった場合にその結果をクラウド上の Google スプレッドシートに送信する、という仕組みを作ってみました。必要な設定やコードはこちらからも公開しています：
https://github.com/dotnsf/obniz_sensor_gas


Google スプレッドシートを複数人で共有しておけば人感センサーのセンシング結果を複数の人で共有することができます。今回の例ではそこまで実装していませんが、センシング結果にセンサーの ID を含めるようにすれば、複数のセンサーからの結果を一枚のスプレッドシートにまとめることもでき、またスプレッドシート側でピボットして確認したり、グラフ化などの視覚化も容易にできるようになります。

この仕組みはもともとこちらの動画で紹介されていたものを参考にしています。この動画ではラズベリーパイと HC-SR501 を接続して、ラズベリーパイのコマンドでセンシングを実現しています。また最終的にはそのコマンドを cron に登録する形で永続処理化まで実現されているようでした：
RaspberryPIでIoT簡単入門！クラウド対応人数カウントを作る！

2021052502


これを参考にラズベリーパイを obniz に置き換えて GPIO に接続し、ラズベリーパイのコマンドではなく、obniz のウェブページの JavaScript でセンシングし、その結果を Google スプレッドシートに向けて JavaScript で（jQuery で） POST する、となるように書き換えました。

以下、その準備段階も含めた実現の手順を紹介します（ハードウェアは持っていない場合は購入の必要があります）。

【入手するハードウェア】
・obniz 本体
・人感センサー（HC-SR501）
・ジャンパケーブル（オス-メス）３本

【用意するソフトウェア】
・Google スプレッドシート（Google Drive から作成）


【準備作業】
人感センサーによるセンシングを行うための準備作業は以下の大きく以下３つの作業を行います：
１　スプレッドシート側の準備
２　obniz と HC-SR501 の接続
３　センシングを実行する HTML / Javascript コードの用意


１　スプレッドシート側の準備

まず Google スプレッドシートを１つ新規に作成します：
2021052601


スプレッドシートのメニューから　ツール - スクリプトエディタ　を選択します：
2021052602


コード画面が表示されるので、デフォルトで用意されているスクリプトをすべて消し、代わりに以下のコード（後述のデータ送信を受けて、シート内に日付時刻と送信データ内容を追加するコード）をコピー＆ペーストします：
function doPost(e) {
  SpreadsheetApp.getActive().getActiveSheet().appendRow(
    [new Date(),e.postData.contents]
  );
  
  var output = ContentService.createTextOutput("ok");
  output.setMimeType(ContentService.MimeType.TEXT);
  return output;
}
2021052603



メニューから　公開 - ウェブアプリケーションとして導入　を選択します：
2021052604


プロジェクト名を聞かれたら適当に入力して OK をクリックしてください：
2021052605


続けてデプロイ設定画面になります。バージョンは "New" の "1" 、アプリケーションの実行者は自分のメールアドレスを指定、そしてアクセスできる人として "Anyone, even anonymous" を選択して deploy ボタンをクリックします：
2021052606


アプリケーションの認証が必要なので「許可を確認」をクリックします：
2021052607


ちょっとびっくりするような画面になりますがエラーではないので続けます。詳細を表示して、安全ではないページに移動します：
2021052608


作成したプロジェクトにアクセスの許可を与えるため、「許可」ボタンをクリックします：
2021052601


デプロイが行われ、最後に URL が含まれる画面になります。この URL はこの後必要になるので、メモしておきます。OK を押してダイアログを閉じます：
2021052602


これでスプレッドシートの最低限の準備はできました。必要であればスプレッドシートの名称を変更したり、他の人と共有しておいてください：
2021052603


２　obniz と HC-SR501 の接続

まず以下の作業をする前に obniz の電源を OFF にしてください。以下の作業は obniz の電源が OFF になっている状態で行ってください。

HC-SR501 には３本の接続コネクタピンがあります。接続コネクタ部分が左側にくるように向きを調整した時のピンを上から０、１、２とみなします。また obniz も同様に GPIO が下側にくるように向きを調整した時のコネクタを左から０、１、２、・・・とみなすことにします：
2021052601


この状態でジャンパケーブルを使って、０と０、１と１、２と２をそれぞれ接続します。ここまでできると写真のような状態になります：
D95C6870-EBAC-4D5B-88BB-F8F5F48F9D7F


これで obniz と HC-SR501 が接続できました。この後、obniz の電源を ON にすると HC-SR501 でセンシングできるようになり、センシングした結果を obniz から取得することができるようになりました。


３　センシングを実行する HTML / Javascript コードの用意

最後に実際にセンサーで人の存在を確認し、その結果を取得して、（上述で準備した）スプレッドシートに記録する、というプログラムを作ります。

まず obniz の開発者コンソールにログインします：
https://obniz.io/ja/console


画面左の「リポジトリ」から「新規作成」を選択します：
2021052604


新しいプログラムのダイアログが表示されます。タイプは「WebApp」、アクセスレベルは「公開」を選択し、適当なファイル名を付けて「作成」します：
2021052605


サンプルページの HTML がエディタ内に表示されます：
2021052606


この HTML をすべて消して、以下の内容をコピー＆ペーストします：
<html>
  <head>
    <meta name="viewport" content="width=device-width, initial-scale=1" />
    <script src="https://code.jquery.com/jquery-3.2.1.min.js"></script>
    <script src="https://unpkg.com/obniz@3.8.0/obniz.js"></script>
  </head>
  <body>
    <h1>人感センサー</h1>
    <div id="obniz-debug"></div>

    <script>
      var obniz = new Obniz("OBNIZ-ID");
      var sensor = null;
      const url = "https://script.google.com/macros/s/xxxxxxxxxx-xxxxxxxxxx/exec";//GASのウェブアプリケーションのURLを指定してください

      obniz.onconnect = async function () {
        sensor = obniz.wired( "Keyestudio_PIR", { signal:1, vcc:0, gnd:2 } );
        console.log( sensor );
        sensor.onchange = function( v ){
          console.log( v );
          $.post( url, { value: v } )
            .done( function( data ){ console.dir( data ); } );
        };
      };
    </script>
  </body>
</html>
2021052607


12 行目と 14 行目は変更が必要です。12 行目の new Obniz( "OBNIZ-ID" ); となっている OBNIZ-ID の部分は自分が使っている obniz の ID （電源を入れた時に画面に表示される nnnn-nnnn 形式の８桁数字）に置き換えてください。　また 14 行目の url の値は上述の作業１の最後、スプレッドシートに権限を与えた際に取得した URL 文字列に置き換えてください。

なお、17 行目で obniz と人感センサーをソフトウェア的に接続しています。上述の 0-0, 1-1, 2-2 と物理的に繋いだ作業の意味（0 が vcc 、1 がシグナル、2 がアース）を指定しています。

ここまでの変更ができたら準備はすべて完了です。


【センシング実行】
まず obniz と、obniz に接続された HC-SR501 に電源を入れます。obniz にマイクロ USB ケーブルを接続して、WiFi に接続してください。成功すると以下のように obniz ID とその QR コードが表示される画面になります：
2021052601


そして obniz コンソール画面右上の「実行」ボタンをクリックします：
2021052607


すると以下のような画面になります。緑の帯が出ていれば obniz は WiFi を経由してインターネット接続ができていることを意味しています（緑にならず、赤くなっている場合は WiFi 接続に失敗しているなど、インターネットに接続できていないことを意味しているので修正が必要です）：
2021052602


この時に人感センサー近くで人が行ったり来たりして、人が存在していたりいなかったりするような状況になると、その変化を人感センサーが感知してスプレッドシートにその変化の様子が送信されます。スプレッドシートは以下のような感じになり、変化を検知する度に一行ずつ増えていきます：
2021052603

↑左が検知の日付時刻、右は value=true が人を感知した時、value=false は検知していた人がいなくなったことを検知したことを意味しています。

なお、このスプレッドシートは手動で情報を消さない限りはずっと情報が「追加」されます。一度リセットしたい場合は該当シート内に書かれた部分を手動で全選択して DELETE してください。


動作確認ができたら、いったん「終了」ボタンを押してセンシングを終了します：
2021052602


【センシングの自動化設定】
とりあえず人感センサーとしての挙動は確認できました。最後にこの仕組を（わざわざウェブページを開いて「実行」ボタンを押さなくても、電源に接続しただけで実行できるように）自動実行する方法を紹介します。

改めて obniz 開発者コンソールに戻り、左側のメニューから「サーバーレスイベント」を選んで「新規作成」します：
2021052601


適当な名前（図では「人感センサー」）を入力し、「トリガー」には obniz Hardware Event を選択後に対象 obniz id を選択して online イベントを、「実行するアプリケーション」にはリポジトリ内の HTML を選択後に先程作成した HTML を、それぞれ選択します。最後に「作成」ボタンをクリックします：
2021052602


これで対象 obniz がオンラインになったタイミングで対象 HTML ページが実行されるので、自動起動に近い処理が実現できました。いったん obniz の電源を OFF にしてから ON にするなどして、再度センシングできているかどうか（スプレッドシートに情報が追加されるかどうか）を確認してください：
2021052603


肝心のセンサー精度などはまだちゃんと測定できていない（苦笑）のと、実際には HC-SR501 についているネジでそのあたりの調整もできるようですがまだしていない（苦笑）ので、実際のところどの程度現実的に役立つものなのか、まだわかっていないところもありますが、（ラズベリーパイや）obniz があるとこんなことも簡単に作れちゃうんですね。単にセンシングするだけでなく、取得した内容をインターネット上に記録して共有するところまでできている点がポイント高いと思っています。




タグ ：
#jquery
#obniz
#hc-sr501
#google
#spreadsheet
#gas

このページのトップヘ