EXCELの集計方法につきまして、ご教授をお願い致します。

EXCELで複数の数値の中で、上位3個の数値の合計を算出する場合。
通常はA列~K列までに数値が入っている場合
=LARGE(A1:K1,1)+LARGE(A1:K1,2)+LARGE(A1:K1,3)
のように算出するのが一般的ですが・・・。

B、D、F、H、J列を集計対象から除きたい場合は、どのようになりますでしょうか?
すなわち A、C、E、G、I列のみの上位3個の数値合計を算出したいのです。

すみませんが、よろしくお願いいたします。

A 回答 (5件)

=LARGE((A1,C1,E1,G1,I1),1)+LARGE((A1,C1,E1,G1,I1),2)+LARGE((A1,C1,E1,G1,I1),3)


のように。

=SUM(LARGE((A1,C1,E1,G1,I1),{1,2,3}))
でもいいです。
    • good
    • 0
この回答へのお礼

ご回答下さり有り難うございます。
おかげ様で上手く集計ができました。ありがとうございます。

上記で空白列が有り、対象セルに数字が入っているのが3つに満たない場合はエラーが出ますが、
このエラーを出さない方法ってあるのでしょうか?

誠にご面倒ですが、お解りでしたらお教え願います。

お礼日時:2011/04/19 00:05

B,D,F,H,Jの列のデータが、数値以外が無ければ(文字列や空白セルばかり)なら


=LARGE(A1:K1,1)+LARGE(A1:K1,2)+LARGE(A1:K1,3)や=SUM(LARGE(A1:K1,{1,2,3}))
がそのまま使えるようです。
B,D,F,H,Jの列のデータに数値がありますか。
    • good
    • 0
この回答へのお礼

ご回答下さり有り難うございます。

実はB、D、F、H、J列にも数字が入っております。
従って上記計算式は使えません。

有り難うございます。

お礼日時:2011/04/19 20:48

=SUM(LARGE(IF(MOD(COLUMN(A1:K1),2),A1:K1),{1,2,3}))

    • good
    • 0
この回答へのお礼

ご回答下さり誠に有り難うございます。
この形だとエラーが防げるんですね。
勉強になりました。

お礼日時:2011/04/19 20:43

ん?


とりあえず次のようにすればエラーは防げます。
=IF(COUNT(A1,C1,E1,G1,I1)>0,LARGE((A1,C1,E1,G1,I1),1))+IF(COUNT(A1,C1,E1,G1,I1)>1,LARGE((A1,C1,E1,G1,I1),2))+IF(COUNT(A1,C1,E1,G1,I1)>2,LARGE((A1,C1,E1,G1,I1),3))

あるいは空っぽにするからイケナイので,空のセルには例えばゼロを埋めてゼロは表示しないようにしてください。



>…場合はエラーが出ます

そういう状況では,あなたが最初にご質問された
=LARGE(A1:K1,1)+LARGE(A1:K1,2)+LARGE(A1:K1,3)
などでも,最初からエラーになります。

あんまり思いつきで「実は実はあれもこれもどれもそれも」と後出しで質問を追加垂れ流しされると,付き合わされる方も大変です。回答する側も,後出しで美味しいところだけさらっていった方が二度手間も無くて良いですしね。

具体的にやってみると新しい状況が発生してそちらも追加で解決しなきゃならない事が判明した,それ自体は勿論悪いことでも何でもありませんし実際よくある事ですが,そういう時は一回ご相談を区切って,新たにご質問として投稿し直してください。そういう問題だと判って取り組めば,それならそれでまたちょっと違うアプローチの方が適しているという事もあるかもしれません。
    • good
    • 0
この回答へのお礼

再びご回答下さり誠に有り難うございます。
お蔭様で問題が全て解決いたしました。

仰るとおり、失礼な追加質問をした事を反省しております。
申し訳ありません。

No.1でほぼ解決させて戴いたのですが、新しい状況でエラーが発生してしまいました。
以後、気を付けます。

