Excel教學

教你使用Excel處理各樣問題

Match教學影片(3) Case Sensitive(分大小寫) lookup

這條短片會教大家使用Match Case Sensitive(分大小寫) lookup。 如果你對Index和Match有其他疑問,歡迎在這裏留言,Excel爸打很樂意回答你。你也可以subscribe這條youtube頻道,收看更多有關Excel的教學短片。 按下圖收看教學影片: 以下為影片內容全文: 大家好,Excel爸打之前拍過一段影片,是教大家如何用Index+Match功能的。亦拍過另一段影片教大家如何用Index+Match來做向左的lookup。今次這段影片則教大家如何用Index+Match功能來做Case Sensitive效果。 首先這裏有些data,是跟之前一樣的。那我作少許改動,把Banana改為啤梨,這個啤梨改為大階R。然後在這個位置,輸入大階R的啤梨。之後若是用之前的Match功能,則選取這個value、這個array,輸入「0」,即exact match。這樣便會得出2。 那是什麼意思?即是說Excel的這個Match功能將這個大階R的啤梨,跟細楷r的啤梨視為同一項資料。即是說它是non-case sensitive,即是沒有case sensitive。即是將大階R和細楷r都視作一樣的意思。 那若我想做到大細楷都exactly匹配,可以怎麼做?那我們在這裏用另一個Match功能。這次的輸入會有些許不同,第一個value要輸入「true」,然後要輸入「exact」。然後text1要選取這個,text2則要選取這些位置,再關括號,再輸入「0」(exact match)。它便會得出一個#VALUE! 。您在這裏按SHIFT+CTRL+ENTER,它便會得出3。如此便能match到大階R的啤梨。 那到底剛才做了什麼?讓我稍作講解。首先第一個是EXACT function。EXACT的作用是比較兩項資料是否一模一樣。現在我這裏第一個是A9,即大階R的啤梨。然後用array去表達,即A2至A6。 那這個array會得出什麼呢?是會得出這些資料。即是將這個A9跟第一個比較便是FALSE。跟第二個比較是FALSE。跟第三個比較是TRUE。接着是FALSE、FALSE這樣。因此用EXACT時,得出的是FALSE、FALSE、TRUE、FALSE、FALSE這些資料。然後Match的第一個value我輸入了「TRUE」。即是用這個TRUE來跟這array作比對。Match到第三個,所以return了3出來。 因此這功能可以做到exact match的效果。至於剛才得出的#VALUE!,就需要在這個function後面按CTRL+SHIFT+ENTER,如此便能得出value了。 好,今天便分享到這裏。

Index Match教學影片(2) 向左lookup點做到?

這條短片會教大家使用Index和Match向左lookup,比VLOOKUP更強。 如果你對Index和Match有其他疑問,歡迎在這裏留言,Excel爸打很樂意回答你。你也可以subscribe這條youtube頻道,收看更多有關Excel的教學短片。 按下圖收看教學影片: 以下為影片內容全文: 大家好,Excel爸打在上一段影片向大家介紹過Index和Match的功能。今天這段影片是想向大家分享Index+Match的一個向左lookup功能,而這功能是VLOOKUP做不了的。讓我示範給大家看看。 我先在這個位置輸入啤梨。然後我先用VLOOKUP給大家看。在VLOOKUP,我先選這個value。然後Table_array選這個範圍,我想lookup五個columns,即Apr的結果。再輸入「false」,這樣便lookup到3.6。以上便是VLOOKUP的向右lookup功能。 那若果我不想由左至右lookup,而是調轉,由右至左lookup,VLOOKUP是做不了的。但我用Index+Match是可以做到的。 我先用Match的功能,第一個lookup_value我選這個啤梨。lookup_array則選這個範圍。match_type輸入「0」,即exact match(這點已在上一段影片中講解過了)。然後便會得出第3位的結果。現在輸入Index功能,第一個是選array。 其實我想lookup哪一個column也可以的。我喜歡lookup這個column便框起它的範圍。然後row_num選取剛才Match的結果便OK了。得出3.6,跟剛才VLOOKUP的結果是一樣的。若想用一條公式一氣呵成也是可以的。我把這個Match formula放到這個位置,便得出3.6了。 以上便示範了如何用Index和Match功能的向左lookup方式,去得出跟VLOOKUP一樣的效果,而VLOOKUP做不了的。今天便分享到這裏。

Mail Merge教學影片

