エクセルで作られた社員名簿のデータを別シートにINDEX関数を使って抜き出したいのですが、MATCH関数での列指定が上手くいきません。
表は
   A    B     C     D     E     F
1 社員番号 社員氏名A 家族1氏名 家族1年齢 家族2指名 家族年齢
2 社員番号 社員氏名B 家族1氏名 家族1年齢 家族2指名 家族年齢
3 社員番号 社員氏名C 家族1氏名 家族1年齢 家族2指名 家族年齢
と、社員1名につきその家族が一行で表示されています。
これを別シートに家族1名につき一行の表があるので、年齢だけをこの表から持っていきたいのですが、一行に複数の家族がいるので、MATCH関数での列指定が上手くいきません。何かいい方法はないでしょうか?

このQ&Aに関連する最新のQ&A

A 回答 (2件)

#01です


OFFSET関数は基準セルから行数、列数を指定して離れたセルを参照しますが、基準セルがOFFSET(A1,0,0)、右となりならOFFSET(A1,0,1)になります。C1を指すときはOFFSET(A1,0,2)です

一方MATCH関数で列方向の検索をする場合はA列を基準(1)として何列目かを返します。C列に目的の検索語があれば3が返ります。
MATCHで得た3をOFFSET関数で使うために -1 しています。

今回はOFFSET(基準セル、行位置、列位置、行数、列数)の型も使用しています。OFFSET関数のHELPを見て、意味を考えて見て下さい

20は「20列目まで」を範囲とするために指定しましたが、別に40でも100でもかまいません。20を指定したので(家族氏名、年齢)の対は9個までと書いたのです(最初の2列は社員番号、社員氏名)
    • good
    • 0

シート1が以下の時に


    A列   B列    C列    D列  E列    F列
1行目 123456 社員氏名A  家族A1  40  家族A2  18
2行目 234567 社員氏名B  家族B1  35  家族B2  9
3行目 345678 社員氏名C  家族C1  29  

シート2に以下の結果を得るなら
    A列   B列    C列    D列
1行目 123456 社員氏名A  家族A1  40
2行目 123456 社員氏名A  家族A2  18
3行目 234567 社員氏名B  家族B1  35
4行目 234567 社員氏名B  家族B2  9
5行目 345678 社員氏名C  家族C1  29  

シート2のD1の式は以下になります
=OFFSET(Sheet1!$A$1,MATCH($A1,Sheet1!A:A,0)-1,MATCH($C1,OFFSET(Sheet1!$A$1,MATCH($A1,Sheet1!A:A,0)-1,0,1,20),0))

D1を下方向にコピーしてください。なお扶養家族は9人までOKです

この回答への補足

早速の回答ありがとうございます。こんな関数があったとは知りませんでした。勉強になりました。
ちなみに質問なんですが・・・
この式で-1や20という数字は何を意味しているのでしょうか?私にはちょっと複雑すぎて式の意味がいまいちよく理解できません^^;

補足日時:2007/09/26 15:56
    • good
    • 0

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

QINDEX関数とMATCH関数を使っての数式

順位 氏名 Pt  
一位 武田  5     
二位 田中 8   
三位 町田 10     
四位 荒井 9       
五位 阿部 7

順位   氏名
一位
二位
三位


上の氏名の欄にINDEXとMATCH関数を使って一位から
三位の方の氏名を表示させたいのですがわかりません。教えてください。。。宜しくお願いします。

Aベストアンサー

A    B
1 順位 氏名   
2 一位 武田      
3 二位 田中   
4 三位 町田      
5 四位 荒井       
6 五位 阿部
7
8 順位 氏名
9 一位
10二位
11三位

としてB9に、
=INDEX($B$2:$B$6,MATCH(A9,$A$2:$A$6,0),1)
を入れて下へオートフィルでどうでしょう。

QINDEX関数?それともMATCH関数?

INDEX関数?それともMATCH関数?

エクセル2000で下記の様な事をしたいのですが、どうしても分からず思い悩んでいます。
どなたか分かる方がいらしたら、教えてください。

【この表について】
・予測A(行3)と予測B(行20)と予測(行30)は最初から入っている数値
・結果A(行4)と結果B(行21)に左から1クール終わるごとに数値が入力される
・結果A(行4)と結果B(行21)に数値が入ると、実績(行31)が表示される
・(行38)と(行39)は予測Aに対する予測Bの割合


【やりたいこと】
<黄色のセル>
(1)B31が表示されたらB38を表示
(2)B31~C31が表示されたらC38を表示
(3)B31~D31が表示されたらD38を表示
(4)B31~D31が全て空白ならばD38を表示
(FGHについても同様に表示させたい)

