如果出现 #N/A,我该如何修复?

huangapple go评论62阅读模式
英文:

How can I fix If NO MATCH #N/A?

问题

I'm working on a schedule table to pull the corresponding working hours for each shift that match.

I have the following data:

CELL 1: E694

CELL 2: E356

TABLE 1: $AL$10:$AL$16

TABLE 2: $AL$1031:$AL$1037

TABLE 3: $AM$1031:$AM$1037

Here's the intended scenario:

If CELL 1 MATCH TABLE 1, then MATCH CELL 2 with TABLE 2 and pull data from TABLE 3.
If CELL 1 DON'T MATCH TABLE 1, MATCH CELL 1 with TABLE 2 and pull data from TABLE 3.

I tried the following formula:

=IF(MATCH(E694,$AL$10:$AL$16,0), timevalue(INDEX($AM$1031:$AM$1037,MATCH(E356,$AL$1031:$AL$1037,0),0))*24, timevalue(INDEX($AM$1031:$AM$1037,MATCH(E694,$AL$1031:$AL$1037,0),0))*24)

The formula only works when there's a MATCH, but If there's no MATCH it doesn't execute the false condition and returns #N/A error.

I know I'm missing something here, but I just can't figure it out.

Would someone be able to give me a hand here?

P.S: here's a simple test sheet (not with the exact same values, but the same formula and logic)

https://docs.google.com/spreadsheets/d/1Wvw-K7iZ6TnID-SzLt2CtOxu4Xx1BuhAjexU_onRASg/edit?usp=sharing

英文:

I'm working on a schedule table to pull the corresponding working hours for each shift that match.

I have the following data:

CELL 1: E694

CELL 2: E356

TABLE 1: $AL$10:$AL$16

TABLE 2: $AL$1031:$AL$1037

TABLE 3: $AM$1031:$AM$1037

Here's the intended scenario:

If CELL 1 MATCH TABLE 1, then MATCH CELL 2 with TABLE 2 and pull data from TABLE 3.
If CELL 1 DON'T MATCH TABLE 1, MATCH CELL 1 with TABLE 2 and pull data from TABLE 3.

I tried the following formula:

=IF(MATCH(E694,$AL$10:$AL$16,0), timevalue(INDEX($AM$1031:$AM$1037,MATCH(E356,$AL$1031:$AL$1037,0),0))*24, timevalue(INDEX($AM$1031:$AM$1037,MATCH(E694,$AL$1031:$AL$1037,0),0))*24)

The formula only works when there's a MATCH, but If there's no MATCH it doesn't execute the false condition and returns #N/A error.

I know I'm missing something here, but I just can't figure it out.

Would someone be able to give me a hand here?

P.S: here's a simple test sheet (not with the exact same values, but the same formula and logic)

https://docs.google.com/spreadsheets/d/1Wvw-K7iZ6TnID-SzLt2CtOxu4Xx1BuhAjexU_onRASg/edit?usp=sharing

答案1

得分: 2

ISNA 最有可能是您想要的,尽管根据情况,ISERRISERROR 也可能适用。

一些注意事项:

  • 我复制了您的原始工作表(请参阅您的 Google 电子表格上的“修复”选项卡),并稍微调整了布局,以便更好地理解您想要实现的目标。
  • 我现在在您的公式中使用了命名范围,而不是绝对的 A1 符号范围。这样做更容易维护,更不容易出错,并且在编写公式时更不容易混淆。
  • 我删除了您用于将格式化的日期时间字符串转换为时间跨度的 timevalue(...)*24 部分。相反,我将该列的格式设置为时间跨度(h:mm),这样可以在不使公式混乱的情况下执行相同的操作。
  • “公式”、“单元格1”和“单元格2”我分成了不同的列,以便您和我可以同时测试和验证多个单元格值的不同变化。您现在看到的8行是单元格1和单元格2的8种变化(“公式”列对所有测试行都是相同的)。

基于我对您描述的最佳理解,下面是更新后的工作公式:

=IF(
COUNTIF(table1, B1), 
INDEX(table3, MATCH(C1, table2, 0)), 
INDEX(table3, MATCH(B1, table2, 0)))

如下面的图像中弹出的帮助中所示,IF 语句评估第一个参数(logical_expression),该参数必须是可以转换为布尔值(true/false)的值。

由于数字可以表示布尔值(0 = false,其他任何值 = true),并且我们只需要第一个参数的是/否,因此我用一个简单的 COUNTIF 替换了第一个 MATCH 函数,它只返回与 B1 匹配的 table1 中的单元格数量。与 MATCH 函数不同,如果找不到匹配项,COUNTIF 简单地返回0。由于0与false相同,我们的 IF 语句知道使用第二个 MATCH 而不是第一个。

如果出现 #N/A,我该如何修复?

*关于我原始答案的说明:*我最初提供的第一个解决方案使用了 ISNA(MATCH(...)) = false 而不是 COUNTIF(...)。这也可以工作,但是过于复杂:ISNA 如果其参数的值为“N/A”,则返回true,否则返回true,实际上将我们的 MATCH 转换为布尔值。然而,必须反转该布尔值,以便“N/A”值为false,而成功的匹配为true。=false 仅用于返回与 ISNA 给我们的相反值。false=false吗?是的,所以我们返回true。true=false吗?不是,所以我们返回...false。

英文:

ISNA is most likely what you are wanting, though ISERR or ISERROR could also be applicable depending on the situation.

A couple notes:

  • I duplicated your original sheet (see the "Fixes" tab on your Google Spreadsheet), and tightened up the layout slightly so I could better understand what you're trying to accomplish.
  • I'm using named ranges in your formula for the tables now, instead of absolute A1 notation ranges. They are easier to maintain, less error prone, and less confusion when writing formulas.
  • I removed the timevalue(...) * 24 you were using to convert the formatted DateTime strings to TimeSpans. Instead, I set that column format to TimeSpan (h:mm), which does exactly the same thing without cluttering our formulas.
  • "Formula", "Cell 1", and "Cell 2" I separated into columns, so that you and I could test and verify the formula on multiple variations of the cell values at once. The 8 rows you see now are 8 variations of cell 1 and cell 2 ("Formula" column is the same same for all test rows).

The updated working formula is below, based on my best interpretation of your description.

=IF(
COUNTIF(table1, B1), 
INDEX(table3, MATCH(C1, table2, 0)), 
INDEX(table3, MATCH(B1, table2, 0)))

As seen in the help popup in the image below, an IF statement evaluates the first argument (logical_expresion), which must be a value that can be converted into a boolean value (true/false).

Since numbers can represent a bool value (0 = false, anything else = true), and all we need for the first argument is a yes/no, I replaced the first MATCH function with a simple COUNTIF, which simply returns the number of cells in table1 that match B1. Unlike the MATCH function, which returns N/A if no matches are found, COUNTIF simply returns a 0. Since 0 is the same as false, our IF statement knows to use our second MATCH instead of the first.

如果出现 #N/A,我该如何修复?

Note regarding my original answer: The first solution I provided used ISNA(MATCH(...)) = false instead of COUNTIF(...). This works also, but is unnecessarily complicated: ISNA returns true if the value of it's argument is "N/A", true otherwise, effectively converting our MATCH into a bool value. That bool value had to be inverted, however, so that "N/A" value would be false, and a successful match would be true. =false was just used to return the opposite value that ISNA gave us. Does false=false? yes, so we return true. Does true=false? no, so we return...false.

huangapple
  • 本文由 发表于 2023年5月10日 21:46:52
  • 转载请务必保留本文链接:https://go.coder-hub.com/76219210.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定