【先着1,000名様!】1,000円分をプレゼント!

A1→1
A2→2

B1→=SUM(A1:A2)

と入力しています。
この状態で
A3→3
と入力した場合
自動的に
B1→=SUM(A1:A3)
にすることは不可能ですか?

「=SUM(A1:A3)」のA3の部分をA列の最終行を取得したいです。
VBAなら可能でしょうか?

ご教授よろしくお願い致します。

A 回答 (7件)

範囲内で最終行まで確実に数値があるなら


=SUM(OFFSET($A$1,,,COUNT($A$1:$A$100)))

範囲内で最終行までにブランクがあるなら
=SUM(OFFSET($A$1,,,MATCH(MAX($A$1:$A$100)+1,$A$1:$A$100)))

上記の$A$1と$A$100が範囲にかかわる部分です。
範囲が変わる場合はここを替えてください。
    • good
    • 1
この回答へのお礼

ありがとうございます。参考になりました。

お礼日時:2009/06/28 18:59

初心者にわかりよいのは INDIRECT関数のようです。


=SUM(INDIRECT("A1:A"&COUNT(A:A)))

人気は無いですがお勧めは
=SUM(A1:INDEX(A:A,COUNT(A:A)))

A1セルが文字列なら
=SUM(A1:INDEX(A:A,COUNTA(A:A)))


参考まで
    • good
    • 0
この回答へのお礼

大変参考になりました。ありがとうございます。

お礼日時:2009/06/28 21:59

=SUM(OFFSET(A1,,,COUNT(A:A)))

    • good
    • 0
この回答へのお礼

できました。ありがとうございます。

お礼日時:2009/06/28 21:59

質問者の気休めに、VBAで


Sub test01()
d = Range("A65536").End(xlUp).Row
MsgBox d
Cells(d + 1, "A") = WorksheetFunction.Sum(Range("A1:A" & d))
End Sub
直ぐ下の行にA1セルから最終行までの合計を出している。
ーー
関数では、1つのやり方として
=SUM(OFFSET(A1,0,0,COUNT(A1:A100),1))
A列のA1以下に数だけのセルが有る場合のこと。
ーー
SUMPRODUCT関数では
=SUMPRODUCT((A1:A100<>"")*(A1:A100))
ーーー
配列数式では
例データ
1
2
3
4
a
d
12
の場合、字列があってもSUMの場合は
=SUM(IF(A1:A100<>"",A1:A100),0)と入れて、SHIFT+CTRL+ENTER(3つのキーを同時押し)で22と出る。
    • good
    • 0
この回答へのお礼

ありがとうございます。
できました。

「SHIFT+CTRL+ENTER」って必要なのでしょうか?
なくても22になりました。

お礼日時:2009/06/28 20:14

データが連続しているなら、次の方法もあります。

大して意味はありませんが……

=SUM(INDIRECT("A1:A"&COUNT(A:A)))
    • good
    • 1
この回答へのお礼

mshr1962さんの=SUM(OFFSET($A$1,,,COUNT($A$1:$A$100)))と同意味ですね!
ありがとうございます。参考になりました。

お礼日時:2009/06/28 19:00

1行目から最終行までの集計は回答No1の方法でできますし簡単です。

=SUM(A:A)の方法ですとA列に文字列があった場合でもそのセルは集計されませんし、数値のセルの間に空きのセルがあった場合でも集計されます。
逆にA3セルから最終セルの集計ではA1セルからA2セルまでに数値が入力されている場合にはその数値も集計されますので、次のような式になりますね。
=SUM(A:A)-SUM(A1:A2)
    • good
    • 0
この回答へのお礼

こういう使い方もできるのですね。
参考になりました。

お礼日時:2009/06/28 18:57

1行目から最終行までなら


=SUM(A:A)
が簡単です。
    • good
    • 1
この回答へのお礼

やっぱこれしかないですかね、、、
ありがとうございます。

お礼日時:2009/06/28 17:46

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

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

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

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

Qエクセル:データが入った最終行の行番号の取得

お世話になります。
A列にいろいろデータが入っているとして、データが入っている一番下(最終行)の行番号をB1のセルに表示させるにはどういう計算式で出来るでしょうか?
A列のデータは数値だけでなく文字列も含みます。また途中空白セルもあります。
A1~A20にデータが入っているときはB1に20と表示させる。
よろしくお願いします。

Aベストアンサー

=MAX(IF(COUNTIF(A:A,"*"),MATCH("",A:A,-1)),IF(COUNT(A:A),MATCH(MAX(A:A)+1,A:A,1)))

