Excel配列数式 行毎、列毎、の集計

質問

恥を忍んで質問します

２次元の配列数式結果を発生させる式を得た時
此を　行毎、列毎、
で、集計して、１次元に集約するには
どうすればよかったでしょうか？

昔はやっていたのですが
過去歴にもうなく
やり方を忘れてしまい
踠いています

どうぞお助けください

回答No.10

bunjii

回答No.9の一部に勘違いがありましたので訂正します。
FIND("100",{"FGH/55R16100","FGH/55R1650","FGH/55R1610"})
　　　↓
FIND({"100","50","10"},{"FGH 100/55R16100";"FGH 100/55R1650";"FGH 100/55R1610"})

元データが1列×4行で検査値が3列×1行のうち元データの2行目についての展開なので次のようになります。
補足での元データについて展開後の文字列には" 100"が漏れています。
セミコロン（;）をカンマ（,）に訂正します。
FIND("100",{"FGH/55R16100","FGH/55R1650","FGH/55R1610"})
　　　↓
FIND({"100","50","10"},{"FGH 100/55R16100","FGH 100/55R1650","FGH 100/55R1610"})

お礼

結果として本筋にそうご回答を頂けず、残念に思います

補足

謝罪します
私の方が間違えています
済みませんでした

回答No.9

bunjii

誤解があるようなので追加説明させて頂きます。

>＞OR(INDEX((FIND($AB$3:$AD$3,$Y$4:$Y$7&AB$3:AD$3)<LEN($Y$4:$Y$7)),ROWS($Y$4:$Y4),0))
>全く文法的にあり得ない式です
ご指摘の数式はExcel 2013で検証していますので数式の構成に誤りはありません。
OR関数の引数であるINDEX関数の配列が2次元配列であることを貼付画像で証明します。
但し、FIND関数でTRUEとFALSEの配列を1と0に置き換えてすべての配列を目視できるように1を乗じています。

>FIND($AB$3,$Y$4&AB$3:AD$3)
>は
>FIND("100","FGH 100/55R16"&{"100","50","10"})
>から
>FIND("100",{"FGH/55R16100","FGH/55R1650","FGH/55R1610"})
>と、変換されます
誤りがあるようです。
FIND("100",{"FGH/55R16100","FGH/55R1650","FGH/55R1610"})
　　　↓
FIND({"100","50","10"},{"FGH 100/55R16100";"FGH 100/55R1650";"FGH 100/55R1610"})
FIND("100","FGH 100/55R16100") → 5
FIND("50","FGH 100/55R1650") → 14
FIND("10","FGH 100/55R1610") → 5

従って、次の指摘である結果は
{10,err,err} → {5,14,5}

>此処で質問です
>エクセルは両方同時には返せません
行と列の扱いを正しく指定すればINDEX関数の配列を2次元で扱えますが格納するセルの値は1つに絞らなければなりません。
行と列の両方または何れか一方の指定を省くと左上の値が代表的に表示されます。

回答No.8

bunjii

>結果が
>{TRUE;TRUE;TRUE;TRUE}
>に、なりますか？
考え方によります。
AF4=OR(INDEX((FIND(AB$3:AD$3,Y4&AB$3:AD$3)<LEN(Y4)),0))
とすれば、AF4を下へAF7までオートフィルコピーしてください。
AF4:AF7　→　{TRUE;TRUE;TRUE;TRUE}
になります。
敢えて次のような数式を次のような数式を求めているのでしょうか？
AF4=OR(INDEX((FIND($AB$3:$AD$3,$Y$4:$Y$7&AB$3:AD$3)<LEN($Y$4:$Y$7)),ROWS($Y$4:$Y4),0))
AF4を下へAF7までコピーすると各セルは回答No.7で提示の数式を下へコピーしてものと等価になります。
∴数式を簡素化した回答No.7で提示した数式が妥当と考えます。

>Find({"50"},{"FGH 100/55R1610"})
>の検証でエラーが出ます
当然です。
"FGH 100/55R1610"の中に"50"が存在しないためのエラーです。
私が提示した数式は次のようになります。
Find({"50"},{"FGH 100/55R1610"}&{"50"})<LEN({"FGH 100/55R1610"}) → {FALSE}

補足

