新規記事の投稿を行うことで、非表示にすることが可能です。
2015年06月04日
〈SUM/OFFSET〉上位から指定した順位までの数値を合計したい：膨大なデータの簡単処理
受注高ランキングなどから、上位の数値だけ合計してみましょう♪
なんて。
そんなニーズはありませんか？？
膨大な売上表からそこだけを目視と電卓で抽出する、、、、
なんてことやってないですよね？
それこそこういった複雑な計算は手作業だとミスの原因。
ボスに怒られちゃいますので、
excelちゃんに仕事をお願いしちゃいましょう。
しかしexcelってホント色々な関数ありますよね。
今回はSUM関数の応用編です。
excel組んで格好よく処理しちゃいましょう。
※SUM関数の詳細は以下を参照ください※
上図の赤枠にある関数をご覧ください。
=SUM(OFFSET(C3,0,0,B16,1))
この関数はSUM関数でありながら、更に”OFFSET”という
数式を用いて、表の中から必要な部分の値を合計することが格好良く！できます。
さあ、いつものように数式分解をしてみましょう。
,（カンマ）で区切られた箇所をみると、以下のように3つの要素に分解されます。
①OFFSET（・・） これは基準となるセルから、
指定した行や列の数だけ移動した位置にあるセルを先頭に、
指定した高さ、幅を持つセル範囲を参照できるようにする暗号であります。
②OFFSET(C3,0,0,・・・) C3セルを先頭にするよ、という意味です。
③=SUM(OFFSET(・・・B16,1)) B16セルの値に応じて下方へ広がる“可変の範囲”を合計できるよ、という意味。
③を補足すると、引数には「基準、行数、列数、高さ、幅」を指定することができ、
ここでは”1”がそれを表しております。
ここで取り上げたサンプルでは、下に一列なので”1”とはまっていますが
もちろん2列・3列でも、2行・3行でも、この可変値を変えることで
計算するテーブルを変えることが可能になるわけです。
これ、使いこなすと恰好いいですよ♪
なんて。
そんなニーズはありませんか？？
膨大な売上表からそこだけを目視と電卓で抽出する、、、、
なんてことやってないですよね？
それこそこういった複雑な計算は手作業だとミスの原因。
ボスに怒られちゃいますので、
excelちゃんに仕事をお願いしちゃいましょう。
しかしexcelってホント色々な関数ありますよね。
今回はSUM関数の応用編です。
excel組んで格好よく処理しちゃいましょう。
※SUM関数の詳細は以下を参照ください※
上図の赤枠にある関数をご覧ください。
=SUM(OFFSET(C3,0,0,B16,1))
この関数はSUM関数でありながら、更に”OFFSET”という
数式を用いて、表の中から必要な部分の値を合計することが格好良く！できます。
さあ、いつものように数式分解をしてみましょう。
,（カンマ）で区切られた箇所をみると、以下のように3つの要素に分解されます。
①OFFSET（・・） これは基準となるセルから、
指定した行や列の数だけ移動した位置にあるセルを先頭に、
指定した高さ、幅を持つセル範囲を参照できるようにする暗号であります。
②OFFSET(C3,0,0,・・・) C3セルを先頭にするよ、という意味です。
③=SUM(OFFSET(・・・B16,1)) B16セルの値に応じて下方へ広がる“可変の範囲”を合計できるよ、という意味。
③を補足すると、引数には「基準、行数、列数、高さ、幅」を指定することができ、
ここでは”1”がそれを表しております。
ここで取り上げたサンプルでは、下に一列なので”1”とはまっていますが
もちろん2列・3列でも、2行・3行でも、この可変値を変えることで
計算するテーブルを変えることが可能になるわけです。
これ、使いこなすと恰好いいですよ♪
〈DSUM〉複数の特定条件での数字を合計したい：膨大なデータの処理
各部署の経費集計を等を行うときに、
膨大なデータベースから、欲しい情報だけ瞬時に取り出したい！
なんて。
そんなニーズはありませんか？？
膨大な売上表からそこだけを目視と電卓で抽出する、、、、
なんてことやってないですよね？
それこそこういった複雑な計算は手作業だとミスの原因。
ボスに怒られちゃいますので、
excelちゃんに仕事をお願いしちゃいましょう。
この手の話は、便利で簡単。
〈DSUM〉関数を使えば一発です！
上記の関数をご覧ください。
=DSUM(B10:D30,"4月金額",B2:C3)
いつものように数式分解をしてみます。
,（カンマ）で区切られた箇所をみると、以下のように3つの要素に分解されます。
①B10:D30 これは合計したい【データベースの表】を丸ごと指定しています。（←ここポイント！）
②"4月金額" これは【抽出したい項目】の名称を表します。
③B2:C3 これは【検索条件】を表します。
つまり翻訳すると次のようです。
「検索したい内容を【検索条件】に基づいて、【抽出したい項目】である"4月金額"について、
【データベースの表】の金額を合計してね♪」
関数って会話なんですね。まるで人が会話しているよう。
似たような関数で「SUMIF」関数ってのがあります。
こちらも参考に！
膨大なデータベースから、欲しい情報だけ瞬時に取り出したい！
なんて。
そんなニーズはありませんか？？
膨大な売上表からそこだけを目視と電卓で抽出する、、、、
なんてことやってないですよね？
それこそこういった複雑な計算は手作業だとミスの原因。
ボスに怒られちゃいますので、
excelちゃんに仕事をお願いしちゃいましょう。
この手の話は、便利で簡単。
〈DSUM〉関数を使えば一発です！
上記の関数をご覧ください。
=DSUM(B10:D30,"4月金額",B2:C3)
いつものように数式分解をしてみます。
,（カンマ）で区切られた箇所をみると、以下のように3つの要素に分解されます。
①B10:D30 これは合計したい【データベースの表】を丸ごと指定しています。（←ここポイント！）
②"4月金額" これは【抽出したい項目】の名称を表します。
③B2:C3 これは【検索条件】を表します。
つまり翻訳すると次のようです。
「検索したい内容を【検索条件】に基づいて、【抽出したい項目】である"4月金額"について、
【データベースの表】の金額を合計してね♪」
関数って会話なんですね。まるで人が会話しているよう。
似たような関数で「SUMIF」関数ってのがあります。
こちらも参考に！
