Excel replace na and 0 with empty: Difference between revisions

From LemonWiki共筆
Jump to navigation Jump to search
No edit summary
mNo edit summary
 
(2 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>
<pre>
* IF( logical_test,      <span style="color: #98bf21">[value_if_true]</span>,      [value_if_false] )
=IF( logical_test,      [value_if_true],      [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,     "",      VLOOKUP(A1, SHEET1!A:E, 3, 0) )
</pre>


2. replace #N/A with empty
2. replace #N/A with <span style="color: #98bf21">empty</span>
<pre>
* =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)),      "")
</pre>




Line 21: Line 17:


[[Category:Spreadsheet]] [[Category:Excel]]
[[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)), "" )


參考資料