2021-09-30

《OpenOffice / LibreOffice / Calc》除了HLookup, VLookup,有些時候用match, address, indirect來組合運用會更方便

如下附圖,要把A, B, C三欄整理成E1:E25, J1:J25, L1:L25,去除節氣時間、節氣是空白的,通常會想到排序整理一下,但有些時候就是要保留原資料,不能讓原排序亂掉!


在已將所有節氣名稱資料都備妥的情況下,只是要取得節氣對應的日期即發生的時間點,該如何下公式呢?

通常會想到要用VLOOKUP,但是來看一下vlookup的公式參數:

vlookup 公式語法
=VLOOKUP(SearchCriterion; Array; Index; SortOrder)
SearchCriterion: 是指要在參照陣列範圍的第一欄中搜尋的條件值。
Array:是參照範圍,至少包含兩欄,被搜尋條件的範圍,必須放在參照範圍的第一欄。
Index:回傳值再參照範圍所在的欄。第一欄的編號為 1,以此類推。
SortOrder:
 是選用的參數,指出陣列中的第一欄是否向上排序(遞增排序)。
 若第一欄未向上排序(遞增排序),請輸入布林值 FALSE 或0
 搜尋排序過後的欄可更快速,且即使搜尋值未完全符合,只要該值介於排序清單的最低值與最高值之間,函數仍會一律傳回值。
 在未排序的清單中,搜尋值必須完全符合。否則函數會傳回訊息:錯誤:數值不存在(#NA)

這裡搜尋的條件值為「節氣」這個欄位不適合放在第一欄當作被搜尋的參照範圍,所以這裡試著用match, address, indirect來完成預期的效果:

  1. 用match來找到指定節氣名稱在原始資料中的所在位置(第?列) 
    MATCH(SearchCriterion; LookupArray; Type)
    Type:1:遞增, -1:遞減, 0:未排序

    =MATCH(E2;colJQ;0)
    colJQ:原始資料的節氣資料所在範圍名稱,這對應的是C2:C366
    原始資料的節氣資料夾雜空白和中文字,所以是未經排序的資料,Type用0

  2. 用address列出所找到的
    ADDRESS(Row; Column; Abs; A1; "Sheet")
    Row:表示儲存格參照的列號
    Column:表示儲存格參照的欄號 (數字,而非字母)
    Abs 決定參照的類型:
     1: absolute ($A$1)
     2:列絕對;欄相對 (A$1)
     3:列相對;欄絕對 ($A1)
     4:相對 (A1)
    A1 (選擇性) - 若設為 0,則使用 R1C1 表示法。若此參數不存在或設為非 0 值,則使用 A1 表示法。
    Sheet 表示工作表的名稱。必須在前後加上雙引號。

    =ADDRESS(F2+1;1)
  3. 只用所在列數、欄數,結果顯示為:$A$6

  4. 用indirect顯示指定儲存格內容的值
    INDIRECT(Ref; A1)
    Ref 表示將傳回內容的儲存格或區域 (使用文字格式) 之參照。
    A1 (選擇性) - 若設為 0,則使用 R1C1 表示法。若此參數不存在或設為非 0 值,則使用 A1 表示法。

    =INDIRECT(H2)
    顯示指定位址的內容  (指定節氣小寒的對應日期)

  5. 指定節氣小寒的對應時間,就直接將address, indirect合併使用顯示內容
    =INDIRECT(ADDRESS(F2+1;2))

  6. 直接將match, address, indirect合併使用顯示內容
    =INDIRECT(ADDRESS(MATCH(E29;colJQ;0)+1;1))
    =INDIRECT(ADDRESS(MATCH(E29;colJQ;0)+1;2))


沒有留言: