Excel で調べこと色々¶
入力データの指定¶
データが増減しても入力データを自動で指定。
=OFFSET($E$2,0,0,COUNTA(E:E)-1)
$E$2: 開始セル。COUNTA(E:E)-1: 入力データの行数。開始セルの行にあるデータとしない値の入ったセルの数（ここではヘッダだけを想定して 1）を引く
範囲から指定した行、列の値を取得¶
=INDEX(A1:D5, 3, 2)
A1:D5: 範囲3: 行2: 列
セルに入力した値を数式に使う¶
C:\path\to\fileFolder\filename.xlsx の sheetName シートの A:B 行から A 列が hoge になる B 列の値を取得。
A1:C:\path\to\fileFolder\A2:filename.xlsxA3:sheetNameA4:="'"&A$1&"["&$A$2&"]"&$A$4&"'!A:B"
=VLOOKUP("hoge", INDIRECT($A$4), 2, FALSE)
条件に合致した値を空白なしで取得¶
$A$47:$C$55 から C 列が 1 になる B 列の値を47行目のセルから空白なく下方向に並べる。
{=IFERROR(INDEX($A$47:$C$55,SMALL(IF($C$47:$C$55=1,ROW($C$47:$C$55)),ROW()-ROW($A$47)+1)-ROW($A$47)+1,2),"")}
配列数式を使うので、
{}の中の数式を入力した後 CTRL+SHIFT+ENTER で確定セルを下方向にコピペ
SMALL(IF($C$47:$C$55=1,ROW($C$47:$C$55)),ROW()-ROW($C$47)+1): C 列が 1 の行を求めて順番に取得している
条件に合致する複数の行から最大の値を取得する¶
以下のような表から 1 列目が a01 になる行のうち 2 列目の値が最大となる 3列目の値（ 20 ）を取得したい。
a01 |
000000 |
100 |
a02 |
190425 |
200 |
a01 |
190401 |
50 |
a03 |
190426 |
150 |
a01 |
190421 |
25 |
表の座標は
A1:C5とするD1 - D3:a01 - a03E1:{=MAX(IF($A$1:$A$5=D1,VALUE($B$1:$B$5),0))}1 列目がa01になる位置の値以外は 0 になる配列を取得して、その最大値（190421）を求めるF1:=D1&TEXT(E1,"000000")比較用の文字列（a01190421）生成G1:{=MAX(IF(A1:A5&B1:B5=F1,C1:C5,0))}1 列目と 2 列目の結合した文字列がa01190421と同一の位置の値は 3 列目の値、それ以外は 0 の配列を取得して、その最大値を取得することで25が取得できるもっとスマートにできると思う