エクセルの関数について

質問

以前も同様の質問をさせていただきましたが、より具体的に質問させていただきます。

添付のような資料を基に下記の条件で集計したいと考えています。
どのような関数を使用すれば良いか教えて下さい。
(1)Ｂ列の中で85278を探す
(2)Ｂ列の最初の数字が1
(3)Ｂ列の右から2文字目の数字が1または2の場合

(1)(2)(3)の条件が満たされた場合、月ごとの数値を集計したいです。
よろしくお願い致します。

回答No.3

keithin · ベストアンサー

＞(1)Ｂ列の中で85278を探す
＞(2)Ｂ列の最初の数字が1

掲示のサンプルだけで見ると、これは「B列が185278で始まる」で十分に見えますが、まだ何か説明不足なのですか？

また「数字の中（の何文字目にあるか不明）を探す」じゃなく、Ｂ列の２文字目から５文字分が、という風にもっと具体的に限定できそうですが？

計算例：
Ｃ７に
=SUMPRODUCT((LEFT(B2:B6,1)="1")*(MID(B2:B6,2,5)="85278")*((MID(B2:B6,12,1)="1")+(MID(B2:B6,12,1)="2"))*C2:C6)

補足

もう一つ条件を追加しなくてはならなくなりました。
B列に挿入して、B2～B6には170か180が入力されています。

170の時に集計したい場合、
SUMPRODUCT関数の(LEFT(B2:B6,1)="1")の前に
(B2:b6="170")*と記載すれば良いのでしょうか？

宜しくお願い致します。

回答No.6

Nouble

済みません、訂正させてください

配列関数は
セル引き渡し、できません

なので、先の回答で
各々を
セルに、入れれば良い
と、したのは
間違いです

正しくは
(1)ですが
SUMPRODUCT((len(TEXT(B:B,"@"))<>len(SUBSTITUTE(TEXT(B:B,"@"),"85278","")))+0)
で、判る
と、思います

数値16桁かな?
其の内の何処に
85278が、在ろうと
見つけます。

(2)ですが、
SUMPRODUCT((LEFT(B:B,1)=1)+0)
で、判る
で、しょう

(3)ですが、
SUMPRODUCT((ABS(RIGHT(B:B,1)－1.5)=0.5)+0)　…A
で、判る
で、しょう

総合すると
SUMPRODUCT((len(TEXT(B:B,"@"))<>len(SUBSTITUTE(TEXT(B:B,"@"),"85278","")))*(LEFT(B:B,1)=1)*(ABS(RIGHT(B:B,1)－1.5)=0.5))
ですかね

同シート上の、何処か☆セルに
イコールの後に書き込むと
動作する
と、思う
の、ですが
INDEX文を、掛けてないので
もしかしたら、動作しないかな?

回答No.5

Nouble

(1)Ｂ列の中で85278を探す
(2)Ｂ列の最初の数字が1
(3)Ｂ列の右から2文字目の数字が1または2の場合
ですか？

(1)ですが
数値を文字列に変え　　　　　　　　:TEXT(B:B,"@")　…甲
希望の文字列を置換により削除し　　:SUBSTITUTE(甲,"85278","")　…乙
桁数が減ったセル数を数える　　　　:SUMPRODUCT((len(甲)<>len(乙))+0)　…壬
で、判る
と、思います

(2)ですが、
ちょっと強引ながら
左1文字取りだし　　　　　　　　　　:LEFT(B:B,1)　…丙
1かどうか見る　　　　　　　　　　　:丙=1　…丁
一致したセル数を数える　　　　　　:SUMPRODUCT(丁+0)　…癸
で、判るでしょう

(3)ですが、大凡
(2)と、左右が違うだけ
ですよね？

なので、
右ひと文字取りだし　　　　　　　　　:RIGHT(B:B,1)　…戊
1.5 引き　　　　　　　　　　　　　　:戊－1.5　…己
絶対値を取る　　　　　　　　　　　　:ABS(己)　…庚
此が0.5か、見る　　　　　　　　　　:(庚=0.5)　…辛
一致したセル数を数える　　　　　　:SUMPRODUCT(辛+0)　…A
で、判るでしょう

ので、
総合では
SUMPRODUCT((壬*癸*A)+0)
で、判るでしょう

各引用は
別々に、セルに入れて
参照させるも、よし

引用部を式に当てはめて
1式にしても良し

ですね

如何でしょうか？
お役に、立てていたなら
幸いです。

回答No.4

heisukewada

K,M,L,Nを作業領域にします。
K2に =IF(COUNTIF(B2,"*85278*"),"○","")　B2に"85278"が含まれているなら”○”を入れてね。
L2に =IF(LEFT(B2,1)="1","○","")　一番左の文字が１なら"○”をいれてね。
M2に =IF(OR(MID(B2,LEN(B2)-1,1)="2",MID(B2,LEN(B2)-1,1)="1"),"○","")　右から２番めの文字が、１か２なら　”○”　を入れてね。
N２に =IF(AND(K2="○",L2="○",M2="○"),"○","")　K2,L2,M2のみっつとも”○”なら”○”を入れてね。
６行までオートフィルで式を入れます。
C7に =SUMIF($N$2:$N$6,"○",C2:C6)　N2:N6に”○”が入っているものだけを合計してね
I7迄、オートフィルで式を入力します。

余計なことだと思いますが・・・E2　140000では合計が　980000　になりませんよね。
合計を正しいとして　E2に　163000　を入れてやってみました。

回答No.2

mshr1962

例えばC2:C6の合計なら
=SUMPRODUCT(ISNUMBER(FIND("85278",B2:B6))*(LEFT(B2:B6,1)="1")*((LEFT(RIGHT(B2,B6,2),1)=”1”)+(LEFT(RIGHT(B2,B6,2),1)=”2”)),C2:C6)

まあ、本当はB列の科目コードを分解した列を作っておけば、もっと楽に計算できるんですけどね。
J2=LEFT(B2,1)
K2=MID(B2,2,5)
L2=LEFT(RIGHT(B2,B6,2),1)
なら
=SUMIFS(C2:C6,J2:J6,"1",K2:K6,"85278",L2:L6,">=1",L2:L6,"<=2")
で可能です

回答No.1

hawa254

???
書き間違いだと思います。本当は何でしょうか？

>(1)Ｂ列の中で85278を探す
>(2)Ｂ列の最初の数字が1
>(3)Ｂ列の右から2文字目の数字が1または2の場合

>(1)(2)(3)の条件が満たされた場合

たぶん、(2)(3)の列が違うのだと思います。

一番簡単な方法は下記だと思います。

・月ごとの数値の集計は、合計欄の列を１列増やして、
　全ての行で「=sum()」により合計をしておく
・条件は「条件付き書式」を使ってセルに色を付ける

とすればよいと思います。