JustAnswer のしくみ:
  • 専門家に質問
    知識豊富な専門家があらゆる質問にお答えするために常に待機しています。
  • 専門家が丁寧に対応
    E メールやサイト内オンラインメッセージなど、さまざまな手段で回答を通知。必要に応じてフォローアップの質問をすることもできます。
  • 満足度 100% 保証
    専門家からの回答を確認し評価をすることで、支払うかどうかを決めます。
rushqkに今すぐ質問する
rushqk
rushqk, ITエンジニア
カテゴリ: IT・インターネット
満足したユーザー: 766
経験:  AV機器開発設計
60440014
ここに IT・インターネット に関する質問を入力してください。
rushqkがオンラインで質問受付中

こんにちは。以前の質問で、機械の稼働表(○)を付けていくのを教えていただきましたがマルの付いたセルが自動で色も表

解決済みの質問:

こんにちは。以前の質問で、機械の稼働表(○)を付けていくのを教えていただきましたが マルの付いたセルが自動で色も表示出来るようにしたいのですが、 うまくいかず、困ってます。セルの書式設定では出来なさそうで(会社のPCはEXELE2003でした) 関数式が沢山入っているので、あまり変更出来なくて・・・ 先生またまたお願いします。 それと何故か「indireect」関数で参照式を入れた表を作りましたが、(A4で収まるように昼勤務・夜勤務 を分けました。)うまく表に”○”が付きません。たしか、参照する元データを縮小して画面に出すと 参照する表画面に”○”が付きますよね?なんどやってもうまく機能しなくなりました。

 

サンプルです。

http://ux.getuploader.com/sampledate1920/download/3/%E6%99%82%E9%96%93%E3%81%AE%E8%A8%88%E7%AE%97%E5%B8%B3.xlsx

 

http://ux.getuploader.com/sampledate1920/download/2/%E9%9B%86%E8%A8%88%E3%82%B5%E3%83%B3%E3%83%97%E3%83%AB.xls

 

以前の先生からのサンプルを使用しています。

集計サンプルのE2セルにある¥tetsuya-akamatsu¥Desktop¥の表示が「時間の計算帳xls 10月1日に名前が変更しないから参照出来ないのでしょうか?B9、B22には変更したセル($H~K、A2だったかな?)

を入れています。

投稿: 4 年 前.
カテゴリ: IT・インターネット
専門家:  rushqk 返答済み 4 年 前.

質問ありがとうございます。

 

色をつけるのであれば条件付書式を使うと良いです。ただし、2003などでは3条件までしか設定できません。

また、Indirect関数は、参照先のシートが開いていないと正しく動作しません。

今手元にある2003では正しく表示されなかったので、帰宅後2010で再確認してみます。

 

2003と2007以降はExcelの動作が結構変わっていますので注意が必要です。

専門家:  rushqk 返答済み 4 年 前.
確認しましたが、ダウンロードしたものと、10月.xlsを同時に開けば○は正常に表示されます。
このシートの条件では同じフォルダ内に置かないと反映されません。
また、条件付き書式も問題なく機能しているようです。

ちょっと状況がわかりにくいので、申し訳ありませんがファイル構成と状況の説明
をもう少ししていただけませんか?
質問者: 返答済み 4 年 前.

http://ux.getuploader.com/matamataonegaisimasu/download/2/%E6%99%82%E9%96%93%E3%81%AE%E8%A8%88%E7%AE%97%E5%B8%B3.xlsx

 

http://ux.getuploader.com/matamataonegaisimasu/download/2/%E6%99%82%E9%96%93%E3%81%AE%E8%A8%88%E7%AE%97%E5%B8%B3.xlsx

 

おはようございます。さっき夜勤から帰ってきまして、先生の返事が気になり

すぐ開けました。先生は動作確認はOKとしてますが、自分は全然うまくいかなくて

 

少し苛立ってます。言われた通り新しいフォルダーに2つ入れました。

”時間の集計帳”を開き。次に集計ファイルをコマンドから開き、”ウィンドー

整列ー並べて表示にして2分割にして並べました。

しかし、日付を入れても全然動かないです。動かないと言うか、円が出てきません

 

しかも書式設定では、指示を出せない状態です。ある程度頑張りましたが

すべての手順が分からなくなりました。

どうしましょ?

 

専門家:  rushqk 返答済み 4 年 前.

参照先の文字列生成を間違えていませんか?

何をどのように参照して、どのように表示したいのかの情報が少ないので、組み合わせる関数を提示するぐらいしかできません。

時間の集計帳についての説明がないので想像でしかないのですが、こちらのシートにある

