[Excel] 陣列公式進階應用 !?

[leonchou]

大家好∼ 最近又開始覺得寒氣逼人..

嗯~ 上次提到計算範圍字數的陣列公式，算是初步認識了..
但是陣列公式絕非如此而已，請看以下應用範例：

　　　Ａ欄　　Ｂ欄　Ｃ欄　Ｄ欄　Ｅ欄　Ｆ欄　Ｇ欄
－－－－－－－－－－－－－－－－－－－－－－－－
１｜認證編號　ＡＡ　ＡＢ　ＡＣ　ＢＡ　ＢＢ　ＢＣ
２｜Ａ０１　　　１　　１　　　　　１
３｜Ａ０２　　　１　　　　　　　　１　　１
４｜Ａ０３　　　１　　　　　　　　　　　１
５｜Ａ０４　　　　　　１　　１　　１　　　　　１

　　　　　　　ＢＡ　ＢＢ　ＢＣ
８　ＡＡ　　　　２　　２　　０
９　ＡＢ　　　　２　　０　　１
１０ＡＣ　　　　１　　０　　１

上表 A1：G5 有兩組產品，分別是ＡＡ,ＡＢ,ＡＣ為一組，ＢＡ,ＢＢ,ＢＣ為一組，每個產品可能有數個認證編號，例如ＡＡ的認證編號有Ａ０１、Ａ０２、Ａ０３。
現在想把兩組產品對應到同一個編號的筆數統計出來，人工計算結果如 A7：D10 . 例如ＡＡ和ＢＡ都有Ａ０１,Ａ０２編號，所以計算結果為２，ＡＣ和ＢＣ都有Ａ０４編號，計算結果為１.

B8：D10 共９個計算結果，要一個公式算出一個結果，除了[陣列公式]沒有其他辦法。
問題如下：
１.如何使用 [陣列公式] 計算出 B8：D10 的結果？
　(提示: 例如 B8 -- 如果ＡＡ=１且ＢＡ=１則算１點)
２.如何在 B8：D10 每個數字上查詢符合的認證編號有哪幾個？
　(例如選取 B8 則得知符合的編號有Ａ０１及Ａ０２)

你們不覺得冷風刺骨嗎？請大家一起再來動動腦熱鬧一下吧！
　

[蘇才峰]

這個問題，我是大致想到一個方法，我把我的思考邏輯寫一下，有勞Leon Chou指點一下：
1）九個計算結果，原理大致一樣，所以就先挑出AA和BA這兩個產品，來思考公示。

2）在AA和BA的認證方面，我發現兩者都有相同的證實時，恰巧乘積為"一"，如果遇到沒有的認證，因為欄位是空白的，所以相乘的結果會是零。

3）只要把AA和BA這兩欄相乘的結果加總起來，其數字就是擁有相同認證的個數。所以我使用了下面這個公示：
{=SUM((B2:B5)*(E2:E5))}

4）其餘同理類推，以AB和BA來說，其公式就是：
{=SUM((C2:C5)*(E2:E5))}

以上就是我想破頭所想出來的，但是這個公式有幾個缺點：

A）那九個計算結果的公式都不一樣（雖然長的都很像），與題目所說的一個公式用在九個地方不合
==>如果是這樣，嘻嘻，還望請Leon Chou先不要公布答案，我再想想看.......

B）利用相乘結果為一或零，是恰巧此題在沒有認證的儲存格式空白，如果在沒有認證的地方是"無"，那就慘了，公式結果會出現錯誤。

C）題目如果A系列產品的認證只到A04，B系列產品的認證比較多，達到A05，那這種不對稱我也沒輒，必須把A系列的認證延伸到A05，然後A系列的產品在A05這方面通通是空白。（也許是我想太多了）

總之，第一個問題，我想的答案就是這樣，至於第二個，我想了很久，還是想不出來，我想大致可能跟"備註欄"有關......

其他網友有想法嗎？

[leonchou]

才兄.. 謝謝您再次的支持~~~　^___^~~

好厲害~~ 每次才兄的見解都能帶給我不同的啟發..
嗯.. 看了您的回應，先來表達一下小弟的想法~

1.針對這題目來說，使用 [乘積加總] 的算法確實令人佩服！
　但它如您所說，的確是 "恰巧"~~ ^__^　..如果表格裡不是
　用 1 來計算認證，而是用其他文字標記呢...？
　當然啦，可以說這方法不夠嚴謹~~ 也可以說小弟吹毛求疵..
　畢竟想太多也不好，題目怎麼出怎麼答就好了阿 ~~~

2.請看清楚題目唷~~ 它是說 "一個公式一個結果"，而不是
　"一個公式九個結果" 喔 ~~ ..那您也太強了吧
　...其實 [陣列公式] 是可以做到 "一公式多結果" -- 確實
　是 "一個公式" 多結果，而非 "一種公式" 多結果 ~~
　ㄝ.. 有沒有看過九個儲存格裡的公式都一模一樣，但結果卻都
　不同呢？這個就是陣列公式的厲害之處了.. ^+++^
　但這種陣列公式不是應用在這裡... 以後有機會再來探討吧。
　所以呢，就請才兄不需往那方面想了 ^^

3.再來個提示：只要在 B8 設一個公式，再複製到其他 8 格。
　換句話說，這關係到公式的 [相對絕對] 參照的應用。
　而且在用公式之前，必需事先進行一個小小的手續。
　-- 這也就是 "一種公式多結果" 。　而才兄的方法也不差，
　運用上述的參照方式，設三個公式 (B8:B10) 再往右複製即可~~~

4.關於第二小題.. 算是一個附題 -- 它沒辦法用公式做出來。
　需要用 VBA 來做 -- 有 [自動篩選] 和 [自動註解] 兩種
　方式。只是想順便來討論一下 "巨集" 的小應用...

加油喔∼　大家也一起來活絡一下腦細胞ㄅ~~~

[蘇才峰]

喔喔喔喔，喔喔喔喔.......我解出來了，呵呵呵呵......
請問，答案是不是：

{ =sum( if( (b2:b5)=1 , if((e2:e5)=1,1) ) ) }

上面我用空白隔開，以便大家方便閱讀，此外，在解釋上面的邏輯時，先介紹一下IF函數的用法（這是EXECL一個非常重要的函數喔，詳情可到HELP中查閱一下）：

IF（ 判斷式 ， "判斷式為真"的動作 ， "判斷式為假"的動作 ）

上面三個參數中：
1）如果判斷式為真，那麼就會執行第二個參數，如果判斷式為假，那麼就會執行第三個參數
2）第二、三個參數，可以是數值，或者是另一個函數。
3）第三個參數可以省略，就是有沒有都沒關係。如果省略，遇到判斷式為假的狀況，就什麼事都不做而已。

解釋IF函數後，皆下來就解釋我的公式囉：
1）先把問題簡單化，拿出AA和BA這兩欄來比較。
2）在B2這個儲存格用IF函數判斷，如果B2不等於1，那就什麼事都不做（我把第三個參數省略了），如果B2＝1，那......
3）再看看是否E2＝1，如果為真，則給定一個值[1]，如果E2不等於1，那也是當作什麼事也沒發生。
4）當然，從B2到B5以及E2到E5都要判斷，所以我使用了B2：B5和E2：E5。
5）最後當然是使用陣列公式和SUM函數，把列2到列5經由判斷後的結果，加總起來。
6）其他同理類推，當然，你用滑鼠拖曳過去也可以。


我在這裡贅言地解釋一大堆，尤其是IF函數，實在有點像是關公門前耍大刀，希望各位高手不要見笑囉......，上星期我人都在外地四處遊蕩，所以都沒有上PCZONE，等到我星期六在PCZONE四處晃晃，竟然看到有一題動動腦題目，孤獨的躺在好下面喔，都沒有人回應，不過我想，這可能是由於這個動動腦的題目有點門檻，對一些初學者可能會霧煞煞，所以就詳細的把我的思考過程寫出來，希望可以讓多一點人瞭解，也希望大家在瞭解EXECL的公式應用後，能加入討論，嘻嘻，大家討論討論，我的腦細胞才不會死得太多......

