.思路與EXCEL物流十多年工作中解決各種問題的實(shí)例葉劍倉儲(chǔ)作業(yè)高效思路EXCEL培訓(xùn)專用教材前言物流工作十多年來，從快遞工作到第三方物流和企業(yè)物流管理,尤其是在倉庫作業(yè)中，我有很多提高效率和準(zhǔn)確率的革新，而這些革新中EXCEL給了我很大的幫助，讓我十分受益。因?yàn)镋XCEL讓我受益匪淺，所以，我很樂意跟大家分享EXCEL那些對我們工作有幫助的功能，尤其是從事物流工作或以后可能會(huì)從事物流工作的人，應(yīng)當(dāng)需要熟練運(yùn)用EXCEL的一些功能。 EXCEL是一種辦公軟件，對我來講，它是很好的工具，它不能代替其他軟件和工具，但其他軟件和工具也不能代替EXCEL。各有所長，各有所短。沒有好與壞，只有適合之說。能解決實(shí)際問題的就是好工具!EXCEL的特點(diǎn)，就是靈活性比較高，受限止少,是大家常用的辦公軟件,EXCEL可以通過函數(shù)和VBA制作許多的小程序，進(jìn)行系統(tǒng)性的操作。EXCEL不僅可以進(jìn)行各種運(yùn)算制作各種圖表，還能繪制許多圖形?？梢杂盟治鰯?shù)據(jù)，還能以很多形式去體現(xiàn)數(shù)據(jù)，功能十分強(qiáng)大！EXCEL的缺點(diǎn)就是存儲(chǔ)數(shù)據(jù)有限，數(shù)據(jù)量大計(jì)算過程較多時(shí)，反應(yīng)速度會(huì)比較慢。所以，我們用EXCEL，就要揚(yáng)長避短。我認(rèn)為，作為一個(gè)物流工作人員，尤其是從事倉庫管理工作，如果不懂點(diǎn)EXCEL操作，可能會(huì)過度依賴現(xiàn)有的系統(tǒng)軟件等條件,不能靈活機(jī)動(dòng),與時(shí)俱進(jìn),導(dǎo)致許多作業(yè)的浪費(fèi)。工作中，我們需要用到EXCEL對數(shù)據(jù)進(jìn)行統(tǒng)計(jì)，分析，計(jì)算，這樣可以開拓很多思路，不會(huì)用EXCEL，就意味著很多思路沒有打開，自然就會(huì)做得不夠好。當(dāng)然，即便是EXCEL用得很好的人，沒有物流實(shí)戰(zhàn)經(jīng)驗(yàn)和管理經(jīng)驗(yàn)，也不一定就能把倉庫管理得好，故實(shí)踐方能出真知。作為倉庫管理人員,如果倉庫沒有自動(dòng)分揀和電子揀貨系統(tǒng),但你要有自動(dòng)分揀和電子揀貨系統(tǒng)的思路；沒動(dòng)自動(dòng)化的立體倉庫,但你要有自動(dòng)化立體倉庫的思路,做到閉著眼睛也能配出貨品；沒有RFID射頻技術(shù),但你要有RFID高效掃描的思路；沒有飛機(jī)大炮,就把小米加步槍的潛力挖掘起來充分利用.日積月累,非常有幸的是能借助物流沙龍這個(gè)平臺(tái),將經(jīng)驗(yàn)和大家分享,為了能讓大家有更多的認(rèn)識(shí),我將十多年工作中用EXCEL解決的種種問題和如何提高操作效率的應(yīng)用表格匯編于本冊，第一部分為常用函數(shù)應(yīng)用示例(截圖),EXCEL函數(shù)不熟悉的人員需要先學(xué)習(xí)第一部分后再看第二部分案例。由于我一開始EXCEL尚不精通，故資料中定有一些表格會(huì)有煩瑣之處。如有不值一看的表,還請一帶而過。無論讀者是EXCEL初,中,高級或在不同的物流倉儲(chǔ)管理領(lǐng)域,只希望大家能從中各取所需,通過一些案例打開更多的工作思路，在工作中不斷的加以優(yōu)化。56r 2014年7月24日目錄目錄3第一部分 EXCEL基礎(chǔ)及常見函數(shù)應(yīng)用示例501.了解EXCEL表格502.運(yùn)算及引用符號503.加減乘除的基本計(jì)算604.“$”的應(yīng)用605.年，月，日的計(jì)算706.時(shí)，分，秒的計(jì)算707.數(shù)據(jù)統(tǒng)計(jì)中的應(yīng)用（求各種值圖例）808.字段信息的提取與編輯1309.“IF”函數(shù)的廣范應(yīng)用1510.文字格式的轉(zhuǎn)換1811.信息的查找與匹配1812.“INDEX”在信息查詢與提取中的應(yīng)用及說明21第二部分 十年工作中的應(yīng)用實(shí)例2301在KPI統(tǒng)計(jì)表中，求滿足多種條件的值2302從相對規(guī)則的信息中提取所要的信息數(shù)據(jù)2603從工作量統(tǒng)計(jì)表中提取每一個(gè)人的工作量2704工作量分值與績效考核2705考勤異常情況提醒2906根據(jù)打卡記錄生成考勤表3107根據(jù)考勤機(jī)打卡記錄統(tǒng)計(jì)加班人數(shù)3308考勤統(tǒng)計(jì)表的系統(tǒng)性制作3609將已知信息錄入到指定表格4010數(shù)據(jù)透視的應(yīng)用4111每周工作量的匯總4212工作計(jì)劃的”7要素”4313倉儲(chǔ)費(fèi)用計(jì)算4514簡易庫存賬的制作4715第三方物流臺(tái)賬4816倉庫可視化管理5117如何做到庫存貨品的先進(jìn)先出5418如何判斷貨品是否按先進(jìn)先出發(fā)貨5519大宗貨位的自動(dòng)選擇5620服裝線上與線下入庫貨品庫位分配5821服裝退貨整理與箱單打印6122退貨上架貨品堆碼指定托盤號6323箱嘜連續(xù)打印的VBA方法6524自動(dòng)化分揀思路之服裝越庫操作6725根據(jù)已揀貨品量求剩余量6926貨品完全匹配條件的分揀7027RFID條件下的高效分揀思路7128RFID思路之整托貨品越庫分配的計(jì)算（精典案例）7629發(fā)貨包裝（拼箱）自動(dòng)計(jì)算8230庫內(nèi)短駁加箱(零箱)拼箱標(biāo)簽的制作8431根據(jù)整箱規(guī)格檢查播種或揀貨過程的準(zhǔn)確性8532播種后如何提高點(diǎn)數(shù)裝箱的效率與準(zhǔn)確率?8533播種單格式高效讀報(bào)方式的轉(zhuǎn)換8634分批入庫貨品的配發(fā)9135黃金包裝需求量計(jì)算思路9336不同配比條件下的黃金包發(fā)貨量計(jì)算9537電商操作與物流發(fā)貨思路9938電商揀貨流程優(yōu)化10339電商分區(qū)揀貨再合單的操作思路11140電商兩地發(fā)貨模式的單據(jù)審核11441電商揀貨路徑優(yōu)化與距離計(jì)算11942亞馬遜電商導(dǎo)出數(shù)據(jù)的整理12743第三方物流（企業(yè)物流）發(fā)貨方式及承運(yùn)商自動(dòng)選擇思路13144兩家貨運(yùn)公司價(jià)格與時(shí)效對比參考13245上海市內(nèi)快遞編號法分揀13446上海同城快遞“蜘蛛網(wǎng)”中轉(zhuǎn)班車運(yùn)行設(shè)計(jì)13647全國辦事處倉庫分布圖13848上海地區(qū)店鋪分布圖13949長三角地帶物流中心選址分析14150服裝的標(biāo)準(zhǔn)化物流14251不同規(guī)格的包裝以滿足不同需求的發(fā)貨146倉儲(chǔ)現(xiàn)場管理心得147作者簡介:葉劍,部隊(duì)優(yōu)秀”四會(huì)教練員”,從快遞員,倉管員,客服員,調(diào)度做起,是具有豐富的物流現(xiàn)場實(shí)戰(zhàn)和管理經(jīng)驗(yàn)的物流師,歷任快遞公司,第三方物流公司主管,目前就職于綾致時(shí)裝任倉儲(chǔ)部經(jīng)理.在從事快遞,第三方物流,企業(yè)倉庫管理的近15年中,憑借清晰的工作思路,在企業(yè)沒有WMS的情況下,借助EXCEL,提高效率和準(zhǔn)確率,降低成本的革新案例達(dá)一百多項(xiàng),為服務(wù)的企業(yè)節(jié)約物流操作成本達(dá)上千萬元,成為許多院校,電商倉儲(chǔ)企業(yè)和物流沙龍受歡迎的講師和顧問.第一部分 EXCEL基礎(chǔ)及常見函數(shù)應(yīng)用示例在第一部分中，Excel函數(shù)基礎(chǔ)應(yīng)用及說明都以精簡截圖的方式表達(dá)，希望讓讀者在入門的時(shí)候能夠?qū)xcel函數(shù)功能有更直觀的了解。01.了解EXCEL表格02.運(yùn)算及引用符號03.加減乘除的基本計(jì)算04.“$”的應(yīng)用單元格的位置，由列坐標(biāo)和行坐標(biāo)組合確定，列坐標(biāo)用英文字母表示，行坐標(biāo)用阿拉伯?dāng)?shù)字表示，如第1列的表達(dá)式是“A”，第2列的表達(dá)式是“B”，以此類推。第1行的表達(dá)式是“1”，第2行的表達(dá)式是“2”，以此類推。第2列的第2行位置就是單元格B2，第1列的第11行位置就是單元格A11，在我們用函數(shù)輸入公式時(shí)，A1就是表示A1這個(gè)單元格的值，A11就是表示A11這個(gè)單元格的值，如果在某個(gè)單元格內(nèi)輸入“=A11”，那么A11單元格里面是什么，這個(gè)單元格將體現(xiàn)什么。如上表中，A11單元格內(nèi)是一個(gè)“甲”字，在B11中輸入了=A11，結(jié)果也就顯示了“甲”字。在選中B11這個(gè)單元格往下拉時(shí)，相應(yīng)的單元格B12,B13,B14,分別就有了結(jié)果A12,A13,A14。但如果在公式中的行號前面加“$”，再往下拉時(shí)，仍是11，也就是說“$”已經(jīng)對11進(jìn)行了鎖定。要注意的是，將選中輸入公式的單元格，如果公式中的單元格行號沒有用$鎖定，向下拖動(dòng)時(shí)會(huì)依次增加，列號不變。如果列號沒有用$鎖定，則往右拖動(dòng)時(shí)，列號依次增加，行號不變。05.年，月，日的計(jì)算求某個(gè)日期的年份，月份和號，分別用：YEAR,MONTH,DAY.在EXCEL任意單元格內(nèi)輸入此“公式”，回車后就會(huì)體現(xiàn)當(dāng)天的日期/當(dāng)時(shí)的時(shí)間。用結(jié)束日期減開始日期，即可得到它們之間相隔的天數(shù)。然求當(dāng)中的工作日，則需要用函數(shù)NETWORKDAYS.如果要知道某一天是星期幾？可以先通過WEEKDAY函數(shù)求出所在周的第幾天，然后再去匹配第幾天是星期幾，在計(jì)算時(shí)需要注意中西方一周的第一天是不同的。06.時(shí)，分，秒的計(jì)算求某個(gè)時(shí)間的時(shí)，分，秒，可以直接用函數(shù)：HOUR,MINUTE,SECOND.如果計(jì)算某項(xiàng)工作開始到結(jié)束，用了多少分鐘，可以先用結(jié)束時(shí)間減開始時(shí)間，得到用了多少小時(shí)多少分鐘，然后用小時(shí)數(shù)60分鐘分即可。 07.數(shù)據(jù)統(tǒng)計(jì)中的應(yīng)用（求各種值圖例）尤其求最大公約數(shù)，可以確定最小需求單位，在物流中意義很大。 直接輸入=PI()，就得到圓周率。MAX，求一組數(shù)值中的最大值；MIN，求一組數(shù)值中的最小值。SUM，常用的求和函數(shù)，直接引用，即可求某個(gè)范圍所有數(shù)值的和。COUNT與COUNTA都是計(jì)數(shù)，區(qū)別在于COUNTA可以將文本格式的值進(jìn)行計(jì)數(shù)，而COUNT只對數(shù)值進(jìn)行計(jì)數(shù)。AVERAGE與AVERAGEA都是求平均值，但AVERAGE只是針對數(shù)值，用數(shù)值的和除以數(shù)值的個(gè)數(shù)，而得到平均值。但AVERAGEA，則是用數(shù)值的和除以所有參與的（包含文本）的人數(shù)，而得到的平均值。INT，取整，不保留任何小數(shù)。ROUNDUP，向上舍入，可按需求保留小數(shù)位數(shù)，“0”即是不保留。MOD，求被除數(shù)與除數(shù)的余數(shù)。此例在倉庫管理中計(jì)算入庫上架庫位和貨品盤點(diǎn)中應(yīng)用較多。RANK用于求某個(gè)值，在某個(gè)范圍的大小序列。B2單元格是王二小的成績，在B2:B7（6個(gè)人的成績）中排名第5。注意計(jì)算排名與倒數(shù)排名公式的區(qū)別，在后面加上“1”的作用。=SUMPRODUCT(A2:A13=”南大”)*(B2:B13=”大一”)*(D2:D13) 解釋：求符合多種條件的和，即當(dāng)學(xué)校為“南大”時(shí)(A2:A13=”南大”)，班級為“大一”時(shí)(B2:B13=”大一”)，有多少人參加（計(jì)算范圍D2:D13），“*”為連接多種滿足條件的符號。注意在使用SUMPRODUCT時(shí)，“，”與“*”的區(qū)別，如上SUMPRODUCT(B2:B9,C2:C9)，其結(jié)果就是從B2*C2一直加到B9*C9。COUNTIF，求在什么范圍，滿足“什么”條件的數(shù)值有幾個(gè)，計(jì)數(shù)所用。SUBTOTAL函數(shù)，具有多種函數(shù)的功能，在計(jì)算范圍前加“1,2,3,4,5,6,7,8,9”結(jié)果各不一樣。SUMSQ，所有數(shù)值平方的和；SQRT，求某個(gè)數(shù)值的平方根。SUMIF是求滿足條件的和，如求一年級有多少學(xué)生，及在A14:A22年級范圍內(nèi)有多少是等于A14”一年級”的，如果是“一年級”，那么后面的“人數(shù)”相加即得到所求的值。注意：COUNT，COUNTA，COUNTIF的區(qū)別。COUNTIF與SUMIF的區(qū)別，一是計(jì)數(shù)，一是求和。MIN（計(jì)算范圍）SMALL（計(jì)算范圍,1）MAX（計(jì)算范圍）LARGE（計(jì)算范圍,1）解釋：上表公式，是計(jì)算某個(gè)產(chǎn)品本次的發(fā)貨日期減去上一次的發(fā)貨日期，得到間隔的發(fā)貨時(shí)間。其中嵌用了：INDEX,LARGE,IF,ROW.在D4單元格輸入上面公式，然后下拉即可， 是同時(shí)按：ctrl, shift, enter 生成。08.字段信息的提取與編輯注意：&與CONCATENATE的區(qū)別?！?amp;”的輸入，是按住shift鍵同時(shí)按上行數(shù)字鍵“7”即可。注意：LEN與LENB的區(qū)別，及字符與字符串的區(qū)別，一個(gè)文字是一個(gè)字符串，一字符串可能是一個(gè)字符，也可能是兩個(gè)字符，一個(gè)數(shù)字或字母只有一個(gè)字符，但一個(gè)漢字卻有兩個(gè)字符。小竅門求字段中有幾個(gè)漢字：如果在上表中，用計(jì)算出來的字符數(shù)減去計(jì)算出來的字符串?dāng)?shù)，就可以知道字段中有幾個(gè)漢字了。LEFT(A5,3),是求A5單元格中左側(cè)3位的字段。A5單元格內(nèi)是“江蘇省泰州市”，左側(cè)3位是“江蘇省”。MID是從字段中間提出信息的函數(shù)，一個(gè)人的出生年份，在身份證號碼中，是從第7位，向后4位。上表中=RIGHT（A4，1），A4中的值是“中華人民共和國”，“中華人民共和國”的右側(cè)第1位，是“國”。FIND，查找某個(gè)字符串在某個(gè)字段中的第幾位。REPT，將需要重復(fù)的字段，重復(fù)“N”次。REPLACE，將字段中第幾位指定往后幾位的字段，替換成相應(yīng)的內(nèi)容。SUBSTITUTE，將字段中，符合替換的字段，替換成相應(yīng)的內(nèi)容。09.“IF”函數(shù)的廣范應(yīng)用IF，如果滿足條件，即為結(jié)果“A”，否則就為結(jié)果“B”。=IF(B3>=60,”是”,”) 如果王二小的成績B3大于等于60，即顯示“是”，否則什么也不顯示。=IF(B11<60,”不及格”,”) 如果王二小的成績B11小于60，即顯示“不及格”，否則什么也不顯示?！啊北硎静粷M足條件時(shí)，不顯示任何信息。AND,如果三門課程的成績同時(shí)達(dá)到90,即”是”全優(yōu)生,否則不是,即為”否”.OR,有任一門課程達(dá)到90分,即為滿足條件。上表以“王二小”為例，成績?yōu)?6，使用“IF”判斷，由于不滿90，所以不是“優(yōu)”，由于不滿80，所以也不是“良”，接下為大于60，所以評定為“及格”。而用“LOOKUP”函數(shù)，76位于60-80之間，匹配結(jié)果為“及格”。先判斷兩個(gè)日期之間相隔的年份，注意公式的使用，而不是YEAR(C4)YEAR(B4)。再判斷當(dāng)工齡大于等于5年時(shí)，給予獎(jiǎng)勵(lì)，否則沒有?；蛘哒f如果工齡不小于5年時(shí)，給予獎(jiǎng)勵(lì)。錢永正的入職日期是2011-3-5，雖然2013減2011等于2，但是由于2013-3-5小于2013-1-1，所以結(jié)果就需要減去1，即錢永正的工齡滿1年，不滿2年。=IF($A9<=$B$1,$A9,”),在B1(即$B$1)中輸入需要打印的箱數(shù)“5”，由于A9中的值“6”大于“5”，所以結(jié)果為”，而不是“6”， ”即不體現(xiàn)任何值。箱數(shù)輸入“2”，則體現(xiàn)2箱的打印內(nèi)容。10.文字格式的轉(zhuǎn)換11.信息的查找與匹配VLOOKUP，查找匹配函數(shù)，在數(shù)據(jù)首列搜索某個(gè)值，匹配首列往右“N”列所對應(yīng)的值。關(guān)鍵詞：首列。MATCH，在區(qū)域里A2:A9，找出所求值，從A2起算所在的序列號，。選定區(qū)域很重要，如區(qū)域設(shè)定為B1:I1，即從B1算起，B1作為第1列，香蕉則是在第3列。HLOOKUP，與VLOOKUP用法相同，區(qū)別在于搜索的值一個(gè)是在首行，一個(gè)是在首列。OFFSET，從某個(gè)單元格起，向下N列，再向右N列的單元格的值。注意發(fā)現(xiàn)上表中，INDEX與OFFSET的區(qū)別。ADDRESS，生成指定單元格的位置，ADDRESS（指定的行號，指定的列號）INDIRECT通常與ADDRESS結(jié)合使用，注意INDIRECT(F3)與INDIRECT(“F3”)的區(qū)別。注意：INDIRECT(ADDRESS(1,1)的結(jié)果，為INDIRECT(“A1”)。12.“INDEX”在信息查詢與提取中的應(yīng)用及說明說明：通過MATCH函數(shù)可以計(jì)算出所求值所在的“行數(shù)”和“列數(shù)”，表中“梨子”在第3列，而“價(jià)格”和“產(chǎn)地”分別在第2行和第3行，因此，通過INDEX查找，第2行第3列的值是“2.8”，第3行第3列的值是“濰坊”。上表是根據(jù)倉庫的溫度和干濕表溫差求庫房內(nèi)的相對濕度.通過INDEX函數(shù)直接計(jì)算出結(jié)果.通常情況下，查找信息，會(huì)用“篩選”來操作，比如在“分配地區(qū)”中篩選“上?！保梢缘弥心男┫涮柕呢浧贩峙浣o上海。但對于經(jīng)常性的操作，“篩選”就不太方便，而通過函數(shù)公式設(shè)置好后，查詢?nèi)我坏貐^(qū)的信息就會(huì)自動(dòng)生成，十分方便。圖示說明：.