[[20160419193935]] 『複数の連名の場合に行を自動で複製させたい』(はるっち) ページの最後に飛ぶ

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

 

『複数の連名の場合に行を自動で複製させたい』(はるっち)

初めまして。
関数は使うものだけを独学で使用。それもずいぶん昔のため
いま作りたいデータでなにをどうしたらよいのか見当がつきません。
こういったところに書き込むのは初めてで勝手がわからないのですが
どなたか教えていただけるととても助かります。

再現をわかりやすくしたつもりなのですが
記述のルールがあったら申し訳ありません!

1 2 3 種 番


あ い う a 1
か     b 1
さ し   a 2

という顧客リストがあるのですが
2や3にもデータがある場合

1 2 3 種 番


あ い う a 1
か     b 1
さ し   a 2
い あ う a 1
う あ い a 1
し さ   b 2

のように自動で複製させたいのです

もし 二列目に文字列がある場合 指定した範囲を 複製
(理数で考えられなくてお恥ずかしいです。すみません。)
と考えるのでしょうが
その時に
二列目の文字列と一列目の文字列を入れ替える方法が思いつきません。

加えて
複製元行の種や番号を変更した場合
複製されたデータも自動的に変更させたいのです。

さらに
「番号」を3に変更したときは
その行ごとsheet2に移動させたいです。

もしかしたら簡単なのかもしれませんが
ここで質問させてください。

どうぞよろしくお願いいたします!

< 使用 Excel:Excel2013、使用 OS:Windows8 >


 >>もしかしたら簡単なのかもしれませんが

 関数苦手のβですが、おそらく、専門家さんでも、めちゃ困難?

 たとえばアップされた最初のリストがタイトル行含めて、4行あったとして、5行目に何か数式をいれるわけですよね。
 どんな数式になるのでしょうね。
 B2 に値があればどうこう,C2 に値があればどうこう、どちらにも値がなければ、B3に値があればどうこう、C3に値があればどうこう、
 どちらにも値がなければ B4に値があれば・・・・・
 6行目はどんな式になるんでしょうね・・・

 さらにいえば、あるセルの値がこれこれなら、その行を 別シートに 【移動】させる?
 これも、別シートでの記載はできると思いますが、元シートから【なくしてしまう】というのは関数では無理?
 せいぜい、条件付書式で文字色を白にして空白にするぐらい?

 いずれにしても関数処理向きではないと思いますが?

(β) 2016/04/19(火) 20:30


 専門家さんから関数処理案がでてくるかもしれませんが、場つなぎで。
 なお、現在の構想、Sheet1内で、連名分を展開してしまうと、その後、名簿追加や
 番号変更の際に、運用が面倒になる可能性があります。

 たとえば 番号3 として 別シートに【移動】してしまい SHeet1 からなくなった。
 あっ! しまった。2 だったということで呼び戻そうとしても SHeet1 にはもうないわけで。

 なので、Sheet1 は、あくまで、【原紙】
 これを連名補充したものを 3 以外は Sheet2 へ、3 は Sheet3 へ展開します。
 SHeet2とSheet3が正式の名簿になります。

 SHeet1 のシートタブを右クリックしてコードの表示を選んでください。
 でてきたところに以下を そのままコピペで貼り付けて、画面右上のXボタンをクリックしてシートに戻ってください。

 SHeet1 から 別シートを選んだ時に、Sheet2とSheet3への展開を自動的に行います。

 Private Sub Worksheet_Deactivate()

    Dim sh2 As Worksheet
    Dim sh3 As Worksheet
    Dim shT As Worksheet
    Dim c As Range
    Dim v(1 To 3, 1 To 5) As Variant
    Dim w As Variant
    Dim d1 As Variant
    Dim d2 As Variant
    Dim d3 As Variant
    Dim x As Long

    Set sh2 = Sheets("Sheet2")
    Set sh3 = Sheets("Sheet3")

    sh2.UsedRange.ClearContents
    sh3.UsedRange.ClearContents
    sh2.Range("A1:E1").Value = Range("A1:E1").Value
    sh3.Range("A1:E1").Value = Range("A1:E1").Value

    For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp))
        Erase v
        x = 1
        d1 = c.Value
        d2 = c.Offset(, 1).Value
        d3 = c.Offset(, 2).Value

        For Each w In Array(Array(d1, d2, d3), Array(d2, d1, d3), Array(d3, d1, d2))
            If Not IsEmpty(w(0)) Then
                v(x, 1) = w(0)
                v(x, 2) = w(1)
                v(x, 3) = w(2)
                v(x, 4) = c.Offset(, 3).Value
                v(x, 5) = c.Offset(, 4).Value
                x = x + 1
            End If
        Next

        If v(1, 5) = 3 Then
            Set shT = sh3
        Else
            Set shT = sh2
        End If

        shT.Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(3, 5).Value = v

    Next

 End Sub

(β) 2016/04/19(火) 21:40


今のデータでは、4つ目の顧客が登場した場合に、列追加になってしまいます。
データ並びを変えて、以下のようにする方が活用できるかと思うのですが、いかがでしょうか?
同じ顧客名が複数回登場するのは、利用時にミスの起こる可能性が増すと思います。

