エクセル関数で

質問

ある特定のセルに、そのセルが存在する行の一番右端の特定の記号（たとえば「○」印）を特定して、そのセルが存在する列の上に存在する特定のセルの値を表示させる方法をおしえて下さい。
12
　　　　　　 ↑
セル→→→→→○ ・・・一番右端（○は複数存在）
↑
(12)と表示させたい。

回答No.7

shiotan99 · ベストアンサー

No.5です。

> △や×は無視して、いちばん右の○の場合でしたので、

‥ということは、この式↓の説明をすればいいのでしょうか？
=IF(COUNTIF(B4:K4,"○"),INDEX($B$3:$K$3,,MAX(IF(B4:K4="○",COLUMN(B4:K4)-1,""))),"")

COUNTIF 関数や INDEX 関数はご存知だという前提で要点だけ説明します。

★ 試しに新規シートに次のように入れてみてください。
B1セルに 2005/11/1 と入力して、F1セルまで右にフィルコピー
2行目は C2 と E2 セルだけに ○を入力
B3セルに
=IF(B2="○",COLUMN(),"")

と入れて、F3セルまで右にフィルコピー
結果は、C3に 3、E3に 5‥つまり2行目に ○を入力した C列とE列だけに数字が入ります。

COLUMN 関数は指定したセルの列番号を返す関数です。列番号というのは、A列が1、B列が2、C列が3‥なので、=COLUMN(A1) なら 1が、=COLUMN(C1) なら 3が返ります。
=COLUMN() のように（ ）内の引数を省略すれば、COLUMN関数を入力したセルの列番号が返ります。
C3に 3、E3に 5と表示される理由がおわかりになりましたでしょうか？

2行目のいちばん右にある○の位置は、3行目の数値の最大値（ 上の例では 5 ） が取得できればわかります。
あとは INDEX関数を使って、B1：F1の日付のうち、5番目の日付を参照すればいいわけですが、5番目というのは A列から数えて 5番目ですから、そのままでは 1コずれてしまいます。
5 から 1を引いて 4番目‥B列から数えて 4番目の E1セルの日付を参照すればいいことになります。

★ A2セルに
=INDEX(B1:F1,,MAX(B3:F3)-1)

と入れてみてください（ フツーに Enterです ）。3行目のデータを利用して、いちばん右に○が入っている列の日付を表示します。
このように作業用に1行使えば、これだけの数式で目的の日付を表示させることができます。

★ 下の A3セルに
=INDEX(B1:F1,,MAX(IF(B2:F2="○",COLUMN(B2:F2)-1,"")))

と入れて、これは配列数式ですから Ctrl＋Shiftキーを押しながら、Enterで確定してみてください。3行目のデータは使っていませんが、A2セルの数式と同じ結果になるはずです。
◆
前置きがすごく長くなりましたが、配列数式なら作業用の行を使わなくても、上の 3行目でやっているようなことを、エクセル内部でやってくれるということです。
IF(B2:F2="○",COLUMN(B2:F2)-1,"") の部分で

B2 が ○なら COLUMN(B2)-1 つまり 1、○でなければ空白（""）、
C2 が ○なら COLUMN(C2)-1 つまり 2、○でなければ空白（""）、
・
F2 が ○なら COLUMN(F2)-1 つまり 5、○でなければ空白（""）、
というデータを、B2：F2まで順に見て、エクセル内部のメモリに格納しています。

上の例では C2 と E2セルにしか ○は入っていないので、｛"",2,"",4,""} という配列データが格納され、その中の最大値4 を MAX関数で取得します。
あとは（ くり返しになりますが ）、INDEX関数を使って、B1：F1の中の 4番目の日付を参照しています。
◆
配列数式について詳しくは↓の参考URLをご覧ください。
わかりやすく解説してあります。

参考URL：

http://pc21.nikkeibp.co.jp/special/hr/

お礼

今ＰＣで実際に例題をやってみました。配列の考え方がとても良く理解できました。感動しました！
本当に、どうもありがとうございました。

補足

ものすごくよく分かりました。shiotan99さんは、ものを教えることの天才ですね！！shiotan99は、エクセル関数をどのような教科書で学ばれたのですか？もし差し支えなければ教えて頂けませんでしょうか・・・。

回答No.8

Wendy02

こんばんは。

#6のWendy02です。私が書いたのは、そのご質問を明らかにしたいわけで、サンプル図を載せて、自分の理解が合っているのか確かめたかったからです。

それが結果的に、数式が合っているかどうかも、分るわけです。式の解説をいくらお話しても、それは、結果如何でしかありません。元が間違えば、何もなりません。

#7さんで解説がありますので、直接の内容には触れませんが、「配列数式」については、私個人としては、VBAの基礎レベルよりも、はるかにむつかしいと思います。
多くの人の発見や多くの経験から導き出された定石のようなものから組み立てる数式だと思います。ただ、伝家の宝刀のような配列数式も、有効なようでいて、配列数式は、その割には、限られたスペースの掲示板の中だけで、実務上は、あまり使う必要がないテクニックかとも思います。私は、VBAが中心ですが、ここらあたりは、隔靴掻痒というところですね。VBAで同じことは出来ます。しかし、VBAでは、どんなにがんばっても、組み込み関数を越えることは出来ませんからね。

