Board logo

標題: 公式與基本技巧演練 [打印本頁]
作者: oobird    時間: 2010-11-29 22:31     標題: 公式與基本技巧演練

下了個資料檔，可拿來練習公式與基本技巧。適合新手練習用！
[attach]3835[/attach]
作者: ANGELA    時間: 2010-12-2 12:30

回復 6# oobird

A2=OFFSET(成績!A$1,(ROW()-2)*9+1,)&""
C2=IF(B2="","",TEXT(OFFSET(成績!$D$1,(ROW()-2)*9+COLUMN(A1),)-65,"[>=20]A;C;!B"))
作者: oobird    時間: 2010-12-2 14:30

ANGELA的函數造詣相當高，很輕易的解決這個問題。
雖然我的原意是想請大家往基本操作的方向思考。不過不管用什麼方法，最終目的還是為了解決問題的，解出來就是好答案！
作者: gong    時間: 2010-12-2 15:00

本帖最後由 gong 於 2010-12-2 15:09 編輯

樞紐+格式自訂[>=85]!A;[<65]C;B
作者: oobird    時間: 2010-12-2 15:24

龔版主抓住我的目的了。以基礎操作達成這種複雜的運算。比我做的簡單了好幾個步驟！
可以再思考一下如何取得等級ABC的值而不是數字的自訂格式！
作者: emma    時間: 2010-12-2 16:53

本帖最後由 emma 於 2010-12-2 17:00 編輯

向ANGELA大大與gong版主的方法學習中，
如果以我自己本來的方法，可能比較耗費系統的資源，
我原本是先使用樞紐，再將其欄位複制，
將分數的欄位用公式=IF(ISNA(VLOOKUP($I2&K$1,$A:$E,5,0)),"",IF(VLOOKUP($I2&K$1,$A:$E,5,0)>=85,"A",IF(VLOOKUP($I2&K$1,$A:$E,5,0)>=65,"B","C")))
先向右拉再往下複製....
如下圖所示，方法笨重了些>"<

不好意思，剛忘了提我還有多一欄「輔助欄」(後補上)
[attach]3886[/attach]
[attach]3885[/attach]
作者: emma    時間: 2010-12-2 17:23

