[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『空白行をつめて集計する』(関節痛)
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"にするだけでも いけます。
(川野鮎太郎)
お役に立つかどうか判りませんが、参考までに書いてみます。 1.全体を下の空いているところに複製する。 2.複製したE列範囲を選択し、編集、ジャンプ、セル選択、空白セル、OK。 3.黒いところを右クリック、削除、行全体、OK。 としますと、空白行が削除できます。 (LOOKUP)
私の書いたものは、数式で処理するものでは、ありません。
行削除を行った後に、B列のキーごとにE列を累計するのでしたら、
さらに下の空いているところを選択し、データ、統合として、 統合元範囲にSheet1!$B$50:$E$53などととして、追加をクリックし、 統合の基準に左端列にチェックをして、OK。としますと、 そのように集計できます。 (LOOKUP)
>COUNTIF($E$7:$E$13,"<>"&"") これは、E7からE13で空白ではないセルの数を数えています。
(川野鮎太郎)
手作業ですが
元データにオートフィルタをかけて E列で(空白以外のセル)を選択し 50行以下にコピペ。
一度書き込みしたのですが、この下に書いていた作業は 全く意図と違っていました。(しかも何も生み出さない・・・) よって、削除しました。
(HANA)
フィルタオプションを使った方法。 (先ほど削除した所の訂正案です。)
6行目に見出しがあるとして [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)
INDIRECT("B"&SMALL(IF(($E$7:$E$13<>""),の部分はどのように考えればよろしいでしょうか。どうしても全体の式を理解したいのですが参考書を当たってもわかりません。宜しくお願いいたします。
鮎太郎さんはお忙しそうなので・・・ [[20060302144720]]『90より以上の人の名前を抽出したい』(shota) 「配列数式」に関してはこちらが参考になるかもしれません。
(HANA)
HANAさん、フォローありがとうございます。m(_ _)m
配列数式は一足飛びでは理解できないかもしれませんが、回数をこなせば理解できると思いますよ(^_^A;
(川野鮎太郎)
たとえば、 IF(A1="","","○") と言う式が有った場合、この式の意味は 「A1が""(空欄)なら""(空欄)。そうでないなら○("○"この部分)を表示」 ですよね。 この場合、「○を表示なんだから」 IF(A1="","",○) ~~として、A1に何か入力が有った場合 #NAME?(何のこと?)と言う結果が表示されます。
「""」が何を含んでいるのか落ち着いて見直してみてはどうでしょうか。 COUNTIF($E$7:$E$13,"<>"&"") ~~~~ ~~ 「"&"」では無いですよ。
(HANA)
私もよく分かっていないので違うのかもしれませんが この様に考えてみるのはどうでしょうか。
""と言うのは、「何も入力されていない文字列」です。 そこで、サンプルとして 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)
適当でない、レスでしたので 編集で、クリアしました。
すみませんでした。 。。。。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)
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,"""<>""") で、1 と数えます。
=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に入っている式は(各1行目の式。5行目までコピー) 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)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.