[[20091122135908]] 『データの入力規則』(りお) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『データの入力規則』(りお)

 お世話になります。

       A      B      C      D     E      

 1  商品名 寸法1 寸法2 寸法3 寸法合計  

 2 ***  100           200    300

 3 ###  300    100           400

 上記のような表を作っており、データの入力規則でA〜Eへ順番に
データを入力してもらうようにしたいんです。
A〜Eまで全てデータが入力されるのであれば左のセルにデータがある場合のみデータの入力を許可する。という入力規則でいいとおもうのですがC2のように寸法に関しては
空白の場合があります。この場合、どのような入力規則を設定すればよいのでしょうか?? 

よろしくおねがいします。


 つまり
  B:Dのどこかにデータが入っていないと
  Eの列は入力しちゃ駄目だよ
 って事でしょうか?

 ただ、E列は SUM(・・・) の式を
 入れておけば良さそうなので。。。
  B:Dのどこにもデータを入れていないのに
  下の行の入力を始めちゃ駄目だよ
 って事なのかな。。。?

 或いは
  A列を入力する前に、B:D列を入力しないで
 って事?

 それとも
  これは例だから!!
 なのでしょうか。。。

 どうなればよいのか、一寸良く分かりません。
 駄目な例とかも挙げてみてもらえませんか?

 (HANA)

(HANA)さんお返事ありがとうございます!

説明不足で申し訳ありません。

つまり

  B:Dのどこかにデータが入っていないと
  Eの列は入力しちゃ駄目だよ
 って事でしょうか?

その通りです!実際には寸法合計には寸法合計にはB〜D以外の長さが足されることもありますので

E列も手入力になります。なのでA、B、C、D、Eと順番に入力してもらいたいんです。

理由は、重複データが入力されないようにF列に「=A+B+C+D+E」という数式を作ってE列に入力規則で

countif関数を使用して重複データを探すようにしているからです。

困ったことにA〜Dは全く同じで最後の寸法合計だけが違う値になることがあるんです。

なのでE列にcountifの入力規則を作ったのですが‥

先にE列に合計を入力してからA〜Dを入力すると重複データが入力できてしまうんです(泣)

もしかして重複データの入力を防ぐ方法自体を見直したほうがいいのでしょうか??

(りお)

すみません、追記します。

困ったことにA〜Dは全く同じで最後の寸法合計だけが違う値になることがあるんです。

具体的には

 商品名  寸法1  寸法2  寸法3  寸法合計

 ***    300               300      600
 ***    300               300      650

のようになることがあるのです。


 >E列に入力規則で 
 >countif関数を使用して重複データを探すようにしているからです。
 使っている設定(数式)を教えて下さい。

 (HANA)

  (HANA)さん

     A      B     C     D         E         F
 1 商品名 寸法1 寸法2 寸法3 寸法合計

 2 *****   300           300    600

 3 #####   100     200          400

 4 *****   300           300    650

 F列に「=A2+B2+C2+D2+E2]という数式を作って下セルへコピーしています。
 E列にデータの入力規則でユーザー定義で数式はE4セルの場合「=COUNTIF($F$1:F4,E4)=1」
 という数式を入れています。

 何度も説明不足ですみません!

 (りお)

