Excel総合相談所 152

1 ：名無しさん＠そうだ選挙にいこう：2022/12/25(日) 07:38:10.99 .net

【1 OSの種類 　 　 　 　 .】 Windows**
【2 Excelのバージョン 　 】 Excel**
【3 VBAが使えるか　 　 .】 はい･いいえ
【4 VBAでの回答の可否】 可･否

注意事項
・情報を隠すために別の問題を設定するのはやめましょう。たいていの場合その問題は的外れな設定で、期待していたものからずれた回答が返ってきます。

スレッド作成は>>980がやります

※前スレ
Excel総合相談所 151
https://mevius.5ch.net/test/read.cgi/bsoft/1664405768/

593 ：名無しさん＠そうだ選挙にいこう：2023/02/04(土) 00:18:43.06 .net

このスレに多い、馬鹿というより知恵遅れじゃないか？と思うような質問が消えるといいな

594 ：名無しさん＠そうだ選挙にいこう：2023/02/04(土) 00:47:55.30 .net

馬鹿馬鹿言う奴がこの世から消えるといいなw

595 ：名無しさん＠そうだ選挙にいこう：2023/02/04(土) 03:32:29.78 .net

他人に対して何かにつけて馬鹿って言うヤツの心理は、自分が優位に立ちたいからなんだとさ。

596 ：名無しさん＠そうだ選挙にいこう：2023/02/04(土) 05:52:39.57 .net

って言うヤツの心理は、自分が優位に立ちたいからなんだとさ。

597 ：名無しさん＠そうだ選挙にいこう：2023/02/04(土) 08:29:51.16 .net

つまりバカって事ですね～先輩方

598 ：名無しさん＠そうだ選挙にいこう：2023/02/04(土) 10:12:46.07 .net

昔の人はいいことを言いました
「バカって言うやつが本当のバカ」
これが真実だったと、この歳になって実感しています

599 ：名無しさん＠そうだ選挙にいこう：2023/02/04(土) 11:34:26.90 .net

そう思い込むのは勝手だけど、言うやつを否定しても言われるようなことをした事実はなくならないよ？

600 ：名無しさん＠そうだ選挙にいこう：2023/02/04(土) 11:53:46.96 .net

1から10まで0.1刻みのセルを作りたいとき
[A2] =A1+0.1
みたいなのを10になるまでコピペすればいいし、見た目上手くいくんだけど
[A61] 6
以降の中身を見ると「5.9999999999999999・・・」になってて
検索の引数にするとうまくいかない
たぶん20年ぐらい直ってないバグだと思う

601 ：名無しさん＠そうだ選挙にいこう：2023/02/04(土) 12:03:06.41 .net

【1 OSの種類 　 　 　 　 .】 Windows11
【2 Excelのバージョン 　 】 Excel16
【3 VBAが使えるか　 　 .】 はい
【4 VBAでの回答の可否】 可

エクセルのセルの配置をそのままグラフにしたい。
http://iup.2ch-library.com/i/i022738933915874311293.jpg
上のようなワークシートがあった場合、下のようなグラフを作りたい。
■がつながってるけど、難しいなら、値があるセルだけでも■でプロットしたい。
どのグラフを使えばいい？

602 ：名無しさん＠そうだ選挙にいこう：2023/02/04(土) 12:05:54.27 .net

とりあえず 浮動小数点 誤差 あたりでググってこい

603 ：名無しさん＠そうだ選挙にいこう：2023/02/04(土) 12:07:17.63 .net

バグって言うやつが本当のバグ

604 ：名無しさん＠そうだ選挙にいこう：2023/02/04(土) 12:15:09.44 .net

>>600
フィルで引っ張ればそのままの数式でちゃんと連続するぞ　コピペって何だ？そんな面倒な事ずっとやり続けてんのか？10年間？11年前ならできたのか？

605 ：名無しさん＠そうだ選挙にいこう：2023/02/04(土) 12:32:33.85 .net

あ、スマン　見た目じゃ無い方だったか　Roundを駆使するしか無いな [A2] = A1 + ROUND(0.1,1)

606 ：名無しさん＠そうだ選挙にいこう：2023/02/04(土) 17:37:56.83 .net

>>603
笑った

607 ：名無しさん＠そうだ選挙にいこう：2023/02/04(土) 18:01:44.04 .net

>>601
見た目だけでいいのなら、積み上げ縦棒グラフを使えばいける。
(浮かした方のグラフの塗りつぶしをなしにする)。

608 ：名無しさん＠そうだ選挙にいこう：2023/02/04(土) 18:08:58.10 .net

