エクセル 関数が動かない

質問

ある1000行から成るロウデータから、Large関数とCountifs関数の掛け合わせで最小値を得て、Match関数で、その値を持つレコードからIDを抽出しています。
しかし、自動計算が186行目までしか働かず困っています。(計算式自体は問題ありません) 設定の問題ではないかと推測しているのですが... どのように確認すれば良いか(対処すればよいか)わかる方がいらっしゃいましたらご教示いただけますと助かります。

ちなみにOptionの設定は下記の通りです。

回答No.9

Nouble · ベストアンサー

確定的
では、ありませんが
なんとなく、解りました。

データに、式が
フィットして、いません。

先ず、
当方の、環境の
2011では
エラーに、なったため

INDEXの、引数を
,0)から、,,)に
変えました

此は、公式仕様では
,0,0)でも、良い
と、なっていますし
構わないはず
とは、思いますが
厳密には
動作が、違います
,,)
を
使って、頂きたい

少なくとも
,0)では
動かない、EXCELが
出ます。

さて、本題ですが
式を、変え
データを、作成した
初段では
全件、動作しました。

其の後、
式を
解析する、内に
データの、粗密さが
ある程度、荒くないと
意味が、無い事に
気付き
荒くした、途端
1件も、動きませんでした

#num!ですが
要するに
anマッチ、しています。

此を、ご参照ください
https://1drv.ms/x/s!AjviygfJDgV_1QNBJwwpfRyp4Jj6

未確定、結論
と、しては
式の、設計が
不適切
と、いう事
です。

エラー処理、等
何らかの、考慮を
入れましょう

回答No.12

Nouble

追記
其の後も、直向きに
考えて、いた
の、ですが

己の、条件中
には
sheet2!D2:D1000=sheet!1Dx　(x|2≦x≦1000　xは任意の行番号)
の、成立が
含まれて、います

また
己を、満たす時、
己の、条件内に
甲が、含まれている
ので
甲は、必ず
成立して、います

故に、
己を、満たす行の　D列値=甲を、満たす行の　D列最小値
ならば、
己を、満たす行の　D列値=シート1　D列、同行値
なので

其れが、指し示す
値は、どんな時も
シート1の
D列同行の、値
です

もう1つ
甲を、満たす行
最小値と、同値行中の
C列値と、D列値が、
シート１、同行の
C列と、D列に、
等しい、なら

其れは、
条件が、成立しており
結果は
シート１の、同行D列値です。

回答No.11

Nouble

済みませんが
引っかかって、しょうが無い
ので
質問させて、ください

sheet2!$B:$B1000=B2…甲
と、
仮に、します

先ず
COUNTIFで
甲の、合致件数を
得て…乙

其れを、元に
LARGEで、甲成立行中の
C列の、最小値を
取ってます
よね？

仮に
丙と、します。

次に
丙に、合致する
件数を
得た、上で…丁

