Excel VBA 質問スレ Part62

2019/06/24(月) 00:21:37.48

!extend:checked:vvvvv:1000:512

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

※前スレ
Excel VBA 質問スレ Part61
http://mevius.5ch.net/test/read.cgi/tech/1556203263/

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

2019/08/31(土) 09:56:21.35

西暦2100年そこにはいまだにVBA を書く社畜の姿が

2019/08/31(土) 10:01:54.51

>>819
こんな感じにデータベースファイルみたいなのが100個くらいのbookに分かれてるんですよね
それを1個のbookで参照・集計してアナライズしたいんですけど
自分で書いててアレなんですけどやっぱ明らかにexcelでやることじゃないんですよねーｗｗもうどうしようかしらん

2019/08/31(土) 12:03:18.46

>>820
デカくて潰せない会社と同じ
批判はあっても存続
他言語導入でも併存

他言語になっても、新たに覚え直す程の魅力がなければワザワザ移行しない
そんな言語があるとも思えない

2019/08/31(土) 13:10:01.15

もう既に基本的なことはpythonのopenpyxlで叩く方が楽だけどなｗ

2019/08/31(土) 13:27:16.71

pythonは入れだけでつらいから

2019/08/31(土) 13:54:41.11

>>818
まず、kore互いに参照し合う構成にすること自体が間違ってる
book2のA2はbook2のA1*2でいいんじゃないのか

2019/08/31(土) 13:56:14.97

Excelは互いに参照し合う構成にすること自体が間違ってる

2019/08/31(土) 14:33:22.19

>>827
book1に1日目のりんごとみかんの売り上げ個数が書いてある
book2に2日目のりんごとみかんの売り上げ個数が書いてある

book3で1日目と2日目のりんごとみかんの売り上げを表示したい、またbook3で書く売り上げに用いる消費税というパラメータを定義してある

こんな構成の時ってどうすればいいの？
今はbook1とbook2でbook3の消費税パラメータを参照して計算して、book3で計算結果を吸い上げてる

2019/08/31(土) 14:39:04.14

横からだが、その構成途中で値上がりしたときにやばくないか？

2019/08/31(土) 14:41:09.90

>>828
book3が最終形態ならbook1とbook2から個数を参照してbook3で料金計算

2019/08/31(土) 14:58:39.86

>>829
例えなので、実際は単価が上がることはなく、種類は100種類程度のデータです。
book3で全日付のデータ吸い上げて1日の売り上げ個数をbook3の別シートに仮保存して計算というのもしましたが、
日数分だけシートが増えてくので、。
欲しいのは売り上げ金額だけなので、無駄なデータは増やしたくないのですが・・

2019/08/31(土) 15:29:32.08

>>831
なら尚更構成見直した方がいいと思う
なんであっち見たりこっち見たりするんだ…

2019/08/31(土) 20:18:57.86

教えてください
ctrl+cでセルを値でコピーするのと同時に、セルか文字に色をつけたいのですがなかなか作れません
ヒントあれば教えて下さい

2019/08/31(土) 20:25:39.22

>>833
色だけコピーしたいと言ってんの？

2019/08/31(土) 20:38:21.36

>>834
いえ、コピーしたセルがわかるように色付けしたいです

2019/08/31(土) 20:44:10.45

>>833
マクロを
・色付
・コピー
にしてショートカットに登録

ctrl+cではなくそのショートカットでコピーする

2019/08/31(土) 20:51:57.67

>>832
どう見直しても解決出来そうになくて・・
そんなに特殊ケースでもないと思うのですが、世間の人々はどんな構造にしてるのでしょうか。

2019/08/31(土) 20:52:51.76

>>836
ありがとうございます。
ctrl+cでやるのは無理なんですか？

2019/08/31(土) 21:41:30.00

>>838
作ったマクロを ctrl+cに割り当ててみ

2019/08/31(土) 21:44:06.08

>>837
日報と同じファイルに単価とか共通の固定データ用のシートを1個作る
日報がどんなレイアウトか知らないが↓みたいなのと仮定する
　　　　　　8/1　8/2…
　りんご　
　みかん
sumifで1日分の個数を取得×Vlookupで単価取得
個数のファイル名はyyyymmddとかにして数式コピペできるようにしとく

