Excel で QR コード在庫トラッカーを構築 – シンプル、コスト効率、信頼性が高い

 

Excel は動的ツールであり、ユーザーは軽量の在庫管理のための在庫トラッカーとして使用できます。高価なソフトウェアを購入せずにシンプルな在庫システムが必要な場合は、QR ベースのトラッカーが最もクリーンなセットアップの 1 つです。 QR 在庫トラッカーは、スキャンごとに新しいログ エントリ (追加のみ) が作成される場合に最適に機能し、Excel は最新のログ行から各アイテムの現在のステータスを計算します。最もスムーズなユーザー エクスペリエンスは、QR コードによって事前に入力された Microsoft フォームが開くときです。中小企業、倉庫、学校、家庭組織に最適です。

このチュートリアルでは、Excel を使用して QR コード在庫トラッカーを構築する方法を説明します。

ステップ 1:Excel ワークブックを設定する

• Excel を開いて新しいファイルを作成します
• OneDrive に保存します (モバイル同期にとって重要)
• 3 つのシートを作成します:
  • 在庫 (マスターリスト)
  • スキャンログ (ここではすべてのスキャンが行になります)
  • ダッシュボード (現在のステータス + 簡単な概要)

この構造をインベントリ シートで使用します。

• アイテムID
• 製品名
• カテゴリ
• 現在の在庫
• 最小在庫
• 場所
• QRテキスト
• QR イメージ

テーブルに変換:

• セル範囲を選択します
• 挿入に移動します タブ>> テーブルを選択します
• 「テーブルにヘッダーがある」 にチェックを入れます。
• [OK] をクリックします。
• テーブル デザインに移動します。 タブ>> 「在庫」という名前を付けます。

ルール:

• ItemID は一意である必要があります (これが QR コード内に入る)
• QRText は、QR コードにエンコードされた正確な文字列を保存します

ItemID を生成 (シンプル、読みやすい):

ItemID では、次のような一貫した形式を使用します。

• ITM-0001、ITM-0002、…

自動数式が必要な場合は、次を使用します。

="ITM-"&TEXT(ROW(A1),"0000")

• 数式を下にドラッグします

QRText の作成 (オプション):

QRText では、クリーンで安定した状態に保ちます。

QR コードの内容は ItemID=ITM-0001 のようになります。 。これにより、スキャン エラーが回避され、後の解析が容易になります。

ステップ 2:QR コードを自動的に生成する

オプション A:Excel を使用して QR コードを一括生成する

Inventory テーブルの QRImage 列の最初のデータ行に次の数式を入力します。

=IMAGE("https://api.qrserver.com/v1/create-qr-code/?size=180x180&data=" & [@ItemID])

• 数式を下にドラッグします
• これにより、ItemID のみを含むクリーンで高品質の QR コードが作成されます

代替の無料 API (QR サーバーが遅い場合):goqr.me または https://chart.googleapis.com/chart?chs=180×180&cht=qr&chl=

オプション B:無料のオンライン ジェネレーターを使用する

• QR Code Generator (qr-code-generator.com) や QRCode Monkey (qrcode-monkey.com) などの無料の QR コード ジェネレーターにアクセスする
• アイテムごとに、ItemID のみを含む QR コードを作成します
• 各 QR コードを PNG または JPG としてダウンロードします
• QR コードを印刷して物理的なアイテムに添付します

プロのヒント: アイテムが多い場合は、バッチ QR コード ジェネレーターを使用します。 QRExplore のようなウェブサイトでは、CSV ファイルをアップロードして複数の QR コードを一度に生成できます。

ステップ 3:QR コード ラベルを印刷する

Excel で印刷可能なラベル シートを作成し、アイテムに QR コードを添付できるようにします。

• ItemID、ProductName、QRImage を選択します
• ラベルという名前の新しいシートにコピーして貼り付けます
• 印刷用の形式:
  • QR が収まるように行の高さを設定します
  • ページ レイアウトに移動します。 タブ>> 余白を選択します>>狭いを選択します
• ステッカー用紙に印刷するか、ラベルを切り取ってください
• 商品/棚/箱にラベルを貼ります

