• 締切済み

エクセル2010 同データの検索と関連セルの表示

エクセル関数に詳しくないので教えて頂けませんか? 画像の様なデータシートがあります。 ■データの場所 U~AD列には、画像の様に10列、8行が一括りになった上書きされる貼り付けデータがあります。 ■任意に入力される場所 B4、D4、F4、H4、J4、L4、N4、P4には、検査機器などの名前が入ります。 ■やりたい事 それでB4~P4に名前が入るとY列からそれと同じものを検索し、 その関連データである行のデータを表示させたいと思います。 また試行回数は10回まで表示させ、10回を超える分のデータは無視していただいて構いませんが、Y列に同じ名称のものがなければひとつ古いデータから抜き出す形になります。 一応、誤解があってはなりませんので、 試行回数1回目の機器A, 機器B 試行回数2回目の機器Aには実際の場所をセル番号で示しました。 また、U~ADのデータは毎回貼り付けられるため、表示データもそれと同時に更新されるものが理想です。 ※ちなみにA2~S2セルは空白ですので、ここは使えます。 これを出来れば関数のみで実現したいと思います。 どなたか詳しいかた、具体的な関数などで教えて頂けませんでしょうか? よろしくお願いいたします。

みんなの回答

  • kagakusuki
  • ベストアンサー率51% (2610/5101)
回答No.8

 回答No.3、6、7です。 >ただ、どうしても上手く行かないのがB9セルなんです。 >しかしまたなんでB9セルだけがとんちんかんな答えを出しているのかがわかりません。  「上手く行かない」、「とんちんかんな答え」というだけでは何が起きているのかさっぱり伝わって来ませんので、回答のしようが御座いません。  V列の他の行の値が表示されるのでしょうか?  それとも、V列以外の全く別のセルに入力されている値が表示されるのでしょうか?  もしそうであるのならば、B9セルやB14セル、及びB17セルには、それぞれ、どのセルに入力されている値が表示されているのでしょうか?  それとも、元データであるU列~AD列の中には入力されていない、意味不明な値が表示されているのでしょうか?  もしそうであるのならば、B9セルやB14セル、及びB17セルには、それぞれ、どの様な値が表示されているのでしょうか?

gekikaraou
質問者

お礼

再再度の回答ありがとうございます。 画像が添付できないため、新たに質問をさせて頂きました。 http://oshiete.goo.ne.jp/qa/8412584.html よろしくお願いいたします。

gekikaraou
質問者

補足

失礼しました、正しくは http://oshiete.goo.ne.jp/qa/8412611.html こちらです。

  • kagakusuki
  • ベストアンサー率51% (2610/5101)
回答No.7

 回答No.3、6です。 >数式は間違いなくS列に入力されています。  そうしますと、他に可能性として考えられますのは、S1セルに関数を入力した際に、気付かない内に何らかのミスを犯して、関数を写し間違えられたのかも知れません。  例えば、"■"という部分の「■」の前後にスペースや「'」等の、気付き難い文字や、改行等を、気付かない内に付けてしまってはおられないでしょうか?  ですから、念のために、S1セルの関数を、最初から入力し直して頂いた上で、S1セルをコピーしてS2以下のセルに数式のみを貼り付け直してみて下さい。  因みに、回答No.3で提示させて頂きました関数ですが、サイトの回答欄の仕様によって、「COUNTIF」の次の所で改行されている形で表示されておりますが、実際には、回答No.3で提示させて頂いたどの関数においても、関数の途中で改行が行われている箇所は御座いません。(とは言え、" "で括られている箇所以外の所であれば、例え途中で改行が行われていた処で、動作に影響する事は無い筈なのですが)

gekikaraou
質問者

お礼

失礼しました、当然オートフィル後もB9に習うB13、B17です。

gekikaraou
質問者

補足

何度も相談にのって頂き本当にありがとうございます。 少し進展がありました。 教えて頂いた事をひとつひとつ何回も試してみているのですが、どうしても上手く行かない為、数式を一度テキストエディタに貼り付けて、その後エクセルに貼り付けてみました。 そうすると、機器Aの一回目のデータをちゃんと表示しているのです。 もしかしたら、WEBとエクセルの文字コードの問題などがあったのかも。 ただ、どうしても上手く行かないのがB9セルなんです。 機器Aの他セルは上手く行っています。 しかしまたなんでB9セルだけがとんちんかんな答えを出しているのかがわかりません。 当然オートフィル後もB9に習うB14、B17・・・と間違った答えを返してきます。 また機器Aの一回目で上手く行っているC10セルも、オートフィルをすると2回目以降を空白で返してくると言う始末です。 上手く行きそうなだけに、不思議でたまりません。。。。。

  • kagakusuki
  • ベストアンサー率51% (2610/5101)
