ここから質問投稿すると、最大4000ポイント当たる!!!! >>

範囲内で空白を除いて一番左端のセルの位置を取得する方法というのはあるでしょうか?

たとえば以下のような表の場合にA1にはC1、A2にはD2、A3にはE3が入るようにしたいのです。
以下のような表の場合に
 A  B  C  D  E
1      1  2   3
2           2   3
3             3

わかりづらい質問で申し訳ありませんが、よろしくお願いします。

A 回答 (9件)

No.5です。



MATCH(1,INDEX(SIGN(LEN(B1:E1)),0),0)
MATCH(検査値, 検査範囲, 照合の型)

検査範囲は INDEX関数でつくられた配列データ。
配列データの内容は LEN(B1:E1)で、B1:E1各セルの文字数( 桁数 )。

質問の例ではわかりにくいので
B1:空白、C1:1234、D1:空白、E1:56
のようにデータが入っていると思ってください。

SIGN関数を使わずに、INDEX(LEN(B1:E1),0) だとすると、
{0,4,0,2}
という配列データになります。
0、4、0、2 は LEN関数の戻り値、B1:E1のそれぞれの文字数( 桁数 )です。

SIGN関数は数値の正負を調べる関数。
引数が正の数なら( 何桁であっても )1を返します。
だから SIGN関数を使って、INDEX(SIGN(LEN(B1:E1)),0) なら
{0,4,0,2} という配列データは
{0,1,0,1}
というふうになります。
ここでは SIGN関数の引数は文字数なので、セルが空白なら 0、データが入っていれば 1。戻り値は 0 か 1 かのどちらかです。

なので、MATCH関数は↓な感じになります。
=MATCH(1,{0,1,0,1},0)

1が検査値。{0,1,0,1}が検査範囲。
何桁の数値( 文字列でも )が入っていても、検査値を 1にしておけば左端のデータ位置を求めることができるってことです。

もしかしてこの検査値の 1を見て、
>左端の値は1とは限らないのです。
と思いました? ちっ、ヴァカな回答よこしやがって! て感じでした?

このときの MATCH関数が返す位置は B列を1とするものなので、正しいセル番地を求めるために +1しています。
MATCH(1,INDEX(SIGN(LEN(B1:E1)),0),0)+1

検査範囲が C列から始まるなら +2、D列から始まるなら +3のように調整してください。
    • good
    • 0
この回答へのお礼

丁寧な解説ありがとうございました。
配列を検査範囲に指定するというのを知らなかったので、最初回答を見てもチンプンカンプンでした><
よくわかりました。

>もしかしてこの検査値の 1を見て、
そのとおりでした。お恥ずかしい限りです。

お礼日時:2006/01/21 09:02

A-G列で左から見ていって、初めて空白でない数字文字などの現れる列番号


配列数式で考えてみました。たとえばH2セルに
=MIN(IF(A2:G2<>"",COLUMN(A2:G2),""))
と入れてSHIFT+CTRL+ENTERを(3つのキーを)同時に押す。
意味はA2:G2までで、空白セルでなければ、列番号を採る。
それらの列番号の中で最小の列番号を採ると、一番左の列を見つけたことになる。
    • good
    • 0
この回答へのお礼

>SHIFT+CTRL+ENTERを(3つのキーを)同時に押す。
これも知らない操作でした。
検索かけて、配列数式を調べてきました。
こんな便利な機能があったのかと、知らなくて損してた気分ですw
ありがとうございました。

お礼日時:2006/01/21 09:04

こんばんは。



私は、ご質問の意味がわからなかったので、そのままにしていましたが、単に、BLANK の次のセルを探すなら、このようにすれば簡単では?

=MATCH(FALSE,INDEX(ISBLANK(B1:E1),,),0)+1

なお、+1 を足すのは、MATCH関数で、B1 にあると、1が立つので、+1 を足します。
    • good
    • 0
この回答へのお礼

わかりやすい回答ありがとうございます。
あとはADDRESS関数を使ってやればいいようですね。
大変ありがとうございました。

お礼日時:2006/01/21 09:14

>SUMPRODUCT(MIN(10^(B1:E1="")*COLUMN(B1:E1)))


10^(B1:E1="")で空白のセルなら10、空白でなければ1を返します。
COLUMN(B1:E1)で行位置がでますので上記の係数をかけた上で
MIN関数で空白でない位置の最小値を求めています。
例題の1行目ならMIN(20,3,4,5)=3、2行目ならMIN(20,30,4,5)=4
このMIN関数の中で配列の計算をしている為、このままでは正常な値を返しません。
そのためにSUMPRODUCTで配列の和を求めて行の位置を表示させています。
    • good
    • 1
