贊助廠商

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

搜尋本部落格文章資料

目前分類:講義資料 (2086)

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

根據 Excel 資料表中(參考下左)的資料清單,想要篩選出合於類別並且和指定數量接近的資料,該如何處理?

本例要依兩個條件:類別、數量,來篩選資料。以指定數量-99~+99為接近值。

Excel-多條件篩選資料(OFFSET,SMALL,ROW)


【公式設計與解析】

選取儲存格A1:E100,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:項目、類別、編號、數量、狀態。

儲存格H2:{=IFERROR(OFFSET($A$1,SMALL(IF((類別=$G$2)*(數量<=$G$4+99)*
(數量>=$G$4-99),ROW(編號),""),ROW(1:1))-1,0),"")}

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

複製儲存格H2,貼至儲存格H2:H15。

(1) IF((類別=$G$2)*(數量<=$G$4+99)*(數量>=$G$4-99),ROW(編號),"")

條件一:(類別=$G$2),

因為指定數量-100~+100為接近值,所以:

條件二:(數量<=$G$4+99)*(數量>=$G$4-99)

其中的「*」運算相當於執行邏輯 AND 運算。

在陣列公式中,當合於二個條件者,會傳回對應的儲存格列號,否則傳回空字串。

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

利用 SMALL 函數根據第(1)式傳回的列號,由小至大取出列號。當公式向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→…。

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

將第(2)式取出的列號代入 OFFSET 函數,可以取得對應的儲存格內容。

(4) IFERROR(第(3)式,"")

當公式向下複製時,若傳回錯誤訊息,則以 IFERROR 函數轉換為空字串。

同理,

儲存格I2:{=IFERROR(OFFSET($C$1,SMALL(IF((類別=$G$2)*(數量<=$G$4+99)*
(數量>=$G$4-99),ROW(編號),""),ROW(1:1))-1,0),"")}

儲存格J2:{=IFERROR(OFFSET($D$1,SMALL(IF((類別=$G$2)*(數量<=$G$4+99)*
(數量>=$G$4-99),ROW(編號),""),ROW(1:1))-1,0),"")}

文章標籤

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