回答No.6

 回答No.3です。 >これは最下部までコピーすると事でよろしかったでしょうか?  質問者様が仰る「最下部」とは、Excelで扱う事の出来る最下段の行である1048576行目の事なのか、それともA列~E列の試行の結果を表示させる欄の中の最下段の行である48行目の事なのか、それともU列~AD列の中で最も下に入力されているデータが存在している行の事なのか、それともU列~AD列において、枠線が引かれていて表の形態となっているセル範囲の中での最下段の行の事なのか、どの「最下部」の事であるのか判りませんが、一応、Y列において機器名が入力されている行範囲を全てカバー出来る様に入力して頂ければ結構です。  従いまして、将来的にU列~AD列にデータが入力されている行数が増える事もあり得ると考えますと、データが増えるたびに一々追加でコピーし直すのは面倒ですので、「U列~AD列の表の中で最も下の行」と同じ行の所までコピーして頂いた方が宜しいかと思います。 >B9セルは #N/A となってしまいます。 >同じくB10~C10までを貼り付けましたが、同じようなエラーや空白が出てしまいます。  実際にはどの様なレイアウトにされておられるのかを見た訳では御座いませんので、断言は出来かねますが、おそらく質問者様は回答No.3において「(S1セル)に次の関数を入力して下さい。」となっている箇所の次に記述されている =IF(INDEX($Y:$Y,ROW())="","",INDEX($Y:$Y,ROW())&"■"&COUNTIF(INDEX($Y:$Y,1):INDEX($Y:$Y,ROW()),INDEX($Y:$Y,ROW()))) という関数を、S列ではなく別の列に入力しておられるのではないでしょうか?  その事自体は一向に構わない事なのですが、回答No.3において提示致しましたB9~C12の各セルに入力する関数は、あくまで >(ここでは仮にS列とします) とした場合の話における例に過ぎませんから、もしも、作業列をS列ではなく、別の列に設けている場合には、それに合わせて、B9~C12の各セルに入力する関数も変更せねばなりません。  おそらく、この「実際の作業列に合わせて、B9~C12の各セルに入力する関数を変更する」という作業を怠った事が、エラーが現れたり、表示が現れなかったりする原因ではないかと思われます。  ですから、B9~C12の各セルに入力する関数の中で、 $S:$S と記述されている全ての箇所を、実際に作業列を設けている列の列番号に合わせて変更して下さい。  尚、その際には、Excelの置換機能を利用されると便利です。  例えば、あくまで仮の話として、実際に作業列として使用している列がA列であったとしますと、まず、B9~G48のセル範囲をまとめて範囲選択します。  次に、Ctrlキーを押しながらHキーを押します。  すると、「検索と置換」 ダイアログ ボックスの[置換]タブが開きますので、「検索する文字列」欄に $S:$S と入力し、「置換後の文字列」欄に $A:$A と入力します。(あくまで作業列として使用している列がA列である場合の例です。実際に作業列として使用している列がA列以外の列である場合には、それに合わせて「置換後の文字列」欄に入力する内容を変更して下さい)  次に、、「検索と置換」 ダイアログ ボックスの[すべて置換]ボタンをクリックします。  すると、関数の中において $S:$S と記述されている箇所が全て $A:$A に書き換えられますので、関数の参照先をS列からA列に変更する作業を一括して行う事が出来ます。

gekikaraou
質問者

補足

再度のご回答ありがとうございます。 =IF(INDEX($Y:$Y,ROW())="","",INDEX($Y:$Y,ROW())&"■"&COUNTIF(INDEX($Y:$Y,1):INDEX($Y:$Y,ROW()),INDEX($Y:$Y,ROW()))) の数式は間違いなくS列に入力されています。 他の方も試して頂いたところ、kagakusukiさんの回答で求める結果が出ているところを見ると、私の側に何か根本的な問題があるのかと思っていますが、それがなんだかわからないのです。

  • tom04
  • ベストアンサー率49% (2537/5117)
回答No.5

No.2です。 >#NUM! というエラーが出るようですが・・・ 一般的には数式に問題がある場合が多いエラーです。 前回の数式はアップされている配置で↓の画像のようにこちらで作って確認した数式です。 こちらではエラーは表示されませんでした。 サンプル数が少ないので詳しい検証ができませんが、 前回投稿した前提条件は満たしているのでしょうか? その前提条件の一つでも違う場合、意味のない数式になってしまいます。 それと一つ気になるのが、8セルすべてが配列数式になっているでしょうか? 今一度確認してみてください。 ※ ↓の画像の配置でNo.3さんの数式もコピー&ペーストしてやってみましたが ちゃんと表示されました。 すなわちこちらではエラーの原因が掴めません。m(_ _)m

gekikaraou
質問者

お礼

質問者です。 再挑戦してみました。 色々試してみたのですが、表示されている部分のもとになるU~AD列のデータに問題があったようです。 このデータは他の場所からコピーした物ですので、元データの形式に依って、あるいは貼り付け方によってこの様な結果が生まれたのかも知れません。 問題なく表示されているデータだけを複数回(10回分になるように)コピーして貼り付けてみたところ、現在は10回目まで表示されています。 おそらくこれが原因の様な気がします。 何度もご回答頂きまして、ありがとうございます。

gekikaraou
質問者

補足