この回答へのお礼

解説ありがとうございます。
MINに配列を入れるとか、到底思いつかない方法ですね。目からうろこです。
範囲が広くなったら10を大きくしてやると言い訳ですね。
ありがとうございました。

お礼日時:2006/01/20 18:39

No.3です。



>左端の値は1とは限らないのです。

意味がわかりません。
実際に試していただいた上でのご発言でしょうか?
    • good
    • 0
この回答へのお礼

大変失礼いたしました。
こちらの確認不足と勘違いでした。
No.3のご回答でこちらの期待通りの動作をいたしました。
不愉快な思いをさせてしまい、申し訳ありませんでした。

よろしければ内容の説明をお願いできないでしょうか。
特に、
SIGN(LEN(B1:F1))
がさっぱりです><

お礼日時:2006/01/20 18:23

次のような式も考えられます。


=INDEX(B1:E1,1,COUNTBLANK(B1:E1)+1)
アドレスなら、式のある行とCOUNTBLANK(B1:E1)+1です。
    • good
    • 0
この回答へのお礼

例が悪かったです。大変申し訳ありませんでした。
こちらも参考になりましたが、途中で空白セルが入る場合もあるため、今回のものにはあわないようです。

お礼日時:2006/01/20 18:03

No.1です。



>セルの位置を取得

値じゃなく、アドレスですか?
これかな? p(..)

A1:
=ADDRESS(ROW(),MATCH(1,INDEX(SIGN(LEN(B1:E1)),0),0)+1,4)

下にコピー

ちなみにNo.1の式は↓に訂正しておきます。
=INDEX(B1:E1,,MATCH(1,INDEX(SIGN(LEN(B1:E1)),0),0))

結果は同じことですけど、B1:E1のあとはカンマ2個の方がいいです。
    • good
    • 0
この回答へのお礼

回答ありがとうございます。参考になりました。
ただ、こちらの例が悪かったです。左端の値は1とは限らないのです。
大変申し訳ありませんでした。

お礼日時:2006/01/20 18:00

A1=ADDRESS(ROW(),SUMPRODUCT(MIN(10^(B1:E1="")*COLUMN(B1:E1))),4,1)


コピーしてA2以降に貼り付けてください。
    • good
    • 0
この回答へのお礼