在 Excel 的工作表中,如果想要以公式來取出分隔符號(例如:*,",',~,?)內的文字,該如何處理?

參考下圖,以「*,",',~,?」為例:

Excel-取出分隔符號內的文字(SUBSTITUTE,REPT)

 

【公式設計與解析】

為了方便說明,先建立輔助欄位。

儲存格B2:=SUBSTITUTE(A2,"*",REPT(" ",20))

先利用 SUBSTITUTE 函數將分隔符號置換為20個空白字元。(20只是一個遠大於儲存格內字數的數值。而空白字元是因為原字串中都沒有空白字元。)

REPT 函數可以產生多個重覆的指定字元。

儲存格B3:=SUBSTITUTE(A3,"""",REPT(" ",20))

特別注意:若要置換「"」為20個空白字元,則公式要使用「""""」(4個")。

儲存格C2:=SUBSTITUTE(MID(B2,20,20)," ","")

若想要將兩個公式合而為一:

儲存格C2:=SUBSTITUTE(MID(SUBSTITUTE(A2,"*",REPT(" ",20)),20,20)," ","")

文章標籤

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

在 Excel 中有一個日期的清單,當要將日期加2天並且要跳過星期六、日,該如何處理?例如:

星期四加2天,應是星期六,則要調整為星期一。

星期五加2天,應是星期日,則要調整為星期二。

星期六加2天,應是星期一,則要調整為星期二。

Excel-計算隔2天日期並跳過星期六和星期日(WEEKDAY,CHOOSE)

 

【公式設計與解析】

儲存格E2:=A2+2+CHOOSE(WEEKDAY(A2,2),0,0,0,2,2,1,0)

使用 WEEKDAY 函數來傳回是星期幾。再利用 CHOOSE 函數微調日期。

Excel-計算隔2天日期並跳過星期六和星期日(WEEKDAY,CHOOSE)

文章標籤

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

在 Excel 的工作表中有一個資料清單(參考下圖左),其中的內容有部分是重覆的,如何能列出已剔除相同內容(只留下第一個)的清單(參考下圖右)?

Excel-列出資料中沒有重覆內容的項目清單(COUNTIF,OFFSET,SMALL)

 

【公式設計與解析】

這個例子,要用到一個輔助欄位(B欄)。

儲存格B2:=IF(COUNTIF($A$2:A2,A2)>1,"",ROW(A2))

複製儲存格B2,貼至儲存格B2:B19。

(1) COUNTIF($A$2:A2,A2)

利用 COUNTIF 函數計算每個儲存格由儲存格A2起始的儲存格範圍裡,共有幾個相同內容的儲存格數量。

(2) IF(第(1)式>1,"",ROW(A2))

如果傳回數值大於 1,則傳回空白,否則(=1)傳回該儲存格的列號。

 

儲存格C2:=IFERROR(OFFSET($A$1,SMALL($B$2:$B$19,ROW(1:1))-1,0),"")

複製儲存格C2,貼至儲存格C2:C19。

(1) SMALL($B$2:$B$19,ROW(1:1))

利用 SMALL 函數由小至大依序找出對應的數值。

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

將第(1)式傳回的數值代入 OFFSET 函數,找出對應的儲存格內容。

(3) IFERROR(第(2)式,"")

若公式傳回了錯誤訊息,則以空白顯示。

文章標籤

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

接著前一篇文章:Excel-利用政府資料開放平臺練習資料處理,來看看有那些資料可以拿來練習。

例如:取用「農村地方美食小吃特色料理」所提供的資料,下載XML檔。(本例:https://data.gov.tw/dataset/6037

Excel-利用政府資料開放平臺練習資料處理(Google地圖和Google地球)

要開啟這個下載的XML檔,你必須啟動「開發人員」表功能。(點選「檔案/選項」,再由「自訂功能區」中勾選「開發人員」。)

接著,選取「開發人員」功能表中的「匯入」,再選取下載的XML檔。

Excel-利用政府資料開放平臺練習資料處理(Google地圖和Google地球)

開啟檔案後,如下圖:

Excel-利用政府資料開放平臺練習資料處理(Google地圖和Google地球)

稍加整理,留下想要的資料:

Excel-利用政府資料開放平臺練習資料處理(Google地圖和Google地球)

利用工作表中的經度和緯度資料,設定公式可以在Google地圖中顯示該地點。

儲存格Y2:=HYPERLINK("https://www.google.com.tw/maps/place/"&V2,B2)

複製儲存格Y2,貼至儲存格Y2:Y100。

Excel-利用政府資料開放平臺練習資料處理(Google地圖和Google地球)

在工作表中點選一個地點的 Google 地圖超連結,即會在地圖上顯示該地點的位置。

Excel-利用政府資料開放平臺練習資料處理(Google地圖和Google地球)

 

例如:取用「颱風消息與警報-颱風消息」所提供的資料,下載KML檔。(本例:https://data.gov.tw/dataset/10063

Excel-利用政府資料開放平臺練習資料處理(Google地圖和Google地球)

如果你有安裝Google地球(https://www.google.com/intl/zh-TW/earth/desktop/),點選這個KML檔,即可看到颱風的走向預測等資訊。

現在(2018/7/11 10:30),瑪莉亞颱風還在我們的附近,我因放颱風假可以專心寫文章。

Excel-利用政府資料開放平臺練習資料處理(Google地圖和Google地球)

有興趣可以看看颱風誕生到現在的路徑:

Excel-利用政府資料開放平臺練習資料處理(Google地圖和Google地球)

 

例如:取用「新北市電動汽車充電站-新店區」所提供的資料,下載KML檔。(本例:https://data.gov.tw/dataset/79920

Excel-利用政府資料開放平臺練習資料處理(Google地圖和Google地球)

Excel-利用政府資料開放平臺練習資料處理(Google地圖和Google地球)

 

例如:取用「桃園市河濱自行車道資料」所提供的資料,下載其中一個KMZ檔。(本例:https://data.gov.tw/dataset/26119

Excel-利用政府資料開放平臺練習資料處理(Google地圖和Google地球)

在 Google 地球中開啟所下載的 KMZ 檔(軌跡檔),

Excel-利用政府資料開放平臺練習資料處理(Google地圖和Google地球)

按下「播放遊覽」,你就可以在螢幕上先把這一段的自行車道遊覽一下。顯示的高度變化也可以做為評估。

Excel-利用政府資料開放平臺練習資料處理(Google地圖和Google地球)

文章標籤

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

如果你想要運用 Excel 來練習資料處理,而找不到可利用的範例,可以進到政府資料開放平臺來取資料。政府資料開放平臺(https://data.gov.tw)提供了各類型的資料供民眾可以下載使用。

Excel-利用政府資料開放平臺練習資料處理

這個平台的各種資料使用了各式各樣的格式,例如:CSV、PDF、TXT等。

Excel-利用政府資料開放平臺練習資料處理

隨意取一個資料,例如:學生體重平均值(6歲-15歲),其提供了「CSV」格式的檔案。下載這個CSV檔。

Excel-利用政府資料開放平臺練習資料處理

開啟這個 CSV 檔,其內容如下圖:

Excel-利用政府資料開放平臺練習資料處理

先刪除不要的資料:

Excel-利用政府資料開放平臺練習資料處理

利用其提供的資料,轉換為下圖右:

儲存格G2:=SUMPRODUCT((學年度=$F3)*(年齡=$G$1)*INDIRECT(G$2))

Excel-利用政府資料開放平臺練習資料處理

另一種轉換:

儲存格G2:=SUMPRODUCT((年齡=$F3)*(學年度=$G$1)*INDIRECT(G$2))

Excel-利用政府資料開放平臺練習資料處理

再轉換為另一種格式:

儲存格G2:=SUMPRODUCT((年齡=$F3)*(學年度=G$2)*INDIRECT($G$1))

Excel-利用政府資料開放平臺練習資料處理

這些數據應該都是真實的,但不一定是最新的。不過,隨意取來練習資料處理,說不定可以讓你分析出令人意外的結果啊!

文章標籤

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

在 Excel 的工作表裡有一個資料表,如何判斷同一列中是否有重覆的內容?

參考下圖,資料的第一列中的「AAAA」有重覆出現,則判定為「O」;若沒有重覆出現,則判定為「X」。

Excel-找出儲存格範圍內是否有儲存格重覆(SUMPRODUCT,COUNTA,COUNT)

 

【公式設設與解析】

儲存格F2:

=IF(SUMPRODUCT(COUNTIF(A2:D2,A2:D2))>COUNTA(A2:D2),"O","X")

複製儲存格F2,貼至儲存格F2:F9。

(1) COUNTIF(A2:D2,A2:D2)

SUMPRODUCT 函數可以執行陣列運算,此公式可以找出儲存格A2:D2裡,每個儲存格內容的數量。傳回陣列:{3, 3, 1, 3}。

(2) SUMPRODUCT(COUNTIF(A2:D2,A2:D2))

SUMPRODUCT 函數裡將第(1)式的內容予以加總,本例為:3+3+1+3=10。

(3) IF(第(2)式>COUNTA(A2:D2),"O","X")

COUNTA(A2:D2)為計算儲存格的數量。若第(2)式的傳回值大於儲存格的數量,則必有至少2個儲存格內容重覆。

文章標籤

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

參考下圖,在 Excel 中有一個報名日期和報名人員的資料表(下圖左),由於每個人員有多次報名,如何找出每一個人員的最後一次報名日期(下圖右)?

Excel-找出日期清單中每個人員最後報名日期(OFFSET,MAX,SUBSTITUTE)

 

【公式設計與解析】

儲存格E2:{=OFFSET($A$1,MAX((SUBSTITUTE($B$2:$B$11,D2,"")<>
$B$2:$B$11)*ROW($B$2:$B$11))-1,0)}

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

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

(1) SUBSTITUTE($B$2:$B$11,D2,"")

陣列公式中,利用 SUBSTITUTE 函數將每一個儲存格內容,含有儲存格D2內容者,全部置換成空字串。

(2) SUBSTITUTE($B$2:$B$11,D2,"")<>$B$2:$B$11)

判斷第(1)式的傳回結果和原來儲存格陣列內容是否相符,傳回 TRUE/FALSE 陣列。

本例儲存格D2的內容為「甲」,所以傳回 FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE

(3) 第(2)式*ROW($B$2:$B$11)

將第(2)式乘上儲存格陣列中每一個儲存格的列號(例如:ROW(B2)=2、ROW(B3)=3、...、ROW(B11)=11),在運算過程中 TRUE/FALSE 陣列會轉換為 1/0 陣列。

所以,傳回的結果即為含有儲存格D2內容的列號。本例結果傳回 0, 3, 0, 0, 0, 0, 0, 0, 0, 11。

(4) MAX(第(3)式)

利用 MAX 函數將第(3)式傳回的列號取其中的最大值。

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

將第(4)式傳回的列號最大值,代入 OFFSET 函數求得在第A欄中對應的日期。

文章標籤

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

在使用 Word 文件時插件表格,有時會希望能固定表格在頁面中的特定位置,或是希望表格暫時不要出現,該如何處理?

如下圖,其中的表格希望能固定於特定位置,參考以下做法:

1. 在表格中按滑鼠右鍵,並選「表格內容」選項。

Word文件中如何固定表格位置與隱藏表格

2. 在「表格內容」對話框中的選取「文繞圖」。

3.再選取「位置」。

Word文件中如何固定表格位置與隱藏表格

4. 在「表格的位置」對話框中,取消勾選「隨段落文字移動」。

5. 然後,在「相對於」中選取「邊界」或「整個頁面」,並設定一個想要的位置。

Word文件中如何固定表格位置與隱藏表格

如果在文件輸入其他文字,即使表格被擠到下一頁,仍會固定於該頁面於先前設定的位置。

Word文件中如何固定表格位置與隱藏表格

如果你想讓表格暫時隱藏,則可以選取整個表格(例如下圖中綠色表格):

Word文件中如何固定表格位置與隱藏表格

1. 按 Ctrl+Shift+H 鍵,設定表格為隱藏內容。

2. 當按 Ctrl+Shitt+8Ctrl+*) 鍵,即可切換這個表格的隱藏/顯示。

Word文件中如何固定表格位置與隱藏表格

文章標籤

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

Google 地圖最近的改版,在電腦版上已可以搜尋指定區域附近的餐廳、飯店、酒吧、咖啡等,而且會隨之顯示該區域的天氣和溫度。參考下圖,會顯示地區的溫度和天氣狀態。(應該不是即時資訊,而是預測資訊吧!)

直接由Google地圖上查詢天氣概況

看來是以行政區和景點來顯示資訊,對照中央氣象局的 App 氣象資訊,不見得吻合。但是,天氣資訊和溫度誰說的準呢?!天氣只是參考性質,但是直接在地圖上顯示天氣資訊,可以省去在Google搜尋框中輸入地區名稱。

直接由Google地圖上查詢天氣概況

直接由Google地圖上查詢天氣概況

直接由Google地圖上查詢天氣概況

直接由Google地圖上查詢天氣概況 直接由Google地圖上查詢天氣概況
文章標籤

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

在 Excel 中有一個日期清單,如何能依據相同日期給予流水號?

如下圖,日期是由小至大排列,每個日期的數量不一,希望能將相同日期給予三碼的流水號。

Excel-在日期清單中依相同日期加上流水號(TEXT,COUNTIF)

【公式設計與解析】

儲存格C2:=TEXT(A2,"yyyy/mm/dd")&"-"&TEXT(COUNTIF($A$2:A2,A2),"000")

複製儲存格C2,貼至儲存格C2:C26。

(1) TEXT(A2,"yyyy/mm/dd")

利用 TEXT 函數將儲存格A2的日期以「年4碼/月2碼/日2碼」的格式呈現。

(2) TEXT(COUNTIF($A$2:A2,A2),"000")

先利用 COUNTIF 計算儲存格A2起始至目前儲存格A2中相同日期有幾個。

再利用 TEXT 函數將數值以「3碼補0」的格式呈現。

(3) 第(1)&"-"&第(2)式,即為所求。

 

如果你的日期並不是相同日期排在一起,該公式仍會得到相同結果。

Excel-在日期清單中依相同日期加上流水號(TEXT,COUNTIF)

文章標籤

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

在 Excel 中,如下圖左的資料清單,如依據日期和項目計算加總?

Excel-增加清單項目不需改變加總計算的公式(SUMPRODUCT,OFFSET,COUNT)

【公式設計與解析】

儲存格F2:

=SUMPRODUCT(($A$2:$A$23=$E2)*($B$2:$B$23=F$1)*$C$2:$C$23)

複製儲存格F2,貼至儲存皮F2:H8。

利用 SUMPRODUCT 函數,只要一個式子即完成所有計算。

 

但是,如果清單項目會持續的增加,要如何能不改變加總計算的公式,也可自動得到正確結果?(參考下圖)

Excel-增加清單項目不需改變加總計算的公式(SUMPRODUCT,OFFSET,COUNT)

因為清單項目會持續的增加,所以可以利用名稱定義來決定儲存格範圍。

定義名稱:

日期:=OFFSET(工作表2!$A$1,1,0,COUNT(工作表2!$A:$A),1)

項目:=OFFSET(工作表2!$B$1,1,0,COUNT(工作表2!$A:$A),1)

數量:=OFFSET(工作表2!$C$1,1,0,COUNT(工作表2!$A:$A),1)

COUNT(工作表2!$A:$A)可以決定共有幾筆資料,再利用 OFFSET 函數決定資料的儲存格範圍。

image

修改公式:

儲存格F2:=SUMPRODUCT((日期=$E2)*(項目=F$1)*數量)

文章標籤

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

在 Excel 中,如何根據起迄日期計算二個日期區間裡每個星期幾的個數?參考下圖。

Excel-計算二個日期區間裡每個星期幾的個數(SUMPRODUCT,WEEKDAY)

 

【公式設計與解析】

儲存格C2:=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(N($A2)&":"&
N($B2))),2)=COLUMN(A:A))*1)

 

複製儲存格C2,貼至儲存格C2:I17。

(1) INDIRECT(N($A2)&":"&N($B2))

N($A2):將起日的日期轉換為數字。

N($B2):將迄日的日期轉換為數字。

利用 INDIRECT 函數將 N($A2)&":"&N($B2) 轉換為儲存格範圍。

例如:100:200

(2) ROW(第(1)式)

將第一式傳回的儲存格範圍代入 ROW 函數,得到一個列的區間。

例如:ROW(100:200)

(3) WEEKDAY(第(2)式,2)

利用 WEEKDAY 函數將第(2)式傳回的儲存格範圍(數值),利用參數「2」,使其傳回值 1~7 對應至星期一~星期日。

Excel-計算二個日期區間裡每個星期幾的個數(SUMPRODUCT,WEEKDAY)

(4) 第(3)式=COLUMN(A:A)

判斷第(3)式傳回值是否和COLUMN(A:A)=1相同,傳回 TRUE /FALSE 陣列。

當公式向右複製時,COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:C)→...。