わざわざ検証までして頂きまして、大変感謝いたします。 ありがとうございます!! そうですか、それではこちらの何処かに問題があると考えるのが普通ですね。 今シートを触れないので後ほど結果はお伝えさせて頂きますが、機器Aの一回目、二回目が目的通りに表示されている以上、コピーの時に何か問題が起きていると言う事ですよね? うーん・・・謎ですねー・・・・。 帰って再挑戦してみます。

  • kagakusuki
  • ベストアンサー率51% (2610/5101)
回答No.4

 後それから、 >これを出来れば関数のみで実現したいと思います。 との事ですが、V列に入力されている値を1~8の繰り返しではなく、例えば V1セル   1#1 V2セル   1#2 V3セル   1#3 V4セル   1#4 V5セル   1#5 V6セル   1#6 V7セル   1#7 V8セル   1#8 V9セル   2#1 V10セル   2#2 V11セル   2#3 V12セル   2#4 V13セル   2#5 V14セル   2#6 V15セル   2#7 V16セル   2#8 V17セル   3#1 V18セル   3#2 V19セル   3#3 V20セル   3#4 V21セル   3#5 V22セル   3#6 V23セル   3#7 V24セル   3#8 等の様に、同じデータが複数回現れる事がない様な形式のデータに変更して頂ければ、作業列や配列数式といった面倒な方法を使わずに、関数のみで表示させる方法もあります。  その場合、機器ごとの思考結果に表示させるデータの中に、同類のデータの中に重複するデータが存在していない事が保障されているデータさえあれば、必ずしもV列のデータではなくとも構いませんし、上記の様な#付きの数字の様な形式ではなくとも構いません。  それどころか、定まった形式が無くとも構いませんし、順不同に並んでいても構いません。  兎に角、同じ列内には、たった1回しか登場していない様なデータでさえあれば、何でも構いません。  この様に、B列~G列に表示させるデータの1つとして、重複する事が無いデータを設ける事は可能でしょうか?  又、別の方法として、B列~G列においてV列の値を表示させるセルに入るデータを、実際にV列から取り出したデータでもなければ、データ上は1~8の数字ですらない、行番号の情報が含まれているデータとしておき、セルの書式設定を使用して、モニターに現れる表示や印刷の際に印字される表示のみを1~8に見える様にする(つまり、見かけ上は1~8に見えるだけで、セルに実際に入っているデータは別物)という方法を使う事で、作業列や配列数式を使う事なく、関数のみで実現させる事も可能なのですが、B列~G列においてV列の値を表示させるセルに入るデータは、見かけだけが1~8となっているだけでは駄目なのでしょうか?  又、配列数式の一種であるためコンピュータの負荷が大きくなりますが、「Shift+Ctrlキーを押しながらEnterキーで確定」という操作を必要とせずに、通常の関数と同様に単にデータを入力するだけで、結果に反映される方法もあります。  上記に挙げました様に、どの方法にも一長一短がありますが、どの方法が宜しいでしょうか?

  • kagakusuki
  • ベストアンサー率51% (2610/5101)
回答No.3

