前回は、OFFSET関数を使い、諸費月別一覧から区分により使用する表を変化させるため、OFFSET関数の引数(①基準セル,②行数,③列数,高さ,幅)「①基準セル」の部分に登録した「名前」を使用する・・・・・という作業をしました。
今回は、諸費を管理する生徒リストを作成し、実際にOFFSET関数を設定してみたいと思います。
こんな感じで作成してみました。
一番左から生徒番号、氏名と続き、4月から来年3月までの入金実績まで作成します。
シートは学年ごとやクラスごとなどいろんな分け方が想定されます。
注目点は「区分名」フィールドと各諸費の上部の番号です。
「区分名」は、生徒の家庭環境により、使用する諸費一覧を変化させるために使用します。
各諸費の上部の番号ですが、これは、OFFSET関数の引数(①基準セル,②行数,③列数,高さ,幅)の「②行数」の部分に使います。
諸費の一覧を見れば分かりますが、例えば「学級費」はどの表の基準セルからも1行目なので番号が「1」となっています。
これで、いちいち「②行数」部分を数値で指定するのを回避することができます。
ということは、「③列数」も「②行数」と同じ扱いにしたいですよね。
そのために、別シートに処理月を入力するところを作ります。
一応シート名を「メイン」としました。（なんでもいいです。）
このように処理する月を入力する部分と、その月の「月コード」の一覧を作成しました。
月コードの一覧はその月が諸費一覧の何列目にあるかを表したコードです。
「月」を入力するとVKOOKUP関数で隣の一覧から「月コード」を表示するように作成しました。この月コードをOFFSET関数の「③列数」部分に使用することで、数値で指定する手間を回避します。
例えば、4月は各諸費一覧も基準セルから1列目ですね。したがって「月コード」は「1」としています。
リストの「区分名」には区分を簡単に入力できるよう「入力規則」でリストを作成しておきましょう。
こんな感じで入力していきます。まずはすべて「基準」でコピーして、あとでそれ以外を修正するというのが、早いかもしれません。
それでは、一人目の「学級費」の部分にOFFSET関数を設定しましょう。
「=OFFSET(」まで入力します。
一つめの引数は、「①基準セル」です。ここで使うのが、前回登録した「名前」なんです。
「名前の登録」では、各諸費一覧の最初のセル、つまり、OFFSET関数の「①基準セル」となるセル番地を名前を付けて登録し、さらに使い方も解説しました。
その「名前」と「区分名」で入力した値は同じですので（というより、絶対同じにする！）、「区分名」のセルに入力された値が「①基準セル」となれば正しいのですが・・・・・・。
ここで問題なのが、区分が入力されたセルをただ単純にクリックするだけでは、エラーになってしまいます。
どういうことかというと、この「①基準セル」部分に関しては、そのクリックしたセル番地が「基準セル」になってしまうのです。
この問題を回避するには、引数「①基準セル」部分用に登録した名前、「基準」とか「準用1」とかを直接入力するしかありません。・・・・・・でもそれだと、区分を一生懸命入力した意味がないですね。
そこで、指定したセルの入力された値を直接取得してくれるINDIRECT関数を使います。
使い方は簡単で、引数に値を取得したいセルを指定すればいいだけです。
このように入力します。（コピーすることを考えて列番号のみ固定の複合参照にしましょう。）
=OFFSET(INDIRECT($C4)
これで、「区分名」に入力された「基準」という値が直接指定されたことと同じになります。
次は、「②行数」です。これは、各諸費の上部の番号をクリックすればOKです。（コピーすることを考えて、行番号のみ固定の複合参照にしましょう。）
=OFFSET(INDIRECT($C4),D$2
最後に「③列数」です。これは処理月を入力するために作成した「メイン」シートの「月コード」の部分をクリックします。（絶対参照にしましょう。）
=OFFSET(INDIRECT($C4),D$2,メイン!$D$2)
区分セル「C4」に入力された値を基準セルとし、セル「D2」の値を行数、「メイン」シートの「D2」の値を列数とした位置のセルの値を取得するという意味になります。（くどいですね）
図の生徒で解説すると＝OFFSET（基準（諸費一覧シートのセル「A1」）,1,1)となってるはずです。
出来たらEnterを押します。
諸費一覧の基準セル「基準」（諸費一覧の「A1」）から「学級費」（1行目）で4月（1列目）の金額が表示されました。
他の諸費にもコピーしましょう。
正しく設定されました。※「集金額」にはSUM関数で合計を設定しておきましょう。
ちょっとテスト。
「メイン」シートの処理月を「5」（5月）に変更してみます。
「月コード」は「2」に変更されたので、諸費一覧の2列目「5月」にの金額に変更になりました。
「メイン」シートを4月に戻して・・・・・。
今度は、「区分」を「準用1」に変更してみます。
ちゃんと、「準用1」の表を見にいってますね。
どうやら正しく数式が入力されました。
あとは、生徒全員にこの数式をコピーします。
基準セル部分の登録（名前の登録）、各諸費の行番号、月の列番号（月コード）により、区分を修正するだけで、使用する表を自由に変更できます。
区分で使用する表を変幻自在に操ることができる・・・・・・。いろんなことに応用できそうですね。
今回も長くなってしまいました。最後まで読んでくれてありがとうございます。
次回は、実際の管理方法を考えていきます。
Excelで学校諸費管理③～月ごとの管理・・・・・・へつづく
0 件のコメント:
コメントを投稿