Monthly Archives: April 2016

時に、行列を一列に列べたいときが有ります。例えば、乗車料金表は良く行列に記載されます。出発地と目的地はそれぞれ行と列で表します。行と列の交差点は料金です。この料金表を関係型データベースに入れる場合は、一列に並べられてから入れるのが殆どです。例えば、下記の図に示されたようです。手動で一列ずつでのコピーは得策でき有りません。数式を使いましょう。 まずはタスクの目的を明確にします。六行五列の行列に、26この英文字が並べられています。これを縦一列に並べます。合わせて三列で、左から右へそれぞれ行の名称、列の名称、対応の英文字になります。全部26個ありますが、5個ごとに一行進みます。行が進む度に、列は先端に戻ります。これは割算とMOD関数の動作とほぼ一致します。まずは行の番号を求めてみまます。 結果の三列に望む内容としては、第一行から第五行まではあの行の英文字が欲しいです。しかし、1/5..4/5は1未満ですが、5/5は1になります。言い換えますと、同じ「あ」行なのに、割算の結果では整数の部分は一致しません。これは不便なので、結果の行を数えることは1からではなく、0からにします。つまり、補助として、I3:I28に0..25の26個の数字を入れます。O列とP列にそれぞれ行と列の番号を入れます。(Aを持つセルを(1,1)にします。)O3に「=FLOOR(I3/5,1)+1」を、P3に「=MOD(I3,5)+1」をそれぞれ入れます。(FLOORの作用は少数を削ります。MOD関数は割算で剰余を求めるものです。) O3とP3をO28とP28に引いて、全26個の座標を作ります。 最後のステップはK3に「=INDEX(B$3:B$8,O3)」を、L3に「=INDEX(C$2:G$2,1,P3)」を、M3に「=INDEX(C$3:G$8,O3,P3)」を入れて、28行目まで引きます。これで完了になります。  

例えば右の図にあるような表が有ります。仮にこれは座席表です。この中の「西川」さんの座席はC4です。これをEXCELやGoogle Sheetsで割り出したい場合は、どうしましょうか？ 以下はステップ分けて解説します。 一気に行と列を割り出すのは大変難しいので、より簡単なタスクに分解します。西川さんは何列に居るかと西川さんは何行に居るかという二つの問題に分解します。まずは西川さんは何列に居るかを考えます。 でもまだ難しいです。もっと分解します。まずは西川さんは指定の席に居るかどうかを割り出します。これなら簡単です。E1に「=A1=”西川”」を入れます。そして、G5まで引っ張ります。できるものは右の図に示されたようなものです。ほぼ全部FALSEで、一個だけがTRUEになります。このTRUEは西川さんの居る場所です。既にお分かりかもしれませんが、このTRUEは行と列に関係なく、TRUEです。このTRUEを以って行と列の番号をそれぞれ取得できます。 つまりこうしたいです。TRUEの場合のみ行と列の番号を出してもらいたいです。その他の場合は0にします。そうなれば、行か列の最大値として西川さんの位置が分かります。では、I1に「=IF(E1,ROW(),0)」を入れます。K5までセルの式を引きます。ROW()関数は所在セルの行番号を返します。結果としては、E1:K5は全部0で、一個だけが4になります。この4は西川さんの行番号になります。同様にM1に「=IF(E1,COLUMN()-COLUMN(J1),0)」を入れて、O5まで引きます。COLUMN()関数は所在セルの列番号を返します。ここで注目すべきのは式をM1に入れるので、COLUMN()関数はMの列番号を返します。C列の番号では有りません。C列の番号が欲しいので、「-COLUMN(J1)」を使用しています。COLUMN()関数にセルを記載した場合は、そのセルの列番号を返してきます。J列は相対的原点の位置なので、J1を使いました。(J列のセルであればどれでもいいです。)ここまでは、西川さんの行と列の座標を行列の形で表しました。7行目は各列の最大値を取っています。例えばI7に「=MAX(I1:I5)」を入れています。 最後に、I7:K7とM7:O7に対して、それぞれ最大値を取って、「C4」のようなセル名を作ります。これはADDRESSと言う関数を使用します。この関数は行の番号と列の番号をとって、説明を返します。もし三番目の引数を使用すれば、出力の形式を指定できます。例として、Q1に「=ADDRESS(MAX(I7:K7),MAX(M7:O7),4)」を入れます。そして、Q1の表示内容は「C4」になります。

