エクセル2010使用です。
とある表の列(A列とします)にデータが入っています。
とりあえず10個のデータがあったとします
例
りんご
みかん
みかん
みかん
りんご
りんご
パイナップル
りんご
みかん
みかん
さて、このデータのうち
りんご、が連続して出現した場合のその連続回数の最大値、平均値、最小値を計算して、表の下に表示したいです。
同様にみかん、パイナップルの連続登場回数の最大値、平均値、最小値も表示させたいです。
補足:データは16バイト文字列、すなわち、全角漢字、全角平仮名、全角カタカナとします。
8バイト文字列、すなわち、半角英数文字や数値データは登場しないものとします。
登場するデータの種類は限定されているので、「未知のデータが登場することはない」との前提で結構です。
算出結果を表示させるセルは、データごとにそれぞれ別のセルで構いません。
あくまでも「連続している場合の連続回数の最大値、平均値、最小値」です。「データの個数」ではありません。
以上をセル関数で計算、表示する方法を教えてください。
No.4ベストアンサー
- 回答日時:
こんにちは!
安直な方法ですが・・・
↓の画像のように作業用の列を3列設けてみました。
作業列1はA列を重複なしに表示するため、作業列2・作業列3は最大値・最小値・平均値を求めるためです。
作業列1のB2セルに
=IF(COUNTIF(A$2:A2,A2)=1,ROW(),"")
C2セルに
=IF(A3<>A2,ROW()-1,"")
D2セルに
=IF(C2="","",C2-MAX(C$1:C1))
という数式を入れそれぞれこれ以上データはない!という位まで下へコピーしておきます。
F2セルに
=IFERROR(INDEX(A:A,SMALL(B:B,ROW(A1))),"")
G2セルに
=IF(F2="","",MAX(IF(A$2:A$1000=F2,D$2:D$1000)))
G2セルは配列数式なのでCtrl+Shift+Enterで確定!(←必須★)
H2セルに
=IF(F2="","",MIN(IF(A$1:A$1000=F2,D$1:D$1000)))
こちらも配列数式なのでCtrl+Shift+Enterで確定!(★)
I2セル(配列数式ではありません)に
=IF(F2="","",AVERAGEIF(A:A,F2,D:D))
という数式を入れ、F2~I2セルを範囲指定 → I2セルのフィルハンドルで下へコピーすると
画像のような感じになります。
※ 作業列が目障りであれば、どこか遠く離れた列にするか
非表示にしてください。m(_ _)m
No.3
- 回答日時:
1つの関数式でそれをするのは無理があると思います。
作業セルを使うと簡単にできますが。果物の名前が入ってるのがA列の1行目からだとすると、
B列で「りんご」だけを数える
C列で「みかん」だけを数える
みたいに専用にカウントするセル(作業セル)を作ります。で、後でこれを集計するという方法です。集計の経過を印刷したくないとか見せたくないって場合は、作業セルを非表示にするか、印刷範囲外に作ります。
りんごを数える作業セルの式はこんな感じで。わざとaをくっつけて文字列にしているので複雑に見えますが、これはカウントしてる途中経過を数値にせず、結果だけを数値にしたいためです。
セルB1(数え始め)
=IF($A1="りんご","1a","")
セルB2(それ以降はオートフィル)
=IF($A2="りんご",IF(RIGHT(B1,1)<>"a","1a",MID(B1,1,LEN(B1)-1)+1&"a"),IF(B1="","",MID(B1,1,LEN(B1)-1)))
こうすればB列の最大値と最小値が、りんごの最大最小連続登場回数になります。ところで、この式だと答えとして現れうる最小値は1(連続データがない)ですが、それでいいですか?
1だった場合には答えとして出さないようにすることは、最後のIF関数をいじるだけで可能です。でもそうすると、今回の例のパイナップルみたいに1度も連続して登場しないものがデータ中に含まれていたら、値が取り出せないことになります。
No.2
- 回答日時:
(´・ω・`)?
いきなり一つの数式で解決しようと考えていませんか?
こういう問題は、一つずつ手順を追って処理することで解決できますよ。
紙の上で処理する時、どんな手順で作業をするかを考えてみましょう。
連続する最大値を求める手順なら、
一番上のデータを比較する対象がないので、二つ目のデータから ”比較” を行いますよね。
(質問の例では一つ目のデータ「りんご」は比較せず、二つ目のデータ「みかん」から)
二つ目のデータは一つ目のデータと同じならその横に2と書き、違えば1と書く。(この場合は1)
三つ目のデータは二つ目のデータを比較して、同じなら二つ目のデータに対応する数字(この場合は1)に1を加えた値を横に書き、違えば1と書く。
これを最後のデータまで繰り返す。
次に
「りんご」の横にある数字で一番大きいものを選ぶ。
「みかん」の横にある数字で一番大きいものを選ぶ。
「パイナップル」の横にある数字で一番大きいものを選ぶ。
などじゃないかな。
・・・
集計したい項目が書かれた列の横に作業用の列を設けてみましょう。
そうしたうえで何が分からないのかを考え、その解決方法を探るようにすると
以後、同様の問題が現れたときに自力で対処できるようになります。
こういった考え方ができるようになったら、もっと高度な集計方法を考えてみましょう。
がんばれ。
・・・余談・・・
自分なら作業列まで作ったらピボットテーブルを作って処理する。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセルのセルの書式設定・ユーザー定義の条件設定について 1 2022/08/17 21:56
- Visual Basic(VBA) VBA 「,」・空白・カタカナ等の複数条件のマクロ 2 2023/08/23 11:57
- C言語・C++・C# このプログラミングの問題を教えてほしいです。 キーボードからデータ数nとn個のデータを入力し、平均値 3 2022/12/19 22:51
- C言語・C++・C# [C言語] コメント文字列を無視して、数値データを読み込むプログラム部分について 5 2022/10/05 11:03
- その他(プログラミング・Web制作) プログラミング pythonの問題について 2 2022/04/19 00:41
- Excel(エクセル) エクセルの数式で教えてください。 1 2023/02/02 10:20
- Excel(エクセル) Excelで全クラスのランキング表を作成したい 4 2022/05/24 15:28
- Excel(エクセル) [オートフィルター]機能について 3 2023/02/04 14:32
- C言語・C++・C# このプログラミング誰か教えてくれませんか 1 2022/06/02 15:27
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
このQ&Aを見た人はこんなQ&Aも見ています
-
「環境が人を育てる」って本当?環境によって人格や生き方は本当に変わるのか
環境が人生に与える影響は実際どれほどのものなのか、専門家の田宮由美さんに伺った。
-
エクセルで連続するデータの個数だけカウントする
Excel(エクセル)
-
エクセル、○が連続する回数を数えるには?
その他(コンピューター・テクノロジー)
-
Excelで連続するデータの個数をカウントする方法を教えてください
Excel(エクセル)
-
-
4
エクセルで同じ文字が3連続している回数の数え方
Excel(エクセル)
-
5
【EXCEL】連続データの個数を抽出する方法を御教授ください
Excel(エクセル)
-
6
エクセルで直近の連続回数を求める
Excel(エクセル)
-
7
Excelで連続データのカウント
Excel(エクセル)
-
8
文字列に同じ文字が2回以上連続した時の件数
Excel(エクセル)
-
9
Excel 同じ値が連続している行の抽出
Excel(エクセル)
-
10
【エクセル】シフトで○が6個以上の場合に色を自動で変更したい
Excel(エクセル)
-
11
元データから連続5日以上だった人を抽出したい
Excel(エクセル)
-
12
エクセルで連続した数値と0が何回出たかをカウントする
その他(Microsoft Office)
-
13
エクセル関数/連続した○◎●をカウントする関数
その他(Microsoft Office)
-
14
文字列が連続しているかどうか調べたいのですが、良い方法はないでしょうか
Visual Basic(VBA)
関連するカテゴリからQ&Aを探す
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルの関数について教えて...
-
Excelカスタム関数(アドイン登...
-
Excelデータをコピペして、ペー...
-
Excel関数-文字列で自動作成さ...
-
スプレッドシート、Excelでの数...
-
Excelで50個のセルに同じ文字を...
-
Microsoft Officeの中古は信用...
-
スプレッドシートで使う数式を...
-
エクセルVBA、別ブックへ転記す...
-
エクセルで会社の従業員のデー...
-
エクセルで不等号記号(≠)が上に...
-
エクセルの表で1年間の曜日を...
-
A列とB列を参照してC列に連番を...
-
エクセルの空欄をつめて、次の...
-
エクセルでの特別な文字を上に...
-
エクセルでセルに標準で入力さ...
-
エクセル日付 文字列の関数がエ...
-
エクセル2013で月間勤務表から...
-
エクセルの日付を編集する
-
EXCELの質問です 119から足した...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルVBA、別ブックへ転記す...
-
エクセルでの作業計算方法について
-
時間によってファイル名が変わ...
-
【関数】適切な文字数の数字を...
-
Excelについて教えてください
-
エクセル初心者です 関数の入れ...
-
【マクロ】ファイル名の変更に...
-
UNIQUE関数が使えないバージョ...
-
エクセルの計算
-
【関数】先頭だけにある、半角...
-
Excelで、決まった行を繰り返し...
-
Excelでセルの値が同じか...
-
LOOKUP関数を使えばいいのでし...
-
Excel
-
はがきについて。
-
エクセルの条件付き書式につい...
-
エクセルのデーターが2か月前の...
-
エクセル②
-
エクセルで「-0.0」と表示さ...
-
Microsoft1Officeの互換ソフト...
おすすめ情報