エクセルの本のおすすめを教えてください。
今まで何となくエクセルを触ったりはして分からない事はネット検索
してたんだけどちゃんと基本を覚えてた方がいいかなと思って。
正しい画面周りとか機能の用語から入っているのがいいです。
そして分からない時はネット検索しなくて手元で逆引きできるような。

609 ：名無しさん＠そうだ選挙にいこう：2023/02/04(土) 20:00:24.47 .net

>> 608
基本がどこまでを指すのか難しいが、Youtubeで金子動画にはお世話になった。
お世話になったので、金子の本も買ったのだが読んでない。
ありがとう、金子。


610 ：名無しさん＠そうだ選挙にいこう：2023/02/05(日) 04:13:03.64 .net

>>608
ハッキリ言ってオススメ本とか無いです。
なぜかと言うと、質問者が求めている情報が必ずや学べるとは限らないからです。

１番良いのはヤフー知恵袋やこのスレにガンガン質問することがベスト。

611 ：名無しさん＠そうだ選挙にいこう：2023/02/05(日) 04:15:38.31 .net

わからないことがわからないっぽいが何て質問しろと？

612 ：名無しさん＠そうだ選挙にいこう：2023/02/05(日) 04:19:07.17 .net

購入は歴史ある「できる」シリーズが一番初心者に勧めやすい
あと仕事帰りに毎日本屋で20ページくらい入門者用を閲覧かYoutubeすればよい
「一番売れてるエクセルの本」とか「「たった1日で即戦力になる・・」とか
謳ってるやつはキャチコピーだけが優れている

613 ：名無しさん＠そうだ選挙にいこう：2023/02/05(日) 04:28:56.28 .net

>>608
今の時代、youtubeで見るのが一番ですよ
バーも必死に再生数を稼ぎたいから、意外とクオリティが高い
ここに質問するとAIに劣る人間性の者から罵倒される
零細企業認定される
と良いことは無いです

614 ：名無しさん＠そうだ選挙にいこう：2023/02/05(日) 08:41:49.65 .net

エクセルで台帳を管理しています。
商品名、単価を入力したマスターからVLOOKUPにて台帳に情報を引っ張ってきています。
商品名をリストから選択すると、自動でマスターの単価が反映されます。
台帳とマスターはシートで分けており、同じエクセルファイル内に存在します。

価格変更があり、マスターの対象価格セルを変更すると過去、同一のリスト選択を行って商品まで変更後の価格になってしまいます。
過去の価格は実際、旧価格で動いているので変更したくありません。

同じ商品名を作成し新旧価格それぞれで行を作成しようとも思いましたが、変更の都度、同一商品名が増えていくので避けたいです。
マスターの古い価格行を非表示にしましたが選択リストからは消えませんでした。

選択リストからリストラ条件範囲内でも、特定の行のみ見えなくする方法はあるのでしょうか？

よろしくおねがい致します。

615 ：名無しさん＠そうだ選挙にいこう：2023/02/05(日) 08:44:41.30 .net

>>608
俺もyoutubeが一番だと思う。2～3チャンネル登録しとけば？
「こういう時はこうすればいい」という動画をほぼ毎日更新しているから。
それをたまに見ておけばいい。簡潔にまとめられているから短時間で済むし。

わからないことだはchatGPTで聞くのが一番。２ｃｈだと馬鹿、質問もまともに出来ないのかと罵倒されるから。

616 ：名無しさん＠そうだ選挙にいこう：2023/02/05(日) 08:51:11.47 .net

>>614
有効か無効かの列を追加して価格が変わったら無効にして新しいところには反映させなければ良いとAIさんがおっしゃっておりました

617 ：名無しさん＠そうだ選挙にいこう：2023/02/05(日) 08:51:44.03 .net

>>615
俺もそう思う

馬鹿、バカ、零細はNGワード

618 ：名無しさん＠そうだ選挙にいこう：2023/02/05(日) 08:53:39.29 .net

VLOOKUPの時に有効のみの情報を抽出すれば良いそうです
>>614

619 ：名無しさん＠そうだ選挙にいこう：2023/02/05(日) 09:06:34.27 .net

神髄さんが今１番評判良いらしい。
とくに中級者以上に。

620 ：名無しさん＠そうだ選挙にいこう：2023/02/05(日) 09:13:54.02 .net

>>614
実務で商品マスター作る時は、
・価格についての適応開始年月日
・生死フラグ(0：廃番、1：保留、2：取り扱い中、とか)
のカラムも必要かと。

価格改定の情報入力は、同商品レコードを上書きするのではなく
価格と適応開始年月日が異なる別レコードを作る
運用で。


> 過去の価格は実際、旧価格で動いているので変更したくありません。

新価格と旧価格の適応条件はなに？

