出産前後の痔にはご注意!

お世話になります。

掲題の件で ご相談が御座います。


=SUBTOTAL(3,U5:U63) と
=COUNTIF(U5:U63,"完了") を 1つのセルで表現したいのですが
これは不可能でしょうか?

本当は「U列」だけではなく、20~30列全部に そのような機能を持った関数を入れたいのですが
両方を兼ね備えた関数を どなたかご存知ないでしょうか。

ようは、その表全体にフィルタを設定しており、いくつかの条件で抽出した時に「完了」の数が変わるので
現状 自分が思いつく所で考えると別シートに移して「=COUNTIF(●:●,"完了")」を都度入れていくか?
ぐらいしか思いつきません。

・フィルタで抽出しても元の範囲選択はそのまま。
・フィルタで抽出後に見えている選択範囲の「完了」だけを数えて欲しい
・上記2つの条件で1つのセルに数式を入力したい。

この3つの条件が満たされれば「=SUBTOTAL」や「=COUNTIF」にこだわる必要はありません。

どなたか ご存知であればご教示いただけますでしょうか。

説明が不足のようでしたら何度でも補足致しますので何卒宜しくお願い致します。

このQ&Aに関連する最新のQ&A

A 回答 (8件)

またまたまた登場、myrangeです。



質問者がマクロをどの程度使えるのか分からないのでなんですが、
(質問から推測するにあまり使ったことがない?)
先に提示したマクロは関数ではありませんから
データの内容が変るたびに(結果を求めたい時点で)実行しなければいけません。
もし、SUBTOTALのような関数(セルに式としてセットする)にしたければ
以下のようなユーザー定義関数を作ることになります。

'---------------------------------------------- 
Function Ookami(myRange As Range, myStr As Variant) As Long
 Dim Rng As Range
 Dim Cnt As Long
 For Each Rng In myRange
  If Rng.EntireRow.Hidden = False And Rng.Value = myStr Then
    Cnt = Cnt + 1
  End If
 Next Rng
 Ookami = Cnt
End Function
'---------------------------------------------

