Excel VBA 質問スレ Part75

**デフォルトの名無しさん** (ｽﾌｯ Sd7f-wadS) · 2022/02/09(水) 14:24:32.62

!extend:checked:vvvvv:1000:512
!extend:checked:vvvvv:1000:512
↑2行に減ってるけど、同じ内容を3行に増やして貼り付けるナリ

ExcelのVBAに関する質問スレナリ
コード書き込みや作成依頼もOKナリ

※前スレ
Excel VBA 質問スレ Part74
https://mevius.5ch.net/test/read.cgi/tech/1639932059/

VIPQ2_EXTDAT: checked:vvvvv:1000:512:: EXT was configured

2022/03/20(日) 20:21:02.46

>>404
プリンタドライバをもう一つ入れて、そのドライバの初期設定を白黒にして「白黒印刷専用」とする。
このプリンタ（ドライバ）をＶＢＡで選択して印刷する。

過去にやったことがあるけどちょっとめんどくさかった記憶
他にいい方法あるのかな

2022/03/20(日) 20:23:50.61

>>403
チェックボックスにマクロを登録してフィルターのキーをその入力があるセルを参照すれば良いだけだと思う
例外処理があるならそれはどういう条件で省くのか分からないから自分で考えて

2022/03/20(日) 20:36:02.96

>>406
ご回答ありがとうございます。
理由も一緒に書けばよかったですね。大変失礼致しました。
以後気を付けます。

やはり無理なんですね。
他のかたがご指摘のように、白黒専用プリンタドライバを増やして
プリンタを切り替えてトライしてみます。

ご指摘の通り、PageSetup.BlackAndWhite = Trueが白黒です。失礼致しました。

2022/03/20(日) 20:41:31.10

>>407
ご回答ありがとうございます。
プリンタドライバを増やす方法、ご提案ありがとうございます。
早速トライしてみます。
ドライバーを入れた後、プリンタの基本設定で白黒を初期設定にすれば白黒専用でいけますね。

2022/03/20(日) 21:09:18.42

>>403
多分VBA要らない
スライサーで十分

2022/03/21(月) 02:46:04.32

スライサやタイムライン知らない人多いらしいね
マイナーなのかな？

2022/03/21(月) 06:08:07.49

マイナーはマイナーだと思う
MOS受けて初めて知ったわ

2022/03/21(月) 07:21:11.67

テーブルからしてまだマイナーだからかしら？

2022/03/21(月) 07:27:35.78

ブックの共有するとテーブルが使えない
共有機能とテーブルどちらを選ぶとしたら会社では共有を選ぶ
糞共有機能は早く廃止してほしいな

**デフォルトの名無しさん** (JP 0H73-Sjl/) · 2022/03/21(月) 08:06:13.98

誰か教えてください
各営業所から毎月送られてくる実績ファイルを一つのブックに集計するマクロを作りたい
一つのフォルダに全てのブックを入れてループ処理するマクロ自体はできたんだけど肝心の転記の仕方がわからない
具体的には各ファイルの指定のセルから集計表ファイルの指定のセルに営業所名ごとに転記したい
営業所ファイルには4〜3月まで縦に月毎に実績が並んでいて集計表のファイルは横に月毎になってるって感じ
どうしてもわからなくてどなたか助けてください

2022/03/21(月) 08:19:15.34

powerqueryだな

**デフォルトの名無しさん** (JP 0H73-Sjl/) · 2022/03/21(月) 08:24:32.18

>>417
ありがとうございます
パワークエリってやつですかね？
検索かけたら使い方分かりそうでしょうか？

2022/03/21(月) 08:52:49.57

>>418
ようつべのライブラリに放り込んでた中から
役に立ちそうなもの見繕ってみた。

PLをフォルダに放り込むだけで科目ごとの数値を比較できる【エクセルパワークエリ PowerQuery】
https://youtu.be/xxc5sCl6U3w

大量のエクセルファイルを１つにまとめる方法【ファイル名活用】
https://youtu.be/XMwbj7x4Q0Y

