物流管理工作中的EXCEL應(yīng)用1

1、倉儲(chǔ)作業(yè)高效思路EXCEL培訓(xùn)專用教材前言物流工作十多年來，從快遞工作到第三方物流和企業(yè)物流管理 ,尤其是在倉庫作業(yè)中，我有很多提高效率和準(zhǔn)確率的革新，而這些革新中 EXCEL 給了我很大的幫助，讓我十分受益。因?yàn)?EXCEL讓我受益匪淺，所以，我很樂意跟大家分享 EXCEL那些對(duì)我們工作有幫助的功能，尤其是從事物流工作或以后可能會(huì)從事物流工作的人， 應(yīng)當(dāng)需要熟練運(yùn)用 EXCEL 的一些功能。 EXCEL 是一種辦公軟件，對(duì)我來講，它是很好的工具，它不能代替其他軟件和工具，但其他軟件和工具也不能代替 EXCEL。各有所長，各有所短。沒有好與壞，只有適合之說。 能解決實(shí)際問題的就是好工具 !EX

2、CEL 的特點(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 對(duì)數(shù)據(jù)

3、進(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)和大家分享 ,為

4、了能讓大家有更多的認(rèn)識(shí) ,我將十多年工作中用 EXCEL 解決的種種問題和如何提高操作效率的應(yīng)用表格匯編于本冊(cè)，第一部分為常用函數(shù)應(yīng)用示例 (截圖 ),EXCEL 函數(shù)不熟悉的人員需要先學(xué)習(xí)第一部分后再看第二部分案例。由于我一開始 EXCEL 尚不精通，故資料中定有一些表格會(huì)有煩瑣之處。如有不值一看的表 ,還請(qǐng)一帶而過。無論讀者是 EXCEL 初,中,高級(jí)或在不同的物流倉儲(chǔ)管理領(lǐng)域 , 只希望大家能從中各取所需 ,通過一些案例打開更多的工作思路，在工作中不斷的加以優(yōu)化。56r2014 年7月24 日目錄目錄 .第一部分EXCEL 基礎(chǔ)及常見函數(shù)應(yīng)用示例 .01.了解 EXCEL表格 .02.運(yùn)

5、算及引用符號(hào) .03.加減乘除的基本計(jì)算 .04.“的$”應(yīng)用 .05.年，月，日的計(jì)算 .06.時(shí)，分，秒的計(jì)算 .07.數(shù)據(jù)統(tǒng)計(jì)中的應(yīng)用（求各種值圖例）.08.字段信息的提取與編輯 .09.“ IF函數(shù)”的廣范應(yīng)用 .10.文字格式的轉(zhuǎn)換 .11.信息的查找與匹配 .12.“ INDEX”在信息查詢與提取中的應(yīng)用及說明.第二部分十年工作中的應(yīng)用實(shí)例 .01在 KPI 統(tǒng)計(jì)表中，求滿足多種條件的值.02從相對(duì)規(guī)則的信息中提取所要的信息數(shù)據(jù).03從工作量統(tǒng)計(jì)表中提取每一個(gè)人的工作量.04工作量分值與績效考核.05考勤異常情況提醒.06根據(jù)打卡記錄生成考勤表.07根據(jù)考勤機(jī)打卡記錄統(tǒng)計(jì)加班人數(shù).

6、08考勤統(tǒng)計(jì)表的系統(tǒng)性制作.09將已知信息錄入到指定表格.10數(shù)據(jù)透視的應(yīng)用.11每周工作量的匯總.12工作計(jì)劃的 ”7要素 ”.13倉儲(chǔ)費(fèi)用計(jì)算.14簡易庫存賬的制作.15第三方物流臺(tái)賬.16倉庫可視化管理.17如何做到庫存貨品的先進(jìn)先出.18如何判斷貨品是否按先進(jìn)先出發(fā)貨.19大宗貨位的自動(dòng)選擇.20服裝線上與線下入庫貨品庫位分配.21服裝退貨整理與箱單打印.22退貨上架貨品堆碼指定托盤號(hào).23箱嘜連續(xù)打印的VBA 方法 .24自動(dòng)化分揀思路之服裝越庫操作.25根據(jù)已揀貨品量求剩余量.26貨品完全匹配條件的分揀.27 RFID條件下的高效分揀思路.28 RFID思路之整托貨品越庫分配的計(jì)算

7、（精典案例）.29發(fā)貨包裝（拼箱）自動(dòng)計(jì)算.30庫內(nèi)短駁加箱(零箱 )拼箱標(biāo)簽的制作.31根據(jù)整箱規(guī)格檢查播種或揀貨過程的準(zhǔn)確性.32播種后如何提高點(diǎn)數(shù)裝箱的效率與準(zhǔn)確率.33播種單格式高效讀報(bào)方式的轉(zhuǎn)換.34分批入庫貨品的配發(fā).35黃金包裝需求量計(jì)算思路.36不同配比條件下的黃金包發(fā)貨量計(jì)算.37電商操作與物流發(fā)貨思路.38電商揀貨流程優(yōu)化.39電商分區(qū)揀貨再合單的操作思路.40電商兩地發(fā)貨模式的單據(jù)審核.41電商揀貨路徑優(yōu)化與距離計(jì)算.42亞馬遜電商導(dǎo)出數(shù)據(jù)的整理.43第三方物流（企業(yè)物流）發(fā)貨方式及承運(yùn)商自動(dòng)選擇思路.44兩家貨運(yùn)公司價(jià)格與時(shí)效對(duì)比參考.45上海市內(nèi)快遞編號(hào)法分揀.46上

8、海同城快遞“蜘蛛網(wǎng)”中轉(zhuǎn)班車運(yùn)行設(shè)計(jì).47全國辦事處倉庫分布圖.48上海地區(qū)店鋪分布圖.49長三角地帶物流中心選址分析.50服裝的標(biāo)準(zhǔn)化物流.51不同規(guī)格的包裝以滿足不同需求的發(fā)貨.倉儲(chǔ)現(xiàn)場管理心得.作者簡介 :葉劍 ,部隊(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é)約物

9、流操作成本達(dá)上千萬元,成為許多院校 ,電商倉儲(chǔ)企業(yè)和物流沙龍受歡迎的講師和顧問.第一部分EXCEL 基礎(chǔ)及常見函數(shù)應(yīng)用示例在第一部分中， Excel 函數(shù)基礎(chǔ)應(yīng)用及說明都以精簡截圖的方式表達(dá)，希望讓讀者在入門的時(shí)候能夠?qū)?Excel 函數(shù)功能有更直觀的了解。01. 了解 EXCEL表格2. 運(yùn)算及引用符號(hào)3. 加減乘除的基本計(jì)算4. “$”的應(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 行位置就是單元

10、格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。但如果在公式中的行號(hào)前面加“$”，再往下拉時(shí)，仍是11，也就是說“ $”已經(jīng)對(duì) 11 進(jìn)行了鎖定。要注意的是，將選中輸入公式的單元

11、格，如果公式中的單元格行號(hào)沒有用$鎖定，向下拖動(dòng)時(shí)會(huì)依次增加，列號(hào)不變。如果列號(hào)沒有用$鎖定，則往右拖動(dòng)時(shí)，列號(hào)依次增加，行號(hào)不變。5. 年，月，日的計(jì)算求某個(gè)日期的年份，月份和號(hào)，分別用：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í)需要注意中西方一周的第一天是不同的。6. 時(shí)，分，秒的計(jì)算求某個(gè)時(shí)間的時(shí)，分，秒，可

