[[20140710172741]] 『重複条件の抽出(抽出範囲は可動)』(ふく) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『重複条件の抽出(抽出範囲は可動)』(ふく)

  A   B   C   D 
1 項目名

2 001 そら  1   001

3 002 うみ      002 

4 003 そら      001

5 004 そら      001

6 005 そら     (  )

D列に関数を入れ、B列の値が一致し、C列に[1]が入っているものの
A列値を返したいのですが、
対象範囲を前行までの可変にするところで躓いてしまいました。
※A列の数値重複はないものとします。

以下のような関数を入れてみましたが、エラーになるわけではなく
0の数値が入ってしまいます。
因みに可動とせずoffsetを抜かしたものだときちんと値が反映されるので、
ここに問題があるのだと思いますが…
どなたが教えて頂けないでしょうか。
===========
=sumproduct(offset($b$2:row()-1)=B5)*(offset($c$2:row()-1)=1)*(offset($a$2:row()-1))
===========

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

 

< 使用 Excel:Excel2013、使用 OS:Windows7 >


 >以下のような関数を入れてみましたが、エラーになるわけではなく0の数値が入ってしまいます。 
 >=sumproduct(offset($b$2:row()-1)=B5)*(offset($c$2:row()-1)=1)*(offset($a$2:row()-1)) 
 本当にこの式は動きますか?
 こちらではエラー以前に「正しくない数式」と叱られ入力できませんが。

 =offset($b$2:row()-1)=B5) この式も私には理解できないですよ
 =offset($b$2,row()-1),5) 結果はどうあれこれならわかりますが

 sumproductを使って、offsetを絡ませて、D列の答えを求める
 これは無理ではないでしょうか?

 >因みに可動とせずoffsetを抜かしたものだときちんと値が反映されるので、 
 ↑これはどのような式でしょうか?

 ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

 提示されたD列4行からの推測ですが
 B2「そら」に対応するA1は「001」、B3「うみ」に対応するA2は「002」
 でも、B4「そら」に対応するのはA4の「003」なんだけど、
 最初に出てきた「そら」に対応する「001」を適用したい。
 ↑このような意味ではないのですか?

 D2に
=INDEX($A$2:$A$6,MATCH(B2,B$2:B$6,0))

 こんなことでしょうか?

(jun53) 2014/07/10(木) 22:26


返信ありがとうございます。
要点を得ない質問ですみません。
しかも、あげた数式が全く合っておりませんでした。

エラーになるわけでなく、0の数値になってしまったのは、以下になります。
因みにD6セルにいれているものです。
=============
=sumproduct(offset($b$2,0,0,row()-1,1)=B6)*(offset($c$2,0,0,row()-1,1)=1)*(offset($a$2,0,0,row()-1,1))
=============

 >>因みに可動とせずoffsetを抜かしたものだときちんと値が反映されるので、 
 > ↑これはどのような式でしょうか?
=============
=sumproduct(b2:b5=B6)*(c2:c5)*(a2:a5)
=============
このb2:b5やc2:c5などの選択範囲を可動とし、D5セルの場合はb2:b4に、としたい
と思っております。

要素としては(D6基準にした場合)
・B列が「そら」
・C列が「1」
・上記条件のA列を返す
・選択範囲は常に基準セルの上までを対象とするため、稼働となる。
 D6の場合は5行目まで、D7の場合は6行目までが対象。

何度もお手数をおかけしてしまい恐縮ですが、再度ご教授寝返れば幸いです。

(ふく) 2014/07/11(金) 10:06


すみません、すぐにはわかりません。
みなさま、ヘルプ!です。

(jun53) 2014/07/11(金) 13:05


 整理
 最初に提示した表と説明があってない気がします。
 >D列に関数を入れ、B列の値が一致し、C列に[1]が入っているものの 
 >A列値を返したいのですが、 
 B列の値が「何」と一致した時ですか?

 また、3行目のうみはC列に1がないので「空白」にならなくていいのでしょうか?
	[A]	[B]	[C]	[D]	[E]
[1]	項目名				
[2]	001	そら	1	001	
[3]	002	うみ		002	←C列に1がないけどあってるの?
[4]	003	そら		001	
[5]	004	そら		001	
[6]	005	そら		001	
[7]	006	そら	1	006	←C列に1があるので、A列の値を更新?
[8]	007	うみ		002	←?
[9]	008	そら		006	

 それから↓これ
 >=sumproduct(b2:b5=B6)*(c2:c5)*(a2:a5)
 エラーになりますよね?
 =SUMPRODUCT((B2:B5=B6)*(C2:C5)*(A2:A5))
 こちらの間違いではないですか?
 ちなみにSumproduct関数はセル参照を戻す関数ではないので、A列の値を表示させる
 ことは出来ませんよ。

 数式をこちらに記載するときに、なぜコピーではなく手打ちするのですか?
 手打ちする必要があるのでしたら、間違いないか確認したうえでお願いします。