種 番 顧客
a 1 あ
a 1 い
a 1 う
b 1 か
a 2 さ
a 2 し
(???) 2016/04/20(水) 10:08


 あ い う の場合

 い と あ を入れ替えるのはわかったけど

 「 う あ い 」

 のルールがよーわからん。
 
(GobGob) 2016/04/20(水) 13:29

βさんありがとうございます。
難しいのですね!そうかー。そうでしたか。
表のつくり自体を考え直したほうがよいのかもしれないですね。

そしてなるほど、sheetの運用についてはとてもわかりやすかったです。
自分でももやもやしていたことなのですが
なるほど、すべては原本で。必要なデータをsheet別に抽出。
のほうがわかりやすいですね!
書いてくださった式での試験はまだできていないのですが
試してみたいと思います。
ありがとうございました!

???さんありがとうございます。
たしかに顧客名が増えた時の対応が難しいのですよね。
なるほど思い切って名前を列後方に持ってきたほうがあとあと便利かもしれません。
連名については紙ベースでの運用をしているため個別にはできないのが現状です。
あ様の名前で管理している品物を、い様う様も本人の名前で利用されます。
その際い様のしか伺ってなくてもあ様の品物をお出ししなければならないのです。
考えてくださったのに申し訳ありません。

GobGobさま
ありがとうございます。
上記のとおり紙ベースでの運用をしております。
あ様い様う様
の三名が
あ様の品物を利用されるため
い様しか来店されない場合でも
あ様の品物をお出ししなければならないのです。
そのため
あ様と連名の方を切り離せずにおります。

(はるっち) 2016/04/20(水) 20:27


GobGobさま
すみません質問の意味がいまわかりました。
ういあ
うとあを入れ替えるほうが正解です。
(はるっち) 2016/04/21(木) 19:24

	A	B	C	D	E	F	G	H	I	J	K
1	1	2	3	種	番		1	2	3	種	番
2	あ	い	う	a	1		あ	い	う	a	1
3	か			b	1		か			b	1
4	さ	し		a	2		さ	し		a	2
5							い	あ	う	a	1
6							う	い	あ	a	1
7							し	さ		a	2
8											

 G列以降で。

 G2 =IFERROR(IF(COUNTA(A$2:A$100)<ROW(A1),INDIRECT(TEXT(SMALL(INDEX((B$2:C$100="")*10^16+ROW(A$2:A$100)*1000+COLUMN(B$2:C$2),),ROW(A1)-COUNTA(A$2:A$100)),"!R0!C000"),),A2),"")
 H2 =IFERROR(IF(COUNTA(A$2:A$100)<ROW(A1),INDEX(IF(INDEX(B:B,SMALL(INDEX(($B$2:$C$100="")*10^16+ROW(A$2:A$100),),ROW(A1)-COUNTA($A$2:$A$100)))=$G2,$A:$A,B:B),SMALL(INDEX(($B$2:$C$100="")*10^16+ROW(A$2:A$100),),ROW(A1)-COUNTA($A$2:$A$100))),B2),"")&""
 H2をI2にコピー。
 J2 =IFERROR(IF(COUNTA($A$2:$A$100)<ROW(A1),OFFSET(INDIRECT(TEXT(SMALL(INDEX(($B$2:$C$100="")*10^16+ROW(A$2:A$100),),ROW(A1)-COUNTA($A$2:$A$100)),"!R0!C!3"),),0,COLUMN(A1)),D2),"")
 J2をK2にコピー。

 G2:K2 を下へコピー。
 
(GobGob) 2016/04/21(木) 21:39 → 21:56 チョイ修正

 補足。

 A:E列 2〜100行目まで前提。
 
(GobGob) 2016/04/21(木) 21:42

GobGobさん!
え!すごい!ありがとうございます!
別の業務であわただしくしていてここのチェックができずにいました。
返信遅くなって申し訳ありません。
本当に助かります。ありがとうございます!

(はるっち) 2016/04/26(火) 19:53


GobGobさん
およびエキスパートな皆さま

先日GobGobさんから教えていただいたとおりで問題なく運用できていたと思ったのですが
不具合?があることがわかりまして、式をなんどもなんども見てはみたのですが
やはりわたしにななぜそれがおきているのか判明できませんでした。
もう一度お教えくださいませ!

	A	B	C	D	E	F	G	H	I	J	K
1	1	2	3	種	番		1	2	3	種	番
2	あ	い	う	a	1		あ	い		a	1
3	か			b	1		か			b	1
4	さ		す	a	2		さ			a	2
5							い	あ	う	a	1
6							う	い	あ	a	1
7							す		さ	a	2

上記のようにC列の文字列が複製されないのです。
AとC入れ替えての複製行には反映されています。

教えていただいた式のどこを修正すればよいのでしょうか?!
よろしくお願いいたします !
(はるっち) 2016/06/14(火) 19:56


お返事ないかなー?
あきらめるしかないですかね?( ;∀;)
(はるっち) 2016/06/16(木) 20:16

コメント返信:

[ 一覧(最新更新順) ]


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