[[20210127133825]] 『A4印刷に適した「商品毎に納場表示・納場毎に商品』(とたつ) >>BOT

[ 初めての方へ | 一覧(最新更新順) |

| 全文検索 | 過去ログ ]

 

『A4印刷に適した「商品毎に納場表示・納場毎に商品表示」をしたい』(とたつ)

お世話になっております。
下記のリストから
A4印刷に適した「商品毎に納場表示・納場毎に商品表示」をできないでしょうか?


 	  A       B      C        D
 1	商品		納場	重複チェック	リストチェック
 2	XX0088PP	2244	XX0088PP2244	有
 3	XX0088PP	96P0	XX0088PP96P0	有
 4	XX0088PP	0444	XX0088PP0444	有
 5	YY5599XX	2244	YY5599XX2244	有
 6	XX0088PP	0N9P	XX0088PP0N9P	有
 7	YY5599XX	96P0	YY5599XX96P0	有
 8	XX0077WW	0444	XX0077WW0444	有
 9	XX0077WW	222W	XX0077WW222W	有
 10	22304P		2244	22304P2244	有
 11	XX0088PP	0Q66	XX0088PP0Q66	有
 12	22304P		0444	22304P0444	有

A:手入力
B:手入力
C:計算式(=A&B)/条件付き書式で重複チェック
D:計算式(COUNTIFで別のリストに商品が存在しているかチェック)

追加が発生したときは最下に入力
現在データは257行有り


現在は、ピポットテーブルで
「商品毎に納場表示・納場毎に商品表示」に表示した後、
手動のコピペでA4に収まるようにレイアウトを行っています。

この手動工程の負担を減らしたく、お力をお借り頂ければ幸いです。

「商品毎に納場表示・納場毎に商品表示」ができており、
印刷後に可読性のある状態であればレイアウトは問いません。
上記リストからのデータ構成が悪い場合は、リストの変更も可能です。


現在の手段・レイアウト
※例のデータ量が少ないので印刷範囲が13行だとした場合で記載します。

1.リストからピポットテーブル作成

 商品毎		納場毎
 22304P		2244
 2244		22304P
 0444		XX0088PP
 XX0077WW	YY5599XX
 0444		0444
 222W		22304P
 XX0088PP	XX0077WW
 2244		XX0088PP
 0444		0N9P
 0N9P		XX0088PP
 0Q66		0Q66
 96P0		XX0088PP
 YY5599XX	222W
 2244		XX0077WW
 96P0		96P0
		XX0088PP
		YY5599XX

2.13行に収まるように、改行。見やすいように罫線引いて文字色変更して終了。

 22304P		YY5599XX	2244		222W
 2244		2244		22304P		XX0077WW
 0444		96P0		XX0088PP
 				YY5599XX	96P0
 XX0077WW					XX0088PP
 0444				0444		YY5599XX
 222W				22304P
 				XX0077WW
 XX0088PP			XX0088PP
 2244
 0444				0N9P
 0N9P				XX0088PP
 0Q66
 96P0				0Q66	
				XX0088PP

< 使用 Excel:Excel2016、使用 OS:Windows10 >


こちら案はありませんでしょうか?
機能的にできない・難しい、情報が不足している為答えることができないなど、
何か情報があれば嬉しいです。

できない場合は面倒ですが手動でできる作業なので、今後も手動で行います。

(とたつ) 2021/02/02(火) 10:22


 こんにちは ^^
多分出来ると思いますよ。。。
いきなり出来たものをお渡しするには。。。確かに情報交換量は
完全に不足かも。予測で書けば、修正やら何やらで、どぉなるか
予測が付かないので、二の足をふんでいるとか。
又、将来変更時案等が有った時、とたつ 様が返ってお困りになる
かもとかもあるかもです。[文字色、罫線]までサポートとなると。
本当はシンプルなものでも、かなぁ〜り煩雑に。。。^^;
でも
ご自身でお作りになる、ご決意で臨まれるのでしたら、こちらは
懇切丁寧な、先生が多いですよ。実際に困った事象を、具体的に
一つずつお尋ねになると、やがて、壮大なプログラムでも[関数含む]
出来上がる事でしょう。また、熱心に頼み込んでると、ちゃちゃっと
コードを書いてくださる方もおられるかもしれません。
私的な感想だけ述べ、申し訳ありません。全て私の想像です。でわ
m(_ _)m
(隠居じーさん) 2021/02/02(火) 11:09

こんにちは。

A4印刷に適したレイアウト ← 「適している」が主観や好みに左右されやすい

行高、列幅、フォントの種類、大きさ、印刷設定、プリンタの種類によっても違ってくるので
答えづらいのではないかと思います。

