Practical Excel
応用:表要素の取得

【当サイト人気ソフト】
ナンプレ無双:問題作成・解析・印刷・プレイができる無料ナンプレソフト。雑誌掲載多数

表要素の取得

ここでは関数を組み合わせ、表の行・列、各要素を取得します。
取得には様々な方法があると思いますが、「ワークシート関数のみで実装すること」「なるべくシンプルにすること」に重点を置き、考えてみました。

尚、紹介する方法を実装したサンプルファイルはダウンロード出来ますので、興味のある方はご覧ください。

準備

表と名列を準備します。 今回のサンプルは、遠足や林間学校での班名簿の表から、「班名」(行要素)と「係」(列要素)を、名列に引っ張ってくるものです。
流れは「班名簿から該当生徒を見つける」「その場所の行と列を取得する」「その情報を元に名列に該当要素を表示する」といったものです。

表の要素を取得するために、表の左の列と上の行に1~○(要素数分)の数字を入れてあります。


行の取得

名列の右側に、行列番号を取得する作業列を作り、次のように数式を入れます。
=SUM(IF($C$4:$F$7=$C12,$A$4:$A$7))


次に、この数式を配列数式にします。配列数式にしないとエラーになります。
配列数式にする方法は、数式バーにカーソルがある状態で、
「shift」と「ctrl」を押しながら、「enter」を押します。

この数式は、班名簿の中から「小矢部 翔」を探し、該当する場合、その位置(行)にあたる数字が[A4:A7]の中から表示される、といったものです。SUM関数を使用しているのは、単一セルに配列を表示ができないため、「配列内の数字を合計し、それを表示する」という形をとっているためです。

列の取得

列も同様の方法で取得するため、次のように数式を入れます。
=SUM(IF($C$4:$F$7=$C12,$C$2:$F$2))

指定するセル範囲が少し異なるので、注意してください。
こちらも「shift」と「ctrl」を押しながら「enter」を押し、配列数式数式にします。

これで行と列の取得ができました。これらの数式をそのまま下にコピー&ペーストします。

要素の取得

次に、表の行と列番号から要素を取得します。
まずは「班名」を表示するため、名列一人目の「班」箇所に、次の数式を入れます。
=INDEX($B$4:$B$7,$F12)

Index関数を用い、該当の要素を表示します。これは配列数式ではなく、通常の数式です。

「係」の方も同様に数式を入れます。Index関数の引数の値と場所が、先と少し異なるので注意してください。
=INDEX($C$3:$F$3,1,$G12)


あとは、これらの数式を下までコピー&ペーストして完了です。

備考

エラー処理をしていないので、必要な場合は実装してください。

この方法は、一意のデータであることが前提です。ですので、今回の場合「同姓同名がいる」「係を複数担当するため、同一人物が複数個所表示されている」等は、上手くいきません。 ただ、そのような場合でも、最初に氏名のあった箇所の要素を表示することは可能です。例えば、「SUM」関数の代わりに「SMALL」関数を使用し行を求め、MATCH関数とINDIRECT関数を組み合わせて列を求めるなどです。 工夫によって、様々な状況に対応できると思います。


サンプルファイル

これまで説明したもののファイルです。「仕組みの理解」に重点を置いているため実用的な作りにはなっていませんので、自分なりに改変して下さい。尚、ファイルによる問題が生じないよう気をつけておりますが、万が一、本ファイルの使用によって損害等が生じたとしても、責任は取りませんので、悪しからずご了承下さい。

サンプルファイル「表の行列要素取得」・・・[sample_get_element.xls] 32kb

Excel Tips for Teachers

Copyright (C) 2009 坂江 保 All Rights Reserved.