時間を参照しようとしているのでしょうか?

そうであれば、以下の式を変更しないといけません。

「=+CONCATENATE("'",LEFT(CELL("filename"),FIND("[",CELL("filename"))-1),"[",B3,"月.xls","]",B4,"日","'!",)」

「=+CONCATENATE("'",LEFT(CELL("filename"),FIND("[",CELL("filename"))-1),"[時間の計算帳.xls","]",B3,"月",B4,"日","'!",)」

と言ったように変更する必要があります。

 

2003で扱うなら、2007形式のファイルは編集できませんので、xlsxではなく2003形式のxlsで保存してください。

「時間の計算帳.xlsx」ではなく、「時間の計算帳.xls」として保存し、上記の修正を反映したサンプルをUpしておきます。

http://kie.nu/xIa

 

質問者: 返答済み 4 年 前.

http://ux.getuploader.com/matamataonegaisimasu/download/5/%E6%99%82%E9%96%93%E3%81%AE%E8%A8%88%E7%AE%97%E5%B8%B3.xlsx

 

http://ux.getuploader.com/matamataonegaisimasu/download/5/%E6%99%82%E9%96%93%E3%81%AE%E8%A8%88%E7%AE%97%E5%B8%B3.xlsx

 

回答ありがとうございます。

先生の添付されたサンプルを見て入れ直しをしてみました。

が、動作がうまくいかない?自分の今使っているのは2007バージョンですので、

この構築の式は使えますよね?会社のPCは2003でした。

なので、.xlslの所を.xlsに直して入力すればいいのですね!(反対?)

 

それと編集後に{集計サンプル}のB37セルに###が出てきました。

これは何かが間違っているのでしょうか?

その前に、エンターキーを押したら”ドキュメントの保存?””式の編集”だったか

変な画面が現れて、{ファイルを開く か キャンセル} が問いかけられました。

なんでしょうかね?

 

 本題は「新しいフォルダー」内に2つのファイルを入れました。

会社では同じ様に「時間の計算帳」のA2に”L-1" B2に”開始時間” C2に”停止時間”

が入っており、それがL-23まであります。

また、夜勤でも同じようにA26(多分これくらいのセル)に”L-1”と上記と同じ内容が

入ります。今回G2セル~K2セルを作りましたが、本来のシートにはありません。

それは、自分が勝手に「$Bセルや$Cセルは昼と夜があるからややこしくならないかな?」と思ってあえて横に仮に集計した物を作成しました。もちろん、会社の方にも

同じ仮集計表を作成(編集)してもいいかなと思っています。

 

何をどうしたい?ですが、「時間の計算帳」の時間入力が(B・C列)行われます。

我々現場がそうして入力管理をします。上司が(主任)が後日何かの資料を作成

する時に機械の時間別稼働状態・時間別稼働台数をわかりやすくしておいて欲しいと
指示がありました。そこで、先生にお願いをしています”○”の表示により、30分間隔での稼働状態を表示出来ないか?と考えております。

 

自分の考えている方向性はこんな感じです。

出来れば一からやり直しが一番かと・・・思いますが。

それはどんな式だったか今手元にないのですが、「時間の計算帳」の時間入力(B・C列)も実は入力規則をかけています。リストを作成してそこから選んでね!とみんなに

伝えて入力をしてもらっています。式とは、各機械の停止時間(C-B)をDに入れて

いますが、昼はいいけどややこしい夜勤の24:00をまたぐときですよね。

リストには24:00・24:15・24:30・24:45・1:00と15分間隔で刻んでいます。

式の構築上24:00にしているので、先生の集計サンプルの時刻表示のN21=0:00  

O21セルは=N21+TIME(0,30,0)と違う表現になりますよね。

 

今晩会社で構築式を見てきます。また明日にでも返事を出します。

長くなりましたが、読んでいただきありがとうございます。

では、仕事に行ってきます。

専門家:  rushqk 返答済み 4 年 前.
ファイル名xlsやxlsxを直接変えても形式は変わりません。動作不良の原因になります。上書き保存ではなく名前を指定して保存を選び、保存形式を変更してください。
保存形式は2003互換のxlsです。
ファイルの指定ダイアログがでるのは正しい指定ができていないからです。この

サンプルの方は正しく動作していますか?

今日は出かけていますので回答は遅い時間になると思います。
申し訳ありませんが了承願います
質問者: 返答済み 4 年 前.

http://ux.getuploader.com/sampledate1920/download/4/%E6%99%82%E9%96%93%E5%85%A5%E5%8A%9B.xls

 

 

