スプレッドシートでVLOOKUPのスクリプト文

質問

スプレッドシートで1つ教えて下さい。

A列のプルダウンで選択すると別シートの単価表を参照し
B列からD列に単価が表示されるようにしました。
=VLOOKUP($A2,'単価シート'!$A$2:$D$10,2,False)という式になります。
しかし表示された単価を変更したい時に変更すると式までが消えてしまいます。
プルダウンから変更し直そうとすると式が消えてしまい参照されません。

例えば

単価シート
品番　　単価1 単価2 単価3
あ　　　　50 60 70
い　　　　80 90 100　

入力シート
A 　　 B C D
あ　　50 60 70

ここで単価を修正入力
A 　　 B C D
あ　　55 60 70

式を入力しないで、スクリプト文でできないでしょうか。
どのようなコードになるか分かる方おられましたら
よろしくお願いします。

回答No.8

kkkkkm · ベストアンサー

A10はどうなのかも見てもらえますか。
もしA10とA2がうまくいくということでしたら
if(myColumn==1 && myRow>=2 && myRow<=10){
を
if(myColumn==1 && myRow>1 && myRow<11){
に変更してみてください。

A10も駄目な場合
原因がスクリプトなのかトリガーなのかをを見極めるために、スクリプトを手動で実行してみてください。
スクリプトエディタを開いておき
シートに戻って
B3を適当な数値に変更したのちA3を選択して
スクリプトエディタで実行をしてみてください。
B3が変更されていたらスクリプトに問題はなく、トリガーに問題があると思われます。ただ、A2だと実行できるということなので…完全にトリガーが働いていないとも思えません。

トリガーの設定の画像を添付しておきます。

手動でもうまくいかない場合
if(myColumn==1 ){
//A列全ての行で実行されます。
や
if(myColumn==1 && myRow>1){
//A2以降の行で実行されます。
に変更して実行してみてください。

あと
以前気になって聞くのを忘れたのですが
> A1だけの処理はできるようになりました
これのA1は書き間違いですよね。

回答No.7

kkkkkm

あと、こちらも変更してみたらいいかもしれません。
(こちらではどちらでも同じ結果でしたが念のために)

strformula ="=VLOOKUP($A" + myRow + ",'単価シート'!$A$2:$D$10," + i + ",False)";

のところを

strformula ='=VLOOKUP($A' + myRow + ',\'単価シート\'!$A$2:$D$10,' + i + ',False)';

に変更してみてください。

お礼

回答ありがとうございます。

何度もChromeで試していますが
表示されません。

もう一度最初から作り直そうかと思っています。
何に原因があるのでしょうか？

心当たりがあれば教えて頂けないでしょうか。

回答No.6

kkkkkm

> A2からA10というコードを実行していますが
> A3以降セットされません。

A2がセットされたという事はトリガーもスクリプトも問題はないと思います。
セットされないときに「保存しています」の状態で止まっていることはないでしょうか。メニューのヘルプの横のところです。
Internet Explorer 11で利用した場合、そこで止まったままになって変更されないことがあります。
ChromeやEdgeはそのような事はなかったので環境のせいでおかしくなるのだと思っています。

ためしに、起動して最初にA3以降の変更を実行してみてください。

お礼

回答ありがとうございます。
何度も申し訳ありません。
試してみたいと思います。

回答No.5

kkkkkm

A2からA10までの例です。
myRow>=2 && myRow<=10
の2と10を適切な行番号に変更してください。
2行目以降全てなら&& myRow<=10は不要です。

function WriteVlookup() {
var sheet = SpreadsheetApp.getActive().getSheetByName('入力シート');
var myCell = sheet.getActiveCell();
var myRow=myCell.getRow();
var myColumn=myCell.getColumn();
if(myColumn==1 && myRow>=2 && myRow<=10){
for(var i = 2; i <= 4; i++) {
strformula ="=VLOOKUP($A" + myRow + ",'単価シート'!$A$2:$D$10," + i + ",False)";
sheet.getRange(myRow, i).setFormula(strformula);
}
}
};

お礼

kkkkkmさん、大変丁寧な回答ありがとうございました。

おかげ様でA1だけの処理はできるようになりました。
久々感動です。凄いですねトリガーっていうのは。
A2を変更したら再びVLOOKUPでデータを参照してきます。

A2からA10というコードを実行していますが
A3以降セットされません。

なにか手順があるのでしょうか？
申し訳ありません、今一度教えて頂けないでしょうか。

宜しくお願いします。

回答No.4

kkkkkm

トリガーについては以下のサイトを参考にして設定してください。
https://vba-gas.info/gas-trigger
イベントの種類は「変更時」です。

また、一部訂正してください。
&ではなく正式には&&ということなので
if(myCell.getColumn()==1 & myCell.getRow()==2){
↓
if(myCell.getColumn()==1 && myCell.getRow()==2){

お礼

回答ありがとうございます。
トリガーは確認します。
あとは行が何行もあるので
入力した行の単価をコントロールになります。
また何かヒントを頂けたら幸いです。

回答No.3

kkkkkm

回答No2のシート名が違ってました。

getSheetByName('シート1');
↓
getSheetByName('入力シート');

お礼

回答ありがとうございます。
今一度お聞きしたいのですが、
WriteVlookupをコーディングしたあと
実行するのですが、思い通りに実行しません。
ただコーディングするだけでは、ダメなのでしょうか？
スクリプトのトリガーで変更時にスクリプトを実行と
ありましたが、どのようにするとよいのか
もう少し教えて頂けないでしょうか。
宜しくお願いします。

回答No.2

kkkkkm

スクリプトのトリガーで変更時に下記のスクリプトを実行するようにしてください。A2の値が変更されるたびに入力シートのB2,C2,D2にVlookup関数を書き込みます。B2,C2,D2の単価を変更した後にA2を変更すると元の単価にもどります。

function WriteVlookup() {
var sheet = SpreadsheetApp.getActive().getSheetByName('シート1');
var myCell = sheet.getActiveCell();
if(myCell.getColumn()==1 & myCell.getRow()==2){
for(var i = 2; i <= 4; i++) {
strformula ="=VLOOKUP($A2,'単価シート'!$A$2:$D$10," + i + ",False)"
sheet.getRange(2, i).setFormula(strformula);
}
}
};

回答No.1

mt2015

単価を修正する時は単価シートの方を修正します。