【関数不要で誰でも簡単】歩掛の集計をExcelのピボットテーブルで効率化する方法

原価管理において重要な歩掛。1つの作業にどれくらいの時間が必要かを数値化したものです。原価管理の第一歩として、実際の工程でかかった人工の集計を若手現場監督に任されることがあります。毎日の歩掛を記録したものの、工種や施工業者ごとに集計するのが大変で困っている方も多いはず。

私自身、新卒でゼネコンに入社して1年半くらい経った時に、先輩から歩掛の記録を任されました。作業所で従来行っていた歩掛の記録方法は集計に向いておらず、施工業者から請求があがる度に1ヶ月分の記録をチェック。もっと効率よく集計できないかと模索した結果、今ではExcelのピボットテーブルを用いてあっという間に歩掛をまとめることができるようになりました。

そこで本記事では、若手現場監督が歩掛の集計で苦労しないように、Excelのピボットテーブルで効率よくまとめる方法を分かりやすく解説します。この記事を読めば、「誰でも簡単に歩掛の集計を効率化すること」ができます。

私が何度も試行錯誤することで手に入れたノウハウをまとめました。原価管理業務の生産性を向上させたい方は、ぜひ最後まで読んでください。

Excelのピボットテーブルを用いた歩掛の集計方法

Excelで歩掛の記録と集計を行う手順は、以下の通りです。

日付を入力

まず初めに、施工を行った日付を入力します。ピボットテーブルで集計を行う際に、月毎でフィルターをかけるために必要な情報だからです。日付を入力しておけば、任意の月の歩掛をワンクリックで集計することができます。

施工業者を入力

次に施工業者を入力してください。日付と同様、施工業者毎にフィルターを掛けてピボットテーブルで集計したいからです。あらかじめ施工業者を記録しておけば、簡単に各業者の歩掛を集計することができます。

工種・種別・細別を入力

工種・種別・細別の入力を行います。設計内訳書の各項目に合わせて、それぞれ記録しましょう。

施工詳細を入力

細別まで記録したら、施工詳細を入力しましょう。設計内訳書の細別だけでは、細かい作業まで区別することはできないからです。例えば細別が支保工基礎の場合、鋼矢板の敷設と撤去が実際の作業として挙げられます。同じ支保工基礎工でも、鋼矢板の敷設と撤去で歩掛は変わります。より精度の高い歩掛を記録するために、施工詳細も入力するのがおすすめです。

歩掛を入力

施工詳細に入力した作業に対しての、歩掛を入力しましょう。本記事で紹介しているシートでは、歩掛の記録を以下の9種類に分類しています。

各分類ごとの歩掛を計算して、それぞれ入力してください。例えば、5人の作業員さんが1日かけて作業した場合は、職人の列に5を入力します。1人の職員が半日かけた作業の場合は、0.5を入力。重機の場合も同様です。

記録の繰り返し

以上の手順を繰り返して、毎日の歩掛を記録します。歩掛を記録することで、ピボットテーブル用の表が完成です。

ピボットテーブル用の表を準備する

ピボットテーブルを使用するには、事前に「ピボットテーブル用の表」を準備する必要があります。具体的には次のような表を作成します。

• 表の1行目(行見出し)に、必ず項目名を入れる
• 数値や日付値は、Excelが正しく読み込めるように整える

Excel最強の教科書[完全版]　藤井直弥 / 大山啓介

テーブルの作成

作成した表の中で任意のセルを選択した状態で、[Ctrl]＋[T]キーを押してください。写真のように、テーブルの作成というウィンドウが表示されます。作成した表全体が指定されていることと「先頭行をテーブルの見出しとして使用する」にチェックが入っていることを確認して、OKをクリック。作成した表をテーブルに変換することができます。テーブル化は、以下のメリットがあります。

データの増減に自動対応できる

歩掛の記録は日々追加されるので、テーブル化のメリットは非常に大きいです。表作成の時点では入力していなかった歩掛記録をテーブルに追加しても、自動的にピボットテーブルのデータ参照範囲を拡張してくれます。