此の質問は
１セルの１式内で
多次元形式の配列数式結果を
幾次元か集約する
此の話です

先にも申した通り
配列数式の途中の式です

此に更に演算を
同一セル内で行う訳です

配列数式の結果が他項に及ぶ時
其を他セルから参照する
此は叶わないもの
と、思います

故に
他のセルと合わせたら
は
無しです

1セルに書かれた一つの式だけで
結果を得てください

幾多のセルを使うなら
私にも既に出来ます

また
二次元や三次元形式
または其以上の演算の結果について
ご説明させて頂いて尚
ご理解が及ばない場合

残念ですが
幾ら話をお伺いしても
ご回答を頂くまでには至らない
そう、思えます

多次元のデータ構造への理解、素養、
または理解する事への許容
此等が、あって初めて話せる
そんな内容です

せめて
データ区切りの,;位の違い
式の展開順にどういう変遷がなされるか

其等位はご理解頂けないと
無理です
お話し合いに成りません

＞OR(INDEX((FIND($AB$3:$AD$3,$Y$4:$Y$7&AB$3:AD$3)<LEN($Y$4:$Y$7)),ROWS($Y$4:$Y4),0))
全く文法的にあり得ない式です

演算からの戻り値における
位置的配慮を頂きたい
と、申した筈です

最後に一度だけ
説明させて頂きます

説明のための例として
FIND($AB$3:$AD$3,$Y$4&AB$3:AD$3)
を、使用します

此の説明では
２パターンに
仮に簡略化し
分けて説明します

一方は
FIND($AB$3,$Y$4&AB$3:AD$3)
他方は
FIND($AB$3:$AD$3,$Y$4&AB$3)
です

まずは
FIND($AB$3,$Y$4&AB$3:AD$3)
から

エクセルでは
随時順をおって
参照の値を読み込み
定数に置き換えて
いきます

ので
FIND($AB$3,$Y$4&AB$3:AD$3)
は
FIND("100","FGH 100/55R16"&{"100","50","10"})
から
FIND("100",{"FGH/55R16100","FGH/55R1650","FGH/55R1610"})
と、変換されます

此の戻り値は
{10,err,err}
（※注:errとは何らかのエラー値を示す）
ですね

恐らくは
エラー回避のために
&AB$3:AD$3
と、されたのでしょうが
全く無意味
と、解ります

次に
FIND($AB$3:$AD$3,$Y$4&AB$3)
です

此も
FIND({"100","50","10"}."FGH/55R16100")
と、展開されます

戻り値は
{10,err,10}
で、しょうね

今までで
２式についての
戻り値が解ったのですが

{10,err,err}
{10,err,10}
ですね

其々は
引数の第一項について
抽出した式
です

ので
戻り値の項
此における位置関係は
各々其々同じです

また、
データ区切りも同じで
行並びの横向き
です

此処で質問です
エクセルは両方同時には返せません

で、
どちらをどう返せば
いいのでしょうか？

決めかねますよね？

詰まり
こんな事をしては
いけない
の、です

極一部を抜き出して尚の
此の重複

全体ではどんな事をしたか
もうお解り
ですね？

もし
そんな事、知ったこっちゃね～
と、申されるなら
構いません

其は其で
其の判断を
私が何とも出来ませんので
致し方ない
かと、思います

仕方ないので
配列数式の初歩の初歩
一次元の世界にとじ込もって
出て来ないで構いません

否、むしろ
ハイクラス者の話に
入らないで頂きたい

もし、多次元も扱えたい
と、申して頂けたなら
先に私が挙げた
トランスポンドを適応した式
此がどういうデータに還元されるか

エクセルの式のトレース機能
等を用い
過程を含め
確認頂きたい

確認すれば
すぐ解る事です
から

如何でしょうか？
ご理解頂ければ幸いです

にしても
志は買い、感謝します

回答No.7

bunjii

質問の内容が二転三転して、あなたの思考を読み取るのに時間がかかります。
前回に提示した数式を再検討し、あなたの目的に合うと思われる数式に直してみました。

=OR(INDEX((FIND(AB$3:AD$3,Y4&AB$3:AD$3)<LEN(Y4)),0))