あなたのPCやプリンタ環境を教えてもらったとしても同様の環境を再現できる人もきっといないので、

たとえば例に示されたような「13行レイアウトにする」などの必要な条件をはっきり指定する
たとえば「あなたのプリンタでA4に収まる」というような環境依存の条件は指定しない

ということを工夫すると、良い回答が得られるかもしれません。
(ふ) 2021/02/02(火) 12:03


Sub main()
    'Sheet1からSheet2に整形
    Dim c As Range
    With Sheets("Sheet2")
        .Cells.ClearContents
        Call subtrn("A", "B")
        Call subtrn("B", "A")
        .Range("A:A").Cells.Replace What:=Chr(2), Replacement:=""
        For Each c In .Range("A:A").SpecialCells(2).Areas
            c.Copy .Range("A1").Offset(Rows.Count - 1, 1 + Int(c.Cells(c.Count).Row / 13)).End(xlUp).Offset(2) '13を適宜調整
        Next c
        .Range("A:A").Delete
        .Rows("1:2").Delete
    End With
End Sub

Sub subtrn(x, y)

    Dim c As Range, cc As Range, dic As Object
    With Sheets("Sheet1")
        For Each c In .Range(x & "2:" & x & Rows.Count).SpecialCells(2)
            If WorksheetFunction.CountIf(.Range(x & "2:" & x & c.Row), c.Value) = 1 Then
                Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).Value = c.Value
                Set dic = CreateObject("Scripting.dictionary")
                For Each cc In .Range(y & "2:" & y & Rows.Count).SpecialCells(2)
                    If cc.Offset(, IIf(x = "A", -1, 1)).Value = c.Value Then
                      If dic(cc.Value) = False Then Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).Value = cc.Value
                      dic(cc.Value) = True
                    End If
                Next cc
                Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Chr(2)
            End If
        Next c
    End With
End Sub
(mm) 2021/02/02(火) 13:16

すみません。
質問で返します。

1)重複チェックとピボットテーブルの関係は?
2)
 >「商品毎に納場表示・納場毎に商品表示」に表示した後、

ピボットテーブルを作る時(表示するとき?)にそのような選択肢がでるのですか?
そのように操作したいのはわかりますが、
エクセルの操作の説明にはなってないと思います。
エクセルをどのように操作したら同じ結果になるかを説明してみては?

3)ピボットテーブルの結果は中間の結果ですよね?
元のデータはどんな感じで、
結果としてはどのように表示したいのですか?

4)マクロでの解決をしたいのですか?
数式または一般機能のみで解決したいのですか?

(まっつわん) 2021/02/02(火) 14:29


皆さんご回答いただきありがとうございます。
面倒な作業をなくしたいという気持ちが先行しすぎて、
ご指摘の通り情報が不足していたようです……。

順番が変わってしまうのですが、まずはまっつわんさんのご質問に回答致します。


1)重複チェックとピポットテーブルに直接的な関係はありません。
ピポットテーブルは「商品」と「納場」の項目のみ利用しています。
既存の商品と納場の組み合わせで入力しないように確認をしている列です。

2)最初に載せた表(A〜Dまで、現在257行まであるリスト)をテーブル化して、
それを元にピポットテーブルを作成しています。
データを追加した際には、ピポットテーブルの更新を行っています。

同じデータを参照した2つのピポットテーブルがあり、
それぞれ行の欄に上から順に「商品・納場」「納場・商品」で設定してあります。

3)上記で述べた通り、ピポットテーブルは中間の結果で、元のデータは最初に載せた表です。
「商品毎に納場がまとまった状態」「納場毎に商品がまとまった状態」にする為だけに、
ピポットテーブルを使用しました。

最終的には「商品毎に納場がまとまった状態」「納場毎に商品がまとまった状態」が
A4に収まればレイアウトはなんでもいい……のですが、これだと皆さん困ってしまうのですよね。

実際に行っている工程も下記のように行き当たりばったりで……
・並び替えたピポットテーブルを利用しよう
・318行だからそのまま印刷するとA4縦で8Pに、
 シートを1ページに収める印刷機能を使用すると文字が潰れて読めなくなってしまう
・A4に収まるようにA4の比率(7:5)でデータを並び替え・縮小印刷(現在はA1:K79)
 (並び替えのついでに罫線と色付けを行っている状態です)

4)解決の手段は問いません。

(とたつ) 2021/02/02(火) 15:51


まっつわんさんへの回答の(3)で述べた行き当たりばったり工程を、
データ更新の度に行うことが面倒で相談しましたが、
ふさんの仰ることが御尤もでございまして……
そんな中ご協力いただき感謝の念でいっぱいです。

