EXCEL関数 条件一致の値の反映について

質問

下記のようなデータがあります。
データは１セル毎に「氏名」「所属」「役職」が入力されています。

織田信長 調達部 １課長
豊臣秀吉 販売部 ２課長
徳川家康 企画部 係長
宮本武蔵 企画部 ２課長
源頼朝 販売部 主任
平清盛 販売部 主任

このデータを基に別シートで、「所属」「役職」を入力すると「氏名」を出力したいと思っております。

例えば、A1セルに「販売部」B1セルに「２課長」と入力するとC1セルに「豊臣秀吉」を出力する　という具合です。

OFFSET　と　MATCH　関数である程度までは上手くいくのですが、同じ「所属」に同じ「役職」があると最初のデータしか反映できません。

作業セルになんらかの数式をいれれば、なんとかなりそうな気がしますが、良い考えが思いつきません。

ご指示の程よろしくお願いします。

回答No.7

shiotan99

No.4です。

> 別シートで、「所属」「役職」を入力すると「氏名」を出力したい

「別シートで」を見落としていました。
一応、訂正しておきますね。

【仮定】
元データが Sheet1のA列からC列にあり、1行目が見出し、データは2行目から。
Sheet2の A1に「所属」、B1に「役職」を入力すると、Ｃ1以下に該当する「氏名」をすべて抽出。
作業列は、Sheet1の F列を使用。

Sheet1のF2に

=IF(AND(B2=Sheet2!$A$1,C2=Sheet2!$B$1),ROW(),"")

を入れ、元データの最終行までフィルコピー
今後もデータが増えることを考えて、多めにコピーしておいた方がいいかもしれません。
作業列が邪魔なら非表示にしてください。

Sheet2のC1に

=IF(COUNT(Sheet1!F:F)<ROW(A1),"",INDEX(Sheet1!A:A,SMALL(Sheet1!F:F,ROW(A1))))

を入れ、適当に下にフィルコピー
ROW(A1)の A1は、先頭がどのセルであってもそのままにしておいてください。
以上で、質問の要件は満たしていると思います。
「配列」の知識などは特に必要ありません。

以下、蛇足です。
No.4で余計なことを書かなければよかったのでしょうが、No.5さんはどうもわたしのコメントを曲解されているようです。
わたしは配列数式を使うべきではないと言っているのではありません。
配列数式が便利なことは承知しておりますし、実際に多用もしています。
ただ、配列数式が使える場面であっても、作業列を1列使うだけで数式がシンプルになれば、数式のメンテひとつとってもよりベターな場合があります。
状況に応じて使い分けたほうがいいのではないかという個人的な感想を述べたまでです。
会社などでいろいろな人が使うケースなどは特にそのように感じます。
もちろん上記の方法を押しつけるつもりはありませんし、どんな方法をとられるのかは質問者さんが決めればいいことです。
選択肢のひとつとして、こんな方法もあるのかと参考にしていただければ幸いです。

回答No.6

imogasi

＞作業セルになんらかの数式をいれれば・・
そのとおりと思います。
私は同じ型のことを何度も回答してますが。（ｉｍｏｇａｓｉ方式？）
例でやって見ます。
Ａ１：Ｃ８に下記データがあるとします。
Ａ２：Ｂ８がデータです。
Ａ１とＢ１は探す条件の文字列を入れるとします。
Ｃ列は下記に説明する関数式の結果です
a x
a x 1
s y
a x 2
a x 3
d u
f v
g x

Ｃ２に=IF(AND(A2=$A$1,B2=$B$1),MAX($C$1:C1)+1,"")と入れて
Ｃ８まで式を複写します。
後は、Ｃ列の１，２、３・・の連続数字を、ＲＯＷ（）関数で発生させる連続数と関連付けて
ＭＡＴＣＨ関数を使って行を割り出し、ＯＦＦＳＥＴやＩＮＤＥＸで値を
拾います。列数だけ、列を示すオフセット値を増やして拾います。
私の過去の回答例をご覧ください。

回答No.5

Wendy02

#4 さんの

＞ご質問のケースで「配列数式」を使うメリットはあまり感じられません。

一応、配列数式を書いた私としては、コメントをいれておきます。

配列数式は、PCの性能やメモリ、またVersionにも依存するようです。以前、調べた結果ですと、内部的な配列の制限が、Excel 2000 ですと、5500個程度です。それ以上のVersion では、この制限はなくなりました。全列・全行を指定しなければ可能なはずです。