本帖最後由 emma 於 2010-12-2 17:29 編輯
樞紐+格式自訂[>=85]!A;[
gong 發表於 2010-12-2 15:00



gong版主，不好意思，剛在依您的方法練習時，
發現您文上貼的格式自訂[>=85]!A;[<65]C;B少了一個!號，
所以一直無法自訂格式，
後來下載您的附件查看後，正常的格式自式是[>=85]!A;[<65]C;!B
照這個輸入後就沒問題了，謝謝~~

另外可以請您解釋一下!的用意嗎?
A跟B前面要有!
但C前面卻不用!
為什麼呢?
作者: gong    時間: 2010-12-2 18:00

本帖最後由 gong 於 2010-12-3 21:30 編輯

1.抱歉我幫不上忙,因為我也不知道,它是自己產生的
2.我的B前面不需要"!"(OFFICE 2000版不用,XP版B的前面開啟會自動產生"!")
3.如果將"A"改為其他英文也不需要"!",如[>=85]Z;[<65]C;B
4[紅色][>=85]Z;[綠色][<65]X;B,
  [紅色][>=85]Z;[綠色][<65]X;[黃色]B,這樣也不用,
5.試了"D"是不行用的,"A"前面會自動產生"!"

原因仍待其他高手解答
作者: oobird    時間: 2010-12-2 21:02

如D、E、G等保留給日期用的字母自然都不能用。
A會自動加上驚嘆號，而B要自己加，不加就通不過(我也搞不懂為什麼)
通常設定時會用雙引號把字母括起來就沒這些問題了！
作者: gong    時間: 2010-12-2 21:41

我的B可以不用加"!"

M是月份
Y是年
應該都不能用
作者: Hsieh    時間: 2010-12-2 21:49

B原則上也算保留字
若輸入B，EXCEL會自動改成bb
顯示數值以1900年為43起算的年度
1900年日期顯示成43
1901年日期顯示成44
以此類推
加上驚嘆號!或反斜線\或用雙引號""
都代表使用字元
只是反斜線會被EXCEL自動轉成驚嘆號
作者: oobird    時間: 2010-12-3 00:05

把我做的過程PO上來。
[attach]3896[/attach]
作者: gong    時間: 2010-12-3 21:58

本帖最後由 gong 於 2010-12-3 22:00 編輯

本也想用輔助欄,但無法達成直接取得值
另種方式也提供參考
作者: fr447446    時間: 2010-12-14 10:04

回復 18# gong


    GONG大：
我在第三張圖要改成第四張圖的時候要輸入 =E$5 的步驟時
出現，"您無法編輯此頁、此欄、此列中的欄位儲存格"，這樣的錯誤訊息，
不知道GONG大是如何辦到的
作者: gong    時間: 2010-12-15 09:50

=E$5
如果可以輸入
不是按"ENTER"

要按"CTRL+ENTER"
作者: fr447446    時間: 2010-12-15 11:14

嗯~又學到了一個特殊的用法，
受教了~
作者: FAlonso    時間: 2011-3-16 22:16

回復 1# oobird
做了VBA程式,不過等候時間較長,有時程式會自動中斷+有顯示錯誤,所以不要濫用VBA
  1. Option Explicit
  2. Sub table()
  3. Dim classnumber As Object, mycell As Range
  4. Set classnumber = CreateObject("scripting.dictionary")

  6. With Range("S1")
  7. .Value = "考生號"
  8. .Offset(, 1).Value = "姓名"
  9. .Offset(, 2).Value = "化學"
  10. .Offset(, 3).Value = "外語"
  11. .Offset(, 4).Value = "生物"
  12. .Offset(, 5).Value = "地理"
  13. .Offset(, 6).Value = "物理"
  14. .Offset(, 7).Value = "政治"
  15. .Offset(, 8).Value = "語文"
  16. .Offset(, 9).Value = "數學"
  17. .Offset(, 10).Value = "歷史"
  18. End With

  20. Range("S2").Activate

  22. For Each mycell In Sheet1.Range([a2], [a65536].End(xlUp))
  23. If classnumber.exists(mycell.Value) Then
  24. Else
  25. classnumber.Add mycell.Value, mycell.Value
  26. ActiveCell.Value = mycell.Value
  27. ActiveCell.Offset(, 1).Value = mycell.Offset(, 1).Value
  28. ActiveCell.Offset(, 2).Value = mycell.Offset(, 3).Value
  29. ActiveCell.Offset(, 3).Value = mycell.Offset(1, 3).Value
  30. ActiveCell.Offset(, 4).Value = mycell.Offset(2, 3).Value
  31. ActiveCell.Offset(, 5).Value = mycell.Offset(3, 3).Value
  32. ActiveCell.Offset(, 6).Value = mycell.Offset(4, 3).Value
  33. ActiveCell.Offset(, 7).Value = mycell.Offset(5, 3).Value
  34. ActiveCell.Offset(, 8).Value = mycell.Offset(6, 3).Value
  35. ActiveCell.Offset(, 9).Value = mycell.Offset(7, 3).Value
  36. ActiveCell.Offset(, 10).Value = mycell.Offset(8, 3).Value
  37. ActiveCell.Offset(1).Activate
  38. End If
  39. Next

  41. For Each mycell In Range("U2:Ac643")
  42. If mycell.Value >= 85 Then
  43. mycell.Value = "A"
  44. ElseIf mycell.Value >= 65 And mycell.Value < 85 Then
  45. mycell.Value = "B"
  46. ElseIf mycell.Value < 65 Then
  47. mycell.Value = "C"
  48. End If
  49. Next

  51. Columns("S").NumberFormat = "0"
  52. Columns("S").AutoFit

  54. Set classnumber = Nothing

  56. End Sub
複製代碼
不過呢,可否降低下載附件的門檻,看那麼多無意義的回帖
作者: Hsieh    時間: 2011-3-17 08:36

本帖最後由 Hsieh 於 2011-3-17 08:48 編輯

回復 39# FAlonso
這篇重點在於基本技巧演練，並不鼓勵使用VBA或函數解題
既然兄台使用VBA來做，而且您也提到一些VBA使用上的困擾
底下程式碼供您參考看看
您會發生錯誤原因可能發生在
dictionary物件加入項目的方法
您使用了add方法來加入項目
dictionary物件有一個特性，就是索引值不能重覆
當add方法加入索引時，若該索引值已經存在就會產生錯誤
避免此情況發生，可用exists方法先判斷索引值是否存在，然後再決定是否加入
在您的流程中dictionary物件是用不到的
而底下程式碼利用的方法是直接指定索引值對應的值
這樣，值會改變，但不會有重複索引疑慮
再則您提到速度問題
加快速度的方法因個案各有不同
以本題為例，首先是將資料範圍以陣列型態讀取
直接靠記憶體讀寫，會比一格一格的讀寫快很多
在來是當你要指定儲存格時，要儘量減少select的使用
直接指定欄列位也會使速度加快
減少不必要的循環與動作都是加速的方法
您可以自己試試看
  1. Sub Ex()
  2. Dim d As Object, d1 As Object, d2 As Object, Ar(), i&, A As Range, C As Range
  3. Set d = CreateObject("Scripting.Dictionary")
  4. Set d1 = CreateObject("Scripting.Dictionary")
  5. Set d2 = CreateObject("Scripting.Dictionary")
  6. d2("考生號") = "姓名"
  7. Ar = Range([A1], [A65536].End(xlUp).Offset(, 3))
  8. For i = 2 To UBound(Ar, 1)
  9.    d(Ar(i, 1) & Ar(i, 3)) = IIf(Ar(i, 4) < 65, "C", IIf(Ar(i, 4) < 85, "B", "A"))
  10.    d1(Ar(i, 3)) = ""
  11.    d2("'" & Ar(i, 1)) = Ar(i, 2)
  12. Next
  13. [G:Q].ClearContents
  14. Application.ScreenUpdating = False
  15. [G1].Resize(d2.Count, 1) = Application.Transpose(d2.keys)
  16. [H1].Resize(d2.Count, 1) = Application.Transpose(d2.items)
  17. [I1].Resize(, d1.Count) = d1.keys
  18. For Each A In Range([I1], [I1].End(xlToRight))
  19.    For Each C In Range([G2], [G65536].End(xlUp))
  20.       Cells(C.Row, A.Column) = d(C & A)
  21.    Next
  22. Next
  23. End Sub
複製代碼
作者: FAlonso    時間: 2011-3-17 12:41

回復 40# Hsieh
多謝H大指點

我很喜歡pivot table那個解決辦法,快捷得很(還有是怎樣錄製影片的?)
作者: zeny    時間: 2011-8-12 20:16     標題: 很惱人， 有個43分的格式自定，就是轉換不成C

[attach]7398[/attach]

很惱人， 有個43分的格式自定，就是轉換不成C
作者: Hsieh    時間: 2011-8-12 22:08

回復 20# zeny
這些儲存格內容都變成了日期
這是你使用bb的緣故
自訂格式改成
[>=85]!A;[>=65]!B;C
作者: zeny    時間: 2011-8-14 13:21     標題: 經大指正，知道自己錯在了哪裡

本帖最後由 zeny 於 2011-8-14 13:26 編輯
回復  zeny
這些儲存格內容都變成了日期
這是你使用bb的緣故
自訂格式改成
[>=85]!A;[>=65]!B;C
Hsieh 發表於 2011-8-12 22:08



   是的，是的，我沒有注意到大大之前寫的
B原則上也算保留字
若輸入B，EXCEL會自動改成bb
顯示數值以1900年為43起算的年度
1900年日期顯示成43
1901年日期顯示成44
以此類推
加上驚嘆號!或反斜線\或用雙引號""
都代表使用字元
只是反斜線會被EXCEL自動轉成驚嘆號

現在已經成功了。
真是越學，越覺得自己淺薄
謝謝大大。
[attach]7447[/attach]
作者: ky2599y    時間: 2011-8-16 18:13

樞紐→將格子複製到右邊→輸入公式
    IF(C5<65,"C",IF(C5<85,"B","A"))
作者: ky2599y    時間: 2011-8-16 18:23

本帖最後由 ky2599y 於 2011-8-16 21:19 編輯

樞紐→直接到樞紐表裡去設儲存格格式
作者: jeff2266    時間: 2012-3-31 17:20

感謝你的分享



歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)