初心者ユーザーです。Excel関数についで教えてください。

Question

図のようなA列に順次増えていく数字があり、指定したセルの下のセルの数字の個数と割合を知りたいのです。

例としてA2セルの5を指定したとして、次のセルA3セル8の個数と割合ということになります。ど

のような関数をどのように使えばよいのかお教えください。ペコリ

Answer 1

返信ありがとうございます。

ただ、手入力で良いのでご希望の数値が入った表（具体例）を提示していただければと思ったのですが、言葉足らずで失礼しました。

1.から 4.に書いていただいた補足と最初に提示されている図を見比べてみたのですが、今回の場合、図２のように列あるいはセル位置を指定する必要があるように思います。

・図２

ここで、

• E2セルには、A列で検索したい値（指定値＝特定数）を手入力します。
• F2セルには、E2セルの値を A列で検索した結果の個数を、数式により表示させます。［補足の 2. の対処］
• B列には、E2セルの値を A列で検索した結果として得られるセル位置の更に直下のセルの数値（複数ある可能性が大きい）を、数式により B2セル以下に並べて表示します。［補足 3. の対処］
• C列には、B2セル以下に並べられた数値の個数を、数式により C2セル以下に並べて表示します。。［補足 3. の対処］
• D列には、C2セル以下に並べられた値の個数を A列全体の数値の個数で除算した結果を、数式により D2セル以下に並べて表示します。［補足 4. の対処］

ご希望の表示内容と違っているような場合には、具体的な表の配置が分かる形の図と、どこにどのような値を表示させたいのか、といった情報の提供（個人情報に関わる部分は隠すなどして）をお願いします。

以下、図２を基に、実際の数式を考える際の不明点を書き出してみました。

1. E2セルで指定した値の入っているセルの直下のセルの値を A列で検索する際に、E2セルの値の直下にあるセルの値のみの個数を調べるのか、または、E2セルの値の直下にあるセル以外の該当の値も含めて個数を調べるのか、どちらでしょう？。
   例えば、図３で黄色に色付けされているセルの値を個数に含めるかどうかです。
   ・図３
   
2. B列に表示される数値について、同一の数値が複数表示される可能性があります。
   これをそのまま表示するのか、一つだけにして表示するのか、どちらでしょう？。
   例えば、図４で黄色に色付けされているセルの値を 3行目も 4行目も両方とも表示するかどうかです。
   ・図４
   
3. B列に表示される数値について、並べる順番は A列に並んでいる順番で良いのでしょうか？。
   それとも、表示される数値を昇順／降順に並べ替えるのでしょうか？。
   例えば、図２、図３、図４は、いずれも A列に並んでいる順番そのままで表示しています。
4. 最新版の Excel（ Microsoft365 の Excel、Excel2021 など）か、旧来の Excel（ Excel2019 以前）か、どちらをお使いでしょうか？。
   最新版であれば、より多くの関数が使え、場合によっては簡潔で効率的な数式が書けますので。

こういった情報を更に提示していただければ、とりあえず、ご希望に近い動作をする具体的な数式をご紹介できるかと思っています。

以下は、上記不明点について、下記のように仮定して考えてみた場合の数式の一例になります。

• 不明点の 1.
  E2セルの値の直下にあるセル以外の該当の値も含めて個数を表示する。（図３の黄色に色付けされたセルの値も個数に含める。）
• 不明点の 2.
  同一の数値が複数ある場合、両方とも表示する。（図４の黄色に色付けされているセルの値を両方とも表示する。）
• 不明点の 3.
  A列に並んでいる順番そのままで表示する。
• 不明点の 4.
  旧来の Excel でも動作する数式とする。（最新版でしか使えない関数は使わない。）

以下の数式では、A列には最大で 10000行（厳密には見出しを除いた 9999行）の数値が入力されるものと仮定しています。

・数式２

図２の B2セルに入れる数式です。

=IFERROR(1/(1/INDEX(A$1:A$10000,SMALL(IF(A$1:A$10000=E$2,ROW(F$1:F$10000),10^6),ROW(F1))+1)),"")

旧来の Excel（ Excel2019 以前）の場合、この数式を B2セルに入力後（あるいはコピー・貼り付け後）、Ctrl＋Shift＋Enter で確定して配列数式にする必要があります。

