京都ビジネス学院 舞鶴校

schoolpc.exblog.jp

能力評価・キャリア形成

Excelワザ_複数データの取り出し

かなり、苦戦したので、備忘録
生徒さんからの質問で…
「日々入力するデータを1月ごとに、
事業所別に、該当するデータを集計したい!
ダイレクトに集計表に表示したい!」
マクロを使わずに…
フィルタも使わずに…
ピボットテーブルも使わずに…

簡単ではなかった…(^_^;)
ネットでググると、
同じような事をしたい人がかなりいて、
解答事例もたくさん有り。
その中に「imogasi方式」なるものを発見。
imogasi氏曰く
>「もう回答は出尽くしていますが、
いつも気になることを書きます。
この種の質問は週に3度ぐらい出ます。
タイプは「条件付き抜き出し問題」です。
しかし関数でやるのは、
1セル当たりでも非常に長い式で、
関数の5式以上の組み合わせになる式になります。
エクセルには検索に適した関数が作られてないことが原因です。
初心者には、自分で生み出せる式でなく、
式の意味もわからないと思う。
コピペして出来ましたと、それで良い人は使ったら良い。
しかし質問者の場合への修正も一苦労。
解法のタイプは
(1)A 作業列を使う
   B 使わない
(2)A 配列数式を使う
   B 使わない(通常の式)
(3)A ユーザー定義関数を使うー(一種のVBA)
   B 使わない。
(4)フィルタ
ですから、私は
(1)操作又は、フィルタ
を使うことをすすめます。
(2)VBA
(3)特別のソフト(フリーソフト)
(ただし見つけるのが難しい)
を考える人もあるでしょう。
私は長年「imogasi方式」と
いうのを書いてきました。
上記で言うと(1)のAにあたります
Googleで「imogasi方式」で照会すれば、
沢山の質問回答の例が出て、
私以外の、タイプの回答も毎回出ています。……」


ということが書いてあり、
色々ほかのやり方を試してみたが、…
データ量が増えると上手くいかなかったり…
かなり苦戦した(・へ・)

で、結局、
「imogasi方式の作業列を使ったやり方」
で作ることにした。

例題)
日々のデータを入力しているシートから
該当する講師ごとに、
レッスン日、コース名、レッスン名、時間の行
を取り出し、1か月分のレッスン時間,講師料を集計する。
講師がやめたり、入ったり、講師数の変動あり。
<入力データシート>A列~G列まで7列
b0186959_11474027.jpg

<講師別集計シート>
b0186959_1385890.jpg


1.準備として、
 ★範囲名に名前の定義
 範囲名を4つ作ります。
 まず、<講師名簿シート>
b0186959_12571647.jpg

 ここで、Excelワザ!
 講師の増減がある場合、
 いちいち講師名リストを選択するのは、面倒なので
 OFFSET関数を使います。
=OFFSET(基準セル、移動する行数、高さ(行数)、幅(列数)
=OFFSET(講師名簿!$B$2,0,0,COUNTA(講師名簿!$B:$B)-1,1)
b0186959_1343416.jpg
 
 講師名の入っているB2を基準セルとし、
 移動する行、列は、「0」
 高さはCOUNTA($B:$B)を指定します。
 これは、B列に何か値が入力されたセルの数を数え、
 見出し行分を含めないように-1引きます。
 幅は、講師列だけなので、「1」を指定します。
同様に、入力データシートの
 範囲名:講師名
 =OFFSET(入力データ!$D$2,0,0,COUNTA(入力データ!$D:$D)-1,1)
 範囲名:入力データ
 =OFFSET(入力データ!$A$2,0,0,COUNTA(入力データ!$A:$A)-1,7)
 範囲名:作業列
 =OFFSET(入力データ!$H$2,0,0,COUNTA(入力データ!$H:$H)-1,1)
これで、4つの範囲名作成。
2.講師別集計表のC1に
 リストから講師名が入るように入力規則を設定。
b0186959_13342317.jpg

 すると、リストから選択できるようになります。
b0186959_13525950.jpg

3.入力データシートの作業列,H2に
 =IF(D2=講師別集計!$C$1,COUNTIF($D$2:D2,講師別集計!$C$1),"")
 (条件と一致した行に連番を入れる式です。
  D2の講師名と講師別集計表のC2の講師名が同じなら、
  D2から数えて、何個目かを表示します。同じでないなら、空白)
 この式を下方向へコピーしておきます。
 すると、一致する講師の作業列に1,2,3、
 と連番が振られます。
b0186959_13505550.jpg

この連番を利用して、
4.講師別集計シートの
 セルB5~F5に式入力
(右方向に式をコピーし、
  INDEX関数の中の列番号を修正)
b0186959_14425845.jpg

6.B列は「m/d」(日付)に
 F列は「h:mm」(時刻)表示にする。
 時間の合計欄は「[h]:mm」にしておく。
出ました~~~!
b0186959_1422393.jpg


 
  
[PR]
by school-pc | 2014-07-07 11:48 | Comments(0)