エクセル 追加質問

質問

【質問】
Ａ1のセルに「日本一株式会社　マーケティングスタッフ　山田太郎」　と一行で書かれた時に、
日本一株式会社を判別してＢ1にはその住所、Ｂ2は電話番号、Ｂ3はＦＡＸ番号を
書かせたいのですがどうのようにしたらいいのでしょうか。

の質問に対して以下のような解答を頂きました。

【回答】
シート１のA1セルに入力されるデータはスペースでいくつかの文字列に分かれているとして、最初のスペースまでの文字列を対象として住所や電話番号FAX番号を表示させるとしたら、次のようにします。
データベースとしてシート２にはA列に日本一株式会社などの文字列が、B列には住所、C列には電話番号、D列にはFAX番号が入力されているとします。
そこでシート１のB１セルには次の式を入力してB３セルまでオートフィルドラッグします。

=IF(COUNTIF(Sheet2!A:A,LEFT(A$1,FIND("　",A$1)-1))=0,"",INDEX(Sheet2!B:D,MATCH(LEFT(A$1,FIND("　",A$1)-1),Sheet2!A:A,0),ROW(A1)))

と頂きましたが、
Sheet1　Ｂ3　まで反映されるのですが、Ｂ4、Ｂ5・・・と以降反映させる為にはどこを書き換えればよろしいのでしょうか。

ちなみに　Ｂ4は
=IF(COUNTIF(Sheet2!A:A,LEFT(A$1,FIND("　",A$1)-1))=0,"",INDEX(Sheet2!B:D,MATCH(LEFT(A$1,FIND("　",A$1)-1),Sheet2!A:A,0),ROW(A4)))
になっています。

変更箇所の指摘、回答よろしくどうぞ。

回答No.4

某HN クロメート(Chromate)（@CoalTar）

追加で質問するのなら以前の質問のリンクを張ってください
http://okwave.jp/qa/q6608835.html
せめて 質問履歴を公開していれば探しやすいのですが。。。

さらに言うと、以前の質問は解決していないのに、
追加で質問するのもどうかと思います。

関数の基礎的なものが分かっていないようなのでINDEX関数を勉強してください
=INDEX(範囲,行番号,列番号)

=ROW(範囲)
範囲を省略するとその数式の書いてあるセルが範囲となり、その行番号を返す。

KURUMITOさんの数式(長い方)はXL2003ではネストの制限に引っかかってしまいました(T.T)
おそらく、A6セル、A11セル・・・と続くだろうと考えた数式だと思われます。
その場合、B1:B5セルを選択して
=IF(ISNA(MATCH(LEFT(LOOKUP("ー",A$1:A1),
FIND("　",LOOKUP("ー",A$1:A1))-1),Sheet2!A:A,0)),"",""&
TRANSPOSE(INDEX(Sheet2!B:F,MATCH(
LEFT(LOOKUP("ー",A$1:A1),
FIND("　",LOOKUP("ー",A$1:A1))-1),Sheet2!A:A,0),)))
を数式バーにコピペ
[Ctrl]+[Shft] ＋[Enter] で確定、配列数式です({}で囲まれる)
B1:B5セルを選択して5行単位で下へオートフィル

極力シート上でのINDIRECT関数は避けたほうが良いです。
(名前の定義は大丈夫のようです)
計算対象以外の別ブックや別シートでセルの値を編集するだけで計算が走ってしまいます。

回答No.3

KURUMITO

前回の追加ご質問ですね。

A1,A5・・・とデータがある場合には
B１セルに次の式を入力して下方にオートフィルドラッグします。

=IF(A1<>"","",IF(COUNTIF(Sheet2!A:A,LEFT(INDEX(A:A,ROUNDUP(ROW()/4,0)*4-3),FIND("　",INDEX(A:A,ROUNDUP(ROW()/4,0)*4-3))-1))=0,"",INDEX(Sheet2!A:D,MATCH(LEFT(INDEX(A:A,ROUNDUP(ROW()/4,0)*4-3),FIND("　",INDEX(A:A,ROUNDUP(ROW()/4,0)*4-3))-1),Sheet2!A:A,0),MOD(ROW()-1,4)+1)))

回答No.2

kagakusuki

元の数式は、

=IF(COUNTIF(Sheet2!A:A,LEFT(A$1,FIND("　",A$1)-1))=0,"",INDEX(Sheet2!B:D,MATCH(LEFT(A$1,FIND("　",A$1)-1),Sheet2!A:A,0),ROW(A1)))

とするよりも

=IF(COUNTIF(Sheet2!A:A,LEFT(A$1,FIND("　",A$1)-1))=0,"",VLOOKUP(LEFT(A$1,FIND("　",A$1)-1),Sheet2!A:D,ROWS($1:2),0))

とした方が良いと思います。
　そして、それをB5まで反映させる場合には、

=IF(COUNTIF(Sheet2!A:A,LEFT(A$1,FIND("　",A$1)-1))=0,"",VLOOKUP(LEFT(A$1,FIND("　",A$1)-1),Sheet2!A:F,ROWS($1:2),0))

とします。
　そして、Sheet2の列が増えても変更する必要のない数式は、次の様になります。

=IF(COUNTIF(Sheet2!A:A,LEFT(A$1,FIND("　",A$1)-1))=0,"",VLOOKUP(LEFT(A$1,FIND("　",A$1)-1),INDIRECT("Sheet2!1:"&MATCH("゛",Sheet2!$A:$A,-1)),ROW(A1),0))

回答No.1

mshr1962

何行目まで反映かで変わりますが直す場所は一箇所です。
=IF(COUNTIF(Sheet2!A:A,LEFT(A$1,FIND("　",A$1)-1))=0,"",INDEX(Sheet2!B:D,MATCH(LEFT(A$1,FIND("　",A$1)-1),Sheet2!A:A,0),ROW(A1)))
上記の式の中央にある[INDEX(Sheet2!B:D,]の部分のB:DのDを下記の例にそって変更してください。

B3まで D
B4まで E
B5まで F
B6まで G
B7まで H
B8まで I
B9まで J
B10まで K
・
・
B25までZ
B26までAA