http://ux.getuploader.com/sampledate1920/download/5/%E9%9B%86%E8%A8%88%E3%82%B5%E3%83%B3%E3%83%97%E3%83%AB.xls

 

お忙しいのに申し訳ありません。先生のご指摘通りに、バージョンを落として

保存するを選択して拡張子を変更しました。動作確認は良好でした。

 

集計サンプルのB4セルにある日付を変えるとさっと画面が動きますけど

変える度にその日付のデータが下の方のセルに反映されますか?

 

それとも「ここに反映して!」と日付別表を前もって作成しておかないといけませんか?何故か日付を変えてるだけで、下に反映されていません。

 

しかし、やはり夜勤の時間入力と「停止計」の式が集計サンプルの式とマッチしていないのが原因か”○”の表示がイマイチうまくいきません。

 

それとあの「丸が付いたらセルに色をつける」みたいな書式設定ですが、どうすれば

そんな条件が出せるのか?さっぱりです。何度も条件を変えてみてもエラーでした。

 

「セルの数字が」  「次の数字の・・・」 「式入力」 ???

先生は条件付き書式でうまく機能していると言ってましたがなぜですか?

 

よろしくお願い致します。なお、いま仕事で使用しているままのシートを集計サンプル

の10月1日にアップしました。「停止計」が問題の(特に夜勤です)構築式ですので

 

ご査収をお願い致します。

 

専門家:  rushqk 返答済み 4 年 前.
確認しました。
時間入力.xlsの10月1日が、10月1日と、1が全角になっています。
そのため、「1」と「1」で違う文字として認識されて参照できなくなっています。

条件付き書式は、2003では書式メニューの条件付き書式で設定でき、
「セルの値」「次の値に等しい」「="O"」として設定すればOKです。
ここではマルではなくオーを入れているので"O"ですが、○ならもちろん「="○"」です。
質問者: 返答済み 4 年 前.


確認ありがとうございます。全角になっていましたか。


気がつかなかったです。ご指摘ありがとうございます。


 


ところで、構築した式はどうでしょうか?やはり何度やっても18:30からの


表ではうまく動作しません。0:00の表現もいけないでしょうか?


 


また例えば10月1日シートのM2セルを0:00より前の23:30と入力しても


丸が23:30を超えて朝までついてしまうのです。やはり式がまずいの


 


では?

専門家:  rushqk 返答済み 4 年 前.

参照先を間違えていませんか?

B9には以下の式を入れてみてください。

「=+IF(INDIRECT($E$2&"$C"&$A9+1)<>INDIRECT($E$2&"$D"&$A9+1),IF(B$8>=INDIRE CT($E$2&"$C"&$A9+1),IF(B$8<=INDIRECT($E$2&"$D"&$A9+1),"O",""),""),"")」

B22には以下の式を入れてみてください。

「=IF(INDIRECT($E$2&"$C"&$A22+19)<>INDIRECT($E$2&"$D" &$A22+19),IF(B$21>=TIME(12,0,0),IF(B$21>=INDIRECT($E$2&"$C"&$A22+19),IF(B$21>=INDIRECT($E$2&"$D"&$A22+19),"O",""),""),IF(B$21<=INDIRECT($E$2&"$C"&$A22+19),IF(B$21<=INDIRECT($E$2&"$D"&$A22+19),"O",""),"")),"")」

質問者: 返答済み 4 年 前.

http://ux.getuploader.com/sampledate1920/download/7/%E9%9B%86%E8%A8%88%E3%82%B5%E3%83%B3%E3%83%97%E3%83%AB.xls

 

http://ux.getuploader.com/sampledate1920/download/6/%E6%99%82%E9%96%93%E5%85%A5%E5%8A%9B.xls

 

おはようございます。回答ありがとうございました。

急な研修だったので、確認が遅くなり申し訳ありません。

 

今先生の式を再度入力してみましたが、やはり○が続きます。

「時間入力.xls」のC・D20セルですね。本来、「集計サンプル.xls」のG22〜M22

 

に○が付きますよね?しかし、M22を超えて○が付いてしまうのです。これがよく

わからなくて・・・

 

本当に何度もスミマセンが再度のご確認をお願い致します。

専門家:  rushqk 返答済み 4 年 前.

停止時刻が24:00よりも前だと、時間帯計算がおかしくなっていますので、

停止時刻の24:00またぎの条件を考慮すれば正しく表示されるようになります。

 

逆に開始時刻が24:00以降になってしまうと、この場合も条件計算がおかしくなり

