excelで以下のような表とした場合、作業列なしでカウントする方法はありますか?
A列 B例
1/1 りんご
1/1 りんご
1/1 みかん
1/2 ぶどう
1/3 みかん
1/3 りんご
2/1 ぶどう
2/2 りんご
2/2 みかん
2/4 みかん
2/5 りんご
2/5 ぶどう
2/5 りんご
欲しい答えは、同じ日付は1としてカウントするため、りんごで数える場合は4、みかんで数える場合は4、ぶどうで数える場合は3としたいです。
なお、何を数えるかは、別セルに入力したもので指定します。
また、更に1月とか2月などの期間を絞って、同様にカウントする方法はありますか?
欲しい答えは、1月のりんごで数える場合2、1月のみかんで数える場合2、1月のぶどうで数える場合1としたいです。
なお、何月の何を何を数えるかは、別セルに入力したもので指定します。
A 回答 (5件)
- 最新から表示
- 回答順に表示
No.5
- 回答日時:
No.3です。
BelltreeriverさんがUNIQUE関数を使った例をお示しになったのですが、利用環境がスプレッドシートのようです。
スプレッドシートだと、COUNTIF関数の「対象範囲」に「配列」を利用できるのですが、EXCELだとエラーになってしまいます。
そこで、EXCELでも使用できるUNIQUE関数を使った例を考えてみました。
前回回答で使用した添付画像のG3セルに、
=SUMPRODUCT(1-ISERROR(FIND(E3&F3,UNIQUE(INDEX(TEXT(A2:A14,"d/m")&B2:B14,0)))))
という数式を記述することになります。
No.4
- 回答日時:
unique関数を使用した1例です。
D1=countif(unique(index(text(A1:A13,"d/m")&B1:B13,0)),"*"&C2&C1&"*")
No.3
- 回答日時:
fujillinさんが、COUNTIFS関数を使用した数式をお示しになったので、別の数式をお示しします。
fujillinさんの数式はCOUNTIFS関数を使って、最初に出現する組合せを判別することで、重複しないものを抽出する手法です。
この回答ではMATCH関数を使って「最初に出現する」を判別して重複を排除しています。MATCH関数は範囲(または配列)の中から検索値と一致するものを探すことができる関数ですが、最初に登場するものしか検出できないという欠点もよく知られています。
つまり、複数同じものがあっても、最初に登場するものしか検出できないので、これを逆手にとって、「自分と同じものが検出される位置が自分の位置と同じなら最初に登場するのは自分自身である」と判断して重複排除しています。その考えで、数式を組み立ててみました。
前提条件としては日付は「○月○日」と表示されているが「2023/1/5」のように入力し、中身はシリアル値で、セルの表示形式を「m"月"d"日"」としているものとします。
また、カウントする対象月の指定セルである添付画像のE2セルは入力規則でドロップダウンリストから1~12の数値を選択するものとし、セルの表示形式を「0"月"」として「○月」と表示しているものとします。
>欲しい答えは、同じ日付は1としてカウントするため、りんごで数える
>場合は4、みかんで数える場合は4、ぶどうで数える場合は3としたい
>です。
というのが、最初のご質問です。
添付画像をご覧ください。G2セルに
=SUMPRODUCT((MATCH($A$2:$A$14&$B$2:$B$14,$A$2:$A$14&$B$2:$B$14,0)=ROW($A$1:$A$13))*($B$2:$B$14=F2))
という数式を記述しています。
>なお、何を数えるかは、別セルに入力したもので指定します。
ということなので、F2セルに「数えるもの」を入力するようにしています。
>更に1月とか2月などの期間を絞って、同様にカウントする方法は
>ありますか?
というのが次のご質問です。
添付画像をご覧ください。G3セルに
=SUMPRODUCT((MATCH($A$2:$A$14&$B$2:$B$14,$A$2:$A$14&$B$2:$B$14,0)=ROW($A$1:$A$13))*($B$2:$B$14=F3)*(MONTH($A$2:$A$14)=E3))
という数式を記述しています。
>なお、何月の何を何を数えるかは、別セルに入力したもので
>指定します。
ということなので、E3に「何月」、F3に「数えるもの」を入力するようにしています。この場合も同日のカウントは1つとして数えています。
なお、各数式のセル範囲はご質問者が掲示されたデータ内容そのままですので、実際のデータにあわせて変更してください。
その際、ROW($A$1:$A$13)の部分はMATCH関数が返す表中のデータ範囲の行位置を示します。つまりシート2行目~14行目までデータがある場合、MATCH関数が返す範囲は1行目から13行目という意味になります。
fujillinさんのご指摘だと、
>MONTH関数は配列計算には対応していない
とのことです。
本回答の数式はMONTH関数を使用していますが、正しい結果を返す気がします。私の検証不足かも知れませんが・・・・・・。
No.2
- 回答日時:
No1です。
連投失礼。
式中のセル範囲を修正するのを忘れました。
No1の最後の条件式は
(A1:A999<"2023/2/1"*1)
が正ですね。
また、定数部分を先に計算して
(A1:A999<44958)
としておいた方が、計算量はわずかながら軽減されると思います。
No.1
- 回答日時:
こんばんは
>作業列なしでカウントする方法はありますか?
配列計算で各行を評価して、TRUEになる(=該当する)行の数をカウントすればできそうですね。
作業列を用いるのに比して、無駄な計算が飛躍的に多くなるので、計算負荷がかかります。
例えば、「りんご」をカウントするのであれば、
=SUMPRODUCT((COUNTIFS(OFFSET(A1,,,ROW(A1:A999)),A1:A999,OFFSET(B1,,,ROW(B1:B999)),"りんご")=1)*(B1:B999="りんご"))
で、ご提示のデータの場合は4の結果が得られます。
「みかん」、「ぶどう」の場合は、式中の"りんご"の部分(=2か所)を変更することで、それぞれ4、3が得られます。
※ A列の日付は全て、シリアル値であるものと仮定しています。
※ 配列計算なので、対象範囲を広くしすぎると計算が重くなります。
必要最な範囲に絞っておくことをお勧めします。
(上式ではA1:B999の範囲を対象にしてあります)
※ 365環境であれば、UNIQUE関数を利用することで重複を除くことができますので、式を簡略化することが可能であろうと思います。
>1月とか2月などの期間を絞って、同様にカウントする方法はありますか?
最初に述べたように、各種条件をANDで評価(=乗算)して、結果がTRUE(=1)になるものをカウントしていますので、上記の式に「期間の条件」を追加することで計算可能です。
ただし、条件が多くなるほど、計算負荷は「条件数×セル範囲数」という形で増加します。
簡単な例として、上式に
(A1:A13<"2023/2/1"*1)
の判定式を追加すれば、(片側判定のみですが)2月より前(=1月)を条件にすることになるので、結果は2となります。
MONTH関数が使えれば簡単ですが、MONTH関数は配列計算には対応していないようなので、上記のようなプリミティブな式にしています。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) IF 関数で「〇〇 という文字を含む場合」の分岐処理で表示された数字はSUMで数字集計できますか? 3 2022/08/02 16:29
- Excel(エクセル) Excelでの複数条件のカウントについて 1 2022/09/25 07:40
- Excel(エクセル) エクセルシートの合計の変動 5 2022/04/05 15:56
- Excel(エクセル) Excelについて A1からA12まで、1月〜12月と入力し、 B1からB12の範囲に、C1とD1に 4 2022/05/26 22:48
- Visual Basic(VBA) Sheet1の出荷日と品名が並んだ表からSheet2の品名別出荷日別の個数一覧表を作っています。 オ 3 2022/12/01 23:54
- その他(Microsoft Office) 【スプレッドシート】白色のセルをカウントしたい 2 2023/02/24 07:39
- Excel(エクセル) [日程計]の計算式 1 2022/05/20 13:43
- その他(Microsoft Office) 従業員増減対応で当番種類の増減対応な当番表 21 2022/07/19 07:30
- Excel(エクセル) エクセルの関数について 5 2023/04/30 17:24
- Excel(エクセル) エクセル VBA セルの結合 2 2022/09/07 11:48
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
ハーブソルトを代用できるもの...
-
7文字以上のフルーツをできるだ...
-
ラズベリーの種の抜き方を教え...
-
何という名前の虫か教えてください
-
Excelでの全通りの組み合わせ出...
-
教えてください!!
-
煮物こんにゃくの冷凍保存について
-
梶井基次郎の檸檬
-
こんなスイカは食べても大丈夫...
-
Excel MATCH関数で検索範囲内...
-
【ぶどう】 友人からデラウェア...
-
職場で隣の人についてです。飲...
-
4歳の子供がさくらんぼを種ご...
-
「であり」と「であって」の使...
-
冷蔵庫の中に虫が・・・
-
北海道の方に喜ばれる食べ物と...
-
パイナップルにたかる虫
-
ローリエって何のために入れる...
-
中黒「・」か読点「、」か?
-
メロンとヨーグルトは合わない...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
7文字以上のフルーツをできるだ...
-
ハーブソルトを代用できるもの...
-
4歳の子供がさくらんぼを種ご...
-
教えてください!!
-
筍の上の部分が緑色になってい...
-
ラズベリーの種の抜き方を教え...
-
中黒「・」か読点「、」か?
-
さくらんぼを塩水で洗うのは?
-
Excelでの全通りの組み合わせ出...
-
Excel MATCH関数で検索範囲内...
-
スポンジケーキってどのくらい...
-
煮物こんにゃくの冷凍保存について
-
梶井基次郎の檸檬
-
メロンとヨーグルトは合わない...
-
パイナップルにたかる虫
-
さくらんぼに似ているが、さく...
-
ふにゃふにゃになったキウイっ...
-
パイナップル果汁はなぜ泡立ち...
-
「であり」と「であって」の使...
-
エクセルで数字を入力したら文...
おすすめ情報