這段短片教大家Step by Step用Mail Merge預覽Preview重覆如何處理? 按下圖收看教學影片: 以下為影片內容全文: 大家好,Excel爸打今天會教大家使用Mail Merge function及預覽時出現重複情況的處理方法。 先說說Excel裏的data。我做了兩個columns:Name和Telephone,總共有14個records。Name一欄我輸入了A、B、C、D、E直到N。Telephone一欄則輸入了11111111、22222222、33333333直到14,共有14個records。然後我開Word,在「郵件」下點選「啟動合併列印」,「逐步合併列印精靈」。這樣便能step by step地用Mail Merge。 然後點選這個「標籤」,即是製作一個label。去「下一步」,在「標籤選項」處選用「A4 size / 12 labels」格式。這裏便會出現——讓我用框線框起它們給您們看。有兩個columns,六個rows,總共製作了12個labels在這。然後去下一頁,這裏點選「瀏覽」,選取放data的file,即是這個。 點選第一個Worksheet,即data的Worksheet。這裏並不需要filter。這裏便會出現第一個label。我這就去「下一步:安排標籤」。點選這裏的「其他項目」。第一個是Name,「插入」。然後第二個位這裏則點選這個「Telephone」。這樣便做好第一個label了。 若我想將這東西全部apply到12個labels中。只需按「更新所有標籤」,就能全部自動apply進去了。如此便基本上完成,可以去「預覽標籤」。 您會看到第一個label是A,即名字,電話則是11111111。第二個label B是22222222,第三就是C 33333333,看上去都挺正常的。去到K第11,L是12。那我預期第13、14是在下一頁的。但在我預覽並按這個箭咀去下一頁時,卻又會看到B 22222222﹑C 33333333、D 44444444,而沒了13、14。 遇到這種情況,我們通常會不知如何處理,呆在當場。當您按下一步去第三頁,同樣回到C 33333333、D 44444444這樣,是重複的。遇到這種情況,我們應該如何處理呢? 其實很簡單,就是不要理會這個預覽。而是直接按這個「完成合併」。然後點選這個「編輯個別標籤」,即是output。點選「全部」。這就是結果,即是generate出來的output。 您會看到A 11111111﹑B 22222222。第一頁是去到11、12,下一頁則是M 13和N 14這兩個資料,是沒有重複的。總共只有兩頁而已。因此總結是,您不用理會那個預覽(您可能會感到被騙了)。那您只需直接輸出這個file就可以完成的了。 好,今天便跟大家分享到這裏。

用Pivot Table從數據中提取資訊

Pivot Table(樞紐分析表)是Excel最強大的功能之一。Pivot Table允許您從詳細數據集中提取重要資訊。 我們的數據集由 16條記錄和 3 個欄位組成。包括Product,Month和Amount。 插入Pivot Table 要插入Pivot Table,請執行以下步驟。 1. 選取數據集中的任何1個格。 2. 在插入選項中,選取Pivot Table。 出現以下對話框。Excel 會自動為您選擇數據。新Pivot Table的默認位置是新工作表。 3. 按確定。 拖動欄位 Pivot Table欄位窗格出現。要獲取每個Product的總額,請將以下字段拖到不同的區域。 1. Product欄位到列標籤。 2. Amount欄位到值。 3. Month欄位到報表篩選。 您可以在下方找到Pivot Table。Mobile Phone就是Amount最高的產品。 排序 要使 Mobile Phone位於列表頂部,請對Pivot Table進行排序。 1. 選取Amount總和列內的任何一格。 2. 按右鍵並選取排序,從大到小排序。 篩選(filter) 因為我們將Month欄位添加到報表篩選(filter),所以我們可以按 Month過濾這個Pivot Table。例如,我們想知3月份的Amount是什麼? 單擊報表篩選下拉菜單並選擇Mar。 結果顯示3月份的Amount。 變更加總計算 一般情況下,Excel 通過對項目加總(SUM)或計數(COUNT)來匯總數據。要更改要使用的計算類型,請執行以下步驟。 1. 單擊金額加總列內的任何一格。 2. 右鍵單擊並單擊值欄位設定。 …

用Pivot Table從數據中提取資訊 Read More »

INDEX和MATCH函數教學

