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/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 は遅いでしょ

2022/03/25(金) 01:24:52.38

>>505
ヤマダ電機は関係なくお前が馬鹿なだけだろ

2022/03/25(金) 01:25:53.93

事務員が職場でゲーミングノートなんか使っていたらカオスだね
遊びに見られる

2022/03/25(金) 01:28:11.57

つか立ち上がりってメモリよりSSDが大きく関係しているんじゃないの？
過去にウィンドウズスレで詳しい人に教えてもらった。

2022/03/25(金) 01:40:28.27

どうしてVBAプログラマーって、
コードの内容だけ調べて、

「はい、こうなってこうなって、こうなっています。
　なぜこうなっているのか、これ以上のことに関しては、こちらではわかりません。
　詳しい人に聞いてねー」

となるんだろう。
自分で何も動こうとしないで、
ただ VBE 開いて、コードを日本語訳して、終わり。
それで仕事した気になってるんだから笑っちゃうよ。

2022/03/25(金) 01:48:43.98

そういう人はプログラマーとは言わないよ

2022/03/25(金) 02:37:31.54

どういう状況でその回答に至ったかの前提条件がないとそれが妥当な返答である可能性を否定できない

2022/03/25(金) 05:36:38.48

>>510
ネットや書籍でも人に読ませる気のないコードやつぎはぎだらけでそもそもまともに動かないコード置いてはい終わり。ってのが多いし情報量は多いが質が悪いので質の悪い解釈が更に増えるという悪循環に陥ってる

2022/03/25(金) 06:16:41.66

>>509
正解

>>510
「ルーチンに関しては」詳しい人に聞いてね、だよ
むしろそれ以上なんか答えてほしい事はあるのだろうか

2022/03/25(金) 08:31:08.17

そら「知らん」からよ
給料の範疇じゃないんだろ

2022/03/25(金) 10:34:06.30

>>510
お前が自分で動けばいいんじゃね

2022/03/25(金) 11:22:36.46

>>437
民族性としか言いようがない。山本七平さんが生涯かけて取り組んだ、
「なぜ我が国はこうも組織づくりと運営ががダメダメで、暴力と数合わせによる実態のないものに陥っていくのか」っていうあれ

2022/03/25(金) 12:05:26.87

本当にできる人はネットとか何も見ないでスラスラとコード書ける人だと聞いた。
なかなかいないと思うけど。

2022/03/25(金) 12:13:20.83

VBAは大体ググるわ
普段いじらんし構文まで覚えてないし独特な部分も多いし

2022/03/25(金) 12:38:22.10

ググる人はできない人なのかw

2022/03/25(金) 12:55:56.28

>>510
それはVBAに限ったことじゃないよ。
VBA使う人だってモデリングしてクラスの構成やらインターフェースとかどう使って行くか考えてから作る人もいるし、
Javaや.Net使っている人でもその場凌ぎの平べったいコード書く人もいる。

むしろ何がどうなってそのような結果となるのかは、VBAやってる年寄り連中の方が
今ある便利な部品ばかり使うことじゃなく、もっとハードよりな理屈を知ってる人(ついでにCやC++、アセンブラ等も経験している人)が多いだろうから
一概にその理屈では片付くものでもないよ。

2022/03/25(金) 13:23:03.47

>>510
どう考えてもそれ以上は「プログラマー」の仕事じゃないわけですが

業務分担外の仕事まで無理やりやらせるブラック会社の人ですか？
責任範囲外のことを要請されても困るわけですが

2022/03/25(金) 13:44:09.14

会社のパソコンは底辺スペックが
多いからできるだけ配列必須

2022/03/25(金) 14:15:40.45

ここで質問してくる人はプログラマと呼ぶにはどうかと思うスキルだという認識だし
>>510みたいな答え返してくる奴が自分の職場にいたらっていうかこんなやついないけどｗ

2022/03/25(金) 16:09:31.18

そもそもどうしてそういうプログラムになっているか、
どうしてそういう条件文になっているか、
わからなければ、
チームリーダーやマネージャーに聞けばわかるかもしれないし、
チームリーダーやマネージャーは、主査に聞けばわかるかもしれない。

主査は、わからなかったとしたら、そういう仕様でやっていると
クライアントに説明できる。

クライアントからオペレーターに問合せが入って、
オペレーターからプログラマーに問合せがきた場合は、
プログラマーとしては、チームリーダーやチームメイトに聞くのが筋。
それをめんどくさがって、コードだけ読んで、条件文を和訳して
オペレーターに回答するだけのプログラマーって、どうなの。

オペレーターからすれば、まさかの異次元回答をくらった思いになる。
そうじゃなくて、こういう場合どう対処すればよいか、
関係者に聞いてほしい、でも関係者がわからないから、
唯一わかっている身近なプログラマーに聞いている。
そういう話だと思うんだけどなあ。

絶対いいたいこと説明できてない。
さすが俺。

2022/03/25(金) 16:25:13.86

前任が作ったコードをよくわからず使っているが、変な挙動があった
VBAかけるやつに聞いてみたら……

だとオレは思うけど

2022/03/25(金) 16:55:04.11

PGやSEを抱えていない職場で業務改善の一環としてワンオペで誰かが作ったものは仕様書すら作られてない時も少なくない
コーティング規約もないから中身が無法地帯なんてのもあるある

2022/03/25(金) 17:11:26.32

>>525
あー、そういうことね。
でもやっぱりそれはVBAがどうのではないよ。
要件定義、基本設計、詳細設計に携わっていて
仕様を押さえているかだもの。