2019/08/31(土) 21:46:45.55

日報て集計用のファイルな

2019/08/31(土) 23:13:48.46

>>833
死ね、クズ

2019/09/01(日) 02:37:04.86

>>842
すみません

2019/09/01(日) 04:01:41.09

コピーはCtrl+Cだけとは限らないからクリップボードを監視して
対象のシートがアクティブな間に状態が変わったら選択セルに色を付けるだけ
個人で使うだけなら適当でいいんじゃないの

2019/09/01(日) 13:44:49.90

Ctrl+Cでやりたいと言ってるんだから面倒なことしなくていいと思うが

2019/09/03(火) 17:37:57.38

A1を始点に行方向に5,列方向に4の間隔で格子状に存在するセルの先頭の文字だけを赤くしたいのですがどうやれば可能ですか？

2019/09/03(火) 17:50:49.52

>>846
Sub Macro1()
　　For r = 1 To 100 Step 5
　　　　For c = 1 To 100 Step 4
　　　　　　Cells(r, c).Characters(Start:=1, Length:=1).Font.Color = vbRed
　　Next c, r
End Sub

2019/09/03(火) 18:00:40.88

今日、Excelの表で手動でソートしてたら変な挙動が現れ、原因が分からず困ってしまい…

まず、「状況」列に、「完走」と「未達成」のデータがあるのですが、
この列を昇順ソートすると、「未達成」の上と下に「完走」が分かれてしまいました
もちろん降順ソートも同様です

で、この上下の「完走」を別のシートにコピペで貼り、挙動をチェックしてみましたが…
　https://i.imgur.com/FXwnn0h.jpg

上のリンクでも書いていますが、文字自体はif文でもフィルターでも同一扱いです
（ソートの時だけ、なぜか違いが出ています）

思い当たる違いは入力の仕方で、手打ちしたものとマクロ（VBA）で入力した違いがあります
マクロを作って以降はマクロで入力してますが、それ以前のデータは手打ち＆コピペで入力しました

とりあえずの対処法として、マクロで入力した文字を全てにコピペすればこの問題は解決しますが
今後のこともあり、なにか見落としている点や抜本的な対策がないか、
何かアドバイスを頂ければと思い質問しました。

なお、これは仕事とかじゃなくて趣味でやってるものです
あと、使ってるのは Excel2007 で、ちょっと古いバージョンです

以上、よろしくお願いします

2019/09/03(火) 18:37:10.54

>>848
ふりがな？

2019/09/03(火) 19:03:22.22

>>849
それでした！

「Excel、ソート、ふりがな」でググると出てきました
https://whiteleia.com/%E3%82%A8%E3%82%AF%E3%82%BB%E3%83%AB-%E4%B8%A6%E3%81%B9%E6%9B%BF%E3%81%88-%E3%82%BD%E3%83%BC%E3%83%88-%E3%81%A7%E3%81%8D%E3%81%AA%E3%81%84-%E6%BC%A2%E5%AD%97/

Sortオブジェクトで並べ替えを実行する
http://officetanaka.net/excel/vba/tips/tips148c.htm

いろいろ見てみると、Excelでソートをする際の（隠れた？）重要事項のようですね
（見えない「ふりがな」で漢字も正しくソート！、親切な機能ですが…やめてくれ～！！）

2019/09/03(火) 19:08:44.32

>>850
大を「おおきい」で変換するか「だい」で変換するかで位置が変わるのと同じやね

う
え
大 (おお)
か
き
大 (だい)

2019/09/03(火) 19:09:53.69

シュワルツ変換じゃないんだな
やばいね

2019/09/03(火) 19:30:59.43

>>850
デフォで ONなのがあかんよね

2019/09/04(水) 03:28:35.08

ふりがなは集計とかで計算が合わない原因の1つ
非常にヤバイ

2019/09/04(水) 04:06:51.76

俺ならorder byでソートするわ。

2019/09/04(水) 04:06:51.76

俺ならorder byでソートするわ。

2019/09/04(水) 04:10:04.07

あ、勝手に2回書き込まれた。
2回押したわけじゃない。(時間が同じ)

2019/09/04(水) 06:58:53.39

同時にマルチポストするスクリプトのテストなんでしょ？わたししってます

