發表文章

EXCEL-GOOGLE翻譯

圖片
最近發現EXCEL可以做另一種用途,蠻有趣的,這次來介紹,EXCEL另一種用法,這不是使用函數,而是需要巨集才可以實現的,就是使用EXCEL來GOOGLE翻譯,不是EXCEL內建的翻譯,是透過EXCEL去抓GOOGLE翻譯網頁去翻譯,再把結果傳回EXCEL中,就和在GOOGLE翻譯頁面翻譯是同樣的,如下圖這樣 EXCEL結果如下 翻譯很簡單,只要去GOOGLE翻譯就可以了,為什麼還要用到EXCEL來做,多此一舉,當只有一兩句要翻譯的時候,直接去網頁翻就可以,但是如果有10,000個句子要翻譯,光(複製→貼上→翻譯→複製→再貼回)就要花超多時間,一句約4秒的話,10,000句就要花11小時以上(中間不休息的話),EXCEL只要按按鈕5分鐘內就翻完了(電腦夠給力的話),如下圖 當然不只是中翻英,A欄位(隨便)的地方是可以輸入任何語言,(B欄位)可以指定翻譯成任何語言,只要GOOGLE翻譯網頁中可以選擇的語言都可以辦到。 這個巨集中間有遇到一些BUG需要去克服的,例如:遇到特殊符號會轉成網頁的編碼,或是被GOOGLE翻譯限制只能一次翻1000筆資料,沒有特殊符號或是自己處理特殊服號的情況下翻譯大量句子是可行的。 另外GOOGLE翻譯出來的句子是否很順,這就要視GOOGLE翻譯為主,所以翻譯出來的句子是需要再經過潤飾的。

樞紐分析之群組

圖片
有一天同是來問我,要怎麼把” 金額 ”分區間統計數量,例如0~1000,1000~2000等等,可以用函數的方式來實現,簡單一點可以用樞紐分析來達成。 【步驟1】:先將金額做 樞紐分析 如下圖 【步驟2】:在紅色的區域滑鼠右鍵,選” 群組 ” 【步驟3】: 開始點 →如果要從0開始就填0。 結束點 →就是結束點。 間距值 →就是想要的間距,例如1000為一區間。 【步驟4】:結果如下 以上的方式適合” 固定 ”的間距使用,很簡單又快速,不用帶函數。 還有另一種用法 用在日期上,假如有一堆儲值的資料,想要統計出每個月的次數,也可以利用上面的方式使用,如下 原始資料: 【步驟1】一樣在紅色區域滑鼠右鍵選" 群組 ",這時候會跳出” 天”月”季”年 ”各種不同的區間。 【步驟2】選擇" 月 "之後,結果如下 【補充新範例】 假如我有一張表格是有金額有時間的每筆資料(如下圖),我想要統計每個月的儲值次數,和儲值金額的時候也可以利用群組來完成,不用特別處理儲值的時間,例如儲值時間很細,細到分秒都有,這就可以不用理他。 【步驟1】下圖是資料和樞紐(NT的地方可以拉兩次到右下角,一個選 計數 ,一個選 加總 ,就會變成中間的圖) 【步驟2】再點” 群組 ”選” 月 ”,就會得到想要的統計資料了

EXCEL-不會自動更新

圖片
前幾天同事問我為什麼公式寫好後,往下拉都不會自己更新,像下面的情況 B1寫好=A1*5,然後往下拉,照理說最後一個應該是"25",可是顯示還是”5”, 原因是EXCEL不會自己更新公式 ,至於為什麼會發生還在找原因中,當遇到這種情況時解法如下: 步驟1: EXCEL左上角的功能鍵→EXCEL選項 步驟2: 公式→活頁簿計算→勾 “自動” 步驟3: 成功了 當然還有別種解法,這是能一次解決更新的問題,當時解完後,跟我說我甚麼都沒按到,為什麼不會自動更新,我哪知道你按了什麼鬼,GOOGLE是萬能的~ㄎㄎ

樞紐分析之並排顯示

圖片
在樞紐分析中可以把同類型的放在一起顯示,基本顯示會是以下這樣 (1) 假如有一表格長下面這樣 (2) 然後使用樞紐想要顯示各商品使用次數 (3)都顯示出來了,可是3C下面是電視,公仔下面是海賊王,這樣的顯示有點不合自己想要的,心中想要的是下面這樣 (4) 樞紐顯示的方式調整一下就可以了,共2步驟: 步驟1: 點商品類型和商品名稱→欄位設定 步驟2: 在 小計與篩選 中選第二個” 無 ”。 在 版面配置與列印 中選第二個” 以列表方式顯示項目標籤 ”就完成了。 可應用的地方很多,例如角色UID和角色名稱想要並排顯示,不然又要重新對照UID或名稱。

