12
返回列表 上一主題 發帖

依條件選對應值

yeh6712

帖子
20 
主題
7 
精華
0 
積分
96 
點名
0  
作業系統
win7 
軟體版本
office 2013 
閱讀權限
20 
性別
男 
來自
來自台灣新北巿 
註冊時間
2010-7-5 
最後登錄
2020-4-24 
1# 跳轉到 » 倒序看帖
打印
tT
發表於 2014-4-8 23:03 | 只看該作者

依條件選對應值

如圖，如何在第1個工作表中，輸入仰臥起坐的次數，程式會根據C欄的男、女，
來判斷要找第2個工作表中的那一個對照表，再把相對應的分數，填在E欄中…感謝…

kun1.zip (11.92 KB)
收藏 分享

GBKEE

帖子
5923 
主題
13 
精華
1 
積分
5986 
點名
0  
作業系統
win10 
軟體版本
Office 2010 
閱讀權限
150 
性別
男 
來自
台灣基隆 
註冊時間
2010-5-1 
最後登錄
2022-1-23 

2017 雞年全勤獎章2016猴年全勤獎章2015羊年全勤獎章2014馬年全勤獎章2013蛇年全勤獎章2012龍年全勤獎章論壇貢獻勳章熱烈參與勳章2011兔年全勤獎章
2#
發表於 2014-4-9 06:58 | 只看該作者
回復 1# yeh6712
  1. Option Explicit
  2. Sub Ex()
  3.     Dim i As Integer, M As Variant, S As Integer, Rng As Range
  4.     i = 2
  5.     With Sheets("成績總表")
  6.         Do While .Cells(i, "C") <> ""
  7.             S = 0               '不是男女
  8.             If .Cells(i, "C") = "男" Then
  9.                 S = 1                'A欄
  10.             ElseIf .Cells(i, "C") = "女" Then
  11.                 S = 4                'D欄
  12.             End If
  13.             If S > 0 And .Cells(i, "D") > 0 Then   '條件: 有欄位 且 仰臥起坐次數 > 0
  14.                 If .Cells(i, "D") < Sheets("仰臥起坐").Columns(S).Cells(2, 1) Then
  15.                     M = Application.Match(.Cells(i, "D"), Sheets("仰臥起坐").Columns(S), 0)
  16.                 ElseIf .Cells(i, "D") >= Sheets("仰臥起坐").Columns(S).Cells(2, 1) Then
  17.                     M = 2  '100分
  18.                 End If
  19.                 If IsNumeric(M) Then .Cells(i, "E") = Sheets("仰臥起坐").Columns(S).Cells(M, 2)
  20.                 'Application.Match 工作表的函數:沒有找到時傳回 錯誤值               
  21.             End If
  22.             i = i + 1
  23.         Loop
  24.     End With
  25. End Sub
複製代碼
感恩的心......(在麻辣家族討論區.用心學習會有進步的)
但資源無限,後援有限,  一天1元的贊助,人人有能力.

TOP

yen956

帖子
522 
主題
36 
精華
1 
積分
603 
點名
0  
作業系統
win xp sp3 
軟體版本
Office 2003 
閱讀權限
50 
性別
男 
註冊時間
2012-12-13 
最後登錄
2021-7-11 
3#
發表於 2014-4-9 13:08 | 只看該作者
本帖最後由 yen956 於 2014-4-9 13:14 編輯

回復 1# yeh6712
1. 工作表 "仰臥起坐", (第2列)插入一列, 並各填 100,
這樣, 男生超過60次, 女生超過45次才能抓到分數
2. 定義名稱：
x=仰臥起坐!$A$2:$A$63
y=仰臥起坐!$D$2:$D$47
如下圖：

3. 工作表 "成績總表" , E2 公式：
=IF(C2="男",INDIRECT("仰臥起坐!B"&IF(C2="男",MATCH(D2,x,-1)+1,MATCH(D2,y,-1)+1)),INDIRECT("仰臥起坐!E"&IF(C2="男",MATCH(D2,x,-1)+1,MATCH(D2,y,-1)+1)))
向下拉
請參考!!
註：原附圖, 女生45次, 應為100分, 你誤填為男生的95分,
害我以為公式有問題, 查了半天才發現問題所在.

TOP

yeh6712

帖子
20 
主題
7 
精華
0 
積分
96 
點名
0  
作業系統
win7 
軟體版本
office 2013 
閱讀權限
20 
性別
男 
來自
來自台灣新北巿 
註冊時間
2010-7-5 
最後登錄
2020-4-24 
4#
發表於 2014-4-9 18:16 | 只看該作者
回復 3# yen956

不好意思原圖有誤…感謝您

TOP

yeh6712

帖子
20 
主題
7 
精華
0 
積分
96 
點名
0  
作業系統
win7 
軟體版本
office 2013 
閱讀權限
20 
性別
男 
來自
來自台灣新北巿 
註冊時間
2010-7-5 
最後登錄
2020-4-24 
5#
發表於 2014-4-9 18:17 | 只看該作者
本帖最後由 yeh6712 於 2014-4-9 18:23 編輯

回復 2# GBKEE

感謝大大的程式…了解…謝謝…！
目前是輸入次數後，再執行程式，才可顯示分數…
可否在輸入單筆次數後即顯示相對應的分數…？

TOP

yen956

