スペースとアルファベットが1行に並んでいる時、
(スペースを除く)アルファベットの一番左と一番右の列番号を調べるには、
どうしたらいいでしょうか?
例えば、
○○A○BCB○○
という行があるとき、(スペースを○で示しています)
Aがある列番号(3)と一番右のBがある列番号(7)
をセルに表示できないかと思っています。
自分なりに調べてみたのですが、
正解にたどり着けませんでした。
初歩的な質問かもしれませんが、
よろしくお願いします。
配列数式で対応できるかと思います。
1行目に○○A○BCB○○ があるとして…
左の列番号は
=MIN(IF(TRIM(1:1)<>"",COLUMN(1:1),99999))
右の列番号は
=MAX(IF(TRIM(1:1)<>"",COLUMN(1:1),-1))
配列数式なので式を入力後 ENTER キーだけではなく
SHIFT + CTRL + ENTER
を入力してください。
数式バーの表示は以下のようになります。
{=MIN(IF(TRIM(1:1)<>"",COLUMN(1:1),99999))} {=MAX(IF(TRIM(1:1)<>"",COLUMN(1:1),-1))}
ありがとうございます。
MATCH関数だと一番左側はできそうなのですが、
一番右側はどうしたらいいでしょうか?
具体的な式を教えていただけると助かります。
VBAの話ですよね?私も素人なのですが、ちょっとがんばってみます。
まず、AだとかBを検索するループを書きます。
一番左の列番号を調べる場合は、最初に検索が一致したところで変数に列番号を格納してループを抜けるようにプログラミングします。
一番右の列番号を調べる場合は、Cells(y,x)のxが終端まで来るまでループが繰り返されるよう組んで、
ループの中身は一番左の列番号を調べるのと同様、
If Cells(y,x).Value = "アルファベット"
を条件とするのですが、TrueでもFalseでもループを抜けず、Trueの場合のみ変数の値(xの値)を上書きしながらxが規定値に達するまで検索を続けます。
上記の方法で2つの変数 一番左の列番号 と 一番右の列番号を得られます。
あとはこの変数を使ってセルに表示するだけです。
ありがとうございます。
私も最初VBA組むのかなー、と
なんとなく考えていたのですが、
具体的にどうやったらいいかな?
もう少しいいやり方がないのかな?
と思って質問させていただきました。
具体的な方法の説明、ありがとうございます。
あまり列数が多いと煩雑になってしまいますが、例示の内容で関数を書いてみました。
例示の値がA1:I1に入っているとして
まず
J1に=IF(A1="",1,2)
と入力し、横方向にR1セルまでフィルコピーします。
それぞれ一番目の列数をS1、最後の列数をT1に表示するとして
S1=FIND(2,CONCATENATE(J1,K1,L1,M1,N1,O1,P1,Q1,R1))
T1=LEN(CONCATENATE(R1,Q1,P1,O1,N1,M1,L1,K1,J1))-FIND(2,CONCATENATE(R1,Q1,P1,O1,N1,M1,L1,K1,J1))+1
と入力すると求める値が表示されます。
これは、最初のIF文でスペース列を1、とアルファベット列を2に文字変換し、
変換した文字列を一つの文字列にし、その文字列で最初の2を見つけさせて値をだし、
最後の文字は、文字列の合体を逆順にして後ろから何番目かをまずだしてから、全体の列数から値をだしています。
以上ご参考まで。
ありがとうございます。
まずは配列数式でやってみようと思っていますが、
教えていただいた方法も勉強したいと思います。
配列数式で対応できるかと思います。
1行目に○○A○BCB○○ があるとして…
左の列番号は
=MIN(IF(TRIM(1:1)<>"",COLUMN(1:1),99999))
右の列番号は
=MAX(IF(TRIM(1:1)<>"",COLUMN(1:1),-1))
配列数式なので式を入力後 ENTER キーだけではなく
SHIFT + CTRL + ENTER
を入力してください。
数式バーの表示は以下のようになります。
{=MIN(IF(TRIM(1:1)<>"",COLUMN(1:1),99999))} {=MAX(IF(TRIM(1:1)<>"",COLUMN(1:1),-1))}
ありがとうございます!
この方法を試してみたいと思います。
http://pc.nikkeibp.co.jp/pc21/special/hr/hr3.shtml
配列数式を使えば簡単ですよ。
データが入っているのを1行目とすると
①一番左の行番号の場合
=MIN(IF(LEN(TRIM(1:1))=0,2^16,COLUMN(1:1)))
②一番左の行番号の場合
=MAX(IF(LEN(TRIM(1:1))=0,0,COLUMN(1:1)))
をA2とかのセル入力し、Ctrl+Shift+Enterで決定します。
すると、こんな表示になります→{=MIN(IF(LEN(TRIM(1:1))=0,2^16,COLUMN(1:1)))}
軽く式を説明するとtrimでスペースを削除した上でLEN関数で長さを見て
スペースでない場合の最小or最大の列番号を求めます。
2^16はそんなに深い意味はありません。
MIN関数を使っているので列の最大より大きい数字なら何でも結構です。
ありがとうございます!
この方法を試してみたいと思います。
ありがとうございます!
この方法を試してみたいと思います。