[[20061030122048]]　『空白行をつめて集計する』（関節痛）　ページの最後に飛ぶ

『空白行をつめて集計する』（関節痛）
お世話になります。次のような表を集計する場合Ｅ行が空白の場合はつめて、Ｅ行に金額のある場合は客先コードと金額を集計する方法をご教示ください。B行は客先コード、Ｅ行は売上金額です。（Ｃ、Ｄは集計対象外）

        B ..... C .....D ..... E
 7    139 ....................21,000
 8    357 ....................63,000
 9   1422 ....................
 10  1800 ....................
 11  5008 ................... 40,900
 12  5009 ...................
 13  5016 ................... 10,500
 14
 .
 .

集計表

       B ..... C .....D ..... E

 50   139 ...................21,000
 51   357 ...................63,000
 52  5008 ...................40,900
 53  5016 ...................10,500

のようにしたいのです。いま、客先コードを参照する場合IF($E7="","",IF(COUNTIF($E$7:$E$40,$E7)>0,$B7))をB50に、金額を参照する場合E50に=E7としていますが、これでは9,10,12行...分が空白行で集計されてしまいます。つめて集計したいのですが宜しくお願いいたします。

 B50セル　=IF(ROWS(B$50:B50)>COUNTIF($E$7:$E$13,"<>"&""),"",INDIRECT("B"&SMALL(IF(($E$7:$E$13<>""),ROW($B$7:$B$13),""),ROWS(B$50:B50))))
 として、Shift+Ctrlキー押しながらEnterキーで確定させて配列数式にしてください。

 B列が出ていれば、E列はVLOOKUP関数でもいけますし、上記式の"B"の部分を"E"にするだけでも
 いけます。

 （川野鮎太郎）

 お役に立つかどうか判りませんが、参考までに書いてみます。
 １．全体を下の空いているところに複製する。
 ２．複製したE列範囲を選択し、編集、ジャンプ、セル選択、空白セル、ＯＫ。
 ３．黒いところを右クリック、削除、行全体、ＯＫ。
 としますと、空白行が削除できます。　　　　　　　　　　　　　　　　　　　(LOOKUP)

みなさま大変ありがとうございます。鮎太郎さまの式は自分のレベルでは理解するのに大変です。もっと勉強させてください。LOOKUPさま　こんな方法もあるのかと感心するばかりです。ただ手作業が多くなり大変そうですがこれなら自分でも出来そうです。ありがとうございました。

LOOKUPさん　質問が間違っていました。E50には　=IF(B50,$E7,"")といれてあります。これですとB9, B10の場合(Bが空白の場合)E52,E53がゼロ"0"となり""（空白）になりません。空白にするにはどのようにすればよろしいでしょうか？初歩的な質問ですみません。宜しくお願いいたします。

川野鮎太郎さま　質問させてください。COUNTIF($E$7:$E$13,"<>"&""),でこのCOUNTIFの式の意味がわかりません。初心者用に解説していただけませんでしょうか？よろしくおねがいいたします。

 私の書いたものは、数式で処理するものでは、ありません。

 行削除を行った後に、B列のキーごとにE列を累計するのでしたら、

 さらに下の空いているところを選択し、データ、統合として、
 統合元範囲にSheet1!$B$50:$E$53などととして、追加をクリックし、
 統合の基準に左端列にチェックをして、ＯＫ。としますと、
 そのように集計できます。　　　　　　　　　　　　　　　　　　　　　　　　　　　　(LOOKUP)

 ＞COUNTIF($E$7:$E$13,"<>"&"")
 これは、E7からE13で空白ではないセルの数を数えています。

 （川野鮎太郎）

 手作業ですが

 元データにオートフィルタをかけて
 E列で（空白以外のセル）を選択し
 50行以下にコピペ。

 　一度書き込みしたのですが、この下に書いていた作業は
   全く意図と違っていました。（しかも何も生み出さない・・・）
   よって、削除しました。

 (HANA)

 フィルタオプションを使った方法。
 （先ほど削除した所の訂正案です。）

 ６行目に見出しがあるとして
	[B]	[C]	[D]	[E]	[F]
[6]	コード	A	B	金額	
[7]	139			21000	TRUE
[8]	357			63000	
[9]	1422				
[10]	1800				
[11]	5008			40900	
[12]	5009				
[13]	5016			10500	
 B7:E13の様なデータだった場合
 F7=E7>0

 メニュー・データ→フィルタオプションの設定
    抽出先－－－－
      ○選択範囲内(F)
      ●指定した範囲(O)

    リスト範囲(L)   B6:E13
    検索条件範囲(C) F6:F7
    抽出範囲(T)     B50

            [OK]

 とすると、E列が0より大きい数字が入力されたもののみ
 B50以下に抽出されます。

 (HANA)