(5) SUMPRODUCT(第(4)式*1)

其中的「*1」運算可以將 TRUE/FALSE 轉換為 1/0。在 SUMPRODUCT 函數將 1/0 加總,即為所求。

文章標籤

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

在教學現場,每次上課都要記錄學生學習狀況,為了方便、快速,也不想分分計較,所以會使用代碼來記錄。最後在學期末時,要如何將這些代碼轉換為分數?

例如,對照以下的代碼和分數來轉換:

代碼:A缺課 P玩手機 D遲到 C未關機 S睡覺 V實作佳 O實作可 X實作差

分數:A標記X P扣20 D扣10 C扣15 S扣10 V得85 O得75 X得60   

Excel-教師將平時標記的符號轉換為分數(SUBSTITUTE,SUMPRODUCT)

 

【公式設計與解析】

根據訂定的代碼和分數來轉換:

代碼:A缺課 P玩手機 D遲到 C未關機 S睡覺 V實作佳 O實作可 X實作差

分數:A標記X P扣20 D扣10 C扣15 S扣10 V得85 O得75 X得60

Excel-教師將平時標記的符號轉換為分數(SUBSTITUTE,SUMPRODUCT)

儲存格N2:=IF(B2="A","X",SUMPRODUCT((SUBSTITUTE(B2,{"V","O","X"},"")
<>B2)*{85,75,60})-SUMPRODUCT((SUBSTITUTE(B2,{"D","C","S","P"},"")<>B2)*
{10,15,10,20}))