:


 全体として(B:Eを通して)
 何が入力可能で、何が駄目なのか
 良く分からないのですが。。。

 例えば
 B:D列のセルには
 =$E2=""
 で、E列に入力が無いときだけ入力可能にし

 E列のセルには現在の制約にB:Dに入力が無い
 を付け加えた物を設定しておくと
 =AND(COUNT(B2:D2)<>0,COUNTIF($F$1:F2,F2)=1)

 少しは抑制になるかもしれません。

 ただ、当初からですが
 例えば 5行目に
 100,100,100,400
 と言うデータも入力不可に成るので
 本当にそれでよいのか疑問に思います。

 (HANA)

 ちょっと横から失礼します。

 >その通りです!実際には寸法合計には寸法合計にはB〜D以外の長さが足されることも
 >ありますのでE列も手入力になります。

 というのが、状況を複雑にしている一因だとおもいますが、具体的にはどのような値が
 入ってくるのでしょうか。

 それを列として追加し、合計は合計で素直に SUM を使ったほうが良いように思いますが。
 (Mook)


 (HANA)さん、(Mook)さん、回答ありがとうございます。

 このシートは実際に製品を加工する職人さんに入力してもらってるのですが、
 まず、寸法合計の長さに材料を切り分けます。
 加工する際に材料の伸び等があるため単純に各寸法を足しただけでは規格に収まらないそうで、
 職人さんの経験により寸法合計=材料の長さを決めてもらってます。
 私も、(Mook)さんのおっしゃる通りもう一列追加することを提案したのですがそれだと
 どれが寸法でどれが伸びか一目で分らん!と却下されました(泣)

 B〜Dの寸法に関しては、記入がないところに関しては決まった寸法があるらしく無記入だそうです。
 それに関しても記入をお願いしましたが職人さんが記入ミスを起こす例があったらしく駄目でした。
 なので、確実に記入があるのは商品名、寸法合計だけ、ということになります。

 今回このシートを作ることになったのも同じ商品、寸法合計が重複して入力される例が多数ある。
 という工場側からのクレームに対応するためなのですが‥‥。

追記

 ごめんなさい、最後の文章、なんかおかしいですね。同じ商品名で同じ寸法合計‥
 エクセルでいう同じレコードが重複入力される。ということです。


 >確実に記入があるのは商品名、寸法合計だけ、ということになります。
 そしたら
 > B:Dのどこかにデータが入っていないと
 > Eの列は入力しちゃ駄目だよ
 と言う規制ではだめなのではないかと思いますが。。。?

 それと
 イメージがつきやすいような説明をしてもらえるのが良いのですが。

 例えば
 寸法1,寸法2,寸法3 ではなく
 長さ,幅,厚み とか。。。

 今回、ご説明用にその様に言い表して居られるのではなく
 実際にも「寸法1,寸法2,寸法3」と呼ばれているのでしょうか?
 (ただ
  >記入がないところに関しては決まった寸法がある
  って事なので、ある程度決まっているのではないかと思いますが。)

 寸法合計と言うのは何をあらわすのですか?
 例では、それぞれの数字を足した数+α 的なイメージがありますが
 「決まった寸法は入力しない」と言う事は
 実際はそうではないのですよね?

 なんだか、どう言った事なんだか
 良く分かりません。

 >同じ商品名で同じ寸法合計
 が駄目なだけなのですか?
 商品名違いはOKだし
 B:Dの入力がどうでも?

 (HANA)


(HANA)さん

 各項目名に関しては実際にこの名称で使用しています。
 寸法合計というのは材料の長さにあたります。
 商品名を見ると寸法1がどの箇所で寸法2がどの箇所にあたるのか 
 図面に記してあるのでこのような単純な項目名になってます。
