列を左右逆さにして貼り付ける
左から右に1月から12月と並んでいる表を、12月から1月に並び替えて表を作り直したいときに役立つ数式を紹介します。
 |
| 送信者 OpenOffice |
２の表に入力されている数式 I1セル
=INDIRECT(ADDRESS(ROW($A1);COLUMNS($A$1:$G$1)-COLUMN(A1)+1))
この数式は、ADDRESS関数で特定したセルの番地の内容をINDIRECT関数で表示する構成です。
ADDRESS関数で、行の指定と列の指定をするための工夫がこの数式のポイントです。
=ADDRESS(行の指定;列の指定)
まず行の指定です。
I1セルには、1行目のG1セルの内容を表示させたいので$A1セルの行番号をROW関数で返すようにします。=ROW($A1)
A1でなく、G1でも構いません。一行目が参照できればそれでOKです。
次に列の指定です。
元の表は、A列から始まってG列で終わっています。この間の列の数を=COLUMNS($A$1:$G$1)で数えます。
全体の列の長さから、表の左端の列番号を引いて1を加えると参照したい列番号を得ることができます。
=COLUMNS($A$1:$G$1)-COLUMN(A$1)+1
I1セルは、変更後の表の中で左端に当たるので、A1セルの列番号をCOLUMN関数で参照して1という数字を返させています。
数式をそのままコピーして貼り付ければ、COLUMN関数で参照するセルが自動的にB1、C1、D1・・・・・・と移動します。
若干、参照する列を得る数式の根拠が分かりにくいかもしれないので手書きの図を添付します。
 |
| 送信者 OpenOffice |
この数式に使用されている関数
関数名：ADDRESS関数
書式：=ADDRESS(行番号;列番号;種類;A1;シート名)
何ができる：範囲の中で行番号と列番号が交差するセルのアドレスを文字列として返す
平たく書くと：=ADDRESS(3;1)で$A$3セルが返されます。
●ADDRESS関数で指定する種類と返される値：
種類を省略すると絶対参照・・・$A$3
種類を1に設定すると絶対参照・・・$A$3
種類を2に設定すると相対参照・・・A$3(行を固定）
種類を3に設定すると相対参照・・・$A3(列を固定）
種類を4に設定すると参照なし・・・A3
上の例では、省略して問題ありません。
●ADDRESS関数で指定するA1と返される値：
0を指定するとRC方式でのADDRESSが返されます。通常のA1方式でADDRESSを得たいときは省略して問題無しです。
●シート名：ADDRESS関数を入力しているsheetのほかのsheetを参照したい場合は、sheet名を入力します。上の例の場合は不要です。
関数名：INDIRECT関数
関数名：=INDIRECT(参照範囲)
何ができる：文字列として入力されたセル番号や範囲を計算式で利用できるようにする
平たく書くと：=INDIRECT(A3)でA3セルを参照範囲として計算式の中で利用できるようにします。
INDIRECT関数では、空白のセルを参照したとき0を返してしまいます。これを回避したいときの方法をこちらに書きました。ご覧ください。
関数名：COLUMN関数
書式：=COLUMN(セルのアドレス)
何ができる：セルの列番号を返す
平たく書くと：=COLUMN()がG3セルに入っていると。7が返される
ADDRESS関数とINDIRECT関数、COLUMN関数を連携させると色々便利になります。
表の大きさを変えた後、集計範囲を変更し忘れるミスもこの3つの関数が連携すれば防げます。詳しくは、こちらを。
