Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Home : Excel 2016)
リストの設定
投稿日時: 20/08/17 08:12:27
投稿者: 桃太郎7

宜しくお願いします。
リスト選択の内容を更にリスト選択する。
 
問題
 sheet1でデータベース
   A  B   C  D   E  F   G  H
 1 品名  形式 
 2  A  1-001 1-002 1-003 1-004 1-005 1-006 1-007〜〜〜1-125 まであります
 3  B  2-001 2-002 2-003 2-004 2-005 2-006 2-007〜〜〜2-115
 4  C  3-001 3-002 3-003 3-004 3-005 3-006 3-007〜〜〜3-098
 5  D  4-001 4-002 4-003 4-004 4-005 4-006 4-007〜〜〜3-075
 6  E  5-001 5-002 5-003 5-004 5-005 5-006 5-007〜〜〜3-108
 7  F  6-001 6-002 6-003 6-004 6-005 6-006 6-007〜〜〜3-085
 8  G  7-001 7-002 7-003 7-004 7-005 7-006 7-007〜〜〜3-115
 9  H  8-001 8-002 8-003 8-004 8-005 8-006 8-007〜〜〜3-088
 10  I  9-001 9-002 9-003 9-004 9-005 9-006 9-007〜〜〜3-065
 11  J 10-001 10-002 10-003 10-004 10-005 10-006
 
希望する例題
 sheet2でリストによる選択
   G9    H9
  1 品名   形式
  2 D ▼  4-005 ▼ リストで品名と形式をそれぞれの▼で選択したいです。
  3 A    1-125
  4 G    7-115
  5 C    3-001
  6 E    5-004
  7 H    8-003
  8 B    2-001
  9 J    10-006 
 
  sheet2の入力セルを「品名」G9にリスト、「形式」H9にリストを挿入して入力したいです。
 
  宜しくご指導お願いします。
 
 
 

回答
投稿日時: 20/08/17 11:42:45
投稿者: Suzu

1. A2:A11 を選択し、「数式」-「名前の定義」 にて、
   新しい名前 ダイアログが出ますので、
   「名前」に【品名】、「範囲」ブックを指定し、参照範囲に =Sheet1!$A$2:$A$11 OK
 
2. A2 から 形式の最終セルまでを選択し「数式」-「選択された範囲」
   選択範囲から名前を作成 ダイアログが出ますので
   「左端列」のみ チェックし OK
 
3. Sheet2 の 品名を入力する範囲を選択し
   「データ」-「データの入力規則」
     入力の種類 に 【リスト】 空白を無視する・ドロップダウンリストから選択 にチェック
     「元の値」 に 【=品名】
 
4. Sheet2 の 形式を入力する範囲を選択し
    「データ」-「データの入力規則」
     入力の種類 に 【リスト】 空白を無視する・ドロップダウンリストから選択 にチェック
     「元の値」 に 【=INDIRECT(H9)】
 
※ 実際の品名に 「C」は無いでしょうけど
   名前の定義ので、「C」という名前をつける事はできません。
     (CはExcelの予約語として 使用できません)
   なので、「C」は名前は「C_」と自動で変更されますので、品名 C の場合の形式は抽出されません
 
 
追伸
 いままでの一連のご質問を拝見すると Excel で実装するより
 Access の方が簡単に実装できる件が多いです。検討されてはいかがでしょうか。

投稿日時: 20/08/17 15:48:50
投稿者: 桃太郎7

Suzuさん 早速のご回答ありがとうございます。
私、PCは未熟でExcelの簡単な関数を使って自分なりに楽しんでいます。
以前に「Access」を勉強しましたが難しくて挫折しました。
 
ご親切に教えて頂きました説明文をプリントしまして挑戦してみます。
ありがとうございました。

投稿日時: 20/08/18 08:58:41
投稿者: 桃太郎7

Suzuさん いつも大変お世話になっております。
教えてください。
ご回答通りにExcelに立ち上げてテストしています。
質問
1)1.は出来ましたが、2.の名前の定義にA2:U11までの範囲指定して
   「左端列」のみチエックし OKについて教えてください。
