[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『関数について』（覚えたて）
次のような結果が出るように関数を使いたいのですがどのようにすればよいでしょうか。
Ａ列にＮｏ、Ｂ列に氏名、Ｃ列に住所が入力されている一覧があります。 Ａ列のＮｏは連番ですが複数個存在します。
＜例＞ Ａ Ｂ Ｃ １ 山田 東京 １ 岡田 千葉 ２ 山本 名古屋 ２ 佐藤 岐阜 ２ 鈴木 三重
この場合、Ａ列のＮｏが“１”であれば、“１”のＡＢＣ列すべてがシート２に コピーされ、Ａ列のＮｏが“２”であれば、“２”のＡＢＣ列すべてがシート３ に表示されるようにするにはどうすればよいでしょうか。
例としているシートが「シート１」として Ｄ列に作業列を作成 =A1&"-"&COUNTIF(A1:$A$1,A1)
シート１のＤ列をキーに抽出
シート２
=IF(COUNTIF(シート1!$D:$D,"1-*")<ROW(),"",INDEX(シート1!$A$1:$C$5,MATCH("1-"&ROW(),シート1!$D$1:$D$5,0),COLUMN()))
シート３は、「1-」を「2-」と置き換えてください
(Ohagi)
COLUMN() → 列番号ですので、この部分を変えてあげればいいと思います
ＡＢＣ → １２３(列番号) 参照元と同じ列番号なので 「COLUMN()」でＯＫ ＡＣＢ → １３２(列番号)
(Ohagi)
ありがとうございます。 不具合があるのですがどうすればよいでしょうか。 最初にお尋ねした方法でシート２へ表示できたと思ったのですが 最後の一行が表示漏れとなります。シート３でも同じように最後 の一行が表示漏れとなります。どのように変えればすべて表示さ れますか。また、氏名のところがブランクの場合「０」が表示さ れます。ブランクにするにはどうすればよいですか。
>最後の一行が表示漏れとなります 「$A$1:$C$5」と1行目～5行目までの式になっていますが こちらは、変更されましたか？
>氏名のところがブランクの場合「０」が表示 式で対応する以外なら、条件付書式で「0」を白文字にして見えなくする ツール→オプション→表示→ウィンドウオプション「ゼロ値」のチェックを外す
式が長くなるより、「条件付書式」でやった方がシンプルな気がします… （e2qw）条件付き書式 http://www.excel.studio-kazu.jp/lib/e2qw/e2qw.html
(Ohagi)
>氏名のところがブランクの場合「０」が表示 については条件付書式で対応できました。
>最後の一行が表示漏れとなります の方ですが、変更はしていませんのでこの例で作成すればすべて表示されます。 しかし、実際に作業するデータはＡ列が同一番号のものは、それぞれ３０前後あ り、ご教示いただいた関数式の一部を修正しています。ただ、列番号や行番号の範囲が 違うだけなので、その部分のみ修正していますから誤表示となるとは思えないのですが。 ちなみに、１行目に題目があるため２行目からの表示としています。
>変更はしていません 範囲を変更されていないのですか？ 変更しなければ、当然対応されません
=A1&"-"&COUNTIF(A1:$A$1,A1) として、Ａ列の数字と個数を連番で表示させています 一行目がタイトルであれば =A2&"-"&COUNTIF(A2:$A$2,A2) から始まりますよね？
どのような修正をしたのでしょうか？ サンプルで対応できていれば、あっていると思うのですが…
(Ohagi)
言われるとおり =A2&"-"&COUNTIF(A2:$A$2,A2)から始めています。 ちなみにシート２には以下のように修正作成しています。
=IF(COUNTIF(Sheet1!$N:$N,"19-*")<ROW(),"",INDEX(Sheet1!$A$2:$K$1806,MATCH("19-"&ROW(),Sheet1!$N$3:$N$1806,0),COLUMN()))
シート１のＮ列にキーを作成し、Ａ列が１９と表示されている行すべてをシート２に表示するようにしました。
実際のデータは、列：ＡからＫ、行：１行目はタイトルで２行目から１８０６行目までです。
なお、MATCH("19-"&ROW(),Sheet1!$N$3:$N$1806,0)のところは$N$2にすると２行目が表示されなくなります。
>INDEX(Sheet1!$A$2:$K$1806,… の部分は、INDEX(Sheet1!$A$1:$K$1806, ではないでしょうか？
ROWやCOLUMNは、行番号と列番号です それを利用して抽出する場合、対象の表内の「1行目」と実際の行番号の「1行目」が 同じになるようにならないとダメですよね？ でなければ、「ROW()」を「ROW()-1」などとしてあわせてあげる必要があると思います
(Ohagi)
範囲を確認されましたか？ A3のセルでは、３の値が「ROW()」で、返されますが INDEXの「範囲」で「A3～A15」となっていれば、「１」となるようにして あげなければなりません また、その「範囲」に「タイトル行」を含めるか含めないかでも 指定する「行番号」が変ってきますよね？
(Ohagi)
小さなサンプルを作成し 各データ（数式の戻り値まで表示されているもの） とそれぞれのセルに入力している数式を ご提示して頂くのが良いかもしれませんよ。
式の主要部分の仕組みを理解して頂くのが先決かと。
また、コメント記入の際は、その都度 ご署名をお願いしますね。
(HANA)
元となるシート１には１行目にタイトル行があり、表示先のシート２にも１行目にタイトル行があります。 したがって、範囲をご指摘どおり修正したためシート１の２行目がシート２の２行目に表示されています。 ただし、Ａ列に「１９」の表示があるにも関わらず最終行のみ表示されません。念のためシート３に次の Ａ列に「２０」の表示があるものとして関数を作成しましたがやはり最終行が非表示となります。
ちなみに、
シート１のセル「Ｎ２」には
=A2&"-"&COUNTIF(A2:$A$2,A2)
シート２の各セルには
=IF(COUNTIF(Sheet1!$N:$N,"19-*")<ROW(),"",INDEX(Sheet1!
$A$1:$K$1806,MATCH("19-"&ROW(),Sheet1!$N$2:$N$1806,0),
COLUMN()))
（覚えたて）
と入力しました。
>Sheet1!$N$2:$N$1806 Sheet1!$N$1:$N$1806 ではないでしょうか？
(Ohagi)
（覚えたて）
ご本人さんと衝突してしまいましたがそのまま載せます。 たぶん以下の推測であっていると思いますが・・・。 ・・・いろいろ不可解なのですよね・・・。 以下、衝突前の文章です。
この式は、いろいろ問題のある式だと思いますが・・・ 私の想像力の至らないせいですかね？
まず、Ohagiさんご指摘の部分に関して この様なサンプルで試してみましょう。 数式内のMATCH関数の参照先と INDEX関数の参照先のずれに関してです。
このサンプルは、B2セルと同じ値が 範囲の何番目にあるかMATCH関数で特定し INDEX関数でその値を取り出す事を目的とします。 B4セルに =INDEX($A$1:$A$5,MATCH(B2,$A$2:$A$5,0)) と言う数式を入れると「ロ」と言う結果が返ります。 [A] [B] [1] 見出し 検索値 [2] イ ハ [3] ロ 結果 [4] ハ ロ [5] ニ 「ハ」を検索したのに、その一行上の「ロ」が 返されるのは、「ハ」をA2:A5から検索し「3」 と言う数字を求め、A1:A5の上から3番目の値を 取り出しているためです。
この２カ所の参照先が同じ行から始まらなくては ならない必要が分かりますか？
私が不思議に思うもう一点ですが、例えば 数式を入れる一番最初の行が2行目だった場合 "19-"&ROW() は 19-2 と言う値を返します。 これではどう頑張ってもA列が19となっている 一番最初のデータは持ってこれないと思うのですが その点は、上手く表示されているのですか？
・・・・両方が問題ですね。（笑） ２回目に出てくる行番号を求め、その一つ上を引っ張って来ているので １回目から表示できるが、一番最後が表示されない。
ROW()に関しては、ROW(A1)に変更してみて下さい。 参照先は、Ohagiさんのご指摘の通りに変更です。
＞２行目と３行目に同一の内容で表示されます。 これはよく分かりませんが、取り敢えず上記試してみて下さい。 駄目なら確認していただきたいことがありますので その様に仰って下さい。
(HANA)
HANAさんに先を越されましたね(^_^;) >"19-"&ROW() は 19-2 と言う値を返します。 これにさっき気づいてカキコミしようとしたのですが…
>表示先シートの２行目と３行目に同一の内容 となるのがとおっしゃられていますので… 私も良く分からず、躊躇しておりました
出来たら、「表内の1行目なのか」「シート上の1行目なのか」 あたりをハッキリさせたほうが、見通しが付く様な…
>ちなみに元となるシート１のセルＮ１は空白でセルＮ２から入力しています って事は、データはシート上の３行目(Ｎ３)からですよね？ INDEX(Sheet1!$A$2:$K$1806 (＠_＠;)？
(Ohagi)
INDEX(Sheet1!$A$1:$K$1806,MATCH("19-"&ROW(),Sheet1!$N$1:$N$1806,0)としますと
表示したい行の１行目が表示されません。
（表示したい行の総数が１０行の場合、２行目から１０行目まで表示）
INDEX(Sheet1!$A$1:$K$1806,MATCH("19-"&ROW(),Sheet1!$N$2:$N$1806,0)としますと
表示したい行の最後の行が表示されません。
（表示したい行の総数が１０行の場合、１行目から９行目まで表示）
ちなみに "19-"&ROW(A1)とした場合は表示したい行の最後の行が表示されません。 （表示したい行の総数が１０行の場合、１行目から９行目まで表示）
シート１にもシート２にも１行目にはタイトルがあり、実質２行目以降が対象となって います。 キーとする関数はシート１の２行目より１８０６行目まで入力しています。
なんども申し訳ありません。
（覚えたて）
>表示したい行の１行目が表示されません。 検索する為の連番が「19-2」からになるためです 「ROW()-1」として「19-1」から始まるようにしてあげなくてはなりません
>表示したい行の最後の行が表示されません。 「Sheet1!$A$1:$K$1806」「Sheet1!$N$2:$N$1806」 開始行が「１行目」と「２行目」になるのはおかしいです
[A] [B] [1] 項目1 項目2 [2] あああ 19-2 ←="19-"&ROW() [3] いいい 19-3
[A] [B] [1] 項目1 項目2 [2] あああ 19-1 ←="19-"&ROW()-1 [3] いいい 19-2
(範囲に項目行を含む) (含まない)
[A] [B] [1] 項目1 項目2 １行目 [2] あああ 19-2 ２行目 １行目 [3] いいい 19-3 ３行目 ２行目
(Ohagi)
（覚えたて）
COUNTIF(シート1!$D:$D,"1-*")<ROW() → COUNTIF(シート1!$D:$D,"1-*")<ROW()+1 としたら、どうなりますか？
(Ohagi)
(Ohagi)さん、(HANA)さん
大変お世話になり、ありがとうございました。
今後ともよろしくお願いします。
もう見て居られないかもしれませんが 私は上で ＞この式は、いろいろ問題のある式だと思いますが・・・ と書きました。 それは、修正なさって上手く表示されている 現在の式も同様です。
ROW関数の戻り値に「1」を得たいはずが その数式を入力した場所によっては 希望する値を得られないためです。 現在２行目に入力するので「-1」の調整で良いですが 今後、何かの拍子に３行目から表示したくなったとき また式の訂正が必要です。
先の書き込みで
＞ROW()に関しては、ROW(A1)に変更してみて下さい。
＞参照先は、Ohagiさんのご指摘の通りに変更です。
この様に書きました。
これは、この式を
＞=IF(COUNTIF(Sheet1!$N:$N,"19-*")<ROW(),"",INDEX(Sheet1!
$A$1:$K$1806,MATCH("19-"&ROW(),Sheet1!$N$2:$N$1806,0),
COLUMN())) ↓~~~
=IF(COUNTIF(Sheet1!$N:$N,"19-*")<ROW(A1),"",INDEX(Sheet1!
$A$1:$K$1806,MATCH("19-"&ROW(A1),Sheet1!$N$1:$N$1806,0),
COLUMN())) ↑ ~~~
この様に変更してやってみて下さい。
と言う事です。ROW関数は２カ所で使われていますから。
ROW関数の中にA1を入れることにより、この式をどの行に入力しても 一番最初の「19-1」を検索出来るようになります。 現在の式では、入力する行が特定されてしまいますので 他への流用を考えたとき、入力行が２行目でない場合は 式を変更する必要があります。
その辺りは、ご理解いただけてますかね？
(HANA)
（覚えたて）
（覚えたて）
B2に入れた式は、上で私が載せたのと同じ式ですか？ それとも、どこか変更しましたか？
また ＞どのセルも正しく表示されなくなる そのセルには、何が表示されているのですか？
もう一度、 元表のタイトルが入っているセル番地 作業列に使用した列 作業列に入れてある数式 表示用のB2に入れた式 あわせて教えてください。
(HANA)
作業列は Ｎ
数式は =A2&"-"&COUNTIF(A2:$A$2,A2)
表示側のＢ２セルには
=IF(COUNTIF(Sheet1!$N:$N,"19-*")<ROW(A1),"",INDEX(Sheet1! $A$1:$K$1806,MATCH("19-"&ROW(A1),Sheet1!$N$1:$N$1806,0),COLUMN()))
ただし、これではＢ２セルに元表のＢ２がそのまま表示されるので COLUMN() を COLUMN(A1)としましたらＢ２には元表のＡ１が正しく表示されます。
しかし、Ｃ２、Ｄ２もすべてＢ２と同じ元表のＡ１が表示されます。
したがって、これまでと違っている点は表示側の起点となるセルがＡ２からＢ２に変わった 点のみです。
B2セルに作成したその式を、横にコピーしましたか？
確認してもらいたい事が２点あります。
一つは、現在C2に入って居る式の COLUMN関数の参照先が B列になって居るかどうか。
もう一つは、計算方法が 手動 になっていないか。
です。 計算方法は F9 を押して、正しい答えになるかで 確認できます。
(HANA)
↑は携帯からだったので 確認事項だけ書きましたが INDEX関数について少しだけ補足しておきます。
今回の式で使っているINDEX関数は INDEX(配列,行番号,列番号) このような書式になっています。
配列 は、元表の範囲「$A$1:$K$1806」
行番号は、MATCH関数で見つかった行「MATCH("19-"&ROW(A1),Sheet1!$N$1:$N$1806,0)」
列番号に、COLUMN関数「COLUMN(A1)」
この「列番号」とは、元表の行番号で指定された行の 何列目を表示するか を指定するものです。
1列目(A列)が表示したければ「1」となる、COLUMN(A1) 2列目(B列)が表示したければ「2」となる、COLUMN(B1) 3列目(C列)が表示したければ「3」となる、COLUMN(C1) を指定する必要があります。
「常に１列目を表示している」と言うお話ですので このCOLUMN関数の参照先が 全てA1になっているのではないか と言う思いから、 COLUMN関数の参照先がどの様になっているのか確認して下さい と言う発言です。
また、計算方法が「手動」になっていた場合 数式をフィルドラッグしただけでは計算が行われず コピーしたセルと同じ物が表示されます。
「B2と同じ結果がC2,D2・・・にも表示されている」 と言うお話ですので、こちらも疑ってみます。 COLUMN関数の参照先が、正しいのに 表示されている結果がおかしい場合は ツール→オプションの[計算方法]タブで 「自動」が選択されているか確認してください。
一つ御願いがあります。 文章の最初に半角スペースを入れると コメント欄での改行が、そのまま改行として表示されますので 適度に改行を入れながら （なるべく前詰めで） コメントを書いてもらいたいと思います。 （実は、非常に読みにくいのですよね。） _←この部分に半角スペース です。
(HANA)
私は、貴方のシートは見えません。 「正常に表示されない」と言うご報告だけでは 何が起きているのか、推測することすら出来ません。
貴方が先に ＞どのセルも正しく表示されなくなる と仰ったとき、私は何と聞きましたか？ 「式を載せて下さい」ともう一つ 聞いたことがありますよね？
また ＞_←この部分に半角スペース です。 こちらの記述は読んでいただけておりますか？ 改行を行っただけでは、改行してくれません。 文頭に半角スペースを入れて下さい。
この行は、半角スペースが入っていません。（改行）
ここで改行していますが、改行されません。
(HANA)
大変不慣れなため申し訳ありません。
もう一度お答えします。
B2ｾﾙには =IF(COUNTIF(Sheet1!$N:$N,"19-*")<ROW(A1),"",INDEX(Sheet1!$A$1:$K$1806,
MATCH("19-"&ROW(A1),Sheet1!$N$1:$N$1806,0),COLUMN(A1)))
C2ｾﾙは =IF(COUNTIF(Sheet1!$N:$N,"19-*")<ROW(B1),"",INDEX(Sheet1!$A$1:$K$1806,
MATCH("19-"&ROW(B1),Sheet1!$N$1:$N$1806,0),COLUMN(B1))) と入っていますが、
計算方法が「手動」となっていたため隣のｾﾙも同じ表示となっていました。
「自動」にしたところ正しく表示されました。
ただ、横には正しく表示されるようになりましたが、縦には正しく表示されません。
B3ｾﾙには =IF(COUNTIF(Sheet1!$N:$N,"19-*")<ROW(A2),"",INDEX(Sheet1!$A$1:$K$1806,
MATCH("19-"&ROW(A2),Sheet1!$N$1:$N$1806,0),COLUMN(A2)))
B4ｾﾙには =IF(COUNTIF(Sheet1!$N:$N,"19-*")<ROW(A3),"",INDEX(Sheet1!$A$1:$K$1806,
MATCH("19-"&ROW(A3),Sheet1!$N$1:$N$1806,0),COLUMN(A3)))
と式を入れています。
>元表のタイトルが入っているセル番地 作業列に使用した列 作業列に入れてある数式 表示用のB2に入れた式
元表のタイトルは Ａ２からＫ２ 作業列に使用した列 Ｎ列 作業列に入れてある数式 =A2&"-"&COUNTIF(A2:$A$2,A2) 表示用のB2に入れた式 上の式です。
すみません。 元表のタイトルはＡ１からＫ１の誤りです。
そこは変更して無いのですよね？
＞また ＞＞どのセルも正しく表示されなくなる ＞そのセルには、何が表示されているのですか？ 「何が表示されていますか？」です。 これに関して、貴方は私に情報をくれましたか？
＞Ｃ２、Ｄ２もすべてＢ２と同じ元表のＡ１が表示されます。 A3,A4,A5も全て、B2と同じ元表のA1が表示されているのですか？
焦らなくて良いですからね。 ゆっくり、教えて下さい。
(HANA)
セルに表示されているのは「#VALUE!」です。 この件はこちらで気づきました。 これまでは同一ファイルのシート間で表示させる式でしたが、今使って いるのはファイルが別なのでどちらのファイルも開いていないと正しく 表示されないことに気づいていなかったのが原因でした。 この点を守ればうまく表示されました。 つまらないことですみませんでした。 ただ、新たな問題としてファイルが別では常にどちらのファイルも開か なくてはならないため表示されている側のファイルのみ開くことが出来 ません。 あらたにファイルを作成しそのファイルのシートに値のみ貼り付ければ いいのですがこれを簡単にできる方法はあるのでしょうか。
貴方が私に教えてくれた情報です。 [１]これまでと違っている点は表示側の起点となるセルがＡ２からＢ２に変わった点のみです。 [２]計算方法が「手動」となっていたため「自動」にしたところ正常に表示されました。 ただ、横には正常に表示されますが縦には正常に表示されません。 [３]B2ｾﾙには =IF(COUNTIF(Sheet1!$N:$N,"19-*")<ROW(A1),"",INDEX(Sheet1!$A$1:$K$1806,・・・ どれが正しい情報でしたか？
(HANA)
どれも正しい情報です。 元表のＡ列からＫ列までのセル内容を表示側表の Ｂ列からＬ列に表示させるということです。 ＢからＬにセルの内容を見ていくとROWとCOLUMNの()の中が A1、B1、C1・・・と変わっていきます。 縦の場合も同様にA1、A2、A3・・・と変わっていきます。 これに計算方法が「自動」にすることですべてうまく表示され ています。 正常に表示されなかったのはよくわかりませんが、現在はすべて 正常です。
(覚えたて)
貴方は上で ＞[２]計算方法が「手動」となっていたため「自動」にしたところ正常に表示されました。 ただ、横には正常に表示されますが縦には正常に表示されません。 と書いて居られます。 この時点で、正常に表示されて居なかったのですよね？ そして、私は 「何が表示されていますか？」 と言う質問をしました。 それに対する貴方の答えが ＞セルに表示されているのは「#VALUE!」です。 ＞今使っているのはファイルが別なので・・・ です。 [１][２][３]の情報が全て正しいなら このお答えにはならないと思いますが？
[１]ブック間参照に変更 [２]全ての行列が上手く表示されない [３]式はブック間参照の物 これが、貴方のお答えから導き出される状況ではないですか？
主張することは大切です。 ですが、聞かれた事に答えること。 現在質問している事が解決したなら その時点でその旨発信すること。 これらも大切なことですよ。
最新の御質問に関しては、 「ブック間参照はお薦め出来ません。」 これは、先に言っておきます。 どうしてもそうする必要があるので有れば 現在エラー処理に、COUNTIF関数が使ってありますが これを使わない式にしてみてください。 ISERROR関数で処理しても良いと思いますし エラー値はそのままで、条件付き書式で「見えなくする」 と言う方法でも、良いかもしれません。
INDEX関数もMATCH関数も、参照先ブックが閉じている時でも 「更新する」を選択すれば、正しい答えを返してくれると思います。
(HANA)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.