この数式は行毎に検索文字列の有無を抽出して、その結果をOR演算しています。
INDEX関数の行番号と列番号を0または無指定にすると配列値を返すので、それをOR演算すればあなたの希望に合うと思います。

補足

済みません
此って

結果が
{TRUE;TRUE;TRUE;TRUE}
に、なりますか？

{TRUE}
に、なりませんか？

どうも根本的な部分を
ご理解頂けていない
其のように思えて
ならないのです

あと、
お示し頂いた式では
Findの
第一引数参照範囲が行並びで横
第二引数参照範囲も行並びで横
そうすると
第一引数の
１項目の２番目の結果と
２項目の１番目の結果

具体的に言うと
Y4&AC$3に対し、AB$3を検査結果
Y4&AB$3に対し、AC$3を検査結果
此れが位置的に重複します

また、
此れだけでなく以降幾つかが重複します

FIND(AB$3,Y4&AB$3:AD$3)　→　{7,7,7}
FIND(AC$3,Y4&AB$3:AD$3)　→　{11,11,11}
FIND(AD$3,Y4&AB$3:AD$3)　→　{14,14,14)

と、なるでしょうが
２番目の７と１番目の１１
３番目の７と２番目の１１と１番目の１４
３番目の１１と２番目の１４
此等のリターン値での位置が、
同一の場所になります

ので
Excelが重複した結果を
どう扱っていいか
何れを返していいのか
解らなくなります

つまりは
そんな事出来るかい！！
エラーを返す訳です

ので
こういう時
つまりFind等で
多数のキーを
複数の対象に対して
検索する時には
TRANSPOSE等で
第一引数の向きと
第二引数の向きを
変える事
此を、お勧めします

=OR(INDEX((FIND(AB$3:AD$3,TRANSPOSE(Y4&AB$3:AD$3))<LEN(Y4)),0))

でも、此れでも
{TRUE;TRUE;TRUE;TRUE}
には、ならないでしょう
ね

今回の此のご回答に対するコメントとして最後に
この式は厳密に言うと
意図されたものが
達成されていませんよ

Find({"100","50","10"},{"FGH 100/55R16100","FGH 100/55R1650","FGH 100/55R1610"})
は
Find({"100"},{"FGH 100/55R16100","FGH 100/55R1650","FGH 100/55R1610"})
Find({"50"},{"FGH 100/55R16100","FGH 100/55R1650","FGH 100/55R1610"})
Find({"10"},{"FGH 100/55R16100","FGH 100/55R1650","FGH 100/55R1610"})
此等を順に検証する訳ですが

この内の
Find({"50"},{"FGH 100/55R16100","FGH 100/55R1650","FGH 100/55R1610"})
を検証する際
Find({"50"},{"FGH 100/55R16100"})
Find({"50"},{"FGH 100/55R1610"})
の検証で
エラーが出ます

エラーは不要な情報
ではなく
”キーが含まれない”
と、いう
「重要な情報」です
よね？

そういう意味において
式
間違えていますよ

回答No.6

bunjii

>先のＵＲＬのファイルに変更を与え
>更新致しました
今回の処理はY列の文字列にAB$3またはAC$3またはAD$3の文字列の何れかが含まれているときAF列にTRUEを返すようになっていますね。
しかし、Y4の文字列が"ABCDE 105/55R15"のときでもAF4にTLUEが返ります。それでもよろしいですか？
FIND関数で#VALUEエラーを起こさない方法は検索対象の文字列の後尾へ検索文字列を連結し、検出位置が検索対象文字列の長さより大きいときFALSEを返すようにします。
従って、次の数式を使えば作業用のセルを設ける必要がありません。
=OR(FIND(AB$3,Y4&AB$3)<LEN(Y4),FIND(AC$3,Y4&AC$3)<LEN(Y4),FIND(AD$3,Y4&AD$3)<LEN(Y4))

>様々なケースにおいて適応する
>基本的技術を求めております
関数の使い方は応用力で数式を組み替えるものです。
処理対象のデータから結果を導くための論理は一様ではありません。
従って、模擬データと結果の提示が無ければ的確な数式を提示できません。

補足

出来れば行列演算のようなものが欲しいのです
駄目でしょうか？

