处理列表DataValidation公式中的错误 [英] Handle error in list DataValidation formula

问题描述

我有一个依赖于其他列的列表DataValidation公式.

I have a list DataValidation formula with depends on other column.

OFFSET(DATA!A2;0;MATCH(A1;DATA!1:1;0)-1;COUNTA(OFFSET(DATA!A:A;0;MATCH(A1;DATA!1:1;0)-1))-1)

当其他列为空时，公式将计算为错误(MATCH导致#N/A)，并且excel会通过以下方式警告该错误:

When other column is empty the formula evaluates to an error (MATCH results in #N/A) and excel warns about it with:

源当前评估为错误.您要继续吗?"

"The source currently evaluates to an error. Do you want to continue?"

当我接受警告弹出窗口时，DataValidation会导致列表为空，这对我来说很好. 我的目标是创建一个公式，该公式将永远不会评估为错误，而是提供默认值.

When I accept the warning popup DataValidation results in empty list, which is fine for me. My goal is to create formula which will never evaluate to error but provide default value instead.

有没有一种方法可以处理此类错误并为excel提供空白列表?还是列出一个包含空字符串的项目?

Is there a way to handle such error and provide empty list for excel? Or list with one item containing empty string?

我当前的想法是通过提供默认的空列表来创建处理错误.但是我无法为其创建适当的语法.

My current idea is to create handling error with providing default empty list. But I cannot create proper syntax for it.

IFERROR(myformula, <empty list or list with one empty string>)

任何帮助将不胜感激.

注意:此问题源自我的其他问题我在其中创建了python脚本来重现这种情况.

NOTE: this question originates from my other problem where I created python script to reproduce this case.

推荐答案

知道MATCH结果在#N/A中，我将其包装在IF(IFNA(...

Knowing MATCH results in #N/A I wrapped it in IF(IFNA(...

IF(IFNA(failingformula;FALSE);formula;someCellWithEmptyValue)
and whole formula is
IF(IFNA(MATCH(A1;DATA!1:1;0);FALSE);OFFSET(DATA!A2;0;MATCH(A1;DATA!1:1;0)-1;COUNTA(OFFSET(DATA!A:A;0;MATCH(A1;DATA!1:1;0)-1))-1);C3)

其中fomula是有问题的，而C3是我知道的一些随机单元格，将包含空值.

where fomula is written in question and C3 is some random cell I know will contain empty value.

如果有人知道更好和更清洁的方法，那么请分享这个想法.

Still if someone knows better and cleaner approach then please share the idea.

这篇关于处理列表DataValidation公式中的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！