エクセル関数について

質問

会社のある集計業務を退社する方より引き継いだのですが、エクセルの関数で理解ができない内容があり困っています。

=SUMPRODUCT((MIDB($H$6:$H$3017,1,7)=N3020)*(($Z$6:$Z$3017)=$M3021))

この関数式はどんな処理をしているのか、どなたかご教示いただきたく思います。

よろしくお願いいたします。

回答No.2

Cupper-2 · ベストアンサー

=SUMPRODUCT((MIDB($H$6:$H$3017,1,7)=N3020)*(($Z$6:$Z$3017)=$M3021))
これを少しだけ分かりやすく書き直すと
　=SUMPRODUCT((MIDB(H6:H3017,1,7)=N3020)*((Z6:Z3017)=M3021))
になります。
（「絶対参照」「複合参照」を「相対参照」にしただけですけどｗ）

で、本題。
　H列のセルの先頭から７ビット目からの値とN3020セルの値、
　Z列のセルの値とN3021セルの値
　の両方等しい行数を数えている
と言うことなんですけど...。

　　心当たりありますか？

以下、この関数の理解に必要な情報を並べてみます。
まず、SUMPRODUCT関数（サムプロダクトかんすう）は理解できますか。
SUMPRODUCT関数は、同じ行のセルを掛け、各行の合計を求める関数です。
　A1*B1 + A2*B2 + A3*B3 …
表にするなら、
　１行目はリンゴの単価（A列）と数量（B列）
　２行目はミカンの単価（A列）と数量（B列）
　３行目はバナナの単価（A列）と数量（B列）
　そしてその合計の値段を求める
・・・ような計算を行う関数と覚えてください。

この質問の例では結構面倒というか、ちょっとイレギュラーな使い方をしています。
本来あるSUMPRODUCT関数の特性を使って、複数の条件で一致しているセルの数を調べる事ができるんです。
（Excel2007以降ではSUMIFS関数で楽勝にできちゃうんですけど
　それ以前のバージョンでは、こーやって無理やり計算させていたんですね）
別の書き方をすると

　A6セルに　=AND(IF(MIDB(H6,1,7)=N3020),IF(Z6=M3021))*1
　A7セルに　=AND(IF(MIDB(H7,1,7)=N3020),IF(Z7=M3021))*1
　A8セルに　=AND(IF(MIDB(H8,1,7)=N3020),IF(Z8=M3021))*1
　・・・
　A3017セルに=AND(IF(MIDB(H3017,1,7)=N3020),IF(Z3017=M3021))*1
　=SUM(A6:A3017)

とかになるんですけど、これはこれで面倒すぎ。
それをSUMPRODUCT関数一発でやっているに過ぎません。
（質問の例では、SUMIFS関数と言うよりも
　合計する条件がひとつだけのSUMIF関数的な使い方になっています）

ちなみに
　条件を満たしたときの”TRUE”
　条件を満たさないときの”FALSE”
は、四則演算するとき、TRUEは１、FALSEは０として扱われます。
ですので
　MIDB(H6,1,7)=N3020
の結果と
　Z6=N3021
の結果の積を取れば、
６行目のH列の値とZ列の値が決められた値になっていれば１が返ってくると言う仕組みです。
で、この値を６行目から３０１７行目まで行い合計を求めているわけですね。

・・・ああ、説明下手くそ！（自虐

お礼

大変詳細にご教示いただき、感謝しております。
ありがとうございました。

回答No.3

bunjii

>会社のある集計業務を退社する方より引き継いだのですが、エクセルの関数で理解ができない内容があり困っています。
会社の仕事で分からないことは上司に聞くのが原則です。
あなたが理解できないのはあなたの責任ではありません。
理解できないあなたに業務を引き継がせた上司の責任です。

実際のデータを参照しながら説明を受けないと理解しにくいので第三者に相談するような事柄ではありません。

お礼

ありがとうございました。

回答No.1

kagakusuki

$H$6:$H$3017の範囲内に入力されている文字列の中で、「全角文字は2バイト、半角文字は1バイト、という数え方で、1文字目(即ち文字列の先頭)から7バイト分の文字列」がN3020に入力されている文字列と等しく、尚且つ、同じ行のZ列に入力されている値が$M3021に入力されている値に等しい、という条件を満たしている行が何行あるのかという事をカウントしている関数です。

お礼

大変わかりやすいご回答ありがとうございました。
助かりました。