>少し不規則なのは、データが8個くくりになっていて、その8個の中になければその下の8個から探す。 という事は、要するに4列目に入力されている機器名のデータは飛び飛びに入力されている場合もあるものの、B列~G列に表示させる際には、間を詰めて表示させるという事ですね?  その際、「ひとつ古い」か否かは関係なく、兎に角、U列~AD列に入力されている行番号が小さい物から順番に表示させるという事で宜しいでしょうか?  又、試行回数が10回以内であれば全て抜き出し、10回を超えている場合には、10回までしか表示しないという事ですね?  処で、後述する方法で情報を処理する際に必要な条件なのですが、V列が1となっている行におけるU列のセルには、必ず日付が入力されていて、日付が入力されていない事はあり得ないと考えて宜しいのでしょうか?  そして、8行1組のデータの中で、U列に日付や時間、数値のデータが入力されているのは1箇所のみであり、機器名として数値データとして扱う事が出来る様な名称は存在していないと考えても宜しいでしょうか?  もし、上記の条件が満たされている場合には、下記の様な方法を使う事が出来ます。  まず、適当な使用していない列(ここでは仮にS列とします)の1行目のセル(S1セル)に次の関数を入力して下さい。 =IF(INDEX($Y:$Y,ROW())="","",INDEX($Y:$Y,ROW())&"■"&COUNTIF(INDEX($Y:$Y,1):INDEX($Y:$Y,ROW()),INDEX($Y:$Y,ROW())))  次に、S1セルをコピーしてS2セルに数式のみを貼り付けて下さい。  次に、B9セルに次の関数を入力して下さい。 =IF(INDEX($4:$4,COLUMN())="","",IF(ROUNDUP((ROW()-ROW($A$8))/4,0)>COUNTIF($Y:$Y,INDEX($4:$4,COLUMN())),"",IF(INDEX($V:$V,MATCH(INDEX($4:$4,COLUMN())&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0))="","",INDEX($V:$V,MATCH(INDEX($4:$4,COLUMN())&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0)))))  次に、B10セルに次の関数を入力して下さい。 =IF(INDEX($4:$4,COLUMN())="","",IF(ROUNDUP((ROW()-ROW($A$8))/4,0)>COUNTIF($Y:$Y,INDEX($4:$4,COLUMN())),"",IF(INDEX($Z:$Z,MATCH(INDEX($4:$4,COLUMN())&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0))="","",INDEX($Z:$Z,MATCH(INDEX($4:$4,COLUMN())&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0)))))  次に、B11セルに次の関数を入力して下さい。 =IF(INDEX($4:$4,COLUMN())="","",IF(ROUNDUP((ROW()-ROW($A$8))/4,0)>COUNTIF($Y:$Y,INDEX($4:$4,COLUMN())),"",IF(INDEX($AA:$AA,MATCH(INDEX($4:$4,COLUMN())&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0))="","",INDEX($AA:$AA,MATCH(INDEX($4:$4,COLUMN())&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0)))))  次に、B12セルに次の関数を入力して下さい。 =IF(INDEX($4:$4,COLUMN())="","",IF(ROUNDUP((ROW()-ROW($A$8))/4,0)>COUNTIF($Y:$Y,INDEX($4:$4,COLUMN())),"",IF(INDEX($AC:$AC,MATCH(INDEX($4:$4,COLUMN())&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0))="","",INDEX($AC:$AC,MATCH(INDEX($4:$4,COLUMN())&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0)))))  次に、C9セルに次の関数を入力して下さい。 =IF(INDEX($4:$4,COLUMN()-1)="","",IF(ROUNDUP((ROW()-ROW($A$8))/4,0)>COUNTIF($Y:$Y,INDEX($4:$4,COLUMN()-1)),"",IF(COUNT(INDEX($U:$U,1):INDEX($U:$U,MATCH(INDEX($4:$4,COLUMN()-1)&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0))),LOOKUP("9999/12/31"+1,INDEX($U:$U,1):INDEX($U:$U,MATCH(INDEX($4:$4,COLUMN()-1)&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0))),"")))  次に、C9セルの書式設定の表示形式を[日付]にして下さい。  次に、C10セルに次の関数を入力して下さい。 =IF(ISERROR(1/(INDEX($U:$U,MATCH("9999/12/31"+1,INDEX($U:$U,1):INDEX($U:$U,MATCH(INDEX($4:$4,COLUMN()-1)&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0)))+3)<>"")),"",INDEX($U:$U,MATCH("9999/12/31"+1,INDEX($U:$U,1):INDEX($U:$U,MATCH(INDEX($4:$4,COLUMN()-1)&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0)))+3))  次に、C11セルに次の関数を入力して下さい。 =IF(INDEX($4:$4,COLUMN()-1)="","",IF(ROUNDUP((ROW()-ROW($A$8))/4,0)>COUNTIF($Y:$Y,INDEX($4:$4,COLUMN()-1)),"",IF(INDEX($AB:$AB,MATCH(INDEX($4:$4,COLUMN()-1)&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0))="","",INDEX($AB:$AB,MATCH(INDEX($4:$4,COLUMN()-1)&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0)))))  次に、C12セルに次の関数を入力して下さい。 =IF(INDEX($4:$4,COLUMN()-1)="","",IF(ROUNDUP((ROW()-ROW($A$8))/4,0)>COUNTIF($Y:$Y,INDEX($4:$4,COLUMN()-1)),"",IF(INDEX($AD:$AD,MATCH(INDEX($4:$4,COLUMN()-1)&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0))="","",INDEX($AD:$AD,MATCH(INDEX($4:$4,COLUMN()-1)&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0)))))  次に、ドラッグアンドドロップを使用して、B9~C12の範囲の関数等を、D9~G12の範囲に複写して下さい。  次に、ドラッグアンドドロップを使用して、B9~G12の範囲の関数等を、B13~G48の範囲に複写して下さい。  これで、4行目に入力されている機器の試行データがB列~G列の9行目以下に、まとめて表示されます。

gekikaraou
質問者

補足

ご回答ありがとうございます。 やってみました。 まず 1点目 =IF(INDEX($Y:$Y,ROW())="","",INDEX($Y:$Y,ROW())&"■"&COUNTIF(INDEX($Y:$Y,1):INDEX($Y:$Y,ROW()),INDEX($Y:$Y,ROW())))  次に、S1セルをコピーしてS2セルに数式のみを貼り付けて下さい。  次に、B9セルに次の関数を入力して下さい。 の部分ですが、Y列の文字列が■の数字付きでS列に表示されています。 これは最下部までコピーすると事でよろしかったでしょうか? 2点目 =IF(INDEX($4:$4,COLUMN())="","",IF(ROUNDUP((ROW()-ROW($A$8))/4,0)>COUNTIF($Y:$Y,INDEX($4:$4,COLUMN())),"",IF(INDEX($V:$V,MATCH(INDEX($4:$4,COLUMN())&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0))="","",INDEX($V:$V,MATCH(INDEX($4:$4,COLUMN())&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0))))) の部分を貼り付けたら、B9セルは #N/A となってしまいます。 同じくB10~C10までを貼り付けましたが、同じようなエラーや空白が出てしまいます。 ちなみに B9  #N/A   C9  空白 B10  #N/A   C10  空白 B11  #N/A   C11  #N/A B12  #N/A   C12  #N/A これはどこがいけなかったのでしょうか? 

  • tom04
  • ベストアンサー率49% (2537/5117)
