Excel 交互參照必學神器, 用 lookup 系列函數一鍵搞定資料比對 : hlookup篇

延續上一篇的 vlookup 教學文章,藉由先給定參照值,再於指定範圍內尋找符合條件的資料並傳回指定「欄位」的對應數值,藉著介紹的 hlookup 也大同小異,但找到符合條件的資料後卻是傳回指定「列」的對應數值。 由於大部分資料庫匯出的格式,欄位代表的是變數,列資料代表的是樣本(或筆數),以至於絕大部份時候會用到的是 vlookup ,因為我們希望回傳的通常是同一筆樣本在不同變數的數值,這樣就能夠在不同的資料庫(例如客服中心電話紀錄 vs 銷售紀錄)根據相同的 key 值(例如客戶編號),建立一個新的資料集或摘要表來使用,但有些時候 hlookup 也是可以派得上用場,像是典型的實驗設計表格。 範例表格如下,這是一個飲料好感度盲測研究的實驗資料。 假如我們想把指定的資料填入一組如下方的特定清單,該怎麼做呢? 當然就是要透過 hlookup 囉! 如同 vlookup , hlookup 也擁有 4 個參數,唯一的差別是傳回值的參數,在 vlookup 中是「Col_index_num」,可指定要傳回數值的垂直範圍,在 hlookup 中則是「Row_index_num」,用來指定要傳回數值的水平範圍。 Lookup_value 想要指定的參照值在此設定,以本例而言,是各廠牌的飲料名稱,先選擇摘要工作表中的 A1:A10 範圍作為參照值。 Table_array 接著指定要被比對的資料範圍在此設定,至少需要包含兩列(Row),一列用來和參照值比對,另一列則是提供要回傳的資料。以本例而言,選擇 A1:K5 的範圍。 Row_index_num 針對被比對的資料範圍(根據 Table_array 設定的範圍),指定要回傳的值,指定的方式是選擇所要回傳的「第幾列」的資料,順序由上至下。以本例而言,希望回傳盲測組 C 的評分,也就是第 4 列,填入數字 4。 Range_lookup 這是一個邏輯判斷式,填入「TRUE」or「FALSE」以指定選擇的標準。若填上 FALSE… Continue Reading

Excel 交互參照必學神器, 用 lookup 系列函數一鍵搞定資料比對 : vlookup篇

有一陣子沒有寫部落格了,最近遇見很多年輕的朋友,發現許多人等到進入職場以後,才發現原來職務說明中所謂「具備運用 Excel 的能力」,並不是指能夠打打加減乘除而已,只有學會了運用 Excel 的函數,才能在資料分析時達到事倍功半的效果,也才算是真正會用 Excel。 正因如此,就來分享一下 Excel 的使用技巧吧。 Excel 當中主要的函數列表,可以參考 Microsoft 提供的清單(按此)。在官方的分類底下,有一類就叫做「查閱與參照函數」,相同的分類也可以在各版的 Excel 函數說明中找到,可惜對於 Excel 使用不深的人,比起「統計」、「財務」等類別,這是一個經常會被遺忘的函數集,不過若以泛用性而言,參照函數或許才是各 Excel 課程首先必須著墨的部分。 不得不說,它真的太重要啦,在企業中 Excel 的使用量很大,就算自己不用,你的客戶或供應商八成也都用得很兇,而且 Excel 的應用範圍也很廣,幾乎所有部門都能用得到,各種專案合作中只要看到別人該用卻不用對應的函數,只靠手動製作資料摘要,我就會忍不住內心嘆氣+翻白眼,因為這實在是太浪費人力了,畢竟好人才的時間成本可是很貴的啊!! 各位看官,介紹參照函數必學第一名,就是大名鼎鼎的「vlookup」函數。 假如你有以下的狀況…: 必須同時管理許多個部份資料有重疊的工作表 沒有任何單一工作表包含所有要用到的資料欄位 工作表的資料筆數很多,不太可能花時間人工一一對照 那麼再不學 vlookup ,就真的太遜啦! 還不快看看底下的 vlookup 教學! 本文用氣象局的觀測資料為例,範例包含兩個工作表各自包含「酸雨PH值」及「雨量」的資料,其中「地點(鄉鎮名)」是兩個資料集的共同點,如下圖所示: 這裡另外加入一個獨立的工作表,包含了鄉鎮名稱資料方便檢視結果。 假如我們想要建立一個包含鄉鎮、雨量以及酸雨PH值的資料集(如 test 工作表的範例),那麼該怎麼做? 如果不利用 Excel 函數,那麼恐怕我們就得一筆一筆資料尋找,然後複製貼上了,假若只有幾個地點那倒無所謂,但是面對數百個鄉鎮地點,顯然最好的辦法不是手動。藉由 vlookup 函數,我們可以設定一組參照值,然後讓公式根據參照值,在給定的工作表每一列當中尋找符合參照值的資料,回傳設定好的欄位所對應的數值。 然後呢? 點兩下複製公式到所有格子就完成了! 就這麼簡單。 vlookup 共有幾個參數要設定,一一說明如下:… Continue Reading

