資料的排序 / 排名 RANK.EQ

如下圖,求年度業績排名

使用RANK.EQ函數,先說明如下：

NUMBER=要找出等級的數字 (所以儲存格的性質要是數值類)

REF=要排序的資料範圍

ORDER=決定遞增(KEY 1)或遞減(KEY 0或省略)排序

一般排名是用遞減,因為數值越大序號越小

比如業績總額越大,排名"數字"越小

考試總分越高,排名"數字"越小

(第1名高於第2名,但以數字概念是2>1)

假設商品銷售數量從少到多排序

或是答錯題數從少到多排名

就是用遞增的概念了

在H2下公式:

NUMBER=要找出等級的數字 (G2)

REF=要排序的資料範圍 (G2到G7,要鎖定因為要下拉填滿)

ORDER=遞減(KEY 0或省略)排序

確定後再下拉填滿到H7

完成的樣子

快速回傳資料 VLOOKUP

VLOOKUP是垂直搜尋,所以要比對的資料是以欄為單位.

何時使用呢? 

比如,計算業績獎金,業績達到什麼級距的人給多少獎金.(以下範例)

先說明內容：

LOOKUP_VALUE=要比對的資料

TABLE_ARRAY=要回傳的值所在的資料範圍

COL_INDEX_NUM=要回傳的值在上列範圍中的第幾欄

RANGE_LOOKUP=完全符合(FALSE),近似值(TRUE)

這個範例需要做二層計算

第一層：先找出ABBY的年度業績額(完全符合”ABBY”-FALSE)

第二層：再依ABBY的年度業績,對應出獎金的金額(級距概念-TRUE)

1.找出ABBY的年度總業績

LOOKUP_VALUE=L3(ABBY)

TABLE_ARRAY=B26到G31,要鎖定因為等會會下拉填滿

COL_INDEX_NUM=上列範圍(B26:G31)裡,年度業績額在第”6”欄

RANGE_LOOKUP=需要完全符合ABBY的資料(KEY入0=FALSE)

2.根據ABBY的年度業績找出應發的獎金金額

LOOKUP_VALUE=第一層的函數(ABBY年度業績額)

TABLE_ARRAY=對照表I5到J8,要鎖定因為等會會下拉填滿

COL_INDEX_NUM=上列範圍(I5:J8)裡,獎金在第”2”欄

RANGE_LOOKUP=不能完全符合(KEY入1=TRUE)

計算出ABBY的獎金=7萬,再下拉填滿就可算出所有人的獎金了

(注意-級距的對照表一定要是遞增的概念,否則無法比對)

需符合多個條件的計算 COUNTIFS 及SUMIFS 函數

如下圖

一、符合”帽T”及”超過15件”的二個條件,然後計算”次數”(或”個數”)
使用COUNTIFS:

CRITERIA_RANGE1=第一個條件”帽T”所在的範圍(B2到B27)

CRITERIA1=”帽T”

CRITERIA_RANGE2=第二個條件”超過15件”所在的範圍(數量,D2到D27)

CRITERIA2=”>15”

……如果還需要其他條件就再一直往下設定……

計算得到結果=2(次)

二、符合”球衣”及”銷售額超過5千”的二個條件,然後計算手續費的”總和”

使用SUMIFS:

SUM_RANGE=要加總的數值所在的範圍(成交手續費,F2到F27)

CRITERIA_RANGE1=第一個條件”球衣”所在的範圍(B2到B27)

CRITERIA1=”球衣”

CRITERIA_RANGE2=第二個條件”銷售額超過5千”所在的範圍(銷售總額,E2到E27)

CRITERIA2=”>5000”

……如果還需要其他條件就再一直往下設定……

計算得到結果=1414

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

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

SUMIF函數：計算所有符合指定條件的數值總和.

RANGE=要比對的條件所在的儲存格範圍.

CRITERIA=指定的條件.

SUM_RANGE=要加總的範圍.

以本例,先計算ABBY的年度業績額-產品A.

在C26格中下公式=SUMIF,進入函數視窗如下：

RANGE=要比對的條件所在的儲存格範圍—B2到B25(比對人名),要鎖定不然等會下拉填滿會出錯.

CRITERIA=指定的條件—B26(ABBY),鎖欄不鎖列.

SUM_RANGE=要加總的範圍—C2到C25(產品A的業績額),鎖列不鎖欄.

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

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

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

編輯規則

這裡的選項都可以做變動

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

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

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

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

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

如果是要移除掉重覆的人名,整理成右側的格式.首先先複製人名(B2:B27),然後到要貼上的儲存格裡貼上資料,接著到"資料"裡的"移除重複"

會詢問是否要連旁邊的資料一併移除,本範例不需要,所以選"依照目前的選取範圍排序"

確定

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

如果要計算符合所需資料的"個數""次數",用COUNTIF函數.

在F2格裡下公式=COUNTIF.

RANGE=要計算個數的範圍---我要找某人本週共簽到幾次,所以範圍要選所有的人名.由於等會要下拉填滿,選取的範圍會跑掉,所以要把這個範圍鎖定住.

CRITERIA=要比對的值---我要找小白本週簽到次數,所以選E2,下拉填滿時底下的格子正好會比對到正確的人名.

函數視窗

完成的樣子

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

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

有很多資料要拆分,填到不同格子時,可以用”資料剖析”的功能.

例如:有多筆地址,要分隔成 市/區,路/街,……

以我自己使用而言,是在分隔會計科目或一些代號時用,這樣整理過的資料可以迅速的用篩選或其他功能,依照不同的需求整理,計算……

以下分別為二種剖析的方式說明

1.固定寬度

把要剖析的資料選取

點上方”資料”裡的”資料剖析”

選”固定寬度”-下一步

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

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

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

完成的樣子

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

就會變這樣

2.分隔符號

把要剖析的資料選取

點上方”資料”裡的”資料剖析”

選”分隔符號”-下一步

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

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

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

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

完成的樣子

EXCEL好好用：一些基本介紹

有一些針對勞工開放報名的課程,政府有補助,個人覺得是蠻好的一個上課管道, https://ojt.wda.gov.tw/

最近在上EXCEL的課,也許很多人會覺得自己就會用了啊,或是上網學就可以,但由於
1.經常使用,

2.課程有補助,自己只要付不到2千,
3.想學一些整合,
4.接著想去學VBA,
所以就去報名囉~

既然都上課了,就整理一些筆記加深印象,陸續更新~

基本概念：

每個儲存格裡的資料,文字會自動靠左,數值會自動靠右.了解這一點的好處是,之後在用公式時,有一些錯誤出現單純只是因為下條件的那個格子裡不是數值……

B3格,裡面看起來是數字1,但由於該格子被設成文字格式,所以不會被加總.

在公式裡常會看到一些符號,我之前查到也只是照用,沒有去了解它的意義,現在了解了之後就不會再一頭霧水了：

“OOO“ 表示字串,所以通常看到單純” ”表示空白
&  表示連結,用上圖例,若設=A3&B3,會出現QQ1
$  表示鎖定,可以鎖欄鎖列,比如$B$3,也可以單鎖一項,比如$B3或B$3(鎖定的快速鍵是帥哥F4,按一下是鎖欄鎖列,按二下是鎖列不鎖欄,按三下是鎖欄不鎖列),鎖定的功能蠻好用的因為有時下公式,直接複製或下拉填滿時公式會跑掉,加了個鎖定就可以解決這個問題.以99乘法表為例解釋鎖定的功用

單純下公式=A3*B2,然後下拉填滿或右拉填滿,出現錯誤答案

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

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

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

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

先KEY一數值,然後到格式

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

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

就會跳出正確的寫法,之後只要直接KEY阿拉伯數字,就會自動帶出國字大寫金額,很方便~

其實原本內鍵的格式就蠻夠用的,但有時只是要加點小變化以符合需求,自訂是非常方便的功能,再多舉個例子：

先選擇所需的日期格式

會顯示為這樣

再進入自訂

把類型改成yyyy/mm/dd

就會顯示成這樣

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

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