問題描述
查找包含數字的行中的第一個單元格？ (Find first cell in a row that contains a number?)
I'm working in Excel with an exported table such as this:
|-------------------------------------------------------------------------------|
| | A | B | C | D | E | F | G | H | I |
|---|-------------------|-----|-----|-----|-----|-----|-------|-----|-----------|
| 1 | Domain | JAN | FEB | MAR | APR | MAY | Start | End | Change |
|---|-------------------|-----|-----|-----|-----|-----|-------|-----|-----------|
| 2 | www.mydomain1.com | | 1 | 4 | 3 | 1 | 1 | 1 | 0 |
|---|-------------------|-----|-----|-----|-----|-----|-------|-----|-----------|
| 3 | www.mydomain2.com | 2 | 4 | 12 | 18 | 23 | 2 | 23 | 21 |
|---|-------------------|-----|-----|-----|-----|-----|-------|-----|-----------|
| 4 | www.mydomain3.com | | | 14 | 12 | | 14 | xxx | NOT FOUND |
|-------------------------------------------------------------------------------|
I'm trying to compare the current state (last cell) to the original cell (first cell with a value).
In column I, I have the formula =IF(G2 = "xxx", "NOT FOUND", IF(H2 = "xxx", "NOT FOUND", H2 - G2))
In column H, I have the formula =IF(F2 = "", "xxx", F2)
In column G, I need to find the first cell with a number. If there isn't one in that range, I need G to be "xxx". I suppose I only need to check for the first cell in the range (B2 to F2) that contains a value, not just a number. I tried using an Index and Match combo, but I couldn't quite understand it.
參考解法
方法 1:
The following formula (in $G$2 and filled down) should do the trick:
=IF(ISBLANK(F2),"xxx",INDEX(B2:E2,,COUNTBLANK(B2:F2)+1))
Note that this will only work if there are no other blank cells in between the first and last entries.
update: Here is a more versatile option from the Mr.Excel forum:
=INDEX(B2:F2,MATCH(1,IF(B2:F2<>0,IF(B2:F2<>"",1)),0))
This one has to be entered as an "array formula" which means you must press CTRL-SHIFT-ENTER after pasting in the formula.
response to comment:
It works great, except that if all cells are empty on a row, it returns #N/A. So, I changed the formula slightly to include an IsNA() condition:
=IF(ISNA(INDEX(B2:F2,MATCH(1,IF(B2:F2<>0,IF(B2:F2<>"",1)),0))), "xxx",
INDEX(B2:F2,MATCH(1,IF(B2:F2<>0,IF(B2:F2<>"",1)),0)))
Rather than duplicate the whole formula, you could do the following:
=IF(COUNTBLANK(B2:F2)=COLUMNS(B2:F2),"xxx",
INDEX(B2:F2,MATCH(1,IF(B2:F2<>0,IF(B2:F2<>"",1)),0)))
方法 2:
The following works if entered as an ARRAY formula (ctrl-shift-enter).
Works also if there are blank cells in the middle.
{=INDEX(B2:F2,1,MIN(IF(ISBLANK(B2:F2),99999,COLUMN(B2:F2))))}
HTH
Edit
This one controls the error too:
{=IF(ISERROR(INDEX(B2:F2,1,MIN(IF(ISBLANK(B2:F2),9999,COLUMN(B2:F2))))),"xxx",
INDEX(B2:F2,1,MIN(IF(ISBLANK(B2:F2),9999,COLUMN(B2:F2)))))}
(by Dexter、e.James、Dr. belisarius)