勤哲Excel服務(wù)器做復雜報表

詳情請聯(lián)系范展鵬 qq3051828416 電話15818855188

要求：從外部數(shù)據(jù)源圖一提取數(shù)據(jù)，按圖二報表格式自動提數(shù)

難點: ? a) 需要的數(shù)據(jù)分散在很多個字段里面，需要SQL知識轉(zhuǎn)換數(shù)據(jù)源格式；?b) 報表格式比較特殊，數(shù)據(jù)區(qū)域非連續(xù)，每個年級分為上下兩個區(qū)域，如果做成多個子表，定義數(shù)據(jù)項和設(shè)置提數(shù)公式都非常復雜。

技術(shù)方案: 把加工后的數(shù)據(jù)源根據(jù)學校和年份動態(tài)篩選到一個表，以這個表為新的數(shù)據(jù)源，通過Excel內(nèi)置函數(shù)sumifs() 填充目標報表。

具體步驟:

1. 創(chuàng)建外部數(shù)據(jù)源視圖【v_unPivot_RawDt】，并通過unpivot處理，使之格式簡化，效果如 。

2. 進一步把上面視圖轉(zhuǎn)化為新視圖【v_data2】, 方便提數(shù)。

3. 創(chuàng)建主表，明細表，定義數(shù)據(jù)項

4. 創(chuàng)建表間公式，根據(jù)主表下拉選項，提取數(shù)據(jù)

5. 在Sheet1的目標報表內(nèi)寫入sumifs公式，提取sheet2數(shù)據(jù)

最終效果

附錄

圖四?SQL源代碼

--【v_data_0】WITH a
AS (SELECT
SUBSTRING(grage_name, CHARINDEX('屆', grage_name) - 4, 4) AS Yr, CASE
WHEN SUBSTRING(nj, 1, 1) = 1 THEN
'一年級下學期'
WHEN SUBSTRING(nj, 1, 1) = 2 THEN
'二年級下學期'
WHEN SUBSTRING(nj, 1, 1) = 3 THEN
'三年級下學期'
WHEN SUBSTRING(nj, 1, 1) = 4 THEN
'四年級下學期'
WHEN SUBSTRING(nj, 1, 1) = 5 THEN
'五年級下學期'
WHEN SUBSTRING(nj, 1, 1) = 6 THEN
'六年級下學期' ELSE
'無定義' END AS 年級,
CAST(CAST(SUBSTRING(class_name, CHARINDEX('屆', class_name) + 1, 2) AS INT) AS VARCHAR
* FROM dbo.std
) SELECT Yr,
年級,
班級, Cat,
CAST(HC AS INT) HC,
tenant_name AS 學校
FROM a
UNPIVOT (
HC
FOR Cat IN (disabilityStu, insideArea, outsideArea, overSeas, class_stuCount, lodging, le countryside, girlStu, minorityNational, follow_class, foreignStu, fieldStu
)
) AS P;

--【v_data2】SELECT a.*,
b.表單名 AS 分類
FROM v_data_0 a
LEFT JOIN 字段映射 b
ON a.Cat = b.字段名;