有り難うございました。

お礼日時:2011/04/19 20:36

私はワーク列を使います。


L列に=A$1、M列に=C$1…で、後はL~P列を範囲にすればOKです。
    • good
    • 0
この回答へのお礼

ご回答くださり有り難うございます。

ワーク列ですか?そのような算出方法もあるんですね。
少し難しそうですが、勉強になりました。
有り難うございます。

お礼日時:2011/04/19 00:07

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Qエクセル プルダウンの作り方

お世話になります。
エクセルでプルダウンの作り方を教えていただけませんでしょうか?
住所を▼のボタンでクリックしたら【北海道,青森,秋田,岩手・・・】などの選択ができるようにしたいのです。
宜しくお願いします。

Aベストアンサー

メニューから「データ」、「入力規則」、「設定」で「入力値の種類」を「リスト」を選択します。
そうすると「元の値」という表示がでますので、そこで前もって作っておいたリストの範囲を指定します。
多くないのでしたら、そのままそこにカンマで区切って入力しても出来ます。

QExcelの集計方法について(集計KEYが2個ある場合です)

WinxpでExcel2002を使用しています。

A列  B列   C列
日付  性別  人数

上記の表があります。

SORTで日付(降順)、性別(昇順)に並べ替えを行い、その結果を以下のように人数を集計するには、どうすれば良いでしょうか?
性別計  男 ○人 
性別計  女 ×人
日付計   ○+×人

尚、合わせて、日付計で、改ページも行いたいです。

ご教示宜しくお願いします。
 

Aベストアンサー

「データ」「集計」で「日付」をキーにして「人数」を「合計」
「グループ毎に改ページを挿入する」にチェック
もう一度
「データ」「集計」で「性別」をキーにして「人数」を「合計」する。
「現在の集計表と置き換える」のチェックをはずす。

本当は「データ」「ピボットテーブル」を使ったほうが楽なんですけどね。

Qエクセルでプルダウンメニューの作り方

  エクセルの画面で、よく三角形を逆さまにした形をクリックするといくつかメニューが出てき、どれかを選べるようになっていますが、その作り方を教えてください。
 会社で人事を担当していますが、三角形(プルダウンボタン)をクリックすると社員氏名一覧が表示され、そこから選択できるようにしたいのです。
 しばらく自力でいろいろやってみましたが、さっぱり見当がつかず、どうやればいいのか分かりませんでした。よろしくお願いします。

Aベストアンサー

こんばんは!
当方使用のExcel2003での一例です!

↓の画像のようにSheet2に名簿表を作成しておきます。
画像ではSheet2のA2セル以降を範囲指定 → 名前ボックスに仮に「名簿」と入力しOK
これで範囲指定したセルが「名簿」と名前定義されましたので、

Sheet1のリスト表示させたいセルを範囲指定 → メニュー → データ → 入力規則
→ リスト → 「元の値」の欄に
=名簿
としてOK

これでSheet1のセルをアクティブにすると右側に下向き▼が表示されますので、そこをクリック!
これで希望に近い形にならないでしょうか?
Excel2007の場合は↓のURLが参考になるかもしれません。

http://www.eurus.dti.ne.jp/~yoneyama/Excel2007/excel2007-ny_kis2.html

尚、同一Sheetに「名簿表」を作成する場合は名前定義する必要はなくて
「元の値」の右側の四角をクリックし、リスト表示したいセルをそのまま範囲指定すればOKです。

以上、お役に立てば良いのですが・・・m(_ _)m

こんばんは!
当方使用のExcel2003での一例です!

↓の画像のようにSheet2に名簿表を作成しておきます。
画像ではSheet2のA2セル以降を範囲指定 → 名前ボックスに仮に「名簿」と入力しOK
これで範囲指定したセルが「名簿」と名前定義されましたので、

Sheet1のリスト表示させたいセルを範囲指定 → メニュー → データ → 入力規則
→ リスト → 「元の値」の欄に
=名簿
としてOK