通往線性代數的聖母峰 : 特徵值分解(EVD)、奇異值分解(SVD) 與主成分分析(PCA)

聖母峰的譬喻很有趣,原文來自交大周志成老師的線代啟示錄部落格,周老師文章裡曾說線代領域有兩座聖母峰,一座叫Jordan Form用來判斷矩陣相似問題,另一座就是奇異值分解(SVD,Singular Value Decomposition)。 特徵值( Eigenvalue )、奇異值( Singular Value )以及相關的矩陣觀念(正交、正規、跡數、對稱、轉置共軛、么正、可逆與不可逆、行列式、實數域與複數域、正定與半正定、可對角化、相似、秩…etc )在線性代數中都是重要的觀念,不過篇幅有限不可能一一討論,所以本文的重點將放在 SVD 的原理與類似方法的比較,因此延伸的線性代數知識都將予以簡化或略過以免模糊焦點。

統計R語言實作筆記系列 – 2D視覺化進階 ggplot()的基本架構(二)

上一篇介紹 ggplot 基本架構的教學文章當中,主要示範如何把數據轉換成圖形的方法,接著要來介紹的剩下三個部份,則是影響最終圖形樣式的輔助指令,它們包括改變繪圖座標的 coord 、佈景主題  theme 以及控制圖形輸出的 facet 。 為方便比較,一樣先送上作者自行分類的 ggplot 基本功能圖解: 本文繪圖以內建資料「 ChickWeight 」為例,先使用 str() 觀察資料集的基本資訊: > str(ChickWeight) Classes ‘nfnGroupedData’, ‘nfGroupedData’, ‘groupedData’ and ‘data.frame’: 578 obs. of 4 variables: $ weight: num 42 51 59 64 76 93 106 125 149 171 …$ Time : num 0 2 4 6 8 10… Continue Reading

[reBuzz專欄] 哪些顏色讓我們不自覺地就敗家? 心理學家告訴你!