→現在の数式は(1)~(3)についてはできるのですが、(4)の場合に#N/Aとなってしまいます。

<赤色セル>
(1)B31が表示されたらB38を表示
(2)B31~C31が表示されたらC38を表示
(3)B31~D31が表示されたらD38を表示
(4)B31~D31とF31が表示されたらF39を表示
(5)B31~D31とG31が表示されたらG39を表示
(6)B31~D31とH31が表示されたらH39を表示
(7)B31~D31とF31~H31が全て空白ならばH39を表示

→これもINDEXやMATCHを使って色々考えてみたのですが、(7)の時が#N/Aとなってしまいます。


どうぞよろしくお願いします。

INDEX関数?それともMATCH関数?

エクセル2000で下記の様な事をしたいのですが、どうしても分からず思い悩んでいます。
どなたか分かる方がいらしたら、教えてください。

【この表について】
・予測A(行3)と予測B(行20)と予測(行30)は最初から入っている数値
・結果A(行4)と結果B(行21)に左から1クール終わるごとに数値が入力される
・結果A(行4)と結果B(行21)に数値が入ると、実績(行31)が表示される
・(行38)と(行39)は予測Aに対する予測Bの割合


【やりたいこと】
<黄色のセル>
(1)B31が表示されたらB38を...続きを読む

Aベストアンサー

ごめんなさい。ご質問を勘違いしていました。

E30セルには次の式を入力します。

=IF(COUNT(B31:D31)=0,D38,INDEX(B38:D38,COUNT(B31:D31)))


I30セルには次の式を入力します。

=IF(COUNT(F31:H31)=0,H38,INDEX(F38:H38,COUNT(F31:H31)))

J30セルには次の式を入力します。

=IF(COUNT(B31:D31,F31:H31)=0,H39,IF(COUNT(B31:D31,F31:H31)<=3,INDEX(B39:D39,COUNT(B31:D31)),IF(COUNT(B31:D31,F31:H31)>3,INDEX(F39:H39,COUNT(B31:D31,F31:H31)-3),"")))

いずれのセルもセルの表示形式はユーザー定義で 00.00% のようにします。

Qエクセルの一行1レコードのDBから、1レコード1葉にレイアウトするには

一行に1レコードのデータが入った100行くらいのエクセルの表があります。(これはアンケート結果の表です。)1レコードには数10個のフィールド(アンケートの質問番号Q1~Q.XXに対する回答)があり、右に送っていかなければ、最後まで見えません。またフィールドによっては比較的長い文章もあります。
そこで、カード型データベースのように、1レコードを例えば1画面に収まるように(または例えばA4サイズに印刷できるように)、レイアウトしたいのですが、どのようにすればよいでしょうか。(1画面やA4サイズは例であって、長文がある場合には、縦に長くなったり、A4が複数枚になったりするのはかまいません)

ワードの差し込み印刷でできるのかなぁと想像していますが、やったことがないので、もっといい方法があれば、または差し込み印刷でできるよ、といったアドバイスでもいいのでお願いします。

Aベストアンサー

補足を見ると判るが、シート上の表示だけか、印刷をしたいのかはっきりしない。前半は表示、後半は印刷とテーマがわかり難い。
かく言うのも、表示だけならVBAを使わなくても関数で(下記のように)何とかできるから。
しかし全般的にはVBAが必要で、その技術が伴ってない、課題ではないかな。
>DBから
エクセルではDBは大げさです。エクセルの「表」や「リスト」と表現します。レコードも「行」です。
---
データベースのあるシート  Sheet1
1葉といっているシート   Sheet2
とする。

Sheet1の空きセルに 1セル使い、そこにデータの行番号を入れます。
そしてSheet2でシート上の配置は自由なセルに
Sheet1での列名と 内容を =INDIRECT("Sheet1!B"&(Sheet1!G1+1)) のように関数式を出したい項目数だけ入れていく。
すると
Sheet1 のデータ例が
連番氏名問1問2自動車保有
1山田はいいいえ3
2鈴木いいえいいえ1
とすると
Sheet2
(Sheet1のG1に連番1を入れると)
結果見た目(レイアウトは例示)
     B列       d列
B3とD3氏名山田

B5とD5問1はい

B7とD7問2いいえ

B9とD9自動車保有台数3
ーー
B列は値が固定なので手入力する
D3の式は =INDIRECT("Sheet1!B"&(Sheet1!G1+1))
D5の式は ==INDIRECT("Sheet1!C"&(Sheet1!G1+1))
以下同様。
ーー
G1を2に変えると
氏名鈴木

問1いいえ

問2いいえ

自動車保有台数1
に変わる。
ーー
印刷は
手作業か
VBAで無いと出来ない。関数では出来ない。
ボタンでも作ってクリックすると
Range("A1:F20".PrintOut
を実行するようにする。
==
VBAを本格的に使えば、クリックした行の明細を上記のように出したりできる。

補足を見ると判るが、シート上の表示だけか、印刷をしたいのかはっきりしない。前半は表示、後半は印刷とテーマがわかり難い。
かく言うのも、表示だけならVBAを使わなくても関数で(下記のように)何とかできるから。
しかし全般的にはVBAが必要で、その技術が伴ってない、課題ではないかな。
>DBから
エクセルではDBは大げさです。エクセルの「表」や「リスト」と表現します。レコードも「行」です。
---
データベースのあるシート  Sheet1
1葉といっているシート   Sheet2
とする。

Sh...続きを読む

QエクセルのMATCH関数について

エクセルのMATCH関数を使って“E18:E23”の表から125以下の1番目の値相当する”“E11:E16”の値を取り出す計算式を作成しています。(“E18:E23”表のそれぞれの行の数値が“E11:E16”の表のそれぞれの行の数値のリンクしています。そのときの数式が“=INDEX(E11:E16,MATCH(125,E18:E23,1),1)”です。この場合は上手くいくのですが、条件を“125以上の1番目の値相当するを作成して、”=INDEX(E11:E16,MATCH(125,E18:E23,-1),1)“の様にMATCHの式の最後を-1にするんですが、”#N/A“のエラーメッセージが表示します。どうすればいいのでしょうか?(*なお表“E18:E23”は数値が小さい順ですが、表“E11:E16”そうとは限りません)  それと、125以上の1番目だけでなく、2番目、3番目を拾い出す計算式はないでしょうか? 宜しくお願いします。関数に関しては初心者でいろいろ検索しましたが、ぴったり来ません。

Aベストアンサー

こういうことでは?

「125以上の1番目の値相当する」

=INDEX(E11:E16,MATCH(INDEX(SMALL((E18:E24>=125)*E18:E24,COUNTIF(E18:E24,"<=125")+1),,),E18:E24,0),1)

"<=125")+1) の+1が125の次の1番目ということで、
2番目、3番目 は、+2, +3 ということになりますが、そういう数が存在している限りです。

Qvlookupに、Indirect と Match 関数を組み合わせる

vlookup関数で、検索値にSheet1(Book1)から、範囲は、Sheet2(Book1)のセルにある名前のついているSheet1(Book2), そして、列番号は、指定した名前とマッチするSheet1(Book2)の列番号から、導き出そうとしています。
ところが、Matchで指定した範囲は、Indirect内の関数として認識されないようで、結果が得られません。

たとえば、
Book1のA1~E1にタイトルが入っています。(Index, Title, Book, Author, Frequency)
Book2には、
シート1: InputName
シート2: Output

シート2に以下の関数を入れてみましたが、うまくいきません。
ちなみに、VLOOKUPの列番号を Match関数の代わりに、5 とすると、結果オーライですが、Book1 の列順が変わっていることがあるので、Matchを使ってエラーを避けたいのですが、以下の関数のどこを直すべきでしょうか?
=IF(ISNA(VLOOKUP(C2,INDIRECT("'["&InputName!$A$2&"]"& InputName!$B$2&"'!"&"$A$2:$E$1400"),MATCH("Frequency",$A$2:$E$2,0),FALSE)),0,VLOOKUP(C2,INDIRECT("'["&InputName!$A$2&"]"& InputName!$B$2&"'!"&"$A$2:$E$1400"),MATCH("Frequency",$A$2:$E$2,0),FALSE))

よろしくお願いします。

vlookup関数で、検索値にSheet1(Book1)から、範囲は、Sheet2(Book1)のセルにある名前のついているSheet1(Book2), そして、列番号は、指定した名前とマッチするSheet1(Book2)の列番号から、導き出そうとしています。
ところが、Matchで指定した範囲は、Indirect内の関数として認識されないようで、結果が得られません。

たとえば、
Book1のA1~E1にタイトルが入っています。(Index, Title, Book, Author, Frequency)
Book2には、
シート1: InputName
シート2: Output

シート2に以下の関数を入れてみ...続きを読む

Aベストアンサー

> Book2には、シート List のみ。
> A1~E1には、Index, Title, Category, Author, Frequency とあります。

じゃぁ、indirect("'["&InputName!$A$2&"]"& InputName!$B$2&"'!"&"$A$1:$E$1")でしょう。


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング

おすすめ情報