これでSheet1のセルをアクティブにすると右側に下向き▼が表示されますので、そこをクリック!
これで希望に近い形にならない...続きを読む

QExcelで特定の数値が入った行の別列の数値を集計

ExcelのA列にそれぞれ空白 or 1、2、3いずれかの値が入っています。
B列には行ごとに異なる数値(1、2、3、4)が入っています。
A列とB列の行数は同じです。

それぞれの数値を下記の通りにまとめたいと考えています。
・A列に「1」が入っている行のB列の数値を集計し、その数をA100に入れる
・A列に「2」が入っている行のB列の数値を集計し、その数に1.5を掛けた値をA101に入れる
・A列に「3」が入っている行のB列の数値を集計し、その数に2を掛けた値をA102に入れる

上記はどのような計算式で実現できるでしょうか?
よろしくお願いします。

Aベストアンサー

A100 =SUMIF(A2:A10,1,B2:B10)
A101 =SUMIF(A2:A10,2,B2:B10)*1.5
A102 =SUMIF(A2:A10,3,B2:B10)*2
の式を入れます
添付画像参照してください <(_ _)>

Qエクセル(Excel) 納品書の作り方【画像修正版

昨日http://oshiete.goo.ne.jp/qa/7348426.htmlで質問させていただき、詳しくご回答いただき少し進んだのですが、状況が変わったので改めて質問させていただきます。

■エクセル(Excel)で納品書の作成をしています。
シート1に納品書、シート2に商品マスタ(一覧)を作っていて、シート2の一覧を反映させて
納品書に番号を打ち込むだけで、商品名・単価までが出るシステムを作りたいのですが、
昨日のご回答の中の「VLOOKUP」?を入れて、自分なりにマス目の数字を変えてやってみたのですが
反映されずN/?のようなエラーになってしまいます。

※画像が見にくかったのでシート<CENTER></CENTER>だけにしました。

1、上記のように、シート2との関連付けの係数を、写真の場合の数字で教えてください。

2、合計と、合計から20%を引いた数値を割り出す関数も、写真の数字で御願いします。

宜しくご教授お願い致します。

Aベストアンサー

こんばんは!
前回投稿した者です。

当方もかなり古い(人間も古い!なぁ~んちゃって!)Excel2003を使用しています。
↓の画像のようにSheet2にデータを作成しておきます。

#N/A というエラーは、「検索値」がない!ということですので
お示しの画像のB列にSheet2のA列にないデータを入力するとそういったエラーが表示されます。

画像のセル配置ですと
C4セルに
=IF($B4="","",VLOOKUP($B4,Sheet2!$A:$C,COLUMN(B1),0))
(「$」マークの位置に気を付けてください)
という数式を入れD4セルまでオートフィルでコピー!
そのまま最後の24行目までコピーしておきます。

F4セルには
=IF(COUNTBLANK(B4:E4),"",D4*E4)
という数式を入れ、F24までオートフィルでコピー!

これでB列に商品番号を入力すればSheet2のデータが反映され、
E列に数量を入力でF列に金額が表示されると思います。

最後に合計金額のF26セルは
=IF(COUNT(F4:F24),SUM(F4:F24),"")
手数料のF27セルは
=IF(F26="","",F26*0.2)

これで何とか形にならないでしょうか?

※ 振込金額の欄は不明ですので手を付けていません。

参考になりますかね?m(_ _)m

こんばんは!
前回投稿した者です。

当方もかなり古い(人間も古い!なぁ~んちゃって!)Excel2003を使用しています。
↓の画像のようにSheet2にデータを作成しておきます。

#N/A というエラーは、「検索値」がない!ということですので
お示しの画像のB列にSheet2のA列にないデータを入力するとそういったエラーが表示されます。

画像のセル配置ですと
C4セルに
=IF($B4="","",VLOOKUP($B4,Sheet2!$A:$C,COLUMN(B1),0))
(「$」マークの位置に気を付けてください)
という数式を入れD4セルまでオートフィルで...続きを読む

