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/

565 ：名無しさん＠そうだ選挙にいこう：2023/02/02(木) 22:30:15.65 .net

https://pbs.twimg.com/media/DM_M70lVQAAqOvu.jpg
それよりどれがいい？
右かな？

566 ：名無しさん＠そうだ選挙にいこう：2023/02/02(木) 22:38:29.76 .net

>>564
入力規則のリスト用に、別のセル範囲に　2022/09 2022/10 2022/11 2022/12 2023/01 2023/02 等と入力しておきます
ダイアログ内に直接手打ちすると勝手にそう変換されてしまいます　リストの範囲をセルの範囲にするのがミソです
また、その範囲のセルの書式設定を [yyyy"/"mm] などとしておく必要もあります

567 ：名無しさん＠そうだ選挙にいこう：2023/02/02(木) 23:02:04.29 .net

どうせオカマでしょ？
オカマバーはいいぞ～
お前らもきっとハマるはずた

568 ：名無しさん＠そうだ選挙にいこう：2023/02/03(金) 00:18:34.39 .net

>>564

入力規則を下記のとおりにする。

・空白を無視するにチェック
・2022/1の前に空白とカンマをつける。

空白を入れることで書式が変換されるのを防止し、
かつ空白を無視することでリストに表示させないようにしてる。

尚、実際に入力される値は「2022/1/1」となるので
セルの書式でyyyy/mあたりを設定。

エクセル2021で確認。
他のバージョンは未検証。

569 ：名無しさん＠そうだ選挙にいこう：2023/02/03(金) 00:46:37.30 .net

資金繰表て

>>543
の要素以外で必要な物って何？
答えられる人いるの？

570 ：名無しさん＠そうだ選挙にいこう：2023/02/03(金) 01:19:03.48 .net

>>559
凄いな
零細の人が気になって気になってその人のプロフィールまで覚えちゃったんだw

571 ：名無しさん＠そうだ選挙にいこう：2023/02/03(金) 07:14:30.70 .net

>>569
何を持って資金繰り表と言うかにもよるだろうけど単に日々の資金の状態だけ見せられてもどうしょうもないだろ
最低この程度の分解能がないと意味のある資金繰り表にはならないと思うよ
https://j-net21.smrj.go.jp/qa/financial/Q0225.html

572 ：名無しさん＠そうだ選挙にいこう：2023/02/03(金) 07:18:04.71 .net

ある範囲内で、特定の文字が書かれたセルを全て選択するにはctrl+F→ctrl+Aでいけますが、特定文字の含まないセルを全て選択するにはどうすればいい？

573 ：名無しさん＠そうだ選挙にいこう：2023/02/03(金) 07:43:05.19 .net

>>571
だから経理の人はその区別は付くのだよ
勘定科目とその属性とかは
だから勘定科目さえ分かれば後は属性に応じて集合セルにまとめるだけ

支払手数料他一般的な費用の科目は「経費」のセルにまとめるとか

それから資金繰表は過去の実績と未来の予測の2要素で成り立つけど、今ここで質問となっているのは過去の実績を会計ソフトから引っ張るという話
ソフトに先行して入力している場合には未来予測の基礎データとなるけど

574 ：名無しさん＠そうだ選挙にいこう：2023/02/03(金) 07:45:01.18 .net

で資金繰表は会社毎に違う管理会計分野のものだから
これというフォーマットは無いし

日々資金繰表もあれば5日単位もあれば。。

575 ：名無しさん＠そうだ選挙にいこう：2023/02/03(金) 07:46:40.44 .net

理想的には

一月、5日単位、1日単位をボタン一つで切り替えられるように出来たら良い
グループ化で月間と5日単位は出来るけど
更にクリック一つで1日単位の明細も見れたら良いなと

576 ：名無しさん＠そうだ選挙にいこう：2023/02/03(金) 09:58:26.16 .net

>>543

1. 現金または預貯金を相手にしている仕訳だけ（出納もの）、出金／入金別にクエリーで抽出する
出金もの：（借方）○○／（貸方）現金（または預貯金）
入金もの：（借方）現金（または預貯金）／（貸方）○○
（○○は任意の勘定科目。取引によっては○○が複数になる場合がある）
2. 複数年データを日付順に並べて、曜日・祝日も紐付ける

そこから固定費的なもの、変動費もの、臨時ものを
勘定科目と摘要だけで仕訳られるか、やってみれば？

その会社の事業内容や業界の商習慣、決済方法によって、
資金繰表の作り方はだいぶ変わってくる。
手形決済なんかが入ってくると、さらにややこしくなる。

固定費でも必ず毎月支払うものもあれば、
半年ごとに支払うものもあるので、
リースや賃借なんかの契約で定型払いものは、
支払時期・金額を別途「契約リスト」作って
日々の取引ではこれらを先に紐付け出来るようにするのが
その次の断面。

577 ：名無しさん＠そうだ選挙にいこう：2023/02/03(金) 10:09:23.48 .net

