Excel総合相談所 149

1 ：名無しさん＠そうだ選挙にいこう：2022/03/18(金) 11:20:25.11 .net

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

注意事項
・情報を隠すために別の問題を設定するのはやめましょう。たいていの場合その問題は的外れな設定で、期待していたものからずれた回答が返ってきます。
※前スレ
Excel総合相談所 148
https://mevius.5ch.net/test/read.cgi/bsoft/1643843984/

955 ：名無しさん＠そうだ選挙にいこう：2022/06/05(日) 18:28:43.28 .net

954です。
直リンクで画像上げ直します。
https://i.imgur.com/ZKtLgmO.jpg

956 ：名無しさん＠そうだ選挙にいこう：2022/06/05(日) 18:44:33.50 .net

>>954
テーブル/クエリーにして良いのなら、

PowerQueryでフィル使った後、
https://excel-master.net/data-shaping/power-query-fill/
1列目でピボットする、

に1票。

957 ：名無しさん＠そうだ選挙にいこう：2022/06/05(日) 19:16:03.37 .net

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

ピボットテーブルで、列ラベルに「日にち」、行ラベルに「担当者」、値エリアに「個数/担当者」が入っている状態で作成した棒グラフを
担当者一つ一つの棒グラフに「カテゴリ」の比率も入れたいです。
行ラベルに「カテゴリ」を追加すると、一人ひとりの担当者に対して各カテゴリの棒グラフが表示されますが、
これを一人に対して一つの棒グラフでその一人の棒グラフの中で比率を表示させたいのです。

わかりづらい説明ですみませんがよろしくおねがいします。

958 ：957：2022/06/05(日) 19:51:18.95 .net

>>957
こうなってるグラフを、一人ひとり積み上げ縦棒にして一つの棒にしたいです。
https://i.imgur.com/AxTiR1F.png

959 ：名無しさん＠そうだ選挙にいこう：2022/06/05(日) 20:09:27.81 .net

>>954
単純に以下ではどうすか？
ただし、スピルって別シートに鬼門があるようなので、
隣のシートのデータを色々いじくる時には細工が必要になることがあるかも？
今は治ってるのかな？

LET(a,A1:A25,b,B1:B25,TRANSPOSE(IF(b="",a,b)))

960 ：名無しさん＠そうだ選挙にいこう：2022/06/05(日) 20:13:41.87 .net

横じゃなくて上ね？
もそっとひねりが必要だたな。

961 ：名無しさん＠そうだ選挙にいこう：2022/06/05(日) 20:24:41.25 .net

SCAN

962 ：名無しさん＠そうだ選挙にいこう：2022/06/05(日) 20:32:00.59 .net

さっきは失礼、正しくはこんな感じ？

TRANSPOSE(SCAN(,B1:B25,LAMBDA(a,b,IF(b="",a,b))))

963 ：名無しさん＠そうだ選挙にいこう：2022/06/05(日) 21:02:26 .net

>>958
行ラベル、列ラベルが指定通りだとうまく行かなかったで入れ替えて、
ひとまず、日にちごと・担当者ごとのカテゴリ積み上げ棒グラフは出来たけど、
https://i.imgur.com/onXCZRL.png
(もう一つ同じピボットテーブル作って、値の「計算の種類」を「行集計に対する比率」にしたもの)

> 一人に対して一つの棒グラフでその一人の棒グラフの中で比率を表示させたい
というのは、
個数のラベルに比率も付け足す
または
個数表示を比率に置換えたい
ということ？

だとすれば、ピボットテーブルで可能かどうかは
俺にはわからない。

964 ：名無しさん＠そうだ選挙にいこう：2022/06/05(日) 21:42:15.26 .net

>>963
これです！やりたかったのはまさにこれです。比率じゃなくて個数表示で大丈夫です
でも値エリアを「合計/個数」にするやり方がわからないです。
すみません、基本的なことなんでしょうけどどうやったんでしょう？

965 ：名無しさん＠そうだ選挙にいこう：2022/06/05(日) 22:36:58.01 .net

>>964
列：カテゴリ
行：日にち、担当者
としておいて。
Σ値のフィールドを「個数」にするだけで、集計方法のデフォルトは「合計」

966 ：名無しさん＠そうだ選挙にいこう：2022/06/06(月) 01:38:56.98 .net