ますので、開始時刻、停止時刻を先にチェックしてから○をつける条件を整理すれば

全て正しくなると思います。

質問者: 返答済み 4 年 前.


回答ありがとうございます。流石に先生ですね。


やはりひとつの条件を直すと、新たに問題が出てくる事をお気づきに


 


なるとは・・・しかし自分には到底理解できませんね。難しすぎる・・・


条件の順番を変えても全然動きませんし、式の理解も半分以下でしか


 


理解をしていません。やっぱりエンジニアには凄さを感じます。


出来れば先生の具体的な式と解説をいただきたいです。

専門家:  rushqk 返答済み 4 年 前.

時間を計算しやすいよう に、夜勤側の値で開始、終了共に12時より小さな値は
一日繰り上げるように処理すると楽になります。時間入力のDとFに補正結果を入れています。
集計サンプル側は0:00も前の値から+30分として、こちらも一日繰り上がるようにしてます。
これで計算式は単純化されるので昼勤と同じ計算で○を付けることができます。
なお、DとFは入力者が安易にいじらないようにセル幅を0にしてあります。
運用するなら、入力セル以外はすべてロックして編集できないようにしておいた方が
いいかもしれません。

隠れた列はCとEの間の|をダブルクリックすることで見ることができるようになります。
また、この列だけわかりやすいように書式をyyyy/mm/dd hh:mm:ssの形にしています。

http://kie.nu/yUB

余談ですが、質問の趣旨として、質問者様が作れるようになる事がゴールだと思っていますので、
ヒントやサンプルは出していますが私の方で作ることは控えています。
まどろっこしいかもしれませんが、あえてずばりの答えを出していません。
もちろん、不具合対策などの場合はできる限り早く解決できることを目指して回答しています。

質問者: 返答済み 4 年 前.


全くの御意見と回答、ありがとうございます。


私自身も最終目標として、「自分で色々試して答えを出してみる!」でした。


 


仕事でも後輩達に簡単には正解を言わないし、逆に話をして答えを出す


「気づき」を大事にしております。


 


恥ずかしながら”学”が無いためにしつこく質問ばかりして、ご迷惑をおかけしました事を心からお詫び申し上げます。


 


頑張って、関数を自分なりに勉強してみます。


またいつか先生の御指導を受けるかもしれませんが、その時は


 


よろしくお願い致します。ありがとうございました。


 

専門家:  rushqk 返答済み 4 年 前.
意図した動作はするようになったでしょうか。
足りない個所や、細かい動作で困ったところがあればお尋ねください。
rushqkをはじめその他名のIT・インターネットカテゴリの専門家が質問受付中
質問者: 返答済み 4 年 前.


昨夜、休日出勤で頑張って解読してみましたが、どんなにやっても


動こうとはしません。図を書いて行き先の確認をしてみたり、動作の


 


指示を追いかけながらやってみましたが、お手上げです。


何かたりないのでしょうか?思いつかなくなりました。


 


完全にKOされました。

専門家:  rushqk 返答済み 4 年 前.

 

念のため、文字化けしていたのでUpしなおしました。

http://kie.nu/zeC

 

動作結果としてImage1.pngを一緒にいれておきました。

このような動作結果にならないということでしょうか。

graphic

質問者: 返答済み 4 年 前.

ありがとうございます。そうです。そのような動作結果にはならず


日付セルを(1・2日)変えても###のままでした。


 


###ということは、参照場所違い?式が成り立たない?のでしょうね。


自分なりに考えてみましたが無理だったので、先生の提示された式を


 


入れ直してみましたが、それでも動かない?


そんな状況です。何がどうおかしいのかがさっぱりです。


 

専門家:  rushqk 返答済み 4 年 前.

sampleにあるファイルを同時に開き、編集可能な状態にしていますか?

参照できない場合や、エラーの場合は#REFと表示されます。

###ということは単純にファイルが開いていない、もしくはロックがかかっている状態だと思います。

 

質問者: 返答済み 4 年 前.


すみません。もう一度確かめたいのですが、集計サンプルと時間入力ファイルは、確か同じフォルダーの中に入れておいて、集計サンプルを開いた後に


 (どちらが先でもOKだと思うのですが・・・)左上の”ファイル”から時間入力ファイルを選択して開き、表示-分割のダイアログで画面上に2つ並べると


認識しています。が違いますか?

専門家:  rushqk 返答済み 4 年 前.

分かりにくい表現ですみません。

「同時に」とは、開く順番は関係ありませんが、両方とも開いている状態であれば

よいです。

 

集計サンプル.xlsのE4あたりに「=+INDIRECT(E2&"A3")」と入力してみてください。

