『一番いい関数は?』（はつこい） sampleデータがうまくつくれないので、リアルデータの一部を載せます。 入力値 (B列) 表示させたい車種 (C列)入力値 (D列)表示させたい車種 A列 B列 C列 D列 ・・・・K列 1 50 CDI12V50Y/M 5NO SR125 2 CDI12V50Y/M G STD 4DD SR125 3 CDI12V50Y/M G DX 4WP SR125 4 CDI12V50Y/M B STD 3YX ZEAL250 5 HA02 CDI6V90-2段ｻｽ ・ ・ ・ 342行 したいこと 入力値であるA1に50を入れたら、B1のCDI12V50Y/Mと表示させたくて、C1、E1、G1、と入力値がJ列まであります。 ただ、1つの入力値に対して、表示させたい車種が1つならOKなのですが、A2〜A4のように、入力値が入っていないのはA1の50の車種の種類がいくつかに分かれるという意味になります。 342行まで、1つの入力値に対して対応車種1つとか、入力値1つに対して、対応車種が2種類以上のものが混合しています。 私の関数の知識は今回のこの表に関して言うと、C列より右にくるものをすべてコピーして、A列343行目に貼り付け、vlookupにすることを考えてたという初期的ものですが、車種が複数に亘ってあるものがこれだと対応できません。 私が今望むことがどこまで関数で対応できるのかもわかりませんし、何か方法があるとしても、私の知識が上の状態です。 そこで、 1.私の希望してることはネスト関数にすれば、対応できるものなのか。 2.対応できたとしても、どのような関数式になるのか 以上をご教示いただきたく、よろしくお願いします。 ＜ 使用 Excel：Excel2013、使用 OS：Windows7 ＞ ---- ＞入力値であるA1に50を入れたら、B1のCDI12V50Y/Mと表示させたくて、C1、E1、G1、と入力値がJ列まであります。 １．CDI12V50Y/Mとかの対応車種名群は、どこにあるんですか？ ２．また、50とどう紐付される様になっているんですか？ ３．50の対応車種は同じ場所に一塊りになっているんですか？ 　　それとも散らばっているんですか？ ４．A列用とC列用は見に行くセル範囲(列?）が根本的に異なるのですか？ 　　この記述を読むと「元々は同じ列には無い」と云う印象を受けますけど・・・ 　　　↓ ＞C列より右にくるものをすべてコピーして、A列343行目に貼り付け、 （半平太） 2014/11/01(土) 17:05 ---- 半平太さんご回答、ありがとうございます。 サンプルデータとして載せたリアルデータは、実際に入れたいデータを作るための基礎のデータといったらいいんでしょうか? 完成させたい仕入表が別にあります。 それは、車種名を簡易入力させて(上のサンプルデータのA1にある50)、正式名称は、CDI12V50Y/Mのように表示させたかったのです。 そこで私の知識で思いついたのがVLOOKUPでしたが、50と入れた時に表示させたい正式名称が1種しかないのなら、いいですが、50という種類はサンプルデータですと4種あるので、それでもうお手上げということと、私がVLOOKUPを組むのに、列番号を2というやり方しかしらないので、(列番号を複数指定する方法があるのかないのかもわからない)C列より右にあるデータを無理やり縦にはりつけるしかわからないというのがあります。 以上の説明はご質問の1〜4までの答えになったでしょうか? （はつこい） 2014/11/01(土) 21:05 ---- ↓はどうなりましたか？ [[20141030085312]]『vlookupのテーブル』（はつこい） まぁ、A列が追加になって居るので話は変わってきている様ですが。 ＞完成させたい仕入表が別にあります。 の完成例を書いてもらうのが良いかもしれませんね。 　 （HANA） 2014/11/01(土) 21:15 ---- HANAさん すみませんでした。 えーと、お応え頂いた内容が私の知識ではまったくお手上げ状態でした。 教えて頂いたIFERRORのデータを関数boxのどこにどう入れたらいいのかを聞くのもためらってしまいました。 また、別記載して頂いたindexも知らないので、まずはそこから調べないと、というのもあり、こんなことでは、とりあえず列番号を2として縦貼り付けをしたほうが今の私のレベルだということで早いと判断しました。 で、今日のデータですが、詳しく書いたつもりが、第三者からしたらまったく訳のわからない表になってたようです。 例 (今現在の)バイク仕入表 A列 B列 C列 D列 1 車種 価格 仕入先名 仕入日 ... 2 CDI12V50 50,000円 ●▽株式会社 11.1 という表を完成されるのに、問題となっているのが車種の表示で、仕入の紙を見ながら手入力するのですが、車種の書き方が人によって違ったりしているので、本来なら同じ車種の筈なのに、別の書き方のせいで、私がそれがわからず、書かれた通りに入力しているために、仕入表を見たい方が困ってしまっています。 そこで、最低限、車種名に表示させたいというデータの元をバイクがわかっている営業さんに作ってもらいました。 そしたらそのデータの元を利用して、VLOOKUPができるのでは?とおもったからです。 私の頭の中で(勝手に)想像したVLOOKUPをつかったバイク仕入表(できるできないは別です) A列 B列 C列 D列 .... 1 入力値 車種 価格 仕入先 2 50 CDI12V50 50,000 ●▽株式会社 3 40 FORSIGHT250 20,000 ××有限会社 私の頭の中の表のB2の車種を表示させるためにA2の50を入れる欄を挿入させたわけですが、A2の50という値を入力するための元データが、今日最初にサンプルデータとして書いたものになります。 (意味は通じていますでしょうか?) しかし、営業さんが作ってくれた元データで困ってしまったのは、入力値がA3の40とした時の車種名が1種類しかなければ、私の頭の中でのVLOOKUPが完成しますが、50としたときは車種が4種類あるので、行き詰ってしまったことです。 後は、元データをメーカーごとに横列で作成してくれているので、(表としては見やすい) 私がVLOOKUP関数を組む時の列指定は、今の私の知識上 2としかできないので、C列以降のデータを縦１列に貼り付けさせるしかないわけです。 以上で話が全部通じましたでしょうか? すみません、ページ更新させたら、表の空白が詰まってしまって見にくいですが、編集しても改善できませんので、考慮していただけたら幸いです。 (はつこい） 2014/11/01(土) 22:15 ---- A2に「50」を入力したとしますよね？ すると、困っておられるように 1 50 CDI12V50Y/M 5NO SR125 2 CDI12V50Y/M G STD 4DD SR125 3 CDI12V50Y/M G DX 4WP SR125 4 CDI12V50Y/M B STD 3YX ZEAL250 の4件が該当するのですよね？ それで、B2にはこのうちのどれが表示されれば良いかは　どうやって決めるのですか？ ご説明では「CDI12V50」が表示されることになって居ますが。 　　当初ご説明の一覧に「CDI12V50」はないので 　　なんで「CDI12V50」になるのかわからないですが。 たぶん、最初に載せておられる表より　わかりやすい表を想定されて それを使った希望結果図を載せて下さったのだと思います。 そのデータがどのようなものかわからないので、その新しい表（データ）と それを使って、A2に「50」と入力したとき B2以降にどの様に表示されるのが良いのかの完成例をもう一度書いてみてもらえますか？ 　 （HANA） 2014/11/01(土) 23:12 ---- おはようございます。 HANAさん、つたない知識の私のお相手をして下さり、ありがとうございます。 A2欄50を入力した時の表示車種名はCDI12V50Y/M、以下3種類でした。(勝手にY/Mを省略していたのを今気づきました。すみません。) B2にはこのうちのどれが表示されれば良いかは　どうやって決めるのですか？ これは、入力値のA1に50と入力されたら、4種類がブルタウン形式で選択できるような関数があればそれを組む事で解決できたら、という結果を思っていました。 (もっといい認識方法があれば、教えて頂きたいです。) 実際、営業さんの元データによると、入力値1つに対して、表示させたい車種名は、最大40種類までありました。となると、選択プルダウンから探し出すにも一苦労という思いはあります。 私の完成希望結果仕入表 A列 B列 C列 入力値 表示させたい車種名 仕入価格 1行目 MF03 CDI12V50Y/M 都度手入力 CDI12V50Y/M G STD CDI12V50Y/M G DX CDI12V50Y/M B STD 表示させたいB列は、MF03を入力出来た時に選択形式になれば、という思いがあります。 2行目 AA01 LITTLE CUB'00UP 都度手入力 2行目はAA01の入力値に対して、表示させたい車種名が1つだとして、これでOKです。 以下、3行目から342行目まで入力値1つにたいして表示させたい車種名が十数種類あるものと1:1のものと混合ですが、A1の例のように、仕入完成表を作るための営業さんの元データ一覧表には、MF03といれたらそれに対応する種類が一塊で載せてあります。 そして、以下に仕入表を作るための営業さんに作ってもらった元データを載せようとおもいましたが、テキスト整形のルールがいまいち頭にはいっていないのか、ズレまくりで上手くできません。 A列 B列 ホンダ 入力値 表示させたい車種名 MF03 CDI12V50Y/M 〃 CDI12V50Y/M G STD 〃 CDI12V50Y/M G DX 〃 CDI12V50Y/M B STD このAB列のように、CD列には、ヤマハ、EF列にはスズキ、GH列にはカワサキ&その他とメーカごとに入力値と車種名が載っており、入力値1つに対して車種が複数だったり、1つだったりというのは全メーカーにあります。 VLOOKUPを組むのに列番号が2しかできないので、ホンダの最終行の続きにC列より右のメーカーを貼り付けてこれを解決しようとしましたが、もっとスマートな方法があれば、というのが、10/31の最初の質問でしたが、既出の通り、お答えて頂いたものは私の知識には手に負えないものでしたので、とりあえず諦めました。 （はつこい） 2014/11/02(日) 10:34 ---- ＞AB列のように、CD列には、ヤマハ、EF列にはスズキ、GH列にはカワサキ&その他とメーカごとに入力値と車種名が載っており これは当初から言っておられるように、C:D列はA:B列の続きに E:F列はさらにその続きに・・・と貼り付けて、一つの表にして下さい。 シート名：一覧　のA:B列に　一覧表を作ってください。 入力シートのA2セルに「MF03」（←一覧表のA列にあるデータのうち一つ）を入力 B2セルの入力規則で「リスト」を選び　元の値に =OFFSET(一覧!$B$1,MATCH(A2,一覧!$A:$A,FALSE)-1,,COUNTIF(一覧!$A:$A,A2)) の式を設定。 B2セルの入力規則を開くと、候補が表示されます。 ＞AA01の入力値に対して、表示させたい車種名が1つだとして、これでOKです。 とのことですが、入力規則を設定するので　一つだけ表示される候補の中から 選択してもらう事になります。 　 （HANA） 2014/11/02(日) 16:07 ---- HANAさん、ありがとうございます。 まずは、書いて頂いた通り、 営業さんの作ってくれた一覧を縦ab列に収め、このシート名を一覧としました。 次に、完成させたい仕入表シートのa2セルにMF3と入れ、すぐ隣のb2セルより入力規則で下記の式 =OFFSET(一覧!$B$1,MATCH(A2,一覧!$A:$A,FALSE)-1,,COUNTIF(一覧!$A:$A,A2)) をそのまま入れようとしました。(コピペ) そうすると、「データの入力規則に他のワークシートまたは、ブックへの参照は使用できません」となりました。 ちなみに、仕入表と同じシート内の右側にこの一覧を持って来て試してみましたが、同じエラーメッセージが返ってきました。 どのように改善したらいいでしょうか? （はつこい） 2014/11/02(日) 18:50 ---- 使用 Excel：Excel2013 ってのは合ってますか？ 表を同シートに持ってきて、設定した入力規則の式は 参照先を同シートのセルに変更しましたか？ 数式に「！」が入っていると シート間参照とみなされるので もう一度確認してみて下さい。 　 （HANA） ---- 使用 Excel：Excel2013ってのは合ってますか？ あ、すみません。今は自宅からなので、2007でした。 エクセルのバージョンダウンで使用不可ですか? （はつこい） 2014/11/02(日) 19:27 ---- 2007はどうだったかな。。。？ でも、駄目だったんなら 駄目なんでしょう。 因みに、私は2010です。 でも、同じシートに表を作れば 大丈夫と思います。 たとえば、入力をC列にするとして D2の入力規則に =OFFSET($B$1,MATCH(C2,$A:$A,FALSE)-1,,COUNTIF($A:$A,C2)) の式です。 ひとつ忘れてましたが、A列の記号は、先頭行だけでなく 続くセルにも入れてください。 MF03の対応が4件あったら、それらのA列に MF03を入れてください。 　 （HANA） ---- 同シートにしたら、「元の値はエラーと判断されます。続けますか?」となり、はい、を押したら、プルタウンボタンのみ表示になりました。 2013がウチにはないので、火曜日までどうなるかわかりませんが、やってみます。 ありがとうございます。 MF03の対応が4件あったら... 了解です。 （はつこい） 2014/11/02(日) 20:38 ---- C2セルに、A列に入力されているデータのうちの一つ（MF03）が 入力されていますか？ コピーして、C2セルに貼り付けてから D2セルの▼を 開いてみて下さい。 「元の値はエラーと判断されます。続けますか?」 は、表示されてもそのまま設定して下さい。 （HANA） ---- HANAさん 明日、2013で早速試してみますね。 ところで、今回は、結果的にプルダウンから選択という形で提案して頂きましたが、HANAさんが一通り私のしたいことを考えた時、何か他によさげな方法はありますか? 入力値1つに対して複数の車種名がなければ文句なし?にvlookupでよかったのでしょうか? 他のもっといい関数はあったのでしょうか? （はつこい） 2014/11/03(月) 19:21 ---- ひとつしかないなら、やはり VLOOKUP関数を使うと思います。 車種名（？）が決まれば、その他の情報は決まると思いますので そこには、VLOOKUP関数を使います。 候補を挙げて一つを選ばないといけないので 「関数で」と言われると、思い付きません。 色々仕組みを変えて良いなら 　たとえば、枝番号の様なものを決めておき 最終的にそれで呼び出す 結局、一対一にして呼び出す感じです。 方法はあるかもしれませんが。 （HANA） ---- 元の値の式の入れ方がまずいのか一覧がでませんでした。(多分、式の言葉とかセルを変えないといけない) ただ、入力値に対して車種名が50種類くらいあると、選択するのだけでも大変です。 仕組みをまったく変えていただいてもかまいませんので、何かいい方法がないでしょうか? 私が思いついたのがVLOOKUPくらいでしたので、営業さんに入力形式と表示させたい車種ということで一覧を作ってもらいましたが、この指示は無駄だったかもしれませんが、とにかく、関数に限らず、エクセルの機能でなにか賄えそうなものがあれば、どんな機能でもいいです。 いい方法はないでしょうか? （はつこい） 2014/11/06(木) 18:00 ---- ＞入力値に対して車種名が50種類くらいあると、選択するのだけでも大変です。 もう一度お伺いしますが この中のどれを選ぶかは、どうやって決まるのですか？ そもそも、入力値が同じで車種が違うものは　何が違うのですか？ 入力値は営業の人に指示してもらうんですよね？ でしたら、車種ごとに別々の入力値を作って それを指示してもらえば良いんじゃないかと思いますが。 一つずつとまではいかなくても、もう少しグループに分けてもらうとか。 たとえば、載せて下さっているサンプルデータの 入力値 表示させたい車種名 MF03 CDI12V50Y/M MF03 CDI12V50Y/M G STD MF03 CDI12V50Y/M G DX MF03 CDI12V50Y/M B STD だと、「MF03」で共通なのは「CDI12V50Y/M」ですよね？ これを表示させるだけなら、VLOOKUP関数で簡単だと思います。 その後ろの　最初は　「無し」「G」「B」のいずれか その後ろは　「無し」「STD」「DX」のいずれか の様に情報を整理してみるのはどうですか？ 「G」「B」「STD」「DX」なんてのは、それぞれ意味があると思います。 その組み合わせで50種類　とかになってるんじゃないですか？ 　 （HANA） 2014/11/06(木) 22:42