回答No.2

こんばんは! 外しているかもしれませんが・・・ ※ 前提条件 ※ (1)U列の日付は 1・9・17・・・と8行おきに入っている (2)U列の「工場名」は 4・12・20・・・とこちらも8行おきに入っている (3)U列の日付と工場名は8行内に1度のみ出現 (4)Y列の「検査機器」は8行内に1度のみ出現 というのが大前提です。 表の配置を変えることはできないみたいなので、 1セルずつコツコツ数式を入れてやる必要があると思います。 すべて配列数式になりますので、Shift+Ctrl+Enterで確定してください。 B9セル =IFERROR(INDEX($V$1:$V$1000,SMALL(IF($Y$1:$Y$1000=B$4,ROW($A$1:$A$1000)),ROW(A4)/4)),"") B10セル =IFERROR(INDEX($Z$1:$Z$1000,SMALL(IF($Y$1:$Y$1000=B$4,ROW($A$1:$A$1000)),ROW(A4)/4)),"") B11セル =IFERROR(INDEX($AA$1:$AA$1000,SMALL(IF($Y$1:$Y$1000=B$4,ROW($A$1:$A$1000)),ROW(A4)/4)),"") B12セル =IFERROR(INDEX($AC$1:$AC$1000,SMALL(IF($Y$1:$Y$1000=B$4,ROW($A$1:$A$1000)),ROW(A4)/4)),"") C9(セルの表示形式は「日付」に)セル =IF(COUNTIF($Y:$Y,B$4)<ROW(A4)/4,"",INDEX($U$1:$U$1000,INT(SMALL(IF($Y$1:$Y$1000=B$4,ROW($A$1:$A$1000)),ROW(A4)/4)/8)*8+1)) C10セル =IF(COUNTIF($Y:$Y,B$4)<ROW(A4)/4,"",INDEX($U$1:$U$1000,INT((SMALL(IF($Y$1:$Y$1000=B$4,ROW($A$1:$A$1000)),ROW(A4)/4)-1)/8)*8+4)) C11セル =IFERROR(INDEX($AB$1:$AB$1000,SMALL(IF($Y$1:$Y$1000=B$4,ROW($A$1:$A$1000)),ROW(A4)/4)),"") C12セル =IFERROR(INDEX($AD$1:$AD$1000,SMALL(IF($Y$1:$Y$1000=B$4,ROW($A$1:$A$1000)),ROW(A4)/4)),"") 以上の8セルに配列数式を入れます。 この画面からコピー&ペーストする場合は、 上記数式をドラッグ&コピー → 貼り付けたいセルを選択 → 数式バー内に貼り付け → そのまま(編集可能なまま) Shift+Ctrlキーを押しながらEnterキーで確定します。 数式の前後に{ }マークが入り配列数式になります。 最後にB9~C12の8セルを範囲指定 → C12セルのフィルハンドルで列方向へ2列ずつコピー → 行方向へ4行ずつコピー! 尚、オートフィルでコピーした後に「エラーチェックオプション」のマークが表示されると思いますが それが目障りであれば ファイル → オプション → 数式 → 「領域内の他の数式と矛盾する・・・」の項目のチェックを外してOK これでエラーチェックオプションのマークは表示されなくなります。 ※ 配列数式はPCにかなりの負担を掛けますので、とりあえず1000行まで対応できる数式にしています。 おそらく3000行程度であれば大丈夫だと思いますが、 極端にデータ量が多い場合は別の方法を考える必要があります。m(_ _)m

gekikaraou
質問者

補足

こんにちは、ご回答ありがとうございます。 まさにこの方法です。 機器Aの一回目のデータはちゃんと抜き出せました。 コピーした 機器Aの2回目のデータは表示もOKでした! ですが機器Aの3回目からや機器Bのデータは空白になったり、#NUM!となったりします。 ちなみに機器Aの三回目は B17 空白 C17 #NUM! B18 空白 C18 #NUM! B19 空白 C19 空白 B20 空白 C20 空白 機器Bの1回目は D9  1  E9  #NUM! D10 空白 E10 #NUM! D11 空白 E11 空白 D12 空白 E12 空白 と言う様な状態です。 これは、なぜかお分かりになりますか?

  • kagakusuki
  • ベストアンサー率51% (2610/5101)
回答No.1

 御説明内容に矛盾がある様です。 >試行回数は10回まで表示させ と仰っておられるにかかわらず、質問者様が添付された画像の例では、元データであるU~AD列には機器A,、機器Bともに3回分のデータが存在しているというのに、試行回数の欄であるB列~E列には、機器Aでは2回分のみ、機器Bでは1回分のみしか表示されておりません。  元データであるU~AD列には、機器A,と機器Bのどちらも同じ3回分のデータが存在しているのにもかかわらず、何故、表示されている試行回数が異なっているのでしょうか?  試行回数を何回分まで表示させれば良いのかという事を、実際には、どの様な基準で決めておられるのでしょうか?  又、 >Y列に同じ名称のものがなければひとつ古いデータから抜き出す形になります。 とありますが、同じ名称のものがないという事は、その機器に関するデータが無いという事なのですから、そもそも、古いデータなどというものも存在しないという事になる筈です。  質問者様が仰る「ひとつ古いデータ」とは、一体、何のデータの事なのでしょうか?  又、どの日付を基準として、それよりも「ひとつ古い」と言っているのでしょうか?  それに、質問者様が添付された画像の例では、試行された日付として12月22日という同じ日付が2箇所に入力されていますが、この様に同じ日付のデータが複数存在している場合には、「ひとつ古い日付のデータ」が複数存在するという事もあり得る事になります。  その様な場合には、どのデータを抜き出せば良いのでしょうか?