【マクロ無しで】大量ファイルを1つに統合。簡単にクリック操作で可能（Power Query、取得と変換）
https://youtu.be/RCmoho3jU8Q

PowerQuery 複数のExcelファイルからデータを結合する方法
https://youtu.be/VxKoqBJWgRo

2022/03/21(月) 09:00:07.15

>>416
Sub foo()

'開いてset
Set 転記先ブック = ThisWorkbook
Set 営業所ブック = Workbooks.Open( _
"C:\Users\81909\Desktop\eigyosyo.xlsx")

'転記
転記先ブック.Sheets("sheet1").Range("b2:d3").Value = _
営業所ブック.Sheets("sheet1").Range("b2:d3").Value

End Sub

保存ファイルのパスと転記セルの範囲変えたら使えると思う

2022/03/21(月) 09:06:14.38

>>397
ありがとうございます。
やはりEXCELのシートにINDEXを設定するのは無理なんですね。

>>398
ありがとうございます。
残念ながら20万件100項目という量のせいか、
フィルターを掛けるとそのまま固まってしまい、いつ返ってくるか、そもそも返ってくるのかすら分からない状況になりました。

2022/03/21(月) 09:14:48.04

>>421
そもそもフィルター掛ける必要があるのかどうかってのもあるけど・・・
ぱっとおもいつくのは

・forで回して別シートに転記。量が少なければそこまでかからんはず。量が多いとかなり大変
・別シートにfilter関数。かなり軽いけど、その量に対してはやったことないな
・スプレッドシートでquery関数。軽さならこれが最強で、めちゃくちゃ軽い

2022/03/21(月) 16:06:54.19

>>419
ありがとうございます
見て参考にさせていただきます

2022/03/21(月) 16:10:10.41

>>420
ありがとうございますこの構文のでいける感じですか？
ホントど素人で申し訳ないんですけど転機の構文は何をしてる感じでしょうか？

2022/03/21(月) 16:14:49.98

>>420は要点をコンパクトに上手に説明してる
これが理解できないなら自分で最終回答に辿り着くのは無理だよ

2022/03/21(月) 16:21:01.88

>424
転記の部分は値でコピーペーストだよ
試しに動かしてみたほうが早いと思う

2022/03/21(月) 16:38:45.50

>>425
>>426
ありがとうございます
まずは動かしてみます

2022/03/21(月) 16:43:02.21

テーブルってまだ普及していないねかな？

2022/03/21(月) 16:50:26.11

永遠に普及しないでしょうね
列島猿はセル結合したり自分で色塗ったり線引いたりするのが大好きで、お仕着せのDBにデータだけ放り込むみたいなのは苦手なので

2022/03/21(月) 16:56:11.28

全面的に同意
テーブルで表作ったらテーブルを解除しろって言われるレベル

2022/03/21(月) 17:19:32.38

>>424
転記する際に縦横を置換したいんじゃねえの？
420の例にはそれが入ってないかと

2022/03/21(月) 17:21:13.79

テーブルでも椅子でもいいからさっさとやれと言われるわしら下っ端

2022/03/21(月) 17:57:58.00

>>416
マクロ記録開始してその転記の操作してみればいいかと

**デフォルトの名無しさん** (JP 0H73-Sjl/) · 2022/03/21(月) 19:13:04.04

>>431
あーそうなんですね
そこ大事なところなもんで…
ありがとうございます

2022/03/21(月) 19:37:57.87

いつも思うのだけど、これだけExcel情報が世の中に溢れているのにセルご法度の結合だの色塗りまくるだの改行だのする人ばかりなのは何故なんだろうね？？
パソコン雑誌とか読んで勉強していないのかなと？

**デフォルトの名無しさん** (US 0H45-9bVs) · 2022/03/21(月) 19:43:19.70

>>435
http://www2.odn.ne.jp/~cbl97790/shinsakutenjishitu.htm

2022/03/21(月) 19:53:51.30

>>435
業務改革を微塵も考えていないから
一日8時間で、最初に与えられたルーチンが回ればそれで良いと考えてる人がほとんど
なんでそうなってるかは、知らん

