Excelについて教えてください。下記のURLを参考ください。

http://q.hatena.ne.jp/1264659728

Book1にシート1、シート2があります。下記のようにシート1にデータが存在します。
A1=01,B1=02,C1=03,D1=04,E1=05
A5=ABC,B5=DEF,C5=GHI,D5=JKL,E5=MNO
これらは対になっています。つまり、A1=01とA5=ABCは対になっています。
A6=06,B6=07,C6=08,D6=09,E6=10
A11=あいう,B11=かきく,C11=たちつ,D11=なにぬ,E11=はひふ
とデータがあります。これもA6とA11は対です。こういったルールで、A100までデータがあります。

シート2のC列には、縦方向に、つまりC1=04,C2=01,C3=01,C4=05.....といった具合にデータがあります。

シート2のD列に式をいれてデータを引っ張ってきたいです。
例えばシート1のE1の値でシート2のC列を検索します。データがあれば、シート1のE5の値をシート2のD列に表示します。この例では、D5にMNOが表示されるという仕様にしたいです。このような事が
実現できる関数をお教えください。vlookup,hlookupではできないと思います。

回答の条件
  • 1人2回まで
  • 登録:2010/01/29 20:57:00
  • 終了:2010/01/31 20:32:15

ベストアンサー

id:cerevisiae111 No.1

すべすべまんじゅう回答回数54ベストアンサー獲得回数52010/01/29 23:29:12

ポイント51pt

ご質問のsheet1にあるデータ何ですが1単位のデータ長が一定でないのでしょうか?

 A1~A5  の5列が1対

 A6~A11 の6列が1対

のようになっていますが、もし

 A1~A5  の5列が1対

 A6~A10 の5列が1対

の様にデータ系列が一定のものであるならば以下のような方法で検索が可能だと思います。

データ系列が一定でないと言う前提のご質問であればポイントなしで結構です。

<sheet1のデータ系列が一定の場合>

①sheet1の横並びのデータ系列を立て並びの連続データに置き換えるために

 新たにsheet3に並べ替え関数を書きます。

 ・sheet3のA1からB5に以下の関数を設定して、

 ・A1からB1までをマウスで選択状態にします。

 ・選択範囲の右下をマウスでつかみ100行目まで引っ張ってオートフィルします。

 ・sheet1のデータが立てならびに変換されます。

  A         B

1 =Sheet1!A1 =Sheet1!A5

2 =Sheet1!B1 =Sheet1!B5

3 =Sheet1!C1 =Sheet1!C5

4 =Sheet1!D1 =Sheet1!D5

5 =Sheet1!E1 =Sheet1!E5

②sheet2の D1 に下記の関数を設定します。

 =VLOOKUP(C1,Sheet3!$A$1:$B$18,2)

③sheet2のC列のデータ列の下端の行まで②の式をオートフィルします。

これで、参照検索できますが、VLOOKUP関数ですのでsheet1の「01,02...」という

検索対象が昇順で並んでいる必要があります。

この対策としては、sheet3 の縦並びデータを「データ-並び替え」でA列に対して昇順に並び替えることで対処できます。

id:akaired

ご回答ありがとうございます。大変参考になりました!

2010/01/31 20:31:09

その他の回答(1件)

id:cerevisiae111 No.1

すべすべまんじゅう回答回数54ベストアンサー獲得回数52010/01/29 23:29:12ここでベストアンサー

ポイント51pt

ご質問のsheet1にあるデータ何ですが1単位のデータ長が一定でないのでしょうか?

 A1~A5  の5列が1対

 A6~A11 の6列が1対

のようになっていますが、もし

 A1~A5  の5列が1対

 A6~A10 の5列が1対

の様にデータ系列が一定のものであるならば以下のような方法で検索が可能だと思います。

データ系列が一定でないと言う前提のご質問であればポイントなしで結構です。

<sheet1のデータ系列が一定の場合>