Σ値のフィールドを「個数」（←フィールドにない）にするってとこがよくわからなかったですけど
Σ値に「担当者」で同じ結果になったのでこれでいいかって感じです。
どうもありがとうございました。

967 ：名無しさん＠そうだ選挙にいこう：2022/06/06(月) 01:54:23.29 .net

久しぶりにここ覗いたらみんな動的配列の扱い劇的に上手くなってるな
1年くらい前に見たときは俺が教えることもあったけど今は俺が習わなきゃだBYROWが実装されたのに気づくの遅れたのはイタいｗ

968 ：名無しさん＠そうだ選挙にいこう：2022/06/06(月) 01:56:35.74 .net

VBAのSpillRangeプロパティは最初俺が使ったときバグあってVBA内でも#アドレスで逃げてたけど、もう治ってるんかな

969 ：Nouble：2022/06/06(月) 02:10:52.69 .net

>>850
少しだけ　検証してみました。

excel365、
挿入できませんでした、
https://1drv.ms/v/s!AjviygfJDgV_gYYIVqc-bteS8g3N6A


excel2019
挿入できました、
https://1drv.ms/v/s!AjviygfJDgV_gYYKro1OstPe7gTUKA


という事で、

環境と、バージョン、
伺っていい手スか？

970 ：名無しさん＠そうだ選挙にいこう：2022/06/06(月) 02:39:30.66 .net

>>956
>>962
ありがとうございました。
ラムダ関数使えば出来ることを確認しました。
できればラムダ関数でやりたかったのですが会社のExcelが半期チャネルにつきクエリのやり方検討してみます。

971 ：Nouble：2022/06/06(月) 05:45:41.94 .net

>>853
>>854

>>855
いやいや、

そんな　無駄な、
事は、
excelに　任せておけば、
いいのですよ。


其れって、

こういう　事ですよね？
https://1drv.ms/x/s!AjviygfJDgV_gYYLIwZQFTXafV3M-g


excelでは、

一日を　　1、
１時間を　1/24、
１分を　　1/(24×60)、
こう　管理しています。


又、

表示と、内部値は、
別管理です。

972 ：Nouble：2022/06/06(月) 05:49:12.27 .net

>>853　>>854

>>855　>>971
P.2、
例えば、

2022/5/27日なら　44708、
2020/11/6日なら　44141、

なので　普通に、
退社日から　入社日を、
単純に　引けば、
勤務通算日数に　なるのです。


では、

此を　日付表記に、
変える　方法ですが、
表示形式を　日付にすれば、
日付で　表示されますし、

セルの　設定→表示形式
内の　ユーザー表示書式で、

yy"年"mm"ヶ月間"
と　設定すれば、

お望みは　其れで、
済む　話しなのですよ。

973 ：Nouble：2022/06/06(月) 05:52:26.98 .net

>>853　>>854

>>855　>>972
P.3、
又は、

入社日、退社日、
共に　何月までしか、
換算しない　方が、
いいなら、