ステップ 4:データを Excel に送信するスキャン アプリを選択する

オプション A:履歴をエクスポートできるスキャン アプリを使用する

QR コードを読み取り、データを Excel に送信するスキャン アプリを使用できます。

• アプリ名:スキャン to Excel – QR およびバーコード (Dynamiq Data による)
• 無料枠では、ほとんどのユーザーに十分なスキャンが提供されます。サブスクリプションの料金は、頻繁に使用する場合、通常、月額約 3 ～ 5 ドルかかります
• Google Play からインストールする
• アプリを開いて Microsoft アカウントでサインインします
• OneDrive Excel ファイルに接続する
• スキャンログを選択します。 テーブル
• スキーマを設定します:
  • タイムスタンプ>> 自動タイムスタンプ
  • アイテムID>> スキャンデータ
  • アクション>> 手動選択 (各スキャン後に「受信」または「発行」を選択します)
  • 数量>> デフォルトは 1
• スキャンを開始

ほとんどの QR スキャナはスキャン履歴を保存し、エクスポート/共有が可能です。

• QR ラベルをスキャンする
• アプリに短いメモをすぐに入力します（メモがサポートされている場合）
• 一日または週の終わり:スキャン履歴を CSV にエクスポート
• ScanLog に貼り付け/追加

オプション B:スキャン送信用の Microsoft フォームを作成する

Microsoft フォームを作成し、携帯電話で QR コードをスキャンしてトランザクションを送信し、Excel を自動的に更新することができます。これはほとんどの人が望んでいることです。

• 挿入に移動します タブ>> [新しいフォーム] をクリックします。
• フォームインターフェイスが開きます。次のフィールドを追加します:
  • アイテムID (短い答え)
  • アクション (選択肢):受け取る、発行する
  • 数量 (短い答え):後で数値に変換します。 Excel で

• [回答を収集] をクリックします。

• [リンクをコピー] をクリックします。 URL を共有する

このフォームは、すべての送信が新しい行となる、接続された Excel ワークブックを自動的に作成します。いくつかのサンプルを送信してテストします。

• スマートフォンで QR をスキャン
• アイテムIDを確認します (または手動で入力します)
• アクションを選択します
• 数量を入力します
• [送信] をクリックします。

• Excel ワークブックを開いて、フォームの回答シートを確認します
• すべての送信は自動的に行として追加されます
• 列名を更新し、不要な列を削除する
• シートに「ScanLog」という名前を付けます

オプション C:スキャン時に項目 ID フィールドを自動入力する

ITM-0001 のみを QR コードに含める代わりに、事前に入力されたフォームのリンクをエンコードできます。 Microsoft Forms は、URL パターンを使用した事前入力フィールドをサポートしています (フォーム設定に応じて)。各 QR コード内に事前入力フォームのリンクをエンコードするように在庫シートを設定できます。

• フォームを開く>> 3 点>> 事前入力リンクを取得 を選択します

• サンプルのアイテムIDを入力します。 (ITM-0001 など)
• [事前入力リンクを取得] をクリックします。
• [リンクをコピー] をクリックします。 生成されたリンクを取得する
• サンプルの ItemID を置き換えます プレースホルダー付き

事前に入力された URL は次のようになります。

https://forms.office.com/Pages/ResponsePage.aspx?id=DQSIkWdsW0yxEjajBLZtrQAAAAAAAAAAAAN__gCq2f5UNloxU0IwWTM0Nj……..=ITM-0001

Excel でアイテムごとの PrefilledLink を作成します。

• インベントリ シートに PrefilledLink という名前の新しい列を挿入します
• コピーした事前入力 URL を FormPrefillTemplate に貼り付けます。 セル (G2 など)
• 次の数式を挿入して、テンプレート内のサンプル ITM-0001 を現在の行の項目 ID に置き換えます

=SUBSTITUTE($G$2,”ITM-0001”,[@ItemID])

これで、各行に独自の一意の URL が設定され、正しいItemID がすでに入力されたフォームが開きます。

各 PrefilledLink を QR コードに変換します。

QRImage 内 列に、次の式を挿入します。

