Board logo

標題: [發問]請教各位先進大哥及大姐，以下資料Excel如何運用，以最短時間求得最精確之資料 [打印本頁]
作者: q88007    時間: 2010-6-13 13:35     標題: [發問]請教各位先進大哥及大姐，以下資料Excel如何運用，以最短時間求得最精確之資料

本帖最後由 q88007 於 2010-6-13 17:33 編輯

各位先進大哥及大姐們：
    因目前公司要求，每月需依相關生產資訊提供以下資料，因小妹我對於Excel函數認知有限，且不會使用VB
，因此上網求助各位先進大哥及大姐們，麻煩請告知小妹，怎樣做才能在最短時間求得最精確之資料。
1、每月均有銷售之型式筆數有多少?
2、每月均未銷售之筆數有多少?
3、每月均有銷售，且月平均銷售數量n的筆數有多少?(n為自行設定可為10、100、1000、10000、……)
4、並非每月均有銷售，但月平均銷售數量n的筆數有多少?(n為自行設定可為10、100、1000、10000、……)
5、若定義每月均有出貨，且月平均出貨數量到n時，則定義該型式為A類，有幾筆符合，且符合之型式有哪些??
6、若定義月平均出貨數量到n時，則定義該型式為B類，有幾筆符合，且符合之型式有哪些??

目前小妹每月需花費3~4天約(32個工作小時) ，統計以上資料，目前公司規格比數約有10000餘筆，且每月持續增加中，
格式範例如下：敬請協助。

類別        規格        09/01        09/02        09/03        09/04        09/05        09/06        09/07        09/08        09/09        09/10        09/11        09/12
A        A1B1        100        200        150        200        220        360        400        420        380        360        350        450
B        A1C1        0        0        0        1000        0        0        0        800        0        0        0        2000
C        A1B2        5        10        15        20        30        20        15        25        30        40        35        20
A        A1C2        0        0        0        0        0        20        0        0        0        0        0        23
B        A1B3        10        12        14        16        18        12        16        18        20        22        24        20
C        A1C3        5        0        5        0        5        0        5        0        5        0        5        0
A        A1B4        2        2        2        6        6        8        8        0        0        0        0        0
B        A1C4        30        0        0        16        0        40        0        30        14        0        0        10
C        A1B5        60        2        14        0        2        0        35        0        5        16        12        5


2010/6/13 17:33補充
感謝大大您的回覆：
    您誤解我的資料排序了，09/01~09/12代表月份，且此份資料是從MRP系統轉出的，
不需要人在重新KEY，謝謝。
作者: ted10932    時間: 2010-6-15 00:42

本帖最後由 ted10932 於 2010-6-15 00:51 編輯

建議可以將MRP轉出的統計表格先在excel 整理好並自行增加2個計算欄位 分別為:
零業績月數 --> 公式 COUNTIF($C2:$N2,0) 請往下複製到最後一筆資料
平均銷售 --> 公式 AVERAGE($C2:$N2) 請往下複製到最後一筆資料

問題會使用到的計算公式如下
每月均有銷售之型式筆數有多少?--> COUNTIF(o2:o10,">0")
每月均未銷售之筆數有多少? Countif(o2:o10,0)
3-6題請善用excel 的"篩選"功能,找出符合設定條件的資料作統計或填入分類, 10000筆資料應能在數分鐘內達成[attach]1285[/attach][attach]1285[/attach]
作者: Hsieh    時間: 2010-6-20 21:22

各位先進大哥及大姐們：
    因目前公司要求，每月需依相關生產資訊提供以下資料，因小妹我對於Excel函數認 ...
q88007 發表於 2010-6-13 13:35



5、若定義每月均有出貨，且月平均出貨數量到n時，則定義該型式為A類，有幾筆符合，且符合之型式有哪些??
6、若定義月平均出貨數量到n時，則定義該型式為B類，有幾筆符合，且符合之型式有哪些??
表示類別也已經是自動轉出嗎?
建議把這些資料做成檔案,並用手工完成這些資料整理後想要的形式後上傳
比較能夠了解你的需求
作者: q88007    時間: 2010-6-22 17:29

目前測試後，還有一個問題，
假設符合<類別A>月平均出貨量大於100台之筆數，怎樣可以計算出來呢??
其他的問題應該就OK的吧??
請大大交一下吧
檢附我的檔案[attach]1362[/attach]
作者: Hsieh    時間: 2010-6-22 19:42

回復 6# q88007
試試看
每月均有出貨月平均超過100的A類別數量=SUMPRODUCT((TRIM($A$2:$A$1314)="A")*($Q$2:$Q$1314>100)*($S$2:$S$1314=12))
每月均有出貨月平均超過100的A類別清單
U2陣列公式
{=IF(ROW(A1)>SUMPRODUCT((TRIM($A$2:$A$1314)="A")*($Q$2:$Q$1314>100)*($S$2:$S$1314=12)),"",INDIRECT("B"&SMALL(IF((TRIM($A$2:$A$1314)="A")*($Q$2:$Q$1314>100)*($S$2:$S$1314=12),ROW($2:$1314),FALSE),ROW(A1))))}
作者: q88007    時間: 2010-6-22 22:00

