エクセル「数字の出現個数の計算式について」

質問

お世話になります。
あるセル（A1）に（112345）という６桁の数値が入っています。この数値を構成する数字は５種類（1,2,3,4,5)になります。
この５と言う数字をB1セルに出す計算式は「=SUM((FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890)<=LEN(A1))*1)」だとあるサイトに出ていましたが、私にはこの計算式の意味がどうにも分かりませんでした。
どのようにしてこの計算式がなりたっているのかご教示いただければと思います。

回答No.1

n_na_tto · ベストアンサー

●少し単純な式で配列数式に慣れましょう。
＿＿＿_A＿B_
1＿10183＿4_

B1
=SUM(ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},A1))*1)
のほうが簡単です。

1.数式バーのFIND({1,2,3,4,5,6,7,8,9,0},A1)の部分だけを
マウスで選択して、F9キーを押してください。

FIND({1,2,3,4,5,6,7,8,9,0},A1)
↓
{1,#VALUE!,5,#VALUE!,#VALUE!,#VALUE!,#VALUE!,4,#VALUE!,2}

となりますね。ESCキーで戻してください。
FIND(1,A1)→1はA1の中の何番目？(答) 1(1番目)
FIND(2,A1)→2はA1の中の何番目？(答) #VALUE!(無し)
以下同様に

□↓
{1,2,3,4,5,6,7,8,9,0}
FIND(□,A1)↓
{1番目,無,5番目,無,無,無,無,4番目,無,2番目}
この10回の演算を、背後でExcelが行っているんです。
配列を作る{　}は便利ですね。

2.
1番目だろうが5番目だろうが、関係ありません。
数値であれば、それを1種類と数えればいいんです。
そこで、ISNUMBER関数の出番。
ISNUMBER(FIND(1,A1))→1は数値？(答) TRUE
ISNUMBER(FIND(2,A1))→#VALUE!は数値？(答) FALSE

以下同様に

FIND(□,A1)↓
{1番目,無,5番目,無,無,無,無,4番目,無,2番目}
ISNUMBER(FIND(□,A1))↓
{TRUE,FALSE,TRUE,FALSE,FALSE,FALSE,FALSE,TRUE,FALSE,TRUE}

3.
TRUE(Excel関数では1)*1=1
FALSE(Excel関数では0)*1=0を使って
0,1の数値に変換

ISNUMBER(FIND(□,A1))↓
{TRUE,FALSE,TRUE,FALSE,FALSE,FALSE,FALSE,TRUE,FALSE,TRUE}
ISNUMBER(FIND(□,A1))*1↓
{1,0,1,0,0,0,0,1,0,1}

4.
あとはSUM関数で合計すれば種類の数
SUM(ISNUMBER(FIND(□,A1))*1)
=1+0+1+0+0+0+0+1+0+1
=4(種類)

●続いて、今回の配列数式。
＿＿＿_A＿B_
1＿10183＿4_

B1
=SUM((FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890)<=LEN(A1))*1)

1.数式バーのA1&1234567890の部分だけを
マウスで選択して、F9キーを押してください。

A1&1234567890
↓
"101831234567890"
となりますね。ESCキーで戻してください。
後ろにつけた1234567890は一見無駄に見えますが、
#VALUE!のエラーを出さないための工夫です。
3.で役に立ちます。

2.
□↓
{1,2,3,4,5,6,7,8,9,0}
FIND(□,"101831234567890")↓
{1番目,7番目,5番目,9番目,10,11,12,4,14,2}

3.
1番目～5番目→10183(5文字)の範囲内で見つかったから、合格
6番目,7番目,...→10183(5文字)の範囲外でしか見つらなかったから、不合格
LEN(A1)=5文字も使って、
1番目～5番目→LEN(A1)以下だから、TRUE
6番目,7番目,...→LEN(A1)より大きいから、FALSE

FIND(□,A1&1234567890)↓
{1番目,7番目,5番目,9番目,10,11,12,4,14,2}
FIND(□,A1&1234567890)<=LEN(A1)↓
{TRUE,FALSE,TRUE,FALSE,FALSE,FALSE,FALSE,TRUE,FALSE,TRUE}

4.
TRUE(Excel関数では1)*1=1
FALSE(Excel関数では0)*1=0を使って
0,1の数値に変換

