Excel関数備忘録

項番の自動採番①

=MAX(OFFSET(INDIRECT(ADDRESS(1,COLUMN())),0,0,ROW()-1,1))+1

• 列内の通番で採番する
• 空白のセルを挟んでも通番で採番できる
• 1行目に使うとエラーになるためIFERRORなりを付けると良い

IFERRORあり

=IFERROR(MAX(OFFSET(INDIRECT(ADDRESS(1,COLUMN())),0,0,ROW()-1,1))+1,1)

項番の自動採番②

=IFERROR(INDIRECT("R[-1]C",FALSE)+1,1)

• 上のセルが空白の場合は1から採番する

複数の検索要素がある表からのデータ抽出

=VLOOKUP(子コード,OFFSET(INDIRECT(ADDRESS(MATCH(親コード1,※1,0)+ROW(※2),COLUMN(※2))),0,1,COUNTIF(※1,親コード),※3),※3,FALSE)

• 下の画像を例に説明
  • 子コード：検索する県コード（セル：C2）
  • 親コード：検索する地方コード（セル：B2）
  • ※1：親コードの検索対象列（セル：B5:B26）
  • ※2：親コードのヘッダーセル（セル：B4）
  • ※3：子コード（VLOOKUP）の検索対象幅（例の場合、VLOOKUPはC列D列が検索範囲のため2となる）

実際に使用した関数

=VLOOKUP(C2,OFFSET(INDIRECT(ADDRESS(MATCH(B2,B5:B26,0)+ROW(B4),COLUMN(B4))),0,1,COUNTIF(B5:B26,B2),2),2,FALSE)