05.使用EXCEL內建函數Find,Left,Mid,Concatenate將半結構化資料轉換為結構化資料

使用Excel內建函數將半結構化資料轉換為結構化資料

1.先至維基百科（關鍵字：DAX index wiki）複製「DAX指數的組成公司」的項目。
*除了按右鍵複製，也可直接按快捷鍵Ctrl+C複製。

2.開啟一個新的Excel檔案，在A2欄按右鍵「選擇性貼上」。


3.選擇貼上成為「文字」，按確定。


4.貼上完成後，在A1-D1欄輸入標題名稱。


5.下圖可看到，第16列的資料格式與其他資料並不相同，為了避免下面步驟出錯，現在先手動更動，排解錯誤。
將「E.ON（E.ON AG能源）」改為「意昂集團（E.ON AG，能源）」。



6.接下來要建立資料表，到資料標籤點選「從表格/資料」。


7.對話框跳出後，記得勾選「我的表格有標題」，再按確定。


8.直接關閉跳出的Power Query編輯器。


9.接下來要使用find函數計算左括號、逗號及右括號在字串中的位置。

B2=FIND("（",[@原來])
C2=FIND("，",[@原來])
D2=FIND("）",[@原來])

*記住！"雙引號"內的標點符號為全形；輸入函式的標點符號則為半形。


10.計算結果如下。


11.進入下一步驟前，請先新增三欄：

E=中文
F=原文
G=產業類別


12.找出字串分隔符號的位置後，接下來要使用left及mid函數單獨抓出中文、原文及產業類別的名稱。

函數介紹：
Left_從左數幾個字元
Right_從右數幾個字元
Mid_從第n個開始數，共數幾個字元

在儲存格中輸入：
E2=中文=LEFT([@原來],[@左括號]-1)
F2=原文=MID([@原來],[@左括號]+1,[@逗號]-[@左括號]-1)
G2=產業類別=MID([@原來],[@逗號]+1,[@右括號]-[@逗號]-1)

函式說明：
E2=中文=LEFT([@原來],從左括號的左邊一個字元開始數起)
F2=原文=MID([@原來],從左括號的右邊一個字元開始數起,數逗號跟左括號之間的字元)
G2=產業類別=MID([@原來],從逗號的右邊一個字元開始數起,數右括號跟逗號之間的字元)

完成圖如下。


13.接下來統整一下以上的函數。


14.接下來因為要將資料做成表格並貼至網頁，因此需要先統一輸入表格語法。

以下介紹兩種方法：

(1)直接將表格語法和文字以"&"連在一起。

H2="<tr><td>"&[@中文]&"</td><td>"&[@原文]&"</td><td>"&[@產業類別]&"</td></tr>"


(2)使用CONCATENATE將多組字串組合成單一字串。

*此函數是用逗號將語法及欄位名稱連結在一起。
H2=CONCATENATE("<tr><td>",[@中文],"</td><td>",[@原文],"</td><td>",[@產業類別],"</td></tr>")


兩種方法，相同結果，只要選擇自己理解的方法擇一輸入即可。

15.接下來將H欄的資料全選並複製至編輯器，並用<table></table>標籤包住即可。

<table border="1"></table>
*border 屬性可設定表格框線。


16.完成表格如下。

德國 DAX 30 成分股

沈慧鈞整理
中文原文產業類別
愛迪達Adidas AG體育用品
安聯Allianz SE保險
巴斯夫BASF SE化工
拜耳Bayer AG醫藥
西門子能源股份公司Siemens Energy AG能源
寶馬Bayerische Motoren Werke AG汽車製造
大陸集團Continental AG運輸行業製造
科思創Covestro AG聚合物材料
戴姆勒賓士汽車公司Daimler AG汽車製造
德意志銀行Deutsche Bank AG金融
德國證券交易所Deutsche Borse AG金融
德國郵政Deutsche Post AG物流
德國電信Deutsche Telekom AG電信
德國住宅公司Deutsche Wohnen SE房地產
意昂集團E.ONAG能源
費森尤斯集團Fresenius SE健康，醫院
費森尤斯醫療Fresenius Medical Care AG & Co. KGaA腎透析產品和服務,Fresenius SE的子公司
漢高Henkel化工
英飛凌Infineon Technologies AG半導體製造
林德Linde AG化工
默克集團Merck KGaA化工製藥
MTU航空發動機公司MTU Aero Engines AG飛機發動機製造
麥德龍Metro AG商品零售
慕尼黑再保險 Munchener Ruckversicherungs-Ges. AG保險
萊茵集團RWE AG能源
SAP公司SAP AG企業管理軟體與解決方案供應商
西門子Siemens AG電器製造
大眾汽車Volkswagen AG汽車製造
沃諾維亞Vonovia SE房地產
Delivery HeroDelivery Hero SE網際網路訂餐服務

劉任昌的教學影片 075

留言

張貼留言

這個網誌中的熱門文章

09.公開資訊觀測站 / 基本資料 / 簽證會計師 / 產業類別