2022/03/21(月) 20:06:38.09

>>435
別にExcelの達人になるわけじゃないし
そもそも結合するなと言うなら縦方向の「選択範囲内で中央」を実現しやがれ

**デフォルトの名無しさん** (JP 0H73-Sjl/) · 2022/03/21(月) 20:11:42.48

>>433
それも思ったんですけどそれだと恐らく一つの営業所の実績を一つのセルにコピーする操作になってしまいそうで…

2022/03/21(月) 20:23:41.52

>>439
ここまでの説明で理解できてないようなので
とりあえず営業所毎ファイルと集計表ファイルをアップロードしないと話が進まない

**デフォルトの名無しさん** (JP 0H73-Sjl/) · 2022/03/21(月) 20:42:56.54

>>440
オケ
さすがにそこまでは出来ないので色々試してみます
ありがとうございました

2022/03/21(月) 20:43:57.15

>>439
転記部分は好きなように変えたり増やしたりスレば良い
b2をd3に、a1をe5に、など、なんとでもなる。

'転記
転記先ブック.Sheets("sheet1").Range("b2").Value = _
営業所ブック.Sheets("sheet1").Range("d3").Value
転記先ブック.Sheets("sheet1").Range("a1").Value = _
営業所ブック.Sheets("sheet1").Range("e5").Value

2022/03/21(月) 21:18:25.50

>>439
思うだけで手を動かす気はないのね…
いち抜けた~っと

**デフォルトの名無しさん** (JP 0H73-Sjl/) · 2022/03/21(月) 21:20:10.88

>>443
ごめんなさい会社のファイルなので今日できんのよ
明日行ったらやってみる

**デフォルトの名無しさん** (JP 0H73-Sjl/) · 2022/03/21(月) 21:21:07.73

>>442
ありがとうございます！
やってみます！

2022/03/21(月) 21:49:42.78

日本のExcelのレベルが低いからExcel講師やパソコン教室が潤うと。

2022/03/21(月) 22:13:13.78

ループ文とか条件文とかユーザーフォームとかクラスとか使ってごちゃごちゃやってるけど結局のところファイルの開閉と転記作業しかしてない事に気づいた

転記があれば何でも出来る

2022/03/21(月) 23:00:27.32

転記の子

2022/03/22(火) 00:36:53.70

始めたてのド素人ですがご教示頂けると幸いです。
表の列ごとにランダムに入っている異なるフォントの色で正負を判断して最終行に和と差の合計値を出力したいのですが可能でしょうか？
詳しく言うと赤色が負として、黒色が正としてセルに数値が手打ちされてます。
列の最終行を取得しsum関数を挿入することはできるのですが、フォントの色で正負を判断して合計値が出せるのか教えていただけないでしょうか。

2022/03/22(火) 00:49:54.42

>>449
もともとエクセルにはマイナスの数字を自動的に赤くする機能がある
その機能で色を変えてるんだと集計の方法も変わってくるから、まずはそこを確認して

2022/03/22(火) 01:08:13.62

書式に辿り着ければ
自ずと答えが見つかろうぞ

2022/03/22(火) 01:19:04.08

そもそもVBA不要
sumif関数で出来る

2022/03/22(火) 02:43:58.13

きっとこれは黒色の負とか青色の正とかあって
あえて色で集計したいんだろうな

と、深読み

2022/03/22(火) 12:14:00.76

ご返信ありがとうございます。
表のシートが種類ごとにあって量が千を超えているので自動化したい所存です。
前任が手書きの台帳をエクセルに落してくれたまでは良かったのですが、他の社員が手書きのときと変わらず正の値で色のみ変えて入力するため、毎年年度末になると延々と手作業で何時間も単純作業を強いられるのでなんとかならないかと思案しておりました。

2022/03/22(火) 12:33:30.37

条件書式でゼロ以上は赤字にするとかでもダメなんか？
逆にVBA使った方が遅かったり不便にかりそうな案件だと思うんだが