Q【EXCEL】条件+上位○位までを集計したい

以下のようなデータがあります
(列)a,b,c
2,aaa,1000
2,bbb,1500
2,ccc,800
3,ddd,1000
3,eee,1500
3,fff,800

a=コード、c=金額

ここで以下の条件で集計したいのです。
「コードが2のもので上位2位までの金額を集計したい」

このような事を関数で行うとどうなりますでしょうか。
調べましたがお手上げでした。
すみませんが宜しくお願いします。

Aベストアンサー

こんばんは。

>例えばコードが3で上位30位までの金額を集計したい。と。

=SUMPRODUCT(LARGE(($A$1:$A$100=3)*($C$1:$C$100),ROW(A1:A30)))

というような具合になりますね。

>SUMPRODUCTもROWもまだモノにしていませんが、これを期に理解したいと思います。

少し、『配列数式』のことを書いておきます。

SUMPRODUCT は、配列数式のおまじないのようなもです。

例えば、

=A1:A10 としたら、だいたいは #VALUE! というエラーになるはずです。
なぜか考えたことありますか?

空のセルの A1:A10 をマウスで範囲を取って、F9 を押してみると、
={0;0;0;0;0;0;0} 

(『{}』この中カッコが、配列の中に収まっているというサインです。)

というように見えます。その「;」 が、邪魔で、#VALUE!のエラーが出ています。
しかし、数値としての値は存在しています。それを SUMPRODUCT()で囲むと、きちんとした数値が出てきます。これは、Excelの計算用キャシュから、SUMPRODUCT()が、実体化させてワークシートに表していると考えたらよいかと思います。これは、セル1つ1つに、そのような入れ物があるようです。

 =SUMPRODUCT(LARGE(($A$1:$A$6=2)*($C$1:$C$6),ROW(A1:A2)))
以上の式から、SUMPRODUCTを取ると、

= LARGE(($A$1:$A$6=2)*($C$1:$C$6),ROW(A1:A2))
ですね。

これに、また、マウスで、「LARGE」から、最後尾の 「))」 までの範囲を取って、F9 を押すと、
 ={1500;1000}

という値が見えるはずです。つまり、値は出ているけれど、実体化していないので、SUMPRODUCTをつけてあげます。

このほかにも、一旦、F2を押して、再度、SHIFT キーとCTRL キーを押しながら、ENTER を押すと、やはり、式が実体化して、同じ値が出てくるはずです。これを、『配列の確定』と呼んでいます。

つまり、
分解すると、
= SUM(LARGE(($A$1:$A$6=2)*($C$1:$C$6),1), LARGE(($A$1:$A$6=2)*($C$1:$C$6),2)) に他なりませんね。

このROW(A1:A2)のことを『インクリメント(Increment)』 と呼んでいます。
それから、($A$1:$A$6=2)*($C$1:$C$6)のように、高さ(A1:A6)のように、両方の中の範囲(高さ・幅)を合わせることを、『配列の共通部分参照』と呼んでいます。

=ROW(A1:A2)
も同じようにして、マウスで、範囲を取って、F9で見てください。
={1;2}
というようになっているはずです。
引数に、1,2,3,.... と数字を与えたいときに、このような方法を使います。

例えば、
 =SUMPRODUCT(ROW(A1:A10))
とすれば、55 という値が出てきます。それは、1~10までを足した計算です。

なお、以下は、私が、一番最初に、配列のことを知ったサイトです。(当時は、2000でしたが)

http://support.microsoft.com/default.aspx?scid=kb;ja;402181
[XL2002] n 行おきの合計を算出する方法

ただ、配列数式は、Excel 2000以下ですと、その制限があります。また、関数の中でも、MODEやTRANSPOSE, MINVERSE,MMULT など、配列しか取らないものもあります。

私の記憶の範囲では、SUMPRODUCT()だけが、配列の確定をしなくて済む関数のはずです。また、SUMPRODUCTの中は、条件式などは取れませんから、その場合は、配列の確定をしなくてはなりません。また、配列の確定をした場合は、若干、計算スピードが落ちるようです。

