[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『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
'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
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
１）クエリ名：商品毎に納場表示
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
削除された列
２）クエリ名：納場毎に商品表示
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.