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