• ベストアンサー

Execelで列の検索に関する質問、最終行から2番目を見つけたい

お世話になります。 Execelの例えばA列に入力されている最終行から2番目の行の値(文字列のこともあり得る)を返したいのですが、 どうしたらよいのでしょうか? 並びは以下のようにランダムで、この場合であれば、 「7」を返したいのです。 12 8 15 7 12 宜しくお願い致します。

質問者が選んだベストアンサー

  • ベストアンサー
noname#70958
noname#70958
回答No.6

途中に空欄があるとすると、 「最終行から2番目の行」が空白の場合にどうするか解釈の余地がありそうですが……。 解釈A.【空白でないセルのうち、2番目に下方にあるセル】の値を返す場合  ※長さゼロの文字列""は「空白」「未入力」とみなす  ※必ず空白以外の値が返る  =INDEX(A1:A99,LARGE(INDEX((A1:A99<>"")*ROW(A1:A99),),2)) 解釈B.【空白でないセルのうち、もっとも下方にあるセルの上隣のセル】の値を返す場合  ※長さゼロの文字列""は「空白」「未入力」とみなす  ※該当セルが空白なら空白が返る  =INDEX(A1:A99,MAX(INDEX((A1:A99<>"")*ROW(A1:A99),))-1) Excel2003で動作確認。以上ご参考まで。

berry_late
質問者

お礼

2通りの解釈を頂きありがとうございます。 解釈Bがほしいものです。 そして私もダミーのデータを使用して行うととてもうまくゆきます。(2007環境) なのに、既存の大きなデータ上(少しシート上のデータが多い)で行うとなぜか「0」になり不思議でなりません。 一つ一つの関数を追いながら考えてもなぜ「0」になってしまうのかさっぱりですので、時間があるときにゆっくりシートを確認しなおしてみます。 ありがとうございました。 急場は、空白セルにダミーデータを挿入して、#1番さんの最も単純な方法で凌ぐことにしました。

その他の回答 (5)

  • imogasi
  • ベストアンサー率27% (4737/17068)
回答No.5

#4です。 >VBAは諸事情から使いたくないです 事情にもよるが、#4に書いたのは、「ユーザー関数」で、それはVBAで記述するが、一般のVBAの処理と違うものと思いますが。 一般のエクセル関数と区別はほとんどない。 念のため。

  • imogasi
  • ベストアンサー率27% (4737/17068)
回答No.4

=INDEX($A$1:$A$50,MAX(IF(A1:A50<>"",ROW(A1:A50),0))-1,1) と入れて、SHIFT+CTRL+ENTERの三つのキーを同時押しする。 ただし上記で求めた対象のセルが空白だと、0 を返してしまう。 実質的な(空白を除いたセルはVBAでユーザー関数を定義しないと ややこしいでしょう。 Function mae(a) Application.Volatile True d = Cells(65536, a).End(xlUp).Row For i = d - 1 To 1 Step -1 If Cells(i, a) <> "" Then mae = Cells(i, a) Exit For Else End If Next i End Function この場合、=mae("A")のように列を引数で指定する。この場合A列以外にこの式を入れるか、上記コードの65536をその式より上の行番号に限定すること。

berry_late
質問者

お礼

ありがとうございます。 VBAは諸事情から使いたくないです。。。。すみません。

回答No.3

#1です。 VBAを使用して、以下のようにすれば簡単にB1セルに値が出ます。 Sub sample() Range("B1").Value = Cells(Range("A65536").End(xlUp).Row - 1, "A") End Sub ただ、A列のデータ件数が不定で、空欄個数も何個か不明であれば、関数のみで出すとなると他の条件が無いと厳しいかもしれません。 例えば、一番下のセルが『合計』という文字が入力されているとか、最大値が入力されているとかなら抜け道が出てきますけど。

berry_late
質問者

お礼

ありがとうございます。 VBAは諸事情から使いたくないです。。。。すみません。

noname#204879
noname#204879
回答No.2

=OFFSET(A1,COUNTA(A1:A5)+COUNTBLANK(A1:A5)-2,)

berry_late
質問者

お礼

なぜでしょう。ダミーを作って試すと成功しますが、実際のシート上ではワークしません。。。。不思議です。 ちょっと研究してみます。

回答No.1

=OFFSET(A1,COUNTA(A:A)-2,) この式で出ると思います。 ただ、データの途中に空白セルが入らない事が前提です。

berry_late
質問者

お礼

ありがとうございます。 空欄も入るんですが、、、どうしたらよいでしょう?

関連するQ&A

  • 最終行に合計(最終行が列によって異なる場合)

    エクセルVBAに於いて質問させて頂きます。 タイトルにも書かせていただきましたが、 A列に行数可変のデータ(文字列)があり、 B列に行数可変のデータ(数字)があり、 A列の行数>=B列の行数の条件とき、 A列の最終行+2の位置のA列に文字で合計 A列の最終行+2の位置のB列にsumを表示する場合、 どのようにすれば出来ますでしょうか。 A列の最終行+2の位置のA列に文字で合計は With Range("A3") .End(xlDown).Offset(2, 0).Formula = "合計" End With でいけると思うのですが、 A列の最終行+2の位置のB列にsumを表示する場合どのようにすればいいのかわかりません。 ご教授のほどお願い致します。

  • エクセル・マクロで最終行を表示することについて

    エクセル・マクロで最終行を表示することについて C列の最終行を表示する場合、以下のマクロで表示できます。 今回はC列に C1=A1&B1 C2=A2&B2  ↓ という式が入力されている場合について質問します。 A列とB列が空白の場合、C列には0が表示されます。 したがって、下記のマクロだとこの0の行が最終行となります。 私の希望はこのC列の0は空白とみなし、0以外の値や文字が入力されている最終行を表示することです。 どのような工夫をすれば可能でしょうか? ご指導よろしくお願いします。 Sub 最終行表示() maxrow = Range("c65536").End(xlUp).Row MsgBox maxrow End Sub

  • エクセル最終行の下に貼り付け

    VBAで以下の作業を教えて下さい。 Sheet1のB列に入力済みのセルが何行かあります。 その入力済みのセルの値をコピーして、Sheet2のA列の入力済みの最終行のすぐ下の行に貼り付ける。 *Sheet1のB列の入力済みのセルの行数は毎回変わります。 *Sheet2のA列には一番最初は何も入力されていない状態です。 宜しくお願い致します。

  • EXCELの最終列の数値の取得

    お世話になります。 EXCELにて画像のようなデータがあった場合についてです。 例えばA列には受注目安の1回目を入力する欄が設けられており、それぞれの状態を入力します。 OKの場合はそれ以降入力する必要がないのでA列が最終列となりますが、未定の場合はC列の 受注目安2に再度入力しており、それが最終列扱いとなります。 売上も同じような方法で入力していくのですが、その右側にそれぞれの行の一番最後の値が 最終売上、最終目安に反映されるようにしたいです。 ネットで調べてみて最終行の取得などはあったのですが、入力された最終行の取得について 分かりやすい解説が見つかりませんので質問いたしました。 お手数ですが、解決方法をご教授下さい。 よろしくお願いします。

  • 文字の場合でもA列の最終行の行番号を取得するには

    A列の最終行の行番号を、 =MATCH(10^10,A:A) を使えば取得できますが、 これはA列にの最終行が数値の時だけで、文字列が最終行の場合は無視されるようです。 文字列でも取得する方法はありますか? 10^10の部分を変更すればいいと思いますが、どうすればいいですか?

  • 列最終行を取得し別列に貼付け

    VBAでA列最終行を取得しB列にその行の値を貼付けることができますか宜しくお願いします。

  • 該当行の最終列にコピーする

    すべて windows7 使用するPCによってoffice2007、2010とoffice xp Sheet1A1:D1000の表をSheet2&3A1:BZ1000の表該当セルにコピー(書式、セルフォント色とも)します。 Sheet1 1行めはタイトル行です。 Sheet1A2の値(英数字)をSheet2&3A:Aから検索し、その行の最終列の次列からSheet1B2:D2の値(B2は文字列C2は日付D2は文字列)をコピー(書式、セルフォント色とも)します。

  • 最終行の検索なんですが、、

    きっと簡単だと思うのですが、最終行の検索が、、ど初心者でわかりません。 A列の最終行(可変)にあるB、C、D、Eの値を拾いたい。 データは別のシートにあります。 過去の質問を2時間くらい検索したけどVBA、マクロ関連が多く参考にできるものが見つからないのでよろしくお願いします。

  • 何列の何行目が最終行なのか取得するコードはありますでしょうか?

      A列  B列  C列 1  ○   ○ 2        ○ 3               ○ これは最終行が3行目と言うのがぱっと見でわかりますが もっと列も行も多い場合、 シート内の何列の何行目が最終行なのか取得するコードはありますでしょうか? Cells(65536, ?).End(xlUp).Row を行ないたいのですが 毎回何列の行が一番下かを取得する方法があれば教えてください。 よろしくお願い致します。

  • エクセルでの行検索

    エクセルにおいて、2つの列に入力されている「値」が違うものだけを抽出する方法はありますか? 具体的には、 A列 B列 1  1 2  2 3  3 4  8 5  5 6  6 のように入力されている時に、4番目の行だけを抽出する方法です。

専門家に質問してみよう