[[20170708180047]]　『隣接セルの値が同じ時、一括して名前の定義』（たくと）　ページの最後に飛ぶ

『隣接セルの値が同じ時、一括して名前の定義』（たくと）

質問失礼いたします。

左隣のセルが同じ名前のものがある場合、または一つしかない場合に
一括して左隣のものを名前として名前の定義をしたいです。

説明が下手くそなのですが、具体的には

　　　　　A 　　　 B

   1      　東京　　　新宿　　　 
   2        大阪　　　難波　　 
　 3        兵庫　　　神戸　　　　
　 4        東京　　　原宿　　
　 5        大阪　　　梅田

上のような、データのとき
B1、B4に東京という名前の定義
B2、B5に大阪という名前の定義
B3に兵庫という名前の定義
ができるような機能またはマクロを書くことは可能でしょうか？

よろしくお願いいたします。
　　　　　　　

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

 私の知る限りでは同一シート内に同一名称の名前は定義できないはずですよ？
 それが出来たら、エクセルが混乱しそう...
（seiya） 2017/07/08(土) 18:39

こんにちは。
マクロで対応が可能でしょう。
複数セルの場合は、Unionで連結したものに
名前をつけることになるでしょう。
　
ただ、その名前の使い方はどのようなものですか？
後学のため教えて下さい。
　
例えば、Offsetと組み合わせることはできませんよ。
そもそもどういう目的なのかを含めて質問されるとよいかもしれません。
別のアイデアも提案されるかもしれません。

（γ） 2017/07/08(土) 18:49

これでどうなりますか

 Sub test()
    Dim dic As Object
    Dim c As Range, s As String
    Dim k

    Set dic = CreateObject("scripting.dictionary")

    For Each c In Range("a1").CurrentRegion
        s = c.Value
        If Not dic.exists(s) Then
            Set dic(s) = c.Offset(, 1)
        Else
            Set dic(s) = Union(dic(s), c.Offset(, 1))
        End If
    Next

    For Each k In dic.keys
        dic(k).Name = k
    Next

 End Sub

（マナ） 2017/07/08(土) 19:03

seiyaさま

コメントありがとうございます！
説明が下手で申し訳ありません。。
例えば、
名前　　　東京
参照範囲　B1,B4

という名前を新しく定義したいということです。

γさま

コメントありがとうございます！

したいイメージは例えばA商品の各地域の単価として
日本　東京　　新宿区　　西側　　→100円/個
日本　東京　　新宿区　　北側　　→50円/個
日本　東京　　中央区　　西側　　→80円/個
日本　大阪　　中央区　　南側　　→80円/個
米国　ハワイ　ホノルル　西側　　→60円/個

という樹形図のような元データがあったとして
1つ手前の選択肢ごとにグループ分け（名前の定義）をした上で
違うシートを集計用に設け
集計用セルにindirect関数とプルダウンリストで各項目行ごとにを紐付けしていって
（重複の度合いがわからないので2条件で特定できれば）dget関数もしくはindex関数を使って
各単価を出して行けるようにしたいと思っています

ただ、その中で元データは増えていく前提なので
自動で名前の参照範囲の更新等もしなければいけないし
データの打ち込みができれば誰でも使える形にしたいので
名前をリセットして定義し直す
というマクロを作ろうかと考えていたのですがどうでしょうか？

 たくとさん、

 失礼しました、私が問題を読み間違えました。
。
（seiya） 2017/07/08(土) 19:41

ごめんなさい。修正です。

 ＞For Each c In Range("a1").CurrentRegion
 　　↓
   For Each c In Range("a1").CurrentRegion.Resize(, 1)

（マナ） 2017/07/08(土) 20:17

マナさま

コメントありがとうございます！

実行してみたところ、エラー1004が出てきてしまったのですが
これは元データが悪いのでしょうか？
とりあえず、A列にひらがな1文字をランダムで入れてみたのですが駄目でした。。

デバックだと
dic(k).Name = k
の箇所に問題があるようなのですが。。

実行時エラー'1004'
入力した名前は正しくありません

以下の理由が考えられます

名前の先頭が、英文字、ひらがな、カタカナ、漢字、またはアンダースコアではありません。
名前に空白または他の無効な文字が含まれています
名前がエクセルの組み込み名またはブック内の他のオブジェクト名と競合しています

（たくと） 2017/07/08(土) 20:44

これでもエラーになりますか。

   　　　　　A 　　 　 B 
   1      　東京　　　新宿　　　 
   2        大阪　　　難波　　 
