Excel VBA 質問スレ Part67

2020/06/26(金) 02:01:29.56

!extend:checked:vvvvv:1000:512
↑同じ内容を2行貼り付ける

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

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

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

2020/08/02(日) 13:42:27.44

俺らそういうのいやだから
重複しなさそうとか認めないし

2020/08/02(日) 14:04:53.69

ベースモデル考えて確率的に速い手段を選ぶだけ。
コード書くのが好きなら計算結果を配列に入れて、既定カウントに達するまで回せばいいけど、時間の無駄。

2020/08/02(日) 14:52:01.85

条件にもよるけど今回の話の場合は1回目重複覚悟でやってから重複分だけ再度重複なしの方法でやるのが速そうだね

2020/08/02(日) 15:17:38.22

時間かかるってそんなの５分で終わるし

2020/08/02(日) 18:40:53.06

>>751
ﾜｯﾁｮｲって何かわかる？

2020/08/02(日) 19:11:50.62

>>752
実際にコード書いたから５分かかるっていってんだろぼけなす
てめぇみたいに他人のﾜｯﾁｮｲに執着する気持ちの悪いはげおやじじゃねぇんだよぉ

2020/08/02(日) 22:26:59.52

>>747
A列：連番
B列：口数
C列：当選確率 = 口数/口数の合計
D列：点数 = 当選確率*RAND()
これでソートかけたほうが簡単じゃね
300番付近のボーダーラインで点数の重複が生じたら
重複者のみを対象に再度抽選
まだ重複者がいたらさらに再度抽選（・・・）

再抽選回数が0～300と未定だけども現実的には多くても数回で終わるだろうよ

2020/08/02(日) 22:40:10.04

>>754
そちらの方がいいですね。

2020/08/03(月) 02:34:06.94

それだと当選確率が公平にならない気がする
まあコーディングの手間と計算時間と公平さとどっちを取るかだな

2020/08/03(月) 05:36:57.74

一回の抽選ごとに当選者を省いていけばいとおもうが

2020/08/03(月) 06:39:58.48

数学的に「当選者を除外しながら次を抽選」で公平になる

A列に応募者を一列に並べて、2口応募なら2回並べて、存在するデータの行数*RAND()+1で抽選すれば、自動的に口数に比例して当選確率が上がる
当選者はどこかに覚えておいて、A列から除外してから次を抽選

300人の抽選なんて1秒もかからないし、プログラムもきわめて単純、作業列も必要ない

2020/08/03(月) 11:15:41.27

>>756
当選の公平さを取らないと企業として問題あるよ

2020/08/03(月) 11:17:37.07

5ちゃんに書き込んでる時点でお察し

2020/08/03(月) 11:20:43.87

もう無理して数式でなんとかしようとするよりVBAでさっさと作った方がややこしくないし手っ取り早いんじゃないの
それなら処理時間なんか気にする必要も無し

2020/08/03(月) 12:19:51.87

や～い、お察しお察し～

2020/08/03(月) 12:31:15.20

いぜん懸賞の雑誌で架空の人物に当選したとかやってたな

2020/08/03(月) 12:34:55.84

発表は発送をもってかえさせろってのは
みんなインチキだよ

運ちゃんがボスジャン着ていたのは役得

2020/08/03(月) 12:41:50.97

抽選システムなんて大概再使用されるだろうから手作業で当選者除去とかやらないといけないならVBAで組んじゃったほうがいいだろうな

2020/08/03(月) 13:54:58.27

つかVBAの乱数とか公平といえるんかね
本当に公平な抽選は第三者立ち合いで見えるようにやるんだが

2020/08/03(月) 14:42:43.98

プログラムによる抽選が不正扱いなら全国の企業が謝罪案件になるが・・・

2020/08/03(月) 14:57:06.86

第三者立ち合いで乱数生成を！？

2020/08/03(月) 15:12:09.39

パチンコ屋はあやしい

2020/08/03(月) 15:30:38.22

立会人を選定するための立会人が必要になるな

2020/08/03(月) 18:38:47.21

立会人って居るだけじゃねーの

2020/08/03(月) 18:41:49.78

