[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『上からn番目まで合計と条件指定の関数』（ソウ）
質問失礼します。
上から３番目を合計する場合、SUMIFとLARGEを使い以下のような４つから３番目まで合計しようとすると、
1,200
1,100
1,000
1,000
合計が4,300になってしまいます。理想は3,300なのですが、同数を上手く処理するにはどうすればよろしいでしょうか？
更に、A店舗ではAさんのみで範囲を指定かつn番目の合計 を関数処理するにはどうすればよろしいでしょうか？
回答の程宜しく御願い致します。
＜ 使用 Excel：unknown、使用 OS：Windows10 ＞
N番目という条件もどこかのセルに入力されているのでしょうか？
＞更に、A店舗では・・・・
A店舗とか、Aさんとかわかるのは 質問者さんだけです。
回答者にわかるような情報をできるだけ提供したほうがレスが付きやすいと思います。
（渡辺ひかる） 2019/08/05(月) 09:18
＞A店舗ではAさんのみで範囲を指定かつn番目の合計 を関数処理するにはどうすればよろしいでしょうか？
表の構成が分からないと答えようがありません。
A B C D
1
2
このように行列番号を記入た表をアップしてください。
（メジロ） 2019/08/05(月) 09:23
一番目について。
データの途中に空白行がないとして。
=SUM(IFERROR(LARGE(IFERROR(IF(FREQUENCY(A1:A10,A1:A10),A1:A10,""),""),ROW(1:3)),0))
と入力してShiftキーとCtrlキーを押しながらEnterキーで式を確定（確定後、式が{}で囲まれればOK）ではどうか？
（ねむねむ） 2019/08/05(月) 09:25
なお上記式はデータがA1セルから最大A10セルまでとしている。 また、データの途中でなければ（データ連続していれば）空白行があってもOK。 (上記式であれば5行目までしかデータがなくて６行目以降が空白でも構わない） （ねむねむ） 2019/08/05(月) 09:26
表はこの通りです。
B2には、=SUMIF(B3:B7,">="&LARGE(B3:B7,B1)) を入力しているのですが、同数がｎ番目の場合全て含まれてしまう為、改善をお願い致します。
B1の上から３番目は固定ではないので、セル指定でお願い致します。
B2には追加で、E1のリストでA店舗を選択している場合にはAさんのみで合計、B店舗ではBさん、C店舗ではCさん、全店舗ではAさん、Bさん、Cさんと合計の範囲を指定したいです。
宜しくお願い致します。
（ソウ） 2019/08/05(月) 10:15
=IF(E1<>"全店舗",SUMIF(OFFSET(B1,MATCH(LEFT(E1,1)& "*",A1:A15,0)-1,,COUNTIF(A1:A15,LEFT(E1,1) & "*")),">="&LARGE(OFFSET(B1,MATCH(LEFT(E1,1)& "*",A1:A15,0)-1,,COUNTIF(A1:A15,LEFT(E1,1) & "*")),B1))-(COUNTIF(OFFSET(B1,MATCH(LEFT(E1,1)& "*",A1:A15,0)-1,,COUNTIF(A1:A15,LEFT(E1,1) & "*")),LARGE(OFFSET(B1,MATCH(LEFT(E1,1)& "*",A1:A15,0)-1,,COUNTIF(A1:A15,LEFT(E1,1) & "*")),B1))-1-B1+RANK(LARGE(OFFSET(B1,MATCH(LEFT(E1,1)& "*",A1:A15,0)-1,,COUNTIF(A1:A15,LEFT(E1,1) & "*")),B1),OFFSET(B1,MATCH(LEFT(E1,1)& "*",A1:A15,0)-1,,COUNTIF(A1:A15,LEFT(E1,1) & "*"))))*LARGE(OFFSET(B1,MATCH(LEFT(E1,1)& "*",A1:A15,0)-1,,COUNTIF(A1:A15,LEFT(E1,1) & "*")),B1),SUMIF(B3:B15,">="&LARGE(B3:B15,B1))-(COUNTIF(B3:B15,LARGE(B3:B15,B1))-1-B1+RANK(LARGE(B3:B15,B1),B3:B15))*LARGE(B3:B15,B1))
一応提示されたデータで一通り検証しました。
（渡辺ひかる） 2019/08/05(月) 12:44
リストの中を(cut_tupsz,sn_ct,kjo_ct,tvhb_ct,k_ipqf_ct,kjnjo_ct,w_ct,kvohjjp_ct）
にして
全範囲は、
cut_tupsz
他は、
sn
kjo
tvhb
k_ipqf
kjnjo
w
kvohjppj
をそれぞれの範囲にした場合には可能でしょうか？
申し訳ないですが、宜しくお願い致します。
（ソウ） 2019/08/05(月) 16:45
（渡辺ひかる） 2019/08/05(月) 16:49
＞A店舗を選択している場合にはAさんのみで
とあったので、先頭の1文字を比較して集計していました
今回の条件は
文章化はされていませんが、
E1セルの文字列から 最後の3文字 _ct を除いた文字列で検索し、集計しています。
=IF(E1<>"cut_tupsz",SUMIF(OFFSET(B1,MATCH(LEFT(E1,LEN(E1)-3),A1:A15,0)-1,,COUNTIF(A1:A15,LEFT(E1,LEN(E1)-3))),">="&LARGE(OFFSET(B1,MATCH(LEFT(E1,LEN(E1)-3),A1:A15,0)-1,,COUNTIF(A1:A15,LEFT(E1,LEN(E1)-3))),B1))-(COUNTIF(OFFSET(B1,MATCH(LEFT(E1,LEN(E1)-3),A1:A15,0)-1,,COUNTIF(A1:A15,LEFT(E1,LEN(E1)-3))),LARGE(OFFSET(B1,MATCH(LEFT(E1,LEN(E1)-3),A1:A15,0)-1,,COUNTIF(A1:A15,LEFT(E1,LEN(E1)-3))),B1))-1-B1+RANK(LARGE(OFFSET(B1,MATCH(LEFT(E1,LEN(E1)-3),A1:A15,0)-1,,COUNTIF(A1:A15,LEFT(E1,LEN(E1)-3))),B1),OFFSET(B1,MATCH(LEFT(E1,LEN(E1)-3),A1:A15,0)-1,,COUNTIF(A1:A15,LEFT(E1,LEN(E1)-3)))))*LARGE(OFFSET(B1,MATCH(LEFT(E1,LEN(E1)-3),A1:A15,0)-1,,COUNTIF(A1:A15,LEFT(E1,LEN(E1)-3))),B1),SUMIF(B3:B15,">="&LARGE(B3:B15,B1))-(COUNTIF(B3:B15,LARGE(B3:B15,B1))-1-B1+RANK(LARGE(B3:B15,B1),B3:B15))*LARGE(B3:B15,B1))
（渡辺ひかる） 2019/08/05(月) 17:10
17:27 全店舗修正
最後の3文字 _ct を除いた文字列と数値を順不同にして合計を出したいのですが、修正可能でしょうか？
宜しくお願い致します。
（ソウ） 2019/08/05(月) 19:17
こんなので求まらないですか？
↓
B2セル =SUMPRODUCT(LARGE((IF(E1="cut_tupsz",1,INDEX(INDIRECT("A3:A"&COUNTA(A:A))=LEFT($E$1,LEN($E$1)-3),0)))*INDIRECT("B3:B"&COUNTA(A:A)),ROW(INDIRECT("1:"&$B$1))))
＞修正可能でしょうか？
初めから分かっている条件なのですから、あとからちょこちょこ変更せず、 初めから全部出す様にしてください。
（半平太） 2019/08/06(火) 08:45
（ソウ） 2019/08/06(火) 14:31
＞初めから分かっている条件なのですから・・・
以後気を付けます。
（ソウ） 2019/08/06(火) 14:35
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.