Qエクセルで最後の行までを選択

エクセルで1万行位あるものの1行目から最後の行までを一気に選択する方法はあるでしょうか?また、同じようにA列とB列のみを最後の行まで選択する方法もあるでしょうか?よろしくお願いします。

Aベストアンサー

こんにちは。

マクロなどが設定されていなければ、

シフトキーを押しながら、A1 の、セルの四角の底辺の枠線をダブルクリックすれば、その列が最後まで選択されます。A列B列のみなら、A列で、シフトキーを押しながら、B列のB1 のセルの四角の枠線をダブルクリックすれば選択されます。(ただし、空白セルが途中にあったら、そこでとまります。)

シフトキーを押しながら
+-------------+
|            |
|            |
+-------------+
   ↑
   ここをダブルクリック

QEXCEL 最終行のデータを他のセルに参照したい

1ヶ月の集計表があります。
1日○○○人
2日○○○人
3日○○○人
4日○○○人
5日(空白)
6日(空白)



という毎日の該当者数の日報があります。
最後に入力されている人数(この場合4日の人数)を使って
別シートで計算をしたいのですが、
どうやって抽出(指定?)すればいいでしょうか?

簡単に言うと、
上から順に入力された最後の行のデータを
他のセルに参照する方法(関数)は?
ということになるでしょうか?

簡単なようですが関数が思いつきません。
マクロが必要なんでしょうか?

Aベストアンサー

たとえば、列Aに日付、列Bに値の表だとしたら、
B列の最終行の値を参照する式は以下のとおり。
(※ただしB列は連続してデータが存在している前提)

=OFFSET(B1,COUNTA(B:B)-1,0)

OFFSET関数は基準になる位置から
指定された行数、列数の位置のセルを参照する関数です。
この例の場合は、基準の位置がB1、基準の位置B1から
B列のデータ個数-1行目を参照させる式になっています。

ヘルプもご確認ください。

Qエクセルで、条件に一致した行を別のセルに抜き出す方法

エクセルで、指定した条件に一致するセルを含む行をすべて抜き出す方法が知りたいです。

たとえば、

<A列> <B列> <C列>
7/1 りんご 100円
7/2 ぶどう 200円
7/2 すいか 300円
7/3 みかん 100円

このような表があって、100円を含む行をそのままの形で、
別のセル(同じシート内)に抜き出したいのですが。

7/1 りんご 100円
7/3 みかん 100円

抽出するだけならオートフィルターでもできますが、
抽出結果を自動的に、別の場所に、常に表示させておきたいのです。

初歩的な質問だと思いますが、検索しても分からなかったので、よろしくお願いします。

Aベストアンサー

同じ質問が結構よく出てますが、そんなに初歩的でもありません
別シートのA1セルに「100円」と入力し、そのシートの任意のセルに以下の式を貼り付けて下さい。後は、下方向、右方向にコピー。
日付のセル書式は「日付」形式に再設定してください

=IF(COUNTIF(Sheet1!$C:$C,$A$1)>=ROW(A1),INDEX(Sheet1!A:A,LARGE(INDEX((Sheet1!$C$1:$C$500=$A$1)*ROW(Sheet1!$C$1:$C$500),),COUNTIF(Sheet1!$C:$C,$A$1)-ROW(A1)+1)),"")

データ範囲は500行までとしていますが、必要に応じて変更して下さい

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すると関係ないセルの値が返るので、
場...続きを読む

QExel VBA 別ブックから該当データを検索し、必要なデータを取得する方法について

部品表というブックがあります
A列に商品名、B列に商品番号が入力してあります。C列のコードは未入力です。
A列     B列     C列      
商品名  商品番号  コード
モータ  U-1325-L  
ホルダ  R-134256

また、コード一覧表という別のブックには、A列に商品番号と、B列にコードが、何千件も入力されています。

やりたいことは
部品表のC列のコード欄に、コード一覧表ブックから商品番号と一致するコードを貼り付けしたいのです。

部品表は、何百種類もありますので、関数ではなく、マクロで処理を希望します。

自分では、部品表の商品番号をコピーして、コード一覧表で検索し、検索結果の右隣のセル(B列のコード)の値を部品表のC列に貼り付ければよいかと思い、書いてみたんですが…

Sub 別ブックから貼り付ける()
  Dim 検索する As Long
Windows("部品表.xls").Activate
検索する = cells(i,2).Value
Windows("コード一覧表.xls").Activate
ActiveWindow.SmallScroll Down:=-3
Selection.AutoFilter Field:=3, Criteria1:="=検索する", Operator:= xlAnd