配列数式になると、自動的に数式全体が { } で囲まれますので、必ずこれを確認するようにします。

あとは、この B2セルに入れた数式を、必要なだけ下方向（行方向）にコピーします。

コピーする行数は、最大どのくらいの数の数値が表示される可能性があるのかで決めますが、ここでは、とりあえず 10000行分（厳密には見出しを除いた 9999行分）コピーしています。

＜数式の動作概要＞

最初に、IF(A$1:A$10000=E$2,ROW(F$1:F$10000),10^6) の箇所で、A列の数値の中に E2セルの数値と一致する数値がある場合はその行番号を、そうでない場合は 10^6（1000000）という数値を当てはめた配列を作り出し、SMALL 関数に渡します。

※図２の場合の配列の例

{1000000;1000000;3;1000000;1000000;1000000;1000000;8;1000000;10;1000000;1000000;1000000;1000000;1000000;1000000;1000000;1000000;19;20;1000000;・・・}

次に、SMALL(・・・,ROW(F1))+1 の箇所で渡された配列の中から最小の行番号を取り出し、それに +1 した行番号（＝直下のセルの行番号）を INDEX 関数に渡します。ここで、ROW(F1) の箇所は行番号を取り出す際のカウンター（※）として使います。

最後に、INDEX(A$1:A$10000,・・・) の箇所で、A列の中から渡された行番号の値を表示します。

尚、IFERROR(1/(1/・・・),"") の箇所で、INDEX 関数から返される値が 0 か ""（空白の文字列）あるいはエラー値などだった場合に、""（空白の文字列）を表示させるようにしています。

※カウンターについて

この数式を下方向にコピーしていくことで、以下のように ROW(F1) の値は変化します。

ROW(F1)=1

ROW(F2)=2

ROW(F3)=3

・

・

この数値を使い、

1番目に小さい行番号（最小の行番号）

2番目に小さい行番号

3番目に小さい行番号

・

・

というように、配列の中から行番号（ E2セルの数値と一致する数値がある行番号）を小さい順に取り出すことが出来ます。

尚、ROW 関数は行番号だけを取り出すので、どの列を使っても良いのですが、念のため F列を使い ROW(F1) のように指定しています。

・数式３

図２の C2セルに入れる数式です。

=IF(B2="","",COUNTIF(A$1:A$10000,B2))

この数式を C2セルに入力後、下方向（行方向）に数式２と同じ行数分コピーします。

・数式４

図２の D2セルに入れる数式です。

=IF(B2="","",C2/COUNT(A$1:A$10000))

この数式を D2セルに入力後、下方向（行方向）に数式２と同じ行数分コピーします。

・数式５

図２の F2セルに入れる数式です。

=COUNTIF(A$1:A$10000,E2)

これらの数式は一例に過ぎません。

また、不明点について上記のように仮定した場合の数式になっていますが、よろしければ、ご自身の環境に当てはめてお使いになってみてください。

図２、図３、図４は、実際にこれらの数式を入力して動作している状態です。

尚、数式をコピーする行数が増えれば増えるほどその処理時間も長くなりますので、ご注意ください。

例えば、数式を 10000行分コピーしている状態で E2セルの指定値や A列の値を変更した場合、最終的な結果が表示されるまでの時間は、手元の環境で約 3～6秒ほど掛かっています。

コピー数を少なくすればもっと短い時間で結果が表示されると思いますが、ご自身の環境で実際にどのくらいの時間が掛かるかは、ご自身で確認していただくしかありません。

Windows11 と Excel2021 の組み合わせで動作確認しています。

ご参考になれば幸いです。

＜追記＞

最初に「初心者ユーザー」と、ご自身で書かれているので追記しますが、失礼がありましたらお許しください。

もし、ここでご紹介している数式が、ご自身の手に余るとお考えで、業務で使用するような Excel 表なのでしたら、Excel 作成に長けた専門の業者さんに依頼することも検討されたほうが良いかもしれません。

一つ一つの数式を文章で詳細にご説明するのは難しく、お手元の表がどのようなものか推測しながらのやり取りでは長時間掛かることも予想されますので。

尚、専門の業者さんについて当方では全く存じ上げませんので、必要な場合はご自身でお探しになってみてください。悪しからずご了解のほど。

＜文章の修正＞

