史萊姆論壇

返回   史萊姆論壇 > 專業主討論區 > 軟體應用問題討論區
忘記密碼?
論壇說明 標記討論區已讀

歡迎您來到『史萊姆論壇』 ^___^

您目前正以訪客的身份瀏覽本論壇,訪客所擁有的權限將受到限制,您可以瀏覽本論壇大部份的版區與文章,但您將無法參與任何討論或是使用私人訊息與其他會員交流。若您希望擁有完整的使用權限,請註冊成為我們的一份子,註冊的程序十分簡單、快速,而且最重要的是--註冊是完全免費的!

請點擊這裡:『註冊成為我們的一份子!』

Google 提供的廣告


發文 回覆
 
主題工具 顯示模式
舊 2017-10-06, 03:25 PM   #1
輕舞飛揚
超級版主
 
輕舞飛揚 的頭像
榮譽勳章
UID - 222573
在線等級: 級別:35 | 在線時長:1401小時 | 升級還需:39小時級別:35 | 在線時長:1401小時 | 升級還需:39小時級別:35 | 在線時長:1401小時 | 升級還需:39小時級別:35 | 在線時長:1401小時 | 升級還需:39小時級別:35 | 在線時長:1401小時 | 升級還需:39小時級別:35 | 在線時長:1401小時 | 升級還需:39小時級別:35 | 在線時長:1401小時 | 升級還需:39小時級別:35 | 在線時長:1401小時 | 升級還需:39小時級別:35 | 在線時長:1401小時 | 升級還需:39小時級別:35 | 在線時長:1401小時 | 升級還需:39小時
註冊日期: 2006-01-27
住址: 心的牢寵
文章: 2445
現金: 11059 金幣
資產: 2882357 金幣
預設 求助 - excel lookup 表格對應問題



--------------------
閱讀本主題的最佳解答
--------------------


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

此帖於 2017-10-07 12:00 AM 被 輕舞飛揚 編輯.
__________________
97年5月1日求檔區新版規正式實施.
98年3月24日修改及新增規定了!!請注意!!
求檔不回應不動作,被人列入黑名單可別怨嘆!!
輕舞飛揚 目前離線  
送花文章: 340, 收花文章: 747 篇, 收花: 1809 次
回覆時引用此帖
舊 2017-10-08, 04:34 AM   #2 (permalink)
管理員
 
getter 的頭像
榮譽勳章
UID - 6433
在線等級: 級別:96 | 在線時長:9733小時 | 升級還需:64小時級別:96 | 在線時長:9733小時 | 升級還需:64小時級別:96 | 在線時長:9733小時 | 升級還需:64小時級別:96 | 在線時長:9733小時 | 升級還需:64小時級別:96 | 在線時長:9733小時 | 升級還需:64小時級別:96 | 在線時長:9733小時 | 升級還需:64小時
註冊日期: 2002-12-08
住址: 天線星球
文章: 8157
精華: 0
現金: 19955 金幣
資產: 765391 金幣
預設

試看看 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))

此帖於 2017-10-12 06:21 PM 被 getter 編輯.
__________________
在「專業主討論區」中的問題解決後,要記得按一下 http://forum.slime.com.tw/images/stamps/is_solved.gif 按鈕喔,
這是一種禮貌動作。

一樣是在「專業主討論區」中發問,不管問題解決與否,都要回應別人的回答文喔。
不然搞 [斷頭文],只看不回應,下次被別人列入黑名單就不要怪人喔。

天線寶寶說再見啦~ ... 天線寶寶說再見啦~

迪西:「再見~ 再見~」

Otaku Culture Party 關心您 ...
getter 目前離線  
送花文章: 37855, 收花文章: 6441 篇, 收花: 26052 次
回覆時引用此帖
有 3 位會員向 getter 送花:
a471 (2017-10-08),不飛 (2017-10-08),輕舞飛揚 (2017-10-10)
感謝您發表一篇好文章
舊 2017-10-09, 05:49 AM   #3 (permalink)
管理員
 
getter 的頭像
榮譽勳章
UID - 6433
在線等級: 級別:96 | 在線時長:9733小時 | 升級還需:64小時級別:96 | 在線時長:9733小時 | 升級還需:64小時級別:96 | 在線時長:9733小時 | 升級還需:64小時級別:96 | 在線時長:9733小時 | 升級還需:64小時級別:96 | 在線時長:9733小時 | 升級還需:64小時級別:96 | 在線時長:9733小時 | 升級還需:64小時
註冊日期: 2002-12-08
住址: 天線星球
文章: 8157
精華: 0
現金: 19955 金幣
資產: 765391 金幣
預設



另外一提,年資 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 負責「顯示 各年資的 個別天數」

此帖於 2017-10-10 02:22 AM 被 getter 編輯.
getter 目前離線  
送花文章: 37855, 收花文章: 6441 篇, 收花: 26052 次
回覆時引用此帖
有 2 位會員向 getter 送花:
a471 (2017-10-10),輕舞飛揚 (2017-10-10)
感謝您發表一篇好文章
舊 2017-10-10, 11:40 PM   #4 (permalink)
超級版主
 