(稲葉) 2014/07/11(金) 13:22


jun53様、稲葉様
回答ありがとうございます。
また初めての質問とは言え、誤りが多く大変申し訳ございませんでした。

以下、ご指摘、確認頂きましたコメントに返答させて頂きます。

 >>D列に関数を入れ、B列の値が一致し、C列に[1]が入っているものの 
 > >A列値を返したいのですが、 
 > B列の値が「何」と一致した時ですか?
6Dであれば、6Bの値と一致し、C列に[1]が入っているものを指しております。

B列については重複した名称で掲載があるのですが、同じ名称の中で
C列に[1]の記載があるものは1つしか存在しないものになります。

 > また、3行目のうみはC列に1がないので「空白」にならなくていいのでしょうか?
ご指摘のとおりです。
以下、追記修正させて頂きます。
	[A]	[B]	[C]	[D]	[E]
[1]	項目名				
[2]	001	そら	1	001	
[3]	002	うみ	1	002	←C列に[1]を追記しました。
[4]	003	そら		001	
[5]	004	そら		001	
[6]	005	そら		001	
[7]	006	そら		001	←同名で、C列に[1]があるものは1つ
[8]	007	うみ		002	←あっております。
[9]	008	そら		001	←上記のため、Dは001になります。

 > =SUMPRODUCT((B2:B5=B6)*(C2:C5)*(A2:A5))
 > こちらの間違いではないですか?
ご指摘のとおりです。
しかも一点抜け漏れがございました。
正しくは以下になります。
=SUMPRODUCT((B2:B5=B6)*(C2:C5=1)*(A2:A5))

 >ちなみにSumproduct関数はセル参照を戻す関数ではないので、A列の値を表示させる
 >ことは出来ませんよ。
そもそも、そこの認識が違っているのですね。

 > 数式をこちらに記載するときに、なぜコピーではなく手打ちするのですか?
 > 手打ちする必要があるのでしたら、間違いないか確認したうえでお願いします。
時間を割いて頂いているにも関わらず、低レベルな誤りに対する指摘を頂くこととなり、
本当に申し訳ございません。
以後、気を付けるようにいたします。

お手間を取らせてしまい、大変申し訳ないのですが、
解決する方法がございましたら、教えて頂けますでしょうか。
よろしくお願いいたします。

(ふく) 2014/07/11(金) 14:37


 >←同名で、C列に[1]があるものは1つ
 ということは、B列とC列の組合せは一つしかないということですよね?
 E列に作業列を作ります。
 E2=B2&C2
 D2=IFERROR(INDEX($A$2:A2,MATCH(B2&1,$E$2:E2,0)),"")
 それぞれ下方向にコピー
	[A]	[B]	[C]	[D]	[E]
[1]	項目名				
[2]	001	そら	1	001	そら1
[3]	002	うみ	1	002	うみ1
[4]	003	そら		001	そら
[5]	004	そら		001	そら
[6]	005	そら		001	そら
[7]	006	そら		001	そら
[8]	007	うみ		002	うみ
[9]	008	そら		001	そら

 E列を検索してA列を表示させます。
 MATCH関数は一番最初に見つかった配列の番号を返すので、上記例では、例えC6に1が入力されても、
 2行目の001が返されるので問題ないのではないでしょうか?
(稲葉) 2014/07/11(金) 15:45

	A	B	C	D
1	項目名			
2	001	そら	1	001 ←???
3	002	うみ	1	002 ←???
 
 
 対象範囲を「前行まで」にするんだったら、
 D2セルの対象範囲は1行目だけ。
 そこには「項目名」しかないんだから該当するものはないのでは?

 D3セルも同じ。
 対象範囲を2行目までにしたら「うみ」なんかないんだから、該当するものはないのでは?
 
 
 > 同名で、C列に[1]があるものは1つ

 該当するものが2個以上あることはない、ってことだよね?
 で、A列は数値でしょ?
 だったら SUMPRODUCT でできるでしょう。
 2013だったら SUMIFS でもできるよね。
 
(名無しのおっさん) 2014/07/11(金) 15:53

 >・選択範囲は常に基準セルの上までを対象とするため、稼働となる。 
 > D6の場合は5行目まで、D7の場合は6行目までが対象。 
 表だけしか見てなかったけど、↑の条件入れると
 常に1つ上の行までしか見ないってこと?
 D2=IFERROR(INDEX(A1:A2,MATCH(B2&1,E1:E2,0)),"")

 そうすると
	[A]	[B]	[C]	[D]	[E]