行列的に言うと
　　　　　　　　1
(1　2　3)　=　( 5 )　or　(False True True)
　　　　　　　　3
(書き方とか間違えていたら済みません）

　　1　　2　 3
1 True False False
5False False False
3False False True
　　　Or
False True True
　　　=
False False True

こういう風なもので
列毎に個別に書かれたもの
ではなく

一般化した式で書かれた配列数式
此を求めます。

今回は
簡単な要素数の少ない
只の　"例"

なので、本当は
or(({1,2,3}={1;5;3}),{False,False,True})
で、行ける
と、思うのです

が、
あくまで此は例
本来、求めるものは

先に示したような
Find文で得られるような
二次元以上のデータに対して行う演算
なのです

で、
次の式は
間違えた式だ
と、思うのです

が
イメージ的には
or(Index({1,2,3}={1;5;3},
0,column(A1:C1)),{False,False,True})

こんな感じです

宜しくお願いします

回答No.5

bunjii

言葉のキャッチボールがうまくできていないようなので勝手解釈の回答になります。

>お示し頂いた式は
>A1:A10000
>の記載が抜けていませんでしょうか？
あなたが提示したシートをそのまま利用していますので回答No.4の貼付画像の通り1行目と2行目はデータが無く、然も、あなたの数式も対象範囲が$A$3:$A$10000になっています。
また、あなたが2次元データとしたAD4:AF7を対象に1次元データに加工したものがAA4:AA7です。

>其とも此のまま
>予備セルを使わず
>A1:A10000
>其のデータを
>扱う、
>適応する、
>其には
>どうすれば
>良いでしょう？
A列の文字列に特定の3つの文字列が含まれるものについて、その数をカウントするのであれば次のようになります。（作業用のセルは不要）
1行目は項目とします。
A列が元データとし、C列にチェック文字列1、D列にチェック文字列2、E列にチェック文字列3が設定されているときF列にA列の文字列にC列、D列、E列の文字列が全て含まれるデータ数をカウントします。
F2=IF(COUNT(C2:E2)=3,COUNTIF(A:A,"*"&C2&"*"&D2&"*"&E2&"*"),"")
F2セルを下へ必要数コピーすれば今回の貼付画像のようになります。

お礼

先のＵＲＬのファイルに変更を与え
更新致しました

お参照頂ければ幸いです
宜しくお願い致します

補足

再三のお越し、有難うございます
集約の趣旨は伝わった
と、思います

拙い文章にお付き合い頂き
併せて、お礼申します

さて、
お示しになられた操作では
フィルを前提として
行毎に適応を与え結果を得ているもの
と、思います

が
此れでは
此の結果を
次段の演算に一括利用する
其れが叶わない
と、思います

方向性としては、こうなのです

が、
此を１つの配列数式内のみで行い
次段の演算データに活用したい
のです

作業セルを使えば
失礼ながら、私でもできる
のです

又、先達て申した内容
でも、ありますが、

個々のケースに付いての
対症療法
此を求めている
のでは、ありません

様々なケースにおいて適応する
基本的技術を求めております

此の方向性で、ご回答の程を
宜しくお願い致します

回答No.4

bunjii

補足して頂いた情報も抽象的な説明になっていますので本来の目的が見えてきません。
検索キーとされる$AB$3:$AD$3の3個の値が$A3:$A$10000の文字列に含まれている位置を検出していることは分かります。
A3セルには"ABCDE 100/50R10"と言う文字列がありますので、AB3の"100"は先頭から7番目に有り、AC3の"50"は先頭から11番目に有ります。しかし、AD3の"10"は先頭から7番目と14番目の2ヶ所に存在し、あなたの数式では7番目を返したいます。
あなたの目的ではA3の文字列の14番目に有る"10"の位置が返らなければならないのではないでしょうか？

「1次元に集約する」と言う処理内容も説明されていませんので具体的な提言ができません。
例えばA3の文字列にはAB3とAC3とAD3の文字列が含まれるので結果を1とします。
A4の文字列にはAB3とAD3の文字列は含まれるがAC3の文字列はA4の文字列に含まれないので結果を0とします。
同様にA5の文字列にはA3の文字列と同様なので結果を1とします。
上記のような処理であれば次の数式で1次元へ集約できるでしょう。
=IF(COUNT(AD4:AF4)>2,1,0)

お礼

済みません
No.6の補足において
若干の間違いがありました
お詫びします

現在、説明のための資料
此を作成しています

暫しお待ちください

補足

足を運び頂き
有り難うございます

此の式は一例に過ぎません
他の式、他の演算、
此の全てにおいて
都度、聞く事
其は出来ないでしょう

また、
お示し頂いた式は
A1:A10000
の記載が抜けていませんでしょうか？
もしそうした場合
行毎、列毎、
此の結果が得られますか？

其とも此のまま
予備セルを使わず
A1:A10000
其のデータを
扱う、
適応する、
其には
どうすれば
良いでしょう？

アドレスを指定せず
代替え処置も見受けられない式でも
データを読み込める
のでしょうか？

回答No.3

bunjii

ご提示のExcelシートのAD4に設定された数式が何の目的か理解できません。
数式を解読すると$A$3～$A$10000の文字列にAB$3が含まれていればその位置をAD4に返すようになっているようです。
AE4は同様にAC$3の値が含まれる位置が返っているようです。
しかし、AF4ではAD$3の値で検索しても桁数が少ないためAB$3の検査結果と同じになることもあり、正しい処理ではないと思われます。
AD4～AF7の結果から判断すると配列数式の必要が無いと考えられます。

質問の趣旨がA列の中からAC$3とAD$3とAE$3が同時に含まれるセルの数を計数したいのであれば次の数式で良いと思います。
=COUNTIF($A:$A,"*"&AB3&"*"&AC3&"*"&AD3&"*")

補足

お気に止めて頂き
有り難うございます

此の式ですが
横方向に羅列された
複数の検索キーの
其の各々に対し

縦に羅列された
データ範囲の
此の各々で
其々試験し
結果を返す式です

ポイントは
縦と横が
意識して意図して
使い分けられている
此の点です

此により
演算結果において
一次元域での重複を回避でき
二次元域、マトリクスに配置される

ので、
重複する事なく得られます

幾つかの関数で利用できる
何ら問題の無い
エクセル標準の
大昔からある仕様です

search文等
配列数式非対応（？）なものを
埋める為、

また、更なる高機能を
得る為、

使用していました

回答No.2

msMike

近年になって配列数式を使い始め、特に困ったことはないのですが、専門語の多くは知りません。
そこで「恥を忍んで」確認したいのですが、「２次元の配列数式結果」の意味は理解できるとしても、「１次元に集約する」とはどういうことでしょうか？教えてください。

補足

お気に止めて頂き
有り難うございます

補足させて頂きます

まずは
各々の説明から

二次元配列
二方向の幅をもつ配列
自由度が２ある配列

エクセルの場合の例

A1:D5といった
行、列、共に幅のある
セル範囲

Sheet1!A1:Sheet5!A5
と、言った串刺しのセル範囲

ブックを跨いだ集計で
より下位の一次元の要素
此を指定した場合

其れ等をデータに指定した時等は
此に当たる

定数配列で言えば
,や;等
と、言った
区切り文字
此の種類が、
二種類あり、
かつ、二種類を越えていない場合

一次元配列
単一の幅しか持たない配列
自由度が１のみの配列

エクセルの場合の例

ブックを跨いだ集計で
１ブック辺り１セルしか参照しないもの

１シート辺り単一セルしか参照しない串刺し範囲

一行多数列
または
多数行一列
此の範囲

其等の参照

定数配列で言えば
,や;等
と、言った区切り文字
此の種類が
一種に限定されたもの

二次元配列を一次元に集約する
とは
二次元配列の幅の一方を
集計、平均、等で
集約し
幅を捨てる事
自由度を一つ減らし、１にする事

です。

此の拙い説明で
ご理解頂けました
なら、幸いです

此ができた場合
一つのFind文で
多数の検索キーを
一次元の範囲に適応し調べ
結果から解を得る
と、言った展開が可能になります

現に過去においてはしていたのです

が
忘れてしまい
私の中での
ロストテクノロジー化しました

お知恵をお借りしたく思います
宜しくお願い致します

回答No.1

aokii

ピボットテーブルがいいかも。

お礼

有難うございます

ですが済みません
今回は配列数式の演算過程のデータを得るお話なので
ピボットは使えない
と、思います

くたん無きご意見、有難うございました