你看過鬥牛的驚險場面嗎?穿著華麗的鬥牛士在場上靈巧地搖擺大紅旗子,引誘憤怒的兇猛公牛狂奔而來,只見鬥牛士一個轉身閃躲攻擊,優雅地向群眾獻禮並贏得滿堂掌聲。這幕戲當中有三個主角:俊拔的鬥牛士、瘋狂的公牛以及大旗上亮眼的鮮紅色。 雖然大部份人們並不在鬥牛圈裡搏鬥討生活,然而身在擁擠而忙碌的商業化島國,消費者每天都暴露在大量的訊息刺激下,繽紛多姿的色彩搭配往往是行銷訊息能否突出重圍並獲得消費者寶貴注意力的關鍵。你也許不曾注意到,但這些週遭的小小顏色早已在不知不覺中改變了你我的購物行為! 心理學家早在 100 多年前研究具有暴力傾向的病患時就發現,改變環境的顏色也會改變人們的心理狀態、進而影響行為,因此醫師可以藉由控制顏色的變化來幫助病患提升或減少發生特定行為的動機。隨著相關理論的發展趨於成熟,行銷實務也開始致力於應用正確的顏色來刺激銷售、增強品牌在消費者心中的印象以及創造良好的購物環境。 揭開顏色背後的心理奧秘 人們的心情時常受到外界刺激的影響,像是顏色、氣味、空間大小、物品位置、形狀等等,其中色彩是各種刺激必然的要素,對企業來說,對色彩的最大想像不外乎找出一個最能刺激銷售的顏色。 不過從科學的角度來看,顏色並不只是映入眼簾「看起來的樣子」這麼簡單,它除了客觀的色調差異外,還具有人體生理測量上的意義,更與特定環境下的心理認知變化有關。 例如被廣泛檢驗的紅色,它除了在可視光當中被歸類為暖色系的代表外,暴露在紅色的刺激下,還有促進人體心血管機能的功用(例如加速心跳、提高血壓、增加一定時間內的呼吸次數)。在許多研究當中,紅色引發的心理狀態一致地指向「侵略( Aggression )」、「競爭( Competition )」、「緊張( Tension )」的情緒。另一方面,經常拿來與紅色作為對比的藍色,則是冷色系的代表色,藍色刺激的主要影響是讓人覺得心情穩定以及放鬆( Relaxation )。 但除了冷暖色系的差異,「明亮度( Brightness )」也有著不可小覷的作用,1996 年曾有一份研究指出,亮度較高的顏色(像是白色、粉紅、紅色、藍色)比起亮度較暗的顏色(例如黑灰色、棕色),比較能幫助人們感覺到快樂以及其他正面情緒。 色彩威力,不容小覷! 曾有學者統計了各種項目奧運選手的背心顏色與比賽成績,結果發現穿著紅色背心的選手居然也有比較高的得獎機率,這些選手的教練們恐怕從沒想過,只是改變背心顏色居然就有這麼大的影響吧! 學者認為,紅色是人類憤怒與情緒激動時表現在身體上的天然顏色,當受到紅色的刺激時,心理上就會產生暗示作用,暗示個體正處在危機存亡的關頭,因此身體機能也會自然地做出反應,好讓個體能度過眼前的挑戰,這種機制能夠幫助運動選手在短期決賽中表現得更好。 這樣看來,只要多加利用紅色之類的暖色系顏色、以及選擇明亮度較高的色彩,應該也比較能夠牽動消費者的情緒,從而提高注意力、刺激購買行為吧?然而事情可沒有這麼簡單。 求知心切的研究者們針對顏色的應用做了更多的檢驗,他們發現店內有「藍色牆壁」的店鋪比起「紅色牆壁」的店鋪,更受到消費者的喜愛,而且消費者也認為藍色牆壁的店鋪讓他們覺得比較放鬆。 在其它網路研究中也發現,藍色似乎比較能引起正面的購買行為,而大量的紅色則會降低搜尋、瀏覽網頁的意願。研究人員甚至發現,套用藍色當網站基底甚至還可以減少消費者下載軟體時的知覺等待時間呢! 既然不同色彩之間有不同效果,那麼行銷人員應該如何選擇有效的顏色呢?這個問題顯然沒有簡單答案,關鍵就在於消費情境。 用「對的」顏色為銷售大加分! 最新的一份研究中,BAGCHI 和 CHEEMA 兩位學者發現,根據網路競價( Bid )與一對一議價( Negotiation )這兩種不同的購物模式,詳細比較了不同色彩(紅、藍、灰、白)的影響力,結果發現在「競價」模式中,紅色刺激比起其他顏色可以顯著提升投標動機,但另一方面,在「議價」模式中,紅色卻是四種色彩選項中,對購買意圖最沒有貢獻的顏色。 藍色在「議價」模式的表現則是所有顏色最好的,同時在「不二價」的情況下,也就是賣方不願意做任何讓步的時候,反而是最能引起購買意圖的色彩。 研究者認為較合理的解釋是,在公開的網路競價中,消費者注意到的對象不只是商品與賣家,而是「所有其他可能參與投標的消費者」,這時候紅色刺激能夠加強競爭的意識,結果造成投標次數與金額的增加。但在一對一的議價情境裡,消費者的注意力都放在賣家身上,因此紅色的刺激會使得消費者不願意付出更高價格,因此投標的意願偏低,同理,在賣家不願對價格讓步的情況中運用紅色刺激也將收到反效果。 reBuzz 也特別為大家整理了各大網購平台的按鈕顏色,你有看出甚麼玄機嗎? 選對好色彩,就從了解消費者開始! 「什麼樣的色彩才能最有效幫助企業得益」的這個問題,得視行銷的目標來做調整。例如想營造舒適放鬆的用餐環境,不妨多加一些藍色系的佈景試試,而那些商店結帳櫃台上擺放的隨手小物,則可以考慮選用鮮紅色包裝的商品,來吸引消費者的注意力。 行銷人員應該了解,色彩運用不該流於刻板的調色公式,因為人的心理狀態不只是受到行銷刺激、內在暗示的影響,同時也和個體對「其他人」的認知、當時的購物情境有很大的關係。 因此面對面的銷售服務與網路購物兩者之間自然就有很大的不同,而這種「理解」消費者購物需求的能力,不只是對於色彩的應用,也是創造成功行銷案例的關鍵所在。 ◎ 延伸閱讀 色彩運用必學: 你不可不知的網站色調設計! 成功提案必備: 如何讓你的企劃與眾不同?行銷人不可不知的門檻心理學 有感行銷必看: 不確定的小確幸比較好?!行銷人一定要懂的消費者心理學… Continue Reading

統計R語言實作筆記系列 – 2D視覺化進階 ggplot()的基本架構(一)

R 世界的諸多套件當中,有兩個高級繪圖套件非常受到歡迎,分別是「 lattice 」與「 ggplot2 」。 和 R 內建的圖形函數相比,兩者都納入更多子功能可以繪製複雜的圖形,不過作圖的基本原理並不相同,其中 lattice 除了 2D 繪圖之外,還能繪製 3D 圖形,但就美觀來說, ggplot2 的潛力是讓人驚艷不已的,豆瓣的網友甚至暱稱為「文藝繪圖」的工具。

統計R語言實作筆記系列- 直線abline()、曲線curve()與多邊形polygon()視覺化繪圖

最近的文章中提過如何用 R 產出基本的統計圖表,也示範過用 abline() 函數繪製輔助線,但假如想要加上各種經典曲線在圖形中方便比較差異,或者隨心所欲地繪製自定義的曲線,那麼這篇文章將要介紹的函數 curve() 就能幫上忙。 輔助線之外,適當地繪製面積也是一種替圖表增色的方法,尤其是在面積本身帶有數學意義,例如累積機率,此時標示出面積能幫助圖形資訊的理解,只要使用 polygon() 函數就能做到這點。 繪製簡單直線與迴歸線: abline()、lsfit() abline() 本身無法直接做圖,得先畫出一張圖表之後再當成輔助線加上去。在此稍微回顧一下繪製直線的函數abline(),它的語法如下: abline(a = NULL, b = NULL, h = NULL, v = NULL, reg = NULL,coef = NULL, untf = FALSE, …) 其中參數a代表截距常數,參數b則是斜率常數。假如我們已經知道迴歸式等於: Y=1.1X+1,就能使用 abline() 函數來繪製直線。 假如迴歸式未知,也無須另行計算迴歸模型,只要利用 lsfit() 函數快速取得迴歸參數,再配合 abline() 就能得到迴歸直線了,參考以下的例子。 par(mfrow=c(3,1)) #切割繪圖視窗為3X1,方便稍後合併比較 aa

統計R語言實作筆記系列 – 2D資料視覺化入門

R的繪圖套件相當豐富,在「R Graphical Manual」網站上有不少範例可供學習,一般的2D統計圖表自然不在話下,還可以輕易繪製出工程上常用到的3D圖形。此外,R還包含了一些特殊的繪圖功能,像是自訂函數、繪製世界地圖資料、互動式圖表、把圖表輸出到網頁上等等。R的社群經常喜歡把這種跨領域綜合繪圖的能力概稱為「資料視覺化(Data Visualization)」。

統計R語言實作筆記系列 – 資料尺度與變數類型

收集原始資料是統計的必要步驟,前一篇介紹過把資料輸入到 R 的方法,但是在收齊資料到執行統計分析之前,還有一些基本的要件必須定義清楚,例如資料的統計尺度為何,一般常見分為四種: 比例(ratio)、區間(interval)、順序(order)、類別(nominal),依據尺度不同,適用的統計模型也有所調整。 如果只用統計尺度對資料做定義,那麼計算程式經常會出現衝突,因為程式無法像人一樣辨別不同尺度的意義,因此需要按照機器能讀懂的方式給予變數一些規定,另一方面為了不使程式設計過於複雜,這些格式也要盡量能夠符合多變量數學運算的邏輯需求。 這些考量構成了 R 裡頭常見的變數類型: 1. 向量 vector() 2. 因素向量 factor() 3. 陣列 array() 4. 矩陣 matrix() 5. 資料框架 data.frame() 變數的基本單位: 向量Vector 所謂向量是一組觀察值的集合,僅有一個資料點可以視為向量的特例。大多時候,一個變數在程式中代表的意義就是一個向量。 向量的指定可以透過「 c() 」函數操作,例如: x

統計R語言實作筆記系列 – R簡介與資料輸入教學

R 的實作筆記是去年在 Soumya 的「應用商業分析」就想過要整理了,不過一直抽不出時間,新學期上 Rich 的「行動雲端服務系統設計」,其中一項作業正好也跟 R 有關,趁這個機會複習複習。 現在 R 普及流行的程度比起四、五年前第一次聽說的時候又提升了不少, Big Data 、數據繪圖及學術社群討論得尤其熱烈,甚至還看到新創公司的資料分析職缺直接要求能操作 R 。 R 程式可以直接到 R-project 網站下載。 大名鼎鼎的 R ,是兩位姓氏為 R 開頭的學者基於統計的需求開發的開源軟體,光是免費這一點就使它受到廣大的注意,近年在許多熱心使用者的開發補足下,現在 R 已經具備相當豐富的程式庫,而且實用性與運行速度都不亞於市面上昂貴的套裝軟體,對進階的程式使用者來說, R 與其他語言的高相容性也是一大賣點。 R 的介面不像 SPSS 之類的 GUI 產品,而是靠輸入簡單的程式碼來操作(如 SAS ),雖然聽起來嚇人,不過實際上並沒有想像中困難,因為分析功能大多已經有現成的套件( packages )可供使用。軟體主程式可以下載簡易的 R GUI 或者具備開發環境的 R Studio ,僅就統計而言兩者差不多,就看各位的使用需求了。 作為一套統計工具,首先的問題是怎麼把資料輸入到 R 中。這篇文章就先介紹幾種初學者必學的函數吧。 R 讀取資料的常用函數… Continue Reading