「1つの行のなかの全てのセルが同じ値」である行を見つける。
この記事は自分用の覚書です。
■MacBook Pro macOS Monterey
バージョン 12.6.5
■Microsoft 365 Apps for businessサブスクリプション
(旧称 Office 365 Business)
Excel for Mac バージョン 16.72 (23040900)
T.N さん。
「1つの行のなかの全てのセルが同じ値」である行を見つけるやり方です。
■課題
アンケート結果（数字）の一覧がある。
各行を調べて、その行の全てのセルが同じ値である行を見つけたい。
結果としては、それらの行以外の行を一覧にしたい。
（全てのセルの値が異なっている行の一覧をつくる。）
ただし、「行の全てのセルが同じ値である行」は削除しないで残しておきたい。
■「行の全てのセルが同じ値である行」を見つける。
次図をやりたいということですよね。
上側の図の状態を作れれば、オートフィルタを使ってF列を「空白セル」で絞り込むことで目的の一覧になりますが、以下ではFILTER関数を使ってみました。
どこかのセルにFILTER関数の数式を入れておけば、オートフィルタを使うこともないので手順を少しでも省略できますから。
あとあとデータが増えていくことを考えて、元データ全体を「テーブル」にしておくことをお勧めします。
テーブルにしてあれば、行や列の追加があっても参照している数式を書き換える必要がありません。
いくつかやり方を思いつきましたが、ここではCOLUMN関数とCOUNTIF関数をIFS関数の引数に使うやり方を採りました。
数式の可読性を上げるために、セル内改行してあります。
（引数の頭はスペースで揃えています。）
テーブルの中を参照するやり方は「構造化参照」と呼ぶそうです。
見慣れない数式なので、ややこしく感じるかもしませんが、落ち着いて見ればわかると思います。
やっていることは従来のセル参照と同じですから。
COLUMN関数で「設問5」の列（E列）の列番号を取得しています。
E列のセルを参照しているので、どれも答えは「5」になります。
COUNTIF関数で、「設問1」から「設問5」（A列からE列）のセル範囲にある「設問1」の列（A列）の値の個数を取得しています。
全て同じ値なら答えは「5」個ですね。
IFS関数の引数「論理式1」は「COLUMN関数とCOUNTIF関数の答えが等しい」としています。
そして、それが成り立つ場合は、IFS関数は「同」という文字列を返します。
次に引数「論理式2」では、「COLUMN関数とCOUNTIF関数の答えが等しくない」としています。
こちらが成り立つ場合は、IFS関数は空白を返します。
なお、IFS関数は論理式とそれが成り立ったときに返す値の組み合わせを最大127個まで指定でき、最後として TRUE を論理式として指定すると、その前までのどの論理式も成り立たない場合に返す結果を指定できます。
これは、論理式とそれが成り立ったときに返す値の組み合わせが1つの場合でも同じです。なので、次のように数式を書いても上記と同じ結果になります。
ただ、「TRUE を指定するとはどういうことなのか」を知らない場合には、何をしているかの理解のしやすさが少しとはいえ落ちると感じたので、今回は TRUE を引数に指定するやり方は避けました。
■FILTER関数で絞り込む。
ここでは別シートにFILTER関数の数式を用意しました。
テーブルの「行の中で全ての値が同じかの判定」の列（F列）が空白の行だけになるように、FILTER関数で絞り込んでいます。
テーブルの見出しとデーターを参照している数式（構造化参照）は次のようです。
=a_テーブル1[[#見出し],[設問1]:[設問5]]
=FILTER(a_テーブル1[[設問1]:[設問5]],a_テーブル1[行の中で全ての値が同じかの判定]="")
テーブル名は「a_テーブル1」にしました。
テーブル名の頭にアルファベットを付けておくことで、数式のなかでそのアルファベットを打つだけでテーブル名を呼び出せます。
なお、 _ （アンダーバー）は、Windows版Excel対応として付けています。
テーブル名の頭にアルファベットを付けただけだと、Windows版Excelでは数式オートコンプリートの表示で、同じアルファベットで始まる関数名のなかにそのテーブル名が混ざって表示されてしまい、それらの中からさらにテーブル名を探さないといけなくなります。
（Excel for Mac では、アルファベットを付けておくだけでテーブル名と関数名は分けて表示され、テーブル名のほうが上に表示されます。）
テーブル名にアンダーバーも添えておくことで、Windows版Excelでは数式の入力の際に頭のアルファベットを打つと、数式オートコンプリートの表示のなかで関数名より上にテーブル名を表示できます。
■他のやり方について
今回扱うのは「数字」なので、標準偏差を使うやり方も思い付きました。
標準偏差が 0 なら全ての値は等しいということになります。
このやり方を採用しなかったのには、次の2つの理由があります。
- 「標準偏差が 0 なら全ての値は等しい」と知らなければ、何をやっているかを理解しづらい。
- 文字列が対象になった場合には使えない。
職場で作るファイルは誰のものでもなく皆のものです。誰かに引き継ぐかもしれませんし、他の人が流用するかもしれません。それを想定して「いつ誰が見ても理解しやすく作っておく」ことが大事です。
その理由で、うちの職場ならたぶん困る人は誰もいないだろうとは思いましたが、「"標準偏差が 0 なら全ての値は等しい" ことを知っていないと何をやっているのかわからない」状態にはしておかないほうがよいと判断しました。
また、COLUMN関数とCOUNTIF関数を使ったやり方なら、対象が「数字」でも「文字列」でも対応できます。汎用性がわずかでも高いので、そちらを採りました。
あるいは、AND関数とEXACT関数を使うやり方も思い付きました。
ただし、私ならそのやり方は採用しません。エクセルのヘルプに載っているように、EXACT関数は文字列を比較するための関数だからです。
ヘルプには「文字列を比較する」と書いてあるだけなので、引数に数字を指定するのは間違った使い方です。
それでも TRUE と FALSE を正しく判定してくれますが、それはいわばエクセルが気をつかって例外的に扱ってくれているからといえます。
でも間違った使い方なんです。
今後、マイクロソフトの方針というか事情というかで、いつ正しい答えが得られなくなってもおかしくないんです。そうなったら、それまで作ってきたファイルが全て無駄になります。
正直いって、そんなことが本当に起こるとは私も思っていません。
ですが、起こってもおかしくはないんです。
EXACT関数を使うやり方しか思いつけなかったならしかたがないでしょうが、他のやり方も思いついたのにわざわざEXACT関数を使うべき理由がありません。
「数字を正しく判定できなくなることが、いつ起こってもおかしくない」という潜在的な危険性を無くしておいたほうが、ビジネスではよいと私は判断します。その理由で、この件でEXACT関数を使うことは私はお勧めしません。
最近のコメント