12、以直接用函數(shù)：HOUR,MINUTE,SECOND.如果計(jì)算某項(xiàng)工作開始到結(jié)束，用了多少分鐘，可以先用結(jié)束時(shí)間減開始時(shí)間，得到用了多少小時(shí)多少分鐘，然后用小時(shí)數(shù)× 60分鐘分即可。7. 數(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 只對(duì)數(shù)值進(jìn)行計(jì)數(shù)。AVERAGE與 AVERAGEA都

13、是求平均值，但AVERAGE只是針對(duì)數(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=

14、 ”大一 ”)*(D2:D13)解釋：求符合多種條件的和，即當(dāng)學(xué)校為“南大”時(shí)(A2:A13=”南大 ”)，班級(jí)為“大一”時(shí)(B2:B13=”大一 ”)，有多少人參加（計(jì)算范圍D2:D13），“ * ”為連接多種滿足條件的符號(hào)。注意在使用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

15、，求某個(gè)數(shù)值的平方根。SUMIF 是求滿足條件的和， 如求一年級(jí)有多少學(xué)生，及在 A14:A22 年級(jí)范圍內(nèi)有多少是等于A14”一年級(jí) ”的，如果是“一年級(jí)” ，那么后面的“人數(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í)按

16、： ctrl, shift, enter生成。8. 字段信息的提取與編輯注意： &與 CONCATENATE的區(qū)別?！?& ”的輸入，是按住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 位是“江蘇省” 。

17、是從字段中間提出信息的函數(shù)，一個(gè)人的出生年份，在身份證號(hào)碼中，是從第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)容。9. “IF”函數(shù)的廣范應(yīng)用IF，如果滿足條件，即為結(jié)果“A”，否則就為結(jié)果“B”。=IF(B3>=60,”是 ”,”) 如果王二小的成績B3 大于等于60，即顯示“是”

18、 ，否則什么也不顯示。=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，所以評(píng)定為“及格” 。而用“ LOOKUP”函數(shù)， 76 位于 60-80 之間，匹配結(jié)果為“及格”。先判斷兩個(gè)日期之間相隔的年份，注意公式的

19、使用，而不是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”列所對(duì)應(yīng)的值。關(guān)鍵詞：首列。MATCH，在區(qū)域里A2:A