B20:AY120の 50列100行 の表で、1~40列で何らかのフィルターをかけた時、41~50列の特定の内容の数量(個数)を一気に見たいのですが、何か良い方法はないでしょうか?
1~40の列にはいろいろな項目に対する内容が書かれていて、41~50の列では、1.2.3.”--”.及び空白となっています。1~40列で何らかのフィルターをかけた時、41~50列の表に出てきた分(フィルターで隠れていない分)の内、1の数量、2の数量、3の数量、および”--”の数量を常時表に出したいのですが何か良い方法がないでしょうか?
51列目の各行に1を入れ、SUBTOTAL(9,VS21:VS10000)の表からフィルターをかけた時の数量を特定し、マクロで出す方法はあるのですが、時間がかかりすぎるので、一瞬で表にできる関数があればありがたいのです。
この表の行が20行目から始まるので、上が19行空いています。そこで41~50列の1~20行を利用して別表を作り、各フィルターをかけた時に41から50列の内容が決まるので、その決まった内容で、各列の1の数量、2の数量、3の数量および--の数量を出したいのです。
COUNTIFS で出す方法も、フィルターが色々代わるので、その都度関数を変えないとだめなので、困っています。
何か良い方法はないでしょうか?
要は、1から40列でフィルターがかかっているとき、その内容がどうあれ、41~50列の表に出た分だけを計算する式があればよいのですが・・・。
SUBTOTAL(9,VS21:VS10000)のような式で、1.2.3を分けて出す式があればよいのですが・・・
尚、フィルターを掛けた時、41~50の表には、1.2.3--が全て混ざって入っているので、
SUBTOTAL(9,VS21:VS10000)では、わからないのです。
このフィルターをかけた内容を別シートにコピーし、フィルターがかかっていない表にしたうえで、1.2.3を求める方法もできますが、これも時間がかかり困っています。
よろしくお願いいたします。
No.3ベストアンサー
- 回答日時:
No2です。
連投失礼。
よく考えたら、「作業行」は不要でした。失礼いたしました。
(B1の値はSUBTOTAL関数で求められますので・・)
改めて、
B1セルに以下の式を入力し、B3セルまでフィルコピー。
=IFERROR(SUMPRODUCT((AGGREGATE(15,7,B$20:B$120,ROW(OFFSET($A$1,0,0,SUBTOTAL(102,B$20:B$120))))=ROW(A1))*1),0)
その上で、B1:B3を右方にフィルコピーすれば同様の結果を得られます。
ありがとうございました。
非常に難解ですが、答えはバッチリあっていました。
最高です。以前から fujillin さんには何度も難しいのを解決していただいています。
まことに何とお礼申し上げればよいかわからないほどです。
本当に感謝です。
これからこの式の意味を調べて今後に役立てたいと思います。
ありがとうございました。
No.2
- 回答日時:
こんにちは
なさりたいことがよくわからないのですけれど・・
>SUBTOTAL(9,VS21:VS10000)のような式で、
>1.2.3を分けて出す式があればよいのですが・・・
式の雰囲気等から、各列ごとに指定数と一致する個数を算出したいものと解釈しました。
(合計を算出したい場合は、個数に指定数を乗算すれば得られます)
>この表の行が20行目から始まるので、上が19行空いています。
であるなら、各列毎に計算してしまえばよさそうに思われます。
例えば、A2:A5に1、2、3、--を入力しておいて、B2:AY5に各列の個数が表示されれば良いということでしょうか?
ご質問文には明示されていませんけれど「フィルター云々」とあるので、非表示行はカウント対象にしないということと解釈しましたが、関数で非表示行を判断可能なのはSUBTOTAL関数とAGGREGATE関数だけですが、これらは基本的には数値計算用の関数ですので、これを用いて算出しようとするといくつかの制約が出ます。
・配列計算になるため、計算が重くなります。
フィルターを変更すると全セルが再計算になるので、若干時間がかかる
可能性があります。(PCの性能によります)
・数値以外(ご例示の「--」等)をカウントするには、別計算をする
必要があります。
(例えば、--は「-1」としてユーザ定義書式で「0_ ;"--";0;@」として
表示上は「--」とするなどとしておけば、同じ式で全部の計算が可能です)
計算の要領の例を以下に。
例えば、B1セルに(仮に1行目を作業行とします)
=SUMPRODUCT((1-ISERROR(AGGREGATE(15,7,B$20:B$120,ROW(A$1:A$101))))*1)
を入力すると、B列の「表示行のうち数値であるものの数」が表示されます。
これを利用して、
B2セルに
=IFERROR(SUMPRODUCT((AGGREGATE(15,7,B$20:B$120,ROW(OFFSET($A$1,0,0,B$1)))=ROW(A1))*1),0)
の式を入力すれば、値が「1」であるもの個数が算出されます。
B2セルをB4セルまでフィルコピーすると、値が1~3までの個数がそれぞれ表示されます。
B1の式をB2以下の式に組み込んで、作業行を無くすことは可能ですが、更に計算が重くなるので、こちらの方法にしてあります。
これらの式を右方にフィルコピーすればそれぞれの列で、「表示行のうちで1、2、3の値の個数」が求められます。
「--」のカウントは上記の数値化をすれば「-1」の数を数えれば良いので同じ要領で可能です。
文字列のままで行いたければ、SUBTOTAL関数でCOUNTAからCOUNTを減ずれば求められるでしょう。
ただし、この式は「表示されている値のあるセル数から数値のセル数を減ずる」という計算になりますので、「--」を特定してカウントしてはいません。
ですので、仮に「aa」やスペースだけのセルがあってもカウントしてしまいます。
関数だとかなりかったるいので、ユーザ定義関数を利用すれば簡単にすることは可能です。
例えば
=KEISAN(対象セル範囲, カウントしたい値)
のような関数にできますので、シート上の式はとてもスッキリとします。
ただし、質問者様がVBAを扱えるという別の条件が付いてしまいますけれど・・
ありがとうございます。
質問の仕方がまずくて申し訳ありません。
ご記載の解釈内容はその通りですので、一度これでやってみます。
ありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) Excel(エクセル)でフィルター抽出後、非表示の行を計算しないで、合計を算出する方法 【内容】 添 4 2023/01/30 17:17
- Excel(エクセル) 特定文字(数字)で行挿入、挿入された行で合計したい 2 2023/03/13 14:30
- Excel(エクセル) Excelの表作成でコピペ作業を軽減したい 11 2022/02/04 15:36
- Excel(エクセル) COUNTIFで同一範囲にある複数の内容の数量を簡単に抽出する方法を教えてください。 5 2023/11/23 11:45
- Excel(エクセル) エクセルで可視セルにのみ値貼り付けをする方法があったら教えて下さい やりたいこととしては フィルター 5 2023/09/25 11:38
- Visual Basic(VBA) エクセルのマクロを教えてください 4 2022/02/04 08:05
- Excel(エクセル) Indirect関数について、Formulatextで抽出した数式を参照したい。 1 2022/12/15 11:16
- 魚類 メダカが次々に死んでしまいます。 外観では病変が見られず元気だったメダカが、ある日突然弱って餌を食べ 5 2023/08/12 07:22
- Excel(エクセル) EXCELピボットテーブル(複数アイテム) 1 2023/04/27 12:15
- Excel(エクセル) [オートフィルター]機能について 3 2023/02/04 14:32
このQ&Aを見た人はこんなQ&Aも見ています
-
性格の違いは生まれた順番で決まる?長男長女・中間子・末っ子・一人っ子の性格の傾向
同じ環境で生まれ育っても、生まれ順で性格は違うものなのだろうか。家庭教育研究家の田宮由美さんに教えてもらった。
-
エクセルのセル統合について
Excel(エクセル)
-
Excelのシート背景に不明な文字が表示されています。何故?
Excel(エクセル)
-
VBA(えくせる)ってなんでメンテできない人が多いんですか?
Excel(エクセル)
-
-
4
EXCELファイルが読み取り専用でしか開かず上書きも出来ません。 EXCELを開いて最上部中央部のフ
Excel(エクセル)
-
5
Excelで【1-11】と入力すると【1月11日】になってしまう
Excel(エクセル)
-
6
なぜか「Nextに対応するForがありません」
Excel(エクセル)
-
7
<急募>エクセルデータの関数を用いた並び替えについて
Excel(エクセル)
-
8
Excel データの並び替え
Excel(エクセル)
-
9
りんご"と"みかん"というシートがあります。 "りんご"というシートの B列にて、B2から40行お"
Excel(エクセル)
-
10
エクセル 該当行だけを表示したい。 削除でもいいし、非表示でもいいです。 手動でひひょうじにするので
Excel(エクセル)
-
11
Excelの関数で起きた現象の原因がわかりません。
Excel(エクセル)
-
12
曜日の 月 を (月) と表示させる方法について
Excel(エクセル)
-
13
【Excel】セル内の時間帯が特定の時間帯に含まれるかの判定
Excel(エクセル)
-
14
エクセルの表の参照値から円を取って数字で扱えるようにしたい
Excel(エクセル)
-
15
同じ型【ハイフンと数字】だけ抜き出す関数について
Excel(エクセル)
-
16
同じ型【ハイフンと数字】をぶつけて、イコールである事を証明する関数はありますか?
Excel(エクセル)
-
17
ExcelでA列をコピーしたいのですがコピー範囲内に空白セルがあるとそこで終わってしまいます。 全て
Excel(エクセル)
-
18
エクセルで比較 かつ 部分一致について
Excel(エクセル)
-
19
2つのシートを連動させたいです
Excel(エクセル)
-
20
エクセル 複数条件について
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
カメラ初心者です。 ミラーレス...
-
メールソフトサンダーバードの...
-
Excelで任意の塗りつぶし色の数...
-
スプレッドシートでフィルター...
-
フィルターとフードを付ける順...
-
浄水器の使用期限
-
RF24-105mm F4-7.1 IS STM のレ...
-
VBAにてオートフィルターの条件...
-
Reaperで徐々にフィルターをか...
-
100行50列の表で、1~40列でフ...
-
NDフィルターの購入を検討して...
-
Excelで ピボット上のフィルタ...
-
インスタグラムのストーリーで...
-
レンズフードの互換性について...
-
エクセルのオートフィルターが...
-
マクロの作り方で質問です。
-
thunderbirdのメッセージフィル...
-
パソコンで補正できない類のフ...
-
破損フィルターの取り外し方法
-
PicsArtでフィルターはどこにあ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
スプレッドシートでフィルター...
-
メールソフトサンダーバードの...
-
Excelで任意の塗りつぶし色の数...
-
auの安心フィルターを掛けられ...
-
安心フィルターについての質問...
-
PENTAX Super Takumar 55mm f1....
-
タバコ火つける方間違ったんで...
-
エクセル質問「フィルターをか...
-
浄水器の使用期限
-
Web ページ (フィルター後)で...
-
VBAにてオートフィルターの条件...
-
レンズフード、フィルターの互...
-
thunderbirdのメッセージフィル...
-
UQモバイルの安心フィルター(中...
-
フィルターとフードを付ける順...
-
レンズフードの互換性について...
-
レンズ/フィルターのねじ切り...
-
Excel オートフィルタ オプショ...
-
安心フィルターの位置情報がば...
-
iPhoneからAndroidへ機種変。op...
おすすめ情報