輕舞飛揚 的頭像
榮譽勳章
UID - 222573
在線等級: 級別:35 | 在線時長:1401小時 | 升級還需:39小時級別:35 | 在線時長:1401小時 | 升級還需:39小時級別:35 | 在線時長:1401小時 | 升級還需:39小時級別:35 | 在線時長:1401小時 | 升級還需:39小時級別:35 | 在線時長:1401小時 | 升級還需:39小時級別:35 | 在線時長:1401小時 | 升級還需:39小時級別:35 | 在線時長:1401小時 | 升級還需:39小時級別:35 | 在線時長:1401小時 | 升級還需:39小時級別:35 | 在線時長:1401小時 | 升級還需:39小時級別:35 | 在線時長:1401小時 | 升級還需:39小時
註冊日期: 2006-01-27
住址: 心的牢寵
文章: 2445
現金: 11059 金幣
資產: 2882357 金幣
預設

謝謝getter大的解釋和幫忙,明天上班就來去驗證看看.
輕舞飛揚 目前離線  
送花文章: 340, 收花文章: 747 篇, 收花: 1809 次
回覆時引用此帖
舊 2017-10-11, 11:13 AM   #5 (permalink)
超級版主
 
輕舞飛揚 的頭像
榮譽勳章
UID - 222573
在線等級: 級別:35 | 在線時長:1401小時 | 升級還需:39小時級別:35 | 在線時長:1401小時 | 升級還需:39小時級別:35 | 在線時長:1401小時 | 升級還需:39小時級別:35 | 在線時長:1401小時 | 升級還需:39小時級別:35 | 在線時長:1401小時 | 升級還需:39小時級別:35 | 在線時長:1401小時 | 升級還需:39小時級別:35 | 在線時長:1401小時 | 升級還需:39小時級別:35 | 在線時長:1401小時 | 升級還需:39小時級別:35 | 在線時長:1401小時 | 升級還需:39小時級別:35 | 在線時長:1401小時 | 升級還需:39小時
註冊日期: 2006-01-27
住址: 心的牢寵
文章: 2445
現金: 11059 金幣
資產: 2882357 金幣
預設

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!
是我哪裡出錯了嗎?
輕舞飛揚 目前離線  
送花文章: 340, 收花文章: 747 篇, 收花: 1809 次
回覆時引用此帖
舊 2017-10-11, 04:03 PM   #6 (permalink)
管理員
 
getter 的頭像
榮譽勳章
UID - 6433
在線等級: 級別:96 | 在線時長:9733小時 | 升級還需:64小時級別:96 | 在線時長:9733小時 | 升級還需:64小時級別:96 | 在線時長:9733小時 | 升級還需:64小時級別:96 | 在線時長:9733小時 | 升級還需:64小時級別:96 | 在線時長:9733小時 | 升級還需:64小時級別:96 | 在線時長:9733小時 | 升級還需:64小時
註冊日期: 2002-12-08
住址: 天線星球
文章: 8157
精華: 0
現金: 19955 金幣
資產: 765391 金幣
預設

引用:
作者: 輕舞飛揚 查看文章
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!
是我哪裡出錯了嗎?
一種是括號、引數的錯誤 ... 另一種 迪西認為比較有可能的錯誤是

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 這個按鈕去驗證公式有沒有錯,或是哪裡有錯。

基本上整條公式,是下在那一個「可休天數」的那一格
getter 目前離線  
送花文章: 37855, 收花文章: 6441 篇, 收花: 26052 次
回覆時引用此帖
有 2 位會員向 getter 送花:
a471 (2017-10-12),輕舞飛揚 (2017-10-12)
感謝您發表一篇好文章
發文 回覆


主題工具
顯示模式

發表規則
不可以發文
不可以回覆主題
不可以上傳附加檔案
不可以編輯您的文章

論壇啟用 BB 語法
論壇啟用 表情符號
論壇啟用 [IMG] 語法
論壇禁用 HTML 語法
Trackbacks are 禁用
Pingbacks are 禁用
Refbacks are 禁用

相似的主題
主題 主題作者 討論區 回覆 最後發表
EXCEL的排序問題 finalwwc 一般電腦疑難討論區 2 2003-10-06 06:44 AM
Excel 底下的輸入法會自動變成全形? dalai 一般電腦疑難討論區 0 2003-10-05 05:05 AM
excel的工作表列印問題!! bojiun 軟體應用問題討論區 0 2003-09-14 09:55 PM
word的表格如何轉成excel 357753 軟體應用問題討論區 3 2003-09-08 11:48 PM
有關於excel使用上的問題! Dian 軟體應用問題討論區 4 2003-09-05 11:23 AM


所有時間均為台北時間。現在的時間是 10:30 PM


Powered by vBulletin® 版本 3.6.8
版權所有 ©2000 - 2024, Jelsoft Enterprises Ltd.


SEO by vBSEO 3.6.1