図２の下の「ここで、」以下の説明の中で、「B列には、・・・」の説明文が分かりにくくなってしまっていたので修正しました。

Answer 2

お返事いただき有難うございます。

また とても丁寧に教えていただき感謝しかありません。

---

さて・・教えていただいた冒頭に

＞A2セルの5を指定したとして、次のセルA3セル8の個数と割合ということになります。

と書かれていますが、『 A3セル8の個数と割合 』ということであれば、A2セルや A2セルの値と何ら関係ない話になってしまいます・・・。

と、ありましたが・・わたしが説明を端折りすぎたみたいで、申し訳ないのですが、

補足として以下を書き足します。

1. A列には1から50までの数字が数千個ランダムに配置されている
2. 選んだ特定数を仮に1としてA列に数字1が幾つあるのかを知りたい
3. A列に存在する数字1の下のセル内の数字をXとしてXは幾つあるのかを知りたい
4. 最後にそのXの出現率、割合を知りたい

特定数の数は自分でも探せるのですが、複数存在する特定数の下のセルの数字を調べる関数や構文がが分からず困っていました。

お教えいただければ幸いです。ペコリ

Answer 3

お返事いただき有難うございました。

Answer 4

こんにちは。

＞A2セルの5を指定したとして、次のセルA3セル8の個数と割合ということになります。

と書かれていますが、『 A3セル8の個数と割合 』ということであれば、A2セルや A2セルの値と何ら関係ない話になってしまいます・・・。

つまり、A2セルの下は A3セルに決まっていますので、下記の数式１のように A2セルを絡めなくても数式を作ることが出来てしまいます。（一例です。）

・数式１

=COUNTIF(A:A,A3)/(COUNTA(A:A)-1)

この数式の意味合いは、A列の中で A3セルと同じ値（ A3 = 8 ）が入っているセルの個数を求め、その値を、A列全体で何らかの値が入っているセルの個数から 1（見出し行の個数）を引いた個数（数値の個数）で除算した結果（数値全体に対する割合）を求めています。

※提示されておられる表に A3セルの個数を表示する箇所が無いので、割合だけを求めています。

あとは、この数式を、例えば B2セル（ B3セルに入れても結果は同様）に入れ、必要なだけ下方向にコピーすれば、図１のような結果を得ることが出来ます。

・図１：B2セルに入れた数式１を B9セルまでコピーしています。

ご覧いただければお分かりいただけると思いますが、

• B2セルの結果は、A3セル（ A2セルの下のセル）に入っている数値（ 8 ）の個数が、A列の数値全体の個数（この場合は 8 個）の中でどのくらいの割合になるかを表示。
• B3セルの結果は、A4セル（ A3セルの下のセル）に入っている数値（ 13 ）の個数が、A列の数値全体の個数（この場合は 8 個）の中でどのくらいの割合になるかを表示。
• B4セルの結果は、A5セル（ A4セルの下のセル）に入っている数値（ 5 ）の個数が、A列の数値全体の個数（この場合は 8 個）の中でどのくらいの割合になるかを表示。
  ※以下、同様となります。
  ※B9セルの結果が 0 なのは、A10セルに何も値が入っていないからです。

こういった結果で良いのでしょうか？。

あるいは、A2セルもしくは A2セルの数値を絡めた結果や全く異なる結果を求めておられるのでしょうか？。（その場合は、どのセルにどんな結果を求めておられるのかを具体例でお知らせください。）

返信をお待ちしています。

尚、上記の数式１は、

• A列には、 ""（数式の結果として表示される空白の文字列）は存在しない。
• A列には、見出しの文字列（ A1セルの値）を除き、数値のみが存在し文字列は含まれていない。

といった前提条件があるものとして組んでいます。ご注意ください。

Winows11 と Excel2021 の組み合わせで動作確認しています。

Answer 5

こんにちは高崎隆!

Excelには多くの関数がありますが、セルA3、A4を取得するために使用された関数の種類はわかりません...

ただし、すべてのセルに入力されたデータだけである可能性があります。

ただし、セルA3をクリックすると、数式バーのセルで使用されている数式が表示されます。

たとえば、下の画像を確認してください。

セル A3 を選択すると、使用された数式が数式バーに表示されました。

敬具

シャキル

この回答は自動翻訳されています。文法や表現の誤りが発生した場合はご容赦ください。