適応開始年月日が過ぎているなら自動的に新価格適応でないのであれば、
手動入力の生死フラグが「取扱中」のものを検索するとか？
商品マスターはクエリーで、
簡単に年月日フィルターや生死フラグの値置換が出来るようにしておく。

一致条件検索はVLOOKUPでも2条件は可能だけど、
INDEX(MATCH)の方がスッキリする。

621 ：名無しさん＠そうだ選挙にいこう：2023/02/05(日) 10:11:28.49 .net

LAMBDAがらみの深い話がバリバリ載ってる本があれば欲しい気もするが、Excel特有の癖を自分で整理するしかないかな？と最近思いつつある。

622 ：名無しさん＠そうだ選挙にいこう：2023/02/05(日) 10:23:35.19 .net

ヒントとしてはVLOOKUPで呼び出してるのは、コピペをリンク貼り付けしているのと同じ　マスタが変われば当然それに従う
値で貼り付けるように工夫すればマスタ値が変わっても入力時の値は保持される　マクロやVBAで作れるならそうした方がいい

或いは、マスタの商品単価を複数列用意して何番目の単価を呼び出すかを工夫する　単価の変動がどの程度の頻度か？台帳側の寿命は
どのぐらいの期間を目安にしているか、などでも効率いい方法は変わって来たりもするので、後出し情報を書くなり自分で工夫するなり

623 ：名無しさん＠そうだ選挙にいこう：2023/02/05(日) 10:30:13.83 .net

今、値上げ値上げでコロコロ上がるよな
原価が上がるから仕方が無い
不変なのは給料だけ

624 ：名無しさん＠そうだ選挙にいこう：2023/02/05(日) 10:59:24.33 .net

>>614
・価格の履歴を取りたいなら>>620の言うようにマスター側に価格と適用時期を持って台帳側の日付（あるよね？）で引いてくる
・履歴が要らないなら>>622の言うように計算式の結果を値で貼り付ける仕組みを作って台帳側で値を記録しておく

のどちらがが定番

625 ：名無しさん＠そうだ選挙にいこう：2023/02/05(日) 12:06:04.11 .net

給料の上げ幅はもはや絶望的らしいな。
派遣さんの方が手取りは上だったりザラ。

626 ：名無しさん＠そうだ選挙にいこう：2023/02/05(日) 15:16:08.48 .net

関数の書き方によって処理スピードが変わると思いますが比較する方法ありますか？

627 ：名無しさん＠そうだ選挙にいこう：2023/02/05(日) 15:24:18.75 .net

VBA必要だけど　：　できそうなので試して
https://study-satellite.com/programming/excel-vba/execution-time/
https://www.excelspeedup.com/syorijikan/
https://www.sejuku.net/blog/69319

628 ：名無しさん＠そうだ選挙にいこう：2023/02/05(日) 15:28:03.61 .net

>>608
ホントにまったくExcel触ったこともないって人であるなら「できるExcel」あたりでも使えばいいんだけど、
ある程度触ったことがあるなら、今やってるようにネットで調べるとか、他の人も言うようにYouTubeがいいよ
強いて言うのなら、そういうあちこちで知ったバラバラの知識を、自分の中で再構築することが必要かも
例えば concatenate、substitute、left、midなど→文字列操作系 みたいにまとめてみる、とか

629 ：名無しさん＠そうだ選挙にいこう：2023/02/05(日) 15:35:24.44 .net

>>628
逆にそういうのが逆引き出来るEXCELファイルがあれば便利なのにね

630 ：名無しさん＠そうだ選挙にいこう：2023/02/05(日) 15:35:57.42 .net

俺って
逆に
が口癖なのかな。。

631 ：名無しさん＠そうだ選挙にいこう：2023/02/05(日) 17:14:04.19 .net

データの入力規則→リストの選択肢を編集したときに
編集した内容を選択先に反映することは可能でしょうか？

632 ：名無しさん＠そうだ選挙にいこう：2023/02/05(日) 17:20:29.24 .net

>>631
はい、Excelでデータの入力規則とリストの選択肢を編集することで、選択先に反映することは可能です。具体的には、下記の手順で実現できます。

リストを編集したいセルを選択します。
[データ]タブから[入力規則]を選択します。
[リスト]を選択します。
リストを編集したい場合は、[設定]ボタンをクリックし、新しいリストを入力します。
[OK]をクリックして設定を保存します。
これにより、編集したリストが選択先に反映されます。

633 ：名無しさん＠そうだ選挙にいこう：2023/02/05(日) 17:29:43.17 .net

具体的に書かないと　例えば、リストの選択肢が [a,b,c,d] なのを [w,x,y,z] に変更したら
以前に入力してた abcd が勝手に(自動的に)wxyzに変わって欲しいとかなのか？それは無理筋だろうけど