座って接待を受けるだけの誰にでも出来る仕事

2020/08/03(月) 20:25:24.18

Sheet2で使うユーザーフォームにSheet1から参照したデータをコンボボックスに表示させ
CMBBox1の入力内容に合わせCMBBox2を連動させたいのですが

CMBbox1はこんな感じでデータを拾っています。
CMBBox1.RowSource = "Sheet1! H3:H" & Worksheets("Sheet1").Range("H" & Rows.Count).End(xlUp).Row

CMBBox2にCMBBox１が入力されると、参照列(H列)の4つ右のセルを表示させるにはどうしたらよいでしょうか
（CNBBox1にSheet1のH3が入力されると、CMBBox2のリストにSheet１のL3が表示される）

似たようなコードを拾ってきたのですがこれを弄って使えるようにしたいです。
Private Sub CMBBox1_Change()
Set ws = Worksheets("Sheet1")
Col = CMBBox1.ListIndex + 12
品名CMB2Box.Clear
For i = 1 To ws.Cells(Rows.Count, Col).End(xlUp).Row
CMBBox2.AddItem ws.Cells(i, Col).Value
Next
End Sub

よろしくお願いします

2020/08/03(月) 21:06:03.90

リストに1件だけ表示っておかしくね？
そのデザインは却下だ

2020/08/03(月) 21:11:33.10

listindexとoffset使えばいけんじゃね?

2020/08/03(月) 21:37:39.33

A列最終行の次の行から、下方向150行に本日の日付を表示させたいのですが、
どう記述して良いのかわかりません。
ぐぐって最終行取得のコードまでは見つけたのですが、指定範囲に日付表示のコードを
見つけることが出来ませんでした。
お手数をおかけしますが、ご教授をお願いします。

2020/08/03(月) 21:49:07.37

>>773
自分で考える気なさそうだな
その拾ってきたコードのどこが分からんのだよ？

それが出来てそのあとどうする気か知らんが
CMBbox2.Clear
CMBbox2.AddItem (Sheet1.Cells(CMBbox1.ListIndex + 3, "L").Value)
これで、二度と来るな

2020/08/03(月) 21:53:06.72

また馬鹿が出てきた

2020/08/03(月) 23:28:33.53

>>776
簡単なのはループで入れる。

r=最終行 + 1 to 最終行 + 150
cells ( r, 1)= date
next r

**779** (ｱｳｱｳｴｰ Sa22-gQbI) · 2020/08/03(月) 23:29:45.08

for とか、変数宣言とか抜けてるけど、そこはがんばって補完して。

2020/08/03(月) 23:40:17.65

forなんて使わなくてもいいんじゃね

Sub test()
Dim i As Long
i = Cells(Rows.Count, 1).End(xlUp).Row
Range(Cells(i + 1, 1), Cells(i + 150, 1)) = Date
End Sub

**779** (ｱｳｱｳｴｰ Sa22-gQbI) · 2020/08/03(月) 23:44:27.96

なるほど、それはそうだ。
失礼しました。

2020/08/04(火) 00:12:45.83

>>780
ありがとうございます！
あとは自力で頑張ってみます！

2020/08/04(火) 00:17:38.16

>>781
解答ありがとうございます！
1つの答えに対し複数のコードがあるって面白いですね！
初歩的なコードが書けるよう頑張ってみます。

2020/08/04(火) 00:51:16.26

そういう感想が出るってことは、答えを出すための手順を全く自分で考えたことがないのでは…

2020/08/04(火) 01:06:00.92

>>784
死ねよ

2020/08/04(火) 02:59:27.48

RNDやRAND関数は疑似乱数だから公平じゃない
そもそもコンピューターで乱数を生成するのは不可能

知識としては知ってるけど、代替手段を考えるのも面倒だしみんなRNDを使ってるから別にいいんだよ、ってこと

2020/08/04(火) 05:51:29.11

そのための乱数シードってのがある

2020/08/04(火) 06:07:40.64

同じシード与えたら同じ乱数列吐くよね。
で？何のためにあるって？

2020/08/04(火) 06:08:47.59