複製儲存格N2,貼至儲存格X37。

(1) IF(B2="A","X", ...)

判斷如果儲存格內容為「A」,表示缺課,標記為「X」。

(2)SUMPRODUCT((SUBSTITUTE(B2,{"V","O","X"},"")<>B2)*{85,75,60})

如果儲存格內容不是為「A」,先計算得分項目:

利用:SUBSTITUTE(B2,{"V","O","X"},"") 來將儲存格B2的內容分別置換 V、O、X 為空白。其中 {"V","O","X"} 為定數陣列的寫法,同一個公式可以做三次動作。

再利用 SUBSTITUTE(B2,{"V","O","X"},"")<>B2 來判斷置換後的結果是否與原內容相符,傳回 TRUE/FALSE 。(如果相符,代表含有該字元,若不相符,表示不含有該字元。而且,傳回的結果必定只有一個 TRUE、二個 FALSE。)

於 SUMPRODUCT 函數執行 (SUBSTITUTE(B2,{"V","O","X"},"")<>B2)*{85,75,60})運算,結果會傳回 85、75、60 其中一個數值。

(3) SUMPRODUCT((SUBSTITUTE(B2,{"D","C","S","P"},"")<>B2)*{10,15,10,20}))

原理同第(2)式,計算扣分項目。