INDEX 和 MATCH 是 Excel 中最常用的公式之一。這是因為 INDEX 和 MATCH 非常靈活,您可以進行水平和垂直搜尋、二維搜尋、多個條件的搜尋等。如果你想提昇你的 Excel 技巧,那麼就學習INDEX 和 MATCH吧。 本文簡單介紹如何配搭一起使用 INDEX 和 MATCH 來執行搜尋。首先解釋 INDEX,然後是 MATCH,然後向您展示如何將這兩個函數組合在一起作雙向搜尋。 INDEX 函數 INDEX 實際上是做什麼的?簡而言之,INDEX 檢索範圍內給定位置的值。例如,假設您有一張水果排名列表(見下文),並且您想通過公式獲得第四位的名稱,即Apple。您可以像這樣使用 INDEX: =INDEX(A2:A6,4) 如果你想用INDEX得到Price怎麼辦?在這種情況下,我們可以同時提供行號和列號。下面的 INDEX 公式使用 A2:B6 中的全範圍數據,行號為 4,列號為 2: =INDEX(A2:B6,4,2) 總而言之,INDEX 根據數字位置在範圍內的定位獲取值。當範圍是一維時,您只需要提供一個行號。當範圍是二維的時,您需要提供行號和列號。 此時,您可能會想“那又怎樣?實際上怎會知道表格中某項內容的位置呢?” 非常正確。我們需要一種方法來定位我們正在尋找的東西的位置。 這方法就是MATCH功能。 MATCH功能 MATCH 函數被設計用於一個目的:在一個範圍內找到一個項目的位置。例如,我們可以使用 MATCH 來獲取單詞“Apple”在這個水果列表中的位置,如下所示: =MATCH(“apple”,A2:A6,0) MATCH 不關心範圍是水平的還是垂直的,如下所示: =MATCH(“apple”,C2:G2,0) 重要提示:MATCH 函數中的最後一個參數是MATCH類型。MATCH類型很重要,它控制MATCH是精確MATCH還是近似MATCH。在許多情況下,您會希望使用零 (0) 來強制精確MATCH。MATCH類型默認為 1,這是近似MATCH,因此提供一個(0)值很重要。 …

INDEX和MATCH函數教學 Read More »

Excel Vlookup公式用途教學

在辦公室裏經常要使用excel公式去解決各種的問題。 其中一個常見的問題,就是有兩個表格,兩個表格裏都有一些共同的資料,怎樣將這些資料從一個表格帶到另一個表格? 這個時候,使用vlookupfunction就能解決這些問題。 這篇教學文章,會教你怎樣用vlookup,包括vlookup函數的用途,也會用一些範例加以解釋。務求令您在閱讀這篇文章後,能夠熟悉vlookup點用,並且在出現#n/a的時候,知道是什麼原因。 首先介紹一下vlookup這個function。 你可以在公式>插入函數>輸入vlookup。 這時公式的表格便會出現。 第一個值是Lookup_value意思是,兩個表格裏共同的keyword。 第二個值是Table_array,意思是第一個表格,即是完整資料的表格的範圍。 第三個值是Col_index_num,意思是第一個表格裏面要帶資料去第二個表格的第幾個column。 第四個值是Range_lookup,意思是要用一個範圍內lookup還是要exact match才lookup。一般我們最常使用是要exact match,所以輸入一個false就可以了。 這裏有一個excel範例 Sheet1有第一個表格總共有兩個column,第一個column是items,裏面有apple,orange,bananer。第二個column是price,分別是5.5,4和3.2。 第二個表格也是有兩個column,第一個column也是items,第二個column也是price。但不同的是今次的items有好多,但相同的是items也有apple,orange和banner,只是每個items重複好幾次而已。 第二個column也是price但沒有資料。我們就是要透過vlookup將price這個value從第一個表格帶到第二個表格。 首先lookup_value就是sheet2裏面的A2。 Table_array就是Sheet1裏面A1:B4的範圍。由於我們用vlookup會將公式帶到下面,我們不想這個Table_array會移位,所以在A1:B4前面加一個$的符號,變成$A$1:$B$4。 Col_index_num輸入2代表第一個表格裡面第二個column的意思。 Range_lookup輸入false代表我們要個items exact match才出現。 輸入公式後B2會得出對應的price。 然後我們可以按加號將公式帶到B3:G8裏面,這樣便完成。 如果公式裏面出現#N/A,原因是第一個表裏面的items沒有對應的value所以match不到,這時我們便需要檢查到底是第一個表item漏了,還是兩個表的item有唔match的地方,例如多了一個space。 大家可以按此觀看[Excel教學][入門] VLOOKUP教學 (廣東話),了解這個例子的操作示範。 按此在Medium閱讀文章。

辦公室最常用到的十大公式

大家在職場中,應該會經常在使用Excel worksheet上遇到問題。以下教學文章會向大家介紹一些常用的Excel公式,希望幫助大家有一個易上手的入門教學。現在python已經很利害,仍有需要學習Excel spreadsheet嗎? 答案是肯定的,因為Excel仍然是office中最常使用的功具,例如一些finance report等。 首先,要介紹Excel file的extension (檔案副檔名),通常是xls或xlsx。如果你google一下xls vs xlsx,就可以找出兩者的分別。簡單來說,如果是Excel2003及以前的Excel版本,就只能使用xls檔案,在Excel2003之後的版本,都能夠支援xls和xlsx的檔案。使用xlsx的好處,包括支援256列和65536行以上的數據,支援更多新功能和使用上更快等。所以如果沒有兼容舊版本的考慮,一般建議使用xlsx便可。 在辦公室最常用到的formula有那些? 以下列出在辦公室常用到的10個公式: 加法 減法 乘法 除法 SUM COUNT IF SUMIF COUNTIF SUMIFS VLOOKUP PIVOT TABLE INDEX MATCH 1. 加法 減法 乘法 除法 加減乘除在Excel公式中是最常用的。只要有2個數值,就可以計算結果。 加法 =A2+B2 就會得出結果25 減法 =A2-B2 就會得出結果5 乘法 =A2*B2 就會得出結果150 除法 =A2/B2 就會得出結果1.5 2. SUM Sum的意思是在一個範圍內的數字加總。 =SUM(A2:A4) 就會得出45的結果 3. COUNT Sum的意思是在一個範圍內有多少個數值。 =COUNT(A2:A4) 就會得出2的結果(由於A不是數值,所以沒有計算在內) …

辦公室最常用到的十大公式 Read More »

Index Match教學影片

教你如何用Index+Match,找出2D Index和Match這兩個function經常會配搭一起來使用。Match就是從一個值去找回他的座標。Index就是從座標裏面去找回他的值。 如果你對Index和Match有其他疑問,歡迎在這裏留言,Excel爸打很樂意回答你。你也可以subscribe這條youtube頻道,收看更多有關Excel的教學短片。 按下圖收看教學影片: 以下為影片內容全文: 大家好,今天Excel爸打會教大家用Index和Match的function。Index和Match這兩個functions經常會配合一起使用。Match是從一個值中找出它的坐標。Index則是從坐標裏找出它的值。 首先我想跟大家講解一些data。這個表裏有items A、B、C、D、E,您可以把它們看成是五個products。橫向的column有Jan到Jun六個月這樣。至於中間的數字,您可看作是sales的data。 先說Match的function。先在「公式」裏「插入函數」,輸入「match」。首先Lookup_value,我選A8這個位置,待會我會在這個位置輸入value的。Lookup_array就是A、B、C、D、E這範圍。Match_type則是「0」,即代表exact match的意思。現在它得出#N/A,因為A8還未有值。那我輸入「C」,它便得出3。那是什麼意思呢?是代表在這個範圍內,C是排第3的意思。 然後我來call一個Index的function。先插入Index,選用第一種類型。Array我選B2至B6這個範圍。Row_num則選剛才3的位置。Column_num先留空這樣。它便會得出200。那代表什麼意思?是代表在這個範圍內,排第3的值是200的意思。以上便是Index的function。 從這裏看,用C這個值會找到200這個值,那好像跟VLOOKUP沒什麼分別。For這個case來說是的,但其實Index可以做到一個2-dimensional的matching function那樣的。讓我示範給大家看。 首先我想找500這個值這樣。而剛才我已經找到C的坐標是3。那我想多加一個matching,找Apr的column這樣。我再加入Match的function。Lookup_value是Apr這個位置。Array是橫向這個column。Match_type同樣輸入「0」。它便會找到Apr是排第4的。 現在我有了row 3和column 4這個坐標。便可插入Index的function,選用第一個類型。因為這次是2-dimensional,所以Array是選取這個範圍。Row選3的位置,Column選4的位置,便會得出500這個值。 想用一條公式一氣呵成也是可以的。我在B8這個位置套用這條公式。然後我用第二條公式套用到B9這個位置。便會得出500的值。意思是在B2至G6這個範圍。我用第一個Match:A、B、C、D、E,跟第二個Match:Jan至Jun找出C和Apr的坐標。便找到500這個值這樣。以上便是Index和Match配合一起的function。

Pivot Table教學影片

這段短片會教大家使用pivot table。Pivot table能夠幫助你將一些數據會整為一個可供閱讀的報表。例如將一些數據歸納和總結。但有些時候,整完一個Pivot table後怎樣再將這個結果再加以利用呢?這段短片會教大家怎樣使用Pivot table並且將它轉化為一個可供利用的格式。 如果你對Pivot table有其他疑問,歡迎在這裏留言,Excel爸打很樂意回答你。你也可以subscribe這條youtube頻道,收看更多有關Excel的教學短片。 按下圖收看教學影片: 以下為影片內容全文: 大家好。Excel爸打今天會教大家運用Pivot Table來整理data,整理完再轉換成一個易用的格式。 在Sheet1中有一大堆數據,有Name、Month、Product和Amount。這是一個transaction table,不同人都在不同月份買了多於一件product,也有它的Amount在其中。 然後我在Sheet2點選「插入」,「樞紐分析表」,即Pivot Table。選取剛才Sheet1的數據範圍,便會出現這個表。把剛才的columns都剔選了。它便自動替我整理出一個Pivot Table。 只是這格式不太便於運用。我把它複製了,選擇性地貼上它的「值」,便會變成一堆這樣的數值,是不太好用的。我想把它轉換成一個易用的格式。 先回到Pivot Table,點選「選項」。「選項」裏有「顯示」,顯示「古典」格式。您會看到這些columns被拆開了。若想移除裏面的「合計」,便要到Pivot Table的表裏。Name處點選「欄位設定」,把「自動」轉為「無」,便可移除「合計」了。 Month亦一樣,在「欄位設定」的「小計」下,把「自動」轉為「無」,這樣便移除了「小計」。現在這個格式變得相對簡單,我再複製這個範圍,在Sheet3處貼上它的「值」。 現在這樣已經挺易用的了,但它裏面有些空格,有沒有辦法填上它們呢?是有方法可以很快填上它們的。先在這個位置選擇=上面的value,有了formula後複製它,再框起這個範圍。然後在「尋找與選取」中點選「到」,意思即是Go。點選「特殊」裏的「空格」,它便會自動框起所有空格。我只需在這個位置選「貼上」,便能自動把這個formula填上這些空格。 那我可以把這個formula全部移除嗎?也是可以的。您只需框起這個範圍,選「複製」,「選擇性貼上」,「值」,便能移除所有formula了。 以上便是Pivot Table運用及轉換成簡單格式的方法。

Vlookup教學影片

這條短片教大家使用vlookup function。Vlookup function就是從兩個dataset裏面有共同的資料,將資料從一個data set帶到去另一個data set。 如果你對vlookup有其他疑問,歡迎在這裏留言,Excel爸打很樂意回答你。你也可以subscribe這條youtube頻道,收看更多有關Excel的教學短片。 按下圖收看教學影片: 以下為影片內容全文: 大家好,Excel爸打今天會教大家使用VLOOKUP function。VLOOKUP function是把兩個有共同items的data set。把一個data set的資料放到另一個data set,這便是VLOOKUP。 在這個Excel裏有兩個Worksheets。Sheet1有items和price,也有apple、orange和banana。 價錢為$5.5、$4和$3.2。Sheet2有相同的items,只是有很多不同排列的次序。而我想把Price LOOKUP到Sheet2裏。 首先我要call出function,在「公式」裏「插入函數」。在裏面輸入「vlookup」,按「開始」便會找到這function。或者在「檢視與參照」裏也可找到VLOOKUP這function。 Call完這個function後,首先第一項是Lookup_value。我選A2,即apple這個位置。 Table_array就要去Sheet1框起這些資料的範圍。我需要在這裏的「A1」處給它加「$」,「B2」處也要給它加「$」。我為何要這樣做?因為我待會要拉那條formula。為免formula移位,我要用「$」來lock住value。 然後是Col_index_num。在Sheet1中,Column A為第一個column,Column B為第二個column,如此類推。而現在是第二個column,所以要輸入「2」。 然後Range_lookup就要輸入「false」,代表要exact match的意思。 按「確定」。這$5.5便LOOKUP進去了。 只要把這條formula拉下去,所有價錢都會自動以VLOOKUP的方式放入適當位置。 以上便是VLOOKUP function了。

Open chat
我有Excel問題想問Excel爸打