[[20211025211407]]　『名前の自動入力』（愛子）　ページの最後に飛ぶ

『名前の自動入力』（愛子）

お世話になります。

ボランティア活動（全員登録済み）でビルの管理を1階から7階まで、各階担当
者を毎回5名を選びます。各担当者は、毎回変わります。

その表を作成する際にいちいち手入力をするのは、時間がかかり煩わしいので、
下記に基づき自動入力が出来るようにしたいのです。

シート１のa1に項目名“担当フロアー”a2、は１階　a3は２階‐‐‐～a8は7
階と縦に記載しています。
シート１のb2からf8迄名前を入力しますが、いちいち手入力するには時間がか
かります。
そこで、
シート２に、a1に項目名“名前の見出し”、a2からa45迄縦に、名前の見出しとして、あいうえお‐‐‐わ行迄記載しています。
B２からf45までは、縦に、あいうえお順に名前を登録しています。

例えば、シート１のb2に青山春香と入力する際に、青と一文字を入力すると
シート２の表の中から、あ行で青から始まる文字をシート１にリストで抽出し
て、青山春香があればそこをクリックすれば自動入力出来る方法を教えて頂け
ないでしょうか？

＜使用 Excel：Excel2013、使用 OS：Windows8 ＞

 ＞シート１のb2に青山春香と入力する際に、青と一文字を入力すると
 ＞シート２の表の中から、あ行で青から始まる文字をシート１にリストで抽出して、
 ＞青山春香があればそこをクリックすれば自動入力出来る方法を教えて頂けないでしょうか？
 セル編集中の文字列が元では、数式、マクロどちらも
 動かないと思いますよ。
 ですので、お望みの自動入力方法は出来ません。

（tkit） 2021/10/26(火) 09:53

tkit様

お返事頂き有難うございました。

＞セル編集中の文字列が元では、数式、マクロどちらも
＞動かないと思いますよ。
はい、意味がわかります、その通りですね。

（皆様からのお返事が一切ないので、再度読み返しながらどこが
伝え間違えているのかと読み返したなかで、音読み、訓読みがある為
難しいのかと思ったり他に原因があるのかなあと思っていました。）

１、では、編集中の箇所ではなく、どこか余白に”あ”と入力しましたら
シート２のあ　行からのすべての名前をリストボックスで閲覧でき、入力欄にセルポインターをあて
該当する名前をクリックすれば入力欄に自動入力される方法はないでしょうか？

２、または、上記にこだわらずに手入力をせずに自動的に名前が入る方法を教えていただけますか？

よろしくお願いします。
（愛子） 2021/10/26(火) 11:44

https://hirachin.com/excel-indirect/

 1については、上記のようなことをなされば可能かと思います。
 イメージが違うのであれば、違う部分を明確化して再度質問いただければ精度が上がるかと。
（*） 2021/10/26(火) 13:11

＊さま、有難うございます。

こんなことが出来るのですね、私としましては、驚きです。

ただ、これだと商品の選択とオプションの選択が一箇所のセルで行われます。

ｂ２からｆ８までを全て入力しなければいけません。

よろしくお願いします。

（愛子） 2021/10/26(火) 14:10

 >ｂ２からｆ８までを全て入力しなければいけません
 よくわかりませんが、全てのセルに同じ人物の名前を入力したいのであればどこかのセルを参照させれば良いのでは？
 すべて入力というのはどういうことを想定されているのでしょうか。
（*） 2021/10/26(火) 15:46

横からですが、

入力規則を使う一例です。
こんな感じだとして、
Sheet2

	A	　B	　　C	　　D	　　E	　　F			
1　名前の見出し								
2	あ	青山春香　秋元	　秋田	　新井	　安藤			
3	い	井口	　石原	　伊藤	　稲垣	　位田			
4	う	上木	　上田	　宇治原　内田	　内野

 >B２からf45までは、縦に、あいうえお順に名前を登録しています。									
 名前定義は完了しているのですよね？

 まだであれば、
 1)A2（あ）からF45まで範囲選択し［数式］タブー「定義された名前］内の［選択範囲から作成］をクリック									
 　　　□左端列(L) にチェックを入れて［OK］。									
       ※□上端行(T)のチェックは外します。									
 これで範囲の各行に名前が付きました。

 2)次にA1:A45まで範囲選択して今度は□上端行(T)にチェックを入れ［OK］。

ここから入力規則

Sheet1のB2:F8をドラッグ範囲選択してデータの入力規則で種類を「リスト」にして「元の値」に
=IF(COUNTIF(名前の見出し,B2)=0,名前の見出し,INDIRECT(B2))

B2:F8をドラッグ範囲選択してから入れているのでその範囲全てで入力できます。

▼で「あ」を選び、もう一度▼をクリックすると「あ」の人の候補が出るので選ぶだけです。
▼で選び直し可です。

（檸檬） 2021/10/26(火) 16:08

＊さま
有難うございます。

＞全てのセルに同じ人物の名前を入力したいのであればどこかのセルを参照させれば良いのでは？
＞すべて入力というのはどういうことを想定されているのでしょうか。

シート１のb2からf8迄は、１階から７階まで（すべてと言いました）を別人の担当者名を入力します。
　同じ担当者はいません。

よろしくお願いします。

（愛子） 2021/10/26(火) 17:39

檸檬さま

今から会議に出席しますので、
終わってから順次行っていきます。

有難うございます。
（愛子） 2021/10/26(火) 17:43

檸檬さま

データの入力規則で入力値の種類をリストとしまして元の値に

=IF(COUNTIF(名前の見出し,B2)=0,名前の見出し,INDIRECT(B2)) 　を入れました。

上記名前の見出しを実際の見出しシート１のa1名前の見出しを（担当フロアー）に置き換えましたら

”指定した名前の範囲は、見つかりません”と出ました。

何を修正すればよろしいでしょうか？
（愛子） 2021/10/26(火) 22:45

訂正です、上記関数に、名前の見出しは、何を入れるのですか？

（愛子） 2021/10/26(火) 22:59

 >実際の見出しシート１のa1名前の見出しを（担当フロアー）に置き換えましたら
こちらでもSheet1!A1は担当フロアーにしてあります。

見直したら、Sheet2!$A$2:$A$45には名前をつけなくてもよかったので

 >2)次にA1:A45まで範囲選択して今度は□上端行(T)にチェックを入れ［OK］。
は忘れてください。
申し訳ありませんが、余分な名前は必要ありませんので
「名前の管理」画面で「名前の見出し」を選んで削除しておいてくださいm(_ _)m

Sheet1のB2:F8をドラッグ範囲選択して、今設定してあるデータの入力規則をいったん全部クリアして（置き換えますか？と出るかも）

入力規則－「リスト」－「元の値」に

 =IF(COUNTIF(Sheet2!$A$2:$A$45,B2)=0,Sheet2!$A$2:$A$45,INDIRECT(B2))
としてみてください。

一応2013の場合

 https://excel-plus.jp/2015/10/26/3722			
「数式」タブを選択し			
「定義された名前」グループの			
「名前の管理」ボタンを選択したら			
「名前の管理」画面が開きます。			
そこで名前と名前の範囲が確認できます。編集や削除もできます。

（檸檬） 2021/10/27(水) 01:58

檸檬　様
おはようございます。朝早くから申し訳ございません。

下記１、から３を順次実行しました結果、シート１で▼をクリックしますと
=IF(COUNTIF(Sheet2!$A$2:$A$45,B2)=0,Sheet2!$A$2:$A$45,INDIRECT(B2))
の式が入っています。？　どこが違うのでしょうか？

２、まだであれば、

 1)A2（あ）からF45まで範囲選択し［数式］タブー「定義された名前］内の［選択範囲から作成］をクリック

 □左端列(L) にチェックを入れて［OK］。

