close
標題:

EXCEL 使用表格查找成績的方法 超難！

發問:

假設有位32歲、使用秒數為788秒的人，要自動查找他的成績為31歲那欄，少於790秒但大於785秒，則用790秒那列，查附表得知成績為94分，請問這樣查找成績的方法，該如何使用函數？附表：--------19----23----27----31----35----39 ... 顯示更多 假設有位32歲、使用秒數為788秒的人，要自動查找他的成績為31歲那欄，少於790秒但大於785秒，則用790秒那列，查附表得知成績為94分，請問這樣查找成績的方法，該如何使用函數？ 附表： --------19----23----27----31----35----39 (歲數) 100---700--720--740--760--780--800 99----705--725--745--765--785--805 98----710--730--750--770--790--810 97----715--735--755--775--795--815 96----720--740--760--780--800--820 95----725--745--765--785--805--825 94----730--750--770--790--810--830 93----735--755--775--795--815--835 92----740--760--780--800--820--840 91----745--765--785--805--825--845 90----750--770--790--810--830--850 (成績) (秒數) 更新: 感謝KK ( 大師 4 級 )的幫忙，但是若為31歲、790秒， 對應到的卻不是成績94，這樣該如何處理呢？謝謝您！ 更新 2: 感謝Daniel ( 大師 1 級 )與KK ( 大師 4 級 )的幫忙！ 你們真的很酷，我會去了解一下你們使用函數的邏輯，太感謝了！ 更新 3: 請問一下，使用CEILING(I2,5)是因為剛好附表的間隔是5單位，如果今天間隔不是固定而是亂跳的，那函數該如何改，又同時與秒數相同時，對應的成績也正確，請問該怎麼用呢？ 更新 4: 例如附表改成： 附表： --------19----23----27----31----35----39 (歲數) 100---701--722--741--763--785--800 99----702--727--743--765--786--803 98----713--737--754--770--790--811 97----714--738--755--775--795--817 96----725--744--764--784--804--821 95----726--747--765--786--805--825 (成績) (秒數) 這樣該如何呢？ 更新 5: 接上面：假設我要查的是32歲，使用秒數為774、775時的成績，這樣怎麼辦呢？謝謝教學！ 更新 6: 感謝KK ( 大師 4 級 )的幫忙，我想了很久，也查了很久，還是看不懂陣列公式， =MAX((B2:G12>=I2)*(B1:G1<=I1)*A2:A12) 可否稍微解釋提點一下我，這到底是什麼意思呢？太感謝了！

最佳解答:

aa.jpg

 

此文章來自奇摩知識+如有不便請留言告知

範例檔案 http://www.FunP.Net/490784 查詢表 A1:G12 I1 輸入歲數 I2 輸入使用秒數 I3 公式 =INDEX(A2:A12,MATCH(I2,OFFSET(B2,,MATCH(I1,B1:G1)-1,11))+1) 2012-06-16 09:22:00 補充： 借用 Daniel 大師 的技巧 I3公式 =LOOKUP(CEILING(I2,5),OFFSET(A:A,,MATCH(I1,B1:G1)),A:A) 2012-06-16 10:18:58 補充： 只細看一下這表示有規律，也可以用數學計算方式算出 =100-ROUNDUP((I2-(700+INT((I1-19)/4)*20))/5,) 2012-06-16 19:05:57 補充： 純查表方式，用001的資料TEST =LOOKUP(1,0/FREQUENCY(I2,OFFSET(A2:A12,,MATCH(I1,B1:G1))),A2:A12) 2012-06-16 22:37:01 補充： 012貼錯公式 I3 陣列公式 =MAX((B2:G12>=I2)*(B1:G1<=I1)*A2:A12) 2012-06-17 08:27:41 補充： 補充說明 I3 =MAX((B2:G12>=I2)*(B1:G1<=I1)*A2:A12) 不能用ENTER輸入 需採用陣列輸入，需CTRL + SHIFT + ENTER 三鍵齊按輸入 研究說明 1.全選 B22:G32 2.輸入 =(B2:G12>=I2)*(B1:G1<=I1)*A2:A12 3.陣列輸入 可以看看MAX函數前的數值狀況 當然可以逐步輸入研究 =(B2:G12>=I2) =(B2:G12>=I2)*(B1:G1<=I1) =(B2:G12>=I2)*(B1:G1<=I1)*A2:A12 比較看看 2012-06-19 19:51:45 補充： 本題查表方式可以採用下列方式，還有其他方式或 Daniel大師的精解詳意見區 A1:G12 查詢表 I1 輸入歲數 I2 輸入使用秒數 I3 公式如下(查表方式) 一般公式 =LOOKUP(1,0/FREQUENCY(I2,OFFSET(A2:A12,,MATCH(I1,B1:G1))),A2:A12) 陣列公式 =MAX((B2:G12>=I2)*(B1:G1<=I1)*A2:A12) 不能用ENTER輸入 需採用陣列輸入，需CTRL + SHIFT + ENTER 三鍵齊按輸入 2012-06-21 12:04:41 補充： 研究說明 1.全選 B22:G32 2.輸入 =(B2:G12>=I2)*(B1:G1<=I1)*A2:A12 3.陣列輸入 可以看看MAX函數前的數值狀況 當然可以逐步輸入研究 =(B2:G12>=I2) =(B2:G12>=I2)*(B1:G1<=I1) =(B2:G12>=I2)*(B1:G1<=I1)*A2:A12 比較看看

其他解答:

所以如果你想婚的念頭已經確定 就去找一家評價好一點的婚友社! 行動吧! 這是我之前參加的婚友社我覺得不錯 或搜尋 紅娘李姐|||||台灣第一家合法博弈娛樂城熱烈開幕! 運動彩、遊戲對戰、現場百家樂、多國彩球 高賠率投注，歡迎您免費體驗! 官方網站 ss777.net|||||如果I2輸入整數，如780，應在96，但K大結果為95，建議I3改為: =INDEX(A:A,MATCH(CEILING(I2,5),OFFSET(A:A,,MATCH(I1,B1:G1)))) . 2012-06-16 14:08:40 補充： 依005式整理後可得: =240+INT((I1-19)/4)*4-ROUNDUP(I2,)/5 2012-06-16 14:17:10 補充： 可再短一點: =220+INT((I1+1)/4)*4-ROUNDUP(I2,)/5 2012-06-16 14:20:04 補充： 更正，/5要在ROUNDUP裡面，改為: =220+INT((I1+1)/4)*4-ROUNDUP(I2/5,) 2012-06-16 14:34:57 補充： 如考慮到表裡查不到的數值，最低90分，最高100分，則: =MAX(MIN(220+INT((I1+1)/4)*4-ROUNDUP(I2/5,),100),90) 2012-06-16 21:30:20 補充： I3陣列公式: =MAX(IF(I2<=OFFSET(A2:A12,,MATCH(I1,B1:G1)),A2:A12))BFC66BE0445C3814
arrow
arrow
    文章標籤
    公式 更多 成績也 附表 解一下
    全站熱搜

    ddhdxb5 發表在 痞客邦 留言(0) 人氣()

    E-mail轉寄