場合によってエクセルで一定のデータを集めたいです。例えば、右のような表が有ります。この表の一列目が埋まっている行を集めたいとします。もし単純にコピペーで一回だけで行を集めるので有れば、結構にいい教材が有ります。しかし、時に動的に自動に更新して欲しいです。つまり、例えば、E10に「Michael」を入れたとします。希望としては、行を集めた結果に自動に「Michael」の行を追加されます。また、フィルターのように行を表示しないではなく、本当に連続の行でデータだけ集めたいです。この記事はこういう方法をご紹介します。 エクセルは計算表です。言い換えれば、計算データの変換表です。今回の行集めの根拠はEの列なので、当面の目的としては、E列の埋まっている行の番号を洗い出します。この番号一覧さえ分かれば、元の各行からデータをとればいい話しになります。最終的に下記のAF7:AJ17のテーブルにしたいです。以下はステップ分けて説明します。 ステップ１ 初めは、E列にどのセルが埋まっているかを得たいです。これは簡単です。ISBLANK関数を使用します。

この数式をT7に埋め込み、T17に引っ張ります。Eの列に埋まっている/埋まってないは1/0で表示されます。 ステップ２ Uの列をこの行まで一番近い埋まっている行はどの行かに設定します。例えば、9行目は埋まっているので、U9を9にします。10行目は埋まってないので、一番近くて埋まっている行は9行目ので、U10を9にします。7行前は一行もないので、U7を0にします。数式的に考えますと、同行のT列のセルが1の場合は、現在行のU列のセルを現在行の番号にし、そうでないと、上の行のU切のセルの値を取ります。数式で実装すれば、U7は下記のものになります。

その他のセルは同様に引っ張って作ります。 ステップ３ U列の番号を結果表の何行目に表示させるべきかを決めたいです。U列を観察してみれば、前行と現在行の数字が相違していれば、結果表の行数は増えます、そうでなければ、増えません。V7は下記の数式になります。

ステップ４ 変換は後半になります。このステップではU列の重複値を取り除きます。ここはVLOOKUP関数を活用します。VLOOKUP関数は、一定の範囲内で縦で指定の値を検索し、相応の値を右の列から取り出すことはできます。ただ、相応の値を左から取り出すことはできないようです。V列は1,2,3…になっていて、V列から順番を検索して、U列から相応の値を取り出したいです。VLOOKUPを使いたいので、U列とV列の位置を反転させてもいいですが、説明しやすさのため、U列の内容を「=U7」のようにW列で引用します。Uで上り順の順番を検索したいので、X列で重複しない上り順の数字を用意します。そして、Y7を次のような数式にします。

Y行の他の列は引っ張ってできます。 ステップ５ 所要の列番号は集まりました。必要なセル位置を割り出すことは簡単になります。Z7を下記のような数式にします。

Z7:AD17は引っ張ってできます。この範囲のすべての列はY列を参照するため、Yの前に$をつけてから引っ張ります。 ステップ６ いよいよ最後になります。Z7:AD17のセルを値を変換します。ここに二個の落とし穴を注意する必要が有ります。例えば「=A1」の場合、A1に何もかかれていなければ、「=A1」の値は0になります。空きではないです。空きを空きのままに維持したいので、判断を入れます。もう一個の落とし穴はISBLANKは使えません。ISBLANKは数式を検知した場合は、空きだと認識できません。このため、比較演算子を使用します。AF7に下記の数式を入れます。

AF7:AJ17に他のセルはAF7を引っ張って作ります。 できました！！ 纏め この技術はかなりパワーを発揮する場合が有ります。例えば、一年中の営業日の日付を集めたい場合は使えます。スケジュール管理の効率向上にかなり役立ちます。実際の例としては、「簡単ガント図」の記事を参考してください。