2019/09/04(水) 09:41:13.02

それ何の意味があるの?

2019/09/04(水) 21:10:47.96

utf8でテキストファイルを読み書きするにはADODB.Stream使うしか無いんですか？

2019/09/04(水) 22:19:41.54

>>860
お前にはできないだけ

2019/09/04(水) 22:43:43.48

>>860
テキスト取り込み機能の、文字コードの選択肢に、
普通にUTF-8入ってるけど。

2019/09/05(木) 05:41:49.26

>>862
詳しく

2019/09/05(木) 19:27:53.95

>>863
データ → テキストまたはCSVから → テキストファイル選択 → インポート → 元のファイル「65001:Unicode (UTF-8)」

バージョンによって、多少表記は違うと思うけど。

2019/09/05(木) 20:11:50.19

>>864
ありがと、試してみる。

utf8でファイルへ書き出す方法はある？
ADODB.Stream以外で。

2019/09/05(木) 21:14:36.97

>>865
なぜDODB.Streamを避ける？参照設定ならVBAで追加できるぞ

2019/09/05(木) 21:28:31.64

>>866
ゴメン、それをここで議論するつもりは無いんだ。
utf8でテキストファイルを読み書きするにはADODB.Stream使うしか無いのか知りたいだけ。

2019/09/05(木) 21:35:39.87

言い直す。
utf8でテキストファイルを読み書きするのにADODB.Stream以外の方法があれば知りたい。

2019/09/05(木) 21:37:45.62

言い直す。
utf8でテキストファイルを読み書きするのにADODB.Stream以外の方法があれば知りたい。

2019/09/05(木) 21:39:34.16

大事なことなので

2019/09/05(木) 21:47:14.25

こだわりって奴か

2019/09/05(木) 22:05:29.23

どんな方法でもいいのなら、文字コード変換プロシージャを自作すればいい
変換テーブルはネットに落ちてる

**デフォルトの名無しさん** (ｽﾌｯ Sd62-fUZA) · 2019/09/05(木) 22:10:54.25

>>868
APIで変換できる
ADODB.streamも内部では同じAPIを呼んでるはず
http://www.t-net.ne.jp/~cyfis/win_api/sdk/MultiByteToWideChar.html

2019/09/05(木) 22:29:23.75

.netのライブラリ呼び出すとか？
ADODBを避けたい理由がわからんけど

2019/09/05(木) 22:37:00.77

違う視点で調べてみるとか、
UTF-8じゃなくて、中国語をどうにかしたい、とかさ。

2019/09/06(金) 01:25:28.43

>>868
方法はいくつもある
無料のWebAPIもあるし、コマンドラインでnkfを呼び出したり、テキストボックスのプロパティをいじったり
あとは理由、目的、条件などをはっきりさせてくれないと話の続けようがない

2019/09/06(金) 09:13:01.42

参照とかAPIとか事務のおばちゃんには難しいのでは？
VBAを使う理由って何もさせてくれない設定だったよね？

2019/09/06(金) 09:20:07.41

何設定って
ぼく働いたことないから実態わかりませんてこと？

2019/09/06(金) 12:46:48.43

・2列の一致する組み合わせを数える方法(ex.A列に野菜B列に果物が書かれている時AがトマトかつBがりんごの行をカウントしたい)

・数式の結果の文字の先頭を色付けする方法(ex.A1セルに【=IF(条件式,"☆優先","▽後回し")】が入力されていてその結果の文字の先頭の記号だけを色付けしたい)

この2つのやり方教えてください。

2019/09/06(金) 13:17:28.93

>>879
対象範囲をForで回して評価します。
式で表現されたセルの部分文字列の書式を違えることは出来ないんじゃ？

2019/09/06(金) 13:22:25.08

>>879
最初の方は何をしたいのか意味が分からない。
トマトが野菜かどうかはどうやって判定するの?

2番目の方は数式の結果はそのセルのValueで取れるから数式関係無くね?
VBAでやりたいのか数式でやりたいのかも不明。

2019/09/06(金) 13:24:03.74

馬鹿の書いた仕様にマジレスか

2019/09/06(金) 17:23:07.64

>>881
トマトが野菜かは関係無いだろ

2019/09/06(金) 17:29:43.22