2022/03/22(火) 13:33:25.27

>>421
> 残念ながら20万件100項目という量のせいか、
> フィルターを掛けるとそのまま固まってしまい、いつ返ってくるか、そもそも返ってくるのかすら分からない状況になりました。
PC買い替えろ
30万行x300列に数値データを埋めてやってみたけどフィルター処理なんてほぼ瞬時に終わるぞ

2022/03/22(火) 13:38:08.96

他の社員が入力してるものだからなあ
集計よりも入力用のシートを何とかできたら手っ取り早いんだがそれは無理なんかな

2022/03/22(火) 13:43:49.21

色付けるよりマイナス記号打つ方が速いだろうに……
ご苦労さまでした

2022/03/22(火) 13:48:33.03

>>454
はっきり言えばこんなこともできないお前は手を出すな

2022/03/22(火) 14:03:10.79

>>455
元のデータが 1, -2 ってなってるところを 1, 2 (←赤文字) で入力されてるんだろ

>>454

if Cells(行,列).Font.Color = RGB(255,0,0) Then ...
で判断できるよ
データ書き換えていいなら（てか、個人的にはそうすべきと思うが）
For C In ActiveSheet.UsedRange
If C.Font.Color = XlRgbColor.rgbRed And IsNumeric(C.Value) Then
C.Value = -C.Value
C.Font.Color = XlRgbColor.rgbBlack
End If
Next
として、一気に書き換えちゃうのがいいかも

2022/03/22(火) 19:08:50.48

>>456
流石に仕事場のPCを勝手に買い替えるのは私には出来ませんし、
最終的にフィルター等の処理は使用せずにかなりの高速化をはかることが出来ました。

この方式が最速ではないかも知れませんが、
今までADO接続でSQLを実施して
2～3分掛かっていた処理が一瞬で終わるようになったので満足しています。

今まで質問に答えて頂いた方、色々参考にさせていたきました。
ありがとうございました。

2022/03/22(火) 20:15:28.30

>>454
やはりVBAは不要
オートフィルターのフォント色で絞り込み、subtotal関数で十分

2022/03/22(火) 23:22:39.90

本当にド素人なのでお許し下さい
ここに3枚の請求書（Excelデータ形式)があり、それぞれsheet1、sheet2、sheet3にあります
フォーマットはバラバラで文字や配置が異なります
これをsheet4で「支払日」「取引先名」「取引内容」「税込金額」を一覧表にしたいのです
こういうことはVBAでは可能でしょうか？
可能であれば、難易度はどの程度でしょうか？

＊実際には請求書は数百枚あります

2022/03/22(火) 23:26:23.97

>>463
フォーマットが統一されていれば、vbaでやる意味はある
フォーマットが統一されていない場合、vbaで書く意味がほとんどない。手作業でコピペするのと変わらん

2022/03/22(火) 23:26:42.94

かわらん、と言ったけど手作業でコピペする方が早いわ

2022/03/22(火) 23:43:05.71

>>461
そのフィルターが処理するために、
全データをメモリに載せようとするから、メモリ不足でフリーズする

20万件 * 100項目 = 2千万項目, 20,000,000

仮に、1つの項目が1KB とすると、
20,000,000 KB = 20,000 MB = 20 GB

メモリに載せられないから、フリーズする

ファイルの読み込みと同じ。
20GBのファイルを一度に全部読み込めない

部分的な読み込みにしないと無理

例えば、1MBずつ読み込んで処理して、そのデータを捨てるとか。
これなら、1MBしかメモリを使わない

でも、この方法では、全データをソートしたりできない。
自分で実装できないから、結局、データベースを使うしかない

2022/03/23(水) 00:15:35.00

>>461
せめてどうやったかぐらい書けよ

つかエクセルからエクセルブックにADOでSQLとか、余計なオーバーヘッドで遅くなるイメージしかないんだがな
それで高速化できるようなケースがあるのか？

2022/03/23(水) 00:34:00.08

。。同志達は例の追加された新しい関数の情報つかんでいるよね？
なんだか凄そうな予感

