贊助廠商

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

搜尋本部落格文章資料

目前日期文章:201609 (30)

瀏覽方式: 標題列表 簡短摘要

根據前二篇文章:

Excel-由清單挑選列出多組資料中的其中一組(OFFSET,INDIRECT,陣列公式)

Excel-由清單挑選列出多組資料中的其中一組並分頁顯示(OFFSET,ROW,陣列公式)

如果把挑選資料的條件再加「日期」欄位,該如何處理:

參考下圖,每個月的每一天中,所有報表集合在一個清單,由第一個欄位決定所屬的日期。在摘要表中只要輸入一個日期,則會篩選出同一日期的所有資料。

Excel-由清單挑選列出多組資料中的其中一組並分頁分日顯示(OFFSET,陣列公式)


【公式設計與解析】

公式說明請參考前二篇文章:

Excel-由清單挑選列出多組資料中的其中一組(OFFSET,INDIRECT,陣列公式)

Excel-由清單挑選列出多組資料中的其中一組並分頁顯示(OFFSET,ROW,陣列公式)

將日期欄位的所有資料內容定義名稱:日期。

公式修改如下:

(以下為陣列公式,輸入完成請按 Ctrl+Shift+Enter 鍵,Excel會自動產生「{}」)

(1) 品名欄位

儲存格L2:{=OFFSET($B$1,SMALL(IF((INDIRECT($L$1)<>0)*(日期=$O$1),ROW
(INDIRECT($L$1)),999),($N$1-1)*8+ROW(1:1))-1,0)}

其中『(INDIRECT($L$1)<>0)*(日期=$O$1)』的『*』相當於執行邏輯 AND 運算。

(2) 售價欄位

儲存格M2:{=OFFSET($B$1,SMALL(IF((INDIRECT($L$1)<>0)*(日期=$O$1),ROW
(INDIRECT($L$1)),999),($N$1-1)*8+ROW(2:2))-1,0)}

(3) 數量欄位

儲存格N2:{=OFFSET($B$1,SMALL(IF((INDIRECT($L$1)<>0)*(日期=$O$1),ROW
(INDIRECT($L$1)),999),($N$1-1)*8+ROW(3:3))-1,0)}

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

最近 Pokémon Go 大流行,一般使用者都見識到了 VR(Virtual Reality)和AR(Augmented Reality)。除了 Pokémon Go 這個遊戲之外,其實你的手機上也有一些你已經在使用的 VR 和 AR 應用。

(1) Google 街景檢視

當你在 Google 街景檢視 App 中,點選了某一個 360 度環景圖,再點選右上角的 Cardboard 圖示。

手機裡的VR和AR應用程式(以Google為例)  手機裡的VR和AR應用程式(以Google為例)

手機上立即呈現 VR 模式的圖像:

手機裡的VR和AR應用程式(以Google為例)

你可以透過 Google Cardboard 來觀看這個 VR 實景,展現了 3D 的360度景象。

手機裡的VR和AR應用程式(以Google為例) 