１、申し訳ありませんが、余分な名前は必要ありませんので
「名前の管理」画面で「名前の見出し」を選んで削除しておいてください

　Sheet1のB2:F8をドラッグ範囲選択して、今設定してあるデータの入力規則をいったん全部クリアして
　（置き換えますか？と出るかも）
３、シート１で、
　　入力規則－「リスト」－「元の値」に

 　　=IF(COUNTIF(Sheet2!$A$2:$A$45,B2)=0,Sheet2!$A$2:$A$45,INDIRECT(B2))
　　　としてみてください。

（愛子） 2021/10/27(水) 05:18

色々確認をしている時に、▼をクリックすると関数が見えるのは、下記の点に問題があるのでしょうか？
参考になるかどうかわかりませんが。

データ→データの入力規則には、下記、式が入っていますが、（上記コピー、貼り付けをしました。）

=IF(COUNTIF(Sheet2!$A$2:$A$45,B2)=0,Sheet2!$A$2:$A$45,INDIRECT(B2))

▼をクリックしますと上記式の　真ん中の　Ｂ２）＝０、のカンマがありませんでした。
カンマがない為にならばの次の式が生かされないのでしょうか？　

（愛子） 2021/10/27(水) 05:35

 =IF(COUNTIF(Sheet2!$A$2:$A$45,B2)=0,Sheet2!$A$2:$A$45,INDIRECT(B2))
 そうですね。
でも、'をとってみると他のブックを参照できませんと出るので他にも原因があるのかもしれませんが
今日は仕事に行きますのでこのあとお返事ができません。
もう一度やり直してみてください。
コピペでなぜ'が抜けるのか不思議ですが。。。

1)Sheet2のA2（あ）からF45まで範囲選択し［数式］タブー「定義された名前］内の［選択範囲から作成］をクリック

 　　　□左端列(L) にチェックを入れて［OK］。									
       ※□上端行(T)のチェックは外します。									
 これで範囲の各行に名前が付きました。

2）ここから入力規則
Sheet1のB2:F8をドラッグ範囲選択してデータの入力規則で種類を「リスト」にして「元の値」に
=IF(COUNTIF(名前の見出し,B2)=0,名前の見出し,INDIRECT(B2))
B2:F8をドラッグ範囲選択してから入れているのでその範囲全てで入力できます。
▼で「あ」を選び、もう一度▼をクリックすると「あ」の人の候補が出るので選ぶだけです。
▼で選び直し可です。

2）は必ずSheet1のB2を起点にしてドラッグしてください。
（檸檬） 2021/10/27(水) 09:07

=IF(COUNTIF(名前の見出し,B2)=0,名前の見出し,INDIRECT(B2)) （檸檬） 2021/10/27(水) 09:07
と記載がありますが、

愛子の思いですが、（檸檬） 2021/10/27(水) 01:58で　Sheet2!$A$2:$A$45には名前をつけなくても
よかったのでと記載していましたが、朝の忙しい出かけでしたから元のところをコピーされたのではない
かと思いました。

再度、下記をやり直しましたが、▼をクリックすると、やはり関数が見えて真ん中のカンマがありません。
お忙しいところ恐れ入りますが、急ぎませんのでよろしくお願いします。
私もわからないながらも色々試してみます。

1)Sheet2のA2（あ）からF45まで範囲選択し［数式］タブー「定義された名前］内の［選択範囲から作成］を
クリック

 □左端列(L) にチェックを入れて［OK］。									
 ※□上端行(T)のチェックは外します。									
 これで範囲の各行に名前が付きました。

2）ここから入力規則
Sheet1のB2:F8をドラッグ範囲選択してデータの入力規則で種類を「リスト」にして「元の値」に
=IF(COUNTIF(名前の見出し,B2)=0,名前の見出し,INDIRECT(B2)) ではなく、
=IF(COUNTIF(Sheet2!$A$2:$A$45,B2)=0,Sheet2!$A$2:$A$45,INDIRECT(B2))にしました。