=DATE(YEAR(退社日,MONTH(退社日),1)-DATE(YEAR(入社日),MONTH(入社日),1)

こう　換算してから、
計算するだけですよ。

974 ：名無しさん＠そうだ選挙にいこう：2022/06/06(月) 06:42:37.08 .net

>>954
=IF(INDEX(Sheet1!$B$1:$B$25,COLUMN()-1)="",A2,INDEX(Sheet1!$B$1:$B$25,COLUMN()-1))

975 ：Nouble：2022/06/07(火) 04:23:47.17 .net

>>884
P.1、
ザックリとだけ　いうと、
3σ域傾向でしょうね。



元表ですが、

区間を　６つに、
別けてますよね？
最頻値は　中央付近ですから、

両翼3σの　正規分布を、
取っている　ものと、
思われます。


詰まり、

行５、６、
が　１σ、

行４、７、
が　２σ、

行３、８、
が　３σ。

976 ：Nouble：2022/06/07(火) 04:25:06.47 .net

>>884
P.2、
さて、

ご質問の　式では、
ザックリといえば、

行３、８、
此れ等の　傾向を、
取ってますよね？


私では、

１列目見出し項が　読めないのですが、
其の　項名に、
当たる　ものの、
３σ区間平均が、

中央値や、最頻値と、
如何　ズレているか、

裾野と　全体の、
傾向差を　取っていると、
思われますが。

977 ：Nouble：2022/06/07(火) 04:27:47.12 .net

>>976
>>884
P.2、
此、

企業内の　最も、
極秘Classのノウハウと　思われますよ？

貴方　危険な、
事　してますよ？

バレたら　賠償、
とんでもない　額、
請求兼ねませんので、

早めに　会社、
辞めて、
消息　経ちませんか？


こんなにも、

不用意に　危険行為して、
迂闊にも　人生、
捨てては　いけませんよ？

978 ：名無しさん＠そうだ選挙にいこう：2022/06/07(火) 07:01:08.70 .net

20年ぐらい2chやってるけどここまで読む気にならないレスは始めてみた
凄い才能だ

979 ：名無しさん＠そうだ選挙にいこう：2022/06/07(火) 07:12:35.06 .net

ポエムかな

980 ：名無しさん＠そうだ選挙にいこう：2022/06/07(火) 07:22:18.80 .net

AIが作成した文章じゃないかな

981 ：名無しさん＠そうだ選挙にいこう：2022/06/07(火) 07:49:29.01 .net

相手にしてる時点でお前らも同類

982 ：名無しさん＠そうだ選挙にいこう：2022/06/07(火) 07:51:17.43 .net

>>981
何一つ理論的でなくて草

983 ：名無しさん＠そうだ選挙にいこう：2022/06/07(火) 08:19:18.28 .net

AVERAGEで標準偏差が計算出来る
という珍説現る

984 ：名無しさん＠そうだ選挙にいこう：2022/06/07(火) 08:29:16.92 .net

品質管理なんかで使う3σはこういうこと。

> 正規分布の場合、
>
> 1σ 区間におさまる確率→ 約 68.27%
> 2σ 区間におさまる確率→ 約 95.45%
> 3σ 区間におさまる確率→ 約 99.73%
>
> であることが知られています。

1σ、2σ、3σの意味と正規分布の場合の確率
https://mathwords.net/sigumakukan

985 ：Nouble：2022/06/07(火) 10:58:10 .net

>>954
基本式、

A1=OFFSET(Sheet1!$A$1,COLUMN(A1)-1,0,1,1)
B2=IF(OFFSET(Sheet1!$A$1,COLUMN(B6)-1,1,1,1)="",A6,OFFSET(Sheet1!$A$1,COLUMN(B6)-1,1,1,1))


実現手順、

B1=IF(OFFSET(Sheet1!$A$1,COLUMN(B1)-1,0,1,1)=0,"",OFFSET(Sheet1!$A$1,COLUMN(B1)-1,0,1,1))
B2=IF(B1="","",IFERROR(IF(OFFSET(Sheet1!$A$1,COLUMN(B2)-1,1,1,1)="",A2,OFFSET(Sheet1!$A$1,COLUMN(B2)-1,1,1,1)),OFFSET(Sheet1!$A$1,COLUMN(B2)-1,1,1,1)))

B1:B2を　コピー、
1:2を　選択、
選択範囲に　貼り付け。

986 ：Nouble：2022/06/07(火) 11:12:00 .net

>>985
失礼、

作成済み検証ファイルと、実証映像を、
添付し忘れていました。


ファイル、

https://1drv.ms/x/s!AjviygfJDgV_gYYTB33H2cSlXYmmXA


映像、

https://1drv.ms/u/s!AjviygfJDgV_gYYV7WxgjRMRY2kKMQ


尚、

ファイルは　1度、
ローカルに　別名保存して、
其の　保存ファイルを、
扱うように　してくださいね、

別名保存でないと、
意味が　無いですよ。


さすれば、

閲覧も、編集も、
叶うものと　思いますよ。

987 ：Nouble：2022/06/07(火) 11:27:26 .net

>>983
記載読みに、

誤認識が　あるようなので、
訂正しておきましょう。


私は、

敢えて　気を付けて、
「標準偏差、」「正規分布、」「歪度、」
等とは　書かず、
「傾向、」としか　書いてません。

勿論　平均では、
標準偏差は　出なくとも、
傾向は　出ますよね、

詰まり　全くの、
思い込み変換からの　誤認いいかがかりですよ？


脳内誤変換で、

書いている　事を、
よめてもない　内に、

不実を　元に、
誤認から　誹謗されては、
困りますね。

988 ：名無しさん＠そうだ選挙にいこう：2022/06/07(火) 14:41:00.99 .net

頭の病気なんですか？

989 ：名無しさん＠そうだ選挙にいこう：2022/06/07(火) 14:57:47.10 .net

頭の病気というよりASDなんじゃないですか。

990 ：名無しさん＠そうだ選挙にいこう：2022/06/07(火) 16:27:46 .net

初動ミスですよ
構ってくれる人が居るから嬉しかったのでしょう

991 ：名無しさん＠そうだ選挙にいこう：2022/06/07(火) 16:44:09 .net

変なの居着いたから、このスレはそっと閉じよっと

992 ：名無しさん＠そうだ選挙にいこう：2022/06/07(火) 19:35:19.92 .net

>>955
FILTER関数とTRASPOSE関数でいけんじゃね
たしかFILTER関数は空白が0になったから

993 ：Nouble：2022/06/07(火) 23:46:42.75 .net

>>976
P.1、
失礼、

記載AVERAGE構文の　読み方を、
見間違えて　いましたね、

此では　改竄者と、
同列でしょうね、

お詫びします。


さて、

他の　方も、
触れられているようですが、

解らない　人が、
馴れと、誤認思い込みから、
間違いと　勘違い断定して、

過去　改竄して、
しまったのでしょうね。

994 ：Nouble：2022/06/07(火) 23:51:07.26 .net

>>993　>>976
P.2、
excel365で、

色々　確認した、
結果も　踏まえ、

区間区切りと　集計値が、
変わらない限り、
分布が　変わるだけでは、
同じ　結果になりますから、

計算する　意味は、
ないでしょう。


又、

= AVERAGE(セル範囲1,セル範囲2)
仮に　こう、
式を　置いた場合、

セル範囲1と、セル範囲2が、
提示票のように　隣接してるなら、

別けても、別けなくても、
結果が　一致するので、
（,）の　存在意味も、
ありません。

995 ：Nouble：2022/06/07(火) 23:53:06.68 .net

>>994　>>976
P.3、
以上より、

〉本来は（,）では、
　なかった、
　誰かが　誤改竄した、
と、

確定的に　判定できるでしょう。


受けて、

平均の　種類内で、
平均の　関数に、
近くなる　ものを、
探してみてみましたが、

元は　加重平均なのでしょね、　※1、
此だと　分布により、
価が　変化しますからね。

996 ：Nouble：2022/06/07(火) 23:55:22.82 .net

　>>976
P.4、
サンプル、

正規表記寄りデータ、
3 10
8 20
20 30
20 40
8 50
3 60
62 361.7

大区間側寄りデータ、
1 　　10
4.72 20
8.44 30
12.16 40
15.88 50
19.6 60
61.8 469

小区間側方寄りデータ、
16 10
20 20
15 30
6 40
4 50
1 60
62 251.7

997 ：Nouble：2022/06/07(火) 23:57:01.72 .net

　>>976
P.5、
此の様に、

何れも　合計値は、
ほぼ同じなので、
平均に　差は、
でない　ものの。


加重平均値には、

・361.7
・469
・251.7
と、

価差が　見られますからね。


加重平均計算式は、

= AVERAGE(セル範囲1*セル範囲2)
でしょうから、

式中から　思い込みで、
見慣れない　形態だからと

（*）を、(,)に、
書き換えちゃったのでしょうね、

998 ：Nouble：2022/06/07(火) 23:58:38.34 .net

>>997　>>976
P.6、
思い込みや、馴れで、
確認も　調べも、
せず、

私感を　ねじ込むと、
此の様な　事に、
なるのでしょうね。


確実に、

・中央値、・最頻値、・分布、・歪度、・尖度、
等を、
同時に　求めるので、

正規分布との　異差を、
見る　票でしょう、

又全体が標準偏差ではなく、
当該項が　別途、
求められてますよね、

あからさまに　標準偏差では、
ないでしょうね。

999 ：名無しさん＠そうだ選挙にいこう：2022/06/07(火) 23:59:03.73 .net

うめ

1000 ：名無しさん＠そうだ選挙にいこう：2022/06/07(火) 23:59:18.93 .net

終了

1001 ：2ch.net投稿限界：Over 1000 Thread

2ch.netからのレス数が1000に到達しました。