Google 和 EXCEL 試算表

  EXCEL下拉式選單另一種呈現的方式 怕忘記所以先記錄下來備存， 有時下拉式選單很方便， 由其是處理單一種類的時候， 但是如果想要分類再分類的話就...... 剛好在網路上找到有個方法可以解決這個問題。 ->>>>> 效果 標題 選標題之後 會出現各標題的下拉式選單 不會全部都混在一起，像這樣 ->>>>> 做法 (1). 先選D1 公式->定義名稱 參照到(R)的地方先清除原來的，然後滑鼠再選D2到D4 再按確定，就完成一個"定義名稱"。 如下圖   (2). 其餘的鞋子，外套，襯衫也一樣做"定義名稱" 在"名稱管理員"裡面可以檢查每個"定義名稱" 如下圖 (3). 選A1 資料->資料驗證 儲存格內允許的地方選"清單" 來源的地方"=男裝" 按確定。 如下圖 (4). 選B1資料->資料驗證 儲存格內允許->"清單" 來源->"=INDIRECT(A1)" 按確定->是。 如下圖

最近有個朋友問我，要如何在一堆資料裡面找所有A之中的最小值， 例如在表中我要找表中的類別C，然後再找出C之中數量的最小值， 有類別C是 C200(216)，C400(514)，C600(890)，則最小值是C200(216)， 我所知道的函數裡面有COUNTIF，SUMIF，但是就是沒有MAXIF，MINIF這些函數， 要達到MAXIF和MINIF的效果的話，就需要用到陣列公式 第一步： 先定義資料表 選取儲存格A1:D10，按一下 Ctrl+Shift+F3 鍵，選【頂端列】， EXCEL就會自動定義：項目、類別、編號、數量。 第二步： 儲存格G6輸入：=MIN(IF(B2:B10=G2,D2:D10,FALSE)) 輸入完之後先別急著按ENTER或是離開公式，這時候按一下 Ctrl+Shift+F3 鍵， 公式就會自己變成{=MIN(IF(B2:B10=G2,D2:D10,FALSE))}， {}不能手動直接KEY進去，一定要按快捷建生成， 這樣就完成了，所謂的陣列公式，如下圖。 解決了朋友的問題之後，再解決下一個問題，第2個問題雖然感覺上很容易懂的敘述， 但是不用VBA巨集只靠函數方式完成真的還是需要想想，第2個問題是， 找到最小數值216之後，還要顯示最小數值的類別的所有數值， 也就是說216是屬於類別C，就要另外顯示類別C的所有數量(216、514、890)， 這也是要使用到陣列公式，做法如下 儲存格H2：{=IFERROR(INDEX(資料,SMALL(IF(類別=$G$2,項目,FALSE),ROW(1:1))+1,3),"")} 儲存格I2： { =IFERROR(INDEX(資料,SMALL(IF(類別=$G$2,項目,FALSE),ROW(1:1))+1,4),"") } 填完公式之後把H2和I2選起來，往下拉大約10格欄位左右即可，如下圖 當變換G2裡面的類別，旁邊也會一起變動，如下圖 【感想】 要在不使用巨集的情況下完成很有挑戰性，缺點是當EXCEL裡面的公式欄位越多， 開啟EXCEL就會越久，甚至切換頁面的時候也會LAG， 不過經過這次我又了解EXCEL的另外一面，真的很神奇，學也學不完， 要感謝我的朋友出的難題。 ------------------------------------------

  vlookup是excel中必會的函數，否則你就注定比別人晚下班。 最常用的地方是搜尋對應道具名稱，其二是對帳用。 (小插曲：有次面試主管問我，你會不會用EXCEL在道具表中尋找某道具名稱，我沒有說用Ctrl+F搜尋，我說用vlookup來尋找，然後他一臉點點點茫然，想了一下說我不知道然後就問下一個問題，馬上跳過，我大概說的太複雜了，說不定Ctrl+F就足夠惹) 例如：有個幾百個或是幾千個的道具編號列表，手邊有一個道具編號，想要找出對應編號的道具名稱以及其他資料，範例如下 手邊的道具編號是1004，正常對應的名稱是槌子，雙手，攻擊力35，只有一兩個道具編號的時候用搜尋又快又簡單，但是當手邊有200多個不一樣的道具編號的時候，不就要搜尋200多次然後再慢慢複製貼上，不能說笨方法，就是用時間來換結果，這時候使用函數vlookup就可以馬上解決問題得到答案。 在G3的欄位中輸入公式 =VLOOKUP(F3,$A$2:$D$8,2,0) ，馬上就會顯示”槌子”，以下是公式解讀 =VLOOKUP(F3,$A$2:$D$8,2,0) ，公式中只有4個位置要填寫 第一個 ：『F3』是想要找的道具編號。 第二個 ：要搜尋的範圍沒有鎖是這樣『A2:D8』，道具表要用$鎖起來像這樣『$A$2:$D$8』，這樣往下拉的時候公式才不會跑掉。要注意的是，範圍的列表中，第1個欄位一定要是”編號”。如果要用”物品”尋找攻擊力，那範圍的第1個就是”物品”(B2:D8)然後用$鎖起來($B$2:$D$8)。 第三個 ：『2』找道具編號對應的物品名稱，1是由第一個欄位算起，2就是第二個欄位”物品”，也就是要顯示的，如果要顯示”種類”，那就填3，以此類推。 第四個 ：『0』基本上只會用到完全比對，永遠填0就對了。 例如下圖，要尋找1004對應的攻擊力，範圍不變，那公式中的第3個欄位就填4，也就是道具表往右邊數的第4個欄位。 vlookup實際上不難，只要會填3個位置就好了，1.想搜尋的東西，2.搜尋的範圍，3.顯示第幾個，4.永遠填0，沒惹~ 最後想要搜尋全部的道具，就圈起來3個欄位然後，滑鼠放框框的右下角往下拉到底，公式就會自動全部套用，在稍微檢查幾個編號有沒有錯即可。

EXCEL第二常用的函數： COUNTIF 系列我覺得是第2最常用的，統計符合你想要的條件的數量。 =COUNTIF(範圍,指定的東西) 例如： 想要在上面表格中統計”小刀”出現的次數，答案是5 公式寫成： =COUNTIF(B:B,"小刀") 也可以這樣用，把後面(”小刀”)改成指定欄位( F2)， 然後欄位填(小刀)就會顯示小刀的數量， 欄位填(長劍)就會顯示長劍的數量，如下 ==================================== 進階用法： COUNTIFS =COUNTIF(範圍1,指定1, 範圍2,指定2, 範圍3,指定3, ....) 很簡單用，把(範圍1,指定1)當成1組一個條件來聯想，二個條件就2組，三個條件就3組。 一樣的表格，假如我想要找”小刀”且同時攻擊力是”10”的有多少個，答案是3個 公式： =COUNTIFS(B:B,"小刀",D:D,10) 分兩組，效果如下 『 B:B,"小刀" 』：在B欄中找符合"小刀"的文字(注：文字一定要用引號””) 『 D:D,10 』：在D欄中找符合10的數字(注：數字不要用引號””) 同樣的也可以把"小刀"改成指定欄位(F2)，10改成欄位裡面只有10的。 如果想要再繼續增加條件一樣的用法，後面再加一組，以此類推。 PS：一樣有任何問題可以問我，我一定會回答，但是限定在我有空閒的時候。

樞紐分析 跟 VLOOKUP 一樣辦公室工作必會的東西，統計 大量 資料的時候一定會用到，少量資料的話不要猶豫直接手動，一定比帶公式快，不要懷疑就是這樣。 先用簡單的範例說一下功能，有日期和金額的資料時，然後想要呈現每天總金額是多少的時候，像下圖顯示這樣，只要 四步驟(圈，點，點，拉) 輕輕鬆鬆就完成，不用背公式，不用輸入任何字，只要動滑鼠。 第一步(圈) ：先把兩列的資料圈起來 第二步(點) ： 上方點” 插入→樞紐分析圖案 ” 第三步(點) ： 插入→樞紐分析圖示→點已經存在的工作表→空白的地方→確定 第四步(拉) ： 在最右邊會出現日期和金額，接下來一左一右， ” 日期 ”拉到左下的” 標籤列 ”， ” 金額 ”拉到右下的” 值 ”，就完成了。 注意事項： 1.可以圈很多行或列資料，要用到的再拉到下面，意思就是多圈可被予許。 2.假如我圈了”日期，金額，UID”，但我只想要統計日期和金額，就只要拉日期和金額，UID不要管他，也行。 3.上面範例是我常用的方式，想學的人可以自己找到習慣的方式，這沒有一定的做法。

  會了使用樞紐分析的方式之後，想要再做一些進階的統計， 例如下面這張表有(日期，金額，渠道) 可以統計成，(每一日，每個渠道，的金額統計)如下圖這樣 做法： 前半段使用樞紐分析的方式一樣，先圈A1到C1，再按樞紐分析，不一樣的地方如下圖，最右邊的選項不同，分別把(日期，金額，渠道)拉到這3個地方就完成了。 PS：到這邊應該有注意到了吧 欄標籤 →會顯示在 橫 的 列標籤 →會顯示在 直 的 Σ 值 →就是中間資料要 加總 的部分，也可以是計算 次數 每個部份都可以隨意拉來拉去，自己多實驗看看，就能抓到自己的感覺。