[[20201214222803]]　『最頻値が文字の時』（塩）　ページの最後に飛ぶ

『最頻値が文字の時』（塩）

初めて利用します。
最頻値が文字のときに、どのような関数を使えば良いかわかりません。

A A A B といったデータで、
最も出現の多い文字を別のセルに表示させるにはどのような関数を使えば良いのでしょうか。
上のデータだとAの個数が3つ、Bが1つなので個数の多いAが返されると言った関数を教えていただきたいです。
MODE関数は数値のみということで、困っております。

また、
9列の表ですが
A A A Bのように、残り5つのデータが空欄ということもありえます。
その場合、どのような関数を使えば良いでしょうか。

ご指導よろしくおねがいします。

＜使用 Excel：Excel2019、使用 OS：MacOSX ＞

 	[A]	[B]	[C]	[D]	[E]	[F]	[G]	[H]	[I]	[J]	[K]
 [1]	A	B	A	B	A	B	B				B
 [2]		C	B	C	A	A	A	A	C		A
 [3]			A	C			C				C

 このようなレイアウトだとして、
 K1=INDEX(A1:I1,1,MATCH(MAX(COUNTIF(A1:I1,A1:I1)),COUNTIF(A1:I1,A1:I1),0))

 と入力して、CtrlとShiftキーを押しながらEnterで確定、配列数式にします。
 それを下にコピーするとこのようになります。

 同数のデータがある場合は、左から見て最初のデータが表示されます。
 もっといい方法があるかもしれませんが・・
 　
（tora） 2020/12/14(月) 22:59

tora様

ご指導ありがとうございます。
まさしく、私の望み通りの結果になり、感激しました。
現在Excel勉強中の身で、わからないことが多く途方に暮れていました。
countif関数を使うという発想すらありませんでした。
非常に勉強になりました。
心より感謝します。
（塩） 2020/12/14(月) 23:16

tora様

もしよかったら、
この関数を解説していただくことはできませんか。
不躾なお願いであることは承知しています。
初心者としては、
どういう発想でこの関数を導き出したのか、とても気になっています。

よろしくお願いします。

（塩） 2020/12/14(月) 23:38

 似たような回答したことあるかも
[[20201202174553]]　『特定の文字を除外した上で2番目に頻出なワードを秩x（いろいろ～）　>>BOT
 時間取れたらチャレンジさせてください！

 toraさんの式は数式の検証使うとわかりやすいですよ
https://www.google.com/amp/s/excel-master.net/worksheet-function/verification-formula/amp/

 勝手に一列目だけ解説すると、
 index({A,B,A,B,A,B,B},1,match(max({3,4,3,4,3,4,4}),{3,4,3,4,3,4,4},0))
 =index({A,B,A,B,A,B,B},1,match(4,{3,4,3,4,3,4,4},0))
 =index({A,B,A,B,A,B,B},1,2)
 =B

 です
 なので、同数だと一番左しか反映されないのは、matchの特性ですね

（稲葉） 2020/12/15(火) 06:38

稲葉様

ありがとうございます。
関数を数種類組み合わせると訳がわからなくなってしまうので、
Excelを使いこなすみなさんはどうやって考えているのか知りたかったです。

