Q&A
11 CCCが「アクション_汎用」「アクション_専用」両方のテーブルにありますが、何か特別なルールがありますか？
テーブルとして設定している範囲があり（図のアクション部分）
これを元に、条件に応じて別のテーブルに値を切り出したいです。
図の例だと、アクションテーブルのうち「コード」列が100未満であるもの、10以上であるもの それぞれでテーブルを作りたいです。
最終的にやりたいこと
入力規則のリストで選択肢として表示させたいです。
大元データテーブル=>切り出したテーブル=>1列のみの名前付き範囲=>入力規則のリスト
といった具合です。
そのためテーブルという手段は取れなくとも名前である範囲の1列を構造化参照で見れる、もしくはリストの選択肢として取得できる仕組みであれば良いです
試したこと
FILTER関数を1行目に記載してみたところ、SPILLエラーとなりテーブルとして設定した範囲には使用できないようでした。
特別なルールというほどではありませんが…
同じコード値で複数のリストから参照させたかったためこうしています。
元の100未満と100以上に分けるルールに当てはまらないのでお尋ねしました。
100以上を10以上に訂正されたんですね。了解しました。
回答2件
0
ベストアンサー
私も今日存在を知ったところなので、まだ詳しくないのですが
power queryではどうでしょうか。名前が違ってたらすみません。。。
作り方ですが
元のデータを選択した状態で、挿入＞テーブル でテーブルを作って
データ＞テーブルから で新たなテーブルができます。
これでいけないでしょうか。
なお、これをマクロの記録をしながら実行したら
最後に作った新たなテーブルにSQLを投げる方法がわかります。
マクロの記録を参考に作ってみたVBAです。
VBA
1 Dim tLo As ListObject 2 Set tLo = ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ 3 "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""テーブル14"";Extended Properties=""""" _ 4 , Destination:=Range("$E$9")) 5 tLo.Name = "wwww" 6 tLo.QueryTable.CommandType = xlCmdSql 7 tLo.QueryTable.CommandText = Array("SELECT * FROM [テーブル14]") 8 tLo.QueryTable.Refresh BackgroundQuery:=False
これでテーブル:テーブル14をデータソースにしてE9の位置にSQLの結果が出力されます。
SQLを修正する処理を実行すれば、この出力は更新できます。
googleのスプレットシートのQuery関数みたいに使えそうな感じはしました。
ただここまでするならいらないかもですが
なお、私の環境2016での情報です。
投稿2021/03/22 20:23
編集2021/03/22 20:32
総合スコア1508
ありがとうございます。
ご提示いただいた通り、PowerQueryでテーブルを元にフィルタした結果に別の名前を付与することができました。
1点、もとのテーブルの内容を更新しても、明示的に「テーブルデザイン」タブ=>「すべて更新」を実行しないと今回作成したテーブルを参照するテーブルの内容が更新されないようでした。
ここを自動で更新するのは難しいですかね…
今は確認出来る環境にないので想像でお答えします
一応、シートの変更イベントに処理を記載するれば、更新処理を記載出来るとは思います
それ以外で自動更新する方法は、今の所知りません。
まぁ他にもっと良い方法があれば良いのですが
そうですね、ひとまずはWorksheetのchangeイベントで拾うことにしました。
今は元テーブルのデータの更新契機がコントロールできる範囲内なのでなんとかなりそうです！
助かりました、ありがとうございます!!
0
解決済みになっていますが、参考になればと思いあげてみます。
テーブルの構成は画像の通りです。
A:B列がアクション
D:E列がアクション_汎用（100未満）
G:H列がアクション_専用（10以上）
それぞれ数式をいれて、Ctrl+Shift+Enterを押下してください。
D2
1=IFERROR(INDEX(アクション,SMALL(IF((アクション[コード]<100)*(アクション[コード]>0),ROW(アクション[コード])-1),ROW()-1),1),"")
E2
1=IFERROR(INDEX(アクション,SMALL(IF((アクション[コード]<100)*(アクション[コード]>0),ROW(アクション[コード])-1),ROW()-1),2),"")
G2
1=IFERROR(INDEX(アクション,SMALL(IF(アクション[コード]>=10,ROW(アクション[コード])-1),ROW()-1),1),"")
H2
1=IFERROR(INDEX(アクション,SMALL(IF(アクション[コード]>=10,ROW(アクション[コード])-1),ROW()-1),2),"")
あらかじめ各テーブルで行を多めに数式を入力しておけば、
アクションテーブルにデータが追加されても自動的に反映されます。
※注意点
・名前を付ける際にはOFFSETやCOUNTを使うことが必要になります。（列にすると空白も含まれるため）
・行数が多くなると重いです。
投稿2021/03/23 01:49
総合スコア1925
別案ありがとうございます！
そうですね、予め領域を確保する方式を取るとすればご提示いただいた方式が良さそうです。
普通の関数参照で済むので変に複雑にならなそうです。 覚えておきます！
あなたの回答
tips
プレビュー
