Excel総合相談所 136
■ このスレッドは過去ログ倉庫に格納されています
【1 OSの種類 .】 Windows** 【2 Excelのバージョン 】 Excel** 【3 VBAが使えるか .】 はい・いいえ 【4 VBAでの回答の可否】 可・否 ▼━関連スレ━━━━━━━━━━━━ 前スレ Excel総合相談所 https://find.5ch.net/search?q=excel Excel VBA 質問スレ https://find.5ch.net/search?q=excel+vba 【質問不可】Excel総合相談所スレの雑談・議論スレ https://find.5ch.net/search?q=excel+%E3%80%80%E7%9B%B8%E8%AB%87%E6%89%80 >>13 なんかこう、電気信号をスカラー波みたいなものに変えて…無理か。 >>21 ふむふむ、ありがとうございます 参考になります とても簡単な話なんでしょうが解決できません。 A1:AM9:00 B1翌AM9:30 C1にA1~B1の間の22:00〜5:00の時間数を抽出したいです。 17:00〜翌9:00ってのはできたんですが24時間超となるとできませんでした・・・ 何度もさーせん・・・だめでした・・・教えてください。 >>24 時間軸に沿って並べると ア 当日の09:00 A1セルに入力 イ 当日の22:00 ウ 翌日の05:00 エ 翌日の09:30 B1セルに入力 でいいんですよね? であれば、質問にあった「A1〜B1の間の」の部分は無視して 単純に上記イ〜ウの時間数の計算でいいのでは? >>27 ありがとうございます そのように、セルを分けることができたらよいですが 膨大な人数及び入力者はそれぞれ、なので 入力してもらえるセルはA1とB1のみなんです。 せめてB1を33:30としてくれたら助かるんですが それも望めません・・ >>28 当日の09:00 や 翌日09:30 が可変データで、当日の22:00と翌日の05:00が固定データということですか? >>27 で示した時間軸の並びが変わることもあるということ? >>29 9:00と翌9:30が可変データです。22:00と5:00は固定です。 時間軸の並びは変わらないです。 >>28 時刻として入力されたデータを「翌日の時刻」として扱いたいのであれば、+1するだけです。 当日の09:00から翌日の09:30までの時間は、=B1+1-A1 で計算できます。 B1+1 で、B1の時刻は”翌日の”という意味になるわけです。 ところが表示されるのは 0:30、そこでセル書式を h:mm から [h]:mm に変更します。 [h]は、24時間以上の時間数を 日 へ繰り上げずにそのまま表示する書式設定のこと。 >>31 ありがとうございます +1まではわかりました。 =MAX(MIN(29/24,B1+(B1<A1))-MAX(22/24,A1),0) これで深夜帯拾ってます。どこに+1すればよいですか? >>30 可変データであれば、時間軸の並びは変わる可能性があるってことですよ。 ア 当日の22:00 イ 当日の23:00 A1セルに入力 ウ 翌日の05:00 エ 翌日の09:30 B1セルに入力 A1セルに入力する時刻が当日の0:00〜23:59までの範囲の値をとる可能性があれば、 上記のように、A1セルの値(可変値)と当日の22:00(固定値)の並びが逆転することがある。 このとき、求めたい時間は当日の23:00〜翌日の05:00ということになる。 並びが逆転しないんであれば、当日の22:00(固定値)〜翌日の05:00(固定値)の時間は 7時間で一定、Excelの出番はない。 >>32 /24 の意味がわからない。 セルに時刻として入力したら、24時間を数字の1としてみなします。これをさらに24でわったらダメです。 時刻として入力したセルから時間数(2時間なら数字の2として)を求めたいのであれば、逆に ×24 すること。 >>34 勘違いしてた。時刻としての22、29(翌日の5時)を24で割って時刻値にしてたのね。 >>32 の式をそのまま =MAX(MIN(29/24,B1+(B1<A1))-MAX(22/24,A1),0) 改良した式 =MAX(MIN(29/24,B1+1+((B1+1)<A1))-MAX(22/24,A1),0) >>31 に B1+1 で、B1の時刻は”翌日の”という意味になる と書いたとおりです。 >>24 =TEXT((IF(A1>TIMEVALUE("22:00"),A1,"22:00"))+IF(B1>TIMEVALUE("5:00"),"5:00",B1),"hh:mm") すまんちょっと抜けてた =TEXT((1-IF(A1>TIMEVALUE("22:00"),A1,"22:00"))+IF(B1>TIMEVALUE("5:00"),"5:00",B1),"hh:mm") アンティルとドゥーンを組み合わせた全く新しいループを発見した 【1 OSの種類 .】 Windows10 【2 Excelのバージョン 】 Excel2019 【3 VBAが使えるか .】 いいえ 【4 VBAでの回答の可否】 否 もとから入っているユーザー設定リストを削除、もしくは無視させる方法はありませんか? どなたかExcelの神髄ってサイトの総合練習問題2を解説していただけないでしょうか? https://excel-ubara.com/excelvba1/EXCELVBA326.html 特にわからないところがなぜ合計を初期化するのか? 数値型への変換、指定数値以上合計に加算の3つのコードの意味です。 >>46 カスみたいなサイト。もう見るな lngIn = strIn '数値型への変換 変換しても何の意味もない 数値型の「確認」にして数値型でなければ終了という処理にしなければならない lngTotal = 0 '合計を初期化 これは必要 明示的にやっておいて損はない、というかやらないと大抵良くない事になる。俺も絶対にする >>36 37 ありがとう。 早速試してみたよ しかしながら問題が・・ A1:AM9:00 B1:翌AM9:30 この時は、お二人が教えてくれたのでできました。 でも A1:9:00 B1:18:00 という場合にも、深夜帯が無いのにC1:7:00となってしまう。 ここはC1:0:00となってほしい。 >>37 のはtextは要らなかったので消してやってみたけど、上の問題が生じます。 >>50 >>27 ,33 をよく見てほしい。 A1セルには当日の時刻、B1セルには翌日の時刻として書いています。 >>36 にも B1+1 で、B1の時刻は”翌日の”という意味になる と書いています。 B1の時刻は翌日であるという前提なので、18:00の場合は 翌日の18時 とみなして計算してるので、 当日の9時〜翌日の18時の間の22時〜翌日の5時である7時間を返すのは当然。 B1も当日の時刻が入力されるのであれば、当日と翌日の境の時刻を明示する必要があります。 例えば、勤務開始時刻は9時、かつ 勤務開始時刻から24時間を超える終了時刻(B1)はありえない、ということであれば、 B1がとりうる当日の時刻は 9:01〜23:59 で、翌日の時刻は 0:00〜8:59 ということになります。 計算式の中の B1+1 の +1 の部分をB1の値によって +1 するかしないかを調整すればいいです。 計算式の中に2か所ある B1+1 を B1+(B1<TIMEVALUE("9:00"))*1 に変えてみてください。 >>51 当日9時〜翌日9時という24時間勤務がありうるのであれば、 >>51 で書いた B1がとりうる当日の時刻は 9:01〜23:59 で、翌日の時刻は 0:00〜8:59 ということになります。 を B1がとりうる当日の時刻は 9:01〜23:59 で、翌日の時刻は 0:00〜9:00 ということになります。 に変更し、 >>51 の最後の行は 計算式の中に2か所ある B1+1 を B1+(B1<=TIMEVALUE("9:00"))*1 に変えてみてください。 に変更します。 >>50 念のため、A1(開始時刻)はかならず当日の時刻になりますか? ありえるのかどうかわかりませんが、深夜0時以降の開始時刻を指定した場合、 現在の計算式では、当日でのA1の時刻〜終了時刻(B1)の時間で計算してしまいます。 例えばA1に1:00、B1に6:00と入力すると、本来なら5:00となるべきところ、7:00という 計算結果になります。 >>48 返信ありがとうございました。 まだVBAをかじり出した程度なので手探り状態です。構文を段階的に学べるおすすめのサイト、書籍などはありますか? >>53 何度も詳細にありがとうございます A1は確実に当日です。 >>53 =MAX(MIN(29/24,B1+(B1<=TIMEVALUE("9:00"))*1+((B1+(B1<=TIMEVALUE("9:00"))*1)<A1))-MAX(22/24,A1),0) この式に変えてみましたが、c1は0:00になりますね・・・ また、これは始業を9:00に固定した場合だと思うのですが 始業は当日の範囲で0:01〜23:59までばらばらです。 >>53 新しいシートで実験するとできました。 すいません。 >>53 いや・・・9:00から翌9:30だとできないですね・・ 9:00から翌6:00とかだとできるんですが。 何度も申し訳ないので整理させてください A1に入るのは0:00〜23:59 B1に入るのは0:01〜翌17:00 例えば 8/1の0:00から23:59までの間に始業 終業は8/1の0:01から8/2の17:00の間 という場合に、一つの関数で8/1の22:00から8/2の5:00 までの時間数を抽出する、というこです。 >>58 A1に9:00、B1に9:30と入力して、B1は当日の場合もあるんであれば、 当日の9時から9時半までの30分勤務の場合と 当日の9時から翌日9時半までの24時間30分勤務の場合という 2通りが考えられます。よって、どちらになるのかの条件を指定する必要があります。 もう一度>>51 をみてください。B1に入力された時刻を当日と翌日のどちらで判定するかの 閾(しきい)値をしてしなければなりません。 たとえば、終了時刻が当日の12時より前はありえないのであれば、 2か所の TIMEVALUE("9:00") を TIMEVALUE("12:00") に変更すればいいはず。 >>50 >>38 はAが当日、Bが翌日の前提で作った式だからそりゃできないよ Bが当日になったり翌日以降になったりするのなら、それが判別できる何かがないと計算のしようがない 日付用のセル作るとか、時刻のセルを日時にするとかね てかそれ人間が見る表としてもいつの時間なのか入力した人しかはっきりわからなくね? >>59 やってみます >>60 出勤簿なんで、前提として58の範囲に収まるので 人間は判断つきます >>61 5時 - 8時 だと3時間か27時間か人間でもわからないが 33:30と入力させるのは無理かもしらんが、 「翌」チェックボックスとか日付の入力は 逆に普通に入力させるべき事項じゃねーのこれw >>64 チェックボックスが良いね 当日と翌日のふたつ作りどちらかチェックしないと退出時刻を入力出来ないようにすればいい 当日退勤の列と翌日退勤の列をつくればいいんじゃない? 退勤の列を分けると誤入力が増え修正が面倒になる気がするな チェックボックスって連続コピーで計算式が出来なさそうなイメージ チェックボックスと言うよりオプションボタンだな たとえば男か女のどちらかを押さないと氏名を入力出来ないように入力規則を設定し記入もれを防ぐのと同じ 動作はリンクさせたセルの値が変化するだけ 押されてない状態は空白、当日を押せば1、翌日を押せば2って具合だ 【1 OSの種類 .】 Windows10 【2 Excelのバージョン 】 Excel2016 【3 VBAが使えるか .】 いいえ 【4 VBAでの回答の可否】 否 計算式の連続コピーに関する質問です ◯のところのシートに計算式A1/B1、一つシート開けてその下の◎のシートに計算式A2/B2を入力し、また一つ空白にしてその下にA3/B3…っていう連続の計算式を入力をしたいです。 しかしオートフィルを利用すると、A3/B3としたいシートがA5/B5に、A4/B4としたいシートがA6/B6、A5/B5としたいシートがA9/B9…といった感じになります。 もちろん空白をなくせばしっかり連続の計算式が入力できますが、空白を1シート作った上で連続の計算式を入力する方法はありますか?(オートフィルを使わなくても大丈夫です) >>74 あ、間違えた わかりにくくしてしまいました、すいません >>71 方法1 A10に =OFFSET($A$1,(ROW()-ROW($A$10))/2,0) A10:A11を選択してずらずらっと下に連続コピー 方法2 A10に0を入れる A10:A11を選択して下にずらずらっと連続コピー。これで一つ飛ばしの連番ができる。 B10に =OFFSET($A$1,$A10,0) B10:B11を選択してずらずらっと下に連続コピー ということかな? あとは工夫で >>76 回答ありがとうございます、OFFSET利用しようと思うのですが、A10とA11はどこから出てきたのでしょうか? >>71 単純にコピーするだけでいいんだな? 仮にC1〜C10に式を入れるとして、D列を作業列とする @C1〜C5にオートフィルで数式入れる A = を☆とか数式に無い文字に置換 AD1〜D5に1〜5を入れる BD6〜D10にAを貼る CA〜D列をD列昇順で並べ替える DD列削除 E☆を=に置換 >>77 >>73 の画像の〇があるアドレスが不明だったのでA10と勝手に決めました >>79 なるほど ただ上記のだと計算式A1÷B1の設定がない気がするのですが… >>78 最初にそれを考えたのですが、縦列1000近くあるので、星→=の置換が大変かなと >>80 >空白を1シートx(セル)作った上で連続の計算式を入力する方法はありますか?(オートフィルを使わなくても大丈夫です) という要望だったのでわかりやすく原理の説明をしたわけです。 あとは工夫で 頭つかってね >>81 まさか手入力で置換してるのか?置換機能で置換するんだぞ… 一行おきに空白をセルを挿入するアドインがあるから それを使っちゃうな 手っ取り早いしw >>83 あ、なるほど ありがとうございます、ならできそうです Manbowさんもありがとうございました 【1 OSの種類 .】 Windows7 【2 Excelのバージョン 】 Excel2016 【3 VBAが使えるか .】 いいえ 【4 VBAでの回答の可否】 否 A列からC列までの合計とE列からG列の合計が間違っていたら メッセージを出す関数を作っています。 =If (SUM(A1:C1)=SUM(E1:G1),"","エラーメッセージ") まではあっさり書けたのですが、 行が、例えば100くらいになった(A100:C100とE100:G100)場合、 どうすればいいのでしょう。 まさか、If関数のカッコを外側にどんどん追加して書くわけにはいかないし。 やっぱり、マクロの出番でしょうか? ついでに、row関数で、どの行が間違っているかわかれば楽だと思って試行錯誤中です。 これも、=ROW()&"行目"まではあっさり書いたのですが、 if関数と合わせる関数が書けなくて。 長々とすみません。 >>87 H列に書くと思うから、 I1に=COUNTIF(H:H,"エラーメッセージ") とかやるとか まぁフィルタかマクロか、その辺はルーチン次第かね 自分でその場で治すならフィルタかけてぽちぽちやれば終わりだし、 数式触られたくないならマクロが良いだろう >>87 >=IF(SUM(A1:C1)=SUM(E1:G1),"","エラーメッセージ") という計算式をどのセルに入力してますか? ”作業セル”というのを知ってますか?上記の例で言えばF1セルに上記の計算式を入力し、 F1セルを下方向へフィルコピーするだけで F2セルの式 =IF(SUM(A2:C2)=SUM(E2:G2),"","エラーメッセージ") F3セルの式 =IF(SUM(A3:C3)=SUM(E3:G3),"","エラーメッセージ") のように、手間はかかりません。 もし、既存の表に作業セルを設定することが難しいのであれば、エラーメッセージの代わりに ”セルに着色”という手もあります。 >>89 アルファベットの順番間違えたorz 作業セルのF1 は H1 に読み替えてください。 >>87 全行を判定する式を1セルで済ませたいってこと? >>88-91 迅速なレスをありがとうございます。 そして、まとめてレスですみません。 H列やI列に、下に引っ張って結果を出したら できれば特定のセルだけに結果を出してほしいと言われて。 〇行目が違う、という感じで。 今のところ、SUM関数で全体を見て、 どこが違うかはわからないけれど違う箇所がありますよ、 で妥協してもらっています。 ちなみに、1銭もお金は動いていません。 知り合いに頼まれて試行錯誤しているだけなので。 >>92 H1=IF(SUM(A1:C1)=SUM(E1:G1),"",ROW()&"行目が違う、") I1=I2&H1 下にオートフィル >>92 相違がある行の(行全体でもA列だけでもいいけど)セルの着色じゃダメなの? >>92 それは間違ってる箇所は1つって前提? 1つでも複数でも、チェック用の列作ってそこから答えを出すってのが一般的なんだがその列を作るのもNGなん? でないと無駄に複雑になるが 【1 OSの種類 .】 Windows7 【2 Excelのバージョン 】 Excel2016 【3 VBAが使えるか .】 はい 【4 VBAでの回答の可否】 否 @UCL・LCLのある管理図(グラフ)でエリアごとに色分けする事は可能でしょうか? 規格外のエリアは赤、UCL・LCLのエリアは黄色、規格内のエリアは緑といった感じです (紙にプリントしてプロットするので薄い赤、黄色、緑です) 今は管理図の各エリアにテキストボックスを貼り付けて色付けしていますが、 規格値の見直しがあった際にテキストボックスの位置を調整するのが何かと面倒くさいので・・・ A規格値が100+35/-20(80〜135)でUCLが120、LCLが90の時に規格外の時はセルを赤にして黒字でNG、 UCL・LCL外れの時はセルを黄色にして文字は黒字で管理値NG、規格内のときはセルを緑にして黒字でOK と表示させる方法を教えて下さい >>96 UCL・LCLはどうでもよくて、 単にグラフの既定値に色分けするってこと? 標準機能では無理だ vbaでも結構めんどくさい気がする。おそらく最初の高さ等は手動で算出することになる >>96 @はこのサイト参考にしてみるといいかもね https://www.waenavi.com/entry/20190124/1548275841# 1折れ線グラフの場合-1 A >「規格外の時はセルを赤にして黒字でNG」 >「UCL・LCL外れの時はセルを黄色にして文字は黒字で管理値NG」 >「規格内のときはセルを緑にして黒字でOK」 ・これはグラフじゃなくリストの方? ・具体的に「セル」がどこのセルを指しているの?この説明では分からん ・ちなみに何かを書いているセルに、色を付けるのは可能だけれど、文字を上書きするのは関数では無理 【1 OSの種類 .】 Windows10 【2 Excelのバージョン 】 Excel2019 【3 VBAが使えるか .】 はい 【4 VBAでの回答の可否】 否 文書を上書き保存するたびにOneDriveにアップロードするようになってしまったのですが これをやめさせる方法はありますか? 【1 OSの種類 .】 Windows10 【2 Excelのバージョン 】 Excel2010 【3 VBAが使えるか .】 少し 【4 VBAでの回答の可否】 否 よろしくお願いいたします。 あるセルへの入力で、 基本的にはダウンリストからの選択だけど直接入力も可能にしたい、 というような場合は、どういう設定にするのが一般的なんでしょうか? 例えば、性別欄で、基本的にはダウンリストから「男」「女」を選ばせたいのだけど、 直接入力で「どちらでもない」とか「答えたくない」「秘密」など、自由に入力することも 可能にしたいです。 実務で使い込んでおられる方は、こういう場合はどうしてますか? >>99 エクスプローラー、OneDriveのローカルにあるファイルを読みに行くとどうなる? >>97 >>98 @は諦めます Aの質問はA1のセルの値に対してB1に「OK」「管理値NG」「NG」と表示&セルを色付けです(厳密にはA1自体は別途計算された値が入ります) >>100 入力規則のドロップダウンリストは任意の値は入力できないから フォームのボタン使うしかないんじゃない? こんな風に2列になっていて、日付と項目があり、最後に合計が書いてあるシートがあるのですが https://i.imgur.com/keGTaW5.png 日付 金額 日付 金額 項目 XXX 項目 XXX 項目 XXX 項目 XXX 項目 XXX 項目 XXX 項目 XXX 項目 XXX 合計 ZZZ 合計 ZZZ ※項目の数は可変 これを ↓ こんな風に変換するにはどうすればいいのでしょうか? 日付 ZZZ 日付 ZZZ 日付 ZZZ 日付 ZZZ >>104 各日にちの合計にある数式をK列のセルにコピー >>105 すいません、JK列はこちらの画像を作るために手動で作りました。 質問としては、JK列をどうやって作ればいいのかってことになります・・・ VBAで2行目以降の、列Oと列AFが同じ値の行を削除して、行を詰める場合のコードを教えてください >>104 あとあとのこと考えると、元帳をさっさと 日付 項目 金額 で正規化・DB化して、 ピボットテーブルで日付別に集計した方が 良さそうだけど・・・ >>106 各日付と合計セルをJKから参照すればいんじゃないの?もっと複雑な話? >>108 dim i as long with 対象シート for i=2 to 最終行 if .cells(i, "O").value = .cells(i, "AF").value then .rows(i).delete next end with ※速度無視の単純コードなので行数増えれば増えるほど遅い 遅くなってすみません。 >>93-95 何度もありがとうございます。 着色で妥協していただくか、マクロを組むか…。 チェック用のシートを作って動かしていたんですが 違う場所をわかるようにして! Excelで作れないの? となってしまって困っていました。 皆様のレスを見せて妥協していただきます。 >>111 チェック列作ってオートフィルタかけるってのがとってもシンプルで結果も一目瞭然なんだがな… 何がそんなに嫌なんだろうな… >>99 保存オプションの中に、そんな設定見つからんぞ。 どうやってんだそれwwwww onedriveの共有フォルダにでも入ってるんじゃないか >>109 はい、そうなのですが、過去3年にわたって運用されていたので、手動で書き直すのは至難なんです・・・ >>110 項目数が可変なので、JKからは簡単に参照できないんです・・・ >>115 可変てどうやって変えてんの?行挿入削除程度なら相対参照利かんか? >>101 ありがとう、ローカルからだと大丈夫でした >>113 ネット上にあるファイルを直接開くと保存もネット上へ行うようになっているみたいです みんなほんとにExcel詳しいなあ。うらやましいわ。 >>117 上書き保存してるんだからそりゃそうだよね… 【1 OSの種類 .】 Android** 【2 Excelのバージョン 】 最新 【3 VBAが使えるか .】 【4 VBAでの回答の可否】 Androidでシートを縮小しても、ほぼほぼ見える範囲が変わらないんですが、何かいい方法ないですか 【1 OSの種類 .】 Windows2010 【2 Excelのバージョン 】 Excel2019 【3 VBAが使えるか .】 いいえ 【4 VBAでの回答の可否】 否 振り仮名表示してる漢字が、直前に平仮名・カタカナ・漢字を入力すると消えてしまい、どうしたら解決するでしょうか? 英字は大丈夫なので、編集するときは直前に英小文字を挟んで、その小文字の前から入力してます ググっても同じ症状が出てこないです ■ このスレッドは過去ログ倉庫に格納されています
read.cgi ver 07.5.5 2024/06/08 Walang Kapalit ★ | Donguri System Team 5ちゃんねる