エクセルで別のシートの項目を参照（ちょっと急ぎです）

質問

お願いします。

（例）
　A　　　B　　　C　　　D
　＊　ケーキ　　1個　500円
　　　　プリン　　2個　800円

と、このようにシート1にデータが並んでます。
A列に＊印を付けると、シート2のA～D列に1行分すべてが参照されるようにしたいのですが、可能でしょうか？

自分ではちょっと思いつきませんでした、ご存知の方いらしたら宜しくお願いします。

回答No.3

jindon · ベストアンサー

Sheet2のA1に

=IF(COUNTIF(Sheet1!$A$1:A100,"＊")>=ROW(A1),INDIRECT(ADDRESS(SMALL(IF(Sheet1!$A$1:$A$100="＊",ROW($A$1:$A$100)),ROW(A1)),COLUMN(A1),,,"sheet1")),"")

として
Ctrl + Shift + Enter で確定　(式の両端に　{ } が出る)

D列までコピー
A1:D1を必要なだけ下方コピー

お礼

有難うございます。
シート１とシート２には名前がついているので、置き換えればよいでしょうか。

恐れ入りますが、関数の解説（数式の意味）を教えて頂ければ助かります。

少しの時間家を出てしまいますので、この後の御礼は遅くなりますが、どうぞ宜しくお願いします。

回答No.5

Ryokucha

#2です。

同じシート１内なら、フィルタオプションの設定を使って＊を付けたものを別の範囲に出せるのですが、
これではダメですかね？
このフィルタオプションの設定は他のシートの参照は基本的には許していないみたいで、一度は出来るのですが
再度実行しようとすると、他のシートの連携名が消えてしまい、やるたびに設定をしなおさなくてはなりません。

他の手段としては、マクロでしょうね。
手作業でやれば出来る作業なので、別にコーディングなんて必要なくて、マクロの記録で手順を覚えさせるだけで出来ると思います。

ご参考にして下さい。

お礼

有難うございます。

フィルタオプションやマクロだと、私の求める動作とは少々違ってきてしまうのです。
やはり関数で解決したかったので、No3,4さんのを参考にさせて頂きます。

有難うございました。

回答No.4

jindon

>シート１とシート２には名前がついているので、置き換えればよいでしょうか。

Sheet1の部分を実際の参照するシート名に変更してください。

>恐れ入りますが、関数の解説（数式の意味）を教えて頂ければ助かります。

配列数式です。(webで検索してください)
各関数(countif, indirect, address, row, column, small)はHELPを参照してください。

これを全部説明しようとするとかなりなスペースになりますので、ご容赦願います。

数式に一部絶対参照が抜けたところがありましたので、下記に変えてください。

=IF(COUNTIF(Sheet1!$A$1:$A$100,"＊")>=ROW(A1),INDIRECT(ADDRESS(SMALL(IF(Sheet1!$A$1:$A$100="＊",ROW($A$1:$A$100)),ROW(A1)),COLUMN(A1),,,"sheet1")),"")

なお、範囲 $A$1:$A$100 を変更する必要がある場合は
すべて同一に変更してください。

お礼

たびたび有難うございます。

大変参考になりました。
これから意味を調べつつ試してみます。

有難うございました。

回答No.2

Ryokucha

>A列に＊印を付けると、シート2のA～D列に1行分すべてが参照されるようにしたい

どうしたいのか、いまいち理解出来ないのですが、
Aに*を付けたものだけの表を作りたいのでしょうか？

もし、そうなら*を付けて、オートフィルタでA列の*の付いているものを選択すれば出来るかと思います。

お礼

有難うございます。
言葉足らずですみません。

一度きりの表を作るのならそれでも良いのですが、テンプレートのように毎回使えるような物を作りたいと思ってます。
Ａ列に"*"を付けると自動的にシート２の方に反映されるようにしたいのです。
それもシート1の方で"*"はランダムに付けても、シート2の方では行間を開けずにデータが並ぶようにしていきたいのです。

また何か良い方法がありましたらお願いいたします。

回答No.1

bisuko1014

「シート2の『ケーキ』と表示させたいセル」に次の式を入力します。
=IF(Sheet1!A2="*",Sheet1!B2,"")
・・・これは「シート1のA2セルに*と入力されたらシート1のB2セルの内容を表示し、そうでなければ空白のままにする」という意味です。
同様にして「1個」と表示させたいセル、「500円」と表示させたいセルに式を入力していきます。（「どこを参照させるか」を意味するセル座標を変えればOKです。）
ご質問にあった「1行分全てが参照されるように」とは厳密には異なりますが（1個1個のセルに式を入力していくので）とりあえずご所望の結果は表示されると思います。
説明が分かりにくいようでしたら補足しますのでお知らせ下さい。

お礼

有難うございます。
ｉｆ関数も考えたのですが、やはり面倒なのと、表示させるセルが決まってしまうのとでちょっと違うかな・・・という感じで他の方法を考えているところです。
また何かありましたらお願いします。