（愛子） 2021/10/27(水) 11:56

データの入力規則で元の値に下記をコピー、貼り付けをしました。

=IF(COUNTIF(Sheet2!$A$2:$A$45,B2)=0,Sheet2!$A$2:$A$45,INDIRECT(B2))

▼をクリックすると関数の表示が出て真ん中のカンマがない為に入力規定で種類をリストにして
元の値のところを色々触って見ました。
そして式の始めに＝が入っていなかったので（コピー時には＝からコピーしました。）＝をつけましたら

エラーメッセージ、”条件データの入力規定で他のブックへの参照を使用することは、出来ません”
と出ました。？

＝がないとエラーは出ませんが、▼をクリックすると関数の文字が出ます。（真ん中のカンマがはありません）
（愛子） 2021/10/27(水) 13:25

 合っているかわかりませんが
 プルダウンメニューで関数式が表示されるってことは
 檸檬さんの式をコピペするときに先頭の半角スペースまでコピーしてないですかね
 気になったので
（なるへそ） 2021/10/27(水) 14:18

なるへそ様、
気にして頂きご連絡を下さり有難うございます。

今、下のお返事を出そうとしたときに衝突しました。
半角スペースは、コピーしていません。

うまく行きました。

以下が、衝突前のお返事です。

檸檬様

結論からですが、檸檬様の言われた通りにしましたら万事うまく行ったという事です。

色々あれやこれやと試しているうちに、ふと▼をクリックしましたら　「あ」　から　「わ」　迄表示され（驚きました、嬉しかったです）
▼で「あ」を選び、もう一度▼をクリックすると「あ」の人の候補が出ました。
再び、▼で選び直して「か」を選ぶと「か」の候補が出ました。

入力規定で元の値を見てみますと関数は、問題のカンマは、ついていました。
不思議でわかりません。

時間を取って頂き有難うございました。と共に、お手間を取らせました。

tkit様、＊様　も　ご協力に時間を費やして頂き本当に有難うございました。

（愛子） 2021/10/27(水) 14:29

追伸です。
色々考えていましたら下記の疑問がでました。

シート１で、もし名前入力の追加範囲が離れたところにある場合には、どのようにすればよろしいか？、
例えば、範囲がb10～f16 b18～f24 など　範囲が離れたところにいくつかある場合には、どのように
すればいいのでしょうか？　

（愛子） 2021/10/27(水) 15:05

https://www.pc-koubou.jp/magazine/39585

オートコンプリート機能を使えるように工夫したらよいのでは？

飛び飛びのセルではうまくいかないようです。

（まっつわん） 2021/10/27(水) 17:51

まっつわん様
お世話になります。
後で、オートコンプリート機能を勉強してみます。

有難うございました。
（愛子） 2021/10/27(水) 18:04

 ＞範囲がb10～f16 b18～f24 など　範囲が離れたところにいくつかある場合

 同じ入力規則を設定すればよいだけでは？

（めいぷる） 2021/10/27(水) 18:10

 >朝の忙しい出かけでしたから元のところをコピー
あっちゃ～、申し訳ありませんm(_ _)m
でも、できてよかったですね＾＾

 >シート１で、もし名前入力の追加範囲が離れたところにある場合には、どのようにすればよろしいか？、
 >例えば、範囲がb10～f16 b18～f24 など　範囲が離れたところにいくつかある場合には、どのように
 >すればいいのでしょうか？　
一度できてしまえば簡単ですよ。名前定義はできているので
同じように入力規則を設定するだけです。
 >B2:F8
=IF(COUNTIF(Sheet2!$A$2:$A$45,B2)=0,Sheet2!$A$2:$A$45,INDIRECT(B2))
 >範囲がb10～f16
=IF(COUNTIF(Sheet2!$A$2:$A$45,B10)=0,Sheet2!$A$2:$A$45,INDIRECT(B10))
 >b18～f24
=IF(COUNTIF(Sheet2!$A$2:$A$45,B18)=0,Sheet2!$A$2:$A$45,INDIRECT(B18))