時間入力.xlsの方を修正したときに、反映されているでしょうか。

質問者: 返答済み 4 年 前.


返事が遅くなりすみません。さっき夜勤から帰ってきました。


家で確認しましたが、言われたようにE4に入れると”L-1"だったか?


 


何かしら入り、反映されたようです。しかし、会社で同じように


やってみましたがエラーでした。


 


そのエラーは、「数式で使用されるデータの形式が正しくありません」


と出て、セルに合わせると# VALUE! と出ました。


 


これは一体なんでしょう?

専門家:  rushqk 返答済み 4 年 前.

#Value!は数式である必要の箇所が文字列混入していたりするとでるエラーです。

セルに緑▼がついていませんか?そちらを押すと何がエラーなのか表示されます。

さらに、計算過程もでますので確認してもらえますか。

以下のページが参考になります。

http://blog.yumizo.gonna.jp/?eid=391731

 

 

セキュリティ関連の設定で、全ての編集がOFFになっている可能性もありますので

確認しなおしてみてください。

 

当初のシートでは会社の方でも動作していたのであれば、当初のシートをベースに修正

を加えていってみてください。

質問者: 返答済み 4 年 前.


会社で確認しました。計算過程のエラーチェックをしましたところ


・・・参照データ.xls]11月1日'!$B"&"L-1+1)<>INDIRECT($E$2&"$D"($A9+1),IF(B$8・・・)      ここのアンダーライン部が


エラーチェックされました。


やはり何かがイケナイのでしょうか?


修正する方法も分からずじまいで。  

専門家:  rushqk 返答済み 4 年 前.

集計サンプルのセルを編集したものでエラーがでているのですよね?

元ファイルでは問題が起きていないと思います。

 

L-1というのは機械No.の項を1だったものをL-1とした結果だと思います。

項目を利用して参照位置を変更させているので1でないと計算できません。

L-1とつけたいのであれば、L-1から1だけを抽出(RIGHT関数など利用)して、それを数値に

変換しないといけません。もしくはA列のL-1の横のB列に見えない列を作って、1といれておき、参照先を

A9ではなくB9にしておくといった方法が考えられます。

質問者: 返答済み 4 年 前.

ありがとうございます。早速今夜現場で作成・確認をしてみます。


また報告します。宜しくお願いします。

専門家:  rushqk 返答済み 4 年 前.
了解いたしました。

少しでも編集してから動作しない場合は、編集した個所も教えていただかないと
回答するのがむずかしくなりますのでよろしくお願いいたします。
質問者: 返答済み 4 年 前.

 


動作確認完了しました。ありがとうございます。


初めはやはり動きませんでした。表示すら出ない(真っ白の状態)


事もあり、諦めかけていましたが先生のアドバイスを思い出し


なんとか、完全に動きました。参照場所やセル番地がずれていたり


等、編集を沢山していたために頭もシートもぐちゃ*2になっていました。


 


この出来たファイルを今後続けて行こうと思いますが、(実はみんなに


先生の指導があってこのような事が出来る様になったと広めてしまい)


12月・1月・・・と参照元のフォルダー内のシートを増やさないといけませんよね?今の11月が出来上がりこれをコピーして12月としても中の


日々別作業シートを(セルや日付)編集していかないとおかしくなりますよね。毎日一つづつ編集は大変です。(泣)


さて一体どのようにして11月1日の作業シートを早く増やして行くのか?


それだけどうかお願い致します。


 

専門家:  rushqk 返答済み 4 年 前.

一度整理して作っておけば問題ないと思いますよ。

もちろん、シートのコピーやファイルの増加はあると思いますが、そのあたりを処理

しやすくするためにIndirect関数を使って文字列を自動生成する処理をサンプルとして提示

しました。

参照先のセルの位置や名称を変更してしまうとズレが発生するのは避けられませんが

ルールを決めておけば運用だけで何とかなると思います。

これ以上の処理自動化を行うのであれば既存の関数を駆使するだけではなくVBAなどを

使っていく方が楽だと思います。VBAを使えばシートの追加も自動化できますが、もちろん

それなりの処理コードを書かないといけません。

 

シートの構成に関してはデザイン設計を行って、どのような事をするのか、どのようなファイル

を扱って、扱うデータの構成はどうするのかと言った全体像を考えておけばいいと思います。

慣れない規模の処理を場当たり的に作っていくとどこかで破綻します。

 

rushqkをはじめその他名のIT・インターネットカテゴリの専門家が質問受付中

IT・インターネット についての関連する質問