Narábanie s prázdnymi bunkami
In older versions of the software, empty cells were forced to numeric 0 in some contexts and to empty string in others, except in direct comparison where =A1=0 and =A1="" both resulted in TRUE if A1 was empty. Emptiness now is inherited until used, so both =VLOOKUP(...)=0 and =VLOOKUP(...)="" give TRUE if the lookup resulted in an empty cell being returned.
Jednoduchý odkaz na prázdnu bunku sa naďalej zobrazuje ako číslo 0, ale už nemusí byť nevyhnutne číselného typu, takže porovnávania s odkazujúcou bunkou fungujú tak, ako sa očakáva.
Nech pre nasledujúce príklady A1 obsahuje číslo, B1 je prázdna a C1 obsahuje odkaz na B1:
|
Case |
Formula |
Results and comments |
|---|---|---|
|
A1: 1 |
C1: =B1 |
Displays 0 |
|
=B1=0 |
||
|
=B1="" |
TRUE |
|
|
=C1=0 |
TRUE |
|
|
=C1="" |
TRUE (previously was FALSE) |
|
|
=ISNUMBER(B1) |
||
|
=ISNUMBER(C1) |
FALSE (previously was TRUE) |
|
|
=ISNUMBER(VLOOKUP(1;A1:C1;2)) |
FALSE (B1) |
|
|
=ISNUMBER(VLOOKUP(1;A1:C1;3)) |
FALSE (C1, previously was TRUE) |
|
|
=ISTEXT(B1) |
FALSE |
|
|
=ISTEXT(C1) |
TRUE |
|
|
=ISTEXT(VLOOKUP(1;A1:C1;2)) |
FALSE (B1, previously was TRUE) |
|
|
=ISTEXT(VLOOKUP(1;A1:C1;3)) |
FALSE (C1) |
|
|
=ISBLANK(B1) |
TRUE |
|
|
=ISBLANK(C1) |
TRUE |
|
|
=ISBLANK(VLOOKUP(1;A1:C1;2)) |
TRUE (B1, previously was FALSE) |
|
|
=ISBLANK(VLOOKUP(1;A1:C1;3)) |
FALSE (C1) |
Poznamenajme, že Microsoft Excel sa správa odlišne a vždy vracia číslo ako výsledok odkazu na prázdnu bunku, alebo bunku so vzorcom, ak je výsledkom prázdna bunka. Napríklad:
|
Case |
Formula |
Results and comments |
|---|---|---|
|
A1: <Empty> |
B1: =A1 |
Displays 0, but is just a reference to an empty cell. |
|
=ISNUMBER(A1) |
FALSE |
|
|
=ISTEXT(A1) |
FALSE |
|
|
=A1=0 |
TRUE |
|
|
=A1="" |
TRUE |
|
|
=ISNUMBER(B1) |
FALSE (Microsoft Excel: TRUE) |
|
|
=ISTEXT(B1) |
FALSE |
|
|
=B1=0 |
TRUE |
|
|
=B1="" |
TRUE (Microsoft Excel: FALSE) |
|
|
C1: =VLOOKUP(...) with empty cell result |
displays empty (Microsoft Excel: displays 0) |
|
|
=ISNUMBER(VLOOKUP(...)) |
FALSE |
|
|
=ISTEXT(VLOOKUP(...)) |
FALSE |
|
|
=ISNUMBER(C1) |
FALSE (Microsoft Excel: TRUE) |
|
|
=ISTEXT(C1) |
FALSE |