メインコンテンツへスキップ
見出し画像

Excelの限界突破。LOOKUPに“時間軸を付ければ”過去データは崩れない【DigicoTips Lookup編.01】



    そのルックアップ、本当に理解してますか??


    ある日一年間の売上管理Excelファイルを見せてもらいました。
    レイアウトも綺麗で整った表に見えます。

    一見、問題はない。
    ただ
    「この商品単価に違和感が・・・同じ商品IDなのに単価が違う?」

    数式を確認してみました。
     
    ハッ!(`ロ´;)
    そこにあったのは 素のままのルックアップ。


    上席者が作るマスタファイルの更新によってVLOOKUPで取得
    これだと数か月前の単価まで書き換わる方式です。

    VLOOKUP は常に“最新の単価”しか返さないので、 別の単価が混在すること自体おかしい

    ヽ(~~~ )ノ ハテ?
    一瞬理解できませんでした。


    ルックアップとコピペの“よくある運用”


    「条件なしのルックアップで値を取得しているの?」

    と疑問を直球で聞くと
    彼女の答えは、私の斜め上をいく意外な答えだったのです。

    ━━━━━━━━━━━━━━━

    「それ、後で値のみを貼り付けしてます」

    ━━━━━━━━━━━━━━━


    「……ぇ」
    思考が数秒、停止しました。
    過去単価まで変わると困るので、その場しのぎの値貼り付けで固定してる?

    数式と直打ちの混在じゃないか。

    変に式を残さず、固定値で凍結すればいいのに、きっと後々のためルックアップの存在を残したかったのでしょう。


    この運用は一見合理的です。

    式を上書きした瞬間にデータは参照関係を失い、凍結状態になる。
    計算で求めるべき列を、値貼付けや、キーの直打ちで内職する不便

    もし、誰かが1行目の数式を全行コピー
    気が付かないまま、全ての直打ちした値まで変えてしまったら・・・

    危うい運用です。


    一方で、ルックアップを使い続ければ、また別の問題が起きます。

    マスタを更新した瞬間、過去の売上実績までもが「現在の価格」に書き換わってまうのです。

    ルックアップは「現在の参照」であり、「過去の保持」ではないからです。

    それでも単価や価格は、固定された値ではありません。
    時間とともに変化するものです。


    そして、こういう場面もきっとどこかで起きているのだろうなぁ・・と、ふと思うのです。

    誰かが「単価が変だ」と気づき、 誰かが「昨日マスタを直したからだよ」と答え、 誰かが「だから値貼り付けしろって言っただろ」とため息をつく。

    本当は誰も仕組みを理解していないのに、 “ルールを守らなかった人” だけが矢面に立つ。

    そして思うのです。
    壊れやすい仕組みほど、なぜか人間に厳しくなる。

    本来守られるべきはデータなのに、 守らされているのはいつも人間のほう

    今日もどこかで、ひとつのセルが静かに書き換わり、 その小さな変化の理由を、誰かが背負わされているのだろうなぁ、と。

    僕は思います。
    設計が悪いのに「人間のミスを許容しない仕組み」が、「人間を責める文化」を生んでいる。
    これが現実に蔓延しているのです。



    であれば、マスタ側に「いつの時点の価格か」という履歴を持たせる必要があると考えないのでしょうか?

    設計者が「履歴」を持つ手間を惜しんだツケを、現場が「値貼り付け」という内職で支払わされている。

    そんな不毛な運用を終わらせるために、ここからは私たちが本当に向き合うべき設計の話をしましょう。



    解決の方向性:マスタに履歴を持たせる

    ここで私は、一般的な「縦に積む履歴」ではなく、あえて「横履歴+連結キー(履歴塊 この記事では塊と呼びましょう) 」を使うことをお勧めします。

    なぜ、横履歴はいけないのか

    設計のセオリーでは、横に履歴を広げるのは「いつか破綻する素人の手法」だと一蹴されます。

    しかし、現実はどうでしょう。
    横に並んだ数字ほど、人間の目にとって「変化」を捉えやすいものは無いと思うのです。



    Lookupデータハブという構想

    私は、その組織で使う、あらゆる参照データを同一ブック内の各シートへ集約し、これを「Lookupデータハブ」と定義し、中心となる「商品ID」のすぐ隣に「履歴の塊」を配置する提案をします。

    この履歴塊をハブとして業務データを一括運用する手法は、複雑な多条件検索を排除し、Excelの計算負荷を最小限に抑え込みます。シンプルゆえに、動作は極めて軽い。


    理由はシンプルで、Excel の計算エンジンは

    • 参照セルの数

    • 再計算の回数

    • 依存関係の複雑さ

    • LOOKUP関数の数が多い

    • 参照先がバラバラ

    • 依存関係が増える

    • 再計算が多発する

    この3つで負荷が決まるから。


    これに対し、Digico方式は

    • LOOKUP は 1回だけ

    • 返ってくるのは ただの文字列

    • 分解は TEXTSPLIT / MID / FIND などの軽い処理

    • 依存関係が 1セルに集約される

    つまり、

    計算負荷が “LOOKUP 1回 + 軽い文字列処理” に固定される。

    Excel の計算エンジンは 文字列処理の方が圧倒的に軽い。



    一見、非効率に見えるこの「横の広がり」が、なぜ現場から「値貼り付け」という内職を駆逐できるのか。

    その具体的なロジックを、ここから解説します。



    ▼ 横履歴テンプレート(Digico方式)

    ロジック説明(連結・参照・判定・抽出方式)


    【連結】
    Sheet1:履歴を「塊」にするシート
    入力した横履歴をシステムが運搬しやすい「履歴塊」へと連結関数”&”でパッキング

    画像
    マスタテーブルに単価変更の履歴を持たせる

    【参照・判定・抽出】
    Sheet2:履歴を「展開」するシート

    「履歴塊」を解き、必要な時点のデータだけを展開します。

    画像
    作成日によって現在の単価が返ります。【E1が作成日です。この時点の単価を抽出させます。】

    ※これらは同一ブック内はもちろん、別ファイル間での連携でもその真価を発揮します。


    Digicoロジックの本質は、横履歴を作り、その履歴を連結、履歴塊を生成

    それを参照先で分解抽出し、必要なデータを得るという仕組みです。

    一撃で多くの情報を取得できるため、エクセルの負担は激減しますし、管理も楽になります。

    ”履歴を連結”
    ”参照・判定・分解”
    ができれば、どのExcelバージョンでも、持てる関数で対応できます。
    (日付での判定を使わずに、特定の行に収納されている、特定の列も抽出できます。)

    日付を判定することは、この社員はいつから役職が付き、給与が上がったのか等の応用も可能となります。

    設計次第で他にも応用例は見つかります。

    次回はExcel 2021/365で実現する「過去を壊さない」履歴保持型LOOKUPについて解説します。

    次回
    「履歴を失う世界で、僕たちは何を信じるのか」【DigicoTips Lookup編.02】


    付則・古いExcelでの実装方法


    第一話であるこの記事では、古いバージョンでの計算式を紹介します。

    これより先の記事は専門性があり、古いバージョンなので、必要性を感じない方は仕組みを読み飛ばしてください。



    Sheet1【連結】

    「日付と単価をカンマ区切りでパッキングする」という発想は、Excelを単なる表計算ソフトとしてではなく、「軽量なデータ転送エンジン」として定義し直すものです。

    この手法の凄みは、以下の2点に凝縮されます。

    1. データの「履歴塊」により参照数を減らします

    通常、別ファイルから「商品ID・日付1・単価1・日付2・単価2...」と個別にVLOOKUPを飛ばすと、計算回数はカラム数分だけ倍増し、ファイルはどんどん重くなります。

    しかし、この「履歴塊」なら、VLOOKUP一撃で全履歴を呼び出せます。

    2. 日付をシリアル値で持つ合理性

    連結関数によって日付が自動的に数値(シリアル値)に変換されるのは計算上非常に有利です。
    文字列としての「YYYY/MM/DD」ではなく、数値として塊に込めることで、受け手側のSheet2で「日付の比較計算(大小比較)」が即座に行えるようになります。


    ▼ 履歴塊の生成(古いExcelでは&でセルを連結)

    上記と同じ計算式
    =A2&","&B2&","&C2&","&D2&","&E2&","&F2&","&G2

    履歴塊の完成形:

    A001,45292,1000,45383,1200,45474,900

    これで、商品の履歴が一つのセルに詰め込まれました。
    これを参照先で分解させます。



    Sheet2【参照・判定・分解】

    ▼ LOOKUPで履歴塊を取得(古いExcel対応)
    同じブックでシートを分けた場合のルックアップの使い方

    画像
    商品IDを入れると、マスタから履歴を含んだセルが参照されます。
    =LOOKUP(B2, Sheet1!A:A,Sheet1!H:H)

    無事、履歴塊が参照されました。
    この参照したセルから、以下の方法で現在の単価を抜き取ります。


    ▼ 分解(TEXTSPLITなし・古いExcel対応)


    画像
    作成日によって現在の単価が返ります。【E1が作成日です。この時点の単価を抽出させます。】

    単価の抽出計算式 
    参照セルはお使いの環境に合わせて番地を変えてください。
    新しいバージョンのExcelであればもっと簡単な関数もあります。

    =IF(
     $E$1 < FILTERXML("<t><s>"&SUBSTITUTE($C4,",","</s><s>")&"</s></t>","//s[4]"),
     FILTERXML("<t><s>"&SUBSTITUTE($C4,",","</s><s>")&"</s></t>","//s[3]"),
     IF(
      $E$1 < FILTERXML("<t><s>"&SUBSTITUTE($C4,",","</s><s>")&"</s></t>","//s[6]"),
      FILTERXML("<t><s>"&SUBSTITUTE($C4,",","</s><s>")&"</s></t>","//s[5]"),
      FILTERXML("<t><s>"&SUBSTITUTE($C4,",","</s><s>")&"</s></t>","//s[7]")
     )
    )

    上記計算式は、現在の単価の判定式も含まれています。


    ▼ 売上データ側に必要なもの

    図で示した右上の取引日を入れていないと、履歴判定が出来ないので、シートのタイトル右端にでも、取引日や作成日を載せる癖を付けてほしいです。

    この計算式は、ルックアップした「履歴塊」の文字列から、対象日の期間に合致する単価を自動判別します。



    「横履歴を連結、参照・判定・分解・抽出する」
    という設計思想の根幹は不変です。


    ▼ なぜこの方式が強いのか

    • 過去データが壊れない

    • 作り方次第で例外もセールも全部積める

    • 未来の改定も予約できる

    • 配列に依存しない

    • 判定はシリアル値の大小比較だけ

    • 古いExcelでも動く

    • VBA不要

    • PowerQuery不要

    • RPA不要


    まとめ

    通常LOOKUPは: 「2列目を取る」 「4列目を取る」 みたいに 列番号に縛られます。
    でも Digico方式は: 2番目の値・4番目の値・6番目の値・7番目の値・最後の値・n番目の値・条件に合う値
    だけ抽出したり
    途中の値をまとめて抽出等が全部できます。
    列という概念から解放されているのが特徴です。

    重要なのは、

    • 値は変わるものとして扱う

    • その変化を履歴として横に積む

    • 履歴塊を連結し、LOOKUPで運ぶ

    • 参照した連結文字列を分解。

    • シリアル値日付で期間判定

    • 終了日は「次の開始日−1」で自動生成

    • 必要な列のみを抽出できます。

    この設計を取り入れるだけで、
    Excelは 小さなデータベース に変わります。

    このロジックを使うと、 Lookup のために個々の担当者が独自の参照表を作ったり、 人によって違う補助列を追加したりする必要がなくなります。

    また、マスタと計算式の構造が統一されるため、 参照方法が人に依存しなくなり、属人化を防ぐ手立てにもなります。

    さらに、参照シートは必要なだけ増やせるので、 組織全体で同じマスタを共有しながら、 Excel 内に自然と“データハブ”が形成される というメリットも生まれます。

    これこそが、現場が自力で構築できる、最強のデータ管理基盤だと私は確信しています。

    次回はExcel 2021/365で実現する「過去を壊さない」履歴保持型LOOKUPについて解説していきます。

    さらに洗練された現代版ロジックへと昇華する事でエクセルの進化の履歴も確認できます。



    LookupTips履歴

    【DigicoTips Lookup編.02】

    【DigicoTips Lookup編.03】

    【DigicoTips Lookup編.04】



     
     
    PCと幾数十年・現在はスマホデータベースに夢中 日本では普及していないMemento Databaseを使ってます。 最先端技術記事を見ても、部品の山 現場で動くシステムは、技術でなく現場を知る発想。 大手もアイデアで短時間DX化を図る時代、私はスマホでDXを伝えたい。

    あなたへのおすすめ