Excelの関数詳しい方お願いします。

Excelの関数詳しい方お願いします。
A1~A4の範囲で赤、青、黄緑、緑がランダムで入っています。
それぞれの色の数を出したいです。

COUNTIFだと緑と黄緑が重複するため除外の数式あればご教示願います。
もちろん他の数式でも結構です。
よろしくお願い致します。

質問者からの補足コメント

• 皆様、ご丁寧に解説頂きましてありがとうございました。
  そして大変申し訳ございません、完全に説明不足と例えが悪かったです。
  応用できると思いましたが自分の裁量ではできませんでした、下記改めて詳細説明させて頂きます。
  
  Excelバージョン：2021
  
  A1:A4に添付のような表示がランダムに入っており、B列はカウント対象の色、C列で各色のカウントをしたいです。
  A列表記の例「1個目:赤 / 2個目:青 / 3個目:緑」
  /の前後にスペースありです。
  1セル内に同じ色が2回ある場合は2回とカウントします。
  可能であれば作業列なしで求めたいです。
  
  
  最初からこの説明をさせて頂くべきでした。
  ご丁寧に添付までつけて頂いた方もいらっしゃったのに申し訳ございません。

  
    補足日時：2023/01/19 23:30

No.11 ベストアンサー 回答者： goomania 回答日時： 2023/01/20 10:45

NO.10です。

連投失礼します。

ご質問者の例示には登場しないので、考慮からもれていたのですが、B列に「黄」が登場した場合、前回回答では正しい結果にならないことに気づきました。これを修正した数式が以下です。C1に、

=SUMPRODUCT(1-ISERROR(FIND(":"&B1&":",TRIM(TEXTSPLIT(TEXTJOIN("/",1,$A:$A),,"/"))&":")))

という数式を記述して、下方向へコピーすることになります。

この回答へのお礼

無事に解決しました^^
私の説明不足でご迷惑お掛けしてしまいましたが何度も丁寧に説明頂き本当にありがとうございました。今回ベストアンサーに選ばせて頂きました。

お礼日時：2023/01/20 22:14

No.13 回答者： mike_g 回答日時： 2023/01/20 20:12

[質問者からの補足コメント]へのコメント、

貴方自身が「最初からこの説明をさせて頂くべきでした」と自覚されたとおりです！
》 可能であれば作業列なしで求めたいです。
私には生憎(ｱｲﾆｸ)そこ迄の能力が無いモンでえ～ｯ、お粗末ながら、ここに付けた添付図(Excel-2019)の如く、作業列・作業行の縦横無尽使用方式をお目に掛けましょう

１．式 =SUBSTITUTE(A1&"/",":","/") を入力したセル E1 を下方に(3行)ズ
￣￣ズーッとオートフィル
２．次式を入力したセル G1 を右方3列＆下方3行オートフィル
￣￣=(LEN($E1)-LEN(SUBSTITUTE($E1,"/"&G$6&"/","")))/LEN("/"&G$6&"/")
３．式 =SUM(G1:G4) を入力したセル G5 を右方にオートフィル
４．式 =INDEX(G$5:J$5,MATCH(B1,G$6:J$6,0)) を入力したセル C1 の
￣￣フィルハンドルを「エイヤッ!」とダブルクリック

この回答へのお礼

無事に解決できました、本当にありがとうございました。
以後、説明文に不足がないように気を付けます。

お礼日時：2023/01/20 22:17

No.12 回答者： Belltreeriver 回答日時： 2023/01/20 20:04

C1=(len(textjoin("/",1,$A$1:$A$4))-len(SUBSTITUTE(textjoin("/",1,$A$1:$A$4),":"&B1,"")))/(len(B1)+1)

下へフィルコピー

この回答へのお礼

ありがとうございます！無事に解決できました^^

お礼日時：2023/01/20 22:15

No.10 回答者： goomania 回答日時： 2023/01/20 10:27

No.7前回です。

前回回答に補足します。

前回回答の

=SUMPRODUCT(1-ISERROR(FIND(":"&B1,TEXTSPLIT(TEXTJOIN("/",1,$A$1:$A$4),,"/"))))

という数式の「$A$1:$A$4」はデータ範囲がA1～A4という意味です。

データ範囲を実際の範囲に合わせて調整願います。

システム負荷を気にしない(実行速度は気にしない)というのであれば、

=SUMPRODUCT(1-ISERROR(FIND(":"&B1,TEXTSPLIT(TEXTJOIN("/",1,$A:$A),,"/"))))