引数にも、LARGE, SMALL などのように、配列を引数に取れる関数もあれば、同じ仲間のRANKは、配列を数式に取れません。私は、その理由を、RANKのほうが出来た年代が古いからだと思っています。RANKやCOUNTIF などの検索範囲に配列が取れるとひじょうに便利になると思っていますが、改変する可能性は薄いようです。また、配列と共に良く使われる関数は、MATCH関数です。

最後に、入力規則や条件付き書式は、特殊な使い方にはなりますが、配列の確定せずに値を受け取ります。

例: 入力規則で、半角文字を制限させる

=AND(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>47,CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<123)

長文、お付き合い下されたら、感謝いたします。
この機に、疑問に思うことは、質問されたほうがよいかと思います。

こんばんは。

>例えばコードが3で上位30位までの金額を集計したい。と。

=SUMPRODUCT(LARGE(($A$1:$A$100=3)*($C$1:$C$100),ROW(A1:A30)))

というような具合になりますね。

>SUMPRODUCTもROWもまだモノにしていませんが、これを期に理解したいと思います。

少し、『配列数式』のことを書いておきます。

SUMPRODUCT は、配列数式のおまじないのようなもです。

例えば、

=A1:A10 としたら、だいたいは #VALUE! というエラーになるはずです。
なぜか考えたことありますか?

空のセ...続きを読む

Qエクセル(Excel) 納品書の作り方【改めて】

昨日http://oshiete.goo.ne.jp/qa/7348426.htmlで質問させていただき、詳しくご回答いただき少し進んだのですが、状況が変わったので改めて質問させていただきます。

■エクセル(Excel)で納品書の作成をしています。
シート1に納品書、シート2に商品マスタ(一覧)を作っていて、シート2の一覧を反映させて
納品書に番号を打ち込むだけで、商品名・単価までが出るシステムを作りたいのですが、
昨日のご回答の中の「VLOOKUP」?を入れて、自分なりにマス目の数字を変えてやってみたのですが
反映されずN/?のようなエラーになってしまいます。

※画像が貼り付けてあります。商品名は1番以外伏せさせていただいています。
くっつけてありますが、左側がシート1・右側がシート2です。

1、上記のように、シート2との関連付けの係数を、写真の場合の数字で教えてください。

2、合計と、合計から20%を引いた数値を割り出す関数も、写真の数字で御願いします。

宜しくご教授お願い致します。

Aベストアンサー

画像がいまいちよく見えないのですが、納品書の項目は左から、No、商品番号、商品名、単価、数量、金額でいいのでしょうか(名前は多少違っていても意味があっていればもんだいないです)

でしたら、
C1セルに=IF(ISBLANK(B2),"",VLOOKUP(B2,Sheet2!$A$2:$C$200,2,FALSE))
D1セルに=IF(ISBLANK(B2),"",VLOOKUP(B2,Sheet2!$A$2:$C$200,3,FALSE))
E1セルは空白で
F1セルに=IF(D2="","",D2*E2)
といれて、C1からF1までをコピーしてその下の行にタテに貼り付ければ出来ますよ。
おそらくエラーが出たのは、コピーしたときにVLOOKUP関数の最初のセルの指定がずれてしまっているのでは無いかと思いますよ。     

QExcelで2つ1組の数値の内、1つの数値を別の数値とセットにする方法

①のシートのA列に型番、B列にシリアルが入っており、②のシートのA列に型番のみが入っています。
①と②の型番は完全一致しているものもあれば、型番の末尾が途切れてしまい完全一致しないものもありますし、数文字しか一致しないものもあります。
②の型番の隣に①のシリアルを入れたいのですが、何かうまく関数を使えば簡単にできるでしょうか?

型番をそれぞれ昇順にして貼り付けようとしたのですが、①と②の型番が完全一致ではないため、同じ並びにならず、そのまま貼り付けることができません。
何かいい方法があれば教えて下さい。
宜しくお願い致します。

