エクセルの学校：全文検索：Hyper Estraier： A��...

Results of 1 - 1 of about 48855 for A��... (0.010 sec.)

[[20150602155558]]: #score: 1420; @digest: 380e78ae91af23314be286ecfa7836c0; @id: 68150; @mdate: 2015-06-04T04:55:24Z; @size: 13939; @type: text/plain; #keywords: equity (59364), money2 (38744), （eq (34220), 図4 (20803), 番デ (15085), 図1 (11149), 図2 (10094), 最上 (9241), プ内 (7405), 本番 (6998), 上部 (6875), total (4911), ・10 (4591), 大値 (4202), gobgob (3966), 算出 (3811), 2015 (3501), 期待 (3450), ウッ (3289), ココ (3094), グル (3084), 笑) (2907), を算 (2847), 最大 (2381), ッシ (2234), sumproduct (2073), 以下 (1795), 列な (1711), 構成 (1710), ロジ (1710), sumif (1669), countif (1622); 『同じ名前のグループで合計額を算出する数式・関数について』（equity）
初めて質問させていただきます。以下の図1をご確認ください。 ★図1 A B C D E F 1 [name] [money] [money2] [total2] [total3] [total] 2 a 1 2 3 3 3 3 b 1 2 3 6 6 4 b 1 2 3 3 5 c 1 2 3 3 3 ・・・ 10 最終的にF列に合計額を表示するつもりで作成しました。 D列とE列に入っている値は、F列に入っている数式で利用します。 D2=SUM(B2,C2) その行の合計額を算出します。 ※B列とC列の値の合計です。 E2=SUMIF(A2:$A$10,A2:$A$10,D2:$D$10) その列から最下部まで入力されているA列の文字列で同じ文字列をもつ行のD列の合計を算出します。 ※図1の場合はA列のbが2つあるので、D3とD4の値を合計して E3が6になります。 F2=SUMPRODUCT(MAX(($A$2:$A$10=A2)*($E$2:$E$10))) 最上部から最下部まで入力されているA列の文字列で同じ文字列をもつ行のE列の最大値を算出します。 ※図1の場合はA列のbが2つあるので、E3とE4の最大値を算出して F3が6になります。さらに上記の関数を以下の◆に置き換えます。置き換えたものが図1のようになります。 F2=if(◆=E2,◆,"") A列の文字列で同じ文字列をもつ場合同じ文字列をもつ最上部の行のみF列の値を表示させます。 ※図1の場合はF4が空白になります。 ------------------------------------- ここからが本題です。合計値に新しいケースが出てきたため、数式の改善が必要です。 F列にマイナス値が表示されるべきケースです。以下の図2のようになることを期待しています。 ★図2 A B C D E F 1 [name] [money] [money2] [total2] [total3] [total] 2 a -10 2 -8 -8 -8 3 b 1 2 3 -5 -5 4 b -10 2 -8 -8 5 c 1 2 3 3 3 ・・・ 10 図2を表現するためのF列の数式をご教授ください。ちなみに私の構成は固執していません。目的を達成できるもっとスッキリした構成があるならば是非とも。。! 以下は私の失敗作です。。 F2=IF(SUMPRODUCT(MAX(($A$2:$A$10=A2)*($E$2:$E$10)))=E2,SUMPRODUCT(MAX(($A$2:$A$10=A2)*($E$2:$E$10))),IF(AND(E2<0,E2>SUMPRODUCT(MIN(($A$2:$A$10=A2)*($E$2:$E$10)))),E2,"")) ※A列に同じ文字列が入力されている場合(図2のA列がbの場合)は表現できますが、A列が一意の場合(図2のA列がaの場合)は失敗する(空白になる)。＜使用 Excel:Excel2010、使用 OS:Windows7 ＞（equity） 2015/06/02(火) 15:59 ---- こんにちはセルF2に =IF($E2=MAX(IF($A$2:$A$10=$A2,$E$2:$E$10,"")),MAX(IF($A$2:$A$10=$A2,$E$2:$E$10,"")),"") と入力して、「Ctrl」+「Shift」+「Enter」で確定して下方にフィルコピーとかでどうでしょうか? （ウッシ） 2015/06/02(火) 16:44 ---- ウッシさんありがとうございます! 実機で確認してみたところ。。成功しました!完璧です!結婚して下さい!(笑) 今回の質問で、私のスキルの程度は把握できるかもしれませんが配列数式はまだノータッチなのです。。関数に依存せずに、これからも精進します。これから回答いただいた数式を解析・学習します。（equity） 2015/06/02(火) 16:58 ---- ウッシさん以下の図3のケースでは結果不良でした。。 5行目を差し込みました。 ★図3(ウッシさんの場合) A B C D E F 1 [name] [money] [money2] [total2] [total3] [total] 2 a -10 2 -8 -8 -8 3 b 1 2 3 -2 ←ココ 4 b -10 2 -8 -5 5 b 1 2 3 3 3 ←ココ 6 c 1 2 3 3 3 ・・・ 10 ★期待する結果 A B C D E F 1 [name] [money] [money2] [total2] [total3] [total] 2 a -10 2 -8 -8 -8 3 b 1 2 3 -2 -2 ←ココ 4 b -10 2 -8 -5 5 b 1 2 3 3 ←ココ 6 c 1 2 3 3 3 ・・・ 10 E列にマイナスがある場合は 0以下かつ最大値という条件がポイントですね。私も格闘中です。。（equity） 2015/06/02(火) 17:12 ---- こんにちは表が崩れていて良く分からないのですが、 0以下かつ最大値っていつ出た条件ですか? （ウッシ） 2015/06/02(火) 17:18 ---- 表の崩れは、余計なお世話とは思いましたが、直しておきました。 (見にくくて、考えにくいので・・、・・けど仕様の解釈に確信が持てないので、思考自体停止中） equityさんへ表の場合、各行の左端に半角スペースを入れると整形されますので、今後ご励行願います。（半平太） 2015/06/02(火) 17:22 ---- 半平太さんわざわざありがとうございます。今後は整形に注意します。ウッシさん「E列にマイナスがある場合は0以下かつ最大値」はF列に目的の値を算出するためのロジックとして記入しました。しかし、グループ内でE列にマイナスの値を含んでいても F列にプラスの値が算出されることを期待する場合 (図3でB5が10の場合)などは、このロジックは通用しませんでした。。 F列が、グループ内でE列の最上部の値を拾ってくれれば話は早いんですケド。。（equity） 2015/06/03(水) 08:01 ---- 進捗報告。自己解決しかけています。 E列をいじりました。 E2=SUMIF($A$2:$A$11,$A$2:$A$11,$D$2:$D$11) F2=IF(E2<0,E2,IF(SUMPRODUCT(MAX(($A$2:$A$11=A2)*($E$2:$E$11)))=E2,SUMPRODUCT(MAX(($A$2:$A$11=A2)*($E$2:$E$11))),IF(AND(E2<0,E2>SUMPRODUCT(MIN(($A$2:$A$11=A2)*($E$2:$E$11)))),E2,""))) あとはグループ内のF列の最上部以外を空白にするだけです。。一応、上記の数式でモニタリング募集中です。（equity） 2015/06/03(水) 08:35 ---- でもF列が意味を成しませんね。。 F列の最上部以外を空白にするために変更前のE列があったかもしれません(今、気付いた)。（equity） 2015/06/03(水) 08:49 ---- ・A列グループのE列が0以下のみなら 0以下での最大値・〃 0より大きい数値のみなら単純に最大値で、いいんだよね? =IF(COUNTIF(A$2:A2,A2)=1,MAX(IF(A$2:A$10=A2,IF(COUNTIFS(A$2:A$10,A2,E$2:E$10,"<=0")=0,E$2:E$10,IF(E$2:E$10<=0,E$2:E$10)))),"") CTRL+SHIFT+ENTER （GobGob） 2015/06/03(水) 09:56 ---- F列が、グループ内でE列の最上部の値を拾ってくれれば話は早いんですケド。。あー。そういうこと。 F2 =IF(COUNTIF(A$2:A2,A2)=1,E2,"") で、いいんでないの? ※てか、F列なしで E2 =IF(COUNTIF(A$2:A2,A2)=1,SUMIF(A2:A$10,A2,D2:D$10),"") でもいいね。（GobGob） 2015/06/03(水) 10:10 ⇒ 10:22修正 ---- GobGobさん回答ありがとうございます。・A列グループのE列が0以下のみなら 0以下での最大値・〃 0より大きい数値のみなら単純に最大値申し訳ございません。 F列の値を算出するための、E列に対する私のロジックが間違っていました。。 F列の結果としては A列グループのD列の合計を、グループ内の最上部のみに表示するだけです。「E列の最大値を探す」というのは A列グループ内のF列の最上部以外を空白にするためだけのロジックとして考えていました。。なのでE列を以下の数式と、GobGobさんから頂いたF列の数式を組み合わせて図4のケースで試行すると、上記の私の期待した結果は得られませんでした。 E2=SUMIF(A2:$A$10,A2:$A$10,D2:$D$10) ★図4 A B C D E F 1 [name] [money] [money2] [total2] [total3] [total] 2 a -10 2 -8 -8 -8 3 b 1 2 3 7 12 ←ココが7になることを期待している 4 b -10 2 -8 4 5 b 10 2 12 12 ・・・ 10 ところが E列を以下の数式と、GobGobさんから頂いたF列の数式を組み合わせて上記と同じ図4のケースで試行すると、私の期待した結果が得られました! E2=SUMIF($A$2:$A$10,$A$2:$A$10,$D$2:$D$10) ★図4 A B C D E F 1 [name] [money] [money2] [total2] [total3] [total] 2 a -10 2 -8 -8 -8 3 b 1 2 3 7 7 ←good! 4 b -10 2 -8 7 ←(空白も)good! 5 b 10 2 12 7 ←(空白も)good! ・・・ 10 いろいろなケースでテストしてますが、今のところ結果は良好です。 --------------------------- ここまで書き込み中 GobGobさんの >※てか、F列なしで E2 =IF(COUNTIF(A$2:A2,A2)=1,SUMIF(A2:A$10,A2,D2:D$10),"") > でもいいね。を確認しました。コレです、コレ!。。ありがとうございました(笑) （equity） 2015/06/03(水) 10:57 ---- 一応。。。 E列もなしに D2 =IF(COUNTIF(A$2:A2,A2)=1,SUMIF(A:A,A2,B:B)+SUMIF(A:A,A2,C:C),"") でもOKのような。。。。（GobGob） 2015/06/03(水) 11:13 ---- GobGobさんありがとうございます。 >D2 =IF(COUNTIF(A$2:A2,A2)=1,SUMIF(A:A,A2,B:B)+SUMIF(A:A,A2,C:C),"") >でもOKのような。。。。みるみる簡略化されてゆく。。(笑) COUNTIFって便利ですね〜ただ本番データはB列とC列だけではなく約6個、列があります。さらに基本的に2行を結合して1行のデータとして扱っており結合されていない上段と下段のセルも混在している中で合計を算出しているので本番データの場合、F列なし止まりが個人的に良いかもです。手動入力列が少ないor複雑ではない場合は E列なしの構成が最高ですね! （equity） 2015/06/03(水) 11:48 ⇒ 12:03修正 ---- 結合している片方のセルにデータは無いんで問題ないのでは? =IF(COUNTIF(A$2:A2,A2)=1,SUMPRODUCT((A$2:A$10=A2)*B$2:G$10),"") （GobGob） 2015/06/03(水) 13:00 ---- GobGobさんどうもです。あと合計を算出するための複数の列は連続していないんですよね。。以下のように。。 B C D E F H 連続していない場合は、数式が長くなってしまうと認識しています。だったらF列なし止まりがキレイかな、と。（equity） 2015/06/03(水) 13:32 ---- データ列で途切れてる列が空白または文字列なら =IF(COUNTIF(A$2:A2,A2)=1,SUMPRODUCT((A$2:A$10=A2)*1,B$2:H$10),"") で、問題ないだろけど。どーなんだろね。（GobGob） 2015/06/03(水) 13:39 ---- GobGobさんまいどです。本番データを簡易化したものが以下です。 ★図5 A B C D E F G H I 1 [name] [money] [money2] [money3] [money4] [money5] [char] [money6] [total] 2 1 1 a 1 1 1 1 moji #VALUE! ←「8」を期待する! 3 1 1 ----------------------------- 4 1 1 b 1 1 1 1 moji #VALUE! ←「16」を期待する! 5 1 1 ----------------------------- 6 1 1 b 1 1 1 1 moji 7 1 1 ----------------------------- 頂いた数式の B$2:H$10 の部分に無理があるのではないかと認識しています。（equity） 2015/06/03(水) 14:44 ---- 複数列だめっぽいね・・・（検証不足） I2 =IF(COUNTIF(A$2:A2,A2)=1,SUMPRODUCT((A$2:A$11=A2)*(TEXT(B$2:H$11,"標準;-標準;0;!0")+TEXT(B$3:H$12,"標準;-標準;0;!0"))),"") （GobGob） 2015/06/03(水) 14:59 ---- GobGobさんありがとうございます。結果良好です! しかし図5のB〜H列のいずれかに見た目のみ日付として表示させたい以下の '06/04 を入力すると I列で上記の日付を数値に変換し、合計した値となってしまいます。 (B〜H列を選択して、ステータスバーに表示されている合計値と異なっていることを確認しました) 頂いた数式の以下の部分が原因だと認識しています。 "標準;-標準;0;!0" 上記の「!0」の部分が文字列の場合の扱い・表示方法と認識しています。。 (また「!」の意味が不明でした) （equity） 2015/06/04(木) 09:04 ---- 日付（シリアル値）のみなら I2 =IF(COUNTIF(A$2:A2,A2)=1,SUMPRODUCT((A$2:A$11=A2)*(B$1:H$1<>G$1)*(B$2:H$11+B$3:H$12)),"") ※シリアル値と文字列いろいろあるならTEXT併用。（GobGob） 2015/06/04(木) 09:19 ⇒ 9:29 間違い訂正 ---- ちなみにTEXTの第2引数は関係ないよ。 mojiが文字列でなくシリアル値なんで計算されてしまってるだけ。（GobGob） 2015/06/04(木) 09:31 ---- GobGobさん本番データに全て結果良好で反映させることができました! また、計算に使用する数値以外の数値、日付、文字列などに「'」を付与する必要なく正しい合計値が表示されています。ただ、E列なしの構成にしてから処理速度が落ちました。具体的には。。・手動入力箇所(図5の場合、A〜H列、2行目以下)のセルに入力を行い、Enterあるいは他のセルをアクティブにする。・手動入力箇所のセルの値をDeleteやBackspaceで削除する。上記のいずれかを行うとステータスバーに再計算:(プロセッサ ▲ 個):■% と表示されます。本番データの行数は1000行、 2行を結合しているので500件のデータがあります。しかし現在、入力されているのは6.5割ほどです。また、印刷用に他のシートに50件ずつ(計10シート)以下の数式で引っ張って表示させてます。 =INDIRECT(ADDRESS(ROW(),COLUMN(◆参照セル◆),4,,"◆参照シート◆")) 理由は50件ずつの合計値を算出したいからです。メモリは8GBです。以下のURL に記載されている方法で回避できましたがデータ入力者は初心者なので、手動で再計算させる手間をできるだけ省きたいです。。計算式が複雑でExcelが重い - インストラクターのネタ帳 http://www.relief.jp/itnote/archives/000046.php そもそもE列なしの構成で使用する数式が原因かも分かりませんが。。（equity） 2015/06/04(木) 13:23 ---- まぁ、重くなるかもね。配列数式やから。最初の質問から脱線してもーたし（レイアウト違いとか列数とか・・・） COUNTIF使って作業列+SUMIF列でいいと思う。あと、一応＞ =INDIRECT(ADDRESS(ROW(),COLUMN(◆参照セル◆),4,,"◆参照シート◆")) =INDEX(◆参照シート!$1:$1048576,ROW(),COLUMN()) でよさそうだけど。（GobGob） 2015/06/04(木) 13:39 ---- GobGobさんおんぶにだっこ状態ですいません。。 (-_-;) 後継者にGobGobさん並のスキルの人が現れ、簡略化・最適化してくれることを願うばかりです(笑) 以上、ありがとうございました。拍手! （equity） 2015/06/04(木) 13:55 ...; https://www.excel.studio-kazu.jp/wiki/kazuwiki/201506/20150602155558.txt - [detail] - similar

PREV NEXT

訪問者：カウンタ