(図面といっても長さが記されているわけではないので…)

 先ほどから教えて頂いた入力規則を使用して色んな値を入力して
 検証してますが、これでも重複入力は防げそうな気がします!

 あとずっとおつたえするのを忘れていて申し訳ないのですが
 商品名はリドロップダウンリストに設定しています。

 (りお)

 何が駄目なのか明確にしてください
 と書いていますが?

 E列だけ入力するような事もあるのですよね?

 B:Dの入力は違うがE列は同じ
 と言うパターンの入力も駄目なのですか?

 商品名が違っても?

 どうもその入力規則では違っているような気がしますが。。。
 >駄目な例とかも挙げてみてもらえませんか?
 にもお答えが無い様ですし
 深追いする必要も無い(私が駄目な気がするだけで実際はOK)
 なのかもしれませんが。

 (HANA)


 一生懸命整理しようとされているとは思いますが、
 失礼ながら仕様が不明確な状態で迷走している典型のように見えます。

 まず、どのようにしたいのかを明確にするのが最初のお仕事ではないでしょうか。
 文章をざっと見ただけですが、もし私なら

 >どれが寸法でどれが伸びか一目で分らん!と却下されました(泣)
 ⇒ 寸法と伸びを分かりやすく記入できるように工夫する

 >B〜Dの寸法に関しては、記入がないところに関しては決まった寸法があるらしく
 >無記入だそうです。
 >それに関しても記入をお願いしましたが職人さんが記入ミスを起こす例があったらしく
 >駄目でした。

 ⇒決まった寸法は商品を入力したときに、入るようにする。
 ⇒入力ミスをしないように工夫する。

 といった対策を考えると思います。

 そもそもこの入力を行うのが何のためなのかという説明があると、
 分かりやすいのですが。

 商品はドロップリストになっているということですが、種類は多いのですか?
 種類ごとにルールを明文化すると、入力ルールも考えやすいかもしれません。
 (Mook)


     A      B        C         D           E             F         G
 1 商品名 寸法1  寸法2     寸法3     寸法合計

 2 *****   300                 300          600     *****300300600  TRUE

 3 #####   100      200                     400     #####100200400  TRUE

 4 *****   300                 300          650     *****300300650  TRUE

 作業列をG列に 追加して HANA様の式を参考に追加して 

 G2に=IF(A2="","",AND(COUNT(B2:D2)<>0,COUNTIF($F$1:F2,F2)=1,SUM(B2:D2)<=E2,SUM(B2:D2)*1.1>=E2)) 下へフィルコピー 
 SUM(B2:D2)*1.1>=E2の意味はB2とC2とD2の合計×1割以下がE2ならばTRUE (ここは 勝手に判断してます。寸法の合計×有り得る範囲割合に変更してみて)
 
 F列に「=A2&B2&C2&D2&E2]という数式を作って下セルへコピー ちょいと&に変更 
 E列にデータの入力規則でユーザー設定で数式はE2セルの場合「=if(g2=true,1,"")」
 もしかしてE列の入力後 A列〜D列の変更でG列がFALSEに成った場合は条件書式設定で [=g2=false]色着けや取り消し線が目安になるかも?
 こんな感じじゃダメかな?だめならスルーして下さい。
(hs)

(HANA)さん、(MOOK)さん、(hs)さん
回答ありがとうございます!

 ご指摘のとおりまずは私が条件、やりたいことを
 整理しなければ今より先に進めないようです。
 しかし、みなさんからたくさんのヒントを頂けた
 と感じておりますので、もう一度じっくり考えてみようと思います。

 その上でどうしても解決出来ないところがあった場合
 あらためて質問させて頂くことをお許し下さい。
ほんとうにありがとうございました! 

 せっかくの掲示板ですから、質問のやり取りの中で整理をしていけばよいのでは?
 問題としては面白そうなので、どう解決するかも気になります。

 伸びが分かりずらいという問題に関しては、背景色を変えるなどで対応できないで
 しょうか。

 私ならということで、
     A      B     C     D         E         F
 1 商品名 寸法1 寸法2 寸法3   伸び     寸法合計
 2                          =F2-SUM(B2:D2)
 3 *****    300    300             50        650

 のような形で、
 寸法     B、C、D・・ 背景色  白   細枠
 伸び     E ・・・・・背景色  灰色 細枠(数式のみ:セルにロックで入力不可)
 寸法合計 F ・・・・・背景色  黄色 太枠

 あとは条件に応じて、条件付き書式を設定するといったようにしてみたい感じが
 します。

 いずれにせよ、やり取りの中で明らかになるということもありますので、
 もう少し条件を明示されてはどうですか?

 マクロという強力な方法もあります。
 (Mook)

