EXCELで担当者から利用者名を抽出する方法

質問

利用者名簿約１００人に対して曜日ごとに担当する人を振り分けたデータがあります。
別シートで担当者が各曜日に担当する利用者リストを出したいのですがうまくいきません。
MACH関数とOFFSET関数を使うと出来そうなのですが・・・
下記では表示できませんでした。
どなたかご教授おねがいします。

=OFFSET(【データ元シート】!B5,MATCH($A$2,【データ元シート】!B5:B100,0),-1)

回答No.3

bunjii · ベストアンサー

提示の数式には引数の扱いに誤りがあります。
OFFSET関数の第1引数はどのセルからの参照においても【データシート】!$A$1からの隔たりで目的の値を抽出するようにすべきです。
また、第2引数の隔たりの行数を算出するためにMATCH関数を使っていますが対象範囲に複数の一致データがあるときは最初に一致する位置を返しますので不適切です。
更に、列の隔たりを固定で-1としていることも不具合の原因です。

=OFFSET(【データ元シート】!B5,MATCH($A$2,【データ元シート】!B5:B100,0),-1)
　　　　　　　　　　↓
=OFFSET(【データ元シート】!$A$1,SMALL(INDEX((【データ元シート】!B$1:B$200<>$A$2)*ROW(B$201)+ROW(B$1:B$200),0),ROWS(B$4:B4))-1,0)&""

OFFSET関数の他にINDEX関数を使う方法もありますので参考にされると良いでしょう。
=INDEX(【データ元シート】!$A:$A,SMALL(INDEX((【データ元シート】!B$1:B$200<>$A$2)*ROW(B$201)+ROW(B$1:B$200),0),ROWS(B$4:B4)))&""

但し、【データ元シート】の201行は空欄であることが条件です。

回答No.4

kagakusuki

回答No.1です。

　書き忘れておりましたが、回答No.1の関数では、【データ元シート】のA列に入力されている利用者氏名の中に、もしも同一氏名の利用者が複数居る(入力されている文字列データが全く同一のセルが複数存在する)場合には、正しい結果が得られなくなりますので、「同姓の利用者が複数いる場合には名前等も利用者氏名欄内に併記する」、「同一氏名の利用者が複数居る場合には、番号か住所等も利用者氏名欄内に併記する」などといった方法で、同じ利用者指名が重複しない様にして下さい。

回答No.2

msMike

B4: =IFERROR(INDEX(Sheet1!$A$1:$A$100,SMALL(IF(Sheet1!B$5:B$100=$A$2,ROW(A$5:A$100),""),ROW(A1))),"")

【お断り】
１．上式は配列数式として入力
２．簡単のために、【データ元シート】を Sheet1 としています
【お詫び】
MACH関数を使わなくて済みません m(_._)m

回答No.1

kagakusuki

>利用者名簿約１００人に対して

という事は、【データ元シート】上に利用者氏名が入力されているのはA5:A104のセル範囲であると考えれば宜しいのですね？
　その場合、まず「利用者リストを表示させるための別シート」のB3セルに次の関数を入力して下さい。

=IF($A$2="","",IF(ROWS($3:3)>COUNTIF(【データ元シート】!B:B,$A$2),"",INDEX(【データ元シート】!$A:$A,MATCH($A$2,INDEX(【データ元シート】!B:B,IF(ROWS($3:3)=1,ROW(【データ元シート】!$B$4:$V$4),MATCH(B2,【データ元シート】!$A:$A,0))+1):【データ元シート】!B$104,0)+IF(ROWS($3:3)=1,ROW(【データ元シート】!$B$4:$V$4),MATCH(B2,【データ元シート】!$A:$A,0)))))

　次に、「利用者リストを表示させるための別シート」のB3セルをコピーして、同シートのB3～V3のセル範囲に貼り付けて下さい。
　次に、同シートのB3～V3のセル範囲をコピーして、同シートのB列～V列の4行目以下に貼り付けて下さい。

　以上です。