もう最大限のことも自分で考えない馬鹿には答えなくて良くね？
多分何度も聞きに来るぞ

2020/08/04(火) 06:09:01.26

最低限ねw

2020/08/04(火) 06:22:53.51

>>789
同じシードを与えないためだろ、使い方知らんのか？

2020/08/04(火) 07:55:02.77

>>787
コンピュータという広域の括りであれば
乱数を発生させるのは不可能ではないよ

2020/08/04(火) 09:23:33.17

数式で「真の乱数」を発生させることは不可能でしょ
何かしらの雑音を外部から取り入れるならあり得るけど

しかし真の乱数と区別できない疑似乱数というのはある
たかだか数万個の乱数を発生させる程度なら普通にRANDで構わんよ
コンピューター空間内（例えば64bit)なら32bitの疑似乱数を発生させ得る

2020/08/04(火) 09:36:35.85

excel起動時のシードが同じだから変えたほうがいいとおもう

2020/08/04(火) 12:47:00.19

>>794
数式で真の乱数を発生させることが出来ないのは多分そう。ただ、それでも真の乱数は発生出来る。

ただ、かなり特殊な環境で言語的にもかなり特殊なのでVBAだけやってる人にはまぁ分からないだろう。

ただそれは、一度世界で最も出回ったハードであり言語でもあって、それでは乱数を求めるのに至極当たり前の方法でもあったから
このスレにいるおじいちゃんにはひょっとして知ってる人もいるかも知れない。

2020/08/04(火) 12:54:38.56

乱数こそこのスレで教えてくれた最高の武器
15個～20個しか評価してないデータを50個に乱数使ってn増ししてるわ
プロシージャ名は乱れ雪月花にしといた

**デフォルトの名無しさん** (JP 0H62-3SEW) · 2020/08/04(火) 13:32:34.79

学術の巨大掲示板群 - アルファ・ラボ ttp://x0000.net
数学物理学化学生物学天文学地理地学
IT 電子工学言語学国語方言など

