akiraaniの日記： エクセルでTRPGの高機能キャラクターシートを作るためのTips 3
INDIRECT関数＋VLOOKUP関数と、入力規則を使ったチャートの自動選択。
例１：クラスをプルダウンメニューで選択したら、基本能力値が表に自動的に適用されるようにしたい
まず、別シートにクラスの基本能力値表を作成し、名前を定義する
例えば、こんな表があったとする
__|____A_____|_B|_C|_D|_E|_F|_G|
01|_クラス名_|ST|DX|AG|IQ|HP|MP|
02|クレリック|_1|_1|_0|_1|20|15|
03|__シーフ__|_0|_2|_2|_0|15|10|
04|ファイター|_2|_1|_0|_0|25|10|
05|__メイジ__|_0|_0|_0|_3|10|25|
この表に対して二つの名前を定義しておく(やらなくてもいいが、やった方が後でわかりやすい)
範囲：A2:A5 名前：クラス名
範囲：A2:G5 名前：クラス能力値修正
リスト選択用のセルの入力規則を追加、リストによる入力規則で定義した名前「クラス名」を指定
あとは、選択したクラス名に応じて能力値の修正値をVLOOKUP関数で参照する。
例えば「=VLOOKUP(クラス能力値修正,[クラス名を選択したセル],1,false)」という式であれば、STの修正値が入力される。
例２：選択したクラスに応じて、クラスに応じて選択可能なスキルをプルダウンメニューで表示させたい
→クラスごとスキルのデータ範囲を定義するvlookup用テーブルを作る
|__F___|___G____|_____________H_______________|_____I_______|
2|要素名|データ列|_____データ数________________|___範囲______|
3|シーフ|____J___|=COUNTA(INDIRECT(G3&":"&G3)))|G3&"1:"&G3&H3|
4|メイジ|____K___|=COUNTA(INDIRECT(G4&":"&G4)))|G4&"1:"&G4&H4|
→それぞれのデータ列(上記例だとJ列がシーフ、K列がメイジ)のスキル一覧を記述しておく
|___J____|___K___
1|_鍵明け_|炎の矢
2|_罠解除_|氷の矢
3|__追跡__|岩の矢
4|背後攻撃|風の矢
5|__軽業__|雷の矢
6|________|光の矢
7|________|闇の矢
リスト選択用セル(下の例ではB3セル)→リストによる入力規則でF3:F4を指定
要素選択用セル→リストによる入力規則で=INDIRECT(VLOOKUP($B$3,$F$3:$I$5,4,false))を指定
INDIRECTよりはOFFSET (スコア:1)
・JとかKとか埋め込むのはちょっと気持ち悪いというか、そこも検索させた方が保守性がいいと思います。
3つめの表のは、1行目(J1/K1)に「シーフ」「メイジ」と入れといて
G3: =MATCH($F3, $J$1:$Z$1, 0)+9
G4: =MATCH($F4, $J$1:$Z$1, 0)+9
で、何列目から取ってくるかを選択
(MATCHの第3引数0は、ソート不要な完全一致検索オプション。
9足すのは、J列からの検索から列番号に変えるため。J列=10列目に対し、範囲の1列目で見つかった時はMATCHは1を返すので、9足してます。)
・OFFSETが使えるところは、INDIRECT よりは OFFSET を使った方がいいと思います。
H3: =COUNTA(OFFSET($A:$A, 0, $G3-1))
に。後述のR1C1形式などは1オリジンで、それに合わせて式を入れてるのに対し、OFFSETの引数は0オリジンなので、「$G3-1」と1引いてます。
・どうしてもINDIRECTを使うしか無い場合は、R1C1形式で。
「リストによる入力規則で=INDIRECT(VLOOKUP($B$3,$F$3:$I$5,4,false))を指定」の部分はOFFSETによる置き換えが無理で、INDIRECT を使うしかないと思いますが、
INDIRECTを使う時は、R1C1形式の方が動的生成しやすいです。
I3: ="R2C"&$G3&":R"&$H3&"C"&$G3
リストによる入力規則: =INDIRECT(VLOOKUP($B3,$F$3:$I$5,4,false),false)
INDIRECTの第2引数をfalseにすると、R1C1形式での指定になります。
といったとこですかね。
まあ、今の分量ぐらいなら全部INDIRECTでもいいと思いますが、
参照数が増えてくると、OFFSETに比べてINDIRECTはかなり重いです。再計算にかかる時間が目に見えて遅くなるぐらい…
ちょこちょこと (スコア:0)
まず、リンク先にもあるがCONCATENATE 関数は「&」で代用できる。
次にVLOOKUP関数では第四引数に「False」を指定すると検索範囲を昇順に並べておく必要はなくなる。
(第四引数が「True」または省略された場合は昇順に並べておく必要があり、その場合、検索値以下で一番大きい値を検索する。
「False」の場合は検索値と等しい値を検索する)
また、
>エクセル上で昇順ソートをかけたもの
はソートのオプションの「ふりがなを使わない」にチェックを入れた状態で並べ替える必要がある。
カタカナ・ひらがなの場合はいいが、漢字の場合に影響してくる。
Re:ちょこちょこと (スコア:1)
言われてみればその通りで……。
よくよく調べてみてCONCATENATE 関数のいらない子っぷりにちょっとなけてきた……。
というわけで、このへんざっくり修正しました。情報サンクスです。
しもべは投稿を求める →スッポン放送局がくいつく →バンブラの新作が発売される