[[20040921141849]]　『LOOKUPで参照した次の行から再度LOOKUP』（なつ）　ページの最後に飛ぶ

『LOOKUPで参照した次の行から再度LOOKUP』（なつ）

 　　Ａ　　　　　　Ｂ　　　　　　　　
 １　００１　　　　Ｘ１２３４
 ２　００１　　　　Ｘ１２３４
 ３　００１　　　　Ｘ２３４５
 ４　００１　　　　Ｘ３４５６
 ５　００２　　　　Ｘ１２３４
 ６　００３　　　　Ｘ２３４５
 ７　００３　　　　Ｘ５６７８

 というシート１から、次のようなシート２を作ります

 　　Ａ　　　　　　Ｂ　　　　　　　　
 　　↓入力　　　　　
 １　００１　　　　Ｘ１２３４←LOOKUPで参照
 ２　　　　　　　　Ｘ２３４５←Ｂ２＝Ｂ１の場合、シート１のＢ３以下からLOOKUPで参照
 ３　　　　　　　　Ｘ３４５６←Ｂ３＝Ｂ２の場合、シート１のＢ４以下からLOOKUPで参照
 ４　００２　　　　Ｘ１２３４
 ５　００３　　　　Ｘ２３４５
 ６　　　　　　　　Ｘ５６７８

 行数は不定で、週に一回程度この作業を行ないたいと思います。
 関数で=IF(LOOKUP($A2,SHEET1!A:A,SHEET1!B:B))=A1,(LOOKUP($A2,SHEET1!X"←ここに何をいれたらいいの？":A,SHEET1!Y"←ここに何をいれたらいいの？":B),FALSE)
 みたいなところまで考えたのですが、行き詰まってしまいました。
 お知恵を貸していただけると助かります。

 色々と考えてみましたが、不測の結果が出たりして、時間が掛かりました。
A列は、数値を『000』の表示形式にしているとして考えています。
Sheet2のレイアウトは、作業列を加え、下の様な感じになりますけどよろしいでしょうか？
     A       B        C       D
 1 検索値	   結果	   行数	Sheet1最終行
 2  001	Ｘ１２３４    2	   7
 3	Ｘ２３４５    3	
 4	Ｘ３４５６    4	
 5  002	Ｘ１２３４    5	
 6  003	Ｘ２３４５    6	
 	Ｘ５６７８    7	
 	   #N/A	   #N/A	
 	   #N/A	   #N/A

 B2セルに
