Excel総合相談所 158

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

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

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

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

>>251

=LET(val,{1;2;3},
TOCOL(HSTACK(val,SCAN(0,val,LAMBDA(init,val,IF(val=2,"↑ is 2","↑ is not 2"))))))

Ifを評価する前に、列数が未確定な計算はできないってことのような気がする。

列数じゃなくて行数やった

arrの長さを変えないために足りない方の穴埋めに#N/Aが使われる
この#N/AをIFが評価して、以降#N/Aエラー
=REDUCE({1},{2;3},LAMBDA(arr,val,IF(TAKE(TAKE(arr,-2),1)=2,VSTACK(arr,"↑ is 2",val),VSTACK(arr,val))))
→ {1;2;#N/A;"↑ is 2";3}

最近FILTERXML関数というものを知って、使ってみようとしています。
やりたいことは、セルに入っているXML形式のデータから、特定の値を取り出すことです。

例えば、セルA1に以下のようなXMLが入っています：

<items>
<item>
<name>りんご</name>
<price>100</price>
</item>
<item>
<name>バナナ</name>
<price>80</price>
</item>
</items>


この中から、1つ目の商品の名前（「りんご」）だけを取り出したくて、
以下のように書いてみました：

=FILTERXML(A1, "//item[1]/name")


でも、結果が「#VALUE!」になってしまいます…。
XMLの構造が悪いのか、XPathの書き方が間違っているのか、そもそもFILTERXMLの使い方が違うのか、よく分かりません💦

ExcelのバージョンはMicrosoft 365です。
FILTERXML関数を使うときの注意点や、うまくいく書き方など、教えていただけると嬉しいです🙇‍♀

これだとエラーにならないのが不思議
=REDUCE({1},{2;3},LAMBDA(arr,val,IF(ROWS(arr)=2,VSTACK(arr,"↑ is 2",val),VSTACK(arr,val))))
IF条件式で配列の値を参照したときだけ、true値とfalse値との要素数違いでエラーになるみたいなんだ

>>257
問題をちょっとシンプルに
=IF(1=2,VSTACK(1,2),VSTACK(1))
=IF({1}=2,VSTACK(1,2),VSTACK(1))

シンプルに
=IF(1=2,{1;2},1)
=IF({1}=2,{1;2},1)

REDUCEの中で最初にNAが出てくるのはこんな感じで、
=IF({1}=2,{1;2;3},{1;2})
その後#N/Aで埋まるのはこんな感じか
=IF({#N/A}=2,{1;2;3},{1;2})

=IF({1;2}=2,{1;2;3},{4;5})

>>258
それ目的から乖離してます

>>257
どうしても動かしたい場合は、以下のように書けない事もないけど、どうせなら、アルゴリズムを考えるのに頭を悩ませた方が良いと思う。

=REDUCE({1},{2;3},LAMBDA(arr,val,IF(SUM(TAKE(arr,-1))=2,VSTACK(arr,"↑ is 2",val),VSTACK(arr,val))))

=REDUCE({1},{2;3},LAMBDA(arr,val,IF(SUM(TAKE(arr,-1))=2,VSTACK(arr,val),VSTACK(arr,"↑ is not 2",val))))

>>262
問題の原因は一言で言うとIFが配列数式になってるから

なるほど>>263でいけるならこうするのがよさそうだ
=REDUCE({1},{2;3},LAMBDA(arr,val,IF(INDEX(arr,ROWS(arr),1)=2,VSTACK(arr,"↑ is 2",val),VSTACK(arr,val))))
=REDUCE({1},{2;3},LAMBDA(arr,val,IF(INDEX(arr,ROWS(arr),1)=2,VSTACK(arr,val),VSTACK(arr,"↑ is not 2",val))))
TAKEの結果が配列なのが原因か

vstackって遅いから、勉強には良いかもしれんけど、大量の計算には向かないって言っとくよ。

@演算子
=REDUCE({1},{2;3},LAMBDA(arr,val,IF(@TAKE(arr,-1)=2,VSTACK(arr,"↑ is 2",val),VSTACK(arr,val))))
=REDUCE({1},{2;3},LAMBDA(arr,val,IF(@TAKE(arr,-1)=2,VSTACK(arr,val),VSTACK(arr,"↑ is not 2",val))))

>>267
なぜわざと抜かす？
性格悪いな

改良してみた

=REDUCE({1},{2;3},LAMBDA(arr,val,IF(SUM(TAKE(arr,-1))=2,VSTACK(arr,"↑ is 2",val),VSTACK(arr,val))))

=REDUCE({1},{2;3},LAMBDA(arr,val,IF(SUM(TAKE(arr,-1))=2,VSTACK(arr,val),VSTACK(arr,"↑ is not 2",val))))

ありがとうございます

ファイルを開いた当日から5営業日後の日付を表示したいのですが数式を教えてください
よろしくお願いします

出来ました
ありがとうございました

どういたしまして
お役に立てて光栄です

>>273
今後とも変わらぬご愛顧賜りますよう何卒よろしくお願い申し上げます

>>271
連休は最大5日間まで
=LET(
days, 5,
holidayList, {"2025/1/1","2025/1/2","2025/1/3"},
dateList, TODAY()+SEQUENCE(6,,days),
daysList, BYROW(dateList,LAMBDA(date,NETWORKDAYS(TODAY(),date,holidayList)-1)),
XLOOKUP(days,daysList,dateList)
)

>>275
ちょっと何言ってるか分からない

ではこちらでいかがでしょうか
=WORKDAY(TODAY(),5)

祝日を除外したい場合は、祝日の日付をリストにして、以下のようにします：

`excel
=WORKDAY(TODAY(), 5, 祝日リスト)
`

たとえば、セル A1:A10 に祝日が入っている場合：

`excel
=WORKDAY(TODAY(), 5, A1:A10)
`

これで、土日＋祝日を除いた「5営業日後」の日付が表示されます。

>>277
馬鹿は消えろ

>>279
まだバグ報告できないの？

>>277-278
もう休んでいいよ

シート1
4/1　aさん　1件
4/5　bさん　2件
4/10　cさん　1件
4/15　aさん　1件
4/20　dさん　1件

↓

シート2
aさん　2件
bさん　3件
cさん　1件
dさん　1件

今まで手計算で集計してましたが、これ自動集計で出来るようにしたいです
xlookup使えばいいのでしょうか

sumifじゃない？

365ならGROUPBYを使え

セルa1a3a5a7 のいずれか１個以上のセルに何か入ってる時に
入っていないセルに色を付ける
条件付き書式設定って可能?

>>285
=AND((A1&A3&A5&A7)<>"",A1="")

相変わらず質問するのは馬鹿ばっか

=AND(A$1&A$3&A$5&A$7<>"",A1="")

>>286
感謝

>>287
年収低そう

底辺の人に好かれても

A列に日付が入ってて
別の列で前日と同じかのフラグを作って
書式設定で前日と異なれば違う色にしている
この方法がとフィルタかけると異なる日が並んでても同じ色になってしまう
どのようにしたらいい？

>>291
>>3

担当者　店舗名　販売先　商品①販売額　商品②販売額…
となっているものをピポットテーブルを利用して、

担当者
A店　合計/商品(①+②)販売額合計　個数/販売先

と集計しました
ここに【合計/商品(①+②)販売額合計】/【個数/販売先】という感じで平均額を求めたいのですが、
平均/商品(①+②)販売額合計にすると合計と同じ数字が出てきてしまいますし、集計フィールドの挿入で上記の式を入れてみたのですがエラーが出て計算できません
ピポットテーブルで集計方法が違う値同士の計算をしたい場合良い手段はないでしょうか

もはや、Excelの問題と言うより算数レベルであいまいとしか
合計を出した計算式、平均を出した計算式ぐらい記述しないと、こちら側ではなんとも

filter関数やgroupby関数でゴリゴリ作れば如何様にもできる

マクロの記録で
複数のセルを選択してDELキーで削除
を記録しました
そのマクロを実効すると正しく実行できます
しかし元に戻す
ができません
元に戻すを可能にするにはどうしたら良いですか?

>>295
=LET(
担当者,"Aさん",
担当者行,FILTER(ROW(A:A),A:A=担当者),
店舗名,CHOOSEROWS(B:B,担当者行),
販売先,CHOOSEROWS(C:C,担当者行),
商品販売額合計,BYROW(CHOOSEROWS(D:E,担当者行),SUM),
ROWS_UNIQUE,LAMBDA(x,ROWS(UNIQUE(x))),
PIVOT,DROP(GROUPBY(店舗名,HSTACK(商品販売額合計,販売先),HSTACK(SUM,ROWS_UNIQUE),,0),1),
HSTACK(PIVOT,CHOOSECOLS(PIVOT,2)/CHOOSECOLS(PIVOT,3))
)

考え方としては、選択したセルの値を削除前にどこかに予めコピペしとく
またそれを消す操作が必要に成るけど

>>298
具体的に

>>296
SendKeys "{DEL}"

え？　何のために特定のセルを選択して、敢えてマクロで消したいかわからんけど
消してしまってから｢あ！いけね！｣が有り得るような業務体系なら
幾重にも予防措置を用意しとかないとならない

1）選択した範囲を、AAA列の999行目に貼り付けておいてからDeleteする
2）マクロ実行前に「本当に消してもいいか？元に戻せないぞ？」のMsgboxを出して
　から実行させれば、削除した本人の責任にできる
3）Sendkeys を使って Delete と 元に戻す(Ctrl+ｚ) を操作する ←推奨されてない
4）Undo が必要なら、マクロでは特定の範囲を選択するだけに留めておく
　その後、削除も元に戻すも手動でやらせる
ヒトは大抵ばかだから、いっくら先々を考え抜いて工夫を凝らしても、それを上回る
ミスを犯すのが一般的
しかし、まだマクロの記録のレベルなら、1）でやっといて、戻す場合はAAA999から
必要な範囲を再度コピペし直して貼り付け直す

と書いてみたけど、選択範囲が四角とも限らなかったな　飛び飛び範囲を選択してたら
戻すのが一苦労だった　やっぱ4）で

c3の辺でウインドウ固定したんですが
なぜかaとb列が重複して表示されます
スクロールすると元に戻りますが何ですかこれは

https://dailyexcelhacks.blog/excel-window-freeze-trouble/
稀によくある現象　ほとんどのケースはいろんなことを同時にやってるケース

なぜおっさんは教えたがりますか

かまってもらえないからです

おっさんというかハゲジジイね
ポロシャツとラガーシャツ着てるwww

洗濯しろよ？
臭いから

>>304
「自分で調べろ」的なわざと教えない先輩や同僚を目にしてきてるから

もうその話終わったんだけど

>>304
ココは相談所ですよ
スレタイ読めます?

相談してるじゃん
脳が間抜けなのですか？

間抜けは話を終わらせられない

昨日のメインはインド米のカレーピラフで昼・夜それを食べた
今朝は玄米を炊く予定
玄米に卵、納豆、きむち、豆腐を入れたごちゃ混ぜ丼
これも健康にいいぞ

誤差を許容できない場合、整数以外は信用するな
A2：=ROUND(A1*24+1,0)/24

>>314
Excel2021だけどなった

>>315
原因は少数誤差っぽいんだけけど、理由がわからんくて気持ち悪い
書式なしで計算してみても誤差なんか出てないんだよなあ
日付と時刻を別々に処理するとしても、セルの表示だけズレるならまだギリ理解できるが、編集しようとした時だけズレるのはなんでだ？
https://i.imgur.com/iYABRb0.png

あと、これもしかして既知のバグで釣り質問かコピペだったりする？

確認ありがとうございます。
自分も昨日会社の人からたまたま指摘があり、
自分のPCや他の人のPCで出たので
もしかして有名なバグ？と思ってネットで調べても出てこなかったので
こちらで確認させていただきました。

Excelを含め大半のソフトウェアはIEEE浮動小数を利用しているから誤差が出る
https://learn.microsoft.com/ja-jp/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result

計算結果として「2025/10/1 23:59.99999999999997」みたいな値になって、
Excelがこれをどう解釈するかの統一がとれていないのだろう

>>318
Excelの場合1/24を24回足しても 0.999999 にならないらしい
誤差があったとしても、編集状態にしただけで表示が変わるのが今回の最大の謎

改行を含んだ文字列を値でコピペしても改行されないけど
編集でエンター押したら改行されるじゃん
処理が別なんでしょ

セルのコピペは同じ値なら表示更新されない
一方、編集バーは数値が値になったので表示が更新される
編集バーを再編集して確定したらセルは必ず表示更新される
なのかな

あと誤差があったとしても2025/10/1 0:00になるのはバグだよなあ

・式の時点では分からず
・式外してもそのセルを選択するまで分からず
・該当セルをday関数など指定してる場合も
　該当セルを編集バーで改行するまで変更前の結果のまま


これが特定バージョンの現象じゃなかったら
気づかずに日付計算してる人めっちゃいそう

ってチャッピーに聞いたら瞬殺なんだが

見るからに小数点が出る計算式を組むよりも
Time関数で一時間ずつ足してくのがお作法
=セル番地+Time(1,0,0)　邪道や我流で四苦八苦してもムダ

>>324
TIME(1,0,0)は1/24なのでアウト

UNIX時間のようにexcelも秒単位の整数にすればよかったのにね

>>325
Excelはミリ秒までサポートしてるから結局詰むし2038年に互換性の呪縛で苦しむことになるだけ

>>326
お馬鹿さん🌱

値貼り付け関係なく=A1+TIME(24,0,0)とすると2025/10/1 0:00ならない？

スマソ。
time関数は24で割った余りだから正しいのね。

「2025/10/1 1:00」と表示されているセルに「45931.999995」と打てば再現するな
45931.999995〜45931.999999を「2025/10/1 0:00」と間違えるバグなのでは

A1：「2025/10/1」
A2：「=A1*1000000」 (表示形式を標準にする）

A1に「45931.9999995」と打つ ⇒ A2が「45932000000」になる
A1を数式バーで編集状態にしてそのままEnter ⇒ A2が「45931000000」に変わる

これって別の年、月でも起きる現象なのか、今年の今月に限った話かで見方が変わってくるね
意図的に仕込まれたのかバグなのか何か理由のある仕様か、どれだろう

小数の計算で誤差が発生してそれがバグのトリガーになってるな
=REDUCE(45931+1/24,SEQUENCE(23),LAMBDA(s,_,s+1/24))-45932 ⇒ -5.82077E-11

結論としては：コンピュータの宿命で発生する誤差(Excelに限らず)
7.3-7 と 8.3-8 でも発生する(らしい バージョンに依りけりまであるそう)
そもそも手順の5.で、値として貼り付けする意味・意義はどうなのか、と
=TEXT(A24,"yyyy/mm/dd" & " " & "hh:mm:ss")とかを他のセルに設定するとかならまだ判る
https://learn.microsoft.com/ja-jp/answers/questions/4847589/excel-12-3-12-0-3000000000000010000-0-3-excel?page=1#answers
セルの表示と数式バーの表示が違うのはおかしいだろ！というお怒りなら判らないでも無いが
エキスパートを目指すなら、その気付きを糧にやよ励むのが正解
(すぐバグだ！何だ！と目くじら立てて鼻息荒くするのは賢人の行為とは呼び難い)

別にめくじら立ててないわな
自己紹介すんな

一旦アンインストールして三連休を過ごしましょう

自分がバグ見つけられなかったからってそんなイラつかなくても・・・ｗ

45932⇒45931に勝手に変わる件は目くじら立てていいと思いますよ
だれかできる人いたらマイクソにバグ報告しといて

社会生活でも人間関係でもバグと呼んでいい事象なんかいっくらだってあるのに
もっと狭くて、市販の業務アプリだのオンラインシステムだのでその事象があってもバグと言い募らないクセに
何故かExcelに関しては｢バグだ、バグだ｣と連呼する勢がそこかしこに存在する
何故だろう　言い易いんだろうな｢俺の機嫌を損ねるのはバグに決まってるだろ｣と　只の仕様なのに
7.3-7 8.3-8 で答えが0.3じゃ無い事があるのはバグだ!!って連綿と言い続けてればいい
Excelの中の人がいつか回答してくれるかもしれないから

浮動小数の演算で誤差が生じることをバグとは言ってませんよ
X.999995をX.000000としてしまうのがバグなのです

秒未満の時刻情報が編集&確定で失われるというか秒まで入力した状態で確定したらそうなるに決まってる
編集する際に丸められた24:00が0:00になっていることはバグ

マイナス時間を入力したいのに
時刻扱いされてマイナスが入力できないのは仕様?

精神が幼稚なのか？
いちいち報告しなくていいから

>>343
>343

>>342
セルの書式設定を文字列にしたら入力できるが
それじゃだめかな？

あのーそれだとメモ帳でしょ

まぁ職種や用途によるだろうけど、
VBAマクロまでいかずに、普通のマクロやエクセル表のテーブル化能あたりで事足りるよな？

1/24は、0.04166666666666670000
Time関数の(1,0,0)は、0.04166666666424130000
どっちを採るか
マイナス時間を入力とはどういう状況かわからんけど、マイナス表示をしたいなら
ファイル - オプション - 詳細設定で、□1904 年から計算する　にチェック入れる

>>348
それどんな環境で試した？
うちでは1/24とTimeは一致してるんだけど
https://i.imgur.com/FFYVDOP.png

>>349
環境は同じだけどその関数は目的と違う

質問させてほしい
新しく買ったPCに前使ってたPCのMSアカウントでログインしたら、前のPCと合わせて購入したOffice2019でExcelが起動した。
新しく買ったPCはOffice2021同梱だと思っていたのでOffice2021で起動するようにしたいが、「アカウント」→「ライセンスの切り替え」だと他のライセンスは表示されない状態
つまり、Office2021同梱だと思ってたのは勘違いということでよいのかな？
プロダクトキーもOffice2021のを入力してないから新たにOffice2021なりOffice2024のプロダクトキーを購入しないとOffice2019しか使えないということですかね？

>>351
アカウントの[ライセンスの切り替え]で、プロダクトキーを所有してる場合へ向かってく
>>349
すまん　テキトーぶっこいた　一時間ごとの前後の差を比べたつもりだったが、手抜きがあった
1/24もTime関数も、そのままだと末尾67だが、差分だと末尾13というだけだった
差分の方は×24しても0.999999999941792000000000 止まり　1/24もTimeも×24なら同じ値
で、
Excel公式では、Time関数も一日の中での扱いしか想定してないもより
https://support.microsoft.com/ja-jp/office/time-関数-9a5aff99-8f7d-4611-845e-747d0b8d5457
>TIME 関数で返される小数は 0 〜 0.99988426 の範囲内の値であり･･　←ここがミソだったようで
一日を超えるケースは、そのページの下段、関連項目-時間を加算または減算する のページを参照すべき
つまり　バグ　では無く　仕様だったようで、あんたらおそまつ