excel sample
?
excel
excel統計函數實例匯總 數組公式指南和示例 - Excel Excel公式應用常見錯誤及處理 excel多個條件篩選的三種方法 EXCEL分類匯總的用法_百度知道 Excel用函數公式篩選數據的方法 在公式中定義和使用名稱 - Excel 快速入門：創建數據透視表 - Excel Excel：[2]vlookup雙條件查詢實例_百度經驗 Love 4.0 EXCEL實現類似SELECT…GROUP BY的方法 在工作表中添加按鈕并為其分配宏 - Excel Privacy warning for Office Excel - Microsoft Community VLOOKUP when the look up value is the output of a formula 使用 VLOOKUP、MATCH 和 INDEX 執行動態搜索 - Excel 易寶典：Excel 中的 COUNTA 和 COUNTIF 函數使用入門 Excel 中使用數據透視表 Pivot Table - 炭炭 - 博客園 如何在 Excel 中使用 Visual Basic 程序選擇單元格-范圍 Excel 引用其他工作簿工作表的特殊方法-Excel基礎應用-ExcelHome技術論壇 - Extract a unique distinct list from two columns using excel 2007 array formula Get Digital Help - Microsoft Excel resource?
excel 2010里加入vba怎么保存不了-CSDN論壇-CSDN.NET-中國最大的IT技術社區
http://bbs.csdn.net/topics/390359839?page=1#post-393597841
隱藏或顯示公式
http://office.microsoft.com/zh-cn/excel-help/HP010066252.aspx
RAW_JIRA
------------------------------------------
Project?Type?Key?Title?Component/s?Date?Username?Time Spent (h)?
grrp-Axiom?Task?GRRPAXIOM-765?China Bank UAT Support (Sam)?2014_China_Bank_P332558?6-Nov-14?Sam Hon?16?
RAW_SN
------------------------------------------
Task?Long Description?Time in seconds?Assigned To?Created Date?Created by?Time Worked Type?Updated?Updated by?Updates?Assignment Group?Short Description?Name?Category?Impacted Configuration Item
INC06771627??47?Edbert Fan?2014/10/20 11:25:02?edbertf?ALERT - APP INTEROPERABILITY?2014/10/20 11:25:02?edbertf?0?CPT-APG-NEWMKTS-AXIOM?HOST rise: ia115c1n7 Disk/Usage/Bytes/Percent? [80.04 > 80]???Axiom for Asia Operations [18925] [grn:/ms/stp/oracdm/RegReporting/AxiomAsia]
TMP_JIRA
------------------------------------------
?Username?Profile?Time?ID?identifier?Profile#?ppm?ppm desc?desc
=IF(INDIRECT("RAW_JIRA!"&ADDRESS(ROW(),CONFIG!$A$3))=0,"",INDIRECT("RAW_JIRA!"&ADDRESS(ROW(),CONFIG!$A$3)))
=IF(INDIRECT("RAW_JIRA!"&ADDRESS(ROW(),CONFIG!$A$4))=0,"",INDIRECT("RAW_JIRA!"&ADDRESS(ROW(),CONFIG!$A$4)))
=IF(INDIRECT("RAW_JIRA!"&ADDRESS(ROW(),CONFIG!$A$5))=0,"",INDIRECT("RAW_JIRA!"&ADDRESS(ROW(),CONFIG!$A$5)))
=IF(INDIRECT("RAW_JIRA!"&ADDRESS(ROW(),CONFIG!$A$6))=0,"",INDIRECT("RAW_JIRA!"&ADDRESS(ROW(),CONFIG!$A$6)))
=IF(A2="","","JIRA")
=IF(ISNUMBER(RIGHT(B2,6)*1),RIGHT(B2,6),"")
=IF(A2="","",IF(F2="","999999",F2))
=IFERROR(VLOOKUP(G2+0,CONFIG!$B$27:$C$208,2,FALSE),"")
=IF(INDIRECT("RAW_JIRA!"&ADDRESS(ROW(),CONFIG!$A$7))=0,"",INDIRECT("RAW_JIRA!"&ADDRESS(ROW(),CONFIG!$A$7)))
TMP_SN
------------------------------------------
Username?Profile?Time?ID?identifier?Profile#?ppm?ppm desc?real Time?desc
=IF(INDIRECT("RAW_SN!"&ADDRESS(ROW(),CONFIG!$A$11))=0,"",INDIRECT("RAW_SN!"&ADDRESS(ROW(),CONFIG!$A$11)))
=IF(INDIRECT("RAW_SN!"&ADDRESS(ROW(),CONFIG!$A$12))=0,"",INDIRECT("RAW_SN!"&ADDRESS(ROW(),CONFIG!$A$12)))
=IF(INDIRECT("RAW_SN!"&ADDRESS(ROW(),CONFIG!$A$13))=0,"",INDIRECT("RAW_SN!"&ADDRESS(ROW(),CONFIG!$A$13))/CONFIG!$A$23)
=IF(INDIRECT("RAW_SN!"&ADDRESS(ROW(),CONFIG!$A$14))=0,"",INDIRECT("RAW_SN!"&ADDRESS(ROW(),CONFIG!$A$14)))
=IF(A2="","","SN")
=IF(A2="","",IFERROR(MID(B2,SEARCH("[",B2)+1,SEARCH("]",B2)-SEARCH("[",B2)-1),"999999"))
=IF(A2="","",IFERROR(VLOOKUP(F2+0,CONFIG!$A$27:$C$208,2,FALSE),"999999"))
=IFERROR(VLOOKUP(F2+0,CONFIG!$A$27:$C$208,3,FALSE),"")
=IF(C2="","",IF(MOD(INDIRECT("RAW_SN!"&ADDRESS(ROW(),CONFIG!$A$13)),CONFIG!$A$23),0,INDIRECT("RAW_SN!"&ADDRESS(ROW(),CONFIG!$A$13))/CONFIG!$A$23))
=IF(INDIRECT("RAW_SN!"&ADDRESS(ROW(),CONFIG!$A$15))=0,"",INDIRECT("RAW_SN!"&ADDRESS(ROW(),CONFIG!$A$15)))
CONFIG
------------------------------------------
JIRA_timesheet required column location??
column number?column name?
=MATCH(B3,RAW_JIRA!A1:I1,0)?Username?
=MATCH(B4,RAW_JIRA!A1:I1,0)?Component/s?
=MATCH(B5,RAW_JIRA!A1:I1,0)?Time Spent (h)?
=MATCH(B6,RAW_JIRA!A1:I1,0)?Key?
=MATCH(B7,RAW_JIRA!A1:I1,0)?Title?
??
SERVICENOW_timesheet required column location??
column number?column name?
=MATCH(B11,RAW_SN!A1:O1,0)?Assigned To?
=MATCH(B12,RAW_SN!A1:O1,0)?Impacted Configuration Item?
=MATCH(B13,RAW_SN!A1:O1,0)?Time in seconds?
=MATCH(B14,RAW_SN!A1:O1,0)?Task?
=MATCH(B15,RAW_SN!A1:O1,0)?Short Description?
??
total count of Jira and serviceNow item??
Jira count?=COUNTA(aLista)-COUNTIF(aLista,"")?
serviceNow count?=COUNTA(aList)-COUNTIF(aList,"")?
total?=B18+B19?
??
time unit to be divided ??
3600??
??
mapping between service now and ppm??
sn id?profile id?ppm desc
18925?308209?308209 - Support - ABO
43235?308889?308889 - Support - ORC
24031?333706?333706 - Others - Capacity & Perf
?332558?332558 - China Bank (Ph3)
?308491?308491 - China Bank (Ph2)
?332561?332561 - China Bank (Change Request)
?334012?334012 - Europe ESMA
?333598?333598 - Hong Kong/Korea SP
?333596?333596 - Japan Short Sell
?332546?332546 - Japan FinOPS Rpt Renovate
?332549?332549 - Japan FXOPS Rpt Renovate
?333601?333601 - Korea Compliance
?344466?344466 - Korea Record Keeping Rpt
?332687?332687 - Korea OTC
?332562?332562 - Korea Bank RTB
?332563?332563 - Others - Autosys R11
?333599?333599 - Others - ABO Upgrade
?333243?333243 - Others - ABO Infra TAM
?333602?333602 - Others - Infra Autos upgrade
?332556?332556 - ORC - MCTB Rpt Renovate
?309015?309015 - SRRP - Integration
?332560?332560 - SDM deco'
?332548?332548 - SSBO Rpt Renovate
999999?999999?Unidentified - Pls assign appropiate component in your ticket
TMP_MERGE1
------------------------------------------
Username?identifier?ppm#?SN Ticket / Jira ID?Time(h)?SN Profile / Jira Component?ppm desc?description?final ppm display
=IF(INDEX(aList,? COUNTA(A$1:A1))<>"",INDEX(aList,? COUNTA(A$1:A1)),INDEX(aLista,? COUNTA(A$1:A1)-CONFIG!$B$19))?
=IF(INDEX(aList,? COUNTA(B$1:B1))<>"",INDEX(eList,? COUNTA(B$1:B1)),INDEX(eLista,? COUNTA(B$1:B1)-CONFIG!$B$19))?
=IF(INDEX(aList,? COUNTA(C$1:C1))<>"",INDEX(ppmList,? COUNTA(C$1:C1)),INDEX(ppmLista,? COUNTA(C$1:C1)-CONFIG!$B$19))?
=IF(INDEX(aList,? COUNTA(D$1:D1))<>"",INDEX(dList,? COUNTA(D$1:D1)),INDEX(dLista,? COUNTA(D$1:D1)-CONFIG!$B$19))?
=IF(INDEX(aList,? COUNTA(E$1:E1))<>"",INDEX(realTimeList,? COUNTA(E$1:E1)),INDEX(cLista,? COUNTA(E$1:E1)-CONFIG!$B$19))?
=IF(INDEX(aList,? COUNTA(F$1:F1))<>"",INDEX(bList,? COUNTA(F$1:F1)),INDEX(bLista,? COUNTA(F$1:F1)-CONFIG!$B$19))?
=IF(INDEX(aList,? COUNTA(G$1:G1))<>"",INDEX(ppmDescList,? COUNTA(G$1:G1)),INDEX(ppmDescLista,? COUNTA(G$1:G1)-CONFIG!$B$19))?
=IF(INDEX(aList,? COUNTA(G$1:G1))<>"",INDEX(descList,? COUNTA(G$1:G1)),INDEX(descLista,? COUNTA(G$1:G1)-CONFIG!$B$19))?
=IF(A2<>"",IF(F2="","N/A",F2),"")
TMP_MERGE2
------------------------------------------
Username?identifier?ppm#?SN Ticket / Jira ID?Time(h)?SN Profile / Jira Component?ppm desc?description?final ppm display
=IF($M2=4^8,"",INDIRECT("TMP_MERGE1!A"&$M2))?
=IF($M2=4^8,"",INDIRECT("TMP_MERGE1!B"&$M2))?
=IF($M2=4^8,"",INDIRECT("TMP_MERGE1!C"&$M2))?
=IF($M2=4^8,"",INDIRECT("TMP_MERGE1!D"&$M2))?
=IF($M2=4^8,"",INDIRECT("TMP_MERGE1!E"&$M2))?
=IF(IF($M2=4^8,"",INDIRECT("TMP_MERGE1!F"&$M2))="",G2,IF($M2=4^8,"",INDIRECT("TMP_MERGE1!F"&$M2)))?
=IF($M2=4^8,"",INDIRECT("TMP_MERGE1!G"&$M2))?
=IF($M2=4^8,"",INDIRECT("TMP_MERGE1!H"&$M2))?
=IF($M2=4^8,"",INDIRECT("TMP_MERGE1!I"&$M2))
row to be filtered
{=IFERROR(SMALL(IF(((INDIRECT("TMP_MERGE1!$E$2:$E$"&CONFIG!$B$20+1)>0)),ROW(INDIRECT("$2:$"&CONFIG!$B$20+1)),4^8),ROW(M1)),4^8)}
JIRA_SN_LIST
-------------------------------------------
Username?identifier?ppm#?SN Ticket / Jira ID?Time(h)?SN Profile / Jira Component?ppm desc?description?rank
=TMP_MERGE2!A1?=TMP_MERGE2!B1?=TMP_MERGE2!C1?=TMP_MERGE2!D1?=TMP_MERGE2!E1?=TMP_MERGE2!F1?=TMP_MERGE2!G1?=TMP_MERGE2!H1?rank
BOOKING_SUMMARY
-------------------------------------------
this is pivot table
notes
-----------------------------------------
1. copy JIRA timesheet to RAW_JIRA, serviceNow time sheet to RAW_SN
2. check if CONFIG sheet required column name matched with RAW_JIRA and RAW_SN, if not, then adjust CONFIG sheet.
3. TMP_JIRA , TMP_SN and TMP_MERGE1/TMP_MERGE2/JIRA_SN_LIST only defined 1000 rows, if you need to expand please choose Name Manager under Formulas tab to adjust, also you need to regenerate pivotTable.
such as aList =TMP_SN!$A$2:$A$1000
4. there is sum of total row of TMP_JIRA and TMP_SN, they are used in TMP_MERGE1/TMP_MERGE2 sheet?
5. the time unit in CONFIG sheet is used to convert time in TMP_SN real Time column
6. mapping table in CONFIG is used in TMP_SN to generate ppm column
7. JIRA_SN_LIST is used to hold the final result from TMP_MERGE2 sheet, It will filter by column Time(h)>0, and sort by Username
8. you need to refresh BOOKING_SUMMARY after? every update by? click Refresh Workbook button
?
button assigned macro
Sub RefreshWorkbook()
'
' RefreshWorkbook Macro
'
'
??? Sheets("JIRA_SN_LIST").Select
??? ActiveSheet.Range("$A$1:$I$1000").AutoFilter Field:=1, Criteria1:="<>"
??? Sheets("BOOKING_SUMMARY").Select
??? ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
??? Sheets("PROJECT_SUMMARY").Select
??? ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
??? Sheets("notes").Select
End Sub
?
?
?
?
總結
以上是生活随笔為你收集整理的excel sample的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 我隐藏的Excel工作表 你别想取消
- 下一篇： 用interrupt()中断Java线程