=IF(SUM(C1)>=$D$2,#N/A,VLOOKUP(LOOKUP(10^3,$A$1:A2),INDIRECT("Sheet1!A"&SUM(C1,1)&":B"&$D$2),2,FALSE))
 C2セルに
=IF(SUM(C1,1)>$D$2,#N/A,LOOKUP(CONCATENATE(TEXT(LOOKUP(10^3,$A$1:A2),"000"),"_",B2),CONCATENATE(TEXT(INDIRECT("Sheet1!$A$"&SUM(C1,1)&":$A$"&$D$2),"000"),"_",INDIRECT("Sheet1!$B$"&SUM(C1,1)&":$B$"&$D$2)),ROW(INDIRECT("Sheet1!$A$"&SUM(C1,1)&":$A$"&$D$2))))
 B2:C2セルを任意の行までコピぺして下さい。
 D2セルに
=MAX(IF(Sheet1!A1:A1000<>"",ROW(Sheet1!A1:A1000)))
この式は、Ctrl+Shift+Enterで配列数式として確定して下さい。

 B列に#N/Aが、出ているA列の行に1などと入力していきます。
 #VALUE!が出たらデータが終了です。
エラー値が見苦しいと思われる場合は、条件付書式で文字色を白にして下さい。
※上記の式上では、#N/Aをポイントにしている為。
 ～～～　　　～～～　　　～～～
なつさんの書かれている
 >１　００１　　　　Ｘ１２３４←LOOKUPで参照
このLOOKUPで参照する式は、どんな式で「Ｘ１２３４」を求められましたか？
書かれている式からの推測では、？？？だったもので。
(sin) 今回の自信度75％

 上記回答を一部修正しました。　(sin)自信度80％にUP

 sinさん、おはようございます。
 丁寧に検証していただいて、ありがとうございます。
 なのに！教えていただいた関数を試してみたのですが、#N/Aがでるばかりです。
 自信度80%ということは、こちらの問題だと思うのですが…
 わたしが作りたいのは、Sheet１に入力されたデータから、
 　　Ａ　　　　　　Ｂ　　　　　　　　
 　　↓入力　　　　　
 １　００１　　　　Ｘ１２３４
 ２　　　　　　　　Ｘ２３４５
 ３　　　　　　　　Ｘ３４５６
 という一覧です。

 Ａ１には、Sheet1上のＡ列にある数値を入力します。
 Ｂ１には、=VLOOKUP(A1,Sheet1!A:B,2,FALSE)として一つ目の参照はできるのですが、二つ目以降も何とかならないかな？と思って、質問しました。
 Ａ１に数値を入力することで、Ｂ２から下にも、２番目、３番目の参照結果がでるようにしたいです。

 >Ctrl+Shift+Enterで配列数式として確定
 と、
 >B列に#N/Aが、出ているA列の行に1などと入力していきます。
 >#VALUE!が出たらデータが終了です。
 というところが、よくわかりません(・・?)
 それと、式の解説していただけるとうれしいです。
 （なつ）

 解説（？）の前に、まず結果が出るか否か下記の事項をご確認ください。
1.Sheet1には、データが入力されており、
　A列を最優先キーB列を2番目に優先されているキーとして昇順に並び替えられていますか。
2.Sheet2の一行目は、空白もしくは上記の見出しを入れてください。
　B2,C2,D2に上記の式をコピペし、D2セルの式は数式バー上にカーソルを合わせ、
　CtrlキーとShiftキーおよびEnterキーを一緒に打ってください。
　{=MAX(IF(Sheet1!A1:A1000<>"",ROW(Sheet1!A1:A1000)))} のように{ }で括られれば、
　配列数式になった事になります。
　B2:C2を選択　コピーし、3行目以降にペーストしてください。
3.上記1・2項が完了した時点では、B2:C(?)までは、#N/Aが、表示され、
　D2にはSheet1に入力した最終行の行ナンバーと同じ数字が表示されているはずです。
　仮にこの数字が『1』でSheet1に2行以上入力されている場合は、配列数式になっているかを再度ご確認ください。
4.A2セルに該当する数値を入力するとB・C列に値が返されます。
　次にB列の一番上に出ている#N/Aの行のA列に次の数値を入力・・・以下同様。
　※　>#VALUE!が出たらデータが終了です。　は間違っておりました。
　データ最終行の告知に関しては、後々の宿題とさせて下さい。
とりあえず、上記の点に留意し、再度試行していただけますでしょうか？
(sin)　一方的な書き込みとなりましたが、迷解説(?)は、結果が出てから　っという事でも良いですか...

 sinさん、分かりやすく書いていただき、ありがとうございます。
 >3.上記1・2項が完了した時点では、B2:C(?)までは、#N/Aが、表示され、
　D2にはSheet1に入力した最終行の行ナンバーと同じ数字が表示されているはずです。
 までは、できたのですが、検索値を入力しても、#N/Aのままなんです…
 （なつ）

 原因が、分かりました。
 説明不足だったみたい。
 Ａ列に入力されている数字は、３～４桁の数字で、連続したものではありません。
 試しに１、２、３、という風に元データのＡ列の数字を置き換えたら、うまくいきました。
 さて、どうしたらよいのだろう？
 （なつ）

 とりあえず出来たようで安心しました。
 >Ａ列に入力されている数字は、３～４桁の数字で、連続したものではありません。
 3桁までならば対応するようにしていましたが、4桁だと修正が必要になるかも？
最大何桁までのデータを使いそうですか？分からない様で有れば色々と考えて見ますが・・・
また、Sheet1のA列およびB列は、連続していなくても昇順に並び替えされていれば大丈夫なはずです。
あと、Sheet2のA列も昇順に沿った入力順序が必須です。
※※Sheet1のA列にある数値をSheet2のA列にも入力するんですよね？

 ◆最終的な使い方を再度教えていただけますか？
Sheet1のA&Bセルで重複無く且つA列が同じものは省略する形式で全てのデータを表示するならば、
フィルタオプションの設定や区切り位置の機能を使えば、マウスだけでの操作で済む？かとも思います。
(sin)
 追記：ザクッと見直したところ4桁の場合には、10＾3(=1000)の部分を10＾4(=10000)にすれば、
A列の数値は、それより小さい数値ですので対応できそうです。
また、TEXT(･････,"000")の"000"を"0000"にすれば式の理屈はキレイに見えます。
 今日はそろそろ帰りますので、続きは金曜日以降になってしまいます。ご容赦下さい。

 sinさん、お付き合いいただき、ありがとうございました。
 明日中に、整理しておきます。
 懲りずに、またお願いしますね。
 (*^-゜)/~お疲れ様ですっ
 （なつ）

 sinさん、おはようございます。
 整理するのに、時間がかかってしまいました。
 実際に使うかたちのまま、説明させていただきます。

 Sheet1の形
 　　　Ａ　　　　　　Ｂ　　　　　　　　
 １　　担当　　　　　商品
 ２　　３０３　　　　Ｘ１２３４
 ３　　４９５　　　　Ｘ１２３４
 ４　　４９５　　　　Ｙ２３４５
 ５　１２３４　　　　Ｙ３４５６
 ６　１２３４　　　　Ｘ１２３４
 ７　１３５７　　　　Ｙ２３４５
 ８　１３５７　　　　Ｙ５６７８

 １行目はタイトル行で、２行目からデータが入力されています。
 初めてお話しますが、Sheet３に次のようなデータがあります。

 　　　Ａ　　　　　　Ｂ　　　　　　Ｃ　　　　　Ｄから先　　
 １　　担当　　　　　商品　　　　　取引先　　　月別取引金額
 ２　１２３４　　　　Ｘ１２３４　　　　２２
 ３　　４９５　　　　Ｘ１２３４　　２４２４
 ４　　３０３　　　　Ｘ１２３４　　　９９９
 ５　１２３４　　　　Ｙ３４５６　　１１１１
 ６　１３５７　　　　Ｙ２３４５　　　８００
 ７　　４９５　　　　Ｙ２３４５　　　　２２
 ８　１３５７　　　　Ｙ５６７８　　　９９９
 ９　１２３４　　　　Ｘ１２３４　　　　５５

 というものです。ここの数値は、これがまた別シートからの参照値（VLOOKUP）でできています。
 担当は２桁～４桁、商品は、アタマにアルファベットで中間にハイフンを２箇所含む１２文字、取引先は２桁～４桁です。
 このSheet３は、発注順で、正確にいうと、複数行を使っています（各取引で１０行）。

 Sheet３から、Sheet１を作ることはできました（SoulManさんに助けていただきました）。
 Sheet２のＡ列に担当コードを入力し（できれば任意の順で）、Ｃ列（サンプルではＢ列としていました）に商品コードを表示させ、Ｅ列から右側には、SUMIFを使って、取引金額をまとめたいのです。
 どこまで具体的に書いたら良いのか分からず、ぶっちゃけてしまいました(^-^)ゞ
 こういう説明でよかったでしょうか？
 （なつ）

 ひょっとして、Sheet3のデータを担当・商品コードで昇順に並び替え、コードを重複しない様にまとめて、
そのコードに基づいたD列以降の取引金額を集計させたいのでしょうか？
→この意図であれば、ピボットテーブルで出来そうな気がします。
意図が異なる場合は『違うー！』とレスして下さい。
～　～　～
 >Sheet２のＡ列に担当コードを入力し（できれば任意の順で）
 この任意の順とは、担当コードを思いのままに入れたいと言う事でしょうか？それとも、
基本として昇順で入れていくが、途中途中に抜け番が発生すると言う事でしょうか？
(sin) とりあえず気付いた点を書いてみました。

 はい、おっしゃるとおりです。
 ただ、Sheet３は、ひとつの取引に対して、10行のデータを持っていて、上の７行までが作業細目で、下の３行が、その合計や、差額などになっています。
 ひとつの取引が１行なら集計しやすいのですが、これは直しちゃいけないのです（ＴＴ）。。。
 Sheet２のＡ列に任意でというのは、思いのままにという意味です。
 よろしくお願いします。
 （なつ）

 あれっ、LOOKUPさんのレスが消えてる？？？
今までの考え方では、無理なので参考にさせていただこうと思ったのに。。。
なつさんへ、根本的に考え直さなければならないので、すこしお時間を下さい。
　※多分Sheet1でデータを整理する必要がありそうです。（並べ替え→フィルタオプション（重複無視））
マクロでよければ達人の方々がチャチャっと回答してくれるかも？
(sin)

 ええと、Sheet３から、Sheet１を作るところまではできたのですが、だめなんでしょうか？
 Sheet３Ａ～Ｃ列をコピー→作業用シートにペーストしてＡ～Ｃ列を並べ替え→Sheet１に重複無視して抽出しました。
 ここまでは、マクロで何とかなりました。SoulManさん、ドモ～♪ヽ(^-^ )
（なつ）

 >Sheet２のＡ列に任意でというのは、思いのままにという意味です。
 私の考え方でのネックは、ここなんですよ。
例えば、495と入力し、C列に抽出させます。その次にA列に入力する数値が495より小さい303だとその分が抽出できないのです。
常に入力する数字が、先に入力した数字より大きくないとダメなんです。(sin)

 わかりました。
 よろしくお願いします。
 （なつ）

 Sheet2のA列ですが、入力は一度でその下に＜該当分-1の数だけ＞羅列され、
条件付書式で文字白にし、見た目は入力されたものだけが見えるって言うのはダメですか？
但し、テンプレート的に別名で保存し直して、使い回していただかないといけませんが？
これでよろしければ、何とか考え付きましたが・・・　　(sin)

 何とか出来たかな？？？
A列は、お好きに入力してください。
C2セルに↓の式を入れ、下方コピーしてください。
=IF(ISERROR(LOOKUP(10^4,$A$1:A2)),"入力してください！",IF(ISNA(MATCH(LOOKUP(10^4,$A$1:A2),Sheet1!$A$1:$A$100,FALSE)),"該当担当者はありません!",IF(COUNTIF(Sheet1!$A$1:$A$100,LOOKUP(10^4,$A$1:A2))<=ROW()-LOOKUP(10^4,$A$1:A2,ROW($A$1:A2)),"",VLOOKUP(LOOKUP(10^4,$A$1:A2),INDIRECT("Sheet1!A"&MATCH(LOOKUP(10^4,$A$1:A2),Sheet1!$A$1:$A$100,FALSE)+ROW()-LOOKUP(10^4,$A$1:A2,ROW($A$1:A2))&":B100"),2,FALSE))))
Sheet1のデータ範囲は現状A1:B100で考えています。
不具合等有るとは思いますが、集中力がもう出ません。
続きは、週明けという事でお願いします。
 (sin)　　↑--修正＆追加書き込みしました。

 ありがとうございます。
 試してみました。
 Ａ列に数字を入力した行のＣ列のセルとすぐすぐ下のセルに同じ結果が出てしまうようです。
 私も自分でがんばってみたいのですが、式が難しくて理解できなくて…
 10^4ってどういう意味ですか？
 それから、マクロでもいいです。たぶん(>_<、) 
 （なつ）

 なつさん遅くなりました。
上記の式一部修正しました。Sheet1の範囲でA1:A13をA1:A100にしました。（A1:B100に対応）

 同じ物が表示されるとの事ですが、以下の点を再度ご確認ください。
1.Sheet1のデータは、A列（担当）最優先、B列（商品）2番目に優先で並び替えられて、重複がないか？
2.式の絶対参照と相対参照に間違いがないか？　C2およびC3セルの式の頭部分で比較すると
  C2：IF(ISERROR(LOOKUP(10^4,$A$1:A2)),
  C3：IF(ISERROR(LOOKUP(10^4,$A$1:A3)),
                                  ↑ここ　以下も同様です。($が付いているか否かでコピーすると勝手に変わります）
3.念の為の確認。C2セルに入れた数式をC3以下のセルにコピーされましたか？

 C2セルの式を例に全体の考え方を簡単に説明しますと、
まず、何回も出てくる　LOOKUP(10^4,$A$1:A4)　についてですが、
LOOKUP関数は、範囲または配列が昇順に並び替えられている事を前提に、範囲の先頭から検索値を検索します。
この際該当値が存在しない場合には、該当値より小さい最大値を代替該当値と認識します。
今回の場合、10＾4（＝10000）は、担当コード（2～4桁）の最大値9999よりも大きい数値ですので代替値を探します。
この際A列は、昇順に並び替えられていると思い込んでいますので、数値の入っている最終行の数値を該当値と認識する。
これは、私の勝手な解釈および使い方です。試験では○を頂戴できるかは？？？
以下、式を分解して説明してみます。
IF(ISERROR(LOOKUP(10^4,$A$1:A2)),"入力してください！",
A1（絶対参照）からA2（相対参照）の範囲内で10＾4以下の数値を探してもエラーの場合は、"入力してください！"と表示し、ある場合には
IF(ISNA(MATCH(LOOKUP(10^4,$A$1:A2),Sheet1!$A$1:$A$100,FALSE)),"該当担当者はありません!",
その数値と同じ値を、Sheet1のA1:A100の中より探して見つからない場合は、"該当担当者はありません!"と表示し、ある場合には
IF(COUNTIF(Sheet1!$A$1:$A$100,LOOKUP(10^4,$A$1:A2))<=ROW()-LOOKUP(10^4,$A$1:A2,ROW($A$1:A2)),"",
Sheet1のA1:A100に存在するその数値の個数が、Sheet2での数以下の場合は、””（空白）を返す。
Sheet2の数は、次の様に求めています。
ROW()は、数式が入力されているセルの行位置。そこからA列で数値の入力されているセルの行位置を引く事で比較する数値を作っています。
VLOOKUP(LOOKUP(10^4,$A$1:A2),INDIRECT("Sheet1!A"&MATCH(LOOKUP(10^4,$A$1:A2),Sheet1!$A$1:$A$100,FALSE)+ROW()-LOOKUP(10^4,$A$1:A2,ROW($A$1:A2))&":B100"),2,FALSE))))
VLOOKUP関数の範囲をINDIRECT関数で設定します。INDIRECT関数の中身は、
『Sheet1!A』と『最初にSheet1に出てくる行位置に上記の数をプラスさせて開始行を移動させた値』と『:B100』を繋げた範囲です。
解り難いとは思いますが、以上です。
また、マクロでも　という事ですが、私にはマクロを組めませんので、どなたかにタッチです。
(sin)

 (*^^)/ありがとうございますっ!
 sheet３のタイトル行が４行あったので、だめだったみたいです。
 何度も何度も、お忙しい中ありがとうございました。
 式の解説も助かります。
 ところどころ?('.')? ですが、とても勉強になりました。
 今後ともよろしくお願いいたしますm(__)m
 （なつ）

 出来た！　と受け取ってよろしいんですよね？
 >ところどころ?('.')? ですが、
 ここまで来ましたので、最後までお付き合いします！　どこですか？
あと少し気になる点が、あります。Sheet3がデータ、Sheet1が抽出補助データ、Sheet2が最終抽出シートですよね？
データは、何行ぐらいあるのでしょうか？結構重たいかも？？？　　(sin)

 はい、できました(^0^)y
 式の解説については、全体的には分かるし、部分的に分かるところもあるのですが、
 途中で混乱してきて、また最初から見直してる感じです。
 落ち着いて何度も見るようにします。
 sheet３のデータは現時点で2000行くらいです。
 今作っているサンプルでは400行くらいで、うまくいきました。
 Ｄ列以下の再計算に時間がかかるけど…
 （なつ改めなっぴ）←事情があって改名しました(・o・)ノ

 >はい、できました(^0^)y
 よかった！一安心です。
Sheet3で2000行。ふーむ、BOOKでは軽く2～3MBいきそうですね？
手動計算とか使って誤魔化しながら使ってみて、
本当に困ったらマクロ処理にするって感じですか？
その時は、SoulManさんが助けてくれることでしょう。
なっぴさん、頑張り過ぎないように。お体には気を付けて。。。
(sin)

コメント返信：

[ 一覧(最新更新順) ]

[[20040921141849]] 『LOOKUPで参照した次の行から再度LOOKUP』（なつ） ページの最後に飛ぶ

[[20040921141849]]　『LOOKUPで参照した次の行から再度LOOKUP』（なつ）　ページの最後に飛ぶ