文章標籤

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

如何在 Excel 的工作表中指定年和月,即可產生該月的月曆表?

參考下圖:

1. 其中日期儲存為完整日期(年/月/日),但是只顯示「日」的數值。

2. 星期六和星期日的儲存格顯示不同色彩。

根據年和月產生月曆並標示星期六日(DATE,RIGHT,TEXT)


【公式設計與解析】

儲存格B3:=DATE($A$2,$B$2,COLUMN(A:A))

根據設定的年和月,藉由 DATE 函數轉換為日期格式。公式向右複製時,COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:C)=3→ …..。

儲存格B4:=RIGHT(TEXT(B3,"[$-zh-TW]aaaa;@"),1)

利用 TEXT 函數設定日期格式:星期一。再以 RIGHT 函數取出最後一個字元。

複製儲存格B3:B4,貼至儲存格B3:AF4。

根據年和月產生月曆並標示星期六日(DATE,RIGHT,TEXT)

如果要考量不同月份會有不同的日數,例如:28、30、31。修改公式:

儲存格B3:=IF(DATE($A$2,$B$2,COLUMN(A:A))>=DATE($A$2,$B$2+1,1),"",
DATE($A$2,$B$2,COLUMN(A:A)))

若要設定星期六和星期日顯示不同的儲存格色彩,則:

首先,選取儲存格B5:AF14。

接著,設定儲存格的格式化條件。

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

編輯規則:=WEEKDAY(B$3,2)>5

設定格式:儲存格底色為粉紅色

根據年和月產生月曆並標示星期六日(DATE,RIGHT,TEXT)

文章標籤

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

在 Excel 的工作表中有一個數值清單,要根據每個數值的最後一碼做不同的調整。

條件:(參考下圖)

最後一碼是奇數時,進位為偶數。

最後一碼為7、8、9時,進位為10。

Excel-根據數值最後一碼調整(CHOOSE,MOD)

 

【公式設計與解析】

儲存格C2:=A2+CHOOSE(MOD(A2,10)+1,0,1,0,1,0,1,0,3,2,1)

複製儲存格C2,貼至儲存格C2:C16。

MOD(A2,10):找出儲存格A2的個位數(0, 1, 2, .... ,9)

利用 CHOOSE 函數對 0~9 要加上的數值,即為所求。

如果你想運用 Excel 提供的進位函數,可以參考這篇:Excel-各種進位方式

文章標籤

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

 

例如,當你在 Excel 的工作表中取得一個金額的清單,其中每個金額都附上了單位,該如何計算其總計金額?

如下圖,原始資料中的金額後都有單位:元,以這個清單如何直接計算加總?