テーブルを作成したら、テーブル名を変更しましょう。ここでは「bugakari」という名前を付与しています。ちなみに英語の名前に変える方がメリットが大きい模様です。

構造化参照を使って数式入力する

(前略)

テーブル名は自動的に付与されますが、英語の名前に変えるのが原則です。こうしておけば、数式の入力時にオートコンプリート(自動入力の)候補にあがるなどのメリットが生まれます。

できるYOUTUBER式 EXCEL現場の教科書 長内孝平

ピボットテーブルの作成

毎日の歩掛を記録したら、ピボットテーブルを実行します。記録した表内の任意のセルを選択した状態で、[挿入]タブの[ピボットテーブル]をクリックしましょう。

[ピボットテーブルの作成]ダイアログが表示されるので、先ほど名前をつけた「bugakari」がセル範囲に選ばれていることを確認してください。配置場所は「新規ワークシート」を選んでOKをクリックします。

集計表の作成

新規ワークシートが作成され、画面右側に「ピボットテーブルのフィールド」が表示されます。サイドバーでフィールドをドラッグして集計表を作成します。写真の例では、以下のようにドラッグしました。

ボックス	フィールド
フィルター	施工業者・月
列	値(ドラッグ不要)
行	工種・種別・細別・詳細
値	職員・職人・4.9tCC・13tRC・25tRC・50tRC・75tRC・100tRC・4.9m3BH

上記の振り分けによって、各作業の歩掛を集計することができます。

ドラッグするだけで簡単に集計できる

ピボットテーブルを使えば、項目名をドラッグ&ドロップするだけで集計表を作ることができます。数式や関数を入力する必要はありません。

できるYOUTUBER式 EXCEL現場の教科書 長内孝平

列のみ集計をクリック

「デザイン」の「総計」タブを表示して、「列のみ集計を行う」をクリックします。必須の作業ではありませんが、歩掛の総計が最下行に表示されます。月毎の全人工を数えたい場合などに使用してください。

施工業者を選択

ピボットテーブルのB2セルで施工業者を選択することができます。該当する施工業者のみの歩掛集計が可能です。

集計する月を選択

施工業者と同様に、B3セルで集計する月を選択することも可能です。該当する月の歩掛を集計します。施工業者のフィルターと月のフィルターは併用できます。例えば、各施工業者から提出された常傭工事の請求内容を確認したい時などに使用してください。

記録が増えたら更新をクリック

歩掛記録が増えたら、「ピボットテーブル分析」の「更新」をクリックしてください。ピボットテーブルのデータが更新されます。毎日増え続ける歩掛記録も、ワンクリックで集計し直すことが可能です。

歩掛をピボットテーブルで集計するメリット

歩掛をExcelのピボットテーブルで集計するメリットとして、以下の3つが挙げられます。

記録を追加してもワンクリックで更新

ピボットテーブル機能を使えば、歩掛の記録が増えてもワンクリックで更新できます。記録用シートをテーブル化することで、参照範囲を自動的に拡張してくれるからです。あなたも一度はこんな経験をしていませんか？

膨大な量の歩掛記録をまとめて集計するのが大変…

基本的に毎日追加される歩掛記録。日々の業務に追われて面倒な集計は後回しにしてしまいがちですよね。しかし、ピボットテーブルを活用すれば、溜まった記録もワンクリックで集計できます。

施工業者や月ごとの集計が簡単

膨大な量の歩掛記録とにらめっこしながら、施工業者や月ごとのデータをまとめる必要はありません。ピボットテーブルのフィルター機能を利用すれば、一瞬でデータを抽出して集計することができます。

原価管理において重要な歩掛ですが、工程管理において必須の業務ではないのでなるべく早く終わらせたい。そんな思いからピボットテーブルの活用を思いつきました。ピボットテーブルを使えば、面倒くさい集計作業の生産性を大幅に上げることが可能です。

関数を使わずに階層構造の集計可能

本記事で紹介しているピボットテーブル機能では、関数を使用していません。Excelの関数に苦手意識のある方でも、悩むことなく階層構造の集計が可能です。歩掛を階層構造で集計すると、以下のメリットがあります。