仮に、サーバーと複数のクライアントが有るとします。 サーバー上でAPIのパッケージを実行したいとします。このAPIは複数のクライアントに使用される場合が有ります。ただ、誰でも使えるわけではなく、認証されたクライアントのみに使用できます。この場合はどのように認証をするかを考えてみました。 IP制限 もっとも簡素な方法の一つとしては、IP制限になります。ただし、IP制限はよりコントロールされたネットワークの中では有効で、IP Spoofingに遭遇しましたら、一溜まりも有りません。クライアントのサーバーを同一のＬＡＮに制限することは非現実的なので、IP制限を避けたほうがいいと思います。もう一点は、IP制限はサーバー移行やIP変更の時に結構漏れやすいです。動的IPのクライアントさんには全く通用しません。 非対称のキー もっとも有名な非対称キーは多分ＲＳＡでと思います。PKCS#1としてネットの一つの標準になっています。クライアントがサーバーの認証を求めたい場合は、サーバーの公開キーを使って、ＩＤとパスワードを送信します。サーバーは秘密キーを使って暗号化のパスワードを解読して、ＩＤとパスワードをチェックします。有効なＩＤとパスワードだと確認できた場合は、一時アクセスキーを発行します。その後の一定期間において、この一時アクセスキーを利用して、個々のコールの認証を行います。 ＲＳＡで暗号化できる文字列の長さは限られますから、一時アクセスキーを発行せざるを得ません。 しかし、このパタンに一つ致命的な問題が有ります。毎回送信される暗号文は同じです。この暗号文され取られれば、認証は撃破されます。一つ簡単な改善策はＩＤとパスワード以外に、今の時間を一緒に送ります。つまり、ＩＤとパスワードと時間を一つの組として暗号化されます。そうすれば、時間の経過とともに、認証用の暗号文は失効します。再利用はできなくなります。安全性は多少上がります。ただ、これもやはり甘いです。原因は簡単です。ネットではデータ転送のスピードはある程度不可知です。発信の時刻と着信の時刻はどのくらいの差が有るか、明確ではありません。このため、時間により有効性チェックは全部有る程度の含みを残します。この含みは落とし穴になります。ハッキングはリアルタイムで進行しますから、暗号文を取られてから即時にハッキングに使用されます。こうなれば、時間のチェックを通過できるようになります。不当に認証される異になります。 上記の考えで、この方式も却下されました。 データダイジェストの問答式 どんな認証方式であっても、サーバーとクライアントの間に共通の秘密を保持する必要が有ります。「非対称キー」の問題はその秘密を直接に送信している点に有ります。この問題は毎回変わらないから、不正のアクセスに利用されやすいです。このため、毎回送信の内容をランダムに変化させます。一回送信の内容は一回しか使用できません。ハッカーは一回の認証用暗号文を取得できたとしても、再利用はできず、元のパスワードを推測する事もほぼ不可能です。安全性は高いです。 この方式にも問題点が有ります。一回のみ有効のものはサーバーとクライアントは共有していないため、クライアントがサーバーに請求して、もらってくる必要が有ります。つまり、一回多くＡＰＩをコールする必要が有ります。 具体に、一回目のコールにサーバーからランダムの問題文を発行します。二回目のコールでクライアントはパスワードを使って問題文を処理した結果を回答をサーバーに送信します。サーバーは回答を確認して、一時アクセスキーを発行します。 一時アクセスキーについて 一時アクセスキーはただ往復に送信されては、前の同様な問題が発生します。このため、コールに一定のシリアル番号又はその暗号を保持したほうがいいかもしれません。ただ、同一サーバーから複数の平行コールを許したい場合は、この方式はできなくなります。 APIコールは一般にHTTPSを介するので、認証後の情報漏洩する場合は少ないかもしれません。最初の認証ステップはパスワードを特に厳重に守るものです。 実際は一時アクセスキーも一回だけネットを介したほうがいいです。必要であれば、一時アクセスキーをクライアントの公開キーで暗号化して送信します。しかし、運用上、クライアントに非対称キーを発行させることはあまり考えられません。サーバーで発行して、秘密キーを送るか、初回でHTTPS以外に暗号化せずに送信するか…… 毎回一時アクセスキーを送ることを避けたいのであれば、一時アクセスキーを使って、ＡＰＩのパラメータのメッセージダイジェストを算出して、このダイジェストを一緒に送信すればサーバーは個々のコールに検証できるようになります。 纏め セキュリティを考えますと、いろいろとやらなくては、……