セルに入力があったら別のシートに自動で入力させたい

質問

似たような質問もあり自分でもいろいろ調べたのですが、
解決できなかったのでお願いします。

エクセルは2011で少し使える程度、あまり詳しくはありません。

シート１に「名前」「一回目」「二回目」という列があります。
そこの「一回目」の列に入力があった場合のみ別のシート２に自動で入力（抽出）されるようにしたいです。

例としてはこんなかんじです。↓
シート１
名前　　一回目　　二回目
佐藤　　１　　　　　　　　
山田　　　　　　　１　　　
鈴木　　　　　　　１
田中　　2　　　　　　　　　　

とあったら、「一回目」に入力があった行からシート２に

シート２
名前　　点数
佐藤　　50
田中　　100

こんなかんじでシート２には「名前」と「一回目」に入力された数値に50掛けたものが自動で表示されるようにしたいのですが、
このようなことは可能でしょうか？

説明がわかりにくいかも知れませんがよろしくお願いします。

回答No.4

tom04 · ベストアンサー

No.3です。

数式を

=IF(COUNT(Sheet1!B$41:B$60)<ROW(A1),"",INDEX(Sheet1!A$41:A$60,SMALL(IF(ISNUMBER(Sheet1!B$41:B$60),ROW(A$1:A$20)),ROW(A1))))
にしてみてください。（前回同様配列数式です）

数式の意味を説明すると

＞IF(COUNT(B$41:B$60)<ROW(A1),"",
はIF関数でB41～B60セル内に数値データがオートフィルで下へコピーした行数よりも少ない場合
その行は空白に！
という意味で、エラー処理です。
ROW(A1)部分は数式を入れたセルが「1」となり、オートフィルで下へコピーすると
2行目は ROW(A2)=2　ROW(A3)=3・・・と変化していきます。

＞INDEX(Sheet1!A$41:A$60,SMALL(IF(ISNUMBER(Sheet1!B$41:B$60),ROW(A$1:A$20)),ROW(A1))))
の部分の説明は
まず、INDEX関数で表示したいセルを範囲指定
数式ではB列の41行目から範囲指定していますので、41行目が範囲指定の「1行目」というコトになります。

＞IF(ISNUMBER(Sheet1!B$41:B$60),ROW(A$1:A$20)),　　「1」
は
Sheet1のB41～B60（行の絶対参照）で数値セルが「TRUE＝1」の場合のセルが範囲指定した何行目か？を導き出すための数式です。
＞ROW(A$1:A$20)部分は必ず1行目から、index関数で範囲指定した行数に合わせて指定してやります。
（列番号はA列でなくても構いません、あくまで行番号重視です）

範囲指定内で数値セル以外は「FALSE=0」なりますので、無視されます。
すなわち「TRUE」のセルにROW(A1)=1からの行番号を掛け算しています。
仮に、範囲指定した4行目・7行目に数値データがあると　4と7が返り
＞SMALL(「1」の数式,ROW(A1))　（2）
で　小さい順に表示！
というコトになります。
（2）の数式内の　ROW(A1)部分は最初に説明した通りで入力したセルが
ROW(A1)=「1」→　一番小さいもの
ROW(A2)=「2」→　二番目に小さいもの
といった具合のオートフィルです。
（この「ROW(A1)」もB1やC1でもどこの列でもOKです。単に入力するのにやりやすくしているだけです）

以上、長々と書きましたが
この程度で参考になりますかね？m（_ _）m

お礼

できました！
解りやすい解説ありがとうございます。

＞ROW(A$1:A$20)部分は必ず1行目から、index関数で範囲指定した行数に合わせて指定してやります。

この行数を合わせていなかったのが誤作動の要因だったようです。

今まで二度手間だった入力作業が一度で済むようになり、
大変快適になりました。
ほんとうにありがとうございます。

回答No.3

tom04

こんばんは！
＞エクセルは2011
とありますので、Macをお使いかと思います。
微妙なところが違うかもしれませんが、
当方使用のExcel2010の場合です。

↓の画像で右側がSheet2となります。

Sheet2のA2セルに
=IF(COUNT(Sheet1!B:B)<ROW(A1),"",INDEX(Sheet1!A$1:A$100,SMALL(IF(ISNUMBER(Sheet1!B$1:B$100),ROW(A$1:A$100)),ROW(A1))))
これは配列数式になってしまいますので、Shift＋Ctrl＋Enterで確定！
この画面からコピー＆ペーストする場合はA2セルに貼り付け後、数式バー内で一度クリック！
編集可能になりますので、Shift＋Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。

次にSheet2のB2セル（配列数式ではありません）に
=IF(A2="","",VLOOKUP(A2,Sheet1!A:B,2,0)*50)
という数式を入れ、A2・B2セルを範囲指定　→　B2セルにフィルハンドルで下へコピー！

これで画像のような感じになります。m(_ _)m

お礼

はい、Macのエクセルです。
これが自分のやりたかったことで、
教えていただいた数式でできました。
ありがとうございます。

応用というか行を飛ばしてこの数式を当てはめたかったのですが、
それがうまくいかなかったのでさらに質問させてください。

シート内にA4で印刷できるように区切りがあります。
その一ページのなかに今回の数式が20行、
全く別の数式などの項目が20行あります。
（別の部分の入力は反映しないようにしたいです）

次のページも同じ作りにしたい場合、
単純に今回教えていただいた数式を
=IF(COUNT(Sheet1!B41:B60<ROW(A1),"",INDEX(Sheet1!A$41:A$60,SMALL(IF(ISNUMBER(Sheet1!B$41:B$60),ROW(A$41:A$60)),ROW(A1))))
に変えればできるのかと思ってやってみたのですがうまくいきません。

そこで自分なりに数式の内容を調べてみたのですが、
ROW関数の部分がいまいちどのような意味というか作用をしてるのかがわからないので教えていただきたいです。
よろしくお願いします。

回答No.2

nemuneco

No: 1です

No: 1の後に
フィルタ機能を使い点数が50、100のもの
あるいは１以上、０より大きいなど
数値フィルタを使い抽出するのが、初心者には一番わかりやすい方法だと思います。

回答No.1

nemuneco

仮に
佐藤、一回目の「１」と入っているのがシート１のセルＢ２とします。

佐藤、一回目の「50」を表示したいセルをシート２のセルＢ２とします。

※この時、シート１のＡ列、シート２のＡ列(名前の入ってるセル)は同じデータが入っているのが条件です。

シート２のセルＢ２に「＝」を入れてシート１のセルＢ２をクリックすると
シート２のＢ２に

＝Sheet1!B2

と入力され、この後ろに「*50」を入力し

＝Sheet1!B2*50

とするとシート１のＢ２に入力されたものに５０をかけた数字が式の結果として
シート２のＢ２に表示されます。

あとはシート２のＢ２の式を下へコピーすれば大丈夫だと思います。

シート１に「１」と入っているものはシート２に「５０」
シート１に「２」と入っているものはシート２に「１００」
シート１に何も入力されてないものはシート２に「０」と表示されるはずです。

なのでシート２には

佐藤　　５０
山田　　　０
鈴木　　　０
田中　１００

と表示されます。

お礼

説明不足ですいません。
名前は固定されたものではなく、
その都度入力して一回目に入力がないものは非表示にしたいと思っています。