(Mook)さん、ありがとうございます!

 今日一日、仕事をしながら頭の中を整理してみました。
 まず、このシートについてですが取引先より受け取った設計図を基に職人さんが
 加工寸法を算出しこのシートに入力します。その後、このデータを基に工場の方で加工を行う。
 という目的で作られています。

 職人さんが入力を行う際、商品名(加工の形)、寸法、寸法合計、全てが重複する物がある場合、
 それは入力してはいけないのですが重複入力される例があるのでデータの入力規則でそれを防ぎたい。
 ということで今回質問させていただきました。
  
 入力されるデータについてですが、

 ●商品名、寸法合計が同じでも寸法1〜3が違う商品もある。(切り分ける材料の長さ、形は同じでも各箇所の長さ=寸法が違う。)いずれかの寸法が違えば違う商品になりますので(商品名は同じですが)重複にはなりません。

 ●商品名、寸法合計のみ入力される商品もあります。その場合、材料を切断するだけでいいそうです。

 ●寸法合計は単純に寸法を足した値ではなく、材料の伸び等を考慮した値+無記入の寸法を足した値になります。この無記入の寸法というのが不思議で同じ商品名なのにひとつは寸法1のみ入力。もうひとつは寸法2のみ入力。というパターンがあるのです。
 しかし、それでも出来上がる商品はちゃんと規格に収まるものになっているので職人さんたちの間で暗黙の了解のようなものがあるんだと思います。

 ●商品の種類は20種類ほどあります。

 みなさんのご意見を参考に入力列の追加をできないかもう一度交渉してみたのですが各工場共通の様式
 ということで簡単には変更できないということでした。

 (りお)


 うーん、なんかロジックだけでは解決しない気がしてきました。

 素人考えでは20種類の製品を複数の職人さんが作るのだったら、20種類の寸法の
 セットを用意しておいて、製品の種類だけ指定したらその材料を用意するという方法に
 したら楽ではないかと思ったのですが、職人さんごとに同じ製品を作成するのにも異なる
 寸法の材料を使用しているということでしょうか。

 そうなると、なかなか難しいですね。
 ただそうはいってある職人さんが毎回同じ製品を作成するのに異なる寸法を記入している
 という気もしません。

 であれば、極論、「職人さん」×「製品の種類」 の寸法テーブルを用意し、職人さんの
 名前と製品を入力したら寸法が入るようにしてしまうというのは乱暴でしょうか。
 であれば、VLOOKUP だけでエクセルシートは作成できます。
 (ただ、商品名が同じで寸法が異なるというのは、別の商品名にする必要があります。)

 ただ最初の質問に立ち返って、入力時に誤入力を防止するのが最善の対策であるので
 あれば、もう少しそこを訴求するのもよいのかもしれません。
 (Mook)


(Mook)さん、ありがとうございます!

 最初の質問に立ち返って、とのことですが現在のような状況でもA〜Eへ順番に
 入力してもらう規則は作れるのでしょうか?それとも、マクロを使用するのでしょうか?
 マクロは”マクロの記録”くらいしか使ったことがないのですが‥‥


 警告を表示するのもいろいろなレベルがあると思います。

 たとえば、A、B、...Eの順番で入力しないときに、セルの色を変える程度でしたら、
 条件付き書式で B2:E2(2行目の例) を選んだ状態で
 「数式が」  =AND(A2="",B2<>"")
 (書式は適当に)とする方法もあります。

 マクロでやるなら、シートの「コードを表示」で

 Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column > 1 And Target.Column <= 5 Then
        If Target.Offset(0, -1) = "" Then
            MsgBox Replace(Target.Offset(0, -1).AddressLocal, "$", "") & "を先に入力してください。"
        End If
    End If
 End Sub

 のようにすれば、空欄の右セルを選ぶとメッセージが出ます(入力の禁止はしていません)。
 (Mook)

 (Mook)さん、ありがとうございます!

 教えていただいた方法を試してみました。
 マクロで警告を出す方法が効果的に思えたので”を先に入力してください。”の部分を
 ”を先に入力してください。空白でよければこのまま進んでください。”と少し文章を追加してみました。この方法と今までの入力規則を組み合わせれば重複入力は防げそうです!
 とりあえずこの状態で経過を見ながら私自身ももっと勉強をしなければ
 いけないと痛感しました。私の説明が曖昧で皆さんにはご迷惑をおかけしました。
 たくさんのヒントを頂き本当にありがとうございました!!


 入力規則でやる場合、作業列が沢山必要に成ってしまいますが
    	[A]   	[B]  	[C]  	[D]  	[E]     	[F]                 	[G]   	[H]  	[I]     
