[[20160924154458]] 『複数の列に入力されているデータの「行数」のカウ』(NH) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『複数の列に入力されているデータの「行数」のカウント方法』(NH)

お世話になっております。

複数の列に入力されているデータの行数のカウント方法につきまして
ご教示願います。

具体例

  A   B   C   D   E
1 あ      う        
2     い  う         
3
4 あ          え  お
5     い      え  か

上記のようなデータがあった場合、データが入っている1,2,4,5行目をカウントし集計結果「4」を出す方法。

データには都度異なる文字列等が入ります
都合上、作業列を追加することができないため、特定の1つのセルに数式を入力する方法で集計結果を出したいです。

宜しくお願い致します。

< 使用 Excel:Excel2007、使用 OS:Windows7 >


 =SUMPRODUCT(N(COUNTIF(OFFSET(A1:E1,ROW(1:5)-1,0),"<>")>0))

 こういうこと?

 ROW(1:5) は表の行数を指定。
 A1:E5の表は全部で「5行」なのでこのようにします。

 もし A2:E10 の表なら「9行」なので ROW(1:9) にする。

 言ってること、わかります?
(笑) 2016/09/24(土) 16:27

笑様

ご返信ありがとうございます。

説明不足で申し訳ございません。

表の行数は都度変わるため、ROW(1:5)のように
指定せずにカウントする方法はありますでしょうか。

宜しくお願い致します。
(NH) 2016/09/24(土) 16:44


 行数の変動化については、引き続き、アドバイスがあると思いますので別のことを。

 >>都合上、作業列を追加することができないため

 どんな都合があるんでしょうかね? エクセルの列数は、16384列もあります。
 それらをぎっしりと使っているのですか? どこかに使っていない列はありますよねぇ。
 そこを作業列にして、目障りなら非表示にしておけばいいと思いますが?

 都合上 ではなく、勉強のために ということならわかりますが。

(β) 2016/09/24(土) 16:59


 > 表の行数は都度変わるため

 では、今回は何行の表だっていうのは、どうやって判断するんですかね?

 返事もらっても回答するかどうかわかりませんけど。
(笑) 2016/09/24(土) 17:04

(β)様

ご返信有難うございます。

都合につきましては、下記2点となります。

・あるシステムから毎回ローデータを落として、集計する作業を単純にしたい
 (=都度、列を挿入して数式を入力する作業を省きたい)
・作業を誤ったときのために、なるべくローデータの中身をいじらずに、別のシートに集計結果を出すようにしておきたい

具体例はA〜E列だけにしていますが、
実際には20〜30列ほどあり、
1列に全てのデータが入っているものもあれば、
複数の列にデータがランダムに入っているものもあり、
作業用シートを用意して、そこに集計対象列を入力して
集計結果を出力するようなものを作りたいと思っています。

宜しくお願い致します。
(NH) 2016/09/24(土) 17:11


 ↑ 読んでませんけど、、

 表の下には何も入力しないのなら、範囲を広めにとっておくとか、

 最大100行だとして

 =SUMPRODUCT(N(COUNTIF(OFFSET(A1:E1,ROW(1:100)-1,0),"<>")>0))

 質問しても返事をもらえないようなので、これがファイナル・アンサー
(笑) 2016/09/24(土) 17:20

(笑)様

わかりずらく大変申し訳御座いません。

状況がわかるように再度具体例を記載いたします。

下記の表が、A列、1行目から、シート名「Sheet1」に入っています。

メンバーID 設問1 設問2-1 設問2-2 設問2-3 設問3
70      A サラダ カルパッチョ 赤
81 A サラダ カルパッチョ 赤
153 A サラダ 青
206 B カルパッチョ
243 A サラダ カルパッチョ パスタ 青
260 B
373 A サラダ 緑
376 A サラダ カルパッチョ 緑
385 B
400 A サラダ 赤

別のシート、シート名「Sheet2」を作業者の入力用シートとし、
作業者が集計範囲の「開始列」「終了列」を入力すると、メンバーID数が表示される

	集計範囲		
	開始列	終了列	メンバーID数
設問1	B	B	10
設問2	C	E	8
設問3	F	F	7

設問1、3は、INDIRECT関数を使って求めることができましたが、
設問2のC〜Eを指定した場合のメンバーID数を求める数式の書き方がわかりません。

宜しくお願い致します。
(NH) 2016/09/24(土) 17:31


 わかりました。システムから作成されるブックなんですね。

 私は関数のことがよくわかりませんので、VBAです。
 (中身は 笑さんに提示いただいた式をそのまま使っています)

 新規ブックの標準モジュールに以下を貼り付けて、単独のマクロブックにしておきます。
 システムからブックが作成されたら、そのエクセル区画で、このマクロブックを開いて Sampleを実行します。

 Sub Sample()
    Dim r As Range
    Dim c As Range
    Dim adr As String
    Dim n As Long

    On Error Resume Next
    Set c = Application.InputBox("対象ブックの対象シートの任意のセルを選択してください", Type:=8)
    On Error GoTo 0

    If c Is Nothing Then Exit Sub   'キャンセルボタン

    With c.Parent   '対象シート
        With .Range("A1", .UsedRange)
            adr = .Rows(1).Address(external:=True)
            n = .Rows.Count
        End With

        MsgBox .Evaluate("SUMPRODUCT(N(COUNTIF(OFFSET(" & adr & ",ROW(1:" & n & ")-1,0),""<>"")>0))")

    End With

 End Sub

(β) 2016/09/24(土) 17:40


(笑)様

表の貼り付けが何度やっても上手くいかないので、
カンマ区切りで貼り付けます。

メンバーID,設問1,設問2-1,設問2-2,設問2-3,設問3
70,A,サラダ,カルパッチョ,(空白),赤
81,A,サラダ,カルパッチョ,(空白),赤
153,A,サラダ,(空白),(空白),青
206,B,(空白),カルパッチョ,(空白),(空白)
243,A,サラダ,カルパッチョ,(空白),パスタ,青
260,B,(空白),(空白),(空白),(空白)
373,A,サラダ,(空白),(空白),緑
376,A,サラダ,カルパッチョ,(空白),緑
385,B,(空白),(空白),(空白),(空白)
400,A,サラダ,(空白),(空白),赤

私の不手際で返信も遅く
ファイナルアンサーとのことですが
もしまだご回答いただけそうでしたら何卒宜しくお願い申し上げます。

(β)様
VBA有難うございます。 
誠に恐れ入りますが、
今回は数式で対応したいと思っております。
(NH) 2016/09/24(土) 17:56


 返事をもらえなかったんじゃなく、返事を書いてる途中だったみたいなので、
 もう一度回答します。

 で、行を広めにとっておくことで問題ないんですか?
 問題ないのなら
 
【Sheet2】

	A	B	C	D
1		開始列	終了列	ID数
2	設問1	B	B	10
3	設問2	C	E	8
4	設問3	F	F	7

 Sheet1のデータ開始は2行目からで、最大100行
 Sheet2の開始列・終了列は入力済みだとして

 D2 =SUMPRODUCT(N(COUNTIF(OFFSET(INDIRECT("Sheet1!"&B2&"2:"&C2&2),ROW($1:$100)-1,0),"<>")>0))

 下コピー

 とか?
(笑) 2016/09/24(土) 18:08

 範囲を広めにとるのでは何か問題がある場合ですけど、

 ・Sheet1のA列(メンバーID)は、すべての行に入力されてますか?
  つまり、それをカウントすれば何行の表かわかるんですか?

 ・メンバーIDはサンプルと同じく数値ですか(文字列ではなく)?

 以上、確認まで。
(笑) 2016/09/24(土) 19:01

(笑)様

ご返信が遅くなり申し訳ありません。

教えていただきました数式で、集計結果を出すことができました。ありがとうございます。

メンバーIDはすべての行に「数値」で入力されています。

現在メンバーIDは最大で2200行程度になりますが、
ID数は日々増加していくため、いずれは範囲を超えてしまう場合も想定されます。
メンバーIDをカウントして範囲を指定する方法がありますか?
(NH) 2016/09/26(月) 09:55


有効行数を得る関数は無いのですが、例えば、以下の数式で行数が得られます。
 =MAX(INDEX((A:A<>"")*ROW(A:A),))

これをどこかのセルに埋めておき、笑さんの式の行数部分と置き換えるとか。
(???) 2016/09/26(月) 11:24


 Sheet1のA列(メンバーID)が数値で、すべての行に入力されているのであれば

 データ行数は =COUNT(Sheet1!A:A)

 ※ ID以外の数値が、A列に入ることはないという前提で
 
 
 Sheet2の集計表は【2016/09/24(土) 18:08】の回答のと同じだとして

 D2 =SUMPRODUCT(N(COUNTIF(OFFSET(INDIRECT("Sheet1!"&B2&"2:"&C2&2),ROW(INDIRECT("1:"&COUNT(Sheet1!A:A)))-1,0),"<>")>0))

 例えばですが、Sheet2のF1セルに =COUNT(Sheet1!A:A) の式を入れておけば

 D2 =SUMPRODUCT(N(COUNTIF(OFFSET(INDIRECT("Sheet1!"&B2&"2:"&C2&2),ROW(INDIRECT("1:"&$F$1))-1,0),"<>")>0))

 当然、行数が多くなればなるほど計算速度は遅くなります。
 前回の数式(範囲を広めにとる)と今回のを比較して、どれぐらいの差があるかはわかりません。

 行数・設問数ともに最大にして、どんな感じになるか、そちらで検証してみてください。

 参考まで。
(笑) 2016/09/26(月) 12:18

コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.