faq/5/445

セルの参照で背景も参照したい †

メッセージ †

サンプルのようにINDIRECTを用いて参照する場合で、参照元に背景を設定していて参照先でも同じ背景にしたいのですが、なにかいい方法はないでしょうか？

---

関数で解決したい？ †

M.Kamataki (2010-07-16 22:46:11 (金))

ご質問の意図がうまくつかめていないのですが、以下のような質問と意図は同じでしょうか。

Excel　関数を使ってデータと一緒にその書式もコピーしたいのですが・・・
http://okwave.jp/qa/q4788041.html

あとはC1セルとA1セルの内容が同じなら背景を設定するような「条件付きの書式設定」を使うくらいですかね。

関数での解決が希望です †

匿名 (2010-07-17 10:30:05 (土))

入力用のシートとそれを参照する印刷用のシートを作成しています。

入力用のシートには背景に複数の色が使われています。

INDIRECTを用いて印刷用のシートに表示するようにしていますが、その場合当然文字のみの参照になります。

ユーザーより背景も参照して欲しいと言われて関数を確認したのですが、見つからなかったので質問させてもらった次第です。

Calcにも書式を変更する関数はありません †

M.Kamataki (2010-07-17 18:27:34 (土))

上記で紹介したExcelに関する質問ページや以下のExcelの解説ページにもあるように、書式をコピーしたり変更するような関数はCalcにもありません。

Excel(エクセル)基本講座：条件付き書式の使い方
http://www.eurus.dti.ne.jp/~yoneyama/Excel/jyo-syo.html

同ページには、「エクセルの関数で書式を変更することは出来ませんので、この条件付き書式もしくはVBAを使います。」とも書いてあります。Calcでも事情は同じです。関数だけでは解決しないとお考えください。

ご教授願います †

匿名 (2010-07-21 12:26:30 (水))

複数色がある場合に条件付き書式で色分けするにはどのようにすればいいのでしょうか？
考え付くのが行に色のサインを設けて１は赤、２は青のようにする方法ですが、この場合背景が赤のときにいちいち１を入力しないといけないので面倒です。
自動で色コードのようなものを認識することは可能でしょうか？

追記：上記色コードはExcelのユーザー定義関数のColorIndexのようなものと考えて下さい。

サンプルをお願いします †

M.Kamataki (2010-07-21 14:23:56 (水))

ExcelとCalcでは条件付き書式の機能に違いがあります。Calcではスタイルを利用します。このあたりが勘どころですので、よく理解しないと前へ進めないと思います。違いについては、faq/3/126「条件付書式で1行おきに色付けする方法」を見ると良いかもしれません。

とりあえずCalcで条件付き書式を利用したサンプルを作成してみてください。未完成のものでもかまいません。

もしかするとCalcの条件付き書式ではうまくいかないのかもしれません。しかし、入力用シートと印刷用シートの関係がわからないので、その検証もできない状態です。サンプルがあると他のアイデアも出てくるのではないでしょうか。

＞追記：上記色コードはExcelのユーザー定義関数のColorIndexのようなものと考えて下さい。

どこか参考になるWeb上のページをご紹介ください。

添付致します †

匿名 (2010-07-21 15:40:51 (水))

添付ファイルのように『入力』シートに日付順で入力していき、色付けをしています。

sample2.ods

『表示』シートのH1セルに表示したい日付を入力すると日付別のデータが出ます。

この『表示』シートに『入力』シートと同じ背景を表示したいです。

ユーザー定義関数はこちらを参考にしました。
http://hp.vector.co.jp/authors/VA016119/hajimete/udf1.html

よろしくお願いします。

Re: 添付致します †

M.Kamataki (2010-07-22 10:20:56 (木))

サンプルを拝見しました。コメント「ご教授願います」の意味がようやくわかったような気がします。

シンプルな解決方法は、「入力」シートに「行に色のサインを設けて１は赤、２は青のようにする方法」だと思いますね。3色までなら、条件付き書式で解決できると思います。ただ、この方法は取りたくないということですね。

そこで、「自動で色コードのようなものを認識することは可能でしょうか？」という質問になっています。その意味ですが、色を行の背景に設定したら、その色コードを取得して条件付き書式の条件にしたい、ということになりますね。あってますか？

とりあえず、以下を調べた限りでは情報に行き着いていません。

OOoBasic
http://hermione.s41.xrea.com/pukiwiki/pukiwiki.php?FrontPage

色々考えてみましたが †

ike@九州 (2010-07-22 13:22:52 (木))

今回はマクロによるセルの変更イベントリスナーが良いような気がしました。

表示シートの H1 セルが変更された時に動作するよう設定しています。
入力シートの日付列の該当セルの背景色を取得して
表示シートの 7 行目から背景色を変更します(色の制限無しですね)

サンプルの　ツール＞カスタマイズ＞イベント
文書を開く時、ドキュメントを閉じる時に登録、解除のマクロを割り当てました（イベント名が分かり辛いですね）

使えるようでしたら、実シートに Functions、Module2 マクロを全てコピペして各イベントに割り当ててください。

sample3.ods

背景色を扱うユーザー定義関数 †

M.Kamataki (2010-07-22 21:49:11 (木))

「自動で色コードのようなものを認識することは可能でしょうか？」について調べてみました。OOoBasicで見つからなかった場合は、英語の「OpenOffice.org Community Forum」を調べます。以下のものが見つかりました。

Background Color Cell Count.
http://user.services.openoffice.org/en/forum/viewtopic.php?f=9&t=2741

Villeroyさんの以下のコメントでは、CELL_BACKCOLORというユーザー定義関数が紹介されています。

http://user.services.openoffice.org/en/forum/viewtopic.php?f=9&t=2741#p12146