2022/03/23(水) 01:05:25.56

ワークシート関数の追加のニュースなら見たけどVBAにも何か追加されるん？
https://forest.watch.impress.co.jp/docs/news/1395929.html

2022/03/23(水) 05:27:44.59

>>466
なるほど、一度メモリに全て載せるようにするからフリーズするかのごとく重くなるのですね。
EXCELに割り当てられるメモリがどのくらいなのかは分かりませんが、納得です。
ありがとうございます。

2022/03/23(水) 05:59:20.73

>>467
最終的にはその重いシートを使用する場合にはADOを使用するのをやめました。
代わりに、>>397の人が教えてくれたような技法を使うようにしました。

正確には本来、プライマリキーが設定されるような最も検索後の件数が少なくなるような項目の列をFindで検索して、
その際に検索された値が入っている行の、別項目で最も値の大きい行を取得し、その行にある情報を取得するようにしました。

ADO接続ではSQLを投げられるのでかなり便利で、作成して扱う部品も統一化し易いのですが、
大量なデータを持つシートを扱う場合、或いは後に大量なデータを持つ可能性があると思われるシートを扱う場合は
都度個別にロジックを作成する必要があるという結論になりました。

2022/03/23(水) 06:08:26.57

>>466
> 仮に、1つの項目が1KB とすると、
どんなデータだよw

2022/03/23(水) 06:19:06.78

今は完全に個別の処理としてロジックを作成していますが、Findで一番検索後の件数が少なくなる場所を検索する部分は共通部品化出来そうですね。
その内その中で更にどのように抽出するかの部分においては別関数化してリフレクションで呼び出すようにすればその部分だけ個別で行えるような気もするので、
当面の目標はそれになります。

**466** (ﾜｯﾁｮｲ 292c-kNuw) · 2022/03/23(水) 09:27:45.30

>>466
>仮に、1つの項目が1KB とすると、
>20,000,000 KB = 20,000 MB = 20 GB

1つの項目が100バイトなら、2GB。
これでも、メモリ不足になる

1つのアプリは普通、100MB ぐらいまででしょ？
数百MBも使えるアプリは無いでしょ？

使用メモリの制限を外さないと無理じゃないの？

2022/03/23(水) 09:46:29.99

メモリの制限に達するとストレージ、仮想メモリ使うよ・・・何を言ってるんだ・・・

2022/03/23(水) 09:50:01.23

>>474は後で恥ずかしい思いするぞ
それとも過去の人か

**466** (ﾜｯﾁｮｲ 292c-kNuw) · 2022/03/23(水) 11:39:32.08

Java とか、VM のメモリ不足で落ちるとか？
デフォルトのメモリ割り当て設定が少ないからとか、聞いたけど

各アプリには、そういう設定があるのでは？

2022/03/23(水) 11:43:25.55

テキトーすぎでは

**466** (ﾜｯﾁｮｲ 292c-kNuw) · 2022/03/23(水) 11:45:19.28

例えば、2GB のメモリに、Windows 10 をインストールしたら、
1クリックの動作に、1分掛かるので、全く使えない

仮想メモリとか、ほぼ意味ない。
遅すぎる

ドンキの格安PC とかも、そう

2022/03/23(水) 11:49:59.54

質問者のPCの中身が分からないのになんでどうでもいい豆知識披露してるんだｗ

2022/03/23(水) 11:50:45.45

2Gでも1クリックに1分とかはかからないのでは？
win10とそっくりなwin 2019 serverが
2core メモリ1Gでも、
ああもっさりだけどまぁ普通にマウス操作できるなぁ、
ぐらいには動くし。

2022/03/23(水) 12:06:56.65

句読点の使い方とかからして関わっちゃいけない人だとわかる

**466** (ﾜｯﾁｮｲ 292c-kNuw) · 2022/03/23(水) 12:22:53.98

CPU がセロリンだからかも

メモリ2GBぐらいのPC は、CPUも安物だから、1クリック1分ぐらい掛かる

