心あるエンジニアには嫌われていますが、
「上のセルと同じ値なら空白」という書き方をするExcelに遭遇することがあります。
あるいは、「空欄だったら上方向で一番近い値とみなす」Excelと言えばいいでしょうか。
具体的には、以下のようなExcelを、
| A | B | |
|---|---|---|
| 1 | 大分類 | 小分類 |
| 2 | 交通費 | 電車 |
| 3 | 交通費 | バス |
| 4 | 交通費 | タクシー |
| 5 | 広告宣伝費 | チラシ |
| 6 | 広告宣伝費 | 新聞広告 |
| 7 | 広告宣伝費 | インターネット広告 |
以下のように書く場合があります。
| A | B | |
|---|---|---|
| 1 | 大分類 | 小分類 |
| 2 | 交通費 | 電車 |
| 3 | バス | |
| 4 | タクシー | |
| 5 | 広告宣伝費 | チラシ |
| 6 | 新聞広告 | |
| 7 | インターネット広告 |
こういうタイプのExcelを扱う方法について書きます。
環境
以下の環境で確認しました
- Excel Online
- Excel 2016
「上のセルと同じ値なら空白」スタイルの列から値が全部埋まってる列を生成
以下の式でできます。(オートフィル使えます)
=LOOKUP(1,0/(A$2:A2<>""),A$2:A2)
-
A$2: 「上のセルと同じ値なら空白」スタイルの列の一番上のセル -
A2: 「上のセルと同じ値なら空白」スタイルの列の自分の行のセル
具体的には、以下のような式を書くと、
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 大分類 | 小分類 | ||
| 2 | 交通費 | 電車 | =LOOKUP(1,0/(A$2:A2<>""),A$2:A2) |
=LOOKUP(1,0/(A$2:A2<>""),A$2:A2)&" - "&B2 |
| 3 | バス | =LOOKUP(1,0/(A$2:A3<>""),A$2:A3) |
=LOOKUP(1,0/(A$2:A3<>""),A$2:A3)&" - "&B3 |
|
| 4 | タクシー | =LOOKUP(1,0/(A$2:A4<>""),A$2:A4) |
=LOOKUP(1,0/(A$2:A4<>""),A$2:A4)&" - "&B4 |
|
| 5 | 広告宣伝費 | チラシ | =LOOKUP(1,0/(A$2:A5<>""),A$2:A5) |
=LOOKUP(1,0/(A$2:A5<>""),A$2:A5)&" - "&B5 |
| 6 | 新聞広告 | =LOOKUP(1,0/(A$2:A6<>""),A$2:A6) |
=LOOKUP(1,0/(A$2:A6<>""),A$2:A6)&" - "&B6 |
|
| 7 | インターネット広告 | =LOOKUP(1,0/(A$2:A7<>""),A$2:A7) |
=LOOKUP(1,0/(A$2:A7<>""),A$2:A7)&" - "&B7 |
以下のように表示されます。
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 大分類 | 小分類 | ||
| 2 | 交通費 | 電車 | 交通費 | 交通費 - 電車 |
| 3 | バス | 交通費 | 交通費 - バス | |
| 4 | タクシー | 交通費 | 交通費 - タクシー | |
| 5 | 広告宣伝費 | チラシ | 広告宣伝費 | 広告宣伝費 - チラシ |
| 6 | 新聞広告 | 広告宣伝費 | 広告宣伝費 - 新聞広告 | |
| 7 | インターネット広告 | 広告宣伝費 | 広告宣伝費 - インターネット広告 |
逆に全部埋まってる列から「上のセルと同じ値なら空白」スタイルの列を生成
求められてる形式が「上のセルと同じ値なら空白」スタイルというだけなら、表示用の列を用意する方が簡単かもですね。
その場合は以下の式でできます。(オートフィル使えます)
=IF(A2<>A1,A2,"")
-
A2: 参照する値が入っている列の自分の行のセル -
A1: 「参照する値が入っている列の自分の行のセル」の一つ上のセル
具体的には、以下のような式を書くと、
| A | B | |
|---|---|---|
| 1 | 大分類(値) | 大分類(表示用) |
| 2 | 交通費 | =IF(A2<>A1,A2,"") |
| 3 | 交通費 | =IF(A3<>A2,A3,"") |
| 4 | 交通費 | =IF(A4<>A3,A4,"") |
| 5 | 広告宣伝費 | =IF(A5<>A4,A5,"") |
| 6 | 広告宣伝費 | =IF(A6<>A5,A6,"") |
| 7 | 広告宣伝費 | =IF(A7<>A6,A7,"") |
以下のように表示されます。
| A | B | |
|---|---|---|
| 1 | 大分類(値) | 大分類(表示用) |
| 2 | 交通費 | 交通費 |
| 3 | 交通費 | |
| 4 | 交通費 | |
| 5 | 広告宣伝費 | 広告宣伝費 |
| 6 | 広告宣伝費 | |
| 7 | 広告宣伝費 |
発想を転換して、条件付き書式で見た目だけ変える
条件付き書式で見た目だけ「上のセルと同じ値なら空白」スタイルにするという手もあります。
ただしこちらの方法は残念ながらExcel Online(無料版)では使えないようです。
リボンを
条件付き書式 > 新しいルール > 数式を使用して、書式設定するセルを決定
と辿っていき、条件として以下の式を指定します。
=IF(A2=A1,1)
-
A2: 参照する値が入っている列の自分の行のセル -
A1: 「参照する値が入っている列の自分の行のセル」の一つ上のセル