634 ：名無しさん＠そうだ選挙にいこう：2023/02/05(日) 17:49:12.10 .net

>>608
大正義「日経」と「FOM」
ググればいくらか出てくる。
自分は8割は日経Excel本とFOM本で学習しました。

635 ：名無しさん＠そうだ選挙にいこう：2023/02/05(日) 17:53:52.40 .net

あるエクセルファイルのセル関数を精査しているのですが、
VLOOKUP関数で、次のような式が埋め込まれていました。

=VLOOKUP([@請求会社名],請求先マスタ!A:E,1,FALSE)
この「[@請求会社名]」は、1行目のカラム名を指しているのですが、
このようなセル範囲の指定のことを、何と呼ぶのでしょうか？

このような指定の仕方をしらなかったので、
詳しく知りたいのですが、何という用語で検索すればいいのかわからないので
お分かりになるかた教えてください。

636 ：名無しさん＠そうだ選挙にいこう：2023/02/05(日) 18:06:10.27 .net

構造化参照

637 ：名無しさん＠そうだ選挙にいこう：2023/02/05(日) 18:45:18.63 .net

通りすがりだけどこんな機能があるんだな
テーブル自体をあまり使わないから知らなかったわ
世の中の人でこの機能まで使いこなしてる人ってどれくらいいるんだろう...

638 ：名無しさん＠そうだ選挙にいこう：2023/02/05(日) 18:57:19.94 .net

>>637
テーブル化すれば自動的に構造化参照になるので
使いこなすもクソもない

639 ：名無しさん＠そうだ選挙にいこう：2023/02/05(日) 18:59:30.68 .net

構造化参照で一つ上の行を参照できないのがつらい

640 ：名無しさん＠そうだ選挙にいこう：2023/02/05(日) 19:18:34.25 .net

パワークエリとパワーピボットとマクロ記録の3つ覚えればかなり自動化出来る

641 ：名無しさん＠そうだ選挙にいこう：2023/02/05(日) 19:48:55.92 .net

>>639
構造化参照の式の中でも、セル番地参照の混在は出来る。
B列が「値」で、その重複ckのためにC列「重複ck」に
=COUNTIF(B$1:B2,[@値])
のように、ドラッグ&手打ちになるが。