2)4.「データの入力規則」の「元の値」に【=INDIRECT(H9)】を入力すると次の
   エラーメッセージが表示されます「元の値はエラーと判断されます、続けますか?」です。
  現在の状況は、商品のリストの作成で商品表示されています。
  「形式」H9セルリストは出来ましたが無地表示です。
 
  ご面倒ですがご指導宜しくお願いします。

回答
投稿日時: 20/08/18 09:05:39
投稿者: Suzu

引用:
Excelの簡単な関数を使って自分なりに楽しんでいます。
以前に「Access」を勉強しましたが難しくて挫折しました。

 
使用者からしてみれば、AccessよりExcelの方が使用頻度は高いでしょうから
データやフォーマットを自由に扱い易いでしょう。
 
今回の様に、データのシートの他に、数式を配置したシートやブックを用意すると
その分のファイル容量も多く必要になります。
(セル一個一個に数式を入れるのでその分容量も多くなる。)
 
また、見せる為のファイルの開く際、印刷の際に時間を要する事が多くなります。
(今時、ファイル容量は気にしないかも知れませんが・・)
 
手違いで、1セルの数式を修正してしまうと、そのセルには希望でない計算結果結果が表示される事になりますが、間違いである事に気づきづらい。
 
Accessは列方向に、計算式を用意しておき、そこにデータを差し込む形
行方向の数式は必ず一致する(行によって数式を変える事はできない)
 
今回の様に定型の出力を、ある特定条件で抽出し出力するには向いています。
時間が許せば検討してみてください。

回答
投稿日時: 20/08/18 09:37:11
投稿者: Suzu

引用:
1)1.は出来ましたが、2.の名前の定義にA2:U11までの範囲指定して
   「左端列」のみチエックし OKについて教えてください。

 
何をお答えすれば良いのでしょうか?
 
「数式」-「定義された名前」の「選択範囲から作成」
選択範囲から名前を作成 ダイアログが出て、
以下に含まれる値から名前を作成 の 【上端行】【左端列】にチェックが入っているので
上端行のチェックを外し、【左端列】のみチェックされた状態で OK
 
 
確認として
「数式」-「定義された名前」の「名前の管理」で
 
名前の管理ダイアログが表示されます。
名前として、A、B、C_、D、E、F、G、H、I、J、品名 がある事を確認。
 
 
引用:
2)4.「データの入力規則」の「元の値」に【=INDIRECT(H9)】を入力すると次の
   エラーメッセージが表示されます「元の値はエラーと判断されます、続けますか?」です。
  現在の状況は、商品のリストの作成で商品表示されています。
  「形式」H9セルリストは出来ましたが無地表示です。

 
説明に間違いがあるのと、説明不十分でした・・
【=INDIRECT(H9)】 ではなく、【=INDIRECT(G9)】ですね。
 
でも、提示頂いた入力が、G9とH9に入れたいと言っていて、行番号が 1〜になっているので
齟齬防止の為
	A	B
1	品名	形式
2	D	4-005
3	A	1-125
4	G	7-115

とさせてください。
 
その場合 入力規則は、
品名 の元の値は、【=品名】
形式 の元の値は、【=INDIRECT(A2)】 → 元の値はエラーと判断されます が出てきますので 【はい】

投稿日時: 20/08/18 11:37:50
投稿者: 桃太郎7

Suzuさん お願いします。
 
何をお答えすれば良いのでしょうか?
「数式」-「定義された名前」の「選択範囲から作成」
>>「名前の定義」で行えばよろしいですか?
選択範囲から名前を作成 ダイアログが出て、
>>
選択範囲から名前を作成 ダイアログが分らない
以下に含まれる値から名前を作成 の 【上端行】【左端列】にチェックが入っているので
上端行のチェックを外し、【左端列】のみチェックされた状態で OK
 
確認として
「数式」-「定義された名前」の「名前の管理」で
>>商品には、A,B,D,E確認出来ました。
名前の管理ダイアログが表示されます。
名前として、A、B、C_、D、E、F、G、H、I、J、品名 がある事を確認。
>>
 
説明に間違いがあるのと、説明不十分でした・・
【=INDIRECT(H9)】 ではなく、【=INDIRECT(G9)】ですね。
 