>>543
なお、日々の会計取引は、現金出納のその日に最終勘定科目が定まらず、
中間勘定（預り金や雑流動資産、雑流動負債）にいったん整理して
後で最終科目に振替える場合もあるので、
勘定科目だけでは追えないこともある。

578 ：名無しさん＠そうだ選挙にいこう：2023/02/03(金) 17:13:42.98 .net

【1 OSの種類 　 　 　 　 .】 Windows10 pro 22H2
【2 Excelのバージョン 　 】 Excel2019

昨日あたりからExcelファイル開くとディスク領域100%になって
PCが激重になるようになった。

タスクマネージャー見るとExcelのディスクアクセスが常時3MB/秒とか
（他のPCだと作業してなけりゃほぼ0MB/秒）
ファイル関係なく、新規作成した白紙のファイルでも同じ症状。

Excel閉じたら途端にPCサクサク。

Excelxx.xlbの再構築、クリーンブートやofficeの修復、
windowsのシステム復元しても変わらず。

過去にこんな症状になった人います？

579 ：名無しさん＠そうだ選挙にいこう：2023/02/03(金) 17:58:14.46 .net

ブックに関係ないなら、Excelのアンインスコ・再インストールかな？
その前にアプデの履歴を要チェック

580 ：名無しさん＠そうだ選挙にいこう：2023/02/03(金) 18:05:22.16 .net

>>579
ありがとう。試してみます。

581 ：名無しさん＠そうだ選挙にいこう：2023/02/03(金) 18:08:43.36 .net

ちな、windowsのシステム復元で2301から2212に戻ってて
今は更新止めてます。

582 ：名無しさん＠そうだ選挙にいこう：2023/02/03(金) 18:46:38.60 .net

VBAのファイルが壊れて修復掛かったら全部マクロが消えてしまったわ

あるあるなのなこれ

583 ：名無しさん＠そうだ選挙にいこう：2023/02/03(金) 19:01:24.02 .net

>>543
この件、AI様のお陰でおおよそ解決しそうなんだけど一点解決出来ていない問題が

20230203という数値を関数でテキスト数値化→日付にする方法は分かったんだけど、
パワークエリで読み込む際に変換加えるにはどうすれば良いんでしょう？
方法ありますか？

584 ：名無しさん＠そうだ選挙にいこう：2023/02/03(金) 19:02:12.63 .net

日付に出来れば
期間で絞り込みが出来るようになるので

585 ：名無しさん＠そうだ選挙にいこう：2023/02/03(金) 19:07:39.65 .net

>>584
この件、ググったら有ったわ

586 ：名無しさん＠そうだ選挙にいこう：2023/02/03(金) 19:46:02.91 .net

会計の話しつこいな
いつまでも自分の話するなよ
そもそも価値の低い仕事なんだから

587 ：名無しさん＠そうだ選挙にいこう：2023/02/03(金) 19:46:50.44 .net

テーブルの列に計算式がある場合、式を変更すればすべてに反映されますが、
一部のセルに直接数値が入力されていた場合、式を変更してもすべてに反映されなくなってしまいます。
このデフォルトの計算式を変更したい場合、どうするのがよいのでしょうか。

588 ：名無しさん＠そうだ選挙にいこう：2023/02/03(金) 19:55:43.38 .net

VBAなんて会計が中心だろ

589 ：名無しさん＠そうだ選挙にいこう：2023/02/03(金) 20:00:45.56 .net

>>587
テーブルは構造化参照として列名だけで計算式を組み立てられるのがミソだから、基本
・行方向の参照は同行
・同じ列は同じ構造化参照（列名だけ参照）の式
が前提。
もちろん、列名の構造化参照を手入力でセル参照することも可能（COUNTIFで重複数を数えるときなど）

で、テーブル内の1セルの式だけ変更すると
同列の式も全て変更されるはずが、
F4の再編集やF9の再計算やっても反映されないことがある。

その場合は、反映されない他の行の式を一度削除して
残したセルをF4再編集をかければ、
全行に反映されるはず。

構造化参照を使いたくない場合は、テーブル自体を使わないか、
範囲定義するのが吉かと。

590 ：名無しさん＠そうだ選挙にいこう：2023/02/03(金) 20:41:25.85 .net

いや、単純なゲーム作りに良く使われているでしょ
あと意外な使い方でメールの中身を解析してパワポにエクスポートするとか。

591 ：名無しさん＠そうだ選挙にいこう：2023/02/03(金) 21:09:30.72 .net

chatGPTでVBAのことまで教えてくれるのに驚いたわ。
細かい数字の精度は悪いけど構文の流れは凄く参考になった。
1年後には2chの質問スレも過疎るんだろうな

592 ：名無しさん＠そうだ選挙にいこう：2023/02/03(金) 23:54:39.68 .net

>>591
そもそも馬鹿には調べられなかっただけだろ

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性能あげてもエラーは出る
エクセルの限界だと思う