親子におすすめの新型プラネタリウムとは?

Excelのオートフィルタにて
(1)空白セルを抽出し、その数をカウントする
(2)空白セル以外を抽出し、その数をカウントする
を同時に行いたいのですが、
どの様な数式を使えばよいでしょうか。

(2)はSUBTOTAL関数で引数をCOUNTA関数にすれば出来ますが、、

どなたか、ご教授願います。

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

A 回答 (3件)

表の範囲がA1からA10と仮定した場合



(1) =subtotal(5,a1:a10)

(2) =subtotal(3,a1:a10)
    • good
    • 0
この回答へのお礼

お礼が遅れてしまい、申し訳ありません。
回答ありがとうございました。

(1)、(2)ともに単独では、その数式でよいと思いますが
今回は「同時」に行いたいということでした。。

お礼日時:2006/02/28 09:08

オートフィルタで抽出対象範囲を A2:A20(A1 は項目名)と仮定するなら、次式でよろしいかと。


=IF(SUBTOTAL(3,A2:A20),SUBTOTAL(3,A2:A20),COUNTBLANK(A2:A20))

この回答への補足

お礼文内の訂正です。。

「合格」 カウントOK
「不合格」 カウントOK
「(セルが)空欄」 カウントOK
「フィルタリングなし」カウントNG:「合格」+「不合格」 数をカウント
※フィルタリングしないときには
「合格」+「不合格」+「(セルが)空欄」の総数がカウント出来れば、OKです。

補足日時:2006/02/28 09:51
    • good
    • 0
この回答へのお礼

お礼が遅れてしまい、申し訳ありません。
回答ありがとうございました。

項目として、「合格」「不合格」「空欄」があります。
教えて頂いた数式では
「合格」 カウントOK
「不合格」 カウントOK
「空欄」 カウントNG:トータル数をカウント
「フィルタリングなし」カウントNG::空欄の数をカウント
となり、一部うまいくいきませんでした。

お礼日時:2006/02/28 09:06

こんなのは、どうでしょうか?個数を求めたいセルの数式です。

データ範囲をa2:a13と設定したばあいです。

=IF(COUNTBLANK(A2:A13)=0,SUBTOTAL(3,A2:A13),COUNTBLANK(A2:A13))

フィルターで「空白」を選んだら、「COUNTBLANK関数」によって空白数を数え、それ以外を選んだら「SUBTOTAL関数」で数える・・・。
    • good
    • 0
この回答へのお礼

お礼が遅れてしまい、申し訳ありません。
回答ありがとうございました。
項目として、「合格」「不合格」「空欄」があります。

教えて頂いた数式では、うまくいきませんでした。。
(トータル数は正しいのですが、フィルタリングしても個数が
変わりませんでした。)

お礼日時:2006/02/28 09:05

このQ&Aに関連する人気のQ&A

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

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

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

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

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

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

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

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

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

Aベストアンサー

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

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

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

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


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

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

Q=SUBTOTAL に =COUNTIF の機能を実装

お世話になります。

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


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

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

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

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

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

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

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

お世話になります。

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


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

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

ようは、その表全体にフィルタを設定しており、いくつかの条件で抽出した時に「完了」の数が変わるので
現状 自分が思いつく所で考えると別シートに移して「=COUNTIF(●:●,"...続きを読む

Aベストアンサー

またまたまた登場、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,"連絡済")

要するにふつうの関数と同じような使い方になります。
以上です。
 

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

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