回答いただけたおかげで、理解が進みました。
ありがとうございました。
（塩） 2020/12/15(火) 07:07

 複数に対応した式考えてみました。
 １）以下の式を配列数式で確定
 K1=IFERROR(INDEX($A1:$I1,1,INDEX(MODE.MULT(IFERROR(MATCH($A1:$I1,$A1:$I1,0),"")),COLUMN(A1))),"")
                                                                                  ~~~~~~~↑~
                                                                     ここのA1は表の始まりが変更になっても変更しないでください。
 ２）必要な範囲にコピー（最大4種（9÷2の切り捨て）なので、K:N列でよいかと。
 ３）下記が実行結果例です。
 　　重複が無かった場合は、手当の必要ありますかね？

    |[A]|[B]|[C]|[D]|[E]|[F]|[G]|[H]|[I]|[J]         |[K]|[L]|[M]|[N]|[O]|[P]
 [1]|A  |B  |A  |B  |A  |B  |   |   |   |重複２      |A  |B  |   |   |   |   
 [2]|A  |B  |C  |D  |E  |F  |G  |H  |I  |重複無      |   |   |   |   |   |   
 [3]|A  |A  |B  |B  |C  |C  |D  |D  |E  |重複４      |A  |B  |C  |D  |   |   
 [4]|   |B  |B  |   |C  |D  |E  |   |   |空白あり    |B  |   |   |   |   |   
 [5]|   |C  |   |C  |   |D  |   |D  |   |空白重複２  |C  |D  |   |   |   |   
 [6]|A  |   |   |   |   |   |   |   |   |空白重複なし|   |   |   |   |   |

 式の意味
 =IFERROR(INDEX({A,B,A,B,A,B,"","",""},1,INDEX(MODE.MULT(IFERROR(MATCH({A,B,A,B,A,B,"","",""},{A,B,A,B,A,B,"","",""},0),"")),COLUMN(A1))),"")

 =IFERROR(INDEX({A,B,A,B,A,B,"","",""},1,INDEX(MODE.MULT(IFERROR({1,2,1,2,1,2,#N/A,#N/A,#N/A},"")),COLUMN(A1))),"")

 =IFERROR(INDEX({A,B,A,B,A,B,"","",""},1,INDEX(MODE.MULT({1,2,1,2,1,2,"","",""}),COLUMN(A1))),"")

 =IFERROR(INDEX({A,B,A,B,A,B,"","",""},1,INDEX({1,2},COLUMN(A1))),"")

 =IFERROR(INDEX({A,B,A,B,A,B,"","",""},1,INDEX({1,2},1)),"")←ここで列がK→Lになると、Column(B2)になり、2行目をIndexで指定したことになる

 =IFERROR(INDEX({A,B,A,B,A,B,"","",""},1,1,"")

 =IFERROR(A,"")

=A

 躓きやすいのが、Matchの配列数式のやっている意味かと思います。
 こんな感じで、検索値の配列をそれぞれ何番目にあるかで配列かさせてます。

        ┌──────────┐
        │                    ↓
 MATCH({A,B,A,B,A,B,"","",""},{A,B,A,B,A,B,"","",""},0)

         ┌───────────┐
         │                      ↓
 MATCH({1,B,A,B,A,B,"","",""},{A,B,A,B,A,B,"","",""},0)

           ┌─────────┐
           │                  ↓
 MATCH({1,2,A,B,A,B,"","",""},{A,B,A,B,A,B,"","",""},0)
           ↓
 MATCH({1,2,1,B,A,B,"","",""},{A,B,A,B,A,B,"","",""},0)
（稲葉） 2020/12/15(火) 09:28

稲葉様

初心者にもわかりやすく、丁寧なご回答ありがとうございます。

心より感謝します。
式の意味も理解できました。
非常に勉強になりました。

もし、もしよろしければ
エクセルで関数を使いこなす稲葉さんのような発想の流れを教えていただきです。

発想の流れがもう少し理解できると、
index関数やmatch関数の応用の仕方が分かる気がします。

1．文字の最頻値はすぐにはできない。
2.文字を数値に直そう。
のように、どうしてindexやmatchを使おうと思ったのかを教えていただくことはできますか・・・。

申し訳ありません。
もし、良かったらお願いいたします。

（塩） 2020/12/15(火) 18:31

 >MODE関数は数値のみということで
提示の例(A A A B といったデータ)なら
CODE関数で数値に変換してMODE関数を使用してみた
途中に空白が有ると駄目なので、実用性は有りませんが
お遊び程度で、
=CHAR(MODE(CODE(OFFSET(A1,,,1,COUNTA(A1:I1)))))

（はまちゃん） 2020/12/15(火) 18:54

 私は関数はへたっぴなので、ねむねむさん、半平太さん、笑さん（あいうえお順）を参考にしてみてください。
 ※toraさん、はまちゃんさん含め、勉強させていただいております。

 また、必ずしも1つの式に収める必要はありませんし、
 他の人から見れば理解できない式はできるだけ使わないほうがいいと思ってます。

 以上の前置きを踏まえたうえで・・・

 今回の例に限らず、まず分解して考えます。
 通常であれば、このまま作業列を使った状態でおすすめしてますが、今回は作業列が増えてしまったので、配列にまとめているわけです。
 本来は下図のままご利用されたほうが後々のメンテナンスは簡単かとおもいます。

    |[A]|[B]|[C]|[D]|[E]|[F]|[G]|[H]|[I]|[J]         |[K]|[L]|[M]|[N]|[O]|[P]|[Q]|[R]|[S]|[T]|[U]|[V]|[W]|[X]|[Y]|[Z]|[AA]
 [1]|A  |B  |A  |B  |A  |B  |   |   |   |重複２      |  1|  2|  1|  2|  1|  2|   |   |   |  1|  2|   |   |A  |B  |   |    
 [2]|A  |B  |C  |D  |E  |F  |G  |H  |I  |重複無      |  1|  2|  3|  4|  5|  6|  7|  8|  9|   |   |   |   |   |   |   |    
 [3]|A  |A  |B  |B  |C  |C  |D  |D  |E  |重複４      |  1|  1|  3|  3|  5|  5|  7|  7|  9|  1|  3|  5|  7|A  |B  |C  |D   
 [4]|   |B  |B  |   |C  |D  |E  |   |   |空白あり    |   |  2|  2|   |  5|  6|  7|   |   |  2|   |   |   |B  |   |   |    
 [5]|   |C  |   |C  |   |D  |   |D  |   |空白重複２  |   |  2|   |  2|   |  6|   |  6|   |  2|  6|   |   |C  |D  |   |    
 [6]|A  |   |   |   |   |   |   |   |   |空白重複なし|  1|   |   |   |   |   |   |   |   |   |   |   |   |   |   |   |    
 [7]|└ |─ |─ |─ |─ |─ |─ |─ |┘ |            |└ |─ |─ |─ |─ |─ |─ |─ |┘ |└ |─ |─ |┘ |└ |─ |─ |┘  
 [8]|   |   |   |(1)|   |   |   |   |   |            |   |   |   |   |(2)|   |   |   |   |   |(3)|   |   |   |(4)|   |

 (1)　データ
 (2)　K1=IF(A1="","",MATCH(A1,$A1:$I1,0))
 (3)　T1=IFERROR(INDEX(MODE.MULT($K1:$S1),COLUMN(A1)),"")
 (4)　X1=IF(T1="","",INDEX($A1:$I1,,T1))

 それ以前に、こんな計算しなくても済むような表設計が何より重要だと思います。

 あとは数こなして、恥かいてもいいので回答者として先輩方に交じって回答されてはどうですか？

（稲葉） 2020/12/15(火) 19:27

はまちゃん様
ありがとうございます。
勉強になります。とても嬉しいです。
（塩） 2020/12/15(火) 20:15

稲葉様

私の無茶苦茶な要望にお答えくださりありがとうございます。
挙げていただいた方々からも勉強させていただきます。

助言いただいたように、表設計から見直すことも視野に入れて学びます。
恥は既に晒しているので、この掲示板から多くを学び取れるよう努力します。
本当にありがとうございます。
（塩） 2020/12/15(火) 20:20

コメント返信：

[ 一覧(最新更新順) ]

[[20201214222803]] 『最頻値が文字の時』（塩） ページの最後に飛ぶ

[[20201214222803]]　『最頻値が文字の時』（塩）　ページの最後に飛ぶ