とすれは、A列全てが対象となります。

No.9 回答者： d-q-t-p 回答日時： 2023/01/20 09:44

あっと一部「,」のままだった。

=SUM(N(ISNUMBER(FIND(C1,FILTERXML("<x><y>"&SUBSTITUTE(TEXTJOIN(" / ",,A:A)," / ","</y><y>")&"</y></x>","//y")))))

この回答へのお礼

無事に解決できました、ご教示いただきありがとうございました。

お礼日時：2023/01/20 22:16

No.8 回答者： d-q-t-p 回答日時： 2023/01/20 09:33

Excel2021じゃしょうがない。

=SUM(N(ISNUMBER(FIND(C1,FILTERXML("<x><y>"&SUBSTITUTE(TEXTJOIN(",",,A:A)," / ","</y><y>")&"</y></x>","//y")))))
下にコピー

No.7 回答者： goomania 回答日時： 2023/01/20 09:29

No.5です。

バージョンは2021ということなので、添付画像のように、

C1セルに

=SUMPRODUCT(1-ISERROR(FIND(":"&B1,TEXTSPLIT(TEXTJOIN("/",1,$A$1:$A$4),,"/"))))

を記述し、下方向へデータのある位置までコピーする

でどうでしょう。

前提条件としては前回回答と同様、B列は「赤」「青」････等の色名だけ入力されていて、セルの書式設定の表示形式をユーザー定義で「＠”の数"」として「〇の数」と見せているものとします。
※補足説明だと「〇の数」見せておらず、色名そのもののようですが、結果に影響はありません。

No.6 回答者： chonami 回答日時： 2023/01/19 12:27

特に作業列が嫌とは書いてないので、一旦どこかの列に一色ごとに並べてしまってはどうでしょう？

365をお使いなら

=TOCOL(TEXTSPLIT(TEXTJOIN("，",,A1:A4),"，"))

で，やセルごとに区切った色の名前が1列に並びますからそこからCOUNTIFで集計してはどうでしょうか？
ただし、COUNTIFの範囲指定の際に抽出したセルをクリックすると列番号#になりCOUNTIFで使えなくなってしまうので列ごと範囲指定するなどをお勧めします。

質問の際にはExcelのバージョンは必ず書いておいてください。

No.5 回答者： goomania 回答日時： 2023/01/19 09:42

EXCELのバージョンが365ならTEXTSPLIT関数が使えます。

添付画像をご覧ください。
A列にカウントすべきデータがあり、B列にカウント対象の色名があるとします。
また、B列は「赤の数」のように表示されていますが、セルの中身は「赤」でセルの書式設定の表示形式をユーザー定義で「@"の色"」として「〇の色」と見せているものとします。

C1セルに

=SUMPRODUCT(1-ISERROR(FIND(","&B1&",",","&TEXTSPLIT(TEXTJOIN(",",1,$A$1:$A$4),,",")&",")))

という数式を記述し、下方向へコピーしています。

なお、B列にカウントすべき色が入力されている行を超えて、C列を下方向へコピーすると0が並んでしまいます。
これを避けるのであれば、上記数式を

=IF(B1="","",SUMPRODUCT(1-ISERROR(FIND(","&B1&",",","&TEXTSPLIT(TEXTJOIN(",",1,$A$1:$A$4),,",")&","))))

として、0でなく空白になるようにしておけばよいと思います。

No.4 回答者： mike_g 回答日時： 2023/01/19 00:46

添付図参照(Excel 2019)

Fig-1 において、
範囲 B1:B４ を“ユーザ定義”で
@"の数"
に書式設定して、上から文字列の 赤、青、黄緑、緑 を入力。
そして、
範囲 A1:A4 を選択⇒Alt+AE⇒“コンマや…データ”に目玉が入れ⇒[次へ]⇒“コンマ”にチェック入れ⇒[次へ]⇒[表示先]ボックス内にマウスポインタを無造作に投げ入れ⇒そこに在った文字 $A$1 を消去して、マウスでセル E1 をチョーン⇒[完了]をパシーッ⇒もし何やら奇妙なメッセージが出ても知らぬ存せぬで[OK]をパシーッ

ここで図Fig-1の訂正です。
C列の 0 はすべて無かったことにしてください。m(_._)m

式 =COUNTIF(E$1:G$4,B1) を入力したセル C1 のフィルハンドルをマウスで「エイヤッ!」とダブルクリック
その結果が Fig-2 でゴザンスｯ！