KSL`s Base: 2019

2019年10月9日

2019年10月1日

Excel：LOOKUP

找接近數值時可以用的函數。而且比vlookup方便的是，他可以直接回傳往左邊欄位的內儲存格內容。但缺點是，如果找不到直，可能會傳回不完全相符的結果。

語法
LOOKUP(lookup_value, lookup_vector, [result_vector])

LOOKUP 函數的向量形式語法具有下列引數：

lookup_value 必要。這是 LOOKUP 在第一個向量中要尋找的值。 Lookup_value 可以是數字、文字、邏輯值，或是參照某個值的名稱或參照。

lookup_vector 必要。僅包含一列或一欄的範圍。 lookup_vector 中的值可以是文字、數字或邏輯值。

重要: lookup_vector 中的值必須以遞增順序排列：..., -2, -1, 0, 1, 2, ...、A-Z、FALSE、TRUE，否則，LOOKUP 可能不會傳回正確的值。文字不區分大小寫。

result_vector 選用。僅含一列或一欄的範圍。 result_vector 引數的大小必須與 lookup_vector 相同。其大小必須一樣。

備註
如果 LOOKUP 函數找不到 lookup_value，就會比對 lookup_vector 中小於或等於 lookup_value 的最大值。

如果 lookup_value 小於 lookup_vector 中的最小值，LOOKUP 函數會傳回 #N/A 的錯誤值。

參考資料：
https://support.office.com/zh-hk/article/lookup-%E5%87%BD%E6%95%B8-446d94af-663b-451d-8251-369d5e3864cb

2019年9月17日

Excel：OFFSET

之前常遇到一些固定錯位的表格，通常可以用OFFSET作便利的整理。

語法 OFFSET(reference, rows, cols, [height], [width])
OFFSET 函數語法具有下列引數：

Reference 必要。這是用以計算位移的起始參照。 Reference 必須參照一個儲存格或相鄰的儲存格範圍，否則 OFFSET 會傳回 #VALUE! 錯誤值。
Rows 必要。這是要左上角儲存格往上或往下參照的列數。使用 5 做為 rows 引數，指出參照的左上角儲存格是 reference 下方的第五列。 Rows 可以是正數 (表示在起始參照下方) 或負數 (表示在起始參照上方)。
Cols 必要。這是要結果的左上角儲存格向左或向右參照的欄數。使用 5 作為 cols 引數，指出參照位址的左上角儲存格是 reference 右方的第五欄。 Cols 可以是正數 (表示在起始參照右方) 或負數 (表示在起始參照左方)。 [高度] 選擇性。這是要傳回參照的列數高度。
Height 必須是正數。寬度選擇性。這是要傳回參照的欄數寬度。
Width 必須是正數。

也可以透過一些計算來但判斷所要選取的範圍，例如：

=SUM(OFFSET(S3,0,$K$2,1,1):OFFSET(S3,0,$K$2+$L$2-1,1,1))

參考資料：https://support.office.com/zh-tw/article/offset-%E5%87%BD%E6%95%B8-c8de19ae-dd79-4b9b-a14e-b4d906d11b66

2019年8月14日

Excel：VBA，使用變數選擇範圍（Range）

自己試著從零開始寫的第一個VBA

Sub ()
v1 = Cells(5, 1)    '對應的列、欄位置，變數訂為v1
v2 = Cells(6, 1)    '對應的列、欄位置，變數訂為v2
'MsgBox (v1)    '訊息彈窗可以用來檢查變數v1
Range(v1 & ":" & v2).Select '選擇目標欄位（已變數回傳值所指定的範圍）
Selection.Copy  '複製所選
End Sub

其中我原本A4位置的公式=COUNTA(1:1)-1，是用來計算A5和A6的=ADDRESS(3,5,4)及=ADDRESS(3,5+A4-1,4)，所以可以改寫如下：

v1 = WorksheetFunction.CountA(Range("1:1")) - 1 '計算COUNTA(1:1)-1的值，將變數訂為v1
v2 = Cells(3, 5).Address    '計算ADDRESS(3,5,4)的值，將變數訂為v2
v3 = Cells(3, 5 + v1 - 1).Address    '計算ADDRESS(3,5 +v1 -1,4)的值，將變數訂為v3
Range(v2 & ":" & v3).Select '選擇目標欄位（已變數回傳值所指定的範圍）
Selection.Copy  '複製所選

參考資料：

EXCEL VBA從頭來過-基本語法(上篇)
https://medium.com/@weilihmen/excel-vba%E5%BE%9E%E9%A0%AD%E4%BE%86%E9%81%8E-%E5%9F%BA%E6%9C%AC%E8%AA%9E%E6%B3%95-%E4%B8%8A%E7%AF%87-c2bc76065ecd

提到cells、range

MsgBox 函式
https://docs.microsoft.com/zh-tw/office/vba/language/reference/user-interface-help/msgbox-function

如何在Excel VBA使用Excel工作表中的函數
http://wordpress.bestdaylong.com/blog/archives/2547

Thread: Using "Address" function in VBA?
https://www.mrexcel.com/forum/excel-questions/341078-using-address-function-vba.html

2019年8月7日

Excel：INDEX+MATCH=退著VLOOKUP

用vlookup的時候常常會遇到一個問題，只能傳回查的目標資料右方的資料。

如果用INDEX+MATCH就可以達到查目標資料左方的資料了。例如

=INDEX(C:C,MATCH(A2,D:D,0),1)

這樣的意思是在D欄查A2的資料，如果查到，回傳的值則當作INDEX函數中的列，並找出C欄對應列的值。

MATCH(lookup_value, lookup_array, [match_type])

MATCH 函數語法具有下列引數：

lookup_value 必要。這是要在 lookup_array 中比對的值。例如，當您在通訊錄中查閱某個人的號碼時，您是以那個人的姓名作為查閱值，但是電話號碼才是您要的值。

lookup_value 引數可以是一值 (數字、文字或邏輯值)，也可以是數字、文字或邏輯值的儲存格參照。

lookup_array 必要。這是要搜尋的儲存格範圍。

match_type 選擇性。數字 -1、0 或 1。 match_type 引數會指定 Excel 如何將 lookup_value 與 lookup_array 中的值相比對。這個引數的預設值是 1。

下表將描述該函數如何根據 match_type 引數的設定來尋找值。

MATCH函數參考：
https://support.office.com/zh-hk/article/match-%E5%87%BD%E6%95%B8-e8dffd45-c762-47d6-bf89-533f4a37673a

INDEX(array, row_num, [column_num])

陣列形式的 INDEX 函數具有下列引數：

array 必要。儲存格範圍或陣列常數。

如果陣列包含只有一個資料列或欄，則相對應的 row_num 或 column_num 引數是選擇性的。

如果陣列有一個以上的列與多個欄中，使用 [僅限 row_num 或 column_num 設，INDEX 會傳回整列或欄的陣列陣列中。

row_num 必要。選取列中的值傳回的陣列。如果省略 row_num，column_num 是必要的。

column_num 選用。選取要從中傳回值的陣列中的欄。如果省略 column_num，row_num 是必要的。

註解
如果使用了 row_num 與 column_num 引數，INDEX 會傳回 row_num 與 column_num 交集處儲存格中的值。

row_num 及 column_num 必須對應到陣列; 內的儲存格否則，INDEX 會傳回 #REF ！錯誤的非數字 (文字) 值，您的公式則會中斷。

如果將 row_num 或 column_num 設為 0 （零），INDEX 會分別傳回整欄或整列的值的陣列。若要使用以陣列方式傳回的值，INDEX 函數輸入為陣列公式。

INDEX函數參考：
https://support.office.com/zh-hk/article/index-%E5%87%BD%E6%95%B8-a5dcf0dd-996d-40a4-a822-b56b061328bd

2019年6月20日

SynologyDS418

2019/2/14購入SynologyDS418，$10999
WD 10TB紅標，$9390
用Line point換了WD 3TB紅標，3390點
把舊的3顆WD 3TB紅標加上新的WD 3TB紅標組成RAID5，空間可達7.8TB

Note：
如果有用Drive，檔案刪除需要到Drive中的資源回收筒再刪除一次，否則空間不會釋出。
（快照也是相同原理）

事件紀錄：

2019/2/20儲存空間建立完畢

2019/4/9儲存空間降級，顯示第顆3硬碟（由左至右數）移除，原本以為可能損毀。

控制台→系統→硬體&電源→嗶聲控制→目前發出嗶聲的原因→停止嗶聲

拔出硬碟檢查無壞軌，先插回NAS。

儲存空間管理員→HDD/SSD→動作→secure erase（抹除資料，3TB硬碟估計需約424分鐘）

儲存空間管理員→儲存集區→動作→修復

於2019/4/12重建完畢。

2019/4/15儲存空間又被降級，仍然顯示第3顆硬碟被停用。

詢問客服，他建議我將第顆3硬碟和第4顆硬碟交換，透過交換硬碟位置來判斷問題是跟著插槽還是硬碟。

儲存空間管理員→HDD/SSD→動作→secure erase（抹除資料，3TB硬碟估計424分鐘）

抹除資料後還是顯示紅色，再拔出硬碟裝在電腦重新格式化後，插回NAS。

儲存空間管理員→儲存集區→動作→修復（約10小時完成）

於2019/4/18重建完畢。

截至2019/6/20為止沒有再異常，暫不追蹤。

Excel：MID&SEARCH擷取特定字元間的文字

如果有一串文字是：
板主：xxx | 昨日人氣：1787211 | 昨日文章：3474

如果我想要的值是「1787211」，而我發現它前後有「氣：」和「 | 昨日文」這個字串是固定的。那麼我可以透過計算來知道目標值所在文字的起始數字來擷取目標值。假設該字串位置在D2，則範例如下：

=MID(D2,SEARCH("氣：",D2)+2,SEARCH(" | 昨日文",D2)-(SEARCH("氣：",D2)+2))

函數參考：

MID、MIDB 函數
https://support.office.com/zh-hk/article/mid%E3%80%81midb-%E5%87%BD%E6%95%B8-d5f9e25c-d7d6-472e-b568-4ecb12433028

SEARCH 與 SEARCHB 函數
https://support.office.com/zh-tw/article/search-%E8%88%87-searchb-%E5%87%BD%E6%95%B8-9ab04538-0e55-4719-a72e-b6f54513b495

2019年5月28日

Excel：TEXT &VALUE 函數，可把日期變為文字

如果用&去連結某個儲存格中的日期，會變成一串數字。（Excel紀錄日期的方式）

但如果用 TEXT 函數，就可把日期變為文字：

例如A1是日期，則

="今天是"&TEXT(A1, "M/D")

可讓儲存格顯示為：今天是1/12

而不會是：今天是43477

其他可參考：
https://support.office.com/zh-tw/article/text-%E5%87%BD%E6%95%B8-20d5ac4d-7b94-49fd-bb38-93d29371225c

訂閱：文章 (Atom)

KSL`s Base

##title##