- 期間
- 2026年6月8日〜8月14日（月次会議で不良率が0.0%と出た日から、関数の集計シートをピボットテーブルに置き換え終えるまでの約2か月。その後の運用は9月上旬まで）
- 対象
- 検査データの集計ブック1つ（元データ2シート・関数の集計9シート・グラフ3シート、元データ約11,800行、18MB）
- 体制
- 品質保証の管理職である私が置き換えを担当。月次集計の作業は同じ部の後輩1人。会社のExcelは2021
- 結論
- 関数の集計9シートのうち5枚をピボット4枚に置き換え、3枚を廃止し、客先提出の様式1枚だけは関数のまま残した。月次集計は55分から12分、ブックは18MBから6MBに減り、更新ボタンの押し忘れはオプション1つで消えた
共有フォルダの「検査集計_2026.xlsx」は、アイコンの横に18MBと出る。ダブルクリックしてから最初のシートが表示されるまで40秒。下のタブは14枚あって、右端のほうは画面に収まらず、矢印で送らないと見えない。集計シートの1つを開いてセルをクリックすると、数式バーに212文字の式が出る。SUMIFSの中にIFERRORが入り、その中にまたSUMIFSが入っている。
この式を書いたのは3年前の私だ。
0.0%と出た月曜の会議
6月8日の月次会議で、4月の工程内不良率が0.0%と表示された。前の月は0.6%で、4月に急に良くなる理由は無い。会議のあとに後輩と2人でブックを開くと、集計シートの4月の行に#REF!が出ていて、グラフ側はそれを0として描いていた。
会議の場では、製造課長が「4月は何かやりました？」と聞いてきて、私は「集計の側を確認します」としか言えなかった。0.0%を見て喜んだ人は1人もいない。全員が数字のほうを疑い、3年間この集計で報告してきて疑われたのは初めてだった。会議室を出るとき、製造課長が「まあ、0.6%のままでしょ」と言い、そのとおりだった。
原因は5月の末に後輩が元データのシートに1行挿入したことで、集計側の式が参照している範囲の途中に行が入り、範囲がずれていた。後輩は行を挿入したことを覚えていて、「式が壊れるとは思わなかった」と言った。私も、行を1つ足しただけで壊れる式を3年前に自分が組んだことを、その日まで忘れていた。
この日、関数を直すのではなく、集計のやり方を変えると決めた。決める前に、ピボットテーブルについて自分が何を知っていて何を知らないかを、一度確かめる必要があった。
サポートページを2本読んだ夜
ピボットテーブルは使ったことがある。ただ、月次の集計に使わなかったのは「元データを直しても勝手には変わらない」という印象があったからで、その印象がどこから来たのかを説明できなかった。6月10日の夜、自宅のノートPCでMicrosoftのサポートページを2本読んだ。検索で先に出てくるのは解説サイトのほうで、サポートページにたどり着くまでに15分ほど回り道をしている。
1本目は「ピボットテーブルのデータを更新する」のページ。ここに「以前のバージョンの Excel では、ピボットテーブルは自動的に更新されません」とあり、私の印象はここから来ていた。同じページに、ブックを開くときに自動で更新する設定として「ファイルを開くときにデータを更新する」というオプションが書いてある。自動更新の機能そのものは、読んだ時点ではMicrosoft 365のInsiderプログラム向けと注記されていて、会社のExcel 2021には無い。
2本目は元データの範囲の話で、範囲をセル番地で指定したままだと、あとから足した行はピボットに入らない。元データをテーブルにしておくと、行が増えてもテーブルの範囲として扱われる。これも知っていたつもりで、実際には3年前の私は範囲を番地で指定していた。
勘違いしていた3つ
読み終えて、勘違いが3つあると分かった。
1つ目は更新の話で、元データを直せばピボットも変わると思っていたのは逆で、更新の操作をしない限り変わらない。ただし開くときに更新する設定はある。2つ目は範囲の話で、テーブルにしていなければ足した行は入らない。3つ目は不良率の出し方で、これはページには書いていなかったが、試して分かった。元データの行ごとに不良率の列を持たせ、ピボットで「平均」を取ると、検査数の多いロットも少ないロットも同じ重みで平均される。4月の値でやってみると、正しい0.6%に対して0.9%が出た。不良率は不良数の合計を検査数の合計で割る必要があり、ピボットでは集計フィールドにその式を入れれば出る。
3つ目に気づいたのは6月12日で、ここで気づいていなければ、置き換えたあとの月次会議で0.9%を報告していた。この3つを紙に書いてから、置き換えの計画を立てた。
9枚を4枚にする2か月
集計シートは9枚あった。1枚ずつ、何を出しているかを書き出すと、月別の不良率が2枚（工程別と製品群別）、不良項目別の件数が2枚、検査員別の検査数が1枚、ロット別の一覧が1枚、客先に提出する月次成績書の様式が1枚、残り2枚は何のために作ったか私も後輩も思い出せなかった。
行き先はこうなった。月別2枚と不良項目別2枚と検査員別1枚は、元データをテーブルにしたうえでピボット4枚に置き換えた。ロット別の一覧はテーブルそのものにフィルターをかければ足りるので、シートごと廃止。思い出せない2枚も廃止。客先の様式1枚は、客先が指定したセル位置に数字を入れる形で、ピボットにすると行の並びが変わるたびに位置がずれる。ここだけ関数を残し、参照先を旧集計シートからピボットに変えた。
作業は6月15日に始めて8月14日に終えた。実働は私が延べ9時間で、そのうち4時間は元データ約11,800行をテーブルにする前の掃除に使った。日付のセルに文字列が混じっていたのが38行、製品コードの前後に空白が入っていたのが210行。この掃除をしないと、ピボットの行見出しに同じ製品が2つ並ぶ。
後輩には、ピボットを作る側ではなく使う側の操作を教えた。フィールドを行と値に置く、更新する、集計フィールドの式を見る、の3つで、教えたのは20分。教えたあとに後輩が自分で作った工程別のピボットは、値の欄が「不良数の個数」になっていて、合計ではなく行数を数えていた。既定が個数になるのは文字列が混じっている列で起きることで、ここでも掃除の漏れが1列見つかった。
置き換えの途中、6月から7月にかけては旧集計シートとピボットを並走させて、月次の数字が一致することを2回確かめた。6月分は工程別の不良率で小数第2位が1か所違い、原因は旧シートの式が検査数0の行を除外していなかったことだった。つまり旧シートの側が3年間ずれていたことになる。一致はした、と言いきれない並走だったが、並走している間に別の問題が出た。
更新ボタンを2回忘れた
6月22日、後輩が元データに1週間ぶんを追加してから集計を見て、「増えてません」と言った。更新を押していなかった。私が横で更新ボタンを押すと数字が動き、後輩は「これ、毎回押すんですか」と聞いた。
7月13日、月次会議の直前にもう1回。今度は私で、前日に元データを足したあと更新を押さずに保存し、会議の資料に先週までの数字を貼った。会議の途中で後輩が気づいて、その場で直した。
翌7月14日、サポートページで読んだ「ファイルを開くときにデータを更新する」をピボットのオプションでオンにした。4枚のピボットは同じ元データを使っているので、設定は1か所で済んだ。それ以降、8月14日までの1か月は押し忘れが0回。開けば更新されるので、押す必要がない。
ただ、開いたまま元データを足して、そのまま集計を見る場面は残る。この場面では今も更新ボタンが要る。
残した1枚と数字
8月14日に旧集計シート8枚を削除した。ブックは18MBから6MBになり、開くまでの時間は40秒から9秒になった。後輩の月次集計は55分から12分で、減った43分の大半は、式のエラーを探す時間と、集計シートごとにグラフの範囲を直す時間だった。
行の挿入で式が壊れる問題は、8月の1か月で行の挿入が3回あったが、壊れたものは0件。元データがテーブルになったので、行を足しても参照がずれない。
関数を1枚残したことについては、いまも少し気になっている。客先の様式が変わったとき、その1枚だけは私が直さなければならない。全部ピボットにできれば後輩に任せきれるのだが、客先のセル位置は私たちの側では動かせない。関数を捨てたと言いながら、1枚は捨てられなかった。
数式バーに212文字の式が出るセルは、もう無い。
不良率をピボットの「平均」で出すと重みが消えるという勘違いは、私が3つのうちで一番危なかったと思っている。検査データの集計で起きやすい計算の誤りは、ものづくりハンドブック T22 検査データの統計処理でやりがちな誤り5類型にまとめてある。
参考書籍（PR）
関数を減らしてテーブルとピボットで集計する組み立ては、藤井直弥・大山啓介『Excel 最強の教科書』を読んで、自分の作り方が逆だったと分かった。式を重ねる前に元データの形を整えるという順番は、この本で覚えたものだ。
【PR】
|
|
次に読む
検査記録、紙とデータの「どっちが正本か」に決着をつけた日 — 紙の検査表とExcelで不良件数が2件ずれた月末から、正本をExcel台帳に一本化し、探す時間が10分近くから1分もかからなくなった記録。どちらを正本にするかを決めた話が、この集計ブックの土台になっている。
ダッシュボードを入れたのに誰も見なくなった理由 — 18項目のダッシュボードが4週間誰にも開かれず、指標を4項目に絞って朝礼の5分に組み込んだら週9回前後の閲覧に戻った記録。集計の出口を誰が開くかという話で、今回の入口側と対になる。
免責: 本記事は筆者個人の経験に基づく記録です。Excelの機能や画面はバージョンや更新プログラムによって異なります。時間やファイルサイズは自社のブック1つで測った値であり、実際の運用は各社の環境に合わせてご判断ください。