テーブルの中で、「行番号」列作って、見出し行直下の1行目に
=ROW()-ROW(テーブル名[[#見出し],[行番号]])
とやると、行を増やせば自動的に連番が入る。
（クエリー内のM言語式で使う時は、最初の行は0のお約束なので、-1しておくとわかりやすい）

テーブル1の中の「値」列について、1行前とか、
例えば、ちょっと複雑な7日間移動平均のための合計を作りたいとき、
=INDEX(テーブル1[#すべて],MATCH([@行番号]-0,テーブル1[[#すべて],[行番号]],0),MATCH(テーブル1[[#見出し],[値]],テーブル1[#見出し],0))
+INDEX(テーブル1[#すべて],MATCH([@行番号]-1,テーブル1[[#すべて],[行番号]],0),MATCH(テーブル1[[#見出し],[値]],テーブル1[#見出し],0))
+INDEX(テーブル1[#すべて],MATCH([@行番号]-2,テーブル1[[#すべて],[行番号]],0),MATCH(テーブル1[[#見出し],[値]],テーブル1[#見出し],0))
・
・
・
+INDEX(テーブル1[#すべて],MATCH([@行番号]-5,テーブル1[[#すべて],[行番号]],0),MATCH(テーブル1[[#見出し],[値]],テーブル1[#見出し],0))
+INDEX(テーブル1[#すべて],MATCH([@行番号]-6,テーブル1[[#すべて],[行番号]],0),MATCH(テーブル1[[#見出し],[値]],テーブル1[#見出し],0))
とかやると、7日間移動合計が出来る。
このままでも行番号が1～6までは#N/Aになる。

642 ：名無しさん＠そうだ選挙にいこう：2023/02/05(日) 20:27:28.16 .net

>>641
なるほど
揮発性関数のoffsetを使うと激遅で死にそうだったが
indexで参照する方法があったのか
非常に勉強になりましたわ

643 ：名無しさん＠そうだ選挙にいこう：2023/02/05(日) 20:34:32.06 .net

indirectやoffsetの揮発性関数は処理遅いからダメらしいからね。
ネットに書いてあったし

644 ：名無しさん＠そうだ選挙にいこう：2023/02/05(日) 21:08:11.51 .net

=SUM(INDEX([値],MATCH([@行番号]-{0,1,2,3,4,5,6},[行番号],0)))

645 ：名無しさん＠そうだ選挙にいこう：2023/02/05(日) 21:51:52.09 .net

7日間移動累計をクエリーのM言語数式でやる、
例えば、新型コロナの日陽性者数については、
移動累計 = if [年月日] < DateTime.Date(R_StartDate_Data)+#duration(R_TrailingDays-1,0,0,0) or [年月日] > DateTime.Date(R_EndDate_Data)
　then null
　else List.Sum(List.Range([都道府県グループ][日陽性者数],[index]-(R_TrailingDays-1),R_TrailingDays))
　もし、その行の[年月日]が、データ開始日+6日より前、または、データ最終日より後なら、Null、
　それ以外は[年月日]より7日間合計を計算する
・・・なんてことでやっている。(日時推移グラフのX軸を直前の月曜日スタート、当日の翌週月曜日までの表示にしたいため)

ざっくり説明すると、ワークシートのセルを名前定義から、
・R_StartDate_Data：厚労省Webのcsvのデータ開始日・・・2020/1/22(水)
・R_EndDate_Data：厚労省Webのcsvのデータ最終日・・・2023/2/5(日)・・・1,111日分
・R_TrailingDays：何日間の移動累計にするかの指定
M言語関数は、
・DateTime.Date()は、年月日時分秒のうちの年月日だけを取り出す関数。
・#duration(日,時,分,病)は、M言語での日数加減計算の差分
・List.Sum()でその[年月日]までの7日間[日陽性者数]を合計している。
・List.Range()で合計範囲として、その行の[年月日]を含む7日間の行範囲を指定している。
・・・テーブル内ワークシート関数のようにINDEX(MATCH)を7回足すわけではない。

・[都道府県グループ]：この直前のステップは都道府県ごとにグループ化。
実は縦：年月日、横：都道府県のマトリックス形式ではなく、都道府県、年月日、日陽性者数のリスト形式にしていて、
[都道府県グループ][日陽性者数]と続いているのは、そのグループにしたテーブルの中の[日陽性者数]を参照している。
・[index]列は、都道府県ごとに0から始まる行番号連番の、その行の番号
・[index]-(R_TrailingDays-1)は「自分の行-6」という始点
・R_TrailingDaysは「7日間分」

この後のステップで、縦：年月日、横：47都道府県+全国の48列のマトリックス形式にまた戻し、
2020/1/13(月)～2023/2/6(月)のカレンダーテーブルにマージしている。

このクエリー更新に6秒程度かかるけど、
今のところ1,111日間の計算は出来ている。

646 ：名無しさん＠そうだ選挙にいこう：2023/02/05(日) 23:13:48.74 .net

結論だけ書けよ

647 ：名無しさん＠そうだ選挙にいこう：2023/02/06(月) 02:27:54.57 .net

確かに。長過ぎて疲れる

648 ：名無しさん＠そうだ選挙にいこう：2023/02/06(月) 07:28:40.28 .net

ざっくり説明なんだこれw
仕事できなさそう

649 ：名無しさん＠そうだ選挙にいこう：2023/02/06(月) 08:49:30.69 .net

俺の職場はブックの共有が大好きなのでテーブルが使えない状況が多い
また、見栄え優先で見出しが長い&改行入れたがるので、構造化参照の式が使いにくい

650 ：名無しさん＠そうだ選挙にいこう：2023/02/07(火) 01:57:54.05 .net

お前も低脳の一部ってことか

651 ：名無しさん＠そうだ選挙にいこう：2023/02/07(火) 02:32:14.49 .net

SharePoint上で共有する場合、ブック共有してるとリンクがまともにうごかねーんだよな
そもそもブックの共有ってどこのファイルを参照しているのかとか、マスタを置いてる場合、誰がメンテしてるのか、とか分からなくなるから好きじゃ無いんだよね
挙げ句、ブックにリンク貼ってるExcelを、そのままメールしてくるバカもいるし・・・・・・てめーのデスクトップのファイルなんかしらねーよ、と
シート間の共有のほうが、１つのブックでクローズするから分かりやすいと思う

652 ：名無しさん＠そうだ選挙にいこう：2023/02/07(火) 06:58:51.83 .net

NGワード推奨 「低能」

653 ：名無しさん＠そうだ選挙にいこう：2023/02/07(火) 08:31:53.88 .net

さらに低レベルの無能

654 ：名無しさん＠そうだ選挙にいこう：2023/02/07(火) 09:03:38.42 .net

マクロを使うときは
まず参照や関数で貼り付け先に合うセル配置・形式にしたコピー用範囲を作ってから
その範囲をコピペする部分だけマクロで記録するのが普通ですよね？

655 ：名無しさん＠そうだ選挙にいこう：2023/02/07(火) 12:40:26.15 .net

普通じゃねーよ
わざわざ効率悪いことするな

656 ：名無しさん＠そうだ選挙にいこう：2023/02/07(火) 12:48:30.29 .net

数字を文字列としてセルに書き込んでるつもりだけど混在してるみたいでvlookupとかで検索すると引っ掛ったりなかったりする
セルの左上に緑三角マークがあるのは文字列確定だけどエラーを無視するにすると三角は消えて判別不能
混在を解消または判別する良い方法ない？効率いい方法

657 ：名無しさん＠そうだ選挙にいこう：2023/02/07(火) 13:41:36.33 .net

>>656
列選択してから文字揃えを"標準"にしたら、数値は右揃え、文字列は左揃えになるのでは？

658 ：名無しさん＠そうだ選挙にいこう：2023/02/07(火) 16:03:00.38 .net

関数の組み合わせ方によっていろんな解決法があるのだけど、
正直こんなバカバカしい数式を組む前にデータの型を揃える努力をした方が何百倍も効率がいい。　だそうだ
tps://simplism.hatena****.com/entry/2022/06/14/221844　
ブログのリンク貼れなかった　アタマにht追加と****の部分はblogにして

659 ：名無しさん＠そうだ選挙にいこう：2023/02/07(火) 16:16:03.39 .net

578です。

MS配布のツールでアンインストール後、再インストールしても
改善しなかったので、ツールでアンインストール後に関係ありそうな
フォルダ、レジストリのキーを全部手動で削除してから再インストール
したら直りました。

660 ：名無しさん＠そうだ選挙にいこう：2023/02/07(火) 16:25:36.67 .net

おつかれ　xlb活用してる人があんまし居なかったんだろうな
もはや遠い過去の遺産的な立ち位置では？xlb
リネームして思い切って削除しろ　とかのアドバイスはそこそこ出てきたけど、それを提案するのは躊躇われた　
使ってない機能を検証しないままカキコするのも何なんで

661 ：名無しさん＠そうだ選挙にいこう：2023/02/07(火) 20:01:59.28 .net

後付けの言い訳ウザい

662 ：名無しさん＠そうだ選挙にいこう：2023/02/07(火) 20:53:19.30 .net

NGワード:無能

663 ：名無しさん＠そうだ選挙にいこう：2023/02/07(火) 21:23:07.04 .net

自作PCでCore i7-8700まだ使ってるけど、
そろそろ新調しようかと。
ExcelでPowerQuery、PowerPivot、VBA使って104万行超えるビッグデータ扱い出したら、
ちょっと時間が掛かったり、計算が追いつかずエラーになることも。

自作板はゲーマーやベンチ、エンコード目的であまり参考にならず、
このスレの住人だったら何をお勧めする？

Excelはクロック命と聞いていたけど、
13900Kとかにすりゃいいの？

664 ：名無しさん＠そうだ選挙にいこう：2023/02/07(火) 21:51:42.12 .net

100万行をまともに扱うのは厳しい
12900と32GBだけど、PC性能あげてもエラーは出る
エクセルの限界だと思う

665 ：名無しさん＠そうだ選挙にいこう：2023/02/08(水) 01:43:43.88 .net

Accessなら100万件いけるかなあ・・・・・
件数かんがえると、ちゃんとしたRDBを検討したほうがいいと思うけど

それよか自分は未だにA10-5700、Win７だわ。Ci7-8700ならあと7年いける

666 ：名無しさん＠そうだ選挙にいこう：2023/02/08(水) 02:11:56.59 .net

ゲーミングPCならかなり速いぞ

667 ：名無しさん＠そうだ選挙にいこう：2023/02/08(水) 05:11:47.34 .net

業者の回し者がいるな

668 ：名無しさん＠そうだ選挙にいこう：2023/02/08(水) 11:09:25.34 .net

486DXならSXと違ってコプロ内蔵してるからめっちゃんこ速いぞ

669 ：名無しさん＠そうだ選挙にいこう：2023/02/08(水) 11:41:04.83 .net

>>665
アクセスも2GB制限があって、数年前でも解決してなかった
今はされたのかな？

670 ：名無しさん＠そうだ選挙にいこう：2023/02/08(水) 12:55:36.19 .net

Accessは格納庫としてだけ利用してみたけど、遅かった。
地域別に10個に分けた元帳Excelデータを
事前にユニオンクエリーでがっちゃんこしてたんだけど、
2GB制限超えてもうた。

なので今はExcelに出戻りして
一旦データモデルとしてがっちゃんこしてる。

一応SSDにはしてるけど、地域とか月とかスライサー変えるたびに再読込みするのも
もうちょっと速くなったらなぁ・・・と。

671 ：名無しさん＠そうだ選挙にいこう：2023/02/08(水) 13:28:51.37 .net

それは単にいろいろ間違った運用してただけだろ
AccessでダメだったのをExcelに戻す意味が分からん　他のDBにしてみるならまだしも
薄っすい知識で試したけどダメだったと書き直すべき　そこにSSDが出て来る関連性も意味判らんし

672 ：名無しさん＠そうだ選挙にいこう：2023/02/08(水) 13:47:11.52 .net

どうすりゃいいねん？

673 ：名無しさん＠そうだ選挙にいこう：2023/02/08(水) 14:01:24.31 .net

ゲーミングPCしかない

674 ：名無しさん＠そうだ選挙にいこう：2023/02/08(水) 14:20:32.96 .net

どうすりゃいい？じゃ無くて、こういうデータをこうしてみたけどここで困ってる　という詳細な説明を持って質問する
さもなければ、リモートで診断してくれるシステム会社に連絡して、自分のPCとシステムの問題点を探して指摘してもらう

675 ：名無しさん＠そうだ選挙にいこう：2023/02/08(水) 17:33:22.03 .net

Excelしか知らないイキり情シスの限界だろ
普通は最初からデータベース使う

676 ：名無しさん＠そうだ選挙にいこう：2023/02/08(水) 18:14:06.42 .net

Excelしか知らないって、一般事務や営業はExcelをメインで扱うから他のソフトなんか知らないのが普通では？

677 ：名無しさん＠そうだ選挙にいこう：2023/02/08(水) 18:19:25.03 .net

素人に毛が生えたような知ったかに大量のデータを扱う仕事をさせる会社も問題
もしただの事務員なら仕事を情シスに回すか外注させる
その事務員しか選択肢がないなら会社として終わってる

678 ：名無しさん＠そうだ選挙にいこう：2023/02/08(水) 18:23:18.33 .net

事務がExcelしか使わない会社って別に多数派でもないだろ

679 ：名無しさん＠そうだ選挙にいこう：2023/02/08(水) 18:46:07.31 .net

>>678
根拠は？

680 ：名無しさん＠そうだ選挙にいこう：2023/02/08(水) 19:27:24.12 .net

どっちもソースなし

681 ：名無しさん＠そうだ選挙にいこう：2023/02/08(水) 21:08:34.38 .net

仕事のデータじゃ、ないんだわ。

682 ：名無しさん＠そうだ選挙にいこう：2023/02/08(水) 21:25:29.07 .net

DBのテーブル設計もしないまま安直な発想で使おうとする人に多いつまづき
そして、それをDBのせいにするのもそういう層
単純計算で1レコード200byteで1000万件可という仕様を使いこなすには程遠い

683 ：名無しさん＠そうだ選挙にいこう：2023/02/08(水) 22:15:25.32 .net

13地点・1時間値が12年間分
8,760行×12年間＝105,120行/地点
今年度末合計行は、×13地点＝約137万行の予定
各1時間値の行には独立したカラムがとりあえず13列
合計や平均・最大・最小等の計算は、
PowerPivotで最後にやっても、当該月分だけで負荷でエラい目に遭ったので別集計

独立13カラム×約137万行をそのまま読込みたい一つの目的は、
月単位で1時間値の推移（X軸は672～744時間）をグラフで、
地点別、月別で切り替えて比較したいため。

絞込みにはPowerPivotのフィルターをスライサーで。
気象データ1時間値との紐付けも。

Accessでやっていたときは、
月次処理としてExcelで集計した12年間通しの地点別元帳を
ユニオンクエリーの更新で読込んで、全地点通しのDBテーブルとして毎回全行置換えていた。
毎回全行置換えなのは、時々遡及補正が入るため。

上手くいかなかったのは、
・Excelでスライサーを変えるたびに、Accessファイルの読込みに1分近くかかっていた
・10年目後半あたりでAccessファイルが2GBを超過し、最適化してもダメだった

今はExcelに戻って、グラフにするブック内でデータモデルで地点を合体させて、
Core i7-8700で地点や月の切替えに15秒程度かかっている。

せめて切替え5秒くらいにできないか？
CPU？
Oracleでも勉強しろと？（といってもExcelからDB読込みに行くのじゃ、あまり変わらない？）

684 ：名無しさん＠そうだ選挙にいこう：2023/02/09(木) 01:03:36.39 .net

なんでOracleに限ってるんだ？

685 ：名無しさん＠そうだ選挙にいこう：2023/02/09(木) 02:36:12.35 .net

馬鹿だから

686 ：名無しさん＠そうだ選挙にいこう：2023/02/09(木) 11:22:21.67 .net

気象関係のデータを？13地点で1時間ごとに採っているデータがあると？
試してみようかとも思ったが、説明が面倒くさくて、何が何だか
単純に何列？　そのそれぞれの列のデータ型は？　それぐらい書いてくれれば試すが
或いは
tps://marshal115.hatena****.com/entry/2022/07/23/103043　とか参考に物理メモリ見直すとか
　↑例に依ってblogに変換　
そんなサイズのデータを、Access側で下準備もしないままExcel側から操作したら、そら遅いだろうとも思えて
事前にQueryで必要なものだけ抽出して統計のベースにするのが一般的だろうにとも思えて

687 ：名無しさん＠そうだ選挙にいこう：2023/02/09(木) 12:04:59.81 .net

Excel で済むような環境なら sqlite でいいと思うけどね
今どきのマシンなら 100万レコード程度は（もちろんちゃんと使うという前提で）楽勝だし

688 ：名無しさん＠そうだ選挙にいこう：2023/02/09(木) 18:08:45.36 .net

>>678
うちWordがウザイくらい多用してるわ。

689 ：683：2023/02/09(木) 21:06:13.91 .net

>>686

hatenaの記事、ありがとう。
関連記事も含め、ゆっくり読んでみる。

> 気象関係のデータを？13地点で1時間ごとに採っているデータがあると？

気象庁サイトからDLした1時間平均観測データは、
今はあくまで参考値的な位置付け。
気象データもExcelの地点ごと元帳をグラフブックで合体させて
データモデルから共通カレンダーへのマージで引っ張っている。

メインのデータは、電力のエリア需給実績1時間平均値。
全国10エリアと、同時間帯串刺し合計した50Hz連系計、60Hz連系計、10エリア計で13地点。
今は気象庁データのうち、10エリアの需要最大地各1地点の天気と気温だけを紐付けしている。
今後は気温と電力需要について、エリア別・季節別の相関取るとか、
太陽光・風力の各エリア導入容量上位3地点の、日照時間や風速の紐付けもしたい。


> 事前にQueryで必要なものだけ抽出して統計のベースにするのが一般的だろうにとも思えて

「月単位で1時間値の（需要・電源別）推移」グラフはあくまでも入り口で、
再エネの出力状況や、火力の絞り具合や過去順位、揚水や連系線の使用状況、需要の伸び・変動具合とか、
そこからさらに深掘りしている。
同じDBからでも、目的に応じてカラムを絞って別ブックにした分析グラフもいくつか。

年間順位や最大最小フラグとか、
横方向（同時間帯）の加工値は、先にエリアや年度を絞ってしまうと、
スライサーを切り替えたときに手戻りになったり、
集計ブックでその都度計算の負荷が重くなったりしたので、
Excel集計の途中で得られた加工値は、
同エリア・同時間帯のものならできるだけDBに放り込みたいと考えた。

690 ：名無しさん＠そうだ選挙にいこう：2023/02/09(木) 21:58:10.35 .net

データベースを知らない馬鹿

691 ：名無しさん＠そうだ選挙にいこう：2023/02/10(金) 05:22:36.66 .net

時間帯、エリアでユニークなkeyIDは作ってあるので、
加工値の追加カラム分は別DBとして分散格納すれば、
何百万行になっても、処理速度を速くする余地はまだある？

692 ：名無しさん＠そうだ選挙にいこう：2023/02/10(金) 08:04:50.15 .net

あるけどアンタ目的もないならやめとけ

693 ：名無しさん＠そうだ選挙にいこう：2023/02/10(金) 08:14:37.68 .net

Sub B2Sort(ByRef argAry() As Variant, ByVal keyPos As Long)
Dim vSwap
Dim i As Integer
Dim j As Integer
Dim k As Integer
For i = LBound(argAry, 1) To UBound(argAry, 1)
For j = LBound(argAry) To UBound(argAry) - 1
If argAry(j, keyPos) > argAry(j + 1, keyPos) Then
For k = LBound(argAry, 2) To UBound(argAry, 2)
vSwap = argAry(j, k)
argAry(j, k) = argAry(j + 1, k)
argAry(j + 1, k) = vSwap
Next
End If
Next j
Next i
End Sub


二次元配列のソートを解説サイトから頂いて使ってるのですが、
同じ値がいくつある時にいつも同じ並びになってしまいます。
1 2 3 4 4 4 5 6 6 8 8 9
みたいな4や6や8がいつも同じ並びになるということです。
二次元配列なのでこの4はそれぞれ違う行としての性格も持っています。

偏りを無くすために同じ値のとこだけランダムにしたいのでとりあえず各数値にrand数字を付けて後から取り除きました。
目的は果たせてるのですが、しっくりきません。

関数のどこをどう触れば同じ数字の時に毎回違う並びに出来るのかアドバイスいただけませんでしょうか。