【求助】EXCEL公式問題！！

**joe1025** · 2005-01-05, 05:00 PM

請教一個問題
這是我的來源檔

這是目的檔

現在是以"採購編號"為主,作VLOOKUP到目的檔
現在小弟遇到的問題是
以採購編號為主！但是我同一個採購編號有三樣物品
如何能在目的檔裡面出現我要的三個品名呢？
公式如何寫？

再次感謝大家的幫忙,感激不盡^^

**leonchou** · 2005-01-05, 10:51 PM

設
1. 請購單號 = 來源檔請購單號(B欄)範圍
2. 品名 = 來源檔品名(J欄)範圍

則目的檔 C9 陣列公式為
=IF(ROW()-8>COUNTIF(請購單號,C$6),"",INDEX(品名,SMALL(IF(請購單號=C$6,ROW(請購單號)-1),ROW()-8)))
公式往下複製.

**joe1025** · 2005-01-06, 04:25 PM

作者：leonchou

設
1. 請購單號 = 來源檔請購單號(B欄)範圍
2. 品名 = 來源檔品名(J欄)範圍

則目的檔 C9 陣列公式為
=IF(ROW()-8>COUNTIF(請購單號,C$6),"",INDEX(品名,SMALL(IF(請購單號=C$6,ROW(請購單號)-1),ROW()-8)))
公式往下複製.

不好意思
很想靈活運用,公式有點看不是很懂︿︿｜｜
貼上去不會修改

我把檔案上傳（無毒）,可以請大大幫忙嗎？謝謝

如果有點麻煩！可以先不要解釋（上面的問題比較重要^^||）,有空再解釋就可以了（以這個例子作解釋也可以）,再次感謝阿^_^

先把OFFICE裡面解釋的語法貼上來跟大家分享

ROW(reference)
Reference 是希望知道列號的單一儲存格或儲存格範圍。

如果 reference 被省略，則 ROW 函數會引用本身的儲存格位址。

如果 reference 為一個儲存格範圍，而且 ROW 函數也以垂直陣列格式輸入，

則 ROW 函數會以垂直陣列的方式傳回各橫列之列號。
Reference 不能指向多個區域。

語法

COUNTIF(range,criteria)

Range 是您想計算符合篩選條件之儲存格個數的儲存格範圍。

Criteria 是用以決定是否要列入計算的搜尋篩選條件，可以是數字、表示式或文字。例如，篩選條件可以是 32、"32"、">32" 或 "蘋果"。

INDEX

傳回一個表格或範圍內的某個值或參照位址。INDEX() 函數有兩種型式：陣列和參照。陣列形式永遠傳回一個值或一個陣列值；而參照形式則傳回一個參照位址。

INDEX (array,row_num,column_num) 傳回指定儲存格的值或陣列中的儲存格陣列。

INDEX (reference,row_num,column_num,area_num) 傳回指定儲存格或儲存格範圍的參照位址。

SMALL

傳回資料組中第 k 小的值。使用這個函數以傳回一個在資料組中特定位置的數值。

語法

SMALL(array,k)

Array 是一個您要找出第 k 小值的數值陣列或資料範圍。

K 是在陣列或要傳回資料範圍中的位置 (由最小值算起)。

**leonchou** · 2005-01-06, 09:58 PM

依照附檔的內容，已將公式小改為
品名 (C9) (為陣列公式)
=IF(COUNTIF(請購單號,C$6) < A9,"",INDEX(品名,SMALL(IF(請購單號=C$6,ROW(請購單號)-1),A9)))
規格 (D9)
=IF(C9="","",INDEX(OFFSET(規格,MATCH(C$6,請購單號,0)-1,),A9))
數量、單位的公式則大同小異。

簡單說明如下，若不明白再說 --

定義名稱請購單號 =OFFSET($J$2,,,COUNTA($J:$J)-1)
定義名稱的用意是寫公式時參照好寫易讀。
用 OFFSET 定義名稱是讓參照的範圍隨資料量成長而動態變化，
不需手動更改名稱定義，參照範圍也不會有多餘無用的空白。
其他名稱如品名、規格的名稱定義大同小異。
品名 (C9) 公式的意思，用白話來說是：
若公式所在的項次大於指定訂購單號的品名個數則空白，否則依項次取回符合條件（訂購單號）的品名。
規格 (D9) 公式的意思，用白話來說是：
若公式所在格對應的品名空白則空白，否則依項次取回指定訂購單號的品名。
規格、數量、單位自訂數字格式「0;0;」，意思是隱藏 0 值。

注意：

品名 (C9) 公式是陣列公式，建立或修改陣列公式必須按下 Ctrl-Shift-Enter 完成公式輸入，公式兩端會自動加上大括號，才能得到正確的結果。
來源資料必須維持以請購單號排序，否則公式可能會得到不正確的結果。

PS. 我壓不到 48K 以下

，上傳不了只好放到網頁空間 ...
http://home.pchome.com.tw/world/leon...zone134523.zip

**joe1025** · 2005-01-07, 09:38 AM

非常感謝您的幫忙

看到大家這麼熱心,好感動喔

補充說明一下大大講的除了公式之外

前面要先定義您要的"欄位範圍"

插入-名稱-定義

就可以看到定義內容了

給大家作各參考唷^^

因為手頭有很多事情要處理！公式一定會看（晚一點）^^||

有問題會提出來, 再次感謝

ＰＳ.怕ＰＣＨＯＭＥ會砍檔,所以我把答案重新上傳^_^

**joe1025** · 2005-01-17, 11:54 AM

再請教一個問題

如果有任何品名出現再"驗收狀況"
下面出現"良好"

公式應該怎麼打勒？
=if(品名=任何函數,"良好","")

品名=任何函數
這一個公式試不出來

如果是C9="*"
答案出來是空白

正確公式應該怎麼打呢？
謝謝幫忙

**leonchou** · 2005-01-18, 10:24 PM

看不太懂...
是說品名非空白就在[驗收狀況]出現"良好"嗎?
是的話 H9 公式
=IF(C9="","","良好")

**joe1025** · 2005-01-19, 08:09 AM

作者：leonchou

看不太懂...
是說品名非空白就在[驗收狀況]出現"良好"嗎?
是的話 H9 公式
=IF(C9="","","良好")

拍謝^^||

上班上昏頭了,一時公式搞錯

謝謝幫忙指證,再次感謝,也請原諒小弟的無知＝＝+

**yan119** · 2005-01-28, 02:09 PM

作者：leonchou

依照附檔的內容，已將公式小改為
品名 (C9) (為陣列公式)
=IF(COUNTIF(請購單號,C$6) < A9,"",INDEX(品名,SMALL(IF(請購單號=C$6,ROW(請購單號)-1),A9)))
規格 (D9)
=IF(C9="","",INDEX(OFFSET(規格,MATCH(C$6,請購單號,0)-1,),A9))
數量、單位的公式則大同小異。

簡單說明如下，若不明白再說 --

定義名稱請購單號 =OFFSET($J$2,,,COUNTA($J:$J)-1)
定義名稱的用意是寫公式時參照好寫易讀。
用 OFFSET 定義名稱是讓參照的範圍隨資料量成長而動態變化，
不需手動更改名稱定義，參照範圍也不會有多餘無用的空白。
其他名稱如品名、規格的名稱定義大同小異。
品名 (C9) 公式的意思，用白話來說是：
若公式所在的項次大於指定訂購單號的品名個數則空白，否則依項次取回符合條件（訂購單號）的品名。
規格 (D9) 公式的意思，用白話來說是：
若公式所在格對應的品名空白則空白，否則依項次取回指定訂購單號的品名。
規格、數量、單位自訂數字格式「0;0;」，意思是隱藏 0 值。

注意：

品名 (C9) 公式是陣列公式，建立或修改陣列公式必須按下 Ctrl-Shift-Enter 完成公式輸入，公式兩端會自動加上大括號，才能得到正確的結果。
來源資料必須維持以請購單號排序，否則公式可能會得到不正確的結果。

PS. 我壓不到 48K 以下

，上傳不了只好放到網頁空間 ...
http://home.pchome.com.tw/world/leon...zone134523.zip

大大你實在太厲害了
我把你的公式套用我的一些表格真的很好用
但現在遇到一個問題
是如資料工作表如沒有資料會顯示錯誤(顯示#REF!)
要如何解決

**leonchou** · 2005-01-29, 12:48 AM

以資料庫的概念來說，鍵值 (key) 欄位是查詢的基本依據，
作為鍵值的欄位 (如上例的”請購單號”) 是不能空白，也沒道理空白。
其他附帶欄位空白則沒有影響。

若說是資料還沒建立，那公式顯示不正常結果也是可預期的，不需特別處理。

如果尚有疑問，請提供以下資訊，以便找問題
1. 資料排列的方式
2. 定義名稱的公式
3. 顯示錯誤的公式