FIND(□,A1&1234567890)<=LEN(A1)↓
{TRUE,FALSE,TRUE,FALSE,FALSE,FALSE,FALSE,TRUE,FALSE,TRUE}
(FIND(□,A1&1234567890)<=LEN(A1))*1↓
{1,0,1,0,0,0,0,1,0,1}

5.
あとはSUM関数で合計すれば種類の数
SUM((FIND(□,A1&1234567890)<=LEN(A1))*1)
=1+0+1+0+0+0+0+1+0+1
=4(種類)

お礼

お礼が遅くなり申し訳ありませんでした。
ご丁寧な解説にお礼申し上げます。また、おまけの回答までいただきありがとうございました。
配列は説明を聞けば「なる程！！。」なんですが・・・。
これを機会になじんでみたいと思います。

回答No.4

imogasi

もう回答は出ていると思うが、文章で説明するのもわかりやすいかと思って回答を追加してみる。
｛１，２、・・｝は配列で、すべての数字１０種類について、それをFind関数の第１引数として、見つかるかどうかを１０回やってみるということ。関数の学習でこの配列を使える場所は限られていて、これを使いこなすのは、上級の上だと思う。普通ではVLOOKUP関数でしかお目にかからないが。
配列数式という考えが別にあり、それと一脈通じるところがあり、最初に考えた人は、その辺のアナロジーからかもしれない（推測）。
ーー
ただしFind関数は、普通は見つかった桁位置（何文字目）を問題にするが、この課題の場合は、数字列にどこでもよいから有るかどうかだけ問題にすればよい。
ーー
もうひとつ&1234567890の部分は、もし数字１０種のうち、１０種のうちのある数字が元の数字列にない場合はFIND関数はエラーになり、そこで止まってしまう。それを全数字を後部にくっつけて、FIND関数を掛けると、どの数字でも見つかるように、無理に仕組む。
ーー
ただしそれでは課題とは違った答えになるが、本来の数字列の長さ（桁数）より大きい場所で見つかったものを除くと、課題に合うようになる。
SUMは条件に合った数字（文字列桁数内に見つかった数字）があれば、その都度１を足してカウントしていることになる。
ーー
過去にどなたか関数のエキスパートがこれを考え出し、世に広まったものと思う。
　普通ではここまで思いつくのは無理と思うので、せめて理解して、覚えるしかないと思う。

お礼

お礼が遅くなり申し訳ありませんでした。
ご丁寧な解説にお礼申し上げます。
分かりやすい回答が出ていますので申し訳ありませんが今回はポイントを付けることができませんでした　m(_ _)m。
他の方に投稿されている「imogasi」様の回答を何度か参考とさせていただいたことがあります。これからも分かりやすい回答をお願いいたします。

回答No.3

n_na_tto

質問とは関係ありませんが、
今回の目的で比較的簡単な式は

B1
=COUNT(FIND({1,2,3,4,5,6,7,8,9,0},A1))
でしょうね。

SUM((FIND(□,A1&1234567890)<=LEN(A1))*1)
も応用範囲の広い考え方ですが..

回答No.2

noname#204879

以下に「この計算式の意味」を“簡潔に”解いたものです。

　　　A　　　　　　B　　　　　　C　　D　　　　E
1　 112345　1123451234567890
2
3　 検索値　　　検索位置　　　 <=6　 *1
4　　　1　　　　　 1　　　　　TRUE　　1
5　　　2　　　　　 3　　　　　TRUE　　1
6　　　3　　　　　 4　　　　　TRUE　　1
7　　　4　　　　　 5　　　　　TRUE　　1
8　　　5　　　　　 6　　　　　TRUE　　1
9　　　6　　　　　12　　　　　FALSE　 0
10　　 7　　　　　13　　　　　FALSE　 0
11　　 8　　　　　14　　　　　FALSE　 0
12　　 9　　　　　15　　　　　FALSE　 0
13　　 0　　　　　16　　　　　FALSE　 0
14　　　　　　　　　　　　　　　　　　5

B1:　=A1&1234567890
B4:　=FIND(A4,B$1)
C4:　=B4<=LEN(A$1)
D4:　=C4*1
D14: =SUM(D4:D13)

ちなみに、D4 の式は =C4+0、=C4-0、=C4/1 でもＯＫです。

お礼

お礼が遅くなり申し訳ありませんでした。
ご丁寧な解説にお礼申し上げます。
解説を参考に自分ながらの答えを見つけ出したいと思います。