概要
使えそうで使う機会がほぼない、しかし知っておけば役に立つかもしれないOFFSET関数の裏技を紹介します。
SUMIF関数とOFFSET関数を組み合わせれば、やや変則的な表においても条件と一致するデータに関連した指定範囲の数値の合計を算出することができます。
経緯
仕事中、他部署から以下のExcel表について相談を受けました。
横一列に「取引先名(〇月)」、「金額」、「取引先名(〇月)」、「金額」．．と入力されており、各取引先ごとの合計金額を別シートに算出したい。
なに？ このヘンテコなExcel表は？
集計することを全く想定していなかったのか、Excelの本領を発揮する上での大事な鉄則が何一つ守られていません。そして意味不明な状態のまま、かなりの時間をかけて既に作りこまれています。
絶望的ともいえる内容は以下のとおり
・１つのセルに取引先名と取引日が一緒に入力されている
・取引先名の次のセルに金額が入力され、その次のセルにまた別の取引先名・・といった感じで異なるデータ形式が同一列(または同一行)に混在している
・データ入力されている取引先名のカタカナは半角、取引日の前カッコは半角、後カッコは全角、そして集計シートの取引先名の項目のカタカナは全角と、まるで統一されていない
ふぅ～ やるしかないか。
ということで、検索対象となるデータと項目名の半角・全角は統一するとして、今回はSUMIF＋OFFSETで変則的な集計を可能にする裏技を紹介したいと思います。
特徴
同一列または同一行内に項目名と値が交互かつ連続で入力されている場合においても、検索値と一致する項目名の隣のセルを合計していくことができる
完成イメージ
横一列から集計する場合
縦一列から集計する場合
数式について
使用する数式
横一列の場合
=SUMIF(参照範囲,条件,OFFSET(参照範囲,0,1))縦一列の場合
=SUMIF(参照範囲,条件,OFFSET(参照範囲,1,0))解説
指定した参照範囲(配列内)にて条件と一致するセルの１つ右隣のセルの合計を算出しています。
注目すべき点は、SUMIF関数で指定する合計範囲を「OFFSET(参照範囲,0,1)」とすることで、配列内にて条件と一致するセルの隣のセルを合計している点です。
ただ、これについてはまともに意味を考えると混乱してしまうため、あまり複雑なことは考えずに１つの構文として覚えておきましょう。
ちなみに、本来のSUMIF関数の使い方は以下のとおりとなります。
各項目に対する値が別の行または列に入力されていれば、合計範囲の行または列を指定するだけで簡単に条件と一致するセルに関連した別の行または列の値の合計することができます。
今回はそれができない場合に少し役立つ、ちょっとした裏技となります。
コメント