設計内訳書の構造を反映した集計が可能になる

例えば「工種」「種別」「細別」という階層構造を設定することで、設計内訳書と同じ構造の歩掛を簡単に集計することができます。

歩掛を記録するExcelシートの作成手順

本記事で紹介している歩掛記録用のExcelシートは、以下のような仕組みになっています。

プルダウンから工種・種別・細別を選択することによって、入力ミスを防ぐことができます。そして選択肢は、設計内訳書に対応。種別は選んだ工種に該当するものを、細別は選んだ種別に該当するものがそれぞれプルダウン表示されます。

入力不要の「選択リスト」でさらに効率化

(前略)

商品名など選択肢が限られているものは、リストを作成しましょう。[データの入力規則]ダイアログボックスからリストを設定します。引数[検索値]の候補をドロップダウンリストから選べるようにすることで、入力ミスを減らす仕組みが作れます。

できるYOUTUBER式 EXCEL現場の教科書 長内孝平

また、歩掛の単位はセル内に直接入力せずに書式によって表示させています。ピボットテーブルに使用するデータは、数値でないと正しく集計できないからです。

これらの仕組みは、下記の手順で設定できます。

工種・種別・細別の表作成

あなたの現場の設計内訳書をもとに、工種・種別・細別の表を作成しましょう。写真の例では、場所打固定支保工(箱桁橋)の設計内訳書をベースにしています。名前のダブりがあると、ピボットテーブルで正しく集計できません。種別は異なるけど細別の名称が同じ場合などは、細別の列に入力する名前を少し変えるなどの対応が必要です。

テーブル化

先ほど作成した表の中で任意のセルを選択した状態で、[Ctrl]＋[T]キーを押してください。データ範囲の確認と「先頭行をテーブルの見出しとして使用する」をチェックして、OKをクリックしましょう。

テーブル名は任意ですが、写真の例では「sekkeiutiwakesyo」と入力しています。

施工業者の一覧作成

施工業者の一覧を空いているセルに作成します。

工種・種別の抽出

空いているセルのどこでも構わないので、以下の式を入力してください。B列から重複を取り除いた工種がG列に抽出されます。

=UNIQUE(sekkeiutiwakesyo[工種])

種別でも工種と同じ作業を行いましょう。D列から重複を取り除いた種別がH列に抽出されます。

名前の定義

選択肢から資材を選べるように、名前の定義を行います。リボン「数式」の「定義された名前」機能を使って任意のセルやセル範囲に名前をつけましょう。

選択範囲から名前を作成

まず、施工業者の一覧が記入されたセル範囲に名前をつけます。写真のように、施工業者のセル範囲を指定した状態で「選択範囲から作成」をクリックしてください。ウィンドウが表示されるので、「上端行」にチェックしてOKをクリック。これでセル範囲F3:F5に「施工業者」という名前が定義されます。

工種を抽出した列も、施工業者の列と同様の作業を行ってください。写真の例では、セル範囲G3:G4に「工種」という名前が定義されます。

名前の定義

次に、種別が入力されたセル範囲の名前を定義します。工種の列の任意のセルを選択した状態で「名前の定義」をクリックしてください。ウィンドウが表示されるので、名前の欄に選んだ工種名が入力されていることを確認。参照範囲の欄は、工種に該当する種別のセル範囲を指定してOKをクリックします。

細別が入力されているセル範囲も、種別の時と同様に名前を定義しましょう。写真の例では、以下のように名前の定義を行いました。

名前	参照範囲
架設支保工	$D$3:$D$5　(支保工基礎/不陸整正/支保工)

データの入力規則設定

歩掛の記録用シートにて、「データの入力規則」を設定します。施工業者、工種、種別、細別それぞれで入力不要の「選択リスト」を作成します。

入力不要の「選択リスト」でさらに効率化

(前略)