＞どの点で異なるのでしょうか？
ひとつだけ大きく違うのは、配列確定をしない、ということです。配列確定は、仮想メモリをそのセルに取り、計算領域を作ります。私のも構造的には同じですが、それをアプリケーション側に直接渡さないで、もう一度、Index関数のバッファを作って、そこで計算しなおしています。

お礼

Wendy02さん、こんばんわ。
サンプル図は、まさに私の質問の内容そのものです。丁寧な解説、どうもありがとうございました。私の印象ではＶＢＡは最強で、なんでもできると思っていました。ＶＢＡも配列もどちらも難しくまるで違う世界のもののようです。また分からないことがありましたらよろしくお願い致します。ありがとうございました。

回答No.6

Wendy02

こんにちは。

回答者さんたちが、そのつど確認しているようですが、元のご質問の内容に具体性が乏しいようです。もう少し、具体的で正確な情報を与えるようにすれば、正解に早く至るような気がしますね。

#2の回答に対する補足
＞上側の特定のセルのデータは日付データです。
質問事項は、後だしではなくて、最初に出してくださいね!

表例:(IE画面は広げてご覧ください）

　　A　　　B　　　　C　　　　 D　　　 E　　　　F　　　　G
1　　　　11/01　　11/02　　11/03　　11/04　　11/05　　11/06・・
2　11/5　　　　　　 ○　　　　　　　　○　　　　○
3　11/4　　　　　　　　　　　　　　　 ○
4　　　　　　　　　 △　　　　　　　　×
5　11/6　　 ○　　　　　　　　　　　　　　　　　　　　　○
6　11/5　　　　　　 ○　　　　○　　　 ×　　　 ○
7　11/3　　　　　　　　　　　 ○
　　↑
　書式は、日付書式を使ってください。

（掲示する時のレイアウトの調整は、全角空白「　」を使ってください。）

A2の式は、

=IF(COUNTIF(B2:AF2,"*○*"),INDEX($B$1:$AF$1,,MAX(INDEX(COUNTIF(OFFSET($A2,,ROW($A$1:$A$31)),"*○*")*ROW($A$1:$A$31),,))),"")

ということになるかなって思います。

※「*○*」 は、入力時に空白などなければ、「○」でよいです。

補足

ご回答ありがとうございます。また、質問の内容が具体的でなく申し訳ありません。今後注意致します。
勉強のためにおしえて頂きたいのですが、ＮＯ５の方の式（=IF(COUNTIF(B4:K4,"○"),INDEX($B$3:$K$3,,MAX(IF(B4:K4="○",COLUMN(B4:K4)-1,""))),"")）とはどの点で異なるのでしょうか？レベルが低くて申し訳ありません。よろしくお願いします。

回答No.5

shiotan99

こんにちは～

同じ行に○は複数あるんですよね。
○が複数ある場合、No.4さんの数式だとうまくいかないと思うのですが‥
（ 右端ではなく、左端の○を検索しますから ）
それと、同じ行に △とか×も混在していますが、○だけが対象なのでしょうか？

要は↓が不明です。
・記号が○とか△に関係なく、いちばん右端の記号を検索するのですか？
それとも、△とか×は無視して、いちばん右の○を検索するのですか？
・記号はすべて手入力ですか、それとも数式を使って表示しているのですか？

**********
日付データが B3：K3、記号が B4：K4の範囲内にあるとします。
下の中から該当する数式を A4セルに入れてみてください。
（ コピー＆ペーストしてください ）
**********

■ ○とか△に関係なく、いちばん右端の記号の場合
◆記号は手入力
=INDEX($B$3:$K$3,,MATCH("*",B4:K4,-1))

または、記号が1つも入力されていない場合のエラーを非表示にするなら
=IF(COUNTA(B4:K4),INDEX($B$3:$K$3,,MATCH("*",B4:K4,-1)),"")

◆記号は数式を使って表示させている（ 空白は、数式の"" を使っている場合 ）
=INDEX($B$3:$K$3,,MAX(INDEX((LEN(B4:K4)>0)*COLUMN(B3:K3)-1,0)))

または、記号が1つも表示されていない場合のエラーを非表示にするなら
=IF(COUNTIF(B4:K4,"*?"),INDEX($B$3:$K$3,,MAX(INDEX((LEN(B4:K4)>0)*COLUMN(B4:K4)-1,0))),"")

★ COLUMN(B2:K2)-1 の -1は、データが B列から始まっている場合です。C列から始まっていれば -2、D列から始まっていれば-3、‥のようにしてください。

**********
■ △や×は無視して、いちばん右の○の場合
=IF(COUNTIF(B4:K4,"○"),INDEX($B$3:$K$3,,MAX(IF(B4:K4="○",COLUMN(B4:K4)-1,""))),"")

と入れて、配列数式ですので Ctrl＋Shiftキーを押しながら、Enterで確定してください。
数式が{ }で囲まれたらOK
★ 囲まれなかったら NGですので、F2キーを押して編集モードにしてから、もう一度Ctrl＋Shiftキーを押しながら、Enterで確定してください。
（ 配列数式はこの式だけで、これ以外はフツーに Enterで確定です ）

★ COLUMN(B2:K2)-1 の -1は、データが B列から始まっている場合です。C列から始まっていれば -2、D列から始まっていれば-3、‥のようにしてください。

いずれの数式も、必要なだけそのまま下にフィルコピーすればOKです。
数式を入れたセルの表示形式は、《日付》にしてください。
範囲は、実際の表に合わせて変更してください。

補足される場合は、できるだけ具体的にお願いします。
データがどの列からどの列まであるのか、など。
また、上記数式でうまくいかなかった場合、どの数式を入力したのか、そのままコピーして提示してください。

補足

どうもありがとうございました。△や×は無視して、いちばん右の○の場合でしたので、そのまま試してみましたところうまくいきました。向学のために簡単に解説をいただけないでしょうか？よろしくお願い致します。

回答No.4

imogasi

#3の補足に答えて
Ａ４：Ｋ２０までの範囲に○が１行あたり、１つ入っているとします
Ｂ３：Ｋ３まで日付けが入っているとします。
Ｌ４に
=IF(ISERROR(MATCH("○",A4:K4,0)),"",INDEX($A$3:$K$3,0,MATCH("○",A4:K4,0)))
と入れて、Ｌ２０まで式を複写します。
Ｌ４：Ｌ２０に○の列の第3行目の日付が入ります。
ＭＡＴＣＨで○の第何列数（仮にＸ）を求め、ＩＮＤＥＸで、だい３行目（０）の第Ｘ列のセルの値をとってきてます。

回答No.3

imogasi

特定の記号を「○」と仮定します。
＞セルが存在する列の上に存在する特定のセルの値
を直上（同列の接してすぐ上）のセルと解します。
例データ
Ｆ１＝あ、Ｆ２＝○
Ｅ２＝い、Ｅ３＝○
第４行目＝空白
Ｃ５＝う、Ｃ６＝○
とします
データはＫ列までしか入らないとして
Ｌ２セルに式
=IF(ISERROR(MATCH("○",A2:K2,0)),"",(INDEX($A$1:$K$20,ROW()-1,MATCH("○",A2:K2,0))))
と入れて、下方向に式を複写します。
Ｌ２＝あ、Ｌ３＝い、Ｌ４＝空白、Ｌ５＝空白、Ｌ６＝う
となりました。
こういうので良いでしょうか。

補足

凄いですね。こういう感じです。ただ、あ、い、う、は、○の直上ではなくて、○のあるセルの列の三行目という指定にしたいのですが（あ、い、う、は日付で右一列に並んでいます）。また、表示は左側にするのですが、どこを変えればいいのでしょうか？

回答No.2

odessa7

ご質問で確認したいことがあるのですが、下記のようなことをしたいという意味でよろしいのでしょうか？
　　10 5 7 22 10 12
Ｐ　△　×　×　◎　△　○

　↑この例で言うと、Ｐの場所に「６」を表示させる。

　もし、上記の意味の場合、最後の○の左側のすべてのセルには、必ず何かの記号が入力されていますか？

補足

補足させて頂きます。
お示しの例の場合で、Ｐの場所に、一番右側の○について、その上側の特定のセルの値を示したいのです。上側の特定のセルまでは何も記号がない場合もあります。また、最後の○の左側のセルには記号が入力されていない場合があります。
ちなみに、上側の特定のセルのデータは日付データです。

　　１ ２ ３ ４ ５ ６ ７ ８ ９
Ａ
Ｄ
Ｐ　＿＿△＿○＿＿○
Ｆ　＿＿＿×＿＿＿＿○

上の例では、Ｐの上のデータの８、Ｆの上のデータの９をひょうじしたいのです。よろしくお願い致します。

回答No.1

hanada087

うーん。文章だけだとキツイですね。(^^;
取り敢えず自分だったらこう↓します。

■ まず、=right(セル,1) ってすると、そのセルの文字列の一番右の1文字だけ抜き出せます。これを邪魔にならない行に入れてズラーっとコピーします。（これを仮に「セルＢ」とします）
＞＞この段階で、セルの右端が○なら”○”って表示させることができます。
■次に、また邪魔にならないところに、 =if(セルＢ="○",[12が入ったセル],"") って入力します。[12が入ったセル]のところは絶対参照にしておきます。例えばA1っていうセルなら、$A$1って入れます。そうやっといてからズラーっとコピーすれば、参照先は全部A1になります。

どうだろうなぁ。(^^;
いろいろ応用して探ってみて下さい。頑張ってね。(^^