1. 添加dropdownlist
Step 1.1:
在一个新sheet中输入要作为选项的值,每个单元格一个,纵向。
Step 1.2:
选中全部选项,将上方菜单的名字修改成你想要的列表名字
Step 1.3:
点中需要设置下拉菜单的单元格,选择data,data validation, 降低一个tab中的任何值选择成list。
在新窗口中输入=, 然后按下F3键,选择刚刚设置好的列表名称。
完成
2. 固定标题栏不随滚动消失
Step 2.1
选中标题栏
Step 2.2
view -> Freeze Panes -> Freeze top row
Done
3. Formula
3.1 IF (conditional statement)
syntax: IF( condition, return value when true, return value when false)
example: IF(MID(A2,6,3)="TOS","Toshiba", "void")
note: maximum nested level : 60
3.2 MID (substring)
syntax: MID(source string, start index, length)
example: MID(A2,6,3)
3.3 LEFT(substring from left) or Right...
syntax: LEFT(source string, length)
example: LEFT(A2,2)
3.4 VLOOKUP (find value from hash table)
syntax: VLOOKUP(source string, [table start index]:[table end index], return value's column index ,fuzzy match flag)
example: VLOOKUP(MID(A24870,1,2),Sheet2!$D$1:$E$12,2,FALSE)
note: table must be sorted by left column
when fuzzy match flag is set as FALSE, do the exact match
3.5 ISNA (verfy the input value valid or not(#N/A) )
syntax: ISNA(input)
example: ISNA(VLOOKUP(MID(A24870,1,2),Sheet2!$D$1:$E$12,2,FALSE))
note: return TRUE or FALSE
3.6 Find
Syntax: find(“target char”, start_index)
return value: the index of the target char from left
Example: FIND(" ",B56995)
3.6 Search
Syntac: SEARCH("target_string",Cell_Number)
return value: times of the target_string matched
=IF(ISNUMBER(SEARCH("target_string",Cell_Number)),"OK", "Not OK")
Reference:
http://www.youtube.com/watch?v=i-w5iT7Wejc
http://club.excelhome.net/thread-79042-1-1.html
http://zhidao.baidu.com/question/275650150.html
http://zhidao.baidu.com/question/210532082.html