史萊姆論壇 - excel lookup 表格對應問題

史萊姆論壇 (http://forum.slime.com.tw/)

- 軟體應用問題討論區 (http://forum.slime.com.tw/f5.html)

- - excel lookup 表格對應問題 (http://forum.slime.com.tw/thread287855.html)

excel lookup 表格對應問題

請教版上EXCEL的達人大大們.
我現在要做人員的年資計算,
預設的表格如下:

年資的表格如下:

D3,E3手動輸入日期,
F3代入公式: DATEDIF($E$3,$D$3,"y")&"年"
G3代入公式: DATEDIF($E$3,$D$3,"ym")&"月"
請問如何在H3儲存格應套入何種公式後,讓它會去找第二張圖的年資(優先尋找)和月份(次之尋找)後自動代入到H3?
我有找過用VLOOKUP和HLOOKUP方式作業,但以菜鳥來說套來套去都有點問題,
故特在此請教達人大大們指導一下.

試看看 INDEX ... 比方說
INDEX(陣列資料範圍, index num 對應年份, index A 對應月份)
=INDEX(B2:M17,F2+1,G2)

F2+1 為了修正對應 B2:M17 的資料表的年資 index 偏移值因此 +1

因為圖一年資的欄位中有中文字「年、月」會出錯誤，要不就是把「年、月」去掉
或者使用 LEFT(F2,LEN(F2)-1) ,LEFT(G2,LEN(G2) 做自動糾運算時把「年、月」
自己去掉保留數值部份

=INDEX(B2:M17,LEFT(F2,LEN(F2)-1)+1,LEFT(G2,LEN(G2)-1)+1)

進一步修飾 IF(VALUE(LEFT(F2,LEN(F2)-1))>15,20, XXXX)

超過一定年資，自動以一定的天數固定顯示，比方說 20 天

=IF(VALUE(LEFT(F2,LEN(F2)-1))>15,20, INDEX(B2:M17,LEFT(F2,LEN(F2)-1)+1,LEFT(G2,LEN(G2)-1)+1))

另外一提，年資 0 年的那一排都是 0 天，同樣可以利用 IF 來處理

IF(VALUE(LEFT(F2,LEN(F2)-1))＝0,0, XXXX)
因此年資表格的 =INDEX(B2:M17,F2+1,G2)

原本 F2+1 為了修正對應 B2:M17 的資料表的年資 index 偏移值的這個 +1
就可以取消。並將對應的 B2:M17 的資料表的起始範圍位置改為 B3:M17

-----------------------
年資為空白時顯示空白

IF(OR(F2="",G2=""),"", XXXX)
-----------------------

經過公式複製，對應那個年資表的「位置會偏移」要加 $ 改成絕對位置 $x$x
$B$3:$M$17

-----------------------
最後修正得到
=IF(OR(F2="",G2=""),"",IF(VALUE(LEFT(F2,LEN(F2)-1))=0,0,IF(VALUE(LEFT(F2,LEN(F2)-1))>15,20,INDEX($B$3:$M$17,LEFT(F2,LEN(F2)-1),LEFT(G2,LEN(G2)-1)))))

... ...

語法:

=IF(OR(F2="",G2=""),"",  說明 這一行 IF 負責「顯示空白」



      IF(VALUE(LEFT(F2,LEN(F2)-1))=0,0,  說明 這一行 IF 負責「顯示 0 年資 0 天」



          IF(VALUE(LEFT(F2,LEN(F2)-1))>15,20, 說明 這一行 IF 負責「顯示 15年資後 20 天」



              INDEX($B$3:$M$17,LEFT(F2,LEN(F2)-1),LEFT(G2,LEN(G2)-1) ) ) ) )

              說明 這一行 INDEX 負責「顯示 各年資的 個別天數」

謝謝getter大的解釋和幫忙,明天上班就來去驗證看看.

getter大您好,有關公式寫法上再請教一下問題.
假設上方圖片1的試算代表工作表2(sheet2)
圖片2年資表代表工作表1(sheet1)
那你所寫的公式上:
=IF(OR(F2="",G2=""),"",IF(VALUE(LEFT(F2,LEN(F2)-1))=0,0,IF(VALUE(LEFT(F2,LEN(F2)-1))>15,20,INDEX($B$3:$M$17,LEFT(F2,LEN(F2)-1),LEFT(G2,LEN(G2)-1)))))
若要更正確的公式是不是應該是...
=IF(OR(F2="",G2=""),"",IF(VALUE(LEFT(sheet1!F2,LEN(sheet1!F2)-1))=0,0,IF(VALUE(LEFT(sheet1!F2,LEN(sheet1!F2)-1))>15,20,INDEX($B$3:$M$17,LEFT(sheet1!F2,LEN(sheet1!F2)-1),LEFT(sheet1!G2,LEN(sheet1!G2)-1)))))

是不是這樣呢?
若是的話,我最後套入的結果是#value!
是我哪裡出錯了嗎?

引用:

作者: 輕舞飛揚 (文章 2371402)

一種是括號、引數的錯誤 ... 另一種迪西認為比較有可能的錯誤是

INDEX($B$3:$M$17,LEFT(F2,LEN(F2)-1),LEFT(G2,LEN(G2)-1)

的 $B$3:$M$17 的部份 ...

因為沒有原始檔案可以直接測試公式，迪西是臨時只用一個「工作表」做測試的 ...
您的狀況試 ... 這是「兩個」分別的工作表

IF、OR、LEFT、LEN 所使用對應位置的工作表 ... 相當於圖片一
INDEX 的 $B$3:$M$17 部份，您要改成對應成您的圖片二

比方說 sheet1!$B$3:$M$17 或者是 sheet2!$B$3:$M$17 請以您的實際工作表名稱為主 ...

其實可以利用試算表的 fx 這個按鈕去驗證公式有沒有錯，或是哪裡有錯。

基本上整條公式，是下在那一個「可休天數」的那一格