Excelの集計関数について教えてください

質問

最近、会社を立ち上げ営業の進捗管理をExcelで始めたばかりの者で、営業の進捗管理、というよりはExcelの関数（？）に悩み、こちらで質問いたしました。

表は2つあり、最初に商談状況という表へ、顧客ごとに、状況（問合せ・ヒアリング訪問・見積書提示・受注・失注）とその対応日を入力します。状況は、対応日の一番右側にある黄色いセルの日付に対応する内容を入力します。つまり、顧客ごとに最新の状況が商談状況で見れるようにしています。

その後、月次進捗表という表で、月別と状況別に集計をしております。しかし、件数が多くなってきて手作業でのミスが目立ち始めました。そもそも、これを集計と呼ぶかは別としてですが。

月次進捗表の赤枠内（B5～H19）に、Excelの関数を書けば簡単に解決できると思ったのですが、状況ごとと月ごとの集計ができません。添付のファイルはイメージとなりますが、手で入力したものです。

どのような、関数を書けばよいかお知恵の拝借をいただけませんでしょうか？Webで調べながら、CountifやCountifsを試してみたのですが、思ったような結果になりませんでした。

何卒よろしくお願いします。

回答No.4

imogasi

私のやって見た例ですが、
「商談状況」表の、C列からH列までに、日付が入っているとする。
日付は、日付シルアル値で、入っていることは前提です。日付シルアル値が初耳なら
WEB照会のこと。
C1:H1セルには「a b c d e f g」が入っているとする。
実際は「問い合わせ日」、「ヒアリング訪問日」・・ですが、入力の手抜きで略。
第６行目の例では、J列に式　
=INDEX($A$1:$F$1,1,MATCH(MAX(C6:H6),C6:H6)+2)　（＋２は、「状況」の見出しが第3列から始まるからです）
日付けシリアル値の性格から、MAXを採るのは、（交渉）最終日を探すということです。
その後、このJ列での、「a b c d e f g」の各々の件数をCOUMTIF関数で
出せば（交渉最終状況が）出来る。
月の進行ともに、J列に当たる列が右にズレざるを得ない。
この点が難しくする点である。
エクセル関数では、（配列数式を使わない場合は）、J列などに「aーｇ」を一旦出さないと（確定しないと）、省いて＝空では、件数をＣＯＵＮＴＩＦ関数で出せない。
したがって、「月次営業進捗表」で、月の推移で式の複写で対応できるように、しなくてはならない。
＝＝＝＝
これは関数向けの課題ではないと思う．データベースの（ソフトの）利用をすべきでしょう。
又はVBAを併用するとか。
ーー
どうせ言っても振り向いてくれないだろうが、言っておきたい。
ーー
この質問の処理は、商談の進展ごとに（個別にメモでも作って）データを入力する（シートへの入力もあり得る）。
得意先名ー年月日ー（その日での最終）進捗段階名のようなデータ。
エクセル表にするのでなく、一旦データとして記録する。得意先候補別ー日付ー状況の対応データです。
その集積データ結果について、総括する年月が決まった段階で、その実施年月１か月間のものを抜出す。
その１つの月のうちで、複数の進捗段階があり得るので、後の方（ＭＡＸ）を採る。
後戻りはないもののとする。
Ａ表で言えば、進捗段階名を割り出す。得意先候補別です（Ｘ表とする）。
打ち切り以外で、進捗なしの件数も必要だろう。
ーー
そしてそれらの中から、進捗段階名ごとの件数を表にする。最右列に最新月の件数を
加えるなどする。月ごとの単表もあり得ると思う。
これらはExcelだけでは複雑になるので不適と思う。

回答No.3

SI299792

14行は「2023/1/1」の様に入っていて、
表示形式、ユーザー定義｢yyyy"年"m"月"｣にしてあるものとします。

このままでは難しいので、Ｈ列をワークエリアに使います。
B3: =SUBSTITUTE(IFERROR(LOOKUP(H3,C3:G3,C$2:G$2),""),"日","")
下へコピペ。
H3: =MAX(C3:G3)
表示形式、ユーザー定義｢m"月"d"日";;｣
下へコピペ。
I14 2024年8月
（ダミーで１月余計に入力します。目障りならフォントを白にして見えなくして下さい。）
A15 =SUBSTITUTE(INDEX($2:$2,ROW()-12),"日","")
下へコピペ。
B15 =COUNTIFS($B$3:$B$11,$A15,$H$3:$H$11,">="&B$14,$H$3:$H$11,"<"&C$14)
表示形式、ユーザー定義｢0"件";-0;｣
右下へコピペ。

回答No.2

kon555

補足というか、書き漏れがありました。
No1で「最後に『月次進捗表』ではCountifsを使い～」と回答しましたが、この際にCountifsでカウントするのは新しく作った『最新状況』の表です。『商談状況』ではありません。ご注意ください。

回答No.1

kon555

少し表を弄る必要がありますが、Countifsで解決できます。
　まず『商談状況』の表の日付については、年も省略せず入力するようにして下さい。また表示形式も「年」が分かるようにして下さい。
　次に「状況」の入力は変な表記揺れや誤字脱字がないようにして下さい。入力規則のプルダウンリストを使う事を推奨します。
https://www.pc-koubou.jp/magazine/38812
　この2つは集計する上で非常に重要です。

　次に『商談状況』の表に、「最新日付」などの枠を1列追加して下さい。ここにはその行の最新状況の日付が自動で反映するようにします。
「最新状況の日付」とは、つまり同じ行の日付の中で最も新しい日付になりますので、MAX関数で抽出できます。
https://net-business888.com/excel-day-max-min/

　次に『商談状況』と同じレイアウトの別表を作ります。仮に『最新状況』とします。
　この『最新状況』は『商談状況』の対応するセルについて、入力値が最新日付と同じであればそのセルを参照するようにIF関数を組みます。
https://with-pc.com/excel-match-circle/
(ちなみに最新日付セルを作らず、if関数の中にMAX関数を入れ込む事も出来ますが、慣れないうちは1つずつ見えるようにした方が無難です)

　最後に『月次進捗表』ではCountifsを使い、各進捗状況かつ、その月の日付の個数を数えます。
　範囲内の日付(例えば2023年1月1日から1月31日まで、のような)をカウントするのは便利な集計方法なのでマスターしましょう。
https://blog02.aqua-school.com/2022/07/03/excel-65/

　このような組み合わせにすれば、現状のレイアウトやワークフローを変更することなく自動集計が可能になります。
　最終的には票の作り方など自体も見直した方がいいとは思いますが、そのあたりはExcelに習熟してから、あるいは詳しい人を雇ってからでいいと思います。
　もし分からない事があれば、補足等で書いて頂ければ対応します。