　 3        兵庫　　　神戸　　　　
　 4        東京　　　原宿　　
　 5        大阪　　　梅田

（マナ） 2017/07/08(土) 21:25

エラーになったときの　k の値を確認するのが先決でしょうね。
　
さて、私のリクエストにお応え頂きありがとうございました。
dgetとかを使うのであれば、
条件抽出機能が、いわば、dgetにも名前定義にもあるわけで、
機能が重複している印象です。
# 自分だったらそういうことはしないと思いますが、ご自由にどうぞ。

（γ） 2017/07/08(土) 22:19

マナさん

コメントありがとうございます！
１行目から始まっていなかったのが問題だったようです。
勉強不足で申し訳ないのですが
指定の行（例えばA3）から始めるにはどの箇所を変える必要があるのでしょうか？

γさん

コメントありがとうございます！
今春から始めた仕事がエクセルを使う機会も多いのですが
基本、全部手打ちと簡単な関数のみで仕事を進めていくような感じなので
なんとか誰でもわかる操作法で職場の仕事量が減らないかなと思いまして。。
もともと、エクセルをほとんど使ってこなかったこともあり
勉強不足でお恥ずかしいのですが、γさんならどういった手法をとられますか？
よろしければ教えてください。
（たくと） 2017/07/09(日) 10:07

それだけではやりたいことが理解できないので、助言できません。

具体的にやりたいことをもっと詳しく書いたほうが
皆さんからのコメントもつくでしょう。
（γ） 2017/07/09(日) 10:12

例えば

 Sub test2()
    Dim dic As Object
    Dim c As Range, s As String
    Dim k
    Dim p As Range

    Set p = Range("A3")

    Set dic = CreateObject("scripting.dictionary")

    For Each c In Range(p, p.End(xlDown))
        s = c.Value
        If Not dic.exists(s) Then
            Set dic(s) = c.Offset(, 1)
        Else
            Set dic(s) = Union(dic(s), c.Offset(, 1))
        End If
    Next

    For Each k In dic.keys
        dic(k).Name = k

    Next

 End Sub

（マナ） 2017/07/09(日) 10:38

 >基本、全部手打ちと簡単な関数のみで仕事を進めていくような感じなので 
 >なんとか誰でもわかる操作法で職場の仕事量が減らないかなと思いまして。。

 入力をより簡単にして、ミスも少なくしたい。

 ということなら、可変の名前定義をしてプルダウンリストを連動させることでできませんか？
 リストの構成に若干手を加えればできると思いますが？

http://www.excelist.net/faq/10.html
https://kokodane.com/2013_waza_076.htm

（seiya） 2017/07/09(日) 11:38

外出から戻りました。
　
単価とかの言葉から集計機能を想像しました。
数値の計算は関係なく、単なる参照だけですか。
それならできるかもしれませんね。
　　
ただし、飛び地のようなデータは
入力規則のリストにそのまま使えるのでしょうか？
　
いずれにせよ、
具体的にどのような作業をするのか、例で示したらどうでしょう。
あなたにも益があると思いますが。
　
（γ） 2017/07/09(日) 21:18

マナさん

参考にさしていただいて、なんとか形にすることができました！
ありがとうございました！

seiyaさん

そういった可変のドロップダウンリストを作るための名前の定義でした！
名前の参照範囲が飛び地だと無理みたいなので
リスト構成上、元データを結構いじる必要がありましたが、、

γさん

飛び地だとできなかったので、元データの方で修正をしました。
会社で工事依託を特定の数社にしていて、もちろん毎度見積もりを上げてもらっているのですが
同じ項目でも状況ごとの各単価の変動が大きいので
状況を簡素化してモデル化→自社としての各項目の標準単価を設定
という作業を行っております。
各状況の数字を全部拾えるわけではないので、数個の条件分岐によって各標準単価を出したのですが
他の社員でも、その標準単価にアクセスし集計できるようなシステムを作れればと考え、連動するドロップダウンによって各単価が参照されるのが一番簡単かなと思い、質問させて頂きました。
（たくと） 2017/07/11(火) 10:43

コメント返信：

[ 一覧(最新更新順) ]

[[20170708180047]] 『隣接セルの値が同じ時、一括して名前の定義』（たくと） ページの最後に飛ぶ

[[20170708180047]]　『隣接セルの値が同じ時、一括して名前の定義』（たくと）　ページの最後に飛ぶ