[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『数式の埋め込み』（ミカン）
お世話になります。 標記の件で困っております。
データ入力シートとCSV作成用シート、データ参照用シートがあります。 データ入力シートにコードを入力し、CSV作成用シートでデータ参照用シートのデータを数式(VLOOKUP)で参照しております。 問題なのは、入力用シートの入力欄が可変ということです。 入力シートのレイアウトは以下の通りで図では2行目から77行目で1セットになり、 次の入力表を作成した場合は79行目～81行目までがヘッダとなり、82行目からまた入力可能な表となります。
A B C D E F G H I J K L M N
2 ｜ ヘッダ ｜ 3 ｜ ヘッダ ｜ 4 ｜ ヘッダ ｜ 5 6 7 ･ ･ 74 75 ｜ 入力不可 ｜ 76 ｜ 入力不可 ｜ 77 ｜ 入力不可 ｜ 78 空 白 79 ｜ ヘ ッ ダ ｜
上記の図では5行目から74行目のA～C、F～I、K列が入力可能で、75～77行目は入力不可の行です。 入力シートではこの入力欄を複数作成したり、削除することが出来ます。 CSV作成用シートに予め参照用の数式を複数作っておくということをしましたが 入力シートの入力欄を削除してしまうと数式が崩れてしまい、#REF!が出てしまいます。 VBAでの対応となると思いますがどうか宜しくお願い致します。
CSV作成用のシートのA1に =IF(INDIRECT(ADDRESS(ROW(A1),COLUMN(A1),,,"データ入力シート"))="","",INDIRECT(ADDRESS(ROW(A1),COLUMN(A1),,,"データ入力シート"))) って入力して右と下にフィルコピーしてみて。
データ入力用シートの方で削除したりしても、#REF!にならないよ。 (春日野馨)
=IF(INDIRECT("データ入力シート!RC",)="","",INDIRECT("データ入力シート!RC",))
(GobGob)
質問変わったんやね。
入力範囲が可変になるんなら「入力範囲外～入力範囲外」を 数式の参照セルにすればいいんでない？ （入力範囲外は削除しないんでしょ？ 4～75にすれば5～74が可変でも大丈夫しょ。）
例： × =VLOOKUP(A1,データ入力シート!$A$5:$B$74,2,FALSE) ･･･ 追加で不備。 ○ =VLOOKUP(A1,データ入力シート!$A$4:$B$75,2,FALSE) ･･･ 追加OK。
※10:00加筆、10:03勘違い修正ｗｗｗ
(GobGob)
(春日野馨)様、(GobGob)様、回答ありがとうございます。 自分で見直して不明瞭な部分があったため、加筆、修正致しました。 お手数をお掛けしてしまい申し訳ありません。
(ミカン)
(GobGob)様 回答ありがとうございます。 質問の仕方が下手で誠に申し訳ありません。 CSV作成用シートは全部で252列あり、その内42列に下記のような数式が入っております。 =IF(入力シート!C5="","",VLOOKUP(入力シート!C5,データ参照用シート,3,0)) VLOOKUPの検索値と列数とが変わります。 これをCSV作成用シートで増やしたり減らしたりしなければなりません。 後出しになってしまい申し訳ありません。 (ミカン)
あぁ、列やったんやね。
名前定義で「データ参照用シート」にINDIRECT関数つかった数式に設定すれば いいと思う。
A列～
名前：「データ参照用シート」 範囲 ： =INDIRECT("データ入力シート!A5"):D74
F～I列
範囲 = E5:J75
※またまた勘違い修正。 10:52
(GobGob)
データ参照用シートに範囲を設定するのでしょうか？
何度も何度も後出しで申し訳ありません。 =IF(入力シート!C5="","",VLOOKUP(入力シート!C5,データ参照用シート,3,0)) 上記の数式で変わる部分は「入力シート!C5」の「5」の部分と列数の「3」のところです。
=IF(入力シート!$C5="","",VLOOKUP(入力シート!$C5,データ参照用シート,3,0))
と書けば良かったですね…すみません
それと、今更ですが環境を書き加えます。 XP Proffesional Excel2002 です。
(ミカン)
根本的に勘違いしてたわ。
一度整理すると
・入力シートのA～C列、F～I列、K列が入力範囲。 ・入力範囲のセルはVLOOKUP関数の第一引数に使う（検索値） ・範囲は追加、削除が自由。
ってことで、次に疑問。
・追加された列の数式はどうすんの？ ・列固定の複合参照式ってことはA～B列どうすんの？ ・VLOOKUPの列数3が変動の意味がわからない。（コレは入力シート対象ではないもんね）
ってことで、各シートと各数式が見える表がほしいね。
(GobGob)
GobGob様、大変お手数お掛けします。
まず、入力シートですが __A__ __B__ __C__ __D__ __E__ __F__ __G__ __H__ __I__ __J__ __K__ __L__ __M__ __N__
1 空白行 2 販売店CD 3 販売店名 4 客先名称 商品名 コード 名称 販売本数 細目CD 売価 納入数 仕入数 マージン 補助 粗利 金額 金額 5 あああ いい 11111 AAAAAA ･ ･ 75 合計(税抜) =SUM(N5:N74) 76 消費税 =N75*1.05 77 合計(税込) =SUM(N75:N76)
2行目から77行目までで1セット。 次は79行目から同じ入力欄が作成可能となっています。
次に、データ参照用シートですが __A__ __B__ __C__ __D__ __E__ __F__ __G__ __H__ __I__ __J__ __K__ __L__ __M__ ･･･ __AM__
4 コード コード名称 細目1 細目2 細目3 ･･･ 細目32 5 細目CD 細目名称 細目CD 細目名称 細目CD 細目名称 6 11111 AAAAAA 1111 AAAA 6789 AAA 0123 AA 7 22222 BBBBBB 2222 BBBB 9012 BBB 4567 BB 8 33333 CCCCCC 3333 CCCC 3456 CCC 8901 CC 9 44444 DDDDDD 4444 DDDD 7890 DDD 2345 DD ･ ･ 105 99999 EEEEEE 9999 BBBB 6789 EEE 0123 EE
となっております。
また、CSV作成用シートですが __A__ __B__ __C__ __D__ __E__ __IR__ 1 項目1 項目2 項目3 項目4 項目5 …項目252 2 3 4 5 ここで、=IF(入力シート!$C5="","",VLOOKUP(入力シート!$C5,データ参照用シート,3,0))の数式をA3から入れていきます。 ただし、この数式は1例で、 =IF(入力シート!$C70="","",VLOOKUP(入力シート!$C70,データ参照用シート,15,0)) だったりすることがあります。
>・入力範囲のセルはVLOOKUP関数の第一引数に使う（検索値） に関してですが、引数に使うのはデータ入力シートのC列の値のみです。
また、3つの疑問についてですが >・追加された列の数式はどうすんの？ >・列固定の複合参照式ってことはA～B列どうすんの？ 入力用シートのC列に入力されたコードのみ用いますので、考えなくても良いです。
>・VLOOKUPの列数3が変動の意味がわからない。（コレは入力シート対象ではないもんね） 変動というのは、取得したい項目の列の位置が変わってくるからです。
これで分かりますでしょうか… お時間のある時で良いので目を通して頂けるだけで結構です。
(ミカン)
この表3つのうち、どこが削除されたり、追加されたりするの？
結局データ参照用シートで列追加、削除されると読んで。。。。
=INDEX(入力シート!5:5,MATCH(入力シート!$C5,データ参照用シート!$E$6:$E$105,0),COLUMN(A1)*2+5)
下、右へコピー。
※実際にEXCELで試してないのけど。こんなかんじかも。
(GobGob)
=INDEX(データ参照用シート!5:5,MATCH(入力シート!$C5,データ参照用シート!$E$6:$E$105,0),COLUMN(A1)*2+5)
の間違い。
(GobGob)
削除されたり、追加されたりするのは 入力用シートとCSV作成用シートです。 入力用シートの入力欄を増やすロジックは出来ているのですが CSV作成用シートの行を増減させるロジックが出来ていません。 言葉が足りずに申し訳ありません。
(ミカン)
入力シートは結局 C列しか数式に使わないんだから ココが残れば問題ないわけだよね？（コレ自体を削除しないでしょ？）
なんで、あの数式なんですけどね。
(GobGob)
返事が遅くなってしまい申し訳ありません。 結局仕様が変わり、対応しないことになりました… こんな私の為に尽力して頂きありがとうございました。
(ミカン)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.