December 30, 2008

如何利用Excel將相同的東西編號?

今天同事問我,假設有一個Excel檔有5千多筆銷貨資料,如何計算同一個人的消費次數,並在後面顯示消費次數,例如:某甲今年度共消費4次,如何在這4筆費用編上1~4號、某乙今年度共消費3次,如何在這3筆費用編上1~3號?

假設姓名欄位在A欄、消費金額在B欄,要顯示編號在B欄,只要在儲存格輸入公式=COUNTIF($A$2:A2,A2)再按下「確定」鍵,之後再複製公式往下填滿即可。



August 15, 2008

如何用Excel將完整身分證字號的後四碼置換成****

今天同事問我,如果有多筆包含完整身分證字號的資料要公佈,但是又怕公佈完整身分證字號會洩漏個資的疑慮,想要把完整身分證字號的後四碼置換成****,要如何作呢?

STEP1:假設A欄是完整身份證號碼,在B1儲存格輸入公式=LEFT(A1,6),可以擷取完整身分證字號的前六碼。

STEP2:在C1儲存格輸入四個****,並複製公式。 

STEP3:在D1儲存格輸入公式=B1&C1,可以合併B1及C1儲存格,就可以將身分證字號的後四碼置換成****。


延伸閱讀:
kiroro's Tech Blog躲開老闆的雙眼 用Excel來看股票!
kiroro's Tech Blog如何用EXCEL將身分證自動填上性別



August 13, 2008

如何用EXCEL將身分證自動填上性別

今天同事問我,他有一個3000多筆完整身分證字號的Excel檔,要在後面加上性別,問我有沒有比較快的方法,當然因為有完整身分證字號可以慢慢一筆一筆的把男女key上去,但是這樣的方法既耗時又容易發生錯誤,我們只要利用Excel函數就可以快速辦到。

假設資料在A1,只要在A2加上=IF(MID(F2,2,1)="1","男","女")這一行公式後,再按下「確定」鍵後就可以依身份證字號的第二碼來判定男女,這樣既不會發生錯誤也可節省時間喔。

延伸閱讀:
kiroro's Tech Blog躲開老闆的雙眼 用Excel來看股票!



January 22, 2007

在Excel把列與欄的資料互相交換

有時候因為特定的原因,或是一開始不小心把資料的位置輸入錯了,剛好是欄列對調的資料時,必須要欄變成列、列變成欄的時候該怎麼辦呢?這時候,可以使用「轉置」功能,一個步驟就可以輕鬆完成,不需要費力的重新輸入,就可以直接利用資料轉置的技巧來完成。

STEP1先選取要轉置的資料範圍欄位,接著按下工具列的「複製」,在要複製此轉置資料的目的地儲存格上按一下滑鼠左鍵。 

STEP2按一下滑鼠右鍵,點選快速選單中的「選擇性貼上」。「選擇性貼上」對話盒出現後,勾選「轉置」,再按下「確定」。

 STEP3現在就可以看到資料已被轉置成功,並貼到正確的位置上了。