と、してみたものの、検索しても、その検索結果の隣のセルのコードをどうやって取得すればいいのかが、わかりませんでした。

基本事項は本で学びましたが、呪文のようなコードはよく理解できません。懸命にネットで検索して、訳して理解する努力をしてはいますが。

どうぞよろしくお願いします。

部品表というブックがあります
A列に商品名、B列に商品番号が入力してあります。C列のコードは未入力です。
A列     B列     C列      
商品名  商品番号  コード
モータ  U-1325-L  
ホルダ  R-134256

また、コード一覧表という別のブックには、A列に商品番号と、B列にコードが、何千件も入力されています。

やりたいことは
部品表のC列のコード欄に、コード一覧表ブックから商品番号と一致するコードを貼り付けしたいのです。

部品表は、何百種類もありますので、関数...続きを読む

Aベストアンサー

こんにちは。
とりあえず実用性も踏まえました。
メインの動作はワークシート関数のVLOOKUPをVBA上で使用していますので理解はしやすいかと思います。
また、質問文から察するに「部品表.xls」と「コード一覧表.xls」の両方を開いて処理されていますが「コード一覧表.xls」はプログラム内で開いて閉じているので実行するときは「コード一覧表.xls」は閉じて置いてください。
Option Explicit
Sub Sample()
 Application.ScreenUpdating = False
 Dim I As Long
 Dim xlBook
 Set xlBook = Workbooks.Open("C:\★★\コード一覧表.xls") '★要変更★
 I = 2
 Do While Range("A" & I).Value <> ""
  ThisWorkbook.Worksheets("Sheet1").Range("C" & I).Value = Application.VLookup(ThisWorkbook.Worksheets("Sheet1").Range("B" & I).Value, xlBook.Worksheets("Sheet1").Range("A2:B65535"), 2, 0)
  I = I + 1
 Loop
 xlBook.Close
 Application.ScreenUpdating = True
 MsgBox ("完了")
End Sub

こんにちは。
とりあえず実用性も踏まえました。
メインの動作はワークシート関数のVLOOKUPをVBA上で使用していますので理解はしやすいかと思います。
また、質問文から察するに「部品表.xls」と「コード一覧表.xls」の両方を開いて処理されていますが「コード一覧表.xls」はプログラム内で開いて閉じているので実行するときは「コード一覧表.xls」は閉じて置いてください。
Option Explicit
Sub Sample()
 Application.ScreenUpdating = False
 Dim I As Long
 Dim xlBook
 Set xlBook = Workbooks....続きを読む

Q別のシートから値を取得するとき

Worksheets("シート名").Activate
上記のを行ってから別シートの値を取得するのですが、
この処理を行うと指定したシートへ強制的にとんでしまいます。。。

※イメージ
For ~ To ~
  Worksheets("シートA").Activate
  シートAの値取得
       :
  Worksheets("シートB").Activate
  シートBの値取得
Next

このイメージ処理を行うとものすごい勢いで画面がチカチカします。。。
シートを変えずに他のシートから値を取得する方法はないのでしょうか。
教えてください!

Aベストアンサー

Worksheets("シートA").Range("A1")

みたいな感じでできませんか?

Qエクセルマクロ オートSUM(合計)式の挿入

データの最下行の下にエクセルのマクロで列の合計(オートSUM)を挿入したいです。
毎作業ごとに最下行が異なるので、「マクロの記録」では上手くいきません。
(毎回式の選択範囲を変えなければならなくなるため)
ご教授頂きたく、よろしくお願いします。

Aベストアンサー

一例です。
C列の最終行にSUM関数を設定しています。
endrow = Cells(Rows.Count, "C").End(xlUp).Row
Cells(endrow + 1, "C").Formula = "=sum(C1:C" & endrow & ")"

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

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

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

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

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

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

宜しくお願いします。

Aベストアンサー

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

でどうでしょうか?

QVBAで複数の数式セルを最終行までコピーするには?

エクセルで下記のような表を作成しています。

   A   B   C   D
1 項目1 数式 数式 数式
2 項目2
3 項目3
  ・
  ・
  ・

B1~D1の数式は項目1を参照したものです。
この時、2行目以下~最終行まで数式をコピーするには、
どのようなVBAを書けばよいでしょうか?

よろしくお願い致します。

Aベストアンサー

Range("A1", Cells(Rows.Count, 1).End(xlUp)).Offset(, 1).Resize(, 3).Formula = Range("B1:D1").Formula

とか?


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

このカテゴリの人気Q&Aランキング