2022/03/23(水) 12:52:02.64

>>474
よくわからないのでExcelでの検証方法と結果を出してくれないか？

2022/03/23(水) 14:41:23.04

同志達はケッコー上等なパソコン使っているイメージだがどうなの？？

2022/03/23(水) 15:29:25.74

パフォーマンスが必要ならDB使えばいいのよ

2022/03/23(水) 18:26:48.33

社用PCってのは基本10万未満に抑えないと固定資産化して面倒だからそこまでスペックが高くないんよ
それこそ自社でPC作ってる会社でも未だにメモリ4GB以下だのCore i3だのが当たり前なぐらい

2022/03/23(水) 18:30:41.97

i3なんて贅沢な

**421** (ﾜｯﾁｮｲ 134f-QoK8) · 2022/03/23(水) 19:13:52.79

今更i5の1.6Gでメモリ8Gなんて言えない・・・ー

2022/03/23(水) 19:39:45.28

そのスペックでその程度のデータ処理でそんなに時間がかかるのは明らかに何かがおかしい
確実に変なことをしている

2022/03/23(水) 20:00:19.51

ADOのExcel Driverなんておもちゃに決まってんでしょ
上っ面の体裁整えただけ

2022/03/23(水) 21:28:40.17

今のPC平均てi8700 or Ryzen2600くらいじゃないんかいな？
2.4から3.5Ghz の6-12コアあたりが多いやろ。
3-4年前のマシン。

ややいいと4Ghz の12-16コアくらいでしょ。

2022/03/23(水) 22:55:53.13

SSDかHDDか
問題はここだけだ
変なエクセルファイル作る人は、どれだけPC性能よくてもいずれ詰む

2022/03/24(木) 01:29:04.78

つかアーカイブドライブが大きな1-2T程度のSATASSDで、CやDの2つくらいが500-1TのNVMeなんじゃないの
HDDなんかは半年に一度みるかもなデータでもない限り使わないと思うけどな。
内蔵にHDDなんかあったら存在だけで使ってなくてもめっちゃ遅くなるし。

2022/03/24(木) 02:30:52.55

>>494
馬鹿は黙っていればいいのに

2022/03/24(木) 03:16:35.71

ゲーミングPCの恩恵受けたいです。
派でなキーボードで

2022/03/24(木) 12:17:51.66

20万ｘ100の中から最大値求めたいなら
1万x100ずつ読みだして最大値20個比較するのが一番早いんでねーの？

2022/03/24(木) 13:05:59.63

>>497
なぜそれで速くなると思った？

2022/03/24(木) 14:49:33.59

分割処理は面倒なようで低スペックには限界を回避し優しい

2022/03/24(木) 15:52:30.97

>>492
事務用PCだとセレロンがせいぜい
そしてエクセルは事務用ソフトやで
データベース扱わせたら即固まる

2022/03/24(木) 16:05:22.00

数年前、外出時にexcelを使うため
windowsタブレット(atom)を、最適化してた
そこそこ使えるんだけど、
たまにすごく遅くなるから原因探ったらmdbにアクセスしてるときだった

2022/03/24(木) 20:19:51.61

会社はケチだから高級スペッコのパソコンを購入してくれない。
職場の古いデスクよか私物のノーパソの方がサクサク(Ryzen積んだヤツ)

2022/03/24(木) 20:23:26.56

うちは全部SSDに変えたよ
i5 2400もサクサク
1万投資するだけでPCが復活するというのに、なんでみんなやらないんだろうな

2022/03/24(木) 21:32:40.98

儲かっている会社だけじゃないことくらい想像つくだろ

2022/03/24(木) 23:51:54.91

7年前に買ったi7のLavie。
何かメチャクチャ重いので中見てみたら
メモリが4G。そして当たり前のようにHDD。

メモリは買い換えて16G積んだけど
それでも立ち上げはなぜか遅い。

それ以来もうYAMADA電気では絶対PCを
買わないことを心に誓った。

2022/03/25(金) 01:16:28.60

さすがに、HDD は遅いでしょ