[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『指定したセル全てがブランクなら画像を表示させたい』（りんこ）
読み込み専用ブックのシート２のセル（B27:X38）の範囲がすべてブランクならば、アクティブシートであるシート１のC8の位置を基準として画像ファイル”予定はありません.jpg"を表示させたいのですがうまくできません。
入力専用ブックから読み込んだ結果、1か所でもブランクでなければ表示させる必要はありません。
netやここの掲示板で色々調べたのですがわかりませんでしたので、質問させていただきました。
教えていただけたら嬉しく思います。
＜ 使用 Excel：Excel2016、使用 OS：Windows10 ＞
とりあえず、以下の構成を前提とした、マクロを使わない案なぞ書いてみますので、応用してみてください。
・対象とするブックは1つだけ。
・画像を表示／非表示したいシートをSheet1とする。
・B27:X38をブランク判定するシートをSheet2とする。
・シートを1つ追加し、これを「DATA」というシート名にしておく。
まず、DATAシートのA1セルには「FALSE」、A2セルには「TRUE」と入力しておきます。
DATAシートのB2セルに、表示したい画像を挿入してから、画像サイズとセルサイズを調節してください。
（B2セルサイズに収まった画像だけ表示されるようになります）
次に、「数式」－「名前の管理」に名前の定義を「新規作成」します。 名前は「画像」とし、参照範囲は以下の数式としてください。
=INDIRECT("DATA!$B$"&INDEX(MATCH(COUNTBLANK(Sheet2!$B$27:$X$38)=276,DATA!$A:$A,0),1))
Sheet1のC8セルには、何でも良いから画像を挿入してください。
Sheet1の画像をクリックし、次に数式バーをクリック。数式バーには、以下の数式を入力してください。
=画像
これでできあがりです。 Sheet2のB27:X38に何か入力したり、全部消したり試してみてください。
うまく画像が切り替わるようならば、DATAシートの「表示」－「枠線」のチェックを外すと、画像非表示時に余計な灰色が出なくなります。
（???） 2020/03/11(水) 16:58
ありがとうございます。
若干、情報が不足していたことをお詫びします。
このブックに表示されるものは他のブックで入力されたものであり、このブック上で入力をすることはないという意味でした。
したがって、当ブックを編集することで問題を解決しようとしております。
マクロを使うにかと思っていましたが、関数でできるとはすばらしいです。
結論から申し上げますと、教えていただいた手順で期待通りの結果となりました。
ありがとうございます。
当初は以下の通りエラーが出ておりました。
当該ブックのコピー上で
■済■DATAシートを作成、入力、挿入
■済■数式＞名前の定義＞新規作成
■済■Sheet1＞C8セル＞透明画像（PNG)を挿入
（画像を挿入し通常表示される文字が隠れるのを防ぐため透明化）
ここまでは順調にできました。
画像をクリックして数式バーに、=画像 を入力しエンターを押すと
参照が正しくありません
となります。OKボタンを押しても、=画像 の式を消さない限り、どこを触っても同じ表示でした。
複製ブック３個までは同じ症状のために
新規ブックで試したところ、不具合が再現しなかったので、当該ブックに記述しているマクロが悪さ
しているのかと悩んでいました。
再度、当該ブックを新たにコピーしたファイルで試したところ、今度はうまく動きました。
スペースの確認、文字種の確認などやっていることは全く同じはずだったのですが・・・・
とても助かりました。
素晴らしい、アドバイスありがとうございました。
（りんこ） 2020/03/12(木) 11:07
「=画像」とするとエラーになる場合、名前の定義を再確認してみてください。 どういうタイミングかまでは把握していませんが、数式内で参照しているセル範囲が変わってしまう場合があるので、再設定すると直ります。（変わってしまうのは最初だけで、うまく画像が切り替わるようになると、後は問題ないようです）
（???） 2020/03/12(木) 11:25
数式を再設定しましたが、変わらずです。色々と調査中です。
（りんこ） 2020/03/12(木) 16:52
276になっていないようなら、範囲コピーして別のエディタに貼り付けてみるとか、空欄に見えるけどスペース文字になっているようなものがないか調べましょう。
（???） 2020/03/12(木) 17:04
>実際に入力用ブックから読み込んで
どうやって読み込んでいるのでしょうか？ 数式で、= "" とかなる場所ってないですかね？ 数式の COUNTBLANK で面倒なのは、これもカウントしてしまいます。
VBAの
Range("xx:xx").SpecialCells(xlCellTypeBlanks).Count
と、挙動が違う。
この前、数式で全くの空白セルの個数を出してみようと考えてみたけど。 なんか面倒くさい事になりそうだったので、数式では無理？と思って途中でやめた。 （BJ） 2020/03/12(木) 18:07
あれ、セル範囲のセルの個数を調べたんだったっけ？ （BJ） 2020/03/12(木) 18:17
↑ 素直に、=ROWS(A1:C6)*COLUMNS(A1:C6) で、すませれば良かった・・・。
で、 =ROWS(A1:C7)*COLUMNS(A1:C7)-COUNTA(A1:C7) とか？ （BJ） 2020/03/12(木) 18:28
行、列の削除追加はなく範囲指定は一致しています。
その範囲のCOUNTBLANKを実施したところ276にならず108でした。
結果は以下の通りです
[Sheet2]
_________B______C~F_____G~M_____N~U _______V______W~X
内容_____判定____文字______h:mm____文字_______数字_____h:mm
Blank_____12_______0________0_______12_________0_______0
小計______12_______0________0_______96_________0_______0
BLANK合計108
判定のB列とN～U列は正常にカウントされていますが
B列は予定があっても朝一はBlankなので使えません。
N～U列の計96で判定をすることもできますが、例外の発生する日もあるため
悩みどころです。
BJ様
当該BOOKの処理はB27:X38セルに参照元のBookの当該セルを ＝ を使って読み込んでいます
参照元BOOK側での操作
スケジュール更新時にマクロで Selection.ClearContentsと初期化。読み込むスケジュールがない場合はフラグを立てていないので初期化した状態で保存されています。
当該BOOKでスケジュールがない状態の時に
＝COUNTA(B27:X38)とすると276となってしまいます。
さらに謎が増えてきました。
（りんこ） 2020/03/13(金) 10:24
あと、CTRL+SHIFT+@ で、通常表示と数式表示を切り替えられるので、数式表示にしてから監視範囲を範囲コピーし、他のエディタに貼り付けてから、数式の抜けや間違いがないか確認してみてください。（数式設定忘れがあれば普通の空欄になるので、それもカウントされるから、問題にならないはずですが…）
実際に監視対象セル範囲に書いている数式（列毎に違う？）を教えてもらう事はできますか？ もちろん、ブック名やシート名は他の文字列に変換して構いません。
または、N:U列を使うように変えて、例外はIFERRORで対応すると良かったりしますか？
（???） 2020/03/13(金) 11:29
SUMは最初にトライしました(笑）
CTRL+SHIFT+@の技はすごいですね。式の見直しも簡単で素晴らしいです。
監視セルはすべて式が埋まっていますが
＝予定表!C6 ←同じブック
の一連がすべて入っております。
ちなみに上記セルに入っている式は
'\\サーバー\予定表\[予定表【入力】.xlsm]予定表'!B6 ←別ブックA
B6に入っているのはマクロで読み込んだ文字が
A-1 のように表示されています。
マクロが読み込んでいるのは
='\\Wサーバー２\\計画表\[計画表.xlsm]平日用'!$B$5 ←別ブックB
B5セルはプルダウンでA-1を選択しています。
全体を見ましたが空白などは入っていませんでした。
N:U列を使うように変えて、例外が発生する場合は次の通りです。
通常はN:U列に人名(社内）が入ります。
しかし、予定表全体では社外の予定も入るのです。
社外の場合は人名以外の予定はわかります。
つまり、予定は見えますが、人名が空欄となります。
社内の計画が無く、社外の計画がある場合に人名が空欄となるので
例外と考えております。
ここが悩むところです。
（りんこ） 2020/03/13(金) 13:26
空欄なら空欄の結果にする場合、「=IF(予定表!C6="","",予定表!C6)」のようにするかと思うのですが、条件付き書式で0は見せなくしているのであれば、空欄ではないという事になります。
N:U列利用の場合は、社外ならハイフンを埋める、とかしないと、空欄と区別が付かないようですね。
（???） 2020/03/13(金) 13:52
質問の冒頭に書かれていた「読み込み専用ブック」は、いつも同じブックなのですか？
いつも同じなら、そっちを見て判断すればいいと思うんですがねぇ・・
（半平太） 2020/03/13(金) 14:33
経過概要
監視対象セルはシート２のB27:X38
B27:X38は同じブックのシート「予定表」を＝予定表!C6のように見に行っていました。
したがって、教えていただいたように「=IF(予定表!C6="","",予定表!C6)」の処理を
全ての監視対象セルにそれぞれ実施。しかし、ブランクのカウント数は変わりませんでした。
BLANK合計108 のまま
次に
シート「予定表」は別ブックAの予定表を ＝ で参照しているので
上記と同様の処理を実施しました。今回はキーとなるC列のみを実施
すると、12個のBLANKセルを見事にカウントして１２となりました。
数式の定義を=INDIRECT("DATA!$B$"&INDEX(MATCH(COUNTBLANK(Sheet2!$C$27:$C$38)=12,DATA!$A:$A,0),1))
に変更し、試しに入力用のBookで3/14の予定を空欄にして更新すると、期待どおりに図が表示されました。
次に3/16の予定をいくつか入れて更新すると、いままでは正常に更新していたのですが全く更新されず
図は表示されたままに。（数回実施して、この状態は再現しています）
図を消すだけであればマクロで行けるのですが、更新された予定を読み込まないのが原因不明です。
＞ユーザー定義で「0;;」にしている、等
オプション>詳細設定＞ゼロ値でゼロ値を表示するのチェックをはずっしてありましたのでBlankではなかったと思われます。
＞「読み込み専用ブック」
いつも同じです。別のブックは時代を経て別の方たちが作成しておりましたので、基本触らないようにしております。
ここで、お礼をして問題解決と思っていたのですが、また週明けにチャレンジします。
また、アドバイスがいただければ幸いです。
（りんこ） 2020/03/13(金) 16:32
１．例えば、DATAシートのE1セルに以下の数式を入れる。 ※この設定作業をする時は、読み込み専用.xlsxは開いて置いてください。 その方が楽です。作業が終わったら、閉じて構いません。
E1セル =COUNTBLANK([読み込み専用.xlsx]Sheet1!$B$27:$X$38)
<DATA シート 何も入力されてない場合のサンプル> 行 ____B_____ _C_ _D_ E 1 276 ←何も入力がないのに、ここが276にならなかったら、 2 貼付け画像 元ブックの状態も検査する必要があります。
２．名前定義 名前 → 警告画像 参照範囲→ =IF(DATA!$E$1=276,DATA!$B$2,DATA!$B$1)
３．Sheet1の画像をクリックし、数式バーに以下の数式を入力する。 =警告画像
（半平太） 2020/03/13(金) 18:52
↑ 上の案は、撤回します。m(__)m
入力専用のブックでカウントしないと、いつまでも画像が出てしまう。
（半平太） 2020/03/13(金) 19:51
完全空白の数 =ROWS(A1:C7)*COLUMNS(A1:C7)-COUNTA(A1:C7)
数式で空白（=""、長さ0の文字）の数 =COUNTIF(A1:C7,"")-(ROWS(A1:C7)*COLUMNS(A1:C7)-COUNTA(A1:C7)) =COUNTBLANK(A1:C7)-(ROWS(A1:C7)*COLUMNS(A1:C7)-COUNTA(A1:C7))
両方込みなら =COUNTIF(A1:C7,"") =COUNTBLANK(A1:C7) （BJ） 2020/03/14(土) 22:44
予定がない日に図（予定なし）を表示している状態で、翌日の予定を読み込ませても表示が変わらなかった件について
＞試しに入力用のBookで3/14の予定を空欄にして更新すると、期待どおりに図が表示されました。
＞次に3/16の予定をいくつか入れて更新すると、いままでは正常に更新していたのですが全く更新されず
＞図は表示されたままに。（数回実施して、この状態は再現しています）
（1）当初はExcelを立ち上げ直して対処していましたが、原因は長時間エクセルを表示し続けることによる
メモリーが一杯になるのでは？と思い、
(2)MS社純正のツール「rktools.exe」をインストールしてbatファイルを作り、10分ごとにbatファイルを起動するようにタスクスケジュラーで組んでみました。 ⇒予定がない日からのリカバリーには影響しませんでした。翌日の予定を入れる直前にメモリーを解放したほうがいいかもと思い
(3)上記マクロを作動させるボタン１を挿入し、使ってみました。⇒これも効果がありませんでした。
これとは別に、ごくまれに表示が停まることがあるために、マクロをリスタートするボタン２を挿入しておきました。
昨日、予定なしから予定ありに切り替えた際に、ボタン２を押したところ、見事に復帰しました。
もともと組んでいたマクロと予定なし画像を表示させるマクロの相性がいたずらしているのかなと勝手に解釈していますが、運用上は問題が解決し楽になりました。
最初に自分で作ったマクロに不要なところがあるとは思いますが、今後少しずつ見直していきたいと思います。
アドバイスいただいた皆様、ありがとうございます。
（りんこ） 2020/05/19(火) 10:01
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.