[Excel]HYPERLINK関数のリンク先が行挿入された場合に対応する

HYPERLINK関数で直接指定する

HYPERLINK関数でリンク先のセルを直接指定するとリンク先が行挿入された場合にずれてしまう。

直接指定した場合。シート２の「A10」セルを指定

=HYPERLINK("#Sheet2!A10","直指定")

ジャンプしてみる。

最初のジャンプはOK

行挿入してしまうと

次のジャンプはリンクしたいところからズレてしまう。

CELL関数を駆使してみる

CELL関数でアドレス情報を取得し、そのアドレス情報をHYPERLINKのリンク先にする。

=CELL("address",Sheet2!A10)

リンク先であるシート2セルA10のアドレス情報を取得する。

=HYPERLINK(CELL("address",Sheet2!A10),"CELL関数で位置指定")

そのセルアドレス情報をリンク先として、HYPERLINK関数を作成する。

ジャンプする。

行挿入前のジャンプはOK

2行の行挿入をしてみる

CELL関数の参照セルを見ると、挿入後のセルA12に変わっている。

再びジャンプ。

HYPERLINK関数でリンク先にジャンプすると、行がズレずにジャンプできた。

[Excel]Row関数をナンバリングに使う

Row関数をNoの連番に使うことがある。

=ROW()-スタート行-1

ただこの場合だと、行挿入などで行位置が変わったときにNoがずれることがある

関数の使い方を変えてみる。

=ROW()-ROW(スタート行のひとつ上のセル)

上記例だと=ROW()-ROW(B$3)

こうすると、行挿入で位置が動いても、Noの数字がずれることはない。

注意

セルを指定するとき、行位置は絶対参照($)にする。

=ROW()-ROW(B$3)

絶対参照にしないと、コピー＆ペースト時に基点セルがずれ、ずっとNo1のままになってしまう。

[Excel]複数の項目に分岐した設定値（IFを使用しない）

「記号」列を入力すると、その内容に応じて「優先度」列の値が設定されるリストを作る

通常ならばIF関数をネストさせてやるが、これだと複雑。

IF( 記号 =" ◎ ","S",IF( 記号 =" ○ ","A",IF( 記号 =" △ ","B",IF( 記号 =" × ","C",""))))

CHOOSE関数とMATCH関数を使って、もっと簡潔にできる。

CHOOSE(MATCH( 記号 ,{" ◎ "," ○ "," △ "," × "},0),"S","A","B","C")

MATCH

範囲内で値を検索し、見t買った値が何番目に位置するのかを数値で返す。上記でいうと、記号列が「◎」なら「1」、「○」なら「2」といった具合。第2引数の「0」は「完全一致」という意味。

CHOOSE

第1引数のインデックスに応じて、第2引数以降の値を返す。上記でいうと、インデックスが「1」なら「S」、「2」なら「A」といった具合。

Excel表示例

=CHOOSE(MATCH(B:B,{"◎","○","△","×"},0),"S","A","B","C")

[Excel]開いているExcelファイルのディレクトリを取得する

Excelファイルのある場所
「D:\work\temp\text.xlsx」

①ファイル名を含むフルパスを取得する。

=CELL("filename")

⇒「D:\work\temp\text.xlsx」

②ファイル名のみを取得する

=TRIM(RIGHT(SUBSTITUTE(①のセル,"\",REPT(" ",100)),100))

⇒「text.xlsx」

解説

SUBSTITUTEは置換関数。REPTは繰り返し表示関数。

フルパスセルの「\」部分をまず、100個のブランクに変換する。

⇒ 「D:百個のブランクwork百個のブランクtemp百個のブランクtext.xls」

これを、右から100文字分取得している

⇒ 「xxx個のブランクtext.xslx」

最後にTRIMをかけてブランクを除去する

⇒ 「text.xlsx」

ファイル名が100文字以上あると正確に表示されなくなることに注意

③ディレクトリ名を取得する

=SUBSTITUTE( ①のセル,②のセル,)

⇒「D:\work\temp\」

①のフルパスセルのうち、②のファイル名のみを空に置換している ＝フルパスからファイル名を除去している。

[Excel]右列を検索し、左列を表示する

VLOOKUP だと、検索したい範囲は必ず左端の列にいる必要がある。

数式例

=VLOOKUP(E4,B3:C7,2,FALSE)

左端以外の列を検索したい場合は MATCH 関数と INDEX 関数を組み合わせて使用する。

数式例

=INDEX(B13:B16,MATCH(E13,C13:C16,0))

[Excel]複数条件のデータ抽出

＝DGET（データベース,フィールド,条件）

データベース：検索対象となる表を範囲指定する

フィールド：抽出したいフィールドのヘッダを指定する。あるいは直接ヘッダ名を入力する

条件：条件を記述したエリアを範囲指定する。ヘッダ部がそのまま検索条件となるヘッダを指す。

E