でも、提示頂いた入力が、G9とH9に入れたいと言っていて、行番号が 1〜になっているので
>>sheet1にA列A2:A11に商品(A〜K)、B2:U11に形式を入力済みです。
齟齬防止の為

	A	B
1	品名	形式
2	D	4-005
3	A	1-125
4	G	7-115

とさせてください。
>>>上記の内容を希望しているのですが、中々出来ないです。
その場合 入力規則は、
品名 の元の値は、【=品名】
形式 の元の値は、【=INDIRECT(A2)】 → 元の値はエラーと判断されます が出てきますので 【はい】
>>形式の元の値は、=INDIRECT(A2)】にしましたがリストの中は空になっています。
 
 本当に未熟でご迷惑をお掛けしていますが、宜しくご指導ください。
確認します!A2に商品リストで、B2に形式リストでサンプルが出来ていますね。これを希望しています。
説明が難しくて不十分で申し訳ございません。

回答
投稿日時: 20/08/18 12:21:17
投稿者: んなっと

●Sheet2
 
   G    H
1 品名  形式
2  C  3-008
3  A  1-015
4  E  5-003
5  F  6-005
6  G  7-005
7  H  8-001
8  I  9-006
9  J 10-004
 
G2
入力規則→リスト▼→元の値:
=OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A:$A)-1)
H2
入力規則→リスト▼→元の値:
=INDEX(Sheet1!$B:$EZ,MATCH(G2,Sheet1!$A:$A,0),)
どちらも下方向・↓コピー
 
《参考》名前定義の方法もいいのですが、今後不具合が出る可能性もあるので読んでおいてください。
プルダウンリストの連動で一部だけプルダウンのリストがでない
https://www.moug.net/faq/viewtopic.php?t=79504

投稿日時: 20/08/18 15:33:51
投稿者: 桃太郎7

んなっとさん、早速ご回答頂きましてありがとうございます。
 テストしましたら以下の数式で成功しましたが、入力したい既存のファイルで上手く出来ません。
 
 テスト成功数式 sheet1にデータ、sheet2に入力 G2商品、H2形式
G2
=OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A:$A)-1)
H2
=INDEX(Sheet1!$B:$EZ,MATCH(G2,Sheet1!$A:$A,0),)
***********************************************
入力したい既存ファイルへの失敗数式 sheet1にデータ、材料明細に入力 G9商品、H9形式
G9
=OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$B:$B)-1)
H9
=INDEX(Sheet1!$B:$EY,MATCH(G9,Sheet1!$B:$B,0),)
結果報告
H9セルのリストの中が空です。
 
 いつもご親切にご指導頂きまして本当に感謝致しております。

回答
投稿日時: 20/08/18 16:02:32
投稿者: んなっと

引用:
テストしましたら以下の数式で成功しました

よくできました。頑張りましたね。
引用:
G9商品、H9形式

G2ではなくてG9だということで、再度書き込みます。
 
●Sheet1
 
    A    B    C    D    E    F    G    H
 1 品名  形式                        
 2  A  1-001  1-002  1-003  1-004  1-005  1-006  1-007
 3  B  2-001  2-002  2-003  2-004  2-005  2-006  2-007
 4  C  3-001  3-002  3-003  3-004  3-005  3-006  3-007
 5  D  4-001  4-002  4-003  4-004  4-005  4-006  4-007
 6  E  5-001  5-002  5-003  5-004  5-005  5-006  5-007
 7  F  6-001  6-002  6-003  6-004  6-005  6-006  6-007
 8  G  7-001  7-002  7-003  7-004  7-005  7-006  7-007
 9  H  8-001  8-002  8-003  8-004  8-005  8-006  8-007
10  I  9-001  9-002  9-003  9-004  9-005  9-006  9-007
11  J 10-001 10-002 10-003 10-004 10-005 10-006 10-007
 
●Sheet2
 
    G    H
 8 品名  形式
 9  C  3-008
10  A  1-015
11  B  2-020
12  F  6-005
13  G  7-005
14  H  8-001
15  I  9-006
16  J 10-004
 
G9
入力規則:リスト▼:元の値:
=OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A:$A)-1)
H9
入力規則:リスト▼:元の値:
=INDEX(Sheet1!$B:$EZ,MATCH(G9,Sheet1!$A:$A,0),)

