[[20200528202111]]　『重複するデータの数値を合計して返して欲しい』（ほねてぃっく）　ページの最後に飛ぶ

『重複するデータの数値を合計して返して欲しい』（ほねてぃっく）

最近Excel始めまして、壁にぶつかりました。いや他の方法はあるのですが関数に拘って解決したいので、力を貸してください。

何をやっているかといえば、毎月の売上等のデータ(元データ)を自分の見やすいシートにデータを追加していく作業です。元データからVLOOKUP(自分はINDEX-MATCHが好きなのでこっちを使っています)でデータを引っ張ってくる際に、重複したデータ(月に同じ顧客に数回販売したデータ)は1つの金額しか返してくれません。
しかしそうです、ピボットを使えば同じ販売先への売上の合計は簡単に求められます。しかしピボットは開くたびにシート名がバラバラになることも有り、マクロを作るときに不便さを感じますので、なんとか関数だけで処理したい。つまりこういうことです
　　A　B　　　　　　　　　A　B　　A列は顧客　B列は金額　という感じ
1　あ　1　　　　　　　1　あ　3　　
2　い　5　重複は合計　2　い　5　　関数だけを使い
3　あ　2　======>　3　う　5　　合計して自分のシートに返してほしい
4　う　3　　　　　　　4　え　4　　新規のものはそのまま行に継ぎ足していく
5　う　2　　　　　　　5　お　9
6　え　4　　　　　　　　
7　お　9
これを関数のみでやりたいということで四苦八苦しております
IFSやSUMIFSやCOUNTIFSなど色々ネストして試しましたが、中々うまくいきません。どうかご教授ください

＜使用 Excel：Excel2019、使用 OS：Windows10 ＞

 元のデータがSheet1にあるものとして、そのシートのC列を作業列とし、
 C1に =IF(COUNTIF($A$1:A1,A1)=1,ROW(),"")　として下にコピー。

 集計するシートで

 A1=IFERROR(INDEX(Sheet1!A:A,SMALL(Sheet1!C:C,ROW(A1))),"")
 B1=IF(A1="","",SUMIF(Sheet1!A:A,A1,Sheet1!B:B))

 として下にコピーでいいでしょうか。

（tora） 2020/05/28(木) 22:28

 こんばんは！
 私がよくやる方法ですけど、、、
 A1から始まっている時は簡単で
 データ　という名前を定義します。
 =OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A))

 重複を除いたデータを表示したい範囲を選択した状態で↓と入力して配列で確定します。
 =IF(COUNTA(データ)<ROW(),"",IFERROR(INDEX(データ,SMALL(IF(MATCH(データ,データ,0)=ROW(データ),ROW(データ)),ROW(データ))),""))
 すると

 あ
 い
 う
 え
 お
 か

 重複を除いたデータが表示されますから後は　SUMIF　で

 あ	3　=IF(A1<>"",SUMIF(Sheet1!$A$1:$A$7,A1,Sheet1!$B$1:$B$7),"")
 い	5
 う	5
 え	4
 お	9
 か	0

 みたいにします。。。
 するとデータに追加された新たなデータは下に追加されていきます。。。

 A1から始まっていない時はデータとインデックス用の配列を作って応用してくださいね。。。
 では、、では、、
（SoulMan） 2020/05/28(木) 23:03

toraさん！SoulManさん！ご教授ありがとうございます！
しかし難しすぎるので、これから1ヶ月かけてその意味を理解したいと思います
ベテランの人はROW関数をよく使いますね
素人にはこれ何の意味有るの？って関数ですが
現在地を指定する意味で大切な関数なのでしょうか
つまり起点を示す関数ですかね
（ほねてぃっく） 2020/05/28(木) 23:42

 ROW関数が行番号を取得する関数であることはご存じたと思います。
 ROW()とした場合は、その関数が入力されているセルの行番号を表示しますね。

 C1=IF(COUNTIF($A$1:A1,A1)=1,ROW(),"")
 この数式では、A列の重複しないデータが何行目にあるかを求めています。

 一方、A1=IFERROR(INDEX(Sheet1!A:A,SMALL(Sheet1!C:C,ROW(A1))),"")
 この数式ではROW(A1)と、かっこの中にA1が入っています。
  ROW(A1)ははA1の行番号、つまり 1 ですから、それならなぜ最初から 1 と書かないの?と思うのも当然です。
 でも、ROW(A1)は下にコピーしたときに(A2)、(A3)・・・となり、1、2、3・・・と連番を作ることができます。

 SMALL(Sheet1!C:C,ROW(A1))はこのことを利用して、
 Sheet1!C:Cのなかから一番小さ数字、二番目に小さい数字・・・・を見つけることができるのです。

 あとはそれをもとにINDEX関数で目的のデータを検索しています。
 最後に、データが見つからないときのエラー処理をすれば完成です。

（tora） 2020/05/29(金) 10:15

 おはようございます
ちなみにMicrosoft 365 だとunique関数というのがあるみたいです。
Excelも便利になりましたねぇ😅
http://officetanaka.net/excel/function/function/unique.htm
（SoulMan） 2020/05/29(金) 10:18

こんばんは
toraさんさらに詳しい説明ありがとうございます。
SoulManさんこのunique関数はまさに自分の望んでいる関数ですね。

お二人のやり方を両方とも試してみてその理屈を理解し、さらに自分で応用出来るようになろうと思います。
ご親切にありがとうございました。
また壁にぶつかったときはよろしくおねがいします。
（ほねてぃっく） 2020/05/29(金) 21:00

コメント返信：

[ 一覧(最新更新順) ]

[[20200528202111]] 『重複するデータの数値を合計して返して欲しい』（ほねてぃっく） ページの最後に飛ぶ

[[20200528202111]]　『重複するデータの数値を合計して返して欲しい』（ほねてぃっく）　ページの最後に飛ぶ