Excel-對含有單位的儲存格加總數值(SUMPRODUCT,SUBSTITUTE)

 

【公式設計與解析】

儲存格C2:=SUMPRODUCT(SUBSTITUTE(A2:A20,"元","")*1)&"元"

首先,在 SUMPRODUCT 函數中,利用 SUBSTITUTE 函數將儲存格A2:A20裡的「元」,全部置換為空白字元。

因為,SUBSTITUTE 函數傳回的結果是文字,所以再以「*1」運算,將文字數字轉換為數值數字。

最後,以 SUMPRODUCT 函數加總,並且串接(& )字串「元」。

文章標籤

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

在 Excel 的工作表中,如下圖的數值清單裡,要如何計算編號為 3 的倍數者,其大於50的個數有幾個,該如何處理?

Excel-計算編號為N的倍數且數值大於M的個數(SUMPRODUCT,MOD,ROW)

 

【公式設計與解析】

儲存格D2:=SUMPRODUCT((MOD(ROW(B2:B26),3)=1)*(B2:B26>50))

SUMPRODUCT 函數裡,使用兩個條件式來運算。

(1) 條件一:MOD(ROW(B2:B26),3)=1

判斷 MOD(ROW(B2:B26),3)= 1 是否成立,傳回 TRUE/FALSE 陣列。

因為 ROW 函數會傳回儲存格的列號,所以 ROW(B2)=2、ROW(B3)=3、ROW(B4)=4、……。

MOD(ROW(B2),3)=2、MOD(ROW(B3),3)=0、MOD(ROW(B4),3)=1、......。

因為編號 A003 在儲存格B4,所以利用 MOD(ROW(B2:B26),3)=1 來判斷編號是否為 3 的倍數。

(2) 條件二:B2:B26>50

判斷儲存格 B2:B26 的內容是否大於 50,傳回 TRUE/FALSE 陣列。

(3) ((MOD(ROW(B2:B26),3)=1)*(B2:B26>50)

其中的「*」運算會將 TRUE/FALSE 陣列轉換為 1/0 陣列。

最後於 SUMPRODUCT 函數中將 1/0 陣列相加,結果即會符合二個條件的個數。

>

文章標籤

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

 

在 Excel 中如果想要執行儲存格中的運算式可以利用 EVALUATE 函數,但是要把檔案儲存為巨集活頁簿(*.xlsm)。

以下圖為例,輸入字串: $9,861-$6,296,要直接得到結果:$3,565,該如何處理?

Excel-執行儲存格中的運算式(SUBSTITUTE,EXT,EVALUATE)

 

【公式設計與解析】

先將儲存格中的字串加以轉換。(將「X」取代為「*」、將「$」和「,」取代為空白)

儲存格B2:=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"X","*"),"$",""),",","")

複製儲存格B2,貼至儲存格B2:B9。

接著,定義名稱。

名稱:Cal

參照到:=EVALUATE(工作表1!B2)

Excel-執行儲存格中的運算式(SUBSTITUTE,EXT,EVALUATE)

儲存格C2:=TEXT(Cal,"$#,##0")

將計算結果套用格式:$#,##0。

複製儲存格C2,貼至儲存格C2:C9。

文章標籤

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

在 Excel 中有一個資料清單(如下圖左),如果想要重組其中的文字(如下圖右),該如何處理?

在下圖中,要重組其中的文字,主要是要把英文後的數字移到英文字之前,再把「-」去除。其中兩組數字,可能為一碼,也可能為二碼。

Excel-字串重組(MID,LEFT,FIND)

 

【公式設計與解析】

儲存格C2:

=MID(A2,2,FIND("-",A2)-2)&LEFT(A2,1)&MID(A2,FIND("-",A2)+1,99)

複製儲存格C2,貼至儲存格C2:C24。

(1) FIND("-",A2)

找尋儲存格A2的內容裡,「-」的位置。(本例為:3)

(2) MID(A2,2,FIND("-",A2)-2)

取出英文字之後,「-」之前的數字。(本例為:1)

(3) LEFT(A2,1)

取出英文字。(本例為:A)

(4) MID(A2,FIND("-",A2)+1,99)

取出「-」之後的數字。(本例為:12)

文章標籤

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

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