excel 相關

Excel深度教學:使用「資料驗證」來製作「下拉式選單」

Excel深度教學:「二層」、「三層」的動態「下拉式選單」設定

Excel深度教學:VLOOKUP查表函數及比對中文字串時發生錯誤的問題

EXCEL實用技巧教學「樞紐分析表(Pivot Tables)」

EXCEL的樞紐分析表-1

==================================================

多條件查詢
=index(array,match(1,match(array,(columnX=X1)*(columnY=Y1),0),data_column)

文章出處:

How to VLOOKUP with Multiple Criteria Using INDEX and MATCH

==================================================

名稱

EXCEL 的名稱類型有:
已定義名稱:可表示單儲存格,儲存格範圍,公式,常數
表格名稱

套用任意儲存格的公式(公式很長,看起來很雜亂時很好用,有點函數或變數的概念),可用名稱來簡化
定義A欄某一儲存格位址,定義名稱:A位址 =address(row(),1,3) ,(3表欄絕對參照)
定義A欄某一儲存格的值,定義名稱:A值 =indirect(A位址)
A欄某一儲存格中若含有字串(例如: 5-16),截取在-右側的數字16,定義名稱:Ardash = right(A值,find(“-“,A值))
上述3個定義完成後,接著在非A欄的同一列的儲存格輸入 Ardash 即可完成擷取A欄儲存格-右側的數字 16

==================================================

陣列公式

陣列形式:
一維陣列:
單欄陣列:垂直連續儲存格或{1;2;3;4} (;分隔)
單列陣列:水平連續儲存格或{1,2,3,4} (,分隔)
二維陣列:
連續儲存格(參考位址陣列)或常數陣列

陣列函數:
多格陣列函數或單格陣列函數

單格陣列函數:
SUM

多格陣列函數:
COUNTIF 函數語法:=COUNTIF(range,criteria),陣列語意:{=COUNTIF(範圍,來源參考位址陣列)}
MATCH 函數語法:=MATCH(lookup_value, lookup_array, [match_type])
MATCH 函數的一般用法,MATCH(參考位址,單欄/單列範圍,0)
MATCH 函數的陣列用法,{=MATCH(來源陣列,單欄/單列範圍,0)}
LOOKUP 函數語法:=LOOKUP(lookup_value, array)
LOOKUP 函數 “正港” 語法:=LOOKUP(lookup_value, lookup_vector, result_vector)
LOOKUP 函數 “簡潔” 語法:=LOOKUP(lookup_value, lookup_table)
VLOOKUP 函術語法:=VLOOKUP(lookup_value, table_array, col_index_num,range_lookup)
第一個參數不適合用常數陣列,第二個參數不能擴展成陣列,第三個參數可以為常數陣列但第一個參數只能是參考位址
SUMIF 函數語法:=SUMIF(range,criteria,[sum_range])
陣列語意:{=SUMIF(查找範圍,來源陣列,[加總範圍])}

參考文章:
Excel陣列公式 系列一:入門
Excel陣列公式 系列三:SUM函數之一  
Excel陣列公式 系列五:COUNTIF函數
Excel陣列公式 系列六:MATCH函數之一
Excel陣列公式 系列七:LOOKUP函數
Excel陣列公式 系列八:VLOOKUP函數之一
Excel陣列公式 系列九:VLOOKUP函數之二

========================================

二維陣列排序

排序 A1:D36二維陣列的數值:={small($A1:$D36),row(1:1)}

在另外一欄某一列(例如:第三列)取用某一維陣列的內容,結果會是該一維陣列在第三列的值,並非是第一列

========================================

函數

取商 QUOTIENT
餘數 MOD
文字轉數字 VALUE

This entry was posted in 應用程式 and tagged . Bookmark the permalink.