回答
投稿日時: 20/08/18 16:08:14
投稿者: んなっと

まだスレッドを閉じないでくださいね。

投稿日時: 20/08/18 20:11:48
投稿者: 桃太郎7

んなっとさん、いつも大変お世話になっております。
報告します!!G9の数式、H9の数式が完成しました。
G9
入力規則:リスト▼:元の値:
=OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A:$A)-1)
H9
入力規則:リスト▼:元の値:
=INDEX(Sheet1!$B:$EZ,MATCH(G9,Sheet1!$A:$A,0),)[/quote]
 
ずに乗って最後に追加の質問をします。
選択された「形式」には指定指数がありまして、選択された形式の指定指数を
別のセル「O9」に自動入力がしたいです。VLOOKUPで出来ると思っていましたが上手く出来ません。
 
問題例  G9、H9、O9のセルが入力セルです。
  sheet1でデータベース
   A   B   C    D   E   F  G    H   I 〜〜250迄
  1 品名  形式  指定数値    指定数値    指定数値    指定数値
  2  A  1-001 0.306  1-002 0.377  1-003 0.447  1-004 0.518 
  3  B  2-001 0.222  2-002 0.499  2-003 0.888  2-004 1.04 
  4  C  3-001 0.711  3-002 1.33   3-003 2.01   3-004 2.83 
  5  D  4-001 6.92   4-002 1.12   4-003 1.36   4-004 2.16 
  6  E  5-001 12.9   5-002 9.36   5-003 13.4   5-004 0.813 
  7  F  6-001 17.2   6-002 0.652  6-003 17.1   6-004 0.939 
  8  G  7-001 0.419  7-002 0.621  7-003 31.5   7-004 40.2 
 
 形式を選択した品番の指定数値を別のセル「O9」へ入力する。
 例題 
  品名  形式  指定数値      
  A   1-001  0.306 ・・・・・「O9」に入力     
  D   4-003  1.36  ・・・・・「O10」
  B   2-002  0.499  ・・・・ 「O11」
  F   6-002  0.652   ・・・ 「O12」のように形式を選択したら自動的に指定指数を入力
 この質問形式のルールを知りませんので、ルール違反であれば謝りますのでご指摘、ご指導お願いします。
 

回答
投稿日時: 20/08/18 21:39:45
投稿者: んなっと

Sheet1が大幅に変わってしまいましたね...
 
    G   H I J K L M N   O P
 8 品名  形式           指定  
 9  C 3-002           1.33 4
10  A 1-002          0.377 2
11  B 2-003          0.888 3
12  F 6-004          0.939 7
13  G 7-001          0.419 8
 
O9
=IFERROR(INDEX(Sheet1!$1:$1000,P9,MATCH(H9,INDEX(Sheet1!$1:$1000,P9,),0)+1),"")
下方向・↓
P9
=MATCH(G9,Sheet1!A:A,0)
下方向・↓
 
 
どうも質問文に使われる用語が不正確です。
別のセル「O9」へ入力する ではなくて、
別のセル「O9」へ表示する ですよ。
似たような間違いを過去にもしているので、これはうっかりミスではありません。
 
例えば知り合いの高校生で、ある程度パソコンの基礎がわかっている方はいませんか?
もしいたら、その方に添削してもらってから書き込んでみてはどうでしょうか。
過去の質問でも、その高校生に回答を読んで実行してもらえば
簡単に解決できたと思われるものもあります。
あなたがやるから「あー、うまくいかない」となって状況が悪化する。
 
お互いに無駄な時間を省けたらいいですね。

投稿日時: 20/08/19 14:42:44
投稿者: 桃太郎7

んなっとさん、ご回答ありがとうございます。
>>Sheet1が大幅に変わってしまいましたね...
勝手にsheet1を変更しまして申し訳ございません。
  新sheet1でデータベース
   A   B   C    D   E   F  G    H   I 〜〜250迄
   品名  形式  指定数値    指定数値    指定数値    指定数値
    A  1-001 0.306  1-002 0.377  1-003 0.447  1-004 0.518 
    B  2-001 0.222  2-002 0.499  2-003 0.888  2-004 1.04 
     C  3-001 0.711  3-002 1.33   3-003 2.01   3-004 2.83 
     D  4-001 6.92   4-002 1.12   4-003 1.36   4-004 2.16 
  上記の通りsheet1を変更しましたら! C列、E列、G列、I列と単純に列追加しました。
