質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
Google スプレッドシート

Google スプレッドシートは、フリーで利用できる表計算ソフト。Webアプリのためインターネットに接続することで利用できます。チャートやグラフの作成のほか、シートを他のユーザーと共有したり、同時に作業を進めることも可能です。

Q&A

解決済

2回答

672閲覧

スプレッドシートの条件付き書式(カスタム数式)を参照元をずらして反映させたい

UY4_kxOmC

総合スコア1

Google スプレッドシート

Google スプレッドシートは、フリーで利用できる表計算ソフト。Webアプリのためインターネットに接続することで利用できます。チャートやグラフの作成のほか、シートを他のユーザーと共有したり、同時に作業を進めることも可能です。

0グッド

0クリップ

投稿2022/07/14 10:22

知りたいこと

特定の範囲に設定した条件付き書式(カスタム数式)をそのまま別の行に反映させることは可能でしょうか。
(数式内に相対参照あり)
方法があればご教示ください。
そもそも不可能な内容の質問であればご容赦ください。

実現したいこと

添付画像のように、B2:F6の範囲で2行目が「完了」であれば、完了が入っている同一列の2行目〜6行目に色を付ける、という条件付き書式をそのままスライドさせて、B7:F11の範囲に7行目が完了だったら7行目〜11行目に色を付けるというように反映させたい。

試したこと

B7:F11の範囲にindirectを用いて行数を可変できるよう「countif(indirect("B$"&row(B2)+5),"完了")」と入れてみましたが、条件付き書式のカスタム数式では「$」が生きないのか、「countif(B$7,"完了")」といれた時のような動きにはなりませんでした。

イメージ説明

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答2

0

ベストアンサー

条件付き書式は、書式を適用するセルを起点としてロジックを組む必要があります。
なので、

引用テキストB2:F6の範囲で2行目が「完了」であれば、完了が入っている同一列の2行目〜6行目に色を付ける

という視点を書式を適用するセルからみたロジックに変換してあげて、

自分(対象セル)を含む上方向 6セル内に"完了"がある場合に色を付ける

という条件でカスタム数式を作ればよいです。そうするとOFFSETでいけそうだなってなるかと。

ただ、OFFSETで 行方向に -5する場合、当然ですが5行目以下の上の方のセルに対してはエラーになるので、そこを切り分ける必要があります。

範囲を B2:F
カスタム数式を
=IF(ROW(B2)<6,COUNTIF(B$2:B2,"完了")>0,COUNTIF(OFFSET(B2,-5,0,6,1),"完了")>0)

投稿2022/07/14 13:07

sawa

総合スコア3002

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

UY4_kxOmC

2022/07/14 14:07

ありがとうございます! イメージ通りの結果になりました。offset関数が苦手なのでとても勉強になりました。
guest

0

列に対して設定するのであれば式は
B$2="完了"になるように思いますが。

投稿2022/07/14 11:11

usekay

総合スコア395

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

UY4_kxOmC

2022/07/14 11:17

ご助言ありがとうございます。 そのパターンでカスタム数式を入れた場合でも最初の2行目〜6行目までは反映しますが、主旨であるその条件を今度は7行目〜11行目に反映させることはできないようでした。
usekay

2022/07/14 13:44

offset(D3,-OFFSET(D3,0,if(COLUMN()<4,column($A3)-COLUMN(D3),Column($D2)-column())),if(4>COLUMN(),COLUMN($B1)-COLUMN(),COLUMN($D1)-COLUMN()))="完了" とすれば表の一番左の列に項目番号が入っている限りは確実に判定できますし表の縦幅がバラバラでも変わっても適切に処理できますね。 3~6行目に完了と入ってもこの場合誤検出されません。(はいるかどうかはわかりませんが) オフセットの基準列を変えるだけで右の方にも適用できますし。
usekay

2022/07/14 13:47

まあ結局はどこが変わってどこが変わらないか次第ですかね。
UY4_kxOmC

2022/07/14 14:39

追記ありがとうございます。 普段使わない数式で慣れていないためか、うまく反映できませんでしたが、こちらも参考にしてうまく反映できるように勉強して参りたいと思います!
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問