'---------------------------------------------- 
Function Ookami(myRange As Range, myStr...続きを読む

Qオートフィルタの選択肢数を数えたいのですが・・・

エクセルについて質問です。

オートフィルタを使用しますと、プルダウン式に
選択肢が一覧で出ますけれど、その数を数えるには
どうしたらよいのでしょうか?

例えば出荷一覧を見る場合など、
出荷日でソートしてみるのですが、
その日に動いたのはいったい何アイテムなのか、
ということを知りたいのです。

プルダウンで出る候補をいちいち数えるしか
方法はないのでしょうか?

どなたか、何とぞご教授ご指導のほど、よろしくお願いいたします。

Aベストアンサー

No.2です。
すみません、質問の意図を誤って解釈していました。
私の知識では、オートフィルタを使用して重複を除いた個数を数える方法は思いつきませんでした。

ちょっとややこしいですがフィルタオプションを使用した以下の方法はいかがでしょうか。

A列に出荷日、C列にアイテムが入力されているとします。
A列のタイトルが「出荷日」となっていると場合、抽出条件としてD1に「出荷日」、D2にフィルタリングしたい出荷日を記入しておきます。

この状態で、A列全体とC列全体を選択して、「データ」>「フィルタ」>「フィルタオプションの設定」の画面で、「検索条件範囲」に$D$1:$D$2を指定し、「重複するレコードは無視する」にチェックを入れ、OKを押します。
こうすると、D2のセルで指定した条件の日付のアイテムが重複なしに表示されるので、下のステータスバーを見れば抽出件数がわかります。

ただ、条件を変える(抽出する日付を変える)には、いちいち「データ」>「フィルタ」>「すべて表示」で元の表示に戻して、検索値(今の例ではD2)の内容を変えて同じことを繰り返さないといけないので、ちょっと面倒です。一連の流れをマクロにすれば簡単かもしれません。

一応、今の例をマクロにしてみたら、以下のようになりました。ボタンを配置してマクロを実行できるようにしてみたら、比較的簡単に抽出ができると思います。

Sub 抽出()
Range("A:A,C:C").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("D1:D2"), Unique:=True
End Sub

Sub 全て表示()
ActiveSheet.ShowAllData
End Sub

No.2です。
すみません、質問の意図を誤って解釈していました。
私の知識では、オートフィルタを使用して重複を除いた個数を数える方法は思いつきませんでした。

ちょっとややこしいですがフィルタオプションを使用した以下の方法はいかがでしょうか。

A列に出荷日、C列にアイテムが入力されているとします。
A列のタイトルが「出荷日」となっていると場合、抽出条件としてD1に「出荷日」、D2にフィルタリングしたい出荷日を記入しておきます。

この状態で、A列全体とC列全体を選択して、「...続きを読む

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エクセルで重複しているデータの抽出のしかたを教えてください。

エクセルで重複しているデータを抽出したいのですが・・。なにぶん初心者なもので簡単な方法があればよいのですが・・。
A列に日付 B列に企業名このデータが300件ほどあります。

 Å列      B列
1月 1日    AAAA社
1月 4日   BBBB社
1月 8日   CCCC社
2月 1日   BBBB社
2月20日    DDDD社
3月 2日   AAAA社

と、あるとしてB列の企業名が重複しているものを抽出したいのですが・・・。この場合 AAAA社とBBBB社ですが。
これを簡単に抽出する方法はないでしょうか?
教えてください お願いいたします。

Aベストアンサー

企業名がセルB2からセルB300まで入力されていると仮定します。
セルC2に IF(COUNTIF($B$2:B2,B2)>1,"*","") を入力し、セルC300までコピーして下さい。すると重複したデータの二番目以降に "*" のマークが付きます。
(注) 数式を下のセルまでコピーするため、COUNTIF($B$2 は絶対参照にして下さい。

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

Qエクセル 同じ値を探して隣の数値をコピーする

エクセルで以下の作業を簡単にする方法を教えて下さい。

Sheet1の1行目には見出しがあり、A列とB列の2行目から下にデータが入っています。(約200行)
A列     B列
田中    13
山本     8
谷口    11
鈴木    6
佐々木    9
奥村     15




Sheet2のA列1行目から下には別のデータが入っています。(約600行)
A列
太田川
山村
田中
多賀先
鈴木
奥村
幸田




Sheet2のA列のデータと完全一致するデータ(名前)をSheet1のA列から探して、同じ名前があれば、その隣のB列にある数値をSheet2のB列に貼り付ける。
見つからない場合はSheet2のB列は空欄のままです。
Sheet2
A列      B列
太田川     
山村
田中    13
多賀先
鈴木     6
奥村    15
幸田


Aベストアンサー

シート2のB2セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNTIF(Sheet1!A:A,A2)=0,"",VLOOKUP(A2,Sheet1!A:B,2,FALSE))

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エクセル オートフィルタの件数表示が出ません

私の父の問題なのですが、エクセルのオートフィルタを使ったときに、普通なら下のほうに「○件中○件みつかりました」と表示されるところ、表示されないそうです。どなたか表示されない理由、表示させるにはどうしたらいいか分かる方いらっしゃいませんか?(XP使用です。)

Aベストアンサー

こんばんは。

マイクロソフト技術情報にそれについての記述があります
Excel5.0と書いてありますが最新のでも同様だと思いますので
下記参考URLを覗いてみて下さい。

参考URL:http://support.microsoft.com/default.aspx?scid=kb;ja;402542


人気Q&Aランキング