本帖最後由 q88007 於 2010-6-22 22:19 編輯

回復 7# Hsieh


    您好：經過測試，無法求得結果 ，請大大確認一下，或是請您將公式寫入檔案在上傳，謝謝。[attach]1370[/attach]
作者: Hsieh    時間: 2010-6-22 22:16

回復 8# q88007


    檔案中黃底部分,以A類別示範[attach]1369[/attach]
作者: q88007    時間: 2010-6-22 22:33

回復 9# Hsieh


大大您好：
    您真的太厲害了 ，但是我有疑問請看7樓我寫的演算式，
跟大大您寫的一樣，但是為什麼我就無法算出呢??
第二公式也一樣，請大大再協助，謝謝。
作者: Hsieh    時間: 2010-6-22 22:54

回復 10# q88007
有一樣嗎?
=SUMPRODUCT((TRIM($A$2:$A$1314)="A")*($Q$2:$Q$1314>100)*($S$1:$S$1314=12))
sumproduct的所有陣列大小要相同

清單公式要以陣列公式輸入
CTRL+SHIFT+ENTER使大括弧自動產生
作者: ted10932    時間: 2010-6-23 20:19

延續4樓問題, 提供用計算欄位的方式統計
step1.新增6個計算欄位,
step2.依每個料號實際平均值使用公式if與門檻值作比較,符合條件者顯示數字1,反之為0
step3.使用公式sumif 判斷類別作加總. 結果如附檔(因檔案大小問題,檔案資料筆數已先刪減 公式不變)
[attach]1397[/attach]
另外版主所附的公式 R2, S2 有改變,請留意公式結果的意思.
作者: q88007    時間: 2010-6-23 21:47

回復 10# ted10932


感謝ted10932大大的回覆，謝謝
另外想請教，關於問題六，不曉得大大您有甚麼其他的方式嗎??
作者: q88007    時間: 2010-6-23 22:05

回復 5# Hsieh


    Hsieh 大大：
        有關您的u2陣列公式我想修正為不用管<類別>這個項目，只要月均量大於n(100、200、300...)，公式該如何修改，我已經研究一天，還是不會，
請大大救救我吧
作者: Hsieh    時間: 2010-6-23 23:41

回復 12# q88007


    {=IF(ROW(A1)>SUMPRODUCT(($Q$2:$Q$1314>100)*($S$2:$S$1314=12)),"",INDIRECT("B"&SMALL(IF(($Q$2:$Q$1314>100)*($S$2:$S$1314=12),ROW($2:$1314),FALSE),ROW(A1))))}
條件刪除就好了
作者: ted10932    時間: 2010-6-24 00:26

回覆問題6. 定義月平均出貨數量到n時，則定義該型式為A類，有幾筆符合，且符合之型式有哪些?

意思就是連 類別 欄都要自動計算, 個人作法使用三個 if , 條件值請填在$a$10  
IF(AND(R12=0,Q12>=$A$10),"B",IF(Q12>=$A$10,"A","C"))
要再顯示型式目前只有用手動篩選,也許有高手作能作到自動.
[attach]1401[/attach]

題外話: 版主的需求還算清楚, 將它交給資訊人員產生您需要的報表才是解決之道
作者: gong    時間: 2010-6-24 08:57

如果有多種組合要計算
用篩選方式不是很方便嗎

試試第10列
資料>篩選
用下拉定條件
自己試試
作者: q88007    時間: 2010-6-25 20:26

本帖最後由 Hsieh 於 2010-6-25 23:21 編輯

回復 13# Hsieh


    Hsieh 大大：
        您真是太厲害了，這兩天進行測試，都OK，謝謝您；
但是今天跟同事們討論，

{=IF(ROW(A1)>SUMPRODUCT(($Q$2:$Q$1314>100)*($S$2:$S$1314=12)),"",INDIRECT("B"&SMALL(IF(($Q$2:$Q$1314>100)*($S$2:$S$1314=12),ROW($2:$1314),FALSE),ROW(A1))))}
這個公式，若我要設定>100<249或是大於250小於499時如何填寫，煩請再次指教，謝謝。
作者: Hsieh    時間: 2010-6-26 00:14

回復 16# q88007


    {=IF(ROW(A1)>SUMPRODUCT(($Q$2:$Q$1314<249)*($Q$2:$Q$1314>100)*($S$2:$S$1314=12)),"",INDIRECT("B"&SMALL(IF(($Q$2:$Q$1314<249)*($Q$2:$Q$1314>100)*($S$2:$S$1314=12),ROW($2:$1314),FALSE),ROW(A1))))}



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