for each c in [a2:a20]
if c.value & c.offset(,1).value ="トマトりんご" then cnt =cnt + 1
next
msgbox "うんこ"

2019/09/06(金) 17:45:39.03

トウモロコシは野菜？

2019/09/06(金) 18:20:35.01

トマトは野菜だがフルーツトマトはフルーツである。○か×か？

2019/09/06(金) 18:51:20.65

2019/09/06(金) 21:07:32.81

オーノー

**デフォルトの名無しさん** (ｽﾌｯ Sd62-TE+O) · 2019/09/06(金) 21:47:07.49

>>881
このスレにわざわざ書き込んでるんだからさすがにVBAでやりたいんだろうと思うけど。

2019/09/06(金) 22:15:50.48

>>879
一つ目は数式でできるがVBAでないと駄目なのか？

2019/09/06(金) 22:33:33.84

質問ですが、ExcelのワークシートとVBAとでは、日付の扱い（起点？）が異なるのでしょうか？

例えば、数字の「５」をDate型で表示すると、
　・ワークシート：　1900/01/05
　・VBA　　　　　：　1900/01/04
　https://i.imgur.com/8Iq99R4.jpg

このように１日ズレますが、これはどういう事なのか…、これで合ってるのでしょうか？
（Excelのバージョンは2007です）

何かアドバイスを頂けると幸いです

2019/09/06(金) 22:47:22.58

https://www.tipsfound.com/vba/05cdate
CDate
値に数値を指定したときは、日付型の初期値 1899/12/30 0:0:0 にその数値の日を足した日付に変換します

https://www.officepro.jp/excelfunc/date/index0.html
日付のシリアル値は、1900年1月1日が基準日となりシリアル値は「1」となります。

らしいぞ

2019/09/06(金) 23:27:12.11

>>892
ソースのリンク先まで貼って頂き、ありがとうございます。
シリアル値の「１」を日付で表示した場合、やはりワークシートとVBAで起点が１日ズレてるんですね

「期限の５日前までのものを抽出する」って操作をワークシートでやってたのですが
これをVBAに置き換えたら何か結果が変で…、しばらく変だったことにも気付かず…

とりあえず、そういうものと思って注意して使うしかないですね
しかし>>848のソートもだけど、いろいろ思いがけないことがあるもんですね

2019/09/07(土) 06:45:32.58

勤務表の作成の相談です。
以前も少しさせていただいたのですが、下記の条件で作ることは可能ですか？

1月の勤務時間160時間週40時間
週休2日
月に1度は2連休を入れる
勤務形態の組み合わせは2種類
（1）昼8、昼12、遅8、夜12
（2）昼8、遅8、夕8、夜8
夜12、夜8の次の日は休みで無くてはいけない
昼12.遅8の次の日は昼8、昼12を入れてはいけない
勤務の希望や休みの希望は必ずその通りにしなくてはいけない
経験年数によって行える業務も違うため、どの日も均等に業務ができるように組まなくてはいけない
業務内容は☆、△、□、◇、●の5種類

2019/09/07(土) 08:49:24.82

>>894
質問は可能かということだから答えるけど可能

コード晒せとか言うのであれば
長くなるのでここでは不向き

どんな形態の勤務表作るのかは知らないけど
ボタン押して勤務表の記載内容をチェックして
ダメならメッセージボックスでその旨表示して
ダメなセルを着色かなんかするようなイメージ？

2019/09/07(土) 09:13:25.23

盆正月GW諸々考慮しないでいいのか心配になる内容
完成してからの修正が本番になる予感

2019/09/07(土) 09:35:53.34

そして1から作り直した方が早い案件に

2019/09/07(土) 11:53:44.36

879
すいません、一つ目はcountfsでやれました。
2つ目が出来ません
直接、☆優先、とだけ文字を書いたところなら先頭だけ赤くできますが計算結果の文字の一部を赤くするにはどうすればいいですか？
関数でもVBAでも何でもいいです。openpyxlでも。

2019/09/07(土) 12:19:08.18

Range("A1").Characters(Start:=1,Length:=1).Font.ColorIndex = 3

2019/09/07(土) 13:03:36.73

>>898
アンカの付け方覚えて

2019/09/07(土) 13:27:28.99

