世界一やさしいWebの教科書
WEBサービス・アプリ・ソフトウェア

Excel(スプレッドシート)で複数セル(範囲)が条件と一致するか判別する数式

今回説明するのは、Excel(Googleスプレッドシート)で、複数セル(範囲)に対する条件分岐を作る方法です。例えば、B2:F2に入力されている文字に対して条件を指定したい時のことです。

これがどのように使われるかというと、項目1~項目5があり、入力がすべて終わった作業を「OK」にして、まだ終わっていない作業を「未完了」にするときなどです。最近ではクラウドワークでGoogleスプレッドシートを使う人が多く、作業者の管理のために数式を使って作業状態を表すことが増えています。私もそのために、複数セルが条件と一致しているかどうかを判別する式を利用しています。

このページで使用している数式はすべてコチラ(https://docs.google.com/spreadsheets/d/1MABmGWZiZ0nzDuzdtHwuN8gak31sojGxOL2Rzp3PWrI/)から直接見ることができます。

範囲セル(セルの数)が小さければAND/ORを使う

項目1~5、つまりは5件くらいならばANDやORでつなげてしまうこともできます。

=IF(OR(B15=””,C15=””,D15=””,E15=””,F15=””),”未完了”,”OK”)

ANDやORを使うことで条件を複数重ねることができます。複数条件を指定するスタンダードな方法はこれですね。この場合、B8:F8の中で「””」(何も入力していない)セルが1つでもあれば、「未完了」(TRUE)。セルのすべてに何かしらが入力されていたら「OK」(FALSE)となります。

ちなみにこの場合、上の式と次の式は同じ結果を返します。

=IF(AND(B15=”〇”,C15=”〇”,D15=”〇”,E15=”〇”,F15=”〇”),”OK”,”未完了”)

ここで注意ですが、以下の条件文はエラーが起こります。

=IF(B20:F21=””,”未完了”,”OK”)

一見、処理してくれそうな数式ですが、B8:F8という風に配列を指定してしまうと、エラーが起きてしまいます。そのため、一つずつ比較していく必要があります。

COUNTIFを使う

COUNTIFを使った数式だと、ANDやORのように条件を羅列することなく複数のセルをまとめて条件にすることができます。

if(countif(B2:F2,”〇”)=5,”OK”,”未完了”)

IF文ではB2:F2という配列をしようすることができませんでしたが、COUNTIFという、条件にあった個数をカウントする数式であれば、使用できます。
B2:F2に条件「〇」に合うものが指定した数「5」である場合、「OK」という文字を入力します(TRUE)。条件に一致しない場合、「未完了」(FALSE)を入力します。この数式であれば入力する手間もあまりかかりませんし、項目が増えたときも簡単に対応できます。

もし条件に使いたいセルの数が多い時は、ANDやORを使うよりスマートな記述ができます。速度はあまり変わらないと思います。

COUNTBLANKを使う

COUNTIFと同じような使い方ですが、COUNTBLANKという、ブランク(空欄)の数を数える数式があります。

=if(countblank(B9:F9)<1,”OK”,”未完了”)

もし、全部のセルが埋まっているかどうかを確認するなら、COUNTBLANKで空欄の数を数えるというのも一つの手です。これなら式の意味も明白なので、誰が読んでもすぐに理解できます。

ArrayFormulaでANDを使ってみたけど

ArrayFormulaはExcelにはなく、Googleスプレッドシート特有の機能です。ArrayFormulaの数式を一つ入力するだけで、指定した範囲セルにテキストを入力してくれるといった軽量化・保守性(手入れのしやすさ)に優れたシステムです。

これを使った数式でできないかなと試行錯誤したのですが、ANDやCOUNTIFを使って複数セル(範囲セル)を対象とした条件を使用することはできませんでした。なぜか絶対参照になっちゃったりして、うまくいきません。

もし「こうやるんやで」って分かる人がいれば、教えていただけると嬉しいです。