①sheet1の横並びのデータ系列を立て並びの連続データに置き換えるために

 新たにsheet3に並べ替え関数を書きます。

 ・sheet3のA1からB5に以下の関数を設定して、

 ・A1からB1までをマウスで選択状態にします。

 ・選択範囲の右下をマウスでつかみ100行目まで引っ張ってオートフィルします。

 ・sheet1のデータが立てならびに変換されます。

  A         B

1 =Sheet1!A1 =Sheet1!A5

2 =Sheet1!B1 =Sheet1!B5

3 =Sheet1!C1 =Sheet1!C5

4 =Sheet1!D1 =Sheet1!D5

5 =Sheet1!E1 =Sheet1!E5

②sheet2の D1 に下記の関数を設定します。

 =VLOOKUP(C1,Sheet3!$A$1:$B$18,2)

③sheet2のC列のデータ列の下端の行まで②の式をオートフィルします。

これで、参照検索できますが、VLOOKUP関数ですのでsheet1の「01,02...」という

検索対象が昇順で並んでいる必要があります。

この対策としては、sheet3 の縦並びデータを「データ-並び替え」でA列に対して昇順に並び替えることで対処できます。

id:akaired

ご回答ありがとうございます。大変参考になりました!

2010/01/31 20:31:09
id:Newswirl No.2

Newswirl回答回数210ベストアンサー獲得回数242010/01/30 00:46:58

ポイント35pt

少々無駄があるかもしれません。

前提条件は、1行目から96行目までの数字が昇順でなければならないということです。

=HLOOKUP(C1,INDEX(Sheet1!A1:E100,VLOOKUP(C1,Sheet1!A1:C100,3,TRUE)-2,1):INDEX(Sheet1!A1:E100,VLOOKUP(C1,Sheet1!A1:C100,3,TRUE)-2+4,5),5)

id:akaired

ご回答ありがとうございます。大変参考になりました!

2010/01/31 20:31:21
  • id:Mook
    対応する行は 1行-5行(4行間隔)、6行-11行(5行間隔) と不定なのですか?

    VBA を使ってユーザ定義関数を使えば出来そうですが、マクロは使用可能ですか。
  • id:akaired
    すいません、例が誤っていました。常に4行間隔で離れています。なので、
    A6=06,B6=07,C6=08,D6=09,E6=10
    A10=あいう,B10=かきく,C10=たちつ,D10=なにぬ,E10=はひふ
    が正しいです。VBAやScripting.Dictionaryなど使用はOKです。連想配列あたり
    使えればいけるかなと思ったりもしたのですが、アイディアがでてきません。

    ちなみにシート2のC列には重複データはありません。
  • id:airplant
    cerevisiae111さんの方法で良いと思います。
    タイプミスが何箇所かあるようなので、そこだけ気をつけていただければ要望のことは実現できると思います。【】で囲んだ箇所が修正箇所です。

     ・sheet3のA1からB5に以下の関数を設定して、
     ・A1からB1までをマウスで選択状態にします。

     ・sheet3のA1からB5に以下の関数を設定して、
     ・A1から【B5】までをマウスで選択状態にします。


    ②sheet2の D1 に下記の関数を設定します。
     =VLOOKUP(C1,Sheet3!$A$1:$B$18,2)

    ②sheet2の D1 に下記の関数を設定します。
     =VLOOKUP(C1,Sheet3!$A$1:$B$【100】,2【,false】)
    ($B$18を$B$100にして、後ろにfalseパラメータを追加)

    なお、重複なしとのことなので、VLOOKUPの元データ(sheet3)の並べ替えは不要です。
  • id:goobee
    前回の質問のコメント欄で既に回答が出てる気がするのは気のせい?
    http://q.hatena.ne.jp/1264659728
  • id:cerevisiae111
    >airplantさん修正ありがとうございます。
    重大なタイプミスでございました。
    修正がないとまともに実行できないですね
    感謝です!
  • id:akaired
    皆様、ご回答頂きありがとうございます、感謝です!

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません