2019年5月14日 星期二

設定格式化的條件 及 計算符合指定條件的總額(SUMIF函數)

如下圖,已用SUM函數計算出每人每季的業績額,要計算出年度總業績金額.


SUMIF函數:計算所有符合指定條件的數值總和.
RANGE=要比對的條件所在的儲存格範圍.
CRITERIA=指定的條件.
SUM_RANGE=要加總的範圍.

以本例,先計算ABBY的年度業績額-產品A.
C26格中下公式=SUMIF,進入函數視窗如下:
RANGE=要比對的條件所在的儲存格範圍—B2B25(比對人名),要鎖定不然等會下拉填滿會出錯.
CRITERIA=指定的條件—B26(ABBY),鎖欄不鎖列.
SUM_RANGE=要加總的範圍—C2C25(產品A的業績額),鎖列不鎖欄.

確定後再下拉填滿,右拉填滿.

有時候會需要做一些醒目標示之類的,就可以用設定格式化的條件

設定好的條件也可以做修改,進入設定格式化的條件”—“管理規則”—“編輯規則

編輯規則

這裡的選項都可以做變動

2019年5月10日 星期五

移除重覆的資料 及 計算符合指定條件的個數(COUNTIF函數)

如下圖範例,要計算每個人一週的簽到次數

以前我用很笨的方法就是設篩選,然後一個個數有幾個人......無言

如果只是要看有沒有重覆的人名,可以選"常用"下的"設定格式化的條件"

裡面有一些內建的項目,選擇"只格式化唯一或重複的值",然後選擇一個要用的格式,就會把重複的資料標色了.


如果是要移除掉重覆的人名,整理成右側的格式.首先先複製人名(B2:B27),然後到要貼上的儲存格裡貼上資料,接著到"資料"裡的"移除重複"
會詢問是否要連旁邊的資料一併移除,本範例不需要,所以選"依照目前的選取範圍排序"

確定


這裡會告訴你共有幾筆資料,點確定後便會移除重覆

如果要計算符合所需資料的"個數""次數",用COUNTIF函數.
在F2格裡下公式=COUNTIF.

RANGE=要計算個數的範圍---我要找某人本週共簽到幾次,所以範圍要選所有的人名.由於等會要下拉填滿,選取的範圍會跑掉,所以要把這個範圍鎖定住.
CRITERIA=要比對的值---我要找小白本週簽到次數,所以選E2,下拉填滿時底下的格子正好會比對到正確的人名.
函數視窗

完成的樣子
CRITERIA不鎖定,下拉後就會自動符合所需對應的格子

2019年5月8日 星期三

如何拆開EXCEL儲存格的資料? 資料剖析

有很多資料要拆分,填到不同格子時,可以用資料剖析的功能.
例如:有多筆地址,要分隔成 /,/,……
以我自己使用而言,是在分隔會計科目或一些代號時用,這樣整理過的資料可以迅速的用篩選或其他功能,依照不同的需求整理,計算……
以下分別為二種剖析的方式說明

1.固定寬度
把要剖析的資料選取

點上方資料裡的資料剖析

固定寬度”-下一步

在要分隔的地方點一下,產生分隔線(可以調整或移除)-下一步

若要直接在原本的格子做分隔,此處不須動作,直接點完成


補充:黑色的地方表示選取,可以在上方資料格式裡選擇樣式及保不保留,每一個分隔的部份都可以做這些調整


完成的樣子

如果要填到選定的格子,目標儲存格點一下,然後去點選要放資料的儲存格-完成

就會變這樣

2.分隔符號

把要剖析的資料選取
點上方資料裡的資料剖析

分隔符號”-下一步

分隔符號選取要做分界點的符號,沒有的話就自己KEY其他-下一步

若要直接在原本的格子做分隔,此處不須動作,直接點完成
如果要填到選定的格子,目標儲存格點一下,然後去點選要放資料的儲存格-完成


補充:黑色的地方表示選取,可以在上方資料格式裡選擇樣式及保不保留,每一個分隔的部份都可以做這些調整

完成的樣子


2019年5月7日 星期二

EXCEL好好用:一些基本介紹


有一些針對勞工開放報名的課程,政府有補助,個人覺得是蠻好的一個上課管道, https://ojt.wda.gov.tw/
最近在上EXCEL的課,也許很多人會覺得自己就會用了啊,或是上網學就可以,但由於
1.經常使用,
2.課程有補助,自己只要付不到2,
3.想學一些整合,
4.接著想去學VBA,
所以就去報名囉~
既然都上課了,就整理一些筆記加深印象,陸續更新~



基本概念:
每個儲存格裡的資料,文字會自動靠左,數值會自動靠右.了解這一點的好處是,之後在用公式時,有一些錯誤出現單純只是因為下條件的那個格子裡不是數值……
B3,裡面看起來是數字1,但由於該格子被設成文字格式,所以不會被加總.
在公式裡常會看到一些符號,我之前查到也只是照用,沒有去了解它的意義,現在了解了之後就不會再一頭霧水了:
“OOO“ 表示字串,所以通常看到單純” ”表示空白
表示連結,用上圖例,若設=A3&B3,會出現QQ1
表示鎖定,可以鎖欄鎖列,比如$B$3,也可以單鎖一項,比如$B3B$3(鎖定的快速鍵是帥哥F4,按一下是鎖欄鎖列,按二下是鎖列不鎖欄,按三下是鎖欄不鎖列),鎖定的功能蠻好用的因為有時下公式,直接複製或下拉填滿時公式會跑掉,加了個鎖定就可以解這個問題.99法表為例解鎖定的功用
純下公式=A3*B2,然後下拉填滿或右拉填滿,出現錯誤答案