喔，對了，關於第二個問題，請Leon Chou再......給個一天吧（唉，以前拖作業習慣的後遺症...*_*），我今天有空的時候都在想第一個問題，一想到解答，就高興的跑來POST，呵呵呵呵，真的很奇怪，現在知道答案了，就覺得自己怎麼會想了一天才想到......

[aztec]

按照周大師提示三，作起來就比較簡單了
小弟原本想說只能一格作好其他通通用拉的不能再修改
土法鍊鋼如下，讓大家笑一下，有夠爛的公式

{=SUM(IF(IF(ROW()=8,$B$2:$B$5=1,IF(ROW()=9,$C$2:$C$5=1,$D$2:$D$5=1)),IF(IF(COLUMN()=2,$E$2:$E$5=1,IF(COLUMN()=3,$F$2:$F$5=1,$G$2:$G$5=1)),1,0),0))}

另提供一非陣列作法，也是一格拉到底
=SUMIF(OFFSET($A$2:$A$5,0,ROW()-7,,),1,E$2:E$5)

[蘇才峰]

耶！這也是一種解答........嘻嘻，又看到兩種方式了，感謝你的加入討論。

你的公式大意上和我的差不多，使用的函數不一樣，正所謂條條大路通羅馬，不過當初一看，蠻長的公式，腦筋差點反應不過來，唉，腦袋被考試弄得蠻僵硬的......

不過我還是沒有想出來第二個問題，在使用巨集方面，我蠻弱的，因為我都使用錄製巨集而已，邏輯知道，但一些VB的語法卻忘光了，關於這點，你有何高見？

不然就只好請Leon Chou來給我們解惑一下囉........希望束脩便宜一點........^_^

[bronc]

大家好ㄚ~~

首先我要聲明我不是來解題的，不是我藏私，而是我的功力還不足。

那各位大概會問我是來幹啥的，請容我說明一二：

首先我是為了表達對各位的欽佩，leon chou 兄費心的思考題目，蘇才峰兄及其他諸位用心的討論解題，最後還仔細的將正確的方法完整的述說，對於功力尚淺的同好真是一個最好的學習，我就是蒙受諸位好意教導中的一個。

前些天，承蒙leon chou兄的指導。我把公司每日的報表改良得更加好用了。我對於excel有著一份喜愛，可是公司運用的只是粗淺的技巧。書上學到的因為沒得應用，加上記憶退化，沒多久就忘得差不多了。因此一直苦於無法提昇自己excel的功力。看到各位在此的討論，讓我是獲益非淺。

基於這些理由，雖然明知自己還沒能有所貢獻，我甘冒不受歡迎的風險，向各位表達我的感受及感激，請各位體諒我的唐突！也祝各位功力更精進←夠狗腿吧...哈...

[leonchou]

ㄟ.. 果然一起討論　可以學得更多　

To aztec :
別這麼稱呼嘛~ 害偶直冒冷汗 ^^|||　大家都是一起來討論呀~~　I just feel cold ^^　貼個題目給大家玩玩　又不是教學~~~　不然只有自己在兜圈子　老以為自己的方法才是標準答案..　現在才發現自己的冷汗已經流滿地了　慚愧慚愧
你的答案令我豁然開竅　因為我很少用到 OFFSET　原來這麼好用ㄚ
希望以後可以常看到你　一起來討論ㄡ


To 才兄 :
一直想回應你的解答　可是剛好遇上PCZONE施工...
想問的是　你的公式真的可以複製到其他格嗎 ?　不用加＄符號嗎 ??　能不能指導一下~~

另外，陣列公式裡面大都用 IF 判斷，大家有沒有覺得如果條件一多，那不就一大串 ? 而且一個公式裡最多只能 7 個 IF..　可是，若要用邏輯運算子 (AND、OR) 在陣列公式裡又行不通~

沒關係 -- 在陣列公式裡面，AND、OR 可以用 * 和 + 代替 !!
以才兄的公式而言，原來應該是這樣：