使い方は、以下のようにします。
関数名は質問者のハンドルネームです。。(^^;;;

■Ookami(セル範囲, 検索文字)■

=Ookami(U5:U63,"完了")

F5~F666の範囲で、表示されてる"連絡済"をカウントしたければ

=Ookami(F5:F666,"連絡済")

要するにふつうの関数と同じような使い方になります。
以上です。
 
    • good
    • 3
この回答へのお礼

何度も ご回答いただきまして
誠にありがとう御座います。

やはりVBAにほぼ不可能は無いような感じですね。

こういった事を聞かずにやれるようになりたいものです。。

気に掛けていただきましてありがとう御座いました。

お礼日時:2009/07/29 10:04

VBA案が順当だとは思いますが、数式一発でもできなくはないので一応…。



●セル範囲U3:U63について、
 「値が"完了"であり」かつ「フィルタで表示されている」セルの数。

 =SUMPRODUCT(SUBTOTAL(3,OFFSET(U2,ROW(INDIRECT("1:"&ROWS(U3:U63))),))*(U3:U63="完了"))

※U2を参照している部分は、対象範囲の直上セルを指定してください。

以上ご参考まで。

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

ご回答誠にありがとう御座います。

結論「マクロは嫌」と言われてしましまして、また 急いでいたこともあり
_Kyleさんの回答を見る前に作業列を使って解決してしましました。。。
もったいないことをしました。

いただいた数式の中身を理解し、別の機会に利用させていただこうと思います。

ご対応いただきましてありがとう御座いました。

お礼日時:2009/07/29 10:08

作業列1列使います。

作業列V列として
V5セルに =(SUBTOTAL(3,U5))
V63行までオートフィル(括弧をつけてください)
フィルタによる表示/非表示の検査となります

結果を求める数式は
=SUMIF(U5:U63,"完了",V5:V63)
    • good
    • 6
この回答へのお礼

ご回答誠にありがとう御座います。

やはり作業列を使う方が良いようですね。

ご提示いただきました数式も参考にさせていただきます。

ご対応ありがとう御座いました。

お礼日時:2009/07/29 10:01

こんばんは!


まったくの的外れになるかもしれませんが・・・

どうしてもオートフィルタを使わないとダメですか?

無理矢理って方法になります。

↓の画像のようにオートフィルタを使わずに
条件だけを入力すれば
その条件に合ったセルの個数と「完了」のセルの個数を
2段で表示させる方法になるのですが、

画像の場合は「ハ」の個数と「完了」個数を表示しています。

B21セル =SUMPRODUCT(($A$2:$A$19=$A$21)*(B$2:B$19<>""))
B22セル =SUMPRODUCT(($A$2:$A$19=$A$21)*(B$2:B$19=$A$22))

として、B21・B22セルを範囲指定した後にオートフィルで列方向にコピーしています。

この程度の回答しか思い浮かびませんでしたが、
的外れなら軽く読み流してくださいね。m(__)m
「=SUBTOTAL に =COUNTIF」の回答画像5
    • good
    • 0
この回答へのお礼

ご回答 ありがとう御座います。

フィルタを使う必要が御座いますので この方法はちょっと。。。

でも、いつも いろいろと工夫をされているtom04さんの姿勢はいつも関心させられます。
自分も見習いたいと思います。

ありがとう御座いました。

お礼日時:2009/07/26 18:36

またまた登場、myRangeです。


マクロでやりたいということなので。。。
コードの書き方はいくつかありますが、簡単なのを2つほど。

結果は、セルU1に表示するものとした場合。

'--------------------------------------- 
Sub Test222()
 Dim Cnt As Long
 Dim Rng As Range
 Dim myRange As Range

 Set myRange = Range("U5", Cells(Rows.Count, "U").End(xlUp))

 For Each Rng In myRange.SpecialCells(xlCellTypeVisible)
   If Rng.Value = "完了" Then
     Cnt = Cnt + 1
   End If
 Next Rng

 Range("U1").Value = Cnt
End Sub
'------------------------------------------------

Sub Test333()
 Dim R As Long
 Dim Cnt As Long

 For R = 5 To Cells(Rows.Count, "U").End(xlUp).Row
   If Cells(R, "U").Value = "完了" And _
   Cells(R, "U").EntireRow.Hidden = False Then
       Cnt = Cnt + 1
   End If
 Next R

 Range("U1").Value = Cnt
End Sub
'---------------------------------------------


それから、お礼のコメントに

>やはり作業列を追加する以外方法はないのでしょうか。。。
>現状 このシートは「DP列」までデータが入っておりまして
>そろそろ限界に近づいております。。。

とありますが、DP列は最大列の半分(xl2003まで)しか使ってないですが、
それがなぜ、そろそろ限界に近づいて、となるのでしょうか。
ちょと疑問。

以上です。
    • good
    • 0
この回答へのお礼

再登場 誠にありがとう御座います。

また、VBAのコードまでいただきましてありがとう御座いました。

結論から言うと「マクロは嫌」と言われてしまいまして
関数で作業列を使ってやる事になってしまいました。。。

業務用共有資料ではなく 個人の作業用に活用させていただこうと思います。

わざわざ ありがとう御座いました。

お礼日時:2009/07/29 10:00

エクセルのバージョンは何でしょうか?


当方Excel2002では
=SUBTOTAL(3,U5:U63)
を入力しているのセルの値は、オートフィルタの結果によりご質問の希望通りに変化します。
    • good
    • 0
この回答へのお礼

ご回答誠にありがとう御座います。

言葉足らずですみませんでした。。。

Excelは2003です。

あと、各列には「完了」と「未完了」が混在しており、フィルタの抽出条件により
都度見えている画面上「完了」と「未完了」の数が変わってきます。

なので「=SUBTOTAL」だけだと 完了、未完了の両方の合計が出て来ますので苦労しております。

何か良い方法を ご存知でしたら是非お願い致します。

お礼日時:2009/07/24 17:19

作業列を使わないなら、マクロになるでしょう。



作業列を使う方法(作業列をV列とする)

V5に、
 =(U5="完了")*1
と式をいれ、V63までコピーする

●求める式は、

=SUBTOTAL(9,V5:V63)

となる。
作業列のV列が邪魔なら非表示にしておく。


尚、マクロなら簡単にできますが。。。
以上です。
 
    • good
    • 0
この回答へのお礼

ご回答誠にありがとう御座います。

やはり作業列を追加する以外方法はないのでしょうか。。。

現状 このシートは「DP列」までデータが入っておりまして
そろそろ限界に近づいております。。。

もし よろしければマクロでの解決方法を ご教示いただけませんでしょうか。

お忙しい中 大変 恐縮では御座いますが
何卒 宜しくお願い致します。

お礼日時:2009/07/24 17:25

>・フィルタで抽出後に見えている選択範囲の「完了」だけを数えて欲しい



オートフィルターなら、この後、U列の▼クリックして「完了」...で良いのでは?
    • good
    • 0
この回答へのお礼

ご回答ありがとう御座います。

すみません。
言葉が足りなかったようです。

「フィルタで抽出後に見えている選択範囲の「完了」だけを数えて”表示させて”欲しい」
のです。

何か方法をご存知ないでしょうか。

宜しくお願い致します。

お礼日時:2009/07/24 15:30

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

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

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

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

Qオートフィルタ使用時にCOUNTIFをSUBTOTALのように使いたい。

エクセルのシートでデータ管理をしようと思います。
その時便利なのが、オートフィルタですが、

   SUBTOTAL関数「例:=SUBTOTAL(9,A1:A100)」

を設定した場合、フィルタを操作すると、必要な数値が返ってきます。ところがというか当然というか、

   COUNTIF関数「例=COUNTIF(B1:B100,"○")

ではだめですよね。どうにかして関連づける方法はないでしょうか。
または、その他に同様な効果が得られる方法はありませんか。

Aベストアンサー

回答がありませんね。
質問の意味は、
  例えば、101行目にSubtotalを置いていればフィルタをかけたときに選択されたものだけが集計される。
  しかしCountifのように条件付きの集計はできないので代替手段は?
ということでしょうか。

多分、Subtotalの集計方法はフィルタで絞られたものを対象にするので、さらにそれを絞り込むことは難しいと思います。

>その他に同様な効果が得られる方法はありませんか
ということなので、機能が無ければ作ればいいという事で下記のユーザー定義関数を書いてみました。
フィルタで絞る時は、該当しない行は自動的に非表示になるのでそれを利用しています。

使い方は、COUNTIF(B1:B100,"○") と同じようにsubtotal_countif(B1:B100,"○") とします。

標準モジュールに貼り付けます。
Public Function subtotal_countif(rgSelect As Range, moji As String)
  Dim rg As Range 'セル
  Dim cot As Long 'カウンタ

  For Each rg In rgSelect
    If Rows(rg.Row).Hidden = False Then '表示されている行だけ対象にする
      If rg = moji Then
        cot = cot + 1
      End If
    End If
  Next
  subtotal_countif = cot
End Function

回答がありませんね。
質問の意味は、
  例えば、101行目にSubtotalを置いていればフィルタをかけたときに選択されたものだけが集計される。
  しかしCountifのように条件付きの集計はできないので代替手段は?
ということでしょうか。

多分、Subtotalの集計方法はフィルタで絞られたものを対象にするので、さらにそれを絞り込むことは難しいと思います。

>その他に同様な効果が得られる方法はありませんか
ということなので、機能が無ければ作ればいいという事で下記のユーザー定義関数を書いて...続きを読む

Qエクセルでフィルタ後の条件付き個数のカウント

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

エクセル(2007)で作成した表をフィルタ後に条件に合致する個数をカウントする方法を教えてください。
(例)
A列:名前
B列:出身県のデータがあったとします。

名前でフィルタをかけた後、出身県ごとのカウントを行いたいのですが、条件が無い場合のデータ個数はSUBTOTAL関数を使えばOKなのはわかります。
COUNTIFS関数を使うと、フィルタで隠れた行のデータまでカウントしていまいます。

ピボットを使う(使い方がイマイチわかりませんが・・・)という方法もあると思いますが、今回は同一シート無いに関数でカウントしたいのです。

どなたかよろしくお願いいたします。

Aベストアンサー

=subtotal(3,a2:a3000)
あるいは
=subtotal(3,b2:b3000)
あるいは他の列

※空白セルは個数にカウントされないことに注意して、任意の列を選択

といった数式を、「フィルタで絞り込んだ際に非表示にならない位置」(表の最上行より上とか、最下行より下とか、別シートとか)に記入。その後、A 列で絞り込み、同時に B 列でも絞り込み。つまり、2 列で絞り込み。

以上により、A 列と B 列の AND 条件における個数が数式を記入したセルに表示されているはず。


>ピボットを使う(使い方がイマイチわかりませんが・・・)……

慣れ。やってみれば、意外とできます。たくさん使ってみてください。

QExcel関数:SUBTOTALとSUMIFを組み合わせる?

下記のような場合の集計ができる関数を教えて下さい。
SUBTOTALとSUMIFを組み合わせようと思ったのですが、うまくいきませんでした。
よろしくお願いします。

<表の内容>
・セルA1:「ランク」
・セルA2~A50:「A」or「B」or「C」を入力している
・セルB1:「金額」
・セルB2~B50:各金額を入力している

<集計方法>
・セルB51に「ランクが"A"」の金額合計を表示したい
・ただしオートフィルターを使って他の条件で抽出もおこなっているのでSUBTOTAL関数のように、表示されている行の値のみを合計したい

Aベストアンサー

オートフィルタ実行時でも可能な集計です。

=SUMPRODUCT((SUBTOTAL(3,INDIRECT("A"&ROW(A2:A50))))*(A2:A50="A")*(B2:B50))

で試してみてください。
B51セルだとフィルタ実行時に非表示になるかもしれませんので、もう少し下のセルに入れてください。

Q検索条件に合うセルの個数を数えたい

EXCELで顧客管理表を作っています。

顧客データ一覧の特定の項目の中から条件にあうセルがいくつあるかを数えて
同シート内に個数を表示させたいと思っているのですが、うまくいきません。
(今回の場合は項目が「支店」で、○支店を含むデータが何個、×支店を含む
データが何個…といった感じです)

また顧客データは毎日追加していき、オートフィルタを使った場合には表示
されているデータの中から検索、という形が希望です。

COUNTIF関数を使ってみたのですが、オートフィルタをかけた場合でも
全てのデータから検索されてしまうので、もし他の関数もしくは方法を
ご存知でしたら教えてください。

よろしくお願いします。

Aベストアンサー

作業列(仮にA列とします)を追加して、A2に以下の式を入力します。

=SUBTOTAL(3,B2)

B2でなくても、データがある列ならよいです。
下にコピーします。

SUBTOTAL関数は非表示セルを無視しますので、A列は表示されていれば1、非表示なら0となっています。

あとはSUMPRODUCT関数の条件の一つとしてA列を加えれば、表示されているセルのみの計算結果が得られます。

=SUMPRODUCT((条件列範囲="○支店")*A列範囲)

なお、A列自体は非表示でも構いません。

Q可視セルを対象としたcountifができるでしょうか?

オートフィルターで表示してある所だけを対象としてcountifで対象
値の数を出したいのでのですが
できますでしょうか?

Sub countif()
Range("C1") = Application.WorksheetFunction.countif(Range("A:A").SpecialCells(xlCellTypeVisible), Range("B1"))
End Sub

イメージはこんな感じなのですがよろしくお願いします。

Aベストアンサー

できないようなので、ユーザー定義関数を作ってみました。

しかし、このやり方だと可視セルを一つ一つチェックしていくので
とても時間がかかります。

時間短縮のために
Range("A:A")をRange("A1").CurrentRegion.Resize(, 1)
に変更しています。


Sub test()
  Dim myRange As Range
    
  Set myRange = Range("A1").CurrentRegion.Resize(, 1).SpecialCells(xlCellTypeVisible)
'  Set myRange = Range("A:A").SpecialCells(xlCellTypeVisible)

  Range("C1").Value = myCountIF(myRange, Range("B1").Value)

  Set myRange = Nothing
End Sub

Public Function myCountIF(argRng As Range, arg2 As String) As Long
  Dim myCell As Range
  
  Application.Volatile
  myCountIF = 0
  For Each myCell In argRng
    If myCell.EntireRow.Hidden = False Then
      myCountIF = myCountIF + Application.WorksheetFunction.countif(myCell, arg2)
    End If
  Next
  
  Set myCell = Nothing
End Function

できないようなので、ユーザー定義関数を作ってみました。

しかし、このやり方だと可視セルを一つ一つチェックしていくので
とても時間がかかります。

時間短縮のために
Range("A:A")をRange("A1").CurrentRegion.Resize(, 1)
に変更しています。


Sub test()
  Dim myRange As Range
    
  Set myRange = Range("A1").CurrentRegion.Resize(, 1).SpecialCells(xlCellTypeVisible)
'  Set myRange = Range("A:A").SpecialCells(xlCellTypeVisible)

  Range("C1").Value = myCoun...続きを読む

Qオートフィルタを使っている状態で特定の文字のセルをカウントしたい。

大学の先輩の統計を手伝おうと思い、昨日からエクセルを勉強しています。

質問なのですが、オートフィルタの状態で、Hの列の陽性という文字をカウントしたいのです。この列には陽性か空白しかありません。陽性はFの列が0以上であれば陽性とします。
しかし、SUBTOTALの3を用いると、このHの列には=IF($F$10:$F$1000>0,"陽性","")があるせいなのか、空白になっているところもカウントしてしまいます。
どうしたらよいのでしょうか?どなたかアドバイスをお願いします。

Aベストアンサー

””も空白文字としてカウントします。
引数3のSUBTOTALは、COUNTAとほぼ同じ働きをするからです。
1列右に作業列を設け、=(H3="陽性")*1 として
I2に =SUBTOTAL(9,I2:I1000) としてみてはいかがでしょうか。

Q関数で可視セルのデータ個数カウント

Excel2002を使用しています。
オートフィルタで抽出したデータの個数を関数で
カウントしたいのですが、可視セルのみをカウント
出来る関数はあるのでしょうか?

例えば

  A
1 件数
2  2
3  1
4  3
5  1
6 関数


A6セルに何らかの関数を入れておき、
A列のオートフィルタを使って、1を抽出した時は
A6に”2”
3を抽出した時はA6に”1”を表示させたいの
です。
このように、可視セルのデータ個数のみを
カウントする関数はありますでしょうか?

フィルタを使わずにCOUNTIF関数でカウントする
方法、また、可視セルをコピー貼り付け後
カウントする方法は避け、あくまでフィルタで
抽出したデータの個数を瞬時にカウントしたいの
です。
ご教授宜しくお願いします。

Aベストアンサー

=subtotal(2,範囲)

A6だとオートフィルタで消されちゃうので1行目に行挿入してA1にする方が良いです。

Qエクセルでの指定文字 カウントについて

エクセルで並んだデータでの指定した名前だけの個数をカウントするにはどうすればいいのでしょうか?

山田 高橋 佐藤
高橋 梅田 赤田
 西 山田 梅田
佐藤 山田 梅田

名前が並んだデータで「高橋」という名前が何個あるのかをカウントしたいのですがどうすればいいのでしょうか?

Aベストアンサー

 データは入力されているセルの範囲を「A1:C4」とすれば、

=COUNTIF(A1:C4,"高橋")

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

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

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

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

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

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

宜しくお願いします。

Aベストアンサー

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

でどうでしょうか?

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&Aを見た人がよく見るQ&A

人気Q&Aランキング