![](https://1.bp.blogspot.com/-T-awiPei_Pw/WiSwi2-c0bI/AAAAAAACSdc/LGX8q3LkDkEFtshuDywBxo3uyufOHHgoACLcBGAs/s1600/vol25.png)
┃台帳管理にも使える!シートを自在に操る技②
前回のINDIRECT関数を試してみていただけたでしょうか?
実際にやってみれば「あぁ、なるほど」と思っていただけた方も多いと思います。
INDIRECT関数を使って、個人別の勤務実績表や退職金等の台帳をまとめることもできます。
【問題7】難易度★★
社員番号がシート名になっている個人別勤務実績シートがあります。
各社員の氏名と勤務時間合計を転記した、「勤務実績集計」シートを作成してください。
社員のシートは大量にあることを前提として考えてください。
![](https://2.bp.blogspot.com/-e-zQLrv8o8U/WiSw6IpCYfI/AAAAAAACSdg/ssQjrm66peg0gTewjawktVvZX7wrYe03ACLcBGAs/s0/25-1.png)
【問題7 解答】
それでは解説します。
各シートの同じセルを読み込めばいいのですから、読み込みたい項目ごとに番地を書いておきましょう。
氏名はB2セルにありますし、勤務時間合計はB3セルにあります。
「!」も付けておくとあとの数式がシンプルになることがあります。
![](https://3.bp.blogspot.com/-IHM1zr83IVM/WiSw6I8apsI/AAAAAAACSdk/NxiWs2hUZzI9GUt-Ry5b2fZCfIYFgiYGgCEwYBhgL/s1600/25-2.png)
社員番号とセル番地を結合して、INDIRECT関数で参照しましょう。
絶対参照に気を付けて数式を完成させれば、たったひとつの数式をコピペするだけで表を完成させることができます。
=INDIRECT(TEXT($A3,"0000")&B$1)
![](https://2.bp.blogspot.com/--4gQh8ukZ1Y/WiSw6Io4b6I/AAAAAAACSdo/cvKSvw0MBPAZLrcqCPO2fm7CCfDCh9EOACEwYBhgL/s1600/25-3.png)
今回は集計シートの社員番号を数字で入力して、TEXT関数で文字列に変換していますが、社員番号を最初から文字列で入力しておけばTEXT関数も必要ありません。
![](https://1.bp.blogspot.com/-T-awiPei_Pw/WiSwi2-c0bI/AAAAAAACSdc/LGX8q3LkDkEFtshuDywBxo3uyufOHHgoACLcBGAs/s1600/vol25.png)
┃台帳管理にも使える!シートを自在に操る技②
前回のINDIRECT関数を試してみていただけたでしょうか?
実際にやってみれば「あぁ、なるほど」と思っていただけた方も多いと思います。
INDIRECT関数を使って、個人別の勤務実績表や退職金等の台帳をまとめることもできます。
【問題7】難易度★★
社員番号がシート名になっている個人別勤務実績シートがあります。
各社員の氏名と勤務時間合計を転記した、「勤務実績集計」シートを作成してください。
社員のシートは大量にあることを前提として考えてください。
![](https://2.bp.blogspot.com/-e-zQLrv8o8U/WiSw6IpCYfI/AAAAAAACSdg/ssQjrm66peg0gTewjawktVvZX7wrYe03ACLcBGAs/s0/25-1.png)
【問題7 解答】
それでは解説します。
各シートの同じセルを読み込めばいいのですから、読み込みたい項目ごとに番地を書いておきましょう。
氏名はB2セルにありますし、勤務時間合計はB3セルにあります。
「!」も付けておくとあとの数式がシンプルになることがあります。
![](https://3.bp.blogspot.com/-IHM1zr83IVM/WiSw6I8apsI/AAAAAAACSdk/NxiWs2hUZzI9GUt-Ry5b2fZCfIYFgiYGgCEwYBhgL/s1600/25-2.png)
社員番号とセル番地を結合して、INDIRECT関数で参照しましょう。
絶対参照に気を付けて数式を完成させれば、たったひとつの数式をコピペするだけで表を完成させることができます。
=INDIRECT(TEXT($A3,"0000")&B$1)
![](https://2.bp.blogspot.com/--4gQh8ukZ1Y/WiSw6Io4b6I/AAAAAAACSdo/cvKSvw0MBPAZLrcqCPO2fm7CCfDCh9EOACEwYBhgL/s1600/25-3.png)
今回は集計シートの社員番号を数字で入力して、TEXT関数で文字列に変換していますが、社員番号を最初から文字列で入力しておけばTEXT関数も必要ありません。
![](https://1.bp.blogspot.com/-AtM7VIfkVTw/WhINvaOVpeI/AAAAAAACRmw/LDQ5VTEfHfQGDsba-mHTZ-MkpScq24s4QCLcBGAs/s1600/jitanbanner.png)
![](https://2.bp.blogspot.com/-4j4bvhag8zY/Wf-kh1saESI/AAAAAAACROs/ujwln4xXmlUYUGovqDWkuO0_faWE8IbIQCLcBGAs/s0/vol23.png)
【繰り返しを操る技(実践編)】
前回ご紹介した「繰り返しを操る技」が、早速現場で役に立ちました!
膨大な量のコピペ作業を一瞬で終わらせることができました。
しかもほぼメンテナンス不要です。
![](https://4.bp.blogspot.com/-RMu2qSl-W24/WfFKnrECMlI/AAAAAAACQqI/yJwmD1EiZQIiccHW-yH6LurUrzD1pjkKQCLcBGAs/s0/vol22.png)
【繰り返しを操る技】
集計開始日と集計終了日のデータを、1行目の数式をコピペするだけで完成させる問題の解答例です。
![](https://3.bp.blogspot.com/-i_-t-RbazOw/Wdv4gtBjVKI/AAAAAAACQO8/4-ojt7_BbnkmhFG9spvEG77hIFtuPkIbQCLcBGAs/s0/vol21.png)
【力技から脱却する技】
問題5(関数編)の回答例です。
=SUMIFS($C:$C,$A:$A,"<="&$E2,$B:$B,F$1)-SUM(F$1:F1)
![](https://1.bp.blogspot.com/-25OsR7ImGJU/WcoO_QXQiAI/AAAAAAACPzE/gLq1ELY3n5QlIVWu29j4LMCqSUex-vjngCLcBGAs/s0/v20.png)
【ピボットテーブルから脱却する技】
ピボットテーブルはとても便利な機能なので、仕事でExcelを使う人であれば必ず使えるようにしなくてはなりません。
![](https://2.bp.blogspot.com/-q613e6IgcaY/WGMqZpaMvYI/AAAAAAACHTk/85A4uttXZEsXzlKzqUMA0WZZ2ojOIvhqQCLcB/s0/fbfor.png)
いろいろな方法が考えられますが、今回は「フラグ」を立ててからピボットテーブルで集計してみてください。
セルを贅沢に使って分かりやすく作成してみてください。
![](https://2.bp.blogspot.com/-q613e6IgcaY/WGMqZpaMvYI/AAAAAAACHTk/85A4uttXZEsXzlKzqUMA0WZZ2ojOIvhqQCLcB/s0/fbfor.png)
【日付を自在に操る技】
前回の決算日を関数で求める問題の解答プロセスを解説します。
![](https://2.bp.blogspot.com/-q613e6IgcaY/WGMqZpaMvYI/AAAAAAACHTk/85A4uttXZEsXzlKzqUMA0WZZ2ojOIvhqQCLcB/s0/fbfor.png)
現場でExcelにおける日付の基本を理解していない人が多いと感じることが多いので、日付の問題です。
日付操作は仕事でExcelを使う人にとっては必須の知識です。
![](https://2.bp.blogspot.com/-q613e6IgcaY/WGMqZpaMvYI/AAAAAAACHTk/85A4uttXZEsXzlKzqUMA0WZZ2ojOIvhqQCLcB/s0/fbfor.png)
【SUMIF関数を使って参照する技】
VLOOKUP関数はユニークなキーを使ってセルを参照する関数です。
SUMIF関数は検索条件に合致する数字を合計する関数です。
両者の役割は異なるのですが、「ユニークなキー」を使って「数値を参照」したい時はどちらを使っても同じことができます。
キーがユニークであれば、合計しても数値は変わらないからです。
![](https://2.bp.blogspot.com/-q613e6IgcaY/WGMqZpaMvYI/AAAAAAACHTk/85A4uttXZEsXzlKzqUMA0WZZ2ojOIvhqQCLcB/s0/fbfor.png)
【VLOOKUP関数を使って思いどおりの参照をする技】
「ユニークなキーを利用して特定のセルを参照する」といったらまず思い浮かべていただきたいのがVLOOKUP関数です。
今回のケースもまずはVLOOKUP関数を使ってみましょう。
![](https://2.bp.blogspot.com/-q613e6IgcaY/WGMqZpaMvYI/AAAAAAACHTk/85A4uttXZEsXzlKzqUMA0WZZ2ojOIvhqQCLcB/s0/fbfor.png)
【フィルタを使わずに関数で抽出する技】
連番を使って「あるべきリスト」を作成することを思い付けばあとは簡単です。
調べたい数字の入った列の中から「あるべきリスト」の数字をひとつひとつチェックして、ゼロかどうかを確認すれば完成です。
開催日時・会場
2017年05月17日(水曜日) 09:30~11:30
企業研究会セミナールーム(東京・麹町)
企業研究会セミナールーム(東京・麹町)
受講対象
仕事でExcelの基本的な関数を使用したことがある方で、より知識を深めたい方
講 師
株式会社シンプルソリューションズ 公認会計士 一木 伸夫氏