G9
入力規則:リスト▼:元の値:  <異常なし>
=OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A:$A)-1)
 H9
入力規則:リスト▼:元の値: <表示が空です>
=INDEX(Sheet1!$B:$EZ,MATCH(G9,Sheet1!$A:$A,0),)[/quote]
 * G9は異常なし、H9の形式表示が消えた
 * O9  【表示されませんでした】
=IFERROR(INDEX(Sheet1!$1:$1000,P9,MATCH(H9,INDEX(Sheet1!$1:$1000,P9,),0)+1),"")
   表示されませんでした。
従来のsheet1でテストしましたら、異常はないですが変更した新sheet1では上記の症状です。
 * P9は使用している列ですから、数式から削除してください。勝手言ってすみません。
****** 素晴らしいです **** こんな感じが希望です *****
    G   H I J K L M N   O P
 8 品名  形式           指定  
 9  C 3-002           1.33 4
10  A 1-002          0.377 2
11  B 2-003          0.888 3
12  F 6-004          0.939 7
13  G 7-001          0.419 8
O9
=IFERROR(INDEX(Sheet1!$1:$1000,P9,MATCH(H9,INDEX(Sheet1!$1:$1000,P9,),0)+1),"")
下方向・↓
P9
=MATCH(G9,Sheet1!A:A,0)
下方向・↓
>>>
どうも質問文に使われる用語が不正確です。
別のセル「O9」へ入力する ではなくて、
別のセル「O9」へ表示する ですよ。
似たような間違いを過去にもしているので、これはうっかりミスではありません。
>>申し訳ありませんでした、PC知識不足でご迷惑をお掛けしています、以後注意をします。
例えば知り合いの高校生で、ある程度パソコンの基礎がわかっている方はいませんか?
>>すみません・・知り合いにはいませんので、つい頼りにしまして申し訳ないです。
もしいたら、その方に添削してもらってから書き込んでみてはどうでしょうか。
過去の質問でも、その高校生に回答を読んで実行してもらえば
簡単に解決できたと思われるものもあります。
>>質問する場合は、十分気をつけてご依頼をします。
  今後とも宜しくお願いします。

回答
投稿日時: 20/08/19 15:13:31
投稿者: んなっと

数式が無駄に長くなりますが、
 
    G   H I J K L M N   O
 8 品名  形式           指定
 9  C 3-002           1.33
10  A 1-002          0.377
11  B 2-003          0.888
12  F 6-004          0.939
13  G 7-001          0.419
 
O9
=IFERROR(INDEX(Sheet1!$1:$1000,MATCH(G9,Sheet1!A:A,0),MATCH(H9,INDEX(Sheet1!$1:$1000,MATCH(G9,Sheet1!A:A,0),),0)+1),"")
下方向・↓

引用:
上記の通りsheet1を変更しましたら! C列、E列、G列、I列と単純に列追加しました。

いったん解決した入力規則の数式も、当然使い物にならなくなります。
 
Sheet1は最初の質問の配置のまま変更せず、新たにSheet3を作成して、
O9の数式はそのSheet3を参照するのがいいと思います。
ただ、説明しても無駄でしょうからやめておきます。
 
ずっと言いたかったことも書き込めましたし、最初の質問も解決したようなので
スレッドを閉じて結構ですよ。

投稿日時: 20/08/19 17:15:33
投稿者: 桃太郎7

んなっとさん、いつも大変ご親切にご指導くださりましてありがとうございます。
>>
Sheet1は最初の質問の配置のまま変更せず、新たにSheet3を作成して、
O9の数式はそのSheet3を参照するのがいいと思います。
 
出来ました!! 感激!感謝〜感謝です。
 
今回のご回答で、数式sheet1はそのままで、sheet3に変更して成功しました。
いつもいろいろとご指導頂いていましたが、今回は特に勉強になりました。
今後とも宜しくお願いします。