HANAさん　ありがとうございます。
川野鮎太郎さん　度々で申し訳ございません。

 INDIRECT("B"&SMALL(IF(($E$7:$E$13<>""),の部分はどのように考えればよろしいでしょうか。どうしても全体の式を理解したいのですが参考書を当たってもわかりません。宜しくお願いいたします。

 鮎太郎さんはお忙しそうなので・・・
[[20060302144720]]『９０より以上の人の名前を抽出したい』（shota）
 「配列数式」に関してはこちらが参考になるかもしれません。

 (HANA)

 HANAさん、フォローありがとうございます。m(_ _)m

 配列数式は一足飛びでは理解できないかもしれませんが、回数をこなせば理解できると思いますよ(^_^A;

 （川野鮎太郎）

HANAさん,川野鮎太郎ありがとうございました。勉強の参考にさせていただきます。（関節痛）

むしかえしで申し訳ありません。式を理解しようと勉強していたら大変時間がかかってしまいました。鮎太郎さんの式もINDIRECTの意味や、SMALL(IFの考え方や配列など一つ一つ勉強してやっと全体がみえてきました。ここでもう一度空白でないセルを数える式COUNTIF($E$7:$E$13,"<>"&"")で"&"の意味が分かりません。"&"は参考書には接合子とでているだけです。すると$E$7:$E$13の範囲で&でないものの数となってなんのことかわからなくなってしまいました。この"<>"&""の部分をどのように考えるのかご教示ください。よろしくお願いいたします。(関節痛）

 たとえば、
 IF(A1="","","○")
 と言う式が有った場合、この式の意味は
 「A1が""(空欄)なら""(空欄)。そうでないなら○("○"この部分)を表示」
 ですよね。
 この場合、「○を表示なんだから」
 IF(A1="","",○)
             ~~として、A1に何か入力が有った場合
 #NAME?（何のこと？）と言う結果が表示されます。

 「""」が何を含んでいるのか落ち着いて見直してみてはどうでしょうか。
 COUNTIF($E$7:$E$13,"<>"&"")
                    ~~~~ ~~
 「"&"」では無いですよ。

 (HANA)

HANAさん　早速ありがとうございます。まだピーンとこないのですがエクセル小学1年生レベルで考えると空白でないセルの数をかぞえるなら=COUNTIF($E$7:$E$13,"<>""")のようになるのではないかと思うのですがそうすると"7"とでてE7からE13までの全てのセルの数となってしまバツです。これは口語的にいうとNOT<>でしかも空白""というのでしょうか？落ち着いて考えようとしていますが実はあわてているのかもしれません。＆の存在がというか機能についてわかりません。再度ご教示いただけないでしょうか？よろしくお願いいたします。

 私もよく分かっていないので違うのかもしれませんが
 この様に考えてみるのはどうでしょうか。

 ""と言うのは、「何も入力されていない文字列」です。
 そこで、サンプルとして
 A7:A16に「Aと言う文字列を入力したもの」を用意し
 その内「Aと言う文字が入っていないセルの個数をカウント」
 したいとします。（例えば、B6に）
 まず、Aと言う文字が入っているか居ないか確認する
 式をつくり、その結果を表示しましょう。(B7:B16)

 サンプルと、B7:B16の結果はこんな感じです。
	[A]	[B]
[7]		TRUE
[8]	A	FALSE
[9]		TRUE
[10]	A	FALSE
[11]		TRUE
[12]	A	FALSE
[13]		TRUE
[14]	A	FALSE
[15]		TRUE
[16]	A	FALSE
 B7=A7<>"A"　として、B16までフィルドラッグ。
      ~~~~~
 この条件で上手く行くので（TRUEの数を数えれば求める個数）
 B6=COUNTIF(A7:A16,"<>"A"")と言う式を入れようとすると、怒られませんか？
                    ~~~~~
 エクセルとしては
 「""」を前から順番に組み合わせていって
 「"<>"」「A」「""」と考え
          ↑『Aってなによ！！』
 ってなっているからだと思うのですが・・・。

 「"<>"」と「"A"」ですよ。
 と言うためには、やっぱり「&」が必要です。

 (HANA)

HANAさん　ご丁寧な説明大変ありがとうございました。これですっきりできました。条件部分に比較演算子と文字列を使う場合は＆がはいると覚えます。これで一つ勉強ができました。ありがとうございました。（関節痛）

 適当でない、レスでしたので
 編集で、クリアしました。

 すみませんでした。
 。。。。Ms.Rin～♪♪

 Ms.Rinさん
 私は「適当なレス」だと思いました。
 もし宜しければ、復活をお願いします。
 もしかしたら、関節痛さんは思い違いをなさっているかもしれません。
 Ms.Rinさんの言葉の方が、より正確だと思います。

 ちなみにクリアされた部分に含まれていたのですが
 COUNTIF関数の条件部分「"<>"&""」を「"<>"」とも書けるのは
 何処かのセルで計算させてみたら分かりやすいかもしれません。
 「="<>"&""」と「="<>"」は同じ結果になります。
 たとえば、こんな感じです。
	[A]	
[1]	123	="123"&""
[2]	123	="123"
[3]	123	=123&""　　←参考に・・・・
[4]	TRUE	=AND(A1=A2,A1=A3,A2=A3)
 ここで、何処かのセルを「123」とすると、A4は「FALSE」になります。

 Ms.Rinさん、勝手にごめんなさい。

 (HANA)

みなさまいろいろありがとうございます。まだすっきりしないのですが、&をいれるのは、文字列の空白「＜＞」と数値の空白「""」の両方をみているということでしょうか？何度もすみません。

 ・・・えっと、
 条件を「"<>"""」と出来ないのは何故か？
 と言う所が疑問ですか？それとも、そこは解決ですか？

 もしかして、
 >「"<>"&""」を「"<>"」とも書けるのは
 この部分のことを仰っていますか？
 それならば
 「文字数0の文字列("")が付加(&)された文字列("<>")」と
 「文字列("<>")」は一緒ですよ。
 と言うことですが・・・・。

 >文字列の空白「＜＞」と数値の空白「""」の両方をみているということでしょうか？
 この言葉が何を表しているのかよく分かりません。

 (HANA)

「文字列の空白「＜＞」と数値の空白「""」の両方をみているということでしょうか？」はい、そうです。＆があるのはそのためだろうと思ったのですが？自分でもよくわからなくなってしまいました。すみません。

 何が分からないのかよく分かっていませんが
 この式は「配列数式」と呼ばれるものです。

 セルの値が「<>」＆「""」ですか？
 と言うのを見ているのではなく
 セルの値は「"<>"&""」（Ctrl+Shift+Enterで確定)→「<>""」ですか？
 と言うのを見ています。

 ・・・と言うことですかね？

 (HANA)

Rinです。。。♪

 HANAさんへ
 HANAさんの回答は、いつも丁寧で見習なければと
 思っています。
 いろいろと勉強も、させて頂いています。

 >Ms.Rinさん、勝手にごめんなさい。

 ぜんぜん、構いません。
 お気になさらずに～。。

 >私は「適当なレス」だと思いました。

 レスの内容は、そう間違ってはいない事を
 書いたつもりだったんですが。
 質問者さんが、前のレスで一応、理解された様ですので
 あまり、余計なことを書くと混乱されると思って
 削除したんですが。

 関節痛さんへ

 ★COUNTIFで検索条件を　& で、繋ぐのは

 「比較演算子を使って、文字列　や　セル参照　を検索条件にする場合。」
 こんな感じで、覚えておけば良いのではないでしょうか。
 (例外もありますが。)

 実際は、比較演算子と文字列は、あまり使う事はないかと
 <> くらいでしょうか。セル参照も文字列でしたら　<> くらいですね。

 "<>"&"" は、COUNTIFの範囲内の　""(空白セル) でない セル
 を見ていると思ってはいかがでしょうか。

 私の説明は、完全な回答と言うわけではありませんので
 ご理解くださいね。

 。。。。Ms.Rin～♪♪

みなさん　ありがとうございます。いろいろ勉強させていただきました。かさねてお礼申し上げます。（関節痛）

関節痛さんへ

 スレをお借りします。。

 ふたたび～。HANAさんへ

 COUNTIFも色々と試すと解らないことがあります。

 こんな表で

	A
[1]	<>
[2]	
[3]	3
[4]	a
[5]	b

 (1) =COUNTIF(A1:A5,"<>") →　空白でないセルを数えます。4

 (2) =COUNTIF(A1:A5,"=")  →　空白セルを数えます。1

 (3) =COUNTIF(A1:A5,"*")　→　文字列を数えます。3

    A1の <> は、文字列でしょうか？
    でも、　(1)の　=COUNTIF(A1:A5,"<>") →　では、数えません。

    A1セルの <> を　"<>" としますと
    =COUNTIF(A1:A5,"""<>""")
    で、１　と数えます。

 =SUMPRODUCT((A1:A5="<>")*1)
 なら、A1セルが　<> のままでも数えます。

 COUNTIFだけかどうか、解りませんがSUMIFもそうかも？
 試していませんけれど。

 比較演算子は、特殊な文字列という事なんでしょうね？

 私が知らないだけかも知れませんので
 ご存知でしたら、HANAさんに失礼ですので謝ります。

 それから
 最近、こちらで回答をさせて頂いていますが
 そそっかしいので、間違った回答の際は
 遠慮なく指摘してください。
 これからも、よろしくご指導お願いします。

 。。。。Ms.Rin～♪♪

 Ms.Rinさん、ありがとうございました。
 ・・・おっと、衝突。
 ちょっと時間がないので後で確認させてもらいます。

 とりあえず、説明がおかしかったので訂正します。

 >この式は「配列数式」と呼ばれるものです。
 >セルの値は「"<>"&""」（Ctrl+Shift+Enterで確定)→「<>""」ですか？
 >と言うのを見ています。
 この部分。
 COUNTIF($E$7:$E$13,"<>"&"")
 この式は「配列数式」では無いです。（ごめんなさい）
 ただ、条件の中に「&」は有りますが、
 セルの値は「"<>"&""」→「<>""」ですか？
 と言うのを見ています。

 (HANA)

 Ms.Rinさんへ

 私も色々試してみました。
 そこで、まずは
 >(3) =COUNTIF(A1:A5,"*")　→　文字列を数えます。3
 >   A1の <> は、文字列でしょうか？
 >   でも、　(1)の　=COUNTIF(A1:A5,"<>") →　では、数えません。
 こちらから。
 「文字列を数えます」と言う事なので、以下のようなサンプルを作ってみました。
 ISTEXT関数がB列の様な結果を返すとき、(3)の式はC1の様な結果になります。
	[A]	[B]	[C]
[1]	<>	TRUE	3
[2]		FALSE	
[3]	3	FALSE	
[4]	a	TRUE	
[5]	b	TRUE

	[A]	[B]	[C]
[1]	<>	TRUE	4
[2]		FALSE	
[3]	3	TRUE	
[4]	a	TRUE	
[5]	b	TRUE

 次に、(1)と(2)とSUMPRODUCT関数を使った式について・・・。
 そもそも
 >「文字数0の文字列("")が付加(&)された文字列("<>")」と
 >「文字列("<>")」は一緒ですよ。
 この部分の考え方に付いて仰っていることでしょうか？
 そうでないとして・・・次のような検算を行ってみました。
	[A]	[B]	[C]	[D]
[1]	<>	TRUE	FALSE	TRUE
[2]		FALSE	TRUE	FALSE
[3]	3	TRUE	FALSE	FALSE
[4]	a	TRUE	FALSE	FALSE
[5]	b	TRUE	FALSE	FALSE
[6]		<>""	=""	="<>"	←A列との比較
[7]		4	1	1
		↑(1)	↑(2)	↑=COUNTIF(A1:A5,"="&"<>")
		"<>"	"="	←COUNTIF関数の条件部分

 B1:D5に入っている式は（各１行目の式。５行目までコピー）
 B1に =A1<>""　C1に =A1=""　D1に =A1="<>"
 また、それぞれをSUMPRODUCT関数で行うとすれば
 B7=SUMPRODUCT((A2:A6<>"")*1)
 C7=SUMPRODUCT((A2:A6="")*1)
 D7=SUMPRODUCT((A2:A6="<>")*1)（これはご提示の式です）
 この様に書けるのではないでしょうか。

 ・・・そもそも意図が違っていたらごめんなさい。

 (HANA)

 あぁ、分かりました。（・・・かな？）

 たとえば、A1:A5のなかで「a」を探すときは
 =COUNTIF(A1:A5,"a")
 で良いのに、「<>」を探すときは
 =COUNTIF(A1:A5,"="&"<>")
                ~~~~これが必要になる
 って事ですね。

 (HANA)

コメント返信：

[ 一覧(最新更新順) ]

[[20061030122048]] 『空白行をつめて集計する』（関節痛） ページの最後に飛ぶ

[[20061030122048]]　『空白行をつめて集計する』（関節痛）　ページの最後に飛ぶ