┃シートの移動もクリックで！シートを自在に操る技③

INDIRECT関数は便利なのですが、シートがたくさんあるとシートの移動が大変です。
そんな時はHYPERLINK関数と組み合わせて使いましょう。

┃台帳管理にも使える！シートを自在に操る技②

前回のINDIRECT関数を試してみていただけたでしょうか？
実際にやってみれば「あぁ、なるほど」と思っていただけた方も多いと思います。

INDIRECT関数を使って、個人別の勤務実績表や退職金等の台帳をまとめることもできます。

【問題７】難易度★★
社員番号がシート名になっている個人別勤務実績シートがあります。
各社員の氏名と勤務時間合計を転記した、「勤務実績集計」シートを作成してください。
社員のシートは大量にあることを前提として考えてください。




【問題７ 解答】
それでは解説します。

各シートの同じセルを読み込めばいいのですから、読み込みたい項目ごとに番地を書いておきましょう。
氏名はB2セルにありますし、勤務時間合計はB3セルにあります。
「!」も付けておくとあとの数式がシンプルになることがあります。




社員番号とセル番地を結合して、INDIRECT関数で参照しましょう。
絶対参照に気を付けて数式を完成させれば、たったひとつの数式をコピペするだけで表を完成させることができます。

=INDIRECT(TEXT($A3,"0000")&B$1)




今回は集計シートの社員番号を数字で入力して、TEXT関数で文字列に変換していますが、社員番号を最初から文字列で入力しておけばTEXT関数も必要ありません。

┃台帳管理にも使える！シートを自在に操る技②

前回のINDIRECT関数を試してみていただけたでしょうか？
実際にやってみれば「あぁ、なるほど」と思っていただけた方も多いと思います。

INDIRECT関数を使って、個人別の勤務実績表や退職金等の台帳をまとめることもできます。

【問題７】難易度★★
社員番号がシート名になっている個人別勤務実績シートがあります。
各社員の氏名と勤務時間合計を転記した、「勤務実績集計」シートを作成してください。
社員のシートは大量にあることを前提として考えてください。




【問題７ 解答】
それでは解説します。

各シートの同じセルを読み込めばいいのですから、読み込みたい項目ごとに番地を書いておきましょう。
氏名はB2セルにありますし、勤務時間合計はB3セルにあります。
「!」も付けておくとあとの数式がシンプルになることがあります。




社員番号とセル番地を結合して、INDIRECT関数で参照しましょう。
絶対参照に気を付けて数式を完成させれば、たったひとつの数式をコピペするだけで表を完成させることができます。

=INDIRECT(TEXT($A3,"0000")&B$1)




今回は集計シートの社員番号を数字で入力して、TEXT関数で文字列に変換していますが、社員番号を最初から文字列で入力しておけばTEXT関数も必要ありません。

最初に新刊発行のお知らせです！

なんと！

伝統ある日経文庫より、

「ビジュアル 7つの基本で身につく エクセル時短術」



を上梓することができました！

【繰り返しを操る技(実践編)】

前回ご紹介した「繰り返しを操る技」が、早速現場で役に立ちました！
膨大な量のコピペ作業を一瞬で終わらせることができました。
しかもほぼメンテナンス不要です。

【繰り返しを操る技】
集計開始日と集計終了日のデータを、1行目の数式をコピペするだけで完成させる問題の解答例です。

【力技から脱却する技】

問題５(関数編)の回答例です。
=SUMIFS($C:$C,$A:$A,"<="&$E2,$B:$B,F$1)-SUM(F$1:F1)

【ピボットテーブルから脱却する技】

ピボットテーブルはとても便利な機能なので、仕事でExcelを使う人であれば必ず使えるようにしなくてはなりません。

カード利用明細を集計する【問題５】の解説です。

Excelを使った集計は漠然と考えていてもなかなか良いアイデアが浮かびません。
アウトプットを明確にすると、必要な情報が分かります。

前問に引き続き日付の問題です。
いろいろな方法が考えられますが、今回は「フラグ」を立ててからピボットテーブルで集計してみてください。

セルを贅沢に使って分かりやすく作成してみてください。

【日付を自在に操る技】
前回の決算日を関数で求める問題の解答プロセスを解説します。

【日付を自在に操る技】

現場でExcelにおける日付の基本を理解していない人が多いと感じることが多いので、日付の問題です。
日付操作は仕事でExcelを使う人にとっては必須の知識です。

【SUMIF関数を使って参照する技】
VLOOKUP関数はユニークなキーを使ってセルを参照する関数です。
SUMIF関数は検索条件に合致する数字を合計する関数です。
両者の役割は異なるのですが、「ユニークなキー」を使って「数値を参照」したい時はどちらを使っても同じことができます。
キーがユニークであれば、合計しても数値は変わらないからです。

【VLOOKUP関数を使って思いどおりの参照をする技】
「ユニークなキーを利用して特定のセルを参照する」といったらまず思い浮かべていただきたいのがVLOOKUP関数です。

今回のケースもまずはVLOOKUP関数を使ってみましょう。

【ユニークなキーを割り当てる技】
フラグを立てたデータだけを別リストで参照したいような場合、ユニークなキーを割り当てるとコントロールできます。

【フィルタを使わずに関数で抽出する技】
連番を使って「あるべきリスト」を作成することを思い付けばあとは簡単です。
調べたい数字の入った列の中から「あるべきリスト」の数字をひとつひとつチェックして、ゼロかどうかを確認すれば完成です。

開催日時・会場

受講対象

講 師

あけましておめでとうございます。
今年もお役立ち情報をお届けしていきます。
「スゴ技Excel」をよろしくお願いします。