そして別の方法として

 >範囲がb10～f16 b18～f24
ということはB2:F8の範囲と大きさが同じですから、その範囲をいったん全部Deleteして
B2:F8が空欄になった状態でコピー
b10～f16 b18～f24の範囲に貼り付けすれば良いです。
B10セルで入力規則を見てみると自動的に上のように数式が入ってるのが確認できると思います。

 >その範囲をいったん全部Delete
しなくてもいい場合もありますが時々Excel君に怒られます(笑
なんか言われたらDeleteしてやり直してみてください。
（檸檬） 2021/10/27(水) 20:45

めいぷる様
連絡、ご親切に有難うございました。

檸檬様
お疲れ様でした。

色々有難うございました。

結論：範囲が離れているところでも上手く行きました。

上記、その他別の方法を実行しました。言われるように自動的に上のような数式が
確認出来ました。

また今後とも、よろしくお願いします。

（愛子） 2021/10/27(水) 21:49

解決後ですが。。。

 >飛び飛びのセルではうまくいかないようです。		
が気になって少し試してみました。

 >Sheet1のB2を起点にしてドラッグしてください。		
とありますように		
 =IF(COUNTIF(Sheet2!$A$2:$A$45,B2)=0,Sheet2!$A$2:$A$45,INDIRECT(B2))		
を使うなら		
B2を起点にする必要があります。

なので最初から飛び飛びの範囲に入力規則を設定する場合は
Ctrlを押しながら飛び飛びの必要な範囲をドラッグし
B2を含む範囲を最後にドラッグするか、もう一度ドラッグし直してB2を起点にする必要があります。
そうすれば、元の値に=IF(COUNTIF(Sheet2!$A$2:$A$45,B2)=0,Sheet2!$A$2:$A$45,INDIRECT(B2))でOKです。

そして別の方法として

その範囲をいったん全部Delete と言いましたがB2だけでよかったです。
B2が空欄になった状態でコピー（Ctrl+C）
Ctrlを押しながら飛び飛びの必要な範囲をドラッグし
貼り付け（Ctrl+V）

そして愛子さんのおっしゃる意味がやっとわかりました。
入力規則の元の値ではなくA2で見ると「'」が抜けているということなのですね。
1.入力規則の元の値で「=」をつけていない場合
2.つけていてもその前に半角スペースまたは全角スペースがある場合
3.「＝」が全角の場合
にそうなりました。
以上です。

（檸檬） 2021/10/28(木) 15:49

檸檬　様
解決後に、あえて確認をして頂き色々教えていただいて嬉しいです、感謝です。

＞そして別の方法として
＞その範囲をいったん全部Delete と言いましたがB2だけでよかったです。
＞B2が空欄になった状態でコピー（Ctrl+C）
＞Ctrlを押しながら飛び飛びの必要な範囲をドラッグし
＞貼り付け（Ctrl+V）

私も、その範囲をいったん全部Delete と言われたのでそのようにしましたが上手く行きませんでしたので
迷った末にb2からf8迄をコピーして離れた任意の場所に貼り付けてその範囲内で各セルを確認しましたなら
ば、うまく行っていました。

’が抜けてる意味を教えて頂き有難うございました。

どうぞコロナ感染に気をつけられて、ご自愛ください。

（愛子） 2021/10/28(木) 21:49

檸檬　様

お世話になります。

上記内容の自動入力は、無事終わったのですが、

先程、知り合いの人から自動入力する表をもらって
同じことをしようと思いましたら、２）ここから入力がうまく行きません。
当該コメントは長すぎましたので、改めて”自動入力追加”として質問をさせていただきます。

（愛子） 2021/10/31(日) 18:54

コメント返信：

[ 一覧(最新更新順) ]

[[20211025211407]] 『名前の自動入力』（愛子） ページの最後に飛ぶ

[[20211025211407]]　『名前の自動入力』（愛子）　ページの最後に飛ぶ