gekikaraou
質問者

補足

ご指摘ありがとうございます。 え・・・っと、説明が分かりにくかったですね、すいません、 茶色で示した部分は 例に挙げただけで、ご指摘の通り、10回目まで抜き出したデータが入ります。 今回の画像で言えば どちらも三回目までですね。 わかりやすく箇条書きで書きます。 ・Bセルに機器Aと言う文字列が入力された場合、Yセルの上から順にその文字列を探す。(最大10個まで) ・同じ文字列があった場合その関連するデータを1回目、2回目と10回目まで表示させる(関連データは画像を参照 要は上から10個データを抜き出してその関連セルも同時に抜き出し。 少し不規則なのは、データが8個くくりになっていて、その8個の中になければその下の8個から探す。 と言う事です。 これでお分かりになるでしょうか?

関連するQ&A

  • エクセル2010 同データの検索と関連セルの表示

    先の質問、 http://okwave.jp/qa/q8405162.html において、実践後の画像を再アップさせて頂きます。 まずは、S1セルに =IF(INDEX($Y:$Y,ROW())="","",INDEX($Y:$Y,ROW())&"■"&COUNTIF(INDEX($Y:$Y,1):INDEX($Y:$Y,ROW()),INDEX($Y:$Y,ROW()))) を入力後、ctrl + ENTER で確定し、オートフィルで最下部まで。 その後、すべてctrl + ENTER で 以下を貼り付けました。 B9セル =IF(INDEX($4:$4,COLUMN())="","",IF(ROUNDUP((ROW()-ROW($A$8))/4,0)>COUNTIF($Y:$Y,INDEX($4:$4,COLUMN())),"",IF(INDEX($V:$V,MATCH(INDEX($4:$4,COLUMN())&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0))="","",INDEX($V:$V,MATCH(INDEX($4:$4,COLUMN())&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0))))) B10セル =IF(INDEX($4:$4,COLUMN())="","",IF(ROUNDUP((ROW()-ROW($A$8))/4,0)>COUNTIF($Y:$Y,INDEX($4:$4,COLUMN())),"",IF(INDEX($Z:$Z,MATCH(INDEX($4:$4,COLUMN())&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0))="","",INDEX($Z:$Z,MATCH(INDEX($4:$4,COLUMN())&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0))))) B11セル =IF(INDEX($4:$4,COLUMN())="","",IF(ROUNDUP((ROW()-ROW($A$8))/4,0)>COUNTIF($Y:$Y,INDEX($4:$4,COLUMN())),"",IF(INDEX($AA:$AA,MATCH(INDEX($4:$4,COLUMN())&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0))="","",INDEX($AA:$AA,MATCH(INDEX($4:$4,COLUMN())&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0))))) B12セル =IF(INDEX($4:$4,COLUMN())="","",IF(ROUNDUP((ROW()-ROW($A$8))/4,0)>COUNTIF($Y:$Y,INDEX($4:$4,COLUMN())),"",IF(INDEX($AC:$AC,MATCH(INDEX($4:$4,COLUMN())&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0))="","",INDEX($AC:$AC,MATCH(INDEX($4:$4,COLUMN())&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0))))) C9セル =IF(INDEX($4:$4,COLUMN()-1)="","",IF(ROUNDUP((ROW()-ROW($A$8))/4,0)>COUNTIF($Y:$Y,INDEX($4:$4,COLUMN()-1)),"",IF(COUNT(INDEX($U:$U,1):INDEX($U:$U,MATCH(INDEX($4:$4,COLUMN()-1)&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0))),LOOKUP("9999/12/31"+1,INDEX($U:$U,1):INDEX($U:$U,MATCH(INDEX($4:$4,COLUMN()-1)&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0))),""))) C9セルの書式設定の表示形式を[日付]に。 C10セル =IF(ISERROR(1/(INDEX($U:$U,MATCH("9999/12/31"+1,INDEX($U:$U,1):INDEX($U:$U,MATCH(INDEX($4:$4,COLUMN()-1)&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0)))+3)<>"")),"",INDEX($U:$U,MATCH("9999/12/31"+1,INDEX($U:$U,1):INDEX($U:$U,MATCH(INDEX($4:$4,COLUMN()-1)&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0)))+3)) C11セル =IF(INDEX($4:$4,COLUMN()-1)="","",IF(ROUNDUP((ROW()-ROW($A$8))/4,0)>COUNTIF($Y:$Y,INDEX($4:$4,COLUMN()-1)),"",IF(INDEX($AB:$AB,MATCH(INDEX($4:$4,COLUMN()-1)&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0))="","",INDEX($AB:$AB,MATCH(INDEX($4:$4,COLUMN()-1)&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0))))) C12セル =IF(INDEX($4:$4,COLUMN()-1)="","",IF(ROUNDUP((ROW()-ROW($A$8))/4,0)>COUNTIF($Y:$Y,INDEX($4:$4,COLUMN()-1)),"",IF(INDEX($AD:$AD,MATCH(INDEX($4:$4,COLUMN()-1)&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0))="","",INDEX($AD:$AD,MATCH(INDEX($4:$4,COLUMN()-1)&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0))))) 機器Aの1回目は求める数値を抜き出して表示してくれています。 それを、コピー&ペーストで 機器Bの1回目、機器Aの2回目に貼り付けました。 これは、オートフィルでも同じ数値が帰って来ます。 それで問題点なのですが、 機器Bの1回目、E9セルには1月1日と、E10セルには空白が帰って来ています。 また機器Aの2回目も同じ場所、C13セルが1月2日と、C14セルが空白で帰って来ています。 ちなみにE9セルの数式は =IF(ISERROR(1/(INDEX($U:$U,MATCH("9999/12/31"+1,INDEX($U:$U,1):INDEX($U:$U,MATCH(INDEX($4:$4,COLUMN()-1)&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0)))+3)<>"")),"",INDEX($U:$U,MATCH("9999/12/31"+1,INDEX($U:$U,1):INDEX($U:$U,MATCH(INDEX($4:$4,COLUMN()-1)&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0)))+3)) E10セルの数式は =IF(ISERROR(1/(INDEX($U:$U,MATCH("9999/12/31"+1,INDEX($U:$U,1):INDEX($U:$U,MATCH(INDEX($4:$4,COLUMN()-1)&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0)))+3)<>"")),"",INDEX($U:$U,MATCH("9999/12/31"+1,INDEX($U:$U,1):INDEX($U:$U,MATCH(INDEX($4:$4,COLUMN()-1)&"■"&ROUNDUP((ROW()-ROW($A$8))/4,0),$S:$S,0)))+3)) となっています。 これで問題点が明らかになるでしょうか? よろしくお願いいたします。

  • エクセル2002で同じ文字を検索し、関連セルのデー

    こんばんは、エクセルにまだまだ詳しくないので教えていただけませんか? エクセル2002を使っています。 画像の様なシートがあります。 それで、、P14に機器Aと入力した時に、AS列にある機器Aのデータの下から5つを抜き出したいと思います。 これは機器Bとか機器Cとか毎回異なります。 例として、機器Aと入力した時は、P41に機器AのAVセルにあるデータ29をQ41にはAWセルにあるデータA29を。 上のP40にはデータ28、Q40にはデータA28をと言う風に読み出してくれればOKです。 もし機器Aのデータが4つしかなければP及びQセルの一番上は空白で結構です。 これをマクロではなく 関数で実現するにはどうしたらよいでしょうか? 詳しい方、よろしくお願いいたします・

  • データを検索し関連セルのデータを表示

    エクセル2002を使っています。 画像の様なデータシートがあります。 紫の部分 Q14に 機器A と入力された場合に AR列の機器Aを下から5つ検索し、その関連データを水色の部分に表示させたいと思います。 画像で言えばオレンジの部分のデータを水色の部分に表示させたいです。 (水色の部分は求めたい答えを回答者の方に解りやすく表示させたもので、もともとは空白です) 繰り返しますが、機器Aが5つ以上有った場合下から5つを抜き出します。 この場合、どういった関数を使えばよいでしょうか? よろしくお願いいたします。

  • 【エクセル】抽出データを上に詰めて表示させたい。

    重複するデータを上に詰めて表示さセル方法が解らないです。 B列はA列の元データから重複分は表示しないようにしています。 C列はA列の元データから重複分の数を数えています。 │A │B    │C│ D │ 1│あああ │あああ │3│ 2│いいい │いいい │2│ 3│あああ │     │ │ 4│ええ  │ ええ  │2│ 5│おおお │おおお │1│ 6│ええ  │   │ │ 7│いいい │  │ │ 8│あああ │  │ │ 9│うううう│うううう│1│ ・ ・ 上記のような表で、A列が元データで、 B列にはセルB1から、=IF(COUNTIF($A$1:A1,A1)>1,"",A1)という関数を オートフィルで下まで伸ばしています。 C列は、=IF(COUNTIF($A$1:A1,A1)>1,"",COUNTIF($A$1:$A$100,A1))と いう関数を使用しています。 そこで、B列C列の何も表示されていないセルを詰めて、上に詰めて 表示させたいです。マクロは良くわからないのでなるべく関数で お願いします。ちなみに、今使用している関数も、こうしたほうが いいというのがありましたら。訂正してもらえると助かります。 解りづらいところがありましたら、補足します。 宜しくお願いします。

  • セル内容の検索と表示

     エクセル初心者です。宜しくお願いします。      D   E   F   G   H          AB  AC   AD 1 a10                          あ   a39   か 2 b21                          う   a10   こ 3 x25                          い   a25   く  上記のような表があります。データとしては1500行ほどあります。 D1のa10に対応する値が同じシート内(この場合AD2の こ にあたります) 必ずD列の内容がAC列にあり、なおかつ、AC列右にあります。 D1に対応するAD2をE1に表示させたいのですが、関数ではどうすればよろしいですか?    

  • excel 関数 表示されているセルのみ抜き出す

    関数で表示されているセルだけ抜き出しを行いたいのですが、 (フィルタではなく)   A  B C D 1 2 2 2 4 3 5 4 4 8 5 5 6  7 8 8 10 11 A列のように、とびとびになっているものを、B列に表示されているものだけ抜き出したいのですが、 どうすればよろしいでしょうか。

  • あるセルにデータが入力された日を表示

    A列(A1)のセルにデータが入った場合、D列(D1)にそのセルに データが入力された日付を表示させたいのですが、どのように やったらいいでしょうか?TODAY関数を使うと毎回更新されて しまいます。 TEXT関数+TODAY関数で出来るかな?と思ったのですが、 =TODAY()を使うとセルに入力された日でなくて、ファイルを開いた 日付に毎回更新されてしまいます。 A列のセルにデータが入った時にD列にその入力日を表示させたい のです。こういう事は簡単に関数を使って出来ませんか?わかる方 がいれば教えてください。

  • エクセル 検索

    Microsoft Excel 2007を使用しています。 条件を選んでマッチするデーターを表示する方法をお尋ねします。 A列、A1に「1」以下セルA10に「10」までの数字が昇順で入力されています。 セルB1からセルB10まで果物の名前が入力されています。名前の重複はありません。 セルC1に果物の名前を入力したらセルD1に関数を用いてそれに対応するA列の数値が表示されるようにできませんでしょうか。 エクセルのヘルプを見ましたが、旨く当てはまる関数を探し出せませんでした。 宜しくお願い致します。 例)  セルA5に「5」という数値があり、B5に「柿」が入力されている。 セルC1に「柿」と入力すると、セルD1に「5」が表示される。

  • あいまい検索でヒットするデータを表示する方法

    office2016 ある機種のデータをsheet1のA列2行目以降に取り込みます code  ←題目 1233 127011 ←(A) C00001 C00233 … C90001 ←(B) その後昇順にならんだデータにします。約50行くらいあります。 (A)のデータは 1270で始まるデータで 127011,127021の様に6ケほど存在します。 (A)のデータは機種に必須のデータなので6ケの中でどれか1つが必ず存在します。 文字列と数字が混載表示のデータで1270が含まれるデータはありません。 C91270の様なデータは無いということです。 (B)のデータは C9000で始まるデータで C90001,C9002の様に5ケほど存在します。 (B)のデータは機種に必須ではないので、存在しない場合があります。 A列のデータの中から (A)で存在するデータはsheet2のG3セルへ (B)で存在するデータはsheet2のM3セルへ それぞれ表示したいのですが、何か簡単に一発で表示される良い方法があれば教えていただきたく。 現状の構成は次の通りです。 マクロで下記を対応してます。 Aデータの有無を確認する作業シート(シート名はA)を設けて A列にコピーし B2セルに判定として =IF(ISERROR(SEARCH("1270",A2,1)),"対象外",SEARCH("1270",A2,1)) これをA列の行分コピー B列で対象外の行を削除 1行目が題目で2行目に対象のデータが残るのでそれを表示 code 127011   1 の状況になるので SHEET2のG3セルは =IF(A!B2=1,A!A2,"") とすると 127011がG3セルに表示される Bデータの有無を確認する作業シート(シート名はB)を設けて A列にコピーし B2セルに判定として =IF(ISERROR(SEARCH("C9000",A2,1)),"対象外",SEARCH("C9000",A2,1)) これをA列の行分コピー B列で対象外の行を削除 1行目が題目で2行目に対象のデータがあれば残るのでそれを表示 code C90001   1 の状況になるので SHEET2のM3セルは =IF(B!B2=1,B!A2,"") とすると C90001がM3セルに表示される C9****のデータが存在しない場合、M3セルは空欄表示でOK これで一応やりたい内容は達成できていますが、関数で一発表示ができたらとの思いです。マクロでも構いません。 よろしくお願いします。

  • Excelで0と1を非表示にしたい。

    Excel2010で商品管理票を作りたいと思っています。 (Windows7 32bit) 画像のようにA列に商品コードを記入した場合 B列に何回目かを表示したいのです。 初めての商品の場合は非表示で 2回目から2、3、・・・というように増やしたいのですが どうすればいいでしょう? 【やってみたこと】 =IF(COUNTIF($A$2:A2,A2)>1,"*","") の様な関数を入れれば 2回目以降に”*”が表示されますが、回数がわかりません。 =COUNTIF($A$2:A2,A2)-1 ですと2回目が1と表示されてしまいます。 ”0”は書式設定のユーザー定義で「G/標準;G/標準;」にして非表示にしました。 もっと他の関数を使うのか、セルの書式設定で解決するのかもわかりません。 どなたかご教示ください。 よろしくお願いします。

専門家に質問してみよう