例】
①シート
A列 B列
ABC 1234
PQR 2345
STU 5678
XYZ 1209
②シート
A列 B列
TU
XYS
PQR
BC

分かりづらかったらすみません。。。
宜しくお願い致します。

Aベストアンサー

部分的に重複しているような型番もあるのでしょうか?
ABCとABDという型番があり、②に欠けたABという型番があった場合、ABCかABDか判断する方法が無いと思います。
また、②のXYSというのはXYZが間違えて?入力されているような場合を想定していると思われますが、それもどの程度の一致で判断するのでしょう?
②にXYSとあって、①にXYZとEYSが存在した場合、どちらと判定すればよいか分からないと思います。

何らかの判断基準を決めたとして、
まずVLOOKUPで完全一致を探し、一致したもののシリアルを取得します。
フィルタを使い、エラー表示のものだけ表示し、式の内容を変更します。

判断基準の優先度が高いものから順に試します。
場合によっては①のA列とB列の間に列を挿入し、判定用の補助セルとして使います。
例えば、前から5文字一致したら同一とみなす。とした場合、
VLOOKUPとLEFTにより、左から5文字抽出したものが一致した場合にシリアルを取得します。
先ほど同様にフィルタでエラーのものだけ表示させ、また別の条件の式を入力します。

例えば今度は右から5文字一致したら、とかでしょうか。
あるいは②の文字が欠けているとして、②の全てを含むセル。という条件もあるでしょう。
この場合SUMPRODUCTやFIND、INDIRECT、ROW等を使い、一致するものがある列の行番号を取得する必要があると思われます。

データの数にもよりますが、残り少なくなれば判定式を考えるより目で見た方が早いかもしれません。

自分が思いついたのはこの程度です。
人間が条件を指定してあげないと機械も判別できないですよ。
もっと上手いやり方もあるかもしれないので、他の回答者に期待。

部分的に重複しているような型番もあるのでしょうか?
ABCとABDという型番があり、②に欠けたABという型番があった場合、ABCかABDか判断する方法が無いと思います。
また、②のXYSというのはXYZが間違えて?入力されているような場合を想定していると思われますが、それもどの程度の一致で判断するのでしょう?
②にXYSとあって、①にXYZとEYSが存在した場合、どちらと判定すればよいか分からないと思います。

何らかの判断基準を決めたとして、
まずVLOOKUPで完全一致を探し、一致したもののシリアルを取得します。
...続きを読む

Qエクセル2007でプルダウンで選んだものに反応

Excel2007でプルダウンで選んだものに反応して隣のセルが自動入力される方法(エクセル2007)
A1をプルダウンで「猫」「犬」から選べるようにし、「猫」を選んだ場合B1に自動に「111」が、「犬」を選んだ場合B1に自動に「222」と入力されるようにしたいです。
ご教授の程、宜しくお願いします。

Aベストアンサー

VLOOKUP関数での方法です。
(1)別シートに入力文字列と対応コード表を作成。(仮にSheet2のA:B列範囲で順不同)
(2)B1に=IF(COUNTIF(Sheet2!A:A,A1),VLOOKUP(A1,Sheet2!A:B,2FALSE),"")を設定
   入力文字列が存在しない場合は空白としています。

Q列を数値で選択する方法をご教授ください

エクセル2007です。

Columns("a:b").Select
なら列を選択できますが、
Columns(1 & ":" & 2).Select
だと
「実行時エラー1004、アプリケーション定義またはオブジェクト定義のエラーです」
になってしまいます。

数値にしたい理由は、変数を代入したいからです。
アドバイスよろしくお願いします。

Aベストアンサー

Columns("1:2")といった書きぶりはありませんので,別の方法を使うしかありません。

実際の所,趣味に応じて書きぶりは多々ありますが,たとえば
range(columns(1), columns(2)).select
など。


人気Q&Aランキング

おすすめ情報