贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

網友問到:在 Excel 中有一個資料表,如何計算出每個日期的配編數有幾類?

(請參考下圖)

image


【公式設計與解析】

為了簡化公式,特別利用二個輔助欄位。

1. 搜尋指定日期的起始列號

儲存格F2:{=MIN(IF(D2=INT($A$2:$A$25),ROW($A$2:$A$25),999))}

這是陣列公式,公式輸入完畢,要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。

2. 搜尋指定日期的結束列號

儲存格E2:{=MAX(IF(D2=INT($A$2:$A$25),ROW($A$2:$A$25),0))}

這是陣列公式,公式輸入完畢,要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。

3. 完整公式

儲存格G2:=SUMPRODUCT((1/COUNTIF(OFFSET($B$1,F2-1,0,G2-F2+1,1),
OFFSET($B$1,F2-1,0,G2-F2+1,1))))

請參考其他說明:

http://isvincent.pixnet.net/blog/post/46855459

http://isvincent.pixnet.net/blog/post/47236374

, ,

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

在 Excel 的資料表如下圖左,網友問到如何重組每三個文字插入一個數字,依此規則產生資料清單?

在下圖左之中,A欄由數字組成,B欄由文字組成,如何取出每三個文字插入一個數字?

Excel-資料交錯排列組合(OFFSET,INT,MOD)


【公式設計與解析】

(1) 處理數字部分

儲存格F1:=OFFSET($A$1,INT((ROW(1:1)-1)/4),0)

利用 INT 函數將除法結果再取不大於的最大整數。

公式:INT((ROW(1:1)-1)/4)

當公式向下複製時,產生:0,0,0,0,1,1,1,1,2,2,2,2,3,3,3,3,4,4, ...。

儲存格F1向下複製時,產生:1,1,1,1,2,2,2,2,3,3,3,3,4,4,4,4,5,5, ...。

(2)處理文字部分

儲存格G1:=OFFSET($B$1,ROW(1:1)-INT(ROW(1:1)/4)-1,0)

當公式向下複製時,產生:0,1,2,2,3,4,5,5,6,7,8,8,9,10,11,11,12,13, ...。

儲存格G1向下複製時,產生:甲,乙,丙,丙,丁,戊,己,己,庚,辛,壬,壬,癸,子,丑,丑,寅,卯, ...。

(3) 整合文字和數字

儲存格D1:=IF(MOD(ROW(1:1),4)=0,OFFSET($A$1,INT((ROW(1:1)-1)/4),0),
OFFSET($B$1,ROW(1:1)-INT(ROW(1:1)/4)-1,0))

MOD(ROW(1:1),4)=0:判斷儲存格的列號是否為 4,8,12,16, ...。

公式判斷,每隔四列時,會顯示數字,其餘顯示文字。

當公式向下複製時,會產生資料:甲,乙,丙,1,丁,戊,己,2,庚,辛,壬,3,癸,子,丑,4, ...。

, , ,

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

網友問到:在 Excel 的活頁簿中有 6 個工作表(A單位、B單位、C單位、D單位、E單位、F單位),如何計算這個 6 個工作表中相同位置儲存格的和?

這 6 個工作表的格式一致,其中「數值」欄位的內容不各有不同。

Excel-根據工作表名稱摘要各工作表的小計(INDIRECT)

在下圖中,分別計算A單位~F單位的甲乙丙、丁戊己庚、辛壬癸之小計,即根據工作表名稱,來摘要各工作表的小計,該如何處理?

Excel-根據工作表名稱摘要各工作表的小計(INDIRECT)


【公式設計與解析】

儲存格B2:=SUM(INDIRECT(A2&"!B2:B4"))

先將工作表名稱(儲存格A2)和字串『!B2:B4』加以串接,再利用 INDIRECT 函數將其轉換為儲存格位址。

儲存格C2:=SUM(INDIRECT(A2&"!B5:B8"))

儲存格D2:=SUM(INDIRECT(A2&"!B9:B11"))

複製儲存格B2:D2,貼至儲存格B2:D7。

,

vincent 發表在 痞客邦 PIXNET 留言(4) 人氣()

網友問到:在 Excel 的工作表中有一個資料表(如下圖),如何計算資料中的的每個項目之小數的和?

如下圖,資料是由 A, B, C, D 再加上一個數字組合而成。如何分別依據各個項目來計算數字的小計。

Excel-取出第1個字元之後的數字計算總和(SUMPRODUCT,LEFT,MID)

【公式設計與解析】

選取資料欄位中有資料的所有儲存格(本例為儲存格A1:A22),按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:資料。

儲存格D2:=SUMPRODUCT((LEFT(資料,1)=C2)*MID(資料,2,999))

在 SUMPRODUCT 函數中利用二個陣列來運算:

LEFT(資料,1)=C2:在陣列公式中取出每個儲存格中的第一個字元,並判斷是否和儲存格 C2 的內容相同。傳回 TRUE/FALSE 陣列。

MID(資料,2,999):利用 MID 函數取出第一個字元以外的所有字元。(其中 999 只是一個較大的數,即使這個數字超過字串長度,仍可正常運作。)

公式中的『*』運算,可以將 TRUE/FALSE 陣列轉換為 1/0 陣列。

, , ,

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

以前的文章提過,如果在 Excel 的工作表中置入一張相片,如果放大到幾個A4版面時,列印出來的紙張就可以拼貼成一張大海報了。(例如下圖的相片跨兩張 A4 紙張)

如何將PDF檔列印在比A4還大的紙張

如果你將放有照片的工作表版面,直接放大到你要輸出的紙張(沒有要拼貼了)。下圖已將工作表調整為 A3 版面。

如何將PDF檔列印在比A4還大的紙張

當你把 Excel 另存新檔時,可以直接轉換為 PDF 檔。

如何將PDF檔列印在比A4還大的紙張

當你要列印這個 PDF 檔時,預設會遇到其自動切割成 A4 大小,該如何印在 A3 的紙上呢?

如何將PDF檔列印在比A4還大的紙張

如何將PDF檔列印在比A4還大的紙張

請你在列印時,在「大小」中勾選「依PDF頁面大小選擇紙張來源」。PDF 就不會縮放,也不會切割紙張了。

如何將PDF檔列印在比A4還大的紙張

或是在版面設定中先行改變紙張大小(例如:A3),就可以實際大小來列印了。

如何將PDF檔列印在比A4還大的紙張

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