給与改定って、人事担当者にとっては気が重いイベントの一つですよね。
大企業では人事部が昇給基準に従って機械的に行うケースも多いのですが、ベンチャーでは各事業部長が裁量を持っていて、個別調整が行われたりします。
この時期が来てしまった…。
各事業部長に金額入力を依頼しないと…。
集計も大変そうだな…。
そんな人事担当者の悩みを、実はスプレッドシートの関数で解決することができます。
具体的には、QUERY関数とIMPORTRANGE関数を使用します。
初見の方でも分かるように、ケーススタディで解説していきます。
何だかんだ手作業になるんだろ？
人事情報だから気をつけるんだぞ〜。
…そう思われるかもしれませんが、
- 機密性の担保
- 全体進捗の可視化
- 自動集計
という３点を満たしているので、ご安心下さい。
え、個別の進捗確認が不要なんですか？
集計する時もシートをペタペタ貼らずに済むのは便利かも…。
私はもともと大企業で人事をしており、その時は独自のシステムで自動計算していました。
一方、ベンチャーではルールが頻繁に変わりますし、そもそもシステム開発にそれほどコストを割くことも難しいため、ルールが確立されるまでは柔軟に対応できるスプレッドシートを活用することをおすすめします。
たった２つの関数を組み合わせるだけで、煩雑な作業から解放され、気持ちも軽くなりますので、ぜひ最後までご覧下さい。
給与改定など人事評価プロセスを効率化することで、本当に大切なことに頭と時間を使えるようになるわ。
こちらを参考にしながら、パパッと準備しちゃいましょう！
- 給与改定のオペレーションを自動化できる
- 各事業部用のシート作成
- フィルタ・コピペ不要
- 全体進捗の可視化
- 各事業部への進捗確認不要
- 人事側での自動集計
- 取りまとめ不要
- 各事業部用のシート作成
- 結果的に、一人ひとりの評価をじっくり検討するなど、本当に大切なことに注力できる
- 取りまとめが必要な他のバックオフィス業務にも応用できる
- 異動配置案の共有
- 経費の仮計上
- イベントの出欠
事前準備：人事集計用シートと事業部入力用シートの作成
今回は、AKB48を例に見ていきましょう。（年収は適当な値です）
人事集計用シート
まずはマスタとなる人事側の集計用シートを作成します。
左側（B-E列）の「配布用」には、給与改定対象者の現情報を入力します。
この後、D列の事業部ごとに配布し、E列の現年収をベースに昇給額を検討するので、正しい情報であることを確認しましょう。
右側（G-L列）の「集計用」の欄には、各事業部の改定結果が自動入力されます。
現時点では空欄でOKです。
また、会社のお財布サイズは決まっており、全体のコントロールも人事の役割になるので、K列で確認できるようにします。
- 1行目：原資額
- 給与改定に使えるお財布のサイズ
- 2行目：消化額
- 昇給額の合計
- =sum(K5:K28)
- 昇給額の合計
- 3行目：残額
- 原資額から消化額を引いた残り
- プラスなら余っているので良いが、マイナスなら原資オーバーになるので調整が必要
- 原資額から消化額を引いた残り
事業部入力用シート
次に、各事業部に入力してもらうシートを作成します。
【重要】閲覧・編集権限を個別設定するため、新しくスプレッドシートを立ち上げましょう。
当ケースの前提条件は以下の通りです。
- 会社は、チームA・チームK・チームBの3事業部で構成されている
- 原資の総額は3,000,000円で、各事業部に1,000,000円ずつ割り当てられている
- 各事業部が入力した結果を人事が集計して最終確認する
早速、チームAから作成していきます。
レイアウトは、前述の人事集計用シート左側の「配布用」と全く同じなので、5行目以下を削除したものをコピーするのが早いです。
【重要】B3セルの見出しにはチーム名を入力します。
なお、F1セルの原資額は、割当分（1,000,000）を入力します。
この時点では、まだ他事業部のシートを複製しなくて結構です。
本題：QUERY・IMPORTRANGEによる自動化
準備が整ったので、ここから関数を使った自動化について解説していきます。
マスタから各事業部の対象者だけを抽出する
先ほど作成した事業部入力用シートに、人事集計用シートのマスタから「チームA」だけを表示させます。
ここで登場するのが、QUERY関数とIMPORTRANGE関数です。
QUERY関数の参照範囲が別のスプレッドシートだから、IMPORTRANGE関数を使う必要があるってことですよね？
その通り！
これで情報流出リスクを抑えられるし、もし対象者が変わったりしたらマスタを修正すれば各シートにも自動反映できるわ。
アクセスを許可すると、以下のようになります。
なるほど、人事集計用シート（マスタ）のB-E列を事業部入力用シートのB-E列にインポートしてるんですね。
でも何で「チームA」だけを表示できるんですか？
秘密は数式の最後「where Col3='"&$B$3&"'」にあるわ。
人事集計用シートのD列（事業部）から、事業部入力用シートのB3セル（チームA）と一致するものを絞り込む条件を指定しているの。
参照先としてセルを指定する方法は、こちらの記事をご覧下さい。
以下の文字列をコピーして使用して下さい。（緑文字を置き換え）
=QUERY(IMPORTRANGE("https://●●","シート名!$この列から:$この列まで"),"select* where Col3 = '"&セル番地&"'")
- 数式前半の範囲指定部分にIMPORTRANGE関数をそのまま置き換える
- 参照先が別のスプレッドシートである場合、複数範囲でなくても列は「Col1,Col2,…」のように番号で指定する
- QUERY関数の中でセル番地として認識させるために"（ダブルコーテーション）と&（アンド）で挟み込む
- 貼り付けたいセルにカーソルを合わせて「fx」欄に貼り付け
- キーボード「F2」ボタン（セル編集）を押してから貼り付けでも可
最後に、閲覧・編集権限の設定も忘れずに行いましょう。
シート右上の「共有」から、その事業部の決裁者（事業部長等）に編集権限を設定します。
あわせて、関係者以外に共有されていないことも確認します。
また、更にセキュリティレベルを高める場合は、B5セルの関数を編集できないようにシートを保護することをおすすめします。
以上の手順を、他の事業部についても繰り返し対応します。
テンプレは完成しているので、チームAのスプレッドシートをコピーし、B3セルの事業部名と閲覧・編集権限を変更するだけで完了です。
対象者が漏れなく表示できていることを確認したら、各事業部長にシートを共有して、入力依頼しましょう。
各事業部の改定結果を集計する
今頃、事業部長がメンバーの昇給を検討し始めているかと思います。
その間に、人事集計用シートを完成させましょう。
完成イメージとしては、以下の赤枠内に各事業部のシートがリアルタイム集計されるようになります。
ここでも、QUERY関数とIMPORTRANGE関数を使用します。
但し、集計プロセスになるので、複数のスプレッドシートを参照する点に注意しましょう。
また、空白セルを除外するように「is not null」を条件指定しています。
数式を入力すると、以下のようにエラーが出ます。
２つ以上のシートリンクを同時に指定する場合、アクセス許可の渋滞が発生します。
シートの余白で構いませんので、IMPORTRANGEの部分だけ抜き出して、１つずつリンクを読み込んであげましょう。
すると、エラーが解決して、各事業部のシートを集計することに成功しました。
詳しくは、こちらの記事をご覧下さい。
=QUERY({IMPORTRANGE("https://●●","シート名!$この列から:$この列まで");IMPORTRANGE("https://▲▲","シート名!$この列から:$この列まで");IMPORTRANGE("https://■■","シート名!$この列から:$この列まで")},"select* where Col1 is not null")
- 範囲指定の部分をIMPORTRANGE関数にそのまま置き換える（型は変わらない）
- 範囲全体を { } で囲む
- 縦結合の場合は範囲同士を ; で繋ぐ
参照先リンクの順番で、チームA・K・Bと縦に並んでいるワケですね。
昇給額がまっさらですが、本当に事業部長が入力したら反映するんですか？
確かにそれは不安よね。
試しに、チームAの事業部長に入力してもらいましょうか。
ーーー作業タイムーーー
チームAのシートが、以下の通り入力完了しました。
メンバー全員の昇給額（F列）が埋まっていて、原資もピッタリ消化していることが分かります。
人事集計用シートに戻って確認してみます。
すると、チームAの昇給額が即時反映しており、同時に他事業部は未入力であることが分かります。
リアルタイム反映はすごいですね！
わざわざ各事業部に連絡したり、シートを覗きにいく必要がなくなります。
そうね！後はこの集計用シートだけ見ておけば大丈夫。
原資全体の消化状況も分かるので、オーバーしていたら早めに気付いて修正できるのもメリットね。
続いて、チームKとチームBも入力完了しました。
全事業部の入力が完了し、人事集計用シートにも反映しました。
これって、既に取りまとめまで済んでいることになりますよね？！
いちいちコピペしなくて良いのは嬉しいですね〜。
締切さえ設けておけば、基本的には個別連絡や取りまとめも不要になるわ。
コピーすれば次の給与改定でも使い回せるので、もう仕組みは整ったわね！
今回は、QUERY関数とIMPORTRANGE関数を使用した集計作業の自動化について解説しました。
人事などのバックオフィスで働いていると、機密度の高い情報を取りまとめるシーンが多いと思います。
その度にスプレッドシートを切り貼りするのは大変ですし、ミスの元にもなりますので、是非こちらの方法を活用してみて下さい。