=DEC2HEX(CELL_BACKCOLOR("Sheet1";ROW(A1);COLUMN(A1)))

などと指定すると、明るい赤なら「FF0000」などと返してくれます。

条件付き書式を利用したサンプル †

M.Kamataki (2010-07-22 22:03:20 (木))

Calcの条件付き書式は、Excelと違いスタイルを利用します。上で紹介したVilleroyさんのユーザー定義関数の中にセルに設定されたスタイル名を返すCELL_STYLEというのがありました。それを利用したサンプルを作成してみました。

faq5_445sample4.ods

「入力」タブの背景色はスタイルによるものに変更しています。「表示」タブではセルごとにフォントの書式が設定されていたので違和感が起きないよう、2種類のスタイルを追加しています。

書式を設定できる関数 †

M.Kamataki (2010-07-23 01:05:07 (金))

Excelにはない関数ですが、CalcにはSTYLEという関数があり、これならスタイルという形で、セルの書式を変更できました。

ユーザー定義関数のCELL_STYLEと組み合わせることで、最初のご質問である「セルの参照で背景も参照したい」も実現できそうです。以下、サンプルです。

faq5_445sample5.ods

一部、値のないセルでスタイルが参照できていませんが、

&T(STYLE(CELL_STYLE("入力";ROW(INDIRECT($AH7));COLUMN(INDIRECT($AH7))))

をIF文の外にだすと値のないセルでもスタイルが設定されると思います。また、CELL_STYLE関数が特別な形でセルを参照するので、式が長くなってしまっています。

ありがとうございます †

匿名 (2010-07-23 09:01:40 (金))

条件付き書式の場合登録できる件数に制限があるのでマクロの方をとりあえず使用してみたいと思います。

ただ動作がもっさりしているのと、表示シートのH1を変更した際に変更前の日にちの件数が多くて、変更後の件数が少ない場合に背景が残ったままになっています。（実際は印刷しないので関係ないですが。。）

あと間違えて入力していない日付を入力した際に『BASIC ランタイムエラー』となってしまうんですが、回避方法はないでしょうか？？

ついでになんですが、、、 †

匿名 (2010-07-23 09:18:29 (金))

実際は入力シートで並べ替えをしているんですが、
日付、本舗、分類、Batch #の順で並べたいんですが、条件が３つ以上の並べ替え方法を教えていただきたいです。
過去ログで見た気がするのですが、見つかりませんでしたのでどこに載っているかだけでもいいのでよろしくお願いします。

Re: ありがとうございます †

M.Kamataki (2010-07-23 10:40:20 (金))

＞ただ動作がもっさりしている

アドイン関数にするという方法もあるみたいです。こちらは動作速度が早い模様です。以下です。

http://oooug.jp/sns/?m=diary&a=page_detail&target_c_diary_id=578

＞『BASIC ランタイムエラー』となってしまう

適当に「ON ERROR GOTO」文を入れてください。faq/5/107「EXCELﾏｸﾛが、ｵｰﾌﾟﾝｵﾌｨｽで動かない？」の「エラー処理」やfaq/4/851「マクロ>プログラミング>文字列分解」の「ワークシートから使うには」をご覧ください。

Re: ついでになんですが、、、 †

M.Kamataki (2010-07-23 10:45:14 (金))

＞過去ログで見た気がする

「[openoffice:2265] Calcで4キー以上の並べ替えをするには？」
http://search.luky.org/oo/openoffice.2003/threads.html#02256

でしょうか。

Calcの標準機能だけでは、無理っぽいです。「並べ替え」はマクロの記録に取れるので、4つ目の条件はマクロ上で加えると良いのでは。良いサンプルがないので未検証です。

無題 †

ike@九州 (2010-07-23 13:18:14 (金))

実ファイルはデータ量多いだろうと思ってました

使えそうであれば、イベントリスナーの修正です
テキストファイル内マクロを module2 へ上書きしてドキュメントを再オ−プンして下さい。

module2.txt

ありがとうございます †

匿名 (2010-07-23 13:52:49 (金))

かなり処理が早くなりました。
要望にまで応えてもらえて非常に助かります。

理解してから使用させていただきます。
質問ですが、入力シートのI1,J1セルにH1セルで指定した日付の最初と最後の行数を求めているんですが（フォントを白にしています）、これを使って簡潔に書くことって出来るでしょうか？

ちょっと理解するのに時間が掛かりそうなもので。。

なんとか形になりました †

匿名 (2010-07-23 17:41:11 (金))

ike@九州さんのマクロをベースにして実シートに移行してみました。

たまに入力シートの同じ行で異なる背景が設定されていることがあるため、
再現性を高めようと１セルずつセットするようにしたら非常に時間が掛かるようになりました。。

サンプルではAからAG列までの範囲に１つのセルの背景を適用していますが、範囲で適用させることは出来ないのでしょうか？
試しにやったところ失敗しました。

無題 †

ike@九州 (2010-07-23 18:42:58 (金))

個々セルに背景色のみ適用では、セルの個数分必ず処理が掛かるので
データ量多いと処理時間は特に多くなります。

どうしても、個々のセル背景色を速く適用したい場合は
マクロの記録から

範囲選択コピー
貼り付け先頭セル選択
形式を選択して貼り付けを行い
（テキスト、数、書式）

選択範囲をマクロで生成するような手順になります。
（但しフォント書式、罫線等もコピーされます）

これ以外には現状ないようです

了解しました †

匿名 (2010-07-26 09:38:02 (月))

マクロのソース上では範囲選択をしてのセルの背景の適用は出来ないということですね。
個々の設定は非常に時間が掛かったので諦めます。

ありがとうございます。

お名前:
題名: