エクセルでこんな集計方法が出来ますか？

質問

よろしくお願いします。

エクセル表の1行(横方向)に、商品(アルファベット記号)が売れた順番に
１個づつ左側からインプットされてます。
Ａ，Ａ，Ｂ，Ｄ，Ｆ，Ｆ，Ｆ，Ａ，Ａ，Ｂ，Ｂ，Ｅ，・・・・のように、

この1行の商品を売れた順に、以下のように整理したいのですが、

Ａ　2、Ｂ　1、Ｄ　１、Ｆ　3、Ａ　2、Ｂ　2、Ｅ　1、・・・・(これが欲しいデータです）

注：Ａ　2、とは、Ａが連続して2個売れたの意味：
　　整理は、Ａのセルと2のセルでセルを２つ使う。

勝手ながら、マクロの知識は全くありませんので
エクセルの関数で教えていただけるようお願いします。

OfficeＸＰ、を利用してます。

回答No.3

nishi6 · ベストアンサー

算式を簡単にするためにB1から文字を入力します。A列には入力はないとします。
B1～M1に　Ａ、Ａ、Ｂ、Ｄ、Ｆ、Ｆ、Ｆ、Ａ、Ａ、Ｂ、Ｂ、Ｅ　が入力されている例です。

2～4行目を計算に使います。結果は5行目に出力します。

＜重複の判定＞
B2 : =(B1=A1)*1　、これをC2～M2にコピー

＜連続した個数の計算＞
B3 : =IF(B2+C2=0,B2+1,IF(AND(B2=1,C2=0),COLUMN()-SUM($A$3:A3)-1,""))
　　　これをC3～M3にコピー

＜詰めて表示するために表示すべき列を求める＞
C4 : =IF(COUNT($B$3:$M$3)-((COLUMN()-1)/2-1)>0,
　　　　　LARGE(IF($B$3:$M$3<>"",COLUMN($B$3:$M$3),""),
　　　　　　　　COUNT($B$3:$M$3)-((COLUMN()-1)/2-1))-1,"")
　　　として(上は連続した算式です)、Ctrl+Shift+Enter で登録。（配列数式として登録）
　　　これを、E4、G4、I4、K4、M4、O4、Q4 ・・・にコピー。（１列おきに、多めにコピーしておきます）

＜最終結果の編集＞
B5 : =IF(SUM(C4)-1>=0,OFFSET($B$1,0,C4-1),"")
C5 : =IF(SUM(C4)-1>=0,OFFSET($B$1,2,C4-1),"")
　　　このペアを、D5、F5、H5、J5、L5、N5、P5 ・・・にコピーします。

２～４行目を非表示にすれば、計算に使用した部分を隠せます。
実際は何列使うか分からないので、『$B$3:$M$3』を『$B$3:$IV$3』にしておけば大丈夫でしょう。

このように関数を使って出力できますが、マクロを使って出力したほうがはるかに簡単でしょう。
かつ、分かり易く表現できるでしょう。

お礼

nishi6　様

ご回答ありがとうございます。

テストデータを入れて、実験した中継です。

＜連続した個数の計算＞
テストし、求める数値が3行目に見えた時
心臓がドキドキしました。

＜詰めて表示するために表示すべき列を求める＞
3行にわたる数式をコピー、ペーストで、本当かしら？、
出てきて頂戴！！と祈る気持ちでエンターしました。
数値が出ましたが、ただの数値で、すこし、拍子ぬけ。

＜最終結果の編集＞
コピー、ペーストで、
エイヤーとエンター！！
『出来た！　出た　出た　月も笑顔も　みんな出た　！！』

今日もまたすばらしい日です。
ご回答感謝します。ありがとうございました。

追伸；マクロを使って出力したほうがはるかに簡単でしょう
かつ、分かり易く表現、、、、。　
んーーーｍｍ。勉強したいが、頭が追いつかなく、挫折中です。

回答No.4

imogasi

出来あがった体裁は少しご希望のものと違うかもしれませんが、
（１）元データを「並べ替えせず」に、Ａ２からＡ１３までＡ，Ａ，Ｂ，Ｄ，Ｆ，Ｆ，Ｆ，Ａ，Ａ，Ｂ，Ｂ，Ｅと入っているとして（Ａ１は見だしの商品名と仮にする。必須）
（２）Ａ３からＢ１３まで範囲指定して
　　データ－集計（Ｂ）－グループの基準「商品名」
　　「集計の方法」は「データの個数」－　　集計するフィールド「商品名」－「集計行をデータの下に挿入する」をチェック－ＯＫで
表ができるが、ブック名の下の集計レベル「１２３」の
「２」をクリックすると
商品名
Ａ データの個数 2
Ｂ データの個数 1
Ｄ データの個数 1
Ｆ データの個数 3
Ａ データの個数 2
Ｂ データの個数 2
Ｆ データの個数 1
総合計 12
となります。

お礼

imogasi 様

再度のご回答ありがとうございます。

ご指示の方法で最終のデータ個数が現れました。
目で数えていることを思うと、10倍早く、正確に
出来ます。後で、ちょっと細工をして、
横並びにコピー＆ペーストで済むわけです。
勉強になりました。

今回は＃3の回答者様の方法(複雑な関数の意味を理解しておりませんが)、
これをセットしておき、対処することにしました。

皆様のご回答に本当に感謝しております。
ありがとうございました。

回答No.2

imogasi

考え易くするため、行と列を入れ替えて見ました。
Ａ１：Ｈ２までに下記テストデータを入れます。
Ａ Ｂ Ｃ Ａ Ｄ Ａ Ｂ Ｃ
1 2 3 2 4 5 6 7
Ａ１：Ｈ２を範囲指定して、編集－コピー
Ａ４を指定して、編集－形式を選択して貼りつけ－（形式を選択して貼りつけの画面右下の）「行と列を入れ替える」－ＯＫ
これでＡ４：Ｂ１１に
Ａ 1
Ｂ 2
Ｃ 3
Ａ 2
Ｄ 4
Ａ 5
Ｂ 6
Ｃ 7
が現われます。本例では仮にＡ３に品名、Ｂ３に数量と入れます。
（２）Ａ４からＢ１１まで範囲指定して
データ－並べ替え－第１キーは品名Ａ列、第２キーは
数量Ｂ列を指定してＯＫ
品名 数量
Ａ 1
Ａ 2
Ａ 5
Ｂ 2
Ｂ 6
Ｃ 3
Ｃ 7
Ｄ 4
となりました。
（３）Ａ３からＢ１１まで範囲指定して
　　データ－集計（Ｂ）－グループの基準「品名」
　　「集計の方法」は合計（「データの個数」でも出来る）－　　集計するフィールド「数量」－「集計行をデータの下に挿　　入する」をチェック－ＯＫで
－－－－
品名 数量
Ａ 1
Ａ 2
Ａ 5
Ａ 計 8
Ｂ 2
Ｂ 6
Ｂ 計 8
Ｃ 3
Ｃ 7
Ｃ 計 10
Ｄ 4
Ｄ 計 4
総計 30
となります。
ブック名のすぐ下の「１２３」の「２」をクリック
品名 数量
Ａ 計 8
Ｂ 計 8
Ｃ 計 10
Ｄ 計 4
総計 30
となります。
また行と列の入れ替えコピーをします。
ここで明細を消したいのだが、残念ながらその方法がわかりません。縦列のままではダメですか。

補足

imogasi 様

ご回答ありがとうございます。

imogasi様のご回答を理解できましたが、
私の説明が不足しており、求める答えとなりません。
申し訳ありませんが、ここに、不足な点を記入します。

アルファベット(商品名)；Ａは、Ａが１個売れたことを意味します。

Ａ，Ａ，Ａ，・・・では、Ａが(時間の経過で連続して)３個売れたことになります。
（データ表は、右方向に60セルがあり、60個をオーバーすると、
2行目から、また、商品名が始まります：アルファベットだらけの表です）

したがって、以下テストデータの数量はすべて”1個”です。
（Ａ Ａ Ｂ Ｃ Ｃ Ｃ Ａ Ｄ ・・）は、Ａが2個売れて、次にＢが1個、
Ｃが3個、またＡが１個売れて、・・・・　です。
Ａ 2、Ｂ　1、Ｃ　3、Ａ 1、Ｄ 1、・・・・　と整理できるのが、希望です。

売上個数集計でなく、売れた品名の順番-個数の変化を確認するのが
目的です。

なお、整理するのに、行と列を入れ替えることはＯＫです。

ご面倒でもよろしくお願いします。

回答No.1

o_tooru

こんばんわ、疑問は尽きませんね。

さてご質問の件ですが、ピボットテーブルあたりが使えそうな気がしますが。・・・Ａがいくつ売れて、Ｂがいくつ売れたかをカウントするので有れば使えるのですが宇。

ただ、データーが横方向というのがちょっと難しいですね。どうして横方向なのでしょうか？

また、Ａが連続して２個売れたというのがよく分かりません。全体としてはＡは４つ売れていますよね？

Ａが売れた総計ではなく、連続して売れたことに意味を求めるので有れば、手計算でやる方法が適している気がしますが・・・

ちょっと、試してみてください。分からなければ説明しますが。

お礼

o_tooru　様

今回の私の質問に
ご回答いただきありがとうございました。

私の抱えていた問題は、＃3の回答者様の方法で100％満足しましたので、
質問を締め切ります。

ありがとうございました。

補足

o_tooru　様

ご回答ありがとうございます。

昨夜は質問をしてから早めに就寝のため、ご連絡が遅くなりごめんなさい。
補足です。

1）ピボットテーブルあたりが
　ピボットテーブルと訊いて、訳が分からない世界で困っております。

2）データーが横方向
　これは、縦方向でも構いません。並べ替えしますので。

3）Ａが連続して２個売れた
　時間の経過で、Ａが1個売れて、その後また、Ａが１個売れた。という意味。
　Ａが２個売れたと考えても良いです。

4）全体としてはＡは４つ売れていますよね
　Ａが売れた後、別の商品が売れて、またＡが売れ、計４個ですが、
　その間に、別の商品が売れたことがポイントで、その時間的な売れ行き変化が
　作業の目的です。（＃2の回答者様への補足も参照してください。）

5）手計算でやる方法が適している
　おっしゃるとおりで、今は目を真っ赤にして手計算です。

よろしくお願いします。