[1]	項目名				
[2]	001	そら	1	001	そら1
[3]	002	うみ	1	002	うみ1
[4]	003	そら			そら
[5]	004	そら			そら
[6]	005	そら			そら
[7]	006	そら			そら
[8]	007	うみ			うみ
[9]	008	そら			そら

 こういう結果になると思うんだけど・・・

 表に合わせるの?
 言葉に合わせるの?

 名無しのおっさんさん
 >該当するものが2個以上あることはない、ってことだよね?
 >で、A列は数値でしょ?
 >だったら SUMPRODUCT でできるでしょう。
 >2013だったら SUMIFS でもできるよね。
 D2=SUMIFS(A1:A2,B1:B2,B2,C1:C2,C2)
 こういうことですかね?
 私はA列は文字列だと考えてました。

(稲葉) 2014/07/11(金) 16:18


稲葉様、名無しのおっさん様

回答ありがとうございました。
結論から申し上げると、対象範囲を可動とする必要がなくなったため
お二人から頂いた回答で解決することができました。

今回については解決したのですが、
対象範囲を可動にしたい場合の対応については理解できていないままなので、
時間がある時で結構ですので、回答頂けると嬉しいです。

対象範囲の可動についての考え方ですが、
ご提示頂きました以下の数式の場合、A1:A2やE1:E2の部分にあたるところに
「A1から、一つ前の行までを対象範囲とする」、といった数式を
入れられればと思っております。
============
D2=IFERROR(INDEX(A1:A2,MATCH(B2&1,E1:E2,0)),"")
============

> 対象範囲を「前行まで」にするんだったら、
> D2セルの対象範囲は1行目だけ。
> そこには「項目名」しかないんだから該当するものはないのでは?

> D3セルも同じ。
> 対象範囲を2行目までにしたら「うみ」なんかないんだから、該当するものはないのでは?
確かにそうですね。
該当がない場合は、D2であればA2を、D3であればA3を返す形にしておりました。
説明不足ですみません。

また、A列は今回については数値でしたので、
名無しのおっさん様が仰っていた SUMPRODUCT、SUMIFSともに
使うことが可能でした!
因みにSUMIFSを使った数式では、前述のとおり可動の考えがなくなったため
以下で導きだしております。
=========
D2=SUMIFS(A:A,B:B,B2,C:C,1)
=========
ただ、これについても対象範囲が可動となった場合は、どうすればよいのだろうか???
というのが何となく気持ち悪い感じです。

私のつたない説明により、お手数をおかけしてしまい、すみません。
上記については、お時間のある時に回答頂けば嬉しいです。

(ふく) 2014/07/11(金) 18:15


 > 該当がない場合は、D2であればA2を、D3であればA3を返す形にしておりました。

 ↑ をどのように解決したのかわからないので、ちょっと置いといて

 > 対象範囲を可動にしたい場合の対応

 ↑ についてだけ。
 
 
 あくまで「前行まで」ということだったら
 D2セルは100パー該当なしなので、数式を入れるのはD3セルからだとすると、
 範囲は
 $A$2:A2、$B$2:B2、$C$2:C2
 でいいのでは?

 SUMIFSだったら
 D3 =SUMIFS($A$2:A2,$B$2:B2,B3,$C$2:C2,1)
~~~~            ~~~~
 これをコピーすれば
 4行目だったら $A$2:A3
 5行目だったら $A$2:A4
10行目だったら $A$2:A9
と前行までの可変になるけど、これだと何か都合が悪いことがあるの?
 
 
 ちなみにSUMIFSだったら該当なしの場合「0」になるから
 000 と表示される(表示形式のユーザー定義が 000 だとして)
 この 000 を非表示にしたい場合は
 ユーザー定義を 000;; にすればいいっす。(;; をつけるってこと)
 
(名無しのおっさん) 2014/07/14(月) 08:41

名無しのおっさん様

ありがとうございます!
[$]はいつもペア(はじめの行と終わりの行)でつけなければ
ならないものと、勝手に思っていました…

あまりのあっけない解決に、恥ずかしいほどです。

 >ちなみにSUMIFSだったら該当なしの場合「0」になるから
 >000 と表示される(表示形式のユーザー定義が 000 だとして)
 >この 000 を非表示にしたい場合は
 >ユーザー定義を 000;; にすればいいっす。(;; をつけるってこと)
こちらも助かりました!

ご回答頂きました皆様、本当にありがとうございました。
そしてお時間頂いてしまい、すみませんでした。
(ふく) 2014/07/14(月) 10:11


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.