正確要=$A3*B$2,才能弄出正確答案

常常在一堆資料裡看到格子左上有小綠三角,雖然沒影響但有時看著煩人,不如處理掉它:


點到該格子,會出現一個 ! 的圖案,點一下,忽略錯誤”,小綠三角就會不見了.

自訂格式:
以我之前印銀行存款,取款,匯款單或開支票時用的範例說明


KEY一數值,然後到格式


先到特殊選到國字金額樣式

再到自訂,中間的類型,內容不改,在最後加上字串元整”,按確定.


就會跳出正確的寫法,之後只要直接KEY阿拉伯數字,就會自動帶出國字大寫金額,很方便~
其實原本內鍵的格式就蠻夠用的,但有時只是要加點小變化以符合需求,自訂是非常方便的功能,再多舉個例子:


先選擇所需的日期格式

會顯示為這樣

再進入自訂

把類型改成yyyy/mm/dd

就會顯示成這樣
這樣,當有很多資料時,所有的日期長度都會一樣,看起來較整齊.

原來做筆記很累……好難KEY,只寫幾個簡單的概念就有虛累累的感覺……真心感謝網路上很多人的分享,讓我能學到很多東西~^^~


2018年9月18日 星期二

里里落落的一些......

1994/5/10

歸人沉重的腳步聲
驚醒了我
我倏地起身
著上我亮眼的紅
站在窗邊 迎著風
眼裡映著你的笑
和著燦爛的陽光 灑了一地

驀然回首
黑 吞噬了我 沉浸了我

一睜眼
看見床頭的相片
才了解 又是一個同樣的夢


1995/1/10

應該是累了吧,窩在沙發裡不肯動。
你已經三天又七個小時十四分鐘十一秒沒打電話給我,是怎麼了?

「鈴!」倏地接起它,渴望的心卻冷了。
「打錯了!」狠狠的放下電話,開了瓶啤酒,點一根煙,聽著收音機傳來的聲音:

「你把我灌醉,你讓我流淚......」

不久,我真的醉了、哭了,原來平時認為無病呻吟的情歌,竟是如此真實。

只能怪自己笨,滿心快樂的赴約,卻沒注意到那天是 四月一日,街上只有我這個呆子,捧著一大束玫瑰從白天等到黑夜。失落的回家後,看見牆上的日曆,才明白在你的心中,我一直是個”愚人”,連分手這二個字,你也不屑對我說。


1995/6/16

從來都沒想到 言語的力量是可以這麼震攝人的
你的聲音到現在 還繚繞在我心間
你已給了我承諾 但我卻膽怯
不是不喜歡你 只是現實逼的我太緊
不是不渴望愛情 只是壓力迫的我抬不起頭
其實我也不知道要說什麼
好似有千言萬語哽在心頭
卻無法化成字句 向你訴說
你也有相同的感受嗎
那或許是我們對愛情懂的不夠 尚未成熟
前面的路是崎嶇坎坷艱辛無比的
你還有勇氣繼續走嗎
如果有 請記得拉我一把
讓我們一起走
或許在途中 我會離開你 或你會先丟下我
但希望當我們驀然回首
都不會後悔曾經攜手走過的一切


1996/1/4

你是第一個 我愛得如此深的人
你是第一個 我想要依靠一輩子的人
你說希望能帶給我快樂 分享我所有的心事
我也希望能給你所有 帶給你一些收獲
不要害怕我的眼神 那是我不知所惜的流露
別擔心我有話不肯說 只要是你 我會坦白我的所有
別總是說那杞人憂天的話
難道你對我 沒有信心
我不會輕易放棄你
如同你珍惜我一樣
別讓烏雲籠罩你 我的陽光


1997/8/21

昨天實在是頂愉快的一天,雖然在塞車陣中停了好幾個小時。
你開車不知道會不會覺得很煩哩?
許久不見,再見面時那感覺……不知要如何形容。

不知怎的,每次接到你的信,或是想到你時,甚至是見到你了,都會有一種”虧欠”的感覺,想要補償你什麼,可是、又怎樣呢……

一開始,或許是尷尬吧,我們都有些沉默,可後來也慢慢多話了。突然想到過去的我們,好像從未相處過這麼長的時光,對吧?不知為何、昨天的我一直想到以前—高三最末那幾個月的往事。

回家前,我提出了去海邊的意見,其實、我一直想和你去一遭,去了了那個我們曾熱切盼望,卻未能實現的夢。看著你的側臉,突然有股莫名的衝動想說:讓我再做你五分鐘的情人好嗎?或許,是那段從前有著太多缺憾吧,才會有這奇怪的念頭。

雖然、一切已成往昔,但追憶仍是最美的。友誼之路,或許對你我而言,是更長遠的抉擇吧!



(現在再回頭看當時的決定,十個有八個會後悔~可惜人生不能重來......不過也就是因為這樣,現在決定要做任何事前都會想的更多,這、就是成長的一種吧~)