ありがとうございます。期待通りの動作しております。
ただ、なぜそうなるのかがわかりません(泣
SUMPRODUCT(MIN(10^(B1:E1="")*COLUMN(B1:E1)))
の部分を教えていただけないでしょうか。

お礼日時:2006/01/20 17:57

こんなので p(..) どうでしょう?



A1:
=INDEX(B1:E1,MATCH(1,INDEX(SIGN(LEN(B1:E1)),0),0))

下にコピー
    • good
    • 0

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Q値が入っている一番右のセル位置を返す方法

たとえばA行に値(文字列)が横に並んでいるとして
値が入っている一番右のセルの位置を返すようにするにはどのようにすればいいでしょう?

Aベストアンサー

セルの位置を返したいのであれば
=CHAR(MATCH(MAX(1:1)+1,1:1,1)+64)&1
でどうでしょうか。

Q範囲内の最左に入力された数値を取得する方法

エクセルにおいて、関数で
□□□□22222233333□□□□□
などと数値が入力されている場合、一番左の数値を抜き出すにはどのようにしたらいいのでしょうか?
一番右の数値を抜き出すやり方はわかったんですが、左の場合がわからなくて困ってます。。。

Aベストアンサー

お手軽に
=INDEX(A1:Z1,MIN(IF(ISNUMBER(A1:Z1),COLUMN(A1:Z1))))
と記入して,コントロールキーとシフトキーを押しながらEnteで入力してみるなどで。

Q値の入っているセルのうち、一番右側の値を取得したい(エクセル)

現在、データを左から右へ順次追加しています。
一番右側が、最新データとなります。
やりたいことは、複数の行にある、最新データを拾って、グラフを作成したいと思ってます。
どうやったら、できるのでしょうか?


<イメージ?>
●:最新データ
×:古いデータ
○:空欄
●のみを拾って、グラフを作成したい。

×××××●○
××××●○○
×●○○○○○
××××●○○

Aベストアンサー

こんにちは。maruru01です。

データがB列から、順に右へ入力されていくとして、A列に最新データを表示する場合は、

A1を選択して数式バーに、

=INDIRECT(ADDRESS(ROW(),MAX(IF(LEN($B1:$M1),COLUMN($B1:$M1)))))

と入力し、まだカーソルが数式バーにある状態で[Ctrl]と[Shift]を押しながら[Enter]を押して決定します。
(数式の両端に「{}」が付いて配列数式になります。)
これを下の行へコピーします。
あとは、このA列の値でグラフを作成すればいいと思います。
なお、上記の数式ではデータ入力範囲をB~M列にしましたが、後ろの列は、データを入力する可能性のある最大の列にして下さい。
(何列まで入力するか分からない場合は、エクセルの最終列のIVにしましょう。)

各関数については、ヘルプなどを参照して下さい。
配列数式については、以下のページを参考にして下さい。

http://pc21.nikkeibp.co.jp/pc21/pc_10/hr_top.htm

参考URL:http://pc21.nikkeibp.co.jp/pc21/pc_10/hr_top.htm

こんにちは。maruru01です。

データがB列から、順に右へ入力されていくとして、A列に最新データを表示する場合は、

A1を選択して数式バーに、

=INDIRECT(ADDRESS(ROW(),MAX(IF(LEN($B1:$M1),COLUMN($B1:$M1)))))

と入力し、まだカーソルが数式バーにある状態で[Ctrl]と[Shift]を押しながら[Enter]を押して決定します。
(数式の両端に「{}」が付いて配列数式になります。)
これを下の行へコピーします。
あとは、このA列の値でグラフを作成すればいいと思います。
なお、上記の数式ではデータ入...続きを読む

QEXCEL 関数 選択範囲一番右のセル番地確認方法

いつもお世話になっております。

EXCELの関数について相談をさせてください。
選択範囲($AC11:$IV11)で入力されているセルの一番左のセル番地を返す関数はありますでしょうか。
一番右のセルは、以下の関数で取得できるのですが、一番左が取得できません。

=INDIRECT(ADDRESS(7,LARGE(INDEX(($AC11:$IV11<>"")*COLUMN($AC11:$IV11),),1)))

ご存じの方がおられましたら、ご教授いただけませんでしょうか。
以上、よろしくお願いいたします。

Aベストアンサー

なぜ、そのような回りくどい数式を考えるのだろう??

そのまま考えて一番右
=INDEX($7:$7,MAX(IF($AC11:$IV11<>"",COLUMN($AC11:$IV11))))
[Ctrl]+[Shift] +[Enter] で確定、配列数式です({}で囲まれる)

一番左
=INDEX($7:$7,MIN(IF($AC11:$IV11<>"",COLUMN($AC11:$IV11))))
[Ctrl]+[Shift] +[Enter] で確定

11行目 文字列なら
一番左
=INDEX($AC$7:$IV$7,MATCH("*",$AC11:$IV11,0))
一番右
=INDEX($AC$7:$IV$7,MATCH("ーー",$AC11:$IV11))

11行目 数値なら 一番右
=INDEX($AC$7:$IV$7,MATCH(8^3^8,$AC11:$IV11))

とすることも可能です

Q【EXCEL】空白でないセルの位置を検索したい

初めまして。
EXCELの関数を利用して、
下記のようなことをしたいと思案中です。

 A B C D E F
1* 1   2   3
2* 1   2 3 3
3* 1   2   
4* 1   2 3 3

*が計算式を挿入したい箇所です。
各行の最初のブランク位置を検索したいです。
ブランクの個数は不定ですが、
B~Fのセルに入れるデータは全て数値です。

MATCH関数を使い、MATCH("",B3:F3)のようにしても、
#N/Aとなってしまいます。

何か良い方法はないでしょうか?
ご教授頂けますようよろしくお願い致します。

Aベストアンサー

配列数式を使ってようやく出来ました。
下は1行目のF列までの関数です。
{=MIN(IF(ISBLANK(B1:F1),COLUMN(B1:F1),""))}
入力する際に、コントロールキーとシフトキーを押しながらEnterキーを押してください。
指定した範囲に空白セルが無い場合は、0になります。それ以外は範囲中の最も左にある空白セルの列番号が数字で返されます。

Qある範囲のセルから任意の値を検索して、その隣のセルの値を取得するという関数はありますか?

Excelの関数について質問します。
ある範囲のせるを検索して、その隣のセルの値を取得するという関数を探しています。
なければユーザー定義で作りたいと思っています。
VLOOKUP関数では一番左端が検索されますが、
それをある範囲まで拡張して、
その右隣の値を取得できるようにしたいのです。
どうかお知恵をお貸しください。

Aベストアンサー

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場合によっては、IFをかぶせてCOUNTIFで確認した方が良いかもしれません。
 ex. =IF(COUNTIF(A1:F200,X1)=1,【上記数式】,"えらー")

ちなみに、VBAでやるならこんな感じになるかと。

動作の概要
 【検査範囲】から【検査値】を探し、
 最初にHITしたセルについて、右隣のセルの値を返す。
 ex. =Sample(X1,A1:F200)

'--------------------------↓ココカラ↓--------------------------
Function Sample(ByVal 検査値 As Variant,ByVal 検査範囲 As Range)
 For Each セル In 検査範囲
  If セル = 検査値 Then Exit For
 Next セル
 Sample = セル.Offset(0, 1)
End Function
'--------------------------↑ココマデ↑--------------------------

いずれもExcel2003で動作確認済。
以上ご参考まで。

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場...続きを読む

QExcel 一番右のセルを参照する

Excelで、A1からZ1のセルの内で一番右の入力してあるセルの数字を参照したい場合はどうしたらいいのでしょうか。
例えば、A1,B1,C2,D1,E1にそれぞれ、5, (未入力), 10,7,(未入力)とかの場合は、入力されている中で一番右側にあるD1にある7がその答えになります。

Aベストアンサー

すいません、補足です。
=HLOOKUP(10,A1:Z1,1,1)
の最初のパラメーター"10"は入力される最大値より大きい数字にして下さい。

QエクセルのIF関数で、文字が入力されていたならば~

エクセルのIF関数で文字が入力されていたならば~、という論理式を組み立てたいと思っています。

=IF(A1="『どんな文字でも』","",+B1-C1)

A1セルに『どんな文字でも』入っていたならば、空白に。
文字が入っていなければB1セルからC1セルを引く、という状態です。

この『どんな文字でも』の部分に何を入れればいいのか教えてください。

またIF関数以外でも同様のことができれば構いません。

宜しくお願いします。

Aベストアンサー

=IF(ISTEXT(A1),"",B1-C1)

でどうでしょうか?

Qエクセルで条件に一致したセルの隣のセルを取得したい

下のような「得点」という名前のシートがあります。
(「田中」のセルがA1です。)

 [ 田中 ][ 10 ][ 200 ]
 [ 山田 ][ 21 ][ 150 ]
 [ 佐藤 ][ 76 ][ 250 ]
 [ 鈴木 ][ 53 ][ 350 ]

別のシートのA1セルに、「佐藤」と入力すると、

 [ 佐藤 ]

「得点」シートから「佐藤」の列を見つけて、B1、C1に

 [ 佐藤 ][ 76 ][ 250 ]

のように表示させたいのですが、B1、C1にはどのような式を書けば良いのでしょうか。
「得点」シートでは氏名が重複する事はありません。
IF文を使うと思うのですが、いまいち良く分かりませんでした。

よろしくおねがい致します。

Aベストアンサー

こんにちは!
VLOOKUP関数で対応できます。
IF関数と併用すればエラー処理が可能です。

Excel2007以降のバージョンであれば
B1セルに
=IFERROR(VLOOKUP($A1,得点!$A:$C,COLUMN(B1),0),"")
としてC1セルまでオートフィルでコピー!
そのまま下へコピーすると行が2行目以降でも対応できます。

Excel2003までの場合は
=IF($A1="","",VLOOKUP($A1,得点!$A:$C,COLUMN(B1),0))

としてみてください、m(_ _)m

QExcelで、条件と一致する最後のセルを検索したい

Excelで、条件と一致する最後のセルを検索したいのですが、どの関数をどのように使えばいいかわかりません。
どなたかお力をお貸し下さい。

問)
日付  社名  品名  個数
1/1   A    あ    1
1/1   C    い    2
1/2   B    う     3
1/2   A    え    4
1/3   C    お    5
1/3   A    あ    3

答)
A社の最終購入日は  1/3 あ 3
B社の最終購入日は  1/2 う  3
C社の最終購入日は  1/3 お 5

となるようお願いします。

Aベストアンサー

エクセルには「最後の該当」を検索する関数はありません。
無駄に複雑な数式を駆使すればもちろん「やればできます」が、添付図のようにちょっと工夫するだけで、普段使いの数式だけでやっつけるのがお勧めです。

A2:
=IF(OR(C2="",COUNTIF($C$2:C2,C2)<COUNTIF(C:C,C2)),"",C2)
以下コピー

答えは簡単なVLOOKUP関数を使うだけです。
H2:
=IF(COUNTIF($A:$A,$G2),VLOOKUP($G2,$A:$E,2,FALSE),"")




まぁ一応ご参考に作業列を使わない方法:
H2:
=IF(COUNTIF($C:$C,$G2),INDEX(B:B,MAX(IF($C$2:$C$999=$G2,ROW($C$2:$C$999)))))
と記入し、必ずコントロールキーとシフトキーを押しながらEnterで入力する

説明は省略しますので、ブラックボックスで使ってください。


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング