• ベストアンサー

【EXCEL】連続データの個数を抽出する方法を御教授ください

申し訳ありませんが、どなたかお助けください。 日付が横軸、名前が縦軸にあります。 該当する場合には1が立ち、該当がなければデータは表示されません。 1か月の中で、連続するデータの最大個数を求めたいのですが、 よくわかりません。 なにとぞよろしくお願いします。   A  B   C  D   E  F 1    4/1  4/2  4/3  4/4  4/5 2 鈴木 1       1   1 3 田中     1   1   1   1 4 佐藤     1       1 上のデータのみで月末を迎えたら、 鈴木=(最大)2 田中=(最大)4 佐藤=(最大)1 ※できれば2以上の連続する個数を求めたいので、  佐藤はデータなしとしたいです。 Count Index Max あたりを使用するように思えるのですが、 情けないかな、うまく関数を使いこなせないのです。 申し訳ありませんが、 お力をお貸しください。

質問者が選んだベストアンサー

  • ベストアンサー
  • chiezo2005
  • ベストアンサー率41% (634/1537)
回答No.1
chihatatu
質問者

お礼

chiezo2005 様 早速のレスありがとうございます。 おかげさまで、できました!! 大変わかりやすかったです。 本当に感謝です。

その他の回答 (6)

  • imogasi
  • ベストアンサー率27% (4737/17068)
回答No.7

>Count Index Max あたりを使用するように思えるのですが、・・ 多分見当ハズレです。関数は1つのセルの値を調べたり・値で条件を考えセルの数を数えたりは出来ますが、 位置関係(連なり、配置・セルの値の存在情況パターン)まで条件になると、力を発揮できません。3セルが順にa、b、cのあるシート上のあり場所も関数で探すのは難しいと思う。 ーー #2のご回答の方法も昨晩から考えましたが、例えば4連の時、1234と123もセルに出てしまうので、4だけカウントするのが難しく思いました。 4だけシートに出る関数組み合わせが可能かを考えて見ます。 そうすればCOUNTIFで連の数の統計がたやすく取れると思う。 ====== ですからVBAでやらざるを得ないと思います。 例データA1:H8 A-H列 1 4月1日 4月2日 4月3日 4月4日 4月5日 4月6日 4月7日 2 鈴木 1 1 1 1 3 田中 1 1 1 1 4 佐藤 1 1 1 1 5 川田 1 1 1 1 1 6 島田 1 1 1 1 7 三島 1 1 1 8 木村 1 1 1 1 1 結果 K-N列 K1:N8 第1行は連続日数(見出しとしてのもの。文字列可) 1 2 3 4 1 1 1 2 1 1 2 1 3 1 1 コード 標準モジュールに Sub test01() d = Range("B65536").End(xlUp).Row MsgBox d For i = 2 To d '第2行から最下行まで行単位の処理を繰り返し r = 0 For j = 3 To 8 'C列からH列まで1かどうかチェック If Cells(i, j) = 1 Then '--1の場合 rはその列まででの連なりの数を示す r = r + 1 MsgBox i & "行・連 " & r Else '--空白の場合 連が途切れ、連の個数分類をK列以右対応列に記録 'ただしこの列空白でも、前の列のセルが空白なら処理スキップ If Cells(i, j - 1) <> "" Then Cells(i, 10 + r) = Cells(i, 10 + r) + 1 r = 0 End If End If Next j If r <> 0 Then Cells(i, 10 + r) = Cells(i, 10 + r) + 1 r = 0 End If Next End Sub ただこの処理ロジックは注意点があって、意外に経験を要するようにも思うが。 ーー 実際の場合には 4月7日までになっているが、日数=列数を増やす。 ==>For j = 3 To 8の8を増やす。 それに伴い結果を出すセルをより右列にずらす必要あり。 Cells(i, 10 + r) = Cells(i, 10 + r) + 1の10を増やすか、 いっそ別シートに出すようにコードを改める(コード略) 対象者の増加は、コードをいじくる必要なし。

chihatatu
質問者

お礼

imogasi 様 親身になって考えてくださり、 本当にありがとうございました。 VBAもう少し勉強します。

  • merlionXX
  • ベストアンサー率48% (1930/4007)
回答No.6

もし表の配置がお書きになったようにA1から始まり、1行目が日付、A列が氏名で、条件に該当するセルには数値が入力されているなら、 以下の手順をおためしください。 もし数値が入力されているのではなく、数式の結果で表示されているのなら For Each myArs In myRng.Item(i).Offset(0, 1).Resize(, x - 1).SpecialCells(xlCellTypeConstants, 1).Areas 'ここを書き換え の部分を For Each myArs In myRng.Item(i).Offset(0, 1).Resize(, x - 1).SpecialCells(xlCellTypeFormulas, 1).Areas に書き換えてください。 1.AltキーとF11キー同時に押し(以下Alt+F11キーと記述)て Visual Basic Editor を呼び出します。 2.Visual Basic Editor のメニューから「挿入」、「標準モジュール」で出てきたコードウィンド(右側の白い広い部分)に以下のコード(Sub~End Sub)をコピペします。 '********これより下********** Sub test01() Dim ws As Worksheet, ns As Worksheet Dim myRng As Range, myArs As Range Dim x As Long, y As Long, i As Long, n As Long, z As Long Dim myCnt() Set ws = ActiveSheet Set ns = Sheets.Add(After:=ws) Set myRng = ws.Range("A1").CurrentRegion.Rows x = ws.Range("A1").CurrentRegion.Columns.Count y = myRng.Rows.Count For i = 2 To y ReDim Preserve myCnt(i - 2) For Each myArs In myRng.Item(i).Offset(0, 1).Resize(, x - 1).SpecialCells(xlCellTypeConstants, 1).Areas 'ここを書き換え z = IIf(myArs.Cells.Count > myCnt(i - 2), myArs.Cells.Count, myCnt(i - 2)) Next myArs myCnt(i - 2) = IIf(z > 1, z, "なし") Next i ns.Range("A1").Resize(UBound(myCnt) + 1).Value = ws.Range("A2").Resize(UBound(myCnt) + 1).Value ns.Range("B1").Resize(UBound(myCnt) + 1).Value = Application.Transpose(myCnt) End Sub '********これより上********** 3.Alt+F11キーでワークシートへもどります. 4.Alt+F8キーで出てきたマクロ名(test01)を選択して実行します。 これで、新しいシートを挿入し、そこに表示されます。

chihatatu
質問者

お礼

merlionXX 様 お忙しいところ、ありがとうございました。 VBAがわかるよう、今年はチャレンジします。

  • okdeath
  • ベストアンサー率28% (13/46)
回答No.5

こんにちわ。 方法としてはtom04さんの方法でよいとおもいます。 (私はVBAわかんないので・・) でも、計算式は別のシートにするか、下、又は横に張ったほうがあとで元データ又は計算式の修正をするときに楽です。   A  B   C  D   E  F 1    4/1  4/2  4/3  4/4  4/5 2 鈴木 1       1   1 3 田中     1   1   1   1 4 佐藤     1       1 5 6 計算式用のスペース 7 鈴木 1    0   1   2   0 8 田中 0    1   2   3   4 9 佐藤 0    1   0   1   0 10 隙間に計算式いれると後々めんどうだとおもったもので。 そんなことわかってるて?失礼しました><

chihatatu
質問者

お礼

お忙しいところ、一緒になって考えてくださり、 本当にありがとうございます。 皆様のアドバイスにより、だんだんとエクセルが面白くなってきています。

  • tom04
  • ベストアンサー率49% (2537/5117)
回答No.4

No.2です! たびたびごめんなさい。 先ほどの回答で佐藤さんとしましたが、鈴木さんの間違いでした。 そして数式を =IF(MAX(B3:AE3)<=1,"データなし",MAX(B3:AE3)) として田中さん・佐藤さんも同じようにやっていただければいいのではないかと思います。 どうも失礼しました。m(__)m

  • mitarashi
  • ベストアンサー率59% (574/965)
回答No.3

難しい関数より、SpecialCellsを使って、ユーザー定義関数で簡単に、と思ったのですが、SpecialCellsはユーザー定義関数中では所期の動作をしない様です。仕方なくマクロとしましたが、折角作ったので載せておきます。マクロが嫌ならスルーしてください。なお、A列の途中に空白行があると、そこで処理を打ち切ってしまいます。当方、XL2000です。 Sub test() Dim myCell As Range Dim retVal As Long Set myCell = Range("a2") Do While myCell.Value <> "" retVal = maxBlock(myCell.Offset(0, 1).Resize(, 31)) If retVal > 1 Then myCell.Offset(0, 32).Value = retVal Set myCell = myCell.Offset(1, 0) Loop End Sub Private Function maxBlock(target As Range) As Long Dim myArea As Range Dim targetrange As Range Set targetrange = target.SpecialCells(xlCellTypeConstants, xlNumbers) For Each myArea In targetrange.Areas If myArea.Cells.Count > maxBlock Then maxBlock = myArea.Cells.Count Next myArea End Function

chihatatu
質問者

お礼

貴重なお時間をさいて検討してくださり 本当にありがとうございました。

  • tom04
  • ベストアンサー率49% (2537/5117)
回答No.2

こんばんは! スマートな方法ではないのですが・・・ ↓の画像のように水色部分にすべて作業列を挿入します。 B3セル=A3*B2+B2 B5セル=A5*B4+B4 B7セル=A7*B6+B6 としてオートフィルで右へコピーしていきます。 連続している場合のみ2以上の数値が表示されると思いますので 仮に佐藤さんの場合、連続最大値のセルに =MAX(B3:AE3) ←(1日~30日までのデータ) とすれば希望の数値になるのではないでしょうか? あくまで「1」という数値が入る前提での回答です。 色々関数を駆使すればもっとすっきりした方法があるかもしれませんが、 素人っぽい回答で申し訳ございません。 今はこの程度しか思い浮かびませんでした。 以上、参考になれば幸いですが 的外れ・他に良い方法があれば読み流してください。m(__)m

関連するQ&A

  • Excelで名寄せしてデータ個数を集計する方法

    このような表があります。 001 山田 001 山田 002 鈴木 003 田中 004 高橋 004 高橋 005 鈴木 005 鈴木 005 鈴木 006 高橋 007 鈴木 008 鈴木 この名前(「山田」等)を基準にして名寄せをし、かつデータ個数を集計しなくてはなりません。 普通に集計をすると、 001 山田 001 山田 山田 データ個数 2 002 鈴木 鈴木 データ個数 1 003 田中 田中 データ個数 1 004 高橋 004 高橋 高橋 データ個数 2 005 鈴木 005 鈴木 005 鈴木 鈴木 データ個数 3 006 高橋 高橋 データ個数 1 007 鈴木 008 鈴木 鈴木 データ個数 2 ・・・という具合になりますが、番号(001等)の枠を超えて名寄せしたいのです。 希望する集計結果としては、 001 山田 001 山田 山田 データ個数 2 002 鈴木 005 鈴木 005 鈴木 005 鈴木 007 鈴木 008 鈴木 鈴木 データ個数 6 003 田中 田中 データ個数 1 004 高橋 004 高橋 006 高橋 高橋 データ個数 3 ・・・という表示になるようにしたいのです。(番号も必要データなので消去せずに残したいです) 今までは、番号&名前レベルでまず集計し、あとは検索して同じ名前があればカット&ペーストという非常に面倒な作業をマニュアルでしてきました。 何かもっと楽にできる方法をご存知の方はぜひ教えてください。

  • MS Excelで連続したデータの個数を調べる方法を教えてください。

    MS Excelで連続したデータの個数を調べる方法を教えてください。 データ範囲の中で、プラスが何個連続で出たか、その個数の最大値を知りたい。 Aの列に連続で、  1  3 -5  2  3  1  1 -1  1  2  1 とあった場合、プラスが連続した個数は、2回、4回、3回となり、その最大値の4回が得られる方法を御願します。   

  • エクセルで連続した重複セルを抽出する方法

    お世話になります、ネット上で検索しましたが、 求めている答えを見つけることができなかったので、コチラで質問させていただきます。 エクセル表の制作についこのようなことが可能かどうかわからないのですが、 ご存知の方がいらっしゃいましたらご教授お願いいたします。 以下のようなエクセル表があるとします。 田中  佐藤  鈴木  木山  佐藤  平原  鈴木  木山 鈴木  平原  田中  木山  木山  平原  田中  佐藤        ・        ・        ・ 列こそ違いますが、行で言うと「木山」は4回連続で出現しています。 この場合、4回連続で入力された「木山」を抽出して背景の色を変えるとか 即座にそれを認識できる方法はありませんか? 要するに、4行連続で入力することをエラーとしたいのです。 当方、エクセルに関してそれほど知識等ありませんので、できるだけわかりやすく ご教授いただけたらありがたいです。よろしくお願いいたします。

  • ExcelVBAのデータの連続する個数の検索

    A~Fの各列ごとに、121以上の数字が連続する個数を出力し、出力したデータの最大値を求めたいのですが、どうやって式を組んだらいいか、わかりません。 アドバイスいただけましたら幸いです。 A / B / C / D / E / F 118.19 / 118.11 / 118.22 / 118.20 / 118.20 / 118.74 120.93 / 120.86 / 120.96 / 120.92 / 120.92 / 121.44 123.45 / 123.35 / 123.45 / 123.43 / 123.43 / 123.88 120.97 / 121.04 / 120.94 / 121.00 / 120.97 / 120.93 118.57 / 119.48 / 118.69 / 121.52 / 118.48 / 119.69 123.48 / 123.35 / 123.44 / 123.43 / 123.41 / 123.61 123.59 / 120.47 / 123.58 / 123.57 / 123.57 / 123.74 連続する個数 1コ / 2コ / 1コ / 5コ / 1コ / 2コ 2コ / 1コ / 2コ / - / 2コ / 2コ 最大値 2コ /2コ /2コ /5コ /2コ /2コ

  • Excel 該当データ数の抽出方法(日付)

    Excel 該当データ数の抽出方法(日付) <元データ>  A    B      C 1 氏名  開始日  終了日 2 3 青木 2010/3/1 2010/3/10 4 石田 2010/3/1 2010/3/20 5 鈴木 2010/3/3 2010/3/7 6 佐藤 2010/3/1 2010/3/3 7 田中 2010/3/5 2010/3/17  上記のような元データがあった場合に、各日に実施されているデータ数(開始日~終了日の間 に当たるデータ数)を以下のように抽出したいのですが、その方法を教えていただけないでしょうか。抽出先は同じシート内でも、別シートでもいいのですが。 <抽出>    A      B 1  日付    該当数 2 3 2010/3/1   3 4 2010/3/2   3 5 2010/3/3   4 6 2010/3/4   3 7 2010/3/5   4  よろしくお願いします。 

  • エクセルのデータ並べ替え(抽出)の方法

    エクセルのデータ並べ替え(抽出)の方法 を教えてください。 下記のようなデータがあるとします。   A    B    C   D 1 田中  東京  千葉  福岡 2 山田  京都  滋賀 3 佐藤  奈良  青森  USA 4 鈴木  カナダ 愛媛 A列は名前、B列以降は文字列です。B列以降はC列までの行、D列までの行とさまざまです。重複セルはありません。 これを下記のように並べ替えたいです。   A    B    C   D 1東京  田中 2千葉  田中 3福岡  田中 4京都  山田 5滋賀  山田 6奈良  佐藤 7青森  佐藤 8USA   佐藤 9カナダ 鈴木 10愛媛  鈴木 こういうことは可能でしょうか??? 教えてください。 よろしくお願いします。

  • 重複しないデータの個数(EXCEL, 条件付き)

    こんにちは。お世話になります。 このようなデータがあるとします。 元データ 日付 ID 140501 11011 140501 11011 140501 11012 140502 11013 140502 11013 140502 11014 (以下月末まで、データ個数20,000個) このときに、別シートに 「5/1にログインしたIDは何個」 「5/2にログインしたIDは何個」 ・・・ 「5/31にログインしたIDは何個」 という結果を一覧で表示したいのですが、 いいアイデアがございますでしょうか? よろしくお願いいたします。

  • excel2003 2列のデータを1列に

    エクセル2003にて 下記のように2列のデータを1列に表示させたいと思っております。 A列  B列              C列 田中 佐藤              田中 鈴木 池田 このように>      鈴木 後藤 内藤              後藤                     佐藤                     池田                     内藤 C列に入れる数式をお教えください。 よろしくお願いします。

  • Excel 他シートのデータを抽出

    お世話になっております。 さっそく質問失礼いたします。 全売上が記載されたシートのデータを抽出し、営業担当者名で割り振られた各シートに該当するデータを抽出する方法を教えてください。 具体的には下記のようなものを作成したく思います。 シート1(月度売上表) 日付 担当 商品 台数 1日 田中  A1  1台 1日 山田  B1  2台 1日 佐藤  B1  5台 2日 田中  B1  3台 2日 佐藤  A1  2台 2日 佐藤  B1  4台 シート2(田中) 日付 担当 商品 台数 1日 田中  A1  1台 2日 田中  B1  3台 シート3(佐藤) 日付 担当 商品 台数 1日 佐藤  B1  5台 2日 佐藤  A1  2台 2日 佐藤  B1  4台 以下続く 以上のような表を一覧として作成したいと思っております。 シート1に内容が記載されたら自動的にリンクされるようにしたいので、ご質問いたしました。 よろしくおねがいいたします。

  • [Excel ADO]未登録データの抽出方法

    Excel2007を使っています CSVデータをADOによるSQL処理していますが、 名簿に未登録のデータが入っているかどうかを抽出するにはどうしたらいいでしょうか? T_名簿 No,登録者名 1,佐藤 2,鈴木 : という名簿に対して T_訪問者 No,日付,訪問者名 1,0601,鈴木 2,0601,佐藤 3,0602,山田 : という訪問者リストと照らし合わせ、山田さんは未登録者リストとして出力するというものです SELECT * FROM T_訪問者 WHERE 訪問者名 NOT IN (SELECT 登録者 FROM T_名簿) などとしてみましたが、うまくいきません SELECT * FROM T_訪問者 WHERE 訪問者名 NOT IN ('佐藤','鈴木') とすると出てくるのですが、名簿の中身は数十はあり変動もあるのでそのまま列挙はできません リテラル部分をテーブルから持ってくる方法、あるいは全く別のやりかたなど ありましたら教えてください よろしくお願いします

専門家に質問してみよう