申し訳ありませんが、どなたかお助けください。
日付が横軸、名前が縦軸にあります。
該当する場合には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
あたりを使用するように思えるのですが、
情けないかな、うまく関数を使いこなせないのです。
申し訳ありませんが、
お力をお貸しください。
No.1ベストアンサー
- 回答日時:
chiezo2005 様
早速のレスありがとうございます。
おかげさまで、できました!!
大変わかりやすかったです。
本当に感謝です。
No.2
- 回答日時:
こんばんは!
スマートな方法ではないのですが・・・
↓の画像のように水色部分にすべて作業列を挿入します。
B3セル=A3*B2+B2
B5セル=A5*B4+B4
B7セル=A7*B6+B6
としてオートフィルで右へコピーしていきます。
連続している場合のみ2以上の数値が表示されると思いますので
仮に佐藤さんの場合、連続最大値のセルに
=MAX(B3:AE3) ←(1日~30日までのデータ)
とすれば希望の数値になるのではないでしょうか?
あくまで「1」という数値が入る前提での回答です。
色々関数を駆使すればもっとすっきりした方法があるかもしれませんが、
素人っぽい回答で申し訳ございません。
今はこの程度しか思い浮かびませんでした。
以上、参考になれば幸いですが
的外れ・他に良い方法があれば読み流してください。m(__)m
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
No.4
- 回答日時:
No.2です!
たびたびごめんなさい。
先ほどの回答で佐藤さんとしましたが、鈴木さんの間違いでした。
そして数式を
=IF(MAX(B3:AE3)<=1,"データなし",MAX(B3:AE3))
として田中さん・佐藤さんも同じようにやっていただければいいのではないかと思います。
どうも失礼しました。m(__)m
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
隙間に計算式いれると後々めんどうだとおもったもので。
そんなことわかってるて?失礼しました><
お忙しいところ、一緒になって考えてくださり、
本当にありがとうございます。
皆様のアドバイスにより、だんだんとエクセルが面白くなってきています。
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)を選択して実行します。
これで、新しいシートを挿入し、そこに表示されます。
No.7
- 回答日時:
>Count Index Max あたりを使用するように思えるのですが、・・
多分見当ハズレです。関数は1つのセルの値を調べたり・値で条件を考えセルの数を数えたりは出来ますが、
位置関係(連なり、配置・セルの値の存在情況パターン)まで条件になると、力を発揮できません。3セルが順にa、b、cのあるシート上のあり場所も関数で探すのは難しいと思う。
ーー
#2のご回答の方法も昨晩から考えましたが、例えば4連の時、1234と123もセルに出てしまうので、4だけカウントするのが難しく思いました。
4だけシートに出る関数組み合わせが可能かを考えて見ます。
そうすればCOUNTIFで連の数の統計がたやすく取れると思う。
======
ですからVBAでやらざるを得ないと思います。
例データA1:H8 A-H列
14月1日4月2日4月3日4月4日4月5日4月6日4月7日
2鈴木111 1
3田中1111
4佐藤1111
5川田11111
6島田1111
7三島111
8木村11111
結果
K-N列 K1:N8 第1行は連続日数(見出しとしてのもの。文字列可)
1234
11
1
2
11
21
3
11
コード
標準モジュールに
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を増やすか、
いっそ別シートに出すようにコードを改める(コード略)
対象者の増加は、コードをいじくる必要なし。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) EXCEL 関数を教えてください。(A列の同じ値が複数ある場合vlookupで出来ますか) 4 2022/12/07 20:54
- Excel(エクセル) Excelマクロ 差分抽出の方法が知りたいです。 2 2023/03/07 13:25
- その他(データベース) 20万行あるデータを動かしたい 2 2023/06/13 15:21
- Visual Basic(VBA) エクセルについて教えてください。 3 2023/06/28 09:11
- Excel(エクセル) エクセルで対象日に該当するデータがある場合に別表へ全対象者を表示させたい。 3 2023/07/12 09:48
- その他(プログラミング・Web制作) Python pandasについての質問です。 日付 名前 ◯月◯日 佐藤 ・ 伊藤 ・ 山田 ・ 2 2022/06/13 17:16
- その他(法律) 携帯電話会社に保管されている解約済み個人情報を消去したい 3 2022/08/13 23:23
- Excel(エクセル) Excelについて 4 2023/03/02 09:24
- Excel(エクセル) 再質問 エクセル「社員の重なっている仕事時間の算出方法について」教えてください。 10 2023/02/07 19:16
- Excel(エクセル) エクセルの条件付き書式 個人シートを参照して集計シートに色付けしたい 1 2023/06/22 00:39
このQ&Aを見た人はこんなQ&Aも見ています
-
外出時に「待たせる妻」vs イライラする「待つ夫」は日本だけ?見習いたい海外事情
夫の家事参加に積極的なイメージのある海外でも、同様の事例はあるのか。結婚カウンセラーの佐竹悦子さんに伺ってみた。
-
Excelで連続データのカウント
Excel(エクセル)
-
エクセルで連続するデータの個数だけカウントする
Excel(エクセル)
-
Excelで連続するデータの個数をカウントする方法を教えてください
Excel(エクセル)
-
-
4
エクセル2010 同じ数字や文字が連続する数をカウントするには?
Excel(エクセル)
-
5
元データから連続5日以上だった人を抽出したい
Excel(エクセル)
-
6
Excel 同じ値が連続している行の抽出
Excel(エクセル)
-
7
エクセル関数/連続した○◎●をカウントする関数
その他(Microsoft Office)
-
8
エクセル、○が連続する回数を数えるには?
その他(コンピューター・テクノロジー)
-
9
Excelで連勤の氏名を抽出する
Excel(エクセル)
-
10
エクセルで連続データから、数個飛ばしのデータを抜き取る方法
Excel(エクセル)
-
11
Excel上でのデータ数字が連番であることを確認する方法を教えてくださ
その他(Microsoft Office)
-
12
文字列が連続しているかどうか調べたいのですが、良い方法はないでしょうか
Visual Basic(VBA)
-
13
エクセルで直近の連続回数を求める
Excel(エクセル)
-
14
【エクセル】シフトで○が6個以上の場合に色を自動で変更したい
Excel(エクセル)
-
15
【エクセル】シフトで○が6個以上の場合に色を自動で変更したい Part.2
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
特定の文字がある行以外を削除...
-
excelのデータで色つき行の抽出...
-
直近の5個の平均を求めたい
-
[EXCEL]ボタン押す→時刻が表に...
-
【Excel関数】UNIQUE関数で"0"...
-
アクティブになっている行をマ...
-
エクセル マクロで数値が変っ...
-
Excel グラフのプロットからデ...
-
【EXCEL】連続データの個数を抽...
-
エクセルで特定の文字列が入っ...
-
Excel 時刻の並び替え
-
エクセルのセルに指定画像(.jpg...
-
チェックボックスをクリックし...
-
セルの色によって条件文をつけ...
-
エクセル マクロ オートフィ...
-
エクセル2016で時間を入力して...
-
電話番号の入力方式が違うデー...
-
このような複雑な表をワードで...
-
エクセルで、ポインタのある行...
-
Excel マクロで特定のセルに入...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルで特定の文字列が入っ...
-
エクセル マクロ オートフィ...
-
【Excel関数】UNIQUE関数で"0"...
-
[EXCEL]ボタン押す→時刻が表に...
-
結合されたセルをプルダウンの...
-
エクセル マクロで数値が変っ...
-
Excel グラフのプロットからデ...
-
AのセルとB行を比較して、一致...
-
エクセル 上下で列幅を変えるには
-
Excel ウインドウ枠の固定をす...
-
特定の文字がある行以外を削除...
-
excelのデータで色つき行の抽出...
-
エクセル2016で時間を入力して...
-
excel 小さすぎて見えないセル...
-
EXCELで最後の行を固定
-
エクセルVBA 最終行を選んで並...
-
VBAで色の付いているセルの行削除
-
エクセルマクロで偶数行(又は...
-
エクセルのセルに指定画像(.jpg...
-
罫線の斜線を自動で引くマクロ
おすすめ情報