mmさんからご回答いただいたVBAを使用致しました。
ピポットテーブルを挟まずとも商品毎・納場毎にまとめられて感動しております。

理解が及ばないところが多々ある為、修正できないのですが、
設定した行数よりも下にデータがある状態が発生します。
お知恵をお借り頂ければ幸いです。


 Sub main()

 'Sheet1からSheet3(印刷レイアウト)に整形
     Dim c As Range

     With Sheets("Sheet3")
         .Cells.ClearContents 'シート内のセルクリア
         Call subtrn("A", "B") '商品名毎に納場を取り出し

     '???:ここに1行入れたら、改行工程で商品毎と納場毎をわけることができる?

         Call subtrn("B", "A") '納場毎に商品名を取り出し

     '指定行で改行
     '???:指定行よりも下にデータがあることがある、発生原因不明
         .Range("A:A").Cells.Replace What:=Chr(2), Replacement:=""
         For Each c In .Range("A:A").SpecialCells(2).Areas
             c.Copy .Range("A1").Offset(Rows.Count - 1, 1 + Int(c.Cells(c.Count).Row / 79)).End(xlUp).Offset(2) '79(行数)を適宜調整
         Next c

         '作業用列・行の削除
         .Range("A:A").Delete
         .Rows("1:2").Delete

     End With

 End Sub

 Sub subtrn(x, y)

 '商品毎に納場・納場毎に商品を取り出すマクロ
 '???:取り出す際に色付けたらその後の並び替えでも色付く?

     Dim c As Range, cc As Range, dic As Object
     With Sheets("Sheet1")
         For Each c In .Range(x & "2:" & x & Rows.Count).SpecialCells(2)
             If WorksheetFunction.CountIf(.Range(x & "2:" & x & c.Row), c.Value) = 1 Then
                 Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1).Value = c.Value
                 Set dic = CreateObject("Scripting.dictionary")
                 For Each cc In .Range(y & "2:" & y & Rows.Count).SpecialCells(2)
                     If cc.Offset(, IIf(x = "A", -1, 1)).Value = c.Value Then
                       If dic(cc.Value) = False Then Sheets("Sheet3").Range("A" &  Rows.Count).End(xlUp).Offset(1).Value = cc.Value
                       dic(cc.Value) = True
                     End If
                 Next cc
                 Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Chr(2)
             End If
         Next c
     End With
 End Sub
(とたつ) 2021/02/02(火) 16:10

質問ばっかで、すみません。

行フィールドに商品
列フィールドに納場
値フィールドにリストチェック(個数)
ではだめなのですか?

マクロを使えば好き勝手にできますが、
普段使ってないのであれば、メンテナンスが出来ないでしょう。

ピボットテーブルのデザイン等突き詰めてみては?
スライサーという機能でもいいかも知れませんし。2016ならたぶんありますよね?
手動操作で比較的容易に加工編集できるなら、マクロに手を出さない方がたぶん幸せです。

長く複雑な手順での編集が必要ならその時にマクロ化を検討しましょう。
(まっつわん) 2021/02/02(火) 17:15


何の情報が不足しているかもわからない状態ですので、
質問は助かります。ありがとうございます。

ご提示いただいた条件でピポットテーブルを作成しました。
個数が1であれば重複していないということになるのですね。

ピポットテーブルに関する知識がほとんどない為、
デザインについてよ全くわからず……スライサーを初めて使用しました。
レイアウトも変わるフィルターといった印象です。

一部の商品や納場を印刷するなら使用できそうですが、
全商品・全納場を印刷する場合、使用する必要がないように感じました。
使用方法を間違えているでしょうか?


1)下記の条件でピポットテーブルを作成
行フィールドに商品
列フィールドに納場
値フィールドにリストチェック(個数)

2)スライサーの挿入
商品と納場を選択

その後)フィルター機能と同じように、単品選択・複数選択できることを確認しました。
全商品・全納場を印刷する場合、フィルターをかけないので、
どう活用すればいいのかわからない状態です。
印刷物ではなく、PCで確認する場合には大活躍しそうだと思いました。
(とたつ) 2021/02/02(火) 17:44


 >どう活用すればいいのかわからない状態です。

定型に印刷するなら、
印刷用のシートを用意して参照式をいれるとか(同一グループで分けがうまく出来ないかな^^;)

手動でコピペするとか。

まぁ、人間に合わせてエクセルに表示させるか、
エクセルが表示するものに人間が慣れるかというのは、
いつも悩ましい問題です。

ピボットテーブルなら、
表形式で表示
小計を表示
A列に条件付き書式設定で「集計」が付くデータのフォントを白にして見えなくする
手動でコピペして整理。

なんてのもありかと。
ヒントは出したんで、あとは、工夫してちょ。

マクロは、マクロが完成したらあとは一瞬でできるかもですが、
完成するまでに膨大な時間がかかるうえに、その間結果へ少しも近づかない。
あと簡単な変更や追加の要望でも対応が難しい。(マクロを使いこなせれば比較的容易でしょうが)
手動でやり慣れていれば、たいていのことはなんとか対応は出来るのではないでしょうか。
(まっつわん) 2021/02/02(火) 18:18


商品、納場、それぞれ何種類ほどあるのでしょうか?

(マナ) 2021/02/02(火) 18:46


こんな感じだと、効率悪いでしょうか

     -A-          -B-                           -C-      -D-          -E-   
 1  商品          納場                                  納場         商品
 2  XX0088PP      2244, 96P0, 444, 0N9P, 0Q66           2244         XX0088PP, YY5599XX, 22304P
 3  YY5599XX      2244, 96P0                            96P0         XX0088PP, YY5599XX
 4  XX0077WW       444, 222W                            444          XX0088PP, XX0077WW, 22304P
 5  22304P        2244, 444                             0N9P         XX0088PP
6                                                      222W         XX0077WW
7                                                      0Q66         1XX0088PP

(マナ) 2021/02/02(火) 19:16


↑でよければ、Power Queryで。
数式でも可能かもしれませんが、わたしには無理。

 1)クエリ名:商品毎に納場表示
 let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"商品", type text}, {"納場", type text}}),
    フィルターされた行 = Table.SelectRows(変更された型, each [商品] <> null and [商品] <> ""),
    グループ化された行 = Table.Group(フィルターされた行, {"商品"}, {{"group", each _, type table [商品=nullable text, 納場=nullable text]}}),
    追加されたカスタム = Table.AddColumn(グループ化された行, "納場", each Text.Combine([group][納場],", ")),
    変更された型1 = Table.TransformColumnTypes(追加されたカスタム,{{"納場", type text}}),
    削除された列 = Table.RemoveColumns(変更された型1,{"group"})
 in
    削除された列

 2)クエリ名:納場毎に商品表示
 let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"商品", type text}, {"納場", type text}}),
    フィルターされた行 = Table.SelectRows(変更された型, each [商品] <> null and [商品] <> ""),
    グループ化された行 = Table.Group(フィルターされた行, {"納場"}, {{"group", each _, type table [商品=nullable text, 納場=nullable text]}}),
    追加されたカスタム = Table.AddColumn(グループ化された行, "商品", each Text.Combine([group][商品],", ")),
    変更された型1 = Table.TransformColumnTypes(追加されたカスタム,{{"商品", type text}}),
    削除された列 = Table.RemoveColumns(変更された型1,{"group"})
 in
    削除された列

(マナ) 2021/02/02(火) 19:58


>まっつわんさん

ピポットテーブルツール→デザイン→レイアウトの項目のことで合っていますでしょうか?
たくさんの機能を教えていただきありがとうございます。
まだ使いこなせませんが、今回の件に活用できるか引き続き調べて参ります。


>マナさん

現在、商品が60種、納場は30種あります。
今後増えることはあっても減ることはありません。

ご提案いただいた表示方法に問題はありません。
教えてくださってありがとうございます。
Power Queryという機能を初めて知り少し調べたのですが、
ご提示いただいた内容で設定すれば、ご提案の表示方法になるということですよね。

教えていただいたところ大変恐縮なのですが、
本日は時間が取れない為、明日以降実践させていただきます。
また実践後に報告に参ります。

(とたつ) 2021/02/03(水) 10:11


遅くなってしまい申し訳ありません。

マナさんからご教示いただいたPower Queryを使用しました。
(テーブルに名前を付けていた為ソースのテーブル名を変更して使用)
提示いただいた通りの表を作成することができました。

データ追加の際も更新することを確認できたこと、
並び替えが簡易に行えたこと、
セルの幅調整や改行機能で簡易に印刷範囲に収まるようにできたことから、
こちらの機能を使用させていただきます。

データ整形を行う他の業務にも活用できそうな機能を知ることができ嬉しく思います。


皆様ご協力いただきありがとうございました。
おかげで一つの業務が大変楽になりました。

今回はパワークエリを使用することにしましたが、
教えていただいた様々な方法を活用できるよう、精進してまいります。

(とたつ) 2021/02/05(金) 16:34


コメント返信:

[ 一覧(最新更新順) ]


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