はじめに 本 書 は エクセル 習 熟 度 を 高 めるための 本 ではありません エクセルというツールを 使 いながら 在 庫 管 理 のスムーズなスタートを 支 援 し 運 用 中 の 負 担 を 軽 減 する 為 の 本 です 書 籍 やWebページで エクセルによる 在 庫 管 理 を 紹 介 しているものはあります それらは 数 式 や 関 数 マクロや VB など 設 定 方 法 に 関 する 情 報 が 中 心 です エクセル 教 本 としては 良 く 出 来 ていると 思 うのですが 紹 介 されているのは1パター ンであり 明 細 行 の 上 限 の 問 題 や 誤 操 作 によるデータ 削 除 など 実 際 に 運 用 する 上 で 重 要 な 情 報 が 提 供 されることは 殆 どありません 600 件 以 上 の 在 庫 管 理 の 相 談 を 受 け 50 以 上 の 在 庫 管 理 システムを 導 入 した 私 の 目 か ら 見 ると 本 気 で 業 務 で 使 うことを 考 えているとは 思 えないのです 業 務 で 使 うのであれば エクセルであっても 専 用 の 在 庫 管 理 システムであっても 考 慮 しなければならないことがあります エクセル 運 用 で 陥 りがちな 準 備 不 足 を 避 けて 欲 しい エクセルの 限 界 を 超 えた 運 用 を 行 い 無 駄 な 時 間 を 費 やさないで 欲 しい そして エクセルで 快 適 な 在 庫 管 理 を 運 用 して 欲 しい そんな 思 いで 本 書 を 書 き 上 げました 本 書 は データベースは 扱 ったことがなく プログラミングも 書 けないという 方 に 向 けの 内 容 となっております そのために マクロやVBAは 使 わない 方 針 で 書 いています また 仕 入 金 額 や 売 上 金 額 などの 金 額 に 関 する 処 理 は 対 象 から 外 しています 現 物 管 理 の 強 化 を 目 指 し 入 出 庫 情 報 を 入 力 することで 在 庫 数 が 更 新 される 運 用 を 前 提 に 話 を 進 めます エクセルでの 在 庫 管 理 を 成 功 させるコツは エクセルが 出 来 ることを 知 ることです どのようなパターンがあり 自 分 にあった 運 用 はどのようなものかを 見 定 めてください 本 書 が スムーズな 在 庫 管 理 の 一 助 になれば 幸 いです 株 式 会 社 インフュージョン 角 三 十 五
第 1 章. 在 庫 管 理 の 基 礎 知 識...4 1-1. 在 庫 管 理 の 目 的...4 1-2. 在 庫 はなぜ 狂 うのか...5 1-3. 在 庫 の 管 理 ツール...6 1-4. 在 庫 管 理 の 分 類...7 第 2 章. 在 庫 管 理 におけるエクセル 運 用 のポイント...9 2-1. エクセルによる 在 庫 管 理 の3タイプの 概 要...9 2-2. 在 庫 管 理 で 行 うべき 処 理...10 2-3. 入 出 庫 件 数 の 上 限 について...11 2-4. 品 目 数 と 操 作 性...13 2-5. データ 削 除 とシートの 保 護...15 2-6. データ 増 加 と 処 理 速 度...17 2-7. エクセルに 寄 せられる 期 待...19 第 3 章. 在 庫 管 理 で 必 要 なエクセル 知 識...20 3-1. 数 式 を 設 定 する 為 の 基 礎...20 (1) 数 式 とは...20 (2) セル 参 照 の 基 本...21 (3) コピーによる 表 の 拡 張 その1...22 (4) コピーによる 表 の 拡 張 その2...23 3-2. 関 数 を 使 いこなせ...24 (1) 関 数 とは...24 (2) 調 整 数 を 導 きエラーを 抑 制 する IF 関 数...24 (3) 同 一 品 目 の 入 出 庫 の 合 計 を 導 く SUM 関 数...26 (4) 別 品 目 の 入 出 庫 には SUMIF 関 数...27 (5) 品 目 台 帳 と VLOOKUP 関 数...28 3-3. 在 庫 管 理 で 多 用 されるテクニック...29 (1) 数 値 しか 入 力 を 受 け 付 けない 方 法...29 (2) 日 付 の 入 力 規 則 と 年 表 示 に 関 する 注 意 点...30 (3) ワーク 列 の 非 表 示...33 (4) 検 索 で 陥 りがちな 勘 違 い...35 (5) 数 式 を 見 やすくする 名 前 定 義...35 (6) 入 力 ミスを 減 らすドロップダウンリスト...37 (7) 数 式 を 守 るシートの 保 護...40 (8) ブックの 共 有 と 同 時 入 力...41 第 4 章. 基 本 の3タイプ...44 4-1. 3つの 基 本 タイプ...44 4-2. 単 票 タイプ...45 4-3. 移 動 表 タイプ...47 2
4-4. データベースタイプ...48 4-5. タイプ 別 に 見 る 運 用 負 荷 の 限 界...49 4-6. タイプ 別 に 見 るロケーション 管 理...50 4-7. タイプ 別 に 見 るロット 管 理...51 第 5 章. 実 運 用 に 向 けて...52 5-1. エクセルブックの 最 終 確 認...52 (1) 入 力 規 則 と 書 式 設 定 の 確 認...52 (2) 列 の 非 表 示...52 (3) セルの 保 護...53 (4) 不 要 データの 削 除...53 5-2. 入 力 セルへの 移 動 方 法 の 検 証...54 (1) 単 票 タイプの 確 認...54 (2) 移 動 表 タイプの 確 認...55 (3) データベースタイプの 確 認...56 5-3. バーコード 活 用...57 (1) バーコードスキャナタイプ...57 (2) ハンディターミナルタイプ...57 5-4. 設 計 資 料 と 運 用 ルールの 作 成...58 3
第 1 章. 在 庫 管 理 の 基 礎 知 識 エクセルというツールを 使 って 業 務 改 善 を 行 うには まず 業 務 そのものを 理 解 する 必 要 が あります エクセルの 学 習 の 前 に まずは 在 庫 管 理 の 基 礎 を 確 認 したいと 思 います 1-1. 在 庫 管 理 の 目 的 あなたが 管 理 する 対 象 は 何 でしょうか? 備 品 かもしれませんし 原 材 料 かもしれません 販 売 する 商 品 製 品 かもしれません いずれにしても 企 業 や 法 人 としては 資 金 を 投 入 し 現 物 に 変 えたもの 在 庫 を 正 確 に 把 握 できないと 正 確 な 経 営 状 況 が 掴 めなくなります コンピュータ 上 の 論 理 在 庫 で 月 次 決 算 しても 正 確 な 利 益 が 計 算 されません 実 地 棚 卸 で 差 異 が 大 きいと 経 営 層 から 問 題 視 されるはずです また 在 庫 精 度 が 悪 いと 過 剰 在 庫 を 招 きます 過 剰 在 庫 はキャッシュフローの 悪 化 を 招 き 資 金 不 足 の 要 因 となります 実 務 面 では 欠 品 が 続 出 します 在 庫 精 度 が 悪 いと 過 剰 在 庫 を 招 きますが 一 方 で 欠 品 も 多 く 発 生 させます 要 らない 在 庫 は 多 いけど 欲 しい 在 庫 が 無 い という 状 態 になるのです サービスレベルを 低 下 させず 少 ない 在 庫 で 効 率 的 に 運 営 するためには 正 確 な 在 庫 の 把 握 は 欠 かせません このように 経 営 面 業 務 面 どちらから 見 ても あなたが 目 指 す 在 庫 精 度 の 向 上 は 大 変 重 要 なのです <まとめ> 在 庫 精 度 が 悪 いと 正 確 な 経 営 状 況 が 掴 めず 過 剰 在 庫 によるキャッシュフローが 悪 化 し サービスレベルも 低 下 します 在 庫 精 度 の 向 上 は 重 要 な 経 営 課 題 です 4
1-2. 在 庫 はなぜ 狂 うのか そうした 大 切 な 在 庫 情 報 ですが コンピュータ( 帳 簿 ) 在 庫 と 実 在 庫 はなぜズレるので しょうか この 要 因 はひとつではありません 在 庫 が 変 動 する 業 務 すべてがズレの 原 因 となりえます < 在 庫 のズレが 生 じる 要 因 > (1) 保 管 が 乱 雑 で 発 見 できない 発 見 できなければ 無 いのと 同 じ (2) 入 出 庫 のミス 受 入 れ 払 出 し 時 の 照 合 不 足 (3) 入 力 ミス 記 帳 ミス 伝 票 から 入 力 する 際 のコード 間 違 え 数 量 間 違 え (4) 棚 卸 のミス カウントミス 入 力 ミス (5) 例 外 処 理 のミス 返 品 処 理 の 不 徹 底 緊 急 出 庫 の 不 徹 底 営 業 サンプル 出 荷 の 不 徹 底 (6) 盗 難 横 流 し 在 庫 管 理 は 人 が 深 く 関 わる 業 務 です いくら 良 いシステムがあっても 人 が 正 しく 運 用 しなければ 正 しい 在 庫 は 掴 めません ひとつひとつのことを 確 実 に 行 い コンピュータ( 帳 簿 ) 在 庫 と 実 在 庫 のズレをなくし ましょう <まとめ> 在 庫 がズレる 要 因 はひとつではありません 在 庫 が 変 動 する 業 務 を ひとつひとつを 確 実 に 行 いましょう 5
1-3. 在 庫 の 管 理 ツール 在 庫 管 理 を 行 うにはどのようなツール( 道 具 )があるのでしょうか その 規 模 やシステム 化 により 帳 票 での 管 理 エクセルでの 管 理 在 庫 管 理 システムで の 管 理 の3つに 分 けて 説 明 します 帳 票 での 管 理 会 社 に 在 庫 管 理 システムが 全 く 導 入 されていないことは 少 ないと 思 います しかし 会 社 には 備 品 や 資 材 のように そうしたシステムの 管 理 対 象 になっていない 在 庫 も 多 く 存 在 するはずです こうしたアイテムが 品 目 数 動 きともに 少 なければ 帳 票 で 管 理 されていることでしょ う 入 庫 出 庫 があるたびに 書 込 み 新 たな 在 庫 数 も 書 き 込 んで 管 理 していきます エクセルでの 管 理 帳 票 管 理 の 発 展 形 がエクセルでの 管 理 です 帳 票 管 理 はさすがに 非 効 率 だけど 在 庫 管 理 システムを 導 入 するほどの 規 模 ではない ま たは 導 入 したいけど 予 算 が 無 いという 場 合 もあるでしょう エクセルの 場 合 は 帳 票 と 違 い 入 出 庫 を 入 力 すれば 自 動 的 に 在 庫 数 は 更 新 します この 教 材 は エクセルでの 管 理 を 成 功 させるための 情 報 を 提 供 しています 在 庫 管 理 システムでの 管 理 品 目 数 や 入 出 庫 が 多 い 場 合 や ロケーション( 棚 番 )やロット 賞 味 期 限 別 の 管 理 が 必 要 になると 処 理 も 複 雑 となりデータ 量 も 増 大 します また ハンディーターミナルや PDA を 用 いた スムーズな 現 場 入 力 が 必 要 なケースもあり ます そうした 場 合 は 在 庫 管 理 を 行 うための 専 用 システムでなければ 対 応 できません 入 出 庫 を 入 力 すれば 在 庫 情 報 や 履 歴 棚 卸 や 集 計 表 など 多 角 的 な 情 報 が 取 得 可 能 です <まとめ> 在 庫 管 理 は 帳 票 での 管 理 エクセルでの 管 理 在 庫 管 理 システムでの 管 理 に 分 けられます 後 者 になるほど システム 化 での 支 援 機 能 が 強 力 で より 大 規 模 な 管 理 が 可 能 です 6
1-4. 在 庫 管 理 の 分 類 在 庫 管 理 といっても 様 々な 違 いがあります どのような 違 いがあるのかを 確 認 してみましょう ここでは 管 理 項 目 の 違 いと 運 用 規 模 の 違 いに 分 けて 主 な 確 認 ポイントを 列 挙 してみます ぜひ ご 自 身 の 管 理 すべき 項 目 をチェックしてください このチェック 内 容 から エクセルでの 適 したタイプが 見 えてきます < 在 庫 管 理 における 主 な 管 理 すべき 項 目 の 違 い> 品 目 モノに 関 する 情 報 品 目 +ロット 品 目 + 賞 味 期 限 必 要 なし 在 庫 管 理 項 目 保 管 場 所 情 報 倉 庫 ロケーション( 棚 番 ) 必 要 なし 状 態 良 品 / 不 良 品 入 庫 出 庫 のみ 作 業 返 品 などの その 他 作 業 含 む 作 業 履 歴 項 目 必 要 なし 担 当 者 必 要 必 要 なし 仕 入 先 出 荷 先 必 要 チェック 在 庫 管 理 項 目 が 品 目 のみ の 場 合 は 帳 票 やエクセルでも 管 理 しやすいのですが ロッ トやロケーション 状 態 が 加 わると 複 数 項 目 の 組 み 合 わせで 管 理 しなければなりません 組 み 合 わせパターンが 増 えるため 運 用 負 担 も 増 えますし 数 式 も 複 雑 になります エクセルでの 動 作 が 重 い という 場 合 も その 多 くが 在 庫 管 理 項 目 の 複 雑 さが 原 因 とな っています エクセルは 簡 単 に 項 目 を 増 やせますが シンプルにまとめることが 成 功 のポイントです 7
< 在 庫 管 理 における 運 用 規 模 の 違 い> チェック 品 目 数 ~100 100~300 300~1,000 入 出 庫 明 細 数 ( 日 ) ~10 10~50 50~100 100~ 入 力 人 数 1 名 2 名 3 名 ~10 名 10 名 ~ ロット 管 理 がある 場 合 は 品 目 数 にその 組 み 合 わせのパターン 数 を 入 れて 下 さい エクセルでどこまで 管 理 できるのですか? という 質 問 をよく 受 けます その 根 拠 は4 章 で 紹 介 しますが 品 目 数 300 がひとつの 目 安 と 考 えてください <まとめ> 在 庫 管 理 の 種 類 を 管 理 項 目 と 運 用 規 模 に 分 けてポイントをリストアップ 在 庫 管 理 項 目 が 複 数 になると 複 雑 化 する エクセル 運 用 の 限 界 は 品 目 数 300 が 目 安 8
第 2 章. 在 庫 管 理 におけるエクセル 運 用 のポイント エクセルは 表 計 算 ソフトウェアです その 特 性 を 意 識 しながら 自 分 に 合 う 在 庫 管 理 を 考 え なければません ここでは 表 計 算 で 在 庫 管 理 を 運 用 する 場 合 のポイントを 確 認 していき ます しっかりと 確 認 してください 2-1.エクセルによる 在 庫 管 理 の3タイプの 概 要 エクセルでの 在 庫 管 理 は 大 きく3つのタイプに 分 けられます ここではポイントだけ 確 認 してください 詳 しくは4 章 で 説 明 します 1 単 票 タイプ 1 品 1 枚 の 表 記 入 された 紙 を 定 期 的 に 回 収 しエクセルへ 入 力 するなど 手 書 き 管 理 との 併 用 に 向 いている 一 定 期 間 の 明 細 行 の 上 限 を 決 めることが 望 まれる ( 変 更 が 大 変 ) 2 移 動 表 タイプ 品 目 と 日 付 のマトリックスの 表 同 一 日 付 の 入 出 庫 を 集 約 して 入 力 入 出 庫 の 大 まかな 流 れを 確 認 したい 場 合 に 向 いている 納 品 書 控 での 確 認 など 入 出 庫 情 報 は 別 管 理 で 良 い 場 合 に 適 している 3データベースタイプ 入 出 庫 表 と 品 目 台 帳 (マスタ)が 分 離 している 品 目 の 統 廃 合 が 多 い 場 合 に 向 いている 品 目 単 位 での 入 出 庫 の 流 れを 確 認 するには 並 び 替 えが 必 要 一 定 期 間 の 明 細 行 と 品 目 数 の 上 限 を 決 めることが 望 まれる ( 変 更 が 大 変 ) 9
2-2. 在 庫 管 理 で 行 うべき 処 理 システムで 現 在 庫 を 正 確 に 算 出 するには どのようにすればよいのでしょうか? ある 時 点 での 在 庫 情 報 と ある 時 点 からの 入 出 庫 情 報 この2つの 情 報 があれば 在 庫 数 の 算 出 は 可 能 です ある 時 点 での 在 庫 情 報 ある 時 点 から の 入 出 庫 情 報 システム 現 時 点 での 在 庫 情 報 特 定 品 目 の 現 在 庫 数 を 計 算 式 で 表 すと 以 下 のようになります 現 在 庫 数 = ある 時 点 での 在 庫 数 + ある 時 点 からの 入 庫 数 合 計 - ある 時 点 からの 出 庫 数 合 計 10
2-3. 入 出 庫 件 数 の 上 限 について 少 し 実 践 的 な 話 に 入 りましょう 下 にあるような 在 庫 台 帳 ( 単 票 )タイプのレイアウトで 考 えてみます こうしたレイアウトは 昔 からある 帳 票 での 管 理 で 多 く 見 られるものです 帳 票 そのもののイメージで 管 理 できますので 紙 からエクセルへの 移 行 もスムーズに 出 来 ますし 帳 票 との 併 用 管 理 にも 向 いています 在 庫 数 ( 今 月 繰 越 )のセルには 先 月 繰 越 数 から 入 庫 分 を 加 算 して 出 庫 分 を 減 算 する 数 式 や 関 数 が 記 述 されています 先 ほどの 式 をこの 表 の 用 語 に 変 更 すると 以 下 のようになります 現 在 庫 数 = ある 時 点 での 在 庫 数 + ある 時 点 からの 入 庫 数 合 計 - ある 時 点 からの 出 庫 数 合 計 今 月 繰 越 = 先 月 繰 越 + 入 庫 数 合 計 - 出 庫 数 合 計 この 例 のレイアウトでは 1 品 目 につき1ヶ 月 12 明 細 です 帳 票 ならば 超 えてしまう 場 合 はもう1 枚 足 せばよいだけなのですが エクセルでは 数 式 が 絡 むのでそう 簡 単 にはいきません 今 月 繰 越 数 は 来 月 用 シートの 先 月 繰 越 数 になるように 数 式 が 設 定 されていますので 帳 票 と 同 じように 空 いている 表 を 使 う 訳 にはいかないのです レイアウトを 変 更 して 明 細 数 を 拡 張 して 対 応 することとなります 11
既 に 入 力 されている 内 容 を 崩 さずに 表 のレイアウトを 変 更 するのは 大 変 工 数 もかかりますし リスクの 高 い 作 業 でもあります 運 用 中 には なるべくレイアウト 変 更 が 発 生 しないようにしたいものです エクセルがあくまでも 表 のため 予 め 用 意 した 件 数 を 超 えると レイアウト 変 更 が 発 生 してしまいます 品 目 数 と 入 出 庫 明 細 数 を 確 認 し 予 め 用 意 する 適 切 な 件 数 を 決 めてから 開 始 しましょう <まとめ> エクセルは あくまでも 表 予 め 用 意 した 行 数 や 列 数 を 超 えると レイアウト 変 更 が 必 要 となります 最 初 に 適 切 な 件 数 を 確 認 しましょう コーヒーブレイク 平 成 12 年 春 期 の 初 級 システムアドミニストレータ 試 験 で 表 計 算 ソフトを 用 いた 商 品 別 在 庫 台 帳 に 関 する 問 題 が 出 ています s 1つの 商 品 について1 年 分 の 入 出 庫 を 登 録 する 単 票 タイプ なのですが 明 細 行 は, 過 去 の 入 出 庫 回 数 から 当 期 分 として 最 大 500 行 あれば,すべて 入 力 で きることは 確 認 してある と 書 いてあります やはり 上 限 を 設 けていますね それにしても1 品 目 あたり 500 行 もあったら 使 いにくそうな 気 がしますが 12