Excel replace na and 0 with empty: Difference between revisions
Jump to navigation
Jump to search
(Created page with "replace #N/A and 0 with empty when vlookup 1. replace 0 with empty <pre> =IF( logical_test, [value_if_true], [value_if_false] ) =IF( VLOOKUP(A1,SHEET1!A:E,3,0) = 0...") |
mNo edit summary |
||
| (4 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
replace #N/A and 0 with empty when vlookup | replace #N/A and 0 with <span style="color: #98bf21">empty</span> when vlookup | ||
1. replace 0 with empty | 1. replace 0 with <span style="color: #98bf21">empty</span> | ||
< | * IF( logical_test, <span style="color: #98bf21">[value_if_true]</span>, [value_if_false] ) | ||
* <span style="border-bottom-style: dotted;border-color: #999;">IF( <nowiki>VLOOKUP(A1,SHEET1!A:E,3,0) = 0</nowiki>, <span style="color: #98bf21"><nowiki>""</nowiki></span>, <nowiki>VLOOKUP(A1, SHEET1!A:E, 3, 0)</nowiki> )</span> | |||
=IF( VLOOKUP(A1,SHEET1!A:E,3,0) = 0, | |||
</ | |||
2. replace #N/A with empty | 2. replace #N/A with <span style="color: #98bf21">empty</span> | ||
< | * =IFNA (value, <span style="color: #98bf21">value_if_na</span> ) | ||
=IFNA (value, value_if_na ) | * =IFNA( <span style="border-bottom-style: dotted;border-color: #999;">IF( VLOOKUP(A1,SHEET1!A:E,3,0) = 0, <nowiki>""</nowiki>, VLOOKUP(A1, SHEET1!A:E, 3, 0))</span>, <span style="color: #98bf21"><nowiki>""</nowiki></span> ) | ||
=IFNA( IF( VLOOKUP(A1,SHEET1!A:E,3,0) = 0, "", VLOOKUP(A1, SHEET1!A:E, 3, 0)), "" | |||
</ | |||
[[Category:Spreadsheet]] | 參考資料 | ||
* [https://support.microsoft.com/zh-tw/kb/181213/zh-tw 如何使用 VLOOKUP 或 HLOOKUP 尋找完全相同的值] | |||
* [https://support.office.com/zh-tw/article/IF-%e5%87%bd%e6%95%b8-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2?ui=zh-TW&rs=zh-TW&ad=TW IF 函數] | |||
* [https://support.office.com/zh-tw/article/ISERROR-%e5%87%bd%e6%95%b8-6a5f3e99-40bc-43ce-a7c8-e79b7b6af5d5?ui=zh-TW&rs=zh-TW&ad=TW ISERROR 函數] | |||
[[Category:Spreadsheet]] [[Category:Excel]] | |||
[[Category:Data transformation]] | |||
[[Category:Data_hygiene]] | [[Category:Data_hygiene]] | ||
[[Category:Data Science]] | [[Category:Data Science]] | ||
Latest revision as of 11:15, 19 April 2015
replace #N/A and 0 with empty when vlookup
1. replace 0 with empty
- IF( logical_test, [value_if_true], [value_if_false] )
- IF( VLOOKUP(A1,SHEET1!A:E,3,0) = 0, "", VLOOKUP(A1, SHEET1!A:E, 3, 0) )
2. replace #N/A with empty
- =IFNA (value, value_if_na )
- =IFNA( IF( VLOOKUP(A1,SHEET1!A:E,3,0) = 0, "", VLOOKUP(A1, SHEET1!A:E, 3, 0)), "" )
參考資料