1. 為什么我選在1月24日這天專門(mén)補(bǔ)Excel1.1 這天的學(xué)習(xí)目標(biāo)圍繞實(shí)際工作痛點(diǎn)坦白說(shuō)這個(gè)Excel學(xué)習(xí)記錄并不是心血來(lái)潮而是年前積壓的一堆表處理需求實(shí)在推不過(guò)去了。1月24日這天我把手頭所有跟Excel相關(guān)的毛病集中梳理了一遍有同事問(wèn)SUMIFS怎么多條件求和有項(xiàng)目要往ArcGIS里導(dǎo)Excel表格有報(bào)表系統(tǒng)每次粘貼都失靈還有一份數(shù)據(jù)要按關(guān)鍵詞歸類后求和。這些問(wèn)題單獨(dú)看都不難但湊在一起就是典型的日常辦公綜合癥。我的想法很簡(jiǎn)單把當(dāng)天的學(xué)習(xí)整理成一個(gè)可復(fù)現(xiàn)的清單按“函數(shù)處理、數(shù)據(jù)流轉(zhuǎn)、故障排查、效率技巧”四個(gè)板塊過(guò)一遍。后面再遇到類似場(chǎng)景直接翻這篇記錄就能抄作業(yè)不用又在搜索引擎里翻半小時(shí)。這篇文章就是把1月24日這一天的學(xué)習(xí)過(guò)程完整展開(kāi)適合正在補(bǔ)Excel函數(shù)、經(jīng)常被數(shù)據(jù)導(dǎo)入導(dǎo)出折騰、以及老遇到粘貼失效和公式下拉失效的朋友們參考。1.2 當(dāng)天的學(xué)習(xí)路線安排我是按“先解決問(wèn)題、再補(bǔ)原理、最后做總結(jié)”的順序推進(jìn)的。上午集中處理函數(shù)類問(wèn)題包括SUMIFS、通配符求和、兩列查重和快速定位下午研究數(shù)據(jù)流轉(zhuǎn)圍繞Excel導(dǎo)入ArcGIS、Python讀寫(xiě)Excel、Excel導(dǎo)入數(shù)據(jù)庫(kù)這三條線展開(kāi)晚上的時(shí)間用來(lái)排查疑難雜癥重點(diǎn)看CtrlV失效、加載項(xiàng)被禁用、公式下拉失效和文件擴(kuò)展名無(wú)效這四類高頻問(wèn)題。這個(gè)順序其實(shí)藏著一個(gè)邏輯函數(shù)問(wèn)題解決的是“表內(nèi)怎么算”數(shù)據(jù)流轉(zhuǎn)解決的是“表怎么跟外部系統(tǒng)對(duì)接”疑難雜癥解決的是“表本身用不了怎么辦”效率技巧解決的是“怎么能少加班多完成”。四層遞進(jìn)每解決一層下一層才走得動(dòng)。你如果也想系統(tǒng)過(guò)一遍Excel按這個(gè)緯度拆會(huì)比零散刷教程高效得多。2. 函數(shù)實(shí)戰(zhàn)條件求和、查重、快速定位一次搞定2.1 SUMIFS多條件求和的正確使用姿勢(shì)SUMIFS是我在1月24日最先復(fù)習(xí)的函數(shù)因?yàn)樗侨粘＝y(tǒng)計(jì)中最常用的多條件匯總函數(shù)。它的語(yǔ)法是SUMIFS(求和區(qū)域, 條件區(qū)域1, 條件1, 條件區(qū)域2, 條件2, ...)。注意SUMIFS的求和區(qū)域是寫(xiě)在第一個(gè)參數(shù)位置而舊版SUMIF是求和區(qū)域?qū)懺谧詈髢烧唔樞蛳喾磩偵鲜趾苋菀讓?xiě)反。舉個(gè)例子。有一張銷售明細(xì)表A列是銷售員B列是產(chǎn)品類別C列是金額。想統(tǒng)計(jì)“張三”賣出的“數(shù)碼類”產(chǎn)品總金額公式就是SUMIFS(C:C, A:A, 張三, B:B, 數(shù)碼類)如果條件值在單元格里比如張三在E2單元格就要用單元格引用SUMIFS(C:C, A:A, E2, B:B, 數(shù)碼類)我在實(shí)際排查中發(fā)現(xiàn)一個(gè)高頻問(wèn)題很多同事把條件區(qū)域?qū)懗梢徽袥](méi)問(wèn)題但求和區(qū)域只選了幾行列數(shù)不一致結(jié)果返回#VALUE!或結(jié)果明顯偏小。SUMIFS要求所有區(qū)域的行數(shù)必須一致首行必須對(duì)齊這一點(diǎn)在動(dòng)態(tài)數(shù)組版本里尤其嚴(yán)格。還有一點(diǎn)值得注意SUMIFS天然支持多條件篩選但不支持“同列多個(gè)獨(dú)立值直接相加”這種寫(xiě)法。比如要統(tǒng)計(jì)“張三”和“李四”兩個(gè)人的金額正確做法是寫(xiě)成兩個(gè)SUMIFS相加而不是把條件寫(xiě)成{張三,李四}后者在普通公式里不生效除非配合SUMPRODUCT。2.2 同一列中按關(guān)鍵詞統(tǒng)計(jì)對(duì)應(yīng)數(shù)據(jù)求和這是當(dāng)天熱搜詞里提到的“excel同一列中統(tǒng)計(jì)含關(guān)鍵詞對(duì)應(yīng)數(shù)據(jù)求和”。需求很典型A列是備注信息比如“廣告投放-618”“廣告投放-雙11”“線下活動(dòng)-展會(huì)”B列是費(fèi)用金額現(xiàn)在想把所有包含“廣告投放”的行的費(fèi)用加總。這類需求用通配符處理。SUMIFS支持通配符星號(hào)*代表任意多個(gè)字符問(wèn)號(hào)?代表單個(gè)字符。公式如下SUMIFS(B:B, A:A, *廣告投放*)注意“廣告投放”表示包含“廣告投放”四個(gè)字即可不限制前后是否有其他字符。如果只想匹配以“廣告投放”開(kāi)頭的就寫(xiě)“廣告投放*”只想匹配以“廣告投放”結(jié)尾的就寫(xiě)“*廣告投放”。如果關(guān)鍵詞在單元格里比如關(guān)鍵詞放在D2可以這樣引用SUMIFS(B:B, A:A, *D2*)這里用符號(hào)把星號(hào)和關(guān)鍵字拼接起來(lái)是SUMIFS與文本拼接的經(jīng)典組合。如果要統(tǒng)計(jì)的對(duì)象同時(shí)滿足“包含廣告投放”和“金額大于500”兩個(gè)條件就再加一個(gè)條件區(qū)域SUMIFS(B:B, A:A, *廣告投放*, B:B, 500)這種方法適合單列關(guān)鍵詞匹配。如果備注里有多組關(guān)鍵詞聯(lián)想求和比如“廣告投放”和“市場(chǎng)推廣”都要就要用SUMPRODUCT和ISNUMBER的組合或者把兩個(gè)SUMIFS加起來(lái)。2.3 兩列查重與快速定位的具體方案“excel兩列如何進(jìn)行查重”是最常被搜到的問(wèn)題之一。我在學(xué)習(xí)記錄里整理了三套方案按場(chǎng)景選。第一套是條件格式。選中兩列數(shù)據(jù)點(diǎn)擊“開(kāi)始-條件格式-突出顯示單元格規(guī)則-重復(fù)值”Excel會(huì)自動(dòng)標(biāo)出兩列中重復(fù)出現(xiàn)的單元格。這個(gè)方法最快但只能標(biāo)出重復(fù)不能直接篩出是哪一列獨(dú)有的。第二套是公式判斷。比如A列和B列要對(duì)比在C1寫(xiě)入IF(COUNTIF(B:B, A1)0, B列有, B列無(wú))這樣就能逐行標(biāo)出A列中每個(gè)值在B列是否存在。反向檢查就寫(xiě) IF(COUNTIF(A:A, B1)0, A列有, A列無(wú))。COUNTIF的統(tǒng)計(jì)區(qū)域用整列引用計(jì)算量會(huì)偏大如果是幾千行數(shù)據(jù)沒(méi)問(wèn)題幾萬(wàn)行以上建議鎖范圍范圍寫(xiě)成COUNTIF($B$1:$B$10000, A1)。第三套是高級(jí)篩選適用于需要把重復(fù)項(xiàng)或唯一項(xiàng)單獨(dú)提取到新工作表。點(diǎn)擊“數(shù)據(jù)-高級(jí)篩選”選擇“將篩選結(jié)果復(fù)制到其他位置”列表區(qū)域選兩列勾選“選擇不重復(fù)的記錄”即可快速提取唯一值列表?？焖俣ㄎ环矫鎺讉€(gè)常用快捷鍵值得背下來(lái)CtrlG打開(kāi)定位對(duì)話框可以定位到空值、公式、批注、常量CtrlShiftL開(kāi)啟篩選CtrlF查找后用“查找全部”會(huì)列出所有匹配單元格點(diǎn)擊任意一行可直接跳轉(zhuǎn)。組合使用這些操作幾千行里找目標(biāo)值基本能控制在十秒以內(nèi)。2.4 用正則表達(dá)式處理內(nèi)容提取的補(bǔ)充Excel新版里增加了REGEXEXTRACT和REGEXREPLACE函數(shù)專門(mén)處理文本提取。熱搜詞里的“excel regexextract函數(shù)”指的就是這個(gè)。REGEXEXTRACT(文本, 正則表達(dá)式)可以從字符串里按規(guī)則提取內(nèi)容。比如從“訂單號(hào)ABC123456”里提取數(shù)字部分REGEXEXTRACT(A1, \d)如果要從“張三-13812345678-北京”里提取手機(jī)號(hào)就用“1[3-9]\d{9}”。這類函數(shù)在老版本里通常要借助MID、LEFT、RIGHT加FIND組合或者用VBA正則對(duì)象而新版本一個(gè)函數(shù)就搞定。不過(guò)需要注意REGEX系列目前主要在Microsoft 365的更新通道中可用普通Office 2019或永久版不一定支持。1月24日檢查我自己的電腦時(shí)確認(rèn)Office 365最新版已經(jīng)能直接用。如果版本不支持退路是VBA正則在模塊里引用 Microsoft VBScript Regular Expressions 5.5 然后寫(xiě)自定義函數(shù)。這個(gè)方案稍復(fù)雜建議同一批文本需要反復(fù)提取時(shí)再上VBA一次性小需求直接用查找替換或分列更省事。3. 數(shù)據(jù)流轉(zhuǎn)Excel和ArcGIS、Python、數(shù)據(jù)庫(kù)的打通3.1 Excel表格導(dǎo)入ArcGIS 10.8的完整步驟熱搜詞里“excel 表格怎么導(dǎo)入arcgis10.8”屬于GIS類高頻問(wèn)題。很多人辛辛苦苦把經(jīng)緯度整理進(jìn)Excel結(jié)果一導(dǎo)入ArcGIS全是亂碼或點(diǎn)位置錯(cuò)位。這里我把1月24日測(cè)試通過(guò)的操作步驟完整寫(xiě)一遍。第一步檢查Excel表結(jié)構(gòu)。建議首行是字段名且字段名不要帶空格、不要用中文括號(hào)、不要以數(shù)字開(kāi)頭最好是英文或簡(jiǎn)短中文比如“經(jīng)度”“緯度”“名稱”“產(chǎn)值”。第二行開(kāi)始是數(shù)據(jù)不要合并單元格不要有空行。第二步另存為.xls或.csv格式。ArcMap 10.8對(duì).xls支持最好但對(duì).xlsx通常也能讀不過(guò)如果數(shù)據(jù)量大超過(guò)65536行xls格式會(huì)爆上限這時(shí)建議用.csv。如果含中文且Excel默認(rèn)編碼不行另存.csv時(shí)選“CSV UTF-8逗號(hào)分隔”會(huì)更穩(wěn)妥否則在ArcGIS里打開(kāi)中文可能亂碼。第三步在ArcMap里添加數(shù)據(jù)。點(diǎn)擊“添加數(shù)據(jù)”旁邊的下箭頭選擇“添加X(jué)Y數(shù)據(jù)”在彈出窗口里選擇你的表X字段選經(jīng)度Y字段選緯度坐標(biāo)系根據(jù)實(shí)際選WGS84或CGCS2000等。點(diǎn)確定后數(shù)據(jù)會(huì)以事件圖層形式顯示在地圖上。第四步把事件圖層導(dǎo)出為要素類。右鍵事件圖層選“數(shù)據(jù)-導(dǎo)出數(shù)據(jù)”保存為.shp或要素類這一步是把Excel臨時(shí)事件轉(zhuǎn)成真正可編輯的GIS數(shù)據(jù)。很多人跳過(guò)這一步結(jié)果下次打開(kāi)工程表一刪點(diǎn)就全沒(méi)了。第五步的避坑點(diǎn)批量出圖時(shí)想插入Excel表格ArcGIS本身不自帶直接讀Excel單元格并自動(dòng)更新的功能。我測(cè)試的方案是先在Excel里做排版表格并另存為PDF或圖片然后在ArcGIS布局視圖里添加該文件。如果要求每個(gè)圖幅自動(dòng)換對(duì)應(yīng)表格就得用ArcPy腳本循環(huán)出圖并動(dòng)態(tài)設(shè)置圖片路徑這個(gè)屬于進(jìn)階玩法建議先手工驗(yàn)證再寫(xiě)腳本。3.2 Python讀寫(xiě)Excel的常用方案“python寫(xiě)入excel”和“python查找excel中字符串”這兩個(gè)問(wèn)題本質(zhì)都是Python和Excel的交互。1月24日我重新測(cè)了三個(gè)庫(kù)pandas、openpyxl、xlwings各有用處。如果是數(shù)據(jù)分析場(chǎng)景優(yōu)先pandas。寫(xiě)入Excel用ExcelWriterimport pandas as pd df pd.DataFrame({姓名: [張三, 李四], 金額: [100, 200]}) with pd.ExcelWriter(output.xlsx) as writer: df.to_excel(writer, sheet_nameSheet1, indexFalse)如果要在一個(gè)文件里寫(xiě)多個(gè)工作表可以調(diào)整ExcelWriter的sheet_name。pandas讀取Excel同樣方便pd.read_excel(input.xlsx, sheet_nameSheet1)即可搜索字符串則可以直接用DataFrame的字符串方法df[df[備注].str.contains(廣告, naFalse)]這段代碼會(huì)篩出“備注”列包含“廣告”的所有行相當(dāng)于Excel里的篩選或通配符查找。如果要對(duì)Excel格式做精細(xì)控制比如合并單元格、設(shè)置列寬、寫(xiě)公式、加背景色就用openpyxl。它面向單元格對(duì)象操作自由度更高。下面是一段示例from openpyxl import Workbook wb Workbook() ws wb.active ws[A1] 銷售收入 ws[B1] 12345 ws[B1].font Font(boldTrue) wb.save(report.xlsx)xlwings則適合需要調(diào)用Excel本身功能比如刷新數(shù)據(jù)透視表、執(zhí)行VBA宏的場(chǎng)景因?yàn)樗窃谀愕碾娔X上打開(kāi)真實(shí)Excel進(jìn)程來(lái)操作所以執(zhí)行速度比openpyxl慢但功能更接近人手操作。我的建議是純數(shù)據(jù)處理用pandas要排版用openpyxl要調(diào)Excel原生能力就上x(chóng)lwings。3.3 Excel導(dǎo)入數(shù)據(jù)庫(kù)及開(kāi)源Excel數(shù)據(jù)庫(kù)軟件“excel導(dǎo)入數(shù)據(jù)庫(kù)”“開(kāi)源excel數(shù)據(jù)庫(kù)軟件”這兩個(gè)熱搜詞說(shuō)明很多人不只是想做單表統(tǒng)計(jì)而是希望Excel能承擔(dān)小型數(shù)據(jù)庫(kù)的職能。Excel本身自帶“獲取數(shù)據(jù)”功能數(shù)據(jù)-獲取數(shù)據(jù)-自文件-從Excel工作簿可以直接將另一個(gè)Excel文件加載進(jìn)Power Query做清洗。但真正把Excel當(dāng)數(shù)據(jù)庫(kù)用更推薦配合SQLite。用Python的pandas將Excel導(dǎo)入SQLite只需幾行import sqlite3 import pandas as pd df pd.read_excel(data.xlsx, sheet_nameSheet1) conn sqlite3.connect(mydb.db) df.to_sql(sales, conn, if_existsreplace, indexFalse)之后所有查詢都走SQL比在Excel里做VLOOKUP快得多。查找數(shù)據(jù)可以用SQL語(yǔ)句SELECT * FROM sales WHERE 備注 LIKE %廣告%;這與在Excel里用通配符求和本質(zhì)一樣但SQL在大數(shù)據(jù)量場(chǎng)景下性能遠(yuǎn)超Excel公式。開(kāi)源Excel數(shù)據(jù)庫(kù)軟件方面常見(jiàn)思路有幾種一是用LibreOffice Base搭配內(nèi)嵌的HSQLDB或Firebird界面和Excel類似能通過(guò)ODBC連接Excel文件二是直接使用SQLite Studio或DBeaver連接Excel文件DBeaver的Excel驅(qū)動(dòng)支持將xlsx當(dāng)表查三是在Python里用DuckDB它可以直接查詢Excel文件SELECT * FROM data.xlsx WHERE 備注 LIKE %廣告%;DuckDB對(duì)xlsx的只讀查詢非?？爝m合做快速分析和跨表關(guān)聯(lián)而且完全免費(fèi)開(kāi)源。如果你有Excel多表關(guān)聯(lián)、大數(shù)據(jù)量篩選這類需求強(qiáng)烈建議配上SQLite或DuckDB把它當(dāng)本地?cái)?shù)據(jù)庫(kù)玩。3.4 其他數(shù)據(jù)流轉(zhuǎn)場(chǎng)景的關(guān)鍵提示熱搜詞里還有“arcgis批量出圖想插入excel表格”“eplan部件匯總表導(dǎo)出excel”“a2l轉(zhuǎn)excel”“c#讀取excel”“easypoi導(dǎo)出excel模板帶圖片無(wú)效”這類具體軟件聯(lián)動(dòng)問(wèn)題。ArcGIS批量出圖插表格的問(wèn)題在3.1提到過(guò)。Eplan部件匯總表導(dǎo)出Excel屬于專業(yè)電氣軟件導(dǎo)出功能一般在Eplan的報(bào)表生成器里把部件匯總表生成表格右鍵“導(dǎo)出-Excel”注意導(dǎo)出后格式需要微調(diào)因?yàn)镋plan導(dǎo)出時(shí)中文編碼有時(shí)會(huì)亂建議用CSV中轉(zhuǎn)。A2L轉(zhuǎn)Excel是汽車電子標(biāo)定領(lǐng)域的需求.a2l文件本質(zhì)是文本文件可以用Python解析按“MEASUREMENT”“CHARACTERISTIC”等關(guān)鍵字切塊后寫(xiě)入Excel。這事沒(méi)有現(xiàn)成免費(fèi)一鍵工具建議用正則解析。C#讀取Excel的場(chǎng)景如果是.NET環(huán)境推薦使用NPOI或ClosedXML。ClosedXML的代碼寫(xiě)法更簡(jiǎn)潔using ClosedXML.Excel; using var workbook new XLWorkbook(data.xlsx); var ws workbook.Worksheet(1); var value ws.Cell(A1).GetString();如果遇到“c#無(wú)法讀取excel中的數(shù)據(jù)并打印”這類問(wèn)題多半是單元格還沒(méi)被格式化或Excel正在被另一個(gè)進(jìn)程占用讀取前先判斷文件是否鎖定用FileShare.ReadWrite打開(kāi)流再交給庫(kù)解析成功率會(huì)高很多。Easypoi導(dǎo)出Excel模板帶圖片無(wú)效的問(wèn)題常見(jiàn)原因是Easypoi對(duì)圖片類型字段要求類型為byte[]且模板里的圖片占位符必須嚴(yán)格寫(xiě)成{{image}}這樣的格式圖片列數(shù)據(jù)類型不能用String否則導(dǎo)出后圖片位置為空。用elementType byte[].class顯式聲明配合注解 Excel(name 圖片, type 2, height 20, width 30) 就能正常導(dǎo)出。4. 疑難雜癥排查粘貼失效、公式下拉失效、加載項(xiàng)被禁用4.1 CtrlV失效的快速判斷思路“excel ctrl v 失效”和“excel粘貼快捷鍵用不了頻閃”這類問(wèn)題1月24日我專門(mén)花時(shí)間做了排查結(jié)論是場(chǎng)景不同原因也不同但有一個(gè)通用排查順序。第一步先排除剪貼板占用問(wèn)題。Windows系統(tǒng)剪貼板被某些軟件截圖工具、翻譯軟件、遠(yuǎn)程控制軟件駐留監(jiān)控時(shí)CtrlV會(huì)偶爾失靈。第一種驗(yàn)證方式是按WinV打開(kāi)剪貼板歷史看內(nèi)容是否已進(jìn)入剪貼板如果歷史列表為空說(shuō)明復(fù)制環(huán)節(jié)就沒(méi)成功問(wèn)題出在復(fù)制端如果歷史有內(nèi)容但CtrlV沒(méi)反應(yīng)問(wèn)題出在Excel端或按鍵沖突。第二步檢查Excel是否有加載項(xiàng)沖突。有些加載項(xiàng)會(huì)在復(fù)制粘貼時(shí)攔截剪貼板事件。關(guān)閉所有非必要加載項(xiàng)路徑是“文件-選項(xiàng)-加載項(xiàng)-管理COM加載項(xiàng)-轉(zhuǎn)到”把不必要的勾去掉再重啟Excel。第三步檢查是否開(kāi)啟了“粘貼特殊”模式。有時(shí)單元格處于編輯狀態(tài)雙擊進(jìn)入單元格內(nèi)部時(shí)CtrlV不是粘貼數(shù)據(jù)而是粘貼字符格式這時(shí)按一下Esc退出編輯狀態(tài)再粘貼即可。第四步個(gè)別文件CtrlV用不了但其他文件正常這個(gè)場(chǎng)景通常是工作簿啟用了共享模式或保護(hù)工作表。共享工作簿對(duì)剪貼板限制很嚴(yán)格去掉“審閱-共享工作簿”里的勾粘貼就恢復(fù)了。如果還不行檢查“審閱-保護(hù)工作表”是否勾選了“編輯對(duì)象”保護(hù)狀態(tài)下禁止了對(duì)象粘貼自然就粘不上。我的經(jīng)驗(yàn)是80%的CtrlV失效問(wèn)題可以通過(guò)重啟Excel或關(guān)閉加載項(xiàng)解決剩下的是共享、保護(hù)和剪輯板監(jiān)控這類邊界場(chǎng)景。4.2 Excel加載項(xiàng)被禁用及公式下拉失效“excel加載項(xiàng)被禁用”時(shí)Excel會(huì)彈一個(gè)“此應(yīng)用程序中的加載項(xiàng)存在問(wèn)題”之類的提示然后加載項(xiàng)自動(dòng)被系統(tǒng)禁用。常見(jiàn)原因有幾個(gè)一是加載項(xiàng)啟動(dòng)崩潰Excel出于保護(hù)機(jī)制自動(dòng)禁用。解決方法是在“文件-選項(xiàng)-加載項(xiàng)-管理COM加載項(xiàng)”里重新勾選然后重啟Office。二是插件與Office版本不兼容比如32位插件裝進(jìn)了64位Office。這時(shí)卸載插件下載對(duì)應(yīng)架構(gòu)的版本重新安裝。三是宏安全性設(shè)置。代碼簽名缺失或宏被阻止時(shí)加載項(xiàng)會(huì)被當(dāng)作高風(fēng)險(xiǎn)而禁用。解決辦法是在“文件-選項(xiàng)-信任中心-宏設(shè)置”里啟用“禁用所有宏并發(fā)出通知”或者對(duì)已知安全插件選擇信任。公式下拉失效這個(gè)問(wèn)題在Office 2019里尤其常見(jiàn)。表現(xiàn)是C2單元格寫(xiě)好公式鼠標(biāo)放到右下角填充柄上雙擊或拖動(dòng)時(shí)下拉不生效甚至整列只顯示同一個(gè)值。可能原因之一是Excel自動(dòng)計(jì)算被關(guān)閉。點(diǎn)擊“公式-計(jì)算選項(xiàng)”看一下是不是選了“手動(dòng)”。如果選了手動(dòng)下拉填充公式后不會(huì)重新計(jì)算看起來(lái)就像是公式失效。切換回“自動(dòng)”再拖動(dòng)填充柄。原因之二是表格格式問(wèn)題。如果數(shù)據(jù)區(qū)域被定義成“表格”插入-表格且下面還有空行有時(shí)公式列不會(huì)自動(dòng)填充到新行。右鍵表格-“表格選項(xiàng)”確保勾選了“擴(kuò)展數(shù)據(jù)區(qū)域格式及公式”。原因之三是填充選項(xiàng)默認(rèn)選“不帶格式填充”。如果原單元格格式和相鄰單元格格式不一致拖拽后公式雖然復(fù)制了但格式?jīng)]跟過(guò)來(lái)看到的結(jié)果就比對(duì)不上。4.3 文件無(wú)法打開(kāi)與擴(kuò)展名無(wú)效的解決路徑“excel無(wú)法打開(kāi)文件因?yàn)槲募袷交蛭募U(kuò)展名無(wú)效”這個(gè)報(bào)錯(cuò)1月24日我也專門(mén)復(fù)現(xiàn)了一遍。這類問(wèn)題通常是三種情況。一種是文件本身是文本文件或網(wǎng)頁(yè)文件只是名字改成.xlsx。處理方式是先用記事本打開(kāi)看看開(kāi)頭內(nèi)容如果是純文本說(shuō)明是CSV或HTML文件正確做法是直接用Excel的“打開(kāi)-Browse”并選擇“所有文件”來(lái)打開(kāi)或改成正確的擴(kuò)展名再打開(kāi)。第二種是Excel文件損壞。文件內(nèi)部結(jié)構(gòu)被破壞時(shí)會(huì)出現(xiàn)這個(gè)報(bào)錯(cuò)。可以先嘗試Excel自帶的“打開(kāi)并修復(fù)”打開(kāi)Excel按CtrlO打開(kāi)這個(gè)文件所在位置選中文件后點(diǎn)文件名稱旁邊的下拉箭頭選擇“打開(kāi)并修復(fù)”。如果能修復(fù)Excel會(huì)盡量恢復(fù)數(shù)據(jù)如果修復(fù)失敗可以嘗試把文件擴(kuò)展名改為.xls重新打開(kāi)或用WPS試一下再導(dǎo)出回Excel。第三種是文件確實(shí)不是標(biāo)準(zhǔn)Office格式比如從第三方系統(tǒng)導(dǎo)出的假xlsx、加密后的文件、或部分網(wǎng)盤(pán)下載中斷的文件。這種只能用源系統(tǒng)重新導(dǎo)出或從網(wǎng)盤(pán)重新下載完整文件。補(bǔ)一個(gè)實(shí)用技巧文件打不開(kāi)之前先檢查文件大小如果一個(gè)xlsx只有1KB大概率是空殼或者下載錯(cuò)誤沒(méi)必要浪費(fèi)時(shí)間。4.4 VBA、宏與打印相關(guān)的隱藏坑當(dāng)天還整理了兩個(gè)偏門(mén)問(wèn)題用VBA關(guān)閉Excel文件以及宏工作表插入空行方法、Excel狀態(tài)欄看小說(shuō)的VBA代碼。用VB關(guān)閉Excel文件如果是在外部VB程序里操作Excel核心是要拿到Workbook對(duì)象并調(diào)用Close但要防止出現(xiàn)“內(nèi)存不足”或進(jìn)程殘留。我不會(huì)用GetObject獲取已打開(kāi)的Excel實(shí)例時(shí)要加上ReleaseComObject和GC.Collect否則每運(yùn)行一次就留一個(gè)EXCEL.EXE進(jìn)程。示例Dim xlApp As Object Set xlApp CreateObject(Excel.Application) xlApp.Workbooks.Open C:\test.xlsx xlApp.ActiveWorkbook.Close SaveChanges:True xlApp.Quit Set xlApp Nothing宏工作表插入空行的方法更簡(jiǎn)單但在1月24日學(xué)習(xí)時(shí)發(fā)現(xiàn)很多同事不清楚宏里插入行本質(zhì)是Range.Insert方法。示例是在第5行前插入一行Rows(5:5).Select Selection.Insert Shift:xlDown, CopyOrigin:xlFormatFromLeftOrAbove如果是批量插入空行比如每隔3行插一行就用循環(huán)從最后一行往前處理避免插入后行號(hào)變化導(dǎo)致錯(cuò)位。網(wǎng)上流傳的“Excel狀態(tài)欄看小說(shuō)的VBA代碼”本質(zhì)是用狀態(tài)欄顯示文本Application.StatusBar 小說(shuō)內(nèi)容配合定時(shí)循環(huán)刷新這種小玩具代碼容易寫(xiě)但看久了傷眼睛我建議當(dāng)作學(xué)習(xí)VBA的練手項(xiàng)目不值得真的用來(lái)消磨時(shí)間。4.5 打印相關(guān)的三大高頻問(wèn)題說(shuō)到“excel打印”1月24日也踩了幾個(gè)坑。第一個(gè)坑是打印區(qū)域不對(duì)可能只想打印A到F列結(jié)果Excel把整張表都輸出。解決方法是選中要打印的區(qū)域點(diǎn)擊“頁(yè)面布局-打印區(qū)域-設(shè)置打印區(qū)域”。第二個(gè)坑是打印時(shí)沒(méi)有網(wǎng)格線。很多人以為Excel默認(rèn)打印網(wǎng)格線其實(shí)默認(rèn)不打印。需要點(diǎn)擊“頁(yè)面布局-工作表選項(xiàng)-網(wǎng)格線-打印”勾選。第三個(gè)坑是打印縮放在顯示器上很好看印出來(lái)卻截?cái)?。建議在“頁(yè)面布局-調(diào)整為適合大小-寬度1頁(yè)高度1頁(yè)”統(tǒng)一設(shè)置如果表格太長(zhǎng)又不想縮小就在“分頁(yè)預(yù)覽”里手動(dòng)拖動(dòng)分頁(yè)符。Excel打印還有一個(gè)冷門(mén)技巧可以配合“Excel點(diǎn)擊股票代碼自動(dòng)打開(kāi)通達(dá)信分時(shí)圖”這類自動(dòng)化需求本質(zhì)上是超鏈接或VBA調(diào)用外部程序但打印場(chǎng)景中注意不要打印超鏈接的跳轉(zhuǎn)標(biāo)記用“頁(yè)面布局-行和列-在Excel中保留鏈接”之類的設(shè)置提前處理即可。5. 提效小技巧下拉列表、UUID、Markdown轉(zhuǎn)Excel、更多細(xì)節(jié)5.1 下拉列表、快捷錄入與單獨(dú)提取同列相同數(shù)據(jù)熱搜詞里的“excel設(shè)置為下拉列表”“excel單元格怎么做下拉欄單獨(dú)提取同列相同數(shù)據(jù)”本質(zhì)上就是數(shù)據(jù)驗(yàn)證的小進(jìn)階。基礎(chǔ)的下拉列表設(shè)置選中目標(biāo)區(qū)域點(diǎn)擊“數(shù)據(jù)-數(shù)據(jù)驗(yàn)證-設(shè)置-允許-序列”來(lái)源框里手動(dòng)輸入選項(xiàng)選項(xiàng)用英文逗號(hào)分開(kāi)是,否,待定如果選項(xiàng)來(lái)自某個(gè)工作表的單元格區(qū)域來(lái)源框直接寫(xiě) $F$1:$F$10 或者用名稱管理器定義一個(gè)名稱再引用。注意來(lái)源區(qū)域不要包含空白單元格否則下拉里會(huì)出現(xiàn)空格選項(xiàng)?！皢为?dú)提取同列相同數(shù)據(jù)”這個(gè)需求我提供最實(shí)用的方案選中該列按CtrlShiftL開(kāi)啟篩選點(diǎn)篩選下拉取消“全選”勾選需要的值即可臨時(shí)查看如果想生成一份唯一的選項(xiàng)列表用“數(shù)據(jù)-高級(jí)篩選-選擇不重復(fù)的記錄”或者用UNIQUE函數(shù)新版ExcelUNIQUE(A2:A100)這個(gè)函數(shù)會(huì)返回所有去重后的值且自動(dòng)溢出到相鄰單元格。配合數(shù)據(jù)驗(yàn)證就能做一個(gè)動(dòng)態(tài)更新的下拉列表源數(shù)據(jù)新增內(nèi)容下拉選項(xiàng)自動(dòng)同步。這是1月24日測(cè)試下來(lái)最順滑的組合方案。5.2 Excel寫(xiě)UUID的兩種可行辦法“excel寫(xiě)uuid”這個(gè)需求在測(cè)試管理和數(shù)據(jù)處理場(chǎng)景里經(jīng)常出現(xiàn)。UUID是通用唯一識(shí)別碼形如“3f2a2b34-6a69-4b0e-9ad4-5a1f677c9a12”需要在Excel里批量生成時(shí)有現(xiàn)成辦法。方法一用Excel公式拼接。LOWER(CONCATENATE( DEC2HEX(RANDBETWEEN(0, 4294967295), 8), -, DEC2HEX(RANDBETWEEN(0, 65535), 4), -, DEC2HEX(RANDBETWEEN(0, 65535), 4), -, DEC2HEX(RANDBETWEEN(0, 65535), 4), -, DEC2HEX(RANDBETWEEN(0, 4294967295), 8), DEC2HEX(RANDBETWEEN(0, 65535), 4) ))這個(gè)方法的問(wèn)題是不保證唯一性批量生成幾千個(gè)時(shí)碰撞概率不低。方法二用VBA生成真正符合UUID v4規(guī)范的值。UUID v4要求第13個(gè)字符是4第17個(gè)字符是8、9、A或B。標(biāo)準(zhǔn)VBA示例Function GenerateUUID() As String Dim i As Long Dim hexChars As String Dim uuid As String Dim rndByte As Integer hexChars 0123456789abcdef Randomize uuid For i 1 To 36 Select Case i Case 9, 14, 19, 24 uuid uuid - Case 15 uuid uuid 4 Case 20 rndByte Int((3) * Rnd 8) uuid uuid Mid(hexChars, rndByte 1, 1) Case Else rndByte Int((15) * Rnd) uuid uuid Mid(hexChars, rndByte 1, 1) End Select Next i GenerateUUID uuid End Function在單元格里輸入 GenerateUUID() 并下拉填充即可。如果想一次性寫(xiě)幾百行用這個(gè)自定義函數(shù)后復(fù)制-粘貼為值避免下次重算時(shí)變化。5.3 Markdown表格轉(zhuǎn)Excel、批量處理與框架化思維“markdown表格轉(zhuǎn)換excel”是很多人寫(xiě)文檔時(shí)遇到的尷尬。Markdown表格是文本格式直接復(fù)制到Excel往往還是一列兩列錯(cuò)亂。我的做法分兩種。第一種在支持復(fù)制Markdown表格的編輯器比如Typora里直接選中表格右鍵復(fù)制為純文本再粘貼到Excel時(shí)選擇“文本導(dǎo)入向?qū)А卑捶指舴x擇“制表符”通常能分列成功。第二種如果不想開(kāi)編輯器用在線工具將Markdown表格轉(zhuǎn)CSV再把CSV導(dǎo)入Excel。不會(huì)用在線工具的還有一個(gè)本地方案復(fù)制Markdown表格到文本編輯器把“|”全部替換成制表符在Excel里粘貼后點(diǎn)“數(shù)據(jù)-分列-分隔符號(hào)-制表符”也能還原。這個(gè)問(wèn)題的本質(zhì)是數(shù)據(jù)格式轉(zhuǎn)換思維。熱搜詞里的“excel處理框架”和“excel轉(zhuǎn)換規(guī)則引擎”也屬于這一類單一表格操作不滿足復(fù)雜業(yè)務(wù)時(shí)人們會(huì)想把這些規(guī)則引擎化。比如“早做完不加班excel函數(shù)應(yīng)用效率手冊(cè)pdf下載”這類需求表面上是求手冊(cè)實(shí)質(zhì)是想要一套成體系的Excel處理方法論。我的判斷是結(jié)構(gòu)化的Excel處理能力比單獨(dú)記住某個(gè)函數(shù)更重要。具體操作就是給常用數(shù)據(jù)建模板、把清洗規(guī)則固定成流程、用名稱管理器和表格對(duì)象代替裸單元格引用再配合少量VBA或Python腳本處理重復(fù)勞動(dòng)。這套框架一旦沉淀下來(lái)你會(huì)發(fā)現(xiàn)大量“Excel使用技巧大全”里零散的技巧最終都可以歸并到幾個(gè)固定的工作流里處理效率自然就上來(lái)了。另外統(tǒng)計(jì)、標(biāo)準(zhǔn)化這類操作比如“excel做z-score標(biāo)準(zhǔn)化”其實(shí)就是函數(shù)式地套用公式(X - 平均值) / 標(biāo)準(zhǔn)差。在Excel里用AVERAGE和STDEV.P或STDEV.S搭配即可。這類操作一旦熟練配合前面對(duì)數(shù)據(jù)流轉(zhuǎn)的掌握足以覆蓋大部分初級(jí)數(shù)據(jù)處理崗的日常需求。6. 1月24日學(xué)習(xí)記錄里我最想保留的幾條經(jīng)驗(yàn)寫(xiě)完這篇記錄我最想強(qiáng)調(diào)的是做完之后順手沉淀這點(diǎn)。單獨(dú)學(xué)一個(gè)SUMIFS、導(dǎo)入一次ArcGIS、修復(fù)一次CtrlV都不難難的是把每次解決問(wèn)題的路徑記錄下來(lái)形成自己的Excel處理手冊(cè)。我自己的做法是建一個(gè)“Excel問(wèn)題庫(kù)”工作簿里面按“函數(shù)、數(shù)據(jù)流轉(zhuǎn)、故障排查、自動(dòng)化”四個(gè)工作表分類每次踩坑就記錄四列問(wèn)題描述、原因、解決步驟、適用版本。像1月24日這次學(xué)習(xí)就把SUMIFS多條件的寫(xiě)法、Excel導(dǎo)入ArcGIS的字段名限制、CtrlV失效的排查順序、UUID的VBA生成函數(shù)全部沉淀進(jìn)去。下次再遇到同類問(wèn)題直接搜自己的記錄速度遠(yuǎn)比去網(wǎng)上翻快。最后分享一個(gè)小習(xí)慣每次打開(kāi)Excel哪怕只用五分鐘處理一個(gè)表我也會(huì)順手把操作錄成文字或截圖放到一個(gè)待整理文件夾月底統(tǒng)一匯總歸檔。堅(jiān)持半年后你會(huì)發(fā)現(xiàn)自己對(duì)Excel的理解不再停留在“會(huì)操作”而是慢慢形成了系統(tǒng)性的判斷力。1月24日的這次學(xué)習(xí)記錄就是這套方法里的一份新鮮樣本。