EXCEL-取固定欄位 & 加總

圖片
  上個月遇到的問題,直到現在才有空想出來 TAT,怎麼可以那麼忙 = = 效果如下 (一)固定每3欄取數值 例如:如果想要取 每3個欄位的第3個 ,可用以下公式 =INDIRECT("B"&ROW(A1)*3) 1. 公 式:INDIRECT,有點複雜,不用懂會用就好了。 2. “B”→要取資料的欄。 3. ROW(A1)→取A1的 列 ,例如A1=1,A2=2,A100=100。 4. 整個公式結果就是 INDIRECT("B"&ROW(A1)*3)=B3 。 5. 第一個公式寫好後點右下方”+”往下拉, 第1個 =INDIRECT("B"&ROW(A1)*3)=B3 第2個 =INDIRECT("B"&ROW(A2)*3)=B6 第3個 =INDIRECT("B"&ROW(A3)*3)=B9 (二)固定每3欄加總一次 例如:如果想要取每3個欄位加總一次,可用以下公式 =SUM(INDIRECT("B"&ROW(A1)*3-2):INDIRECT("B"&ROW(A1)*3)) 1 . 公式SUM=和,INDIRECT 有點複雜,不用懂會用就好了。 2. 整個公式的結果 SUM(INDIRECT("B"&ROW(A1)*3-2):INDIRECT("B"&ROW(A1)*3))=SUM(B1:B3) 3. 第一個公式寫好後點右下方” + ”往下拉, 第1個= SUM(INDIRECT("B"&ROW(A1)*3-2):INDIRECT("B"&ROW(A1)*3))=SUM(B1:B3) 第2個= SUM(INDIRECT("B"&ROW(A2)*3-2):INDIRECT("B"&ROW(A2)*3))=SUM(B4:B6) 第3個= SUM(INDIRECT("B"&ROW(A3)*3-2):INDIRECT("B"&ROW(A3)*3...

VLOOKUP另一種用法→多條件分類

圖片
  以前VLOOKUP只會用在絕對對照的方式,最近才知道還有另一種用法【分類】。 例如:一個班級中考試分數出來了,想要將所有學生的分數分類, 59分以下”D”, 60~69→”C”, ......., 100分以上→”SS”, 當只有分及格和不及格可以用IF函數,如果想要區分更多類型的話,IF會寫的很長。 如果使用VLOOKUP可以輕鬆完成,方法如下↓↓↓ B3欄位的公式: =VLOOKUP( A3 , $D$3:$F$9 , 3 , 1 ) 第一個【A3】是要比對的資料。 第二個【$D$3:$F$9】是對照表,D3:F9然後F4鎖起來。 第三個【3】是找到後取第3欄。 第四個【1】是”模糊對照”(其中有某個數字就可以),以前都是用0”絕對對照”(就是一模一樣)。 用模糊對照的好處是, (1).當分數是10分的時候,在對照表會找不到10,但是會找對照表小於10的第一個資料”0”,然後回傳0右邊的資料”D”。 (2).當分數是75分的時候,會在對照表找第一個小於75分取70(不取60或0),然後回傳70右邊的第3欄的資料”B”回傳。 (3)利用取第一個小於條件的資料這種方式就可以利用到”條件分類”裡面,不用在寫很多層IF或是IFS。 備註:還沒發現BUG例外情況,有發現再說。

【Google 試算表】條件式格式設定

圖片
 【Google 試算表】條件式格式設定 只要為『真(true)』就(變色) 【套用範圍】 A2:G6 【指定條件】 (一樣) 1.第一 個 變色 =F2="已處理" 【模糊條件】 (包含) 1.第一 列 變色 =REGEXMATCH($F2, "處理中") 注意事項: 1.『REGEXMATCH』這個功能是用來處理模糊搜尋用的,在GOOGLE試算表裡面不能用『*』。 2.『 $ F2』前面加 $ 可以讓整列都變色,沒有加只會有第一個變色。 條件衍伸應用: 1.=and($C2<>"",$M2=""),and就是裡面2個條件都成立才會 變色 ,整串的意思是,C2不是空的(有資料)和M2是空的(沒資料),才會 變色 。