つまり、>>527の人が言うように
PGと言うよりはSEのお仕事。

そしてここはVBAのスレだから
PGにそういうこというのはお門違いだし
このスレからしてみてもスレ違い。

で、VBAやってる人が仕様を押さえて
いないというのはそれこそ偏見だよ。
むしろちっちゃいツールばかりなんで
押さえ易いしね。

2022/03/25(金) 17:20:12.47

>>525
それはオペレーターが直接プログラマーに聞いてるのがおかしい
つか関係者わからんオペレーターがプログラマーならわかるってどんな異次元組織だよ
関係者がわからんからってプログラマーにきいてもわかるわけないだろ

ちゃんとした組織でのプログラマーの仕事と、事務員が片手間にプログラム作ったのと同じ次元で話されてもなぁ

2022/03/25(金) 17:30:04.98

ググってもおkなのか良かったわ
必死でヘルプ見て暗記していたのは非効率だよね

2022/03/25(金) 17:36:57.23

>>530
慣れでスラスラ書けるようにはなるだろうけど
どちらかといえばググり力の方が必要だよ

2022/03/25(金) 18:03:42.37

ググり方というより、公式ヘルプでの確認が大事だと思うんだが
いま公式ヘルプでさえオンラインとかいう時代だからなぁ

まあVBAの文法ていどなら暗記できるけど
エクセルのオブジェクト全部なんて暗記できる奴がいるとは思えん

2022/03/25(金) 18:39:02.37

オペレータがプログラマに聞くって何を？ｗ
知ってる言葉ならべてるだけなのかな

2022/03/25(金) 19:43:12.04

要求通りの動作をするコードが書ければできるやつだろ
検索しようが、他人に聞こうが何も見ないで作ろうが関係ないよ

2022/03/25(金) 20:11:44.87

>>534
それだとエラーチェックとかどうでも良くなってしまう
使い捨てのスクリプトならともかく、その言い方はさすがに意識が低すぎる

2022/03/25(金) 20:12:25.07

納期を守ってちゃんと動く
結果がすべて
検索だろうがコピペだろうが関係ない

2022/03/25(金) 20:40:46.85

さっき会社の上司とたまたまそういう話したけど、やっぱ何も見ないでスラスラとコード書けるヤツいたら貴重だから重宝するって言っていた。
ただ、そういう人ほど初歩的なミスに気がつかないとも。

2022/03/25(金) 20:44:28.64

＞ただ、そういう人ほど初歩的なミスに気がつかないとも。
こんな話はないわ
たまたまそういう人としか仕事してないだけ

2022/03/25(金) 20:57:16.02

飯島彩矢
いいじまよした

2022/03/25(金) 21:03:49.94

>>535
？？？
要求通りなんだから要求にエラーチェックが入ってたら当然実装するだろ

2022/03/25(金) 21:21:49.50

エクセルのクエリで、一つのファイルにある複数のシートから、たとえば１つ目から４つ目のデータ（１０００行くらい）を１枚目を１から４、２枚目を５から８に転記していくにはどのような設定でやればいいでしょうか

2022/03/25(金) 21:22:42.56

どうしよう、>>541の言っている意味が分からない…

2022/03/25(金) 21:26:08.92

おらも分からん

2022/03/25(金) 21:26:33.02

>>537
まぁちっこいツールとかならそうかもね
ある程度大きくなったら後々のメンテナンスのし易さとか、テストの工数をどうやれば減らせるかとか
最初の内に構成練り込んで考えて作るから、いくらスラスラ組めてもその辺がすっぽ抜けてると
後々手を加えるごとに分岐だらけのスパゲッティコードになって行くことが多いんだよね

2022/03/25(金) 21:28:20.55

>>542
えーっとですね
例えばシート１の１から４つめにA,B,C,Dってデータがあるとします
シートの１から１０まであって、
それを一つのシートの１箇所に横に並べたいんです

2022/03/25(金) 21:32:43.40

>>541
スピード重視でなければこの間からここで色々言われてたADO接続使ってSQLでレコードセットに値取得してそのままシートに貼り付ければいいんじゃない？

ヘッダがいるなら最初から書いとくか、レコードセットから取得するなら別ロジックが必要だけど

2022/03/25(金) 21:34:59.71

>>546
単発での処理なのであまりそういう複雑なのは難しいです

2022/03/25(金) 21:38:14.89

>>547
こんなこともわからないような馬鹿は手を出すな

2022/03/25(金) 21:39:37.46

クエリとかの簡単な操作でデータ整理できないですかね？

2022/03/25(金) 21:45:45.83

>>549
何だろう、クエリってSQLも立派なクエリだよ？
そっちの言ってるクエリの定義が分からない。
アクセスとかにあるあのツールっぽいのを言ってるのかな？

2022/03/25(金) 21:46:14.94

普通にマクロの記録使ってシート1の範囲をコピーして
シート2の開始地点で貼り付けたらいいだけじゃないの…

2022/03/25(金) 21:47:30.56

そうすると横にデータを並べていきたいので重複していきます
シート全体の数もそのときどきで変わるので

2022/03/25(金) 21:50:02.56

？？？
どんどん何をしたいのか分からなくなってくるね

2022/03/25(金) 21:51:28.75

>>553
簡単に言うと、複数シートにまたがる複数列の１から４列のデータを、
１枚のシートの横に縦の行を順番に並べていきたいのです

2022/03/25(金) 21:56:14.91

ｽﾞｺｰｯ !