>>898
これは俺が悪かった。
数式に対してはセル内文字列の一部分に対して書式の設定は出来ないようだ。
条件付き書式で出来るかと思ったけど、条件付き書式もセル内文字の一部分に適用出来ない。

つまり、出来ないと思う。

2019/09/07(土) 13:39:34.44

固定値だったらできるのにね

2019/09/07(土) 19:11:14.74

>>895
勤務、休み希望を先に選択し残りの空白部分は自動で組めるようにしたいです。

2019/09/07(土) 19:34:32.24

判定や入力もVBAでやってRange.caractors(1,1)から変えればいいじゃん

2019/09/07(土) 20:03:22.01

>>904
直接数式の入ったセルに対してやりたいんだろ。

2019/09/07(土) 20:04:40.69

数式の入ったセルにRange.caractors(1,1)で書式変更しても反映されない。

2019/09/07(土) 20:05:24.41

>>903
自動部分のパターンが明確に決まってるなら出来るよ

**デフォルトの名無しさん** (ｽﾌｯ Sd62-TE+O) · 2019/09/07(土) 20:07:08.23

>>906
根性が足りないだけでは。

2019/09/07(土) 20:19:51.67

>>907
自動部分のパターンとは？

2019/09/07(土) 20:30:18.66

フツーに行単位でセルの色を変えた方が見やすいんじゃないの

2019/09/07(土) 20:38:50.56

>>909
自動で勤務の振り分けをしたいんじゃないのか？

2019/09/07(土) 20:44:02.71

セル分けれは解決じゃん

2019/09/07(土) 20:48:53.79

（ほぼ）リアルタイムに状況見ながら各自が休みとか入れさせるのか、単に休みの希望だけオフラインで集めて、スケジュール担当者だけがシステム触るのかでも難易度が全然違う

今時やるなら前者で行ってもらいたいものだが、仕様考えながら完成まで一ヶ月でできるかも怪しい気がする

2019/09/08(日) 06:46:04.07

先頭の記号一文字分だけ色をつける仕様が糞仕様だと気付くことが必要だよな

2019/09/08(日) 10:33:26.83

いやでも実際文字全体をカラーにされると読みづらいから先頭の色付記号だけでアテンション引いてくれる方が見やすくてデザイン性は高いよ

2019/09/08(日) 10:51:40.94

1列追加すればいいだけだわな

2019/09/08(日) 11:08:41.98

俺はセル内改行された文字列のセル内1行目だけを
フォント大きめで色を付けて強調させるマクロをよくつかう

2019/09/08(日) 11:26:28.31

それでも直接文字列でしょ？
IFで選択した文字列に使えないのが雑魚すぎる
VBAマジでアップデートしないかなぁ
今時continueするためにラベル+gotoが必要とかマジでお笑いだわ

2019/09/08(日) 11:40:50.50

>>911
そうです。条件に合う勤務表を自動で作成したいです

>>913
オフラインで休み希望等を確認し担当者が入力。あとは、自動作成というのを作りたいです。

2019/09/08(日) 12:47:36.20

>>919
たぶん既に作ってる表は、一ヶ月分が1シートで横方向がその月の日、縦が氏名。その下に☆△等の個数な感じかな。
賢明なら、月初と月末にかかる一週間もそのシートに含めてあるか…（含めてないなら含めておくとプログラミングが楽になる）

対象が何人かわからないけど、条件を満たす候補が何十（百）もあるときどうするのか
そうなるなら、休み希望だけでなく出勤希望も可能とする。そうすると連休よりバラしたい人は休みの前後に出勤希望を入れればいい

プログラム的には、休みや出勤を本人希望か自動設定かを区別しておくこと（見た目でも）
あとは、粛々と仕様のロジックを組み込むだけかな

学校の時間割り作るのと似てるので、そっちで検索すると参考なることあると思う

2019/09/08(日) 13:14:35.01

>>919
他には、氏名ごとに日のあとに、2連休有無、各週、月の勤務時間なども出しておきたい

まず作るのは、仕様に合った勤務割り振りになってるかチェックするプログラム
それができていれば、自動割り振りできてなくても手作業で始められるし、できたとこから随時自動化を進めていける

仕様へのリンクメモ >>894