＝LARGE(INDEX(
(sheet2!B$2:B$1000=B2)*(sheet2!C$2:C$1000=C2)
*sheet2!D$2:D$1000,,),
COUNTIFS(sheet2!B$2:B$1000,B2,sheet2!C$2:C$1000,
丁)
と
されて、います。

COUNTIFS(sheet2!B$2:B$1000,B2,密データ!C$2:C$1000,
丙)
は
(sheet2!B$2:B$1000=B2)*(sheet2!C$2:C$1000=C2)
の
成立件数を
カウントできて、いません
故に、
最小値を、取っている
其のようには、なっていません
よね？

此は、何を
されて、いますか？

もし
最小値を、取りたい
なら
乙、または、C2、
に
統一、されるべき
ですよね？

詰まり
成立行中、D列最小値を
得たい、場合
其れ、以降の
式は
意味が、無く
間違え
または、
シート1のC2の、存在価値が
無い
と、いう事に
成りますが、

其の、可能性を
排除して
考えた、場合
他の、意図を
聞く、必要が
出ます。

所で、
過去の、ご質問を
思い起こすと

甲成立中の、C列最小値行中の
D列最小値行の、E列値が
欲しい
と、いう事
かな？
と、思えますが、

此は、言い換えれば
甲成立行中に、おいて
C列も、D列も、
最小値の、行

此って、
データが、完全に
どの列も、整列されている
なら
甲成立行中、最上行
ですよね？

なので
OFFSET(sheeet2!$A$1,LARGE(INDEX((甲)*ROW($B$2:$B$1000,,),乙),3,1,1)
ですよね？

抑も
データが、ソートされて
いたなら
何段も、LARGEを
使う、必要は
ありませんよ？

もう1つ
シート2B列と、シート1B列
同行値が、一致し、
シート2C列と、シート1C列
同行値が、一致し、
シート2D列と、シート1D列
同行値が、一致する、…戊
行中の
G列値と、
OFFSET(sheeet2!$A$1,LARGE(INDEX((甲)*ROW($B$2:$B$1000,,),乙),3,1,1)
が、一致する行
其の、行位置は

MATCHで、探させるより
成立行、行番号最小値を
取る方が
処理速度が、速くなる
と、思います

内部で、行われる
検査回数が
格段に、違います

あと、
私は、絶対に
COUNTIFは、使いません

人が、見る
見た目通りに
COUNTIFは
セル値を、扱わないから
です

COUNTIFの、処理内容と
目視から、期待する
処理結果、との
間には
差異が、出るから
です

セル内の、値の
扱い方が
見た目と、違うのです

SUMPRODUCTで、件数集計する事を
圧倒的に、強く
勧めます。

と、まあ
勝手に、話を
進めましたが

結局
何が、したいのか

甲成立最上行、Ｄ列値と
一致する
戊成立行の、D列値が
知りたい
のですか？

其れは、単に
データが、前列
完全整列されて、いる
前提なら
甲成立中の、D列最小値行の
中に
戊成立行が、あるか…己
見るだけ
で、解りますよ？

因みに
手元データでは
全件、不成立
でした。

己なんて
草々、成立
しませんから
ね

お礼

Largeを使っていたのは、運用上データがソートされていないケースを想定していたからでした。しかし、動作の安定性を考慮してoffsetを使う案も検討して見ます。sumproductが実用的なのは知っていました。しかし、データ量が増すごとに動作が不安定となり、現在の仕様に至りました。
本当にいろいろアドバイスをいただきありがとうございました。

回答No.10

bunjii

>Work sheet 1: 出力用
>Work sheet 2: ロウデータ
>計算式は下記の通りです。
------------ここから------------
=MATCH(LARGE(INDEX((Sheet2!b$2:b$1000=b2)*(Sheet2!c$2:c$1000=c2)*Sheet2!d$2:d$1000,0),COUNTIFS(Sheet2!b$2:b$1000,b2,Sheet2!c$2:c$1000,LARGE(INDEX((Sheet2!b$2:b$1000=b2)*Sheet2!c$2:c$1000,0),COUNTIF(Sheet2!b$2:b$1000,b2)))),INDEX((Sheet2!b$2:b$1000=b2)*(Sheet2!c$2:c$1000=c2)*(Sheet2!d$2:d$1000=d2)*Sheet2!d$2:d$1000,0),0)
------------ここまで------------
検索値=LARGE(INDEX((Sheet2!b$2:b$1000=b2)*(Sheet2!c$2:c$1000=c2)*Sheet2!d$2:d$1000,0),COUNTIFS(Sheet2!b$2:b$1000,b2,Sheet2!c$2:c$1000,LARGE(INDEX((Sheet2!b$2:b$1000=b2)*Sheet2!c$2:c$1000,0),COUNTIF(Sheet2!b$2:b$1000,b2))))
この数式ではSheet2のB列がSheet1のB2と同じで、且つ、Sheet2のC列がSheet1のC2と同じ行のSheet2のC列の値で最小値と言うことのようです。
検索範囲=INDEX((Sheet2!b$2:b$1000=b2)*(Sheet2!c$2:c$1000=c2)*(Sheet2!d$2:d$1000=d2)*Sheet2!d$2:d$1000,0)
この数式はSheet2のB列がSheet1のB2と同じで、且つ、Sheet2のC列がSheet1のC2と同じであり、更に、Sheet2のD列がSheet1のD2同じの行が検索対象になっています。
従って、検索値が検索範囲に存在しない可能性があります。
MATCH関数の検索値と検索範囲は整合性を取らなければ目的の結果を得られません。
当方でExcel 2013による検証で提示の数式を使うとMATCH関数で検索値が存在しないときの#N/Aが出ます。
今一度、目的と結果の整合性を確認された方が良いでしょう。
提示の数式の範囲を減らしました。
=MATCH(LARGE(INDEX((Sheet2!B$2:B$20=B2)*(Sheet2!C$2:C$20=C2)*Sheet2!D$2:D$20,0),COUNTIFS(Sheet2!B$2:B$20,B2,Sheet2!C$2:C$20,LARGE(INDEX((Sheet2!B$2:B$20=B2)*Sheet2!C$2:C$20,0),COUNTIF(Sheet2!B$2:B$20,B2)))),INDEX((Sheet2!B$2:B$20=B2)*(Sheet2!C$2:C$20=C2)*(Sheet2!D$2:D$20=D2)*Sheet2!D$2:D$20,0),0)
添削1
=MATCH(LARGE(INDEX((Sheet2!B$2:B$20=B2)*(Sheet2!C$2:C$20=C2)*Sheet2!D$2:D$20,0),COUNTIFS(Sheet2!B$2:B$20,B2,Sheet2!C$2:C$20,LARGE(INDEX((Sheet2!B$2:B$20=B2)*Sheet2!C$2:C$20,0),COUNTIF(Sheet2!B$2:B$20,B2)))),INDEX((Sheet2!B$2:B$20=B2)*(Sheet2!C$2:C$20=C2)*Sheet2!D$2:D$20,0),0)
添削2
=MATCH(LARGE(INDEX((Sheet2!B$2:B$20=B2)*(Sheet2!C$2:C$20=C2)*(Sheet2!D$2:D$20=D2)*Sheet2!D$2:D$20,0),COUNTIFS(Sheet2!B$2:B$20,B2,Sheet2!C$2:C$20,LARGE(INDEX((Sheet2!B$2:B$20=B2)*Sheet2!C$2:C$20,0),COUNTIF(Sheet2!B$2:B$20,B2)))),INDEX((Sheet2!B$2:B$20=B2)*(Sheet2!C$2:C$20=C2)*(Sheet2!D$2:D$20=D2)*Sheet2!D$2:D$20,0),0)
添削1、添削2の何れを使うかはあなたの目的に合わせてください。

回答No.8

bunjii

>ロウデータの追加の仕方で、水平方向はダメで垂直方向はOKなんてあるのでしょうか？
意味不明です。
「ロウデータ」はテキストですよね？
行の区切りは改行で列の区切りはカンマやタブの他に空白も使えるようです。
任意の区切り記号（特定の文字でも可）も指定すれば貼り付け時にフィールド（列）を区切って入力されます。
新しいブックへカンマ区切りの行データ（文字列）を貼り付けると1つのセルへ長い文字列が入力されると思います。
貼り付け時に「貼り付けオプション」の「テキストファイルウィザードを使用」を使えば列の区切りを指定できます。

お礼

意味不明です。ということは、貼り付け方の違いが問題の解決に繋がったわけではないと認識します。計算式が複雑になった背景がありまして...ただ、現在は動作しておりますので、やはり計算式の問題とは言い難いです。
ただ、ネストを多用すると正常に動作しなくなるものと心得ます。アドバイスありがとうございました。

回答No.7

mt2015

186行目までしか動かない理由は解りませんが、式を見る限りでは失礼ですがExcelにあまり慣れてないであろうと推測しました。
質問に何か大事な情報が抜けていると思われます。

--
長い式で目的の値を取得できるとそれはそれで達成感もありますが、問題が解りにくくなりメンテナンス性も悪いです。
作業列等を使って、なるべく短い式にすることをお勧めします。
例えば今回の例では、「IDを抽出しています」とありますが、Sheet1のセルA2、B2、C2の値3つと全てが一致する行が、Sheet2！B2:D1000の何番目に出てくるかを取得したいのではないでしょうか？

その場合、一例ではありますがSheet2のE列を作業列として使用します。
E2セルに↓の式を入れ、E1000までコピーします。
=B2&"_"&C2&"_"&D2

Sheet1には以下の式を入れます
=IFERROR(MATCH(B2&"_"&C2&"_"&D2,Sheet2!E$2:E$1000,0),"")

回答No.6

tsubu-yuki

>> =IFERROR(MATCH(LA（略）000,0),0),"")
> 空白でした。

ということは、エクセルのシステムは
「ちゃんと最後の行まで関数式を評価（計算）している」
ということですね。
で、その評価（計算）の結果、#NUM!エラーを返している。
つまり「問題は式に有るのではないか？と疑うのが自然」ですね。

５番さんもおっしゃる通り、
「ロウデータ」を簡略したサンプルを提示いただけないと
式が煩雑過ぎて読めないんです。
なので「構成を～」と申したんです。

情報を小出しにして時間をかけるのは
質問者・回答者双方にとってストレスでしかないですよ。
身バレしない程度にボカして、サンプルと目的をご提示くださいませ。

と言いつつ、
私は「無駄に長い、煩雑な式」は不得手なので
　（他人に伝達・引継ぎ、メンテナンスを任せるのが困難ですしね）、
特段の意見を思いつかない限り静観の立場を決め込みます。
失礼しました。

回答No.5

bunjii

>自動計算が186行目までしか働かず困っています。
症状の説明が不明瞭です。
ハングアップ（フリーズ？）するのですか？
または、下記で言う出力用シート（Sheet1）の186行目までは正常に抽出できるが187行目以降が抽出できない（エラーになる？）と言うことですか？

>動かない=計算式されない の意です。
>セルの戻り値は#num! になっております。
Excelのバージョンは？
#N/Aではないですか？
MATCH関数で検索範囲に検索値が見つからないと解釈できます。
COUNTIFS関数やCOUNTIF関数では該当が無いとき0が戻ります。

>Work sheet 1: 出力用
>Work sheet 2: ロウデータ
>計算式は下記の通りです。
------------ここから------------
=MATCH(LARGE(INDEX((Sheet2!b$2:b$1000=b2)*(Sheet2!c$2:c$1000=c2)*Sheet2!d$2:d$1000,0),COUNTIFS(Sheet2!b$2:b$1000,b2,Sheet2!c$2:c$1000,LARGE(INDEX((Sheet2!b$2:b$1000=b2)*Sheet2!c$2:c$1000,0),COUNTIF(Sheet2!b$2:b$1000,b2)))),INDEX((Sheet2!b$2:b$1000=b2)*(Sheet2!c$2:c$1000=c2)*(Sheet2!d$2:d$1000=d2)*Sheet2!d$2:d$1000,0),0)
------------ここまで------------
難解な数式ですね。

>照合の形は0です。検索範囲は昇順です。
Sheet2のB列、C列、D列の全てが昇順と言うことですか？
「照合の形」とはMATCH関数の「照合の種類」のことですよね？
提示の数式が込み入っていて読み切れない状況です。
検索値と一致しない結果が起る可能性はありませんか？
検索の論理を見直して数式を簡略化することを再検討された方が良いでしょう。
次のCOUNTIFS関数でLARGE関数の第2引数（順位）を算出している部分を簡略化できるような気がします。
COUNTIFS(Sheet2!b$2:b$1000,b2,Sheet2!c$2:c$1000,LARGE(INDEX((Sheet2!b$2:b$1000=b2)*Sheet2!c$2:c$1000,0),COUNTIF(Sheet2!b$2:b$1000,b2)))
また、条件の論理に誤りはありませんか？
「その値を持つレコードからIDを抽出」とありますが、Sheet2のD列にそのIDが記載されているのでしょうか？
Sheet2のB2:D1000の数表をカンマ区切りのテキストデータで貼り付けて頂ければ検証して数式の添削を考えられます。

補足

ロウデータを一列ごとにコピーし直したら問題は解決しました。
大変お騒がせしました。

ただ、原因については特定できませんでしたので念のためご報告いたします。

・条件の論理には誤りはありませんでした。
・sheet2のC列にエラーメッセージが混在していました。

ロウデータの追加の仕方で、水平方向はダメで垂直方向はOKなんてあるのでしょうか？難しいですね。

回答No.4

tsubu-yuki

> セルの戻り値は#num!

試しに
　　=IFERROR(MATCH(LA（略）000,0),0),"")
としてみたら返り値はどうなりますか？

補足

空白でした。

回答No.3

Nouble

動かない、セルの
戻り値は
#REF、ですか？
其れとも…　？

補足

動かない=計算式されない の意です。
セルの戻り値は#num! になっております。

何卒よろしくお願いいたします。

回答No.2

tsubu-yuki

追記。
エクセルのオプション（数式）を
ご提示の図通り（と言っても、初期値ですね）に戻して
１０００行×３０列のサンプルデータを作成、
　　Ｂ列：=INT(RAND()*10000)　　　※乱数発生
　　Ｃ列：=RANK(B1,B:B)　　　　　 ※範囲内での順位を判断
　　Ｄ～ＡＤ列：=INT(RAND()*10000)　　　※乱数発生
　　Ａ列：=MATCH(ROW(),C:C,0)　　
でやってみましたが、問題なく動いています。

１０００行・・だとするとメモリをそこそこ消費するはずですから、
ご使用のＰＣ環境・表の条件によっては表示が遅れるかもしれませんね。
とはいえ、最近のＰＣならほとんど問題ないと思うのですが・・
その辺りは環境がわからないと何とも言えませんね。