圖片
公開資訊觀測站 / 基本資料 1.先至「 資料庫管理課程教學088 」下載檔案。 2.開啟檔案(.csv)後，另存成.xlsx檔 。 3.一併選取1、2列，按右鍵插入兩列。 4.在A1輸入姓名及日期，設定跨欄至中(A1-C1)，並套用醒目底色。 5.接下來至資料標籤，點選「從表格/範圍」。 *要記得先在 要製成表格的資料 隨便點選一格，否則無法建立正確的表格。 6.跳出對話框後，按確定。 7.按確定後，接下來會跳出Power Query編輯器，至檔案標籤，按關閉並載入「 關閉並載入至 」。 8.接下來會挑出對話框，選擇「只建立連線」，並點選確定。 樞紐分析：簽證會計師(列) / 產業類別(欄) 9.接下來至插入標籤，點選「 樞鈕分析表 」。 10.跳出對話框後，直接按確定即可。 11. 依下圖將要分析之項目拖曳至對應欄位。 *「值」的那欄可放除了數值以外的項目。 12.按右鍵 將「總計」從 大至小 排列 。 13.完成圖如下，從下圖中可以看到A5及A46是相同的事務所，只是名稱有所差異，所以接下來要整理資料。 14.首先回到「公司基本資料」的工作表，找到最後一欄「 簽證會計師事務所 」，至資料標籤，點選「排序」。 15.排序方式選擇「 簽證會計師事務所 」，順序選擇 Z到A（大到小） 。 16.如何整理資料？就是以一個名稱為標準，將名稱統一。全部整理完之後就回到工作表1，至 資料 標籤按「 全部重新整理 」。 17.完成圖如下。 最大的5家事務所 上市公司客戶 勤業眾信聯合會計師事務所 360 資誠聯合會計師事務所 206 安侯建業聯合會計師事務所 202 安永聯合會計師事務所 95 國富浩華聯合會計師事務所 27 公開資訊觀測站是模仿美國EDGAR 台灣的公開資訊觀測站隸屬於台灣證券交易所股份有限公司，美國的EDGAR則是直接隸屬於聯邦政府的證券交易所委員會。 EDGAR即電子化數據收集、分析及檢索系統 。1996年，美國SEC 規定所有的信息披露義務人（美國上市公司 ）都必須進行電子化入檔。為了確保追蹤使用情況，Vintage Filings會將所發生的全部書面郵件、傳真抄送給新華美通。 （ 資料來源： https://wiki.mbalib.com/zh-tw/EDGAR ） 劉任昌的教學影片088 劉...
閱讀完整內容

18.名稱管理員 / EXCEL 函數 / TRANSPOSE 矩陣轉置 / INDIRECT 間接定址

圖片
練習檔下載： 資料庫管理課程教學109_矩陣轉置與名稱管理員 名稱管理員 在使用間接定址函數之前，需先將樞紐分析表內的資料定義才抓的到資料，因此需要先全選並使用名稱管理員先行定義每一列的名稱。 此功能位於： 公式 \ 已定義之名稱 \ 從選取範圍建立 先選取範圍A4:E28，再從選取範圍建立名稱，系統會自動偵測選取範圍中最左列(也就是國家名稱)的值來建立名稱。 最後可在 名稱方塊 或 名稱管理員 中確認是否建立成功。 EXCEL巢狀函數 應用 -    TRANSPOSE 矩陣轉置 & INDIRECT 間接定址 剛剛定義了名稱，現在可以使用INDIRECT+TRANSPOSE函數組合來配合清單從選取範圍抓取特定資料。 首先須先使用資料驗證功能在I3儲存格建立一個清單(範圍是A欄的所有國家名稱)。 因為原始資料列為橫向，但抓取後要放置的資料方向為直式，所以在用INDIRECT間接定址功能時需搭配TRANSPOSE矩陣轉置函數，才能正確地顯示欲抓取的資料。 首先須框選資料欲顯示的範圍，再輸入函式 =TRANSPOSE(INDIRECT(I3)) ，並 同時按住Shift+Ctrl，最後按Enter ，這樣才能成功地顯示資料。 與原始抓取資料範圍對照確認結果無誤後，即抓取資料成功。 INDIRECT 間接定址 傳回文字串所指定的參照。 該參照會立刻進行計算並顯示其內容。 INDIRECT 通常是想在公式中變更儲存格參照卻不想改變公式本身時使用。 TRANSPOSE 矩陣轉置 有時候需要切換或旋轉儲存格， 會複製、複製 、複製及使用轉置選項來執行此工作， 但這麼做會建立重複的資料，如果不想重複輸入，可以改使用 TRANSPOSE 函數輸入公式。 #記得在完成後按 CTRL+SHIFT+ENTER，而不是只按下 ENTER。 劉任昌的教學課程 109
閱讀完整內容

22.將上次的成果利用VS Code編輯為解答欄加上表格 / 原始碼@2634-2841

金融市場常識 答 1 2 3 4 下列何者不是金融市場的主要功能？ 提供金融工具交易的場所 擔任資金需求者與供給者的橋樑 促進投資活動的效率，提升經濟發展 提供交易者投機的場所 答 1 2 3 4 下列何者不是金融市場交易的工具？ 商業本票 銀行存款 股票 房地產 答 1 2 3 4 當金融市場管理的品質較佳時，企業發行的成本會______，金融工具的流動性會______。 較低，較低 較高，較低 較低，較高 較高，較高 答 1 2 3 4 當企業需要資金來進行投資，最好不要用下列何種方式尋求融通？ 向銀行借錢 發行股票 發行債券 向地下錢莊借錢 答 1 2 3 4 下列何者不是近年來國內主管機關管理金融市場的發展方向？ 減少法規的限制 鼓勵金融創新 對國外資金進行更嚴格的限制與管理 加強國際金融的流通 答 1 2 3 4 在金融市場中主管機關訂定法律規範的目的在於： 增加政府的收入 促進金融市場健全發展與有效經營 增加金融機構的進入障礙 提高金融機構的經營成本 答 1 2 3 4 目前國內監理銀行、票券、證券及保險等金融機構的主管機關為： 財政部 內政部 金融監督管理委員會 經濟部 答 1 2 3 4 依照國內金融監督管理制度的架構，主管證券相關事業的監理單位為： ...
閱讀完整內容