また、ワークシートの配列数式の場合は、セルが5500程度ということではなく、累計で参照セルが、5500 どまりですから、縦横のマトリックスで検索する場合は、それは500行や、ひどいときは、100行で一杯になることもあります。

それ以外の場合は、私はひじょうに便利だと思いますね。

ただ、ある程度の大きさの場合は、簡単なイベント型のマクロを使ったフィルタ・オプションを用いれば、良いのかと思います。

回答No.4

shiotan99

こんにちは～

1つの数式でまとめようとすれば 「配列数式」になると思いますが、「配列数式」にすると、
・数式が複雑になる
・データ量が多いと重たくなる
というデメリットがあります。

個人的には、ご質問のケースで「配列数式」を使うメリットはあまり感じられません。
作業列を使った方がシンプルです。

A1に「所属」、B1に「役職」の検索値
「氏名」「所属」「役職」の一覧が、それぞれ F列・G列・H列 にあるとして。

作業列を仮にJ列とします。
一覧の1行目が見出しだとすれば、J2に

=IF(AND(G2=$A$1,H2=$B$1),ROW(),"")

と入れ、一覧表の最終行までフィルコピー

C1に

=IF(COUNT(J:J)<ROW(A1),"",INDEX(F:F,SMALL(J:J,ROW(A1))))

と入れ、適当に下にフィルコピー

ご参考まで。

回答No.3

mshr1962

通常は従業員コード等の固有のキーを検索値にします。
上記の例だと"販売部","主任"が二人ということでこれをキーにしたい場合は
"１主任","２主任"の様に固有になるような設定が必要です。

例　上記の表がSheet1!A2:C7にあるとして
D2=B2&C2&TEXT(SUMPRODUCT((B$2:B2=B2)*(C$2:C2=C2)),"000")
これをD7までコピー

C1=IF(COUNTIF(Sheet1!$D$2:$D$7,$A$1&$B$1&"*")>=ROW(),LOOKUP($A$1&$B$1&TEXT(ROW(),"000"),Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7),"")
これを表示したい人数分下方にコピー

回答No.2

fly_moon

キーというのを聞いたことがありますか？
例えば、
１　りんご　100円
２　みかん　80円
３　バナナ　30円
とあったとすると、１といえば[りんご　100円]、３といえば[バナナ　30円]とわかりますよね。
それが、
１　りんご　100円
１　みかん　80円
１　バナナ　30円
だとして、わたしがあなたに、「１を一つ下さい」と言ったらあなたは何を渡してくれますか？困りますよね？コンピューターも同じで、この場合とりあえず、[りんご　100円]を適用しているだけです。

この１とか２とかがキーと言います。キーはその一つのレコード(りんご　100円などの情報)に一つづつオリジナルなものでないといけません。それを使った検索にしなければならないのもおわかり頂けますよね？

この場合なんですが、コードというものをキーにされれば、うまく検索されると思います。
例えば
>源頼朝 販売部 主任
なら HS01
>平清盛 販売部 主任
ならHS02
>織田信長 調達部 １課長
ならCK11 というようにコードをオリジナルなものにして(単なる数字でもよろしいかと思いますが、入力時に覚えていられないので、意味のあるアルファベットを利用されるほうが良いかと思います)、そのコードを入力すると、所属、役職、氏名が表示されるというような形が一番よろしいかと思います。

回答No.1

Wendy02

こんにちは。
関数で処理にするには、データベース関数か、配列の知識がないと出来ません。

　　　F　　　　G
1　販売部　　主任

と置いたら、

A1:C7 までの中で、項目行が、1行目にあるとして、実際の氏名、所属、役職の内容は、２行目から始っているとしています。

=IF(SUMPRODUCT(($B$2:$B$7=$F$1)*($C$2:$C$7=$G$1))<ROW(A1),"",INDEX($A$1:$A$7,SMALL(IF(($B$2:$B$7=$F$1)*($C$2:$C$7=$G$1),ROW($A$2:$A$7),""),ROW(A1)),))

配列数式ですから、式を、一旦入力したら、F2を押して、『ShiftとCtrlを押しながらEnterキー』を押して、配列数式として再確定させます。

後は、必要なだけフィルダウン・コピーします。