「知っていると得するかもしれないExcelの癖」シリーズ第十回、テーマは「1行おきの行番号」になります。
Excel 2016バージョン1902を使用して確認しています。
限られたスペースに2行で1つのデータを表すような表があることと存じます。
その際にどのような数式にするのが良いのか?想定される下図の3ケースに応じて考えてみたいと思います。
①連番と空白行を交互に出力する
2行で1データになっていることが見た目で判るようにしたいケースです。
この場合は、連番+1を2で割ったあまりがゼロかをIF関数で判断し、ゼロなら連番を表示させれば実装することができます。
=IF(MOD(ROW()+1-ROW($A$1),2)=0,(ROW()+1-ROW($A$1))/2,"")
この例は1行目($A$1)が表の見出し行になっている場合です。実際の行見出しの位置に応じて変更します。
②すべての行に連番を出力する(同じ番号を2行づつ出力)
これは連番でフィルターを掛けた時に2行に渡るデータをすべて表示させたいというケースです。
行番号と空白行が交互に出力されていると、フィルターで行を選んだ時に行番号が附番されている行だけに絞られてしまいます。
これを実装するために数式は下記になります。(連番を出力する全行に同じ数式を設定します。)
=INT((ROW()+1-ROW($B$1))/2)
連番に1をプラスした値を2で割った値の小数点以下をINT関数で切り捨てることで実装することができます。
また2行目の数字を見えなくするには、条件付き書式で、この例では奇数行の場合にB列の文字色を白色に設定することで見えなくできます。
=ISODD(ROW(B2))
B2セルを選択して、条件付き書式に数式を使用して上記を入力し、適用先は「=$B$2:$B$7」のように設定します。
③連番をソートキーとしても使用する
ソートキーにするためには、連番がユニークになるようにする必要があります。
そこで②ではINT関数で切り上げていましたが、切り上げはしないで、計算値をそのまま使用します。数式は全行同じになります。
=(ROW()+1-ROW($C$1))/2
なお2行目の数値を見えなくする条件付き書式は下記になります。
=IF(INT(C2)=C2,FALSE,TRUE)
2行で1データを表す表にある項目でフィルターを設定するには
これも別段妙案がある訳ではございません。下手なやり方ですが、表のフォーマットをデザインする時に、どの項目でフィルターが必要になるか?を決めて、フィルター設定する項目の値を2行同じものを出力するのが良いと思います。
この時、2行目の出力セルには下記の数式を設定します。この数式を必要な行に数式貼り付けします。
=OFFSET(D3,-1,0)
なお上記はD3セルに設定する例になります。
まとめ
フィルターを設定したり、ソートができるようにするには表のデザインを決める段階で必要となる項目を埋め込んでおかないと、実装するのに労力が必要になります。
範囲を指定したソートであれば、そこに設定されている条件付き書式には影響を与えないと思いますが、必ず確認して見ることをお勧めいたします。
以上、最後までご一読いただきありがとうございました。