simulationライブラリで純粋な関数式プログラミングをする
UIライブラリ (C#, 2D) を作ったよ
連続と離散を統一した！
4Dエンジン
matrixのライブラリ
ある強力なFor関数
SQLライブラリ

☆ VM + ASM を書いた (C#, DX) * x86 ではない!
ttp://up.x0000.net/files/TSimulang.zip
☆ malloc / free を実装してみた (C#)
ttp://up.x0000.net/files/TMallocTest.zip

2020/08/04(火) 20:23:21.05

2016使ってるけど、いつの間にかSORTBYとかFILTERとか使えるようになってるのな。

2020/08/04(火) 20:58:22.81

そもそも乱数に偏りがあろうと応募者が狙った並びでない時点で機会は公平だけどな

2020/08/04(火) 21:08:04.57

決まった番号がでるとわかっているからその番号に身内を入れて置けるわな

2020/08/05(水) 07:02:57.17

>>797
偏差値低くね？

2020/08/06(木) 11:58:31.88

乱数で疑似データをつくるとばらつきが均等になるから偏差を求めたときに不自然に広がるよね

2020/08/06(木) 13:28:48.79

乱数２つ使うといいぞ

2020/08/07(金) 19:45:16.94

スレチだったらｽﾏｿだけど、excelのスピル？のせいで、ver違うexcel間で共有してたファイルが勝手に配列やら@やら数式が変換されてバグってんだけど、これ無効にできないの？

**デフォルトの名無しさん** (ｱﾒ MM75-cUya) · 2020/08/07(金) 20:09:47.44

>>805
キミにピッタリな格言を贈ろう。
It is no use crying over spilt milk

2020/08/07(金) 20:45:55.98

できないの？

2020/08/07(金) 20:47:34.25

>>807
最近追加されたアレね
分からん。田中先生にでも聞いたら？

2020/08/07(金) 21:11:23.51

え・・・まじで無理なん？
おわってんな

2020/08/07(金) 21:38:31.44

最近の機能の不具合だから知らねってことやろ

2020/08/07(金) 22:08:09.38

旧バージョンの配列式に勝手に＠が付くのは仕様じゃねえの
旧verの {=a1:a3} が新verでは =@a1:a3 に変換される
新verのスピル式 =a1:a3 は旧verでは {=a1:a3} に

VBA的にはFormulaプロパティとFormula2プロパティを使い分けることによって新旧verどっちにするかを指定できる

**デフォルトの名無しさん** (JP 0Ha3-3Z56) · 2020/08/08(土) 13:26:33.40

パーフェクトExcelVBAの初版本の正誤表とサンプルコードってどこかにありませんか？

2020/08/08(土) 14:27:44.52

出版社に聞いたら

2020/08/08(土) 15:27:34.88

>>813
たし蟹

2020/08/09(日) 10:06:54.07

コレクションにaddしたシートだけを一発で選択するにはどうしたら良いですか？

2020/08/09(日) 10:50:04.26

>>815
addしたあとから調べる方法はないからaddする時に変数に覚えておくしかない

2020/08/09(日) 13:28:26.53

セルA1~A5に1~5が入っていて、5個のaverage=3ですが、
1) 複数の連続セルを選択することで同じ動作をするfunction
2) function 自作平均値(セルを1個指定する as ???, 上のセル数 as byte, 下のセル数 as byte) as double
で、=自作平均値(A3, 2, 2)　としたら同じ結果を得る関数
ってそれぞれどう書けばよいでしょうか。
値を直接入力する予定はなく、セルアドレスを取得できれよいかと思いますが、
方法がわかりません。
参考urlでかまいませんので教えてください。

2020/08/09(日) 13:55:27.58

>>817
自作の関数の中で、ワークシート関数とまったく同じ結果が欲しいなら、Application.WorkSheetfunctionを使うのが一番確実

参考サイトに基本的な作り方は書いてあるから、計算式の部分だけ自分で作り直せばいい
https://kokodane.com/tec3_3.htm

2020/08/09(日) 21:56:13.30

ITに疎いクライアントのために
デモ用のVBAが動作しているところ録画して見せたいのですが
そのために向いているツールとかありますか？

2020/08/09(日) 22:02:16.81

まずは標準のステップ記録ツールでダメな点を書けよ

2020/08/09(日) 23:01:18.40

>>817
Function 自作平均値(セルを1個指定するAs Range, 上のセル数, 下のセル数)
自作平均値 = Application.WorksheetFunction.Average(セルを1個指定する.Offset(-上のセル数).Resize(上のセル数 + 下のセル数 + 1).Value)
End Function

文章が意味不明なんだけどこんな感じでいいの？

2020/08/10(月) 01:01:45.89

>>819
Excelの画面をビデオ録画する操作はExcelを起動してから田+Alt+R
ただしVBEのウィンドウは開いても重ねても録画されないので、ワークシート上にマクロの起動ボタンを置いておく必要がある

2020/08/10(月) 06:44:41.63

>>819
win10なら標準で画面録画ツールあるよ
win+rだったかな

2020/08/10(月) 08:03:08.52

822で解決してる内容を間違って紹介するの巻

2020/08/10(月) 08:12:31.43

>>820の時点で普通は調べりゃわかる

2020/08/10(月) 08:49:14.88

>>815
そういう時はDictionaryにすれば良いんでね?

2020/08/10(月) 08:51:12.59

>>819
そのVBAを自動実行するVBAを書く。

2020/08/11(火) 02:22:17.18

VBAの知識は全く無いけど無駄な事は少しでも省きたい
そこで質問なのですが、複数の結合されたセルの値を一括でクリアしたい時
Range(“A1”).MergeArea.ClearContents
Range(“A2”).MergeArea.ClearContents
Range(“A3”).MergeArea.ClearContents
Range(“A4”).MergeArea.ClearContents
ひたすら続く

これを短くするにはどうすれば良いでしょうか？

2020/08/11(火) 02:42:12.83

A1:A1000

2020/08/11(火) 03:03:48.27

あーっとすみません
AはBと連結していますのでそれぞれが横に連結しています

2020/08/11(火) 03:10:45.62

>>830
死ね

2020/08/11(火) 03:24:29.64

>>828
まずセルの結合が無駄の元だから禁止するのが一番の早道

2020/08/11(火) 03:32:11.78

>>832
やはりそうですよねぇ
既存のフォーマットに寄せようと思うとどうしても結合セルが必要に...
色々試行してみます。

2020/08/11(火) 07:15:12.76

悪いけどこの程度のこと質問するようじゃ手でやったほうが早いと思う
該当セルを全部選択して右クリックから値のクリア

2020/08/11(火) 07:29:46.25

>>834
それをマクロの記録で記録するのが一番楽だわな

2020/08/11(火) 09:13:15.51

>>835
この程度なら、毎回やるにしてもマクロ使わずに手でやって余裕でできるレベルだろ

2020/08/11(火) 09:43:23.03

できるかできないかじゃないのがわからない奴

2020/08/11(火) 10:26:55.02

>>828
きったないコードの書き方になるが
Din i as Long
For i =1 to 最後の数
Range(”A” & i).以下略
Next i
で簡素化

2020/08/11(火) 12:32:13.53

range("a1:最後").value=emptyじゃだめなのか

2020/08/11(火) 12:34:29.17

>>828
Dim i 　　　As Long
Dim lngMaxRow As Long

'/// 最終行取得
lngMaxRow = ActiveSheet.Range("A65000").End(xlUp).Row

'/// ループ処理
For i = 1 To lngMaxRow
ActiveSheet.Range("A" & i).MergeArea.ClearContents
Next i

2020/08/11(火) 14:42:39.58

.Clear と .ClearContents と .Value=Empty の違いがわからん
これをマウスやキーボードで再現する方法も
たとえばセルを選択してDeleteキーはどれに相当？

2020/08/11(火) 15:03:15.53

Clearはセルに入力されてる数式や値、そして設定されてる書式もすべて消す
.ClearContents は数式と値のみクリア、書式は残る
.Value=Emptyはニュアンスが逆で"何も無いを入力"、つまり消す。.ClearContentsと同じ

deleteキーは.ClearContentsや.Value=Emptyと同じ

2020/08/11(火) 15:15:55.64

不規則な結合セルがあると .MergeArea.ClearContentsはエラーになるけど .Value = Emptyなら動くから>>828はRange("A:A").Value = Emptyの一行で解決ってこと

2020/08/11(火) 15:48:36.99

foreachやればええやん

2020/08/12(水) 02:04:20.40

>>834-844 ありがとうございます！
皆さんが言うように全くの知識ゼロですが色々検索してなんとか思った通りのボタンが完成しました。
しかし全角やらスペースやら分かり辛いプログラムって難しいですね...

2020/08/12(水) 20:55:54.61

赤線先生が手取り足取り教えてくれるのに何が不満だと申すのかこのガキは

2020/08/12(水) 23:02:53.31

n行ごとにデータを拾って少し文字列等を足してからほかのシートに貼り付ける、
という作業を現在For文でやっています。行数が多いと多少時間がかかることも
あるので、配列でできないかと思い試してみたのですが、「n行ごと」の部分が
うまくいきません

Dim tmpArr(), tmp As Variant
Dim rowIndex, colIndex As Long

tmpArr = Worksheets(1).Range("A3:C101").Value
For rowIndex = 1 To UBound(tmpArr) - 2 Step 3
For colIndex = 1 To UBound(tmpArr, 2)
tmp = tmpArr(rowIndex, colIndex) & "結合データ"
tmpArr(rowIndex, colIndex) = tmp
Next
Next

Worksheets(2).Range("A3:C35").Value = tmpArr

上記の場合3行ごとに元の値に「結合データ」という文字列を足すことはできて
いるのですが、貼り付け先のシートに全行貼り付いてしまいます
3行ごとの場合に要らない2行目、3行目を除外して
Sheet1 1行目→Sheet2 1行目、Sheet1 4行目→Sheet2 2行目…
というように貼り付けるにはどこを変更したらいいでしょうか

貼り付けたあと要らない行を削除してみたのですが、却って時間がかかって
しまったため、それ以外の解決方法があれば教えてください、よろしくお願いします