=IMAGE(“https://api.qrserver.com/v1/create-qr-code/?size=180×180&data=” &ENCODEURL([@PrefilledLink]))

事前に入力されたフォームのリンクを開く QR 画像が返されます。

アイテムの印刷とラベル付け:

以下を示す単純なラベル レイアウト (同じシートまたは別の「ラベル」シート) を作成します。

• アイテムID
• 製品名
• QR イメージ

シール用紙に印刷し、商品にラベルを貼り付けます。ラベルが存在した後は、ItemID を変更しないでください。

スキャン ワークフローをテストします:

ほとんどのユーザーは、電話スキャナを使用してスキャンします。

• スマートフォンでスキャナを開きます
• QR ラベルをスキャンします
• ItemID が入力されたフォームが自動的に開きます

• ItemID がすでに入力されていることを確認します
• アクションと数量を選択します
• [送信] をクリックします。

これは現実世界の最もスムーズなワークフローです。

ステップ 5:スキャン ログ構造を設計する

すべてのスキャンはインベントリ トランザクションとして扱われます。 Microsoft Forms は、タイムスタンプ列と質問ごとに 1 つの列を持つ回答テーブルを作成します。回答シートの名前を ScanLog に変更します。 .

ScanLog に次の列が含まれていることを確認してください:

• タイムスタンプ
• アイテムID
• アクション
• 数量

このログは追加専用のままにする必要があります。トラッカーは履歴が保存され、在庫は常に完全なログから計算されるため、信頼性が高くなります。

重要な原則:

• このテーブルは追加専用です
• 行は編集または削除されません

ステップ 6:現在の在庫を計算する

Inventory テーブルの CurrentStock 列で、次の式を使用して手持ち在庫を計算します。

=LET(
id, [@ItemID],
initial, 0,
received, SUMIFS(ScanLog[Quantity], ScanLog[ItemID], id, ScanLog[Action], "Receive"),
issued, SUMIFS(ScanLog[Quantity], ScanLog[ItemID], id, ScanLog[Action], "Issue"),
initial + received - issued
)

これにより、スキャンするたびに在庫が自動的に更新されます。

在庫残りわずかのアラート:

• 現在の在庫を選択します。 列>> 条件付き書式を選択します。>> セルのハイライトを選択します。>>未満を選択します
• 最小在庫を選択します>>フォーマットを選択します
• [OK] をクリックします。

株価が低くマイナスの場合は強調表示されます。

ステップ 7:シンプルなダッシュボードを構築する

ダッシュボード シートを作成し、インベントリを要約します。

ユニークアイテムの合計:

=COUNTA(UNIQUE(Inventory[ItemID]))

手持ち在庫の合計:

=SUM(Inventory[CurrentStock])

オーバードローアイテム (マイナス):

=COUNTIF(Inventory[CurrentStock], "<0")

最終スキャン時間:

合計トランザクション数:

=COUNTA(ScanLog[Timestamp])

残り少ない在庫を表示:

=FILTER(Inventory[[ItemID]:[Location]], Inventory[CurrentStock] < Inventory[MinStock], "No items to reorder")

ワークフローの概要

<オル>

携帯電話またはスキャナ アプリで商品の QR コードをスキャンします

受信または発行を選択します

Excel は自動的に更新されます (ワークブックが同期していない場合は更新されます)

在庫は常に最新です

結論

上記の手順に従って、Excel を使用して QR コード在庫トラッカーを構築できます。このシステムは、プロセスがユーザーにとってシンプルであり、レポートの信頼性を維持できる場合に最も効果的に機能します。各アイテムに永続的なItemIDを割り当て、QRラベルを生成し、事前入力されたMicrosoftフォームを介してすべてのスキャンを記録することにより、各インタラクションは、在庫セルを手動で編集することなく、クリーンなタイムスタンプ付きのレコードになります。無料または低コストのスキャナー アプリを使用することも、Microsoft Forms を使用して独自のワークフローを構築することもできます。 Excel の数式と軽量のダッシュボードにより、在庫レベルの監視、取引履歴の確認、在庫の増加に伴う変更の監査が簡単に行えます。

ソリューション付きの高度な Excel 演習を無料で入手しましょう!