帖子
522 
主題
36 
精華
1 
積分
603 
點名
0  
作業系統
win xp sp3 
軟體版本
Office 2003 
閱讀權限
50 
性別
男 
註冊時間
2012-12-13 
最後登錄
2021-7-11 
6#
發表於 2014-4-9 18:53 | 只看該作者
回復 5# yeh6712
抱歉, 考慮久周, 修正公式如下：
=IF($D2="","",IF($C2="男",INDIRECT("仰臥起坐!B"&IF($C2="男",MATCH($D2,x,-1)+1,MATCH($D2,y,-1)+1)),INDIRECT("仰臥起坐!E"&IF($C2="男",MATCH($D2,x,-1)+1,MATCH($D2,y,-1)+1))))
向下拉, 多拉一點沒關係
試過了, 應該沒問題

TOP

yeh6712

帖子
20 
主題
7 
精華
0 
積分
96 
點名
0  
作業系統
win7 
軟體版本
office 2013 
閱讀權限
20 
性別
男 
來自
來自台灣新北巿 
註冊時間
2010-7-5 
最後登錄
2020-4-24 
7#
發表於 2014-4-9 18:59 | 只看該作者
本帖最後由 yeh6712 於 2014-4-9 19:09 編輯

回復 6# yen956
感謝您快速的回答…
新公式OK了…謝謝…！

TOP

GBKEE

帖子
5923 
主題
13 
精華
1 
積分
5986 
點名
0  
作業系統
win10 
軟體版本
Office 2010 
閱讀權限
150 
性別
男 
來自
台灣基隆 
註冊時間
2010-5-1 
最後登錄
2022-1-23 

2017 雞年全勤獎章2016猴年全勤獎章2015羊年全勤獎章2014馬年全勤獎章2013蛇年全勤獎章2012龍年全勤獎章論壇貢獻勳章熱烈參與勳章2011兔年全勤獎章
8#
發表於 2014-4-9 20:41 | 只看該作者
回復 5# yeh6712
  1. Option Explicit
  2. '成績總表 工作表模組: 工作表上儲存格有修改的預設程式
  3. Private Sub Worksheet_Change(ByVal Target As Range)  'Target變數->工作表上有改變值的儲存格
  4.     Dim Rng As Range, S As Integer, T As Range
  5.     If Target.Column = 3 Or Target.Column = 4 Then 'C欄 或 D欄
  6.         Set T = Cells(Target.Row, "D")              'D欄
  7.         If (Cells(Target.Row, "C") = "男" Or Cells(Target.Row, "C") = "女") And IsNumeric(T) Then
  8.             With Sheets("仰臥起坐")
  9.                 If Cells(Target.Row, "C") = "男" Then
  10.                     Set Rng = .Range("A2:A" & .[A2].End(xlDown).Row)
  11.                 Else
  12.                     Set Rng = .Range("D2:D" & .[D2].End(xlDown).Row)
  13.                 End If
  14.             End With
  15.             If T >= Rng.Cells(1) Then
  16.                 T.Cells(1, 2) = Rng.Cells(1, 2)
  17.             ElseIf T < Rng.Cells(1) And T > 0 Then
  18.                 S = Rng.Cells(1) - T
  19.                 T.Cells(1, 2) = Rng.Cells(S + 1, 2)
  20.             Else
  21.                 T.Cells(1, 2) = ""    'E欄
  22.             End If
  23.         Else
  24.            If T.Row > 1 Then T.Cells(1, 2) = ""      ' E欄 列數>1
  25.         End If
  26.     End If
  27. End Sub
複製代碼
感恩的心......(在麻辣家族討論區.用心學習會有進步的)
但資源無限,後援有限,  一天1元的贊助,人人有能力.

TOP

yeh6712

帖子
20 
主題
7 
精華
0 
積分
96 
點名
0  
作業系統
win7 
軟體版本
office 2013 
閱讀權限
20 
性別
男 
來自
來自台灣新北巿 
註冊時間
2010-7-5 
最後登錄
2020-4-24 
9#
發表於 2014-4-9 22:30 | 只看該作者
本帖最後由 yeh6712 於 2014-4-9 22:37 編輯

回復 8# GBKEE

感謝…了解了…:)

工作表上儲存格有修改的預設程式→是不是儲存格必須有手動輸入才算，
預先用公式寫好，再自動算出來的就不算儲存格有修改過，是嗎？
例： 預設C1=A1+B1  ，A1輸入2 ， B1輸入3 ，則C1自動算出5
這時，儲存格C1就不算是有修改過嗎？

TOP

GBKEE

帖子
5923 
主題
13 
精華
1 
積分
5986 
點名
0  
作業系統
win10 
軟體版本
Office 2010 
閱讀權限
150 
性別
男 
來自
台灣基隆 
註冊時間
2010-5-1 
最後登錄
2022-1-23 

2017 雞年全勤獎章2016猴年全勤獎章2015羊年全勤獎章2014馬年全勤獎章2013蛇年全勤獎章2012龍年全勤獎章論壇貢獻勳章熱烈參與勳章2011兔年全勤獎章
10#
發表於 2014-4-10 06:16 | 只看該作者
本帖最後由 GBKEE 於 2014-4-10 06:17 編輯

回復 9# yeh6712
   
例： 預設C1=A1+B1  ，A1輸入2 ， B1輸入3 ，則C1自動算出5
這時，儲存格C1就不算是有修改過嗎

公式值的改變用重算事件
  1. Private Sub Worksheet_Calculate()
  2. MsgBox "A"
  3. End Sub
複製代碼
還有許多 工作表的觸動事件程序可用

感恩的心......(在麻辣家族討論區.用心學習會有進步的)
但資源無限,後援有限,  一天1元的贊助,人人有能力.

TOP

        靜思自在 : 世上有兩件事不能等：一、孝順 二、行善。
12
返回列表 上一主題