[1] 	商品名	寸法1	寸法2	寸法3	寸法合計	                    	商品名	寸法 	寸法合計
[2] 	***** 	300  	     	300  	600     	*****_300__300_600  	FALSE 	FALSE	TRUE    
[3] 	##### 	100  	200  	     	400     	#####_100_200__400  	FALSE 	FALSE	TRUE    
[4] 	***** 	300  	     	300  	650     	*****_300__300_650  	FALSE 	FALSE	TRUE    
[5] 	      	     	     	     	      	____                	FALSE 	FALSE	FALSE   
[6] 	      	     	     	     	        	____                	TRUE  	FALSE	FALSE   
[7] 	      	     	     	     	        	____                	TRUE  	FALSE	FALSE   
[8] 	      	     	     	     	        	____                	TRUE  	FALSE	FALSE   
[9] 	      	     	     	     	        	____                	TRUE  	FALSE	FALSE   
[10]	      	     	     	     	        	____                	TRUE  	FALSE	FALSE   

 F2
=A2&"_"&B2&"_"&C2&"_"&D2&"_"&E2
 G2
=COUNT(B2:E2)=0
 H2
=AND(A2<>"",E2="")
 I2
=AND(A2<>"",COUNTIF(F:F,F2)=1)
 として必要行フィルドラッグ。

 入力規則は
  A列   =G2
  B〜D列   =$H2
  E列      =I2
 の様に設定します。

 (HANA)

(HANA)さん、ありがとうございます!
 こんな入力規則、私にはとても思いつきません…
 それぞれの数式を理解するのにも時間がかかりそうですが、
みなさんのおかげで 理想以上のシートが出来上がりました!!
 私もこんな数式やマクロがつかえるように頑張って勉強します。
 また質問させて頂くこともあると思いますが、その時はご指導おねがいします!

 (りお)

 HANA様ありがとう御座います。
 あぢゃまー これは,
 E列にデータの入力規則でユーザー設定で数式はE2セルの場合「=if(g2=true,1,"")」
                             ↑ =g2
 これだけで良かったのね・・そんな事も知らないで・・
 また一つ参考になりました。
 関係ないのに、レス汚してすみません。 
 (hs)

 ん?数式自体は難しい物を使っているとは思いませんが。

 寧ろ、これを作業列無しでやろうとすると
 気付かない内に循環参照になるのか(たぶん)
 どうも思った動きに成らないのが。。。

 条件を整理すると

 品名を入力出来るのは
  =COUNT(B2:E2)=0
  B:E列に入力が無い時

 寸法を入力出来るのは
  =AND(A2<>"",E2="")
  品名に入力があって、合計寸法に入力が無い時

 合計寸法を入力出来るのは
  =AND(A2<>"",COUNTIF(F:F,F2)=1)
  品名に入力があって、入力データに重複が無い時

 ですよね。

 私がしつこくお伺いしていたのは、最初F列の式を
 =SUM(・・・)でやっていると書いて居られたので
 これでは例えば↓2行目が入力されていたら

 1 商品名 寸法1 寸法2 寸法3 寸法合計
 2 #####   100     200          400        700
 3 #####   100     100    100   400        700  ★
 4 *****   100     200          400        700  ★

 3,4行目の様なデータが入力出来ない事に成りますよね。

 >>何が駄目なのか明確にしてください
 と書いていたのは、この部分で
 「本当にそれで良いの?駄目だと思うけど」
 と言う思いがあったからです。

 それぞれの列で確認して、全部が一致したら駄目だけど、一部でも違うならOK
 と言う事であれば やはり
 「それぞれの列のデータが一致する物が無いか」を
 確認する必要が有るでしょう。

 To,hsさん
 何かの参考にして頂けた様で、光栄に思います。

 (HANA)

コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.