【Indirect函數】
INDIRECT函數
How to use the INDIRECT function to create references in Excel
利用indirect和data validation來做級聯菜單
Excel Data Validation -- Create Dependent Lists
Create Cascading Drop Down Menu Box List Using Data Validation in Excel
indirect用法
Excel INDIRECT Function（http://www.contextures.com）非常詳細
Excel INDIRECT Function Arguments
Lock a Cell Reference
Create a Reference from a Cell Value
Create a Reference from a Cell Value and Text
Create a Reference to a Different Sheet
Create a Reference to a Different Workbook
Refer to a Named Range
Dependent Data Validation Lists
【sumif&sumifs】
sumif符合兩個條件需要用數組公式即CSE
小楊用執行個體講解條件求和函數 SumIF 的詳細用法
Use a Special Excel Array Formula to Simulate SUMIF with Two Conditions
Excel Functions: Excel 2003 SUMIF() & Excel 2007 SUMIFS()
[多重合并資料透視表]
資料透視表之多表合并.xls
Excel資料透視表多表合并應用教程(下載下傳)
Create Excel Pivot Table from Multiple Sheets
用VBA進行多表合并計算一例
[按顔色排序]
在Excel2007中按單元格顔色進行排序
Excel技巧之按顔色排序或篩選
易寶典：Excel 2007 能不能按顔色對資料進行排序？
如何為EXCEL單元格顔色排序
在excel中依據顔色進行排序
http://www.excelperfect.com/ 完美Excel
http://wwww.excel123.com
http://www.excelhome.net
http://www.excelqq.com
http://www.exceltip.net
http://www.excelpx.com
http://www.excelforum.com
http://www.excelboost.com
http://www.mrexcel.com/
按指定的單元格顔色進行計數或求和
作者：未知 文章來源：未知 點選數： 3659 更新時間：2009-6-19 16:45:17
如果Excel工作表的某區域中包含不同的底紋顔色，我們可以用一個自定義函數對該區域按指定的單元格顔色進行計數或求和。方法是：
1.按Alt+F11，打開VBA編輯器。
2.單擊菜單“插入→子產品”，将插入名稱為“子產品1”的子產品，在右側的代碼視窗中輸入下列代碼：
Function SumByColor(Ref_color As Range, Sum_range As Range)
Application.Volatile
Dim iCol As Integer
Dim rCell As Range
iCol = Ref_color.Interior.ColorIndex
For Each rCell In Sum_range
If iCol = rCell.Interior.ColorIndex Then
SumByColor = SumByColor + rCell.Value
End If
Next rCell
End Function
Function CountByColor(Ref_color As Range, CountRange As Range)
For Each rCell In CountRange
CountByColor = CountByColor + 1
上述兩個自定義函數，一個是SumByColor，可以對區域按指定單元格的顔色求和。另一個是CountByColor，可以統計區域中某種顔色的個數。這兩個自定義函數都有兩個參數，前一個參數指定包含某種顔色的單元格，後一個參數為求和或計數區域。
3.關閉VBA編輯器。
使用方法：假如要求和或計數的區域在A1:B10區域中。
求出該區域中單元格底紋顔色為紅色的所有單元格數值之和，在單元格中輸入公式：
=sumByColor(A1,A1:B10)
求出該區域中單元格底紋顔色為紅色的所有單元格的個數，在單元格中輸入公式：
=CountByColor(A1,A1:B10)
Past Tip of the Day
Kári asks, I have a formula where I have to put in a criteria. The criteria is, that the formula has to gather numbers that are bigger than 0,5 (>0,5), but not bigger than 2 (<2). But how do I do that? I have tried: ">0,5"&"<2" and a lot of other combinations, but nothing works.
Kári: For a SUMIF or COUNTIF formula with 2 conditions, you need to use an array formula.
This type of formula is discussed here: http://www.mrexcel.com/tip031.shtml.
Since I wrote that article a few years ago, a better version of the formula has come to light. The web page discusses using this formula for a CountIf with 2 conditions:
=SUM(IF($C$2:$C$4403>0.5,IF($B$2:$B$4403<2,1,0),0))
You can use boolean logic instead to write this formula for CountIf
=SUM(($C$2:$C$4403>0.5)*($C$2:$C$4403<2)*1)
or this formula for SumIf:
=SUM(($C$2:$C$4403>0.5)*($C$2:$C$4403<2)*($C$2:$C$4403))
Remember, you must hold down the Ctrl and Shift keys then hit enter to enter these CSE or Array formulas.
This tip, and 276 others are in the best-selling book, Learn Excel from MrExcel. You can sign up to receive chapters from this book every Tuesday for free.