商品名など選択肢が限られているものは、リストを作成しましょう。[データの入力規則]ダイアログボックスからリストを設定します。引数[検索値]の候補をドロップダウンリストから選べるようにすることで、入力ミスを減らす仕組みが作れます。

できるYOUTUBER式 EXCEL現場の教科書 長内孝平

施工業者の設定

まず初めに、施工業者の設定を行います。施工業者列の先頭行を選択した状態で、「データの入力規則」をクリックしてください。

「入力値の種類」欄は「リスト」を選択。「元の値」欄はリボン「数式」の「数式で使用」から「施工業者」を選んでOKをクリックしましょう。

工種の設定

施工業者と同様、工種の列も「データの入力規則」をクリックします。ウィンドウが表示されたら、「数式で使用」から「工種」を選んでください。「工種」に該当する文字列が選択肢として表示されます。

種別の設定

次に、種別の設定を行います。種別列の先頭行を選択した状態で、「データの入力規則」をクリック。「入力値の種類」欄は先ほどと同様に「リスト」を選びます。そして「元の値」欄は以下の数式を入力してください。

=INDIRECT(D3)

数式を入力後にOKをクリックすると、D3で選んだ工種に該当する種別がE3の「選択リスト」に表示されるようになります。

INDIRECT関数

指定される文字列への参照を返します。 セル参照はすぐに計算され、結果としてセルの内容が表示されます。 INDIRECT 関数を使うと、数式自体を変更しないで、数式内で使用しているセル参照を変更することができます。

Microsoftサポート　INDIRECT関数

細別の設定

細別の設定も種別と同様の手順です。「元の値」欄は以下の数式を入力してください。

=INDIRECT(E3)

E3で選んだ種別に該当する細別がF3の「選択リスト」に表示されます。

セルの書式設定

最後にセルの書式設定を行います。歩掛の数字を入力するセルを選択した状態で、[Ctrl]＋[1]キーを押してください。「セルの書式設定」ウィンドウが表示されます。ユーザー定義で「種類」欄に「0.0″人”」と入力しましょう。すると、入力した数字が少数第一位まで表示され、末尾に「人」の文字が自動的に表示されます。

書式設定の表示形式を使って表記を整える

セルの書式設定を行う際に、表示形式の[分類]に[ユーザー定義]を設定すると、さまざまな表記方法を指定できます。例えば、[セルの書式設定]ダイアログの[種類]欄に[@”様”]と入力すると、入力した文字列の末尾に「様」の文字が自動的に表示されます。

(中略)

このように、書式設定の表示形式をマスターすると、入力の手間を大幅に省略でき、かつ、入力ミスを大幅に減らすことができます。

Excel最強の教科書[完全版]　藤井直弥 / 大山啓介

コピー&ペースト

全ての列でセルの設定を終えたら、最上行をコピーして下の行にペーストしましょう。Excelには色々なコピペがありますが、ここでは単純なコピペで問題ありません。

コピペを制すると作業効率が劇的に向上する

(前略)

思い通りに効率よく作業を進めるためには、Excelのコピー&ペースト機能を理解しておくことが大切です。Excelには「セルのさまざまな設定のうち、何をペーストする(貼り付ける)のか」を指定できる機能が用意されています。

Excel最強の教科書[完全版]　藤井直弥 / 大山啓介

Excelの機能を活用して集計作業を効率化しよう

この記事では、歩掛の集計をExcelのピボットテーブルで効率化する方法を解説しました。ピボットテーブルを利用すれば、以下の手順で簡単に歩掛を集計することができます。

ピボットテーブルを利用するメリットは以下の通りです。

以下の手順で歩掛記録の入力ミスを防ぐ仕組みを作ることができます。

本記事で紹介した仕組みを作るのは、確かに若干面倒くさいです。しかし一度作ってからは、歩掛の集計作業がミスなくワンクリックで終了。頑張って作成して良かったと心の底から思います。

建設業界では、深刻な人材不足と働き方改革により、生産性の向上が求められています。この記事によって、少しでも日々の業務効率が向上すれば嬉しいです。一緒に業務のICT化をどんどん進めて建設業界を盛り上げていきましょう！