[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『【関数で】表の固定されてない列の同じ行にある値を検索する 』（はっしー）
[[20150314234209]]でVBAによりいろいろとご教授頂いていますが、そのスレ内でβ様より2015/03/17(火) 06:04のレスで ご提案頂いたことを受け【VBA】ではなく【関数】による方法があれば、どうかご教授ください。宜しくお願いします。
VBAは諦めた訳ではありません。が、関数でもよい方法があればどちらも試してみて、よりマッチしたものを...と思っ ています。
VBA+関数でも、当然OKです。
＜ 使用 Excel：Excel2013、使用 OS：Windows 8 .1 ＞
一応、リンクもはっておきます。
[[20150314234209]] 『表の固定されてない列の同じ行にある値を検索する』（はっしー）
（β） 2015/03/18(水) 05:41
やっぱり関数でやるのは難しいんですかね... （はっしー） 2015/03/19(木) 07:55
難しいというか前のスレに書かれてる条件を全部読み込むのが大変というか。 どこまでがお望みなのかよくわからないので敬遠しがちに…
とりあえず昔無理やりやったヤツがあるので、参考までのたたき台に。
条件 ・1行目に空白はない。型番の入っている列の頭にも何か決まった文字列がある（んだよね？） ・とりあえずA表の型番の上にある文字列を「データ1」 B表の型番の上にある文字列を「データ2」と仮定。 ・前作ったのがSheet2だったからそのままになってるんで適宜変えて。 ・最大データ数（行数）を1000に仮定。
A B C D E F G H I J 1 データ1 3/2 3/3 3/4 3/5 データ2 3/2 3/3 3/4 3/5 2 AA101 123 456 789 10 BB105 23 45 67 89 3 AA102 124 457 790 11 CA101 24 46 68 90 4 AB101 125 458 791 12 CC101 25 47 69 91 5 BB103 126 459 792 13 CC102 26 48 70 92
名前の定義にて以下を定義
名前：データ1
参照範囲：=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A$1:$A$1000),MATCH("データ2",Sheet2!$1:$1,0)-1)
名前：データ1頭
参照範囲：=OFFSET(Sheet2!$A$1,0,0,1,MATCH("データ2",Sheet2!$1:$1,0)-1)
名前：データ2
参照範囲：=OFFSET(INDIRECT(ADDRESS(1,MATCH("データ2",Sheet2!$1:$1,0),1,1,"Sheet2")),0,0,COUNTA(INDIRECT(ADDRESS(1,MATCH("データ2",Sheet2!$1:$1,0),1,1,"Sheet2")&":"&ADDRESS(1000,MATCH("データ2",Sheet2!$1:$1,0),1,1))),COUNTA(Sheet2!$1:$1)-MATCH("データ2",Sheet2!$1:$1,0)+1)
名前：データ2頭
参照範囲：=OFFSET(INDIRECT(ADDRESS(1,MATCH("データ2",Sheet2!$1:$1,0),1,1,"Sheet2")),0,0,1,COUNTA(Sheet2!$1:$1)-MATCH("データ2",Sheet2!$1:$1,0)+1)
別シート A B C D 1 3/3 3/4 3/5 2 CA101 46 68 90
B2に =IFERROR(VLOOKUP($A2,データ1,MATCH(B$1,データ1頭,0),0),VLOOKUP($A2,データ2,MATCH(B$1,データ2頭,0),0)) 下と右に必要なだけフィルコピー
誰かもっときれいな式にしてくれ…
（1111） 2015/03/19(木) 11:17
↑ の表をお借りしま〜す（元表が「Sheet2」ね）
B2 =IFERROR(VLOOKUP($A2,INDEX(Sheet2!$C$2:$Z$2,MATCH("?*",Sheet2!$C$1:$Z$1,0)):Sheet2!$AX$10,MATCH(B$1,INDEX(Sheet2!$C$1:$Z$1,MATCH("?*",Sheet2!$C$1:$Z$1,0)):Sheet2!$AX$1,0),FALSE),"")
右と下にコピーしてちょｗ
表Aだけならできるってことなんで、表Bの検索だけっすｗ
リンク先ほとんど読んでないんで、もしかしたら書いてあるかもしれないけど、 以下【使用上の注意】 （1）型番列の1行目（機種名？）は文字列なら何でもいいっす。 （2）表Bの型番列は【C列からZ列の間】にある 表Bの最後の日付は【AX列まで】にある、 元データは2行目から10行目まで、という前提でおまｗ 実情に合わせて変更してちょ。 （3）日付の追加は列の挿入でなく、追加のたびごと全データを上書きしてるという前提。 列挿入とかデータの切り貼りとかしてるのなら、INDIRECT関数をかませる等しないとダメっすｗ （4）その他、何か重要なことを書き漏らしているような気がして仕方がないｗ （帰って来た名無しのおっさん） 2015/03/19(木) 16:04
1111様、帰って来た名無しのおっさん様、お世話になります。 レス遅くなりすみません。
>難しいというか前のスレに書かれてる条件を全部読み込むのが大変というか。 >どこまでがお望みなのかよくわからないので敬遠しがちに… 横着しました、不便お掛けしてすみません。<m(_ _)m>
まず、1111様の【名前の定義】ですが、これは表A、表Bのあるシートの指定した範囲に名前を付ける ということでよろしいでしょうか？ そうであれば、前スレでも書いたんですがこの表A、Bのあるシートは他ファイルからの全コピペしたものです。 それに【名前の定義】はできませんよね？ それから、このシートを含め、シート数3に表が5つあり、そこから出力用シートに抽出したいと思ってます。 なので表A,B形態のシートが2つ、表が1つのものが1つ、出力用と合わせ4シートでできています。 出力用以外の3シート（表のあるシート）は別ファイルからシート毎にコピー貼り付けしたものです。
以上の点を踏まえ、可能であれば引き続きお願いしたいと思います。 また、条件や設定等で説明の不備が多々あると思われますので、適時説明させて頂きます。 宜しくお願いいたします。 （はっしー） 2015/03/19(木) 21:34
連続レスで済みません。 続いて、帰って来た名無しのおっさん様、お世話になります。
> 表Aだけならできるってことなんで、表Bの検索だけっすｗ それで大丈夫です。
>（1）型番列の1行目（機種名？）は文字列なら何でもいいっす。 はい、OKです。
>（2）表Bの型番列は【C列からZ列の間】にある いいえ、表Aの日付の数しだいで変わります。最後尾は今まで確認したもので最も後ろでCL列のがあります。 表Bの最後の日付は【AX列まで】にある、 上記CL列が型番名の場合、最後尾はDN列までのものがありました。 元データは2行目から10行目まで、という前提でおまｗ 実情に合わせて変更してちょ。 了解です。 >（3）日付の追加は列の挿入でなく、追加のたびごと全データを上書きしてるという前提。 列挿入とかデータの切り貼りとかしてるのなら、INDIRECT関数をかませる等しないとダメっすｗ 「追加のたびごと全データを上書き」です。
（はっしー） 2015/03/19(木) 21:51
>表A、Bのあるシートは他ファイルからの全コピペしたものです。 それに【名前の定義】はできませんよね？
元スレでは、扱っているのが送られてきたブックそのもので、さわれない、コピペもできないと思い込んでいたんですが 別ブックにコピペしたものですか？ それなら、なんでも自由自在にできるのでは？ その都度領域が変わる部分もできると思いますが、少なくとも 表A,B の最初の列の機種名のセルには定義できますよね。 表Aに列挿入されても、動いていった先に名前も移動しますので。
そうできるなら、関数であれＶＢＡであれ、めちゃくちゃ楽になりますが？
ＰＳ）元スレの最後の試行も、お願いできたらうれしいですね。
（β） 2015/03/19(木) 21:57
β様、お世話になります。
> 別ブックにコピペしたものですか？ それなら、なんでも自由自在にできるのでは？ コピペのシートは新たなファイルが送られてきたら上書きで全コピペするのでそのシートへは式は入れられないと思います。仮に入れても上書きで消えてしまいます。
(1)他社メール添付ファイルの各シートを全コピ（仮にシート1表A,B、シート2表C,D、シート3表Eとします。表A,Bは今まで出てきた表A,Bの元表にあたるものです）
(2)自ファイルのシート1に表A,Bのシート貼り付け、同様にシート2に表C,D、シート3に表Eも貼り付けます。
(3)シート4の出力用シートのD1に必要日（表示させたい日付けの初日）を入力すると出力用シートにある表（ひな形）に日付と型番別、日付別の数量が表示される。
ここで、（1）は編集不可、（2）は編集可能だが上書きコピーで編集したものが消えます。（3）は編集可能です。
（はっしー） 2015/03/20(金) 01:01
βさんも書いてくださってますが、(2)のファイルにコピペするってことで【名前の定義】は可能ですよ。 上書きコピーだろうが列の挿入だろうがデータが１セルもなかろうが、今回の【名前の定義】には関係ないんで（計算式がエラーになるだけ）。 シートの名前とコピペするデータの位置（今回の場合日付行が1行目で開始列はA列、ってだけ）を間違わなければ、 前もって先に(2)のファイルに【名前の定義】をしておけばデータを好きに追加できるよ。
(2)のファイルは毎回新規ファイルで作るんだ、というならテンプレートにしておくという手もある。
まあ他の条件等もあるかもしれないし、やってみるだけやってみてよ。 どうしても【名前の定義】は嫌だってならここで終わるけどねｗ
（1111） 2015/03/20(金) 08:41
>>（2）表Bの型番列は【C列からZ列の間】にある > いいえ、表Aの日付の数しだいで変わります。最後尾は今まで確認したもので最も後ろでCL列のあります。 >> 表Bの最後の日付は【AX列まで】にある、 > 上記CL列が型番名の場合、最後尾はDN列までのものがありました。
いやあのね、いいえじゃなくてｗ ･･･そこは実情に合わせて自分で変更してねって言ったつもり。 もしかして試すことすらしてない？ 前提が実情と違うという時点で試す価値もないと思った？ 数式のどこを修正したらいいかわからなかった？
日付を追加して追加して、最終的に表Bの「型番列」と「日付の最終列」がこれ以上右には行かんだろう、 というのがだいたいどのあたりになるかは見当つくよね？ 要は最終的に日付が最大何日分ぐらいになるのかってこと。 それがわかってれば、この列以降にはならないってところが容易に見当つくはず。
最大日数から考えて、これなら安心という列番号にそっちで変更してちょうだいってことっす。 回答した数式では暫定的に「型番列」をZ列まで、「日付最終列」をAX列までにしてるからそこを修正する。 全く見当もつかないんだったらExcel2013の最終列（XFD列）にしておけばいいんだけどね。
それにしてもなんかおかしいんじゃないの？ 型番列が「CL列」で、日付の最後が「DN列」だったら、日付の数はたったの28日分しかないことになる。 表Aと表Bの日付は全く同じという認識でいるんだけど違うの？
>（2）表Bの型番列は【C列からZ列の間】にある
自分で書いておいて何だけど、このC列からっていうのもさすがに実際はそんなことないはずｗ 日付を追加する前の一発目の表Bが、例えばだけどAA列より前から始まることなんかあり得ないんだったら 表Bの機種名を検索してる範囲をAA列からに変更すればいい（回答した数式ではC列になってるところね）。 そのへんのことがオイラにはサッパリわからないんで、 少なくとも表BがB列から始まることはないだろうと思ったからテキトーに書いただけｗ まあ、関数でやるのは難しいのかと書いてあったんで、そんなことないですよ〜という一例を提示しただけなんだけどね。 しかもオイラが理解してるのは、日付を追加していってどんどん右に移動していく表をどうやったら検索できるか、という一点のみｗ シートが全部で何枚あって、他に表が何個あって、とかいう情報がこの質問にどう関係するのか 全然わかってないし、わかりたいとも思わないｗ 他の処理をVBAでやってるんだったらこれもその流れでやればいいんでない？ とは思う。
ていうか、なんで表Aと表Bをタテ並びにできないのか、そこからして理解できてないｗ （帰って来た名無しのおっさん） 2015/03/20(金) 10:27
こちらのトピには、関数は素人だということもあり、あまりしゃしゃりでないようにしようと思いましたがあえて。
(はっしー)さんは現物のシートを目のまえにしているので、この項目は、ここに、こうなっているじゃないか、 なぜ自分の説明で理解できないんだ、と、そう思うかもしれませんが、回答側は、それが目に見えないんです。
だから、こうかな？ と思って、その想像のレイアウトを前提にコードなり式なりをアップする。 また、何をやりたいかということに対しても、「想像」して回答案をつくる。
でも、うまくいいかない。(はっしー)さんからは、「だめです、こうなってしまいます。」というレスがでる。
で、たとえば、元レスのほうで、当方が考えてコード化したロジックは、こうこう、と詳細をアップする。 そうすると (はっしー) さんからは、「まさしく、それをやりたい。よろしく」というレス。 これでは、先に進みません。
さらに、元スレで、相手にしている（βが想像した）レイアウトを無条件に作り出すコードをアップして それでつくってもらった表A,表Bに対してアップしたコードを実行してもらった。
その結果のレスは、「これでやればうまくきました。コードを実際の表のセルの位置関係に合わせれば可能なような気がします」
どうやって、その位置関係にあわせたらいいのでしょう？ （あとは、βがアップしたコードの微調整を(はっしー)さんが行うというのならそれでいいですけど） 実際のレイアウトとβが想像したのは、こことここが、このように異なると、そういうレスがほしいですね。
（β） 2015/03/20(金) 10:57
皆様、お世話になります。 この度は私の為に時間を割いて下さったにも関わらず、粗雑な対応をしたことをお許しください。 時間の無い中で一度に複数の提案を頂き、確認もせずにとりあえずコメントだけでもと思い、このような対応をしてしっまた次第です。 誠に申し訳ありませんでした。
先立って謝罪のみのレスですみません。このあと、ひとつずつ確認してみたいと思っていますのでしばしお時間を頂きたく、宜しくお願いします。
（はっしー） 2015/03/20(金) 21:49
皆様、お世話になります。 レス遅くなり、恐縮です。
さっそくですが1111様ご提案（2015/03/19(木) 11:17）の名前の定義を実際のシートへコピペしました。 （Sheet2、データ2は実際のものに置き換え）
データ1の参照範囲にこの式をコピペ→=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A$1:$A$1000),MATCH("データ2",Sheet2!$1:$1,0)-1)
この時に、【この数式に問題が見つかりました。修正するには[数式]タブの[関数の挿入]をクリックし、一般的な数式の問題の詳細を 確認する場合は[ヘルプ]をクリックしてください。】とのメッセージが表示されてしまいます。
また、「新しい名前」ダイアログで【範囲】はブックとなっていますが対象シート名（ご提案式でいうSheet2）に変更する必要等はありませんか？ （はっしー） 2015/03/21(土) 18:19
新規ファイルでここからコピペしてみたけど、問題なく定義できたよ。 そのメッセージは式が正しくない時に出るやつなんで、イコールの前に余計なものがついていないかや一番最後の）がぬけたりしていないか、確認してみて。
もしもSheet2やデータ2の文字を変更したなら！や”が抜けてないかも確認を。
どーしてもならないなら、エラーが出る貼り付けた式をここにコピペして。
あ、範囲は「ブック」にしておいてね。
（1111） 2015/03/22(日) 02:27
1111様、お世話になります。
>もしもSheet2やデータ2の文字を変更したなら！や”が抜けてないかも確認を。 "Sheet2"はそのまま " と " の間に文字列（シート名）を入れてて問題なしです。 Sheet2!も文字列を" "で囲んでいてダメでした。が、' 'に変更してうまくいきました。
次に別シートへ =IFERROR(VLOOKUP($A2,データ1,MATCH(B$1,データ1頭,0),0),VLOOKUP($A2,データ2,MATCH(B$1,データ2頭,0),0)) をコピーしてデータ1、データ1頭等を名前の定義で付けた名前に変更し、$A2、B$1を実際の表のセル位置に変更したところ#N/Aが表示されてしまい、 名前の前後に" "をつけたら#VALUE!になってしまいます。 何が違ってるんでしょうか？ 因みに、$A2は型番項目最上段、B$1は最初の日付部分に変更しています。
（はっしー） 2015/03/22(日) 03:28
> Sheet2!も文字列を" "で囲んでいてダメでした。が、' 'に変更してうまくいきました。
シート名が文字列でアドレスの方のシート名を「'」で囲った、ということでいいかな？ それならOK。
> 何が違ってるんでしょうか？
#N/Aなのは検索結果見つからない、ということで、型番か日付が見つからないまたはデータ範囲がおかしい、ということ。 #VALUE!は式がおかしい（名前の定期の名前に" "をつけたなら、式があってないので当然#VALUE!になる）ということ。
というわけで、名前の前後につけた「"」をはずして、型番か日付をチェック。 実際に元のデータにある型番をコピーしてもってきて、日付もコピーして実験してみて。 型番も日付もOKなら元データシート（俺のだとSheet2）を開いて「名前の管理」を表示。 名前をクリックして下に表示される参照範囲をクリックすると、現在その名前が参照している範囲がシート上で点線で囲まれるから、範囲が正しいかチェック。
以上の２つをやってみて。
（1111） 2015/03/22(日) 22:43
お世話になってます。
私事で大変申し訳ありませんが仕事でトラブルがあり、急な出張中です。 日にちがあいてしまって放置状態ですが、明日、ないし明後日には戻って再開できると思いますので、宜しくお願い致します。 （はっしー） 2015/03/25(水) 22:28
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.