　{=SUM(IF(($B2:$B5)=1,IF((E2:E5)=1,1)))}

使用 * 號只要在條件的最外層用一個 IF 即可：

　{=SUM(IF(($B2:$B5=1)*(E2:E5=1),1))}

條件一多，就能明顯看出後者的優點~~　But 這公式僅能往右複製，不能往下，需分別設定 B8、B9、B10 三個才行...


To bronc
您言重了~~ 啥麼"甘冒不受歡迎的風險"，真是..　^^|||
有誰規定只有解題的人才能發言ㄇ ??　這是一個討論的園地　有心得的人分享　有疑惑的人發問　就算沒有解題沒有疑問　也可以表達感想ㄚ　再說發言的人也需要有人回應　才會更來勁不是嗎
就是要這樣　討論區才會熱鬧　大家也可以互相學習求進步嘛~~

說真的　看到 PCZONE 的寬頻/網路討論區一頭熱　自己較感興趣的討論區卻冷清清　我有點不甘心　>_<"　其實很多人也常應用到 Office 不是嗎 ??　為何發問、討論的人卻不多 --
我想是因為氣氛的關係　久而久之大家就不來這裡而跑去其他地方問了~~~
尤其現在 PCZONE 的環境介面變得這麼棒..　相形之下真的很可惜　討論區熱鬧程度實在相差太大了~~
希望更多朋友能來這裡　讓它熱翻天 ^__^　在最好的 PCZONE 得到最多的收穫~~~


咦.. 我怎麼越說越像板主或站長的口氣???　這這這... 太激動了點
ㄝ..... 今天剛回苦候多時的 PCZONE，扯的太多了些 --
關於我對這題的解法~~ 包括兩個小題... 留待下回再說吧　拍寫喔 ^___^

[aztec]

第二題我挑比較簡單的來作
反正巨集錄製一下，然後再東拼西湊 ^_^

說實在話，我原本只想到註解的部份，但不會作咩
還好大師有指點也可以用自動篩選

所以挑自動篩選的功能，先作一按鈕，另建下列巨集
此巨集須被設定於按鈕被點按執行，由於不知道如何讓
儲存格被選取就自動執行，所以只好另設一按鈕，先選
取儲存格後再點按該按鈕，上頭的資料就會自動篩選

Sub 自動篩選應用()
x = ActiveCell.Row
y = ActiveCell.Column
Range("A1").Select
Selection.AutoFilter
If x > 7 And x < 11 Then
If y > 1 And y < 5 Then
Selection.AutoFilter Field:=x - 6, Criteria1:="1"
Selection.AutoFilter Field:=y + 3, Criteria1:="1"
End If
End If
End Sub


峰哥的學習精神實在令人敬佩，bronc，既然您這麼說，改天請周大師
找一些簡單的題目讓你解，好膽嘜造

[蘇才峰]

首先，我要承認......人...不是我殺的......不是不是啦，我是說，要加上＄才對，沒有電腦時，我都是在紙上思考的，想到解答時，又在別的欄位驗證，結果也對，一時高興，趕快找台電腦上網POST，就沒考慮拖曳（絕對、相對關係）的問題了，真是對不起大家，若有誤導各位網友，那...大家可以....放狗來咬我......

第二題的解法，我在前幾天放棄了，我對EXECL的巨集真的不太熟悉，不過aztec真是厲害，又給你解對了一題...，不過我想出題者的原意如您所說的，在選取儲存格時，就要顯示相關資訊。MS的軟體，例如按鈕，游標移過去，停住幾秒鐘就會顯示一些資訊，這都是在物件的屬性或事件中，可以設定或寫寫程式碼的。我是朝著這方面去思考的，但是無奈EXECL的HELP沒有給我多大的幫助，只好等Leon Chou來說說囉......

對了，往前翻翻POST，還有兩題動動腦題目喔，一題是陣列公式的使用，我就是從這裡開始認識陣列公式的，另一題是公車班次的問題，兩題都很有意思，bronc網友，您可以去參考看看，而我也要請請周大師找一些簡單的題目讓我解，下一次大家在一起來廝殺......

好膽再來........^_^