(圖片來源:https://vr.google.com/cardboard/)


2. Google Translate

Pokémon Go 是 AR 的應用,有著虛實整合的概念。

手機裡的VR和AR應用程式(以Google為例) 手機裡的VR和AR應用程式(以Google為例)

手機裡的VR和AR應用程式(以Google為例) 手機裡的VR和AR應用程式(以Google為例)

而 Google Translae 也有著 AR 的應用,從以下的過程中是否有著虛實整合的概念。

試著開啟 Google Translate App,按下相機圖示,對著待翻譯的文字拍照:

手機裡的VR和AR應用程式(以Google為例) 手機裡的VR和AR應用程式(以Google為例)

當 Google 對著拍好的影像分析出文字,你只要以手指輕觸即可標明文字取得翻譯結果。

手機裡的VR和AR應用程式(以Google為例) 手機裡的VR和AR應用程式(以Google為例)

如此便可取得想要的翻譯結果。

手機裡的VR和AR應用程式(以Google為例)

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

根據前一篇文章:

Excel-由清單挑選列出多組資料中的其中一組(OFFSET,INDIRECT,陣列公式)

網友想要以每頁只顯示 8 筆,然後以下拉式清單來切換頁數,該如何處理?

如下圖,每家店的進貨數量可能會超過 8 筆,想要在儲存格M1中使用下拉式清單來選取顯示頁數。

Excel-由清單挑選列出多組資料中的其中一組並分頁顯示(OFFSET,ROW,陣列公式)

【公式設計與解析】

公式說明請參考前一篇文章:

Excel-由清單挑選列出多組資料中的其中一組(OFFSET,INDIRECT,陣列公式)

先在儲存格M2,利用資料驗證方式設定下拉式清單:

資料驗證準則:清單;來源:1,2,3,4,5

Excel-由清單挑選列出多組資料中的其中一組並分頁顯示(OFFSET,ROW,陣列公式)

公式修改為:

(以下為陣列公式,輸入完成請按 Ctrl+Shift+Enter 鍵,Excel會自動產生「{}」)

(1) 品名欄位

儲存格K2:{=OFFSET($A$1,SMALL(IF(INDIRECT($K$1)<>0,ROW(INDIRECT
($K$1)),999),($M$1-1)*8+ROW(1:1))-1,0)}

(2) 售價欄位

儲存格L2:{=OFFSET($A$1,SMALL(IF(INDIRECT($K$1)<>0,ROW(INDIRECT
($K$1)),999),($M$1-1)*8+ROW(2:2))-1,0)}

(3) 數量欄位

儲存格M2:{=OFFSET($A$1,SMALL(IF(INDIRECT($K$1)<>0,ROW(INDIRECT
($K$1)),999),($M$1-1)*8+ROW(3:3))-1,0)}

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

網友問到:在 Excel 的工作表中有一個各個商家進貨數量表(如下圖左),如何透過下拉式清單選取商家後,可以自動建立該商家的進貨資料(如下圖右)?

在下圖中共有六個商家,對於不同商品有不同的進貨的數量,要列出有進貨的商品清單,該如何處理?

Excel-由清單挑選列出多組資料中的其中一組(OFFSET,INDIRECT,陣列公式)


【公式設計與解析】

1. 定義名稱

選取儲存格C2:H23,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:忠店, 孝店, 仁店, 愛店, 信店, 義店。

Excel-由清單挑選列出多組資料中的其中一組(OFFSET,INDIRECT,陣列公式)

2. 建立下拉式清單

在儲存格K1中設定資料驗證:

儲存格內允許:清單;來源:=$C$2:$H$2。

Excel-由清單挑選列出多組資料中的其中一組(OFFSET,INDIRECT,陣列公式)

3. 輸入公式

列出「品名」:

儲存格K2:{=OFFSET($A$1,SMALL(IF(INDIRECT($K$1)<>0,ROW(INDIRECT
($K$1)),999),ROW(1:1))-1,0)}

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

(1) INDIRECT($K$1)

將儲存格K1中的內容("仁店")透過 INDIRECT 函數轉換為儲存格範圍(已在步驟 1 定義好「仁店」的儲存格範圍)。

(2) IF(INDIRECT($K$1)<>0,ROW(INDIRECT($K$1)),999)

在陣列公式中,判斷儲存格K1內容(仁店)的儲存格範圍內容是否為空白(=0),若是則傳回其列號陣列,若否則傳回『999』(這只是一個很大的數)。

(3) SMALL(第(2)式,ROW(1:1))

利用 SMALL 函數找出第(3)式傳回的列號陣列中的最小者。當公式向下複製時,ROW(1:1)=1→ROW(2:2)=3→ROW(3:3)=3→...。可以依序找出第1, 2, 3, ... 較小者。

(4) OFFSET($A$1,第(3)式-1,0)

根據第(4)式傳回的列號利用 OFFSET 函數查詢對應的儲存格內容。

同理:

列出「售價」:

儲存格L2:{=OFFSET($B$1,SMALL(IF(INDIRECT($K$1)<>0,ROW(INDIRECT
($K$1)),999),ROW(1:1))-1,0)}

列出「數量」:

儲存格M2:{=OFFSET(INDIRECT($K$1),SMALL(IF(INDIRECT($K$1)<>0,ROW
(INDIRECT($K$1)),999),ROW(1:1))-3,0)}

複製儲存格K2:M2,貼至儲存格K2:M23。

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

網友根據下圖左的 Excel 資料表,想要列出如下圖右的摘要結果,該如何處理?

在下圖左的資料表中每個人員可以參加 3 個組別(沒有重覆),要如何列出每個組的人員清單(如下圖右)?

Excel-從多欄中列出符合者清單(OFFSET,SMALL,ROW,陣列公式)


【公式設計與解析】

儲存格E2:{=OFFSET($A$1,SMALL(IF(($B$2:$D$23=F$1),ROW($B$2:$D$23),
999),ROW(1:1))-1,0)}

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

複製儲存格E2,貼至儲存格E2:K23。

(1) IF(($B$2:$D$23=F$1),ROW($B$2:$D$23),999)

在陣列公式中,若資料範圍儲存格B2:D23和儲存格F1相同,則傳回相符者的列號陣列,否則傳回『999』(這只是一個很大的數值,必須超過所以資料的列號最大值。)。

(2) SMALL(第(1)式,ROW(1:1))

根據第(1)式傳回的列號陣列,利用 SMALL 函數找出其最小值(ROW(1:1)=1)。若公式向下複製,則ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...,可以依序找出第 1, 2, 3, ...較小者。

(3) OFFSET($A$1,第(2)式-1,0)

將第(2)式傳回的列號代入 OFFSET 函數找出對應的儲存格內容。當公式向下複製時,可以由最小列至最大列,依序列出符合者的儲存格內容。

摘要結果的「0」是公式運算的結果,表示已超出資料範圍的傳回值。

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

有網友問到:在 Excel 中的 SUMIF、SUMIFS、SUMPRODUCT 函數有其共通之處,在使用時如何互相取代?如果使用 SUM+IF+陣列公式,又是如何互相取代?

參考下圖的資料表,運用 SUMIF、SUMIFS、SUMPRODUCT 函數及使用 SUM+IF+陣列公式來計算特定條件下的數量小計和金額小計。

SUM+IF+陣列公式的互通

以下用四個例子來對照 SUMIF、SUMIFS 及 SUMPRODUCT 函數及使用 SUM+IF+陣列公式。

為方便說明,先選取B1:D23,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:單價、數量、金額。

陣列公式輸入完成要 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。


(1) 計算單價500以上者數量小計

儲存格F3:=SUMIF(單價,">500",數量)

儲存格F3:=SUMPRODUCT((單價>500)*數量)

儲存格F3:{=SUM(IF(單價>500,數量,))}


(2) 計算單價500以上者金額小計

儲存格F5:=SUMIF(單價,">500",金額)

儲存格F5:=SUMPRODUCT((單價>500)*金額)

儲存格F5:{=SUM(IF(單價>500,金額,))}


(3) 計算單價300~600者數量小計

儲存格F8:=SUMIFS(數量,單價,">=300",單價,"<=600")

儲存格F8:=SUMPRODUCT((單價>=300)*(單價<=600)*數量)

儲存格F8:{=SUM(IF((單價>=300)*(單價<=600),數量,))}


(4) 計算單價300~600者金額小計

儲存格F10:=SUMIFS(金額,單價,">=300",單價,"<=600")

儲存格F10:=SUMPRODUCT((單價>=300)*(單價<=600)*金額)

儲存格F10:{=SUM(IF((單價>=300)*(單價<=600),金額,))}

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

網友問到:在 Excel 中如下圖的資料表中,如何在挑選日期後自動計算每日的數量總和?

參考下圖,當指定一個項目時,希望能計算各日的總和;當指定一個日期時,希望能計算所有項目的總和,該如何處理?

Excel-利用下拉式清單挑選後計算各欄各列的總和(OFFSET,INDIRECT,MATCH)


【公式設計與解析】

1. 為儲存格範圍定義名稱

選取儲存格A2:J11,按 Ctrl+Shift+F3 鍵,勾選「最左欄」,一次定義每個項目的儲存格範圍。

Excel-利用下拉式清單挑選後計算各欄各列的總和(OFFSET,INDIRECT,MATCH)

結果如下:

Excel-利用下拉式清單挑選後計算各欄各列的總和(OFFSET,INDIRECT,MATCH)

2. 設計下拉式清單

利用「資料驗證」功能來設計下拉式清單功能。

Excel-利用下拉式清單挑選後計算各欄各列的總和(OFFSET,INDIRECT,MATCH)

(1) 在儲存格L3的資料驗證準則中設定:

儲存格內允許:清單;來源:=$A$2:$A$11

(2) 在儲存格L5的資料驗證準則中設定:

儲存格內允許:清單;來源:=$B$1:$J$1

Excel-利用下拉式清單挑選後計算各欄各列的總和(OFFSET,INDIRECT,MATCH)

3. 輸入公式

(1) 儲存格L2:=SUM(INDIRECT(L2))

INDIRECT(L2):將儲存格L2的內容(本例:己)利用 INDIRECT 函數轉換為位址。因先前已定義各個項目的儲存格範圍,本例結果為:儲存格B7:J7。

最後透過 SUM 函數將儲存格範圍予以加總。

(2)儲存格L5:=SUM(OFFSET(A1,1,MATCH(L4,B1:J1,0),10,1))

MATCH(L4,B1:J1,0):找尋儲存格L4的內容在儲存格B1:J1中的位置(本例傳回『6』)。

OFFSET(A1,1,MATCH(L4,B1:J1,0),10,1):利用 OFFSET 函數找出儲存格L4所屬的儲存格範圍(本例為:儲存格G2:G11)。

最後透過 SUM 函數將儲存格範圍予以加總。


【補充資料】

如何讓資料表中的欄和列儲對照存格L5和儲存格L2的內容以不同色彩文字顯示?

1. 選取儲存格A2:J11,進入設定格式化的條件編輯規則:

規則:=$A2=$L$2 (注意位址的相對參照和絶對參照)

格式:紅色文字。

Excel-利用下拉式清單挑選後計算各欄各列的總和(OFFSET,INDIRECT,MATCH)

2. 選取儲存格A2:J11,進入設定格式化的條件編輯規則:

規則:=B$1=$L$4 (注意位址的相對參照和絶對參照)

格式:紅色文字。

Excel-利用下拉式清單挑選後計算各欄各列的總和(OFFSET,INDIRECT,MATCH)

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

有網友問到在 Excel 中跨工作表使用SUMPRODUCT函數的問題。參考下圖:

假設在工作表3的 SUMPRODUCT 函數要使用工作表1和工作表2的內容來運算。

Excel-跨工作表使用SUMPRODUCT函數

SUMPRODUCT 函數中參照其他工作表的位址是可以的,只要儲存格範圍的對照是正確的即可。如下公式:

工作表3的儲存格A2:=SUMPRODUCT((工作表1!$B$2:$B$11>5)*工作表2!B2:B11)

如果你覺得公式太長不好處理,可以參考以下的做法:

定義工作表1的儲存格B1:B11之名稱:次數

定義工作表2的儲存格B1:B11之名稱:數量

最重要的是領域必須設定為「活頁簿」。

Excel-跨工作表使用SUMPRODUCT函數

輸入公式:

工作表3的儲存格A2:=SUMPRODUCT((次數>5)*數量)

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

網友問到:在下圖左的 Excel 工作表中有一些廠商和產品的需求量,其中有一些是重覆的項目,如何將其轉換為下圖右的摘要統計表?

在下圖中,轉換過程中,空白列也一併消除了。該如何建立這個報表?

Excel-使用樞紐析表建立工作表的摘要統計表

網友詢問該用何公式來處理?我也還沒想出來。但是使用樞紐分析表,幾分鐘即可以輕易做出結果。

參考以下的步驟:

1. 選取儲存格A1:C20,建立樞紐分析表。

Excel-使用樞紐析表建立工作表的摘要統計表

2. 將「列」加入『廠商』,再加入『產品』。

3. 在「值」加入『需求量』。

Excel-使用樞紐析表建立工作表的摘要統計表

4. 將「值」的需求量由計數改為加總。

Excel-使用樞紐析表建立工作表的摘要統計表

5. 選取儲存格A4,按一下「欄位設定」。

6. 在「小計與篩選」標籤下的「小計」區中選取『無』。

Excel-使用樞紐析表建立工作表的摘要統計表

7. 在[版面配置與列印]標籤下:

選取『以列表方式顯示項目標籤』,再勾選『重複項目標籤』。

Excel-使用樞紐析表建立工作表的摘要統計表

8. 選取儲存格A4:C15,貼至原來工作表。

Excel-使用樞紐析表建立工作表的摘要統計表

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

網友問到的 Excel 問題:如下圖左有一個人員的排班基本表,其中每個日期分為二列,分別是早班和晚班的人員輪值名單,現在要轉換為下圖右的人員和日期的彙整表,該如何處理?對照

Excel-資料表重組(SUMPRODUCT,OFFSET,MATCH)


【公式設計與解析】

選取儲存格A1:A21,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期。

儲存格I2:=OFFSET($B$1,SUMPRODUCT((OFFSET($C$1,MATCH($H2,
日期,0),0,2,4)=I$1)*ROW(OFFSET($C$1,MATCH($H2,日期,0),0,2,4)))-1,0)

複製儲存格I2,貼至儲存格I2:P11。

(1) MATCH($H2,日期,0)

查詢儲存格H2在日期陣列中的位置(列號),因為每一個日期都有二個符合,但是只會傳回第1個(列號較小者)。本例傳回「2」。

Excel-資料表重組(SUMPRODUCT,OFFSET,MATCH)

(2) OFFSET($C$1,第(1)式,0,2,4)

利用第(1)式的傳回值找出符合該日期的儲存格範圍,例如:儲存格H2為『9/1』,會傳回儲存格C2:F3。

(3) ROW(第(2)式)

利用第(2)式的儲存格範圍,藉由 ROW 函數傳回每個儲存格的列號陣列。以儲存格C2:F3為例,在 SUMPRODUCT 函數中會傳回 {2,3}。

(4) SUMPRODUCT((第(2)式=I$1)*ROW(第(2)式)

SUMPRODUCT 函數中的條件:第(2)式=I$1,因為本例只會有一個符合,所以乘以ROW(第(2)式後,即可得符合者的列號。公式=SUMPRODUCT({0,0,0,0,0,0,0,3},最後傳回『3』,表示『甲』在『第3列』。

(5) OFFSET($B$1,第(4)式-1,0)

根據第(4)式的傳回值,代入 OFFSET 函數於B欄中找出對應的位置,即為所求。

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

網友問到實用且簡單的問題:在下圖的 Excel 工作表中,如何將報到日期欄位尚未輸入資料者,整列以不同底色標示?

Excel-將資料表中未輸入資料者整列顯示不同色彩

參考以下的步驟:

1. 選取所有的資料,本例為儲存格A2:D26。

2. 在「設定格式化的條件」中新增一個規則。

規則類型:使用公式來決定要格式化哪些儲存格

公式:=$D2=""  (注意:D欄要使用絶對參照,而2列要使用相對參照。)

格式:粉紅色儲存格底色

Excel-將資料表中未輸入資料者整列顯示不同色彩

如果你的資料很多,透過「自動篩選」工具,在其中一個欄位中選取「依色彩排序」,再選取一個色彩(本例為粉紅色)。

Excel-將工作表中未輸入資料者整列顯示不同色彩

未輸入資料的欄位即會集合在一起,以方便輸入資料。

Excel-將工作表中未輸入資料者整列顯示不同色彩

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

最近報載有人於 Google 景街檢視中看到以故的親人,或是已消逝的家園景像。隨即有人問到該如何在 Google 地圖中看到較舊的景像?

Google 地圖很早以前就有提供過往的歷史街景,參考以下做法來找出以前的街景。

1. 首先到你想檢視的 Google 景街中。

2. 點選視窗左上角「街景服務-日期」左側的圖示的三角形。

Google地圖中的歷史街景

3. 移動捲軸到你想要的日期。

Google地圖中的歷史街景

4. 點選小視窗中的景像,整個視窗即會顯示「當時」的街景。

Google地圖中的歷史街景

你也有來不及記錄的影像嗎?到 Google 地圖上找找看。

你的學校作業和歷史中的地理有關嗎?到 Google 地圖上找找看。

你的學校作業和地理中的歷史有關嗎?到 Google 地圖上找找看。

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

有同事問到一個簡單卻常見的問題。在 Excel、Word 中,我們常會利用各種捷徑、超連結、釘選等功能來達到快速開啟文件,但卻常忽略了這份文件的實體位置,而在檔案總管中要切換至文件所屬資料夾,總覺得要想很久文件放在那個資料夾,或切換很多層資料夾才能到達文件所在位置。

如何才能在檔案總管中,快速開啟某一文件的所在資料夾呢?

Excel/Word/PowerPoint-快速在檔案總管中開啟文件的資料夾

以 Excel 2013 版本為例,其「資訊」頁面提供了解答!

當你開啟了一份文件後,切換至[檔案]功能表的「資訊」面頁下。你會在檔案名稱之下看到檔案的儲存格位置,點選這個位置就會顯示快顯功能,點選「開啟檔案位置」,即會在檔案總管中開啟這個資料夾。

或是在視窗右下角的[相關文件]區中也可以點選「開啟檔案位置」,快速在檔案總管裡進入這份文件的資料夾。

Excel/Word/PowerPoint-快速在檔案總管中開啟文件的資料夾

附帶一提:

在上圖中的[版本]區有多個編輯過程中自動儲存的檔案,點選這些檔案可以回復較早前的編輯版本。

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

網友問到:在 Excel 工作表中有一個社團選取結果的資料表(如下圖左),如何才能分社團/分星期的列出各個報表?

例如:在下圖中有多個班級的選社結果,其中星期一至星期五,每個學生每天都有一個要參加的社團,如何能分別依各星期和各社團列出社團人員的清單?

Excel-資料表利用雙條件查詢列出清單(INDIRECT,ROW,OFFSET,陣列公式)

【公式設計與解析】

1. 定義儲存格範圍名稱

選取D欄至H欄中有資料的範圍(例如:儲存格D1:H200),按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:星期一、星期二、星期三、星期四、星期五。

2. 輸入公式

儲存格J5:{=OFFSET($A$1,SMALL(IF(INDIRECT($K$1)=$K$2,ROW(INDIRECT
($K$1)),999),ROW(1:1))-1,0)}

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

(1) INDIRECT($K$1)

將儲存格K1中的內容("星期一")透過 INDIRECT 函數轉換為儲存格範圍(已定義好「星期一」的儲存格範圍)。

(2) ROW(INDIRECT($K$1))

藉由 ROW 函數取得由儲存格K1內容轉換的儲存格範圍的列號。例如:ROW(C2)=2、ROW(C3)=3、...。

(3) IF(INDIRECT($K$1)=$K$2,ROW(INDIRECT($K$1)),999)

在陣列公式中,判斷儲存格K1內容(星期幾)的儲存格範圍和儲存格K2內容(社團名稱)相同者,傳回其列號陣列,不相同者傳回『999』(這只是一個很大的數)。

(4) SMALL(第(3)式,ROW(1:1))

利用 SMALL 函數找出第(3)傳回的列號陣列中的最小者。當公式向下複製時,ROW(1:1)=1→ROW(2:2)=3→ROW(3:3)=3→...。可以依序找出第1,2,3, ... 較小者。

(5) OFFSET($A$1,第(4)-1,0)

根據第(4)式傳回的列號利用 OFFSET 函數查詢對應的儲存格內容。

同理:

儲存格K5:{=OFFSET($B$1,SMALL(IF(INDIRECT($K$1)=$K$2,ROW(INDIRECT
($K$1)),999),ROW(1:1))-1,0)}

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

儲存格L5:{=OFFSET($C$1,SMALL(IF(INDIRECT($K$1)=$K$2,ROW(INDIRECT
($K$1)),999),ROW(1:1))-1,0)}

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

複製儲存格J5:L5,往下各列貼上。


【補充說明】

利用資料驗證功能建立星期幾和社團名稱的下拉式清單,可以變成動態查詢系統。一個資料表可以製作25個報表。

Excel-資料表利用雙條件查詢列出清單(INDIRECT,ROW,OFFSET,陣列公式)

Excel-資料表利用雙條件查詢列出清單(INDIRECT,ROW,OFFSET,陣列公式)

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

線上編輯、協作一份文件,對一個團隊而言是愈來愈重要了!

Dropbox 推出 Paper 的試用功能,讓你能夠在線上編輯文件,並且讓團隊成員參與協作,或是提供相關意見。你也來試試,看看對你的工作是否有幫助。

Dropbox Paper:https://paper.dropbox.com/

Dropbox Paper-線上編輯文件,團隊成員參與協作或是提供意見

我們就來練習操作一下!

先為文件設定一個標題(本例為:快樂悅讀),點選加號可以增加各種類型內容。

Dropbox Paper-線上編輯文件,團隊成員參與協作或是提供意見

可以新增圖片、Dropbox上的檔案、表格,可以為文字建立項目符號、項目清單、核取工作清單,可以新增分隔線和新增程式碼。

Dropbox Paper-線上編輯文件,團隊成員參與協作或是提供意見

你可以一次新增一個或多個圖片,圖片可以選取:

Dropbox Paper-線上編輯文件,團隊成員參與協作或是提供意見

拖曳圖片即可為圖片改變位置並且進行排版:

Dropbox Paper-線上編輯文件,團隊成員參與協作或是提供意見

單一圖片可以修改其顯示的位置:

Dropbox Paper-線上編輯文件,團隊成員參與協作或是提供意見

選取所輸入的文字,可以為文字設定格式:粗體、刪除線、大型標題字、小型標題字、項目符號、編號、超連結、註解等。

Dropbox Paper-線上編輯文件,團隊成員參與協作或是提供意見

若要加入網頁超連結,可以直接複製一個網址,貼至 Paper 文件上即可。如果你複製一個 YouTube 網址,則會自動嵌入 YouTube 影片。(Dropbox Paper認得的格式,請自行參閱其網站說明)

Dropbox Paper-線上編輯文件,團隊成員參與協作或是提供意見

如果新增一個表格,要再新增欄或列,則點選「+」號,即可新增一欄或一列。

Dropbox Paper-線上編輯文件,團隊成員參與協作或是提供意見

選取一欄或一列,可以進行刪除動作。拖曳欄格線即可改線欄寬度,而且可以切換表格放大為螢幕寬度或是縮小。

Dropbox Paper-線上編輯文件,團隊成員參與協作或是提供意見

當你完成一份文件,可以在選單中選取「Download」來下載文件:

Dropbox Paper-線上編輯文件,團隊成員參與協作或是提供意見

可以選取下載為一個 Word 格式的檔案(docx),

Dropbox Paper-線上編輯文件,團隊成員參與協作或是提供意見

先轉換而後才能下載:

Dropbox Paper-線上編輯文件,團隊成員參與協作或是提供意見

Word 檔如下圖:

Dropbox Paper-線上編輯文件,團隊成員參與協作或是提供意見

你可以將這份文件分享給其他使用者,按一下「Copy link」,即可產生文件的超連結:

Dropbox Paper-線上編輯文件,團隊成員參與協作或是提供意見

按一下上圖的「linking settings」,可以設定文件可以被誰開啟(任何人或是被邀請者),可以設定是否能編輯或是只能註記。

Dropbox Paper-線上編輯文件,團隊成員參與協作或是提供意見

本例設定為除了自己,訪客可以檢視(無法編輯)。

Dropbox Paper-線上編輯文件,團隊成員參與協作或是提供意見

他人取得這個網址時,可以檢視這份文件。視窗左側會顯示你設定的標題(以H1和H2設定的文字)。

Dropbox Paper-線上編輯文件,團隊成員參與協作或是提供意見

如果檢視者有登入自己的帳號,則可以對這份文件進行註記。

Dropbox Paper-線上編輯文件,團隊成員參與協作或是提供意見

如果你想知道這個文件被其他編輯的狀況,可以從選單中點選「View history」:

Dropbox Paper-線上編輯文件,團隊成員參與協作或是提供意見

其中記載了那一個使用者修改了那些內容。按一下「Undo」,你可以回到先前時間點的各種編輯狀態。

Dropbox Paper-線上編輯文件,團隊成員參與協作或是提供意見

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

如果你有在使用 Google Keep,你可以注意這個功能。當你上傳一張圖片到一個記事中,Google Keep 可以幫你取出其中的文字(即類似OCR功能)。不過,目前僅限英文字,期待開放不同語系。

利用下圖為例:

Google Keep-取出圖片中的文字

在記事中上傳這張圖片:

Google Keep-取出圖片中的文字

在選單選取「擷取圖片文字」:

Google Keep-取出圖片中的文字

隨即產生圖片中文字的辦識結果,複製這些文字可以做其他的應用。

這真是方便而實用的功能!

Google Keep-取出圖片中的文字

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

這篇文章雖然簡單,但是有感而發。因為常看到同事在已寫好的公式又新增資料,造成一些調整公式上的困擾。

在 Excel 中寫好的計算公式,如下圖:

Excel-已寫好的公式又新增資料,如何方便的調整公式

一旦新增了某些資料後,還得要自行更新某些公式,是否有些較方便的方式?

Excel-已寫好的公式又新增資料,如何方便的調整公式

(1)

選取儲存格E2,編輯公式時,使用拖曳已有儲存格範圍的框線來改變儲存格範圍。

Excel-已寫好的公式又新增資料,如何方便的調整公式

公式中的儲存格範圍隨之更新:

Excel-已寫好的公式又新增資料,如何方便的調整公式

(2)

點選在儲存格E3中的左上角的綠色三角圖示,再選取「更新公式以包含儲存格」。

Excel-已寫好的公式又新增資料,如何方便的調整公式

公式內容會自動調整至正確的新儲存格範圍。

Excel-已寫好的公式又新增資料,如何方便的調整公式

如果你的 Excel 不會自動出現儲存格左上角的綠色三角圖示,則可以選取[檔案/選項]功能表中的「公式」標籤,在[錯誤檢查]區中勾選「啟用背景錯誤檢查」。在此也可以設定這個錯誤標示的色彩。

Excel-已寫好的公式又新增資料,如何方便的調整公式

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

網友提問 Excel 的問題:想要統計公司裡的三間工廠於一年中各個月的離職人數,該如何處理?

如下圖,有一個日期清單,在工廠欄位中有A廠、B廠、C廠三種可能,如何能建立分月/分廠的分析報表?

Excel-依日期清單分月計算不同項目的數量(SUMPRODCUT)


【公式設計與解析】

1. 定義儲存格範圍名稱

選取儲存格A1:B27,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:「日期、工廠」。

2. 輸入公式

儲存格E2:=SUMPRODUCT((MONTH(日期)=ROW(1:1))*(工廠=E$1))

複製儲存格E2,貼至儲存格E2:G13。

MONTH(日期):利用 MONTH 函數取出日期陣列中的每個日期的月份。

ROW(1:1):當公式向下複製時,ROW(1:1)=1→ROW(2:2)=2→ ...→ ROW(12:12)=12。

條件一「MONTH(日期)=ROW(1:1)」:判斷日期的月份是否為一月(ROW(1:1)=1),傳回陣列 TRUE/FALSE

條件二「工廠=E$1」:判斷工廠的陣列中是否和儲存格E1(=A廠)內容是符,傳回陣列 TRUE/FALSE

(MONTH(日期)=ROW(1:1))*(工廠=E$1):運式時 TRUE/FALSE 會轉換為 1/0 陣列。而兩個條件中的「*」,等同於執行邏輯 AND 運算,

最後,透過 SUMPRODUCT 運算二個條件傳回值(1/0 陣列)的乘積和,即為所求。


【附註】

為了幫助讀者理解,列出複製儲存格E2公式後的結果:

(1) 儲存格F2:=SUMPRODUCT((MONTH(日期)=ROW(1:1))*(工廠=F$1))

(2) 儲存格G2:=SUMPRODUCT((MONTH(日期)=ROW(1:1))*(工廠=G$1))

(3) 儲存格E3:=SUMPRODUCT((MONTH(日期)=ROW(2:2))*(工廠=E$1))

(4) 儲存格E4:=SUMPRODUCT((MONTH(日期)=ROW(3:3))*(工廠=E$1))

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

網友在下圖的 Excel 工作表中,想要根據圖上的A欄條件,要在B欄顯示一個結果,該如何處理?

如下圖,共有四個條件,四個結果,假設A欄小於或等於 500 時,結果預設為 0。

Excel-多條件判斷結果(IF,VLOOKUP)


【公式設計與解析】

(1) 使用 IF 函數

儲存格B1:=IF(A1>2000,98,IF(A1>1600,75,IF(A1>1100,60,IF(A1>500,30,0))))

這個例子只有四個條件,很容易即可獲取答案。但是如果條件擴充為 7 個以上,則在 Excel 中將會受限 IF 函數的巢狀結果只能有 7 層,如此公式寫法將會傳回錯誤訊息。


(2) 使用 VLOOKUP 函數

儲存格B1:=VLOOKUP(A1,{0,0;500,30;1100,60;1600,75;2000,98},2,TRUE)

{0,0;500,30;1100,60;1600,75;2000,98} 是一個二維陣列的表示法,如果條件超過 7 個時,這個公式可以擴充,不受影響。

注意:公式中的『;』和『,』,{0,0;500,30;1100,60;1600,75;2000,98}可以看成:

Excel-多條件判斷結果(IF,VLOOKUP)

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

網友問到 Excel 的問題,如下圖一組分成 2, 3, 4, 6, 7, ... 個,如何計算分組後的小計(加總。

以下圖為例,每 2 個一組時,第01組為儲存格A2:A3、第02組為儲存格A4:A5、...,如何自動計算各個分組的小計?

Excel-計算一組幾個的小計(OFFSET,ROW)


【公式設計與解析】

儲存格D2:=SUM(OFFSET($A$2,(ROW(1:1)-1)*D$1,0,D$1,1))

複製儲存格D2,貼至儲存格D2:H14。

(ROW(1:1)-1)*D$1:向下複製時產生:

一組2個: 2, 4, 6, 8, 10, ...

一組3個: 3, 6, 9, 12, 15, ...

一組4個: 4, 8, 12, 16, 20, ...

OFFSET($A$2,(ROW(1:1)-1)*D$1,0,D$1,1):用以產生每一組的儲存格範圍。

一組2個:儲存格A2:A3、儲存格A4:A5、...

一組3個:儲存格A2:A4、儲存格A5:A7、...

一組4個:儲存格A2:A5、儲存格A6:A9、...

最後,透過 SUM 予以加總,即為小計。

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

1 2

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